MySQL表基本操作（DDL）

DDL（Data Definition Language）是用于定义和管理数据库结构的语言，主要包括创建、修改和删除数据库对象的操作。

表相关的 DDL 主要围绕以下对象展开：

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

1、表创建（CREATE TABLE）

1.1 基本语法

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

1.2 核心组件详解

1.2.1 列定义

column_name data_type [NOT NULL | NULL] [DEFAULT default_value]
[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 完整创建表示例

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

1.4 创建临时表

CREATE TEMPORARY TABLE temp_orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE
);

1.5 基于查询结果创建表

-- 复制表结构
CREATE TABLE new_employees LIKE employees;

-- 复制表结构和数据
CREATE TABLE employees_backup AS SELECT * FROM employees;

-- 创建表并插入筛选数据
CREATE TABLE high_salary_employees AS
SELECT * FROM employees WHERE salary > 100000;

2、修改表 (ALTER TABLE)

2.1 添加列

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

2.2 修改列

-- 修改数据类型
ALTER TABLE employees
MODIFY COLUMN salary DECIMAL(12,2) NOT NULL DEFAULT 0.00;

-- 修改列名和类型
ALTER TABLE employees
CHANGE COLUMN email email_address VARCHAR(150) NOT NULL UNIQUE;

-- 修改默认值
ALTER TABLE employees
ALTER COLUMN bonus SET DEFAULT 1000.00;

2.3 删除列

ALTER TABLE employees
DROP COLUMN middle_name,
DROP COLUMN bonus;

2.4 添加约束

-- 添加主键
ALTER TABLE orders
ADD PRIMARY KEY (order_id);

-- 添加外键
ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

-- 添加唯一约束
ALTER TABLE products
ADD CONSTRAINT uc_product_code UNIQUE (product_code);

-- 添加检查约束（MySQL 8.0.16+）
ALTER TABLE employees
ADD CONSTRAINT chk_hire_date CHECK (hire_date >= '2000-01-01');

2.5 删除约束

-- 删除主键
ALTER TABLE orders
DROP PRIMARY KEY;

-- 删除外键
ALTER TABLE orders
DROP FOREIGN KEY fk_customer;

-- 删除唯一约束
ALTER TABLE products
DROP INDEX uc_product_code;

-- 删除检查约束
ALTER TABLE employees
DROP CHECK chk_hire_date;

2.6 重命名表

ALTER TABLE old_employee_data RENAME TO archived_employees;
-- 或
RENAME TABLE old_employee_data TO archived_employees;

2.7 修改表选项

-- 修改存储引擎
ALTER TABLE employees ENGINE = MyISAM;

-- 修改字符集
ALTER TABLE employees CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 添加表注释
ALTER TABLE employees COMMENT = '当前在职员工信息';

3、表删除（DROP TABLE）

3.1 基本语法

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

3.2 示例

-- 安全删除表
DROP TABLE IF EXISTS temp_employees;

-- 删除多个表
DROP TABLE employees_backup, old_department_data;

-- 删除临时表
DROP TEMPORARY TABLE temp_orders;

4、表截断（TRUNCATE TABLE）

4.1 基本语法

TRUNCATE [TABLE] table_name;

4.2 特点

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

4.3 示例

TRUNCATE TABLE audit_log;

5、查看表相关信息

-- 查看数据库中所有表
SHOW TABLES;

-- 查看表结构（字段、类型、约束）
DESCRIBE `user`;
-- 简写
DESC `user`;

-- 查看表的详细创建语句（含完整DDL）
SHOW CREATE TABLE `user`;

-- 查看表的存储引擎和字符集
SHOW TABLE STATUS LIKE 'user';