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(分组后筛选)