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

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

select*

from班级表,学生表;

结果非常庞大，有17个字段，180条记录，原因：如果SELECT不带WHERE子句时，首先从前一个表（此处为“班级表”）中取一条记录然后与后面的表（此处为“学生表”）中的每条记录进行逐一连接再从第一张表取第二条记录，重复上述过程，直到第一张表的记录取完结束结果存在组合爆炸的问题，产生很多无用的垃圾数据

牛牛文库文档分享两表自然连接查询工作原理【例3-33】

select*

1两表自然连接查询工作原理2二个表之间自然连接（内连）与主键、外键约束的关系：SELECT从两表检索结果时，要得到有效的数据必须带WHERE子句，通常二个表之间必须有主外键的约束，即二个表中有共同的字段（或字段集），这个字段或字段集在一个表中为主键，在另一个表中为外键。如果二表是多对多的关系，也可以转化为二个一对多的关系。

牛牛文库文档分享两表自然连接查询工作原理2二个表之间自然连接（内连）与主键、2两表自然连接查询【例3-34】【例3-34】将学生表和班级表进行自然连接，求结果集。

牛牛文库文档分享两表自然连接查询【例3-34】【例3-34】将学生表和班3将学生表和班级表进行自然连接select*

from班级表innerjoin学生表

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

select*;

from班级表,学生表

where班级表.班号=学生表.班号;

牛牛文库文档分享将学生表和班级表进行自然连接select*

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

牛牛文库文档分享两表自然连接查询【例3-34】2例3-34中的子句www5两表自然连接查询与数据库模式关系仅存在主外键约束的两个表可以进行自然连接

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

牛牛文库文档分享两表自然连接查询规则一对多的自然连接的结果是一个关系表，关系7两表自然连接查询【例3-35】【例3-35】求成绩在85分以上学生的学号、姓名和成绩。

牛牛文库文档分享两表自然连接查询【例3-35】【例3-35】求成绩在85分8求成绩在85分以上学生的学号、姓名和成绩select学生表.学号,姓名,成绩

from学生表,成绩表

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

and成绩>=85;

牛牛文库文档分享求成绩在85分以上学生的学号、姓名和成绩select学生表9两表自然连接查询---别名例子为简化SELECT的书写，SQL中允许使用表的别名，上例可写成如下SELECT语句。selecta.学号,姓名,成绩

from学生表a,成绩表b

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

牛牛文库文档分享两表自然连接查询---别名例子为简化SELECT的书写，SQ10多表自然连接查询【例3-36】将两表的自然连接推广到多表【例3-36】将“学生表”、“成绩表”和“课程表”进行自然连接，求结果。

牛牛文库文档分享多表自然连接查询【例3-36】将两表的自然连接推广到多表ww11多表自然连接查询【例3-36】2select*

from(学生表innerjoin成绩表

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

innerjoin课程表

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

select*

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

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

and课程表.课程号=成绩表.课程号;

牛牛文库文档分享多表自然连接查询【例3-36】2select*

from12多表自然连接查询一般方法必须首先知道数据库模式，才能编写正确的SQL语句数据库导航概念根据给出的已知条件，求需要的数据。已知条件是在一个表中的某个字段取值，所求数据是我们感兴趣的字段。要从已知的数据表出发，通过表间的关联到达目的表，最后根据题目要求筛选相关的字段和记录。

牛牛文库文档分享多表自然连接查询一般方法必须首先知道数据库模式，才能编写正确13多表的自然连接查询【例3-37】【例3-37】求“杨小建”的成绩表。根据给出的数据库模式，我们知道：已知条件为“学生表”的“姓名”字段其内容等于“杨小建”待求的是“课程表”中的“课程号、课程名”和“成绩表”中的“成绩”

牛牛文库文档分享多表的自然连接查询【例3-37】【例3-37】求“杨小建”的14多表的自然连接查询【例3-37】select课程表.课程号,课程名,成绩

from(学生表innerjoin成绩表

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

innerjoin课程表

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

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

from学生表a

,成绩表b,课程表c

wherea.学号=b.学号andc.课程号=b.课程号and姓名=“杨小建”;

牛牛文库文档分享多表的自然连接查询【例3-37】select课程表.课程号15多表的自然连接查询【例3-38】【例3-38】求会计学081班的所有成绩单。分析：已知条件为“班级表”中的“专业名称”待求为“学生表”的“姓名”；“课程”表的“课程号、课程名”；“成绩表”的“成绩”

牛牛文库文档分享多表的自然连接查询【例3-38】【例3-38】求会计学08116多表的自然连接查询【例3-38】2select学生表.学号,姓名,课程表.课程号,课程名,成绩,班级表.班号

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

innerjoin

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

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

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

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

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

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

牛牛文库文档分享多表的自然连接查询【例3-38】2select学生表.学号17【例3-39】求“会计学081”的《高等数学III》成绩单。

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

牛牛文库文档分享多表自然连接查询【例3-39】【例3-39】求“会计学08119多表查询GROUPBY【例3-40】【例3-40】求每个学生所修的总学分数。

牛牛文库文档分享多表查询GROUPBY【例3-40】【例3-40】求每个20求每个学生所修的总学分数select姓名,sum(学分)as学分合计from学生表a,成绩表b,课程表cwherea.学号=b.学号andc.课程号=b.课程号groupby姓名orderbysum(学分)desc;

牛牛文库文档分享求每个学生所修的总学分数select姓名,sum(学分)21多表查询使用GROUPBY的例子【例3-41】求总学分在11分以上的学生姓名

牛牛文库文档分享多表查询使用GROUPBY的例子【例3-41】求总学分在122求总学分在11分以上的学生姓名select姓名,sum(学分)as学分合计from学生表a,成绩表b,课程表cwherea.学号=b.学号andc.课程号=b.课程号groupby姓名havingsum(学分)>=11;不难看出，HAVING子句的功能是过滤GROUPBY子句的结果。

牛牛文库文档分享求总学分在11分以上的学生姓名select姓名,sum(学233.2.4参数查询前面我们编写的查询，运行时直接返回结果，即所有的数据已经编写在SQL语句中。所谓参数查询是指在运行该查询时，系统会要求用户输入所需的参数值。

牛牛文库文档分享3.2.4参数查询前面我们编写的查询，运行时直接返回结果，24参数查询【例3-42】【例3-42】用户随机输入入学成绩，使用参数查询求大于该入学成绩的学生学号、姓名和入学成绩。

select学号,姓名,入学成绩

from学生表

where入学成绩>=[请输入入学成绩];运行该查询时，首先弹出一个对话框，在输入620，单击确定后，得到所需的结果。下次运行，根据用户随机输入的值，返回不同结果。

牛牛文库文档分享参数查询【例3-42】【例3-42】用户随机输入入学成绩，253.2.4参数查询【例3-43】【例3-43】用户随机输入学生姓氏，输出该姓氏的所有学生学号和姓名。

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

牛牛文库文档分享3.2.4参数查询【例3-43】【例3-43】用户随机输入26参数查询【例3-44】【例3-44】随机输入入学成绩的下限和上限，输出入学成绩在下限和上限之间的所有学生学号，姓名和入学成绩。

select学号,姓名,入学成绩from学生表where入学成绩between[请输入入学成绩下限]and[请输入入学成绩上限];

牛牛文库文档分享参数查询【例3-44】【例3-44】随机输入入学成绩的下限和273.2.5SQL子查询与合并查询1.SQL子查询带IN子查询带关系运算符子查询ALL限定词ANY限定词2.合并查询

牛牛文库文档分享3.2.5SQL子查询与合并查询1.SQL子查询www.281.SQL子查询子查询是一个SELECT语句，它嵌套在一个SELECT语句（也可是INSERT语句、DELETE语句或UPDATE语句）的WHERE子句部分子查询的SELECT查询总是使用圆括号括起来。子查询也可嵌套在另一子查询中。有三种语法来创建子查询：（1）WHERE表达式[NOT]IN(子查询语句)（2）WHERE表达式关系运算符[ANY|ALL](子查询语句)（3）WHERE[NOT]EXISTS(子查询语句)

牛牛文库文档分享1.SQL子查询子查询是一个SELECT语句，它嵌套在一个29带IN子查询仅讲解前两种子查询（1）带IN子查询

IN子查询语法结构为：

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

牛牛文库文档分享带IN子查询仅讲解前两种子查询牛30带IN子查询【例3-45】【例3-45】求“会计学”专业的所有的同学学号、姓名和班号。

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

牛牛文库文档分享带IN子查询【例3-45】【例3-45】求“会计学”专业的所31带IN子查询【例3-45】2select学号,姓名,班号

from学生表

where班号in(select班号

from班级表

where专业="会计学");

牛牛文库文档分享带IN子查询【例3-45】2select学号,姓名,班号32带IN子查询【例3-46】【例3-46】求没有修“高等数学III”同学的学号和姓名。

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

from成绩表,课程表

where成绩表.课程号=课程表.课程号and课程名="高等数学III");

牛牛文库文档分享【例3-46】求没有修“高等数学III”同学的学号和姓名。w34带关系运算符子查询（2）带关系运算符子查询语法结构为：WHERE表达式关系运算符[ANY|ALL](子查询语句)当子查询返回单值时，符号ANY或ALL可以省略其他情况下，必须使用ANY或ALL修饰>ANY大于子查询结果中的某个最小值<ANY小于子查询结果中的某个最大值>=ANY大于等于子查询结果中的某个最小值<=ANY小于等于子查询结果中的某个最大值=ANYWhere表达式在子查询结果中的某个值中<>ANY无意义>ALL大于子查询结果中的某个最大值<ALL小于子查询结果中的某个最小值>=ALL大于等于子查询结果中的某个最大值<=ALL小于等于子查询结果中的某个最小值=ALL无意义<>ALLWhere表达式不在子查询结果中的某个值中

牛牛文库文档分享带关系运算符子查询（2）带关系运算符子查询语法结构为：>A35带关系运算符子查询【例3-47】【例3-47】求入学成绩高于平均入学成绩的“计算机科学与技术081”同学学号和姓名。求解步骤分为两步，求“计算机科学与技术081”平均入学成绩，求“计算机科学与技术081”入学成绩大于平均入学成绩的学号和姓名。

牛牛文库文档分享带关系运算符子查询【例3-47】【例3-47】求入学成绩高于36带关系运算符子查询【例3-47】2select学号,姓名from学生表where班号="计算机科学与技术081"and入学成绩>=(selectavg(入学成绩)from学生表

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

牛牛文库文档分享带关系运算符子查询【例3-47】2select学号,姓名w37带关系运算符子查询

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

from学生表

where班号="会计学091");

牛牛文库文档分享带关系运算符子查询

ANY和ALL功能。给出高于“会计学0938Select学号,姓名,班号,入学成绩From学生表Where入学成绩>(selectmin(入学成绩)from学生表

where班号="会计学091");

牛牛文库文档分享Select学号,姓名,班号,入学成绩www.niuw39ANY功能---例3-48【例3-48】求入学成绩小于等于班号为“会计学091”学生的任一入学成绩的学生学号、姓名和入学成绩。select学号,姓名,班号,入学成绩from学生表where入学成绩<=any(select入学成绩

from学生表

where班号="会计学091")

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

牛牛文库文档分享ANY功能---例3-48【例3-48】www.niuwk.40ANY功能【例3-48】3等价于：select学号,姓名,班号,入学成绩from学生表where入学成绩<=(selectmax(入学成绩)from学生表

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

牛牛文库文档分享ANY功能【例3-48】3等价于：41ALL功能【例3-49】【例3-49】求小于等于所有班号为“会计学091”学生入学成绩的学生学号、姓名和入学成绩。

牛牛文库文档分享ALL功能【例3-49】【例3-49】求小于等于所有班号为“42ALL功能【例3-49】2select学号,姓名,班号,入学成绩from学生表where入学成绩<=all(select入学成绩

from学生表

where班号="会计学091")and班号<>"会计学091"orderby入学成绩desc;结果为小于等于“会计学091”班最小成绩556分的所有非“会计学091”同学。思考：在子查询中使用聚合函数完成上述功能。

牛牛文库文档分享ALL功能【例3-49】2select学号,姓名,班号432.SQL合并查询SQL合并查询就是将两个SELECT语句的查询结果通过并运算（UNION）合并为一个查询结果。SQL合并查询要求两个查询的字段个数相同，且对应字段的数据类型相同。

牛牛文库文档分享2.SQL合并查询SQL合并查询就是将两个SELECT语句44SQL合并查询【例3-50】【例3-50】使用SQL合并查询运算求“会计学081”班和“会计学091”的学号、姓名和班号。

虽然该查询操作可以使用集合包含条件描述，但也可使用SQL合并查询完成。

牛牛文库文档分享SQL合并查询【例3-50】【例3-50】使用SQL合并查45SQL合并查询【例3-50】2select学号,姓名,班号

from学生表

where班号=“会计学081”

union

select学号,姓名,班号

from学生表

where班号=“会计学091”;

牛牛文库文档分享SQL合并查询【例3-50】2牛463.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.2.6交叉表查询交叉表查询是Access特有的SQL查47交叉表查询---语句

牛牛文库文档分享交叉表查询---语句牛牛文库文档48查询向导编写交叉查询【例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】【例3-52】使用查询向导49查询向导写交叉查询【例3-52】2（3）启动查询向导。在出现的“新建查询”对话框中选择“交叉表查询向导”选项后，单击“确定”按钮。

牛牛文库文档分享查询向导写交叉查询【例3-52】2（3）启动查询向导。在出现50查询向导写交叉查询【例3-52】2

牛牛文库文档分享查询向导写交叉查询【例3-52】251查询向导写交叉查询【例3-52】3（4）选择表或查询包含交叉表查询结果所需字段。

牛牛文库文档分享查询向导写交叉查询【例3-52】3（4）选择表或查询包含交叉52查询向导写交叉查询【例3-52】4（5）选择字段作为行标题。

牛牛文库文档分享查询向导写交叉查询【例3-52】4（5）选择字段作为行标题。53查询向导写交叉查询【例3-52】5（6）选择字段作为列标题。

牛牛文库文档分享查询向导写交叉查询【例3-52】5（6）选择字段作为列标题。54查询向导写交叉查询【例3-52】6（7）选择行列交叉点聚合计算方法。

牛牛文库文档分享查询向导写交叉查询【例3-52】6（7）选择行列交叉点聚合计55查询向导写交叉查询【例3-52】7（8）指定交叉查询名称。

牛牛文库文档分享查询向导写交叉查询【例3-52】7（8）指定交叉查询名称。w56查询向导写交叉查询【例3-52】8（9）交叉查询结果。

牛牛文库文档分享查询向导写交叉查询【例3-52】8（9）交叉查询结果。www572.交叉查询语法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.班号;

牛牛文库文档分享2.交叉查询语法TRANSFORM聚合函数

583.3数据操纵包括插入(INSERT）、删除(DELETE）和更新(UPDATE）三种。插入、删除和修改操作有可能导致数据违背数据库完整性约束，操作时必须细心。例如本书教学管理数据库中如果成绩表中有某个同学的成绩，学生表和成绩表之间有外键约束，则不能够先删除学生表中的学生记录同样插入数据记录也存在先后顺序问题

牛牛文库文档分享3.3数据操纵包括插入(INSERT）、删除(DELETE591.插入记录INSERT语句格式：INSERTINTO<表名>[(<字段名1>[,<字段名2>,...])]VALUES(<表达式1>[,<表达式2>,...])命令功能：向表中插入一条记录

牛牛文库文档分享1.插入记录INSERT语句格式：www.niuwk.co60插入记录【例3-53】【例3-53】向学生表中插入一条记录。insertinto学生表(学号,姓名,性别,出生日期,入学成绩,民族,籍贯,班号,贷款否)values("s0082999","李莉","女",1989/06/01,588,"汉","江西南昌","会计学081",false);使用该语句时，如果违法数据表的约束，即表中要求不能为空的或主键冲突的数据将不能插入数据表中。

牛牛文库文档分享插入记录【例3-53】【例3-53】向学生表中插入一条记录。612.更新记录语句格式：

UPDATE<表名1>

SET<字段名1>=<表达式1>

[,<字段名2>=<表达式2>...]

WHERE<条件1>[AND|OR<条件2>...]

UPDATE命令功能：更新表中满足条件的记录。

牛牛文库文档分享2.更新记录语句格式：

UPDATE<表名1>62更新记录【例3-54】【例3-54】将学号为S0082999的学生入学成绩改为612分。update学生表set入学成绩=612where学号=‘S0082999’;注意：由于UPDATE语句对数据表更新是不可逆的。所以，UPDATE语句中的WHERE条件必须仔细写好。

牛牛文库文档分享更新记录【例3-54】【例3-54】将学号为S0082999633.删除记录语句格式：

DELETE[table.*]

FROMtable

WHERE<条件1>[AND|OR<条件2>...]

DELETEFROM命令的功能是从表中删除满足条件的记录。

牛牛文库文档分享3.删除记录语句格式：

DELETE[table64删除记录【例3-55】【例3-55】删除学号为S0082999的学生。Deletefrom学生表where学号=“S0082999”;由于delete删除语句同样不可逆，使用时需细心。

牛牛文库文档分享删除记录【例3-55】【例3-55】删除学号为S00829965两表自然连接查询工作原理【例3-33】

select*

from班级表,学生表;

结果非常庞大，有17个字段，180条记录，原因：如果SELECT不带WHERE子句时，首先从前一个表（此处为“班级表”）中取一条记录然后与后面的表（此处为“学生表”）中的每条记录进行逐一连接再从第一张表取第二条记录，重复上述过程，直到第一张表的记录取完结束结果存在组合爆炸的问题，产生很多无用的垃圾数据

牛牛文库文档分享两表自然连接查询工作原理【例3-33】

select*

66两表自然连接查询工作原理2二个表之间自然连接（内连）与主键、外键约束的关系：SELECT从两表检索结果时，要得到有效的数据必须带WHERE子句，通常二个表之间必须有主外键的约束，即二个表中有共同的字段（或字段集），这个字段或字段集在一个表中为主键，在另一个表中为外键。如果二表是多对多的关系，也可以转化为二个一对多的关系。

牛牛文库文档分享两表自然连接查询工作原理2二个表之间自然连接（内连）与主键、67两表自然连接查询【例3-34】【例3-34】将学生表和班级表进行自然连接，求结果集。

牛牛文库文档分享两表自然连接查询【例3-34】【例3-34】将学生表和班68将学生表和班级表进行自然连接select*

from班级表innerjoin学生表

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

select*;

from班级表,学生表

where班级表.班号=学生表.班号;

牛牛文库文档分享将学生表和班级表进行自然连接select*

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

牛牛文库文档分享两表自然连接查询【例3-34】2例3-34中的子句www70两表自然连接查询与数据库模式关系仅存在主外键约束的两个表可以进行自然连接

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

牛牛文库文档分享两表自然连接查询规则一对多的自然连接的结果是一个关系表，关系72两表自然连接查询【例3-35】【例3-35】求成绩在85分以上学生的学号、姓名和成绩。

牛牛文库文档分享两表自然连接查询【例3-35】【例3-35】求成绩在85分73求成绩在85分以上学生的学号、姓名和成绩select学生表.学号,姓名,成绩

from学生表,成绩表

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

and成绩>=85;

牛牛文库文档分享求成绩在85分以上学生的学号、姓名和成绩select学生表74两表自然连接查询---别名例子为简化SELECT的书写，SQL中允许使用表的别名，上例可写成如下SELECT语句。selecta.学号,姓名,成绩

from学生表a,成绩表b

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

牛牛文库文档分享两表自然连接查询---别名例子为简化SELECT的书写，SQ75多表自然连接查询【例3-36】将两表的自然连接推广到多表【例3-36】将“学生表”、“成绩表”和“课程表”进行自然连接，求结果。

牛牛文库文档分享多表自然连接查询【例3-36】将两表的自然连接推广到多表ww76多表自然连接查询【例3-36】2select*

from(学生表innerjoin成绩表

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

innerjoin课程表

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

select*

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

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

and课程表.课程号=成绩表.课程号;

牛牛文库文档分享多表自然连接查询【例3-36】2select*

from77多表自然连接查询一般方法必须首先知道数据库模式，才能编写正确的SQL语句数据库导航概念根据给出的已知条件，求需要的数据。已知条件是在一个表中的某个字段取值，所求数据是我们感兴趣的字段。要从已知的数据表出发，通过表间的关联到达目的表，最后根据题目要求筛选相关的字段和记录。

牛牛文库文档分享多表自然连接查询一般方法必须首先知道数据库模式，才能编写正确78多表的自然连接查询【例3-37】【例3-37】求“杨小建”的成绩表。根据给出的数据库模式，我们知道：已知条件为“学生表”的“姓名”字段其内容等于“杨小建”待求的是“课程表”中的“课程号、课程名”和“成绩表”中的“成绩”

牛牛文库文档分享多表的自然连接查询【例3-37】【例3-37】求“杨小建”的79多表的自然连接查询【例3-37】select课程表.课程号,课程名,成绩

from(学生表innerjoin成绩表

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

innerjoin课程表

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

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

from学生表a

,成绩表b,课程表c

wherea.学号=b.学号andc.课程号=b.课程号and姓名=“杨小建”;

牛牛文库文档分享多表的自然连接查询【例3-37】select课程表.课程号80多表的自然连接查询【例3-38】【例3-38】求会计学081班的所有成绩单。分析：已知条件为“班级表”中的“专业名称”待求为“学生表”的“姓名”；“课程”表的“课程号、课程名”；“成绩表”的“成绩”

牛牛文库文档分享多表的自然连接查询【例3-38】【例3-38】求会计学08181多表的自然连接查询【例3-38】2select学生表.学号,姓名,课程表.课程号,课程名,成绩,班级表.班号

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

innerjoin

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

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

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

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

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

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

牛牛文库文档分享多表的自然连接查询【例3-38】2select学生表.学号82【例3-39】求“会计学081”的《高等数学III》成绩单。

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

牛牛文库文档分享多表自然连接查询【例3-39】【例3-39】求“会计学08184多表查询GROUPBY【例3-40】【例3-40】求每个学生所修的总学分数。

牛牛文库文档分享多表查询GROUPBY【例3-40】【例3-40】求每个85求每个学生所修的总学分数select姓名,sum(学分)as学分合计from学生表a,成绩表b,课程表cwherea.学号=b.学号andc.课程号=b.课程号groupby姓名orderbysum(学分)desc;

牛牛文库文档分享求每个学生所修的总学分数select姓名,sum(学分)86多表查询使用GROUPBY的例子【例3-41】求总学分在11分以上的学生姓名

牛牛文库文档分享多表查询使用GROUPBY的例子【例3-41】求总学分在187求总学分在11分以上的学生姓名select姓名,sum(学分)as学分合计from学生表a,成绩表b,课程表cwherea.学号=b.学号andc.课程号=b.课程号groupby姓名havingsum(学分)>=11;不难看出，HAVING子句的功能是过滤GROUPBY子句的结果。

牛牛文库文档分享求总学分在11分以上的学生姓名select姓名,sum(学883.2.4参数查询前面我们编写的查询，运行时直接返回结果，即所有的数据已经编写在SQL语句中。所谓参数查询是指在运行该查询时，系统会要求用户输入所需的参数值。

牛牛文库文档分享3.2.4参数查询前面我们编写的查询，运行时直接返回结果，89参数查询【例3-42】【例3-42】用户随机输入入学成绩，使用参数查询求大于该入学成绩的学生学号、姓名和入学成绩。

select学号,姓名,入学成绩

from学生表

where入学成绩>=[请输入入学成绩];运行该查询时，首先弹出一个对话框，在输入620，单击确定后，得到所需的结果。下次运行，根据用户随机输入的值，返回不同结果。

牛牛文库文档分享参数查询【例3-42】【例3-42】用户随机输入入学成绩，903.2.4参数查询【例3-43】【例3-43】用户随机输入学生姓氏，输出该姓氏的所有学生学号和姓名。

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

牛牛文库文档分享3.2.4参数查询【例3-43】【例3-43】用户随机输入91参数查询【例3-44】【例3-44】随机输入入学成绩的下限和上限，输出入学成绩在下限和上限之间的所有学生学号，姓名和入学成绩。

select学号,姓名,入学成绩from学生表where入学成绩between[请输入入学成绩下限]and[请输入入学成绩上限];

牛牛文库文档分享参数查询【例3-44】【例3-44】随机输入入学成绩的下限和923.2.5SQL子查询与合并查询1.SQL子查询带IN子查询带关系运算符子查询ALL限定词ANY限定词2.合并查询

牛牛文库文档分享3.2.5SQL子查询与合并查询1.SQL子查询www.931.SQL子查询子查询是一个SELECT语句，它嵌套在一个SELECT语句（也可是INSERT语句、DELETE语句或UPDATE语句）的WHERE子句部分子查询的SELECT查询总是使用圆括号括起来。子查询也可嵌套在另一子查询中。有三种语法来创建子查询：（1）WHERE表达式[NOT]IN(子查询语句)（2）WHERE表达式关系运算符[ANY|ALL](子查询语句)（3）WHERE[NOT]EXISTS(子查询语句)

牛牛文库文档分享1.SQL子查询子查询是一个SELECT语句，它嵌套在一个94带IN子查询仅讲解前两种子查询（1）带IN子查询

IN子查询语法结构为：

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

牛牛文库文档分享带IN子查询仅讲解前两种子查询牛95带IN子查询【例3-45】【例3-45】求“会计学”专业的所有的同学学号、姓名和班号。

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

牛牛文库文档分享带IN子查询【例3-45】【例3-45】求“会计学”专业的所96带IN子查询【例3-45】2select学号,姓名,班号

from学生表

where班号in(select班号

from班级表

where专业="会计学");

牛牛文库文档分享带IN子查询【例3-45】2select学号,姓名,班号97带IN子查询【例3-46】【例3-46】求没有修“高等数学III”同学的学号和姓名。

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

from成绩表,课程表

where成绩表.课程号=课程表.课程号and课程名="高等数学III");

牛牛文库文档分享【例3-46】求没有修“高等数学III”同学的学号和姓名。w99带关系运算符子查询（2）带关系运算符子查询语法结构为：WHERE表达式关系运算符[ANY|ALL](子查询语句)当子查询返回单值时，符号ANY或ALL可以省略其他情况下，必须使用ANY或ALL修饰>ANY大于子查询结果中的某个最小值<ANY小于子查询结果中的某个最大值>=ANY大于等于子查询结果中的某个最小值<=ANY小于等于子查询结果中的某个最大值=ANYWhere表达式在子查询结果中的某个值中<>ANY无意义>ALL大于子查询结果中的某个最大值<ALL小于子查询结果中的某个最小值>=ALL大于等于子查询结果中的某个最大值<=ALL小于等于子查询结果中的某个最小值=ALL无意义<>ALLWhere表达式不在子查询结果中的某个值中

牛牛文库文档分享带关系运算符子查询（2）带关系运算符子查询语法结构为：>A100带关系运算符子查询【例3-47】【例3-47】求入学成绩高于平均入学成绩的“计算机科学与技术081”同学学号和姓名。求解步骤分为两步，求“计算机科学与技术081”平均入学成绩，求“计算机科学与技术081”入学成绩大于平均入学成绩的学号和姓名。

牛牛文库文档分享带关系运算符子查询【例3-47】【例3-47】求入学成绩高于101带关系运算符子查询【例3-47】2select学号,姓名from学生表where班号="计算机科学与技术081"and入学成绩>=(selectavg(入学成绩)from学生表

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

牛牛文库文档分享带关系运算符子查询【例3-47】2select学号,姓名w102带关系运算符子查询

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

from学生表

where班号="会计学091");

牛牛文库文档分享带关系运算符子查询

ANY和ALL功能。给出高于“会计学09103Select学号,姓名,班号,入学成绩From学生表Where入学成绩>(selectmin(入学成绩)from学生表

where班号="会计学091");

牛牛文库文档分享Select学号,姓名,班号,入学成绩www.niuw104ANY功能---例3-48【例3-48】求入学成绩小于等于班号为“会计学091”学生的任一入学成绩的学生学号、姓名和入学成绩。select学号,姓名,班号,入学成绩from学生表where入学成绩<=any(select入学成绩

from学生表

where班号="会计学091")

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

牛牛文库文档分享ANY功能---例3-48【例3-48】www.niuwk.105ANY功能【例3-48】3等价于：select学号,姓名,班号,入学成绩from学生表where入学成绩<=(selectmax(入学成绩)from学生表

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

牛牛文库文档分享ANY功能【例3-48】3等价于：106ALL功能【例3-49】【例3-49】求小于等于所有班号为“会计学091”学生入学成绩的学生学号、姓名和入学成绩。

牛牛文库文档分享ALL功能【例3-49】【例3-49】求小于等于所有班号为“107ALL功能【例3-49】2select学号,姓名,班号,入学成绩from学生表where入学成绩<=all(select入学成绩

from学生表

where班号="会计学091")and班号<>"会计学091"orderby入学成绩desc;结果为小于等于“会计学091”班最小成绩556分的所有非“会计学091”同学。思考：在子查询中使用聚合函数完成上述功能。

牛牛文库文档分享ALL功能【例3-49】2select学号,姓名,班号1082.SQL合并查询SQL合并查询就是将两个SELECT语句的查询结果通过并运算（UNION）合并为一个查询结果。SQL合并查询要求两个查询的字段个数相同，且对应字段的数据类型相同。

牛牛文库文档分享2.SQL合并查询SQL合并查询就是将两个SELECT语句109SQL合并查询【例3-50】【例3-50】使用SQL合并查询运算求“会计学081”班和“会计学091”的学号、姓名和班号。

虽然该查询操作可以使用集合包含条件描述，但也可使用SQL合并查询完成。

牛牛文库文档分享SQL合并查询【例3-50】【例3-50】使用SQL合并查110SQL合并查询【例3-50】2select学号,姓名,班号

from学生表

where班号=“会计学081”

union

select学号,姓名,班号

from学生表

where班号=“会计学091”;

牛牛文库文档分享SQL合并查询【例3-50】2牛1113.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.2.6交叉表查询交叉表查询是Access特有的SQL查112交叉表查询---语句

牛牛文库文档分享交叉表查询---语句牛牛文库文档113查询向导编写交叉查询【例3-52】【例3-52】使用查询向导，编写交叉查询。（1）打开Access“教学管理数据库”文件。（2）编写一个称为“查询4张表”的查询，语句如下：selecta.专业,a.班号,d.学号,d.课程号,c.课程名FROM班级表a,学生表b,课程表c,成绩表dwherea.班号=b.班号andc.课程号=d.课程号and