MySQL时间函数

MySQL数据库的一些操作：

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for employee_copy
-- ----------------------------
DROP TABLE IF EXISTS `employee_copy`;
CREATE TABLE `employee_copy` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `last_name` varchar(50) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `gender` char(1) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of employee_copy
-- ----------------------------
INSERT INTO `employee_copy` VALUES ('2', 'Jerry', 'jerry@163.com', '0', '25', '2019-11-15');
INSERT INTO `employee_copy` VALUES ('3', 'Blackqqq', 'black@163.com', '1', '30', '2019-11-13');
INSERT INTO `employee_copy` VALUES ('4', 'Whiteqqq', 'white@163.com', '0', '35', '2015-01-01');
INSERT INTO `employee_copy` VALUES ('5', 'xiaozhang', '21123@qq.com', '1', '231', '2010-12-01');
INSERT INTO `employee_copy` VALUES ('6', 'xiaozhang', '12344@qq.com', '1', '245', '1995-02-28');
INSERT INTO `employee_copy` VALUES ('7', 'xiaozhang', '27851@qq.com', '1', '98', '2014-02-28');
INSERT INTO `employee_copy` VALUES ('8', 'xiaozhang', '28651@qq.com', '1', '231', '2006-01-28');
INSERT INTO `employee_copy` VALUES ('9', '卡萨丁qwe', '2dsfs1@qq.com', '1', '333', '1998-01-01');
INSERT INTO `employee_copy` VALUES ('10', 'zhanghong', '2sdgf1@qq.com', '1', '12', '2009-01-28');
INSERT INTO `employee_copy` VALUES ('11', '韦小宝weq', '2wds1@qq.com', '0', '18', '2004-01-28');
INSERT INTO `employee_copy` VALUES ('12', '梨花eqwee', 'wev21@qq.com', '0', '11', '1998-01-01');
INSERT INTO `employee_copy` VALUES ('13', 'zhangsan', '1@qq.com', '1', '23', '2001-01-28');
INSERT INTO `employee_copy` VALUES ('14', '卡萨丁', '21@qq.com', '1', '231', '2001-01-28');
INSERT INTO `employee_copy` VALUES ('15', '卡萨丁丁', '21@qq.com', '1', '231', '1992-11-29');
INSERT INTO `employee_copy` VALUES ('16', 'sds sd', '23123', '1', null, '2012-02-28');
INSERT INTO `employee_copy` VALUES ('17', '213123', '3123', '0', null, '2014-02-28');
INSERT INTO `employee_copy` VALUES ('18', '卡萨丁丁', '21@qq.com', '1', '231', '2007-01-28');

对某张表进行备份（复制）操作：

CREATE TABLE employee01 SELECT * FROM employee;

在指定的列的前面或是后面添加一个字段：
语法：ALTER TABLE 表名 ADD 字段名 数据类型(长度) DELETE NULL AFTER/BEFORE 列名;
例如：

ALTER TABLE `employee` ADD qq号码 VARCHAR(32) DEFAULT NULL AFTER address;

日期函数：

SELECT YEAR(birthday) ,MONTH(birthday),DAY(birthday) FROM employee;

SELECT NOW(),CURRENT_DATE(),CURRENT_TIME();

SELECT TIMESTAMPDIFF(YEAR,birthday,NOW()) FROM employee;

SELECT TIMESTAMPDIFF(MONTH,birthday,NOW()) FROM employee;

SELECT TIMESTAMPDIFF(WEEK,birthday,NOW()) FROM employee;

-- 查询员工表中2010年出生的人
-- 方法一 
SELECT *from employee WHERE year(birthday) ='2010';
 -- 方法二：字符串截取
SELECT *from employee WHERE LEFT(birthday,4) ='2010';

-- 查询48小时后的某个时间
SELECT TIMESTAMP(NOW(),'48:00:00');

-- 获取60秒以前的时间
SELECT DATE_ADD(NOW(),INTERVAL -60	 SECOND);
--  10小时22分之前的时间
SELECT DATE_ADD(NOW(),INTERVAL '-10:22'	 HOUR_MINUTE);
-- 3天13小时后的时间
SELECT DATE_ADD(NOW(),INTERVAL '3 13'	 DAY_HOUR);

-- 当前月的最后一天
SELECT LAST_DAY(NOW());
--  当前月的第一天
select DATE_SUB(NOW(),INTERVAL DAYOFMONTH(NOW())-1 DAY);