数据库应用03 access中sql查询语言

3.2.3多表自然连接查询1.多表自然连接查询的工作原理

多表查询指FROM子句包括多个数据表。为简化问题，先看两个表的查询

.两表自然连接查询工作原理【例3-33】

select*

from班级表,学生表;

结果非常庞大，有17个字段，180条记录，原因：如果SELECT不带WHERE子句时，首先从前一个表（此处为“班级表”）中取一条记录然后与后面的表（此处为“学生表”）中的每条记录进行逐一连接再从第一张表取第二条记录，重复上述过程，直到第一张表的记录取完结束结果存在组合爆炸的问题，产生很多无用的垃圾数据.两表自然连接查询工作原理2二个表之间自然连接（内连）与主键、外键约束的关系：SELECT从两表检索结果时，要得到有效的数据必须带WHERE子句，通常二个表之间必须有主外键的约束，即二个表中有共同的字段（或字段集），这个字段或字段集在一个表中为主键，在另一个表中为外键。如果二表是多对多的关系，也可以转化为二个一对多的关系。.两表自然连接查询【例3-34】【例3-34】将学生表和班级表进行自然连接，求结果集。

.将学生表和班级表进行自然连接select*

from班级表innerjoin学生表

on班级表.班号=学生表.班号;上述SELECT语句的等价SQL语句如下：

select*;

from班级表,学生表

where班级表.班号=学生表.班号;.两表自然连接查询【例3-34】2例3-34中的子句“from班级表innerjoin学生表on班级表.班号=学生表.班号”或“from班级表,学生表where班级表.班号=学生表.班号”表示的是“班级表”和“学生表”进行自然连接自然连接操作是数据库SQL检索语句中最常用的操作要求参与自然连接操作的两个关系表间存在一对多的约束，即两个关系表间存在外键约束，这样的自然连接才有意义.两表自然连接查询与数据库模式关系仅存在主外键约束的两个表可以进行自然连接

.两表自然连接查询规则一对多的自然连接的结果是一个关系表，关系表的结果为：横向上（字段数）为两表的字段相叠加；纵向上（记录数）以多表的记录为最终结果。以“学生表”与“成绩表”的关联为例，由于“成绩表”中有60条记录，使用SELECT中的子句“WHERE学生表.学号=成绩表.学号”表示将两表进行自然连接操作，其结果中字段为13个（3个在班级表，11个在学生表），记录为60条。

.两表自然连接查询【例3-35】【例3-35】求成绩在85分以上学生的学号、姓名和成绩。

.求成绩在85分以上学生的学号、姓名和成绩select学生表.学号,姓名,成绩

from学生表,成绩表

where学生表.学号=成绩表.学号

and成绩>=85;.两表自然连接查询---别名例子为简化SELECT的书写，SQL中允许使用表的别名，上例可写成如下SELECT语句。selecta.学号,姓名,成绩

from学生表a,成绩表b

wherea.学号=b.学号and成绩>=85;a表示是数据表“学生表”的别名b是数据表“成绩表”的别名

.多表自然连接查询【例3-36】将两表的自然连接推广到多表【例3-36】将“学生表”、“成绩表”和“课程表”进行自然连接，求结果。

.多表自然连接查询【例3-36】2select*

from(学生表innerjoin成绩表

on学生表.学号=成绩表.学号)

innerjoin课程表

on课程表.课程号=成绩表.课程号;本例中可理解为先进行“课程表”与“成绩表”的自然连接，然后再用得到的关系与“学生表”进行自然连接。例3-36的另一种书写方式为：

select*

from学生表,成绩表,课程表

where学生表.学号=成绩表.学号

and课程表.课程号=成绩表.课程号;.多表自然连接查询一般方法必须首先知道数据库模式，才能编写正确的SQL语句数据库导航概念根据给出的已知条件，求需要的数据。已知条件是在一个表中的某个字段取值，所求数据是我们感兴趣的字段。要从已知的数据表出发，通过表间的关联到达目的表，最后根据题目要求筛选相关的字段和记录。.多表的自然连接查询【例3-37】【例3-37】求“杨小建”的成绩表。根据给出的数据库模式，我们知道：已知条件为“学生表”的“姓名”字段其内容等于“杨小建”待求的是“课程表”中的“课程号、课程名”和“成绩表”中的“成绩”.多表的自然连接查询【例3-37】select课程表.课程号,课程名,成绩

from(学生表innerjoin成绩表

on学生表.学号=成绩表.学号)

innerjoin课程表

on课程表.课程号=成绩表.课程号

where姓名=“杨小建”;或selectc.课程号,课程名,成绩

from学生表a

,成绩表b,课程表c

wherea.学号=b.学号andc.课程号=b.课程号and姓名=“杨小建”;.多表的自然连接查询【例3-38】【例3-38】求会计学081班的所有成绩单。分析：已知条件为“班级表”中的“专业名称”待求为“学生表”的“姓名”；“课程”表的“课程号、课程名”；“成绩表”的“成绩”

.多表的自然连接查询【例3-38】2select学生表.学号,姓名,课程表.课程号,课程名,成绩,班级表.班号

from(班级表innerjoin学生表on班级表.班号=学生表.班号)

innerjoin

(课程表innerjoin成绩表on课程表.课程号=成绩表.课程号)

on学生表.学号=成绩表.学号

where班级表.班号=“会计学081”;select学生表.学号,姓名,课程表.课程号,课程名,成绩,班级表.班号

from班级表,学生表,课程表,成绩表Where班级表.班号=学生表.班号and

学生表.学号=成绩表.学号and

班级表.班号=“会计学081”;

.【例3-39】求“会计学081”的《高等数学III》成绩单。.多表自然连接查询【例3-39】【例3-39】求“会计学081”的《高等数学III》成绩单。selectb.学号,姓名,c.课程号,课程名,成绩,a.班号from班级表a,学生表b,课程表c,成绩表dwherea.班号=b.班号andc.课程号=d.课程号andb.学号=d.学号anda.班号="会计学081"and课程名="高等数学III";

.多表查询GROUPBY【例3-40】【例3-40】求每个学生所修的总学分数。

.求每个学生所修的总学分数select姓名,sum(学分)as学分合计from学生表a,成绩表b,课程表cwherea.学号=b.学号andc.课程号=b.课程号groupby姓名orderbysum(学分)desc;.多表查询使用GROUPBY的例子【例3-41】求总学分在11分以上的学生姓名

.求总学分在11分以上的学生姓名select姓名,sum(学分)as学分合计from学生表a,成绩表b,课程表cwherea.学号=b.学号andc.课程号=b.课程号groupby姓名havingsum(学分)>=11;不难看出，HAVING子句的功能是过滤GROUPBY子句的结果。.3.2.4参数查询前面我们编写的查询，运行时直接返回结果，即所有的数据已经编写在SQL语句中。所谓参数查询是指在运行该查询时，系统会要求用户输入所需的参数值。.参数查询【例3-42】【例3-42】用户随机输入入学成绩，使用参数查询求大于该入学成绩的学生学号、姓名和入学成绩。

select学号,姓名,入学成绩

from学生表

where入学成绩>=[请输入入学成绩];运行该查询时，首先弹出一个对话框，在输入620，单击确定后，得到所需的结果。下次运行，根据用户随机输入的值，返回不同结果。.3.2.4参数查询【例3-43】【例3-43】用户随机输入学生姓氏，输出该姓氏的所有学生学号和姓名。

select学号,姓名from学生表where姓名like[请输入姓氏]&"*";

.参数查询【例3-44】【例3-44】随机输入入学成绩的下限和上限，输出入学成绩在下限和上限之间的所有学生学号，姓名和入学成绩。

select学号,姓名,入学成绩from学生表where入学成绩between[请输入入学成绩下限]and[请输入入学成绩上限];.3.2.5SQL子查询与合并查询1.SQL子查询带IN子查询带关系运算符子查询ALL限定词ANY限定词2.合并查询.1.SQL子查询子查询是一个SELECT语句，它嵌套在一个SELECT语句（也可是INSERT语句、DELETE语句或UPDATE语句）的WHERE子句部分子查询的SELECT查询总是使用圆括号括起来。子查询也可嵌套在另一子查询中。有三种语法来创建子查询：（1）WHERE表达式[NOT]IN(子查询语句)（2）WHERE表达式关系运算符[ANY|ALL](子查询语句)（3）WHERE[NOT]EXISTS(子查询语句)

.带IN子查询仅讲解前两种子查询（1）带IN子查询

IN子查询语法结构为：

WHERE表达式[NOT]IN(子查询语句)

.带IN子查询【例3-45】【例3-45】求“会计学”专业的所有的同学学号、姓名和班号。

可以用自然连接实现，也可以借助子查询来完成。先查班级表中“会计学”专业对应的班级号，再对学生表中查找相应班级号的同学学号、姓名和班号。

.带IN子查询【例3-45】2select学号,姓名,班号

from学生表

where班号in(select班号

from班级表

where专业="会计学");

.带IN子查询【例3-46】【例3-46】求没有修“高等数学III”同学的学号和姓名。.【例3-46】求没有修“高等数学III”同学的学号和姓名。select学号,姓名from学生表where学号notin(select学号

from成绩表,课程表

where成绩表.课程号=课程表.课程号and课程名="高等数学III");.带关系运算符子查询（2）带关系运算符子查询语法结构为：WHERE表达式关系运算符[ANY|ALL](子查询语句)当子查询返回单值时，符号ANY或ALL可以省略其他情况下，必须使用ANY或ALL修饰>ANY大于子查询结果中的某个最小值<ANY小于子查询结果中的某个最大值>=ANY大于等于子查询结果中的某个最小值<=ANY小于等于子查询结果中的某个最大值=ANYWhere表达式在子查询结果中的某个值中<>ANY无意义>ALL大于子查询结果中的某个最大值<ALL小于子查询结果中的某个最小值>=ALL大于等于子查询结果中的某个最大值<=ALL小于等于子查询结果中的某个最小值=ALL无意义<>ALLWhere表达式不在子查询结果中的某个值中.带关系运算符子查询【例3-47】【例3-47】求入学成绩高于平均入学成绩的“计算机科学与技术081”同学学号和姓名。求解步骤分为两步，求“计算机科学与技术081”平均入学成绩，求“计算机科学与技术081”入学成绩大于平均入学成绩的学号和姓名。.带关系运算符子查询【例3-47】2select学号,姓名from学生表where班号="计算机科学与技术081"and入学成绩>=(selectavg(入学成绩)from学生表

where班号="计算机科学与技术081");

.带关系运算符子查询

ANY和ALL功能。给出高于“会计学091”班的同学入学成绩的学生的学号、姓名、班号和入学成绩Select学号,姓名,班号,入学成绩From学生表Where入学成绩>any(select入学成绩

from学生表

where班号="会计学091");.Select学号,姓名,班号,入学成绩From学生表Where入学成绩>(selectmin(入学成绩)from学生表

where班号="会计学091");.ANY功能---例3-48【例3-48】求入学成绩小于等于班号为“会计学091”学生的任一入学成绩的学生学号、姓名和入学成绩。select学号,姓名,班号,入学成绩from学生表where入学成绩<=any(select入学成绩

from学生表

where班号="会计学091")

and班号<>"会计学091"orderby入学成绩desc;

.ANY功能【例3-48】3等价于：select学号,姓名,班号,入学成绩from学生表where入学成绩<=(selectmax(入学成绩)from学生表

where班号="会计学091")and班号<>"会计学091"orderby入学成绩desc;

.ALL功能【例3-49】【例3-49】求小于等于所有班号为“会计学091”学生入学成绩的学生学号、姓名和入学成绩。.ALL功能【例3-49】2select学号,姓名,班号,入学成绩from学生表where入学成绩<=all(select入学成绩

from学生表

where班号="会计学091")and班号<>"会计学091"orderby入学成绩desc;结果为小于等于“会计学091”班最小成绩556分的所有非“会计学091”同学。思考：在子查询中使用聚合函数完成上述功能。.2.SQL合并查询SQL合并查询就是将两个SELECT语句的查询结果通过并运算（UNION）合并为一个查询结果。SQL合并查询要求两个查询的字段个数相同，且对应字段的数据类型相同。

.SQL合并查询【例3-50】【例3-50】使用SQL合并查询运算求“会计学081”班和“会计学091”的学号、姓名和班号。

虽然该查询操作可以使用集合包含条件描述，但也可使用SQL合并查询完成。.SQL合并查询【例3-50】2select学号,姓名,班号

from学生表

where班号=“会计学081”

union

select学号,姓名,班号

from学生表

where班号=“会计学091”;.3.2.6交叉表查询交叉表查询是Access特有的SQL查询语句。【例3-51】求给定课程选修总人数,但要给出各班级的人数。transformcount(d.学号)asAAselect课程名,count(d.学号)as选课人数from班级表a,学生表b,课程表c,成绩表dwherea.班号=b.班号andc.课程号=d.课程号andb.学号=d.学号groupby课程名pivota.班号;

.交叉表查询---语句.查询向导编写交叉查询【例3-52】【例3-52】使用查询向导，编写交叉查询。（1）打开Access“教学管理数据库”文件。（2）编写一个称为“查询4张表”的查询，语句如下：selecta.专业,a.班号,d.学号,d.课程号,c.课程名FROM班级表a,学生表b,课程表c,成绩表dwherea.班号=b.班号andc.课程号=d.课程号andb.学号=d.学号;.查询向导写交叉查询【例3-52】2（3）启动查询向导。在出现的“新建查询”对话框中选择“交叉表查询向导”选项后，单击“确定”按钮。.查询向导写交叉查询【例3-52】2.查询向导写交叉查询【例3-52】3（4）选择表或查询包含交叉表查询结果所需字段。.查询向导写交叉查询【例3-52】4（5）选择字段作为行标题。.查询向导写交叉查询【例3-52】5（6）选择字段作为列标题。.查询向导写交叉查询【例3-52】6（7）选择行列交叉点聚合计算方法。.查询向导写交叉查询【例3-52】7（8）指定交叉查询名称。.查询向导写交叉查询【例3-52】8（9）交叉查询结果。.2.交叉查询语法TRANSFORM聚合函数

select语句

PIVOTpivotfield[IN(value1[,value2[,...]])]TRANSFORM语句各部分含义如表3-12所示。transformcount(d.学号)asAAselect课程名,count(d.学号)as选课人数from班级表a,学生表b,课程表c,成绩表dwherea.班号=b.班号andc.课程号=d.课程号andb.学号=d.学号groupby课程名pivota.班号;.3.3数据操纵包括插入(INSERT）、删除(DELETE）和更新(UPDATE）三种。插入、删除和修改操作有可能导致数据违背数据库完整性约束，操作时必须细心。例如本书教学管理数据库中如果成绩表中有某个同学的成绩，学生表和成绩