mysql数据类型--[日期类型]--date类型

date类型说明

类   型：date
显示效果：年-月-日
有效范围：1000-01-01 至 9999-12-31
插入方式：
       insert into test3(mydate) values(20190601);
       insert into test3(mydate) values('2019-06-01');
       insert into test3(mydate) values(curdate());
       insert into test3(mydate) values(date(now()));

实践环境准备

-- 创建chenliang库,字符集为utf8
mysql> create database if not exists chenliang character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.01 sec)

mysql> show databases like "chenliang";
+----------------------+
| Database (chenliang) |
+----------------------+
| chenliang            |
+----------------------+
1 row in set (0.00 sec)

-- 进入chenliang库，并查看是否成功进入到了chenliang库
mysql> use chenliang;
Database changed

mysql> select database();
+------------+
| database() |
+------------+
| chenliang  |
+------------+
1 row in set (0.00 sec)

通过案例理解

-- 创建test3表，字符集为utf8,存储引擎为innodb，mydate字段为date
mysql> create table if not exists test3(
-> mydate date
-> )engine=innodb character set utf8 collate utf8_general_ci;
Query OK, 0 rows affected (0.00 sec)

-- 查看test3表的表结构
mysql> desc test3;
+--------+------+------+-----+---------+-------+
| Field  | Type | Null | Key | Default | Extra |
+--------+------+------+-----+---------+-------+
| mydate | date | YES  |     | NULL    |       |
+--------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

-- 第一种插入数据的方式【不建议使用】
mysql> insert into test3(mydate) values(20190601);
Query OK, 1 row affected (0.01 sec)

mysql> select * from test3;
+------------+
| mydate     |
+------------+
| 2019-06-01 |
+------------+
1 row in set (0.00 sec)

-- 第二种插入数据的方式【建议使用】
mysql> insert into test3(mydate) values("2019-06-02");
Query OK, 1 row affected (0.00 sec)

mysql> select * from test3;
+------------+
| mydate     |
+------------+
| 2019-06-01 |
| 2019-06-02 |
+------------+
2 rows in set (0.00 sec)

-- 第三种插入数据的方式【建议使用】
mysql> insert into test3(mydate) values(curdate());
Query OK, 1 row affected (0.00 sec)

mysql> select * from test3;
+------------+
| mydate     |
+------------+
| 2019-06-01 |
| 2019-06-02 |
| 2019-06-15 |
+------------+
3 rows in set (0.00 sec)

-- 第四种插入数据的方式【建议使用】
mysql> insert into test3(mydate) values(date(now()));
Query OK, 1 row affected (0.00 sec)

mysql> select * from test3;
+------------+
| mydate     |
+------------+
| 2019-06-01 |
| 2019-06-02 |
| 2019-06-15 |
| 2019-06-15 |
+------------+
4 rows in set (0.00 sec)

转载于:https://www.cnblogs.com/chenliangc/articles/11107447.html