MySQL数据库管理与应用 单元5 查询数据教案

教案授课教师部门课程名称MySQL数据库管理与应用课次14，15，16，17，18，19单元标题单元5查询数据授课学时12学时授课班级授课时间上课地点合作教师日期节次教学目标知识目标1.熟练掌握使用SELECT语句查询单个数据表中列数据的方法。2.熟练掌握使用SELECT语句查询单个数据表中行数据的方法。3.熟练掌握对查询结果进行排序的方法。4.熟练掌握利用聚合函数对查询结果进行统计的方法。5.熟练掌握分组查询的方法，理解HAVING与WHERE子句的区别。6.理解交叉连接、等值连接、自然连接之间的关系，熟练掌握使用连接查询查询多个数据表中数据的方法。7.掌握左外连接和右外连接查询的方法。8.理解子查询，掌握使用子查询查询数据的方法。能力目标1.会进行精确查询和模糊查询。2.会根据需要使用聚合函数进行查询结果的统计或汇总。3.会按照要求对查询结果排序。4.会根据需要对查询结果分组。5.会根据需要实现多表查询。6.会实现子查询。素质目标1.培养学生吃苦耐劳的品质和精益求精的工匠精神。2.使学生理解数据库的主要用途，培养学生主动探索和自主学习的能力。3.通过以小组为单位学习，培养学生的人际沟通能力和团队协作意识。教学重点1.用SELECT语句询单个数据表的方法。2.利用聚合函数对查询结果进行统计的方法。3.分组查询的方法。4.使用连接查询查询多个数据表中数据的方法。5.左外连接和右外连接查询的方法。6.子查询。教学难点1.分组查询的方法。2.利用聚合函数对查询结果进行统计。3.外连接查询的方法。4.子查询。教学方法线上自学，课堂理实一体化教学、项目式教学，第二课堂拓展学习等。教学资源教材数字资源教材配套微课、PPT、数据库建库建表脚本、习题解答等；智慧职教平台：学习通平台：其他：教学环境配备有电脑、电子教室或投影仪、有线或无线网络环境、黑板或白板等教学工具的计算机房或多媒体教室。MySQL、MySQLWorkbench或NavicatforMySQL等软件。教学过程第14次课（任务1实现学生成绩管理数据库的单表查询）教学环节教学活动主要内容学生活动说明课前预习学生自由组合，4人一组，以小组为单位学习。学生通过在线课程平台预习本单元内容。课中讲练一、导入新课使用数据库和表的主要目的是存储数据，以便在需要的时候进行查询、统计和输出，数据库的查询是数据库应用中最核心和最常用的操作。在MySQL中，对数据库的查询使用SELECT语句，该语句是SQL的核心，具有十分强大的功能且使用灵活。使用SELECT语句既可以完成简单的单表查询，也可以完成复杂的连接查询和子查询，其基本语法格式如下。SELECT[ALL|DISTINCT|DISTINCTROW]select_expr[,select_expr]…[FROMtable_references[PARTITIONpartition_list]][WHEREwhere_condition][GROUPBY{col_name|expr|position}[ASC|DESC],...[WITHROLLUP]][HAVINGwhere_condition][ORDERBY{col_name|expr|position}[ASC|DESC],...][LIMIT{[offset,]row_count|row_countOFFSEToffset}]二、新课讲解任务1实现学生成绩管理数据库的单表查询教师演示讲解操作过程，介绍相关命令的用法。单表查询是指仅涉及一个表的查询。5.1.1选择列最基本的SELECT语句仅有要返回的列和这些列的来源表，这种不使用WHERE子句的查询称为无条件查询，也称作投影查询。1．查询表中所有的列使用SELECT语句查询表中所有的列时，不必逐一列出列名，可用“*”通配符代替所有列名，但此时只能按照数据表中列的原有顺序进行排列。【例题5.1】在学生成绩管理数据库cjgl中，查询学生表xs中每位学生的信息。2．查询表中指定的列许多情况下，用户只对表中的部分列感兴趣，可以使用SELECT语句查询表中指定的列，各列名之间要以英文逗号分隔，列的显示顺序可以改变。【例题5.2】在学生成绩管理数据库cjgl中，查询学生表xs中每位同学的姓名、学号和专业名。3．查询经过计算的列SELECT子句中的列名列表可以是表达式，如例题5.3中用到了日期函数year()，用于输出对列值计算后的值。【例题5.3】在学生成绩管理数据库cjgl中，查询学生表xs中每位同学的学号、姓名和年龄。4．消除重复行关键字DISTINCT可用于消除查询结果中以某列为依据的重复行，以保证行的唯一性。DISTINCT关键字必须放在列名列表的前面，如果涉及多个列，则会对多个列进行组合去重。【例题5.4】在学生成绩管理数据库cjgl中，查询选修了课程的学生的学号。5．限制返回的行数当数据表中有很多行数据时，一次性查询出表中的全部数据会降低数据返回的速度。可以用LIMIT子句来限制查询结果返回的行数。LIMIT子句可用于指定查询结果从哪条记录开始显示多少条记录，其基本语法格式如下。LIMIT[offset,]row_count|row_countOFFSEToffset【例题5.5】在学生成绩管理数据库cjgl中，查询选修了课程的前6位学生的学号，返回从第3条记录开始的4条记录。5.1.2选择行教师演示讲解操作过程，介绍相关命令的用法。当要在表中查找出满足某些条件的行时，需要使用WHERE子句指定查询条件，这种查询称为选择查询，其语法格式如下。WHERE<search_condition>其中，查询条件可以是表达式比较、范围比较、确定集合、模糊查询、空值判断和子查询等表达式，其结果为TRUE、FALSE或UNKNOWN。1．表达式比较比较运算符用于比较两个表达式的值。比较运算的语法格式如下。expression{=|<|<=|>|>=|<>}expression其中，expression是除TEXT、NTEXT和IMAGE类型外的表达式。【例题5.6】在学生成绩管理数据库cjgl中，查询软件技术专业的学生的信息。当需要通过WHERE子句指定一个以上的查询条件时，则需要使用逻辑运算符AND、OR、XOR和NOT将其连成复合的逻辑表达式。【例题5.7】在学生成绩管理数据库cjgl中，查询软件技术专业的男同学的信息。2．范围比较当要查询的条件是某个值的范围时，可以使用关键字BETWEEN。BETWEEN用于检查某个值是否在两个值之间，其语法格式如下。expression[NOT]BETWEENexpression1ANDexpression2【例题5.8】在学生成绩管理数据库cjgl中，查询2000年出生的学生的信息。3．确定集合IN运算符用来查询属性值属于指定集合的元组，主要用于表达子查询，其语法格式如下。expression[NOT]IN(subquery|expression[,…n])【例题5.9】在学生成绩管理数据库cjgl中，查找选修了课程号为“101”或“102”的同学的信息。4．模糊查询当不知道精确的值时，可以使用LIKE关键字进行部分匹配查询，也称模糊查询。模糊查询的一般语法格式如下。string_expression[NOT]LIKEstring_expression[ESCAPEescape_character]字符串常量可以包含表5-1所示的MySQL通配符。若要匹配用作通配符的字符，可用关键字ESCAPE，ESCAPEescape_character表示将字符escape_character作为实际的字符对待。【例题5.10】在学生成绩管理数据库cjgl中，查找所有王姓同学的学号和姓名；查询姓名中第2个汉字是“长”的同学的学号和姓名。5．空值判断当需要判定一个表达式的值是否为空值时，可使用ISNULL关键字，其语法格式如下。expressionIS[NOT]NULL【例题5.11】在学生成绩管理数据库cjgl中，查询没有考试成绩的学生的学号和相应的课程号。课堂小结：教师针对学生实验中出现的典型问题进行点评，学生讨论、查漏补缺。教师总结本次课学习的主要内容。学生小组练习，教师巡视指导学生小组练习课后拓展提升学生利用某种国产数据库平台（如达梦数据库等）在cjgl数据库中完成本节课所学的查询操作。课后作业教材习题5教学总结第15次课（任务1实现学生成绩管理数据库的单表查询）教学环节教学活动主要内容说明课前预习学生通过在线课程平台预习本单元内容。课中讲练一、复习巩固教师检查学生对安装、配置和卸载MySQL的掌握情况，通过上节课作业的完成情况，对学生吸收不好的知识点进行再次巩固讲解。二、导入新课三、新课讲解任务1实现学生成绩管理数据库的单表查询5.1.3对查询结果进行排序教师演示讲解操作过程，介绍相关命令的用法。使用ORDERBY子句可以对查询结果进行排序，其语法格式如下。ORDERBY{col_name|expr|position}[ASC|DESC][,…n]【例题5.12】在学生成绩管理数据库cjgl中，将软件技术专业的学生按出生时间降序排列。5.1.4使用聚合函数查询教师演示讲解操作过程，介绍相关命令的用法。在SELECT语句中，可以利用聚合函数对查询结果进行统计。聚合函数也称为统计函数，主要用于对数据集合进行统计，返回单个计算结果，如总和、平均值、最大值、最小值、行数，一般用于SELECT子句、HAVING子句和ORDERBY子句中。MySQL提供的聚合函数如表5-2所示。下面介绍几个常用的聚合函数。1．SUM()和AVG()SUM()和AVG()分别用于求表达式中所有值的总和与平均值，忽略空值。其语法格式如下。SUM/AVG([ALL|DISTINCT]expression)【例题5.13】在学生成绩管理数据库cjgl中，查询学号为“001101”的学生的总分和平均分。2．MAX()和MIN()MAX()和MIN()分别用于求表达式中所有值的最大值与最小值，忽略空值。其语法格式如下。MAX/MIN([ALL|DISTINCT]expression);【例题5.14】在学生成绩管理数据库cjgl中，查询选修了课程号为“206”课程的学生的最高分和最低分。3．COUNT()COUNT()用于统计满足条件的行数或总行数，COUNT()函数对空值不进行计算，但会对0进行计算。其语法格式如下。COUNT({[ALL|DISTINCT]expression}|*)【例题5.15】在学生成绩管理数据库cjgl中，查询学生的总人数。5.1.5分组统计查询教师演示讲解操作过程，介绍相关命令的用法。在SELECT语句中，可以利用GROUPBY子句和HAVING子句等实现分组查询。1．GROUPBY子句使用GROUPBY子句可以将查询结果按列或列的组合在行的方向上进行分组或分组统计，如对各个分组求总和、平均值、最大值、最小值、行数，每组在列或列的组合上具有相同的聚合值。GROUPBY子句的语法格式如下。GROUPBY[ALL]group_by_expression[,…,n][WITH{CUBE|ROLLUP}]【例题5.16】在学生成绩管理数据库cjgl中，将学生表xs中的数据按性别分组；查询各专业的学生人数；查询每位学生的学号及其选课的数量。单独使用GROUPBY子句时，查询结果只显示每个分组的第一条记录，在实际应用中意义不大。因此，GROUPBY子句通常和聚合函数配合使用，以达到分组统计的目的。2．HAVING子句使用GROUPBY子句对数据进行分组后，还可以使用HAVING子句对分组数据集合进行筛选。HAVING子句支持WHERE子句中所有的操作符和语法。【例题5.17】在学生成绩管理数据库cjgl中，查询平均成绩大于85的学生的学号及平均成绩。在包含GROUPBY子句的查询中，有时需要同时使用WHERE子句和HAVING子句，此时应注意WHERE、GROUPBY及HAVING这3个子句的执行顺序及含义。首先，用WHERE子句筛选FROM关键字指定的数据，将不符合WHERE子句中的条件的行剔除；然后，用GROUPBY子句对WHERE子句的查询结果分组；最后，用HAVING子句对GROUPBY子句的分组结果进行筛选。【例题5.18】在学生成绩管理数据库cjgl中，查询选课门数在3门以上且各门课程均及格的学生的学号及其总分。老师讲解清楚WHERE子句和HAVING子句的区别。课堂小结：教师针对学生实验中出现的典型问题进行点评，学生讨论、查漏补缺。教师总结本次课学习的主要内容。学生小组练习，教师巡视指导学生小组练习学生小组练习课后拓展提升学生利用某种国产数据库平台（如达梦数据库等）在cjgl数据库中完成本节课所学的查询操作。课后作业教材习题5教学总结第16次课（任务1实现学生成绩管理数据库的单表查询）教学环节教学活动主要内容学生活动说明课前预习学生自由组合，4人一组，以小组为单位学习。学生通过在线课程平台预习本单元内容。课中讲练一、复习巩固教师检查学生对安装、配置和卸载MySQL的掌握情况，通过上节课作业的完成情况，对学生吸收不好的知识点进行再次巩固讲解。二、导入新课三、新课讲解任务1实现学生成绩管理数据库的单表查询教师演示讲解操作过程，介绍相关命令的用法。3．WITHROLLUP关键字WITHROLLUP关键字用来在所有记录的最后加上一条记录，该记录是前面所有记录的总和，起到总计的作用。【例题5.19】在学生成绩管理数据库cjgl中，进行如下操作。（1）查找各专业的学生人数，并生成一个学生总人数行。（2）查找各专业的学生人数和学生姓名，并生成一个学生总人数行。函数GROUP_CONCAT(name)用于显示每个分组的所有name列的值。5.1.6用查询结果生成新表教师演示讲解操作过程，介绍相关命令的用法。使用CREATETABLE语句可以将通过SELECT语句查询所得的结果保存到一个新建的表中，其语法格式如下。CREATETABLEnew_table[AS]SELECT*FROMtable;其中，new_table是要创建的新表名。【例题5.20】在学生成绩管理数据库cjgl中，依据学生表xs创建软件技术专业学生表rjxs1，其中包括学号、姓名和性别列。5.1.7合并结果表教师演示讲解操作过程，介绍相关命令的用法。两个或多个SELECT查询的结果可以合并到一个表中，并且不需要对这些行进行任何修改，但要求所有查询结果中的列数和列的顺序必须相同、数据类型必须兼容，这种操作称为联合查询。联合查询常用于归档数据，其运算符为UNION，语法格式如下。{<queryspecification>|(<queryexpression>)}UNION[ALL]{<queryspecification>|(<queryexpression>)}[…n]其中，queryspecification和queryexpression都是SELECT查询语句。【例题5.21】在学生成绩管理数据库cjgl中，新建软件技术专业学生表rjxs、网络技术专业学生表wlxs，分别存储两个专业的学生信息，表结构与学生表xs相同，将这两个表的数据合并到学生表xs中。执行如下语句。USEcjgl;SELECT*FROMxsUNIONALLSELECT*FROMrjxsUNIONALLSELECT*FROMwlxs;执行以上代码后可以看到查询结果中出现了重复的行。如果想去掉这些重复行，只需将上述代码中的UNIONALL改成UNION即可。实验6人力资源管理数据库的单表查询（1）查找每位员工的所有信息。（2）查找所有名为John的员工的员工号、姓名、部门编号。（3）查找每位员工的员工号、姓名、部门编号、工作号、聘用日期、工资。（4）列出所有工资在6000～10000元的员工的员工号、姓名、部门编号和工资，并按照工资由高到低进行排序。（5）求员工总人数。（6）求各个部门的员工人数。（7）计算员工的总收入和平均收入。（8）计算各个部门员工的总收入和平均收入。（9）找出各个部门员工的最高和最低工资。课堂小结：教师针对学生实验中出现的典型问题进行点评，学生讨论、查漏补缺。教师总结本次课学习的主要内容。学生小组练习，教师巡视指导学生小组练习学生小组练习学生小组练习课后拓展提升学生利用某种国产数据库平台（如达梦数据库等）在cjgl数据库中完成本节课所学的查询操作。课后作业教材习题5实验6人力资源管理数据库的单表查询教学总结第17次课（任务2实现学生成绩管理数据库的连接查询）教学环节教学活动主要内容学生活动说明课前预习学生自由组合，4人一组，以小组为单位学习。学生通过在线课程平台预习本单元内容。课中讲练一、复习巩固教师检查学生对安装、配置和卸载MySQL的掌握情况，通过上节课作业的完成情况，对学生吸收不好的知识点进行再次巩固讲解。二、导入新课在关系数据库中，表与表之间是有联系的，所以在实际应用中，经常使用多表查询。当一个查询涉及两个以上的表时，则该查询称为连接查询。连接查询中用来连接两个表的条件叫作连接条件或连接谓词，连接条件的一般语法格式如下。[<表1>.]<列名1><比较运算符>[<表2>.]<列名2>其中，比较运算符主要有=、>、<、>=、<=、<>。连接条件中的列名称为连接字段。连接查询的目的就是通过连接条件将多个表连接起来，以便从多个表中查询数据。连接查询是关系数据库中最主要的查询。在MySQL中，根据查询方式的不同，连接主要分为交叉连接、内连接、自身连接和外连接等。连接查询的类型可以在SELECT语句的FROM子句中指定，也可以在WHERE子句中指定。在MySQL中，一般使用内连接和外连接，它们的效率要高于交叉连接。三、新课讲解任务2实现学生成绩管理数据库的连接查询5.2.1交叉连接查询教师演示讲解操作过程，介绍相关命令的用法。交叉连接（CROSSJOIN）又称笛卡儿连接，实际上是将两个表进行笛卡儿积运算，结果表是由第1个表的每行与第2个表的每行拼接后形成的表，因此，结果表的行数等于两个表的行数之积。交叉连接查询的语法格式如下。SELECT查询列表FROM表1CROSSJOIN表2;或SELECT查询列表FROM<表1>,<表2>;【例题5.22】在学生成绩管理数据库cjgl中，列出学生所有可能的选课情况。5.2.2内连接查询教师演示讲解操作过程，介绍相关命令的用法。内连接（INNERJOIN）主要通过设置连接条件的方式，移除查询结果中某些数据行的交叉连接。也就是利用条件表达式来消除交叉连接的某些数据行。内连接查询的语法格式如下。SELECT查询列表FROM<表1>[别名1]INNERJOIN<表2>[别名2]ON<连接条件表达式>[WHERE<条件表达式>];或SELECT查询列表FROM<表1>[别名1],<表2>[别名2][,…]WHERE<连接条件表达式>[AND<条件表达式>];如通过INNERJOIN连接3个数据表的方法如下。SELECT*FROM(表1INNERJOIN表2ON表1.列名=表2.列名)INNERJOIN表3ON表1.列名=表3.列名1．等值连接查询与非等值连接查询当连接运算符为“=”时，连接运算称为等值连接，其他情况则称为非等值连接。【例题5.23】在学生成绩管理数据库cjgl中，查找每个学生以及选修的课程信息。2．自然连接查询若在等值连接中把目标列中重复的值去掉，则该连接为自然连接。【例题5.24】在学生成绩管理数据库cjgl中，查找每个学生以及选修的课程信息。【例题5.25】在学生成绩管理数据库cjgl中，查询学生王元选修的课程。当进行3个以上表的内连接查询时，在FROM关键字后连续使用INNERJOIN或JOIN即可。通过INNERJOIN连接3个数据表的方法如下。SELECT*FROM(表1INNERJOIN表2ON表1.列名=表2.列名)INNERJOIN表3ON表1.列名=表3.列名【例题5.26】在学生成绩管理数据库cjgl中，查找学号、姓名、选修的课程名及成绩。【例题5.27】在学生成绩管理数据库cjgl中，查找选修了“C程序设计”课程且成绩在80分以上的学生的学号、姓名、课程名及成绩。5.2.3自身连接查询教师演示讲解操作过程，介绍相关命令的用法。连接操作不仅可以在两个表之间进行，也可以在一个表与其自身之间进行，即将同一个表的不同行连接起来，这种连接称为表的自身连接。自身连接是多表连接的一种特殊情况，可以看作一个表的两个副本之间的连接。自身连接通常用于表中的数据有层次结构的情形，如区域表、菜单表、商品分类表等。当需要在同一个表内进行比较、查找部分重复的记录或找出列的组合时，可以使用自身连接。例如，在人力资源管理数据库HR的员工表employees中有经理、员工两种身份，要查询某个员工属于哪个经理的部门，而有的员工本身就是经理，这时候就要用到自身连接。自身连接查询的语法格式如下。SELECT查询列表FROM<表1>[别名1]JOIN<表1>[别名2]ON<连接条件表达式>[WHERE<条件表达式>];课堂小结：教师针对学生实验中出现的典型问题进行点评，学生讨论、查漏补缺。教师总结本次课学习的主要内容。学生小组练习，教师巡视指导学生小组练习学生小组练习学生小组练习课后拓展提升学生利用某种国产数据库平台（如达梦数据库等）在cjgl数据库中完成本节课所学的查询操作。课后作业教材习题5教学总结第18次课（任务2实现学生成绩管理数据库的连接查询）教学环节教学活动主要内容学生活动说明课前预习学生自由组合，4人一组，以小组为单位学习。学生通过在线课程平台预习本单元内容。课中讲练一、复习巩固教师检查学生对安装、配置和卸载MySQL的掌握情况，通过上节课作业的完成情况，对学生吸收不好的知识点进行再次巩固讲解。二、导入新课三、新课讲解任务2实现学生成绩管理数据库的连接查询5.2.4外连接查询教师演示讲解操作过程，介绍相关命令的用法。在一般的连接操作中，只有满足连接条件的记录才能作为结果输出。但有时也需要使一个或两个表中不满足连接条件的记录出现在结果中，这时就需要用到外连接。外连接查询的语法格式如下。SELECT查询列表FROM<表1>LEFT|RIGHT[OUTER]JOIN<表2>ON<表1.列1>=<表2.列2>;其中，OUTER关键字可以省略。外连接只能对两个表进行。外连接会先将连接的表分为基表和参考表，然后以基表为依据返回满足和不满足条件的记录。外连接包括左外连接和右外连接。（1）左外连接（LEFTOUTERJOIN）是指结果表中除了包括满足连接条件的行外，还包括左表的所有行，此时左表为基表，右表为参考表。（2）右外连接（RIGHTOUTERJOIN）是指结果表中除了包括满足连接条件的行外，还包括右表的所有行，此时右表为基表，左表为参考表。【例题5.29】在学生成绩管理数据库cjgl中，查找所有学生及他们选修的课程号信息，若学生未选修任何课程，也要输出其信息。【例题5.30】在学生成绩管理数据库cjgl中，查找选修课程的信息和所有开设的课程名。实验7人力资源管理数据库中表的连接查询和子查询对于员工表employees和部门表departments，完成下列操作。（1）查找每位员工的基本信息和部门名称。（2）查找IT部门收入在6000～10000元的员工的员工号、姓名和工资，并按照工资由高到低进行排序。（3）求IT部门员工的平均收入。（4）求IT部门的员工人数。（5）查找比IT部门员工收入都高的员工的员工号、姓名和部门名称。课堂小结：教师针对学生实验中出现的典型问题进行点评，学生讨论、查漏补缺。教师总结本次课学习的主要内容。学生小组练习，教师巡视指导学生小组练习课后拓展提升学生利用某种国产数据库平台（如达梦数据库等）在cjgl数据库中完成本节课所学的查询操作。课后作业教材习题5实验7人力资源管理数据库中表的连接查询和子查询教学总结第19次课（任务3实现学生成绩管理数据库的子查询）教学环节教学活动主要内容说明课前预习学生通过在线课程平台预习本单元内容。课中讲练一、复习巩固教师检查学生对安装、配置和卸载MySQL的掌握情况，通过上节课作业的完成情况，对学生吸收不好的知识点进行再次巩固讲解。二、导入新课可以使用另一个查询的结果作为查询条件的一部分（即在WHERE子句中包含一个形如SELECT…FROM…WHERE的查询语句块），作为查询条件一部分的查询称为子查询或嵌套查询，包含子查询的语句称为父查询或外层查询。MySQL允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。嵌套查询可以让多个简单的查询构成复杂的查询，从而增强SQL的查询能力。嵌套查询是由里向外进行的，即先进行子查询，然后将子查询的结果用作其父查询的查询条件。子查询通常与IN、E