17-基础SQL-函数-日期函数

什么是函数：是指一段可以直接被另一段程序调用的程序或代码

MySQL的函数主要包括：字符串函数、数值函数、日期函数、流程函数。

日期函数

常见的日期函数如下：

CURDATE() 演示：返回当前日期

SELECT CURDATE();

CURTIME() 演示：返回当前时间

SELECT CURTIME();

NOW() 演示：返回当前日期和时间

SELECT NOW();

YEAR(date)、MONTH(date)、DAY(date) 演示：获取"2023-11-24 16:00:15" 中对应的年、月、日

SELECT YEAR("2023-11-24 16:00:15") AS year, MONTH("2023-11-24 16:00:15") AS month, DAY("2023-11-24 16:00:15") AS day;

DATE_ADD(date,INTERVAL expr type) 演示：获取"2023-11-24 16:00:15" ，往后的1天、1个月、1年的时间

SELECT "2023-11-24 16:00:15",
DATE_ADD("2023-11-24 16:00:15",INTERVAL 1 DAY) AS day,
DATE_ADD("2023-11-24 16:00:15",INTERVAL 1 MONTH) AS month,
DATE_ADD("2023-11-24 16:00:15",INTERVAL 1 YEAR) AS year;

DATE_SUB(date,INTERVAL expr type) 演示：获取"2023-11-24 16:00:15" ，往前的1天、1个月、1年的时间

SELECT "2023-11-24 16:00:15",
DATE_SUB("2023-11-24 16:00:15",INTERVAL 1 DAY) AS day,
DATE_SUB("2023-11-24 16:00:15",INTERVAL 1 MONTH) AS month,
DATE_SUB("2023-11-24 16:00:15",INTERVAL 1 YEAR) AS year

DATEDIFF(date1,date2) 演示：获取"2023-11-24"到"1996-12-31"之间的天数（第一个时间 减去 第二个时间）

SELECT DATEDIFF("2023-11-24","1996-12-31");

案例：创建一个 emp 员工表，添加一些员工数据

CREATE TABLE emp(
    id int comment "编号",
    workno varchar(10) comment "工号",
    name varchar(10) comment "姓名",
    gender char(1) comment "性别",
    age tinyint unsigned comment "年龄",
    idcard char(18) comment "身份证号",
    workaddress varchar(50) comment "工作地址",
    entrydate date comment "入职时间"
) comment "员工表";

INSERT INTO emp (id,workno,name,gender,age,idcard,workaddress,entrydate)
VALUES    
(1,"1","员工1","男","23","123456789000000000","江西","2022-8-31"),
(2,"2","员工2","男","26","123456789000000001","河北","2022-12-31"),
(3,"3","员工3","女","23","123456789000000002","河北","2022-5-31"),
(4,"4","员工4","女","24","123456789000000003","山西","2022-3-31"),
(5,"5","员工5","男","23","123456789000000000","江西","2022-8-21"),
(6,"6","员工6","男","26","123456789000000001","河北","2022-12-21"),
(7,"7","员工7","女","23","123456789000000002","河北","2022-5-21"),
(8,"8","员工8","女","24","123456789000000003","山西","2022-3-21"),
(9,"9","员工9","男","23","123456789000000000","江西","2022-8-11"),
(10,"10","员工10","男","26","123456789000000001","河北","2022-12-11"),
(11,"11","员工11","女","23","123456789000000002","河北","2022-5-11"),
(12,"12","员工12","女","24","123456789000000003","山西","2022-3-11")

1）查询所有员工的入职天数，并根据入职天数，倒序排序

SELECT *,DATEDIFF(NOW(),entrydate) AS "entrydays" FROM emp ORDER BY entrydays DESC;