当前位置:Gxlcms > 数据库问题 > MySQL学习总结之路(第五章:函数)

MySQL学习总结之路(第五章:函数)

时间:2021-07-01 10:21:17 帮助过:19人阅读

MySQL学习总结之路(第二章:表)

MySQL学习总结之路(第三章:数据类型)

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]
THEN[result1]…ELSE[default]END

如果 value1 是真,返回 result1,否则返回 default

CASE [expr] WHEN [value1]
THEN[result1]…ELSE[default]END

如果 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学习总结之路(第五章:函数)

标签:位长   距离   set   orm   warnings   floor   zhang   date()   负数   

人气教程排行