mysql学习笔记（5）数据库的三种设计范式

一、第一范式 1NF

第一范式数据表中的字段都是不可分割的原子值。

所谓第一范式，就是数据表的列不可再分。

create table student2 (
    id int,
    name varchar(20),
    address varchar(30)
);

insert into student2 value(1,'张三','蔡村');
insert into student2 value(2,'李四','景村');
insert into student2 value(3,'王五','站村');

mysql> select * from student2;
+------+------+---------+
| id   | name | address |
+------+------+---------+
|    1 | 张三 | 蔡村    |
|    2 | 李四 | 景村    |
|    3 | 王五 | 站村    |
+------+------+---------+
3 rows in set (0.00 sec)
#字段值还可以继续拆分的就不满足第一范式1NF

 下面这个数据表可以使用第一范式：

create table student3 (
    id int,
    name varchar(20),
    country varchar(30),
    privence varchar(30),
    city varchar(30),
    details varchar(30)
);

mysql> desc student3;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| country  | varchar(30) | YES  |     | NULL    |       |
| privence | varchar(30) | YES  |     | NULL    |       |
| city     | varchar(30) | YES  |     | NULL    |       |
| details  | varchar(30) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

insert into student3 value(1,'张三','中国','山东省','青岛市','崂山区青城大道305号');
insert into student3 value(2,'李四','中国','湖北省','武汉市','洪山区珞狮路122号');
insert into student3 value(3,'王五','中国','广东省','广东市','北环路52号');

mysql> select * from student3;
+------+------+---------+----------+--------+---------------------+
| id   | name | country | privence | city   | details             |
+------+------+---------+----------+--------+---------------------+
|    1 | 张三 | 中国    | 山东省   | 青岛市 | 崂山区青城大道305号 |
|    2 | 李四 | 中国    | 湖北省   | 武汉市 | 洪山区珞狮路122号   |
|    3 | 王五 | 中国    | 广东省   | 广东市 | 北环路52号          |
+------+------+---------+----------+--------+---------------------+
3 rows in set (0.01 sec)
#country、privence、city、detail可作为范式

一般范式设计的越详细，对于实际操作越好 。

二、第二范式 2NF

在满足第一范式的基础上，第二范式要求，除主键外的每一列都必须依赖完全于主键。（表中非主键列不存在对主键的部分依赖）

联合主键可能不依赖。

例1.设计一个订单表

create table myorder (
    product_id int,
    customer_id int,
    priduct_name varchar(20),
    customer_name varchar(20),
    primary key (product_id,customer_id) #这两个id共同组成了订单中的主键
);

--问题？
--除主键外的字段不完全依赖于主键。因此不满足第二范式。
--拆表。

create table myorder (
    order_id int primary key,
    product_id int,
    customer_id int          --product_id和customer_id完全依赖于order_id
);
create table product (
    id int primary key,
    name varchar(20)         --name完全依赖于id
);
create table customer (
    id int primary key,
    name varchar(20)         --name完全依赖于id
);

--分成三个表后就满足了第二范式的设计。

例2.学生选课表 

学号（主键）	课程（主键）	成绩	课程学分
10001	数学	100	6
10001	语文	90	2
10001	英语	85	3
10002	数学	90	6
10003	数学	99	6
10004	语文	89	2

表中主键为 （学号，课程），我们可以表示为 (学号，课程) -> (成绩，课程学分)， 表示所有非主键列 (成绩，课程学分)都依赖于主键 (学号，课程)。

但是，表中还存在另外一个依赖：（课程）->(课程学分）。这样非主键列 ‘课程学分‘ 依赖于部分主键列 ’课程‘， 所以上表是不满足第二范式的。

我们把它拆成如下2张表：

学生选课表：

学号（主键）	课程（主键）	成绩
10001	数学	100
10001	语文	90
10001	英语	85
10002	数学	90
10003	数学	99
10004	语文	89

 课程信息表：

课程（主键）	课程学分
数学	6
语文	3
英语	2

 那么上面2个表，学生选课表主键为（学号，课程），课程信息表主键为（课程），表中所有非主键列都完全依赖主键。不仅符合第二范式，还符合第三范式。 

再看这样一个学生信息表：

学号（主键）	姓名	性别	班级	班主任
10001	张三	男	一班	小王
10002	李四	男	一班	小王
10003	王五	男	二班	小李
10004	张小三	男	二班	小李

上表中，主键为：（学号），所有字段 （姓名，性别，班级，班主任）都依赖与主键（学号），不存在对主键的部分依赖。所以是满足第二范式。

三、第三范式 3NF

第三范式定义是，满足第二范式，并且表中的列不存在对非主键列的传递依赖。（除开除主键列外的其他列之间不能有传递依赖）

create table myorder (
    order_id int primary key,
    product_id int,
    customer_id int          --product_id和customer_id完全依赖于order_id
    customer_phone varchar(11)  --customer_phone和非主键的customer_id之间有联系。
);
--非第三范式。

--修改成这样：
create table myorder (
    order_id int primary key,
    product_id int,
    customer_id int          
);
create table customer (
    id int primary key,
    name varchar(20),         --name完全依赖于id
    phone varchar(11)         --放到customer里就行了
);
满足了第三范式