一、数据类型介绍¶
数据类型从数据存储底层机制来看，主要和内存中如何存储数据信息有关；
在数据库服务中，每一个常量、变量和参数都有数据类型，数据类型用来指定数据的存储格式、约束和有效范围
| 序号 | 类型 | 类型细化 | 说明 |
|---|---|---|---|
| 01 | 数字类型 | ||
| 整型（数字/整数） | int | 普通整型数字 | |
| tinyint | 微小整型数字 | ||
| bigint | 超大整型数字 | ||
| 浮点（数字/小数） | float | 单精度浮点数 | |
| double | 双精度浮点数 | ||
| decimal | 定点数 | ||
| 02 | 字符串类型 | ||
| 字符（字符/符号/整数） | char（8） | 定长字符类型 | |
| varchar（8） | 变长字符类型 | ||
| enum | 枚举类型 | ||
| set | 集合类型 | ||
| text | 大文本类型 | ||
| 03 | 时间类型 | ||
| date | 日期类型 | ||
| time | 时间类型 | ||
| datetime | 日期时间类型（1000~9999）占8字节 | ||
| timestamp | 时间戳类型（1970~2038）格林威治时间 占4字节 | ||
| 04 | 二进制类型 | ||
| 05 | json类型 |
数据类型区别分析：
| 类别 | 数据类型细分 | 差异区别 |
|---|---|---|
| 整数类型 | tinyint | 占用1字节 有符号取值 -128~127 无符号取值 0 ~ 255（最大3位数） |
| int | 占用4字节 有符号取值 -2147483648 ~ 2147483647 无符号取值 0 ~ 4294967295（最大10位数） | |
| BIGINT | 占用8字节 ... 0~2^64-1（最大20位数） |
说明：9位数是亿，10位数是十亿，13位数是万亿，14位数是兆，19位数是万兆，20位数是京；
字符类型区别分析：
| 类别 | 数据类型细分 | 差异区别 |
|---|---|---|
| 字符类型 | char(n) | 表示定长的字符串类型，n表示可以存储字符的字节上限（n取值 0~255） |
| varchar(n) | 表示变长的字符串类型，n表示可以存储字符的字节上限（n取值 0~65535） |
详细的数据类型知识参考链接：https://m.php.cn/article/460317.html
二、数据库属性约束设置¶
数据类型约束与属性：
在数据库服务中进行数据存储时，类似于在一个execl表中存储数据一样，如果没有对表的字段进行约束和限制，是可以随意存储数据的；
但是，在数据表的某些字段上，是有特殊含义的，如果随意进行存储数据，会造成存储信息的混乱，因此引入了约束与属性概念；
通过数据类型设置的约束与属性，可以让数据库服务限制人类录入的数据信息，从而避免录入数据信息混乱的局面；
并且，通过数据类型的约束与属性设置，还可以避免数据信息输入的重复与输入数据信息不能为空；
常见的约束定义：
| 序号 | 约束方法 | 解释说明 |
|---|---|---|
| 01 | PK（primary key） | 表示主键约束，非空且唯一（表中只能有一个主键） |
| 02 | UK（unique key） | 表示唯一约束 |
| 03 | NN（not null） | 表示非空约束 |
| 04 | FK（foreign key） | 表示外键约束，多表之间关联使用 |
常见的属性定义：
| 序号 | 属性信息 | 解释说明 |
|---|---|---|
| 01 | default | 设定默认数据信息，可以实现自动填充 |
| 02 | auto_increment | 设定数值信息自增，可以实现数值编号自增填充（一般配合主键使用） |
| 02 | comment | 设定数据注释信息 |
| 03 | unsigned | 设定数值信息非负，可以实现数值信息列不能出现负数信息 |
外键应用补充说明：
外键也称之为外键约束: foreign key
外键: 外面的键, 一张表的一个字段(非主键)指向另外一个表的主键, 那么该字段就称之为外键.
外键所在的表称之为子表(附表); 外键所指向的主键所在的表称之为父表(主表)
2.1 外键设置说明¶
1、设置添加外键
实现将一个表的字段与另外一张表的主键进行关联(实体与实体之间的联系)，具体增加外键有两种形式：
方式一：在创建表的时候就增加外键: 在表字段之后使用foreign key
# 创建外键语法格式
foreign key(外键字段) references 主表(主键);
# 环境准备
## 创建数据库
mysql> create database db07;
# 创建外键关联的父表
mysql>
create table db07.class(
id int primary key auto_increment,
name varchar(10) not null comment "班级名字，不能为空",
room varchar(10) comment '教室：允许为空'
) charset utf8;
# 创建子表使用外键，增加外键：c_id是外键字段，class是引用表（父表），id是引用字段（主键）
mysql>
create table db07.student(
id int primary key auto_increment,
number char(10) not null unique comment "学号：不能重复",
name varchar(10) not null comment "姓名",
c_id int,
foreign key(c_id) references class(id)
) charset utf8;
#查看外键信息是否配置成功(方式一)
mysql> desc db07.student;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| number | char(10) | NO | UNI | NULL | |
| name | varchar(10) | NO | | NULL | |
| c_id | int | YES | MUL | NULL | |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
#查看外键信息是否配置成功(方式二)
mysql> show create table db07.student;
方式二：在创建表之后增加外键: 指定外键名字
# 创建外键语法格式
alter table 表名 add constraint 外键名 foreign key(外键字段) references 父表(主键字段)
# 环境准备
## 创建数据库
mysql> create database db08;
# 创建没有外键信息的表
mysql>
create table db08.t_foreign(
id int primary key auto_increment,
c_id int
)charset utf8;
# 创建没有外键关联的父表
mysql>
create table db08.class(
id int primary key auto_increment,
name varchar(10) not null comment "班级名字，不能为空",
room varchar(10) comment '教室：允许为空'
) charset utf8;
# 在没有外键的表中添加外键
mysql> alter table db08.t_foreign add constraint class_foreign foreign key(c_id) references class(id);
#查看外键
mysql> show create table db08.t_foreign;
外键增加条件: 外键字段必须与引用表(父表主键)的数据类型严格保持一致
2、设置删除外键
环境准备
# 创建数据库
mysql> create database db09;
# 创建没有外键信息的表
mysql>
create table db09.t_foreign(
id int primary key auto_increment,
c_id int
)charset utf8;
# 创建没有外键关联的父表
mysql>
create table db09.class(
id int primary key auto_increment,
name varchar(10) not null comment "班级名字，不能为空",
room varchar(10) comment '教室：允许为空'
) charset utf8;
# 在没有外键的表中添加外键
mysql> alter table db09.t_foreign add constraint class_foreign foreign key(c_id) references class(id);
# 验证，观察到FOREIGN KEY字段，说明添加外键成功
mysql> show create table db09.t_foreign;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_foreign | CREATE TABLE `t_foreign` (
`id` int NOT NULL AUTO_INCREMENT,
`c_id` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `class_foreign` (`c_id`),
CONSTRAINT `class_foreign` FOREIGN KEY (`c_id`) REFERENCES `class` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
外键不能被修改，只能先删除后再新增
# 删除外键语法格式
mysql> alter table 表名 drop foreign key 外键名;
# 示例:删除表（t_foreign）中外键信息
mysql> alter table db09.t_foreign drop foreign key class_foreign;
删除外键后进行信息查看：
# 删除表（t_foreign）中外键信息
mysql> alter table db09.t_foreign drop foreign key class_foreign;
# 验证，观察到FOREIGN KEY字段，说明删除外键已成功
mysql> SHOW CREATE TABLE db09.t_foreign;
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_foreign | CREATE TABLE `t_foreign` (
`id` int NOT NULL AUTO_INCREMENT,
`c_id` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `class_foreign` (`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
# 删除外键后进行验证，观察删除外键时只删除了外键，外键创建的索引没有删除，需要手动进行删除
mysql> desc db09.t_foreign;
+-------+------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| c_id | int | YES | MUL | NULL | |
+-------+------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
# 手动删除外键创建的索引
mysql> ALTER TABLE db09.t_foreign DROP INDEX class_foreign;
# 再次查看c_id这列的key为空
mysql> desc db09.t_foreign;
+-------+------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| c_id | int | YES | | NULL | |
+-------+------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
注意：删除外键只能删除外键部分，不能删除外键创建的索引部分
2.2 外键作用说明¶
外键也称之为外键约束，主要作用在于对数据进行约束：
约束01：外键对子表的数据写操作约束（增加和更新）
如果子表中插入的数据所对应的外键在父表不存在，创建不能成功.
# 创建数据库
mysql> create database db10;
# 创建外键关联的父表
mysql>
create table db10.class(
id int primary key auto_increment,
name varchar(10) not null comment "班级名字，不能为空",
room varchar(10) comment '教室：允许为空'
) charset utf8;
# 创建子表使用外键，增加外键：c_id是外键字段，class是引用表（父表），id是引用字段（主键）
mysql>
create table db10.student(
id int primary key auto_increment,
number char(10) not null unique comment "学号：不能重复",
name varchar(10) not null comment "姓名",
c_id int,
foreign key(c_id) references class(id)
) charset utf8;
# 查看db10.class表的内容
mysql> select * from db10.class;
Empty set (0.00 sec)
# 查看db10.student表的内容
mysql> select * from db10.student;
Empty set (0.00 sec)
# 设置的外键 c_id=1 在父表 class 中不存在对应的记录,外键约束要求子表中引用的值必须在父表中有相应的主键记录
mysql> insert into db10.student values(null,'2023110001','aaa',1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db01`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `class` (`id`))
# 解决方法：先在父表 class 中插入一条记录
mysql> insert into db10.class values(null, '班级1', '教室A');
# 再次尝试，可以成功
mysql> insert into db10.student values(null,'2023110001','aaa',1);
约束02：外键对父表也有数据约束
当父表操作一个记录，但是该记录被子表所引用的时候，那么父表的操作将会被限制(更新: 主键和删除)
# 创建数据库
mysql> create database db11;
# 创建外键关联的父表
mysql>
create table db11.class(
id int primary key auto_increment,
name varchar(10) not null comment "班级名字，不能为空",
room varchar(10) comment '教室：允许为空'
) charset utf8;
# 创建子表使用外键，增加外键：c_id是外键字段，class是引用表（父表），id是引用字段（主键）
mysql>
create table db11.student(
id int primary key auto_increment,
number char(10) not null unique comment "学号：不能重复",
name varchar(10) not null comment "姓名",
c_id int,
foreign key(c_id) references class(id)
) charset utf8;
# 给子表student添加数据，观察到因为外键约束报错
mysql> insert into db11.student values (1,'001','zq',1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db01`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `class` (`id`))
# 给父表class添加数据
mysql> insert into db11.class values (1,'Linux80','001'),(2,'Linux81','002');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
#验证父表数据是否添加完成
mysql> select * from db11.class;
+----+---------+------+
| id | name | room |
+----+---------+------+
| 1 | Linux80 | 001 |
| 2 | Linux81 | 002 |
+----+---------+------+
2 rows in set (0.00 sec)
#再次尝试给子表student添加数据
mysql> insert into db11.student values(null,'2023110001','aaa',1);
#验证子表student是否添加完成
mysql> select * from db11.student;
+----+------------+------+------+
| id | number | name | c_id |
+----+------------+------+------+
| 1 | 2023110001 | aaa | 1 |
+----+------------+------+------+
1 row in set (0.00 sec)
# 尝试删除父表class,观察到因为外键约束无法删除父表
mysql> delete from db11.class where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db01`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `class` (`id`))
# 先删除子表，再删除父表,观察到删除成功
mysql> delete from db11.student where id=1;
Query OK, 1 row affected (0.00 sec)
mysql> delete from db11.class where id=1;
Query OK, 1 row affected (0.00 sec)
# 最后验证，观察已成功删除
mysql> select * from db11.student;
Empty set (0.00 sec)
说明：删除父表数据，数据被字表所引用，所以不能更新或者删除父行记录
2.3 外键面试题¶
什么是外键 什么是外键约束？
#概念方面
外键: 外面的键, 一张表的一个字段(非主键)指向另外一个表的主键, 那么该字段就称之为外键.
外键所在的表称之为子表(附表); 外键所指向的主键所在的表称之为父表(主表)
#功能作用
约束01：外键对子表的数据写操作约束 添加操作 - 先写主表 再写子表
约束02：外键对父表也有数据约束 删除操作 - 先删子表 再删主表