MySQL表基本操作（DDL）

DDL（Data Definition Language）是用于定义和管理数据库结构的语言，主要包括创建、修改和删除数据库对象的操作。
表相关的 DDL 主要围绕以下对象展开：

• 表（Table）：数据存储的基本单位
  
• 字段（Column）：表中的列，包含数据类型和约束
  
• 约束（Constraint）：保证数据完整性的规则（主键、外键、唯一等）
  
• 索引（Index）：提升查询性能的结构
  
• 存储引擎（Engine）：表的底层存储方式
  

1、表创建（CREATE TABLE）

1.1 基本语法

1. CREATE TABLE [IF NOT EXISTS] 表名 (
2.   字段名1 数据类型 [约束],
3.   字段名2 数据类型 [约束],
4.   ...
5.   [表级约束]
6. )
7. [ENGINE=存储引擎]
8. [CHARSET=字符集]
9. [COMMENT=表注释];

复制代码

1.2 核心组件详解

1.2.1 列定义

1. column_name data_type [NOT NULL | NULL] [DEFAULT default_value]
2. [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY] [COMMENT 'column_comment']

复制代码

1.2.2 常用数据类型

整数：INT, TINYINT, SMALLINT, MEDIUMINT, BIGINT

• 小数：DECIMAL(m,d), FLOAT, DOUBLE
  
• 字符串：CHAR(n), VARCHAR(n), TEXT, BLOB
  
• 日期时间：DATE, TIME, DATETIME, TIMESTAMP
  
• 枚举：ENUM('val1', 'val2', ...)
  
• 集合：SET('val1', 'val2', ...)
  

1.2.3 列约束

• NOT NULL：禁止空值
  
• DEFAULT：设置默认值
  
• AUTO_INCREMENT：自动递增（仅限整数）
  
• UNIQUE：唯一约束
  
• PRIMARY KEY：主键约束
  

1.2.4 表约束

• PRIMARY KEY (col1, col2, ...)：复合主键
  
• UNIQUE KEY index_name (col1, col2, ...)：唯一索引
  
• FOREIGN KEY (col) REFERENCES parent_table(col)：外键约束
  
• CHECK (condition)：检查约束（MySQL 8.0.16+）
  

1.3 完整创建表示例

1. CREATE TABLE IF NOT EXISTS employees (
2.     emp_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '员工ID',
3.     first_name VARCHAR(50) NOT NULL COMMENT '名字',
4.     last_name VARCHAR(50) NOT NULL COMMENT '姓氏',
5.     email VARCHAR(100) NOT NULL UNIQUE COMMENT '邮箱',
6.     hire_date DATE NOT NULL COMMENT '入职日期',
7.     salary DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '薪资',
8.     department_id INT NOT NULL COMMENT '部门ID',
9.     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
10.     updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
11.    
12.     -- 表级约束
13.     CONSTRAINT fk_department FOREIGN KEY (department_id)
14.         REFERENCES departments(department_id)
15.         ON DELETE CASCADE
16.         ON UPDATE RESTRICT,
17.    
18.     CONSTRAINT chk_salary CHECK (salary >= 0) -- MySQL 8.0.16+
19. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
20. COMMENT='员工信息表';

复制代码

1.4 创建临时表

1. CREATE TEMPORARY TABLE temp_orders (
2.     order_id INT PRIMARY KEY,
3.     customer_id INT,
4.     order_date DATE
5. );

复制代码

1.5 基于查询结果创建表

1. -- 复制表结构
2. CREATE TABLE new_employees LIKE employees;
4. -- 复制表结构和数据
5. CREATE TABLE employees_backup AS SELECT * FROM employees;
7. -- 创建表并插入筛选数据
8. CREATE TABLE high_salary_employees AS
9. SELECT * FROM employees WHERE salary > 100000;

复制代码

2、修改表 (ALTER TABLE)

2.1 添加列

1. ALTER TABLE employees
2. ADD COLUMN middle_name VARCHAR(50) NULL AFTER first_name,
3. ADD COLUMN bonus DECIMAL(10,2) DEFAULT 0.00 COMMENT '奖金';

复制代码

2.2 修改列

1. -- 修改数据类型
2. ALTER TABLE employees
3. MODIFY COLUMN salary DECIMAL(12,2) NOT NULL DEFAULT 0.00;
5. -- 修改列名和类型
6. ALTER TABLE employees
7. CHANGE COLUMN email email_address VARCHAR(150) NOT NULL UNIQUE;
9. -- 修改默认值
10. ALTER TABLE employees
11. ALTER COLUMN bonus SET DEFAULT 1000.00;

复制代码

2.3 删除列

1. ALTER TABLE employees
2. DROP COLUMN middle_name,
3. DROP COLUMN bonus;

复制代码

2.4 添加约束

1. -- 添加主键
2. ALTER TABLE orders
3. ADD PRIMARY KEY (order_id);
5. -- 添加外键
6. ALTER TABLE orders
7. ADD CONSTRAINT fk_customer
8. FOREIGN KEY (customer_id) REFERENCES customers(customer_id);
10. -- 添加唯一约束
11. ALTER TABLE products
12. ADD CONSTRAINT uc_product_code UNIQUE (product_code);
14. -- 添加检查约束（MySQL 8.0.16+）
15. ALTER TABLE employees
16. ADD CONSTRAINT chk_hire_date CHECK (hire_date >= '2000-01-01');

复制代码

2.5 删除约束

1. -- 删除主键
2. ALTER TABLE orders
3. DROP PRIMARY KEY;
5. -- 删除外键
6. ALTER TABLE orders
7. DROP FOREIGN KEY fk_customer;
9. -- 删除唯一约束
10. ALTER TABLE products
11. DROP INDEX uc_product_code;
13. -- 删除检查约束
14. ALTER TABLE employees
15. DROP CHECK chk_hire_date;

复制代码

2.6 重命名表

1. ALTER TABLE old_employee_data RENAME TO archived_employees;
2. -- 或
3. RENAME TABLE old_employee_data TO archived_employees;

复制代码

2.7 修改表选项

1. -- 修改存储引擎
2. ALTER TABLE employees ENGINE = MyISAM;
4. -- 修改字符集
5. ALTER TABLE employees CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
7. -- 添加表注释
8. ALTER TABLE employees COMMENT = '当前在职员工信息';

复制代码

3、表删除（DROP TABLE）

3.1 基本语法

1. DROP [TEMPORARY] TABLE [IF EXISTS] table_name [, table_name] ...
2. [RESTRICT | CASCADE];

复制代码

3.2 示例

1. -- 安全删除表
2. DROP TABLE IF EXISTS temp_employees;
4. -- 删除多个表
5. DROP TABLE employees_backup, old_department_data;
7. -- 删除临时表
8. DROP TEMPORARY TABLE temp_orders;

复制代码

4、表截断（TRUNCATE TABLE）

4.1 基本语法

1. TRUNCATE [TABLE] table_name;

复制代码

4.2 特点

• 删除表中所有数据
  
• 重置自增计数器
  
• 比 DELETE 更快（不记录日志）
  
• 无法回滚（隐式提交）
  
• 不会触发 DELETE 触发器
  

4.3 示例

1. TRUNCATE TABLE audit_log;

复制代码

5、查看表相关信息

1. -- 查看数据库中所有表
2. SHOW TABLES;
4. -- 查看表结构（字段、类型、约束）
5. DESCRIBE `user`;
6. -- 简写
7. DESC `user`;
9. -- 查看表的详细创建语句（含完整DDL）
10. SHOW CREATE TABLE `user`;
12. -- 查看表的存储引擎和字符集
13. SHOW TABLE STATUS LIKE 'user';

复制代码

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！