MySQL数据库字段详解

一、字段类型

学习目标：了解字段类型的作用，掌握MySQL中的字段类型，熟练运用字段类型来设计数据表

• 字段类型的作用
• 整数类型
• 小数类型
• 时间日期类型
• 字符串类型

1、字段类型作用

目标：了解为什么要有字段类型以及字段类型的作用

概念

字段类型：MySQL中用来规定实际存储的数据格式

• 字段类型在定义表结构时设定
• 设定好字段类型后，插入数据时必须与字段类型对应，否则数据错误
• MySQL有四大数据类型
  • 整数类型
  • 小数类型
  • 字符串类型
  • 时间日期类型

示例

规定类型的字段只能插入相应的数据格式

# 正确数据类型插入
insert into t_6 values(3,'444111222211110101','Lily','Lee');

# 错误数据类型插入
insert into t_6 values('hello','444444222211110102','Lucy','Lu');

小结

1、字段类型的作用就是强制规范录入的数据格式

• 规范数据的格式
• 保证数据的有效性

2、MySQL中有四种数据类型规范

• 整数类型：只能存储整数
• 小数类型：可以存储有效数值
• 字符串类型：存储字符串数据
• 时间日类类型：存储时间日期格式数据

2、整数类型

目标：了解MySQL中的整数机制，掌握常用的整数类型解决整数存储问题

概念

整数类型：有效的整数数据

• MySQL中为了数据空间的有效使用，设定了五种整数类型
  • 迷你整型：tinyint，使用1个字节存储整数，最多存储256个整数（-128~127）
  • 短整型：smallint，使用2个字节存储整数
  • 中整型：mediumint，使用3个字节存储整数
  • 标准整型：int，使用4个字节存储整数
  • 大整型：bigint，使用8个字节存储
• 数值型存储在MySQL中分为有符号（有负数）和无符号（纯正数）

数据类型	存储范围	字节
TINYINT	有符号值：-128到127(-27到27-1)；无符号值：0到255（0到28-1)	1
SMALLINT	有符号值：-32768到32767(-215到215-1)；无符号值：0到65535（0到216-1)	2
MEDIUMINT	有符号值：(-223到223-1)；无符号值：0到16777215（0到224-1)	3
INT	有符号值：(-231到231-1)；无符号值：0到4.294967E9（0到232-1)	4
BIGINT	有符号值：(-263到263-1)；无符号值：0到1.09951E12（0到264-1)	8

步骤

1、确定数据的格式是存储整数

2、预估整数的范围，选择合适的整数类型

3、确定整数是否需要符号（负数区间）

示例

1、设计一个表记录个人信息：年龄、头发数量

# 年龄：没有负数，正常年龄也不超过200岁，迷你整型无符号即可
# 头发数量：没有负数，大概在几百万根，所以标准整型无符号即可
create table t_7(
	age tinyint unsigned, # unsigned修饰整数，表示无符号（从0开始）
    haircount int unsigned
)charset utf8;

2、设计一个表记录4S店的汽车销量信息：库存数量、销量、采购量

# 4S店经常是先卖后进货，所以库存可能为负数，一个店铺的库存数通常不会太多，那么小整型即可
# 销量通常全国一个月也就几万台，所以基本上小整型就够了，但是为了保证后续可能存在的爆发，那么中整型也是绝对够了的，而且不会是负数
# 采购通常是正向采购，所以不会出现负数，而采购数量与销量持平就好

create table t_8(
    stock smallint,
    sales mediumint unsigned,
    purchase mediumint unsigned
)charset utf8;

小结

1、整型是用来存储整数数据的

2、整数数据也需要根据业务大小来选择合适的存储方式

• 迷你整型：存储数量不超过1个字节表示范围
• 小整型：存储数量不超过2个字节表示范围
• 中整型：存储数量不超过3个字节表示范围
• 整型：存储数量不超过4个字节表示范围
• 大整型：存储数量不超过8个字节表示范围

3、数值型类型在MySQL中默认是有符号的，即有正负

• 无符号需要使用unsigned修饰整型，即纯正数

4、一般开发中不会太计较一个或者两个字节（不愿意算），所以tinyint和int居多，其他较少

3、显示宽度

目标：了解显示宽度的实现，掌握在具体需求下使用显示宽度

概念

显示宽度：int(L)，整数在数据库中显示的符号（数字+符号）个数

• 显示宽度一般是类型能表示的最大值对应的数字个数（通过desc查看表字段显示）

• 显示宽度包含符号（如果允许为负数，-负号会增加一个宽度）

• 显示宽度可以主动控制：创建字段时加括号确定

• 显示宽度不会影响类型能表示的最大数值

• 可以通过zerofill让不够宽度的数值补充到对应宽度：在字段类型后使用zerofill

示例

1、有符号和无符号对应的宽度不一样

create table t_9(
	a tinyint,
    b tinyint unsigned
)charset utf8;

2、可以主动控制显示宽度

alter table t_9 add c tinyint(2) unsigned;

3、显示宽度不影响数据的大小

insert into t_9 values(1,1,1); #小于显示宽度
insert into t_9 values(100,100,100); #大于显示宽度

4、可以通过zerofill让小于显示宽度的数值前置补充0到显示宽度

alter table t_9 add d tinyint(2) zerofill; # 0填充只能针对正数
insert into t_9 values(1,1,1,1);

小结

1、显示宽度是显示整型能表示的最多符号数量

2、显示宽度能主动设置，但是绝对不会改变类型本身能表示的数据大小

3、可以通过zerofill来强制让不够宽度的数据补充前置0来达到显示宽度

• zerofill默认要求整型为无符号
• zerofill通常用来制作一些规范宽度的数据

4、小数类型（浮点型）

目标：了解浮点型的概念，掌握浮点数的应用场景

概念

浮点数：float/double，存储不是特别精确的数值数据

• 浮点数又称之为精度数据，分为两种
  • 单精度：float，使用4个字节存储，精度范围为6-7位有效数字
  • 双精度：double，使用8个字节存储，精度范围为14-15位有效数字
• 浮点数超过精度范围会自动进行四舍五入
• 精度可以指定整数和小数部分
  • 默认不指定，整数部分不超过最大值，小数部分保留2位
  • 可以指定：float/double(总长度,小数部分长度)
• 可以使用科学计数法插入数据：AEB，A * 10 ^ B

步骤

1、确定当前设计的字段的数据为不精确型数据（或者小数）

2、确定数据的大小或者精度的要求范围

• 6-7位有效数字使用float
• 14-15位有效数字使用double

3、确定精度的分布：整数部分和小数部分

示例

1、记录宇宙中恒星、行星的数量

# 数量属于不确定量级，所以精确的数据是无意义的，只能是个大概（绝大部分时候float就可以）
create table t_10(
	h_star float unsigned,
    x_star float(20,0) unsigned
)charset utf8;

insert into t_10 values(1234567890,1.2E10);

2、记录商品的价格

# 商品名字字符串
# 商品价格一般都允许带小数
create table t_11(
	goods_name varchar(20),
    goods_price float
)charset utf8;
insert into t_11 values('Nokia3310',199.99);
insert into t_11 values('Nokia6100',1999.9999);

小结

1、浮点数是用来记录一些不需要特别精确的数值或者小数数值的

• float：单精度，6-7位有效数字
• double：双精度，14-15位有效数字

2、浮点数能够表示很大的数值

3、浮点数可以指定整数部分和小数部分的有效数值区间

• float/double
  • 默认是整数不超过最大范围即可
  • 小数部分保留2位有效数字
• float/double(有效数位,小数部分有效位)
  • 整数部分为有效数位 - 小数部分
  • 数值如果超过整数部分就不让插入

4、因为浮点数会自动四舍五入，所以不要使用浮点数来存储对精度要求较高的数值

5、小数类型（定点型）

目标：了解定点型的概念和使用场景，运用定点型解决特定小数问题

概念

定点型：decimal，能够保证精度的小数

• 不固定存储空间存储
• 每9个数字使用4个字节存储
• 定点型可以指定整数部分长度和小数部分长度
  • 默认不指定，10位有效整数，0位小数
  • 可以指定：decimal(有效数位,小数部分数位)
  • 有效数位不超过65个
• 数据规范
  • 整数部分超出报错
  • 小数部分超出四舍五入

步骤

1、确定小数是否需要保证精度

2、确定有效数位长度

示例

记录个人资产情况：资产和负债

# 资产和负债应该都是精确的，小数部分可以到分
create table t_12(
    money decimal(14,2),
    bet decimal(10,2)
)charset utf8;

insert into t_12 values(1111111111.12,1111111.999);
insert into t_12 values(1111111111.12,99999999.999); # 错误：进位导致正数部分超过指定范围

小结

1、定点数是用来存储精确的小数的

2、定点数可以指定长度

• decimal：默认
  • 整数部分为10位
  • 小数部分为0
• decimal(有效位数,小数位数)
  • 整数部分为：有效位数 - 小数位数
  • 有效数位不超过65个

3、定点数的存储模式不是固定长度，所以数据越大占用的存储空间越长

6、字符串类型（定长型）

目标：了解定长型的语法和存储原理，掌握定长型的应用

概念

定长型：char(L)，指定固定长度的存储空间存储字符串

• 定长是指定存储长度
• 定长的长度是字符而不是字节
  • L的最大值是255
  • 实际存储空间：L字符数 * 字符集对应字节数
• 定长里存储的数据不能超过指定长度，但是可以小于指定长度
• 字符串数据使用单引号或者双引号包裹
• 1 和 ‘1’

步骤

1、确定数据类型为字符串（或不能用整数存储的超长数字符号）

2、确定数据长度基本一致（定长占用固定空间）

3、确定具体长度

示例

记录个人信息：身份证信息和手机号码

# 身份证为固定长度18位（数字）
# 手机号码是11位固定长度（数字）
create table t_13(
	id_number char(18),
    phone_number char(11)
)charset utf8;
insert into t_13 values('440111999912120304','13512345678');

小结

1、定长是固定存储空间

• 实际存储空间：L字符 * 字符集对应字节数

2、定长对应的是字符长度，而不是字节长度

3、字符串数据需要使用引号包裹具体数据

4、定长的访问效率较高，但是空间利用率不高

• 固定长度的数据使用定长

• 定长最大数据长度指定不超过255字符

7、字符串类型（变长型）

目标：了解变长型的语法和存储原理，掌握变长型的应用

概念

变长型：varchar(L)，根据实际存储的数据变化存储空间

• 变长型的存储空间是由实际存储数据决定的
• 变长型的L也是指字符而不是字节
  • L指定的是最大存储的数据长度
  • L最大值理论是65535
  • 变长需要额外产生1-2个字节，用来记录实际数据的长度
    • 数据长度小于256个，多1个字节
    • 数据长度大于256个，多2个字节
  • 实际存储空间：实际字符数 * 字符集对应字节数 + 记录长度
• 变长数据不能超过定义的最大长度

步骤

1、确定数据类型为字符串

2、确定数据是不规则的数据

3、确定最大长度

示例

记录个人信息：用户名、密码、姓名、身份证

# 用户名不确定长度，最长不超过50个字符
# 密码不确定长度，最长超过15个字符
# 姓名不确定长度，最长不超过10个字符
# 身份证固定长度，18个字符
create table t_14(
	`username` varchar(50),
    `password` varchar(15),
    `name` varchar(10),
    `id_number` char(18)
)charset utf8;

insert into t_14 values('username','password','name','444111999912121111');

小结

1、变长varchar是根据数据的长度实际计算存储空间

2、变长需要规定数据的最大长度，理论长度为65535个字符

3、变长字符串能够更好的利用存储空间

4、变长字符串需要有额外1-2个字节存储数据长度

• 不超过256个字符：1个字节
• 超过256个字符：2个字节

5、变长字符串在读取时需要进行长度计算，所以效率没有定长字符串高

8、字符串类型（文本字符串）

目标：了解文本字符串的作用，掌握文本字符串的使用规则

概念

文本字符串：text/blob，专门用来存储较长的文本

• 文本字符串通常在超过255个字符时使用
• 文本字符串包含两大类
  • text：普通字符
    • tinytext：迷你文本，不超过2 ^ 8 -1个字符
    • text：普通文本，不超过 2 ^ 16 - 1个字符
    • mediumtext：中型文本，不超过 2 ^ 24 - 1 个字符
    • longtext：长文本，不超过 2 ^ 32 - 1 个字符（4G）
  • blob：二进制字符（与text类似）
    • tinyblob
    • blob
    • mediumblob
    • longblob
• 文本字符串会自动根据文本长度选择适合的具体类型
• 一般在文本超过255个字符时，都会使用text（blob现在极少使用）

步骤

1、确定类型为文本类型

2、确定数据长度可能超过255个字符

3、使用text

示例

记录新闻信息：标题、作者和内容

# 标题一般不会超过50个字符，varchar
# 作者一般不会超过10个字符：varchar
# 内容通常都很长，使用text
create table t_15(
	author varchar(10),
    title varchar(50),
    content text
)charset utf8;

insert into t_15 values('佚名','给联合国的一封信','给联合国的一封信给联合国的一封信给联合国的一封信给联合国的一封信给联合国的一封信给联合国的一封信给联合国的一封信给联合国的一封信给联合国的一封信给联合国的一封信给联合国的一封信给联合国的一封信给联合国的一封信给联合国的一封信给联合国的一封信给联合国的一封信给联合国的一封信给联合国的一封信给联合国的一封信给联合国的一封信给联合国的一封信给联合国的一封信给联合国的一封信给联合国的一封信给联合国的一封信给联合国的一封信给联合国的一封信...');

小结

1、文本类型是专门用来存储长文本的

• text：普通文本字符
• blob：二进制文本字符

2、一般文本长度超过255的（较长）都使用text

3、text/blob根据数据存储长度有很多种，但是一般使用text/blob，因为文本会根据数据长度自适应选择

9、字符串类型（枚举）

目标：enum，了解枚举概念和存储原理，掌握枚举的实际应用

概念

枚举：一种映射存储方式，以较小的空间存储较多的数据

• 枚举是在定义时确定可能出现的可能
• 枚举在定义后数据只能出现定义时其中的一种
• 枚举类似一种单选框
• 枚举使用1-2个字节存储，最多可以设计65535个选项
• 枚举实际存储是使用数值，映射对应的元素数据，从1开始
• 枚举语法：enum(元素1,元素2,…元素N)

步骤

1、确定数据是固定的几种数据之一

2、使用枚举穷举相应的元素

3、数据存储只能选择穷举中的元素之一

示例

1、记录人群类型：小朋友、少年、青年、中年、老年，每个人实际只属于一种类别

# 要保证未来数据只能出现在某种可能中，所以要先列出来，可以使用enum
create table t_16(
	type enum('小朋友','少年','青年','中年','老年')
)charset utf8;

insert into t_16 values('少年');
insert into t_16 values('仙人');	# 不存在的数据不能插入

2、enum是建立映射关系，然后实际存储是数字，数值是按照元素顺序从1开始

# 可以使用字段 + 0来判定数据具体的效果（字符串转数值为0）
select type,type + 0 from t_16;
insert into t_16 values(5);

流程原理

1、枚举定义原理

枚举数据	映射值
数据1	1
数据2	2
…	…
数据N	N（小于65535）

2、数据存储（读取反过来）

小结

1、枚举是在定义时确定可能出现的元素，而后数据只能出现规定的元素之一的数据类型

2、枚举的存储是一种映射关系，对元素进行顺序编号，实际存储的是编号

3、使用枚举的作用

• 规范数据模型
• 优化存储空间

10、字符串类型（集合）

目标：了解集合概念和存储原理，掌握集合的应用

概念

集合：set，一种映射存储方式，以较小的空间存储较多的数据

• 集合是在定义时确定可能出现的元素进行穷举
• 集合在定义后数据只能出现定义时其中的元素（可以是多个）
• 集合类似一种多选框
• 集合使用1-8个字节存储数据，最多可以设计64个元素
• 集合实际存储是使用数值（二进制位），映射对应的元素数据，每个元素对应一个比特位
• 集合语法：set(元素1,元素2,…元素N)

步骤

1、确定数据是固定的几种数据组合

2、使用集合穷举相应的元素

3、数据存储只能选择穷举中的元素组合（多个使用逗号分隔）

示例

1、记录个人的球类爱好，有篮球、足球、羽毛球、网球、乒乓球、排球、台球、冰球

# 爱好可以是多种，并非固定的，但是只能从规定的类型中选择
create table t_17(
	hobby set('足球','篮球','羽毛球','网球','乒乓球','排球','台球','冰球')
)charset utf8;

insert into t_17 values('足球');
insert into t_17 values('冰球,台球,篮球');

2、集合建立的也是映射关系，映射方式是每个元素对应一个字节的比特位，从左边开始第一个对应字节从右边开始的第一位

# 可以通过字段 + 0的方式查看存储的具体数值
select hobby,hobby + 0 from t_17;

# 可以通过插入数值来组合元素，但是需要确定对应的十进制转换成二进制有对应的元素对应
insert into t_17 values(8);

/*
 8:00001000
 
 1:00000001
 
 冰球、台球、篮球
 11000010 => 1 + 2 ^ 1 + 1 * 2 ^ 6 + 1 * 2 ^ 7 = 2 + 64 + 128 = 194
*/

流程原理

1、集合定义原理

集合数据	映射位
数据1	00000001
数据2	00000010
…	…
数据8	10000000

2、数据存储（读取反过来）

小结

1、集合是在定义时确定可能出现的元素，而后数据只能出现规定的元素数据类型

2、集合的存储是一种映射关系，每个元素对应字节中的一个位，实际存储的是编号

• 数据存在：对应位为1
• 数据不存在：对应位为0

3、使用集合的作用

• 规范数据模型
• 优化存储空间

11、时间日期类型（年）

目标：了解年类型的存储方式和数据管理方式

概念

年：year，MySQL中用来存储年份的类型

• MySQL中使用1个字节存储年份
• year能够表示的范围是1901-2155年（256年）
  • year的特殊值是：0000
• year允许用户使用两种方式设计（效果一样）
  • year
  • year(4)

步骤

1、确定存储的数据是年份

2、确定年份的区间在1901-2155之间

3、使用year类型

示例

1、记录个人的出生年份

create table t_18(
	y1 year,
    y2 year(4)
)charset utf8;
insert into t_18 values(1901,2155);

2、Year类型允许使用2位数来插入，系统会自动匹配对应的年份

• 69以前：系统加上2000
• 69以后：系统加上1900

insert into t_18 values(69,70);

3、Year类型的特殊值是0000，可以使用00或者0000插入

insert into t_18 values(00,0000);

小结

1、year类型是MySQL用来存储年份信息的

2、year使用1个字节，所以只能表示256个年号，表示区间为1901-2155年

3、year数据可以用两种方式插入

• 直接插入4位年，1901-2155之间都可以
• 插入2位年，0-99之间，系统会自动以69和70为界限

4、因为year字段表示的范围有限，所以通常会使用字符串来存储（牺牲空间换安全）

12、时间日期类型（时间戳）

目标：了解时间戳的概念，掌握时间戳的特殊应用

概念

时间戳：timestamp，基于格林威治时间的时间记录

• MySQL中时间戳表现形式不是秒数，而是年月日时分秒格式
  • YYYY-MM-DD HH:II::SS
  • YYYYMMDDHHIISS
• timestamp使用4个字节存储
• timestamp的特点是所对应的记录不论哪个字段被更新，该字段都会更新到当前时间

步骤

1、确定类型需要使用年月日时分秒格式

2、确定当前字段需要记录数据的最近更新时间

3、使用timestamp时间戳

示例

1、记录商品库存的最后更新时间

create table t_19(
	goods_name varchar(10),
    goods_inventory int unsigned,
    change_time timestamp
)charset utf8;

insert into t_19 values('Nokia3110',100,'1971-01-01 00:00:00');
insert into t_19 values('Nokia7100',100,'19710101000000');

2、timestamp会在自己所在的记录任何位置被修改时自动更新时间

update t_19 set goods_inventory = 90;

注意：在MySQL8以后，取消了timestamp的默认自动更新，如果需要使用，需要额外使用属性： on update current_timestamp

alter table t_19 add c_time timestamp on update current_timestamp;

update t_19 set goods_inventory = 80;

小结

1、timestamp是用以时间戳的形式来保存时间的

• 时间戳算法是从格林威治时间开始
• MySQL中存储的是年月日时分秒格式

2、timestamp使用4个字节存储数据

• 表示范围是1971年1月1日0时0分0秒-2155年12月31日23是59分59秒
• timestamp可以使用0000-00-00 00:00:00

3、timestamp一般用来记录数据变化的，其他时候通常用整型保存真正的时间戳

• timestamp在MySQL8中需要主动使用on update current_timestamp才会自动更新

13、时间日期类型（日期）

目标：了解日期类型的存储原理，掌握日期类型的应用场景

概念

日期：date，用来记录年月日信息

• 使用3个字节存储数据
• 存储日期的格式为：YYYY-MM-DD
• 存储的范围是：1001-01-01~9999-12-31

步骤

1、确定存储的数据格式为日期类格式

2、确定数据格式为YYYY-MM-DD

3、使用date类型

示例

记录个人生日

create table t_20(
	name varchar(10),
    birth date
)charset utf8;

insert into t_20 values('Jim','2000-12-12');
insert into t_20 values('Tom','10011212');

小结

1、日期date是用来存储YYYY-MM-DD格式的日期的

2、date用3个字节存储，存储区间是1000 - 9999年，跨度很大

3、date可以在需要存储日期的地方放心使用

14、时间日期类型（日期时间）

目标：了解日期时间的存储格式，掌握日期时间的实际应用

概念

日期时间：datetime，用来综合存储日期和时间

• 使用8个字节存储数据
• 存储格式为：YYYY-MM-DD HH:II:SS
• 存储区间为：1000-01-01 00:00:00 到9999-12-31 23:59:59

步骤

1、确定要存储的时间格式包含日期

2、确定存储格式为：YYYY-MM-DD HH:II:SS

3、使用datetime

示例

记录个人具体的出生时间

create table t_21(
	name varchar(10),
    birth datetime
)charset utf8;

insert into t_21 values('Jim','2000-12-12 12:12:12');
insert into t_21 values('Tom','10011212182323');

小结

1、日期时间是综合存储日期和时间两部分的

2、日期时间datetime使用8个字节存储

3、datetime的存储区间为：1000-01-01 00:00:00 到 9999-12-12 23:59:59

4、实际开发中因为编程语言（PHP）的强大，实际存储的时候通常不会使用这种类型

• 占用较大存储空间
• 处理不够灵活（固定格式）
• 使用int unsigned存储时间戳然后利用PHPdate进行格式处理

15、时间日期类型（时间）

目标：了解时间的存储格式和实际效果

概念

时间：time，用来记录时间或者时间段

• 使用3个字节存储数据
• 数据范围是 -838:59:59 - 838:59:59
• 数据插入的格式分为两种
  • 时间格式：[H]HH:II:SS（[]表示可以没有）
  • 时间段格式：D HH:II:SS（D表示天）

步骤

1、确定要存储的类型是时间格式

2、确定格式类型为time能表示的格式

3、使用time存储

示例

记录用户登录的具体时间

# 具体登录时间可以使用时间戳（包含年月日时分秒信息）
# 也可以时间datetime格式，或者date+time双字段格式（具体后面学习范式时会知道该怎么用）
create table t_22(
	login_time1 int unsigned,
    login_time2 datetime,
    login_date date,
    login_time3 time
)charset utf8;

insert into t_22 values(12345678,'2000-12-12 12:12:12','2000-12-12','12:12:12');
insert into t_22 values(1234567,'2000-12-12 12:12:12','2000-12-12','3 12:12:12');

小结

1、时间格式time主要用来记录时间点或者时间段

2、time类型通常被用来做时间段计算：如多少天后的什么时间点（可以理解为过期检查）

16、总结

1、字段类型是用来规范数据的格式的

2、MySQL中有很多类型用来规范数据格式

• 整数类型（常用）
  • 常用类型：tinyint、int
• 小数类型（常用）
  • 常用类型：decimal、float
• 字符串类型（常用）
  • 常用类型：char、varchar、text
• 时间日期类型（不常用：通常使用真正时间戳存储数据，然后PHP进行灵活解读）

3、实际开发的时候，一定要仔细了解需求，根据需求判定好具体选用那种数据类型

• 最原始的维护能够具有最大的通用性（选中类型）
• 最小的消耗能够解决全部的问题（巧妙利用存储空间）

二、属性

学习目标：了解属性的类型和作用，掌握使用属性来完成表字段的约束和控制

• 空属性
• 默认值属性
• 主键
• 自动增长
• 唯一键
• 字段描述

1、属性作用

目标：了解属性的查看方式以及属性存在的意义

概念

属性：建立在字段类型之后，对字段除类型之外的其他约束

• 属性是在定义表字段的时候针对每个字段进行属性设定
• 设定好的属性可以通过查看表字段desc进行查看
• 数据在进行增删改（写）操作时需要在满足字段的要求同时还要满足属性的要求

示例

查看表属性：desc 表名

desc t_1;

# Field：字段名字
# Type：数据类型
# Null：是否为空（属性）
# Key：索引类型（属性）
# Default：默认值（属性）
# Extra：额外属性

小结

1、属性是用来MySQL用来增加字段规范和约束的

2、数据的写操作必须严格满足字段类型和属性要求

3、用好属性能够提升数据的有效性，方便未来进行数据操作和数据分析（数据真实性和有效性）

2、NULL属性

目标：了解Null属性的作用，掌握Null属性的应用

概念

NULL：数据是否允许为空

• 默认情况下数据是允许为空的
• 不为空设计：Not Null
• 数据为空一般不具备运算和分析价值，所以通常数据都需要设定为Not Null（不区分大小写）

步骤

1、数据类型确定

2、数据是否为空确定

• 允许为空：不用考虑Null属性（默认为Null）
• 不允许为空：Not Null

示例

1、用户信息表：用户名、密码、姓名、年龄、注册时间

create table t_23(
	username varchar(50) not null,
    password char(32) not null,
    name varchar(20),
    age tinyint unsigned,
    reg_time int unsigned not null
)charset utf8;

2、如果字段不能为空（Not Null），那么数据就必须满足条件：插入时不能为空的字段就需要主动提供值

insert into t_23 values('username','password','Jim',20,123456789);

# 错误操作：reg_time不能为空，而默认为空，所以系统报错
insert into t_23 (username,password) values('username','password');

小结

1、Null/Not Null属性是用来限定数据是否为Null值的

• 默认是允许为Null值
• 不允许为空：Not Null

2、一般有效的数据都必须设定为Not Null来保证数据的有效性

3、Default属性

目标：了解默认值的概念和作用，掌握默认值的使用以及Default关键字的特殊使用

概念

默认值：default，在设计表字段的时候给定默认数据，在后续字段操作（数据新增）的时候系统没有检测到字段有数据的时候自动使用的值

• 默认值在字段设计的时候使用（默认值需要满足数据类型规范）
• 默认值通常设计的是字段容易出现的数据
  • 一般字段的默认值默认是Null
• 默认值触发
  • 在系统进行数据插入时自动检测触发
  • 主动使用default关键字触发默认值

步骤

1、确定字段的数据类型

2、确定字段可能出现的数据会是某个经常出现的值

3、设定默认值

4、触发默认值

• 自动触发：数据插入时不给字段赋值
• 手动触发：数据插入时主动使用default关键字

示例

1、用户开户：银行卡账号、身份证号码、姓名、账户余额

create table t_24(
	account varchar(19) not null,
    id_card char(18) not null,
    name varchar(20) not null,
    money decimal(16,2) default 0.00 not null
)charset utf8;

2、默认值触发

# 默认触发
insert into t_24 (account,id_card,name) values('6226000000000001','44011120001212000x','Lily');

# 主动触发
insert into t_24 values('6226000000000002','440111200012120011','Tom',default);
insert into t_24 values('6226000000000003','440111200012120022','Jim',100);

小结

1、每个字段都有默认值

• 默认情况下基本为Null
• 主动设置后，默认值改变

2、默认值是在设置表字段的时候添加

3、默认值触发

• 自动触发
• 关键字手动触发

4、默认值通常用于一些不重要的字段，但是会出现常用值（或者初始值一样）

4、主键

目标：了解主键的概念，掌握主键的实际应用

概念

主键：primary key，用来保证整张表中对应的字段永远不会出现重复数据

• 主键在一张表中只能有一个
• 主键的另外一个特性是能够提升主键字段作为查询条件的效率（索引）
• 主键不能为空：Not Null（默认）
• 逻辑主键：数据没有具体业务意义，纯粹是一种数值数据
  • 逻辑主键通常是整数：int
  • 逻辑主键目的是方便检索和数据安全（不暴露数据真实信息）
• 复合主键：多个字段共同组成不能重复的数据
  • primary key(字段1,字段2,…字段N)
  • 联合主键使用不多，一般也不会超过2个字段

步骤

1、确定字段数据具有唯一性

2、确定数据不允许为空

3、确定数据会经常用于数据检索（条件查询）

4、使用主键primary key

5、一般每张表都会使用一个逻辑主键（id）

示例

1、银行账户信息：账户、姓名、余额

# 银行账户具有唯一性，不能重复，也不允许为空
create table t_25(
    account varchar(17) primary key,
    name varchar(20) not null,
    money decimal(16,2) not null default 0.00
)charset utf8;

# 复合主键
create table t_26(
	account varchar(17),
    name varchar(20),
    money decimal(16,2) not null default 0.00,
    primary key(account,name)
)charset utf8;

# 一般使用逻辑主键
create table t_27(
	id int unsigned primary key,
    account varchar(17) not null,
    name varchar(20) not null,
    money decimal(16,2) not null default 0.00
)charset utf8;

2、主键数据不允许重复

insert into t_27 values(1,'6226000000000001','Lily',default);

# 错误：主键1已经存在
insert into t_27 values(1,'6226000000000002','Tom',default);

# 联合主键就是联合字段加起来不重复即可
insert into t_26 values('6226000000000001','Tom',default);
insert into t_26 values('6226000000000002','Tom',default);

小结

1、主键的作用就是控制对应字段的数据具有唯一性（不能重复）

2、一张表只能有一个主键

3、虽然主键可以用来保证数据的唯一性，但是一般都是使用逻辑主键作为主键字段（保证唯一性还有其他方式，如唯一键）

4、通常也不怎么使用复合主键

5、主键管理

目标：了解主键的后期管理，主要是删除和新增

概念

主键管理：在创建表并且已经有数据后的维护

• 删除主键
• 追加主键
• 修改主键（先删除后新增）

示例

1、删除主键：主键只有一个，所以删除语法也比较特殊

alter table t_26 drop primary key;

2、后期新增主键：如果是针对业务主键需要保证字段数据没有Null数据且没有数据重复（一般主键都会在表创建时维护好）

alter table t_26 add primary key(account,name);

小结

1、主键的使用通常是在创建表的时候就会指定好

2、主键的维护实际使用较少，主要涉及的操作是删除和新增

6、自增长属性

目标：了解自增长的概念，掌握自增长的应用和内置控制

概念

自增长：auto_increment，被修饰的字段在新增时，自动增长数据

• 自增长只能是整数类型，而且对应的字段必须是一个索引（通常逻辑主键）
• 一张表只能有一个自动增长
• 自增长数据可以理解为一种默认值，如果主动给值，那么自动增长不会触发
• 自增长由两个变量控制
  • 初始值：auto_increment_offset，默认是1
  • 步长：auto_increment_increment，默认值也是1
  • 查看自增长控制：show variables like 'auto_increment%';

步骤

1、确定数据类型为整型

2、确定数据需要有规则的变化

• 从1开始
• 每次增长1
• 可以调整，但是通常有固定规则（一般不调整）

3、必须是一个索引字段（逻辑主键）

4、使用auto_increment

示例

1、记录学生信息：学号和姓名

# 学生信息：逻辑主键自动增长
create table t_28(
    id int primary key auto_increment,
	stu_no int(8) zerofill not null,
    stu_name varchar(20) not null
)charset utf8;

2、触发自增长

# 使用自增长（可以使用NULL或者default来触发）
insert into t_28 values(null,1,'Jim');
insert into t_28 values(default,2,'Tom');

# 主动控制：自增长的值会从当前最大的值开始自动增长
insert into t_28 values(10,3,'Lily');
insert into t_28 values(null,4,'Lucy');

小结

1、自增长auto_increment一般是配合逻辑主键实现自动增长

• 整型字段
• 存在索引（主键）

2、自增长的触发是通过不给值（默认值）实现自动计算

3、自增长是根据当前表中自增长列最大值运算

4、一张表中只能有一个自增长

7、自增长管理

目标：了解自增长的系统维护以及一些特殊性

概念

自增长管理：在某些特殊使用下，需要自增长按照需求实现

• 修改表中自增长的值：让下次自增长按照指定值开始

• 修改自增长控制：调整自增长的变化

示例

1、修改表中自增长的值，跳过一些值，直接从下次开始按照新的目标值出现

alter table t_28 auto_increment = 50;

注意：奇数会保留原值，偶数会自动加1（可能出现的情况）

2、修改自增长控制：步长和起始值（修改针对的是整个数据库，而非单张表）

set auto_increment_increment = 2;	# 当前用户当前连接有效（局部）
set @@auto_increment_increment = 2;	# 所有用户一直有效（全局）

小结

1、自增长通常不会修改，如果有规则要求必须修改，通常也会在数据库运行前修改好

2、如果碰到要修改操作的，通常会选择全局修改而不是局部修改

8、唯一键

目标：了解唯一键的概念，掌握唯一键的应用

概念

唯一键：unique key，用来维护数据的唯一性

• 一个表中可以有多个唯一键
• 唯一键与主键的区别在于唯一键允许数据为Null（而且Null的数量不限）
• 唯一键与主键一样，可以提升字段数据当做条件查询的效率（索引）
• 复合唯一键：多个字段共同组成
  • unique key(字段1,字段2,…字段N)
  • 一般不会出现，最多2个字段组成

步骤

1、确定数据列具有唯一特性

2、确定数据列不用作为主键

3、确定数据会经常用于检索条件

4、唯一键字段是否允许数据为空

• 为空：普通唯一键
• 不为空：not null（唯一键与主键效果一样）

5、使用唯一键

示例

1、用户表：用户名唯一，而且经常作为查询条件

create table t_29(
	id int primary key auto_increment,
    username varchar(50) unique comment '唯一键，可以直接在字段之后增加，使用unique关键字',
    password char(32) not null
)charset utf8;

insert into t_29 values(null,'username','password');
insert into t_29 values(null,Null,'password');
insert into t_29 values(null,Null,'password');

# 错误
insert into t_29 values(null,'username','password1');

2、学生成绩表：一个学生只能有一个学科成绩，但是可以有多个学科

# 学号和学科编号共同组成唯一
create table t_30(
	id int primary key auto_increment,
    stu_name varchar(20) not null,
    course varchar(20) not null,
    score decimal(5,2),
    unique key(stu_name,course)
)charset utf8;

insert into t_30 values(null,'Jim','Math',50);
insert into t_30 values(null,'Jim','English',80);

小结

1、唯一键的目标是保证对应字段数据的唯一性

• 唯一键不限定数据是否为Null（Null不参与唯一判定）
• 复合唯一键：允许多个字段共同组成唯一性

2、唯一键能够弥补主键只有一个的特性（不限定数据量）

3、唯一键使用的位置应该要确保该字段数据会用作数据检索条件

9、唯一键管理

目标：掌握唯一键的后续管理

概念

唯一键管理：在表创建后对唯一键的管理

• 删除唯一键：一张表中不止一个唯一键，所以删除方式是相对麻烦：alter table 表名 drop index 唯一键名字；
• 新增唯一键：alter table 表名 add unique key(字段列表)；

示例

1、删除表中已有的唯一键

alter table t_30 drop index `stu_name`;

2、追加唯一键

alter table t_30 add unique key `stu_course` (stu_name,course);

• 追加唯一键要保证字段里的数据具有唯一性

小结

1、唯一键一般也会在前期架构（创建表）时就会设置好

2、如果在后期数据庞大后进行数据库优化，可能会涉及到后期维护唯一键

3、唯一键的删除不能像主键那样删除，原因就是唯一键在表中不是唯一存在的，必须指定唯一键名字

10、comment属性

目标：了解comment的概念和作用

概念

描述：comment，是用文字描述字段的作用的

• comment代表的内容是对字段的描述
  • 方便以后自己了解字段的作用
  • 方便团队了解字段的作用
• 描述如果涉及到字符集（中文）一定要在创建表之前设置好客户端字符集（否则会出现描述乱码）

步骤

1、字段命名不是特别简单（见名知意）

2、使用comment增加简易描述

示例

学生成绩表

# 学生成绩表中通常是存储学生学号
# 学科通常也是学科代码
create table t_31(
	id int primary key auto_increment,
    stu_no varchar(10) not null comment '学号',
    course_no varchar(10) not null comment '课程号',
    score decimal(5,2) comment '考试成绩',
    unique key `stu_course` (stu_no,course_no) comment '学号和课程号组成唯一键'
)charset utf8;

• 如果要查看描述信息，需要使用show create table查看

小结

1、养成描述的良好习惯，为自己也为其他同事提供方便

2、并非所有字段都需要描述，但是对于自定义名字或者关联关系，都应该使用描述说清楚

11、外键约束

目标：了解外键的概念，掌握外键的实际应用

概念

外键：foreign key, 用来在两个表的数据之间建立关联。

• 外键在一张表中可以有一个或者多个

• 外键可以是一个列或者多个列。

• 语法：

  CONSTRAINT 外键名 FOREIGN KEY(外键字段名) REFERENCES 主键表名(主键字段名)

步骤

1、确定字段数据是否包含其他表主键

2、使用外键foreign key

创建部门表dept和员工表emp,并在员工表上创建外键。

#先创建部门表dept,包含部门编号、部门名称、部门地址
create table dept(
    id int primary key auto_increment,
    name varchar(20) not null comment '部门名称',
    location varchar(50) not null comment '部门地址'
)charset utf8;

#创建员工表emp，包含员工编号，员工姓名，员工部门编号
create table emp( 
    id int primary key auto_increment, 
    name varchar(20) not null comment '员工姓名', 
    dept_id int not null comment '员工所属部门编号', 
    constraint fk_emp_dept foreign key(dept_id) references dept(id) 
 )charset utf8;
 
 
 

外键删除：删除外键时，一定要指定外键名，因为一个表中，可以有多个外键。

alter table emp drop foreign key `fk_emp_dept`;

12、总结

1、字段属性是基于字段类型对数据控制后，再进行其他控制（辅助加强字段类型对数据的控制）

2、巧妙的利用好字段属性，能够帮助我们更好的保证数据的有效性、安全性

3、字段属性与字段类型通常是搭配使用，常见的属性有

• 主键（逻辑）+自增长
• 唯一键+Not Null（Null数据不参与运算，没有价值）
• 默认值以及default关键字的使用

三、数据库记录长度

目标：了解MySQL中数据库记录长度的概念，理解数据库字段设计时要控制字段的长度和不要超过数据库记录长度

概念

数据库记录长度：MySQL中规定一条记录所占用的存储长度最长不超过65535个字节

• 记录长度为表中所有字段预计占用的长度之和
• 所有字段只有允许Null存在，系统就会预留一个字节存储Null（多个Null也只要一个就好）
• 因为MySQL记录长度的存在，varchar永远达不到理论长度
  • GBK存储：65535（字符） * 2 + 2 = 131072（字节）
  • UTF8存储：65535（字符） * 3 + 2 = 196607（字节）
• 一般数据长度超过255个字符都会使用text/blob进行存储（数据存储不占用记录长度）

步骤

1、字符串字段如果超过长度255字符，使用text/blob替代

2、所有字段是否有允许为Null的，如果有要占用1个字节

3、计算字段所占用的预计总长度不要超过65535个字节

示例

1、GBK表能存储的最大varchar字符串长度

create table t_32(
	content varchar(65535)
)charset gbk;	# 错误

create table t_32(
	content varchar(32767)
)charset gbk;	# 错误

create table t_32(
	content varchar(32766)
)charset gbk;	

2、UTF8表能存储的最大varchar字符串长度

create table t_33(
	content varchar(65535)
)charset utf8;	# 错误

create table t_33(
	content varchar(21844)
)charset utf8;

3、Null也要占用一个字节

create table t_34(
    id tinyint,
	content varchar(21844)
)charset utf8;	# 错误

create table t_34(
    id tinyint not null,
	content varchar(21844) not null
)charset utf8;

小结

1、MySQL的记录长度是从设定表的时候就会检查所有字段加起来的预占用长度是否超过65535个字节

• 超过：创建失败
• 不超过：创建成功

2、创建表字段的时候要使用text/blob来避免长字符串出现，超过MySQL记录长度

3、Null是个细节，一条记录只要允许出现Null就会占用记录长度里的一个字节