SQL基础及数据库系统原理（一）

SQL基础

增删改查（DML）的使用

1. 增加 INSERT * INTO *
2. 删除 DELETE FROM *
3. 修改 UPDATE * (,) VALUES(,)
4. 查询 SELECT * FROM * WHERE

drop、delete和truncate比较

1. 按内容比。drop删除表，delete和truncate不删除表
2. 按速度比。drop>truncate>delete
3. 按效果比。drop和truncate立马生效，deletle会在事务提交后才生效且如果有触发器会执行触发器。
4. 从应用比。删除表时用drop，清空表时用truncate，清除表中某一项是用delete。

连接查询和分组查询语句

连接查询：主要分为内部连接（inner join）和外部链接（outer join）
SELECT * FROM * INNER JOIN * ON * = *
内部查询用于表内部的查询，外部查询用于表与表之间的查询
分组查询（GROUP BY）：将查询结果按一个或多个进行分组，字段相同为一组
SELECT * FROM * WHERE * GROUP BY *

连接查询和子查询的比较

连接查询能替代子查询的功能，且性能优于子查询。

视图的作用，何时能更新视图

视图是虚拟的表，存储着SQL语句集,对视图的操作和对普通表的操作一模一样
作用：

1. 简化了SQL的使用
2. 增加了安全性，视图只访问SELECT语句涉及的列
   

存储过程、触发器的作用

存储过程类似Java里面的函数，存储着一组SQL语句 delimiter CREATE PROCEDURE
触发器和存储过程几乎一样，触发器在某个表执行UPDATE、INSERT、DELETE时自动执行。CREATE TRIGGER

系统原理

什么是事务，事务的四个特性其原理

事务是一条或一组SQL语句
事务的四个特性（ACID）

1. 原子性(atomicity)： 要么全部提交，要么全部回滚
2. 一致性（consistency）： 数据库的状态在事务执行前后保持一致
3. 隔离性（isolation）： 一个事务执行时，对其他事务不可见，直到提交
4. 持久性（duration）：事务一旦提交，对数据库的修改是永久的

原理：

• 原子性：undo log（回滚日志），当事务开始修改时，innoDB的undo log会开始记录；如果事务执行失败或者调用了回滚，则会根据undo log中记录的信息并将数据回滚到修改前状态。

• 持久性：redo log(重写日志),数据库的读取和写入的时候是在Buffer Pool缓冲里，定期将数据更新到磁盘中，当数据库宕机的时候Buffer Pool没有将数据更新到数据库则会造成数据丢失，因此innoDB提供了redo log，当事务提交以后，先将数据写入redo log，再写入Buffer Pool，如果数据库宕机，重启后会读取redo log，对数据库进行修复，保证了持久性。
  

• 隔离性：主要可以分为两个方面
  a. 写操作对写操作的影响：锁机制保证隔离性（类似同步锁）
  b. 写操作对读操作的影响：MVCC保证隔离性，在同一时刻，不同的事务读取到的数据可能是不同版本的，保证了隔离性

• 一致性：一致性是事务追求的最终目标，一致性的实现既需要数据库层面的保障，也需要应用层面的保障

  并发一致性问题：丢失修改、脏读、不可重复读、幻影读

事务的隔离级别

1. 读未提交：事务中的修改，即使没提交也对其他事务可见
2. 读已提交：事务中的修改，只有提交之后才也对其他事务可见
3. 可重复读：保证同一事务多次读取同一数据的结果一样
4. 序列化：强制事务串行执行，需要加锁（不可能脏读）