数据库系统SSD7 实验2《表的查询、更新与视图操作》

素颜马尾好姑娘i 2021-11-09 03:26 622阅读 0赞

#### 实验2《表的查询、更新与视图操作》 ####

##### 一、实验目的 #####

1.  熟悉和掌握对数据表中数据的查询操作和SQL命令的使用，学会灵活熟练的使用SQL 语句的各种形式，加深理解关系运算的各种操作（尤其是关系的选择，投影，连接和除运算）；
2.  熟悉和掌握数据表中数据的插入、修改、删除操作和命令的使用（熟悉使用UPDATE/INSERT/DELETE语句进行表操作）；加深理解表的定义对数据更新的作用。
3.  熟悉和掌握对数据表中视图的定义操作和SQL命令的使用；
4.  熟悉和掌握对数据表中视图的查询操作和SQL命令的使用；
5.  熟悉和掌握对数据表中视图的更新操作和SQL命令的使用，并注意视图更新与基本表更新的区别与联系。

##### 二、实验内容 #####

（一）在表S，C，SC上完成以下查询：

1.  查询“CS”系学生的基本信息；
2.  查询“CS”系学生年龄不在19到21之间的学生的学号、姓名；
3.  找出各系年龄最大的学生，显示其学号、姓名；
4.  查询无先修课的课程的课程名和学时数；
5.  统计每位学生选修课程的门数、学分及其平均成绩；
6.  找出平均成绩在85分以上的学生，结果按系分组，并按平均成绩的升序排列；
7.  查询选修了课程名为“数据库系统”且成绩在60分以下的学生的学号、姓名和成绩；
8.  查询选修课程成绩至少有一门在80分以上的学生学号；

（二）在表S、C、SC中完成下列更新（在进行删除操作前要备份好数据）：

1.  在表S、C、SC上练习数据的插入、修改、删除操作。（比较在表上定义/未定义主码（Primary Key）或外码（Foreign Key）时的情况）
2.  如果要在表SC中插入某个学生的选课信息（如：学号为“20160105”，课程号为“c123”，成绩待定），应如何进行？
3.  将“CS”系全体学生的成绩置零；
4.  将学号为“S1”的学生的学号修改为“S001”；  
    5 把平均成绩大于80分的男同学的学号和平均成绩存入另一个表S——GRADE（SNO，AVG\_GRADE）；
5.  把选修了课程名为“数据结构”的学生的成绩提高10%；
6.  把选修了“C2”号课程，且成绩低于该门课程的平均成绩的学生成绩删除掉。

（三）以S , C , SC表为基础完成以下视图定义及使用

1.  定义“SSCH”院学生基本情况视图V\_SSCH；
2.  将S，C，SC表中学生的学号，姓名，课程号，课程名，成绩定义为视图V\_S\_C\_G;
3.  将各位学生选修课程的门数及平均成绩定义为视图V\_AVG\_S\_G并查询结果;
4.  查询平均成绩为90分以上的学生学号、姓名和成绩；
5.  通过视图V\_SSCH，新增加一个学生记录 (‘S15’,‘YAN XI’,19, ‘SSCH’)，并查询结果；
6.  将视图V\_SSCH中学号为“S15”的学生改名“中南人”。

##### 三、实验方法 #####

本次实验利用SQL Server 2017和Microsoft SQL Server Management Studio 17实验工具、SQL语言进行对数据库表和视图的基本操作，实验方案为参考书本知识及网络资料完成实验内容要求。

##### 四、实验步骤 #####

1.  阅读实验内容，整理本次实验需要用到的技术需求；
2.  完成对数据表S，C，SC的查询工作；
3.  在表S、C、SC中完成实验内容要求的更新工作；
4.  以S , C , SC表为基础完成视图定义及使用；
5.  总结实验过程，编写实验报告。

##### 五、实验结果 #####

（一）在表S，C，SC上完成以下查询：

1.  查询“CS”系学生的基本信息；

SELECT * from S where sdept = 'CS'

1.  查询“CS”系学生年龄不在19到21之间的学生的学号、姓名；

SELECT Sno, Sname from S where sdept = 'CS' and Sage<19 or Sage>21

1.  找出各系年龄最大的学生，显示其学号、姓名；

SELECT * from S a where Sage = (SELECT MAX(Sage) from S b where a.sdept=b.sdept )

1.  查询无先修课的课程的课程名和学时数；

SELECT Cname, Cperiod from C where trim(Cpno)=''

1.  统计每位学生选修课程的门数、学分及其平均成绩；

SELECT SC.Sno,count(SC.Cno) Cnum,sum(ccredit) Ccredit,avg(grade) Ave_grade
    from SC,C
    where SC.Cno=C.Cno
    group by Sno;

1.  找出平均成绩在85分以上的学生，结果按系分组，并按平均成绩的升序排列；

SELECT S.Sno,avg(grade) avg_grade,sdept
    from SC,S
    where SC.Sno=S.Sno
    group by S.Sno,sdept
    having avg(grade)>85
    order by avg_grade;
    select S.Sno,avg(grade) avg_grade,S.sdept
    from SC,S
    where SC.Sno=S.Sno
    group by S.Sno,Sdept
    having avg(grade)>=85
    order by avg_grade;

1.  查询选修了课程名为“数据库系统”且成绩在60分以下的学生的学号、姓名和成绩；

SELECT S.Sno, S.Sname, SC.grade
    from S,SC,C
    where SC.Cno=C.Cno and S.Sno=SC.Sno and C.Cname='数据库系统' and SC.grade<60;

1.  查询选修课程成绩至少有一门在80分以上的学生学号；

SELECT S.Sno
    from S,SC
    group by S.Sno
    having MAX(SC.grade)>80;

（二）在表S、C、SC中完成下列更新（在进行删除操作前要备份好数据）：

1.  在表S、C、SC上练习数据的插入、修改、删除操作。（比较在表上定义/未定义主码（Primary Key）或外码（Foreign Key）时的情况）  
    插入操作：

INSERT INTO C(Cno, Cname, Cpno, ccredit,Cperiod)
    	VALUES
    	('T1','测试数据','',1,12);
    
    INSERT INTO S (Sno, Sname, Ssex, Sage, sdept)
    	VALUES
    	(1111111111,'测试','男',11,'TEST');
    
    INSERT INTO SC(Sno, Cno, grade)
    	VALUES
    	(1111111111,'T1',88);

更新操作：

UPDATE C SET Cname = '修改数据' WHERE Cname = '测试数据'
    UPDATE S SET Sname = '修改' WHERE Sname = '测试'
    UPDATE SC SET Cno = 'ttt' WHERE Cno='T1'
    
    SELECT * from C where Cno='T1';
    SELECT * from S where Sno='1111111111';
    SELECT * from SC where Sno = '1111111111';

删除操作：

DELETE from C where Cno='T1';
    DELETE from S where Sno='1111111111';
    DELETE from SC where Sno = '1111111111';

1.  如果要在表SC中插入某个学生的选课信息（如：学号为“20160105”，课程号为“c123”，成绩待定），应如何进行？

--新建查询-输入插入代码
    INSERT INTO SC (Sno, Cno) VALUES (20160105, 'c123');

1.  将“CS”系全体学生的成绩置零；

UPDATE SC SET grade=0 WHERE Sno in (SELECT SC.Sno from SC,S WHERE SC.Sno= S.Sno and S.sdept='CS')

1.  将学号为“S1”的学生的学号修改为“S001”；

UPDATE S SET Sno='S001' WHERE Sno='S1'

1.  把平均成绩大于80分的男同学的学号和平均成绩存入另一个表S——GRADE（SNO，AVG\_GRADE）；

INSERT INTO SC(Sno, grade)
    SELECT SC.Sno, avg(SC.grade) from SC,S 
    where SC.Sno=S.Sno and S.Ssex='男' 
    group by SC.Sno
    having avg(SC.grade)>80

1.  把选修了课程名为“数据结构”的学生的成绩提高10%；

UPDATE SC SET Cno=Cno*1.10 
    where Sno in (SELECT * from SC,C where C.Cname='数据结构' and SC.Cno=C.Cno)

1.  把选修了“C2”号课程，且成绩低于该门课程的平均成绩的学生成绩删除掉。

SELECT * from SC where Cno='C2' and grade<(SELECT avg(grade) from SC)

（三）以S , C , SC表为基础完成以下视图定义及使用

1.  定义“SSCH”院学生基本情况视图V\_SSCH；

CREATE VIEW V_SSCH AS
    SELECT * from S
    where S.sdept='SE'
    
    SELECT * from V_SSCH

1.  将S，C，SC表中学生的学号，姓名，课程号，课程名，成绩定义为视图V\_S\_C\_G;

CREATE VIEW V_S_C_G
    AS
    SELECT S.Sno Sno, S.Sname Sname, C.Cno Cno, C.Cname Cname, SC.grade grade
    from SC, S, C
    where S.Sno=SC.Sno and SC.Cno=C.Cno;
    
    SELECT * from V_S_C_G

1.  将各位学生选修课程的门数及平均成绩定义为视图V\_AVG\_S\_G并查询结果;

CREATE VIEW V_AVG_S_G
    AS
    SELECT COUNT(SC.Sno) Cnum, AVG(SC.grade) Avg_grade
    from SC,C
    where SC.Cno=C.Cno
    group by SC.Sno;
    
    SELECT * from V_AVG_S_G

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

SELECT SC.Sno,S.Sname,SC.grade from SC,S
    where SC.Sno=S.Sno and SC.Sno in (SELECT SC.Sno from SC group by Sno having avg(grade)>90)

1.  通过视图V\_SSCH，新增加一个学生记录 (‘S15’,‘YAN XI’,19, ‘SSCH’)，并查询结果；

INSERT INTO V_SSCH (Sno, Sname, Sage, sdept) VALUES ('S15','YAN XI',19, 'SE')
    
    SELECT * from V_SSCH

1.  将视图V\_SSCH中学号为“S15”的学生改名“中南人”。

UPDATE V_SSCH SET Sno='中南人' where Sno='S15'
    SELECT * from V_SSCH

##### 六、实验结论 #####

实验结果中有部分要求与题目有区别，因为表在构建时与实际情况有差别，所以用等同其他类进行实验，原理是一样的。比如视图中要求系是“SSCH”，而表中并无该系，所以用“SE”系代替，效果是一样的。  
  实验结果与预期基本一致，无误差。  
  实验结论是利用数据库的增删改查操作和视图可以对数据库中的数据对多种方式的统计从而进行后续分析操作。

##### 七、实验小结 #####

通过本次实验主要是对数据库增删改查的函数操作、视图操作更加熟练了，能够对基本数据进行按要求处理然后进一步操作用于分析等。

遇到的问题主要有：  
  ①查询无先修课的课程的课程名和学时数时需要判空，当时列名 is null 一直无效；  
  解决方式：查阅网络资料，可使用trim（列名）=’’ 进行替代。  
  ②在3.4实验中利用group和having一直查询的结果中成绩中只有大于90的结果，而题目要求的平均成绩大于90的学生的所有成绩。  
  解决方式：利用in操作完成。

有待改进：部分操作可以有多种方式，可以进行比较然后选择择优操作。

参考文章：【1】[实验四 数据查询实验 - 程序园][_ -]

[_ -]: http://www.voidcn.com/article/p-efbuutiz-bo.html