数据库讲义ch07jWord格式文档下载.docx

数据库讲义ch07jWord格式文档下载.docx

《数据库讲义ch07jWord格式文档下载.docx》由会员分享，可在线阅读，更多相关《数据库讲义ch07jWord格式文档下载.docx（34页珍藏版）》请在冰豆网上搜索。

姓名

04302768

芮丹平

04302809

袁国平

04303396

陈凤

04303707

张里

04300477

李超

04300522

张兆营

04301190

王璐

04301378

蔡亚梅

04301388

吴挺

04301391

洪文达

04302472

沙洋娟

04302610

侯蕾

04302633

杜鹃

04302653

张微

04302686

蔡成凯

04302696

赵小俊

04302714

董莺

04302717

张豆

7.1.3外部参照

在子查询体内，通常有必要指定主查询当前行字段的值。

外部参照是一个字段名，但该字段名并不参照出现的子查询中FROM子句指定表的任何字段，而是参照主查询FROM子句指定表的—个字段。

当DBMS在子查询中检查搜索条件时，外部参照中字段值从当前主查询测试行中获得。

列出可授予多个学位的专业名称

SELECT名称

FROM专业

WHERE1<

（SELECTCount（学位代码）

FROM专业学位

WHERE专业代码=代码）外部参照

社会工作

思想政治教育

应用物理学

材料物理

材料化学

工业工程

7.2子查询搜索条件

子查询通常作为WHERE子句或HAVING子句搜索条件的—部分出现。

除了前面介绍的简单搜索条件之外，子查询中还可以有下面的搜索条件：

●子查询比较测试，它用以将表达式的值与子查询生成的单值进行比较，这个测试与简单比较测试类似。

●子查询组成员测试，它用于检查表达式的值是否与子查询生成的—组值的之一匹配。

这种测试与简单组成员测试类似。

●存在性测试，它用于测试子查询是否生成查询结果的一些行。

●限定比较测试，它用于将表达式的值与子查询生成的一组值中的每—个值进行比较。

7.2.1子查询比较测试

列出班级人数大于等于30人的班级及其所在院系名称和专业名称

SELECT班级.班级号,单位.名称,专业.名称

FROM（班级INNERJOIN单位ON班级.所属学院=单位.代码）INNERJOIN专业ON班级.所属专业=专业.代码

WHERE30<

=（SELECTCount（*）FROM学生WHERE学生.班级=班级.班级号）

班级号

单位.名称

专业.名称

04014001

机械工程学院

05014001

列出2005年同一专业在不同学院分别开设的专业大类名称、专业代码、专业名称、学院名称

2005年的专业设置

SELECT专业设置.年度,专业大类.名称,专业.代码,专业.名称,单位.名称

FROM（（专业设置INNERJOIN专业ON专业设置.专业代码=专业.代码）INNERJOIN单位ON专业设置.单位代码=单位.代码）INNERJOIN专业大类ON专业.专业大类代码=专业大类.代码

WHERE（（（专业设置.年度）=2005））

ORDERBY专业.专业大类代码,专业.代码

年度

专业大类.名称

代码

2005

经济学类

020101

经济学

经济管理学院

020102

国际经济与贸易

020104

金融学

法学类

030101

法学

人文与社会科学学院

社会学类

030302

政治学类

030404

外国语言文学类

050201

英语

外语系

新闻传播学类

050302

广播电视新闻学

艺术类

050408

艺术设计

数学类

070101

数学与应用数学

理学院

070102

信息与计算科学

物理学类

070202

电子信息科学类

071203

光信息科学与技术

材料科学类

071302

化工学院

材料类

080204

高分子材料与工程

080205

材料科学与工程

材料科学与工程系

机械类

080301

机械设计制造及其自动化

080302

材料成型及控制工程

080303

工业设计

080305

机械工程及自动化

080306

车辆工程

仪器仪表类

080401

测控技术与仪器

能源动力类

080501

热能与动力工程

动力工程学院

电气信息类

080601

电气工程及其自动化

自动化学院

080602

自动化

080603

电子信息工程

电子工程与光电技术学院

080604

通信工程

080605

计算机科学与技术

计算机科学与技术学院

080606

电子科学与技术

080613

网络工程

080616

光电信息工程

土建类

080703

土木工程

080704

建筑环境与设备工程

环境与安全类

081001

环境工程

081002

安全工程

化工与制药类

081101

化学工程与工艺

081102

制药工程

交通运输类

081202

交通工程

武器类

081601

武器系统与发射工程

081602

探测制导与控制技术

081603

弹药工程与爆炸技术

081604

特种能源工程与烟火技术

081605

地面武器机动工程

081606

信息对抗技术

081607

武器系统与工程

工程力学类

081701

工程力学

生物工程类

081801

生物工程

管理科学与工程类

110102

信息管理与信息系统

110103

工商管理类

110201

工商管理

110202

市场营销

110203

会计学

110204

财务管理

110205

人力资源管理

110209

电子商务

公共管理类

110302

公共事业管理

WHERE专业设置.年度=2005And1<

（SELECTCount（*）FROM专业设置WHERE年度=2005AND专业代码=专业.代码）

SQL1标难规定的、所有主流DBMS产品都支持的子查询比较测试只允许子查询在比较运算符的右边。

A<

（子查询）允许，

（子查询）>

A不允许。

但是SQL2标准取消了这一限制。

7.2.2组成员测试

列出微机原理及应用课程期末成绩好于平时成绩的学生

SELECT学号FROM教与学

WHERE课程编号='

AND平时/0.3<

期末/0.6

ORDERBY学号

这个例子就是要列出学号为上表中学生的姓名。

SELECT学号,姓名FROM学生

WHERE学号IN

（SELECT学号FROM教与学

期末/0.6）

列出不在由职工号为05094任负责人的单位中教师姓名

SELECT姓名FROM教师

WHERE（单位代码NOTIN

（SELECT代码FROM单位WHERE负责人='

05094'

））

吴慈仁

贾德

徐霓

列出课程编号以“05”为首且学分介于4与6之间的课程为哪个年级、哪个专业、在哪个学期和哪个学年开设，性质为何

SELECT计划.年级,专业.名称,计划.课程性质,计划.开课学年,计划.开课学期

FROM计划INNERJOIN专业ON计划.专业代码=专业.代码

WHERE计划.课程编号In

（SELECT编号FROM课程

WHERE编号Like'

05*'

AND学分Between4And6）

年级

课程性质

开课学年

开课学期

2004

学科基础课

3

春

集中实践教学环节

4

秋

上面的例子中子查询生成一组值，主查询的WHERE子句检查来自主查询行的值是否与该组值中某一个匹配。

7.2.3存在性测试

用于测试子查询是否生成查询结果的一些行。

列出这样的教材：

被学分数超过3的课程选用

SELECT作者,名称,出版社,价格

FROM教材

WHEREEXISTS

（SELECT编号

FROM课程

WHERE使用课程=编号And学分>

3）

作者

出版社

价格

JefferyD.Ullman著，史嘉权译

数据库系统基础教程

清华大学出版社

￥36.00

DouglasE.Comer著，徐良贤译

计算机网络与互联网

电子工业出版社

￥28.00

使用EXISTS关键字引入一个子查询时，就相当于进行一次存在测试。

外部查询的WHERE子句测试子查询返回的行是否存在。

子查询实际上不产生任何数据；

它只返回TRUE或FALSE值。

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

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

列出未参加微机原理及应用课程期末考试的学生

SELECT姓名FROM学生

WHEREEXISTS（SELECT*FROM教与学

WHERE学生.学号=教与学.学号AND

课程编号='

AND

期末IsNull）

7.2.4限定测试

可以用ALL或ANY关键字修改引入子查询的比较运算符。

由带修改的比较运算符引入的子查询返回一列零值或更多值，并且可以包括GROUPBY或HAVING子句。

以>

比较运算符为例，>

ALL表示大于每一个值；

换句话说，大于最大值。

例如，>

ALL（1,2,3）表示大于3。

>

ANY表示至少大于一个值，也就是大于最小值。

因此>

ANY（1,2,3）表示大于1。

要使带有>

ALL的子查询中的某行满足外部查询中指定的条件，引入子查询的列中的值必须大于由子查询返回的值的列表中的每个值。

同样，>

ANY表示要使某一行满足外部查询中指定的条件，引入子查询的列中的值必须至少大于由子查询返回的值的列表中的一个值。

7.2.4.1ANY测试

如果任何一个的测试比较产生TRUE的结果，那么ANY测试就返回TRUE。

列出这样的学生：

他有一门课的成绩大于等于90分

SELECT课程.名称,教与学.学号,学生.姓名,教与学.成绩

FROM（教与学INNERJOIN学生ON教与学.学号=学生.学号）INNERJOIN课程ON教与学.课程编号=课程.编号

WHERE（（（教与学.成绩）>

=90））

ORDERBY教与学.学号

成绩

微机原理及应用

92

网络与数据库基础

94

93

96

SELECT姓名

FROM学生

WHERE（90<

=Any（SELECT成绩

FROM教与学

WHERE学生.学号=教与学.学号））

ANY测试有时很难理解，这是因为它涉及到完整的比较组，而不仅仅是一个。

如果用稍微不同于语句中的方法阅读这种测试，则有助于理解ANY测试。

如果ANY测试为：

Wherex<

any（selecty……）

不要读成：

Wherexislessthenanyselecty……

而要读成：

Where,forsomey,xislesstheny

●如果子查询生成空字段的查询结果，那么ANY测试返回FASLE——子查询没有生成比较测试需要的值。

●如果比较测试至少对字段中的一个数值产生了TRUE，那么ANY搜索条件返回TRUE——子查询生成比较测试所需要的值。

●如果比较测试对字段中每个数值均产生FALSE，那么ANY搜索条件返回FALSE。

在这种情况下，你可以确定子查询没有生成比较测试所需要的值。

●如果比较测试对字段中任何数值均不产生TRUE，但它对一个或更多的数值产生NULL（未知）值，那么ANY搜索条件返回NULL值。

在这种情况下，不能确定子查询是否生成了比较测试所需要的值，这取决于NULL（未知）数据的“正确值”。

列出不是单位负责人的姓名和年龄

SELECT姓名,年龄FROM教师

WHERE（职工号<

ANY

（SELECT负责人FROM单位WHERE负责人ISNotNULL））

年龄

王霞

35

55

56

25

辛蛟奭

徐骏善

41

汪惠芬

42

刘婷婷

28

张友良

65

李东波

52

于敏建

38

王益祥

袁红兵

40

这个结果显然不对！

SELECT负责人FROM单位WHERE负责人ISNotNULL

负责人

90001

05094

徐骏善不是负责人，他的雇员代码是20998，20998不等于（90001，90001，05094）中的任何一个，所以徐骏善应该出现在查询结果中。

吴慈仁（90001）是负责人，他就不应该出现在查询结果中，但是他确实出现了。

90001≠90001结果为TRUE

90001≠05094结果为FALSE

只要有一个比较的结果为TRUE，ANY测试的结果就是TURE！

注意：

<

>

ANY运算符与NOTIN有所不同：

ANY表示不等于a，或不等于b，或不等于c。

而NOTIN表示不等于a，且不等于b，且不等于c。

但<

ALL与NOTIN意义相同。

正确的查询语句是：

WHERE（NOT（职工号=ANY

（SELECT负责人FROM单位WHERE负责人ISNotNULL）））

ANY子查询总是可通过EXISTS重新表述。

方法是将比较转到子查询的搜索条件中。

SELECT姓名,年龄

FROM教师

WHERE（NOTEXISTS

（SELECT*

FROM单位

WHERE职工号=负责人））

7.2.4.2ALL测试

要执行这种测试，SQL用特定的比较运算符去比较测试值和该字段中的每一个数值，一次一个。

如果所有的比较均产生TRUE的结果，则ALL测试返回TRUE。

他所有课程的成绩均大于等于90分

=All（SELECT成绩

ALL测试跟ANY测试一样难于理解，这是因为它涉及整组数据的比较，而不仅仅是一个。

再次说明一下，用一种稍微不同于语句的方式阅读该测试有助于对它的理解。

如果ALL测试为：

all（selecty……）

Wherexislessthenallselecty……