MySQL-3

十三、自查询

案例：查询员工的姓名和他的领导的姓名

在一张员工表里面

select e1.ename,e2.ename
-- 把一张表在内存中复制为两张表
from emp e1,emp e2
-- 这里的e1表示员工表  e2表示领导表
where e1.mgr=e2.empno;

十四、SQL语句

14.1 SQL

结构化查询语言（Structured Query Language）简称SQL，是一种特殊目的的编程语言

14.2 用途

sql语句是用于操作数据库的语言

用于存取数据以及查询、更新和管理[关系数据库系统]

14.3 分类

结构化查询语言包含6个部分：

1、数据查询语言（DQL: Data Query Language）：其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING。这些DQL保留字常与其它类型的SQL语句一起使用。

2、数据操作语言（DML：Data Manipulation Language）：其语句包括动词INSERT、UPDATE和DELETE。它们分别用于添加、修改和删除。

3、事务控制语言（TCL）：它的语句能确保被DML语句影响的表的所有行及时得以更新。包括COMMIT（提交）命令、SAVEPOINT（保存点）命令、ROLLBACK（回滚）命令。

4、数据控制语言（DCL）：它的语句通过GRANT或REVOKE实现权限控制，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。

5、数据定义语言（DDL）：其语句包括动词CREATE,ALTER和DROP。在数据库中创建新表或修改、删除表（CREATE TABLE 或 DROP TABLE）；为表加入索引等。

6、指针控制语言（CCL）：它的语句，像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。

14.4 mysql数据库引擎

（1）InnoDB

支持事务，索引采用聚簇索引。

（2）MyISAM

不支持事务，索引采用非聚簇索引。

（3）MEMORY

（4）Archive

十五、约束

约束是对数据的增删改操作的限制，目的：保证数据的完整性。

数据的完整性：

（1）实体完整性：通过主键约束

（2）参照完整性：通过外键约束

（3）域完整性：通过列级约束

创建表结构时，添加约束。

约束分为：表级约束和列级约束。

15.1 是否允许为空

列级约束：

null 默认是null

not null：不管是添加还是修改操作，都允许该列的值为null。

15.2 唯一性约束

列级约束

unique：该列的值不允许重复。

注意：null和任何值都不相等，null和null也不相等。

15.3 缺省值

列级约束

系统默认的缺省值是null。

我们可以使用default修改缺省值。

用法：default 缺省值

15.4 检查约束

列级约束

检查插入或者修改的数据是否符合指定的条件。

用法：check （条件）

15.5 主键

表级约束

作用：用于区别每行数据，确定数据的唯一性的信息。

比如：身份证号码可以区别所有中国人；

​ 学号可以区别一个学校中所有学生；

​ 城市的编码，可以区别城市。

一个表最多只能有一个主键，主键可以由一列或多列的信息构成。

主键：单列主键和复合主键。

主键约束：有且唯一，not null+unique。

单列主键

单列主键可以写在设置主键的语句之后，也可以写在表的最后面。

联合主键

15.6 自动增长

列级约束

auto_increment，从1开始。

作用：某列的值，系统自动插入增长的值。

前提：该列是整数类型，并且该列是构成主键的列。

mysql支持自动增长，oracle数据库不支持，oracle使用序列实现自增。

15.7 外键

表级约束

用来表示两张表之间的关系的。

从表创建外键：

FOREIGN KEY(外键列) REFERENCES 主表(主键列)

从表的外键关联到主表的主键上。

一张表可以有多个外键，每个外键都由一列构成。

外键约束：从表的外键列数据必须来源于主表的主键列中。

总结外键约束：

先建主表，再建从表；

先删从表，再删主表。

先添加主表数据，再添加从表数据；

先删除从表数据，再删除主表数据。

外键可以插入null，这样也不会违背外键约束。

在企业中开发，设计数据库时，我们一般不添加外键约束。心中有外键就可以了。

十六、数据库三范式

数据库的设计范式是数据库设计所需要满足的规范，满足这些规范的数据库是简洁的、结构明晰的，同时，不会发生插入（insert）、删除（delete）和更新（update）操作异常。

根据要存储的数据来设计数据库：有哪些表，每张表有哪些字段，表有哪些约束，表有哪些索引，每个字段的数据类型是什么？

按照数据库的三范式来设计。

16.1 范式

我们在设计数据库时，应该遵守的规范和原则。

我们一共有六个规范：

第一范式（1NF），第二范式（2NF），第三范式（3NF）

巴斯科德范式（BCNF），第四范式（4NF），第五范式（5NF，完美范式）。

16.2 第三范式（3NF）

企业要求满足第三范式，基本就可以了。

第二范式必须先满足第一范式。

第三范式必须先满足第二范式。

（1）第一范式 ：列不可再分

第一范式（1NF）是指在关系模型中，对于添加的一个规范要求，所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。

第一范式，要求每列只能存储一个值。所以上面这个表格中的name列不符合第一范式要求。

（2）第二范式 ：属性完全依赖于主键

在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）

通俗的讲，每个表中必须有主键。

（3）第三范式

在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）。（所有列都要依赖于主键列）

避免信息大量重复造成数据冗余

十七、索引和视图

17.1 索引

什么是索引？

一个索引是存储的表中一个特定列的值数据结构（最常见的是B-Tree）。索引是在表的列上创建。所以，要记住的关键点是索引包含一个表中列的值，并且这些值存储在一个数据结构中。请记住记住这一点：索引是一种数据结构 。

（1）作用：

1.通过创建索引,可以在查询的过程中,提高系统的性能

2.通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性

3.在使用分组和排序子句进行数据检索时,可以减少查询中分组和排序的时间

（2）索引为什么能够提高查询效率？

将关键字和我们数据的地址建立关联，通过关键字直接可以找到地址，获取到数据。

（3）索引采用的数据结构

采用B+树存储索引信息。

原因：层数少，查询效率高，存储的数据量大。

（4）索引的分类与说明

1.普通索引 index 单列索引

一个索引只包含单个列，一个表可以有多个单列索引

CREATE INDEX....

DROP INDEX.....

2.唯一索引 unique（指定唯一键–>系统自动创建）

索引列的值必须唯一，但允许有空值

CREATE UNIQUE INDEX.....


drop index......

3.主键索引 primary key（指定主键–>系统自动创建）

设定为主键后数据库会自动建立索引，innodb为聚簇索引

4.复合索引

一个索引包含多个列，在数据库操作期间，复合索引比单值索引所需要的开销更小（对于相同的对个列建索引）

如果一个表中的数据在查询时有多个字段总是同时出现则这些字段就可以作为复合索引，形成索引覆盖可以提高查询效率。

CREATE INDEX...ON....

DROP INDEX.....ON...

5.聚集索引与非聚集索引

聚集索引

指索引项的排序方式和表中数据记录排序方式一致的索引。它会根据聚集索引键的顺序来存储表中的数据，即对表 的数据按索引键的顺序进行排序，然后重新存储到磁盘上。因为数据在物理存放时只能有一种排列方式，所以一个表只能有一个聚集索引。比如字典中，用‘拼音’查汉字，就是聚集索引。因为正文中字都是按照拼音排序的。而用‘偏旁部首’查汉字，就是非聚集索引，因为正文中的字并不是按照偏旁部首排序的，我们通过检字表得到正文中的字在索引中的映射，然后通过映射找到所需要的字。

聚集索引的使用场合为：

a.查询命令的回传结果是以该字段为排序依据的；
b.查询的结果返回一个区间的值；
c.查询的结果返回某值相同的大量结果集。
聚集索引会降低 insert，和update操作的性能，所以，是否使用聚集索引要全面衡量。

非聚集索引

非聚集索引：与聚集索引相反， 索引顺序与物理存储顺序不一致。

非聚集索引的使用场合为：

• a.查询所获数据量较少时；
• b.某字段中的数据的唯一性比较高时；

非聚集索引必须是稠密索引

create [unique] [clustered] [nonclustered] index index_name  on {tabel/view} (column[dese/asc][....n])

注： [unique] [clustered] [nonclustered]表示要创建索引的类型，以此为唯一索引，聚集索引，和非聚集索引，当省略unique选项时，建立非唯一索引.当省略clustered,nonclustered选项时.建立聚集索引，省略nonclustered选项时，建立唯一聚集索引。

使用场景对比

6.聚簇索引与非聚簇索引

6.1 聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据。

聚簇索引的特点：

①聚簇索引具有唯一性，由于聚簇索引是将数据跟索引结构放到一块，因此一个表仅有一个聚簇索引。

②表中行的物理顺序和索引中行的物理顺序是相同的，在创建任何非聚簇索引之前创建聚簇索引，这是因为聚簇索引改变了表中行的物理顺序，数据行 按照一定的顺序排列，并且自动维护这个顺序；

③聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一且非空的索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键（类似oracle中的RowId）来作为聚簇索引。如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可。

6.2 非聚簇索引

不是聚簇索引的二级索引，也叫辅助索引，都称为非聚簇索引。将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置。

6.3 MySQL的MYISAM引擎和INNODB引擎

因为这两种引擎对非聚簇索引和聚簇索引的使用，就是他们之间很大的一个区别。所以结合这两个引擎，再对这两种索引展开些描述就更明了了。

对比总结

7. 稠密索引与稀疏索引

在了解稠密索引和稀疏索引之前我们先来了解一下什么是聚焦索引。在一个文件中，可以有多个索引，分别基于不同的搜索码。如果包含数据记录的文件按照某个指定的顺序排列，那么该搜索码对应的索引就是聚焦索引。

7.1 稠密索引

在稠密索引中，文件中的每个搜索码值都对应一个索引值。也就是说，稠密索引为数据记录文件的每一条记录都设一个键-指针对。如下图所示，索引项包括索引值以及指向该搜索码的第一条数据记录的指针，即我们所说的键-指针对。

7.2 稀疏索引

在稀疏索引中，只为搜索码的某些值建立索引项。也就是说，稀疏索引为数据记录文件的每个存储块设一个键-指针对，存储块意味着块内存储单元连续。如下图所示。

（5）创建索引

创建表时：

表创建完毕之后再加索引

（6）索引的创建策略

问题：

是不是索引创建的越多越好？

越多是不是查询效率就越高呢？

解答：

索引要适量。

索引会影响增删改的效率，增删改时需要添加索引信息。

索引多了，会消耗内存和硬盘，反而会影响查询效率。

哪些列需要建立索引？

1.主键列自动建立索引

2.唯一键列自动建立索引

3.常出现where和on后面的列，建立索引

（7）删除索引

drop index 索引名

（8）索引失效

1.如果条件中有or，即使其中有条件带索引也不会使用（这也是为什么少用or的原因）

要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引。

2.对于多列索引，不是使用的第一部分，则不会使用索引。

3.like查询以%开头

4.如果列类型是字符串，那一定要在条件中将数据使用引号引起来，否则不使用索引。

5.如果mysql估计使用全表扫描要比使用索引快，则不使用索引

6.索引列没有限制 not null，索引不存储空值，如果不限制索引列是not null，oracle会认为索引列有可能存在空值，所以不会按照索引计算

7.truncate会删除索引

17.2 索引的底层原理

底层是通过B+树来实现的数据结构存储。

数据存储结构，决定了数据查找和操作时的效率，包括时间复杂度和空间复杂度。而在取舍的时候，也无非就是时间换空间，空间换时间的权衡罢了。所以，这就很好的解释了，为什么Mysql在索引的底层设计上，选用了B+数，而没有选用B-树，或是红黑树，AVL树等等其他数据结构。总之，就是使用B+树作为索引的结构存储，能在I/O性能上得到一个较大的优势。

17.2.1 B-Tree

B-树是一种多路自平衡的搜索树 它类似普通的平衡二叉树，不同的一点是B-树允许每个节点有更多的子节点。B-Tree 相对于 AVLTree 缩减了节点个数，使每次磁盘 I/O 取到内存的数据都发挥了作用，从而提高了查询效率。

注：B-Tree就是常说的B树

那么m阶 B-Tree 是满足下列条件的数据结构：

• 所有键值分布在整颗树中
• 搜索有可能在非叶子结点结束，在关键字全集内做一次查找,性能逼近 二分查找
• 每个节点最多拥有m个子树
• 根节点至少有2个子树
• 分支节点至少拥有m/2颗子树（除根节点和叶子节点外都是分支节点
• 所有叶子节点都在同一层、每个节点最多可以有m-1个key，并且以升序排列

每个节点占用一个磁盘块，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为 17 和 35，P1 指针指向的子树的数据范围为小于 17，P2 指针指向的子树的数据范围为 17~35，P3 指针指向的子树的数据范围为大于 35。

模拟查找关键字29的过程：

1.根据根节点找到磁盘块 1，读入内存。【磁盘 I/O 操作第 1 次】
2.比较关键字 29 在区间（17,35），找到磁盘块 1 的指针 P2。
3.根据 P2 指针找到磁盘块 3，读入内存。【磁盘 I/O 操作第 2 次】
4.比较关键字 29 在区间（26,30），找到磁盘块 3 的指针 P2。
5.根据 P2 指针找到磁盘块 8，读入内存。【磁盘 I/O 操作第 3 次】
6.在磁盘块 8 中的关键字列表中找到关键字 29。
7.分析上面过程，发现需要 3 次磁盘 I/O 操作，和 3 次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而 3 次磁盘 I/O 操作是影响整个 B-Tree 查找效率的决定因素。

但同时B-Tree也存在问题：

每个节点中有key，也有data，而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小。

当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。

17.2.2 B+Tree

B+Tree 是在 B-Tree 基础上的一种优化，InnoDB 存储引擎就是用 B+Tree 实现其索引结构。它带来的变化点：

• B+树每个节点可以包含更多的节点，这样做有两个原因,一个是降低树的高度。另外一个是将数据范围变为多个区间，区间越多，数据检索越快

• 非叶子节点存储key，叶子节点存储key和数据

• 叶子节点两两指针相互链接（符合磁盘的预读特性），顺序查询性能更高

  注：MySQL 的 InnoDB 存储引擎在设计时是将根节点常驻内存的，因此力求达到树的深度不超过 3，也就是说 I/O 不需要超过 3 次。

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对 B+Tree 进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

17.2.3 B-Tree和B+Tree的区别

• B+树内节点不存储数据，所有数据存储在叶节点导致查询时间复杂度固定为 log n
• B-树查询时间复杂度不固定，与 key 在树中的位置有关，最好为O(1)
• B+树叶节点两两相连可大大增加区间访问性，可使用在范围查询等
• B+树更适合外部存储(存储磁盘数据)。由于内节点无 data 域，每个节点能索引的范围更大更精确。

17.2 视图

（1）视图的作用

视图是按照指定的要求，将表里的数据展示出来。

视图主要用于查询，不做增删改操作。

（2）使用视图的原因

有些敏感字段或者安全要求比较高的字段我们是不能展示的。

将一些比较复杂数据查询，通过视图直接提供出来，就不需要我们去写这些复杂的查询语句了。

（3）创建视图

create view 视图名 as 表的查询语句；

（4）查询视图

查询视图的语句和查询表是一样的。

（5）视图的本质

视图对象中只存储了一条表的查询语句而已，并没有存储数据。

数据还是在表中存储的。

我们查询视图时，先去执行表的查询语句，然后在此基础上继续查询罢了。

所以，视图的效率不高。

（6）删除视图