系列十五、多表查询_department表-优快云博客

本文链接：https://blog.youkuaiyun.com/HelloWorld20161112/article/details/129159046

本文介绍了数据库中的一对一、一对多和多对多关系设计，以及如何通过外键和中间表实现这些关系。接着详细讲解了内连接、外连接（左外和右外）和自连接的概念，提供了多种查询示例。此外，还涵盖了子查询的使用，包括标量子查询、列子查询、行子查询和表子查询，并给出了实际的查询场景案例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、多表关系

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：一对多（多对一），多对多、一对一

1.1、一对多

案例: 部门与员工的关系

关系 : 一个部门对应多个员工，一个员工对应一个部门

实现 : 在多的一方建立外键，指向一的一方的主键

1.2、多对多

案例：学生与课程的关系

关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择

实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

SQL脚本

drop table if exists student;
create table student(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    no varchar(10) comment '学号'
) comment '学生表';

insert into student values 
(null, '黛绮丝', '2000100101'),
(null, '谢逊','2000100102'),
(null, '殷天正', '2000100103'),
(null, '韦一笑', '2000100104');

drop table if exists course;
create table course(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '课程名称'
) comment '课程表';

insert into course values 
(null, 'Java'), 
(null, 'PHP'), 
(null , 'MySQL') ,
(null, 'Hadoop');

drop table if exists student_course;
create table student_course(
    id int auto_increment comment '主键' primary key,
    student_id int not null comment '学生ID',
    course_id int not null comment '课程ID',
    constraint fk_course_id foreign key (course_id) references course (id),
    constraint fk_student_id foreign key (student_id) references student (id)
)comment '学生课程中间表';
insert into student_course values 
(null,1,1),
(null,1,2),
(null,1,3),
(null,2,2),
(null,2,3),
(null,3,4);

1.3、一对一

案例：用户与用户详情的关系

关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率

实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

二、多表查询概述

2.1、数据准备

删除之前employee、department表中的数据并重新初始化数据。

-- 创建department表，并插入数据
drop table if exists department;
create table department(
	id int auto_increment comment 'ID' primary key,
	name varchar(50) not null comment '部门名称'
)comment '部门表';

INSERT INTO department (id, name) VALUES 
(1, '研发部'), (2, '市场部'),(3, '财务部'), (4,'销售部'), (5, '总经办'), (6, '人事部');

-- 创建employee表，并插入数据
drop table if exists employee;
create table employee(
	id int auto_increment comment 'ID' primary key,
	name varchar(50) not null comment '姓名',
	age int comment '年龄',
	job varchar(20) comment '职位',
	salary int comment '薪资',
	entry_date date comment '入职时间',
	manager_id int comment '直属领导ID',
	department_id int comment '部门ID'
)comment '员工表';

-- 删除外键
-- alter table employee drop foreign key fk_employee_department_id;
-- 添加外键
alter table employee add constraint fk_employee_department_id foreign key (department_id) references department(id);

INSERT INTO employee (id, name, age, job,salary, entry_date, manager_id, department_id)
VALUES
(1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),
(2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1),
(3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),
(4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1),
(5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),
(6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1),
(7, '灭绝', 60, '财务总监',8500, '2002-09-12', 1,3),
(8, '周芷若', 19, '会计',48000, '2006-06-02', 7,3),
(9, '丁敏君', 23, '出纳',5250, '2009-05-13', 7,3),
(10, '赵敏', 20, '市场部总监',12500, '2004-10-12', 1,2),
(11, '鹿杖客', 56, '职员',3750, '2006-10-03', 10,2),
(12, '鹤笔翁', 19, '职员',3750, '2007-05-09', 10,2),
(13, '方东白', 19, '职员',5500, '2009-02-12', 10,2),
(14, '张三丰', 88, '销售总监',14000, '2004-10-12', 1,4),
(15, '俞莲舟', 38, '销售',4600, '2004-10-12', 14,4),
(16, '宋远桥', 40, '销售',4600, '2004-10-12', 14,4),
(17, '陈友谅', 42, null,2000, '2011-10-12', 1,null);

2.2、概述

多表查询就是指从多张表中查询数据。

2.3、分类

2.3.1、连接查询

内连接
外连接
- 左外连接：查询左表所有数据，以及两张表交集部分数据
- 右外连接：查询右表所有数据，以及两张表交集部分数据
自连接：当前表与自身的连接查询，自连接必须使用表别名

2.3.2、子查询

三、内连接

内连接查询的两张表交集部分的数据。(也就是绿色部分的数据)

3.1、隐式内连接

SELECT 字段列表 FROM 表 1 , 表 2 WHERE 条件 ... ;

3.2、显示内连接

SELECT 字段列表 FROM 表 1 [ INNER ] JOIN 表 2 ON 连接条件 ... ;

3.3、案例

3.3.1、案例一：查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)

select e.name,d.name from employee e, department d where e.department_id = d.id;

3.3.2、案例二：查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现)

select e.name, d.name from employee e inner join department d on e.department_id = d.id;

3.3.3、注意事项

一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段。

四、外连接

外连接分为两种，分别是：左外连接和右外连接

4.1、左外连接

语法： SELECT 字段列表 FROM 表 1 LEFT [ OUTER ] JOIN 表 2 ON 条件 ... ;

说明：左外连接相当于查询表 1( 左表 ) 的所有数据，当然也包含表 1 和表 2 交集部分的数据。

4.2、右外连接

语法：SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;

说明：右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。

4.3、案例

4.3.1、案例一：查询employee表的所有数据, 和对应的部门信息(左外连接)

分析：
    由于需求中提到，要查询employee的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询。
    表结构: employee, department
    连接条件: employee.department_id = department.id

select e.*, d.name from employee e left outer join department d on e.department_id = d.id;

select e.*, d.name from employee e left join department d on e.department_id = d.id;

4.3.2、案例二：查询department表的所有数据, 和对应的员工信息(右外连接)

分析：
   由于需求中提到，要查询department表的所有数据，所以是不能内连接查询的，需要考虑使用外连接询。
   表结构: employee, department
   连接条件: employee.department_id = department.id

select d.*, e.* from employee e right outer join department d on e.department_id = d.id;

select d.*, e.* from department d left outer join employee e on e.department_id = d.id;

4.3.3、注意事项

左外连接和右外连接是可以相互替换的，只需要调整在连接查询时SQL中，表结构的先后顺序就可以了。而我们在日常开发使用时，更偏向于左外连接。

五、自连接

5.1、自连接查询

定义：自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次。对于自连接查询，可以是内连接查询，也可以是外连接查询

5.1.1、语法

SELECT 字段列表 FROM 表 A 别名 A JOIN 表 A 别名 B ON 条件 ... ;

5.1.2、案例一：查询员工及其所属领导的名字

select a.name , b.name from employee a, employee b where a.manager_id = b.id;

5.1.3、案例二：查询所有员工 employee 及其领导的名字 employee , 如果员工没有领导, 也需要查询出来

select a.name '员工', b.name '领导' from employee a left join employee b on a.manager_id = b.id;

5.1.4、注意事项

在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底是哪一张表的字段。

5.2、联合查询

定义：对于 union 查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

5.2.1、语法

SELECT 字段列表 FROM 表 A ...

UNION [ ALL ]

SELECT 字段列表 FROM 表 B ....;

注意事项：

1、对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。

2、union all 会将全部的数据直接合并在一起， union 会对合并之后的数据去重。

5.2.2、案例一：将薪资低于 5000 的员工 , 和年龄大于 50 岁的员工全部查询出来(or 实现)

select * from employee where salary < 5000 or age > 50 order by id;

5.2.3、案例二：将薪资低于 5000 的员工 , 和年龄大于 50 岁的员工全部查询出来(union实现)

select * from employee where salary < 5000
union 
select * from employee where age > 50
order by id;

5.2.4、案例三：将薪资低于 5000 的员工 , 和年龄大于 50 岁的员工全部查询出来(union all实现)

select * from employee where salary < 5000
union all
select * from employee where age > 50
order by id;

5.2.5、union vs union all

union 联合查询，会对查询出来的结果进行去重处理。

union all 查询出来的结果，仅仅进行简单的合并，并未去重。

5.2.6、注意事项

如果多条查询语句查询出来的结果，字段数量不一致，在进行 union/union all 联合查询时，将会报错。如：

select * from employee where salary < 5000
union
select `name` from employee where age > 50
order by id;

六、子查询

6.1、概述

SQL 语句中嵌套 SELECT 语句，称为嵌套查询，又称子查询。

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );

子查询外部的语句可以是 INSERT / UPDATE / DELETE / SELECT 的任何一个。

6.2、分类

6.2.1、根据子查询结果分类

标量子查询（子查询结果为单个值）
列子查询（子查询结果为一列）
行子查询（子查询结果为一行）
表子查询（子查询结果为多行多列）

6.2.2、根据子查询位置分类

WHERE之后
FROM之后
SELECT之后

6.3、标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。常用的操作符：= <> > >= < <=

6.3.1、案例一：查询 "销售部" 的所有员工信息

分析：完成这个需求时，我们可以将需求分解为两步：
    1、查询 "销售部" 部门ID
    select id from department where name = '销售部';

    2、根据 "销售部" 部门ID, 查询员工信息
    select * from employee where department_id = (select id from department where name = '销售部');

6.3.2、案例二：查询在 "方东白" 入职之后的员工信息

分析：完成这个需求时，我们可以将需求分解为两步;
    1、查询 方东白 的入职日期
    select entry_date from employee where name = '方东白';

    2、查询指定入职日期之后入职的员工信息
    select * from employee where entry_date > (select entry_date from employee where name = '方东白');

6.4、列子查询

子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。常用的操作符：IN 、 NOT IN 、 ANY 、 SOME 、 ALL

6.4.1、案例一：查询 "销售部" 和 "市场部" 的所有员工信息

分析：
    1、 查询 "销售部" 和 "市场部" 的部门ID
    select id from department where name = '销售部' or name = '市场部';

    2、根据部门ID, 查询员工信息
    select * from employee where department_id in (select id from department where name = '销售部' or name = '市场部');

6.4.2、案例二：查询比财务部所有人工资都高的员工信息

分析：
    1、查询所有财务部人员工资
    select id from department where name = '财务部';
    select salary from employee where department_id = (select id from department where name = '财务部');

    2、比财务部所有人工资都高的员工信息
    select * from employee where salary > all (select salary from employee where department_id = (select id from department where name = '财务部'));

    OR

    select * from employee where salary > (select max(salary) from employee where department_id = (select id from department where name = '财务部'));

6.4.3、案例三：查询比研发部其中任意一人工资高的员工信息

分析：
    1、查询研发部所有人工资
    select salary from employee where department_id = (select id from department where name = '研发部');

    2、比研发部其中任意一人工资高的员工信息
    select * from employee where salary > any (select salary from employee where department_id = (select id from department where name = '研发部'));

    OR

    select * from employee where salary > (select min(salary) from employee where department_id = (select id from department where name = '研发部'));

6.5、行子查询

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。常用的操作符：= 、 <> 、 IN 、 NOT IN

6.5.1、案例：查询与 "张无忌" 的薪资及直属领导相同的员工信息

分析：
    1、查询 "张无忌" 的薪资及直属领导的薪资
    select salary, manager_id from employee where name = '张无忌';

    2、 查询与 "张无忌" 的薪资及直属领导相同的员工信息 
    select * from employee where (salary,manager_id) = (select salary, manager_id from employee where name = '张无忌');

6.6、表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询。

常用的操作符：IN

6.6.1、案例一：查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息

分析：
    1、 查询 "鹿杖客" , "宋远桥" 的职位和薪资
    select job, salary from employee where name = '鹿杖客' or name = '宋远桥';
    

    2、 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
    select * from employee where (job,salary) in (select job, salary from employee where name = '鹿杖客' or name = '宋远桥');

6.6.2、案例二：查询入职日期是 "2006-01-01" 之后的员工信息及其部门信息

分析：
    1、入职日期是 "2006-01-01" 之后的员工信息
    select * from employee where entry_date > '2006-01-01';

    2、查询这部分员工, 对应的部门信息
    select e.*, d.* from (select * from employee where entry_date > '2006-01-01') e left
join department d on e.department_id = d.id ;

七、多表查询案例

7.1、SQL初始化

drop table if exists sal_grade;

create table sal_grade(
    grade int comment '等级',
    losal int comment '最低工资',
    hisal int comment '最高工资'
) comment '薪资等级表';

insert into sal_grade values 
(1,0,3000),
(2,3001,5000),
(3,5001,8000),
(4,8001,10000),
(5,10001,15000),
(6,15001,20000),
(7,20001,25000),
(8,25001,30000);

7.2、案例

7.2.1、查询员工的姓名、年龄、职位、部门信息（隐式内连接）

select e.name '姓名',e.age '年龄',e.job '职位',d.`name` '部门' from employee e,department d where e.department_id = d.id;

7.2.2、查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）

select e.name '姓名',e.age '年龄',e.job '职位',d.`name` '部门' 
from employee e inner join department d on e.department_id = d.id
where e.age < 30;

7.2.3、查询拥有员工的部门ID、部门名称

select DISTINCT d.id,d.`name` from department d INNER JOIN employee e on d.id = e.department_id;

7.2.4、查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来(外连接)

select e.*,d.name from employee e LEFT JOIN department d on e.department_id = d.id where e.age > 40;

7.2.5、查询所有员工的工资等级

-- 方式一
select e.name,e.salary,s.losal, s.hisal,s.grade from employee e INNER JOIN sal_grade s on e.salary BETWEEN s.losal and s.hisal order by e.salary;

-- 方式二
select e.name,e.salary,s.losal, s.hisal,s.grade from employee e , sal_grade s where e.salary >= s.losal and e.salary <= s.hisal order by e.salary;

7.2.6、查询 "研发部" 所有员工的信息及工资等级

select e.name '员工姓名',d.`name` '部门名称',e.salary '员工工资', s.losal '最低工资',s.hisal '最高工资',s.grade '工资等级' 
from employee e 
INNER JOIN sal_grade s on e.salary BETWEEN s.losal and s.hisal
INNER JOIN department d on e.department_id = d.id
where e.department_id = (select id from department where name = '研发部');

7.2.7、查询 "研发部" 员工的平均工资

select avg(e.salary) from employee e where e.department_id = (select id from department where name = '研发部');

OR

select avg(e.salary) from employee e, department d where e.department_id = d.id and d.name = '研发部';

7.2.8、查询工资比 "灭绝" 高的员工信息

select e.* from employee e where salary > (select salary from employee WHERE `name` = '灭绝') order by e.salary;

7.2.9、查询比平均薪资高的员工信息

select * from employee where salary > (select avg(salary) from employee);

7.2.10、查询低于本部门平均工资的员工信息

select * from employee e2 INNER JOIN department d
on e2.department_id = d.id
where e2.salary < (select avg(e1.salary) from employee e1 where e1.department_id = e2.department_id);


select * from employee e2 where e2.salary < ( select avg(e1.salary) from employee e1 where e1.department_id = e2.department_id );

7.2.11、查询所有的部门信息, 并统计部门的员工人数

select d.id, d.name, (select count(*) from employee e where e.department_id = d.id) '人数' from department d;

7.2.12、查询所有学生的选课情况, 展示出学生名称, 学号, 课程名称

select s.name , s.no , c.name from student s , student_course sc , course c where s.id = sc.student_id and sc.course_id = c.id ;