MySQL的函数
- 像编程语言利用函数封装业务功能一样，数据库也把-一些复杂的功能封装到函数里，供使用者调用
- 函数分类：数字函数、字符函数、日期函数、条件函数
一、数字函数
-
LN是以常数e为底数
二、日期函数
获取系统时间函数
-
NOW()函数能获得系统日期和时间，格式yyy-MM-dd hh:mm:ss -
CURDATE()函数能获得当前系统日期，格式yyyy-MM-dd -
CURTIME()函数能获得当前系统时间，格式hh:mm:ss
日期格式化函数
-
DATE_FORMAT()函数用于格式化日期，返回用户想要的日期格式
DATE_FORMAT(日期, 表达式)
SELECT ename, DATE_FORMAT (hiredate, "Y") FROM t_ emp;
-
%r：12小时制的时间 -
%T：24小时制的时间
练习
- 利用日期函数，查询明年你的生日是星期几?
select date_format("2019-6-20","%W");
- 利用日期函数，查询1981年上半年入职的员工有多少人?
select count(*) from t_emp
where date_format(hiredate,"%Y")=1981
and date_format(hiredate,"%m")<=6;
日期计算的注意事项
- MySQL数据库里面，两个日期不能直接加减，日期也不能与数字加减
SELECT hiredate+1 FROM t emp;
日期偏移计算
-
DATE ADD()函数可以实现日期的偏移计算，而且时间单位很灵活
DATE ADD(日期，INTERVAL 偏移量 时间单位) - 例子：
SELECT DATE_ADD(NOW(), INTERVAL 15 DAY) ;
SELECT DATE_ADD(NOW(), INTERVAL -300 MINUTE) ;
- 练习
//100天后的日期
select date_add(now(), interval 100 day);
//6个月零3天之前的时间
select
date_format(
date_add(date_add(now(),interval -6 month),interval -3 day),"%Y/%m/%d");
计算日期之间相隔的天数
-
DATEDIFF()函数用来计算两个日期之间相差的天数
DATEDIFF(日期, 日期)
三、字符函数
-
INSERT("你好", 1, 0, "先生")表示：将先生插入到你好前面，第一个1表示插入的位置，第二个0表示替换字符偏移量，0表示不替换，1表示替换一个 - 练习
select
lower(ename),upper(ename),length(ename),
concat(sal,"$"),instr(ename,"A")
from t_emp;
select insert("先生",1,0,"你好"),insert("先生",1,1,"你好");-
select replace("你好先生","先生","女士");
-
SUBSTR(STRING, START, END)：其中的END是指结尾位置 -
SUBSTRING(STRING, START, OFFSET)：其中的OFFSET是指偏移量 - 例子
select
substr("你好世界",3,4),substring("你好世界",3,2),
lpad(substring("13312341234",8,4),11,"*"),
rpad(substring("李晓娜",1,1),length("李晓娜")/3,"*"),
trim(" Hello World");
四、条件函数
- SQL语句中可以利用条件函数来实现编程语言里的条件判断
IFNULL(表达式，值)
IF(表达式，值1，值2)：结果为TRUE返回第一个值，结果为FALSE范围第二个值
练习
- 中秋节公司发放礼品，SALES部门发放礼品A，其余部门发放礼品B，打印每名员工获得的礼品。
select
e.ename,d.dname,if(d.dname="SALES","礼品A","礼品B") gift
from t_emp e join t_dept d on e.deptno=d.deptno;
条件语句
- 复杂的条件判断可以用条件语句来实现，比IF语句功能更强大
CASE
WHEN 表达式 THEN 值1
WHEN 表达式 THEN 值2
...
ELSE 值N
END
练习
- 公司年庆决定组织员工集体旅游，每个部门旅游目的地是不同的。SALES部门去P1地点，ACCOUNTING部门去P2地点, RESEARCH部门去P3地点，查询每名员工的旅行地点。
SELECT e.ename,d.dname,
CASE
WHEN d.dname="SALES" THEN "P1"
WHEN d.dname="ACCOUNTING" THEN "P2"
WHEN d.dname="RESEARCH" THEN "P3"
END AS place
FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno;
-
公司决定为员工调整基本工资，具体调整方案如下:
UPDATE t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno
LEFT JOIN (SELECT deptno,AVG(sal) avg FROM t_emp GROUP BY deptno) t
ON e.deptno=t.deptno
SET e.sal=(
CASE
WHEN d.dname="SALES" AND DATEDIFF(NOW(),e.hiredate)/365>=20
THEN e.sal*1.1
WHEN d.dname="SALES" AND DATEDIFF(NOW(),e.hiredate)/365<20
THEN e.sal*1.05
WHEN d.dname="ACCOUNTING" THEN e.sal+300
WHEN d.dname="RESEARCH" AND e.sal<t.avg THEN e.sal+200
WHEN d.dname IS NULL THEN e.sal+100
ELSE e.sal
END
);
