阿里云天池龙珠计划SQL入门与实践 | Task03 复杂查询方法-视图、子查询、函数等

最新推荐文章于 2024-08-20 20:45:00 发布

Beneldor

最新推荐文章于 2024-08-20 20:45:00 发布

阅读量230

点赞数 1

分类专栏： SQL入门与实践文章标签： sql mysql 数据库

本文链接：https://blog.csdn.net/qq_41671503/article/details/119423809

版权

SQL入门与实践专栏收录该内容

3 篇文章 0 订阅

订阅专栏

本文为阿里云天池学习《SQL入门与实践》第三讲学习笔记，同时该讲内容基于《SQL基础教程》第五章（复杂查询）及第六章（函数、谓词、case表达式）。

一、视图

学习重点：

从SQL的角度来看，视图和表是相同的，两者的区别在于表中保存的是实际的数据，而视图中保存的是SELECT语句（视图本身并不存储数据）。
使用视图，可以轻松完成跨多表查询数据等复杂操作。
可以将常用的SELECT语句做成视图来使用。
创建视图需要使用CREATE VIEW语句。
视图包含“不能使用ORDER BY”和“可对其进行有限制的更新”两项限制。
删除视图需要使用DROP VIEW语句。

1.1 什么是视图

视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的（会在下面具体介绍），所以操作视图时会根据创建视图的SELECT语句生成一张虚拟表，然后在这张虚拟表上做SQL操作。

1.2 视图与表有什么区别

《sql基础教程第2版》用一句话非常凝练的概括了视图与表的区别—“是否保存了实际的数据”。所以视图并不是数据库真实存储的数据表，它可以看作是一个窗口，通过这个窗口我们可以看到数据库表中真实存在的数据。所以我们要区别视图和数据表的本质，即视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上。

下面这句顺口溜也方便大家记忆视图与表的关系：“视图不是表，视图是虚表，视图依赖于表”。

1.3 为什么会存在视图

主要有以下几点原因：

通过定义视图可以将频繁使用的SELECT语句保存以提高效率。
通过定义视图可以使用户看到的数据更加清晰。
通过定义视图可以不对外公开数据表全部字段，增强数据的保密性。
通过定义视图可以降低数据的冗余。

1.4 如何创建视图

创建视图的基本语法如下：

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

其中SELECT 语句需要书写在 AS 关键字之后。 SELECT 语句中列的排列顺序和视图中列的排列顺序相同， SELECT 语句中的第 1 列就是视图中的第 1 列， SELECT 语句中的第 2 列就是视图中的第 2 列，以此类推。而且视图的列名是在视图名称之后的列表中定义的。需要注意的是视图名在数据库中需要是唯一的，不能与其他视图和表重名。

视图不仅可以基于真实表，我们也可以在视图的基础上继续创建视图。

注：虽然在视图上继续创建视图的语法没有错误，但是我们还是应该尽量避免这种操作。这是因为对多数 DBMS 来说，多重视图会降低 SQL 的性能。

注意事项：

需要注意的是在一般的DBMS中定义视图时不能使用ORDER BY语句。下面这样定义视图是错误的。
```
-- 错误示范
CREATE VIEW productsum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type
 ORDER BY product_type;
```
为什么不能使用 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;
-- 使用
SELECT product_type, cnt_product
  FROM ProductSum;

创建的视图如下图所示：

基于多表的视图
为了学习多表视图，我们再创建一张表，相关代码如下：

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 *
 FROM view_shop_product;

创建的视图如下图所示：

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

SELECT sale_price, shop_name
  FROM view_shop_product
 WHERE product_type = '衣服';

查询结果为：

1.5 如何修改视图结构

修改视图结构的基本语法如下：

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

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

比如修改上述的productSum视图，代码如下：

 ALTER VIEW productSum
    AS
        SELECT product_type, sale_price
          FROM Product
         WHERE regist_date > '2009-09-11';
--         
 SELECT *
 FROM productSum;

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

1.6 如何更新视图内容

因为视图是一个虚拟表，所以对视图的操作就是对底层基础表的操作，所以在修改时只有满足底层基本表的定义才能成功修改。

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

聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
DISTINCT 关键字。
GROUP BY 子句。
HAVING 子句。
UNION 或 UNION ALL 运算符。
FROM 子句中包含多个表。

视图归根结底还是从表派生出来的，因此，如果原表可以更新，那么视图中的数据也可以更新。反之亦然，如果视图发生了改变，而原表没有进行相应更新的话，就无法保证数据的一致性了。

举例——更新视图：

-- 查看原表
SELECT *
FROM Product;
-- 创建视图
CREATE VIEW ProductJim (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
AS
SELECT *
FROM Product
WHERE product_type = '办公用品';
-- 向视图中添加一行
INSERT INTO ProductJim VALUES ('0009', '印章', '办公用品', 95, 10, '2009-11-30');
-- 查看视图
SELECT *
FROM ProductJim;
--查看原表
SELECT *
FROM Product;

在这里插入图片描述

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

1.7 如何删除视图

删除视图的基本语法如下：

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

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

DROP VIEW productSum;

这样，我们就删除了刚才创建的productSum视图。如果我们继续操作这个视图的话就会提示当前操作的内容不存在。

二、子查询

学习重点：

一言以蔽之，子查询就是一次性视图（ SELECT语句）。与视图不同，子查询在SELECT语句执行完毕之后就会消失。
由于子查询需要命名，因此需要根据处理内容来指定恰当的名称。
标量子查询就是只能返回一行一列的子查询。

2.1 什么是子查询

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

2.2 子查询和视图的关系

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

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;

查询结果：

2.4 标量子查询

标量是单一的意思，标量子查询有一个特殊的限制，那就是必须而且只能返回 1 行 1列的结果，也就是返回表中某一行的某一列的值，例如“10”或者“东京都”这样的值。

2.5 标量子查询有什么用

由于返回的是单一的值，标量子查询的返回值可以用在 = 或者 <> 这样需要单一值的比较运算符之中。这也正是标量子查询的优势所在。

比如我们想查询出销售单价高于平均销售单价的商品，如果像下面这样操作就会出错：

-- 在WHERE子句中不能使用聚合函数
SELECT product_id, product＿name, sale_price
FROM Product
WHERE sale_price > AVG(sale_price);

此时标量子查询就可以发挥它的功效了：

SELECT AVG(sale_price)
FROM Product;

查询结果：

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

查询结果：

注：

标量子查询的书写位置并不仅仅局限于 WHERE 子句中，通常任何可以使用单一值的位置都可以使用。也就是说，能够使用常数或者列名的地方，无论是 SELECT 子句、 GROUP BY 子句、 HAVING 子句，还是ORDER BY 子句，几乎所有的地方都可以使用。
如果子查询返回了多行结果，那么它就不再是标量子查询，而仅仅是一个普通的子查询了，因此不能被用在 = 或者 <> 等需要单一输入值的运算符当中，也不能用在 SELECT 等子句当中。

2.6 关联子查询

学习重点：

关联子查询会在细分的组内进行比较时使用。
关联子查询和GROUP BY子句一样，也可以对表中的数据进行切分。
关联子查询的结合条件如果未出现在子查询之中就会发生错误。

比如我们想选取出各商品种类中高于该商品种类的平均销售单价的商品，如果依葫芦画瓢用上一节的标量子查询的程序就会出错，因为如果返回多行值就不是标量子查询了：

-- 发生错误的子查询
SELECT product_id, product_name, sale_price
FROM Product
WHERE sale_price > (SELECT AVG(sale_price)
FROM Product
GROUP BY product_type);

此时关联子查询便发挥了作用：

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

查询结果：

这里起到关键作用的就是在子查询中添加的 WHERE 子句的条件。该条件的意思就是，在同一商品种类中对各商品的销售单价和平均单价进行比较。

这次由于作为比较对象的都是同一张 Product 表，因此为了进行区
别，分别使用了 P1 和 P2 两个别名。在使用关联子查询时，需要在表所对应的列名之前加上表的别名，以“< 表名 >.< 列名 >”的形式记述。

在对表中某一部分记录的集合进行比较时，就可以使用关联子查询。
因此，使用关联子查询时，通常会使用“限定（绑定）”或者“限制”这
样的语言，例如本次示例就是限定“商品种类”对平均单价进行比较。

但是如果刚接触的话一定会比较疑惑关联查询的执行过程，这里有一个博客讲的比较清楚。在这里我们简要的概括为：

首先执行不带WHERE的主查询
根据主查询讯结果匹配product_type，获取子查询结果
将子查询结果再与主查询结合执行完整的SQL语句

注意:

结合条件一定要写在子查询中。

练习题-第一部分

练习题1

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

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

SELECT * FROM ViewPractice5_1;

执行结果：

-- 答案：考察视图的创建。
CREATE VIEW ViewPractice5_1 AS
SELECT product_name, sale_price, regist_date
  FROM Product
 WHERE sale_price >= 1000 
   AND regist_date = '2009-09-20';
 
SELECT * FROM ViewPractice5_1;

练习题2

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

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

答：
会发生错误。 对视图的更新归根结底是对视图所对应的表进行更新，由于INSERT 语句中只对 product_name（商品名称）、 sale_price（销售单价）、 regist_date（登记日期） 3 列进行了赋值，所以剩余的列都会被自动插入 NULL，因此，该 INSERT 语句实质上和下面的 INSERT 语句相同。

INSERT INTO Product (product_id, product_name, product_type, sale_price, 
purchase_price, regist_date)
VALUES (NULL, '刀子', NULL, 300, NULL, '2009-11-02');

而product_id（商品编号）、product_name（商品名称）、product_type（商品种类） 3 列在表定义时都被赋予了 NOT NULL 约束。因此，向product_id（商品编号）以及 product_type（商品种类）中插入 NULL 的 INSERT 语句是无法执行的，于是就发生了错误。

练习题3

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

-- 答案：考察标量子查询。
SELECT product_id,
	   product_name,
       product_type,
       sale_price,
      (SELECT AVG(sale_price) FROM Product) AS sale_price_all
FROM Product;

练习题4

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

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

-- 答案：考察关联子查询。
CREATE VIEW AvgPriceByType AS
SELECT product_id, product_name, product_type, sale_price,
       (SELECT AVG(sale_price)
          FROM Product AS P2
          WHERE P1.product_type = P2.product_type
          GROUP BY PRODUCT_TYPE) AS avg_sale_price
  FROM Product AS P1;
--
SELECT *
  FROM AvgPriceByType;

三、各种各样的函数

SQL自带了各种各样的函数，极大提高了SQL语言的便利性。

函数大致分为如下几类：

算术函数（用来进行数值计算的函数）
字符串函数（用来进行字符串操作的函数）
日期函数（用来进行日期操作的函数）
转换函数（用来转换数据类型和值的函数）
聚合函数（用来进行数据聚合的函数）

函数总个数超过200个，不需要完全记住，常用函数有 30~50 个，其他不常用的函数使用时查阅文档即可。

3.1 算数函数

四则运算：+ - * /；
绝对值：ABS( 数值 )；
求余数：MOD( 被除数，除数 )
四舍五入：ROUND( 对象数值，保留小数的位数 )

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

3.2 字符串函数

字符串拼接：CONCAT(str1, str2, str3)，或者 str1 + str2 + str3；
求字符串长度：LENGTH( 字符串 )，一个汉字占3个字节；
小写转换：LOWER(字符串)；
字符替换：REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )；
字符串的截取：SUBSTRING (对象字符串 FROM 截取的起始位置 FOR 截取的字符数)；
字符串按索引截取：SUBSTRING_INDEX (原始字符串，分隔符，n)。该函数用来获取原始字符串按照分隔符分割后，第 n 个分隔符之前（或之后）的子字符串，支持正向和反向索引，索引起始值分别为 1 和 -1。

3.3 日期函数

获取当前日期：CURRENT_DATE；
获取当前时间：CURRENT_TIME；
获取当前日期和时间：CURRENT_TIMESTAMP；
截取日期元素：EXTRACT(日期元素 FROM 日期)，如EXTRACT(YEAR FROM CURRENT_TIMESTAMP)，日期元素可以是YEAR，MONTH，DAY，HOUR，MINUTE，SECOND。

3.4 转换函数

类型转换：CAST(转换前的值 AS 想要转换的数据类型）
将NULL转换为其他值：COALESCE(数据1，数据2，数据3……)

COALESCE 是 SQL 特有的函数。该函数会返回可变参数 A 中左侧开始第 1个不是NULL的值。参数个数是可变的，因此可以根据需要无限增加。在 SQL 语句中将 NULL 转换为其他值时就会用到转换函数。

四、谓词

学习重点：

谓词就是返回值为真值的函数（真值包括RUE / FALSE / UNKNOWN）。
掌握LIKE的三种使用方法（前方一致、中间一致、后方一致）。
需要注意BETWEEN包含三个参数。
想要取得NULL数据时必须使用IS NULL。
可以将子查询作为IN和EXISTS的参数。

4.1 什么是谓词

返回值为真值的函数称为谓词，谓词主要有以下几个：

LIKE
BETWEEN
IS NULL、IS NOT NULL
IN
EXISTS

4.2 LIKE谓词 – 用于字符串的部分一致查询

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

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

首先我们来创建一张表:

-- DDL ：创建表
CREATE TABLE samplelike
( strcol VARCHAR(6) NOT NULL,
PRIMARY KEY (strcol)
samplelike);
-- 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
WHERE strcol LIKE 'ddd%';
-- 中间一致
SELECT *
FROM samplelike
WHERE strcol LIKE '%ddd%';
-- 后方一致
SELECT *
FROM samplelike
WHERE strcol LIKE '%ddd';
-- _下划线匹配任意 1 个字符
SELECT *
FROM samplelike
WHERE strcol LIKE 'abc__';

注意：中间一致即查询对象字符串中含有作为查询条件的字符串，无论该字符串出现在对象字。

结果呈现：

原表

前方一致

中间一致

后方一致/下划线匹配

4.3 BETWEEN谓词 – 用于范围查询

BETWEEN包含两个临界值，得到的是闭区间，举例：

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

4.4 IS NULL、IS NOT NULL – 用于判断是否为NULL

为了选取出某些值为 NULL 的列的数据，不能使用 =，而只能使用特定的谓词IS NULL。与此相反，想要选取 NULL 以外的数据时，需要使用IS NOT NULL。

4.5 IN谓词 – OR的简便用法

以下语句效果相同：

-- 通过OR指定多个进货单价进行查询
SELECT product_name, purchase_price
FROM product
WHERE purchase_price = 320
OR purchase_price = 500
OR purchase_price = 5000;
-- 利用谓词IN进行多值查询
SELECT product_name, purchase_price
FROM product
WHERE purchase_price IN (320, 500, 5000);

否定形式可以用 NOT IN表示。

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

-- ShopProduct表未给出，仅做演示
SELECT product_name, sale_price
FROM Product
WHERE product_id NOT IN (SELECT product_id
						   FROM ShopProduct
						  WHERE shop_id = '000A');

从ShopProduct表中选取出shop_id为000A的店铺对应产品的ID，并在Product表中找到对应产品的名称以及售价，实现了跨表查询。

实际上，shop_id为000A的店铺对应的产品ID为(‘0003’, ‘0004’, ‘0006’, ‘0007’)，直接将其作为IN谓词的参数也可以得到相同的查询效果，然而使用子查询作为IN谓词的参数有如下两点原因：

实际生活中，某个门店的在售商品是不断变化的，使用 in 谓词就需要经常更新 sql 语句，降低了效率，提高了维护成本；
实际上，某个门店的在售商品可能有成百上千个，手工维护在售商品编号真是个大工程。使用子查询即可保持 sql 语句不变，极大提高了程序的可维护性，这是系统开发中需要重点考虑的内容。

4.7 EXIST 谓词

EXIST 谓词的用法理解起来有些难度，但一旦能够熟练使用 EXIST 谓词，就能体会到它极大的便利性。

EXIST 的使用方法与之前的都不相同；
语法理解起来比较困难；
实际上即使不使用 EXIST，基本上也都可以使用 IN（或者 NOT IN）来代替。

EXIST谓词的用法：

谓词的作用就是 “判断是否存在满足某种条件的记录”；
如果存在这样的记录就返回真（TRUE），如果不存在就返回假（FALSE）；
EXIST（存在）谓词的主语是“记录”。
EXIST 是只有 1 个参数的谓词，且通常是关联子查询。

-- 使用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);

由于 EXIST 只关心记录是否存在，因此返回哪些列都没有关系。 EXIST 只会判断是否存在满足子查询中 WHERE 子句指定的条件“商店编号（shop_id）为 ‘000C’，商品（product）表和商店商品（shopproduct）表中商品编号（product_id）相同”的记录，只有存在这样的记录时才返回真（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);

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

五、CASE 表达式

5.1 什么是 CASE 表达式？

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

CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种。由于搜索CASE表达式包含简单CASE表达式的全部功能。本课程将重点介绍搜索CASE表达式。

语法：

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

上述语句执行时，依次判断 when 表达式是否为真值，是则执行 THEN 后的语句，如果所有的 when 表达式均为假，则执行 ELSE 后的语句。无论多么庞大的 CASE 表达式，最后也只会返回一个值。

5.2 CASE表达式的使用方法

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

查询结果：

应用场景2：实现列方向上的聚合

SELECT product_type,
       SUM(sale_price) AS sum_price
  FROM product
 GROUP BY product_type;

原表：

-- 对按照商品种类计算出的销售单价合计值进行行列转换
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;

实现方向上的聚合：

应用场景3：实现行列转换

-- 创建表格
CREATE TABLE score 
(name_ CHAR(4) NOT NULL, 
 subject_ CHAR(4) NOT NULL,
 score INTEGER NOT NULL, 
PRIMARY KEY	(score));
INSERT INTO score VALUES ('张三','语文',93),
('张三','数学',88),
('张三','外语',91),
('李四','语文',87),
('李四','数学',90),
('李四','外语',77);
-- 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_;
-- 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_;

原表：

实现数字列 score 行转列：

文本列 subject 行转列：

总结：

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

练习题-第二部分

练习题5

运算或者函数中含有 NULL 时，结果全都会变为NULL ？（判断题）
答：错误，转换函数COALESCE函数除外。

练习题6

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

-- 1)
SELECT product_name, purchase_price
  FROM product
 WHERE purchase_price NOT IN (500, 2800, 5000);

结果1：

-- 2)
SELECT product_name, purchase_price
 FROM product
WHERE purchase_price NOT IN (500, 2800, 5000, NULL);

结果2：

此时无法取出任何记录。 NOT IN的参数中包含NULL时结果通常会为空，也就是无法选取出任何记录。其中的理由十分复杂，属于中级学习的范畴，这里希望大家了解的是 NOT IN 的参数中不能包含 NULL。

另外：不仅仅是指定 NULL 的情况，使用子查询作为 NOT IN 的参数时，该子查询的返回值也不能是 NULL。请大家一定要遵守这一规定。

练习题7

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

低档商品：销售单价在1000日元以下（T恤衫、办公用品、叉子、擦菜板、圆珠笔）
中档商品：销售单价在1001日元以上3000日元以下（菜刀）
高档商品：销售单价在3001日元以上（运动T恤、高压锅）

请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句，结果如下所示。

-- 答案
SELECT COUNT(CASE WHEN sale_price <= 1000 THEN sale_price END) AS low_price,
	   COUNT(CASE WHEN sale_price BETWEEN 1001 AND 3000 THEN sale_price END) AS mid_price,
       COUNT(CASE WHEN sale_price >= 3001 THEN sale_price END) AS high_price
  FROM product;

Beneldor

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
阿里云天池龙珠计划SQL入门与实践 | Task03 复杂查询方法-视图、子查询、函数等

本文为阿里云天池学习《SQL入门与实践》第三讲学习笔记，同时该讲内容基于《SQL基础教程》第五章（复杂查询）及第六章（函数、谓词、case表达式）。复杂查询方法-视图、子查询、函数等一、视图1.1 什么是视图1.2 视图与表有什么区别1.3 为什么会存在视图1.4 如何创建视图1.5 如何修改视图结构1.6 如何更新视图内容1.7 如何删除视图二、子查询2.1 什么是子查询2.2 子查询和视图的关系2.3 嵌套子查询2.4 标量子查询2.5 标量子查询有什么用2.6 关联子查询练习题-第一部分练习题1.
复制链接

扫一扫

专栏目录