数据库MySQL学习——day11(高级连接与数据库规范化)

1：高级连接（Advanced Joins）

1.1 SELF JOIN（自连接）

定义 ：SELF JOIN 是一个表连接它自己，用于表中的行之间有关联的情况。

• 示例：员工与上级之间的关系
  创建一个员工表，每个员工有一个上级：

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT
);

插入数据：

INSERT INTO employees (emp_id, name, manager_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2),
(5, 'Eva', 2);

目标 ：找出每个员工及其上级的名字：

SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.emp_id;

解释 ：

• 表employees自我连接：e1 是员工，e2 是他们的上级。
• 利用 manager_id 与 emp_id 关联。

1.2 CROSS JOIN（笛卡尔积）

定义 ：每一行左表的数据都会和右表的每一行匹配，产生组合。

• 示例：用户与权限组合

CREATE TABLE users (
    user_id INT,
    username VARCHAR(50)
);

CREATE TABLE permissions (
    perm_id INT,
    perm_name VARCHAR(50)
);

INSERT INTO users VALUES (1, 'Tom'), (2, 'Jerry');
INSERT INTO permissions VALUES (1, 'Read'), (2, 'Write');

目标 ：列出每个用户和所有权限的组合（模拟分配权限）

SELECT u.username, p.perm_name
FROM users u
CROSS JOIN permissions p;

输出 ：

• Tom Read
• Tom Write
• Jerry Read
• Jerry Write

适用场景 ：生成所有组合、数据分析、初始化权限分配等。

2：数据库规范化（Database Normalization）

2.1 什么是规范化？

数据库规范化（Normalization）是一种结构优化方法，用来：

• 消除数据冗余（重复）
• 避免插入、删除、更新异常
• 提高数据一致性和存储效率

2.2 第一范式（1NF） – 每个字段只有一个值

非规范化数据示例（错误）：

学号	姓名	课程
001	张三	数学, 英语, 历史

问题 ：一列包含多个值。

1NF 修正方式 （每列只有原子值）：

学号	姓名	课程
001	张三	数学
001	张三	英语
001	张三	历史

2.3 第二范式（2NF） – 消除部分依赖

前提 ：表已满足 1NF

问题 ：非主属性只依赖于主键的一部分（通常是复合主键）
非规范化示例：

学号	课程	姓名	分数
001	数学	张三	90
001	英语	张三	85

问题 ：

• 主键是（学号+课程）
• 姓名只依赖“学号”，和“课程”无关 → 部分依赖

2NF修正方式 ：
拆成两张表：
① 学生表：

学号	姓名
001	张三

② 成绩表：

学号	课程	分数
001	数学	90
001	英语	85

2.3 第三范式（3NF） – 消除传递依赖

前提 ：表已满足 2NF
问题 ：非主属性依赖其他非主属性
非规范化示例：

学号	姓名	班级编号	班级名称
001	张三	101	一班

问题 ：

• “班级名称”依赖“班级编号”
• “班级编号”依赖“学号” → 传递依赖

3NF 修正方式 ：

① 学生表：

学号	姓名	班级编号
001	张三	101

② 班级表：

班级编号	班级名称
101	一班

3. 实践任务

3.1：SELF JOIN 操作

SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.emp_id;

3.2：CROSS JOIN 生成所有组合

SELECT u.username, p.perm_name
FROM users u
CROSS JOIN permissions p;

3.3：表规范化（以选课系统为例）

非规范化表：

学号	姓名	课程
001	张三	数学, 英语
002	李四	数学, 物理

规范化后：

• 学生表（students）：

CREATE TABLE students (
    student_id VARCHAR(10) PRIMARY KEY,
    name VARCHAR(50)
);

• 课程表（courses）：

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(50)
);

• 选课关系表（enrollments）：

CREATE TABLE enrollments (
    student_id VARCHAR(10),
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

4. 总结

连接类型	说明
SELF JOIN	表自己连接自己
CROSS JOIN	两表所有组合（笛卡尔积）

范式	原则
1NF	每列值必须是原子值（不能有逗号分隔）
2NF	消除部分依赖（非主属性依赖主键全部）
3NF	消除传递依赖（非主属性不依赖其他非主属性）