Detailed explanation of examples of classification of SQL query statements in mysql
- 黄舟Original
- 2017-09-08 13:44:241661browse
There are many kinds of SQL query statements, summarized below. First, build three tables for later experiments
-- 学生表，记录学生信息
CREATE TABLE student(
sno VARCHAR(10),
sname VARCHAR(10),
ssex ENUM('男','女'),
sage INT,
sdept VARCHAR(10),
PRIMARY KEY(sno)
);
+-----------+-------+------+------+-------+
| sno | sname | ssex | sage | sdept |
+-----------+-------+------+------+-------+
| 201215121 | 李勇 | 男 | 20 | CS |
| 201215122 | 刘晨 | 女 | 19 | CS |
| 201215123 | 王敏 | 女 | 18 | MA |
| 201215125 | 张立 | 男 | 19 | IS |
+-----------+-------+------+------+-------+
-- 课程表，记录课程信息，cpno是指当前记录的先行课程的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 | 李勇 | 男 | 20 | CS |
| 201215122 | 刘晨 | 女 | 19 | CS |
| 201215123 | 王敏 | 女 | 18 | MA |
| 201215125 | 张立 | 男 | 19 | IS |
+-----------+-------+------+------+-------+
-- 选课记录表，记录选课信息
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 that only involves one table is called a single-table query statement, as an example.
SELECT * FROM student； SELECT FROM student WHERE sage>=20;
These statements only involve one table, so they are single-table query statements.
2. Multi-table query
Corresponds to single-standard query. A query involving multiple tables is a multi-table query, which is divided into join query, nested query, derived table query and set. Inquire.
2.1 Connection query
Connection query is the most commonly used query statement in database queries. It refers to the connection through connection fields and connection conditions Multiple tables can be queried, and connection queries are divided into subcategories: equijoin, non-equivalent join, natural join, outer join, inner join, and self-join.
Equivalent connection and non-equivalent connection
When the connection condition is the equal sign (=), the connection is called an equivalence connection. On the contrary, when the connection condition is the equal sign (=) Either the equal sign or the non-equivalent connection.
-- 查询每个学生的选修课情况,连接条件是等于，连接字段是sno SELECT * FROM student,sc WHERE student.sno = sc.sno; +-----------+-------+------+------+-------+-----------+------+-------+ | sno | sname | ssex | sage | sdept | sno | cno | grade | +-----------+-------+------+------+-------+-----------+------+-------+ | 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 1 | 92 | | 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 2 | 85 | | 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 3 | 88 | | 201215122 | 刘晨 | 女 | 19 | CS | 201215122 | 1 | 90 | | 201215122 | 刘晨 | 女 | 19 | CS | 201215122 | 2 | 80 | +-----------+-------+------+------+-------+-----------+------+-------+
The process of the connection operation is to first take out the first record in the student table, and then match it with all the records in the sc table according to the connection conditions and connection fields, and then connect them directly to form the result A tuple in the table. Then match the second record of the student table with the sc table, the third record..., and repeat until the fetching is completed. This matching algorithm is called Nested Loop Join Algorithm
Inner Join
Inner join is another way of writing equal join or non-equivalent join. The writing method is: There are two types of INNER JOIN ON or CORSS JOIN USING
-- 使用内连接查询每个学生的选修课情况,查询结果和使用上面的等值连接一样。 -- 在MySQL中，INNER可省略,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 | 李勇 | 男 | 20 | CS | 201215121 | 1 | 92 | | 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 2 | 85 | | 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 3 | 88 | | 201215122 | 刘晨 | 女 | 19 | CS | 201215122 | 1 | 90 | | 201215122 | 刘晨 | 女 | 19 | CS | 201215122 | 2 | 80 | +-----------+-------+------+------+-------+-----------+------+-------+
Outer joins (left outer join, right outer join, full outer join)
The existence of outer joins can make up for inner joinsOnly match tuples that meet the conditionsThe defect, that is to say, the inner join can only query the tuples that meet the join conditions in the two tables, and the outer join can make up for this to some extent. defect. Outer joins are divided into Left outer join (based on the table on the left side of the JOIN keyword, and if there is no matching record, NULL is set), Right outer join (based on the table on the right side of the JOIN keyword) (based on the tables on the left and right sides of the JOIN keyword), Full outer join (based on the tables on the left and right sides of the JOIN keyword). MySQL does not support full outer joins, but it can be done using set queries, that is, UNION ALL operations are performed on the query results of the left outer join and the query results of the right outer join.
-- 左外连接，以左边的表student为基准。 在MySQL中，OUTER关键字在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 | 李勇 | 男 | 20 | CS | 201215121 | 1 | 92 | | 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 2 | 85 | | 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 3 | 88 | | 201215122 | 刘晨 | 女 | 19 | CS | 201215122 | 1 | 90 | | 201215122 | 刘晨 | 女 | 19 | CS | 201215122 | 2 | 80 | | 201215123 | 王敏 | 女 | 18 | MA | NULL | NULL | NULL | | 201215125 | 张立 | 男 | 19 | IS | NULL | NULL | NULL | +-----------+-------+------+------+-------+-----------+------+-------+ -- 右外连接，注意sc和student换了位置 SELECT * FROM sc RIGHT OUTER JOIN student ON student.sno=sc.sno; +-----------+------+-------+-----------+-------+------+------+-------+ | sno | cno | grade | sno | sname | ssex | sage | sdept | +-----------+------+-------+-----------+-------+------+------+-------+ | 201215121 | 1 | 92 | 201215121 | 李勇 | 男 | 20 | CS | | 201215121 | 2 | 85 | 201215121 | 李勇 | 男 | 20 | CS | | 201215121 | 3 | 88 | 201215121 | 李勇 | 男 | 20 | CS | | 201215122 | 1 | 90 | 201215122 | 刘晨 | 女 | 19 | CS | | 201215122 | 2 | 80 | 201215122 | 刘晨 | 女 | 19 | CS | | NULL | NULL | NULL | 201215123 | 王敏 | 女 | 18 | MA | | NULL | NULL | NULL | 201215125 | 张立 | 男 | 19 | IS | +-----------+------+-------+-----------+-------+------+------+-------+ -- 全外连接 SELECT * FROM sc FULL JOIN student ON student.sno=sc.sno; ERROR 1054 (42S22): Unknown column 'sc.sno' in 'on clause' -- 注意是UNION ALL，而非UNION，UNION有个去重效果 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 | 李勇 | 男 | 20 | CS | 201215121 | 1 | 92 | | 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 2 | 85 | | 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 3 | 88 | | 201215122 | 刘晨 | 女 | 19 | CS | 201215122 | 1 | 90 | | 201215122 | 刘晨 | 女 | 19 | CS | 201215122 | 2 | 80 | | 201215123 | 王敏 | 女 | 18 | MA | NULL | NULL | NULL | | 201215125 | 张立 | 男 | 19 | IS | NULL | NULL | NULL | | 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 1 | 92 | | 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 2 | 85 | | 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 3 | 88 | | 201215122 | 刘晨 | 女 | 19 | CS | 201215122 | 1 | 90 | | 201215122 | 刘晨 | 女 | 19 | CS | 201215122 | 2 | 80 | +-----------+-------+------+------+-------+-----------+------+-------+
Natural connection (all natural connection, left natural connection, right natural connection)
Removing the same attributes in the equivalent connection is a natural connection or all natural connection, Left natural joinMatching based on the left table, Right natural joinMatching based on the right table
-- 查询每个学生的选修课情况，自然连接，去除相同的属性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 | 李勇 | 男 | 20 | CS | 1 | 92 | | 201215121 | 李勇 | 男 | 20 | CS | 2 | 85 | | 201215121 | 李勇 | 男 | 20 | CS | 3 | 88 | | 201215122 | 刘晨 | 女 | 19 | CS | 1 | 90 | | 201215122 | 刘晨 | 女 | 19 | CS | 2 | 80 | +-----------+-------+------+------+-------+------+-------+ SELECT * FROM student NATURAL LEFT JOIN sc; +-----------+-------+------+------+-------+------+-------+ | sno | sname | ssex | sage | sdept | cno | grade | +-----------+-------+------+------+-------+------+-------+ | 201215121 | 李勇 | 男 | 20 | CS | 1 | 92 | | 201215121 | 李勇 | 男 | 20 | CS | 2 | 85 | | 201215121 | 李勇 | 男 | 20 | CS | 3 | 88 | | 201215122 | 刘晨 | 女 | 19 | CS | 1 | 90 | | 201215122 | 刘晨 | 女 | 19 | CS | 2 | 80 | | 201215123 | 王敏 | 女 | 18 | MA | NULL | NULL | | 201215125 | 张立 | 男 | 19 | IS | NULL | NULL | +-----------+-------+------+------+-------+------+-------+ -- sc和student位置交换了，仍已student为基准，以为王敏、张立没有选课，所以有NULL字段 SELECT * FROM sc NATURAL RIGHT JOIN student; +-----------+-------+------+------+-------+------+-------+ | sno | sname | ssex | sage | sdept | cno | grade | +-----------+-------+------+------+-------+------+-------+ | 201215121 | 李勇 | 男 | 20 | CS | 1 | 92 | | 201215121 | 李勇 | 男 | 20 | CS | 2 | 85 | | 201215121 | 李勇 | 男 | 20 | CS | 3 | 88 | | 201215122 | 刘晨 | 女 | 19 | CS | 1 | 90 | | 201215122 | 刘晨 | 女 | 19 | CS | 2 | 80 | | 201215123 | 王敏 | 女 | 18 | MA | NULL | NULL | | 201215125 | 张立 | 男 | 19 | IS | NULL | NULL | +-----------+-------+------+------+-------+------+-------+
Self-join
As the name suggests, self-connection is a table, connecting itself to itself.
-- '数据库'的先修课信息，连接条件是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 | 数据库 | 2 | 4 | 7 | PASCAL | 4 | 4 | +-----+--------+------+---------+-----+--------+------+---------+
2.2 Nested query
First introduce the concept of a query block, a SQL statement in the form of SELECT...FROM...WHERE... is called query block. When the SELECT clause or WHERE clause of a query block is nested in the query statement of another query block, it is called a nested query. The outermost query is called outer query or parent query, and the innermost query is called inner query or subquery. When a subquery uses the data (tables, fields) of the parent query, it is called a correlated subquery. On the contrary, if it is not used, it is called an irrelevant subquery. Nested queries are usually used in conjunction with IN, ALL, ANY, and EXISTS.
-- 查询与刘晨在同一个系中的学生（先查出刘晨所在系，再查该系中的学生）
-- 内层查询可以独立运行没有依赖于外层，所以是不相关子查询
SELECT * FROM student WHERE sdept IN (
SELECT sdept FROM student WHERE sname='刘晨'
)
+-----------+-------+------+------+-------+
| sno | sname | ssex | sage | sdept |
+-----------+-------+------+------+-------+
| 201215121 | 李勇 | 男 | 20 | CS |
| 201215122 | 刘晨 | 女 | 19 | CS |
+-----------+-------+------+------+-------+
-- 查询选修了‘信息系统’的学生信息（先查出信息系统的课程号cno，再查处所有选课信息，再查出学生信息）
-- 同样，也是不相关子查询
SELECT * FROM student WHERE sno IN (
SELECT sno FROM sc WHERE cno IN (
SELECT cno FROM course WHERE cname='信息系统'
)
)
+-----------+-------+------+------+-------+
| sno | sname | ssex | sage | sdept |
+-----------+-------+------+------+-------+
| 201215121 | 李勇 | 男 | 20 | CS |
+-----------+-------+------+------+-------+
-- 找出每个学生超过自己选修课平均成绩的选课信息(先查出平均成绩，再查出选课信息)
-- 内层查询无法独立运行，所以是相关子查询
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 think it is also a kind of nested query, but it is widely used, so I proposed it. When the query block appears after the FROM clause, it is called a derived table query.
-- 查询所有选修了cno=1的课程的学生信息
SELECT * FROM student,(
SELECT sno FROM SC WHERE cno=1
) AS tempSC
WHERE student.sno = tempSC.sno
+-----------+-------+------+------+-------+-----------+
| sno | sname | ssex | sage | sdept | sno |
+-----------+-------+------+------+-------+-----------+
| 201215121 | 李勇 | 男 | 20 | CS | 201215121 |
| 201215122 | 刘晨 | 女 | 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 perform union, but UNION will remove duplicate records. Finally, MySQL does not support INTERSECT and EXCEPT.
--查询CS系及年龄不大于19岁的学生（CS系的学生与年龄不大于19岁的学生做并集） SELECT * FROM student WHERE sdept='CS' UNION ALL SELECT * FROM student WHERE sage<=19 +-----------+-------+------+------+-------+ | sno | sname | ssex | sage | sdept | +-----------+-------+------+------+-------+ | 201215121 | 李勇 | 男 | 20 | CS | | 201215122 | 刘晨 | 女 | 19 | CS | | 201215122 | 刘晨 | 女 | 19 | CS | | 201215123 | 王敏 | 女 | 18 | MA | | 201215125 | 张立 | 男 | 19 | IS | +-----------+-------+------+------+-------+ -- UNION去重 SELECT * FROM student WHERE sdept='CS' UNION SELECT * FROM student WHERE sage<=19 +-----------+-------+------+------+-------+ | sno | sname | ssex | sage | sdept | +-----------+-------+------+------+-------+ | 201215121 | 李勇 | 男 | 20 | CS | | 201215122 | 刘晨 | 女 | 19 | CS | | 201215123 | 王敏 | 女 | 18 | MA | | 201215125 | 张立 | 男 | 19 | IS | +-----------+-------+------+------+-------+ -- 查询计算机系年龄不大于19岁的学，计算机系的学生与年龄不大于19岁的学生取交集，MySQL不支持INTERSECT操作 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 -- 用内连接代替 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 | 刘晨 | 女 | 19 | CS | +-----------+-------+------+------+-------+ -- 查询计算机系中年龄大于19岁的学生，就是查询计算机系的学生与年龄不大于19岁的学生的差集，MySQL不支持EXCEPT操纵 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 -- 用外连接或普通连接代替 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 | 李勇 | 男 | 20 | CS | +-----------+-------+------+------+-------+
Summary
The above is the detailed content of Detailed explanation of examples of classification of SQL query statements in mysql. For more information, please follow other related articles on the PHP Chinese website!