mysql中的函数

常用函数

字符串函数

• CANCAT(S1,S2,…Sn)函数:把传入的参数连接成为一个字符串。
  下面的例子把“aaa”、“bbb”、“ccc”3 个字符串连接成了一个字符串“aaabbbccc”。另外,
  任何字符串与 NULL 进行连接的结果都将是 NULL。

mysql> select concat('aaa','bbb','ccc') ,concat('aaa',null);
| concat('aaa','bbb','ccc') | concat('aaa',null) |
+---------------------------+--------------------+
| aaabbbccc
| NULL
|
+---------------------------+--------------------+
1 row in set (0.05 sec)

• INSERT(str ,x,y,instr)函数:将字符串 str 从第 x 位置开始,y 个字符长的子串替换为字符
  串 instr。
  下面的例子把字符串“beijing2018you”中的从第 12 个字符开始以后的 3 个字符替换成
  “me”。

mysql> select INSERT('beijing2018you',12,3, 'me') ;
+-------------------------------------+
| INSERT('beijing2018you',12,3, 'me') |
+-------------------------------------+
| beijing2018me
|
+-------------------------------------+
1 row in set (0.00 sec)

• LOWER(str)和 UPPER(str)函数:把字符串转换成小写或大写。
  在字符串比较中,通常要将比较的字符串全部转换为大写或者小写,如下例所示:

mysql> select lower('BEIJING2018'),UPPER('beijing2018');
+----------------------+----------------------+
| lower('BEIJING2018') | UPPER('beijing2018') |
+----------------------+----------------------+
| beijing2018          | BEIJING2018          |
+----------------------+----------------------+
1 row in set (0.00 sec)

• LEFT(str,x)和 RIGHT(str,x)函数:分别返回字符串最左边的 x 个字符和最右边的 x 个字符。
  如果第二个参数是 NULL,那么将不返回任何字符串。

mysql> select left('beijing2018',7),left('beijing2018',null),right('beijing2018',4);
+-----------------------+--------------------------+------------------------+
| left('beijing2018',7) | left('beijing2018',null) | right('beijing2018',4) |
+-----------------------+--------------------------+------------------------+
| beijing               | NULL                     | 2018                   |
+-----------------------+--------------------------+------------------------+
1 row in set (0.00 sec)

• LPAD(str,n ,pad)和 RPAD(str,n ,pad)函数:用字符串 pad 对 str 最左边和最右边进行填充,
  直到长度为 n 个字符长度。
  下例中显示了对字符串“2018”和“beijing”分别填充后的结果。

mysql> select lpad('2018',20,'beijing'),rpad('beijing',20,'2018');
+---------------------------+---------------------------+
| lpad('2018',20,'beijing') | rpad('beijing',20,'2018') |
+---------------------------+---------------------------+
| beijingbeijingbe2018      | beijing2018201820182      |
+---------------------------+---------------------------+
1 row in set (0.00 sec)

• LTRIM(str)和 RTRIM(str)函数:去掉字符串 str 左侧和右侧空格。
  下例中显示了字符串“beijing”加空格进行过滤后的结果。

mysql> select ltrim('  |beijing'),rtrim('beijing|  ');
+---------------------+---------------------+
| ltrim('  |beijing') | rtrim('beijing|  ') |
+---------------------+---------------------+
| |beijing            | beijing|            |
+---------------------+---------------------+
1 row in set (0.00 sec)

• REPEAT(str,x)函数:返回 str 重复 x 次的结果。
  下例中对字符串“mysql”重复显示了 3 次。

mysql> select repeat('mysql',3);
+-------------------+
| repeat('mysql',3) |
+-------------------+
| mysqlmysqlmysql   |
+-------------------+
1 row in set (0.00 sec)

• REPLACE(str,a,b)函数:用字符串 b 替换字符串 str 中所有出现的字符串 a。
  下例中用字符串“2018”代替了字符串“beijing_2008”中的“_2008”。

mysql> select replace('beijing_2008','_2008','2018');
+----------------------------------------+
| replace('beijing_2008','_2008','2018') |
+----------------------------------------+
| beijing2018                            |
+----------------------------------------+
1 row in set (0.00 sec)

• 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','b') | strcmp('b','b') | strcmp('c','b') |
+-----------------+-----------------+-----------------+
|              -1 |               0 |               1 |
+-----------------+-----------------+-----------------+
1 row in set (0.02 sec)

• TRIM(str)函数:去掉目标字符串的开头和结尾的空格。
  下例中对字符串“$ beijing2008 $ ”进行了前后空格的过滤。

mysql> select trim(' $ beijing $  ');
+------------------------+
| trim(' $ beijing $  ') |
+------------------------+
| $ beijing $            |
+------------------------+
1 row in set (0.00 sec)

• SUBSTRING(str,x,y)函数:返回从字符串 str 中的第 x 位置起 y 个字符长度的字串。
  此函数经常用来对给定字符串进行字串的提取,如下例所示。

mysql> select substring('beijing2018',8,4),substring('beijing2018',1,7);
+------------------------------+------------------------------+
| substring('beijing2018',8,4) | substring('beijing2018',1,7) |
+------------------------------+------------------------------+
| 2018                         | beijing                      |
+------------------------------+------------------------------+
1 row in set (0.00 sec)

数值函数

• ABS(x)函数:返回 x 的绝对值。
  下例中显示了对正数和负数分别取绝对值之后的结果。

mysql> select abs(-0.8);
+-----------+
| abs(-0.8) |
+-----------+
|       0.8 |
+-----------+
1 row in set (0.01 sec)

• CEIL(x)函数:返回大于 x 的最大整数。
  下例中显示了对 0.8 和-0.8 分别 CEIL 后的结果。

mysql> select ceil(-0.8),ceil(0.8);
+------------+-----------+
| ceil(-0.8) | ceil(0.8) |
+------------+-----------+
|          0 |         1 |
+------------+-----------+
1 row in set (0.00 sec)

• FLOOR(x)函数:返回小于 x 的最大整数,和 CEIL 的用法刚好相反。
  下例中显示了对 0.8 和-0.8 分别 FLOOR 后的结果。

mysql> select floor(-0.8),floor(0.8);
+-------------+------------+
| floor(-0.8) | floor(0.8) |
+-------------+------------+
|          -1 |          0 |
+-------------+------------+
1 row in set (0.00 sec)

• MOD(x,y)函数:返回 x/y 的模。
  和 x%y 的结果相同,模数和被模数任何一个为 NULL 结果都为 NULL。如下例所示:

mysql> select mod(15,10),mod(null,10);
+------------+--------------+
| mod(15,10) | mod(null,10) |
+------------+--------------+
|          5 |         NULL |
+------------+--------------+
1 row in set (0.00 sec)

• RAND()函数:返回 0 到 1 内的随机值。
  每次执行结果都不一样,如下例所示:

mysql> select rand(),rand();
+---------------------+--------------------+
| rand()              | rand()             |
+---------------------+--------------------+
| 0.23571444790411222 | 0.5954422015654316 |
+---------------------+--------------------+
1 row in set (0.00 sec)

利用此函数可以取任意指定范围内的随机数,比如需要产生 0~100 内的任意随机整数,可
以操作如下:

mysql> select ceil(100*rand()),ceil(100*rand());
+------------------+------------------+
| ceil(100*rand()) | ceil(100*rand()) |
+------------------+------------------+
|               28 |               57 |
+------------------+------------------+
1 row in set (0.01 sec)

• ROUND(x,y)函数:返回参数 x 的四舍五入的有 y 位小数的值。
  如果是整数,将会保留 y 位数量的 0;如果不写 y,则默认 y 为 0,即将 x 四舍五入后取整。
  适合于将所有数字保留同样小数位的情况。如下例所示。

mysql> select round(1,1),round(1.1,2),round(1,2);
+------------+--------------+------------+
| round(1,1) | round(1.1,2) | round(1,2) |
+------------+--------------+------------+
|          1 |         1.10 |          1 |
+------------+--------------+------------+
1 row in set (0.00 sec)

• 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.00 sec)

日期和时间函数

• CURDATE()函数:返回当前日期,只包含年月日。

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2018-10-14 |
+------------+
1 row in set (0.15 sec)

• CURTIME()函数:返回当前时间,只包含时分秒。

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 15:22:53  |
+-----------+
1 row in set (0.02 sec)

• NOW()函数:返回当前的日期和时间,年月日时分秒全都包含。

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2018-10-14 15:24:06 |
+---------------------+
1 row in set (0.00 sec)

• UNIX_TIMESTAMP(date)函数:返回日期 date 的 UNIX 时间戳。

mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
|       1539501927 |
+------------------+
1 row in set (0.00 sec)

• FROM_UNIXTIME ( unixtime ) 函 数 : 返 回 UNIXTIME 时 间 戳 的 日 期 值 , 和
  UNIX_TIMESTAMP(date)互为逆操作。

mysql> select from_unixtime(1539501927);
+---------------------------+
| from_unixtime(1539501927) |
+---------------------------+
| 2018-10-14 15:25:27       |
+---------------------------+
1 row in set (0.00 sec)

• WEEK(DATE)和 YEAR(DATE)函数:前者返回所给的日期是一年中的第几周,后者返回所
  给的日期是哪一年。

mysql> select week(now()),year(now());
+-------------+-------------+
| week(now()) | year(now()) |
+-------------+-------------+
|          41 |        2018 |
+-------------+-------------+
1 row in set (0.00 sec)

• HOUR(time)和 MINUTE(time)函数:前者返回所给时间的小时,后者返回所给时间的分钟。

mysql> select hour(curtime()),minute(curtime());
+-----------------+-------------------+
| hour(curtime()) | minute(curtime()) |
+-----------------+-------------------+
|              15 |                30 |
+-----------------+-------------------+
1 row in set (0.00 sec)

• MONTHNAME(date)函数:返回 date 的英文月份名称。

mysql> select monthname(now());
+------------------+
| monthname(now()) |
+------------------+
| October          |
+------------------+
1 row in set (0.00 sec)

• DATEDIFF(date1,date2)函数:用来计算两个日期之间相差的天数。
  下面的例子计算出当前距离2019年还有多少天:

mysql> select datediff('2019-01-01',now());
+------------------------------+
| datediff('2019-01-01',now()) |
+------------------------------+
|                           79 |
+------------------------------+
1 row in set (0.00 sec)

流程函数

下面的例子中模拟了对职员薪水进行分类,这里首先创建并初始化一个职员薪水表:

mysql> create table salary(userid int,salary decimal(9,2));
Query OK, 0 rows affected (0.34 sec)

插入一些测试数据:

mysql> insert into salary values(1,1000),(2,2000),(3,3000),(4,4000),(5,5000),(1,null);
Query OK, 6 rows affected (0.06 sec)
Records: 6  Duplicates: 0  Warnings: 0

接下来,通过这个表来介绍各个函数的应用。

• IF(value,t,f)函数:我们认为月薪在 2000 元以上的职员属于高薪,用“high”表示;而
  2000 元以下的职员属于低薪,用“low”表示。

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.00 sec)

• 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)

• 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.00 sec)

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)

其他常用函数

• DATABASE()函数:返回当前数据库名。

mysql> select database();
+------------+
| database() |
+------------+
| hyp01      |
+------------+
1 row in set (0.00 sec)

• VERSION()函数:返回当前数据库版本。

mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.7.23-0ubuntu0.18.04.1 |
+-------------------------+
1 row in set (0.01 sec)

• USER()函数:返回当前登录用户名。

mysql> select user();
+---------------+
| user()        |
+---------------+
| hyp@localhost |
+---------------+
1 row in set (0.00 sec)

• MD5(str)函数:返回字符串 str 的 MD5 值,常用来对应用中的数据进行加密。

mysql> select md5('123345');
+----------------------------------+
| md5('123345')                    |
+----------------------------------+
| 42dae262b8531b3df48cde9cc018c512 |
+----------------------------------+
1 row in set (0.00 sec)