SQL学习笔记(三): 视图与一些复杂查询(虽复杂但常用)

80 阅读 0 评论 53 点赞

我是靠谱客的博主完美大碗，最近开发中收集的这篇文章主要介绍SQL学习笔记(三): 视图与一些复杂查询(虽复杂但常用)，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

1. 写在前面

最近需要用到HQL语句做一些数据分析的工作，又发现了自己的短板， HQL这个东西用起来并不是很流畅，虽然建表，查询的基本语句还能应付(这还是吃的大学的SQL基础老本)，但涉及到一些复杂的查询等就不行了，每次都得谷歌，但谷歌完了并没有留下记录，记不住，导致下一次再用到还得再继续谷歌，这中间就浪费了很多搜索的时间。效率非常低。所以就想再突击一遍SQL, 之所以复杂的查询不会，是因为之前的SQL基础忘光了，而基于hive的查询和SQL语句实在是太像了，并且来到公司之后发现，不管是开发还是算法， SQL应该都是必备技能了。所以借着这个机会，重新走一遍SQL，这次的参考资料是Datawhale的组队学习资料SQL基础教程学习笔记。真的是非常感谢组织伙伴们的辛苦整理和分享，接下来，就大约用两天的时间，快速过一遍，而这个系列，就是整理在这个过程中常用到的一些操作和知识点。这样，以后再用到别的高级知识的时候，就有相应的地方整理啦，也方便回看回练。学习新知识的时候，先俯瞰下整个森林，再走近森林，去看树木，这样不容易迷失 ????

今天是第三篇文章，主要是整理有关视图和一些复杂的查询了(子查询，字符串的函数，谓词，CASE表达式等)，而从这一章开始，也正式进入了我的知识盲区，在公司里面常用到的除了上一篇里面的基本查询操作，还有这一章的一些数据处理函数等(处理些特殊情况数据)以及下一章里面的join函数等。所以这一章干货满满的还是，主要如下：

视图
子查询
各式各样的函数
谓词
CASE表达式

2. 视图

视图与表的区别： 是否保存了实际的数据。视图并不是数据库真实存储的数据表，它可以看作是一个窗口，通过这个窗口我们可以看到数据库表中真实存在的数据。所以要区别视图和数据表的本质，即视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上。
在这里插入图片描述
视图不是表，视图是虚表，视图依赖于表。之前学习的时候总不是很理解视图的这个概念，而随着认知的提高，我理解的视图就是select查询出来的一个结果，我们select之后，不是会出来一个结果吗？把这个给一个视图，下一次再查的时候，直接看视图就OK了。效率有所提高。所以视图的四个优点：

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

2.1 创建视图

语法：

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 将被忽略。

建立视图的例子：

# 基于单表的视图
CREATE VIEW productsum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type ;


# 基于多表的视图
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 = '衣服';

2.2 修改视图结构

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

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

看个例子，感觉就是重新选择查询的语句：

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

2.3 更新视图内容

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

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

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

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

看个例子：

UPDATE productsum
   SET sale_price = '5000'
 WHERE product_type = '办公用品';

此时会发现视图相应的地方更新了，原表相应的地方也更新了。但是，在视图中修改原表数据是只能修改在视图里面看到的那部分内容，视图只是原表的某个窗口而已，并不是全部。另外，就是最好不要试图通过改视图数据去改原表数据。视图更多的是查询功能

2.4 删除视图

语法：

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

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

3. 子查询

3.1 子查询定义

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

3.2 子查询和视图的关系

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

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

虽然嵌套子查询可以查询出结果，但是随着子查询嵌套的层数的叠加，SQL语句不仅会难以理解而且执行效率也会很差，所以要尽量避免这样的使用。但是在公司里面，我看好多都是套好几层，这种如果要用，最好是写下注释，否则别人理解起来会很麻烦。

3.4 标量子查询

标量子查询也就是单一的子查询，所谓单一就是要求我们执行的SQL语句只能返回一个值，也就是要返回表中具体的某一行的某一列。这玩意啥用？看下面这个例子：

# 销售单价高于平均售价的这种
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 product_id,
       product_name,
       sale_price,
       (SELECT AVG(sale_price)
          FROM product) AS avg_price
  FROM product;

3.5 关联子查询

所谓关联，就是当前的查询是和子查询有一定的关系的，往往子查询需要当前查询的传值，记得当时学这个地方的噩梦就是双Not exists语句了。这里先简单来个例子：

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_type传到子查询里面去，句子的执行顺序是

先执行不带WHERE的主查询，进行SELECT操作
```
SELECT product _type , product_name, sale_price	
FROM Product AS P1
```
这样取出第一个值来，比如"衣服", 然后通过WHERE P1.product_type = P2.product_type传入子查询，子查询变成：
```
SELECT AVG(sale_price)
FROM Product AS P2
WHERE P2.product_type = ‘衣服’
GROUP BY product_type);
```

从子查询得到衣服类型的结果AVG(sale_price)=2500，返回主查询

SELECT product_type, product_name, sale_price
FROM Product AS P1
WHERE sale_price > 2500 AND product_type = ‘衣服’

这样就能得到"衣服"这个类型的主查询结果了。
然后，product _type取第二个值，得到整个语句的第二结果，依次类推，把product _type全取值一遍，就得到了整个语句的结果集。

所以关联子查询和正常的SELECT语句完全不同。具体看这篇文章

在这里插入图片描述

4. 函数介绍

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

算术函数(数值计算的函数)
字符串函数(字符串操作函数)
日期函数
转换函数(转换数据类型和值)
聚合函数

下面一一来介绍。

4.1 算数函数

ABS — 绝对值函数：
ABS(数值 )： ABS 函数用于计算一个数字的绝对值，表示一个数到原点的距离。
当 ABS 函数的参数为NULL时，返回值也是NULL。
MOD — 求余数
MOD( 被除数，除数 )：MOD 是计算除法余数（求余）的函数，是 modulo 的缩写。小数没有余数的概念，只能对整数列求余数。注意：主流的 DBMS 都支持 MOD 函数，只有SQL Server 不支持该函数，其使用%符号来计算余数。
ROUND — 四舍五入
ROUND( 对象数值，保留小数的位数 )：ROUND 函数用来进行四舍五入操作。注意：当参数保留小数的位数为变量时，可能会遇到错误，请谨慎使用变量

例子：

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

4.2 字符串函数

CONCAT — 拼接
CONCAT(str1, str2, str3)： MySQL中使用 CONCAT 函数进行拼接。
LENGTH – 字符串长度
LOWER – 小写转换
LOWER 函数只能针对英文字母使用，它会将参数中的字符串全都转换为小写。该函数不适用于英文字母以外的场合，不影响原本就是小写的字符。类似的， UPPER 函数用于大写转换。
REPLACE – 字符串的替换，REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )
SUBSTRING – 字符串的截取
SUBSTRING (对象字符串 FROM 截取的起始位置 FOR 截取的字符数)。使用 SUBSTRING 函数可以截取出字符串中的一部分字符串。截取的起始位置从字符串最左侧开始计算，索引值起始为1。

SUBSTRING_INDEX – 字符串按索引截取
SUBSTRING_INDEX (原始字符串，分隔符，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)

正数是从前往后看，找第n个分割符之前的子字符串，而负数从后往前看，找第n个分割符之后的子字符串。

获取第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)

这个函数还是比较重要的，解析json数据的时候，会用到。

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 函数可以截取出日期数据中的一部分，例如“年”，“月”，或者“小时”“秒”等。该函数的返回值并不是日期类型而是数值类型

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 转换函数

“转换”这个词的含义非常广泛，在 SQL 中主要有两层意思：一是数据类型的转换，简称为类型转换，在英语中称为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的值。参数个数是可变的，因此可以根据需要无限增加。在 SQL 语句中将 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

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

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

SELECT *
FROM samplelike
WHERE strcol LIKE 'ddd%';
+--------+
| strcol |
+--------+
| dddabc |
+--------+
1 row in set (0.00 sec)

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

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

SELECT *
FROM samplelike
WHERE strcol LIKE 'abc__';
+--------+
| strcol |
+--------+
| abcdd  |
+--------+
1 row in set (0.00 sec)

5.2 ETWEEN谓词 – 用于范围查询

使用 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 的特点就是结果中会包含 100 和 1000 这两个临界值，也就是闭区间。如果不想让结果中包含临界值，那就必须使用 < 和 >

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)

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

5.4 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 的叉子和圆珠笔。 NULL 只能使用 IS NULL 和 IS NOT NULL 来进行判断。

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

SELECT product_name, sale_price
FROM product
WHERE product_id IN (SELECT product_id
  FROM shopproduct
  WHERE shop_id = '000C');

5.5 EXIST 谓词

谓词的作用就是 “判断是否存在满足某种条件的记录”。

如果存在这样的记录就返回真（TRUE），如果不存在就返回假（FALSE）。

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 通常会使用关联子查询作为参数。也就是主查询会往子查询中传值。由于 EXIST 只关心记录是否存在，因此返回哪些列都没有关系。 EXIST 只会判断是否存在满足子查询中 WHERE 子句指定的条件“商店编号（shop_id）为 ‘000C’，商品（product）表和商店商品（shopproduct）表中商品编号（product_id）相同”的记录，只有存在这样的记录时才返回真（TRUE）。所以后面一般是SELECT *

不在大阪门店销售的商品的销售单价可以用NOT EXIST

SELECT product_name, sale_price
  FROM product AS p
 WHERE NOT EXISTS (SELECT *
                     FROM shopproduct AS sp
                    WHERE sp.shop_id = '000A'
                      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）

6. CASE表达式

这个很重要，也是SQL语句中常用的语句。

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

语法：

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

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

应用场景一：根据不同分支得到不同列值

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”是不能省略的，请大家特别注意不要遗漏。忘记书写 END 会发生语法错误，这也是初学时最容易犯的错误。

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

-- 对按照商品种类计算出的销售单价合计值进行行列转换
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 表达式即可实现该转换:

-- 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等聚合函数

最后

以上就是完美大碗为你收集整理的SQL学习笔记(三): 视图与一些复杂查询(虽复杂但常用)的全部内容，希望文章能够帮你解决SQL学习笔记(三): 视图与一些复杂查询(虽复杂但常用)所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。

本文分类：SQL学习笔记
浏览次数：80 次浏览
发布日期：2023-05-16 15:42:02
本文链接：https://www.kaopuke.com/article/k-p-k_14_ujo_2_f3_12__23__14_y.html

SQL学习笔记(三): 视图与一些复杂查询(虽复杂但常用)

概述

1. 写在前面

2. 视图

2.1 创建视图

2.2 修改视图结构

2.3 更新视图内容

2.4 删除视图

3. 子查询

3.1 子查询定义

3.2 子查询和视图的关系

3.3 嵌套子查询

3.4 标量子查询

3.5 关联子查询

4. 函数介绍

4.1 算数函数

4.2 字符串函数

4.3 日期函数

4.4 转换函数

5. 谓词

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

5.2 ETWEEN谓词 – 用于范围查询

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

5.4 IN谓词 – OR的简便用法

5.5 EXIST 谓词

6. CASE表达式

最后

评论列表共有 0 条评论

发表评论取消回复

SQL学习笔记(三): 视图与一些复杂查询(虽复杂但常用)

概述

1. 写在前面

2. 视图

2.1 创建视图

2.2 修改视图结构

2.3 更新视图内容

2.4 删除视图

3. 子查询

3.1 子查询定义

3.2 子查询和视图的关系

3.3 嵌套子查询

3.4 标量子查询

3.5 关联子查询

4. 函数介绍

4.1 算数函数

4.2 字符串函数

4.3 日期函数

4.4 转换函数

5. 谓词

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

5.2 ETWEEN谓词 – 用于范围查询

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

5.4 IN谓词 – OR的简便用法

5.5 EXIST 谓词

6. CASE表达式

最后

相关文章

评论列表共有 0 条评论

发表评论 取消回复

发表评论取消回复