---

前言

上次说到了MySQL的查询进阶三和四，这次继续讨论查询的相关的知识 链接: https://pan.baidu.com/s/1a_wstxPVhxeliZkt6-BTiw 提取码: gaj8

进阶五：分组查询

语法

select 分组函数，列(要求出现在group by的后面) from 表 【where 筛选条件】 group by 分组的列表 【oder by 子句】 注意：查询列表必须特殊，要求是分组函数和group by后出现的字段

特点

1.分组查询中的筛选分为两类
分组前筛选   位置：group by 子句的前面  关键字：where
分组后筛选   位置：group by 子句的后面  关键字：having
能用分组前筛选的优先考虑分组前筛选
group by 既支持多个字段分组也支持单个字段的分组（多个字段的分组用逗号隔开且没有顺序的要求）
group by 也支持函数和表达式（用的相对上面的比较的少）
group by 也可以添加排序（放在整个的分组查询的最后的）

1.简单的分组查询

#案例1、查询每个工种的最高工资 
SELECT MAX(salary),job_id
FROM `employees`
GROUP BY job_id;
#案例2、查询每个位置的部门个数
SELECT COUNT(1),`location_id`
FROM `departments`
GROUP BY `location_id`;
#案例3、查询邮箱中包含a字符的，每个部门的平均工资
SELECT AVG(salary),`department_id`
FROM `employees`
WHERE `email` LIKE '%a%'
GROUP BY `department_id`;
#案例4、查询有奖金的每个领导手下的最高工资
SELECT MAX(salary),`manager_id`
FROM `employees`
WHERE `commission_pct` IS NOT NULL
GROUP BY `manager_id`;

2.添加分组后的筛选

#案例5、查询哪个部门的员工个数大于2
SELECT COUNT(*),`department_id`
FROM `employees`
GROUP BY `department_id`
HAVING COUNT(*)>2;
#案例6、查询每个工种有奖金的的员工的最高工资大于12000的工种编号和最高工资
SELECT MAX(salary),job_id
FROM `employees`
WHERE `commission_pct` IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;
#案例7、查询领导编号大于102的每个领导手下的最低工资大于5000的领导编号是哪个，以及其最低工资
SELECT MIN(salary),`manager_id`
FROM `employees`
WHERE `manager_id` > 102
GROUP BY `manager_id`
HAVING MIN(salary)>5000;

1. 按表达式或函数分组

#案例8、按姓名的长度分组，查询每一组的员工个数，筛选员工个数大于5的有那些
SELECT COUNT(*),LENGTH(last_name)
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>5;

2. 按多个字段分组

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

进阶六 连接查询

概念

又称多表查询，当查询的字段来自于多个表时，就会用到多表查询 笛卡尔乘积现象：表一有m行，表二有n行，结果就是：m*n行 发生的原因：没有有效的连接条件 如何避免：添加有限的连接条件

分类

按年代分类：
sql92标准：仅仅支持内连接
sq199标准：【推荐】支持内连接+外连接（左外和右外）+交叉连接
按功能分类：
	内连接：
		等值连接
		非等值连接
		自连接
	外连接：
		左外连接
		右外连接
		全连接
	交叉连接

SELECT * FROM `beauty`;

SELECT * FROM `boys`;

SELECT `name`,`boyName` FROM `beauty`,`boys`
WHERE `beauty`.`boyfriend_id`=`boys`.`id`;

一、sq192标准

1.等值连接

1.多表等值连接的结果为多表的交集部分 2.n表连接至少需要n-1个连接条件 3.多表的顺序没有要求 4.一般需要为表起别名 5.可以搭配前面的所有子句进行使用，；例如分组，排序，筛选等等

#案例、查询对应的员工名和部门名
SELECT `last_name`,`department_name`
FROM `departments`,`employees`
WHERE `employees`.`department_id`=`departments`.`department_id`;

1.为表起别名

/*
好处：
	①提高语句的简洁度
	②区分多个重名的字段
注意：如果取了表名的别名就不能用原来的表名去限定了，只能用别名了
*/
#案例、查询员工名，工种号，工种名
SELECT e.`last_name`,e.`job_id`,j.`job_title`
FROM `employees` e,`jobs` j
WHERE e.`job_id`=j.`job_id`;

2.表的顺序是否可以调换（可以调换）

#案例、查询员工名，工种号，工种名
SELECT e.`last_name`,e.`job_id`,j.`job_title`
FROM `jobs` j,`employees` e
WHERE e.`job_id`=j.`job_id`;

3.可以加筛选

#案例、查询有奖金的员工名和部门名
SELECT e.`last_name`,d.`department_name`,e.`commission_pct`
FROM `employees` e,`departments` d
WHERE e.`department_id`=d.`department_id`
AND e.`commission_pct` IS NOT NULL;
#查询城市中第二个字符为o的城市名和部门名
SELECT l.`city`,d.`department_name`
FROM `departments` d,`locations` l
WHERE d.`location_id`=l.`location_id`
AND l.`city` LIKE '_o%';

4.可以加分组

#查询每个城市的部门个数
SELECT COUNT(*) 个数,l.`city`
FROM `departments` d,`locations` l
WHERE d.`location_id`=l.`location_id`
GROUP BY l.city
#案例、查询有奖金的每个部门的部门名和部门领导的编号和该部门的最低工资
SELECT `department_name`,d.`manager_id`,MIN(`salary`)
FROM `departments` AS d,`employees` e
WHERE d.`department_id`=e.`department_id`
AND e.`commission_pct` IS NOT NULL
GROUP BY d.`department_name`,d.`manager_id`;

5.可以加排序

#案例、查询每个工种的工种名和员工的个数，并且按员工个数降序  
SELECT `job_title`,COUNT(*)
FROM `employees` e,`jobs` j
WHERE e.`job_id`=j.`job_id`
GROUP BY j.`job_id`
ORDER BY COUNT(*) DESC;

6.可以实现三表连接

#查询员工名，部门名和所在的城市
SELECT `last_name`,`department_name`,`city`
FROM `employees` e,`departments` d,`locations` l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`;

2.非等值连接

SELECT * FROM `job_grades`;
#查询员工的工资和工资级别
SELECT salary,grade_level
FROM `employees` e,`job_grades` j
WHERE salary BETWEEN j.`lowest_sal` AND j.`highest_sal`;

3.自连接

#查询员工的名和上级的名称 SELECT e.employee_id,e.last_name,m.employee_id,m.last_name FROM employees AS e,employees AS m WHERE e.manager_id=m.employee_id;

二、sq199标准

语法：

select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【...】

分类：

内连接（⭐）：inner
外连接：
	左外（*）：left 【outer】
	右外（*）：right【outer】
	全外：full【outer】
交叉连接：cross

相关概念

#内连接 /* 语法： select 查询列表 from 表1 别名 【连接类型】 join 表2 别名 on 连接条件 【where 筛选条件】 【…】 分类：

内连接：
		等值连接
		非等值连接
		自连接

特点：

1.可以添加分组，排序，分组等
2.inner可以省略
3.筛选条件放在where的后面，连接条件放在on的后面，提高分离性，便于阅读
4.inner join连接和sql92的等值连接的效果是一样的，都是查询多表的交集

*/

1.等值连接

#查询员工名，部门名
SELECT `last_name`,`department_name`
FROM `employees` e
INNER JOIN `departments` d
ON e.`department_id`=d.`department_id`;
#查询名字中包含e的员工名和工种名
SELECT `last_name`,`job_title`
FROM `employees` e
INNER JOIN `jobs` j
ON e.`job_id`=j.`job_id`
WHERE `last_name` LIKE '%e%';
#查询部门个数大于3的城市名和部门个数
SELECT `city`,COUNT(*) 部门个数
FROM `departments` d
INNER JOIN `locations` l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING 部门个数>3;
#查询哪个部门的员工的个数>3的部门名和员工个数并按个数降序
SELECT `department_name`,COUNT(*)
FROM `departments` d
INNER JOIN `employees` e
ON d.`department_id`=e.`department_id`
GROUP BY `department_name`
HAVING COUNT(*)>3
ORDER BY  COUNT(*) DESC;
#查询员工名，部门名，工种名，并按部门名降序
SELECT `last_name`,`department_name`,`job_title`
FROM `employees` e
INNER JOIN `departments` d
ON e.`department_id`=d.`department_id`
INNER JOIN `jobs` j
ON e.`job_id`=j.`job_id`
ORDER BY `department_name` DESC;

2、非等值连接

#查询员工的工资等级
SELECT `salary`,`grade_level`
FROM `employees` e
INNER JOIN `job_grades` j
ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;

3、自连接

#查询员工的名字。上级的名字
SELECT n.`last_name`,m.`last_name`
FROM `employees` n
INNER JOIN `employees` m
ON n.`manager_id`=m.`employee_id`;
#查询员工的名字（含k的）。上级的名字
SELECT n.`last_name`,m.`last_name`
FROM `employees` n
INNER JOIN `employees` m
ON n.`manager_id`=m.`employee_id`
WHERE n.`last_name` LIKE '%k%';

4.外连接

/* 应用场景：用于查询一个表中有，一个表中没有的记录 特点： 1.外连接的查询结果为主表中的所有记录（意思就是想要查询的哪个表就是主表） 如果从表中有和他匹配的则显示和他匹配的值 如果从表中没有和他匹配的则显示的是null 外连接查询的结果=内连接结果+主表中有而从表中没有的记录 2.左外连接：left join 左边是主表 右外连接：right join 右边的是主表 3.左外和右外交换两个表的顺序可以实现同样的效果 4.全外连接=内连接的结果+表1中有但表2中没有的+表2中有但表1中没有的 */

1.左外

#查询男朋友不在男神表的女神名(左外连接)
SELECT b.`name`
FROM `beauty` b
LEFT JOIN `boys` bo
ON b.`boyfriend_id`=bo.`id`
WHERE bo.id IS NULL;

2.右外

#查询男朋友不在男神表的女神名(右外连接)
SELECT b.`name`
FROM `boys` bo
RIGHT JOIN `beauty` b
ON b.`boyfriend_id`=bo.`id`
WHERE bo.id IS NULL;
#案例、查询哪个部门没有员工
SELECT d.*
FROM `departments` d
LEFT OUTER JOIN `employees` e
ON d.`department_id`=e.`department_id`
WHERE e.`employee_id` IS NULL;

5.全外连接

SELECT bo.*,b.*
FROM `beauty` b
FULL OUTER JOIN `boys` bo
ON b.`boyfriend_id`=bo.id;
交叉连接（就是相当于是笛卡尔乘积）
SELECT b.*,bo.*
FROM `beauty` b
CROSS JOIN `boys` bo;