MySQL基础6——MySQL函数
cuckooyang
|
|
MySQL
|
809

976 字
|
15 分钟
本文最后更新于 518 天前，如有失效请评论区留言。

定义

函数是指一段可以直接被另一段程序调用的程序或代码。MySQL中的函数常用的有字符串函数、数值函数、日期函数、流程函数等，下面将逐一进行讲解。

字符串函数

MySQL中内置了很多字符串函数，以下是常用的几个字符串函数。

image-20240730170425874

使用方法

concat

mysql> select concat('Hello' , ' MySQL');
+---------------------------+
| concat('Hello' , 'MySQL') |
+---------------------------+
| Hello MySQL                |
+---------------------------+

lower

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

upper

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

lpad

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

rpad

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

trim

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

substring

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

使用案例

由于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0。比如：1号员工的工号应该为00001。

mysql> update emp set workno = lpad(workno, 5, 0);
mysql> select * from emp;

image-20240730172725242

数值函数

常见数值函数如下

image-20240730173332678

使用方法

ceil

  • 小数位不管有多小，个位都+1。
mysql> select ceil(1.1);
+-----------+
| ceil(1.1) |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)

floor

  • 小数位不管有多大，都舍弃。
mysql> select floor(1.9);
+------------+
| floor(1.9) |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

mod

  • 5除以4的余数，也就是取模。
mysql> select mod(5,4);
+----------+
| mod(5,4) |
+----------+
|        1 |
+----------+
1 row in set (0.01 sec)

rand

  • 括号内不用传递任何参数，取0-1之间的随机小数。
mysql> select rand();
+---------------------+
| rand()              |
+---------------------+
| 0.18073834309423187 |
+---------------------+
1 row in set (0.00 sec)

round

  • 四舍五入，第二个参数是保留的小数位数。
mysql> select round(2.345,2);
+----------------+
| round(2.345,2) |
+----------------+
|           2.35 |
+----------------+
1 row in set (0.00 sec)

使用案例

通过数据库的函数，生成一个六位数的随机验证码。

  • 通过rand生成随机小数
  • 随机小数*1000000倍，并在个位四舍五入，从而得到0-1000000之间的随机数。
  • 在前面补0，保证是6位数
mysql> select round(rand()*1000000, 0);
+--------------------------+
| round(rand()*1000000, 0) |
+--------------------------+
|                   751882 |
+--------------------------+
1 row in set (0.00 sec)
mysql> select lpad(round(rand()*1000000, 0), 6, 0);
+--------------------------------------+
| lpad(round(rand()*1000000, 0), 6, 0) |
+--------------------------------------+
| 009213                               |
+--------------------------------------+
1 row in set (0.00 sec)

日期函数

常见日期函数如下：

image-20240731091614631

使用方法

curdate()

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2024-07-31 |
+------------+
1 row in set (0.00 sec)

curtime()

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 09:27:19  |
+-----------+
1 row in set (0.00 sec)

now()

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-07-31 09:27:41 |
+---------------------+
1 row in set (0.00 sec)

year(date)

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

month(date)

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

day(date)

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

date_add(date,interval expr type)，增加一个日期，大小取决于type指定的单位。

mysql> select date_add(now(), interval 70 day );
+-----------------------------------+
| date_add(now(), interval 70 day ) |
+-----------------------------------+
| 2024-10-09 09:34:54               |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> select date_add(now(), interval 70 month);
+------------------------------------+
| date_add(now(), interval 70 month) |
+------------------------------------+
| 2030-05-31 11:42:57                |
+------------------------------------+
1 row in set (0.00 sec)

datediff(date1,date2)

  • 第一个时间减去第二个时间，所以这里是负数。
mysql> select datediff('2021-12-01', '2024-07-31');
+--------------------------------------+
| datediff('2021-12-01', '2024-07-31') |
+--------------------------------------+
|                                 -973 |
+--------------------------------------+
1 row in set (0.00 sec)

使用案例

查询所有员工的入职天数，并根据入职天数倒序排序。

select * from emp;
select name,datediff(curdate(), entrydate) from emp;
select name,datediff(curdate(), entrydate) as 'entrydays' from emp order by entrydays desc;

image-20240731115038112

流程控制函数

流程控制函数也是很常用的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率。

image-20240731115109235

使用方法

if

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)

ifnull

  • 第一个值是否为null
mysql> select ifnull('ok', 'Default');
+-------------------------+
| ifnull('ok', 'Default') |
+-------------------------+
| ok                      |
+-------------------------+
1 row in set (0.00 sec)

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

case when then else end

需求：查询emp表的员工姓名和工作地址，如果员工地址是上海或者是北京，要展示一线城市，其他城市展示为二线城市。

select
    name,
    (case workaddress when '北京' then '一线城市' when  '上海' then '一线城市' else '二线城市' end) as ‘工作地址’
from emp;

image-20240731123443773

使用案例

统计班级各个学员的成绩，展示的规则如下：>=85，展示优秀；>=60，展示及格；否则，展示不及格。

准备表格

create table score(
    id      int comment 'ID',
    name    varchar(20) comment '姓名',
    math    int comment '数学',
    english int comment '英语',
    chinese int comment '语文'
)comment '学员成绩表';
insert into score(id,name,math,english,chinese) values(1,'Tom',67,88,95),(2,'Rose',23,66,90),(3,'Jack',56,98,76);

进行查询

select
    id,
    name,
    (case  when math >= 85 then '优秀' when math >= 60 then '及格' else '不及格' end) as '数学',
    (case  when english  >= 85 then '优秀' when english >= 60 then '及格' else '不及格' end) as '英语',
    (case  when chinese  >= 85 then '优秀' when chinese >= 60 then '及格' else '不及格' end) as '语文'
from score;

image-20240731125242533

函数小结

1、字符串函数

concat, lower, upper, lpad, rpad, trim, substring

2、数值函数

ceil, floor, mod, rand, round

3、日期函数

curdate, curtime, now, year, month, day, date_add, datediff

4、流程函数

if, ifnull, case [...] when ... then...else...end

需求1：

数据库表中，存储的是入职日期，如2000-01-01，如何快速计算入职天数。

思路：使用datediff实现

数据库表中，存储的是学生分数值，如何快速判定分数等级。

思路：使用case when then else end实现
版权声明：除特殊说明，博客文章均为cuckooyang原创，依据CC BY-SA 4.0许可证进行授权，转载请附上出处链接及本声明。 | 博客订阅：RSS | 广告招租：留言板 | 博客VPS |
暂无评论

发送评论 编辑评论 


				

			

						

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
							
													

					

				

			

			
			

				

					
				

			

				

											

							
							
						

																					

							
							
						

										
					
											
						

	

		
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
（╯‵□′）╯︵┴─┴
￣﹃￣
(/ω＼)
∠( ᐛ 」∠)＿
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ｀)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ(￣∇￣o)
ヾ(´･ ･｀｡)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò｡)
Σ(っ °Д °;)っ
( ,,´･ω･)ﾉ"(´っω･｀｡)
╮(╯▽╰)╭
o(*////▽////*)q
＞﹏＜
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
	

	

		
颜文字
Emoji
小恐龙
花!
	


									

			

			
			
		

	






上一篇
下一篇