python-day41--数据库---数据类型
一、存储引擎决定了表的类型,而表内存放的数据也要有不同的类型,每种数据类型都有自己的宽度,但宽度是可选的
二、mysql 数据类型
1.数字:(宽度指的是显示宽度,与存储无关) 不用指定宽度,用默认就OK
默认都是有符号,要想变成无符号的,要先清空表内的记录再改
整型:tinyinit int bigint :个数,年龄,id,qq号,手机号
随着内部存储字节的增加,存储的数字大小也增加
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
======================================== tinyint[(m)] [unsigned] [zerofill] 小整数,数据类型用于保存一些范围的整数数值范围: 有符号: -128 ~ 127 无符号: ~ 255 PS: MySQL中无布尔值,使用tinyint(1)构造。 ======================================== int[(m)][unsigned][zerofill] 整数,数据类型用于保存一些范围的整数数值范围: 有符号: -2147483648 ~ 2147483647 无符号: ~ 4294967295 ======================================== bigint[(m)][unsigned][zerofill] 大整数,数据类型用于保存一些范围的整数数值范围: 有符号: -9223372036854775808 ~ 9223372036854775807 无符号: ~ 18446744073709551615
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
=========有符号和无符号tinyint========== #tinyint默认为有符号 MariaDB [db1]> create table t1(x tinyint); #默认为有符号,即数字前有正负号 MariaDB [db1]> desc t1; MariaDB [db1]> insert into t1 values -> (-129), -> (-128), -> (127), -> (128); MariaDB [db1]> select * from t1; +------+ | x | +------+ | -128 | #-129存成了-128 | -128 | #有符号,最小值为-128 | 127 | #有符号,最大值127 | 127 | #128存成了127 +------+ #设置无符号tinyint MariaDB [db1]> create table t2(x tinyint unsigned); MariaDB [db1]> insert into t2 values -> (-1), -> (0), -> (255), -> (256); MariaDB [db1]> select * from t2; +------+ | x | +------+ | 0 | -1存成了0 | 0 | #无符号,最小值为0 | 255 | #无符号,最大值为255 | 255 | #256存成了255 +------+ ============有符号和无符号int============= #int默认为有符号 MariaDB [db1]> create table t3(x int); #默认为有符号整数 MariaDB [db1]> insert into t3 values -> (-2147483649), -> (-2147483648), -> (2147483647), -> (2147483648); MariaDB [db1]> select * from t3; +-------------+ | x | +-------------+ | -2147483648 | #-2147483649存成了-2147483648 | -2147483648 | #有符号,最小值为-2147483648 | 2147483647 | #有符号,最大值为2147483647 | 2147483647 | #2147483648存成了2147483647 +-------------+ #设置无符号int MariaDB [db1]> create table t4(x int unsigned); MariaDB [db1]> insert into t4 values -> (-1), -> (0), -> (4294967295), -> (4294967296); MariaDB [db1]> select * from t4; +------------+ | x | +------------+ | 0 | #-1存成了0 | 0 | #无符号,最小值为0 | 4294967295 | #无符号,最大值为4294967295 | 4294967295 | #4294967296存成了4294967295 +------------+ ==============有符号和无符号bigint============= MariaDB [db1]> create table t6(x bigint); MariaDB [db1]> insert into t5 values -> (-9223372036854775809), -> (-9223372036854775808), -> (9223372036854775807), -> (9223372036854775808); MariaDB [db1]> select * from t5; +----------------------+ | x | +----------------------+ | -9223372036854775808 | | -9223372036854775808 | | 9223372036854775807 | | 9223372036854775807 | +----------------------+ MariaDB [db1]> create table t6(x bigint unsigned); MariaDB [db1]> insert into t6 values -> (-1), -> (0), -> (18446744073709551615), -> (18446744073709551616); MariaDB [db1]> select * from t6; +----------------------+ | x | +----------------------+ | 0 | | 0 | | 18446744073709551615 | | 18446744073709551615 | +----------------------+ ======用zerofill测试整数类型的显示宽度============= MariaDB [db1]> create table t7(x int(3) zerofill); MariaDB [db1]> insert into t7 values -> (1), -> (11), -> (111), -> (1111); MariaDB [db1]> select * from t7; +------+ | x | +------+ | 001 | | 011 | | 111 | | 1111 | #超过宽度限制仍然可以存 +------+
小数: 价格,身高,体重,余额
float :小数位数越多越不精准
double :小数位数越多越不精准
decimal:(如果用小数,则用推荐使用decimal)
精准(因为内部原理是以字符串形式去存)
eg:create table t1(salary float(5,2)); #5代表 salary代表一共有几位(正负号不算),2代表小数后几位
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
====================================== decimal[(m[,d])] [unsigned] [zerofill] 准确的小数值,m是数字总个数(负号不算),d是小数点后个数。 m最大值为65,d最大值为30。 特别的:对于精确数值计算时需要用此类型 decaimal能够存储精确值的原因在于其内部按照字符串存储。 ====================================== FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] 单精度浮点数(非准确小数值),m是数字总个数,d是小数点后个数。 有符号: -3.402823466E+38 to -1.175494351E-38, 1.175494351E-38 to 3.402823466E+38 无符号: 1.175494351E-38 to 3.402823466E+38 **** 数值越大,越不准确 **** ====================================== DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL] 双精度浮点数(非准确小数值),m是数字总个数,d是小数点后个数。 有符号: -1.7976931348623157E+308 to -2.2250738585072014E-308 2.2250738585072014E-308 to 1.7976931348623157E+308 无符号: 2.2250738585072014E-308 to 1.7976931348623157E+308 **** 数值越大,越不准确 ****
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
MariaDB [db1]> create table t8(salary float(5,2)); #总共5位,小数部分占2位,因而整数部分最多3位 MariaDB [db1]> insert into t8 values -> (3.3), -> (7.33), -> (9.335), -> (1000.1); MariaDB [db1]> select * from t8; +--------+ | salary | +--------+ | 3.30 | | 7.33 | | 9.34 | #4舍5入 | 999.99 | #小数最多2位,整数最多3位 +--------+ 验证
2.字符 类型 char 与varchar
字符(宽度指的是字符存时的个数):姓名,性别,职业,地址,职称,介绍
char 定长:简单粗暴,不够则用空格凑够固定长度存放起来,浪费空间,存取速度快
varchar 变长:精准,计算出待存放的数据的长度,节省空间,存取速度慢
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#注意:char和varchar括号内的参数指的都是字符的长度 #char类型:定长,简单粗暴,浪费空间,存取速度快 字符长度范围:0-255(一个中文是一个字符,是utf8编码的3个字节) 存储: 存储char类型的值时,会往右填充空格来满足长度 例如:指定长度为10,存>10个字符则报错,存<10个字符则用空格填充直到凑够10个字符存储 检索: 在检索或者说查询时,查出的结果会自动删除尾部的空格,除非我们打开pad_char_to_full_length SQL模式(SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';) #varchar类型:变长,精准,节省空间,存取速度慢 字符长度范围:0-65535(如果大于21845会提示用其他类型 。mysql行最大限制为65535字节,字符编码为utf-8) 存储: varchar类型存储数据的真实内容,不会用空格填充,如果'ab ',尾部的空格也会被存起来 强调:varchar类型会在真实数据前加1-2Bytes的前缀,该前缀用来表示真实数据的bytes字节数(1-2Bytes最大表示65535个数字,正好符合mysql对row的最大字节限制,即已经足够使用) 如果真实的数据<255bytes则需要1Bytes的前缀(1Bytes=8bit 2**8最大表示的数字为255) 如果真实的数据>255bytes则需要2Bytes的前缀(2Bytes=16bit 2**16最大表示的数字为65535) 检索: 尾部有空格会保存下来,在检索或者说查询时,也会正常显示包含空格在内的内容
1). char填充空格来满足固定长度,但是在查询时却会很不要脸地删除尾部的空格(装作自己好像没有浪费过空间一样),然后修改sql_mode让其现出原形: SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> create table t1(x char(5),y varchar(5)); Query OK, 0 rows affected (0.26 sec) #char存5个字符,而varchar存4个字符 mysql> insert into t1 values('你瞅啥 ','你瞅啥 '); Query OK, 1 row affected (0.05 sec) #在检索时char很不要脸地将自己浪费的2个字符给删掉了,装的好像自己没浪费过空间一样,而varchar很老实,存了多少,就显示多少 mysql> select x,char_length(x),y,char_length(y) from t1; +-----------+----------------+------------+----------------+ | x | char_length(x) | y | char_length(y) | +-----------+----------------+------------+----------------+ | 你瞅啥 | 3 | 你瞅啥 | 4 | +-----------+----------------+------------+----------------+ row in set (0.00 sec) #略施小计,让char现出原形 mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH'; Query OK, 0 rows affected (0.00 sec) #这下子char原形毕露了...... mysql> select x,char_length(x),y,char_length(y) from t1; +-------------+----------------+------------+----------------+ | x | char_length(x) | y | char_length(y) | +-------------+----------------+------------+----------------+ | 你瞅啥 | 5 | 你瞅啥 | 4 | +-------------+----------------+------------+----------------+ row in set (0.00 sec)
2). 引出: select * from t1 where y='abc ';
mysql 比较的时候 会把存储时末尾的空格去掉,也会把查找条件末尾的空格去掉
注意:如果字符长度超过了255个字符,就不要用char 与varchar了,存储数据量较小的音频图片等,放在mysql服务器的硬盘中,然后数据库中存的就是文件的路径;如果是大的音频等,要放在单独文件服务器上,数据库中存的就是文件的链接,用户请求文件的时候,数据库把链接返回给用户,用户拿着链接到文件服务器中下载音频。
3.日期 类型 :最常用 datetime
#注册时间
datetime 2017-09-06 10:39:49
#出生年月日,开学时间
date:2017-09-06
#聊天记录,上课时间
time:10:39:49
#出生年
year:2017
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 create table student( 2 id int, 3 name char(5), 4 born_date date, 5 born_year year, 6 reg_time datetime, 7 class_time time 8 ); 9 10 insert into student values(1,'alex',now(),now(),now(),now()); 11 结果: 12 mysql> select * from student; 13 +------+------+------------+-----------+---------------------+------------+ 14 | id | name | born_date | born_year | reg_time | class_time | 15 +------+------+------------+-----------+---------------------+------------+ 16 | 1 | alex | 2017-09-06 | 2017 | 2017-09-06 16:22:08 | 16:22:08 | 17 +------+------+------------+-----------+---------------------+------------+ 18 1 row in set (0.00 sec) 19 20 21 insert into student values(2,'alex','2017-09-06','2017','2017-09-06 10:39:00','08:30:00‘) 22 结果: 23 mysql> select * from student; 24 +------+------+------------+-----------+---------------------+------------+ 25 | id | name | born_date | born_year | reg_time | class_time | 26 +------+------+------------+-----------+---------------------+------------+ 27 | 1 | alex | 2017-09-06 | 2017 | 2017-09-06 16:22:08 | 16:22:08 | 28 | 2 | alex | 2017-09-06 | 2017 | 2017-09-06 10:39:00 | 08:30:00 | 29 +------+------+------------+-----------+---------------------+------------+ 30 2 rows in set (0.00 sec)
datetime 与 timestamp 的区别
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
在实际应用的很多场景中,MySQL的这两种日期类型都能够满足我们的需要,存储精度都为秒,但在某些情况下,会展现出他们各自的优劣。下面就来总结一下两种日期类型的区别。 1.DATETIME的日期范围是1001——9999年,TIMESTAMP的时间范围是1970——2038年。 2.DATETIME存储时间与时区无关,TIMESTAMP存储时间与时区有关,显示的值也依赖于时区。在mysql服务器,操作系统以及客户端连接都有时区的设置。 3.DATETIME使用8字节的存储空间,TIMESTAMP的存储空间为4字节。因此,TIMESTAMP比DATETIME的空间利用率更高。 4.DATETIME的默认值为null;TIMESTAMP的字段默认不为空(not null),默认值为当前时间(CURRENT_TIMESTAMP),如果不做特殊处理,并且update语句中没有指定该列的更新值,则默认更新为当前时间。 datetime与timestamp的区别
4 .枚举与集合
enum枚举:规定一个范围,可有多个值,但是为该字段传值时,只能取规定范围中的一个
set集合:规定一个范围,可有多个值,但是为该字段传值时,可以取规定范围中的一个或多个
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
create table consumer( name varchar(50), sex enum('male','female'), level enum('vip1','vip2','vip3','vip4','vip5'), #在指定范围内,多选一 hobby set('play','music','read','study') #在指定范围内,多选多 ); insert into consumer values ('egon','male','vip5','read,study'), ('alex','female','vip1','girl'); select * from consumer; +------+--------+-------+------------+ | name | sex | level | hobby | +------+--------+-------+------------+ | egon | male | vip5 | read,study | | alex | female | vip1 | | #girl 不在hobby中 所以表中没有 +------+--------+-------+------------+