SQL--Task03 复杂一点的查询

最新推荐文章于 2023-03-13 15:58:19 发布

努摆的皮卡皮

最新推荐文章于 2023-03-13 15:58:19 发布

阅读量320

点赞数 1

分类专栏： SQL 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_68175518/article/details/126502149

版权

SQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

第三章复杂一点的查询

3.1 视图

从SQL的角度来说操作视图与操作表看起来是完全相同的，那么为什么还会有视图的存在呢？视图到底是什么？视图与表有什么不同呢？

3.1.1 什么是视图

视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的，所以操作视图时会根据创建视图的SELECT语句生成一张虚拟表，然后在这张虚拟表上做SQL操作。（通过表动态生成的数据，临时性，是虚拟的结果集）

3.1.2 视图与表有什么区别

视图与表的区别---“是否保存了实际的数据”。所以视图并不是数据库真实存储的数据表，它可以看作是一个窗口，通过这个窗口我们可以看到数据库表中真实存在的数据。

3.1.3 为什么会存在视图

1. 通过定义视图可以将频繁使用的SELECT语句保存以提高效率。

2. 通过定义视图可以使用户看到的数据更加清晰。

3. 通过定义视图可以不对外公开数据表全部字段，增强数据的保密性。

4. 通过定义视图可以降低数据的冗余。

3.1.4 如何创建视图

语法：

CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS <SELECT语句>

其中，SELECT 语句需要书写在 AS 关键字之后。

SELECT 语句中列的排列顺序和视图中列的排列顺序相同， SELECT 语句中的第几列就是视图中的第几列。

视图的列名是在视图名称之后的列表中定义的。

视图名在数据库中需要是唯一的，不能与其他视图和表重名。

*视图不仅可以基于真实表，我们也可以在视图的基础上继续创建视图。虽然在视图上继续创建视图的语法没有错误，但我们还是应该尽量避免这种操作。这是因为对多数 DBMS 来说，多重视图会降低 SQL 的性能。

* 注意：一般的DBMS中定义视图时不能使用ORDER BY语句。因为视图和表一样，数据行都是没有顺序的。

*在MySQL中视图的定义是允许使用 ORDER BY 语句的，但是若从特定视图进行选择，而该视图使用了自己的 ORDER BY 语句，则视图定义中的 ORDER BY 将被忽略。*

基于单表的视图

我们在product表的基础上创建一个视图，如下：

CREATE VIEW productsum (product_type, cnt_product)

AS

SELECT product_type, COUNT(*)

  FROM product

 GROUP BY product_type ;

创建的视图如下图所示：

基于多表的视图

我们再创建一张表 `shop_product`，如下：

CREATE TABLE shop_product

(shop_id    CHAR(4)       NOT NULL,

 shop_name  VARCHAR(200)  NOT NULL,

 product_id CHAR(4)       NOT NULL,

 quantity   INTEGER       NOT NULL,

 PRIMARY KEY (shop_id, product_id));

INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000A',        '东京',          '0001',        30);

INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000A',        '东京',          '0002',        50);

INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000A',        '东京',          '0003',        15);

INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',        '名古屋',        '0002',        30);

INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',        '名古屋',        '0003',        120);

INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',        '名古屋',        '0004',        20);

INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',        '名古屋',        '0006',        10);

INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',        '名古屋',        '0007',        40);

INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C',        '大阪',          '0003',        20);

INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C',        '大阪',          '0004',        50);

INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C',        '大阪',          '0006',        90);

INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C',        '大阪',          '0007',        70);

INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000D',        '福冈',          '0001',        100);

我们在product表和shop_product表的基础上创建视图。

CREATE VIEW view_shop_product(product_type, sale_price, shop_name)

AS

SELECT product_type, sale_price, shop_name

  FROM product,

       shop_product

 WHERE product.product_id = shop_product.product_id;

创建的视图如下图所示

我们可以在这个视图的基础上进行查询

SELECT sale_price, shop_name

  FROM view_shop_product

 WHERE product_type = '衣服';

查询结果为：

3.1.5 如何修改视图结构

语法：

ALTER VIEW <视图名> AS <SELECT语句>

其中视图名在数据库中需要是唯一的，不能与其他视图和表重名。当然也可以通过将当前视图删除然后重新创建的方式达到修改的效果。

* 修改视图

我们修改上方的productSum视图为

ALTER VIEW productSum

AS

        SELECT product_type, sale_price

          FROM Product

         WHERE regist_date > '2009-09-11';

此时productSum视图内容如下图所示

3.1.6 如何更新视图内容

对于一个视图来说，如果包含以下结构的任意一种都是不可以被更新的：

* 聚合函数 SUM()、MIN()、MAX()、COUNT() 等。

* DISTINCT 关键字。

* GROUP BY 子句。

* HAVING 子句。

* UNION 或 UNION ALL 运算符。

* FROM 子句中包含多个表。

即，原表可以更新，则视图中的数据也可以更新。反之亦然，如果视图发生了改变，而原表没有进行相应更新的话，就无法保证数据的一致性了。

*更新视图：

UPDATE productsum

   SET sale_price = '5000'

 WHERE product_type = '办公用品';

此时我们再查看productSum视图，可以发现数据已经更新了

此时观察原表也可以发现数据也被更新了

为什么原表的数据只有一条做了修改呢？因为视图的定义，视图只是原表的一个窗口，所以它修改也只能修改透过窗口能看到的内容。

注意：这里虽然修改成功了，但是并不推荐这种使用方式。而且我们在创建视图时也尽量使用限制不允许通过视图来修改表

3.1.7 如何删除视图

语法：

DROP VIEW <视图名1> [ , <视图名2> …]

注意：需要有相应的权限才能成功删除。

* 删除视图

我们删除刚才创建的productSum视图

DROP VIEW productSum;

如果我们继续操作这个视图的话就会提示当前操作的内容不存在。

3.2 子查询

先来看一个语句

SELECT stu_name

FROM (

         SELECT stu_name, COUNT(*) AS stu_cnt

          FROM students_info

          GROUP BY stu_age) AS studentSum;

其中使用括号括起来的sql语句首先执行，执行成功后再执行外面的sql语句。但是视图也是根据SELECT语句创建视图然后在这个基础上再进行查询。那么什么是子查询呢？子查询和视图又有什么关系呢？

3.2.1 什么是子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。

3.2.2 子查询和视图的关系

子查询就是将用来定义视图的 SELECT 语句直接用于 FROM 子句当中。其中AS studentSum可以看作是子查询的名称，而且由于子查询是一次性的，所以子查询不会像视图那样保存在存储介质中，而是在 SELECT 语句执行之后就消失了。

3.2.3 嵌套子查询

与在视图上再定义视图类似，子查询也没有具体的限制，例如

SELECT product_type, cnt_product

FROM (SELECT *

        FROM (SELECT product_type,

                COUNT(*) AS cnt_product

                FROM product

               GROUP BY product_type) AS productsum

       WHERE cnt_product = 4) AS productsum2;

其中最内层的子查询我们将其命名为productSum，这条语句根据product_type分组并查询个数，第二层查询中将个数为4的商品查询出来，最外层查询product_type和cnt_product两列。

虽然嵌套子查询可以查询出结果，但是随着子查询嵌套的层数的叠加，SQL语句不仅会难以理解而且执行效率也会很差，所以要尽量避免这样的使用。

3.2.4 标量子查询

标量就是单一的意思，就是要求我们执行的SQL语句只能返回一个值，也就是要返回表中具体的某一行的某一列。

*标量子查询有什么用

查询出销售单价高于平均销售单价的商品。

SELECT product_id, product_name, sale_price

  FROM product

 WHERE sale_price > (SELECT AVG(sale_price) FROM product);

上面的这条语句首先后半部分查询出product表中的平均售价，前面的sql语句在根据WHERE条件挑选出合适的商品。

标量子查询不仅仅局限于 WHERE 子句中，通常任何可以使用单一值的位置（常数列名等）都可以使用。

我们还可以这样使用标量子查询：

SELECT product_id,

       product_name,

       sale_price,

       (SELECT AVG(sale_price)

          FROM product) AS avg_price

  FROM product;

3.2.5 关联子查询

* 什么是关联子查询

关联子查询就是通过一些标志将内外两层的查询连接起来起到过滤数据的目的

`选取出各商品种类中高于该商品种类的平均销售单价的商品`。SQL语句如下：

SELECT product_type, product_name, sale_price

  FROM product AS p1

 WHERE sale_price > (SELECT AVG(sale_price)

                       FROM product AS p2

                       WHERE p1.product_type = p2.product_type

                       GROUP BY product_type);

我们将外面的原product表标记为p1，将内部的同商品种类平均价格产品组成的product设置为p2，而且通过WHERE语句连接了两个查询。

执行结果

* 关联子查询与子查询的联系

关联子查询执行逻辑如下：

（1）先从主查询的Product表中product _type列取出第一个值，进入子查询中，得到子查询结果，然后返回父查询，判断父查询的where子句条件，则返回整个语句的第1条结果。

（2）重复上述操作，直到所有主查询中的Product表中product _type列记录取完为止。得出整个语句的结果集，就是最后的答案。

*在子查询中像标量子查询，嵌套子查询或者关联子查询可以看作是子查询的一种操作方式即可。

练习题-第一部分

3.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 ViewPractice5_1 AS
SELECT product_name, sale_price, regist_date
      FROM product
WHERE sale_price >= 1000
      AND regist_date = '2009-09-20';

3.2

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

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

解答：插入时将会报错。因为视图插入数据时，原表也会插入数据，而原表数据插入时不满足约束条件NULL，所以会报错。

3.3

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

解答：

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

3.4

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

提示：其中的关键是 `sale_price_avg_type` 列。与习题三不同，这里需要计算出的是各商品种类的平均销售单价。这与使用关联子查询所得到的结果相同。也就是说，该列可以使用关联子查询进行创建。问题就是应该在什么地方使用这个关联子查询。

-- 创建视图的语句
CREATE VIEW AvgPriceByType AS
SELECT product_id,
       product_name,
       product_type,
       sale_price,
       (SELECT AVG(sale_price)
            FROM product p2
        WHERE p1.product_type = p2.product_type
        GROUP BY p1.product_type) AS sale_price_avg_type
 FROM product p1;

-- 确认视图内容
SELECT * FROM AvgPriceByType;

3.3 各种各样的函数

sql 自带了各种各样的函数，函数大致分为如下几类：

* 算术函数（用来进行数值计算的函数）

* 字符串函数（用来进行字符串操作的函数）

* 日期函数（用来进行日期操作的函数）

* 转换函数（用来转换数据类型和值的函数）

* 聚合函数（用来进行数据聚合的函数）

3.3.1 算数函数

`+ - * /`四则运算在之前的章节介绍过，此处不再赘述。

为了演示其他的几个算数函数，在此构造`samplemath`表

-- DDL ：创建表

USE shop;

DROP TABLE IF EXISTS samplemath;

CREATE TABLE samplemath

(m NUMERIC(10,3),

 n INT,

 p INT);

-- DML ：插入数据

START TRANSACTION; -- 开始事务

INSERT INTO samplemath(m, n, p) VALUES (500, 0, NULL);

INSERT INTO samplemath(m, n, p) VALUES (-180, 0, NULL);

INSERT INTO samplemath(m, n, p) VALUES (NULL, NULL, NULL);

INSERT INTO samplemath(m, n, p) VALUES (NULL, 7, 3);

INSERT INTO samplemath(m, n, p) VALUES (NULL, 5, 2);

INSERT INTO samplemath(m, n, p) VALUES (NULL, 4, NULL);

INSERT INTO samplemath(m, n, p) VALUES (8, NULL, 3);

INSERT INTO samplemath(m, n, p) VALUES (2.27, 1, NULL);

INSERT INTO samplemath(m, n, p) VALUES (5.555,2, NULL);

INSERT INTO samplemath(m, n, p) VALUES (NULL, 1, NULL);

INSERT INTO samplemath(m, n, p) VALUES (8.76, NULL, NULL);

COMMIT; -- 提交事务

-- 查询表内容

SELECT * FROM samplemath;

+----------+------+------+

| m        | n    | p    |

+----------+------+------+

|  500.000 |    0 | NULL |

| -180.000 |    0 | NULL |

|     NULL | NULL | NULL |

|     NULL |    7 |    3 |

|     NULL |    5 |    2 |

|     NULL |    4 | NULL |

|    8.000 | NULL |    3 |

|    2.270 |    1 | NULL |

|    5.555 |    2 | NULL |

|     NULL |    1 | NULL |

|    8.760 | NULL | NULL |

+----------+------+------+

11 rows in set (0.00 sec)

* ABS -- 绝对值

语法：ABS( 数值 )

ABS 函数用于计算一个数字的绝对值，表示一个数到原点的距离。

当 ABS 函数的参数为`NULL`时，返回值也是`NULL`。

* MOD -- 求余数

语法：MOD( 被除数，除数 )

小数没有余数的概念，只能对整数列求余数。

注意：主流的 DBMS 都支持 MOD 函数，只有SQL Server 不支持该函数，其使用`%`符号来计算余数。

* ROUND -- 四舍五入

语法：ROUND( 对象数值，保留小数的位数 )

ROUND 函数用来进行四舍五入操作。

注意：当参数保留小数的位数为变量时，可能会遇到错误，请谨慎使用变量。

SELECT m,

    ABS(m)ASabs_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 |

+----------+---------+------+------+---------+-----------+

11 rows in set (0.08 sec)

3.3.2 字符串函数

为了学习字符串函数，在此我们构造`samplestr`表。

-- DDL ：创建表

USE  shop;

DROP TABLE IF EXISTS samplestr;

CREATE TABLE samplestr

(str1 VARCHAR (40),

str2 VARCHAR (40),

str3 VARCHAR (40)

);

-- DML：插入数据

START TRANSACTION;

INSERT INTO samplestr (str1, str2, str3) VALUES ('opx',        'rt', NULL);

INSERT INTO samplestr (str1, str2, str3) VALUES ('abc', 'def', NULL);

INSERT INTO samplestr (str1, str2, str3) VALUES ('太阳',   '月亮', '火星');

INSERT INTO samplestr (str1, str2, str3) VALUES ('aaa',        NULL, NULL);

INSERT INTO samplestr (str1, str2, str3) VALUES (NULL, 'xyz', NULL);

INSERT INTO samplestr (str1, str2, str3) VALUES ('@!#$%', NULL, NULL);

INSERT INTO samplestr (str1, str2, str3) VALUES ('ABC', NULL, NULL);

INSERT INTO samplestr (str1, str2, str3) VALUES ('aBC', NULL, NULL);

INSERT INTO samplestr (str1, str2, str3) VALUES ('abc哈哈',  'abc', 'ABC');

INSERT INTO samplestr (str1, str2, str3) VALUES ('abcdefabc', 'abc', 'ABC');

INSERT INTO samplestr (str1, str2, str3) VALUES ('micmic', 'i', 'I');

COMMIT;

-- 确认表中的内容

SELECT * FROM samplestr;

+-----------+------+------+

| str1      | str2 | str3 |

+-----------+------+------+

| opx       | rt   | NULL |

| abc       | def  | NULL |

| 太阳      | 月亮 | 火星 |

| aaa       | NULL | NULL |

| NULL      | xyz  | NULL |

| @!#$%     | NULL | NULL |

| ABC       | NULL | NULL |

| aBC       | NULL | NULL |

| abc哈哈   | abc  | ABC  |

| abcdefabc | abc  | ABC  |

| micmic    | i    | I    |

+-----------+------+------+

11 rows in set (0.00 sec)

* CONCAT -- 拼接

语法：`CONCAT(str1, str2, str3)`

MySQL中使用 CONCAT 函数进行拼接。

* LENGTH -- 字符串长度

语法：`LENGTH( 字符串 )`

* LOWER -- 小写转换

LOWER 函数只能针对英文字母使用，它会将参数中的字符串全都转换为小写。该函数不适用于英文字母以外的场合，不影响原本就是小写的字符。

类似的， UPPER 函数用于大写转换。

* REPLACE -- 字符串的替换

语法：`REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )`

* SUBSTRING -- 字符串的截取

语法：`SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）`

使用 SUBSTRING 函数可以截取出字符串中的一部分字符串。截取的起始位置从字符串最左侧开始计算，索引值起始为1。

*（扩展内容）SUBSTRING_INDEX -- 字符串按索引截取

语法：`SUBSTRING_INDEX (原始字符串，分隔符，n)`

该函数用来获取原始字符串按照分隔符分割后，第 n 个分隔符之前（或之后）的子字符串，支持正向（正着数第n个分隔符前面的）和反向（倒着数第n个分隔符后面的）索引，索引起始值分别为 1 和 -1。

SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);

+------------------------------------------+

| SUBSTRING_INDEX('www.mysql.com', '.', 2) |

+------------------------------------------+

| www.mysql                                |

+------------------------------------------+

1 row in set (0.00 sec)

SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);

+-------------------------------------------+

| SUBSTRING_INDEX('www.mysql.com', '.', -2) |

+-------------------------------------------+

| mysql.com                                 |

+-------------------------------------------+

1 row in set (0.00 sec)

获取第1个元素比较容易，获取第2个元素/第n个元素可以采用二次拆分的写法。

SELECT SUBSTRING_INDEX('www.mysql.com', '.', 1);

+------------------------------------------+

| SUBSTRING_INDEX('www.mysql.com', '.', 1) |

+------------------------------------------+

| www                                      |

+------------------------------------------+

1 row in set (0.00 sec)

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('www.mysql.com', '.', 2), '.', -1);

+--------------------------------------------------------------------+

| SUBSTRING_INDEX(SUBSTRING_INDEX('www.mysql.com', '.', 2), '.', -1) |

+--------------------------------------------------------------------+

| mysql                                                              |

+--------------------------------------------------------------------+

1 row in set (0.00 sec)

*（扩展内容）REPEAT -- 字符串按需重复多次

语法：`REPEAT(string, number)`

该函数用来对特定字符实现按需重复。

mysql> SELECT REPEAT('加油！',3);

+-----------------------------+

| REPEAT('加油！',3)          |

+-----------------------------+

| 加油！加油！加油！          |

+-----------------------------+

1 row in set (0.00 sec)

3.3.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 日期)`

使用 EXTRACT 函数可以截取出日期数据中的一部分，例如“年”“月”，或者“小时”“秒”等。该函数的返回值并不是日期类型而是数值类型

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)

3.3.4 转换函数

“转换”在 SQL 中主要有两层意思：一是数据类型的转换，简称为类型转换；另一层意思是值的转换。

* 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)

注意：当要转换为整型时，需要指定为 SIGNED（有符号）或者 UNSIGNED（无符号）

* COALESCE -- 将NULL转换为其他值

语法：`COALESCE(数据1，数据2，数据3……)`

COALESCE 是 SQL 特有的函数，在 SQL 语句中将 NULL 转换为其他值时就会用到转换函数。该函数会返回可变参数 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)

3.4 谓词

3.4.1 什么是谓词

谓词就是返回值为真值的函数。包括`TRUE / FALSE / UNKNOWN`。

谓词主要有以下几个：

* LIKE

* BETWEEN

* IS NULL、IS NOT NULL

* IN

* EXISTS

3.4.2 LIKE谓词 -- 用于字符串的部分一致查询

当需要进行字符串的部分一致查询时需要使用该谓词。

部分一致大体可以分为前方一致、中间一致和后方一致三种类型。

首先我们来创建一张表

-- DDL ：创建表

CREATE TABLE samplelike

( strcol VARCHAR(6) NOT NULL,

  PRIMARY KEY (strcol)

);

-- DML ：插入数据

START TRANSACTION; -- 开始事务

INSERT INTO samplelike (strcol) VALUES ('abcddd');

INSERT INTO samplelike (strcol) VALUES ('dddabc');

INSERT INTO samplelike (strcol) VALUES ('abdddc');

INSERT INTO samplelike (strcol) VALUES ('abcdd');

INSERT INTO samplelike (strcol) VALUES ('ddabc');

INSERT INTO samplelike (strcol) VALUES ('abddc');

COMMIT; -- 提交事务

SELECT * FROM samplelike;

+--------+

| strcol |

+--------+

| abcdd  |

| abcddd |

| abddc  |

| abdddc |

| ddabc  |

| dddabc |

+--------+

6 rows in set (0.00 sec)

* 前方一致：选取出“dddabc”

前方一致即作为查询条件的字符串（这里是“ddd”）与查询对象字符串起始部分相同。

SELECT *

FROM samplelike

WHERE strcol LIKE 'ddd%';

+--------+

| strcol |

+--------+

| dddabc |

+--------+

1 row in set (0.00 sec)

其中，`%`是代表“零个或多个任意字符串”的特殊符号，本例中代表“以ddd开头的所有字符串”。

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

中间一致即查询对象字符串中含有作为查询条件的字符串，无论该字符串出现在对象字符串的最后还是中间都没有关系。

SELECT *

FROM samplelike

WHERE strcol LIKE '%ddd%';

+--------+

| strcol |

+--------+

| abcddd |

| abdddc |

| dddabc |

+--------+

3 rows in set (0.00 sec)

* 后方一致：选取出“abcddd“

后方一致即作为查询条件的字符串（这里是“ddd”）与查询对象字符串的末尾部分相同。

SELECT *

FROM samplelike

WHERE strcol LIKE '%ddd';

+--------+

| strcol |

+--------+

| abcddd |

+--------+

1 row in set (0.00 sec)

综合如上三种类型的查询可以看出，查询条件最宽松，也就是能够取得最多记录的是`中间一致`。这是因为它同时包含前方一致和后方一致的查询结果。

* `_`下划线匹配任意 1 个字符

使用 _（下划线）来代替 %，与 % 不同的是，它代表了“任意 1 个字符”。

SELECT *

FROM samplelike

WHERE strcol LIKE 'abc__';

+--------+

| strcol |

+--------+

| abcdd  |

+--------+

1 row in set (0.00 sec)

3.4.3 BETWEEN谓词 -- 用于范围查询

使用 BETWEEN 可以进行范围查询。该谓词与其他谓词或者函数的不同之处在于它使用了 3 个参数。

-- 选取销售单价为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)

BETWEEN 的特点是闭区间。如果不想让结果中包含临界值，那就必须使用 < 和 >。

SELECT product_name, sale_price

FROM product

WHERE sale_price > 100

AND sale_price < 1000;

+--------------+------------+

| product_name | sale_price |

+--------------+------------+

| 打孔器       |        500 |

| 叉子         |        500 |

| 擦菜板       |        880 |

+--------------+------------+

3 rows in set (0.00 sec)

3.4.4 IS NULL、 IS NOT NULL -- 用于判断是否为NULL

为了选取出某些值为 NULL 的列的数据，不能使用 =，而只能使用特定的谓词IS 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)

与此相反，想要选取 NULL 以外的数据时，需要使用IS NOT NULL。

SELECT product_name, purchase_price

FROM product

WHERE purchase_price IS NOT NULL;

+--------------+----------------+

| product_name | purchase_price |

+--------------+----------------+

| T恤          |            500 |

| 打孔器       |            320 |

| 运动T恤      |           2800 |

| 菜刀         |           2800 |

| 高压锅       |           5000 |

| 擦菜板       |            790 |

+--------------+----------------+

6 rows in set (0.00 sec)

3.4.5 IN谓词 -- OR的简便用法

多个查询条件取并集时可以选择使用`or`语句。

-- 通过OR指定多个进货单价进行查询

SELECT product_name, purchase_price

FROM product

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)

虽然上述方法没有问题，但随着希望选取的对象越来越多， SQL 语句也会越来越长，阅读起来也会越来越困难。这时，我们就可以使用IN 谓词“IN(值1, 值2, 值3, ......)”来替换上述 SQL 语句。

SELECT product_name, purchase_price

FROM product

WHERE purchase_price IN (320, 500, 5000);

+--------------+----------------+

| product_name | purchase_price |

+--------------+----------------+

| T恤          |            500 |

| 打孔器       |            320 |

| 高压锅       |           5000 |

+--------------+----------------+

3 rows in set (0.00 sec)

反之，希望选取出“进货单价不是 320 元、 500 元、 5000 元”的商品时，可以使用否定形式NOT IN来实现。

SELECT product_name, purchase_price

FROM product

WHERE purchase_price NOT IN (320, 500, 5000);

+--------------+----------------+

| product_name | purchase_price |

+--------------+----------------+

| 运动T恤      |           2800 |

| 菜刀         |           2800 |

| 擦菜板       |            790 |

+--------------+----------------+

3 rows in set (0.00 sec)

注意：在使用IN 和 NOT IN 时是无法选取出NULL数据的。NULL 只能使用 IS NULL 和 IS NOT NULL 来进行判断。

3.4.6 使用子查询作为IN谓词的参数

* IN和子查询

IN 谓词（NOT IN 谓词）具有其他谓词所没有的用法，那就是可以使用子查询作为其参数。而子查询又是 SQL内部生成的表，因此也可以说“能够将表作为 IN 的参数”。同理，也能够将视图作为 IN 的参数。

在此，我们创建一张新表`shopproduct`显示出哪些商店销售哪些商品。

-- DDL ：创建表

DROP TABLE IF EXISTS shopproduct;

CREATE TABLE shopproduct

( shop_id CHAR(4)     NOT NULL,

  shop_name VARCHAR(200) NOT NULL,

product_id CHAR(4)      NOT NULL,

  quantity INTEGER      NOT NULL,

PRIMARY KEY (shop_id, product_id) -- 指定主键

);

-- DML ：插入数据

START TRANSACTION; -- 开始事务

INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0001', 30);

INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0002', 50);

INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0003', 15);

INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0002', 30);

INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0003', 120);

INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0004', 20);

INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0006', 10);

INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0007', 40);

INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0003', 20);

INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0004', 50);

INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0006', 90);

INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0007', 70);

INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000D', '福冈', '0001', 100);

COMMIT; -- 提交事务

SELECT * FROM shopproduct;

+---------+-----------+------------+----------+

| shop_id | shop_name | product_id | quantity |

+---------+-----------+------------+----------+

| 000A    | 东京      | 0001       |       30 |

| 000A    | 东京      | 0002       |       50 |

| 000A    | 东京      | 0003       |       15 |

| 000B    | 名古屋      | 0002       |       30 |

| 000B    | 名古屋      | 0003       |      120 |

| 000B    | 名古屋      | 0004       |       20 |

| 000B    | 名古屋      | 0006       |       10 |

| 000B    | 名古屋      | 0007       |       40 |

| 000C    | 大阪      | 0003       |       20 |

| 000C    | 大阪      | 0004       |       50 |

| 000C    | 大阪      | 0006       |       90 |

| 000C    | 大阪      | 0007       |       70 |

| 000D    | 福冈      | 0001       |      100 |

+---------+-----------+------------+----------+

13 rows in set (0.00 sec)

由于单独使用商店编号（shop_id）或者商品编号（product_id）不能区分表中每一行数据，因此指定了 2 列作为主键，对商店和商品进行组合，用来唯一确定每一行数据。

假设我们需要取出大阪在售商品的销售单价：

-- 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_id

                       FROM shopproduct

                       WHERE shop_id = '000C');

+--------------+------------+

| product_name | sale_price |

+--------------+------------+

| 运动T恤      |       4000 |

| 菜刀         |       3000 |

| 叉子         |        500 |

| 擦菜板       |        880 |

+--------------+------------+

4 rows in set (0.00 sec)

子查询是从最内层开始执行的（由内而外），因此，上述语句的子查询执行之后，sql 展开成下面的语句

-- 子查询展开后的结果

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 谓词也能实现，那为什么还要使用子查询呢？这里给出两点原因：

①：实际生活中，某个门店的在售商品是不断变化的，使用 in 谓词就需要经常更新 sql 语句，降低了效率，提高了维护成本；

②：实际上，某个门店的在售商品可能有成百上千个，手工维护在售商品编号真是个大工程。

使用子查询即可保持 sql 语句不变，极大提高了程序的可维护性，这是系统开发中需要重点考虑的内容。

* NOT IN和子查询

NOT IN 同样支持子查询作为参数，用法和 in 完全一样。

-- NOT IN 使用子查询作为参数，取出未在东京门店销售的商品的销售单价

SELECT product_name, sale_price

  FROM product

 WHERE product_id NOT IN (SELECT product_id

                            FROM shopproduct

                           WHERE shop_id = '000A');

+--------------+------------+

| product_name | sale_price |

+--------------+------------+

| 菜刀         |       3000 |

| 高压锅       |       6800 |

| 叉子         |        500 |

| 擦菜板       |        880 |

| 圆珠笔       |        100 |

+--------------+------------+

5 rows in set (0.00 sec)

3.4.7 EXIST 谓词

* EXIST谓词的使用方法

谓词的作用就是 “判断是否存在满足某种条件的记录”，如果存在这样的记录就返回真（TRUE），如果不存在就返回假（FALSE）。EXIST（存在）谓词的主语是“记录”。

我们继续以 IN和子查询中的示例，使用 EXIST 选取出大阪门店在售商品的销售单价。

SELECT product_name, sale_price

  FROM product AS p

 WHERE EXISTS (SELECT *

                 FROM shopproduct AS sp

                WHERE sp.shop_id = '000C'

                  AND sp.product_id = p.product_id);

+--------------+------------+

| product_name | sale_price |

+--------------+------------+

| 运动T恤      |       4000 |

| 菜刀         |       3000 |

| 叉子         |        500 |

| 擦菜板       |        880 |

+--------------+------------+

4 rows in set (0.00 sec)

* EXIST的参数

之前我们学过的谓词，基本上都是像“列 LIKE 字符串”或者“ 列 BETWEEN 值 1 AND 值 2”这样需要指定 2 个以上的参数，而 EXIST 的左侧并没有任何参数。因为 EXIST 是只有 1 个参数的谓词。所以，EXIST 只需要在右侧书写 1 个参数，该参数通常都会是一个子查询。

(SELECT *

   FROM shopproduct AS sp

  WHERE sp.shop_id = '000C'

    AND sp.product_id = p.product_id)

上面这样的子查询就是唯一的参数。确切地说，由于通过条件“SP.product_id = P.product_id”将 product 表和 shopproduct表进行了联接，因此作为参数的是关联子查询。 EXIST 通常会使用关联子查询作为参数。

* 子查询中的SELECT

由于 EXIST 只关心记录是否存在，因此返回哪些列都没有关系，只有存在这样的记录时才返回真（TRUE）。因此，使用下面的查询语句，查询结果也不会发生变化。

SELECT product_name, sale_price

  FROM product AS p

 WHERE EXISTS (SELECT 1 -- 这里可以书写适当的常数

                 FROM shopproduct AS sp

                WHERE sp.shop_id = '000C'

                  AND sp.product_id = p.product_id);

+--------------+------------+

| product_name | sale_price |

+--------------+------------+

| 运动T恤      |       4000 |

| 菜刀         |       3000 |

| 叉子         |        500 |

| 擦菜板       |        880 |

+--------------+------------+

4 rows in set (0.00 sec)

（大家可以把在 EXIST 的子查询中书写 SELECT * 当作 SQL 的一种习惯）。

* 使用NOT EXIST替换NOT IN

就像 EXIST 可以用来替换 IN 一样， NOT IN 也可以用NOT EXIST来替换。

下面的代码示例取出，不在东京门店销售的商品的销售单价。

SELECT product_name, sale_price

  FROM product AS p

 WHERE NOT EXISTS (SELECT *

                     FROM shopproduct AS sp

                    WHERE sp.shop_id = '000C'

                      AND sp.product_id = p.product_id);

+--------------+------------+

| product_name | sale_price |

+--------------+------------+

| 菜刀         |       3000 |

| 高压锅       |       6800 |

| 叉子         |        500 |

| 擦菜板       |        880 |

| 圆珠笔       |        100 |

+--------------+------------+

5 rows in set (0.00 sec)

NOT EXIST 与 EXIST 相反，当“不存在”满足子查询中指定条件的记录时返回真（TRUE）。

3.5 CASE 表达式

3.5.1 什么是 CASE 表达式？

CASE 表达式是函数的一种，CASE 表达式是在区分情况时使用的，这种情况的区分在编程中通常称为（条件）分支。

CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种。由于搜索CASE表达式包含简单CASE表达式的全部功能，因此下面介绍搜索CASE表达式。

语法：

CASE WHEN <求值表达式> THEN <表达式>

     WHEN <求值表达式> THEN <表达式>

     WHEN <求值表达式> THEN <表达式>

     .
     .
     .

ELSE <表达式>

END

上述语句执行时，依次判断 when 表达式是否为真值，是则执行 THEN 后的语句，如果所有的 when 表达式均为假，则执行 ELSE 后的语句。

无论多么庞大的 CASE 表达式，最后也只会返回一个值。

3.5.2 CASE表达式的使用方法

假设现在要实现如下结果：

A ：衣服

B ：办公用品

C ：厨房用具

因为表中的记录并不包含“A ： ”或者“B ： ”这样的字符串，所以需要在 SQL 中进行添加。并将“A ： ”“B ： ”“C ： ”与记录结合起来。

应用场景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 NULL

  END 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)

ELSE 子句也可以省略不写，这时会被默认为 ELSE NULL。但为了防止有人漏读，还是希望大家能够显式地写出 ELSE 子句。

注意： 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)

假如要在列的方向上展示不同种类额聚合值，该如何写呢？

sum_price_clothes | sum_price_kitchen | sum_price_office

------------------+-------------------+-----------------

             5000 |             11180 |              600

聚合函数 + CASE WHEN 表达式即可实现该效果（THEN后面是第一行中每一个列名）

-- 对按照商品种类计算出的销售单价合计值进行行列转换

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)

应用场景3：实现行转列

假设有如下图表的结构

计划得到如下的图表结构

聚合函数 + CASE WHEN 表达式即可实现该转换（THEN后面是第一行中每一个列名）

-- CASE WHEN 实现数字列 score 行转列

SELECT name,

       SUM(CASE WHEN subject = '语文' THEN score ELSE null END) as chinese,

       SUM(CASE WHEN subject = '数学' THEN score ELSE null END) as math,

       SUM(CASE WHEN subject = '外语' THEN score ELSE null END) as english

  FROM score

 GROUP BY name;

+------+---------+------+---------+

| name | chinese | math | english |

+------+---------+------+---------+

| 张三 |      93 |   88 |      91 |

| 李四 |      87 |   90 |      77 |

+------+---------+------+---------+

2 rows in set (0.00 sec)

上述代码实现了数字列 score 的行转列，也可以实现文本列 subject 的行转列

-- CASE WHEN 实现文本列 subject 行转列

SELECT name,

       MAX(CASE WHEN subject = '语文' THEN subject ELSE null END) as chinese,

       MAX(CASE WHEN subject = '数学' THEN subject ELSE null END) as math,

       MIN(CASE WHEN subject = '外语' THEN subject ELSE null END) as english

  FROM score

 GROUP BY name;

+------+---------+------+---------+

| name | chinese | math | english |

+------+---------+------+---------+

| 张三 | 语文    | 数学 | 外语    |

| 李四 | 语文    | 数学 | 外语    |

+------+---------+------+---------+

2 rows in set (0.00 sec

总结：

* 当待转换列为数字时，可以使用`SUM AVG MAX MIN`等聚合函数；

* 当待转换列为文本时，可以使用`MAX MIN`等聚合函数

练习题-第二部分

3.5 判断题

四则运算中含有 NULL 时（不进行特殊处理的情况下），运算结果是否必然会变为NULL ？

解答：是的

3.6

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

①

SELECT product_name, purchase_price

  FROM product

 WHERE purchase_price NOT IN (500, 2800, 5000);

解答：

mysql> SELECT product_name, purchase_price
    ->     FROM product
    -> WHERE purchase_price NOT IN (500, 2800, 5000);
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| 打孔器       |            320 |
| 擦菜板       |            790 |
+--------------+----------------+
2 rows in set (0.01 sec)

该查询语句仅仅取出了 purchase_price 不是 500、2800、5000 的商品，而不包含 purchase_price 为 NULL 的商品，这是因为 谓词无法与 NULL 进行比较。

②

SELECT product_name, purchase_price

  FROM product

 WHERE purchase_price NOT IN (500, 2800, 5000, NULL);

解答：

mysql> SELECT product_name, purchase_price
    ->   FROM product
    ->  WHERE purchase_price NOT IN (500, 2800, 5000, NULL);
Empty set (0.00 sec)

因为 NOT IN 的参数中不能包含 NULL，否则，查询结果通常为空。

3.7

按照销售单价( `sale_price` )对练习 3.6 中的 `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_price
FROM product;

商品数量：THEN后面跟1