多表查询

表连接查询

• 什么是多表查询:
  涉及在多张表里寻找数据的的查找，使用多表联合查询。
• 多表查询的作用：
  比如：我们想查询孙悟空的名字和他所在的部门的名字，则需要使用多表查询。
  如果一条SQL语句查询多张表，因为查询结果在多张不同的表中。每张表取1列或多列

多表查询的分类：

笛卡尔积：

• 有两个集合A,B .取这两个集合的所有组成情况。
• 要完成多表查询，需要消除无用的数据

内连接查询

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

# 创建部门表
	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);

隐式内连接

隐式内连接：看不到JOIN关键字，条件使用WHERE指定

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

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

显式内连接

显示内连接：使用INNER JOIN … ON语句, 可以省略INNER

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

-- 查询唐僧的信息，显示员工id，姓名，性别，工资和所在的部门名称，我们发现需要联合2张表同时才能查询出需要的数据，使用内连接
确定查询哪些表
select * from emp inner join dept;
确定表连接条件，员工表.dept_id = 部门表.id 的数据才是有效的
select * from emp e inner join dept d on e.`dept_id` = d.`id`;
确定查询条件，我们查询的是唐僧的信息，员工表.name='唐僧'
select * from emp e inner join dept d on e.`dept_id` = d.`id` where e.`name`='唐僧';

总结内连接查询步骤：

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

外链接查询：

左外连接

左外连接：使用LEFT OUTER JOIN … ON，OUTER可以省略

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

用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示NULL
可以理解为：在内连接的基础上保证左表的数据全部显示(左表是部门，右表员工)

-- 在部门表中增加一个销售部
insert into dept (name) values ('销售部');
select * from dept;
-- 使用内连接查询
select * from dept d inner join emp e on d.`id` = e.`dept_id`;
-- 使用左外连接查询
select * from dept d left join emp e on d.`id` = e.`dept_id`;

右外连接

右外连接：使用RIGHT OUTER JOIN … ON，OUTER可以省略

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

用右边表的记录去匹配左边表的记录，如果符合条件的则显示；否则，显示NULL
可以理解为：在内连接的基础上保证右表的数据全部显示

-- 在员工表中增加一个员工
insert into emp values (null, '沙僧','男',6666,'2013-12-05',null);
select * from emp;
-- 使用内连接查询
select * from dept inner join emp on dept.`id` = emp.`dept_id`;
-- 使用右外连接查询
select * from dept right join emp on dept.`id` = emp.`dept_id`;

子查询

• 什么是子查询：

  • 一个查询的结果做为另一个查询的条件
  • 有查询的嵌套，内部的查询称为子查询
  • 子查询要使用括号

• 子查询结果的三种情况：

  • 子查询的结果是单行单列
    
  • 子查询的结果是多行单列
    
  • 子查询的结果是多行多列
    

• 子查询的结果是一个值的时候
  子查询结果只要是单行单列，肯定在WHERE后面作为条件，父查询使用：比较运算符，如：> 、<、<>、= 等

SELECT 查询字段 FROM 表 WHERE 字段=（子查询）;

查询工资最高的员工是谁？
-- 1) 查询最高工资是多少
select max(salary) from emp;
-- 2) 根据最高工资到员工表查询到对应的员工信息
select * from emp where salary = (select max(salary) from emp);

查询工资小于平均工资的员工有哪些？
-- 1) 查询平均工资是多少
select avg(salary) from emp;
-- 2) 到员工表查询小于平均的员工信息
select * from emp where salary < (select avg(salary) from emp);

• 子查询结果是多行单列的时候
  子查询结果是单例多行，结果集类似于一个数组，父查询使用IN运算符

SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;

查询工资大于5000的员工，来自于哪些部门的名字
-- 先查询大于5000的员工所在的部门id
select dept_id from emp where salary > 5000;
-- 再查询在这些部门id中部门的名字 Subquery returns more than 1 row
select name from dept where id = (select dept_id from emp where salary > 5000);
select name from dept where id in (select dept_id from emp where salary > 5000);

• 子查询的结果是多行多列
  子查询结果只要是多列，肯定在FROM后面作为表
  子查询作为表需要取别名，否则这张表没有名称则无法访问表中的字段

SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件;

查询出2011年以后入职的员工信息，包括部门名称
-- 查询出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 ;

-- 也可以使用表连接：
select * from emp inner join dept on emp.`dept_id` = dept.`id` where join_date >='2011-1-1';
select * from emp inner join dept on emp.`dept_id` = dept.`id` and join_date >='2011-1-1';