mysql常用函数示例

CREATE TABLE `orders` (
  `OrderId` INT(10) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `ProductName` VARCHAR(32) NOT NULL COMMENT '名称',
  `OrderDate` DATETIME NOT NULL COMMENT '时间',
  PRIMARY KEY (`OrderId`)
) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `orders` */

INSERT  INTO `orders`(`OrderId`,`ProductName`,`OrderDate`) VALUES (1,'Jarlsberg Cheese','2008-11-11 13:23:44');

INSERT  INTO `orders`(`OrderId`,`ProductName`,`OrderDate`) VALUES (2,'xxxxyyyy','2008-01-12 15:03:14');

#1.4、DATE()：提取日期或时间表达式的日期部分
SELECT ProductName, DATE(OrderDate) AS OrderDate FROM Orders WHERE OrderId=1;

SELECT ProductName, TIME(OrderDate) AS OrderDate FROM Orders WHERE OrderId=1;

#1.5、EXTRACT()：返回日期(年月日)单独部分
SELECT EXTRACT(YEAR FROM OrderDate) AS OrderYear FROM Orders WHERE OrderId=2;

SELECT EXTRACT(MONTH FROM OrderDate) AS OrderMonth FROM Orders WHERE OrderId=2;

SELECT EXTRACT(DAY FROM OrderDate) AS OrderDay FROM Orders WHERE OrderId=2;


#1.1、DATE_ADD()：向日期添加指定的时间间隔
SELECT OrderId,DATE_ADD(OrderDate,INTERVAL 1 DAY) AS OrderPayDate FROM Orders;

#1.7、DATE_SUB()：从日期减去指定的时间间隔
SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 1 DAY) AS SubtractDate FROM Orders;

#1.8、DATEDIFF()：返回两个日期之间的天数
SELECT DATEDIFF('2008-11-30','2008-11-20') AS DiffDate;

#1.9、DATE_FORMAT()：用不同的格式显示日期/时间
SELECT DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p');

SELECT  VERSION();##查看当前mysql的版本
SELECT DATABASE();##查看当前数据库是哪个
SELECT NOW();##查看当前系统时间
SELECT CURDATE();##查看当前日期
SELECT CURTIME();##查看当前系统时分秒

 

未完，待续...........