mysql 定义外键并增删改查_20150102--Mysql外键+增删改查-01

回顾

校对集：校对集必须事先设置好，修改无效

乱码问题：三码合一，通过php设置数据库和浏览器的编码

存储引擎：数据库数据的存储方式

数据类型：数值型，字符串型和时间日期型

数值型：整型，小数型(浮点和定点)

字符串型：定长，变长，文本，枚举和集合

时间日期型：时间日期，日期，时间，时间戳(以时间戳时间为起始单位的时间日期)，年

列属性：null，default，primary key，auto_increment

列属性

注释

注释不是指普通的注释，让系统(服务器)自动的去忽略无效代码。真正的注释将一段用来描述字段文件保存到对应的数据表里，用于提示用户当前结构的情况。

SQL注释：让系统忽略

-- ：两个中划线和一个空格，行注释，以后所有内容都可以忽略

#：也是单行注释

/**/：块注释

注释关键字：comment

语法：在数据类型之后comment ‘注释内容’;

注意：注释不分语言类型，都是为了方便别人和自己去维护代码。

唯一键

唯一键是一个索引，对应字段里面的数据应该具有唯一性。

存在：一个表主键也是用来保证数据的唯一性(对应的字段)，但是一张表只能拥有一个主键，但是其他字段也有可能需要保证数据唯一性，唯一键就是为了解决该问题。

唯一键特点

1. 唯一键与主键类似，都是保证当前的数据具有唯一性

2. 唯一键不统计数据为空的字段(允许有空字段而且是多个)

3. 唯一键在表中可以使用多个

添加唯一键

唯一键关键字：unique key

1. 在字段之后使用关键字unique

2. 可以在所有的字段只用，使用unique key(字段列表);

3. 修改表结构

a) 修改字段(modify)，在字段之后增加属性unique关键字

b) 增加唯一键：add unique key(字段列表)

使用唯一键

如果给唯一键字段添加不唯一的数据，那么就会报错。

唯一键不能修改，只能删除

删除唯一键

alter table表名 drop index索引名字;删除唯一键

alter table表名 drop primary key;删除主键

alter table表名 drop foreign key外键名;删除外键

查看索引名字

删除唯一键

伪主键

关系

自然界中的关系通过数据对应的模式进行分析之后，得出结果：可以大致分为三种，一对一，一堆多，多对多

一对多

一对多与多对一是一个概念，指的是一个实体的某个数据与另外一个实体的多个数据有关联关系。

班级表(一表)名称 教室 总人数 学科

PHP141115 A814 53 PHP

PHP140925 A806 55 PHP

学生表(多表)学号 姓名 性别 年龄

Itcast0001 何权森 男 22

Itcast0002 朱红林 男 保密

Itcast0003 詹曼雪 女 保密

Itcast0004 刘星 男 28

将实体间的多对一的关系进行维护：在“多”表中，增加一个字段能够指向“一”表中，唯一字段。学号 姓名 性别 年龄 班级名称

Itcast0001 何权森 男 22 PHP141115

Itcast0002 朱红林 男 保密 PHP141115

Itcast0003 詹曼雪 女 保密 PHP140925

Itcast0004 刘星 男 28 PHP140925

多对多

一个实体的数据对应另外一个实体的多个数据，另外实体的数据也同样对应当前实体的多个数据。

教师表(多)姓名 性别 年龄

犬夜叉 男 300

戈薇 女 18

班级表(多)名称 教室 总人数 学科

PHP141115 A814 53 PHP

PHP140925 A806 55 PHP

犬夜叉：教过PHP141115和PHP140925

PHP141115：被犬夜叉和戈薇教过

解决方案：创建一个中间表，专门用来维护多表之间的对应关系，通常是能够唯一标识出数据的字段(主键)

一对一

一个实体的数据对应另外一个实体的数据，一条对一条

学生表(内部关系)姓名 性别 年龄 身高 政治面貌 家庭住址 紧急联系人 紧急联系人方式 婚姻情况

张无忌 男 20 175 党员 冰火岛 金毛狮王 吼 未婚

周芷若 女 18 168 gcd 峨眉山 灭绝师太 飞鸽传书 未婚

赵敏 女 18 168 国名党 蒙古 王爷 号角 未婚

宋青书 男 22 178 党员 武当山 周芷若 吹 未婚

为了提升操作的效率会主动的将一张表进行拆分，拆分成两张表：一张经常用于操作，另外一张很少操作。

常用信息姓名 性别 年龄 身高

张无忌 男 20 175

周芷若 女 18 168

赵敏 女 18 168

宋青书 男 22 178

不常用信息姓名 政治面貌 家庭住址 紧急联系人 紧急联系人方式 婚姻情况

张无忌 党员 冰火岛 金毛狮王 吼 未婚

周芷若 gcd 峨眉山 灭绝师太 飞鸽传书 未婚

赵敏 国名党 蒙古 王爷 号角 未婚

宋青书 党员 武当山 周芷若 吹 未婚

范式

规范的方式，指的是一套维护数据的方式

范式：Normal Format，简称NF，是数学中的一个概念。是为了解决一个数据之间的联系，同时还要保证数据最简化的内容。

范式一共分为六层：第一范式(1NF)，第二范式…第六范式(6NF)

范式的严格程度是逐层递增，要满足后续的范式，必须先满足前面所有的范式。

数据库规范中引入的范式的概念，只引入到三层范式(3NF)。

1NF

第一范式：指数据库中表的设计(字段)，字段在使用的过程中，不需要通过额外的方式对数据进行处理(拆分)，要保证数据库中的字段必须拿出来就可以直接使用。1NF必须满足原子性。

讲师代课表讲师 性别 班级 教室 代课时间 代课时间(开始，结束)

李东超 Male php0226 D302 30天 2014-02-27,2014-05-05

李东超 Male php0320 B206 30天 2014-03-21,2014-05-30

马浩洋 Male php0320 B206 15天 2014-06-01,2014-06-20

如果在使用的过程中，需要额外的使用代课的起始时间，需要分别得到起始时间和结束时间。代课时间取出来需要对数据进行拆分，不符号1NF，不具有不可拆分性(原子性)

解决方案：将代课时间拆分成开始时间和结束时间讲师 性别 班级 教室 代课时间 开始 结束

李东超 Male php0226 D302 30天 2014-02-27 2014-05-05

李东超 Male php0320 B206 30天 2014-03-21 2014-05-30

马浩洋 Male php0320 B206 15天 2014-06-01 2014-06-20

满足第一范式很简单：将数据拆分成最小颗粒存储到数据库即可。

2NF

若要满足第二范式，必须先满足第一范式。

2NF，如果一个数据库中存在一个字段依赖主键的一部分，而不是整个主键。这个时候就存在部分依赖，第二范式不满足，需要解除部分依赖。

讲师带课表讲师P 性别 班级P 教室 代课时间 开始 结束

李东超 Male php0226 D302 30天 2014-02-27 2014-05-05

李东超 Male php0320 B206 30天 2014-03-21 2014-05-30

马浩洋 Male php0320 B206 15天 2014-06-01 2014-06-20

将主键由讲师和班级共同组建(复合主键),性别只依赖于讲师，教室依赖于班级

班级和性别依赖主键的一部分，存在部分依赖。

解决方案：取消复合主键，额外的增加一个逻辑主键IDP 讲师 性别 班级 教室 代课时间 开始 结束

1 李东超 Male php0226 D302 30天 2014-02-27 2014-05-05

2 李东超 Male php0320 B206 30天 2014-03-21 2014-05-30

3 马浩洋 Male php0320 B206 15天 2014-06-01 2014-06-20

第二范式：不让表中存在复合主键

3NF

要满足第三范式，必须满足第二范式。

指的是如果一个字段不依赖主键，而是依赖其他的字段的时候，那么就会形成传递依赖。一张表中的所有字段都应该依赖主键，而不是依赖主键以外的其他字段。如果存在依赖主键以外的字段，那就形成了传递依赖。第三范式要求取消传递依赖。

讲师带课表IDP 讲师 性别 班级 教室 代课时间 开始 结束

1 李东超 Male php0226 D302 30天 2014-02-27 2014-05-05

2 李东超 Male php0320 B206 30天 2014-03-21 2014-05-30

3 马浩洋 Male php0320 B206 15天 2014-06-01 2014-06-20

性别不依赖ID，只依赖讲师，而讲师依赖主键，形成了传递依赖。

解决方案：将形成传递依赖的字段，单独取出来，形成一个新的实体，然后在需要使用新实体的地方使用对应实体表中主键。

因为讲师代课表中不存在字段不依赖主键了，所以就不存在传递依赖。

数据库并没有强制要求数据表设计必须满足三范式。

逆规范化

明知设计不符合三范式，但是有时候会为了提高效率，会刻意的增加数据冗余。IDP 讲师ID 班级ID 代课时间 开始 结束

1 李东超 10 30天 2014-02-27 2014-05-05

2 李东超 12 30天 2014-03-21 2014-05-30

3 马浩洋 12 15天 2014-06-01 2014-06-20

因为数据查询操作如果跨表的话会影响效率，有时候为了效率会增加必要的数据冗余(牺牲磁盘空间)

逆规范化其实本质是效率与空间利用率的博弈。