多表查询分类
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;