数据库实验四 视图

---

一、实验目的：

1．掌握创建视图的SQL语句的用法。
2．掌握修改视图的方法。
3.熟悉和掌握对数据表中视图的查询操作和SQL命令的使用；
4.熟悉和掌握对数据表中视图的更新操作和SQL命令的使用，并注意视图更新与基本表更新的区别与联系
5.学习灵活熟练的进行视图的操作，认识视图的作用

---

二、实验环境：

Navicat 15 for MySQL

---

三、实验内容：

Part 1 定义视图

在数据库中，以Student、 Course 和sc 表为基础完成以下视图定义:

1. 定义计算机系学生基本情况视图V_Computer；

CREATE VIEW V_Computer
AS
SELECT Sno,Sname,Sage,sdept
FROM student
WHERE Sdept='CS'

2. 将Student Course 和sc表中学生的学号，姓名，课程号，课程名，成绩定义为视图V_S_C_G

CREATE VIEW V_S_C_G
AS
SELECT student.Sno,Sname,course.Cno,cname,grade
FROM student,course,sc
WHERE student.sno=sc.Sno AND course.cno=sc.Cno

3. 将各系学生人数，平均年龄定义为视图V_NUM_AVG

CREATE VIEW V_NUM_AVG(Sdept,stcount,avgage)
AS
SELECT Sdept,count(*),avg(sage)
FROM  student
GROUP BY student.Sdept

4. 将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G

CREATE VIEW V_AVG_S_G(sno,cnumber,ascore)
AS
SELECT sno,count(*),avg(grade)
FROM  sc
GROUP BY sc.Sno

5. 各门课程的选修人数及平均成绩定义为视图V_AVG_C_G

CREATE VIEW V_AVG_C_G(cno,stnumber,avgcnograde)
AS
SELECT cno,count(*),avg(grade)
FROM  sc
GROUP BY sc.cno

Part 2 使用视图

1. 查询视图V_S_C_G中所有记录。

SELECT *
FROM  V_S_C_G

2. 查询平均成绩为90分以上的学生学号、姓名和成绩；

SELECT V_S_C_G.sno,Sname,Grade
FROM  V_S_C_G,V_AVG_S_G
WHERE V_S_C_G.Sno=V_AVG_S_G.sno AND ascore>=90

3. 查询各门课程成绩均大于平均成绩的学生学号、姓名、课程和成绩；

  SELECT V_S_C_G.sno,Sname,Cno,Grade
	FROM  V_S_C_G,V_AVG_S_G
	WHERE V_S_C_G.Sno=V_AVG_S_G.Sno AND Grade>=ascore

4. 按系统计各系平均成绩在80分以上的人数，结果按降序排列；

 SELECT DISTINCT V_AVG_S_G.sno,V_S_C_G.Sname,ascore
FROM V_AVG_S_G,V_S_C_G
WHERE ascore>=80 AND V_AVG_S_G.sno=V_S_C_G.Sno
ORDER BY ascore DESC

Part3 修改视图

1. 通过视图V_Computer，将学号为“201215121”的学生姓名进行修改，并查询结果;
   修改sql：

    UPDATE V_Computer
SET Sname='张勇'
WHERE Sno='201215121'

查询sql：

SELECT *
FROM V_Computer

2. 通过视图V_Computer，新增加一个学生记录 (‘201215201’,‘YAN XI’,19,‘IS’)，并查询结果

INSERT
INTO V_Computer
VALUES('201215201','YAN XI','19','IS')

3. 通过视图V_Computer，新增加一个学生记录

INSERT
INTO V_Computer
VALUES('201215298','张弛华','20','CS')

4.要通过视图V_S_C_G，将学号为“201215127”的姓名改为“Weiwen”，是否可以实现？并说明原因

答：不可以实现，因为该视图是由Student Course 和sc三个基本表导出，此视图不可以更新！
5. 要通过视图V_AVG_S_G，将学号为“201215121”的平均成绩改为90分，是否可以实现？并说明原因

答：不可以实现，因为该视图中的字段cnumber,ascore来自字段表达式和聚集函数，不可以对视图进行操作！

---

本实验的基本表在数据库实验三 数据更新也有使用！！！