1. Initial MySql

1.1 why learn database?

1. Job skill demand

2. In today's world, those who get data get the world

3. Method of storing data

4. How to keep a large amount of data in programs and websites for a long time?

5. Database is almost the core of software system.

1.2. What is a database?

DataBase (DB for short)

Concept: it is an organized and shareable collection of large amounts of data stored in the computer for a long time. It is a data "warehouse"

Function: save and safely manage data (such as addition, deletion, modification, query, etc.) to reduce redundancy

Database overview:

• Relational database (SQL)

  • MySQL , Oracle , SQL Server , SQLite , DB2 , ...
  • Relational database establishes the relationship between tables through foreign key Association

• Non relational database (NOSQL)

  • Redis , MongoDB , ...

  • Non relational database usually means that data is stored in the database in the form of objects, and the relationship between objects is determined by the attributes of each object

1.3. What is DBMS?

DataBase Management System

Database management software can scientifically organize and store data, and efficiently obtain and maintain data

Why do you say this?

Because MySQL we want to learn should be regarded as a database management system

1.4 introduction to MySQL

Concept: it is a popular, open source, free relational database

History: developed by Swedish MySQL AB company, it is currently a product of Oracle.

characteristic:

• Free, open source database
• Compact and fully functional
• Easy to use
• It can run on Windows or Linux operating system
• It can be applied to small and medium-sized or even large website applications

Official website: https://www.mysql.com/

course: https://www.runoob.com/mysql/mysql-tutorial.html

1.5. Installing MySQL

It is recommended to use the compressed version, which is fast, convenient and uncomplicated

Software download

mysql5.7 64 bit download address:

https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19-winx64.zip

If the computer is 64 bit, download and use the 64 bit version!

Installation steps

1. After downloading, get the zip package

2. Unzip to the directory where you want to install

3. Add environment variables: my computer - > properties - > Advanced - > environment variables

choice PATH,Add after it: Yours mysql Under the installation file bin folder

4. Edit my INI file, pay attention to the replacement path location

[mysqld]
#Backslash in windows/
basedir=D:/Program Files/mysql-5.7

datadir=D:/Program Files/mysql-5.7/data

port=3306

# Maximum connections allowed
# max_connections=1000

#Skip password verification. Comment it out after modifying the password
skip-grant-tables

5. Start CMD in administrator mode, switch the path to bin directory under mysql, and then enter mysqld – install

6. Then enter mysqld -- initialize secure -- user = Mysql to initialize the data file

7. Then start MySQL again: Net start MySQL

Then use the command mysql – u root – p to enter the mysql management interface (the password can be blank). P cannot be followed by a space

8. Change the root password after entering the interface

update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';

9. Refresh permissions

flush privileges;

10. Modify my INI file, delete the last skip grant tables

#Cancel skip password verification. You can enter only after you have a new password
#skip-grant-tables

11. Restart mysql to work normally

net stop mysql

net start mysql

12. After the test on the connection shows the following results, it is installed

1.6 visualization tool SQLyog

1.7 common commands

Open MySQL command window

• Enter the installation directory \ mysql\bin in the DOS command line window
• Set the path environment variable, which can be opened in any directory!

-- Single-Line Comments 
/*
multiline comment 
*/

--Connect to the database. p There must be no spaces between and password
mysql -h Server host address -u user name -p User password

--Change Password
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';

--Refresh permission: operation after modifying password
flush privileges;

--Show all databases
show databases; 

--Open a database
use dbname；

--show database mysql All tables in
show tables; 

--Display table user Column information for
describe user; 

--Create database
create database name; 

--Select database
use databasename; 

--sign out Mysql
exit; 

--Command keywords : ask for help
? 

2. Operation database

2.1 classification of common sentences

name	abbreviation	explain	command
Database definition language	DDL	Define and manage data objects, such as databases, tables, etc	CREATE,DROP,ALTER
Database operation language	DML	Used to manipulate data in database objects	INSERT,UPDATE,DELETE
Database query language	DQL	Query database data	SELECT
Database control language	DCL	The language used to manage the database, including rights management and data changes	GRANT,COMMIT,ROLLBACK

D (database): Database

D (definition): definition

L (language): language

M (management): management

Q (quary): query

C (control): control

Commands are not case sensitive. It is recommended to add ` ` sign on both sides of database name, table name and field name (above Tab key). If it is not a keyword, it can be omitted

2.2. Database operation

--Create database
create database [if not exists] `Database name`;

--Delete database
drop database [if exists] `Database name`;

--view the database
show databases;

--Use database
use `Database name`;

2.3 column data type

• value type

type	size	Range (signed)	Range (unsigned)	purpose
TINYINT	1 byte	(-128，127)	(0，255)	Small integer value
SMALLINT	2 bytes	(-32 768，32 767)	(0，65 535)	Large integer value
MEDIUMINT	3 bytes	(-8 388 608，8 388 607)	(0，16 777 215)	Large integer value
INT or INTEGER	4 bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	Large integer value
BIGINT	8 bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	Maximum integer value
FLOAT	4 bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	Single precision floating point value
DOUBLE	8 bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	Double precision floating point value
DECIMAL	For DECIMAL(M,D), if M > D, it is M+2, otherwise it is D+2	Values dependent on M and D	Values dependent on M and D	Small value

DECIMAL: mostly used in Finance

The MySQL DECIMAL data type is used to store precise values in the database. We often use the DECIMAL data type for columns that retain accuracy, such as currency data in the accounting system.

To define a column whose data type is DECIMAL, use the following syntax:

`column_name`  `DECIMAL`(P,D);

In the syntax above:

• P is the precision of the significant number. P ranges from 1 to 65.
• D is the number of digits after the decimal point. The range of D is 0 ~ 30. MySQL requires d to be less than or equal to (< =) P.

DECIMAL(P, D) indicates that the column can store P digits of D decimal places. The actual range of decimal columns depends on precision and scale.

Like the INT data type, the DECIMAL type has the UNSIGNED and ZEROFILL properties. If the UNSIGNED property is used, the DECIMAL UNSIGNED column will not accept negative values.

If ZEROFILL is used, MySQL will fill the display value to 0 to display the width specified by the column definition. In addition, if we use ZERO FILL on the DECIMAL column, MySQL will automatically add the UNSIGNED attribute to the column.

• Character type

type	size	purpose
CHAR	0-255 bytes	Fixed length string
VARCHAR	0-65535 bytes	Variable length string
TINYBLOB	0-255 bytes	Binary string up to 255 characters
TINYTEXT	0-255 bytes	Short text string
BLOB	0-65 535 bytes	Long text data in binary form
TEXT	0-65 535 bytes	Long text data
MEDIUMBLOB	0-16 777 215 bytes	Medium length text data in binary form
MEDIUMTEXT	0-16 777 215 bytes	Medium length text data
LONGBLOB	0-4 294 967 295 bytes	Maximum text data in binary form
LONGTEXT	0-4 294 967 295 bytes	Maximum text data

• Date and time type

type	Size (bytes)	Range	format	purpose
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	Date value
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	Time value or duration
YEAR	1	1901/2155	YYYY	Year value
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	Mixed date and time values
TIMESTAMP	4	1970-01-01 00:00:00/2038 The end time is 2147483647 seconds, 11:14:07, January 19, 2038, Beijing time, 03:14:07 AM GMT, January 19, 2038	YYYYMMDD HHMMSS	Mixed date and time values, timestamp

• NULL value
  • Understood as "no value" or "unknown value"
  • Do not use NULL for arithmetic operation, the result is still NULL

2.4. Data field attribute [ key ]

UnSigned

• Unsigned
• Declare that the data column does not allow negative numbers

ZEROFILL

• 0 filled
• If the number of digits is less than 0, fill it with 0. For example, int(3), 5 is 005; If it is 1250, it is still 1250. 0 is added only when the number of digits is insufficient

Auto_InCrement

• For automatic growth, every time a piece of data is added, 1 will be automatically added to the number of previous records (default)

• It is usually used to set the primary key and is of integer type

• You can define the starting value and step size

  • Current table setting step (AUTO_INCREMENT=100): only the current table is affected

  • SET @@auto_increment_increment=5 ; Affect all tables that use auto increment (global)

NULL and NOT NULL

• The default is NULL, that is, no value of the column is inserted
• If set to NOT NULL, the column must have a value

DEFAULT

• default
• Used to set default values
• For example, the gender field is male by default, otherwise it is female; If no value is specified for this column, the default value is male

2.5. Create database table [ key ]

-- target : Create a school database
-- Create student table(column,field)
-- Student number int Login password varchar(20) full name,Gender varchar(2),date of birth(datatime),Home address,email
-- Before creating a table , Be sure to select the database first

CREATE TABLE IF NOT EXISTS `student` (
	`id` int(4) 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(2) NOT NULL DEFAULT 'male' COMMENT 'Gender',
	`birthday` datetime DEFAULT NULL COMMENT 'birthday',
	`address` varchar(100) DEFAULT NULL COMMENT 'address',
	`email` varchar(50) DEFAULT NULL COMMENT 'mailbox',
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Format: [optional]

CREATE TABLE [IF NOT EXISTS] `Table name`(
	`Field name` Column type [attribute] [Indexes] [notes],
    `Field name` Column type [attribute] [Indexes] [notes],
    ......
    `Field name` Column type [attribute] [Indexes] [notes]
)[Table type][Character set settings][notes]

2.6 data table type (engine)

Engine is the regulation of building table, Provided for table use, Not a database

# mysql> show engines; # Show all engines

# innodb (default engine): supports transactions, row level locks, and foreign keys

# myisam: the query efficiency is better than innodb. When you do not need to support transactions, row level locks and foreign keys, you can set myisam to optimize the database

compare	MYISAM	INNODB = = (default)==
affair	I won't support it	support
Data row locking	I won't support it	support
Foreign key constraint	I won't support it	support
Full text retrieval	support	MySql5.6 not supported before, 5.6 and later
Space occupied	Small	Large, about 2 times

Experience (where applicable):

• Applicable to MyISAM: space saving and response speed
• Applicable to InnoDB: security, transaction processing and multi-user operation data sheet
• Full text indexes can be created only when the data types of fields are char, varchar, text and their series

2.7 data storage location

• MySQL data tables are stored on disk as files

  • Including table files, data files, and database options files

  • Location: store the data table under Mysql installation directory \ data \. The directory name corresponds to the database name, and the file name under the directory corresponds to the data table

• be careful:

  • *. frm - table structure definition file

  • *. MYD – data file (data)

  • *. MYI - index file (index)

  • InnoDB type data table has only one * frm files, and * ibd index file + data file

  • The MyISAM type data table corresponds to three files:

2.8. Set character set

We can set different character sets for database, data table and data column. Setting method:

• When creating, it is set by command, such as: CREATE TABLE table name () CHARSET = utf8;
• If there is no setting, according to the MySQL database configuration file my Parameter setting in ini

2.9. Modify database

1. Modify tables and fields

--Modify table name
ALTER TABLE `Old table name` RENAME AS `New table name`
 
--Add field
ALTER TABLE `Table name` ADD `Field name` Column properties[attribute]

--Modify field(Column type)
ALTER TABLE `Table name` MODIFY `Field name` Column type[attribute]

--Modify field(Rename, column type)
ALTER TABLE `Table name` CHANGE `Old field name` `new field name` Column properties[attribute]

--Delete field
ALTER TABLE `Table name` DROP `Field name`

• Change can change the column name and column type (write the new column name and old column name every time, even if the two column names are not changed, but the type is changed)

• modify can only change column properties. You only need to write the column name once, which is easier than change

2. Delete table

DROP TABLE [IF EXISTS] `Table name`

• IF EXISTS is optional to judge whether the data table exists
• If you delete a nonexistent data table, an error will be thrown

All creation and deletion, try to add judgment

2.10 precautions

• You can wrap the identifier (database name, table name, field name, index, alias) with backquotes to avoid duplicate names with keywords! Chinese can also be used as an identifier!
• Each library directory has an option file DB to save the current database opt
• notes
  • Single line note # note content
  • Single line comment -- comment content (standard SQL comment style, requiring double dashes followed by a space character (space, TAB, line feed, etc.)
  • Multiline comment / * comment content*/
• Pattern wildcard
  • _ Pattern wildcard
  • %Any number of characters, even zero characters
  • Single quotation marks need to be escaped \ '
• The statement terminator in CMD command line can be;, \G. \ g, only affect the display results. Other places still end with semicolons. delimiter to modify the statement terminator of the current conversation

3. MYSQL data management

3.1. Foreign key [ understand ]

Foreign key concept

If a public keyword is the primary keyword in a relationship, the public keyword is called the foreign key of another relationship. Thus, the foreign key represents the correlation between the two relationships. A table with a foreign key of another relationship as the primary key is called the master table, and a table with this foreign key is called the slave table of the master table.

In practice, the value of one table is put into the second table to represent the association. The value used is the primary key value of the first table (including composite primary key value if necessary). At this point, the attribute that holds these values in the second table is called a foreign key.

Foreign bond action

The main purpose of maintaining data consistency and integrity is to control the data and constraints stored in the foreign key table. To associate two tables, foreign keys can only refer to the values of columns in the appearance or use null values.

• Create foreign key

  • Create foreign keys when creating tables

    -- How to create a foreign key : Create child tables and foreign keys
    
    -- Grade table (id\Grade name)
    CREATE TABLE IF EXISTS `grade` (
    	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'grade ID',
    	`gradename` VARCHAR(50) NOT NULL COMMENT 'Grade name',
    	PRIMARY KEY (`gradeid`)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8
    
    -- Student information sheet (Student number,full name,Gender,grade,mobile phone,address,date of birth,mailbox,ID number)
    CREATE TABLE IF EXISTS `student` (
    	`studentno` INT(4) NOT NULL AUTO_INCREMENT COMMENT 'Student number',
    	`studentname` VARCHAR(20) NOT NULL DEFAULT 'anonymous' COMMENT 'full name',
    	`sex` TINYINT(1) DEFAULT '1' COMMENT 'Gender',
    	`gradeid` INT(10) DEFAULT NULL COMMENT 'grade',
    	`phoneNum` VARCHAR(50) NOT NULL COMMENT 'mobile phone',
    	`address` VARCHAR(255) DEFAULT NULL COMMENT 'address',
    	`borndate` DATETIME DEFAULT NULL COMMENT 'birthday',
    	`email` VARCHAR(50) DEFAULT NULL COMMENT 'mailbox',
    	`idCard` VARCHAR(18) DEFAULT NULL COMMENT 'ID number',
    	PRIMARY KEY (`studentno`),
    	KEY `FK_gradeid` (`gradeid`),
    	CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8
    

    

  • Add foreign key after creating table

    -- Create foreign key mode 2 : After creating the sub table, modify the sub table and add foreign keys
    ALTER TABLE `student`
    ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);
    

• Delete foreign key

  -- First step
  -- Delete foreign key
  ALTER TABLE `student` DROP FOREIGN KEY `FK_gradeid`;
  
  -- Step 2
  -- It is found that the above steps have been executed,The index is still there,So you have to delete the index
  -- notes:This index is generated by default when creating foreign keys
  ALTER TABLE `student` DROP INDEX `FK_gradeid`;
  

  be careful:

  • When deleting a table with a primary foreign key relationship, delete the child table first, and then the primary table

  • The foreign keys mentioned above are physical foreign keys, that is, foreign keys at the database level.

  • Foreign key constraints are not recommended in the database. All foreign key concepts are solved in the application layer

    reason: https://www.cnblogs.com/youngdeng/p/12857093.html

3.2. DML database management language [key]

1. Add

--add to  insert
INSERT INTO Table name[(Field 1,Field 2,Field 3,...)] VALUES('Value 1','Value 2','Value 3')

-- Add multiple values at once 
insert into `surface`(`Field 2`)
values ('Value 1'),('Value 2'),('Value 3')

-- The auto increment field can be omitted
insert into `student`(`name`,`sex`) 
values ('sss','male')；

2. Modification

--modify update
UPDATE Table name SET column_name=value [,column_name2=value2,...] [WHERE condition];

3. Delete

--delete delete
DELETE FROM Table name [WHERE condition];

--Empty table
TRUNCATE [TABLE] table_name;

Difference between DELETE and TRUNCATE

• Same: data can be deleted without deleting the table structure, but TRUNCATE is faster

• Different:

  • Reset auto using TRUNCATE TABLE_ Increment counter

  • Using TRUNCATE TABLE will not affect the transaction (the transaction will be described later)

  • Use DELETE to empty database table data of different engines. After restarting the database service

    InnoDB: Auto incrementing column starts from the initial value (because it is stored in memory, power loss occurs when power is off)

    MyISAM: Auto increment column still starts from the last auto increment data (it exists in the file and will not be lost)

4. Data query DQL [super Focus]

4.1. DQL (data query language)

• Query database data, such as SELECT statement
• Simple single table query, or complex query and nested query of multiple tables
• It is the core and most important statement in database language
• Most frequently used statements

4.2. Query statement template

SELECT [ALL | DISTINCT] -- DISTINCT duplicate removal
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
  [left | right | inner join table_name2]  -- Joint query
  [WHERE ...]  -- Specify the conditions to be met for the results
  [GROUP BY ...]  -- Specify which fields the results are grouped by
  [HAVING]  -- Secondary conditions that must be met to filter grouped records
  [ORDER BY ...]  -- Specifies that query records are sorted by one or more criteria
  [LIMIT {[offset,]row_count | row_countOFFSET offset}];
   -- Specify which records to query from

Note: [] brackets represent optional and {} brackets represent mandatory

4.3. Specify query fields

-- Query the results of all data columns in the table , use * Symbol; However, it is inefficient and not recommended .

-- Query all student information
SELECT * FROM `student`;

-- Query specified column(Student number , full name)
SELECT `studentno`,`studentname` FROM `student`;

1. AS alias

• You can give the data column a new alias
• You can give the table a new alias
• The calculated or summarized results can be replaced by another new name

-- Here is the alias for the column(of course as Keywords can be omitted)
SELECT `studentno` AS 'Student number',`studentname` AS 'full name' FROM `student`;

-- use as You can also alias the table
SELECT `studentno` AS 'Student number',`studentname` AS 'full name' FROM `student` AS 's';

-- use as,Give the query results a new name
-- CONCAT()Function concatenation string
SELECT CONCAT('full name:',`studentname`) AS 'New name' FROM `student`;

2. DISTINCT de duplication

• Remove the duplicate records in the record results returned by the SELECT query (the values of all returned columns are the same), and only one record is returned

-- # Check which students took the exam (student number) to remove duplicates
SELECT * FROM result; -- View test results
SELECT studentno FROM result; -- Check which students took the exam
SELECT DISTINCT studentno FROM result; -- understand:DISTINCT Remove duplicates , (The default is ALL)

3. Expression

Expressions in the database: generally composed of text values, column values, nulls, functions and operators

Application scenario:

• The SELECT statement is used in the return result column
• Used in order by, having and other sub clauses in the SELECT statement
• Expressions are used in where conditional statements in DML statements

-- selcet Expressions can be used in queries
SELECT @@auto_increment_increment; -- Query auto increment step
SELECT VERSION(); -- Query version number
SELECT 100*3-1 AS Calculation results; -- expression

-- Students collectively raise one score to check their examination results
SELECT studentno,StudentResult+1 AS 'After scoring' FROM result;

• Avoid including '.', '*' in the SQL return result And parentheses to interfere with the development of language programs

4. where conditional statement

Function: used to retrieve qualified records in a data table

Search criteria can be composed of one or more logical expressions, and the results are generally true or false

• MySQL operator:
  • Arithmetic operator
  • Comparison operator
  • Logical operator
  • Bitwise Operators

Detailed tutorial: https://www.runoob.com/mysql/mysql-operator.html

1. Logical operator

Logical operators are used to judge whether an expression is true or false. If the expression is true, the result returns 1. If the expression is false, the result returns 0.

Operation symbol	effect
NOT or!	Logical non
AND or&&	Logic and
OR or||	Logical or
XOR	Logical XOR

-- Qualified query(where)
SELECT Studentno,StudentResult FROM result;

-- The query test score is 95-100 Between
SELECT Studentno,StudentResult
FROM result
WHERE StudentResult>=95 AND StudentResult<=100;

-- AND It can also be written as &&
SELECT Studentno,StudentResult
FROM result
WHERE StudentResult>=95 && StudentResult<=100;

-- Fuzzy query(Corresponding word:Precise query)
SELECT Studentno,StudentResult
FROM result
WHERE StudentResult BETWEEN 95 AND 100;

-- Except classmate 1000,Ask other students for their grades
SELECT studentno,studentresult
FROM result
WHERE studentno!=1000;

-- use NOT
SELECT studentno,studentresult
FROM result
WHERE NOT studentno=1000;

Try to use English letters

2. Comparison operator (fuzzy query)

be careful:

• Arithmetic operation can only be performed between records of numerical data type;
• Only data of the same data type can be compared;

Symbol	describe	remarks
=	be equal to
<>, !=	Not equal to
>	greater than
<	less than
<=	Less than or equal to
>=	Greater than or equal to
BETWEEN	Between two values	>=min&&<=max
NOT BETWEEN	Not between two values
IN	In collection
NOT IN	Not in collection
<=>	Strictly compare two NULL values for equality	When both opcodes are NULL, the resulting value is 1; When an opcode is NULL, the resulting value is 0
LIKE	Fuzzy matching
REGEXP or RLIKE	Regular matching
IS NULL	Empty
IS NOT NULL	Not empty

like

For fuzzy query, wildcards can be used

'%a'     //Data ending with a
'a%'     //Data starting with a
'%a%'    //Data with a
'_a_'    //Three digits with a in the middle
'_a'     //Two digits and ending with a
'a_'     //Two digits with the initial letter a

-- Inquire the student number and name of students surnamed Liu
-- like Wildcards used in combination : % (Represents 0 to any character) _ (One character)
SELECT studentno,studentname FROM student
WHERE studentname LIKE 'Liu%';

-- Inquire about students surnamed Liu,There is only one word after it
SELECT studentno,studentname FROM student
WHERE studentname LIKE 'Liu_';

-- Inquire about students surnamed Liu,There are only two words behind it
SELECT studentno,studentname FROM student
WHERE studentname LIKE 'Liu__';

-- Query names containing Jiazi
SELECT studentno,studentname FROM student
WHERE studentname LIKE '%Jia%';

-- If the query name contains special characters, you need to use escape symbols '\'
-- Custom escape key: ESCAPE ':'

in

Exact matching is required. Wildcards are not allowed

-- The inquiry student number is 1000,1001,1002 Name of student
SELECT studentno,studentname FROM student
WHERE studentno IN (1000,1001,1002);

-- The inquiry address is in Beijing,Nanjing,Students in Luoyang, Henan
SELECT studentno,studentname,address FROM student
WHERE address IN ('Beijing','Nanjing','Luoyang, Henan');

null

-- Query students whose birth date is not filled in
-- Can't write directly=NULL , This represents a mistake , use is null
SELECT studentname FROM student
WHERE BornDate IS NULL;

-- Query students with birth date
SELECT studentname FROM student
WHERE BornDate IS NOT NULL;

-- Query students who do not write their home address(Empty string is not equal to null)
SELECT studentname FROM student
WHERE Address='' OR Address IS NULL;

4.4. Associated table query join

You can use Mysql JOIN in SELECT, UPDATE and DELETE statements to JOIN multi table queries

command	name	describe
INNER JOIN	Internal connection, or equivalent connection	Get the record of field matching relationship in two tables
LEFT JOIN	Left connection	Get all records in the left table, even if there is no corresponding matching record in the right table
RIGHT JOIN	Right connection	In contrast to LEFT JOIN, it is used to obtain all records in the right table Even if there is no corresponding matching record in the left table

1,INNER JOIN

INNER JOIN (INNER JOIN can also be omitted, and the effect is the same). as can also be omitted

select a.runoob_id,a.runoob_author,b.runoob_count 
from runoob_tbl as a
inner join tcount_tbl as b
on a.runoob_author = b.runoob_author ;

Equivalent to:

select a.runoob_id,a.runoob_author,b.runoob_count
from runoob_tbl a,tcount_tbl b
where a.runoob_author = b.runoob_author;

2,LEFT JOIN

MySQL LEFT JOIN is different from join. MySQL LEFT JOIN will read all data in the left data table, even if there is no corresponding data in the right table

select a.runoob_id,a.runoob_author,b.runoob_count 
from runoob_tbl as a
left join tcount_tbl as b
on a.runoob_author = b.runoob_author ;

3,RIGHT JOIN

MySQL RIGHT JOIN will read all the data in the right data table, even if there is no corresponding data in the left table.

select a.runoob_id,a.runoob_author,b.runoob_count 
from runoob_tbl as a
right join tcount_tbl as b
on a.runoob_author = b.runoob_author ;

4.5 sorting and paging

• sort
  • asc ascending order
  • desc reverse order

/*============== Sort================
Syntax: ORDER BY
   ORDER BY Statement to sort the result set based on the specified column.
   ORDER BY Statement sorts records in ascending ASC order by default.
   If you want to sort records in descending order, you can use the DESC keyword.
   
*/

-- Query database structure-1 All test results(Student number student name subject name grade)
-- Sort by grades in descending order
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='database structure -1'
ORDER BY StudentResult DESC

• paging

/*
Syntax: SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
 Benefits: (user experience, network transmission, query pressure)

deduction:
   Page 1: limit 0,5
   Page 2: limit 5,5
   Page 3: limit 10,5
   ......
   Page N: limit (pageNo-1)*pageSzie,pageSzie
   [pageNo:Page number, pageSize: number of items displayed on a single page]   
*/

-- Display 5 pieces of data per page
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='database structure -1'
ORDER BY StudentResult DESC , studentno
LIMIT 0,5

4.6 sub query and nested query

/*============== Subquery================
What is a subquery?
   Another query statement is nested in the WHERE condition clause of the query statement
   Nested query can be composed of multiple sub queries, and the solution method is from the inside to the outside;
   The results returned by subqueries are generally collections, so it is recommended to use the IN keyword;
*/

-- Query database structure-1 All test results(Student number,Account number,achievement),And the grades are in descending order
-- Method 1:Use join query
SELECT studentno,r.subjectno,StudentResult
FROM result r
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
WHERE subjectname = 'database structure -1'
ORDER BY studentresult DESC;

-- Method 2:Use subquery(Execution sequence:From inside to outside)
SELECT studentno,subjectno,StudentResult
FROM result
WHERE subjectno=(
   SELECT subjectno FROM `subject`
   WHERE subjectname = 'database structure -1'
)
ORDER BY studentresult DESC;

-- The inquiry course is advanced mathematics-2 Student number and name of students with a score of no less than 80
-- Method 1:Use join query
SELECT s.studentno,studentname
FROM student s
INNER JOIN result r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` sub
ON sub.`SubjectNo` = r.`SubjectNo`
WHERE subjectname = 'Advanced mathematics-2' AND StudentResult>=80

-- Method 2:Use join query+Subquery
-- Student number and name of students with a score of no less than 80
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80

-- on top SQL on the basis of,Add requirements:The course is advanced mathematics-2
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80 AND subjectno=(
   SELECT subjectno FROM `subject`
   WHERE subjectname = 'Advanced mathematics-2'
)

-- Method 3:Use subquery
-- Step by step writing is simple sql sentence,Then nest them
SELECT studentno,studentname FROM student WHERE studentno IN(
   SELECT studentno FROM result WHERE StudentResult>=80 AND subjectno=(
       SELECT subjectno FROM `subject` WHERE subjectname = 'Advanced mathematics-2'
  )
)

/*
Exercise topic:
   Check the score information (student number, name, score) of the top 5 students in C Language-1
   Use sub query to query the grade name of Guo Jing
*/

4.7 grouping and filtering

-- Query the average score of different courses, the highest score, the lowest score, and the average score is greater than 80
--Core:	((grouped according to different courses)
SELECT subjectname,AVG(studentresult) AS average,MAX(StudentResult) AS Highest score,MIN(StudentResult) AS Lowest score
 FROM result AS r
 INNER JOIN `subject` AS sub
 ON r.subjectno = sub.subjectno
 GROUP BY r.subjectno --By what field
 HAVING average>80; --having stay group by behind

5. Functions

5.1 common functions

1. String function

SELECT CHAR_LENGTH('Crazy God says persistence can succeed'); /*Returns the number of characters contained in a string*/
 SELECT CONCAT('I','love','program');  /*Merge strings. There can be multiple parameters*/
 SELECT INSERT('I love programming helloworld',1,2,'Super love');  /*Replace string, replacing a length from a position*/
 SELECT LOWER('KuangShen'); /*a lowercase letter*/
 SELECT UPPER('KuangShen'); /*Capitalize*/
 SELECT LEFT('hello,world',5);   /*Intercept from left*/
 SELECT RIGHT('hello,world',5);  /*Intercept from the right*/
 SELECT REPLACE('Crazy God says persistence can succeed','insist','strive');  /*Replace string*/
 SELECT SUBSTR('Crazy God says persistence can succeed',4,6); /*Intercept string, start and length*/
 SELECT REVERSE('Crazy God says persistence can succeed'); /*reversal
 
 -- Query students surnamed Zhou,Changed to Zou
 SELECT REPLACE(studentname,'week','Zou') AS New name
 FROM student WHERE studentname LIKE 'week%';

2. Digital function

 SELECT ABS(-8);  /*absolute value*/
 SELECT CEILING(9.4); /*Round up*/
 SELECT FLOOR(9.4);   /*Round down*/
 SELECT RAND();  /*Random number, returns a random number between 0 and 1*/
 SELECT SIGN(0); /*Symbolic function: negative number returns - 1, positive number returns 1, 0 returns 0*/

3. Date function

 SELECT CURRENT_DATE();   /*Get current date*/
 SELECT CURDATE();   /*Get current date*/
 SELECT NOW();   /*Get current date and time*/
 SELECT LOCALTIME();   /*Get current date and time*/
 SELECT SYSDATE();   /*Get current date and time*/
 
 -- Get date,Hour, minute and second
 SELECT YEAR(NOW());
 SELECT MONTH(NOW());
 SELECT DAY(NOW());
 SELECT HOUR(NOW());
 SELECT MINUTE(NOW());
 SELECT SECOND(NOW());

4. Advanced function

 SELECT VERSION();  /*edition*/
 SELECT USER();     /*user*/

Detailed explanation: https://www.runoob.com/mysql/mysql-functions.html

5.2 aggregation function

Function name	describe
COUNT()	Returns the total number of records that meet the Select criteria, such as select count(*) [not recommended, inefficient]
SUM()	Returns a numeric field or expression column for statistics, and returns the sum of a column.
AVG()	Statistics are usually made for numeric fields or expression columns, and the average value of a column is returned
MAX()	Statistics can be made for numeric fields, character fields or expression columns to return the maximum value.
MIN()	Statistics can be made for numeric fields, character fields or expression columns to return the smallest value.

5.3 MD5 encryption

1. Introduction to MD5

MD5, namely message digest algorithm 5, is used to ensure the integrity and consistency of information transmission. It is one of the hash algorithms widely used by computers (also translated into digest algorithm and hash algorithm). MD5 has been widely implemented in mainstream programming languages. Data (such as Chinese characters) operation is another fixed length value, which is the basic principle of hash algorithm. The predecessor of MD5 is MD2, MD3 and MD4.

2. Encryption and post encryption comparison

 --Direct encryption when adding data
 INSERT INTO testmd5 VALUES(4,'kuangshen3',md5('123456'));
 
 --Compare encrypted data during query
 SELECT * FROM testmd5 WHERE `name`='kuangshen' AND pwd=MD5('123456');

6. Business

6.1 ACID principle of affairs [key points | understanding]

Atomicity

• All operations in the whole transaction are either completed or not completed. It is impossible to stagnate in an intermediate link. If an error occurs during the execution of a transaction, it will be rolled back to the state before the start of the transaction, as if the transaction had never been executed.

Consistency

• A transaction can encapsulate state changes (unless it is read-only). A transaction must always keep the system in a consistent state, no matter how many concurrent transactions are at any given time. That is to say, if multiple transactions are concurrent, the system must also operate as a serial transaction. Its main characteristics are preservation an invariance, Taking the transfer case as an example, if there are five accounts, and the balance of each account is 100 yuan, the total amount of the five accounts is 500 yuan. If multiple transfers occur between the five accounts at the same time, no matter how many are concurrent, such as 5 yuan between accounts a and B, 10 yuan between accounts C and D, and 15 yuan between accounts B and E, the total amount of the five accounts should still be 500 yuan, This is protection and invariance.

Isolated

• Isolate state execution transactions so that they appear to be the only operation performed by the system at a given time. If two transactions run at the same time and perform the same functions, the isolation of transactions will ensure that each transaction is considered to be the only one using the system in the system. This attribute is sometimes called serialization. In order to prevent confusion between transaction operations, requests must be serialized or serialized so that only one request is used for the same data at the same time.

Persistent

• After the transaction is completed, the changes made by the transaction to the database will be permanently saved in the database and will not be rolled back.

reference resources: https://blog.csdn.net/dengjili/article/details/82468576

Some problems caused by isolation

• Dirty reading

  A transaction reads uncommitted data from another transaction

• Non repeatable reading

  A row of data in a table is read in a transaction, and the results are different multiple times. (this is not necessarily a mistake, but it is wrong on some occasions)

• Virtual reading (unreal reading)

  It refers to that data inserted by other transactions is read in one transaction, resulting in inconsistent total number of reads before and after.

6.2. Transaction syntax

-- use set Statement to change the auto submit mode
SET autocommit = 0;   /*close*/
SET autocommit = 1;   /*open*/

-- be careful:
--- 1.MySQL The default is auto submit
--- 2.Auto commit should be turned off first when using transactions

-- Start a transaction,Mark the starting point of the transaction
START TRANSACTION;  

-- Commit a transaction to the database
COMMIT;

-- Rollback transaction,The data returns to the initial state of this transaction
ROLLBACK;

-- reduction MySQL Automatic submission of database
SET autocommit =1;

-- Save point
SAVEPOINT Save point name -- Set a transaction savepoint
ROLLBACK TO SAVEPOINT Save point name -- Rollback to savepoint
RELEASE SAVEPOINT Save point name -- Delete savepoint

6.3 test

/*
Classroom test questions

A Buy a commodity with a price of 500 yuan online and transfer it through online bank
A Your bank card balance is 2000, and then pay 500 to merchant B
 Merchant B's bank card balance at the beginning is 10000

Create a database shop and create a table account, and insert 2 pieces of data
*/

CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;

CREATE TABLE `account` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
`cash` DECIMAL(9,2) NOT NULL, -- Amount used decimal type
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

insert into `account`(`name`,`cash`)
values('Zhang San',2000),('Li Si',3000);

--Start transaction operation
set autocommit = 0;-- Turn off auto commit transactions

start transaction; --Start transaction

--A set of operations within a transaction
update `account` set `cash` = `cash` - 300 where `name` = 'Zhang San';
update `account` set `cash` = `cash` + 300 where `name` = 'Li Si';

commit;--Commit transaction

#rollback;-- Rollback transaction

set autocommit = 1;--Automatic commit of recovery transactions

7. Index

7.1 function of index

• Improve query speed
• Ensure data uniqueness
• The connection between tables can be accelerated to realize the referential integrity between tables
• When using grouping and sorting clauses for data retrieval, the time of grouping and sorting can be significantly reduced
• Full text search field for search optimization

7.2 classification

1. Primary key index

Primary key: an attribute group can uniquely identify a record

characteristic:

• The most common index type
• Ensure the uniqueness of data records
• Determine the location of specific data records in the database

2. Unique index

Function: avoid duplicate values in a data column in the same table

Difference from primary key index

• There can only be one primary key index

• There may be more than one unique index

  CREATE TABLE `Grade`(
    `GradeID` INT(11) AUTO_INCREMENT PRIMARYKEY,
    `GradeName` VARCHAR(32) NOT NULL UNIQUE
     -- or UNIQUE KEY `GradeID` (`GradeID`)
  )
  

3. General index (Index)

Function: quickly locate specific data

be careful:

• Both index and key keywords can set the general index

• Fields that should be added to query criteria

• Too many general indexes should not be added, which will affect the operation of data insertion, deletion and modification

  CREATE TABLE `result`(
     -- Omit some code
    INDEX/KEY `ind` (`studentNo`,`subjectNo`) -- Add when creating table
  )
  

  -- Add after creation
  ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);
  

4. Full text index (FullText)

Function: quickly locate specific data

be careful:

• Used for data tables of MyISAM type (InnoDB from 5.6 also supports full-text indexing)

• Can only be used for char, varchar, text data column types

• Suitable for large data sets

  /*Add full text index*/
  ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname` (`StudentName`);
  
  /*EXPLAIN : Analyze SQL statement execution performance*/
  EXPLAIN SELECT * FROM student WHERE studentno='1000';
  
  /*Use full-text indexing*/
  -- Full text search passed MATCH() Function complete.
  -- Search string as against() The parameters of are given. The search is performed ignoring the case of letters. For each record row in the table, MATCH() Returns a correlation value. That is, between the search string and the record line MATCH() The similarity scale between the text of the column specified in the list.
  EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');
  
  /*
  Before we start, let's talk about the full-text index version, storage engine and data type support
  
  MySQL 5.6 In previous versions, only MyISAM storage engine supported full-text indexing;
  MySQL 5.6 And later versions, MyISAM and InnoDB storage engines support full-text indexing;
  Full text indexes can be created only when the data types of fields are char, varchar, text and their series.
  When testing or using full-text indexing, first check whether your MySQL version, storage engine and data type support full-text indexing.
  */
  

5. Composite index

7.3 common operation commands

--Method 1: when creating a table
CREATE TABLE Table name (
    Field name 1 data type [Integrity constraints],
    Field name 2 data type [Integrity constraints],
    [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
    [Index name] (Field name[(length)] [ASC |DESC])
);


--Method 2: CREATE Create an index on an existing table
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX Index name
                    ON Table name (Field name[(length)] [ASC |DESC]) ;


--Method 3: ALTER TABLE Create an index on an existing table
ALTER TABLE Table name ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                            Index name (Field name[(length)] [ASC |DESC]) ;
                           
                           
--Delete index:
DROP INDEX Index name ON Table name;

--Delete primary key index: 
ALTER TABLE Table name DROP PRIMARY KEY;


--Display index information:
SHOW INDEX FROM student;

7.4 test

Video: https://www.bilibili.com/video/BV1NJ411J79W?p=31

Blog: https://blog.csdn.net/pan_h1995/article/details/106060935

7.5 index criteria

• The more indexes, the better
• Do not index data that changes frequently
• It is recommended not to add indexes to tables with small amount of data
• The index should generally be added to the field of the search criteria

Indexed data structure

Reference article: http://blog.codinglabs.org/articles/theory-of-mysql-index.html

-- When creating the above index, we can specify the index type for it, which can be divided into two categories
hash Type index: single query is fast and range query is slow
btree Index of type: b+Tree, the more layers, the exponential growth of data volume (we use it because innodb (it is supported by default)

-- Different storage engines support different index types
InnoDB Supports transaction, row level locking, and B-tree,Full-text Index, not supported Hash Indexes;
MyISAM Transaction is not supported, table level locking is supported, and B-tree,Full-text Index, not supported Hash Indexes;
Memory Transaction is not supported, table level locking is supported, and B-tree,Hash Index, not supported Full-text Indexes;
NDB Supports transaction, row level locking, and Hash Index, not supported B-tree,Full-text Equal index;
Archive Transaction and table level locking are not supported B-tree,Hash,Full-text Equal index;

8. User management

8.1 authority command

/* User and rights management */ ------------------
User information table: mysql.user

-- Refresh permissions
FLUSH PRIVILEGES

-- Add user CREATE USER kuangshen IDENTIFIED BY '123456'
CREATE USER user name IDENTIFIED BY [PASSWORD] password(character string)
  - Must have mysql Global of database CREATE USER Permission, or possession INSERT jurisdiction.
  - Only users can be created and cannot be granted permissions.
  - User name, note the quotation marks: for example: 'user_name'@'192.168.1.1'
  - Passwords also need quotation marks, and pure digital passwords also need quotation marks
  - To specify a password in plain text, ignore it PASSWORD key word. To specify the password as PASSWORD()The mixed value returned by the function must contain keywords PASSWORD

-- Rename User  RENAME USER kuangshen TO kuangshen2
RENAME USER old_user TO new_user

-- Set password
SET PASSWORD = PASSWORD('password')    -- Set password for current user
SET PASSWORD FOR user name = PASSWORD('password')    -- Sets the password for the specified user

-- delete user DROP USER kuangshen2
DROP USER user name

-- Assign permissions/Add user
GRANT Permission list ON Table name TO user name [IDENTIFIED BY [PASSWORD] 'password']
  - all privileges Indicates all permissions (not all permissions), and root Fewer users than grant Permission, cannot authorize others)
  - *.* All tables representing all libraries
  - Library name.The table name represents a table under a library

-- View permissions   SHOW GRANTS FOR root@localhost;
SHOW GRANTS FOR user name
   -- View current user permissions
  SHOW GRANTS; or SHOW GRANTS FOR CURRENT_USER; or SHOW GRANTS FOR CURRENT_USER();

-- revoking permission
REVOKE Permission list ON Table name FROM user name
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user name    -- Revoke all permissions

8.2 interpretation of authority

-- Permission list
ALL [PRIVILEGES]    -- Set division GRANT OPTION All simple permissions except
ALTER    -- Allow use ALTER TABLE
ALTER ROUTINE    -- Change or cancel stored subroutines
CREATE    -- Allow use CREATE TABLE
CREATE ROUTINE    -- Create stored subroutines
CREATE TEMPORARY TABLES        -- Allow use CREATE TEMPORARY TABLE
CREATE USER        -- Allow use CREATE USER, DROP USER, RENAME USER and REVOKE ALL PRIVILEGES. 
CREATE VIEW        -- Allow use CREATE VIEW
DELETE    -- Allow use DELETE
DROP    -- Allow use DROP TABLE
EXECUTE        -- Allows the user to run stored subroutines
FILE    -- Allow use SELECT...INTO OUTFILE and LOAD DATA INFILE
INDEX     -- Allow use CREATE INDEX and DROP INDEX
INSERT    -- Allow use INSERT
LOCK TABLES        -- Allow you to have SELECT Table usage of permissions LOCK TABLES
PROCESS     -- Allow use SHOW FULL PROCESSLIST
REFERENCES    -- Not implemented
RELOAD    -- Allow use FLUSH
REPLICATION CLIENT    -- Allows the user to ask for the address of the secondary or primary server
REPLICATION SLAVE    -- For replicated secondary servers (reading binary log events from the primary server)
SELECT    -- Allow use SELECT
SHOW DATABASES    -- Show all databases
SHOW VIEW    -- Allow use SHOW CREATE VIEW
SHUTDOWN    -- Allow use mysqladmin shutdown
SUPER    -- Allow use CHANGE MASTER, KILL, PURGE MASTER LOGS and SET GLOBAL sentence, mysqladmin debug Command; Allows you to connect (once), even if you have reached max_connections. 
UPDATE    -- Allow use UPDATE
USAGE    -- "Synonymous with "no permission"
GRANT OPTION    -- Permission granted


/* Table maintenance */

-- Analyze and store the keyword distribution of the table
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE Table name ...
-- Check one or more tables for errors
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
-- Defragment data files
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

9. Backup

1. Copy physical files directly

2. Export manually in the visualizer

3. mysqldump client

effect:

• Dump database
• Collect database for backup
• Transfer data to another SQL server, not necessarily MySQL server

-- export
-- Export a table 
mysqldump -uroot -p123456 school student >D:/a.sql
mysqldump -u user name -p Password library name table name > file name(D:/a.sql)

-- Export multiple tables
mysqldump -uroot -p123456 school student result >D:/a.sql
mysqldump -u user name -p Password library name table 1 Table 2 Table 3 > file name(D:/a.sql)

-- Export all tables
mysqldump -uroot -p123456 school >D:/a.sql
mysqldump -u user name -p Password library name > file name(D:/a.sql)

-- Export a library
mysqldump -uroot -p123456 -B school >D:/a.sql
mysqldump -u user name -p password -B Library name > file name(D:/a.sql)

-- sure-w Carry backup conditions

-- Import
-- Logging in mysql In case of:
source D:/a.sql
source Backup file

-- Without logging in
mysql -u user name -p Password library name < Backup file

10. Standardize database design

10.1 why design?

Poor database design:

• Data redundancy, waste of storage space
• Exceptions in data update and insertion
• Poor program performance

Good database design:

• Save data storage space
• Ensure data integrity
• Facilitate the development of database application system

Database design in software project development cycle:

• Demand analysis stage: analyze customers' business and data processing requirements
• Outline design stage: design the E-R model diagram of the database to confirm the correctness and completeness of the requirement information

10.2 three paradigms

First normal form (1st NF)

• The goal of the first normal form is to ensure the atomicity of each column. If each column is the smallest non separable data unit, the first normal form is satisfied

Second normal form (2nd NF)

• The second normal form (2NF) is established on the basis of the first normal form (1NF), that is, to meet the second normal form (2NF) must first meet the first normal form (1NF).
• The second paradigm requires each table to describe only one thing

Third paradigm (3rd NF)

• If a relationship satisfies the second normal form and no columns other than the primary key are passed and depend on the primary key column, the third normal form is satisfied
• The third paradigm needs to ensure that each column of data in the data table is directly related to the primary key, not indirectly

Relationship between normalization and performance

• In order to meet some business goals, database performance is more important than normalized database
• At the same time of data standardization, we should comprehensively consider the performance of the database
• By adding additional fields to a given table, the time required to search for information is greatly reduced
• By inserting calculated columns into a given table, it is convenient to query

11,JDBC

11.1. Database driver

Java Database Connectivity (JDBC) is an application program interface used in the Java language to regulate how client programs access the database. It provides methods such as querying and updating data in the database. JDBC is also a trademark of Sun Microsystems. We usually say JDBC is oriented to relational databases.

11.2 JDBC test

public static void main(String[] args) throws Exception {
    //Load driver
    Class.forName("com.mysql.jdbc.Driver");
    //User information and url
    String url = "jdbc:mysql://localhost:3306/jdbcstudy?useSSL=false&useUnicode=true&characterEncoding=utf8";
    String username="root";
    String password="123456";
    //Successfully linked database object
    Connection connection = DriverManager.getConnection(url,username,password);
    //Object executing SQL
    Statement statement = connection.createStatement();
    //The object executing SQL may return results when executing SQL
    String sql="select * from users";
    ResultSet resultSet =  statement.executeQuery(sql);//Returned result set
    while (resultSet.next()){
        System.out.println("id="+resultSet.getObject("id"));
        System.out.println("name="+resultSet.getObject("name"));
    }
    //Cast link
    resultSet.close();
    statement.close();
    connection.close();

}

Steps:

1. Load driver
2. Linked database DriverManager
3. Get the statement of the object executing sql
4. Get the returned result set
5. Release link

Note: possible causes of error reporting:

• &No & amp; replace with

• I didn't put it first

• DriverManager

//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver");

• url

String url = "jdbc:mysql://localhost:3306/jdbcstudy?useSSL=false&useUnicode=true&characterEncoding=utf8";
//mysql--3306
// Protocol: / / host address: port number / database name? Parameter 1 & parameter 2
//oracle--1521
//jdbc:oracle:thin:@localhost:1521:sid

• Connection

//The link succeeded. The database object connection represents the database
Connection connection = DriverManager.getConnection(url,username,password);
connection.commit();
connection.rollback();
connection.setAutoCommit();

• Statement

statement.executeQuery();//The result set returned by the query operation is ResultSet
statement.execute();//You can execute any sql

statement.executeUpdate();//Update, insert and delete all use this to return the number of affected rows

• ResultSet

//If you don't know the type, use Object
resultSet.getObject();
//If you know the type, you can obtain it directly using the corresponding type
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDouble();
resultSet.next();//Move to next row of data
resultSet.beforeFirst();//Move to front
resultSet.afterLast();//Move to last
resultSet.previous();//Move to previous line
resultSet.absolute(i);//Move to line i

11.3 code implementation

Resource file: dB properties

//db.properties store information to reduce coupling
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useSSL=false&useUnicode=true&characterEncoding=utf8
username=root
password=123456

Tool class: jdbcutils java

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils {
    private static  String driver=null;
    private static  String url=null;
    private static  String username=null;
    private static  String password=null;
    static {
        try {
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");

            Properties properties=new Properties();
            assert in != null;
            properties.load(in);
            driver=properties.getProperty("driver");
            url=properties.getProperty("url");
            username=properties.getProperty("username");
            password=properties.getProperty("password");
            //The driver is loaded only once
            Class.forName(driver);

        }catch (Exception e){
            e.printStackTrace();
        }
    }

    //Get connection
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }
    //Release resources
    public  static void release(Connection connection, Statement statement, ResultSet resultSet) throws Exception {
        if (resultSet!=null){
            resultSet.close();
        }
        if (statement!=null){
            statement.close();
        }
        if (connection!=null){
            connection.close();
        }
    }
}

Call:

public class Test2 {
    public static void main(String[] args) throws Exception {
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;
        try {
            connection= JdbcUtils.getConnection();
            statement=connection.createStatement();
            String sql="insert into users(id,name,password,email,birthday)" +
                    "values(100,'tzt','123456','123456@qq.com','1998-08-08');";
            int i=statement.executeUpdate(sql);
            if (i>0){
                System.out.println("Insert succeeded!");
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}  

11.4. SQL injection

SQL injection means that the web application does not judge or filter the legitimacy of the user's input data. The attacker can add additional SQL statements at the end of the query statements defined in advance in the web application, and realize illegal operations without the knowledge of the administrator, so as to deceive the database server to execute unauthorized arbitrary queries, So as to further obtain the corresponding data information.

select * from users where name='name' and password ='password'
    
String name=" ' or  1=1 ";
String password =" ' or  1=1 ";

select * from users where name='  ' or  1=1 and password ='' or 1=1

11.5,PrepareStatement

• It can prevent SQL injection and is more efficient
• Treat the passed data as a string. If there are escape characters, ignore them directly

 //？ placeholder 
String sql="insert into users(id,name,password,email,birthday)" +
    "values(?,?,?,?,?);";
//The difference between and Statement!!!!!!!!!
statement=connection.prepareStatement(sql);
//Assign parameters manually
statement.setInt(1,99);
statement.setString(2,"hhh");
statement.setString(3,"12312313");
statement.setString(4,"15612318@qq.com");
statement.setDate(5,new java.sql.Date(new Date(1231).getTime()));

11.6 database connection pool

Database link - execution complete - release very expensive resources
Pooling Technology: prepare some pre prepared resources and link the pre prepared resources

If the number of common connections is 10
Minimum number of connections: 10
Maximum number of connections: 15. The maximum carrying capacity of the service. If it exceeds this value, it will be queued
Wait timeout: if the wait time exceeds a certain value, it will fail directly

Write connection pool: implement DataSource interface

Open source data source implementation

DBCP
C3P0
Druid: Alibaba

db.properties

proxool.driverClassName=com.mysql.jdbc.Driver
proxool.url=jdbc:mysql://localhost:3306/test?seUnicode=true&characterEncoding=utf-8
proxool.username=root
proxool.password=123456

proxool.maximumConnectionCount=500
proxool.minimumConnectionCount=10