跳到主要内容

MySQL 表操作

本章将详细介绍 MySQL 中表的创建、修改、查看和删除操作。

创建表​

基本语法​

CREATE TABLE [IF NOT EXISTS] 表名 (
    列名1 数据类型 [列约束],
    列名2 数据类型 [列约束],
    ...
    [表约束]
) [表选项];

基本创建示例​

-- 创建用户表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    age TINYINT UNSIGNED,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

说明：

  • AUTO_INCREMENT：自动递增，每次插入新记录时自动生成唯一值
  • PRIMARY KEY：主键，唯一标识每一行
  • NOT NULL：不允许空值
  • UNIQUE：值必须唯一
  • DEFAULT：设置默认值

查看表结构​

-- 方式一：DESCRIBE 命令（简写 DESC）
DESCRIBE users;
DESC users;

-- 方式二：SHOW COLUMNS
SHOW COLUMNS FROM users;

-- 方式三：查看创建语句
SHOW CREATE TABLE users;

输出示例：

+------------+---------------------+------+-----+-------------------+----------------+
| Field      | Type                | Null | Key | Default           | Extra          |
+------------+---------------------+------+-----+-------------------+----------------+
| id         | int                 | NO   | PRI | NULL              | auto_increment |
| username   | varchar(50)         | NO   |     | NULL              |                |
| email      | varchar(100)        | NO   | UNI | NULL              |                |
| age        | tinyint unsigned    | YES  |     | NULL              |                |
| status     | enum('active',...)  | YES  |     | active            |                |
| created_at | datetime            | YES  |     | CURRENT_TIMESTAMP |                |
+------------+---------------------+------+-----+-------------------+----------------+

列约束​

PRIMARY KEY（主键）​

主键用于唯一标识表中的每一行记录。

-- 单列主键
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 复合主键
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

-- 自增主键（推荐）
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

主键特点：

  • 值必须唯一
  • 不能为 NULL
  • 每个表只能有一个主键
  • InnoDB 存储引擎按主键组织数据（聚簇索引）

FOREIGN KEY（外键）​

外键用于建立表与表之间的关联关系。

-- 创建主表
CREATE TABLE departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

-- 创建从表，包含外键
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department_id INT,
    salary DECIMAL(10, 2),
    
    -- 定义外键约束
    CONSTRAINT fk_department
        FOREIGN KEY (department_id)
        REFERENCES departments(id)
        ON DELETE CASCADE      -- 删除部门时，自动删除相关员工
        ON UPDATE CASCADE      -- 更新部门ID时，自动更新员工表
);

外键选项：

选项说明
RESTRICT拒绝删除/更新操作（默认）
CASCADE级联删除/更新
SET NULL设置为 NULL
NO ACTION等同于 RESTRICT

查看外键：

SELECT 
    CONSTRAINT_NAME,
    TABLE_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'employees';

UNIQUE（唯一约束）​

确保列中的值唯一，允许 NULL 值。

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,      -- 列级约束
    email VARCHAR(100),
    
    -- 表级约束（可以命名）
    CONSTRAINT uk_email UNIQUE (email)
);

UNIQUE vs PRIMARY KEY：

特性UNIQUEPRIMARY KEY
唯一性是是
允许 NULL是（可以有多个 NULL）否
每表数量多个一个

NOT NULL（非空约束）​

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,    -- 必填字段
    description VARCHAR(500)        -- 可选字段
);

DEFAULT（默认值）​

CREATE TABLE orders (
    id INT PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CHECK（检查约束）​

MySQL 8.0.16+ 支持 CHECK 约束，用于限制列中的值范围。

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0,
    
    -- 价格必须大于 0
    CONSTRAINT chk_price CHECK (price > 0),
    
    -- 库存不能为负
    CONSTRAINT chk_stock CHECK (stock >= 0)
);

修改表​

ALTER TABLE 语法​

ALTER TABLE 表名 
    [ADD 列定义 | ADD 约束]
    [DROP COLUMN 列名 | DROP 约束]
    [MODIFY 列定义]
    [CHANGE 旧列名 新列定义]
    [RENAME 新表名];

添加列​

-- 添加单个列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 添加多个列
ALTER TABLE users 
    ADD COLUMN address VARCHAR(200),
    ADD COLUMN city VARCHAR(50);

-- 添加列到指定位置
ALTER TABLE users ADD COLUMN age INT AFTER username;  -- 在 username 之后
ALTER TABLE users ADD COLUMN id INT FIRST;            -- 在第一列

修改列​

-- 修改列的数据类型
ALTER TABLE users MODIFY COLUMN age TINYINT UNSIGNED;

-- 修改列名和数据类型
ALTER TABLE users CHANGE COLUMN username user_name VARCHAR(100);

-- 修改列的默认值
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'inactive';

-- 删除默认值
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;

删除列​

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

-- 删除多个列
ALTER TABLE users 
    DROP COLUMN address,
    DROP COLUMN city;

添加和删除约束​

-- 添加主键
ALTER TABLE users ADD PRIMARY KEY (id);

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

-- 添加外键
ALTER TABLE employees 
    ADD CONSTRAINT fk_department 
    FOREIGN KEY (department_id) REFERENCES departments(id);

-- 删除外键
ALTER TABLE employees DROP FOREIGN KEY fk_department;

-- 添加唯一约束
ALTER TABLE users ADD UNIQUE (email);

-- 删除唯一约束
ALTER TABLE users DROP INDEX email;  -- 唯一约束实际是唯一索引

-- 添加 CHECK 约束
ALTER TABLE products ADD CONSTRAINT chk_price CHECK (price > 0);

-- 删除 CHECK 约束
ALTER TABLE products DROP CHECK chk_price;

重命名表​

-- 方式一
ALTER TABLE users RENAME TO customers;

-- 方式二
RENAME TABLE users TO customers;

-- 批量重命名
RENAME TABLE 
    old_table1 TO new_table1,
    old_table2 TO new_table2;

复制表​

复制表结构​

-- 只复制表结构，不复制数据
CREATE TABLE users_copy LIKE users;

复制表结构和数据​

-- 复制表结构和数据（不复制索引和约束）
CREATE TABLE users_backup AS SELECT * FROM users;

-- 复制部分数据
CREATE TABLE active_users AS 
SELECT * FROM users WHERE status = 'active';

使用 INSERT ... SELECT 复制数据​

-- 将数据从旧表复制到新表
INSERT INTO users_copy SELECT * FROM users;

-- 复制部分列
INSERT INTO users_copy (id, username, email)
SELECT id, username, email FROM users;

删除表​

DROP TABLE​

-- 删除单个表
DROP TABLE users;

-- 删除多个表
DROP TABLE users, orders, products;

-- 如果存在则删除
DROP TABLE IF EXISTS users;
危险操作

DROP TABLE 会删除表结构和所有数据，且不可恢复。执行前请确认：

  1. 是否需要备份数据
  2. 确认表名正确
  3. 检查是否有外键依赖

TRUNCATE TABLE​

快速清空表数据，但保留表结构：

TRUNCATE TABLE users;

TRUNCATE vs DELETE：

特性TRUNCATEDELETE
速度更快较慢
WHERE 条件不支持支持
重置 AUTO_INCREMENT是否
触发器不触发触发
回滚不支持支持（事务内）
删除方式整表删除后重建逐行删除
-- DELETE 可以回滚
START TRANSACTION;
DELETE FROM users;
ROLLBACK;  -- 数据恢复

-- TRUNCATE 不能回滚（DDL 语句隐式提交）
TRUNCATE TABLE users;  -- 无法回滚

临时表​

临时表只在当前会话可见，会话结束后自动删除。

-- 创建临时表
CREATE TEMPORARY TABLE temp_users (
    id INT,
    name VARCHAR(50)
);

-- 基于查询创建临时表
CREATE TEMPORARY TABLE temp_active_users AS
SELECT * FROM users WHERE status = 'active';

-- 查看临时表
SHOW TABLES;  -- 临时表不会显示

-- 必须显式指定才能查看
SHOW CREATE TABLE temp_users;

-- 手动删除临时表
DROP TEMPORARY TABLE IF EXISTS temp_users;

临时表特点：

  • 只在当前会话可见
  • 会话结束自动删除
  • 可以与永久表同名（临时表优先）
  • 不支持外键

表信息查询​

查看所有表​

-- 查看当前数据库的所有表
SHOW TABLES;

-- 模糊匹配
SHOW TABLES LIKE '%user%';

查看表状态​

-- 查看所有表状态
SHOW TABLE STATUS;

-- 查看指定表状态
SHOW TABLE STATUS LIKE 'users';

输出包含：存储引擎、行数、平均行长度、数据大小、索引大小、创建时间等。

从 information_schema 查询​

-- 查询表的详细信息
SELECT 
    TABLE_NAME,
    TABLE_TYPE,
    ENGINE,
    TABLE_ROWS,
    AVG_ROW_LENGTH,
    DATA_LENGTH,
    INDEX_LENGTH,
    CREATE_TIME,
    UPDATE_TIME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb';

-- 查询列信息
SELECT 
    COLUMN_NAME,
    DATA_TYPE,
    IS_NULLABLE,
    COLUMN_DEFAULT,
    COLUMN_KEY,
    EXTRA
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'users'
ORDER BY ORDINAL_POSITION;

表选项​

存储引擎​

-- 创建表时指定存储引擎
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) ENGINE = InnoDB;

-- 修改表的存储引擎
ALTER TABLE users ENGINE = InnoDB;

字符集和排序规则​

-- 创建表时指定字符集
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 修改表的字符集
ALTER TABLE users CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

自动递增起始值​

-- 设置自增起始值
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
) AUTO_INCREMENT = 1000;

-- 修改自增起始值
ALTER TABLE users AUTO_INCREMENT = 2000;

表注释​

-- 创建表时添加注释
CREATE TABLE users (
    id INT PRIMARY KEY COMMENT '用户ID',
    name VARCHAR(50) COMMENT '用户名'
) COMMENT '用户信息表';

-- 查看表注释
SHOW TABLE STATUS LIKE 'users';

最佳实践​

表设计原则​

  1. 合理选择数据类型

    • 使用能满足需求的最小数据类型
    • 整数优先选择合适的范围（TINYINT、SMALLINT、INT、BIGINT）
    • 金额使用 DECIMAL 而非 FLOAT/DOUBLE

  2. 正确使用约束

    • 每个表都应有主键
    • 适当使用 NOT NULL 约束
    • 外键约束保证数据完整性

  3. 命名规范

    • 表名使用小写，单词间用下划线分隔
    • 列名遵循同样的命名规范
    • 约束使用有意义的前缀（pk_、fk_、uk_、chk_）

示例：完整的表设计​

CREATE TABLE orders (
    -- 主键
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '订单ID',
    
    -- 业务字段
    order_no VARCHAR(32) NOT NULL COMMENT '订单编号',
    user_id BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
    total_amount DECIMAL(12, 2) NOT NULL COMMENT '订单总金额',
    status TINYINT NOT NULL DEFAULT 0 COMMENT '订单状态：0-待付款，1-已付款，2-已发货，3-已完成，4-已取消',
    
    -- 时间字段
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    
    -- 唯一约束
    CONSTRAINT uk_order_no UNIQUE (order_no),
    
    -- 检查约束
    CONSTRAINT chk_amount CHECK (total_amount >= 0),
    CONSTRAINT chk_status CHECK (status IN (0, 1, 2, 3, 4)),
    
    -- 外键约束
    CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT
    
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单表';

-- 创建索引
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_created_at ON orders(created_at);

小结​

本章我们学习了：

  1. 创建表：CREATE TABLE 语法和各种列约束
  2. 列约束：PRIMARY KEY、FOREIGN KEY、UNIQUE、NOT NULL、DEFAULT、CHECK
  3. 修改表：ALTER TABLE 添加、修改、删除列和约束
  4. 复制表：复制结构和数据的方法
  5. 删除表：DROP TABLE 和 TRUNCATE TABLE 的区别
  6. 临时表：会话级别的临时表
  7. 表选项：存储引擎、字符集、自增、注释等

练习​

  1. 创建一个包含主键、外键、唯一约束的表
  2. 练习修改表结构：添加列、修改列类型、删除列
  3. 创建一个临时表并测试其特性
  4. 比较 TRUNCATE 和 DELETE 的区别

参考资源​