mysql의 SQL 쿼리문 분류 예에 대한 자세한 설명

아래에 요약된 것처럼 다양한 유형의 SQL 쿼리 문이 있습니다. 먼저 나중 실험을 위해 세 개의 테이블을 만듭니다

-- 学生表，记录学生信息
    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. 단일 테이블 쿼리

예를 들어 하나의 테이블만 포함하는 쿼리 문을 단일 테이블 쿼리 문이라고 합니다.

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

이 문은 하나의 테이블만 포함하므로 단일 테이블 쿼리 문입니다.

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

연결 작업의 과정은 먼저 학생 테이블의 첫 번째 레코드를 꺼낸 다음 연결 조건 및 연결 필드에 따라 sc 테이블의 모든 레코드와 일치시킨 다음 직접 연결하여 양식을 만드는 것입니다. 결과 테이블 튜플. 그런 다음 학생 테이블의 두 번째 레코드를 sc 테이블, 세 번째 레코드와 일치시키고 가져오기가 완료될 때까지 반복합니다. 이 일치 알고리즘을 Nested Loop Join Algorithm

Inner Join

Inner Join은 Equivalent Join 또는 Non-Equivalent 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 |
+-----------+-------+------+------+-------+-----------+------+-------+

Outer를 작성하는 방법이 있습니다. 조인 (왼쪽 외부 조인, 오른쪽 외부 조인, 완전 외부 조인)

외부 조인의 존재로 내부 조인의 단점을 보완할 수 있음조건을 만족하는 일치하는 튜플만 즉, 내부 조인이 가능함 조인 조건을 충족하는 테이블에서 두 개의 튜플만 쿼리하며 외부 조인은 이러한 단점을 어느 정도 보완할 수 있습니다. 외부 조인은 왼쪽 외부 조인(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 |
+-----------+-------+------+------+-------+------+-------+

Self-join

이름에서 알 수 있듯이 셀프 조인은 자신과 연결된 테이블입니다.

-- '数据库'的先修课信息，连接条件是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을 수행하고, 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 중국어 웹사이트의 기타 관련 기사를 참조하세요!