《数据库原理及应用》课件第5章

第5章数据查询5.1SQL5.2SELECT语句5.3单表查询5.4连接查询5.5子查询

【技能目标】

学会根据实际问题的要求，灵活、快速地查询相关信息。

【知识目标】

掌握SELECT语句的语法格式；掌握单表查询技术；掌握聚合函数的使用方法；掌握连接查询技术；掌握嵌套查询技术。

5.1.1SQL概述

SQL的全称为StructuredQueryLanguage(结构化查询语言)。SQL是在20世纪70年代末由IBM公司开发的一套程序语言。由于其功能丰富，语言简捷，因此备受欢迎。经不断修改、扩充和完善，SQL语言最终发展成为关系数据库的标准语言。5.1SQL1986年10月，美国国家标准局(AmericanNationalStandardInstitute，ANSI)数据库委员会批准了SQL作为关系数据库语言的美国标准。1987年，国际标准化组织(InternationalOrganizationforStandardization，ISO)正式通过SQL作为关系数据库标准语言。

SQL成为关系数据库标准语言后，大多数数据库均以SQL作为共同的数据存取语言和标准接口。SQL是应用于数据库的语言，本身不能独立存在。它是一种非过程性语言。使用SQL时，只需告诉数据库需要什么数据，怎么显示就可以了，具体内部操作则由数据库系统来完成。各个DBMS产品在具体实现标准SQL时都有一些扩充。例如，Sybase与Microsoft公司使用Transact-SQL，而Oracle公司使用PL/SQL。5.1.2SQL的特点

SQL主要有以下特点：

(1)综合统一。SQL语言集数据定义语言、数据处理语言、数据控制语言为一体，具有统一风格，是综合统一的语言。

(2)高度非过程化。SQL语言只需告诉数据库需要什么数据，怎么显示，具体的内部操作则由数据库系统自动完成，是一种高度非过程化的语言。

(3)能以多种方式使用。SQL语言能以多种方式使用，可直接以命令的方式交互使用，也可嵌入到程序设计语言中使用，非常灵活。

(4)简捷易学。SQL语言功能强大，但由于语法比较简单，接近自然语言，所以容易学习，易于掌握。表5-1所列是SQL语言中完成核心功能的九个动词。表5-1SQL语言的动词5.1.3SQL的分类

SQL按照用途可以分为如下三大类。

1.数据定义语言

在SQL中定义数据库、表、视图和索引等数据库对象的语言，称为数据定义语言(DataDefinitionLanguage，DDL)。例如，创建数据库语句(CREATEDATABASE)、创建数据表语句(CREATETABLE)和创建视图语句(CREATEVIEW)等。

2.数据处理语言

在SQL中处理数据的语言称为数据处理语言(DataManipulationLanguage，DML)。例如，使用SELECT(数据查询语句)查询表的内容，或者使用INSERT(插入语句)、UPDATE(更新语句)和DELETE(删除语句)插入、修改和删除表中的数据记录。

3.数据控制语言

在SQL中提供并发控制及恢复功能，向用户授权，支持事务提交、回滚等功能的语言，称为数据控制语言(DataControlLanguage，DCL)。例如，有时可能需要一次处理几条SQL语句，希望它们必须全部执行成功，如果其中一条执行失败，则这几条语句都不要执行，而且已经执行的应该恢复到开始的状态，这时就需要用到数据控制语言。

前面已经学习了部分数据定义语句，本章重点学习数据处理语言中的SELECT查询语句。查询是SQL的核心功能，也是数据库中使用最多的操作。其他SQL语句在后续章节中将陆续讲述。本章以SCMS数据库查询为例来说明。

5.2.1SELECT语句的语法格式

SELECT语句的语法格式如下：

SELECT[ALL|DISTINCT]<字段表达式1>[,<字段表达式2>[,...n]]

[INTO<新表名>]

5.2SELECT语句

FROM<表名1>[,<表名2>[,...n]]

[WHERE<筛选条件表达式>]

[GROUPBY<分组表达式>[HAVING<分组条件表达式>]]

[ORDERBY<排序表达式>[ASC|DESC]]

各个子句说明如下：

●

SELECT子句：指定查询返回的列。

●

INTO子句：将查询结果存储到新表中。

●

FROM子句：用于指定查询列所在的表和视图。

●

WHERE子句：指定用于限制返回的行的筛选条件。●

GROUP子句：将结果按<分组表达式>的值进行分组，该值相等的记录为一个组。如果GROUP子句带有HAVING短语，则只有满足<分组条件表达式>条件的组才会显示输出。

SELECT语句的基本格式是由SELECT子句、FROM子句和WHERE子句组成的查询块。

整个SELECT语句的含义是：根据WHERE子句指定的条件，从FROM子句指定的表或视图中查找满足条件的数据，将查询结果按GROUPBY子句指定的条件分组，按ORDERBY子句指定的顺序排序，按SELECT子句指定的字段表达式及次序构造一个结果表。5.2.2SELECT语句的执行方式

SQLServer2005中的查询编辑器用于编辑和运行查询代码。

【例5-1】查询所有教师的信息。

具体操作步骤如下：

(1)启动SQLServerManagementStudio。

(2)单击工具栏中的“新建查询”按钮，打开查询编辑器。

(3)单击工具栏中的“可用数据库”下拉列表框，选择SCMS数据库，或使用USESCMS语句，将当前数据库修改为SCMS数据库。

(4)在查询编辑器窗口中输入如下查询语句：

SELECT*FROMteacher

(5)单击工具栏上的“分析”按钮，进行语法分析，保证语句语法正确。

(6)单击工具栏上的“执行”按钮，执行查询语句。执行结果如图5-1所示。图5-1例5-1执行结果

单表查询是指在一个表或一个视图中进行的查询。

5.3.1SELECT子句

SELECT子句用于指定查询返回的列。

1.查询指定的列

通常，用户只对部分列感兴趣，用SELECT子句指定所需的列。SELECT子句中字段的先后顺序可以与表中的顺序不一致，结果表按照SELECT子句中的字段顺序显示。5.3单表查询

【例5-2】查询学生表中所有学生的学号、姓名和家庭住址。

SELECTsno,sname,saddressFROMstudent

执行结果如图5-2所示。图5-2例5-2执行结果

2.查询所有的列

在SELECT子句中通常用通配符“*”表示指定表或视图中的所有列。

【例5-3】查询所有学生的信息。

SELECT*FROMstudent

执行结果如图5-3所示。图5-3例5-3执行结果

3.使用DISTINCT消除重复记录

在查询结果中往往会出现重复记录，使用DISTINCT短语可以去掉查询结果中重复出现的记录。如果不使用DISTINCT短语，默认使用ALL短语，表示允许重复记录出现。

【例5-4】查询所有学生所属班级的班级号。

SELECTDISTINCTclassno

FROMstudent

执行结果如图5-4所示。图5-4例5-4执行结果

4.加入字符串列

在显示查询结果时，为了增强结果的可读性，可以在某列前面加入字符串列，对该列起到说明作用。

【例5-5】查询所有学生的姓名和家庭住址。

SELECTsname,'家庭住址',saddress

FROMstudent

执行结果如图5-5所示。图5-5例5-5执行结果

5.使用别名

在显示查询结果时，为了增强结果的可读性，也可以指定别名代替原来列的名称。指定别名的方法有以下三种：

(1)采用“列名AS别名”的格式。

(2)采用“列名别名”的格式。

(3)采用“别名=列名”的格式。

这里别名可以用单引号引起来，也可以不用。

【例5-6】查询所有学生的姓名和家庭住址，并在标题栏显示“姓名”、“家庭住址”。

以下三句的执行效果相同：

SELECTsnameAS姓名,saddressAS家庭住址FROMstudent

SELECTsname姓名,saddress家庭住址FROMstudent

SELECT姓名=sname,家庭住址=saddressFROMstudent

执行结果如图5-6所示。图5-6例5-6执行结果

6.显示表达式的值

SELECT子句中的字段表达式不仅可以是字段，也可以是表达式。表达式中也可使用函数。

【例5-7】查询所有学生的姓名和年龄。

SELECTsname姓名,year(getdate())-year(borndate)年龄

FROMstudent

执行结果如图5-7所示。图5-7例5-7执行结果5.3.2WHERE子句

WHERE子句的目的是从表或视图中过滤出符合条件的记录。WHERE子句中的筛选条件表达式可以是关系表达式、逻辑表达式和特殊表达式。

1.关系表达式

用关系运算符将两个表达式连接在一起的式子即为关系表达式，关系表达式的返回值为逻辑值(TRUE，FALSE)。关系表达式的格式如下：

<表达式1><关系运算符><表达式2>

WHERE子句中的常用关系运算符如表5-2所示。表5-2关系运算符

说明

①字符型数据之间的比较是对字符的ASCII值进行比较。

②字符串的比较是从左向右依次进行的。

【例5-8】查询所有男生的姓名和家庭住址。

SELECTsname,saddress

FROMstudent

WHEREssex='男'

执行结果如图5-8所示。

【例5-9】查询1988年以后出生的学生的基本情况。

SELECT*

FROMstudent

WHEREborndate>'1988-12-31'

执行结果如图5-9所示。

图5-8例5-8执行结果

图5-9例5-9执行结果

2.逻辑表达式

用逻辑运算符将两个表达式连接在一起的式子即为逻辑表达式，逻辑表达式的返回值为逻辑值(TRUE，FALSE)。逻辑表达式的格式如下：

[<关系表达式1>]<逻辑运算符><关系表达式2>

WHERE子句中常用的逻辑运算符如表5-3所示。表5-3逻辑运算符【例5-10】查询所有年龄大于20岁的女生的姓名和出生日期。

SELECTsname,borndate

FROMstudent

WHEREyear(getdate())-year(borndate)>20ANDssex='女'

执行结果如图5-10所示。

【例5-11】查询09031012班甘肃籍的学生的姓名、性别和家庭住址。

SELECTsname,ssex,saddress

FROMstudent

WHEREclassno='09031012'ANDsubstring(saddress,1,2)='甘肃'

执行结果如图5-11所示。

图5-10例5-10执行结果

图5-11例5-11执行结果

3.特殊表达式

特殊表达式在比较运算中有一些特殊的用途，如用于指定范围、模式匹配、测试字段值是否为空、检查一个字段值是否属于一组值之中等。

WHERE子句中特殊表达式常用的特殊运算符如表5-4所示。特殊表达式的具体格式在使用时给出。表5-4特殊运算符

说明使用通配符时，一个汉字算一个字符。

1)

BETWEEN…AND

使用BETWEEN…AND可实现一个区间范围的筛选。格式如下：

<表达式>[NOT]BETWEEN<表达式1>AND<表达式2>

【例5-12】查询所有年龄为20～21岁的学生的基本情况。

SELECT*

FROMstudent

WHEREyear(getdate())-year(borndate)BETWEEN20AND21

执行结果如图5-12所示。

【例5-13】查询所有年龄不在20～21之间的学生的基本情况。

SELECT*

FROMstudent

WHEREyear(getdate())-year(borndate)NOTBETWEEN20AND21

执行结果如图5-13所示。

图5-12例5-12执行结果

图5-13例5-13执行结果

2)

ISNULL

当需要判断一个表达式是否为空值时，可使用ISNULL。格式如下：

<表达式>IS[NOT]NULL

【例5-14】查询所有电子邮件为空的学生姓名。

SELECTsname

FROMstudent

WHEREsemailISNULL

执行结果如图5-14所示。

【例5-15】查询有电子邮件的学生的姓名和电子邮件。

SELECTsname,semail

FROMstudent

WHEREsemailISNOTNULL

执行结果如图5-15所示。

图5-14例5-14执行结果

图5-15例5-15执行结果

3)

LIKE

LIKE用于判断字符串表达式是否与一个指定的模式字符串相匹配。格式如下：

<字符串表达式>[NOT]LIKE<模式字符串>

【例5-16】查询所有姓张，且姓名为两个汉字的学生的学号和姓名。

SELECTsno,snameFROMstudentWHEREsnameLIKE'张_'

执行结果如图5-16所示。

【例5-17】查询所有姓张和姓陈的学生的学号和姓名。

SELECTsno,snameFROMstudentWHEREsnameLIKE'[张,陈]%'

执行结果如图5-17所示。

图5-16例5-16执行结果

图5-17例5-17执行结果

4)

IN

IN可以指定一个值表，当表达式的值与值表中的任意一个匹配时，返回TRUE，否则返回FALSE。IN的作用类似于逻辑“或”。格式如下：

<表达式>[NOT]IN(表达式1[,...n])

【例5-18】查询所有09011011班和09011012班的学生的学号、姓名和性别。

SELECTsno,sname,ssex

FROMstudent

WHEREclassnoIN('09011011','09011012')

执行结果如图5-18所示。

【例5-19】查询既不是09011011班，也不是09011012班的学生的学号、姓名和性别。

SELECTsno,sname,ssex

FROMstudent

WHEREclassnoNOTIN('09011011','09011012')

执行结果如图5-19所示。

图5-18例5-18执行结果

图5-19例5-19执行结果5.3.3ORDERBY子句

在SELECT语句中，使用ORDERBY子句可以对查询结果进行升序或降序排列。其中“排序表达式”可以是一个列名、列的别名、表达式或非零的整数值，而非零的整数值则表示列名、列的别名、表达式在选择列表中的位置。ASC表示升序，为默认值；DESC表示降序。排序时空值(NULL)被认为是最小值。

【例5-20】按照出生日期顺序列出所有学生的学号、姓名、性别和出生日期。

SELECTsno,sname,ssex,borndate

FROMstudent

ORDERBYborndate

执行结果如图5-20所示。

【例5-21】查询所有09011011班和09011012班学生的学号、姓名、性别和出生日期，要求按照班级升序排序，同一班级按出生日期降序排序。

SELECTsno,sname,ssex,borndate

FROMstudent

WHEREclassnoIN('09011011','09011012')

ORDERBYclassno,borndateDESC

执行结果如图5-21所示。

图5-20例5-20执行结果

图5-21例5-21执行结果5.3.4聚合函数

SQLServer提供的聚合函数用来完成一定的统计功能。SQL语言中常用的聚合函数如表5-5所示。表5-5SQL语言中常用的聚合函数

说明如果指定DISTINCT，则表示在计算时要取消指定字段中的重复值。如果不指定DISTINCT，则表示为ALL(ALL为缺省)，即在计算时不取消重复值。

【例5-22】统计学生总人数。

SELECTCOUNT(*)AS学生人数

FROMstudent

执行结果如图5-22所示。

【例5-23】查询年龄最大和最小的学生的出生日期。

SELECTMIN(borndate)AS年龄最大学生生日,MAX(borndate)AS年龄最小学生生日

FROMstudent

执行结果如图5-23所示。

图5-22例5-22执行结果

图5-23例5-23执行结果

【例5-24】统计03201号课程的平均分。

SELECTAVG(result)AS平均成绩

FROMsc

WHEREcno='03201'

执行结果如图5-24所示。

【例5-25】统计第一学期所开课程的总课时和总学分。

SELECTSUM(hours)AS总课时,SUM(credit)AS总学分

FROMcourse

WHEREterm='1'

执行结果如图5-25所示。

图5-24例5-24执行结果

图5-25例5-25执行结果5.3.5GROUPBY子句

利用GROUPBY子句能够将查询结果按照指定的字段值进行分组，实现分组统计。HAVING子句对分组后的结果进行条件筛选，控制只输出分组条件表达式值为TRUE的组。

【例5-26】统计各班学生人数。

SELECTclassnoAS班级,COUNT(*)AS人数

FROMstudent

GROUPBYclassno

执行结果如图5-26所示。

【例5-27】统计各班学生人数和平均年龄。

SELECTclassno班级,COUNT(sno)人数,

AVG(year(getdate())-year(borndate))平均年龄

FROMstudent

GROUPBYclassno

执行结果如图5-27所示。

图5-26例5-26执行结果

图5-27例5-27执行结果图5-28例5-28执行结果

【例5-28】查询人数大于2的班级。

SELECTclassnoAS班级,COUNT(*)AS人数

FROMstudent

GROUPBYclassnoHAVINGCOUNT(*)>2

执行结果如图5-28所示。

说明WHERE子句与HAVING子句的区别在于作用的对象不同。WHERE子句作用于表或视图，从表或视图中选择满足筛选条件的记录；HAVING子句作用在组上，选择满足筛选条件的组。

单表查询只在一个表或视图中进行查询。在实际查询中，往往涉及多个表或视图。例如，查询某学生选修课程的情况，包括学生姓名、课程名、课程成绩、取得学分等信息，就需要在student表、sc表和course表中进行查询。将同时涉及两个或两个以上的表或视图的查询称为连接查询。

在Transact-SQL中，连接查询有两类表示形式：一类是符合SQL标准的连接谓词的表示形式，一类是Transact-SQL扩展的JOIN连接的表示形式。5.4连接查询5.4.1连接谓词

在SELECT语句的WHERE子句中使用比较运算符，给出连接条件对表进行连接，将这种表示形式称为连接谓词的表示形式。基本格式如下：

[<表名1>.]<字段名1><运算符><表名2.><字段名2>

以上连接谓词中的两个字段称为连接字段，它们必须是可比的。连接谓词中的比较运算符可以是<、<=、=、>、>=、!=、<>等。当比较运算符为“=”时，就是等值连接；若在等值连接中去除结果表中相同的字段名，则为自然连接；若有多个连接条件，则为复合条件连接；若一个表与自身连接，则称为自身连接。

【例5-29】查询每个学生及其班级的详细情况。

SELECTstudent.*,class.*

FROMstudent,class

WHEREstudent.classno=class.classno

这是一种等值连接的方法，结果表中有两个“classno”字段。执行结果如图5-29所示。图5-29例5-29执行结果

说明在查询所引用的两个或多个表之间，任何重复的字段都必须用表名限定。如果某字段在查询用到的两个或多个表中不重复，则不必用表名去限定。但是如果所有的字段都用表名限定，则可提高查询的可读性。

【例5-30】查询每个学生及其班级的详细情况。

SELECTstudent.*,class.classname,class.leader,class.dno

FROMstudent,class

WHEREstudent.classno=class.classno

这是一种自然连接的方法，结果表中只有一个“classno”字段。执行结果如图5-30所示。图5-30例5-30执行结果

【例5-31】查询选修了“计算机导论”课程且成绩在80分以上的学生的学号、姓名、课程名及成绩。

SELECTstudent.sno,student.sname,ame,sc.result

FROMstudent,course,sc

WHEREstudent.sno=sc.snoANDo=o

ANDame='计算机导论'ANDsc.result>80

这是一种复合条件连接。执行结果如图5-31所示。

【例5-32】查询所有课程的先修课的先修课。

SELECTame,ame

FROMcoursea,courseb,coursec

WHEREa.pno=oANDb.pno=o

这是一种自身连接。执行结果如图5-32所示。

图5-31例5-31执行结果

图5-32例5-32执行结果5.4.2JOIN连接

Transact-SQL扩展的JOIN连接的表示形式可将多个表连接起来。FROM后面JOIN连接的格式如下：

<first_table><join_type><second_table>ON<search_condition>

|<first_table>CROSSJOIN<second_table>

参数说明如下：

●

first_table，second_table：需要连接的表。

●

join_type：连接类型。其格式如下：

[INNER]|{LEFT|RIGHT|FULL}[OUTER]JOIN

●

ON：用于指定连接条件。

以JOIN指定的连接有三种类型：INNERJOIN为内连接，OUTERJOIN为外连接，CROSSJOIN为交叉连接。

1.内连接

内连接按照ON所指定的连接条件合并两个表，返回满足条件的行。内连接是系统默认的，可以省略INNER。使用内连接后，仍可使用WHERE子句指定其他条件。

【例5-33】查询所有学生的基本情况和选修课程的情况。

SELECT*FROMstudentINNERJOINscONstudent.sno=sc.sno

结果表中包含了student和sc表的所有字段，其中sno是重复字段。执行结果如图5-33所示。若要去除重复字段，可将以上语句改为如下语句：

SELECTstudent.*,o,sc.result

FROMstudentINNERJOINscONstudent.sno=sc.sno

【例5-34】查询选修了03105号课程且成绩在85分以上的学生的姓名和成绩。

SELECTstudent.sname,sc.result

FROMstudentJOINscONstudent.sno=sc.sno

WHEREo='03105'ANDsc.result>85

执行结果如图5-34所示。

图5-33例5-33执行结果

图5-34例5-34执行结果

【例5-35】查询选修了“计算机导论”课程且成绩在80分以上的学生的学号、姓名、课程名及成绩。

SELECTstudent.sno,student.sname,ame,sc.result

FROMstudentJOINscONstudent.sno=sc.sno

JOINcourseONo=o

WHEREame='计算机导论'ANDsc.result>80

这是通过student表、sc表、course表之间的内连接实现的。执行结果如图5-35所示。图5-35例5-35执行结果

2.外连接

外连接的结果表不但包含ON所指定的连接条件的行，还包含相应表中的所有行。外连接包括三种：左外连接、右外连接和完全外连接。外连接中OUTER可以省略。

1)左外连接(LEFTOUTERJOIN或LEFTJOIN)

结果表中除了满足连接条件的行以外，还包含左表中的所有行，但不包括右表中的不匹配行。

【例5-36】查询所有学生的选修课程情况，列出学号、姓名、选修课程号、成绩。没有选课的学生也一并列出。

SELECTstudent.sno,student.sname,o,sc.result

FROMstudentLEFTOUTERJOINscONstudent.sno=sc.sno

结果表中包含了student表中所有的学生，没有选课的学生cno和result字段值为空值。执行结果如图5-36所示。图5-36例5-36执行结果

2)右外连接(RIGHTOUTERJOIN或RIGHTJOIN)

结果表中除了满足连接条件的行以外，还包含右表中的所有行，但不包括左表中的不匹配行。

【例5-37】采用右外连接查询09031011班所有学生的选修课程情况，列出学号、姓名、选修课程号、成绩。没有选课的学生也一并列出。

SELECTstudent.sno,student.sname,o,sc.result

FROMscRIGHTOUTERJOINstudentONstudent.sno=sc.sno

WHEREstudent.classno='09031011'

结果表中包含了student表中所有的学生，没有选课的学生cno和result字段值为空值。执行结果如图5-37所示。图5-37例5-37执行结果

3)完全外连接(FULLOUTERJOIN或FULLJOIN)

结果表中除了满足连接条件的行以外，还包含两表中的所有行。若要通过在连接结果中包括不匹配信息，可以使用完全外连接。

3.交叉连接

交叉连接的结果表是由第一个表的每行与第二个表的每行拼接后形成的表。交叉连接就是两个表的笛卡尔积。第一个表的行数乘以第二个表的行数等于交叉连接结果表的行数。交叉连接不使用WHERE子句。

【例5-38】查询所有可能的选课情况，列出学号、姓名、课程号、课程名。

SELECTstudent.sno,student.sname,o,ame

FROMstudentCROSSJOINcourse

执行结果如图5-38所示。图5-38例5-38执行结果

在SQL语句中，将一条SELECT查询语句嵌套在SELECT、INSERT、DELETE、UPDATE等语句中，该SELECT查询就是子查询。

5.5子查询当一条SELECT语句嵌套在SELECT语句中时，外层的SELECT语句称为外部查询或父查询，内层的查询就称为内部查询或子查询。子查询可以嵌套，利用子查询嵌套可以将比较复杂的查询分解成几个简单查询的嵌套来实现，使得条理清晰。

嵌套查询是按照逻辑顺序由里向外执行的，即先处理子查询，然后将子查询的结果用于父查询的查询条件。5.5.1使用IN的子查询

在嵌套查询中，子查询的结果往往是一个集合，使用IN的子查询用于进行一个表达式的值是否在子查询结果集中的判断。使用IN的子查询的格式如下：

<表达式>[NOT]IN(子查询)

当表达式与子查询结果集中的某个值相等时，返回TRUE，否则，返回FALSE。若使用NOT，则返回的值正好相反。

【例5-39】查询所有与“贺迎春”在一个班学习的学生的学号、姓名、班级号。

SELECTsno,sname,classno

FROMstudent

WHEREclassnoIN

(SELECTclassnoFROMstudentWHEREsname='贺迎春')

DBMS在实现该查询时，先查找“贺迎春”所在的班级号，再查找在该班级学习的学生。执行结果如图5-39所示。本例查询也可以通过以下的自身连接来完成，执行结果如图5-40所示。

SELECTa.sno,a.sname,a.classno

FROMstudenta,studentb

WHEREa.classno=b.classnoANDb.sname='贺迎春'

图5-39例5-39第1句执行结果

图5-40例5-39第2句执行结果

【例5-40】查询选修了03105号课程的学生的学号和姓名。

SELECTstudent.sno,student.sname

FROMstudent

WHEREsnoIN

(SELECTsnoFROMscWHEREcno='03105')

执行结果如图5-41所示。

【例5-41】查询09031011班没有选修03105号课程的学生的学号和姓名。

SELECTstudent.sno,student.sname

FROMstudent

WHEREclassno='09031011'ANDsnoNOTIN

(SELECTsnoFROMscWHEREcno='03105')

执行结果如图5-42所示。

图5-41例5-40执行结果

图5-42例5-41执行结果

【例5-42】查询选修了“数据库原理及应用”课程的学生名单。

SELECTstudent.sname

FROMstudent

WHEREsnoIN

(SELECTsnoFROMsc

WHEREcnoIN

(SELECTcnoFROMcourseWHEREcname='数据库原理及应用'))

执行结果如图5-43所示。

【例5-43】查询没有选修“数据库原理及应用”的学生名单。

SELECTstudent.sname

FROMstudent

WHEREsnoNOTIN

(SELECTsnoFROMsc

WHEREcnoIN

(SELECTcnoFROMcourseWHEREcname='数据库原理及应用'))

执行结果如图5-44所示。

图5-43例5-42执行结果

图5-44例5-43执行结果5.5.2使用比较运算符的子查询

使用比较运算符的子查询是指父查询和子查询之间用比较运算符连接的子查询。使用比较运算符的子查询使表达式的值与子查询的结果进行比较运算，其格式如下：

<表达式>{<|<=|=|>|>=|!=|<>|!<|!>}{ANY|ALL}(子查询)

其中，ANY和ALL说明对比较运算符的限制。

ALL表示表达式要与子查询结果集中的每个值都进行比较，当表达式与结果集中的每个值都满足比较运算的关系时，才返回TRUE，否则，返回FALSE。

ANY表示表达式只要与子查询结果集中的某个值满足比较运算的关系时，就返回TRUE，否则，返回FALSE。例如，“>ALL(0,1,2)”表示表达式的值要大于2，才返回TRUE。“>ANY(0,1,2)”表示表达式的值至少大于一个值，也就是表达式只要大于0或1或2，就返回TRUE。

因此，要使带有“>ALL”的子查询中的某行满足外部查询中指定的条件，引入子查询的列中的值必须大于由子查询返回的值的列表中的每个值。

同样，要使带有“>ANY”的子查询中的某行满足外部查询中指定的条件，引入子查询的列中的值必须至少大于由子查询返回的值的列表中的一个值。表5-6带有ALL和ANY的子查询运算【例5-44】查询比09031011班的所有学生年龄都小的学生的学号、姓名和出生日期。

SELECTstudent.sno,student.sname,borndate

FROMstudent

WHEREclassno!='09031011'ANDborndate>ALL

(SELECTborndateFROMstudentWHEREclassno='09031011')

执行结果如图5-45所示。

本查询也可以用聚合函数来实现，语句如下：

SELECTstudent.sno,student.sname,borndate

FROMstudent

WHEREclassno!='09031011'ANDborndate>

(SELECTMAX(borndate)FROMstudentWHEREclassno='09031011')

【例5-45】查询比09031011班的某个学生年龄小的学生的学号、姓名和出生日期。

SELECTsno,sname,borndate

FROMstudent

WHEREclassno!='09031011'ANDborndate>ANY

(SELECTborndateFROMstudent

WHEREclassno='09031011')

执行结果如图5-46所示。本查询也可以用聚合函数来实现，语句如下：

SELECTstudent.sno,student.sname,borndate

FROMstudent

WHEREclassno!='09031011'ANDborndate>

(SELECTMIN(borndate)FROMstudent

WHEREclassno='09031011')

图5-45例5-44执行结果

图5-46例5-45执行结果5.5.3使用EXISTS的子查询

使用EXISTS用于测试子查询的结果集是否为空表。若子查询的结果集不为空，则EXISTS返回TRUE，否则，返回FALSE。EXISTS还可与NOT结合，即NOTEXISTS，其返回值与EXISTS刚好相反。使用EXISTS的子查询格式如下：

[NOT]EXISTS(子查询)

由EXISTS引出的子查询，因为SELECT子句指定列无意义，其SELECT子句格式通常是SELECT*。

【例5-46】查询选修了03105号课程的学生学号和姓名。

SELECTstudent.sno,student.sname

FROMstudent

WHEREEXISTS

(SELECT*FROMscWHEREcno='03105'ANDsno=student.sno)

执行结果如图5-47所示。图5-47例5-46执行结果

【例5-47】查询没有选修03105号课程的学生学号和姓名。

SELECTstudent.sno,student.sname

FROMstudent

WHERENOTEXISTS

(SELECT*FROMscWHEREcno='03105'ANDsno=student.sno)

执行结果如图5-48所示。图5-48例5-47执行结果

【例5-48】查询选修了全部课程的学生名单。

SELECTstudent.sname

FROMstudent

WHERENOTEXISTS

(SELECT*FROMcourseWHERENOTEXISTS

(SELECT*FROMscWHEREsno=student.snoANDcno=o))

执行结果如图5-49所示。图5-49例5-48执行结果5.5.4相关子查询

分析例5-46、例5-47和例5-48,可发现它们有一个共同之处，即子查询的查询条件中引用了外层父查询的某个字段值。

如果子查询的WHERE子句中引用了外部查询表，则该查询称为相关子查询。

相关子查询与非相关子查询不同。非相关子查询的内层查询只处理一次，得到一个结果集，依据子查询的结果处理外层查询；而相关子查询的内层查询要处理多次，内层查询与外层查询的某个字段有关，当外层查询表随着不同行该字段值变化时，内层查询的结果集就会随之变化，依据变化的子查询的结果处理外层查询。以例5-46为例，子查询的查询条件中引用了外层父查询的student表的sno字段，父查询的student表的不同行的sno字段值不同，相对应的子查询的查询条件就不同，因而子查询的查询结果集就随着父查询的student表的sno字段值的变化而变化。

相关子查询的执行过程是：

(1)取外层查询表的一行，取出与内层查询相关的字段值。

(2)根据该行与内层查询相关的字段值来处理内层查询，若外层查询的WHERE子句返回值为真，则取此行记录放入结果集中。

(3)返回到第(1)步，直到外层查询表的所有行处理完为止。

【例5-49】查询软件09-1班的学生名单。

SELECTstudent.sname

FROMstudent

WHERE'软件09-1'IN

(SELECTclassnameFROMclass

WHEREclassno=student.classno)

执行结果如图5-50所示。图5-50例5-49执行结果5.5.5INSERT、DELETE、UPDATE语句中的子查询

子查询也可以嵌套在INSERT、DELETE、UPDATE语句中。

1.带子查询的INSERT语句

前面介绍的INSERT语句一次只能插入一条记录。实际上，带子查询的INSERT语句一次可以插入多条记录。

带子查询的INSERT语句的基本格式如下：

INSERTINTO<表名>[(<字段1>[,<字段2>...])]

子查询

【例5-50】对每个班，求学生的最小年龄、最大年龄、平均年龄，然后新建一个表，把结果存入表中。

首先建立一个新表，有五个字段：班级号、班级名、最小年龄、最大年龄、平均年龄。

CREATETABLEclass_age

(

classnochar(8),

classnamevarchar(30),