【SQL02】基础查询与排序

感谢datawhale开展的每月组队学习，本章教材ref: SQL Task02

SELECT语句

比较下面两者输出结果的不同：

-- 用来选取product type列为衣服’的记录的SELECT语句
SELECT product_name，product_type
  FROM product
 WHERE product_type = '衣服'；
-- 也可以选取出不是查询条件的列（条件列与输出列不同）
SELECT product_name
  FROM product
 WHERE product_type = '衣服'；

第一个SELECT取了两个column/attribute的值(product_name & product_type)
第二个SELECT只取了一个column/attribute的值(product_name)

算术运算符和比较运算符

不相等 != 也是可以的，但是因为在ANSI/ISO/IEC International Standard (IS)
Database Language SQL中，<>有被定义为不等于，但是!=并没有。所以不是所有的database 都支持!=。

常用法则

• SELECT子句中可以使用常数或者表达式。
• 使用比较运算符时一定要注意不等号和等号的位置。
• 字符串类型的数据原则上按照字典顺序进行排序，不能与数字的大小顺序混淆。
• 选取NULL记录时，需要在条件表达式中使用IS NULL运算符。希望选取不是NULL的记录时，需要在条件表达式中使用IS NOT NULL运算符。

逻辑运算符

NOT运算符

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

NOT WHERE xxx := 取非 WHERE xxx 的内容

AND运算符和OR运算符

• AND 相当于“并且”，类似数学中的取交集

• OR 相当于“或者”，类似数学中的取并集

• 括号优先处理

真值表

• AND 两边全真才return True，其余为False

• OR 两边只要有一个为真就return True，全假才False

• NOT True := False, NOT False := True

含有NULL时的真值

• NULL的真值结果既不为真，也不为假，因为并不知道这样一个值。
• 用不确定（UNKNOWN）表示
• 一般的逻辑运算并不存在这第三种值。SQL 之外的语言也基本上只使用真和假这两种真值。与通常的逻辑运算被称为二值逻辑相对，只有 SQL 中的逻辑运算被称为三值逻辑。

三值逻辑下的AND和OR真值表为：

练习题-第一部分

2.1

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

SELECT
    product_name, regist_date
FROM
     product
WHERE
    regist_date > '2009=04-28';

思路：
需要的表：product
需要选择的列：product_name 和 regist_date
用WHERE子句过滤掉不满足 “regist在2009年4月28日之后” 条件的数据

2.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;

不会出现我们想要取的为空值/不为空值的内容，什么都不反悔
因为NULL 不等于anything，甚至NULL不等于NULL

2.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

-- solution 1
SELECT
    product_name, sale_price, purchase_price
FROM
    product
WHERE
    abs(sale_price - product.purchase_price) > 499;

-- solution 2
SELECT b.product_name, b.sale_price, b.purchase_price
FROM
     product b
JOIN
(SELECT
    product_id, abs(sale_price-product.purchase_price) as diff
FROM
    product) a
ON b.product_id = a.product_id
WHERE
    a.diff >= 500;

思路：

• solution 1：
  • 因为是需要高出500日元以上，例子中给出的是大于等于500，这两个列的数值类型是整数型(Int)。所以这里大于499是一样的
• solution 2:
  • 这里我先做了一个temp table，这个表中包含了产品ID以及售价和购买价格的差值（diff）。用这个临时表去和原本product表去JOIN（用product_id链接），然后用WHERE clause去过滤掉 diff小于500 的部分。

2.4

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

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

-- 销售单价打九折之后利润高于100日元的办公用品和厨房用具
-- solution 1
SELECT
    product_name, product_type, (sale_price*0.9-purchase_price) as profit
FROM
     product
WHERE
        product_type = 'Office'
    OR
        product_type = 'Kitchen'
HAVING
    profit >= 100;
    
-- solution 2
SELECT
    product_name, product_type, (sale_price*0.9-purchase_price) as profit
FROM
     product
WHERE
    product_type IN ('Office', 'Kitchen')
HAVING
    profit >= 100;

思路：

• 首先我们需要的表为：product
• 我们需要SELECT的列名包括 product_name, product_type 和 profit
  • profit 我们通过对sale_price 和 purchase_price的计算可以很容易得到
• 然后我们需要过滤一下不关心的部分，通过WHERE condition OR condition可以得到办公用品和厨房用品
• 但是对于SELECT句中的profit，因为SQL的执行顺序，WHERE子句是执行在SELECT子句之前的，所以WHERE不能够知道profit是什么。但是我们可以用HAVING解决这个问题，把profit大于等于100放到HAVING子句里。

对表进行聚合查询

聚合函数 aggregate function

对CHAR，VARCHAR使用MAX()/MIN()

• 对字符串使用聚合函数 MIN() 或 MAX()，实际上是在做collation（文字排序），指在计算机科学与图书馆学、词典编撰中书写信息的标准排序。描述了如何对数据进行比较和排序。
• 从MySQL官方文档中知道，MySQL支持很多种字符集。我们可以用 SHOW CHARACTER SET; 去查看可用的字符集。

mysql> SHOW CHARACTER SET;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
...
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
...
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
...
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
...
| binary   | Binary pseudo charset           | binary              |      1 |
...

对表进行分组

GROUP BY语句

分组汇总时（即：将现有的数据按照某列来汇总统计）用GROUP BY

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

SQL query execution order
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY
好像MySQL的execution order是不一样的？？还没看到足够ref和官方doc
FROM -> WHERE -> SELECT -> GROUP BY -> HAVING -> ORDER BY

为聚合结果指定条件

对查询结果进行排序

练习题-第二部分

2.5

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

-- 本SELECT语句中存在错误。
SELECT product id，SUM（product name）
  FROM product 
 GROUP BY product_type 
 WHERE regist_date > '2009-09-01'；

1. SELECT clause中：列名错误
   • product id? 列名应该是product_id, 这样写的话query执行的是找名字是product的列并赋予其别名叫id。
   • 同样同一行的product name应该是product_name
2. aggregate function SUM() 只能使用在数值类型的列，而product_name的数据类型是字符型
3. GROUP BY clause的位置放错，GROUP BY应该出现在WHERE后面
4. SELECT clause中出现 非GROUP BY中指定的列，而且没有aggregate function可以对非聚合键进行一个操作

2.6

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

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

-- 求出销售单价（sale_price列）合计值是进货单价（purchase price列）合计值1.5倍的商品种类。执行结果如下所示
SELECT
    product_type ,SUM(sale_price) as total_sale, SUM(purchase_price) as total_purcha
FROM
    product
GROUP BY
    product_type
HAVING
    total_sale / total_purcha >= 1.5;

思路
首先我们确定需要使用到的表为product。哈哈虽然对于这道题一目了然，但是将来遇到复杂问题先确定表还是很好用的
然后我们知道，是要对每个商品种类计算它们的销售单价总和&进货单价总和，于是我们知道要用GROUP BY, 聚合键为product_type
根据题目提供的最终执行结果可知，我们需要`SELECT product_type, total_sale, 和 total_purchase三项
最后，我们用一个HAVING去过滤掉不满足 销售单价总和&进货单价总和 相差1.5倍的数据

2.7

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

SELECT *
FROM
    product
ORDER BY
    regist_date IS NOT NULL,regist_date DESC, sale_price;

啊哈解决了，哈哈哈可以安心去吃饭了～

SELECT *
FROM
    product
ORDER BY
    regist_date DESC, sale_price;

我的电脑会把null放到最后面？除了regist_date为null的那项位置和图片不一样（图片在第一行，我query出来在最后一行）。其余的顺序都是一样的。
其实是：MySQL NULL values are considered lower than any non-NULL value, therefore, NULL values appear first when the order is ASC (ascending), and ordered last when the order is DESC (descending).