MySQL数据库项目化教程（第2版）（微课版）课件 项目五 数据查询

MySQL数据库项目化教程V1项目五数据查询项目任务知识目标学习目标任务1：简单查询任务2：连接查询任务3：子查询（1）了解简单查询和复合查询（2）掌握模糊查询的用法（3）掌握内连接、左外连接、右外连接和全连接查询的用法（4）掌握连接查询和子查询的区别能力目标素质目标学习目标（1）能够完成关于表的行和列的查询（2）能够使用模糊查询（3）能够实现自身连接查询（4）能够实现表间连接查询（1）培养学生的编程能力和业务素质（2）培养学生自我学习的习惯、爱好和能力（3）培养学生的科学精神和态度课程思政（素养提示）学习目标（1）数据库SQL查询语句的正确性、查询效率的优化都体现了严谨的工匠精神和求真务实的科学精神。（2）工匠精神是一种执着专注、精益求精、一丝不苟、追求卓越的精神。通过对数据查询的学习培养学生的工匠精神，形成科学的思维方法。思维导图学习目标任务分析任务完成任务描述Part

2Part

3Part

1任务一简单查询任务分析任务完成Part

1Part

2Part

3任务描述在数据库应用中，最常用的操作就是查询，它也是数据库其他操作的基础。数据查询不应只是简单返回数据库中存储的数据，还应该根据需要对数据进行筛选，以及确定数据以什么样的格式显示。简单查询通常是指查询的范围只涉及一个表的查询。以下根据要求分别从学生技能竞赛管理系统中查找相关数据信息。任务描述任务分析任务完成Part

1Part

2Part

3任务描述在学生技能竞赛管理系统中，学生需要在数据表中查询自己参加竞赛的信息，教师需要查询指导学生的信息等。在MySQL中，使用SELECT语句不仅能够从数据表中查询所需要的数据，也可以进行数据的统计汇总，将查询的数据以用户规定的格式整理返回给用户端。任务分析任务分析任务完成Part

1Part

2Part

3任务描述1.查询表中指定字段用户可以查询表中的所有字段进行显示，如果有些列在本次查询中无关紧要，也可以只查询部分字段。SELECT查询语句最简单的形式是从一个表中检索所有记录，实现的方法是使用星号（*）通配符指定查询所有字段名称。语法格式如下：SELECT*FROM表名;【例5-1】查询student表中所有学生的详细信息。SELECT*FROMstudent;【例5-2】查询teacher表中所有教师的详细信息。SELECT*FROMteacher;所有字段所有字段运行结果【例5-1】【例5-2】使用SELECT查询语句，可以获取多个字段下的数据，只需要在关键字SELECT后面指定要查找的字段的名称即可，不同字段名称之间用逗号（，）隔开，最后一个字段后面不需要加逗号，语法格式如下：SELECT字段名1,字段名2,…,字段名nFROM表名;【例5-3】查询student表中学生的学号和姓名。SELECTst_no,st_nameFROMstudent;【例5-4】查询teacher表中教师的姓名和信息。SELECTtc_name,tc_infoFROMteacher;1.查询表中指定字段部分字段部分字段运行结果【例5-3】【例5-4】为字段取别名在有些情况下，显示的列的名称会很长或者名称不够直观，MySQL可以指定列别名，替换字段或表达式。为字段取别名的基本语法形式为：列名[AS]列别名“列名”为表中字段定义的名称，“列别名”为字段新的名称，AS关键字为可选参数。【例5-5】查询student表中所有学生的学号、姓名和班级编号，要求字段名为汉字形式。SELECTst_noAS学号,st_nameAS姓名,class_idAS班级编号FROMstudent;为字段取别名【例5-6】查询teacher表中所有教师的工号、姓名和性别，要求字段名为汉字形式。SELECTtc_no教师工号,tc_name姓名,tc_sex性别FROMteacher;1.查询表中指定字段运行结果【例5-5】【例5-6】简单条件查询2.选择行选择行查询就是通过满足某些条件进行查询，从而查询出相应的记录，通常会把相应的查询条件放到WHERE子句。语法形式为：SELECT字段名1,字段名2,…,字段名nFROM表名WHERE查询条件;简单条件查询表时只有一个查询条件。简单条件查询【例5-7】查询student表中院系编号为4的所有学生信息。SELECT*FROMstudentWHEREdp_id=4;运行结果【例5-7】复合条件查询2.选择行复合条件查询通常使用AND或者OR连接多个查询条件。其中AND表示前后的两个查询条件要同时成立，OR表示前后的两个查询条件其中一个成立即可。OR可以和AND一起使用，但是在使用时要注意两者的优先级，由于AND的优先级高于OR，因此先对AND两边的操作数进行操作，再与OR中的操作数结合。语法形式如下：SELECT字段1,字段2,…,字段nFROM表名WHERE查询条件1AND查询条件2AND…AND查询条件n;【例5-8】查询student表中院系编号为4的所有女学生的信息。SELECT*FROMstudentWHEREdp_id=4ANDst_sex='女';复合条件查询【例5-9】查询class表中院系编号为1和2的班级信息。SELECT*FROMclassWHEREdp_id=1ORdp_id=2;运行结果【例5-8】【例5-9】指定范围查询2.选择行指定范围查询表示要查询的记录在指定的条件范围内或者不在指定的条件范围内，使用[not]BETWEENAND来查询[不在]某个范围内的值，该操作符需要两个参数，即范围的开始值和结束值，如果字段值满足指定的范围查询条件，则这些记录被返回。语法形式如下：SELECT字段1,字段2,…,字段nFROM表名WHERE查询条件[not]BETWEEN开始值AND结束值;指定范围查询【例5-10】在project表中查询培训天数在20到35天的竞赛项目名称及培训天数。SELECTpr_name,pr_daysFROMprojectWHEREpr_daysBETWEEN20AND35;运行结果【例5-10】模糊条件查询2.选择行在前面的查询操作中，讲述了如何查询多个字段的记录，如何进行比较查询或者是查询一个条件范围内的记录，如果要查找所有的包含字符“陈”的学生名字，该如何查找呢？简单的比较操作在这里已经行不通了，在这里需要使用通配符进行匹配查找，通过创建查找模式对表中的数据进行比较。执行这个任务的关键字是LIKE。通配符是一种在SQL的WHERE条件子句中拥有特殊意思的字符，SQL语句中支持多重通配符。一般模糊条件查询格式如下：SELECT字段FROM表WHERE某字段LIKE条件；模糊条件查询%：匹配任意长度的字符，甚至包括零字符。【例5-11】查询student表中姓“陈”的学生信息。SELECT*FROMstudentWHEREst_nameLIKE'陈%';运行结果【例5-11】模糊条件查询2.选择行_:表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度。如果要匹配多个字符，则需要使用相同个数的“_”。【例5-12】查询student表中姓“李”的且名字为两个字的学生信息。SELECT*FROMstudentWHEREst_nameLIKE'李_';[]:表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配的对象为他们中的任一个。【例5-13】查询student表中名字带有单字“旭”的学生，姓可以为李、王、林、张。SELECT*FROMstudentWHEREst_nameREGEXP'[李王林张]旭';模糊条件查询运行结果【例5-12】【例5-13】模糊条件查询2.选择行[^]：表示不在括号所列之内的单个字符。其取值和[]相同，但它所要求匹配对象为指定字符以外的任一个字符。【例5-14】查询student表中名字带有单字“灿”的学生，姓不可以为王、林、张。SELECT*FROMstudentWHEREst_nameREGEXP'[^王林张]灿';空值查询2.选择行数据表创建的时候，设计者可以指定某列中是否可以包含空值（NULL）。空值不同于0，也不同于空字符串。空值一般表示数据未知、不适用或将在以后添加数据。在SELECT语句中使用ISNULL子句，可以查询某字段内容为空记录。空值查询【例5-15】查询teacher表中2号院系没有信息介绍的教师信息。SELECT*FROMteacherWHEREdp_id=2ANDtc_infoISNULL;消除重复行2.选择行从例题5-15可以看到返回了6条记录，其中性别只有男，有时，处于对数据分析的要求，需要消除重复的记录值，如何使查询结果没有重复呢？在SELECT语句中，可以使用DISTINCT关键字指示MySQL消除重复的记录。如果查询结果有重复值并且所有重复值的次数并不重要的情况下，可以消除重复行。消除重复行【例5-16】查询teacher表中院系编号为2的教师性别情况。SELECTDISTINCTtc_sexFROMteacherWHEREdp_id=2;显示前N行2.选择行SELECT返回所有匹配的行，有可能是表中所有的行，如仅仅需要返回第一行或者前几行，使用LIMIT关键字，基本语法形式如下：LIMIT[位置偏移量,]行数;显示前N行【例5-17】查询前3名学生的信息。SELECT*FROMstudentLIMIT3;COUNT()函数3.使用聚合函数COUNT()函数统计数据表中包含的记录行的总数，或者根据查询结果返回列中包含的数据行数。其使用方法有两种：计算表中总的行数，不管某列有数值或者为空值。COUNT(*)计算指定列下总的行数，计算时将忽略空值的行。COUNT(字段名)COUNT()函数【例5-18】统计student表中男学生的人数。SELECTCOUNT(*)AS男生人数FROMstudentWHEREst_sex='男';SUM()函数3.使用聚合函数SUM()是一个求总和的函数，返回指定列值的总和。AVG()函数【例5-20】统计project表中各种竞赛项目的平均培训天数。SELECTAVG(pr_days)AS平均培训天数FROMproject;【例5-19】统计院系编号为6的竞赛项目的总培训天数。SELECTSUM(pr_days)AS总培训天数FROMprojectWHEREdp_id=6;AVG()函数通过计算返回的行数和每一行数据的和，求得指定列数据的平均值。运行结果【例5-19】【例5-20】MAX()函数3.使用聚合函数MAX()返回查询列中的最大值。MIN()函数【例5-22】在project表中找出所有竞赛项目中的最小培训天数。SELECTMIN(pr_days)AS最小培训天数FROMproject;【例5-21】在project表中找出所有竞赛项目中的最大培训天数。SELECTMAX(pr_days)AS最大培训天数FROMproject;MIN()返回查询列中的最小值。分组查询4.分组查询分组查询是对数据按照某个或多个字段进行分组，可以使用GROUPBY子句，用于将结果集中的行分成若干个组来输出，在一个查询语句中，如果用了GROUPBY子句，则SELECT子句中的列只能使用分组项字段和聚合函数。基本语法形式为：[GROUPBY字段][HAVING<条件表达式>]字段值为进行分组时所依据的列名称；“HAVING<条件表达式>”指定满足表达式限定条件的结果将被显示。分组查询【例5-23】查询表project中平均培训天数大于40天的院系编号和平均培训时间。SELECTdp_id,AVG(pr_days)AS平均培训天数FROMprojectGROUPBYdp_idHAVINGAVG(pr_days)>40;排序输出5.排序输出从前面的查询结果，我们会发现有些字段的值是没有任何顺序的，MySQL可以通过在SELECT语句中使用ORDERBY子句，对查询的结果进行排序。有升序排序和降序排序，默认为升序排序。排序输出【例5-24】查询表project中竞赛项目的项目名称和培训时间，并按培训天数降序排列。SELECTpr_name,pr_daysFROMprojectORDERBYpr_daysDESC;运行结果【例5-24】任务总结本任务主要介绍在MySQL软件中关于单表数据的查询操作，使用SELECT语句实现，从选择列查询、选择行查询、使用聚合函数查询、分组查询和排序输出五个方面进行介绍。查询是对数据库里的数据进行检索，并按用户要求返回所需数据的过程。它是SQL语言的核心操作。任务分析任务完成任务描述Part

2Part

3Part

1任务二连接查询任务分析任务完成Part

1Part

2Part

3任务描述一个数据库中，通常存在多张数据表，用户一般需要用多个表进行组合查询来查询出所需要的信息。如果一个查询需要对多个表进行操作，那么这样的操作就称为连接查询。多表连接查询是关系数据库中最重要也是最常用的查询。多表连接查询分为内连接、外连接和自连接等不同的连接方式，可以实现用户各种各样的查询要求。任务描述任务分析任务完成Part

1Part

2Part

3任务描述教师的基本信息存储在教师表（teacher）中，而项目编号存储在教师指导项目表（tc_project）中，这就涉及两个表的查询了。而这两个表中有一个公共属性，即教师编号（tc_id），可以通过教师编号这个公共属性将这两个表连接起来，以得到符合要求的查询结果。任务分析任务分析任务完成Part

1Part

2Part

3任务描述自身连接查询1.第一种内连接查询如果在一个连接查询中，涉及到的两个表都是同一个表，这种查询称为自连接查询。自连接是一种特殊的内连接，它是指相互连接的表在物理上为同一张表，但可以在逻辑上分为两张表。【例5-25】查询所有教师工号比“张咏风”大的教师的姓名、工号和性别。SELECTA.tc_name,A.tc_no,A.tc_sexFROMteacherA,teacherBWHEREB.tc_name='张咏风'ANDA.tc_no>B.tc_no;表间连接查询在多个不同表之间进行连接，这样的连接称为表间连接查询。【例5-26】查询“王晓东”学生参加的竞赛项目名称、培训地点、培训开始时间和培训结束时间。SELECTpr_name,pr_trainaddress,pr_starttime,pr_endtimeFROMstudent,st_project,projectWHEREst_name='王晓东'ANDstudent.st_id=st_project.st_idANDst_project.pr_id=project.pr_id;运行结果【例5-25】【例5-26】内连接查询2.第二种内连接查询除了以上的内连接查询以外，还可以使用JOINON实现内连接查询。【例5-27】用JOINON查询编号为27的学生的班级号、班级名和年级。SELECTclass.class_no,class.class_name,class.class_gradeFROMstudentJOINclassONstudent.st_id=27ANDstudent.class_id=class.class_id;左外连接3.外连接查询左向外连接的结果集包括

LEFTOUTER子句中指定的左表的所有行，而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。【例5-28】查询3号院系所有的学生姓名、性别、参赛项目编号和对应的指导教师编号，如果该学生没有参赛，也需要显示参赛项目编号和对应的指导教师编号。SELECTstudent.st_name,student.st_sex,st_project.pr_id,st_project.tc_idFROMstudentLEFTOUTERJOINst_projectONstudent.st_id=st_project.st_idWHEREdp_id=3;运行结果【例5-28】右外连接3.外连接查询右向外连接是左向外连接的反向连接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。【例5-29】查询所有的教师姓名、性别及指导的项目编号，如果该教师没有指导竞赛，也需要显示项目编号。SELECTteacher.tc_name,teacher.tc_sex,st_project.pr_idFROMst_projectRIGHTOUTERJOINteacherONteacher.tc_id=st_project.tc_id;运行结果【例5-29】任务总结本任务主要是分两部分来讲解连接查询。1.内连接（典型的连接运算，使用像=

或<>之类的比较运算符）。包括相等连接和自然连接。

内连接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如，检索student和st_project表中学生编号相同的所有行。

2.外连接。外连接可以是左向外连接、右向外连接或完整外部连接。

任务分析任务完成任务描述Part

2Part

3Part

1任务三子查询任务分析任务完成Part

1Part

2Part

3任务描述当一个查询是另一个查询的条件时，称之为子查询。子查询是一个SELECT语句，它嵌套在一个SELECT、SELECT...INTO语句、INSERT...INTO语句、DELETE语句或UPDATE语句或嵌套在另一子句查询中。在学生技能竞赛管理系统中，要查询出所有参加竞赛的学生的学号和姓名。任务描述任务分析任务完成Part

1Part

2Part

3任务描述根据任务二可知，使用连接查询将学生表（student）与学生参赛表（st_project）按照学生编号相等连接，即可得到已经参加竞赛的学生姓名和项目编号，因为凡是在学生参赛表中的学生都是已经参加竞赛的。除使用连接查询之外，还可以使用子查询，子查询又称嵌套查询。任务分析任务分析任务完成Part

1Part

2Part

3任务描述1.使用EXISTS的子查询使用EXISTS关键字引入子查询后，子查询的作用就相当于进行存在测试。外部查询的WHERE子句测试子查询返回的行是否存在。子查询实际上不产生任何数据，它只返回TRUE或FALSE值，其目标表达式通常都是*。【例5-30】查询已有学生参赛的项目和培训天数。SELECTpr_name,pr_timeFROMprojectWHEREEXISTS(SELECT*FROMst_projectWHEREst_project.pr_id=project.pr_id);EXISTS关键字运行结果【例5-30】2.使用IN或NOTIN的子查询通过IN（或NOTIN）引入的子查询结果是包含零个值或多个值的列表。子查询返