--数据库的三大范式 ,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)
);