MySQL学习笔记-多表查询

多表查询

内连接

1). 隐式内连接

SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;

案例: 查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)
表结构: emp , dept
连接条件: emp.dept_id = dept.id
-- 为每一张表起别名,简化SQL编写
SELECT e.name,d.name FROM emp e , dept d WHERE e.dept_id = d.id;

 
2). 显式内连接

SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;

案例: 查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现) --- INNER JOIN ...
ON ...
表结构: emp , dept
连接条件: emp.dept_id = dept.id

SELECT e.name, d.name FROM emp e INNER JOIN dept d ON e.dept_id = d.id;
-- 为每一张表起别名,简化SQL编写
SELECT e.name, d.name FROM emp e JOIN dept d ON e.dept_id = d.id;

外连接

1). 左外连接

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

2). 右外连接

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

案例: 查询emp表的所有数据, 和对应的部门信息
表结构: emp, dept
连接条件: emp.dept_id = dept.id

SELECT e.*, d.name FROM emp e LEFT OUTER JOIN dept d ON e.dept_id = d.id;
SELECT e.*, d.name FROM emp e LEFT JOIN dept d ON e.dept_id = d.id;

案例: 查询dept表的所有数据, 和对应的员工信息(右外连接)
表结构: emp, dept
连接条件: emp.dept_id = dept.id

SELECT d.*, e.* FROM emp e RIGHT OUTER JOIN dept d ON e.dept_id = d.id;
SELECT d.*, e.* FROM dept d LEFT OUTER JOIN emp e ON e.dept_id = d.id;

自连接

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;

案例：查询员工 及其 所属领导的名字
表结构: emp

SELECT a.name , b.name FROM emp a , emp b WHERE a.managerid = b.id;

案例：查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来
表结构: emp a , emp b

SELECT a.name '员工', b.name '领导' FROM emp a LEFT JOIN emp b ON a.managerid =
b.id;

子查询

标量子查询

案例: 查询 "销售部" 的所有员工信息
完成这个需求时，我们可以将需求分解为两步：
①. 查询 "销售部" 部门ID

SELECT id FROM dept WHERE NAME = '销售部';

②. 根据 "销售部" 部门ID, 查询员工信息

SELECT * FROM emp WHERE dept_id = (SELECT id FROM dept WHERE NAME = '销售部');

案例: 查询在 "小白" 入职之后的员工信息
完成这个需求时，我们可以将需求分解为两步：
①. 查询 小白 的入职日期

SELECT entrydate FROM emp WHERE NAME = '小白';

②. 查询指定入职日期之后入职的员工信息

SELECT * FROM emp WHERE entrydate > (SELECT entrydate FROM emp WHERE NAME = '小白');

列子查询

案例: 查询 "销售部" 和 "市场部" 的所有员工信息
分解为以下两步:
①. 查询 "销售部" 和 "市场部" 的部门ID

SELECT id FROM dept WHERE NAME = '销售部' OR NAME = '市场部';

②. 根据部门ID, 查询员工信息

SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '销售部' OR NAME = '市场部');

案例: 查询比 财务部 所有人工资都高的员工信息

分解为以下两步:
①. 查询所有 财务部 人员工资

SELECT id FROM dept WHERE NAME = '财务部';

 SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE NAME = '财务部');       
②. 比 财务部 所有人工资都高的员工信息

SELECT * FROM emp WHERE salary > ALL ( SELECT salary FROM emp WHERE dept_id =
(SELECT id FROM dept WHERE NAME = '财务部') );

案例: 查询比研发部其中任意一人工资高的员工信息
分解为以下两步:
①. 查询研发部所有人工资

SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE NAME = '研发部');

②. 比研发部其中任意一人工资高的员工信息

SELECT * FROM emp WHERE salary > ANY ( SELECT salary FROM emp WHERE dept_id =
(SELECT id FROM dept WHERE NAME = '研发部') );

行子查询

案例: 查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;
这个需求同样可以拆解为两步进行:
①. 查询 "张无忌" 的薪资及直属领导

SELECT salary, managerid FROM emp WHERE NAME = '张无忌';

②. 查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;

SELECT * FROM emp WHERE (salary,managerid) = (SELECT salary, managerid FROM emp
WHERE NAME = '张无忌');

表子查询

案例: 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
分解为两步执行:
①. 查询 "鹿杖客" , "宋远桥" 的职位和薪资

 SELECT job, salary FROM emp WHERE NAME = '鹿杖客' OR NAME = '宋远桥';

 ②. 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息

 SELECT * FROM emp WHERE (job,salary) IN ( SELECT job, salary FROM emp WHERE NAME ='鹿杖客' OR NAME = '宋远桥' );

案例: 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
分解为两步执行:
①. 入职日期是 "2006-01-01" 之后的员工信息
 SELECT * FROM emp WHERE entrydate > '2006-01-01';

②. 查询这部分员工, 对应的部门信息 

SELECT e.*, d.* FROM (SELECT * FROM emp WHERE entrydate > '2006-01-01') e LEFT
JOIN dept d ON e.dept_id = d.id ;