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)