《数据库技术》配套教案 项目5 基于多表查询数据

项目五,基于多表查询数据,2,项目要求,熟练掌握基于内联接的多表查询方法 掌握基于左外联接和右外联接的方法 了解自联接的方法 掌握子查询的表示与执行,3,项目5.1 基于多表联接的查询,基本语法：FROM子句的基本语法格式1 FROM 表1 AS 表别名 联接类型 表2 AS 表别名 ON 联接条件 n 说明： 表之间的联接类型有四种：内联接、外联接、交叉联接和自联接。,4,任务1：基于内联接的多表查询,基本语法：基于内联接的语法格式1 FROM 表1 AS 表别名 INNER JOIN 表2 AS 表别名 ON 联接条件 n 说明： 内联接：根据每个表中共有列的值匹配两个表中的行 联接条件：经常采用“主键=外键的形式”,5,任务1：基于内联接的多表查询,基本语法：基于内联接的语法格式2 FROM 表1 AS 表别名 , 表2 WHERE 联接条件 n,6,提出任务 在“cj”、“xs”、“kc”表中，找出“kcdh”为“001”的，“cj”在80-90分之间的学生的姓名、课程名和成绩 分析任务 由题意和SELECT语句的基本语法格式可得出： 列名：xs.xm（姓名）, kc.kcm（课程名）, cj.cj（成绩） 表名：xs表、cj表、kc表 联接类型：内联接 选择查询条件：条件1：kc.kcdh = '001'； 条件2：cj.cj BETWEEN 80 AND 90,任务1：基于内联接的多表查询,7,打开“xs”表的【查询设计器】窗口。 在【关系图窗格】中，右键选择快捷菜单【添加表】，进入【添加表】对话框，依次选择“cj”，“kc”表，【SQL窗格】中自动出现相应代码。,任务1：基于内联接的多表查询,完成任务 方案一：通过【查询设计器】操作,8,任务1：基于内联接的多表查询,在【网格窗格】中，在【列】中依次选择“xs.xm”、“kc.kcm”、“cj.cj”字段 在【网格窗格】中，在【列】中选择“kc.kcdh”字段，去除【输出】勾选项，在【准则】中输入“001”；在【列】中选择“cj.cj”字段，去除【输出】勾选项，在【准则】中输入“BETWEEN 80 AND 90”。 单击工具栏上的“ ” 按钮，如下图所示。,9,任务1：基于内联接的多表查询,10,任务1：基于内联接的多表查询,完成任务 方案二：通过【查询分析器】操作 方法一：采用INNER JOIN关键字的源代码： 进入【查询分析器】； 选择“ ”； SELECT-SQL代码为： SELECT xs.xm, kc.kcm, cj.cj FROM xs INNER JOIN cj ON xs.xh = cj.xh INNER JOIN kc ON cj.kcdh = kc.kcdh WHERE (kc.kcdh = '001') AND (cj.cj BETWEEN 80 AND 90),11,任务1：基于内联接的多表查询,完成任务 方法二：采用WHERE关键字的源代码： 进入【查询分析器】； 选择“ ”； SELECT-SQL代码为： SELECT xs.xm, kc.kcm, cj.cj FROM xs,cj,kc WHERE (xs.xh = cj.xh) AND(cj.kcdh = kc.kcdh) AND (kc.kcdh = '001') AND (cj.cj BETWEEN 80 AND 90),12,任务2：基于左外联接和右外联接的多表查询,基本语法：基于外联接的语法格式 FROM 表1 AS 表别名 外联接类型 表2 AS 表别名 ON 联接条件 n 说明： 外联接类型： 左向外联接（LEFT OUTER JOIN）： 返回左表的所有行； 右向外联接（RIGHT OUTER JOIN）： 返回右表的所有行； 完全联接（FULL JOIN）： 返回左表和右表的所有行。,13,任务2：基于左外联接和右外联接的多表查询,提出任务 查询所有学生的成绩信息 分析任务 “xs”表里包含了所有学生的记录，是“主表”，“cj”表里包含了学生的成绩信息，是“子表”。 并非所有学生每门课都有成绩，若需要返回所有学生的成绩信息，缺少的成绩信息以“NULL”来代替。 由题意可知，返回“xs”表的所有行，若“xs”表联接时在左边，就用“LEFT OUTER JOIN”，反之，用“RIGHT OUTER JOIN”。,14,任务2：基于左外联接和右外联接的多表查询,完成任务 方法一：通过【查询设计器】操作 打开“xs”表的【查询设计器】窗口。 【关系图窗格】中，右键选择快捷菜单【添加表】，进入【添加表】对话框，依次选择“cj”，表，【SQL窗格】中自动出现相应代码。 在【关系图窗格】中，右键单击“ ”，选择快捷菜单【从xs中选择所有行（S）】；或选择【属性】，进入【属性】对话框，勾选【xs的所有行（L）】。 在【网格窗格】中，采用【列】中默认状态“*”； 单击工具栏上的“ ” 按钮，查询结果在【结果窗格】中显示。,15,任务2：基于左外联接和右外联接的多表查询,完成任务 方法二：通过【查询分析器】操作 进入【查询分析器】； 选择“ ”； SELECT-SQL代码为： SELECT * FROM xs LEFT OUTER JOIN cj ON xs.xh = cj.xh,16,任务3：基于自联接的多表查询,基本语法：基于自联接的语法格式 FROM 表1 AS 表别名 JOIN 表2 AS 表别名 ON 联接条件 n 说明： 自联接可看作是一张表的两个副本之间进行的联接； 在自联接中，必须为表指定两个别名，使之在逻辑上成为两张表； 自联接建议通过【查询分析器】操作。,17,任务3：基于自联接的多表查询,提出任务 用自联接的方法查询“xm”相同但“xh”不同的学生信息 分析任务 由题意和SELECT语句的基本语法格式可得出： 列名：xs.xm（姓名）, kc.kcm（课程名）, cj.cj（成绩） 表名：xs表、cj表、kc表 联接类型：内联接 选择查询条件：条件1：kc.kcdh = '001'； 条件2：cj.cj BETWEEN 80 AND 90,18,任务3：基于自联接的多表查询,完成任务 进入【查询分析器】； 选择“ ”； SELECT-SQL代码为： SELECT DISTINCT a.* FROM xs a JOIN xs b ON a.xm = b.xm WHERE a.xh b.xh ORDER BY a.xh,19,项目练习,练习5.1： 外联接类型有几种，各自的特点是什么？ 练习5.2： 查询“大学语文”成绩在85分以上的学生信息。,20,项目5.2 子查询,基本概念：内部查询（子查询）与外部查询 内部查询（子查询）： 包含在SELECT、INSERT、UPDATE或DELETE命令中的SELECT-SQL语句。 外部查询： 而包含内部查询（子查询）的SQL语句。,21,任务1：非相关子查询（不依赖于外部查询的子查询）,基本概念：非相关子查询 内部查询（子查询）的结果执行不依赖于外部查询 说明： 非相关子查询执行过程：先执行子查询，子查询的结果作为外部查询的条件来使用，再执行外部查询，显示查询结果集。 子查询执行的结果可以是单个值，也可以是多个值,22,返回单个值的非相关子查询,提出任务 查找“cj”表中“kcbh”为“001”的成绩高于平均分的所有学生的学号、姓名、成绩信息。 分析任务 由题意： 内部查询（子查询）查询“cj”表中“kcbh”为“001”的平均分。 外部查询查询“001”课程学生成绩大于平均分的学生学号、姓名和成绩信息。 列名：xs.xh（学号）, xs.xm（姓名）, cj.cj（成绩） 表名：xs表、cj表 联接类型：内联接 选择查询条件：条件1：kc.kcdh = '001'； 条件2：cj.cj 平均分（子查询的结果）,23,完成任务 方法一：通过【查询设计器】操作 打开“xs”表的【查询设计器】窗口。 在【关系图窗格】中，右键选择快捷菜单【添加表】，进入【添加表】对话框，依次选择“cj”，表，【SQL窗格】中自动出现相应代码。 在【网格窗格】中，在【列】中依次选择“xs.xh”、“xs.xm”、“cj.cj”字段。 在【网格窗格】中，在【列】中选择“kc.kcdh”字段，去除【输出】勾选项，在【准则】中输入“001”；重新选择【列】中 “cj.cj”字段，在【准则】中输入“ (SELECT AVG(cj) FROM cj WHERE kcdh = 001)”（内部查询）。,返回单个值的非相关子查询,24,完成任务 方法二：通过【查询分析器】操作 进入【查询分析器】； 选择“ ”； SELECT-SQL代码为： SELECT xs.xh, xs.xm, cj.cj FROM xs INNER JOIN cj ON xs.xh = cj.xh WHERE (cj.kcdh = '001') AND (cj.cj (SELECT AVG(cj) FROM cj WHERE kcdh = '001') ORDER BY cj.cj DESC,返回单个值的非相关子查询,25,返回一组值的非相关子查询,基本概念：子查询的相关逻辑运算符： ANY ：若表达式的值与子查询返回的一组值某一个值进行比较时返TRUE，结果为TRUE。 ALL：若表达式的值与子查询返回的一组值每一个值进行比较时返回TRUE，结果为TRUE。 NOT IN：若表达式的值包含在子查询返回的一组值中，结果为FALSE（TRUE）。 说明： 由于NOT IN 子查询可以与NOT EXISTS子查询互换，所以NOT IN 子查询在任务3中讲解。,26,返回一组值的非相关子查询,提出任务 查找成绩表中，高于各门课程平均分的学生信息 分析任务 由题意： 内部查询（子查询）查询“cj”表中各门课程的平均分。 外部查询查询学生成绩大于所有课程平均分的学生信息。 列名：xs.* 表名：xs表、cj表 联接类型：内联接 选择查询条件：cj.cj 所有课程平均分（ALL（子查询的结果）,27,返回一组值的非相关子查询,完成任务 方法一：通过【查询设计器】操作 打开“xs”表的【查询设计器】窗口。 在【关系图窗格】中，右键选择快捷菜单【添加表】，进入【添加表】对话框，依次选择“cj”，表，【SQL窗格】中自动出现相应代码。 在【网格窗格】中，在【列】中选择“xs.*”，右键单击“xs.*”，选择快捷菜单【属性】，打开【属性】对话框，勾选【DISTINCT值】选项。 在【网格窗格】中，在【列】中选择“cj.cj”字段，去除【输出】勾选项，在【准则】中输入“all(select avg(cj) from cj group by kcdh)”（内部查询）。 单击工具栏上的“ ” 按钮，查询结果在【结果窗格】中显示。,28,返回一组值的非相关子查询,完成任务 方法二：通过【查询分析器】操作 进入【查询分析器】； 选择“ ”； SELECT-SQL代码为： SELECT DISTINCT xs.* FROM xs INNER JOIN cj ON xs.xh = cj.xh WHERE (cj.cj ALL (SELECT AVG(cj) FROM cj GROUP BY kcdh),29,任务2：相关子查询（依赖于外部查询的子查询）,基本概念：相关子查询 内部查询（子查询）的结果执行依赖于外部查询 说明： 相关子查询： 多数情况下，在子查询的WHERE子句中引用了外部查询的表 相关子查询执行过程： 子查询为外部查询的每一行执行一次，外部查询将子查询所引用的外部字段的值传递给子查询进行操作；外部查询根据子查询的结果返回值,30,任务