【MySQL】聚合函数和内置函数

---

1 🍑聚合函数🍑

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的 数量
SUM([DISTINCT] expr)	返回查询到的数据的 总和，不是数字没有意义
AVG([DISTINCT] expr)	返回查询到的数据的 平均值，不是数字没有意义
MAX([DISTINCT] expr)	返回查询到的数据的 最大值，不是数字没有意义
MIN([DISTINCT] expr)	返回查询到的数据的 最小值，不是数字没有意义

案例：我们使用的是students表和exam_result表：

• 统计班级共有多少同学？

• 统计班级收集的 qq 号有多少?

• 统计本次考试的数学成绩分数个数？

没有去重：

去重：

• 统计70 分以上的数学最低分；

---

2 🍑group by子句的使用🍑

在select中使用group by 子句可以对指定列进行分组查询。
语法：

select column1, column2, .. from table group by column;

案例：
准备工作：创建一个雇员信息表（来自oracle 9i的经典测试表）
参考下面：

• 如何显示每个部门的平均工资和最高工资？

• 显示每个部门的每种岗位的平均工资和最低工资；

• 显示平均工资低于2000的部门和它的平均工资；

注意：having和group by配合使用，对group by结果进行过滤。

总结SQL查询中各个关键字的执行先后顺序：

from > on> join > where > group by > with > having > select> distinct > order by > limit

---

3 🍑内置函数🍑

3.1 🍎日期函数🍎

函数名称	描述
current_date()	当前日期
current_time()	当前时间
current_timestamp()	当前时间戳
date(datetime)	返回data参数的日期部分
date_add(date, interval d_value_type)	interval后的数值单位可以是：year,minute,second,day
date_sub(date, interval d_value_type)	interval后的数值单位可以是：year,minute,second,day
datediff(date1, date2)	两个日期的差，单位是天
now()	当前的日志时间

• 在日期的基础上加日期；

• 计算两个日期之间相差多少天；

• 创建一个留言表插入数据，显示所有留言信息，发布日期只显示日期，不用显示时间，以及查询在2分钟内发布的帖子；

显示所有留言信息，发布日期只显示日期，不用显示时间：

查询在2分钟内发布的帖子：

---

3.2 🍎字符串函数🍎

函数名称	描述
charset(str)	返回字符串字符集
concat(str, [……])	连接字符串
instr(str, sunstring)	返回substring在string中出现的位置，没有则返回0
ucase(str)	转化成大写
lcase(str)	转化成小写
left(str, len)	从str左边取len个字符
length(str)	str的长度
replace(str, serstr, repstr)	在str中用repstr替代serstr
strcmp(str1, str2)	比较两个字符串
substring(str, pos, len)	从str的pos位置开始截取len的字符
ltrim(str) rtrim(str) trim(str)	除去前空格或者后空格

案例:

• 获取emp表的ename列的字符集

• 以首字母小写的方式显示所有员工的姓名；

3.3 🍎数学函数🍎

函数名称	描述
abs(num)	绝对值
bin(num)	十进制转二进制
hex(num)	十进制转十六进制
conv(num, form_base, to_base)	form_base进制转化为to_base进制
ceiling(num)	向上取整
floor(num)	向下取整
format(num, decimal_places)	格式化小数位数
mod(num)	取模
rand()	返回随机浮点数->[0.0, 1.0)

---

4 🍑其它函数🍑

• user() 查询当前用户:

mysql> select user();
+--------+
| user() |
+--------+
| root@  |
+--------+
1 row in set (0.00 sec)

• md5(str)对一个字符串进行md5摘要，摘要后得到一个32位字符串:

mysql> select md5('hello');
+----------------------------------+
| md5('hello')                     |
+----------------------------------+
| 5d41402abc4b2a76b9719d911017c592 |
+----------------------------------+
1 row in set (0.00 sec)

• database()显示当前正在使用的数据库:

mysql> select database();
+------------+
| database() |
+------------+
| scott      |
+------------+
1 row in set (0.00 sec)

• password()函数，MySQL数据库使用该函数对用户加密:

mysql> select password('grm');
+-------------------------------------------+
| password('grm')                           |
+-------------------------------------------+
| *3170D93DA75A9CE48555747589F6EFC4C29276D7 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)

• ifnull（val1， val2） 如果val1为null，返回val2，否则返回val1的值:

mysql> select ifnull(null, '123');
+---------------------+
| ifnull(null, '123') |
+---------------------+
| 123                 |
+---------------------+
1 row in set (0.00 sec)

---