mysql范式学习_mysql ---范式的学习

--数据库的三大范式 ，sql

--1.第一范式--1NF--数据表中的所有字段都是不可分割的原子值

create tablestudent2(

idint primary key,

namevarchar(20),

addressvarchar(30)

);

mysql> insert into student2 values(1,"xiaozhan","chongqing");

Query OK,1 row affected (0.01sec)

mysql> insert into student2 values(2,"wangyibo","henan");

Query OK,1 row affected (0.00sec)

mysql> insert into student2 values(3,"wanghan","hunan");

Query OK,1 row affected (0.01sec)

mysql> select * fromstudent2;+----+----------+-----------+

| id | name | address |

+----+----------+-----------+

| 1 | xiaozhan | chongqing |

| 2 | wangyibo | henan |

| 3 | wanghan | hunan |

+----+----------+-----------+

3 rows in set (0.00sec)

mysql>

----字段值还可以继续拆分的，就不满足第一范式。

create tablestudent3(

idint primary key,

namevarchar(20),

countryvarchar(30),

privencevarchar(30),

cityvarchar(30),

detailsvarchar(30)

);

mysql> descstudent3;+----------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+----------+-------------+------+-----+---------+-------+

| id | int | NO | PRI | 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.00sec)insert into student3 values(1,"zhangsan","中国","江苏","南京","江宁区");insert into student3 values(1,"zhangsan","中国","江苏","南京","溧水");insert into student3 values(1,"zhangsan","中国","湖北","十堰","竹溪");

mysql> insert into student3 values(2,"zhangsan","中国","湖北","十堰","竹溪");

Query OK,1 row affected (0.00sec)

mysql> insert into student3 values(3,"zhangsan","中国","江苏","南京","溧水");

Query OK,1 row affected (0.00sec)

mysql> select * fromstudent3;+----+----------+---------+----------+--------+-----------+

| id | name | country | privence | city | details |

+----+----------+---------+----------+--------+-----------+

| 1 | zhangsan | 中国 | 江苏 | 南京 | 江宁区 |

| 2 | zhangsan | 中国 | 湖北 | 十堰 | 竹溪 |

| 3 | zhangsan | 中国 | 江苏 | 南京 | 溧水 |

+----+----------+---------+----------+--------+-----------+

3 rows in set (0.00sec)

mysql>

---范式，设计的越详细，对于某些实际操作可能更好，但是不一定都是好处。---范式的设计根据实际的项目的开发设定，不一定非得满足某个特定的范式

---2.第二范式

---必须是满足第一范式下的前提下，第二范式要求，除主键外的每一列都必须完全依赖于主键，---如果要出现不完全依赖，只可能发生在联合组建的情况下。

---订单表

create tablemyorder(

product_idint,

customer_idint,

product_namevarchar(20),

customer_namevarchar(20),primary key(product_id,customer_id)

);---问题

---除主键以外的其他例，只依赖与主键的部分字段。--拆表。

create tablemyorder(

order_idint primary key,

product_idint,

customer_idint);create tableproduct(

idint primary key,

namevarchar(20)

);create tablecustomer(

idint primary key,

namevarchar(20),

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

---第三范式：---3nf---必须先满足第二范式，除开主键列的其他列之间不能有传递依赖关系。

create tablemyorder(

order_idint primary key,

product_idint,

customer_idint,

);create tableproduct(

idint primary key,

namevarchar(20)

);create tablecustomer(

idint primary key,

namevarchar(20),

customer_phonevarchar(15)

);