基础查询与排序

本文学习自DATAWHALE SQL教程

SELECT & WHERE

SELECT：从表中选出必要数据

query：匹配查询/查询，通过SELECT选出必要数据的过程

SELECT "栏位名" FROM "表格名";

WHERE：选择性地提取必要数据

SELECT "栏位名" 
FROM "表格名" 
WHERE "条件";

例如：

SELECT sale_price
FROM product
WHERE product_name='裤子';

一些SQL规则

一、星号（*）代表全部列

SELECT * FROM <表名>；

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

三、SELECT语句中可使用DISTINCT可以删除重复行

SELECT DISTINCT product_type FROM product;

四、单行注释：–；多行注释：/* */

五、选取NULL记录时使用IS NULL运算符；选取不是NULL的记录时使用IS NOT NULL运算符

-- 选取NULL的记录
SELECT product_name, purchase_price
FROM product
WHERE purchase_price IS NULL;
-- 选取不为NULL的记录
SELECT product_name, purchase_price
FROM product
WHERE purchase_price IS NOT NULL;

六、SQL语句可以使用AS关键字为列设定别名（用中文时需要双引号）

SELECT product_id     As id,
       product_name   As name,
       purchase_price AS "进货单价"
FROM product;

七、SQL语句可以使用算术表达式

SELECT product_name, sale_price, sale_price * 2 AS "sale_price x2"
FROM product;

SELECT product_name, sale_price, purchase_price FROM product
WHERE sale_price-purchase_price >= 500;

八、可以使用AND、OR、NOT运算符

-- 通过使用括号让OR运算符先于AND运算符执行
SELECT product_name, product_type, regist_date
FROM product
WHERE product_type = '办公用品'
AND ( regist_date = '2009-09-11' OR regist_date = '2009-09-20');

九、注意SQL因为NULL的存在，其逻辑运算称为三值逻辑，总之就是不管AND还是OR，但凡有一个不确定就不确定。

练习题

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

SELECT product_name, regist_date From product
WHERE regist_date >= '2009-4-28';

说明：SQL中可以使用>, <, >=, <=来比较和选择日期，

二、请说出对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;

• 返回product表中purchase_price一列为NULL的全部列；
• 返回product表中purchase_price一列不为NULL的全部列；
• 报错，NULL不能比较；

三、请写出一条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 > 100 AND (product_type='办公用品' OR product_type='厨房用具');

SQL中的聚合函数

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

• COUNT：计算表中的记录数（行数）
• SUM：计算表中数值列中数据的合计值
• AVG：计算表中数值列中数据的平均值
• MAX：求出表中任意列中数据的最大值
• MIN：求出表中任意列中数据的最小值

-- 计算全部数据的行数（包含NULL）
SELECT COUNT(*)
  FROM product;
-- 计算NULL以外数据的行数
SELECT COUNT(purchase_price)
  FROM product;
-- 计算销售单价和进货单价的合计值
SELECT SUM(sale_price), SUM(purchase_price) 
  FROM product;
-- 计算销售单价和进货单价的平均值
SELECT AVG(sale_price), AVG(purchase_price)
  FROM product;
-- MAX和MIN也可用于非数值型数据
SELECT MAX(regist_date), MIN(regist_date)
  FROM product;
-- 计算去除重复数据后的数据行数
SELECT COUNT(DISTINCT product_type)
 FROM product;

一些常用法则

• COUNT函数的结果根据参数的不同而不同。COUNT(*)会得到包含NULL的数据行数，而COUNT(<列名>)会得到NULL之外的数据行数。
• 聚合函数会将NULL排除在外。但COUNT(*)例外，并不会排除NULL。
• MAX/MIN函数几乎适用于所有数据类型的列。SUM/AVG函数只适用于数值类型的列。

分组汇总与排序

GROUP BY：用于分组汇总

举例：我们知道一个商场所有店铺的销售额（用SUM求），若想了解每家店铺单独的销售额，就需要用到GROUP BY，如下

SELECT Store_Name, SUM(Sales) 
FROM Store_Information 
GROUP BY Store_Name;

这样返回的结果就是各个店铺的总销售额。

GROUP BY的书写位置：

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

其中前三项用于筛选数据，GROUP BY对筛选出的数据进行处理，例如：

SELECT purchase_price, COUNT(*)
FROM product
WHERE product_type = '衣服'
GROUP BY purchase_price;

上述代码是在统计不同购买价格的衣服的数量，我理解该统计结果会包含NULL.

在使用聚合函数及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子句中使用聚合函数。

HAVING：对分组进行过滤

SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type
HAVING COUNT(*) = 2;

下面是一个典型的错误的例子：

-- 错误形式（因为product_name不包含在GROUP BY聚合键中）
SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type
HAVING product_name = '圆珠笔';

ORDER BY：顺序排列结果

-- 降序排列为DESC
SELECT product_id, product_name, sale_price, purchase_price
FROM product
ORDER BY sale_price DESC;
-- 升序排列是默认的
SELECT product_id, product_name, sale_price, purchase_price
FROM product
ORDER BY sale_price, product_id;

注意当用于排序的列名中含有NULL时，NULL会在开头或末尾进行汇总。

当使用了过多的SQL命令时，命令的执行顺序变得特别重要，SQL在使用 HAVING 子句时 SELECT 语句的顺序为：

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

该顺序符合逻辑，不难理解

练习题

一、指出语法错误

SELECT product_id, SUM（product_name）
FROM product 
GROUP BY product_type 
WHERE regist_date > '2009-09-01';

在聚合函数的SELECT子句中写了聚合健以外的列，product_type不是SELECT子句里面的列；

此外，GROUP BY和WHERE的顺序写错了。

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

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

SELECT product_type, SUM（sale_price）, SUM(purchace_price)
FROM product 
WHERE sale_price > purchace_price*1.5;

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

答：ORDER BY regist_date;