分组查询
语法:
select 分组函数,列(要求出现在group by的后面)
from 表名
【where 筛选条件】
group by 分组的列表
【order by 子句】
注意:
查询列表比较特殊，要求是分组函数和group by 后出现的字段
特点:
1、分组查询中的筛选条件分为两类（筛选的数据源不一样）
数据源 位置 关键字
分组前筛选 原始表 group by子句的前面 where
分组后筛选 分组后的结果集 group by子句的后面 having
①分组函数做条件肯定是放在having子句中
②能用分组前筛选的，就优先考虑分组前筛选
2、group by子句支持单个字段分组，也支持多个字段分组（多个字段之间用逗号隔开，没有顺序要求）
也支持表达式、函数。（使用较少）
3、也可以添加排序（排序放在整个分组查询的最后位置）
#引入: 查询每个部门的平均工资
SELECT AVG(`salary`) FROM `employees`;
#简单的分组查询
#案例1: 每个工种的最高工资
SELECT MAX(`salary`),`job_id` FROM `employees` GROUP BY `job_id`;
#案例2: 查询每个位置上的部门个数
SELECT COUNT(*),`location_id` FROM `departments` GROUP BY `location_id`;
#添加分组前筛选条件 where
#案例1: 查询邮箱中包含a字符的，每个部门的平均工资
SELECT AVG(`salary`),`department_id` FROM `employees` WHERE `email` LIKE "%a%" GROUP BY `department_id`;
#案例2: 查询有奖金的每个领导手下员工的最高工资
SELECT MAX(`salary`),`manager_id` FROM `employees` WHERE `commission_pct` IS NOT NULL GROUP BY `manager_id`;
#添加分组后的筛选条件 HAVING
#案例1: 查询哪个部门的员工个数大于2
#①查询每个部门的员工个数
#②根据①的结果进行筛选，查询哪个部门的员工个数大于2
SELECT COUNT(*),`department_id` FROM `employees` GROUP BY `department_id` HAVING COUNT(*)>2;
#案例2: 查询每个工种有奖金的员工的最高工资大于12000的工种编号和最高工资
#①查询每个工种有奖金的员工最高工资
#②根据①的结果进行筛选，最高工资大于12000
SELECT MAX(`salary`),`job_id` FROM `employees` WHERE `commission_pct` IS NOT NULL GROUP BY`job_id` HAVING MAX(`salary`)>12000;
#案例3: 查询领导编号大于102的每个领导手下的最低工资大于5000的领导编号是哪个，以及其最低工资
SELECT MIN(`salary`),`manager_id` FROM `employees` WHERE `manager_id` >102 GROUP BY `manager_id` HAVING MIN(`salary`) >5000;
#按表达式或函数分组
#案例: 按员工的姓名长度分组，查询每一组的员工个数，筛选员工个数大于5有哪些
SELECT COUNT(*),LENGTH(`last_name`) FROM `employees` GROUP BY LENGTH(`last_name`) HAVING COUNT(*)>5;
#按多个字段分组
#案例: 查询每个部门每个工种的员工的平均工资
SELECT AVG(`salary`),`department_id`,`job_id` FROM `employees` GROUP BY `department_id`,`job_id`;
#添加排序
#案例: 查询每个部门每个工种的员工的平均工资，并且按平均工资的高低显示出来
SELECT AVG(`salary`),`department_id`,`job_id` FROM `employees` WHERE `department_id` IS NOT NULL GROUP BY `department_id`,`job_id` ORDER BY AVG(`salary`) DESC;
含义: 又称多表查询，当查询的字段来自于多个表时，就用到连接查询
笛卡尔乘积现象: 表1 有m行
表2 有n行
结果 = m * n 行
发生原因: 没有有效的连接条件
如何避免: 添加有效的连接条件
分类:
按年代分类:
sql92标准: 仅仅支持内连接
sql99标准【推荐】: 支持内连接+外连接（左外、右外）+交叉链接
按功能分类:
内链接:
等值连接
非等值连接
自连接
外链接:
左外连接
右外连接
全外连接
交叉链接
#一、sql92标准
#1.等值连接
/*
①多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③多表的顺序没有要求
④一般需要为表 起别名
⑤可以搭配前面介绍的所有查询子句使用，比如排序、分组、筛选
*/
#案例1: 查询女生名和对应的男生名
SELECT `name`,`boyName` FROM `beauty`,`boys` WHERE `beauty`.`boyfriend_id`=boys.`id`;
#案例2: 查询员工名和对应的部门名
select `last_name`,`department_name` FROM `departments`,`employees` WHERE `employees`.`department_id`=`departments`.`department_id`
#2.为表起别名
/*
①提高语句的简洁度
②区分多个重名字段
注意: 如果为表起了别名，则查询的字段就不能使用原来的表名去限定
*/
#案例: 查询工种号、员工名、工种名
select `last_name`,e.`job_id`,`job_title` FROM `employees` as e,`jobs` as j WHERE j.`job_id`=e.`job_id`;
#3.两个表的顺序是可以调换的
#4.可以加筛选条件
#案例1: 查询有奖金的员工名、部门名
select `last_name`,`department_name` FROM `employees` e,`departments` d where e.`department_id`=d.`department_id` and e.`commission_pct` is not null;
#案例2: 查询城市名中第二个字符为o的部门名和城市名
select `department_name`,`city` from `departments` d,`locations` l where d.`location_id`=l.`location_id` and l.`city` like "_o%";
#5.可以加分组
#案例1: 查询每个城市的部门个数
select count(*),`city` FROM `locations` l,`departments` d where d.`location_id`=l.`location_id` group by `city`;
#案例2: 查询有奖金的每个部门的部门名和部门领导编号和该部门的最低工资
select `department_name`,d.`manager_id`,min(`salary`) from `employees` e,`departments` d where e.`manager_id`=d.`manager_id` and `commission_pct` is not null group by `department_name`;
#6.可以加排序
#案例: 查询每个工种的工种名和员工的个数，并且按员工个数降序
select `job_title`,count(*) from `jobs` j,`employees` e where e.`job_id`=j.`job_id` group by `job_title` order by COUNT(*) desc;
#7.可以实现三表连接
#案例: 查询员工名、部门名和所在的城市
select `last_name`,`department_name`,`city` from `locations` l,`employees` e,`departments` d where e.`department_id`=d.`department_id` and d.`location_id`=l.`location_id`;
#2.非等值连接
#案例1: 查询员工工资和工资级别
select `salary`,`grade_level` from `employees` e,`job_grades` j where `salary`between j.`lowest_sal` and j.`highest_sal`
#3.自连接
#案例: 查询员工名和上级的名称
select e.`last_name`,e.`employee_id`,m.`employee_id`,m.`last_name` from `employees` e,`employees` m
where e.`manager_id`=m.`employee_id`;
> 语法:
select 查询列表
from 表1 别名【连接类型】
join 表2 别名
on 连接条件
【where 分组前筛选条件】
【group by 分组】
【having 分组后筛选条件】
【order by 排序列表】
> 内连接（★）inner
外连接
左外（★） left【outer】
右外（★） right【outer】
全外 full【outer】
交叉连接 cross
#二、sql99标准
#一) 内连接
/*
语法:
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;
父类:
等值连接
非等值连接
自连接
特点:
①也可以添加排序、分组、筛选
②inner可以省略
③筛选条件放在where后面，连接条件放在on后面，提高了分离性，便于阅读
④inner join 连接和sql92语法的等值连接的效果是一样的，都是查询多表的交集
*/
#1.等值连接
#案例1: 查询员工名、部门名
SELECT `last_name`,`department_name`
FROM `employees` e
JOIN `departments` d
ON e.`department_id`=d.`department_id`;
#案例2: 查询名字中包含e的员工名和工种名（筛选）
SELECT `last_name`,`job_title`
FROM `employees` e
INNER JOIN `jobs` j
ON j.`job_id`=e.`job_id`
WHERE e.`last_name` LIKE "%e%";
#案例3: 查询部门个数大于3的城市名和部门个数（分组+筛选）
SELECT `city`,COUNT(*) 部门个数
FROM `locations` l
INNER JOIN `departments` d
ON d.`location_id`=l.`location_id`
GROUP BY `city`
HAVING COUNT(*)>3;
#案例4: 查询哪个部门的部门员工个数>3的部门名和员工个数，并按个数降序（排序）
SELECT COUNT(*) 员工个数,`department_name`
FROM `departments`d
INNER JOIN `employees` e
ON d.`department_id`=e.`department_id`
GROUP BY `department_name`
HAVING 员工个数>3
ORDER BY 员工个数 DESC;
#案例5: 查询员工名、部门名、工种名，并按部门名降序(添加三表连接)
SELECT `last_name`,`department_name`,`job_title`
FROM `employees` e
INNER JOIN `departments` d ON d.`department_id`=e.`department_id`
INNER JOIN `jobs` j ON j.`job_id`=e.`job_id`
ORDER BY `department_name` DESC;
#2.非等值连接
#案例1 查询员工的工资级别
SELECT `salary`,grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
#案例2 查询工资级别的个数>20的个数，并按工资级别降序
SELECT COUNT(*),grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC;
#3.自连接
#案例 查询姓名包含k的员工的名字、上级的名字
select e.`last_name`,m.`last_name`
FROM `employees` e
join `employees` m
on e.`manager_id`=m.`employee_id`
where e.`last_name` like '%k%';
#二) 外连接
/*
应用场景: 用于查询一个表中有，另一个表中没有的记录
特点:
1.外连接的查询结果为主表中的所有记录
如果从表中 有 和他匹配的值（连接条件一致），则显示匹配的值，
如果从表中 没有 和他匹配的值（连接条件不一致），则显示null
外连接的查询结果=内连接结果+主表有而从表没有的记录
2.左外连接，left join左边的是主表
右外连接，right join右边的是主表
3.左外和右外交换两表顺序，可以实现同样的效果
4.全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1中没有
*/
#引入: 查询没有男朋友不在男生表的女生名
#左外连接写法:
SELECT b.`name`,b.`boyfriend_id`,bs.`boyName`
FROM `beauty` b
LEFT OUTER JOIN `boys` bs
ON b.`boyfriend_id`=bs.`id`
WHERE bs.`boyName` IS NULL;
#右外连接写法:
SELECT b.`name`,b.`boyfriend_id`,bs.`boyName`
FROM `boys` bs
RIGHT OUTER JOIN `beauty` b
ON b.`boyfriend_id`=bs.`id`
WHERE bs.`boyName` IS NULL;
#案例1: 哪个部门没有员工
#左外连接写法:
SELECT d.*,e.`employee_id`
FROM `departments` d
LEFT OUTER JOIN `employees` e
ON d.`department_id`=e.`department_id`
GROUP BY d.`department_id`
HAVING e.`employee_id` IS NULL;
#右外连接写法:
SELECT d.*,e.`employee_id`
FROM `employees` e
RIGHT OUTER JOIN `departments` d
ON d.`department_id`=e.`department_id`
GROUP BY d.`department_id`
HAVING e.`employee_id` IS NULL;
#全外连接 (mysql不支持)
#查询没有男朋友不在男生表的女生名
SELECT b.*,bo.*
FROM `beauty` b
FULL OUTER JOIN `boys` bo
ON b.`boyfriend_id`=bo.`id`
#三) 交叉连接 （用99语法标准实现笛卡尔乘积）
SELECT b.*,bo.*
FROM `beauty` b
CROSS JOIN boys bo;
sql92 VS sql99
功能: 99支持较多
可读性: 99实现连接条件和筛选条件的分离，可读性高
554
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
