mysql函数

mysql函数

1.字符串函数

（1）concat 字符串拼接

mysql> select concat('hello','mysql');
+-------------------------+
| concat('hello','mysql') |
+-------------------------+
| hellomysql              |
+-------------------------+
1 row in set (0.00 sec)

（2）lower(str) 将字符串全部转换为小写

mysql> select lower('HelloWorld');
+---------------------+
| lower('HelloWorld') |
+---------------------+
| helloworld          |
+---------------------+
1 row in set (0.00 sec)

（3）upper(str) 将字符串全部转换为大写

mysql> select upper('HelloWorld');
+---------------------+
| upper('HelloWorld') |
+---------------------+
| HELLOWORLD          |
+---------------------+
1 row in set (0.00 sec)

（4）lpad(str,n,pad) 左填充，用字符串pad对str左边进行填充达到n个字符

mysql> select lpad('nihao',8,'0');
+---------------------+
| lpad('nihao',8,'0') |
+---------------------+
| 000nihao            |
+---------------------+
1 row in set (0.00 sec)

（5）rpad(str,n,pad) 右填充，用字符串pad对str右边进行填充达到8个字符

mysql> select rpad('nihao',8,'0');
+---------------------+
| rpad('nihao',8,'0') |
+---------------------+
| nihao000            |
+---------------------+
1 row in set (0.00 sec)

（6）trim(str) 去掉字符串头部和尾部的空格

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

（7）substring(str,start,len)

mysql> select substring('hello',1,3);
+------------------------+
| substring('hello',1,3) |
+------------------------+
| hel                    |
+------------------------+
1 row in set (0.00 sec)

2.数值函数

（1）ceil:向上取整

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

（2）floor:向下取整

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

（3）mod:取模

mysql> select mod(7,4);
+----------+
| mod(7,4) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

（4）rand:获取随机数

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

（5）round:四舍五入

mysql> select round(3.45,1);
+---------------+
| round(3.45,1) |
+---------------+
|           3.5 |
+---------------+
1 row in set (0.00 sec)

3.日期函数

（1）curdate:返回当前日期

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2022-12-24 |
+------------+
1 row in set (0.00 sec)

（2）curtime:返回当前的时间

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 18:29:06  |
+-----------+
1 row in set (0.00 sec)

（3）now():返回当前日期和时间

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2022-12-24 18:29:23 |
+---------------------+
1 row in set (0.00 sec)

（4）year():返回指定date的年份

mysql> select year('2020-03-11');
+--------------------+
| year('2020-03-11') |
+--------------------+
|               2020 |
+--------------------+
1 row in set (0.00 sec)

（5）month():返回指定date的月份

mysql> select month('2020-06-19');
+---------------------+
| month('2020-06-19') |
+---------------------+
|                   6 |
+---------------------+
1 row in set (0.00 sec)

（6）day():获取指定date的日期

mysql> select day('2020-06-15');
+-------------------+
| day('2020-06-15') |
+-------------------+
|                15 |
+-------------------+
1 row in set (0.00 sec)

（7）date_add(date,interval,expr type )

mysql> select date_add(now(),interval 70 day);
+---------------------------------+
| date_add(now(),interval 70 day) |
+---------------------------------+
| 2023-03-04 18:30:24             |
+---------------------------------+
1 row in set (0.00 sec)

（8）datediff(date1,date2):获取天数差值

mysql> select datediff('2022-12-23','2022-06-19');
+-------------------------------------+
| datediff('2022-12-23','2022-06-19') |
+-------------------------------------+
|                                 187 |
+-------------------------------------+
1 row in set (0.00 sec)

4.流程函数

（1）if(value,t,f):如果value为true,则返回t,否则返回f

mysql> select if(true,'ok','error');
+-----------------------+
| if(true,'ok','error') |
+-----------------------+
| ok                    |
+-----------------------+
1 row in set (0.00 sec)

mysql> select if(false,'ok','error');
+------------------------+
| if(false,'ok','error') |
+------------------------+
| error                  |
+------------------------+
1 row in set (0.00 sec)

（2）ifnull(value1,value2) 如果value1不为空，则返回value1,否则返回value2

mysql> select ifnull(null,'default');
+------------------------+
| ifnull(null,'default') |
+------------------------+
| default                |
+------------------------+
1 row in set (0.00 sec)

mysql> select ifnull('haha','default');
+--------------------------+
| ifnull('haha','default') |
+--------------------------+
| haha                     |
+--------------------------+
1 row in set (0.00 sec)

（3）case when … then … when … then… else … end

mysql> select * from emp;
+------+--------+-----------+--------+------+--------------------+-------------+------------+
| id   | workno | name      | gender | age  | idcard             | workaddress | entrydate  |
+------+--------+-----------+--------+------+--------------------+-------------+------------+
|    1 | 00001  | 柳岩666   | 女     |   20 | 123456789012345678 | 北京        | 2000-01-01 |
|    2 | 00002  | 张无忌    | 男     |   18 | 123456789012345670 | 北京        | 2005-09-01 |
|    3 | 00003  | 韦一笑    | 男     |   38 | 123456789712345670 | 上海        | 2005-08-01 |
|    4 | 00004  | 赵敏      | 女     |   18 | 123456757123845670 | 北京        | 2009-12-01 |
|    5 | 00005  | 小昭      | 女     |   16 | 123456769012345678 | 上海        | 2007-07-01 |
|    6 | 00006  | 杨逍      | 男     |   28 | 12345678931234567X | 北京        | 2006-01-01 |
|    7 | 00007  | 范瑶      | 男     |   40 | 123456789212345670 | 北京        | 2005-05-01 |
|    8 | 00008  | 黛绮丝    | 女     |   38 | 123456157123645670 | 天津        | 2015-05-01 |
|    9 | 00009  | 范凉凉    | 女     |   45 | 123156789012345678 | 北京        | 2010-04-01 |
|   10 | 00010  | 陈友谅    | 男     |   53 | 123456789012345670 | 上海        | 2011-01-01 |
|   11 | 00011  | 张士诚    | 男     |   55 | 123567897123465670 | 江苏        | 2015-05-01 |
|   12 | 00012  | 常遇春    | 男     |   32 | 123446757152345670 | 北京        | 2004-02-01 |
|   13 | 00013  | 张三丰    | 男     |   88 | 123656789012345678 | 江苏        | 2020-11-01 |
|   14 | 00014  | 灭绝      | 女     |   65 | 123456719012345670 | 西安        | 2019-05-01 |
|   15 | 00015  | 胡青牛    | 男     |   70 | 12345674971234567X | 西安        | 2018-04-01 |
|   16 | 00016  | 周芷若    | 女     |   18 | NULL               | 北京        | 2012-06-01 |
+------+--------+-----------+--------+------+--------------------+-------------+------------+
16 rows in set (0.00 sec)

## 实现北京，上海，为一线城市，其他为二线城市

mysql> select
    ->     name,
    ->     (case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址'
    -> from emp;
+-----------+--------------+
| name      | 工作地址     |
+-----------+--------------+
| 柳岩666   | 一线城市     |
| 张无忌    | 一线城市     |
| 韦一笑    | 一线城市     |
| 赵敏      | 一线城市     |
| 小昭      | 一线城市     |
| 杨逍      | 一线城市     |
| 范瑶      | 一线城市     |
| 黛绮丝    | 二线城市     |
| 范凉凉    | 一线城市     |
| 陈友谅    | 一线城市     |
| 张士诚    | 二线城市     |
| 常遇春    | 一线城市     |
| 张三丰    | 二线城市     |
| 灭绝      | 二线城市     |
| 胡青牛    | 二线城市     |
| 周芷若    | 一线城市     |
+-----------+--------------+
16 rows in set (0.00 sec)

（4）CASE [ expr ] WHEN [ val1 ] THEN
[res1] … ELSE [ default ] END

mysql> select * from score;
+------+------+------+---------+---------+
| id   | name | math | english | chinese |
+------+------+------+---------+---------+
|    1 | Tom  |   67 |      88 |      95 |
|    2 | Rose |   23 |      66 |      90 |
|    3 | Jack |   56 |      98 |      76 |
+------+------+------+---------+---------+
3 rows in set (0.00 sec)

## >85显示优秀，大于60显示及格，否则不及格
mysql> select
    ->     id,
    ->     name,
    ->     (case when math>=85 then '优秀' when math >60 then '及格' else '不及格' end) as '数学',
    ->     (case when chinese>=85 then '优秀' when chinese >60 then '及格' else '不及格' end) as '语文',
    ->     (case when english>=85 then '优秀' when english >60 then '及格' else '不及格' end) as '英语'
    -> from score;
+------+------+-----------+--------+--------+
| id   | name | 数学      | 语文   | 英语   |
+------+------+-----------+--------+--------+
|    1 | Tom  | 及格      | 优秀   | 优秀   |
|    2 | Rose | 不及格    | 优秀   | 及格   |
|    3 | Jack | 不及格    | 及格   | 优秀   |
+------+------+-----------+--------+--------+
3 rows in set (0.00 sec)