SQL之查询篇

1.数据查询语句

1.1 查询语句的基本结构

SELECT <目标列名序列>  --需要哪些列
    From <表名>      --来自哪张表
    [WHERE <行选择条件>]
    [GROUP BY <分组依据列>]
    [HAVING <组>]
    [ORDER BY <排序依据列>]

• 1
• 2
• 3
• 4
• 5
• 6

SELECT子句用于指定输出的字段； 
FROM子句用于指定数据的来源； 
WHERE子句用于指定数据的选择条件； 
GROUP BY子句用于对检索到的记录进行分组； 
HAVING 子句用于指定组的选择条件； 
ORDER BY 子句用于对查询的结果进行排序； 
以上子句中，SELECT 子句和FROM子句是必需的，其它是可选的。

---

---

1.2 单表查询

1.2.1选择表中若干列

（1）查询指定的列

SELECT 列名 FROM 表名

• 1

例子 ：SELECT Sname,Sno FROM Student

（2）查询全部列

SELECT * FROM 表名

• 1

例子 ：SELECT * FROM Student

（3）查询经过计算的列

SELECT 列名 FROM 表名

• 1

例子 ：SELECT Sname,year(getdata()) - year(Birthdate) FROM Student

---

---

---

1.2.2 选择表中的若干元祖

（1）消除取值相同的行：DISTINCT

SELECT DISTINCT Sno FROM 表名

• 1

例子 ：SELECT DISTINCT Sno FROM Student

---

---

（2）查询满足条件的元祖

查询条件	谓 词
比较	=、>、>=、<=、<、<>、!=、!>、!<
确定范围	BETWEEN…AND、 NOT BETWEEN…AND
确定集合	IN 、NOT IN
字符匹配	LIKE 、NOT LIKE
空值	IS NULL、IS NOT NULL
多重条件（逻辑谓词）	AND、OR

a.比较大小 
例子 ：SELECT Sname FROM Student WHERE year(getdata()) - year(Birthdate) < 20

---

b.确定范围 
BETWEEN…AND 和 NOT BETWEEN…AND可用于查找属性值在或不在指定范围。

列名 | 表达式 | [NOT] BETWEEN 下限值 AND 上限值  

• 1

BETWEEN…AND 代表的范围是在上限值和下限值之间（包括边界值），即为 true。 
NOT BETWEEN…AND 代表的范围是不在上限值和下限值之间（不包括边界值），即为true。（若判断值为边界值时，为 false）

例子 ：SELECT Sno,Cno FROM SC WHERE Grade BETWEEN 80 AND 90 
此查询等价于：SELECT Sno,Cno FROM SC WHERE Grade >= 80 AND Grade <= 90

例子 ：SELECT Sno,Cno FROM SC WHERE Grade NOT BETWEEN 80 AND 90 
此查询等价于：SELECT Sno,Cno FROM SC WHERE Grade < 80 OR Grade > 90

---

c. 确定集合 
IN运算符的含义：当列中的值和集合中的某个常量值相等时，结果为True。 
NOT IN运算符的含义：当列中的值和集合中的全部常量值都不相等时，结果为True。

例子 ：SELECT Sno FROM Student WHERE Dept IN ('信息管理系','计算机系') 
此查询等价于：SELECT Sno FROM Student WHERE Dept = '信息管理系' OR Dept = '计算机系')

例子 ：SELECT Sno FROM Student WHERE Dept NOT IN ('信息管理系','计算机系') 
此查询等价于：SELECT Sno FROM Student WHERE Dept != '信息管理系' AND Dept != '计算机系')

---

d. 字符串匹配 
Like运算符用于查找指定列中与匹配串匹配的元祖。

列名 [NOT] LIKE <匹配串>

• 1

通配符	含义
_（下划线）	匹配任意一个字符
%（百分号）	匹配0个或多个字符
[]	匹配[]中的任意一个字符。如[abcd]表示匹配abcd其中任何一个，若是连续的，可以用 - 表示，如[a-d]
[^]	不匹配[]中的任意一个字符。如[^abcd]表示不匹配abcd其中任何一个，若是连续的，可以用 - 表示，如[^a-d]

例子 ：

(查询姓“张”的学生详细信息)
SELECT * FROM Student WHERE Sname LIKE '张%'

(查询不姓“张”的学生详细信息)
SELECT * FROM Student WHERE Sname NOT LIKE '张%'

（查询姓“张”、“李”的学生详细信息）
SELECT * FROM Student WHERE Sname LIKE '[张李]%'

（查询名字的第二个字为“小” 或 “大”的学生详细信息）
SELECT * FROM Student WHERE Sname LIKE '_[小大]%'

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11

---

e. 涉及空值的查询 
空值（NULL）在数据库中有特殊含义，表示当前不确定或未知的值。判断是否为NULL时，不可用普通的比较运算符，需用IS NULL 
例子 ：SELECT Sno FROM Student WHERE Grade IS NULL

---

---

1.2.3 对查询结果进行排序

将查询结果按照指定的顺序显示。ASC表示按列值升序排列（从上往下，值从大到小）。DESC表示按列值降序排列（从上往下，值从小到大）。默认为ASC。

ORDER BY <列名> [ASC|DESC]

• 1

例子 ：SELECT Sno,Grade FROM SC ORDER BY Grade DESC

---

---

1.2.4 使用聚合函数统计数据

聚合函数也称为统计函数或集合函数，作用是对一组值进行计算并返回一个统计结果。

聚合函数	含义
COUNT(*)	统计表中元祖的个数
COUNT([DISTINCT]<列名>)	统计本列的非空列值个数
SUM(<列名>)	计算列值的和值（必须是数值型列）
AVG(<列名>)	计算列值的平均值（必须是数值型列）
MAX(<列名>)	计算列值的最大值
MIN(<列名>)	计算列值的最小值

上述函数除 COUNT(*) 外，其它函数在计算过程中均忽略NULL值

（统计学生总人数）
SELECT COUNT(*) FROM Student

（统计“001”学号学生的考试平均成绩）
SELECT AVG(Grade) FROM SC WHERE Sno = '001'

（查询“C001”号课程考试成绩的最高分和最低分）
SELECT MAX(Grade) 最高分,MIN(Grade) 最低分 FROM SC WHERE Cno = 'C001'

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8

聚合函数不能出现在WHERE子句中！

---

---

1.2.5 对数据进行分组统计

需要先对数据进行分组，然后再对每个组进行统计。分组子句GROUP BY。在一个查询语句中，可以用多个列进行分组。 
分组子句跟在WHERE子句的后面：

GROUP BY <分组依据列>[,...n]
    [HAVING <组筛选条件>]

• 1
• 2

（1）使用GROUP BY 子句

（统计每门课程的选课人数，列出课程号和选课人数）
SELECT Cno as 课程号, COUNT(Sno) as 选课人数 From SC Group BY Cno

（统计每个学生的选课门数和平均成绩）
SELECT Sno 学号, COUNT(*)  选课门数,AVG(Grade) 平均成绩 From SC Group BY Sno

带WHERE子句的分组（统计每个系的女生人数）
SELECT Dept, COUNT(*)  女生人数 From Student Where Sex = '女' Group BY Dept

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8

---

（2）使用HAVING 子句 
HAVING子句用于对分组后的统计结果再进行筛选，它的功能与WHERE子句类似，它用于组而不是单个记录。在HAVING子句中可以使用聚合函数，但在WHERE子句中不能，通常与GROUP子句一起使用。

（查询选课门数超过3门的学生的学号和选课门数）
SELECT Sno 学号, COUNT(*)  选课门数,AVG(Grade) 平均成绩 From SC Group BY Sno HAVING COUNT(*) > 3

• 1
• 2

---

（3）WHERE 、GROUP BY 、HAVING 的作用及执行顺序

• WHERE子句用于筛选FROM子句中指定的数据所产生的行数据。
• GROUP BY 子句用于对经 WHERE 子句筛选后的结果数据进行分组。
• HAVING 子句用于对分组后的统计结果再进行筛选。

可以分组操作之前应用的筛选条件，在WHERE子句中指定它们更有效，这样可以减少参与分组的数据行。在HAVING子句中指定的筛选条件应该是那些必须在执行分组操作之后应用的筛选条件。

（查询计算机系和信息管理系每个系的学生人数）
第一种：
SELECT Dept,COUNT(*) FROM Student GROUP BY Dept Having Dept in('计算机系','信息管理系')
第二种：
SELECT Dept,COUNT(*) FROM Student WHERE Dept in ('计算机系','信息管理系')GROUP BY Dept 

• 1
• 2
• 3
• 4
• 5
• 6

以上例子比较：第一种是按照系分组好了之后，只采取所有系中的两个系，显然效率不高。而第二种是先进行WHERE筛选条件之后，再进行GROUP BY 计算，显示更好。

---

---

---

---

1.3 多表连接查询

若一个查询同时涉及到两张或以上的表，则称为连接查询。

1.3.1 内连接

使用内连接时，如果两个表的相关字段满足条件，则从两个表中提取数据组成新的记录。

FROM 表1 [INNER] JOIN 表2 ON <连接条件>

• 1

注意：连接条件中的连接字段必须是可比的，必须是语义相同的列。

（查询学生及选课的详细信息）
SELECT * FROM Student INNER JOIN SC ON Student.Sno = SC.Sno

（查询计算机系学生的选课情况，列出该学生的名字、所修课程号、成绩）------行选择条件
SELECT Sname,Cno,Grade FROM Student INNER JOIN SC ON Student.Sno = SC.Sno WHERE Dept = '计算机系'

（统计每个系的平均成绩） ------分组的多表查询
SELECT Dept,AVG(Grade) AS AverageGrade FROM Student S INNER JOIN SC ON S.Sno = SC.Sno Group BY Dept

（统计计算机系每个学生的选课门数、平均成绩、最高成绩、最低成绩）------分组和行选择条件的多表连接查询
SELECT Sno,COUNT(*),AVG(Grade),MAX(Grade),MIN(Grade) FROM Student S JOIN SC ON S.Sno = SC.Sno WHERE Dept = '计算机系' Group BY Dept

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11

---

1.3.2 自连接

自连接是一种特殊的内连接，相互连接的表在物理上是一张表，但在逻辑上可以看做是两张表。

FROM 表1 AS T1 JOIN 表1 AS T2

• 1

通过为表取别名的方法，可以让物理上的一张表在逻辑上成为两张表。（一定要为表取别名！）

（查询与刘晨在同一个系学习的学生的姓名、所在系）
SELECT S1.Sname,S1.Dept FROM Student S1 JOIN Student S2 
ON S1.Dept = S2.Dept   ---同一个系的学生
WHERE S2.Sname = '刘晨'  ---S2表作为查询条件
AND S1.Sname != '刘晨'   ----S1表作为结果表，并从中去掉‘刘晨’本人信息

• 1
• 2
• 3
• 4
• 5

---

1.3.3 外连接

在内连接操作中，只有满足条件的元祖才能出现在查询结果集中。 
外连接是只限制一张表中的数据必须满足条件，而另一张表的数据可以不满足条件。

FROM 表1 LEFT|RIGHT [OUTER] JOIN 表2 ON <连接条件>

• 1

LEFT [OUTER] JOIN 称为左外连接，含义是限制表2中的数据必须满足条件，但不管表1中的数据是否满足条件，均输出表1中的数据。 
LEFT [OUTER] JOIN 称为右外连接，含义是限制表1中的数据必须满足条件，但不管表2中的数据是否满足条件，均输出表2中的数据。

---

内连接与外连接的区别： 

内连接：表A与表B进行内连接，则结果为两个表中满足条件的记录集，即C部分。 
外连接：如果表A和表B进行左外连接，则结果为 记录集A + 记录集C；如果表A和表B进行右外连接，则结果为 记录集B + 记录集C。

（查询没有人选的选修课程名）
SELECT Cname FROM Course C LEFT JOIN  SC ON C.Cno = SC.Cno WHERE SC.Cno IS NULL

• 1
• 2

例子解析：如果存在部分课程为被人选择，则必定在Course表中有但在SC表中没有出现，即在进行外连接时没人选的课程在与SC表构成的连接结果集中，对应的Sno、Cno、Grade列必定为空，所以只需**在连接后的结果中选出**SC表中Sno或Cno为空的元祖即可。

（统计计算机系每个学生的选课门数，包括没选课的学生）
SELECT S.Sno AS 学号,COUNT(SC.Cno) AS 选课门数 FROM Student S LEFT JOIN SC ON S.Sno = SC.Sno WHERE Dept = '计算机系' GROUP BY S.Sno

• 1
• 2

例子解析： 上述例子要求统计每个学生的….，所以在GROUP BY分组时，是按照学生表中的学号来分。而对于聚合函数COUNT，上述要求统计每个学生的选课门数，若写成COUNT(S.Sno)或COUNT(*)，则对没选课的学生都返回1，因为在外连接结果中，S.Sno不会是NULL，而COUNT(*)函数本身也不考虑NULL，它是直接对元祖个数进行计数。

注意：在对外连接的结果进行分组、统计等操作时，一定要注意分组依据列和统计列的选择。

---

---

---

---

1.4 使用TOP限制结果集行数

在使用SELECT语句进行查询时，有时只需要前几行数据。

TOP (expression) [PERCENT] [WITH TIES]

• 1

• expression：指定返回行数的数值表达式。如果指定了PERCENT，expression将隐式转换成float，否则是bigint
• PERCENT：指定只返回结果集中前 expression% 行数据。
• WITH TIES：指定从基本结果集中返回额外的数据行（只有在SELECT子句中包含了ORDER BY子句时，才能使用）。

TOP谓词写在SELECT单词的后面（如果有DISTINCT，则在DISTINCT后面）。

（查询考试成绩最高的3个成绩。列出学号、课程号、成绩）
SELECT TOP 3 Sno,Cno,Grade FROM SC ORDER BY Grade DESC
若要包括并列第3名的成绩：
SELECT TOP 3 Sno,Cno,Grade WITH TIES FROM SC ORDER BY Grade DESC

• 1
• 2
• 3
• 4

---

---

---

---

---