数据库学习笔记2-列类型 | 8月更文挑战

259 阅读12分钟

这是我参与8月更文挑战的第2天,活动详情查看:8月更文挑战

数值类型

  • 数值型数据都是数值
  • 系统将数值型分为整数型和小数型

整数型

  • 因为sql需要考虑如何节省磁盘空间,因为系统将整型又细分为5类
    1. tinyint:迷你整型,使用一个字节存储,表示的状态为256种(常用)
    2. smallint:小整型,使用2个字节存储,表示的状态最多为65536种
    3. mediumint:中整型, 使用3个字节存储
    4. int: 标准整型, 使用4个字节存储(常用)
    5. bigint: 大整型,使用8个字节存储
  • 创建一个整型的表
create table if not exists my_int(
	int_1 tinyint,
	int_2 int
)charset utf8;
  • 插入数据
-- 有效数据
-- 查看数据为正确数据100,100
insert into my_int values(100,100);

-- 无效数据: 类型限定
-- 因为只能存储数值型,类型限制
insert into my_int values('a','188');	 

-- 错误: 超出范围
-- 因为tinyint是有符号的,存储范围是-128-127,若是无符号则存储范围是0-255
insert into my_int values(255,10000);
  • sql中的数值类型全部都是默认有符号的: 就是分正负
  • 在需要无符号数据的时候,需要给数据类型进行限定-unsigned,无符号从0开始
  • 增加一个无符号的字段
alter table my_int add int_3 tinyint unsigned;

-- 增加一条数据,此时的255可以存储进去
insert into my_int values(127,222,255);
  • 产看数据结构的时候,会在type这列发现列类型后面会存在int(11),tinyint(3) unsigned,tinyint(4)的结构
  • tinyint(3) unsigned 代表无符号的数据,3代表显示宽度,255是3位的宽度,正号省略
  • tinyint(4)代表有符号的数据,4代表显示宽度(包括符号),-111是一个4位的显示宽度(包括负号)
  • 显示宽度没有什么特殊的意义,仅仅只是默认的告诉用户可以显示的形式而已,虽然可以自定义显示宽度的长度,但是并不能控制其本身存储的数据大小,即显示宽度修改并不能影响存储的数据大小
  • 单纯的字段宽度并没有实际意义,因此会搭配零填充(zerofill)进行使用,这样会当数据宽度不够显示宽度的时候,会自动配合zerofill进行填充,在其前面使用0进行填充至显示宽度的要求宽度,zerofill会导致数值自动变成无符号
-- 新增一个宽度为2的零填充行\
alter table my_int add column tinyint(2) zerofill;

-- 插入数据
insert into my_int values(100,100,100,1),(200,200,200,2);

-- 插入的数据1,2 会显示为01,02
  • zerofill配合显示宽度意义:保证数据格式一致

小数型

  • SQL中将浮点型分为浮点型和定点型
  1. 浮点型: 小数点浮动,精度有限,会丢失精度
  2. 丁典型: 小数点固定,精度固定,不会丢失精度

浮点型

  • 浮点型数据是一种精度型数据:超出指定范围之后会丢失精度(进行四舍五入)
  • 浮点型:float、double两种类型
  • 浮点型使用方式:
    1. 直接float:表示没有小数部分
    2. float(M,D):M代表总长度,D代表小数部分长度,整数部分长度等于M-D
-- 创建表
create table if not exists my_float(
	float_1 float, -- 没有小数部分
	float_2 float(10,2), -- 总长度为10,整数为8
	float_3 float(5,2)
)charset utf8;

-- 插入数据 
-- 插入数据
insert into my_float values(1000.10,1000.10,1000.10); -- 符合条件
insert into my_float values(1234567890,12345678.90,1234.56); -- 符合条件
insert into my_float values(3e38,3.01e7,1234.56);
insert into my_float values(9999999999,99999999.99,9999.99); -- 最大值

-- 超出长度插入数据
insert into my_float values(123456,1234.123456768,123.9876543);	-- 小数部分OK
insert into my_float values(123456,1234.12,12345.56);	-- 整数部分超出
  • 浮点型数据插入:整型部分不能超出长度的,但是小数部分是可以超出长度的(系统会自动四舍五入)
  • 若是浮点数超出精度范围是一定会进行四舍五入的
  • 浮点数若是因为系统进位导致整数部分超出指定长度,系统是允许成立的

定点型

  • 绝对的保证整数部分不会被四舍五入(不会丢失精度)
  • 小数部分有可能会被四舍五入(理论上小数部分也不会丢失精度)
-- 创建定点型表,与float对比
create table if not exists my_decimal(
	f1 float(10,2),
	d1 decimal(10,2)
)charset utf8;

-- 插入数据
insert into my_decimal values(12345678.90,12345678.90); -- 有效数据
insert into my_decimal values(1234.123456,1234.1234356); -- 小数部分超出:ok

insert into my_decimal values(99999999.99,99999999.99); -- 没有问题
insert into my_decimal values(99999999.99,99999999.999); -- 进位超出范围
  • 定点数的整数部分一定不能超出长度(进位不可以),小数部分的长度可以随意超出(系统自动四舍五入)
  • 浮点数如果进位导致长度溢出是没有问题的,但是定点数不行

字符串类型

char

  • 定长字符串:磁盘(二维表)在定义结构的时候,就已经确定最终数据的存储长度
  • char(L):L代表length,可以存储的长度,单位为字符,最大长度值可以为255
  • cahr(4):在utf8环境下,需要4*3=12个字节

varchar

  • 变长字符串:varchar,在分配空间的时候,按照最大的空间分配,但是实际上最终用了多少,是根据具体的数据来确定

  • Varchar(L): L表示字符长度 理论长度是65536个字符, 但是会多处1到2个字节来确定存储的实际长度,当数据小于255时,会多1个字节来确定实际的存储长度,当数据大于255,小于65535时,会多出2个资金额来确定实际的存储长度, 但是实际上如果长度超过255,既不用定长也不用变长, 使用文本字符串text

  • Varchar(10): 存储10个汉字, utf8环境, 10 * 3 + 1 = 31(bytes)

    ​ 存储3个汉字: 3 * 3 + 1 = 10(bytes)

  • 定长、变长选择

    • 定长的磁盘空间比较浪费,但是效率高。若是数据基本上确定长度都一样,使用定长,例如手机号码、身份证等
    • 变长的磁盘空间比较节省,但是效率低,如果数据不能确定长度,不同的数据有变化,使用变长,例如姓名、地址等

text、blob

  • 如果数据量比较大,一般超过255个字符就会使用文本字符串
  • 文本字符串根据村粗的数据格式进行分类:text和blob
    • text:存储文字,二进制数据实际上都是存储路径
    • blob:存储二进制数据,基本不用

enum

  • 枚举字符串:enum。事先将多有可能出现的结果都设计好,实际上存储的数据必须是规定好的数据中的一个,相当于html中的radio
  • 枚举的使用方式
    • 定义:enum(可能出现的元素列表),enum('man','woman','secrecy')
    • 使用:存储数据,只能存储上面定义好的数据
-- 创建表
create table if not exists my_enum(
	gender enum('man','woman','secrecy')
)charset utf8;

insert into my_enum values('man'),('woman'),('secrecy'); -- 插入成功

insert into my_enum values('男');	-- 错误: 没有该元素

-- 将字段结果取出来进行+0运算,会发现gender+0 是数字1,2,3分别对应man,woman,secrecy
select gender + 0, gender from my_enum;

-- 数值插入枚举元素,这样插入数据1代表man,2代表woman,3代表secrecy
insert into my_enum values(1),(2),(3);
  • 枚举的作用
  1. 规范数据格式,数据只能是规定的数据中的其中一个
  2. 节省存储空间(枚举有一个别名:单选框),因为枚举存储的是数值而不是字符串本身
  • 枚举存储,是将所有规定好的数据在内存中按照元素出现的顺序,从1开始编号,所以也可以使用编号进行插入数据
  • 枚举原理: 枚举在进行数据规范的时候(定义的时候),系统会自动建立一个数字与枚举元素的对应关系(关系放到日志中): 然后在进行数据插入的时候,系统自动将字符转换成对应的数字存储, 然后在进行数据提取的时候, 系统自动将数值转换成对应的字符串显示.
  • 因为枚举实际存储的是数值,所以可以直接插入数值.

set

  • 集合和枚举很类似,实际存储的是数值,而不是字符串(集合相当与checkbox,多选框)
  • 集合使用方式
    • 定义: set(元素列表)
    • 使用:可以使用列表中的元素(多个),使用逗号分隔
-- 创建表
create table if not exists my_set(
hobby set('篮球','足球','乒乓球','羽毛球','排球','台球','网球','棒球')
)charset utf8;

-- 插入数据
insert into my_set values('足球,台球,网球');
insert into my_set values(3); 

-- 查看集合数据
select hobby + 0, hobby from my_set;
-- 98转成二进制 = 64 + 32 + 2 = 01100010, 对应的存储是 足球,台球,网球
-- 3转成二进制 = 64 + 32 + 2 = 00000011, 所以insert into my_set values(3); 实际存储的是'篮球','足球'

-- 颠倒元素出现的顺序,不会影响存储,系统还是按照元素列表去匹配顺序
insert into my_set values('网球,台球,足球');
  • 集合主要用户就是规范数据和节省空间,因为后台语言维护成本比较高,所以使用很少

时间日期类型

  • Datetime: 时间日期, 格式是YYYY-mm-dd HH:ii:ss,表示的范围是从1000到9999年,有0值: 0000-00-00 00:00:00
  • Date: 日期,就是datetime中的date部分
  • Time: 时间(段), 指定的某个区间之间, -时间到+时间
  • Timestamp: 时间戳, 并不是时间戳,只是从1970年开始的YYYY-mm-dd HH:ii:ss格式与datetime完全一致
  • Year: 年份,两种形式, year(2)和year(4): 1901-2156

-- 创建时间日期表
create table my_date(
d1 datetime,
d2 date,
d3 time,
d4 timestamp,
d5 year
)charset utf8;


-- 插入数据
insert into my_date values('2015-9-28 11:50:36','2015-9-28','11:50:54','2015-9-28 11:51:08',2015);

-- 时间使用负数
insert into my_date values('2015-9-28 11:50:36','2015-9-28','-11:50:54','2015-9-28 11:51:08',2015);
insert into my_date values('2015-9-28 11:50:36','2015-9-28','-211:50:54','2015-9-28 11:51:08',2015);
insert into my_date values('2015-9-28 11:50:36','2015-9-28','-2 11:50:54','2015-9-28 11:51:08',2015); -- -2过去2天:48

-- year可以使用2位或者4位
insert into my_date values('2015-9-28 11:50:36','2015-9-28','11:50:54','2015-9-28 11:51:08',69);
insert into my_date values('2015-9-28 11:50:36','2015-9-28','11:50:54','2015-9-28 11:51:08',70);

-- timestamp: 修改记录
update my_date set d1 = '2015-9-28 11:55:45' where d5 = 2069;

mysql记录长度

  • mysql中规定,任何一条记录最长不能超过65535个字节(varchar永远达不到理论值)

    • Utf8 下varchar的实际顶配: 21844字符,一个字符占通常占3个字节, 21844 * 3 + 2 = 65534
    • GBK下的varchar的实际顶配: 32766字符,一个字符占通常占2个字节,32766 * 2 + 2 = 65534
    -- 求出varchar在utf8和GBK下的实际最大值
    create table my_utf8(
    name varchar(21844) -- 21844 * 3 + 2 = 65532 + 2 = 65534
    )charset utf8;
    
    create table my_gbk(
    name varchar(32766) -- 32766 * 2 + 2 = 65532 + 2 = 65534
    )charset gbk;
    
    
    • 若是想将35565个字节全部使用
    create table my_utf81(
    age tinyint,	    -- 1
    name varchar(21844) -- 21844 * 3 + 2 = 65532 + 2 = 65534
    )charset utf8;
    
    create table my_gbk1(
    age tinyint,	    -- 1	
    name varchar(32766) -- 32766 * 2 + 2 = 65532 + 2 = 65534
    )charset gbk;
    
  • mysql记录中,如果有任何一个字段允许为空,那么系统会自定从整个记录中保留一个字节来存储NULL(若是想释放NULL所占的字节,必须保证所有的字段都不允许为空)

-- 释放NULL
create table my_utf82(
age tinyint not null,	    -- 1
name varchar(21844) not null -- 21844 * 3 + 2 = 65532 + 2 = 65534
)charset utf8;

create table my_gbk2(
age tinyint not null,	    -- 1	
name varchar(32766) not null -- 32766 * 2 + 2 = 65532 + 2 = 65534
)charset gbk;
  • mysql记录中text文本字符串不占用记录长度,是额外进行存储的,但是text文本字符串也是属于记录的一部分,所以还是会占据记录中的部分长度:占用10个字节,用来保存数据的地址以及长度
-- text占用十个字节长度
create table my_text(
name varchar(21841) not null, -- 21841 * 3 + 2 = 65523 + 2 = 65525
content text not null	      -- 10		
)charset utf8;