实例10(视图).sql

经典的sql资料

--上课内容：第六章 视图及索引——视图

-- 6.1 视图的创建和使用

 -- 6.1.1 视图的概念：从一个或多个数据表或视图派生出来的虚表

  -- 特点：

   -- 1. 视图存储了要执行检索的查询语句的定义，并不存储数据

   -- 2. 可以通过视图来查询、新增、修改、删除基表中的数据，但对数据要满足一定的条件 

   -- 3. 通过视图修改数据后，相应基表会发生变化；同时，基表变化时，会自动反映在视图中


  -- 功能：

   -- 1. 屏蔽数据复杂性（简化用户对数据库的操作；降低应用程序对底层表的依赖性，为数据库重构提供一定的逻辑独立性）

   -- 2. 简化权限管理（按需求重新划分数据，让不同的用户以不同的方式看到不同或者相同的数据集）


  -- 创建视图的原则：

   -- 1. 视图名称必须遵循标识符的规则，且对每个用户必须为唯一。此外，该名称不得与该用户拥有的任何表的名称相同。

   -- 2. 可以在其它视图和引用视图的过程之上建立视图,允许嵌套多达 32 级视图。

   -- 3. 不能将规则或 DEFAULT 定义与视图相关联。

   -- 4. 定义视图的查询不可以包含 ORDER BY、COMPUTE 或 COMPUTE BY 子句或 INTO 关键字。


  -- 使用视图的原则：

   -- 1. 修改视图中的数据时每次修改都只能影响一个基表

   -- 2. 不能修改那些通过计算得到的字段

   -- 3. 如果在创建视图时指定了WITH CHECK OPTION选项，那么所有使用视图修改数据库信息时，必须保证修改后的数据满足视图定义的范围

   -- 4. 执行UPDATE、DELETE命令时，所删除与更新的数据必须包含在视图的结果集中

   -- 5. 如果视图引用多个表时，无法用DELETE命令删除数据，若使用UPDATE命令则应与INSERT操作一样，被更新的列必须属于同一个表


 -- 6.1.2 在查询分析器里用Transact SQL语句 创建视图

  -- 6.1.2.1 语法：CREATE VIEW 视图名 [with encryption] AS 查询语句 [with check option]

    create table gamer(g_id int,g_name varchar(10),g_gender char(2),g_jf int)

    insert into gamer values(1,'aaa1','男',200)
    insert into gamer values(2,'aaa2','男',800)
    insert into gamer values(3,'aaa3','男',2000)
    insert into gamer values(4,'aaa4','男',1200)
    insert into gamer values(5,'aaa5','男',5600)
    insert into gamer values(6,'aaa6','女',8700)
    insert into gamer values(7,'aaa7','女',1000)
    select * from gamer

   -- 例: 创建一个视图显示所有游戏者的信息
    create view v_g as select * from gamer

  -- 注意：

   -- 在一个数据库中不能创建同名的视图
    create view v_g as select * from gamer   -- 创建同名的视图将会报错

   -- 在创建视图时，select 子句 不能有order by,compute,into子句
    create view v_g as select * from gamer order by g_jf  -- 创建带order by 的视图将会报错

  -- 6.1.2.2 查看视图内容
   select * from v_g

  -- 6.1.2.3 查看视图相关信息

   sp_help v_g       -- 查看视图信息

   sp_helptext v_g   -- 查看视图定义

   sp_depends v_g    -- 查看视图相关性

   -- 当我们只想让指定人查看视图内容而不让看到视图是如何定义时
   -- 要使用 with encription
   create view v_g1 with encryption as select * from gamer
   select * from v_g1
   sp_helptext v_g1

  -- 6.1.2.4 基表与视图相互作用

   -- 对视图内容进行修改，查看数据表的变化
   insert into v_g1 values(8,'aaa8','男',4500)
   select * from v_g1
   select * from gamer  -- 说明对视图的修改会修改基表内容

   -- 对数据表进行修改，查看视图的变化
   update gamer set g_jf=1200 where g_id=1
   select * from gamer
   select * from v_g1    -- 说明对表的修改会动态的显示在视图上

  -- 6.1.2.5 强制执行数据检查
   create view v_g2 as select * from gamer where g_jf>1900
   select * from v_g2
   select * from gamer   
   insert into v_g2 values(9,'aaa9','女',1800)   --不符合条件的记录也插入到gamer表里了
     
   -- 当我们在数据操作时要做条件检查
   -- 要使用 with check option
   create view v_g3 as select * from gamer where g_jf>1900 with check option
   insert into v_g3 values(10,'aaa10','女',1800)       --不符合条件的记录就出错了
   select * from v_g3
   select * from gamer  

  -- 对视图的修改可以是insert 也可以是update
   create view v_g4 as select g_id,g_jf,g_jf*1.1 as 优惠 from gamer
   update v_g4 set g_jf=400 where g_id=1

  -- 查看视图及基表数据的变化
   select * from v_g4
   select * from gamer


  -- 对视图的修改还可以delete
   delete from v_g4 where g_id=1

  -- 查看视图及基表数据的变化
   select * from v_g4
   select * from gamer


 -- 注意：
 
  -- 不能修改计算出来的字段
   update v_g4 set 优惠=400 where g_id=1 -- 报错

  -- 如果视图引用多个表时，无法用DELETE命令删除数据
   CREATE VIEW V_E AS SELECT S.T_NUMBER,S.T_NAME,E.T_GRADE   FROM STU_INFO AS S,EXAM AS E WHERE S.T_NUMBER=E.T_NUMBER
   DELETE FROM V_E WHERE T_NUMBER='20040301'  --提示：视图或函数 'V_E' 不可更新，因为修改会影响多个基表


 -- 6.1.2 修改视图

  -- 语法：ALTER VIEW 视图名 [with encryption] AS 查询语句 [with check option]

   -- 例：修改视图v_g1,使积分>1900
   alter  view v_g1 as select * from gamer where g_jf>1900
   select * from v_g1
   sp_helptext v_g1  -- 说明如果修改视图时不加with encryption,就默认没有了
   alter  view v_g1 with encryption as select * from gamer where g_jf>1900

   -- 同样
   alter  view v_g3 as select * from gamer where g_jf>2000
   select * from v_g3
   insert into v_g3 values(11,'aaa11','女',1800)
   select * from gamer -- 说明如果修改视图时不加with check option,就默认没有了  
   alter  view v_g3 as select * from gamer where g_jf>2000 with check option
   insert into v_g3 values(12,'aaa12','女',1800)

 -- 6.1.3 重命名视图
 sp_rename v_g4,v_g5
 select * from v_g5

 -- 6.1.4 删除视图
 drop view v_g5
 select * from v_g5
 


-- 练习：

-- 1. 创建emp61表（eid int,ename varchar(10),salary decimal(10,2)）, 插入数据

-- 2. 创建视图v_sal，使插入或修改的工资要大于2000,并验证 

-- 3. 修改视图v_sal，使输入的工资大于平均工资

-- 4. 在northwind数据库里，创建视图v_ps查询显示产品名称和供应商名称

-- 5. 修改视图v_ps 查询显示国家为'USA'的供应商名称、所在国家和产品名称

-- 6. 修改视图v_sal为v_salary

-- 7. 查看视图v_sal的信息、视图定义、视图相关性

-- 8. 删除视图v_ps







-- 复习：

 -- 创建视图基本语法：

  CREATE VIEW 视图名 [with encryption] AS 查询语句 [with check option]

   -- 如果有[with encryption] 其他人就看不到视图的定义

   -- 如果有[with check option] 对视图进行更新操作时做条件检查

   -- 定义视图的查询不可以包含 ORDER BY、COMPUTE 或 COMPUTE BY 子句或 INTO 关键字

   -- 不能将规则或 DEFAULT 定义与视图相关联

   -- 允许嵌套多达 32 级视图


 -- 修改视图基本语法：

  ALTER VIEW 视图名 [with encryption] AS 查询语句 [with check option]

   -- 如果有[with encryption] 其他人就看不到视图的定义

   -- 如果有[with check option] 对视图进行更新操作时做条件检查


 -- 使用视图对数据进行操作：

   -- 如果有[with check option],数据操作时作条件检查

   -- 修改视图中的数据时每次修改都只能影响一个基表

   -- 不能修改那些通过计算得到的字段

   -- 如果视图引用多个表时，无法用DELETE命令删除数据



-- 练习参考答案：

-- 1. 创建emp61表（eid int,ename varchar(10),salary decimal(10,2)）, 插入数据

create table emp61(eid int,ename char(10),salary decimal(10,2))
insert into emp61 values(1,'aaa1',1900)
insert into emp61 values(2,'aaa1',2500)
insert into emp61 values(3,'aaa1',3600)
insert into emp61 values(4,'aaa1',4800)
insert into emp61 values(5,'aaa1',1000)
select * from emp61


-- 2. 创建视图v_sal，使插入或修改的工资要大于2000,并验证 
create view v_sal as select * from emp61 where salary>2000 with check option
insert into v_sal values(6,'aaa6',1800)
update v_sal set salary=1800 where eid=2
select * from v_sal


-- 3. 修改视图v_sal，使输入的工资大于平均工资
alter view v_sal as select * from emp61 where (salary>(select avg(salary) from emp61)) with check option
select avg(salary) from emp61
insert into v_sal values(6,'aaa6',6800)


-- 4. 在northwind数据库里，创建视图v_ps查询显示产品名称和供应商名称
create view v_ps as select p.productname,s.companyname from products as p,suppliers as s where p.supplierid=s.supplierid

-- 5. 修改视图v_ps 查询显示国家为'USA'的供应商名称、所在国家和产品名称
alter view v_ps as select p.productname,s.companyname,s.country from products as p,suppliers as s where p.supplierid=s.supplierid and s.country='USA'
select * from v_ps

-- 6. 修改视图v_sal为v_salary
sp_rename v_sal,v_salary 
select * from v_salary

-- 7. 查看视图v_sal的信息、视图定义、视图相关性
sp_help v_salary
sp_helptext v_salary
sp_depends v_salary


-- 8. 删除视图v_ps
drop view v_ps
select * from v_ps