MySQL数据库原理及应用（微课版|第4版）
数据库原理及应用
----项目5 查询与维护学生信
息管理数据表
MySQL数据库原理及应用（微课版|第4版）
情景导入
通过几周的学习实践，王宁已经完成数据库和数据表的创建与维护，并导入数据。
此时，李老师给王宁布置了另一项任务，在我们的学生信息管理系统中，如何查
询某个学生的信息，或者有新生入学或其它同学转学加入某班级，或者有同学要
退学，又或者某些同学要修改姓名、专业等信息，怎么实现呢？
带着李老师布置的任务，王宁又开始了自主学习和探索，通过学习，
王宁发现，不只是在学生信息管理系统中会遇到这些问题，在各个
应用领域都会遇到类似问题，比如我们在订购火车票时，车票信息
的及时更新与查询，我们网购时每种商品的价格、库存等信息的查
询……，可见，数据查询与记录的插入、修改、删除等操作是数据
库应用中最基本最重要的操作。那么，如何去实现这些操作呢？又
会用到哪些SQL语句呢？
MySQL数据库原理及应用（微课版|第4版）
掌握简单数据查询
掌握多表连接查询
理解嵌套查询
数据更新
主要内容
MySQL数据库原理及应用（微课版|第4版）
项目1 理解数据库
职业能力目标（含课程思政）
掌握数据查询命令的使用
掌握聚集函数的使用方法及技巧
掌握分组与排序的使用方法
理解多表连接查询和嵌套查询的使用规则
掌握表记录的插入、修改和删除操作
MySQL数据库原理及应用（微课版|第4版）
任务5-1 掌握简单数据查询
【任务提出】
王宁同学在完成学生信息管理系统数据库的设计并导入
数据后，李老师给王宁布置了一个新任务，如何从学生信息
管理系统数据库中查询每个系每个班级的学生人数，并按班
级人数降序排序？如何统计参加考试并有考试成绩的学生人
数？本任务将带领王宁一起深入学习并解决这些问题。
MySQL数据库原理及应用（微课版|第4版）
数据查询是数据库中最常见的操作，SQL语言通过SELECT语句
来实现查询。
SELECT语句的语法结构
SELECT 子句1
FROM 子句2
[WHERE 表达式1]
[GROUP BY 子句3
[HAVING 表达式2]]
[ORDER BY 子句4]
[UNION 运算符]
[LIMIT [M,]N]
[INTO OUTFILE 输出文件名];
任务5-1 掌握简单数据查询
MySQL数据库原理及应用（微课版|第4版）
功能及说明
SELECT子句：指定查询结果中需要返回的值。
FROM子句：指定从其中检索行的表或视图。
WHERE表达式：指定查询的搜索条件。
GROUP BY子句：指定查询结果的分组条件。
HAVING表达式：指定分组或集合的查询条件。
ORDER BY子句：指定查询结果的排序方法。
UNION运算符：将多个SELECT语句查询结果组合为一个结果集，
该结果集包含联合查询中的所有查询的全部行。
[LIMIT [M,]N]：指定输出记录的范围。
INTO OUTFILE 输出文件名：将查询结果输出到指定文件中。
任务5-1 掌握简单数据查询
MySQL数据库原理及应用（微课版|第4版）
语句格式
SELECT [ALL|DISTINCT] <选项> [AS <显示列名>]
[,<选项> [AS <显示列名>][,...]]
FROM <表名|视图名> [LIMIT [M,]N];
（一）单表无条件数据查询
MySQL数据库原理及应用（微课版|第4版）
说明
（1）ALL：表示输出所有记录，包括重复记录。默认值为ALL。
DISTINCT：表示在查询结果中去掉重复值。
（2）LIMIT N：返回查询结果集中的前N行。加[M,]：表示从表的第M
行开始，返回查询结果集中的N行。M从0开始，N的取值范围由表中的
记录数决定。
（3）选项：查询结果集中的输出列。可为字段名、表达式或函数。用
“*”表示表中的所有字段。若选项为表达式或函数，输出的列名系统
自动给出，不是原字段名，故用AS重命名。
（4）显示列名：在输出结果中，设置选项显示的列名。
（5）表名：要查询的表。表不需打开，到当前路径下寻找表所对应的
文件。
（一）单表无条件数据查询
MySQL数据库原理及应用（微课版|第4版）
【例】 查询全体学生的学号和姓名。
【例】 查询全体学生的姓名、学号、所在系。
SELECT sno,sname
FROM student;
SELECT sname,sno,sdept
FROM student;
1、查询指定列
（一）单表无条件数据查询
MySQL数据库原理及应用（微课版|第4版）
【例】 查询选修了课程的学生学号。
SELECT DISTINCT sno
FROM sc;
如果没有指定DISTINCT，则默认为
ALL，即保留结果表中取值重复的
行。显示结果如右表所示：
sno
2005010101
2005020201
sno
2005010101
2005010101
2005010101
2005020201
2005020201
（一）单表无条件数据查询
MySQL数据库原理及应用（微课版|第4版）
【例】查询全体学生的详细记录。
【例】输出学生表中的前10条记录。
SELECT *
FROM student;
SELECT * FROM student LIMIT 10;
2、查询全部列
上面的语句等价于：
SELECT * FROM student LIMIT 0,10;
（一）单表无条件数据查询
MySQL数据库原理及应用（微课版|第4版）
【例】查询全体学生的姓名及其年龄。
SELECT sname,YEAR(CURDATE())-YEAR(sbirthday)
FROM student;
有两种方法指定列名。
① 通过“选项 列名”形式。
② 通过“选项 AS 列名”形式。
3、查询经过计算的列
（一）单表无条件数据查询
MySQL数据库原理及应用（微课版|第4版）
【例】查询全体学生的姓名、出生年份和所在系，同时为姓名
列指定别名为姓名，出生年份所在列指定别名为年份，系别所在
列指定别名为系别。
【例】将sc表中的学生成绩增加20%后输出。
SELECT sname AS '姓名','出生年份:', YEAR(sbirthday)
AS '年份',sdept AS '系别'
FROM student;
SELECT sno,cno, degree* as 成绩
FROM SC;
（一）单表无条件数据查询
MySQL数据库原理及应用（微课版|第4版）
• （1）复制表
语法格式：
SQL提供了复制表的功能，允许用户使用SELECT语句查询得
到的结果记录来创建一个新的数据表，复制表使用CREATE
TABLE语句，然后把SELECT语句嵌套在其中。
CREATE TABLE <新表名> SELECT 语句;
4、查询结果的输出
（一）单表无条件数据查询
MySQL数据库原理及应用（微课版|第4版）
说明
【例】 使用CREATE TABLE语句创建一个新表，存放
student表中的姓名和系别两列。
新创建的数据表的属性列由SELECT语句的目标列表达式来确
定，属性列的列名、数据类型以及在表中的顺序都与SELECT
语句的目标列表达式相同。新表的行数据也来自SELECT语句
的查询结果，其值可以是计算列表达式，也可以是函数。
CREATE TABLE studtemp
SELECT sname, sdept FROM student;
（一）单表无条件数据查询
MySQL数据库原理及应用（微课版|第4版）
• （2）将查询结果输出到文本文件中
语法格式：
使用SELECT语句的INTO子句可以将查询结果记录输出到文本
文件中，用于数据的备份。INTO子句不能单独使用，它必须
包含在SELECT语句中。
INTO OUTFILE ‘[文件路径]文本文件名'
[FIELDS TERMINATED BY '分隔符']
（一）单表无条件数据查询
MySQL数据库原理及应用（微课版|第4版）
【例】 使用INTO子句将student表中女生的信息备份到D盘bak
文件夹中的中，字段分隔符用逗号“，”。
SELECT * FROM student WHERE ssex='女'
INTO OUTFILE 'd:/bak/'
FIELDS TERMINATED BY ',';
若想把备份好的文本文件导入到数据库中，可以使用
LOAD DATA INFILE 语句。该语句可以将文本文件中的内
容读取到一个表中。文件名必须是一个文字字符串。具
体信息可参考项目10中的命令介绍。
（一）单表无条件数据查询
MySQL数据库原理及应用（微课版|第4版）
语句格式
SELECT [ALL|DISTINCT] <选项> [AS<显示列名>]
[,<选项> [AS<显示列名>][,…]]
FROM <表名|视图名>
WHERE <条件表达式>;
（二）使用WHERE子句实现条件查询
MySQL数据库原理及应用（微课版|第4版）
• WHERE条件中的运算符
查询条件 运 算 符
比较运算符 =,<,>,<=,>=,< >,!=,!<,!>
范围运算符 BETWEEN AND,NOT BETWEEN AND
列表运算符 IN,NOT IN
字符匹配符 LIKE,NOT LIKE
空值 IS NULL,IS NOT NULL
逻辑运算符 AND,OR,NOT
（二）使用WHERE子句实现条件查询
MySQL数据库原理及应用（微课版|第4版）
• 比较运算符
语法格式：WHERE 表达式1 比较运算符 表达式2
【例】 查询所有男生的信息。
SELECT * FROM student WHERE ssex='男';
【例】 查询所有成绩大于80分的学生的学号
和成绩。
SELECT sno AS '学号',degree AS'成绩'
FROM sc
WHERE degree>80;
（二）使用WHERE子句实现条件查询
MySQL数据库原理及应用（微课版|第4版）
【例】 查询所有男生的学号、姓名、系别及出生日期。
SELECT sno, sname,sdept,sbirthday
FROM student WHERE ssex='男';
【例】 查询计算机工程系全体学生的名单。
SELECT sname FROM student WHERE sdept='计算机工程系
';
【例】 查询考试成绩不及格的学生的学号。
SELECT DISTINCT sno FROM sc WHERE degree<60;
（二）使用WHERE子句实现条件查询
MySQL数据库原理及应用（微课版|第4版）
• 逻辑运算符(AND、OR和NOT)
语法格式：WHERE NOT 逻辑表达式|逻辑表达式1 逻辑运算符
逻辑表达式2
【例】 查询计算机工程系女生的信息。
SELECT * FROM student
WHERE sdept='计算机工程系' AND ssex='女';
【例】 查询成绩在90分以上或不及格的学生
学号和课程号信息。
SELECT sno,cno FROM sc
WHERE degree>90 or degree<60;
（二）使用WHERE子句实现条件查询
MySQL数据库原理及应用（微课版|第4版）
【例】 查询非计算机工程系的学生信息。
SELECT * FROM student WHERE NOT sdept='计算机工程系';
或
SELECT * FROM student WHERE sdept<>'计算机工程系';
学生课堂实践：
① 查询有考试成绩的课程号。
② 查询数学系的男生信息。
③ 查询计算机工程系和数学系学生的姓名、性别和出生日
期，显示列名分别为“姓名”、“性别”和“出生日期”。
④ 查询考试成绩在60~70分的学生学号和成绩。
（二）使用WHERE子句实现条件查询
MySQL数据库原理及应用（微课版|第4版）
• 范围运算符(between…AND…)
语法格式：表达式 [NOT] BETWEEN 初始值 AND 终止值
注意：绝对不允许初始值大于终止值!!!
其中，NOT为可选项，初始值表示范围的下限，终止值
表示范围的上限。
【例】 查询成绩在60~70分的学生学号及成绩。
SELECT sno, degree
FROM sc
WHERE degree BETWEEN 60 AND 70;
（二）使用WHERE子句实现条件查询
MySQL数据库原理及应用（微课版|第4版）
• 字符运算符(LIKE或NOT LIKE)
语法格式：WHERE 字段名 [NOT] LIKE '字符串' [ESCAPE '
转义字符']
通配符及其说明：
%：任意多个字符
_：任意一个字符
ESCAPE '转义字符'的作用是当用户要查询的字符串本身
含有通配符时，可以使用该选项对通配符进行转义。
【例】 查询所有姓李的学生的个人信息。
SELECT * FROM student WHERE sname LIKE '李%';
（二）使用WHERE子句实现条件查询
MySQL数据库原理及应用（微课版|第4版）
【例】 查询生源地不是山东省的所有学生信息。
SELECT * FROM student
WHERE saddress NOT LIKE '%山东省%';
【例】 查询名字中第2个字为“阳”字的学生的姓名和学号。
SELECT sname,sno FROM student WHERE sname LIKE '_阳
%';
【例】 查询学号为“2008030122”的学生姓名和性别。
SELECT sname,ssex FROM student
WHERE sno LIKE '2008030122';
（二）使用WHERE子句实现条件查询
MySQL数据库原理及应用（微课版|第4版）
【例】 查询DB_Design课程的课程号。
SELECT cno FROM course
WHERE cname LIKE 'DB/_Design' ESCAPE'/';
其中，ESCAPE'/' 短语表示“/”为转义字符，这样匹配串中
紧跟在“/”后面的字符“_”不再具有通配符的含义，转义
为普通的“_”字符。
（二）使用WHERE子句实现条件查询
MySQL数据库原理及应用（微课版|第4版）
• 正则表达式
语法格式：WHERE 字段名 REGEXP '操作符'
【例】 查询家庭住址以“济”开头的学生信息。
SELECT * FROM student
WHERE saddress REGEXP '^济';
【例】 查询学生电话号码出现“66”数字的学生信息。
SELECT * FROM student
WHERE sphone REGEXP '66';
（二）使用WHERE子句实现条件查询
MySQL数据库原理及应用（微课版|第4版）
• 列表运算符(IN或NOT IN)
语法格式：WHERE 表达式 [NOT] IN 值列表
【例】 查询信息工程系、软件工程系和计算机工程
系学生的姓名和性别。
SELECT sname,ssex FROM student
WHERE sdept IN('计算机工程系', '软件工程系', '信息工程系
');
此处，WHERE条件表达式的另一种表示方法是sdept='计算机
工程系' OR sdept='软件工程系' OR sdept='信息工程系'
（二）使用WHERE子句实现条件查询
MySQL数据库原理及应用（微课版|第4版）
• 涉及空值的查询(NULL或NOT NULL)
语法格式：WHERE 字段 IS [NOT] NULL
【例】 某些学生选修课程后没有参加考试，所以有
选修记录，但没有考试成绩。查询缺少成绩的学生的学
号和相应的课程号。
SELECT sno,cno FROM sc WHERE degree IS NULL;
（二）使用WHERE子句实现条件查询
MySQL数据库原理及应用（微课版|第4版）
聚集函数可作为列标识符出现在SELECT子句的目标列、
HAVING子句的条件或ORDER BY子句中。
聚集函数 具体用法 具体含义
COUNT COUNT([DISTINCT|ALL]*) 统计元组个数
COUNT COUNT([DISTINCT|ALL] <列名>) 统计一列中值的个数
SUM SUM([DISTINCT|ALL] <列名>) 计算一列值的总和（此列必须为数值型）
AVG AVG([DISTINCT|ALL] <列名>) 计算一列值的平均值（此列必须为数值型）
MAX MAX([DISTINCT|ALL] <列名>) 求一列值中的最大值
MIN MIN([DISTINCT|ALL] <列名>) 求一列值中的最小值
（三）使用常用聚集函数统计数据
MySQL数据库原理及应用（微课版|第4版）
SELECT COUNT(*) FROM student;
SELECT COUNT(DISTINCT sno) FROM sc;
SELECT AVG(degree) FROM sc WHERE cno='C01';
【例】 查询学生总数。
【例】 查询选修了课程的学生人数。
【例】 计算C01号课程的学生平均成绩。
（三）使用常用聚集函数统计数据
MySQL数据库原理及应用（微课版|第4版）
【例】 查询选修了C01号课程的学生最高分和最低分。
SELECT MAX(degree) AS ‘最高分’,MIN(degree) AS ‘最低分
’
FROM sc
WHERE cno='C01';
【例】 查询学号为“2012010112”的学生的总成绩及平均成绩。
SELECT SUM(degree) AS 总成绩,AVG(degree) AS 平均成绩
FROM sc
WHERE sno='2012010112';
（三）使用常用聚集函数统计数据
MySQL数据库原理及应用（微课版|第4版）
• 使用GROUP BY子句可以将查询结果按照某一列或多列数据值
进行分类，换句话说，就是对查询结果的信息进行归纳，以汇
总相关数据。
语法格式：[GROUP BY 列名清单 [HAVING 条件表达式]]
GROUP BY子句把查询结果集中的各行按列名清单进行分组。
在这些列上，对应值都相同的记录分在同一组。若无HAVING
子句，则各组分别输出；若有HAVING子句，只有符合HAVING
条件的组才输出。
（四）分组筛选数据
MySQL数据库原理及应用（微课版|第4版）
SELECT sdept,COUNT(*) 各系人数
FROM student
GROUP BY sdept;
SELECT ssex,COUNT(*) 人数
FROM student
GROUP BY ssex;
SELECT sdept,ssex,COUNT(*)
FROM student
GROUP BY sdept,ssex;
（四）分组筛选数据
【例】 统计各系学生数。
【例】 统计student表中男、女学生人数。
【例】 统计各系男、女生人数。
MySQL数据库原理及应用（微课版|第4版）
【例】 统计各系女生人数。
SELECT sdept,COUNT(*) 各系女生人数
FROM student
WHERE ssex='女'
GROUP BY sdept;
或 SELECT sdept,COUNT(*)
FROM student
GROUP BY sdept,ssex
HAVING ssex='女';
SELECT sno
FROM sc
GROUP BY sno
HAVING COUNT(*)>3;
【例】 查询选修了3门以上课程的学生学号。
（四）分组筛选数据
MySQL数据库原理及应用（微课版|第4版）
本小节基于学生信息管理数据库，借助GROUP BY子句实现了数据的分
类统计和数据分析，数据量不是特别大。但是，在大数据技术迅速发
展的今天，数据量呈现指数级增长，大数据分析技术也日渐成熟、应
用广泛。
思政小贴士
其中，比较典型的行业就是电商行业，运用相关技术对行业
数据进行分析，对提高行业的整体运行效率起到了极大的推
动作用。但对于像Hadoop、非结构化数据库、数据可视化工
具以及个性化推荐引擎这样的新技术，其较高的技术门槛和
高昂的运营维护成本使得国内只有少数企业能够将其运用到
深入分析行业数据中。
作为学生，如何利用所学知识，借助数据分析，为创造智
慧校园、智慧城市添砖加瓦，是值得我们思考的问题。
MySQL数据库原理及应用（微课版|第4版）
• 使用ORDER BY子句可以对查询结果按照一个或多个字段进行
升序（ASC）或降序（DESC）排序，默认值为升序。
语法格式：[ORDER BY <列名1> [ASC|DESC][,<列名2>
[ASC|DESC]][,…]]
SELECT语句的查询结果集中各记录将按顺序输出。首先
按第1个列名值排序；前一个列名值相同者，再按下一个列
名值排序，依此类推。若某列名后有DESC，则以该列名
值排序时为降序排列；否则，为升序排列。
（五）对查询结果进行排序
MySQL数据库原理及应用（微课版|第4版）
【例】 查询选修了C03号课程的学生的学号及其成绩，
查询结果按分数的降序排列。
SELECT sno,degree
FROM sc
WHERE cno='C03'
ORDER BY degree DESC;
SELECT *
FROM student
ORDER BY sdept ASC, sbirthday DESC;
【例】 查询全体学生情况，查询结果按所在系升序
排列，同一系中的学生按出生日期降序排列。
（五）对查询结果进行排序
MySQL数据库原理及应用（微课版|第4版）
任务5-1 掌握简单数据查询
【任务实施】
通过深入学习，王宁找到了问题的解决方法，并给出相
应的代码如下：
SELECT COUNT(DISTINCT sno)
FROM sc
WHERE degree IS NOT NULL;
（2）统计参加考试有考试成绩的学生人数。
SELECT sdept,classno,count(*)
FROM student
GROUP BY sdept,classno
ORDER BY count(*);
（1）查询每个系每个班级的学生人数，并按班级人数降序排序。
MySQL数据库原理及应用（微课版|第4版）
任务5-2 掌握多表连接查询
【任务提出】
在任务 5-1的学习中，王宁发现一个问题，这些数据查询
都是在一张表中进行的，那么，如果要查询计算机工程系的学
生的学号、姓名、课程名及成绩，就要涉及3张表，该如何实现
呢?在实际应用中，也经常会进行涉及两张表甚至 3张表的数据
查询。本任务将带领王宁学习如何实现多表连接查询。
MySQL数据库原理及应用（微课版|第4版）
任务5-2 掌握多表连接查询
【任务实施】
在任务 5-1的学习中，王宁发现一个问题，这些数据查询
都是在一张表中进行的，那么，如果要查询计算机工程系的学
生的学号、姓名、课程名及成绩，就要涉及3张表，该如何实现
呢?在实际应用中，也经常会进行涉及两张表甚至 3张表的数据
查询。本任务将带领王宁学习如何实现多表连接查询。
MySQL数据库原理及应用（微课版|第4版）
• 理解多表连接查询
多表连接查询是指查询同时涉及两个或两个以上的表，
连接查询是关系数据库中最主要的查询，表与表之间的连接
分为交叉连接（Cross Join）、内连接（Inner Join）、自
连接（Self Join）、外连接（Outer Join）。外连接又分为
3种，即左外连接（Left Join）、右外连接（Right Join）
和全外连接（Full Join）。
连接查询的类型可以在SELECT语句的FROM子句中指定，
也可以在WHERE子句中指定。
任务5-2 掌握多表连接查询
MySQL数据库原理及应用（微课版|第4版）
• 交叉连接又称笛卡儿连接，是指两个表之间做笛卡儿
积操作，得到结果集的行数是两个表的行数的乘积。
（一）交叉连接
SELECT [ALL|DISTINCT] [别名.]<选项1> [AS<显示列
名>] [,[别名.]<选项2> [AS<显示列名>][,…]]
FROM <表名1>[别名1] ,<表名2>[别名2]；
语句格式：
MySQL数据库原理及应用（微课版|第4版）
【例】 成绩表（sc）和课程关系表（course）进行
交叉连接。
此处为了简化表名，分别给两个表指定了别名。
但是，一旦表名指定了别名，在该命令中，都必
须用别名代替表名。
SELECT A.*, B.*
FROM course A, sc B;
（一）交叉连接
MySQL数据库原理及应用（微课版|第4版）
SELECT [ALL|DISTINCT] [别名.]<选项1>[AS<显示列名>]
[,[别名.]<选项2>[AS<显示列名>][,…]]
FROM <表名1> [别名1],<表名2> [别名2][,…]
WHERE <连接条件表达式> [AND <条件表达式>];
（二）内连接
SELECT [ALL|DISTINCT] [别名.]<选项1>[AS<显示列名
>] [,[别名.]<选项2>[AS<显示列名>][,…]]
FROM <表名1> [别名1] INNER JOIN <表名2> [别名2]
ON <连接条件表达式>
[WHERE <条件表达式>];
语句格式：
或者为：
MySQL数据库原理及应用（微课版|第4版）
或者为：
SELECT A.*, B.*
FROM student A,sc B
WHERE = ;
SELECT A.*, B.*
FROM student A INNER JOIN sc B ON =;
SELECT , sname, ssex, sbirthday,
sdept, cno, degree
FROM student, sc
WHERE =;
（二）内连接
【例】 查询每个学生及其选修课的情况。
【例】 用自然连接完成例的查询。
MySQL数据库原理及应用（微课版|第4版）
【例】 输出所有女学生的学号、姓名、课程号及成绩。
或者为：
SELECT , sname, cno, degree
FROM student A, sc B
WHERE = AND ssex='女';
SELECT , sname, cno, degree
FROM student A INNER JOIN sc B ON =
WHERE ssex='女';
（二）内连接
MySQL数据库原理及应用（微课版|第4版）
【例】 输出计算机工程系学生的学号、姓名、课程名及成绩。
或者为：
SELECT , sname, cname, degree
FROM student A, sc B, course C
WHERE = AND = AND sdept='计算
机工程系';
SELECT , sname, cname, degree
FROM student A INNER JOIN sc B ON =
INNER JOIN course C ON =
WHERE sdept='计算机工程系';
（二）内连接
MySQL数据库原理及应用（微课版|第4版）
• 连接操作不只是在不同的表之间进行，一张表内还可以进
行自身连接操作，即将同一个表的不同行连接起来。自连
接可以看作一张表的两个副本之间的连接。在自连接中，
必须为表指定两个别名，使之在逻辑上成为两张表。
（三）自连接
SELECT [ALL|DISTINCT] [别名.]<选项1> [AS<显示列
名>] [,[别名.]<选项2> [AS<显示列名>][,…]]
FROM <表名1> [别名1],<表名1> [别名2][,…]
WHERE <连接条件表达式> [AND <条件表达式>]；
语句格式：
MySQL数据库原理及应用（微课版|第4版）
【例】 查询同时选修了C01和C04课程的学生学号。
SELECT
FROM sc A,sc B
WHERE = AND ='C01' AND ='C04';
【例】 查询与刘晨在同一个系学习的学生的学号、姓名和所在系。
SELECT ,,
FROM student A ,student B
WHERE = AND ='刘晨' AND
!='刘晨';
（三）自连接
MySQL数据库原理及应用（微课版|第4版）
• 在自然连接中，只有在两个表中匹配的行才能在结果集
中出现。而在外连接中可以只限制一个表，而对另外一
个表不加限制（所有的行都出现在结果集中）。
外连接分为左外连接、右外连接和全外连接。
左外连接是对连接条件中左边的表不加限制，即在结果
集中保留连接表达式左表中的非匹配记录；
右外连接是对右边的表不加限制，即在结果集中保留连
接表达式右表中的非匹配记录；
全外连接对两个表都不加限制，所有两个表中的行都会
包括在结果集中。
（四）外连接
MySQL数据库原理及应用（微课版|第4版）
SELECT [ALL|DISTINCT] [别名.]<选项1> [AS<显示列名>]
[,[别名.]<选项2> [AS<显示列名>][,…]]
FROM <表名1> LEFT| RIGHT| FULL [OUTER]JOIN <表名2>
ON <表名1.列1>=<表名2.列2>;
语句格式：
【例】 利用左外连接查询改写例。
SELECT ,sname,ssex,sbirthday,sdept,cno,degree
FROM student LEFT JOIN sc
ON =;
（四）外连接
MySQL数据库原理及应用（微课版|第4版）
任务5-2 掌握多表连接查询
【任务实施】
王宁成功利用多表连接查询的两种语法格式解决了任务提出中的问题，相应代码如下。
输出计算机工程系学生的学号、姓名、课程名及成绩。
SELECT ,sname,cname,degree
FROM student A,sc B,course C
WHERE = AND = AND sdept='计算机工程系';
其中，= AND =是连接条件，3个表进行两两连接。
另一种方法如下。
SELECT , sname, cname, degree
FROM student A INNER JOIN sc B ON =
INNER JOIN course C ON =
WHERE sdept='计算机工程系';
MySQL数据库原理及应用（微课版|第4版）
任务5-3 理解嵌套查询
【任务提出】
在掌握了多表连接查询之后，王宁已经可以完成大部分
的查询任务了，但是，善于思考的王宁又提了新的问题，有
没有更加高效的查询方法呢？李老师告诉王宁，可以用嵌套
查询实现。本任务将带领王宁一起深入学习嵌套查询，并查
询与王宁在同一个系的学生信息。
MySQL数据库原理及应用（微课版|第4版）
• 在SQL语言中，一个SELECT—FROM—WHERE语句
称为一个查询块。将一个查询块嵌套在另一个查询块的
WHERE子句或HAVING子句的条件中称为嵌套查询或
子查询。
SELECT sname
FROM student
WHERE sno IN(SELECT sno FROM sc WHERE cno='C02');
例如：
任务5-3 理解嵌套查询
MySQL数据库原理及应用（微课版|第4版）
• 需要特别指出的是，子查询中的SELECT语句用一对括
号“( )”定界，查询结果必须确定，并且在该SELECT
语句中不能使用ORDER BY子句，ORDER BY子句永
远只能对最终查询结果排序。
子查询一般分为两种：嵌套子查询和相关子查询。
嵌套查询的求解方法是由里向外处理的，即每个子查
询在其上一级查询处理之前求解，子查询的结果用于
建立其父查询的查找条件。
任务5-3 理解嵌套查询
MySQL数据库原理及应用（微课版|第4版）
• 执行过程：
嵌套子查询又称为不相关子查询，也就是说，嵌套子
查询的执行不依赖于外部嵌套。
首先执行子查询，子查询得到的结果集不被显示出来，
而是传给外部查询，作为外部查询的条件使用，然后执
行外部查询，并显示查询结果。子查询可以多层嵌套。
（一）嵌套子查询
MySQL数据库原理及应用（微课版|第4版）
• 【例】 查询所有年龄大于平均年龄的学生姓名。
SELECT sname
FROM student
WHERE YEAR(CURDATE())-YEAR(sbirthday)
>(SELECT AVG(YEAR(CURDATE())-YEAR(sbirthday))
FROM student );
1、子查询返回单个值
（一）嵌套子查询
MySQL数据库原理及应用（微课版|第4版）
【例】 查询没有选修高等数学的学生学号和姓名。
SELECT sno,sname
FROM student
WHERE sno NOT IN ( SELECT sno
FROM sc
WHERE cno IN ( SELECT cno
FROM course
WHERE cname='数学'));
2、子查询返回一个值列表(用IN操作符实现查询)
（一）嵌套子查询
MySQL数据库原理及应用（微课版|第4版）
• 使用格式：<字段><比较符>[ANY|ALL]<子查询>
3、子查询返回一个值列表(用ANY或ALL操作符实现查询)
用 法 含 义
>ANY 大于子查询结果中的某个值
>ALL 大于子查询结果中的所有值
<ANY 小于子查询结果中的某个值
<ALL 小于子查询结果中的所有值
>=ANY 大于等于子查询结果中的某个值
>=ALL 大于等于子查询结果中的所有值
<=ANY 小于等于子查询结果中的某个值
<=ALL 小于等于子查询结果中的所有值
=ANY 等于子查询结果中的某个值
=ALL 等于子查询结果中的所有值（通常没有实际意义）
!=ANY或< >ANY 不等于子查询结果中的某个值
!=ALL或< >ALL 不等于子查询结果中的任何一个值
（一）嵌套子查询
MySQL数据库原理及应用（微课版|第4版）
【例】 查询其他系中比计算机工程系某一学生年龄小的
学生姓名和年龄。
SELECT sname,YEAR(CURDATE())-YEAR(sbirthday)
FROM student
WHERE YEAR(CURDATE())-YEAR(sbirthday)<ANY(SELECT
YEAR(CURDATE())-YEAR(sbirthday)
FROM student
WHERE sdept='计算机工程系')
AND sdept<>'计算机工程系'; //该句为父查询中的一个条件
（一）嵌套子查询
MySQL数据库原理及应用（微课版|第4版）
【例】 查询其他系中比计算机工程系学生年龄都小的学
生。
SELECT * FROM student
WHERE YEAR(CURDATE())-YEAR(sbirthday)<ALL(SELECT
YEAR(CURDATE())-YEAR(sbirthday) FROM student
WHERE sdept='计算机工程系') AND sdept<>'计算机工程系';
SELECT * FROM student
WHERE YEAR(CURDATE())-YEAR(sbirthday)< (SELECT
MIN(YEAR(CURDATE())-YEAR(sbirthday)) FROM student
WHERE sdept='计算机工程系') AND sdept<>'计算机工程系';
或：
（一）嵌套子查询
MySQL数据库原理及应用（微课版|第4版）
• 在相关子查询中，子查询的执行依赖于外部查询，即子
查询的查询条件依赖于外部查询的某个属性值。
（1）子查询为外部查询的每一个元组（行）执行一次，外
部查询将子查询引用列的值传给子查询。
（2）如果子查询的任何行与其匹配，外部查询则取此行放
入结果表。
（3）再回到（1），直到处理完外部表的每一行。
执行过程：
（二）相关子查询
MySQL数据库原理及应用（微课版|第4版）
【例】 查询所有选修了C01号课程的学生姓名。
SELECT sname FROM student
WHERE EXISTS ( SELECT * FROM sc
WHERE sno= AND cno='C01') ;
SELECT sname FROM student
WHERE NOT EXISTS
(SELECT * FROM course
WHERE NOT EXISTS
(SELECT * FROM sc
WHERE sno= AND cno=));
【例】 查询选修了全部课程的学生姓名。
（二）相关子查询
MySQL数据库原理及应用（微课版|第4版）
• 练习：查询成绩比该课程平均成绩高的学生的学号及成绩。
select ,degree
from sc a
where degree>(select avg(degree)
from sc b
where =);
（二）相关子查询
MySQL数据库原理及应用（微课版|第4版）
任务5-3 理解嵌套查询
【任务实施】
SELECT *
FROM student
WHERE sdept=(SELECT sdept
FROM student
WHERE sname='王宁')
AND sname!='王宁';
王宁熟练掌握了嵌套查询的使用方法，对于提出的问题：查询与王
宁在同一个系的学生信息，王宁给出的实现代码如下。
MySQL数据库原理及应用（微课版|第4版）
【任务提出】
在学生信息管理系统中，随着每年新生入学，都需要增加学生基本信息、
班级基本信息。随着学校新专业的开设，还需要增加课程基本信息等，这
就是表中数据记录的插入。如果学生的基本信息发生变化，或者某课程的
开课学期发生变化等，都需要及时进行数据记录的修改。随着对数据的使
用和修改，表中可能会存在一些无用的或过期的数据。这些无用的数据不
仅会占用空间，还会影响修改和查询的速度，所以应该及时删除。
任务5-4 数据更新
MySQL数据库原理及应用（微课版|第4版）
• 语句格式
（一）数据记录的插入
功能
INSERT INTO<表名>[(<列名清单>)]
VALUES(<常量清单>);
向指定表中插入一条新记录。
1、插入单条记录
MySQL数据库原理及应用（微课版|第4版）
• 说明
① 若有<列名清单>，则<常量清单>中各常量为新记录
中这些属性的对应值（根据语句中的位置一一对应）。
但该表在定义时，说明为NOT NULL且无默认值的列必
须在<列名清单>中，否则将出错。
② 如果省略<列名清单>，则按<常量清单>顺序为每个
属性列赋值，即每个属性列上都应该有值。
（一）数据记录的插入
MySQL数据库原理及应用（微课版|第4版）
【例】 向student表中插入一个学生记录。
【例】 向student表中添加一条记录。
INSERT INTO student
VALUES('2002010101','胡一兵','男','1980-12-30',
'计算机工程系');
INSERT INTO student(sno,sname)
VALUES ('2005010104','张三');
（一）数据记录的插入
MySQL数据库原理及应用（微课版|第4版）
• 语句格式
功能
INSERT INTO<表名>[(<列名清单>)]
VALUES(<常量清单1>),(<常量清单2>),…, (<常量清单n>);
向指定表中插入多条新记录。
2、插入多条记录
（一）数据记录的插入
MySQL数据库原理及应用（微课版|第4版）
【例】向sc表中连续插入3条记录，可用下列语句实现。
INSERT INTO sc
VALUES('2005020202', 'C01',78),
('2005020202', 'C02',91),
('2005020202', 'C03', 83);
（一）数据记录的插入
MySQL数据库原理及应用（微课版|第4版）
子查询不仅可以嵌套在SELECT语句中，用以构造主查
询的条件，还可以嵌套在INSERT语句中，用以生成要插
入的批量数据。
（一）数据记录的插入
3、插入子查询的结果
（一）数据记录的插入
语句格式
INSERT INTO <表名>[(列名1,列名2，…)]<子查询语句>;
MySQL数据库原理及应用（微课版|第4版）
（一）数据记录的插入
INSERT INTO S_GRADE(SNO,AVG_GRADE)
SELECT sno,AVG(degree)
FROM sc
GROUP BY sno
HAVING AVG(degree)>80;
【例】把平均成绩大于80分的学生的学号和平均成绩存入另
一个已知的基本表S_GRADE(SNO，AVG_GRADE)中。
（一）数据记录的插入
MySQL数据库原理及应用（微课版|第4版）
• 语句格式
功能
UPDATE <表名>
SET<列名1>=<表达式1>［,<列名2>=<表达式2>］[,…]
［WHERE<条件表达式>］;
把指定<表名>内符合<条件表达式>的记录中规定<列名>
的值更新为该<列名>后<表达式>的值。如果省略WHERE
子句，则表示要修改表中的所有记录。
（二）数据记录的修改
MySQL数据库原理及应用（微课版|第4版）
（二）数据记录的修改
【例】将sc表中不及格的成绩修改为60分。
UPDATE sc SET degree=60
WHERE degree<60;
【例】 将计算机工程系全体学生的成绩置0。
或者：
UPDATE sc SET degree=0
WHERE sno IN (SELECT sno
FROM student
WHERE sdept='计算机工程系');
UPDATE sc SET degree=0
WHERE (SELECT sdept
FROM student
WHERE =)='计算机工程系';
MySQL数据库原理及应用（微课版|第4版）
• 语句格式
功能
DELETE [FROM] <表名>[WHERE<条件表达式>];
在指定<表名>中删除所有符合<条件表达式>的记录。
（三）数据记录的删除
MySQL数据库原理及应用（微课版|第4版）
• 说明
①当无WHERE<条件表达式>项时，将删除<表名
>中的所有记录。但是该表的表结构还在，只是没有
了记录，是个空表而已。
② DELETE语句只能从一个基本表中删除记录。
WHERE子句中条件表达式可以嵌套，也可以是来自
几个基本表的复合条件。
（三）数据记录的删除
MySQL数据库原理及应用（微课版|第4版）
【例】 删除学号为2005030301的学生记录。
【例】 删除学生的所有成绩。
DELETE FROM student
WHERE sno='2005030301';
DELETE FROM sc;
DELETE FROM sc
WHERE sno IN(SELECT sno
FROM student
WHERE sdept='计算机工程系');
【例】删除计算机工程系所有学生的成绩。
（三）数据记录的删除
MySQL数据库原理及应用（微课版|第4版）
【任务实施】
王宁学会了数据的插入、修改、删除的操作方法，并写出以下代码。
（1）将班级新转来的王丽同学的基本信息加入到班级表中。
INSERT INTO student
VALUES('2020010120', '王丽', '女', '1998-10-12', '山东省济南市',
'1390536XXXX', '计算机工程系', '20200101');
（2）将张芳同学的性别改为女。
UPDATE student
SET ssex='女'
WHERE sname='张芳';
任务5-4 数据更新
MySQL数据库原理及应用（微课版|第4版）
项目总结
本项目内容主要带领大家学习了数据查询和数据更新的基本操作，包
括单表查询、多表连接查询、嵌套查询和数据的插入、修改、删除。
主要内容
单表查询部分主要学习SELECT语句的各个子句的用法及各关键
字的使用。
多表连接查询重点理解掌握内连接和自连接的使用。
嵌套查询重点掌握不相关子查询的写法，理解其执行过程。
数据更新重点掌握INSERT、UPDATE、DELETE语句的使用。
通过本项目的学习，应该能够灵活运用SELECT命令实现各种条
件的数据查询。
重难点要求
MySQL数据库原理及应用（微课版|第4版）
志存高远 自强不息
