MySQL数据库教程（第2版）-课件 第5章 数据查询

第5章

数据查询【学习目标】 掌握SELECT语句的基本语法格式。

熟练运用SELECT语句实现单表查询。 熟练运用聚合函数实现统计查询。 掌握多表的连接运算，实现多表连接查询。 掌握子查询和联合查询。5.1数据查询语句数据查询（SELECT）语句具有丰富的功能和灵活的使用方式，使用该语句可以实现对表中数据的选择、投影和连接等运算，返回指定数据表中全部或部分满足条件的数据集合。功能：

从FROM子句指定的数据源中返回满足WHERE子句指定条件的记录集，该记录集中只包含SELECT语句中指定的字段或表达式。基本语法格式SELECT[ALL|DISTINCT]列表达式1[,列表达式2…][FROM表名][WHERE条件表达式][GROUPBY列表达式1[,列表达式2…]][HAVING条件表达式][ORDERBY列表达式1[ASC|DESC][,列表达式2…]][LIMIT[M,]N];说明：（1）SELECT子句：指定查询结果中要显示的列，列表达式可以是字段名称或计算表达式。ALL表示查询结果中显示满足条件的全部记录，是默认值；DISTINCT表示查询结果不包含重复的数据。（2）FROM子句：指定查询的数据源。数据源可以是一张表或多张表，如果是多张表，必须要进行连接。（3）WHERE子句：指定查询的条件，筛选出满足条件的数据。（4）GROUPBY子句：指定对数据进行分组的方式。（5）HAVING子句：指定分组满足的条件，必须与GROUPBY子句配合使用。（6）ORDERBY子句：指定查询结果的排序方式。ASC表示升序排列，是默认值；DESC表示降序排列。（7）LIMIT子句：指定返回查询结果中从第M行开始的N行数据。不指定M时，表示返回前N行数据。SELECT语句书写顺序为

SELECT→DISTINCT→FROM→WHERE→GROUPBY→HAVING→ORDERBY→LIMIT数据库实际执行的顺序为

FROM→WHERE→GROUPBY→HAVING→SELECT→DISTINCT→ORDERBY→LIMIT5.2单表查询5.2.1简单数据查询简单数据查询是从FROM子句指定的表中，筛选SELECT子句指定的列。常见的列表达式是字段名称，查询结果中只显示指定字段的数据，实际上完成的是投影运算。SELECT[ALL|DISTINCT]

列表达式1[,列表达式2…]FROM表名1.查询全部字段的数据

显示指定的字段列是进行投影运算，查询结果中只显示指定的字段或表达式。SELECT子句使用“*”号时，表示查询结果包括表中的全部字段。【例5-1】查询课程表course中全部字段数据。

mysql>SELECT*

FROMcourse;2.查询指定字段的数据在SELECT之后列出需要显示的字段名称，多个字段名称之间用英文逗号进行分隔。

【例5-2】查询学生表student中所有学生的学号stuno、姓名stuname和出生日期birthdate。mysql>SELECTstuno,stuname,birthdateFROMstudent;3.指定字段的别名

查询结果默认的列标题是字段名称本身，也可以使用AS指定显示结果的列标题名称。

【例5-3】查询学生表student中所有学生的学号stuno、姓名stuname和出生日期birthdate，并且分别为这3字段指定中文别名。mysql>SELECTstunoAS学号,stunameAS姓名,birthdateAS出生日期FROMstudent;4.显示经过计算的值（1）使用运算符构成的计算表达式【例5-4】查询课程表course中全部课程的课程编号、课程名称、学分和学时。mysql>SELECTcrsnoAS课程编号,crsnameAS课程名称,creditAS学分,credit*16AS学时FROMcourse;SELECT子句的列表达式不仅可以是字段名称，也可以是用“+”“-”“*”“/”等运算符构成的计算表达式，或者CASE表达式。1学分对应16学时（2）使用CASE表达式①

CASE表达式格式一CASE

表达式

WHEN

值1THEN结果值1

[WHEN值2THEN结果值2]

…

[ELSE结果值n]ENDAS别名先计算出CASE关键字后表达式的值，然后与WHEN…THEN中的值进行匹配如果与某个值的匹配结果为真，则得到对应的结果值如果与各个值都不匹配，则得到ELSE中的结果值n【例5-5】查询教师表teacher，显示教工号、教师姓名、职称和要求承担的学时数（教授32学时，副教授64学时，讲师72学时）。mysql>SELECTtchnoAS教工号,tchnameAS教师姓名,titleAS职称,->CASEtitle

->WHEN'教授'THEN'32学时'

->WHEN'副教授‘THEN'64学时'

->WHEN'讲师'THEN'72学时'

->ENDAS要求承担的学时数->FROMteacher;②CASE表达式格式二CASEWHEN条件1THEN结果值1[WHEN条件2THEN结果值2]…[ELSE结果值n]ENDAS别名

注意：CASE关键字后没有参数。在WHEN…THEN中指定一个条件，如果条件的值为真，则得到对应的结果值；如果各个条件的值都不为真，则得到ELSE中的结果值n。【例5-6】查询课程表course，根据每一门课程的学分判断学时情况（2学分及以下为较少学时，4学分及以上为较多学时，其他学分为中等学时）。mysql>SELECTcrsnameAS课程名称,creditAS学分,->CASE->WHENcredit<=2THEN'较少学时'->WHENcredit>=4THEN'较多学时'

->ELSE'中等学时'

->ENDAS学时情况->FROMcourse;5.消除查询结果中的重复行

不完全相同的记录投影到指定的某些字段后，可能会变成相同的行，使用DISTINCT可以消除查询结果中重复的数据。【例5-7】查询学生表student中所包含的班级classno。mysql>SELECTDISTINCTclassnoFROMstudent;因为一个班级中有多名学生，所以student表中的classno字段可能存在多个相同的值DISTINCT6.对查询结果进行排序

ORDERBY子句用于对查询结果进行排序。

ORDERBY子句后可以是一个或多个字段、表达式或正整数，正整数表示按照结果中该位置上的字段列排序。ASC表示升序排列，是系统默认的排序方式，DESC表示降序排序。【例5-8】查询学生表student中所有学生的学号stuno、姓名stuname、班级classno和入学总分enterscore，按照enterscore的降序排序。mysql>SELECTstuno,stuname,classno,enterscoreFROMstudent

->ORDERBYenterscoreDESC;mysql>SELECTstuno,stuname,classno,enterscoreFROMstudent

->ORDERBY4DESC;（1）单字段排序ORDERBY子句中包含1个字段，是单字段排序。（2）多字段排序ORDERBY子句中包含两个及以上的字段，构成多字段排序。查询结果先按照第1个字段排序，该字段相同时按照第2个字段排序，以此类推。【例5-9】查询学生表student中所有学生的学号stuno、姓名stuname、班级classno和入学总分enterscore，结果先按照classno的升序排序，classno值相同时再按照enterscore的降序排序。mysql>SELECTstuno,stuname,classno,enterscoreFROMstudent

->ORDERBYCONVERT(classnoUSINGGBK),enterscoreDESC;CONVERT函数强制按中文字符集GBK来排序7.限制查询结果中的记录条数

使用LIMIT子句来限制查询结果中的记录条数，通常与ORDERBY子句配合使用。【例5-10】查询学生表student中入学总分最高的5个学生的学号stuno、姓名stuname、班级classno和入学总分enterscore。mysql>SELECTstuno,stuname,classno,enterscoreFROMstudent

->ORDERBYenterscoreDESCLIMIT5;【例5-11】查询学生表student中入学总分排名第4位和第5位的两个学生的学号stuno、姓名stuname、班级classno和入学总分enterscore。如果要求初始记录不是表中的第1条记录，则应采用LIMITM,N的形式。注意，行号是从0开始的，而不是从1开始的。因此如果要从第4条记录开始，则M为3，一共要显示2条记录，则N为2，即使用LIMIT3,2。mysql>SELECTstuno,stuname,classno,enterscoreFROMstudent

->ORDERBYenterscoreDESCLIMIT3,2;第4、5条记录5.2.2条件数据查询实际的数据表中包含大量的数据，用户通常只需要查询满足特定条件的数据，因此可以使用WHERE子句按照指定条件筛选数据，这一过程完成的是选择运算。条件表达式可以包含比较运算、逻辑运算、确定范围运算、字符匹配运算、空值判断运算等。如果条件表达式成立，则结果为TRUE（真），否则结果为FALSE（假）。使用WHERE子句对数据表中的数据逐行进行判断，当条件表达式的结果为真时，该行数据被加入结果集。1.比较运算运算符功能运算符功能=等于<

小于>

大于<=大于或等于>=大于或等于<>、!=不等于【例5-12】查询学生表student中入学总分在580分及以上的学生的学号stuno、姓名stuname、性别gender和入学总分enterscore。mysql>SELECTstuno,stuname,gender,enterscoreFROMstudent

->WHEREenterscore>=580;【例5-13】查询教师表teacher中所有教授的教工号、教师姓名、性别和职称。在MySQL命令提示符后输入如下命令语句。mysql>SELECTtchnoAS教工号,tchnameAS教师姓名,genderAS性别,titleAS职称FROMteacher

->WHEREtitle='教授';mysql>SELECTtchnoAS教工号,tchnameAS教师姓名,genderAS性别,titleAS职称FROMteacher

->WHERE职称='教授';ERROR1054(42S22):Unknowncolumn'职称'in'whereclause'SELECT语句的执行顺序：FROM

WHERE

GROUPBY

HAVING

SELECT

DISTINCT

ORDERBY→LIMIT2.逻辑运算通过逻辑运算符可以将多个表达式组合成更加复杂的查询条件。逻辑运算的结果为TRUE（真）或FALSE（假）。NOT优先级最高，AND优先级次之，OR优先级最低。同时出现多种运算符时，先执行NOT运算，然后执行算术运算、比较运算、AND运算，最后执行OR运算。运算符说明NOT非运算，如果当表达式的值为真，则结果为假，否则结果为真AND与运算，如果连接的两个表达式的值均为真，则结果为真，否则结果为假OR或运算，如果连接的两个表达式的值均为假，则结果为假，否则结果为真【例5-14】查询学生表student中入学总分在580及以上的男学生的学号stuno、姓名stuname、性别gender和入学总分enterscore。mysql>SELECTstuno,stuname,gender,enterscoreFROMstudent

->WHEREgender='男'ANDenterscore>=580;3.确定范围运算指定范围运算符可以表示查询条件在特定的范围中运算符说明BETWEENaANDb如果表达式在a与b之间（包含a和b），则结果为真，否则结果为假IN(列表)如果表达式在列表中，则结果为真，否则结果为假【例5-15】查询学生表student中2006年出生的学生的学号stuno、姓名stuname和出生日期birthdate。在MySQL命令提示符后输入如下命令语句。mysql>SELECTstuno,stuname,birthdateFROMstudent

->WHEREbirthdateBETWEEN'2006-01-01'AND'2006-12-31';birthdate>='2006-01-01'ANDbirthdate<='2006-12-31'【例5-16】查询学生表student中英语2401、风电2401和财务2401这3个班学生的stuno、姓名stuname和班级classno。mysql>SELECTstuno,stuname,classnoFROMstudent

->WHEREclassnoIN('英语2401','风电2401','财务2401');classno='英语2401'ORclassno='风电2401‘ORclassno='财务2401'4.字符匹配运算运算符说明LIKE匹配串如果匹配指定的匹配串，则结果为真，否则结果为假NOTLIKE匹配串如果不匹配指定的匹配串，则结果为真，否则结果为假模式匹配运算中可以使用的通配符：“%”表示0个或多个字符“_”表示一个字符LIKE用于在WHERE子句中进行模糊匹配，通常与通配符一起使用，查找符合特定模式的数据。【例5-17】查询学生表student中姓名只有2个汉字且姓李的学生的学号stuno、姓名stuname、性别gender和入学总分enterscore。mysql>SELECTstuno,stuname,gender,enterscoreFROMstudent

->WHEREstunameLIKE'李_';【例5-18】查询学生表student中2024级学生（学号以2024开头）中3月份出生的学生的学号stuno、姓名stuname和出生日期birthdate。mysql>SELECTstuno,stuname,birthdateFROMstudent

->WHEREstunoLIKE'2024%'ANDbirthdateLIKE'%-03-%';5．空值判断运算空值判断运算符可以判断表达式的值是否为空值空值的含义是没有任何值，不等于空格和0。运算符说明ISNULL如果表达式的值是空值，则结果为真，否则结果为假ISNOTNULL如果表达式的值不是空值，则结果为真，否则结果为假【例5-19】查询学生表student中奖惩情况不为空值的学生的学号stuno、姓名stuname和奖惩情况awards。mysql>SELECTstuno,stuname,awardsFROMstudent

->WHEREawardsISNOTNULL;5.3聚合函数和数据分组5.3.1聚合函数聚合函数可以实现数据的统计与计算，用于计算表中的一组数据并返回计算结果。函数名功能COUNT()统计一组数据的行数AVG()计算一组数据的平均值SUM()计算一组数据的总和MAX()计算一组数据的最大值MIN()计算一组数据的最小值（1）COUNT函数COUNT函数统计表中满足条件的行数或总行数，返回SELECT语句检索到的行的数目，若找不到匹配的行，则返回0。语法格式:COUNT({[ALL

DISTINCT]表达式}

*)说明：（1）ALL|DISTINCT：ALL表示对所有值进行统计，但不统计值为NULL的行，是默认值；DISTINCT表示去除重复值。（2）表达式：可以是常量、字段名称、函数或计算表达式。（3）*：表示统计数据表中的总行数，包括值为NULL的行。【例5-20】统计学生表student中的学生人数。mysql>SELECTCOUNT(*)AS学生人数FROMstudent;【例5-21】统计学生表student中的班级数。学生表student中的班级classno字段中有重复的值，需要使用DISTINCT去除重复值。mysql>SELECTCOUNT(DISTINCTclassno)AS班级数FROMstudent;DISTINCT去除重复值【例5-22】统计教师表teacher中男教师人数。mysql>SELECTCOUNT(*)AS男教师人数FROMteacherWHEREgender='男';SUM函数用于求出一组数据的总和，AVG函数用于求出一组数据的平均值。语法格式:SUM

AVG([ALL

DISTINCT]表达式)说明：①

表达式的数据类型只能是数值类型。

②

计算时会忽略空值。（2）SUM函数和AVG函数【例5-23】计算课程表course中所有课程的总学分和平均学分。mysql>SELECTSUM(credit)AS总学分,AVG(credit)AS平均学分FROMcourse;MAX函数用于求出一组数据的最大值，MIN函数用于求出一组数据的最小值。其语法格式:MAX

MIN([ALL

DISTINCT]表达式)MAX函数和MIN函数参数与COUNT函数类似。（3）MAX函数和MIN函数【例5-24】在学生表student中统计入学总分enterscore的最高分和最低分。mysql>SELECTMAX(enterscore)AS最高分,MIN(enterscore)AS最低分FROMstudent;5.3.2数据分组统计查询中通常进行的是分类统计。分类统计需要先按照指定字段对数据进行分组，然后利用聚合函数统计每个组内的记录来得到结果。下面以一个日常生活中的例子做类比。一个盒子中有若干个红色、黄色和蓝色的小球，要求统计各种颜色小球的数量。一个简单、有效的方法是先取3个空盒子，然后将颜色相同的小球放在同一个盒子中（分组），最后分别对每个盒子中的小球计数（利用聚合函数统计）。GROUPBY子句可以实现数据分组功能。分组计数354SELECT分组统计语句的基本语法格式SELECT分组列1[,分组列2…],聚合函数1[,聚合函数2…]FROM表名GROUPBY分组列1[,分组列2…];在标准的MySQL中，SELECT子句中的所有非聚合函数列必须出现在GROUPBY子句中，作为分组列。分组列通常是字段。如果GROUPBY子句中只有一个字段，则记录将按照该字段的值进行分组，值相等的为一组。1.单字段分组【例5-25】统计学生表student中不同性别的人数。mysql>SELECTgenderAS性别,COUNT(*)AS人数FROMstudent->GROUPBYgender;先使用“GROUPBYgender”子句进行分组，将表中记录按照gender分成2组：“男”组“女”组然后使用聚合函数COUNT(*)统计每一组中记录的条数，结果是：“男”组有4条记录“女”组有8条记录【例5-26】分别统计学生表student中男生和女生入学总分enterscore的最高分和最低分。例5-24统计的是student表中全部学生enterscore的最高分和最低分，如果需要按照性别来分别统计，则需要用GROUPBY子句先按照性别进行分组，然后在各个组内用聚合函数MAX和MIN分别求出最高分和最低分。mysql>SELECTgenderAS性别,MAX(enterscore)AS最高分,MIN(enterscore)AS最低分FROMstudent

->GROUPBYgender;（2）多字段分组如果GROUPBY子句中有多个字段，则记录将依次按照字段的值进行多次分组。首先按照第1个字段分组，然后在每个组内再按照第2个字段进行分组，以此类推。【例5-27】在学生表student中按照院系代码deptno和性别gender统计人数，并按照deptno排序。mysql>SELECTdeptnoAS院系代码,genderAS性别,COUNT(*)AS人数FROMstudent->GROUPBYdeptno,genderORDERBYdeptno;GROUPBY子句先按照deptno将学生分成每个院系一组然后分别在各个院系组中按照gender再分为两组3.HAVING子句HAVING子句必须和GROUPBY子句配合使用，用于指定分组需满足条件。注意HAVING子句与WHERE子句有3点不同之处。（1）WHERE子句在GROUPBY分组之前起作用；HAVING子句在GROUPBY分组之后起作用。（2）WHERE子句作用于表，从表中选择满足条件的记录；HAVING子句作用于GROUPBY分组，从分组中选择满足条件的组。（3）WHERE子句中不能使用聚合函数，HAVING子句可以使用聚合函数。【例5-28】查询选修成绩表score中平均成绩在80分以上的学生的学号和平均分（保留2位小数）。mysql>SELECTstunoAS学号,ROUND(AVG(grade),2)AS平均分FROMscore

->GROUPBYstunoHAVINGAVG(grade)>=80;【例5-30】统计学生表student中各个院系男生的入学总分平均分，要求显示院系代码和平均分（保留2位小数），按照平均分的降序排序。mysql>SELECTdeptnoAS院系代码,ROUND(AVG(enterscore),2)AS平均分FROMstudent->WHEREgender='男'->GROUPBYdeptnoORDERBY2DESC;查询结果中显示的是院系代码，并不能直观表示具体的院系名称，用户需要查看院系表department才能对应出具体的院系名称。如果需要直接显示出院系名称，涉及到department表和student表，在SELECT语句中需要将两张表进行连接运算。5.4多表连接查询前面的查询都是针对单表进行的，如果查询结果的字段来自多张不同的表，则需要先通过连接运算将多张表连接起来进行查询。多表连接查询是最重要的查询之一。第1章已经介绍了连接运算的概念，本任务将使用具体的连接运算语句实现多表连接查询。5.4.1连接运算连接运算主要分为交叉连接、内连接和外连接。1.交叉连接交叉连接的结果集是由第一张表中的每一条记录与第二张表中的每一条记录的笛卡儿积所形成的表，其总记录条数是两张表记录条数的乘积。交叉连接有如下两种连接方法。语法格式:SELECT{*

字段列表}FROM

表名1CROSSJOIN

表名2;或SELECT{*

字段列表}FROM

表名1,表名2;【例5-31】将教师表teacher和课程表course进行交叉连接，查询出教师和课程所有可能的组合。在MySQL命令提示符后输入如下命令语句。mysql>SELECT*FROMteacherCROSSJOINcourse;或输入如下命令语句。mysql>SELECT*FROMteacher,course;因为teacher表中有9条记录，course表中有10条记录，交叉连接的结果集中的记录条数为9

10=90条，显示查询结果为“90rowsinset(0.00sec)”。在现实中一个教师不可能讲授所有的课程，所以其结果集中许多记录没有现实意义，因此很少使用交叉连接。2.内连接内连接是应用最广泛的连接运算，使用INNERJOIN就可以将两张表内连接在一起。方法一：使用INNERJOINSELECT{*

字段列表}FROM表名1[INNER]JOIN表名2ON连接条件[WHERE筛选条件];内连接是系统默认的，可以省略关键字INNER。方法二：使用WHERE定义连接条件SELECT{*

字段列表}FROM表名1,表名2WHERE

连接条件[AND筛选条件];该方法中的WHERE可以是连接条件，也可以是筛选条件连接条件的格式：[表名1.]字段1

比较运算符

[表名2.]字段2说明：①

对于多张表同名的字段，该字段前必须加表名，中间用“.”间隔

格式为：表名.字段名称例如，学生表student和院系表department中都有deptno字段，在使用deptno字段时，必须加上表名，写成“student.deptno”和“department.deptno”。对于非同名的字段，可以直接写字段名称。②

比较运算符：=、<、<=、>、>=、!=、<>等。（1）等值连接【例5-32】查询所有学生的学号stuno、姓名stuname、性别gender、院系代码deptno、院系名称deptname、班级classno和入学总分enterscore。因为涉及到学生表student和院系表department，所以需要对这两张表进行等值连接。mysql>SELECTstuno,stuname,gender,student.deptno,deptname,classno,enterscore->FROMstudentINNERJOINdepartmentONstudent.deptno=department.deptno;先执行INNERJOIN后执行SELECTdeptno是student表和department表同名的同名字段，所以必须加上表名，其他字段均非两张表同名的字段，可以省略表名。【例5-33】按院系名称统计各个院系男生入学总分的平均分（保留2位小数），要求显示院系名称和平均分，并按照平均分的降序排序。mysql>SELECTdeptnameAS院系名称,ROUND(AVG(enterscore),2)AS平均分

->FROMdepartmentJOINstudentONdepartment.deptno=student.deptno->WHEREgender='男'GROUPBYdeptnameORDERBYAVG(enterscore)DESC;也可以使用WHERE子句来定义连接条件mysql>SELECTdeptnameAS院系名称,ROUND(AVG(enterscore),2)AS平均分

->FROMdepartment,studentWHEREdepartment.deptno=student.deptnoANDgender='男'->GROUPBYdeptnameORDERBYAVG(enterscore)DESC;直观表别名在对数据表查询时需要在FROM子句中指定表名来明确数据的来源。如果表名比较长或者需要执行一些特殊查询，为了方便操作，可以为数据表指定一个别名，用这个别名代替数据表的名称，别名只在当前SQL语句中有效。FROM子句指定别名的格式。FROM表名1[[AS]别名1][,表名2[[AS]别名2]…]例如，指定department表和student表的表别名分别为d和s。mysql>SELECTdeptnameAS院系名称,ROUND(AVG(enterscore),2)AS平均分->FROMdepartmentdJOINstudentsONd.deptno=s.deptno->WHEREgender='男'GROUPBYdeptnameORDERBYAVG(enterscore)DESC;如果一张表与自身进行连接，则称为自连接。使用自连接时需要为表指定两个别名，使之成为逻辑上的两张表，且对所有查询字段的引用必须使用表的别名进行限定。score表指定为别名a和b的两张逻辑表，将表a和表b连接。mysql>SELECTa.stuno

->FROMscoreaJOINscorebONa.stuno=b.stuno

->WHEREa.crsno='10101400'ANDb.crsno='10600611';（2）自连接【例5-34】查询选修成绩表score中同时选修了课程编号crsno为10101400和10600611两门课程的学生的学号stuno。3.外连接外连接是指从一张表中选择全部的记录行，从另一张表中只选择与连接字段匹配的记录行。有两种外连接的方式：左外连接和右外连接。（1）左外连接使用关键字LEFTJOIN定义左外连接的语法格式:SELECT{*|字段列表}FROM表名1LEFT[OUTER]JOIN表名2ON连接条件;左外连接的结果集中包含表1的所有记录，当表2中没有匹配的记录时，表2中对应的字段会被设置为空值NULL。【例5-35】查询计算2401班全班学生的学号stuno、姓名stuname、班级classno和成绩grade。mysql>SELECTs.stuno,stuname,classno,grade

->FROMstudentsLEFTJOINscorescONs.stuno=sc.stuno

->WHEREclassno='计算2401';查询结果包含了student表中计算2401班的所有学生，score表中没有刘丽对应的记录，则grade字段值设置为NULL。（2）右外连接使用关键字RIGHTJOIN定义右外连接的语法格式。SELECT{*|字段列表}FROM表名1[[AS]别名1]LEFT[OUTER]JOIN表名2[[AS]别名2]ON连接条件;右外连接的结果集中包含表2的所有记录，当表1中没有匹配的记录时，表1中对应的字段会被设置为空值（NULL）。查询结果包含了course表中的所有记录。【例5-36】查询各门课程的平均分，要求显示所有课程（包括没有学生选修的课程）的课程编号、课程名称和平均分（保留2位小数）。mysql>SELECTc.crsno,crsname,ROUND(AVG(grade),2)->FROMscorescRIGHTJOINcoursecONsc.crsno=c.crsno->GROUPBYc.crsno,crsname;5.4.2等值连接查询【例5-37】查询各门课程的课程编号、课程名称、平均分（保留2位小数）、最高分和最低分。mysql>SELECTc.crsnoAS课程编号,crsnameAS课程名称,ROUND(AVG(grade),2)AS平均分,MAX(grade)AS最高分,MIN(grade)AS最低分

->FROMcoursecJOINscorescONc.crsno=sc.crsno->GROUPBYc.crsno,crsname;【例5-38】查询每个学生的学号、姓名和平均成绩（保留2位小数），查询结果按平均成绩的降序排序。mysql>SELECTs.stunoAS学号,stunameAS姓名,ROUND(AVG(grade),2)AS平均成绩

->FROMstudentsJOINscorescONs.stuno=sc.stuno->GROUPBYs.stuno,stunameORDERBY3DESC;【例5-39】查询王平老师授课的班级、课程编号和课程名称。在MySQL命令提示符后输入如下命令语句。mysql>SELECTclassnoAS班级,c.crsnoAS课程编号,crsnameAS课程名称

->FROMteachingtg,teachert,coursecWHEREtg.tchno=t.tchnoANDtg.crsno=c.crsnoANDtchname='王平';连接条件筛选条件【例5-40】查询学生宋洪博的成绩单，要求显示学号、姓名、课程名称和成绩。mysql>SELECTs.stunoAS学号,stunameAS姓名,crsnameAS课程名称,gradeAS成绩

->FROMstudentsJOINscorescONs.stuno=sc.stunoJOINcoursecONc.crsno=sc.crsno->WHEREstuname='宋洪博';【例5-41】查询成绩grade在95分及以上的学生的学号、姓名、课程名称和成绩，并按成绩降序排序。mysql>SELECTs.stunoAS学号,s.stunameAS姓名,c.crsnameAS课程名称,sc.gradeAS成绩

->FROMstudentsJOINscorescONs.stuno=sc.stunoJOINcoursecONc.crsno=sc.crsno->WHEREsc.grade>=95ORDERBYsc.gradeDESC;每个字段均采用了“别名.字段名称”的形式，可以清晰的表明每个字段来自于哪张表，不足之处在于书写比较繁琐。5.5子查询子查询也称为嵌套查询，是在执行某个查询的过程中使用另一个查询的结果。子查询允许多层嵌套，即一个子查询中可以嵌套其他子查询。需要特别指出的是，子查询的SELECT语句中不能使用ORDERBY子句，ORDERBY子句只能对最终的查询结果排序。子查询可以存在于SELECT子句、FROM子句和WHERE子句中。1．SELECT子句中的子查询子查询可以替代SELECT子句中的列表达式。【例5-42】查询每个学生的入学总分和平均分差（平均分差=入学总分

入学总分平均分），要求显示姓名、入学总分和平均分差（保留2位小数）。mysql>SELECTstunameAS姓名,enterscoreAS入学总分,

ROUND(enterscore-(SELECTAVG(enterscore)FROMstudent),2)AS平均分差

->FROMstudent;【例5-43】查询每位教师授课的班级数量，要求显示教师姓名和班级数量，按照班级数量的降序排序。mysql>SELECTtchnameAS教师姓名,

(SELECTCOUNT(*)FROMteachingtgWHEREtg.tchno=t.tchno)AS班级数量

->FROMteachertORDERBY2DESC;子查询结果为单个值时，条件中可以使用“=”“>”“<”“<=”“>=”“!=”“<>”等比较运算符。【例5-44】查询学生表student中入学总分高于平均值的学生的学号stuno、姓名sname和入学总分enterscore。mysql>SELECTstuno,stuname,enterscoreFROMstudentWHEREenterscore>AVG(enterscore);mysql>SELECTstuno,stuname,enterscoreFROMstudent->WHEREenterscore>=(SELECTAVG(enterscore)FROMstudent);系统执行语句时从内层到外层进行先执行内层的“SELECTAVG(enterscore)FROMstudent”语句然后执行外层的“SELECTstuno,stuname,enterscoreFROMstudentWHEREenterscore>=538.5”语句2．WHERE子句中的子查询子查询的结果可能是单个值，也可能是多个值的集合。（1）

结果为单个值ERROR1111(HY000):Invaliduseofgroupfunction因为WHERE子句中不可以使用聚合函数（2）

结果为多个值子查询结果有多个值时，可以使用ANY、SOME、ALL、IN等运算符。【例5-45】通过学生表student和选修成绩表score两张表，查询没有选修任何课程的学生的学号stuno和姓名stuname。mysql>SELECTstuno,stunameFROMstudent

->WHEREstunoNOTIN(SELECTDISTINCTstunoFROMscore);执行分成两步：先执行内层的SELECT语句，检索出已选修课程的所有stuno；然后执行外层的SELECT语句，检索出不在已选修课程的stuno集合中的记录，并显示这些记录的stuno和stuname。3．FROM子句中的子查询子查询出现在FROM子句中时，将生成临时表。【例5-46】查询授课数量在3门及以上的教师姓名和授课数量。mysql>SELECT教师姓名,授课数量->FROM(SELECTtchnameAS教师姓名,COUNT(*)AS授课数量FROMteachertJOINteachingtg->ONt.tchno=tg.tchnoGROUPBYtchname)AS教师授课统计->WHERE授课数量>=3;执行分成两步：首先执行内层的SELECT语句，生成一张临时表“教师授课统计”，包含教师姓名和授课数量两个字段然后执行外层的SELECT语句，从临时表中查询出满足条件的数据5.6联合查询联合查询是将两个查询结果集合并为一个查询结果集，使用关键字UNION语法格式:SELECT语句1UNION[ALL|DISTINCT]SELECT语句2;说明：（1）UNION运算符可以将前后两个SELECT语句的查询结果进行合并，生成一个数据集。（2）联合查询时默认选项为DISTINCT，表示查询结果集中消除了重复行，所有的记录都是唯一的；若选项为ALL，表示查询结果集中包含查询出的所有记录行。（3）联合查询中的两个SELECT语句必须具有相同的字段列数目、且各字段列具有相同的数据类型。【例5-47】查询所有女老师和女学生信息。mysql>SELECTstunoAS编号,stunameAS名称,'学生'AS类型FROMstudentWHEREgender='女'->UNION->SELECTtchnoAS编号,tchnameAS名称,'教师'AS类型FROMteacherWHEREgender='女'->ORDERBY类型,编号;【课堂案例】学生成绩管理数据库的数据查询单表查询和多表连接查询（1）查询学生表student中学生的学号、姓名、入学总分和入学奖励情况（如果enterscore大于等于580，则显示“优胜奖”；enterscore大于等于550，则显示“鼓励奖”，其他情况则显示NULL）。SELECTstunoAS学号,stunameAS姓名,enterscoreAS入学总分,CASEWHENenterscore>=580THEN'优胜奖'WHENenterscore>=550THEN'鼓励奖'ELSENULLENDAS入学奖励情况FROMstudent;（2）查询教师的教工号、教师姓名、职称和院系名称。SELECTtchnoAS教工号,tchnameAS教师姓名,titleAS职称,deptnameAS院系名称FROMteachertJOINdepartmentdONt.deptno=d.deptno;（3）查询选修了程序设计基础课程的学生的学号、姓名和程序设计基础成绩。SELECTs.stunoAS学号,stunameAS姓名,gradeAS程序设计基础成绩FROMstudentsJOINscorescONs.stuno=sc.stunoJOINcoursecONc.crsno=sc.crsnoWHEREcrsname='程序设计基础';（4）查询选修了程序设计基础课程的学生的学号、姓名和程序设计基础成绩评价（优秀、良好、中等、及格、不及格）。SELECTs.stunoAS学号,stunameAS姓名,CASEWHENgrade>=90THEN'优秀'WHENgrade>=80THEN'良好'WHENgrade>=70THEN'中等'WHENgrade>=60THEN'及格'ELSE'不及格'ENDAS程序设计基础成绩评价FROMstudentsJOINscorescONs.stuno=sc.stunoJOINcoursecONc.crsno=sc.crsnoWHEREcrsname='程序设计基础';（5）查询尚未承担任何课程的教师的教工号和教师姓名。SELECTt.tchnoAS教工号,tchnameAS教师姓名FROMteache