MySQL数据库的数据类型和基于MySQL数据类型的综合实例项目
1、MySQL具有的数据类型
1.1、MySQL整数类型
数值型数据类型主要用来存储数字。MySQL的整数类型如下所示
TINYINT、SMALLINT、MEDIUMINT、INT(INTEGER）、 BIGINT。
| 类型名称 | 说明 | 存储需求 |
| TINYINT | 很小的整数 | 1字节 |
| SMALLINT | 小的整数 | 2字节 |
| MEDIUMINT | 中等大小的整数 | 3字节 |
| INT | 普通大小的整数 | 4字节 |
| BIGINT | 大整数 | 8字节 |
创建表tmp1，其中字段x、y、z、m、n数据类型依次为TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，SQL语句如下：
mysql> CREATE TABLE tmp1 ( x TINYINT, y SMALLINT, z MEDIUMINT, m INT, n BIGINT ); Query OK, 0 rows affected (0.01 sec) mysql> DESC tmp1; +-------+-----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+---------+-------+ | x | tinyint | YES | | NULL | | | y | smallint | YES | | NULL | | | z | mediumint | YES | | NULL | | | m | int | YES | | NULL | | | n | bigint | YES | | NULL | | +-------+-----------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
1.2、MySQL浮点类型和定点数
MySQL中使用浮点数和定点数来表示小数。
浮点类型：FLOAT、DOUBLE。
定点类型：DECIMAL。
| 类型名称 | 说明 | 存储需求 |
| FLOAT | 单精度浮点数 | 4字节 |
| DOUBLE | 双精度浮点数 | 8字节 |
| DECIMAL(M,D) | 定点数 | M+2字节 |
DECIMAL的存储空间不是固定的，而是由M和D决定的。
创建表tmp2，其中字段x、y、z数据类型依次为FLOAT(5,1)、DOUBLE(5,1)和DECIMAL(5,1)，向表中插入数据5.12、5.15和5.123，SQL语句如下：
mysql> CREATE TABLE tmp2 ( x FLOAT(5,1), y DOUBLE(5,1), z DECIMAL(5,1) ); Query OK, 0 rows affected, 2 warnings (0.01 sec)
向tmp2表中插入数据：
mysql> INSERT INTO tmp2 VALUES(5.12, 5.15, 5.123); Query OK, 1 row affected, 1 warning (0.01 sec)
查看警告信息
mysql> SHOW WARNINGS; +-------+------+----------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------+ | Note | 1265 | Data truncated for column 'z' at row 1 | +-------+------+----------------------------------------+ 1 row in set (0.00 sec)
可以看到定点数被阶段了，发出警告
查看如下：
mysql> SELECT * FROM tmp2; +------+------+------+ | x | y | z | +------+------+------+ | 5.1 | 5.2 | 5.1 | +------+------+------+ 1 row in set (0.00 sec)
1.3、MySQL日期与时间类型
MySQL中表示日期和时间的数据类型有
YEAR、TIME 、DATE、DATETIME和TIMESTAMP。
| 类型名称 | 日期格式 | 日期范围 | 存储需求 |
| YEAR | YYYY | 1901~2155 | 1字节 |
| TIME | HH:MM:SS | -838:59:59~838:59:59 | 3字节 |
| TIMESTAMP | YYYY-MM-DD HH:MM:SS | 1970-01-01 00:00:1 UTC~2038-01-19 03:14:07 UTC | 4字节 |
1、YEAR
存储格式：
存储方式(YEAR) 范围 注意点
4位字符串或数字 ‘1901’~’2155’ 输入‘2022’或2022，插入到数据库的值都为2022
2位字符串 ‘00’~’99’ ‘00’~‘69’对应2000~2069；‘70’~‘99’对应1970~1999； 2位数字 1~99 1~69对应2001~2069；70~99对应1970~1999；0对应0000
创建数据表tmp3，定义数据类型为YEAR的字段y，向表中插入值2010，’2010’，’2166’，SQL语句如下：
首先创建表tmp3：
mysql> CREATE TABLE tmp3( y YEAR ); Query OK, 0 rows affected (0.01 sec)
向表中插入数据：
mysql> INSERT INTO tmp3 values(2010),('2010'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0
再次向表中插入数据：
mysql> INSERT INTO tmp3 values ('2166'); ERROR 1264 (22003): Out of range value for column 'y' at row 1 mysql> SHOW WARNINGS; +-------+------+--------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------+ | Error | 1264 | Out of range value for column 'y' at row 1 | +-------+------+--------------------------------------------+ 1 row in set (0.00 sec)SELECT * FROM tmp3;
2166超过了最大取值：2155，所以会有Out of range value for column ‘y’ at row 1的错误。
查看数据表tmp3中的数据表
mysql> SELECT * FROM tmp3; +------+ | y | +------+ | 2010 | | 2010 | +------+ 2 rows in set (0.00 sec)
可以看到2010和‘2010’都转换成了2010。
向tmp3表中y字段插入2位字符串表示的YEAR值，分别为’0’、’00’、’77’和’10’，SQL语句如下：
向表中插入数据：
mysql> INSERT INTO tmp3 values('0'),('00'),('77'),('10'); Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0
查看数据表tmp3中的数据表：
mysql> SELECT * FROM tmp3; +------+ | y | +------+ | 2010 | | 2010 | | 2000 | | 2000 | | 1977 | | 2010 | +------+ 6 rows in set (0.00 sec)
可以看到‘0’，‘00’转换成了2000，‘77’转换成了1977，‘10’转换成了2010。
向tmp3表中y字段插入2位数字表示表示的YEAR值，分别为0、75和13，SQL语句如下：
向表中插入数据：
mysql> INSERT INTO tmp3 values(0),(75),(13); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0
查看数据表tmp3中的数据表：
SELECT * FROM tmp3; mysql> SELECT * FROM tmp3; +------+ | y | +------+ | 2010 | | 2010 | | 2000 | | 2000 | | 1977 | | 2010 | | 0000 | | 1975 | | 2013 | +------+ 9 rows in set (0.00 sec)
可以看到0转换成了0000，75转换成了1975，13转换成了2013。
2、TIME
存储格式：
存储方式(TIME) 范围 注意点
‘HH:MM:SS -838:59:59~838:59:59 ‘HHMMSS’类型的字符串被理解为：‘HH:MM:SS’；’D HH:MM:SS’的D为0~34之间，为小时保存—》‘D*24+H’
创建数据表tmp4，定义数据类型为TIME的字段t，向表中插入值’10:05:05’，’23:23’，’2 10:10’，’3 02’，’10’，SQL语句如下：首先创建表tmp4，
mysql> CREATE TABLE tmp4( t TIME ); Query OK, 0 rows affected (0.01 sec)
向表中插入数据：
INSERT INTO tmp4 values('10:05:05 '), ('23:23'), ('2 10:10'), ('3 02'),('10'); mysql> INSERT INTO tmp4 values('10:05:05 '), ('23:23'), ('2 10:10'), ('3 02'),('10'); Query OK, 5 rows affected, 1 warning (0.00 sec) Records: 5 Duplicates: 0 Warnings: 1
查看结果：
mysql> SELECT * FROM tmp4; +----------+ | t | +----------+ | 10:05:05 | | 23:23:00 | | 58:10:00 | | 74:00:00 | | 00:00:10 | +----------+ 5 rows in set (0.00 sec)
向表tmp4中插入值’101112’，111213,’0’，107010，SQL语句如下：
向表中插入数据：
mysql> INSERT INTO tmp4 values('101112'),(111213),( '0'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0
再向表中插入数据：
mysql> INSERT INTO tmp4 values ( 107010); ERROR 1292 (22007): Incorrect time value: '107010' for column 't' at row 1 mysql> show warnings; +-------+------+--------------------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------------------+ | Error | 1292 | Incorrect time value: '107010' for column 't' at row 1 | +-------+------+--------------------------------------------------------+ 1 row in set (0.00 sec)
可以看到插入的时间不对会报错。
3、DATE
DATE类型用在仅需要日期值时，没有时间部分，在存储时需要3字节。日期格式为’YYYY-MM-DD’—》YYYY表示年，MM表示月，DD表示日。在给Date类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合DATE的日期格式即可。
(1)、以‘YYYY-MM-DD’或者‘YYYYMMDD’字符串格式表示的日期，取值范围为“1000-01-01’~ 9999-12-3’。例如，输入‘2022-08-01’ 或者‘20220801’，期都为2022-08-01。
(2)、以‘YY-MM-DD’ 或者’YYMMDD’’ 字符串格式表示的日期，在这里YY表示两位的年值。Mysql使用以下规则解释两位年值：‘0069’范围的年值转换为‘2000 2069’;‘70~ 99’范围的年值转换为‘1970~ 1999’。例如，输入‘22-08-01’，插入数据库的日期为2022-08-01; 输入‘991231’，插入数据的日期为1999-12-31。
(3)、以YY-MM-DD或者YYMMDD数字格式表示的日期，与前面相似，0069范围的年值转换为2000 2069，70~ 99范围的年值转换为1970~1999。例如，输入12-12-31 插入数据库的日期为2012-12-31;输入981231，插入数据的日期为1998-12-31。
(4)、使用CURRENT DATE或者NOW(),插入当前系统日期。
创建数据表tmp5，定义数据类型为DATE的字段d，向表中插入“YYYY-MM-DD”和“YYYYMMDD”字符串格式日期，SQL语句如下：
首先创建表tmp5：
mysql> CREATE TABLE tmp5(d DATE); Query OK, 0 rows affected (0.01 sec)
向表中插入“YYYY-MM-DD”和“YYYYMMDD”格式日期：
mysql> INSERT INTO tmp5 values('2022-08-01'),('19990108'),('20220610'); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0
查看插入结果：
mysql> SELECT * FROM tmp5; +------------+ | d | +------------+ | 2022-08-01 | | 1999-01-08 | | 2022-06-10 | +------------+ 3 rows in set (0.00 sec)
向tmp5表中插入“YY-MM-DD”和“YYMMDD”字符串格式日期，SQL语句如下：
向表中插入“YY-MM-DD”和“YYMMDD”格式日期：
mysql> INSERT INTO tmp5 values ('99-09-09'),( '990909'), ('000101') ,('111111'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0
查看插入结果：
SELECT * FROM tmp5; mysql> SELECT * FROM tmp5; +------------+ | d | +------------+ | 2022-08-01 | | 1999-01-08 | | 2022-06-10 | | 1999-09-09 | | 1999-09-09 | | 2000-01-01 | | 2011-11-11 | +------------+ 7 rows in set (0.00 sec)
向tmp5表中插入YY-MM-DD和YYMMDD数字格式日期，SQL语句如下：
向表中插入YY-MM-DD和YYMMDD数字格式日期：
mysql> INSERT INTO tmp5 values(990909), ( 000101) ,( 111111); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0
查看插入结果：
SELECT * FROM tmp5; mysql> SELECT * FROM tmp5; +------------+ | d | +------------+ | 1999-09-09 | | 2000-01-01 | | 2011-11-11 | +------------+ 3 rows in set (0.00 sec)
向tmp5表中插入系统当前日期，SQL语句如下：
向表中插入系统当前日期：
mysql> INSERT INTO tmp5 values( CURRENT_DATE() ),( NOW() ); Query OK, 2 rows affected, 1 warning (0.00 sec) Records: 2 Duplicates: 0 Warnings: 1
查看插入结果：
SELECT * FROM tmp5; mysql> SELECT * FROM tmp5; +------------+ | d | +------------+ | 1999-09-09 | | 2000-01-01 | | 2011-11-11 | | 2022-08-05 | | 2022-08-05 | +------------+ 5 rows in set (0.00 sec)
