This article is from WeChat official account GitHub: From getting started to getting into the ground: a complete learning guide to MySQL, including teaching and learning!

1. Introduction to SQL

SQL(Structured Query Language), whose semantics is a structured language, is an ANSI standard computer language.

2. Introduction to database

1. Database

A database is a container that holds a file or group of files.

2. Database management system

Database management system is used to manage databases, such as MySQL, Access, DB2, Informix, Server, Oracle, Sybase, etc.

3. Table

A table is a structured file that can be used to store specific types of data.

The table name of each table is unique and cannot be repeated.

4. Columns and data types

Column is a field in a table. A table is composed of multiple columns; Each column consists of a specific data type and can only store data of the specified data type.

Data type is to limit that each column in the table can only store specific types of data. Common data types include integer, number, text, string, date, etc.

5. OK

A row is a record in a table.

6. Primary key

The primary key is the unique identifier of each row. The characteristic is the primary key. It cannot be empty, duplicate or modified.

7. Line number

Row number refers to the row number of each row in the table.

3. Basic search

Install MySQL and connect to the database using Navicat.

Create a new student table. The columns are id, name, age and student info.

Create table statement:

CREATE TABLE IF NOT EXISTS `student` (
	`id` INT AUTO_INCREMENT,
	`name` VARCHAR (100) NOT NULL,
	`age` INT NOT NULL,
	`info` VARCHAR (40) NOT NULL,
	PRIMARY KEY (`id`) 
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;

• ENGINE=InnoDB sets the storage engine and CHARSET specifies the encoding format

Insert data into the table:

INSERT INTO `student` (id,name,age,info) VALUES (1,'user1',18,'Freshman');
INSERT INTO `student` (id,name,age,info) VALUES (2,'user2',20,'graduate');
INSERT INTO `student` (id,name,age,info) VALUES (3,'user3',27,'Social personage');
INSERT INTO `student` (id,name,age,info) VALUES (4,'user4',17,'Senior three students');

1. select keyword

SQL is a statement composed of many keywords. Keywords are reserved words in the database. Users cannot use them as table names and fields for creating tables; The data retrieval in the table uses the select keyword as the beginning to query the information of the database table.

2. Retrieve a single field

SELECT name FROM student;

user1
user2
user3
user4

3. SQL statement precautions

• Multiple SQL statements are executed together using semicolons; separate;
• When spelling statements, it is recommended to use uppercase for table keywords, and lowercase for table fields and table names;
• For ease of reading, it is recommended to divide SQL into multiple lines;
• SQL language also uses English letters. Do not open Chinese, so as not to cause symbol errors and not easy to be found;
• SQL statements are case insensitive by default
• SQL statement table name or field name can be without quotation marks. If quotation marks are used, use ` instead of single and double quotation marks;
• The value value corresponding to the field name of SQL statement can use single and double quotation marks.

4. Retrieve multiple fields

SELECT name,age FROM student;

user1	18
user2	20
user3	27
user4	17

5. Retrieve all fields

SELECT * FROM student;

1	user1	18	Freshman
2	user2	20	graduate
3	user3	27	Social personage
4	user4	17	Senior three students

• Wildcard * indicates that all columns in the table are returned. It is not necessary. It is not recommended to use wildcards, which will affect the performance of the database.

6. distinct weight removal

distinct indicates distinction, which means that the retrieved row is unique (de duplication), which is placed in the front of the column;

If the keyword distinct is used, it applies to all subsequent columns.

• Add a list of existing data:

INSERT INTO `student` (id,name,age,info) VALUES (5,'user4',17,'Senior three students');

• Then query

SELECT DISTINCT name,age FROM student;

user1	18
user2	20
user3	27
user4	17

• user4 is filtered

7. Number of restrictions

access and sql server:

SELECT TOP 2 FROM student;

• TOP 2 means to limit the return of the first 2 lines

postgresql, SQLLite, and MySQL:

SELECT name FROM student LIMIT 2;

• LIMIT 2 means to limit the return of the first 2 lines

• Execution result:

  user1
  user2
  

DB2:

SELECT name FROM student FETCH FIRST 2 ROWS ONLY;

• FETCH FIRST 2 ROWS ONLY means that only the first 2 rows of data are fetched

8. Offset

SELECT name  FROM student LIMIT 1 OFFSET 1;

• Indicates that the name of the query column comes from the student table, the limit number of entries is 1, and the offset value is 1;

• It means to query the second row of data in the student table;

• Jump or offset

• Execution result:

  user2
  

MySQL and MariaDB simplified form:

SELECT name FROM student LIMIT 1,2;

• Indicates that the query field name comes from the student table, with 2 restrictions and 1 offset;

• Pay attention to the order

• Execution result:

  user2
  user3
  

4. Sequential search

1. ORDER BY clause

SELECT name,age FROM student ORDER BY age;

• The name of the search field, which comes from the student table and is sorted according to the age of the column;

• Note that the default is ascending ASC;

• The ORDER BY clause is usually at the end of a statement

• Execution result:

  user4	17
  user4	17
  user1	18
  user2	20
  user3	27
  

2. Multi column sorting

SELECT name,age FROM student ORDER BY age DESC, name ASC;

• Query the name. The age comes from the student table and is sorted by age in descending order and name in ascending order;

• The keyword DESC(descending) refers to descending order, and the letter defaults to Z-A,

• ASC(ascending) refers to ascending order, and the letters default to A-Z;

• In the case of multiple columns, DESC is specified after each column, separated by commas. If you do not write, the default is ascending.

• Execution result:

  user3	27
  user2	20
  user1	18
  user4	17
  user4	17
  

3. Sort DESC and ASC by bit

SELECT name,age FROM student ORDER BY 2 DESC, 1 ASC;

• Bit by bit refers to the position of the query field. 2 corresponds to the field age and 1 corresponds to the field name. The result is consistent with the sorting of multiple columns

• Execution result:

  user3	27
  user2	20
  user1	18
  user4	17
  

5. Filter and search

The keyword of filter condition in SQL statement is WHERE, followed by the table name.

1. WHERE statement operator

Different database management systems support slightly different operators.

Operator	explain
=	be equal to
>	greater than
<	less than
!=	Not equal to
<>	Not equal to
>=	Greater than or equal to
<=	Less than or equal to
!<	Not less than
!>	Not greater than
BETWEEN	In the middle
IS NULL	Empty

2. Single condition filtering WHERE

SELECT * FROM student WHERE name = 'user1'

• Output result:

  1	user1	18	Freshman
  

3. Multi condition filtering AND and OR

Use the AND OR clause:

• The AND connection expression represents the data whose filtering conditions are true;
• The OR join expression represents any one of the matching filter conditions

AND example:

SELECT * FROM student WHERE age >= 18 AND age <= 23;

• Conditions: the student's age is greater than or equal to 18 and the student's age is less than 23

• Execution result:

  1	user1	18	Freshman
  2	user2	20	graduate
  

OR example:

SELECT * FROM student WHERE age >= 18 OR age <= 23;

• Conditions: the student's age is greater than or equal to 18 or the student's age is less than 23

AND and OR examples:

SELECT * FROM student WHERE age >= 18 AND (age <= '23' OR id >= 2);

• When using OR AND, the filtering conditions should be clear AND enclosed in parentheses, because the database management system is executed in order, AND it is easy to cause semantic errors without parentheses;

• Filter criteria: query data with age greater than 18 and (age greater than or equal to 23 or id greater than or equal to 2)

• Query results:

  1	user1	18	Freshman
  2	user2	20	graduate
  3	user3	27	Social personage
  

4. Range query BETWEEN

SELECT * FROM student WHERE age BETWEEN 18 AND 23

• Query age between 18 and 23 (including 18 and 23)

• Query results:

  1	user1	18	Freshman
  2	user2	20	graduate
  

5. Null value query IS NULL

SELECT * FROM student WHERE age IS NULL

• The database table is not filled with data, and the default value is null. Of course, you can also set the default value for the column of the specified type

• Filter criteria: query data with empty age

• Query result (because the data age of the insert has a value, the return is empty):

  empty
  

6. IN operation

SELECT * FROM student WHERE age IN (18,20,27);

• Query criteria: Data aged 18 or 20 or 27

• IN is a range query that matches any value specified IN parentheses. The function is similar to OR. An IN is equivalent to multiple ors

• Query results:

  1	user1	18	Freshman
  2	user2	20	graduate
  3	user3	27	Social personage
  

7. NOT operator

SELECT * FROM student WHERE NOT age = 20;

• The NOT operator means negative, followed by WHERE, and its function is similar to < >

• Query results:

  1	user1	18	Freshman
  3	user3	27	Social personage
  4	user4	17	Senior three students
  5	user4	17	Senior three students
  

Query IN and NOT:

SELECT * FROM student WHERE NOT age IN (20, 27);

• Query criteria: data whose age is not in 20 or 27

• Execution result:

  1	user1	18	Freshman
  4	user4	17	Senior three students
  5	user4	17	Senior three students
  

6. Wildcard search

1. Introduction to wildcards

Wildcards: special strings that make up a matching pattern. The wildcard of the retrieved text is used after the keyword LIKE.

2. Wildcard%

Before matching characters:

SELECT * FROM student WHERE name LIKE '%er2'

• Query criteria: data whose name starts with any character and ends with er2

• %Represents any string containing 0 but not null

• Query results:

  2	user2	20	graduate
  

After matching characters:

SELECT * FROM student WHERE name LIKE '%o%';

• Query criteria: data whose name starts and ends with any character and whose character contains s

3. Wildcard_

Wildcard_ Match a string. Not in Access database_ But?

SELECT * FROM student WHERE name LIKE '_ser3';

• Query criteria: data matching an arbitrary character before the name ser3:

• Execution result:

  3	user3	27	Social personage
  

4. Wildcard []

Wildcard [] matches one character in one position. Multiple characters can be stored in it. The relationship is or. Only one position is occupied in pattern matching. Access and SQL Server support

[24] General Query:

SELECT * FROM student WHERE name REGEXP '[24]';

• Query criteria: query the data whose name contains 2 or 4

• Return result:

  2	user2	20	graduate
  4	user4	17	Senior three students
  5	user4	17	Senior three students
  

[2-4] range query:

SELECT * FROM student WHERE name REGEXP '[2-4]';

• Query criteria: query the data whose name contains 2 to 4, that is, the data whose name contains 2 or 3 or 4

• Return result:

  2	user2	20	graduate
  3	user3	27	Social personage
  4	user4	17	Senior three students
  5	user4	17	Senior three students
  

7. Basic field operation

1. Field splicing

SELECT CONCAT('How do you do',name,'How is the weather today?') FROM student WHERE id = 1;

• CONCAT: combine (splice) multiple arrays or strings into one string

• Different database management systems are used in slightly different ways:

  • MySQL uses concat function
  • postgresql use||
  • Access and SQL server use+

• Execution result:

  How do you do user1 How is the weather today?
  

2. Remove Blank strings

SELECT RTRIM('    Brother, be full today     ') FROM student WHERE id=1;
SELECT LTRIM('    Brother, be full today     ') FROM student WHERE id=1;
SELECT TRIM('    Brother, be full today     ') FROM student WHERE id=1;

• RTRIM(str) function removes the empty string on the right;

• LTRIM(str) function removes the empty string on the left;

• The TRIM(str) function removes empty strings on both sides.

• Operation results:

      Brother, be full today  # Empty string on the left
   Brother, be full today     # Empty string after
   Brother, be full today  # There is no empty string on either side
  

as. Alias 3

# Alias fields
SELECT name as student_name FROM student WHERE id=1

# Alias table
SELECT name FROM student as s WHERE id=1

# Alias fields and tables
SELECT name as student_name FROM student as s where id=1

• Execution result (only field alias is reflected):
  

4. Calculation

Operator	explain
*	ride
+	plus
-	reduce
/	except

SELECT 2 * 8;

• result:
  

8. Use of aggregation function

First create three tables and add data:

• Customer list:

CREATE TABLE IF	NOT EXISTS `customer` (
	`user_id` INT AUTO_INCREMENT COMMENT 'customer id',
	`username` VARCHAR( 255 ) NULL COMMENT 'Customer name',
	`telephone` VARCHAR( 255 ) NULL COMMENT 'Customer telephone',
	PRIMARY KEY ( `user_id` ) 
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO customer (`user_id`, `username`, `telephone`) VALUES (1, 'zxzxz', '1327');
INSERT INTO customer (`user_id`, `username`, `telephone`) VALUES (2, 'youku1327', '1996');

• Commodity list

CREATE TABLE IF	NOT EXISTS `product` (
	`product_id` INT AUTO_INCREMENT COMMENT 'commodity id',
	`product_name` VARCHAR ( 255 ) NULL COMMENT 'Trade name',
	`price` VARCHAR ( 255 ) NULL COMMENT 'commodity price',
	PRIMARY KEY ( `product_id` ) 
	);

INSERT INTO product ( `product_id`, `product_name`, `price` ) VALUES ( 1, 'Apple', '5' );
INSERT INTO product ( `product_id`, `product_name`, `price` ) VALUES ( 2, 'Pear', '4' );
INSERT INTO product ( `product_id`, `product_name`, `price` ) VALUES ( 3, 'Banana', '3' );

• Order form

CREATE TABLE IF NOT EXISTS `order` (
`id` INT NOT NULL AUTO_INCREMENT COMMENT'order id',
`user_id` INT NULL COMMENT 'customer id',
`product_id` INT NULL COMMENT 'commodity id',
`order_name` VARCHAR(255) NULL COMMENT 'Order name',
PRIMARY KEY (`id`)
);

INSERT INTO `order` (`id`, `user_id`, `product_id`, `order_name`) VALUES(1, 1, 1, 'Good order');
INSERT INTO `order` (`id`, `user_id`, `product_id`, `order_name`) VALUES(2, 2, 2, 'Yueyue order');
INSERT INTO `order` (`id`, `user_id`, `product_id`, `order_name`) VALUES(3, 1, 3, 'Cocoa order');

Definition of aggregation function: run some functions with some rows of data to return an expected value.

1. AVG (average)

The avg() function calculates the number of rows and calculates the average value by calculating the sum of specific column values of these rows (sum of specific column values / number of rows = average value). When using, note that it will ignore rows with null column value:

SELECT AVG(price) FROM product;

• Statement analysis: query the average price from the commodity table (5 + 4 + 3) / 3 = 4;

• result:
  

2. count() calculates the number of rows

The count() function calculates the number of rows. Count (*) calculates the number of all rows. count("column") ignores the number of rows with NULL column:

SELECT count(*) FROM product;

• Statement analysis: the total query function comes from the commodity table

• result:
  

3. max() maximum value of column value

The max() function returns the maximum value of a specific column value and ignores rows whose specific column is NULL:

SELECT MAX(price) FROM product;

• Statement analysis: the maximum value of query price comes from the commodity table

• result:
  

4. Min (minimum value of column value)

The min() function returns the minimum value of a specific column and ignores rows whose specific column is NULL:

SELECT MIN(price) FROM product;

• Statement analysis: the minimum value of query price comes from the commodity table

• result:
  

5. sum() of specific columns

sum() returns the sum of a specific column and ignores rows whose specific column is NULL:

SELECT SUM(price) FROM product;

• Statement analysis: the sum of query prices comes from the commodity table

• result:
  

9. Grouped data

Grouping definition: Group queries are performed according to specific columns, and group queries are performed using the GROUP BY clause.

be careful:

• The column after SELECT must appear after the group by clause, otherwise a syntax error will be reported;
• Usually, the position of the group by clause is after the where condition and before the order by clause.

1. Group summation

Insert the data of a pear (peoduct_id is 4):

SELECT product_name, SUM(price) FROM product GROUP BY product_name;

• Sentence analysis: first, apple, pear and banana are divided into three groups according to the commodity name; Then sum according to different groups

• result:
  

2. Group filtering

SELECT COUNT(*) FROM `order` GROUP BY user_id HAVING COUNT(*) > 1;

• Statement analysis: query the order table, group according to user id, and the number of filter conditions is greater than 2

• Note: there is little difference between having and where:

  • where is usually used as a standard filter condition
  • having is used as grouping filter condition
  • Some database management systems, having, do not support aliases as part of group filtering criteria

• result:
  

3. Grouping and sorting

SELECT COUNT(*) as count FROM `order` GROUP BY user_id ORDER BY count;

• Statement analysis: query the order table, group according to customer id and sort according to the number of rows

• Note: after grouping, the results appear to be sorted, but in fact, they cannot be guaranteed to be sorted. Therefore, to sort, you must use the order by clause

• result:
  

10. Sub query

Subquery: nested query in query.

Note: sub query can only return single column. If you attempt to return multiple columns, syntax error will be reported.

SELECT username FROM customer WHERE user_id = ( SELECT user_id FROM `order` WHERE order_name = 'Good order' );

• Statement analysis: first look at the in parentheses and query the user in the order table_ ID, assigned to user outside parentheses_ ID, and then query the username of the customer table.
• result:
  

11. Connection table

Join table is the query of association table. Its main function is to use one SQL to retrieve the expected value in multiple tables, but it exists in the actual database table and only exists during the query period.

Classification:

• join keyword used by inner and outer connections
• The join table will return one to many, one to one, and many to many relationships
• It is not recommended to have more than three connection tables

1. Simple link

SELECT username,order_name FROM customer,`order` WHERE customer.user_id = `order`.user_id;

• Statement analysis: query the user in the customer and order tables (username from the customer table and order_name from the order table)_ Data with equal ID, return username and order_ name
• Note: the simple join where clause must be followed by the join relationship between two tables, otherwise Cartesian sets will appear (for example, 3 * 3 = 9 entries will be generated when 3 rows of data are joined to another table)
• result:

2. Inner connection

Inner join is also called equivalent join, and its query result is consistent with the previous simple join.

SELECT username,order_name FROM customer INNER JOIN `order` ON (customer.user_id = `order`.user_id);

• Statement analysis: slightly different from the previous simple join, the equivalence condition is placed after the on keyword, and the where clause filter condition query can be performed after the equivalence condition
• result:
  

3. Natural connection

Unlike standard joins, natural joins only return columns with unique values and do not return duplicate columns.

1. Example of natural connection

SELECT username,order_name FROM customer INNER JOIN `order` ON (customer.user_id = `order`.user_id);

• result:
  

2. Unnatural connection

SELECT * FROM customer INNER JOIN `order` ON (customer.user_id = `order`.user_id);

• result:
  
  Repeat user_id.

4. Outreach

1. Right outer connection

Right OUTER JOIN is to query all the data of the right table and the data matching the left table according to the equivalence condition relative to the table on the right of OUTER JOIN. If the data of the left table does not match, the value of the returned column is NULL.

SELECT * FROM `order` RIGHT OUTER JOIN customer ON (customer.user_id = `order`.user_id);

• result:
  

2. Left outer connection

The left OUTER JOIN is to query all the data of the left table and the data matching the right table according to the equivalence condition relative to the table on the left of the OUTER JOIN. If the data of the right table does not match, the value of the returned column is NULL.

SELECT * FROM customer LEFT JOIN `order` ON customer.user_id = `order`.user_id);

• result:
  

The difference between left and right outer joins: in fact, there is no difference, but the order of the query table is inconsistent. The same result can be found by replacing the relative position of the table.

12. Query combination

Combined query: also known as "compound operation", it can execute multiple select statements. Its query structure is consistent and returns query results.

SELECT user_id FROM customer UNION SELECT user_id FROM `order`;

• result:
  

• Statement analysis: the fields or aggregate functions associated with the union must be the same in the two tables, and the results will be de reprocessed by default

• If you do not want to remove the duplicate, you can use union all:

SELECT user_id FROM customer UNION ALL SELECT user_id FROM `order`;

• result:
  
• Statement analysis: it is equivalent to merging the user IDs of the customer table and the order table into a union for query without duplication; If you sort the combined statements, it will be applied to the sorting of the combined data fields by default, rather than one of the query statements

13. Insert

Inserting database records uses the insert keyword to insert a statement into the database. Advanced can combine the select keyword to insert the result set of the query and the whole table.

• Create table statement:

CREATE TABLE IF NOT EXISTS `user` (
`id` INT NOT NULL AUTO_INCREMENT COMMENT 'user id',
`name` VARCHAR(255) NULL COMMENT 'user name',
`telephone` VARCHAR(11) NULL COMMENT 'cell-phone number',
PRIMARY KEY (`id`)
);

1. Insert a complete piece of data

INSERT INTO `user`(id,`name`,telephone) VALUES (2,'zszxz','1327');

• Statement analysis: insert data into the user table. The fields are id, name and telephone, and the values are 2, 'zszxz' and '1327'

• INTO can be ignored without writing, but it is not recommended, because there will be portability problems between database management systems;

• Other fields can also be ignored and not written, but not recommended, which is easy to cause errors in inserting data;

• The position of the field corresponds to the position of the value one by one. If there is no value in some positions, NULL can be used instead

• result:
  

2. Insert some data

INSERT INTO user (id,name) VALUES (3, 'zszxz');

• Statement analysis: insert data into the user table. The fields are id and name respectively, and the values are 3 and zszxz respectively. No telephone field is inserted

• result:
  

3. Insert search data

Insert the results of the query into another table, which can be combined into one statement using the insert select key.

INSERT INTO `user` (id,`name`) SELECT id,`name` FROM student WHERE id = 4;

• Statement analysis: insert it into the user table. The fields are id and name respectively. The value is the query field id. the name comes from the student table. The condition is that the id is equal to 4. The columns are in the same order. Name matching is recommended

• result:
  

• Data of student table id=4

• Data inserted into the user table

4. Copy table

Retrieve the data of one table and insert it into another table. There are two methods, but different database management systems support different methods, as follows:

MySQL:

CREATE TABLE student_copy AS SELECT * FROM student;

• Statement analysis: creating table student_copy data structure source query all fields are from the student table

• Error SQL:

  SELECT id,name INTO student_copy FROM student;

14. Update

The update keyword is used to update the rows of the database. The update operation is a very dangerous operation. You should check whether the where clause is missing before each execution.

1. Update all rows

UPDATE student_copy set age = 20;

• Statement analysis: updating student_copy table, set the field age value to 20, and you can see that all ages in the table have changed to 20;
• If there are multiple fields that need to be updated, separate them with commas

result:

2. Update specific rows

UPDATE student_copy SET age = 18 where id=4;

• Statement analysis: updating student_copy table, set the age of students equal to 18, and the condition is that the id is equal to 4

• result:
  

3. Update the result set from the query

UPDATE student_copy INNER JOIN student on student.id = student_copy.id SET student_copy.age = student.age, student_copy.`name` = student.`name`;

• Statement analysis: updating student_ The copy table is associated with the student table, provided that the id of the student table is equal to student_ id of copy table, set student_ The age of the copy table is the age of the student table, and the name is the name of the student table.

15. Delete table

The delete keyword can be used to delete rows in the table. You can delete specific rows or all of them. When using it, first see whether the where clause is lost.

1. Delete the whole table data

DELETE FROM student_copy;

• Statement analysis: delete all rows from student_copy table

2. Delete a specific line

DELETE FROM student WHERE id = 4;

• Statement analysis: delete the row from the student table if the id is equal to 4

3. Suggestions for updating and deleting

• Check whether the where clause is missing before each operation
• It is best to use the select statement to verify before each operation

16. SQL classification

1. SQL classification

There are three types of SQL operations on the database. If you learn these three SQL languages and operate the database skillfully, you will enter the room; If you learn advanced database operation, you have some experience in using the database; If you learn to optimize the database, divide the database into tables, and separate reading and writing, you have reached the expert level.

• DDL: data definition language, which defines the structure of data. For example, create, drop and alter operations
• DML: data management language, adding, deleting, modifying and querying. For example, insert, delete, update and select operations
• DCL: data control language, which controls permissions, transactions, etc. For example: Grant, revoke, commit, roolback, etc.

2. Basic operation of database

1. Connect to the database

mysql -h address - P port - u user name - P password

mysql -h 192.168.0.127 -P 3306 -u root -p root

2. View the current database

SELECT DATABASES();

3. Display user active threads

SHOW PROCESSLIST;

4. Display system variables

SHOW VARIABLES;

5. Display the current time, user and database version number

SELECT now(), user(), version();

6. Create database

CREATE DATABASE[IF NOT EXISTS] database name [database options]

• Database options:
  • CHARACTER SET character set name
  • COLLATE collation name

create database demo;

7. Delete database

DROP DATABASE [IF EXISTS] database name;

drop database demo;

3. CREATE TABLE statement

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] [library name.] Table name (structure definition of the table) [table options]

• TEMPORARY indicates a TEMPORARY table, and the contents in brackets indicate optional, which is often used in formal database version management development.

• The fields are decorated with the following data types:

  • Non null | NULL constraint: [NOT NULL | NULL]
  • Default value: [default DEFAULT default_value]
  • Automatic growth: [AUTO_INCREMENT]
  • Unique key | primary key: [UNIQUE[KEY] | [PRIMARY KEY]]
  • Remarks: [COMMENT 'string']

• Table options are generally to formulate database engine and character set:

  ENGINE=InnnoDB DEFAULT CHARSET=utf8 COMMENT = "customer table"

• Example:

CREATE TABLE IF	NOT EXISTS `customer` (
	`id` INT AUTO_INCREMENT COMMENT 'Primary key',
	`customer_name` VARCHAR( 255 ) NULL COMMENT 'Customer name',
	`gender` varchar(255) NULL COMMENT 'Gender',
	`telephone` VARCHAR( 255 ) NULL COMMENT 'Telephone number',
	`register_time` timestamp NULL DEFAULT NULL COMMENT 'Registration time',
	PRIMARY KEY ( `user_id` ) 
	) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Customer table';

4. Modify table structure

1. View all tables

SHOW TABLES

2. View the table of the specified database

SHOW TABLES FROM database name

SHOW TABLES FROM demo_database;

3. Delete table

DROP TABLE [IF EXISTS] table name;

drop table op;

4. Empty table (empty data)

TRUNCATE [TABLE] table name

5. Copy table structure

CREATE TABLE name LIKE the table name to be copied;

create table op like `order`;

6. Copy table structure and data

CREATE TABLE table name [AS] SELECT * FROM table name to be copied;

CREATE TABLE op AS SELECT * FROM `order`;

7. Common alter operations

• Append a column (append to the end)

alter table [database name.] Table name add [column] field data type;

alter table `order` add column `year` year;

• Add to first column

alter table [database name.] Table name add [column] field data type first;

• Add a column to the specified field name

alter table [database name.] Table name add [column] field data type after another field;

• Modify the data type of the field name

alter table [database name.] Table name modify [column] field name new data type;

alter table `order` modify column `gender` tinyint;

• Modify the data type of the table field and move to the first column

alter table [database name.] Table name modify [column] field name data type first;

• Modify the data type of the table field and move it after the specified field

alter table [database name.] Table name modify [column] field name data type after another field name;

• Modify the name of the table field

alter table [database name.] Table name change [column] old field name new field name data type;

• Add primary key

alter table [database name.] Table name ADD PRIMARY KEY (field name);

alter table `order` add primary key (`id`);

• Add unique key

alter table [database name.] Table name ADD UNIQUE [index name] (field name)

• Add index

alter table [database name.] Table name ADD INDEX [index name] (field name)

• Delete a column

alter table [database name.] Table name drop [column] field name

alter table `order` drop column `gender`;

• Delete index

alter table [database name.] Table name DROP INDEX index name

• Delete primary key

alter table [database name.] Table name DROP PRIMARY KEY

• Delete foreign key

alter table [database name.] Table name DROP FOREIGN KEY

17. View

1. Concept of view

A view is a virtual table, which is essentially a SQL retrieval statement and does not store any data components.

View benefits:

• Simplify query SQL and query the view directly, regardless of the details of view generation;
• The part of the available table becomes the view to protect the data. When opening the user permission, you can only open the view instead of the entity table;
• By modifying the data structure, you can directly create a view of the existing table and use different table names and field names.

explain:

• The operation on the view can only stay on the query
• If it is a view generated from a single table, you can also insert data; If it is a view generated by multi table Association, the insertion will not work
• If more than three tables are associated at any time, it does not meet the specification, which seriously hinders the query performance. The same is true for views. Using complex nested views and multi table associations will also greatly reduce query performance

2. Specification of view

• View is a virtual table, which has some characteristics of the table: the view name is unique, which is similar to the table name
• If you are not an administrator user, you must have create permission to create a view
• Views are essentially query statements, so views can be nested and joined with other tables
• Views cannot have indexes and triggers

3. View statement

View requires MySQL 5 Only 0 or above is supported

1. Create view and table types

The create view statement is used to create a view

2. Display view creation statement

show create view viewName

3. Delete view

drop view viewName

4. Update view

create or replace view

4. View operation

1. Create a simple view example

create view `view_order` as SELECT `id`,`order_name`,`year` FROM `order`;

• Use the id and order of the order table_ The name and year fields form a view. As is followed by query statements, or complex query statements such as sub query and multi table Association

2. Query view is essentially the same as query table

SELECT * FROM `view_order`;

3. Insert data into the view. The inserted data is directly inserted into the entity table order

INSERT INTO `view_order` (`order_name`,`year`) VALUES ('Order for cocoa',2021);

4. Delete view

drop view `view_order`;

5. Summary

• View is essentially a query statement, which can be made into a view for some simple data statistics
• If the permission is open to a third-party company, using the view to query the data of some entity tables as an open table is also a reasonable application of the view
• Simple tables can be linked into views to simplify development
• View is a virtual table and only has some functions of the table

18. Stored procedure

1. Concept of stored procedure

Concept: use multiple statements to complete business operations. A simple definition of a stored procedure is a collection of multiple SQL statements.

characteristic:

• Using stored procedures can simplify a single complex SQL, which greatly improves the performance compared with a single complex SQL;
• If the table structure changes, you only need to change the table name of the SQL statement used by the stored procedure. If the business logic changes, you only need to jump to the stored procedure, which has strong flexibility;
• Once a stored procedure is established, it can be used without repeated establishment, so as to ensure that developers use the same stored procedure and ensure data reliability
• In general, the use of stored procedures is simple, flexible, safe and reliable, and has good performance

2. Stored procedure syntax

1. Create stored procedure

Create PROCEDURE Stored procedure name (parameter list)
begin
  Process body
end;

2. Parameter list

• IN input

IN var1 Declmal(6,2)

• OUT output

IN var2 Decimal(6,2)

• INOUT input / output

IN var3 Decimal(6,2)

3. Variables

declare variable name variable type [default value]

4. Execute stored procedures

call stored procedure name

5. Delete stored procedure

DROP PROCEDURE stored procedure name

6. Assignment

Assign values to variables using set and select into statements

set @var := 20
select sum(price) into total from table_name

7. if statement

f condition then
	expression
[elseif condition then
	expression]
...
[else
	expression]
end if;

8. case statement

CASE value WHTN Match value THEN result
[WHEN Match value THEN result]
......
[ELSE result]
END

9. while statement

[Start label:]while condition do
	Circulatory body
[End tag]
end while;

10. loop statement

[Start label:] loop
 Statement body
[End tag]
end loop;

11. iterate/leave statement

Through the label, you can:

• iterate means iteration
• Leave means to leave

12. repeat statement

repeat
--Circulatory body
until Cycle condition
end repeat;

Knowledge points: if you use the command line to learn, use / / when writing multiline SQL to realize line feed.

3. Stored procedure instance

Prepare a table order_detail and insert several pieces of data

CREATE TABLE `order_detail` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
  `detail_name` varchar(255) DEFAULT NULL COMMENT 'order details ',
  `price` decimal(10,2) DEFAULT NULL COMMENT 'Price',
  `oid` int(11) DEFAULT NULL COMMENT 'order id',
  PRIMARY KEY (`id`)
)

1. Parameterless stored procedure

• View all order names of order details, which is no different from ordinary query statements

CREATE PROCEDURE slelect_detail ( ) BEGIN
SELECT
	detail_name 
FROM
	order_detail;
END;

• Call the stored procedure again

CALL slelect_detail ( );

• The following will be printed:
  

• Delete stored procedure

DROP PROCEDURE slelect_detail;

2. Example of storage process

• Query the detailed names of all orders whose oid is dynamic. Considering that oid is dynamic and needs to be entered by the user, oid is used as the input parameter:

CREATE PROCEDURE slelect_detail ( IN order_id INT ) BEGIN
SELECT
	detail_name 
FROM
	order_detail 
WHERE
	oid = order_id;
END;

• Call the stored procedure and only query the order details name of the user with oid 1

call slelect_detail(1);

• The printed content is as follows:
  
• Delete stored procedure

DROP PROCEDURE slelect_detail;

3. Examples of the stored procedures with the input and output parameters

• Query all amounts of order details of any user: define the input order id as order_id, the total output amount is total

CREATE PROCEDURE slelect_toatal_money ( IN order_id INT, OUT total DECIMAL ( 8, 2 ) )
BEGIN
SELECT
	sum( price ) INTO total 
FROM
	order_detail 
WHERE
	oid = order_id;
END;

• Call stored procedure example

CALL slelect_toatal_money ( 1, @total );

• Query order_ Example of total amount with ID 1

SELECT @total;

• Output result:
  
• Delete stored procedure

drop PROCEDURE slelect_toatal_money;

4. Example of if statement

Use control flow to realize complex stored procedures.

Order of input_ ID automatically increases by 5, and then judge whether var is less than 7. If yes, query the order detail price; otherwise, query the total order detail price:

create procedure slelect_toatal_money(IN order_id INT)
begin
-- Define variables
declare var  int;
-- assignment
set var= order_id+5;
-- if judge
if var<7 then
select price  from oder_detail where oid = order_id;
else
select sum(price)  from oder_detail where oid = order_id;
end if;
end;

• call

CALL slelect_toatal_money(1);

• Query results:
  

• call

 CALL slelect_toatal_money(2);

• Query results:
  
• Delete stored procedure

DROP PROCEDURE slelect_toatal_money;

5. Example of while statement

• Judge the variable var. if var < 7, it points to the query price statement, and VaR increases automatically

CREATE PROCEDURE slelect_toatal_money(IN order_id INT)
BEGIN
-- Define variables
DECLARE var INT;
-- assignment
SET var = order_id + 5;
-- while
while var < 7 DO
 SELECT price FROM order_detail WHERE oid = order_id;
 SET var = var + 1;
 END WHILE;
END;

• Call example

CALL slelect_toatal_money ( 1 );

• Output:

6. Example of case statement

• The effect of the following statement is consistent with that of the above if statement:

CREATE PROCEDURE slelect_toatal_money(IN order_id INT)
BEGIN
-- Define variables
DECLARE var INT;
-- assignment
SET var := order_id;
-- case Judge matching
CASE var
WHEN 1 THEN
 SELECT price FROM order_detail WHERE oid = order_id;
WHEN 2 THEN
 SELECT SUM(price) FROM order_detail WHERE oid = order_id;
 END CASE;
END;

• Example call

call slelect_toatal_money(1);

• result:
  
• Call example

CALL slelect_toatal_money(2);

• result:
  

7. loop statement

• If VaR is less than 3, calculate the value of price + var

CREATE PROCEDURE slelect_toatal_money(IN order_id INT)
BEGIN
	-- Define variables
	DECLARE var INT;
	-- assignment
	SET var := order_id;
	-- loop
	select_loop : LOOP
		SELECT price + var FROM order_detail WHERE oid = order_id;
		SET var = var + 1;
		-- Jump out of loop
		IF var > 3 THEN
			LEAVE select_loop;
		END IF;
	END loop;
END;

• Call example

CALL slelect_toatal_money(1);

• The results will output three groups of results:
  
• Call example

CALL slelect_toatal_money(2);

• result:
  

8. repeat

• The difference between report and while is that while checks the conditions before execution; Check the condition after the test is executed:

CREATE PROCEDURE slelect_toatal_money(IN order_id INt)
BEGIN
	-- Define variables
	DECLARE var INT;
	-- assignment
	SET var = order_id + 5;
  -- repeat loop
	REPEAT
		SELECT price FROM order_detail WHERE oid = order_id;
		SET var = var + 1;
		UNTIL var > 7
	END REPEAT;
END;

• Call example

CALL slelect_toatal_money(1);

• Two sets of the same results will appear:
  

Knowledge points:

• loop, while, repeat and iterate are loops
• The functions of loop, while and repeat are almost the same
• iterate can call a loop in the form of a tag in the same way as the leave statement

19. Cursor

1. Concept of cursor

Cursor essence: the result set after query. Cursors can be used when performing similar operations on the previous row or the next row of the query result set

2. Cursor syntax

1. Grammar

• Define cursor: declare cursor name cursor for query statement;
• Open cursor: open cursor name
• Retrieve the result set (cursor) of the query, and provide row to variable for use
• Close cursor: close cursor name

2. Examples

create procedure stored procedure name ()
begin
– cursor –
– xx name. After opening the cursor, grab each row and assign the result to name
declare name varchar(20);
– create cursor
declare cursor name cursor for query statement;
– open cursor
open cursor name;
– retrieve the result set of the query (i.e. cursor), and provide row to variable use
fetch cursor name into name;
select name;
– close cursor
close cursor name;
end;
– call stored procedure
call stored procedure name;
– delete stored procedure
drop procedure stored procedure name;

3. Use cursor

1. Simple use of cursor

Demand: query the result set of the order detail name with oid 1 as the cursor

• After opening the cursor, grab the result of each row and assign it to the variable name

CREATE PROCEDURE printName()
BEGIN
	-- Order name
	DECLARE name VARCHAR(20);
	-- Create cursor
	DECLARE cur CURSOR FOR SELECT detail_name FROM order_detail WHERE oid = '1';
	-- Open cursor
	OPEN cur;
  FETCH cur INTO name;
  SELECT name;
    -- Close cursor
    CLOSE cur;
END;

• Call stored procedure

CALL printName;

• There is only one piece of data in the print result, which indicates that only one piece of data is captured in the cursor in the above method, and it is the row with the smallest row number in the table:
  

2. Use cursors in loops

• Assign the result set with the query oid of 1 to the cursor, grab the order detail name and price of each line through the cursor, and assign them to the variables name and detail respectively_ Price, SQLSTATE '02000' will appear when the cycle cannot continue, that is, when the variable continues, set done to 1 for true. At this time, the cycle ends and jumps out of the cycle:

DROP PROCEDURE IF EXISTS printDetail;
CREATE PROCEDURE printDetail()
BEGIN
	-- Order name
	DECLARE name varchar(20);
	-- Price
	DECLARE detail_price DECIMAL(8,2);
	-- End flag variable (false by default)
	DECLARE done boolean DEFAULT 0;
	-- Create cursor
	DECLARE cur CURSOR FOR SELECT detail_name, price FROM order_detail WHERE oid='1';
	-- Specifies the return value at the end of the cursor loop
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
	-- Open cursor
	OPEN cur;
	-- Circular cursor data
	detail_loop:LOOP
	-- Based on a piece of data currently pointed to by the cursor
	FETCH cur INTO name, detail_price;
	SELECT name, detail_price;
	-- Determine whether the cycle of the cursor ends
	IF done THEN
		-- Jump out of cursor loop
		LEAVE detail_loop;
	END IF;
	END LOOP;
	-- Close cursor
	CLOSE cur;
END;

• Call stored procedure:

CALL printDetail();	

• Query result: (the deficiency is that the last row will be traversed. If you want to fine process, you still need to customize the flag bit to jump out of the loop)
  

20. Trigger

1. Concept of trigger

Trigger: the action triggered when the table changes.

Example: when inserting data into a table, the table changes. Now you want to check whether all input parameters are lowercase before inserting data. At this point, the trigger can be used to detect.

According to the above analysis, using a basic trigger, at least the table should change and meet a triggered event.

Table change usually refers to adding, deleting and modifying. Its action can occur before or after adding, deleting and modifying. The trigger event is the process we need to write:

• update(after/before)
• insert(after/before)
• delete(after/before)

2. Basic syntax of trigger

• Create trigger: create trigger trigger name departure action on table name for each row [trigger event]
• Delete trigger: drop trigger trigger name;
• View trigger: show trigger;

Knowledge points:

• Triggers depend on table creation. Without tables, there will be no triggers. For example, views and temporary tables are not real tables. They have no triggers;
• Generally speaking, each table has the limit of triggers, and generally supports up to 6 different types of triggers;
• The use of triggers will frequently change each row of the table, so it will greatly affect the performance, especially for some large tables with fast update frequency. If triggers are set, it will take up a lot of system resources;
• Generally speaking, triggers are used for small tables with small table changes. They can be deleted immediately without using triggers.

3. insert trigger example

1. Create trigger

Create a trigger getPrice to act on order_ For each row of the detail table, the price of this order detail is queried and assigned to the variable @ price every time data is inserted.
NEW is a virtual table that records the rows of inserted data. Therefore, the data inserted each time can be obtained in the NEW table.

-- insert trigger
CREATE TRIGGER getPrice AFTER INSERT ON order_detail FOR EACH ROW SELECT NEW.price INTO @price;

-- Detect insert trigger
INSERT INTO `order_detail`(`detail_name`,`price`,`oid`) VALUES ('Washbasin',20.00,2);

SELECT @price;

• select @price result:
  

2. Delete trigger

DROP TRIGGER getPrice;

4. update trigger example

Change the trigger after insertion to the trigger after update. Just change after insert to after update.

UPDATE `order_detail` SET `price` = 30.00 WHERE `id` = 2;

SELECT @price;

• Query results:
  

-- Delete trigger 
DROP TRIGGER getPrice;

Change the NEW table of the update trigger to the OLD table

CREATE TRIGGER getPrice AFTER UPDATE ON order_detail FOR EACH ROW SELECT OLD.price INTO @price;

The updated price is 40

UPDATE `order_detail` SET `price` = 40.00 WHERE `id` = 2;

At this time, the query price is 30, indicating that the OLD table triggers the original data value

SELECT @price;

Knowledge points:

• Updating trigger is mainly to understand the original data stored in OLD
• NEW stores the data to be updated
• The NEW table can set and change values, while the OLD table is read-only

5. delete trigger

Change the update trigger to the delete trigger. begin and end were omitted before. If there are multiple execution statements, you need to add:

CREATE TRIGGER getPrice AFTER DELETE ON order_detail FOR EACH ROW
BEGIN
SELECT OLD.price INTO @price;
END;

Delete previous SQL data

DELETE FROM order_detail WHERE `id` = 2;

• At this time, the data with id=2 is deleted

The query price is 40. The OLD table stores the data to be deleted:

SELECT @price;

21. User operation

The information about the user account is stored in the MySQL database of MySQL, so if you need to view the user information, you need to enter the MySQL database.

1. View user information

The user table stores all login accounts. Use MySQL to query the user in the user table:

USE mysql;
SELECT `user` FROM user;

• Print results:

2. Create user

CREATE USER username IDENTIFIED BY [PASSWORD] password;

Example: create user Jason and specify the password as Python:

CREATE USER Jason IDENTIFIED BY 'Python';

then

SELECT `user` FROM user; 

Query results

3. Rename user

RENAME USER old user name TO new user name;

Example: Rename user Jason to Silence:

RENAME USER Jason TO Silence;

then

SELECT `user` FROM user;

Query results

4. Delete user

DROP USER user name;

Example: delete user Silence:

DROP USER Silence;

Then there is no Silence user in the query result.

5. Change password

SET PASSWORD FRO user name = PASSWORD('password ')

Example: change password to py for user Jason

SET PASSWORD FOR Jason = PASSWORD('py');

22. Permission Operation

1. View user permissions

SHOW GRANTS FOR user name;

Example: view permissions owned by user Jason

SHOW GRANTS FOR Jason;

• Print:
  
• A permission is found, but use indicates that there is no permission at all.

2. Grant authority

GRANT permission ON table name TO user name [IDENTIFIED BY [PASSWORD] 'password']

Common permissions: all, create, drop, insert, update, select.

Example: assign query permission to all tables in the test library to user Jason

View permission becomes 2:

3. Revoke authority

REVOKE permission list ON table name FROM user name;

Example: undo user Jason's query operation on all tables in the test library

REVOKE SELECT ON test.* FROM Jason;

4. Permission list

When using authorization and revoking permission, you can refer to the following permission list:

jurisdiction	explain
ALL	All permissions except GRANT OPTION
ALTER	Use ALTER TABLE
ALTER ROUTINE	Use ALTER PROCEDURE and DROP PROCEDURE
CREATE	Using CREATE TABLE
CREATE ROUTINE	Using CREATE PROCEDURE
CREATE TEMPORARY TABLES	Using CREATE TEMPORARY TABLE
CREATE USER	Use CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES
CREATE VIEW	Using CREATE VIEW
DELETE	Use DELETE
DROP	Using DROP TABLE
EXECUTE	Using CALL and stored procedures
FILE	Use select into output file and LOAD DATA INFILE
GRANT OPTION	Use GRANT and REVOKE
INDEX	Using CREATE INDEX and DROP INDEX
INSERT	Using INSERT
LOCK TABLES	Using LOCK TABLES
PROCESS	Use SHOW FULL PROCESSLIST
RELOAD	Use FLUSH
REPLICATION CLIENT	Access to server location
REPLICATION SLAVE	Used by replication slaves
SELECT	Using SELECT
SHOW DATABASES	Using SHOW DATABASES
SHOW VIEW	Using SHOW CREATE VIEW
SHUTDOWN	Use mysqladmin shutdown (to shut down MySQL)
SUPER	Use CHANGE MASTER, KILL, LOGS, PURGE, MASTER, and SET GLOBAL. It also allows mysqladmin to debug login
UPDATE	Use UPDATE
USAGE	No access

23. MySQL architecture and lock

1. MySQL Architecture Overview

MySQL can be divided into three levels:

• Layer 1: connection layer, as long as it is responsible for MySQL database connection and security authentication function;
• The second layer: the core layer of MySQL. The main functions include: MySQL query, cache, execution plan, optimization, etc;
• The third layer: engine layer. Specifying different engines for MYSQL will achieve different data operation effects.

2. Query Cache

MySQL's Query Cache is a cache mechanism for matching based on hash value calculation. If you turn on the cache option frequently, it will increase the performance of the production environment. If you turn off the cache option frequently, it will not pay for the loss.

Available statements: Show variables like '% query'_ Check whether the Query Cache is closed. Mainly focus ON the parameter query_ cache_ Whether type is turned OFF (OFF, ON), you don't have to pay too much attention to the query of cache allocation size_ cache_ Size parameter.

3. Read lock

According to different engines, there are three types of locks in MySQL: table lock, read lock and write lock.

Read lock: it is also a shared lock, that is, in the multi-user state, the reading of resources at the same time does not affect each other, but the data cannot be modified.

Usage scenario: generally, manually add read locks to one or a range of data (generally used in stored procedures).

Example of lock reading syntax:

select field from table name [where condition] lock in share mode;

4. Write lock

Write lock: it is "exclusive lock", also known as "independent lock".

Usage scenario: it is generally the case of writing data. If a user obtains a write lock, other users will not be able to obtain a write lock or a read lock until the user completes the operation and releases the lock.

Usage: add a for update statement after the execution statement

Example of write lock syntax:

select field from table name [where condition] for update;

5. Lock granularity

Lock granularity: a degree of resource locking range. Different locking strategies are used to achieve better concurrency performance.

Lock granularity usage strategies are divided into row lock, table lock and page lock.

1. Watch lock

Concept: lock the whole table.

Advantages and disadvantages:

• Advantages: low performance overhead and fast locking speed
• Disadvantages: large lock granularity and low concurrency

Syntax example of manually adding table lock:

lock table table name

Release lock:

unlock tables table name

2. Row lock

Concept: lock rows.

Advantages and disadvantages:

• Advantages: it can support the maximum amount of concurrency, so the lock granularity is the smallest
• Disadvantages: slow locking speed, high performance consumption and deadlock

Type of row lock:

• Record lock (primary key or unique index)
• Gap lock (GAP) is generally used when the query condition is range, not equality condition
• Record the combination of lock and clearance lock (next key lock)

3. Page lock

Generally, page locks are not encountered, and their overhead and locking time are between table locks and row locks. Deadlock will occur, and the locking granularity is between table locks and row locks.

Knowledge points:

• MyISAM and Memory engines support table locks. They will automatically add table locks to select, update, insert and delete
• InnoDB supports table locks and row locks. It will automatically add exclusive locks to the data of update, insert and delete statements, while select statements do not

4. Optimistic lock

Optimistic lock is implemented based on version number.

Note: the condition must be a primary key. When reading, the data version number will be read out. When updating data, the version number will be added by 1 to compare the queried data. If the version number is inconsistent, it is expired data.

Query example:

select id,value,version from table name where id=#{id}

Update example:

update table name set value=2,version=version+1 where id=#{id} and version=#{version}

5. Pessimistic lock

Table lock, row lock and read-write lock are pessimistic locks.

6. Introduction to engine

MySQL supports a variety of engines. The mainstream engine is InnoDB, followed by MyISAM. In special cases, Memory is used.

1. InnoDB

The most widely used engine is also the most important engine.

Storage performance:

• InnoDB is a repeatable transaction isolation level, but it implements next key lock to prevent unreal reads
• Implementation of index based on Clustering
• The main composition structure is memory structure, thread and disk file group

2. MyISAM

MyISAM is the default engine of MySQL in earlier versions, which is in MySQL 5.0 No longer used after 1.

Features: it does not support transactions, row locks, default table locks, and low concurrency

3. Memory

The stored content is stored in the engine.

characteristic:

• Advantages: it supports Hash and Btree indexes, and its data reading is fast
• Disadvantages: if the server fails, data will be lost after restarting

24. Lock waiting

Lock waiting means that session A acquires an exclusive lock (usually A write lock) on A row of data, and then session B acquires an exclusive lock on the same row. Lock waiting occurs. MySQL has A reserved parameter innodb_lock_wait_timeout specifies the deadlock time. If the deadlock waiting time is exceeded, an exception is reported.

Example:

• session A executes the following statements to start the transaction and update the statement with index 1; At this time, session A obtains the write lock permission of the statement id=1:

begin
update `order` set `year`='2021' where id='1';

• session B executes the following statement. Like the above statement, since the write lock of the data with id=1 has been obtained by session A, lock waiting will occur:

begin
update `order` set `year`='2021' where id='1';

• Wait for 50 seconds, and an exception is reported:

lock wait timeout exceeded; try restarting transaction

• View default lock wait statements

show BARIABLES like 'innodb_lock_wait_timeout'

25. Deadlock

1. Deadlock generation

Two or more sessions wait for each other in the process of preempting resources.

Deadlock is based on lock waiting. session A obtains the write lock with id=1 and session B obtains the write lock with id=2. At this time, due to different indexes, lock waiting will not occur; When session A attempts to acquire a write lock with id=2, because id=2 has been acquired by session A, lock waiting occurs. Since both session A and session B are in lock waiting state at the same time, waiting for the other party to release the lock will occur, so deadlock will occur.

Example:

• session A executes the statement to obtain the write lock permission with id=1:

BEGIN;
UPDATE `order` SET `year`='2021' WHERE id=1;

• session B executes the statement to obtain the write lock permission with id=2:

BEGIN;
UPDATE `order` SET `year`='2022' WHERE id=2; 

• session A executes the statement, attempts to obtain the write lock permission with id=2, and enters the lock waiting state:

UPDATE `order` SET `year`='2022' WHERE id=2;

• session B executes the statement, attempts to obtain the write lock permission with id=1, and enters the lock waiting state:

update  `order` set `year`= '2022' where id = '1';

When B enters the lock waiting, it will directly report deadlock exception

Deadlock found when trying to get lock; try restarting transaction

2. View deadlock

You can use show engine innodb status to view deadlocks

......
*** (1) TRANSACTION: // Thing A
TRANSACTION 253507, ACTIVE 474 sec starting index read
mysql tables in use 1, locked 1 // A lock has been used
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 17001, OS thread handle 139824777217792, query id 2191731 ......
root updating
update `order` set `year`= '2022' where id = '2'//Executed statement
*** (1) WAITING FOR THIS LOCK TO BE GRANTED: // Wait for lock release to acquire lock
RECORD LOCKS space id 65 page no 3 n bits 80 index PRIMARY of table `zszxz`.`order` trx id 253507 lock_mode X locks rec but not gap waiting
.....

*** (2) TRANSACTION: // Thing B
TRANSACTION 253508, ACTIVE 425 sec starting index read
mysql tables in use 1, locked 1 // A lock has been used
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 17002, OS thread handle 139824778569472, query id 2191735 ......
root updating
update  `order` set `year`= '2022' where id = '1'//Executed statement
*** (2) HOLDS THE LOCK(S): //Hold lock
RECORD LOCKS space id 65 page no 3 n bits 80 index PRIMARY of table `zszxz`.`order` trx id 253508 lock_mode X locks rec but not gap
......

*** (2) WAITING FOR THIS LOCK TO BE GRANTED: // Wait for lock release to acquire lock
RECORD LOCKS space id 65 page no 3 n bits 80 index PRIMARY of table `zszxz`.`order` trx id 253508 lock_mode X locks rec but not gap waiting
......

The letters represent the following types of locks:

• S hared lock
• Exclusive lock (X)
• Intentional sharing (IS)
• Intentional exclusivity (IX)
• Gap lock (GK) is a gap lock that locks a range, excluding the current record itself
• RECORD LOCKS stands for RECORD LOCKS

It can be seen that the above statement (1) represents transaction A and MySQL thread ID17001, (2) represents transaction B and MySQL thread ID17002. Both transactions A and B are waiting for each other to release the lock, resulting in A deadlock.

Knowledge points:

• View table lock: show status like 'table%';

Resolve Deadlock:

• Find the deadlock thread and kill the thread of MySQL deadlock (Kill Command)
• If the transaction is not committed, roll back the transaction directly

3. How to avoid deadlock

• Using table locks on tables that are prone to deadlock will not cause deadlock;
• Avoid cross using the same locks.