如何创建、修改和删除数据库表？

以下是关于数据库表的创建、修改和删除操作的完整指南，涵盖基础语法、注意事项及主流数据库系统（MySQL/PostgreSQL）的差异，结合资料进行系统性说明：

一、创建数据库表

1.创建数据库

语法：

CREATE DATABASE [IF NOT EXISTS] database_name
  [DEFAULT CHARACTER SET charset_name]
  [DEFAULT COLLATE collation_name];

• IF NOT EXISTS：避免重复创建时报错。
• CHARACTER SET：指定字符集（如utf8mb4）。
• COLLATE：指定排序规则（如utf8mb4_0900_ai_ci）。

示例：

CREATE DATABASE teaching 
  DEFAULT CHARACTER SET utf8mb4 
  DEFAULT COLLATE utf8mb4_0900_ai_ci;  -- 指定字符集和排序规则（）

2.选择数据库

创建表前需指定操作数据库：

USE database_name;  -- 避免"No database selected"错误（）

3.创建表

核心语法：

CREATE TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
    [table_constraints]
);

• 关键要素：

  • 列定义：列名 + 数据类型（如INT, VARCHAR(50), DATE） + 约束。
  • 常用约束：

• PRIMARY KEY：主键（唯一标识行）。

• NOT NULL：禁止空值。

• UNIQUE：列值唯一。

• FOREIGN KEY：外键关联其他表。

• DEFAULT：设置默认值。

   

  

• 设计步骤：

  1. 确定列数据类型及是否允许NULL。
  2. 定义唯一列（候选键）和主键。
  3. 设置外键关联和默认值。
  4. 添加域约束（如CHECK）。

• 示例：

CREATE TABLE employees (
    id INT PRIMARY KEY NOT NULL,
    name VARCHAR(50) NOT NULL,
    age INT,
    salary DECIMAL(10,2) DEFAULT 0.0,
    hire_date DATE
);

二、修改表结构

1. 通用语法（ALTER TABLE）

添加列：

ALTER TABLE table_name ADD column_name datatype;  -- 如`ALTER TABLE employees ADD email VARCHAR(100);`（）

删除列：

ALTER TABLE table_name DROP COLUMN column_name;  -- 如`ALTER TABLE employees DROP COLUMN age;`（）

修改列数据类型：

MySQL：

ALTER TABLE table_name MODIFY COLUMN column_name new_datatype;  -- 如`MODIFY COLUMN salary FLOAT;`

PostgreSQL/SQL Server：

ALTER TABLE table_name ALTER COLUMN column_name TYPE new_datatype;  -- （）

重命名列：

-- MySQL
ALTER TABLE table_name CHANGE old_name new_name datatype;
-- PostgreSQL/SQL Server
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;  -- （）

2. 注意事项

• 备份数据：修改前务必备份，避免数据丢失。
• 测试环境验证：先在非生产环境测试修改脚本。
• 事务控制：使用事务（BEGIN; ... COMMIT;）确保操作原子性。
• 锁表风险：大表修改可能锁表，影响服务（如MySQL的ALGORITHM=INPLACE可缓解）。

三、删除表

1.语法与操作

DROP TABLE [IF EXISTS] table_name;  -- `IF EXISTS`避免表不存在时报错（）

• 影响：永久删除表结构、数据、索引及关联对象（触发器、约束等）。

2.关键注意事项

• 权限检查：需具备DROP权限。
• 依赖关系：
  • 若表被外键引用，需先删除依赖或使用CASCADE（PostgreSQL支持）。
  • 检查视图、存储过程是否引用该表。
• 备份：执行前导出数据（如mysqldump或pg_dump）。
• 生产环境谨慎操作：建议通过审批流程记录操作日志。
• 示例安全删除：

USE my_database;
DROP TABLE IF EXISTS users;  -- 检查存在性后删除（）

四、主流数据库差异（MySQL vs PostgreSQL）

1. 表操作性能

2. 功能对比

3. 适用场景

• MySQL：高并发读操作（如Web应用），结构简单，快速部署。
• PostgreSQL：复杂查询、写入密集型场景（如金融系统），需高级数据类型和扩展性。

五、最佳实践总结

1. 设计阶段：
   • 规范命名（有意义且一致），合理选择数据类型（避免过度使用VARCHAR）。
   • 明确主键和约束（如外键保障数据完整性）。
2. 修改操作：
   • 始终在非高峰时段执行ALTER TABLE。
   • 使用IF EXISTS/IF NOT EXISTS增强脚本健壮性。
3. 删除操作：
   • 强制备份 + 权限隔离（避免开发者直接操作生产库）。
4. 跨数据库兼容：
   • 使用ORM工具或抽象层（如Hibernate）减少方言差异影响。
   • 为MySQL/PostgreSQL分别编写迁移脚本。

引用说明：

• 创建语法：
• 修改操作：

  

• 删除操作：
• 数据库差异：