第七章练习答案

01-ddl-basics

练习 1：创建表

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT DEFAULT 0
);

练习 2：添加列

ALTER TABLE products ADD COLUMN description TEXT;

练习 3：修改列类型

-- PostgreSQL
ALTER TABLE products ALTER COLUMN price TYPE DECIMAL(10,2);

-- MySQL
ALTER TABLE products MODIFY COLUMN price DECIMAL(10,2);

练习 4：重命名

ALTER TABLE products RENAME COLUMN name TO product_name;
ALTER TABLE products RENAME TO items;

练习 5：删除列

ALTER TABLE products DROP COLUMN description;

练习 6：临时表

CREATE TEMP TABLE temp_data (id INT, value TEXT);

练习 7：从查询创建表

CREATE TABLE expensive_products AS
SELECT * FROM products WHERE price > 100;

练习 8：IF EXISTS

CREATE TABLE IF NOT EXISTS products (id INT);
DROP TABLE IF EXISTS products;

---

02-data-types

练习 1：不同数值类型

CREATE TABLE demo (
    small_num SMALLINT,
    normal_num INT,
    big_num BIGINT,
    price DECIMAL(10,2),
    ratio FLOAT
);

练习 2：CHAR vs VARCHAR

CREATE TABLE test (
    code CHAR(5),
    name VARCHAR(100)
);
INSERT INTO test VALUES ('ABC', 'Alice');
SELECT LENGTH(code), LENGTH(name) FROM test;

练习 3：金额

CREATE TABLE transactions (
    id INT,
    amount DECIMAL(10,2)
);

练习 4：时间戳

CREATE TABLE events (
    id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

练习 5：JSONB（PostgreSQL）

CREATE TABLE products (
    id INT,
    metadata JSONB
);
INSERT INTO products VALUES (1, '{"brand": "Apple", "color": "Silver"}');

练习 6：数组（PostgreSQL）

CREATE TABLE articles (
    id INT,
    tags TEXT[]
);
INSERT INTO articles VALUES (1, ARRAY['sql', 'database']);

练习 7：用户表

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    age SMALLINT CHECK (age >= 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

练习 8：订单表

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

---

03-constraints

练习 1：主键

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

练习 2：外键

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

练习 3：CASCADE

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 测试
INSERT INTO users VALUES (1, 'Alice');
INSERT INTO orders VALUES (1, 1);
DELETE FROM users WHERE id = 1;  -- orders 中的记录也被删除

练习 4：唯一约束

ALTER TABLE users ADD UNIQUE (email);

练习 5：检查约束

ALTER TABLE users ADD CHECK (age >= 18);

练习 6：默认值

CREATE TABLE orders (
    id INT,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

练习 7：命名约束

CREATE TABLE users (
    id INT,
    email VARCHAR(255),
    age INT,
    CONSTRAINT pk_users PRIMARY KEY (id),
    CONSTRAINT uk_email UNIQUE (email),
    CONSTRAINT chk_age CHECK (age >= 0)
);

练习 8：删除和添加约束

ALTER TABLE users DROP CONSTRAINT uk_email;
ALTER TABLE users ADD CONSTRAINT uk_email UNIQUE (email);

---

04-normalization

练习 1：识别违反 1NF

-- 违反 1NF
CREATE TABLE orders (
    id INT,
    products VARCHAR(255)  -- 'Laptop, Mouse'
);

-- 修正
CREATE TABLE order_items (
    order_id INT,
    product VARCHAR(100)
);

练习 2：范式化到 2NF

-- 违反 2NF
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    product_name VARCHAR(100),  -- 部分依赖
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

-- 修正
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

练习 3：范式化到 3NF

-- 违反 3NF
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    user_name VARCHAR(100),
    user_email VARCHAR(255)
);

-- 修正
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255)
);

练习 4：电商系统

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100)
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    price DECIMAL(10,2)
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
    order_id INT REFERENCES orders(id),
    product_id INT REFERENCES products(id),
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

练习 5：博客系统

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE
);

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    author_id INT REFERENCES users(id),
    title VARCHAR(200),
    content TEXT
);

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    article_id INT REFERENCES articles(id),
    user_id INT REFERENCES users(id),
    content TEXT
);

CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE
);

CREATE TABLE article_tags (
    article_id INT REFERENCES articles(id),
    tag_id INT REFERENCES tags(id),
    PRIMARY KEY (article_id, tag_id)
);

---

复习要点

DDL 操作

• CREATE TABLE、ALTER TABLE、DROP TABLE
• ADD/DROP/MODIFY COLUMN
• RENAME

数据类型

• 整数：SMALLINT、INT、BIGINT
• 小数：DECIMAL（精确）、FLOAT（近似）
• 字符串：CHAR、VARCHAR、TEXT
• 日期时间：DATE、TIMESTAMP
• 其他：BOOLEAN、JSON、ARRAY

约束

• PRIMARY KEY：唯一标识
• FOREIGN KEY：引用完整性
• UNIQUE：唯一性
• NOT NULL：非空
• CHECK：自定义规则
• DEFAULT：默认值

范式化

• 1NF：原子值
• 2NF：完全依赖主键
• 3NF：不依赖非主属性
• 反范式化：性能优化

---

SQL 课程完成！

恭喜完成 SQL 七章的学习！你已经掌握：

• 查询、过滤、排序
• 聚合、分组、汇总
• 连接、子查询、集合
• 窗口函数、CTE、递归
• 事务、隔离、锁、MVCC
• 索引、性能优化
• DDL、约束、范式化

返回第七章概览 · 返回SQL 学习路线