回顾

校对集：用来比较字符大小

三种校对方式：二进制（_bin），区分大小写（_cs），不区分大小写（_ci）

存储引擎：用不同的方式来存储和处理数据

Myisam：查询和插入的效率高，三个独立文件（结构，索引，数据）

Innodb：安全性高，更新和删除效率高（结构），数据保存在ibdata文件中

乱码问题：三码合一，通常是要保证服务器的文件编码与浏览器的显示编码和数据库所设置的客户端编码保证一致

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

数值型：整型，小数型

整型：tinyint（1），smallint（2），mediumint（3），int（4），bigint（8）

小数型：浮点型和定点型

浮点型：float（4）和double（8），精度不一样

定点型：decimal(M,D)，M表示总长度，D表示小数部分长度，decimal(65,30)

字符型：char，varchar，text，blob，enum，set

char：char(L)，定长的字符长度，系统会分配L个字符的长度，不管实际长度是多少

varchar：varchar(L)，变长的字符长度，系统能够最多存放L个字符的长度，系统实际分配的是实际字符所占用的长度+1或者2个字节（保存变长字符才长度）

text&blob：都是存储较大的文本，blob存储二进制文本

enum：枚举，数据库对应存储的实际内容是数字（对应枚举元素的序列号），类似单选框，enum使用2个字节保存元素，能够保存65535个元素

set：集合，数据库存储的还是数组，集合的元素对应的不是一个数值，而是二进制的一个位，十进制与二进制之间的转换，set使用8个字节保存数据，能保存64个集合元素

时间日期型：datetime，date，time，timestamp，year

datetime：日期时间，格式：yyyy-mm-dd HH:ii:ss，占用8个字节存储，存储的范围0000 – 9999

date：日期时间的日期部分

time：表示一个时间段，格式：HHH:ii::ss，插入数据时还可以这样：d HH:ii:ss

timestamp：标准整型保存数据，时间戳，表现形式与datetime，与datetime的区别在于表示的范围不一样

year：年，范围是1900 – 2155，使用1个字节操作

mysql每条数据（记录）的最大长度是65535个字节，这个字段类型基本受该限制影响，text除外（占用10个字节用来保存外部字符串保存的地址）。

能力有多大不代表能表现出来，受平台影响。

字段属性：null/not null，default，primary key，unique key，auto_increment，comment

关系

如何将自然界中的关系，在mysql数据库中进行设计和体现。

学生表

学号	姓名	年龄
PHP1001	余秋雨	68
PHP1002	韩寒	38

上述信息就是数据库在反应自然界实体内部属性之间的联系的设计。

教室表

教室	容量
D306	100
B2302M	60

教室与学生之间的关系是，一个教室可以容纳多个学生，多个学生在一个教室上课。这种关系就称之为一对多或者多对一的关系

如何将多对一的关系，在数据库设计中体现呢？

多对多关系

一对一的关系

范式

范式：Normal Formal，指的是对某个事物的规范

现在，范式有六范式：第一范式到第六范式。从低层到高层是越来越严格。如果要满足后续范式，必须要满足前面的范式。关系型数据库的设计只要满足三范式即可。

第一范式：1NF

数据表设计，字段必须具有原子性

教师与上课关系表

讲师	性别	班级	教室	代课时间	代课时间（开始，结束）
张三	Male	php0226	D302	30天	2018-02-27,2018-05-05
李四	Male	php0320	B206	30天	2018-03-21,2018-05-30
王五	Male	php0320	B206	15天	2018-06-01,2018-06-20

如果需求单独需要上课的开始时间和结束时间。就需要将拿到的数据进行拆分，然后再显示。

这种方式就违背了数据库设计的第一范式：原子性，字段不可再分

如何满足第一范式？将需要使用的字段变成最小单位，不可再分

讲师	性别	班级	教室	代课时间	开始	结束
张三	Male	php0226	D302	30天	2018-02-27	2018-05-05
李四	Male	php0320	B206	30天	2018-03-21	2018-05-30
王五	Male	php0320	B206	15天	2018-06-01	2018-06-20

只要获取对应的开始时间和结束时间字段。

为什么要满足第一范式？

为了保证数据库的高效性，在需要查询的时候，能够直接从数据库获得响应的数据，而不需要对数据进行加工处理。

第二范式：2NF

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

一张表中不能存在部分依赖。部分依赖指的一张表有一个或者多个字段，依赖部分主键（主键是复合主键）

代课关系表

讲师和班级共同确定一条唯一的记录，称之为复合主键

讲师P	性别	班级P	教室	代课时间	开始	结束
张三	Male	php0226	D302	30天	2018-02-27	2018-05-05
李四	Male	php0320	B206	30天	2018-03-21	2018-05-30
王五	Male	php0320	B206	15天	2018-06-01	2018-06-20

教室与讲师和班级组成的主键没有关系，但是受班级字段影响。这个时候，就存在教室依赖班级存在。其中，班级字段与讲师字段是一个复合主键。

一个字段依赖复合主键中的部分字段（复合主键的某一个字段），违背了第二范式。

通常的解决方式：使用单一字段主键（不适用复合主键），或者给一张表添加一个逻辑主键，与业务无关

ID P	讲师	性别	班级	教室	代课时间	开始	结束
1	张三	Male	php0226	D302	30天	2018-02-27	2018-05-05
2	李四	Male	php0320	B206	30天	2018-03-21	2018-05-30
3	王五	Male	php0320	B206	15天	2018-06-01	2018-06-20

第三范式：3NF

前提条件满足第二范式

在满足二范式的前提下，不存在传递依赖。

传递依赖：表中的所有字段应该都依赖于主键，而不是依赖于其他字段。如果是通过依赖其他字段，而其他字段依赖主键，这时候就形成了传递依赖。

代课关系表：一个老师对应一个班级所上课程的天数以及起始结束时间。

IDP	讲师	性别	班级	教室	代课时间	开始	结束
1	张三	Male	php0226	D302	30天	2018-02-27	2018-05-05
2	李四	Male	php0320	B206	30天	2018-03-21	2018-05-30
3	王五	Male	php0320	B206	15天	2018-06-01	2018-06-20

代课关系表，要求必须要有讲师和班级字段，这两个字段都依赖于主键。但是性别和教室字段与主键没有形成依赖关系，性别依赖了讲师，而教室依赖了班级。从而形成了传递依赖。

如何取消传递依赖？

将存在的这种依赖新建一张表，然后在原来需要当前实体的地方，使用新实体对应的主键。

代课关系表

讲师表 班级表

总结：数据库的设计如何满足三范式

1. 根据具体业务，将字段分解到最小单位，从而不能再分割，满足了1NF
2. 不再使用复合主键，使用逻辑主键。满足2NF
3. 每个实体单独建立对应的数据表，凡是需要使用其他表的信息的时候，都使用对应的主键即可，满足了3NF

范式是一个规范，但不是一个强制的标准。没有遵循规范也能保证数据库的正常运行。满足规范能够使得数据变的精简，没有数据冗余的出现。

逆规范化

有时候需要特意的设计，不去满足范式。

在某张表中，需要其他表的某个字段（通常不是主键），而该表又被非常频繁使用。为了保证执行的效率，从而设计了必要数据冗余。

考虑效率和冗余的对抗==》执行效率和磁盘空间的对抗。

外键

外键：Foreign key，一个表中有一个字段，指向另外一张表的主键。那么该字段就称之为外键。

什么时候使用外键？

外键约束：使用了外键之后，那么对外键指向的表就会产生很多约束，删改表与表之间是有联系的，如何证明这种联系呢？使用外键。

外键的使用

包括在建立表的时候指定外键，以及在创建表之后修改表结构增加外键

语法1：在创建表时增减外键 foreign key (字段) references 表名 (字段)

查看外键

show create table 表名

外键的作用

约束了使用外键的表的数据操作：不能插入一个外键所指向的主键不存在的记录

如果要在子表插入数据，必须保证外键对应父表的主键里已经存储该对应的数据

在外键使用上有两个概念

父表：被外键所指向（引用）的表就是父表

子表：定义了外键的表就是子表

删除外键

将外键移除

因为一个表中可能出现多个外键，因此在删除外键的时候必须指定对应的外键的名字

语法：alter table 表名 drop foreign key 外键名

注意：外键要求对应的字段必须是一个索引，在创建外键的时候会自动给对应的外键字段创建索引；但是删除外键时只会删除对应的外键，而不会将对应字段的索引删除。

语法2：修改表结构增加外键

语法：alter table 表名 add foreign key (字段) references 表名 (字段)

修改外键名字

要先删除外键，然后重新添加

外键名字语法：constraint 外键名 foreign key (字段) references 表名 (字段)、

注意：在添加外键名字的时候，不要对外键名添加单引号，如果非要区别，使用反引号。

外键约束

对父表和子表相应数据的操作的时候，必须要满足约束的条件之后，才能操作，否则不能操作。

外键约束规则：

1. restrict模式，严格模式（默认），不允许父表对有约束的数据进行操作（删除和修改，对应的主键）

2. set null模式，置空模式，当父表的数据被删除的时候，子表对应的数据被设置成null
3. cascade模式，级联模式，当父表的数据被删除的时候，子表对应的数据也随之被删除。

通常，对应外键的约束规则：在更新的时候，级联操作，在删除的时候，置空（父表影响子表）

约束1：当子表插入数据的时候，必须保证父表已经存在对应的主键ID。

约束2：子表对父表的约束，父表的数据操作，如果在有子表约束的情况下（被引用），那么不能随便操作

在添加外键时需要指定约束模式

add foreign key (字段) references 表名 (字段) on update 模式[cascade] on delete 模式[set null]

1. 更新父表数据

2. 删除父表数据

外键约束的存在是为保证数据的一致性。

从数据库设计角度出发，应该尽可能的使用外键来保证数据的一致性。如果从外部应用的角度出发（PHP），很少使用外键约束

PHP：在需要使用外键的表中，增加一个字段，该字段用来保存对应的表的主键，但是不建立外键关系。

注意：

1. 建立外键的前提条件：外键对应的字段类型要与指向表的主键的字段类型一致
2. 约束时父表删除子表置空：子表的字段允许为空

数据操作高级操作

数据操作包括CRUD，增删改查

插入数据

基本语法：insert into 表名 [(字段列表)] values (值列表),…

蠕虫复制：将已有的数据直接插入到表中

语法：insert into 表名 [(字段列表)] select [字段列表] from 表名（可以是自己，也可以是其他表）

蠕虫复制一般用于测试使用，测试服务器的压力和执行时间。

当主键冲突时处理，正常情况下主键冲突则不能进行数据插入，从而报错。

当主键插入冲突时，需要执行更新操作

语法：insert into 表名 values (值列表) on duplicate key update 字段=值,…

mysql还提供了一种冲突解决方式

语法：replace into 表名 value(值列表); -- 当原来的主键已经存在时则覆盖

更新数据

标准语法：update 表名 set 字段=值,… where条件

搭配order by和limit进行更新；order by对字段进行排序，limit 限制受影响的行数

语法：update 表名 set 字段=值 where条件 order by limit

删除数据

标准语法：delete from 表名 where条件

搭配order by和limit进行更新

语法：delete from 表名 [where条件] [order by 字段] [limit 数量]

采用delete并不会改变当前自增长值

有什么办法能够清空数据同时，还重置自增长值呢？

drop 表名，create表名

语法：truncate 表名，第一步：删除表，第二步：创建表

查询数据

标准语法：select *[字段列表] from 表名 [where条件]

详细语法：select [select选项] [字段列表|表达式] [from子句：表名|表名列表] [where子句] [group by子句] [having 子句] [order by 子句] [limit子句]

特别注意：五子句的顺序不能乱，可以只有部分或者全部都没有。

select 选项：all和distinct

all：表示获得全部数据，不管数据是否重复，默认的是all

distinct：去重，指的是对整个记录进行去重，如果两条记录完全一致则被认为是重复。

表达式：凡是能返回结果的语句都是表达式

select 1 + 10；

字段别名

字段 [as] 别名

不适用as

from子句

指定数据源

语法：from 表名

还可以从多张表里获取数据

语法：from 表名1,表名2,…

1. 将第一张表的第一条数据与第二张表的每一条数据进行拼接，然后保存该数据
2. 结果数据：字段数= 两张表字段数之和，记录数 = 两张表记录数相乘

以上获得的内容在数学上被称之为：笛卡尔积

笛卡尔积在数据库中没有意义，所以应当尽量避免。

虚拟表：dual，虚拟的意思

mysql为了保证语句结构的完整性而存在的，并没有真实的表和真实的数据。

from子句可以对数据源使用别名

from 表名 [as] 别名

where子句

用来筛选满足条件的数据

where本身不能筛选，而是通过一些筛选的表达式来实现。

where表达式：逻辑表达式（&&and,||or,!not），比较表达式（<,<= > >= = != <> in not in between-and is like any some all）

where 条件1 and 条件2：两个条件都必须满足

=：mysql里=表示比较，当然也可以是赋值

in：在一个集合内

语法：字段 in (条件集合)

not in刚好取相反的数据

between and：在某个范围之内，是闭区间

语法：between 条件1 and 条件2，包含条件1和条件，条件1必须 小于 条件2

is：因为null和任何数据的计算都会得到null结果，没有办法进行比较，只能通过is null或者is not null来对null属性数据进行判断。

判断数据为空

any,some和all对应的条件应该是子查询的结果。不能直接给出条件。

where子句原理：

where子句之后，所有的操作都是针对内存里的结果进行操作。

group子句

group就是对结果进行分组

语法：group by 字段名

分组是为了统计

将所有的数据按照分组字段先进行排列，所以非分组的信息基本没有用。

一系列统计函数：基本都是针对某个字段，count除外

count：统计记录数，不统计为NULL的记录

max：获取最大值

min：获取最小值

avg：获得平均值

sum：求和

group by原理

group_concat()：将分组内的某个字段进行拼接显示

count：统计不为空的所有字段

多字段分组

语法：group by 字段1，字段2，。。。。。

group排序功能，group会对分组的字段进行排序。默认是升序排序

语法：group by 字段 [asc|desc]

回溯统计

将数据进行多字段分组之后，每次在向上一次统计之前，系统会对前一个字段的结果进行一次统计。

语法：with rollup