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

该连接操作的过程是,首先拿出student表中的第一条记录,然后根据连接条件和连接字段,与 sc表中的所有记录进行匹配,合适接连接起来形成结果表中的一个元组。然后再拿student表的 第二条记录与sc表进行匹配,第三条记录...,如此反复直到取完。这一匹配算法称为嵌套循环连接算法

内连接

内连接就是等值连接或者非等值连接的另一种写法,写法有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 |
+-----------+-------+------+------+-------+-----------+------+-------+

外连接(左外连接、右外连接、全外连接)

外连接的存在可以弥补内连接仅匹配符合条件的元组的缺陷,也就是说,内连接仅能查询出两个表中符合连接条件的元组 ,而外连接可以在某种程度上弥补这种缺陷。外连接分为左外连接(以JOIN关键字左边的表为基准,没有匹配的记录则置NULL),右外连接(以JOIN关键字右边的表为基准) ,全外连接(以JOIN关键字左右两边的表为基准)。其中MySQL不支持全外连接,但是可以用集合查询做到,即将左外连接的查询结果和右外连接的查询结果做UNION ALL操作。

-- 左外连接,以左边的表student为基准。
在MySQL中,OUTER关键字在MySQL中可省略 LEFT JOIN=LEFT OUTER JOINRIGHT 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='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    |
+-----------+-------+------+------+-------+

总结

引用

1.博客

  1.1 http://www.cnblogs.com/Ewin/archive/2009/10/05/1578322.html

  1.2 http://www.cnblogs.com/afirefly/archive/2010/10/08/1845906.html

  1.3 http://blog.csdn.net/zlxdream815/article/details/8208509

  1.4 http://www.cnblogs.com/liulaoshi/p/6219540.html

2.《数据库系统概论》

posted @ 2017-03-18 10:52  付大石  阅读(9099)  评论(0编辑  收藏