SQL详解之DDL - 数据库定义语言完整指南

引言

数据定义语言（Data Definition Language，DDL）是SQL语言的一个重要子集，用于定义和管理数据库结构。DDL命令包括CREATE、ALTER、DROP等，它们负责创建、修改和删除数据库对象，如数据库、表、索引、视图等。本文将详细介绍DDL的各种命令，并对比MySQL和PostgreSQL在DDL实现上的差异。

1. DDL基础概念

1.1 什么是DDL？

DDL（Data Definition Language）是SQL语言中用于定义数据库结构的部分，主要包括：

  • CREATE：创建数据库对象
  • ALTER：修改数据库对象
  • DROP：删除数据库对象
  • TRUNCATE：清空表数据

DDL语句会自动提交，执行后无法回滚（除了TRUNCATE在某些数据库中）。

1.2 DDL与DML的区别

语言类型主要命令用途事务特性
DDLCREATE, ALTER, DROP定义/修改数据库结构自动提交
DMLSELECT, INSERT, UPDATE, DELETE操作数据需手动提交

2. 数据库操作

2.1 创建数据库

MySQL语法：

-- 创建数据库
CREATE DATABASE IF NOT EXISTS company_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

-- 查看数据库
SHOW DATABASES;

-- 使用数据库
USE company_db;

PostgreSQL语法：

-- 创建数据库
CREATE DATABASE company_db
WITH 
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;

-- 查看数据库
\l  -- 或者
SELECT datname FROM pg_database;

-- 连接数据库（通过客户端）
\c company_db;

2.2 删除数据库

MySQL：

-- 删除数据库
DROP DATABASE IF EXISTS old_database;

PostgreSQL：

-- 删除数据库
DROP DATABASE IF EXISTS old_database;
-- 注意：在PostgreSQL中，删除数据库前需要终止所有连接
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = 'old_database' AND pid <> pg_backend_pid();

3. 表操作

3.1 创建表

MySQL表创建示例：

-- 创建员工表
CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    hire_date DATE NOT NULL,
    salary DECIMAL(10, 2) CHECK (salary > 0),
    department_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_email (email),
    INDEX idx_name (last_name, first_name),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
) ENGINE=InnoDB CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 创建带分区的表（MySQL 8.0+）
CREATE TABLE sales (
    id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

PostgreSQL表创建示例：

-- 创建员工表
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    hire_date DATE NOT NULL,
    salary NUMERIC(10, 2) CHECK (salary > 0),
    department_id INTEGER,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    CONSTRAINT fk_department 
        FOREIGN KEY (department_id) 
        REFERENCES departments(department_id)
        ON DELETE SET NULL
);

-- 更新updated_at字段的触发器
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_employees_updated_at 
    BEFORE UPDATE ON employees 
    FOR EACH ROW 
    EXECUTE FUNCTION update_updated_at_column();

-- 创建带分区的表（PostgreSQL 10+）
CREATE TABLE sales (
    id SERIAL,
    sale_date DATE NOT NULL,
    amount NUMERIC(10,2)
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2023 PARTITION OF sales
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE sales_2024 PARTITION OF sales
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

3.2 修改表结构

MySQL ALTER操作：

-- 添加列
ALTER TABLE employees 
ADD COLUMN phone VARCHAR(20),
ADD COLUMN manager_id INT;

-- 修改列
ALTER TABLE employees 
MODIFY COLUMN salary DECIMAL(12, 2),
CHANGE COLUMN phone mobile_phone VARCHAR(20);

-- 删除列
ALTER TABLE employees 
DROP COLUMN manager_id;

-- 添加约束
ALTER TABLE employees 
ADD CONSTRAINT chk_salary CHECK (salary > 0),
ADD INDEX idx_hire_date (hire_date);

-- 删除约束
ALTER TABLE employees 
DROP CHECK chk_salary,
DROP INDEX idx_hire_date;

-- 重命名表
ALTER TABLE employees RENAME TO staff;

PostgreSQL ALTER操作：

-- 添加列
ALTER TABLE employees 
ADD COLUMN phone VARCHAR(20),
ADD COLUMN manager_id INTEGER;

-- 修改列
ALTER TABLE employees 
ALTER COLUMN salary TYPE NUMERIC(12, 2),
ALTER COLUMN phone TYPE VARCHAR(25);  -- 重命名需要单独操作

-- 重命名列
ALTER TABLE employees RENAME COLUMN phone TO mobile_phone;

-- 删除列
ALTER TABLE employees DROP COLUMN manager_id;

-- 添加约束
ALTER TABLE employees 
ADD CONSTRAINT chk_salary CHECK (salary > 0),
ADD CONSTRAINT uk_employee_email UNIQUE (email);

-- 删除约束
ALTER TABLE employees 
DROP CONSTRAINT chk_salary,
DROP CONSTRAINT uk_employee_email;

-- 重命名表
ALTER TABLE employees RENAME TO staff;

3.3 删除表

MySQL：

-- 删除表
DROP TABLE IF EXISTS employees;

-- 临时删除（MySQL 8.0+回收站功能）
SET SESSION sql_log_bin = 0;  -- 仅用于演示
-- 实际上MySQL没有内置回收站功能，需要手动实现

PostgreSQL：

-- 删除表
DROP TABLE IF EXISTS employees;

-- 删除表及相关对象
DROP TABLE IF EXISTS employees CASCADE;  -- 删除依赖此表的对象
DROP TABLE IF EXISTS employees RESTRICT; -- 只有在没有依赖时才删除

4. 索引操作

4.1 创建索引

MySQL索引：

-- 普通索引
CREATE INDEX idx_last_name ON employees(last_name);
CREATE INDEX idx_name ON employees(last_name, first_name);

-- 唯一索引
CREATE UNIQUE INDEX idx_employee_email ON employees(email);

-- 全文索引（仅MyISAM和InnoDB）
CREATE FULLTEXT INDEX idx_description ON products(description);

-- 空间索引（仅MyISAM）
CREATE SPATIAL INDEX idx_location ON locations(location);

-- 创建表时定义索引
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    category_id INT,
    INDEX idx_category (category_id),
    UNIQUE INDEX idx_sku (sku)
) ENGINE=InnoDB;

PostgreSQL索引：

-- 普通索引
CREATE INDEX idx_last_name ON employees(last_name);
CREATE INDEX idx_name ON employees(last_name, first_name);

-- 唯一索引
CREATE UNIQUE INDEX idx_employee_email ON employees(email);

-- 部分索引
CREATE INDEX idx_active_employees ON employees(employee_id) 
WHERE status = 'active';

-- 表达式索引
CREATE INDEX idx_upper_name ON employees(UPPER(last_name));

-- 不同类型的索引
CREATE INDEX idx_gin_json ON documents USING GIN (metadata);  -- GIN索引用于JSON
CREATE INDEX idx_gist_geom ON locations USING GIST (geom);    -- GiST索引用于几何数据
CREATE INDEX idx_hash_id ON employees USING HASH (employee_id); -- Hash索引

-- 并发创建索引（不锁定表）
CREATE INDEX CONCURRENTLY idx_large_table ON large_table(column_name);

4.2 删除索引

MySQL：

-- 删除索引
DROP INDEX idx_last_name ON employees;

PostgreSQL：

-- 删除索引
DROP INDEX idx_last_name;
-- 或者
DROP INDEX IF EXISTS idx_last_name CASCADE;

5. 视图操作

5.1 创建视图

MySQL视图：

-- 创建简单视图
CREATE VIEW active_employees AS
SELECT employee_id, first_name, last_name, email, salary
FROM employees
WHERE status = 'active';

-- 创建复杂视图
CREATE VIEW department_summary AS
SELECT 
    d.department_name,
    COUNT(e.employee_id) as employee_count,
    AVG(e.salary) as avg_salary,
    MIN(e.hire_date) as earliest_hire
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;

-- 创建可更新视图
CREATE VIEW high_earners AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 80000
WITH CHECK OPTION;  -- 确保更新时仍满足条件

PostgreSQL视图：

-- 创建简单视图
CREATE VIEW active_employees AS
SELECT employee_id, first_name, last_name, email, salary
FROM employees
WHERE status = 'active';

-- 创建复杂视图
CREATE VIEW department_summary AS
SELECT 
    d.department_name,
    COUNT(e.employee_id) as employee_count,
    AVG(e.salary) as avg_salary,
    MIN(e.hire_date) as earliest_hire
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;

-- 创建物化视图（PostgreSQL特色）
CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT 
    DATE_TRUNC('month', sale_date) as month,
    SUM(amount) as total_sales,
    COUNT(*) as transaction_count
FROM sales
GROUP BY DATE_TRUNC('month', sale_date);

-- 刷新物化视图
REFRESH MATERIALIZED VIEW monthly_sales_summary;

5.2 修改和删除视图

MySQL：

-- 修改视图
CREATE OR REPLACE VIEW active_employees AS
SELECT employee_id, first_name, last_name, email, salary, hire_date
FROM employees
WHERE status = 'active';

-- 删除视图
DROP VIEW IF EXISTS active_employees;

PostgreSQL：

-- 修改视图
CREATE OR REPLACE VIEW active_employees AS
SELECT employee_id, first_name, last_name, email, salary, hire_date
FROM employees
WHERE status = 'active';

-- 删除视图
DROP VIEW IF EXISTS active_employees CASCADE;

6. 约束操作

6.1 MySQL约束

-- 创建带约束的表
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL DEFAULT (CURRENT_DATE),
    total_amount DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'processing', 'completed', 'cancelled') DEFAULT 'pending',
    notes TEXT,
    
    -- 约束定义
    CONSTRAINT chk_amount CHECK (total_amount > 0),
    CONSTRAINT chk_date CHECK (order_date <= CURRENT_DATE),
    CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- 添加约束
ALTER TABLE orders ADD CONSTRAINT chk_status CHECK (status IN ('pending', 'processing', 'completed', 'cancelled'));

-- 删除约束
ALTER TABLE orders DROP CHECK chk_status;

6.2 PostgreSQL约束

-- 创建带约束的表
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date DATE NOT NULL DEFAULT CURRENT_DATE,
    total_amount NUMERIC(10, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'completed', 'cancelled')),
    notes TEXT,
    
    -- 约束定义
    CONSTRAINT chk_amount CHECK (total_amount > 0),
    CONSTRAINT chk_date CHECK (order_date <= CURRENT_DATE),
    CONSTRAINT fk_customer 
        FOREIGN KEY (customer_id) 
        REFERENCES customers(customer_id)
        ON DELETE RESTRICT
);

-- 添加约束
ALTER TABLE orders ADD CONSTRAINT chk_status 
CHECK (status IN ('pending', 'processing', 'completed', 'cancelled'));

-- 删除约束
ALTER TABLE orders DROP CONSTRAINT chk_status;

7. 模式（Schema）操作

7.1 PostgreSQL模式操作

-- 创建模式
CREATE SCHEMA sales_schema AUTHORIZATION sales_manager;

-- 在特定模式中创建表
CREATE TABLE sales_schema.monthly_reports (
    report_id SERIAL PRIMARY KEY,
    report_month DATE NOT NULL,
    revenue NUMERIC(15, 2)
);

-- 查看所有模式
SELECT schema_name FROM information_schema.schemata;

-- 删除模式
DROP SCHEMA IF EXISTS sales_schema CASCADE;

8. DDL最佳实践

8.1 设计原则

  1. 命名规范：
-- 好的命名示例
CREATE TABLE user_accounts (
    account_id SERIAL PRIMARY KEY,
    created_timestamp TIMESTAMP WITH TIME ZONE
);

-- 约束命名
CONSTRAINT fk_user_orders FOREIGN KEY (user_id) REFERENCES users(user_id)
CONSTRAINT chk_positive_amount CHECK (amount > 0)
  1. 数据类型选择：
-- 选择合适的数据类型
-- 金额：NUMERIC/DECIMAL（避免浮点精度问题）
-- 标识符：SERIAL/INTEGER（根据数据量选择）
-- 文本：VARCHAR（根据最大长度）或 TEXT（长度不定）
-- 日期时间：TIMESTAMP WITH TIME ZONE（考虑时区）
  1. 索引策略：
-- 避免过度索引
-- 为经常查询的列创建索引
-- 考虑复合索引的顺序
-- 定期审查和清理不必要的索引

8.2 性能考虑

-- MySQL性能优化
-- 使用适当的存储引擎（InnoDB用于事务）
-- 合理设置缓冲区大小
-- 定期分析表统计信息

-- PostgreSQL性能优化
-- 合理设置共享缓冲区
-- 使用适当的填充因子
-- 定期运行VACUUM和ANALYZE

8.3 安全考虑

-- 权限管理
-- 创建专用角色
CREATE ROLE app_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_reader;
GRANT USAGE ON SCHEMA public TO app_reader;

-- 敏感数据处理
-- 避免在表定义中包含敏感信息
-- 使用适当的访问控制

9. MySQL与PostgreSQL DDL差异总结

特性MySQLPostgreSQL
自增主键AUTO_INCREMENTSERIAL
默认值DEFAULT literalDEFAULT expression
分区表PARTITION BYPARTITION BY + CREATE TABLE ... PARTITION OF
物化视图不支持支持（MATERIALIZED VIEW）
模式使用数据库独立的schema概念
数组类型不支持原生支持
JSON支持JSON类型（5.7+）JSON和JSONB类型
并发DDL有限CREATE INDEX CONCURRENTLY

相关教程

在执行DDL操作前，务必备份相关数据。对于生产环境的结构变更，建议在维护窗口期间进行，并提前在测试环境中验证。

总结

DDL是数据库设计和管理的核心工具，掌握各种DDL命令对于数据库管理员和开发人员都至关重要。MySQL和PostgreSQL在DDL实现上各有特色：

  • MySQL：语法相对简单，适合快速开发，分区功能强大
  • PostgreSQL：功能更丰富，标准兼容性更好，支持高级数据类型

选择合适的DDL策略和最佳实践，可以确保数据库结构的合理性、性能和可维护性。在实际应用中，应该根据具体的业务需求和团队技能来选择最适合的数据库系统。