该系列文章系个人读书笔记及总结性内容,任何组织和个人不得转载进行商业活动!
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:可以明确指定返回记录的数量,以及从哪一条记录开始返回;