1. 分组函数
功能： 用作统计使用, 又称为聚合函数或者统计函数或者组函数
分类： sum 求和, avg 平均数, max 最大值, min 最小值, count 计算个数
特点： (1) sum, avg只能用于处理数值型;
(2) max, min, count 可以处理任何类型;
(3) 以上分组函数都忽略 null 值
(4) 一般使用count(*) 用作统计行数; 或者使用 count(1)作统计行数
(5) 和分组函数一同查询的字段要求是 group by 后的字段1.2 求和
# select sum(salary) from employees;1.3 平均值
# select avg(salary) from employees;1.4 最大值
# select max(salary) from employees;1.5 最小值
# select min(salary) from employees;1.6 求总个数
# select count(salary) from employees;1.7 求和，平均值，最大值，最小值，总个数
# select sum(salary) as 和, avg(salary) as 平均值, max(salary) as 最大值, min(salary) as 最小值, count(salary) as 个数 from employees;1.8 round() 范围
# select round(avg(salary), 2) as 平均数 from employees; // 平均数保留两位小数1.9 分组函数和 distinct 搭配(去重)
# select sum(distinct salary), sum(salary) from employees;2.1 查询公司员工工资的最大值, 最小值, 平均值, 总和
# select max(salary) as 最大值, min(salary) as 最小值, avg(salary) as 最小值, count(salary) as 平均值 from employees;2.2 查询员工表中的最大入职时间和最小入职时间的相差天数(计算参数1和参数2相差天数：datediff(参数1, 参数2))
# select diffdate(max(hiredate), min(hiredate)) as 入职相差天数 from employees;2.3 查询部门编号为 90 的员工个数
# select count(*) as 个数 from employees where partment_id = 90;2.4 分组数据： group by 子句语法
可以使用 group by 子句将表中的数据分成若干组
语法：
select 分组函数, 列 (要求出现在 group by 的后面)
from 表
when 筛选条件
group by 分组的列
order by 排序【desc|asc】;
注意： 查询列表必须特殊, 要求是分组函数和group by 后出现的字段2.5 查询每个工种的最高工资
# select max(salary), job_id from employees group by job_id;2.6 查询每个位置上的部门个数
# select count(*), location_id from employees group by location_id;2.7 查询邮箱中包含 a 字符的, 每个部门的平均工资
# select svg(salary), department_id from employees where email like '%a%' group by department_id;2.8 查询有奖金的每个领导手下员工的最高工资
# select max(salary), manager_id from employees where salary is not null group by manager_id;2.9 查询哪个部门的员工个数 > 2
(1) 首先查询每个部门的员工个数
# select count(*), department_id from employees group by department_id;
(2) 再（1）的基础上, 进行筛选, 查询哪个部门的员工个数 > 2
# select count(*), department_id from employees group by department_id having count(*) > 2;3.1 查询每个工种有奖金的员工的最高工资 > 12000 的工种编号和最高工资
（1）首先查询每个工种有奖金的员工的最高工资
# select max(salary), job_id from employees where commission_pct is not null group by job_id;
（2）再（1）的基础上 继续筛选，最高工资 > 12000
# select max(salary), job_id from employees where commission_pct is not null group by job_id having max(salary) > 12000;3.2 查询领导编号 > 120 的每个领导手下的最低工资>5000的领导编号是哪个, 以及其最低工资
（1） 首先查询每个领导手下的员工固定最低工资
# select min(salary), manager_id from employees group by manager_id;
（2）在（1）的基础上，继续筛选： 编号 > 102
# select min(salary), manager_id from employees where manager_id > 102 group by manager_id
（3） 在（2）的基础上， 继续筛选 最低工资 > 5000
# select min(salary), manager_id from employees where manager_id > 102 group by manager_id having min(salary) > 50003.3 分组查询
语法: select 分组函数, 列 (要求出现在 group by 的后面) from 表 where 筛选条件 group by 分组的列表 order by 子句;
注意： 查询列表必须特殊, 要求是分组函数和 group by 后出现的字段
特点：分组查询中的筛选条件分为两类
（1）分组函数做条件肯定是放在 having 子句中
（2）能用分组前筛选的, 就优先考虑使用分组前筛选3.4 按员工姓名的长度分组, 查询每一组的员工个数, 筛选员工个数 > 5 的有哪些
（1） 首先查询每个长度的员工个数
# select count(*), length(last_name) len_name from employees group by length(last_name)
（2） 添加筛选条件 (支持起别名)
# select count(*) c, length(last_name) len_name from employees group by len_name having > 5;3.5 查询每个部门每个工种的员工的平均工资（按多个字段分组）
# select ave(salary), department_id, job_id from employees group by job_id, department_id; // job_id = department_id 为一组3.6 查每个部门每个工种的员工的平均工资, 并且按平均工资的高低显示
# select avg(salary), department_id, job_id from employees where department_id id not null group by jobb_id, department_id having avg(salary) > 10000 order by avg(salary) desc;3.7 等值连接
（1） 多表等值连接的结果为多表的交集部分
（2） n 表连接, 至少需要 n-1 个链接条件
（3） 多表的顺序没有要求
（4） 一般需要为表起别名
（5）可以搭配前面介绍的所有子句使用，比如排序、分组、筛选// 非等值连接
3.8 查询员工的工资和工资级别
# select salary, grade_level from employees e, job_grades g where salary between g.`lowest_sal` and g.`highest_sal` and g.`grade_level`='A';// 非等值连接
3.9 查询员工名和上级的名称
# select e.employees_id, e.last_name, m.employee_id, m.last_name from employees e, employees m where e.`manager_id`=m.`employees_id`4.1 显示员工表的最大工资， 工资平均值
# select max(salary), avg(salary) from employees;4.2 查询员工表的 employees_id, job_id, last_name， 按 department_id 降序, salary升序
# select employees_id, job_id, last_name from employees order by department_id desc, salary asc;4.3 查询员工表的 job_id 中包含 a和e的, 并且a在e的前面
# select job_id from employees where job_id like `%a%e%`4.5 已知表 student 里面有 id (学号)、name(姓名), gradeId(年级编号)；已知 grade 里面有 id(年级编号)、name (年级名)；已知result 里面有 id, score, studentNo(学号) 要求查询姓名、年级名、成绩
# select s.name, g.name, r.score from sudent s, grade g, result r where s.id=r.studentNo and g.id = s.gradeId4.6 显示当前日期, 以及去前后空格, 截取子字符串的函数
select now();
select trim( 字符 from '' )
select substr(str, startIndex)
select substr(str, startIndex, length)4.7 筛选
select 查询列表 from 表名 where 筛选条件 order by 排序列表 [asc | desc]
// asc 升序, 如果不写默认升序; desc 降序
// 排序列表支持单个字段、多个字段、函数、表达式、别名
// order by 的位置一般放在查询语句的最后（除 limit 语句之外）
// 字符函数
（1） concat: 字符串连接
（2） substr: 截取子串
（3） upper: 变大写
（4） lower: 变小写
（5） replace: 替换
（6） length: 获取字节长度
（7） trim: 去除前后空格
（8） lpad: 左填充
（9） rpad: 右填充
（10）instr: 获取子串第一次出现的索引
// 数学函数
(1) ceil: 向上取整
(2) round: 四舍五入
(3) mod: 取模
(4) floor: 向下取整
(5) truncate: 截断
(6) rand: 获取随机数，返回 0-1之间的小数
// 日期函数
(1) now: 返回当前日期+时间
(2) year: 返回年
(3) month: 返回月
(4) day: 返回日
(5) date_format: 将日期转换字符
(6) curdate: 返回当前日期
(7) str_to_date: 将字符转换成日期
(8) curtime: 返回当前时间
(9) hour: 小时
(10) minute: 分钟
(11) second: 秒
(12) datediff: 返回两个日期相差的天数
(13) monthname: 以英文形式返回月
// 其他函数
（1） version: 查看当前数据库服务器的版本
（2） database: 查看当前打开的数据库
（3） user: 当前用户
（4） password("字符")： 返回该字符的密码形式
（5） md5("字符")： 返回该字符的MD5加密形式
// if(条件表达式, 表达式1, 表达式2)： 如果条件表达式成立, 返回表达式1, 否则返回表达式2
// case 使用 （第一种方式）
case 变量或者表达式或者字段
when 常量1 then 值1
when 常量2 then 值2
...
else 值n
end
// case 使用 （第二种方式）
case
when 条件1 then 值1
when 条件2 then 值2
...
else 值n
end
// 分组函数
（1） 分类
max: 最大值
min: 最小值
sum: 和
avg: 平均值
count: 计算个数
（2） 语法： select max(字段) from 表名
2.1 sum 和 avg 一般用于处理数值型
2.2 max、min、count 可以处理任何数据类型
2.3 以上分组函数都忽略 null
2.4 都可以搭配 distinct 使用, 实现去重的统计, select sum(distinct 字段) from 表;
2.5 count 函数
2.5.1 count(字段)： 统计该字段非空值的个数
2.5.2 count(*)：统计结果集的行数
2.5.3 count(1): 统计结果集的行数4.8 筛选语句顺序
# select 分组函数, 分组后的字段 from [where 筛选条件] group by 分组的字段 [having 分组后的筛选] [order by 排序列表]
// 当查询中涉及到了多个表的字段, 需要使用多表连接
# select 字段1, 字段2 from 表1, 表2, 表3....
// 等值连接
# select 查询列表 from 表1 别名, 表2 别名 where 表1.key = 表2.key [and 筛选条件] [group by 分组字段] [having 分组后的筛选] [order by 排序字段]
// 非等值连接
# select 查询列表 from 表1 别名, 表2 别名 where 非等值的连接条件 [and 筛选条件] [group by 分组字段] [having 分组后的筛选] [order by 排序字段]
// 自连接
# select 查询列表 from 表1 别名1, 表2 别名2 where 等值的连接条件 [and 筛选条件] [group by 分组字段] [having 分组后的筛选] [order by 排序字段]4.9 显示所有员工的姓名, 部门号和部门名称
# select last_name, d.department_id, department_name from employees e, departments d where e.`department_id` = d.`department_id`5.1 查询90号部门员工的job_id和90号部门的location_id
# select job_id, location_id from employees e, departments d where e.`department_id`=d.`department_id` and e.`department_id` = 90;5.2 选择所有有奖金的员工的 last_name, department_name, location_id, city
# select last_name, department_name, l.location_id, city from employees e, departments d, locations l where e.department_id=d.department_id and d.location_id=l.location_id and e.commission_pct is not null;5.3 选择city在Toronto工作的员工的 last_name, job_id, department_id, department_name
# select last_name, job_id, d.department_id, department_name from employees e, departments d, location l where e.department_id=d.department_id and d.location_id=l.location_id and city='Toronto';5.4 查询每个工种、每个部门的部门名、工种名和最低工资
# select department_name, job_title, min(salary) 最低工资 from employees e, departments d, jobs j where e.department_id=d.department_id and e.job_id=j.job_id group by deprtment_name, job_title;5.5 查询每个国家下的部门个数大于2的国家编号
# select count_id, count(*) 部门个数 from departments d, locations l where d.location_id=l.location_id group by countryy_id having count(*) > 2;5.6 选择指定员工的姓名、员工号、以及他的管理者的姓名和员工号, 结果类似于下面的格式
employees Emp# manager Mgr#
kochhar 101 king 100
# select e.last_name employees, e.employee_id "Emp#", m.last_name manager, m.employee_id "Mgr#" from employees e, employees m where e.manager_id=m.employee_is and e.last_name='kochhar';5.7 语法：
select 查询列表 from 表1 别名 [连接类型] join 表2 别名 on 连接条件 [where 筛选条件] [group by 分组] [having 筛选条件] [order by 排序列表];
// 连接类型
内连接： inner
外连接：
左外：left [outer]
右外：right [outer]
全外： full [outer]
交叉连接; cross
// 内连接
语法： select 查询列表 from 表1 别名 inner join 表2 别名 on 连接条件;5.8 等值连接
（1） 查询员工名、部门名
# select last_name, department_name from employees e inner join departments d on e.department_id = d.department_id;5.9 查询名字中包含e的员工名和工种名(添加筛选)
# select last_name, job_title from employees e inner join jobs j on e.job_id=j.job_id where e.last_name like '%e%';6.1 查询部门个数>3 的城市名和部门个数
(3.1) 查询每个城市的部门个数
# select city, count(*) 部门个数 from departments d inner join location l on d.location_id=l.location_id group by city;
(3.2) 在 (3.1) 的基础上继续筛选满足部门个数大于3
# select city, count(*) 部门个数 from departments d inner join location l on d.location_id=l.location_id group by city having count(*)>3;6.2 查询哪个部门的员工个数>3的部门名和员工个数, 并按个数降序
（4.1）查询每个部门的员工个数
# select count(*), department_name from employees e inner join departments d on e.department_id=d.department_id group by department_name;
(4.2) 在（4.1）结果的基础上继续筛选员工个数>3的数据，最后在排序
# select count(*) 个数, department_name from employees e inner join departments d on e.department_id=d.department_is group by department_name having count(*)>3 order by count(*) desc;6.3 查询员工名、部门名、工种名、并按部门名降序
# select last_name, department_name, job_title from employees e inner join departments d on e.department_id=d.deparment_id order by department_name desc;// 非等值连接
6.4 查询员工的工资级别
# select salary, grade_level from employees e join job_grades g on e.salary between g.lowest_sal and g.highest_sal;// 非等值连接
6.5 查询工资级别的个数>20的个数, 并且按工资级别降序
# select count(*), grade_level from employees e, join job_grades g on e.salary between g.lowest_sal and g.jighest_sal group by grade_level having count(*)>20 order by grade_level desc// 非等值连接
6.6 查询员工的名字、上级的名字
# select e.last_name, m.last_name from employees e join employees m on e.manager_id=m.employee_id// 非等值连接
6.7 查询姓名中包含字符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）外连接的查询结果为主表中的所有记录，如果从表中有和它匹配的，则显示匹配值；（2）如果从表中没有和它匹配的，则显示null；（3）外连接查询的结果=内连接结果+主表中有而从不表中没有的记录。
左外连接：left join 左边的是主表
右外连接：right join 右边的是主表
左外和右外交换两个表的顺序，可以实现同样的效果
全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的// 总结
// 内连接： 两个表公共部分（select 查询条件 from 表1 innner join 表2 on 表1.key=表2.key）
// 左外连接： 主表有从表没有+内连接部分（select 查询条件 from 主表 left join 从表 on 主表.key=从表.key）
// 右外连接： 主表有从表没有+内连接部分 （select 查询条件 from 从表 right join 主表 on 主表.key=从表.key）
// 全连接： 左外连接+右外连接-内连接（select 查询条件 from 表1 full join 表2 on 表1.key=表2.key）
// 查询主表有从表没有：在左外连接的基础上添加条件 （select 查询条件 from 主表 left join 从表 on 主表.key=从表.key where 从表.key is null）
// 查询主表有从表没有：在右外连接的基础上添加条件 （select 查询条件 from 从表 right join 主表 on 主表.key=从表.key where 从表.key is null）
// 查询表1和表2非共同部分：（select 查询条件 from 表1 full join 表2 on 表1.key=表2.key where 表1.key is null or 表2.key is null）6.8 查询编号>3的女神的男朋友信息, 如果有则列出详细, 如果没有， 用 null 填充
# select b.id, b.name, bo.* from beauty b left outer join boys bo on b.boyfriend_id = bo.id where b.id>3;6.9 查询哪个城市没有部门
# select city, d.* from departments d right outer join locations l on d.location_id=l.location_id where d.department_id is null7.1 查询部门名为sal 或it 的员工信息
# select 2.* d.department_name from departmets d left join employees e on d.department_id=e.department_id where d.department_name in('sal', 'IT');
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
