最近看了数据分析岗位的招聘，几乎都对 SQL 有要求。大二的《数据库原理》老师讲得很扎实，学得也不错，奈何时间长用得少还给老师了，找了 Ben Forta 的《MySQL 必知必会》补补课。

SQL 检索数据时的逻辑和 dplyr 类似，只是语法上的差异，不在话下。看书的时候更多关注数据库的基础知识和性能改善。至于数据库安全管理、维护等超出了目前数据分析所涉及的领域，因此只是简单地浏览，留个印象。

记录一些知识点，常看常记，努力内化为常识。

SQL 基础

• 应该总是定义主键，以便以后的数据操纵和管理。
• 主键的最好习惯：
  • 不更新主键列中的值；
  • 不重用主键列的值；
  • 不在主键列中使用可能会更改的值。

检索数据

• 关系数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出的数据有意义。如果不明确排序查询结果，返回的数据顺序没有特殊意义，可能是数据被添加到表中的顺序，也可能不是，只要返回相同数目的行，就是正常的。
• SQL 语句不区分大小写。许多 SQL 开发人员喜欢对所有 SQL 关键字使用大写，而对所有列和表名使用小写，这样使代码更易于阅读和调试。
• SQL 语句一般返回原始的、无格式的数据。数据的格式化是一个表示问题，而不是一个检索问题。因此，表示一般在显示该数据的应用程序中规定。一般很少使用实际检索出的原始数据。
• DISTINCT 关键字应用于所有列而不仅是前置它的列。
• MySQL 在执行匹配时默认不区分大小写，所以 fuses 与 Fuses 匹配。
• BETWEEN 匹配包含起始值和结束值。
• 空值检查：WHERE column IS NULL。

通配符和正则表达式

• 在搜索串中，% 表示任何字符出现任意次数，_ 只匹配单个字符而不是多个字符。
• % 不能匹配值为 NULL 的行。
• 通配符搜索所花时间更长，不要过度使用通配符。
• 把通配符置于搜索模式的开始处，搜索起来是最慢的。
• 使用 \\ 转义，\\. 表示查找 .。
• 用 SELECT 测试正则表达式：SELECT 'hello 110' REGEXP '[0-9]'; 返回 1。

汇总数据

• COUNT(*) 不忽略 NULL；如果指定列名，指定列为 NULL 的行被 COUNT() 函数忽略。

分组数据

• 如果分组列中具有 NULL 值，则 NULL 将作为一个分组返回。
• 使用 WITH ROLLUP 关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值。
• WHERE 过滤行，在数据分组前进行过滤；HAVING 过滤分组，在数据分组后进行过滤。

联结表

• 相同数据出现多次绝不是一件好事，这是关系数据库涉及的基础。关系表的设计就是要保证把信息分解成多个表，一类数据一个表。各表通过某些常用的值（即关系设计中的关系（relational））互相关联。
• 可伸缩性（scale）：能够适应不断增加的工作量而不失败。
• 笛卡尔积（cartesian product）：由没有联结条件的表关系返回的结果为笛卡尔积。检索出的行数将是第一个表的行数乘以第二个表的行数。
• ANSI SQL 规范首选 INNER JION 语法。
• 表别名只在查询执行中使用。与列别名不同，表别名不返回到客户机。

组合查询

• UNION 中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。
• UNION 从查询结果中自动去除了重复的行。如果想返回所有匹配行，可使用 UNION ALL。

全文本搜索

• 数据库引擎 MyISAM 支持全文本搜索，InnoDB 不支持。
• 在使用全文本搜索时，MySQL 不需要分别查看每个行，不需要分别分析和处理每个词。MySQL 创建指定列中各词的一个索引，搜索可以针对这些词进行。这样，MySQL 可以快速有效地决定哪些词匹配（哪些行包含它们），哪些词不匹配，它们匹配的频率等等。
• 除非使用 BINARY 方式，否则全文本搜索不区分大小写。
• 全文本搜索返回以文本匹配的良好程度排序的数据，具有较高等级的行先返回（因为这些可能是真正想要的）。
• 文本中词靠前的行的等级值比词靠后的行的等级值高。
• 使用查询扩展 WHERE MATCH(note_text) AGAINST('anvils' WITH QUERY EXPANSION)。

插入数据

• 一般不要使用没有明确给出列的列表的 INSERT 语句。使用列的列表能使 SQL 代码继续发挥作用，即使表结构发生了变化。
• 如果数据检索是最重要的，可以在 INSERT 和 INTO 之间添加关键字 LOW_PRIORITY，指示 MySQL 降低 INSERT 语句的优先级，这也适用于 UPDATE 和 DELETE 语句。
• INSERT SELECT 不关心 SELECT 返回的列名，它使用的是列的位置。

更新和删除数据

• 使用 UPDATE 和 DELETE 时一定要细心，不要省略 WHERE 子句，否则会变动表中所有行。
• 在对 UPDATE 或 DELETE 语句使用 WHERE 子句前，应该先用 SELETE 进行测试，保证它过滤的是正确的记录，以防编写的 WHERE 子句不正确。

创建和操纵表

• NULL 是没有值，它不是空串。
• SELECT last_insert_id() 返回最后一个 AUTO_INCREMENT 值。
• 几个需知的数据库引擎：
  • InnoDB 是一个可靠的事务处理引擎，它不支持全文本搜索；
  • MyISAM 是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理；
  • MEMORY 在功能等同于 MyISAM，但由于数据储存在内存（而不是磁盘)中，速度很快（适合临时表）。
• 外键不能跨引擎。
• ALTER TABLE 一种常见用途是定义外键。
• 复杂的表结构更改一般需要手动删除过程，涉及以下步骤：
  1. 用新的列布局创建一个新表；
  2. 使用 INSERT SELECT 从旧表复制数据到新表。如有必要，可使用转换函数和计算字段；
  3. 检索包含所需数据的新表；
  4. 重命名旧表（如果确定，可以删除它）；
  5. 用旧表名字重命名新表；
  6. 根据需要，重新创建触发器、存储过程、索引和外键。

视图

• 视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。
• 更新一个视图将更新其基表。如果对视图增加或删除行，实际上是对其基表增加或删除行。
• 一般，应该将视图用于检索而不用于更新。

存储过程

• 通过存储过程限制对基础数据的访问减少了数据讹误的可能。
• 使用存储过程有 3 个主要的好处，即简单、安全、高性能。
• MySQL 变量都必须以 @ 开始。
• SHOW CREATE PROCEDURE 显示存储过程。

游标

• 游标（cursor）是存储在 MySQL 服务器上的数据库查询，它不是一条 SELECT 语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以更具需要滚动或浏览其中的数据。
• 游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。
• 不像多数 DBMS，MySQL 游标只能用于存储过程和函数。

触发器

• 触发器是 MySQL 响应 DELETE、INSERT 或 UPDATE 语句而自动执行的一条 MySQL 语句（或位于 BEGIN 和 END 语句之间的一组语句）。
• 只有表才支持触发器，视图和临时表不支持。
• 触发器不能更新或覆盖。为修改一个触发器，必须先删除它，然后再重新创建。

事物处理

• 事务处理（transaction processing）可以用来维护数据库的完整性，它保证成批的 MySQL 操作要么完全执行，要么完全不执行。
• 并非所有引擎都支持事务处理，MyISAM 不支持明确的事务处理管理，InnoDB 支持。
• 事务处理的几个术语：
  • 事务（transaction） 指一组 SQL 语句；
  • 回退（rollback） 指撤销指定 SQL 语句的过程；
  • 提交（commit） 指将未存储的 SQL 语句结果写入数据库表；
  • 保留点（savepoint） 指事务处理中设置的临时占位符（place-holder），可以对它发布回退（与回退整个事务处理不同）。

改善性能

• 一般来说，存储过程执行得比一条一条地执行其中的各条 SQL 语句快。
• 必须索引数据库表以改善数据检索的性能。如果有一些表用于收集数据且不经常被搜索，在有必要之前不要索引它们（索引可根据需要添加和删除）。
• 通过使用多条 SELECT 语句和连接它们的 UNION 语句来替代复杂的 OR 条件，可以极大地改善性能。
• LIKE很慢，最好用 FULLTEXT。
• 数据库是不断变化的实体。一组优化良好的表可能以后会面目全非。由于表的使用和内容的更改，理想的优化和配置也会改变。
• 最重要的规则就是，每条规则在某些条件下都会被打破。