数据库
# SQL标准
# SQL发展简史
SQL发展简史如下：
- 1986年，ANSI X3.135-1986，ISO/IEC 9075:1986，SQL-86
- 1989年，ANSI X3.135-1989，ISO/IEC 9075:1989，SQL-89
- 1992年，ANSI X3.135-1992，ISO/IEC 9075:1992，SQL-92（SQL2）
- 1999年，ISO/IEC 9075:1999，SQL:1999（SQL3）
- 2003年，ISO/IEC 9075:2003，SQL:2003（SQL4）
- 2011年，ISO/IEC 9075:200N，SQL:2011（SQL5）
| 年份 | 名字 | 别名 | 注释 |
|---|---|---|---|
| 1986 | SQL-86 | SQL-87 | ANSI首次标准化 |
| 1989 | SQL-89 | FIPS 127-1 | 小修改，增加了integrity constraint |
| 1992 | SQL-92 | SQL2, FIPS 127-2 | 大修改，成为现代SQL的基础 |
| 1999 | SQL:1999 | SQL3 | 增加了正则表达式匹配、递归查询（传递闭包）、数据库触发器、过程式与控制流语句、非标量类型(arrays)、面向对象特性。在Java中嵌入SQL(SQL/OLB)及其逆(SQL/JRT) |
| 2003 | SQL:2003 | 增加XML相关特性(SQL/XML)、window functions、标准化sequences、自动产生值的列。对SQL:1999的新特性重新描述其内涵。 | |
| 2006 | SQL:2006 | 导入/导出XML数据与SQL数据库。XQuery | |
| 2008 | SQL:2008 | 在cursor之外的ORDER BY语句。INSTEAD OF触发器。TRUNCATE语句。FETCH子句 | |
| 2011 | SQL:2011 | 增加时态数据(PERIOD FOR)。增强了window functions与FETCH子句 | |
| 2016 | SQL:2016 | 增加行模式匹配、多态表函数、JSON。 | |
| 2019 | SQL:2019 | 增加了第15部分，多维数组（MDarray类型和运算符）。 |
# 语法要素
- 子句 是语句和查询的组成成分。（在某些情况下，这些都是可选的。）
- 表达式 可以产生任何标量值，或由列和行组成的数据库表
- 谓词 给需要评估的SQL三值逻辑（3VL）（true/false/unknown）或布尔真值指定条件，并限制语句和查询的效果，或改变程序流程。
- 查询 基于特定条件检索数据。这是SQL的一个重要组成部分。
- 语句 可以持久地影响纲要和数据，也可以控制数据库事务、程序流程、连接、会话或诊断。
- 语句终结符 SQL语句也包括分号（";"）语句终结符。尽管并不是每个平台都必需，但它是作为SQL语法的标准部分定义的。
- 无意义的空白 在SQL语句和查询中一般会被忽略，更容易格式化SQL代码便于阅读
# 各标准语法
# MERGE
MERGE用来合并多个表的数据。它结合了INSERT和UPDATE元素。它是在SQL:2003标准中定义的；在那之前，一些数据库也以不同的语法提供了相似的功能，又是叫做“upsert”。
MERGE INTO table_name --要处理的表
USING table_reference ON (condition) --参照的表
-- 如果记录匹配，就更新目标表的匹配行
WHEN MATCHED THEN
UPDATE SET column1 = value1 [, column2 = value2 ...]
-- 如果要处理表没有参照表上的记录，则插入
WHEN NOT MATCHED THEN
INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...])
-- 如果要处理表的记录在参照表上不存在，则删除
WHEN NOT MATCHED BY SOURCE THEN DELETE
-- 用OUTPUT输出刚刚变动过的数据 $action AS [ACTION]
OUTPUT $action, Inserted.*, Deleted.*
# 表连接
- INNER JOIN
-- 86标准
SELECT a.id, a.name, b.id, b.name, c.id, c.name
FROM a, b, c
WHERE a.id = b.id
AND a.id = c.id
-- 92标准
SELECT a.id, a.name, b.id, b.name, c.id, c.name
FROM a
INNER JOIN b
ON a.id = b.id
INNER JOIN c
ON a.id = c.id;
// Make sure to add code blocks to your code group
- LEFT JOIN
-- 86标准
SELECT a.id, a.name, b.id, b.name, c.id, c.name
FROM a, b, c
WHERE a.id = b.id(+) /*b表和a表进行左连接，以a表为准，称为左连接。注意哦，(+)是放在右边的*/
AND a.id = c.id(+) /*c表和a表进行左连接，以a表为准*/
--------------------------
SELECT a.id, a.name, b.id, b.name, c.id, c.name
FROM a, b, c
WHERE a.id = b.id(+)
AND b.id = c.id(+)
-- 92标准
SELECT a.id, a.name, b.id, b.name, c.id, c.name
FROM a
LEFT JOIN b ON a.id = b.id
LEFT JOIN c ON a.id = c.id;
---------------------
SELECT a.id, a.name, b.id, b.name, c.id, c.name
FROM a
LEFT JOIN b ON a.id = b.id
LEFT JOIN c ON b.id = c.id;
// Make sure to add code blocks to your code group
- RIGHT JOIN
-- 86标准
SELECT a.id, a.name, b.id, b.name, c.id, c.name
FROM a, b, c
WHERE a.id(+) = b.id /*b表和a表进行左连接，以a表为准，称为左连接。注意哦，(+)是放在右边的*/
AND a.id(+) = c.id /*c表和a表进行左连接，以a表为准*/
--------------------------
SELECT a.id, a.name, b.id, b.name, c.id, c.name
FROM a, b, c
WHERE a.id(+) = b.id
AND b.id(+) = c.id
-- 92标准
SELECT a.id, a.name, b.id, b.name, c.id, c.name
FROM a
RIGHT JOIN b ON a.id = b.id
RIGHT JOIN c ON a.id = c.id;
---------------------
SELECT a.id, a.name, b.id, b.name, c.id, c.name
FROM a
RIGHT JOIN b ON a.id = b.id
RIGHT JOIN c ON b.id = c.id;
// Make sure to add code blocks to your code group
# 事务相关
# 事务
事务保证一组原子性的操作，要么全部成功，要么全部失败。一旦失败，回滚之前的所有操作。MySql采用自动提交，如果不是显式的开启一个事务，则每个查询都作为一个事务。
事务的基本要素:
- 原子性（Atomicity）：事务开始后所有操作，要么全部完成，要么全部不完成，不可能停滞在中间环节。事务执行过程中出错，会回滚（Rollback）到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体
- 一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到
- 隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账
- 持久性（Durability）：事务完成后，该事务所对数据库所作的更改将被保存到数据库之中，不能回滚。即使系统出现故障，也能够保持。
隔离级别控制了一个事务中的修改，哪些在事务内和事务间是可见的。四种常见的隔离级别：
- 未提交读（Read UnCommitted），事务中的修改，即使没提交对其他事务也是可见的。事务可能读取未提交的数据，造成脏读。
- 提交读（Read Committed），一个事务开始时，只能看见已提交的事务所做的修改。事务未提交之前，所做的修改对其他事务是不可见的。也叫不可重复读，同一个事务多次读取同样记录可能不同。
- 可重复读（RepeatTable Read），同一个事务中多次读取同样的记录结果时结果相同。
- 可串行化（Serializable），最高隔离级别，强制事务串行执行。
# 索引相关
# 索引
索引包含一个或多个列的值。MySql只能高效的利用索引的最左前缀列。索引的优势：
- 减少查询扫描的数据量
- 避免排序和零时表
- 将随机IO变为顺序IO （顺序IO的效率高于随机IO）
B-Tree
使用最多的索引类型。采用B-Tree数据结构来存储数据（每个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的遍历）。B-Tree索引适用于全键值，键值范围，键前缀查找，支持排序。
B-Tree索引限制：
- 如果不是按照索引的最左列开始查询，则无法使用索引。
- 不能跳过索引中的列。如果使用第一列和第三列索引，则只能使用第一列索引。
- 如果查询中有个范围查询，则其右边的所有列都无法使用索引优化查询。
哈希索引
只有精确匹配索引的所有列，查询才有效。存储引擎会对所有的索引列计算一个哈希码，哈希索引将所有的哈希码存储在索引中，并保存指向每个数据行的指针。
哈希索引限制：
- 无法用于排序
- 不支持部分匹配
- 只支持等值查询如=，IN（），不支持 <>
优化建议点
- 注意每种索引的适用范围和适用限制。
- 索引的列如果是表达式的一部分或者是函数的参数，则失效。
- 针对特别长的字符串，可以使用前缀索引，根据索引的选择性选择合适的前缀长度。
- 使用多列索引的时候，可以通过 AND 和 OR 语法连接。
- 重复索引没必要，如（A，B）和（A）重复。
- 索引在where条件查询和group by语法查询的时候特别有效。
- 将范围查询放在条件查询的最后，防止范围查询导致的右边索引失效的问题。
- 索引最好不要选择过长的字符串，而且索引列也不宜为null。
# 什么时候索引会失效
在写SQL想到一个问题, 为什么会认为union all的效率比or的效率【原因可能是or会导致索引失效，假如此列非索引列，union all的效率是不是和or近似】
索引失效的10种场景
- 不满足最左匹配原则
- 使用了select *
- 索引列上有计算
- 索引列用了函数
- 字段类型不同
- like左边包含%
- 列对比
- 使用or关键字
- not in和not exists
- order by的坑
# 需要避免的问题
# 1. 视图
个人感觉：视图用不好会导致视图泛滥、重复关联，造成查询效率极低的情况。
看到别人说的很有道理：原文链接 (opens new window)
- 做个一般的业务系统，完全可以不用视图，如果你要用视图，只能说明你表没设计好，那就继续理解业务优化表设计去（有的人可能要喷这句，但实际情况就是要连表多表的SQL都是复杂的后台数据综合分析系统才需要，这样的SQL整个系统也要不了几句，再说现在不是有google的分析工具么，已经可以代替大部分自己要做的分析，只要网页嵌个js代码就行）。
- 视图一般都只有查询能力，和真正的表并不一样。而许多公司底层DAO都是BaseDao，然后各个表去继承的形式，这样就导致如果你有视图，肯定也要有dao，然而一继承这个BaseDao，就会暴露出视图根本就不支持的增删改能力。而为了DAO层统一性，很多情况下都是不允许你自己去写DAO的，你继承又暴露出你根本不支持的功能。你现在用的爽（因为你知道，所有只用了查），而后来者对此一无所知，他就按照公司的习惯，同时你也暴露出来增删改，他就去增删改，结果报错。
- 我们这次要改表，可我并不知道哪些视图依赖此表，好吧，我改表了，我的业务完成了；你的视图依赖了此表，但这是你很久前就做好的模块，你也不管。结果呢？大家都以为没事一切ok，当真的用到你做的模块时，却报错。你大吼“这是我以前做的，都上线3个月了，一直好好的。而这次谁都没改，怎么可能报错，反正不是我的错”，那谁的错？只是因为你的视图依赖的表结构改了，而你没有重新执行此视图的代码。
编辑 (opens new window)