SQL-多表查询

多表关系

• 一对多（多对一）
  • 在多的一方建立外键，指向一的一方的主键。
• 多对多
  •  建立第三张中间表，中间表至少包含两个外键，分别关联两方主键。
• 一对一
  • 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的。（UNIQUE）

多表查询概述

笛卡尔积：在数学中，两个集合A集合和B集合的所有组合情况。

---笛卡尔积
SELECT *
FROM emp，dept;
--消除笛卡尔积
SELECT * 
FROM emp,dept 
WHERE emp.dept_id = dept.id;
--不满足连接条件，就不会查询出来

内连接

相当于查询A、B交集部分数据

• 隐式内连接

        SELECT 字段列表 FROM 表1，表2 WHERE 条件

        表结构：emp，dept

        连接条件：emp.dept_id=dept.id

SELECT * 
FROM emp, dept
WHERE emp.dept_id = dept_id;

• 显式内连接 

         SELECT 字段列表 FROM 表1  JOIN 表2 ON 连接条件

SELECT *
FROM emp e 
INNER JOIN dept p
ON e.dept_id = d.id;

 外连接

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

         SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ...;

        相当于查询表1的所有数据，包含表1和表2交集部分数据

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

        SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ...;

-- 左
SELECT e.*, d.name 
FROM employee  e 
LEFT [OUTER] JION dept  d 
ON e.dept = d.id;

-- 右
select d.name, e.* 
from employee  e 
right outer join dept d 
on e.dept = d.id;

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

--查询员工及其所属领导的名字
SELECT a.name, b.name
FROM emp a, emp b
WHERE a.mangerid= b.id;
--查询所有员工及其领导名字，如果员工没有领导，也需要查询出来
SELECT a.name "员工", b.name “领导”
FROM emp a
LEFT JOIN emp b
ON a.mangerid= b.id; 

联合查询

语法：

1. SELECT 字段列表 FROM 表A ...
2. UNION [ALL]
3. SELECT 字段列表 FROM 表B ...

--薪资低于5000和年龄大于50的员工全部查询出来（直接合并）
SELECT * 
FROM emp
WHERE salary < 5000
UNION ALL
WHERE *
FROM emp
WHERE age > 50;
----薪资低于5000和年龄大于50的员工全部查询出来（合并后去重）
SELECT * 
FROM emp
WHERE salary < 5000
UNION 
WHERE *
FROM emp
WHERE age > 50;

对于联合查询的多张表的列表必须保持一致，字段类型也需要保持一致。

UNION ALL 会将全部的数据直接合并在一起，UNION会对合并之后的数据去重。

子查询

SQL语句中嵌套SELECT语句，称谓嵌套查询，又称子查询。

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2);

子查询外部的语句可以是 INSERT / UPDATE / DELETE / SELECT 的任何一个

根据子查询结果可以分为：

• 标量子查询（子查询结果为单个值）

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

-- 查询销售部所有员工
SELECT id 
FROM dept 
WHERE name = '销售部';
-- 根据销售部部门ID，查询员工信息
SELECT * 
FROM employee 
WHERE dept = 4;
-- 合并（子查询）
SELECT * 
FROM employee 
WHERE dept = (SELECT id FROM dept WHERE name = '销售部');
-- 查询xxx入职之后的员工信息
SELECT * 
FROM employee 
WHERE entrydate > (SELECT entrydate FROM employee WHERE  name = 'xxx');

• 列子查询（子查询结果为一列）

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

        常用操作符： IN ; NOT IN ; ANY ; SOME; ALL

操作符	描述
IN	在指定的集合范围内，多选一
NOT IN	不在指定的集合范围内
ANY	子查询返回列表中，有任意一个满足即可
SOME	与ANY等同，使用SOME的地方都可以使用ANY
ALL	子查询返回列表的所有值都必须满足

--查询销售部和市场部的部门ID
SELECT id
FROM dept
WHERE name = '销售部' OR name = '市场部';
--根据部门ID，查询员工信息
SELECT * 
FROM emp 
WHERE dept in (2,4)
-- 查询销售部和市场部的所有员工信息
select * from employee where dept in (select id from dept where name = '销售部' or name = '市场部');


-- 查询比财务部所有人工资都高的员工信息
select * from employee where salary > all(select salary from employee where dept = (select id from dept where name = '财务部'));
-- 查询比研发部任意一人工资高的员工信息
select * from employee where salary > any (select salary from employee where dept = (select id from dept where name = '研发部'));

• 行子查询（子查询结果为一行）

        返回的结果是一行（可以是多列）。
       常用操作符：=, <, >, IN, NOT IN

-- 查询与xxx的薪资及直属领导相同的员工信息
select * from employee where (salary, manager) = (12500, 1);
select * from employee where (salary, manager) = (select salary, manager from employee where name = 'xxx');

• 表子查询（子查询结果为多行多列）

        

-- 查询与xxx1，xxx2的职位和薪资相同的员工
select * from employee where (job, salary) in (select job, salary from employee where name = 'xxx1' or name = 'xxx2');


-- 查询入职日期是2006-01-01之后的员工，及其部门信息
--a.入职日期是“2006-01-01”之后的员工信息
SELECT *
FROM emp
WHERE entrydate > '2006-01-01';
--b.查询这部分员工，对应的部门信息
select e.*, d.* from (select * from employee where entrydate > '2006-01-01') as e left join dept as d on e.dept = d.id;

根据子查询位置可分为：

• WHERE 之后
• FROM 之后
• SELECT 之后

多表查询案例

salgrade

emp

--查询所有员工的工资等级
--表 emp,salgrade
--连接条件：emp.salary >= salgrade.losal and emp.salary <= salgrade.hisal

SELECT e.*, s.grade, s.hisal 
FROM emp e, salgrade s 
WHERE e.salary >= s.losal AND e.salary <= s.hisal;

SELECT e.*, s.grade, s.hisal 
FROM emp e, salgrade s 
WHERE e.salary between s.losal AND s.hisal;

--查询研发部所有员工的信息及工资等级
--表 emp，salgrade，dept
--连接条件：emp.salary between salgrade.losal and salgrade.hisal, emp.dept_id = dept.id
--查询条件：dept.name = '研发部'
SELECT e.*,s.grade 
FROM emp e, dept d, salgrade s 
WHERE e.dept_id = d.id AND (e.salary between s.local and s.hisal) AND d.name='研发部';