mysqlにおけるSQLクエリ文の分類例を詳しく解説

SQL クエリ ステートメントには多くの種類があります。以下にまとめます。まず、後の実験のために 3 つのテーブルを作成します

-- 学生表，记录学生信息
    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. 単一テーブル クエリ

例として、1 つのテーブルのみを含むクエリ ステートメントを単一テーブル クエリ ステートメントと呼びます。

SELECT * FROM student；
SELECT  FROM student WHERE sage>=20;

これらのステートメントには 1 つのテーブルのみが含まれるため、単一テーブルのクエリ ステートメントとなります。

2. マルチテーブル クエリ

は、単一標準クエリに相当します。複数のテーブルを含むクエリは、結合クエリ、ネストされたテーブル クエリ、およびセット クエリに分類されます。

2.1 接続クエリ

接続クエリは、データベース クエリで最もよく使用されるクエリ ステートメントであり、接続フィールドと接続条件を介して複数のテーブルを接続することによるクエリを指します。接続クエリは、値結合などのサブカテゴリに分かれています。 、非等価結合、自然結合、外部結合、内部結合、自己結合。

等価接続と非等価接続

接続条件が等号(=)の場合を等価接続、逆に等号でない場合を等価接続といいます。非等価な接続。

-- 查询每个学生的选修课情况,连接条件是等于，连接字段是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 |
+-----------+-------+------+------+-------+-----------+------+-------+

接続操作のプロセスは、最初にstudentテーブルの最初のレコードを取り出し、それを接続条件と接続フィールドに従ってscテーブルのすべてのレコードと照合し、それらをフォームに直接接続します。結果テーブルのタプル。次に、student テーブルの 2 番目のレコードを sc テーブル、3 番目のレコードと照合し、フェッチが完了するまで繰り返します。このマッチング アルゴリズムは、Nested Loop Join Algorithm

Inner Join

と呼ばれます。Inner Join は、等価結合または非等価結合を記述する別の方法です。INNER JOIN ON または 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 |
+-----------+-------+------+------+-------+-----------+------+-------+

の 2 つの記述方法があります。結合 (左外部結合、右外部結合、完全外部結合)

外部結合の存在により、内部結合の欠点を補うことができます条件を満たすタプルのマッチングのみ、つまり、内部結合でできることは、結合条件を満たすテーブル内の 2 つのタプルのみをクエリするため、外部結合によってこの欠点をある程度補うことができます。外部結合は、左外部結合 (JOIN キーワードの左側のテーブルに基づき、一致するレコードがない場合は NULL が設定される)、右外部結合 (右側のテーブルに基づく) に分けられます。 JOIN キーワードの左側)、完全外部結合 (JOIN キーワードの左側と右側のテーブルに基づく)。 MySQL は完全外部結合をサポートしていませんが、セット クエリを使用して実行できます。つまり、左外部結合のクエリ結果と右外部結合のクエリ結果に対して UNION ALL 操作が実行されます。

-- 左外连接，以左边的表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 |
+-----------+-------+------+------+-------+-----------+------+-------+

自然な接続 (すべて自然な接続、左の自然な接続、右の自然な接続)

同等の接続内の同じ属性を削除することは、自然な接続、または全自然な接続と呼ばれます、左の自然な接続左のテーブルを次のようにしますベースライン マッチング、正しい自然結合正しいテーブルに基づくマッチング

-- 查询每个学生的选修课情况，自然连接，去除相同的属性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 |
+-----------+-------+------+------+-------+------+-------+

自己結合

その名前が示すように、自己結合はそれ自体に接続されたテーブルです。

-- '数据库'的先修课信息，连接条件是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 ネストされたクエリ

まず、クエリ ブロックの概念を紹介します。SELECT...FROM...WHERE... の形式の SQL ステートメントはクエリ ブロックと呼ばれます。クエリ ブロックの SELECT 句または WHERE 句が、別のクエリ ブロックのクエリ ステートメント内にネストされている場合、ネストされたクエリと呼ばれます。最も外側のクエリは外部クエリまたは親クエリと呼ばれ、最も内側のクエリは内部クエリまたはサブクエリと呼ばれます。サブクエリが親クエリのデータ（テーブル、フィールド）を使用する場合、相関サブクエリと呼ばれます。逆に、使用されない場合、無関係サブクエリと呼ばれます。 ネストされたクエリは通常、IN、ALL、ANY、および 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 派生テーブルクエリ

個人的にはこれもネストクエリの一種だと思っていますが、よく使われているので提案してみました。クエリ ブロックが FROM 句の後に現れる場合、それは派生テーブル クエリと呼ばれます。

-- 查询所有选修了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 セットクエリ

UNION、UNION ALL、INTERSECT、EXCEPTを含むクエリ操作はセットクエリと呼ばれます。このうちUNIONとUNION ALLは結合を行いますが、UNIONは重複レコードを削除します。最後に、MySQL は INTERSECT と 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=&#39;CS&#39;
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=&#39;cs&#39;
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 &#39;INTERSECT
SELECT * FROM student WHERE sage<=19&#39; at line 2

-- 用内连接代替
SELECT a.* FROM student AS a INNER JOIN student AS b ON a.sno=b.sno
WHERE a.sdept=&#39;CS&#39; AND b.sage<=19
+-----------+-------+------+------+-------+
| sno       | sname | ssex | sage | sdept |
+-----------+-------+------+------+-------+
| 201215122 | 刘晨  | 女   |   19 | CS    |
+-----------+-------+------+------+-------+


-- 查询计算机系中年龄大于19岁的学生，就是查询计算机系的学生与年龄不大于19岁的学生的差集，MySQL不支持EXCEPT操纵
SELECT * FROM student WHERE sdept=&#39;CS&#39; 
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 &#39;EXCEPT
SELECT * FROM student WHERE sage<=19&#39; at line 2

-- 用外连接或普通连接代替
SELECT a.* FROM student AS a LEFT JOIN student AS b ON a.sno=b.sno
WHERE a.sdept=&#39;CS&#39; 
AND b.sage>19
AND b.sno IS NOT NULL

SELECT * FROM student WHERE sdept=&#39;CS&#39; AND sage>19;

+-----------+-------+------+------+-------+
| sno       | sname | ssex | sage | sdept |
+-----------+-------+------+------+-------+
| 201215121 | 李勇  | 男   |   20 | CS    |
+-----------+-------+------+------+-------+

概要

以上がmysqlにおけるSQLクエリ文の分類例を詳しく解説の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。