索引及高级数据操作
一、 索引
1、 索引的概念：系统根据已有的数据（未来可能新增的数据），单独建立一个文件：文件能够实现快速的匹配数据，并且能够快速的找到表中的对应数据
2、 索引的意义：
1） 提升查询数据的效率
2） 约束数据的有效性（唯一性等等）
3、 前提：
1）索引会产生索引文件，有时候会比数据文件大，这样会非常耗费磁盘空间。我们就需要再效率和磁盘空间之间找出一个平衡。如果某个字段作为查询的条件，那么我们就一定会使用索引（而且会想办法去增加索引）
2）如果某个字段需要进行数据的有效性的约束，那么我们也可以使用索引（主键，唯一键）。
l 注意：mysql中提供了多种索引：
1） 主键索引：primary key，就是我们之前讲的主键
2） 唯一索引：unique key，就是我们之前讲过的唯一键
3） 全文索引：fulltext index
针对文章内部的关键字进行索引，全文最大的一个问题就是如何去确定关键字。如果是英文的话就会比较容易，英文单词之间是有空格的。中文的话，没有空格，而且中文是可以随意组合的（分词：sphinx）。
4） 普通索引：index
二、 关系
将实体与实体之间的关系，反应到最终的数据库表的设计上来，我们可以将关系分为：一对一、一对多（多对一）、多对多。所有的关系都指的是表和表之间的关系
1、 一对一的关系
一张表的一条记录只能与另外一张表中的一条记录进行对应，反之亦然
学生表：姓名、性别、年龄、身高、体重、婚姻状况、籍贯、家庭住址、紧急联系人等
| Id | 姓名 | 年龄 | 体重 | 身高 | 婚姻 | 籍贯 | 住址 | 联系人 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
问题：表的设计成以上这种形式：符合要求，其中姓名，性别，年龄，身高，体重属于常用数据，其他的都是不常用数据，如果每次查询所有的数据，不常用的数据就会影响效率，实际上不是很实用。
解决方案：将常用的信息分别存储，分成两张表：
常用表
| Id | 姓名 | 年龄 | 体重 | 身高 | 性别 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
不常用表：
| Id | 婚姻 | 籍贯 | 住址 | 联系人 |
|
|
|
|
|
|
|
|
|
|
|
|
注意：一个常用表中的一条记录，永远只能在一张不常用的表中找到一条与之对应的记录；反之亦然。这就是一对一的关系的实际应用。
2、 一对多的关系
概念：一张表中有一条记录 可以对应另外一张表中的多条记录，这种就是一对多的关系或者是多对一的关系。
妈妈表：
| Id | 姓名 | 年龄 | 性别 |
|
|
|
|
|
孩子表
| Id | 姓名 | 年龄 | 性别 |
|
|
|
|
|
问题：这样设计的表解决了表的实体设计问题，但是没有解决关系问题。孩子找不到妈妈，妈妈也找不到孩子。
解决方案：在某一张表中，增加一个字段，使其能够找到其中对应的记录：那么我们应该在孩子表中增加一个字段使其指向妈妈。因为孩子只能也只需要匹配一条妈妈的记录。
孩子表：
| Id | 姓名 | 性别 | 年龄 | 母亲Id |
|
|
|
|
|
|
这个指向妈妈表的主键。
3、 多对多关系
概念：一张表（A）的一条记录能够对应另外一张表（B）中的多条记录，反之亦然。
这种关系被广泛应用于商品和订单之间的关系
简单实例：老师与学生的关系
老师表
| Teacher_id | 姓名 | 性别 |
| 1 | A | 男 |
| 2 | B | 女 |
学生表
| Student_id | 姓名 | 性别 |
| 1 | 张三 | 男 |
| 2 | 李四 | 女 |
问题：这样子的设计方案：体现了实体的设计，但是没有维护实体的关系，一个老师教过很多的学生，一个学生也被很多的老师教过
解决方案：在学生表中增加老师字段并且在老师表中增加学生字段，但是这样的设计也会出现另外一个问题，就是增加的该字段要保存多条数据，而且是与其他表有关系的字段，这样是不符合设计规范，我们就应该增加一张新表，专门维护两张表之间的关系。
|
| Teacher_id | Student_id |
|
| 1 | 1 |
|
| 1 | 2 |
|
| 2 | 2 |
|
| 2 | 1 |
增加了中间表之后，中间表就与老师表形成了一对多的关系，而且中间表是多表。维护了能够唯一找到一表的关系；同样的学生表也与中间表形成了一对多的关系
方案：
学生找老师，找出学生ID（一条）-> 中间表寻找匹配记录（多条）->老师匹配表（一条）
老师找学生，找出老师ID（一条）-> 中间表寻找匹配记录（多条）->学生匹配表（一条）
三、 范式
1、 概述：
范式（NomalFormat），是一种离散数学之中的知识，是为了解决数据存储于优化的问题，保存数据之后，凡是能够通过关系寻找出来的数：终极目标是为了减少数据的冗余。他是一种分层结构的规范，分为六层：每一层都比上层更加严格：若要满足下一层范式，前提是满足上一层范式
六层范式：1NF,2NF,3NF,4NF,5NF,6NF;INF是最底层要求最低，6NF是最高层，最严格。
Mysql属于关系型数据：有空间的浪费，也是致力于节省空间，与范式要解决的问题不谋而合，在设计数据库的时候，我们会利用范式来指导设计。但是数据库不单是要解决空间问题，又要保证效率。范式只是为了解决空间问题，所以数据库的设计与不可能完全按照范式的要求实现：一般情况下，只有前三层范式需要满足。我们只要满足到3NF就可以了。所以说，范式在数据库的设计当中是有指导意义，单是不是强制的规范。
2、 1NF
第一范式：在设计表存储数据的时候，如果表中设计的字段存储的数据，在取出来使用之前还需要进行额外的处理（拆分），那么说明表的设计不满足第一范式：第一范式要求字段的数据具有原子性，不可再分。
问题：上表的设计没有问题，但是如果需要将数据查出来之后，要求显示一个老师从什么时候开始上课，到什么时候结课，我们就需要将代课时间进行拆分，这样就不符合第一层范式，因为数据就不具有了原子性。
解决方案：将表中的代课时间拆分成两个字段就解决了问题。
3、 2NF
第二范式：在数据表的设计当中，如果有复合主键（多字段主键），且表中有字段并不是由整个主键来确定，而是依赖主键中的某个字段（主键部分）：存在字段依赖主键的部分的问题，我们将之称为部分依赖，第二范式就是不允许部分依赖。
问题：以上表中，因为讲师没有办法啊作为独立主键，需要结合班级才能作为主键（复合主键，老师在带一个班的时候，只带一个阶段的课），代课时间，开始和结束字段都与当前的代课主键（讲师和班级）有关系，但是性别并不依赖教室，教室不依赖讲师，性别只依赖讲师，教室只依赖班级。这样出现了性别和教室依赖主键中的一部分；这样就产生了部分依赖，不符合第二范式。
解决方案：
1） 将性别与讲师单独成表，班级与教室也单独成表
2） 取消复合主键，采用另外与业务不相关的逻辑主键。这样我们就解决了部分依赖的问题（增加单列关键字），只要我们不存在复合主键，那么就不会产生部分依赖。
这里的ID= 讲师 + 班级（这样这条记录的唯一性的话，我们就不能从数据库的设计来处理这样的问题，我们就只能从业务逻辑来处理这样的问题，比如说将讲师和班级设定为复合唯一键）。
4、 4NF
第三范式：要满足第三范式，就必须西安满足第二式
第三范式，在理论上来讲，我们应该把一张表中的所有字段都直接依赖主键（逻辑主键，代表的是业务主键），如果过数据表的设计过程当中存在一个字段，并不是直接依赖主键，而是依赖一个非主键字段，最终实现的依赖主键，我们将这种依赖称为传递依赖。这种依赖关系是不符合第三范式的。
第三依赖就是要解决这种传递依赖。
问题：上表中的设计方案中，是复合第一和第二范式的。但是：性别依赖讲师存在，教室依赖班级存在，性别和教室都存在传递依赖。我们就要解决这种依赖问题。
解决方案：将存在依赖传递的字段，以及依赖的字段本身单独取出，形成一个单独的表，然后在需要的对应信息的时候，使用对应的实体表的主键将之添加进来。
讲师代课表：
| Id p | 讲师id | 班级id | 代课时间 | 开始 | 结束 |
| 1 | 1 | 10 | 30天 | 2.27 | 5.05 |
| 2 | 2 | 12 | 30 | 2.27 | 5.05 |
讲师表：（Id= 讲师）
| Id | 讲师 | 性别 |
| 1 | 朱元璋 | Male |
| 2 | 李世民 | Male |
班级表：（ID= 班级）
| Id | 班级 | 教室 |
| 1 | Java1501 | 302 |
| 2 | Java1502 | 206 |
这样我们就可以看到，将数据分开在之后，我们就可以结束这种传递的依赖关系了。
注意：我们在是使用逻辑主键的额时候，向数据库中传值的时候，是时分方便的，这时候我们就遇到了另外一个问题，采用逻辑主键的时候，永远都不可能解决传递依赖。但是我们们应该注意到，实际上能代表记录含义的，只是表中的业务数据。
è还有很重要的一点是，我们能够通过一个字段查到的数据，我们就不会重复的设定数据。比如说在代课教师表的时候，我们会发现里面只有讲师id，这是因为如果在这张表里面添加姓名这个字段的话，就跟教师表中的教师姓名重复，这就叫做数据的冗余。
5、 范式的逆规范化
有时候我们在设计表的时候，如果一张表中由几个字段师需要从另外一张表中去获得数据信息，理论上讲，的确可以后去想要的数据，但是效率会低一点。那么我们会刻意的在某些表中，不去保存另外表的主键（逻辑主键），而是直接保存想要的信息，这样一来，在查询数据的时候，一张表刻意直接提供数据，而不需要多张表去查询（效率低）。
这种行为是一种逆规范化。但是这会导致数据冗余增加。
四、 数据的高级操作
1、 新增数据
基本语法：insert into 表名(字段列表) values （值列表）；
在数据插入的时候，假设主键对应的值已经存在：那么数的插入师一定会失败的。
ð主键冲突：当主键发生冲突的时候，可以选择性的进行处理：更新和替换。
1） 主冲突之后的更新操作：
Insert into 表名(字段列表：包含主键)values(值列表)on duplicate key update 字段 = 新值；
ð主键冲突：替换数据
Replace into 表名 (字段列表：包含主键)values (值列表);
ð复制创建表：
Createte table 表名 like 被复制表名;
我们需要注意的是，这样复制的表实际上只是复制了表的设定，没有复制表中的数据。
2） 蠕虫复制从已有的数据中去获取数据，然后将数据又进行新增操作：数据会成倍的增加。
Insert into 表名（字段列表）select 字段列表/* from 数据表名;
意义：
（1） 从一张表中复制数据
（2） 可以迅速的让表中的数据膨胀到一定的数量级，测试表的压力以及效率。
2、 更新数据
基本语法：update 表名 set 字段 = 值[where条件];
高级新增语法：update 表名 set 字段 = 值[where条件][limit 更新数量];
3、 删除数据
基本语法：delete from表名 where条件 limit更新数量;
问题：如果表中存在主键，那么当我们删除之后，自增长是不会还原的。
解决方案：数据的删除是不会改变表的结构的，只能删除表后重建。Truncate表名; 先删除后新增表
4、 查询数据
基本语法：select 字段列表 from 表名（where条件）;
完整语法：select[select选项] 字段列表[字段别名] from 数据源 [where条件] [group by子句] [having 子句][order by子句][limit子句];
1） Select选项：select对查出来的数据的处理方式；
All : 默认的，保留所有结果；
Distinct ：去重查出来的结果将重复的记录去除，所有字段的数都一样才算记录的重复；
2） 字段别名：当数据进行查询的时候，有时候字段名就并不一定满足需求（多张表进行查询的时候，很有可能会有同名字段），我们就需要对字段进行重命名。
语法：字段名 as 别名;
3） 数据源
数据源：数据的来源，关系型数据库的来源都是数据表：本质上只要保证数据类似二维表，最终都可以作为数据源。
分类：数据源可以分为多种：单表数据源，多表数据源，查询语句。
查询语句：
A、 单表数据源：select * from 表名;
B、 多表数据源：select * from 表名1,表名2；
|
多表查询 |
从一张表中取出一条记录，去另外一张表中匹配所有记录，而且全部保留：（记录数和字段数），我们将这种结果称为笛卡儿积（交叉连接），然而笛卡儿积没什么用，所以应该尽量避免。
子查询：数据的来源是一条查询语句（查询语句的结果是一张二维表）
语法：select */字段列表 from（select 语句）as 别名;
注意： 在使用子查询的时候，这条语句的含义是，从括号里面已经查询的内容里面在进行其他的查询，也就是说字段列表必须包含在括号里面的select语句里面。
问题：在实际应用当中，我们是很少使用子查询的，因为这样的操作很容易就会产生笛卡儿积，射阳会使得程序的效率大大降低。
4） Where子句
概念：用来判读数据，筛选数据
Where子句返回的结果：0或者1，0代表false，1代表true
判断条件：
l 比较运算符：<,>,<=,>=,!=,<>(这也是不等于),=,like(模糊匹配),between and(在什么之间),in,not in
l 逻辑运算符：&&(and), ||(or), ! (not)
l Where 原理：where是唯一一个是直接从磁盘获取数据的时候就开始判断的条件，进行where判断的时候 ，判断的结果如果成立就保存到内存当中，如果失败了，就直接放弃。
其他的判断操作是作用于内存，将数据库中的数据取出来放到内存当中的之后在进行判断，最终将判断结果显示给用户。
l Where的好处：不会太占用内存。
l 范例：
条件查询1：要求找出学生id为1或者5的记录
条件查询2：要求查出年龄在20到34之间的数据
注意：between本是就是闭区间，所以我们就必须保证左边的比右边的小或者等于。
问题：当我们输入的where语句的条件仅仅是非零数那么会输出所有的数据。如果是0的话，就会输出empty set。
5） Group By子句
就是分组的意思，根据某个字段进行分组（相同的放一组，不同的划分到不同的组）。
基本语法：group by 字段名；但是我们不能简单通过这样的方式显示所有分组后的数据。我们还需要借助其他的手段才能完成数据的按需显示。
Sql提供的一系列统计函数：
l Count()： 统计分组之后的数据：每一组有多少记录。里面可以使用两种参数：* 代表统计记录，字段名代表对应的字段，但是有时候，我们的字段有可能为空，实际上这个并不会有什么影响，null是不参与统计的，所以我们并不需要担心
l Max() ：统计字段中的最大值
l Min() ：统计字段中的最小值
l Avg() ：统计平均值
l Sum() ：统计分组之后的和
l 多字段分组，先班级，后男女。
假如我们需要知道列表中的复合某种条件的名字。Mysql提供了一个函数，可以分组结果中的摸个字段进行字符串连接（保留改该组中所有的某额字段）：group_concat(字段);
回溯统计：withrollup:
任何一个分组后的结果，都会有一个小组，最后都需要相上级分组进行汇报统计，根据当前字段分组，这就是回溯统计：回溯统计的时候会将分组字段置空。
多字段回溯统计：
|
最后再总的数据回溯一次 |
注意：多字段回溯：考虑到第一层分组会有一次回溯，第二次分组要看第一次分组的组数，组数是多少，回溯就是多少，然后加上第一层回溯即可。
6） Having子句：与where子句一样：进行条件判断。Where是针对磁盘数据进行判断的，但是数据进入到内存时候会进行分组操作，分组结果就需要having来处理数据。Having几乎能做where能做的事情，但是where却不能做having能做的事情
A、 分组统计的结果或者说统计函数都只有having能够使用。
B、 Having能够使用字段别名，但是where不能因为where是从磁盘取数据，而名字只可能是字段名，别名是字段进入字段之后才产生的。
5、 Order By子句
Order by排序，根据某个字段进行升序或者降序排序，它一来校对集
基本语法：order by 字段名[asc/desc]; asc是升序（默认的），desc降序。
排序其实可以进行多字段排序：先根据某个字段进行排序，然后再内排。
6、 Limit子句：limit是一种限制结果的语句：限制数量
方案一：只用来限制长度（数量）:limit 数据量;
方案二：用来限制起始位置以及数量：limit 其实位置，长度;
方案二实际上主要是要来数据的分页，多少条进行一次分页，主要是为用户节省时间，为服务器提高效率，减少资源的浪费。
对于用户来讲：可以点击的分页按钮
对于服务器来讲：根据用户选择的页码来获取不同的数据：
Limitoffset ,length;
Length：meyer显示的数量：基本是不变的。
Offset：起始位置，一般是数量的整数倍（页码-1）*每页显示量。
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
