MySQL多表查询

多表查询

1.什么是多表查询呢?

就是从多个表中查询数据.
例如 : slect * from A, B;
这时查询出来的结果集会产生一个笛卡尔积.
什么是笛卡尔积呢? 意思是A和B中的所有组合情况.
但是这并不是我们想要的结果,所以需要我们筛选出有用的数据.

多表查询的分类

1. 内连接查询

在查询前我们应该考虑到以下几点:

1.查询的表有哪些 ?
2.连接表的条件是什么?
3.查询的条件是什么,也是就查询结果应该满足什么条件?
4.确定查询字段,也就是需要显示出哪些信息.

隐式内连接查询  
语法: select 字段名 from 左表, 右表 where 条件
满足where条件的数据会被查询出来.

显示内连接查询
语法: select 字段名 from 左表 [inner] join 右表 on 条件;
满足on的条件的数据会被查询出来.

举例说明—内连接查询

# 创建部门表
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(1), -- 性别
	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);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);

首先我们查询一下两个表的所有数据 
SELECT * FROM emp,dept;

我们发现不是所有的数据组合都是有用的,只有员工表.dept_id = 部门表.id 的数据才是有用的。所以需要通过条件过滤掉没用的数据。

-- 查询所有员工姓名以及对应的部门名
select emp.`name`, dept.`name` from emp,dept where emp.`dept_id` = dept.`id`;

查询唐僧的信息， 显示员工 id， 姓名， 性别， 工资和所在的部门名称， 我们发现需要联合 2 张表同时才能查询出需要的数据

1.首先确定查询的表,部门表,员工信息表.
2.表之间的连接条件是 员工表.dept_id = 部门表.id.
3.筛选的条件是 员工姓名为唐僧的.
4.查询的字段是 员工 id， 姓名， 性别， 工资和所在的部门名称.

SELECT 
	a.`id`,		-- 员工id	
	a.`name`,	-- 员工名
	a.`gender`,	-- 员工性别
	a.`salary`,	-- 员工工资
	b.`name`	-- 部门名
FROM
	emp a
INNER JOIN
	dept b
ON
	a.`dept_id` = b.`id`
WHERE   
	a.`name` = '唐僧';

2.外连接查询

左外连接查询
语法:
SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件
用左表的记录去匹配右表的记录，如果符合条件的则显示；否则显示 NULL
可以理解为：在内连接的基础上保证左表的数据全部显示.

右外连接查询
语法:
SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件
用右表的记录去匹配左表的记录，如果符合条件的则显示；否则显示 NULL
可以理解为：在内连接的基础上保证右表的数据全部显示.

举例说明—外连接查询

在上边所建表中修改数据 操作如下:

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

-- 左外连接查询
SELECT * FROM dept d LEFT JOIN emp e ON d.`id` = e.`dept_id`;

-- 在员工表中增加一个员工
insert into emp values (null, '沙僧','男',6666,'2013-12-05',null);

-- 使用右外连接查询
SELECT * FROM dept d RIGHT JOIN emp e ON d.`id` = e.`dept_id`;	

3.子查询

子查询的概念
1.一个查询的结果用来做为另一个查询的条件.
2.有查询的嵌套,内部的查询就被称为子查询,并且用括号把子查询括起来.

子查询的结果分为三种:
1.单行单列
子查询结果只要是单行单列， 肯定在 WHERE 后面作为条件， 父查询使用： 比较运算符， 如： > 、 <、 <>、 = 等

语法: SELECT 查询字段 FROM 表 WHERE 字段=(子查询);

eg: 查询最高工资员工的信息
-- 查询最高工资是多少
select max(salary)from emp;
-- 根据最高工资到员工表查询到对应的员工信息
SELECT * FROM emp WHERE salary = (SELECT MAX(salary)FROM emp);

2.多行单列
子查询结果是多行单列，结果集类似于一个数组，父查询使用 IN 运算符

语法: SELECT 查询字段 FROM 表 WHERE 字段 IN (子查询);

eg: 查询开发部与财务部所有的员工信息
-- 先查询开发部与财务部的 id
SELECT dept.`id` FROM dept WHERE NAME IN('开发部','财务部');
-- 再查询这些部门id有哪些员工
SELECT * FROM emp WHERE emp.`dept_id` IN (SELECT dept.`id` FROM dept WHERE NAME IN('开发部','财务部'));

3.多行多列
子查询的结果是多行多列,结果可以当做虚拟表使用,

语法: SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件;
子查询作为表需要取别名，否则这张表没有名称则无法访问表中的字段.

eg: 查询出 2011 年以后入职的员工信息，包括部门名称
-- 在员工表中查询 2011-1-1 以后入职的员工
select * from emp where join_date >='2011-1-1';
-- 查询所有的部门信息，与上面的虚拟表中的信息组合，找出所有部门 id 等于的 dept_id
select * from dept d, (select * from emp where join_date >='2011-1-1') e where
d.`id`= e.dept_id ;

子查询的结果只要是单列，则在 WHERE 后面作为条件.
子查询的结果只要是多列，则在 FROM 后面作为表进行二次查询.