当前位置: 首页 > news >正文

多表查询综合归纳

目录

1. 多表关系

1.1 一对多(多对一)

1.2 多对多

 1.3 一对一

2. 多表查询概述

2.1 熟悉表

2.2 笛卡尔积

2.3 消除笛卡尔积

2.4 多表查询分类

3. 内连接

3.1 隐式内连接

3.2 显式内连接

4. 外连接

4.1 左外连接

4.2 右外连接

5. 自连接

5.1 自连接查询 

5.2 外连接查询

6. 联合查询

6.1 union all 

6.2 union

6.3 使用联合查询条件

7. 嵌套/子查询

7.1 标量子查询

7.2 列子查询

1) in 的使用

2)all 的使用

3) any 的使用

7.3 行子查询

7.4 表子查询

8. 多表查询案例

9. 总结

9.1 多表关系

 9.2 多比查询

​编辑

 10 源代码(复制自取)


1. 多表关系

1.1 一对多(多对一)

1.2 多对多

 1.3 一对一

2. 多表查询概述

 

2.1 熟悉表

在接下来的学习中,我们暂且需要使用这两张表,因此,十分有必要熟悉表的结构及其内容;

1. 员工表

2. 部门表

2.2 笛卡尔积

2.3 消除笛卡尔积

在上述多表查询中,可以查到这样的元组。

 显然,有许多信息是我们不需要的,接下来就需要过滤信息,消除笛卡尔积;

2.4 多表查询分类

3. 内连接

内连接又分为隐式内连接和显示内连接;

3.1 隐式内连接

先执行 from ,因此表取别名后,仅允许使用表名;

3.2 显式内连接

显示内连接中, innet 常省略不写;

4. 外连接

外连接分为左外连接和右外连接

此外,左外连接可以改为右外连接,右外连接可以改为左外连接;

4.1 左外连接

左外连接中,out 通常省略;

4.2 右外连接

5. 自连接

5.1 自连接查询 

其使用如下案例所示:

5.2 外连接查询

6. 联合查询

6.1 union all 

这是薪资低于 5000 的员工信息

这是年龄大于 50 的员工信息

这是将两个条件的查询结果联合起来的表

6.2 union

union 可以去除重复;

6.3 使用联合查询条件

7. 嵌套/子查询

7.1 标量子查询

7.2 列子查询

 

1) in 的使用

下面举例演示:

2)all 的使用

 

 

3) any 的使用

7.3 行子查询

7.4 表子查询

再看第二个例子;

8. 多表查询案例

注意:以下查询并非只有一种方式;

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

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

3. 查询拥有员工的部门ID,部门名称(6号部门没有员工)

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

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

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

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

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

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

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

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

9. 总结
9.1 多表关系

 9.2 多表查询

 此外,较惋惜的是,本篇未涉及 having,group by ,order by 的使用以及SQL 语句的执行顺序;

 10 源代码(复制自取)
--创建数据库
create database mul_table_demo;--使用数据库
use mul_table_demo;--创建表 department
create table department(de_id smallint primary key,  --部门idde_name varchar(20)			 --部门名称
);--插入表 department
insert into department values(1,'研发部'),(2,'市场部'),(3,'财务部'),(4,'销售部'),(5,'总经办'),(6,'人事部');--创建表 employee
create table employee (em_id smallint primary key,                  --员工IDem_name varchar(20),						 --员工名字age smallint check (age between 18 and 65),  --年龄job varchar(20),							 --工作salary int not null,						 --薪水entrydate date,								 --入职日期manager_id smallint,						 --员工的领导IDdept_id smallint foreign key references department(de_id)--部门ID 
);--插入表 employee
insert into employee values								--在将 varchar 值 '*' 转换成数据类型 int 时失败。表示数据类型弄错了
(1, '金庸',  65,'总裁'    ,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,'会计'    ,4800, '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, '2000-01-01',10,2),
(14,'张三丰',65,'销售总监',14000,'2009-02-12',1, 4),
(15,'俞莲舟',38,'销售',    4600, '2004-10-12',14,4),
(16,'宋远桥',40,'销售',    4600, '2004-10-12',14,'4'),	-- 4 or '4':加引号不加引号都是可以的;
(17,'陈友谅',42,null,    2000, '2010-01-01',1, null);	--null不属于任何数据类型,因此外键可以插入 null ,char/int 数据类型都可以插入 null--多表查询:笛卡尔积
select * from employee,department;--消除笛卡尔积
select * from employee,department where employee.dept_id = department.de_id;--内连接
--隐式内连接
--查询每个员工的姓名及关联的部门名称
select em.em_name as '员工姓名',de.de_name as '部门名称' 
from employee as em ,department as de 
where em.dept_id = de.de_id;--显式内连接
--查询每个员工的姓名及关联的部门名称
select em.em_name,de.de_name 
from employee as em 
join department as de
on em.dept_id = de.de_id--外连接
--左外连接
--查询 emp 表的全部数据及对应的部门信息
select em.*,de.de_name
from employee as em 
left join department as de 
on em.dept_id = de.de_id--右外连接
--查询 dept 表的所有信息,和对应的员工信息
select de.de_id,de.de_name,em.* 
from employee as em 
right join department as de
on em.dept_id = de.de_id--自连接
select * from employee;
--查询员工及其领导的名字
select b.em_name as '员工姓名', a.em_name as '领导姓名' 
from employee as a,employee as b
where a.em_id = b.manager_id;--查询所有员工及其领导的名字,如果员工没有领导,也要查询出来
select * from employee;
selectb.em_name  as '员工姓名' ,a.em_name  as '领导姓名'
from employee as a 
right join employee as b
on a.em_id = b.manager_id; --联合查询
--查询薪资低于 5000 或年龄大于 50 岁的员工的信息(union all)select * from employee as e where e.salary < 5000
union all
select * from employee as e where e.age > 50--查询薪资低于 5000 或年龄大于 50 岁的员工的信息(union)
select * from employee as e where e.salary < 5000
union 
select * from employee as e where e.age > 50select * from employee as e where e.salary < 5000 or e.age > 50;--子查询
--标量子查询
--查询“销售部”所在员工的全部信息--1. 查询“销售部”部门ID
select de_id from department as de where de.de_name ='销售部';
--2. 根据部门ID,查询“员工表”员工信息
select * from employee as em where em.dept_id = 4;
--使用子/嵌套查询,将上述结果整合起来
select * from employee as em 
where em.dept_id = (select de.de_id from department as de where de.de_name ='销售部'
);--查询“方东白”入职之后的员工信息
--方东白的入职日期
select e.entrydate from employee as e where e.em_name='方东白';
--根据方东白的入职日期,查询员工信息
select * from employee as em where em.entrydate > '2000-01-01';
----使用子/嵌套查询,将上述结果整合起来
select * from employee as em where em.entrydate > (select e.entrydate from employee as e where e.em_name='方东白');--列子查询
--查询“销售部”和“市场部”的员工信息
--1. 查询“销售部”和“市场部”的部门ID
select de.de_id from department as de where de.de_name in ('销售部','市场部'); 
--2. 查询根据“销售部”和“市场部”的部门ID,查询其员工信息
select * from employee as em where em.dept_id in (2,4);
--使用子/嵌套查询,将上述结果整合起来
select * from employee as em where em.dept_id in (select de.de_id from department as de where de.de_name in ('销售部','市场部')
);--查询比财务部所有人员工资都高的员工信息
--1. 查询财务部的人员工资
select e.salary 
from employee as e 
where e.dept_id in (select d.de_id from department as d where d.de_name = '财务部');
--2. 根据财务部的人员工资,查询比其工资都高的员工信息
select * from employee as e where e.salary > 8500;
--使用子/嵌套查询,将上述结果整合起来(all)
select * from employee as e where e.salary > all(select e.salary from employee as e where e.dept_id in (select d.de_id from department as d where d.de_name = '财务部')
);--查询比研发部其中任意一人工资高的员工信息
--1. 查询研发部人员的工资
select e.salary 
from employee as e 
where e.dept_id in (select d.de_id from department as d where d.de_name = '研发部'); 
--2.查询比其任意一人工资高的员工信息
select * from employee as e where e.salary > 6600;
----使用子/嵌套查询,将上述结果整合起来( any)
select * from employee as e where e.salary > any (select e.salary from employee as e where e.dept_id in (select d.de_id from department as d where d.de_name = '研发部')
);--行子查询
--查询 张无忌的薪资及直属领导相同 的员工信息
--1.查询张无忌的薪资
select e.salary,e.manager_id from employee as e where e.em_name = '张无忌';
--2.根据查询结果,查询员工信息
select * from employee as e where e.salary = 12500 and e.manager_id = 1;
--使用子/嵌套查询,将上述结果整合起来
select * from employee as e where (e.salary,e.manager_id) =(select e.salary,e.manager_id from employee as e where e.em_name = '张无忌');--表子查询
--查询“鹿仗客”,“宋远桥”的职位和薪资相同的员工信息
select * from employee as e where (e.salary,e.job) in (select e.salary,e.job from employee as e where e.em_name in ('鹿仗客','宋远桥'));--查询入职日期是“2006-01-01”之后的员工信息,及其部门信息
select e.*,d.* 
from ( select * from employee as e where e.entrydate > '2006-01-01') as e ,department as d
wheree.dept_id = d.de_id;--案例巩固
select * from employee;
select * from department;
select * from salgrade;
--在创建一张表
create table salgrade(grade int,			--薪资等级losal int,			--最低薪资hisal int			--最高薪资
)
insert into salgrade values(1,0,3000),(2,3001,5000),(3,5001,8000),(4,8001,10000),(5,10001,15000),(6,15001,20000),(7,20001,25000),(8,25001,3000);--1. 查询员工的姓名,年龄,职位,部门信息;
select e.em_name,e.age,e.job,d.de_name 
from employee as e 
left join department as d 
on e.dept_id = d.de_id ; 
--2. 查询年龄小于30岁的员工的姓名,年龄,职位,部门信息
select e.em_name,e.age,e.job,d.de_name 
from employee as e 
left join department as d 
on e.dept_id = d.de_id 
where e.age < 30;--3. 查询拥有员工的部门ID,部门名称(6号部门没有员工)
--方法1:子查询
select distinctd.de_id,d.de_name 
from (select e.dept_id from employee as e) as em ,department as d
where em.dept_id = d.de_id;
--方法2:内连接
select distinctd.de_id,d.de_name 
from employee as e,department as d 
where e.dept_id = d.de_id; --4. 查询所有年龄大于40岁的员工,及其所属部门名称,如果员工没有分配部门,也需要展示出来;
select e.*,d.de_name 
from	employee as e 
left join department as d 
on e.dept_id = d.de_id 
where e.age > 40; --5. 查询所有员工的工资等级(薪水等级表没有外键,如何连接表是关键)
select * 
from employee as e ,salgrade as s 
where e.salary between s.losal and s.hisal; --6. 查询研发部所有员工的信息及工资等级
select e.*,s.grade 
from (select * from employee as e join department as d on e.dept_id = d.de_id where d.de_name = '研发部') as e ,salgrade as s 
where e.salary between s.losal and s.hisal;--7. 查询“研发部“员工的平均工资
select avg(e.salary)
from employee as e 
join department as d 
on e.dept_id = d.de_id 
whered.de_name = '研发部'--8. 查询工资比”灭绝“高的员工信息
select * from 
employee as e 
where e.salary > ( select e.salary from employee as e where e.em_name = '灭绝');--9. 查询比平均工资高的员工信息
select * from 
employee as e 
where e.salary > ( select avg(salary) from employee);--10. 查询低于本部门平均工资的员工信息
select e2.*,(select avg(e1.salary) from employee as e1 where e1.dept_id = e2.dept_id ) as '平均' 
from employee as e2 
where e2.salary < (select avg(e1.salary) from employee as e1 where e1.dept_id = e2.dept_id ) ;--11. 查询所有部门信息,并统计部门员工人数
select count(*) from employee as e where e.dept_id = 6;
select d.de_id,d.de_name,(select count(*) from employee as e where e.dept_id = d.de_id) as '人数' 
from department as d;
http://www.lryc.cn/news/484961.html

相关文章:

  • 【5.线性表-链式表示-王道课后算法题】
  • 存储过程及练习
  • 【在Linux世界中追寻伟大的One Piece】多路转接epoll
  • 设计模式-参考的雷丰阳老师直播课
  • Python +Pyqt5 简单视频爬取学习(一)
  • Python Requests模块全面教程
  • PyQt入门指南六十 与Python其他库的集成方法
  • Android15之解决:Dex checksum does not match for dex:framework.jar问题(二百三十九)
  • 车企自动驾驶功能策略 --- 硬件预埋(卷传感器配置)
  • 【已为网站上传证书,却显示不安全】
  • docker busybox作为initContainers
  • 20.UE5UI预构造,开始菜单
  • Electron教程1-初学入门
  • 从北美火到中国,大数据洞察品牌“STANLEY”的突围之路
  • 深度学习之GAN应用
  • 鸿蒙生态下的安全隐私保护:打造用户信任的应用体验
  • 用pandoc工具实现ipynb,md,word,pdf之间的转化
  • 第三十一天|贪心算法| 56. 合并区间,738.单调递增的数字 , 968.监控二叉树
  • 力扣 最长公共前缀-14
  • IDEA调整警告级别【IntelliJ IDEA 2024.2.0.1】
  • Vulnhub靶场 Billu_b0x 练习
  • Essential Cell Biology--Fifth Edition--Chapter one (6)
  • Jupyter Book 快捷键总结大全
  • Spring Authorization Server OAuth2.1
  • 解决”重复文件名重命名“问题【根据Word系统方式】
  • 【PyTorch】PyTorch Geometric(PyG)安装指南:如何高效配置图神经网络环境
  • SolidWorks21装配体中一个零件无法改为线架图
  • 11.11机器学习_介绍和定义
  • 【代码审计】常见漏洞专项审计-业务逻辑漏洞审计
  • SpringBoot单体服务无感更新启动,动态检测端口号并动态更新