目录
- 1、mysql中常用字符串函数
- 2、数值函数
- 3、日期和时间函数
- 4、流程函数
- 5、其他常用函数
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)
到此这篇关于mysql函数全面总结的文章就介绍到这了,更多相关mysql函数内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！