Summary of common knowledge points required for MySQL

Summary of common knowledge points required for MySQL

This article summarizes the common knowledge points required for MySQL. Share with you for your reference, the details are as follows:

Recently, when I was organizing sql, I found an excellent note. It was the note made by the original author when learning sql. I would like to share this summary with everyone. It can help everyone to do a comprehensive leak detection and troubleshooting for sql. Thanks to the original author hjzCy for his efforts. The original link is at the bottom of the article. If there are any errors, I hope everyone can point them out together!

Logging in and out of the MySQL server

# Log in to MySQL
$ mysql -u root -p12345612

# Exit MySQL database server exit;

Basic syntax

--Show all databases show databases;

-- Create a database CREATE DATABASE test;

-- Switch database use test;

-- Display all tables in the database show tables;

-- Create a data table CREATE TABLE pet (
  name VARCHAR(20),
  owner VARCHAR(20),
  species VARCHAR(20),
  sex CHAR(1),
  birth DATE,
  death DATE
);

-- View the 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 creation constraints

Primary key constraint

-- Primary key constraint -- Makes a field non-repeated and not empty, ensuring the uniqueness of all data in the table.
CREATE TABLE user (
  id INT PRIMARY KEY,
  name VARCHAR(20)
);

-- Joint primary key -- Each field in the joint primary key cannot be empty, and the sum of the fields cannot be the same as the set joint primary key.
CREATE TABLE user (
  id INT,
  name VARCHAR(20),
  password VARCHAR(20),
  PRIMARY KEY(id, name)
);

-- Auto-increment constraint -- The primary key of the auto-increment constraint is automatically assigned 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 set it through SQL statements (two ways):
ALTER TABLE user ADD PRIMARY KEY(id);
ALTER TABLE user MODIFY id INT PRIMARY KEY;

-- Delete the 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 a unique primary key -- If you did not set a unique key when you created the table, you can also set it through SQL statements (two ways):
ALTER TABLE user ADD UNIQUE(name);
ALTER TABLE user MODIFY name VARCHAR(20) UNIQUE;

-- Delete the unique primary key ALTER TABLE user DROP INDEX name;

Not Null Constraint

-- Add a non-null constraint when creating a table -- constrain a field to not be null CREATE TABLE user (
  id INT,
  name VARCHAR(20) NOT NULL
);

-- Remove the not null constraint ALTER TABLE user MODIFY name VARCHAR(20);

Default Constraints

-- Add a default constraint when creating a table -- constrain the default value of a field CREATE TABLE user2 (
  id INT,
  name VARCHAR(20),
  age INT DEFAULT 10
);

-- Remove the not null constraint ALTER TABLE user MODIFY age INT;

Foreign key constraints

-- Class CREATE TABLE classes (
  id INT PRIMARY KEY,
  name VARCHAR(20)
);

-- Student table CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(20),
  -- The class_id here should be associated with the id field in classes class_id INT,
  -- Indicates that the value of class_id must come from the id field value in classes FOREIGN KEY(class_id) REFERENCES classes(id)
);

-- 1. Data values ​​that are not in the main table (parent table) classes cannot be used in the secondary table (child table) students;
-- 2. When the records in the primary table are referenced by the secondary table, the primary table cannot be deleted.

Three major database design paradigms

1NF

As long as the field value can be further split, the first normal form is not met.

The more detailed the paradigm design is, the better it may be for certain practical operations, but it is not always beneficial and needs to be set according to the actual situation of the project.

2NF

Under the premise of satisfying the first paradigm, other columns must be completely dependent on the primary key column. If there is an incomplete dependency, it can only happen in the case of a composite primary key:

-- Order table 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 table, product_name only depends on product_id , customer_name only depends on customer_id . In other words, product_name and customer_id are irrelevant, and customer_name and product_id are also irrelevant.

This does not satisfy the second normal form: 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 the split, product_id and customer_id in myorder table are completely dependent on order_id primary key, and other fields in product and customer tables are completely dependent on the primary key. Satisfies the design of the second paradigm!

3NF

Under the premise of satisfying the second paradigm, there should be no transitive dependencies between columns other than the primary key column.

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

customer_phone in the table may depend on order_id and customer_id columns, which does not meet the design of the third normal form: there can be no transitive dependencies 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 the modification, there is no transitive dependency between other columns. Other columns only depend on the primary key column, which meets the design of the third normal form!

Query Exercise

Prepare the data

-- Create a database CREATE DATABASE select_test;
-- Switch database USE select_test;

-- Create a 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 a 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, -- job title department VARCHAR(20) NOT NULL -- department);

-- Create a course table CREATE TABLE course (
  no VARCHAR(20) PRIMARY KEY,
  name VARCHAR(20) NOT NULL,
  t_no VARCHAR(20) NOT NULL, -- teacher number -- indicates that tno comes from the value of the no field in the teacher table FOREIGN KEY(t_no) REFERENCES teacher(no)
);

-- Score table CREATE TABLE score (
  s_no VARCHAR(20) NOT NULL, -- student number c_no VARCHAR(20) NOT NULL, -- course number degree DECIMAL, -- grade -- indicates that s_no and c_no come from the no field value in the student and course tables respectively. FOREIGN KEY(s_no) REFERENCES student(no),
  FOREIGN KEY(c_no) REFERENCES course(no),
  -- Set s_no, c_no as the joint 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', '匡明', '男', '1975-10-02', '95031');
INSERT INTO student VALUES('103', '王丽', '女', '1976-01-23', '95033');
INSERT INTO student VALUES('104', '李军', '男', '1976-02-20', '95033');
INSERT INTO student VALUES('105', '王芳', '女', '1975-02-10', '95031');
INSERT INTO student VALUES('106', 'Army', 'Male', '1974-06-03', '95031');
INSERT INTO student VALUES('107', '王尼马', '男', '1976-02-20', '95033');
INSERT INTO student VALUES('108', '张全蛋', '男', '1975-02-10', '95031');
INSERT INTO student VALUES('109', '赵铁柱', '男', '1974-06-03', '95031');

-- Add teacher table data INSERT INTO teacher VALUES('804', 'Li Cheng', 'Male', '1958-12-02', 'Associate Professor', 'Computer Science Department');
INSERT INTO teacher VALUES('856', '张旭', '男', '1969-03-12', '讲座师', '电子工程部');
INSERT INTO teacher VALUES('825', '王萍', '女', '1972-05-05', '教助手', '计算机系');
INSERT INTO teacher VALUES('831', 'Liu Bing', 'Female', '1977-08-14', 'Teaching Assistant', 'Department of Electronic Engineering');

-- Add course data INSERT INTO course VALUES('3-105', 'Introduction to Computer Science', '825');
INSERT INTO course VALUES('3-245', 'Operating System', '804');
INSERT INTO course VALUES('6-166', 'Digital Circuits', '856');
INSERT INTO course VALUES('9-888', 'Advanced Mathematics', '831');

-- Add score table 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 the table structure SELECT * FROM course;
SELECT * FROM score;
SELECT * FROM student;
SELECT * FROM teacher;

1 to 10

-- Query all rows of the student table SELECT * FROM student;

-- Query all rows of the name, sex, and class fields in the student table SELECT name, sex, class FROM student;

-- Query the unique department column in the teacher table -- department: deduplication query SELECT DISTINCT department FROM teacher;

-- Query all rows in the score table with scores between 60 and 80 (interval query and operator query)
-- BETWEEN xx AND xx: query interval, AND means "and"
SELECT * FROM score WHERE degree BETWEEN 60 AND 80;
SELECT * FROM score WHERE degree > 60 AND degree < 80;

-- Query the rows in the score table with a score of 85, 86, or 88 -- IN: Query multiple values ​​in the specification SELECT * FROM score WHERE degree IN (85, 86, 88);

-- Query all rows in the student table whose class is '95031' or whose sex is 'female' -- or: indicates an or relationship SELECT * FROM student WHERE class = '95031' or sex = 'female';

-- Query all rows of the student table in descending order of class -- DESC: descending order, from high to low -- ASC (default): ascending order, from low to high SELECT * FROM student ORDER BY class DESC;
SELECT * FROM student ORDER BY class ASC;

-- Query all rows of the score table in ascending order of c_no and descending order of degree SELECT * FROM score ORDER BY c_no ASC, degree DESC;

-- Query the number of students in class "95031" -- COUNT: Statistics SELECT COUNT(*) FROM student WHERE class = '95031';

-- Query the student ID and course ID of the student with the highest score in the score table (subquery or sort query).
-- (SELECT MAX(degree) FROM score): Subquery to calculate the highest score SELECT s_no, c_no FROM score WHERE degree = (SELECT MAX(degree) FROM score);

-- Sorting query -- LIMIT r, n: means starting from the rth row, query n data SELECT s_no, c_no, degree FROM score ORDER BY degree DESC LIMIT 0, 1;

Calculate average scores by group

Query the average grade 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 conditions and fuzzy queries

Query the average scores of courses in the score table that are taken by at least 2 students and whose numbers start 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 |
+------+-------+--------+

The analysis table shows that at least 2 students have chosen the courses 3-105 , 3-245 , and 6-166 , and the courses starting with 3 are 3-105 and 3-245 . That is to say, we want to query the average degree scores of all 3-105 and 3-245 .

-- First, query c_no, AVG(degree) 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 |
+-------+-------------+

-- Query the courses that are taken by at least 2 students -- HAVING: indicates that HAVING COUNT(c_no) >= 2

-- and the course starts with 3 -- LIKE indicates a fuzzy query, "%" is a wildcard that matches any character after "3".
AND c_no LIKE '3%';

-- Concatenate the previous SQL statements together.
-- Adding a COUNT(*) at the end means that the number of each group will also be 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 name of all students, as well as c_no and degree corresponding to the student in score table.

SELECT no, name FROM student;
+-----+-----------+
| no | name |
+-----+-----------+
| 101 | Zeng Hua |
| 102 | Kuang Ming |
| 103 | Wang Li|
| 104 | Li Jun |
| 105 | Wang Fang|
| 106 | Army |
| 107 | Wang Nima |
| 108 | Zhang Quandan |
| 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 analysis, we can find that we only need to replace the s_no field value in score table with the corresponding name field value in the student table. How to do it?

-- FROM...: indicates querying from the student and score tables. -- The WHERE condition indicates that the result will be displayed only when student.no and score.s_no 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 course table) and grade ( degree in score table) columns of all students.

Only score is associated with the student's no , so by querying score table, you can find all no and degree associated with the student:

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 course table:

+-------+-----------------+
| no | name |
+-------+-----------------+
| 3-105 | Introduction to Computers |
| 3-245 | Operating Systems |
| 6-166 | Digital Circuits |
| 9-888 | Advanced Mathematics |
+-------+-----------------+

Just replace c_no in score table with the corresponding name field value in the course table.

-- Add a query field name to query from the score and course tables respectively.
-- as means taking an alias for the 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 Computers | 92 |
| 105 | Introduction to Computers | 88 |
| 109 | Introduction to Computers | 76 |
| 103 | Operating Systems | 86 |
| 105 | Operating Systems | 75 |
| 109 | Operating Systems | 68 |
| 103 | Digital Circuits | 85 |
| 105 | Digital Circuits | 79 |
| 109 | Digital Circuits | 81 |
+------+-----------------+--------+

Three table association query

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

Only the student ID and class ID are associated in score table, so we only 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 replace s_no and c_no with the corresponding name field values ​​in student and srouse tables.

First replace s_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 |
+-----------+-------+--------+

Then replace c_no with the name field in course table:

-- Course table SELECT no, name FROM course;
+-------+-----------------+
| no | name |
+-------+-----------------+
| 3-105 | Introduction to Computers |
| 3-245 | Operating Systems |
| 6-166 | Digital Circuits |
| 9-888 | Advanced Mathematics |
+-------+-----------------+

-- Since the field names are repeated, 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;

Subquery plus grouping to find average score

Query the average score of each course for students in class 95031 .

In score table, filter out the student's class number and grade according to the student number in student table:

-- IN (..): Use the selected student number as the condition for s_no 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 |
+------+-------+--------+

At this time, we only need to group c_no to get the average score of each course of students in class 95031 :

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

Query all records of students whose scores are higher than those of student No. 109 in course 3-105 .

First, filter out the class numbers 3-105 , and then find all the rows where the score is higher than that of student number 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

Query the score records of all courses 3-105 whose scores are higher than those of student No. 109 .

-- There is no restriction on the course number, as long as the score is higher than that of student No. 109 in courses 3-105.
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 that were born in the same year as students No. 101 and No. 108 .

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

Multi-level nested subqueries

Query the student score table of teacher '張旭' .

First find the teacher number:

SELECT NO FROM teacher WHERE NAME = '张旭'

Find the teacher's course number through sourse table:

SELECT NO FROM course WHERE t_no = ( SELECT NO FROM teacher WHERE NAME = '张旭' );

Query the score table by selecting the course number:

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

Multi-table query

Find the names of teachers who have more than 5 students taking an elective course.

First, in the teacher table, use the no field to determine whether there are at least 5 students taking the same course of the teacher:

-- Query the teacher table 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 condition here);

View the information of the table related to the teacher number:

SELECT * FROM course;
-- t_no: Teacher number+-------+-----------------+------+
| no | name | t_no |
+-------+-----------------+------+
| 3-105 | Introduction to Computers | 825 |
| 3-245 | Operating Systems | 804 |
| 6-166 | Digital Circuits | 856 |
| 9-888 | Advanced Mathematics | 831 |
+-------+-----------------+------+

We have found that the field related to the teacher number is in the course table, but we still don’t know which course has at least 5 students enrolled, so we need to query it based on score table:

-- Before this, insert some data into score to enrich the query conditions.
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 the score table 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 |
+------+-------+--------+

-- Use c_no as a group in the score table and limit c_no to hold at least 5 data items.
SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) > 5;
+-------+
| c_no |
+-------+
| 3-105 |
+-------+

Based on the filtered course numbers, find the teacher numbers with 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 based on the filtered teacher number:

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

Subquery - 3

Check the transcript of the "Computer Science Department" courses.

The idea is to first find the numbers of all計算機系courses in course table, and then query score table based on this number.

-- Query all teacher numbers of the Computer Science Department through the teacher table SELECT no, name, department FROM teacher WHERE department = 'Computer Science Department'
+-----+--------+--------------+
| no | name | department |
+-----+--------+--------------+
| 804 | Li Cheng | Department of Computer Science |
| 825 | Wang Ping | Department of Computer Science |
+-----+--------+--------------+

-- Query the teacher's course number through the course table SELECT no FROM course WHERE t_no IN (
  SELECT no FROM teacher WHERE department = 'Computer Science Department'
);
+-------+
| no |
+-------+
| 3-245 |
| 3-105 |
+-------+

-- Query the score table based on the filtered course number SELECT * FROM score WHERE c_no IN (
  SELECT no FROM course WHERE t_no IN (
    SELECT no FROM teacher WHERE department = 'Computer Science 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

Query teachers with different professional titles in計算機系and電子工程系.

-- NOT: represents logical negation SELECT * FROM teacher WHERE department = 'Computer Science' AND profession NOT IN (
  SELECT profession FROM teacher WHERE department = 'Electronic Engineering Department'
)
-- Combine two sets UNION
SELECT * FROM teacher WHERE department = 'Electronic Engineering Department' AND profession NOT IN (
  SELECT profession FROM teacher WHERE department = 'Computer Science Department'
);

ANY means at least one - DESC (descending order)

Query the score table for courses 3-105 whose scores are <u>at least</u> higher than 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: Meets any condition in the SQL statement.
-- That is, among the scores of 3-105, as long as there is one that is greater than any row selected from 3-245, it meets the condition.
--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 |
+------+-------+--------+

ALL

Query the score table for courses 3-105 and grades higher than 3-245 .

-- Just a slight modification to the previous question.
-- ALL: Meets all conditions in the SQL statement.
-- That is to say, the score in each row of 3-105 must be greater than all the rows filtered out from 3-245 to meet the conditions.
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 |
+------+-------+--------+

Copy the table data as a conditional query

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

-- Query the 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 the score table SELECT degree FROM score;
+--------+
| degree |
+--------+
| 90 |
| 91 |
| 92 |
| 86 |
| 85 |
| 89 |
| 88 |
| 75 |
| 79 |
| 76 |
| 68 |
| 81 |
+--------+

-- Apply table b to table a to query data -- score a (b): declare the table as a (b),
-- In this way, you can use a.c_no = b.c_no as the condition to execute the query.
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 name and department of all teachers who teach courses (there are courses in course table) .

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

Conditional group filtering

Query the student table for class that have at least 2 boys.

-- 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 | Army | Male | 1974-06-03 | 95031 |
| 107 | Wang Nima | Male | 1976-02-20 | 95033 |
| 108 | Zhang Quandan | Male | 1975-02-10 | 95031 |
| 109 | Zhao Tiezhu | Male | 1974-06-03 | 95031 |
| 110 | Zhang Fei | Male | 1974-06-03 | 95038 |
+-----+-----------+-----+------------+-------+

-- Only query the gender of male, then group by class, and limit the class rows to be greater than 1.
SELECT class FROM student WHERE sex = '男' GROUP BY class HAVING COUNT(*) > 1;
+-------+
| class |
+-------+
|95033|
|95031|
+-------+

NOTLIKE fuzzy query negation

Query the student table for records of students whose last name is not "Wang".

-- NOT: negation -- LIKE: fuzzy query mysql> SELECT * FROM student WHERE name NOT LIKE '王%';
+-----+-----------+-----+------------+-------+
| 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 | Army | Male | 1974-06-03 | 95031 |
| 108 | Zhang Quandan | 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.

-- Use the YEAR(NOW()) function to 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 |
| Army | 45 |
| Wang Nima | 43 |
| Zhang Quandan | 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 descending order of class and birthday .

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 | Army | Male | 1974-06-03 | 95031 |
| 109 | Zhao Tiezhu | Male | 1974-06-03 | 95031 |
| 105 | Wang Fang | Female | 1975-02-10 | 95031 |
| 108 | Zhang Quandan | Male | 1975-02-10 | 95031 |
| 102 | Kuang Ming | Male | 1975-10-02 | 95031 |
+-----+-----------+-----+------------+-------+

Subquery - 5

Query "male" teachers and the courses they teach.

SELECT * FROM course WHERE t_no in (SELECT no FROM teacher WHERE sex = '男');
+-------+--------------+------+
| no | name | t_no |
+-------+--------------+------+
| 3-245 | Operating Systems | 804 |
| 6-166 | Digital Circuits | 856 |
+-------+--------------+------+

MAX function with subqueries

Query the score table of the student with the highest score.

-- Find the highest score (this query can only have one result)
SELECT MAX(degree) FROM score;

-- Filter out all the highest score tables according to the above conditions,
-- This query may have multiple results, assuming the degree value meets the condition multiple times.
SELECT * FROM score WHERE degree = (SELECT MAX(degree) FROM score);
+------+-------+--------+
| s_no | c_no | degree |
+------+-------+--------+
| 103 | 3-105 | 92 |
+------+-------+--------+

Subquery - 6

Query name of all classmates with the same gender as "Li Jun".

-- First, take Li Jun's gender as a condition SELECT sex FROM student WHERE name = 'Li Jun';
+-----+
| sex |
+-----+
| Male |
+-----+

-- Query name and sex based on gender
SELECT name, sex FROM student WHERE sex = (
  SELECT sex FROM student WHERE name = '李军'
);
+-----------+-----+
| name | sex |
+-----------+-----+
| Zeng Hua| Male|
| Kuang Ming| Male|
| Li Jun| Male|
| Army| Male|
| Wang Nima | Male |
| Zhang Quandan | Male |
| Zhao Tiezhu| Male|
| Zhang Fei | Male |
+-----------+-----+

Subquery - 7

Query name classmates who are of the same gender and class as "李军".

SELECT name, sex, class FROM student WHERE sex = (
  SELECT sex FROM student WHERE name = '李军'
) AND class = (
  SELECT class FROM student WHERE name = '李军'
);
+-----------+-----+-------+
| name | sex | class |
+-----------+-----+-------+
| Zeng Hua | Male | 95033 |
| Li Jun | Male | 95033 |
| Wang Nima | Male | 95033 |
+-----------+-----+-------+

Subquery - 8

Query the transcript of all male students who have taken the course "Introduction to Computer Science".

The required "Introduction to Computer Science" and gender "Male" can be found in course and student tables.

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

Search by level

Create a grade table to represent the students' grades and insert 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_no , c_no , and grade columns for all students.

The idea is to use interval ( BETWEEN ) query to determine whether the student's grade ( degree ) is between low and upp in 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 |
+------+-------+-------+

Connection query

Prepare data for testing connection queries:

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, 'Rice Card'), (2, 'Construction Bank Card'), (3, 'Agricultural Bank Card'), (4, 'Industrial and Commercial Card'), (5, 'Postal Card');
SELECT * FROM card;
+------+-----------+
| id | name |
+------+-----------+
| 1 | Meal Card|
| 2 | CCB Card|
| 3 | Agricultural Bank Card|
| 4 | Business Card|
| 5 | Postal Card |
+------+-----------+

INSERT INTO person VALUES (1, '张三', 1), (2, '李四', 3), (3, '王五', 6);
SELECT * FROM person;
+------+--------+--------+
| id | name | cardId |
+------+--------+--------+
| 1 | Zhang San | 1 |
| 2 | Li Si | 3 |
| 3 | Wang Wu | 6 |
+------+--------+--------+

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

Inner Join

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

-- INNER JOIN: Indicates an inner join, which joins two tables together.
-- on: Indicates that a certain 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 | Agricultural Bank Card |
+------+--------+--------+------+-----------+

-- Omitting the INNER keyword will produce the same result.
-- SELECT * FROM person JOIN card on person.cardId = card.id;
Note: The entire table of card is linked to the right.

Left Outer Join

The left table ( person ) is fully displayed, and the right table is displayed if it meets the conditions, otherwise NULL is filled.

-- LEFT JOIN is also called LEFT OUTER JOIN. The query results of these 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 | Agricultural Bank Card |
| 3 | Wang Wu | 6 | NULL | NULL |
+------+--------+--------+------+-----------+

Right Outer Link

The table on the right ( card ) is fully displayed. The table on the left is displayed if it meets the conditions, otherwise NULL is filled in.

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 | Agricultural Bank Card |
| NULL | NULL | NULL | 2 | CCB Card |
| NULL | NULL | NULL | 4 | Business Card |
| NULL | NULL | NULL | 5 | Postal Card |
+------+--------+--------+------+-----------+

Full external links

Display all the data in the two tables completely.

-- MySQL does not support full outer join with this syntax -- 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 to merge 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 | Agricultural Bank Card |
| 3 | Wang Wu | 6 | NULL | NULL |
| NULL | NULL | NULL | 2 | CCB Card |
| NULL | NULL | NULL | 4 | Business Card |
| NULL | NULL | NULL | 5 | Postal Card |
+------+--------+--------+------+-----------+

Transactions

In MySQL, a transaction is actually the smallest indivisible unit of work. Transactions can ensure 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 an actual project, if only one SQL statement is executed successfully and the other fails, inconsistent data will occur.

Therefore, when executing multiple related SQL statements, a transaction may require that these SQL statements either execute successfully at the same time or all fail.

How to control transactions - COMMIT / ROLLBACK

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

-- Query the automatic commit status of the transaction SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
| 1 |
+--------------+

The role of automatic submission : When we execute a SQL statement, its effect will be reflected immediately and cannot be rolled back .

What is rollback? For example:

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 SQL statements that have been executed and roll them back to the state when the data was last submitted .

To perform a rollback in MySQL, use ROLLBACK :

-- Roll back to the 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 the data be rolled back?

-- Turn off autocommit SET AUTOCOMMIT = 0;

-- Query the automatic commit status SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
| 0 |
+--------------+

After turning off autocommit, the test data is rolled back:

INSERT INTO user VALUES (2, 'b', 1000);

-- After turning off AUTOCOMMIT, 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;

-- Query again SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
+----+------+-------+

So how do you actually submit the virtual data to the database? Using COMMIT :

INSERT INTO user VALUES (2, 'b', 1000);
-- Manually submit data (persistence),
-- Submit the data to the database. After execution, the submitted data cannot be rolled back.
COMMIT;

-- Test rollback ROLLBACK after committing;

-- Query again (rollback is invalid)
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
+----+------+-------+

Summarize

  1. Automatic submission

    • Check the auto-commit status: SELECT @@AUTOCOMMIT ;
    • Set the autocommit status: SET AUTOCOMMIT = 0 .
  2. Manual Submission

    When @@AUTOCOMMIT = 0 , use the COMMIT command to commit the transaction.

  3. Transaction Rollback

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

For the practical application of transactions , let's go back to the bank transfer project:

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

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

--Suppose something unexpected happens during the transfer and it needs to be rolled back.
ROLLBACK;

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
+----+------+-------+

At this point we are back to the state before the accident, that is, the transaction provides us with an opportunity to regret. Assuming that nothing unexpected happens to the data, you can manually commit the data to the table: COMMIT .

Manually start a transaction - BEGIN / START TRANSACTION

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

-- Use BEGIN or START TRANSACTION to manually start a transaction -- START TRANSACTION;
BEGIN;
UPDATE user set money = money - 100 WHERE name = 'a';
UPDATE user set money = money + 100 WHERE name = 'b';

-- Since the manually opened transaction does not have auto-commit turned on,
-- The changed data at this time 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 |
+----+------+-------+

COMMIT is still used to submit data. After submission, the transaction cannot be rolled back.

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 the table data has been committed)
ROLLBACK;

ACID characteristics and usage of transactions

Four characteristics of transactions:

  • A Atomicity : A transaction is the smallest unit and cannot be divided;
  • C consistency : SQL statements in the same transaction must succeed or fail at the same time;
  • I Isolation : Transaction 1 and Transaction 2 are isolated from each other.
  • D Persistence : Once a transaction ends ( COMMIT ), it cannot be returned ( ROLLBACK ).

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 the uncommitted data of other transactions.

  2. READ COMMITTED

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

  3. REPEATABLE READ (repeatable read)

    If multiple connections have transactions opened, data records cannot be shared between transactions. Otherwise, only submitted records can be shared.

  4. SERIALIZABLE

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

View the default isolation level of the current database:

-- For MySQL 8.x, GLOBAL indicates the system level, and no GLOBAL indicates the session level.
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
SELECT @@TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| REPEATABLE-READ | -- The default isolation level of MySQL, which allows repeatable reads.
+--------------------------------+

-- MySQL 5.x
SELECT @@GLOBAL.TX_ISOLATION;
SELECT @@TX_ISOLATION;

To change the isolation level:

-- Set the system isolation level. LEVEL indicates the isolation level to be set (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 |
+--------------------------------+

Dirty Read

Test READ UNCOMMITTED isolation:

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 | Xiao Ming | 1000 |
| 4 | Taobao Store | 1000 |
+----+-----------+-------+

-- Start a transaction to operate data -- Assume that Xiao Ming bought a pair of shoes for 800 yuan in a Taobao store:
START TRANSACTION;
UPDATE user SET money = money - 800 WHERE name = 'Xiao Ming';
UPDATE user SET money = money + 800 WHERE name = 'Taobao store';

--The Taobao store then checks the result on the other side and finds that the money has been received.
SELECT * FROM user;
+----+-----------+-------+
| id | name | money |
+----+-----------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | Xiao Ming | 200 |
| 4 | Taobao Store | 1800 |
+----+-----------+-------+

Since Xiao Ming's transfer is performed on a newly opened transaction, and the result of the operation can be seen by other transactions (the other party's Taobao store), the query result of the Taobao store is correct, and the Taobao store confirms the receipt. But at this moment, if Xiao Ming executes the ROLLBACK command on the transaction he is in, what will happen?

-- Xiao Ming's transaction ROLLBACK;

-- At this point, no matter who the other party is, if you search the results again, you will find:
SELECT * FROM user;
+----+-----------+-------+
| id | name | money |
+----+-----------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | Xiao Ming | 1000 |
| 4 | Taobao Store | 1000 |
+----+-----------+-------+

This is called a dirty read , where a transaction reads data that has not been committed by another transaction. This is not allowed in actual development.

Read Committed

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 transaction connections come in, they can only query the transaction data that has been submitted. But for the current transaction, they still see uncommitted data, for example:

-- Operating data transaction (current transaction)
START TRANSACTION;
UPDATE user SET money = money - 800 WHERE name = 'Xiao Ming';
UPDATE user SET money = money + 800 WHERE name = 'Taobao store';

-- Although the isolation level is set to READ COMMITTED, in the current transaction,
-- It still sees the temporarily changed data in the data table, not the actually submitted data.
SELECT * FROM user;
+----+-----------+-------+
| id | name | money |
+----+-----------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | Xiao Ming | 200 |
| 4 | Taobao Store | 1800 |
+----+-----------+-------+


-- Assume that a new transaction is opened remotely at this time and connected to the database.
$ mysql -u root -p12345612

-- At this time, the data queried by the remote connection can only be the submitted SELECT * FROM user;
+----+-----------+-------+
| id | name | money |
+----+-----------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | Xiao Ming | 1000 |
| 4 | Taobao Store | 1000 |
+----+-----------+-------+

However, there is still a problem with this. That is, suppose that when a transaction is operating data, other transactions interfere with the data of this transaction. For example:

-- When Xiao Zhang was querying the data, he found:
SELECT * FROM user;
+----+-----------+-------+
| id | name | money |
+----+-----------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | Xiao Ming | 200 |
| 4 | Taobao Store | 1800 |
+----+-----------+-------+

-- Before Xiao Zhang calculated the average value of money in the table, Xiao Wang performed an operation:
START TRANSACTION;
INSERT INTO user VALUES (5, 'c', 100);
COMMIT;

-- The actual data of the table at this time is:
SELECT * FROM user;
+----+-----------+-------+
| id | name | money |
+----+-----------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | Xiao Ming | 1000 |
| 4 | Taobao Store | 1000 |
| 5 | c | 100 |
+----+-----------+-------+

-- When Xiao Zhang 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 data that has been committed by other transactions, there is still a problem. When reading data from the same table, inconsistencies may occur. This is called non-repeatable read (READ COMMITTED) .

Phantom Read

Set the isolation level to REPEATABLE READ (repeatable read) :

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

To test REPEATABLE READ , assume 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 started, it cannot be queried before it is committed, but it can be queried after it is committed. However, if other transactions are opened before the commit, then the connection currently operating the transaction will not be queried on this transaction line. It is equivalent to opening up a separate thread.

Regardless of whether Xiao Zhang has executed COMMIT , Xiao Wang will not be able to query Xiao Zhang's transaction records, but only the records of his own transaction:

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

This is because Xiao Wang started a new transaction ( START TRANSACTION ) before , so the line of his new transaction is not connected with other transactions . In other words, if other transactions are operating data at this time, it is not aware of it.

However, the fact is that Xiao Zhang has already inserted a record in the real data table. But Xiao Wang didn't know this at this time and also inserted the same data. What would happen?

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

An error was reported, and the operation was informed that a field with primary key 6 already existed. This phenomenon is also called phantom read, where the data submitted by one transaction cannot be read by other transactions .

Serialization

As the name implies, all write operations of transactions are serialized. What's the meaning? Change the isolation level to SERIALIZABLE :

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

Let’s take Xiao Zhang and Xiao Wang as examples:

-- Xiao Zhang- Chengdu START TRANSACTION;

-- Xiao Wang - Beijing START TRANSACTION;

-- Query the table before starting the transaction and prepare to operate the data.
SELECT * FROM user;
+----+-----------+-------+
| id | name | money |
+----+-----------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | Xiao Ming | 1000 |
| 4 | Taobao Store | 1000 |
| 5 | c | 100 |
| 6 | d | 1000 |
+----+-----------+-------+

-- Found that there is no No. 7 Wang Xiaohua, so insert a piece of data:
INSERT INTO user VALUES (7, '王小花', 1000);

What happens at this point? Since the current isolation level is SERIALIZABLE , serialization means: assuming that all transactions are placed in a serial queue, all transactions will be executed in a fixed order , and the write operation of the next transaction will be executed after one transaction is completed ( this means that only one transaction write operation can be executed in the queue at the same time ).

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

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL common functions summary", "MySQL log operation skills", "MySQL transaction operation skills summary", "MySQL stored procedure skills" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.

You may also be interested in:
  • Analysis of some knowledge points about the difference between mysqli and mysql in php
  • Summary of Mysql basic knowledge points
  • Summary of MySQL Architecture Knowledge Points
  • Mysql join query principle knowledge points
  • MySQL knowledge points and commonly used MYSQL commands for the second-level computer exam
  • Summary of MySQL ALTER command knowledge points
  • Summary of basic knowledge and operations of MySQL database
  • Summary of B-tree index knowledge points in MySQL optimization
  • MySql knowledge points: transaction, index, lock principle and usage analysis

<<:  How to use nginx to build a static resource server

>>:  How to use css variables in JS

Recommend

MySQL 8.0.18 Installation Configuration Optimization Tutorial

Mysql installation, configuration, and optimizati...

Implementation of Nginx configuration of local image server

Table of contents 1. Introduction to Nginx 2. Ima...

Detailed instructions for installing SuPHP on CentOS 7.2

By default, PHP on CentOS 7 runs as apache or nob...

In-depth analysis of MySQL from deleting the database to running away_Advanced (I) - Data Integrity

1. Introduction to Data Integrity 1. Introduction...

Linux gzip command compression file implementation principle and code examples

gzip is a command often used in Linux systems to ...

Easyswoole one-click installation script and pagoda installation error

Frequently asked questions When you are new to ea...

Complete steps to install MySQL 8.0.x on Linux

MySQL Introduction to MySQL MySQL was originally ...

How to install the green version of MySQL Community Server 5.7.16 and implement remote login

1. Download MySQL Community Server 5.7.16 and ins...

What is a MySQL index? Ask if you don't understand

Table of contents Overview From Binary Tree to B+...

MySql cache query principle and cache monitoring and index monitoring introduction

Query Cache 1. Query Cache Operation Principle Be...

Sample code for deploying ELK using Docker-compose

environment Host IP 192.168.0.9 Docker version 19...

Detailed explanation of CSS3+JS perfect implementation of magnifying glass mode

About a year ago, I wrote an article: Analysis of...

Analysis of the difference between placing on and where in MySQL query conditions

Introduction When writing SQL today, I encountere...

CSS3 timeline animation

Achieve results html <h2>CSS3 Timeline</...

The latest virtual machine VMware 14 installation tutorial

First, I will give you the VMware 14 activation c...