mysql多表查询

多表查询分类
     1.内连接
     2.外连接
     3.子查询
笛卡尔积
     有两个集合A、B，取这两个集合的所有组成情况
     要完成多表查询，需要消除无用的笛卡尔积数据

内连接

     1.隐式内连接：
     2.显示内连接

隐式内连接

使用where条件消除无用数据

-- 查询所有员工信息和对应的部门信息
SELECT * FROM dept,emp WHERE emp.dept_id = dept.id;
-- 查询员工的名称，性别。以及所在部门
SELECT 
	emp.`name` 姓名,
	emp.gender 性别,
	dept.`name` 部门	
FROM 
	dept,emp 
WHERE 
	emp.dept_id=dept.id;

SELECT
	t1.`name`,
	t1.gender,
	t2.`name`
FROM
	emp t1, -- 起别名
	dept t2
WHERE
	t1.dept_id=t2.id;

显示内连接

-- 语法：select 字段列表 from 表名1 [inner] join 表名2 on 条件
SELECT
	t1.`name`,
	t1.gender,
	t2.`name`
FROM 
	emp t1
INNER/*INNER 可以省略*/ JOIN
	dept t2
ON
	t1.dept_id = t2.id;
	
-- 多对多 查询 所有有角色的用户（role：角色表 user:用户表 user_role：中间表）
SELECT 
	`user`.*,`role`.*
FROM 
	`user`
JOIN 
	user_role 
ON 
	`user`.`id`=user_role.`UID`
JOIN 
	`role`
ON 
	`role`.`ID`=user_role.`RID`

外连接

     1.左外连接
     2.右外连接

左外连接

-- 语法：select 字段列表 from 表名1 left [outer] join 表名2 on 条件
-- 左外连接查询的是左表所有数据以及其交集部分

-- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门则不显示部门名称
SELECT
	t1.*,
	t2.`name`
FROM
	emp t1  -- 左表
LEFT JOIN
	dept t2 -- 右表
ON
	t1.dept_id = t2.id; -- 交集部分
	
-- 多对多 查询 所有有角色的用户（role：角色表 user:用户表 user_role：中间表）
SELECT 
	`role`.*,`user`.* 
FROM 
	`role` 
LEFT OUTER JOIN 
	user_role 
ON 
	`role`.`ID`=user_role.`RID`
LEFT OUTER JOIN 
	`user`
ON 
	`user`.`id`=user_role.`UID`

右外连接

-- 语法：select 字段列表 from 表名1 right [outer] join 表名2 on 条件
-- 右外连接查询的是右表所有数据以及其交集部分
SELECT
	t1.*,
	t2.`name`
FROM
	emp t1  -- 左表
RIGHT JOIN
	dept t2 -- 右表
ON
	t1.dept_id = t2.id; -- 交集部分

子查询

查询中嵌套查询，被嵌套的查询为子查询
分类：
     1.子查询的结果是单行单列
        子查询可以作为条件，使用运算符去判断。
     2.子查询的结果是多行单列
     3.子查询的结果是多行多列
        子查询的结果是多行多列可以作为一场虚拟表进行查询

子查询的结果是单行单列

 查询工资最高的员工信息
SELECT * FROM emp WHERE emp.salary=(SELECT MAX(salary) FROM emp);

子查询的结果是多行单列

-- 查询市场部和财务部的所有员工信息
SELECT 
	* 
FROM 
	emp 
WHERE 
	emp.dept_id 
IN (
	SELECT 
		dept.id 
	FROM 
		dept 
	WHERE 
		dept.`name` 
		IN('市场部','财务部')
	);

子查询的结果是多行多列

-- 查询员工入职日期是2011-11-11日之后的员工信息
SELECT * FROM emp WHERE emp.join_date > '2011-11-11'; 
-- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
SELECT 
	* 
FROM 
	(SELECT * FROM emp WHERE emp.join_date > '2011-11-11') t1
LEFT JOIN
	dept
ON
	t1.dept_id = dept.id;