SSD7——数据库实验（三）数据库weixin43577675的博客-

数据库实验三

1．定义“SSCH”院学生基本情况视图V_SSCH；
即要对视图VIEW进行操作，那肯定先要创建一个视图
（其中，AS后面要为名为V_SSCH的视图提供一个查询，查询也是要指定表名和查询的限定条件）

CREATE VIEW V_SSCH AS SELECT * FROM S WHERE S.sdept = 'SSCH' 

创建之后再查一下看看有没有这个视图，结果显示查询成功

SELECT * FROM V_SSCH 

2．将S，C，SC表中学生的学号，姓名，课程号，课程名，成绩定义为视图V_S_C_G

CREATE VIEW V_S_C_G AS SELECT S.Sno,S.Sname,C.Cno,C.Cname,SC.grade FROM S,C,SC WHERE S.Sno = SC.Sno AND C.Cno = SC.Cno 

通过语句查询一下

SELECT * from V_S_C_G 

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

CREATE VIEW V_NUM_AVG AS SELECT S.sdept 院系, COUNT(S.Sno) 人数,AVG(S.Sage) 平均年龄 FROM S GROUP BY S.sdept  

最开始的时候，我犯了一个错误，那就是在创建一个视图的时候，每个列一定要有属性名，同时属性名不能重复

select * from V_NUM_AVG 

查询如下：

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

CREATE VIEW V_AVG_S_G AS SELECT COUNT(SC.Sno) 课程号, AVG(SC.grade) 平均成绩 FROM SC,C WHERE SC.Cno=C.Cno GROUP BY SC.Sno 

SELECT * FROM V_AVG_S_G 

如图：

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

SELECT SC.Sno,S.Sname,SC.grade FROM SC,S WHERE S.Sno = SC.Sno AND SC.Sno IN (SELECT SC.Sno FROM SC GROUP BY SC.Sno HAVING AVG(SC.grade) > 90) 

6．通过视图V_SSCH，新增加一个学生记录 (‘S12’,‘YAN XI’,19, ‘SSCH’)，并查询结果(显式)

INSERT INTO V_SSCH(Sno,Sname,Sage,Sdept) VALUES('S12','YAN XI',19, 'SSCH'); 

SELECT * FROM V_SSCH WHERE Sno = 'S12' 

7．通过视图V_SSCH，删除学号为“S12”学生信息，并查询结果

DELETE from V_SSCH WHERE Sno='S12';   

查询：

SELECT * FROM V_SSCH; 

8．将视图V_SSCH中学号为“S12”的学生改名“中南人”

UPDATE V_SSCH SET Sno='中南人' where Sno='S12' 

查询：

SELECT * from V_SSCH 

(二)

1. 在创建下列关系表时完成如下约束：定义实体完整性;参照完整性（外码、在删除S中的元组时级联删除SC中相应元组、当更新S中的Sno时同时更新SC中的Sno）;用户定义完整性：学生年龄<30。
   (这道题要是有点忘了知识点可以看看 这个博客)
   定义实体完整性：
   实体完整性用于保证数据库中数据表的每一个特定实体的记录都是唯一的，约束种类有：PRIMARY KEY(主键)约束、IDENTITY(自增)约束 和 UNIQUE(唯一)约束。自增和唯一在这个简单的表中用不到，不过定义主键还是非常有必要的，也就是主键约束。本题就需要写关于能给这三个表添加主键的SQL。
   给表S设一个主键约束，习惯上把编号或者流水号作为主键

ALTER TABLE S ADD CONSTRAINT constraint_s PRIMARY KEY(Sno) 

表C

ALTER TABLE C ADD CONSTRAINT constraint_c PRIMARY KEY(Cno) 

表SC，这里设置两个主键，因为表SC建立了表S与表C之间的关联

ALTER TABLE SC ADD CONSTRAINT constraint_sc PRIMARY KEY(Sno,Cno) 

参照完整性：
（建表时定义外键或者是添加外键，按照题目的意思应该是添加外键,同时需要清楚级联：on Delete cascade –级联删除
on update cascade –级联更新）

alter table SC    add constraint FK_SC_S    Foreign key(Sno) references S(Sno) ON DELETE CASCADE ON UPDATE CASCADE 

用户定义完整性
这个实际上就是检查约束

ALTER TABLE S ADD CONSTRAINT CK_S_Sage CHECK(Sage < 30) 

2. 修改S中的约束条件，学号在100－1000之间。

Alter table S add constraint S_Sno check(Sno between 100 and 1000) 

3．用实验验证当操作违反了完整性约束时，系统如何处理？

答：下面的两个图很好地表示出系统如何处理的，首先执行ALTER TABLE S ADD CONSTRAINT CK_S_Sage CHECK(Sage < 30) 这条SQL语句，也就是说限定学生年龄在30岁以下

在表中之间对之前某一个同学的年龄进行修改，比如我将“Smith”同学的年龄改为33，然后系统就报出异常，如下图所示，UPDATE语句和刚才名为”CK_S_Sage”的约束发生了冲突，语句终止。

实验三结束。