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

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

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

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

#### 一、创建数据表

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

##### 1. 基本语法

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

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

##### 2. 示例

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

```sql
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`关键字可以向表中添加新列。
  
  ```sql
  ALTER TABLE employees ADD email VARCHAR(100);
  ```

- **修改列的数据类型**：使用`MODIFY`或`CHANGE`关键字可以修改列的数据类型或名称（`CHANGE`时）。
  
  ```sql
  ALTER TABLE employees MODIFY salary DECIMAL(15, 2);
  -- 或者
  ALTER TABLE employees CHANGE salary salary_new DECIMAL(15, 2);
  ```

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

##### 2. 修改表结构

- **添加主键**：如果表中尚未设置主键，可以使用`ADD PRIMARY KEY`来添加。
  
  ```sql
  ALTER TABLE employees ADD PRIMARY KEY (employee_id);
  ```

- **删除主键**（注意：直接删除主键的操作在MySQL中不直接支持，需要先删除主键约束，如果表没有其他主键或唯一索引，则此操作可能不适用）：
  
  ```sql
  ALTER TABLE employees DROP PRIMARY KEY;
  ```

但通常，我们不会随意删除主键，因为它对表的完整性和性能至关重要。

- **添加索引**：使用`CREATE INDEX`或`ALTER TABLE ... ADD INDEX`为表中的列添加索引，以加快查询速度。
  
  ```sql
  CREATE INDEX idx_name ON employees(name);
  -- 或者
  ALTER TABLE employees ADD INDEX idx_name (name);
  ```

- **删除索引**：使用`DROP INDEX`删除索引。
  
  ```sql
  ALTER TABLE employees DROP INDEX idx_name;
  ```

- **修改表名**：使用`RENAME TABLE`语句可以修改表的名称。
  
  ```sql
  RENAME TABLE employees TO staff;
  ```

##### 3. 修改表选项

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

```sql
ALTER TABLE employees CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
```

#### 三、注意事项

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

#### 四、总结

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

该分类下的相关小册推荐：

MySQL必会核心问题

SQL零基础到熟练应用(增删改查)

MySQL从入门到精通(五)

MySQL8.0入门与实践

MySQL 实战 45 讲

MySQL从入门到精通(三)

细说MySQL(零基础到高级应用)

MySQL从入门到精通(四)

MySQL从入门到精通(二)

MySQL从入门到精通(一)