MySQL数据库教程课件

第5章数据查询【学习目标】掌握SELECT语句的语法。熟练运用SELECT实现单表的查询。熟练运用聚合函数实现统计查询。熟练掌握多表的连接操作，实现多表的查询。掌握子查询和联合查询。5.1数据查询语句数据查询使用的SQL语句是SELECT语句，SELECT语句是从数据表检索记录的命令，该命令可以实现对表中记录的选择、投影和连接等运算，返回指定的数据表中的全部或部分满足条件的记录集合。功能：

从FROM子句指定的数据源中返回满足WHERE子句指定条件的记录集，该记录集中只包含SELECT语句中指定的字段或表达式。基本语法格式如下。SELECT[ALL|DISTINCT]字段名称1或表达式1[,字段名称2或表达式2…][FROM表名1[,表名2…]][WHERE条件][GROUPBY字段名称1或表达式1或字段编号1[,字段名称2或表达式2或字段编号2…][HAVING条件][ORDERBY字段名称1或表达式1或字段编号1[ASC|DESC][,…]][LIMIT{[偏移量,]行数

行数OFFSET偏移量}];说明：（1）SELECT子句：指定查询结果中要显示的字段或表达式。查询结果是满足条件的全部记录，默认是ALL；DISTINCT的含义是查询结果不包含重复行的记录。（2）FROM子句：指定查询的数据源。数据源可以是一张表或多张表，也可以是视图。（3）WHERE子句：指定查询的条件，筛选出满足条件的记录行。（4）GROUPBY子句：指定对记录进行分组的方式。（5）HAVING子句：指定分组满足的条件，必须与GROUPBY一起使用。（6）ORDERBY子句：指定查询结果的排序方式。ASC表示升序，是默认值；DESC表示降序。（7）LIMIT子句：指定查询结果集所包含的记录行数。SELECT语句中各个子句的执行顺序：FROM

WHERE

GROUPBY

HAVING

SELECT

DISTINCT

ORDERBY。5.2单表查询5.2.1简单数据查询简单数据查询是在FORM子句指定的表中显示SELECT子句中指定的字段或表达式语法格式：SELECT[ALL|DISTINCT]字段名称1或表达式1[,字段名称2或表达式2…]FROM表名;1.显示指定的字段列

显示指定的字段列是进行投影运算，查询结果中只显示指定的字段或表达式。（1）显示全部字段列SELECT子句使用“*”号时，表示查询结果包括表中的所有字段列。+----------+------------------+-------+|cno|cname|hours|+----------+------------------+-------+|10101400|学术英语|64||10101410|通用英语|48||10300710|现代控制理论|40||10400350|模拟电子技术基础|56||10500131|证券投资学|32||10600200|高级语言程序设计|56||10600450|无线网络安全|32||10600611|数据库应用|56||10700053|大学物理|56||10700140|高等数学|64||10700462|线性代数|48|+----------+------------------+-------+【例5-1】查询课程表course中的全部字段。mysql>SELECT*FROMcourse;（2）显示指定的字段列在SELECT之后列出需要显示的字段名称，多个字段名称之间用英文逗号进行分隔。

+--------------+--------+------------+|sno|sname|birthdate|+--------------+--------+------------+|120211010103|宋洪博|2003-05-15||120211010105|刘向志|2002-10-08||120211010230|李媛媛|2003-09-02||120211030110|王琦|2003-01-23||120211030409|张虎|2003-07-18||120211040101|王晓红|2002-09-02||120211040108|李明|2002-12-27||120211041102|李华|2003-01-01||120211041129|侯明斌|2002-12-03||120211050101|张函|2003-03-07||120211050102|唐明卿|2002-10-15||120211060104|王刚|2004-01-12||120211060206|赵壮|2003-03-13||120211070101|李淑子|2003-06-14||120211070106|刘丽|2002-11-17|+--------------+--------+------------+【例5-2】查询学生表student中所有学生的学号sno、姓名sname和出生日期birthdate。mysql>SELECTsno,sname,birthdate->FROMstudent;2.显示字段列的别名

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

例如，表中的字段名称都是英文的，为了方便用户直观理解字段的含义，可以使用AS指定中文别名。

【例5-3】查询学生表student中所有学生的学号sno、姓名sname和出生日期birthdate，分别为这三列指定中文别名。mysql>SELECTsnoAS学号,snameAS姓名,birthdateAS出生日期->FROMstudent;+--------------+--------+------------+|学号|姓名|出生日期|+--------------+--------+------------+|120211010103|宋洪博|2003-05-15||120211010105|刘向志|2002-10-08||120211010230|李媛媛|2003-09-02||120211030110|王琦|2003-01-23||120211030409|张虎|2003-07-18||120211040101|王晓红|2002-09-02||120211040108|李明|2002-12-27||120211041102|李华|2003-01-01||120211041129|侯明斌|2002-12-03||120211050101|张函|2003-03-07||120211050102|唐明卿|2002-10-15||120211060104|王刚|2004-01-12||120211060206|赵壮|2003-03-13||120211070101|李淑子|2003-06-14||120211070106|刘丽|2002-11-17|+--------------+--------+------------+3.显示计算的列值

查询结果的列可以是表中的字段，也可以是加（+）、减（

）、乘（*）、除（/）等算术运算符构成的计算表达式对字段进行计算的结果。

【例5-4】查询课程表course中全部课程的学分。在课程表course中没有学分字段，可以根据学时数计算出学分，每16学时为1学分，一门32学时的课程的学分为2学分。mysql>SELECTcnoAS课程编号,cnameAS课程名称,hoursAS学时,hours/16AS学分->FROMcourse;+----------+------------------+------+--------+|课程编号|课程名称|学时|学分|+----------+------------------+------+--------+|10101400|学术英语|64|4.0000||10101410|通用英语|48|3.0000||10300710|现代控制理论|40|2.5000||10400350|模拟电子技术基础|56|3.5000||10500131|证券投资学|32|2.0000||10600200|高级语言程序设计|56|3.5000||10600450|无线网络安全|32|2.0000||10600611|数据库应用|56|3.5000||10700053|大学物理|56|3.5000||10700140|高等数学|64|4.0000||10700462|线性代数|48|3.0000|+----------+------------------+------+--------+4.消除查询结果集中的重复行

查询结果是满足条件的全部记录，默认就是ALL。使用DISTINCT参数可以消除查询结果中重复的行，保证行的唯一性。【例5-5】查询学生表student中所包含的班级classno。

mysql>SELECTDISTINCTclassnoFROMstudent;

一个班级中有多名学生，所以学生表student中的班级名称classno字段可能存在多个相同的值。使用DISTINCT来消除查询结果中重复的班级classno记录。

+----------+

|classno|

+----------+

|英语2101|

|英语2102|

|机械2101|

|机械2104|

|电气2101|

|电气2111|

|财务2101|

|计算2101|

|计算2102|

|物理2101|

+----------+

5.限制查询结果集中的行数

使用LIMIT来控制查询结果返回的行数。【例5-6】查询学生表student中前3个学生的学号sno、姓名sname和出生日期birthdate。mysql>SELECTsno,sname,birthdate->FROMstudent->LIMIT3;+--------------+--------+------------+|sno|sname|birthdate|+--------------+--------+------------+|120211010103|宋洪博|2003-05-15||120211010105|刘向志|2002-10-08||120211010230|李媛媛|2003-09-02|+--------------+--------+------------+【例5-7】查询教师表teacher中从第4条记录开始的2个教师的教师工号tno、姓名tname和职称title。如果初始记录行不是从头开始，则要使用两个参数：偏移量、行数。需要注意的是初始记录行的偏移量为0，不是1，因此第4条记录的偏移量为3。mysql>SELECTtno,tname,title->FROMteacher->LIMIT3,2;该语句的含义是显示从第4条记录开始的2条记录，即第4、5条记录。+----------+--------+--------+|tno|tname|title|+----------+--------+--------+|10423769|林达|教授||10501561|赵晓丽|副教授|+----------+--------+--------+6.查询结果的排序

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

ORDERBY子句后可以是一个或多个字段、表达式或正整数，正整数表示按照结果中该位置上的字段列排序。

ASC表示升序排列，是系统默认的排序方式；DESC表示降序排序。

【例5-8】查询课程表course的课程编号cno、课程名称cname和学时hours，结果按照学时hours的升序排序。mysql>SELECTcno,cname,hours->FROMcourse->ORDERBYhours;或mysql>SELECTcno,cname,hours->FROMcourse->ORDERBY3;+----------+------------------+-------+|cno|cname|hours|+----------+------------------+-------+|10500131|证券投资学|32||10600450|无线网络安全|32||10300710|现代控制理论|40||10101410|通用英语|48||10700462|线性代数|48||10400350|模拟电子技术基础|56||10600200|高级语言程序设计|56||10600611|数据库应用|56||10700053|大学物理|56||10101400|学术英语|64||10700140|高等数学|64|+----------+------------------+-------+【例5-9】查询学生表student的学号sno、姓名sname、班级classno和入学总分enterscore，结果先按照班级classno的升序排序，班级classno相同时再按照入学总分enterscore的降序排序。mysql>SELECTsno,sname,classno,enterscore->FROMstudent->ORDERBYclassno,enterscoreDESC;+--------------+--------+----------+------------+|sno|sname|classno|enterscore|+--------------+--------+----------+------------+|120211030110|王琦|机械2101|600||120211030409|张虎|机械2104|650||120211070106|刘丽|物理2101|620||120211070101|李淑子|物理2101|589||120211040108|李明|电气2101|650||120211040101|王晓红|电气2101|630||120211041102|李华|电气2111|648||120211041129|侯明斌|电气2111|617||120211010103|宋洪博|英语2101|698||120211010105|刘向志|英语2101|625||120211010230|李媛媛|英语2102|596||120211060104|王刚|计算2101|678||120211060206|赵壮|计算2102|605||120211050101|张函|财务2101|663||120211050102|唐明卿|财务2101|548|+--------------+--------+----------+------------+查询结果中班级并未按照汉语拼音升序排序，原因是MySQL默认的字符集不是中文字符集，可以使用CONVERT函数强制按中文字符集GBK来排序。mysql>SELECTsno,sname,classno,enterscore->FROMstudent->ORDERBYCONVERT(classnoUSINGGBK),enterscoreDESC;+--------------+--------+----------+------------+|sno|sname|classno|enterscore|+--------------+--------+----------+------------+|120211050101|张函|财务2101|663||120211050102|唐明卿|财务2101|548||120211040108|李明|电气2101|650||120211040101|王晓红|电气2101|630||120211041102|李华|电气2111|648||120211041129|侯明斌|电气2111|617||120211030110|王琦|机械2101|600||120211030409|张虎|机械2104|650||120211060104|王刚|计算2101|678||120211060206|赵壮|计算2102|605||120211070106|刘丽|物理2101|620||120211070101|李淑子|物理2101|589||120211010103|宋洪博|英语2101|698||120211010105|刘向志|英语2101|625||120211010230|李媛媛|英语2102|596|+--------------+--------+----------+------------+5.2.2条件数据查询在实际的数据表中包含了大量的记录，通常只需要查询满足特定条件的记录，可以使用WHRER子句对记录进行选择运算，按照指定条件筛选出满足条件的记录并显示出来。条件表达式中可以包含比较运算、逻辑运算、指定范围、模式匹配、空值判断等。如果条件表达式成立，则返回TRUE，否则返回FALSE。WHERE子句对表中记录行逐条进行判断，当条件表达式的结果为TRUE时，该行记录被包含到结果集中。1.比较运算运算符功能运算符功能=等于<

小于>

大于<=大于或等于>=大于或等于<>、!=不等于【例5-10】查询学生表student中入学总分enterscore在600分及以上的学号sno、姓名sname、性别sex和入学总分enterscore。mysql>SELECTsno,sname,sex,enterscoreFROMstudent->WHEREenterscore>=600;+--------------+--------+------+------------+|sno|sname|sex|enterscore|+--------------+--------+------+------------+|120211010103|宋洪博|男|698||120211010105|刘向志|男|625||120211030110|王琦|男|600||120211030409|张虎|男|650||120211040101|王晓红|女|630||120211040108|李明|男|650||120211041102|李华|女|648||120211041129|侯明斌|男|617||120211050101|张函|女|663||120211060104|王刚|男|678||120211060206|赵壮|男|605||120211070106|刘丽|女|620|+--------------+--------+------+------------+【例5-11】查询教师表teacher中所有“教授”的教师工号tno、姓名tname、性别sex和职称title。mysql>SELECTtnoAS教师工号,tnameAS姓名,sexAS性别,titleAS职称

FROMteacher->WHEREtitle="教授";+----------+--------+------+-------+|tno|tname|sex|title|+----------+--------+------+-------+|10112583|周家罗|男|教授||10309242|宋江科|男|教授||10423769|林达|女|教授||10610910|王平|男|教授||10710050|朱军|男|教授|+----------+--------+------+-------+2.逻辑运算通过逻辑运算符可以将多个表达式组合成更加复杂的查询条件。逻辑运算的结果为TRUE（1）或FALSE（0）。NOT优先级最高，AND优先级次之，OR优先级最低。同时出现多种运算符时，先执行NOT运算，然后执行算术运算、比较运算、AND运算，最后执行OR运算。运算符功能NOT非运算，当表达式的值为真时，结果为假；否则结果为真AND与运算，当连接的两个表达式的值均为真时，结果为真；否则结果为假OR或运算，当连接的两个表达式的值均为假时，结果为假；否则结果为真【例5-12】查询学生表student中入学总分enterscore在600及以上的“男”学生的学号sno、姓名sname、性别sex和入学总分enterscore。mysql>SELECTsno,sname,sex,enterscoreFROMstudent->WHEREenterscore>=600ANDsex="男";+--------------+--------+------+------------+|sno|sname|sex|enterscore|+--------------+--------+------+------------+|120211010103|宋洪博|男|698||120211010105|刘向志|男|625||120211030110|王琦|男|600||120211030409|张虎|男|650||120211040108|李明|男|650||120211041129|侯明斌|男|617||120211060104|王刚|男|678||120211060206|赵壮|男|605|+--------------+--------+------+------------+3.指定范围指定范围运算符可以表示查询条件在特定的范围中运算符功能BETWEENaANDb如果在a与b之间的范围内，结果为真，否则结果为假。要求a与b类型相同，且包含a和b这两个临界值NOTBETWEENaANDb如果不在a与b之间的范围内，结果为真，否则结果为假。要求a与b类型相同，且包含a和b这两个临界值IN(列表)如果在列表中，结果为真，否则结果为假NOTIN(列表)如果不在列表中，结果为真，否则结果为假【例5-13】查询学生表student中出生日期在2003-1-1至2004-12-31之间的学生的学号sno、姓名sname和出生日期birthdatemysql>SELECTsno,sname,birthdate->FROMstudent->WHEREbirthdateBETWEEN"2003-1-1"AND"2004-12-31";+--------------+--------+------------+|sno|sname|birthdate|+--------------+--------+------------+|120211010103|宋洪博|2003-05-15||120211010230|李媛媛|2003-09-02||120211030110|王琦|2003-01-23||120211030409|张虎|2003-07-18||120211041102|李华|2003-01-01||120211050101|张函|2003-03-07||120211060104|王刚|2004-01-12||120211060206|赵壮|2003-03-13||120211070101|李淑子|2003-06-14|+--------------+--------+------------+【例5-14】查询学生表student中“英语2101”“机械2101”和“财务2101”这3个班学生的学号sno、姓名sname和班级classno。mysql>SELECTsno,sname,classnoFROMstudent->WHEREclassnoIN("英语2101","机械2101","财务2101");+--------------+--------+----------+|sno|sname|classno| +--------------+--------+----------+|120211010103|宋洪博|英语2101||120211010105|刘向志|英语2101||120211030110|王琦|机械2101||120211050101|张函|财务2101||120211050102|唐明卿|财务2101|+--------------+--------+----------+4.模式匹配运算符功能LIKE如果符合指定的模式，结果为真，否则结果为假NOTLIKE如果不符合指定的模式，结果为真，否则结果为假模式匹配运算中可以使用的通配符有“_”和“%”。“%”表示0个或多个字符；“_”表示一个字符。【例5-15】查询学生表student中姓名sname只有2个汉字且姓“李”的学生的学号sno、姓名sname、性别sex和入学总分enterscore。mysql>SELECTsno,sname,sex,enterscoreFROMstudent->WHEREsnameLIKE"李_";+--------------+-------+------+------------+|sno|sname|sex|enterscore|+--------------+-------+------+------------+|120211040108|李明|男|650||120211041102|李华|女|648|+--------------+-------+------+------------+【例5-16】查询教师表teacher中姓名的最后一个字是“丽”的“副教授”的教师工号tno、姓名tname、性别sex和职称title。mysql>SELECTtno,tname,sex,titleFROMteacher->WHEREtnameLIKE"%丽"ANDtitle="副教授";+----------+--------+------+--------+|tno|tname|sex|title|+----------+--------+------+--------+|10100391|杨丽|女|副教授||10501561|赵晓丽|女|副教授|+----------+--------+------+--------+5．空值判断空值判断运算符可以判断表达式的值是否为空值空值的含义是没有任何值，不等于空格和0。运算符功能ISNULL如果是空值，结果为真，否则结果为假ISNOTNULL如果不是空值，结果为真，否则结果为假【例5-17】查询学生表student中班级classno为空值的学生的学号sno和姓名sname。mysql>SELECTsno,snameFROMstudent->WHEREclassnoISNULL;Emptyset(0.00sec)查询结果为空集，表示所有的学生均有隶属的班级，不存在没有班级的学生。5.3聚合函数和数据分组查询5.3.1聚合函数聚合函数可以实现数据的统计与计算，用于计算表中的一组数据并返回单个计算结果。函数名功能COUNT()求一组数据的个数AVG()求一组数据的平均值SUM()求一组数据的总和MAX()求一组数据的最大值MIN()求一组数据的最小值（1）COUNT函数COUNT函数统计表中满足条件的行数或总行数，返回SELECT语句检索到的行的数目，若找不到匹配的行，则返回0。语法格式:COUNT({[ALL

DISTINCT]表达式}

*)说明：（1）表达式：可以是常量、字段名称、函数或表达式。（2）ALL

DISTINCT：ALL表示对所有值进行运算，是默认值；DISTINCT表示去除重复值。（3）*：表示返回检索到的行的总数目。【例5-18】统计学生表student中的学生人数。mysql>SELECTCOUNT(*)AS学生人数FROMstudent;+----------+|学生人数|+----------+|15|+----------+【例5-19】统计学生表student中的班级数。学生表student中的班级classno字段中有重复的值，需要使用DISTINCT去除重复值。mysql>SELECTCOUNT(DISTINCTclassno)AS班级数FROMstudent;+--------+|班级数|+--------+|10|+--------+【例5-20】统计教师表teacher中男教师的人数。mysql>SELECTCOUNT(tno)AS男教师人数FROMteacherWHEREsex="男";+------------+|男教师人数|+------------+|6|+------------+（2）SUM函数和AVG函数SUM函数用于求出一组数据的总和，AVG函数用于求出一组数据的平均值。语法格式:SUM

AVG([ALL

DISTINCT]表达式)说明：①

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

②

计算时会忽略空值。【例5-21】查询课程表course中总学时数和平均学时数。mysql>SELECTSUM(hours)AS总学时数,AVG(hours)AS平均学时数->FROMcourse;+----------+------------+|总学时数|平均学时数|+----------+------------+|552|50.1818|+----------+------------+（3）MAX函数和MIN函数MAX函数用于求出一组数据的最大值，MIN函数用于求出一组数据的最小值。其语法格式:MAX

MIN([ALL

DISTINCT]表达式)MAX函数和MIN函数参数与COUNT函数类似。【例5-22】在学生表student中统计入学总分enterscore的最高分和最低分。mysql>SELECTMAX(enterscore)AS最高分,MIN(enterscore)AS最低分->FROMstudent;+--------+--------+|最高分|最低分|+--------+--------+|698|548|+--------+--------+5.3.2数据分组统计查询中通常进行的是分类统计，GROUPBY子句可以实现数据分组功能，首先按照指定字段进行分组，然后对每个组内的记录利用聚合函数统计出结果。举一个简单的例子来说明，一个盒中有红色、黄色和蓝色的小球，要求统计出各色小球的数量。可以先取三个空盒子，然后将颜色相同小球放在同一个盒子中（分组），最后分别对每个盒子中的小球计数（聚合函数）就可以得到各色小球的数量。分组计数3541.单字段分组如果GROUPBY子句中只有一个字段，则记录将按照该字段的值进行分组。【例5-23】统计学生表student中不同政治面貌party的人数。分析：首先需要使用“GROUPBYparty”子句进行分组，将表中数据按照party分成三组：“党员”组共4条记录；“团员”组共9条记录；“群众”组共2条记录。然后使用聚合函数COUNT(sno)来统计每个组中的记录个数。mysql>SELECTpartyAS政治面貌,COUNT(sno)AS人数FROMstudent->GROUPBYparty;+----------+------+|政治面貌|人数|+----------+------+|党员|4||团员|9||群众|2|+----------+------+GROUPBYparty【例5-24】统计教师表teacher中不同职称title的人数。mysql>SELECTtitleAS职称,COUNT(tno)AS职称人数FROMteacher->GROUPBYtitle;+--------+----------+|职称|职称人数|+--------+----------+|教授|5||副教授|2||讲师|3|+--------+----------+【例5-25】分别统计学生表student中男生和女生入学总分enterscore的最高分、最低分和平均分（保留2位小数）。mysql>SELECTsex,MAX(enterscore)AS最高分,MIN(enterscore)AS最低分,ROUND(AVG(enterscore),2)AS平均分->FROMstudent->GROUPBYsex;ROUND函数可以将结果进行四舍五入，其中的参数2表示保留2位小数。+------+--------+--------+--------+|sex|最高分|最低分|平均分|+------+--------+--------+--------+|男|698|600|640.38||女|663|548|613.43|+------+--------+--------+--------+（2）多字段分组如果GROUPBY子句中有多个字段，则记录将依次按照字段的值进行多次分组。首先按照第一个字段分组，然后在每个组内再按照第二个字段进行分组，以此类推。【例5-26】在学生表student中按照性别sex分类统计各种政治面貌party的学生人数。mysql>SELECTsexAS性别,partyAS政治面貌,COUNT(sno)AS人数FROMstudent->GROUPBYsex,partyORDERBYsex;GROUPBY子句先按照性别sex字段进行分组，具有相同性别的记录被分配在同一个组中；然后分别在女生组和男生组内按照政治面貌party再次分组。SELECT前两个字段与GROUPBY指定的两个分组字段是一致的，第三个使用聚合函数COUNT()。+------+----------+------+|性别|政治面貌|人数|+------+----------+------+|女|党员|1||女|团员|5||女|群众|1||男|党员|3||男|团员|4||男|群众|1|+------+----------+------+3.HAVING子句HAVING子句必须和GROUPBY子句配合使用，用于指定分组需满足条件。注意HAVING子句与WHERE子句有3点不同之处。（1）WHERE子句在GROUPBY分组之前起作用；HAVING子句在GROUPBY分组之后起作用。（2）WHERE子句作用于表，从表中选择满足条件的记录；HAVING子句作用于GROUPBY分组，从分组中选择满足条件的组。（3）WHERE子句中不能使用聚合函数，HAVING子句可以使用聚合函数。【例5-27】查询选修成绩表score中平均成绩在75分以上的学生的学号sno和平均分AVG(grade)。mysql>SELECTsnoAS学号,AVG(grade)AS平均分FROMscore->GROUPBYsnoHAVINGAVG(grade)>=75;+--------------+---------+|学号|平均分|+--------------+---------+|120211010103|84.0000||120211010105|80.7500||120211030110|88.0000||120211041102|81.7500||120211041129|78.5000||120211050101|80.5000||120211050102|77.0000||120211070101|75.5000|+--------------+---------+5.4多表连接查询如果查询结果的字段来自于多张不同的表，则需要通过连接运算将多张表进行连接。在第1章的关系运算中已经介绍了连接运算的概念5.4.1连接运算连接运算主要分为交叉连接、内连接和外连接。1.交叉连接交叉连接又称为笛卡儿连接，是指两张表之间做笛卡儿积运算，结果集是由第1张表中的每一条记录与第2张表中的每一条记录的笛卡儿积所形成的表，其总的记录行数是两张表记录数的乘积。语法格式:SELECT{*

字段列表}FROM表1CROSSJOIN表2;或SELECT{*

字段列表}FROM表1,表2;【例5-29】将教师表teacher和课程表course进行交叉连接，查询出教师和课程所有可能的组合。mysql>SELECT*->FROMteacherCROSSJOINcourse;或mysql>SELECT*->FROMteacher,course;教师表teacher中有10条记录，课程表course中有11条记录，交叉连接的结果集中的记录行数为10

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

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

字段列表}FROM表1,表2WHERE连接条件[AND筛选条件];该方法中的WHERE可以是连接条件，也可以是筛选条件连接条件的格式：[表名1.]字段1比较运算符[表名2.]字段2说明：（1）对于多张表中共有的字段，该字段前必须加表名，中间用“.”间隔格式为：表名.字段名称例如，学生表student和院系表department中都有deptno字段，在使用deptno字段时，必须加上表名，写成“student.deptno”和“department.deptno”。对于非共有的字段，可以直接写字段名称。（2）比较运算符：=、<、<=、>、>=、!=、<>等。（3）内连接类型。①

等值连接：表之间通过等号运算符“=”进行连接，结果集中只包含两张表中连接字段值相同的记录行。②

非等值连接：两张表之间通过除了等号运算符“=”以外的其他比较运算符进行连接。【例5-30】显示所有学生的学号sno、姓名sname、性别sex、院系代码deptno、院系名称deptname、班级classno和入学总分enterscore。分析：涉及到student表和department表，所以这两张表需要进行等值连接。mysql>SELECTsno,sname,sex,student.deptno,deptname,classno,enterscore->FROMstudentINNERJOINdepartmentOnstudent.deptno=department.deptno;院系代码deptno是student和department表共有的字段，所以必须加上表名;其他字段均不同名,可以省略表名。按照语句的执行顺序，先执行INNERJOIN内连接操作，将student表和department表按deptno字段进行等值连接。+--------------+--------+------+--------+------------------------+----------+------------+|sno|sname|sex|deptno|deptname|classno|enterscore|+--------------+--------+------+--------+------------------------+----------+------------+|120211010103|宋洪博|男|101|外国语学院|英语2101|698||120211010105|刘向志|男|101|外国语学院|英语2101|625||120211010230|李媛媛|女|101|外国语学院|英语2102|596||120211030110|王琦|男|103|能源动力与机械工程学院|机械2101|600||120211030409|张虎|男|103|能源动力与机械工程学院|机械2104|650||120211040101|王晓红|女|104|电气与电子工程学院|电气2101|630||120211040108|李明|男|104|电气与电子工程学院|电气2101|650||120211041102|李华|女|104|电气与电子工程学院|电气2111|648||120211041129|侯明斌|男|104|电气与电子工程学院|电气2111|617||120211050101|张函|女|105|经济与管理学院|财务2101|663||120211050102|唐明卿|女|105|经济与管理学院|财务2101|548||120211060104|王刚|男|106|控制与计算机工程学院|计算2101|678||120211060206|赵壮|男|106|控制与计算机工程学院|计算2102|605||120211070101|李淑子|女|107|数理学院|物理2101|589||120211070106|刘丽|女|107|数理学院|物理2101|620|+--------------+--------+------+--------+------------------------+----------+------------+【例5-31】按院系名称统计各个学院男同学入学平均分，并按照平均分的降序显示。mysql>SELECTdeptnameAS院系名称,AVG(enterscore)AS平均分->FROMdepartmentJOINstudentOndepartment.deptno=student.deptno->WHEREsex="男"->GROUPBYdeptname->ORDERBYAVG(enterscore)DESC;+------------------------+----------+|院系名称|平均分|+------------------------+----------+|外国语学院|661.5000||控制与计算机工程学院|641.5000||电气与电子工程学院|633.5000||能源动力与机械工程学院|625.0000|+------------------------+----------+如果要连接的表的条件字段名称相同，ON条件可以换成USING子句。USING(字段名称)，其中字段名称必须是两个表中都有的相同字段。mysql>SELECTdeptnameAS院系名称,AVG(enterscore)AS平均分->FROMdepartmentJOINstudentUSING(deptno)->WHEREsex="男"->GROUPBydeptname->ORDERBYAVG(enterscore)DESC;也可以使用WHERE子句来定义连接条件。mysql>SELECTdeptnameAS院系名称,AVG(enterscore)AS平均分->FROMdepartment,student->WHEREdepartment.deptno=student.deptnoANDsex="男"->GROUPBYdeptname->ORDERBYAVG(enterscore)DESC;自连接一张表与自身进行连接。使用自连接时需要为表指定两个别名，使之成为逻辑上的两张表，且对所有查询字段的引用必须使用表的别名进行限定。【例5-32】查询选修成绩表score中同时选修了课程编号cno为“10101400”和“10600611”两门课程的学号sno。为选修成绩表score指定别名a和b，将表a和表b进行连接。mysql>SELECTa.sno->FROMscoreaJOINscorebONa.sno=b.sno->WHEREo="10101400"ANDo="10600611";+--------------+|sno|+--------------+|120211010103||120211010105||120211010230||120211050101|+--------------+【例5-33】查询选修成绩表score中课程编号cno为“10600611”的课程成绩高于学号sno为“120211030110”的学生。使用自连接时为选修成绩表score指定了两个别名a和bmysql>SELECTa.sno,o,a.grade->FROMscoreaJOINscorebONa.grade>b.grade->WHEREo="10600611"ANDo="10600611"ANDb.sno="120211030110"->ORDERBYa.gradeDESC;或mysql>SELECTa.sno,o,a.grade->FROMscorea,scoreb->WHEREa.grade>b.gradeANDo="10600611"ANDo="10600611"ANDb.sno="120211030110"->ORDERBYa.gradeDESC;+--------------+----------+-------+|sno|cno|grade|+--------------+----------+-------+|120211050101|10600611|95||120211010105|10600611|90||120211041102|10600611|90|+--------------+----------+-------+3.外连接外连接是指从一张表中选择全部的记录行，从另一张表中只选择与连接字段匹配的记录行。有两种外连接的方式：左外连接和右外连接。（1）左外连接使用关键字LEFTJOIN定义左外连接的语法格式:SELECT{*

字段列表}FROM左表LEFT[OUTER]JOIN右表ON连接条件;左外连接的结果集中包含左表所有的记录行，当右表中没有匹配的记录时，右表对应字段会被设置为空值NULL。如果需要显示全部院系名称deptname的信息，包括那些没有学生的院系，则需要使用左外连接（LEFTJOIN）的形式，表示包含左表（department）中的全部记录，以及右表（student）中具有相同院系代码deptno的记录。mysql>SELECTsno,sname,deptname,classno->FROMdepartmentLEFTJOINstudentONdepartment.deptno=student.deptno;+--------------+--------+------------------------+----------+|sno|sname|deptname|classno|+--------------+--------+------------------------+----------+|120211010230|李媛媛|外国语学院|英语2102||120211010105|刘向志|外国语学院|英语2101||120211010103|宋洪博|外国语学院|英语2101||NULL|NULL|可再生能源学院|NULL||120211030409|张虎|能源动力与机械工程学院|机械2104||120211030110|王琦|能源动力与机械工程学院|机械2101||120211041129|侯明斌|电气与电子工程学院|电气2111||120211041102|李华|电气与电子工程学院|电气2111||120211040108|李明|电气与电子工程学院|电气2101||120211040101|王晓红|电气与电子工程学院|电气2101||120211050102|唐明卿|经济与管理学院|财务2101||120211050101|张函|经济与管理学院|财务2101||120211060206|赵壮|控制与计算机工程学院|计算2102||120211060104|王刚|控制与计算机工程学院|计算2101||120211070106|刘丽|数理学院|物理2101||120211070101|李淑子|数理学院|物理2101|+--------------+--------+------------------------+----------+（2）右外连接使用关键字RIGHTJOIN定义右外连接的语法格式。SELECT{*

字段表}FROM左表RIGHT[OUTER]JOIN右表ON连接条件;右外连接的结果集中包含右表所有的记录行，当