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

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

文章目录

日期和时间函数 NOW STR_TO_DATE DATE_FORMAT

流程控制函数 IF IFNULL CASE

下章预告

日期和时间函数 NOW STR_TO_DATE DATE_FORMAT

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

名称

英文

中文

返回值

NOW(x)

now

现在(时间)

时间日期一起

CURDATE()

current date

当前日期

当前具体日期

CURTIME()

current time

当前时间

当前具体时间

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

YEAR(date)

MONTH(date)

DAY(date)

HOUR(time)

MINUTE(time)

SECOND(time)

形式上，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 将日期格式化成你想要的样子。

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

名称

用法

举例

％Y

四位数的年

2020

%y

两位数的年

20 (2020)

％m

月

01～12

%M

月

英语形式 eg.January

％d

日

01～31

-

-

-

%H

24小时制小时

00～24

%h

12小时制小时

00~12

％i

分钟

00～59

％S

一分钟钟的第几秒

00～59

流程控制函数 IF IFNULL CASE

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

SELECT

`last_name`,

IF(

`commission_pct`,

'有奖金',

'没奖金'

) AS 奖金结果

FROM

employees ;

对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;

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;

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

结果大概这个样子：

那问题来了，如果我希望是一个区间判断呢？比如，入职时间在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 ;

下章预告

目前我们学习了几种所谓单行函数：

数学函数

字符串处理函数

时间日期函数

流程控制函数，

说白了，算一个值出一个结果的就是单行函数，

然而数据库的重点在于数理统计，意味着我们要处理一组数，返回一些统计量(statistics)(如平均数(average) 中位数(median))

那么这样的统计函数称为统计函数(别名 组函数 多组函数)

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