SQL零基础投喂（复杂⼀点的查询)

Task03：复杂⼀点的查询
之前接触了sql基本的查询⽤法，接下来介绍⼀些相对复杂的⽤法。
3.1 视图
我们先来看⼀个查询语句
1 SELECT stu_name FROM view_students_info;
单从表⾯上看起来这个语句是和正常的从数据表中查询数据是完全相同的，但其实我们
操作的是⼀个视图。所以从SQL的⻆度来说操作视图与操作表看起来是完全相同的，那
么为什么还会有视图的存在呢？视图到底是什么？视图与表有什么不同呢？

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

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

3.1.3 为什么会存在视图
那既然已经有数据表了，为什么还需要视图呢？主要有以下⼏点原因：

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

3.1.4 如何创建视图
说了这么多视图与表的区别，下⾯我们就⼀起来看⼀下如何创建视图吧
创建视图的基本语法如下：
1 CREATE VIEW <视图名称>(<列名1>,<列名2>,…) AS <SELECT语句>
其中SELECT 语句需要书写在 AS 关键字之后。 SELECT 语句中列的排列顺序和视图
中列的排列顺序相同， SELECT 语句中的第 1 列就是视图中的第 1 列， SELECT 语
句中的第 2 列就是视图中的第 2 列，以此类推。⽽且视图的列名是在视图名称之后的列
表中定义的。
需要注意的是视图名在数据库中需要是唯⼀的，不能与其他视图和表重名。
视图不仅可以基于真实表，我们也可以在视图的基础上继续创建视图。
虽然在视图上继续创建视图的语法没有错误，但是我们还是应该尽量避免这种操作。这
是因为对多数 DBMS 来说， 多重视图会降低 SQL 的性能。
注意事项：
需要注意的是在⼀般的DBMS中定义视图时不能使⽤ORDER BY语句。下⾯这样定义视
图是错误的。（postgresql是可以的）
1 CREATE VIEW productsum (product_type, cnt_product)
2 AS
3 SELECT product_type, COUNT(*)
4 FROM product
5 GROUP BY product_type
6 ORDER BY product_type desc;

product_type | cnt_product
--------------±------------
厨房用具 | 4
衣服 | 2
办公用品 | 2
(3 行记录)
在 MySQL中视图的定义是允许使⽤ ORDER BY 语句的，但是若从特定视图进⾏选择，
⽽该视图使⽤了⾃⼰的 ORDER BY 语句，则视图定义中的 ORDER BY 将被忽略

–创建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) – 指定主键
);
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);

create view view_shop_product(product_type,sale_price,shop_name)
AS
select product_type,sale_price,shop_name
from product,shopproduct
where product.product_id=shopproduct.product_id;

select * from view_shop_product;
product_type | sale_price | shop_name
--------------±-----------±----------
衣服 | 1000 | 东京
办公用品 | 500 | 东京
衣服 | 4000 | 东京
办公用品 | 500 | 名古屋
衣服 | 4000 | 名古屋
厨房用具 | 3000 | 名古屋
厨房用具 | 500 | 名古屋
厨房用具 | 880 | 名古屋
衣服 | 4000 | 大阪
厨房用具 | 3000 | 大阪
厨房用具 | 500 | 大阪
厨房用具 | 880 | 大阪
衣服 | 1000 | 福冈
(13 行记录)
我们可以在这个视图的基础上进⾏查询
eg:
select sale_price,shop_name
from view_shop_product
where product_type=‘衣服’;

sale_price | shop_name
------------±----------
1000 | 东京
4000 | 东京
4000 | 名古屋
4000 | 大阪
1000 | 福冈
(5 行记录)

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’;
备注：PostgreSQL对于修改视图有诸多限制（上述方法在Postgresql中无法实现），建议删除重新创建
更新视图只能在最后增加字段，不能改字段名、不能删除字段、也不能在中间增加字段

3.1.6 如何更新视图内容
因为视图是⼀个虚拟表，所以对视图的操作就是对底层基础表的操作，所以在修改时只
有满⾜底层基本表的定义才能成功修改。
对于⼀个视图来说，如果包含以下结构的任意⼀种都是不可以被更新的：
聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
DISTINCT 关键字。
GROUP BY ⼦句。
HAVING ⼦句。
UNION 或 UNION ALL 运算符。
FROM ⼦句中包含多个表。
视图归根结底还是从表派⽣出来的，因此，如果原表可以更新，那么 视图中的数据也可
以更新。反之亦然，如果视图发⽣了改变，⽽原表没有进⾏相应更新的话，就⽆法保证
数据的⼀致性了。
更新视图
因为我们刚刚修改的productSum视图不包括以上的限制条件，我们来尝试更新⼀下视图
update productSum
set cnt_product=‘5000’
where product_type=‘办公用品’;

删除视图的基本语法如下：
DROP VIEW <视图名1> [ , <视图名2> …]
注意：需要有相应的权限才能成功删除。
删除视图
我们删除刚才创建的productSum视图
DROP VIEW productSum;
如果我们继续操作这个视图的话就会提示当前操作的内容不存在。

3.2 ⼦查询
我们先来看⼀个语句
select stu_name
from (
select stu_name,count(*) as stu_cnt
from view_students_infogroup by stu_age
) as studentSum;
这个语句看起来很好理解，其中使⽤括号括起来的sql语句⾸先执⾏，执⾏成功后再执⾏
外⾯的sql语句。但是我们上⼀节提到的视图也是根据SELECT语句创建视图然后在这个
基础上再进⾏查询。那么什么是⼦查询呢？⼦查询和视图⼜有什么关系呢？

3.2.1 什么是⼦查询
⼦查询指⼀个查询语句嵌套在另⼀个查询语句内部的查询，这个特性从 MySQL 4.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_type
group by product_type) as productSum
where cnt_product=4
) as productsum2;
其中最内层的⼦查询我们将其命名