Understand MYSQL in one day

Installation tutorial: https://www.runoob.com/mysql/mysql-install.html
Install video: https://www.bilibili.com/video/BV1HK41157k8?from=search&seid=8785963740363848969

Log in and log out of MySQL server

# Log in to MySQL $ mysql -u -p # Exit MySQL database server exit;

Basic grammar

-- Show all databases show databases; -- Create database CREATE DATABASE test; -- Switch database use test; -- Show all tables in the database show tables; -- Create data table CREATE TABLE pet ( name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE ); -- View data table structure -- describe pet; desc pet; -- Query table SELECT * from pet; -- insert data INSERT INTO pet VALUES ('puffball', 'Diane', 'hamster', 'f', '1990-03-30', NULL); -- Modify data UPDATE pet SET name = 'squirrel' where owner = 'Diane'; -- Delete data DELETE FROM pet where name = 'squirrel'; -- Delete table DROP TABLE myorder;

Table building constraint

Primary key constraint

-- Primary key constraint -- To ensure the uniqueness of all data in the table, a field should not be duplicate and empty. CREATE TABLE user ( id INT PRIMARY KEY, name VARCHAR(20) ); -- composite keys -- Each field in the union primary key cannot be empty and cannot be the same as the set union primary key. CREATE TABLE user ( id INT, name VARCHAR(20), password VARCHAR(20), PRIMARY KEY(id, name) ); -- Autogenous constraint -- The primary key of the auto increment constraint is automatically incrementally allocated by the system. CREATE TABLE user ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) ); -- Add primary key constraint -- If you forget to set the primary key, you can also use the SQL Statement settings (in two ways): ALTER TABLE user ADD PRIMARY KEY(id); ALTER TABLE user MODIFY id INT PRIMARY KEY; -- Delete primary key ALTER TABLE user drop PRIMARY KEY;

Unique primary key

-- Create a unique primary key when creating a table CREATE TABLE user ( id INT, name VARCHAR(20), UNIQUE(name) ); -- Add unique primary key -- If unique creation is not set during table creation, you can also use the SQL Statement settings (in two ways): ALTER TABLE user ADD UNIQUE(name); ALTER TABLE user MODIFY name VARCHAR(20) UNIQUE; -- Delete unique primary key ALTER TABLE user DROP INDEX name;

Nonnull constraint

-- Add a non empty constraint when creating a table -- Constraint a field cannot be empty CREATE TABLE user ( id INT, name VARCHAR(20) NOT NULL ); -- Remove non empty constraints ALTER TABLE user MODIFY name VARCHAR(20);

Default constraint

-- Add default constraints when creating tables -- Constrain the default value of a field CREATE TABLE user2 ( id INT, name VARCHAR(20), age INT DEFAULT 10 ); -- Remove non empty constraints ALTER TABLE user MODIFY age INT;

Foreign key constraint

-- class CREATE TABLE classes ( id INT PRIMARY KEY, name VARCHAR(20) ); -- Student table CREATE TABLE students ( id INT PRIMARY KEY, name VARCHAR(20), -- there class_id And classes In id Fields associated class_id INT, -- express class_id The value of must come from classes In id field value FOREIGN KEY(class_id) REFERENCES classes(id) ); -- 1. Main table (parent table) classes Data value not found in, in sub table (sub table) students It can't be used; -- 2. When a record in the primary table is referenced by a secondary table, the primary table cannot be deleted.

Three design paradigms of database

1NF

As long as the field value can continue to split, it does not meet the first paradigm.

The more detailed the design of the paradigm, the better it may be for some practical operations, but not all of them are beneficial. The actual situation of the project needs to be set.

2NF

On the premise of satisfying the first normal form, all other columns must be completely dependent on the primary key column. If incomplete dependency occurs, it can only occur in the case of a federated primary key:

-- Order form CREATE TABLE myorder ( product_id INT, customer_id INT, product_name VARCHAR(20), customer_name VARCHAR(20), PRIMARY KEY (product_id, customer_id) );

In fact, in this order form, product_name only depends on product_id ，customer_name only depends on customer_id . In other words, product_name and customer_id doesn't matter, customer_name and product_id doesn't matter.

This does not satisfy the second paradigm: all other columns must be completely dependent on the primary key column!

CREATE TABLE myorder ( order_id INT PRIMARY KEY, product_id INT, customer_id INT ); CREATE TABLE product ( id INT PRIMARY KEY, name VARCHAR(20) ); CREATE TABLE customer ( id INT PRIMARY KEY, name VARCHAR(20) );

After splitting, the product in the myorder table_ ID and customer_id completely depends on order_id primary key, while other fields in the product and customer tables are completely dependent on the primary key. Meet the design of the second paradigm!

3NF

On the premise of satisfying the second normal form, except for the primary key columns, there can be no transitive dependency between other columns.

CREATE TABLE myorder ( order_id INT PRIMARY KEY, product_id INT, customer_id INT, customer_phone VARCHAR(15) );

Customer in table_ Phone may depend on order_id , customer_ Two columns of Id do not satisfy the design of the third paradigm: no transitive dependency can exist between other columns.

CREATE TABLE myorder ( order_id INT PRIMARY KEY, product_id INT, customer_id INT ); CREATE TABLE customer ( id INT PRIMARY KEY, name VARCHAR(20), phone VARCHAR(15) );

After modification, there is no transfer dependency between other columns. Other columns only depend on the primary key column, which satisfies the design of the third paradigm!

Query exercise

Prepare data

-- Create database CREATE DATABASE select_test; -- Switch database USE select_test; -- Create student table CREATE TABLE student ( no VARCHAR(20) PRIMARY KEY, name VARCHAR(20) NOT NULL, sex VARCHAR(10) NOT NULL, birthday DATE, -- birthday class VARCHAR(20) -- Class ); -- Create teacher table CREATE TABLE teacher ( no VARCHAR(20) PRIMARY KEY, name VARCHAR(20) NOT NULL, sex VARCHAR(10) NOT NULL, birthday DATE, profession VARCHAR(20) NOT NULL, -- title department VARCHAR(20) NOT NULL -- department ); -- Create Curriculum CREATE TABLE course ( no VARCHAR(20) PRIMARY KEY, name VARCHAR(20) NOT NULL, t_no VARCHAR(20) NOT NULL, -- Teacher number -- Indicates the tno From teacher In the table no field value FOREIGN KEY(t_no) REFERENCES teacher(no) ); -- Score sheet CREATE TABLE score ( s_no VARCHAR(20) NOT NULL, -- Student number c_no VARCHAR(20) NOT NULL, -- Course number degree DECIMAL, -- achievement -- Indicates the s_no, c_no From student, course In the table no field value FOREIGN KEY(s_no) REFERENCES student(no), FOREIGN KEY(c_no) REFERENCES course(no), -- set up s_no, c_no Is the union primary key PRIMARY KEY(s_no, c_no) ); -- View all tables SHOW TABLES; -- Add student table data INSERT INTO student VALUES('101', 'Zeng Hua', 'male', '1977-09-01', '95033'); INSERT INTO student VALUES('102', 'Kuang Ming', 'male', '1975-10-02', '95031'); INSERT INTO student VALUES('103', 'Wang Li', 'female', '1976-01-23', '95033'); INSERT INTO student VALUES('104', 'Li Jun', 'male', '1976-02-20', '95033'); INSERT INTO student VALUES('105', 'Wang Fang', 'female', '1975-02-10', '95031'); INSERT INTO student VALUES('106', 'land force', 'male', '1974-06-03', '95031'); INSERT INTO student VALUES('107', 'Wang NIMA', 'male', '1976-02-20', '95033'); INSERT INTO student VALUES('108', 'Open the whole egg', 'male', '1975-02-10', '95031'); INSERT INTO student VALUES('109', 'Zhao Tiezhu', 'male', '1974-06-03', '95031'); -- Add teacher table data INSERT INTO teacher VALUES('804', 'Li Cheng', 'male', '1958-12-02', 'associate professor', 'Computer Department'); INSERT INTO teacher VALUES('856', 'Zhang Xu', 'male', '1969-03-12', 'lecturer', 'Department of Electronic Engineering'); INSERT INTO teacher VALUES('825', 'Wang Ping', 'female', '1972-05-05', 'assistant', 'Computer Department'); INSERT INTO teacher VALUES('831', 'Liu Bing', 'female', '1977-08-14', 'assistant', 'Department of Electronic Engineering'); -- Add curriculum data INSERT INTO course VALUES('3-105', 'Introduction to computer', '825'); INSERT INTO course VALUES('3-245', 'operating system', '804'); INSERT INTO course VALUES('6-166', 'digital circuit ', '856'); INSERT INTO course VALUES('9-888', 'Advanced mathematics', '831'); -- Add add score sheet data INSERT INTO score VALUES('103', '3-105', '92'); INSERT INTO score VALUES('103', '3-245', '86'); INSERT INTO score VALUES('103', '6-166', '85'); INSERT INTO score VALUES('105', '3-105', '88'); INSERT INTO score VALUES('105', '3-245', '75'); INSERT INTO score VALUES('105', '6-166', '79'); INSERT INTO score VALUES('109', '3-105', '76'); INSERT INTO score VALUES('109', '3-245', '68'); INSERT INTO score VALUES('109', '6-166', '81'); -- View table structure SELECT * FROM course; SELECT * FROM score; SELECT * FROM student; SELECT * FROM teacher;

1 to 10

-- query student All rows of the table SELECT * FROM student; -- query student In the table name,sex and class All lines of field SELECT name, sex, class FROM student; -- query teacher Not repeated in the table department column -- department: De duplication query SELECT DISTINCT department FROM teacher; -- query score Score in the table is 60-80 All rows between (interval query and operator query) -- BETWEEN xx AND xx: Query range, AND express "also" SELECT * FROM score WHERE degree BETWEEN 60 AND 80; SELECT * FROM score WHERE degree > 60 AND degree < 80; -- query score 85 in the table, 86 Or line 88 -- IN: Query multiple values in the specification SELECT * FROM score WHERE degree IN (85, 86, 88); -- query student In the table '95031' Class or gender: 'female' All lines of -- or: Expression or relationship SELECT * FROM student WHERE class = '95031' or sex = 'female'; -- with class Query in descending order student All rows of the table -- DESC: Descending order, from high to low -- ASC(Default): Ascending, low to high SELECT * FROM student ORDER BY class DESC; SELECT * FROM student ORDER BY class ASC; -- with c_no Ascending degree Descending query score All rows of the table SELECT * FROM score ORDER BY c_no ASC, degree DESC; -- query "95031" Number of students in the class -- COUNT: Statistics SELECT COUNT(*) FROM student WHERE class = '95031'; -- query score The student number and course number of the highest score in the table (sub query or sorting query). -- (SELECT MAX(degree) FROM score): Sub query, calculate the highest score SELECT s_no, c_no FROM score WHERE degree = (SELECT MAX(degree) FROM score); -- Sort query -- LIMIT r, n: From r Line start, query n Data SELECT s_no, c_no, degree FROM score ORDER BY degree DESC LIMIT 0, 1;

Group average

Query the average score of each course.

-- AVG: average value SELECT AVG(degree) FROM score WHERE c_no = '3-105'; SELECT AVG(degree) FROM score WHERE c_no = '3-245'; SELECT AVG(degree) FROM score WHERE c_no = '6-166'; -- GROUP BY: Group query SELECT c_no, AVG(degree) FROM score GROUP BY c_no;

Grouping condition and fuzzy query

Query the average score of at least 2 students in the score table who take courses starting with 3.

SELECT * FROM score; -- c_no Course number +------+-------+--------+ | s_no | c_no | degree | +------+-------+--------+ | 103 | 3-105 | 92 | | 103 | 3-245 | 86 | | 103 | 6-166 | 85 | | 105 | 3-105 | 88 | | 105 | 3-245 | 75 | | 105 | 6-166 | 79 | | 109 | 3-105 | 76 | | 109 | 3-245 | 68 | | 109 | 6-166 | 81 | +------+-------+--------+

According to the analysis table, at least two students take 3-105, 3-245 and 6-166 courses, and the courses starting with 3 are 3-105 and 3-245. That is to say, we need to query the average degree scores of all 3-105 and 3-245.

-- First of all c_no, AVG(degree) Query by grouping SELECT c_no, AVG(degree) FROM score GROUP BY c_no +-------+-------------+ | c_no | AVG(degree) | +-------+-------------+ | 3-105 | 85.3333 | | 3-245 | 76.3333 | | 6-166 | 81.6667 | +-------+-------------+ -- Find out the courses that at least 2 students take -- HAVING: Indicates holding HAVING COUNT(c_no) >= 2 -- And it's a course that starts with three -- LIKE Represents a fuzzy query,"%" Is a wildcard, matching "3" Any character after. AND c_no LIKE '3%'; -- Put the front SQL The sentences are spliced, -- Add a COUNT(*)，Indicates that the number of each group is also queried. SELECT c_no, AVG(degree), COUNT(*) FROM score GROUP BY c_no HAVING COUNT(c_no) >= 2 AND c_no LIKE '3%'; +-------+-------------+----------+ | c_no | AVG(degree) | COUNT(*) | +-------+-------------+----------+ | 3-105 | 85.3333 | 3 | | 3-245 | 76.3333 | 3 | +-------+-------------+----------+

Multi table query - 1

Query the name of all students and the C corresponding to the student in the score table_ No and degree.

SELECT no, name FROM student; +-----+-----------+ | no | name | +-----+-----------+ | 101 | Zeng Hua | | 102 | Kuang Ming | | 103 | Wang Li | | 104 | Li Jun | | 105 | Wang Fang | | 106 | land force | | 107 | Wang NIMA | | 108 | Open the whole egg | | 109 | Zhao Tiezhu | +-----+-----------+ SELECT s_no, c_no, degree FROM score; +------+-------+--------+ | s_no | c_no | degree | +------+-------+--------+ | 103 | 3-105 | 92 | | 103 | 3-245 | 86 | | 103 | 6-166 | 85 | | 105 | 3-105 | 88 | | 105 | 3-245 | 75 | | 105 | 6-166 | 79 | | 109 | 3-105 | 76 | | 109 | 3-245 | 68 | | 109 | 6-166 | 81 | +------+-------+--------+

Through the analysis, it can be found that only the s in the score table_ The no field value can be replaced by the corresponding name field value in the student table. How to do this?

-- FROM...: From student, score Query in table -- WHERE The condition of is expressed in the student.no and score.s_no Only when they are equal. SELECT name, c_no, degree FROM student, score WHERE student.no = score.s_no; +-----------+-------+--------+ | name | c_no | degree | +-----------+-------+--------+ | Wang Li | 3-105 | 92 | | Wang Li | 3-245 | 86 | | Wang Li | 6-166 | 85 | | Wang Fang | 3-105 | 88 | | Wang Fang | 3-245 | 75 | | Wang Fang | 6-166 | 79 | | Zhao Tiezhu | 3-105 | 76 | | Zhao Tiezhu | 3-245 | 68 | | Zhao Tiezhu | 6-166 | 81 | +-----------+-------+--------+

Multi table query - 2

Query the no, course name (name in the course table), and grade columns of all students.

Only score is related to the no of students, so as long as you query the score table, you can find all the no and degree related to students:

SELECT s_no, c_no, degree FROM score; +------+-------+--------+ | s_no | c_no | degree | +------+-------+--------+ | 103 | 3-105 | 92 | | 103 | 3-245 | 86 | | 103 | 6-166 | 85 | | 105 | 3-105 | 88 | | 105 | 3-245 | 75 | | 105 | 6-166 | 79 | | 109 | 3-105 | 76 | | 109 | 3-245 | 68 | | 109 | 6-166 | 81 | +------+-------+--------+

Then query the course table:

Just put the C in the score table_ Replace no with the corresponding name field value in the course table.

-- Add a query field name，From score,course These two tables are queried. -- as Indicates to take an alias of this field. SELECT s_no, name as c_name, degree FROM score, course WHERE score.c_no = course.no; +------+-----------------+--------+ | s_no | c_name | degree | +------+-----------------+--------+ | 103 | Introduction to computer | 92 | | 105 | Introduction to computer | 88 | | 109 | Introduction to computer | 76 | | 103 | operating system | 86 | | 105 | operating system | 75 | | 109 | operating system | 68 | | 103 | digital circuit | 85 | | 105 | digital circuit | 79 | | 109 | digital circuit | 81 | +------+-----------------+--------+

Three table associated query

Query the name, course name (name in the course table) and degree of all students.

Only the score table is associated with the student number and class number of students. We just need to query around the score table.

SELECT * FROM score; +------+-------+--------+ | s_no | c_no | degree | +------+-------+--------+ | 103 | 3-105 | 92 | | 103 | 3-245 | 86 | | 103 | 6-166 | 85 | | 105 | 3-105 | 88 | | 105 | 3-245 | 75 | | 105 | 6-166 | 79 | | 109 | 3-105 | 76 | | 109 | 3-245 | 68 | | 109 | 6-166 | 81 | +------+-------+--------+

Just put s_no and C_ Replace no with the corresponding name field values in the student and srouse tables.

First, put s_ Replace no with the name field in the student table:

SELECT name, c_no, degree FROM student, score WHERE student.no = score.s_no; +-----------+-------+--------+ | name | c_no | degree | +-----------+-------+--------+ | Wang Li | 3-105 | 92 | | Wang Li | 3-245 | 86 | | Wang Li | 6-166 | 85 | | Wang Fang | 3-105 | 88 | | Wang Fang | 3-245 | 75 | | Wang Fang | 6-166 | 79 | | Zhao Tiezhu | 3-105 | 76 | | Zhao Tiezhu | 3-245 | 68 | | Zhao Tiezhu | 6-166 | 81 | +-----------+-------+--------+

Put C_ Replace no with the name field in the course table:

-- Class Schedule Card SELECT no, name FROM course; +-------+-----------------+ | no | name | +-------+-----------------+ | 3-105 | Introduction to computer | | 3-245 | operating system | | 6-166 | digital circuit | | 9-888 | Advanced mathematics | +-------+-----------------+ -- Because of duplicate field names, use "Table name.Field name as alias" Instead. SELECT student.name as s_name, course.name as c_name, degree FROM student, score, course WHERE student.NO = score.s_no AND score.c_no = course.no;

Sub query plus component for average

Query the average scores of 95031 students in each course.

In the score table, according to the student number in the student table, students' class number and scores are selected:

-- IN (..): Select student number as s_no Condition query of SELECT s_no, c_no, degree FROM score WHERE s_no IN (SELECT no FROM student WHERE class = '95031'); +------+-------+--------+ | s_no | c_no | degree | +------+-------+--------+ | 105 | 3-105 | 88 | | 105 | 3-245 | 75 | | 105 | 6-166 | 79 | | 109 | 3-105 | 76 | | 109 | 3-245 | 68 | | 109 | 6-166 | 81 | +------+-------+--------+

In this case, just put c_no group by group, you can get the average scores of 95031 students in each course:

SELECT c_no, AVG(degree) FROM score WHERE s_no IN (SELECT no FROM student WHERE class = '95031') GROUP BY c_no; +-------+-------------+ | c_no | AVG(degree) | +-------+-------------+ | 3-105 | 82.0000 | | 3-245 | 71.5000 | | 6-166 | 80.0000 | +-------+-------------+

Subquery - 1

Check the records of students whose scores are higher than 109 in 3-105.

First, select class number 3-105, and find out all the lines with scores higher than 109.

SELECT * FROM score WHERE c_no = '3-105' AND degree > (SELECT degree FROM score WHERE s_no = '109' AND c_no = '3-105');

Subquery - 2

Check the 3-105 course records of all students whose scores are higher than 109.

-- Course number is not limited, as long as the score is greater than 3 of classmate 109-105 The course result is OK. SELECT * FROM score WHERE degree > (SELECT degree FROM score WHERE s_no = '109' AND c_no = '3-105');

YEAR function and query with IN keyword

Query all no, name, birthday columns of students 101 and 108 born in the same year.

-- YEAR(..): Retrieve year from date SELECT no, name, birthday FROM student WHERE YEAR(birthday) IN (SELECT YEAR(birthday) FROM student WHERE no IN (101, 108));

Multi layer nested subquery

Query the student achievement table of teacher Zhang Xu.

First find the teacher number:

SELECT NO FROM teacher WHERE NAME = 'Zhang Xu'

Find the teacher's course number through sourse table:

SELECT NO FROM course WHERE t_no = ( SELECT NO FROM teacher WHERE NAME = 'Zhang Xu' );

Query the grade table by the selected course number:

SELECT * FROM score WHERE c_no = ( SELECT no FROM course WHERE t_no = ( SELECT no FROM teacher WHERE NAME = 'Zhang Xu' ) );

multi-table query

Query the names of teachers with more than 5 students in an elective course.

First, in the teacher table, judge whether the teacher has at least 5 students in the same course according to the no field:

-- query teacher surface SELECT no, name FROM teacher; +-----+--------+ | no | name | +-----+--------+ | 804 | Li Cheng | | 825 | Wang Ping | | 831 | Liu Bing | | 856 | Zhang Xu | +-----+--------+ SELECT name FROM teacher WHERE no IN ( -- Find the corresponding conditions here );

To view information about tables related to teacher number:

SELECT * FROM course; -- t_no: Teacher number +-------+-----------------+------+ | no | name | t_no | +-------+-----------------+------+ | 3-105 | Introduction to computer | 825 | | 3-245 | operating system | 804 | | 6-166 | digital circuit | 856 | | 9-888 | Advanced mathematics | 831 | +-------+-----------------+------+

We have found that the fields related to the teacher number are in the course table, but we can't know which course has at least 5 students to take, so we need to query according to the score table:

-- Before that score Insert some data to enrich the query criteria. INSERT INTO score VALUES ('101', '3-105', '90'); INSERT INTO score VALUES ('102', '3-105', '91'); INSERT INTO score VALUES ('104', '3-105', '89'); -- query score surface SELECT * FROM score; +------+-------+--------+ | s_no | c_no | degree | +------+-------+--------+ | 101 | 3-105 | 90 | | 102 | 3-105 | 91 | | 103 | 3-105 | 92 | | 103 | 3-245 | 86 | | 103 | 6-166 | 85 | | 104 | 3-105 | 89 | | 105 | 3-105 | 88 | | 105 | 3-245 | 75 | | 105 | 6-166 | 79 | | 109 | 3-105 | 76 | | 109 | 3-245 | 68 | | 109 | 6-166 | 81 | +------+-------+--------+ -- stay score In the table c_no As a group, and limit c_no Hold at least 5 pieces of data. SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) > 5; +-------+ | c_no | +-------+ | 3-105 | +-------+

According to the selected course number, find out the teacher number of at least 5 students in a course:

SELECT t_no FROM course WHERE no IN ( SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) > 5 ); +------+ | t_no | +------+ | 825 | +------+

In the teacher table, find the teacher's name according to the filtered teacher number:

SELECT name FROM teacher WHERE no IN ( -- Final conditions SELECT t_no FROM course WHERE no IN ( SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) > 5 ) );

Subquery - 3

Check the grade sheet of the computer department course.

The idea is to find out the number of all computer courses in the course table, and then query the score table according to the number.

-- adopt teacher Table query all `Computer Department` Teacher number of SELECT no, name, department FROM teacher WHERE department = 'Computer Department' +-----+--------+--------------+ | no | name | department | +-----+--------+--------------+ | 804 | Li Cheng | Computer Department | | 825 | Wang Ping | Computer Department | +-----+--------+--------------+ -- adopt course Table to query the course number of the teacher SELECT no FROM course WHERE t_no IN ( SELECT no FROM teacher WHERE department = 'Computer Department' ); +-------+ | no | +-------+ | 3-245 | | 3-105 | +-------+ -- Query the grade table according to the selected course number SELECT * FROM score WHERE c_no IN ( SELECT no FROM course WHERE t_no IN ( SELECT no FROM teacher WHERE department = 'Computer Department' ) ); +------+-------+--------+ | s_no | c_no | degree | +------+-------+--------+ | 103 | 3-245 | 86 | | 105 | 3-245 | 75 | | 109 | 3-245 | 68 | | 101 | 3-105 | 90 | | 102 | 3-105 | 91 | | 103 | 3-105 | 92 | | 104 | 3-105 | 89 | | 105 | 3-105 | 88 | | 109 | 3-105 | 76 | +------+-------+--------+

Use of UNION and NOTIN

Inquire about teachers with different titles in computer department and electronic engineering department.

-- NOT: Represent logical non SELECT * FROM teacher WHERE department = 'Computer Department' AND profession NOT IN ( SELECT profession FROM teacher WHERE department = 'Department of Electronic Engineering' ) -- Merge two sets UNION SELECT * FROM teacher WHERE department = 'Department of Electronic Engineering' AND profession NOT IN ( SELECT profession FROM teacher WHERE department = 'Computer Department' );

ANY means at least one - DESC (descending)

Check the score table of course 3-105 with a score of at least 3-245.

SELECT * FROM score WHERE c_no = '3-105'; +------+-------+--------+ | s_no | c_no | degree | +------+-------+--------+ | 101 | 3-105 | 90 | | 102 | 3-105 | 91 | | 103 | 3-105 | 92 | | 104 | 3-105 | 89 | | 105 | 3-105 | 88 | | 109 | 3-105 | 76 | +------+-------+--------+ SELECT * FROM score WHERE c_no = '3-245'; +------+-------+--------+ | s_no | c_no | degree | +------+-------+--------+ | 103 | 3-245 | 86 | | 105 | 3-245 | 75 | | 109 | 3-245 | 68 | +------+-------+--------+ -- ANY: accord with SQL Any condition in the statement. -- That is to say, in 3-105 As long as one of the scores is greater than 3-245 Any line filtered out meets the conditions, -- Finally, query the results in descending order. SELECT * FROM score WHERE c_no = '3-105' AND degree > ANY( SELECT degree FROM score WHERE c_no = '3-245' ) ORDER BY degree DESC; +------+-------+--------+ | s_no | c_no | degree | +------+-------+--------+ | 103 | 3-105 | 92 | | 102 | 3-105 | 91 | | 101 | 3-105 | 90 | | 104 | 3-105 | 89 | | 105 | 3-105 | 88 | | 109 | 3-105 | 76 | +------+-------+--------+

Represents ALL

Check the score table for courses 3-105 and above 3-245.

-- Just change the last question slightly. -- ALL: accord with SQL All conditions in the statement. -- That is to say, in 3-105 In each line, the score should be greater than 3-245 Only when all lines are filtered out can they be qualified. SELECT * FROM score WHERE c_no = '3-105' AND degree > ALL( SELECT degree FROM score WHERE c_no = '3-245' ); +------+-------+--------+ | s_no | c_no | degree | +------+-------+--------+ | 101 | 3-105 | 90 | | 102 | 3-105 | 91 | | 103 | 3-105 | 92 | | 104 | 3-105 | 89 | | 105 | 3-105 | 88 | +------+-------+--------+

Copying data from a table as a condition query

Query the score table of a course that is lower than the average score of the course.

-- Query average score SELECT c_no, AVG(degree) FROM score GROUP BY c_no; +-------+-------------+ | c_no | AVG(degree) | +-------+-------------+ | 3-105 | 87.6667 | | 3-245 | 76.3333 | | 6-166 | 81.6667 | +-------+-------------+ -- query score surface SELECT degree FROM score; +--------+ | degree | +--------+ | 90 | | 91 | | 92 | | 86 | | 85 | | 89 | | 88 | | 75 | | 79 | | 76 | | 68 | | 81 | +--------+ -- Set the table b Act on table a Query data in -- score a (b): Declare table as a (b)， -- So you can use it a.c_no = b.c_no Query executed as a condition. SELECT * FROM score a WHERE degree < ( (SELECT AVG(degree) FROM score b WHERE a.c_no = b.c_no) ); +------+-------+--------+ | s_no | c_no | degree | +------+-------+--------+ | 105 | 3-245 | 75 | | 105 | 6-166 | 79 | | 109 | 3-105 | 76 | | 109 | 3-245 | 68 | | 109 | 6-166 | 81 | +------+-------+--------+

Subquery - 4

Query the name and department of all the teachers who teach courses (there are courses in the course table).

SELECT name, department FROM teacher WHERE no IN (SELECT t_no FROM course); +--------+-----------------+ | name | department | +--------+-----------------+ | Li Cheng | Computer Department | | Wang Ping | Computer Department | | Liu Bing | Department of Electronic Engineering | | Zhang Xu | Department of Electronic Engineering | +--------+-----------------+

Conditional plus group filtering

Query the class of at least 2 boys in the student table.

-- View student table information SELECT * FROM student; +-----+-----------+-----+------------+-------+ | no | name | sex | birthday | class | +-----+-----------+-----+------------+-------+ | 101 | Zeng Hua | male | 1977-09-01 | 95033 | | 102 | Kuang Ming | male | 1975-10-02 | 95031 | | 103 | Wang Li | female | 1976-01-23 | 95033 | | 104 | Li Jun | male | 1976-02-20 | 95033 | | 105 | Wang Fang | female | 1975-02-10 | 95031 | | 106 | land force | male | 1974-06-03 | 95031 | | 107 | Wang NIMA | male | 1976-02-20 | 95033 | | 108 | Open the whole egg | male | 1975-02-10 | 95031 | | 109 | Zhao Tiezhu | male | 1974-06-03 | 95031 | | 110 | Zhang Fei | male | 1974-06-03 | 95038 | +-----+-----------+-----+------------+-------+ -- Only the gender is male, then press class Group and limit class The row is greater than 1. SELECT class FROM student WHERE sex = 'male' GROUP BY class HAVING COUNT(*) > 1; +-------+ | class | +-------+ | 95033 | | 95031 | +-------+

Not like fuzzy query negation

Query the student records in the student table that do not have the surname "Wang".

-- NOT: Reverse -- LIKE: Fuzzy query mysql> SELECT * FROM student WHERE name NOT LIKE 'king%'; +-----+-----------+-----+------------+-------+ | no | name | sex | birthday | class | +-----+-----------+-----+------------+-------+ | 101 | Zeng Hua | male | 1977-09-01 | 95033 | | 102 | Kuang Ming | male | 1975-10-02 | 95031 | | 104 | Li Jun | male | 1976-02-20 | 95033 | | 106 | land force | male | 1974-06-03 | 95031 | | 108 | Open the whole egg | male | 1975-02-10 | 95031 | | 109 | Zhao Tiezhu | male | 1974-06-03 | 95031 | | 110 | Zhang Fei | male | 1974-06-03 | 95038 | +-----+-----------+-----+------------+-------+

YEAR and NOW functions

Query the name and age of each student in the student table.

-- Using functions YEAR(NOW()) Calculate the current year and subtract the year of birth to get the age. SELECT name, YEAR(NOW()) - YEAR(birthday) as age FROM student; +-----------+------+ | name | age | +-----------+------+ | Zeng Hua | 42 | | Kuang Ming | 44 | | Wang Li | 43 | | Li Jun | 43 | | Wang Fang | 44 | | land force | 45 | | Wang NIMA | 43 | | Open the whole egg | 44 | | Zhao Tiezhu | 45 | | Zhang Fei | 45 | +-----------+------+

MAX and MIN functions

Query the maximum and minimum birthday values in the student table.

SELECT MAX(birthday), MIN(birthday) FROM student; +---------------+---------------+ | MAX(birthday) | MIN(birthday) | +---------------+---------------+ | 1977-09-01 | 1974-06-03 | +---------------+---------------+

Multi segment sorting

Query the student table in the order of class and birthday from large to small.

SELECT * FROM student ORDER BY class DESC, birthday; +-----+-----------+-----+------------+-------+ | no | name | sex | birthday | class | +-----+-----------+-----+------------+-------+ | 110 | Zhang Fei | male | 1974-06-03 | 95038 | | 103 | Wang Li | female | 1976-01-23 | 95033 | | 104 | Li Jun | male | 1976-02-20 | 95033 | | 107 | Wang NIMA | male | 1976-02-20 | 95033 | | 101 | Zeng Hua | male | 1977-09-01 | 95033 | | 106 | land force | male | 1974-06-03 | 95031 | | 109 | Zhao Tiezhu | male | 1974-06-03 | 95031 | | 105 | Wang Fang | female | 1975-02-10 | 95031 | | 108 | Open the whole egg | male | 1975-02-10 | 95031 | | 102 | Kuang Ming | male | 1975-10-02 | 95031 | +-----+-----------+-----+------------+-------+

Subquery - 5

Inquire about "male" teachers and their courses.

SELECT * FROM course WHERE t_no in (SELECT no FROM teacher WHERE sex = 'male'); +-------+--------------+------+ | no | name | t_no | +-------+--------------+------+ | 3-245 | operating system | 804 | | 6-166 | digital circuit | 856 | +-------+--------------+------+

MAX function and subquery

Check the score table of the students with the highest score.

-- Find the highest score (this query can only have one result) SELECT MAX(degree) FROM score; -- According to the above criteria, all the highest scores are selected, -- The query may have multiple results, assuming degree The value meets the criteria more than once. SELECT * FROM score WHERE degree = (SELECT MAX(degree) FROM score); +------+-------+--------+ | s_no | c_no | degree | +------+-------+--------+ | 103 | 3-105 | 92 | +------+-------+--------+

Subquery - 6

Query the name s of all students of the same sex with "Li Jun".

-- First, take Li Jun's gender as a condition SELECT sex FROM student WHERE name = 'Li Jun'; +-----+ | sex | +-----+ | male | +-----+ -- Query by gender name and sex SELECT name, sex FROM student WHERE sex = ( SELECT sex FROM student WHERE name = 'Li Jun' ); +-----------+-----+ | name | sex | +-----------+-----+ | Zeng Hua | male | | Kuang Ming | male | | Li Jun | male | | land force | male | | Wang NIMA | male | | Open the whole egg | male | | Zhao Tiezhu | male | | Zhang Fei | male | +-----------+-----+

Subquery - 7

Check the name s of classmates who are the same sex as "Li Jun".

SELECT name, sex, class FROM student WHERE sex = ( SELECT sex FROM student WHERE name = 'Li Jun' ) AND class = ( SELECT class FROM student WHERE name = 'Li Jun' ); +-----------+-----+-------+ | name | sex | class | +-----------+-----+-------+ | Zeng Hua | male | 95033 | | Li Jun | male | 95033 | | Wang NIMA | male | 95033 | +-----------+-----+-------+

Subquery - 8

Query all the "male" students who take the "Introduction to computer" course.

The required "Introduction to computer" and gender "male" numbers can be found in the course and student tables.

SELECT * FROM score WHERE c_no = ( SELECT no FROM course WHERE name = 'Introduction to computer' ) AND s_no IN ( SELECT no FROM student WHERE sex = 'male' ); +------+-------+--------+ | s_no | c_no | degree | +------+-------+--------+ | 101 | 3-105 | 90 | | 102 | 3-105 | 91 | | 104 | 3-105 | 89 | | 109 | 3-105 | 76 | +------+-------+--------+

Query by level

Create a grade table to represent the student's grade and insert the data:

CREATE TABLE grade ( low INT(3), upp INT(3), grade char(1) ); INSERT INTO grade VALUES (90, 100, 'A'); INSERT INTO grade VALUES (80, 89, 'B'); INSERT INTO grade VALUES (70, 79, 'C'); INSERT INTO grade VALUES (60, 69, 'D'); INSERT INTO grade VALUES (0, 59, 'E'); SELECT * FROM grade; +------+------+-------+ | low | upp | grade | +------+------+-------+ | 90 | 100 | A | | 80 | 89 | B | | 70 | 79 | C | | 60 | 69 | D | | 0 | 59 | E | +------+------+-------+

Query s for all students_ no ,c_no and grade columns.

The idea is to use the "BETWEEN" query to judge the students' grades BETWEEN low and upp in the grade table.

SELECT s_no, c_no, grade FROM score, grade WHERE degree BETWEEN low AND upp; +------+-------+-------+ | s_no | c_no | grade | +------+-------+-------+ | 101 | 3-105 | A | | 102 | 3-105 | A | | 103 | 3-105 | A | | 103 | 3-245 | B | | 103 | 6-166 | B | | 104 | 3-105 | B | | 105 | 3-105 | B | | 105 | 3-245 | C | | 105 | 6-166 | C | | 109 | 3-105 | C | | 109 | 3-245 | D | | 109 | 6-166 | B | +------+-------+-------+

join query

To prepare data for a test connection query:

CREATE DATABASE testJoin; CREATE TABLE person ( id INT, name VARCHAR(20), cardId INT ); CREATE TABLE card ( id INT, name VARCHAR(20) ); INSERT INTO card VALUES (1, 'meal card'), (2, 'CCB card'), (3, 'ABC card'), (4, 'Business card'), (5, 'Post card'); SELECT * FROM card; +------+-----------+ | id | name | +------+-----------+ | 1 | meal card | | 2 | CCB card | | 3 | ABC card | | 4 | Business card | | 5 | Post card | +------+-----------+ INSERT INTO person VALUES (1, 'Zhang San', 1), (2, 'Li Si', 3), (3, 'Wang Wu', 6); SELECT * FROM person; +------+--------+--------+ | id | name | cardId | +------+--------+--------+ | 1 | Zhang San | 1 | | 2 | Li Si | 3 | | 3 | Wang Wu | 6 | +------+--------+--------+

Analyzing the two tables, it is found that the person table does not set a corresponding id foreign key for the cardId field in the card table. If it is set, the row with the cardId field value of 6 in person cannot be inserted, because the cardId value does not exist in the card table.

To query the related data in these two tables, you can use INNER JOIN to join them together.

-- INNER JOIN: Represents an inner connection, which splices two tables together. -- on: Indicates that a condition is to be executed. SELECT * FROM person INNER JOIN card on person.cardId = card.id; +------+--------+--------+------+-----------+ | id | name | cardId | id | name | +------+--------+--------+------+-----------+ | 1 | Zhang San | 1 | 1 | meal card | | 2 | Li Si | 3 | 3 | ABC card | +------+--------+--------+------+-----------+ -- take INNER If the keyword is omitted, the result is the same. -- SELECT * FROM person JOIN card on person.cardId = card.id;

Note: the entire table of the card is connected to the right.

The table on the left (person) is displayed completely. If the table on the right meets the conditions, it will be displayed. If it does not meet the conditions, it will be filled with NULL.

-- LEFT JOIN It's also called LEFT OUTER JOIN，The results of the two methods are the same. SELECT * FROM person LEFT JOIN card on person.cardId = card.id; +------+--------+--------+------+-----------+ | id | name | cardId | id | name | +------+--------+--------+------+-----------+ | 1 | Zhang San | 1 | 1 | meal card | | 2 | Li Si | 3 | 3 | ABC card | | 3 | Wang Wu | 6 | NULL | NULL | +------+--------+--------+------+-----------+

The right table (card) is displayed completely. If the left table meets the conditions, it will be displayed. If it does not meet the conditions, it will be filled with NULL.

SELECT * FROM person RIGHT JOIN card on person.cardId = card.id; +------+--------+--------+------+-----------+ | id | name | cardId | id | name | +------+--------+--------+------+-----------+ | 1 | Zhang San | 1 | 1 | meal card | | 2 | Li Si | 3 | 3 | ABC card | | NULL | NULL | NULL | 2 | CCB card | | NULL | NULL | NULL | 4 | Business card | | NULL | NULL | NULL | 5 | Post card | +------+--------+--------+------+-----------+

Display all data of the two tables completely.

-- MySQL This syntax is not supported for extraneous connections -- SELECT * FROM person FULL JOIN card on person.cardId = card.id; -- An error occurred: -- ERROR 1054 (42S22): Unknown column 'person.cardId' in 'on clause' -- MySQL Full join syntax, using UNION Merge the two tables together. SELECT * FROM person LEFT JOIN card on person.cardId = card.id UNION SELECT * FROM person RIGHT JOIN card on person.cardId = card.id; +------+--------+--------+------+-----------+ | id | name | cardId | id | name | +------+--------+--------+------+-----------+ | 1 | Zhang San | 1 | 1 | meal card | | 2 | Li Si | 3 | 3 | ABC card | | 3 | Wang Wu | 6 | NULL | NULL | | NULL | NULL | NULL | 2 | CCB card | | NULL | NULL | NULL | 4 | Business card | | NULL | NULL | NULL | 5 | Post card | +------+--------+--------+------+-----------+

affair

In MySQL, transactions are actually the smallest and indivisible unit of work. Transaction can guarantee the integrity of a business.

For example, our bank transfer:

-- a -> -100 UPDATE user set money = money - 100 WHERE name = 'a'; -- b -> +100 UPDATE user set money = money + 100 WHERE name = 'b';

In the actual project, if only one SQL statement is executed successfully and the other fails, the data will be inconsistent.

Therefore, when executing multiple associated SQL statements, the transaction may require that these SQL statements either succeed at the same time or fail at all.

How to control transactions - COMMIT / ROLLBACK

In MySQL, the auto commit state of transactions is on by default.

-- Query auto commit status of transactions SELECT @@AUTOCOMMIT; +--------------+ | @@AUTOCOMMIT | +--------------+ | 1 | +--------------+

The function of automatic submission: when we execute an SQL statement, the effect will be immediately reflected and cannot be rolled back.

What is rollback? for instance:

CREATE DATABASE bank; USE bank; CREATE TABLE user ( id INT PRIMARY KEY, name VARCHAR(20), money INT ); INSERT INTO user VALUES (1, 'a', 1000); SELECT * FROM user; +----+------+-------+ | id | name | money | +----+------+-------+ | 1 | a | 1000 | +----+------+-------+

As you can see, the data takes effect immediately after the insert statement is executed, because the transaction in MySQL automatically commits it to the database. The so-called rollback means to undo all executed SQL statements and roll them back to the state of the last data submission.

Use ROLLBACK in MySQL to perform a ROLLBACK:

-- Roll back to last commit ROLLBACK; SELECT * FROM user; +----+------+-------+ | id | name | money | +----+------+-------+ | 1 | a | 1000 | +----+------+-------+

Since all executed SQL statements have been submitted, the data has not been rolled back. So how can data be rolled back?

-- Turn off auto submit SET AUTOCOMMIT = 0; -- Query auto submit status SELECT @@AUTOCOMMIT; +--------------+ | @@AUTOCOMMIT | +--------------+ | 0 | +--------------+

After the auto commit is turned off, the test data is rolled back:

INSERT INTO user VALUES (2, 'b', 1000); -- close AUTOCOMMIT After that, the data changes are displayed in a virtual temporary data table, -- The changed data is not actually inserted into the data table. SELECT * FROM user; +----+------+-------+ | id | name | money | +----+------+-------+ | 1 | a | 1000 | | 2 | b | 1000 | +----+------+-------+ -- The real data in the data table is actually: +----+------+-------+ | id | name | money | +----+------+-------+ | 1 | a | 1000 | +----+------+-------+ -- Since the data has not been actually committed, you can use rollback ROLLBACK; -- Query again SELECT * FROM user; +----+------+-------+ | id | name | money | +----+------+-------+ | 1 | a | 1000 | +----+------+-------+

How to submit virtual data to database? Using COMMIT:

INSERT INTO user VALUES (2, 'b', 1000); -- Submit data manually (persistence), -- When the data is actually submitted to the database, the submitted data cannot be rolled back after execution. COMMIT; -- Test rollback after commit ROLLBACK; -- Query again (invalid rollback) SELECT * FROM user; +----+------+-------+ | id | name | money | +----+------+-------+ | 1 | a | 1000 | | 2 | b | 1000 | +----+------+-------+

summary

1. Auto submit

   • View Auto commit status: select@ @ autocommit;

   • Set auto commit status: SET AUTOCOMMIT = 0.

2. Submit manually

   @@When AUTOCOMMIT = 0, COMMIT the transaction using the COMMIT command.

3. Transaction rollback

   @@When AUTOCOMMIT = 0, use the ROLLBACK command to roll back the transaction.

The practical application of transaction, let's go back to bank transfer project:

-- transfer accounts UPDATE user set money = money - 100 WHERE name = 'a'; -- To account UPDATE user set money = money + 100 WHERE name = 'b'; SELECT * FROM user; +----+------+-------+ | id | name | money | +----+------+-------+ | 1 | a | 900 | | 2 | b | 1100 | +----+------+-------+

In this case, if an accident occurs during the transfer, you can use ROLLBACK to roll back to the last submitted state:

-- Suppose there is an accident with the transfer and it needs to be rolled back. ROLLBACK; SELECT * FROM user; +----+------+-------+ | id | name | money | +----+------+-------+ | 1 | a | 1000 | | 2 | b | 1000 | +----+------+-------+

At this time, we return to the state before the accident, that is to say, affairs provide us with an opportunity to repent. Assuming the data is not unexpected, you can manually submit the data to the data table: COMMIT.

Start transaction manually - BEGIN / START TRANSACTION

After the default commit of a transaction is turned on (@ @ AUTOCOMMIT = 1), transaction rollback cannot be used at this time. However, we can also manually start a transaction event so that it can be rolled back:

-- use BEGIN perhaps START TRANSACTION Manually open a transaction -- START TRANSACTION; BEGIN; UPDATE user set money = money - 100 WHERE name = 'a'; UPDATE user set money = money + 100 WHERE name = 'b'; -- Automatic commit is not enabled for manually opened transactions, -- At this time, the changed data is still saved in a temporary table. SELECT * FROM user; +----+------+-------+ | id | name | money | +----+------+-------+ | 1 | a | 900 | | 2 | b | 1100 | +----+------+-------+ -- Test rollback ROLLBACK; SELECT * FROM user; +----+------+-------+ | id | name | money | +----+------+-------+ | 1 | a | 1000 | | 2 | b | 1000 | +----+------+-------+

The COMMIT is still used to COMMIT the data. After committing, the rollback of this transaction cannot occur again.

BEGIN; UPDATE user set money = money - 100 WHERE name = 'a'; UPDATE user set money = money + 100 WHERE name = 'b'; SELECT * FROM user; +----+------+-------+ | id | name | money | +----+------+-------+ | 1 | a | 900 | | 2 | b | 1100 | +----+------+-------+ -- Submit data COMMIT; -- Test rollback (invalid because table data has been committed) ROLLBACK;

ACID characteristics and usage of transactions

Four characteristics of the transaction:

• A atomicity: transaction is the smallest unit and cannot be subdivided;
• C consistency: SQL statements in the same transaction must be guaranteed to succeed or fail at the same time;
• I isolation: there is isolation between transaction 1 and transaction 2;
• D persistence: once a transaction is committed, it can no longer be returned.

Transaction isolation

Transaction isolation can be divided into four types (performance from low to high):

1. READ UNCOMMITTED

   If there are multiple transactions, any transaction can see uncommitted data of other transactions.

2. READ COMMITTED

   Only data that has been committed by other transactions can be read.

3. REPEATABLE READ

   If multiple connections have transactions enabled, data records cannot be shared between transactions, otherwise only committed records can be shared.

4. SERIALIZABLE (SERIALIZABLE)

   All transactions will be executed in a fixed order, and write operations of the next transaction will continue after one transaction is executed.

To view the default isolation level for the current database:

-- MySQL 8.x, GLOBAL Represents the system level, not the session level. SELECT @@GLOBAL.TRANSACTION_ISOLATION; SELECT @@TRANSACTION_ISOLATION; +--------------------------------+ | @@GLOBAL.TRANSACTION_ISOLATION | +--------------------------------+ | REPEATABLE-READ | -- MySQL Can be read repeatedly. +--------------------------------+ -- MySQL 5.x SELECT @@GLOBAL.TX_ISOLATION; SELECT @@TX_ISOLATION;

Modify isolation level:

-- Set the system isolation level, LEVEL Indicates the isolation level to be set later (READ UNCOMMITTED). SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- Query the system isolation level and find that it has been modified. SELECT @@GLOBAL.TRANSACTION_ISOLATION; +--------------------------------+ | @@GLOBAL.TRANSACTION_ISOLATION | +--------------------------------+ | READ-UNCOMMITTED | +--------------------------------+

Test the isolation of READ UNCOMMITTED:

INSERT INTO user VALUES (3, 'Xiaoming', 1000); INSERT INTO user VALUES (4, 'Taobao store', 1000); SELECT * FROM user; +----+-----------+-------+ | id | name | money | +----+-----------+-------+ | 1 | a | 900 | | 2 | b | 1100 | | 3 | Xiaoming | 1000 | | 4 | Taobao store | 1000 | +----+-----------+-------+ -- Open a transaction operation data -- Suppose Xiao Ming bought a pair of 800 yuan shoes in Taobao store: START TRANSACTION; UPDATE user SET money = money - 800 WHERE name = 'Xiaoming'; UPDATE user SET money = money + 800 WHERE name = 'Taobao store'; -- Then Taobao shop in the other side of the query results, found that the money has arrived. SELECT * FROM user; +----+-----------+-------+ | id | name | money | +----+-----------+-------+ | 1 | a | 900 | | 2 | b | 1100 | | 3 | Xiaoming | 200 | | 4 | Taobao store | 1800 | +----+-----------+-------+

Since Xiao Ming's transfer is operated on a newly opened transaction, and the result of this operation can be seen by other transactions (Taobao store on the other side), the query result of Taobao store is correct, and Taobao store confirms the receipt of the account. But at this time, what happens if Xiaoming executes the ROLLBACK command on its transaction?

-- Xiaoming's business ROLLBACK; -- At this time, no matter who the other party is, if you go to the query result again, you will find: SELECT * FROM user; +----+-----------+-------+ | id | name | money | +----+-----------+-------+ | 1 | a | 900 | | 2 | b | 1100 | | 3 | Xiaoming | 1000 | | 4 | Taobao store | 1000 | +----+-----------+-------+

This is called dirty reading. One transaction reads the uncommitted data of another transaction. This is not allowed in actual development.

Set the isolation level to READ COMMITTED:

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT @@GLOBAL.TRANSACTION_ISOLATION; +--------------------------------+ | @@GLOBAL.TRANSACTION_ISOLATION | +--------------------------------+ | READ-COMMITTED | +--------------------------------+

In this way, when new transactions are connected, they can only query the submitted transaction data. But for the current transaction, they still see uncommitted data, for example:

-- Manipulating data transaction (current transaction) START TRANSACTION; UPDATE user SET money = money - 800 WHERE name = 'Xiaoming'; UPDATE user SET money = money + 800 WHERE name = 'Taobao store'; -- Although the isolation level is set to READ COMMITTED，But in the current transaction, -- It still sees temporary changes to the data in the data table, rather than the actual submitted data. SELECT * FROM user; +----+-----------+-------+ | id | name | money | +----+-----------+-------+ | 1 | a | 900 | | 2 | b | 1100 | | 3 | Xiaoming | 200 | | 4 | Taobao store | 1800 | +----+-----------+-------+ -- Suppose a new transaction is opened remotely at this time to connect to the database. $ mysql -u root -p12345612 -- At this time, the data queried by remote connection can only be submitted SELECT * FROM user; +----+-----------+-------+ | id | name | money | +----+-----------+-------+ | 1 | a | 900 | | 2 | b | 1100 | | 3 | Xiaoming | 1000 | | 4 | Taobao store | 1000 | +----+-----------+-------+

However, there is still a problem. That is to say, when a transaction operates data, other transactions interfere with the data of the transaction. For example:

-- When querying data, Xiao Zhang found that: SELECT * FROM user; +----+-----------+-------+ | id | name | money | +----+-----------+-------+ | 1 | a | 900 | | 2 | b | 1100 | | 3 | Xiaoming | 200 | | 4 | Taobao store | 1800 | +----+-----------+-------+ -- In the small table money Before the average, Xiao Wang did an operation: START TRANSACTION; INSERT INTO user VALUES (5, 'c', 100); COMMIT; -- At this time, the real data of the table is: SELECT * FROM user; +----+-----------+-------+ | id | name | money | +----+-----------+-------+ | 1 | a | 900 | | 2 | b | 1100 | | 3 | Xiaoming | 1000 | | 4 | Taobao store | 1000 | | 5 | c | 100 | +----+-----------+-------+ -- At this time, when Xiaozhang calculates the average value again, the calculation will be inconsistent: SELECT AVG(money) FROM user; +------------+ | AVG(money) | +------------+ | 820.0000 | +------------+

Although READ COMMITTED allows us to read only the committed data of other transactions, there will still be a problem, that is, inconsistency may occur when reading the data of the same table. This is called READ COMMITTED.

Set the isolation level to REPEATABLE READ:

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT @@GLOBAL.TRANSACTION_ISOLATION; +--------------------------------+ | @@GLOBAL.TRANSACTION_ISOLATION | +--------------------------------+ | REPEATABLE-READ | +--------------------------------+

Test REPEATABLE READ, assuming that START TRANSACTION is executed on two different connections:

-- Xiao Zhang - Chengdu START TRANSACTION; INSERT INTO user VALUES (6, 'd', 1000); -- Xiao Wang - Beijing START TRANSACTION; -- Xiao Zhang - Chengdu COMMIT;

After the current transaction is opened and before it is submitted, it cannot be queried. After it is submitted, it can be queried. However, if other transactions are opened before committing, the connection with the current operation transaction will not be queried on this transaction line. It is equivalent to creating a single thread.

No matter whether Xiaozhang has executed COMMIT or not, in Xiaowang's side, the transaction record of Xiaozhang will not be queried, but only the record of his own transaction:

SELECT * FROM user; +----+-----------+-------+ | id | name | money | +----+-----------+-------+ | 1 | a | 900 | | 2 | b | 1100 | | 3 | Xiaoming | 1000 | | 4 | Taobao store | 1000 | | 5 | c | 100 | +----+-----------+-------+

This is because Xiao Wang opened a new transaction before that, so there is no connection with other transactions on his new transaction line, that is to say, if other transactions are operating data at this time, it does not know.

However, in the real data table, Xiao Zhang has inserted a piece of data. But Xiao Wang didn't know at this time and inserted the same data. What would happen?

INSERT INTO user VALUES (6, 'd', 1000); -- ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY'

Error is reported. The operator is informed that there is a field with primary key of 6. This phenomenon is also called Unreal reading. The data submitted by one transaction cannot be read by other transactions.

As the name implies, all transactions are written serially. what do you mean? Change the isolation level to SERIALIZABLE:

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE; SELECT @@GLOBAL.TRANSACTION_ISOLATION; +--------------------------------+ | @@GLOBAL.TRANSACTION_ISOLATION | +--------------------------------+ | SERIALIZABLE | +--------------------------------+

Take Xiao Zhang and Xiao Wang for example:

-- Xiao Zhang - Chengdu START TRANSACTION; -- Xiao Wang - Beijing START TRANSACTION; -- Query the table before opening the transaction to prepare the operation data. SELECT * FROM user; +----+-----------+-------+ | id | name | money | +----+-----------+-------+ | 1 | a | 900 | | 2 | b | 1100 | | 3 | Xiaoming | 1000 | | 4 | Taobao store | 1000 | | 5 | c | 100 | | 6 | d | 1000 | +----+-----------+-------+ -- No. 7 Wang Xiaohua was found, so a piece of data was inserted: INSERT INTO user VALUES (7, 'Wang Xiaohua', 1000);

What happens at this point? Because the current isolation level is SERIALIZABLE, serialization means that if all transactions are placed in a serial queue, then all transactions will be executed in a fixed order, and the next write operation will continue after one transaction is executed (which means that only one transaction can be written in the queue at the same time).

According to this explanation, when Xiao Wang inserts data, he will wait until Xiao Zhang executes a COMMIT to end the transaction he is in, or the wait timeout occurs.