SQL学习-表操作（多表行查询）

雇员表：t_employee

emp_ID	name	job	salary	dept_ID	leader_ID
001	张三1	职员	1000	01	002
002	张三2	主管	5000	01	011
003	张三3	职员	7000	02	005
004	张三4	职员	1500	01	002
005	张三5	主管	6000	02	011
006	张三6	职员	2000	02	005
007	张三7	主管	5000	03	011
008	张三8	职员	3000	02	005
009	张三9	职员	2000	02	005
010	张三10	职员	6000	03	007
011	张三11	董事长	2500	null	null
012	张三12	职员	4000	03	007

部门表：t_department

dept_ID	name	place
01	销售部	北京
02	宣传部	上海
03	设计部	广东
04	生产部	天津

一，内连接查询——inner join

注意：有则返，无则无。

与where条件子句的区别：多条件时

• 内连接、外连接，关键字可以多次书写。
• where，使用小括号、逻辑运算符（and，or）等连接，where关键字只能出现一次。

与外连接区别：多表时

• 内连接遵从最少原则。
• 外连接遵从一表原则（满）

1.1 自连接

语法：子表 inner join 父表 on 子表对应字段 = 父表ID
含义：子表与父表是同一张表

-- 需求：每个雇员的姓名、职位及其领导
-- 方法一：ANSI法
-- 查询
select e.name as '雇员姓名', l.name as '领导姓名'
-- 子表
from t_employee as e 
-- 父表
inner join t_employee as l 
-- 条件
on e.leader_ID = l.emp_ID;
-- 方法二：普通方法
select e.name as '雇员姓名', l.name as '领导姓名'
from t_employee as e,t_employee as l
where e.leader_ID = l.emp_ID;

1.2 等值连接

语法：子表 inner join 父表 on 字表对应字段 = 父表ID

-- 需求：每个雇员的编号、姓名、职位及其所属部门名称
-- 方法一：ANSI法
select 
e.emp_ID as '雇员编号', e.name as '雇员姓名', e.job as '雇员职位', d.name as '所属部门'
from t_employee as e 
inner join t_department as d 
on e.dept_ID = d.dept_ID;
-- 方法二：普通方法
select 
e.emp_ID as '雇员编号', e.name as '雇员姓名', e.job as '雇员职位', d.name as '所属部门'
from t_employee as e, t_department as d
where e.dept_ID = d.dept_ID;

1.3 不等值连接

语法：子表 inner join 父表 on 字表对应字段 比较运算符 父表ID
比较运算符：>、<、>=、<=、!=

-- 需求：薪资大于其部门领导的雇员的编号、姓名、职位及其所属部门名称
-- 方法一：ANSI法
select distinct
e.emp_ID as '雇员编号', e.name as '雇员姓名', e.job as '雇员职位', d.name as '所属部门'
from t_employee as e inner join t_department as d
on e.dept_ID = d.dept_ID 
inner join t_employee as l
on e.emp_ID = l.leader_ID and e.salary >l.salary ;
-- 方法二：普通方法
select distinct
e.emp_ID as '雇员编号', e.name as '雇员姓名', e.job as '雇员职位', d.name as '所属部门'
from t_employee as e,t_department as d,t_employee as l
where e.dept_ID = d.dept_ID and e.emp_ID = l.leader_ID and e.salary > l.salary ;

二、外连接查询

2.1 左连接

含义：返回左表所有的行
语法：左表 left join 右表 on 左表字段 = 右表id

-- 需求：每个雇员的编号、姓名、职位及其所属部门名称
-- 方法一：ANSI法
select 
e.emp_ID as '雇员编号', e.name as '雇员姓名', e.job as '雇员职位', d.name as '所属部门'
from t_employee as e 
left join t_department as d
on e.dept_ID = d.dept_ID;
-- 方法二：普通方法
select 
e.emp_ID as '雇员编号', e.name as '雇员姓名', e.job as '雇员职位', d.name as '所属部门'
from t_employee as e, t_department as d
where e.dept_ID = d.dept_ID;

2.2 右连接

含义：返回右表所有的行
语法：左表 right join 右表 on 左表id = 右表字段

-- 需求：每个雇员的编号、姓名、职位及其所属部门名称
-- 方法一：ANSI法
select 
e.emp_ID as '雇员编号', e.name as '雇员姓名', e.job as '雇员职位', d.name as '所属部门'
from t_department as d 
left join t_employee as e
on d.dept_ID = e.dept_ID;
-- 方法二：普通方法
select 
e.emp_ID as '雇员编号', e.name as '雇员姓名', e.job as '雇员职位', d.name as '所属部门'
from t_employee as e, t_department as d
where e.dept_ID = d.dept_ID;

三、合并查询——跳过

3.1 去重合并——union

含义：合并多个select查询结果，去除行值相同后合并
语法：select查询语句1 union select查询语句2 union select查询语句3

-- 需求：排序，部门领导在前，职员在后，的编号、姓名、职位及其所属部门名称
select 
e.emp_ID as '雇员编号', e.name as '雇员姓名', e.job as '雇员职位' , d.name as '所属部门'
from t_employee as e,t_department as d 
where
e.dept_ID = d.dept_ID
and
emp_ID  in(select distinct leader_ID from t_employee)
union
select 
e.emp_ID as '雇员编号', e.name as '雇员姓名', e.job as '雇员职位' , d.name as '所属部门'
from t_employee as e,t_department as d 
where 
e.dept_ID = d.dept_ID
and 
emp_ID not in(select distinct leader_ID from t_employee);

3.2 直接合并——union all

语法：同union。

四、子查询

4.1 where子句的子查询

4.1.1 单行单列

含义：指子查询的结果为单行单列，类似一个单元格。
语法：where 列名 运算符 (select 列名 from 表名 where 定表列名 = 定表列值)

-- 需求：薪资大于董事长的雇员的编号、姓名、职位及其所属部门名称
select
e.emp_ID as '雇员编号', e.name as '雇员姓名', e.job as '雇员职位', d.name as '所属部门'
from t_employee as e ,t_department as d 
where
e.dept_ID = d.dept_ID and  e.salary > (select salary from t_employee where job = '董事长');

4.1.2 单行多列

环境：小括号内有且仅有一值时，可使用。
含义：指子查询的结果为单行多列，类似一行（单元格数>1）。
语法：where 列名 运算符 (select 列1名,列2名 from 表名 where 定表列名 = 定表列值)

-- 需求：薪资、职位与张三6相同的雇员的编号、姓名、职位及其所属部门名称
select
e.emp_ID as '雇员编号', e.name as '雇员姓名', e.job as '雇员职位', d.name as '所属部门'
from t_employee as e ,t_department as d 
where
e.dept_ID = d.dept_ID and  (e.salary,e.job) = (select salary,job from t_employee where name = '张三6');

4.1.3 多行单列

含义：指子查询的结果为多行单列，类似一列（单元格数>1）。

4.1.3.1 in | not in

环境：小括号内至少一值，并且比（不）选一值时，可使用。
含义：主查询的条件是（不是）子查询的结果时用。
语法：where 列名 in(select 列名 from 表名 where 定表列名 = 定表列值)

-- 需求：薪资等于主管的雇员的信息。
-- 获取主管的薪资
select emp_ID,salary from t_employee where job = '主管';
-- 结果：002(5000)、005(6000)、007(5000)。最小值为5000，最大值为6000。
-- 子查询
select emp_ID,salary from t_employee as e 
where e.salary in(select salary from t_employee where job = '主管');
-- 结果：002(5000)、005(6000)、007(5000)。010(6000)。

4.1.3.2 any

环境：小括号内至少一值，并且与其中值比较时，可使用。
含义：主查询的条件满足子查询结果中的任意一条。
语法：where 列名 运算符 any(select 列名 from 表名 where 定表列名 = 定表列值)

运算符	含义
=	含义同 in
>= （ >）	不小于子查询结果的最小值
<= （<）	不大于子查询结果的最大值

-- 需求：薪资不小于主管的雇员的信息。
-- 获取主管的薪资
select emp_ID,salary from t_employee where job = '主管';
-- 结果：002(5000)、005(6000)、007(5000)。最小值为5000，最大值为6000。
-- 子查询
select emp_ID,salary from t_employee as e 
where
e.salary >= any(select salary from t_employee where job = '主管');
-- 结果：002(5000)、005(6000)、007(5000)。003(7000)、010(6000)。

4.1.3.3 all

环境：小括号内至少一值，并且与其中值比较时，可使用。
含义：主查询的条件满足子查询结果中的所有
语法：where 列名 运算符 all(select 列名 from 表名 where 定表列名 = 定表列值)

运算符	含义
>= （ >）	不小于子查询结果的最大值
<= （<）	不大于子查询结果的最小值

-- 需求：薪资不小于主管的雇员的信息。
-- 获取主管的薪资
select emp_ID,salary from t_employee where job = '主管';
-- 结果：002(5000)、005(6000)、007(5000)。最小值为5000，最大值为6000。
-- 子查询
select emp_ID,salary from t_employee as e 
where
e.salary >= all(select salary from t_employee where job = '主管');
-- 结果：005(6000)。003(7000)、010(6000)。

4.1.3.4 exists | not exists

含义：筛选出与子查询结果相同（不同）的结果。
语法：where exists（not exists）(select 子查询列名 from 子查询表名 where 子查询字段名 = 主查询表名.列名)

-- 需求：找出有（没有）员工的部门。
-- 根据符合子查询条件，选出结果。
select * from t_department
where
-- 剔除相同值，留取不同值。即为子查询结果。
not exists(
-- 找到两表相同值。
select * from t_employee where t_employee.dept_ID = t_department.dept_ID);

4.2 from子句的子查询

即为：多行多列
含义：指子查询的结果为多行多列。即子查询的结果为一张表，并通过字段与已知表的ID相关联。
语法：from 父表 inner join (select 关联字段, 新字段1,新字段2 from 已知表) as 子表(新表) on 父表和子表关联条件。

-- 需求：查询部门ID、部门名称、部门位置、雇员人数和平均工资。
select
d.dept_ID, d.name, d.place, t_new.number, t_new.average
from t_department as d 
-- 3、使用内连接与已知表连接。
inner join 
-- 1、从已知表中获取未显现的信息，组成一张表,并命名。
( select dept_ID, count(emp_ID) as number, avg(salary) as average
from t_employee group by dept_ID DESC) as t_new
on 
-- 2、新表与已知表关联条件：新表字段=已知表ID。
d.dept_ID = t_new.dept_ID;