数据库封锁协议、转储、范式、存储过程、触发器

野性酷女 2022-12-04 07:34 139阅读 0赞

### 三层封锁协议 ###

**并发操作**带来的三类问题：**丢失修改 、读脏数据 、不可重复读**

要解决以上问题就要对操作加锁，常用的基本锁：**共享锁（S锁或读锁）、排它锁（X锁或写锁）**

具体的加锁方式按照三级封锁协议来参照：

*  **一级封锁协议**  
    事务T要修改数据A时必须加X锁，直到事务T结束才释放锁。
    
    可以解决丢失修改问题，因为不能同时有两个事务对用一个数据进行修改，那么事务的修改就不会被覆
 *  **二级封锁协议**  
    在一级的基础上，要求读取数据A时必须加S锁，读取完马上释放S锁。  
    可以解决读脏数据问题，因为如果一个事务在对数据A进行修改，根据一级封锁协议，会加X锁，那么就不能再加S锁了，也就不会读入数据。
 *  **三级封锁协议**  
    在二级的基础上，要求读取数据A时必须加S锁，直到事务结束了才能释放S锁。  
    可以解决不可重复读的问题，因为读取A时，其它事务不能对A加X锁，从而避免了在读的期间数据发生变化。

### 转储 ###

谁也不敢说自己的系统和硬件不会出问题，为了预防灾难，就要对数据进行备份到其它的盘中，以备以后要对数据进行恢复。根据转储时系统状态的不同，转储可分为 **静态转储** 和 **动态转储**

*  静态转储
    
    当系统中无运行事务时进行转储，转储开始时数据库处于一致状态，转储期间不允许对数据库的任何存取、修改活动。
    
    优点：实现简单。
    
    缺点：降低了数据库的可用性（转储必须等用户事务结束；新的事务必须等转储结束）
 *  动态转储
    
    转储操作与用户事务并发进行，转储期间允许对数据库进行存取或修改。
    
    优点：不用等待正在进行的用户事务结束；不会影响新事务的运行
    
    缺点：不能保证副本中的数据正确有效

> 利用动态转储得到的副本进行故障恢复
> 
> 1、需要把动态转储期间各事务对数据库的修改活动登记下来，建立日志文件
> 
> 2、后备副本加上日志文件才能把数据库恢复到某一时刻的正确状态。

### 三范式 ###

> **元组：** 表中的一行就是一个元组。  
> **码：** 表中可以唯一确定一个元组的某个属性（或者属性组），如果这样的码有不止一个，那么大家都叫候选码，我们从候选码中挑一个出来做老大，它就叫主码。  
> **主属性：** 一个属性只要在任何一个候选码中出现过，这个属性就是主属性。

#### 第一范式（1NF）：属性不可分。 ####

以下的tel字段中有一个内部由两个组成，不符合第一范式

<table> 
 <thead> 
  <tr> 
   <th>name</th> 
   <th>tel</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>zhong</td> 
   <td>13988776655、010－1234567</td> 
  </tr> 
  <tr> 
   <td>wang</td> 
   <td>13612345678</td> 
  </tr> 
 </tbody> 
</table>

修改为以下就符合了

<table> 
 <thead> 
  <tr> 
   <th>name</th> 
   <th>手机</th> 
   <th>座机</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>zhong</td> 
   <td>13988776655</td> 
   <td>010－1234567</td> 
  </tr> 
  <tr> 
   <td>wang</td> 
   <td>13612345678</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

#### 第二范式（2NF）：符合1NF，并且，非主属性完全依赖于码。 ####

*  通过AB能得出C，但是AB单独得不出C，那么说C完全依赖于AB

比如一个表中（A、B）这个组合主键能唯一的确定其它的字段C、D、E、F、G……，这就是完全依赖于码。但是，当组合主键中的一个（比如B）出现了可以确定一个非主属性，就不符合第二范式了，比如学生姓名和课程名作为组合主键可以确定其它属性（任课老师、教材和老师职称等等），但是可以只要课程名就可以确定了教材，这样就出现了不完全依赖（或称部分依赖）。

<table> 
 <thead> 
  <tr> 
   <th>学生姓名</th> 
   <th>课程名</th> 
   <th>任课老师</th> 
   <th>教材</th> 
   <th>老师职称</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>名狱</td> 
   <td>Python</td> 
   <td>龙君</td> 
   <td>Python3程序设计</td> 
   <td>59.50</td> 
  </tr> 
 </tbody> 
</table>

解决的方法——分解表：将教材字段拿出来，加上课程名属性，形成一张新表

<table> 
 <thead> 
  <tr> 
   <th>学生姓名</th> 
   <th>课程号</th> 
   <th>任课老师</th> 
   <th>老师职称</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>名狱</td> 
   <td>Python</td> 
   <td>龙君</td> 
   <td>普通教工</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <thead> 
  <tr> 
   <th>课程名</th> 
   <th>教材</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Python</td> 
   <td>Python3程序设计</td> 
  </tr> 
 </tbody> 
</table>

#### 第三范式（3NF）：符合2NF，并且，消除传递依赖 ####

*  通过A得到B，通过B得到C，但是C得不到B，B得不到A，那么成C传递依赖于A

比如学生姓名和课程名作为组合主键可以确定其它属性（任课老师、教材和老师职称等等），但是出现了一个问题，组合主键确定了任课老师，但是任课老师又能确定了教师职称，然而任课老师不能确定学生姓名和课程号，因为老师教很多的科目，并且老师职称如教授不止一个人又不能确定一个老师，这样形成传递依赖。

> 那么这样有什么问题呢？
> 
>  *  老师从普通教工升级为教授，那么对这张表要进行大量的修改
>  *  新来了一个老师，职称确定是普通教工，但是还未为他分配课程，此时只有一张表，就不能存储老师的信息
>  *  ……
> 
> 解决的方法——分解表

<table> 
 <thead> 
  <tr> 
   <th>学生姓名</th> 
   <th>课程号</th> 
   <th>任课老师</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>名狱</td> 
   <td>Python</td> 
   <td>龙君</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <thead> 
  <tr> 
   <th>课程名</th> 
   <th>教材</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Python</td> 
   <td>Python3程序设计</td> 
  </tr> 
 </tbody> 
</table>

### 存储过程 ###

#### 创建存储过程 ####

create procedure 存储过程名
    	@参数1 数据类型,
    	@参数2 数据类型,
    	……
    	@参数n 数据类型
    	AS
    	begin
    		SQL语句
    	end

create procedure UserLogin
    	@name varchar(20),
    	@pass char(16)
    	as
    	begin
    		select * from admin where name=@name and password=@pass
    	end

#### 调用存储过程 ####

exec UserLogin admin,123456    # 写法一
    exec UserLogin @name=admin,@pass=123456    # 写法二

#### 删除存储过程 ####

drop procedure UserLogin

### 触发器 ###

由数据操作(update、insert、delete)事件满足条件时触发

create trigger 触发器名 before|after 触发事件
    	on 表名 for eachc row
    	begin
    		执行语句
    	end

#### 查看触发器信息 ####

show triggers

#### 删除触发器 ####

drop trigger 触发器名