跳到主要内容

第5章 MySQL多表查询

多表查询与连接​

1. 多表连接基础​

数据准备​

mysql -uroot -proot < school.sql

笛卡尔乘积​

笛卡尔乘积是两个表的所有行组合，结果集大部分是无意义的：

-- 两种写法等价
SELECT * FROM teacher, course;
SELECT * FROM teacher JOIN course;

内连接（INNER JOIN）​

内连接返回两个表中匹配的行：

-- JOIN ON语法（推荐）
SELECT * FROM teacher JOIN course ON teacher.tno = course.tno;

-- WHERE语法
SELECT * FROM teacher, course WHERE teacher.tno = course.tno;

结果只包含两表都有的数据：

+-----+--------+------+--------+-----+
| tno | tname  | cno  | cname  | tno |
+-----+--------+------+--------+-----+
| 101 | oldboy | 1001 | linux  | 101 |
| 102 | hesw   | 1002 | python | 102 |
| 103 | oldguo | 1003 | mysql  | 103 |
+-----+--------+------+--------+-----+

外连接（LEFT/RIGHT JOIN）​

左外连接：保留左表所有行，右表无匹配填充NULL

SELECT * FROM teacher LEFT JOIN course ON teacher.tno = course.tno;

结果包含所有老师（包括未授课的）：

+-----+--------+------+--------+------+
| tno | tname  | cno  | cname  | tno  |
+-----+--------+------+--------+------+
| 101 | oldboy | 1001 | linux  |  101 |
| 102 | hesw   | 1002 | python |  102 |
| 103 | oldguo | 1003 | mysql  |  103 |
| 104 | oldx   | NULL | NULL   | NULL |
| 105 | oldw   | NULL | NULL   | NULL |
+-----+--------+------+--------+------+

右外连接：保留右表所有行，左表无匹配填充NULL

SELECT * FROM teacher RIGHT JOIN course ON teacher.tno = course.tno;

2. 多表连接语法与实战​

直接关联语法​

SELECT a.x, b.y
FROM a
JOIN b ON a.z = b.z
WHERE 条件
GROUP BY 分组列
HAVING 分组条件
ORDER BY 排序列
LIMIT 行数;

间接关联语法​

当两表无直接关系时，通过中间表连接：

-- a和c有关，b和c有关
SELECT *
FROM a
JOIN c ON a.i = c.j
JOIN b ON c.x = b.y;

多表查询步骤​

  1. 确定涉及的表
  2. 找出表间关联条件
  3. 使用JOIN ON连接
  4. 添加其他查询条件

实战练习​

1717331876992-3b3afde1-e2c7-438c-b39f-80595e6bd5be.webp

练习1：每位老师所教课程

SELECT teacher.tname, course.cname
FROM teacher
JOIN course ON teacher.tno = course.tno;

练习2：学生学习课程统计

-- 统计每个学员学习门数
SELECT student.sname AS '学生姓名', COUNT(*) AS '学习门数'
FROM student
JOIN sc ON student.sno = sc.sno
GROUP BY student.sno;

练习3：多表连接统计

关系链：student -> sc -> course

-- 统计学生、课程门数及课程列表
SELECT 
    CONCAT(student.sname, '_', student.sno) AS '学生信息',
    COUNT(*) AS '课程数',
    GROUP_CONCAT(course.cname) AS '课程列表'
FROM student
JOIN sc ON student.sno = sc.sno
JOIN course ON sc.cno = course.cno
GROUP BY student.sno;
提示

使用CONCAT函数组合学生姓名和学号，避免同名学生统计错误。

练习4：老师教学统计

关系链：teacher -> course -> sc -> student

-- 统计每位老师的学生数量和学生列表
SELECT 
    CONCAT(teacher.tname, '_', teacher.tno) AS '老师信息',
    COUNT(*) AS '学生数',
    GROUP_CONCAT(student.sname) AS '学生列表'
FROM teacher
JOIN course ON teacher.tno = course.tno
JOIN sc ON course.cno = sc.cno
JOIN student ON sc.sno = student.sno
GROUP BY teacher.tno;

-- 统计每位老师每门课的平均分
SELECT 
    CONCAT(teacher.tname, '_', course.cname) AS '老师课程',
    AVG(sc.score) AS '平均分'
FROM teacher
JOIN course ON teacher.tno = course.tno
JOIN sc ON course.cno = sc.cno
GROUP BY teacher.tno, course.cno;

练习5：复杂条件查询

查找学习了hesw但没学习oldguo课程的学生：

方法1：子查询+左连接

SELECT a.sname 
FROM (
    -- 学习hesw课程的学生
    SELECT student.sname
    FROM teacher
    JOIN course ON teacher.tno = course.tno
    JOIN sc ON course.cno = sc.cno
    JOIN student ON sc.sno = student.sno
    WHERE teacher.tname = 'hesw'
) AS a
LEFT JOIN (
    -- 学习oldguo课程的学生
    SELECT student.sname
    FROM teacher
    JOIN course ON teacher.tno = course.tno
    JOIN sc ON course.cno = sc.cno
    JOIN student ON sc.sno = student.sno
    WHERE teacher.tname = 'oldguo'
) AS b ON a.sname = b.sname
WHERE b.sname IS NULL;

方法2：GROUP BY + HAVING

SELECT student.sname
FROM student
JOIN sc ON student.sno = sc.sno
JOIN course ON sc.cno = course.cno
JOIN teacher ON course.tno = teacher.tno
GROUP BY student.sname
HAVING GROUP_CONCAT(teacher.tname) LIKE '%hesw%' 
   AND GROUP_CONCAT(teacher.tname) NOT LIKE '%oldguo%';

练习6：更多统计查询

-- 只选修一门课的学生
SELECT student.sno, student.sname, COUNT(*) AS '选课数'
FROM sc
JOIN student ON sc.sno = student.sno
GROUP BY sc.sno
HAVING COUNT(*) = 1;

-- 各科成绩最高最低分
SELECT 
    course.cname AS '课程名称',
    MAX(sc.score) AS '最高分',
    MIN(sc.score) AS '最低分'
FROM sc
JOIN course ON sc.cno = course.cno
GROUP BY course.cname;

-- 平均成绩大于85分的学生
SELECT 
    student.sno,
    student.sname,
    AVG(sc.score) AS '平均成绩'
FROM sc
JOIN student ON sc.sno = student.sno
GROUP BY sc.sno
HAVING AVG(sc.score) > 85;

练习7：CASE WHEN分析

统计每门课程各分数段学生：

SELECT 
    a.cname AS '课程名称',
    GROUP_CONCAT(CASE WHEN b.score >= 85 THEN c.sname END) AS '优秀(≥85)',
    GROUP_CONCAT(CASE WHEN b.score >= 70 AND b.score < 85 THEN c.sname END) AS '良好(70-84)',
    GROUP_CONCAT(CASE WHEN b.score >= 60 AND b.score < 70 THEN c.sname END) AS '一般(60-69)',
    GROUP_CONCAT(CASE WHEN b.score < 60 THEN c.sname END) AS '不及格(<60)'
FROM course AS a
JOIN sc AS b ON a.cno = b.cno
JOIN student AS c ON b.sno = c.sno
GROUP BY a.cno;
提示

使用表别名（a, b, c）可以简化复杂查询的编写，但应保持别名有意义。

3. 元数据查询（show语句集合）​

常用SHOW命令​

数据库和表信息：

SHOW DATABASES;                    -- 查询所有库
SHOW TABLES;                       -- 查询当前库的表
SHOW TABLES FROM world;            -- 查询指定库的表
SHOW CREATE DATABASE world;        -- 查看建库语句
SHOW CREATE TABLE city;            -- 查看建表语句

系统信息：

SHOW PRIVILEGES;                   -- 所有权限类型
SHOW ENGINES;                      -- 支持的存储引擎
SHOW CHARSET;                      -- 字符集支持
SHOW COLLATION;                    -- 校对规则

运行状态：

SHOW VARIABLES LIKE '%buffer%';    -- 查询系统变量
SHOW STATUS LIKE 'com_%';          -- 查询状态信息
SHOW PROCESSLIST;                  -- 当前会话列表
SHOW ENGINE INNODB STATUS;         -- InnoDB引擎状态

复制相关：

SHOW BINARY LOGS;                  -- 二进制日志列表
SHOW MASTER STATUS;                -- 主库状态
SHOW SLAVE STATUS\G                -- 从库状态
SHOW SLAVE HOSTS;                  -- 从库主机信息
注意

SHOW PROCESSLIST可能显示敏感信息，生产环境要谨慎使用。

更新: 2024-07-13 08:48:08

最后于 更新