首页 » 编程 » Python-8.MySQL数据库 » 正文

数据类型-数值类型

数值类型类型主要分为整型、浮点型。

一、整数类型

整数类型:tinyint, smallint, mediumint, int, bigint 

最常用的是:tinyint,int,bigint

作用:存储年龄,等级,id,各种号码等

tinyint

tinyint[(m)] [unsigned] [zerofill]    小整数,数据类型用于保存一些范围的整数。

数值范围:

有符号:-128~127

无符号:0~255

PS:Mysql中无布尔值,使用tinyint(1) 构造。

 

验证:

验证有符号的 tinyint:

MariaDB [db1]> create table t1(id tinyint);     # 创建一个t1表,设置id字段,类型为tinyint,默认为有符号的,即数字前有正负号
Query OK, 0 rows affected (0.01 sec)

MariaDB [db1]> desc t1;    # 查看t1表结构
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | tinyint(4) | YES  |     | NULL    |       |    # 默认宽度为4,因为字段是有符号的
+-------+------------+------+-----+---------+-------+
1 row in set (0.01 sec)

MariaDB [db1]> insert into t1 values(-129),(-128),(127),(128);    # 插入4条记录 -129, -128, 127, 128
Query OK, 4 rows affected, 2 warnings (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 2

MariaDB [db1]> select * from t1;    # 查看存入的记录值
+------+
| id   |
+------+
| -128 |    # -129存为了-128,因为字段是有符号的,所以最小值为-128
| -128 |    # -128依然是-128
|  127 |    # 127依然是127
|  127 |    # 128存为了127,因为是字段有符号的,所以最大值为127
+------+
4 rows in set (0.00 sec)

 

验证无符号的 tinyint:

MariaDB [db1]> create table t2(id tinyint unsigned);    # 创建一个t2表,设置id字段,unsigned表示设置为无符号的  
Query OK, 0 rows affected (0.01 sec)

MariaDB [db1]> desc t2;    # 查看t2表结构
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | tinyint(3) unsigned | YES  |     | NULL    |       |    # 宽度为3,因为字段是无符号的
+-------+---------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

MariaDB [db1]> insert into t2 values(-1),(0),(255),(266);    # 插入4条记录 -1, 0, 255, 256
Query OK, 4 rows affected, 2 warnings (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 2

MariaDB [db1]> select * from t2;    # 查看存入的记录值
+------+
| id   |
+------+
|    0 |    # -1存为了0,因为字段是无符号的,所以最小值为0
|    0 |    # 0依然是0
|  255 |    # 255依然是255
|  255 |    # 256存为了255,因为字段是无符号的,所以最大值为255
+------+
4 rows in set (0.00 sec)

 

int

int[(m)][unsigned][zerofill]    整数,数据用于保存一些范围的整数。

数值范围:

有符号:-2147483648 ~ 2147483647

无符号:0 ~ 4294967295

 

验证:

验证有符号的 int:

MariaDB [db1]> create table t3(id int);    # 创建t3表,设置id字段,类型为int,默认为有符号的整数
Query OK, 0 rows affected (0.01 sec)

MariaDB [db1]> desc t3;    # 查看t3表结构
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |    # 默认宽度为11,因为是有符号的
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

MariaDB [db1]> insert into t3 values(-2147483649),(-2147483648),(2147483647),(2147483648);    # 插入4条记录
Query OK, 4 rows affected, 2 warnings (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 2

MariaDB [db1]> select * from t3;    # 查看存入的记录值
+-------------+
| id          |
+-------------+
| -2147483648 |    # -2147483649存为了-2147483648,因为字段是有符号的,所以最小值为-2147483648
| -2147483648 |    # -2147483648依然是-2147483648
|  2147483647 |    # 2147483647依然是2147483647
|  2147483647 |    # 2147483648存为了2147483647,因为字段是有符号的,所以最大值为 2147483647
+-------------+
4 rows in set (0.00 sec)

 

验证无符号的 int:

MariaDB [db1]> create table t4(id int unsigned);    # 创建t4表,设置id字段,类型为int,unsigned设置为无符号的
Query OK, 0 rows affected (0.01 sec)

MariaDB [db1]> desc t4;    # 查看t4表结构
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | YES  |     | NULL    |       |    # 宽度为10,因为是无符号的
+-------+------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

MariaDB [db1]> insert into t4 values(-1),(0),(4294967295),(4294967296);    # 插入4条记录
Query OK, 4 rows affected, 2 warnings (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 2

MariaDB [db1]> select * from t4;    # 查看存入的记录值
+------------+
| id         |
+------------+
|          0 |    # -1存为了0,因为字段是无符号的,所以最小值为0
|          0 |    # 0依然是0
| 4294967295 |    # 4294967295依然是4294967295
| 4294967295 |    # 4294967296存为了4294967295,因为字段是无符号的,所以最大值为4294967295
+------------+
4 rows in set (0.00 sec)

 

bigint

bigint[(m)][unsigned][zerofill]    大整数,数据类型用于保存一些范围的整数。

数值范围:

有符号:-9223372036854775808 ~ 9223372036854775807

无符号:0 ~ 18446744073709551615

 

验证:

验证有符号的 bigint:

MariaDB [db1]> create table t5(id bigint);     # 创建t5表,设置id字段,类型为bigint,默认为有符号的
Query OK, 0 rows affected (0.01 sec)

MariaDB [db1]> desc t5;    # 查看t5表结构
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | bigint(20) | YES  |     | NULL    |       |    默认宽度为20
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

MariaDB [db1]> insert into t5 values(-9223372036854775809),(-9223372036854775808),(9223372036854775807),(9223372036854775808);    # 插入4条记录
Query OK, 4 rows affected, 2 warnings (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 2

MariaDB [db1]> select * from t5;    # 查看存入的记录值
+----------------------+
| id                   |
+----------------------+
| -9223372036854775808 |    # -9223372036854775809存为了9223372036854775808,因为字段是有符号的,所以最小值为-9223372036854775808
| -9223372036854775808 |    # -9223372036854775808依然是9223372036854775808
|  9223372036854775807 |    # 9223372036854775807依然是9223372036854775807
|  9223372036854775807 |    # 9223372036854775808存为了9223372036854775807,因为字段是有符号的,所以最大值为9223372036854775807
+----------------------+
4 rows in set (0.00 sec)

 

验证无符号的 bigint:

MariaDB [db1]> create table t6(id bigint unsigned);    # 创建t6表,设置id字段,类型为bigint,unsigned设置为无符号的
Query OK, 0 rows affected (0.01 sec)

MariaDB [db1]> desc t6;    # 查看t6表结构
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | bigint(20) unsigned | YES  |     | NULL    |       |    # 宽度和有符号的宽度一样,都是20
+-------+---------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

MariaDB [db1]> insert into t6 values(-1),(0),(18446744073709551615),(18446744073709551616);    # 插入4条记录
Query OK, 4 rows affected, 2 warnings (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 2

MariaDB [db1]> select * from t6;    # 查看存入的记录值
+----------------------+
| id                   |
+----------------------+
|                    0 |    # -1存为了0,因为字段是无符号的,所以最小值为0
|                    0 |    # 0依然是0
| 18446744073709551615 |    # 18446744073709551615依然是18446744073709551615
| 18446744073709551615 |    # 18446744073709551616存为了18446744073709551615,因为字段是无符号的,所以最大值为18446744073709551615
+----------------------+
4 rows in set (0.00 sec)

 

用 zerofill 测试整数类型的显示宽度

MariaDB [db1]> create table t7(id int(3) zerofill);     # 创建t7表,设置id字段,宽度为3,zerofill指当没有达到设定的宽度的时候,前面用0来填充
Query OK, 0 rows affected (0.01 sec)

MariaDB [db1]> desc t7;    # 查看t7表结构
+-------+--------------------------+------+-----+---------+-------+
| Field | Type                     | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| id    | int(3) unsigned zerofill | YES  |     | NULL    |       |
+-------+--------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

MariaDB [db1]> insert into t7 values(1),(11),(111),(1111);    # 插入4条记录
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [db1]> select * from t7;  # 查看存入记录值
+------+
| id   |
+------+
|  001 |    # 插入值为1,显示宽度不足3,前面填充2个0
|  011 |    # 插入值为11,显示宽度不足3,前面填充1个0
|  111 |    # 插入值为111,显示宽度刚好为3,不做任何改变
| 1111 |    # 插入值为1111,显示宽度大于3,不做任何改变,依然可以存入
+------+
4 rows in set (0.00 sec)

 

注意:

为整数类型字段指定类型宽度,仅仅只是指定查询结果的显示宽度,与存储范围无关,存储范围如下:

其实我们完全没有必要为整数类型指定显示宽度,使用默认的就可以了,默认的显示宽度,都是在最大值的基础上加1。

有符号和无符号的最大数字需要的显示宽度均为10,而针对有符号的最小值则需要11位才能显示完全,所以int类型默认的显示宽度为11是非常合理的。

 

int 的存储宽度是4个Bytes,即32个bit,即2**32

无符号最大值为:4294967296-1

有符号最大值为:2147483648-1

 

二、浮点型

浮点类型:float, double, decimal

定点数类型 dec 等同于 decimal

作用:存储薪资、身高、体重、体质参数等。

 

float

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

定义:单精度浮点数(非准确小数值),m是数字总个数,d是小数点后个数。m最大值为255,d最大值为30

有符号:-3.402823466E+38 to -1.175494351E-38

         1.175494351E-38 to 3.402823466E+38

无符号:1.175494351E-38 to 3.402823466E+38

精确度:随着小数的增多,进度变的不准确

 

double

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

定义:双精度浮点数(非准确小数值),m是数字总个数,d是小数点后个数。m最大值为255,d最大值为30

有符号:-1.7976931348623157E+308 to -2.2250738585072014E-308

2.2250738585072014E-308 to 1.7976931348623157E+308

无符号:2.2250738585072014E-308 to 1.7976931348623157E+308

精准度:随着小数的增多,精度比float要高,但也会变得不准确

 

decimal

decimal[(m[,d])] [unsigned] [zerofill]

定义:准确的小数值,m是数字总个数(负号不算),d是小数点后个数。 m最大值为65,d最大值为30

精准度:随着小数的增多,精度始终准确

对于精确数值计算时需要用此类型,decaimal能够存储精确值的原因在于其内部按照字符串存储。

 

验证:

float 类型:

MariaDB [db1]> create table t8(id float(256,31));
ERROR 1425 (42000): Too big scale 31 specified for 'id'. Maximum is 30.    # 超过30直接报错
MariaDB [db1]> 
MariaDB [db1]> create table t8(id float(256,30));
ERROR 1439 (42000): Display width out of range for 'id' (max = 255)    # 超过255直接报错
MariaDB [db1]> 
MariaDB [db1]> create table t8(id float(255,30));  # 建表成功
Query OK, 0 rows affected (0.01 sec)

double 类型:

MariaDB [db1]> create table t9(id double(256,31));
ERROR 1425 (42000): Too big scale 31 specified for 'id'. Maximum is 30.    # 超过30直接报错
MariaDB [db1]> 
MariaDB [db1]> create table t9(id double(256,30));
ERROR 1439 (42000): Display width out of range for 'id' (max = 255)    # 超过255直接报错
MariaDB [db1]> 
MariaDB [db1]> create table t9(id double(255,30));    # 建表成功
Query OK, 0 rows affected (0.01 sec)

decimal 类型:

MariaDB [db1]> create table t10(id decimal(256,31));
ERROR 1425 (42000): Too big scale 31 specified for 'id'. Maximum is 30.   # 超过30直接报错
MariaDB [db1]> 
MariaDB [db1]> create table t10(id decimal(256,30));
ERROR 1426 (42000): Too big precision 256 specified for 'id'. Maximum is 65.   # 超过65直接报错
MariaDB [db1]> 
MariaDB [db1]> create table t10(id decimal(65,30));   # 建表成功
Query OK, 0 rows affected (0.01 sec)

 

MariaDB [db1]> desc t8;  # 查看t8表结构
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | float(255,30) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
1 row in set (0.01 sec)

MariaDB [db1]> desc t9;  # 查看t9表结构
+-------+----------------+------+-----+---------+-------+
| Field | Type           | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| id    | double(255,30) | YES  |     | NULL    |       |
+-------+----------------+------+-----+---------+-------+
1 row in set (0.00 sec)

MariaDB [db1]> desc t10;  # 查看t10表结构
+-------+----------------+------+-----+---------+-------+
| Field | Type           | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| id    | decimal(65,30) | YES  |     | NULL    |       |
+-------+----------------+------+-----+---------+-------+
1 row in set (0.00 sec)

 

MariaDB [db1]> insert into t8 values(1.1111111111111111111111111111111);    # 插入小数到t8表,小数后31位
Query OK, 1 row affected (0.00 sec)

MariaDB [db1]> insert into t9 values(1.1111111111111111111111111111111);    # 插入小数到t9表,小数后31位
Query OK, 1 row affected (0.00 sec)

MariaDB [db1]> insert into t10 values(1.1111111111111111111111111111111);    # 插入小数到t10表,小数后31位
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [db1]> select * from t8;   # 查看t8存入记录
+----------------------------------+
| id                               |
+----------------------------------+
| 1.111111164093017600000000000000 |   # 随着小数的增多,精度开始不准确
+----------------------------------+
1 row in set (0.00 sec)

MariaDB [db1]> select * from t9;    # 查看t9存入记录
+----------------------------------+
| id                               |
+----------------------------------+
| 1.111111111111111200000000000000 |   # 精度比float要准确点,但随着小数的增多,同样变得不准确
+----------------------------------+
1 row in set (0.00 sec)

MariaDB [db1]> select * from t10;    # 查看t10存入记录
+----------------------------------+
| id                               |
+----------------------------------+
| 1.111111111111111111111111111111 |   # 精度始终准确,d为30,于是只留了30位小数
+----------------------------------+
1 row in set (0.00 sec)

 

 

 

 

发表评论

1 + 9 =