【数据库原理及应用】——数据查询SELECT（学习笔记）

📖 前言：建立数据库的目的就是为了对数据库进行操作，以便能够从中提取有用的信息。从本节开始将介绍对数据库的操作，其中数据库查询是数据操作中的核心操作，SQL提供了SELECT语句对数据库进行查询操作。

---

🕒 0. 思维导图

🕒 1. SELECT语句的一般格式

SELECT [ALL|DISTINCT]   
<目标列表达式> [别名] [ ，<目标列表达式> [别名]] … 
FROM   <表名或视图名> [别名]   [ ，<表名或视图名> [别名]] …
[WHERE <条件表达式>]
[GROUP  BY<列名1>[HAVING  <条件表达式>]]
[ORDER BY <列名2> [ASC|DESC]]

• SELECT子句：指定要显示的属性列；
• FROM子句：指定查询对象(基本表或视图)
• WHERE子句：指定查询条件；
• GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中使用聚集函数；
• HAVING短语：筛选出只有满足指定条件的组；
• ORDER BY子句：对查询结果表按指定列值的升序或降序排序 ；

在上节例1、2、3中所建立的教学管理系统中的3个表分别为：
（1）Student（Sno，Sname，Ssex，Sage，Smajor，Shometown）
（2）Course（Cno，Cname，Cpno，Ccredit）
（3）SC（Sno， Cno，Grade）

本次例题用到的表：
Student表
$\begin{array}{lllllll}& \text{ Sno }& \text{ Sname }& \text{ Ssex }& \text{ Sage }& \text{ Smajor }& \text{ Shometown }\\ 1& 20160101& \text{ 徐成波 }& \text{ 男 }& 20& \text{ 计算机科学与技术 }& \text{ 广东广州 }\\ 2& 20160102& \text{ 黄晓君 }& \text{ 女 }& 18& \text{ 计算机科学与技术 }& \text{ 湖南衡阳 }\\ 3& 20160103& \text{ 林宇珊 }& \text{ 女 }& 19& \text{ 计算机科学与技术 }& \text{ 河南新乡 }\\ 4& 20160104& \text{ 张茜 }& \text{ 女 }& 18& \text{ 计算机科学与技术 }& \text{ 广东中山 }\\ 5& 20160201& \text{ 黄晓君 }& \text{ 男 }& 21& \text{ 软件工程 }& \text{ 河北保定 }\\ 6& 20160202& \text{ 陈金燕 }& \text{ 女 }& 19& \text{ 软件工程 }& \text{ 江苏徐州 }\\ 7& 20160203& \text{ 张顺峰 }& \text{ 男 }& 22& \text{ 软件工程 }& \text{ 河南洛阳 }\\ 8& 20160204& \text{ 洪铭勇 }& \text{ 男 }& 20& \text{ 软件工程 }& \text{ 河北邯郸 }\\ 9& 20160301& \text{ 朱伟东 }& \text{ 男 }& 19& \text{ 网络工程 }& \text{ 山东青岛 }\\ 10& 20160302& \text{ 叶剑峰 }& \text{ 男 }& 20& \text{ 网络工程 }& \text{ 陕西西安 }\\ 11& 20160303& \text{ 林宇珊 }& \text{ 女 }& 21& \text{ 网络工程 }& \text{ 湖北襄阳 }\\ 12& 20160304& \text{ 吴妍娴 }& \text{ 女 }& 20& \text{ 网络工程 }& \text{ 浙江诸暨 }\end{array}$

Course表
$\begin{array}{lllll}& \text{ Cno }& \text{ Cname }& \text{ Cpno }& \text{ Ccredit }\\ 1& 1001& \text{ 高等数学 }& \text{ NULL }& 9\\ 2& 1002& \text{ C语言程序设计 }& \text{ NULL }& 3.5\\ 3& 1003& \text{ 数据结构 }& 1002& 4\\ 4& 1004& \text{ 操作系统 }& 1003& 4\\ 5& 1005& \text{ 数据库原理及应用 }& 1003& 3.5\\ 6& 1006& \text{ 信息管理系统 }& 1005& 3\\ 7& 1007& \text{ 面向对象与程序设计 }& 1002& 3.5\\ 8& 1008& \text{ 数据挖掘 }& 1005& 3\\ 9& 1009& \text{ 数据库\_0racle 大型数据库 }& \text{ NULL }& \text{ NULL }\\ 10& 1010& \text{ 数据库\_SQL Server }& \text{ NULL }& \text{ NULL }\end{array}$

SC表
$\begin{array}{llll}& \text{ Sno }& \text{ Cno }& \text{ Grade }\\ 1& 20160101& 1006& 89\\ 2& 20160101& 1007& 86\\ 3& 20160101& 1008& 90\\ 4& 20160102& 1005& 80\\ 5& 20160201& 1005& 90\\ 6& 20160203& 1003& 89\\ 7& 20160204& 1005& 96\\ 8& 20160303& 1001& 88\\ 9& 20160303& 1002& 86\\ 10& 20160303& 1003& 68\\ 11& 20160303& 1004& 98\\ 12& 20160303& 1005& 84\\ 13& 20160303& 1006& 73\end{array}$

🕒 2. 单表查询

🕘 2.1 选择表中的若干列（投影）

🕤 2.1.1 查询指定列

SELECT Sno,Sname
FROM Student;

$\begin{array}{lll}& \text{ Sno }& \text{ Sname }\\ 1& 20160101& \text{ 徐成波 }\\ 2& 20160102& \text{ 黄晓君 }\\ 3& 20160103& \text{ 林宇珊 }\\ 4& 20160104& \text{ 张茜 }\\ 5& 20160201& \text{ 黄晓君 }\\ 6& 20160202& \text{ 陈金燕 }\\ 7& 20160203& \text{ 张顺峰 }\\ 8& 20160204& \text{ 洪铭勇 }\\ 9& 20160301& \text{ 朱伟东 }\\ 10& 20160302& \text{ 叶剑峰 }\\ 11& 20160303& \text{ 林宇珊 }\\ 12& 20160304& \text{ 吴妍娴 }\end{array}$

🕤 2.1.2 查询全部列

例1：在Student表中查询所有学生信息。

SELECT * FROM Student

或者

Select Sno，Sname，Ssex，Sage，Smajor，Shometown from student

查询结果：
$\begin{array}{lllllll}& \text{ Sno }& \text{ Sname }& \text{ Ssex }& \text{ Sage }& \text{ Smajor }& \text{ Shometown }\\ 1& 20160101& \text{ 徐成波 }& \text{ 男 }& 20& \text{ 计算机科学与技术 }& \text{ 广东广州 }\\ 2& 20160102& \text{ 黄晓君 }& \text{ 女 }& 18& \text{ 计算机科学与技术 }& \text{ 湖南衡阳 }\\ 3& 20160103& \text{ 林宇珊 }& \text{ 女 }& 19& \text{ 计算机科学与技术 }& \text{ 河南新乡 }\\ 4& 20160104& \text{ 张茜 }& \text{ 女 }& 18& \text{ 计算机科学与技术 }& \text{ 广东中山 }\\ 5& 20160201& \text{ 黄晓君 }& \text{ 男 }& 21& \text{ 软件工程 }& \text{ 河北保定 }\\ 6& 20160202& \text{ 陈金燕 }& \text{ 女 }& 19& \text{ 软件工程 }& \text{ 江苏徐州 }\\ 7& 20160203& \text{ 张顺峰 }& \text{ 男 }& 22& \text{ 软件工程 }& \text{ 河南洛阳 }\\ 8& 20160204& \text{ 洪铭勇 }& \text{ 男 }& 20& \text{ 软件工程 }& \text{ 河北邯郸 }\\ 9& 20160301& \text{ 朱伟东 }& \text{ 男 }& 19& \text{ 网络工程 }& \text{ 山东青岛 }\\ 10& 20160302& \text{ 叶剑峰 }& \text{ 男 }& 20& \text{ 网络工程 }& \text{ 陕西西安 }\\ 11& 20160303& \text{ 林宇珊 }& \text{ 女 }& 21& \text{ 网络工程 }& \text{ 湖北襄阳 }\\ 12& 20160304& \text{ 吴妍娴 }& \text{ 女 }& 20& \text{ 网络工程 }& \text{ 浙江诸暨 }\end{array}$

🕤 2.1.3 查询经过计算的值

SELECT子句的<目标列表达式>可以为：

• 算术表达式
• 字符串常量
• 函数
• 列别名

例2：查询表Student中所有学生的姓名和出生的年。

SELECT Sname AS '姓名', YEAR(GETDATE())-Sage AS '出生日期'
FROM Student;

$\begin{array}{lll}& \text{ 姓名 }& \text{ 出生日期 }\\ 1& \text{ 徐成波 }& 2002\\ 2& \text{ 黄晓君 }& 2004\\ 3& \text{ 林宇珊 }& 2003\\ 4& \text{ 张茜 }& 2004\\ 5& \text{ 黄晓君 }& 2001\\ 6& \text{ 陈金燕 }& 2003\\ 7& \text{ 张顺峰 }& 2000\\ 8& \text{ 洪铭勇 }& 2002\\ 9& \text{ 朱伟东 }& 2003\\ 10& \text{ 叶剑峰 }& 2002\\ 11& \text{ 林宇珊 }& 2001\\ 12& \text{ 吴妍娴 }& 2002\end{array}$

🕘 2.2 选择表中的若干元组（选择）

🕤 2.2.1 消除结果集中的重复行

• 如果没有指定DISTINCT关键词，则缺省为ALL

SELECT Sno FROM SC;
/*等价于：*/
SELECT ALL  Sno  FROM SC;

例3：从Student表中找出所有学生的姓名。

SELECT DISTINCT Sname
FROM   Student;

$\begin{array}{ll}& \text{ Sname }\\ 1& \text{ 陈金燕 }\\ 2& \text{ 洪铭勇 }\\ 3& \text{ 黄晓君 }\\ 4& \text{ 林宇珊 }\\ 5& \text{ 吴妍娴 }\\ 6& \text{ 徐成波 }\\ 7& \text{ 叶剑峰 }\\ 8& \text{ 张茜 }\\ 9& \text{ 张顺峰 }\\ 10& \text{ 朱伟东 }\end{array}$

🕤 2.2.2 查询满足条件的元组（选择）

查询条件	谓词
比较	=，>，<，>=，<=，!=，<>，!>，!<；NOT+上述比较运算符
算术运算	+，-，*，/
确定范围	BETWEEN…AND…，NOT BETWEEN…AND…
确定集合	IN，NOT IN
字符匹配	LIKE，NOT LIKE
空值	IS NULL，IS NOT NULL
多重条件（逻辑运算）	AND，OR，NOT

🕞 2.2.2.1 比较大小

例3：查询Course表中学分超过4的课程号和课程名称。

SELECT cno,cname
FROM Course
WHERE Ccredit >= 4

$\begin{array}{lll}& \text{ cno }& \text{ cname }\\ 1& 1001& \text{ 高等数学 }\\ 2& 1003& \text{ 数据结构 }\\ 3& 1004& \text{ 操作系统 }\end{array}$

🕞 2.2.2.2 指定范围

例4：查询Student表中，学生的年龄介于19到21岁之间（包括19和21），显示这些学生的学号、姓名、专业和籍贯信息。

SELECT  Sno, Sname, Smajor, Shometown
FROM  Student
WHERE Sage BETWEEN 19 AND 21;

若不在期间：

WHERE Sage NOT BETWEEN 19 AND 21;

$\begin{array}{lllll}& \text{ Sno }& \text{ Sname }& \text{ Smajor }& \text{ Shometown }\\ 1& 20160102& \text{ 黄晓君 }& \text{ 计算机科学与技术 }& \text{ 湖南衡阳 }\\ 2& 20160104& \text{ 张茜 }& \text{ 计算机科学与技术 }& \text{ 广东中山 }\\ 3& 20160203& \text{ 张顺峰 }& \text{ 软件工程 }& \text{ 河南洛阳 }\end{array}$

❗ 转载请注明出处
作者：HinsCoder
博客链接：🔎 作者博客主页

🕞 2.2.2.3 确定集合

例5：查询Student表中专业为“网络工程”、“软件工程”的学生学号和姓名。

SELECT  Sno, Sname
FROM   Student 
WHERE Smajor IN ('网络工程', '软件工程');

相反的，若要查询除 “网络工程”、“软件工程”之外的所有专业的学生信息，则有：

WHERE Smajor NOT IN ( '网络工程', '软件工程');

$\begin{array}{lllll}& \text{ Sno }& \text{ Sname }& \text{ Smajor }& \text{ Shometown }\\ 1& 20160101& \text{ 徐成波 }& \text{ 计算机科学与技术 }& \text{ 广东广州 }\\ 2& 20160102& \text{ 黄晓君 }& \text{ 计算机科学与技术 }& \text{ 湖南衡阳 }\\ 3& 20160103& \text{ 林宇珊 }& \text{ 计算机科学与技术 }& \text{ 河南新乡 }\\ 4& 20160104& \text{ 张茜 }& \text{ 计算机科学与技术 }& \text{ 广东中山 }\end{array}$

🕞 2.2.2.4 字符匹配

谓词： [NOT] LIKE ‘<匹配串>’ [ESCAPE ‘ <换码字符>’]
其含义是查找指定的属性列值与<匹配串>相匹配的行。<匹配串>可以是一个完整的字符串，也可以含有通配符“%”和“_”。
使用通配符时，“%”代表任意长度（包括0）的字符串， “_”代表任意单个字符，ESCAPE表示转义符。LIKE匹配中使用通配符的查询又称模糊查询。

①、匹配串为固定字符串
例6：查询STUDENT表中电子专业的学生的学号、姓名和专业。

SELECT Sno, Sname, Smajor
FROM   Student 
WHERE Smajor LIKE '电子'; 

或

WHERE Smajor = '电子';

此类情况下，可以用等号（=）代替LIKE谓词，可以用不等于（<>或!=）运算符代替NOT LIKE谓词。

②、匹配串为含通配符的字符串
例7：查询Student表中工程相关专业的学生的学生号、姓名、专业和籍贯。

SELECT Sno, Sname, Smajor, Shometown
FROM   Student 
WHERE Smajor LIKE '%工程'

$\begin{array}{lllll}& \text{ Sno }& \text{ Sname }& \text{ Smajor }& \text{ Shometown }\\ 1& 20160201& \text{ 黄晓君 }& \text{ 软件工程 }& \text{ 河北保定 }\\ 2& 20160202& \text{ 陈金燕 }& \text{ 软件工程 }& \text{ 江苏徐州 }\\ 3& 20160203& \text{ 张顺峰 }& \text{ 软件工程 }& \text{ 河南洛阳 }\\ 4& 20160204& \text{ 洪铭勇 }& \text{ 软件工程 }& \text{ 河北邯郸 }\\ 5& 20160301& \text{ 朱伟东 }& \text{ 网络工程 }& \text{ 山东青岛 }\\ 6& 20160302& \text{ 叶剑峰 }& \text{ 网络工程 }& \text{ 陕西西安 }\\ 7& 20160303& \text{ 林宇珊 }& \text{ 网络工程 }& \text{ 湖北襄阳 }\\ 8& 20160304& \text{ 吴妍娴 }& \text{ 网络工程 }& \text{ 浙江诸暨 }\end{array}$

例8：查询Student表中名字中第二字为“晓”字的学生的学生号、姓名、专业和籍贯。

SELECT Sno, Sname, Smajor, Shometown
FROM   Student 
WHERE Sname LIKE '_晓%'

$\begin{array}{lllll}& \text{ Sno }& \text{ Sname }& \text{ Smajor }& \text{ Shometown }\\ 1& 20160102& \text{ 黄晓君 }& \text{ 计算机科学与技术 }& \text{ 湖南衡阳 }\\ 2& 20160201& \text{ 黄晓君 }& \text{ 软件工程 }& \text{ 河北保定 }\end{array}$

③、使用换码字符将通配符转义为普通字符

例9：查询课程名中含有”_”课程的课程编号和课程名。

SELECT  Cno, Cname
FROM    Course 
WHERE Cname LIKE '%/_%'  ESCAPE '/';

$\begin{array}{lll}& \text{ Cno }& \text{ Cname }\\ 1& 1009& \text{ 数据库\_Oracle大型数据库 }\\ 2& 1010& \text{ 数据库\_SQL Server }\end{array}$

🕞 2.2.2.5 空值

“is” 不能用 “=” 代替
例10：查询Course表中没有先修课的课程号、课程名和学分等详细信息。

SELECT Cno, Cname, Ccredit
FROM   Course 
WHERE Cpno is NULL

$\begin{array}{llll}& \text{ Cno }& \text{ Cname }& \text{ Ccredit }\\ 1& 1001& \text{ 高等数学 }& 9\\ 2& 1002& \text{ C语言程序设计 }& 3.5\\ 3& 1009& \text{ 数据库\_Oracle大型数据库 }& NULL\\ 4& 1010& \text{ 数据库\_SQL Server }& NULL\end{array}$

🕞 2.2.2.6 多重条件查询

逻辑运算符：AND和 OR来联结多个查询条件

• AND的优先级高于OR
• 可以用括号改变优先级

可用来实现多种其他谓词

• [NOT] IN
• [NOT] BETWEEN … AND …

例11：查询年龄为18或20岁的学生的学号、姓名、性别和专业等信息。

SELECT Sno, Sname, Ssex, Smajor
FROM   Student 
WHERE Sage=18 OR Sage=20

或

WHERE Sage in(18,20)

$\begin{array}{lllll}& \text{ Sno }& \text{ Sname }& \text{ Ssex }& \text{ Smajor }\\ 1& 20160101& \text{ 徐成波 }& \text{ 男 }& \text{ 计算机科学与技术 }\\ 2& 20160102& \text{ 黄晓君 }& \text{ 女 }& \text{ 计算机科学与技术 }\\ 3& 20160104& \text{ 张茜 }& \text{ 女 }& \text{ 计算机科学与技术 }\\ 4& 20160204& \text{ 洪铭勇 }& \text{ 男 }& \text{ 软件工程 }\\ 5& 20160302& \text{ 叶剑峰 }& \text{ 男 }& \text{ 网络工程 }\\ 6& 20160304& \text{ 吴妍娴 }& \text{ 女 }& \text{ 网络工程 }\end{array}$

🕤 2.2.3 聚集函数

主要用于完成计算，统计和分类
在检索数据时，经常需要对结果进行计算和统计。为了进一步方便用户，增强检索功能，SQL提供了许多聚集函数，经常使用的主要包括以下三类：（没有备注即不限制数据类型）

• ① COUNT
  • COUNT([DISTINCT|ALL]*) 统计元组个数
  • COUNT([DISTINCT|ALL] <列名>) 统计一列中值的个数
• ② SUM 和AVG
  • SUM([DISTINCT|ALL] <列名>) 计算一列值的总和（此列必须是数值型）
  • AVG([DISTINCT|ALL] <列名>) 计算一列值的平均值（此列必须是数值型）
• ③ MAX和MIN
  • MAX([DISTINCT|ALL] <列名>) 求一列值中的最大值
  • MIN([DISTINCT|ALL] <列名>) 求一列值中的最小值

例12：查询学生的总人数。

SELECT COUNT(*)  AS  '总人数'
FROM Student

$\begin{array}{ll}& \text{ 总人数 }\\ 1& 12\end{array}$

例13：查询选修了课程的学生数。

SELECT COUNT(DISTINCT Sno)  AS  '学生数'	/*指定DISTINCT短语，表示在计算时要取消指定列中的重复值*/
FROM  SC

$\begin{array}{ll}& \text{ 学生数 }\\ 1& 6\end{array}$

例14：查询课程总学分数。

SELECT  SUM(Ccredit) AS '课程总学分数'
FROM   Course

$\begin{array}{ll}& \text{ 课程总学分数 }\\ 1& 33.5\end{array}$

例15：查询计算机科学与技术和网络工程专业中年龄最小的、最大的值、平均年龄值。

SELECT  MAX(Sage) AS '最大年龄', MIN(Sage) AS '最小年龄', AVG(Sage) AS '平均年龄'
FROM    Student
WHERE   Smajor ='计算机科学与技术' OR Smajor ='网络工程'

$\begin{array}{llll}& \text{ 最大年龄 }& \text{ 最小年龄 }& \text{ 平均年龄 }\\ 1& 21& 18& 19\end{array}$

🕤 2.2.4 GROUP BY子句

目的：细化聚集函数的作用对象

• 未对查询结果分组，聚集函数将作用于整个查询结果
• 对查询结果分组后，聚集函数将分别作用于每个组
• 作用对象是查询的中间结果表
• 按指定的一列或多列值分组，值相等的为一组

例16：查询Student表中各个专业的学生数。

SELECT  Smajor, COUNT(Sno) AS '学生数'
FROM    Student
GROUP BY Smajor

该语句首先对Student表的数据按照“Smajor”进行分组，这样具有相同专业名的元组归为一组；然后再对每一组使用COUNT(Sno) 进行计算，求出每个专业的学号个数，也就是学生人数。

$\begin{array}{lll}& \text{ Smajor }& \text{ 学生数 }\\ 1& 计算机科学与技术& 4\\ 2& 软件工程& 4\\ 3& 网络工程& 4\end{array}$

在分组后，还要按照一定条件进行筛选，需要使用HAVING子句

例17：查询Student表中，女生人数不超过2人的专业名和女生人数。

SELECT Smajor, count(*)AS '女生人数'
FROM  Student
WHERE Ssex = '女'
GROUP BY Smajor HAVING count(*)<=2 

需要说明的是：使用GROUP BY子句后，SELECT子句中的列表只能是GROUP BY 子句中指定的列或聚集函数，否则系统会报错。
$\begin{array}{lll}& \text{ Smajor }& \text{ 女生人数 }\\ 1& 软件工程& 1\\ 2& 网络工程& 2\end{array}$

使用HAVING短语筛选最终输出结果

• WHERE用于筛选FROM指定的数据对象
• GROUP BY用语对WHERE的结果进行分组
• HAVING用语筛选GROUP BY以后的分组数据

HAVING短语与WHERE子句的区别：

• 作用对象不同
• WHERE子句作用于基表或视图，从中选择满足条件的元组
• HAVING短语作用于组，从中选择满足条件的组。

例18：查询SC表中，选修两门及其以上课程的学生的学号和选课门数。

SELECT Sno, count(*)AS 'SC_Num'
FROM  SC
GROUP BY Sno 
HAVING count(*)>=2

GROUP BY 子句按Sno的值分组，所有具有相同Sno的元组为一组，对每一组使用count(*)进行计算，统计出每位学生的选课门数， HAVING子句去掉不满足count(*)>=2 的组。
$\begin{array}{lll}& \text{ Sno }& \text{ SC\_Num }\\ 1& 20160101& 3\\ 2& 20160303& 6\end{array}$

例19：查询平均成绩大于90分的学生的学号和平均成绩

SELECT Sno, Avg(grade)  AS '平均成绩'
FROM  SC
GROUP BY Sno 
HAVING avg(grade)>90

GROUP BY 子句按Sno的值分组，所有具有相同Sno的元组为一组，对每一组使用avg(grade)进行计算，统计出每位学生的选课门数， HAVING子句去掉不满足avg(grade)>90的组。
$\begin{array}{lll}& \text{ Sno }& \text{ 平均成绩 }\\ 1& 20160204& 96\end{array}$

🕤 2.2.5 ORDER BY子句

可以按一个或多个属性列排序

• 升序：ASC；降序：DESC；缺省值为升序
• 当排序列含空值时
  • ASC：排序列为空值的元组最后显示
  • DESC：排序列为空值的元组最先显示

注：这里对空值的处理由各个DBMS实现决定

例20：查询Student表中所有学生的信息，查询结果按专业名降序排列，同一个专业的学生按照年龄升序排列。

SELECT  * 
FROM  Student
ORDER BY Smajor DESC, Sage

$\begin{array}{lllllll}& \text{ Sno }& \text{ Sname }& \text{ Ssex }& \text{ Sage }& \text{ Smajor }& \text{ Shometown }\\ 1& 20160301& \text{ 朱伟东 }& \text{ 男 }& 19& \text{ 网络工程 }& \text{ 山东青岛 }\\ 2& 20160302& \text{ 叶剑峰 }& \text{ 男 }& 20& \text{ 网络工程 }& \text{ 陕西西安 }\\ 3& 20160304& \text{ 吴妍娴 }& \text{ 女 }& 20& \text{ 网络工程 }& \text{ 浙江诸暨 }\\ 4& 20160303& \text{ 林宇珊 }& \text{ 女 }& 21& \text{ 网络工程 }& \text{ 湖北襄阳 }\\ 5& 20160202& \text{ 陈金燕 }& \text{ 女 }& 19& \text{ 软件工程 }& \text{ 江苏徐州 }\\ 6& 20160204& \text{ 洪铭勇 }& \text{ 男 }& 20& \text{ 软件工程 }& \text{ 河北邯郸 }\\ 7& 20160201& \text{ 黄晓君 }& \text{ 男 }& 21& \text{ 软件工程 }& \text{ 河北保定 }\\ 8& 20160203& \text{ 张顺峰 }& \text{ 男 }& 22& \text{ 软件工程 }& \text{ 河南洛阳 }\\ 9& 20160102& \text{ 黄晓君 }& \text{ 女 }& 18& \text{ 计算机科学与技术 }& \text{ 湖南衡阳 }\\ 10& 20160104& \text{ 张茜 }& \text{ 女 }& 18& \text{ 计算机科学与技术 }& \text{ 广东中山 }\\ 11& 20160103& \text{ 林宇珊 }& \text{ 女 }& 19& \text{ 计算机科学与技术 }& \text{ 河南新乡 }\\ 12& 20160101& \text{ 徐成波 }& \text{ 男 }& 20& \text{ 计算机科学与技术 }& \text{ 广东广州 }\end{array}$

例21：查询选修两门以上（含两门）且各门课程均及格的学生的学号和其总成绩，查询结果按总成绩降序排列。

SELECT Sno, sum(Grade)AS 'Total_grade'
FROM  SC WHERE Grade>=60 
GROUP BY Sno 
HAVING count(*)>=2
ORDER BY sum(Grade) DESC

说明：FROM取出整个SC；WHERE 筛选 Grade>=60的元组；GROUP BY 子句按Sno的值分组； HAVING子句筛选选课两门及其以上的分组；SELECT在剩下的组中提取学号和总成绩；ORDER BY将选取结果排序。
$\begin{array}{lll}& \text{ Sno }& \text{ Total\_grade }\\ 1& 20160303& 497\\ 2& 20160101& 265\end{array}$

🕒 3. 连接查询

定义：同时涉及多个表的查询
连接条件或连接谓词：用来连接两个表的条件

一般格式：

[<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>
[<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>

连接字段：连接谓词中的列名称

• 连接条件中的各连接字段类型必须是可比的，但名字不必是相同的

🕘 3.1 连接操作的执行过程

🕤 3.1.1 嵌套循环法(NESTED-LOOP)

• 首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。
• 表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。
• 重复上述操作，直到表1中的全部元组都处理完毕
  

🕤 3.1.2 排序合并法(SORT-MERGE)

• 常用于=连接
• 首先按连接属性对表1和表2排序
  对表1的第一个元组，从头开始扫描表2，顺序查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。当遇到表2中第一条大于表1连接字段值的元组时，对表2的查询不再继续。
• 找到表1的第二条元组，然后从刚才的中断点处继续顺序扫描表2，查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。直接遇到表2中大于表1连接字段值的元组时，对表2的查询不再继续。
  重复上述操作，直到表1或表2中的全部元组都处理完毕为止。

🕤 3.1.3 索引连接(INDEX-JOIN)

• 对表2按连接字段建立索引。
• 对表1中的每个元组，依次根据其连接字段值查询表2的索引，从中找到满足条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。

🕘 3.2 等值与非等值连接查询

例22：查询网络工程专业的每个学生的基本信息及选课情况。

SELECT Student.*, SC.* 
FROM  Student, SC
WHERE Student.Smajor='网络工程' AND Student.Sno = SC.Sno

$\begin{array}{llllllllll}& \text{ Sno }& \text{ Sname }& \text{ Ssex }& \text{ Sage }& \text{ Smajor }& \text{ Shometown }& \text{ Sno }& \text{ Cno }& \text{ Grade }\\ 1& 20160303& \text{ 林宇珊 }& \text{ 女 }& 21& \text{ 网络工程 }& \text{ 湖北襄阳 }& 20160303& 1001& 88\\ 2& 20160303& \text{ 林宇珊 }& \text{ 女 }& 21& \text{ 网络工程 }& \text{ 湖北襄阳 }& 20160303& 1002& 86\\ 3& 20160303& \text{ 林宇珊 }& \text{ 女 }& 21& \text{ 网络工程 }& \text{ 湖北襄阳 }& 20160303& 1003& 68\\ 4& 20160303& \text{ 林宇珊 }& \text{ 女 }& 21& \text{ 网络工程 }& \text{ 湖北襄阳 }& 20160303& 1004& 98\\ 5& 20160303& \text{ 林宇珊 }& \text{ 女 }& 21& \text{ 网络工程 }& \text{ 湖北襄阳 }& 20160303& 1005& 84\\ 6& 20160303& \text{ 林宇珊 }& \text{ 女 }& 21& \text{ 网络工程 }& \text{ 湖北襄阳 }& 20160303& 1006& 73\end{array}$

上述连接是等值连接。

例23：查询网络工程专业的每个学生的基本信息及选课情况，去掉重复的SC.Sno列。

SELECT Student.Sno, Sname, Ssex, Sage, Smajor, Shometown, Cno, Grade 
FROM  Student, SC
WHERE Student.Smajor='网络工程' AND Student.Sno = SC.Sno

$\begin{array}{lllllllll}& \text{ Sno }& \text{ Sname }& \text{ Ssex }& \text{ Sage }& \text{ Smajor }& \text{ Shometown }& \text{ Cno }& \text{ Grade }\\ 1& 20160303& \text{ 林宇珊 }& \text{ 女 }& 21& \text{ 网络工程 }& \text{ 湖北襄阳 }& 1001& 88\\ 2& 20160303& \text{ 林宇珊 }& \text{ 女 }& 21& \text{ 网络工程 }& \text{ 湖北襄阳 }& 1002& 86\\ 3& 20160303& \text{ 林宇珊 }& \text{ 女 }& 21& \text{ 网络工程 }& \text{ 湖北襄阳 }& 1003& 68\\ 4& 20160303& \text{ 林宇珊 }& \text{ 女 }& 21& \text{ 网络工程 }& \text{ 湖北襄阳 }& 1004& 98\\ 5& 20160303& \text{ 林宇珊 }& \text{ 女 }& 21& \text{ 网络工程 }& \text{ 湖北襄阳 }& 1005& 84\\ 6& 20160303& \text{ 林宇珊 }& \text{ 女 }& 21& \text{ 网络工程 }& \text{ 湖北襄阳 }& 1006& 73\end{array}$

上述连接是自然连接。

🕘 3.3 自身连接查询

一个表与其自己进行连接

• 需要给表起别名以示区别
• 由于所有属性名都是同名属性，因此必须使用别名前缀

例24：查找所有课程的先修课程，并显示课程名、学分和先修课程名。

SELECT C1.Cname AS '课程名', C1.Ccredit AS '学分', C2.Cname AS '先修课程名'
FROM Course AS C1, Course AS C2 
WHERE C1.Cpno = C2.Cno

$\begin{array}{llll}& \text{ 课程名 }& \text{ 学分 }& \text{ 先修课程名 }\\ 1& \text{ 数据结构 }& 4& \text{ C语言程序设计 }\\ 2& \text{ 操作系统 }& 4& \text{ 数据结构 }\\ 3& \text{ 数据库原理及应用 }& 3.5& \text{ 数据结构 }\\ 4& \text{ 信息管理系统 }& 3& \text{ 数据库原理及应用 }\\ 5& \text{ 面向对象与程序设计 }& 3.5& \text{ C语言程序设计 }\\ 6& \text{ 数据挖掘 }& 3& \text{ 数据库原理及应用 }\end{array}$

例25：查找每一门课程的间接先修课程号（即先修课的先修课）。

SELECT C1.Cname AS '课程名', C2.Cpno AS'间接 先修课程号'
FROM Course AS C1, Course AS C2 
WHERE C1.Cpno = C2.Cno

$\begin{array}{lll}& \text{ 课程名 }& \text{ 间接 先修课程号 }\\ 1& \text{ 数据结构 }& NULL\\ 2& \text{ 操作系统 }& 1002\\ 3& \text{ 数据库原理及应用 }& 1002\\ 4& \text{ 信息管理系统 }& 1003\\ 5& \text{ 面向对象与程序设计 }& NULL\\ 6& \text{ 数据挖掘 }& 1003\end{array}$

🕘 3.4 外连接查询

外连接与普通连接的区别：

• 普通连接操作只输出满足连接条件的元组
• 外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出

🕤 3.4.1 左外连接

例26：查询网络工程专业的所有学生的信息及其选课情况，如果学生没有选课则列出其基本信息。

SELECT Student.Sno, Sname, Ssex, Sage, Smajor, Shometown, Cno, Grade 
FROM  Student LEFT [OUTER] JOIN SC ON (Student.Sno=SC.Sno)	/*[OUTER]可写可不写*/
WHERE Student.Smajor='网络工程'

$\begin{array}{lllllllll}& \text{ Sno }& \text{ Sname }& \text{ Ssex }& \text{ Sage }& \text{ Smajor }& \text{ Shometown }& \text{ Cno }& \text{ Grade }\\ 1& 20160301& \text{ 朱伟东 }& \text{ 男 }& 19& \text{ 网络工程 }& \text{ 山东青岛 }& NULL& NULL\\ 2& 20160302& \text{ 叶剑峰 }& \text{ 男 }& 20& \text{ 网络工程 }& \text{ 陕西西安 }& NULL& NULL\\ 3& 20160303& \text{ 林宇珊 }& \text{ 女 }& 21& \text{ 网络工程 }& \text{ 湖北襄阳 }& 1001& 88\\ 4& 20160303& \text{ 林宇珊 }& \text{ 女 }& 21& \text{ 网络工程 }& \text{ 湖北襄阳 }& 1002& 86\\ 5& 20160303& \text{ 林宇珊 }& \text{ 女 }& 21& \text{ 网络工程 }& \text{ 湖北襄阳 }& 1003& 68\\ 6& 20160303& \text{ 林宇珊 }& \text{ 女 }& 21& \text{ 网络工程 }& \text{ 湖北襄阳 }& 1004& 98\\ 7& 20160303& \text{ 林宇珊 }& \text{ 女 }& 21& \text{ 网络工程 }& \text{ 湖北襄阳 }& 1005& 84\\ 8& 20160303& \text{ 林宇珊 }& \text{ 女 }& 21& \text{ 网络工程 }& \text{ 湖北襄阳 }& 1006& 73\\ 9& 20160304& \text{ 吴妍娴 }& \text{ 女 }& 20& \text{ 网络工程 }& \text{ 浙江诸暨 }& NULL& NULL\end{array}$

🕤 3.4.2 右外连接

例27：查询开课的课程情况，没有开课的只显示课程名和学分，课程编号为空。

SELECT DISTINCT SC.Cno, Course.Cname, Course.Ccredit
FROM  SC Right [OUTER] JOIN Course ON (SC.Cno=Course.Cno)

$\begin{array}{llll}& \text{ Cno }& \text{ Cname }& \text{ Ccredit }\\ 1& NULL& \text{ 数据库\_Oracle大型数据库 }& NULL\\ 2& NULL& \text{ 数据库\_SQL Server }& NULL\\ 3& 1001& \text{ 高等数学 }& 9\\ 4& 1002& \text{ C语言程序设计 }& 3.5\\ 5& 1003& \text{ 数据结构 }& 4\\ 6& 1004& \text{ 操作系统 }& 4\\ 7& 1005& \text{ 数据库原理及应用 }& 3.5\\ 8& 1006& \text{ 信息管理系统 }& 3\\ 9& 1007& \text{ 面向对象与程序设计 }& 3.5\\ 10& 1008& \text{ 数据挖掘 }& 3\end{array}$

🕘 3.5 复合条件连接查询 ☆

WHERE子句中含多个连接条件

例28：查询选修了课程名中含有“程序”两个字的课程的学生的学号、姓名、专业名、所选修的课程名和学分。

SELECT Student.Sno, Sname, Smajor, Course.Cname, Ccredit
FROM  Student, SC, Course
WHERE Student.Sno = SC.Sno                
AND Course.Cno = SC.Cno
AND  Cname LIKE '%程序%'; /* 其他限定条件 */

$\begin{array}{llllll}& \text{ Sno }& \text{ Sname }& \text{ Smajor }& \text{ Cname }& \text{ Ccredit }\\ 1& 20160101& \text{ 徐成波 }& \text{ 计算机科学与技术 }& \text{ 面向对象与程序设计 }& 3.5\\ 2& 20160303& \text{ 林宇珊 }& \text{ 网络工程 }& \text{ C语言程序设计 }& 3.5\end{array}$

例29：查询计算机科学与技术专业的学生选修课程的信息，包括学生的姓名、年龄、所选修的课程的课程名、成绩。

SELECT Sname, Sage, Cname, Grade
FROM  Student, SC, Course/*多表连接*/
WHERE Student.Sno = SC.Sno AND Course.Cno =  SC.Cno
AND  Smajor='计算机科学与技术';

$\begin{array}{lllll}& \text{ Sname }& \text{ Sage }& \text{ Cname }& \text{ Grade }\\ 1& \text{ 徐成波 }& 20& \text{ 信息管理系统 }& 89\\ 2& \text{ 徐成波 }& 20& \text{ 面向对象与程序设计 }& 86\\ 3& \text{ 徐成波 }& 20& \text{ 数据挖掘 }& 90\\ 4& \text{ 黄晓君 }& 18& \text{ 数据库原理及应用 }& 80\end{array}$

🕘 3.6 集合运算查询

集合操作的种类

• 并操作UNION
• 交操作INTERSECT
• 差操作EXCEPT

注意：
（1）参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同
（2）不同的DBMS，所支持的集合操作也不尽相同

例30：查询软件工程和网络工程两个专业的学生情况。

(SELECT * FROM Student
WHERE Smajor='网络工程')
UNION					/*用or连接也可以*/
(SELECT * FROM Student
WHERE Smajor='软件工程')

$\begin{array}{lllllll}& \text{ Sno }& \text{ Sname }& \text{ Ssex }& \text{ Sage }& \text{ Smajor }& \text{ Shometown }\\ 1& 20160301& \text{ 朱伟东 }& \text{ 男 }& 19& \text{ 网络工程 }& \text{ 山东青岛 }\\ 2& 20160302& \text{ 叶剑峰 }& \text{ 男 }& 20& \text{ 网络工程 }& \text{ 陕西西安 }\\ 3& 20160303& \text{ 林宇珊 }& \text{ 女 }& 21& \text{ 网络工程 }& \text{ 湖北襄阳 }\\ 4& 20160304& \text{ 吴妍娴 }& \text{ 女 }& 20& \text{ 网络工程 }& \text{ 浙江诸暨 }\\ 5& 20160201& \text{ 黄晓君 }& \text{ 男 }& 21& \text{ 软件工程 }& \text{ 河北保定 }\\ 6& 20160202& \text{ 陈金燕 }& \text{ 女 }& 19& \text{ 软件工程 }& \text{ 江苏徐州 }\\ 7& 20160203& \text{ 张顺峰 }& \text{ 男 }& 22& \text{ 软件工程 }& \text{ 河南洛阳 }\\ 8& 20160204& \text{ 洪铭勇 }& \text{ 男 }& 20& \text{ 软件工程 }& \text{ 河北邯郸 }\end{array}$

例31：查询同时选修了“高等数学”和“数据库原理及应用”课程的学生的学号、姓名、专业等基本信息。

(SELECT Student.Sno, Sname, Smajor
FROM Student,Course,SC
WHERE Student.Sno=SC.Sno And Course.Cno=SC.Cno AND Cname='高等数学')		
INTERSECT
(SELECT Student.Sno, Sname, Smajor
FROM Student,Course,SC
WHERE Student.Sno=SC.Sno And Course.Cno=SC.Cno AND Cname='数据库原理及应用')

问题：下面的写法对吗？

(SELECT Student.Sno, Sname, Smajor
FROM Student,Course,SC
WHERE Student.Sno=SC.Sno And Course.Cno=SC.Cno AND Cname='高等数学' AND Cname='数据库原理及应用')	

我们观察运行前部分代码

发现表中一行数据仅对应一个课程，因此简单加上AND Cname='数据库原理及应用'无疑是失败的，因为一个Cname不可能存两个课程。

正确的另一种写法（嵌套查询）：

SELECT Student.Sno, Sname, Smajor
FROM Student,Course,SC
WHERE Student.Sno=SC.Sno And Course.Cno=SC.Cno AND Cname='高等数学' and student.sno in
(SELECT Student.Sno
FROM Student,Course,SC
WHERE Student.Sno=SC.Sno And Course.Cno=SC.Cno AND Cname='数据库原理及应用')

$\begin{array}{llll}& \text{ Sno }& \text{ Sname }& \text{ Smajor }\\ 1& 20160303& \text{ 林宇珊 }& \text{ 网络工程 }\end{array}$

例32：查询选修“数据库原理及应用”课程，但没有选修“数据结构”的学生的学号、姓名、专业等基本信息。

(SELECT Student.Sno, Sname, Smajor
FROM Student,Course,SC
WHERE Student.Sno=SC.Sno And Course.Cno=SC.Cno AND Cname='数据库原理及应用')
EXCEPT			/*另一种解法：用嵌套查询的not in*/
(SELECT Student.Sno, Sname, Smajor
FROM Student,Course,SC
WHERE Student.Sno=SC.Sno And Course.Cno=SC.Cno AND Cname='数据结构')

$\begin{array}{llll}& \text{ Sno }& \text{ Sname }& \text{ Smajor }\\ 1& 20160102& \text{ 黄晓君 }& \text{ 计算机科学与技术 }\\ 2& 20160201& \text{ 黄晓君 }& \text{ 软件工程 }\\ 3& 20160204& \text{ 洪铭勇 }& \text{ 软件工程 }\end{array}$

🕒 4. 嵌套查询

🕘 4.1 嵌套查询概述

• 一个SELECT-FROM-WHERE语句称为一个查询块
• 将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询

例子：

SELECT Cname          /*外层查询/父查询*/
FROM Course
WHERE Cno IN
 (SELECT Cno		  /*内层查询/子查询*/
  FROM SC
  WHERE grade>95 );

$\begin{array}{ll}& \text{ Cname }\\ 1& \text{ 操作系统 }\\ 2& \text{ 数据库原理及应用 }\end{array}$

• 子查询的限制： 不能使用ORDER BY子句
• 层层嵌套方式反映了 SQL语言的结构化
• 有些嵌套查询可以用连接运算替代

🕘 4.2 嵌套查询的分类

🕤 4.2.1 不相关子查询

• 子查询的查询条件不依赖于父查询
• 由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。

🕤 4.2.2 相关子查询

• 子查询的查询条件依赖于父查询
• 首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表。然后再取外层表的下一个元组，重复这一过程，直至外层表全部检查完为止。
• 通常使用EXIST谓词

🕘 4.3 带有IN谓词的子查询

• 带有IN谓词的子查询是指父查询与子查询之间用IN进行连接，用于判断某个属性列值是否在子查询的结果中
• 在嵌套查询中，由于子查询的结果往往是一个集合，所以谓词IN是嵌套查询中最经常使用的谓词，尽量少用=，因为往往是集合操作而非单个对象。

例33：查询选修了“数据结构”课程的学生的学号、姓名和专业。

查询选修“数据结构”课程的学生信息，首先确定“数据结构”的课程号，然后再查找选修该课程的学生学号，最后确定学号所对应的学生信息。所以可以先分步完成此查询，然后再构造嵌套查询。

（1）确定“数据结构”课程号

SELECT Cno
FROM Course
WHERE Cname='数据结构'

$\begin{array}{ll}& \text{ Cno }\\ 1& 1003\end{array}$

（2）查找所有选修课程号为“1003”的学生学号

SELECT Sno
FROM SC
WHERE Cno IN

$\begin{array}{ll}& \text{ Sno }\\ 1& 20160203\\ 2& 20160303\end{array}$

（3）查询学号为“20160101”、“20160203”、“20160303”的学生信息

SELECT Sno, Sname, Smajor
FROM Student
WHERE Sno IN (‘20160101’、‘20160203’、‘20160303’  )

将（1）嵌入到（2）的条件中，再将（2）嵌入到（3）的条件中，构造嵌套查询的形式，表示为：

SELECT Sno, Sname, Smajor
FROM Student
WHERE Sno IN
 (SELECT Sno
  FROM SC
  WHERE Cno IN 
     (SELECT Cno
      FROM Course
      WHERE Cname='数据结构')
 );

$\begin{array}{llll}& \text{ Sno }& \text{ Sname }& \text{ Smajor }\\ 1& 20160203& \text{ 张顺峰 }& \text{ 软件工程 }\\ 2& 20160303& \text{ 林宇珊 }& \text{ 网络工程 }\end{array}$

Q：该查询可以用连接运算实现吗？
A：可以，三个表直接建立连接

❗ 转载请注明出处
作者：HinsCoder
博客链接：🔎 作者博客主页

NOT IN的使用：

例34：查询“计算机科学与技术”专业中没有选修“数据库原理及应用”课程的学生的学号、姓名。

SELECT Sno, Sname
FROM Student
WHERE Smajor='计算机科学与技术' AND Sno NOT IN 
   (SELECT Sno
    FROM SC
	WHERE Cno IN 
	 (SELECT Cno
	  FROM Course
	  WHERE Cname='数据库原理及应用')
    )

$\begin{array}{lll}& \text{ Sno }& \text{ Sname }\\ 1& 20160101& \text{ 徐成波 }\\ 2& 20160103& \text{ 林宇珊 }\\ 3& 20160104& \text{ 张茜 }\end{array}$

Q：直接用连接查询可以实现吗？
A：不能，涉及NOT IN这些都不能简单连接

习题：查询与陈金燕在同一个专业学习的学生信息。
解法一：子查询IN谓词

select Sno,Sname,Ssex,Sage,Smajor,Shometown from student 
  where Smajor in
 (select Smajor from student where Sname=‘陈金燕’)

解法二：自身连接

Select s1.Sno,s1.Sname,s1.Ssex,s1.Sage, s1.Smajor,s1.Shometown 
from Student as s1,Student as s2 
where s1.Smajor =s2.Smajor and s2.Sname='陈金燕'

$\begin{array}{lllllll}& \text{ Sno }& \text{ Sname }& \text{ Ssex }& \text{ Sage }& \text{ Smajor }& \text{ Shometown }\\ 1& 20160201& \text{ 黄晓君 }& \text{ 男 }& 21& \text{ 软件工程 }& \text{ 河北保定 }\\ 2& 20160202& \text{ 陈金燕 }& \text{ 女 }& 19& \text{ 软件工程 }& \text{ 江苏徐州 }\\ 3& 20160203& \text{ 张顺峰 }& \text{ 男 }& 22& \text{ 软件工程 }& \text{ 河南洛阳 }\\ 4& 20160204& \text{ 洪铭勇 }& \text{ 男 }& 20& \text{ 软件工程 }& \text{ 河北邯郸 }\end{array}$

🕘 4.4 带有比较运算符的子查询

• 当能确切知道内层查询返回单值时，可用比较运算符（>，<，=，>=，<=，!=或< >）。
• 与ANY或ALL谓词配合使用

在例33中，“数据结构”的课程号是唯一的，子查询返回值为单值，故父查询中的IN谓词也可以用比较运算符“=”代替，即表示为：

SELECT Sno, Sname, Smajor
FROM Student
WHERE Sno IN 
 ( SELECT Sno FROM SC WHERE Cno = 
	( SELECT Cno FROM Course
	  WHERE Cname='数据结构' ))

$\begin{array}{llll}& \text{ Sno }& \text{ Sname }& \text{ Smajor }\\ 1& 20160203& \text{ 张顺峰 }& \text{ 软件工程 }\\ 2& 20160303& \text{ 林宇珊 }& \text{ 网络工程 }\end{array}$

例35：查询超过平均年龄的学生的学号、姓名、年龄和专业。

SELECT Sno, Sname, Sage, Smajor
FROM Student
WHERE Sage > 
	( SELECT AVG(Sage)
	  FROM Student  )

$\begin{array}{lllll}& \text{ Sno }& \text{ Sname }& \text{ Sage }& \text{ Smajor }\\ 1& 20160101& \text{ 徐成波 }& 20& \text{ 计算机科学与技术 }\\ 2& 20160201& \text{ 黄晓君 }& 21& \text{ 软件工程 }\\ 3& 20160203& \text{ 张顺峰 }& 22& \text{ 软件工程 }\\ 4& 20160204& \text{ 洪铭勇 }& 20& \text{ 软件工程 }\\ 5& 20160302& \text{ 叶剑峰 }& 20& \text{ 网络工程 }\\ 6& 20160303& \text{ 林宇珊 }& 21& \text{ 网络工程 }\\ 7& 20160304& \text{ 吴妍娴 }& 20& \text{ 网络工程 }\end{array}$

本例中，使用了比较运算符“>”。通过观察，可以发现本查询来自同一个表Student，子查询获得全部学生的平均年龄，父查询超过平均年龄的学生信息。

当内层查询返回值的个数为多值时，则比较运算符要与ANY（等价SOME）或ALL谓词配合使用。此类情况将在第三种嵌套查询带有ANY或ALL谓词的子查询中进行讲解。

🕘 4.5 带有ANY或ALL谓词的子查询

谓词语义：
ANY：任意一个值
ALL：所有值

$\begin{array}{ccc}\text{ 运算符 }& \text{ ANY }& \mathbf{ALL}\\ >& \text{ 大于子查询结果中的某个值 }& \text{ 大于子查询结果中的所有值 }\\ <& \text{ 小于子查询结果中的某个值 }& \text{ 小于子查询结果中的所有值 }\\ >=& \text{ 大于等于子查询结果中的某个值 }& \text{ 大于等于子查询结果中的所有值 }\\ <=& \text{ 小于等于子查询结果中的某个值 }& \text{ 小于等于子查询结果中的所有值 }\\ =& \text{ 等于子查询结果中的某个值 }& \text{ 通常没有实际意义 }\\ !=或<>& \text{ 不等于子查询结果中的某个值 }& \text{ 不等于子查询结果中的任何一个值 }\end{array}$

例36：查询其他专业中比所有“计算机科学与技术”专业的学生年龄都大的学生的信息。

SELECT *
FROM Student
WHERE Smajor<>'计算机科学与技术' AND Sage > ALL 
	( SELECT Sage
	  FROM Student
	  WHERE Smajor='计算机科学与技术'  )

$\begin{array}{lllllll}& \text{ Sno }& \text{ Sname }& \text{ Ssex }& \text{ Sage }& \text{ Smajor }& \text{ Shometown }\\ 1& 20160201& \text{ 黄晓君 }& \text{ 男 }& 21& \text{ 软件工程 }& \text{ 河北保定 }\\ 2& 20160203& \text{ 张顺峰 }& \text{ 男 }& 22& \text{ 软件工程 }& \text{ 河南洛阳 }\\ 3& 20160303& \text{ 林宇珊 }& \text{ 女 }& 21& \text{ 网络工程 }& \text{ 湖北襄阳 }\end{array}$

该查询也可以用集函数MAX实现。

SELECT  *
FROM   STUDENT
     WHERE Smajor<>'计算机科学与技术' AND Sage >
	( SELECT max(Sage)
	  FROM Student
	  WHERE Smajor='计算机科学与技术'  )

ANY、ALL谓词与集函数及IN谓词的等价转换关系

$\begin{array}{lcccccc}& =& <>\text{ 或! }& <& <=& >& >=\\ \text{ ANY }& \text{ IN }& --& <\text{ MAX }& <=\text{ MAX }& >\text{ MIN }& >=\text{ MIN }\\ \text{ ALL }& --& \text{ NOT IN }& <\text{ MIN }& <=\text{ MIN }& >\text{ MAX }& >=\text{ MAX }\end{array}$

用集函数实现子查询通常比直接用any或all查询效率要高，因为前者通常能够减少比较次数。

例37：查询其他专业比“软件工程”专业的某个学生年龄大的学生的信息。

SELECT *
FROM Student
WHERE Smajor<>'软件工程' AND Sage > any 
	( SELECT Sage
	  FROM Student
	  WHERE Smajor='软件工程'  )

$\begin{array}{lllllll}& \text{ Sno }& \text{ Sname }& \text{ Ssex }& \text{ Sage }& \text{ Smajor }& \text{ Shometown }\\ 1& 20160101& \text{ 徐成波 }& \text{ 男 }& 20& \text{ 计算机科学与技术 }& \text{ 广东广州 }\\ 2& 20160302& \text{ 叶剑峰 }& \text{ 男 }& 20& \text{ 网络工程 }& \text{ 陕西西安 }\\ 3& 20160303& \text{ 林宇珊 }& \text{ 女 }& 21& \text{ 网络工程 }& \text{ 湖北襄阳 }\\ 4& 20160304& \text{ 吴妍娴 }& \text{ 女 }& 20& \text{ 网络工程 }& \text{ 浙江诸暨 }\end{array}$

该查询也可以用集函数MIN实现。

SELECT *
FROM Student
WHERE Smajor<>'软件工程' AND Sage > 
	( SELECT MIN(Sage)
	  FROM Student
	  WHERE Smajor='软件工程'  )

🕘 4.6 带有EXISTS谓词的子查询

• EXISTS谓词
  • 存在量词$\exists$
  • 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。
    • 若内层查询结果非空，则外层的WHERE子句返回真值
    • 若内层查询结果为空，则外层的WHERE子句返回假值
  • 由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。
• NOT EXISTS谓词
  • 若内层查询结果非空，则外层的WHERE子句返回假值
  • 若内层查询结果为空，则外层的WHERE子句返回真值

例38：查询选修了“操作系统”的学生的学号、姓名、专业。
思路分析：本查询涉及Student、Course和SC关系。在Student中依次取每个元组的“学号”Sno值，用该值去检查SC和Course关系，看该学生是否选修了Cname为“操作系统”的课程。

方法一：连接运算

SELECT  Student.Sno,Sname,Smajor
From Student , Course,SC
Where Cname='操作系统' AND 
	SC.Cno=Course.Cno  AND
	Student.Sno=SC.Sno

方法二：不相关子查询

Select sno,sname,smajor
From student 
Where sno in
	(Select sno From sc
	Where cno in
		(Select cno	From course
	Where cname='操作系统')
	)

方法三：相关子查询

SELECT Sno, Sname, Smajor                                  
FROM Student
WHERE EXISTS
( SELECT *  FROM  SC,Course
 WHERE SC.Cno = Course.Cno  AND
          Cname='操作系统'   AND  Sno = Student. Sno )

分析：在Student中依次取每个元组的Sno值，用此值去检查SC表
若SC中存在这样的元组，其Sno值等于此Student.Sno值，并通过SC.Cno = Course.Cno 两表连接操作找到Cname=‘操作系统’，则取此Student.Sno, Sname, Smajor送入结果表

$\begin{array}{llll}& \text{ Sno }& \text{ Sname }& \text{ Smajor }\\ 1& 20160303& \text{ 林宇珊 }& \text{ 网络工程 }\end{array}$

小结：

• EXISTS：当子查询中有满足条件的数据时，EXISTS返回真，否则返回假；
• NOT EXISTS：当子查询中不存在满足条件的数据时，NOT EXISTS返回真；当子查询中有（至少存在一个）满足条件的数据时，NOT EXISTS返回假；

例39：查询没有选修“操作系统”的学生的学号、姓名、专业。

SELECT Sno, Sname, Smajor
FROM Student
WHERE  NOT  EXISTS 
(SELECT *
 FROM SC,Course
 WHERE SC.Cno = Course.Cno  AND
         Cname='操作系统'   AND  SC.Sno = Student. Sno )   

$\begin{array}{llll}& \text{ Sno }& \text{ Sname }& \text{ Smajor }\\ 1& 20160101& \text{ 徐成波 }& \text{ 计算机科学与技术 }\\ 2& 20160102& \text{ 黄晓君 }& \text{ 计算机科学与技术 }\\ 3& 20160103& \text{ 林宇珊 }& \text{ 计算机科学与技术 }\\ 4& 20160104& \text{ 张茜 }& \text{ 计算机科学与技术 }\\ 5& 20160201& \text{ 黄晓君 }& \text{ 软件工程 }\\ 6& 20160202& \text{ 陈金燕 }& \text{ 软件工程 }\\ 7& 20160203& \text{ 张顺峰 }& \text{ 软件工程 }\\ 8& 20160204& \text{ 洪铭勇 }& \text{ 软件工程 }\\ 9& 20160301& \text{ 朱伟东 }& \text{ 网络工程 }\\ 10& 20160302& \text{ 叶剑峰 }& \text{ 网络工程 }\\ 11& 20160304& \text{ 吴妍娴 }& \text{ 网络工程 }\end{array}$

此例用连接运算很难实现，详见修改4

例39修改1：

SELECT Sno, Sname, Smajor
FROM Student
WHERE  EXISTS 
(SELECT *
 FROM SC,Course
 WHERE SC.Cno = Course.Cno  AND
     Cname！='操作系统'   AND  SC.Sno = Student. Sno ) /*正确吗？*/

分析：错误，既包含了没有选修“操作系统”的学生；又包含既选修“操作系统”，又选修了其他课程的学生！
思考：没有选修任何课程的同学未出现在修改1的结果集中，为什么？
因为没有选修任何课程，所以内层查询结果为空，故逻辑返回“假”，不会出现在结果集中。

例39修改2：正确

SELECT Sno, Sname, Smajor
FROM Student
WHERE Sno NOT IN 
(SELECT Sno
 FROM SC,Course
 WHERE SC.Cno = Course.Cno
     AND   Cname='操作系统' )

SELECT Sno, Sname, Smajor
FROM Student
WHERE Sno  NOT IN 
 ( SELECT Sno
   FROM SC
   WHERE Cno IN 
        ( SELECT Cno
          FROM Course
          WHERE Cname='操作系统' )  )

例39修改3：错误

SELECT Sno, Sname, Smajor
FROM Student
WHERE Sno  IN 
(SELECT Sno
 FROM SC,Course
 WHERE SC.Cno = Course.Cno
  AND  Cname!='操作系统')

SELECT Sno, Sname, Smajor
FROM Student
WHERE Sno  IN 
 ( SELECT Sno
   FROM SC
   WHERE Cno IN 
	( SELECT Cno
	  FROM Course
	  WHERE Cname!= '操作系统' )  )

分析：会漏掉什么课都没选的学生

例39修改4：错误

SELECT Sno, Sname, Smajor
FROM Student,Course,SC
WHERE SC.Cno = Course.Cno  
AND SC.Sno = Student.Sno AND Cname != '操作系统'

通常情况下，对于否定条件的查询都应该使用子查询来实现，而且要将否定放在外层！

不同形式的查询间的替换，遵循以下原则：

• 一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换，但有时可以，且有时也可用连接运算替换；
• 所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换 。

关于相关子查询的效率：

• 由于带EXISTS谓词的相关子查询只关心内层查询是否有返回值，并不需要查具体的值，因此效率并不一定低于其他形式的查询。

EXISTS/NOT EXISTS谓词除了代表存在量词$\exists$外，还可以实现全称量词$\forall$（For all）和逻辑蕴含：
（1）用EXISTS/NOT EXISTS实现全称量词（难点）

• SQL语言中没有全称量词$\forall$ （For all）
• 可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：$(\forall \mathbf{x})\mathbf{P}\equiv \neg (\exists \mathbf{x}(\neg \mathbf{P}))$

（2）用EXISTS/NOT EXISTS实现逻辑蕴含（难点）

• SQL语言中没有蕴含(Implication)逻辑运算
• 可以利用谓词演算将逻辑蕴含谓词等价转换为：$\mathbf{p}\to \mathbf{q}\equiv \neg \mathbf{p}\vee \mathbf{q}$

例40：查询选修了全部课程的学生的姓名（Sname）、专业名（Smajor）。
思路分析：本查询的含义是，查询这样的学生，没有一门课是他没有选的。其SQL语句为：

SELECT Sname, Smajor
FROM   STUDENT
WHERE  NOT EXISTS  
(SELECT * FROM   Course		/*不存在这样的课程*/
 WHERE  NOT EXISTS
     (SELECT  * FROM   SC
      WHERE Sno= Student.Sno AND Cno=Course.Cno))	/*该学生没有选修的记录*/

例41：查询至少选修了学号为20160201的学生所选修的全部课程的学生的学号。
思路分析：本查询的含义是，查询学号为x的学生，对于所有的课程，只要20160201学生选修了y，则x也选修了y。
对于课程y， 用p表示谓词“学生20160201选修了课程y”，用q表示谓词“学生选修了课程y”，则上述查询可以表述为：
$(\forall \mathbf{y})\mathbf{p}\to \mathbf{q}$
根据存在量词和全称量词之间的等价变换及谓词演算可得：
$\begin{array}{cc}(\forall y)p\to q& \equiv \neg (\exists y(\neg (p\to q))\\ & \equiv \neg (\exists y(\neg (\neg p\vee q)))\\ & \equiv \neg \exists y(p\wedge \neg q)\end{array}$

变换后语义：不存在这样的课程y，学生20160201选了，而学生x没有选。用NOT EXISTS谓词表示为：

SELECT DISTINCT Sno
FROM  SC  SC1
WHERE NOT EXISTS
(SELECT  *
 FROM  SC  SC2
 WHERE Sno = '20160201'  AND  NOT EXISTS
 (SELECT * FROM SC  SC3 
  WHERE SC3.Sno=SC1.Sno AND SC3.cno=SC2.cno));

$\begin{array}{ll}& \text{ Sno }\\ 1& 20160102\\ 2& 20160201\\ 3& 20160204\\ 4& 20160303\end{array}$

🕒 5. 课后习题

1. 【单选题】现有学生表Student、课程表Course和学生选课表SC，它们的结构如下：
   Student（Sno, Sname, SEX, AGE, DEPT）
   Course（Cno, Cname，TEACHER）
   SC（Sno, Cno, GRADE）
   其中：Sno为学号，Sname为姓名，SEX为性别，AGE为年龄，DEPT为系别，Cno为课程号，Cname为课程名，GRADE为成绩。
   要查找平均分高于90分的学生的学号和平均分的语句为（ ）
   A.select sno，count(grade) from sc group by sno having count(grade)>90
   B.select sno，avg(grade) from sc group by sno having avg(grade)>90
   C.select sno，avg(grade) from sc where avg(grade)>90 group by sno
   D.select sno，avg(grade) from sc group by cno having avg(grade)>90

2. 【单选题】在SQL的SELECT语句中，与关系代数中的选择运算对应的命令动词是（ ）。
   A、SELECT
   B、FROM
   C、WHERE
   D、ORDER BY

3. 【单选题】有关系Student (sno,sname,sage)，下列关于空值的查询语句中，不能产生正确结果的是（ ）。
   A、SELECT sname FROM R WHERE age IS NULL
   B、SELECT sname FROM R WHERE NOT(age IS NULL)
   C、SELECT sname FROM R WHERE age=NULL
   D、SELECT sname FROM R WHERE age IS NOT NULL

4. 【判断题】SELECT子句中的目标列可以是表中的属性列，也可以是表达式。（ ）

5. 【判断题】带有Exists谓词的嵌套查询的查询效率不一定比其他查询的查询效率低。（ ）

6. 【判断题】使用ANY或ALL谓词时必须与比较运算符同时使用。（ ）

7. 【判断题】在SQL语句中表达某个属性X为空，可以使用WHERE X=NULL。（ ）

8. 【判断题】SQL语句中逻辑运算符AND和OR的优先级是一样的。

---

答案：1.B（解析：平均分高于90分是分组后的条件） 2.C 3.C 4.√ 5.√ 6.√（解析：比较运算符就是>=<这些） 7.×（解析：应该是is NULL） 8.×

---

OK，以上就是本期知识点“数据查询SELECT”的知识啦~~ ，感谢友友们的阅读。后续还会继续更新，欢迎持续关注哟📌~
💫如果有错误❌，欢迎批评指正呀👀~让我们一起相互进步🚀
🎉如果觉得收获满满，可以点点赞👍支持一下哟~

❗ 转载请注明出处
作者：HinsCoder
博客链接：🔎 作者博客主页