MySQL数据库 3.6 子查询与union查询教案

教案首页课程名称MySQL数据库开发本节课题任务3.6子查询与集合查询授课方式理实一体化教学参考及教具姜云桥主编.MySQL数据库开发.临科院：教务处,2023计算机MySQL数据库软件多媒体教学系统资料包教学目标及基本要求熟练使用子查询方式完成各种查询需求使用合并查询完成查询需求掌握子查询可以出现的位置。掌握标量子查询掌握列子查询掌握行子查询掌握表子查询掌握集合查询培养学生分析问题，解决问题的能力教学重点教学难点标量子查询、列子查询、行子查询、表子查询以及合并查询掌握子查询和集合查询的操作方法与技巧教学小结作业及要求按照任务书做好预习任务利用数据库rsgl完成标量子查询、列子查询、行子查询、表子查询以及合并查询。教后反思注意：用微课视频每个典型操作做示范

教案用纸教学内容、方法和过程附记任务3.6子查询与集合查询【任务要求】利用数据库jxgl中的student(学生信息表)、course（课程表）、score（成绩表）、department（院系单位表）、teacher（教师表）和teach（讲授表），完成标量子查询、列子查询、行子查询、表子查询以及合并查询。【任务必备知识】3.6.1子查询子查询是指嵌套在其他sql语句（如insert、delete、select、update等）中的查询语句。子查询也称为内查询，必须位于括号之中；包含子查询的查询被称为外查询。MySQL中的子查询按照返回结果可以分为以下几种类型： 标量子查询：返回单个值（一行一列）的子查询。 列子查询：返回单列结果（多行一列）的子查询。 行子查询（RowSubquery）：返回单行结果（一行多列）的子查询，标量子查询是行子查询的一个特例。 表子查询（TableSubquery）：返回一个虚拟表（多行多列）的子查询，行子查询是表子查询的一个特例。按照内查询与外查询之间的关系可分为： 非关联子查询：内查询不依赖外查询的信息，可独立运行。 关联子查询：内查询引用外查询的字段，无法独立运行。一、标量子查询是指子查询返回的是单一值的标量，如一个数字或一个字符串。可以使用=、>、<、>=、<=、<>等操作符对子查询的标量结果进行比较。【任务3.6.1】查询成绩表score，返回大于平均成绩的记录。mysql>select*->fromscore->wheremark>(selectavg(mark)fromscore);如上述查询所示，标量子查询的结果如同一个常量值一样，返回一个平均值。二、列子查询指子查询返回的结果集是N行一列，该结果通常来自对表的某个字段查询返回，可以使用in、any、some和all等操作符，含义如下：in 等于任何一个值即可all 结合比较运算符使用，满足所有值的比较any/some 结合比较运算符使用，满足任何一个值的比较即可【任务3.6.2】查询无授课任务的老师的信息。mysql>select*fromteacherwheret_noin(selectdistinctt_nofromteach);三、行子查询子查询返回的结果集是一行N列，该子查询的结果通常是对表的某行数据进行查询而返回的结果集。【任务3.6.3】查询与”刘光明“同学同性别同系部的学生的基本信息。mysql>select*->fromstudent->where(s_sex,d_no)=(selects_sex,d_nofromstudentwheres_name='刘光明')->ands_name<>'刘光明';四、表子查询子查询返回的结果集是N行N列的一个表数据。表子查询通常用于FROM子句或者查询条件中。【任务3.6.4】使用表子查询获取各系部名称和学生的数量。mysql>selectd.d_nameas'系部名称',ifnull(s.num,0)as'学生数量'->fromdepartmentdleftjoin(->selectd_no,count(*)asnumfromstudentgroupbyd_no->)s->ond.d_no=s.d_no;五、关联子查询内部查询引用了外部查询列，即内部查询会对外部查询的每行进行一次计算，需要注意，使用关联子查询是最容易出现性能的地方。【任务3.6.5】使用关联子查询获得各个系部的教师的数量，显示系部名称，教师数量。mysql>selectd.d_nameas'系部名称',->(selectcount(*)fromteachertwheret.d_no=d.d_no)as'教师数量'->fromdepartmentd;六、exists操作符exists检查指定查询是否产生某些行。根据子查询是否返回行，exists仅返回true或false，不会返回unkown，这是和其他操作符（如in、any、all等）的区别，notexists同样如此，只不过功能相反。【任务3.6.6】返回不存在女性学生的系部名称。mysql>selectd.d_name->fromdepartmentd->wherenotexists(->select1fromstudentstwherest.d_no=d.d_noandst.s_sex='女');3.6.2合并查询union操作符用于将两个查询结果合并成一个结果集。基本语法格式如下：selectcol_a1,col_a2,...fromtable1union[distinct|all]selectcol_b1,col_b2,...fromtable2;说明：1)列的数据类型必须兼容（如col_a1列和col_b1列，col_a2列和col_b2列）类型不必完全相同，但是必须可以隐式转换。2)distinct表示将合并后的结果集进行去重；all表示保留结果集中的重复记录；如果省略，默认为distinct。3)每个select语句中的列也必须以相同的顺序排列说明：union和unionall的orderby子句要写在最后一条select语句后面，虽然orderby看似只是最后一条select语句的组成部分，但实际上MySQL将用它来排序select语句返回的所有结果。如果合并的查询需要分别排序，可以选择下面的语法：(selectcol_a1,col_a2,...fromtable1）union[distinct|all](selectcol_b1,col_b2,...fromtable2）【项目实践】在人事管理数据库rsgl中进行如下操作1.查询和赵剑同职称、学历、政治背景的员工的姓名、职称、学历、政治背景，结果如下：+++++|e_name|professional|education|political|+++++|胡晓群|讲师|博士|党员|+++++2.查询没有员工的部门的名称，结果如下：++|d_name|++|宣传部||后勤处||金融学院|++3.查年龄大于50，并且不是信息学院与外语学院的员工姓名和性别,结果如下：+++|e_name|sex|+++|李明|男||张伟键|男||余明平|男||苏小明|男||王君君|女||欧阳|女||苏珍珍|女|+++4.查询收入最高（收入=基本收入+福利+补贴-公积金-税-其他）和最低的员工编号和收入，结果如下：+++|e_id|income|+++|100331|10