1、视图
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 列,以此类推。而且视图的列名是在视图名称之后的列表中定义的。
需要注意的是视图名在数据库中需要是唯一的,不能与其他视图和表重名。
视图不仅可以基于真实表,我们也可以在视图的基础上继续创建视图。
1.5 如何删除视图
删除视图的基本语法如下:
DROP VIEW <视图名1> [ , <视图名2> …]
2、子查询
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;
其中最内层的子查询我们将其命名为productSum,这条语句根据product_type分组并查询个数,第二层查询中将个数为4的商品查询出来,最外层查询product_type和cnt_product两列。
虽然嵌套子查询可以查询出结果,但是随着子查询嵌套的层数的叠加,SQL语句不仅会难以理解而且执行效率也会很差,所以要尽量避免这样的使用。
3 CASE 表达式
3.1什么是 CASE 表达式?
CASE 表达式是函数的一种。是 SQL 中数一数二的重要功能,有必要好好学习一下。
CASE 表达式是在区分情况时使用的,这种情况的区分在编程中通常称为(条件)分支。
CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种。由于搜索CASE表达式包含简单CASE表达式的全部功能。
CASE WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
.
.
.
ELSE <表达式>
END
上述语句执行时,依次判断 when 表达式是否为真值,是则执行 THEN 后的语句,如果所有的 when 表达式均为假,则执行 ELSE 后的语句。
无论多么庞大的 CASE 表达式,最后也只会返回一个值。
3.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)
CASE 表达式最后的“END”是不能省略的,特别注意不要遗漏。忘记书写 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 表达式即可实现该效果
-- 对按照商品种类计算出的销售单价合计值进行行列转换
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)
参考资料
https://linklearner.com/datawhale-homepage/#/learn/detail/70