MySQL数据库（三）：分组查询、连接查询、子查询、union联合查询

0、查询sql综述：基本包含内容

​#inner可以省略
#inner join连接和sql92等值连接效果一样，都是查询多表交集
#on后面的连接条件可以是=，也可表示两个表中的字段范围
/*
	select 查询列表
	from 表1
	【连接类型】
	join 表2
	on 连接条件
	where 筛选条件1 and 筛选条件2
	group by 分组
	having 分组后的筛选条件
	order by 排序
	limit 【起始索引，】size;
*/
#注意：起始索引从0开始

一、分组查询：group by

语法规则：必须要有聚合函数

select 聚合函数,列（要求出现在group by的后面）
FROM 表  where 条件 
group by 分组列
having 查询条件
order by 排序字段

#查询每个部命的平均工资
SELECT department_id 部门id,AVG(salary) 平均工资 FROM employees
GROUP BY department_id;

又如：查询vend_id（供应商）下的产品数量：

WITH ROLLUP，在group by分组+聚合计算时，使用该关键字，汇总所有分组项的值；

二、连接查询

2.1 等值连接

这种写法是将两个或多个表的相等字段写在where后面作为条件，多条件同样以and连接；

#查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM departments d,locations l
WHERE d.location_id = l.location_id
AND city LIKE '_o%';

2.2 内连接：inner

示例：inner join和上面的等值连接的效果是一样的；

SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;

2.2.1 自连接

自连接：是内链接inner join中特殊的一种，关联的表是一张表

#查询员工表中，员工名称及对应的管理这名称
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.manager_id = m.employee_id;

2.3 外连接

/*外连接
用于查询一个表中有，另一个表中没有的记录
特点：分主从表，以主表为依据，
*/

SELECT d.department_name,e.*
FROM departments d
LEFT JOIN employees e
ON e.department_id=d.department_id
WHERE d.department_name IN ("SAL","IT");

三、子查询

分类：
按子查询出现的位置：
				select后面：
							仅仅支持：结果集只有一行一列
				from后面
							支持：结果集一般为多行多列
				where或having后面
							支持：结果集为一行一列、一列多行、一行多列
				exists后面（这个成为：相关子查询）
							支持：结果集一般为多行多列
按结果集的行列数不同：
				标量子查询（结果集只有一行一列）
				列子查询（结果集有一列多行）
				行子查询（结果集有一行多列）
				表子查询（结果集一般为多行多列）

3.1 子查询出现在where或者having后面

1.标量子查询（单行子查询）
2.列子查询（多列子查询）
3.行子查询（多行）（较少）
特点：
1.子查询放在小括号内
2.子查询一般放在条件的右侧
3.
标量子查询，一般搭配着 单行操作符使用：>,<,>=,<=,<>
列子查询，一般搭配着 多行操作符使用：IN/NOT IN、ANY/SOME、ALL

#一列一行
#谁的工资比Abel高？
SELECT last_name,salary FROM employees
WHERE salary>(
SELECT salary FROM employees WHERE last_name="Abel"
);
#2.列子查询（一列多行）

#返回location_id是1400或1700的部门中的所有员工的姓名

SELECT e.last_name,department_id
FROM employees e
WHERE e.department_id IN (
	SELECT department_id FROM departments WHERE location_id IN (1400,1700)
);

#行子查询
#查询员工编号最小并且工资最高的员工信息
SELECT * FROM employees
WHERE (employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary)
FROM employees
)

3.2 放在select后面的子查询:相关子查询（correlated subquery） 涉及外部查询的子查询

注意：下面的示例中，子查询是使用了主查询的表的别名的，是可以这么用的

#查询每个部门的员工个数
SELECT d.*,(
	SELECT count(*)
	FROM employees e
	WHERE e.department_id = d.department_id
	) 个数
FROM departments d;

#子查询作为计算字段:相关子查询（correlated subquery） 涉及外部查询的子查询
SELECT cust_name,cust_state,(SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;

3.3 放在from后面的子查询

将子查询结果作为主查询的数据来源

#查询每个部门的平均工资的工资等级
SELECT ag_dep.*,g.grade_level
FROM
	(SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id) ag_dep
INNER JOIN
	job_grades g
ON ag_dep.ag BETWEEN g.lowest_sal AND g.highest_sal;

3.4 exist里面的子查询

#EXISTS后面：-----------------------------
/*
EXISTS(完整的select语句)：判断子查询有没有值；
结果：1或0
*/
SELECT EXISTS(SELECT employee_id FROM employees);

四、union联合查询

应用场景：要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时；

特点：

1.要求多条查询语句的查询列数是一致的；

2.要求多条查询语句的查询的每一列的类型和顺序最好一致

格式：

查询语句1
union
查询语句2
union
……