SQL函数用法及实例

select database();
# MySQL中的函数主要分为以下四类： 字符串函数、数值函数、日期函数、流程函数。
# 1.字符串函数
# 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  world  ');
#  substring : 截取子字符串 (返回从字符串str从start位置起的len个长度的字符串)
select substring('Hello mysql',1, 3);

# 由于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0。比如： 1号员
# 工的工号应该为00001。
# DML Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进
# 行增、删、改操作。
update emp set workno = lpad(workno, 5, '0');

# 2.数值函数
#  ceil：向上取整
select ceil(1.1);
# floor:向下取整
select floor(1.1);
# mod：取模(MOD(x,y)返回x/y的模)
select mod(6, 4);
# rand：获取随机数(0-1)；
select rand();
# round:四舍五入（ROUND(x,y) 求参数x的四舍五入的值，保留y位小数）
select round(3.445, 2);

# 通过数据库的函数，生成一个六位数的随机验证码。
# 思路： 获取随机数可以通过rand()函数，但是获取出来的随机数是在0-1之间的，所以可以在其基础
# 上乘以1000000，然后舍弃小数部分，如果长度不足6位，补0
select lpad(round(rand()*1000000, 0), 6, '0');

# 3.日期函数
#  curdate：当前日期
select curdate();
# curtime：当前时间
select curtime();
# now：当前日期和时间
select now();
#  YEAR , MONTH , DAY：当前年、月、日
select YEAR(now());
select MONTH(now());
select DAY(now());
# date_add：增加指定的时间间隔
# DATE_ADD(date, INTERVAL expr type)
# 返回一个日期/时间值加上一个时间间隔expr后的时间值
select date_add(now(), interval 70 YEAR );
# DATEDIFF(date1,date2)返回起始时间date1 和 结束时间date2之间的天数
select datediff('2021-10-01', '2021-12-01');

# 查询所有员工的入职天数，并根据入职天数倒序排序。
select name, datediff(now() , entrydate) as '入职天数'
from emp
order by 入职天数 desc;

# 4.流程函数
# 流程函数也是很常用的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率。
# IF(value , t , f)
# 如果value为true，则返回t，否则返回f
# IFNULL(value1 , value2)
# 如果value1不为空，返回value1，否则返回value2
# CASE WHEN [ val1 ] THEN [res1] ...
# ELSE [ default ] END
# 如果val1为true，返回res1，... 否则返回default默认值
# CASE [ expr ] WHEN [ val1 ] THEN
# [res1] ... ELSE [ default ] END
# 如果expr的值等于val1，返回res1，... 否则返回default默认值

# A. if
select if(false, 'ok','error');
# B. ifnull
select ifnull('Ok','Default');
select ifnull('','Default');
select ifnull(null,'Default');
#  case when then else end
# 需求: 查询emp表的员工姓名和工作地址 (北京/上海 ----> 一线城市 , 其他 ----> 二线城市)
select name, workaddress,
       (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 )
'数学',
(case when english >= 85 then '优秀' when english >=60 then '及格' else '不及格'
end ) '英语',
(case when chinese >= 85 then '优秀' when chinese >=60 then '及格' else '不及格'
end ) '语文'
from score;