mysql学习笔记-常用函数

一、字符串函数

常用函数：
concats(str1,str2，……strn)  字符串拼接
LOWER(str)  字符串转小写
UPPER(str)  字符串转大写
LPAD(str,len,padstr)  左填充，用字符串padstr对str的左边填充，达到n个字符串长度
RPAD(str,len,padstr)  右填充，用字符串padstr对str的右边填充，达到n个字符串长度
TRIM(str) 去掉字符串头部和尾部的空格
SUBSTRING(str,start,len) 返回str从start开始的len个长度的字符串  类似excel mid()

-- concat
select concat('helo','-','北京')

-- lower
select lower('BEIJING')

-- upper()
select upper('beijing')

-- lpad
select lpad('wuhan',10,'hb')
-- result  hbhbhwuhan

-- rpan
select rpad('wuhan',10,'wc')
-- result  wuhanwcwcw

-- trim
select trim('   wo ai zhong guo   ')
-- result:wo ai zhong guo

-- substring
select substring('武汉国立大学', 1, 2)

练习1 对所有员工编号设置为8位，不足8位在前面补0，继续使用DQL学习的emp表格练习。
select * from emp;
update emp set workno=lpad(workno,8,'0');


二、数值函数
常用函数：
CEIL(X)     向上取整
FLOOR(X)    向下取整
MOD(N,M)    返回X/Y的模
RAND()      返回0-1内的随机数
ROUND(X,D)    求参数x的四舍五入，保留D位小数

-- ceil
select ceil(2.28);
# result 3

-- floor
select floor(2.28);
# result 2

-- mod
select mod(10,3);
# result 1

-- rand
select rand();
# result 0.8357684918081094

-- round
select round(3.1415926,2);
# result 3.14

-- 练习1、返回一个6位数随即验证码

select lpad(round(rand()*1000000),6,'0');
# result 098583

三、日期函数
常用函数：
CURDATE()   返回当前日期
CURTIME()        返回当前时间
NOW()                返回当前日期加时间
YEAR(date)  获取指定date的年份
MONTH(date)    获取指定date的月份
DAY(date)   获取指定date的日期
DATE_ADD(date,INTERVAL expr unit)    返回一个指定日期/时间值的基础上，加上一个时间间隔expr后的时间值
DATEDIFF(expr1,expr2)                                 返回起始时间date1和结束时间date2质检的天数

-- CURDATE
SELECT CURDATE();
# 2022-07-12

-- CURTIME
SELECT CURTIME();
# 12:39:15

-- NOW
SELECT NOW();
# 2022-07-12 12:38:42

-- YEAR, MONTH, DAY
SELECT YEAR(NOW());
# 2022

SELECT MONTH(NOW());
# 7

SELECT DAY(NOW());
# 12

-- DATE_ADD 
# 往后推迟70天
SELECT DATE_ADD(NOW(),INTERVAL 70 DAY);

# 往后推迟70月
SELECT DATE_ADD(NOW(),INTERVAL 70 MONTH);

# 往后退出70年
SELECT DATE_ADD(NOW(),INTERVAL 70 YEAR);
#result 2092-07-12 12:43:16

-- DATEDIFF,(date1-date2)

SELECT DATEDIFF('2021-12-01','2022-12-01');
# result -365
SELECT DATEDIFF('2022-12-01','2021-12-01');
# result 365


练习1、查询所有员工入职天数，并根据入职天数排序

select name,datediff(curdate(),entydate) as 'entydays' from emp order by entydays desc ;


四、流程函数
常用函数：
IF(VALUE,T,F)          如果value为true，返回T，否则返回F
IFNULL(expr1,expr2)    如果expr1不为空，返回expr1，否则返回expr2
CASE WHEN [val1] THEN [RES1] ……ELSE[default] END   
如果val1为true，返回res1，否则返回defauit默认值

CASE [expr] WHEN [val1] THEN [res1] …… ELSE [default] END
如果expr值等于val1，返回res1，否则返回defauit默认值  

-- if
select if (true,'答对了','不知道');
# result 答对了

select if(false,'答错了','加油');
# result 加油

-- ifnull
select ifnull("",'空的');
select ifnull("da",'空的');
select ifnull(null,'空的');

-- case when then else end
-- 查询员工姓名和员工地址，如果员工在北京上海，展示一线城市，其他二线城市
select 
        name,
        (case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '是否一线'
from emp;


练习1、统计公司员工年龄，如果大于30岁中年，大于20岁青年，否则 少年（可多字段判断）

select 
        name,
        (case  when age>30 then '中年' when age>20 then '青年' else '少年' end) as '青少年统计',
        (case  when age>30 then 'zhognnian' when age>20 then 'qn' else 'sn' end) as 'enght'
from emp;