当前位置：首页 > article >正文

数据库操作命令详解：CREATE、ALTER、DROP 的使用与实践

article 2025/3/3 19:36:54

引言
数据库是存储和管理数据的核心工具，而 DDL（Data Definition Language，数据定义语言） 是构建和调整数据库结构的基石。本文将通过实际示例，详细讲解 CREATE（创建）、ALTER（修改）、DROP（删除）三大核心命令的使用方法、注意事项及典型场景，帮助开发者高效管理数据库。

一、CREATE：创建数据库对象

1. 创建数据库

CREATE DATABASE shop_db 
DEFAULT CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

作用：新建一个名为 shop_db 的数据库。
关键参数：
- CHARACTER SET：指定字符集（推荐 utf8mb4 支持完整 Unicode）。
- COLLATE：设置排序规则（如区分大小写、重音处理）。

2. 创建数据表

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    email VARCHAR(100) NOT NULL COMMENT '邮箱',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

核心字段：
- AUTO_INCREMENT：自增主键，常用于唯一标识。
- NOT NULL 和 UNIQUE：约束数据完整性。
- DEFAULT：字段默认值（如自动填充时间戳）。
表选项：
- ENGINE：指定存储引擎（InnoDB 支持事务，推荐）。
- COMMENT：为表或字段添加注释，提升可维护性。

二、ALTER：修改数据库结构

1. 新增字段

ALTER TABLE users 
ADD COLUMN phone VARCHAR(20) COMMENT '手机号' AFTER email;

用途：为 users 表添加手机号字段，位置在 email 之后。

2. 修改字段类型

ALTER TABLE users 
MODIFY COLUMN email VARCHAR(150) COMMENT '电子邮箱地址';

注意：修改字段类型可能导致数据截断，需提前备份。

3. 删除字段

ALTER TABLE users 
DROP COLUMN phone;

风险：直接删除字段会导致数据永久丢失，需谨慎操作。

4. 重命名表

ALTER TABLE users 
RENAME TO customer;

场景：调整表名以符合业务术语（如 users → customer）。

三、DROP：删除数据库对象

1. 删除数据表

DROP TABLE customer;

影响：表结构和数据永久删除，不可恢复！
安全建议：
- 先备份：CREATE TABLE backup_customer AS SELECT * FROM customer;
- 确认无依赖：检查外键关联或业务代码是否引用该表。

2. 删除数据库

DROP DATABASE shop_db;

慎用：删除数据库会清除所有表和数据，仅用于测试环境或明确清理。

四、实战技巧与避坑指南

1. ALTER 的锁表问题

现象：修改大表时可能锁表，导致业务阻塞。
优化方案：
- 使用在线工具（如 pt-online-schema-change）。
- 在低峰期操作，分批执行变更。

2. DROP 的防误删设计

技巧：启用数据库回收站（部分云数据库支持），或配置权限隔离：
```
GRANT DELETE, INSERT, SELECT ON shop_db.* TO 'app_user'@'%';  
REVOKE DROP ON shop_db.* FROM 'app_user'@'%';  
```
- 限制生产账号的 DROP 权限，避免误操作。