【备战秋招】数据库

事务

ACID

1. 原子性（Atomicity）
   事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚。
   回滚可以用回滚日志来实现，回滚日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。
2. 一致性（Consistency）
   数据库在事务执行前后都保持一致性状态。在一致性状态下，所有事务对一个数据的读取结果都是相同的。
3. 隔离性（Isolation）
   一个事务所做的修改在最终提交以前，对其它事务是不可见的。
4. 持久性（Durability）
   一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。使用重做日志来保证持久性。

MySQL 默认采用自动提交模式。也就是说，如果不显式使用 START TRANSACTION 语句来开始一个事务，那么每个查询都会被当做一个事务自动提交。

并发一致性问题

• 丢失修改
  T1 和 T2 两个事务都对一个数据进行修改，T1 先修改，T2 随后修改，T2 的修改覆盖了 T1 的修改。

• 读脏数据
  T1 修改一个数据，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据。

• 不可重复读
  T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。

• 幻影读
  T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次
  读取的结果不同。

解决

• *
• 隔离级别

*

MySQL 中提供了两种*粒度：行级锁以及表级锁

1. 读写锁
   排它锁（Exclusive），简写为 X 锁，又称写锁
   共享锁（Shared），简写为 S 锁，又称读锁

2. 意向锁
意向锁在原来的 X/S 锁之上引入了 IX/IS，IX/IS 都是表锁，用来表示一个事务想要在表中的某个数据行上加 X 锁或 S锁。有以下两个规定：
一个事务在获得某个数据行对象的 S 锁之前，必须先获得表的 IS 锁或者更强的锁；
一个事务在获得某个数据行对象的 X 锁之前，必须先获得表的 IX 锁。

隔离级别

• 未提交读（READ UNCOMMITTED）
  事务中的修改，即使没有提交，对其它事务也是可见的。
• 提交读（READ COMMITTED）
  一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。
• 可重复读（REPEATABLE READ）
  保证在同一个事务中多次读取同样数据的结果是一样的。
• 可串行化（SERIALIZABLE）
  强制事务串行执行。
  需要加锁实现，而其它隔离级别通常不需要。

多版本并发控制

多版本并发控制（Multi-Version Concurrency Control, MVCC）是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行，无需使用MVCC。

MVCC 使用到的快照存储在 Undo 日志中，该日志通过回滚指针把一个数据行（Record）的所有快照连接起来。

快照读

select * from table ...;

当前读

select * from table where ? lock in share mode;
select * from table where ? for update;
insert; 
update; 
delete;

MVCC 不能解决幻影读问题，Next-Key Locks 就是为了解决这个问题而存在的。在可重复读（REPEATABLE READ）
隔离级别下，使用 MVCC + Next-Key Locks 可以解决幻读问题。

范式

1. 第一范式 (1NF)
   属性不可分。
2. 第二范式 (2NF)
   每个非主属性完全函数依赖于键码。
   可以通过分解来满足。
3. 第三范式 (3NF)
   非主属性不传递函数依赖于键码。

Mysql

创建数据库

CREATE DATABASE test; 
USE test;

创建表

CREATE TABLE mytable ( 
# int 类型，不为空，自增 
id INT NOT NULL AUTO_INCREMENT, 
# int 类型，不可为空，默认值为 1，不为空 
col1 INT NOT NULL DEFAULT 1,
 # 变长字符串类型，最长为 45 个字符，可以为空 
 col2 VARCHAR(45) NULL, 
 # 日期类型，可为空 
 col3 DATE NULL, 
 # 设置主键为 
 id PRIMARY KEY (`id`));

修改表

#添加列
ALTER TABLE mytable 
ADD col CHAR(20);
#删除列
ALTER TABLE mytable
DROP COLUMN col;
#删除表
DROP TABLE mytable;

插入

#普通插入
INSERT INTO mytable(col1, col2) VALUES(val1, val2);
#插入检索出来的数据
INSERT INTO mytable1(col1, col2) SELECT col1, col2 FROM mytable2;
#将一个表的内容插入到一个新表
CREATE TABLE newtable AS SELECT * FROM mytable;

更新

UPDATE mytable 
SET col = val 
WHERE id = 1;

删除

DELETE FROM mytable WHERE id = 1;
TRUNCATE TABLE mytable;

查询

• DISTINCT
  相同值只会出现一次。它作用于所有列，也就是说所有列的值都相同才算相同。

SELECT DISTINCT col1, col2 FROM mytable;

• LIMIT
  限制返回的行数。可以有两个参数，第一个参数为起始行，从 0 开始；第二个参数为返回的总行数。

SELECT * FROM mytable LIMIT 5;

排序

• ASC 升序
• DESC 降序

过滤

• AND 和 OR 用于连接多个过滤条件。优先处理 AND，当一个过滤表达式决定优先级，使得优先级关系更清晰。
• IN 操作符用于匹配一组值，其后也可以接一个 SELECT 子句，从而匹配子查询得到的一组值。
• NOT 操作符用于否定一个条件。

子查询

SELECT * FROM mytable1 WHERE col1 IN (SELECT col2 FROM mytable2);

连接

• 内连接INNER JOIN
• 自连接
  自连接可以看成内连接的一种，只是连接的表是自身而已。
• 自然连接
  自然连接是把同名列通过等值测试连接起来的，同名列可以有多个。
• 外连接
  外连接保留了没有关联的那些行。分为左外连接，右外连接以及全外连接，左外连接就是保留左表没有关联的行。

事务

START TRANSACTION 
// ... 
SAVEPOINT delete1 
// ... 
ROLLBACK TO delete1 
// ... 
COMMIT