前言
在第8讲MySQL第8讲–DCL(数据控制语言)的基本操作中我们讲到了DCL中用户管理和权限操作的一些基本知识;接下来我们将要讲一下函数;
函数
函数:是指一段可以直接被另一段调用的程序和代码;
在mysql中函数分为四个部分:字符串函数、数值函数、日期函数、流程函数;接下来我们将一一进行介绍;
字符串函数
在mysql中内置的字符串函数有很多,接下来我们介绍几个比较常用的字符串函数;
函数名称 | 功能 |
---|---|
CONCAT(S1,S2,…SN) | 字符拼接,将S1,S2,…SN拼接成一个字符串 |
LOWER(str) | 将字符串str全部转换成小写 |
UPPER(str) | 将字符串str全部转换成大写 |
LPAD(str, n, pad) | 左填充,用字符串pad对str的左边进行填充,达到n个字符串长度 |
RPAD(str, n, pad) | 右填充,用字符串pad对str的右边进行填充,达到n个字符串长度 |
TRIM(str) | 去掉字符串头部和尾部的空格 |
SUBSTRING(str) | 返回从字符串str位置起的len个长度的字符串 |
那这些内置函数的使用语法如下:
SELECT 函数(参数);
接下来我们演示一下以上几个函数如何使用:
实例1:CONCAT() 函数的使用;
select concat('hello','mysql');
运行结果:
从以上结果我们可以看出两个字符串连接在了一起,但是为了美观和断句,我们需要在两个字符串之间加一个空格;可以如下修改
select concat('hello', ' ','mysql');
运行结果:
这样就生成了我们想要的语句结构;
实例2:LOWER() 函数的使用;
select lower('HELLO');
运行结果:
从以上结果可以看出字符全部转换成小写了;
实例3:UPPER() 函数的使用;
select lower('HELLO');
运行结果:
从以上结果可以看出字符全部转换成大写了;
实例4:LPAD(str, n, pad)函数的使用;
select lpad('lucy',10,'_');
运行结果:
可以看到在lucy的左边填充了6个“_”;
实例5:RPAD(str, n, pad)函数的使用;
select rpad('lucy',10,'_');
运行结果:
可以看到在lucy的右边填充了6个“_”;
实例6:trim()函数的使用;
select trim(' hello mysql ');
运行结果:
从以上结果可以看出头尾的空格都去掉了;
实例7:substring()函数的使用;
select substring('hello mysql',1, 5);
# 截取字符串'hello mysql',从1位置开始截取,截取5个字符;
以上几个函数知道如何应用后,我们联系一个案列:
案例1:由于业务变更需求,员工的工号统一更改成5位数,不足5位数的在前面补充0,如:00001;
update employ set worknum = lpad(worknum,5,'0');
运行结果:
从以上运行结果可以看出worknum都更新成了5位;
数值函数
字符串函数介绍完了,接下来我们介绍一下数值函数,常见的数值函数如下图所示:
数值函数 | 功能 |
---|---|
CEIL(X) | 向上取整数 |
FLOOR(X) | 向下取整数 |
MOD(X,Y) | 返回x/y的模 |
RAND() | 返回0~1内的随机数 |
ROUND(X,Y) | 求参数x的四舍五入的值,保留y位小数 |
接下来我们演示一下这5个函数;
练习1:CEIL(X) 的使用
select ceil(30.6); # 向上取整数
运行结果:
练习2: FLOOR(X) 的使用
select floor(30.6); # 向下取整
运行结果:
练习3: MOD(X,Y) 的使用
select mod(6,4); # 就是求余数是几
运行结果:
练习4: RAND() 的使用
select rand(); # 随机生成0~1的数
运行结果:
练习5:ROUND(X,Y) 的使用
select round(10.5868997,3); # 对10.5868997四舍五入保留3位小数
运行结果:
以上几位函数知道如何应用后,下面我们就来通过一个案列来熟悉一下我们所学的知识;
案例1:通过数据库的函数生成一个六位数的随机验证码;
select lpad(ceil(rand()*1000000),6,'0');
运行结果:
日期函数
日期函数常见的函数如下:
日期函数 | 功能 |
---|---|
CURDATE() | 返回当前日期 |
CURTIME () | 返回当前时间 |
NOW() | 返回当前日期和时间 |
YEAR(DATE) | 获取指定date的年份 |
MONTH(DATE) | 获取指定date的月份 |
DAY(DATE) | 获取指定date的日期 |
DATE_ADD(date,interval expr type) | 返回一个日期/时间值加上一个时间间隔expr后的时间间隔 |
DATEDIFF(date1,date2) | 返回起始时间date1和结束时间date2之间的天数 |
接下来我们演示一下这几个函数:
练习1:CURDATE()练习
select curdate();
运行结果:
练习2:CURTIME()练习
select curtime();
运行结果:
练习3:NOW()练习
select now();
运行结果:
练习4:DAY(DATE)练习
select day('2024,3,4');
select day(now()); # 获取当前的天
运行结果:
练习5:MONTH(DATE) 练习
select month('2024,3,4');
select month(now()); # 获取当前的月
运行结果:
练习6:YEAR(DATE)练习
select year('2024,3,4');
select year(now()); # 获取当前的年
运行结果:
练习7:DATE_ADD(date,interval expr type)练习
select date_add(now(),interval 70 day );
# 从当前时间算起,间隔70天
运行结果:
练习7:DATEDIFF(date1,date2)练习
select datediff( now(),'2023,3,4'); # 第一个时间-第二个时间
运行结果:
练习完以上的函数后,我们练习一个案例,来熟悉我们的知识点;
案例1:查询所有员工的入职天数;
select name, datediff(curdate(), employ.intime) from employ;
运行结果:
案例2:查询所有员工的入职天数,并根据入职天数进行排序;
select name, datediff(curdate(), employ.intime) as entryday from employ order by entryday;
# 对入职天数进行取别名entryday,并且根据entryday排序;
运行结果:
流程函数
流程函数在sql语句中实现条件筛选,从而提高语句的效率;
常见的流程函数如下:
流程函数 | 功能 |
---|---|
IF(value, t, f) | 如果value为true则返回t,否则返回f |
IFNULL(value1, value2) | 如果 value1不为空返回value1,否则返回value2 |
CAS WHEN [val1] THEN [res1]…ELSE [default] END | 如果val1为true返回res1,否则返回默认值 |
CAS [expr] WHEN [val1] THEN [res1] … ELSE [default] END | 如果expr的值等于 val1,则返回res1,否则返回默认值 |
接下来我们对以上的函数一一进行练习;
练习1: IF(value, t, f)
select if(true, 'ok','no');
运行结果:
练习2:IFNULL(value1, value2)
select ifnull('hello', 'nihao'); # 第一个不为空则返回第一个
运行结果:
练习3: CAS 语句
需求:查询表中的员工名和工作地址;工作地址为北京和上海的显示为一线城市,其他的为二线城市;
select name,
(case address when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址'
from employ;
运行结果:
以上对以上函数的练习就完成了,接下来我们通过一个案例熟悉一下所学的几个函数;
案例1:统计各个员工的年龄,
展示规则如下:大于等于27岁的,显示成熟
--------------------->小于等于25的,显示稚嫩
--------------------->否则显示正当年级
select name,
(case when age>=27 then '成熟' when age<=25 then '稚嫩' else '正当年' end) as '状态'
from employ;
运行结果:
以上是所有函数的常用知识点;
如有错误欢迎指正,如果帮到您请点赞加收藏哦!