深入浅出MySQL阅读笔记四:常用函数

深入浅出MySQL阅读笔记四:常用函数

1. 字符串函数

函数 功能
CANCAT(S1,S2,…Sn) 连接 S1,S2,…Sn 为一个字符串
INSERT(str,x,y,instr) 将字符串 str 从第 x 位置开始,y 个字符长的子串替换为字符串 instr,会覆盖原有的
LOWER(str) 将字符串 str 中所有字符变为小写
UPPER(str) 将字符串 str 中所有字符变为大写
LEFT(str ,x) 返回字符串 str 最左边的 x 个字符
RIGHT(str,x) 返回字符串 str 最右边的 x 个字符
LPAD(str,n ,pad) 用字符串 pad 对 str 最左边进行填充,直到长度为 n 个字符长度
RPAD(str,n,pad) 用字符串 pad 对 str 最右边进行填充,直到长度为 n 个字符长度
LTRIM(str) 去掉字符串 str 左侧的空格
RTRIM(str) 去掉字符串 str 行尾的空格
REPEAT(str,x) 返回 str 重复 x 次的结果
REPLACE(str,a,b) 用字符串 b 替换字符串 str 中所有出现的字符串 a
STRCMP(s1,s2) 比较字符串 s1 和 s2 的 ASCII 码值的大小。如果 s1 比 s2 小,那么返回-1;如果 s1 与 s2 相等,那么返回 0;如果 s1 比 s2 大,那么返回 1。
TRIM(str) 去掉字符串行尾和行头的空格
SUBSTRING(str,x,y) 返回从字符串 str x 位置起 y 个字符长度的字串

tips:
mysql索引的起始位置是1,不是0

2. 数值函数

函数 功能
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 位小数的结果
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 取0~100内的随机整数
mysql> select round(100*rand()),round(100*rand());
+-------------------+-------------------+
| round(100*rand()) | round(100*rand()) |
+-------------------+-------------------+
| 55 | 43 |
+-------------------+-------------------+
-- round(x,y)和truncate(x,y)
mysql> select round(1.23456,4),truncate(1.23456,4);
+------------------+---------------------+
| round(1.23456,4) | truncate(1.23456,4) |
+------------------+---------------------+
| 1.2346 | 1.2345 |
+------------------+---------------------+

3. 日期和时间函数

函数 功能
CURDATE() 返回当前日期
CURTIME() 返回当前时间
NOW() 返回当前的日期和时间
UNIX_TIMESTAMP(date) 返回日期 date 的 UNIX 时间戳
FROM_UNIXTIME(unix_timestamp,format) 返回 UNIX 时间戳的日期值
WEEK(date) 返回日期 date 为一年中的第几周
YEAR(date) 返回日期 date 的年份
HOUR(time) 返回 time 的小时值
MINUTE(time) 返回 time 的分钟值
MONTHNAME(date) 返回 date 的月份名
DATE_FORMAT(date,fmt) 返回按字符串 fmt 格式化日期 date 值
DATE_ADD(date,INTERVAL expr type) 返回一个日期或时间值加上一个时间间隔的时间值
DATEDIFF(expr,expr2) 返回起始时间 expr 和结束时间 expr2 之间的天数
  • UNIX_TIMESTAMP():没有参数,返回当前时间戳
  • DATE_FORMAT(date,fmt):按字符串 fmt 格式化日期 date 值,此函数能够按指定的
    格式显示日期,可以用到的格式符如表所示
格式符 格式说明
%S,%s 两位数字形式的秒(00,01,…,59)
%i 两位数字形式的分(00,01,…,59)
%H 两位数字形式的小时,24 小时(00,01,…,23)
%h,%I 两位数字形式的小时,12 小时(01,02,…,12)
%k 数字形式的小时,24 小时(0,1,…,23)
%l 数字形式的小时,12 小时(1,2,…,12)
%T 24 小时的时间形式(hh:mm:ss)
%r 12 小时的时间形式(hh:mm:ssAM 或 hh:mm:ssPM)
%p AM 或 PM
%W 一周中每一天的名称(Sunday,Monday,…,Saturday)
%a 一周中每一天名称的缩写(Sun,Mon,…,Sat)
%d 两位数字表示月中的天数(00,01,…,31)
%e 数字形式表示月中的天数(1,2,…,31)
%D 英文后缀表示月中的天数(1st,2nd,3rd,…)
%w 以数字形式表示周中的天数(0=Sunday,1=Monday,…,6=Saturday)
%j 以 3 位数字表示年中的天数(001,002,…,366)
%U 周(0,1,52),其中 Sunday 为周中的第一天
%u 周(0,1,52),其中 Monday 为周中的第一天
%M 月名(January,February,…,December)
%b 缩写的月名(January,February,…,December)
%m 两位数字表示的月份(01,02,…,12)
%c 数字表示的月份(1,2,…,12)
%Y 4 位数字表示的年份
%y 两位数字表示的年份
%% 直接值“%”

一般格式:

1
2
3
4
5
6
mysql> select date_format(now(),'%Y-%m-%d %H:%i:%s');
+----------------------------------------+
| date_format(now(),'%Y-%m-%d %H:%i:%s') |
+----------------------------------------+
| 2018-11-08 11:06:17 |
+----------------------------------------+

  • DATE_ADD(date,INTERVAL expr type):其中 INTERVAL 是间隔类型关键字,expr 是一个表达式,这个表达式对应后面的类型,type是间隔类型,MySQL提供了 13 种间隔类型,如表所示
表达式类型 描述 格式
HOUR 小时 hh
MINUTE mm
SECOND ss
YEAR YY
MONTH MM
DAY DD
YEAR_MONTH 年和月 YY-MM
DAY_HOUR 日和小时 DD hh
DAY_MINUTE 日和分钟 DD hh:mm
DAY_ SECOND 日和秒 DD hh:mm:ss
HOUR_MINUTE 小时和分 hh:mm
HOUR_SECOND 小时和秒 hh:ss
MINUTE_SECOND 分钟和秒 mm:ss
1
2
3
4
5
6
7
mysql> select now() current, date_add(now(),interval '10-6' year_month) after10y
ear6month,date_add(now(),interval '01 01:01:01' day_second) after1day1h1m1s;
+---------------------+---------------------+---------------------+
| current | after10year6month | after1day1h1m1s |
+---------------------+---------------------+---------------------+
| 2018-11-08 11:21:43 | 2029-05-08 11:21:43 | 2018-11-09 12:22:44 |
+---------------------+---------------------+---------------------+

4. 流程函数

函数 功能
IF(value,t f) 如果 value 是真,返回 t;否则返回 f
IFNULL(value1,value2) 如果 value1 不为空返回 value1,否则返回 value2
CASE WHEN [value1] THEN[result1]…ELSE[default]END 如果 value1 是真,返回 result1,否则返回 default
CASE [expr] WHEN [value1] THEN[result1]…ELSE[default]END 如果 expr 等于 value1,返回 result1,否则返回 default
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 建表
drop table if exists salary;
create table salary (
userid int,
salary decimal(9,2)
);
-- 插入数据
insert into salary values(1,1000),(2,2000), (3,3000),(4,4000),(5,5000), (1,null);
-- if()
select if(salary>2000,'high','low') from salary;
-- ifnull()
select ifnull(salary,0) from salary;
-- CASE WHEN
select case when salary<=2000 then 'low' else 'high' end from salary;
-- CASE [expr] WHEN
select case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end from salary;

5. 其他常用函数

函数 功能
DATABASE() 返回当前数据库名
VERSION() 返回当前数据库版本
USER() 返回当前登录用户名
INET_ATON(IP) 返回 IP 地址的数字表示
INET_NTOA(num) 返回数字代表的 IP 地址
PASSWORD(str) 返回字符串 str 的加密版本
MD5() 返回字符串 str 的 MD5 值

6. 小结

本章主要对 MySQL 常用的各类常用函数通过实例做了介绍。MySQL 有很多内建函数,这些
内建函数实现了很多应用需要的功能并且拥有很好的性能,如果用户在工作中需要实现某种
功能,最好先查一下 MySQL 官方文档或者帮助,看是否已经有相应的函数实现了我们需要
的功能,可以大大ᨀ高工作效率。由于篇幅所限,本章并没有介绍所有的函数,读者可以去
进一步查询相关文档