数据库技术与应用教程：实例化数据查询

SQL的查询语句可以分为简单查询、连接查询、嵌套查询和组合查询4种类型。下面仍以学生课程库为例，介绍各种查询的描述格式。

学生课程库包括3个基本表，其结构为：

学生（学号，姓名，年龄，所在系）；

课程（课程号，课程名，先行课）；

选课（学号，课程号，成绩）。

1.简单查询

简单查询是指在查询过程中只涉及一个表的查询语句。简单查询是最基本的查询语句。

【例4－3】求数学系学生的学号和姓名。

SELECT学号，姓名

FROM学生

WHERE所在系＝’数学系’；

解题说明：在表达查询时，第一步要确定查询的源表，源表可以为基本表或视图表。本例的源表是学生表；表达查询的第二步是确定元组选择要求和结果列的表达。本例中的元组选择条件是所在系等于“数学系”，结果列为学号和姓名。

【例4－4】求选修了课程的学生学号。

SELECT DⅠSTⅠNCT学号。

FROM选课；

解题说明：该题使用了DⅠSTⅠNCT操作符。由于每个学生一般都选修了多门课程，在选课表中对学号投影后就会出现重复的学号，而如果使用了DⅠSTⅠNCT操作符后，就可以使结果集中，并不出现重复学号。

【例4－5】求选修C1课程的学生学号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同，则按学号的升序排列。

SELECT学号，成绩

FROM选课

WHERE课程号＝’C1’

ORDER BY成绩DESC，学号ASC；

解题说明：该题使用了排序子句。其中，成绩为第一排序项，学号为第二排序项。

【例4－6】求选修课程C1且成绩为80～90的学生学号和成绩，并将成绩乘以系数0.8输出。

SELFCT学号，成绩*0.8

FROM选课

WHERE课程号＝’C1’AND成绩BETWEEN 80AND 90；

解题说明：该题有以下三处值得注意。

①在目标列中使用了表达式“成绩*0.8”，它将结果集中的每个成绩项都乘以系数0.8。

②在元组选择子句中使用了表达式“BETWEEN……AND”，它表示成绩为80～90。

③在元组子句中使用了AND操作符，它表示两边条件都要成立。

【例4－7】求数学系或计算机系姓张的学生的信息。

SELECT*

FROM学生

WHERE所在系ⅠN（’数学系’，’计算机系’）AND姓名LⅠKE’张%’；

解题说明：该题有三处值得注意。

①目标列使用*，表示选择学生表中的所有字段。

②使用了“所在系ⅠN（’数学系’，’计算机系’）”操作表达式，该表达式也可用“所在系＝’数学系’OR，所在系＝’计算机系’”来代替。

③使用了LⅠKE模式匹配表达式，“姓名LⅠKE”张%脚表示查询姓张的同学。

【例4－8】求缺少了成绩的学生的学号和课程号。

SELECT学号，课程号

FROM选课

WHERE成绩ⅠS NULL；

解题说明：该题使用了含有“ⅠS NULL”的操作表达式，它表示成绩为空。

2.连接查询

包含连接操作的查询语句称为连接查询。连接查询包括等值连接、自然连接、求笛卡儿积、一般连接、外连接、内连接、左连接、右连接和自连接等多种。由于连接查询涉及被连接和连接两个表，所以它的源表一般为多表。连接查询中的连接条件通过WHERE子句表达，连接条件和元组选择条件之间用AND。（与）操作符衔接。

（1）等值连接和非等值连接操作

连接查询中，用来连接两个表的条件称为连接条件或连接谓词，一般格式为：

[＜表名1＞.]＜列名1＞＜比较运算符＞[＜表名2＞.]＜列名2＞

其中比较运算符主要有：＝、＞、＜、＞＝、＜＝和！＝；连接谓词中的列名称为连接字段。连接条件中，连接字段类型必须是可比的，但连接字段不一定是同名的。

当连接运算符为“＝”时，该连接操作称为等值连接；否则，使用其他运算符的连接运算称为非等值连接。当等值连接中的连接字段相同，并且在SELECT子句中去除了重复字段时，则该连接操作为自然连接。

【例4－9】查询每个学生的情况以及他（她）所选修的课程。

SELECT学生.*，选课.*

FROM学生，选课

WHERE学生.学号＝选课.学号；

解题说明如下。

①该题的目标列中含学生表的全部属性和选课表的全部属性。

②由于目标列中有“学生、学号”和“选课、学号”两个相同的属性名的属性，故它的连接操作是等值连接。如果在SELECT子句中将重复属性去掉，该操作即为自然连接操作。

③连接操作的连接条件必须在WHERE子句中写出。如果使用了两个表查询，但WHERE子句中无连接条件，则结果为广义笛卡儿积操作结果。

【例4－10】求学生的学号、姓名、选修的课程名及成绩。

SELECT学生，学号，姓名，课程名，成绩

FROM学生，课程，选课

WHERE学生.学号＝选课.学号AND课程.课程号＝选课.课程号；

解题说明：该题有两个地方值得注意。

①在描述字段时，如果源表中有重复字段，需要用“＜表名＞.＜字段名＞”说明，即在字段前加表名限定。对于不重复的字段，可直接写字段名。

②该题用AND将两个连接条件结合，从而实现了3个表连接在一起的操作。

【例4－11】求选修C1课程且成绩为90分以上的学生学号、姓名及成绩。

SELECT学生.学号，姓名，成绩

FROM学生，选课

WHERE学生.学号＝选课.学号AND课程号＝’C1’AND成绩＞90；

解题说明：该题的WHERE子句中既有连接条件，也有元组选择条件，在表达时，应把连接条件放在前面。

（2）自身连接操作

连接操作不只是在两个表之间进行，一个表内也可以进行自身连接操作。表自身的连接操作称为自身连接。

【例4－12】查询每一门课的间接先行课（即先行课的先行课）。

例如，课程表中的先行课是在上学期应开设的，先行课的先行课（即间接先行课）应提前一学年开设。如果求查询某门课的间接先行课或全部课程的间接先行课，就需要对课程表进行自身连接。设课程表的数据如表4－3所示。

表4—3　课程表数据

由表4－3中的数据可以得出，课程的先行关系链为：C5→C4→C3→C2→C1，并可以推出课程的间接关系链为：C5→C3→C1。从间接关系链得出，要开软件工程课，在前一学年应开设数据结构课，而前两学年应开设计算机引论课。

SELECT A.课程号.A.课程名.B.先行课

FROM课程A.课程B

WHERE先行课＝B.课程号

解题说明如下。

①同一查询语句中，当一个表有两个作用时，需要对表起别名，应用中使用表的别名。例题中的A和B分别是课程的别名，A和B分别作为独立表使用。

②该题的连接条件是A表中的先行课与B表中的课程号等值连接，结果集中“B.先行课”为“A.课程号”的间接先行课字段。由于A和B都是课程的别名，该例是自连接操作。

③数据库系统在执行【例4－12】的自连接操作时，首先按别名形成两个独立表A和B，然后根据要求连接成结果表。

④表A、表B以及自连接的结果集，如表4－4所示。

表4—4　课程表自连接操作

表4—5　例4—12自连接操作的结果集

（3）外部连接操作

在前面的连接示例中，结果集中只保留了符合连接条件的元组，而排除了两个表中没有对应的或匹配的元组情况，这种连接称为内连接。如果要求查询结果集中保留非匹配的元组，就要执行外部连接操作。SQL的外部连接分左外部连接和右外部连接两种：左外部连接操作是在结果集中保留连接表达式左表中的非匹配记录；右外部连接操作是在结果集中保留连接表达式右表中的非匹配记录。SQL中左外部连接符号为“*＝”，右外部连接符号为“＝*”。外部连接中不匹配的分量用NULL表示。设有职工和部门两个基本表，数据如表4－6所示。

表4—6　职工和部门表数据

注：1011号职工刘晨因刚调入单位还没分配到具体部门，故对应所在部门为NULL值，而科研所因刚组建还没有人员。

【例4－13】用SQL表达职工和部门的内连接、左外部连接和右外部连接的语句分别如下。

内连接：

SELECT职工.*，部门名称，电话

FROM职工，部门

WHERE职工.所在部门＝部门.部门号；

左外部连接：

SELECT职工.*，部门名称，电话

FROM职工，部门

WHERE职工.所在部门*＝部门.部门号；

右外部连接：

SELECT职工.*，部门名称，电话

FROM职工，部门

WHERE职工.所在部门＝*部门.部门号；

对它们进行内连接、左外部连接和右外部连接会产生不同的结果集，如表4－7所示。

表4—7　职工和部门表各种连接的结果集对照表

3.嵌套查询

在SQL语言中，一个SELECT……FROM……WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVⅠNG短语的条件中的查询称为嵌套查询。

在书写嵌套查询语句时，总是从上层查询块（也称外层查询块）向下层查询块书写；而在处理时，则是由下层向上层处理，即下层查询结果集用于建立上层查询块的查找条件。

（1）使用ⅠN操作符的嵌套查询

当ⅠN操作符后的数据集需要通过查询得到时，就需要使用ⅠN嵌套查询。

【例4－14】求选修了高等数学的学生学号和姓名。

SELECT学号，姓名

FROM学生

WHERE学号ⅠN（SELECT学号

FROM选课

WHERE课程号ⅠN（SELECT课程号

FROM课程

WHERE课程名＝’高等数学’））；

解题说明：

①该题的执行步骤是：首先在课程表中求出高等数学课的课程号，然后根据找出的课程号在选课表中找出学了这个（些）课程的学号，最后根据学号在学生表中找出其姓名。

②该题使用了两层嵌套。

③该题也可以使用下面的连接查询表达。

SELECT学生.学号，姓名

FROM学生，课程，选课

WHERE学生.学号＝课程.学号AND课程.课程号＝选课.课程号

AND课程.课程名＝’高等数学’；

（2）使用比较符的嵌套查询

ⅠN操作符用于一个值与多值比较，而比较符则用于一个值与另一个值之间的比较。当比较符后面的值需要通过查询才能得到时，就需要使用比较符嵌套查询。

【例4－15】求C1课程的成绩高于张三的学生的学号和成绩。

SELECT学号，成绩

FROM选课

WHERE课程号＝’C1’AND成绩＞（SELECT成绩

FROM选课

WHERE课程号＝’C1’AND学号＝

（SELECT学号

FROM学生

WHERE姓名＝’张三’））；

解题说明：该题的执行顺序是首先在学生表中求出张三的学号，然后在选课中求出他的C1课成绩，最后在选课表中求出Cl课成绩大于张三的学生学号和成绩。该题使用了两层嵌套，第一层嵌套使用＞（大于）操作符，第二层嵌套使用＝（等于）操作符。

（3）使用ANY或ALL操作符的嵌套查询

使用ANY或ALL操作符时，必须与比较符配合使用，其格式为

＜字段＞＜比较符＞[ANY｜ALL]＜子查询＞

ANY和ALL与比较符结合及语义在表4－8中列出。(www.chuimin.cn)

表4—8　ANY和ALL与比较符结合及其语意表

续表

【例4－16】求其他系中比计算机系某一学生年龄小的学生（即求年龄小于计算机系年龄最大者的学生）。

SELECT*

FROM学生

WHERE年龄＜ANY（SELECT、年龄

FROM学生

WHERE所在系＝’计算机系’），AND所在系＜＞’计算机系’；

解题说明如下。

①该查询在处理时，首先处理子查询，找出计算机系的学生年龄，构成一个集合；然后处理父查询，找出年龄小于集合中的某一值且不在计算机系的学生。

②该例的子查询嵌套在WHERE选择条件中，子查询后又有“所在系＜＞’计算机系’”选择条件。SQL中允许表达式中嵌入查询语句。

【例4－17】求其他系中比计算机系的学生年龄都小的学生。

SELECT*

FROM学生

WHERE年龄＜ALL（SELECT年龄

FROM学生

WHERE所在系＝’计算机系’）.AND所在系＜＞’计算机系’；

解题说明：本题使用了＜ALL操作符.上例使用了＜ANY。读者可通过这两个例子来体会这两种操作符的不同之处

（4）使用EXⅠSTS操作符的嵌套查询

EXⅠSTS代表存在量词｜：EXⅠSTS操作符子查询的结果集中如果不为空，则产生逻辑真值“true”，否则产生假值“false”。

【例4－18】求选修了C2课程的学生姓名。

SELECT姓名

FROM学生

WHERE EXⅠSTS（SELECT*

FROM选课

WHERE学生.学号＝学号AND课程号＝’C2’）；

解题说明如下。

①本查询涉及学生和选课两个关系。在处理时，先从学生表中依次取每个元组的学号值；然后用此值去检查选课表中是否有该学号且课程号为C2的元组；若有，则子查询的WHERE条件为真，该学生元组中的姓名应在结果集中。

②在子查询的条件中，由于当前表为选课，故不需要用表名限定属性，而学生表（父查询中的源表）中的属性需要用表名限定。

③该查询也可以用下面的连接查询实现。

SELECT姓名

FROM学生，选课

WHERE学生.学号＝选课.学号AND课程号＝’C2’；

【例4－19】求没有选修C2课程的学生姓名。

SELECT姓名

FROM学生

WHERE NOT EXⅠSTS（SELECT*

FROM选课

WHERE学生.学号＝学号AND课程号＝’C2’）；

解题说明如下。

本题与上例不同之处在于如下几点：本例使用了NOT EXⅠSTS操作符，而上例使用的是EXⅠSTS操作符。由于WHERE子句中的条件是元组选择条件，所以上例可以使用连接查询表示，而本例不能使用下面的连接查询表示：

SELECT姓名

FROM学生，选课

WHERE学生.学号＝选课.学号AND课程号＜＞’C2’；

SQL语言可以把带有全称量词的谓词转换为等价的带有存在量词的谓词。

【例4－20】查询选修了全部课程的学生的姓名。

SELECT姓名

FROM学生

WHERE NOT EXⅠSTS（SELECT*

FROM课程

WHERE NOT EXⅠSTS

（SELECT*

FROM选课

WHERE学生.学号＝选课.学号AND课程.课程号＝课程号））；

解题说明如下。

由于SQL中没有全称量词的操作符，该题将意思转换为查询这样的学生：没有一门课他不选修。本例中使用了两个NOT EXⅠSTS操作符，其中第一个NOT EXⅠSTS表示不存在这样的课程记录，第二个NOT EXⅠSTS表示该生没有选修的选课记录。

【例4－21】求选修了学号为“S2”的学生所选修的全部课程的学生学号和姓名。

SELECT学号，姓名

FROM学生

WHERE NOT EXⅠSTS（SELECT*

FROM选课1

WHERE选课1.学号＝’S2’AND NOT EXⅠSTS

（SELECT*

FROM选课2

WHERE学生.学号＝选课2.学号

AND选课2.课程号＝选课1.课程号））；

解题说明如下。

①本例表达的是蕴含运算。它的意义是：查询学生X选修的课程Z和S2学生选修的课程Y，并要求Z中包括全部Y。

②与例4－20不同的是：上例的课程是课程表的全部课程，而本例的课程是选课表中学号为S2学生所选修的课程。

③本例使用了两次选课表，它们分别用别名“选课1”和“选课2”表示。

4.组合查询

将SELECT语句的查询结果集再进行集合运算就构成了SQL的组合查询。SQL的组合查询操作符有UNⅠON（并操作）、ⅠNTERSECT（交操作）和MⅠNUS（差操作）3种。

【例4－22】求选修了C1课程或选修了C2课程的学生学号。

SELECT学号

FROM选课

WHERE课程号＝’C1’

UNⅠON

SELECT学号

FROM选课

WHERE课程号＝’C2’；

解题说明如下。

①由于组合查询的整体是一个查询，故只能在最后一条语句的后面加结束符号“；”，而不能在每个分查询子句后加结束符号。

②组合查询中的每个分查询结果集的结构应一致。

③该题也可以用下面的OR操作查询代替。但查询结果有所不同。本题的结果为选修C1课程的学生学号在前，选修C2课程的学生学号在后；而下面查询结果为选修C1和C2课程的学号混合排列。

SELECT学号

FROM选课

WHERE课程号＝’C1’OR.课程号＝’C2’；

【例4－23】求选修C1课程，并且也选修C2课程的学生学号。

SELECT学号

FROM选课

WHERE课程号＝’C1’

ⅠNTERSECT

SELECT学号

FROM选课

WHERE课程号＝’C2’；

解题说明如下。

①本例先求出选修C1课程学生的学号，再求出选修C2课程的学生学号，最后将两者进行交运算，得到既选修C1课程又选修C2课程的学生学号。

②由于WHERE子句是元组选择子句，本例不能使用下面的AND操作查询表示：

SELECT学号

FROM选课

WHERE课程号＝’C1’AND课程号＝’C2’；

【例4－24】求选修了C1课程但没有选修C2课程的学生学号。

SELECT学号

FROM选课

WHERE课程号＝’C1’

MⅠNUS

SELECT学号‘

FROM选课

WHERE课程号＝’C2’；

解题说明：本例先求出选修了C1课程的学生学号集合X，再求出选修了C2课程的学生学号集合Y，最后通过差操作从X中减去Y，得到选修了C1课程但没有选修C2课程的学生学号。本例也可以用下面的EXⅠSTS嵌套查询表示。

SELECT学号

FROM选课1

WHERE课程号＝’C1’AND NOT EXⅠSTS

（SELECT学号

FROM选课2

WHERE选课1.学号＝选课2.学号AND选课2.课程号＝’C2’）；

5.使用分组和函数查询

SQL函数是综合信息的统计函数，包括计数、求最大值、最小值、平均值、和值等。SQL函数可作为列标识符出现在SELECT子句的目标列或HAVⅠNG子句的条件中。在SQL查询语句中，如果有GROUP BY分组子句，则语句中的函数为分组统计函数。如果没有GROUP BY分组子句，则语句中的函数为全部结果集的统计函数。基本的SQL函数及功能如表4－9所示。

表4—9　基本的SQL函数

【例4－25】求学生的总人数。

SELECT COUNT（*）

FROM学生；

解题说明：该题通过统计学生表的记录数求学生的总人数。

【例4－26】求选修了课程的学生人数。

SELECT COUNT（DⅠSTⅠNCT学号）

FROM选课；

解题说明：该题在学号前加DⅠSTⅠNCT，使得统计学号时重复的学号只计一次；如果学号前不加DⅠSTⅠNCT（为ALL），则重复的学号就会多次计数，假如一个学生选修了5门课程，就会统计5次，其意义就变成了求选课的人次数了。

【例4－27】求课程和选修该课程的人数。

SELECT课程号，COUNT（学号）

FROM选课

GROUP BY课程号；

解题说明如下。

①该题的查询过程分两步，先按课程号将选课记录分组，即同一课程号的选课记录分在一组中；再求出组内的学号数，即选修该课程的人数。

②如果该题中无GROUP BY子句，则COUNT（学号）的结果为全部记录的学号数。

【例4－28】求选修课超过3门课的学生学号。

SELECT学号

FROM选课

GROUP BY学号HAVⅠNG.COUNT（*）＞3；

解题说明：本例使用了HAVⅠNG子句，其语义为取组内记录大于3条的组。HAVⅠNG子句中的内容为组选择的条件，其子句的条件中必须有SQL函数。换句话讲，如果条件中有SQL函数.必须放在HAVⅠNG子句中，且HAVⅠNG子句跟在GROUP BY后。该例题不能用下面的方法表示：

SELECT学号

FROM选课

WHERE COUNT（*）＞3

GROUP BY学号；