MySQL basic syntax

Several basic database operation commands:

This article is for learning only, non-commercial use, invasion and deletion

update user set password=password('123456')where user='root'; Change Password
flush privileges; Refresh database
show databases; Show all databases
use dbname；Open a database
show tables; show database mysql All tables in
describe user; Display table mysql In database user Column information for table
create database name; Create database
use databasename; Select database

exit; sign out Mysql
? Command keywords : ask for help
-- Indicates a comment

Database operation (just understand)

Command line operation database

Create database :  create database [if not exists] Database name; -- Brackets indicate optional

Delete database : drop database [if exists] Database name;

view the database : show databases;

Use database : use Database name;

The column type of the database

Value:

int Standard integer 4 bytes common int

float Floating point number 4 bytes

double Floating point number 8 bytes

character string:

char String fixed size 0~255
varchar Variable string 0~65535 frequently-used string
tinytext Micro text 2^8-1
text Text string 2^16-1

Time date:

date YYYY-MM-DD Date format
time HH:mm:ss Time format
datetime YYYY-MM-DD HH:mm:ss The most common time format
timestamp Timestamp, 1970.1.1 Milliseconds to now!More commonly used
year Year representation

null
No value, unknown
Note: do not use NULL for operation. The result is NULL

Field properties of database (emphasis):

unsigned:
Unsigned integer
Cannot be negative

zerofill:
0 filled
Insufficient digits, filled with 0, int(3),5-005

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

Each table must have the following five fields (understand)

id primary key
version optimistic lock
is_delete pseudo delete
gmt_create creation time
gmt_update modification time

To create a database table:

– Objective: to create a school database
– create student table (columns, fields)
– student id int login password varchar(20) name, gender varchar(2), date of birth (datatime), home address, email
– before creating a table, be sure to select a database

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

 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]

Common commands:

 -- View the definition of the database
SHOW CREATE DATABASE school;
-- View the definition of the data table
SHOW CREATE TABLE student;
-- Display table structure
DESC student;  -- Set strict check mode(No fault tolerance)SET

Type of data table:

Sets the type of data table
/*
MyISAM: used by default
InnoDB: early use of database
*/

Experience (where applicable):

MyISAM: Transaction: not supported; Data row locking: not supported; Foreign key constraint: not supported; Full text index: support; Table space size: small;
InnoDB: transaction processing: supported; Data row locking: supported; Foreign key constraints: supported; Full text index: not supported; Table space size: large, about twice;

Applicable to MyISAM: save space and corresponding speed
Applicable to InnoDB: security, transaction processing and multi-user operation data sheet

Storage location of data table:

MySQL data tables are stored on disk as files
Including table files, data files, and database options files
Location: the Mysql installation directory \ data \ stores the data table. The directory name corresponds to the database name, and the file name under this directory corresponds to the data table

be careful:
Differences of MySQL engine in physical files

The InnoDB type data table has only one *. frm file and the ibdata1 file in the upper directory

The MyISAM type data table corresponds to three files:

. frm - table structure definition file
. MYD - data file (data)
. MYI - index file (index)

Set the character set encoding of the database table:

CHARSET = utf8;
If it is not set, it will be the default character set encoding of MySQL (Chinese is not supported)
MySQL's default code is Latin1, which does not support Chinese
Configure the default encoding in my.ini
character-set-server=utf8

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, it is set according to the parameters in the MySQL database configuration file my.ini

Modify database:

Modify table ( ALTER TABLE ):
	Modify table name :ALTER TABLE Old table name RENAME AS New table name
	Add field : ALTER TABLE Table name ADD Field column properties[attribute]

	Modify field :
	ALTER TABLE Table name MODIFY Field column type[attribute]
	ALTER TABLE Table name CHANGE Old field name new field column attribute[attribute]

	Delete field :  ALTER TABLE Table name DROP Field name
	
	example:
	ALTER TABLE teacher RENAME AS teacher1
	ALTER TABLE teacher1 ADD age INT(11)
	ALTER TABLE teacher1 MODIFY age VARCHAR(11)
	ALTER TABLE teacher1 CHANGE age age1 INT(1)
	ALTER TABLE teacher1 DROP age1

Delete data table:

Syntax: DROP TABLE [IF EXISTS] Table name
IF EXISTS Is optional , Determine whether the data table exists
 If you delete a nonexistent data table, an error will be thrown
 example: DROP TABLE IF EXISTS teacher

All creation and deletion operations should be judged as much as possible to avoid errors

DML language:

Foreign key

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 whose primary key is a foreign key of another relationship is called a primary table
, the 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 key 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: (delete the table with foreign key relationship, first delete the slave table, and then delete the primary table)

1. Specify foreign key constraints when creating tables:

CREATE TABLE IF NOT EXISTS `grade` (
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Age ID',
`gradename` VARCHAR(50) NOT NULL COMMENT 'Age name',
PRIMARY KEY (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

– the gradeid field of the student table to reference the gradeid field of the grade table
– define foreign key
– add a constraint (execution reference) reference to the foreign key

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',
`gradeid` INT(10) NOT NULL COMMENT 'Student grade',
`address` VARCHAR(100) DEFAULT NULL COMMENT 'address',
`email` VARCHAR(50) DEFAULT NULL COMMENT 'mailbox',
PRIMARY KEY (`id`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) RE

2. Modify after table creation:

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',
`gradeid` INT(10) NOT NULL COMMENT 'Student grade',
`address` VARCHAR(100) DEFAULT NULL COMMENT 'address',
`email` VARCHAR(50) DEFAULT NULL COMMENT 'mailbox',
PRIMARY KEY (`id`)

) ENGINE=INNODB DEFAULT CHARSET=utf8

-- There is no foreign key relationship at the time of creation

ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`);

The above two operations are physical foreign keys, database level foreign keys, which are not recommended!

DML language (remember all):

Significance of database: data storage and data management

How to manage database data:

adopt SQLyog And other management tools to manage database data
 adopt DML Statement to manage database data

DML: Data Manipulation Language

Used to manipulate data contained in database objects

include:
INSERT (add data statement)
UPDATE (UPDATE data statement)
DELETE (DELETE data statement)

Add data:

INSERT command

Syntax:

INSERT INTO Table name[(Field 1,Field 2,Field 3,...)] VALUES('Value 1','Value 2','Value 3')
be careful : 
	Fields or values are separated by English commas .
	' Field 1,Field 2...' This part can be omitted , However, the added value must be consistent with the table structure,Data column,Sequence correspondence,And the quantity is consistent .
	Multiple pieces of data can be inserted at the same time , values Separated by English commas .

	1: INSERT INTO `student`(`name`) VALUES ('Li Si')
	2: INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('Li Si','123456','male')
	3: INSERT INTO `student`(`name`,`pwd`,`sex`)
	VALUES('Li Si','abxsss','male'),('Five brothers','bhhhax','female')

Modify data:

update command
	Syntax:
		UPDATE Table name SET column_name=value [,column_name2=value2,...] [WHERE condition];
		be careful : 
		column_name For the data column to change
		value Is the modified data , Can be a variable , Specifically refers to , Expression or nested SELECT result
		condition Filter criteria , If not specified, all column data of the table will be modified


	where Conditional clause:
		It can be simply understood as : Conditionally filter data from tables
		Operator:= ,<>Or!=(Not equal to)>,<,>=,<=,BETWEEN,AND,OR
		
	Test:
		-- Modify grade information
		UPDATE grade SET gradename = 'high school' WHERE gradeid = 1;
		
	-- All tables are modified without specifying conditions
	UPDATE `student` SET `name`='Bajie'

	-- Modify multiple attributes, separated by commas
	UPDATE `student` SET `name`='name of a fictitious monkey with supernatural powers',`email`='123@qq.com' WHERE `id`=1;

	-- Locate data through multiple conditions, trim
	UPDATE `student` SET `birthday`=CURRENT_TIME WHERE `name`='Five brothers' AND `sex`='female'

Delete data:

DELETE command:
	Syntax:
	DELETE FROM Table name [WHERE condition];
		be careful: condition Filter criteria , If not specified, all column data of the table will be deleted
		-- Delete last data
		DELETE FROM grade WHERE gradeid = 5

	TRUNCATE command:
	Function: used to completely empty table data , Table structure , Indexes , Constraint invariant ;
	Syntax:
	TRUNCATE [TABLE] table_name;
	-- Empty grade table
	TRUNCATE grade
	Note: different from DELETE command
	identical : Can delete data , Do not delete table structure , but TRUNCATE Faster
	Different :
		use TRUNCATE TABLE Reset AUTO_INCREMENT Counter
		use TRUNCATE TABLE It will not affect the transaction (which will be described later)
		
		Test:
			-- Create a test table
			CREATE TABLE `test` (
			`id` INT(4) NOT NULL AUTO_INCREMENT,
			`coll` VARCHAR(20) NOT NULL,
			PRIMARY KEY (`id`)
			) ENGINE=INNODB DEFAULT CHARSET=utf8

			-- Insert several test data
			INSERT INTO test(coll) VALUES('row1'),('row2'),('row3');

			-- Delete table data(No where Conditional delete)
			DELETE FROM test;
			-- conclusion:If not specified Where Delete all column data of the table,The current value of self increment is still based on the original value,Will log.

			-- Delete table data(truncate)
			TRUNCATE TABLE test;
			-- conclusion:truncate Delete data,The current value of auto increment will return to the initial value and start again;No logs will be logged.

			-- Same use DELETE Clear database table data of different engines.After restarting the database service
			-- InnoDB : The auto increment column starts again from the initial value (Because it is stored in memory,Loss of power)
			-- MyISAM : The auto increment column still starts from the previous auto increment data (Exist in file,Not lost

DQL language

DQL( Data Query Language Data query language )
Query database data , as SELECT sentence
 Simple single table query or multi table complex query and nested query
 Is the core of database language,The most important statement
 Most frequently used statements

SELECT syntax:

SELECT [ALL | DISTINCT]
	{* | 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
	be careful : [ ] Brackets represent optional , { }Parentheses are required

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;

AS Clause as alias:
effect:
	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;

Use of DISTINCT keyword:

effect : Remove SELECT Duplicate records in the record results returned by the query ( Returns the same value for all columns ) , Return only one

			-- # 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)

Columns using expressions

	Expressions in the database : Usually by text value , Column value , NULL , Functions and operators
	Application scenario :
		SELECT Statement is used in the return result column
		SELECT In a statement ORDER BY , HAVING Used in equal clauses

Expressions 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' test scores are collectively raised by one point to view
		SELECT studentno,StudentResult+1 AS 'After scoring' FROM result;
		avoid SQL The returned result contains ' . ' , ' * ' And parentheses to interfere with the development of language programs.

where conditional statement:

		Function: used to retrieve qualified records in a data table
		Search criteria can consist of one or more logical expressions , The results are generally true or false.

	Logical operators: AND or&&,OR or||,NOT Or! (try to use logical symbols in English)
	Test:

-- 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;

Fuzzy queries: comparison operators

	IS NULL  :a IS NULL
	IS NOT NULL	:a IS NOT NULL
	BETWEEN	:a BETWEEN b AND c
	LIKE:a LIKE b
	IN :a IN (a1,a2,a3......) 
	
	be careful:
	Arithmetic operations can only be performed between records of numeric data type ;
	Only data of the same data type can be compared ;

	Test:

-- Fuzzy query between and \ like \ in \ null
	-- =============================================
	-- LIKE
	-- =============================================
	-- 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
	-- =============================================
	-- 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 empty
	-- =============================================
	-- 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;

join query

	JOIN contrast:
	INNER JOIN:If there is at least one match in the table, the row is returned
	LEFT JOIN: Even if there is no match in the right table, all rows are returned from the left table
	RIGHT JOIN: Even if there is no match in the left table, all rows are returned from the right table
 test

	/*
	join query
	   If you need to query the data of multiple data tables,You can implement multiple queries through the join operator
	Inner connection inner join
	   Query the intersection of the result sets in two tables
	External connection outer join
	   Left outer connection left join
		   (Take the left table as the benchmark,The table on the right matches one by one,Unmatched,Returns the record of the left table,Right table to NULL fill)
	   Right outer connection right join
		   (Take the right table as the benchmark,The table on the left matches one by one,Unmatched,Returns the record of the right table,Left table to NULL fill)
		   
	Equivalent connection and non equivalent connection

	Self connection

*/

	-- Query the information of students who took the exam(Student number,Student name,Account number,fraction)
	SELECT * FROM student;
	SELECT * FROM result;

	/*Idea:
	(1):Analyze the requirements and determine that the query columns come from two classes, student result and join query
	(2):Determine which connection query to use? (internal connection)
	*/
	SELECT s.studentno,studentname,subjectno,StudentResult
	FROM student s
	INNER JOIN result r
	ON r.studentno = s.studentno

	-- Right connection(Can also be achieved)
	SELECT s.studentno,studentname,subjectno,StudentResult
	FROM student s
	RIGHT JOIN result r
	ON r.studentno = s.studentno

	-- Equivalent connection
	SELECT s.studentno,studentname,subjectno,StudentResult
	FROM student s , result r
	WHERE r.studentno = s.studentno

	-- Left connection (Inquired all the students,Those who don't take the exam will also be found out)
	SELECT s.studentno,studentname,subjectno,StudentResult
	FROM student s
	LEFT JOIN result r
	ON r.studentno = s.studentno

	-- Check the absent students(Left connection application scenario)
	SELECT s.studentno,studentname,subjectno,StudentResult
	FROM student s
	LEFT JOIN result r
	ON r.studentno = s.studentno
	WHERE StudentResult IS NULL

	-- Thinking questions:Query the information of students who took the exam(Student number,Student name,Account name,fraction)
	SELECT s.studentno,studentname,subjectname,StudentResult
	FROM student s
	INNER JOIN result r
	ON r.studentno = s.studentno
	INNER JOIN `subject` sub
	ON sub.subjectno = r.subjectno

Self connection

	/*
	Self connection
	   The data sheet is connected to itself

demand:From a containing column ID , Column name and parent column ID In the table
		Query parent column name and other sub column names
	*/

	-- Create a table
	CREATE TABLE `category` (
	`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'theme id',
	`pid` INT(10) NOT NULL COMMENT 'father id',
	`categoryName` VARCHAR(50) NOT NULL COMMENT 'Subject name',
	PRIMARY KEY (`categoryid`)
	) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

	-- insert data
	INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)
	VALUES('2','1','information technology'),
	('3','1','software development'),
	('4','3','database'),
	('5','1','Art design'),
	('6','3','web development'),
	('7','5','ps technology'),
	('8','2','Office information');

	-- to write SQL sentence,Show the parent-child relationship of the column (Parent column name,Sub column name)
	-- Core idea:Think of a table as like as two peas of two models.,Then join the two tables to query(Self connection)
	SELECT a.categoryName AS 'Parent column',b.categoryName AS 'Sub column'
	FROM category AS a,category AS b
	WHERE a.`categoryid`=b.`pid`

	-- Thinking questions:Query the information of students who took the exam(Student number,Student name,Account name,fraction)
	SELECT s.studentno,studentname,subjectname,StudentResult
	FROM student s
	INNER JOIN result r
	ON r.studentno = s.studentno
	INNER JOIN `subject` sub
	ON sub.subjectno = r.subjectno

	-- Query students and their grades(Student number,Student name,Grade name)
	SELECT studentno AS Student number,studentname AS Student name,gradename AS Grade name
	FROM student s
	INNER JOIN grade g
	ON s.`GradeId` = g.`GradeID`

	-- Query subject and grade(Account name,Grade name)
	SELECT subjectname AS Account name,gradename AS Grade name
	FROM SUBJECT sub
	INNER JOIN grade g
	ON sub.gradeid = g.gradeid

	-- Query database structure-1 All test results(Student number student name subject name grade)
	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'

Sorting and paging

	test

/*============== 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 ================
	grammar : SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
	benefit : (User experience,network transmission,Query pressure)

	deduction:
	   first page : limit 0,5
	   Page 2 : limit 5,5
	   Page 3 : limit 10,5
	   ......
	   The first N page : limit (pageNo-1)*pageSzie,pageSzie
	   [pageNo:Page number,pageSize:Number of single page displays]

*/

	-- 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

	-- query JAVA Information of the top 10 students with scores greater than 80 in the first academic year(Student number,full name,Course name,fraction)
	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='JAVA First academic year' AND StudentResult>=80
	ORDER BY StudentResult DESC
	LIMIT 0,10

Subquery

	/*============== 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'
	  )
	)

Common functions

	Data 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*/
		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 the student surnamed Zhou and change it to Zou
		 SELECT REPLACE(studentname,'Zhou ',' Zou ') AS new name
		 FROM student WHERE studentname LIKE 'Week% ';
		Date and time functions

		 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());
		System information function

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

Aggregate function

Function name Description

COUNT()	Return satisfaction Select The total number of records for the condition, such as select count(*) [Not recommended *，Low efficiency]
	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.
	 -- Aggregate function
	 /*COUNT:Non empty*/
	 SELECT COUNT(studentname) FROM student;
	 SELECT COUNT(*) FROM student;
	 SELECT COUNT(1) FROM student;  /*recommend*/
	 
	 -- In a sense, count(1) And count(*) Represents the query of all data rows.
	 -- count(field) The number of occurrences of this field in the table will be counted. The ignored field is null The situation. That is, the statistics field is not null Record of.
	 -- count(*) It includes all columns, equivalent to the number of rows. In the statistical results, the included fields are null Records of;
	 -- count(1) Use 1 to represent the code line. In the statistical results, the included fields are null Record of.
	 /*
	 Many people think count(1)Execution will be more efficient than count(*)High because count(*)There will be a full table scan, and count(1)You can query for a field. It's not, count(1)and count(*)The whole table will be scanned and the number of records will be counted, including those that are null Therefore, their efficiency can be said to be almost the same. and count(field)Different from the first two, it will count that the field is not empty null Number of records.

Here are some comparisons between them:

	 1)When the table has no primary key, count(1)than count(*)fast
	 2)When there is a primary key, the primary key is used as the calculation condition, count(Primary key)The highest efficiency;
	 3)If the table has only one field, then count(*)High efficiency.
	 */
	 
	 SELECT SUM(StudentResult) AS the sum FROM result;
	 SELECT AVG(StudentResult) AS average FROM result;
	 SELECT MAX(StudentResult) AS Highest score FROM result;
	 SELECT MIN(StudentResult) AS Lowest score FROM result;
	Title:

	 -- Query the average score of different courses,Highest score,Lowest score
	 -- premise:Group 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 s
		 ON r.subjectno = s.subjectno
		 GROUP BY r.subjectno
		 HAVING average>80;

	 /*
	 where Write before group by
	 If the filter is placed after the group
	 To use HAVING
	 Because having filters from the previously filtered fields, and where filters directly from the > field in the data table
	 */

MD5 encryption

1, Introduction to MD5

	MD5 Namely Message-Digest Algorithm 5(information-Abstract algorithm 5) is used to ensure the integrity and consistency of information transmission. It is one of the hash algorithms widely used in computers (also known as abstract algorithm and hash algorithm), and has been widely used in mainstream programming languages MD5 realization. Computing data (such as Chinese characters) into another fixed length value is the basic principle of hash algorithm, MD5 Its predecessor is MD2,MD3 and MD4.

2, Realize data encryption

Create a new table testmd5

	 CREATE TABLE `testmd5` (
	  `id` INT(4) NOT NULL,
	  `name` VARCHAR(20) NOT NULL,
	  `pwd` VARCHAR(50) NOT NULL,
	  PRIMARY KEY (`id`)
	 ) ENGINE=INNODB DEFAULT CHARSET=utf8
	Insert some data

	 INSERT INTO testmd5 VALUES(1,'kuangshen','123456'),(2,'qinjiang','456789')
	If we want to be right pwd This column of data is encrypted. The syntax is:

	 update testmd5 set pwd = md5(pwd);
	For a user alone(as kuangshen)Password encryption for:

	 INSERT INTO testmd5 VALUES(3,'kuangshen2','123456')
	 update testmd5 set pwd = md5(pwd) where name = 'kuangshen2';
	Insert new data automatically encrypted

	 INSERT INTO testmd5 VALUES(4,'kuangshen3',md5('123456'));
	Query login user information( md5 For comparison, check the encrypted password entered by the user for comparison)

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

Summary

	 -- ================ Built in function ================
	 -- Numerical function
	 abs(x)            -- absolute value abs(-10.9) = 10
	 format(x, d)    -- Format the millennial value format(1234567.456, 2) = 1,234,567.46
	 ceil(x)            -- Round up ceil(10.1) = 11
	 floor(x)        -- Round down floor (10.1) = 10
	 round(x)        -- Rounding off
	 mod(m, n)        -- m%n m mod n Remainder 10%3=1
	 pi()            -- Obtain pi
	 pow(m, n)        -- m^n
	 sqrt(x)            -- arithmetic square root 
	 rand()            -- random number
	 truncate(x, d)    -- intercept d Decimal place
	 
	 -- Time date function
	 now(), current_timestamp();     -- Current date and time
	 current_date();                    -- current date
	 current_time();                    -- current time 
	 date('yyyy-mm-dd hh:ii:ss');    -- Get date section
	 time('yyyy-mm-dd hh:ii:ss');    -- Get time section
	 date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j');    -- Format time
	 unix_timestamp();                -- get unix time stamp
	 from_unixtime();                -- Get time from timestamp
	 
	 -- String function
	 length(string)            -- string Length, bytes
	 char_length(string)        -- string Number of characters
	 substring(str, position [,length])        -- from str of position start,take length Characters
	 replace(str ,search_str ,replace_str)    -- stay str of use replace_str replace search_str
	 instr(string ,substring)    -- return substring First in string Location in
	 concat(string [,...])    -- connection string 
	 charset(str)            -- Returns the string character set
	 lcase(string)            -- Convert to lowercase
	 left(string, length)    -- from string2 From left in length Characters
	 load_file(file_name)    -- Read content from file
	 locate(substring, string [,start_position])    -- with instr,However, the start position can be specified
	 lpad(string, length, pad)    -- Reuse pad Add in string start,Until the string length is length
	 ltrim(string)            -- Remove front-end spaces
	 repeat(string, count)    -- repeat count second
	 rpad(string, length, pad)    --stay str Later use pad supplement,Until the length is length
	 rtrim(string)            -- Remove back-end spaces
	 strcmp(string1 ,string2)    -- Compare two string sizes character by character
	 
	 -- Aggregate function
	 count()
	 sum();
	 max();
	 min();
	 avg();
	 group_concat()
	 
	 -- Other common functions
	 md5();
	 default();

affair

What is a transaction

	A transaction is a set of SQL Statements are executed in the same batch
	If one SQL Statement error,Then all in the batch SQL Will be cancelled
	MySQL Transaction only supports InnoDB and BDB Data table type

ACID principle of transaction:

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). Transactions must always keep the system in a consistent state, no matter how many concurrent transactions there are at any given time. In other words, if multiple transactions are concurrent, the system must also operate as a serial transaction. Its main feature is protection and invariance. Taking the transfer case as an example, assuming that 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, for example, 5 yuan is transferred between accounts a and B and 10 yuan is transferred between accounts C and D, If 15 yuan is transferred between B and E, the total amount of the five accounts should still be 500 yuan, which is protective and invariable.

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.

Basic grammar

-- 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

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,
	PRIMARY KEY (`id`)
	) ENGINE=INNODB DEFAULT CHARSET=utf8

	INSERT INTO account (`name`,`cash`)
	VALUES('A',2000.00),('B',10000.00)

	-- Transfer realization
	SET autocommit = 0; -- Turn off auto submit
	START TRANSACTION;  -- Start a transaction,Mark the starting point of the transaction
	UPDATE account SET cash=cash-500 WHERE `name`='A';
	UPDATE account SET cash=cash+500 WHERE `name`='B';
	COMMIT; -- Commit transaction
	# rollback;
	SET autocommit = 1; -- Resume auto commit

Indexes

Function of index

	Improve query speed

	Ensure data uniqueness

	The connection between the accelerometer and the can be , Implement referential integrity between tables

	When using grouping and sorting clauses for data retrieval , It can significantly reduce the time of grouping and sorting

	Full text search fields for search optimization.

classification

	primary key  (Primary Key)

	unique index (Unique)

	General index (Index)

	Full text index (FullText)

primary key

	Primary key : A certain 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

unique index

	effect : 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`)
		)

General index

	effect : Quickly locate specific data

	be careful :

	index and key You can set a general index for any keyword

	Fields that should be added to query criteria

	Too many regular indexes should not be added,Affect data insertion,Delete and modify operations

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`);
		Full text index

Full text index

	effect : Quickly locate specific data

	be careful :

	Only for MyISAM Data table of type

	Only for CHAR , VARCHAR , TEXT Data column type

	Suitable for large data sets

/*
	#Method 1: when creating a table
	  　　CREATE TABLE Table name(
				   Field name 1 data type [integrity constraint...],
				   Field name 2 data type [integrity constraint...],
				   [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
				   [Index name] (field name [(length)] [ASC |DESC])
				   );


	#Method 2: CREATE creates 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 creates 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;
	*/

	/*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 for are given. The search is performed ignoring the case of letters. For each record row in the table, MATCH() Returns a correlation value 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.
	*/

Extension: test index

	Build table app_user:

CREATE TABLE `app_user` (
	`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
	`name` varchar(50) DEFAULT '' COMMENT 'User nickname',
	`email` varchar(50) NOT NULL COMMENT 'User mailbox',
	`phone` varchar(20) DEFAULT '' COMMENT 'cell-phone number',
	`gender` tinyint(4) unsigned DEFAULT '0' COMMENT 'Gender (0):Male; 1: female)',
	`password` varchar(100) NOT NULL COMMENT 'password',
	`age` tinyint(4) DEFAULT '0' COMMENT 'Age',
	`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
	`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app User table'

Batch insert data: 100w

	-- DROP FUNCTION IF EXISTS mock_data;

DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i < num DO
  INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)
   VALUES(CONCAT('user', i), '24736743@qq.com', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
  SET i = i + 1;
END WHILE;
RETURN i;
END;
SELECT mock_data();

Index efficiency test

No index

SELECT * FROM app_user WHERE name = 'User 9999'; -- Viewing time
	SELECT * FROM app_user WHERE name = 'User 9999';
	SELECT * FROM app_user WHERE name = 'User 9999';

EXPLAIN SELECT * FROM app_user WHERE `name`='User 9999';

SELECT * FROM student;

CREATE INDEX id_app_user_name ON app_user(`name`);

SELECT * FROM app_user WHERE `name`='User 9999';
EXPLAIN SELECT * FROM app_user WHERE `name`='User 9999';

	/*mysql> EXPLAIN SELECT * FROM app_user WHERE name = 'User 9999'\G
	*************************** 1. row ***************************
			  id: 1
	select_type: SIMPLE
		   table: app_user
	  partitions: NULL
			type: ALL
	possible_keys: NULL
			key: NULL
		key_len: NULL
			ref: NULL
			rows: 992759
		filtered: 10.00
		  Extra: Using where
	1 row in set, 1 warning (0.00 sec)*/
	Create index

	CREATE INDEX idx_app_user_name ON app_user(name);
	Test general index

	/*mysql> EXPLAIN SELECT * FROM app_user WHERE name = 'User 9999'\G
	*************************** 1. row ***************************
			  id: 1
	select_type: SIMPLE
		   table: app_user
	  partitions: NULL
			type: ref
	possible_keys: idx_app_user_name
			key: idx_app_user_name
		key_len: 203
			ref: const
			rows: 1
		filtered: 100.00
		  Extra: NULL
	1 row in set, 1 warning (0.00 sec)

	mysql> SELECT * FROM app_user WHERE name = 'User 9999 ';
	1 row in set (0.00 sec)

	mysql> SELECT * FROM app_user WHERE name = 'User 9999 ';
	1 row in set (0.00 sec)

	mysql> SELECT * FROM app_user WHERE name = 'User 9999 ';
	1 row in set (0.00 sec)*/

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

	-- 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;

User management:

	use SQLyog Create a user and grant permission to present

Basic 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
	  - *.* 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

Permission interpretation

	-- 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] ...

MySQL backup

	Database backup necessity

	Ensure that important data is not lost

	Data transfer

	MySQL Database backup method

	mysqldump Backup tools

	Database management tools,as SQLyog

	Directly copy database files and related configuration files

mysqldump client

	effect :

	Dump database 

	Collect database for backup

	Transfer data to another SQL The server,Not necessarily MySQL The server



	-- export
	1. 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)
	2. 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)
	3. Export all tables -- mysqldump -uroot -p123456 school >D:/a.sql
	　　mysqldump -u user name -p Password library name > file name(D:/a.sql)
	4. 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
	1. Logging in mysql In case of:-- source D:/a.sql
	　　source Backup file
	2. Without logging in
	　　mysql -u user name -p Password library name < Backup file

Standardized database design

	Why database design
	When the database is complex, we need to design the database

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 database E-R Model diagram , Confirm that the requirement information is correct and complete.

To design a database

	Collect information

	Communicate with relevant personnel of the system , an informal discussion , Fully understand user needs , Understand what the database needs to do.

	Identification entity[Entity]



	Identify the key objects or entities to be managed by the database,Entities are generally nouns

	Identify the details that each entity needs to store[Attribute]

	Identify relationships between entities[Relationship]

Three paradigms

	problem : Why data normalization?

	Problems caused by non-conforming table design:

	Duplicate information

	Update exception

	Insert exception

	Information cannot be represented correctly

	Delete exception

	Missing valid information

Three paradigms

	First paradigm (1st NF)

	The goal of the first paradigm is to ensure the atomicity of each column,If each column is the smallest non separable data unit,The first paradigm is satisfied

	Second paradigm(2nd NF)

	Second paradigm (2) NF)Is in the first paradigm (1 NF)It is established on the basis of the second paradigm (2) NF)The first paradigm (1) must be satisfied first NF). 

	The second paradigm requires each table to describe only one thing

	Third paradigm(3rd NF)

	If a relationship satisfies the second paradigm,Besides, columns other than the primary key are not passed and depend on the primary key column,The third paradigm 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

	To meet a business goal , Database performance is more important than normalized database

	While data standardization , We should comprehensively consider the performance of the database

	By adding additional fields to a given table,To significantly reduce the time it takes to search for information

	By inserting calculated columns into a given table,To facilitate query

reference:
https://mp.weixin.qq.com/s?__biz=Mzg2NTAzMTExNg==&mid=2247484182&idx=1&sn=6a04b10ebac95da1a66abcefa1fa442c&chksm=ce6105b5f9168ca39ef07ff13f55700d4c6acfd5b5bfbe216eca72d6db3d7a09c312a4300f8e&mpshare=1&scene=1&srcid=0518rhuasEYr2UU8QYf8AJUG&sharer_sharetime=1621322690852&sharer_shareid=fd9cc2b7f0d6e8dd7f6b71ff8c701fba&exportkey=A%2FeJnd9ve9KtVuchITojVdE%3D&pass_ticket=I1GXvONyzKgVk3gc8VPMOnSTC3xzKiR%2Bv499x7T%2FiT0OWKfH82KrRgBUPghBgKCG&wx_header=0#rd

https://mp.weixin.qq.com/s?__biz=Mzg2NTAzMTExNg==&mid=2247484193&idx=1&sn=1b499f803d7b7dfec07fb2e41237be35&chksm=ce610582f9168c94542e99e5bd213347e59c707871fe3a41957ef60e7ec5dfd6a3b1e4e1523e&mpshare=1&scene=1&srcid=0518zMnGb1971Xepx9n8TzC5&sharer_sharetime=1621332383533&sharer_shareid=fd9cc2b7f0d6e8dd7f6b71ff8c701fba&exportkey=A0IyPfix7gNiOGQxTW4ozbA%3D&pass_ticket=I1GXvONyzKgVk3gc8VPMOnSTC3xzKiR%2Bv499x7T%2FiT0OWKfH82KrRgBUPghBgKCG&wx_header=0#rd

https://mp.weixin.qq.com/s?__biz=Mzg2NTAzMTExNg==&mid=2247484199&idx=1&sn=1082761b7ffef3cb20a8b29f016f0a31&chksm=ce610584f9168c92869bc42eb4fbfefe3e722a5477d8e520060a909644b127c0ef113b8ebb03&mpshare=1&scene=1&srcid=0519yrbPqwpm6qpx2Pbl6wjG&sharer_sharetime=1621418370331&sharer_shareid=fd9cc2b7f0d6e8dd7f6b71ff8c701fba&exportkey=A00NPmTZaueAyEkog86YUrA%3D&pass_ticket=I1GXvONyzKgVk3gc8VPMOnSTC3xzKiR%2Bv499x7T%2FiT0OWKfH82KrRgBUPghBgKCG&wx_header=0#rd

https://mp.weixin.qq.com/s?__biz=Mzg2NTAzMTExNg==&mid=2247484207&idx=1&sn=2ba333652e06e6b2dcfa949de09aea70&chksm=ce61058cf9168c9aa847f2b3ad54476596816d2a6a04a9481aa6bc28f435b09fb422ac2262e0&mpshare=1&scene=1&srcid=05205DOQkdBBA79DBUUQ3SG0&sharer_sharetime=1621502386509&sharer_shareid=fd9cc2b7f0d6e8dd7f6b71ff8c701fba&exportkey=AwBzOo6%2FPGuKlOTSLmAQph8%3D&pass_ticket=I1GXvONyzKgVk3gc8VPMOnSTC3xzKiR%2Bv499x7T%2FiT0OWKfH82KrRgBUPghBgKCG&wx_header=0#rd

https://mp.weixin.qq.com/s?__biz=Mzg2NTAzMTExNg==&mid=2247484217&idx=1&sn=b2a3463dc50417a571bd551749701065&chksm=ce61059af9168c8cc71e2d16976815f4d4c1f24b07bd9e159691eb5ceaba81036bd93bb2e805&mpshare=1&scene=1&srcid=0523A5IsT1OmJCAPeL0oRUni&sharer_sharetime=1621734813952&sharer_shareid=fd9cc2b7f0d6e8dd7f6b71ff8c701fba&exportkey=A7Fv3jM8jujCvnGyhTpqtGo%3D&pass_ticket=I1GXvONyzKgVk3gc8VPMOnSTC3xzKiR%2Bv499x7T%2FiT0OWKfH82KrRgBUPghBgKCG&wx_header=0#rd

https://mp.weixin.qq.com/s?__biz=Mzg2NTAzMTExNg==&mid=2247484221&idx=1&sn=ca4493b916139ebd136835491c62cc8a&chksm=ce61059ef9168c887ba7eb0317c11c6ac3b05e80c34c1331ae30d4b8d0e47412a43cfc6e6105&mpshare=1&scene=1&srcid=0525dszyLCkn96WM3Usf9k85&sharer_sharetime=1621910332279&sharer_shareid=fd9cc2b7f0d6e8dd7f6b71ff8c701fba&exportkey=A3Jpe9vyKCHlP2qN1zMAXB4%3D&pass_ticket=3u8qQK%2BK93KVI5xTtsxBqyiKWbw6Gdu2XTzPU%2FR6HZWAwmpLKmI5IW498srEXoeT&wx_header=0#rd

https://mp.weixin.qq.com/s?__biz=Mzg2NTAzMTExNg==&mid=2247484227&idx=1&sn=2523e6a5081a0fbf698a39ed63adc60e&chksm=ce6105e0f9168cf635a071a1f2ad1af4341593d76650a1c4b08db33072be57f7b18925c73452&mpshare=1&scene=1&srcid=0525Q9INAqXTWTKM67f8nHgt&sharer_sharetime=1621910358811&sharer_shareid=fd9cc2b7f0d6e8dd7f6b71ff8c701fba&exportkey=AzMsQanh1y%2BluInePxFeSV8%3D&pass_ticket=3u8qQK%2BK93KVI5xTtsxBqyiKWbw6Gdu2XTzPU%2FR6HZWAwmpLKmI5IW498srEXoeT&wx_header=0#rd

Keywords: Database MySQL

Added by bradcis on Mon, 06 Dec 2021 03:59:12 +0200

Programming VIP

MySQL basic syntax

Several basic database operation commands:

Database operation (just understand)

Command line operation database

The column type of the database

Value:

character string:

Time date:

Field properties of database (emphasis):

Each table must have the following five fields (understand)

To create a database table:

format

Common commands:

Type of data table:

Experience (where applicable):

Storage location of data table:

The MyISAM type data table corresponds to three files:

Set the character set encoding of the database table:

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

Modify database:

Delete data table:

DML language:

Foreign key concept

Foreign key action:

1. Specify foreign key constraints when creating tables:

2. Modify after table creation:

DML language (remember all):

How to manage database data:

DML: Data Manipulation Language

Add data:

Syntax:

Modify data:

Delete data:

DQL language

SELECT syntax:

Specify query fields:

Use of DISTINCT keyword:

Columns using expressions

Expressions used in where conditional statements in DML statements:

where conditional statement:

Fuzzy queries: comparison operators

join query

Self connection

Sorting and paging

Subquery

Common functions

Aggregate function

Function name Description

Here are some comparisons between them:

MD5 encryption

1, Introduction to MD5

2, Realize data encryption

Summary

affair

What is a transaction

ACID principle of transaction:

Atomicity:

Consistency:

Isolated:

Persistent:

Basic grammar

test

Indexes

Function of index

classification

primary key

unique index

General index

Full text index

Extension: test index

Batch insert data: 100w

Index efficiency test

Index criteria:

Indexed data structure

User management:

Basic command

Permission interpretation

MySQL backup

mysqldump client