多表查询

知识回顾

1、逻辑运算：当判断需要使用or进行判断时，可以使用in(a,b) in(a,b)数据满足a or b

所使用的两张表

CREATE TABLE department(
id INT PRIMARY KEY AUTO_INCREMENT ,
dep_name VARCHAR(20),
POSITION VARCHAR(20)
);
CREATE TABLE employee(
id INT PRIMARY KEY auto_increment,
NAME VARCHAR(20),
dep_id INT
);

多表查询

1、分类

多表查询时表的数据以笛卡尔积的形式组合，会出现数据错误
1、内连接
2、外连接
3、子查询
这三种方法消除错误

2、内连接查询：

查询什么表，
查询什么字段，
查询的条件

1、隐式内连接
通过where 消除无用数据，还会用到给表起别名的操作
（1）查询employee和department中所有的正确数据
SELECT 
		*
FROM 
		employee,department
WHERE 
		department.`id` = employee.`dep_id`;
（2）查询employee中的id,name,dep_id,department表中的id,dep_name
SELECT
	e.`id`,
	e.`name`,
	e.`dep_id`,
	d.`id`,
	d.`dep_name`
FROM
	employee e,  #别名
	department d #别名
WHERE
	d.`id`=e.`dep_id`;

2、显式内连接
select 查询字段 from 表名1 [inner] join 表名2 on 条件
SELECT 
	employee.`id`,
	employee.`name`,
	employee.`dep_id`,
	department.`id`,
	department.`dep_name`
FROM 
	employee
INNER JOIN
	department
ON
	employee.`dep_id`=department.`id`;

3、外连接查询

1、为什么使用外连接查询？
内连接查询只能查询到符合条件的数据，但是有的时候查询的数据是查询前一个表的全部数据，只查询后一个表的满足条件的数据，则使用外连接
2、分类：
左外连接
右有连接

左外连接

语法：
select 查询字段 from 左表 left [outer] join 右表 on 条件;#查询左表全部数据和右表中满足条件的数据。
例如：
SELECT 	
	employee.*, #查询字段，代表表格中展示的字段
	department.`dep_name`
FROM 
	employee#左表
LEFT JOIN
	department#右表
ON
	employee.`dep_id`=department.`id`;
/*
表格中展示的内容以左表employee中的内容为准，对于查询字段中的内容，坐标中有的内容全部展示，右表只展示满足条件的内容
*/

效果如图

右外连接

语法：
select 查询字段 from 左表 right [outer] join 右表 on 条件;#查询右表全部数据和交集数据
例如：
SELECT 	
	employee.*, #查询字段，代表表格中展示的字段
	department.`dep_name`
FROM 
	employee#左表
RIGHT JOIN
	department#右表
ON
	employee.`dep_id`=department.`id`;
/*
表格中展示的内容以右表department的内容为准，对于查询字段中的内容，右表中有的内容全部展示，左表只展示满足条件的内容
*/

效果如图

4、子查询
概念：查询中嵌套查询

子查询结果分类：
单行单列
多行单列
多行多列

单行单列，可以使用条件运算符进行判断> < >= <=

/*查询表中在调研部工作的职员的信息*/
SELECT id FROM department WHERE dep_name='调研部';#查询调研部的id
SELECT *FROM employee WHERE dep_id=1;#查询此id的信息
上面两条语句等价于：SELECT *FROM employee WHERE dep_id=(SELECT id FROM department WHERE dep_name='调研部');

多行单列,可以使用运算符in进行判断

/*查询在调研部和行政部工作的人员信息*/
SELECT id FROM department WHERE dep_name='调研部' OR dep_name='行政部';
SELECT * FROM employee WHERE dep_id=1 OR dep_id=2;
上面两条语句等价于：SELECT *FROM employee WHERE dep_id IN (1,2);
等价于：SELECT *FROM employee WHERE dep_id IN (SELECT id FROM department WHERE dep_name='调研部' OR dep_name='行政部'
);

多行多列，可以将查询结果作为一张表进行查询

/*查询调研部和人事部的位置以及在这两个部门工作的人的id,name*/
SELECT *FROM department WHERE dep_name='调研部' OR dep_name='行政部';
SELECT t1.`id`,t1.`name`,t2.dep_name,t2.position  FROM employee t1, (SELECT *FROM department WHERE dep_name='调研部' OR dep_name='行政部'
) t2 WHERE t1.dep_id=t2.`id`;