Yes, I’m going to learn mysql in the middle of the night.

The installation

Website: www.mysql.com/downloads/

window

Reference, but be sure to modify the path, there is no clear explanation in the video. www.bilibili.com/video/BV1GW…

If path conflict is displayed, delete the local directory first, and the installation program will automatically create directory index.

Navicat connect to database error: blog.csdn.net/ron03129596…

The use of mysql

Log in to and out of the MySQL server

#Log on to the MySQL
$ mysql -u root -p12345612

#Exit the MySQL database server
exit;
Copy the code

The basic grammar

The end symbol of an SQL statement must be;

-- Display all databases
show databases;

Create database
CREATE DATABASE test;

Mysql > alter database
use test;

-- Displays all tables in the database
show tables;

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

Look at the data table structure
-- describe pet;
desc pet;

- the lookup 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';

Drop data
DELETE FROM pet where name = 'squirrel';

- delete table
DROP TABLE myorder;
Copy the code

The data structure

Decimal type specification

column_name  DECIMAL(P,D);
-- P is the precision of the significant digit number. P The default value is 1 to 65.
-- D is the number of decimal places. D The default value is 0 to 30. MySQL requires D to be less than or equal to (<=)P.

amount DECIMAL(6.2);
In this example, the amount column can store up to six digits, with two decimal digits; Therefore, the amount column ranges from -9999.99 to 9999.99.Create a new table test2: the largest two-digit number is positive or negative99

+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int          | NO   | PRI | NULL    |       |
| num   | decimal(4.2) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

INSERT INTO test2(num) VALUES(12.1251);  - 12.13
INSERT INTO test2(num) VALUES(12.122);   - 12.12
INSERT INTO test2(num) VALUES(12.12516); - 12.13
INSERT INTO test2(num) VALUES(12.12516312312312312);  --12.13


mysql> INSERT INTO test2(num) VALUES(89.991);
Query OK, 1 row affected, 1 warning (0.01 sec)   --89.99

mysql> INSERT INTO test2(num) VALUES(89.999999);  --90.00
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> INSERT INTO test2(num) VALUES(99.999999);
ERROR 1264 (22003) :Out of range value for column 'num' at row 1

mysql> INSERT INTO test2(num) VALUES(98.999999);  99.00
Query OK, 1 row affected, 1 warning (0.01 sec)

Copy the code

Build table constraints

Primary key constraint

What is a primary key?

In mysql, a primary key constraint is a column or a combination of multiple columns. Its value can uniquely identify each row in a table, through which entity integrity of the table can be enforced. The primary key is used to determine the uniqueness of the data. It is mainly used for foreign key association with other tables, as well as the modification and deletion of this record.

1. The main function is to determine the uniqueness of the data. For example, ID=1,NAME= zhang SAN. So if we want to find this in the database, we can use the select * from table where ID =1 and then we can find this guy. And this guy, which can also appear with the same name, uses ID as the main key.

Insert into If an ID is set to a primary key and a duplicate primary key is inserted, an error is reported and no UPDATE is performed. If you want an UPDATE, you must perform an UPDATE.

• PRIMAPYA primary key is a unique value in a table.
• AUTO_INCREMENTThe value is automatically incremented by 1 for each additional record.

Joint primary key: Use multiple fields together as the primary key of a table. Mysql primary key reference www.cnblogs.com/ccstu/p/121…

-- Primary key constraint
-- Make a field unique and not empty to ensure that all data in the table is unique.
CREATE TABLE user (
    id INT PRIMARY KEY.name VARCHAR(20));-- Federated primary key
Each field in the union primary key cannot be empty and cannot add up to duplicate the set union primary key.
CREATE TABLE user (
    id INT.name VARCHAR(20),
    password VARCHAR(20),
    PRIMARY KEY(id.name)); eg:insert into user2 values(1.'Joe'.'123');insert into user2 values(1.'Joe'.'123'); // Error: Duplicate entry '1- Duplicate entry 'for key 'PRIMARY-- Increment constraint
The primary key of the increment constraint is automatically incremented by the system.
CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)); // eg:insert into user3(name) values('Joe'); 

Add primary key constraint
-- If you forget to set the primary key, you can also set the primary key in the SQL statement (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;
Copy the code

The only constraints

The value of this field is unique and cannot be repeated.

Create a unique primary key when creating a table
CREATE TABLE user (
    id INT.name VARCHAR(20),
    UNIQUE(name));CREATE TABLE user2 (
    id INT.name VARCHAR(20),
    sex: VARCHAR(2),
    UNIQUE(id.name,dex) // multiple constraints like federated primary keys);Add a unique primary key
SQL > alter table create table create table create table create table create table
ALTER TABLE user ADD UNIQUE(name);
ALTER TABLE user MODIFY name VARCHAR(20) UNIQUE;

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

Not null constraint

Add a non-null constraint when creating a table
-- Constraints that a field cannot be empty
CREATE TABLE user (
    id INT.name VARCHAR(20) NOT NULL
);

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

The default constraints

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

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

alter table user3 modify info varchar(10) default 'beijing';
Copy the code

Foreign key constraints

- class
CREATE TABLE classes (
    id INT PRIMARY KEY.name VARCHAR(20));- the student table
CREATE TABLE students (
    id INT PRIMARY KEY.name VARCHAR(20),
    Class_id is associated with the ID field in classes
    class_id INT.The value for class_id must come from the id field in classes
    FOREIGN KEY(class_id) REFERENCES classes(id));Classes; students; classes; students;
-- 2. When a record in the primary table is referenced by a secondary table, the primary table cannot be deleted. delte from class where id=4; An error
Copy the code

Three design paradigms for databases

1NF

As long as field values can continue to be split, the first normal form is not satisfied. The more detailed the paradigm is, it may be better for some practices, but not for all, and needs to be set for the reality of the project.

2NF

To satisfy the first normal form, all other columns must depend entirely on the primary key column. Incomplete dependencies, if they occur, can only occur if the primary key is federated:

- orders table
CREATE TABLE myorder (
    product_id INT,
    customer_id INT,
    product_name VARCHAR(20),
    customer_name VARCHAR(20),
    PRIMARY KEY (product_id, customer_id)
);
Copy the code

In fact, in this order table, product_name only depends on product_id and customer_name only depends on customer_id. That is, product_name is irrelevant to customer_id, and customer_name is irrelevant to product_id.

This does not satisfy the second normal form: all other columns must depend entirely 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));Copy the code

After the split, product_id and Customer_id in the MyOrder table are completely dependent on the order_ID primary key, while the other fields in the Product and Customer tables are completely dependent on the primary key. Meet the second normal form of design!

3NF

Under the second normal form, there can be no transitive dependencies between columns except the primary key column.

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

Customer_phone in the table may depend on order_ID and customer_ID columns, which does not satisfy the third normal form design: there can be no transitive dependencies between the 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));Copy the code

After modification, there is no transfer dependency between the other columns, and the other columns only depend on the primary key column, satisfying the third normal form design!

Query practice

To prepare data

Create database
CREATE DATABASE select_test;
Mysql > alter 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.- the title
    department VARCHAR(20) NOT NULL Department of --
);

-- Create a class schedule
CREATE TABLE course (
    no VARCHAR(20) PRIMARY KEY.name VARCHAR(20) NOT NULL,
    t_no VARCHAR(20) NOT NULL.-- Teacher No.
    -- indicates that the TNO comes from the no field in the teacher table
    FOREIGN KEY(t_no) REFERENCES teacher(no));- record table
CREATE TABLE score (
    s_no VARCHAR(20) NOT NULL.-- Student Id
    c_no VARCHAR(20) NOT NULL.- course number
    degree DECIMAL.- grade
    S_no = student; c_no = course
    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'.'KuangMing'.'male'.'1975-10-02'.'95031');
INSERT INTO student VALUES('103'.'wang li'.'woman'.'1976-01-23'.'95033');
INSERT INTO student VALUES('104'.'李军'.'male'.'1976-02-20'.'95033');
INSERT INTO student VALUES('105'.'wang fang'.'woman'.'1975-02-10'.'95031');
INSERT INTO student VALUES('106'.'army'.'male'.'1974-06-03'.'95031');
INSERT INTO student VALUES('107'.'King Nima'.'male'.'1976-02-20'.'95033');
INSERT INTO student VALUES('108'.'Open the egg'.'male'.'1975-02-10'.'95031');
INSERT INTO student VALUES('109'.'Zhao Tie Zhu'.'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'.'male'.'1969-03-12'.'instructor'.'Department of Electrical Engineering');
INSERT INTO teacher VALUES('825'.'wang ping'.'woman'.'1972-05-05'.'助教'.'Computer Department');
INSERT INTO teacher VALUES('831'.'liu bing'.'woman'.'1977-08-14'.'助教'.'Department of Electrical Engineering');

-- Add class schedule data
INSERT INTO course VALUES(' '3-105..'Introduction to Computers'.'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 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');

Look at the table structure
SELECT * FROM course;
SELECT * FROM score;
SELECT * FROM student;
SELECT * FROM teacher;
Copy the code

1 to 10

Select * from student; select * from student
SELECT * FROM student;

Select * from student; select * from student; select * from student
SELECT name, sex, class FROM student;

Alter TABLE teacher alter table teacher alter table teacher
-- department: go to query again
-- Distinct
SELECT DISTINCT department FROM teacher;

Select * from score where score is between 60 and 80
-- BETWEEN xx AND xx;
SELECT * FROM score WHERE degree BETWEEN 60 AND 80;
SELECT * FROM score WHERE degree > 60 AND degree < 80;

Select * from score where score = 85, 86, or 88
-- IN: Query multiple values IN a specified value
SELECT * FROM score WHERE degree IN (85.86.88);

Select * from student where class '95031' or gender 'female'
-- or: indicates or relates
SELECT * FROM student WHERE class = '95031' or sex = 'woman';

Select * from student; select * from student
-- DESC: descending order, from high to low
-- ASC (default) : ascending, from low to high
SELECT * FROM student ORDER BY class DESC;
SELECT * FROM student ORDER BY class ASC;

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

Select * from class 95031 where student number = '95031'
- COUNT: statistics
SELECT COUNT(*) FROM student WHERE class = '95031';

Select student ID and course id from score table (subquery or sort query).
-- (SELECT MAX(degree) FROM score)
SELECT s_no, c_no FROM score WHERE degree = (SELECT MAX(degree) FROM score);

-- Sort query
-- limit: r, n: query n items of data starting from row r
If there are multiple highest scores, the sorting method may discard a record
SELECT s_no, c_no, degree FROM score ORDER BY degree DESC LIMIT 0.1;
Copy the code

The average score is calculated in groups

Query your gpa for 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: indicates GROUP query
Use group by to group the data into c_NO, and then calculate the average logic.
SELECT c_no, AVG(degree) FROM score GROUP BY c_no;
Copy the code

Group condition and fuzzy query

Like: But sometimes we need to get all the records of a field that contains the “COM” character. Then we need to use the SQL LIKE clause in the WHERE clause.

'a' % / / to the data at the end of a 'a %' / / data with a '% a %' / / '_a_' contains a data / / three and middle letter is a '_a / / two and at the end of letter is a' a_ / / two and the beginning of the letter is aCopy the code

SELECT * FROM position WHERE name LIKE 'Java %'; SELECT * FROM position WHERE name LIKE '% Java %';Copy the code

The queryscoreThe table shows the average score of the courses taken by at least 2 students 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 | | 81 | + 6-166------+-------+--------+
Copy the code

The analysis table found that at least two students took courses 3-105, 3-245, 6-166, and courses starting with 3 were 3-105, 3-245. Select * from 3-105; select * from 3-245; select * from 3-105;

AVG(degree) = degree (AVG(degree))
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 |
+-------+-------------+

Select a course that at least 2 students have taken
HAVING: Holding
HAVING COUNT(c_no) >= 2

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

Join SQL statement above;
-- Add a COUNT(*) to indicate that the number of groups 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 | + 76.3333 3-245-------+-------------+----------+
Copy the code

Multi-table query – 1

Query all student’sname, and the student inscoreTable corresponding toc_no 和 degree 。

SELECT no.name FROM student;
+-----+-----------+
| no  | name      |
+-----+-----------+102 | | 101 | Zeng Hua | | KuangMing | | 103 | wang li | | 104 | li | | 105 | | wang fang army | | 106 | | 107 | nima wang | | 108 | Zhang Quan egg | | 109 | tie-zhu zhao | +-----+-----------+

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 | | 81 | + 6-166------+-------+--------+
Copy the code

Score (s_no) = student (name); score (s_no) = student (name); score (s_no) = student (name)

-- FROM... : indicates to query information from the student or score table
S_no = score.s_no = score.s_no = score.s_no
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 | | | tie-zhu zhao | 3-105 | 76 | | tie-zhu zhao | 3-245 | 68 | | tie-zhu zhao | | 81 | + 6-166-----------+-------+--------+
Copy the code

Multi-table query – 2

Query all student’sno, Course Name (courseIn the tablename) and grades (scoreIn the tabledegreeA) column.

Only score is associated with students’ NO, so as long as you query the score table, you can find all 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 | | 81 | + 6-166------+-------+--------+
Copy the code

Then query the course table:

+-------+-----------------+
| no    | name            |
+-------+-----------------+| 3-105 introduction to | computer | | | 3-245 operating system | | | 6-166 digital circuit | | 9-888 | | + advanced mathematics-------+-----------------+
Copy the code

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

Select * from score; select * from course; select * from score; select * from course;
-- as takes 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 |
+------+-----------------+--------+Introduction to | 103 | computer | 92 | | 105 introduction to | computer | 88 | | 109 introduction to | computer | 76 | | 86 | | 103 | operating system | 75 | | 105 | operating system | | 68 | 109 operating system Digital circuits | | 85 | | 103 | | 105 | digital circuit | 79 | | 109 | | 81 | + digital circuit------+-----------------+--------+
Copy the code

Associated query of three tables

Query all student’sname, Course name (courseIn the tablename) anddegree 。

Only the student number and class number associated with the students in the score table, 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 | | 81 | + 6-166------+-------+--------+
Copy the code

Just replace s_no and c_no with the corresponding name field values in the student and srouse tables.

Select * from student where s_no = name;

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 | | | tie-zhu zhao | 3-105 | 76 | | tie-zhu zhao | 3-245 | 68 | | tie-zhu zhao | | 81 | + 6-166-----------+-------+--------+
Copy the code

Replace c_no with course name;

- schedule
SELECT no.name FROM course;
+-------+-----------------+
| no    | name            |
+-------+-----------------+| 3-105 introduction to | computer | | | 3-245 operating system | | | 6-166 digital circuit | | 9-888 | | + advanced mathematics-------+-----------------+

SQL > alter table name; alter 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;
Copy the code

Subquery plus grouping to find the average score

The query95031The average score of each subject in the class.

Select student’s class number and grade from score table according to student number in student table:

-- IN (..) : Select student id as s_NO conditional query
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 | +------+-------+--------+
Copy the code

At this time, as long as c_NO is grouped, we can 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) |
+-------+-------------+82.0000 | | 3-105 | | 3-245 71.5000 | | | | | 80.0000 6-166 +-------+-------------+
Copy the code

Subquery – 1

The query in3-105.In the course, all grades above109Student number.

First, screen out the lines whose class number is 3-105 and find all the lines whose grades are 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.);
Copy the code

Subquery – 2

Query all grades higher than109Student no.3-105.Course record.

-- There is no limit to course number, as long as the student's grade is greater than 109 course 3-105.
SELECT * FROM score
WHERE degree > (SELECT degree FROM score WHERE s_no = '109' AND c_no = ' '3-105.);
Copy the code

YEAR function and query with IN keyword

Query all and101 、108Student Number one was born in the same yearno 、name 、birthdayThe column.

-- YEAR(..) : Retrieves the year in the date
SELECT no.name, birthday FROM student
WHERE YEAR(birthday) IN (SELECT YEAR(birthday) FROM student WHERE no IN (101.108));
Copy the code

Multi-level nested subqueries

The query'zhang'A student’s grade sheet for a teacher’s class.

First find the teacher number:

SELECT NO FROM teacher WHERE NAME = 'zhang'
Copy the code

Find the teacher’s course number from the sourse table:

SELECT NO FROM course WHERE t_no = ( SELECT NO FROM teacher WHERE NAME = 'zhang' );
Copy the code

Query grade table by selected course id:

SELECT * FROM score WHERE c_no = (
    SELECT no FROM course WHERE t_no = ( 
        SELECT no FROM teacher WHERE NAME = 'zhang'));Copy the code

Multi-table query

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

First of all, in the teacher table, the no field is used to judge whether there are at least 5 students taking the same course of the teacher:

Alter table teacher
SELECT no.name FROM teacher;
+-----+--------+
| no  | name   |
+-----+--------+825 | | 804 | li cheng | | wang ping | | 831 | liu bing | | 856 | zhang xu | +-----+--------+

SELECT name FROM teacher WHERE no IN (
    -- Find the corresponding condition here
);
Copy the code

View information about the table associated with the teacher number:

SELECT * FROM course;
T_no: indicates the teacher id
+-------+-----------------+------+
| no    | name            | t_no |
+-------+-----------------+------+| 3-105 introduction to | computer | 825 | | 3-245 | 804 | | operating system | | digital circuit 6-166 | 856 | | 831 | + 9-888 | | advanced mathematics-------+-----------------+------+
Copy the code

We have found the fields related to the teacher number in the course table, but it is still impossible to know which course has at least 5 students taking, so we need to check according to the score table:

Insert some data into score before doing this 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');

Select score from score
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 +------+-------+--------+

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

Based on the selected course numbers, identify the teacher numbers with at least 5 students in a given course:

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

Select teacher from teacher where name = ‘teacher’;

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));Copy the code

Subquery – 3

Query the grade table for “Computer Science” courses.

The idea is to find the number of all the computer science courses in the course table, and then query the score table based on that number.

Select 'computer department' from 'teacher'
SELECT no.name, department FROM teacher WHERE department = 'Computer Department'
+-----+--------+--------------+
| no  | name   | department   |
+-----+--------+--------------+
| 804| li cheng department | | computer |825| wang ping | computer | +-----+--------+--------------+

-- Query the teacher's course number from the course table
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 | | 76 | + 3-105------+-------+--------+
Copy the code

Use of UNION and NOTIN

The queryComputer science department 与 Department of Electronic EngineeringIn different titles of teachers.

-- NOT: indicates logical non
SELECT * FROM teacher WHERE department = 'Computer Department' AND profession NOT IN (
    SELECT profession FROM teacher WHERE department = 'Department of Electrical Engineering'
)
Merge two sets
UNION
SELECT * FROM teacher WHERE department = 'Department of Electrical Engineering' AND profession NOT IN (
    SELECT profession FROM teacher WHERE department = 'Computer Department'
);
Copy the code

ANY indicates at least one -desc (in descending order)

Query course3-105.And the score is at least higher3-245. 的 scoreTable.

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 | | 68 | + 3-245------+-------+--------+

-- ANY: matches ANY condition in the SQL statement.
-- That is, in a 3-105 grade, as long as there is more than any row filtered from 3-245 to qualify,
-- Finally based on descending query results.
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 | +------+-------+--------+
Copy the code

Represents ALL of the ALL’s

Query course3-105.And the grades are higher than3-245. 的 scoreTable.

-- Just a slight modification of the previous question.
-- ALL: ALL conditions in the SQL statement are met.
In other words, each line of 3-105 must be greater than all the lines of 3-245 to qualify.
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 | | 88 | + 3-105------+-------+--------+
Copy the code

Replicate the table data as a conditional query

Query where a course score is lower than the course averagescoreTable.

-- Query average score
SELECT c_no, AVG(degree) FROM score GROUP BY c_no;
+-------+-------------+
| c_no  | AVG(degree) |
+-------+-------------+87.6667 | | 3-105 | | 3-245 76.3333 | | | | | 81.6667 6-166 +-------+-------------+

Select score from score
SELECT degree FROM score;
+--------+
| degree |
+--------+
|     90 |
|     91 |
|     92 |
|     86 |
|     85 |
|     89 |
|     88 |
|     75 |
|     79 |
|     76 |
|     68 |
|     81 |
+--------+

Alter table A alter table B alter table A
-- score a (b): declare a (b),
Select * from a.c_no = b.c_no;
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 | | 81 | + 6-166------+-------+--------+
Copy the code

Subquery – 4

Select name, department from course where course exists.

SELECT name, department FROM teacher WHERE no IN (SELECT t_no FROM course);
+--------+-----------------+
| name   | department      |
+--------+-----------------+| li cheng department | | computer | wang ping is | | computer | liu bing | electrical engineering department, | | zhang xu, | | + of electronic engineering--------+-----------------+
Copy the code

Condition plus group filter

The querystudentThere are at least two boys in the listclass 。

-- Check the student list
SELECT * FROM student;
+-----+-----------+-----+------------+-------+
| no  | name      | sex | birthday   | class |
+-----+-----------+-----+------------+-------+| 101 | Zeng Hua male | | 95033 | 1977-09-01 | | 102 | KuangMing male | | 95031 | 1975-10-02 | | 103 | wang li female | | 95033 | 1976-01-23 | | 104 | li male | | 95033 | 1976-02-20 | | 105 | | wang fang female | 95031 | 1975-02-10 | | 106 | | army men | 95031 | 1974-06-03 | | 107 | nima wang Male | | 95033 | 1976-02-20 | | 108 | | Zhang Quan eggs male | 95031 | 1975-02-10 | | 109 | tie-zhu zhao male | | 95031 | 1974-06-03 | | 110 | zhang fei | male | | 95038 | 1974-06-03 +-----+-----------+-----+------------+-------+

Select * from class; select * from class;
SELECT class FROM student WHERE sex = 'male' GROUP BY class HAVING COUNT(*) > 1;
+-------+
| class |
+-------+95031 | | 95033 | | +-------+
Copy the code

NOTLIKE Fuzzy query negation

The querystudentList of students not named “Wang”.

- NOT: take back
-- LIKE: fuzzy query
mysql> SELECT * FROM student WHERE name NOT LIKE 'the king %';
+-----+-----------+-----+------------+-------+
| no  | name      | sex | birthday   | class |
+-----+-----------+-----+------------+-------+| 101 | Zeng Hua male | | 95033 | 1977-09-01 | | 102 | KuangMing male | | 95031 | 1975-10-02 | | 104 | li male | | 95033 | 1976-02-20 | | 106 Army male | | 1974-06-03 | | 95031 | | 108 | | Zhang Quan eggs male | 95031 | 1975-02-10 | | 109 | tie-zhu zhao male | | 95031 | 1974-06-03 | | 110 | zhang fei | | | 95038 | 1974-06-03 +-----+-----------+-----+------------+-------+
Copy the code

YEAR and NOW functions

The querystudentName and age of each student in the table.

Use the function YEAR(NOW()) 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  |
+-----------+------+44 | | Zeng Hua 42 | | | KuangMing | | wang li 43 | | | li 43 | | | | wang fang 44 | | army 45 | | | wang nima 43 | | | Zhang Quan egg 44 | | | tie-zhu zhao 45 | | | zhang fei | 45  | +-----------+------+
Copy the code

MAX and MIN functions

The querystudentThe largest and smallest in the tablebirthdayValue.

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

The more sequences

In order toclass 和 birthdaySequential queries from largest to smalleststudentTable.

SELECT * FROM student ORDER BY class DESC, birthday;
+-----+-----------+-----+------------+-------+
| no  | name      | sex | birthday   | class |
+-----+-----------+-----+------------+-------+| | 110 zhang fei male | | 95038 | 1974-06-03 | | 103 | wang li female | | 95033 | 1976-01-23 | | 104 | li | | | 95033 | 1976-02-20 male | 107 | wang nima male | | 1976-02-20 | | 95033 | 101 | Zeng Hua male | | 95033 | 1977-09-01 | | 106 | | army men | 95031 | 1974-06-03 | | 109 | tie-zhu zhao Male | | 95031 | 1974-06-03 | | 105 | | wang fang female | 95031 | 1975-02-10 | | 108 | | Zhang Quan eggs male | 95031 | 1975-02-10 | | 102 | KuangMing | male | | 95031 | 1975-10-02 +-----+-----------+-----+------------+-------+
Copy the code

Subquery – 5

Query “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 | | 856 | + digital circuit-------+--------------+------+
Copy the code

MAX functions and subqueries

Query the student with the highest scorescoreTable.

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

Filter out all the top grades according to the above criteria,
This query may have multiple results, assuming that the degree value matches the condition multiple times.
SELECT * FROM score WHERE degree = (SELECT MAX(degree) FROM score);
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+| 103 | | 92 | + 3-105------+-------+--------+
Copy the code

Subquery – 6

Query all students of the same sex as “Li Jun”name 。

First of all, li Jun's gender was taken out as a condition
SELECT sex FROM student WHERE name = '李军';
+-----+
| sex |
+-----+| | +-----+

Select * from sex
SELECT name, sex FROM student WHERE sex = (
    SELECT sex FROM student WHERE name = '李军'
);
+-----------+-----+
| name      | sex |
+-----------+-----+Male | Zeng Hua | | | KuangMing male | | | li male | | | | army men | | wang nima male | | | | Zhang Quan eggs male | | tie-zhu zhao male | | | zhang fei | | +-----------+-----+
Copy the code

Subquery -7

Select * from students of the same gender and class as “li Jun”name 。

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 |
+-----------+-----+-------+Male | Zeng Hua | | 95033 | | li male | | 95033 | | nima wang | | | + 95033-----------+-----+-------+
Copy the code

Subquery -8

Query all male students enrolled in “Introduction to Computing”.

The required “Introduction to Computing” and “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 Computers'
) 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 | | 76 | + 3-105------+-------+--------+
Copy the code

Query by level

Select * from grade where student’s grade is; select * from grade where student’s grade is;

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     |
+------+------+-------+
Copy the code

Query all student’ss_no 、c_no 和 gradeThe column.

Select * from grade where the degree is BETWEEN low and UPP. Select * from grade where upP is BETWEEN low and UPP. Select * from grade where UPP is BETWEEN low and UPP.

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     |
+------+-------+-------+
Copy the code

Join queries

Prepare data to test 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.'the meal card'), (2.CCB Card), (3.Agricultural Bank Card), (4.'Business Card'), (5.'Postal card');
SELECT * FROM card;
+------+-----------+
| id   | name      |
+------+-----------+| | 1 meal card | | 2 | construction bank card | | 3 | agricultural bank card | | | 4 business card | | | | postal card + 5------+-----------+

INSERT INTO person VALUES (1.'Joe'.1), (2.'bill'.3), (3.'Cathy'.6);
SELECT * FROM person;
+------+--------+--------+
| id   | name   | cardId |
+------+--------+--------+| | zhang SAN | 1 | | 2 | 3 |, dick, and harry | | 3 | fifty and | | + 6------+--------+--------+
Copy the code

Analysis of the two tables shows that the person table does not set an ID foreign key corresponding to the cardId field in the card table. If set, rows in person with the cardId value of 6 will not be inserted because the cardId value does not exist in the card table.

In the connection

To query the related data in the two tables, JOIN them together using an INNER JOIN.

INNER JOIN: represents an INNER JOIN, joining two tables together.
-- on: A condition is to be executed.
SELECT * FROM person INNER JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+| | zhang SAN | 1 | 1 | meal card | | 2 | 3 | 3 | |, dick, and harry | + agricultural bank card------+--------+--------+------+-----------+

The INNER keyword is omitted and the result is the same.
-- SELECT * FROM person JOIN card on person.cardId = card.id;
Copy the code

Note: The entire table of card is linked to the right.

The left outer join

The left table (Person) is displayed in its entirety, the right table is displayed if it meets the criteria, and NULL if it does not.

-- LEFT JOIN is also called LEFT OUTER JOIN. The result is the same with both methods.
SELECT * FROM person LEFT JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+| | | zhang SAN | 1 | 1 meal card | | 2 | 3 | 3 | |, dick, and harry agricultural bank card | | 3 | fifty and | | NULL | NULL | + 6------+--------+--------+------+-----------+
Copy the code

Right link

Display the right table (card) completely, the left table if the condition is displayed, do not meet the NULL.

SELECT * FROM person RIGHT JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+| | zhang SAN | 1 | 1 | meal card | | 2 | 3 | 3 | |, dick, and harry agricultural bank card | | NULL | NULL | NULL | 2 | | the construction bank card | NULL | NULL | NULL | | | 4 business card | NULL | NULL | NULL | | | postal card + 5------+--------+--------+------+-----------+
Copy the code

All external links

Complete display of all data for both tables.

MySQL does not support full external joins with this syntax
-- SELECT * FROM person FULL JOIN card on person.cardId = card.id;
-- Error:
-- ERROR 1054 (42S22): Unknown column 'person.cardId' in 'on clause'

MySQL full join syntax, which uses UNION to join 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      |
+------+--------+--------+------+-----------+| | zhang SAN | 1 | 1 | meal card | | 2 | 3 | 3 | |, dick, and harry agricultural bank card | | 3 | fifty and six | | NULL | NULL | | NULL | NULL | NULL | 2 | | the construction bank card | NULL | NULL | NULL | | 4 business card | | NULL | NULL | NULL | | | postal card + 5------+--------+--------+------+-----------+
Copy the code

The transaction

In MySQL, transactions are the smallest indivisible unit of work. Transactions ensure the integrity of a business.

Take 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';
Copy the code

In a real project, if only one SQL statement executes successfully and the other fails, data inconsistencies will occur.

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

How to control transactions – COMMIT/ROLLBACK

In MySQL, automatic commit status for transactions is enabled by default.

-- Queries automatic commit status of transactions
SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+| | + 1--------------+
Copy the code

What auto-commit does: When a SQL statement is executed, its effects are reflected immediately and cannot be rolled back.

What is a rollback? Here’s an 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 |
+----+------+-------+
Copy the code

As you can see, the data takes effect immediately after the insert statement is executed because transactions in MySQL automatically commit it to the database. Rollback means to undo all SQL statements that have been executed and roll them back to the state when the data was last committed.

MySQL > ROLLBACK with ROLLBACK

-- Rollback to last commit
ROLLBACK;

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+
Copy the code

Since all SQL statements executed have already been committed, the data has not been rolled back. So how do you make data rollback possible?

-- Turn off automatic submission
SET AUTOCOMMIT = 0;

-- Query the automatic submission status
SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            0 |
+--------------+
Copy the code

Will auto-commit be turned off after test data rollback:

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

When AUTOCOMMIT is turned off, data changes are displayed in a virtual temporary table.
The changed data is not actually inserted into the table.
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+

The actual data in the table is:
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+

Since the data has not actually been committed, you can use rollback
ROLLBACK;

SQL > alter database
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+
Copy the code

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

INSERT INTO user VALUES (2.'b'.1000);
-- Manually commit data (persistence),
Commit the data to the database. Commit data cannot be rolled back after execution.
COMMIT;

-- Test rollback after commit
ROLLBACK;

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

conclusion

1. Automatically submit

   • Check the automatic commit status: SELECT @@autocommit;

   • To SET the automatic commit status: SET AUTOCOMMIT = 0.

2. Manual submission

   If @@autocommit = 0, run the COMMIT command to COMMIT the transaction.

3. Transaction rollback

   When @@autocommit = 0, run the ROLLBACK command to ROLLBACK the transaction.

For the practical application of transactions, let’s return to the bank transfer project:

- transfer
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 |
+----+------+-------+
Copy the code

If an accident occurs during the transfer, you can use ROLLBACK to ROLLBACK to the last committed state:

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

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

At this point we are back to where we were before the accident, that is, the transaction gives us an opportunity to go back on it. Assuming nothing unexpected happens to the data, you can actually COMMIT the data to the table manually: COMMIT.

Manually START a TRANSACTION – BEGIN/START TRANSACTION

Once the default commit of a transaction is enabled (@@autoCOMMIT = 1), transaction rollback cannot be used at this point. However, we can also manually enable a transaction event that can be rolled back:

-- Manually START a TRANSACTION using BEGIN or START 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,
The changed data is still stored 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 |
+----+------+-------+
Copy the code

COMMIT data is still committed, and the rollback of the transaction cannot happen 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 already been committed)
ROLLBACK;
Copy the code

ACID characteristics and usage of transactions

Four characteristics of transactions:

• A atomicity: the transaction is the smallest unit and cannot be divided again;
• 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;
• D persistence: Once the transaction is completed (COMMIT), you can’t return (ROLLBACK).

Isolation of transactions

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

1. READ UNCOMMITTED

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

2. READ COMMITTED

   Only data already committed by other transactions can be read.

3. REPEATABLE READ (REPEATABLE READ)

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

4. SERIALIZABLE

   All transactions are executed in a fixed order, with one transaction executed before the next transaction writes.

To view the default isolation level for the current database:

-- MySQL 8.x, GLOBAL = system level, GLOBAL = session level
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
SELECT @@TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| REPEATABLE-READ                | -- MySQL's default isolation level, which can be read repeatedly.
+--------------------------------+

-- MySQL 5.x
SELECT @@GLOBAL.TX_ISOLATION;
SELECT @@TX_ISOLATION;
Copy the code

Modify isolation level:

-- Sets the 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               |
+--------------------------------+
Copy the code

Dirty read

Test READ UNCOMMITTED isolation:

INSERT INTO user VALUES (3.'Ming'.1000);
INSERT INTO user VALUES (4.Taobao Shop.1000);

SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+| 1 | a | 900 | | 2 | b | 1100 | | 3 | xiaoming | | 1000 | | 1000 | + 4 | taobao shop----+-----------+-------+

-- Start a transaction operation data
-- Suppose Xiao Ming buys a pair of shoes for 800 yuan at Taobao store:
START TRANSACTION;
UPDATE user SET money = money - 800 WHERE name = 'Ming';
UPDATE user SET money = money + 800 WHERE name = Taobao Shop;

-- The Taobao shop then checks the results on the other side and finds that the money has arrived.
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+| 1 | a | 900 | | 2 | b | 1100 | | 3 | xiaoming | | 200 | | 1800 | + 4 | taobao shop----+-----------+-------+
Copy the code

Since Xiao Ming’s transfer is operated on the newly opened transaction, and the result of this operation can be seen by other transactions (taobao shop of the other party), the query result of Taobao Shop is correct and the Taobao shop confirms the receipt of the account. But at this point, what happens if Ming executes the ROLLBACK command on the transaction it’s in?

-- Xiao Ming's affairs
ROLLBACK;

-- No matter who the other person is, if you query the result again, you will find:
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+| 1 | a | 900 | | 2 | b | 1100 | | 3 | xiaoming | | 1000 | | 1000 | + 4 | taobao shop----+-----------+-------+
Copy the code

This is called dirty reads, where one transaction reads data that another transaction has not committed yet. This is not allowed in real 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                 |
+--------------------------------+
Copy the code

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

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

While the isolation level is set to READ COMMITTED, in the current transaction,
It still sees temporary changes in the table, not actually committed data.
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+| 1 | a | 900 | | 2 | b | 1100 | | 3 | xiaoming | | 200 | | 1800 | + 4 | taobao shop----+-----------+-------+


Suppose a new transaction is started remotely, connecting to the database.
$ mysql -u root -p12345612

The data queried by the remote connection can only be submitted
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+| 1 | a | 900 | | 2 | b | 1100 | | 3 | xiaoming | | 1000 | | 1000 | + 4 | taobao shop----+-----------+-------+
Copy the code

The problem with this, however, is to assume that while one transaction is manipulating data, other transactions interfere with that transaction’s data. Such as:

-- Xiao Zhang found in the data query:
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+| 1 | a | 900 | | 2 | b | 1100 | | 3 | xiaoming | | 200 | | 1800 | + 4 | taobao shop----+-----------+-------+

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

Select * from table_name where table_name = 1;
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+2 | | 1 | 900 | | a | b | 1100 | | 3 | xiaoming | 1000 | | | 4 taobao shop | 1000 | | | | 100 | c + 5----+-----------+-------+

-- When Xiao Zhang calculates the average value again, there will be a case of inconsistent calculation:
SELECT AVG(money) FROM user;
+------------+
| AVG(money) |
+------------+| | + 820.0000------------+
Copy the code

Although READ COMMITTED allows us to only READ data COMMITTED by other transactions, the problem is that inconsistencies can occur when reading data from the same table. This is called READ COMMITTED.

Phantom read

Set isolation level to REPEATABLE READ:

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

Test REPEATABLE READ, assuming 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;
Copy the code

After the current transaction is started, it cannot be queried before the transaction is submitted, but can be queried after the transaction is submitted. However, if another transaction is started before the commit, the connection that currently operates on the transaction will not be queried on that transaction line. The equivalent of opening up a separate thread.

Regardless of whether Zhang has performed a COMMIT or not, wang will not query Zhang’s transaction record, but only his own transaction record:

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

This is because Wang started a new START TRANSACTION before this time, and his new TRANSACTION is disconnected from other transactions. In other words, if other transactions are manipulating data at this time, wang does not know about it.

However, the fact is that in the actual table, Zhang has already inserted a data. But wang doesn’t know that he inserted the same data. What happens?

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

An error was reported and the operation was told that a field with a primary key of 6 already exists. This phenomenon is also known as phantom reading, in which data committed by one transaction cannot be read by other transactions.

serialization

As the name implies, all writes to transactions are serialized. What do you mean? Change the isolation level to SERIALIZABLE:

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

Let’s take Xiao Zhang and Xiao Wang for example:

-- Xiao Zhang, Chengdu
START TRANSACTION;

-- Xiao Wang -- Beijing
START TRANSACTION;

Query the table and prepare the operation data before starting the transaction.
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+2 | | 1 | 900 | | a | b | 1100 | | 3 | xiaoming | 1000 | | 1000 | | | taobao shop 4 5 c | | | 100 | | 1000 | | d | + 6----+-----------+-------+

-- Found no 7 wang Xiaohua, so insert a data:
INSERT INTO user VALUES (7.'Wang Xiaohua'.1000);
Copy the code

So what’s going to happen? Since the isolation level is now SERIALIZABLE, serialization means: Assuming that all transactions are placed in a serial queue, all transactions are executed in a fixed order, with one transaction executed before the next transaction is written (meaning that only one transaction can be written to the queue at a time).

According to this interpretation, when Wang inserts data, he will be in a wait state until Zhang performs a COMMIT to end his transaction or a wait timeout occurs.