Python学习之复习MySQL-Day5（函数）

---

文章声明???

1. 该文章为我（有编程语言基础，非编程小白）的 MySQL复习笔记
2. 知识来源为 B站UP主（黑马程序员）的MySQL课程视频，归纳为自己的语言与理解记录于此并加以实践
3. 此前我已经学习过了MySQL，现在是在复习阶段，所以不是面向小白的教学文章
4. 不出意外的话，我大抵会 持续更新
5. 想要了解前端开发（技术栈大致有：Vue2/3、微信小程序、uniapp、HarmonyOS、NodeJS、Typescript）与Python的小伙伴，可以关注我！谢谢大家！

---

让我们开始今天的学习吧！

函数简介

函数是指一段可以直接被另一段程序调用的程序或代码

---

字符串函数

常用的字符串函数

函数	功能
concat(s1,s2,…)	字符串拼接
lower(str)	转换小写
upper(str)	转换大写
lpad(str,n,pad)	左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
rpad(str,n,pad)	左填充，用字符串pad对str的右边进行填充，达到n个字符串长度
trim(str)	去掉字符串头部和尾部的空格
substring(str,start,len)	返回从字符串str从start位置起的len个长度的字符串

实例演示

mysql> select concat('Hello','Richie');
+--------------------------+
| concat('Hello','Richie') |
+--------------------------+
| HelloRichie              |
+--------------------------+
1 row in set (0.03 sec)

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

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

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

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

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

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

---

数值函数

常用的数值函数

函数	功能
ceil(x)	向上取整
floor(x)	向下取整
mod(x,y)	返回x/y的模（余数）
rand()	返回0-1内的随机数
round(x,y)	求参数x的四舍五入的值，保留y位小数

实例演示

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

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

mysql> select mod(3,4); # 意思就是求余数
+----------+
| mod(3,4) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.2728144491769508 |
+--------------------+
1 row in set (0.03 sec)

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

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

mysql> select rpad(round(rand()*10000,0),4,'0'); # 生成一个四位数的随机验证码
+-----------------------------------+
| rpad(round(rand()*10000,0),4,'0') |
+-----------------------------------+
| 7146                              |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> select rpad(round(rand()*10000,0),4,'0'); # 生成一个四位数的随机验证码
+-----------------------------------+
| rpad(round(rand()*10000,0),4,'0') |
+-----------------------------------+
| 2583                              |
+-----------------------------------+
1 row in set (0.00 sec)

---

日期函数

常用的日期函数

函数	功能
curdate()	返回当前日期
curtime()	返回当前时间
now()	返回当前日期和时间
year(date)	获取指定date的年份
month(date)	获取指定date的月份
day(date)	获取指定date的日期
date_add(date,interval expr type)	返回一个日期/时间值加上一个时间间隔expr后的时间值
datediff(date1,date2)	返回起始时间date1和结束时间date2之间的天数

实例演示

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2023-12-15 |
+------------+
1 row in set (0.03 sec)

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 15:13:39  |
+-----------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2023-12-15 15:13:53 |
+---------------------+
1 row in set (0.00 sec)

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

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

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

mysql> select date_add(now(),interval 70 day); # 今天往后推70天
+---------------------------------+
| date_add(now(),interval 70 day) |
+---------------------------------+
| 2024-02-23 15:15:29             |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select date_add(now(),interval 70 month); # 今天往后推70个月份
+-----------------------------------+
| date_add(now(),interval 70 month) |
+-----------------------------------+
| 2029-10-15 15:15:51               |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> select date_add(now(),interval 70 year); # 今天往后推70年
+----------------------------------+
| date_add(now(),interval 70 year) |
+----------------------------------+
| 2093-12-15 15:16:06              |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select datediff(now(),'2023-6-26');
+-----------------------------+
| datediff(now(),'2023-6-26') |
+-----------------------------+
|                         172 |
+-----------------------------+
1 row in set (0.03 sec)

mysql> select datediff(now(),'2024-6-26');
+-----------------------------+
| datediff(now(),'2024-6-26') |
+-----------------------------+
|                        -194 |
+-----------------------------+
1 row in set (0.00 sec)

---

流程函数

常用的流程函数

函数	功能
if(value,t,f)	如果value为true，则返回t，否则返回f
ifnull(value1,value2)	如果value1不为空，返回value1，否则返回value2
case when [val1] then [res1] … else [dafault] end	如果val1为true，返回res1，…，否则返回default默认值
case [expr] when [val1] then [res1] … else [dafault] end	如果expr的值等于val1，返回res1，…，否则返回default默认值

实例演示

mysql> select if(true,'ok','error');
+-----------------------+
| if(true,'ok','error') |
+-----------------------+
| ok                    |
+-----------------------+
1 row in set (0.00 sec)

mysql> select if(1 = 2,'ok','error');
+------------------------+
| if(1 = 2,'ok','error') |
+------------------------+
| error                  |
+------------------------+
1 row in set (0.00 sec)

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

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

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

mysql> select # 查询员工工作城市和姓名，是上海和北京的写为一线城市，其他的写为二线城市
    ->     name,
    ->     (case address when '上海' then '一线城市'
    ->     when '北京' then '一线城市'
    ->     else '二线城市' end) as '工作地址'
    -> from emp;

---