索引及高级数据操作
一、 索引
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)*每页显示量。