SQL笔记| SQL编程方法

代码要清晰，不要为了“效率”牺牲可读性。 ——布莱恩·克尼汉、普劳格，《编程格调》

表的设计

关系数据库在各类系统中获得广泛支持的最重要原因，就在于它放弃了“地址”这个毫无意义的东西。那么，放弃地址后还剩下什么呢？答案是“名称”。名称既包括用来指代具体东西的固有名称，也包括用来指代概念或者集合的一般名称。对于列、表、所以索引，以及约束，在命名时要做到名副其实。特别注意，如果没有为索引和约束显示地指定名称，DBMS就会自动为之分配随机名称，这也是应该避免的。
命名时允许使用的字符有以下3种：
英文字母
阿拉伯数字
下划线’_’
除此之外，各个数据库实现中可能还加入了$、#、@字符等特殊符号，以及汉字这样多字节的文字，应尽量不要使用，这样的写法可移植性不好，也容易发生难以预期的bug。此外，标准SQL中规定名称的第一个字符应该时英文字母，这一点我们应该遵守。

属性和列

在数据库中，列代表的时某个实体的“属性”，也就是具有一贯性（确定后不可以再改变）。

编程的方针

注释

SQL是声明式语言，即使表达同样的处理过程，逻辑仍然比面向过程语言凝练得多，代码中包含许多处理。因此，SQL很难写出面向过程语言那样“让代码表达”得代码。
SQL很难进行分步执行调试。相比面向过程语言，SQL在分析代码时主要需要进行得时桌面调试（这也是尽可能不使用关联子查询的原因）。
注释的写法有两种。

-- 单行注释
-- 从SomeTable中查询col_1
SELECT col_1 FROM SomeTable ;
/* 多行注释
 从SomeTable中查询col_1 */
SELECT col_1 FROM SomeTable ;

单行注释的写法“–”，多行注释“/* */”这种写法不仅可以用来添加真正的注释，也可以用来注释掉代码，非常方便，可灵活使用。

缩进

代码难以阅读的原因里，也许排在第一位的是没有进行缩进（排在第二位的是没有对长代码划分模块，所有的代码都揉在一起）。

--好的示例
SELECT col_1,
       col_2,col_3,
       COUNT(*)
  FROM tbl_A
 WHERE col_1 = 'a'
   AND col_2 = ( SELECT MAX(col_2)
		   FROM tbl_B
		  WHERE col_3 = 100 )
 GROUP BY  col_1,
	   col_2,
  	   col_3 ;

子查询的代码一定要缩进一层，另外，在SELECT和GROUP BY子句中指定多列时，也需要缩进一层。缩进之后，“子句”的代码块就变得很清晰，更方便阅读。如果不想让代码的行数增加得太多，也可以每行写3列或5列，或者根据具体含义汇总多列并进行换行。

空格

不管用什么语言编程，代码中都需要适当地留一些空格。如果一点都不留，所有的代码都紧凑到一起，代码的逻辑单元就会不明确，也会给阅读的人带来额外负担。

大小写

英文中需要强调某句重要的话时，一般会用斜体或者大写字母。因此在编程中，也有重要的语句使用大写字母，不重要的语句使用小写字母的习惯。
在SQL中，关于应该如何区分使用大小写字母有着不成文的约定：关键字使用大写字母，列名和表名使用小写字母（也有一些人习惯只将单词的首字母大写）。大部分关于SQL的书也是这样写的。
另外，SQL语句中的大写字母和小写字母只是形式上的区别，DBMS的内部处理并不区分大小写。这与Java等编程语言区分保留字的大小写不同，这一点请大家注意。

逗号

在SQL中，分割列或表等元素时需要使用逗号。这里我们以逗号“，”为例，不过“+”“-”等二元运算符，以及AND和OR与这里的逗号一样，起到的都是连接元素的作用，一般会写在行的开头。

SEELCT col_1
      ,col_2
      ,col_3
      ,col_4
  FROM tbl_A ;

这种“前置逗号”的写法有两个好处。第一个好处时删掉最后的哪个col_4后，执行也不会报错。如果按照一般的写法来写，那么删掉最后的col_4后，SELECT子句的结尾会变成“col_3,”，执行就会出错。为了防止出错，还必须手动删除逗号才行。当然，即便是“前置逗号”的写法，如果要删除第一列也会有同样的问题，但是一般来说，需要添加或删掉的大多是最后一列。写在开头的列大部分时候是重要的列，相对而言不会有很大的变动。
第二个好处是，逗号在每行中都出现在同一列，因此使用Emacs等可以进行矩形区域选择的编辑器就会非常方便操作。如果将逗号写在列的后面，那么逗号的列位置就会因列的长度不同而参差不齐。
除了这些好处外，这种写法也有一个缺点，那就是可读性稍微差一些。

不使用通配符

使用通配符“*”指定所有列后，表的全部列都会被选中。虽然这种写法很方便，但最好还是不要这样做。使用通配符后查出的结果中会包含从理论上来说并不需要的列，这不仅会降低代码的可读性，也不利于需要变更。而且，因为结果的格式依赖于列的排列顺序，所以修改表中列的排列顺序，或者添加、修改列就会导致结果的格式发生变好。

SELECT * FROM SomeTable;
SELECT col_1,col_2,col_... FROM SomeTable;

ORDER BY 中不使用列编号

在ORDER BY子句中，我们可以使用列的编号代替实际的列名，作为排序的列来使用。在动态生成SQL等情况下，这是很有用的功能，但是这样的代码可读性很不好。而且，这个功能在SQL-92中已经被列为“未来会被删除的功能”。因此保守一点来讲，最好不要使用它。

SELECT col_1,col_2 FROM SomeTable ORDER BY 1,2;
SELECT col_1,col_2 FROM SomeTable ORDER BY col_1,col_2;

SQL编程方法

SQL是一种有很多方言的语言，各种数据库实现都做了不同的扩展（不管好的坏的）。SQL官方虽然指定了ANSI的标准语法（如SQL：1999、SQL：2003），但并没有完全统一，如果还继续使用方言编程，就很难在不同的DBMS之间移植代码。这些问题只需稍微注意就可避免。
1.不使用依赖各种数据库实现的函数和运算符
依赖数据库实现的函数大多是转换函数或字符串处理函数。不要使用这些函数：DECODE（Oracle)、IF(MySQL)、NVL(Oracle)、STUFF(SQLServer)等，请使用CASE表达式或者COALESCE、NULLIF等标准函数替代它们。此外，像SIGN或ABS、REPLACE这些，虽然标准SQL没有定义它们，但是几乎所有的数据库都实现了它们，所以使用一下也没关系。
让人头疼的是标准SQL中有定义，但是各数据库实现情况不同的功能。例如日期函数EXTRACT，以及用于字符串连接的运算符“||”和POSITION函数。（MYSQL中默认需要使用CONCAT函数，而不使用字符串连接的运算符“||”，可通过修改设置使用“||”）这些函数的使用频率都很高，使用它们会导致代码的可移植性变差。
2.连接操作使用标准语法
在SQL语法中，依赖数据库实现最严重的是连接语句。
最早的时候，连接条件和普通查询条件一样，都写在WHERE子句里。

SELECT * 
 FORM Foo F, Bar B
 WHERE F.state = B.state
  AND F.city = '东京';

标准SQL使用INNER或CROSS等表明连接类型的关键字，连接条件可以使用ON子句分开写。

-- 内连接，而且一眼就能看明白连接条件
SELECT * 
 FORM Foo F INNER JOIN Bar B
   ON F.state = B.state
  WHERE F.city = '东京';

这样写的话，一眼就能看明白连接的类型和条件，代码可读性很好。另外，这样写还有一个好处，即可以防止忘写连接条件时发生非预期的交叉写（通常被称为“意外交叉连接”）。
外连接请使用LEFT OUTER JOIN、RIGHT OUTER JOIN或者FULL OUTER JOIN来写。使用（+）运算符（Oracle）、*=运算符（SQL Server）等依赖数据库实现的写法会降低代码的可移植性，而且表达能力也很有限，所以应尽量避免。标准SQL中允许省略关键字OUTER，但是这个关键字便于我们理解它是外连接而非内连接，所以也还是写上吧。

左连接和右连接

外连接有左连接、右连接和全连接三种类型。其中，左连接和右连接的表达能力是一样的，从理论上讲使用哪个都可以。在代码风格方面，左连接有一个优势：因为一般情况下表头都出现在左边，所以使用左边的表作为主表的话，SQL就能和执行结果在格式上报错一致。这样一来，在看到SQL语句时，我们很容易就能想象出执行结果的格式。

去除关联子查询

人们一直说SQL中存在3个绊脚石：NULL、量化和关联子查询。NULL与量化时深深扎根于SQL的规范，我们只能遵守。可以使用窗口函数代替关联子查询（即所谓的WinMagic），这样可以同时提高可读性和性能，所以我们没有理由不使用窗口函数。
关联子查询不容易编写，调试起来也很困难。这是因为关联子查询无法单独执行，所以只能在大脑中调试。如果使用窗口函数，即使执行子查询，由于它们不是关联的，所以也可以轻松地以最小的单位来执行。这样做符合调试的基本方针“分摊困难”，具有很大的优势。

从FROM子句开始写

SELECT子句时SQL语句中最后执行的部分，写的时候没必要太在意。
SQL中各部分的执行顺序是：FROM -> WHERE -> GROUP BY -> HAVING -> SELECT ( -> ORDER BY)。严格地说，ORDER BY并不是SQL语句的一部分，因此应该排除在外。这样一来，SELECT就是最后才被执行的部分了。
SELECT子句的主要作用是完成列的格式转换和计算，并没有做很多工作。因为它总出现在最开始的位置，所以很容易引起人么的注意，但是在考虑具体逻辑的时候，我们完全可以忽略它。相对而言，WEHRE、GROUP BY和HAVING等起到的作用更重要一些。
因此，如果需要写很复杂的SQL语句，我们可以考虑按照执行顺序从FROM子句开始写，这样在添加逻辑时会更加自然。即使不知道在SELECT子句里写什么，也肯定知道应该在FROM子句中写些什么。
如果把从SELECT子句开始写的方法称为自顶向下法，那么从FROM子句开始写的方法可以称为自底向上法。用C语言等面向过程语言来类比的话，从main函数开始写，逐步完成各个模块的方法时自顶向下法，先写各个模块，再组装到一起的方法就是自底向上法。虽然面向过程语言中的模块和SQL中的子句（clause)并不能完全对等，但是这个道理大家应该都明白。
本节小结：
从长远来看，相比性能，我们最好还是重视可读性。编程时一种沟通手段，每个开发者都有义务保证自己写出的代码表达清晰，具有很好的可读性。