数据库基础

基础语法

1. 增加外键
ALTER TABLE [TABLENAME] WITH CHECK ADD FOREIGN KEY([TemplateID]) REFERENCES [REFERECETABLE]([ID]) ON DELETE CASCADE


2. 修改列
ALTER TABLE [dbo].[SaaS_Template] ALTER COLUMN [TemplateKey] nvarchar(200) NOT NULL


3. 删除约束
ALTER TABLE [dbo].[SaaS_Template] DROP CONSTRAINT XXXX


4. 判断表是否存在
if object_id(N'tablename', N'U') is not null  "U" 表示用户表


5. 判断存储过程是否存在
if exists (select * from sysobjects where id = object_id('存储过程名') and OBJECTPROPERTY(id, 'IsProcedure') = 1


6. 创建带参存储过程
if (exists (select * from sys.objects where name = 'proc_name')) drop proc proc_name
go
create proc proc_name (@id, int, @name varcher(20) out)
as select * from students


7. 左外连接
select A.ID, B.ID from A LEFT JOIN B ON A.ID = B.ID

数据库完整性

数据库完整性指的是数据库中数据的正确性和相容性. 数据库中数据的语法, 语义限制与数据之间的逻辑约束称为静态约束. 
包括:
属性级约束
元组约束
关系约束
关系约束包括: 
实体完整性约束: 主键不能为空
参照完整性约束: 外键存在或者为空
函数依赖约束
统计约束

数据库范式

第一范式: 数据库表的每一列都是不可分割的原子数据项.

第二范式: 要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性.

第三范式: 任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

第三范式（3NF）是第二范式（2NF）的一个子集，即满足第三范式（3NF）必须满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个关系中不包含已在其它关系已包含的非主关键字信息。例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。

两阶段锁协议

 

在数据库系统领域，并发控制机制主要有两种，即锁和多版本机制。

 

1.事务在加锁时有多种方式：

 

一次性锁协议，事务开始时，即一次性申请所有的锁，之后不会再申请任何锁，如果其中某个锁不可用，则整个申请就不成功，事务就不会执行，在事务尾端，一次性释放所有的锁。一次性锁协议不会产生死锁的问题，但事务的并发度不高。

 

两阶段锁协议，整个事务分为两个阶段，前一个阶段为加锁，后一个阶段为解锁。在加锁阶段，事务只能加锁，也可以操作数据，但不能解锁，直到事务释放第一个锁，就进入解锁阶段，此过程中事务只能解锁，也可以操作数据，不能再加锁。两阶段锁协议使得事务具有较高的并发度，因为解锁不必发生在事务结尾。它的不足是没有解决死锁的问题，因为它在加锁阶段没有顺序要求。如两个事务分别申请了A, B锁，接着又申请对方的锁，此时进入死锁状态。

 

树形协议，假设数据项的集合满足一个偏序关系，访问数据项必须按此偏序关系的先后进行。如di->dj，则要想访问dj，必须先访问di。这种偏序关系导出一个有向无环图(DAG)，因此称为树形协议。树形协议的规则有：

树形协议只有独占锁；

事务T第一次加锁可以对任何数据项进行；

此后，事务T对数据项Q的加锁前提是持有Q的父亲数据项的锁；

对数据项的解锁可以随时进行；

数据项被事务T加锁并解锁之后，就不能再被事务T加锁。

树形协议的优点是并发度好，因为可以较早地解锁。并且没有死锁，因为其加锁都是顺序进行的。

缺点是对不需要访问的数据进行不必要的加锁。

 

时间戳排序协议，每个事务都有一个唯一的时间戳，也就是其进入系统的时间。时间戳有大小之分，如果事务Ti比Tj先进入系统，则TS(Ti)<TS(Tj)。对于每个数据项Q，有两个时间戳与其绑定：一个是W-TS(Q)，表示最近一次写数据项Q的事务的时间戳；一个是R-TS(Q)，表示最近一次读数据项Q的事务的时间戳。Thomas协议是对时间戳排序协议的改进，具体内容如下：

若事务Ti发起一个write(Q)，则

如果TS(Ti)<R-TS(Q)，则表明Ti准备写的值还没来得及写入，Q就提前被读取了，所以Ti的write(Q)操作被拒绝，并且事务Ti被回滚。

如果TS(Ti)<W-TS(Q)，表明Ti写的值已过期，比它更新的值已经写到Q上，所以Ti的write(Q)操作被拒绝。

剩下的情况，write(Q)操作被允许。

 

事务在加锁时存在粒度的区别：

如数据库锁，表锁，行锁，字段锁；页锁等。不同的数据库支持的锁粒度不同，BerkeleyDB支持页锁，即对数据项所在的内存页加锁。

 

 

2.多版本机制：

锁是针对集中式数据管理设计的，缺点是降低了事务的并发，并且锁本身有开销。在分布式系统，尤其是读多写少的系统中，采用多版本机制更合适。每个数据项都有多个副本，每个副本都有一个时间戳，根据多版本并发控制协议(MVCC)维护各个版本。

 

MVCC又称为乐观锁，它在读取数据项时，不加锁；在更新数据项时，直到最后要提交时，才会加锁。这与CAS(Compare and Swap)的机制很类似，为了提高并发度，它更新数据前，会将数据拷贝一份，进行一系列修改，并且拷贝的同时，会记录当前的版本号(时间戳)，当修改完毕，即将提交时，再检查此时的版本号是否与刚才记录的一致，如果不一致，则表明数据项被其他事务修改，当前事务的修改被取消。否则，正式提交修改，并增加版本号。

与MVCC相对，基于锁的并发控制机制称为悲观锁，因为它认为其他事务修改自己正在使用的数据项的概率很高，因此对数据项加锁以阻塞其他事务的读和写。