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.*

表连接

1. 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

2. 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

3. 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采用自动提交，如果不是显式的开启一个事务，则每个查询都作为一个事务。

事务的基本要素:

1. 原子性（Atomicity）：事务开始后所有操作，要么全部完成，要么全部不完成，不可能停滞在中间环节。事务执行过程中出错，会回滚（Rollback）到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体
2. 一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到
3. 隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账
4. 持久性（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的坑

原文链接 (opens new window)

需要避免的问题

1. 视图

个人感觉：视图用不好会导致视图泛滥、重复关联，造成查询效率极低的情况。

看到别人说的很有道理：原文链接 (opens new window)

1. 做个一般的业务系统，完全可以不用视图，如果你要用视图，只能说明你表没设计好，那就继续理解业务优化表设计去（有的人可能要喷这句，但实际情况就是要连表多表的SQL都是复杂的后台数据综合分析系统才需要，这样的SQL整个系统也要不了几句，再说现在不是有google的分析工具么，已经可以代替大部分自己要做的分析，只要网页嵌个js代码就行）。
2. 视图一般都只有查询能力，和真正的表并不一样。而许多公司底层DAO都是BaseDao，然后各个表去继承的形式，这样就导致如果你有视图，肯定也要有dao，然而一继承这个BaseDao，就会暴露出视图根本就不支持的增删改能力。而为了DAO层统一性，很多情况下都是不允许你自己去写DAO的，你继承又暴露出你根本不支持的功能。你现在用的爽（因为你知道，所有只用了查），而后来者对此一无所知，他就按照公司的习惯，同时你也暴露出来增删改，他就去增删改，结果报错。
3. 我们这次要改表，可我并不知道哪些视图依赖此表，好吧，我改表了，我的业务完成了；你的视图依赖了此表，但这是你很久前就做好的模块，你也不管。结果呢？大家都以为没事一切ok，当真的用到你做的模块时，却报错。你大吼“这是我以前做的，都上线3个月了，一直好好的。而这次谁都没改，怎么可能报错，反正不是我的错”，那谁的错？只是因为你的视图依赖的表结构改了，而你没有重新执行此视图的代码。