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

连接查询

选课表和授课表中只有编号。把学生表、课程表、教师表连接进来，查出姓名和课程名。

环　境

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

执　行

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

注　意

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

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

  1. 实验 3-1两表连接
  2. 实验 3-2三表连接
  3. 实验 3-3连接后分组
  4. 实验 3-4外连接
  5. 实验 3-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');
3-1

两表连接

讲义 · 连接方式讲义 · 内连接
目的
用两种写法把学生表和选课表连接起来。
前提
已执行数据准备。
  1. 查询 s5 的学号、姓名和所选的课程号，连接条件写在 WHERE 中
  2. 同一个查询，改用 INNER JOIN … ON 来写

参考答案

可以直接复制到 Workbench 执行
-- 写法一：两张表用逗号并列，连接条件写在 WHERE 中
SELECT s.sno, sn, cno
FROM s, sc
WHERE s.sno = sc.sno AND s.sno = 's5';

-- 写法二：用 INNER JOIN 连接，连接条件写在 ON 后面
SELECT s.sno, sn, cno
FROM s INNER JOIN sc ON s.sno = sc.sno
WHERE s.sno = 's5';

语句解释

s.sno = sc.sno连接条件：学生表的学号等于选课表的学号
s.sno两张表都有 sno 字段，要写表名作前缀，只写 sno 会报 1052
sn　cno只有一张表中有的字段，可以不写前缀
执行后看：两句结果相同：s5 魏立，选了 c2 和 c7。
3-2

三表连接

讲义 · 三表连接
目的
连接三张表，把教师号和课程号换成姓名和课程名。
前提
已完成实验 3-1。
  1. 查询每位教师讲授的课程：教师号、教师姓名和课程名，按教师号排序

参考答案

可以直接复制到 Workbench 执行
-- 教师表连授课表，授课表再连课程表，需要两个连接条件
SELECT t.tno, tn, cn
FROM t
  INNER JOIN tc ON t.tno = tc.tno
  INNER JOIN c ON tc.cno = c.cno
ORDER BY t.tno;

语句解释

t 与 c 通过 tc 连接授课表 tc 中既有教师号，又有课程号
两个 ON连接三张表需要两个连接条件
ORDER BY t.tno按教师号排序
执行后看：返回 9 行，每行是一位教师讲授的一门课。t6 张刚没有授课，结果中没有他。
3-3

连接后分组

讲义 · 连接与分组
目的
先连接，再分组统计，并用 HAVING 筛选。
前提
已完成实验 3-2。
  1. 查询选课人数在 3 人及以上的课程的课程号、课程名和选课人数

参考答案

可以直接复制到 Workbench 执行
SELECT c.cno, cn, COUNT(sc.sno) AS 选课人数
FROM c INNER JOIN sc ON c.cno = sc.cno
GROUP BY c.cno, cn
HAVING COUNT(sc.sno) >= 3;

语句解释

COUNT(sc.sno)统计每门课的选课人数
GROUP BY c.cno, cncn 出现在 SELECT 中，也要写进 GROUP BY
HAVING COUNT(sc.sno) >= 3只保留选课人数不少于 3 的课程
执行后看：返回 3 门课：Java程序设计 3 人、程序设计基础 5 人、高等数学 4 人。和实验 2-2 相比，这里多了课程名。
3-4

外连接

讲义 · 外连接讲义 · 左与右
目的
用左外连接保留主表的全部行，找出没有匹配的记录。
前提
已完成实验 3-3。
  1. 查询所有教师的教师号、姓名和讲授的课程名，没有授课的教师也要列出
  2. 只列出没有授课的教师

参考答案

可以直接复制到 Workbench 执行
-- 教师表是主表，写在 LEFT JOIN 的左边
SELECT t.tno, tn, cn
FROM t
  LEFT JOIN tc ON t.tno = tc.tno
  LEFT JOIN c ON tc.cno = c.cno
ORDER BY t.tno;

-- 授课表中没有对应记录的，tc.tno 为 NULL
SELECT t.tno, tn
FROM t LEFT JOIN tc ON t.tno = tc.tno
WHERE tc.tno IS NULL;

语句解释

LEFT JOIN保留左表 t 的全部行，右边匹配不上的字段填 NULL
tc.tno IS NULL右表的字段为 NULL，说明这位教师在授课表中没有记录
执行后看：第一句 10 行，比实验 3-2 多一行：t6 张刚，课程名为 NULL。第二句只有张刚。
3-5

自连接

讲义 · 自连接
目的
给同一张表起两个别名，比较表中的两行。
前提
已完成实验 3-4。
  1. 查询与 王彤 专业相同的学生的学号和姓名
  2. 在第 1 题的结果中去掉王彤本人

参考答案

可以直接复制到 Workbench 执行
SELECT x.sno, x.sn
FROM s AS x INNER JOIN s AS y ON x.maj = y.maj
WHERE y.sn = '王彤';

-- 再加一个条件，排除王彤本人
SELECT x.sno, x.sn
FROM s AS x INNER JOIN s AS y ON x.maj = y.maj
WHERE y.sn = '王彤' AND x.sno <> y.sno;

语句解释

s AS x　s AS y同一张学生表起两个别名，当作两张表使用
y.sn = '王彤'在 y 中找到王彤
x.maj = y.maj在 x 中找出专业和她相同的学生
x.sno <> y.sno学号不同，排除王彤本人
执行后看：第一句 2 人：王彤、何欣荣。第二句只剩何欣荣。
保山学院·人工智能教研室·曹鼎鼎