《数据库》教案及student数据库课件项目5 基于多表查询数据

项目五基于多表查询数据2项目要求熟练掌握基于内联接的多表查询方法掌握基于左外联接和右外联接的方法了解自联接的方法掌握子查询的表示与执行3项目5.1

基于多表联接的查询基本语法：FROM子句的基本语法格式1FROM表1[AS表别名][联接类型表2[AS表别名]ON联接条件][…n]说明：表之间的联接类型有四种：内联接、外联接、交叉联接和自联接。4任务1：基于内联接的多表查询基本语法：基于内联接的语法格式1FROM表1[AS表别名][INNERJOIN表2[AS表别名]ON联接条件][…n]说明：内联接：根据每个表中共有列的值匹配两个表中的行联接条件：经常采用“主键=外键的形式”5任务1：基于内联接的多表查询基本语法：基于内联接的语法格式2FROM表1[AS表别名][,表2WHERE联接条件][…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.cjBETWEEN80AND90任务1：基于内联接的多表查询7打开“xs”表的【查询设计器】窗口。在【关系图窗格】中，右键选择快捷菜单【添加表】，进入【添加表】对话框，依次选择“cj”，“kc”表，【SQL窗格】中自动出现相应代码。“联接多表”后在SQL窗格中自动出现的SQL代码任务1：基于内联接的多表查询完成任务方案一：通过【查询设计器】操作8任务1：基于内联接的多表查询在【网格窗格】中，在【列】中依次选择“xs.xm”、“kc.kcm”、“cj.cj”字段在【网格窗格】中，在【列】中选择“kc.kcdh”字段，去除【输出】勾选项，在【准则】中输入“’001’”；在【列】中选择“cj.cj”字段，去除【输出】勾选项，在【准则】中输入“BETWEEN80AND90”。单击工具栏上的“”按钮，如下图所示。9任务1：基于内联接的多表查询10任务1：基于内联接的多表查询完成任务方案二：通过【查询分析器】操作方法一：采用INNERJOIN关键字的源代码：进入【查询分析器】；选择“”；SELECT-SQL代码为：SELECTxs.xm,kc.kcm,cj.cjFROMxsINNERJOINcjONxs.xh=cj.xhINNERJOINkcONcj.kcdh=kc.kcdhWHERE(kc.kcdh='001')AND(cj.cjBETWEEN80AND90)11任务1：基于内联接的多表查询完成任务方法二：采用WHERE关键字的源代码：进入【查询分析器】；选择“”；SELECT-SQL代码为：SELECTxs.xm,kc.kcm,cj.cjFROMxs,cj,kcWHERE(xs.xh=cj.xh)AND(cj.kcdh=kc.kcdh)AND(kc.kcdh='001')AND(cj.cjBETWEEN80AND90)12任务2：基于左外联接和右外联接的多表查询基本语法：基于外联接的语法格式FROM表1[AS表别名][外联接类型表2[AS表别名]ON联接条件][…n]说明：外联接类型：左向外联接（LEFT[OUTER]JOIN）：返回左表的所有行；右向外联接（RIGHT[OUTER]JOIN）：返回右表的所有行；完全联接（FULLJOIN）：返回左表和右表的所有行。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*FROMxsLEFTOUTERJOINcjONxs.xh=cj.xh16任务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.cjBETWEEN80AND9018任务3：基于自联接的多表查询完成任务进入【查询分析器】；选择“”；SELECT-SQL代码为：SELECTDISTINCTa.*FROMxsaJOINxsbONa.xm=b.xmWHEREa.xh<>b.xhORDERBYa.xh19项目练习练习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”字段，在【准则】中输入“>(SELECTAVG(cj)FROMcjWHEREkcdh=‘001’)”（内部查询）。返回单个值的非相关子查询24完成任务方法二：通过【查询分析器】操作进入【查询分析器】；选择“”；SELECT-SQL代码为：SELECTxs.xh,xs.xm,cj.cjFROMxsINNERJOINcjONxs.xh=cj.xhWHERE(cj.kcdh='001')AND(cj.cj>(SELECTAVG(cj)FROMcjWHEREkcdh='001'))ORDERBYcj.cjDESC返回单个值的非相关子查询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(selectavg(cj)fromcjgroupbykcdh)”（内部查询）。单击工具栏上的“”按钮，查询结果在【结果窗格】中显示。28返回一组值的非相关子查询完成任务方法二：通过【查询分析器】操作进入【查询分析器】；选择“”；SELECT-SQL代码为：SELECTDISTINCTxs.*FROMxsINNERJOINcjONxs.xh=cj.xhWHERE(cj.cj>ALL(SELECTAVG(cj)FROMcjGROUPBYkcdh))29任务2：相关子查询（依赖于外部查询的子查询）基本概念：相关子查询内部查询（子查询）的结果执行依赖于外部查询说明：相关子查询：多数情况下，在子查询的WHERE子句中引用了外部查询的表相关子查询执行过程：子查询为外部查询的每一行执行一次，外部查询将子查询所引用的外部字段的值传递给子查询进行操作；外部查询根据子查询的结果返回值30任务2：相关子查询（依赖于外部查询的子查询）提出任务查询成绩大于该门课程平均分的学生信息。分析任务由题意：内部查询（子查询）查询“cj”表中该门课程的平均分。此时课程信息是动态的，通过外部查询所给的值才能确定。外部查询：查询学生成绩大于该门课程平均分的学生信息。列名：xs.*表名：xs表、cj表联接类型：内联接选择查询条件：cj.cj>该门课程平均分（子查询的结果）31任务2：相关子查询（依赖于外部查询的子查询）完成任务方法一：通过【查询设计器】操作打开“xs”表的【查询设计器】窗口。在【关系图窗格】中，右键选择快捷菜单【添加表】，进入【添加表】对话框，依次选择“cj”表，【SQL窗格】中自动出现相应代码。在【网格窗格】中，在【列】中选择“xs.*”，在【表】中输入“a”，右键单击“xs.*”，选择快捷菜单【属性】，打开【属性】对话框，勾选【DISTINCT值】选项。在【网格窗格】中，在【列】中选择“cj.cj”字段，去除【输出】勾选项，在【表】中输入“b”，在【准则】中输入“>(SELECTAVG(cj)FROMcjAScWHEREb.kcdh=c.kcdh)”（内部查询）。单击工具栏上的“”按钮，查询结果在【结果窗格】中显示。

32任务2：相关子查询（依赖于外部查询的子查询）完成任务方法二：通过【查询分析器】操作进入【查询分析器】；选择“”；SELECT-SQL代码为：SELECTDISTINCTa.*FROMxsaINNERJOINcjbONa.xh=b.xhWHERE(b.cj>(SELECTAVG(cj)FROMcjAScWHEREb.kcdh=c.kcdh))33任务3：

IN子查询和EXISTS子查询基本概念：IN子查询和EXISTS子查询IN子查询和EXISTS子查询都可以返回多条记录；IN子查询通常可以等价转换为EXISTS子查询。说明：一般情况下，对于“字段[NOT]IN(SELECT子句)”可以等价转换成“[NOT]EXISTS(SELECT子句WHERE条件)”34任务3：

IN子查询和EXISTS子查询提出任务查找课程表中没有被学生修课的课程信息。分析任务由题意：内部查询（子查询）：查询“cj”表中被学生修课的“kcdh”外部查询：查询课程表中“kcdh”没有包含在“cj”表中“kcdh”里。列名：kc.*表名：kc表选择查询条件：方式一：采用IN子查询Kc.Kcdh不属于修课“kcdh”集合（内部查询）内

方式二：采用EXISTS子查询不包含在修课“kcdh”集合（内部查询）内35任务3：

IN子查询和EXISTS子查询完成任务

方式一：采用IN子查询方法一：通过【查询设计器】操作打开“kc”表的【查询设计器】窗口；在【网格窗格】中，在【列】中选择“*”；在【列】中选择“kc.kcdh”字段，去除【输出】勾选项，在【准则】中输入“NOTIN(SELECTDISTINCTkcdhFROMcj)”（内部查询）。单击工具栏上的“”按钮，查询结果在【结果窗格】中显示。36任务3：

IN子查询和EXISTS子查询完成任务方法二：通过【查询分析器】操作进入【查询分析器】；选择“”；SELECT-SQL代码为：SELECT*FROMkcWHERE(kcdhNOTIN(SELECTDISTINCTkcdhFROMcj))37任务3：

IN子查询和EXISTS子查询完成任务方式二：采用EXISTS子查询方法一：通过【查询设计器】操作打开“kc”表的【查询设计器】窗口；在【网格窗格】中，在【列】中选择“*”；在【列】中输入“EXISTS(SELECT*FROMcjWHEREcj.kcdh=kc.kcdh)”，【准则】中输入“=FALSE”。单击工具栏上的“”按钮，查询结果在【结果窗格】中显示。38任务3：

IN子查询和EXISTS子查询完成任务方法二：通过【查询分析器】操作进入【查询分析器】；选择“”；SELECT-SQL代码为：SELECT*FROMkcWHERE(NOTEXISTS(SELECT*FROMcjWHEREcj.kcdh=kc.kcdh))39任务4：包含在INSERT、UPDATE、DELETE语句里的子查询基本概念：

INSERT、UPDATE、DELETE语句中表达式部分都可以用子查询来代替。40任务4：包含在INSERT、UPDATE、DELETE语