数据库重要知识

视图：一张虚拟的表

好处：sql语句提高重用性，效率高

和表实现了分离，提高了安全性

视图与表的区别？

              使用方式           占用物理空间

视图       完全相同          不占用，仅仅保存的是SQL逻辑

表          完全相同          占用

视图创建
        CREATE VIEW  视图名
        AS 查询语句;

示例：CREATE VIEW myview as select * from user;

视图更新：crteate or replace veiw 视图名

as 查询语句；

alter view 视图名 as 查询语句；

视图的可更新性和视图中查询的定义有关系，以下类型的视图是不能更新的
        - 包含以下关键字的sql语句：分组函数、distinct、group  by、having、union或者union all
        - 常量视图
        - Select中包含子查询
        - join
        - from一个不能更新的视图
        - where子句的子查询引用了from子句中的表

视图删除：drop view 视图名1，视图名2.。。。；

视图结构查看

desc 视图名；

show create view 视图名；

查看视图数据

select 查询字段  from 视图名；

插入视图数据

insert into 视图名（视图字段）values （值）

修改视图数据

update 视图名

set 视图字段名=值

where 条件

删除视图数据

delete from视图名

存储过程

一组经过预先编译的sql语句的集合

创建存储过程：

delimiter  
    CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名  参数类型,...)
    BEGIN
        sql语句1;
        sql语句2;
    END $

参数

- in:该参数只能作为输入 （该参数不能做返回值）
    - out：该参数只能作为输出（该参数只能做返回值）
    - inout：既能做输入又能做输出

调用存储过程:

call 存储过程名(实参列表)

删除存储过程：

DROP PROCEDURE 存储过程名;

查看存储过程结构：

SHOW CREATE PROCEDURE 存储过程名; 

为什么要用存储过程

1. 简化对变动的管理。如果表名、列名、或业务逻辑有了变化。只需要更改存储过程的代码。使用它的人不用更改自己的代码。

2. 通常存储过程都是有助于提高应用程序的性能。当创建的存储过程被编译之后，就存储在数据库中，可重复使用。

3. 存储过程有助于减少应用程序和数据库服务器之间的流量。 因为应运程序不必发送多个冗长的SQL语句，只用发送存储过程中的名称和参数即可。

存储过程的缺点

1. 不易维护，阅读性差

2. 如果使用大量的存储过程，那么使用这些存储过程的每个连接的内存使用量将大大增加。 此外，如果在存储过程中过度使用大量的逻辑操作，那么CPU的使用率也在增加，因为MySQL数据库最初的设计就侧重于高效的查询，而不是逻辑运算。

3. 很难调试存储过程。只有少数数据库管理系统允许调试存储过程。不幸的是，MySQL不提供调试存储过程的功能。

4. 开发和维护存储过程都不容易。

   开发和维护存储过程通常需要一个不是所有应用程序开发人员拥有的专业技能。这可能导致应用程序开发和维护阶段的问题。

为什么存储过程比sql语句效率高？

1.存储过程经过预编译处理 而SQL查询没有，SQL语句需要先被数据库引擎处理成低级的指令 然后才执行。

2.减少应用程序和数据库服务器之间的流量。

函数

经过编译并存储在数据库中的一段sql语句的集合

 关键字        调用语法                                   返回值                    应用场景
函数        FUNCTION  SELECT 函数()         只能是一个       一般用于查询结果为一个值并返回时，当有返回值而且仅仅一个
存储过程   PROCEDURE CALL 存储过程()    可以有0个或多个       一般用于更新

触发器触发器（trigger）是个**特殊的存储过程**，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。

- 作用：

触发器经常用于加强数据的完整性约束和业务规则等;

可在写入数据前，强制检验或者转换数据(保证安全性);

触发器发生错误时，前面用户已经执行成功的操作会被撤销，类似事务的回滚。

触发器优缺点

安全性

触发器有回滚性，保证数据完整

保存用户的操作，存入日志

触发器可以对数据库中的相关表进行连环更新

缺点：

不好定位出问题的地方

使代码变复杂

变动数据大的时候效率低

索引

官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

索引分为三类：

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

   唯一索引：索引列的值必须唯一，可为空

   复合索引：一个索引包括多个列

索引的优势和劣势
优势：

可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录。

通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。
劣势：

索引会占据磁盘空间

索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件。

什么情况下适合建立索引

   1）主键自动建立唯一索引

   2）频繁作为查询条件的字段（where后面的字段）

   3）查询中与其他表关联的字段（各种join on后面的字段）

   4）单值/复合索引选择？（高并发下倾向选择复合索引）

   5）查询中排序的字段

   6）查询中统计或分组的字段

什么情况下不适合建立索引

   1）表数据太少

   2）频繁更新的字段

   3）where后面用不到的字段

什么时候会出现索引失效

1）like以通配符开头('%abc')会导致索引失效，违反最左前缀法则

2）在索引列上做任何操作(计算、函数、类型转换)，会导致索引失效而转向全表扫描

3）存储引擎不能使用索引中范围条件右边的列，举例：select id，name from student where id > 50 and name = '张三'，会导致name索引失效

4）尽量使用覆盖索引，不要select *

5）MySQL在使用不等于(!=或<>)的时候无法使用索引会导致全表扫描，理由也很简单，B+Tree叶子节点用指针相连且是排好序的，这种数据结构只能解决有序的定值查询，像不等于这种无法利用索引查询。

6）IS NULL、IS NOT NULL无法使用索引，理由同上

7）字符串不加单引号索引失效

隐式转换-->函数操作

8）用or连接时会导致索引失效

索引优化可以使查询优化，还有空间上的优化也可以优化查询

索引优化就是防止索引失效

锁

基于锁的属性分类:共享锁（读锁）、排他锁（写锁）。
基于锁的粒度分类:行级锁（(innodb )、表级锁（ innodb、myisam)、页级锁（ innodb引擎)、记录锁、间隙锁、临键锁。
基于锁的状态分类:意向共享锁、意向排它锁（一般不用）。

按属性分：
共享锁(share lock):共享锁又称读锁，简称S锁;当一个事务为数据加上读锁之后，其他事务只能对该数据加读锁，而不能对数据加写锁，直到所有的读锁释放之后其他事务才能对其进行加持写锁。共享锁的特性主要是为了支持并发的读取数据，读取数据的时候不支持修改，避免出现重复读的问题。

排他锁（exclusive lock)︰排他锁又称写锁，简称×锁;当一个事务为数据加上写锁时，其他请求将不能再为数据加任何锁，直到该锁释放之后，其他事务才能对数据进行加锁。排他锁的目的是在数据修改时候，不允许其他人同时修改，也不允许其他人读取，避免了出现脏数据和脏读的问题。
 

按粒度分：
行锁:行锁是指上锁的时候锁住的是表的某一行或多行记录，其他事务访问同一张表时，只有被锁住的记录不能访问，其他的记录可正常访问，特点:粒度小，加锁比表锁麻烦，不容易冲突，相比表锁支持的并发要高

表锁(table lock):表锁是指上锁的时候锁住的是整个表，当下一个事务访问该表的时候，必须等前一个事务释放了锁才能进行对表进行访问;特点:粒度大，加锁简单，容易冲突;

页锁:页级锁是MysQL中锁定粒度介于行级锁和表级锁中间的一种锁．表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。特点:开销和加锁时间界于表锁和行锁之间，会出现死锁;锁定粒度界于表锁和行锁之间，并发度一般。

记录锁(Record lock):记录锁也属于行锁中的一种，只不过记录锁的范围只是表中的某一条记录，记录锁是说事务在加锁后锁住的只是表的某一条记录，加了记录锁之后数据可以避免数据在查询的时候被修改的重复读问题，也避免了在修改的事务未提交前被其他事务读取的脏读问题

间隙锁:是属于行锁的一种，间隙锁是在事务加锁后其锁住的是表记录的某一个区间，当表的相邻ID之间出现空隙则会形成一个区间，遵循左开右闭原则。范围查询并且查询未命中记录，查询条件必须命中索引、间隙锁只会出现在REPEATABLE_READ(重复读)的事务级别中。

临键锁(Next-Key lock):也属于行锁的一种，并且它是INNODB的行锁默认算法，总结来说它就是记录锁和间隙锁的组合，临键锁会把查询出来的记录锁住，同时也会把该范围查询内的所有间隙空间也会锁住
再之它会把相邻的下一个区间也会锁住。

事务

事务是一系列的动作，它们综合在一起才是一个完整的工作单元，这些动作必须全部完成，如果有一个失败的话，那么事务就会回滚到最开始的状态，仿佛什么都没发生过一样。

数据库事务是保证在并发情况下能够正确执行的重要支撑，MySQL常见的数据库引擎中支持事务的是InnoDB。

事务就是一系列操作，正确执行并提交，如果中途出现错误就回滚。事务要保证能够正常的执行，就必须要保持ACID特性。

事务的开始和结束

- COMMIT 或ROLLBACK 语句
- DDL 或DCL 语句（自动提交）
- 用户会话正常结束
- 系统异常终了

事务的四个特性：

① 原子性(atomicity) 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

事务是一个原子操作, 由一系列动作组成。 组成一个事务的多个数据库操作是一个不可分割的原子单元，只有所有的操作执行成功，整个事务才提交。

事务中的任何一个数据库操作失败，已经执行的任何操作都必须被撤销，让数据库返回初始状态。

② 一致性(consistency) 事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

一旦所有事务动作完成, 事务就被提交。数据和资源就处于一种满足业务规则的一致性状态，即数据不会被破坏。

比如a+b=100,一个事务改变了a比如增加了a的值，那么必须同时改变b，保证在事务结束以后a+b=100依然成立，这就是一致性。

③ 隔离性(isolation) 事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

在并发数据操作时，不同的事务拥有各自的数据空间，它们的操作不会对对方产生干扰。准确地说，并非要求做到完全无干扰。

数据库规定了多种事务隔离级别，不同的隔离级别对应不用的干扰程度。隔离级别越高，数据一致性越好，但并发行越弱。

比如对于A对B进行转账，A没把这个交易完成的时候，B就不知道A要给他转多少钱。

④ 持久性(durability) 持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

数据库管理系统一般采用重执行日志来保证原子性、一致性和持久性。

① DEFAULT(读提交)
这是一个PlatfromTransactionManager默认的隔离级别，使用数据库默认的事务隔离级别。

另外四个与JDBC的隔离级别相对应。大部分数据库的默认级别都是READ_COMMITTED(读取已提交)。

② READ_UNCOMMITTED（读取未提交）
这是事务最低的隔离级别，允许当前事务读取未被其他事务提交的变更。

这种隔离级别会产生脏读，不可重复读和幻读。

产生脏读场景：A事务读取一个字段，但是这个字段被另外一个事务更新却未提交，

再次读取该字段时如果另外一个事务回滚则出现了脏读现象(读到的数据与第一次，

数据库中的数据都不同)。

产生不可重复读场景：A事务读取一个字段，但是这个字段被另外一个事务更新并提交，

再次读取该字段值不一样则出现了不可重复读现象(同一个事务中，不能保证读取的字段值相同)。

产生幻读场景：A事务读取一个字段集合，但是这个表被另外一个事务更新并提交(如插入了几行)，

再次读取该表可能会多几行则出现了幻读现象。

③ READ_COMMITTED（读取已提交)
保证一个事务修改的数据提交后才能被另外一个事务读取，

另外一个事务不能读取该事务未提交的数据。可以避免脏读，但不可重复读和幻读的现象仍然可能出现。

不可重复读

A事务读取一个字段，但是这个字段被另外一个事务更新并提交，再次读取该字段值不一样则出现了不可重复读现象(同一个事务中，不能保证读取的字段值相同)。

```
举例就是对于一个数A原来是50，然后提交修改成100，这个时候另一个事务在A提交修改之前，
读取到了A是50，刚读取完，A就被修改成100了，这个时候另一个事务再进行读取发现A就突然变成100了
```

幻读

读取一个字段，但是这个表被另外一个事务更新并提交(如插入了几行)，再次读取该表可能会多几行则出现了幻读现象。

④ REPEATABLE_READ（可重复读)
确保事务可以多次从某行记录的一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更新。这种事务隔离级别可以防止脏读，不可重复读，但是可能出现幻读。

它除了保证一个事务不能读取另一个事务未提交的数据外，还保证了在一个事务过程，

读取的数据不会发生变化(即使数据库中的数据在该事务过程中发生了变化)。

```
//如下代码所示，可重复读意味着两次读取字段A值相同！

public void test(){
    //开启事务
    //读取字段A；
    //此时数据库中A发生了变化；
    //读取字段A；
    //提交事务；
    //关闭事务；
}
```

幻读

读取一个字段集合，但是这个表被另外一个事务更新并提交(如插入了几行)，

再次读取该表可能会多几行则出现了幻读现象。

⑤ SERIALIZABLE :（可串行化）
在并发情况下和串行化的读取的结果是一致的，没有什么不同。这是花费最高代价但是最可靠的事务隔离级别，事务被处理为顺序执行。除了防止脏读，不可重复读外，还避免了幻读。但性能十分低下！

⑥ 什么是脏读、不可重复读和幻读？
脏读: 对于两个事务 T1, T2。T1 读取了已经被 T2 更新但还没有被提交的字段。之后, 若 T2 回滚, T1读取的内容就是临时且无效的，也就是脏数据。

不可重复读:对于两个事务 T1, T2。 T1 读取了一个字段, 然后 T2 更新了该字段。之后, T1再次读取同一个字段, 值就不同了。

幻读:事务T1读取一条指定where条件的语句，返回结果集。此时事务T2插入一些新记录，恰好满足T1的where条件。然后T1使用相同的条件再次查询，结果集中可以看到T2插入的记录，这些多出来的新纪录就是幻读。

不可重复读和幻读的区别：

不可重复读重点是在update，即事务前后对比**特定数据内容**的修改。而幻读是insert和delete，即事务前后**数据结果集**的对比。

Oracle数据库支持READ COMMITTED(默认) 和 SERIALIZABLE这两种事务隔离级别。所以Oracle不会出现脏读。

MySQL 支持 4 种事务隔离级别：READ_UNCOMMITTED（读取未提交），READ_COMMITTED（读取已提交），REPEATABLE_READ（可重复读-默认）和SERIALIZABLE （可串行化）。

Oracle 默认使用的是READ_COMMITTED。MySQL默认事务隔离级别为 REPEATABLE_READ。