Ausführliche Erläuterung von Beispielen zur Klassifizierung von SQL-Abfrageanweisungen in MySQL
- 黄舟Original
- 2017-09-08 13:44:241620Durchsuche
Es gibt viele Arten von SQL-Abfrageanweisungen, die unten zusammengefasst sind. Erstellen Sie zunächst drei Tabellen für spätere Experimente
-- 学生表，记录学生信息
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. Einzeltabellenabfrage
Eine Abfrageanweisung, die nur eine Tabelle umfasst, wird beispielsweise als Einzeltabellenabfrageanweisung bezeichnet.
SELECT * FROM student； SELECT FROM student WHERE sage>=20;
Diese Anweisungen beziehen sich nur auf eine Tabelle, es handelt sich also um Abfrageanweisungen für eine einzelne Tabelle.
2. Multi-Table-Abfrage
entspricht einer Single-Label-Abfrage. Eine Abfrage mit mehreren Tabellen ist eine Multi-Tabellen-Abfrage, die in Join-Abfrage, verschachtelte Abfrage und abgeleitete Tabellenabfrage unterteilt ist und abfragen.
2.1 Verbindungsabfrage
Verbindungsabfrage ist die am häufigsten verwendete Abfrageanweisung in Datenbankabfragen, die sich auf Verbindungen über Verbindungsfelder und Verbindungsbedingungen bezieht Es können mehrere Tabellen abgefragt werden, und Verbindungsabfragen sind in Unterkategorien unterteilt: Equijoin, nicht äquivalenter Join, natürlicher Join, äußerer Join, innerer Join und Selbstjoin.
Äquivalente Verbindung und nicht äquivalente Verbindung
Wenn die Verbindungsbedingung das Gleichheitszeichen (=) ist, wird die Verbindung als äquivalente Verbindung bezeichnet Die Verbindungsbedingung ist entweder das Gleichheitszeichen oder die nicht äquivalente Verbindung.
-- 查询每个学生的选修课情况,连接条件是等于，连接字段是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 | +-----------+-------+------+------+-------+-----------+------+-------+
Der Prozess des Verbindungsvorgangs besteht darin, zuerst den ersten Datensatz in der Schülertabelle herauszunehmen und ihn dann gemäß den Verbindungsbedingungen und Verbindungsfeldern mit allen Datensätzen in der sc-Tabelle abzugleichen Verbinden Sie sie direkt. Bilden Sie ein Tupel in der Ergebnistabelle. Ordnen Sie dann den zweiten Datensatz der Student-Tabelle der sc-Tabelle zu, den dritten Datensatz ... und wiederholen Sie den Vorgang, bis der Abruf abgeschlossen ist. Dieser Matching-Algorithmus heißt Nested-Loop-Join-Algorithmus
Inner Join
Inner Join ist eine andere Art, gleichwertigen Join oder nicht äquivalenten Join zu schreiben. Er wird wie folgt geschrieben: INNER JOIN ON oder 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 Join (linker Outer Join, rechter Outer Join, vollständiger Outer Join)
Die Existenz eines Outer Joins kann den Inner Join ausgleichen Übereinstimmt nur Tupel, die die Bedingungen erfüllen Mit anderen Worten, innere Verknüpfungen können nur Tupel abfragen, die die Verknüpfungsbedingungen in den beiden Tabellen erfüllen, während äußere Verknüpfungen dies bis zu einem gewissen Grad ausgleichen können Defekt. Äußere Verknüpfungen werden unterteilt in linke äußere Verknüpfung (basierend auf der Tabelle links vom Schlüsselwort JOIN, und wenn kein passender Datensatz vorhanden ist, wird NULL gesetzt), rechte äußere Verknüpfung ( basierend auf der Tabelle rechts vom Schlüsselwort JOIN) (basierend auf), vollständiger äußerer Join (basierend auf den Tabellen links und rechts vom Schlüsselwort JOIN). MySQL unterstützt keine vollständigen Outer-Joins, aber dies kann mithilfe von Set-Abfragen erfolgen, d. h. UNION ALL-Operationen werden für die Abfrageergebnisse des linken Outer-Joins und die Abfrageergebnisse des rechten Outer-Joins ausgeführt.
-- 左外连接，以左边的表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 | +-----------+-------+------+------+-------+-----------+------+-------+
Natürliche Verbindung (ganz natürliche Verbindung, linke natürliche Verbindung, rechte natürliche Verbindung)
Das Entfernen derselben Attribute in der äquivalenten Verbindung ist eine natürliche Verbindung oder ganz natürliche Verbindung , Left Natural JoinMatches basierend auf der linken Tabelle, Right Natural JoinMatches basierend auf der rechten Tabelle
-- 查询每个学生的选修课情况，自然连接，去除相同的属性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
Wie der Name schon sagt, ist ein Self-Join eine Tabelle, die mit sich selbst verbunden ist.
-- '数据库'的先修课信息，连接条件是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 Verschachtelte Abfrage
Stellen Sie zunächst das Konzept eines Abfrageblocks vor, einer SQL-Anweisung in der Form SELECT...FROM...WHERE. .. Abfrageblock genannt. Wenn die SELECT-Klausel oder WHERE-Klausel eines Abfrageblocks in der Abfrageanweisung eines anderen Abfrageblocks verschachtelt ist, wird dies als verschachtelte Abfrage bezeichnet. Die äußerste Abfrage wird äußere Abfrage oder übergeordnete Abfrage genannt, und die innerste Abfrage wird innere Abfrage oder Unterabfrage genannt. Wenn eine Unterabfrage die Daten (Tabellen, Felder) der übergeordneten Abfrage verwendet, wird sie als korrelierte Unterabfrage bezeichnet. Wenn sie jedoch nicht verwendet wird, wird sie als irrelevante Unterabfrage bezeichnet. Verschachtelte Abfragen werden normalerweise in Verbindung mit IN, ALL, ANY und EXISTS verwendet.
-- 查询与刘晨在同一个系中的学生（先查出刘晨所在系，再查该系中的学生）
-- 内层查询可以独立运行没有依赖于外层，所以是不相关子查询
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 Abgeleitete Tabellenabfrage
Persönlich denke ich, dass es sich auch um eine Art verschachtelte Abfrage handelt, aber sie ist weit verbreitet, deshalb habe ich sie vorgeschlagen. Wenn der Abfrageblock nach der FROM-Klausel erscheint, wird er als abgeleitete Tabellenabfrage bezeichnet.
-- 查询所有选修了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 Mengenabfrage
Abfrageoperationen mit UNION, UNION ALL, INTERSECT und EXCEPT werden als Mengenabfragen bezeichnet. Unter diesen führen UNION und UNION ALL eine Vereinigung durch, UNION entfernt jedoch doppelte Datensätze. Schließlich unterstützt MySQL INTERSECT und EXCEPT nicht.
--查询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 | +-----------+-------+------+------+-------+
Zusammenfassung
Das obige ist der detaillierte Inhalt vonAusführliche Erläuterung von Beispielen zur Klassifizierung von SQL-Abfrageanweisungen in MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!