数据库设计

1. 介绍

数据库设计是创建高效、可靠的数据存储结构的过程，旨在确保数据的一致性、完整性和可访问性。以下是数据库设计中的关键知识点。

1.1 实体与关系

• 实体：可以被独立识别的事物，如用户、产品、订单等。
• 属性：实体的特征，例如用户的姓名、邮箱等。
• 关系：实体之间的联系，比如一个用户可以下多个订单。

1.2 ER图（实体-关系图）

ER图是用于描述数据库结构的图示工具，通过图形化的方式展示实体、属性和实体之间的关系。以下是一个简单的ER图示例：

[用户]----<下单>----[订单]
  |                    |
  |                    |
[产品]---------------<包含>

在这个示例中，用户与订单之间是一对多的关系，一个用户可以有多个订单，而每个订单可以包含多个产品。

1.3 范式理论

范式理论是数据库设计的规范，用于减少数据冗余和提高数据一致性。常见的范式包括：

• 第一范式（1NF）：要求每个字段的值都是不可分割的原子值。比如，不应该在一个字段中存储多个电子邮件地址。

• 第二范式（2NF）：在满足第一范式的基础上，要求所有非主键字段完全依赖于主键。即不能存在部分依赖的情况。

• 第三范式（3NF）：在满足第二范式的基础上，要求非主键字段不依赖于其他非主键字段。即消除传递依赖。

1.4 主键与外键

• 主键：唯一标识表中每一行的字段，确保记录的唯一性。

• 外键：用于在两个表之间建立联系的字段，通常是一个表的主键在另一个表中出现，用于实现数据的完整性。

2. 代码案例

以下是一个简单的数据库设计示例，展示了如何设计一个用于管理在线商店的数据库。

2.1 创建数据库

-- 创建数据库，如果已存在则不再创建
CREATE
DATABASE IF NOT EXISTS online_store;

-- 选择刚创建的数据库进行操作
USE
online_store;

2.2 创建表

-- 创建用户表
DROP TABLE IF EXISTS users; -- 如果表已存在，先删除再创建
CREATE TABLE users
(
    user_id    INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID，主键，自增',
    username   VARCHAR(50)  NOT NULL UNIQUE COMMENT '用户名，不能为空，唯一',
    email      VARCHAR(100) NOT NULL UNIQUE COMMENT '电子邮箱，不能为空，唯一',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间，默认当前时间'
) COMMENT='用户信息表';

-- 创建产品表
DROP TABLE IF EXISTS products;
CREATE TABLE products
(
    product_id   INT PRIMARY KEY AUTO_INCREMENT COMMENT '产品ID，主键，自增',
    product_name VARCHAR(100)   NOT NULL COMMENT '产品名称，不能为空',
    price        DECIMAL(10, 2) NOT NULL COMMENT '产品价格，不能为空，最多10位数字，其中2位小数',
    stock        INT            NOT NULL DEFAULT 0 COMMENT '库存数量，不能为空，默认为0',
    created_at   TIMESTAMP               DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间，默认当前时间'
) COMMENT='产品信息表';

-- 创建订单表
DROP TABLE IF EXISTS orders;
CREATE TABLE orders
(
    order_id   INT PRIMARY KEY AUTO_INCREMENT COMMENT '订单ID，主键，自增',
    user_id    INT            NOT NULL COMMENT '用户ID，不能为空，外键',
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '订单日期，默认当前时间',
    total      DECIMAL(10, 2) NOT NULL COMMENT '订单总金额，不能为空，最多10位数字，其中2位小数',
    FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE COMMENT '外键约束，引用用户表'
) COMMENT='订单信息表';

-- 创建订单产品关联表
DROP TABLE IF EXISTS order_products;
CREATE TABLE order_products
(
    order_product_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '订单产品ID，主键，自增',
    order_id         INT NOT NULL COMMENT '订单ID，不能为空，外键',
    product_id       INT NOT NULL COMMENT '产品ID，不能为空，外键',
    quantity         INT NOT NULL DEFAULT 1 COMMENT '产品数量，默认为1',
    FOREIGN KEY (order_id) REFERENCES orders (order_id) ON DELETE CASCADE COMMENT '外键约束，引用订单表',
    FOREIGN KEY (product_id) REFERENCES products (product_id) ON DELETE CASCADE COMMENT '外键约束，引用产品表'
) COMMENT='订单与产品的关联信息表';

2.3 插入数据

-- 向用户表中插入数据
INSERT INTO users (username, email)
VALUES ('alice', 'alice@example.com'),
       ('bob', 'bob@example.com');

-- 向产品表中插入数据
INSERT INTO products (product_name, price, stock)
VALUES ('手机', 2999.99, 50),
       ('笔记本电脑', 4999.99, 30);

-- 向订单表中插入数据
INSERT INTO orders (user_id, total)
VALUES (1, 2999.99), -- 用户ID为1的用户下单
       (2, 4999.99);
-- 用户ID为2的用户下单

-- 向订单产品关联表中插入数据
INSERT INTO order_products (order_id, product_id, quantity)
VALUES (1, 1, 1), -- 订单ID为1，产品ID为1，数量为1
       (1, 2, 1), -- 订单ID为1，产品ID为2，数量为1
       (2, 2, 1); -- 订单ID为2，产品ID为2，数量为1

2.4 查询数据

-- 查询所有用户的信息
SELECT *
FROM users;

-- 查询所有产品的信息
SELECT *
FROM products;

-- 查询所有订单的信息，连同用户信息
SELECT o.order_id, u.username, o.order_date, o.total
FROM orders o
         JOIN users u ON o.user_id = u.user_id;

-- 查询订单及其产品信息
SELECT op.order_id, p.product_name, op.quantity
FROM order_products op
         JOIN products p ON op.product_id = p.product_id;

2.5 更新数据

-- 更新用户的电子邮件地址
UPDATE users
SET email = 'alice_new@example.com'
WHERE username = 'alice';

-- 更新产品的库存数量
UPDATE products
SET stock = stock - 1
WHERE product_id = 1; -- 假设用户购买了ID为1的产品

2.6 删除数据

-- 删除订单
DELETE
FROM orders
WHERE order_id = 1;
-- 删除ID为1的订单

-- 删除用户
DELETE
FROM users
WHERE user_id = 2; -- 删除ID为2的用户

2.7 规范化示例

在设计数据库时，遵循规范化原则，确保数据的完整性。例如，在订单表中，我们不应该重复存储用户信息，而是通过外键引用用户表中的信息，这样就减少了数据冗余。

3. 总结

今天我们介绍了数据库设计的基础知识，包括实体、属性、关系、ER图、范式理论以及主键与外键的概念。通过一个简单的在线商店的数据库设计示例，我们展示了如何创建数据库、表、插入数据以及查询和更新数据的基本操作。

希望大家能对数据库设计有一个初步的了解，后续我们将深入学习更复杂的数据库设计和优化技巧。