【MySQL】深入理解MySQL表操作：从创建到维护

深入理解MySQL表操作：从创建到维护

引言

​ MySQL作为关系型数据库管理系统（RDBMS）的佼佼者，其表操作是数据库管理中的核心内容。本文将深入探讨MySQL表的创建、查看、修改和删除等操作。

1. 创建表

语法：

create table table_name ( field1 datatype, field2 datatype, field3 datatype ) character set 字符集 collate 校验规则 engine 存储引擎;

说明：

• 字段定义：字段（field）是表中的列，每个字段都有其数据类型（datatype）。数据类型决定了字段可以存储的数据种类和格式。
• 字符集和校验规则：字符集（character set）和校验规则（collate）决定了数据的存储方式和比较规则。如果不指定，将使用数据库默认的字符集和校验规则。
• 存储引擎：**存储引擎（engine）决定了数据的存储方式和访问方法。**常见的存储引擎有MyISAM和InnoDB。每种存储引擎都有其特点，例如 MyISAM 适合读密集型应用，而 InnoDB 支持事务处理。

1.1 创建表案例

create table users(
id int, 
name varchar(20) comment '用户名',
password char(32) comment '密码',
birthday date comment '生日'
)character set utf8 engine MyISAM;

2. 查看表结构

在创建表之后，我们经常需要查看表的结构，以了解其字段和数据类型。使用以下命令可以查看表结构：

• describe table_name;
• desc table_name;

这将列出表的所有字段及其属性，包括数据类型、是否允许NULL值、键类型、默认值和额外信息。

3. 修改表

随着项目的发展，表结构可能需要调整。MySQL提供了多种修改表的命令：

3.1 添加字段

3.1.1 不指定位置添加字段

语法：

alter table_name add column_name column_definition;

说明：

• table_name：要修改的表的名称
• column_name：新添加字段的名称
• column_definition：新添加的字段的数据类型和其他属性

3.1.2 指定位置添加字段

如果你需要指定新字段在表中的位置，可以使用first 或者 after 字句。 例如你想将 ecail 字段添加到 name 字段之后，可以使用：

alter table table_name add email varchar(255) after name;

说明：

• table_name：要修改的表名称
• email varchar(255)：新添加的字段的名称、数据类型
• after name：新添加的字段的位置

或者你想将email字段作为表的第一列，可以使用：

alter table table_name add email varchar(255) first;

​ 请注意，添加字段时，如果没有指定NOT NULL约束，新添加的字段默认允许NULL值。如果你希望字段不允许NULL值，需要在字段定义中明确指定NOT NULL。此外，如果表中已有数据，添加不允许NULL的字段时，需要同时指定一个默认值。

3.2 修改字段

3.2.1 修改字段的数据类型或大小

语法：

alter table table_name modify column_name new_datatype;

说明：

• table_name：表示要修改的表名称
• column_name：表示要修改的字段名称
• new_datatype：表示新的字段的数据类型或大小

例如，将 users表中的 name 字段从 varchar(20) 修改为 varchar(50)：

alter table users modify name varchar(50)

3.2.2 重命名字段

语法：

alter table table_name change old_column_name new_column_name new_datatype;

说明：

• table_name：要修改的表名称
• old_column_name：原始字段的名称
• new_column_name：新字段的名称
• new_datatype：新的数据类型（如果也需要更改），如果数据类型不需要更改，则可以省略不写

例如，将 users 表中的 username 字段重命名为 user_name， 并且不更改数据类型。

alter table users change username user_name varchar(20);

3.2.3 添加字段的默认值

语法：

alter table table_name alter column_name set default default_value;

说明：

• table_name：要修改的表名称
• column_name：要修改的列名称（即指定的目标列）
• default_value：设置的默认值

例如，将 users 表中的 birthday字段添加默认值 2024-07-27：

alter table users alter birthday set default '2024-07-27';

3.2.4 删除字段的默认值

语法：

alter table table_name alter column_name drop default;

说明：

• table_name：要修改的表名称
• column_name：要修改的列名称

例如，将 users 表中的 birthday 字段的默认值删除：

alter table users alter birthday drop default;

3.3 删除字段（慎用）

语法：

alter table table_name drop column column_name;

说明：

• table_name：要修改的表名称
• column_name：要修改的列名称

例如，将 users 表中的 email 字段删除：

alter table users drop column email;

3.4 修改表名

语法：

alter table old_table_name rename to new_table_name;

说明：

• old_table_name：修改前的表名
• new_table_name：修改后的表名

4. 删除表

语法：

drop table table_name;

说明：

• table_name：要删除的表名

如果存在多个副本，可以使用 drop temporary table来删除临时表

结语

​ MySQL表操作是数据库管理的基础。通过本文的介绍，我们了解到了如何创建、查看、修改和删除表。掌握这些技能对于任何数据库管理员或开发者来说都是至关重要的。随着技术的不断进步，我们也需要不断学习和适应新的数据库技术，以满足不断变化的业务需求。