【SQL实战进阶】——视图的定义、使用

🍉CSDN小墨&晓末:https://blog.csdn.net/jd1813346972

   个人介绍: 研二｜统计学｜干货分享
         擅长Python、Matlab、R等主流编程软件
         累计十余项国家级比赛奖项，参与研究经费10w、50w级横向

该篇文章主要利用15个案例帮助读者快速掌握SQL的视图相关用法，涉及视图的创建、删除、更新、更改等等。

1 背景和目的

  数据库视图（Database View）是数据库管理系统（DBMS）中的一个重要概念，它提供了一种虚拟表的形式，用于呈现存储在数据库中的数据。视图本身并不存储数据，而是基于一个或多个表（或其他视图）的查询结果动态生成。在商业数据实际操作和数据库管理工作种，视图具有关键价值。

  本文通过15个具体的实验实例，帮助读者掌握视图的定义和SQL命令的使用；视图的查询和SQL命令的使用；掌握视图的更新和SQL命令的使用；学习灵活熟练的进行视图的操作，认识视图的作用。

  实例讲解过程中，利用设计的学生-课程数据库系统进行演示，学生-课程数据库中三张关系表分别为：

  1.学生关系表S：

  2.课程关系表C：

  3.课程关系表SC ：

  在数据库中显示效果为：

2 设备需求

  Pentium 166MHz及以上微机；

  Windows XP/2000/7及以上操作系统；

  安装了SQL Server；

3 相关内容和步骤

  １． 视图的定义

  ２． 视图的查询

  ３． 视图的修改

  本文以S，C，SC表为基础完成以下视图定义及使用

4 相关实例

4.1 定义“IS”系学生基本情况视图V_IS 并查询结果

  创建视图：

create view V_IS as
select* from s
where sdept='计算机系'

  查询结果：

select*
from V_IS

  运行结果：

4.2 将S，C，SC表中学生的学号，姓名，课程号，课程名，成绩定义为视图V_S_C_G并查询结果;

  创建视图：

create view V_S_C_G as
select s.sno,sname,c.cno,cname,grade
from s,sc,c
where s.sno=sc.sno and c.cno=sc.cno

  查询结果：

select*
from V_S_C_G

  运行结果：

4.3 将各系学生人数，平均年龄定义为视图V_NUM_AVG并查询结果

  创建视图：

create view V_NUM_AVG as
select sdept,AVG(grade) agrade
from s,sc
where s.sno=sc.sno 
group by sdept 

  查询结果：

select* from V_NUM_AVG

  运行结果：

4.4 定义一个反映学生出生年份的视图V_YEAR并查询结果

  创建视图

create view V_YEAR as
select sno,sname,2019-sage bornyear
from s

  查询结果：

select* from V_YEAR

  运行结果：

4.5 将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G并查询结果

  创建视图：

create view V_AVG_S_G as
select s.sno,sname,COUNT(*) menshu,AVG(grade) agrade
from s,sc
where s.sno=sc.sno
group by s.sno,sname

  查询结果：

select* from V_AVG_S_G

  运行结果：

4.6 将各门课程的选修人数及平均成绩定义为视图V_AVG_C_G并查询结果

  创建视图：

select sno,sname,agrade
from V_AVG_S_G
where agrade>90

  查询结果：

select* from V_AVG_C_G

  运行结果：

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

  创建视图：

select sno,sname,agrade
from V_AVG_S_G
where agrade>90

  运行结果：

4.8 查询各课成绩均大于平均成绩的学生学号、姓名、课程和成绩

  运行程序：

select s.sno,sname,sc.cno,grade
from s,sc,V_AVG_C_G
where s.sno=sc.sno and sc.cno=V_AVG_C_G.cno
and grade>agrade

  运行结果：

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

  运行程序：

select sdept,COUNT(*) renshu
from s,V_AVG_S_G
where s.sno=V_AVG_S_G.sno and agrade>80
group by sdept 
order by renshu desc

  运行结果：

4.10 通过视图V_IS，分别将学号为“S1”和“S4”的学生姓名更改为“S1_MMM”,”S4_MMM” 并查询结果

（默认：s1=0811101，s4=0811102）

  更新视图：

update V_IS 
set sname='S1_MMM'
where sno='0811101'
update V_IS 
set sname='S4_MMM'
where sno='0811102'

  查询结果：

select* 
from V_IS 

  运行结果：

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

（注：多查了’女’的记录，方便完成题目）

  更新视图：

insert into V_IS
values('S12','YAN XI','女',19,'计算机系')

  查询结果：

select*
from V_IS

  运行结果：

4.12 通过视图V_IS，新增加一个学生记录 (‘S13’,‘YAN XI’,19,‘MA’)，并查询结果

（注：多查了’女’的记录，方便完成题目）

  更新视图：

insert into V_IS
values('S13','YAN XI','女',19,'MA')

  查询结果：

select* from V_IS

4.13 通过视图V_IS，删除学号为“S12”和“S3”的学生信息，并查询结果

（默认：s3=0811103，注：多了一条delete语句，为避免与外键约束冲突。）

  更新视图：

delete from sc
where sno='0811103'

delete from V_IS
where sno='s12'
or sno='0811103'

  查询结果：

select* from V_IS

  运行结果：

4.14 要通过视图V_S_C_G，将学号为“S12”的姓名改为“S12_MMM”，是否可以实现

  更新视图：

update V_S_C_G 
set sname ='S12_MMM'
where sno='0821101'

  查询结果：

select*
from V_S_C_G

  运行结果：

4.15 要通过视图V_AVG_S_G，将学号为“S1”的平均成绩改为90分，是否可以实现

  更新视图：

update V_AVG_S_G 
set agrade=90
where sno='0811101'

  不能实现，原因：