跳到主要内容

SQL 表操作和约束

本章介绍如何创建、修改、删除数据表，以及如何使用约束。

CREATE TABLE 创建表​

基本语法​

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

完整示例​

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50) UNIQUE,
    age INT DEFAULT 18,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

约束类型​

1. PRIMARY KEY（主键）​

  • 唯一标识每条记录
  • 不能为空
  • 一个表只能有一个主键
-- 方式1：列级约束
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 方式2：表级约束
CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    PRIMARY KEY (id)
);

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

2. AUTO_INCREMENT（自增）​

  • 自动生成唯一ID
  • 通常与主键配合使用
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);
-- 插入时省略 id，会自动生成
INSERT INTO users (name) VALUES ('张三');

3. NOT NULL（非空）​

  • 列不能为空
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50)
);

4. UNIQUE（唯一）​

  • 列值不能重复
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(50) UNIQUE
);

5. DEFAULT（默认值）​

  • 列的默认值
CREATE TABLE users (
    id INT PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

6. CHECK（检查）​

  • 限制列的取值范围
CREATE TABLE users (
    id INT PRIMARY KEY,
    age INT CHECK (age >= 0 AND age <= 150)
);

7. FOREIGN KEY（外键）​

  • 关联另一个表
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    product_id INT,
    quantity INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

ALTER TABLE 修改表​

添加列​

ALTER TABLE users ADD COLUMN phone VARCHAR(20);

删除列​

ALTER TABLE users DROP COLUMN phone;

修改列​

-- 修改列类型
ALTER TABLE users MODIFY COLUMN name VARCHAR(100);

-- 修改列名和类型
ALTER TABLE users CHANGE COLUMN name username VARCHAR(50);

添加约束​

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

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

-- 添加检查约束
ALTER TABLE users ADD CONSTRAINT age_check 
CHECK (age >= 0 AND age <= 150);

删除约束​

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

-- 删除外键
ALTER TABLE orders DROP FOREIGN KEY orders_ibfk_1;

-- 删除唯一约束
ALTER TABLE users DROP INDEX email;

DROP TABLE 删除表​

-- 删除表（不可恢复）
DROP TABLE users;

-- 删除前先备份
CREATE TABLE users_backup AS SELECT * FROM users;
DROP TABLE users;

TRUNCATE 清空表​

-- 清空表数据，重置自增
TRUNCATE TABLE users;

CREATE INDEX 索引​

创建索引​

-- 单列索引
CREATE INDEX idx_name ON users(name);

-- 唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

-- 复合索引
CREATE INDEX idx_name_age ON users(name, age);

删除索引​

DROP INDEX idx_name ON users;

约束综合示例​

CREATE TABLE employees (
    -- 主键自增
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- 非空唯一
    employee_id VARCHAR(10) NOT NULL UNIQUE,
    
    -- 非空
    name VARCHAR(50) NOT NULL,
    
    -- 默认值
    department VARCHAR(50) DEFAULT 'General',
    
    -- 检查约束
    salary DECIMAL(10, 2) CHECK (salary >= 0),
    
    -- 外键关联部门表
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(id),
    
    -- 创建时间
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

常见错误​

1. 主键重复​

INSERT INTO users (id, name) VALUES (1, '张三');
INSERT INTO users (id, name) VALUES (1, '李四');
-- Error: Duplicate entry '1' for key 'PRIMARY'

2. 外键引用不存在​

-- users 表不存在 id=100 的记录
INSERT INTO orders (user_id, product_id) VALUES (100, 1);
-- Error: Cannot add or update a child row

3. 违反 CHECK 约束​

INSERT INTO users (name, age) VALUES ('张三', -1);
-- Error: Check constraint violation

小结​

本章我们学习了：

  1. CREATE TABLE 创建表
  2. 各种约束（PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT, CHECK, FOREIGN KEY）
  3. ALTER TABLE 修改表结构
  4. DROP TABLE / TRUNCATE 删除表
  5. 索引的创建和使用

练习​

  1. 创建一个学生表，包含学号、姓名、年龄、班级（使用约束）
  2. 添加一个索引用于查询学生
  3. 修改表结构，添加邮箱字段
  4. 删除表并重建