一.数据类型

整数类型（integer types）

数据类型	字节	有符号范围	无符号范围	说明
tinyint	1	-128 ~ 127	0 ~ 255	非常小的整数
smallint	2	-32,768 ~ 32,767	0 ~ 65,535	小整数
mediumint	3	-8,388,608 ~ 8,388,607	0 ~ 16,777,215	中等整数
int	4	-2,147,483,648 ~ 2,147,483,647	0 ~ 4,294,967,295	常用整数
bigint	8	±9.2 × 10¹⁸	0 ~ 18,446,744,073,709,551,615	大整数

浮点类型（floating point types）

数据类型	字节	精度说明	说明
float(m,d)	4	约 7 位有效数字	单精度浮点，非精确计算
double(m,d)	8	约 15~16 位有效数字	双精度浮点，非精确计算
decimal(m,d)	可变	精确小数，m=总位数，d=小数	精确计算，适合货币金额

字符串类型（string types）

数据类型	存储大小	最大长度	说明
char(n)	固定 n 字节	0 ~ 255字符	固定长度，适合状态等字段
varchar(n)	可变 + 1~2 字节	理论上最大65535字符	可变长度，常用文本字段
tinytext	最多 255 字节		很短的文本
text	最多 64kb		中等长度文本，不能索引全部
mediumtext	最多 16mb		较长文本
longtext	最多 4gb		超长文本
blob	同上（binary）		存储二进制数据（图片等）

1.char(n)若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此

2.char(n)固定长度，char（4）不管存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n<=255）或2个字节（n>255）,所以varchar(4)存入三个字符将占用4个字节

3.char类型的字符串检索速度要比varchar类型的快

日期时间类型（date and time types）

数据类型	格式	范围	说明
date	'yyyy-mm-dd'	1000-01-01 ~ 9999-12-31	仅日期
time	'hh:mm:ss'	-838:59:59 ~ 838:59:59	仅时间
datetime	'yyyy-mm-dd hh:mm:ss'	1000-01-01 ~ 9999-12-31	日期 + 时间
timestamp	同上	1970-01-01 ~ 2038-01-19（utc）	自动时区转换
year	'yyyy'	1901 ~ 2155	表示年份字段

若定义一个字段为timestamp，这个字段里的时间数据会随其他字段修改的时候自动刷新，所以这个数据类型的字段可以存放这条记录最后被修改的时间

特殊类型（enum, set）

数据类型	说明
enum('a','b',...)	枚举值，只能选择一个
set('a','b',...)	集合值，可以选择多个，最多 64 项

说明补充：

• 所有整数类型可加 unsigned 表示无符号。

• decimal 是精确存储，float/double 是近似计算，避免用于金额。

• char 定长，varchar 变长，text 不支持全文索引默认值。

• timestamp 会受当前时区影响，datetime 不会。

二.约束和属性设置（约束用户输入的信息）

表结构设计应同时考虑：数据类型 + 约束 + 属性

约束(用于限制表中数据的合法性，保证数据的一致性和完整性)

简写	全称	关键字	作用说明
PK	primary key	primary key	主键约束，唯一且非空。表中只能有一个。自动建立唯一索引，常用于 id 字段
UK	unique key	unique	唯一约束，字段值必须唯一但可为 null。可以有多个字段设置唯一约束
NN	not null	not null	非空约束，字段值不能为 null
FK	foreign key	foreign key	外键约束，强制当前字段值必须引用另一个表的主键，保证参照完整性，协调多个表

属性(字段属性，用于控制字段的行为或辅助信息,它们与约束协同定义字段语义与逻辑)

属性	作用说明	示例语法
default	设置字段默认值，当插入数据时未指定该字段则使用此值	age int default 18
auto_increment	自动增长，字段值会自动递增，常用于主键	id int primary key auto_increment
comment	字段注释，仅供开发者查看，不影响数据或逻辑	username varchar(50) comment '用户名'
unsigned	无符号整数，表示该字段不能为负数，适用于数值类型	price int unsigned

外键：也称外键约束，外面的键，对数据进行约束，一张表的一个字段指向另一个表的主键，那么该字段就称为外键

外键所在的表称之为子表（附表）；外键所指向的主键所在的表称之为父表（主表）

设置外键

实现将一个表的字段与另一个表的主键进行关联

添加外键

1.在创建表的时候就增加外键：在表字段之后使用foreign key

语法格式：foreign key(外键字段) references 主表(主键);

创建外键关联的父表

create table class(

id int primary key auto_increment,

name varchar(10) not null comment "班级姓名，不能为空",

room varchar(10) comment '教室：允许为空'

) charset utf8;

创建子表使用外键

create table student(

id int primary key auto_increment,

number char(10) not null unique comment "学号：不能重复0",

name varchar(10) not null comment "姓名",

c_id int,

foreign key(c_id) references class(id)

) charset utf8;

--增加外键：c_id是外键字段，class是引用表（父表），id是引用字段（主键）

查看数据表student的结构信息

desc student;

或

describe student;

PRI称为主键索引，MUL称为辅助索引

显示创建表student的完整 SQL 语句，包括字段定义、主键、外键、默认值、注释、索引、字符集、存储引擎等信息。

show create table student;

自动生成了外键名

2.再创建表之后增加外键：指定外键名字

创建外键语法格式

alter table 表名 add constraint 外键名 foreign key(外键字段) references 父表(主键字段)

创建没有外键信息的表

create table t_foreign(

id int primary key auto_increment,

c_id int

)charset utf8;

此时表中（t_foreign）没有创建外键

在没有外键的表中添加外键

alter table t_foreign add constraint class_foreign foreign key(c_id) references class(id);

此时有了外键信息

外键增加条件：外键字段必须与引用表（父表主键）的数据类型严格保持一致

删除外键

删除外键语法格式：alter table 表名 drop foreign key 外键名;

删除表（t_foreign）中的外键信息

alter table t_foreign drop foreign key class_foreign;

删除外键之后仅是把约束删掉了，但索引不会被删除

查看外键是否被删除

show create table t_foreign;

可以看到没有创建外键的信息了，外键的约束确实被删除了

查看外键

desc t_foreign;

show create table t_foreign;

外键作用说明

约束1：外键对子表的数据写操作学术（增加和更新）

如果子表中插入的数据所对应的外键在父表中不存在，创建不能成功

因此一旦建立外键关系，一定先添加父表，然后操作子表

约束2：外键对父表的数据约束

当父表操作一个记录，但是该记录被子表所引用的时候，那么父表的操作将会被限制（更新：主键和删除）

无法删除父表（class）中id=1的字段，因为已被子表引用。但当删除子表的引用后，父表可以成功删除

数据模式

SQL_mode（SQL模式）：保证数据录入合理性

例如：日期不能出现0000-00-00信息，月份只能是1-12，日期只能是1-31，一旦违反常识便会报错

例如：在进行数据运算时（select 1+1;）,除法运算的除数不能为0

例如：当定义数据类型为char(10)，不能超过字符长度，超过长度就报错

例如：设置only_full_group_by(5.7以后的特性)，禁止进行分组查询时，出现聚合信息1对多的显示输出

获取SQLmode设置的默认信息：

select @@sql_mode;

常见 SQL_MODE 模式解释

模式名	含义
STRICT_TRANS_TABLES	严格模式：插入无效数据（如超长字符串、非法数值）时报错（针对事务表）
STRICT_ALL_TABLES	严格模式：所有表（包括非事务表）都严格校验
ONLY_FULL_GROUP_BY	使用 GROUP BY 时必须显示分组的字段，避免不确定聚合行为
NO_ZERO_IN_DATE	禁止日期中出现 “00” 月或日（如 2025-00-01）
NO_ZERO_DATE	不允许将 0000-00-00 作为合法日期插入
ERROR_FOR_DIVISION_BY_ZERO	遇到除以零时报错，而不是返回 NULL
NO_ENGINE_SUBSTITUTION	如果指定的存储引擎不可用则报错（否则用默认引擎替代）
ANSI_QUOTES	将 " 识别为标识符（列名/表名），而不是字符串引号

注意：在数据迁移时（低版本迁移到高版本），需要关闭SQL_mode

临时关闭SQLmode(设置SQLmode为空)

set global sql_mode='';

退出数据库后然后重新进入