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

表结构的操作

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 表的字符集和校验规则

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

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

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

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

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

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

示例：

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

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

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

2.4 创建表实例

1. 创建一个study表 命令：create table study ( score int); 这个命令中，study是表名，score是列名，int是类型。
2. 创建一个表，每列加上注释 命令：> create table test(> id int comment ‘学号’,> name varchar(8) comment ‘姓名’,> score int comment ‘分数’);
3. 创建一个表，id 设置为主键，同时设置字符集和校验集。 命令：> create table study(> id int primary key,> name varchar(10),> score int> ) charset utf8 collate utf8_general_ci;

3. 查看表结构 DESC

3.1 作用

1. 了解表的定义

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

1. 确保数据模型的正确性

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

1. 优化查询性能

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

1. 进行数据迁移和备份

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

1. 调试和问题排查

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

3.2 示例

语法：

desc 表名;

示例：
命令：

desc test;

字段其实就是表

4. 修改表结构 ALTER

4.1 添加列 ADD

语法：

ALTER TABLE table_name
ADD column_name datatype [constraints];

示例：

1. 在表中添加一个 sex 的字段 命令：> ALTER TABLE table_name> ADD column_name datatype [constraints];
2. 添加一个 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 表名;

示例：

1. 查看创建表 命令：show create table test ; 我们可以看到表的具体结构，方便对表的进行修改。
2. 修改表的 age ，字段类型改为 tiny，放在 name 后 命令：> alter table test> modify age tinyint comment ‘年龄’ after name;

4.3 删除列 DROP

语法：

ALTER TABLE table_name
DROP COLUMN column_name;

注意：删除字段一定要小心，删除字段及其对应的列数据都没了

示例：

1. 删除表的 score 列（省略 column） 命令：alter table test drop score;
2. 删除表的 sex 列（省略 column） 命令：alter table test drop column sex;

4.4 更改列名 CHANGE

语法：

ALTER TABLE table_name
CHANGE old_column_name new_column_name new_datatype [new_constraints];

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

show create table 表名;

示例：

1. 查看创建表 命令：show create table test ;
2. 更改列name 名字 命令：> alter table test> change name student varchar(8) comment ‘姓名’;

5. 修改表名 RENAME

语法：

ALTER TABLE old_table_name RENAME TO new_table_name;

示例：

1. 修改表名 命令：alter tablee test rename to study;
2. 修改表名,不使用 to 命令：alter table test rename study;

6. 删除表 DROP

语法：

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...

示例：
命令：

drop table test;