MySQL学习笔记（六）内置函数

函数

1. 字符串函数

函数	功能
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, start, len)	返回字符串str从start位置起的len个长度的字符串

案例

-- 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('hello mysql', 1, 5);

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

2. 数值函数

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

案例

-- ceil
select ceil(1.2);
-- floor
select floor(1.5);
-- mod
select mod(3, 4);
-- rand
select rand();
-- round
select round(2.342, 2);

-- 案例：通过数据库的函数生成一个六位数的随机验证码，rand生成的可能是0.08734，五位，所以要使用lpad对其补0
select lpad(round(rand()*1000000, 0), 6, 0);

3. 日期函数

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

案例

select curdate();
select curtime();
select now();

select YEAR(now());
select month(now());
select day(now());

select date_add(now(), interval 70 day);
select date_add(now(), interval 70 month);
select date_add(now(), interval 70 year);

select datediff('2021-12-01', '2021-11-01');
select datediff('2021-12-01', '2021-10-01');
select datediff('2021-10-01', '2021-12-01');

-- 查看员工入职时间到目前为止一共多少天
select name, datediff(curdate(), entrydate) as 'entrydays' from emp order by entrydays 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默认值

案例

select if(true, 'Ok', 'Error');
select if(false, 'Ok', 'Error');

select ifnull('Ok', 'Default');
select ifnull('', 'Default');
select ifnull(null, 'Default');

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

-- 首先插入score表并添加数据
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);

-- 查询表中数据，且当分数大于85时输出为优秀，大于60输出为及格，否则输出不及格
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;