1.2 数据类型
MySQL中的数据类型是强类型
1.2.1 数值型
1、 整型
整形 | 占用字节数 | 范围 |
---|---|---|
tinyint | 1 | -128~127 |
smallint | 2 | -32768~32767 |
mediumint | 3 | -8388608~8388607 |
int | 4 | -2147483648~2147483647 |
bigint | 8 | -9223372036854775808~9223372036854775807 |
选择的范围尽可能小,范围越小占用资源越少
mysql> create table stu1(
-> id tinyint, # 范围要尽可能小,范围越小,占用空间越少
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.02 sec)
-- 超出范围会报错
mysql> insert into stu1 values (128,'tom');
ERROR 1264 (22003): Out of range value for column 'id' at row 1
无符号整形(unsigned) 无符号整形就是没有负数,无符号整数是整数的两倍
mysql> create table stu2(
-> id tinyint unsigned # 无符号整数
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into stu2 values (128);
Query OK, 1 row affected (0.00 sec)
整形支持显示宽度,显示宽带是最小的显示位数,如int(11)表示整形最少用11位表示,如果不够位数用0填充。显示宽度默认不起作用,必须结合zerofill才起作用。
mysql> create table stu4(
-> id int(5),
-> num int(5) zerofill # 添加前导0,int(5)显示宽带是5
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> insert into stu4 values (12,12);
Query OK, 1 row affected (0.00 sec)
mysql> select * from stu4;
+------+-------+
| id | num |
+------+-------+
| 12 | 00012 |
+------+-------+
1 row in set (0.00 sec)
小结:
1、范围要尽可能小,范围越小,占用空间越少
2、无符号整数是整数的两倍
3、整形支持显示宽度,显示宽带是最小的显示位数,必须结合zerofill才起作用
2、浮点型
浮点型 | 占用字节数 | 范围 |
---|---|---|
float(单精度型) | 4 | -3.4E+38~3.4E+38 |
double(双精度型) | 8 | -1.8E+308~1.8E+308 |
浮点型的声明:float(M,D) double(M,D)
M:总位数 D:小数位数
例题
mysql> create table stu5(
-> num1 float(5,2), -- 浮点数
-> num2 double(6,1) -- 双精度数
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> insert into stu5 values (3.1415,12.96);
Query OK, 1 row affected (0.00 sec)
mysql> select * from stu5;
+------+------+
| num1 | num2 |
+------+------+
| 3.14 | 13.0 |
+------+------+
1 row in set (0.00 sec)
MySQL浮点数支持科学计数法
mysql> create table stu6(
-> num float # 不指定位数,默认是小数点后面6位 double默认是17位
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> insert into stu6 values (5E2),(6E-2); # 插入科学计数法
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from stu6;
+------+
| num |
+------+
| 500 |
| 0.06 |
+------+
2 rows in set (0.00 sec)
浮点数精度会丢失
mysql> insert into stu6 values(99.999999999);
Query OK, 1 row affected (0.00 sec)
mysql> select * from stu6;
+------+
| num |
+------+
| 100 |
+------+
小结:
1、浮点数有单精度和双精度
2、浮点数支持科学计数法
3、浮点数精度会丢失
3、小数(定点数)
原理:将整数部分和小数部分分开存储
语法:
decimal(M,D)
例题:
mysql> create table stu8(
-> num decimal(20,9) # 存放定点数
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> insert into stu8 values(12.999999999);
Query OK, 1 row affected (0.00 sec)
mysql> select * from stu8;
+--------------+
| num |
+--------------+
| 12.999999999 |
+--------------+
1 row in set (0.00 sec)
小结:
1、decimal是变长的,大致是每9个数字用4个字节存储,整数和小数分开计算。M最大是65,D最大是30,默认是(10,2)。
2、定点和浮点都支持无符号、显示宽度0填充。
1.2.2 字符型
在数据库中没有字符串概念,只有字符,所以数据库中只能用单引号
数据类型 | 描述 |
---|---|
char | 定长字符,最大可以到255 |
varchar | 可变长度字符,最大可以到65535 |
tinytext | 28–1 =255 |
text | 216–1 =65535 |
mediumtext | 224–1 |
longtext | 232–1 |
char(4):存放4个字符,中英文一样。
varchar(L)实现变长机制,需要额外的空间来记录数据真实的长度。
L的理论长度是65535,但事实上达不到,因为有的字符是多字节字符,所以L达不到65535。
text系列的类型在表中存储的是地址,占用大小大约10个字节
一个记录的所有字段的总长度也不能超过65535个字节。
小结:
1、char是定长,var是变长
2、char最大值是255,varchar最大值是65535,具体要看字符编码
3、text系列在表中存储的是地址
4、一条记录的总长度不能超过65535
1.2.3 枚举(enum)
从集合中选择一个值作为数据(单选)
mysql> create table stu12(
-> name varchar(20),
-> sex enum('男','女','保密') # 枚举
-> );
Query OK, 0 rows affected (0.06 sec)
-- 插入的枚举值只能是枚举中提供的选项
mysql> insert into stu12 values ('tom','男');
Query OK, 1 row affected (0.00 sec)
-- 报错,只能插入男、女、保密
mysql> insert into stu12 values ('tom','不告诉你');
ERROR 1265 (01000): Data truncated for column 'sex' at row 1
枚举值是通过整形数字来管理的,第一个值是1,第二个值是2,以此类推,枚举值在数据库存储的是整形数字。
mysql> insert into stu12 values ('berry',2); -- 插入数字
Query OK, 1 row affected (0.00 sec)
mysql> select * from stu12;
+-------+------+
| name | sex |
+-------+------+
| tom | 男 |
| berry | 女 |
+-------+------+
mysql> select * from stu12 where sex=2; -- 2表示第二个枚举值
+-------+------+
| name | sex |
+-------+------+
| berry | 女 |
+-------+------+
1 row in set (0.00 sec)
枚举优点:
(1)、限制值
(2)、节省空间
(3)、运行速度快(整形比字符串运行速度快)
思考:已知枚举占用两个字节,所以最多可以有多少个枚举值?
答:2字节=16位,216=65536,范围是(0-65535),由于枚举从1开始,所以枚举值最多有65535个
1.2.4 集合(set)
从集合中选择一些值作为数据(多选)
mysql> create table stu13(
-> name varchar(20),
-> hobby set('爬山','读书','游泳','烫头') -- 集合
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> insert into stu13 values ('tom','爬山');
mysql> insert into stu13 values ('Berry','爬山,游泳');
Query OK, 1 row affected (0.00 sec)
mysql> insert into stu13 values ('Berry','游泳,爬山'); -- 插入的顺序不一样,但显示的顺序一样
Query OK, 1 row affected (0.00 sec)
mysql> select * from stu13;
+-------+-----------+
| name | hobby |
+-------+-----------+
| tom | 爬山 |
| Berry | 爬山,游泳 |
| Berry | 爬山,游泳 |
+-------+-----------+
3 rows in set (0.00 sec)
集合和枚举一样,也为每个集合元素分配一个固定值,分配方式是从前往后按2的0、1、2、…次方,转换成二进制后只有一位是1,其他都是0。
'爬山','读书','游泳','烫头'
1 2 4 8
mysql> select hobby+0 from stu13;
+---------+
| hobby+0 |
+---------+
| 1 |
| 5 |
| 5 |
+---------+
mysql> insert into stu13 values ('rose',15);
Query OK, 1 row affected (0.00 sec)
已知集合类型占8个字节,那么集合中最多有多少选项
答:有64个选项。
1.2.5 日期时间型
数据类型 | 描述 |
---|---|
datetime | 日期时间,占用8个字节 |
date | 日期 占用3个字节 |
time | 时间 占用3个字节 |
year | 年份,占用1个字节 |
timestamp | 时间戳,占用4个字节 |
1、datetime和date
datetime格式:年-月-日 小时:分钟:秒。支持的范围是’1000-01-01 00:00:00’到’9999-12-31
23:59:59’。
mysql> create table stu14(
-> t1 datetime, -- 日期时间
-> t2 date -- 日期
-> );
Query OK, 0 rows affected (0.05 sec)
-- 插入测试数据
mysql> insert into stu14 values ('2019-01-15 12:12:12','2019-01-15 12:12:12');
Query OK, 1 row affected, 1 warning (0.00 sec)
-- 查询
mysql> select * from stu14;
+---------------------+------------+
| t1 | t2 |
+---------------------+------------+
| 2019-01-15 12:12:12 | 2019-01-15 |
+---------------------+------------+
1 row in set (0.00 sec)
2、timestamp(时间戳)
datetime类型和timestamp类型表现上是一样的,他们的区别在于:
datetime从1000到9999,而timestamp从1970年~2038年(原因在于timestamp占用4个字节,和整形的范围一样,2038年01月19日11:14:07以后的秒数就超过了4个字节的长度)
mysql> create table stu15(
-> t1 timestamp
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> insert into stu15 values ('2038-01-19 11:14:07');
Query OK, 1 row affected (0.00 sec)
3、year
只能表示1901~2155之间的年份,因为只占用1个字节,只能表示255个数
mysql> create table stu16(
-> y1 year
-> );
Query OK, 0 rows affected (0.08 sec)
mysql> insert into stu16 values (2155);
Query OK, 1 row affected (0.00 sec)
4、time
可以表示时间,也可以表示时间间隔。范围是:-838:59:59~838:59:59
mysql> create table stu17(
-> t1 time
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into stu17 values ('12:12:12');
Query OK, 1 row affected (0.00 sec)
mysql> insert into stu17 values ('212:12:12');
Query OK, 1 row affected (0.00 sec)
mysql> insert into stu17 values ('-212:12:12');
Query OK, 1 row affected (0.00 sec)
mysql> insert into stu17 values ('839:00:00'); -- 报错
ERROR 1292 (22007): Incorrect time value: '839:00:00' for column 't1' at row 1
-- time支持以天的方式来表示时间间隔
mysql> insert into stu17 values ('10 10:25:25'); -- 10天10小时25分25秒
Query OK, 1 row affected (0.00 sec)
mysql> select * from stu17;
+------------+
| t1 |
+------------+
| 12:12:12 |
| 212:12:12 |
| -212:12:12 |
| 250:25:25 |
+------------+
4 rows in set (0.00 sec)
1.2.6 Boolean
MySQL不支持布尔型,true和false在数据库中对应的是1和0
mysql> create table stu18(
-> flag boolean
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> desc stu18;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| flag | tinyint(1) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into stu18 values (true),(false);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from stu18;
+------+
| flag |
+------+
| 1 |
| 0 |
+------+
2 rows in set (0.00 sec
小结:
boolean型在MySQL中对应的是tinyint。
1.2.6 练习题
电话号码一般使用什么数据类型存储? varchar
手机号码用什么数据类型 char
性别一般使用什么数据类型存储? char tinyint enum
年龄信息一般使用什么数据类型存储? tinyint
照片信息一般使用什么数据类型存储? binary
薪水一般使用什么数据类型存储? decimal