Classification of SQL Query Statements

There are many kinds of SQL query statements, which are summarized below. Firstly, three tables are constructed for later experiments.

-- Student table, record student information
    CREATE TABLE student(
    sno VARCHAR(10),
    sname VARCHAR(10),
    ssex ENUM('male','female'),
    sage INT,
    sdept VARCHAR(10),
    PRIMARY KEY(sno)
);

+-----------+-------+------+------+-------+
| sno       | sname | ssex | sage | sdept |
+-----------+-------+------+------+-------+
| 201215121 | Li Yong  | male   |   20 | CS    |
| 201215122 | Liu Chen  | female   |   19 | CS    |
| 201215123 | Wang Min  | female   |   18 | MA    |
| 201215125 | Zhang Li  | male   |   19 | IS    |
+-----------+-------+------+------+-------+

-- Course schedule, record course information, cpno Refers to the current record of advance courses. cno
CREATE TABLE course(
    cno INT AUTO_INCREMENT,
    cname VARCHAR(10),
    cpno INT,
    ccredit INT NOT NULL,
    PRIMARY KEY(cno),
    FOREIGN KEY(cpno) REFERENCES course(cno)
);

+-----------+-------+------+------+-------+
| sno       | sname | ssex | sage | sdept |
+-----------+-------+------+------+-------+
| 201215121 | Li Yong  | male   |   20 | CS    |
| 201215122 | Liu Chen  | female   |   19 | CS    |
| 201215123 | Wang Min  | female   |   18 | MA    |
| 201215125 | Zhang Li  | male   |   19 | IS    |
+-----------+-------+------+------+-------+

-- Course Selection Record Table, Recording Course Selection Information
CREATE TABLE sc (
    sno VARCHAR(10),
    cno INT,
    grade INT
);

+-----------+------+-------+
| sno       | cno  | grade |
+-----------+------+-------+
| 201215121 |    1 |    92 |
| 201215121 |    2 |    85 |
| 201215121 |    3 |    88 |
| 201215122 |    1 |    90 |
| 201215122 |    2 |    80 |
+-----------+------+-------+

1. Single Table Query

A query statement involving only one table is called a single table query statement. Take a chestnut for example.

SELECT * FROM student；

SELECT FROM student WHERE sage>=20;

These statements involve only one table, so they are single-table queries.

2. Multi-table query

Corresponding to single-label queries, queries involving multiple tables are multi-table queries, which are divided into join queries, nested queries, derived table queries and set queries.

2.1 Connection Query

Connection query is one of the most commonly used query statements in database query. It refers to joining multiple tables through connection fields and specified connection conditions to query. It is also divided into sub-categories: equivalence join, non-equivalence join, natural join, external join, internal join and self-join.

Equivalent connection and non-equivalent connection

When the connection condition is equal to sign (=), the connection is called equivalent connection. On the contrary, when the connection condition is either equal sign or non-equivalent connection.

-- Inquire about each student's elective courses,The connection condition is equal to, and the connection field is sno
SELECT * FROM student,sc WHERE student.sno = sc.sno;

+-----------+-------+------+------+-------+-----------+------+-------+
| sno       | sname | ssex | sage | sdept | sno       | cno  | grade |
+-----------+-------+------+------+-------+-----------+------+-------+
| 201215121 | Li Yong  | male   |   20 | CS    | 201215121 |    1 |    92 |
| 201215121 | Li Yong  | male   |   20 | CS    | 201215121 |    2 |    85 |
| 201215121 | Li Yong  | male   |   20 | CS    | 201215121 |    3 |    88 |
| 201215122 | Liu Chen  | female   |   19 | CS    | 201215122 |    1 |    90 |
| 201215122 | Liu Chen  | female   |   19 | CS    | 201215122 |    2 |    80 |
+-----------+-------+------+------+-------+-----------+------+-------+

The process of this join operation is to first take out the first record in the student table, and then match all the records in the sc table according to the join conditions and the join fields to form a tuple in the result table. Then match the second record of the student table with the sc table, and the third record... and repeat until it is finished. This matching algorithm is called nested loop join algorithm.

Internal connection

Internal connection is another way of writing equivalent connection or non-equivalent connection. There are two ways of writing: INNER JOIN ON or CORSS JOIN USING.

-- Use internal links to query each student's elective courses,The query results are the same as using the above equivalent join.
-- stay MySQL In Chinese, INNER Can be omitted,CROSS JOIN= INNER JOIN = INNER
SELECT * FROM student INNER JOIN sc ON student.sno=sc.sno;
SELECT * FROM student JOIN sc ON student.sno=sc.sno;
SELECT * FROM student CROSS JOIN sc USING(sno);
+-----------+-------+------+------+-------+-----------+------+-------+
| sno       | sname | ssex | sage | sdept | sno       | cno  | grade |
+-----------+-------+------+------+-------+-----------+------+-------+
| 201215121 | Li Yong  | male   |   20 | CS    | 201215121 |    1 |    92 |
| 201215121 | Li Yong  | male   |   20 | CS    | 201215121 |    2 |    85 |
| 201215121 | Li Yong  | male   |   20 | CS    | 201215121 |    3 |    88 |
| 201215122 | Liu Chen  | female   |   19 | CS    | 201215122 |    1 |    90 |
| 201215122 | Liu Chen  | female   |   19 | CS    | 201215122 |    2 |    80 |
+-----------+-------+------+------+-------+-----------+------+-------+

External Connection (Left External Connection, Right External Connection, Total External Connection)

External join can make up for the defect that internal join only matches eligible tuples, that is to say, internal join can only query eligible tuples in two tables, while external join can make up for this defect to some extent. External join is divided into left outer join (based on table on left of JOIN keyword, NULL for no matching record), right outer join (based on table on right of JOIN keyword), and total outer join (based on table on left and right of JOIN keyword). MySQL does not support outbound connections, but it can be done by collecting queries, i.e. UNION ALL operations on the query results of left outbound connections and right outbound connections.

-- Connect the left outer to the left table student As a benchmark.
//In MySQL, the OUTER keyword can be omitted in MySQL LEFT JOIN=LEFT OUTER JOIN，RIGHT JOIN=RIGHT OUTER JOIN
SELECT * FROM student LEFT OUTER JOIN sc ON student.sno=sc.sno;
SELECT * FROM student LEFT JOIN sc ON student.sno=sc.sno;
+-----------+-------+------+------+-------+-----------+------+-------+
| sno       | sname | ssex | sage | sdept | sno       | cno  | grade |
+-----------+-------+------+------+-------+-----------+------+-------+
| 201215121 | Li Yong  | male   |   20 | CS    | 201215121 |    1 |    92 |
| 201215121 | Li Yong  | male   |   20 | CS    | 201215121 |    2 |    85 |
| 201215121 | Li Yong  | male   |   20 | CS    | 201215121 |    3 |    88 |
| 201215122 | Liu Chen  | female   |   19 | CS    | 201215122 |    1 |    90 |
| 201215122 | Liu Chen  | female   |   19 | CS    | 201215122 |    2 |    80 |
| 201215123 | Wang Min  | female   |   18 | MA    | NULL      | NULL |  NULL |
| 201215125 | Zhang Li  | male   |   19 | IS    | NULL      | NULL |  NULL |
+-----------+-------+------+------+-------+-----------+------+-------+

-- Right Outer Connection, Note sc and student Change of position
SELECT * FROM sc RIGHT OUTER JOIN student ON student.sno=sc.sno;
+-----------+------+-------+-----------+-------+------+------+-------+
| sno       | cno  | grade | sno       | sname | ssex | sage | sdept |
+-----------+------+-------+-----------+-------+------+------+-------+
| 201215121 |    1 |    92 | 201215121 | Li Yong  | male   |   20 | CS    |
| 201215121 |    2 |    85 | 201215121 | Li Yong  | male   |   20 | CS    |
| 201215121 |    3 |    88 | 201215121 | Li Yong  | male   |   20 | CS    |
| 201215122 |    1 |    90 | 201215122 | Liu Chen  | female   |   19 | CS    |
| 201215122 |    2 |    80 | 201215122 | Liu Chen  | female   |   19 | CS    |
| NULL      | NULL |  NULL | 201215123 | Wang Min  | female   |   18 | MA    |
| NULL      | NULL |  NULL | 201215125 | Zhang Li  | male   |   19 | IS    |
+-----------+------+-------+-----------+-------+------+------+-------+
-- External connection
SELECT * FROM sc FULL JOIN student ON student.sno=sc.sno;
ERROR 1054 (42S22): Unknown column 'sc.sno' in 'on clause'

-- Note that UNION ALL，Not UNION，UNION It has a weight removal effect.
SELECT * FROM student LEFT OUTER JOIN sc ON student.sno=sc.sno
UNION ALL
SELECT * FROM student RIGHT OUTER JOIN sc ON student.sno=sc.sno;
+-----------+-------+------+------+-------+-----------+------+-------+
| sno       | sname | ssex | sage | sdept | sno       | cno  | grade |
+-----------+-------+------+------+-------+-----------+------+-------+
| 201215121 | Li Yong  | male   |   20 | CS    | 201215121 |    1 |    92 |
| 201215121 | Li Yong  | male   |   20 | CS    | 201215121 |    2 |    85 |
| 201215121 | Li Yong  | male   |   20 | CS    | 201215121 |    3 |    88 |
| 201215122 | Liu Chen  | female   |   19 | CS    | 201215122 |    1 |    90 |
| 201215122 | Liu Chen  | female   |   19 | CS    | 201215122 |    2 |    80 |
| 201215123 | Wang Min  | female   |   18 | MA    | NULL      | NULL |  NULL |
| 201215125 | Zhang Li  | male   |   19 | IS    | NULL      | NULL |  NULL |
| 201215121 | Li Yong  | male   |   20 | CS    | 201215121 |    1 |    92 |
| 201215121 | Li Yong  | male   |   20 | CS    | 201215121 |    2 |    85 |
| 201215121 | Li Yong  | male   |   20 | CS    | 201215121 |    3 |    88 |
| 201215122 | Liu Chen  | female   |   19 | CS    | 201215122 |    1 |    90 |
| 201215122 | Liu Chen  | female   |   19 | CS    | 201215122 |    2 |    80 |
+-----------+-------+------+------+-------+-----------+------+-------+

Natural Connection (All Natural Connection, Left Natural Connection, Right Natural Connection)

Removing the same attributes in equivalence join is called natural join or all natural join. The left natural join matches with the left table as the benchmark, and the right natural join matches with the right table as the benchmark.

-- Query each student's elective course, link naturally, remove the same attributes sno
SELECT student.sno,student.sname,student.ssex,student.sage,student.sdept,sc.cno,sc.grade
FROM student,sc WHERE student.sno = sc.sno;
SELECT * FROM student NATURAL JOIN sc;
+-----------+-------+------+------+-------+------+-------+
| sno       | sname | ssex | sage | sdept | cno  | grade |
+-----------+-------+------+------+-------+------+-------+
| 201215121 | Li Yong  | male   |   20 | CS    |    1 |    92 |
| 201215121 | Li Yong  | male   |   20 | CS    |    2 |    85 |
| 201215121 | Li Yong  | male   |   20 | CS    |    3 |    88 |
| 201215122 | Liu Chen  | female   |   19 | CS    |    1 |    90 |
| 201215122 | Liu Chen  | female   |   19 | CS    |    2 |    80 |
+-----------+-------+------+------+-------+------+-------+

SELECT * FROM student NATURAL LEFT JOIN sc;
+-----------+-------+------+------+-------+------+-------+
| sno       | sname | ssex | sage | sdept | cno  | grade |
+-----------+-------+------+------+-------+------+-------+
| 201215121 | Li Yong  | male   |   20 | CS    |    1 |    92 |
| 201215121 | Li Yong  | male   |   20 | CS    |    2 |    85 |
| 201215121 | Li Yong  | male   |   20 | CS    |    3 |    88 |
| 201215122 | Liu Chen  | female   |   19 | CS    |    1 |    90 |
| 201215122 | Liu Chen  | female   |   19 | CS    |    2 |    80 |
| 201215123 | Wang Min  | female   |   18 | MA    | NULL |  NULL |
| 201215125 | Zhang Li  | male   |   19 | IS    | NULL |  NULL |
+-----------+-------+------+------+-------+------+-------+

-- sc and student The position has been changed, but it has been changed. student As a benchmark, I think Wang Min and Zhang Li did not choose courses, so there are NULL field
SELECT * FROM sc NATURAL RIGHT JOIN student;
+-----------+-------+------+------+-------+------+-------+
| sno       | sname | ssex | sage | sdept | cno  | grade |
+-----------+-------+------+------+-------+------+-------+
| 201215121 | Li Yong  | male   |   20 | CS    |    1 |    92 |
| 201215121 | Li Yong  | male   |   20 | CS    |    2 |    85 |
| 201215121 | Li Yong  | male   |   20 | CS    |    3 |    88 |
| 201215122 | Liu Chen  | female   |   19 | CS    |    1 |    90 |
| 201215122 | Liu Chen  | female   |   19 | CS    |    2 |    80 |
| 201215123 | Wang Min  | female   |   18 | MA    | NULL |  NULL |
| 201215125 | Zhang Li  | male   |   19 | IS    | NULL |  NULL |
+-----------+-------+------+------+-------+------+-------+

Self-connection

As the name implies, self-connection is a table, and self-connection.

-- 'data base'Pre-course information, link condition is course1.cno = course2.cpno
SELECT * FROM course AS course1,course AS course2
WHERE course1.cno = course2.cpno
AND course1.cno = 4
+-----+--------+------+---------+-----+--------+------+---------+
| cno | cname  | cpno | ccredit | cno | cname  | cpno | ccredit |
+-----+--------+------+---------+-----+--------+------+---------+
|   4 | data base |    2 |       4 |   7 | PASCAL |    4 |       4 |
+-----+--------+------+---------+-----+--------+------+---------+

2.2 Nested Query

Firstly, the concept of a query block is introduced. A SQL statement in the form of SELECT, FROM, WHERE is called a query block. Nested queries are called when a query block has a SELECT clause or a WHERE clause nested with another query block. The outermost query is called outer query or parent query, and the inmost query is called inner query or sub query. A child query that uses the data (tables, fields) of the parent query is called a related child query, whereas an unrelated child query is called an unrelated child query if it is not used. Usually nested queries are used in conjunction with IN, ALL, ANY and EXISTS.

-- Query the students in the same department as Liu Chen (first find out the Department where Liu Chen is, then check the students in the department)
-- Inner queries can run independently and do not depend on outer queries, so they are irrelevant sub-queries.
SELECT * FROM student WHERE sdept IN (
    SELECT sdept FROM student WHERE sname='Liu Chen'
)
+-----------+-------+------+------+-------+
| sno       | sname | ssex | sage | sdept |
+-----------+-------+------+------+-------+
| 201215121 | Li Yong  | male   |   20 | CS    |
| 201215122 | Liu Chen  | female   |   19 | CS    |
+-----------+-------+------+------+-------+

-- Inquire about elective courses'information system'Student Information (Find out the Course Number of Information System first) cno，Find out all the information of course selection, and then find out the information of students.
-- Similarly, irrelevant sub-queries
SELECT * FROM student WHERE sno IN (
    SELECT sno FROM sc WHERE cno IN (
        SELECT cno FROM course WHERE cname='information system'
    ) 
)
+-----------+-------+------+------+-------+
| sno       | sname | ssex | sage | sdept |
+-----------+-------+------+------+-------+
| 201215121 | Li Yong  | male   |   20 | CS    |
+-----------+-------+------+------+-------+

-- Find out the information about each student's choices that exceed the average grade of their elective courses.(Find out the average score first, and then find out the course information.)
-- Internal queries cannot run independently, so they are related sub-queries
SELECT * FROM sc AS x WHERE grade >= (
    SELECT AVG(grade) FROM sc AS y WHERE x.sno AND y.sno
)
+-----------+------+-------+
| sno       | cno  | grade |
+-----------+------+-------+
| 201215121 |    1 |    92 |
| 201215121 |    3 |    88 |
| 201215122 |    1 |    90 |
+-----------+------+-------+

2.3 Derived Table Query

Personally, I think it is also a kind of nested query, but it is widely used, so I put forward it. When a query block appears after the FROM clause, it is called a derived table query.

-- Query all the electives cno=1 Student Information for the Course of ____________
SELECT * FROM student,(
    SELECT sno FROM SC WHERE cno=1
) AS tempSC
WHERE student.sno = tempSC.sno
+-----------+-------+------+------+-------+-----------+
| sno       | sname | ssex | sage | sdept | sno       |
+-----------+-------+------+------+-------+-----------+
| 201215121 | Li Yong  | male   |   20 | CS    | 201215121 |
| 201215122 | Liu Chen  | female   |   19 | CS    | 201215122 |
+-----------+-------+------+------+-------+-----------+

2.4 Set Query

Query operations involving UNION, UNION ALL, INTERSECT and EXCEPT are called set queries. Among them, UNION and UNION ALL will do the union, but UNION will remove duplicate records. Finally, MySQL does not support INTERSECT and EXCEPT.

--query CS Department and students under 19 years of age( CS Students of the Department and those younger than 19 do the same thing.
SELECT * FROM student WHERE sdept='CS'
UNION ALL
SELECT * FROM student WHERE sage<=19
+-----------+-------+------+------+-------+
| sno       | sname | ssex | sage | sdept |
+-----------+-------+------+------+-------+
| 201215121 | Li Yong  | male   |   20 | CS    |
| 201215122 | Liu Chen  | female   |   19 | CS    |
| 201215122 | Liu Chen  | female   |   19 | CS    |
| 201215123 | Wang Min  | female   |   18 | MA    |
| 201215125 | Zhang Li  | male   |   19 | IS    |
+-----------+-------+------+------+-------+

-- UNION Duplicate removal
SELECT * FROM student WHERE sdept='CS'
UNION
SELECT * FROM student WHERE sage<=19
+-----------+-------+------+------+-------+
| sno       | sname | ssex | sage | sdept |
+-----------+-------+------+------+-------+
| 201215121 | Li Yong  | male   |   20 | CS    |
| 201215122 | Liu Chen  | female   |   19 | CS    |
| 201215123 | Wang Min  | female   |   18 | MA    |
| 201215125 | Zhang Li  | male   |   19 | IS    |
+-----------+-------+------+------+-------+

-- The computer department is not older than 19 years old. The students of the computer department intersect with those of the computer department who are not older than 19 years old. MySQL I won't support it INTERSECT operation
SELECT * FROM student WHERE sdept='cs'
INTERSECT
SELECT * FROM student WHERE sage<=19
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'INTERSECT
SELECT * FROM student WHERE sage<=19' at line 2

-- Replace with internal connection
SELECT a.* FROM student AS a INNER JOIN student AS b ON a.sno=b.sno
WHERE a.sdept='CS' AND b.sage<=19
+-----------+-------+------+------+-------+
| sno       | sname | ssex | sage | sdept |
+-----------+-------+------+------+-------+
| 201215122 | Liu Chen  | female   |   19 | CS    |
+-----------+-------+------+------+-------+


-- To inquire about the students aged over 19 in the computer department is to inquire about the difference between the students in the computer department and those under 19. MySQL I won't support it EXCEPT control
SELECT * FROM student WHERE sdept='CS' 
EXCEPT
SELECT * FROM student WHERE sage<=19
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'EXCEPT
SELECT * FROM student WHERE sage<=19' at line 2

-- Substitute external connection or ordinary connection
SELECT a.* FROM student AS a LEFT JOIN student AS b ON a.sno=b.sno
WHERE a.sdept='CS' 
AND b.sage>19
AND b.sno IS NOT NULL

SELECT * FROM student WHERE sdept='CS' AND sage>19;

+-----------+-------+------+------+-------+
| sno       | sname | ssex | sage | sdept |
+-----------+-------+------+------+-------+
| 201215121 | Li Yong  | male   |   20 | CS    |
+-----------+-------+------+------+-------+