《数据库应用03 access中sql查询语言.pptx》由会员分享，可在线阅读，更多相关《数据库应用03 access中sql查询语言.pptx（66页珍藏版）》请在三一文库上搜索。
1、3.2.3 多表自然连接查询,1. 多表自然连接查询的工作原理多表查询指FROM子句包括多个数据表。 为简化问题，先看两个表的查询,两表自然连接查询工作原理,【例3-33】select * from 班级表, 学生表;结果非常庞大，有17个字段，180条记录，原因： 如果SELECT不带WHERE子句时，首先从前一个表（此处为“班级表”）中取一条记录 然后与后面的表（此处为“学生表”）中的每条记录进行逐一连接 再从第一张表取第二条记录，重复上述过程，直到第一张表的记录取完结束 结果存在组合爆炸的问题，产生很多无用的垃圾数据,两表自然连接查询工作原理2,二个表之间自然连接（内连）与主键、外键约束
2、的关系： SELECT从两表检索结果时，要得到有效的数据必须带WHERE子句， 通常二个表之间必须有主外键的约束，即二个表中有共同的字段（或字段集）， 这个字段或字段集在一个表中为主键， 在另一个表中为外键。 如果二表是多对多的关系，也可以转化为二个一对多的关系。,两表自然连接查询 【例3-34】,【例3-34】将学生表和班级表进行自然连接，求结果集。,将学生表和班级表进行自然连接,select *from 班级表 inner join 学生表 on 班级表.班号 = 学生表.班号; 上述SELECT语句的等价SQL语句如下： select * ; from班级表, 学生表 where班级表.
3、班号 = 学生表.班号;,两表自然连接查询 【例3-34】 2,例3-34中的子句 “from 班级表 inner join学生表on班级表.班号 = 学生表.班号” 或“from班级表, 学生表 where班级表.班号 = 学生表.班号” 表示的是“班级表”和“学生表”进行自然连接 自然连接操作是数据库SQL检索语句中最常用的操作 要求参与自然连接操作的两个关系表间存在一对多的约束， 即两个关系表间存在外键约束，这样的自然连接才有意义,两表自然连接查询与数据库模式关系,仅存在主外键约束的两个表可以进行自然连接,两表自然连接查询规则,一对多的自然连接的结果是一个关系表，关系表的结果为： 横向上
4、（字段数）为两表的字段相叠加； 纵向上（记录数）以多表的记录为最终结果。 以“学生表”与“成绩表”的关联为例，由于“成绩表”中有60条记录，使用SELECT中的子句“WHERE 学生表.学号 = 成绩表.学号”表示将两表进行自然连接操作，其结果中字段为13个（3个在班级表，11个在学生表），记录为60条。,两表自然连接查询【例3-35】,【例3-35】求成绩在85分以上学生的学号、姓名和成绩。,求成绩在85分以上学生的学号、姓名和成绩,select 学生表.学号,姓名,成绩from 学生表,成绩表where 学生表.学号 = 成绩表.学号 and 成绩=85;,两表自然连接查询-别名例子,为简
5、化SELECT的书写，SQL中允许使用表的别名，上例可写成如下SELECT语句。 select a.学号, 姓名, 成绩from 学生表 a,成绩表 b where a.学号 = b.学号 and 成绩 = 85; a表示是数据表“学生表”的别名 b是数据表“成绩表”的别名,多表自然连接查询【例3-36】,将两表的自然连接推广到多表 【例3-36】将“学生表”、“成绩表”和“课程表”进行自然连接，求结果。,多表自然连接查询【例3-36】2,select *from (学生表 inner join 成绩表 on 学生表.学号 = 成绩表.学号) inner join 课程表 on 课程表.课程号
6、 = 成绩表.课程号; 本例中可理解为先进行“课程表”与“成绩表”的自然连接，然后再用得到的关系与“学生表”进行自然连接。 例3-36的另一种书写方式为：select *from 学生表,成绩表,课程表where 学生表.学号 = 成绩表.学号 and 课程表.课程号 = 成绩表.课程号;,多表自然连接查询一般方法,必须首先知道数据库模式，才能编写正确的SQL语句 数据库导航概念 根据给出的已知条件，求需要的数据。 已知条件是在一个表中的某个字段取值，所求数据是我们感兴趣的字段。 要从已知的数据表出发，通过表间的关联到达目的表，最后根据题目要求筛选相关的字段和记录。,多表的自然连接查询【例3-
7、37】,【例3-37】求“杨小建”的成绩表。 根据给出的数据库模式，我们知道： 已知条件为“学生表”的“姓名”字段 其内容等于“杨小建” 待求的是“课程表”中的“课程号、课程名”和“成绩表”中的“成绩”,多表的自然连接查询【例3-37】,select 课程表.课程号, 课程名, 成绩 from (学生表 inner join 成绩表 on 学生表.学号 = 成绩表.学号) inner join 课程表 on 课程表.课程号 = 成绩表.课程号 where 姓名 = “杨小建”; 或 select c.课程号, 课程名, 成绩 from 学生表 a ,成绩表 b ,课程表 c where a.学
8、号 = b.学号 and c.课程号 = b.课程号 and 姓名 = “杨小建” ;,多表的自然连接查询【例3-38】,【例3-38】求会计学081班的所有成绩单。 分析： 已知条件为“班级表”中的“专业名称” 待求为“学生表”的“姓名”； “课程”表的“课程号、课程名”； “成绩表”的“成绩”,多表的自然连接查询【例3-38】2,select 学生表.学号, 姓名, 课程表.课程号, 课程名, 成绩, 班级表.班号from (班级表 inner join 学生表 on 班级表.班号 = 学生表.班号) inner join (课程表 inner join 成绩表 on 课程表.课程号 =
9、成绩表.课程号) on 学生表.学号 = 成绩表.学号 where 班级表.班号=“会计学081”; select 学生表.学号, 姓名, 课程表.课程号, 课程名, 成绩, 班级表.班号from 班级表 ,学生表 ,课程表,成绩表 Where 班级表.班号 = 学生表.班号 and 学生表.学号 = 成绩表.学号 and 班级表.班号=“会计学081”;,【例3-39】求“会计学081”的高等数学III成绩单。,多表自然连接查询【例3-39】,【例3-39】求“会计学081”的高等数学III成绩单。 select b.学号, 姓名, c.课程号, 课程名, 成绩, a.班号 from 班级表
10、 a,学生表 b,课程表 c,成绩表 d where a.班号 = b.班号 and c.课程号 = d.课程号 and b.学号 = d.学号 and a.班号 =会计学081 and 课程名 = 高等数学III;,多表查询GROUP BY 【例3-40】,【例3-40】求每个学生所修的总学分数。,求每个学生所修的总学分数,select 姓名,sum(学分) as 学分合计 from 学生表 a, 成绩表 b,课程表 c where a.学号 = b.学号 and c.课程号 = b.课程号 group by 姓名 order by sum(学分) desc;,多表查询使用GROUP BY的
11、例子,【例3-41】求总学分在11分以上的学生姓名,求总学分在11分以上的学生姓名,select 姓名,sum(学分) as 学分合计 from 学生表 a, 成绩表 b,课程表 c where a.学号 = b.学号 and c.课程号 = b.课程号 group by 姓名 having sum(学分)= 11; 不难看出，HAVING子句的功能是过滤GROUP BY子句的结果。,3.2.4 参数查询,前面我们编写的查询，运行时直接返回结果，即所有的数据已经编写在SQL语句中。所谓参数查询是指在运行该查询时，系统会要求用户输入所需的参数值。,参数查询【例3-42】,【例3-42】用户随机输
12、入入学成绩，使用参数查询求大于该入学成绩的学生学号、姓名和入学成绩。select 学号, 姓名, 入学成绩from 学生表 where 入学成绩 = 请输入入学成绩; 运行该查询时，首先弹出一个对话框，在输入620，单击确定后，得到所需的结果。 下次运行，根据用户随机输入的值，返回不同结果。,3.2.4 参数查询【例3-43】,【例3-43】用户随机输入学生姓氏，输出该姓氏的所有学生学号和姓名。 select 学号, 姓名 from 学生表 where 姓名 like 请输入姓氏 ,参数查询【例3-44】,【例3-44】随机输入入学成绩的下限和上限，输出入学成绩在下限和上限之间的所有学生学号，
13、姓名和入学成绩。select 学号, 姓名, 入学成绩 from 学生表 where 入学成绩 between 请输入入学成绩下限 and 请输入入学成绩上限;,3.2.5 SQL子查询与合并查询,1. SQL子查询 带IN子查询 带关系运算符子查询 ALL限定词 ANY限定词 2. 合并查询,1. SQL子查询,子查询是一个SELECT语句，它嵌套在一个SELECT语句（也可是INSERT语句、DELETE语句或UPDATE语句）的WHERE子句部分 子查询的SELECT查询总是使用圆括号括起来。子查询也可嵌套在另一子查询中。有三种语法来创建子查询： （1）WHERE表达式 NOT IN (
14、子查询语句) （2）WHERE 表达式 关系运算符 ANY | ALL (子查询语句) （3）WHERE NOT EXISTS (子查询语句),带IN子查询,仅讲解前两种子查询 （1）带IN子查询IN子查询语法结构为：WHERE表达式 NOT IN (子查询语句),带IN子查询【例3-45】,【例3-45】求“会计学”专业的所有的同学学号、姓名和班号。可以用自然连接实现，也可以借助子查询来完成。 先查班级表中“会计学”专业对应的班级号，再对学生表中查找相应班级号的同学学号、姓名和班号。,带IN子查询【例3-45】 2,select 学号,姓名,班号from 学生表where 班号 in (se
15、lect 班号 from 班级表 where 专业 = 会计学);,带IN子查询【例3-46】,【例3-46】求没有修“高等数学III”同学的学号和姓名。,【例3-46】求没有修“高等数学III”同学的学号和姓名。 select 学号, 姓名 from 学生表 where 学号 not in (select 学号 from 成绩表,课程表 where 成绩表.课程号 = 课程表.课程号 and 课程名 = 高等数学III);,带关系运算符子查询,（2）带关系运算符子查询语法结构为： WHERE 表达式 关系运算符 ANY | ALL (子查询语句) 当子查询返回单值时，符号ANY或ALL可以省
16、略 其他情况下，必须使用ANY或ALL修饰,带关系运算符子查询【例3-47】,【例3-47】求入学成绩高于平均入学成绩的“计算机科学与技术081”同学学号和姓名。 求解步骤分为两步， 求“计算机科学与技术081”平均入学成绩， 求“计算机科学与技术081”入学成绩大于平均入学成绩的学号和姓名。,带关系运算符子查询【例3-47】2,select 学号,姓名 from 学生表 where 班号=计算机科学与技术081 and 入学成绩 = (select avg(入学成绩) from 学生表 where 班号 = 计算机科学与技术081);,带关系运算符子查询ANY和ALL功能。,给出高于“会计学
17、091”班的同学入学成绩的学生的学号、姓名、班号和入学成绩 Select 学号, 姓名,班号,入学成绩 From 学生表 Where 入学成绩 any (select 入学成绩 from 学生表 where 班号=会计学091);,Select 学号, 姓名,班号,入学成绩 From 学生表 Where 入学成绩 (select min(入学成绩) from 学生表 where 班号=会计学091);,ANY功能-例3-48,【例3-48】 求入学成绩小于等于班号为“会计学091”学生的任一入学成绩的学生学号、姓名和入学成绩。 select 学号, 姓名, 班号,入学成绩 from 学生表 w
18、here 入学成绩 会计学091 order by 入学成绩 desc;,ANY功能【例3-48】3,等价于： select 学号, 姓名, 班号,入学成绩 from 学生表 where 入学成绩 会计学091 order by 入学成绩 desc;,ALL功能【例3-49】,【例3-49】求小于等于所有班号为“会计学091”学生入学成绩的学生学号、姓名和入学成绩。,ALL功能【例3-49】2,select 学号, 姓名, 班号,入学成绩 from 学生表 where 入学成绩 会计学091 order by 入学成绩 desc; 结果为小于等于“会计学091”班最小成绩556分的所有非“会计
19、学091”同学。 思考：在子查询中使用聚合函数完成上述功能。,2. SQL合并查询,SQL合并查询就是将两个SELECT语句的查询结果通过并运算（UNION）合并为一个查询结果。SQL合并查询要求两个查询的字段个数相同，且对应字段的数据类型相同。,SQL合并查询【 例3-50】,【例3-50】使用SQL合并查询运算求“会计学081”班和“会计学091”的学号、姓名和班号。虽然该查询操作可以使用集合包含条件描述，但也可使用SQL合并查询完成。,SQL合并查询【例3-50】2,select 学号, 姓名, 班号 from 学生表 where 班号 = “会计学081”unionselect 学号,
20、 姓名, 班号 from 学生表 where 班号 = “会计学091”;,3.2.6 交叉表查询,交叉表查询是Access特有的SQL查询语句。 【例3-51】求给定课程选修总人数,但要给出各班级的人数。 transform count(d.学号) as AA select 课程名,count(d.学号) as 选课人数 from 班级表 a,学生表 b,课程表 c,成绩表 d where a.班号 = b.班号 and c.课程号 = d.课程号 and b.学号 = d.学号 group by 课程名 pivot a.班号;,交叉表查询-语句,查询向导编写交叉查询【例3-52】,【例3-
21、52】使用查询向导，编写交叉查询。 （1）打开Access“教学管理数据库”文件。 （2）编写一个称为“查询4张表”的查询，语句如下： select a.专业, a.班号,d.学号, d.课程号, c.课程名 FROM 班级表 a, 学生表 b, 课程表 c,成绩表 d where a.班号=b.班号 and c.课程号=d.课程号 and b.学号=d.学号;,查询向导写交叉查询【例3-52】2,（3）启动查询向导。在出现的“新建查询”对话框中选择“交叉表查询向导”选项后，单击“确定”按钮。,查询向导写交叉查询【例3-52】2,查询向导写交叉查询【例3-52】3,（4）选择表或查询包含交叉表
22、查询结果所需字段。,查询向导写交叉查询【例3-52】4,（5）选择字段作为行标题。,查询向导写交叉查询【例3-52】5,（6）选择字段作为列标题。,查询向导写交叉查询【例3-52】6,（7）选择行列交叉点聚合计算方法。,查询向导写交叉查询【例3-52】7,（8）指定交叉查询名称。,查询向导写交叉查询【例3-52】8,（9）交叉查询结果。,2. 交叉查询语法,TRANSFORM 聚合函数 select语句 PIVOT pivotfield IN (value1, value2, .) TRANSFORM 语句各部分含义如表3-12所示。 transform count(d.学号) as AA s
23、elect 课程名,count(d.学号) as 选课人数 from 班级表 a,学生表 b,课程表 c,成绩表 d where a.班号 = b.班号 and c.课程号 = d.课程号 and b.学号 = d.学号 group by 课程名 pivot a.班号;,3.3 数据操纵,包括插入(INSERT）、删除(DELETE）和更新(UPDATE）三种。 插入、删除和修改操作有可能导致数据违背数据库完整性约束，操作时必须细心。 例如本书教学管理数据库中 如果成绩表中有某个同学的成绩，学生表和成绩表之间有外键约束，则不能够先删除学生表中的学生记录 同样插入数据记录也存在先后顺序问题,1.
24、 插入记录INSERT,语句格式： INSERT INTO ( , , .) VALUES ( , , .) 命令功能：向表中插入一条记录,插入记录【例3-53】,【例3-53】向学生表中插入一条记录。 insert into 学生表( 学号, 姓名, 性别, 出生日期, 入学成绩, 民族, 籍贯, 班号, 贷款否 ) values (s0082999,李莉,女,1989/06/01, 588,汉,江西南昌,会计学081,false); 使用该语句时，如果违法数据表的约束，即表中要求不能为空的或主键冲突的数据将不能插入数据表中。,2. 更新记录,语句格式： UPDATE SET = , = .
25、 WHERE AND | OR .UPDATE命令功能：更新表中满足条件的记录。,更新记录【例3-54】,【例3-54】将学号为S0082999的学生入学成绩改为612分。 update 学生表 set 入学成绩 = 612 where 学号 = S0082999; 注意：由于UPDATE语句对数据表更新是不可逆的。所以，UPDATE语句中的WHERE条件必须仔细写好。,3. 删除记录,语句格式： DELETE table.* FROM table WHERE AND | OR .DELETE FROM命令的功能是从表中删除满足条件的记录。,删除记录【例3-55】,【例3-55】删除学号为S0082999的学生。 Delete from 学生表 where 学号 = “S0082999”; 由于delete删除语句同样不可逆，使用时需细心。,