数据库基础理论知识

一数据库事务

1什么是事务

事务是数据库并发控制的基本单位，可以看作是一系列SQL语句的集合

事务必须要么全部执行成功，要么全部执行失败（回滚）

比如从A转账到B 100元，A-100,B+100如果B执行失败，A的操作不能凭空减少100，故进行回滚操作

2事务的ACID四个特性：

◆事务的原子性(Atomicity)是指一个事务要么全部执行，要么不执行。也就是说一个事务不可能只执行了一半就停止了。比如你从银行取钱，这个事务可以分成两个步骤(1)存折减款，(2)拿到现金。不可能存折钱少了，而钱却没拿出来。这两步必须同时完成，要么就都不完成。

◆事务的一致性(consistency)是指事务的运行并不改变数据库中数据的一致性。例如，完整性约束了a+b=10，一个事务改变了a，那么b也应该随之改变。

◆事务的独立性(或隔离性Isolation:隔离性指的是事务间是相互独立的，不会互相影响)是指两个以上的事务不会出现交错执行的状态。因为这样可能会导致数据不一致。

◆事务的持久性(Durability)是指事务运行成功以后，就系统的更新是永久的。不会无缘无故的回滚。

3事务的并发控制可能产生哪些问题？

如果不对事务进行并发控制，可能会产生四种异常情况

◆幻读( phantom read):个事务第二次查出现第一次没有的结果

◆非重复读( nonrepeatable read):一个事务重复读两次得到不同结果

◆脏读( dirty read):一个事务读取到另一个事务没有提交的修改

◆丢失修改( ost update):并发写入造成其中一些修改丢失

4为了解决并发控制异常，定义了4种事务隔离级别

◆读未提交( read uncommitted):别的事务可以读取到未提交改变

◆读已提交( read committed):只能读取已经提交的数据

◆可重复读 (repeatable read):同一个事务先后查询结果一样

◆串行化( Serializable):事务完全串行化的执行，隔离级别最高，执行效率最低（锁住一个事务，执行完再释放）A插入了数据，B看不到插入的数据，无法在相同得位置插入信息

5如何解决高并发场景下的插入重复?

高并发场景下，写入数据库会有数据重复问题

◆使用数据库的唯一索引

◆使用队列异步写入   

◆使用 redis等实现分布式锁

6.乐观锁和悲观锁[需要根据响应速度、冲突频率、重试代价来判断使用哪一种]

◆悲观锁是先获取锁再进行操作。一锁二查三更新 select for update

◆乐观锁先修改，更新的时候发现数据已经变了就回滚(check and set)

乐观锁一般通过版本号或者时间戳实现

7共享锁(读锁)：在同一时间段内，多个用户可读取同一个资源，读取过程中不会发生任何变化

排他锁（写锁）：在任何时刻，只能有一个用户可读取同一个资源，当进行写锁时会阻塞其他的读锁或写锁操作。

8锁颗粒

开销、加锁速度、死锁、粒度、并发性能

• 表锁：开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低
• 行锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高
• 页锁：开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般
• 表锁更适用于以查询为主，只有少量按索引条件更新数据的应用；行锁更适用于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用。（PS：由于BDB已经被InnoDB所取代，我们只讨论MyISAM表锁和InnoDB行锁的问题）
• https://www.cnblogs.com/jpfss/p/8890250.html

表锁：一种开销最小的锁策略（一个表一个锁），主键不唯一

行锁：一种开销最大的锁策略

二三大范式

1第一范式（1NF）：无重复的列

每一行只包含一个实例信息，每个属性不可再分，不满意1NF就不是关系数据库

2第二范式2NF：属性完全依赖于主键

基于1NF，每一行被唯一区分，实体的属性完全依赖于主关键字

3第三范式3NF

基于2NF，要求不包含已在其他表中存在存在的非主关键字信息

三  SQL注入攻击

所谓SQL注入式攻击，就是攻击者把SQL命令插入到web表单的输入域或页面请求的查询字符串中，欺骗服务器执行恶意的SQL命令。在某些表单中，用户输入的内容直接用来构造(或者影响)动态SQL命令，或作为存储过程的输入参数，这类表单特别容易受到SQL注入式攻击。

防范SQL注入式攻击闯入并不是一件特别困难的事情，只要在利用表单输入的内容构造SOL命令之前，把所有输入内容过滤一番就可以了。过滤输入内容可以按多种方式进行。

四.SQL语句例题

1内连接和外连接【两个表按照字段拼在一起】

◆内连接( I NNER JOIN):两个表都存在匹配时，才会返回匹配行；将左表和右表能够关联起来，类似于表的交集，的数据连接后返回：select * from A inner join B on a.id=b.id

◆外连接(LEFT/RIGHT JOIN):返回一个左/右表的行，即使另一个没有匹配,没有则用NULL填充

◆全连接( FULL JOIN):只要某一个表存在匹配就返回。完整外部联接返回左表和右表中的所有行。

 左连接

select A.id as a_id,B.id as b_id,A.val as a_val,B.val as b_val from left join B on A.id=B.id

a_id	b_id	a_val	b_val
1	1	ab	ab
2	NULL	ab	NULL