阿里云——天池SQL训练营Task02

本笔记为阿里云天池龙珠计划SQL训练营的学习内容，链接为：https://tianchi.aliyun.com/specials/promotion/aicampsql；

一、SELECT语句基础

1.1 从表中选取数据

SELECT语句（查询/从表中选出数据）：

SELECT <列名>,

FROM <表名>;

1.2从表中选取符合条件的数据

WHERE语句（选出满足条件的语句）：

SELECT <列名>, ……

FROM <表名>

WHERE <条件表达式>;

1.3 相关法则

（1）星号（*）代表全部列的意思。

（2）SQL中可以随意使用换行符，不影响语句执行（但不可插入空行）。

（3）设定汉语别名时需要使用双引号（"）括起来。

（4）在SELECT语句中使用DISTINCT可以删除重复行。

（5）注释是SQL语句中用来标识说明或者注意事项的部分。分为1行注释"-- "和多行注释两种"/* */"。

二、算术运算符和比较运算符

2.1 算术运算符

含义	运算符
加法	+
减法	-
乘法	*
除法	/

2.2 比较运算符

运算符	含义
=	和~相等
<>	和~不相等
>=	大于等于~
>	大于~
<=	小于等于~
<	小于~

2.3 常用法则

（1）SELECT子句中可以使用常数或者表达式。

（2）使用比较运算符时一定要注意不等号和等号的位置。

（3）字符串类型的数据原则上按照字典顺序进行排序，不能与数字的大小顺序混淆。

（4）希望选取NULL记录时，需要在条件表达式中使用IS NULL运算符。希望选取不是NULL的记录时，需要在条件表达式中使用IS NOT NULL运算符。

三、逻辑运算符

3.1 NOT运算符

想要表示“不是……”时，除了前文的<>运算符外，还存在另外一个表示否定、使用范围更广的运算符：NOT（不能单独使用）。

3.2 AND运算符和OR运算符

当希望同时使用多个查询条件时，可以使用AND或者OR运算符。（AND 相当于“并且”，OR 相当于“或者”）。

3.3 通过括号优先处理

3.4 真值表

当碰到条件较复杂的语句时，可以采用真值表来帮忙梳理逻辑。

3.5 含有NULL时的真值

这时真值是除真假之外的第三种值——不确定（UNKNOWN）。NULL的真值结果既不为真，也不为假，因为并不知道这样一个值。一般的逻辑运算并不存在这第三种值。

四、对表进行聚合查询

4.1 聚合函数

SQL中用于汇总的函数叫做聚合函数。以下五个是最常用的聚合函数：

（1）COUNT：计算表中的记录数（行数）；

（2）SUM：计算表中数值列中数据的合计值；

（3）AVG：计算表中数值列中数据的平均值；

（4）MAX：求出表中任意列中数据的最大值；

（5）MIN：求出表中任意列中数据的最小值；

4.2 常用法则

（1）COUNT函数的结果根据参数的不同而不同。COUNT(*)会得到包含NULL的数据行数，而COUNT(<列名>)会得到NULL之外的数据行数。

（2）聚合函数会将NULL排除在外。但COUNT(*)例外，并不会排除NULL。

（3）MAX/MIN函数几乎适用于所有数据类型的列。SUM/AVG函数只适用于数值类型的列。

（4）想要计算值的种类时，可以在COUNT函数的参数中使用DISTINCT。

（5）在聚合函数的参数中使用DISTINCT，可以删除重复数据。

五、对表进行分组

5.1 GROUP BY语句

进行分组汇总（即：将现有的数据按照某列来汇总统计）：

SELECT <列名1>,<列名2>, <列名3>, ……

FROM <表名>

GROUP BY <列名1>, <列名2>, <列名3>, ……;

5.2 聚合键中包含NULL时

将NULL作为一组特殊数据进行处理

5.3 GROUP BY书写位置

GROUP BY的子句书写顺序有严格要求，不按要求会导致SQL无法正常执行，目前出现过的子句书写****顺序为：

1**.**SELECT → 2. FROM → 3. WHERE → 4. GROUP BY

5.4 在WHERE子句中使用GROUP BY
5.5 常见错误

在使用聚合函数及GROUP BY子句时，经常出现的错误有：

（1）在聚合函数的SELECT子句中写了聚合健以外的列 使用COUNT等聚合函数时，SELECT子句中如果出现列名，只能是GROUP BY子句中指定的列名（也就是聚合键）。

（2）在GROUP BY子句中使用列的别名 SELECT子句中可以通过AS来指定别名，但在GROUP BY中不能使用别名。因为在DBMS中 ,SELECT子句在GROUP BY子句后执行。

（3）在WHERE中使用聚合函数 原因是聚合函数的使用前提是结果集已经确定，而WHERE还处于确定结果集的过程中，所以相互矛盾会引发错误。 如果想指定条件，可以在SELECT，HAVING（下面马上会讲）以及ORDER BY子句中使用聚合函数。

六、为聚合结果指定条件

6.1 用HAVING得到特定分组

将表使用GROUP BY分组后，可以在GROUP BY后使用HAVING子句来取出其中两组。（HAVING的用法类似WHERE）

6.2 HAVING特点

HAVING子句用于对分组进行过滤，可以使用数字、聚合函数和GROUP BY中指定的列名（聚合键）。

七、对查询结果进行排序

7.1 ORDER BY

SQL中的执行结果是随机排列的，当需要按照特定顺序排序时，可已使用ORDER BY子句。默认为升序排列，降序排列为DESC。

7.2 ORDER BY中列名可使用别名

其中SELECT的执行顺序在 GROUP BY 子句之后，ORDER BY 子句之前。也就是说，当在ORDER BY中使用别名时，已经知道了SELECT设置的别名存在，但是在GROUP BY中使用别名时还不知道别名的存在，所以在ORDER BY中可以使用别名，但是在GROUP BY中不能使用别名。

练习

练习题1

编写一条SQL语句，从product（商品）表中选取出“登记日期（regist在2009年4月28日之后”的商品，查询结果要包含product_name和regist_date两列。

代码编写：select  product_name , regist_date

                  from product

                  where regist_date > '2009-04-28';

练习题2

请说出对product 表执行如下3条SELECT语句时的返回结果。

代码编写：1.select *
                     from product
                     where purchase_price = NULL;

                   2.select *

                      from product

                      where purchase_price <> NULL;

                    3.select *
                       from product
                       where product_name > NULL;

练习题3

代码清单2-22（2-2节）中的SELECT语句能够从product表中取出“销售单价（saleprice）比进货单价（purchase price）高出500日元以上”的商品。请写出两条可以得到相同结果的SELECT语句。执行结果如下所示。

product_name | sale_price | purchase_price 
-------------+------------+------------
T恤衫         | 　 1000    | 500
运动T恤       |    4000    | 2800
高压锅        |    6800    | 5000

代码编写：select product_name,sale_price,purchase_price from product where sale_price-                            purchase_price>=500;
                  select product_name,sale_price,purchase_price from product where sale_price-                            500>=purchase_price;
                  select product_name,sale_price,purchase_price from product where                                              sale_price>=500+purchase_price;

练习题4

请写出一条SELECT语句，从product表中选取出满足“销售单价打九折之后利润高于100日元的办公用品和厨房用具”条件的记录。查询结果要包括product_name列、product_type列以及销售单价打九折之后的利润（别名设定为profit）。

提示：销售单价打九折，可以通过saleprice列的值乘以0.9获得，利润可以通过该值减去purchase_price列的值获得。

代码编写：select product_name,product_type,sale_price*0.9-purchase_price

                  as profit

                  from  product

                 where sale_price*0.9-purchase_price >=100;

练习题5

请指出下述SELECT语句中所有的语法错误。

SELECT product_id, SUM（product_name）
--本SELECT语句中存在错误。
  FROM product 
 GROUP BY product_type 
 WHERE regist_date > '2009-09-01';

答：1.select语句中出现聚合键以外的列名；

        2.where语句应位于group by前；

        3.product_name不能用sum聚合.

练习题6

请编写一条SELECT语句，求出销售单价（sale_price列）合计值大于进货单价（purchase_price列）合计值1.5倍的商品种类。执行结果如下所示。

product_type | sum  | sum 
-------------+------+------
衣服         | 5000 | 3300
办公用品      |  600 | 320

代码编写：select product_type, SUM(sale_price), SUM(purchase_price)
                  from product
                  group BY product_type
                  having SUM(sale_price) > SUM(purchase_price) * 1.5;

练习题7

此前我们曾经使用SELECT语句选取出了product（商品）表中的全部记录。当时我们使用了ORDERBY子句来指定排列顺序，但现在已经无法记起当时如何指定的了。请根据下列执行结果，思考ORDERBY子句的内容。