并发一致性问题

1.丢失修改

​ 一个事务的更新操作被另一个事务更新操作替换。

事务修改数据时必须加X锁，事务结束才释放锁，可解决丢失修改问题。

2.读脏数据

​ 在不同事务下，当前事务可以读到另外事务未提交数据（写后回滚）。

读取数据时候必须加S锁，读取完马上释放S锁，可解决脏读。

3.不可重复读

​ 在一个事务内多次读取同一数据集合，过程中第二个事务对其进行修改，导致第一个事务两次读取数据不一致。

读取数据时必须加S锁，直至事务结束才能释放S锁，可解决不可重复读。

4.幻读

​ 为不可重复读的一种情况，事务一读取某个范围内数据，事务二在此范围内插入新数据，造成两次读取数据不一致。

产生并发不一致主要是破坏了事务的隔离性，可通过封锁实现并发控制来解决。数据库管理系统提供事务的隔离级别来处理并发一致性问题

封锁

封锁粒度主要有行级锁和表级锁两种。

封锁类型有读写锁和意向锁两种。

读写锁

1. 互斥锁，即X锁，又称写锁。
2. 共享锁，即S锁，又称读锁，加锁期间可读取，不能修改。

意向锁

为更方便多粒度封锁提出，在存在行级锁和表级锁情况下，减少加锁时对每一行都要检测的消耗

在原有X/S锁基础上引入IX/IS两种表锁，用法为：

1. 一个事务在获取某个数据行对象的S锁前，必须得到表的IS锁或更强锁。
2. 一个事务在获取某个数据行对象的X锁前，必须得到表的IX锁。

且任意IS/IX之间都是兼容的，因为其仅表示想要对表加锁，而非真正加锁。

各种锁的兼容关系表如下：

锁名	X	IX	S	IS
X	0	0	0	0
IX	0		0
S	0	0
IS	0

隔离级别

隔离级别	脏读	不可重复读	幻读
未提交读	×	×	×
提交读	√	×	×
可重复读	√	√	×
可串行化	√	√	√

多版本并发控制

多版本并发控制MVCC是Mysql的InnoDB存储引擎实现隔离级别的具体方式，主要实现提交读和可重复读两种隔离级别。MVCC不能解决幻读问题。

基本原理

实际场景中读操作多于写操作，所以将读写分开处理。

MVCC利用多版本思想，事务的修改操作（删除，插入，更新）等会为数据行新增一个版本快照，写操作更新最新的版本快照，读操作读取旧版本快照。

读取时，根据快照的事务版本号，来判断数据行快照是否可用。

关系数据库设计理论

根据数据表的函数依赖，来判断不符合范式的关系，主要可能产生以下四种异常：

1. 冗余数据

2. 修改异常

3. 删除异常

4. 插入异常

   范式

为了解决异常，提出范式。

1. 第一范式：属性不可分。
2. 第二范式：每个非主属性完全函数依赖于键码，可分解。
3. 第三范式：非主属性不传递函数依赖于键码。

属性不可分

SQL语法

数据库创建与使用

CREATE DATABASE test;
USE test；

创建表

CREATE TABLE mytable (
	id INT NOT NULL aaa, --int类型，不为空
	col1 VARCHAR(45) NULL,
	PRIMARY KEY (`id`));

修改表

增加列：

ALTER TABLE mytable
ADD col CHAR(20);

删除列：

ALTER TABLE mytable
DROP COLUMN col;

删除表：

DROP TABLE mytable;

插入

普通插入：

INSERT INTO mytable(id, col1)
VALUES(val1, val2);

插入检索出的数据：

INSERT INTO mytable1(col1, col2)
SELECT col1, col2
FROM mytable2;

更新

UPDATE mytable
SET col = val
WHERE id = 1;

删除

DELETE FROM mytable
WHERE id = 1

使用更新和删除时，可以先SELECT语句进行测试，防止错误删除，一定要带Where子句

查询

关键字DISTINCT：作用于所有列，完全相同的行仅出现一次。

SELECT DISTINCT col1, col2
FROM mytable;

关键字LIMIT：限制返回行数，可设两个参数，第一个为起始行，默认0；第二个为返回总行数

返回前五行：

SELECT *
FROM mytable
LIMIT 5;

返回3-5行：

SELECT *
FROM mytable
LIMIT 2, 3;

排序

升序ASC,降序DESC

SELECT *
FROM mytable
ORDER BY col1 DESC, col2 ASC;

通配符

• % 匹配 >=0 个任意字符；
• _ 匹配 ==1 个任意字符；
• [ ] 可以匹配集合内的字符，例如 [ab] 将匹配字符 a 或者 b。用脱字符 ^ 可以对其进行否定，也就是不匹配集合内的字符。
• 使用LIKE来进行通配符匹配

SELECT *
FROM mytable
WHERE col LIKE '[^AB]%'; -- 不以 A 和 B 开头的任意文本

函数

HAVING可与合计函数一起使用筛选

SELECT AVG(DISTINCT col1) AS avg_col
FROM mytable;

连接

连接用于连接多个表，使用 JOIN 关键字，并且条件语句使用 ON 而不是 WHERE。

内连接

SELECT A.value, B.value
FROM tablea AS A INNER JOIN tableb AS B
ON A.key = B.key;

自然连接

内连接提供连接的列，而自然连接自动连接所有同名列。

SELECT A.value, B.value
FROM tablea AS A NATURAL JOIN tableb AS B;

外连接

左外连接，全外连接，右外连接

组合查询

SELECT col
FROM mytable
WHERE col = 1
UNION
SELECT col
FROM mytable
WHERE col =2;

SQL语句练习

在SQLZOO进行