第4课: 表操作

CREATE、ALTER、DROP 完整指南

创建表 (CREATE TABLE)

创建表是数据库设计的基础，需要合理定义字段类型、约束和索引。

表结构设计示意图

-- 示例1: 创建基础用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    age TINYINT UNSIGNED,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 示例2: 创建带外键的订单表
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'paid', 'shipped', 'completed') DEFAULT 'pending',
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- 示例3: 创建商品表（含索引）
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(200) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(10,2) NOT NULL,
    stock INT DEFAULT 0,
    description TEXT,
    INDEX idx_category (category),
    INDEX idx_price (price)
) ENGINE=InnoDB;

修改表结构 (ALTER TABLE)

ALTER TABLE 用于修改已存在的表结构，包括添加、修改、删除字段和约束。

表结构修改操作示意图

-- 示例4: 添加新字段
ALTER TABLE users
ADD COLUMN phone CHAR(11) AFTER email,
ADD COLUMN address VARCHAR(200);

-- 示例5: 修改字段类型和属性
ALTER TABLE users
MODIFY COLUMN username VARCHAR(100) NOT NULL,
CHANGE COLUMN age user_age TINYINT UNSIGNED;

-- 示例6: 删除字段
ALTER TABLE users
DROP COLUMN address;

-- 示例7: 添加和删除索引
ALTER TABLE products
ADD INDEX idx_name (product_name),
DROP INDEX idx_price;

-- 示例8: 添加唯一约束和外键
ALTER TABLE users
ADD UNIQUE KEY uk_email (email);

ALTER TABLE orders
ADD CONSTRAINT fk_user
FOREIGN KEY (user_id) REFERENCES users(id);

删除表 (DROP TABLE)

删除表会永久删除表及其所有数据，操作需谨慎。

-- 示例9: 删除单个表
DROP TABLE IF EXISTS temp_table;

-- 示例10: 删除多个表
DROP TABLE IF EXISTS table1, table2, table3;

查看表信息

-- 查看表结构
DESC users;
SHOW COLUMNS FROM users;

-- 查看建表语句
SHOW CREATE TABLE users;

-- 查看所有表
SHOW TABLES;

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

实战场景：电商数据库设计

-- 创建完整的电商表结构
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone CHAR(11),
    registration_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email (email)
) ENGINE=InnoDB;

CREATE TABLE categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    category_name VARCHAR(50) NOT NULL UNIQUE,
    parent_id INT DEFAULT NULL,
    FOREIGN KEY (parent_id) REFERENCES categories(category_id)
) ENGINE=InnoDB;

CREATE TABLE items (
    item_id INT PRIMARY KEY AUTO_INCREMENT,
    item_name VARCHAR(200) NOT NULL,
    category_id INT,
    price DECIMAL(10,2) NOT NULL,
    stock INT DEFAULT 0,
    FOREIGN KEY (category_id) REFERENCES categories(category_id),
    INDEX idx_category (category_id),
    INDEX idx_price (price)
) ENGINE=InnoDB;