MySQL Learning Notes

Keywords: MySQL

MySQL Learning Notes

Log on and exit MySQL server

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

# Exit MySQL database server
exit;

Basic Grammar

-- Show all databases
show databases;

-- Create a database
CREATE DATABASE test;

-- Switch database
use test;

-- Show all tables in the database
show tables;

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

-- View data table structure
-- describe pet;
desc pet;

-- Query Table
SELECT * from pet;

-- insert data
INSERT INTO pet VALUES ('puffball', 'Diane', 'hamster', 'f', '1990-03-30', NULL);

-- Modify data
UPDATE pet SET name = 'squirrel' where owner = 'Diane';

-- Delete data
DELETE FROM pet where name = 'squirrel';

-- Delete Table
DROP TABLE myorder;

Table building constraints

Primary Key Constraint

-- Primary Key Constraint
-- Make a field not duplicated and not empty to ensure the uniqueness of all the data in the table.
CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

-- composite keys
-- Each field in a union primary key cannot be empty, and together cannot be duplicated with a set union primary key.
CREATE TABLE user (
    id INT,
    name VARCHAR(20),
    password VARCHAR(20),
    PRIMARY KEY(id, name)
);

-- Self-increasing constraint
-- Primary keys for self-increasing constraints are allocated by the system automatically incrementing.
CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
);

-- Add Primary Key Constraint
-- If you forget to set the primary key, you can also use the SQL Statement settings (two ways):
ALTER TABLE user ADD PRIMARY KEY(id);
ALTER TABLE user MODIFY id INT PRIMARY KEY;

-- drop primary key
ALTER TABLE user drop PRIMARY KEY;

Unique Primary Key

-- Create a unique primary key when creating a table
CREATE TABLE user (
    id INT,
    name VARCHAR(20),
    UNIQUE(name)
);

-- Add unique primary key
-- If you did not set up a unique build when you built the table, you can also use the SQL Statement settings (two ways):
ALTER TABLE user ADD UNIQUE(name);
ALTER TABLE user MODIFY name VARCHAR(20) UNIQUE;

-- Delete Unique Primary Key
ALTER TABLE user DROP INDEX name;

Non-empty constraint

-- Add non-empty constraints when building tables
-- Constraint a field cannot be empty
CREATE TABLE user (
    id INT,
    name VARCHAR(20) NOT NULL
);

-- Remove non-empty constraints
ALTER TABLE user MODIFY name VARCHAR(20);

Default Constraints

-- Add default constraints when building tables
-- Constrain a field's default value
CREATE TABLE user2 (
    id INT,
    name VARCHAR(20),
    age INT DEFAULT 10
);

-- Remove non-empty constraints
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),
    -- There class_id And classes In id Field Association
    class_id INT,
    -- Express class_id The value of must come from classes In id field value
    FOREIGN KEY(class_id) REFERENCES classes(id)
);

-- 1. Main table (parent table) classes Data values that are not in the subtable (subtable) students Medium is not usable;
-- 2. The primary table cannot be deleted when a record in the primary table is referenced by a secondary table.

Three design paradigms for databases

1NF

The first paradigm is not satisfied as long as the field values can continue to be split.

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

2NF

Other columns must depend entirely on the primary key column, provided that the first paradigm is satisfied. If an incomplete dependency occurs, it can only occur if the primary key is federated:

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

In fact, in this order form, product_name depends only on product_id, customer_name depends only on customer_id. That is, product_name and customer_id is unrelated, customer_name and product_id is also irrelevant.

This does not satisfy the second paradigm: 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)
);

After splitting, product_in the myorder table ID and customer_id is entirely dependent on order_ The ID primary key, and the other fields in the product and customer tables are entirely dependent on the primary key. Design that meets the second paradigm!

3NF

Without satisfying the second paradigm, there can be no transitive dependencies between columns other than primary key columns.

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

Customer_in table Phone may depend on order_id, customer_ The ID column does not satisfy the design of the third paradigm: there can be no transitive dependency between other columns.

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

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

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

Query Practice

Preparing data

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

-- Create Student Table
CREATE TABLE student (
    no VARCHAR(20) PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    sex VARCHAR(10) NOT NULL,
    birthday DATE, -- Birthday
    class VARCHAR(20) -- In Class
);

-- Create Teacher Table
CREATE TABLE teacher (
    no VARCHAR(20) PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    sex VARCHAR(10) NOT NULL,
    birthday DATE,
    profession VARCHAR(20) NOT NULL, -- Title
    department VARCHAR(20) NOT NULL -- department
);

-- Create Course Schedule
CREATE TABLE course (
    no VARCHAR(20) PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    t_no VARCHAR(20) NOT NULL, -- Teacher number
    -- Represents the tno From teacher In table no field value
    FOREIGN KEY(t_no) REFERENCES teacher(no) 
);

-- Achievement Sheet
CREATE TABLE score (
    s_no VARCHAR(20) NOT NULL, -- Student Number
    c_no VARCHAR(20) NOT NULL, -- Course Number
    degree DECIMAL,	-- achievement
    -- Represents the s_no, c_no From student, course In table no field value
    FOREIGN KEY(s_no) REFERENCES student(no),	
    FOREIGN KEY(c_no) REFERENCES course(no),
    -- Set up s_no, c_no For union primary key
    PRIMARY KEY(s_no, c_no)
);

-- View all tables
SHOW TABLES;

-- Add Student Table Data
INSERT INTO student VALUES('101', 'Hua Zeng', 'male', '1977-09-01', '95033');
INSERT INTO student VALUES('102', 'Correct Ming', 'male', '1975-10-02', '95031');
INSERT INTO student VALUES('103', 'Wang Li', 'female', '1976-01-23', '95033');
INSERT INTO student VALUES('104', 'Li Jun', 'male', '1976-02-20', '95033');
INSERT INTO student VALUES('105', 'Wang Fang', 'female', '1975-02-10', '95031');
INSERT INTO student VALUES('106', 'Land force', 'male', '1974-06-03', '95031');
INSERT INTO student VALUES('107', 'Wang Nima', 'male', '1976-02-20', '95033');
INSERT INTO student VALUES('108', 'Zhang Quan Egg', 'male', '1975-02-10', '95031');
INSERT INTO student VALUES('109', 'Zhao Tieju', 'male', '1974-06-03', '95031');

-- Add Teacher Table Data
INSERT INTO teacher VALUES('804', 'Li Cheng', 'male', '1958-12-02', 'associate professor', 'Computer Department');
INSERT INTO teacher VALUES('856', 'Zhang Xu', 'male', '1969-03-12', 'lecturer', 'Department of Electronic Engineering');
INSERT INTO teacher VALUES('825', 'Wang Ping', 'female', '1972-05-05', 'Assistant', 'Computer Department');
INSERT INTO teacher VALUES('831', 'Liu Bing', 'female', '1977-08-14', 'Assistant', 'Department of Electronic Engineering');

-- Add Course Schedule 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 circuit', '856');
INSERT INTO course VALUES('9-888', 'Advanced mathematics', '831');

-- Add Add Add Report Sheet Data
INSERT INTO score VALUES('103', '3-105', '92');
INSERT INTO score VALUES('103', '3-245', '86');
INSERT INTO score VALUES('103', '6-166', '85');
INSERT INTO score VALUES('105', '3-105', '88');
INSERT INTO score VALUES('105', '3-245', '75');
INSERT INTO score VALUES('105', '6-166', '79');
INSERT INTO score VALUES('109', '3-105', '76');
INSERT INTO score VALUES('109', '3-245', '68');
INSERT INTO score VALUES('109', '6-166', '81');

-- View table structure
SELECT * FROM course;
SELECT * FROM score;
SELECT * FROM student;
SELECT * FROM teacher;

1 to 10

-- query student All rows of table
SELECT * FROM student;

-- query student In table name,sex and class All rows of a field
SELECT name, sex, class FROM student;

-- query teacher Not duplicated in tables department column
-- department: Remove Query
SELECT DISTINCT department FROM teacher;

-- query score Scores on the table are 60-80 All rows between (interval and operator queries)
-- BETWEEN xx AND xx: Query interval, AND Express "also"
SELECT * FROM score WHERE degree BETWEEN 60 AND 80;
SELECT * FROM score WHERE degree > 60 AND degree < 80;

-- query score 85 on the table, 86 Or 88 rows
-- IN: Multiple values in query specification
SELECT * FROM score WHERE degree IN (85, 86, 88);

-- query student In table '95031' Class or gender is 'female' All rows
-- or: Representation or relationship
SELECT * FROM student WHERE class = '95031' or sex = 'female';

-- with class Query in descending order student All rows of table
-- DESC: Descending, 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;

-- with c_no Ascending order, degree Descending Query score All rows of table
SELECT * FROM score ORDER BY c_no ASC, degree DESC;

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

-- query score The student number and course number (subquery or sorting query) with the highest score in the table.
-- (SELECT MAX(degree) FROM score): Subquery to get the highest score
SELECT s_no, c_no FROM score WHERE degree = (SELECT MAX(degree) FROM score);

--  Sort Query
-- LIMIT r, n: Indicates from r Row start, query n Bar data
SELECT s_no, c_no, degree FROM score ORDER BY degree DESC LIMIT 0, 1;

Group calculation average

Query the average score 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: Group Query
SELECT c_no, AVG(degree) FROM score GROUP BY c_no;

Grouping Conditions and Fuzzy Query

Query the score table for at least two students who have taken elections and the average score for a course that starts with three.

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 two students take 3-105, 3-245, 6-166 courses and 3-105, 3-245 courses starting with 3. That is, we want to query the degree average for all 3-105 and 3-245.

-- First put c_no, AVG(degree) Query by grouping
SELECT c_no, AVG(degree) FROM score GROUP BY c_no
+-------+-------------+
| c_no  | AVG(degree) |
+-------+-------------+
| 3-105 |     85.3333 |
| 3-245 |     76.3333 |
| 6-166 |     81.6667 |
+-------+-------------+

-- Find out which courses at least two students are taking
-- HAVING: Indicates possession
HAVING COUNT(c_no) >= 2

-- And a course that starts with three
-- LIKE Represents a fuzzy query,"%" Is a wildcard, matches "3" Any character that follows.
AND c_no LIKE '3%';

-- Put the front SQL Statements are stitched together,
-- Add one after COUNT(*)，Indicates that the number of each group is also queried.
SELECT c_no, AVG(degree), COUNT(*) FROM score GROUP BY c_no
HAVING COUNT(c_no) >= 2 AND c_no LIKE '3%';
+-------+-------------+----------+
| c_no  | AVG(degree) | COUNT(*) |
+-------+-------------+----------+
| 3-105 |     85.3333 |        3 |
| 3-245 |     76.3333 |        3 |
+-------+-------------+----------+

Multi-table Query - 1

Query the name of all the students and the corresponding c_in the score table for that student No and degree.

SELECT no, name FROM student;
+-----+-----------+
| no  | name      |
+-----+-----------+
| 101 | Hua Zeng      |
| 102 | Correct Ming      |
| 103 | Wang Li      |
| 104 | Li Jun      |
| 105 | Wang Fang      |
| 106 | Land force      |
| 107 | Wang Nima    |
| 108 | Zhang Quan Egg    |
| 109 | Zhao Tieju    |
+-----+-----------+

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

From the analysis, you can see that only s_in the score table is needed Replace the value of the no field with the corresponding name field value in the student table. What can I do?

-- FROM...: Represents from student, score Queries in Tables
-- WHERE The condition is expressed as if only student.no and score.s_no Show when 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 Tieju    | 3-105 |     76 |
| Zhao Tieju    | 3-245 |     68 |
| Zhao Tieju    | 6-166 |     81 |
+-----------+-------+--------+

Multi-table Query - 2

Query the no, course name (name in the coursetable) and grade columns for all students.

Only score is associated with student's no, so if you query the score table, you can find all student-related no and degree:

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

Then query the course table:

+-------+-----------------+
| no    | name            |
+-------+-----------------+
| 3-105 | Introduction to Computer Science      |
| 3-245 | operating system        |
| 6-166 | digital circuit        |
| 9-888 | Advanced mathematics        |
+-------+-----------------+

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

-- Add a query field name，From score,course Queries in these two tables.
-- as Indicates 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 Computer Science      |     92 |
| 105  | Introduction to Computer Science      |     88 |
| 109  | Introduction to Computer Science      |     76 |
| 103  | operating system        |     86 |
| 105  | operating system        |     75 |
| 109  | operating system        |     68 |
| 103  | digital circuit        |     85 |
| 105  | digital circuit        |     79 |
| 109  | digital circuit        |     81 |
+------+-----------------+--------+

Three-table Association query

Query all students for name, course name (name in coursetable), and degree.

Only the student and class numbers associated with the score table are available. We just need to query around the score table.

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

Just put s_no and c_ It's good to replace no with the corresponding name field values in the student and srouse tables.

First put s_no is replaced by 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 Tieju    | 3-105 |     76 |
| Zhao Tieju    | 3-245 |     68 |
| Zhao Tieju    | 6-166 |     81 |
+-----------+-------+--------+

Add c_no is replaced by the name field in the course table:

-- Class Schedule Card
SELECT no, name FROM course;
+-------+-----------------+
| no    | name            |
+-------+-----------------+
| 3-105 | Introduction to Computer Science      |
| 3-245 | operating system        |
| 6-166 | digital circuit        |
| 9-888 | Advanced mathematics        |
+-------+-----------------+

-- Because of duplicate field names, use "Table Name.Field name as alias" Instead.
SELECT student.name as s_name, course.name as c_name, degree 
FROM student, score, course
WHERE student.NO = score.s_no
AND score.c_no = course.no;

Average score for subqueries plus groups

Query the average results of each course for class 95031.

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

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

Just put c_ By grouping no together, we can get the average result of each class of 95031 students:

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 the records of all students whose grades are higher than 109 in the 3-105 courses.

Class numbers 3-105 were first screened out, and rows with higher grades than 109 were found.

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 3-105 course record of all students whose grades are higher than 109.

-- No limitation on course number, as long as the result is greater than 3 for Class 109-105 The course results are OK.
SELECT * FROM score
WHERE degree > (SELECT degree FROM score WHERE s_no = '109' AND c_no = '3-105');

YEAR Function and Query with IN Keyword

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

-- YEAR(..): Remove 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 performance table of the'Zhang Xu'teacher.

First find the teacher number:

SELECT NO FROM teacher WHERE NAME = 'Zhang Xu'

Find the teacher's course number through the sourse table:

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

Query the result table by the selected course number:

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

multi-table query

Query the name of a teacher who has more than five classmates in an optional course.

First, in the teacher table, the field no is used to determine if there are at least five students taking the same course for the teacher:

-- query teacher surface
SELECT no, name FROM teacher;
+-----+--------+
| no  | name   |
+-----+--------+
| 804 | Li Cheng   |
| 825 | Wang Ping   |
| 831 | Liu Bing   |
| 856 | Zhang Xu   |
+-----+--------+

SELECT name FROM teacher WHERE no IN (
    -- Find the conditions here
);

View information about tables related to the teacher number:

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

We've found a field related to the number of the teacher that's in the coursetable, but we don't know which course has at least five students taking it, so we'll need to look it up in the score table:

-- To score Insert some data to enrich the query.
INSERT INTO score VALUES ('101', '3-105', '90');
INSERT INTO score VALUES ('102', '3-105', '91');
INSERT INTO score VALUES ('104', '3-105', '89');

-- query score surface
SELECT * FROM score;
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 101  | 3-105 |     90 |
| 102  | 3-105 |     91 |
| 103  | 3-105 |     92 |
| 103  | 3-245 |     86 |
| 103  | 6-166 |     85 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+

-- stay score Table will c_no As a group and restricted c_no Hold at least five pieces of data.
SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) > 5;
+-------+
| c_no  |
+-------+
| 3-105 |
+-------+

Based on the selected course number, find out the number of a teacher who has at least five 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, the names of the teachers are found based on the number of the selected teachers:

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

Subquery - 3

Query the result sheet of the Computer Department course.

The idea is to first find the number of all the computer department courses in the coursetable, and then query the score table based on this number.

-- adopt teacher Table Query All `Computer Department` Teacher number
SELECT no, name, department FROM teacher WHERE department = 'Computer Department'
+-----+--------+--------------+
| no  | name   | department   |
+-----+--------+--------------+
| 804 | Li Cheng   | Computer Department     |
| 825 | Wang Ping   | Computer Department     |
+-----+--------+--------------+

-- adopt course Table queries the teacher's course number
SELECT no FROM course WHERE t_no IN (
    SELECT no FROM teacher WHERE department = 'Computer Department'
);
+-------+
| no    |
+-------+
| 3-245 |
| 3-105 |
+-------+

-- Query the results table based on the selected course number
SELECT * FROM score WHERE c_no IN (
    SELECT no FROM course WHERE t_no IN (
        SELECT no FROM teacher WHERE department = 'Computer Department'
    )
);
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-245 |     86 |
| 105  | 3-245 |     75 |
| 109  | 3-245 |     68 |
| 101  | 3-105 |     90 |
| 102  | 3-105 |     91 |
| 103  | 3-105 |     92 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
| 109  | 3-105 |     76 |
+------+-------+--------+

Use of UNION and NOTIN

Query teachers with different titles in computer and electronic engineering departments.

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

ANY means at least one - DESC (descending)

Query the score table for courses 3-105 with grades at least 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: accord with SQL Any condition in a statement.
-- That is, at 3-105 As long as one of the results is greater than from 3-245 Any line filtered out meets the criteria.
-- Finally, the results are queried in descending order.
SELECT * FROM score WHERE c_no = '3-105' AND degree > ANY(
    SELECT degree FROM score WHERE c_no = '3-245'
) ORDER BY degree DESC;
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
| 102  | 3-105 |     91 |
| 101  | 3-105 |     90 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
| 109  | 3-105 |     76 |
+------+-------+--------+

Represents ALL ALLs

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

-- You only need to make a slight change to the previous question.
-- ALL: accord with SQL All conditions in the statement.
-- That is, at 3-105 In each row, it is greater than from 3-245 All rows are filtered out to meet the criteria.
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 table data as conditional query

Query the score table for a course that has a lower average score than the course.

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

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

-- Will table b Act on tables a Query data in
-- score a (b): Declare the table as a (b)，
-- So it can be used a.c_no = b.c_no The query was executed as a condition.
SELECT * FROM score a WHERE degree < (
    (SELECT AVG(degree) FROM score b WHERE a.c_no = b.c_no)
);
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+

Subquery - 4

Query the name and department of all the teachers who have classes in the course table.

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

Conditional plus group filtering

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

-- View Student Table Information
SELECT * FROM student;
+-----+-----------+-----+------------+-------+
| no  | name      | sex | birthday   | class |
+-----+-----------+-----+------------+-------+
| 101 | Hua Zeng      | male  | 1977-09-01 | 95033 |
| 102 | Correct Ming      | male  | 1975-10-02 | 95031 |
| 103 | Wang Li      | female  | 1976-01-23 | 95033 |
| 104 | Li Jun      | male  | 1976-02-20 | 95033 |
| 105 | Wang Fang      | female  | 1975-02-10 | 95031 |
| 106 | Land force      | male  | 1974-06-03 | 95031 |
| 107 | Wang Nima    | male  | 1976-02-20 | 95033 |
| 108 | Zhang Quan Egg    | male  | 1975-02-10 | 95031 |
| 109 | Zhao Tieju    | male  | 1974-06-03 | 95031 |
| 110 | Zhang Fei      | male  | 1974-06-03 | 95038 |
+-----+-----------+-----+------------+-------+

-- Query only if the gender is male, then press class Grouping and Restricting class Line greater than 1.
SELECT class FROM student WHERE sex = 'male' GROUP BY class HAVING COUNT(*) > 1;
+-------+
| class |
+-------+
| 95033 |
| 95031 |
+-------+

NOTLIKE Fuzzy Query Reverse

Query the records of the students whose last name is Wang in the student table.

-- NOT: Reverse
-- LIKE: Fuzzy Query
mysql> SELECT * FROM student WHERE name NOT LIKE 'king%';
+-----+-----------+-----+------------+-------+
| no  | name      | sex | birthday   | class |
+-----+-----------+-----+------------+-------+
| 101 | Hua Zeng      | male  | 1977-09-01 | 95033 |
| 102 | Correct Ming      | male  | 1975-10-02 | 95031 |
| 104 | Li Jun      | male  | 1976-02-20 | 95033 |
| 106 | Land force      | male  | 1974-06-03 | 95031 |
| 108 | Zhang Quan Egg    | male  | 1975-02-10 | 95031 |
| 109 | Zhao Tieju    | male  | 1974-06-03 | 95031 |
| 110 | Zhang Fei      | male  | 1974-06-03 | 95038 |
+-----+-----------+-----+------------+-------+

YEAR and NOW Functions

Query each student's name and age in the student table.

-- Using functions YEAR(NOW()) Calculate the current year, minus the year of birth.
SELECT name, YEAR(NOW()) - YEAR(birthday) as age FROM student;
+-----------+------+
| name      | age  |
+-----------+------+
| Hua Zeng      |   42 |
| Correct Ming      |   44 |
| Wang Li      |   43 |
| Li Jun      |   43 |
| Wang Fang      |   44 |
| Land force      |   45 |
| Wang Nima    |   43 |
| Zhang Quan Egg    |   44 |
| Zhao Tieju    |   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 order of class and birthday from large to small.

SELECT * FROM student ORDER BY class DESC, birthday;
+-----+-----------+-----+------------+-------+
| no  | name      | sex | birthday   | class |
+-----+-----------+-----+------------+-------+
| 110 | Zhang Fei      | male  | 1974-06-03 | 95038 |
| 103 | Wang Li      | female  | 1976-01-23 | 95033 |
| 104 | Li Jun      | male  | 1976-02-20 | 95033 |
| 107 | Wang Nima    | male  | 1976-02-20 | 95033 |
| 101 | Hua Zeng      | male  | 1977-09-01 | 95033 |
| 106 | Land force      | male  | 1974-06-03 | 95031 |
| 109 | Zhao Tieju    | male  | 1974-06-03 | 95031 |
| 105 | Wang Fang      | female  | 1975-02-10 | 95031 |
| 108 | Zhang Quan Egg    | male  | 1975-02-10 | 95031 |
| 102 | Correct Ming      | male  | 1975-10-02 | 95031 |
+-----+-----------+-----+------------+-------+

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 | digital circuit     | 856  |
+-------+--------------+------+

MAX Functions and Subqueries

Query the score table of the highest score students.

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

-- Based on the above criteria, all the top scoring tables are selected.
-- This query may have multiple results, assuming degree Value meets criteria multiple times.
SELECT * FROM score WHERE degree = (SELECT MAX(degree) FROM score);
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
+------+-------+--------+

Subquery - 6

Query all classmates of the same gender as Li Jun.

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

-- Query by gender name and sex
SELECT name, sex FROM student WHERE sex = (
    SELECT sex FROM student WHERE name = 'Li Jun'
);
+-----------+-----+
| name      | sex |
+-----------+-----+
| Hua Zeng      | male  |
| Correct Ming      | male  |
| Li Jun      | male  |
| Land force      | male  |
| Wang Nima    | male  |
| Zhang Quan Egg    | male  |
| Zhao Tieju    | male  |
| Zhang Fei      | male  |
+-----------+-----+

Subquery - 7

Query and "Li Jun" same gender and classmate name.

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

Subquery - 8

Query the result sheets of all the "male" students taking the course "Introduction to Computer".

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

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

Query by Level

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

CREATE TABLE grade (
    low INT(3),
    upp INT(3),
    grade char(1)
);

INSERT INTO grade VALUES (90, 100, 'A');
INSERT INTO grade VALUES (80, 89, 'B');
INSERT INTO grade VALUES (70, 79, 'C');
INSERT INTO grade VALUES (60, 69, 'D');
INSERT INTO grade VALUES (0, 59, 'E');

SELECT * FROM grade;
+------+------+-------+
| low  | upp  | grade |
+------+------+-------+
|   90 |  100 | A     |
|   80 |   89 | B     |
|   70 |   79 | C     |
|   60 |   69 | D     |
|    0 |   59 | E     |
+------+------+-------+

Query s_for all students No, c_no and grade columns.

The idea is to use the BETWEEN query to judge whether the student's grade is between low and upp in the grade table.

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

join query

Prepare data for testing join 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, 'meal card'), (2, 'Construction Bank 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    |
|    5 | Postal Card    |
+------+-----------+

INSERT INTO person VALUES (1, 'Zhang San', 1), (2, 'Li Si', 3), (3, 'King Five', 6);
SELECT * FROM person;
+------+--------+--------+
| id   | name   | cardId |
+------+--------+--------+
|    1 | Zhang San   |      1 |
|    2 | Li Si   |      3 |
|    3 | King Five   |      6 |
+------+--------+--------+

Analyzing the two tables, it was found that the person table did not set a foreign key to the id corresponding to the cardId field in the card table. If set, rows with a cardId field value of 6 in person will not be interpolated because the cardId value is not in the card table.

Internal connection

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

-- INNER JOIN: Represents an inner join that splits two tables together.
-- on: Indicates that a condition is to be executed.
SELECT * FROM person INNER JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | Zhang San   |      1 |    1 | meal card      |
|    2 | Li Si   |      3 |    3 | Agricultural Bank Card    |
+------+--------+--------+------+-----------+

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

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

Left Outer Connection

Fully display the left table (person), and the right table is displayed if it meets the criteria, and NULL is supplemented if it does not.

-- LEFT JOIN Also called LEFT OUTER JOIN，Query results are the same in both ways.
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 | King Five   |      6 | NULL | NULL      |
+------+--------+--------+------+-----------+

Right Outer Link

Fully display the right table (card), the left table if it meets the criteria, and NULL if it does not.

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 | Construction Bank Card    |
| NULL | NULL   |   NULL |    4 | Business card    |
| NULL | NULL   |   NULL |    5 | Postal Card    |
+------+--------+--------+------+-----------+

External Link

Full display of all data for both tables.

-- MySQL External connections that do not support 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 Merge the two tables together.
SELECT * FROM person LEFT JOIN card on person.cardId = card.id
UNION
SELECT * FROM person RIGHT JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | Zhang San   |      1 |    1 | meal card      |
|    2 | Li Si   |      3 |    3 | Agricultural Bank Card    |
|    3 | King Five   |      6 | NULL | NULL      |
| NULL | NULL   |   NULL |    2 | Construction Bank Card    |
| NULL | NULL   |   NULL |    4 | Business card    |
| NULL | NULL   |   NULL |    5 | Postal Card    |
+------+--------+--------+------+-----------+

affair

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

For example, our bank transfers:

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

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

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

Therefore, when multiple related SQL statements are executed, the transaction may require them to execute successfully or fail at the same time.

How to control transactions - COMMIT / ROLLBACK

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

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

The effect of autocommit: When we execute an SQL statement, the effect is immediately apparent and cannot be rolled back.

What is rollback? For instance:

CREATE DATABASE bank;

USE bank;

CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    money INT
);

INSERT INTO user VALUES (1, 'a', 1000);

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

You can see that the data takes effect immediately after the insert statement is executed because transactions in MySQL automatically commit it it to the database. So what rollback means is to undo all SQL statements that have been executed and roll them back to the state they were in when the data was last submitted.

Rollback using ROLLBACK in MySQL:

-- Rollback to last submission
ROLLBACK;

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

Since all the executed SQL statements have been submitted, no rollback of the data occurs. So how can data be rolled back?

-- Turn off automatic submission
SET AUTOCOMMIT = 0;

-- Query Autosubmit Status
SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            0 |
+--------------+

After autocommit is closed, test data is rolled back:

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

-- Close AUTOCOMMIT The data changes are then 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 |
+----+------+-------+

-- Rollback is available because the data has not yet been actually committed
ROLLBACK;

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

How do you actually submit virtual data to the database? Use COMMIT:

INSERT INTO user VALUES (2, 'b', 1000);
-- Manually submit data (persistence),
-- The data is actually committed to the database and cannot be rolled back after execution.
COMMIT;

-- Test rollback after submission
ROLLBACK;

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

summary

Auto-submit
- View autocommit status: SELECT @@AUTOCOMMIT;
- Set autocommit status: SET AUTOCOMMIT = 0.
Manual Submission

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

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

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

-- Transfer accounts
UPDATE user set money = money - 100 WHERE name = 'a';

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

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

Assuming an unexpected transfer occurs, you can use ROLLBACK to roll back to the last submitted state:

-- Assume that the transfer is unexpected and 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 happened, that is, transactions provide us with an opportunity to repent. Assuming the data is not unexpected, you can manually submit the data to the data table: COMMIT.

Manually open transaction - BEGIN / START TRANSACTION

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

-- Use BEGIN perhaps START TRANSACTION Manually open a transaction
-- START TRANSACTION;
BEGIN;
UPDATE user set money = money - 100 WHERE name = 'a';
UPDATE user set money = money + 100 WHERE name = 'b';

-- Since a manually opened transaction does not turn on autocommit,
-- 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 |
+----+------+-------+

COMMIT is still used to commit data and rollback of this transaction cannot occur after commit.

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

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

-- Submit data
COMMIT;

-- Test rollback (invalid because table data has been submitted)
ROLLBACK;

ACID features and usage of transactions

Four characteristics of transactions:

A Atomicity: Transactions are the smallest unit and cannot be split again;
C Consistency: Requires that SQL statements in the same transaction succeed or fail simultaneously;
I isolation: There is isolation between transaction 1 and transaction 2;
D persistence: Once a transaction has ended (COMMIT), it can no longer be returned (ROLLBACK).

Transaction isolation

There are four types of transaction isolation (low to high performance):

READ UNCOMMITTED (read uncommitted)

If there are multiple transactions, uncommitted data from other transactions can be seen by any transaction.
READ COMMITTED (Read Submitted)

Only data that has been committed by other transactions can be read.
REPEATABLE READ (REPEATABLE READ)

If multiple connections open a transaction, data records cannot be shared between transactions, otherwise only committed records can be shared.
SERIALIZABLE (serialization)

All transactions are executed in a fixed order, one transaction being executed before the next transaction is written.

View the default isolation level for the current database:

-- MySQL 8.x, GLOBAL Represents the system level, not the session level.
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
SELECT @@TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| REPEATABLE-READ                | -- MySQL Default isolation level for repeatable reads.
+--------------------------------+

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

Modify isolation level:

-- Set system isolation level, LEVEL The isolation level to be set is indicated later (READ UNCOMMITTED). 
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- Query the system isolation level and find that it has been modified.
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| READ-UNCOMMITTED               |
+--------------------------------+

Dirty reading

Testing isolation of READ UNCOMMITTED (read uncommitted):

INSERT INTO user VALUES (3, 'Xiao Ming', 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 |
+----+-----------+-------+

-- Open a transaction operation data
-- Suppose Xiao Ming bought a pair of 800 yuan shoes in a Taobao shop:
START TRANSACTION;
UPDATE user SET money = money - 800 WHERE name = 'Xiao Ming';
UPDATE user SET money = money + 800 WHERE name = 'Taobao Store';

-- Then Taobao Store queries the results on the other side and finds that the money has been paid.
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 operated on a newly opened transaction, and the result of the operation can be seen by other transactions (Taobao Store on the other side), the query result of Taobao Store is correct and the Taobao Store confirms the account arrival. But at this point, what happens if Xiao Ming executes the ROLLBACK command on the transaction he is in?

-- Xiao Ming's Affairs
ROLLBACK;

-- At this point, no matter who the other party is, if you go to the query results, 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 dirty reading, where one transaction reads data that has not yet been committed by another transaction. This is not allowed in actual development.

Read Submitted

Set isolation level to READ COMMITTED:

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

This way, when new transactions are joined, they can only query transaction data that has already been committed. However, for current transactions, they still see uncommitted data, such as:

-- 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，But in the current transaction,
-- It still sees temporary changes in the data table, not actually committed data.
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | Xiao Ming      |   200 |
|  4 | Taobao Store    |  1800 |
+----+-----------+-------+


-- Suppose a new transaction is opened remotely and connected to the database.
$ mysql -u root -p12345612

-- At this point the remote connection can only query data that has already been submitted
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | Xiao Ming      |  1000 |
|  4 | Taobao Store    |  1000 |
+----+-----------+-------+

However, there is a problem with this, which is to assume that while a transaction is manipulating data, other transactions interfere with the data of that transaction. For example:

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

-- Looking for tables in small pieces money Before the average value, Xiao Wang did an operation:
START TRANSACTION;
INSERT INTO user VALUES (5, 'c', 100);
COMMIT;

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

-- Then when Xiao Zhang averages again, the calculation does not match:
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, the problem is that inconsistencies may occur when reading data from the same table. This is called unrepeatable reading (READ COMMITTED).

phantom read

Set the isolation level to REPEATABLE READ:

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

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

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

-- Xiaowang - Beijing
START TRANSACTION;

-- Xiao Zhang - Chengdu
COMMIT;

After the current transaction is opened, before it is committed, the query is not available and can be queried after it is committed. However, if other transactions are opened before committing, connections with currently operational transactions will not be queried on this transaction line. This is equivalent to opening up a separate thread.

No matter whether Xiao Zhang has executed COMMIT or not, on Xiao Wang's side, he will not query the transaction records of Xiao Zhang, 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 Wang has opened a new transaction (START TRANSACTION) before, so on his new transaction line, there is no connection with other transactions, that is, if other transactions are working on data at this time, it is not known.

However, the fact is that in the real data table, Zhang has already inserted a piece of data. But Xiao Wang did not know at this time and inserted the same data, what would happen?

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

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

Serialization

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

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

Or take Xiao Zhang and Xiao Wang for example:

-- Xiao Zhang - Chengdu
START TRANSACTION;

-- Xiaowang - Beijing
START TRANSACTION;

-- Query the table and prepare the operational data before opening the transaction.
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 |
+----+-----------+-------+

-- There was no King Flower No. 7, so I inserted a data:
INSERT INTO user VALUES (7, 'Flos Roxburghii', 1000);

What happens at this point? Since the isolation level is now SERIALIZABLE, serialization means that assuming that all transactions are placed in a serial queue, all transactions execute in a fixed order, one transaction is executed before the next one is written (meaning that only one transaction can be written to the queue at the same time).

According to this explanation, when Xiao Wang inserts data, he will wait until Xiao Zhang executes COMMIT to end his business or a wait time-out occurs.

The kind person who turned from a small broken station has been forgotten for a long time. Welcome to study together!!!

Posted by DotSPF on Mon, 29 Nov 2021 10:42:45 -0800

Programmer Group

MySQL Learning Notes

MySQL Learning Notes

Log on and exit MySQL server

Basic Grammar

Table building constraints

Primary Key Constraint

Unique Primary Key

Non-empty constraint

Default Constraints

Foreign Key Constraints

Three design paradigms for databases

1NF

2NF

3NF

Query Practice

Preparing data

1 to 10

Group calculation average

Grouping Conditions and Fuzzy Query

Multi-table Query - 1

Multi-table Query - 2

Three-table Association query

Average score for subqueries plus groups

Subquery - 1

Subquery - 2

YEAR Function and Query with IN Keyword

Multi-level nested subqueries

multi-table query

Subquery - 3

Use of UNION and NOTIN

ANY means at least one - DESC (descending)

Represents ALL ALLs

Copy table data as conditional query

Subquery - 4

Conditional plus group filtering

NOTLIKE Fuzzy Query Reverse

YEAR and NOW Functions

MAX and MIN functions

Multi-Segment Sorting

Subquery - 5

MAX Functions and Subqueries

Subquery - 6

Subquery - 7

Subquery - 8

Query by Level

join query

Internal connection

Left Outer Connection

Right Outer Link

External Link

affair

How to control transactions - COMMIT / ROLLBACK

Manually open transaction - BEGIN / START TRANSACTION

ACID features and usage of transactions

Transaction isolation

Dirty reading

Read Submitted

phantom read

Serialization

Hot Keywords