表操作

表是数据库中存储数据的基本单位。本章将介绍MySQL表的创建、修改、删除等操作。

创建表

基本语法

sql

-- 创建表的基本语法
/*
CREATE TABLE [IF NOT EXISTS] 表名 (
    列名1 数据类型 [列约束],
    列名2 数据类型 [列约束],
    ...
    [表约束]
) [表选项];
*/

-- 创建简单的表
CREATE TABLE students (
    id INT,
    name VARCHAR(50),
    age INT,
    score DECIMAL(5, 2)
);

-- 创建带约束的表
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    department_id INT,
    salary DECIMAL(10, 2) DEFAULT 0,
    hire_date DATE,
    status ENUM('active', 'inactive') DEFAULT 'active'
);

-- 使用IF NOT EXISTS避免重复创建
CREATE TABLE IF NOT EXISTS products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2),
    stock INT DEFAULT 0
);

列约束

sql

/*
 * MySQL列约束：
 * 
 * PRIMARY KEY    - 主键约束（唯一标识）
 * AUTO_INCREMENT - 自动递增
 * NOT NULL       - 非空约束
 * UNIQUE         - 唯一约束
 * DEFAULT        - 默认值
 * COMMENT        - 注释
 * FOREIGN KEY    - 外键约束
 */

-- 创建带各种约束的表
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '订单ID',
    order_no VARCHAR(32) NOT NULL UNIQUE COMMENT '订单号',
    user_id INT NOT NULL COMMENT '用户ID',
    total_amount DECIMAL(10, 2) NOT NULL DEFAULT 0 COMMENT '总金额',
    status TINYINT DEFAULT 0 COMMENT '订单状态：0待支付 1已支付 2已发货 3已完成',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) COMMENT '订单表';

-- 查看表结构
DESC orders;

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

表约束

sql

-- 主键约束
CREATE TABLE primary_key_example (
    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 departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE employees_fk (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    department_id INT,
    salary DECIMAL(10, 2),
    
    -- 外键约束
    CONSTRAINT fk_department 
    FOREIGN KEY (department_id) 
    REFERENCES departments(id)
    ON DELETE SET NULL      -- 删除部门时，员工部门ID设为NULL
    ON UPDATE CASCADE       -- 更新部门ID时，级联更新
);

/*
 * 外键约束选项：
 * ON DELETE/ON UPDATE:
 * - CASCADE：级联操作
 * - SET NULL：设为NULL
 * - RESTRICT：拒绝操作（默认）
 * - NO ACTION：无操作
 */

-- 唯一约束
CREATE TABLE unique_example (
    id INT PRIMARY KEY,
    email VARCHAR(100),
    phone VARCHAR(20),
    
    UNIQUE KEY uk_email (email),
    UNIQUE KEY uk_phone (phone)
);

-- 检查约束（MySQL 8.0+）
CREATE TABLE check_example (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    salary DECIMAL(10, 2),
    
    CONSTRAINT chk_age CHECK (age >= 18 AND age <= 65),
    CONSTRAINT chk_salary CHECK (salary >= 0)
);

表选项

sql

-- 创建表时指定表选项
CREATE TABLE table_options (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
) 
ENGINE = InnoDB                    -- 存储引擎
DEFAULT CHARSET = utf8mb4          -- 字符集
COLLATE = utf8mb4_unicode_ci       -- 校对规则
AUTO_INCREMENT = 1000              -- 自增起始值
COMMENT = '示例表';                 -- 表注释

-- 查看表的存储引擎
SHOW TABLE STATUS LIKE 'table_options';

-- 查看表注释
SELECT 
    TABLE_NAME,
    TABLE_COMMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE();

查看表

sql

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

-- 查看表结构
DESC students;
DESCRIBE students;

-- 查看详细表结构
SHOW COLUMNS FROM students;

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

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

-- 查看表的索引
SHOW INDEX FROM students;

-- 查看表空间使用情况
SELECT 
    TABLE_NAME,
    ENGINE,
    TABLE_ROWS,
    DATA_LENGTH,
    INDEX_LENGTH,
    DATA_LENGTH + INDEX_LENGTH AS total_size
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE();

修改表

添加列

sql

-- 添加单列
ALTER TABLE students 
ADD COLUMN gender ENUM('男', '女') AFTER name;

-- 添加多列
ALTER TABLE students 
ADD COLUMN address VARCHAR(200),
ADD COLUMN phone VARCHAR(20);

-- 添加列到第一列
ALTER TABLE students 
ADD COLUMN student_no VARCHAR(20) FIRST;

-- 添加带约束的列
ALTER TABLE students 
ADD COLUMN email VARCHAR(100) UNIQUE;

修改列

sql

-- 修改列数据类型
ALTER TABLE students 
MODIFY COLUMN name VARCHAR(100) NOT NULL;

-- 修改列名和数据类型
ALTER TABLE students 
CHANGE COLUMN score total_score DECIMAL(6, 2);

-- 修改列默认值
ALTER TABLE students 
ALTER COLUMN age SET DEFAULT 18;

-- 删除列默认值
ALTER TABLE students 
ALTER COLUMN age DROP DEFAULT;

-- 修改列注释
ALTER TABLE students 
MODIFY COLUMN name VARCHAR(100) COMMENT '学生姓名';

删除列

sql

-- 删除列
ALTER TABLE students 
DROP COLUMN address;

-- 删除多列
ALTER TABLE students 
DROP COLUMN phone,
DROP COLUMN email;

修改表名

sql

-- 重命名表
ALTER TABLE students 
RENAME TO student_info;

-- 或使用RENAME TABLE
RENAME TABLE student_info TO students;

-- 重命名多个表
RENAME TABLE 
    old_table1 TO new_table1,
    old_table2 TO new_table2;

修改表选项

sql

-- 修改存储引擎
ALTER TABLE students ENGINE = InnoDB;

-- 修改字符集
ALTER TABLE students DEFAULT CHARSET = utf8mb4;

-- 修改表注释
ALTER TABLE students COMMENT = '学生信息表';

-- 修改自增起始值
ALTER TABLE students AUTO_INCREMENT = 100;

添加和删除约束

sql

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

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

-- 添加唯一约束
ALTER TABLE students 
ADD UNIQUE KEY uk_student_no (student_no);

-- 删除唯一约束
ALTER TABLE students 
DROP INDEX uk_student_no;

-- 添加外键约束
ALTER TABLE orders
ADD CONSTRAINT fk_user
FOREIGN KEY (user_id) REFERENCES users(id);

-- 删除外键约束
ALTER TABLE orders
DROP FOREIGN KEY fk_user;

-- 添加检查约束（MySQL 8.0+）
ALTER TABLE students
ADD CONSTRAINT chk_age CHECK (age >= 0 AND age <= 150);

-- 删除检查约束
ALTER TABLE students
DROP CHECK chk_age;

删除表

sql

-- 删除表
DROP TABLE students;

-- 删除表前检查是否存在
DROP TABLE IF EXISTS students;

-- 删除多个表
DROP TABLE table1, table2, table3;

-- 清空表数据（保留表结构）
TRUNCATE TABLE students;

/*
 * DELETE vs TRUNCATE：
 * 
 * DELETE：
 * - 可以带WHERE条件
 * - 逐行删除，记录日志
 * - 不会重置AUTO_INCREMENT
 * - 可以回滚
 * 
 * TRUNCATE：
 * - 删除所有数据
 * - 整表删除，不记录日志
 * - 重置AUTO_INCREMENT
 * - 不能回滚
 * - 执行更快
 */

临时表

sql

-- 创建临时表（会话结束后自动删除）
CREATE TEMPORARY TABLE temp_users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 临时表只在当前会话可见
INSERT INTO temp_users VALUES (1, '张三');

SELECT * FROM temp_users;

-- 断开连接后临时表自动删除
-- 也可以手动删除
DROP TEMPORARY TABLE temp_users;

表复制

sql

-- 创建表示例数据
CREATE TABLE original_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT
);

INSERT INTO original_table (name, age) VALUES
('张三', 25),
('李四', 30),
('王五', 28);

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

-- 只复制表结构
CREATE TABLE structure_copy LIKE original_table;

-- 复制部分数据
CREATE TABLE partial_copy AS
SELECT id, name FROM original_table WHERE age > 26;

-- 使用INSERT...SELECT复制数据
INSERT INTO structure_copy
SELECT * FROM original_table;

本章小结

本章学习了：

创建表：CREATE TABLE语法和列约束
表约束：主键、外键、唯一、检查约束
查看表：DESC、SHOW CREATE TABLE等命令
修改表：ALTER TABLE添加、修改、删除列
删除表：DROP TABLE和TRUNCATE TABLE
临时表：会话级别的临时表
表复制：复制表结构和数据

下一章，我们将学习数据操作，了解如何插入、更新和删除数据。

表操作 ​

创建表 ​

基本语法 ​

列约束 ​

表约束 ​

表选项 ​

查看表 ​

修改表 ​

添加列 ​

修改列 ​

删除列 ​

修改表名 ​

修改表选项 ​

添加和删除约束 ​

删除表 ​

临时表 ​

表复制 ​

本章小结 ​

表操作

创建表

基本语法

列约束

表约束

表选项

查看表

修改表

添加列

修改列

删除列

修改表名

修改表选项

添加和删除约束

删除表

临时表

表复制

本章小结