数据库-原创手记-慕课网

SQL运行顺序

1. from语句
2. where语句
3. select语句
4. order by语句

数据库三范式

1NF：列的原子性, 列不可以再拆分
	比如：地址信息拆分成 省 市 县
2NF：表中有主键, 非主键要完全依赖主键
	比如：订单编号+商品编号做为联合主键 商品名, 商品价格却只依赖商品编号. 所以不符合第二范式. 将商品编号拆分成一个单表. 订单编号和商品编号之间做个映射.
3NF：非主键之间不能相互依赖
	比如：订单中有客户编码, 但是客户姓名, 客户年龄只依赖客户编号. 所以不符合第三范式. 将客户编号拆分成新表, 订单中只依赖客户编号即可.

数据库事务

1、为什么有事务?
	当执行一系列SQL语句时，有的语句成功，有的语句失败，那么将导致数据库表中的数据非常混乱，所以要有一种机制保证不发生这种情况.
2、事务有哪些特性?
	ACID
	原子性：要么都执行成功，要么失败回滚.
	一致性：A和B一共有1000元，无论A向B怎么转账最终加起来都应该有1000元.
	隔离性：事务在并发的执行时，可能会对同一个资源修改，所以需要将事务的执行隔离开.
	持久性：事务提交后，对数据库的修改就是永久的.

如何产生事务并发问题

表结构如下：
CREATE TABLE `account` (
  `id` int(5) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `balance` decimal(10,0) unsigned zerofill DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
插入两条数据:
insert into account (id,name,balance) values (1,'sicwen',1000);
insert into account (id,name,balance) values (2,'liey',1000);

1. READ UNCOMMITTED级别有(脏读，不可重复读，幻读)问题
-------------------------------脏读----------------------------------
			事务A								事务B
	START TRANSACTION;						START TRANSACTION;
	select * from account;
	                                        update account set balance=balance+100 where name='sicwen';
	select * from account;
	事务A读取到了事务B中未提交的数据，读取到了脏数据.
2. READ COMMITTED解决脏读问题
-----------------------------不可重复读---------------------------------
			事务A								事务B
	START TRANSACTION;						START TRANSACTION;
	select * from account;
                                            update account set balance=balance+100 where name='sicwen';
                                            commit;
	select * from account;
	事务A读取到了事务B中提交的数据，在重复读取数据时，两次结果不一致.
3. REPEATABLE READ解决不可重复读问题
-----------------------------幻读--------------------------------- 
			事务A								事务B
	START TRANSACTION;						START TRANSACTION;
	select * from account;
                                            insert into account (id,name,balance) values (3,'sl',2000)
                                            commit;
	select * from account;
	insert into account (id,name,balance) values (3,'sl',2000)
	事务B插入一条数据,然后事务A查询发现没有该数据,事务A也插入一条数据这时报错已经存在这条数据了,但是刚才读时没有这条数据啊? 难道出现幻觉了?
4. SERIALIZABLE解决幻读问题

事务级别的其他问题

REPEATABLE READ级别与READ COMMITTED冲突了
READ COMMITTED可以读取到commit后的数据，REPEATABLE READ不可以读取到commit后的数据.
InnoDB提供了这样的机制，在默认的可重复读的隔离级别里，可以使用加锁读去查询最新的数据。
如下两种方式：
select * from account lock in share mode;
select * from account for update;

数据库中锁机制

在多个事务并发执行，如下语句时：
start TRANSACTION;
select * from account;
update account set balance=balance+1 where name = 'liey';
commit;
事务A先执行了update操作，然后事务B在执行update操作，这时事务B的update操作会被阻塞. 直到事务A提交或回滚.

在上面的操作过程中有几个锁的使用,
在select时会读取快照.
在update时会加上锁，在锁定期间不能做任何修改操作，直到事务提交或回滚.所以事务A更新之后，B被阻塞.

数据库中的锁

(1)读未提交：select不加锁，可能出现读脏；
(2)读提交(RC)：普通select快照读，锁select(select ... for update) /update /delete 会使用记录锁，可能出现不可重复读；
(3)可重复读(RR)：普通select快照读，锁select /update /delete 根据查询条件情况，会选择记录锁，或者间隙锁/临键锁，以防止读取到幻影记录；
(4)串行化：select隐式转化为select ... in share mode，会被update与delete互斥；

不同存储引擎有使用不同的锁机制

MyIMAS(表级锁)
	隐式加锁：
		在执行查询语句（select）前，会自动给涉及的所有表加读锁
		在执行更新操作（update、delete、insert等）前，会自动给涉及的表加写锁。这个过程并不需要用户干预，因此不需要直接用	lock table命令给MyISAM表显式加锁。
	显示加锁：
		// 当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。
		// 其他线程的读、写操作都会等待，直到锁被释放为止。
		// test表将会被锁住，另一个线程执行select * from test where id = 3;将会一直等待，直到test表解锁
		LOCK TABLE test WRITE; 
InnoDB(行级锁)
	隐式加锁：对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁
	显示加锁：
		共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
		独占锁（X) ：SELECT * FROM table_name WHERE ... FOR UPDATE

应用程序中解决并发

悲观锁：认为一定会有其他人来改变数据，所以在操作数据之前我先获取锁
		使用select...for update来获取锁.
乐观锁
		不是用for update而是采用记录数据的版本号，如果这次的数据与开始记录的数据一致就说明 这期间数据没有被改变。
		如果版本号不一致说明数据被改变了，更新失败，数据回滚.

数据库表的设计：

数据类型int后的长度，表示显示的长度，用如下属性设置字段：
CREATE TABLE `test` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `i1` int(3) unsigned zerofill DEFAULT NULL,
    `i2` int(6) unsigned zerofill DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
就会看到用0来补位了.