第4章 关系数据查询语言

第4章关系数据查询语言关系的查询操作：关系的更新操作：选择(Select)投影(Project)连接(Join)除(Divide)并(Union)交(Intersection)差(Difference)广义笛卡儿积(ExtendedCartesianproduct)插入（Insert）删除（Delete）修改（Update）关系模型三要素：数据结构数据的约束条件关系操作4.1关系代数关系数据语言

4.1关系代数4.1关系代数

4.1.1传统的集合运算1、并（Union）关系的集合表示：RSR∪S

●

●

R∩SR－S2、差（Except）3、交（Intersect）并、交、差举例R={('数据库系统原理'),('嵌入式系统开发'),('面向对象程序设计'),('网络与信息安全')}S={('计算机三维建模'),('面向对象程序设计'),('数字图象处理技术'),('数字媒体技术')}例4-1某校软件学院有软件工程和数字媒体技术两个专业，设关系R为前者在本学期所开设课程的集合，S为后者在本学期所开设课程的集合：关系运算意义结果R∪SR∩SR－S{('数据库系统原理'),('嵌入式系统开发'),

('面向对象程序设计'),('网络与信息安全'),

('计算机三维建模'),('数字图象处理技术'),

('数字媒体技术')

}本学期，两个专业共开设的课程本学期，两个专业均开设的课程本学期，只为软件工程专业开设的课程{('面向对象程序设计')}{('数据库系统原理'),('嵌入式系统开发'),

('网络与信息安全')}4.1.1传统的集合运算4、广义笛卡尔积RSR×S4.1.2专门的关系运算

1、选择（Selection）运算例4-3要求从[课程]关系中提取所有选修课的所有信息。δ(课程)

【课程】关系结果2、联接（Join）例4-4将R关系与S关系做一个等值联接运算，联接条件为：R.课程编号

=S.课程编号。RSR

S

3、投影（Projection）例4-5从课程关系中，提取课程名称与学时数。

(R)

R关系运算的综合举例例4-6根据如图4-7所示的关系R和如图4-8所示的关系S，求如下的对关系进行运算的表达式的结果。

数据源INTERNETSQL请求结果集结果集SQL请求服务器客户端SQL查询是SQL的核心，是对存储在SQLServer中数据的一种用SELECT语句表达的查询请求，其功能是从指定的SQL数据源中提取满足用户要求的数据以结果集的形式返回给用户。4.2SQL查询基础1、数据源的组成SQL数据源由一个或多个表源构成，表源可以是以下三种之一：基表：

本地服务器中的数据表。视图：

本地服务器中的视图。链接表：

远程服务器中的表或视图。数据源2、结果集的构成结果集相关的五个主要属性：结果集包含哪些列以及这些列的属性：列的名称、数据类型、大小；列中数据值的来源。数据源中所包含的表源，以及表源之间的所有逻辑关系。表源中的行所必须达到的条件，凡不符合条件的行会被忽略对表源中的行或者列中的数据如何完成数学统计（如计数、求和、求平均值等）。结果集中行的排列顺序。结果集由行和列组成，是对用SELECT语句提取数据源中的数据的表格排列，与SQL数据表的结构相同，因此，结果集还可以作为其它查询的数据源。3、SELECT语句的语法框架SELECT[ALL|DISTINCT]

[TOP(expression)[PERCENT][WITHTIES]]

select_list[INTOnew_table]FROMdata_source[WHEREline_search_condition][GROUPBYgroup_by_list][HAVINGgroup_search_condition][ORDERBYorder_list[ASC|DESC]]可在多个查询之间使用UNION、INTERSECT和EXCEPT运算符（相当于关系的并、交、差等集合操作），以便将各个查询的结果集归并到一个结果集中。4、单个SELECT语句的执行顺序FROM子句：确定数据源中的表源。JOIN谓词运算符和ON子句（常包含在FROM子句中）：建立表源之间的联接。WHERE子句：行筛选。GROUPBY子句：行分组。聚合函数：组统计。HAVING子句：组筛选。选择列表<select_list>：提取列以建立结果集。DISTINCT或者ALL谓词运算符：消除或者保留重复行ORDER子句：结果集中行的排序。TOP子句（需要ORDER子句的配合）：行截取。INTO子句：以结果集的结构和内容为准，建立新表。数据源4.3 单表查询特点：查找范围狭窄简单、易学课程编号课程名称学时数学分数课程性质课程介绍学院编号C001数据库技术与应用322必修数据库技术药剂学课程是生物制药专业的专业课程与应用是一门非计算机专业的必修课程6C002生物化学885.5必修生物化学课程是生物技术等相关专业的专业基础课1C003药剂学483必修1C004理论力学483必修理论力学是工程类专业的基础课程2C005会计学563.5必修会计学是金融类专业的基础课程3C006社会工作概论644必修本课程是管理类专业的基础课程4C007英美文化概论322必修本课程是语言类专业的基础课程5C008C++程序设计基础644必修NULL6C009Python322选修NULL6C010中国古典文学鉴赏181选修本课程是公选课程，适合于所有喜欢中国古典文学的同学4C011古典哲学322选修本课程是公选课程，适合于所有喜欢世界历史、哲学的同学44.3.1 基本查询SELECT[ALL|DISTINCT]<select_list>FROMtable_name|view_name<select_list>::={[table_name.]*|[view_name.]*|column_name[AScolumn_alias]|expressionAScolumn_alias|{column_alias=expression}}[,...n]例4-7从[学院]表中提取各个学院的所有信息。SELECT

*

FROM

学院

;例4-8从[学院]表中提取所有学院的名称、电话组成一个学院电话表。语句1：SELECT学院名称,学院电话FROM学院;语句2：SELECT学院名称,RIGHT(LTRIM(RTRIM(学院电话)),8)AS教务办电话

FROM学院例4-9查询所有教师所属的职称有哪些？语句1：SELECT职称FROM教师;语句2：去掉重复的行SELECTDISTINCT职称FROM教师;4.3.2 条件查询条件子句：

WHERE<line_search_condition><line_search_condition>：行筛选条件行筛选条件可为：关系表达式、逻辑表达式

关系运算符为：=（等于）!=或<>（不等于）>（大于）>=（大于等于）<（小于）<=（小于等于）等。逻辑运算符为：Not非（求反）And与Or或运算符说明ALL满足子查询中所有值的记录，用法：<字段><比较符>ALL(<子查询>)ANY满足子查询中任意一个值的记录。用法：<字段><比较符>ANY(<子查询>)BETWEEN字段的内容在指定范围内。用法：<字段>BETWEEN<范围始值>AND<范围终值>EXISTS测试子查询中查询结果是否为空。若为空，则返回假（FALSE）。用法：EXISTS(<子查询>)IN字段内容是结果集合或者子查询中的内容。用法：<字段>IN<结果集合>或者<字段>IN(<子查询>)LIKE对字符型数据进行字符串比较，提供两种通配符，即下划线“_”和百分号“%”，下划线表示1个字符，百分号表示0个或多个字符。用法：<字段>LIKE<字符表达式>SOME满足集合中的某一个值，功能与用法等同于ANY。用法：<字段><比较符>SOME(<子查询>)ISIS[NOT]NULLWHERE子句中的条件运算符（谓词）LIKE用法：

<字符型字段名>LIKE<字符表达式>在<字符表达式>中可以使用下面的通配符通配符：

_

表示某个占位符上的一个任意字符，

%

表示可以是任意多个任意字符，

[]

表示取[]中的任意一个字符，如：[abcdef]也可[a-f]

[^]

表示不取[]中的字符，例4-10列出学分在3分以下（不包含3分）所有选修课程的名称和它们的学分数和学时数。SELECT课程名称,学分数,学时数

FROM课程

WHERE学分数<3AND课程性质='选修';例4-11查询学时数在40和60之间的所有课程的名称和学时数。SELECT课程名称,学时数FROM课程

WHERE学时数BETWEEN40AND60WHERE

学时数

>=

40AND学时数<=60例4-12查询四川籍（特指省份，不包括直辖市）男学生的所有信息。SELECT*

FROM学生

WHERE籍贯LIKE'四川%'AND性别='男';WHERELEFT(籍贯,2)='四川'AND性别='男'例4-13查询郑涛、郭豪、苏永红、蒋波四位同学的学号、姓名、籍贯、专业班级和学院编号。SELECT学号,姓名,籍贯,专业班级,学院编号

FROM学生

WHERE姓名IN('郑涛','郭豪','苏永红','蒋波');条件表示这样的意思：WHERE姓名='郑涛'OR姓名='郭豪'OR姓名='苏永红'OR姓名='蒋波'例4-14查找还未设置密码的教师的所有信息。SELECT*

FROM教师

WHERE密码ISNULL空值的判断不能使用等于比较运算符：

密码=NULL×4.3.3生成表查询语法：

INTOnew_table参数：new_table：以SELECT语句执行后的结果集为内容，创建新的数据表或临时表。注意：当选择列表select_list中包括计算列时，新表中的相应列不再是计算列，其类型由其值的格式来确定。例4-15将工程力学1701班所有学生的学号、姓名、出生日期等信息永久保存到当前数据库的新数据表[工程力学1701]中。注意：生成表查询并不显示查询结果。但可用如下SELECT语句显示新表SupperFilm的内容：SELECT*

FROMSupperFilm;SELECT学号,姓名,出生日期

INTO[工程力学1701]

FROM学生

WHERE专业班级='工程力学1701';4.3.4 聚合查询什么是聚合查询？所谓聚合查询就是按照分组列（在GROUP子句中指定）值的个数n，将数据源中指定的行（满足WHERE条件的行）分成n个组（缺省GROUP的情况下，分成一个组），并且可对每一个组做进一步的组筛选（由HAVING子句实现），再针对每一组返回一个统计性的摘要行（该摘要行中的统计数据由SELECT子句中的聚合函数提供）。聚合查询有下列三种实现方式：仅由聚合函数实现聚合查询由聚合函数和GROUP子句共同实现由聚合函数、GROUP子句和HAVING子句共同实现1.仅由聚合函数实现聚合查询计数函数：COUNT(*) COUNT([[ALL|DISTINCT]expression])求和函数：SUM([ALL|DISTINCT]expression)求平均值函数：AVG([ALL|DISTINCT]expression)求最小值函数：MIN(expression)求最大值函数：MAX(expression)聚合函数只能在以下位置作为表达式使用：SELECT语句的选择列表select_list中。HAVING子句的组筛选器中。例4-16统计全校一共开设了多少门课程。

SELECTCOUNT(*)as课程门数

FROM课程;2.由聚合函数和GROUP子句共同实现语法：GROUPBY<group_by_list><group_by_list>::=

<group_by_expression>[,...n]参数：group_by_list：分组列表，可由多个分组列组成。group_by_expression：分组列（或者称分组依据），可以是单独的表列或视图列，也可以是关于表列或视图列的非聚合表达式。其意义是根据其值的个数将数据源中行分成几个组。例4-17统计2017-2018学年第一学期每一个课堂的平均成绩，显示每一个课堂的编号和平均成绩。SELECT课堂编号,AVG(成绩)AS平均成绩

FROM选课成绩

WHERE课堂编号LIKE'2017-2018-1%'GROUPBY课堂编号;结果集

有几行？例4-18根据院系统计男女生的人数，显示每一个学院的编号、性别以及人数。SELECT学院编号,性别,COUNT(*)AS人数

FROM学生

GROUPBY学院编号,性别;3.由聚合函数、GROUP子句和HAVING子句共同实现例4-19查询各种职称的教师人数。SELECT职称,COUNT(*)AS人数

FROM教师

GROUPBY职称HAVING<group_search_condition>group_search_condition为组筛选条件如果想去掉无职称人的统计结果，则可使用如下两种方式：SELECT职称,COUNT(*)AS人数FROM教师GROUPBY职称

HAVING职称ISNOTNULL;SELECT职称,COUNT(*)AS人数

FROM教师

WHERE职称ISNOTNULL

GROUPBY职称;SELECT职称,COUNT(*)AS人数FROM教师GROUPBY职称HAVINGCOUNT(*)>2;

如果我们限定人数，则可使用下面的语句屏蔽人数小于等于2的组（注意：在HAVING子句中不可使用列别名）：生成合计作为附加的汇总列出现在结果集的最后。当与BY一起使用时，COMPUTE子句在结果集内生成控制中断和小计。[COMPUTE

{聚合函数}[,...n][BYexpression[,...n]]]BYexpression表示按expression字段分组，使用BY子句必须先按此字段排序4．使用COMPUTE和COMPUTEBY子句汇总统计学生表中的学生人数并显示详细数据SELECT*FROM学生computecount(学号)按班级分别统计学生表中各班的学生人数并显示详细数据SELECT*FROM学生orderby专业班级computecount(学号)by专业班级4.3.5 结果集的数据排序1、ORDER子句语法：ORDERBYorder_by_listorder_by_list::= {order_by_expression[ASC|DESC]}[,...n]参数：order_by_list：排序列表，可包含多个排序列。order_by_expression：排序列。可以是一个表列、视图列或列别名，也可以是一个表示该名称或别名在选择列表select_list中所处位置的非负整数。[ASC|DESC]：可缺省。ASC代表升序（默认），DESC代表降序。例4-20从[教师]表中提取每一位教师的姓名和职称，要求按职称的字典序排列显示。SELECT姓名,职称

FROM教师

ORDERBY职称;

如果要求按照职称的意义进行排序，即按照教授、副教授、讲师、助教、无职称的顺序排列?CASE表达式的妙用之一SELECT姓名,职称

FROM教师

ORDERBYCASE

WHEN职称='教授'THEN1

WHEN职称='副教授'THEN2

WHEN职称='讲师'THEN3

WHEN职称='助教'THEN4

ELSE5

END;CASE

WHEN条件1THEN表达式1WHEN条件2THEN表达式2…

ELSE表达式nEND2、TOP子句[TOP(expression)[PERCENT][WITHTIES]]参数：TOP(expression)：指示只能从结果集返回由expression指定数目的第一组行。TOP(expression)PERCENT：指示只能从查询结果集返回由expression指定的百分比数目（相对于结果集的大小）的第一组行。WITHTIES指定从结果集中再返回一些额外的行，这些额外的返回行与TOP限制后从结果集返回的最后一行，在ORDER子句指定的排序列上的值是相同的。例4-21提取编号为“2017-2018-2-B009”的中，成绩最高的前三名学生的学号，要求显示学号和成绩。SELECTTOP(3)

学号,成绩

FROM选课成绩

WHERE课堂编号='2017-2018-2-B009'

ORDERBY成绩DESC;SELECTTOP(3)WITHTIES学号,成绩

FROM选课成绩

WHERE课堂编号='2017-2018-2-B009'

ORDERBY成绩DESC;如果要显示并列第三的4.4 多表查询学院学院编号学院名称学院地址学院电话课程课程编号课程名称学时数学分数课程性质课程介绍学院编号数据查询要求：查询生命学院共开设了那些课程，要求显示课程的名称。“生命学院”“课程名称”课程名称生物化学药剂学结论：本次查询至少需要从[学院]、[课程]二个相关数据表中提取结果数据4.4.1 联接概述1、联接的目的建立数据行的导航路径[学院]表[课程]表2、联接展示

[学院]表[课程]表3、联接条件ON<join_condition>{AND<join_condition>}[…n]<join_condition>::=<column_exp>OP<column_exp>OP：=、<、>、<=、>=、<>、!=、!<、!>联接条件最常见的写法是将两个表的公共列的名字用比较运算符连接起来（注意：在列名前必须加上表名限制，以限定列的所属）。公共列的两种常见形态：公共列在两个表中均为主键。公共列是一个表的主键，但在另一个表中是外键。例如：

ON学院.学院编号

=课程.学院编号4、联接类型内链接INNERJOIN显示符合条件的记录，此为默认值等值连接用“=”非等值连接用>,>=,<,<=自然连接去掉重复字段外连接（用“=”）

LEFT（OUTER）JOIN为左（外）连接，用于显示符合条件的数据行以及左边表中不符合条件的数据行，此时右边数据行会以NULL来显示RIGHT（OUTER）JOIN右（外）连接，用于显示符合条件的数据行以及右边表中不符合条件的数据行。此时左边数据行会以NULL来显示FULL（OUTER）JOIN显示符合条件的数据行以及左边表和右边表中不符合条件的数据行。此时缺乏数据的数据行会以NULL来显示交叉连接CROSSJOIN（无条件）将一个表的每一个记录和另一表的每个记录匹配成新的数据行5、联接规范（内部联接）(1) 在FROM子句中指定联接规范，语法如下：FROM<data_source><data_source>::=<table_source>|{<table_source>[INNERJOIN<table_source>ON<join_condition>][…n]}<table_source>::={table_name|view_name}[[AS]table_alias]例4-22在FROM子句中指定[成绩]表和[学生]表的联接规范（采用内部联接）：FROM

成绩

INNER

JOIN学生

ON成绩.学生编号

=学生.学生编号(2)在WHERE子句中指定联接规范FROM<data_source>WHERE<join_condition>[AND<line_search_condistion>]<data_source>::=<table_source>[,…n]<table_source>::={table_name|view_name}[AStable_alias]例4-23在WHERE子句中指定[成绩]表和[学生]表的联接规范（注意：联接类型默认为内部联接）：FROM成绩,学生WHERE

成绩.学生编号

=

学生.学生编号4.4.2内部联接(innerjoin)内部联接可分为：

等值联接、非等值联接、自然联接等值联接的联接条件中使用“=”非等值联接的联接条件中使用“>”、“>=”、“<”、“<=”自然联接的联接条件中使用“=”

和等值联接不同的是要去掉重复属性。例4-26查询教师欧阳淑芳所上的所有课堂，要求按照开课年份和开课学期的升序，显示课堂名称、开课年份、开课学期。SELECT

课堂.课堂名称,

课堂.开课年份,

课堂.开课学期

FROM

教师

INNER

JOIN

课堂

ON

教师.教师编号

=

课堂.教师编号

WHERE

教师.姓名

=

'欧阳淑芳'

ORDER

BY

课堂.开课年份,

课堂.开课学期

;SELECT

课堂.课堂名称,

课堂.开课年份,

课堂.开课学期

FROM

教师,

课堂

WHERE教师.教师编号

=

课堂.教师编号AND

教师.姓名

=

'欧阳淑芳'

ORDER

BY

课堂.开课年份,

课堂.开课学期

;例4-27查询王志强老师讲授的所有学生的名单，显示学号、姓名、专业班级。SELECT

学生.学号,

学生.姓名,

成绩,

专业班级

FROM

教师

INNER

JOIN

课堂

ON

教师.教师编号

=

课堂.教师编号

INNER

JOIN

选课成绩

ON

课堂.课堂编号

=

选课成绩.课堂编号

INNER

JOIN

学生

ON

选课成绩.学号

=

学生.学号

WHERE

教师.姓名

=

'王志强'

;如果将联接条件写在WHERE子句中?例4-28列出最受欢迎（特指选修人数）的前三门课程，要求按选修人数的降序排列课程的名称和选修人数。SELECT

TOP3课程名称,

选修人数

FROM

课程

INNER

JOIN(SELECT

课程.课程编号,

COUNT(*)

AS

选修人数FROM

课程

INNER

JOIN

课堂

ON

课程.课程编号

=

课堂.课程编号

INNER

JOIN

选课成绩

ON

课堂.课堂编号

=

选课成绩.课堂编号GROUP

BY

课程.课程编号

)ASAON

课程.课程编号

=

A.课程编号

ORDER

BY

选修人数

DESC;例4-29统计计算机学院每一位教师本学期的教学工作量（指学时数），要求显示教师编号和学时数，并按学时数的降序排列。SELECT教师.教师编号,SUM(课程.学时数)AS学时数

FROM教师INNERJOIN课堂ON教师.教师编号=课堂.教师编号

INNERJOIN课程ON课堂.课程编号=课程.课程编号

innerjoin学院on教师.学院编号=学院.学院编号

WHERE课堂.开课年份='2017-2018'AND课堂.开课学期='一'and学院名称='计算机学院'GROUPBY教师.教师编号

ORDERBY学时数DESC4.4.3外联接(outerjoin)外联接外联接的联接条件是用“=”实现的外联接结果集中除了有满足条件的元组还有不满足条件的元组。外联接分为：左(外)联接、右(外)联接、全联接联接规范为：From表1left/right/fullouterjoin表2on联接条件交叉链接(crossjoin)交叉联接即没有条件的联接。其结果集为笛卡尔全集。联接规范为：From表1crossjoin表24.4.4结果集的归并处理使用谓词运算符（Union(集合并)、Except(集合差)、Intersect(集合交)）可以将多个结果处理成成一个结果集。语法：<SELECT_Statement>{UNION[ALL]|EXCEPT|INTERSECT}<SELECT_statement>位置：INTO的位置:只能放在第一个select语句中ORDERBY子句的位置:只能放在最后一个select语句中UNION：并运算例4-31从[学生]表中提取湖北省和其他省的人数。SELECT

'湖北'

AS

省份,

count(*)

AS

人数

FROM

学生

WHERE

籍贯

LIKE

'湖北%'UNIONSELECT

'其他'

AS

省份,

count(*)

AS

人数

FROM

学生

WHERE

籍贯

NOT

LIKE

'湖北%'

;INTERSECT：交运算例4-32查询土木工程、工程力学两个专业的学生在2017-2018学年均选修过的必修课程，要求显示课程编号、课程名称，并按课程编号的升序排列，并将查询结果保存到临时表Temp3中。SELECTdistinct课程.课程编号,课程.课程名称

INTOTemp3FROM课程INNERJOIN

课堂ON课程.课程编号=课堂.课程编号

WHERE课堂.开课年份='2017-2018'AND课程.课程性质='必修'AND课堂.班级列表LIKE'%土木工程%'INTERSECTSELECTdistinct课程.课程编号,课程.课程名称

FROM课程INNERJOIN

课堂ON课程.课程编号=课堂.课程编号

WHERE课堂.开课年份='2017-2018'AND课程.课程性质='必修'AND课堂.班级列表LIKE'%工程力学%'ORDERBY课程.课程编号;Except：差运算例4-33给出如表4.6所示的十二星座查询表，查询什么星座的人没有出现在[教师]表中。SELECT

*

FROM(

VALUES('白羊座'),

('金牛座'),

('双子座'),

('巨蟹座'),

('狮子座'),

('处女座'),

('天秤座'),

('天蝎座'),

('射手座'),

('摩羯座'),

('水瓶座'),

('双鱼座')

)

AS

星座表(

星座

)

EXCEPT

01月21日太阳进入水瓶座02月20日太阳进入双鱼座03月21日太阳进入白羊座04月20日太阳进入金牛座05月21日太阳进入双子座06月21日太阳进入巨蟹座07月23日太阳进入狮子座08月23日太阳进入处女座09月23日太阳进入天秤座10月23日太阳进入天蝎座11月22日太阳进入射手座12月22日太阳进入摩羯座SELECT

DISTINCT

CASE

WHEN

出生日期

BETWEEN

STR(YEAR(出生日期),

4)

+

'/1/21'

AND

STR(YEAR(出生日期),4)

+

'/2/19'

THEN

'水瓶座'

WHEN

出生日期

BETWEEN

STR(YEAR(出生日期),

4)

+

'/2/20'

AND

STR(YEAR(出生日期),4)

+

'/3/20'

THEN

'双鱼座'

WHEN

出生日期

BETWEEN

STR(YEAR(出生日期),

4)

+

'/3/21'

AND

STR(YEAR(出生日期),4)

+

'/4/19'

THEN

'白羊座'

WHEN

出生日期

BETWEEN

STR(YEAR(出生日期),

4)

+

'/4/20'

AND

STR(YEAR(出生日期),4)

+

'/5/20'

THEN

'金牛座'

WHEN

出生日期

BETWEEN

STR(YEAR(出生日期),

4)

+

'/5/21'

AND

STR(YEAR(出生日期),4)

+

'/6/20'

THEN

'双子座'

WHEN

出生日期

BETWEEN

STR(YEAR(出生日期),

4)

+

'/6/21'

AND

STR(YEAR(出生日期),4)

+

'/7/22'

THEN

'巨蟹座'

WHEN

出生日期

BETWEEN

STR(YEAR(出生日期),

4)

+

'/7/23'

AND

STR(YEAR(出生日期),4)

+

'/8/22'

THEN

'狮子座'

WHEN

出生日期

BETWEEN

STR(YEAR(出生日期),

4)

+

'/8/23'

AND

STR(YEAR(出生日期),4)

+

'/9/22'

THEN

'处女座'

WHEN

出生日期

BETWEEN

STR(YEAR(出生日期),

4)

+

'/9/23'

AND

STR(YEAR(出生日期),4)

+

'/10/22'

THEN

'天秤座'

WHEN

出生日期

BETWEEN

STR(YEAR(出生日期),

4)

+

'/10/23'

AND

STR(YEAR(出生日期),4)

+

'/11/21'

THEN

'天蝎座'

WHEN

出生日期

BETWEEN

STR(YEAR(出生日期),

4)

+

'/11/22'

AND

STR(YEAR(出生日期),4)

+

'/12/21'

THEN

'射手座'

ELSE

'摩羯座'

END

AS

星座

FROM

教师

;4.5 子查询什么是子查询？

子查询也可称为嵌套查询，SQLServer允许在查询中再嵌套查询。最外面的查询为父查询，里面的查询为子查询，父查询是要做的主要工作，子查询是为父查询提供条件或数据源等工作的。嵌套查询一般的查询方法是由里向外进行处理，即每个子查询在上一级查询处理之前处理，子查询中所存取的表可以是父查询没有存取的表，子查询选出的记录不显示。SELECT

MAX(选课成绩.成绩)

AS

最高分

FROM

选课成绩

INNER

JOIN

课堂

ON

选课成绩.课堂编号

=

课堂.课堂编号

INNER

JOIN

课程

ON

课堂.课程编号

=

课程.课程编号WHERE

课程.课程名称

=

'数据库技术与应用'SELECT

COUNT(*)

AS

人数

FROM

选课成绩

INNER

JOIN

课堂

ON

选课成绩.课堂编号

=

课堂.课堂编号

INNER

JOIN

课程

ON

课堂.课程编号

=

课程.课程编号

WHERE

课程.课程名称

=

'数据库技术与应用'

AND

选课成绩.成绩

=

(“数据库技术与应用”课程的最高分

)

例如：查询自“数据库技术与应用”课程开设以来获得最高分的人数有多少。4.5子查询的出现位置可在外部查询的许多位置指定子查询：在WHERE子句的行筛选条件line_search_condition或者HAVING子句的组筛选条件group_search_condition中：使用比较运算符（注：必须是单值子查询）使用ANY、SOME或ALL修饰的比较运算符使用IN或NOTIN运算符使用EXISTS或NOTEXISTS运算符在FROM子句中：必须使用表别名。在SELECT子句select_list中：代替表达式或表达式的一部分（注：必须是单值子查询）。4.5.1 单值子查询单值子查询：即子查询得到的结果为一个值！例如：查询生命学院都开设了那些课程。(SELECT

学院编号

FROM

学院

WHERE

学院名称

=

'生命学院')SELECT

课程.*

FROM

课程

WHERE

学院编号

='生命学院的学院编号'1、单值子查询出现在行筛选条件中例4-34假设生物科学1702班的吴冰同学的姓名是唯一的，查询与吴冰同学的所有同乡（指相同省份）的姓名和专业班级。SELECT

姓名,

专业班级

FROM

学生

WHERE

LEFT(籍贯,3)

=

(SELECT

LEFT(籍贯,3)

AS

省份

FROM

学生

WHERE

姓名

=

'吴冰'

AND

专业班级

=

'生物科学1702'

)AND

姓名

<>

'吴冰';2、单值子查询出现在组筛选条件中例4-35在所有的“数据库技术及应用”课堂中，查询比“数据库-交通工程1701-2”课堂的平均分还高的其它课堂的编号和平均分。SELECT

课堂.课堂编号,

AVG(选课成绩.成绩)

AS

平均分

FROM

选课成绩

INNER

JOIN

课堂

ON

选课成绩.课堂编号

=

课堂.课堂编号

INNER

JOIN

课程

ON

课堂.课程编号

=

课程.课程编号

WHERE

课程.课程名称

=

'数据库技术与应用'

GROUP

BY

课堂.课堂编号

HAVING

AVG(选课成绩.成绩)

>

(

SELECT

AVG(选课成绩.成绩)

AS

平均分

FROM

选课成绩

INNER

JOIN

课堂

ON

选课成绩.课堂编号

=

课堂.课堂编号

INNER

JOIN

课程

ON

课堂.课程编号

=

课程.课程编号

WHERE

课程.课程名称

=

'数据库技术与应用'AND

课堂.课堂名称

=

'数据库-交通工程1701-2'

);3、单值子查询的结果作为查询的结果集中一列例4-36在所有的“数据库技术及应用”课堂中，显示每一个课堂的平均分和所有课堂总平均分的比对情况，要求显示每一个课堂的编号和平均分、所有课堂的总平均分（作为比对值）。第一步：先做一个生成表查询，因为后续的两步，均要用到第一步的结果。求各个课堂的平均分，并将结果送到临时表“#各课堂平均分”表中保存SELECT

课堂.课堂编号,AVG(选课成绩.成绩)

AS

平均分

INTO

#各课堂的平均分

FROM

选课成绩

INNER

JOIN

课堂

ON

选课成绩.课堂编号

=

课堂.课堂编号

INNER

JOIN

课程

ON

课堂.课程编号

=

课程.课程编号

WHERE

课程.课程名称

=

'数据库技术与应用'

GROUP

BY

课堂.课堂编号第二步：在第一步的执行结果上，求所有课堂的总平均分：SELECT

AVG(平均分)

AS

总平均分

FROM

#各课堂的平均分

第三步：在第一步和第二步的执行结果上，查询每一个课堂的平均分和所有课堂的总平均分的差异比较。SELECT

*,

(第二步的SELECT语句)

AS

总平均分

FROM

#各课堂的平均分最后，将第一步（取消临时表）和第二步的语句带入第三步合成：SELECT

*,(SELECT

AVG(平均分)

AS

总平均分

FROM(SELECT

课堂.课堂编号,

AVG(成绩)

AS

平均分

FROM

选课成绩

INNER

JOIN

课堂

ON

选课成绩.课堂编号

=

课堂.课堂编号

INNER

JOIN

课程

ON

课堂.课程编号

=

课程.课程编号

WHERE

课程名称

=

'数据库技术与应用'

GROUP

BY

课堂.课堂编号

)

AS

各课堂的平均分

)

AS

总平均分

FROM(SELECT

课堂.课堂编号,

AVG(成绩)

AS

平均分

FROM

选课成绩

INNER

JOIN

课堂

ON

选课成绩.课堂编号

=

课堂.课堂编号

INNER

JOIN

课程

ON

课堂.课程编号

=

课程.课程编号

WHERE

课程名称

=

'数据库技术与应用'

GROUP

BY

课堂.课堂编号

)

AS

各课堂的平均分4.5.2 多值子查询所谓多值子查询是指结果集中有多行多列多个数据的子查询。可使用ANY、SOME、ALL修饰的比较运算符或者IN、EXISTS运算符，可出现：在WHERE子句的行筛选条件line_searc