[大数据]数据库【多表查询】

多表查询

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

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

等值/不等值连接

从概念上，区分等值连接和不等值连接非常简单，只需要辨别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）。

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

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

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

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

示例

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: 和子查询返回的所有值比较

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

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

示例

查询部门名称为 红心海贼团 和 红发海贼团 的员工信息
思路分析：
-- 根据部门名称查询部门编号
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);

创作打卡挑战赛

赢取流量/现金/CSDN周边激励大奖