DW学习--MySQL03（复杂查询）

1 视图

1.1 什么是视图

• 什么是视图：视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的。所以操作视图时会根据创建视图的SELECT语句生成一张虚拟表，然后在这张虚拟表上做SQL操作。

• 视图和表的区别：核心是“是否保存了实际的数据”，视图不会将数据保存再任何地方。

  表中保存的是实际数据，视图保存的是从表中取出数据 所使用的SELECT语句。

  • 视图和表的关系图：
  • 视图不是表，视图是虚表，视图依赖于表。

• 视图存在的原因：

  • 视图不保存数据，因此可以节省存储容量
  • 可以将频繁使用的SELECT语句保存成视图来提高效率
  • 通过定义视图可以使用户看到的数据更加清晰
  • 通过定义视图可以不对外公开数据表全部字段，增强数据的保密性

  注意：所谓的“参照视图”就是“执行SELECT语句”的意思

1.2 创建使用视图

• 创建视图：

  • 基本语法：CREATE VIEW <视图名>(<列名1>,<列名2>,...) AS <SELECT语句>

  注意：
  1.SELECT 语句中列的排列顺序和视图中列的排列顺序相同，且视图的列名是在视图名称之后的列表中定义的
  2.视图名在数据库中需要是唯一的，不能与其他视图和表重名

  • 注意：在一般的DBMS中定义视图时不能使用ORDER BY语句（因为 MySQL中视图的定义是允许使用 ORDER BY语句的，但是若从特定视图进行选择，而该视图使用了自己的 ORDER BY语句，则视图定义中的 ORDER BY将被忽略。）
  • 【例子1】基于单表的视图
    

• 使用视图：

  • 1.首先执行定义视图的 SELECT语句
  • 2.根据得到的结果，再执行在 FROM子句中使用视图的 SELECT语句
    【例子1】使用基于单表的视图
    
    
    在工作中需要频繁进行汇总时，就不用每次都使用 GROUP BY 和 COUNT函数写SELECT语句来从 Product 表中取得数据了。创建出视图之后，就可以通过非常简单的SELECT 语句，随时得到想要的汇总结果。

1.3 修改视图

• 修改视图结构：ALTER VIEW <视图名> AS <SELECT语句>
  【例子】将视图修改为日期在2009-9-11以后的商品

     ALTER VIEW productsumAS SELECT product_type,sale_priceFROM ProductWHERE regist_date > '2009-09-11';
  

  
• 更新视图内容：
  视图是一个虚拟表，所以对视图的操作就是对底层基础表的操作。如果原表可以更新，那么 视图中的数据也可以更新。反之亦然。如果视图发生了改变，而原表没有进行相应更新的话，就无法保证数据的一致性了。
  • 定义视图的SELECT语句满足的条件：(大多和聚合有关)
    • SELECT未使用DISTINCT
    • FROM子句中只有一张表
    • 未使用GROUP BY子句
    • 未使用HAVING子句
    • 未使用聚合函数SUM、MIN、MAX、COUNT等
      【例子】错误的例子

  INSERT INTO productsum VALUES ('电器制品', 5);
  

  上述语句会出错，因为视图productsum是因为通过GROUP BY和COUNT汇总得到的，这时视图里电器制品的数量为5，但是在原表中不清楚这5条数据具体是什么
  
• 删除视图：使用DROP VIEW <视图名>[<视图列名1>,<视图列名2>,...]

2 子查询

• 子查询：一个查询语句嵌套在另一个查询语句内部的查询。
• 子查询和视图的关系：子查询就是一次性视图（SELECT语句），将用来定义视图的SELECT语句直接用于FROM子句当中。与视图不同，子查询在SELECT语句执行完毕之后就会消失。
• 【例子】将上面的视图写成子查询形式
  
  子查询就是将用来定义视图的 SELECT语句直接用于FROM子句当中。虽然“AS ProductSum”就是子查询的名称，但由于该名称是一次性的，在SELECT语句执行之后就消失了。
  

  #  ①首先执行 FROM 子句中的 SELECT 语句（子查询）
  SELECT product_type, COUNT(*) AS cnt_product
  FROM Product
  GROUP BY product_type;
  # 根据①的结果执行外层的 SELECT 语句
  SELECT product_type, cnt_product
  FROM ProductSum;
  

注意：使用子查询的SQL会从子查询开始执行。

• 标量子查询（scalar subquery）：标量就是单一，标量子查询也就是单一的子查询，是必须而且只能返回 1 行 1列的结果。如“10”，“菜刀”这种。
  • 在WHERE子句中使用标量子查询：
    因为WHERE子句中不允许使用聚合函数，所以下面是错的。
    
    但是使用子查询可以很好解决这个问题：

  SELECT product_id, product_name, sale_price
  FROM Product
  WHERE sale_price >(SELECT AVG(sale_price) FROM Product);
  

  注意：这里标量子查询的返回值是一个数据，满足标量子查询。

  • 标量子查询的书写位置：通常任何可以使用单一值的位置都可以使用，能够使用常数或者列名的地方，无论是 SELECT 子句、GROUP BY 子句、HAVING子句，还是ORDER BY子句，几乎所有的地方都可以使用。
    【例子】判断结果

  SELECT product_id,product_name,sale_price,(SELECT AVG(sale_price)FROM product) AS avg_priceFROM product;
  

  会发生犯错误，因为该子查询返回了多行结果，因此SELECT语句不能执行。

  注意：标量查询绝对不能返回多行结果，否则它就是普通的子查询，不能用在只需要单一输入值的运算中。

• 关联子查询：用来过滤数据
  • 【例子】上面那个错误的语句怎么写对呢？
    
    在子查询中添加的 WHERE 子句的条件。该条件的意思就是，在同一商品种类中对各商品的销售单价和平均单价进行比较。
  • 使用关联子查询时，需要在表所在的列名之前加上表的别名，以“<表名>.<列名>”
  • 关联子查询也是对集合进行切分的，和GROUP BY基本相同。
    
    首先需要计算各个商品种类中商品的平均销售单价，由于该单价会用来和商品表中的各条记录进行比较，因此关联子查询实际只能返回 1行结果。
  • 关联子查询执行时，DBMS 内部的执行情况如图:
    
    如果商品种类发生了变化，那么用来进行比较的平均单价也会发生变化，这样就可以将各种商品的销售单价和平均单价进行比较了.

  注意：结合条件一定要写在子查询中，即WHERE P1.product_type = P2.product_type一定不能写在外面，这和关联名称的作用域有关。

• 关联子查询和子查询的关系：
  • 关联名称的作用域：关联名称存在一个有效范围的限制，子查询内部设定的关联名称，只能在该子查询内部使用。即“内部可以看到外部，而外部看不到内部”。
  • （SQL是按照先内层子查询后外层查询的顺序来执行的。这样，子查询执行结束时只会留下执行结果，作为抽出源的 P2 表其实已经不存在了。因此，在执行外层查询时，由于 P2 表已经不存在了，因此就会返回“不存在使用该名称的表”这样的错误。）
    

3 习题1

1.创建出满足下述三个条件的视图（视图名称为 ViewPractice5_1）。使用 product（商品）表作为参照表，假设表中包含初始状态的 8 行数据。

• 条件 1：销售单价大于等于 1000 日元。
• 条件 2：登记日期是 2009 年 9 月 20 日。
• 条件 3：包含商品名称、销售单价和登记日期三列。
  对该视图执行 SELECT 语句的结果如下所示。

SELECT * FROM ViewPractice5_1;

执行结果

product_name | sale_price | regist_date
--------------+------------+------------
T恤衫         |   1000    | 2009-09-20
菜刀          |    3000    | 2009-09-20

• 答案：

  CREATE VIEW ViewPractices5_1 
  AS
  SELECT  product_name,sale_price,regist_dateFROM Product
  WHERE sale_price>=1000AND regist_date='2009-09-20' ;
  SELECT* FROM ViewPractices5_1 
  

2.向习题一中创建的视图 ViewPractice5_1 中插入如下数据，会得到什么样的结果呢？

INSERT INTO ViewPractice5_1 VALUES (' 刀子 ', 300, '2009-11-02');

• 答案：会发生错误。对视图的更新其实是对视图对应的表的更新，而在表中，product_id（商品编号）、product_name（商品名称）、product_type（商品种类）3列在表定义时都有NOT NULL约束，而插入的数据只对三列进行赋值，而对于product_id和product_type等其他列自动插入的是NULL数值，因此INSERT语句无法执行。

3.请根据如下结果编写 SELECT 语句，其中 sale_price_all 列为全部商品的平均销售单价。

product_id | product_name | product_type | sale_price | sale_price_all
------------+-------------+--------------+------------+---------------------
0001       | T恤衫         | 衣服         | 1000       | 2097.5000000000000000
0002       | 打孔器        | 办公用品      | 500        | 2097.5000000000000000
0003       | 运动T恤       | 衣服          | 4000      | 2097.5000000000000000
0004       | 菜刀          | 厨房用具      | 3000       | 2097.5000000000000000
0005       | 高压锅        | 厨房用具      | 6800       | 2097.5000000000000000
0006       | 叉子          | 厨房用具      | 500        | 2097.5000000000000000
0007       | 擦菜板        | 厨房用具       | 880       | 2097.5000000000000000
0008       | 圆珠笔        | 办公用品       | 100       | 2097.5000000000000000

• 答案：

  SELECT product_id,product_name,product_type,sale_price,(SELECT AVG(sale_price) FROM Product)AS sale_price_all
  FROM Product; 
  

• 错误答案：

  SELECT product_id,product_name,product_type,sale_price,AVG(sale_price) AS sale_price_all
  FROM Product;
  

  AVG是一个聚合函数，使用聚合函数时，SELECT子句中有很多限制条件，上面的代码犯了–在SELECT子句中写了多余的列，聚合成只有一条数据。
  结果：

  product_id | product_name | product_type | sale_price | sale_price_all
  ------------+-------------+--------------+------------+---------------------
  0001       | T恤衫         | 衣服         | 1000       | 2097.5000000000000000
  

4.请根据习题一中的条件编写一条 SQL 语句，创建一幅包含如下数据的视图（名称为AvgPriceByType）。

product_id | product_name | product_type | sale_price | avg_sale_price
------------+-------------+--------------+------------+---------------------
0001       | T恤衫         | 衣服         | 1000       |2500.0000000000000000
0002       | 打孔器         | 办公用品     | 500        | 300.0000000000000000
0003       | 运动T恤        | 衣服        | 4000        |2500.0000000000000000
0004       | 菜刀          | 厨房用具      | 3000        |2795.0000000000000000
0005       | 高压锅         | 厨房用具     | 6800        |2795.0000000000000000
0006       | 叉子          | 厨房用具      | 500         |2795.0000000000000000
0007       | 擦菜板         | 厨房用具     | 880         |2795.0000000000000000
0008       | 圆珠笔         | 办公用品     | 100         | 300.0000000000000000

• 答案：

  # 创建视图的语句
  CREATE VIEW AvgPriceByType AS
  SELECT  product_id,product_name,product_type,sale_price,(SELECT AVG(sale_price)FROM Product P2WHERE P1.product_type = P2.product_typeGROUP BY P2.product_type) AS avg_sale_price
  FROM Product P1;
  

4 函数

• 函数：就是输入某一值得到相应输出结果的功能，输入值称为参数（parameter），输出值称为返回值。
• 函数的种类：
  • 算术函数 （用来进行数值计算的函数）
  • 字符串函数 （用来进行字符串操作的函数）
  • 日期函数 （用来进行日期操作的函数）
  • 转换函数 （用来转换数据类型和值的函数）
  • 聚合函数 （用来进行数据聚合的函数）

4.1 算术函数

• 基本函数：加减乘除
• ABS函数：绝对值（absolute value）函数，语法：ABS(数值)。当ABS函数的参数为NULL时，返回值也为NULL。
• MOD函数：求余（modulo ）函数，除法求余，语法：MOD(被除数，除数)，小数没有余数的概念，只能对整数类型的列使用。
• ROUND函数：四舍五入，语法：ROUND(对象数值，保留小数的位数)
  【例子】：

SELECT m,ABS(m) AS abs_col,n, p,MOD(n, p) AS mod_col,ROUND(m,1)AS round_col
FROM samplemath;

+----------+---------+------+------+---------+-----------+
| m        | abs_col | n    | p    | mod_col | round_col |
+----------+---------+------+------+---------+-----------+
|  500.000 | 500.000 |    0 | NULL |    NULL |     500.0 |
| -180.000 | 180.000 |    0 | NULL |    NULL |    -180.0 |
|     NULL |    NULL | NULL | NULL |    NULL |      NULL |
|     NULL |    NULL |    7 |    3 |       1 |      NULL |
|     NULL |    NULL |    5 |    2 |       1 |      NULL |
|     NULL |    NULL |    4 | NULL |    NULL |      NULL |
|    8.000 |   8.000 | NULL |    3 |    NULL |       8.0 |
|    2.270 |   2.270 |    1 | NULL |    NULL |       2.3 |
|    5.555 |   5.555 |    2 | NULL |    NULL |       5.6 |
|     NULL |    NULL |    1 | NULL |    NULL |      NULL |
|    8.760 |   8.760 | NULL | NULL |    NULL |       8.8 |
+----------+---------+------+------+---------+-----------+

4.2 字符串函数

• CONCAT函数：拼接函数，语法：CONCAT(str1,str2,str3)
• LENGTH函数：字符串的函数，语法：LENGTH(字符串)
• LOWER函数：小写转换，LOWER 函数只能针对英文字母使用，它会将参数中的字符串全都转换为小写。UPPER函数用于大写转换。
• REPLACE函数：字符串的替换，语法：REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )
  【例子】：

  SELECT str1, str2, str3,
  REPLACE(str1, str2, str3) AS rep_str
  FROM SampleStr;
  

   str1  | str2 | str3 | rep_str
  ----------+------+------+---------opx  | rt  |   |abc  | def  |   |山田  | 太郎  | 是我  | 山田aaa  |   |   || xyz|   |
  @!#$%  |   |   |ABC  |   |   |aBC  |   |   |
  abc太郎 | abc  | ABC  | ABC太郎
  abcdefabc| abc  | ABC  | ABCdefABC
  micmic  | i  | I  | mIcmIc
  

• SUBSTRING函数：字符串的截取，语法：SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）。使用SUBSTRING函数 可以截取出字符串中的一部分字符串。截取的起始位置从字符串最左侧开始计算，索引值起始为1。
  【例子】：

  SELECT str1,SUBSTRING(str1 FROM 3 FOR 2) AS sub_str
  FROM SampleStr
  

    	str1  | sub_str
  ----------+--------opx  | xabc  | c山田 |aaa  | a|@!#$%  | #$ABC  | CaBC  | C
  abc太郎  | c太
  abcdefabc | cdmicmic  | cm
  

4.3 日期函数

• CURRENT_DATE：获取当前日期，没有参数，不用括号。

  SELECT CURRENT_DATE;
  

  +--------------+
  | CURRENT_DATE |
  +--------------+
  | 2020-08-08   |
  +--------------+
  1 row in set (0.00 sec)
  

• CURRENT_TIME：当前时间

  SELECT CURRENT_TIME;
  

  +--------------+
  | CURRENT_TIME |
  +--------------+
  | 17:26:09     |
  +--------------+
  1 row in set (0.00 sec)
  

• CURRENT_TIMESTAMP ：当前日期和时间

  SELECT CURRENT_TIMESTAMP;
  

  +---------------------+
  | CURRENT_TIMESTAMP   |
  +---------------------+
  | 2020-08-08 17:27:07 |
  +---------------------+
  1 row in set (0.00 sec)
  

• EXTRACT：截取日期元素，语法：EXTRACT(日期元素 FROM 日期)，该函数的返回值并不是日期类型而是数值类型。

  SELECT CURRENT_TIMESTAMP as now,
  EXTRACT(YEAR   FROM CURRENT_TIMESTAMP) AS year,
  EXTRACT(MONTH  FROM CURRENT_TIMESTAMP) AS month,
  EXTRACT(DAY    FROM CURRENT_TIMESTAMP) AS day,
  EXTRACT(HOUR   FROM CURRENT_TIMESTAMP) AS hour,
  EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS MINute,
  EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;
  

  +---------------------+------+-------+------+------+--------+--------+
  | now                 | year | month | day  | hour | MINute | second |
  +---------------------+------+-------+------+------+--------+--------+
  | 2020-08-08 17:34:38 | 2020 |     8 |    8 |   17 |     34 |     38 |
  +---------------------+------+-------+------+------+--------+--------+
  1 row in set (0.00 sec)
  

4.4 转换函数

• 转换：一是数据类型的转换，简称为类型转换，在英语中称为cast；另一层意思是值的转换。
• CAST：类型转换，语法：CAST（转换前的值 AS 想要转换的数据类型）
  【例子】

  -- 将字符串类型转换为数值类型
  SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;
  

  +---------+
  | int_col |
  +---------+
  |       1 |
  +---------+
  1 row in set (0.00 sec)
  

  -- 将字符串类型转换为日期类型
  SELECT CAST('2009-12-14' AS DATE) AS date_col;
  

  +------------+
  | date_col   |
  +------------+
  | 2009-12-14 |
  +------------+
  1 row in set (0.00 sec)
  

• COALESCE ：将NULL转换为其他值,语法：语法：COALESCE(数据1，数据2，数据3……)。COALESCE是 SQL 特有的函数。该函数会返回可变参数 A 中左侧开始第 1个不是NULL的值。参数个数是可变的，因此可以根据需要无限增加。

  SELECT COALESCE(NULL, 11) AS col_1,COALESCE(NULL, 'hello world', NULL) AS col_2,COALESCE(NULL, NULL, '2020-11-01') AS col_3;
  

  +-------+-------------+------------+
  | col_1 | col_2       | col_3      |
  +-------+-------------+------------+
  |    11 | hello world | 2020-11-01 |
  +-------+-------------+------------+
  1 row in set (0.00 sec)
  

5 谓词

• 谓词：就是返回值为真值的函数。包括TRUE / FALSE / UNKNOWN。
• 主要的谓词：LIKE、BETWEEN、IS NULL、IS NOT NULL、IN、EXISTS
• LIKE谓词：用于字符串的部分一致查询，部分一致大体可以分为前方一致、中间一致和后方一致三种类型。 **%是代表“零个或多个任意字符串”的特殊符号**
  • 前方一致：选取出“dddabc”

  SELECT *
  FROM samplelike
  WHERE strcol LIKE 'ddd%';
  

  +--------+
  | strcol |
  +--------+
  | dddabc |
  +--------+
  1 row in set (0.00 sec)
  

  • 中间一致：选取出“abcddd”, “dddabc”, “abdddc”

  SELECT *
  FROM samplelike
  WHERE strcol LIKE '%ddd%';
  

  +--------+
  | strcol |
  +--------+
  | abcddd |
  | abdddc |
  | dddabc |
  +--------+
  3 rows in set (0.00 sec)
  

  • 后方一致：%ddd
  • _下划线匹配任意 1 个字符:使用 _（下划线）来代替 %，与 % 不同的是，它代表了“任意 1 个字符”。

  SELECT *
  FROM samplelike
  WHERE strcol LIKE 'abc__';
  

  +--------+
  | strcol |
  +--------+
  | abcdd  |
  +--------+
  1 row in set (0.00 sec)
  

• BETWEEN谓词：用于范围查询，与其他谓词或者函数的不同之处在于它使用了 3 个参数。BETWEEN的特点就是结果中会包含 100 和 1000 这两个临界值，也就是闭区间。如果不想让结果中包含临界值，那就必须使用 < 和 >。

  -- 选取销售单价为100～ 1000元的商品
  SELECT product_name, sale_price
  FROM product
  WHERE sale_price BETWEEN 100 AND 1000;
  

  +--------------+------------+
  | product_name | sale_price |
  +--------------+------------+
  | T恤          |       1000 |
  | 打孔器       |        500 |
  | 叉子         |        500 |
  | 擦菜板       |        880 |
  | 圆珠笔       |        100 |
  +--------------+------------+
  5 rows in set (0.00 sec)
  

• IS NULL、 IS NOT NULL ：用于判断是否为NULL.

  SELECT product_name, purchase_price
  FROM product
  WHERE purchase_price IS NULL;
  

  +--------------+----------------+
  | product_name | purchase_price |
  +--------------+----------------+
  | 叉子         |           NULL |
  | 圆珠笔       |           NULL |
  +--------------+----------------+
  2 rows in set (0.00 sec)
  

• IN谓词：OR的简便用法，也可使用NOT IN

  SELECT product_name, purchase_price
  FROM product
  WHERE purchase_price IN (320, 500, 5000);
  /*
  WHERE purchase_price = 320
  OR purchase_price = 500
  OR purchase_price = 5000;
  */
  

  +--------------+----------------+
  | product_name | purchase_price |
  +--------------+----------------+
  | T恤          |            500 |
  | 打孔器       |            320 |
  | 高压锅       |           5000 |
  +--------------+----------------+
  3 rows in set (0.00 sec)
  

注意：在使用IN 和 NOT IN 时是无法选取出NULL数据的。

• 使用子查询作为IN谓词的参数
  • IN和子查询：
    【例子】取出大阪在售商品的销售单价
    步骤1：取出大阪门店的在售商品 product_id ;
    步骤2：取出大阪门店在售商品的销售单价 sale_price；

  -- step1：取出大阪门店的在售商品 `product_id`
  SELECT product_id
  FROM shopproduct
  WHERE shop_id = '000C';
  +------------+| product_id |
  +------------+
  | 0003       |
  | 0004       |
  | 0006       |
  | 0007       |
  +------------+
  4 rows in set (0.00 sec)
  

  使用上述语句作为第二步的查询条件来使用。

  -- step2：取出大阪门店在售商品的销售单价 `sale_price`SELECT product_name, sale_price
  FROM product
  WHERE product_id IN (SELECT product_idFROM shopproductWHERE shop_id = '000C');
  +--------------+------------+
  | product_name | sale_price |
  +--------------+------------+
  | 运动T恤      |       4000 |
  | 菜刀         |       3000 |
  | 叉子         |        500 |
  | 擦菜板       |        880 |
  +--------------+------------+
  4 rows in set (0.00 sec)
  

  子查询是从最内层开始执行的（由内而外）

  -- 子查询展开后的结果
  SELECT product_name, sale_price
  FROM product
  WHERE product_id IN ('0003', '0004', '0006', '0007');
  +--------------+------------+
  | product_name | sale_price |
  +--------------+------------+
  | 运动T恤      |       4000 |
  | 菜刀         |       3000 |
  | 叉子         |        500 |
  | 擦菜板       |        880 |
  +--------------+------------+
  4 rows in set (0.00 sec)
  

  • 使用子查询的原因：
    ①：实际生活中，某个门店的在售商品是不断变化的，使用 in 谓词就需要经常更新 sql 语句，降低了效率，提高了维护成本；
    ②：实际上，某个门店的在售商品可能有成百上千个，手工维护在售商品编号是个大工程。
    使用子查询即可保持 sql 语句不变。
  • NOT和子查询：和上面一样用法。
• EXIST 谓词
  • 作用：如果存在这样的记录就返回真（TRUE），如果不存在就返回假（FALSE）。EXIST（存在）谓词的主语是“记录”
  • EXIST的参数：只需要在右侧书写 1 个参数，该参数通常都会是一个子查询。
  • 子查询中的SELECT *：EXIST 只关心记录是否存在，因此返回哪些列都没有关系。
  • 使用NOT EXIST替换NOT IN

6 CASE表达式

• CASE表达式分类：在区分情况时使用，CASE表达式分为简单CASE表达式和搜索CASE表达式两种。搜索CASE表达式包含简单CASE表达式的全部功能。
• 搜索CASE表达式：
  • 语法：

  CASE WHEN <求值表达式> THEN <表达式>WHEN <求值表达式> THEN <表达式>WHEN <求值表达式> THEN <表达式>...
  ELSE <表达式>
  END
  

  • 执行顺序：依次判断when表达式是否为真值，是则执行THEN 后的语句，如果所有的 when表达式均为假，则执行ELSE 后的语句。
• 使用方法：
  • 【例子1】根据不同分支得到不同列值

  SELECT  product_name,CASE WHEN product_type = '衣服' THEN CONCAT('A ： ',product_type)WHEN product_type = '办公用品'  THEN CONCAT('B ： ',product_type)WHEN product_type = '厨房用具'  THEN CONCAT('C ： ',product_type)ELSE NULLEND AS abc_product_type
  FROM  product;
  

  +--------------+------------------+
  | product_name | abc_product_type |
  +--------------+------------------+
  | T恤          | A ： 衣服        |
  | 打孔器       | B ： 办公用品    |
  | 运动T恤      | A ： 衣服        |
  | 菜刀         | C ： 厨房用具    |
  | 高压锅       | C ： 厨房用具    |
  | 叉子         | C ： 厨房用具    |
  | 擦菜板       | C ： 厨房用具    |
  | 圆珠笔       | B ： 办公用品    |
  +--------------+------------------+
  8 rows in set (0.00 sec)
  

  注意： CASE 表达式最后的“END”是不能省略的

  • 【例子2】实现列方向上的聚合
    行方向上的聚合，用SUM

  SELECT product_type,SUM(sale_price) AS sum_price
  FROM product
  GROUP BY product_type;  
  +--------------+-----------+
  | product_type | sum_price |
  +--------------+-----------+
  | 衣服         |      5000 |
  | 办公用品     |       600 |
  | 厨房用具     |     11180 |
  +--------------+-----------+
  3 rows in set (0.00 sec)
  

  聚合函数 +CASE WHEN 表达式即可实现该效果

  SELECT SUM(CASE WHEN product_type = '衣服' THEN sale_price ELSE 0 END) AS sum_price_clothes,SUM(CASE WHEN product_type = '厨房用具' THEN sale_price ELSE 0 END) AS sum_price_kitchen,SUM(CASE WHEN product_type = '办公用品' THEN sale_price ELSE 0 END) AS sum_price_office
  FROM product;
  

  +-------------------+-------------------+------------------+
  | sum_price_clothes | sum_price_kitchen | sum_price_office |
  +-------------------+-------------------+------------------+
  |              5000 |             11180 |              600 |
  +-------------------+-------------------+------------------+
  1 row in set (0.00 sec)
  

7 习题2

1.运算或者函数中含有 NULL 时，结果全都会变为NULL ？（判断题）

• 答案：是的。

2.对本章中使用的 product（商品）表执行如下 2 条 SELECT 语句，能够得到什么样的结果呢？
①

SELECT product_name, purchase_priceFROM productWHERE purchase_price NOT IN (500, 2800, 5000);

• 答案：

   product_name | purchase_price
  --------------+----------------打孔器  |  320擦菜板  |  790
  

  取出进货单价为500,2800,5000之外的商品，且NOT IN谓词无法与NULL进行比较，所以进货单价为NULL的商品也没出现
  ②

SELECT product_name, purchase_priceFROM product
WHERE purchase_price NOT IN (500, 2800, 5000, NULL);

• 答案：

   product_name | purchase_price
  --------------+----------------
  

  无法取出任何数据，NOT IN的参数中有NULL时，结果通常为空。

2.按照销售单价（ sale_price）对练习 6.1 中的 product（商品）表中的商品进行如下分类。

• 低档商品：销售单价在1000日元以下（T恤衫、办公用品、叉子、擦菜板、 圆珠笔）
• 中档商品：销售单价在1001日元以上3000日元以下（菜刀）
• 高档商品：销售单价在3001日元以上（运动T恤、高压锅）
  请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句，结果如下所示。
  执行结果：

low_price | mid_price | high_price
----------+-----------+------------5 |         1 |         2

• 答案：

  SELECT SUM(CASE WHEN sale_price <= 1000               THEN 1 ELSE 0 END) AS low_price,SUM(CASE WHEN sale_price BETWEEN 1001 AND 3000 THEN 1 ELSE 0 END) AS mid_price,SUM(CASE WHEN sale_price >= 3001 THEN 1 ELSE 0 END) AS high_priceFROM Product;