【MySQL 保姆级教学】表结构的操作（4）

表结构的操作

1. 定义和语法
2. 创建表 CREATE
- 2.1 创建表的本质
- 2.2 表的存储引擎
- 2.3 表的字符集和校验规则
- 2.4 创建表实例
3. 查看表结构 DESC
- 3.1 作用
- 3.2 示例
4. 修改表结构 ALTER
- 4.1 添加列 ADD
- 4.2 修改列 MODIFY
- 4.3 删除列 DROP
- 4.4 更改列名 CHANGE
5. 修改表名 RENAME
6. 删除表 DROP

1. 定义和语法

在数据库中，表是用于存储和组织数据的基本结构。表由行（记录）和列（字段）组成，每一行代表一个数据记录，每一列代表一个特定的数据属性。表的定义包括表的名称、列的定义、数据类型、约束和其他属性。

表的定义要素：

表名：表的唯一标识符，用于在数据库中引用该表。
列名：表中每一列的名称，用于标识该列存储的数据。
数据类型：每列的数据类型，定义了该列可以存储的数据种类。
约束：用于确保数据的完整性和一致性，常见的约束包括主键、外键、唯一性、非空等。
索引：用于加速数据检索的特殊数据结构。
其他属性：如默认值、注释等。

语法：

CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype
) character set 字符集 collate 校验规则 engine 存储引擎;

说明：

table：表示列名。
datatype ：表示列的类型。
character set ：字符集，如果没有指定字符集，则以所在数据库的字符集为准。
collate：校验规则，如果没有指定校验规则，则以所在数据库的校验规则为准。

2. 创建表 CREATE

2.1 创建表的本质

创建一个study表
命令：create table study ( score int);
这个命令中，study是表名，score是列名，int是类型。
在这里插入图片描述

那么，创建表的本质是什么呢？
我是在Linux中，打开存储数据库test的目录：
命令：ls /var/lib/mysql/test
在这里插入图片描述
同学们可以看到，有两个文件名和我创建的表名一样，所以，创建表的本质就是创建一个文件。

说明：
不同的存储引擎，创建表的文件不一样。
study 表存储引擎是 Innodb ，在数据目中有二个不同的文件，分别是：
study..frm：表的结构信息。
study.ibd：表的数据和索引。

2.2 表的存储引擎

Innodb 存储引擎：

.frm 文件：是 Form 文件的缩写，用于存储表的元数据（即表结构信息）。每个表都有一个对应的 .frm 文件，无论使用哪种存储引擎。
.ibd 文件：是 InnoDB Data 文件的缩写，用于存储 InnoDB 存储引擎表的数据和索引。每个使用 InnoDB 存储引擎的表都有一个对应的 .ibd 文件。

MyISAM 存储引擎：

.frm 文件：存储了表的结构信息，包括列的名称、数据类型、索引定义、约束等。这些信息用于描述表的元数据。
.MYD 文件：存储了表的实际数据行。数据按照行的顺序存储，每行数据按照列的定义进行排列。
.MYI 文件：
存储了表的索引信息。索引用于加速数据的检索，包括主键索引、唯一索引和普通索引。

2.3 表的字符集和校验规则

表的时候，有字符集和校验集的设置：

字符集：定义了数据库中可以存储的字符及其编码方式。字符集决定了每个字符在计算机中如何表示，即每个字符占用多少字节以及具体的二进制编码。
校验集：定义了字符集中的字符如何进行比较和排序。同一个字符集可以有不同的校验集，不同的校验集决定了字符的大小写敏感性、重音敏感性等。

数字存储的时会使用字符集，数据查找或者匹配，这样会进行数据比较，想要进行比较就需要先读取出来，读取使用校验集，支持数据库进行比较使用的校验集，本质也是一种读取数据库中数据的编码格式

举个简单的例子，如果你存储数据使用浮点类型，而取数据用字符串类型，这样取出来的数据肯定会乱套的。

总之，数据库无论对数据做任何操作，都必须保证操作和编码必须是一致的。

当创建数据库时不设置字符集和校验集，会默认使用mysql配置的字符集和校验集

示例：

创建一个表，同时设置字符集和校验集。
命令：

create table study(
id int primary key,
name varchar(10),
score int
) charset utf8 collate utf8_general_ci;

同学们想更加深入的了解字符集和校验集，请看数据库的操作(第三章)

2.4 创建表实例

创建一个study表
命令：create table study ( score int);
这个命令中，study是表名，score是列名，int是类型。
创建一个表，每列加上注释
命令：

create table test(
id int comment ‘学号’,
name varchar(8) comment ‘姓名’,
score int comment ‘分数’);
创建一个表，id 设置为主键，同时设置字符集和校验集。
命令：

create table study(
id int primary key,
name varchar(10),
score int
) charset utf8 collate utf8_general_ci;

3. 查看表结构 DESC

3.1 作用

了解表的定义

列信息：查看表中的所有列及其数据类型、长度、是否允许为空等。
约束信息：查看表中的主键、外键、唯一性约束、检查约束等。
索引信息：查看表中的索引，包括主键索引、唯一索引、普通索引等。

确保数据模型的正确性

验证列定义：确保表中的列定义符合业务需求，没有遗漏或多余列。
验证约束：确保表中的约束（如主键、外键、唯一性约束）正确无误，以保证数据的完整性和一致性。

优化查询性能

索引分析：了解表中的索引情况，帮助你优化查询性能。索引可以显著提高查询速度，但过多的索引会增加写操作的开销。
数据类型选择：确保选择了合适的数据类型，以优化存储和查询性能。

进行数据迁移和备份

备份和恢复：在备份和恢复表时，了解表结构可以帮助你确保备份文件的完整性和正确性。
数据迁移：在将数据从一个数据库迁移到另一个数据库时，了解表结构可以确保数据的一致性和兼容性。

调试和问题排查

调试查询：在调试复杂的查询时，了解表结构可以帮助你更好地理解查询的执行计划和性能瓶颈。
问题排查：在遇到数据问题时，查看表结构可以帮助你快速定位问题所在。

3.2 示例

语法：desc 表名;

示例：
命令：desc test;

在这里插入图片描述字段其实就是表

4. 修改表结构 ALTER

4.1 添加列 ADD

语法：

ALTER TABLE table_name
ADD column_name datatype [constraints];

示例：

在表中添加一个 sex 的字段
命令：

ALTER TABLE table_name
ADD column_name datatype [constraints];
添加一个 age 的字段，在name 的后面
命令：

alter table test
add age int comment ‘年龄’ after name;

4.2 修改列 MODIFY

语法：

ALTER TABLE table_name
MODIFY column_name new_datatype [new_constraints];

注意：modify 是把原来的字段属性覆盖，而不是想改什么属性就只改变什么属性。在修改表的时候需要查看创建表show create table 表名;

示例：

查看创建表
命令：show create table test ;

我们可以看到表的具体结构，方便对表的进行修改。
修改表的 age ，字段类型改为 tiny，放在 name 后
命令：

alter table test
modify age tinyint comment ‘年龄’ after name;