MySQL数据库原理与应用项目化教程（微课版） 课件 项目8-高级数据查询

项目八高级数据查询E-mail:358542298@江苏海事职业技术学院

涉及多表数据的查询或复杂的单表查询问题要用高级查询来完成。高级查询包括连接查询、子查询和集合查询等操作，连接查询又分为交叉连接、内连接、外连接和自连接，子查询可以嵌套在查询语句中使用，也可以在更新语句中使用。本项目将对“学生成绩管理”数据库的数据表作高级查询操作，并在更新语句中应用子查询以实现更强大的数据更新能力。知识目标：识记连接查询、子查询、集合查询相关语句的语法。能力目标：能用连接查询或子查询解决多表查询或复杂的单表查询问题。能用集合查询解决一些查询问题。任务8.1任务8.3交叉连接与内连接子查询任务8.4子查询在更新语句中的应用任务8.2外连接与自连接任务8.5集合查询

任务8.1交叉连接与内连接使用交叉连接或内连接完成对“学生成绩管理”数据库（stuDB）涉及多表数据的查询操作。（具体任务详见任务实施部分）【任务描述】交叉连接与内连接8.1【相关知识】21

内连接

交叉连接8.1交叉连接与内连接1.交叉连接又叫笛卡尔连接。表1（M行）与表2（N行）做交叉连接的结果集是两表所有记录的任意组合，一共M×N行。语法格式有两种：(1)语法格式1SELECT…FROM表l,

表2;(2)语法格式2SELECT…FROM表1CROSSJOIN表2;8.1【相关知识】交叉连接与内连接2.内连接：把两表中满足条件的记录组合在一起，相当于是交叉连接的子集。

(1)等值连接：两表连接条件是对应字段做相等比较。（最常见的内连接）①语法格式1：SELECT…FROM表l,表2

WHERE

表1.列名=表2.列名

...........................连接条件②语法格式2：SELECT…FROM表l[INNER]JOIN表2ON

表1.列名=表2.列名8.1【相关知识】交叉连接与内连接说明：N个表连接成一个表，两两连接N-1次完成。若在WHERE子句中给出连接条件，N个表连接有N-1个连接条件，用AND运算符连接起来；若在FROM子句后面指定连接条件，JOIN一个表，ON后面写一个连接条件。引用的字段被查询的多个表所共有，则引用时需指定其属于哪个表，格式：表名.字段名为了简化连接条件的书写，可以给表起别名，起别名的表，在该查询语句中要统一使用别名代替表名。两个表若没有关联，需要找一个和它们都有关联的第三个表间接地完成两个表的连接操作。8.1【相关知识】交叉连接与内连接(2)自然连接：特殊的等值连接，基于两个表中所有同名的字段做相等比较。

语法格式：SELECT…FROM表lNATURALJOIN表28.1【相关知识】交叉连接与内连接优点：减少冗余，省略连接条件【任务实施】准备工作：数据库studb三个数据表的数据如任务1.2中表1-3、表1-4、表1-5所示。8.1交叉连接与内连接【任务实施】1.把stuinfo表和stumarks表进行交叉连接。SELECT*FROMstuinfo,stumarks;或者SELECT*FROMstuinfoCROSSJOINstumarks;8.1交叉连接与内连接【任务实施】8.1交叉连接与内连接声明：学生信息纯属虚构学生表：8条记录选课成绩表：16条记录交叉连接结果集：8

16=128条记录stuinfo、stumarks二表交连接结果【任务实施】2.查询所有学生的学号、姓名、课程号及成绩。SELECTstuinfo.stuno,stuname,cno,stuscoreFROMstuinfo,stumarksWHEREstuinfo.stuno=stumarks.stuno;或者SELECTstuinfo.stuno,stuname,cno,stuscoreFROMstuinfoJOINstumarksON

stuinfo.stuno=stumarks.stuno;8.1交叉连接与内连接【任务实施】8.1交叉连接与内连接【任务实施】8.1交叉连接与内连接由于连接条件是两表同名字段做相等比较，可以使用自然连接简化代码：SELECTstuno,stuname,cno,stuscoreFROMstuinfoNATURALJOINstumarks;【任务实施】3.查询所有学生的学号、姓名、课程名及成绩。8.1交叉连接与内连接（1）用语法格式1

SELECTstuinfo.stuno,stuname,cname,stuscoreFROMstuinfo,stumarks,stucourseWHEREstuinfo.stuno=stumarks.stunoANDo=o;（2）用语法格式2SELECTstuinfo.stuno,stuname,cname,stuscoreFROMstuinfoJOINstumarksONstuinfo.stuno=stumarks.stuno

JOIN

stucourseONo=o;【任务实施】简化代码如下：（1）用语法格式1SELECTi.stuno,stuname,cname,stuscoreFROMstuinfoi,stumarksm,stucoursecWHEREi.stuno=m.stunoANDm.cno=c.cno;（1）用语法格式2SELECTi.stuno,stuname,cname,stuscoreFROMstuinfoiJOINstumarksmONi.stuno=m.stunoJOINstucoursec

ONm.cno=c.cno;8.1交叉连接与内连接【任务实施】8.1交叉连接与内连接声明：学生信息纯属虚构【任务实施】8.1交叉连接与内连接由于连接条件是同名字段做相等比较，还可以使用自然连接简化代码：SELECTstuno,stuname,cname,stuscoreFROMstuinfoNATURALJOINstumarksNATURALJOINstucourse;【任务实施】4.查询选修“李斯文”老师课程的学生的学号及姓名SELECTi.stuno,stunameFROMstuinfoi,stumarksm,stucoursecWHERE(i.stuno=m.stunoANDo=o)AND(cteacher='李斯文');或者SELECTi.stuno,stunameFROMstuinfoiJOINstumarksmONi.stuno=m.stunoJOINstucoursecONo=oWHEREcteacher='李斯文';8.1交叉连接与内连接【任务实施】8.1交叉连接与内连接【任务实施】8.1交叉连接与内连接还可以使用自然连接简化代码：SELECTstuno,stunameFROMstuinfoNATURALJOINstumarksNATURALJOINstucourseWHEREcteacher='李斯文';重要知识点：1.交叉连接：结果集是两表所有记录的任意组合，一共M×N行

。（不常用）2.内连接**：把两表中满足条件的记录组合在一起，等值连接最常见。（常用）（1）两种语法格式（2）连接后相同字段名的引用格式：表名.字段名（3）可以给表起别名简化代码（4）自然连接：特殊的等值连接，可以减少冗余，省略连接条件。（4）两个没有关联的表的连接要通过第三个表（与两表都有关联）间接实现。8.1交叉连接与内连接任务8.2外连接与自连接使用外连接或自连接完成对“学生成绩管理”数据库（stuDB）涉及多表数据的查询操作或复杂的单表查询操作。具体任务如下：（1）查询没有选修课程的学生的基本信息。（2）查找同一课程成绩相同的选课记录。

【任务描述】外连接与自连接8.2【相关知识】21

自连接外连接外连接与自连接8.21.外连接外连接分为左外连接、右外连接和全外连接。MySQL目前支持左外连接和右外连接操作。两表作连接，JOIN左边的表叫左表，JOIN右边的表叫右表。外连接与自连接8.2【相关知识】(1)左外连接结果集：内连接的结果集+左表中没有参加内连接的记录（左表这些“剩下来”的记录在结果集中右表的那些字段值全为NULL）。语法格式如下：SELECT…FROM表1

LEFT[OUTER]JOIN

表2ON

表1.列名=表2.列名外连接与自连接8.2【相关知识】(2)右外连接内连接的结果集+右表中没有参加内连接的记录（右表这些“剩下来”的记录在结果集中左表的那些字段值全为NULL）语法格式如下：SELECT…FROM表1RIGHT[OUTER]JOIN表2ON

表1.列名=表2.列名外连接与自连接8.2【相关知识】2.自连接

特殊的内连接，连接的两个表完全相同的，为了区分需要给它们分别起别名。（1）语法格式1SELECT…FROM表名别名1,表名别名2WHERE别名1.列名=别名2.列名（2）语法格式2：SELECT…FROM表名别名1JOIN表名别名2ON别名1.列名=别名2.列名外连接与自连接8.2【相关知识】【任务实施】准备工作：数据库studb三个数据表的数据如任务1.2中表1-3、表1-4、表1-5所示。8.2外连接与自连接【任务实施】查询没有选修课程的学生的基本信息分析：先查看stuinfo与stumarks表做左外连接的结果集。SELECT*FROMstuinfoLEFTJOINstumarksONstuinfo.stuno=stumarks.stuno;外连接与自连接8.2【任务实施】执行结果：外连接与自连接8.2没有选课学生所在行，stumarks表的那些字段值全为NULL选课学生所在行，stumarks表的主属性(stuno与cno)值不为NULL【任务实施】（2）判断学生没有选课：左外连接结果集中stumarks表的stuno(或cno)为NULL。 SELECTstuinfo.*FROMstuinfoLEFTJOINstumarksONstuinfo.stuno=stumarks.stunoWHEREstumarks.stunoISNULL;外连接与自连接8.2【任务实施】外连接与自连接8.2声明：学生信息纯属虚构

没有选修课程的学生信息：【任务实施】2．查找同一课程成绩相同的选课记录。SELECTa.stuno,b.stuno,a.cno,a.stuscoreFROMstumarksa,stumarksbWHEREa.stuscore=b.stuscoreANDa.stuno<>b.stunoANDa.cno=b.cno;外连接与自连接8.2重要知识点：1.外连接（1）左外~（右外~）:内连接结果集+左（右）表没有参与连接的记录（2）应用场景：要筛选出在另一个表中没有相关数据的记录。2.自连接（1）特殊的内连接，连接的二个表相同，给它们起不同别名区分（2）应用场景：一些复杂的单表查询问题8.2外连接与自连接任务8.3子查询

使用子查询完成对“学生成绩管理”数据库（stuDB）涉及多表数据的查询或者复杂的单表查询操作，这些多表查询有个特点：查询的数据项在同一个表中，而筛选记录需要通过其他表的数据进行。（具体任务详见任务实施部分）【任务描述】8.3子查询【相关知识】51

[NOT]EXISTS子查询

[NOT]IN子查询8.3子查询2

比较子查询34

ALL子查询

ANY|SOME子查询子查询：是指一个查询块嵌套在SELECT、INSERT、UPDATE、DELETE等语句中的WHERE或其他子句中进行查询。SQL语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。常见的使用形式：嵌套在SELECT语句的WHERE子句中的子查询。子查询分为相关子查询与不相关子查询两大类。不相关子查询是指不依赖于外部查询的子查询，反之，则称为相关子查询8.3子查询【相关知识】子查询返回的值要被外部查询的[NOT]IN、[NOT]EXISTS、比较运算符、ALL等操作符使用，根据操作符的不同，子查询主要分为以下几种：1．[NOT]IN子查询子查询的结果是一个集合，用谓词IN判断某列值是否在集合中，这是最常用的一种子查询，IN前面加NOT表示判断某列值是否不在集合中。IN子查询一般是不相关子查询。8.3子查询【相关知识】2．比较子查询子查询返回单个值，与外部查询用比较运算符(>、<、=、>=、<=、!=或<>)进行连接。比较子查询可能是不相关子查询，也可能是相关子查询。3．[NOT]EXISTS子查询使用EXISTS

谓词来判断子查询是否返回任何记录，当子查询的结果不为空集（即存在匹配行）时，返回逻辑真值。EXISTS前面可以加NOT用来判断是否不存在匹配行。EXISTS子查询是相关子查询。8.3子查询【相关知识】4．ALL子查询ALL操作符可以与比较运算符一起使用，表示将一个值与子查询返回的一组值进行比较，只有所有值都满足某个条件才返回逻辑真值。5．ANY|SOME子查询与ALL子查询类似，表示将一个值与子查询返回的一组值进行比较，ANY操作符指定任何一个值满足条件即返回逻辑真值，SOME操作符指定一些值满足条件即返回逻辑真值。8.3子查询【相关知识】【任务实施】准备工作：数据库studb三个数据表的数据如任务1.2中表1-3、表1-4、表1-5所示。8.3子查询【任务实施】查询选修了课程的学生的基本信息。

方法一：用IN子查询8.3子查询分析：第二步：根据第一步得到的学号集合查这些学生的基本信息解决问题思路：把一个复杂的多表查询问题转换为二个简单的单表查询问题。第一步：查找所有选修了课程的学生的学号【任务实施】代码如下：第一步：查找出所有选修了课程的学生的学号第二步：根据前一步得到的学号集合查这些学生的基本信息SELECT*FROMstuinfoWHEREstunoIN(？）;

8.3子查询【任务实施】方法二：用EXISTS子查询SELECT*FROMstuinfoWHEREEXISTS(SELECT*FROMstumarksWHEREstuno=stuinfo.stuno);子查询依赖于外部查询传递进来的值：stuinfo.stuno（该生学号）。8.3子查询【任务实施】8.3子查询声明：学生信息纯属虚构【任务实施】2.查询没有选修课程的学生的基本信息。（1）用IN子查询SELECT*FROMstuinfoWHEREstunoNOTIN(SELECTDISTINCTstunoFROMstumarks);（2）用EXISTS子查询SELECT*FROMstuinfoWHERENOTEXISTS(SELECT*FROMstumarksWHEREstuno=stuinfo.stuno);8.3子查询【任务实施】8.3子查询声明：学生信息纯属虚构【任务实施】3.查询选修了“高等数学”这门课的学生姓名。

分析：通过三个表的数据分析手工查询步骤8.3子查询三张数据表声明：学生信息纯属虚构【任务实施】3.查询选修了“高等数学”这门课的学生姓名。

分析：第一步：查找‘高等数学’这门课的课程号第二步：根据第一步得到的课程号查选修该门课的学生学号第三步：根据第二步得到的学号集合查这些学生的姓名

8.3子查询三个数据表解决问题思路：把一个复杂的多表查询问题转换为三个简单的单表查询问题。【任务实施】第一步：查找‘高等数学’这门课的课程号第二步：根据第一步得到的课程号查选修该门课的学生学号

8.3子查询【任务实施】第三步：根据第二步得到的学号查学生的姓名

8.3子查询所有选修了”高等数学”的学生的学号思政小贴士【规范化、标准化的代码编写习惯】培养一丝不苟的工匠精神子查询8.3【任务实施】8.3子查询声明：学生信息纯属虚构上机过程演示【任务实施】8.3子查询类似查询问题：查询选修了李斯文老师讲授课程的学生信息

大家一起来出题。。。。。。

拓展题：（1）查询既选修了“高等数学”又选修了“英语”的学生姓名。（2）查询选修了“高等数学”但没有选修“英语”的学生姓名。【任务实施】8.3子查询讨论与总结：哪类多表查询问题可以用子查询来完成？总结：当查询的数据项在同一个表中，但是筛选数据的条件涉及到其他表的数据时，可以用子查询来完成。【任务实施】4.查询成绩最高的选课记录第一步：查找学生选课表中的最高成绩

SELECTMAX(stuscore)FROMstumarks;第二步：查找成绩等于最高成绩的选课记录SELECT*FROMstumarksWHEREstuscore=(SELECTmax(stuscore)FROMstumarks);8.3子查询【任务实施】8.3子查询【任务实施】5.查询年龄最小的学生的基本信息方法一：先找出最大的出生日期（年龄最小）SELECT*FROMstuinfoWHEREstubirthday=(SELECTMAX(stubirthday)FROMstuinfo)方法二：逐一比较SELECT*FROMstuinfoWHEREstubirthday>=ALL(SELECTstubirthdayFROMstuinfo)8.3子查询【任务实施】6.查询年龄不是最小的学生的基本信息方法一：先找出最大出生日期（年龄最小）SELECT*FROMstuinfoWHEREstubirthday<(SELECTMAX(stubirthday)FROMstuinfo)方法二：出生日期只要小于某个同学的出生日期即可SELECT*FROMstuinfoWHEREstubirthday<ANY(SELECTstubirthdayFROMstuinfo)8.3子查询重要知识点1.[NOT]IN子查询:子查询结果是集合（常用）2.比较子查询：子查询结果是单个值3.[NOT]EXISTS子查询：判断子查询是否返回任何记录。内外相关~4.一题可以多解(1)内连接、IN子查询、EXISTS子查询

(2)外连接、NOTIN子查询、NOTEXISTS子查询

8.3子查询任务8.4子查询在更新语句中的应用子查询可以嵌套在INSERT、UPDATE、DELETE语句中使用。

对“学生成绩管理”数据库的数据表进行数据更新时应用子查询，以实现比项目六更强大的数据更新能力。（具体内容见任务实施）【任务描述】8.4子查询在更新语句中的应用【相关知识】31UPDATE和DELETE语句的条件子句带子查询

从一个表向另一个表复制多行多列数据8.4子查询在更新语句中的应用2

嵌套修改1．从一个表向另一个表复制多行多列数据语法格式如下：INSERTINTO表名[(字段列表)]SELECT语句;说明：字段列表中字段的个数、数据类型必须和SELECT语句中查询的数据项个数及数据类型一

一对应。8.4子查询在更新语句中的应用【相关知识】2．嵌套修改利用子查询返回的单个值，修改表中某个字段值。语法格式如下：UPDATE表名SET字段名＝(返回单个值的子查询)[WHERE条件]8.4子查询在更新语句中的应用【相关知识】3．UPDATE和DELETE语句的条件子句带子查询

当UPDATE、DELETE语句修改、删除数据时的筛选条件比较复杂，甚至需要通过另一个表的数据来判断，如果在UPDATE、DELETE语句的WHERE子句中使用子查询，基本可以满足这种筛选需求。8.4子查询在更新语句中的应用【相关知识】【任务实施】准备工作：数据库studb三个数据表的数据如任务1.2中表1-3、表1-4、表1-5所示。8.4子查询在更新语句中的应用【任务实施】1.创建一个空表stuinfo_2（stuno,stuname,avg_stuscore)，要求用INSERT语句把stuinfo表中stuno,stuname两个字段的数据导入到stuinfo_2表中相应字段。(1)创建空表stuinfo_2CREATETABLEstuinfo_2(stunoCHAR(4)PRIMARYKEY,stunameCHAR(5),avg_stuscoreDECIMAL(4,1));子查询在更新语句中的应用8.4【任务实施】(2)stuinfo_2表中导入stuinfo表中stuno,stuname两个字段的数据INSERTINTOstuinfo_2(stuno,stuname)SELECTstuno,stunameFROMstuinfo;子查询在更新语句中的应用8.4声明：学生信息纯属虚构【任务实施】2．修改stuinfo_2表中“S001”同学的平均成绩(avg_stuscore)（注：平均分统计根据stumarks表中该生的选课成绩）。UPDATEstuinfo_2SETavg_stuscore=(SELECTAVG(stuscore)FROMstumarksWHEREstuno='S001')WHEREstuno='S001';子查询在更新语句中的应用8.4声明：学生信息纯属虚构【任务实施】思考题：如果要一次修改所有同学的平均分，前面代码应该怎么改？

子查询在更新语句中的应用8.4UPDATEstuinfo_2SETavg_stuscore=(SELECTAVG(stuscore)FROMstumarksWHEREstuno=stuinfo_2.stuno);声明：学生信息纯属虚构【任务实施】3．把“高等数学”这门课的所有选修成绩都加5分。UPDATEstumarksSETstuscore=stuscore+5WHEREcno=(SELECTcnoFROMstucourseWHEREcname='高等数学');子查询在更新语句中的应用8.4【任务实施】4．删除“刘卫平”同学的所有选课记录（假设“刘卫平”没有同名）。

DELETEFROMstumarksWHEREstuno=(SELECTstunoFROMstuinfoWHEREstuname='刘卫平');子查询在更新语句中的应用8.4重要知识点1.从一个表向另一个表复制多行多列数据INSERTINTO表名[(字段列表)]SELECT语句;2.嵌套修改

UPDATE表名SET字段名＝(返回单个值的子查询)[WHERE条件]3.UPDATE和DELETE语句的条件子句带子查询

8.4子查询在更新语句中的应用任务8.5集合查询用集合查询完成对“学生成绩管理”数据库的以下查询任务：(1）查询选修了“0001”或“0003”号课程的学生的学号，结果保留重复行。(2）查询选修了“0001”或“0003”号课程的学生的学号，结果去掉重复行

（3）查询既选修了“0001”又选修了“0003”号课程的学生的学号。

（4）查询选修了“0001”号，但没有选修“0003”号课程的学生的学号。【任务描述】8.5集合查询集合查询可以把一些复杂的查询问题简单化，SQL提供了集合查询的并、交、差操作。（注：MySQL8.0.31版本开始才支持交、差操作）语法格式：

查询1

集合操作符

查询2说明：查询1和查询2的结果集的字段个数和数据类型要一一对应。

8.5集合查询【相关知识】1．并集操作（UNION|UNIONALL）:合并两个查询结果集

语法格式：

查询1

UNION|UNIONALL

查询2说明：UNIONALL是简单合并，重复行保留，UNION则会合并后去掉重复行