03 | 表：怎么创建和修改数据表？-MySQL必知必会核心内容

当前位置:　首页>> 技术小册>> MySQL必知必会核心内容

03 | 表：怎么创建和修改数据表？

在MySQL数据库中，表（Table）是数据存储的基本结构单元，它按照特定的格式存储着相关的数据记录。一个数据库可以包含多个表，每个表都由列（Column）和行（Row）组成，其中列定义了数据的类型（如整型、字符串等），行则包含了具体的数据记录。掌握如何创建和修改数据表是数据库管理的基础技能之一，对于提升数据库设计和维护效率至关重要。

一、创建数据表

在MySQL中，创建数据表通常使用CREATE TABLE语句。这个语句允许你指定表名、列名、数据类型以及可能的其他约束（如主键、外键、默认值等）。

1. 基本语法

CREATE TABLE 表名 (
    列名1 数据类型 [约束],
    列名2 数据类型 [约束],
    ...
    [表级约束]
);

表名：是你想要创建的表的名称，必须唯一且符合MySQL的命名规则。
列名：是表中每一列的名称，同样需要唯一且符合命名规则。
数据类型：指定了列中可以存储的数据的类型，如INT、VARCHAR、DATE等。
约束：用于限制表中数据的类型或值的范围，如NOT NULL（非空约束）、PRIMARY KEY（主键约束）、UNIQUE（唯一约束）等。
表级约束：某些约束（如外键约束）需要在表级进行定义。

2. 示例

假设我们要创建一个存储员工信息的表employees，包含员工ID、姓名、职位、入职日期和薪资等信息，我们可以这样写：

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    position VARCHAR(50),
    hire_date DATE,
    salary DECIMAL(10, 2)
);

在这个例子中，employee_id列被设置为主键，并且自动增长（AUTO_INCREMENT），这意味着每当插入新记录时，MySQL会自动为该列生成一个唯一的值。name列被设置为非空（NOT NULL），确保每条记录都必须有姓名信息。

二、修改数据表

随着数据库应用的发展，我们可能需要修改已存在的数据表，以适应新的需求。MySQL提供了多种修改表结构的语句，如ALTER TABLE。

1. 修改列

添加列：使用ADD关键字可以向表中添加新列。
```
ALTER TABLE employees ADD email VARCHAR(100);
```

修改列的数据类型：使用MODIFY或CHANGE关键字可以修改列的数据类型或名称（CHANGE时）。

ALTER TABLE employees MODIFY salary DECIMAL(15, 2);
-- 或者
ALTER TABLE employees CHANGE salary salary_new DECIMAL(15, 2);

删除列：使用DROP COLUMN关键字可以从表中删除列。
```
ALTER TABLE employees DROP COLUMN position;
```

2. 修改表结构

添加主键：如果表中尚未设置主键，可以使用ADD PRIMARY KEY来添加。
```
ALTER TABLE employees ADD PRIMARY KEY (employee_id);
```
删除主键（注意：直接删除主键的操作在MySQL中不直接支持，需要先删除主键约束，如果表没有其他主键或唯一索引，则此操作可能不适用）：
```
ALTER TABLE employees DROP PRIMARY KEY;
```
但通常，我们不会随意删除主键，因为它对表的完整性和性能至关重要。
添加索引：使用CREATE INDEX或ALTER TABLE ... ADD INDEX为表中的列添加索引，以加快查询速度。
```
CREATE INDEX idx_name ON employees(name);
-- 或者
ALTER TABLE employees ADD INDEX idx_name (name);
```
删除索引：使用DROP INDEX删除索引。
```
ALTER TABLE employees DROP INDEX idx_name;
```
修改表名：使用RENAME TABLE语句可以修改表的名称。
```
RENAME TABLE employees TO staff;
```

3. 修改表选项

MySQL还允许你修改表的某些选项，如字符集（CHARACTER SET）和校对规则（COLLATE），但这通常不是日常操作的重点。

ALTER TABLE employees CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

三、注意事项

备份数据：在进行任何重大修改之前，备份你的数据总是一个好习惯，以防万一操作失误导致数据丢失。
性能测试：在生产环境中修改表结构前，最好在测试环境中先进行性能测试，以确保修改后的表性能符合预期。
兼容性：注意不同版本的MySQL在语法和特性上可能存在差异，确保你的操作与你的MySQL版本兼容。
事务处理：在支持事务的存储引擎（如InnoDB）中，考虑将多个修改操作放在一个事务中执行，以保持数据的一致性和完整性。

四、总结

创建和修改数据表是MySQL数据库管理的基础任务之一。通过CREATE TABLE语句，我们可以定义新的数据表结构，而ALTER TABLE语句则提供了丰富的选项来修改现有表的结构。在进行这些操作时，需要注意数据的完整性和性能，确保修改后的表能够满足应用的需求。同时，也要关注MySQL的版本差异和特性变化，以充分利用数据库的强大功能。