练习如何设计一个表-上

基础知识

在使用sql设计一个表之前，我们需要先了解一些基础知识，如果已经了解的同学可以跳过，直接查看后面的练习

基础知识-主键

主键是数据库表中的一列或一组列（复合主键），其作用是唯一标识表中的每一行数据。

特点：

• 唯一性：值必须是唯一的。
• 不为空：值不能为 NULL。
• 稳定性：值应该保持稳定不变。

示例：

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

基础知识-外键

外键是一个表中的列，它与另一个表中的主键列相关联，用于建立表之间的关联关系。 特点：

• 可以确保数据的完整性和一致性。
• 用于建立表之间的关联关系
• 外键的值必须在关联表的主键列中存在，或者为 NULL。

示例：

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

基础知识-范式

范式是数据库设计的一种规范化过程，目的是消除数据冗余，确保数据的一致性。常见的范式包括第一范式（1NF）、第二范式（2NF）和第三范式（3NF）等等，通常我们建议遵守设计范式，但也存在使用空间换时间的情况，这时候有一些冗余字段也是允许的。

• 第一范式（1NF）要求表中的每一列都是原子性的，不可再分。
• 第二范式（2NF）要求表中的每一行都具有唯一标识，通常需要使用主键来实现。
• 第三范式（3NF）要求表中的每一列都与主键列直接相关，不应该存在传递依赖关系。 反例就是在上的orders表中添加username， user_age两个字段，这两个字段不依赖于orders表的主键，它们应该拆到一个users表中 反例：

  CREATE TABLE orders (
      id INT PRIMARY KEY,
      user_id INT,
      username VARCHAR(255),
      user_age INT
      FOREIGN KEY (user_id) REFERENCES users(id)
  );
  

基础知识-表关系

在数据库设计中，不同表之间的关系可以分为以下几种：

• 从属关系（One-to-One）：每个表中的一行数据对应另一个表中的一行数据。一个人可以有一个身份证号，一个身份证号只对应一个人。
• 一对多关系（One-to-Many）：每个表中的一行数据可以对应另一个表中的多行数据。一个国家可以有多个城市，但每个城市只属于一个国家。
• 多对一关系（Many-to-One）：多个表中的数据行可以关联到另一个表中的一行数据。多个城市可以属于同一国家
• 多对多关系（Many-to-Many）：多个表中的多个数据行可以关联到其他表中的多个数据行。一个帖子可以有多个作者，一个作者也可以编辑多个帖子

-- 创建帖子表
CREATE TABLE posts (
    post_id INT PRIMARY KEY,
    title VARCHAR(255)
);

-- 创建作者表
CREATE TABLE authors (
    author_id INT PRIMARY KEY,
    author_name VARCHAR(255)
);

-- 创建关联表来表示多对多关系
CREATE TABLE posts_authors (
    post_id INT,
    author_id INT,
    PRIMARY KEY (post_id, author_id),
    FOREIGN KEY (post_id) REFERENCES posts(post_id),
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

常用的数据结构如何用 SQL 表示

树

邻接表

最直观的就是把节点结构转成表结构。 示例：

CREATE TABLE tree_nodes (
    id INT PRIMARY KEY,
    parent_id INT, 
    name VARCHAR(255)
);

缺点：

• 要查询子树需要递归
• 要查询所有的祖先节点也需要递归

闭包表

一个表维护节点信息，一个表维护节点关系

CREATE TABLE nodes (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR ,
    PRIMARY KEY (id)
)

CREATE TABLE node_relation (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    ancestor_id INT NOT NULL, -- 祖先节点
    descendant_id INT NOT NULL, -- 后代节点
    distance INT NOT NULL, -- 距离
    PRIMARY KEY (ancestor_id, descendant_id),
    FOREIGN KEY (ancestor_id) REFERENCES nodes(id),
    FOREIGN KEY (descendant_id) REFERENCES nodes(id)
)

nodes：

id	name
1	Node A
2	Node B
3	Node C
4	Node D
5	Node E

node_relation：

ancestor_id	descendant_id	distance
1	2	1
1	3	1
2	4	1
3	5	1

  Node A
  /     \
Node B  Node C
  |       |
Node D  Node E

很好的解决了邻接表的查询效率问题

查询id为4的所有祖先

SELECT n.*
FROM nodes n
JOIN node_relation nr ON n.id = nr.ancestor_id
WHERE nr.descendant_id = 4;

查询id为4的子节点

SELECT n.*
FROM nodes n
JOIN node_relation nr ON n.id = nr.descendant_id
WHERE nr.ancestor_id = 4;

更多的练习放在下一篇