数据库学习之MySQL (十）—— 日期和时间函数 NOW STR_TO_DATE DATE_FORMAT 流程控制函数 IF IFNULL CASE

MySQL学习专栏 正在持续更新中：）

日期和时间函数 NOW STR_TO_DATE DATE_FORMAT

函数很多 不过都有规律可记，也不难

还有一些具体从一串日期中具体提取的函数，函数名都是简单的英语：

YEAR(date)
MONTH(date)
DAY(date)

HOUR(time)
MINUTE(time)
SECOND(time)
1
2
3
4
5
6
7

形式上，date 是2020-1-5 time是23:34:22，注意是形式。下面是实验结果。

在上面六个函数后面加个NAME，就可以获得名字。

很明显，如果给的参数缺了（HOUR需要时间 却只给了日期），也不会返回NULL，应该是未给参数都默认补全为0了。

STR_TO_DATE: str to date 字符串转日期(格式），他需要提供解析格式，再输出正常日期的格式，我们看下面例子：
SELECT STR_TO_DATE('1-5-2019','%m-%d-%y'); 这个类似正则匹配，
SELECT STR_TO_DATE('1, 5, 2019','%m, %d, %y');

DATE_FORMAT date-format 将日期格式化成你想要的样子。

下面是格式符号的列表，不常用的都删掉了：

流程控制函数 IF IFNULL CASE

IF 函数和IFNULL 类似三目运算符，达到条件 返回啥，不达到条件又返回啥。注意IF()可以看做一个表达式。

SELECT 
  `last_name`,
  IF(
    `commission_pct`,
    '有奖金',
    '没奖金'
  ) AS 奖金结果
FROM
  employees ;
1
2
3
4
5
6
7
8
9

对IF而言 所谓条件就是 是否为真
对IFNULL而言 条件就是 是否为NULL。

案例 老员工回馈问题 在employees中，获得所有人的基本工资，入职时间，奖金率，资历。认为入职时间在04年以前的，资历是老员工，记为1，04以后为新员工，记为0。另外，不允许表格中出现NULL，排序按奖金率降序排列。

SELECT 
  `salary` 基本工资,
  IFNULL(`commission_pct`, 0) 奖金率,
  IFNULL(`hiredate`, 0) 入职时间,
  IF(YEAR(`hiredate`)<2004, 1, 0) 资历
FROM employees
ORDER BY 奖金率 DESC;
1
2
3
4
5
6
7

IFNULL就是为了避免NULL的出现导致计算错误，而IF用于判断年份资历。

CASE
》第一种方式
CASE可以作为一个表达式语句，就和IF() IFNULL()一样的，作为SELECT的组成部分打印出来。尝试下面的案例, 也就是上面案例改造了一下：

USE data1;
SELECT 
  `salary` 基本工资,
  IFNULL(`commission_pct`, 0) 奖金率,
  IFNULL(`hiredate`, 0) 入职时间,
  IF(YEAR(`hiredate`)<2004, 1, 0) 资历,
  CASE IFNULL(`commission_pct`, 0)
    WHEN 0.40 THEN salary*(1+0.6)
    WHEN 0.35 THEN salary*(1+0.4)
    WHEN 0.30 THEN salary*(1+0.2)
    ELSE salary*(1+0.0)
    END AS 第二种工资算法结果
FROM employees
ORDER BY 奖金率 DESC;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

蓝框起来就作为一个语句使用，你可以给个别名“第二种工资算法结果”

结果大概这个样子：

那问题来了，如果我希望是一个区间判断呢？比如，入职时间在2000~2004年的员工奖金加成为0.6，明显上面的方式只能判断时间=2000 也即是一个定值。

》第二种方式
案例 老员工回馈问题：比起之前以2004年，一刀切认为老员工还是新员工，这里我们细分一下（类似采样频率提高 量化精度提升），在96年以前老员工回馈加成为0.6，97~02年回馈加成为0.4, etc

根据公式：salary*(1+commission_pct+回馈加成) = 最终工资
计算所有人的最终工资，降序排列输出。
栏目：名字 入职时间 奖金率 最终工资

最终效果如图：

USE data1;
SELECT 
  CONCAT(`last_name`,'-',`first_name`) 名字,
  `salary` 基本工资,
  IFNULL(`commission_pct`, 0) 奖金率,
  IFNULL(`hiredate`, 0) 入职时间,
  CASE
    WHEN YEAR(`hiredate`) < 1996 
    THEN `salary` * (1+0.6+IFNULL(`commission_pct`, 0)) 
    
    WHEN YEAR(`hiredate`) BETWEEN 1997 AND 2000 
    THEN `salary` * (1+0.4+IFNULL(`commission_pct`, 0)) 
    
    WHEN YEAR(`hiredate`) BETWEEN 2001 AND 2010 
    THEN `salary` * (1+0.2+IFNULL(`commission_pct`, 0))
    
    WHEN YEAR(`hiredate`) BETWEEN 2011 AND 2014 
    THEN `salary` * (1+0.1+IFNULL(`commission_pct`, 0))
    
    ELSE `salary` * (1+0.0+IFNULL(`commission_pct`, 0))
  END AS 最终工资 
  
FROM
  employees 
  
ORDER BY 最终工资 DESC ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

下章预告

目前我们学习了几种所谓单行函数：
数学函数
字符串处理函数
时间日期函数
流程控制函数，
说白了，算一个值出一个结果的就是单行函数，
然而数据库的重点在于数理统计，意味着我们要处理一组数，返回一些统计量（statistics）（如平均数（average） 中位数（median））
那么这样的统计函数称为统计函数（别名 组函数 多组函数）

下一站：数据库学习之MySQL (十一）——统计函数