示例1:concat(s1,s2,s3,...) 函数
mysql> select concat("hello","world");
+-------------------------+
| concat("hello","world") |
+-------------------------+
| helloworld |
+-------------------------+
1 row in set (0.00 sec)
示例2:lower(str)
mysql> select lower("Hello");
+----------------+
| lower("Hello") |
+----------------+
| hello |
+----------------+
1 row in set (0.00 sec)
示例3:upper(str)
mysql> select upper("Hello");
+----------------+
| upper("Hello") |
+----------------+
| HELLO |
+----------------+
1 row in set (0.00 sec)
示例4:lpad(str,n,pad)|
情况一:n小于原始字符串str的长度,只显示原始字符串的长度n的字符
mysql> select lpad("张三",1,"靓仔");
+---------------------------+
| lpad("张三",1,"靓仔") |
+---------------------------+
| 张 |
+---------------------------+
1 row in set (0.00 sec)
情况二:当长度n大于str字符串的原始长度,小于拼接后的字符串长度后,显示原来的字符串str+pad继续补齐到n个字符串
mysql> select lpad("张三",3,"靓仔");
+---------------------------+
| lpad("张三",3,"靓仔") |
+---------------------------+
| 靓张三 |
+---------------------------+
1 row in set (0.00 sec)
情况三:如果n大于拼接后的字符串长度,会重复拼接pad字符串
mysql> select lpad("张三",6,"靓仔");
+---------------------------+
| lpad("张三",6,"靓仔") |
+---------------------------+
| 靓仔靓仔张三 |
+---------------------------+
1 row in set (0.00 sec)
示例5:rpad(str,n,pad)
mysql> select rpad("张三",1,"靓仔");
+---------------------------+
| rpad("张三",1,"靓仔") |
+---------------------------+
| 张 |
+---------------------------+
1 row in set (0.00 sec)
mysql> select rpad("张三",3,"靓仔");
+---------------------------+
| rpad("张三",3,"靓仔") |
+---------------------------+
| 张三靓 |
+---------------------------+
1 row in set (0.00 sec)
mysql> select rpad("张三",6,"靓仔");
+---------------------------+
| rpad("张三",6,"靓仔") |
+---------------------------+
| 张三靓仔靓仔 |
+---------------------------+
1 row in set (0.00 sec)
情况处理:同lpad情况
示例6:trim(str)
mysql> select trim(" Hello world ");
+-----------------------+
| trim(" Hello world ") |
+-----------------------+
| Hello world |
+-----------------------+
1 row in set (0.00 sec)
注意:trim只会取出开始和结尾的空格,不会去除中间的空格。
示例7:substring(str,start,len)
mysql> select substring("hello world", 1, 5);
+--------------------------------+
| substring("hello world", 1, 5) |
+--------------------------------+
| hello |
+--------------------------------+
1 row in set (0.00 sec)
注意:截取字符串的索引从1开始
示例8:在table_test中对id重新编号,统一显示五位数,不足五位数的前面补0
原始表test数据
mysql> select * from test;
+------+---------+--------------+-----------+
| id | stu_num | stu_name | stu_score |
+------+---------+--------------+-----------+
| 1 | 1 | 张三 | 90 |
| 2 | 2 | 李四 | 87 |
| 3 | 3 | 王五 | 78 |
| 4 | 14 | 赵六 | 83 |
| 5 | 25 | 王二麻子 | 94 |
+------+---------+--------------+-----------+
5 rows in set (0.00 sec)
修改后的数据
mysql> update test set stu_num = lpad(stu_num,5,'0');
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5 Changed: 5 Warnings: 0
mysql> select * from test;
+------+---------+--------------+-----------+
| id | stu_num | stu_name | stu_score |
+------+---------+--------------+-----------+
| 1 | 00001 | 张三 | 90 |
| 2 | 00002 | 李四 | 87 |
| 3 | 00003 | 王五 | 78 |
| 4 | 00014 | 赵六 | 83 |
| 5 | 00025 | 王二麻子 | 94 |
+------+---------+--------------+-----------+
5 rows in set (0.00 sec)
示例1:ceil(x):向上取整
mysql> select ceil(1.1);
+-----------+
| ceil(1.1) |
+-----------+
| 2 |
+-----------+
1 row in set (0.00 sec)
示例2:floor(x):向下取整
mysql> select floor(1.9);
+------------+
| floor(1.9) |
+------------+
| 1 |
+------------+
1 row in set (0.00 sec)
示例3:mod(x,y):返回x/y的模
mysql> select mod(5,2);
+----------+
| mod(5,2) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
示例4:rand():返回0-1内的随机数
mysql> select rand();
+---------------------+
| rand() |
+---------------------+
| 0.20385351237268515 |
+---------------------+
1 row in set (0.00 sec)
示例5:round(x,y):求参数x的四舍五入的值,保留y位小数
mysql> select round(3.1415926,2);
+--------------------+
| round(3.1415926,2) |
+--------------------+
| 3.14 |
+--------------------+
1 row in set (0.00 sec)
示例6:综合案例:通过数据库函数,生成一个六位数的随机验证码
mysql> select rpad(round(rand()*1000000, 0),6,'0');
+--------------------------------------+
| rpad(round(rand()*1000000, 0),6,'0') |
+--------------------------------------+
| 192803 |
+--------------------------------------+
1 row in set (0.00 sec)
说明:
1、rand()生成的小数,乘以1000000,才有可能是有六位整数的小数,所以这个时候需要市容round()四舍五入取整数
2、因为rand()生成的小数有可能是0.999999这样的乘以1000000就是6位整数,也有可能生成的小数是0.000009这样的小数就算乘以1000000也不够六位整数
3、所以还需要使用rpad()函数,如果整数不足六位,就在后面补0
示例1:curdate():返回当前日期
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2023-07-18 |
+------------+
1 row in set (0.00 sec)
示例2:curtime():返回当前时间
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 21:52:43 |
+-----------+
1 row in set (0.00 sec)
示例3:now():返回当前日期和时间
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2023-07-18 21:53:10 |
+---------------------+
1 row in set (0.00 sec)
示例4:year(date):获取指定date的年份
mysql> select year("2023-07-18 21:53:10");
+-----------------------------+
| year("2023-07-18 21:53:10") |
+-----------------------------+
| 2023 |
+-----------------------------+
1 row in set (0.01 sec)
示例5:month(date):获取指定date的月份
mysql> select month("2023-07-18 21:53:10");
+------------------------------+
| month("2023-07-18 21:53:10") |
+------------------------------+
| 7 |
+------------------------------+
1 row in set (0.00 sec)
示例6:day(date):获取指定date的日期
mysql> select day("2023-07-18 21:53:10");
+----------------------------+
| day("2023-07-18 21:53:10") |
+----------------------------+
| 18 |
+----------------------------+
1 row in set (0.00 sec)
示例7:date_add(date,interval expr type):返回一个日期/时间值加上一个时间间隔expr后的时间值
mysql> select date_add("2023-07-18 21:53:10", interval 2 day);
+-------------------------------------------------+
| date_add("2023-07-18 21:53:10", interval 2 day) |
+-------------------------------------------------+
| 2023-07-20 21:53:10 |
+-------------------------------------------------+
1 row in set (0.01 sec)
mysql> select date_add("2023-07-18 21:53:10", interval 2 month);
+---------------------------------------------------+
| date_add("2023-07-18 21:53:10", interval 2 month) |
+---------------------------------------------------+
| 2023-09-18 21:53:10 |
+---------------------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add("2023-07-18 21:53:10", interval 2 year);
+--------------------------------------------------+
| date_add("2023-07-18 21:53:10", interval 2 year) |
+--------------------------------------------------+
| 2025-07-18 21:53:10 |
+--------------------------------------------------+
1 row in set (0.00 sec)
示例8:datediff(date1,date2):返回起始时间date1和结束时间date2之间的天数
mysql> select datediff("2023-07-18 21:53:10", "2023-07-28 21:53:10");
+--------------------------------------------------------+
| datediff("2023-07-18 21:53:10", "2023-07-28 21:53:10") |
+--------------------------------------------------------+
| -10 |
+--------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select datediff("2023-07-28 21:53:10", "2023-07-18 21:53:10");
+--------------------------------------------------------+
| datediff("2023-07-28 21:53:10", "2023-07-18 21:53:10") |
+--------------------------------------------------------+
| 10 |
+--------------------------------------------------------+
1 row in set (0.00 sec)
示例1:if(value, t, f):如果value为true,则返回t,否则返回f
mysql> select if(1>2,"ok","no");
+-------------------+
| if(1>2,"ok","no") |
+-------------------+
| no |
+-------------------+
1 row in set (0.00 sec)
示例2:ifnull(value1,value2):如果value1不为空,返回value1,否则返回value2
mysql> select ifnull("ok","default");
+------------------------+
| ifnull("ok","default") |
+------------------------+
| ok |
+------------------------+
1 row in set (0.00 sec)
mysql> select ifnull("","default");
+----------------------+
| ifnull("","default") |
+----------------------+
| | 注意:空字符串不是null
+----------------------+
1 row in set (0.00 sec)
mysql> select ifnull(null,"default");
+------------------------+
| ifnull(null,"default") |
+------------------------+
| default |
+------------------------+
1 row in set (0.00 sec)
示例3:case [expr] when [val1] then [res1]...else[default] end:如果val1为true,则返回res1,否则返回default默认值
需求:在table_test中,展示姓名和特色一栏,特色根据address的值来设定,北京特色烤鸭,上海特色蟹黄包,西安特色肉夹馍,其他地方都是红烧肉
table_test表原始数据
mysql> select * from table_test;
+------+--------+------+---------+
| id | name | age | address |
+------+--------+------+---------+
| 1 | 刘一 | 18 | 西安 |
| 2 | 陈二 | 19 | 上海 |
| 3 | 张三 | 21 | 北京 |
| 4 | 李四 | 18 | 北京 |
| 5 | 王五 | 21 | 西安 |
| 6 | 赵六 | 22 | NULL |
+------+--------+------+---------+
6 rows in set (0.00 sec)
mysql> select name ,(case address when "北京" then "烤鸭" when "上海" then "蟹黄包" when "西安" then "肉夹馍" else "红烧肉" end) as "特色" from table_test;
+--------+--------------+
| name | 特色 |
+--------+--------------+
| 刘一 | 肉夹馍 |
| 陈二 | 蟹黄包 |
| 张三 | 烤鸭 |
| 李四 | 烤鸭 |
| 王五 | 肉夹馍 |
| 赵六 | 红烧肉 |
+--------+--------------+
6 rows in set (0.00 sec)
示例4:需求:在学生成绩表中,根据成绩展示优秀,良好一般。
学生成绩表(test)原始数据
mysql> select * from test;
+------+---------+--------------+-----------+
| id | stu_num | stu_name | stu_score |
+------+---------+--------------+-----------+
| 1 | 00001 | 张三 | 90 |
| 2 | 00002 | 李四 | 87 |
| 3 | 00003 | 王五 | 78 |
| 4 | 00014 | 赵六 | 83 |
| 5 | 00025 | 王二麻子 | 94 |
+------+---------+--------------+-----------+
5 rows in set (0.00 sec)
mysql> select stu_name, (case when stu_score>=90 then "优秀" when stu_score >=80 then "良好" when stu_score >=70 then
"一般" when stu_score >=60 then "及格" else "不及格" end) as "成绩" from test;
+--------------+--------+
| stu_name | 成绩 |
+--------------+--------+
| 张三 | 优秀 |
| 李四 | 良好 |
| 王五 | 一般 |
| 赵六 | 良好 |
| 王二麻子 | 优秀 |
+--------------+--------+
5 rows in set (0.00 sec)