MYSQL 数据库查询语言（DQL） 函数 分组查询

1、函数

（1）概念 （类似于java的方法）

将一组逻辑语句封装在方法体中，对外暴露方法名。隐藏了实现细节，同时提高了代码重用性

（2）语法

 SELECT 函数名（实参列表） 【FROM 数据表】

（3）分类

① 单行函数  

如 concat、length、ifnull等

说明：单行函数都有返回值，将返回值作为查询结果

扩展：几个特殊函数 VERSION()、DATABASE()、USER()

SELECT VERSION(); 查看版本号

SELECT DATABASE();查看当前数据库

SELECT USER();查看当前用户  即 root

concat(str1,str2...)

exp：拼接last_name 列 和 first_name 列 取名为姓名，中间用 _ 隔开

SELECT CONCAT（last_name,'_',first_name）姓名 FROM employees;

② 分组函数

做统计用，输入一组数据（多行），输出一个结果。又称为统计函数，聚合函数，组函数

常用分组函数：sum 求和、avg 平均值、 max 最大值 、 min 最小值、 count 计算个数

特别注意1：以上分组函数统计的时候，都会忽略null

特别注意2：和分组函数一同查询的字段要求是group by后的字段。否则表格不规范

比如：SELECT AVG(salary),employee_id FROM employees;

AVG(salary)是聚合函数，只有一行。employee_id 是普通字段，有107行。所以一起查询，表格就不规范

技巧1：分组函数搭配distinct可实现去重

技巧2：一般使用count(*)统计数据表的行数。因为每一列都可能有null值，count(某一列)，遇到null就不准确。count(*)可以统计每一列，不同列的null可以互补。最简单的统计行数办法是count(1)，新增一列1，并统计行数

exp：计算员工的总工资，平均工资，最高工资

SELECT SUM(【DISTINCT】salary) 和,AVG(salary) 平均,MAX(salary) 最高, FROM employees;

说明：197行员工的工资数据经各种函数计算后 各自合并成1 行。加了distinct以后，会去掉相同数据salary的行，再计算

2、分组查询

引入：查询每个部门的平均工资

显然，将各个部门按照部门ID分组，然后分组统计不同组的平均工资

注意：上面讲的SELECT AVG(salary) FROM employees 相当于把整个公司的员工分成一个大组。

说明：分组查询目标是将数据表按照某个字段（group by后面的分组字段）分成多个组，然后用特定的聚合函数计算每个组的目标值。因此SELECT 后面查询的是 分组函数 和 分组字段

总结：先group by 某个字段分组，再聚合函数按照那个分组做统计

（1）分组前筛选，先筛选（WHERE），再分组（GROUP BY），再统计（聚合函数）

分组前筛选的筛选条件是根据原始数据表来筛选

语法：

SELECT 查询列表 FROM 数据表 【WHERE 筛选条件】

GROUP BY 分组列表 【ORDER BY 排序列表】

注意：查询列表只能是分组函数，和group by后出现的字段（分组字段）。分组列表里面可以有多个分组字段

说明：

① 分组字段，也支持按照函数分组，比如按照姓名长度分组 GROUP BY LENGTH(last_name)

② 分组列表，支持多个分组字段。聚合函数将合并所有分组字段都相等的行。

exp1（多个分组字段）：查询每个部门每个工种的平均工资

SELECT AVG(salary),department_id, job_id FROM employees GROUP BY department_id, job_id;

说明：数据表中department_id , 和 job_id 都相等的行，自动分成一组，然后用聚合函数统计。

exp2（单个字段分组）：查询每个工种的最高工资（按照job_id分组后，用avg聚合函数求值）

SELECT MAX（salary）, job_id FROM employees GROUP BY job_id;

exp:查询邮箱中包含a字符的，每个部门的平均工资

SELECT MAX（salary）, department_id FROM employees WHERE email LIKE '%a%' GROUP BY department_id;

（2）分组后筛选，先分组（GROUP BY），再统计（聚合函数），得到新的表，在新的表中筛选（HAVING）

语法：

SELECT 查询列表 FROM 数据表 GROUP BY 分组列表 HAVING 筛选条件;

注意：查询列表只能是分组函数，和group by后出现的字段（分组字段）。

说明：是在得到分组查询的结果候，在结果中再筛选

exp：查询每个部门的员工个数

SELECT COUNT(*), department_id FROM employees GROUP BY department_id HAVING COUNT(*)>2;

说明：先得到每个部门的员工个数（先分组），再筛选大于2的部门

分组查询总结：筛选条件是根据原始数据表的筛选条件就是WHERE（分组前）。是在分组后得到的新表中筛选，就是HAVING（分组后筛选）