关于mysql:mysql多表查询

多表查问

筹备数据

-- 创立部门表
create table dept(
 id int primary key auto_increment,
 name varchar(20)
);
-- 向部门表增加数据
insert into dept (name) values ('开发部'), ('市场部'), ('财务部');

-- 创立员工表
create table emp (
    id int primary key auto_increment,
    name varchar(10),
    gender char(2),
    salary double,
    join_date date,
    dept_id int,
    foreign key (dept_id) references dept(id) -- 外键，关联部门表主键
);
-- 向员工表中增加数据
insert into emp (name, gender, salary, join_date, dept_id) values
('孙悟空','男',7200,'2013-02-24',1),
('猪八戒','男',3600,'2010-12-02',2),
('唐僧','男',9000,'2008-08-08',2),
('白骨精','女',5000,'2015-10-07',3),
('蜘蛛精','女',4500,'2011-03-14',1);

分类

内连贯

用右边表的记录去匹配左边表的记录，如果符合条件的则显示。如：从表. 外键 = 主表. 主键。

内连贯也分为两个：隐式内连贯 and 显示内连贯。

隐式内连贯

看不到关键字 JOIN，条件应用WHERE 指定

SELECT 字段名 FROM 左表, 右表 WHERE 条件;

代码演示

select * from emp, dept where emp.`dept_id` = dept.`id`;

显示内连贯

应用 INNER JOIN ... ON 关键字的语句（INNER能够省略）

SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件;

内连贯查问步骤

1. 确定查问哪些表
2. 确定表连贯的条件
3. 确定查问的条件
4. 确定查问的字段

代码演示

-- 查问唐僧的信息，显示 id，姓名，性别，工资和所在的部门名称
select 
    e.id id, e.name name, e.gender gender, e.salary salary, d.name deptName
from
    emp e -- 给左表起别名
inner join
    dept d -- 给右表起别名
on
    e.`dept_id` = d.`id`
where
    e.`name` = '唐僧';

外连贯

外连贯也是分为两类：左外连贯 and 右外连贯。

左外连贯

应用 LEFT OUTER JOIN ... ON 关键字的语句（OUTER能够省略）

用右边表的记录去匹配左边表的记录，如果符合条件的则显示. 否则，显示 NULL.

能够了解为：在内连贯的根底上保障左表的数据全副显示

SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件;

代码演示

-- 在部门表中减少一个销售部
insert into dept (name) values ('销售部');

-- 应用内连贯查问(只显示符合条件的数据)
select * from dept d inner join emp e on d.`id` = e.`dept_id`;

-- 应用左外连贯查问(保障左表 dept 数据的全副显示, 再对右表的数据再进条件过滤)
select * from dept d LEFT JOIN emp e on d.`id` = e.`dept_id`;

右外连贯

应用 RIGHT OUTER，OUTER 能够省略

用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示NULL

能够了解为：在内连贯的根底上保障右表的数据全副显示

代码演示

-- 在员工表中减少一个 dept_id 为 null 的员工
insert into emp values (null, 'Jason', '男', 6543.40, '1999-07-12', null);

-- 应用内连贯查问(只显示符合条件的数据)
select * from dept d inner join emp e on d.`id` = e.`dept_id`;

-- 应用右外连贯查问(保障右表 emp 数据的全副显示, 再对左表的数据再进条件过滤)
select * from dept d right join emp e on d.`id` = e.`dept_id`;

子查问

概念

一个查问的后果做为另一个查问的条件

分类

1. 子查问的后果是单行单列

2. 子查问的后果是多行单列

3. 子查问的后果是多行多列

单行单列

子查问后果只有是单行单列，必定在 WHERE 前面作为条件

SELECT 查问字段 FROM 表 WHERE 字段 = (子查问);

代码演示

-- 查问工资最高的员工是谁？select * from emp where salary = (select max(salary) from emp);

-- 查问工资小于平均工资的员工有哪些？select * from emp where salary < (select avg(salary) from emp);

多行单列

子查问后果是多行单列，后果集相似于一个数组，父查问应用 IN 运算符

SELECT 查问字段 FROM 表 WHERE 字段 IN (子查问);

代码演示

-- 查问工资大于 5000 的员工，来自于哪些部门的名字
select name from dept where id in (select dept_id from emp where salary > 5000);

-- 查问开发部与财务部所有的员工信息
select * from emp where dept_id in (select id from dept where name in ('开发部','财务部'));

多行多列

子查问后果只有是多列，必定在 FROM 前面作为表

SELECT 查问字段 FROM (子查问) 表别名 WHERE 条件;

代码演示

-- 查问出 2011 年当前入职的员工信息，包含部门名称

-- 办法一：应用隐式内连贯
select 
    d.name deptName, e.* 
from 
    dept d, (select * from emp where join_date >= '2011-1-1') e 
where 
    d.`id` = e.`dept_id`;
    
-- 办法二：应用显示内连贯 1
select 
    d.name deptName, e.*
from
    dept d
inner join
    (select * from emp where join_date >= '2011-1-1') e
on
    d.`id` = e.`dept_id`;

-- 办法三：应用显示内连贯 2
select 
    d.name deptName, e.* 
from 
    dept d 
inner join 
    emp e
on
    d.`id` = e.`dept_id`
where
    e.`join_date` >= '2011-1-1';
    
-- 办法四：应用显示内连贯 3
select
    d.name deptName, e.*
from
    dept d
inner join
    emp e
on
    d.`id` = e.`dept_id`
and
    e.`join_date` >= '2011-1-1';