「 视图 」

CREATE DATABASE EDUC1;

USE EDUC1;

--创建STUDENT表

CREATE TABLE STUDENT(

  sno CHAR(8) PRIMARY KEY,

  sname CHAR(8) NOT NULL,

  ssex CHAR(2),

  sage   INT,

  sdept  CHAR(10)

);

--创建COURSE表

CREATE TABLE COURSE(

       cno CHAR(2) PRIMARY KEY,

       cname CHAR(30),

       credit INT,     

       cpno CHAR(3)

);

--创建SC表

CREATE TABLE SC(

       sno CHAR(8),

       cno CHAR(2),

       grade INT,

       PRIMARY KEY (sno,cno),

       FOREIGN KEY(sno)REFERENCES STUDENT(sno),

       FOREIGN KEY(cno)REFERENCES COURSE(cno)

);

INSERT INTO STUDENT VALUES ('95001','李勇','男',20,'CS'),

                                             ('95002','刘晨','女',19,'IS '),

                                             ('95003','王额','女',18,'MA'),

                                             ('95004','张立','男',19,'IS '),

                                             ('95005','刘云云','女',18,'CS');

INSERT INTO COURSE VALUES ('1','数据库',4,'5'),

                                            ('2','数学',6,NULL),

                                            ('3','信息系统',3,'1'),

                                            ('4','操作系统',4,'6'),

                                            ('5','数据结构',4,'7'),

                                            ('6','数据处理',3,NULL),

                                            ('7','PASCAL语言',4,'6');

INSERT INTO SC VALUES ('95001','1',92),

                                     ('95001','2',85),

                                     ('95001','3',88),

                                     ('95002','2',90),

                                     ('95002','3',80),

                                     ('95003','2',85),

                                     ('95004','1',58),

                                     ('95004','2',85);

--（一）创建视图

--1、创建MAN_VIEW，内容包括所有男生。

CREATE VIEW MAN_VIEW

AS

SELECT * FROM STUDENT WHERE ssex = '男';

--2、创建学生成绩视图XSCJ_VIEW，包括所有学生的学号、姓名及其所学课程的课程名称和成绩。

CREATE VIEW XSCJ_VIEW(sno,sname,cname,grade)

AS

SELECT STUDENT.sno, sname, cname,grade FROM STUDENT,COURSE,SC

       WHERE STUDENT.sno =SC.sno AND SC.cno = COURSE.cno;

       DROP VIEW XSCJ_VIEW;

--3、基于XSCJ_VIEW视图，创建学生平均成绩视图AVG_VIEW，内容包括学生的学号、平均成绩。

CREATE VIEW AVG_VIEW(sno,avggrade)

AS

SELECT sno,AVG(grade) avggrade  FROM XSCJ_VIEW GROUP BY sno;

--（二）修改视图

--2.使用SQL语句修改视图

--修改AVG_VIEW视图，将该视图内容修改为课程名及每门课程的平均分。

ALTER VIEW AVG_VIEW(cname,avggrade)

AS

SELECT cname,AVG(grade) avggrade FROM SC,COURSE

       WHERE SC.cno = COURSE.cno

       GROUP BY COURSE.cname;

      

--(三)删除视图

--1.使用SSMS删除视图

--删除MAN_VIEW视图。

--2.使用SQL语句删除视图

--删除AVG_VIEW视图。

DROP VIEW AVG_VIEW;

--(四)使用视图操作表数据

--1.创建学生平均成绩视图XSAVG，通过此视图可查询平均分在70分及以上学生的情况，并按平均分降序排列，当平均分相同时按学号升序排列。

CREATE VIEW XSAVG

AS

SELECT TOP 100 PERCENT STUDENT.*,AVG(grade) avggrade FROM STUDENT JOIN SC

       ON STUDENT.sno = SC.sno

       GROUP BY STUDENT.sno,sname,ssex,sage, sdept HAVING AVG(grade) >= 70

       ORDER BY AVG(grade) DESC,STUDENT.sno ASC;

--2.创建每门课程的平均成绩视图KCAVG，通过此视图可查询平均分在75分以上的课程情况，并按平均分降序排列。

CREATE VIEW KCAVG

AS

SELECT TOP 100 PERCENT COURSE.cno,AVG(grade) avggrade FROM COURSE JOIN SC

       ON COURSE.cno = SC.cno

       GROUP BY COURSE.cno HAVING AVG(grade) >75

       ORDER BY AVG(grade);

--3.向“V_计算机系学生”视图中插入一条新记录，学号为95010，姓名是王庞。并查询插入记录后视图和基本表的情况，分析原因。

INSERT INTO V_计算机系学生 VALUES ('95010','王庞');

SELECT * FROM V_计算机系学生;

--4.向学生成绩视图XSCJ_VIEW中插入新记录，内容为：95010，王庞，数据库，60。并分析原因。

INSERT INTO XSCJ_VIEW VALUES ('95010','王庞','数据库',60);

/*视图或函数 'XSCJ_VIEW' 不可更新，因为修改会影响多个基表。

视图或函数依赖于多个表，并且你尝试更改其中的一个表时，会收到这样的错误消息。这是因为视图或函数是基于多个表的查询生成的，如果更改其中一个表，可能会影响视图的查询结果。

解决这个问题的方法通常有两种：

重建视图或函数：可以删除当前的视图或函数，并重新创建一个新的。这样，新的视图或函数将基于新的表结构生成。

更新视图或函数的定义：如果不想删除并重新创建视图或函数，可以尝试更新视图或函数的查询定义，以反映表结构的变化。

*/

--5.通过“V_计算机系学生”视图将学号为95005的学生的姓名改为“白晓云”，是否可以实现？查看结果并说明原因。

UPDATE V_计算机系学生 SET sname = '白晓云' WHERE sno = '95005';

SELECT * FROM V_计算机系学生;

/*

       该视图为行列子集视图，可进行更新

*/

--6.通过XSCJ_VIEW视图将所有学生的“数学”成绩减去2分。（注意检查更新前后的变化）

SELECT * FROM XSCJ_VIEW;

UPDATE XSCJ_VIEW SET grade = grade - 2 WHERE   cname = '数学';

--7.通过V_计算机系学生”视图，删除姓名为“白晓云”的记录。

DELETE FROM V_计算机系学生 WHERE sname = '白晓云';

SELECT * FROM V_计算机系学生;

--8.将各系学生人数，平均年龄定义为视图V_NUM_AVG，并查看。

CREATE VIEW V_NUM_AVG

AS

SELECT sdept,COUNT(*) NUM,AVG(sage) avgsage FROM STUDENT GROUP BY sdept;

SELECT * FROM V_NUM_AVG;

--9.定义一个反映学生出生年份的视图V_YEAR，并查看。

CREATE VIEW V_YEAR

AS

SELECT sno,sname,YEAR(GETDATE())-sage AS birthyear FROM STUDENT;

SELECT * FROM V_YEAR;

--10.将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G，并查看。

CREATE VIEW V_AVG_S_G

AS

SELECT STUDENT.sno,COUNT(*) number,AVG(grade) avggrade FROM STUDENT JOIN SC

       ON STUDENT.sno = SC.sno

       GROUP BY STUDENT.sno;

SELECT * FROM V_AVG_S_G;

--11.将各门课程的选修人数及平均成绩定义为视图V_AVG_C_G，并查看。

CREATE VIEW V_AVG_C_G

AS

SELECT cno,COUNT(*) number,AVG(grade) avggrade FROM SC

       GROUP BY cno;

SELECT * FROM V_AVG_C_G;

--12.要通过视图V_AVG_S_G,将学号为“95003”的平均成绩改为90分，是否可以实现？并说明原因。

UPDATE V_AVG_S_G SET avggrade = 90 WHERE sno = '95003';

/*

无法更改，其主要原因为 平均成绩avggrade是由聚集函数计算得到的，故此视图不允许更改

*/