[Java学习笔记]数据库中的视图、序列、索引以及事务

1.视图

什么是视图
视图(VIEW)也被称作虚表，即虚拟的表，是一组数据的逻辑表示,其本质是对应于一条SELECT语句，结果集被赋予一个名字，即视图名字。视图本身并不包含任何数据，它只包含映射到基表的一个查询语句，当基表数据发生变化，视图数据也随之变化。

‐‐创建一个简单视图V_EMP_10，来显示部门10中的员工的编码、姓名和薪水：
CREATE VIEW v_emp_10
AS SELECT empno, ename, sal, deptno
FROM emp
WHERE deptno = 10;

‐‐查看视图结构：
DESC v_emp_10;

‐‐查询视图和查询表的操作相同,此时视图的列名，和创建视图时的列名一致，不一定是原列名：
SELECT * FROM v_emp_10;

视图允许哪些操作 ？
视图支持的是DML（数据操作语句），但在以下条件下不允许执行DML操作：

• 1.若视图是由两个以上基本表导出的，则此视图不允许更新
• 2.若视图的字段来自字段表达式或常数，则不允许对此视图执行INSERT和UPDATE操作，但允许执行DELETE
• 3.若视图字段来自组函数，则此视图不允许更新
• 4.若视图定义中含有GROUP BY 子句，则此视图不允许更新
• 5.若视图中含有DISTINCT语句或ROWNUM伪列，则此视图不允许更新
• 6.若视图定义中含有嵌套查询，并且内层查询的FROM子句涉及的表也是导出该视图的基本表，则此视图不允许更新
• 7.一个不允许更新的视图上定义的视图也不允许更新

创建具有CHECK OPTION约束的视图

默认权限下：
INSERT : 可以插入不满足where条件的数据，原表也会更改
UPDATE : 满足where条件的数据可以修改，否则不可以，不会报错
DELETE : 不可以删除不满足where条件的数据，不会报错
WITH CHECK OPTION短语表示，通过视图所做的修改，必须在视图的可见
范围内：（也就是说此时虽然视图先被创建了，where的条件仍然作为限制）
假设INSERT，只可以插入满足where条件的数据，不满足会报错！
假设UPDATE，只有满足where条件的数据可以被更新，不会报错
假设DELETE，只能删除满足where条件的数据，不会报错

总而言之：WITH CHECK OPTION改变的就是INSERT的权限

CREATE OR REPLACE VIEW v_emp_10
AS
SELECT empno id, ename name, sal salary, deptno
FROM emp
WHERE deptno = 10
WITH CHECK OPTION;

‐‐下述DML语句操作失败，因为部门20不在视图可见范围内：
INSERT INTO v_emp_10 VALUES(1008,‘donna’,5500, 20);
UPDATE v_emp_10 SET deptno = 20 WHERE id = 7782;

READ ONLY约束的视图

不可以进行任何插入、更新、删除操作，只能查询，否则会报错
.
.

2.序列

序列(SEQUENCE)是一种用来生成唯一数字值的数据库对象。序列的值由
Oracle程序按递增或递减顺序自动生成，通常用来自动产生表的主键值，是一种
高效率获得唯一键值的途径。

序列的使用

1、创建一个序列，起始数据是100，步进是10：

CREATE SEQUENCE emp_seq
    START WITH 100 INCREMENT BY 10;

2、当序列被创建后，第一个序列值将是100，将要生成的序列号分别是110、
120、130等。
3、序列中有两个伪列：
NEXTVAL：获取序列的下个值
CURRVAL：获取序列的当前值
4、当序列创建以后，必须先执行一次NEXTVAL，之后才能使用CURRVAL。
5. 序列是对象，当然需要我们自行删除——DROP SEQUENCE sequence_name;
.
.
.

3、索引

索引 / INDEX 是一种允许直接访问数据表中某一数据行的树型结构，为了提高查询效
率而引入，是独立于表的对象，可以存放在与表不同的表空间（TABLESPACE）
中。索引记录中存有索引关键字和指向表中各行的指针（地址）。对索引进行的
I/O操作比对表进行操作要少很多。一旦被建立就将被Oracle系统自动维护，查询语句中不用指定使用哪个索引，是一种提高查询效率的机制。

一个表的存储是由两部分组成的，一部分用来存放表的数据页面，另一部分存放索引页面。索引就存放在索引页面上，将某一字段作为索引，那么就会以B+树的形式存储该字段每一行的内容，如果不添加索引，当我们进行查找某一关键字的时候，默认是遍历全表进行查找的，当数据量数以十万记以上后，查找效率非常低下，而对创建过索引的字段进行查找时则在这个时候能够极大地减少查找次数（类似二分查找法）。

索引的使用：

‐‐在EMP表的ENAME列上建立索引：
CREATE INDEX idx_emp_ename ON emp(ename);

‐‐复合索引也叫多列索引，是基于多个列的索引。如果经常在ORDER BY子句中使用job和salary作为排序
CREATE INDEX idx_emp_job_sal ON emp(job, sal);

‐‐当做下面的查询时，会自动应用复合索引idx_emp_job_sal
SELECT empno, ename, sal, job FROM emp
ORDER BY job, sal;

索引使用原则：

• 1、为经常出现在WHERE子句中的列创建索引
• 2、为经常出现在ORDER BY、DISTINCT后面的字段建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致
• 3、为经常作为表的连接条件的列上创建索引
• 4、不要在经常做DML操作的表上建立索引
• 5、不要在小表上建立索引
• 6、限制表上的索引数目，索引并不是越多越好
• 7、删除很少被使用的、不合理的索引

.
.

4.事务

事务是什么

事务是应用程序中一系列严密的操作，所有操作必须成功完成，否则在每个操作中所作的所有更改都会被撤消。一个事务中的一系列的操作要么全部成功，要么一个都不做。

• 原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
• 一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
• 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
• 持久性（Durability）：已被提交的事务对数据库的修改应该永久保存在数据库中。

数据库并发事务存在的问题（脏读、不可重复读、幻读等）

脏读 Dirty Read ：

脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。重点在于读取到了未提交内容

例如：
张三的工资为5000,事务A中把他的工资改为8000,但事务A尚未提交。
与此同时，事务B正在读取张三的工资，读取到张三的工资为8000。
随后，事务A发生异常，而回滚了事务。张三的工资又回滚为5000。
最后，事务B读取到的张三工资为8000的数据即为脏数据，事务B做了一次脏读。

不可重复读 Unrepeatable Read

一个事务A，多次读同一数据。在这个事务还没有结束时,另外一个事务B修改了一次该数据，那么事务A发现两次读取间数据结果不一致，因此称为是不可重复读。重点在于修改

例如：
在事务A中，读取到张三的工资为5000，操作没有完成，事务还没提交。
与此同时，
事务B把张三的工资改为8000，并提交了事务。
随后，
在事务A中，再次读取张三的工资，此时工资变为8000。在一个事务中前后两次读取的结果并不致，导致了不可重复读。

幻读
B事务多次读取数据，在两次间读取过程中A事务添加了数据，B事务的这两次读取出来的集合不一样。幻读的重点在于INSERT或者DELETE： 同样的条件，第 1 次和第 2 次读出来的记录数不一样

第一类丢失更新

A事务撤销时，把已经提交的B事务的更新数据覆盖了。重点在于撤销

例如：

可见：A的撤销将B做的更新直接一起撤销了。

第二类丢失更新

A事务提交的结果覆盖B事务已经提交的数据，造成B事务所做的操作丢失 ，重点在于提交

解决事务的并发问题

为了解决上述问题，数据库通过锁机制解决并发访问的问题。根据锁定对象不同：分为行级锁和表级锁，直接使用锁机制管理是很复杂的，基于锁机制，数据库给用户提供了不同的事务隔离级别，只要设置了事务隔离级别，数据库就会分析事务中的sql语句然后自动选择合适的锁。

• READ COMMITED (读已提交)
  意指的是，在读取时限制只能读取已提交状态下的表，那么就能有效解决脏读，但无法解决不可重复读，因为提交后对事物B该表进行数据的修改仍然会造成事务A读取结果不一致
• Repeatable READ (可重复读)
  意指的是，当我们一个事务开始后，在反复读取过程中加锁不允许其他的事务对数据进行更新，这也正是解决不可重复读修改问题的关键，但在这一级隔离下仍然无法避免幻读问题，因为幻读问题在于多次读取下的表结构发生了改变，可能增/删了数据

注：Mysql的默认隔离级别是Repeatable read，大多数数据库的默认级别就是Read committed，比如Sql Server , Oracle。事务的隔离级别和数据库并发性是成反比的，隔离级别越高，并发性越低，