mysql常用函数示例

?

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 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43

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();##查看当前系统时分秒

 

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