数据定义语言 (DDL)

DDL (Data Definition Language) 用于定义和管理数据库对象的结构。

数据库操作

创建数据库

-- 基本创建
CREATE DATABASE my_database;
 
-- 指定字符集（推荐）
CREATE DATABASE my_database
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
 
-- 如果不存在则创建
CREATE DATABASE IF NOT EXISTS my_database;

查看数据库

-- 查看所有数据库
SHOW DATABASES;
 
-- 查看数据库创建语句
SHOW CREATE DATABASE my_database;

修改数据库

-- 修改字符集
ALTER DATABASE my_database
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

删除数据库

-- 删除数据库（危险操作！）
DROP DATABASE my_database;
 
-- 如果存在则删除
DROP DATABASE IF EXISTS my_database;

使用数据库

-- 切换当前数据库
USE my_database;
 
-- 查看当前数据库
SELECT DATABASE();

表操作

创建表

-- 基本语法
CREATE TABLE 表名 (
    列名1 数据类型 [约束],
    列名2 数据类型 [约束],
    ...
    [表级约束]
);
 
-- 示例：创建用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    age INT CHECK (age >= 0 AND age <= 150),
    status ENUM('active', 'inactive', 'banned') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
 
-- 如果不存在则创建
CREATE TABLE IF NOT EXISTS users (...);

常用数据类型

数值类型

类型	大小	范围	用途
TINYINT	1 字节	-128 ~ 127	小整数
SMALLINT	2 字节	-32768 ~ 32767	小整数
INT	4 字节	约 ±21 亿	常用整数
BIGINT	8 字节	非常大	大整数
DECIMAL(M,D)	变长	精确小数	金额计算
FLOAT	4 字节	单精度	科学计算
DOUBLE	8 字节	双精度	科学计算

字符串类型

类型	大小	说明
CHAR(N)	固定 N 字符	定长字符串，存储效率高
VARCHAR(N)	最多 N 字符	变长字符串，节省空间
TEXT	最大 65535 字符	长文本
MEDIUMTEXT	约 16MB	中等长度文本
LONGTEXT	约 4GB	超长文本

日期时间类型

类型	格式	范围
DATE	YYYY-MM-DD	1000-01-01 ~ 9999-12-31
TIME	HH:MM:SS	-838:59:59 ~ 838:59:59
DATETIME	YYYY-MM-DD HH:MM:SS	1000 ~ 9999 年
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970 ~ 2038 年（自动时区转换）
YEAR	YYYY	1901 ~ 2155

其他类型

-- 布尔类型（MySQL 实际存储为 TINYINT）
status BOOLEAN DEFAULT TRUE
 
-- 枚举类型
status ENUM('pending', 'approved', 'rejected')
 
-- JSON 类型（MySQL 5.7+）
metadata JSON
 
-- 二进制类型
avatar BLOB

查看表结构

-- 查看表列表
SHOW TABLES;
 
-- 查看表结构
DESC users;
DESCRIBE users;
 
-- 查看完整建表语句
SHOW CREATE TABLE users;

修改表结构

添加列

-- 添加单列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
 
-- 添加列到指定位置
ALTER TABLE users ADD COLUMN nickname VARCHAR(50) AFTER username;
 
-- 添加列到最前面
ALTER TABLE users ADD COLUMN uuid VARCHAR(36) FIRST;

修改列

-- 修改列类型
ALTER TABLE users MODIFY COLUMN phone VARCHAR(30);
 
-- 修改列名和类型
ALTER TABLE users CHANGE COLUMN phone mobile VARCHAR(30);
 
-- 修改默认值
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'inactive';

删除列

-- 删除列
ALTER TABLE users DROP COLUMN phone;

重命名表

-- 重命名表
ALTER TABLE users RENAME TO members;
 
-- 或者
RENAME TABLE users TO members;

删除表

-- 删除表（危险！）
DROP TABLE users;
 
-- 如果存在则删除
DROP TABLE IF EXISTS users;
 
-- 删除多个表
DROP TABLE IF EXISTS users, orders, products;

清空表

-- TRUNCATE：快速清空，重置自增值
TRUNCATE TABLE users;
 
-- DELETE：逐行删除，保留自增值
DELETE FROM users;

表设计最佳实践

命名规范

-- 表名：小写，下划线分隔，复数形式
CREATE TABLE user_orders (...)
CREATE TABLE product_categories (...)
 
-- 列名：小写，下划线分隔
user_id, created_at, order_status
 
-- 主键：表名单数_id 或直接 id
user_id INT PRIMARY KEY
-- 或
id INT PRIMARY KEY

通用字段设计

CREATE TABLE base_template (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    
    -- 业务字段
    ...
    
    -- 审计字段
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by BIGINT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    updated_by BIGINT,
    
    -- 软删除
    is_deleted BOOLEAN DEFAULT FALSE,
    deleted_at TIMESTAMP NULL
);

示例：电商表设计

-- 用户表
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    avatar_url VARCHAR(500),
    status ENUM('active', 'inactive', 'banned') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
-- 商品表
CREATE TABLE products (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    category_id BIGINT,
    status ENUM('draft', 'active', 'inactive') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_category (category_id),
    INDEX idx_status (status),
    CONSTRAINT chk_price CHECK (price >= 0),
    CONSTRAINT chk_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
-- 订单表
CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(32) NOT NULL UNIQUE,
    user_id BIGINT NOT NULL,
    total_amount DECIMAL(12, 2) NOT NULL,
    status ENUM('pending', 'paid', 'shipped', 'completed', 'cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_user (user_id),
    INDEX idx_status (status),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
-- 订单明细表
CREATE TABLE order_items (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id),
    INDEX idx_order (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

临时表

-- 创建临时表（会话结束自动删除）
CREATE TEMPORARY TABLE temp_results (
    id INT,
    value VARCHAR(100)
);
 
-- 从查询结果创建临时表
CREATE TEMPORARY TABLE temp_high_salary AS
SELECT * FROM employees WHERE salary > 20000;

复制表结构

-- 只复制结构，不复制数据
CREATE TABLE users_backup LIKE users;
 
-- 复制结构和数据
CREATE TABLE users_backup AS SELECT * FROM users;
 
-- 复制结构和部分数据
CREATE TABLE users_backup AS 
SELECT * FROM users WHERE created_at > '2024-01-01';

总结

操作	命令	示例
创建数据库	CREATE DATABASE	`CREATE DATABASE mydb`
删除数据库	DROP DATABASE	`DROP DATABASE mydb`
创建表	CREATE TABLE	`CREATE TABLE t (...)`
修改表	ALTER TABLE	`ALTER TABLE t ADD col`
删除表	DROP TABLE	`DROP TABLE t`
清空表	TRUNCATE TABLE	`TRUNCATE TABLE t`

上一节：05-聚合与分组 下一节：07-约束与索引 — 主键、外键、索引

sql ddl 表设计

Muliminty Note

探索

06-数据定义语言

数据定义语言 (DDL)

数据库操作

创建数据库

查看数据库

修改数据库

删除数据库

使用数据库

表操作

创建表

常用数据类型

数值类型

字符串类型

日期时间类型

其他类型

查看表结构

修改表结构

添加列

修改列

删除列

重命名表

删除表

清空表

表设计最佳实践

命名规范

通用字段设计

示例：电商表设计

临时表

复制表结构

总结

关系图谱

目录

反向链接