mysql多表查询

内连接

查询两张表交替的部分（交集）

隐式内连接

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

select COUNT(*) FROM dept,emp WHERE emp.dept_id=dept.id;

显示内连接

SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 WHERE 条件

select COUNT(*) FROM dept join emp WHERE emp.dept_id=dept.id;

外连接

左外连接

查询表1（左表）的所有数据，包含表1和表2交集部分的数据

SELECT 字段列表 FROM 表1 LEFT JOIN 表2 WHERE 条件

select COUNT(*) FROM dept left join emp ON emp.dept_id=dept.id;

右外连接

查询表2（右表）的所有数据，包含表1和表2交集部分的数据

SELECT 字段列表 FROM 表1 RIGHT JOIN 表2 WHERE 条件

select COUNT(*) FROM dept right join emp ON emp.dept_id=dept.id;

自连接

自连接可以使用内连接也可以使用外连接

注意：自连接必须起别名

SELECT 字段列表 FROM 表1 JOIN 表1 WHERE 条件

#查询员工对应的主管
SELECT e1.ename,e2.ename FROM emp as e1 JOIN emp as e2 WHERE e2.id = e1.manager_id;

#查询员工对应的主管，没有主管也查询出来（通过外连接）
SELECT e1.ename,e2.ename FROM emp as e1 LEFT JOIN emp as e2 ON e2.id = e1.manager_id;

表结构：

联合查询（union,union all）

对于union查询，就是把多错查询的结果合并起来，形成一个新的查询结果集。

注意：两表结构一致即同一个表

SELECT 字段列表 FROM 表1 ......

union [all]

SELECT 字段列表 FROM 表1 ......;

#将薪资低于10000，和年龄大于50岁的员工全部查出来
SELECT * FROM emp where salary < 10000
union all
SELECT * FROM emp where age > 50;
#去重，删去all
SELECT * FROM emp where salary < 10000
union 
SELECT * FROM emp where age > 50;

子查询

SELECT 字段列表 FROM 表1 where column1 = (SELECT column FROM 表2);

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

根据子查询结果不同，分为：

• 标量子查询（子查询结果为单个值）
• 列子查询（子查询结果为一列）
• 行子查询（子查询结果为一行）
• 表子查询（查询结果为多行多列）

标量子查询

定义：子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式。

常用操作符：=、<>、>、>=、<、<=

#查询“销售部”的所有员工信息
select * 
FROM emp 
WHERE 
dept_id = (select id FROM dept WHERE dname = '销售部');

#查询在“刘备”入职之后的员工信息
SELECT * 
FROM	emp
WHERE
joindate > (SELECT joindate FROM emp WHERE ename = '刘备');

列子查询

定义：子查询返回结果是一列（可以是多行）

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

#查询“销售部”和“财务部”的所有员工信息
select * 
FROM emp 
WHERE 
dept_id in (select id FROM dept WHERE dname = '销售部' or dname = '财务部');


#查询比“学工部”所有人，工资都高的员工信息
#方法一：
select * FROM emp WHERE
salary >(
SELECT salary FROM emp WHERE 
dept_id = (select id FROM dept WHERE dname = '学工部') ORDER BY salary DESC LIMIT 1);
#方法二：
select * FROM emp WHERE 
salary > all(SELECT salary FROM emp WHERE dept_id = (select id FROM dept WHERE dname = '学工部'))


#查询比“学工部”任意一人工资都高的员工信息,any也可以换成some
select * FROM emp WHERE 
salary > any(SELECT salary FROM emp WHERE dept_id = (select id FROM dept WHERE dname = '学工部'))

行子查询

定义：子查询返回结果是一行（可以是多列）

常用操作符：=、<>、>、>=、<、<=

#查询与“猪八戒”的薪资及直属领导相同的员工信息
SELECT * FROM emp WHERE
(salary,manager_id) = (select salary,manager_id FROM emp WHERE ename='猪八戒');

表子查询

定义：子查询返回的结果是多行多列

常用操作符：IN

#查询与“猪八戒”或“小白龙”的薪资及直属领导相同的员工信息
SELECT * FROM emp WHERE
(salary,manager_id) in (select salary,manager_id FROM emp WHERE ename='猪八戒' or ename='小白龙');

#查询入职日期是“2006-01-01”之后的员工信息，以及部门信息
SELECT s1.ename,dept.dname FROM
(SELECT * FROM emp WHERE joindate > "2006-01-01") s1
LEFT JOIN dept 
ON
s1.dept_id = dept.id