数据库知识点整理

• 什么是存储过程以及存储过程的调用
• 什么是触发器
• 什么是视图，什么是游标
• 非关系型数据库和关系型数据库区别和对比
• 什么是内连接、外连接、交叉连接、笛卡尔积
• SQL语言的分类
• 什么是索引，索引的优缺点
• MySQL B+Tree索引和Hash索引的区别
• B树和B+树的区别
• 聚集索引和非聚集索引区别
• 数据库事务
• 事务的并发问题，事务的隔离级别
• 数据库锁机制

1. 什么是存储过程以及存储过程的调用
存储过程是一个预编译的SQL语句集，优点是允许模块化的设计，只需创建一次在之后可以多次调用。如果某次操作需要执行多次SQL，使用存储过程比单纯执行SQL语句要快。
存储过程的调用：（1）可以用一个命令对象调用；可以供外部程序调用（如java程序）。

CREATE PROCEDURE order_tot_amt
@o_id int,
@p_tot int output
AS
SELECT @p_tot = sum(Unitprice*Quantity)
FROM orderdetails
WHERE orderid=@o_id
GO

存储过程的优缺点
优点：（1）存储过程是预编译过的，执行效率高； （2）存储过程的代码直接存放在数据库中，可以通过存储过程名直接调用，从而减少网络通讯； （3）执行存储过程需要权限，安全性高； （4）存储过程可以重复使用。
缺点：移植性差，灵活性低。

存储过程和函数的区别
存储过程和函数目的是为了 可重复地 执行操作数据库的sql语句的集合。
区别是写法和调用上。
写法上：存储过程的参数列表可以有输入参数、输出参数、可输入输出的参数；函数的参数列表只有输入参数，并且有return <返回值类型，无长度说明>。
返回值上：存储过程的返回值，可以有多个值，函数的返回值，只有一个值。
调用上：存储过程的调用方式有：exec <过程名>; execute <过程名>；SQL语句块或者外部程序直接调用等，函数则直接在SQL语句块调用。

2.什么是触发器
触发器是一种特殊的存储过程，提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。

create trigger tri_update
on student
for update
as
if update(student_id)
begin
raiserror('学号不能修改!',16,8)
rollbacktran
end
go

3.什么是视图，什么是游标
视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作。视图通常是有一个表或者多个表的行或列的子集。对视图的修改会影响基本表。其本质是对应于一条SELECT语句，结果集被赋予一个名字，即视图名字。视图本身并不包含任何数据，它只包含映射到基表的一个查询语句，当基表数据发生变化，视图数据也随之变化。
视图的优缺点：
优点：
1.视图可以有选择性的选取数据库里的一部分；
2.用户通过简单的查询可以从复杂查询中得到结果；
3.维护数据的独立性，试图可从多个表检索数据；
4.对于相同的数据可产生不同的视图。
缺点：
性能：查询视图时，必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么就无法更改数据。
游标是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。

4.非关系型数据库和关系型数据库区别和对比
关系型数据库指采用关系模型来组织数据结构的数据库(二维表)。常见的包括：SQL Server, PostgreSQL, MySQL, Oracle等。
优点：
1、易于维护：都是使用表结构，格式一致；
2、使用方便：SQL语言通用，可用于复杂查询；
3、复杂操作：支持SQL，可用于一个表以及多个表之间非常复杂的查询。
缺点：
1、读写性能比较差，尤其是海量数据的高效率读写；
2、固定的表结构，灵活度稍欠；
3、高并发读写需求，传统关系型数据库来说，硬盘I/O是一个很大的瓶颈。
非关系型数据库严格上不是一种数据库，是一种数据结构化存储方法的集合，可以是文档或者键值对等。
优点：
1、格式灵活：存储数据的格式可以是key,value形式、文档形式、图片形式等等，文档形式、图片形式等等，使用灵活，应用场景广泛，而关系型数据库则只支持基础类型。
2、速度快：nosql可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘；
3、高扩展性；
4、成本低：nosql数据库部署简单，基本都是开源软件。
缺点：
1、不提供sql支持，学习和使用成本较高；
2、无事务处理；
3、数据结构相对复杂，复杂查询方面稍欠。

5.什么是内连接、外连接、交叉连接、笛卡尔积
内连接: 只连接匹配的行
左外连接: 包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行
右外连接: 包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行

左外连接
SELECT a.,b. FROM t LEFT JOIN usertable as b ON t.username=b.username

全外连接: 包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。
交叉连接: 生成笛卡尔积－它不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配(CROSS JOIN) A表有3行，B表有4行，作交叉连接后得到的是12行，全连接不一定有多少行。

6.SQL语言的分类
SQL语言共分为四大类：
1.数据查询语言DQL
2.数据操纵语言DML
3.数据定义语言DDL
4.数据控制语言DCL。
（1）数据查询语言DQL
数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块。
（2）数据操纵语言DML
数据操纵语言DML主要有三种形式：INSERT，UPDATE，DELETE
（3）数据定义语言DDL
数据定义语言DDL用来创建数据库中的各种对象-----表、视图、索引、同义词、聚簇等如：CREATE TABLE/VIEW/INDEX/SYN/CLUSTER
DDL操作是隐性提交的，不能rollback
（4）数据控制语言DCL
数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。如：

1. GRANT：授权。
2. ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点。回滚—ROLLBACK；回滚命令使数据库状态回到上次最后提交的状态。其格式为：
   SQL>ROLLBACK;
3. COMMIT [WORK]：提交。分为显式提交，隐式提交和自动提交。

7.什么是索引，索引的优缺点
数据库索引，是数据库管理系统中一个排序的数据结构，索引的实现通常使用B树及其变种B+树。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。
唯一、不为空、经常被查询的字段可以用来建立索引。
索引的作用
协助快速查询、更新数据库表中数据
索引的优缺点：
优点：
创建索引可以大大提高系统的性能：
1.通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
2.可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
3.可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
4.在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
5.通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
缺点：
1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

8.MySQL B+Tree索引和Hash索引的区别
Hash索引因为其结构的特殊性，其检索效率非常高，索引的检索可以一次定位; 唯一、不为空、经常被查询的字段
B+树索引需要从根节点到枝节点，最后才能访问到叶节点，需要多次的IO访问
Hash索引的缺点

• Hash索引仅仅能满足"=",“IN"和”"查询，不能使用范围查询,因为经过相应的Hash算法处理之后的Hash值的大小关系，并不能保证和Hash运算前完全一样；
• Hash索引无法被用来避免数据的排序操作，因为Hash值的大小关系并不一定和Hash运算前的键值完全一样；
• Hash索引不能利用部分索引键查询，对于组合索引，Hash索引在计算Hash值的时候是组合索引键合并后再一起计算Hash值，而不是单独计算Hash值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash索引也无法被利用；
• Hash索引在任何时候都不能避免表扫描，由于不同索引键存在相同Hash值，所以即使取满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要回表查询数据；
• Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B+树索引高。

对Hash索引和B+ Tree索引的知识点补充：

• MySQL中，只有HEAP/MEMORY引擎才显式支持Hash索引
• 常用的InnoDB引擎中默认使用的是B+树索引，它会实时监控表上索引的使用情况，如果认为建立哈希索引可以提高查询效率，则自动在内存中的“自适应哈希索引缓冲区”建立哈希索引（在InnoDB中默认开启自适应哈希索引），通过观察搜索模式，MySQL会利用index key的前缀建立哈希索引，如果一个表几乎大部分都在缓冲池中，那么建立一个哈希索引能够加快等值查询
• 哈希索引不适范围查找和模糊查询
• 哈希索引也不支持多列联合索引的最左匹配规则
• B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题
• 在大多数场景下，都会有范围查询、排序、分组等查询特征，用B+树索引就可以了

9.B树和B+树的区别
B树，每个节点都存储key和data，所有节点组成这棵树，并且叶子节点指针为nul，叶子结点不包含任何关键字信息。
B+树，所有的叶子结点中包含了全部关键字的信息，及指向含有这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大的顺序链接，所有的非终端结点可以看成是索引部分，结点中仅含有其子树根结点中最大（或最小）关键字。 (而B树的非终节点也包含需要查找的有效信息)

为什么说B+比B树更适合实际应用中操作系统的文件索引和数据库索引？

• B+的磁盘读写代价更低：B+的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。
• B+tree的查询效率更加稳定：由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

10.聚集索引和非聚集索引区别
聚合索引(clustered index):
聚集索引表记录的排列顺序和索引的排列顺序一致，所以查询效率快，只要找到第一个索引值记录，其余就连续性的记录在物理也一样连续存放。聚集索引对应的缺点就是修改慢，因为为了保证表中记录的物理和索引顺序一致，在记录插入的时候，会对数据页重新排序。
非聚合索引(nonclustered index):
非聚集索引指定了表中记录的逻辑顺序，但是记录的物理和索引不一定一致，两种索引都采用B+树结构，非聚集索引的叶子层并不和实际数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页。适用的情况就在于分组，大数目的不同值，频繁更新的列中，这些情况即不适合聚集索引。
根本区别：
聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致。

11.数据库事务
事务是对数据库中一系列操作进行统一的回滚或者提交的操作。存在包括两个目的：1.为数据库操作序列提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。2.当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。
事务的四大特性（ACID）：原子性，一致性，隔离性，持久性
原子性：事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。
一致性：事务开始前和结束后，数据库的完整性约束没有被破坏。
隔离性：隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。
持久性：持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

12.事务的并发问题，事务的隔离级别
数据库事务的隔离级别：
隔离级别有4个，由低到高依次为读未提交，读提交，重复读和序列化。
读未提交：最低隔离级别，一个事务可以读取另一个事务并未提交的更新结果。
读提交：大部分数据库采用的默认隔离级别，一个事务的更新操作结果只有在该事务提交之后，另一个事务才可以读取到数据更新后的结果。
重复读：mysql的默认级别。整个事务过程中，对同一笔数据的读取结果是相同的，不管其他事务是否在对共享数据进行更新，也不管更新提交与否。
序列化：最高隔离级别：所有事务操作依次顺序执行，会导致并发度下降，性能最差，通常会用其他并发级别加上相应的并发锁机制来取代它。
事务的并发问题：
（1）脏读：事务A读取的事务B修改但还未提交的数据，然后B回滚操作，则A读取到的数据是脏数据。和不可重复读类似，但第二个事务不需要执行提交。
（2）不可重复读：事务A多次读取同一数据，事务B在事务A多次读取的过程中，对数据做了更新并提交，导致事务A多次读取同一数据时先后独到的数据结果会不一致。
（3）幻读：幻读发生在当两个完全相同的查询执行时，第二次查询返回的结果集和第一个查询不同。（发生在没有范围锁的情况）

• 不可重复读的重点是修改，同样的条件发现取出的值不一样了，幻读的重点是新增或删除，同样的条件发现取出的记录条数不一样。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。
• Mysql支持四种事务隔离级别，默认的事务隔离级别是重复读；
• Oracle支持读提交和序列化两种事务隔离级别；
• 隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为读提交，它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、幻读这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。

13.数据库锁机制

• 锁用于处理用户对数据库并发操作时所带来的的数据不一致问题，保证多用户环境下数据库的完整性和一致性。
• 从数据库系统角度分为排他锁，共享锁和更新锁；从程序员角度分为乐观锁和悲观锁。

悲观锁：每次拿数据时都认为别人会修改，每次拿数据时都会上锁，这样别人拿这个数据时就会block(阻塞)。具有强烈的独占和排他特性。悲观锁的实现往往依靠数据库的锁机制。
悲观锁按使用性质分为共享锁，排他锁和更新锁。（摘自https://blog.csdn.net/weixin_39651041/article/details/79985715）

共享锁也叫读锁，用于所有的只读数据操作，非独占，允许多个并发事务读取其锁定的资源。
性质
（1）多个事务可封锁同一个共享页；
（2）任何事务都不能修改该页；
（3）通常是该页被读取完毕，S锁立即被释放。
排他锁也叫写锁，表示对数据进行写操作。如果一个事务对对象加了排他锁，其他事务就不能再给它加任何锁了。
性质
（1）仅允许一个事务封锁此页；
（2）其他任何事务必须等到X锁被释放才能对该页进行访问；
（3）X锁一直到事务结束才能被释放。
更新锁在修改操作的初始化阶段用来锁定可能要被修改的资源，这样可以避免使用共享锁造成的死锁现象。
因为当使用共享锁时，修改数据的操作分为两步： 首先获得一个共享锁，读取数据， 然后将共享锁升级为排他锁，再执行修改操作。
这样如果有两个或多个事务同时对一个事务申请了共享锁，在修改数据时，这些事务都要将共享锁升级为排他锁。这时，这些事务都不会释放共享锁，而是一直等待对方释放，这样就造成了死锁。 如果一个数据在修改前直接申请更新锁，在数据修改时再升级为排他锁，就可以避免死锁。
性质
（1）用来预定要对此页施加X锁，它允许其他事务读，但不允许再施加U锁或X锁；
（2）当被读取的页要被更新时，则升级为X锁；
（3）U锁一直到事务结束时才能被释放。

悲观锁按作用范围划分为行锁，表锁，页锁。分别针对一行数据，一张表数据和一页数据。

乐观锁，每次去拿数据的时候都认为别人不会修改，所以，不会上锁。但是在更新的时候会判断一下在此期间别人有没有更新这个数据，可以使用版本号，时间戳，待更新字段等机制。

死锁: 是指两个或两个以上的进程在执行过程中。因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等待的进程称为死锁进程。