mysql 字段类型总结---date datetime 等时间类型

//date
CREATE TABLE insect
(
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (id),
	name VARCHAR(30) NOT NULL, # type of insect
	date DATE NOT NULL, # date collected
	origin VARCHAR(30) NOT NULL # where collected
);
INSERT INTO insect (name,date,origin) VALUES('cricket','2010-09-11','basement')
INSERT INTO insect (name,date,origin) VALUES('cricket','2010/09/11','basement');
INSERT INTO insect (name,date,origin) VALUES('cricket','2010:09:11','basement');
INSERT INTO insect (name,date,origin) VALUES('cricket','2010:09:111','basement');
select * from insect;

//date datetime time timestamp
CREATE TABLE temporal_val
(
	d       DATE,
	dt      DATETIME,
	t       TIME,
	ts      TIMESTAMP#auto filled with current datetime
);
INSERT INTO temporal_val (d, dt, t, ts) VALUES('1970-01-01','1884-01-01 12:00:00','13:00:00','1980-01-01 02:00:00'),
('1999-01-01','1860-01-01 12:00:00','19:00:00','2021-01-01 03:00:00'),
('1981-01-01','1871-01-01 12:00:00','03:00:00','1975-01-01 04:00:00'),
('1964-01-01','1899-01-01 12:00:00','01:00:00','1985-01-01 05:00:00');
INSERT INTO temporal_val (d, dt, t) VALUES('1970-01-01','1884-01-01 12:00:00','13:00:00');
INSERT INTO temporal_val (d) VALUES('1970-01-01');
SELECT * FROM temporal_val;
SELECT * FROM temporal_val ORDER BY dt;

Function		Description
NOW()			Returns the current date and time
CURDATE()		Returns the current date
CURTIME()		Returns the current time
DATE()			Extracts the date part of a date or date/time expression
EXTRACT()		Returns a single part of a date/time
DATE_ADD()		Adds a specified time interval to a date
DATE_SUB()		Subtracts a specified time interval from a date
DATEDIFF()		Returns the number of days between two dates
DATE_FORMAT()	Displays date/time data in different formats

mysql> select NOW(),CURDATE(),CURTIME();
+---------------------+------------+-----------+
| NOW()               | CURDATE()  | CURTIME() |
+---------------------+------------+-----------+
| 2014-06-20 22:54:13 | 2014-06-20 | 22:54:13  |
+---------------------+------------+-----------+
1 row in set (0.00 sec)

CREATE TABLE Orders
(
OrderId int NOT NULL,
ProductName varchar(50) NOT NULL,
OrderDate datetime NOT NULL DEFAULT NOW(),
PRIMARY KEY (OrderId)
)

满屏的代码，一些自认为不错的例子，也是为了让自己记住