多表查问

筹备数据

-- 创立部门表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 deptNamefrom    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`;    -- 办法二：应用显示内连贯1select     d.name deptName, e.*from    dept dinner join    (select * from emp where join_date >= '2011-1-1') eon    d.`id` = e.`dept_id`;-- 办法三：应用显示内连贯2select     d.name deptName, e.* from     dept d inner join     emp eon    d.`id` = e.`dept_id`where    e.`join_date` >= '2011-1-1';    -- 办法四：应用显示内连贯3select    d.name deptName, e.*from    dept dinner join    emp eon    d.`id` = e.`dept_id`and    e.`join_date` >= '2011-1-1';