3.1.1 什么是视图
视图是一个虚拟的表,不同于直接操作数据表,视图是依据SELECT语句来创建的(会在下面具体介绍),所以操作视图时会根据创建视图的SELECT语句生成一张虚拟表,然后在这张虚拟表上做SQL操作。
“视图不是表,视图是虚表,视图依赖于表”。
3.1.3 为什么会存在视图
那既然已经有数据表了,为什么还需要视图呢?主要有以下几点原因:
- 通过定义视图可以将频繁使用的SELECT语句保存以提高效率。
- 通过定义视图可以使用户看到的数据更加清晰。
- 通过定义视图可以不对外公开数据表全部字段,增强数据的保密性。
- 通过定义视图可以降低数据的冗余。
虽然在视图上继续创建视图的语法没有错误,但是我们还是应该尽量避免这种操作。这是因为对多数 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 将被忽略。
3.2.1 什么是子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询,这个特性从 MySQL 4.1 开始引入,在 SELECT 子句中先计算子查询,子查询结果作为外层另一个查询的过滤条件,查询可以基于一个表或者多个表。
练习题-第一部分
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 ,slae_price ,regist_date
where sale_price>=1000 and regist_date='2009-09-20';
3.2
向习题一中创建的视图 ViewPractice5_1 中插入如下数据,会得到什么样的结果呢?
INSERT INTO ViewPractice5_1 VALUES (' 刀子 ', 300, '2009-11-02');
插入时会报错
视图插入数据时原表也会插入数据,但是视图插入的数据不满足原表的约束条件,所以报错
3.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;
3.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
提示:其中的关键是 avg_sale_price 列。与习题三不同,这里需要计算出的 是各商品种类的平均销售单价。这与使用关联子查询所得到的结果相同。 也就是说,该列可以使用关联子查询进行创建。问题就是应该在什么地方使用这个关联子查询。
create view AvgPriceByType as
#查询商品结果 并命名为p1
# 查询商品的平均价格 并命名为p2
select product_id,product_type,sale_price p1
( select avg (sale_price)) from product as p2
#将商品种类相同的商品按p1分组求平均值
where p1.product_type = p2.product_type
group by p1.product_type as avg_price
from product p1;
3.5.1 什么是 CASE 表达式?
CASE 表达式是函数的一种。是 SQL 中数一数二的重要功能,有必要好好学习一下。
CASE 表达式是在区分情况时使用的,这种情况的区分在编程中通常称为(条件)分支。
CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种。由于搜索CASE表达式包含简单CASE表达式的全部功能。本课程将重点介绍搜索CASE表达式。
语法:
CASE WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
.
.
.
ELSE <表达式>
END
上述语句执行时,依次判断 when 表达式是否为真值,是则执行 THEN 后的语句,如果所有的 when 表达式均为假,则执行 ELSE 后的语句。
无论多么庞大的 CASE 表达式,最后也只会返回一个值。
练习题-第二部分
3.5
运算或者函数中含有 NULL 时,结果全都会变为NULL ?(判断题)
正确
3.6
对本章中使用的 product(商品)表执行如下 2 条 SELECT 语句,能够得到什么样的结果呢?
① 查询product表中价格不包含500,2800,5000的商品名称和商品价格
SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (500, 2800, 5000);
②查询product表中价格不包含500,2800,5000的商品名称和商品价格,但是其中有null则返回为空,因为NOT IN的参数中不能包含NULL
SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (500, 2800, 5000, NULL);
3.7
按照销售单价( 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 <= 100 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;
其中 1 为 true 0 为false