MySQL系列：

初识MySQL，MySQL常用数据类型和表的操作，增删改查(CRUD)操作(总),数据库约束

第一范式

第一范式（1NF）要求每个列都是原子性的，不可再分。

比如，如果一个表中有个字段存储多个值，像电话号码有多个，这就不符合1NF。应该拆分成多行或者多个字段。不过，可能用户需要具体的例子，比如订单表，如果订单中的产品被放在一个字段里用逗号分隔，这就不符合1NF。拆分后每个产品单独一行，这样每个字段都是单一的。

不符合第一范式的列子：

当不能再拆分时满足第一范式：

第二范式

第二范式 (2NF)：消除部分依赖
规则：在满足 1NF 的基础上，所有非主属性必须完全依赖于主键（不存在部分依赖）。

例子：不符合 2NF 的表,假设有一个 订单明细表，主键是 订单ID + 产品ID：

订单ID 产品ID 产品名称 数量 订单日期
1001 P001 手机 2 2023-10-01
1001 P002 耳机 1 2023-10-01
问题：
产品名称 仅依赖于 产品ID（部分依赖主键）。
订单日期 仅依赖于 订单ID（部分依赖主键）。

改进为符合 2NF，拆分表，消除部分依赖：

订单表（主键：订单ID）：
订单ID 订单日期
1001 2023-10-01

产品表（主键：产品ID）：
产品ID 产品名称
P001 手机
P002 耳机

订单明细表（主键：订单ID + 产品ID）：
订单ID 产品ID 数量
1001 P001 2
1001 P002 1
现在所有非主属性完全依赖主键。

第三范式

第三范式 (3NF)：消除传递依赖
规则：在满足 2NF 的基础上，非主属性不能依赖于其他非主属性（即消除传递依赖）。

例子：不符合 3NF 的表,假设有一个 学生信息表：

学生ID 学生姓名 学院 学院电话
1 张三 计算机 010-12345678
2 李四 物理 010-87654321
问题：学院电话 依赖于 学院，而 学院 又依赖于 学生ID，形成传递依赖。

改进为符合 3NF：拆分表，消除传递依赖：

学生表（主键：学生ID）：
学生ID 学生姓名 学院
1 张三 计算机
2 李四 物理

学院表（主键：学院）：
学院 学院电话
计算机 010-12345678
物理 010-87654321
现在 学院电话 直接依赖于主键 学院。

数据库设计过程

(一)、实例之间的关系

1. 用户与账户的一对一关系
   关系描述：一个用户对应一个账户，一个账户也只能属于一个用户。
   实现方式：
   在 users 表中添加 account_id 外键（唯一约束），指向 account 表的主键 id。
   通过外键确保用户和账户的严格一对一绑定。

2. 学生与班级的一对多关系
   关系描述：一个班级可以包含多个学生，但一个学生只能属于一个班级。
   实现方式：
   在 student 表中添加 class_id 外键，指向 class 表的主键 id。
   通过外键将学生与班级关联，实现“一对多”关系。

3. 学生、课程与成绩的多对多关系
   关系描述：
   一个学生可以选修多门课程。
   一门课程可以被多个学生选修。
   每个学生的每门课程对应一个成绩。
   实现方式：
   创建中间表 score，包含 student_id 和 course_id 作为联合主键，并存储成绩。
   通过外键关联 student 和 course 表，实现多对多关系。

(二)、数据库设计过程

1. 需求分析
   业务需求：
   用户与账户需一对一绑定。
   学生需按班级分类。
   学生可选修多门课程，并记录每门课程的成绩。
   数据实体：
   用户（users）、账户（account）、班级（class）、学生（student）、课程（course）、成绩（score）。

2. 规范化设计
   第一范式 (1NF)：
   确保字段原子性，例如：
   phone_num 存储单个手机号（varchar(11)），而非多个值。
   score 表拆分为单独的中间表，避免多值存储。
   第二范式 (2NF)：
   消除部分依赖：
   score 表中的 score 字段完全依赖于联合主键 (student_id, course_id)。
   users 表的 account_id 完全依赖于主键 id。
   第三范式 (3NF)：
   消除传递依赖：
   避免在 student 表中直接存储年龄（age），而是存储出生日期（birthdate），通过计算得到年龄。
   若班级表中需要存储班主任信息，需拆分为 class 表和 teacher 表，避免传递依赖。

3.具体设计步骤
步骤 1：定义实体和属性
用户（users）：id、name、nickname、phone_num、email、gender、account_id。
账户（account）：id、username、password。
班级（class）：id、name。
学生（student）：id、name、sno、birthdate、gender、enroll_date、class_id。
课程（course）：id、name。
成绩（score）：student_id、course_id、score。

步骤 2：建立关系
一对一：users.account_id → account.id。
一对多：student.class_id → class.id。
多对多：score.student_id → student.id，score.course_id → course.id。。

(三)、实体-关系图（ER 图）

1. 实体定义
   用户（users）：
   主键：id
   属性：name、nickname、phone_num、email、gender、account_id（外键）。
   账户（account）：
   主键：id
   属性：username、password。
   班级（class）：
   主键：id
   属性：name。
   学生（student）：
   主键：id
   属性：name、sno、birthdate、gender、enroll_date、class_id（外键）。
   课程（course）：
   主键：id
   属性：name。
   成绩（score）：
   主键：(student_id, course_id)
   属性：score。
2. 关系图示
   ±------------+ ±------------+
   | account | | users |
   |-------------| 1 1 |-------------|
   | id (PK) |<--------| account_id |
   | username | | name |
   | password | | nickname |
   ±------------+ ±------------+
   △
   |
   | 1
   ±------------+ ±------------+
   | student | | class |
   |-------------| N 1 |-------------|
   | id (PK) |-------->| id (PK) |
   | name | | name |
   | sno | ±------------+
   | birthdate |
   | gender |
   | enroll_date |
   ±------------+
   △
   |
   | N
   ±------------+ ±------------+
   | score | | course |
   |-------------| N 1 |-------------|
   | student_id |-------->| id (PK) |
   | course_id |-------->| name |
   | score | ±------------+
   ±------------+
3. 关系说明
   用户 ↔ 账户：通过 users.account_id 实现一对一关联。
   学生 ↔ 班级：通过 student.class_id 实现一对多关联。
   学生 ↔ 课程：通过中间表 score 实现多对多关联。

(四)、代码实现：

#1.用户与账户的一对一
#在用户实体中添加对账户实体的关联
#账户表
drop table if exists account;
create table account(
id bigint primary key auto_increment,
username varchar(20) not null,
password varchar(32) not null
);
#用户表
drop table if exists users;
create table users(
id bigint primary key auto_increment,
name varchar(20) not null,
nickname varchar(20) comment'昵称',
phone_num varchar(11) comment'手机号',
email varchar(30) comment'邮箱编号',
gender tinyint(1) comment'性别',
account_id bigint,
foreign key(account) references account(id)
);# 在账户实体中添加对用户实体的关联
drop table if exists users;
create table users(
id bigint primary key auto_increment,
name varchar(20) not null,
nickname varchar(20),
phone_num varchar(20),
email varchar(30),
gender tinyint(1),
);
drop table if exists account;
create table account(
id bigint primary key auto_increment,
username varchar(20) not null,
password varchar(32) not null,
users_id bigint,
foreign key(users_id) references users(id)
);#2.学生与班级的一对多
#班级表
drop table if exists class;
create table class(
id bigint primary key auto_increment,
name varchar(20)
);
#学生表
drop table if exists student;
create table student(
id bigint primary key auto_increment,
name varchar(20) not null,
sno varchar(10) not null comment'学号',
age int default 18,
gender tinyint(1),
enroll_date date comment'入学日期',
class_id bigint,
foreign key(class_id) references class(id)
);#学生,课程与成绩的多对多关系
drop table if exists student;
create table student(
id bigint primary key auto_increment,
name varchar(20)not null,
sno varchar(10) not null,
age int default 18,
gender tinyint(1),
enroll_date date,
class_id bigint,
foreign key (class_id) references class(id)
);
#课程表
drop table if exists course;
create table course(
id bigint primary key auto_increment,
name varchar(20)
);
#成绩表
drop table id exists score;
create table score(
id bigint primary key auto_increment,
score float,
student_id bigint,
course_id bigint,
foreign key (student_id) references student(id),
foreign key (course_id) references course(id)
);