sql学习-函数

#函数
#字符串函数
-- LOWER
SELECT LOWER('hello');

--UPPER
SELECT UPPER('Hello');

--LPAD(str,len,padstr)
SELECT LPAD('01',5,'-');

--RPAD(str,len,padstr)
SELECT RPAD('01',5,'-');

--TRIM([remstr FROM] str)
SELECT TRIM(' Hello Mysql ');

--SUBSTRING(str FROM pos FOR len)
SELECT SUBSTRING('Hello MySQL',1,5);

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

#数值函数
-- CEIL(X)
SELECT CEIL(1.1);
--FLOOR(X)
SELECT FLOOR(1.9);

--MOD(N,M)
SELECT MOD(3,4);

--RAND()
SELECT rand();

--ROUND(X,D)
SELECT ROUND(2.34,2);

--通过数据库的函数，生成一个六位数的随机验证码
SELECT lpad(round(RAND()*1000000,0),6,'0');

#日期函数
--CURDATE()
SELECT CURDATE();
-- CURRENTTIME
SELECT CURTIME();
-- NOW()
SELECT NOW();

--YEAR(date),MONTH(date),DAY(date)
SELECT YEAR(NOW());

SELECT MONTH(NOW());

SELECT DAY(NOW());

--DATE_ADD(date,INTERVAL expr unit)
SELECT DATE_ADD(NOW(),INTERVAL 70 YEAR);

--DATEDIFF(expr1,expr2)
SELECT DATEDIFF('2021-12-01','2021-11-01'); 

--系例:查询所有员工的入职天数，并根据入职天数倒序排序
SELECT name, datediff(CURDATE(),entrydate) as 'entrydays' FROM emp ORDER BY entrydays DESC;

#流程函数
--if
SELECT IF(TRUE,'OK','Error');

--IFNULL(expr1,expr2)
SELECT IFNULL('ok','Default');

SELECT IFNULL('','Default');

SELECT IFNULL(NULL,'Default');

-- case when then ELSE END
--查询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,68,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;