MySQL学习总结之路（第五章：函数）
目录
MySQL学习总结之路（第五章：函数）
1、MySQL中常用字符串函数
| 函数 | 功能 |
|
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
|
|
TRIM(str)
|
去掉字符串行尾和行头的空格
|
|
SUBSTRING(str,x,y)
|
返回从字符串 str x 位置起 y 个字符长度的字串
|
1：CANCAT(S1,S2,…Sn)函数，把传入的参数连接成为一个字符串。注意：和null拼接后直接返回NULL
mysql> select concat('aaa','bbb','d'),concat('dd',null); +-------------------------+-------------------+ | concat('aaa','bbb','d') | concat('dd',null) | +-------------------------+-------------------+ | aaabbbd | NULL | +-------------------------+-------------------+ 1 row in set (0.00 sec)
2：INSERT(str ,x,y,instr)函数：将字符串 str 从第 x 位置开始，y 个字符长的子串替换为字符串 instr。
将字符串“123456”中从第3个字符开始，后面的3个字符替换成“me”
mysql> select insert('123456',3,3,'me'); +---------------------------+ | insert('123456',3,3,'me') | +---------------------------+ | 12me6 | +---------------------------+ 1 row in set (0.02 sec)
3：LOWER(str)和 UPPER(str)函数：把字符串转换成小写或大写。
mysql> select lower("ZHANG"),upper("zhang"); +----------------+----------------+ | lower("ZHANG") | upper("zhang") | +----------------+----------------+ | zhang | ZHANG | +----------------+----------------+ 1 row in set (0.00 sec)
4：LEFT(str,x)和 RIGHT(str,x)函数：分别返回字符串最左边的 x 个字符和最右边的 x 个字符。注意:如果第二个参数是 NULL，那么将不返回任何字符串。
mysql> select left('zhang',2),left('zhang',null),right('zhang',2),left('zhang',10); +-----------------+--------------------+------------------+------------------+ | left('zhang',2) | left('zhang',null) | right('zhang',2) | left('zhang',10) | +-----------------+--------------------+------------------+------------------+ | zh | NULL | ng | zhang | +-----------------+--------------------+------------------+------------------+ 1 row in set (0.00 sec)
5:LPAD(str,n ,pad)和 RPAD(str,n ,pad)函数：用字符串 pad 对 str 最左边和最右边进行填充,直到长度为 n 个字符长度。
mysql> select lpad('bin',8,'zhang'),rpad('zhang',8,'bin'),rpad('beijing',20,'2008'); +-----------------------+-----------------------+---------------------------+ | lpad('bin',8,'zhang') | rpad('zhang',8,'bin') | rpad('beijing',20,'2008') | +-----------------------+-----------------------+---------------------------+ | zhangbin | zhangbin | beijing2008200820082 | +-----------------------+-----------------------+---------------------------+ 1 row in set (0.00 sec)
6:LTRIM(str)和 RTRIM(str)函数：去掉字符串 str 左侧和右侧空格。
mysql> select ' zhang',ltrim(' zhang'),rtrim('zhang '); +----------+-------------------+--------------------+ | zhang | ltrim(' zhang') | rtrim('zhang ') | +----------+-------------------+--------------------+ | zhang | zhang | zhang | +----------+-------------------+--------------------+ 1 row in set (0.00 sec)
7:REPEAT(str,x)函数：返回 str 重复 x 次的结果。
mysql> select repeat('mysql',5); +---------------------------+ | repeat('mysql',5) | +---------------------------+ | mysqlmysqlmysqlmysqlmysql | +---------------------------+ 1 row in set (0.00 sec)
8:REPLACE(str,a,b)函数：用字符串 b 替换字符串 str 中所有出现的字符串 a。
mysql> select replace('mysql','sql','ddd'); +------------------------------+ | replace('mysql','sql','ddd') | +------------------------------+ | myddd | +------------------------------+ 1 row in set (0.00 sec)
9:STRCMP(s1,s2)函数：比较字符串 s1 和 s2 的 ASCII 码值的大小。如果 s1 比 s2 小，那么返回-1；如果 s1 与 s2 相等，那么返回 0；如果 s1 比 s2 大，那么返回 1。
mysql> select strcmp('a','b'),strcmp('b','b'),strcmp('c','b'),strcmp('a','A'); +-----------------+-----------------+-----------------+-----------------+ | strcmp('a','b') | strcmp('b','b') | strcmp('c','b') | strcmp('a','A') | +-----------------+-----------------+-----------------+-----------------+ | -1 | 0 | 1 | 0 | +-----------------+-----------------+-----------------+-----------------+ 1 row in set (0.01 sec)
10:TRIM(str)函数：去掉目标字符串的开头和结尾的空格。
mysql> select trim(' 111 $ mysql $ '); +-----------------------------+ | trim(' 111 $ mysql $ ') | +-----------------------------+ | 111 $ mysql $ | +-----------------------------+ 1 row in set (0.01 sec)
11:SUBSTRING(str,x,y)函数：返回从字符串 str 中的第 x 位置起 y 个字符长度的字串。
mysql> select substring('mysqlisdd',4,4); +----------------------------+ | substring('mysqlisdd',4,4) | +----------------------------+ | qlis | +----------------------------+ 1 row in set (0.00 sec)
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： ABS(x)函数：返回 x 的绝对值。
mysql> select abs(-0.3),abs(0.3); +-----------+----------+ | abs(-0.3) | abs(0.3) | +-----------+----------+ | 0.3 | 0.3 | +-----------+----------+ 1 row in set (0.36 sec)
2：CEIL（x）函数，返回大于x的最大整数
mysql> select ceil(-0.2),ceil(0.2); +------------+-----------+ | ceil(-0.2) | ceil(0.2) | +------------+-----------+ | 0 | 1 | +------------+-----------+ 1 row in set (0.00 sec)
3：FLOOR（x）函数，返回小于x的最大整数，和CEIL刚好相反
mysql> select floor(-0.2),floor(0.2); +-------------+------------+ | floor(-0.2) | floor(0.2) | +-------------+------------+ | -1 | 0 | +-------------+------------+ 1 row in set (0.00 sec)
4：MOD(x，y)函数：返回 x/y 的模。和 x%y 的结果相同，模数和被模数任何一个为 NULL 结果都为 NULL。如下例所示：
mysql> select mod(15,10),mod(1,11),mod(null,10); +------------+-----------+--------------+ | mod(15,10) | mod(1,11) | mod(null,10) | +------------+-----------+--------------+ | 5 | 1 | NULL | +------------+-----------+--------------+ 1 row in set (0.00 sec)
5：RAND（）函数，返回0到1之间的随机数
mysql> select rand(),rand(); +-------------------+---------------------+ | rand() | rand() | +-------------------+---------------------+ | 0.541937319135235 | 0.10546984067696132 | +-------------------+---------------------+ 1 row in set (0.00 sec)
6：ROUND(x,y)函数，返回参数 x 的四舍五入的有 y 位小数的值。如果是整数，将会保留 y 位数量的 0；如果不写 y，则默认 y 为 0，即将 x 四舍五入后取整。
mysql> select round(1.2,2),round(1.3),round(1,2); +--------------+------------+------------+ | round(1.2,2) | round(1.3) | round(1,2) | +--------------+------------+------------+ | 1.20 | 1 | 1 | +--------------+------------+------------+ 1 row in set (0.00 sec)
7：TRUNCATE(x,y)函数：返回数字 x 截断为 y 位小数的结果。（注意 TRUNCATE 和 ROUND 的区别在于 TRUNCATE 仅仅是截断，而不进行四舍五入。）
mysql> select round(1.235,2),truncate(1.235,2); +----------------+-------------------+ | round(1.235,2) | truncate(1.235,2) | +----------------+-------------------+ | 1.24 | 1.23 | +----------------+-------------------+ 1 row in set (0.31 sec)
3、日期和时间函数
MySQL 中的常用日期时间函数
| 函数 | 功能 |
| CURDATE() | 返回当前日期 |
| CURTIME() | 返回当前时间 |
| NOW() | 返回当前的日期和时间 |
| UNIX_TIMESTAMP(date) | 返回日期 date 的 UNIX 时间戳 |
| FROM_UNIXTIME | 返回 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 之间的天数 |
1：CURDATE()函数：返回当前日期，只包含年月日
mysql> select curdate(); +------------+ | curdate() | +------------+ | 2021-05-25 | +------------+ 1 row in set (0.00 sec)
2：CURTIME()函数：返回当前时间，只包含时分秒
mysql> select curtime(); +-----------+ | curtime() | +-----------+ | 14:07:06 | +-----------+ 1 row in set (0.00 sec)
3：NOW()函数：返回当前的日期和时间，年月日时分秒全都包含。
mysql> select now(); +---------------------+ | now() | +---------------------+ | 2021-05-25 14:07:33 | +---------------------+ 1 row in set (0.00 sec)
4：UNIX_TIMESTAMP(date)函数：返回日期 date 的 UNIX 时间戳。
mysql> select unix_timestamp(now()); +-----------------------+ | unix_timestamp(now()) | +-----------------------+ | 1621922906 | +-----------------------+ 1 row in set (0.30 sec)
5：FROM_UNIXTIME （ unixtime ） 函 数 ： 返 回 UNIXTIME 时 间 戳 的 日 期 值 ， 和UNIX_TIMESTAMP(date)互为逆操作。
mysql> select from_unixtime(1621922906); +---------------------------+ | from_unixtime(1621922906) | +---------------------------+ | 2021-05-25 14:08:26 | +---------------------------+ 1 row in set (0.31 sec)
6：WEEK(DATE)和 YEAR(DATE)函数：前者返回所给的日期是一年中的第几周，后者返回所给的日期是哪一年。
mysql> select week(now()),year(now()); +-------------+-------------+ | week(now()) | year(now()) | +-------------+-------------+ | 21 | 2021 | +-------------+-------------+ 1 row in set (0.00 sec)
7：HOUR(time)和 MINUTE(time)函数：前者返回所给时间的小时，后者返回所给时间的分钟。
mysql> select hour(now()),minute(now()); +-------------+---------------+ | hour(now()) | minute(now()) | +-------------+---------------+ | 14 | 11 | +-------------+---------------+ 1 row in set (0.00 sec)
8：MONTHNAME(date)函数：返回 date 的英文月份名称。
mysql> select monthname(now()); +------------------+ | monthname(now()) | +------------------+ | May | +------------------+ 1 row in set (0.30 sec)
9：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(),'%M,%D,%Y'); +-------------------------------+ | date_format(now(),'%M,%D,%Y') | +-------------------------------+ | May,25th,2021 | +-------------------------------+ 1 row in set (0.00 sec)
10：DATE_ADD(date,INTERVAL expr type)函数：返回与所给日期 date 相差 INTERVAL 时间段的日期。
其中 INTERVAL 是间隔类型关键字，expr 是一个表达式，这个表达式对应后面的类型，type
是间隔类型，MySQL 提供了 13 种间隔类型，如下表
MySQL 中的日期间隔类型
| 表达式类型 | 描述 | 格式 |
| 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 列返回距离当前日期31 天后的日期时间，第 3 列返回距离当前日期一年两个月后的日期时间。
mysql> select now() current,date_add(now(),interval 31 day) after31days,date_add(now(),interval '1_2' year_month) after_oneyear_twomonth; +---------------------+---------------------+------------------------+ | current | after31days | after_oneyear_twomonth | +---------------------+---------------------+------------------------+ | 2021-05-25 14:32:30 | 2021-06-25 14:32:30 | 2022-07-25 14:32:30 | +---------------------+---------------------+------------------------+ 1 row in set (0.03 sec)
同样也可以用负数让它返回之前的某个日期时间，如下第 1 列返回了当前日期时间，第 2列返回距离当前日期 31 天前的日期时间，第 3 列返回距离当前日期一年两个月前的日期时间。
mysql> select now() current,date_add(now(),interval -31 day) bef31days,date_add(now(),interval '-1_-2' year_month) bef_oneyear_twomonth; +---------------------+---------------------+----------------------+ | current | bef31days | bef_oneyear_twomonth | +---------------------+---------------------+----------------------+ | 2021-05-25 14:34:38 | 2021-04-24 14:34:38 | 2020-03-25 14:34:38 | +---------------------+---------------------+----------------------+ 1 row in set (0.00 sec)
11：DATEDIFF（date1，date2）函数：用来计算两个日期之间相差的天数
mysql> select datediff('2013-09-01',now()); +------------------------------+ | datediff('2013-09-01',now()) | +------------------------------+ | -2823 | +------------------------------+ 1 row in set (0.30 sec)
4、流程函数
MySQL中的流程函数
| 函数 | 功能 |
| IF(value,t f) | 如果 value 是真，返回 t；否则返回 f |
| IFNULL(value1,value2) | 如果 value1 不为空返回 value1，否则返回 value2 |
|
CASE WHEN [value1] |
如果 value1 是真，返回 result1，否则返回 default |
|
CASE [expr] WHEN [value1] |
如果 expr 等于 value1，返回 result1，否则返回 default |
1：IF(value,t f)，例子：创建并初始化一个职员薪水表，插入一些测试数据，我们认为月薪在 2000 元以上的职员属于高薪，用“high”表示；而2000 元以下的职员属于低薪，用“low”表示
mysql> create table salary(userid int,salary decimal(9,2)); Query OK, 0 rows affected (0.47 sec) mysql> insert into salary values(1,1000),(2,2000),(3,3000),(4,4000),(5,5000),(1,null); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> select * from salary; +--------+---------+ | userid | salary | +--------+---------+ | 1 | 1000.00 | | 2 | 2000.00 | | 3 | 3000.00 | | 4 | 4000.00 | | 5 | 5000.00 | | 1 | NULL | +--------+---------+ 6 rows in set (0.00 sec) mysql> select if(salary>2000,'high','low') from salary; +------------------------------+ | if(salary>2000,'high','low') | +------------------------------+ | low | | low | | high | | high | | high | | low | +------------------------------+ 6 rows in set (0.31 sec)
2：IFNULL(value1,value2)函数，一般用来替换 NULL 值的，我们知道 NULL 值是不能参与数值运算的，下面这个语句就是把 NULL 值用 0 来替换
mysql> select ifnull(salary,0) from salary; +------------------+ | ifnull(salary,0) | +------------------+ | 1000.00 | | 2000.00 | | 3000.00 | | 4000.00 | | 5000.00 | | 0.00 | +------------------+ 6 rows in set (0.00 sec)
3:CASE WHEN [value1] THEN[result1]…ELSE[default]END 函 数 ： 我 们 也 可 以 用 case when…then 函数实现上面例子中高薪低薪的问题
mysql> select case when salary<=2000 then 'low' else 'high' end from salary; +---------------------------------------------------+ | case when salary<=2000 then 'low' else 'high' end | +---------------------------------------------------+ | low | | low | | high | | high | | high | | high | +---------------------------------------------------+ 6 rows in set (0.29 sec)
4:CASE [expr] WHEN [value1] THEN[result1]…ELSE[default]END 函数：这里还可以分多种情况把职员的薪水分多个档次，比如下面的例子分成高、中、低 3 种情况。
mysql> select case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end from salary; +-----------------------------------------------------------------------+ | case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end | +-----------------------------------------------------------------------+ | low | | mid | | high | | high | | high | | high | +-----------------------------------------------------------------------+ 6 rows in set (0.00 sec)
5、其他常用函数
MySQL 中的其他常用函数
| 函数 | 功能 |
| DATABASE() | 返回当前数据库名 |
| VERSION() | 返回当前数据库版本 |
| USER() | 返回当前登录用户名 |
| INET_ATON(IP) | 返回 IP 地址的数字表示 |
| INET_NTOA(num) | 返回数字代表的 IP 地址 |
| PASSWORD(str) | 返回字符串 str 的加密版本 |
| MD5() | 返回字符串 str 的 MD5 值 |
1：DATABASE()函数：返回当前数据库名。
mysql> select database(); +------------+ | database() | +------------+ | test | +------------+ 1 row in set (0.00 sec)
2：VERSION()函数：返回当前数据库版本。
mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.26 | +-----------+ 1 row in set (0.00 sec)
3：USER()函数：返回当前登录用户名。
mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec)
4：INET_ATON(IP)函数：返回 IP 地址的网络字节序表示。
mysql> select inet_aton('192.168.1.1'); +--------------------------+ | inet_aton('192.168.1.1') | +--------------------------+ | 3232235777 | +--------------------------+ 1 row in set (0.30 sec)
5：INET_NTOA(num)函数：返回网络字节序代表的 IP 地址。
mysql> select inet_ntoa('3232235777'); +-------------------------+ | inet_ntoa('3232235777') | +-------------------------+ | 192.168.1.1 | +-------------------------+ 1 row in set (0.00 sec)
6：PASSWORD(str)函数：返回字符串 str 的加密版本，一个 41 位长的字符串。
mysql> select password('1223456'); +-------------------------------------------+ | password('1223456') | +-------------------------------------------+ | *3B5C2394E86BB91F1D03C5A1F2D3962BB287590B | +-------------------------------------------+ 1 row in set, 1 warning (0.33 sec)
7：MD5(str)函数：返回字符串 str 的 MD5 值，常用来对应用中的数据进行加密。
mysql> select md5('123456'); +----------------------------------+ | md5('123456') | +----------------------------------+ | e10adc3949ba59abbe56e057f20f883e | +----------------------------------+ 1 row in set (0.30 sec)
本章完结！！！
