【教奶奶學SQL】(task2)基礎查詢與排序

學習總結

（1）學習datawhale SQL的總結，本次是SELECT基礎查詢語句，可以用WHERE加條件，算術運算符和邏輯運算符，聚合查詢、對表分組、為聚合結果指定條件等。

（2）SQL中可以随意使用換行符，不影響語句執行（但不可插入空行）。

（3）希望選取NULL記錄時，需要在條件表達式中使用IS NULL運算符。希望選取不是NULL的記錄時，需要在條件表達式中使用IS NOT NULL運算符。注意不是用

<>NULL

表示非NULL。

（4）COUNT函數的結果根據參數的不同而不同。COUNT( * )會得到包含NULL的資料行數，而COUNT(<列名>)會得到NULL之外的資料行數。

聚合函數會将NULL排除在外。但COUNT( * )例外，并不會排除NULL。

（5）用GROUP BY分組後可以用HAVING取出想要的組。SQL在使用 HAVING 子句時 SELECT 語句的順序為：

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

其中SELECT的執行順序在 GROUP BY 子句之後，ORDER BY 子句之前。也就是說，當在ORDER BY中使用别名時，已經知道了SELECT設定的别名存在，但是在GROUP BY中使用别名時還不知道别名的存在，是以不能在ORDER BY中可以使用别名，但是在GROUP BY中不能使用别名。

（6）NULL是非真非假的不确定值，不可以用=或者<>進行查詢，需要使用IS NULL或IS NOT NULL或者ISNULL。

文章目錄

學習總結
一、SELECT語句基礎

1.1 從表中選取資料

SELECT語句

1.2 從表中選取符合條件的資料

WHERE語句

1.3 相關法則

二、算術運算符和比較運算符

2.1 算術運算符
2.2 比較運算符
2.3 常用法則

三、邏輯運算符

3.1 NOT運算符
3.2 AND運算符和OR運算符

通過括号優先處理

3.3 真值表

含有NULL時的真值

四、作業一

五、對表進行聚合查詢

5.1 聚合函數

使用聚合函數删除重複值

5.2 常用法則

六、對表進行分組

6.1 GROUP BY語句

聚合鍵中包含NULL時
GROUP BY書寫位置
在WHERE子句中使用GROUP BY

6.2 常見錯誤

七、為聚合結果指定條件

7.1 用HAVING得到特定分組
7.2 HAVING特點

八、對查詢結果進行排序

8.1 ORDER BY
8.2 ORDER BY中列名可使用别名
8.3 ORDER BY 排序列中存在 NULL 時，指定其出現在首行或者末行的方式

（1）将 `NULL` 值排在末行，同時将所有 `非NULL` 值按升序排列。
（2）将 `NULL` 值排在首行，同時将所有 `非NULL` 值按倒序排列。

九、作業二

Reference

一、SELECT語句基礎

1.1 從表中選取資料

SELECT語句

從表中選取資料時需要使用SELECT語句，也就是隻從表中選出（SELECT）必要資料的意思。通過SELECT語句查詢并選取出必要資料的過程稱為比對查詢或查詢（query）。

基本SELECT語句包含了SELECT和FROM兩個子句（clause）。示例如下：

SELECT <列名>, 
  FROM <表名>;

其中，SELECT子句中列舉了希望從表中查詢出的列的名稱，而FROM子句則指定了選取出資料的表的名稱。

1.2 從表中選取符合條件的資料

WHERE語句

當不需要取出全部資料，而是選取出滿足“商品種類為衣服”、“銷售單價在1000日元以上”等某些條件的資料時，使用WHERE語句。

SELECT 語句通過WHERE子句來指定查詢資料的條件。在WHERE 子句中可以指定“某一列的值和這個字元串相等”或者“某一列的值大于這個數字”等條件。執行含有這些條件的SELECT語句，就可以查詢出隻符合該條件的記錄了。

SELECT <列名>, ……
  FROM <表名>
 WHERE <條件表達式>;

比較下面兩者輸出結果的不同：

-- 用來選取product type列為衣服的記錄的SELECT語句
SELECT product_name, product_type
  FROM product
 WHERE product_type = '衣服';
-- 也可以選取出不是查詢條件的列（條件列與輸出列不同）
SELECT product_name
  FROM product
 WHERE product_type = '衣服';

1.3 相關法則

星号（*）代表全部列的意思。
SQL中可以随意使用換行符，不影響語句執行（但不可插入空行）。
設定漢語别名時需要使用雙引号（"）括起來。
在SELECT語句中使用 DISTINCT 可以删除重複行。
注釋是SQL語句中用來辨別說明或者注意事項的部分。分為1行注釋"-- “和多行注釋兩種”/* */"。

-- 想要查詢出全部列時，可以使用代表所有列的星号（*）。
SELECT *
  FROM <表名>；
-- SQL語句可以使用AS關鍵字為列設定别名（用中文時需要雙引号（“”））。
SELECT product_id     As id,
       product_name   As name,
       purchase_price AS "進貨單價"
  FROM product;
-- 使用DISTINCT删除product_type列中重複的資料
SELECT DISTINCT product_type
  FROM product;

二、算術運算符和比較運算符

2.1 算術運算符

SQL語句中可以使用的四則運算的主要運算符如下：

含義	運算符
加法	+
減法	-
乘法	*
除法	/

2.2 比較運算符

-- 選取出sale_price列為500的記錄
SELECT product_name, product_type
  FROM product
 WHERE sale_price = 500;

SQL常見比較運算符如下：

運算符	含義
=	和~相等
<>	和~不相等
>=	大于等于~
>	大于~
<=	小于等于~
<	小于~

2.3 常用法則

SELECT子句中可以使用常數或者表達式。
使用比較運算符時一定要注意不等号和等号的位置。
字元串類型的資料原則上按照字典順序進行排序，不能與數字的大小順序混淆。
希望選取NULL記錄時，需要在條件表達式中使用IS NULL運算符。希望選取不是NULL的記錄時，需要在條件表達式中使用IS NOT NULL運算符。注意不是用 <>NULL 表示非NULL。

相關代碼如下：

-- SQL語句中也可以使用運算表達式
SELECT product_name, sale_price, sale_price * 2 AS "sale_price x2"
  FROM product;

-- WHERE子句的條件表達式中也可以使用計算表達式
SELECT product_name, sale_price, purchase_price
  FROM product
 WHERE sale_price-purchase_price >= 500;

/* 對字元串使用不等号
首先建立chars并插入資料
選取出大于‘2’的SELECT語句*/
-- DDL：建立表
CREATE TABLE chars
（chr CHAR（3）NOT NULL, 
PRIMARY KEY（chr））;
-- 選取出大于'2'的資料的SELECT語句('2'為字元串)
SELECT chr
  FROM chars
 WHERE chr > '2';
-- 選取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;

三、邏輯運算符

3.1 NOT運算符

想要表示“不是……”時，除了前文的<>運算符外，還存在另外一個表示否定、使用範圍更廣的運算符：NOT。

NOT不能單獨使用，如下例：

-- 選取出銷售單價大于等于1000日元的記錄
SELECT product_name, product_type, sale_price
  FROM product
 WHERE sale_price >= 1000;
-- 向代碼清單2-30的查詢條件中添加NOT運算符
SELECT product_name, product_type, sale_price
  FROM product
 WHERE NOT sale_price >= 1000;

3.2 AND運算符和OR運算符

當希望同時使用多個查詢條件時，可以使用AND或者OR運算符。

AND 相當于“并且”，類似數學中的取交集；

OR 相當于“或者”，類似數學中的取并集。

通過括号優先處理

如果要查找這樣一個商品，該怎麼處理？

“商品種類為辦公用品”并且“登記日期是 2009 年 9 月 11 日或者 2009 年 9 月 20 日”

理想結果為“打孔器”，但當你輸入以下資訊時，會得到錯誤結果

-- 将查詢條件原封不動地寫入條件表達式，會得到錯誤結果
SELECT product_name, product_type, regist_date
  FROM product
 WHERE product_type = '辦公用品'
   AND regist_date = '2009-09-11'
    OR regist_date = '2009-09-20';

錯誤的原因是 AND 運算符優先于 OR 運算符，想要優先執行OR運算，可以使用括号：

-- 通過使用括号讓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');

3.3 真值表

複雜運算時該怎樣了解？

當碰到條件較複雜的語句時，了解語句含義并不容易，這時可以采用真值表來梳理邏輯關系。

什麼是真值？

本節介紹的三個運算符 NOT、AND 和 OR 稱為邏輯運算符。這裡所說的邏輯就是對真值進行操作的意思。真值就是值為真（TRUE）或假（FALSE）其中之一的值。

例如，對于 sale_price >= 3000 這個查詢條件來說，由于 product_name 列為 ‘運動 T 恤’ 的記錄的 sale_price 列的值是 2800，是以會傳回假（FALSE），而 product_name 列為 ‘高壓鍋’ 的記錄的sale_price 列的值是 5000，是以傳回真（TRUE）。

AND 運算符兩側的真值都為真時傳回真，除此之外都傳回假。

OR 運算符兩側的真值隻要有一個不為假就傳回真，隻有當其兩側的真值都為假時才傳回假。

NOT運算符隻是單純的将真轉換為假，将假轉換為真。

真值表

查詢條件為P AND（Q OR R）的真值表

含有NULL時的真值

NULL的真值結果既不為真，也不為假，因為并不知道這樣一個值。

那該如何表示呢？

這時真值是除真假之外的第三種值——不确定（UNKNOWN）。一般的邏輯運算并不存在這第三種值。SQL 之外的語言也基本上隻使用真和假這兩種真值。與通常的邏輯運算被稱為二值邏輯相對，隻有 SQL 中的邏輯運算被稱為三值邏輯。

三值邏輯下的AND和OR真值表為：

四、作業一

4.1

編寫一條SQL語句，從

product

(商品) 表中選取出“登記日期(

regist_date

)在2009年4月28日之後”的商品，查詢結果要包含

product name

和

regist_date

兩列。

mysql> SELECT product_name, regist_date
    -> FROM product
    -> WHERE regist_date > '2009-04-28';
+--------------+-------------+
| product_name | regist_date |
+--------------+-------------+
| T恤衫        | 2009-09-20  |
| 打孔器       | 2009-09-11  |
| 菜刀         | 2009-09-20  |
| 叉子         | 2009-09-20  |
| 原子筆       | 2009-11-11  |
+--------------+-------------+
5 rows in set (0.01 sec)

4.2

還是上面我們建立

product

表：

mysql> SELECT * FROM product;
+------------+--------------+--------------+------------+----------------+-------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+------------+--------------+--------------+------------+----------------+-------------+
| 0001       | T恤衫        | 衣服         |       1000 |            500 | 2009-09-20  |
| 0002       | 打孔器       | 辦公用品     |        500 |            320 | 2009-09-11  |
| 0003       | 運動T恤      | 衣服         |       4000 |           2800 | NULL        |
| 0004       | 菜刀         | 廚房用具     |       3000 |           2800 | 2009-09-20  |
| 0005       | 高壓鍋       | 廚房用具     |       6800 |           5000 | 2009-01-15  |
| 0006       | 叉子         | 廚房用具     |        500 |           NULL | 2009-09-20  |
| 0007       | 擦菜闆       | 廚房用具     |        880 |            790 | 2008-04-28  |
| 0008       | 原子筆       | 辦公用品     |        100 |           NULL | 2009-11-11  |
+------------+--------------+--------------+------------+----------------+-------------+

請說出對product 表執行如下3條SELECT語句時的傳回結果。

①

SELECT *
  FROM product
 WHERE purchase_price = NULL;

②

SELECT *
  FROM product
 WHERE purchase_price <> NULL;

③

SELECT *
  FROM product
 WHERE product_name > NULL;

3個結果都是：

Empty set (0.00 sec)

，

NULL

既不是真也不是假，異或之後的運算結果也是“未知”。

4.3

代碼清單2-22（2-2節）中的SELECT語句能夠從

product

表中取出“銷售單價（

sale_price

）比進貨單價（

purchase_price

）高出500日元以上”的商品。請寫出兩條可以得到相同結果的SELECT語句。執行結果如下所示。

product_name | sale_price | purchase_price 
-------------+------------+------------
T恤衫         | 　 1000    | 500
運動T恤       |    4000    | 2800
高壓鍋        |    6800    | 5000

解法一：

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

解法二：

mysql> SELECT product_name, sale_price,purchase_price
    -> FROM product
    -> WHERE NOT sale_price - purchase_price < 500;

4.4

請寫出一條SELECT語句，從

product

表中選取出滿足“銷售單價打九折之後利潤高于

日元的辦公用品和廚房用具”條件的記錄。查詢結果要包括

product_name

列、

product_type

列以及銷售單價打九折之後的利潤（别名設定為

profit

）。

提示：銷售單價打九折，可以通過

sale_price

列的值乘以0.9獲得，利潤可以通過該值減去

purchase_price

列的值獲得。

mysql> SELECT product_name, product_type, 0.9*purchase_price-sale_price AS profit
    -> FROM product
    -> WHERE(product_type = '辦公用品'
    -> OR product_type = '廚房用具')
    -> AND (0.9*purchase_price-sale_price > 100);

五、對表進行聚合查詢

5.1 聚合函數

SQL中用于彙總的函數叫做聚合函數。以下五個是最常用的聚合函數：

COUNT：計算表中的記錄數（行數）
SUM：計算表中數值列中資料的合計值
AVG：計算表中數值列中資料的平均值
MAX：求出表中任意列中資料的最大值
MIN：求出表中任意列中資料的最小值

請沿用

product

表資料，使用以下操作熟練函數：

-- 計算全部資料的行數（包含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;
-- 是否使用DISTINCT時的動作差異（SUM函數）
SELECT SUM(sale_price), SUM(DISTINCT sale_price)
 FROM product;

mysql> SELECT SUM(sale_price), SUM(DISTINCT sale_price)
    -> FROM product;
+-----------------+--------------------------+
| SUM(sale_price) | SUM(DISTINCT sale_price) |
+-----------------+--------------------------+
|           16780 |                    16280 |
+-----------------+--------------------------+
1 row in set (0.00 sec)

5.2 常用法則

COUNT函數的結果根據參數的不同而不同。COUNT(*)會得到包含NULL的資料行數，而COUNT(<列名>)會得到NULL之外的資料行數。
聚合函數會将NULL排除在外。但COUNT(*)例外，并不會排除NULL。
MAX/MIN函數幾乎适用于所有資料類型的列。SUM/AVG函數隻适用于數值類型的列。
想要計算值的種類時，可以在COUNT函數的參數中使用 DISTINCT 。
在聚合函數的參數中使用 DISTINCT ，可以删除重複資料。

六、對表進行分組

6.1 GROUP BY語句

之前使用聚合函數都是會将整個表的資料進行處理，當你想将進行分組彙總時（即：将現有的資料按照某列來彙總統計），GROUP BY：

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

還是對于上面我們建立

product

表操作：

mysql> SELECT * FROM product;
+------------+--------------+--------------+------------+----------------+-------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+------------+--------------+--------------+------------+----------------+-------------+
| 0001       | T恤衫        | 衣服         |       1000 |            500 | 2009-09-20  |
| 0002       | 打孔器       | 辦公用品     |        500 |            320 | 2009-09-11  |
| 0003       | 運動T恤      | 衣服         |       4000 |           2800 | NULL        |
| 0004       | 菜刀         | 廚房用具     |       3000 |           2800 | 2009-09-20  |
| 0005       | 高壓鍋       | 廚房用具     |       6800 |           5000 | 2009-01-15  |
| 0006       | 叉子         | 廚房用具     |        500 |           NULL | 2009-09-20  |
| 0007       | 擦菜闆       | 廚房用具     |        880 |            790 | 2008-04-28  |
| 0008       | 原子筆       | 辦公用品     |        100 |           NULL | 2009-11-11  |
+------------+--------------+--------------+------------+----------------+-------------+

看一看是否使用GROUP BY語句的差異：

-- 按照商品種類統計資料行數
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type;
+--------------+----------+
| product_type | COUNT(*) |
+--------------+----------+
| 衣服         |        2 |
| 辦公用品     |        2 |
| 廚房用具     |        4 |
+--------------+----------+
3 rows in set (0.01 sec)

 -- 不含GROUP BY
SELECT product_type, COUNT(*)
  FROM product
+--------------+----------+
| product_type | COUNT(*) |
+--------------+----------+
| 衣服         |        8 |
+--------------+----------+
1 row in set (0.00 sec)

按照商品種類對表進行切分

這樣，GROUP BY 子句就像切蛋糕那樣将表進行了分組。在 GROUP BY 子句中指定的列稱為聚合鍵或者分組列。

聚合鍵中包含NULL時

此時會将NULL作為一組特殊資料進行處理。

将進貨單價（

purchase_price

）作為聚合鍵舉例：

SELECT purchase_price, COUNT(*)
  FROM product
 GROUP BY purchase_price;
+----------------+----------+
| purchase_price | COUNT(*) |
+----------------+----------+
|            500 |        1 |
|            320 |        1 |
|           2800 |        2 |
|           5000 |        1 |
|           NULL |        2 |
|            790 |        1 |
+----------------+----------+
6 rows in set (0.00 sec)

GROUP BY書寫位置

GROUP BY的子句書寫順序有嚴格要求，不按要求會導緻SQL無法正常執行，目前出現過的子句順序為：

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

其中前三項用于篩選資料，GROUP BY對篩選出的資料進行處理

在WHERE子句中使用GROUP BY

SELECT purchase_price, COUNT(*)
  FROM product
 WHERE product_type = '衣服'
 GROUP BY purchase_price;
+----------------+----------+
| purchase_price | COUNT(*) |
+----------------+----------+
|            500 |        1 |
|           2800 |        1 |
+----------------+----------+
2 rows in set (0.00 sec)

6.2 常見錯誤

在使用聚合函數及GROUP BY子句時，經常出現的錯誤有：

在聚合函數的SELECT子句中寫了聚合健以外的列使用COUNT等聚合函數時，SELECT子句中如果出現列名，隻能是GROUP BY子句中指定的列名（也就是聚合鍵）。
在GROUP BY子句中使用列的别名SELECT子句中可以通過AS來指定别名，但在GROUP BY中不能使用别名。因為在DBMS中 ,SELECT子句在GROUP BY子句後執行。
在WHERE中使用聚合函數原因是聚合函數的使用前提是結果集已經确定，而WHERE還處于确定結果集的過程中，是以互相沖突會引發錯誤。如果想指定條件，可以在SELECT，HAVING（下面馬上會講）以及ORDER BY子句中使用聚合函數。

七、為聚合結果指定條件

7.1 用HAVING得到特定分組

将表使用GROUP BY分組後，怎樣才能隻取出其中兩組？

這裡WHERE不可行，因為，WHERE子句隻能指定記錄（行）的條件，而不能用來指定組的條件（例如，“資料行數為 2 行”或者“平均值為 500”等）。

可以在GROUP BY後使用HAVING子句。

HAVING的用法類似WHERE

7.2 HAVING特點

HAVING子句用于對分組進行過濾，可以使用數字、聚合函數和GROUP BY中指定的列名（聚合鍵）。

-- 數字
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type
HAVING COUNT(*) = 2;
+--------------+----------+
| product_type | COUNT(*) |
+--------------+----------+
| 衣服         |        2 |
| 辦公用品     |        2 |
+--------------+----------+
2 rows in set (0.00 sec)

-- 錯誤形式（因為product_name不包含在GROUP BY聚合鍵中）
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type
HAVING product_name = '原子筆';

八、對查詢結果進行排序

8.1 ORDER BY

SQL中的執行結果是随機排列的，當需要按照特定順序排序時，可以使用 ORDER BY 子句。

SELECT <列名1>, <列名2>, <列名3>, ……
  FROM <表名>
 ORDER BY <排序基準列1>, <排序基準列2>,

預設為升序排列，降序排列為DESC

-- 降序排列
SELECT product_id, product_name, sale_price, purchase_price
  FROM product
 ORDER BY sale_price DESC;
+------------+--------------+------------+----------------+
| product_id | product_name | sale_price | purchase_price |
+------------+--------------+------------+----------------+
| 0005       | 高壓鍋       |       6800 |           5000 |
| 0003       | 運動T恤      |       4000 |           2800 |
| 0004       | 菜刀         |       3000 |           2800 |
| 0001       | T恤衫        |       1000 |            500 |
| 0007       | 擦菜闆       |        880 |            790 |
| 0002       | 打孔器       |        500 |            320 |
| 0006       | 叉子         |        500 |           NULL |
| 0008       | 原子筆       |        100 |           NULL |
+------------+--------------+------------+----------------+

-- 多個排序鍵
SELECT product_id, product_name, sale_price, purchase_price
  FROM product
 ORDER BY sale_price, product_id;
+------------+--------------+------------+----------------+
| product_id | product_name | sale_price | purchase_price |
+------------+--------------+------------+----------------+
| 0008       | 原子筆       |        100 |           NULL |
| 0002       | 打孔器       |        500 |            320 |
| 0006       | 叉子         |        500 |           NULL |
| 0007       | 擦菜闆       |        880 |            790 |
| 0001       | T恤衫        |       1000 |            500 |
| 0004       | 菜刀         |       3000 |           2800 |
| 0003       | 運動T恤      |       4000 |           2800 |
| 0005       | 高壓鍋       |       6800 |           5000 |
+------------+--------------+------------+----------------+
8 rows in set (0.00 sec)

-- 當用于排序的列名中含有NULL時，NULL會在開頭或末尾進行彙總。
SELECT product_id, product_name, sale_price, purchase_price
  FROM product
 ORDER BY purchase_price;
+------------+--------------+------------+----------------+
| product_id | product_name | sale_price | purchase_price |
+------------+--------------+------------+----------------+
| 0006       | 叉子         |        500 |           NULL |
| 0008       | 原子筆       |        100 |           NULL |
| 0002       | 打孔器       |        500 |            320 |
| 0001       | T恤衫        |       1000 |            500 |
| 0007       | 擦菜闆       |        880 |            790 |
| 0003       | 運動T恤      |       4000 |           2800 |
| 0004       | 菜刀         |       3000 |           2800 |
| 0005       | 高壓鍋       |       6800 |           5000 |
+------------+--------------+------------+----------------+

8.2 ORDER BY中列名可使用别名

前文講GROUP BY中提到，GROUP BY 子句中不能使用SELECT 子句中定義的别名，但是在 ORDER BY 子句中卻可以使用别名。為什麼在GROUP BY中不可以而在ORDER BY中可以呢？

這是因為SQL在使用 HAVING 子句時 SELECT 語句的順序為：

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

其中SELECT的執行順序在 GROUP BY 子句之後，ORDER BY 子句之前。也就是說，當在ORDER BY中使用别名時，已經知道了SELECT設定的别名存在，但是在GROUP BY中使用别名時還不知道别名的存在，是以不能在ORDER BY中可以使用别名，但是在GROUP BY中不能使用别名

8.3 ORDER BY 排序列中存在 NULL 時，指定其出現在首行或者末行的方式

在MySQL中，

NULL

值被認為比任何

非NULL

值低，是以，當順序為 ASC（升序）時，

NULL

值出現在第一位，而當順序為 DESC（降序）時，則排序在最後。

如果想指定存在

NULL

的行出現在首行或者末行，需要特殊處理。

使用如下代碼建構示例表：

這裡可以參考

AUTO_INCREMENT

的解釋：

CREATE TABLE user (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(5),
    date_login DATE,
    PRIMARY KEY (id)
);
-- 創表成功
mysql> desc user;
+------------+------------+------+-----+---------+----------------+
| Field      | Type       | Null | Key | Default | Extra          |
+------------+------------+------+-----+---------+----------------+
| id         | int        | NO   | PRI | NULL    | auto_increment |
| name       | varchar(5) | YES  |     | NULL    |                |
| data_login | date       | YES  |     | NULL    |                |
+------------+------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

INSERT INTO user(name, date_login) VALUES
(NULL,    '2017-03-12'), 
('john',   NULL), 
('david', '2016-12-24'), 
('zayne', '2017-03-02');

mysql> SELECT * FROM user;;
+----+-------+------------+
| id | name  | data_login |
+----+-------+------------+
|  1 | NULL  | 2017-03-12 |
|  2 | john  | NULL       |
|  3 | david | 2016-12-24 |
|  4 | zayne | 2017-03-02 |
+----+-------+------------+
4 rows in set (0.00 sec)

既然排序時，

NULL

的值比

非NULL

值低（可以了解為

或者

-∞

），那麼我們在排序時就要對這個預設情況進行特殊處理以達到想要的效果。

一般有如下兩種需求：

（1）将 NULL 值排在末行，同時将所有非NULL 值按升序排列。

對于數字或者日期類型，可以在排序字段前添加一個負号（minus）來得到反向排序。（

-1、-2、-3....-∞

）

對于字元型或者字元型數字，此方法不一定能得到期望的排序結果，可以使用

IS NULL

比較運算符。另外

ISNULL( )

函數等同于使用

IS NULL

比較運算符。

還可以使用

COALESCE

函數實作需求

（2）将 NULL 值排在首行，同時将所有非NULL 值按倒序排列。

對于數字或者日期類型，可以在排序字段前添加一個負号（minus）來實作。（

-∞...-3、-2、-1

）

對于字元型或者字元型數字，此方法不一定能得到期望的排序結果，可以使用

IS NOT NULL

比較運算符。另外

!ISNULL( )

函數等同于使用

IS NOT NULL

比較運算符。

還可以使用

COALESCE

函數實作需求

九、作業二

9.1

請指出下述SELECT語句中所有的文法錯誤。

SELECT product_id, SUM(product_name)
--本SELECT語句中存在錯誤。
  FROM product 
 GROUP BY product_type 
 WHERE regist_date > '2009-09-01';

錯誤：

（1）聚合函數

SUM

用來計算表中數值列中資料的合計值，而這裡的

product_name

不是數值，如果除去

WHERE

運作如下：

mysql> SELECT product_id, SUM(product_name)
    ->   FROM product
    ->  GROUP BY product_type ;
+------------+-------------------+
| product_id | SUM(product_name) |
+------------+-------------------+
| 0001       |                 0 |
| 0002       |                 0 |
| 0004       |                 0 |
+------------+-------------------+

（2）GROUP BY的子句書寫順序有嚴格要求，不按要求會導緻SQL無法正常執行，目前出現過的子句順序為：

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

（3）在SELECT聚合函數子句中寫了聚合鍵以外的列。

報錯：

ERROR 1054 (42S22): Unknown column 'SUM（product_name）' in 'field list'

可以回顧：

SELECT purchase_price, COUNT(*)
  FROM product
 WHERE product_type = '衣服'
 GROUP BY purchase_price;
+----------------+----------+
| purchase_price | COUNT(*) |
+----------------+----------+
|            500 |        1 |
|           2800 |        1 |
+----------------+----------+
2 rows in set (0.00 sec)

9.2

請編寫一條SELECT語句，求出銷售單價（

sale_price

列）合計值大于進貨單價（

purchase_price

列）合計值1.5倍的商品種類。執行結果如下所示。

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

因為是求滿足一個條件的商品種類，是以需要先用

GROUP BY

進行分組（各個種類），然後通過

HAVING

對分組進行條件過濾。

方法一：

mysql> SELECT product_type, SUM(sale_price) AS sum, SUM(purchase_price) AS sum
    -> FROM product
    -> GROUP BY product_type
    -> HAVING SUM(sale_price) >= 1.5 * SUM(purchase_price);
+--------------+------+------+
| product_type | sum  | sum  |
+--------------+------+------+
| 衣服         | 5000 | 3300 |
| 辦公用品     |  600 |  320 |
+--------------+------+------+
2 rows in set (0.00 sec)

9.3

此前我們曾經使用SELECT語句選取出了product（商品）表中的全部記錄。當時我們使用了

ORDER BY

子句來指定排列順序，但現在已經無法記起當時如何指定的了。請根據下列執行結果，思考

ORDER BY

子句的内容。

如果單純按照

regist_data

降序排序，結果如下：

mysql> SELECT *
    -> FROM product
    -> ORDER BY regist_date DESC;
+------------+--------------+--------------+------------+----------------+-------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+------------+--------------+--------------+------------+----------------+-------------+
| 0008       | 原子筆       | 辦公用品     |        100 |           NULL | 2009-11-11  |
| 0001       | T恤衫        | 衣服         |       1000 |            500 | 2009-09-20  |
| 0004       | 菜刀         | 廚房用具     |       3000 |           2800 | 2009-09-20  |
| 0006       | 叉子         | 廚房用具     |        500 |           NULL | 2009-09-20  |
| 0002       | 打孔器       | 辦公用品     |        500 |            320 | 2009-09-11  |
| 0005       | 高壓鍋       | 廚房用具     |       6800 |           5000 | 2009-01-15  |
| 0007       | 擦菜闆       | 廚房用具     |        880 |            790 | 2008-04-28  |
| 0003       | 運動T恤      | 衣服         |       4000 |           2800 | NULL        |
+------------+--------------+--------------+------------+----------------+-------------+
8 rows in set (0.00 sec)

會發現和題目要求的不太一樣，比如

regist_date

為

NULL

的哪一行在最後一行了；并且當

regist_date

相同時則以

sale_price

進行升序排序：