MySql常用函数

MySql

1.字符函数

1.1 CONCAT 拼接字符

1
2
3
4
5
6
select concat("hello","world");
+-------------------------+
| concat("hello","world") |
+-------------------------+
| helloworld              |
+-------------------------+

1.2 LENGTH 获取字节长度

1
2
3
4
5
6
select length("hello world");
+-----------------------+
| length("hello world") |
+-----------------------+
|                    11 |
+-----------------------+
1
2
3
4
5
6
select length("尚文杰");
+---------------------+
| length("尚文杰")    |
+---------------------+
|                   9 |
+---------------------+

1.3 CHAR_LENGTH 获取字符长度

1
2
3
4
5
6
select char_length("尚文杰");
+--------------------------+
| char_length("尚文杰")    |
+--------------------------+
|                        3 |
+--------------------------+

1.4 SUBSTRING 截取字符串

1
2
3
4
5
6
select substr("尚文杰",1,2);
+-------------------------+
| substr("尚文杰",1,2)    |
+-------------------------+
| 尚文                    |
+-------------------------+
1
2
3
4
5
6
select substr("尚文杰",2);
+-----------------------+
| substr("尚文杰",2)    |
+-----------------------+
| 文杰                  |
+-----------------------+

1.5 INSTR 字符第一次出现的位置

1
2
3
4
5
6
select instr("尚文杰","尚");
+--------------------------+
| instr("尚文杰","尚")     |
+--------------------------+
|                        1 |
+--------------------------+

1.6 TRIM 去前后字符（默认去空格）

1
2
3
4
5
6
select trim(" hello world ");
+-----------------------+
| trim(" hello world ") |
+-----------------------+
| hello world           |
+-----------------------+
1
2
3
4
5
6
select trim("x" from "xxhello worldxx");
+----------------------------------+
| trim("x" from "xxhello worldxx") |
+----------------------------------+
| hello world                      |
+----------------------------------+

1.7 LPAD/RPAD 左填充/右填充

1
2
3
4
5
6
select lpad("hello",10,"x");
+----------------------+
| lpad("hello",10,"x") |
+----------------------+
| xxxxxhello           |
+----------------------+
1
2
3
4
5
6
select rpad("hello",10,"x");
+----------------------+
| rpad("hello",10,"x") |
+----------------------+
| helloxxxxx           |
+----------------------+

1.8 UPPER/LOWER 变大写/变小写

1
2
3
4
5
6
select upper("hello");
+----------------+
| upper("hello") |
+----------------+
| HELLO          |
+----------------+
1
2
3
4
5
6
select lower("HELLO");
+----------------+
| lower("HELLO") |
+----------------+
| hello          |
+----------------+

1.9 STRCMP 比较

1
2
3
4
5
6
7
8
9
10
11
12
select strcmp("aa","bb");
+-------------------+
| strcmp("aa","bb") |
+-------------------+
|                -1 |
+-------------------+
select strcmp("bb","aa");
+-------------------+
| strcmp("bb","aa") |
+-------------------+
|                 1 |
+-------------------+

1.9 LEFT/RIGHT 左截/右截

1
2
3
4
5
6
select left("ab",1);
+--------------+
| left("ab",1) |
+--------------+
| a            |
+--------------+
1
2
3
4
5
6
select right("ab",1);
+---------------+
| right("ab",1) |
+---------------+
| b             |
+---------------+

2.数学函数

2.1 ABS 绝对值

1
2
3
4
5
6
select abs(-1);
+---------+
| abs(-1) |
+---------+
|       1 |
+---------+

2.2 CEIL 向上取整 返回>=该整数的最小整数

1
2
3
4
5
6
select ceil(1.09);
+------------+
| ceil(1.09) |
+------------+
|          2 |
+------------+

2.3 FLOOR 向下取整 返回<=该整数的最大整数

1
2
3
4
5
6
select floor(1.09);
+-------------+
| floor(1.09) |
+-------------+
|           1 |
+-------------+

2.4 ROUND 四舍五入

1
2
3
4
5
6
select round(1.09);
+-------------+
| round(1.09) |
+-------------+
|           1 |
+-------------+
1
2
3
4
5
6
select round(1.8989,2);
+-----------------+
| round(1.8989,2) |
+-----------------+
|            1.90 |
+-----------------+

2.5 TRUNCATE 截断

1
2
3
4
5
6
7
8
9
10
11
12
select truncate(1.8989,1);
+--------------------+
| truncate(1.8989,1) |
+--------------------+
|                1.8 |
+--------------------+
select truncate(1.8989,2);
+--------------------+
| truncate(1.8989,2) |
+--------------------+
|               1.89 |
+--------------------+

2.6 MOD 取余

1
2
3
4
5
6
select mod(10,2);
+-----------+
| mod(10,2) |
+-----------+
|         0 |
+-----------+

3.日期函数

3.1 NOW

1
2
3
4
5
6
select now();
+---------------------+
| now()               |
+---------------------+
| 2020-04-05 22:36:09 |
+---------------------+

3.2 CURDATE 只取日期

1
2
3
4
5
6
select curdate();
+------------+
| curdate()  |
+------------+
| 2020-04-05 |
+------------+

3.3 CURTIME 只取时间

1
2
3
4
5
6
select curtime();
+-----------+
| curtime() |
+-----------+
| 22:38:06  |
+-----------+

3.4 DATEDIFF 取时间差

1
2
3
4
5
6
select datediff('2020-04-08','2020-05-09');
+-------------------------------------+
| datediff('2020-04-08','2020-05-09') |
+-------------------------------------+
|                                 -31 |
+-------------------------------------+

3.5 DATE_FORMAT 格式化日期

1
2
3
4
5
6
select date_format(now(),'%Y年%m月%d日 %H时%m分%s秒');
+------------------------------------------------------+
| date_format(now(),'%Y年%m月%d日 %H时%m分%s秒')       |
+------------------------------------------------------+
| 2020年04月05日 22时04分10秒                          |
+------------------------------------------------------+

3.6 STR_TO_DATE 按指定格式解析字符串为日期

1
2
3
4
5
6
select str_to_date('05/04 2020','%m/%d %Y');
+--------------------------------------+
| str_to_date('05/04 2020','%m/%d %Y') |
+--------------------------------------+
| 2020-05-04                           |
+--------------------------------------+

4.流程控制函数

4.1 IF

1
2
3
4
5
6
select if(100>1,'yes','no');
+----------------------+
| if(100>1,'yes','no') |
+----------------------+
| yes                  |
+----------------------+

4.2 CASE

1
2
3
4
5
6
select case when 100>102 then 'yes' when 100>101 then "100>101" else "no" end;
+------------------------------------------------------------------------+
| case when 100>102 then 'yes' when 100>101 then "100>101" else "no" end |
+------------------------------------------------------------------------+
| no                                                                     |
+------------------------------------------------------------------------+