一、关联查询

1、关联关系

（1）关联关系概述

关联关系三种：

  • 一对一关系：一个实体仅仅关联一个实体。
  • 一对多关系：一个实体可以关联多个实体。关系在多的一方
  • 多对多关系：多对多关系是指多个实体之间是多对多的关系，需要建立一个中间表进行连接。例如，一个学生可能同时选修多个课程，而一个课程也可能被多个学生选修，需要建立一个中间表来记录学生与课程之间的关联关系。

（2）笛卡尔积

笛卡尔积是指两个集合中所有可能的组合，如果两个表一起查，不加以限制，那么得到的结果就是笛卡尔积。

笛卡尔积

2、关联查询

关联查询的目的是解决笛卡尔积。

（1）过滤笛卡尔积

SELECT *
FROM teacher,
     subject
WHERE teacher.subject_id = subject.id;

（2）使用别名简化SQL

SELECT *
FROM teacher t,
     subject s
WHERE t.subject_id = s.id;

（3）设计关联查询

关联关系的最少数量 = 查询的表的数量 - 1

关联查询步骤：

1、首先确定数据的来源表，将表写到 FROM 子句中，可以适当设定别名，方便简写SQL；

2、再找到两个表有关系的属性，作为连接条件，将连接条件写到WHERE子句中，这样就可以获得有意义的查询结果集。

-- 关联查询
-- 查看班级的名称和对应的班主任(老师)是谁？
SELECT t.name,c.name
FROM teacher t,
     class c
WHERE c.teacher_id = t.id;

-- 查看每个学生的名字，年龄，以及其所在的班级名称和所在楼层。
SELECT s.name,s.age,c.name,c.floor
FROM student s,
     class c
WHERE s.class_id = c.id;

（4）关联查询中使用过滤条件

-- 查看姓李的学生的名字，年龄，以及其所在的班级名称和所在楼层。
SELECT s.name, s.age, c.name, c.floor
FROM student s,
     class c
WHERE s.class_id = c.id
  AND s.name LIKE '李%';
  
-- 王克晶是哪个班的班主任？
SELECT t.name, t.salary, c.name, c.floor
FROM teacher t,
     class c
WHERE c.teacher_id = t.id
  AND t.name = '王克晶';

-- 查看三年级的班级班主任都是谁？要列出班级名称，所在楼层，班主任名字和工资
SELECT c.name, c.floor, t.name, t.salary
FROM teacher t,
     class c
WHERE c.teacher_id = t.id
  AND c.name LIKE '3年级%';
  
-- 查看来自南京的学生都有谁？要列出城市名字，学生名字，年龄，性别
SELECT l.name, s.name, s.age, s.gender
FROM student s,
     location l
WHERE l.id = s.location_id
  AND l.name = '南京';

-- 查看5年级的中队长都有谁？要列出学生名字，年龄，性别，职位和所在班级的名字以及楼层
SELECT s.name, s.age, s.gender, s.job, c.name, c.floor
FROM student s,
     class c
WHERE s.class_id = c.id
  AND c.name LIKE '5年级%'
  AND s.job = '中队长';

二、连接查询

1、内连接

（1）什么是连接查询

连接查询（Join）是一种SQL查询方式，用于将两个或多个表格中的数据联接在一起。

连接查询主要包括：

  • 内连接（Inner Join）
  • 外连接（Outer Join）
  • 全连接（Full Join）

连接查询

-- 关联查询 用关联关系
-- 查询5年级的所有中队长
SELECT s.name, s.age, s.gender, s.job, c.name, c.floor
FROM student s,
     class c
WHERE s.class_id = c.id
  AND c.name LIKE '5年级%'
  AND S.job = '中队长'
  
-- 使用连接查询完成操作，解决关联关系和查询条件混在一起的问题
SELECT s.name, s.age, s.gender, s.job, c.name, c.floor
FROM student s
         JOIN class c ON s.class_id = c.id
WHERE s.job = '中队长'
  AND c.name LIKE '5年级%'

（2）内连接查询INNER JOIN

用于将两个或多个表格中的数据进行联接，只返回那些在两个表中都有匹配的行。

SELECT column_names
FROM left_table
[INNER] JOIN right_table
ON left_table.column_name = right_table.column_name;
  • left_table 和 right_table 是要连接的两个表格的名称
  • column_names 是表格中要选择的列的名称
  • JOIN 是关键字，[INNER] 可以省略不写
  • ON 是连接条件
-- 王克晶是哪个班的班主任？列出：班级名称，楼层，老师名称，工资
SELECT c.name, c.floor, t.name, t.salary
FROM teacher t
         JOIN class c on t.id = c.teacher_id
WHERE t.name = '王克晶'

（3）多表连接查询

  • 先确定第一个数据来源表，并将其作为左表，写在FROM子句中
  • 找到第二个数据来源表作为右表，写在JOIN子句中
  • 编写第一个表和第二个表的连接条件并写在ON子句中
  • 将前两个表连接后的结果作为新的左表，继续找到下一个数据来源表作为右表，写在JOIN子句中
  • 编写下一个连接条件的ON子句，写在JOIN子句后面
  • 最后编写WHERE过滤条件
-- 查询"范传奇"所带班级的学生姓名、年龄、班级名称及老师名字。
SELECT s.name, s.age, c.name, t.name
FROM teacher t
         JOIN class c on t.id = c.teacher_id
         JOIN student s on c.id = s.class_id
WHERE t.name = '范传奇';

-- 查看1年级1班的同学的名字和来自的城市
SELECT c.name, s.name, l.name
FROM student s
         JOIN class c on c.id = s.class_id
         JOIN location l on l.id = s.location_id
WHERE c.name = '1年级1班';

（4）多表连接查询练习

-- 查看来自北京的学生都是谁？
SELECT s.name, l.name
FROM student s
         JOIN location l on l.id = s.location_id
WHERE l.name = '北京';

-- 教"英语"的老师都是谁？
SELECT t.name, s.name
FROM teacher t
         JOIN subject s on s.id = t.subject_id
WHERE s.name = '英语';

-- 刘苍松所带班级的学生都有谁？
SELECT t.name, s.name
FROM student s
         JOIN class c on c.id = s.class_id
         JOIN teacher t on t.id = c.teacher_id
WHERE t.name = '刘苍松';

-- 教语文的老师所带的班级有哪些？
SELECT t.name, s.name, c.name
FROM teacher t
         JOIN subject s on s.id = t.subject_id
         JOIN class c on t.id = c.teacher_id
WHERE s.name = '语文';

-- 王克晶所带的班级学生都来自哪些城市（去重）？
SELECT DISTINCT l.name
FROM teacher t
         JOIN class c on t.id = c.teacher_id
         JOIN student s on c.id = s.class_id
         JOIN location l on l.id = s.location_id
WHERE t.name = '王克晶';

-- 3年级的几个班主任都教哪些课程？
SELECT t.name, s.name, c.name
FROM class c
         JOIN teacher t on t.id = c.teacher_id
         JOIN subject s on s.id = t.subject_id
WHERE c.name LIKE '3年级%';

-- 工资高于10000的老师所带班里的大队长都是谁？
SELECT t.name, t.salary, s.name, s.job
FROM teacher t
         JOIN class c on t.id = c.teacher_id
         JOIN student s on c.id = s.class_id
WHERE t.salary > 10000
  AND s.job = '大队长';

-- "李费水"的班主任教哪门课？
SELECT t.name, s2.name
FROM student s
         JOIN class c on c.id = s.class_id
         JOIN teacher t on t.id = c.teacher_id
         JOIN subject s2 on t.subject_id = s2.id
WHERE s.name = '李费水';

-- 所在4楼的班里的大队长和中队长以及班主任都是谁？
SELECT c.floor, c.name, s.name, s.job, t.name
FROM student s
         JOIN class c on c.id = s.class_id
         JOIN teacher t on t.id = c.teacher_id
WHERE c.floor = 4
  AND s.job IN ('大队长', '中队长');

-- 全校最小的同学的班主任是谁？
SELECT s.name, s.age, s.birth, t.name
FROM student s
         JOIN class c on c.id = s.class_id
         JOIN teacher t on t.id = c.teacher_id
ORDER BY s.birth DESC
LIMIT 1;

2、连接查询分组聚合

（1）连接查询中使用聚合函数

-- 连接查询使用聚合函数
-- 查看范传奇所带班级的学生共多少人？
SELECT t.name, c.name, COUNT(*) `人数`
FROM teacher t
         JOIN class c on t.id = c.teacher_id
         JOIN student s on c.id = s.class_id
WHERE t.name = '范传奇';

-- 教语文的老师平均工资是多少
SELECT s.name, AVG(salary)
FROM teacher t
         JOIN subject s on s.id = t.subject_id
WHERE s.name = '语文';

-- 每门课的平均工资
SELECT s.name, AVG(salary) `平均工资`
FROM teacher t
         JOIN subject s on s.id = t.subject_id
GROUP BY s.name

（2）连接查询分组统计

-- 仅查看平均工资高于6000的那些科目的老师平均工资是多少？
SELECT s.name, AVG(salary) `avg`
FROM teacher t
         JOIN subject s on s.id = t.subject_id
GROUP BY s.name
HAVING AVG(salary) > 6000
ORDER BY avg DESC;

3、主键和外键

（1）概述

主键是用于唯一标识数据库表中每一行数据的字段或字段组合。主键的值必须是唯一的，且不能为NULL。

外键是用于建立两个表之间关联关系的字段。外键可以定义在一个表中，它的值对应着另一个表中的主键值。通过使用外键，可以在多个表之间建立起关系，从而实现数据的关联查询和更新操作。外键可以用来限制数据的完整性，以避免不一致和错误的数据操作。外键的值必须与对应主键的值相同，或为NULL。

（3）多对多关系

-- 查看'李费水'都学了哪些课程以及成绩？
SELECT s.name, s2.name `课程`, tsss.score `成绩`
FROM student s
         JOIN t_stu_subject_score tsss on s.id = tsss.stu_id
         JOIN subject s2 on s2.id = tsss.subject_id
WHERE s.name = '李费水';

-- 查看学习语文的学生都有谁
SELECT s.name, s2.name, tsss.score
FROM student s
         JOIN t_stu_subject_score tsss on s.id = tsss.stu_id
         JOIN subject s2 on s2.id = tsss.subject_id
WHERE s2.name = '语文'

4、外连接

（1）查询类型

  • 内连接（INNER JOIN）：返回两个表中匹配的行。只有在两个表中都存在匹配的数据时，才会返回结果。
  • 左连接（LEFT JOIN）：返回左表中的所有行，以及右表中匹配的行。如果右表中没有匹配的行，则返回NULL值。
  • 右连接（RIGHT JOIN）：返回右表中的所有行，以及左表中匹配的行。如果左表中没有匹配的行，则返回NULL值。
  • 全连接（FULL OUTER JOIN）：返回左表和右表中的所有行，如果没有匹配的行，则返回NULL值。（MySQL 没有提供支持）
  • 自连接（SELF JOIN）：在一个表中进行连接查询，可以将表看作两个表，其中一个表是源表，另一个表是目标表。
  • 交叉连接（CROSS JOIN）：返回两个表中所有可能的组合，也称为笛卡尔积。

（2）左外连接查询

返回左表中的所有行，以及右表中匹配的行。如果右表中没有匹配的行，则返回NULL值。

-- 查看所有的班级，以及该班级对应的班主任，没有班主任的也要将班级列出来
SELECT c.name, t.name
FROM class c
        LEFT JOIN teacher t on t.id = c.teacher_id

（3）内连接与外连接对比

内连接与外连接对比

（4）右外连接

-- 查看所有的班级，以及该班级对应的班主任，没有班级的老师也要列出来
SELECT c.name, t.name
FROM class c
         RIGHT JOIN teacher t on t.id = c.teacher_id

（5）全连接

如果希望得到这样的结果：查看所有的班级，以及该班级对应的班主任，不满足连接条件的班级和老师也都要体现，也就是既要显示没有班主任的班级，也要显示没有代班的老师。

全连接

SELECT c.name, t.name
FROM class c
         RIGHT JOIN teacher t on t.id = c.teacher_id
UNION
SELECT c.name, t.name
FROM class c
         LEFT JOIN teacher t on t.id = c.teacher_id

（6）自连接

在自连接中，表被视为两个不同的表，其中一个表包含源表中的数据，而另一个表包含目标表中的数据。通过自连接，可以将表中的数据与它们自身进行比较或匹配，以便执行某些特定的操作。

自连接

-- 查看每个老师以及他的领导信息
SELECT t.name, m.name
FROM teacher t
         LEFT JOIN teacher m
                   ON t.manager = m.id;

-- 查看刘苍松的下属都是谁？
SELECT t.name, m.name
FROM teacher t
         JOIN teacher m
              on t.manager = m.id
WHERE m.name = '刘苍松';

-- 查看3年级2班的班长是谁？(student表中team_leader记录班长的学生id)
SELECT s.name
FROM student s
         JOIN class c on c.id = s.class_id
WHERE s.id = s.team_leader
  AND c.name = '3年级2班';

-- 年龄最大的学生所在班的班主任的上司是谁？
SELECT s.name, s.birth `生日`, t.name `老师`, m.name `上司`
FROM student s
         JOIN class c on c.id = s.class_id
         JOIN teacher t on t.id = c.teacher_id
         JOIN teacher m on t.manager = m.id
WHERE s.birth = (SELECT MIN(birth) FROM student)

三、练习

1 查询练习1

实现如下查询：

-- 查看来自香港和澳门的学生都是谁?
SELECT student.name, age, gender, location.name `地址`
FROM student,
     location
WHERE location.name IN ('香港', '澳门')
  AND location_id = location.id;

-- 教“数学”、“物理”、“化学”的老师都是谁?
SELECT t.name, s.name
FROM teacher t,
     subject s
WHERE s.id = t.subject_id
  AND s.name IN ('数学', '物理', '化学');

-- “1年级3班” 班级的学生都有谁?
SELECT s.name, c.name
FROM student s,
     class c
WHERE c.name = '1年级3班'
  AND s.class_id = c.id;

-- 数学的老师所带的班级有哪些?
SELECT t.name, c.name, s.name
FROM teacher t,
     class c,
     subject s
WHERE c.teacher_id = t.id
  AND t.subject_id = s.id
  AND s.name = '数学';

-- 范传奇所带的班级学生都来自哪些城市(去重)?
SELECT DISTINCT l.name `城市`
FROM teacher t,
     student s,
     location l,
     class c
WHERE c.teacher_id = t.id
  AND s.class_id = c.id
  AND s.location_id = l.id
  AND t.name = '范传奇';


-- 2年级的几个班主任都教哪些课程?
SELECT t.name, c.name `班级`, s.name `所教课程`
FROM teacher t,
     class c,
     subject s
WHERE c.teacher_id = t.id
  AND t.subject_id = s.id
  AND c.name LIKE '2年级%';

-- 工资高于8000的老师所带班里的中队长都是谁?
SELECT t.name `老师姓名`, t.salary `工资`, s.name `学生姓名`, s.job `学生职位`
FROM teacher t,
     student s,
     class c
WHERE c.teacher_id = t.id
  AND s.class_id = c.id
  AND t.salary > 8000
  AND s.job = '中队长';

-- "黄喻"的班主任教哪门课?
SELECT s.name, t.name, s2.name
FROM student s,
     teacher t,
     class c,
     subject s2
WHERE c.teacher_id = t.id
  AND s.class_id = c.id
  AND t.subject_id = s2.id
  AND s.name = '黄喻';

-- 所在3楼的班里的大队长和中队长以及班主任都是谁?
SELECT c.floor, s.name, s.job, t.name
FROM student s,
     class c,
     teacher t
WHERE s.class_id = c.id
  AND c.teacher_id = t.id
  AND c.floor = '3'
  AND s.job IN ('大队长', '中队长');

-- 全校最大的同学的班主任是谁?
SELECT s.name, s.birth, s.age, t.name
FROM teacher t,
     student s,
     class c
WHERE c.teacher_id = t.id
  AND s.class_id = c.id
HAVING s.birth = (SELECT MIN(birth) FROM student);

2 查询练习2

实现如下查询：

-- 查询"王克晶"所带班级的学生姓名、年龄、班级名称及老师名字
SELECT s.name, s.age, c.name, t.name
FROM student s,
     teacher t,
     class c
WHERE c.teacher_id = t.id
  AND s.class_id = c.id
  AND t.name = '王克晶';

-- 查看1年级的同学的名字和来自的城市
SELECT s.name, c.name, l.name
FROM student s,
     class c,
     location l
WHERE c.id = s.class_id
  AND s.location_id = l.id
  AND c.name LIKE '1年级%';

-- 查看教语文课老师的平均工资是多少
SELECT AVG(t.salary)
FROM teacher t
         JOIN subject s on t.subject_id = s.id
WHERE s.name = '语文';

-- 每个科目的平均分是多少
SELECT DISTINCT s.name, AVG(tsss.score)
FROM subject s
         JOIN t_stu_subject_score tsss on s.id = tsss.subject_id
GROUP BY s.name;

-- 计算每个学生的全部科目总分，并且按照分数从高到低排名
SELECT s2.name, SUM(tsss.score)
FROM t_stu_subject_score tsss
         JOIN subject s on s.id = tsss.subject_id
         JOIN student s2 on tsss.stu_id = s2.id
GROUP BY s2.name
ORDER BY SUM(tsss.score) DESC;

-- 找到总分最高的学生和班级
SELECT s.name,c.name,SUM(S2.score)
FROM student s
         JOIN class c on c.id = s.class_id
         JOIN t_stu_subject_score s2 on s2.stu_id = s.id
GROUP BY s.name
ORDER BY SUM(S2.score) DESC
LIMIT 1;


-- 计算每位学生的平均分
SELECT s2.name, AVG(tsss.score)
FROM t_stu_subject_score tsss
         JOIN subject s on s.id = tsss.subject_id
         JOIN student s2 on tsss.stu_id = s2.id
GROUP BY s2.name
ORDER BY AVG(tsss.score) DESC;

-- 找到还没有填写城市信息的学员
SELECT s.name,l.name
FROM student s JOIN location l on l.id = s.location_id
WHERE l.name IS NULL ;

3 查询练习3

实现如下查询：

-- 查询每个城市的学生数量以及该城市的平均年龄。
SELECT l.name, count(s.location_id) `学生数量`, AVG(S.age) `平均年龄`
FROM student s
         JOIN location l on l.id = s.location_id
GROUP BY l.name;

-- 查询每个科目的平均分数以及最高和最低分数的学生姓名和分数。
SELECT s2.name         `科目`,
       AVG(tsss.score) `平均分`,
       MAX(tsss.score) `最高分`,
       MAX(s.name)     `学生姓名`,
       MIN(tsss.score) `最低分`,
       MIN(s.name)     `学生姓名`
FROM t_stu_subject_score tsss
         JOIN student s on s.id = tsss.stu_id
         JOIN subject s2 on s2.id = tsss.subject_id
GROUP BY s2.name;

-- 查询每个老师的工资和每个老师所教科目的数量。
SELECT t.name, t.salary, COUNT(subject_id)
FROM teacher t
         JOIN subject s on s.id = t.subject_id
GROUP BY t.name;

-- 查询每个城市的学生数量及平均年龄。
SELECT l.name, COUNT(location_id), AVG(s.age)
FROM student s
         JOIN location l on l.id = s.location_id
GROUP BY s.location_id;

-- **查询每个班级的学生数量、平均年龄和平均成绩。
SELECT c.name, COUNT(DISTINCT s.id) `学生数量`, AVG(s.age) `平均年龄`, AVG(tsss.score) `平均成绩`
FROM student s
         JOIN class c on c.id = s.class_id
         LEFT JOIN t_stu_subject_score tsss on tsss.stu_id = s.id
GROUP BY c.name;

-- 查询每个城市的学生数量、平均年龄和工资最高的老师信息。
SELECT l.name, COUNT(s.location_id) `学生数量`, AVG(s.age) `平均年龄`, MAX(t.name), MAX(t.salary)
FROM student s
         JOIN location l on l.id = s.location_id
         JOIN class c on c.id = s.class_id
         JOIN teacher t on t.id = c.teacher_id
GROUP BY l.name;

-- 查询每个老师的学生数量、平均年龄和平均成绩，并按照平均成绩降序排列。
SELECT t.name, COUNT(DISTINCT s.id) `学生数量`, AVG(s.age) `平均年龄`, AVG(tsss.score) `平均成绩`
FROM teacher t
         JOIN class c on t.id = c.teacher_id
         JOIN student s on c.id = s.class_id
         JOIN t_stu_subject_score tsss on s.id = tsss.stu_id
GROUP BY t.name
ORDER BY AVG(tsss.score) DESC;

-- 查询每个老师所教授的科目数量、平均工资和平均成绩，并按照平均成绩降序排列。
SELECT t.name, COUNT(DISTINCT t.subject_id) '科目数量', AVG(salary) `平均工资`, AVG(tsss.score) `平均成绩`
FROM teacher t
         JOIN subject s on s.id = t.subject_id
         join t_stu_subject_score tsss on s.id = tsss.subject_id
GROUP BY t.name
ORDER BY AVG(tsss.score) DESC;

-- 查询每个科目的平均成绩，并按照平均成绩降序排列。
SELECT s.name, AVG(tsss.score)
FROM subject s
         JOIN t_stu_subject_score tsss on s.id = tsss.subject_id
GROUP BY s.name
ORDER BY AVG(tsss.score) DESC ;
最后修改：2023 年 08 月 23 日
© 允许规范转载
如果觉得我的文章对你有用，请随意赞赏