教学课件项目5 查询与维护学生信息管理数据表.pptx

《教学课件项目5 查询与维护学生信息管理数据表.pptx》由会员分享，可在线阅读，更多相关《教学课件项目5 查询与维护学生信息管理数据表.pptx（84页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、MySQL数据库原理及应用（微课版|第3版）数据库原理及应用数据库原理及应用-项目5 查询与维护学生信息管理数据表MySQL数据库原理及应用（微课版|第3版）情景导入情景导入通过几周的学习实践，王宁已经完成数据库和数据表的创建与维护，并导入数据。通过几周的学习实践，王宁已经完成数据库和数据表的创建与维护，并导入数据。此时，李老师给王宁布置了另一项任务，在我们的学生信息管理系统中，如何查此时，李老师给王宁布置了另一项任务，在我们的学生信息管理系统中，如何查询某个学生的信息，或者有新生入学或其它同学转学加入某班级，或者有同学要询某个学生的信息，或者有新生入学或其它同学转学加入某班级，或者有同学要退

2、学，又或者某些同学要修改姓名、专业等信息，怎么实现呢？退学，又或者某些同学要修改姓名、专业等信息，怎么实现呢？带着李老师布置的任务，王宁又开始了自主学习和探索，通过学习，带着李老师布置的任务，王宁又开始了自主学习和探索，通过学习，王宁发现，不只是在学生信息管理系统中会遇到这些问题，在各个王宁发现，不只是在学生信息管理系统中会遇到这些问题，在各个应用领域都会遇到类似问题，比如我们在订购火车票时，车票信息应用领域都会遇到类似问题，比如我们在订购火车票时，车票信息的及时更新与查询，我们网购时每种商品的价格、库存等信息的查的及时更新与查询，我们网购时每种商品的价格、库存等信息的查询询，可见，数据查询与

3、记录的插入、修改、删除等操作是数据，可见，数据查询与记录的插入、修改、删除等操作是数据库应用中最基本最重要的操作。那么，如何去实现这些操作呢？又库应用中最基本最重要的操作。那么，如何去实现这些操作呢？又会用到哪些会用到哪些SQLSQL语句呢？语句呢？MySQL数据库原理及应用（微课版|第3版）?掌握简单数据查询掌握简单数据查询?掌握多表连接查询掌握多表连接查询?理解嵌套查询理解嵌套查询?数据更新数据更新主要内容主要内容MySQL数据库原理及应用（微课版|第3版）项目项目1 1 理解理解数据库数据库职业能力目标（含课程思政）掌握数据查询命令的使用掌握数据查询命令的使用掌握聚集函数的使用方法及技巧

4、掌握聚集函数的使用方法及技巧掌握分组与排序的使用方法掌握分组与排序的使用方法理解多表连接查询和嵌套查询的使用规则理解多表连接查询和嵌套查询的使用规则掌握表记录的插入、修改和删除操作掌握表记录的插入、修改和删除操作MySQL数据库原理及应用（微课版|第3版）任务任务5-1 5-1 掌握简单数据查询掌握简单数据查询【任务提出】【任务提出】王宁同学在完成学生信息管理系统数据库的设计并导入王宁同学在完成学生信息管理系统数据库的设计并导入数据后，李老师给王宁布置了一个新任务，如何从学生信息数据后，李老师给王宁布置了一个新任务，如何从学生信息管理系统数据库中查询每个系每个班级的学生人数，并按班管理系统数据

5、库中查询每个系每个班级的学生人数，并按班级人数降序排序？如何统计参加考试并有考试成绩的学生人级人数降序排序？如何统计参加考试并有考试成绩的学生人数？本任务将带领王宁一起深入学习并解决这些问题。数？本任务将带领王宁一起深入学习并解决这些问题。MySQL数据库原理及应用（微课版|第3版）v数据查询是数据库中最常见的操作，SQL语言通过SELECT语句来实现查询。vSELECT语句的语法结构SELECT 子句1FROM 子句2WHERE 表达式1GROUP BY 子句3HAVING 表达式2ORDER BY 子句4UNION 运算符LIMIT M,NINTO OUTFILE 输出文件名;任务任务5-

6、1 5-1 掌握简单数据查询掌握简单数据查询MySQL数据库原理及应用（微课版|第3版）v功能及说明SELECT子句：指定查询结果中需要返回的值。FROM子句：指定从其中检索行的表或视图。WHERE表达式：指定查询的搜索条件。GROUP BY子句：指定查询结果的分组条件。HAVING表达式：指定分组或集合的查询条件。ORDER BY子句：指定查询结果的排序方法。UNION运算符：将多个SELECT语句查询结果组合为一个结果集，该结果集包含联合查询中的所有查询的全部行。LIMIT M,N：指定输出记录的范围。INTO OUTFILE 输出文件名：将查询结果输出到指定文件中。任务任务5-1 5-1

7、 掌握简单数据查询掌握简单数据查询MySQL数据库原理及应用（微课版|第3版）v语句格式语句格式SELECT ALL|DISTINCT AS ,AS,.FROM LIMIT M,N;（一）单表无条件（一）单表无条件数据查询数据查询MySQL数据库原理及应用（微课版|第3版）v说明说明（1）ALL：表示输出所有记录，包括重复记录。默认值为ALL。DISTINCT：表示在查询结果中去掉重复值。（2）LIMIT N：返回查询结果集中的前N行。加M,：表示从表的第M行开始，返回查询结果集中的N行。M从0开始，N的取值范围由表中的记录数决定。（3）选项：查询结果集中的输出列。可为字段名、表达式或函数。用

8、“*”表示表中的所有字段。若选项为表达式或函数，输出的列名系统自动给出，不是原字段名，故用AS重命名。（4）显示列名：在输出结果中，设置选项显示的列名。（5）表名：要查询的表。表不需打开，到当前路径下寻找表所对应的文件。（一）单表无条件（一）单表无条件数据查询数据查询MySQL数据库原理及应用（微课版|第3版）【例5.1】查询全体学生的学号和姓名。【例5.2】查询全体学生的姓名、学号、所在系。SELECT sno,snameFROM student;SELECT sname,sno,sdeptFROM student;v1 1、查询指定列、查询指定列（一）单表无条件（一）单表无条件数据查询数据

9、查询MySQL数据库原理及应用（微课版|第3版）【例5.3】查询选修了课程的学生学号。SELECT DISTINCT snoFROM sc;如果没有指定DISTINCTDISTINCT，则默认为ALL，即保留结果表中取值重复的行。显示结果如右表所示：snosno20050101012005020201snosno20050101012005010101200501010120050202012005020201（一）单表无条件（一）单表无条件数据查询数据查询MySQL数据库原理及应用（微课版|第3版）【例5.4】查询全体学生的详细记录。【例5.5】输出学生表中的前10条记录。SELECT*FR

10、OM student;SELECT*FROM student LIMIT 10;v2 2、查询全部列、查询全部列上面的语句等价于：SELECT*FROM student LIMIT 0,10;（一）单表无条件（一）单表无条件数据查询数据查询MySQL数据库原理及应用（微课版|第3版）【例5.6】查询全体学生的姓名及其年龄。SELECT sname,YEAR(CURDATE()-YEAR(sbirthday)FROM student;有两种方法指定列名。通过“选项 列名”形式。通过“选项 AS 列名”形式。v3 3、查询经过计算的列、查询经过计算的列（一）单表无条件（一）单表无条件数据查询数据查

11、询MySQL数据库原理及应用（微课版|第3版）【例5.7】查询全体学生的姓名、出生年份和所在系，同时为姓名列指定别名为姓名，出生年份所在列指定别名为年份，系别所在列指定别名为系别。【例5.8】将sc表中的学生成绩增加20%后输出。SELECT sname AS 姓名,出生年份:,YEAR(sbirthday)AS 年份,sdept AS 系别FROM student;SELECT sno,cno,degree*1.2 as 成绩FROM SC;（一）单表无条件（一）单表无条件数据查询数据查询MySQL数据库原理及应用（微课版|第3版）（1）复制表v语法格式：语法格式：SQL提供了复制表的功能，

12、允许用户使用SELECT语句查询得到的结果记录来创建一个新的数据表，复制表使用CREATE TABLE语句，然后把SELECT语句嵌套在其中。CREATE TABLE SELECT 语句;v4 4、查询结果的输出、查询结果的输出（一）单表无条件（一）单表无条件数据查询数据查询MySQL数据库原理及应用（微课版|第3版）v说明说明【例5.9】使用CREATE TABLE语句创建一个新表，存放student表中的姓名和系别两列。新创建的数据表的属性列由SELECT语句的目标列表达式来确定，属性列的列名、数据类型以及在表中的顺序都与SELECT语句的目标列表达式相同。新表的行数据也来自SELECT语

13、句的查询结果，其值可以是计算列表达式，也可以是函数。CREATE TABLE studtemp SELECT sname,sdept FROM student;（一）单表无条件（一）单表无条件数据查询数据查询MySQL数据库原理及应用（微课版|第3版）（2）将查询结果输出到文本文件中v语法格式：语法格式：使用SELECT语句的INTO子句可以将查询结果记录输出到文本文件中，用于数据的备份。INTO子句不能单独使用，它必须包含在SELECT语句中。INTO OUTFILE 文件路径文本文件名 FIELDS TERMINATED BY 分隔符（一）单表无条件（一）单表无条件数据查询数据查询MySQ

14、L数据库原理及应用（微课版|第3版）【例5.10】使用INTO子句将student表中女生的信息备份到D盘bak文件夹中的studwoman.txt中，字段分隔符用逗号“，”。SELECT*FROM student WHERE ssex=女INTO OUTFILE d:/bak/studwoman.txt FIELDS TERMINATED BY,;若想把备份好的文本文件导入到数据库中，可以使用LOAD DATA INFILE 语句。该语句可以将文本文件中的内容读取到一个表中。文件名必须是一个文字字符串。具体信息可参考项目10中的命令介绍。（一）单表无条件（一）单表无条件数据查询数据查询MyS

15、QL数据库原理及应用（微课版|第3版）v语句格式语句格式SELECT ALL|DISTINCT AS,AS,FROM WHERE;（二）使用（二）使用WHEREWHERE子句实现条件查询子句实现条件查询MySQL数据库原理及应用（微课版|第3版）WHERE条件中的运算符查询条件查询条件运运 算算 符符比较运算符比较运算符=,=,!=,!=,=,!=,!范围运算符范围运算符BETWEEN AND,NOT BETWEEN ANDBETWEEN AND,NOT BETWEEN AND列表运算符列表运算符IN,NOT ININ,NOT IN字符匹配符字符匹配符LIKE,NOT LIKELIKE,NOT

16、 LIKE空值空值IS NULL,IS NOT NULLIS NULL,IS NOT NULL逻辑运算符逻辑运算符AND,OR,NOTAND,OR,NOT（二）使用（二）使用WHEREWHERE子句实现条件查询子句实现条件查询MySQL数据库原理及应用（微课版|第3版）比较运算符语法格式：WHERE 表达式1 比较运算符 表达式2【例5.11】查询所有男生的信息。SELECT *FROM student WHERE ssex=男;【例5.12】查询所有成绩大于80分的学生的学号和成绩。SELECT sno AS 学号,degree AS成绩FROM scWHERE degree80;（二）使用

17、（二）使用WHEREWHERE子句实现条件查询子句实现条件查询MySQL数据库原理及应用（微课版|第3版）【例5.13】查询所有男生的学号、姓名、系别及出生日期。SELECT sno,sname,sdept,sbirthdayFROM student WHERE ssex=男;【例5.14】查询计算机工程系全体学生的名单。SELECT sname FROM student WHERE sdept=计算机工程系;【例5.15】查询考试成绩不及格的学生的学号。SELECT DISTINCT sno FROM sc WHERE degree90 or degree60;（二）使用（二）使用WHERE

18、WHERE子句实现条件查询子句实现条件查询MySQL数据库原理及应用（微课版|第3版）【例5.18】查询非计算机工程系的学生信息。SELECT*FROM student WHERE NOT sdept=计算机工程系;v或或SELECT*FROM student WHERE sdept计算机工程系;v学生课堂实践：查询有考试成绩的课程号。查询数学系的男生信息。查询计算机工程系和数学系学生的姓名、性别和出生日期，显示列名分别为“姓名”、“性别”和“出生日期”。查询考试成绩在6070分的学生学号和成绩。（二）使用（二）使用WHEREWHERE子句实现条件查询子句实现条件查询MySQL数据库原理及应用

19、（微课版|第3版）范围运算符(betweenAND)语法格式：表达式 NOT BETWEEN 初始值 AND 终止值v注意：注意：绝对绝对不允许初始值大于终止不允许初始值大于终止值值!其中，NOT为可选项，初始值表示范围的下限，终止值表示范围的上限。【例5.19】查询成绩在6070分的学生学号及成绩。SELECT sno,degreeFROM scWHERE degree BETWEEN 60 AND 70;（二）使用（二）使用WHEREWHERE子句实现条件查询子句实现条件查询MySQL数据库原理及应用（微课版|第3版）字符运算符(LIKE或NOT LIKE)语法格式：WHERE 字段名 N

20、OT LIKE 字符串 ESCAPE 转义字符v通配符及其说明：通配符及其说明：%：任意多个字符：任意多个字符_ _：任意一个字符：任意一个字符ESCAPE 转义字符的作用是当用户要查询的字符串本身含有通配符时，可以使用该选项对通配符进行转义。【例5.20】查询所有姓李的学生的个人信息。SELECT *FROM student WHERE sname LIKE 李%;（二）使用（二）使用WHEREWHERE子句实现条件查询子句实现条件查询MySQL数据库原理及应用（微课版|第3版）【例5.21】查询生源地不是山东省的所有学生信息。SELECT*FROM studentWHERE saddres

21、s NOT LIKE%山东省%;【例5.22】查询名字中第2个字为“阳”字的学生的姓名和学号。SELECT sname,sno FROM student WHERE sname LIKE _阳%;【例5.23】查询学号为“2008030122”的学生姓名和性别。SELECT sname,ssex FROM student WHERE sno LIKE 2008030122;（二）使用（二）使用WHEREWHERE子句实现条件查询子句实现条件查询MySQL数据库原理及应用（微课版|第3版）【例5.24】查询DB_Design课程的课程号。SELECT cno FROM courseWHERE c

22、name LIKE DB/_Design ESCAPE/;其中，ESCAPE/短语表示“/”为转义字符，这样匹配串中紧跟在“/”后面的字符“_”不再具有通配符的含义，转义为普通的“_”字符。（二）使用（二）使用WHEREWHERE子句实现条件查询子句实现条件查询MySQL数据库原理及应用（微课版|第3版）正则表达式语法格式：WHERE 字段名 REGEXP 操作符【例5.25】查询家庭住址以“济”开头的学生信息。SELECT*FROM student WHERE saddress REGEXP 济;【例5.26】查询学生电话号码出现“66”数字的学生信息。SELECT*FROM student

23、 WHERE sphone REGEXP 66;（二）使用（二）使用WHEREWHERE子句实现条件查询子句实现条件查询MySQL数据库原理及应用（微课版|第3版）列表运算符(IN或NOT IN)语法格式：WHERE 表达式 NOT IN 值列表【例5.27】查询信息工程系、软件工程系和计算机工程系学生的姓名和性别。SELECT sname,ssex FROM studentWHERE sdept IN(计算机工程系,软件工程系,信息工程系);此处，WHERE条件表达式的另一种表示方法是sdept=计算机工程系 OR sdept=软件工程系 OR sdept=信息工程系（二）使用（二）使用WH

24、EREWHERE子句实现条件查询子句实现条件查询MySQL数据库原理及应用（微课版|第3版）涉及空值的查询(NULL或NOT NULL)语法格式：WHERE 字段 IS NOT NULL【例5.28】某些学生选修课程后没有参加考试，所以有选修记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。SELECT sno,cno FROM sc WHERE degree IS NULL;（二）使用（二）使用WHEREWHERE子句实现条件查询子句实现条件查询MySQL数据库原理及应用（微课版|第3版）聚集函数可作为列标识符出现在SELECT子句的目标列、HAVING子句的条件或ORDER B

25、Y子句中。聚集函数聚集函数具体用法具体用法具体含义具体含义COUNTCOUNTCOUNT(DISTINCT|ALL*)统计元组个数统计元组个数COUNTCOUNTCOUNT(DISTINCT|ALL)统计一列中值的个数统计一列中值的个数SUMSUMSUM(DISTINCT|ALL)计算一列值的总和（此列必须为数值型）计算一列值的总和（此列必须为数值型）AVGAVGAVG(DISTINCT|ALL)计算一列值的平均值（此列必须为数值型）计算一列值的平均值（此列必须为数值型）MAXMAXMAX(DISTINCT|ALL)求一列值中的最大值求一列值中的最大值MINMINMIN(DISTINCT|AL

26、L MIN(DISTINCT|ALL)求一列值中的最小值求一列值中的最小值（三）使用常用聚集函数统计数据（三）使用常用聚集函数统计数据MySQL数据库原理及应用（微课版|第3版）SELECT COUNT(*)FROM student;SELECT COUNT(DISTINCT sno)FROM sc;SELECT AVG(degree)FROM sc WHERE cno=C01;【例5.29】查询学生总数。【例5.30】查询选修了课程的学生人数。【例5.31】计算C01号课程的学生平均成绩。（三）使用常用聚集函数统计数据（三）使用常用聚集函数统计数据MySQL数据库原理及应用（微课版|第3版）

27、【例5.32】查询选修了C01号课程的学生最高分和最低分。SELECT MAX(degree)AS 最高分,MIN(degree)AS 最低分FROM sc WHERE cno=C01;【例5.33】查询学号为“2012010112”的学生的总成绩及平均成绩。SELECT SUM(degree)AS 总成绩,AVG(degree)AS 平均成绩FROM sc WHERE sno=2012010112;（三）使用常用聚集函数统计数据（三）使用常用聚集函数统计数据MySQL数据库原理及应用（微课版|第3版）使用GROUP BY子句可以将查询结果按照某一列或多列数据值进行分类，换句话说，就是对查询结

28、果的信息进行归纳，以汇总相关数据。v语法格式：GROUP BY 列名清单 HAVING 条件表达式vGROUP BY子句把查询结果集中的各行按列名清单进行分组。在这些列上，对应值都相同的记录分在同一组。若无HAVING子句，则各组分别输出；若有HAVING子句，只有符合HAVING条件的组才输出。（四）分组筛选数据（四）分组筛选数据MySQL数据库原理及应用（微课版|第3版）SELECT sdept,COUNT(*)各系人数FROM studentGROUP BY sdept;SELECT ssex,COUNT(*)人数FROM student GROUP BY ssex;SELECT sde

29、pt,ssex,COUNT(*)FROM student GROUP BY sdept,ssex;（四）分组筛选数据（四）分组筛选数据【例5.34】统计各系学生数。【例5.35】统计student表中男、女学生人数。【例5.36】统计各系男、女生人数。MySQL数据库原理及应用（微课版|第3版）【例5.37】统计各系女生人数。SELECT sdept,COUNT(*)各系女生人数FROM studentWHERE ssex=女GROUP BY sdept;v或或SELECT sdept,COUNT(*)FROM studentGROUP BY sdept,ssexHAVING ssex=女;S

30、ELECT snoFROM scGROUP BY snoHAVING COUNT(*)3;【例5.38】查询选修了3门以上课程的学生学号。（四）分组筛选数据（四）分组筛选数据MySQL数据库原理及应用（微课版|第3版）v本小节基于学生信息管理数据库，借助本小节基于学生信息管理数据库，借助GROUP BYGROUP BY子句实现了数据的分子句实现了数据的分类统计和数据分析，数据量不是特别大。但是，在大数据技术迅速发类统计和数据分析，数据量不是特别大。但是，在大数据技术迅速发展的今天，数据量呈现指数级增长，大数据分析技术也日渐成熟、应展的今天，数据量呈现指数级增长，大数据分析技术也日渐成熟、应用广

31、泛。用广泛。思政小贴士思政小贴士v其中，比较典型的行业就是电商行业，运用相关技术对行业其中，比较典型的行业就是电商行业，运用相关技术对行业数据进行分析，对提高行业的整体运行效率起到了极大的推数据进行分析，对提高行业的整体运行效率起到了极大的推动作用。但对于像动作用。但对于像HadoopHadoop、非结构化数据库、数据可视化工、非结构化数据库、数据可视化工具以及个性化推荐引擎这样的新技术，其较高的技术门槛和具以及个性化推荐引擎这样的新技术，其较高的技术门槛和高昂的运营维护成本使得国内只有少数企业能够将其运用到高昂的运营维护成本使得国内只有少数企业能够将其运用到深入分析行业数据中。深入分析行业数

32、据中。v作为学生，如何利用所学知识，借助数据分析，为创造智作为学生，如何利用所学知识，借助数据分析，为创造智慧校园、智慧城市添砖加瓦，是值得我们思考的问题慧校园、智慧城市添砖加瓦，是值得我们思考的问题。MySQL数据库原理及应用（微课版|第3版）使用ORDER BY子句可以对查询结果按照一个或多个字段进行升序（ASC）或降序（DESC）排序，默认值为升序。v语法格式：ORDER BY ASC|DESC,ASC|DESC,vSELECT语句的查询结果集中各记录将按顺序输出。首先按第1个列名值排序；前一个列名值相同者，再按下一个列名值排序，依此类推。若某列名后有DESC，则以该列名值排序时为降序排

33、列；否则，为升序排列。（五）对查询结果进行排序（五）对查询结果进行排序MySQL数据库原理及应用（微课版|第3版）【例5.39】查询选修了C03号课程的学生的学号及其成绩，查询结果按分数的降序排列。SELECT sno,degreeFROM scWHERE cno=C03ORDER BY degree DESC;SELECT*FROM studentORDER BY sdept ASC,sbirthday DESC;【例5.40】查询全体学生情况，查询结果按所在系升序排列，同一系中的学生按出生日期降序排列。（五）对查询结果进行排序（五）对查询结果进行排序MySQL数据库原理及应用（微课版|第3

34、版）任务任务5-1 5-1 掌握简单数据查询掌握简单数据查询【任务实施】【任务实施】通过深入学习，王宁找到了问题的解决方法，并给出相通过深入学习，王宁找到了问题的解决方法，并给出相应的代码如下：应的代码如下：SELECT COUNT(DISTINCT sno)FROM scWHERE degree IS NOT NULL;（2）统计参加考试有考试成绩的学生人数。SELECT sdept,classno,count(*)FROM student GROUP BY sdept,classno ORDER BY count(*);（1）查询每个系每个班级的学生人数，并按班级人数降序排序。MySQL数

35、据库原理及应用（微课版|第3版）任务任务5-2 5-2 掌握多表连接查询掌握多表连接查询【任务提出】【任务提出】在任务在任务 5-1 5-1的学习中，王宁发现一个问题，这些数据查询的学习中，王宁发现一个问题，这些数据查询都是在一张表中进行的，那么，如果要查询计算机工程系的学都是在一张表中进行的，那么，如果要查询计算机工程系的学生的学号、姓名、课程名及成绩，就要涉及生的学号、姓名、课程名及成绩，就要涉及3 3张表，该如何实现张表，该如何实现呢呢?在实际应用中，也经常会进行涉及两张表甚至在实际应用中，也经常会进行涉及两张表甚至 3 3张表的数据张表的数据查询。本任务将带领王宁学习如何实现多表连接查

36、询。查询。本任务将带领王宁学习如何实现多表连接查询。MySQL数据库原理及应用（微课版|第3版）任务任务5-2 5-2 掌握多表连接查询掌握多表连接查询【任务实施】【任务实施】在任务在任务 5-1 5-1的学习中，王宁发现一个问题，这些数据查询的学习中，王宁发现一个问题，这些数据查询都是在一张表中进行的，那么，如果要查询计算机工程系的学都是在一张表中进行的，那么，如果要查询计算机工程系的学生的学号、姓名、课程名及成绩，就要涉及生的学号、姓名、课程名及成绩，就要涉及3 3张表，该如何实现张表，该如何实现呢呢?在实际应用中，也经常会进行涉及两张表甚至在实际应用中，也经常会进行涉及两张表甚至 3 3

37、张表的数据张表的数据查询。本任务将带领王宁学习如何实现多表连接查询。查询。本任务将带领王宁学习如何实现多表连接查询。MySQL数据库原理及应用（微课版|第3版）理解多表连接查询多表连接查询是指查询同时涉及两个或两个以上的表，连接查询是关系数据库中最主要的查询，表与表之间的连接分为交叉连接（Cross Join）、内连接（Inner Join）、自连接（Self Join）、外连接（Outer Join）。外连接又分为3种，即左外连接（Left Join）、右外连接（Right Join）和全外连接（Full Join）。连接查询的类型可以在SELECT语句的FROM子句中指定，也可以在WHER

38、E子句中指定。任务任务5-2 5-2 掌握多表连接查询掌握多表连接查询MySQL数据库原理及应用（微课版|第3版）交叉连接又称笛卡儿连接，是指两个表之间做笛卡儿积操作，得到结果集的行数是两个表的行数的乘积。（一）交叉连接（一）交叉连接SELECT ALL|DISTINCT 别名.AS,别名.AS,FROM 别名1,别名2；v语句格式：语句格式：MySQL数据库原理及应用（微课版|第3版）【例5.41】成绩表（sc）和课程关系表（course）进行交叉连接。v此处为了简化表名，分别给两个表指定了别名。但是，一旦表名指定了别名，在该命令中，都必须用别名代替表名。SELECT A.*,B.*FROM

39、 course A,sc B;（一）交叉连接（一）交叉连接MySQL数据库原理及应用（微课版|第3版）SELECT ALL|DISTINCT 别名.AS,别名.AS,FROM 别名1,别名2,WHERE AND;（二）内连接（二）内连接SELECT ALL|DISTINCT 别名.AS,别名.AS,FROM 别名1 INNER JOIN 别名2 ON WHERE;v语句格式：语句格式：v或者为：或者为：MySQL数据库原理及应用（微课版|第3版）v或者为：或者为：SELECT A.*,B.*FROM student A,sc BWHERE A.sno=B.sno;SELECT A.*,B.*F

40、ROM student A INNER JOIN sc B ON A.sno=B.sno;SELECT student.sno,sname,ssex,sbirthday,sdept,cno,degreeFROM student,scWHERE student.sno=sc.sno;（二）内连接（二）内连接【例5.42】查询每个学生及其选修课的情况。【例5.43】用自然连接完成例5.42的查询。MySQL数据库原理及应用（微课版|第3版）【例5.44】输出所有女学生的学号、姓名、课程号及成绩。v或者为：或者为：SELECT A.sno,sname,cno,degreeFROM student A

41、,sc BWHERE A.sno=B.sno AND ssex=女;SELECT A.sno,sname,cno,degree FROM student A INNER JOIN sc B ON A.sno=B.snoWHERE ssex=女;（二）内连接（二）内连接MySQL数据库原理及应用（微课版|第3版）【例5.45】输出计算机工程系学生的学号、姓名、课程名及成绩。v或者为：或者为：SELECT A.sno,sname,cname,degreeFROM student A,sc B,course CWHERE A.sno=B.sno AND B.cno=C.cno AND sdept=计

42、算机工程系;SELECT A.sno,sname,cname,degreeFROM student A INNER JOIN sc B ON A.sno=B.snoINNER JOIN course C ON B.cno=C.cnoWHERE sdept=计算机工程系;（二）内连接（二）内连接MySQL数据库原理及应用（微课版|第3版）连接操作不只是在不同的表之间进行，一张表内还可以进行自身连接操作，即将同一个表的不同行连接起来。自连接可以看作一张表的两个副本之间的连接。在自连接中，必须为表指定两个别名，使之在逻辑上成为两张表。（三）自连接（三）自连接SELECT ALL|DISTINCT 别

43、名.AS,别名.AS,FROM 别名1,别名2,WHERE AND；v语句格式：语句格式：MySQL数据库原理及应用（微课版|第3版）【例5.45】查询同时选修了C01和C04课程的学生学号。SELECT A.sno FROM sc A,sc BWHERE A.sno=B.sno AND A.cno=C01 AND B.cno=C04;【例5.46】查询与刘晨在同一个系学习的学生的学号、姓名和所在系。SELECT B.sno,B.sname,B.sdeptFROM student A,student BWHERE A.sdept=B.sdept AND A.sname=刘晨 AND B.sna

44、me!=刘晨;（三）自连接（三）自连接MySQL数据库原理及应用（微课版|第3版）在自然连接中，只有在两个表中匹配的行才能在结果集中出现。而在外连接中可以只限制一个表，而对另外一个表不加限制（所有的行都出现在结果集中）。v外连接分为左外连接、右外连接和全外连接。v左外连接是对连接条件中左边的表不加限制，即在结果集中保留连接表达式左表中的非匹配记录；v右外连接是对右边的表不加限制，即在结果集中保留连接表达式右表中的非匹配记录；v全外连接对两个表都不加限制，所有两个表中的行都会包括在结果集中。（四）外连接（四）外连接MySQL数据库原理及应用（微课版|第3版）SELECT ALL|DISTINCT

45、 别名.AS,别名.AS,FROM LEFT|RIGHT|FULL OUTERJOIN ON=;v语句格式：语句格式：【例5.47】利用左外连接查询改写例5.42。SELECT student.sno,sname,ssex,sbirthday,sdept,cno,degree FROM student LEFT JOIN sc ON student.sno=sc.sno;（四）外连接（四）外连接MySQL数据库原理及应用（微课版|第3版）任务任务5-5-2 2 掌握多表连接查询掌握多表连接查询【任务实施】【任务实施】王宁成功利用多表连接查询的两种语法格式解决了任务提出中的问题，相应代码如下。输

46、出计算机工程系学生的学号、姓名、课程名及成绩。SELECT A.sno,sname,cname,degreeFROM student A,sc B,course CWHERE A.sno=B.sno AND B.cno=C.cno AND sdept=计算机工程系;其中，A.sno=B.sno AND B.cno=C.cno是连接条件，3个表进行两两连接。另一种方法如下。SELECT A.sno,sname,cname,degreeFROM student A INNER JOIN sc B ON A.sno=B.snoINNER JOIN course C ON B.cno=C.cnoWHE

47、RE sdept=计算机工程系;MySQL数据库原理及应用（微课版|第3版）任务任务5-3 5-3 理解嵌套查询理解嵌套查询【任务提出】【任务提出】在掌握了多表连接查询之后，王宁已经可以完成大部分的查询任务了，但是，善于思考的王宁又提了新的问题，有没有更加高效的查询方法呢？李老师告诉王宁，可以用嵌套查询实现。本任务将带领王宁一起深入学习嵌套查询，并查询与王宁在同一个系的学生信息。MySQL数据库原理及应用（微课版|第3版）在SQL语言中，一个SELECTFROMWHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING子句的条件中称为嵌套查询或子查询。SELEC

48、T sname FROM studentWHERE sno IN(SELECT sno FROM sc WHERE cno=C02);v例如：例如：任务任务5-3 5-3 理解嵌套查询理解嵌套查询MySQL数据库原理及应用（微课版|第3版）需要特别指出的是，子查询中的SELECT语句用一对括号“()”定界，查询结果必须确定，并且在该SELECT语句中不能使用ORDER BY子句，ORDER BY子句永远只能对最终查询结果排序。v子查询一般分为两种：嵌套子查询和相关子查询。v嵌套查询的求解方法是由里向外处理的，即每个子查询在其上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。任务任

49、务5-3 5-3 理解嵌套查询理解嵌套查询MySQL数据库原理及应用（微课版|第3版）执行过程：嵌套子查询又称为不相关子查询，也就是说，嵌套子查询的执行不依赖于外部嵌套。首先执行子查询，子查询得到的结果集不被显示出来，而是传给外部查询，作为外部查询的条件使用，然后执行外部查询，并显示查询结果。子查询可以多层嵌套。（一）嵌套子查询（一）嵌套子查询MySQL数据库原理及应用（微课版|第3版）【例5.48】查询所有年龄大于平均年龄的学生姓名。SELECT snameFROM studentWHERE YEAR(CURDATE()-YEAR(sbirthday)(SELECT AVG(YEAR(CUR

50、DATE()-YEAR(sbirthday)FROM student);v1 1、子查询返回单个值、子查询返回单个值（一）嵌套子查询（一）嵌套子查询MySQL数据库原理及应用（微课版|第3版）【例5.49】查询没有选修高等数学的学生学号和姓名。SELECT sno,snameFROM studentWHERE sno NOT IN(SELECT sno FROM sc WHERE cno IN(SELECT cno FROM course WHERE cname=数学);v2 2、子查询返回一个值列表、子查询返回一个值列表(用用ININ操作符实现查询操作符实现查询)（一）嵌套子查询（一）嵌套子