SQL函数

目录

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）if和ifnull

（2）case...when...then...else...end

格式1：CASE [ expr ] WHEN [ val1 ] THEN [res1] ... ELSE [ default ] END

格式2：CASE WHEN [ val1 ] THEN [res1] ... ELSE [ default ] END

---

1.字符串函数：concat, lower, upper, lpad, rpad, trim, substring

-- concat 字符串拼接
select concat('hap','py');

-- lower 小写
select lower('Happy');
-- upper 大写
select upper('Happy');

-- lpad 左填充 指定字符串，总长度和填充内容
# 总长度是最终要求的长度,不是添加多长的填充内容
# 若填充内容整体长度超出,则按顺序只截取一部分
select lpad('happy',10,'0123');
-- rpad 右填充
select rpad('happy',10,'0123');

-- trim 去除开头结尾的空格
select trim('    happy  every  day ');

-- substring 提取子字符串，指定字符串，起始提取位置和提取长度
# 注意：会包含start位置对应的内容
select substring('happy every day',5,6);

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

select lpad(workno,5,'0') from information; # 只是查询，要更改需要update修改数据
update information set workno = lpad(workno,5,'0');
select * from information;

2.数值函数: ceil, floor, mod, rand, round

-- ceil 向上取整
select ceil(1.1);

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

-- mod 求模（取余数）
select mod(3,4);

-- rand 0-1之间随机数
select rand();

-- round 四舍五入,需要指定保留小数位数
select round(2.3445,2);

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

select round(rand()*1000000, 0);
# 这样会存在一个问题，0.0015647...这样的小数会被处理为1564，导致不足6位，所以还需进行填充
select rpad(round(rand()*1000000, 0),6,'0');

3.日期函数: curdate, curtime, now, year, month, day, date_add, datediff

-- curdate 当前日期
select curdate();
-- curtime 当前时间
select curtime();
-- now 当前日期+时间
select now();
-- year month day 年 月 日
select year(curdate());
select month(curdate());
select day(curdate());
-- date_add 指定间隔后的日期，可以是年、月、日，interval是固定的，后边是变化的
select date_add(now(), interval 70 day);
select date_add(now(), interval 70 year);
-- datediff 日期间的天数差,是第一个日期减去第二个日期，所以若前小后大则会出现负数
# 注意：日期需要用引号引起
select datediff('2024-07-22','2024-12-03');

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

select name, datediff(curdate(),entrydate) as worktime from information
order by worktime desc;

4.流程函数: if, ifnull, case...when...then...else...end

（1）if和ifnull

-- if 判断语句，判断第一个值条件表达式是true还是false，若为true则返回第二个值，为false则返回第三个值
select if(true, 'ok', 'error');
-- ifnull 判断第一个值是否为空，空则返回第二个默认值，非空则返回第一个值
select ifnull('ok', 'default');
select ifnull('', 'default'); # 空字符串不为空
select ifnull(null, 'default'); # 输入null则返回默认值（第一个值也不可以不输入，会报错）

（2）case...when...then...else...end

格式1：CASE [ expr ] WHEN [ val1 ] THEN [res1] ... ELSE [ default ] END

这种格式适用于条件为某个字段下的值等于某一固定值或几个值的情况，下面举个例子

需求：查询emp表的员工姓名和工作地址 (北京/上海 ----> 一线城市 , 其他 ----> 二线城市)

-- case语句适用于对某些字段内的值进行大量条件性的变更，与update的整体更新或加条件的单独更新不同，且update不适用于仅查询的情况，会直接修改数据
update information set workaddress = '一线城市' where workaddress = '北京';
select * from information; # 这种方式只适用于单条件更新，对于多条件则要写更多遍类似语句

-- case语句
select
       name,
       case workaddress when '北京'or'上海' then '一线城市' else '二线城市' end
from information; # 错误❌ 逻辑有问题
# 可以采用多条when来筛选条件
select
       name,
       case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end
from information;

格式2：CASE WHEN [ val1 ] THEN [res1] ... ELSE [ default ] END

这种格式适用于条件不是某一固定值或几个值，而是一个范围，同样举个例子

需求：统计班级各个学员的成绩，展示规则如下：-- >=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 math_grade,
       (case when english >= 85 then '优秀' when english >= 60 then '及格' else '不及格' end) as english_grade,
       (case when chinese >= 85 then '优秀' when chinese >= 60 then '及格' else '不及格' end) as chinese_grade
from score;