04 数据查询

4 数据查询
⏹三种运算（选择、投影、连接）⏹数据查询（SELECT语句）
选择(Selection)
选择是单目运算，该运算按给定的条件，从表中选出满足条件的行形成一个新表作为运算结果(结果集）。

【例4.1】学生情况表如表4.1所示。

学号姓名专业名性别出生时间总学分备注001101王林计算机男1980-02-1050
001102程明计算机男1981-02-0150
001103王燕计算机女1979-10-0650
给定条件：性别=“女”，选择运算的结果如表4.2所示。

学号姓名专业名性别出生时间总学分备注001103王燕计算机女1979-10-0650
投影(Projection)
投影也是单目运算，该运算从表中选出指定的属性值组成一个新表。

【例4.2】若在表4.1中对学号、姓名和总学分投影，运算结果如表4.3所示。

学号姓名总学分
001101王林50
001102程明50
001103王燕50
连接(JOIN)
连接是把两个表中的行按照给定的条件进行拼接而形成新表，记为：R∞S
T1T2T3T4T51A 13M 2
B
2
N
T3
T4
T5
13M 2
N
T1T21
A
6F 2
B
F ：T1=T3
⏹
两个表连接常用的条件是两个表的某些列值相等，这种连接就称为“等值连接”
⏹
如果参与连接的两个表有共同的属性，则连接操作的结果是在两个表的共同属性上进行等值连接后再去除重复的属性后得到的新表。

这种连接方式称为“自然连接”。

T1T4T513M 2
N
T1T21A 6F 2
B
T1T2T4T51A 3M 2
B
N
SELECT语句
⏹如果希望查看表中的数据，可以使用SELECT语句来完成任务。

SELECT语句有三个基本组成部分：SELECT子句、FROM子句和WHERE子句。

⏹其一般格式为：
SELECT[ALL|DISTINCT]<目标列表达式>[，<目标列表达式>]…
FROM<表名或视图名>[,<表名或视图名>]…
[WHERE<条件表达式>]
[GROUP BY<列名1>[HAVING<条件表达式>]]
[ORDER BY<列名2>[ASC|DESC]]
SELECT子句
⏹查询系统当前日期。

⏹查询圆周率常数及其正弦、余弦值。

⏹查询Student表中的所有数据。

⏹查询全体学生的姓名及其出生日期。

⏹查询全体学生的姓名及其年龄。

⏹查询选修了课程的学生学号。

SELECT子句
⏹选择所有列*
⏹选择指定的列:列名
⏹指定别名：A AS B;B=A
⏹消除重复行：ALL/DISTINCT ⏹计算列
⏹使用文本串
条件查询（where子句，选择行）
1.确定查询（表达式比较）
⏹确定查询指的是使用比较运算符、列表、合并以及取反等
运算进行的条件查询。

⏹比较运算符是搜索条件中最常用的。

用于比较的运算符一
般包括：=（等于），>（大于），<（小于），>=（大于等于），<=（小于等于），!=或<>（不等于）。

⏹查询Student表中所有年龄大于20岁的学生信息。

⏹查询所有计算机系的学生姓名和学号。

⏹思考：如果想查询所有非计算机学院的学生名单呢？
⏹查询Student表中男生或者年龄大于20岁的学生姓名和年龄。

⏹查询Student表中年龄小于20岁的女生姓名、年龄及所属院
系。

2.模糊查询
⏹通常在查询字符数据时，提供的查询条件并不是十分准
确，例如，查询仅仅是包含或类似某种样式的字符，这种查询称为模糊查询。

⏹在WHERE子句中使用LIKE关键字可以实现这种灵活的
查询。

⏹LIKE关键字用于搜索与特定字符串匹配的字符数据。

LIKE关键字后面可以跟一个列值的一部分而不是完整的列值。

其基本语法形式为：
[NOT]LIKE'匹配字符串'[ESCAPE'<转义字符>']
⏹如果LIKE关键字前面有NOT关键字，表示该条件取反。

⏹ESCAPE子句用于指定转义字符，表示该字符后的符号为普
通符号，而不再作为通配符处理。

匹配字符串可以是一个完整的字符串，也可以包含通配符％、_、[]、[^]，这四种通配符的含义如表所示。

通配符含义
％代表任意长度（长度可以为0）的字符串
_代表任意单个字符
[ ]指定范围或集合中的任意单个字符
[^]不在指定范围或集合中的任意单个字符
需要强调的是，带有通配符的字符串必须使用单引号引起来。

下面是一些带有通配符的示例：
LIKE 'AB%' LIKE '%ABC' LIKE '%ABC%' LIKE '_AB'
LIKE ‘[ACE]%’LIKE '[A-
Z]ing‘
LIKE 'L[^a]%'返回以“AB”开始的任意字符串。

返回以“ABC”结束的任意字符串。

返回包含“ABC”的任意字符串。

返回以“AB”结束的3个字符的字符串。

返回以“A”,“C”,“E”开始的任意字符串。

返回4个字符长的字符串，结尾是“ing”，第1个字符的范围是从A到Z。

返回以“L”开始、第2个字符不是“a”的任意字符串。

练习
⏹查询所有姓李的学生学号、姓名和性别。

⏹查询所有不姓李的学生姓名和学号。

⏹查询姓名中第二个字为“林”的学生姓名和学
号。

⏹查询所有姓“王”且单名的学生情况。

⏹查询课程名以DB_开头的课程信息。

3.范围比较
⏹谓词BETWEEN…AND…和IN可以用来查找属
性值在或不在指定范围内的元组。

其中，BETWEEN后是范围的下限，AND后是范围的上限。

⏹查询年龄在19～21之间的学生姓名、年龄和所属院
系。

⏹查询年龄不在19～21之间的学生姓名、年龄和所属
院系。

⏹查询所有不在1989年出生的学生情况。

⏹查询专业为计算机、通信工程的所有学生信息。

练习
在Pubs数据库中完成以下查询：
⏹查找employee表中雇佣日期在1990-1992年之间的员
工姓名。

⏹查找authors表中所有区号为415的电话号码
⏹查找authors表中名字为Cheryl或Sheryl的作者
⏹查找姓为Carson、Carsen、Karson或Karsen的作
者所在的行
4. 空值比较
⏹IS NULL/IS NOT NULL用于判定一个表达式
的值是否为空值。

⏹查询SC表中成绩为空的学生学号。

⏹在S Q L 语言中，一个S E L E C T …F R O M …
WHERE …语句称为一个查询块。

将一个查询块嵌套在另一个查询块的W H E R E 子句或HAVING 短语的条件中的查询称为嵌套查询。

⏹SELECT Sname ⏹
FROM Student ⏹WHERE Sno IN
⏹(SELECT Sno
⏹FROM SC
⏹WHERE Cno ='102'
)外层查询/
父查询内层查询/子查询
嵌套查询（子查询）
⏹SQL语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。

⏹子查询通常与IN，EXISTS，ANY，ALL及比较运算符结合使用。

⏹包括子查询的语句通常采用以下格式中的一种：
⏹WHERE expression[NOT]IN(subquery)
⏹WHERE expression comparison_operator[ANY|
ALL](subquery)
⏹WHERE[NOT]EXISTS(subquery)
①带IN的嵌套查询
⏹在嵌套查询中，子查询的结果往往是一个集合，所以谓词
IN是嵌套查询中最常使用的谓词。

其主要使用方式为：WHERE<条件表达式>
IN（子查询）
⏹查询与“王敏”在同一个院系学习的学生信息。

⏹查询选修了101号课程的学生姓名。

⏹查询选修了课程名为“计算机基础”的学生学号和姓
名。

⏹查找由位于字母B开头的城市的任一出版商出版的书
名。

⏹注意：IN和NOT IN的子查询只能返回一列数据。

②带比较运算符的嵌套查询
⏹带有比较运算符的子查询是指父查询与子查询之
间用比较运算符进行连接。

当用户能确切知道内层查询返回的是单值时，可以用=、>、<、>=、<=、!=或<>等比较运算符。

⏹查询与“王敏”在同一个院系学习的学生信息。

⏹找出每个学生超过他选修课程平均成绩的课程号。

③带ANY或ALL的嵌套查询
子查询返回单值时，可以用比较运算符，但返回多值时，要用ANY或ALL谓词修饰符。

而使用ANY或ALL谓词时，必须同时使用比较运算符。

>ANY大于子查询结果中的某个值
>ALL 大于子查询结果中的所有值
<ANY小于子查询结果中的某个值
<ALL小于子查询结果中的所有值
>=ANY大于等于子查询结果中的某个值
>=ALL大于等于子查询结果中的所有值
<=ANY小于等于子查询结果中的某个值
<=ALL小于等于子查询结果中的所有值
=ANY等于子查询结果中的某个值
=ALL等于子查询结果中的所有值（通常没有实际意义）
!=(或< >)ANY不等于子查询结果中的某个值
⏹查询其他系中比计算机系某一学生年龄小的学生姓名、
年龄和所属院系。

⏹查询其他系中比计算机系所有学生年龄都小的学生姓名、
年龄和所属院系。

⏹查找课程号101的成绩高于课程号101的最低成绩的学生
学号。

⏹查询与出版商在同一城市居住的作者姓名。

⏹查找所有价格高于当前最低价的书籍的名称。

⏹查找在没有出版商的城市中居住的作者。

④带EXISTS的嵌套查询
⏹EXISTS代表存在量词。

带有EXISTS谓词的子
查询不返回任何数据，只产生逻辑真值“true”
或逻辑假值“false”。

⏹使用存在量词EXISTS后，若内层查询结果非
空(有记录)，则外层的WHERE子句返回真值，
外层查询输出结果，否则，返回假值，不输出
结果。

⏹查询所有选修了101号课程的学生姓名。

⏹查找与出版商住在同一城市中的作者。

相关子查询
⏹如果子查询的查询条件依赖于外层父查询的属性值，则该查询称为相关子查询。

⏹求解相关子查询不能像求解不相关子查询那样，一次将子查询求解出来，由于内层查询与外层查询相关，因此必须反复求值。

⏹从概念上讲，相关子查询的一般处理过程是：
⏹首先取外层查询表（Student）中的第一个元组，根据
它与内层查询相关的属性值（Sno值）处理内层查询，如果WHERE子句返回值为真，则取外层查询中该元组放入结果表；
⏹然后再取外层查询表（Student）中的下一个元组；重
复这一过程，直到外层查询表全部检查完为止。

⏹注意：
⏹EXISTS关键字前面没有列名、常量或其它表达式。

⏹由EXISTS引入的子查询的选择列表通常几乎都是由
星号(*)组成。

由于只是测试是否存在符合子查询中
指定条件的行，所以不必列出列名。

⏹与EXISTS谓词相对应的是NOT EXISTS谓词。

⏹使用两次NOT EXISTS后，若内层查询结果为空，则外层的WHERE子句返回真值，否则返回假值。

即双重否定等于肯定。

⏹查询所有没有选修101号课程的学生姓名。

⏹查询选修所有全部课程的学生姓名。

⏹查找所有出版商业书籍的出版商的名称。

⏹一些使用EXISTS表示的查询不能以任何其它方法表示，但所有使用IN或由ANY或ALL修改的比较运算符的查询都可以通过EXISTS表示。

⏹查询与“李明”在同一个院系学习的学生信息。

⏹找到与出版商住在同一城市中的作者姓名。

⏹查找由位于字母B开头的城市中的任一出版商出版的书
名。

⏹SELECT查询语句的结果集往往是一个包含了多行数据（元组）的集合。

在数学领域中，集合之间可以进行并、交、差等运算。

⏹在Microsoft SQL Server2005中，两个查询语句之间也可以进行集合运算，其中主要包括并操作UNION、交操作INTERSECT和差操作EXCEPT。

⏹需要注意的是，在进行集合运算时，所有查询语句中的列的数量和顺序必须相同，且数据类型必须兼容。

⏹并操作UNION：将多个SELECT查询的结果合并成一个结果集。

⏹语法：<子查询>UNION[ALL]<子查询>……
⏹交操作INTERSECT
⏹差操作EXCEPT
⏹查询计算机系和通信工程系的学生信息。

⏹查询既选修了101号课程又选修了102号课程的学生
学号。

⏹查询没有选修课程的学生姓名。

连接
⏹在设计表时，为了提高表的设计质量，经常把相关的数据分散在不同的表中。

但是，在实际使用时，往往需要同时从两个或两个以上表中检索数据，并且每一个表中的数据往往仍以单独的列出现在结果集中。

⏹实现从两个或两个以上表中检索数据且结果集中出现的列来自于两个或两个以上表中的检索操作称为连接技术。

⏹连接查询是关系数据库中最主要的查询，包括内连接、外连接、交叉连接三种。

⏹连接可以在S E L E C T语句的F R O M子句或WHERE子句中建立。

⏹在FROM子句中指出连接有助于将连接操作与WHERE子句中的搜索条件区分开来。

所以，在Transact-SQL中推荐使用这种方法。

⏹在FROM子句中指定连接条件的语法格式为：
SELECT<目标列表达式>
FROM<表1>连接类型<表2>[ON(连接条件)]……
⏹其中连接类型可以是交叉连接(CROSS JOIN)、内连
接(INNER JOIN)、外连接(OUTER JOIN)；
⏹ON子句指出连接条件，它由被连接表中的列和比较
运算符、逻辑运算符等构成。

⏹在WHERE子句中指定连接条件的基本格式为：
SELECT<目标列表达式>
FROM<表1>,<表2>……
[WHERE(连接条件)]
1.交叉连接
⏹交叉连接也称为笛卡儿乘积，它返回两个表中
所有数据行的全部组合，即交叉连接结果集中的数据行数等于第一个表中的数据行数乘以第二个表中的数据行数。

⏹交叉连接使用关键字CROSS JOIN来创建，并
且不带WHERE子句。

⏹列出学生所有可能的选课情况。

⏹在实际的应用中，交叉连接的使用是比较少的。

2.内连接
⏹内连接（INNER JOIN）使用比较运算符进行表
间某（些）列数据的比较操作，并列出这些表中与连接条件相匹配的数据行。

根据所使用的比较方式不同，内连接又分为等值/不等值连接、自然连接和自连接三种。

⏹内连接可以连接多张表。

（1）等值与非等值连接查询
⏹连接查询中用来连接两个表的条件称为连接条件或连接
谓词，它的一般格式为：
表名1.列名1比较运算符表名2.列名2
⏹可以使用的比较运算符有：>、>、=、<、<=、!=（或
<>），还可以使用BETWEEN…AND…之类的谓词。

⏹当连接运算符为等号(=)时，称为等值连接。

而使用其他
比较运算符就构成了非等值连接。

⏹查询每个学生及其选修课程的情况。

⏹查询每个学生及选修课程成绩大于80分的信息。

⏹查询选修了’计算机基础’且成绩在70分以上的学生
信息。

⏹查询在同一城市的出版社和作者信息。

系统执行该连接操作的一种可能过程：
⏹首先，在Student表中找到第一个元组，然后从头开始扫
描SC表，逐一查找与Student第一个元组的Sno相等的SC元组；
⏹找到后就将Student中的第一个元组与该元组拼接起来，
形成结果表中的一个元组；
⏹SC全部查找完后，再找Student中的第二个元组，然后
再从头开始扫描SC表，逐一查找满足连接条件的元组，找到后就将Student中的第二个元组与该元组拼接起来，形成结果表中的一个元组。

⏹重复以上操作，直到Student表中的全部元组都处理完毕。

（2）自然连接查询
⏹如果在进行等值连接时目标列不使用“*”而使
用选择列，从而把结果集中重复的属性列去掉，就成了自然连接。

⏹查询选修了101号课程且成绩大于80分的学生姓名、
学号。

⏹查询选修了“计算机基础”课程且成绩在80分以上的
学生学号、姓名、课程名及成绩。

⏹查询在同一城市的出版社和作者名称。

（3）自连接查询
⏹连接不仅可以在表之间进行，也可以使一个表同
其自身进行连接，这种连接称为自连接（Self Join），相应的查询称为自连接查询。

⏹使用自连接时，需要为表指定两个别名。

⏹查找不同课程成绩相同的学生的学号、课程号和成绩。

⏹查找每一门课程的间接先修课。

⏹查找每位员工以及该员工的潜在领导（工作级别高）
姓名。

⏹查找同一本书有多个作者的书号、书名和作者信息。

3.外连接查询
⏹在内连接操作中，只有满足连接条件的元组才能
作为结果输出，比如查询学生的选课信息，那么没有选课的学生信息就不会在结果中出现。

⏹但是有时需要以Student表为主体列出每个学生
的基本情况及其选课情况，若某个学生没有选课，则只输出其基本情况信息，其选课信息为空值即可，这时可以使用外连接（OUTER JOIN）。

⏹外连接只能连接两张表。

外连接
⏹可以使用3种外连接关键字，即LEFT OUTER JOIN，RIGHT OUTER JOIN和FULL OUTER JOIN。

⏹LEFT OUTER JOIN表示左外连接，结果集中将包含
满足搜索条件的所有数据和第一个连接表中不满足条件的数据（对应第二个表中的数据为NULL）。

⏹RIGHT OUTER JOIN表示右外连接，结果集中将包
含满足搜索条件的所有数据和第二个连接表中不满足条件的数据（对应第一个表中的数据为NULL）。

⏹FULL OUTER JOIN表示全外连接，它综合了左外连
接和右外连接的特点，把两个表中不满足条件的数据集中起来出现在结果集中，这些数据在另外一个表中的对应值是NULL。

⏹查询所有学生选修课程的情况，包括没有选修
课程的学生。

⏹查询所有书籍的销售量信息。

⏹查询销售量大于50本的书籍及其销售量。

排序（ORDER BY子句）
⏹使用ORDER BY子句可以按一个或多个属性对数据进行排序。

⏹基本格式：
⏹ORDER BY排序表达式[ASC|DESC]
⏹其中，排序表达式可以是列名、表达式或者正整数
⏹ASC表示升序排列（默认），DESC表示降序排列
⏹练习
⏹按照出生时间先后输出所有学生信息。

⏹按照院系输出所有学生信息。

⏹按照出生时间先后输出计算机专业学生信息。

⏹将计算机专业学生的“计算机基础”成绩按降序输出，
成绩相等的按照学号先后输出。

显示部分记录
⏹可以TOP关键字表示仅在结果集中从前向后列出指定数量的数据行。

⏹使用TOP关键字的基本语法有两种：
⏹TOP(n)[WITH TIES]：从前向后返回n行数据
⏹TOP(n)PERCENT[WITH TIES]：按照百分比返回
指定数量的数据行
⏹查询班内前5个学生的信息。

⏹找出101号课程成绩排名前三的学生学号及其成绩。

⏹查询选课成绩排名在前30％的学生学号、姓名和成绩。

统计函数（聚合函数）
●计数
⏹COUNT([DISTINCT|ALL]*)
⏹COUNT([DISTINCT|ALL]列名)
●计算总和
⏹SUM([DISTINCT|ALL]表达式)
●计算平均值
⏹AVG([DISTINCT|ALL]表达式)
●求最大值
⏹MAX([DISTINCT|ALL]表达式)
●求最小值
⏹MIN([DISTINCT|ALL]表达式)
⏹除COUNT（*）函数之外，聚合函数忽略空值
⏹查询学生总人数。

⏹查询选修了课程的学生人数。

⏹查询计算机系的学生总人数。

⏹求学生选修101课程的平均成绩。

⏹查询选修101号课程的学生最高分和最低分。

⏹查询“计算机基础”课程成绩在85分以上的人
数。

⏹查询商务书籍的平均价格。

分组（GROUP BY子句）
⏹将查询结果按属性分组，属性值相等的为一组。

⏹这样做的目的是为了细化统计函数的作用对象，如果未对查询结果分组，集函数将作用于整个查询结果；而对查询结果分组后，集函数将分别作用于每个组。

⏹基本格式：
GROUP BY[ALL]表达式1，表达式2，……
[WITH{CUBE|ROLLUP}]
⏹CUBE/ROLLUP与聚合函数一起使用，在查询结果中
增加附加记录。

⏹不能用CUBE或ROLLUP运算符指定ALL。

⏹统计各个专业的学生人数。

⏹查询各门课程的平均成绩与选课人数。

⏹查询每个学生的平均成绩。

⏹统计各专业的男生、女生人数。

⏹统计各专业的男生、女生人数及各专业总人数。

⏹统计各专业的男生人数、女生人数、专业总人数及男生
总数、女生总数、学生总人数。

⏹查询各专业每门课程的平均成绩及总平均成绩。

⏹对版税(royalty)为10%的书籍分类显示其平均价格。

注意
⏹GROUP BY子句的作用对象是查询的中间结果表，它按照指定的一列或多列值进行分组，值相等的为一组。

因此，使用GROUP BY子句后，SELECT子句的列名列表中只能出现分组属性和集函数。

⏹GROUP BY子句可以包含表达式。

HAVING子句
⏹如果分组后还要求按一定的条件对这些组进行
筛选，最终只输出满足指定条件的组，则可以使用HAVING短语指定筛选条件。

⏹查询选修了两门或两门以上课程的学生学号。

⏹查询平均成绩在85分以上的学生学号和平均成绩。

⏹查询选修课程不低于2门且成绩都在80分以上的学生
学号。

⏹查找通信工程专业平均成绩在85分以上的学生的学号
和平均成绩。

⏹注意：
⏹WHERE用于筛选由FROM指定的数据对象；
⏹HAVING用于过滤GROUP BY以后的分组数据。

SELECT …INTO …
⏹把SELECT查询的结果保存到一个新建的表中。

⏹基本格式为：
SELECT列列表INTO表名
[select子句]
⏹创建计算机专业学生信息表，包括学号、姓名。

⏹创建学生成绩表，包括学号、姓名、课程名、成绩。

⏹注意：
⏹SELECT INTO不支持GROUP BY,HAVING,CUBE和
ROLLUP语句。

⏹新表会在执行Select语句时自动创建（不用提前手工创
建）表结构由select语句中的列属性定义。