回顾

字段类型(列类型)：数值型，时间日期型和字符串类型

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

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

字符串类型：定长，变长，文本字符串(text和blob)，枚举和集合

mysql记录长度：65535字节，varchar达不到理论长度，NULL占用一个字节，text
文本不占用记录长度(但是本身占据十个字节)

字段属性：空属性，列描述，默认值

字段属性

主键，唯一键，自增长。

主键

主键：primary key，主要的键。一张表只能有一个字段可以使用对应的键。用来唯一的约束
该字段里面的数据不能重复。这种称之为主键。

一张表只能有最多一个主键。

增加主键

SQL操作中有很多种方式可以给表增加主键：大体分为三种。

方案1：在创建表的时候，直接在字段之后，跟primary key关键字(主键本身不允许为空)

优点：非常直接，缺点：只能使用一个字段作为主键。

方案2：在创建表的时候，在所有字段之后，使用primary key(主键字段列表)来创建主键(如果有多个字段作为主键，可以是复合主键)

方案3：当表已经创建好之后，额外追加主键：可以通过修改表字段属性，也可以直接追加。

alter table 表名 add primary key (字段列表);

前提：表中字段对应的数据本身是独立的(不重复，因为它没办法去修改数据)

主键约束

主键对应的字段中的数据不允许重复：一旦重复，数据操作失败(增和改)

更新主键&删除主键

没有办法更新主键，主键必须先删除，才能增加

alter table 表名 drop primary key;

主键分类

在实际创建表的过程中，很少使用真实业务数据作为主键字段(业务主键：如学号，课程号)；
大部分的时候是使用逻辑性的字段(字段没有业务含义，值是什么都没关系)，将这种字段的
主键称之为逻辑主键

create table my_student(
    id int primary key auto_increment comment '逻辑主键：自增长', -- 逻辑主键
    number char(10) not null comment '学号',
    name varchar(10) not null
)

自动增长

新增自增长

自增长：当对应的字段不给值或者说给默认值或者给NULL的时候，会自动的被系统触发，系统
会从当前字段中已有的最大值再进行+1操作，得到一个新的不同的值。

自增长通常是跟主键搭配。

自增长特点：auto_increment

1. 任何一个字段要做自增长必须前提是本身是一个索引(key一栏有值，主键一定是索引(主键索引)，索引却不一定是主键)

2. 自增长字段必须是数字(整型)

3. 一张表最多只能有一个自增长

自增长的使用

当自增长被给定的值为NULL或者默认值的时候会触发自动增长

自增长如果对应的字段输入了值，那么自增长失效：但是下一次还是能够正确的自增长(从最大值+1)

如何确定下一次是什么自增长呢？可以通过查看表创建语句看到。

修改自增长

自增长如果涉及到字段改变：必须先删除自增长，后增加(因为一张表只能有一个自增长)

修改当前自增长已经存在的值：修改只能比当前已有的自增长的最大值当前，不能小(小不生效)
alter table 表名 auto_increment = 值;

向上修改可以

思考：为什么自增长是从1开始？为什么每次都是自增1呢？

所有系统的表现(如字符集，校对集)都是由系统内部的变量进行控制的。

查看自增长对应的变量：show variables like 'auto_increment%';

可以修改变量实现不同的效果：修改是对整个数据修改，而不是单张表(会话级)

set auto_increment = 5; -- 一次自增5

测试效果：自动使用自增长

由于系统误差引起。

删除自增长

自增长是字段的一个属性：可以通过modify来进行修改(保证字段没有auto_increment即可)

alter table 表名 modify 字段 类型;

唯一键

一张表往往有很多字段需要具有唯一性，数据不能重复：但是一张表只能有一个主键，
唯一键(unique key)就可以解决表中有多个字段需要唯一性约束的问题。

唯一键的本质与主键差不多：唯一键默认允许自动为空，而且可以多个为空(空字段不参与唯一性比较)

增加唯一键

基本与主键差不多：三种方案

方案1：在创建表的时候，直接在字段之后，跟unique/unique key

方案2：在创建表的时候，在所有字段之后，使用unique -- 复合唯一键

方案3：当表已经创建好之后增加唯一键

唯一键约束

唯一键与主键本质相同：唯一区别就是唯一键默认允许为空，而且是多个为空。

如果唯一键也不允许为空：与主键的约束作用是一直的。

更新唯一键&删除唯一键

更新唯一键：先删除后新增(唯一键可以有多个：可以不删除)

删除唯一键

alter table 表名 drop unqiue key; -- 错误：唯一键有多个

alter table 表名 drop index 索引名字; -- 唯一键默认使用字段名作为索引名字

索引

几乎所有的索引都是建立在字段之上。

索引：系统根据某种算法，将已有的数据(未来可能新增的数据)，单独建立一个文件：文件能够实现
快速的匹配数据，并且能够快速的找到对应表中的记录。

索引的意义：

1. 提升查询数据的效率
2. 约束数据的有效性(唯一性等)

增加索引的前提条件：索引本身会产生索引文件(有时候有可能比数据文件还大)，会非常耗费磁盘空间。

如果某个字段需要作为查询的条件经常使用，那么可以使用索引(一定会想办法增加)

如果某个字段需要进行有效性的约束，也可能使用索引(主键，唯一键)

mysql中提供了多种索引：

1. 主键索引：primary key
2. 唯一键索引：unique key
3. 全文索引：fulltext index
4. 普通索引：index

全文索引：针对文章内部的关键字进行索引

全文索引最大的问题：在于如何确定关键字

英文很容易：英文单词与单词之间有空格

中文很难：没有空格，而且中文可以各种随意组合(分词：sphinx)

关系

将实体与实体的关系，反映到最终数据库表的设计上来：将关系分成3种：一对一、一对多(多对一)和多对多。

所有的关系都是指表与表之间的关系

一对一

一对一：一张表的一条记录一定只能与另外一张表的一条记录进行对应；反之亦然。

学生表：姓名，性别，年龄，身高，体重，婚姻，籍贯，住址，联系人

表设计成以上这种形式：符合要求，其中姓名，性别，年龄，身高，体重都属于常用数据；但是
婚姻，籍贯，住址，联系人属于不常用数据。如果每次都是查询所有数据，不常用的数据就会影响效率，实际又不用

解决方案：将常用的和不常用的信息分离存储，分成两张表

常用信息表

不常用信息表：保证不常用信息与常用信息一定能够对应上：找一个具有唯一性(确定记录)
的字段来共同连接两张表

一个常用表中的一条记录：永远只能在一张不常用表中匹配一条记录；反过来，一个不常用表中的一条记录
在常用表中也只能匹配一条记录：一对一的关系

一对多

一对多：一张表中有一条记录可以对应另一张表中的多条记录；但是反过来，另一张表的一条记录只能对应第一张表
的一条记录，这种关系就是 一对多或多对一

母亲与孩子的关系：两个实体

母亲表：

孩子表：

以上关系：一个母亲可以在孩子表中找到多条记录(也有可能是一条)；但是一个孩子只能找到一个母亲：是一种典型的
一对多的关系

以上设计解决了实体的设计表问题，但是没有解决关系问题：孩子找不到母亲，目前也找不到孩子。

解决方案：在某一张表中增加一个字段，能够找到另外一张表中的记录：应该在孩子表中增加一个字段
指向母亲表：因为孩子表的记录只能匹配到一条母亲表的记录。

母亲表：

孩子表：

多对多

多对多：一张表中(A)的一条记录能够对应另外一张表B中的多条记录；同时B表中的一条记录也能对应A表中的多条记录，
多对多的关系

老师教学：老师和学生

老师表：

学生表：

以上设计方案：实现了实体的设计，但是没有维护实体的关系

一个老师教过多个学生：一个学生也被多个老师教过。

解决方案：在学生表中增加老师字段：不管在哪张表中增加字段，都会出现一个问题：该字段要保存多个数据，而且
是与其他表有关系的字段，不符合表设计规范：因此要增加一张新表：专门维护两张表之间的关系。

老师表：

学生表：

中间关系表：老师与学生的关系

增加中间表之后：中间表与老师形成了一对多的关系：而且中间表是多表，维护了能够唯一找到
一表的关系；同样的，学生表与中间表也是一个一对多的关系，一对多的关系可以匹配到关联表
之间的数据。

学生找老师：找出学生id -> 中间表寻找匹配多条记录 -> 老师表匹配(一条记录)

老师找学生：找出老师id -> 中间表寻找匹配多条记录 -> 学生表匹配(一条记录)

范式

范式：Normal Format，是一种离散数学中的知识，是为了解决一种数据的存储存储与优化的问题；
保存的数据存储之后，凡是能够通过关系寻找出来的数据，坚决不再重复存储：终极目标是为了
减少数据的冗余。

范式：是一种分层结构的规范，分为六层：每一层逗比都比上一层更加严格：若要满足下一层范式，前提
是满足上一层范式。

六层范式：1NF，2NF，3NF....6NF，1NF是最底层，要求最低，6NF是最高层，最严格。

mysql属于关系型数据库，有空间浪费，因此它要致力于节省存储空间：与范式所要解决的问题不谋而合：在
设计数据库时，会利用范式来指导设计。

但是数据库不单是要解决空间问题，还要保证效率问题，而范式只为解决空间问题，所以数据库的设计又不可能
完全按照范式的要求实现：一般情况下，只有前三种凡是满足需要。

范式在数据库的设计当中是有指导意义：但不是强制规范

1NF

第一范式：在设计表存储数据的时候，如果表中设计的字段所存储的数据，在取出来使用之前还需要额外的处理(拆分),那么
说明表的设计不满足第一范式：第一范式要求字段的数据具有原子性(不可再分)。

讲师代课表

上表设计不存在问题：但是如果需求是将数据查出来之后，要求显示一个老师从什么时候开始
上课，到什么时候结课，那么将需要对代课时间进行拆分：不符合1NF(数据不具有原子性，可以再拆分)

解决方案：将代课时间拆分成两个字段。

2NF

第二范式：在数据表设计的过程中，如果有复合主键(多字段主键)，且表中有字段并不是由整个主键来确定，
而是依赖主键中的某个字段(主键的部分)：存在字段依赖主键的部分的问题，称之为部分依赖：第二范式就是
要解决表设计不允许出现部分依赖。

讲师代课表

以上表中：因为讲师没有办法作为独立主键，需要结合班级才能作为主键(复合主键：一个老师在一个班级永远只带一个
阶段的课)：代课时间，开始和结束字段都与当前的代课主键(讲师和班级)相关：但是性别并不依赖班级，教室不依赖
讲师：性别只依赖讲师，教室只依赖班级：出现了性别和教室依赖主键中的一部分：部分依赖，不符合第二范式。

解决方案1：可以将性别与讲师单独成表，班级与教室也单独成表。

解决方案2：取消复合主键，使用逻辑主键。

ID = 讲师 + 班级(业务逻辑约束：复合唯一键)

3NF

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

第三范式：理论上讲，应该一张表中的所有字段都应该直接依赖主键(逻辑主键：代表的是业务主键)，如果表设计中存在
一个字段，并不是直接依赖主键，而是通过某个非主键字段依赖，最终实现依赖主键：把这种不是直接依赖主键，而是依
赖非主键字段的依赖关系称之为传递依赖。第三范式就是要解决传递依赖的问题。

讲师代课表

以上设计方案：性别依赖讲师存在，讲师依赖主键，教室依赖班级，班级依赖主键：性别和教室都存在传递依赖。

解决方案：将存在传递依赖的字段、以及依赖的字段本身单独取出，形成一个单独的表，然后在需要对应的信息
的时候，使用对应的实体表的主键加进来。

讲师代课表

讲师表

班级表

讲师表中其实ID=讲师，班级表中其实ID=班级

逆规范化

有时候，在设计表的时候，如果一张表中有几个字段是需要从另外的表中去获取信息，理论上讲，的确可以
获取到想要的数据，但是就是效率低一点。会刻意的在某些表中，不去保存另外表的主键(逻辑主键)，而是
直接保存想要的信息：这样一来，在查询数据的时候，一张表可以直接提供数据，而不需要多表查询(效率低),
但是会导致数据冗余增加。

如讲课代课信息表

逆规范化：磁盘利用率与效率的对抗。

数据高级操作

数据操作：增删改查

新增数据

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

在数据插入的时候，假设主键对应的值已经存在：插入一定会失败

主键冲突

当主键存在冲突的时候(Duplicate key)，可以选择性的进行处理：更新和替换

主键冲突：更新操作

insert into 表名 [(字段列表：包含主键)] values (值列表) on duplicate key update 字段 = 新值;

注意上图中有两行受到影响：因此是更新而不是插入操作。

主键冲突：替换

replace into 表名 [(字段列表：包含主键)] values (值列表);

注意还是会有两行受到影响，先删除再插入

蠕虫复制

蠕虫复制：从已有的数据中去获取数据，然后将数据又进行新增操作：数据成倍的增加。

表创建高级操作：从已有表创建新表(复制表结构)

create table 表名 like 数据库.表名;

蠕虫复制：先插出数据，然后将查出的数据新增一遍

insert into 表名 [(字段列表)] select 字段列表/* from 数据表名;

蠕虫复制的意义

1. 从已有表中拷贝数据到新表中
2. 可以迅速的让表中的数据膨胀到一定的数量级：测试表的压力以及效率

更新数据

基本语法：update 表名 set 字段 = 值 [where条件];

高级更新语法：update 表名 set 字段 = 值 [where条件] [limit更新数量];

删除数据

与更新类似：可以通过limit来限制数量

delete from 表名 [where条件] [limit数量];

删除：如果表中存在主键自增长，那么当删除之后，自增长不会还原

如何还原自增长？

思路：数据的删除是不会改变表结构的，只能删除表重建表

truncate 表名; -- 先删除改变，后新增改变

查询数据

基本语法

select 字段列表/* from 表名 [where条件];

完整语法

select [select选项] 字段列表[字段别名]/* from 数据源 [where条件子句][group by子句][having子句][order by子句][limit子句];

select选项

select选项：select对查出来的结果的处理方式

all：默认的，保留所有的结果
distinct：去重，查出来的结果，将重复(所有字段都相同)给去除

字段别名

字段别名：当数据进行查询出来的时候，有时候名字并不一定就满足需求(多表查询的时候，会有同名字段)。需要对字段进行重命名：别名

语法：

字段名 [as] 别名;

数据源

数据源：数据的来源，关系型数据库的来源都是数据表：本质上只要保证数据类似二维表，最终都可以作为数据源

数据源分为多种：单表数据源，多表数据源，查询语句

单表数据源：select * from 表名;

多表数据源：select * from 表名1,表名2....

从一张表中取出一条记录，去另外一张表中匹配所有记录，而且全部保留：(记录数和字段数)，
将这种结果称为：笛卡尔积(交叉连接)：笛卡尔积没有什么卵用，应该应该尽量避免。

子查询：数据的来源是一条查询语句(查询语句的结果是二维表)

select * from (select语句) as 表名;

where子句

where子句：用来判断数据，筛选数据

where子句返回结果：0或者1，0代表false，1代表true

判断条件

比较运算符：>,<,>=,<=,!=,<>,=,like,between and,in/not in

逻辑运算符：&&(and)，||(or)，!(not)

where原理：where是唯一一个直接从磁盘获取数据的时候就开始判断的条件：从磁盘取出一条记录，开始进行where判断：判断的结果
如果成立保存到内存；如果失败直接放弃。

条件查询1：要求找出学生id为1或者为3的学生

条件查询2：查出区间落在180,190身高之间的学生

between本身是闭区间；between左边的值必须小于或者等于右边的值

group by子句

group by：分组的意思，根据某个字段进行分组(相同的放一组，不同的分到不同组)

基本语法：group by 字段名;

分组的意义：是为了统计数据(按组统计：按分组的字段进行数据统计)

SQL提供了一系列统计函数

count()：统计分组后的记录数：每一组有多少记录

max()：统计每组中最大的值(根据字段)

min()：统计最小值

avg()：统计平均值

sum()：统计和

count函数：里面可以使用两种参数：*代表统计记录；字段名代表统计对应的字段(NULL不统计)

分组会自动排序：根据分组字段--默认升序

group by 字段 [asc|desc]; -- 对分组的结果合并之后的整个结果进行排序

多字段分组：先根据一个字段进行分组，然后对分组后的结果

有一个函数：可以对分组的结果中的某个字段进行字符串连接(保留改组所有的某个字段)：group_concat(字段);

回溯统计：with rollup：任何一个分组后都有一个一个小组，最后都需要向上级进行汇报统计：根据当前分组字段。这就是回溯统计：回溯
统计的时候会将分组字段置空

多字段回溯：考虑第一层分组会有此回溯：第二次分组要看第一次分组的组数，组数是多少，回溯就是多少，然后加上第一层回溯即可。

having子句

having子句：与where子句一样：进行条件判断的

where是针对磁盘数据进行判断：进入到内存之后，会进行分组操作：分组结果就需要having来处理

having能做where能做的几乎所有事情，但是where却不能做having能做的很多事情

1. 分组统计的结果或者说统计函数都只有having能够使用

2. having能够使用字段别名，where不能：where是从磁盘读取数据，而名字只可能是字段名，别名是在
   字段进入内存之后才会产生。

order by子句

order by：排序，根据某个字段进行升序或者降序排序，依赖校对集

使用基本语法

order by 字段名 [asc|desc]; -- asc是升序(默认的)，desc是降序

排序可以进行多字段排序：先根据某个字段进行排序，然后排序好的内部，再按照某个数据进行再次排序

limit子句

limit子句是一种限制结果的语句：限制数量

limit有两种使用方式

方案1：只用来限制长度(数据量)：limit 数据量;

方案2：限制起始位置，限制数量：limit 起始位置, 长度;

方案2主要用来实现数据的分页：为用户节省时间，提高服务器的响应效率，减少资源的浪费

对于用户来讲：可以点击的分页按钮：1,2,3,4

对于服务器来讲：根据用户选择的页码来获取不同的数据：limit offset, length;

length：每页显示的数据量：基本不变

offset: offset = (页码 - 1) * 每页显示量

day 4