课程 › 第 6 章 › 实验四
第 6 章 · 实验四 · 在第六节之后

子查询与集合运算

把一个查询的结果作为另一个查询的条件，用 UNION 合并查询结果，最后在 INSERT 和 UPDATE 中使用子查询。

环　境

MySQL 8.0 和 MySQL Workbench，机房电脑已装好。所有语句在 Workbench 的 SQL 编辑区中执行。

执　行

Ctrl+Enter 执行光标所在的一句，Ctrl+Shift+Enter 执行全部。Mac 上把 Ctrl 换成 ⌘。

注　意

输入法切到英文。表名、字段名不加引号，字符串值才用单引号。

本次实验的顺序　后一个实验用到前一个的结果

  1. 实验 4-1返回一个值
  2. 实验 4-2返回一组值
  3. 实验 4-3相关子查询与 EXISTS
  4. 实验 4-4集合运算
  5. 实验 4-5用于数据操纵
第一步

数据准备（必做）

与实验一是同一份数据。之前没有修改过数据可以不再执行，不确定就再执行一次。

整段复制，全部执行下载 .sql
-- 第 6 章实验数据：教材 teaching 库的五张表和教材中的数据
-- 四次实验共用这一份数据。整段执行一次即可，数据改乱了重新执行就能恢复。
CREATE DATABASE IF NOT EXISTS teaching DEFAULT CHARACTER SET utf8mb4;
USE teaching;

-- 删除旧的表。先暂时关闭外键检查，库里有别的表引用它们时也能删除
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS sc, tc, avgsal, s, c, t;
SET FOREIGN_KEY_CHECKS = 1;

-- 课程表
CREATE TABLE c (
  cno CHAR(4) NOT NULL,
  cn VARCHAR(30) NOT NULL,
  ct INT,
  PRIMARY KEY (cno)
);
-- 学生表
CREATE TABLE s (
  sno CHAR(4) NOT NULL,
  sn VARCHAR(20) NOT NULL,
  sex CHAR(2),
  age INT,
  maj VARCHAR(30),
  dept VARCHAR(30),
  PRIMARY KEY (sno)
);
-- 教师表
CREATE TABLE t (
  tno CHAR(4) NOT NULL,
  tn VARCHAR(20) NOT NULL,
  sex CHAR(2),
  age INT,
  prof VARCHAR(20),
  maj VARCHAR(30),
  dept VARCHAR(30),
  sal DECIMAL(8,2),
  PRIMARY KEY (tno)
);
-- 选课表
CREATE TABLE sc (
  sno CHAR(4) NOT NULL,
  cno CHAR(4) NOT NULL,
  score DECIMAL(5,2),
  PRIMARY KEY (sno, cno),
  FOREIGN KEY (sno) REFERENCES s (sno),
  FOREIGN KEY (cno) REFERENCES c (cno)
);
-- 授课表
CREATE TABLE tc (
  tno CHAR(4) NOT NULL,
  cno CHAR(4) NOT NULL,
  tcdate DATE,
  PRIMARY KEY (tno, cno),
  FOREIGN KEY (tno) REFERENCES t (tno),
  FOREIGN KEY (cno) REFERENCES c (cno)
);

INSERT INTO c VALUES
('c1','Java程序设计',40), ('c2','程序设计基础',48), ('c3','线性代数',48),
('c4','数据结构',64), ('c5','数据库系统',56), ('c6','数据挖掘',32),
('c7','高等数学',60), ('c8','控制理论',32);

INSERT INTO s VALUES
('s1','王彤','女',18,'计算机','信息学院'),
('s2','苏乐','男',20,'信息','信息学院'),
('s3','林欣怡','女',19,'信息','信息学院'),
('s4','陶然','男',19,'自动化','工学院'),
('s5','魏立','男',17,'数学','理学院'),
('s6','何欣荣','女',21,'计算机','信息学院'),
('s7','赵琳琳','女',19,'数学','理学院'),
('s8','李轩','男',18,'自动化','工学院');

INSERT INTO t VALUES
('t1','刘杨','男',38,'教授','计算机','信息学院',3800.00),
('t2','石丽','女',28,'讲师','计算机','信息学院',2800.00),
('t3','顾伟','男',35,'副教授','计算机','信息学院',3200.00),
('t4','赵礼','男',45,'教授','自动化','工学院',4267.90),
('t5','赵希希','女',33,'副教授','数学','理学院',3332.67),
('t6','张刚','男',30,'讲师','计算机','工学院',2600.00);

INSERT INTO sc VALUES
('s1','c1',90.50), ('s1','c2',88.00),
('s2','c4',70.00), ('s2','c5',57.00), ('s2','c6',81.50), ('s2','c7',NULL),
('s3','c1',75.00), ('s3','c2',76.50), ('s3','c4',85.00),
('s4','c1',93.00), ('s4','c2',82.00), ('s4','c6',NULL), ('s4','c7',68.00),
('s5','c2',79.00), ('s5','c7',84.00),
('s7','c3',77.00), ('s7','c5',62.00), ('s7','c7',100.00),
('s8','c2',66.00), ('s8','c3',96.00);

INSERT INTO tc VALUES
('t1','c1','2021-09-03'), ('t1','c2','2021-09-04'),
('t2','c5','2021-03-06'), ('t2','c6','2021-09-06'),
('t3','c2','2021-03-08'), ('t3','c4','2021-09-08'),
('t4','c3','2021-03-10'),
('t5','c7','2021-09-12'), ('t5','c8','2021-03-15');
4-1

返回一个值的子查询

讲义 · 一个值讲义 · 由名查号
目的
把一个查询的结果作为另一个查询的比较值。
前提
已执行数据准备。
  1. 先单独查询 赵琳琳 的年龄
  2. 查询比赵琳琳年龄大的学生的学号、姓名和年龄
  3. 查询讲授 程序设计基础 的教师号和开课日期，用子查询由课程名查出课程号

参考答案

可以直接复制到 Workbench 执行
-- 先单独执行子查询，看它返回什么
SELECT age FROM s WHERE sn = '赵琳琳';

-- 把子查询放进括号，作为比较的值
SELECT sno, sn, age
FROM s
WHERE age > (SELECT age FROM s WHERE sn = '赵琳琳');

SELECT tno, cno, tcdate
FROM tc
WHERE cno = (SELECT cno FROM c WHERE cn = '程序设计基础');

语句解释

(SELECT age FROM s …)子查询先执行一次，得到 19，外层相当于 age > 19
cno = (SELECT cno FROM c …)子查询由课程名查出课程号 c2，外层再查授课记录
用 = 或 > 接子查询子查询只能返回一行，返回多行会报 1242
执行后看：赵琳琳 19 岁，比她大的 2 人：s2 苏乐 20 岁、s6 何欣荣 21 岁。讲授程序设计基础的是 t1 和 t3。
4-2

返回一组值的子查询

讲义 · IN 子查询讲义 · ANY 与 ALL
目的
子查询返回多个值时，用 IN、ANY、ALL 进行比较。
前提
已完成实验 4-1。
  1. 用 IN 查询选修了 c1 的学生的学号和姓名
  2. 用 ANY 查询其他专业中，比计算机专业某一位教师工资高的教师
  3. 把第 2 题的 ANY 改为 ALL，比较两次的结果

参考答案

可以直接复制到 Workbench 执行
-- 子查询返回 s1、s3、s4 三个学号
SELECT sno, sn FROM s
WHERE sno IN (SELECT sno FROM sc WHERE cno = 'c1');

-- 比计算机专业中最低的工资高即可
SELECT tno, tn, maj, sal FROM t
WHERE sal > ANY (SELECT sal FROM t WHERE maj = '计算机')
  AND maj <> '计算机';

-- 要比计算机专业中最高的工资还高
SELECT tno, tn, maj, sal FROM t
WHERE sal > ALL (SELECT sal FROM t WHERE maj = '计算机')
  AND maj <> '计算机';

语句解释

IN (子查询)等于子查询结果中的任意一个值
> ANY (子查询)大于其中任意一个，也就是大于最小值 2600.00
> ALL (子查询)大于其中每一个，也就是大于最大值 3800.00
maj <> '计算机'只看其他专业的教师
执行后看：第一句返回 s1、s3、s4。第二句返回 t4 赵礼、t5 赵希希。第三句只剩 t4 赵礼。
4-3

相关子查询与 EXISTS

讲义 · 相关子查询讲义 · EXISTS
目的
写出引用外层字段的子查询，用 EXISTS 和 NOT EXISTS 判断有没有记录。
前提
已完成实验 4-2。
  1. 用 EXISTS 查询选修了 c1 的学生
  2. 用 NOT EXISTS 查询没有选修 c1 的学生
  3. 查询一门课也没有选的学生

参考答案

可以直接复制到 Workbench 执行
-- 子查询中的 s.sno 来自外层，外层每取一行执行一次子查询
SELECT sno, sn FROM s
WHERE EXISTS (SELECT * FROM sc WHERE sc.sno = s.sno AND cno = 'c1');

SELECT sno, sn FROM s
WHERE NOT EXISTS (SELECT * FROM sc WHERE sc.sno = s.sno AND cno = 'c1');

-- 在选课表中一条记录也没有
SELECT sno, sn FROM s
WHERE NOT EXISTS (SELECT * FROM sc WHERE sc.sno = s.sno);

语句解释

sc.sno = s.sno用到了外层学生表的学号，这是相关子查询
EXISTS (子查询)子查询查出至少一行时为真
NOT EXISTS (子查询)子查询一行也查不出时为真
执行后看：第一句和实验 4-2 第一句的结果相同。第二句返回其余 5 名学生。第三句只有 s6 何欣荣。
4-4

集合运算

讲义 · UNION讲义 · UNION 分组
目的
用 UNION 把两个查询的结果合并成一个。
前提
已完成实验 4-3。
  1. 分别查询计算机专业和数学专业的学生，用 UNION 合并
  2. 分别统计 c1 和 c2 两门课的总分和平均分，用 UNION 合并

参考答案

可以直接复制到 Workbench 执行
SELECT * FROM s WHERE maj = '计算机'
UNION
SELECT * FROM s WHERE maj = '数学';

-- 两个查询各自分组统计，分号只写在整条语句的最后
SELECT cno AS 课程号, SUM(score) AS 总分, AVG(score) AS 平均分
FROM sc WHERE cno = 'c1' GROUP BY cno
UNION
SELECT cno, SUM(score), AVG(score)
FROM sc WHERE cno = 'c2' GROUP BY cno;

语句解释

UNION把上下两个查询的结果合成一个，并去掉重复的行
列数相同两个查询的列数和对应列的类型要相同
列名结果的列名取第一个查询的，第二个查询不必再写别名
执行后看：第一句 4 名学生，与实验 1-3 用 OR 查出的结果相同。第二句 c1 总分 258.50，c2 总分 391.50。
4-5

子查询用于数据操纵

讲义 · INSERT 子查询讲义 · UPDATE 子查询
目的
在 INSERT 和 UPDATE 中使用子查询。这个实验会修改数据，所以放在最后。
前提
已完成实验 4-4。
  1. 新建表 avgsal，有院系 department 和平均工资 average 两个字段，把每个院系教师的平均工资存进去
  2. 把 t1 讲授的课程的课时增加 16

参考答案

可以直接复制到 Workbench 执行
-- 新建存放结果的表
DROP TABLE IF EXISTS avgsal;
CREATE TABLE avgsal (department VARCHAR(20), average SMALLINT);

-- INSERT 后面直接接 SELECT，不写 VALUES
INSERT INTO avgsal SELECT dept, AVG(sal) FROM t GROUP BY dept;
SELECT * FROM avgsal;

-- 子查询先查出 t1 讲授的课程号
UPDATE c SET ct = ct + 16
WHERE cno IN (SELECT cno FROM tc WHERE tno = 't1');
SELECT * FROM c;

语句解释

INSERT INTO avgsal SELECT …把查询结果直接插入表中，列的个数和顺序要与表对应
average SMALLINT整数类型，平均工资的小数部分被四舍五入，如信息学院是 3267
WHERE cno IN (子查询)只修改 t1 讲授的 c1、c2 两门课
执行后看：avgsal 中有 3 行。c1 的课时由 40 变为 56，c2 由 48 变为 64。想恢复原来的数据，重新执行数据准备脚本。
保山学院·人工智能教研室·曹鼎鼎