MySQL基础8——MySQL多表查询
cuckooyang
|
|
MySQL
|
789

3683 字
|
22 分钟
本文最后更新于 516 天前，如有失效请评论区留言。

多表关系

在进行数据库表结构的设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在多种联系，基本上分为三种情况。

  • 一对多（多对一）
  • 多对多
  • 一对一

一对多

部门与员工的关系就是典型的一对多关系，一个部门对应了多个员工，一个员工对应一个部门，在这种情况下，可以在多的一方建立外键，指向一的一方的主键，来实现一对多的表间关联，比如之前学习外键中使用的示例。

image-20240801125349616

多对多

学生与课程之间的关系就是一种多对多关系，一个学生可以选修多门课程，一门课程也可以被多个学生选修，这种情况下，可以建立第三张中间表来实现学生表和课程表间的关联，其中中间表至少包含两个外键，分别关联至两方主键。下面通过一个学生和课程之间的例子来说明。

image-20240801125711726

创建学生表

create table student(
    id int auto_increment primary key ,
    name varchar(20),
    num varchar(10) comment '学号'
) comment '学生表';
insert into student values (null , '黛绮丝', '2000100101'),(null, '谢逊','2000100102'),(null, '殷天正','2000100103'),(null,'韦一笑','2000100104');

image-20240801142344983

创建课程表

create table coures(
    id int auto_increment primary key ,
    name varchar(20)
) comment '课程表';
insert into coures value (null,'java'),(null, 'php'),(null, 'MySQL'),(null, 'hadoop');

image-20240801142400906

创建中间表

create table student_course(
    id int auto_increment primary key ,
    studentid int not null,
    courseid int not null ,
    constraint fk_studentid foreign key (studentid) references student(id),
    constraint fk_courseid foreign key (courseid) references coures(id)
);
insert into student_course  values(null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3),(null,3,4);

image-20240801142412766

一对多

一对一关系多用于单表拆分中，当一张表字段很多时，可以将一张表拆分成多张表来提升操作效率。用户与用户详情的关系就是一种一对一关系，比如将用户基础信息放在一张表中，用户详情信息放在另一张表中，就可以进行专门的查询而不用每次查询全部信息。这种情况下，可以在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（unique）。下面我们通过一个用户信息的例子来说明一对一关系。

原来的表

image-20240801134348968

拆分后的两张表

image-20240801134758886

基础信息表

create table tb_user(
    id  int primary key auto_increment,
    name varchar(20),
    age int,
    gender char(1),
    phone char(11)
);
insert into tb_user (id, name, age, gender, phone)
values (null,'黄渤',45,'1','18800001111'),
(null,'冰冰',18,'1','18800002222'),
(null,'马云',55,'1','18800003333'),
(null,'李彦宏',50,'1','1880000444');

image-20240801141352706

详细信息表

create table tb_user_edu(
    id int primary key auto_increment,
    degree varchar(20),
    major varchar(50),
    primaryschool varchar(50),
    middleschool varchar(50),
    university varchar(50),
    userid int unique,
    constraint fk_userid foreign key (userid) references  tb_user(id)
);
insert into tb_user_edu (id,degree, major, primaryschool, middleschool, university, userid)
values (null,'本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1),
       (null,'硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2),
       (null,'本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3),
       (null,'本科','应用数学','阳泉第一小学','阳泉第一中学','清华大学',4);

image-20240801141404250

这样创建的两张表，就具备了一对一关系。

多表查询

多表查询指的是从多张表中查询数据，这里跟之前学习的DQL有所区别，之前都是针对单表进行查询的案例。

创建部门表

create table dept(
    id int auto_increment primary key,
    name varchar(20) not null
);

insert into dept(id,name) values(1,'研发部'),(2,'市场部'),(3,'财务部'),(4,'销售部'),(5,'总经办'),(6,'人事部');

创建员工表

create table empp(
    id int primary key auto_increment,
    name varchar(20) unique not null,
    age int,
    job varchar(20) not null,
    salary int not null,
    entrydate date,
    managerid int,
    dept_it int
);

insert into empp(id, name, age,job, salary, entrydate, managerid, dept_it) values
(1,'金庸',66,'总裁',20000, '2002-01-01',null,5),
(2,'张无忌',20,'项目经理',12500, '2005-01-01',1,1),
(3,'杨逍',33,'开发',8400, '2002-10-01',2,1),
(4,'韦一笑',48,'开发',11000, '2005-02-05',2,1),
(5,'常遇春',43,'开发',10500, '2004-09-01',3,1),
(6,'小昭',19,'程序员鼓励师',6000, '2005-10-11',2,1),
(7, '灭绝', 60, '财务总监', 8500, '2021-08-04', 1, 3),
(8, '周芷若', 19, '会计', 7000, '2017-08-09', 7, 3),
(9, '丁敏君', 23, '出纳', 7000, '2005-05-10', 7, 3),
(10, '赵敏', 20, '市场部总监', 11000, '1998-07-02', 1, 2),
(11, '鹿杖客', 56, '职员', 4000, '2010-08-24', 10, 2),
(12, '鹤笔翁', 19, '职员', 5000, '2024-07-04', 10, 2),
(13, '东方白', 19, '职员', 5000, '2012-08-21', 10, 2),
(14, '张三丰', 88, '销售总监', 14000, '2021-06-25', 1, 4),
(15, '俞莲舟', 38, '销售', 4600, '2022-08-31', 14, 4),
(16, '宋远桥', 40, '销售', 4600, '1999-08-13', 14, 4),
(17, '陈友谅', 42, '前台', 2000, '2015-11-19', 1, null);

多表查询

  • 多表查询的笛卡尔积现象（笛卡尔提出了这个概念），即两个集合的所有元素的组合情况。
  • 在多表查询中，需要消除无效的笛卡尔积。
select * from empp,dept;

image-20240801152059724

加上查询条件，就得到了我们要的数据。

  • 在17个员工数据中只查到了16个数据，是因为id=17的员工没有分配dept_id，不满足查询条件。
select * from empp,dept where empp.dept_it = dept.id;

image-20240801152818269

多表查询分类

多表查询分为连接查询和子查询：

  1. 连接查询

    内连接：相当于查询A、B的交集；

    外连接——左外连接：查询左表的所有数据，以及两张表交集部分的数据；

    外连接——右外连接：查询由表的所有数据，以及两张表交集部分的数据；

    自连接：当前表与自身的连接查询，自连接必须使用表别名。

  2. 子查询

内连接查询

内连接查询的是两张表交集的部分的数据，内连接分为隐式内连接和现式内连接。

1、隐式内连接

select 字段列表 from 表1,表2 where 条件；

查询每个员工的姓名，及关联的部门的名称。

  • 表：empp，dept
  • 连接条件：empp.dept_id = dept.id
  • 注意：如果为表取了别名，当前sql中就不能够在通过原始表名来引用字段。
select empp.name,dept.name from empp,dept where empp.dept_it = dept.id;
select e.name,d.name from empp e , dept d where e.dept_it = d.id;

image-20240801155235425

显式内连接

select 字段列表 from 表1 [inner] join 表2 on 连接条件...;
# inner join ... on ...

查询每个员工的姓名，及关联的部门的名称。

  • 表：empp，dept
  • 连接条件：empp.dept_id = dept.id
select e.name,d.name from empp e inner join dept d on e.dept_it = d.id;
select e.name,d.name from empp e join dept d on e.dept_it = d.id;

image-20240801155235425

外连接查询

1、左外连接

  • 查询表1的所有数据和表1表2的交集部分的数据
select 字段列表 from 表1 left [outer] join 表2 on 条件;

查询emp表的所有数据，和对应的部门信息。

  • 表：empp，dept
  • 连接条件：empp.dept_id = dept.id
  • 这里可以看到，第17号员工的信息也查出来了，这就是外连接跟内连接的区别了。
select e.*, d.name from empp e left outer join dept d on e.dept_it = d.id;
select e.*, d.name from empp e left join dept d on e.dept_it = d.id;

image-20240801160418697

2、右外连接

  • 查询表2的所有数据和表1表2的交集部分的数据
select 字段列表 from 表1 right [outer] join 表2 on 条件;

查询dept表的所有数据，和对应员工的信息（右外连接）

  • 表：empp，dept
  • 连接条件：empp.dept_id = dept.id
select d.*,e.* from empp e right outer join dept d on e.dept_it = d.id;

image-20240801172738889

实际上左外连接和右外连接是可以互相变换的，在使用中一般使用左外连接。

自连接查询

语法结构

select 字段列表 from 表1 别名a join 表1 别名b on 条件...;
  • 自连接的查询，可以是内连接查询，也可以是外连接查询。
  • 在自连接查询中，表必须起别名。

1、查询员工及其所属领导的名字

select a.name '员工',b.name '所属领导' from empp a,empp b where a.managerid = b.id;

image-20240801174839545

2、查询所有员工及其领导的名字，如果员工没有领导，也要查询出来。

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

image-20240801181001336

联合查询Union，Union all

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

select 字段列表 from 表a ...
union [all]
select 字段列表 from 表b ...;

案例：查询所有薪资低于8000的员工和年龄大于50岁的员工。

  • 通过关键字union all 查询会将全部的数据合并在一起，通过关键字union会对合并之后的数据去重。
  • 联合查询多张表的字段数量和类型必须一致。
select * from empp where salary < 8000
union all
select * from empp where age > 50;

image-20240802080000302

子查询（嵌套查询）

SQL语句中嵌套select语句，成为嵌套查询，也叫子查询。

语法

  • 子查询外部的语句可以是insert/ update/ select 。
select * from t1 where column1 = (select column1 from t2);

根据查询结果不同个，可以将子查询分为四类：

  • 标量子查询：子查询结果为单个值；

  • 列子查询：子查询结果为一列；

  • 行子查询：子查询结果为一行；

  • 表子查询：子查询结果为多行多列。

根据子查询的位置，分为：

  • where之后出现的子查询；
  • from之后出现的子查询；
  • select之后出现的子查询。

标量子查询

子查询返回的结果是单个值（数字，字符串，日期等），是一种最简单的子查询形式，这种子查询称为标量子查询。

常用的操作符(比较运算符)

=   <>  >  >=  <  <=

1、查询销售部所有的员工信息

  • 第一步：查询销售部的部门id
  • 第二步：根据销售部的部门id查询员工信息
select id from dept where name = '销售部';
select * from empp where dept_it = 4;

等价于

select * from empp where dept_it = (select id from dept where name = '销售部');

image-20240802082217717

2、查询在东方白入职之后的员工员工信息

  • 第一步：查询东方白的入职日期；
  • 第二步：查询东方白入职日期之后入职的员工信息。
select entrydate from empp where name = '东方白';
select * from empp where entrydate > '2012-08-21';

等价于

select * from empp where entrydate > (select entrydate from empp where name = '东方白');

image-20240802082552991

根据这两个案例，可以看到在括号里（子查询）的结果为单个值，这种子查询结果为单个值的就叫做标量子查询。

列子查询

子查询返回的结果是一列（可以是多行），这种子查询成为列子查询。

常用的操作符有：

  • in : 在指定的范围内多选一。
  • not in ：不在指定的范围之内。
  • any ：子查询返回列表中，有任意一个满足即可。
  • some ：与any等同，使用some的地方都可以使用any。
  • all ：子查询返回列表的所有值都必须满足。

1、查询销售部和市场部所有的员工信息

  • 第一步：查询销售部和市场部的部门id
  • 第二步：根据部门id查询员工信息
select id from dept where name = '销售部' or name = '市场部';
select * from empp where dept_it in (2,4);

等价于

select * from empp where dept_it in (select id from dept where name = '销售部' or name = '市场部');

image-20240802084226359

2、查询比财务部所有人工资都高的员工信息

  • 第一步：查询所有财务部的人员工资
  • 第二步：查询比财务部所有人工资都高的员工信息
select salary from empp where dept_it = (select id from dept where name = '财务部');
select * from empp where salary > all (select salary from empp where dept_it = (select id from dept where name = '财务部'));

image-20240802084621659

3、查询比研发部其中任意一人工资高的员工信息

  • 第一步：查询研发部所有员工的工资
  • 第二步：查询比研发部其中任意一人工资高的员工信息
  • any可以换成some。
select salary from empp where dept_it = (select id from dept where name = '研发部');
select * from empp where salary > any (select salary from empp where dept_it = (select id from dept where name = '研发部'));

image-20240802085935958

行子查询

子查询返回结果是一行（可以是多列），这种子查询称为行子查询。

常用操作符：

= , <> , in , not in

1、查询与东方白薪资及其直属领导相同的员工信息

  • 第一步：查询东方白薪资和直属领导
  • 第二步：查询与东方白薪资及其直属领导相同的员工信息
select  salary, empp.managerid from empp where name = '东方白';
select * from empp where salary = 5000 and managerid = 10;
或者
select * from empp where (salary,managerid) = (5000,10);

等价于

select * from empp where (salary,managerid) = (select  salary, empp.managerid from empp where name = '东方白');

表子查询

子查询返回的结果是多行多列（类似于一张表），这种子查询成为表子查询。

常用操作符：in (经常出现在from之后，将表子查询返回的结果作为临时表，再跟其他表做联合操作)

1、查询与鹿杖客，宋远桥的职位和薪资相同的员工信息。

  • 第一步：查询鹿杖客，宋远桥的职位和薪资
  • 第二步：查询与鹿杖客，宋远桥的职位和薪资相同的员工信息。
select job,salary from empp where name = '鹿杖客' or name = '宋远桥';
select * from empp where (job,salary) in (select job,salary from empp where name = '鹿杖客' or name = '宋远桥');

image-20240802093744883

  • 可以看到，没有与鹿杖客薪资相同的员工，与俞莲舟薪资和职位相同的是宋远桥。

2、查询入职日期是 2010-08-24 之后的员工信息，及其部门信息

  • 第一步：查询入职日期是 2010-08-24 之后的员工信息
  • 第二步：查询入职日期是 2010-08-24 之后的员工的部门信息
select e.*,d.* from (select * from empp where entrydate > '2010-08-24') e  left join dept d on e.dept_it = d.id;

image-20240802102422931

多表查询案例

1.查询员工的姓名、年龄、职位、部门信息。

2.查询年龄小于30岁的员工姓名、年龄、职位、部门信息。

3.查询拥有员工的部门ID、部门名称。

4.查询所有年龄大于40岁的员工，及其归属的部门名称；如果员工没有分配部门，也需要展示出来。

5.查询所有员工的工资等级。

6.查询“研发部”所有员工的信息及工资等级。

7.查询“研发部”员工的平均工资。

8.查询工资比“灭绝”高的员工信息。

9.查询比平均薪资高的员工信息。

10.查询低于本部门平均工资的员工信息。

11.查询所有的部门信息，并统计部门的员工人数。

12.查询所有学生的选课情况，展示出学生名称，学号，课程名称。

除了之前用到的dept表，empp表，还需新增一张薪资等级表。

create table salgrade(
    grade int,
    losal int,
    hisal int
);
INSERT INTO test.salgrade (grade, losal, hisal) 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);

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

  • 表：empp , dept
  • 连接条件：empp.dept_id = dept.id
  • 隐式内连接把两张表写在一起，并通过条件消除笛卡尔积。
select e.name,e.age,e.job,d.name from empp e,dept d where e.dept_it = d.id;

image-20240802104350218

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

  • 表：empp , dept

    • 连接条件：empp.dept_id = dept.id
    • 显式内连接通过关键字 inner join ... on ... where 实现。
select e.name,e.age,e.job,d.name from empp e inner  join dept d on e.dept_it = d.id where e.age < 30;

image-20240802104844619

3.查询拥有员工的部门ID、部门名称。

  • 表：empp , dept

    • 连接条件：empp.dept_id = dept.id

  • 在部门表中人事部并没有成员，这里可以通过内连接来实现查询，对于查询到的重复字段，可以通过distinct去重。

select distinct d.id ,d.name from empp e, dept d where e.dept_it = d.id;

image-20240802105151382

4.查询所有年龄大于40岁的员工，及其归属的部门名称；如果员工没有分配部门，也需要展示出来。

  • 表：empp , dept

    • 连接条件：empp.dept_id = dept.id
select e.*,d.name from empp e left join dept d on d.id = e.dept_it where e.age > 40;

image-20240802125951611

5.查询所有员工的工资等级。

  • 表：empp , salgrade

    • 连接条件：empp.salary >= salgrade.losal and empp.salary <= salgrade.hisal
select e.*,s.grade from empp e ,salgrade s where e.salary >= s.losal and e.salary <= s.hisal;
select e.*,s.grade from empp e ,salgrade s where e.salary between s.losal and  s.hisal;

image-20240802130340104

6.查询“研发部”所有员工的信息及工资等级。

  • 表：empp，salgrade，dept

  • 连接条件：

    empp.salary between salgrade.losal and salgrade.hisal，empp.dept_id = dept.id ，dept.name = '研发部'

select e.*,s.grade,d.name from empp e,dept d, salgrade s where e.dept_it = d.id and (e.salary between s.losal and s.hisal) and d.name = '研发部';

image-20240802131128401

7.查询“研发部”员工的平均工资。

  • 表：empp , dept

    • 连接条件：empp.dept_id = dept.id
select avg(e.salary) from empp e,dept d where e.dept_it = d.id and d.name = '研发部';

image-20240802131430848

8.查询工资比“灭绝”高的员工信息。

  • 查询灭绝的工资
  • 查询工资比灭绝高的员工
select * from empp  where salary > (select salary from empp where name = '灭绝');

image-20240802131618495

9.查询比平均薪资高的员工信息。

  • 查询员工的平均薪资
  • 查询比平均薪资高的员工信息
select * from empp where salary > (select avg(salary) from empp);

image-20240802131833095

10.查询低于本部门平均工资的员工信息。

  • 查询指定部门平均薪资
  • 查询低于本部门平均工资的员工信息
select *,(select avg(e1.salary) from empp e1 where e1.dept_it = e2.dept_it) '平均薪资' from empp e2 where e2.salary < (select avg(e1.salary)  from empp e1 where e1.dept_it = e2.dept_it);

image-20240802132540715

11.查询所有的部门信息，并统计部门的员工人数。

select d.id,d.name,(select count(*) from empp e  where e.dept_it = d.id) '人数' from dept d;

image-20240802133111856

12.查询所有学生的选课情况，展示出学生名称，学号，课程名称

  • 表：student , course, student_course

  • 连接条件：student.id = student_course.studentid，course.id = student_course.courseid

    n张表查询，需要n-1个条件

 select  s.name, s.num, c.name from student s,student_course sc,coures c where s.id = sc.studentid and sc.courseid = c.id;

image-20240802133834686

小结

SQL的多表联查到这里就结束了，需要注意的是，很多时候不止有一种查询的SQL写法，只要能查出需要的数据，就是正确的。

版权声明：除特殊说明，博客文章均为cuckooyang原创，依据CC BY-SA 4.0许可证进行授权，转载请附上出处链接及本声明。 | 博客订阅：RSS | 广告招租：留言板 | 博客VPS |
暂无评论

发送评论 编辑评论 


				

			

						

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
							
													

					

				

			

			
			

				

					
				

			

				

											

							
							
						

																					

							
							
						

										
					
											
						

	

		
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
（╯‵□′）╯︵┴─┴
￣﹃￣
(/ω＼)
∠( ᐛ 」∠)＿
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ｀)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ(￣∇￣o)
ヾ(´･ ･｀｡)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò｡)
Σ(っ °Д °;)っ
( ,,´･ω･)ﾉ"(´っω･｀｡)
╮(╯▽╰)╭
o(*////▽////*)q
＞﹏＜
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
	

	

		
颜文字
Emoji
小恐龙
花!
	


									

			

			
			
		

	






上一篇
下一篇