5. mysql函数

字符串函数

--concat 拼接字符串

mysql> select concat("Hello",'Mysql');
+-------------------------+
| concat("Hello",'Mysql') |
+-------------------------+
| HelloMysql              |
+-------------------------+
1 row in set (0.00 sec)

--lower 转小写

mysql> select lower("Hello");
+----------------+
| lower("Hello") |
+----------------+
| hello          |
+----------------+
1 row in set (0.00 sec)

--upper('Hello')

全部转换为大写
mysql> select upper("Hello");
+----------------+
| upper("Hello") |
+----------------+
| HELLO          |
+----------------+
1 row in set (0.00 sec)

-- lpad(str,len,str) 第一个参数为需要替换的字符串,第二个是替换到多长,第三个是使用什么替换

从前面填充
mysql> select lpad('01',5,'-');
+------------------+
| lpad('01',5,'-') |
+------------------+
| ---01            |
+------------------+
1 row in set (0.00 sec)

-- rpad(str,5,'-');从后面填充 跟上面的一样

mysql> select rpad('01',5,'-');
+------------------+
| rpad('01',5,'-') |
+------------------+
| 01---            |
+------------------+
1 row in set (0.00 sec)

--trim() 去除头尾空格

mysql> select trim("    Hello     Mysql      ");
+-----------------------------------+
| trim("    Hello     Mysql      ") |
+-----------------------------------+
| Hello     Mysql                   |
+-----------------------------------+
1 row in set (0.00 sec)

--substring(str,1,5); 对于一个字符串从哪里截取 截取多少个返回

mysql> select substring('Hello Mysqll',1,5);
+-------------------------------+
| substring('Hello Mysqll',1,5) |
+-------------------------------+
| Hello                         |
+-------------------------------+
1 row in set (0.00 sec)

更新表中的name字段填充为5个 不满的往前面填充0
mysql> update emp2 set name = lpad(name,5,'0');
Query OK, 16 rows affected (0.00 sec)
Rows matched: 16  Changed: 16  Warnings: 0

mysql> select * from emp2;
+------+-------------+--------+------+----------------------+-------------+------------+
| id   | name        | gendef | age  | idcard               | workaddress | entrydate  |
+------+-------------+--------+------+----------------------+-------------+------------+
|    1 | 000柳岩     | 女     |   22 | 20123456789012345678 | 北京        | 2000-01-01 |
|    2 | 000柳岩     | 女     |   22 | 20123456789012345678 | 北京        | 2000-01-01 |
|    3 | 00韦一笑    | 男     |   38 | 123456789712345670   | 上海        | 2005-08-01 |
|    4 | 00韦一笑    | 男     |   38 | 123456789712345670   | 上海        | 2005-08-01 |
|    5 | 000小昭     | 女     |   16 | 123456769012345678   | 上海        | 2007-07-01 |
|    6 | 000杨道     | 男     |   28 | 12345678931234567X   | 北京        | 2007-04-10 |
|    7 | 000范瑶     | 男     |   40 | 123456789212345670   | 北京        | 2005-05-01 |
|    8 | 00黛绮丝    | 女     |   38 | 123456157123645670   | 天津        | 2015-05-01 |
|    9 | 00范凉凉    | 女     |   45 | 123156789012345678   | 北京        | 2010-04-01 |
|   10 | 00陈友谅    | 男     |   53 | 123456789012345670   | 上海        | 2011-01-01 |
|   11 | 00张士诚    | 男     |   55 | 123567897123465670   | 江苏        | 2015-05-01 |
|   12 | 00常遇春    | 男     |   32 | 123446757152345670   | 北京        | 2004-02-01 |
|   13 | 00张三丰    | 男     |   88 | 123656789012345678   | 江苏        | 2020-11-01 |
|   14 | 000灭绝     | 女     |   65 | 123456719012345670   | 西安        | 2019-05-01 |
|   15 | 00胡青牛    | 男     |   70 | 12345674971234567X   | 西安        | 2018-04-01 |
|   16 | 00周芷若    | 女     |   18 | NULL                 | 北京        | 2012-06-01 |
+------+-------------+--------+------+----------------------+-------------+------------+
16 rows in set (0.00 sec)

数值函数

--CELT(x)向上取整数

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

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

FLOOR(x)向下取整数

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

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

--MOD(x,y) 返回x/y的模

取余数
mysql> select mod(6,4);
+----------+
| mod(6,4) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

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

--RAND() 返回0~1的随机数

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

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

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

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.9038292988239129 |
+--------------------+

--ROUND(x,y) 求参数四舍五入,保留y位小数

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

日期函数

--curdate() 当前日期

-- curtime; 当前时间

--now 全部的时间

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 10:21:40  |
+-----------+
1 row in set (0.00 sec)

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2023-10-16 |
+------------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2023-10-16 10:22:12 |
+---------------------+
1 row in set (0.00 sec)

--year 获取时间中的年

--month 获取时间中的月

--day 获取时间中的天

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

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

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

--date_add(time,flag)推算time后flag

mysql> select date_add(now(),interval 70 day);
+---------------------------------+
| date_add(now(),interval 70 day) |
+---------------------------------+
| 2023-12-25 10:25:49             |
+---------------------------------+
1 row in set (0.00 sec)

--datediff(time,time) 两个时间差多少天

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

查询所有员工的入职数,并根据入职天数倒序
mysql> select name,datediff(now(),entrydate) from emp2 order by entrydate desc;
+-------------+---------------------------+
| name        | datediff(now(),entrydate) |
+-------------+---------------------------+
| 00张三丰    |                      1079 |
| 000灭绝     |                      1629 |
| 00胡青牛    |                      2024 |
| 00黛绮丝    |                      3090 |
| 00张士诚    |                      3090 |
| 00周芷若    |                      4154 |
| 00陈友谅    |                      4671 |
| 00范凉凉    |                      4946 |
| 000小昭     |                      5951 |
| 000杨道     |                      6033 |
| 00韦一笑    |                      6650 |
| 00韦一笑    |                      6650 |
| 000范瑶     |                      6742 |
| 00常遇春    |                      7197 |
| 000柳岩     |                      8689 |
| 000柳岩     |                      8689 |
+-------------+---------------------------+
16 rows in set (0.00 sec)

流程函数--if(条件,真,假)

判断第一个参数返回的值 真返回第二个参数假返回第三个
mysql> select if(false,"OK","Error");
+------------------------+
| if(false,"OK","Error") |
+------------------------+
| Error                  |
+------------------------+
1 row in set (0.00 sec)

--ifnull() 第一个值为空返回后面的不为空返回第一个值

mysql> select ifnull("OK","空");
+--------------------+
| ifnull("OK","空")  |
+--------------------+
| OK                 |
+--------------------+
1 row in set (0.00 sec)


mysql> select ifnull(Null,"空");
+--------------------+
| ifnull(Null,"空")  |
+--------------------+
| 空                 |
+--------------------+
1 row in set (0.00 sec)

工作地址是上海北京的显示一线城市其他显示二线城市

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

需要使用 case when 因为匹配的不是具体的值 所以如果用

case [expr] 第一个判断失败就直接 else了