前三篇介绍了:初级查询 高级查询 增删改三部分董汇标MINUS：MySQL数据库查询-基础篇zhuanlan.zhihu.com董汇标MINUS：MySQL数据库查询-进阶篇zhuanlan.zhihu.com董汇标MINUS：MySQL数据库之--增删改zhuanlan.zhihu.com
今天这篇整理介绍一下函数部分, 以下是今天涉及的内容,你都认识他们以及知道如何使用吗?
数字函数: FORMAT ABS MOD CEIL FLOOR ROUND EXP......
字符函数:UPPER LOWER CHAR_LENGTH CONCAT INSTR......
日期函数:NOW DATE_FORMAT DATE_ADD DATEDIFF ...
条件函数:IF IFNULL......
数字函数
-- 数学函数1SELECT ABS(-100); --绝对值100
SELECT ROUND(4.62); --四舍五入5
SELECT FLOOR(9.9); --向下舍位9
SELECT CEIL(3.2); --向上强进位4
SELECT POWER(2,3); --幂函数2的3次方8
SELECT LOG(7,3); --log7(3)
SELECT LN(10); --ln10
-- 数学函数2
SELECT SQRT(9);
SELECT PI();
SELECT SIN(1);
SELECT COS(1);
SELECT TAN(1);
SELECT COT(1);
SELECT RADIANS(30); -- 角度转弧度
SELECT SIN(RADIANS(30));
SELECT COS(RADIANS(45));
SELECT TAN(RADIANS(30));
SELECT COT(RADIANS(45));
SELECT DEGREES(1); -- 弧度转角度
日期函数
如何获得系统日期函数呢? 用下面这三种函数NOW()函数能获得系统日期和时间,格式yyyy-mm-dd hh:mm:ss
CURDATE()函数能获得当前系统时间, 格式hh:mm:ss
CURTIME()函数能获得当前系统时间, 格式hh:mm:ss
DATE_FORMAT(日期, 表达式)用来提炼如星期几\第几天\等信息
前三个效果如下:
-- 时间函数
SELECT NOW(),CURDATE(),CURTIME();
-- 查员工那年入职的
SELECT ename ,DATE_FORMAT(hiredate,"%j") FROM t_emp;
案例:
-- 案例:明年你的生日是星期几?
SELECT DATE_FORMAT("2020-11-22","%W");
-- 利用日期函数,查询1981年上半年入职的员工有多少人?
SELECT count(*) from t_emp where
DATE_FORMAT(hiredate,"%Y")=1981 AND DATE_FORMAT(hiredate,"%m")<=6;
日期偏移计算函数
日期函数注意事项: MySQL数据库里,两个日期不能直接加减, 日期也不能与数字加减.
DATE_ADD()函数可以实现日期偏移计算,而且时间单位很灵活
- DATE_ADD( 日期 ,INTERVAL 偏移量 时间单位)
时间单位: day hour year
案例:
-- 时间偏移
SELECT DATE_ADD(NOW(),INTERVAL 400 DAY);
SELECT DATE_ADD("2019-03-02",INTERVAL 500 day);
-- 6个月前的再往前推3天_嵌套查询
SELECT DATE_FORMAT(
DATE_ADD( DATE_ADD( NOW(),INTERVAL -6 MONTH), INTERVAL -3 DAY), "%Y/%m/%d");
两个日期之间计算差距DATEDIFF( 日期, 日期)括号里可以是日期,可以是年
-- 案例:计算2020-02-02 举例建国多少年
SELECT DATEDIFF("2020-02-02","1949-10-01")/365;
字符函数
备注:instar 判断是否出现A,以及他的位置
insert 1,0 意思是在第一刀位置插入,不替换; 改成1 ,替换一个字符,改成2替换两个字符
replace 是前边的那个字符串,把其中的先生替换成女士
练习案例:
-- 字符函数练习:小写名\大写名\名字长度(utf8一个汉字3个字符)\工资连接美元符号\名字中A的位置
SELECT
LOWER(ename) , UPPER(ename), LENGTH(ename),CONCAT(sal,"$") , INSTR(ename,"A")
FROM t_emp;
-- 字符串练习2 插入,第一个1意思是在1个字符的位置处插入;第二个位置的0表示不替换,1替换1个,2替换两个...
SELECT INSERT("你好",1,0,"先生");
-- 字符串练习3 把前边你好先生 的先生二字换为女士
SELECT REPLACE("你好先生","先生","女士");
解释: substr 和 substring 区别在于 3截取到4, 和3开始截取2个字符的差异
练习案例:
-- 练习内容提取
SELECT SUBSTR("你好世界",3,4),SUBSTRING("你好世界",3,2);
-- 练习lpad 电话尾号
SELECT LPAD(SUBSTRING("13810888888",8,4),11,"*");
-- 练习rpad 只保留姓氏
SELECT RPAD(SUBSTRING("李晓娜",1,1),LENGTH("李晓娜")/3,"*");
-- 练习TRIM 去空格
SELECT TRIM(" Hello World ");
条件函数
单一状态的:IF 和 IFNULL
SQL语句中可以利用条件函数来实现编程语言里的条件判断IFNULL(表达式,替换值)IF (表达式,值1,值2)true返回值1,false返回值2
-- 条件函数 SALES部门的员工法礼品A,别的法礼品B
SELECT e.empno, e.ename , d.dname,IF(d.dname='SALES','礼品A','礼品B')
FROM t_emp e join t_dept d on e.deptno=d.deptno;
多种状态的条件语句
CASE WHEN 表达式 THEN 值1 WHEN 表达式2 THEN 值2 ELSE END
SELECT e.empno , e.ename ,
CASE
WHEN d.dname='SALES' THEN 'P1'
WHEN d.dname='ACCOUNTING' THEN 'P2'
WHEN d.dname='RESEARCH' THEN 'P3'
ELSE
'HOME'
END AS place
FROM t_emp e join t_dept d on e.deptno=d.deptno
ORDER BY place;
练习:
-- 公司决定为员工调整基本工资,具体方案如下截图
UPDATE t_emp e
LEFT join t_dept d on e.deptno=d.deptno
LEFT JOIN (SELECT deptno,AVG(sal) AS avgg 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
WHEN d.dname IS NULL THEN e.sal+100
ELSE e.sal
END);
总结: 本次涉及了以下四个常用函数数字函数
字符函数
日期函数
条件函数
另外跑上面代码需要运行原始数据表格,需要的联系我,发给你.
