字符串函数
--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了