深入浅出SQL(9)-SELECT进阶
该系列文章系个人读书笔记及总结性内容，任何组织和个人不得转载进行商业活动！
SELECT进阶：以新视角看你的数据
更精确的查询：
我们已经知道如何使用SELECT和WHERE子句选出数据，但有时候我们需要比这个组合更加精确的选取工具；
本章我们将学习如何给数据排序和归组，以及对查询结果套用数学运算，限制条件等；
创建一个表用以说明本章的内容：
记录四季的饮品及其销售记录；
饮品类型分为春季 夏季 秋季 冬季以及通用5种类型；
字段：id序号 drink_name名称 cost价格 meter容量 burdening_main主配料 season适应季节 sale_date售出日期；
SQL语句示例：
创建表：
mysql> CREATE TABLE drinks_of_four_reason
-> (
-> id INT NOT NULL AUTO_INCREMENT,
-> drink_name VARCHAR(30) NOT NULL,
-> cost DEC(4,2) NOT NULL,
-> meter INT NOT NULL DEFAULT 200,
-> burdening_main VARCHAR(30) NOT NULL DEFAULT 'Water',
-> season CHAR(2) NOT NULL DEFAULT '通用',
-> sale_date DATE NOT NULL,
-> PRIMARY KEY (id)
-> );插入数据：（仅显示一条）
mysql> INSERT INTO drinks_of_four_reason
-> (drink_name,cost,meter,burdening_main,season,sale_date)
-> VALUES
-> ('apple juice',5.5,250,'apple','春季','2018-09-01’),熟悉一下表中的数据：（还有很多）
mysql> SELECT * FROM drinks_of_four_reason;
+----+------------------+------+-------+----------------+--------+------------+
| id | drink_name | cost | meter | burdening_main | season | sale_date |
+----+------------------+------+-------+----------------+--------+------------+
| 1 | apple juice | 5.50 | 250 | apple | 春季 | 2018-09-01 |
| 2 | banana juice | 5.50 | 250 | banana | 夏季 | 2018-09-01 |改装升级：
上边这个表不但记录了我们售卖的饮品，同时还是各个饮品的销售记录；（记录的内容有点多，在下一章我们会学习到如何进行精简）
现在，我们想要对我们的果汁加上一点介绍drink_des，来帮助销售人员更好的介绍我们的饮料；
添加drink_des列：
mysql> ALTER TABLE drinks_of_four_reason
-> ADD COLUMN drink_des VARCHAR(100);为新列添加数据：
这里我们需要思考一下，我们需要针对每种饮料去为它的描述字段赋值；
我们可以这样：
UPDATE drinks_of_four_reason SET drink_des = ‘This is the best one！’ WHERE drink_name = ‘apple juice’;
但是，这样会有个问题：
我们需要针对每一种饮料都写这样一条SQL；
这种大量SQL更新的方式也会带来潜在的问题，后续的SQL对于满足条件的记录可能会再一次更新，尤其是WHERE子句中使用OR链接条件时；
我们需要：
只执行一条SQL，完成上述动作；避免后续的更新覆盖已有更新，即一条记录更新过之后就不要再次更新了；
CASE检查：
避开超大型的UPDATE，我们可以使用更好的CASE检查现有的列的值和条件，来结合UPDATE语句；
如果现有列的值符合条件，我们才会在新列中填入值；
CASE甚至能告诉RDBMS，如果没有记录符合条件时该如何处理；
基本语法：
UPDATE table
SET new_column =
CASE
WHEN column1 = someone
THEN newvalue
WHEN column2 = someone
THEN newvalue
ELSE newvalue
END;
CASE检查 WHEN的条件满足时 THEN的值将生效；检查结束END；
使用CASE表达式来UPDATE：
mysql> UPDATE drinks_of_four_reason
-> SET drink_des =
-> case
-> WHEN drink_name = 'apple juice' THEN 'I am apple juice and I am good!'
-> WHEN drink_name = 'banana juice' THEN 'I am banana juice and I am good!'
-> WHEN drink_name = 'pear juice' THEN 'I am pear juice and I am good!'
-> WHEN drink_name = 'peach juice' THEN 'I am peach juice and I am good!'
-> WHEN drink_name = 'orange juice' THEN 'I am orange juice and I am good!'
-> WHEN drink_name = 'watermelon juice' THEN 'I am watermelon juice and I am good!'
-> WHEN drink_name = 'strawberry juice' THEN 'I am strawberry juice and I am good!'
-> WHEN drink_name = 'apricot juice' THEN 'I am apricot juice and I am good!'
-> WHEN drink_name = 'cherry juice' THEN 'I am cherry juice and I am good!'
-> WHEN drink_name = 'plum juice' THEN 'I am plum juice and I am good!’
-> ELSE ''
-> END;再查看我们的表，我们会发现表中的drink_des列都已经更新了相应的数据；
对于我们刚刚担心的多个条件的场景，可以在WHEN子句中，同样的使用OR来链接多个条件（之前WHERE子句中的条件在这里都可以使用）；
这样满足条件的记录会被赋值为相应CASE检查的值，切后续不会再有重复的操作出现；
ELSE：
ELSE子句是可选的，在完全不符合其他条件的时候会使用相应值更新列；这样避免列值是NULL；
当然，如果没有ELSE，而其他所有条件又都不满足，那么想更新的列里什么也不会发生；
可以在关键词END后加上WHERE子句，就好像我们熟悉的UPDATE一样，CASE就只会套用在符合WHERE条件的列上；
CASE表达式同样可以搭配SELECT、INSERT、DELETE等子句；
表变得“乱七八糟”：
我么的表有10中饮料，查出所有的记录看起来会很乱，如下图；
如何只用一条SQL语句就能让查询出来的饮料按照字母顺序排序？
我们需要一种方式来组织我们SELECT出的数据：
我们需要一份按照drink_name排序，夏季的饮料总清单；
一条一条的查询、按顺序查出，SQL语句的数量会很多，这显然不是我们想要的；
有秩序的ORDER BY：
根据某列的排序（ORDER）返回查询的结果；
ORDER BY要求程序依照title的字母顺序返回数据；
mysql> SELECT *
-> FROM drinks_of_four_reason
-> WHERE season = '夏季'
-> ORDER BY drink_name;查询结果如下图：
安单列排序：
上述示例中ORDER BY drink_name就是按照名字的字母顺序来排序查询结果的；
ORDER BY可以按照任何字母顺序来排序查询结果；
不同的RDBMS会对数字、字母、符号规定排序规则；我们需要知道的是 NULL<数字<大写字母<小写字母；
按两列排序：
会先按照第一列排序，然后对按照第一列排序的结果再按照第二列排序；
mysql> SELECT *
-> FROM drinks_of_four_reason
-> ORDER BY drink_name,sale_date;我们举一个简单的例子，方便理解两列排序：
mysql> CREATE TABLE numbers
-> (
-> n1 CHAR(1) NOT NULL,
-> n2 CHAR(1) NOT NULL
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO numbers
-> VALUES
-> ('0','5'),
-> ('0','4'),
-> ('1','4'),
-> ('0','1'),
-> ('0','3'),
-> ('0','3'),
-> ('1','5'),
-> ('1','1'),
-> ('1','3'),
-> ('1','3');
Query OK, 10 rows affected (0.02 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM numbers ORDER BY n1,n2;
+----+----+
| n1 | n2 |
+----+----+
| 0 | 1 |
| 0 | 3 |
| 0 | 3 |
| 0 | 4 |
| 0 | 5 |
| 1 | 1 |
| 1 | 3 |
| 1 | 3 |
| 1 | 4 |
| 1 | 5 |
+----+----+
按多列排序：
我们可以利用所有需要的列来排序所有结果；
对于作为排序依据的列，你可以依需求尽可能多的使用；
多列排序的逻辑可以参照两列排序的；
执行如下SQL 打印结果：
mysql> SELECT *
-> FROM drinks_of_four_reason
-> ORDER BY drink_name,sale_date;
目前，我们已经掌握了CASE检查，以及ORDER BY；我们继续；
有序的drinks：
我们按照sale_date进行排序；
mysql> SELECT drink_name,sale_date
-> FROM drinks_of_four_reason
-> ORDER BY sale_date;这是我们得到的结果的一部分：
+------------------+------------+
| drink_name | sale_date |
+------------------+------------+
| Apple juice | 2018-08-21 |
| plum juice | 2018-08-21 |
| Apple juice | 2018-08-21 |
| plum juice | 2018-08-21 |
| apple juice | 2018-08-29 |
| pear juice | 2018-08-29 |
…...
| banana juice | 2018-09-02 |
| pear juice | 2018-09-03 |
| peach juice | 2018-09-03 |
| pear juice | 2018-09-03 |
| peach juice | 2018-09-03 |
+------------------+------------+这个列表会很长，如果我们想找到最近一天销售的饮品该怎么做？
难道要从清单的底端开始寻找；
SQL有个反转顺序的关键字：
默认，SQL根据ORDER BY指定的列都是升序的排列查询结果；
如果希望降序排列，可以在列名后加上关键字DESC；
此DESC非彼DESC：
DESC table；的是DESCRIPTION表示表说明的意思；
而在ORDER子句中，则是DESCENDING降序的意思，是一种排序方式；
DESC：
关键字DESC应位于ORDER BY子句中的列名后，用来反转查询结果的顺序；
mysql> SELECT drink_name,sale_date
-> FROM drinks_of_four_reason
-> ORDER BY sale_date DESC;
部分查询结果：
+------------------+------------+
| drink_name | sale_date |
+------------------+------------+
| pear juice | 2018-09-03 |
| peach juice | 2018-09-03 |
| peach juice | 2018-09-03 |
| pear juice | 2018-09-03 |
| banana juice | 2018-09-02 |
…...
| apple juice | 2018-08-29 |
| Apple juice | 2018-08-21 |
| plum juice | 2018-08-21 |
| plum juice | 2018-08-21 |
| Apple juice | 2018-08-21 |
+------------------+------------+默认的升序也有关键字ASC，用法和DESC相同；
那种饮料带来的收益更多？
如果我们想知道 哪种饮料的销售额最大；
目前我们可以使用ORDER BY来将不同的饮料区分开，但只能显示每条记录的售价，我们需要的是一个总额；
SUM可以为我们加总：
SQL语言中有些特殊关键字，称为函数（function）；函数是一段代码，可以对数值执行操作；
对整列执行数学运算：SUM可把括号里指定的列值全部加总；
mysql> SELECT drink_name,SUM(cost)
-> FROM drinks_of_four_reason
-> WHERE drink_name = 'banana juice';查询的结果：
+--------------+-----------+
| drink_name | SUM(cost) |
+--------------+-----------+
| banana juice | 22.00 |
+--------------+-----------+但这只是一种水果的总计，我们有10种水果，如果能用一条语句该有多好；
利用GROUP BY完成分组加总：
在SUM语句中加上GROUP BY；
它会先进行分组，在进行加总；
mysql> SELECT drink_name,SUM(cost)
-> FROM drinks_of_four_reason
-> GROUP BY drink_name;查询的结果：
+------------------+-----------+
| drink_name | SUM(cost) |
+------------------+-----------+
| apple juice | 33.00 |
| banana juice | 22.00 |
| pear juice | 22.00 |
| peach juice | 22.00 |
| orange juice | 22.00 |
| watermelon juice | 22.00 |
| strawberry juice | 22.00 |
| apricot juice | 22.00 |
| cherry juice | 22.00 |
| plum juice | 22.00 |
+------------------+-----------+为了方便我们比对查询结果，我们更新一下饮料的cost值；
mysql> UPDATE drinks_of_four_reason
-> SET cost =
-> CASE
-> WHEN drink_name = 'apple juice' THEN cost - 2
-> WHEN drink_name = 'banana juice' THEN cost - 1.5
-> WHEN drink_name = 'pear juice' THEN cost - 1
-> WHEN drink_name = 'peach juice' THEN cost - 0.5
-> WHEN drink_name = 'orange juice' THEN cost
-> WHEN drink_name = 'watermelon juice' THEN cost + 0.5
-> WHEN drink_name = 'strawberry juice' THEN cost + 1
-> WHEN drink_name = 'apricot juice' THEN cost + 1.5
-> WHEN drink_name = 'cherry juice' THEN cost + 2
-> WHEN drink_name = 'plum juice' THEN 3
-> ELSE 1
-> END;我们还可以对加总的结果进行排序：
按照新的价格的表中cost值；
mysql> SELECT drink_name,SUM(cost)
-> FROM drinks_of_four_reason
-> GROUP BY drink_name
-> ORDER BY SUM(cost) DESC;查询结果：
-> ORDER BY SUM(cost) DESC;
+------------------+-----------+
| drink_name | SUM(cost) |
+------------------+-----------+
| cherry juice | 30.00 |
| apricot juice | 28.00 |
| strawberry juice | 26.00 |
| watermelon juice | 24.00 |
| orange juice | 22.00 |
| apple juice | 21.00 |
| peach juice | 20.00 |
| pear juice | 18.00 |
| banana juice | 16.00 |
| plum juice | 12.00 |
+------------------+-----------+AVG搭配GROUP BY：
计算多条的平均值；
mysql> SELECT drink_name,AVG(cost)
-> FROM drinks_of_four_reason
-> GROUP BY drink_name
-> ORDER BY AVG(cost) DESC;查询结果：
+------------------+-----------+
| drink_name | AVG(cost) |
+------------------+-----------+
| cherry juice | 7.500000 |
| apricot juice | 7.000000 |
| strawberry juice | 6.500000 |
| watermelon juice | 6.000000 |
| orange juice | 5.500000 |
| peach juice | 5.000000 |
| pear juice | 4.500000 |
| banana juice | 4.000000 |
| apple juice | 3.500000 |
| plum juice | 3.000000 |
+------------------+-----------+MAX和MIN：
检查表中最大值和最小值；
两者用法相同；
mysql> SELECT drink_name,MAX(cost)
-> FROM drinks_of_four_reason
-> GROUP BY drink_name
-> ORDER BY MAX(cost) DESC;COUNT计算数量：
计算orange juice一共有多少条记录；
COUNT将返回指定列中的行数；
mysql> SELECT drink_name,COUNT(id)
-> FROM drinks_of_four_reason
-> WHERE drink_name = 'orange juice';查询结果：
+--------------+-----------+
| drink_name | COUNT(id) |
+--------------+-----------+
| orange juice | 4 |
+--------------+-----------+如果想计算售出orange juice有多少天呢？
我们先来看看多少条记录；
mysql> SELECT drink_name,sale_date
-> FROM drinks_of_four_reason
-> WHERE drink_name = 'orange juice';查询结果：
+--------------+------------+
| drink_name | sale_date |
+--------------+------------+
| orange juice | 2018-09-01 |
| orange juice | 2018-08-31 |
| orange juice | 2018-08-29 |
| orange juice | 2018-08-31 |
+--------------+------------+显然，我们不能直接通过COUNT(sale_date)来计算售出的天数，因为有一条重复的日期；
关键字DISTINCT：
这是一个关键字 不是函数，是需要加在我们需要的列名前边即可；
他可以选出与众不同的值——去重；
mysql> SELECT DISTINCT sale_date
-> FROM drinks_of_four_reason
-> WHERE drink_name = 'orange juice'
-> ORDER BY sale_date DESC;查询结果：
+------------+
| sale_date |
+------------+
| 2018-09-01 |
| 2018-08-31 |
| 2018-08-29 |
+------------+值得注意的是：
SELECT DISTINCT column1, column2；mysql会认为将两列都去重；
而写成SELECT column1, DISTINCT column2；mysql则会报错，因为DISTINCT必须放在要查询字段的开头；
所以，DISTINCT一般用来查询不重复记录的条数；
DISTINCT和COUNT函数搭配使用：
查询售出orange juice有多少天；
mysql> SELECT COUNT(DISTINCT sale_date)
-> FROM drinks_of_four_reason
-> WHERE drink_name = 'orange juice';查询结果：
+---------------------------+
| COUNT(DISTINCT sale_date) |
+---------------------------+
| 3 |
+---------------------------+查询中的NULL：
比如在Min中，NULL的数据并不会有影响；
LIMIT查询结果的数量：
我们可以用LIMIT限制查询结果的数量；
比如，我们只想看到售出orange juice的最近两天；
mysql> SELECT DISTINCT sale_date
-> FROM drinks_of_four_reason
-> WHERE drink_name = 'orange juice'
-> ORDER BY sale_date DESC
-> LIMIT 2;
查询结果：
+------------+
| sale_date |
+------------+
| 2018-09-01 |
| 2018-08-31 |
+------------+LIMIT，只限第二名出现：
LIMIT甚至能直接点出第二条，只需使用时添加两个参数；
LIMIT 0，4：从0的位置开始 返回4条记录；
那么查询第二条的LIMIT可以是LIMIT 1，1；
mysql> SELECT DISTINCT sale_date
-> FROM drinks_of_four_reason
-> WHERE drink_name = 'orange juice'
-> ORDER BY sale_date DESC
-> LIMIT 1,1;
查询结果：
+------------+
| sale_date |
+------------+
| 2018-08-31 |
+------------+注意：字符串的下标比较特殊，是从1开始的；SQL的计数则是从0开始的；
现在我们的查询越来越长了，即便我们已经掌握了更多的工具，这是因为我们的数据太复杂，我们会在下一章讨论；
总结：
本章我们学习了进阶的SELECT函数、关键字和查询；
1.ORDER BY：根据指定的列，按字母顺序排列查询结果；
2.GROUP BY：根据共有列，把记录分成多个组；
3.COUNT：返回一个整数值，表示有多少条记录符合查询条件；
4.DISTINCT：不同的值只会返回一次，返回结果中没有重复的值；
5.SUM：把数值列中的数据加总；
6.AVG：返回数列值的平均值；
7.MAX和MIN：返回列中的最大和最小值；
8.LIMIT：可以明确指定返回记录的数量，以及从哪一条记录开始返回；
推荐阅读