MySQL学习笔记之函数

最新推荐文章于 2024-10-16 15:12:56 发布

NHuan^_^

最新推荐文章于 2024-10-16 15:12:56 发布

阅读量691

点赞数 7

文章标签： mysql 笔记

本文链接：https://blog.csdn.net/2301_80093566/article/details/141501091

版权

文章目录

MySQL函数

MySQL函数

字符串函数

在 MySQL 中，字符串函数用于处理和操作字符串数据。以下是一些常用的字符串函数及其用法示例：

concat() - 字符串拼接
- 用于将多个字符串连接在一起。
- 示例:

select concat('Hello', ' MySQL'); -- 结果: 'Hello MySQL'

lower() - 转换为小写
- 将字符串中的所有字符转换为小写。
- 示例:

select lower('Hello'); -- 结果: 'hello'

upper() - 转换为大写
- 将字符串中的所有字符转换为大写。
- 示例:

select upper('Hello'); -- 结果: 'HELLO'

lpad() - 左填充
- 用指定字符填充字符串的左侧，直到字符串达到指定长度。
- 示例:

select lpad('01', 5, '-'); -- 结果: '----01'

rpad() - 右填充
- 用指定字符填充字符串的右侧，直到字符串达到指定长度。
- 示例:

select rpad('01', 5, '-'); -- 结果: '01----'

trim() - 去除空格
- 删除字符串开头和结尾的空格。
- 示例:

select trim(' Hello MySQL '); -- 结果: 'Hello MySQL'

substring() - 字符串截取
- 截取字符串的一部分。
- 示例:

select substring('Heoll MySQL', 1, 5); -- 结果: 'Heoll'

应用场景示例

业务需求: 企业员工的工号需要统一为5位数，不足五位的需要在前面补0。

解决方案:

update emp
set workno = lpad(workno, 5, '0');

数值函数

数值函数用于处理数值数据。以下是一些常用的数值函数及其用法示例：

ceil() - 向上取整
- 将小数向上取整为最接近的整数。
- 示例:

select ceil(1.1); -- 结果: 2

floor() - 向下取整
- 将小数向下取整为最接近的整数。
- 示例:

select floor(1.9); -- 结果: 1

mod() - 求模
- 计算两个数之间的余数。
- 示例:

select mod(5, 4); -- 结果: 1

rand() - 生成随机数
- 生成0到1之间的随机数。
- 示例:

select rand(); -- 结果: 例如 0.123456

round() - 四舍五入
- 将数值四舍五入到指定的小数位数。
- 示例:

select round(2.344, 2); -- 结果: 2.34

应用场景示例

生成六位数的随机验证码:

解决方案:

select rpad(round(rand() * 1000000, 0), 6, '0');

日期函数

日期函数用于处理日期和时间数据。以下是一些常用的日期函数及其用法示例：

**curdate()** - 获取当前日期
- 返回当前的日期（年-月-日）。
- 示例:

select curdate(); -- 结果: 例如 '2024-08-24'

curtime() - 获取当前时间
- 返回当前时间（时:分:秒）。
- 示例:

select curtime(); -- 结果: 例如 '14:30:00'

now() - 获取当前日期和时间
- 返回当前的日期和时间（年-月-日时:分:秒）。
- 示例:

select now(); -- 结果: 例如 '2024-08-24 14:30:00'

YEAR(), MONTH(), DAY() - 获取年、月、日
- 分别返回日期的年、月、日部分。
- 示例:

select YEAR(now()); -- 结果: 2024
select MONTH(now()); -- 结果: 8
select DAY(now()); -- 结果: 24

date_add() - 日期加减操作
- 为日期添加指定的时间间隔。
- 示例:

select date_add(now(), INTERVAL 70 YEAR); -- 结果: 例如 '2094-08-24 14:30:00'

datediff() - 计算日期差
- 计算两个日期之间的天数差。
- 示例:

select datediff('2021-12-01', '2021-10-01'); -- 结果: 61

应用景示例

查询所有员工的入职天数，并按入职天数倒序排序:

解决方案:

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

流程控制函数

流程控制函数用于根据条件进行不同的操作。以下是常用的流程控制函数及其用法示例：

if() - 条件判断
- 如果条件为真，则返回第一个值，否则返回第二个值。
- 示例:

select if(true, 'ok', 'error'); -- 结果: 'ok'

ifnull() - 空值判断
- 如果第一个参数不为空，返回第一个参数，否则返回第二个参数。
- 示例:

select ifnull('ok', 'default'); -- 结果: 'ok'
select ifnull(null, 'default'); -- 结果: 'default'

case when ... then ... else ... end - 条件分支
- 根据多个条件，选择不同的结果。
- 示例:

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

应用场景示例

统计学员成绩，显示优秀、及格或不及格:

解决方案:

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;

完整代码

-- ------------------------------------------------ 函数演示 ------------------------------------------------------
-- 字符串函数
-- concat 字符串拼接
select concat('Hello', ' MySQL');

-- lower 全部转为小写
select lower('Hello');

-- upper 全部转为大写
select upper('Hello');

-- lpad 左填充
select lpad('01', 5, '-');

-- rpad 右填充
select rpad('01', 5, '-');

-- trim 去除空格
select trim(' Hello MySQL ');

-- substring 字符串截取
select substring('Heoll MySQL', 1, 5);


-- 1.由于业务需求变更，企业员工的工号，统一为5位数，目前不足五位数的全部在前面补0，比如：1号员工的工号应该为00001。
update emp
set workno = lpad(workno, 5, '0');



-- 数值函数
-- ceil 向上取整
select ceil(1.1);

-- floor 向下取整
select floor(1.9);

-- mod 求模
select mod(5, 4);

-- rand 求随机数
select rand();

-- round 四舍五入
select round(2.344, 2);


-- 案例：通过数据库的函数，生成一个六位数的随机验证码。
select rpad(round(rand() * 1000000, 0), 6, '0');



-- 日期函数
-- curdate() 当前日期
select curdate();

-- curtime() 当前时间（时分秒）
select curtime();

-- now() 当前时间（年月日时分秒）
select now();

-- YEAR, MONTH, DAY 获取年月日
select YEAR(now());
select MONTH(now());
select DAY(now());

-- date_add 返回一个日期/时间值加上一个时间间隔expr后的时间值
select date_add(now(), INTERVAL 70 YEAR);

-- datediff 求取两段时间的差值
select datediff('2021-12-01', '2021-10-01');


-- 案例：查询所有入职天数，并根据入职天数倒序排序。
select name, datediff(curdate(), enerydate) as 'entrydays'
from emp
order by entrydays desc;



-- 流程控制函数
-- if(value, t, f) 如果value为true，则返回t，否则返回f
select if(true, 'ok', 'error');

-- ifnull(value1, value2) 如果value1不为空，返回value1，否则返回value2
select ifnull('ok', 'default');
select ifnull('', 'default');
select ifnull(null, 'default');

-- case when [val1] then [res1] ... else [default] end  如果val1为true，返回res1，...否则返回default默认值
-- 需求：查询emp表的员工姓名和工作地址（北京/上海 ----> 一线城市， 其他 ----> 二线城市）
select name,
       (case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址'
from emp;


-- 案例：统计班级各个学员的成绩，展示的规划如下：
-- >= 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;