一、创建视图：
语法：
create view viewName as query_sql;



select * from customer;
select * from address;
select * from city;
select * from country;


select cu.customer_id,
       cu.first_name||''||cu.last_name as full_name,
       ad.address,
       cy.city,
       cr.country
from customer cu 
    inner join address ad on cu.address_id=ad.address_id
    inner join city cy on ad.city_id=cy.city_id 
    inner join country cr on cy.country_id=cr.country_id;
【注】：多张表连接时，可以把前面连接后的看作是一张新表与之后面的表来再次进行连接

或换种写法：
select cu.customer_id,
       cu.first_name||''||cu.last_name as full_name,
       ad.address,
       cy.city,
       cr.country
from customer cu 
    inner join address ad using(address_id)
    inner join city cy using(city_id) 
    inner join country cr using(country_id);



create view customer_master as 
select cu.customer_id,
       cu.first_name||''||cu.last_name as full_name,
       ad.address,
       cy.city,
       cr.country
from customer cu 
    inner join address ad on cu.address_id=ad.address_id
    inner join city cy on ad.city_id=cy.city_id 
    inner join country cr on cy.country_id=cr.country_id;
    

select * from customer_master;


【注】：视图不支持普通的增删改操作
update customer_master set city='Purwakarta1' where customer_id=524;
结果：
> 错误:  无法更新视图"customer_master"
DETAIL:  不来自单表或单视图的视图不能自动更新.
HINT:  启用对视图的更新操作, 需要提供INSTEAD OF UPDATE触发器或者一个无条件的 ON UPDATE DO INSTEAD 规则.


(2)、多临时表创建试图：
create view viewName as 
  with tmp_1 as (
    sqlstatemetn
  )
  ,
  tmp_2 as (
    select * from tmp_1
  )
  ,
  tmp3_1 as (
    select * from tmp_2
  ),
  select * from tmp_3;  #最终是将tmp_3临时表的数据作为试图viewName的数据



二、更新或替换视图
语法：
create or replace view viewName as query_sql;
【注】：更新或替换原视图只能在其原视图基础上增加列，不能删除原视图的列

案:1：
create or replace view customer_master as 
select cu.customer_id,
       cu.first_name||''||cu.last_name as full_name,
       ad.address,
       cy.city,
       cr.country,  
       cu.email       -- 更新替换原视图新增加一列，可成功
from customer cu 
    inner join address ad on cu.address_id=ad.address_id
    inner join city cy on ad.city_id=cy.city_id 
    inner join country cr on cy.country_id=cr.country_id;


select * from customer_master;


案例2：
create or replace view customer_master as 
select cu.customer_id,
       cu.first_name||''||cu.last_name as full_name,
       ad.address,
       cy.city,
    -- cr.country,  -- 删除原视图的一列，报错：> 错误:  无法从视图中删除列
       cu.email       -- 更新替换原视图新增加一列
from customer cu 
    inner join address ad on cu.address_id=ad.address_id
    inner join city cy on ad.city_id=cy.city_id 
    inner join country cr on cy.country_id=cr.country_id;



三、删除视图
语法：
drop view [if exists] viewName;
drop view if exists customer_master;

customer源表.png

address源表.png

city源表.png

country源表.png