数据库【多表查询】

最新推荐文章于 2023-03-31 14:59:52 发布

Wy_编程

最新推荐文章于 2023-03-31 14:59:52 发布

阅读量895

点赞数

分类专栏：数据库文章标签：数据库 sql database

本文链接：https://blog.youkuaiyun.com/weixin_48119140/article/details/124646769

版权

数据库专栏收录该内容

15 篇文章

订阅专栏

本文详细介绍了SQL中的多表查询，包括等值连接、不等值连接、外连接（左外连接和右外连接）、自连接的使用方法。此外，还讲解了子查询的应用，如同表子查询、不同表子查询以及在select、from、where、having后的使用，并探讨了单行和多行子查询。最后，讨论了集合运算，如并集、交集、差集及其注意事项。

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

文章目录

多表查询

笛卡尔积
笛卡尔积就是两个集合的乘积计算。

在多表查询, 得到的结果中:
- 列数: 多个表的列的和
- 行数: 多个表行数的乘积
  - 里边有无效的数据

等值/不等值连接

从概念上，区分等值连接和不等值连接非常简单，只需要辨别where子句后面的条件:

是 “=” 为等值连接
不是 “=” 为不等值连接。

等值连接

示例：

查询员工信息：员工号，姓名，月薪(在emp表中)和部门名称(在dept表中)
-- 直接使用表名
select emp.deptno, emp.ename, emp.sal, dept.dname from emp, dept where emp.deptno=detp.deptno;

-- 给表名设置别名
select e.deptno, e.ename, e.sal, d.dname from emp e, dept d where e.deptno=d.deptno;

不等值连接

查询员工信息：员工号, 姓名, 月薪 和 月薪级别(salgrade表)  
SQL> select e.empno, e.ename, e.sal, g.grade  from emp e, salgrade g where sal>=g.losal and sal <=g.hisal;

--第二种方式
SQL> select e.empno, e.ename, e.sal, g.grade  from emp e, salgrade g where sal between g.losal and g.hisal;

外连接

使用select, 在最后的查询结果中，如果想要包含某些对于where条件来说不成立的记录, 我们可以使用外连接

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

在左连接和右连接时都会以一张A表为基础表，该表的内容会全部显示，然后加上A表和B表匹配的内容。如果A表的数据在B表中没有记录。那么在相关联的结果集行中列显示为空值（NULL）。

对于外连接，可以使用“(+) ”来表示。关于使用（+）的一些注意事项：

（+）操作符只能出现在WHERE子句中。
当使用（+）操作符执行外连接时，如果在WHERE子句中包含有多个条件，则必须在所有条件中都包含（+）操作符。
（+）操作符只适用于列，而不能用在表达式上。
（+）操作符不能与 OR 和 IN 操作符一起使用。
（+）操作符只能用于实现左外连接和右外连接，而不能用于实现完全外连接。

左外连接 `(左边的表不加限制)`

用（+）来实现，这个+号可以这样来理解： + 表示补充，即哪个表有加号，这个表就是匹配表。如果加号写在右表，左表就是全部显示，所以是左连接。

示例

SQL> select d.deptno, d.dname, count(e.deptno) from emp e, dept d where d.deptno=e.deptno(+) group by d.deptno, d.dname;

右外连接`(右边的表不加限制)`

用（+）来实现，这个+号可以这样来理解： + 表示补充，即哪个表有加号，这个表就是匹配表。如果加号写在左表，右表就是全部显示，所以是右连接。

示例：

SQL> select d.deptno, d.dname, count(e.deptno) from emp e, dept d where d.deptno=e.deptno(+) group by d.deptno, d.dname;

自连接

自连接: 通过给当前表设置不同的别名，将同一张表视为多张表来使用, 进行多表联查。

实列

-- 显示尾田和岸本
SQL> select e.ename "员工名", nvl(b.ename, '少年jump') "领导"  from emp e, emp b where e.mgr=b.empno(+);

子查询

子查询就是select 语句的嵌套使用, 即: 在select中使用select。表示子语句的select需要写在()中

同表子查询

--需求：查询比 路飞 工资高的员工信息 
思路分析：
-- 1. 查询路飞的工资
select sal from emp where ename like '%路飞%';
-- 2. 查找比路飞工资高的人
select * from emp wehre sal > 路飞的工资;

--子查询语句
SQL> select * from emp where sal > (select sal from emp where ename like '%路飞%');

不同表子查询

需求：查询部门名称是 草帽海贼团 的员工信息
思路分析：
-- 1. 根据名字查询部门编号
select deptno from dept where dname='草帽海贼团';
-- 2. 根据部门编号查询员工信息
select * from emp where deptno=查询到的部门编号;

-- 方法1: 使用子查询
select * from emp where deptno=(select deptno from dept where dname='草帽海贼团');

-- 方法2: 使用多表查询
select * from emp e, dept d where e.deptno=d.deptno and d.dname='草帽海贼团';

在select、from、where、having后边使用子查询

在select后使用子查询

需求:查询20号部门的员工号、员工姓名、部门编号、部门名称
-- 部门名称在 dept表
-- 员工号、员工姓名、部门编号 emp表
select empno, ename, deptno, (select dname from dept where deptno=20) from emp where deptno=20;

在from后使用子查询

在使用select进行查询的时候, from关键字值可以`指定表名`或者`一个结果集`即查询的结果. 子查询的结果就是一个结果集, 因此可以将子查询直接写到from的后边.

-- 填空: 查询员工的姓名, 薪水和年薪, 在emp表中
select * from _____?

select * from (select ename, sal, sal*12 from emp);

where后使用子查询

查询比平均工资高的员工信息
-- 平均工资
select ename, sal from emp where sal>(select avg(sal) from emp);

在having后使用子查询

查询部门的平均薪资, 并且该部门的平均薪资高于30号部门的平均薪资
-- 1. 查询30号部门的平均薪资
select avg(sal) from emp where deptno=30;
-- 2. 查询所有部门的平均薪资, 和30号部门的进行比较
select deptno, avg(sal) from emp group by deptno having avg(sal) > 30号部门的平均工资;

select deptno, avg(sal) from emp group by deptno having avg(sal) > (select avg(sal) from emp where deptno=30);

查询部门的最低月薪并且该部门最低月薪高于50号部门的最低月薪
select deptno, min(sal) from emp group by deptno having min(sal) > (select min(sal) from emp where deptno=50);

单行/多行子查询

单行子查询

单行子查询就是该条子查询执行结束时，只返回一条记录(一行数据)。

在当行子查询中只能使用单行操作符: 使用单行操作符：=、>、>=、<、<=、<>

示例

--查询工资比 路飞 工资高的员工的信息
select * from emp where sal >(select sal from emp where ename like '%路飞%');

多行子查询

多行子查询就是该条子查询执行结束时，只返回多条记录(多行数据)。

多行操作符有：

IN: 等于列表中的任意一个
ANY: 和子查询返回的某一个值比较
ALL: 和子查询返回的所有值比较

与每个比较运算符一起使用时的含义：

条件	表示含义
`c = ANY (…)`	`c`列中的值必须与集合中的一个或多个值匹配，以评估为`true`。
`c != ANY (…)`	`c`列中的值不能与集合中的一个或多个值匹配以评估为`true`。
`c > ANY (…)`	`c`列中的值必须大于要评估为`true`的集合中的最小值。
`c < ANY (…)`	`c`列中的值必须小于要评估为`true`的集合中的最大值。
`c>= ANY (…)`	`c`列中的值必须大于或等于要评估为`true`的集合中的最小值。
`c <= ANY (…)`	`c`列中的值必须小于或等于要评估为`true`的集合中的最大值。

下表说明了SQL ALL运算符的含义：

条件	描述
`c > ALL(…)`	`c`列中的值必须大于要评估为`true`的集合中的最大值。
`c >= ALL(…)`	`c`列中的值必须大于或等于要评估为`true`的集合中的最大值。
`c < ALL(…)`	`c`列中的值必须小于要评估为`true`的集合中的最小值。
`c <= ALL(…)`	`c`列中的值必须小于或等于要评估为`true`的集合中的最小值。
`c <> ALL(…)`	`c`列中的值不得等于要评估为`true`的集合中的任何值。
`c = ALL(…)`	`c`列中的值必须等于要评估为`true`的集合中的任何值。

示例

查询部门名称为 红心海贼团 和 红发海贼团 的员工信息
思路分析：
-- 根据部门名称查询部门编号
select deptno from dept where dname='红心海贼团' or dname = '红发海贼团';
-- 查询emp表满足条件的员工信息
select * from emp where deptno =(部门编号);

-- xxx or yyy or zzz == in(xxx, yyy, zzz)

-- 版本1
select *
  from emp
 where deptno in (select deptno
                    from dept
                   where dname = '红心海贼团'
                      or dname = '红发海贼团');

-- 版本2
select *
  from emp
 where deptno in
       (select deptno from dept where dname in ('红心海贼团', '红发海贼团'));

查询所有月薪比30号部门薪资最低者的工资高的员工信息
-- 查询30号部门的最低工资
select min(sal) from emp where deptno=30;

-- 比30号部门工资最低者工资高的员工信息
select * from emp where sal>(30号部门的最低工资);

-- 单行子查询
select * from emp where sal>(select min(sal) from emp where deptno=30);

-- 多行子查询
select * from emp where sal>any(select sal from emp where deptno=30);

查询比30号部门所有员工工资都高的员工信息
-- 查询30号部门的最高工资
select max(sal) from emp where deptno=30;

-- 查询30号部门的最高工资
select max(sal) from emp where deptno=30;

   -- 单行子查询
   select * from emp where sal>(select max(sal) from emp where deptno=30);
   -- 多行子查询
   select * from emp where sal>all(select sal from emp where deptno=30);

子查询中的NULL

判断一个值等于、不等于空，不能使用=和!=号，而应该使用is 和 not。

如果集合中有NULL值, 不能使用not in。例如：not in (10, 20, NULL)，但是可以使用 in

字段有空如何判断:
空: is null,
非空: is not null

示例：

查询不是管理者的员工信息。

-- 字段有空如何判断:
	-- 空: is null, 非空: is not null
	
-- 得到所有的管理者的 编号
-- mgr是员工的编号
select distinct mgr from emp;
-- 查询员工的编号, 这个编号不在管理者集合中就是普通员工
select * from emp where empno not in(管理者集合);

-- 子查询的结果集 (111, 222, ..., NULL)
-- in(xx, yy, xzz) == xx or yy or zz
-- not in(xx, yy, zz) == !=xx and != yy and != zz and !=null
select * from emp where empno not in(select distinct mgr from emp where mgr is not null);

集合运算

交集、并集、差集

关键字: 并集: union, 全并集: union all, 交集: intersect, 差集: minus

比如说有集合 A ( 1 , 2 , 3 ) ， B ( 2 , 3 , 4 ):

A∪B（取并集） = ( 1, 2, 3, 4），如果是全并集就是（1, 2, 3, 2, 3, 4）
A∩B （取交集） = ( 2, 3 )
A – B（取差集） = ( 1 ) ，B – A = （4）

示例：

-- 集合运算是多个结果集共同进行运算
-- 并集
select * from emp where deptno=10 union select * from emp where deptno=20;
-- 全并集
select * from emp where deptno=10 union all select * from emp where deptno in(10,20);
--交集
select * from emp where deptno=10 intersect select * from emp where deptno in(10,20);
-- 差集
select * from emp where deptno in(10,30) minus select * from emp where deptno in(10,20);
select * from emp where deptno in(10,20) minus select * from emp where deptno in(10,30);

集合使用的注意事项

参与运算的各个集合必须列数相同，且类型一致。

-- 错误的写法
-- 列数不一致
select empno, ename from emp where deptno=10 union select * from emp where deptno=20;
-- 列的类型不一致
select empno, ename from emp where deptno=10 union select ename, empno from emp where deptno=20;

采用第一个集合的表头作为最终使用的表头


select empno "员工编号", ename "员工姓名" from emp where deptno=10 union select empno "编号", ename "姓名" from emp where deptno=20;

可以使用括号()先执行后面的语句。

select empno "员工编号", ename "员工姓名" from emp where deptno=10 union (select empno "编号", ename "姓名" from emp where deptno=20);