Catalog

First time to know MySQL

Why study databases

  1. Position requirements
  2. Now the world , Big data era , Those who get data get the world
  3. Forced to demand ： Save the data
  4. Database is the core of all software systems

What is a database

  1. database （DB,DataBase）
  2. Concept ： Data warehouse , Software , Installed on top of the operating system .
  3. effect ： Store the data , Management data .

Database classification

  1. Relational database （SQL）

    1. MySQL,Oracle,SQLServer,DB2,SQLlite
    2. Between tables , The relationship between rows and columns is used to store data
  2. Non relational database （NoSQL-Not Only SQL）
    1. Redis,MongoDB
    2. Object storage , It is determined by the properties of the object .
  3. Database management system （DBMS）
    1. Database management software , Scientific and effective management of data , Maintain and access data .
    2. MySQL, Database management system .

MySQL brief introduction

  1. MySQL Is a relational database management system .
  2. Past life ： The Swedish MySQL AB company
  3. This life ： Belong to Oracle Its products
  4. MySQL It's the best RDBMS(Relational Database Management System, Relational database management system ) One of the application software .
  5. Open source database software .
  6. Small volume 、 Fast 、 Low total cost of ownership , Recruitment costs are relatively low , Everyone has to be able to ！
  7. Small and medium sized websites or large websites （ colony ）.
  8. Official website ：https://www.mysql.com

Windows install MySQL

Installation recommendations

  1. Try not to use exe, Unloading trouble , The registry
  2. Install as much as possible using compressed packages

Software download

  1. Download address ：https://downloads.mysql.com/archives/community/
  2. MySQL5.7.31 64 Bit download address ：https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.31-winx64.zip

Installation steps

  1. Unzip to the installation directory , Such as ：D:\environment\mysql-5.7.31
  2. Add environment variables
    1. My computer -》 attribute -》 senior -》 environment variable
    2. choice PATH, Add after ：MySQL Install under directory bin Directory path , Such as ：D:\environment\mysql-5.7.31\bin
  3. stay MySQL The installation directory D:\environment\mysql-5.7.31 Under the new my.ini file
  4. edit my.ini file
    1. Be careful ：data Directory does not need to be created , Will be generated automatically during initialization
    2. Be careful ：skip-grant-tables Yes, skip password verification , After setting the password , Need to be commented out 
[mysqld]

basedir=D:\environment\mysql-5.7.31\

datadir=D:\environment\mysql-5.7.31\data\

port=3306

skip-grant-tables

character-set-server=utf8
  1. install MySQL service ： Start... In administrator mode cmd, And switch the path to MySQL Under the bin Catalog , Then input mysqld -install, success , return ：Service successfully installed.
cd /d D:\environment\mysql-5.7.31\bin

mysqld -install
  1. Initialization data file ： Input mysqld --initialize-insecure --user=mysql, success , Generate data Catalog .data The directory under the directory corresponds to the database , Such as ：mysql,sys,performance_schema.
mysqld --initialize-insecure --user=mysql

  1. start-up MySQL： Input net start mysql, success , return ：MySQL The service is starting MySQL Service started successfully . And in the task manager MySQL service .
net start mysql
  1. Get into MySQL Management interface ： Input mysql -u root -p, enter , No password , Enter again , success .

    1. Be careful ：-p You can't add spaces after , Spaces are also characters , I think it's a password .
    2. Be careful ： To configure skip-grant-tables, Will skip password verification , When entering the password , No need to input , Directly enter .
    3. Be careful ： Comment out skip-grant-tables after , There are two ways to enter a password ： One is -p Back to the car , Enter the password again ; Two is -p Enter the password directly , Enter again , Such as ：mysql -u root -p123456, But it's not recommended .
mysql -u root -p
  1. change root password , In fact, it's modification mysql Database user Tabular authentication_string Field ,password() Function for md5 encryption .
update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';
  1. Refresh the permissions ：flush privileges,
flush privileges;
  1. Comment out my.ini In the document skip-grant-tables, Don't skip password verification anymore .
[mysqld]

basedir=D:\environment\mysql-5.7.31\

datadir=D:\environment\mysql-5.7.31\data\

port=3306

#skip-grant-tables

character-set-server=utf8
  1. restart mysql：net stop mysql,net start mysql
# sign out mysql

exit

# close mysql

net stop mysql

# start-up mysql

net start mysql
  1. test .
C:\Users\Administrator>mysql -u root -p

Enter password: ******

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 15

Server version: 5.7.29 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

install SQLyog

Download and install

  1. Download address ：https://sqlyog.en.softonic.com/
  2. SQLyog-13.1.6 Download address ：https://gsf-fl.softonic.com/1ce/278/b337b72b7ac51c9fcc627a7e1aa8a7710b/SQLyog-13.1.6-0.x64Community.exe?Expires=1609550838&Signature=d64bccebb6402a44cda8f80f9016fa852af514d0&url=https://sqlyog.en.softonic.com&Filename=SQLyog-13.1.6-0.x64Community.exe
  3. Follow the instructions to install , The free version doesn't need to be registered , The professional edition needs to be registered , If you register , You can try the following registration code .
    1. Registered name ：kuangshen
    2. Registration code ：8d8120df-a5c3-4989-8f47-5afc79c56e7c

Connect to database

Simple operation

  1. New database school, Base character set ：utf8, Database collation ：utf8_general_ci.

utf8_unicode_ci and utf8_general_ci Alignment 、 There is no real difference in English .

utf8_general_ci Fast proofreading , But the accuracy is a little bit poor .

utf8_unicode_ci High accuracy , But proofreading is a little slow .

If your application has German 、 French or Russian , Be sure to use utf8_unicode_ci. It's usually used utf8_general_ci That's enough .

ci yes case insensitive, namely " Case insensitive ", a and A It will be treated as the same in character judgment ;

bin It's binary , a and A Don't discriminate .

For example, you run ：

SELECT * FROM table WHERE txt = 'a'

So in utf8_bin You can't find txt = 'A' That line , and utf8_general_ci Then you can .

utf8_general_ci  Case insensitive , When you register your user name and email address, you need to use .

utf8_general_cs  Case sensitive , If you use this for your user name and email It's going to be bad

utf8_bin: String each string is compiled and stored with binary data .  Case sensitive , And can store binary content

every last SQLyog The execution of , The essence is corresponding to a SQL sentence , Can be viewed in the history of the software .

Such as ： Create database , In historical records, it's as follows , The essence is to execute statements ：CREATE DATABASE schoolCHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE DATABASE `school`CHARACTER SET utf8 COLLATE utf8_general_ci;
  1. new table student, engine ：InnoDB, Character set ：utf8, check ：utf8_general_ci.

The corresponding sentence is as follows 

CREATE TABLE `school`.`student` ( `id` INT(10) NOT NULL COMMENT ' student ID', `name` VARCHAR(100) NOT NULL COMMENT ' Student name ', `age` INT(3) NOT NULL COMMENT ' Age of trainees ', PRIMARY KEY (`id`) ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;
  1. See the table ： Right click on the student, Open the table data window , View table data window .

  1. Add records ： In the table data window , Input record , Click save or refresh , Keep records .
INSERT INTO `school`.`student` (`id`, `name`, `age`) VALUES ('1003', 'zhwj', '66');

Command line connection to database

Single-line comments ：--

Multiline comment ：/* */

mysql -u root -p -- Connect to database
exit; -- Exit connection
update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost'; -- Change user password
flush privileges; -- Refresh the permissions
--------------------------------------------------
show databases; -- View all databases
use school; -- Switch database
show tables; -- Look at all the tables in the database
describe student; -- View the structure of the table 

describe student id; -- Look at the fields in the table id Information about
create database westos; -- Create database 

CREATE DATABASE `school`CHARACTER SET utf8 COLLATE utf8_general_ci; -- Create database

Operating the database

SQL The classification of languages

SQL Language can be divided into four categories ： Data query language DQL, Data manipulation language DML, Data definition language DDL, Data control language DCL.

matters needing attention

  1. Learning order ： Operating the database > Operation data sheet > Operational data .
  2. mysql Keywords are not case sensitive .
  3. Single-line comments ：--
  4. Multiline comment ：/* */
  5. The difference between character sets ：utf8_unicode_ci、utf8_general_ci、utf8_general_cs、utf8_bin.

utf8_unicode_ci and utf8_general_ci Alignment 、 There is no real difference in English .

utf8_general_ci Fast proofreading , But the accuracy is a little bit poor .

utf8_unicode_ci High accuracy , But proofreading is a little slow .

If your application has German 、 French or Russian , Be sure to use utf8_unicode_ci. It's usually used utf8_general_ci That's enough .

ci yes case insensitive, namely " Case insensitive ", a and A It will be treated as the same in character judgment ;

bin It's binary , a and A Don't discriminate .

For example, you run ：

SELECT * FROM table WHERE txt = 'a'

So in utf8_bin You can't find txt = 'A' That line , and utf8_general_ci Then you can .

utf8_general_ci  Case insensitive , When you register your user name and email address, you need to use .

utf8_general_cs  Case sensitive , If you use this for your user name and email It's going to be bad

utf8_bin: String each string is compiled and stored with binary data .  Case sensitive , And can store binary content

  1. If the table name or field name is a special character , Need to put back quotes （tab The symbol on the key ）, Such as ：user.

Operating the database

  1. Create database

utf8_unicode_ci and utf8_general_ci Alignment 、 There is no real difference in English .

utf8_general_ci Fast proofreading , But the accuracy is a little bit poor .

utf8_unicode_ci High accuracy , But proofreading is a little slow .

If your application has German 、 French or Russian , Be sure to use utf8_unicode_ci. It's usually used utf8_general_ci That's enough .

ci yes case insensitive, namely " Case insensitive ", a and A It will be treated as the same in character judgment ;

bin It's binary , a and A Don't discriminate .

For example, you run ：

SELECT * FROM table WHERE txt = 'a'

So in utf8_bin You can't find txt = 'A' That line , and utf8_general_ci Then you can .

utf8_general_ci  Case insensitive , When you register your user name and email address, you need to use .

utf8_general_cs  Case sensitive , If you use this for your user name and email It's going to be bad

utf8_bin: String each string is compiled and stored with binary data .  Case sensitive , And can store binary content 

CREATE DATABASE IF NOT EXISTS westos;

CREATE DATABASE IF NOT EXISTS westos1 CHARACTER SET utf8 COLLATE utf8_general_ci;
  1. Delete database 
DROP DATABASE IF EXISTS westos1;
  1. Using a database 
USE school; -- Switch database 

USE `school`; -- If the table name or field name is a special character , Need to put back quotes , Such as ：`school`
  1. view the database 
SHOW DATABASES; -- View all databases
  1. Look at the statement to create the database 
SHOW CREATE DATABASE `school`; -- Look at the statement to create the database 

-- Query results 

CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8 */

data type

The number

type Number of bytes meaning remarks Corresponding java
tinyint 1 Bytes A very small number Integer
smallint 2 Bytes Smaller numbers Integer
mediumint 3 Bytes A medium number Integer
int 4 Bytes Integers Commonly used Long
bigint 8 Bytes A larger number BigInteger
float 4 Bytes Floating point numbers Not commonly used , The precision will be lost Float
double 8 Bytes Floating point numbers Not commonly used , The precision will be lost Double
decimal Floating point numbers in string form In financial calculation BigDecimal

character string

type length meaning remarks Corresponding java
char 0~255 Fixed size string String
varchar 0~65535 Variable string Commonly used String
tinytext 2^8 - 1 Micro text String
text 2^16 - 1 Text string Commonly used , Save large text String

Date time

type Format meaning remarks Corresponding java
date YYYY-MM-DD date Date
time HH:mm:ss Time Time
datetime YYYY-MM-DD HH:mm:ss date + Time Commonly used Timestamp
timestamp Time stamp ,1970.1.1 The number of milliseconds up to now Commonly used Timestamp
year YYYY year Date

Field properties

  1. Unsigned

    1. An unsigned number
    2. Declared that the value of this field cannot be negative
    3. If you add a negative number , You're going to report a mistake
  2. zerofill
    1. 0 fill
    2. Insufficient digits , Front use 0 fill , Such as ：0030
  3. Self increasing
    1. The default is automatically based on the previous record +1, You can customize the starting value and step size
    2. It is usually used to design a unique primary key
  4. Non empty
    1. Set to non empty not null when , If it is not assigned a value and the default value is not set , You're going to report a mistake
    2. null, If you don't fill in the value , The default is null
  5. Default
    1. Set default value , If you don't fill in the value , Will be set to the default value

Operation data sheet

  1. Create table 
-- If the table name or field name is a special character , Need to put back quotes （tab The symbol on the key ）, Such as ：`student`

-- AUTO_INCREMENT Self increasing , Default +1

CREATE TABLE IF NOT EXISTS `student` (

`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT ' Student number ',

`name` VARCHAR(30) NOT NULL DEFAULT ' anonymous ' COMMENT ' full name ',

`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT ' password ',

`sex` VARCHAR(1) NOT NULL DEFAULT ' Woman ' COMMENT ' Gender ',

`birthday` DATE DEFAULT NULL COMMENT ' Date of birth ',

`address` VARCHAR(100) DEFAULT NULL COMMENT ' Address ',

`email` VARCHAR(50) DEFAULT NULL COMMENT ' mailbox ',

PRIMARY KEY (`id`)

) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT ' student ';


-- Format 

CREATE TABLE [IF NOT EXISTS] ` Table name ` (

` Field name ` Column type [ attribute ] [ Indexes ] [ notes ],

......

` Field name ` Column type [ attribute ] [ Indexes ] [ notes ],

PRIMARY KEY (` Field name `)

) [ Table type ] [ Character set ] [ Proofread rules ] [ Table annotation ];
  1. Delete table 
DROP TABLE IF EXISTS `student`;
  1. See the table 
SHOW TABLES; -- View all tables
  1. View table structure 
DESCRIBE `student`;

DESC `student`;
  1. Look at the statement that created the data table 
SHOW CREATE TABLE `student`; -- Look at the statement that created the data table 

-- Query results 

CREATE TABLE `student` (

`id` int(10) NOT NULL AUTO_INCREMENT COMMENT ' Student number ',

`name` varchar(30) NOT NULL DEFAULT ' anonymous ' COMMENT ' full name ',

`pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT ' password ',

`sex` varchar(1) NOT NULL DEFAULT ' Woman ' COMMENT ' Gender ',

`birthday` date DEFAULT NULL COMMENT ' Date of birth ',

`address` varchar(100) DEFAULT NULL COMMENT ' Address ',

`email` varchar(50) DEFAULT NULL COMMENT ' mailbox ',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' student '

Modify table

  1. Modify the name of the table 
-- ALTER TABLE ` The old name of the table ` RENAME AS ` The new name of the table `;

ALTER TABLE `student` RENAME AS `student1`;
  1. Add fields 
-- ALTER TABLE ` Table name ` ADD ` Field name ` data type [ constraint ] [ notes ];

ALTER TABLE `student1` ADD `phone` INT(11) DEFAULT NULL COMMENT ' Telephone ';
  1. Modify the field name and field type 、 constraint 、 notes , Be careful ： Field name field type is required , Other options .
-- ALTER TABLE ` Table name ` CHANGE ` Old field name ` ` new field name ` Field type [ constraint ] [ notes ];

ALTER TABLE `student1` CHANGE `phone` `tel` VARCHAR(11) NOT NULL DEFAULT '' COMMENT ' Contact information ';
  1. Modify field type 、 constraint 、 notes , Be careful ： Field type is required , Other options , Field name cannot be modified .
-- ALTER TABLE ` Table name ` MODIFY ` Field name ` Field type [ constraint ] [ notes ];

ALTER TABLE `student1` MODIFY `tel` INT(11) DEFAULT NULL COMMENT ' Telephone ';
  1. Delete field 
-- ALTER TABLE ` Table name ` DROP ` Field name `;

ALTER TABLE `student1` DROP `tel`;

Database engine

  1. INNODB 5.5 And then use... By default

  2. MYISAM 5.5 Before the default use of

    | | MYISAM | INNODB |

    | --- | --- | --- |

    | Transaction support | I won't support it | Support |

    | Data row locking | I won't support it     （ Table locks ） | Support     （ Row lock ） |

    | Foreign key constraints | I won't support it | Support |

    | Full-text index | Support | I won't support it |

    | The size of the table space | smaller | more , about 2 times |

  3. Routine operation

    1. MYISAM Save space , Faster
    2. INNODB High safety , Transaction processing , Multi table multi-user operation

  4. All database files exist data Under the table of contents , A directory corresponds to a database , The essence is file storage .

  5. mysql The difference between the engine and the physical file

    1. INNODB Corresponding documents

      1. *.frm Database directory
      2. ibdata1 data Under the table of contents
    2. MYISAM The corresponding files are in the database directory
      1. *.frm Table structure definition file
      2. *.MYD Data files （data）
      3. *.MYI Index file （index）

The character set encoding of the data table

  1. mysql The default character set encoding is Latin1, No Chinese support .
  2. There are two ways to set the character set encoding of data table , It's better to use both ways at the same time , Prevent when creating a table , No character set encoding set , Leading to Chinese miscode .
    1. Add on table creation CHARSET=utf8
    2. stay my.ini Configure the default encoding in character-set-server=utf8
CHARSET=utf8
character-set-server=utf8

MySQL Data management

Foreign keys

  1. Create table time , Add foreign key constraints 
-- Create a grade table 

CREATE TABLE IF NOT EXISTS `grade` (

`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'id',

`name` VARCHAR(100) NOT NULL COMMENT ' name ',

PRIMARY KEY (`id`)

) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT ' grade ';
-- Create student table , What's on the student list grade_id Field refers to id Field 

-- Defining foreign keys key

-- Add constraints to this foreign key （ Execute reference （reference））

CREATE TABLE IF NOT EXISTS `student` (

`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'id',

`name` VARCHAR(100) NOT NULL COMMENT ' name ',

`grade_id` INT(10) NOT NULL COMMENT ' grade id',

PRIMARY KEY (`id`),

KEY `FK_grade_id` (`grade_id`),

CONSTRAINT `FK_grade_id` FOREIGN KEY (`grade_id`) REFERENCES `grade` (`id`)

) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT ' student ';
  1. When deleting a table with a foreign key relationship , The referenced table must be deleted first （ From the table ）, Then delete the referenced table （ Main table ）
  2. Add foreign keys 
-- Create a grade table 

CREATE TABLE IF NOT EXISTS `grade` (

`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'id',

`name` VARCHAR(100) NOT NULL COMMENT ' name ',

PRIMARY KEY (`id`)

) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT ' grade ';
-- Create a student table 

CREATE TABLE IF NOT EXISTS `student` (

`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'id',

`name` VARCHAR(100) NOT NULL COMMENT ' name ',

`grade_id` INT(10) NOT NULL COMMENT ' grade id',

PRIMARY KEY (`id`)

) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT ' student ';
-- Add foreign keys 

-- ALTER TABLE ` Table name ` ADD CONSTRAINT `FK_grade_id` FOREIGN KEY (`grade_id`) REFERENCES `grade` (`id`);

ALTER TABLE `student`

ADD CONSTRAINT ` Constraint name ` FOREIGN KEY (` Field name as foreign key `) REFERENCES ` Referenced table name ` (` The referenced field name `);
  1. Physical foreign key , Is a database level foreign key , Not recommended .
  2. Best practices
    1. A database is just a table , Just to store data , Only OK （ data ） And column （ Field ）
    2. Want to use data from multiple tables , Want to use foreign keys , It's just a program

insert data 

-- insert into Table name ( Field 1, Field 2, Field 3) values( value 1, value 2, value 3)

-- The primary key increases automatically , Don't write 

INSERT INTO `grade`(`name`) VALUES(' Freshman ');
-- Insert a piece of data , Partial field 

INSERT INTO `grade`(`name`) VALUES(' Sophomore ');
-- Insert a piece of data , All fields 

INSERT INTO `grade`(`id`,`name`) VALUES(11,' Junior year ');
-- When inserting multiple data ,values Value after , Need to be separated by brackets and commas , Such as ：values(),(),()

-- Insert multiple data , Partial field 

INSERT INTO `grade`(`name`) VALUES(' Senior '),(' Big five '),(' Big six ');
-- Insert multiple data , All data 

INSERT INTO `grade`(`id`,`name`) VALUES(100,' Banner '),(200,' dam '),(300,' Big wine ');

Modifying data 

-- update ` Table name ` set Field name 1= value / Other field values / Variable , Field name 2= value where Conditions 

-- Modify the value of a field 

UPDATE `grade` SET `name`=' Junior one ' WHERE `id`=1;
-- Modify the value of one field to the value of other fields 

UPDATE `grade` SET `name`=`id` WHERE `id`=11;
-- Modify the values of multiple fields 

UPDATE `grade` SET `id`=20,`name`=' waiter ' WHERE `id`=2;

Delete data 

-- delete from ` Table name ` where Conditions 

DELETE FROM `grade` WHERE `id`=1;

Clear the table 

-- truncate table ` Table name `

TRUNCATE TABLE `grade`;

delete and truncate The difference between

  1. truncate Reset auto increment column , The counter goes to zero .
  2. delete The self increasing column does not change ; But after restarting the database , Engine is INNODB Of , Auto increment column will start from 1 Start （ In memory , If you cut off the power, you will lose ）; Engine is MYISAM Of , The self increasing column does not change （ In the file , Will not be lost ）.
  3. truncate It won't affect the business .

where Clause operators

Operator meaning
= be equal to
<> or != It's not equal to
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
between ... and ... In a certain range
When the type is a number , Including head and tail
The type is date when , Including head and tail
The type is datetime when , Time, minutes, seconds , Including head and tail ; Without time, minutes and seconds , The head is not the tail
and And
or or

DQL Data query language

DQL

  1. The core language in database , The most important language .
  2. The most frequently used language .

Simple queries and aliases (as)

-- Query all fields 

SELECT * FROM `grade`;
-- Query the specified field 

SELECT `name` FROM `grade`;
-- Alias AS, Fields and tables can be aliased 

SELECT `name` AS ` name ` FROM `grade`;

Splicing (concat) And weight removal (distinct)

-- function concat(a,b,c) Splicing abc, You can splice strings or field values 

SELECT CONCAT(' name ：',`name`) AS ` name ` FROM `grade`;

SELECT CONCAT(`id`,':',`name`) AS `id: name ` FROM `grade`;
-- keyword distinct duplicate removal 

-- Remove select Duplicate data in the query results , Duplicate data shows only one 

-- To go with heavy general cooperation count Use , It's used to count numbers , because distinct Can't display other fields that don't duplicate 

-- Single field de duplication 

SELECT DISTINCT `name` FROM `grade`;
-- Multiple fields de duplication 

SELECT DISTINCT `id`,`name` FROM `grade`;
-- coordination count Use , It's used to count numbers 

SELECT COUNT(DISTINCT `name`) AS `count` FROM `grade`;

Query function / Calculation expression / System variables

Expressions in the database ： Text value 、 Column 、null、 function 、 Calculation expression 、 System variables ......

-- select expression [from Table name ]
-- Query system version （ function ）

SELECT VERSION();

SELECT VERSION(),`name` FROM `grade`;
-- To calculate （ Calculation expression ）

SELECT 123*3-12 AS ` result `;

SELECT 123*3-12 AS ` result `,`name` FROM `grade`;

-- Number type field calculation , Other types are not allowed 

SELECT `id`/100 FROM `grade`;
-- The default step size for query autoincrement （ Variable ）

-- @a User variables ,@@a System variables 

SELECT @@auto_increment_increment AS ' Self increasing step size ';

SELECT @@auto_increment_increment AS ' Self increasing step size ',`name` FROM `grade`;

where Conditionals

  1. effect ： Retrieve the eligible values in the data .
  2. The search criteria consist of one or more expressions .

Logical operators

Use English letters as much as possible

Operator grammar describe
and    && a and b    a && b Logic and
or
not    ! not a    ! a Logic is not 
-- Query results 95 To 100 The students who scored 

select `name`,`score` from `result` where `score`>=95 and `score`<=100;

select `name`,`score` from `result` where `score` between 95 and 100;
-- It's not 100 The students who scored 

select `name`,`score` from `result` where `score`!=100;

select `name`,`score` from `result` where not `score`=100;

Fuzzy query ： Comparison operator

Operator grammar describe
is null a is null by null
is not null a is not null Not for null
between ... and ... a between b and c
a betwwen 5 and 10		 In a certain range
When the type is a number , Including head and tail
The type is date when , Including head and tail
The type is datetime when , Time, minutes, seconds , Including head and tail ; Without time, minutes and seconds , The head is not the tail
like a like b
a like 'b'
a like '%b'
a like 'b%'
a like '%b%'
a like '_b'
alike 'b'		 a matching b
wildcard , Can only be used in like in % representative 0 To any character ,_ Represents a character
in a in (a1,a2,a3...) a Whether it is in the value in brackets 
-- wildcard , Can only be used in like in % representative 0 To any character ,_ Represents a character
select 'name' like '%me';

select 'name' like 'na%';

select 'name' like '%am%';
select 'name' in ('name','na','me');

League table query join

operation describe
inner join Both tables must match
left join Left table is the main table , All in the left table , There is no supplement in the right table null
right join The right table is the main one , All in the right table , There is no complement in the left table null

Self join 

-- Self join ： You can think of a table as two identical tables 

select a.`id` as ' Father ', b.`id` as ' Son '

from `menu` as a,`menu` as b

where a.`id`=b.`pid`

Sorting and paging 

-- Sort order by

-- Ascending asc

-- Descending desc

order by `number` desc
-- Pagination limit Start subscript , Number of pieces 

-- Starting subscript from 0 Start 

limit 0,5
-- first page limit 0,5

-- The second page limit 5,5

-- The third page limit 10,5

-- The first n page limit (n-1) * pageSize, pageSize

-- n: The current page pageSize: Number of pieces

Grouping and filtering 

group by grouping 

having Filter

Subqueries and nested queries 

-- Subquery 

select id,name from student

where grade_id=(select id from grade where name=' Sophomore ')

MySQL function

Common functions 

-- Mathematical operations 

SELECT ABS(-8); -- The absolute value 

SELECT CEILING(9.4); -- Rounding up 

SELECT FLOOR(9.4); -- Rounding down 

SELECT RAND(); -- Return to one 0 To 1 The random number 

SELECT SIGN(-10); -- Judge the sign of a number
-- String function 

SELECT CHAR_LENGTH(' Walking on the road ...'); -- Return string length , In characters 

SELECT LENGTH(' Walking on the road ...'); -- Return string length , In bytes 

SELECT CONCAT(' go ',' stay ',' road ',' On '); -- String concatenation 

SELECT INSERT(' I'm on the road ',1,2,' how are you ') ; -- Insert replace , Replace the specified length from a position 

SELECT LOWER('Hello'); -- Turn lowercase 

SELECT UPPER('hello'); -- Turn capitalization 

SELECT INSTR('helloworld','l'); -- Returns the index of the first occurrence of the substring 

SELECT REPLACE('hello','ll','ss'); -- Replace the specified string that appears 

SELECT SUBSTR('helloworld',2,5); -- Returns the specified substring ( The source string , Location of interception , Intercept length )
-- Time and date functions 

SELECT CURDATE(); -- Get current date 

SELECT CURTIME(); -- Get the current time 

SELECT NOW(); -- Get the current date and time 

SELECT SYSDATE(); -- Get system time
SELECT YEAR(NOW());

SELECT MONTH(NOW());

SELECT DAY(NOW());

SELECT HOUR(NOW());

SELECT MINUTE(NOW());

SELECT SECOND(NOW());
-- System 

SELECT SYSTEM_USER(); -- Query current user 

SELECT USER(); -- Query current user 

SELECT VERSION(); -- Query version

Aggregate functions

The name of the function describe
count() Count
sum() Sum up
avg() Average
max() Maximum
min() minimum value
...... 
-- Aggregate functions 

SELECT COUNT(`name`) FROM student; -- count( Field ), Will ignore all null value , When the field is a primary key , Highest efficiency 

SELECT COUNT(*) FROM student; -- count(*), Will not ignore null value , The essence is to count the number of rows 

SELECT COUNT(1) FROM student; -- count(1), Will not ignore null value , The essence is to count the number of rows

database-level MD5 encryption

  1. What is? MD5?

  1. 1996 Years later, the algorithm proved to be weak , Can be cracked , For data requiring high security , Experts generally recommend using other algorithms , Such as SHA-2.2004 year , confirmed MD5 Algorithms can't prevent collisions （collision）, So it doesn't apply to security certification , Such as SSL Public key authentication or digital signature Such uses .
  2. MD5 from MD4、MD3、MD2 modified , It mainly enhances the complexity and irreversibility of the algorithm .MD5 Because of its universality 、 Stable 、 Fast features , Still widely used in the field of encryption and protection of ordinary data .
  3. MD5 Irreversible , The encrypted value of two identical values is the same .
  4. MD5 How to crack websites , There is a dictionary behind it , according to MD5 The encrypted value looks for the value before encryption , Only a simple password can be found .
-- test MD5 encryption 

CREATE TABLE `testmd5`(

`id` INT(4) NOT NULL,

`name` VARCHAR(100) NOT NULL,

`pwd` VARCHAR(50) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
-- Plaintext encryption 

INSERT INTO `testmd5` VALUES(1,'zhangs','123456'),(2,'lis','123456'),(3,'wangw','123456');
-- MD5 encryption 

INSERT INTO `testmd5` VALUES(4,'zhaol',MD5('123456')),(5,'maq',MD5('123456'));
-- Check the password 

SELECT * FROM `testmd5` WHERE `name`='zhaol' AND `pwd`=MD5('123456');

Business

What is business

  1. Either they all succeed , Or they all failed .
  2. A set of SQL Put it in a batch to execute .

Business principles ACID principle

  1. Atomicity ： Multiple steps either work together , Or fail together , Not just one .（ Advance and retreat at the same time ）
  2. Uniformity ： Final consistency , The sum before the beginning is equal to the sum after the end .（ Conservation of energy ）
  3. Isolation, ： For multiple users at the same time , Other transactions will not affect this transaction .（ Mutual interference ）
  4. persistence ： The transaction was not committed , Return to the original state , Once the transaction is committed , It can't be changed .（ Irreversible ）

Problems caused by isolation

  1. Dirty reading ： One transaction reads uncommitted data from another transaction .
  2. It can't be read repeatedly ： Read a row of data in a table within a transaction , Multiple reads result is different .
  3. Idle away （ Fantasy reading ）： Data inserted by other transactions is read in one transaction , Leading to inconsistent reading before and after .

Handle transactions manually 

-- Business
/*

mysql Automatic transaction commit is on by default 

set autocommit=0; -- close 

set autocommit=1; -- Turn on （ Default ）

*/
-- Handle transactions manually 

SET autocommit=0; -- Turn off auto submit
-- The transaction open 

START TRANSACTION -- Mark the beginning of a transaction , After that SQL All in the same transaction
INSERT xx;

INSERT xx;
-- Submit commit

COMMIT;
-- Roll back rollback

ROLLBACK;
-- End of transaction 

SET autocommit=1; -- Turn on auto submit
-- Understanding can 

SAVEPOINT Save it -- Set a save point for a transaction 

ROLLBACK TO SAVEPOINT Save it -- Roll back to savepoint 

RELEASE SAVEPOINT Save it -- Undo savepoint

Analog transfer 

-- Transfer accounts 

CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci;

USE shop;

CREATE TABLE `account`(

`id` INT(3) NOT NULL AUTO_INCREMENT,

`name` VARCHAR(30) NOT NULL,

`money` DECIMAL(9,2) NOT NULL,

PRIMARY KEY (`id`)

)ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
INSERT INTO `account`(`name`,`money`) VALUES('A','2000'),('B','10000');
-- Analog transfer ： Business 

SET autocommit=0; -- Turn off auto submit 

START TRANSACTION -- Start a transaction 

UPDATE `account` SET `money`=`money`-500 WHERE `name`='A'; -- A reduce 500

UPDATE `account` SET `money`=`money`+500 WHERE `name`='B'; -- B Add 500
COMMIT; -- Submit 

ROLLBACK; -- Roll back
SET autocommit=1; -- Turn on auto submit
SELECT @@autocommit; -- Query auto submit status

Indexes

MySQL The official definition of index is ： Indexes （Index） Help MySQL Data structure for efficient data acquisition . Extract the sentence trunk , We can get the essence of index ： An index is a data structure .

Classification of indexes

  1. primary key （PRIMARY KEY）

    1. The only sign , Value cannot be repeated , There can only be one column as the primary key
  2. unique index （UNIQUE KEY）
    1. Avoid duplicate columns , Value cannot be repeated , Multiple columns can be identified as unique indexes
  3. General index （KEY/INDEX）
    1. default ,index or key Keyword to set
  4. Full-text index （FullText）
    1. Only under the specific database engine , Before MyISAM Support , Now? InnoDB And supporting , Specific self-examination
    2. Quickly locate data

Index creation 

-- Show all indexes 

SHOW INDEX FROM `student`;
-- Index creation 

-- 1. Add indexes to fields when creating tables 

-- 2. After creation ,alter Add index 

-- 3. After creation ,create Add index
-- Index naming ：id_ Table name _ Field name 

-- Add index ：create index Index name on Table name ( Field name );

CREATE INDEX `id_user_name` ON `user` (`name`); -- 15.772 sec
-- Add a full text index Index name ( Name )

ALTER TABLE `student` ADD FULLTEXT INDEX `studentName` (`studentName`);
-- EXPLAIN analysis SQL The state of implementation 

EXPLAIN SELECT * FROM `student`; -- Non full text index 

EXPLAIN SELECT * FROM `student` WHERE MATCH(`studentName`) AGAINST(' Liu '); -- Full-text index

Test index

Insert 100 All the data 

CREATE TABLE `user`(

`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,

`name` VARCHAR(50) NOT NULL,

`phone` VARCHAR(20) NOT NULL,

`pwd` VARCHAR(50) NOT NULL,

PRIMARY KEY (`id`)

)ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
-- Insert 100 All the data 

DELIMITER $$ -- Before you write a function, you have to write , sign
CREATE FUNCTION mock_data()

RETURNS INT

BEGIN

DECLARE num INT DEFAULT 1000000;

DECLARE i INT DEFAULT 0;
WHILE i<num DO

-- Insert statement 

INSERT INTO `user`(`name`,`phone`,`pwd`) VALUES(CONCAT(' user ',i),CONCAT('18',FLOOR(RAND()*1000000000)),UUID());

SET i=i+1;

END WHILE;

RETURN i;

END;
SELECT mock_data();
SELECT COUNT(`id`) FROM `user`;

Analyze query statements 

SELECT * FROM `user` WHERE `name`=' user 9999'; -- 0.748 sec

SELECT * FROM `user` WHERE `name`=' user 9999'; -- 0.689 sec

SELECT * FROM `user` WHERE `name`=' user 9999'; -- 0.765 sec
EXPLAIN SELECT * FROM `user` WHERE `name`=' user 9999';
-- Index naming ：id_ Table name _ Field name 

-- Add index ：create index Index name on Table name ( Field name );

CREATE INDEX `id_user_name` ON `user` (`name`); -- 15.772 sec
SELECT * FROM `user` WHERE `name`=' user 9999'; -- 0.052 sec
SHOW INDEX FROM `user`;
ALTER TABLE `user` DROP INDEX `id_user_name`;

Query before creating index

Query after creating index

Conclusion

Index in a small amount of data , Not very useful , But when there's a lot of data , The difference is obvious .

Indexing principles

  1. More indexes is not better
  2. Don't index tables with constantly changing data
  3. A table with a small amount of data does not need to be indexed
  4. The index is usually added to the fields commonly used for query

The data structure of the index

  1. InnoDB The default index data structure for is Btree
  2. Hash

Rights management and backup

User management

  1. User table ：mysql.user
  2. The essence of user management is to add, delete, modify and query user table data 
-- Create user CREATE USER user name IDENTIFIED BY ' password ';

CREATE USER qing IDENTIFIED BY '123456';
-- Change the current user password 

SET PASSWORD=PASSWORD('123456');
-- Change the specified user password 

SET PASSWORD FOR qing=PASSWORD('123456');
-- rename RENAME USER Original user name TO A new user name ;

RENAME USER qing TO qing2;
-- User authorization GRANT ALL PRIVILEGES ON library . surface TO user name ;

-- ALL PRIVILEGES In addition to giving authorization to others 

-- *.* All libraries and tables 

GRANT ALL PRIVILEGES ON *.* TO qing2;
-- Query authority 

SHOW GRANTS FOR qing2; -- View the permissions for the specified user 

-- qing2 jurisdiction GRANT ALL PRIVILEGES ON *.* TO 'qing2'@'%'

SHOW GRANTS FOR root@'%';

-- root jurisdiction GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION
-- Revoke authority REVOKE ALL PRIVILEGES ON library . surface FROM user name ;

REVOKE ALL PRIVILEGES ON *.* FROM qing2;
-- Delete user 

DROP USER qing2;

MySQL Backup

  1. Why backup ？

    1. Ensure that important data is not lost
    2. Data transfer
  2. MySQL The way of database backup
    1. Copy physical files directly data Catalog
    2. Use the export function in the visualization tool
    3. Use the command line to export mysqldump
# Export table 

# mysqldump -h IP -u user name -p password database Table name > Disk character / file name 

C:\Users\Administrator>mysqldump -hlocalhost -uroot -p123456 school student >d:/a.sql

mysqldump: [Warning] Using a password on the command line interface can be insecure.
# Export multiple tables 

mysqldump -h192.168.10.226 -uroot -p123456 school grade >d:/a.sql
# Export Library 

mysqldump -h192.168.10.226 -uroot -p123456 school >d:/a.sql
# Import 

# In case of login , Switch to the specified database 

# source Backup file 

source d:/a.sql
D:\>mysql -uroot -p123456

mysql: [Warning] Using a password on the command line interface can be insecure.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use school;

Database changed

mysql> source d:/a.sql;

Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
# perhaps mysql -u user name -p password Library name < Backup file

Standardize database design

Why design is needed

  1. When the database is complex , We need to design
  2. Bad database design
    1. data redundancy , Waste space
    2. Database insertion and deletion are troublesome 、 abnormal （ Shielding uses physical foreign keys ）
    3. The performance of the program is poor
  3. Good database design
    1. Save memory space
    2. Ensure the integrity of the database
    3. It's convenient for us to develop the system
  4. Software development , About the design of database
    1. Analyze requirements ： Analyze the business and the database requirements that need to be handled
    2. Outline design ： Design diagrams E-R chart

The steps to design a database （ Personal blog ）

  1. To collect information , Analyze requirements

    1. User table （ User login and logout , User's personal information , Blogging , Create classification ）
    2. Classification table （ The article classification , Who created ）
    3. Article table （ The article information ）
    4. Friend list （ Friend chain information ）
    5. Custom table （ system information , keyword , Main field ）
  2. Identifying entities （ Put the requirements into specific tables 、 Field ）

Three paradigms

First normal form （1NF）- Atomicity

Atomicity ： Make sure that each column is no longer divisible

Second normal form （2NF）

Premise ： Satisfy the first paradigm
Each table only describes one thing

Third normal form （3NF）

Premise ： Satisfy the first paradigm and the second paradigm
Make sure that every column in the data table is directly related to the primary key , Not indirectly .

Normative and performance issues

The associated query cannot have more than three tables

  1. Consider the needs and goals of commercialization （ cost , User experience ）, Database performance is more important
  2. When it comes to performance , We need to think about the normative properly
  3. Deliberately add some redundant fields to some tables （ From multi table Association query to single table query ）
  4. Deliberately add some statistical Columns （ From the statistics of large amount of data to the query of small amount of data ）

JDBC

Database driven

The program is driven by the database , Dealing with databases .

JDBC

  1. SUN Company in order to simplify the unified operation of the database developers , Provides a Java Specification of operating database , Be commonly called JDBC.
  2. These specifications are implemented by specific manufacturers .
  3. For developers , We just need to master JDBC Interface operation can be .

relevant jar package

  1. java.sql
  2. javax.sql
  3. Database driver package mysql-connector-java-5.1.49.jar

first JDBC Program

  1. Create test database .
CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;
USE jdbcStudy;
CREATE TABLE users(

`id` INT(10) NOT NULL,

`name` VARCHAR(100) NOT NULL,

`pwd` VARCHAR(100) NOT NULL,

`email` VARCHAR(100),

`birthday` DATE,

PRIMARY KEY (`id`)

)ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
INSERT INTO `users`(`id`,`name`,`pwd`,`email`,`birthday`)

VALUES(1,'zhangsf','123456','zhangsf@163.com','1988-03-13'),

(2,'zhangwj','123456','zhangwj@163.com','1983-04-03'),

(3,'zhangyq','123456','zhangtj@163.com','1989-05-23');
  1. Create a normal project .

  1. Import database driver .

    1. establish lib Catalog , Copy mysql-connector-java-5.1.49.jar
    2. Add to project library , Right click on the Add as Library


  1. Write test code .

package com.qing.demo01;
import java.sql.*;
/**

* first JDBC Program 

*/

public class Demo01 {

public static void main(String[] args) throws ClassNotFoundException, SQLException {

//1. The load driver 

Class.forName("com.mysql.jdbc.Driver");
//2. User information and url

//useUnicode=true Support Chinese coding 

//characterEncoding=utf8 Set the Chinese character set code to utf8

//useSSL=true Using secure connections , If the server doesn't have SSL The certificate will report an error , Use false

String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";

String user = "root";

String password = "123456";
//3. Successful connection , Get database objects Connection

Connection connection = DriverManager.getConnection(url,user,password);
//4. Create execution SQL The object of 

Statement statement = connection.createStatement();
//5. perform SQL, If there is a return result , View return results 

String sql = "SELECT * FROM users";

ResultSet resultSet = statement.executeQuery(sql);

while (resultSet.next()) {

System.out.println("id=" + resultSet.getObject("id"));

System.out.println("name=" + resultSet.getObject("name"));

System.out.println("pwd=" + resultSet.getObject("pwd"));

System.out.println("email=" + resultSet.getObject("email"));

System.out.println("birthday=" + resultSet.getObject("birthday"));

System.out.println("=====================================");

}
//6. Release the connection 

resultSet.close();

statement.close();

connection.close();

}

}

Step summary

  1. The load driver
  2. Connect to database DriverManager
  3. Create execution SQL The object of Statement
  4. Get the returned result set
  5. Release the connection

DriverManager

// Drive management 

//DriverManager.registerDriver(new Driver());

// The load driver , It is recommended to use ,Driver The static code block in the class is the method above executed 

Class.forName("com.mysql.jdbc.Driver");
// Connect to database 

Connection connection = DriverManager.getConnection(url,user,password);

//Connection Represents a database object , Can perform database level operations 

// Database settings auto commit 

connection.setAutoCommit(false);

connection.setAutoCommit(true);

// Transaction submission 

connection.commit();

// Transaction rollback 

connection.rollback();

URL

// Database connection information 

String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";

//String url = "jdbc:mysql:// The host address : Port number / Database name ? Parameters 1& Parameters 2& Parameters 3";

Statement

// perform SQL The object of 

boolean execute = statement.execute("");// Carry out any SQL, But it needs judgment , Minimum efficiency 

ResultSet resultSet = statement.executeQuery();// Execute the query , Returns the query result set 

int i = statement.executeUpdate();// Perform the update 、 Insert 、 Delete , Returns the number of affected rows

Insert examples

Delete example

Update example

Query examples

Release resources 

resultSet.close();

statement.close();

connection.close();// Resource consumption , Turn off after use

First optimization , Extract tool class

  1. establish db.properties
driver=com.mysql.jdbc.Driver

url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false

user=root

password=123456
  1. Extract tool class JdbcUtils： The load driver , Get the connection , Release connection resources
  2. Test addition, deletion, modification, check 
package com.qing.demo02.utils;
import java.io.IOException;

import java.io.InputStream;

import java.sql.*;

import java.util.Properties;
/**

* JDBC Tool class 

*/

public class JdbcUtils {
private static String driver = null;

private static String url = null;

private static String user = null;

private static String password = null;
static {

try {

InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");

Properties properties = new Properties();

properties.load(in);
driver = properties.getProperty("driver");

url = properties.getProperty("url");

user = properties.getProperty("user");

password = properties.getProperty("password");
//1. The driver only needs to be loaded once 

Class.forName(driver);

} catch (Exception e) {

e.printStackTrace();

}

}
// Get the connection 

public static Connection getConnection() throws SQLException {

return DriverManager.getConnection(url,user,password);

}
// Release connection resources 

public static void release(Connection connection, Statement statement, ResultSet resultSet) {

if (resultSet != null) {

try {

resultSet.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

if (statement != null) {

try {

statement.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

if (connection != null) {

try {

connection.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

}


package com.qing.demo02;
import com.qing.demo02.utils.JdbcUtils;
import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;
/**

* Test insert 

*/

public class TestInsert {

public static void main(String[] args) {

Connection conn = null;

Statement st = null;

ResultSet rs = null;
try {

// Get the connection 

conn = JdbcUtils.getConnection();

// Access to perform SQL The object of 

st = conn.createStatement();

// perform SQL

String sql = "insert into `users`(`id`,`name`,`pwd`,`email`,`birthday`) values(4,'zhangyq','123456','zhangyq2163.com','1988-09-09')";

int i = st.executeUpdate(sql);

if (i > 0) {

System.out.println(" Insert the success ！");

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

// Release connection resources 

JdbcUtils.release(conn,st,rs);

}

}

}


package com.qing.demo02;
import com.qing.demo02.utils.JdbcUtils;
import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;
/**

* Test update 

*/

public class TestUpdate {

public static void main(String[] args) {

Connection conn = null;

Statement st = null;

ResultSet rs = null;
try {

// Get the connection 

conn = JdbcUtils.getConnection();

// Access to perform SQL The object of 

st = conn.createStatement();

// perform SQL

String sql = "update `users` set `name`='zhangtj' where `id`=3";

int i = st.executeUpdate(sql);

if (i > 0) {

System.out.println(" The update is successful ！");

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

// Release connection resources 

JdbcUtils.release(conn,st,rs);

}

}

}


package com.qing.demo02;
import com.qing.demo02.utils.JdbcUtils;
import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;
/**

* Test to delete 

*/

public class TestDelete {

public static void main(String[] args) {

Connection conn = null;

Statement st = null;

ResultSet rs = null;
try {

// Get the connection 

conn = JdbcUtils.getConnection();

// Access to perform SQL The object of 

st = conn.createStatement();

// perform SQL

String sql = "delete from `users` where `id`=4";

int i = st.executeUpdate(sql);

if (i > 0) {

System.out.println(" Delete successful ！");

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

// Release connection resources 

JdbcUtils.release(conn,st,rs);

}

}

}


package com.qing.demo02;
import com.qing.demo02.utils.JdbcUtils;
import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;
/**

* Test the query 

*/

public class TestSelect {

public static void main(String[] args) {

Connection conn = null;

Statement st = null;

ResultSet rs = null;
try {

// Get the connection 

conn = JdbcUtils.getConnection();

// Access to perform SQL The object of 

st = conn.createStatement();

// perform SQL

String sql = "select * from `users` where `id`=3";

rs = st.executeQuery(sql);

while (rs.next()) {

System.out.println(rs.getString("name"));

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

// Release connection resources 

JdbcUtils.release(conn,st,rs);

}

}

}

SQL Injection problem 

// perform SQL

String id = "3 or 1=1";

String sql = "select * from `users` where `id`=" + id;

rs = st.executeQuery(sql);


String sql = "select * from user_table where username=

' "+userName+" ' and password=' "+password+" '";
-- When you enter the user name and password above , above SQL Statement becomes ：

SELECT * FROM user_table WHERE username=

'’or 1 = 1 -- and password='’
"""

-- analysis SQL sentence ：

-- After the condition username=”or 1=1 The user name is equal to ” or 1=1 Then this condition is bound to succeed ;
-- And then add two -, This means annotating , It annotates the following statements , Let them not work , This sentence will always be -- Can execute correctly , Users easily cheat the system , Get legal status .

-- It's more gentle , If it's execution 

SELECT * FROM user_table WHERE

username='' ;DROP DATABASE (DB Name) --' and password=''

-- The consequences can be imagined …

"""

PreparedStatement

  1. PreparedStatement Can prevent SQL Inject , And more efficient .

Why? PreparedStatement Can prevent sql Injection? ？

because sql Statement is precompiled , and A placeholder is used in the statement , Specifies the sql Structure of statement . Users can set "?" Value , But it can't change sql Structure of statement , So I want to sql The sentence is followed by “or 1=1” Realization sql Injection doesn't work .

In development , It is generally used PreparedStatement Access database , It can not only prevent sql Inject , Or precompiled ( Recompile the whole... Without changing the parameters once sql sentence , Efficient ), Besides , The result set obtained by executing the query statement is offline , When the connection is closed , You can still access the result set .

package com.qing.demo03;
import com.qing.demo02.utils.JdbcUtils;
import java.sql.*;
/**

* Test the query 

*/

public class TestSelect {

public static void main(String[] args) {

Connection conn = null;

PreparedStatement st = null;

ResultSet rs = null;
try {

// Get the connection 

conn = JdbcUtils.getConnection();

// Use ? Instead of parameters 

String sql = "select * from `users` where `id`=?";

// precompile , First write SQL, Then don't execute 

st = conn.prepareStatement(sql);

// Assign parameters manually 

st.setString(1,"3");

// perform 

rs = st.executeQuery();

while (rs.next()) {

System.out.println(rs.getString("name"));

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

// Release connection resources 

JdbcUtils.release(conn,st,rs);

}

}

}

Use IDEA Connect to database

  1. Connect mysql;

  1. choice mysql drive ;

  1. Fill in mysql user name , password , Test connection ;

  1. Select database ;

  1. Double click on the data table , Open the table ; Modifying data , Submit ; Open the command line , Write SQL;

JDBC Operational transaction

  1. Open transaction ： Turn off database auto commit , Automatically opens the transaction .
  2. A set of business execution completed , Commit transaction .
  3. Can be in catch Statement explicitly defines the rollback statement , Or not , By default, failure rolls back .
  4. finally Turn on automatic database submission in , Then release the connection resources .
package com.qing.demo04;
import com.qing.demo02.utils.JdbcUtils;
import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.SQLException;
/**

* Test transactions 

*/

public class TestTransaction {

public static void main(String[] args) {

Connection conn = null;

PreparedStatement st = null;
try {

conn = JdbcUtils.getConnection();

// Open transaction ： Turn off database auto commit , The transaction will be opened automatically 

conn.setAutoCommit(false);
String sql1 = "update `account` set `money`=`money`-100 where `name`='A'";

st = conn.prepareStatement(sql1);

st.executeUpdate();

String sql2 = "update `account` set `money`=`money`+100 where `name`='B'";

st = conn.prepareStatement(sql2);

st.executeUpdate();
// Commit transaction 

conn.commit();

System.out.println(" success ！");

} catch (SQLException e) {

// Explicit rollback , Or not , Default failure auto rollback 

// try {

// conn.rollback();

// } catch (SQLException e1) {

// e1.printStackTrace();

// }

e.printStackTrace();

} finally {

JdbcUtils.release(conn,st,null);

}

}

}

Database connection pool

  1. Database connection --》 completion of enforcement --》 Release the connection
  2. Connect -- Release , It's a waste of system resources
  3. Pool technology ： Prepare some resources in advance , Come and connect with the prepared
  4. Minimum connections
  5. maximum connection
  6. Waiting for timeout
  7. Write connection pools , Implement an interface DataSource

Common connection pools

After using these database connection pools , We don't need to write the code to connect to the database in the project development ！

  1. DBCP

    1. jar package ：commons-dbcp,commons-pool
    2. The configuration file ：dbcpconfig.properties
    3. Factory mode creates data sources ：DataSource dataSource = BasicDataSourceFactory.creatDataSource(properties);
    4. Get the connection from the data source ：dataSource.getConnection();
  2. C3P0
    1. jar package ：c3p0,mchange-commons-java
    2. The configuration file ：c3p0-config.xml
    3. create data source ：DataSource dataSource = new ComboPooledDataSource("MySQL");// The default data source is not to write parameters , To write a parameter is to specify the data source
    4. Get the connection from the data source ：dataSource.getConnection();
  3. Druid： Alibaba

Random recommendation

  1. Python The way 【 Chapter 7 】python Basics And socket Network programming

    Most of this article draws lessons from http://www.cnblogs.com/nulige/p/6235531.html python socket   Network programming One . Server and client BS framework ( Tencent software :ser ...

  2. Ubuntu utilize xinetd Limit SSH The number of connections

    install xinted sudo apt-get install xinetd Modify the configuration file sudo vim /etc/xinetd.conf Add the following configuration to the configuration file defaults { insta ...

  3. HDU 1728 Escape the maze

    [ Title Description - Problem Description] Given a m × n (m That's ok , n Column ) The maze of , There are two places in the maze ,gloria Want to go from one place in the maze to another , Of course, some places in the maze are empty spaces ,glo ...

  4. ios Modify the navigation bar return button

    ios Modify the navigation bar return button Mode one : Using the system's : You can change the text of the system : And by setting the color of the navigation bar to achieve the desired effect UIBarButtonItem *backBtns = [[UIBarButtonItem ...

  5. URAL 1988 - Planet Ocean Landing【 The geometric &amp; Third answer 】

    [ The question ] On a planet ( It's a sphere ) There's a plane on the surface ( coordinate (x1,y1,z1), The origin is the center of the planet ), There's a space station in the air ( coordinate (x2,y2,z2)), All values are less than 100, Now it's time for the plane to meet the space station , The speed of the plane is ...

  6. from Profile Peep in the middle Unity Memory management for

    warren U3D--- from Profile Peep in the middle Unity Memory management for What does this article contain This article is from Unity Of Profile Start with components , Let's talk about Unity Some differences in memory usage between development and formal environments , ...

  7. Javascript Array operation method

    1.shift: Delete the first item of the original array , And returns the value of the deleted element : Returns if the array is empty undefined var a = [1,2,3,4,5]; var b = a.shift(); //a:[2,3,4,5] ...

  8. Centos7 How to change under the system apache Default site directory

    When we're in Centos7 In the configuration Apache when , Find out apache The default resolution directory is /var/www/html, That is, when accessing the server IP Or local localhost when , The default location is in this directory ind ...

  9. Run batch file how not to display DOS Command window

    Run batch file how not to display DOS Command window   BAT It's impossible not to show DOS window . You can consider using scripts to keep the following to a text file , Rename it to AutoUp_ddyy.vbs set WshShell = WScript.Cre ...

  10. Jmeter stay linux Distributed pressure measurement under the condition of

    Jmeter stay linux Distributed pressure measurement under the condition of 0. take windows The machine acts as master Control machine ( At the same time, it also acts as a load machine slave), linux The machine acts as Load machine slave. 1.linux Environmental installation : (1 ...