S2_day_06 sql视图,事务,索引(转载)

事务,视图,索引的基本概念

一、事务：

   1.概念：是单个逻辑单元执行的一系列操作（一个事务中有多个sql语句），这个操作作为一个整体一起提交，要么执行，要么都不执行，多个事务操作是一个不可分割的逻辑单元。
   2.事务的特性：
     （1）原子性（A）：一个事务中的所有的操作不可再分割，保持原子性，他们要么都执行，要么都不执行。
     （2）一致性（C）：数据保持一致
     （3）隔离性（I）：并发事务（多个事务）之间相互独立、隔离
     （4）永久性（D）：事务完成之后，数据永久有效
   3.事务的使用：
      （1）开始事务 begin transaction（将一个业务的操作包含在开始事务中）
      （2）提交事务 commit transaction（如果所有的事务都没有错误，提交事务）
      （3）回滚事务 rollback transaction（如果有一个错误的话，回滚事务）
   4.事务分类：
      （1）显示事务：用begin transaction 开始的事务，最常用的
      （2）隐士事务：打开隐士事务之后，系统自动控制事务
      （3）自动提交事务，用于单条sql语句中
二、视图
   1.概念：是一个虚拟表，包含单个表的部分数据或多个表的总和数据组成的虚表，他的结构和数据是基于对数据表的查询基础上。
    注：（1）视图不存储数据，实际数据存储在数据表中
        （2）一个数据库表可以创建多个视图
   2.视图的作用：
        （1）筛选数据行
        （2）保护敏感数据
        （3）降低数据库的复杂度
        （4）将多个物理数据库抽象为一个逻辑数据库
   3.创建视图的注意事项：
        （1）select 语句不能包含order by
        （2）不能有into
        （3）不引用临时表或表变量
  4.创建视图：
       create view 视图名 as select 语句
  5.删除视图: drop view 视图名
三、索引
   1.索引的概念：数据库中编排数据的内部方法
   2.索引页的概念：数据库存放索引数据的一种方式
   3.作用：提高数据库的检索速度，改善数据的性能  
   4.分类：
     （1）唯一索引：不允许有重复的行数据，不允许为null
     （2）主键索引：特殊的唯一索引，不允许有null
     （3）聚集索引：索引顺序和数据物理存放顺序一致
     （4）非聚集索引：索引顺序和物理存放顺序不一致
   5.创建索引的原则：
     （1）在频繁搜索的列上
     （2）经常查询的列
     （3）经常排序和分组的列
     （4）进行连接的列（主键和外键）
     （5）在小字节的列上
   6.不推荐创建索引：
     （1）只有少量几个值得列上
     （2）只有几行数据的表上
     （3）在大字段上
   7.使用索引的注意事项：
     （1）查询中尽量不要用*
     （2）where后有多个条件的时候，有索引的放在前面
     （3）order by后尽量不要用表达式
     （4）定期的对索引页进行碎片整理
   8.创建和删除索引
     create index 索引名 on 表名（列名）

      drop index 表名.索引名

事务的隔离级别

脏读（Dirty Reads）

　　一个事务开始读取了某行数据，但是另外一个事务已经更新了此数据但没有能够及时提交。这是相当危险的，因为很可能所有的操作都被回滚。

不可重复读（Non-repeatable Reads）

　　一个事务对同一行数据重复读取两次，但是却得到了不同的结果。它包括以下情况：

　　（1） 事务T1读取某一数据后，事务T2对其做了修改，当事务T1再次读该数据时得到与前一次不同的值。

　　（2） 幻读（Phantom Reads）：事务在操作过程中进行两次查询，第二次查询的结果包含了第一次查询中未出现的数据或者缺少了第一次查询中出现的数据（这里并不要求两次查询的SQL语句相同）。这是因为在两次查询过程中有另外一个事务插入数据造成的。

 

　　为了避免上面出现的几种情况，在标准SQL规范中，定义了4个事务隔离级别，不同的隔离级别对事务的处理不同。

　　● 未授权读取（读未提交）（Read Uncommitted）：允许脏读取，但不允许更新丢失。如果一个事务已经开始写数据，则另外一个数据则不允许同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现。

　　● 授权读取（读提交）（Read Committed）：允许不可重复读取，但不允许脏读取。这可以通过“瞬间共享读锁”和“排他写锁”实现。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。

　　● 可重复读取（Repeatable Read）：禁止不可重复读取和脏读取，但是有时可能出现幻影数据。这可以通过“共享读锁”和“排他写锁”实现。读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。

　　● 序列化（Serializable）：提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。

　　隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed，它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、虚读和第二类丢失更新这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。

 

视图的使用规则

创建视图：

Create view vw1 as

Select st.学号,st.姓名,st.所属院系 from student as st,course as co,score as sc

Where co.课名=’心理学’ and sc.考试成绩>80 and st.学号=sc.学号 and co.课号=sc.课号

这样就可以世界查看视图，查看数据了。

 

3.视图的功能

1.简化用户操作

2.能以不同的角度观察同一个数据库

3.对重构数据库提供了逻辑独立性：

利用视图将需要的数据合并或者筛选，但是不影响原表的数据和结构

3.对机密数据提供安全保护：

 可以建立不同的视图对用不同的用户，以达到安全的目的。

建立一个表如图所示：

SQL SERVER学生表student

建立一个视图，实验一下：

Create view vw1 as

Select 学号,姓名,所属院系

From student

Where 课名=’软件工程’ and 所属院系=’计算机’

运行语句建立视图：

建立的一个成功的视图vw1

 

建立视图的语法：

Create view 视图名称[(字段1) (字段2) (字段3)…]

AS

Select 查询语句

[with  check  option]

参数：[with check  option]可选项，防止用户对数据插入、删除、更新是操作了视图范围外的基本表的数据。

删除视图的语法：

Drop view 视图名称