关于SQL你不得不知道的几件事

最近在做SQL抽象相关的开发，深入了解了一下这个大学本科就开设课程并介绍的语言，发现他是那种乍一看很平淡，越深入了解越觉得斯国一的语言。（其实还是上学时候没认真听课吧==！）SQL，又叫结构化查询语言，属于声明式编程范式一员，相比这个解释，我更想给他贴上逻辑语言。因为这门语言就是为抽象逻辑而生，这也是为什么尽管诞生了很多年（1973年），现在的挑战者任然没能撼动他的地位而且更多的是支持SQL，四个字，简洁好用！

以数据分析经典入门的超市表为例子：

###########订单表##################
CREATE TABLE `订单` (
  `行 ID` int(11) NOT NULL COMMENT '行ID别名',
  `订单 ID` varchar(255) DEFAULT NULL COMMENT '订单ID别名',
  `订单日期` date DEFAULT NULL,
  `发货日期` date DEFAULT NULL,
  `邮寄方式` varchar(255) DEFAULT NULL,
  `客户 ID` varchar(255) DEFAULT NULL,
  `客户名称` varchar(255) DEFAULT NULL,
  `细分` varchar(255) DEFAULT NULL,
  `城市` varchar(255) DEFAULT NULL,
  `省/自治区` varchar(255) DEFAULT NULL,
  `国家` varchar(255) DEFAULT NULL,
  `地区` varchar(255) DEFAULT NULL,
  `产品 ID` varchar(255) DEFAULT NULL,
  `类别` varchar(255) DEFAULT NULL COMMENT '子类别别名',
  `子类别` varchar(255) DEFAULT NULL,
  `产品名称` varchar(255) DEFAULT NULL,
  `销售额` float DEFAULT NULL,
  `数量` int(11) DEFAULT NULL,
  `折扣` float DEFAULT NULL,
  `利润` float DEFAULT NULL,
  PRIMARY KEY (`行 ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
###########退货表##################
CREATE TABLE `退货` (
  `订单 ID` varchar(255) DEFAULT NULL,
  `退回` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
###########销售表##################
CREATE TABLE `销售表` (
  `城市` varchar(100) DEFAULT NULL,
  `省` varchar(100) DEFAULT NULL,
  `地区` varchar(100) DEFAULT NULL,
  `套数` int(11) DEFAULT NULL,
  `单价` double(11,0) DEFAULT NULL,
  `总价` double(11,2) DEFAULT NULL,
  `城市ID` int(11) NOT NULL DEFAULT '0',
  `日期` datetime DEFAULT NULL,
  PRIMARY KEY (`城市ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

当你想知道2014年的各个省份销售额和利润总和是什么样子并且按照利润总和排序并且你觉得年初定的一个亿小目标，你又不想看销售额总和小于一个亿的省份，一句标准的SQL就产生了

SELECT `省/自治区`, sum(`销售额`) as '总销售额',sum(`利润`) as '总利润'
FROM `订单`
GROUP BY  `省/自治区`
WHERE YEAR(`订单日期`) = 2014
HAVING sum(`销售额`) < 100000000
ORDER BY  sum(`利润`) DESC

现在让我们来看一下SQL的基本语法结构吧,在一层SQL里可能会出现如下：

From
ON
JOIN
WHERE
GROUP BY
WITH CUBE or WITH ROLLUP
HAVING
SELECT ***
DISTINCT
ORDER BY
TOP or LIMIT

而一层SQL是指以上语法是可以嵌套在from或者join里面的，即from或join里面又可以有类似上面的结构出现。 不过，你可能好奇，我最先写的不是SELECT嘛，为什么FROM会出现在第一个而SELECT出现在HAVING的后面去了？没错，我上面的顺序其实是按照SQL执行的顺序来写的。不信？查看数据库SQL的执行计划，以MySQL为例子，结果是：

+----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+
|  1 | SIMPLE      | 订单  | ALL  | NULL          | NULL | NULL    | NULL | 10076 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+

咳咳，当然这篇文章不是教你怎么解读MySQL执行计划的。简单来说就是MySQL先找到FROM的那张订单表，然后全表扫描了10076行，然后按照将满足where条件的放入临时表V1，按照GROUP BY的字段对放入临时表的记录分组并且计算产生聚合函数列（sum(销售额)等）将结果放入另一张临时表V2，从V2临时表执行HAVING筛选条件把满足条件的记录放入临时表V3,最后再从V3选择SELECT的几列产生V4,并对V4表按ORDER排序后返回。这下明白了吧，所以就会有

....
SELECT * FROM(
SELECT * FROM(
SELECT * FROM(
SELECT `省/自治区`, sum(`销售额`) as '总销售额',sum(`利润`) as '总利润'
FROM `订单`
GROUP BY  `省/自治区`
WHERE YEAR(`订单日期`) = 2014
HAVING sum(`销售额`) < 100000000
ORDER BY  sum(`利润`) DESC) t1) t2) t3

这种情况无论你嵌套多少层，其实对数据库来说并没有很大区别。。。。

看完了SQL执行顺序，再来说说TABLE JOIN吧。数据库表设计是一门很深的学问，以数据分析和数仓的角度来看一般可以分为事实表、维度表和聚合表。 简单来说事实表就是最细的事件或行为的记录，可以是超市的每一笔订单，可以是股票的每一笔交易记录等等。事实表包含可以与维度表联系的字段（外键）。而维度表呢，就是对事实表记录的特性汇总。可以是超市商品的种类，可以是股票的分类，也可以是客户的支付手段等等。事实表是就是罗马，他通过条条大路通往各地（维度表）。上面例子中超市是事实表，而退货和销售是维度表。而聚合表是当你分析的模型已经确订的时候，可以通过预先聚合结果集复用查询结果。常用的场景就是电商物流，KPI达标情况等等。

数据库的表关联方式一般有三类：

1.OUTER JOIN,分为LEFT OUTER JOIN、RIGHT OUTER JOIN和FULL OUTER JOIN
2.INNER JOIN
3.CROSS JOIN

具体到什么时候用什么呢， 1.OUTER JOIN是以某一张表全!部!记录为准，用某张表全部记录的关联列值去另外一张表同样的那一列的值匹配，如果有相同的则另外那张表的列就会有相等的那条记录的值（这就可能出现一对多情况，结果会比全部记录还要多），如果没有，则另外那一张表的列的值为空。具体到LEFT OUTER JOIN就是以左表表的全部记录为准，RIGHT OUTER JOIN以右边表的记录为准，而FULL OUTER JOIN分别先遍历左表，在遍历右表，只要关联的那列值在对面表有出现，结果就会返回。（假设一对一的话结果集就是两表之和） 以订单和退货为例子，两表通过订单 ID关联：

+-------+-----------------+------------+----------+----------+------------+----------+--------+------+-----------+------+------+----------------------+----------+--------+----------------------------+--------+------+------+---------+
| 行 ID | 订单 ID         | 订单日期   | 发货日期 | 邮寄方式 | 客户 ID    | 客户名称 | 细分   | 城市 | 省/自治区 | 国家 | 地区 | 产品 ID              | 类别     | 子类别 | 产品名称                   | 销售额 | 数量 | 折扣 | 利润    |
+-------+-----------------+------------+----------+----------+------------+----------+--------+------+-----------+------+------+----------------------+----------+--------+----------------------------+--------+------+------+---------+
| 1     | US-2014-1357144 | 2014-04-28 |          | 二级     | 曾惠-14485 | 曾惠     | 公司   | 杭州 | 浙江      | 中国 | 华东 | 办公用-用品-10004670 | 办公用品 | 用品   | Fiskars 剪刀, 高速         |        |    2 |  0.4 | -60.704 |
| 2     | CN-2014-1973789 | 2014-06-16 |          | 标准级   | 许安-10165 | 许安     | 消费者 | 内江 | 四川      | 中国 | 西南 | 办公用-信封-10001735 | 办公用品 | 信封   | GlobeWeis 搭扣信封, 带窗口 |        |    2 |    0 |   42.56 |
| 3     | CN-2014-1973790 | 2014-06-16 |          | 标准级   | 许安-10165 | 许安     | 消费者 | 内江 | 四川      | 中国 | 西南 | 办公用-装订-10001593 | 办公用品 | 装订机 | Cardinal 孔加固材料, 回收  |        |    2 |  0.4 |     4.2 |
| 4     | US-2014-3017568 | 2014-12-10 |          | 标准级   | 宋良-17170 | 宋良     | 公司   | 镇江 | 江苏      | 中国 | 华东 | 办公用-用品-10004966 | 办公用品 | 用品   | Kleencut 开信刀, 易握柄    |        |    4 |  0.4 | -27.104 |
+-------+-----------------+------------+----------+----------+------------+----------+--------+------+-----------+------+------+----------------------+----------+--------+-----------------

+-----------------+------+
| 订单 ID         | 退回 |
+-----------------+------+
| US-2014-1357144 | 是   |
| CN-2014-1973789 | 是   |
+-----------------+------+

可以看出有两条订单记录是退货了，以销售表 LEFT OUTER JOIN 订单表，结果就是4条，并且在订单列的基础上增加退货表的两列。。其中两条记录退回为是，两条为空。 如果这时候退货表出现重复的某一列订单ID记录，即：

+-----------------+------+
| 订单 ID         | 退回 |
+-----------------+------+
| US-2014-1357144 | 是   |
| CN-2014-1973789 | 是   |
| US-2014-1357144 | 是1   |
+-----------------+------+

这时候结果就是5条而不是4条。RIGHT OUTER JOIN同理，来看看FULLOUTER JOIN结果是啥样，当退货表是2条时候，结果是4+2=6条，当退货表是如上3条的时候，结果是4+3+1=8条。额外加的那条相当于从左表算的时候算了一次，即左表关联后结果是5条，右表算的时候算了一次，右表关联完后结果是5+3=8条。那条记录算了两次。

2.INNER JOIN就比较简单啦，就是相当于两者集合取INNER JOIN的列值得交集，要两者都有结果才会出现。具体到上面就是订单ID同时在销售表和退货表出现的记录才会出现在结果集里。

3.CROSS就是两者做笛卡尔乘积，即a表记录数*b表记录数的结果。

4.表关联是一个运算量很大的行为，所以尽可能减少FROM里面的的结果集是一种常见的SQL优化思路。比如对于以CN开头的这一类订单，因为是中国地区成交，我想看2014年的中国订单单笔销售额和最后有没有退货，SQL如下

SELECT * FROM `订单` t1 
LEFT JOIN `退货` t2
ON t1.`订单 ID` = t2.`订单 ID`
WHERE t1.`订单 ID` LIKE 'CN%' and t2.`订单 ID` LIKE 'CN%'

当你把where条件下推到每一个子表里面时候，

SELECT * FROM (SELECT * FROM `订单` WHERE `订单 ID` LIKE 'CN%') t1 
LEFT JOIN (SELECT * FROM `退货` WHERE `订单 ID` LIKE 'CN%' ) t2
ON t1.`订单 ID` = t2.`订单 ID`

在大数据量的时候会会快很多，当然这部分工作其实大多数数据的SQL执行引擎都会帮你做，比如对于第一条SQL,MySQL执行计划就是：

+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                          |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |  296 | Using where                    |
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 8929 | Using where; Using join buffer |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+

默认帮你把外层where条件下放到两张子表里面。

说了这么多，该结尾了，现在有个问题，假如我想知道各个省份下销售额最多的城市及其对应的总销售额和该城市销售额与该省总销售额百分比，SQL应该怎么写呢？？哈哈，他的结果应该是这样子的:

+-----------+--------+--------------------+--------------------------+
| 省/自治区 | 城市   | 城市总销售额       | 城市销售额占省/自治区总销售额百分比 |
+-----------+--------+--------------------+--------------------------+
| 上海      | 上海   |  582450.5663032532 |                        1 |
| 云南      | 中枢   | 136537.82898712158 |     0.024869070745674113 |
| 内蒙古    | 东胜   |  71381.54872894287 |      0.01631485697610554 |
| 北京      | 北京   |  376814.8989562988 |      0.15349606053623108 |
| 吉林      | 三岔子 |  180801.2858352661 |     0.006613441399961467 |
| 四川      | 乐山   | 123775.93193626404 |     0.019297650535071886 |
| 天津      | 咸水沽 | 471183.50846099854 |      0.14280712507978507 |
| 宁夏      | 石嘴山 | 27292.328186035156 |      0.15652591368971552 |
| 安徽      | 亳州   |  137112.3957901001 |     0.009478121506109626 |
| 山东      | 东村   | 206316.26403427124 |    0.0021315037140081676 |
| 山西      | 临汾   | 171042.62032318115 |     0.033626481723326614 |
| 广东      | 东山   |  290086.3412399292 |     0.003978808282959184 |
| 广西      | 八步   | 118154.07675552368 |      0.02085756283598706 |
| 新疆      | 和田   | 21811.972412109375 |     0.044131082859414056 |
| 江苏      | 东台   |  89802.63641357422 |      0.00416703112512734 |
| 江西      | 九江   |  68907.30033874512 |      0.03226060185869783 |
| 河北      | 丰润   | 127557.11521911621 |     0.007012079296323767 |
| 河南      | 义马   |  138357.0167236328 |    0.0057889736313880245 |
| 浙江      | 上虞   | 108758.94358444214 |     0.008814608360180574 |
| 海南      | 三亚   | 60359.235706329346 |      0.13990905729678477 |
| 湖北      | 丹江口 | 229902.65191078186 |     0.009906834969526053 |
| 湖南      | 上梅   | 119747.48852348328 |     0.006620984350616023 |
| 甘肃      | 兰州   |   78771.8962059021 |      0.06277194342627467 |
| 福建      | 三明   |  144801.9429512024 |     0.018911920180069326 |
| 西藏      | 拉萨   |  5578.859909057617 |       0.2785202070823814 |
| 贵州      | 安顺   |  32679.97582244873 |       0.0377745194031589 |
| 辽宁      | 丹东   | 239248.80223464966 |     0.008672468184119193 |
| 重庆      | 万县   |  275639.7839393616 |      0.10884813610796205 |
| 陕西      | 余下   | 226679.59979248047 |      0.04952708309881174 |
| 青海      | 西宁   | 49863.379943847656 |                        1 |
| 黑龙江    | 七台河 |  205688.2310409546 |    0.0028848696819578634 |
+-----------+--------+--------------------+--------------------------+
31 rows in set

具体怎么实现的呢，有想法的童鞋可以想一想具体SQL是啥，具体先卖个关子，敬请期待有数下个版本上线的详细级别表达式功能~~

本文来自网易实践者社区，经作者唐雕龙授权发布。