MYSQL——第一章节(数据类型2)

前言：

MySQL中的数据类型(日期与时间类型)

1.3、日期与时间类型

1.3.1 YEAR类型

关于日期和时间的数据类型今天算是搞完了，还有其他的关于时间的数据类型，如果想要了解可以去百度一下，下次再写关于字符串的类型，数据类型这块内容比较繁琐，但是如果注意一点，会发现在同一类型中很多东西是大致一样的，方便记忆，但关键还在于多实操。

前言：

承接上一篇MySQL数据类型，MySQL中的数据类型关键字比较多样，重要的记住常用的类型即可

MySQL中的数据类型(日期与时间类型)

1.3、日期与时间类型

MySQL中有多种表示日期的数据类型，主要有DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每一个类型都有合法的取值范围，当指定不合法的值时系统会将"零"值插入数据库中。

本节记录MySQL中日期和时间的类型的使用方法和练习。

1.3.1 YEAR类型

YEAR类型是单字节类型，可以使用多种格式指定YEAR值。

（1）以4位字符串或者4位数字格式表示的YEAR，范围是“1901~2155”，输入格式为："YYYY"或者YYYY。

mysql> create table date(年 YEAR);
Query OK, 0 rows affected (0.33 sec)

mysql> select * from date;
Empty set

#使用字符串格式和数字格式 输入年份
mysql> insert into date values("1901");
Query OK, 1 row affected (0.06 sec)

mysql> insert into date values(1901);
Query OK, 1 row affected (0.12 sec)
#查看  数据表中数据均为1901
mysql> select * from date;
+------+
| 年   |
+------+
| 1901 |
| 1901 |
+------+
2 rows in set (0.03 sec)

（2）以两位字符串或者数字格式表示YEAR，范围是"00"到"99"。“00”~“69”的范围插入值为2000~2069，“70”和“99”的范围插入值为1970~1999。“0”和“00”的作用相同，插入值超过取值范围的将被转换为2000。

#两位字符串格式“0”、“00”的区别和“00”~“69”、“70”~“99”的作用。
mysql> insert into date values('0'),('00'),('50'),('75');
Query OK, 4 rows affected (0.13 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from date;
+------+
| 年   |
+------+
| 1901 |
| 1901 |
| 2000 |
| 2000 |
| 2050 |
| 1975 |
+------+
6 rows in set (0.03 sec)

#这里就可以看出字符串格式的“0”和“00”插入的值都是2000，“50”和“75”分别是2050和1975.

#两位数字格式插入0,00,50,75，插入99为分隔点。
mysql> insert into date values(99),(0),(00),(50),(75);
Query OK, 5 rows affected (0.04 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from date;
+------+
| 年   |
+------+
| 1901 |
| 1901 |
| 2000 |
| 2000 |
| 2050 |
| 1975 |
| 1999 |
| 0000 |
| 0000 |
| 2050 |
| 1975 |
+------+
11 rows in set (0.03 sec)
#查看数据表中数据，数字格式0,00的插入值均被转换成0000而不是2000，50和75的转换值则不变。


#插入超出范围值
mysql> insert into date values("1900"),(1900),("2070"),(2070);
1264 - Out of range value for column 'year' at row 1
#报错提示超出范围值
#YEAR类型的范围值为1901~2069

1.3.2、TIME类型

TIME类型用于表示时间信息，格式为"HH:MM:SS",HH表示小时，MM表示分钟，SS表示秒。TIME类型不仅可以表示一天的时间(必须小于24小时),也可以表示某个事件过去的时间或者两个事件之间的时间间隔(可以大于24小时甚至为负值)。

TIME值表示格式：

（1）“D HH:MM:SS”格式的字符串，还可以使用“非严格”语法：“HH:MM:SS”、“HH:MM”、“D HH:MM”、“D HH”或者“SS”。D表示日，可以取0~34之间的值，再插入数据时，D被转换成小时保存，格式为“D*24+HH”。

#插入“D HH:MM:SS”等格式的值。

mysql> create table time(date TIME);
Query OK, 0 rows affected (0.29 sec)

mysql> insert into time values("13:10:20"),("4 13:10"),("4 13"),("4 20"),("10");
Query OK, 5 rows affected (0.13 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from time;
+-----------+
| date      |
+-----------+
| 13:10:20  |
| 109:10:00 |
| 109:00:00 |
| 116:00:00 |
| 00:00:10  |
+-----------+
5 rows in set (0.05 sec)

#看表中数据，第一条是正常输入的时间格式，第二条是“D HH:MM”格式，第三、四条是“D HH”格式、第五条是“SS”格式。使用“D HH”格式的时候小时位必须是两位，小于10的 在前面加零。

mysql> insert into time values("2 10:10"),("2 5"),("2 05");
1292 - Incorrect time value: '2 5' for column 'date' at row 2
#插入值不符合报错

mysql> insert into time values("2 10:10"),("2 05");
Query OK, 2 rows affected (0.39 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from time;
+----------+
| date     |
+----------+
| 58:10:00 |
| 53:00:00 |
+----------+
2 rows in set (0.02 sec)

（2）“HHMMSS”格式的、没有间隔符的字符串或者数字 HHMMSS 格式的，数据库解释时会默认最右边的两位表示秒。如果使用冒号则被看为当天时间。

#插入数字 HHMMSS 格式值，默认最右边两位表示秒。
mysql> insert into time values(111111),(1111),("11:12");
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from time;
+----------+
| date     |
+----------+
| 11:11:11 |
| 00:11:11 |
| 11:12:00 |
+----------+
3 rows in set (0.02 sec)

#字符串格式 "HHMMSS" 的值。
mysql> insert into time values("111111"),("1111");
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from time;
+----------+
| date     |
+----------+
| 11:11:11 |
| 00:11:11 |
+----------+
2 rows in set (0.03 sec)

#插入非法数值报错(11 78 20 中78是不合法值)
mysql> insert into time values("117820");
1292 - Incorrect time value: '117820' for column 'date' at row 1


#插入当前系统时间，在输入的时获取值(所以值可能因为语句输入时间不一样不同)
mysql> insert into time values(NOW()),(CURRENT_TIME);
Query OK, 2 rows affected (0.13 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from time;
+----------+
| date     |
+----------+
| 13:33:35 |
| 13:33:35 |
+----------+
3 rows in set (0.03 sec)

1.3.3、DATE类型

DATE类型只用于输入日期值时，没有时间部分，格式为“YYYY-MM-DD”,其中YYYY表示年，MM表示月，DD表示日。可以使用字符串类型和数字类型插入数据，只要符合DATE的日期格式。

（1）以“YYYY-MM-DD”或者“YYYYMMDD”字符串格式表示日期，取值范围“1000-01-01~9999-12-3” 。

（2）以“YY-MM-DD”或者“YYMMDD”字符串格式插入数据。

（3）以YY-MM-DD或者YYMMDD数字格式插入数据。（如上YEAR类型，两位数取值）。

#以两种字符串格式插入数据

mysql> insert into tmp1 values("2021-3-20"),("20210320");
Query OK, 2 rows affected (0.41 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tmp1;
+------------+
| date       |
+------------+
| 2021-03-20 |
| 2021-03-20 |
+------------+
2 rows in set (0.02 sec)

#以YY-MM-DD格式插入数据
mysql> insert into tmp1 values("21-3-20"),("750320");
Query OK, 2 rows affected (0.13 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tmp1;
+------------+
| date       |
+------------+
| 2021-03-20 |
| 2021-03-20 |
| 2021-03-20 |
| 1975-03-20 |
+------------+
4 rows in set (0.03 sec)

#以数字格式插入数据

mysql> insert into tmp1 values(20210321),(19800320),(200513),(800210);
Query OK, 4 rows affected (0.09 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from tmp1;
+------------+
| date       |
+------------+
| 2021-03-21 |
| 1980-03-20 |
| 2020-05-13 |
| 1980-02-10 |
+------------+
4 rows in set (0.03 sec)


#插入当前系统日期 CURRENT_DATE只返回日期，没有时间部分。NOW()返回日期和时间，但只保存日期值
mysql> insert into tmp1 values(CURRENT_DATE),(NOW());
Query OK, 2 rows affected (0.12 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql> select * from tmp1;
+------------+
| date       |
+------------+
| 2021-03-20 |
| 2021-03-20 |
+------------+
2 rows in set (0.04 sec)

1.3.4、DATETIME类型

DATETIME类型的字段可以同时输入日期和时间信息，日期格式为：“YYYY-MM-DD HH:MM:SS”,在给DATETIME类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合DATETIME类型的日期格式即可。

（1）字符串类型“YYYY-MM-DD HH:MM:SS” 或 “YYYYMMDDHHMMSS”。取值范围“1000-01-01 00:00:00~9999-12-3 23:59:59”

（2）字符串类型“YY-MM-DD HH:MM:SS” 或 “YYMMDDHHMMSS”。取值范围与之前的类型相同。

（3）数字类型YYYYMMDDHHMMSS” 或 YYMMDDHHMMSS。

#基本和前面将的差别不大，DATETIME类型可以插入日期和时间，DATE和TIME是只能插入日期或者时间。

mysql> insert into tmp2 values("2021-03-20 17:50:50"),("20210320175050"),(20210320175050),("210320175050"),("750320175050"),(210320175050),(750320175050);
Query OK, 7 rows affected (0.48 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from tmp2;
+---------------------+
| dt                  |
+---------------------+
| 2021-03-20 17:50:50 |
| 2021-03-20 17:50:50 |
| 2021-03-20 17:50:50 |
| 2021-03-20 17:50:50 |
| 1975-03-20 17:50:50 |
| 2021-03-20 17:50:50 |
| 1975-03-20 17:50:50 |
+---------------------+
7 rows in set (0.03 sec)


#插入当前的日期和时间
#这里就可以看出CURRENT_DATE和NOW()的区别了

mysql> insert into tmp2 values(CURRENT_DATE),(NOW());
Query OK, 2 rows affected (0.13 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tmp2;
+---------------------+
| dt                  |
+---------------------+
| 2021-03-20 00:00:00 |
| 2021-03-20 18:02:07 |
+---------------------+
2 rows in set (0.03 sec)

1.3.5、TIMESTAMP类型

TIMESTAMP类型的显示格式和DATETIME类型相同，显示宽度为19个字符，日期格式为“YYYY-MM-DD HH:MM:SS”。取值范围“1970-0101 00:00:01” UTC ~ “2038-01-19 03:14:07” UTC。（UTC 为世界标准时间）。

#TIMESTAMP类型的取值范围小于DATETIME类型

mysql> insert into tmp2 values('19950101010101'),(19950202020202);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tmp2;
+---------------------+
| dt                  |
+---------------------+
| 1995-01-01 01:01:01 |
| 1995-02-02 02:02:02 |
+---------------------+
2 rows in set (0.03 sec)

#MySQL中的支持“不严格”语法，任何标点符号都可以和用作日期或者时间的分隔符
#但不能使用数字类型插入，只能使用字符串类型插入

mysql> insert into tmp2 values('2021@03.20 17*50#50'),('2021+03-20 17/50&50');
Query OK, 2 rows affected (0.12 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tmp2;
+---------------------+
| dt                  |
+---------------------+
| 2021-03-20 17:50:50 |
| 2021-03-20 17:50:50 |
+---------------------+
2 rows in set (0.04 sec)

#插入当前日期和时间

mysql> insert into tmp2 values(CURRENT_DATE),(NOW());
Query OK, 2 rows affected (0.13 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tmp2;
+---------------------+
| dt                  |
+---------------------+
| 2021-03-20 00:00:00 |
| 2021-03-20 18:16:55 |
+---------------------+
2 rows in set (0.04 sec)


#TIMESTAMP类型与DATETIME类型最大的区别是，DATETIME类型不受时区影响，而TIMESTAMP类型存储是以UTC格式保存，存储时对当前时区进行转换，检索时再转换回当前时区，即查询时，根据时区不同，显示的时间也不同。
#东八区时间
mysql> insert into tmp2 values(NOW());
Query OK, 1 row affected (0.14 sec)

mysql> select * from tmp2;
+---------------------+
| dt                  |
+---------------------+
| 2021-03-20 18:20:57 |
+---------------------+
1 row in set (0.02 sec)

#修改时区为东10区
mysql> set time_zone='+10:00';
Query OK, 0 rows affected (0.02 sec)

mysql> insert into tmp2 values(NOW());
Query OK, 1 row affected (0.07 sec)

mysql> select * from tmp2;
+---------------------+
| dt                  |
+---------------------+
| 2021-03-20 18:20:57 |
| 2021-03-20 20:21:41 |
+---------------------+
2 rows in set (0.02 sec)


#由此看出，东10区比东八区快2小时，因此查询时间也增加了两小时。

关于日期和时间的数据类型今天算是搞完了，还有其他的关于时间的数据类型，如果想要了解可以去百度一下，下次再写关于字符串的类型，数据类型这块内容比较繁琐，但是如果注意一点，会发现在同一类型中很多东西是大致一样的，方便记忆，但关键还在于多实操。【谢谢观看哦！！】

posted @ 2022-08-13 10:38 knsec 阅读(20) 评论(0) 编辑收藏举报

刷新页面返回顶部

Loading

Knsec

Knsec

MYSQL——第一章节(数据类型2)

前言：

MySQL中的数据类型(日期与时间类型)

1.3、日期与时间类型

1.3.1 YEAR类型

1.3.2、TIME类型

1.3.3、DATE类型

1.3.4、DATETIME类型

1.3.5、TIMESTAMP类型

公告