2021-07-21

SQL SERVER学习过程中的笔记代码，每天更新（7.21）

平台：
SQL SERVER 2019 ,
Microsoft SQL Server Management Studio 18
教材与部分代码来源：数据库系统概论（第五版）

今天主要学习了视图VIEW
注释在代码中

view.sql

--总结：视图是根据基本表来得到的，是一个虚表。数据库中只存放了他的定义没有存放数据
--修改视图属性等必须要先使用DROP来删除原来的视图定义，然后使用CREATE VIEW来重新生成视图定义
--所谓的使用UPDATE,INSERT,DELETE对视图操作实质上是对基本表的操作，然后视图根据修改后的基本表进行自动更新




--CREATE VIEW块 要求是批处理文件中仅有的文件,所以可用一对GO语句放置在CREATE VIEW块前后来解决
--视图定义中 WITH CHECK OPTION 作用：可以要求之后对视图更新或插入后的结果元组仍满足视图定义中的WHERE条件
/*
GO
CREATE VIEW IS_STUDENT
AS
SELECT SNO,SNAME,SAGE
FROM student
WHERE Sdept = 'IS'
WITH CHECK OPTION;
GO
*/



--也可以使用多个基本表创建视图
/*
GO
CREATE VIEW IS_S1(SN0,SNAME,GRADE)
AS
SELECT student.Sno,SNAME,GRADE
FROM student,SC
WHERE Sdept='IS' AND
	  student.SNO = SC.SNO AND
	  SC.CNO = '1';
GO
*/


--视图也可以建立在基本表和视图上
/*
GO
CREATE VIEW IS_S2
AS
SELECT SN0,SNAME,GRADE
FROM IS_S1
WHERE GRADE>=90;
GO
*/



--带表达式的视图：在视图定义中可以设置带表达式的属性。生成视图时会自动进行表达式的计算
/*
CREATE VIEW BT_S(SNO,SNAME,SBIRTH)
AS
SELECT SNO,SNAME,2021-SAGE			--使用表达式进行了年龄的计算
FROM student;
*/



--分组视图：使用带有聚集函数和GROUP BY子句的查询来定义视图
--GROUP BY可以将聚集函数的作用对象变为各个分组,一个分组一个函数值.GROUP BY按照属性来分组，属性值相同的分为一组。
--将学生的学号及平均成绩定义为一个视图
/*
GO
CREATE VIEW S_G(SNO,GAVG)
AS
SELECT SNO,AVG(GRADE)
FROM SC
GROUP BY SNO;
GO

SELECT* 
FROM S_G;
*/





--删除视图    DROP VIEW<VIEW NAME>[CASCADE];		SQL SERVER2019不支持CASCADE与DROP一起使用
--DROP语句用来从数据字典删除视图的定义
--教材定义：若视图上还导出了其他视图，则默认无法删除。使用CASCADE级联删除语句可以把该视图以及由它导出的所有视图一起删除
--但在SQL Server2019使用中，不支持DROP与CASCADE一起使用，不论有无导出关系都可以直接删除定义
/*
DROP VIEW BT_S;
DROP VIEW IS_S1;   --视图IS_S1上导出了视图IS_S2,删除IS_S1定义后，无法继续使用IS_S2，但IS_S2定义还在字典中。若回复IS_S1定义，IS_S2可以继续使用
*/





--查询视图 SELECT FROM WHERE GROUP BY HAVING 语句块
--查询信息系学生中年龄小于20岁的
/*
SELECT SNO
FROM IS_STUDENT
WHERE SAGE<20;
*/




--视图消解:DBMS在执行对视图的查询时，首先从数据字典中取出视图的定义，把定义中的SELECT子查询和用户的查询结合起来，
--转化成等价的对基本表的查询，然后再执行修正了的查询。




--更新视图：通过视图来对基本表进行插入、删除、修改数据 ， 本质上是对基本表进行操作
--UPDATE VIEW_NAME SET COLUMN WHERE...;
/*
UPDATE IS_STUDENT
SET SNAME = '张力'
WHERE SNO = '201215125';
SELECT* FROM IS_STUDENT;
SELECT* FROM student;			--可得知基本表也被更新了,本质上是通过视图来修改基本表
*/


--向视图插入行 ：INSERT INTO VIEW NAME VALUES(...);
--可以使用WITH CHECK OPTION语句，但是这个语句容易出问题：要想实现这个功能，视图定义中的属性必须包含视图定义时WHERE中的属性，
--比如WHERE中用到了Sdept属性，那么在视图的属性组中也要有Sdept属性，否则无法插入进去
--实际应用中，基本不会用到向视图中插入数据的操作
--例如：
/*
INSERT INTO IS_STUDENT
VALUES('201215129','赵新',20);			
--教材中有误，这里数据库引擎可能会自动给未列出的Sdept属性赋NULL值，与WHERE条件不符.WHERE中有对Sdept属性做出要求
--所以出现ERROR 550:“试图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某一视图指定了 WITH CHECK OPTION，而该操作的一个或多个结果行又不符合 CHECK OPTION 约束。”
*/
--正常例子
/*
GO
CREATE VIEW TEMP1
AS
SELECT*
FROM student
WHERE Sdept='IS'
WITH CHECK OPTION;
GO

SELECT* FROM TEMP1;

INSERT INTO TEMP1
VALUES('201215180','谢伊','男',20,'IS');			--正常插入

INSERT INTO TEMP1
VALUES('201215181','海尔森','男',20,'CS');		--若插入则会出现视图中的结果行不满足WITH CHECK OPTION条件，所以插入失败

SELECT* FROM TEMP1;
SELECT* FROM student;			--可以看出在基本表中也被插入了数据
*/



--删除视图中的记录： DELETE FROM VIEW_NAME WHERE...;   本质上是通过视图来删除基本表的元组
/*
INSERT INTO student			
VALUES('201215129','赵新','男',20,'IS');		--先插入一个元组
SELECT* FROM student;			
SELECT* FROM IS_STUDENT;			--可发现修改基本表后视图自动更新了

DELETE		
FROM IS_STUDENT
WHERE SNO = '201215129';						--将之前加入的记录删除
SELECT* FROM student;						--视图中的记录被删除
SELECT* FROM IS_STUDENT;					--基本表中的元组也被删除
*/



--注意，部分视图是不可以更新的
/*
UPDATE S_G
SET GAVG=90
WHERE SNO = '201215121';		
*/
--ERROR:对视图或函数 'S_G' 的更新或插入失败，因其包含派生域或常量域。
--在本例中，对视图的更新无法转换为对基本表的更新，因为GAVG是用聚合函数求得的关于多个属性列的值，无法修改各个成绩来使均分为90，所以S_G视图无法更新。



--视图是动态的，当修改了基本表后，视图会自动的根据修改后的基本表和视图的定义条件进行更新内容，不需要手动使用代码修改
--修改基本表
/*
--修改基本表
INSERT INTO student
VALUES ('19030000','艾吉奥','男',19,'IS');
INSERT INTO student
VALUES ('19030001','康纳','男',19,'CS');
--可发现视图随基本表自动修改了
SELECT* 
FROM IS_STUDENT;
--将胡乱添加的元组删除（什么刺客学校）
DELETE
FROM student
WHERE Sname='艾吉奥' OR SNAME='康纳';
*/



/*
--经验：有时可能出现已经建立了对象，但是提示对象名无效，此时可以通过 编辑-Intellisense--刷新本地缓存 来解决(快捷键CTRL-SHIFT-R)
SELECT * 
FROM IS_S1;
*/

部分教材中的定义与代码有错误，经过查官网文档后在代码中进行了纠正。