MySQL 函数介绍

本部分主要介绍：MySQL 的函数。函数分为：单行函数（目的：处理数据、筛选）；聚合函数（目的：统计）

单行函数

介绍
- 只对一行进行变换，每行返回一个结果
- 可以嵌套
- 参数可以是一个字段、一个表达式或一个值
- 包含几种数据类型函数
  1. 字符串函数
  2. 数学函数
  3. 日期时间函数
  4. 加密函数

字符串函数

如表

函数	用法
concat(S1, S2, ... Sn)	连接 S1, S2, ... Sn 为一个字符串
concat_ws(s, S1, S2, ... Sn)	同函数 concat 一样，注释字符串之间有个 s
char_length(s)	返回字符串 s 的字符数
length(s)	返回字符串的字节数（utf-8 一个中文占 3 个字节）
insert(str, index, len, instr)	将字符串 str 从第 index 开始，len 个长度的子字符串替换为 instr
upper(s)/ucase(s)	将字符串转换为大写字母
lower(s)/lcase(s)	将字符串转换为小写字母
left(s, n)	返回字符串最左边的 n 个字符
righ(s, n)	返回字符串最右边的 n 个字符
lpad(str, len, pad)	用字符串 pad 对 str 字符串最左边进行填充，知道 str 长度为 len
rpad(str, len, pad)	用字符串 pad 对 str 字符串最右边进行填充，知道 str 长度为 len
ltrim(s)	去掉字符串 s 左侧空格
rtrim(s)	去掉字符串 s 右侧空格
trim(s)	去掉 s 开始和结尾处的空格
trim([both], s1 form s)	去掉字符串 s，开始和结尾处的 s1
trim([leading], s1 form s)	去掉字符串 s，开始处的 s1
trim([trailing], s1 form s)	去掉字符串 s，结尾处的 s1
repeat(str, n)	返回 str 重复 n 次结果
replace(str, a, b)	用字符串 b 替换在 str 中出现的 a字符串
strcmp(s1, s2)	比较字符串 s1 和 s2
substring(s, index, len)	返回 s 字符串从 index 位置的 len 长度子字符串

使用实例

concat_ws(s, S1, S2,... Sn) 字符串拼接

mysql> select concat_ws('-', bid, bname, price) as '拼接演示' from books;
+----------------+
| 拼接演示       |
+----------------+
| 1-java书-100   |
| 2-mysql书-102  |
| 3-css-90       |
+----------------+
3 rows in set (0.00 sec)

数值函数

如表

函数	用法
abs(x)	返回 x 的绝对值
ceil(x)	返回大于 x 的最小整数
floor(x)	返回小于 x 的最小整数
mod(x, y)	返回 x / y 的模
rand()	返回 0-1 随机数
round(x, y)	返回参数 x 保留 y 位小数的值（采用四舍五入）
truncate(x, y)	返回参数 x 保留 y 位小数的值（采用阶段）
squt(x)	返回 x 的平方
pow(x, y)	返回 x 的 y 次方

日期函数

如图（参数为时间数据类型）

函数	用法
curdate()/current_date()	返回当前日期
curtime()/current_time()	返回当前时间
now()/sysdate()/current_timestamp()/localtime()/localtimestamp()	返回当前系统日期时间
year(date)/month(date)/day(date)/hour(time)/minute(time)/second(time)	返回具体时间值
week(date)/weekofyear(date)	返回一年中的第几周
dayofweek(date)	返回周几（周日为 1，周六为 7）
weekday(date)	返回周几（周一为 0，周六为 6）
dayname(date)	返回星期（monday，tuesday...）
monthname(date)	返回月份（january）
datediff(date1, date2)/timediff(time1, time2)	返回 date1 - date2 日期间隔/time1 - time2 时间间隔
date_add(datetime, interval expr type)	返回与给定日期时间相差 interval 时间段的日期时间
date_format(datetime, fmt)	按照字符串格式 fmt，格式化 datetime 值
str_to_date(str, fmt)	按照字符串 fmt 格式解析 str为一个日期

date_add(datetime, interval expr type)
1. 表达式类型
  
  参数类型参数类型
  
  year year_month
  
  month day_hour
  
  day day_minute
  
  hour day_second
  
  minute hour_minute
  
  second hour_second
  
  minute_second
2. 实例演示
  - select date_add(now(), interval 1 year); 在当前时间添加 1 年，返回时间
```
mysql> select date_add(now(), interval 1 year);
+----------------------------------+
| date_add(now(), interval 1 year) |
+----------------------------------+
| 2021-05-28 16:13:23              |
+----------------------------------+
1 row in set (0.00 sec)
```
  - select date_add(now(), interval -1 year); 在当前时间减去 1 年
  - select date_add(now(), interval '1_2' year_month); 当前时间添加 1年 1 个月

参数类型	参数类型
year	year_month
month	day_hour
day	day_minute
hour	day_second
minute	hour_minute
second	hour_second
	minute_second

date_format(datetime, fmt) 和 str_to_date(str, fmt) 中格式说明

如表

格式符（1）	说明（1）	格式符（2）	说明（2）
%Y	以 4 位数表示年份	%y	以 2 位数表示年份
%M	以英文（January）表示月份	%m	以 2 位数表示月份（01, 02）
%b	以英文缩写（jan）表示月份	%c	以数字表示月份（1, 2）
%D	以英文后缀名（1st, 2nd, 3rd）表示月份中天数	%d	以 2 位数表示月份中天数（01, 02）
%e	以数字形式（1, 2, 3）表示月中的天数
%H	以 2 为数（01， 02）表示小时数（24 小时制）	%h 和 %I	以 2 为数（01， 02）表示小时数（12 小时制）
%k	以数字形式（1, 2）表示小时数（24小时制）	%I	以数字形式（1, 2）表示小时数（12 小时制）
%i	以两位数表示分钟数（00, 01）	%S 和 %s	以两位数表示秒数（00, 01）
%W	一周中的星期名称（Sunday, Tuesday）	%a	一周中星期的缩写（Sun, Mon, Tues）
%w	以数字表示一周中的天数（0 = Sunday）

实例

格式字符的使用，要看存入的时间的格式如何

存入时间格式 2020-01-01

mysql> select date_format('2020-10-10' ,'%Y年%m月%d日');
+----------------------------------------------+
| date_format('2020-10-10' ,'%Y年%m月%d日')    |
+----------------------------------------------+
| 2020年10月10日                               |
+----------------------------------------------+
1 row in set (0.00 sec)

解析 2020-20-10 字符串

mysql> select str_to_date('2020-10-10' ,'%Y-%m-%d');
+---------------------------------------+
| str_to_date('2020-10-10' ,'%Y-%m-%d') |
+---------------------------------------+
| 2020-10-10                            |
+---------------------------------------+
1 row in set (0.00 sec)

流程函数

如表

函数	用法
if(vaule, t, f)	如果 value 位真，返回 t，否则返回 f
ifnull(value1, valude2)	如果 value1不为空，返回 value，否则返回 value2
case where 条件1 then result1 where 条件2 result2 ... [else result] end	相当于 if...else if...else
case expr where 常量1 then 值1 where 常量2 then 值2 ... [else 值n] end	相当于switch...case

其它函数

如表

函数	用法
datebase()	返回当前版本数据库
version()	返回当前数据库版本
user()	返回当前登录用户
passwd(str)	返回 str 加密版，41 位长字符串
md5(str)	返回 str MD5 值

聚合函数

说明：常用 5 个聚合函数，只能查看最后结果

介绍

数据库为快速统计数据，提供了一些 聚合函数

常用 5 个聚合函数

函数	作用
count(*/字段)	统计总行数，不用指出具体哪一行
max(列)	表示求此列的最大值，指明具体哪一列
min(列)	表示求此列的最小值，指明具体哪一列
sum(列)	表示求此列的和，指明具体哪一列，该列数据必须为数字
avg(列)	表示求此列的平均值，指明具体哪一列，该列数据必须为数字