SQL日期时间函数面试必会考点

数据进行查询，报表展开统计，安全实施加密，这些MySQL函数于实际工作当中每日都得使用。面试官最为爱问的便是你可不可以灵活运用这些函数去解决真实存在的问题。将它们掌握住，你的SQL水平能够提升到一个新的层级。

SELECT ename, birthday
FROM t_employee
WHERE MONTH(CURDATE()) = MONTH(birthday);

SELECT ename, hiredate
FROM t_employee
WHERE DATEDIFF(CURDATE(), hiredate) > 365 * 5;

生日提醒和日期计算

WHERE hiredate <= DATE_SUB(CURDATE(), INTERVAL 5 YEAR);

查找在本月过生日的员工，这属于HR系统当中的常见需求，运用MONTH函数去提取生日月份，再与当前月份进行比较，便能轻易达成。比如说，SELECT FROM employees WHERE MONTH(birthday) = MONTH(CURDATE)，如此便能够找出本月过生日的员工。

SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H:%i:%s') AS 当前时间;

SELECT TIMEDIFF('12:00:00', CURTIME()) AS 距离放学;

计算员工入职的年限或者工龄之际，DATEDIFF函数着实相当实用，然而哦直接拿天数去除以365是不够精准精确的，更为优良的办法是采用DATE_ADD函数，比如说DATE_ADD(hiredate, INTERVAL 5 YEAR)能够判断员工是不是有入职满了5年。

-- 创建用户表
CREATE TABLE t_user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(20),
    password VARCHAR(100)
);
-- 插入时加密
INSERT INTO t_user VALUES(NULL, 'chai', MD5('123456'));
-- 登录验证
SELECT * FROM t_user WHERE username='chai' AND password=MD5('123456');

报表里时常得对日期显示予以格式化，DATE_FORMAT函数能够将2026 - 04 - 16转变成为2026年04月16日这般的格式，于财务月报以及销售周报当中，使日期格式保持统一会让报表显得更为专业。

数据加密守护安全

SELECT DATABASE(), VERSION(), USER();

用户登录的场景之中，密码是不能够以明文的形式进行存储的。假设在用户注册之际，所输入的密码为123456，那么在数据库里面，应该存储的是它经过加密之后的值。MySQL所提供的SHA2函数，要比MD5安全好多好多，原因在于MD5已经被彩虹表给破解掉。

留心，MySQL 8.0已然将PASSWORD函数给移除掉了。于实际的开发进程当中，在进行注册操作时，会调用INSERT INTO users VALUES (SHA2(123456, 256))来存储密文。而在登录之际，会把用户所输入的密码运用同样的方式予以加密，之后再与数据库展开比对。

SELECT 
    ename,
    salary,
    CASE 
        WHEN salary > 20000 THEN '羡慕级别'
        WHEN salary > 15000 THEN '努力级别'
        WHEN salary > 10000 THEN '平均级别'
        ELSE '保底级别'
    END AS 等级
FROM t_employee;

当前环境的了解借助于系统信息函数，编写自动化脚本之际，查看当前数据库名运用SELECT DATABASE，确认MySQL版本号得用SELECT VERSION，调试跨环境问题之时，这些函数极具用处。

SELECT 
    ename,
    salary,
    commission_pct,
    salary + salary * IFNULL(commission_pct, 0) AS 实发工资
FROM t_employee;

条件判断实现分支逻辑

SELECT 
    ename,
    work_place,
    CASE CHAR_LENGTH(work_place) - CHAR_LENGTH(REPLACE(work_place, ',', '')) + 1
        WHEN 1 THEN '只在本地工作'
        WHEN 2 THEN '两地奔波'
        WHEN 3 THEN '三地流动'
        ELSE '频繁出差'
    END AS 出差情况
FROM t_employee;

处理NULL值之际，IFNULL函数是万万不可少的。计算员工实发工资之时，要是commission_pct字段设为NULL，那就按0来计算：SELECT salary + IFNULL(commission_pct, 0) FROM employees。不然的话，计算结果同样会变为NULL。

SELECT COUNT(*) FROM t_employee;
SELECT COUNT(1) FROM t_employee;
SELECT COUNT(eid) FROM t_employee;   -- 如果eid为主键，结果相同
SELECT COUNT(commission_pct) FROM t_employee;  -- 只统计有奖金的员工数

SELECT 
    AVG(salary) AS 平均薪资,
    MAX(salary) AS 最高薪资,
    MIN(salary) AS 最低薪资,
    SUM(salary) AS 总薪资
FROM t_employee;

把IF函数拿来做比较，就好比编程里头的那个三元运算符类似，它能够用来判断员工是不是满了、足了18岁，具体的写法是这样的：SELECT IF(age>=18, 成年, 未成年) FROM employees，不过要留意的是，IF它仅仅只能处理那种或者是这边、或者是那边二选其一的场景状况，要是存在好几个分支条件的话，那就必须得使用CASE才行。

SELECT 
    did,
    ROUND(AVG(salary), 2) AS 部门平均薪资
FROM t_employee
GROUP BY did;

聚合函数实现分组统计

函数名(参数) OVER (
    [PARTITION BY 列名]   -- 分组
    [ORDER BY 列名]       -- 排序
    [ROWS BETWEEN  AND ]  -- 定义窗口范围
)

被聚合为一行的多行数据是由分组函数进行返回，COUNT用于统计行数，SUM用来计算总和，AVG是求平均值，MAX和MIN则是找最大最小值，要留意COUNT()包含NULL行，COUNT(列名)会将NULL跳过。

拿来跟GROUP BY一块组合达成分组统计方面的操作。就好比依照部门去统计平均薪资那样：SELECT dept_id, AVG(salary) FROM employees GROUP BY dept_id。每且个部门都会返回一行对应的结果，分组函数是在组内各自进行计算的。

要查询出每个部门之中薪资处于最高以及最低水平的员工相关信息，是能够运用子查询或者窗口函数来达成的。由于聚合函数没办法直接返回职工名字，原因在于名字并不在GROUP BY当中。在这种情形之下，窗口函数会显得更为合适。

SELECT 
    ROW_NUMBER() OVER() AS rn,
    ename,
    salary
FROM t_employee
WHERE salary BETWEEN 8000 AND 10000;

窗口函数数据分析神器

SELECT 
    ename,
    did,
    salary,
    ROW_NUMBER() OVER(PARTITION BY did ORDER BY salary) AS row_num,
    RANK() OVER(PARTITION BY did ORDER BY salary) AS rank_num,
    DENSE_RANK() OVER(PARTITION BY did ORDER BY salary) AS dense_rank_num
FROM t_employee
WHERE gender = '女';

MySQL 8.0所引入的强力武器是窗口函数，它不会使结果集行数减少，会针对每一行开展诸如排序、前后行访问等操作，RANK进行的是跳跃排序，就像1,1,3这样排序，DENSE_RANK进行的是密集排序表现为1,1,2，ROW_NUMBER进行的是连续编号呈现为1,2,3。

SELECT *
FROM (
    SELECT 
        ename,
        did,
        salary,
        DENSE_RANK() OVER(PARTITION BY did ORDER BY salary DESC) AS dr
    FROM t_employee
) t
WHERE dr <= 3;

SELECT *
FROM (
    SELECT 
        ename,
        did,
        salary,
        DENSE_RANK() OVER(ORDER BY salary DESC) AS dr
    FROM t_employee
) t
WHERE dr <= 3;

对于查询每个部门之中薪资处于前三位置的员工而言，选用DENSE_RANK是最为合适的。这是由于并列第一的情况并不会将第二名挤掉：从(SELECT name, salary, DENSE_RANK OVER(PARTITION BY dept_id ORDER BY salary DESC) rk FROM employees) t之中选取满足rk条件的所有内容。

SELECT 
    ename,
    salary,
    LAG(ename, 1, '-') OVER(ORDER BY salary) AS '上一位姓名',
    LAG(salary, 1, 0) OVER(ORDER BY salary) AS '上一位薪资',
    LEAD(ename) OVER(ORDER BY salary) AS '下一位姓名',
    LEAD(salary) OVER(ORDER BY salary) AS '下一位薪资',
    FIRST_VALUE(salary) OVER(ORDER BY salary) AS '首位薪资',
    LAST_VALUE(ename) OVER(ORDER BY salary 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS '末位姓名'
FROM t_employee;

LEAF和LAG对前后行数据进行访问，例如查看员工薪资相较于上个月增长的幅度：SELECT name, salary, salary - LAG(salary) OVER(ORDER BY month) FROM salary_history，这对于环比分析而言极为便利。

可将聚合函数用作窗口函数，以此同时获取分组值以及整体值，来计算部门平均薪资与全公司平均薪资之间的差值，针对此有如下语句，SELECT dept_id，AVG(salary) OVER(PARTITION BY dept_id) - AVG(salary) OVER() FROM employees。

SELECT DISTINCT
    did,
    AVG(salary) OVER(PARTITION BY did) AS 部门平均,
    AVG(salary) OVER() AS 公司平均,
    ROUND(AVG(salary) OVER(PARTITION BY did) - AVG(salary) OVER(), 2) AS 差值
FROM t_employee;

函数组合实战应用

在实际开展工作期间，常常会有需要将多种不一样的函数进行综合运用的时候。举例来说，像是对日期进行格式化处理，并且还要判定是否属于周末这种情况：SELECT IF(DAYOFWEEK(date) IN(1,7), 周末, 工作日) FROM orders。首先得以将能够处理日期的函数所产生的结果传递给用于条件判断的函数。

进行数据清洗之际，字符串与条件判断相互配合，针对用户所输入的电话号码，借助TRIM去除其中的空格，接着运用IFNULL处理空值，通过SUBSTRING提取身份证里的出生日期，随后利用STR_TO_DATE将其转换为日期类型。

SQL并非单纯的取数工具，而是具备完备功能的数据处理语言，碰到复杂需求时，先思考究否能用MySQL函数予以解决，如此才能大量精简应用层代码，掌握这些函数之后，你的SQL水平会远远超越那些仅会编写SELECT语句的人。

哪个MySQL函数，是你于实际工作当中曾运用来化解棘手业务问题的呢？欢迎在评论区将你的实战经验予以分享，同时也别忘记点赞收藏，以便让更多SQL学习者能够看到。