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 ) ;
select date_add( now ( ) , interval 100 day ) ;
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
) ;