深入浅出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 位小数的结果 |
-- 取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 |
两位数字表示的年份 |
| %% |
直接值“%” |
一般格式:
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 |
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 |
-- 建表
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 官方文档或者帮助，看是否已经有相应的函数实现了我们需要
的功能，可以大大ᨀ高工作效率。由于篇幅所限，本章并没有介绍所有的函数，读者可以去
进一步查询相关文档
