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

项目八高级数据查询

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

任务8.1交叉连接与内连接使用交叉连接或内连接完成对“学生成绩管理”数据库（stuDB）涉及多表数据的查询操作。具体任务如下：(1)把stuinfo表和stumarks表进行交叉连接。(2)查询所有学生的学号、姓名、课程号及成绩。(3)查询所有学生的学号、姓名、课程名及成绩。(4)查询选修“李斯文”老师讲授课程的学生的学号及姓名。【任务描述】交叉连接与内连接8.1【相关知识】21

内连接

交叉连接8.1交叉连接与内连接1.交叉连接交叉连接又叫做笛卡尔连接。表1（M行）与表2（N行）做交叉连接，就是把表1的每一行分别与表2的每一行连接，结果集是两表所有记录的任意组合，一共M×N行。交叉连接语法格式有两种，分别如下：(1)语法格式1SELECT…FROM表l,表2;(2)语法格式2SELECT…FROM表1CROSSJOIN表2;8.1【相关知识】交叉连接与内连接2.内连接内连接是把两表中满足条件的记录组合在一起，相当于是交叉连接的子集。(1)语法格式1：SELECT…FROM表l,表2WHERE表1.列名=表2.列名(2)语法格式2：SELECT…FROM表l[INNER]JOIN表2ON表1.列名=表2.列名8.1【相关知识】交叉连接与内连接说明：N个表要连接成一个表，需要两两连接N-1次完成。第1种格式是在WHERE子句中给出连接条件，N个表连接有N-1个连接条件，要用AND运算符连接起来；第2种格式是在FROM子句后面指定连接条件，JOIN一个表，ON后面写一个连接条件。如果所引用的字段被查询的多个表所共有，则引用该字段时必须指定其属于哪个表，引用的语法格式：表名.字段名。为了简化连接条件的书写，可以给表名起别名，起了别名的表，在该查询语句中要统一使用别名代替表名。二个表如果没有共同字段，需要找一个和它们都有共同字段的第三个表间接地完成二个表的连接操作。8.1【相关知识】交叉连接与内连接【任务实施】1.把stuinfo表和stumarks表进行交叉连接。根据交叉连接二种语法格式，代码如下：SELECT*FROMstuinfo,stumarks;或者SELECT*FROMstuinfoCROSSJOINstumarks;8.1交叉连接与内连接【任务实施】8.1图8.1stuinfo、stumarks二表交叉连接结果（最前面8条记录）图8.2stuinfo、stumarks二表交叉连接结果（最后面8条记录）交叉连接与内连接【任务实施】2.查询所有学生的学号、姓名、课程号及成绩。SELECTstuinfo.stuno,stuname,cno,stuscoreFROMstuinfo,stumarksWHEREstuinfo.stuno=stumarks.stuno;或者SELECTstuinfo.stuno,stuname,cno,stuscoreFROMstuinfoJOINstumarksONstuinfo.stuno=stumarks.stuno;8.1交叉连接与内连接【任务实施】8.1图8.3查询所有学生的学号、姓名、课程号及成绩交叉连接与内连接【任务实施】3.查询所有学生的学号、姓名、课程名及成绩。8.1交叉连接与内连接（1）用语法格式1

SELECTstuinfo.stuno,stuname,cname,stuscoreFROMstuinfo,stumarks,stucourseWHEREstuinfo.stuno=stumarks.stunoANDo=o;（2）用语法格式2SELECTstuinfo.stuno,stuname,cname,stuscoreFROMstuinfoJOINstumarksONstuinfo.stuno=stumarks.stunoJOINstucourseONo=o;【任务实施】简化代码如下：（1）用语法格式1SELECTi.stuno,stuname,cname,stuscoreFROMstuinfoi,stumarksm,stucoursecWHEREi.stuno=m.stunoANDo=o;（1）用语法格式2SELECTi.stuno,stuname,cname,stuscoreFROMstuinfoiJOINstumarksmONi.stuno=m.stunoJOINstucoursecONo=o;8.1交叉连接与内连接【任务实施】8.1图8.4查询所有学生的学号、姓名、课程名及成绩交叉连接与内连接【任务实施】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.5查询选修李斯文老师课程的学生的学号及姓名交叉连接与内连接思政小贴士【课堂实践分组管理，组长负责协调组内学习能力较强者指导组内较差的同学完成课堂作业】培养认真负责的工作态度、一丝不苟的工匠精神、团队合作意识。8.1交叉连接与内连接任务8.2外连接与自连接使用外连接或自连接完成对“学生成绩管理”数据库（stuDB）涉及多表数据的查询操作或复杂的单表查询操作。外连接应用场景：要筛选出在另一个表中没有相关数据的记录。具体任务如下：（1）查询没有选修课程的学生的基本信息。（2）查找同一课程成绩相同的选课记录。

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

自连接

外连接外连接与自连接8.21.外连接外连接分为左外连接、右外连接和全外连接，MySQL目前支持左外连接和右外连接操作。这里先给出左表和右表的概念，两表作连接，JOIN左边的表叫左表，JOIN右边的表叫右表。(1)左外连接左外连接的结果集是两表内连接的结果集加上左表中没有参加内连接的记录，左表这些“剩下来”的记录在结果集中右表的那些字段值全为空值（NULL）。语法格式如下：SELECT…FROM表1LEFT[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【相关知识】【任务实施】查询没有选修课程的学生的基本信息

（1）先查看stuinfo与stumarks表做左外连接的结果集。代码如下：SELECT*FROMstuinfoLEFTJOINstumarksONstuinfo.stuno=stumarks.stuno;外连接与自连接8.2【任务实施】执行结果：外连接与自连接8.2图8.6stuinfo表与stumarks表做左外连接的结果【任务实施】（2）结果集中没有选课的学生所在的行，对应stumarks表中的那些字段值全为NULL。这个特点正好可以用来判断哪些学生没有选课，根据实体完整性规则，stumarks表中参与内连接的那些行，主属性（构成主键的字段）不可能为NULL，这里有两个主属性stuno与cno，通过判断它们其中任何一个是否为空值就可以筛选出那些没有选课的学生的基本信息。 SELECTstuinfo.*FROMstuinfoLEFTJOINstumarksONstuinfo.stuno=stumarks.stunoWHEREstumarks.stunoISNULL;外连接与自连接8.2【任务实施】外连接与自连接8.2图8.7查询没有选修课程的学生的基本信息【任务实施】2．查找同一课程成绩相同的选课记录。SELECTa.stuno,b.stuno,o,a.stuscoreFROMstumarksa,stumarksbWHEREa.stuscore=b.stuscoreANDa.stuno<>b.stunoANDo=o;外连接与自连接8.2图8.8查询同一课程成绩相同的选课记录思政小贴士【课堂实践分组管理，组长负责协调组内学习能力较强者指导组内较差的同学完成课堂作业】培养认真负责的工作态度、一丝不苟的工匠精神、团队合作意识。8.1交叉连接与内连接任务8.3子查询

使用子查询完成对“学生成绩管理”数据库（stuDB）涉及多表数据的查询或者复杂的单表查询操作，这种多表查询有个特点，查询的数据项在同一个表中，而筛选记录需要通过其他表的数据进行。具体任务如下：（1）查询选修了课程的学生的基本信息。（2）查询没有选修课程的学生的基本信息。（3）查询选修了“高等数学”这门课的学生的基本信息。（4）查询成绩最高的选课记录。【任务描述】8.3子查询【相关知识】31[NOT]EXISTS子查询[NOT]IN子查询8.3子查询2

比较子查询子查询是指一个查询块嵌套在SELECT、INSERT、UPDATE、DELETE等语句中的WHERE或其他子句中进行查询。SQL语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。根据子查询执行是否依赖于外部查询，子查询可分为相关子查询与不相关子查询两大类。不相关子查询是指不依赖于外部查询的子查询，反之，则称为相关子查询；不相关子查询先于外部查询执行，子查询得到的结果集不会显示，而是传给外部查询使用，不相关子查询总共执行一次；相关子查询的执行依赖于外部查询，即需要外部查询给它传递值，与外部查询正在判断的记录有关，外部查询执行一行，相关子查询就执行一次。8.3子查询【相关知识】子查询返回的值要被外部查询的[NOT]IN、[NOT]EXISTS、比较运算符、ANY（SOME）、ALL等操作符使用，根据操作符的不同，子查询可以分为以下几种：1．[NOT]IN子查询在嵌套查询中，子查询的结果往往是一个集合，用谓词IN判断某列值是否在集合中，这是最常用的一种子查询，IN前面加NOT表示判断某列值是否不在集合中。IN子查询一般是不相关子查询。8.3子查询【相关知识】2．比较子查询带有比较运算符的子查询是指外部查询与子查询之间用比较运算符进行连接。当用户确切知道内层查询返回单个值时，可以用>、<、=、>=、<=、!=或<>等比较运算符。比较子查询可能是不相关子查询，也可能是相关子查询，要看具体情况。3．[NOT]EXISTS子查询使用EXISTS谓词来判断子查询是否返回任何记录，当子查询的结果不为空集（即存在匹配行）时，返回逻辑真值。EXISTS前面可以加NOT用来判断是否不存在匹配行。EXISTS子查询是相关子查询。8.3子查询【相关知识】【任务实施】1.查询选修了课程的学生的基本信息。（1）用IN子查询第一步：查找出所有选修了课程的学生的学号SELECTDISTINCTstunoFROMstumarks第二步：根据前一步得到的学号集合查这些学生的基本信息SELECT*FROMstuinfoWHEREstunoIN(SELECTDISTINCTstunoFROMstumarks);8.3子查询【任务实施】（2）用EXISTS子查询SELECT*FROMstuinfoWHEREEXISTS(SELECT*

FROMstumarks

WHEREstuno=stuinfo.stuno);这里子查询的查询条件依赖于外部查询传递进来的值：stuinfo.stuno（该生学号）。8.3子查询【任务实施】8.3子查询图8.9选修了课程的学生的基本信息【任务实施】2.查询没有选修课程的学生的基本信息。（1）用IN子查询SELECT*FROMstuinfoWHEREstunoNOTIN(SELECTDISTINCTstuno

FROMstumarks);（2）用EXISTS子查询SELECT*FROMstuinfoWHERENOTEXISTS(SELECT*

FROMstumarks

WHEREstuno=stuinfo.stuno);8.3子查询【任务实施】8.3子查询图8.10查询没有选修课程的学生的基本信息【任务实施】3.查询选修了“高等数学”这门课的学生的基本信息。第一步：查找‘高等数学’这门课的课程号SELECTcnoFROMstucourseWHEREcname=‘高等数学’;第二步：根据‘高等数学’的课程号查选修该门课的学生学号SELECTstunoFROMstumarksWHEREcno=(SELECTcno

FROMstucourse

WHEREcname=‘高等数学’);8.3子查询【任务实施】第三步：根据学号找学生基本信息SELECT*FROMstuinfoWHEREstunoIN(SELECTstuno

FROMstumarks

WHEREcno=(SELECTcno

FROMstucourse

WHEREcname=‘高等数学’));8.3子查询【任务实施】8.3子查询图8.11查询选修了“高等数学”这门课的学生的基本信息【任务实施】4.查询成绩最高的选课记录第一步：查找学生选课表中的最高成绩

SELECTMAX(stuscore)FROMstumarks;第二步：查找成绩等于最高成绩的选课记录SELECT*FROMstumarksWHEREstuscore=(SELECTmax(stuscore)

FROMstumarks);8.3子查询【任务实施】8.3子查询图8.12查询成绩最高的选课记录思政小贴士【课堂实践分组管理，组长负责协调组内学习能力较强者指导组内较差的同学完成课堂作业】培养认真负责的工作态度、一丝不苟的工匠精神、团队合作意识。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语句的条件子句中使用子查询，基本可以满足这种筛选需求。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图8.13从一个表向另一个表复制多行多列数据【任务实施】2．修改stuinfo_2表中“S001”同学的平均成绩(avg_stuscore)（注：平均分统计根据stumarks表中该生的选课成绩）。UPDATEstuinfo_2SETavg_stuscore=(SELECTAVG(stuscore)

FROMstumarksWHEREstuno='S001')WHEREstuno='S001';子查询在更新语句中的应用8.4图8.14嵌套修改（使用不相关子查询）【任务实施】思考题：如果要一次修改所有同学的平均分，上面代码应该怎么改进？

子查询在更新语句中的应用8.4UPDATEstuinfo_2SETavg_stuscore=(SELECTAVG(stuscore)

FROMstumarks

WHEREstuno=stuinfo_2.stuno);【任务实施】3．把“高等数学”这门课的所有选修成绩都加5分。UPDATEstumarksSETstuscore=stuscore+5WHEREcno=(SELECTcno

FROMstucourseWHEREcname='高等数学');子查询在更新语句中的应用8.4图8.16U