mysql详解--数据库基本概念

.1、事务

每一种关系数据库都是以事务（Transaction）作为操作的基本单位。在关系型数据库中，对事务操作进行了如下定义：
事务（transaction）是由一系列操作序列构成的程序执行单元，这些操作要么都做，要么都不做，是一个不可分割的工作单位。

2、事务的特性

事务ACID属性，即原子性（Atomicity）、一致性(Consistency)、隔离性（Isolation）、持久性（Durability）。

---

原子性：保证事务中的所有操作全部执行或全部不执行。例如执行转账事务，要么转账成功，要么失败。成功，则金额从转出帐户转入到目的帐户，并且两个帐户金额将发生相应的变化；失败，则两个账户的金额都不变。不会出现转出帐户扣了钱，而目的帐户没有收到钱的情况。
一致性：保证数据库始终保持数据的一致性——事务操作之前是一致的，事务操作之后也是一致的，不管事务成功与否。如上面的例子，转账之前和之后数据库都保持数据上的一致性。
隔离性：多个事务并发执行的话，结果应该与多个事务串行执行效果是一样的。显然最简单（严格）的隔离就是将所有事务都串行执行：先来先执行，一个事务执行完了才允许执行下一个。但这样数据库的效率低下，如：两个不同的事务只是读取同一批数据，这样完全可以并发进行。为了控制并发执行的效果就有了不同的隔离级别。
持久性：持久性表示事物操作完成之后，对数据库的影响是持久的，即使数据库因故障而受到破坏，数据库也应该能够恢复。通常的实现方式是采用日志。

3、事务隔离级别

如果不设置隔离级别，会发生什么事情？（设置隔离级别的必要性）
Lost update（丢失更新）：
两个事务都同时更新一行数据，但是第二个事务却中途失败退出，
导致对数据的两个修改都失效了。

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

Non-repeatable Reads（不可重复读）：
一个事务对同一行数据重复读取两次，但是却得到了不同的结果。

Second lost updates problem（写无效）：
无法重复读取的特例。有两个并发事务同时读取同一行数据，然后其
中一个对它进行修改提交，而另一个也进行了修改提交。这就会造成
第一次写操作失效。

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

---

事务隔离级别（transaction isolation levels）：隔离级别就是系统对事务并发控制的等级。（国际标准化组织）ANSI/ ISO SQL将其分为串行化（SERIALIZABLE）、可重复读（REPEATABLE READ）、读已提交（READ COMMITED）、读未提交（READ UNCOMMITED）四个等级。为了实现隔离级别通常数据库采用锁（Lock）。一般在编程的时候只需要设置隔离等级，至于具体采用什么锁则由数据库来设置。首先介绍四种等级，然后举例解释后面三个等级（可重复读、读已提交、读未提交）中会出现的并发问题。

串行化（SERIALIZABLE）：所有事务都一个接一个地串行执行，这样可以避免幻读（phantom reads）。对于基于锁来实现并发控制的数据库来说，串行化要求在执行范围查询（如选取年龄在10到30之间的用户）的时候，需要获取范围锁（range lock）。如果不是基于锁实现并发控制的数据库，则检查到有违反串行操作的事务时，需要滚回该事务。

可重复读（REPEATABLE READ）：所有被Select获取的数据都不能被修改，这样就可以避免一个事务前后读取数据不一致的情况。但是却没有办法控制幻读，因为这个时候其他事务不能更改所选的数据，但是可以增加数据，因为前一个事务没有范围锁。

读已提交（READ COMMITED）：被读取的数据可以被其他事务修改。这样就可能导致不可重复读。也就是说，事务的读取数据的时候获取读锁，但是读完之后立即释放（不需要等到事务结束），而写锁则是事务提交之后才释放。释放读锁之后，就可能被其他事物修改数据。该等级也是SQL Server默认的隔离等级。

读未提交（READ UNCOMMITED）：这是最低的隔离等级，允许其他事务看到没有提交的数据。这种等级会导致脏读（Dirty Read）。

隔离级别与可能导致的状况：

隔离等级	脏读	不可重复读	幻读
读未提交	YES	YES	YES
读已提交	NO	YES	YES
可重复读	NO	NO	YES
串行化	NO	NO	NO

mysql用户可以用SET TRANSACTION语句改变单个会话或者所有新进连接的隔离级别。它的语法如下：

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

注意：默认的行为（不带session和global）是为下一个（未开始）事务设置隔离级别。如果你使用GLOBAL关键字，语句在全局对从那点开始创建的所有新连接（除了不存在的连接）设置默认事务级别。你需要SUPER权限来做这个。使用SESSION 关键字为将来在当前连接上执行的事务设置默认事务级别。 任何客户端都能自由改变会话隔离级别（甚至在事务的中间），或者为下一个事务设置隔离级别。

你可以用下列语句查询全局和会话事务隔离级别：

SELECT @@global.tx_isolation; 
SELECT @@session.tx_isolation; 
SELECT @@tx_isolation;

4、数据库中的锁

有关于mysql中的锁机制以及相关锁的详细介绍，详见下文
http://blog.csdn.net/wangxiaotongfan/article/details/51367069

5、关系数据库中的“关系”规范

关系模式规范化的基本思想是消除关系模式中的数据冗余，消除数据依赖中的不合适的部分，解决数据插入，删除时发生的异常现象。这就要求关系模式满足一定的条件。我们把关系模式规范化过程中为不同程度的规范化要求设立的不同标准称为范式。由于规范化的程度不同，就产生了不同的范式。
范式分别为1NF，2NF，3NF，BCNF，4NF，5NF，六种范式的关系可表示为
$1NF\supset$$2NF\supset$$3NF\supset$$BCNF\supset$$4NF\supset$$5NF$;
在此我们只研究前面五个范式的情况。

第一范式

如果关系模式R所有的属性均为简单属性，即每个属性都是不可再分的，则R称为第一范式。
不能避免数据冗余，插入异常，删除异常和更新异常。

第二范式(消除部分子函数依赖)

如果关系模式R符合第一范式，且每个非主属性都完全函数依赖于R的主关系键，则称之为第二范式

---

结论

1、从第一范式关系中消除非主属性对主关系的部分函数依赖，则可得到第二范式关系
2、如果R的关系键为单属性，或R的全体属性均为主属性，则R符合第二范式。
2NF规范化是指把1NF关系模式通过投影分解，转化为2NF关系模式的集合。分解时遵循的原则就是“一事一地”，让一个关系只描述一个实体或者实体间的联系。如果多于一个实体或联系，则进行投影分解。

如果关系模式R为第一范式，并且R中每一个非主属性完全函数依赖于R的某个候选键， 则称为第二范式模式。

---

示例

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键、主码。

例如员工信息表中加上了员工编号（emp_id）列，因为每个员工的员工编号是惟一的，因此每个员工可以被惟一区分。

简而言之，第二范式（2NF）就是非主属性完全依赖于主关键字。

所谓完全依赖是指不能存在仅依赖主关键字一部分的属性（设有函数依赖W→A，若存在XW，有X→A成立，那么称W→A是局部依赖，否则就称W→A是完全函数依赖）。如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。

假定选课关系表为SelectCourse(学号, 姓名, 年龄, 课程名称, 成绩, 学分)，关键字为组合关键字(学号, 课程名称)，因为存在如下决定关系：

(学号, 课程名称) → (姓名, 年龄, 成绩, 学分)

这个数据库表不满足第二范式，因为存在如下决定关系：

(课程名称) → (学分)
(学号) → (姓名, 年龄)

即存在组合关键字中的字段决定非关键字的情况。

由于不符合2NF，这个选课关系表会存在如下问题：

(1) 数据冗余：

同一门课程由n个学生选修，”学分”就重复n-1次；同一个学生选修了m门课程，姓名和年龄就重复了m-1次。

(2) 更新异常：

若调整了某门课程的学分，数据表中所有行的”学分”值都要更新，否则会出现同一门课程学分不同的情况。

(3) 插入异常：

假设要开设一门新的课程，暂时还没有人选修。这样，由于还没有”学号”关键字，课程名称和学分也无法记录入数据库。

(4) 删除异常：

假设一批学生已经完成课程的选修，这些选修记录就应该从数据库表中删除。但是，与此同时，课程名称和学分信息也被删除了。很显然，这也会导致插入异常。

把选课关系表SelectCourse改为如下三个表：

学生：Student(学号, 姓名, 年龄)；

课程：Course(课程名称, 学分)；

选课关系：SelectCourse(学号, 课程名称, 成绩)。

这样的数据库表是符合第二范式的， 消除了数据冗余、更新异常、插入异常和删除异常。

另外，所有单关键字的数据库表都符合第二范式，因为不可能存在组合关键字。

第三范式（消除传递依赖）

如果关系模式符合第二范式，且每个非主属性都不传递函数依赖于R的主关系键，则称之为第三范式
3NF规范化是指把2NF的关系模式通过投影分解转化成3NF关系模式的集合；与2NF遵循的原则相同，“一事一地”，让一个关系描述一个实体或实体间的联系。
如果关系模式R是第二范式，且每个非主属性都不传递依赖于R的候选键，则称R为第三范式模式。

满足第三范式（3NF）必须先满足第二范式（2NF）。第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在的员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。

第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。简而言之，第三范式就是属性不依赖于其它非主属性。

所谓传递函数依赖，指的是如果存在”A → B → C”的决定关系，则C传递函数依赖于A。

因此，满足第三范式的数据库表应该不存在如下依赖关系：

关键字段 → 非关键字段x → 非关键字段y

假定学生关系表为Student(学号, 姓名, 年龄, 所在学院, 学院地点, 学院电话)，关键字为单一关键字”学号”，因为存在如下决定关系：

(学号) → (姓名, 年龄, 所在学院, 学院地点, 学院电话)

这个数据库是符合2NF的，但是不符合3NF，因为存在如下决定关系：

(学号) → (所在学院) → (学院地点, 学院电话)

即存在非关键字段”学院地点”、”学院电话”对关键字段”学号”的传递函数依赖。

它也会存在数据冗余、更新异常、插入异常和删除异常的情况，读者可自行分析得知。

把学生关系表分为如下两个表：

学生：(学号, 姓名, 年龄, 所在学院)；

学院：(学院, 地点, 电话)。

这样的数据库表是符合第三范式的，消除了数据冗余、更新异常、插入异常和删除异常。

BC范式
如果关系模式R属于第一范式，且所有的函数依赖X→Y（Y不属于X），决定因素X都包含了R的一个候选键，则称之为属于BC范式。
BCNF具有如下性质：
满足BCNF的关系将消除任何属性（主属性或非主属性）对键的部分函数依赖和传递函数依赖，也就是说，R属于BCNF，那么R也就属于3NF
如果R属于3NF，则R不一定满足BCNF。
该范式解决了原来存在的四个异常问题。

第四范式