【数据库系列】| 视图和子查询

一、视图

• 从SQL的角度来看，视图和表是相同的，两者的区别在于表中保存的是实际的数据，而视图中保存的是SELECT语句（视图本身并不存储数据）。

• 使用视图，可以轻松完成跨多表查询数据等复杂操作。

• 可以将常用的SELECT语句做成视图来使用。

• 创建视图需要使用CREATE VIEW语句。

• 视图不能使用ORDER BY

• 可对其进行有限制的更新，满足以下条件视图可被更新

  1. SELECT 子句中未使用 DISTINCT
  2. FROM 子句中只有一张表
  3. 未使用 GROUP BY 子句 进行聚合
  4. 未使用 HAVING 子句

• 删除视图需要使用DROP VIEW语句。

• 应该避免在视图的基础上创建视图，多重视图会降低 SQL 的性能 。

1.创建视图

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

SELECT 语句需要书写在 AS 关键字之后。 SELECT 语句中列的排列顺序和视图中列的排列顺序相同， SELECT 语句中的第 1 列就是视图中的 1 列， SELECT 语句中的第 2 列就是视图中的第 2 列，以此类推。视图的列名在视图名称之后的列表中定义,视图列名可以和select后面的列名不一致。 select语句中的from 表，如果这个表的数据有更新，那么视图也会跟着更新

2.删除视图

DROP VIEW 视图名称(<视图列名1>, <视图列名2>, ……)

3.例子

/*创建表并插入数据*/
CREATE TABLE PopTbl
(pref_name VARCHAR(32) PRIMARY KEY,
 population INTEGER NOT NULL);
-----------------------------------------------------------------------
INSERT INTO PopTbl VALUES('杭州', 100);
INSERT INTO PopTbl VALUES('湖州', 200);
INSERT INTO PopTbl VALUES('金华', 150);
INSERT INTO PopTbl VALUES('衢州', 200);
INSERT INTO PopTbl VALUES('宁波', 300);
INSERT INTO PopTbl VALUES('深圳', 100);
INSERT INTO PopTbl VALUES('广州', 200);
INSERT INTO PopTbl VALUES('东莞', 400);
INSERT INTO PopTbl VALUES('长沙', 50);
-----------------------------------------------------------------------
/*创建视图*/
create view day_look(province,population)
as
SELECT (case when city in('杭州','湖州','金华','衢州','宁波') then '浙江'
                        when city in('深圳','广州','东莞') then '广东'
                        else '湖南' end) area,sum(population)
from poptbl
group by area;
-----------------------------------------------------------------------
/*从视图中查询*/
select *
from day_look;

/*删除视图*/
drop view day_look;

二、子查询
子查询就是将用来定义视图的SELECT语句直接用于FROM子句当中 ,从这个角度看，子查询可以被看做是一次性的视图

• 单层子查询

/*该 SELECT 语句包含嵌套的结构，首先会执行 FROM 子句中的 SELECT 语句，然后才会执行外层的 SELECT 语句*/
select day_look.area,day_look.a as population
from (SELECT (case when city in('杭州','湖州','金华','衢州','宁波') then '浙江'
                        when city in('深圳','广州','东莞') then '广东'
                        else '湖南' end) area,sum(population) a
from poptbl
group by area) day_look;

• 多层子查询

  /*子查询必须添加别名*/
  select day_look2.*
  from
  (select day_look.*
  from (SELECT (case when city in('杭州','湖州','金华','衢州','宁波') then '浙江'
                          when city in('深圳','广州','东莞') then '广东'
                          else '湖南' end) area,sum(population) population
  from poptbl
  group by area) day_look
  where population>100) day_look2;
  

• 标量子查询

  标量子查询的书写位置并不仅仅局限于 WHERE 子句中，通常任何可以使用单一值的位置都可以使用。也就是说， 能够使用常数或者列名的地方，无论是 SELECT 子句、 GROUP BY 子句、 HAVING 子句，还是ORDER BY 子句，几乎所有的地方都可以使用。

  /*标量子查询就是返回单一值的子查询,因此，标量子查询的
  返回值可以用在 = 或者 <> 这样需要单一值的比较运算符之中。
  此例中标量查询为>后面的select语句*/
  select day_look.*
  from (SELECT (case when city in('杭州','湖州','金华','衢州','宁波') then '浙江'
                          when city in('深圳','广州','东莞') then '广东'
                          else '湖南' end) area,sum(population) population
  from poptbl
  group by area) day_look
  where day_look.population > (select avg(day_look.population)
  from (SELECT (case when city in('杭州','湖州','金华','衢州','宁波') then '浙江'
                          when city in('深圳','广州','东莞') then '广东'
                          else '湖南' end) area,sum(population) population
  from poptbl
  group by area) day_look)