MySQL学习笔记（二） -- 日期

本文旨在自用，记录之前学习MySQL不知道的小Tips，非系统学习。

---

---

日期

1. 日期范围

可用比较运算符比较日期，以‘<’为例：

SELECT * FROM userinfo WHERE birth < '1998-01-01'

结果如下：

2. 日期信息提取

SELECT
	birth,
	-- DAY
	DAY(birth) AS 日期, DAYNAME(birth) AS 星期, DAYOFWEEK(birth) AS 星期（星期天1，ODBC标准）,
	DAYOFMONTH(birth) AS 月日, DAYOFYEAR(birth) AS 年日,
	-- MONTH
	MONTH(birth) AS 月, MONTHNAME(birth) AS 月份名,
	-- YEAR 
	YEAR(birth) AS 年份, YEARWEEK(birth) AS 年周（周日起）, YEARWEEK(birth,1) AS 年周（周一起）,
	WEEK(birth) AS 年周（周日起）, WEEKDAY(birth) AS 星期（星期一0）
FROM userinfo ORDER BY birth

结果如下：
其中，YEARWEEK(date,mode)中：

模式	每周第一天是星期几	返回值范围	第一周是怎么计算的
0	Sunday	0-53	遇到本年的第一个星期天开始，是第一周。前面的计算为第0周
1	Monday	0-53	假如第一周能超过3天，那么计算为本年的第一周。否则为第0周
2	Sunday	1-53	遇到本年的第一个星期天开始，是第一周。前面的计算为上年度的第5x周
3	Monday	1-53	若第一周能超过3天，那么计算为本年的第一周。否则为上年度的第5x周
4	Sunday	0-53	假如第一周能超过3天，那么计算为本年的第一周。否则为第0周
5	Monday	0-53	遇到本年的第一个星期一开始，是第一周。前面的计算为第0周
6	Sunday	1-53	若第一周能超过3天，那么计算为本年的第一周。否则为上年度的第5x周
7	Monday	1-53	遇到本年的第一个星期一开始，是第一周。前面的计算为上年度的第5x周

3. 日期加减

日期的加减可用于时间上下限的计算。

3.1 日期减法

可用于计算年龄、到期时间等。

SELECT
	id, CONCAT(lastname,' ',firstname) AS 中国姓名, birth, -- CONCAT 字符拼接
	TIMESTAMPDIFF(YEAR, birth, NOW()) AS age, -- 年份差值
	NOW() AS 当前时间,
	CURDATE() AS 当前日期,
	TO_DAYS(CURDATE()) - TO_DAYS(birth) AS 天数差值,
	TO_SECONDS(CURDATE()) - TO_SECONDS(birth) AS 秒数差值,
	DATE_SUB(birth, INTERVAL 10 YEAR) AS 出生前10年的日期,
	DATE_SUB(birth, INTERVAL 10 MONTH) AS 出生前10个月的日期
FROM
	userinfo

3.2 日期加法

SELECT
	id,CONCAT(lastname, ' ', firstname) AS 中国姓名,birth,
	DATE_ADD(birth, INTERVAL 10 YEAR) AS 10岁的日期,
	DATE_ADD(birth, INTERVAL 10 MONTH) AS 10个月的日期
FROM
	userinfo

4. TIMESTAMP

timestamp可以自动更新和自动初始化。想要列表中有两个timestamp，可以组合的default和on update来定义。timestamp不设置初始值且不添加其他属性时，默认在更新该行时，值修改为当前时间。
我在userinfo表里添加两个字段：create_time、update_time。
新建表如下：

DROP TABLE IF EXISTS `userinfo`;
CREATE TABLE `userinfo` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `lastname` varchar(30) NOT NULL,
  `firstname` varchar(30) NOT NULL,
  `birth` date NOT NULL,
  `sex` enum('F','M') NOT NULL,
  `phone` varchar(20) NOT NULL,
  `native_place` varchar(20) DEFAULT NULL,
  `create_time` timestamp DEFAULT 0, -- 定义默认值为0
  `update_time` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- 定义默认值为当前时间，且根据行更新设置为操作时间
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of userinfo
-- ----------------------------
INSERT INTO `userinfo` VALUES ('1', 'Ma', 'A', '1980-03-07', 'F', '12345678900', 'Beijing', NOW(), NOW());
INSERT INTO `userinfo` VALUES ('2', 'Ma', 'B', '1999-06-07', 'M', '12345678901', 'ShangHai', NOW(), NOW());
INSERT INTO `userinfo` VALUES ('3', 'Ma', 'C', '1994-05-24', 'M', '12345678902', null, NOW(), NOW());
INSERT INTO `userinfo` VALUES ('4', 'Lee', 'Deta', '1982-01-07', 'F', '12345678903', 'Beijing', NOW(), NOW());
INSERT INTO `userinfo` VALUES ('5', 'Lee', 'Ella', '1988-02-08', 'F', '12345678904', 'Beijing', NOW(), NOW());
INSERT INTO `userinfo` VALUES ('6', 'Waston', 'Fiona', '1992-03-21', 'F', '12345678905', 'HongKong', NOW(), NOW());
INSERT INTO `userinfo` VALUES ('7', 'Kin', 'Gina', '2002-11-16', 'F', '12345678906', 'HongKong', NOW(), NOW());
INSERT INTO `userinfo` VALUES ('8', 'Waston', 'Hana', '1960-08-07', 'F', '12345678907', null, NOW(), NOW());

我看的书上写的timestamp的特殊属性是默认不为null，如果想为null的话需要定义default；并且输入null的话会默认为当前时间。
但我个人使用的时候，按照上述定义，遇到一些问题：
（1）在不定义NULL/NOT NULL时，取值默认可以为null。
（2）定义为NULL，并插入null值时，timestamp保存的结果就是null。
（3）定义为NOT NULL，并插入null值时，timestamp会报错。
所以，我最后插入了NOW()。

在更新一条信息时：

UPDATE userinfo SET firstname = 'Alinda' WHERE id = 1;

update_time的值改变，create_time不变。