数据库原理及应用——数据更新和视图创建

Love The Way You Lie 2023-10-01 13:05 4阅读 0赞

## 实验三 数据更新和视图创建 ##

### **一、实验目的：** ###

1.  熟悉数据库的数据更新操作，能够使用SQL语句对数据库进行数据的插入、修改、删除操作；
2.  熟悉SQL语言有关视图的操作，能够熟练使用SQL语句来创建需要的视图，定义数据库外模式，并能使用所创建的视图实现数据管理。

### **二、实验要求：** ###

（1）针对“学生课程SC”数据库设计单元组插入、批量数据插入、修改数据和删除数据等SQL语句。理解和掌握INSERT、UPDATE和DELETE语法结构的各个组成成分，结合嵌套SQL子查询，分别设计几种不同形式的插入、修改和删除数据的语句，并调试成功。

（2）针对给定的数据库模式，以及相应的应用需求，创建视图、创建带WITH CHECK OPTION的视图，并验证视图WITH CHECK OPTION选项的有效性。理解和掌握视图消解执行原理，掌握可更新视图和不可更新视图的区别。

### **三、实验重点和难点：** ###

#### **实验重点：** ####

（1）插入、修改和删除数据的SQL。

（2）创建视图。

#### **实验难点：** ####

1.  与嵌套SQL子查询相结合的插入、修改和删除数据的SQL语句；利用一个表的数据来插入、修改和删除另外一个表的数据。
2.  可更新的视图和不可更新的视图之区别， WITH CHECK OPTION的验证。

### **四、相关知识：（电子教材：P113---P127）** ###

SQL Server提供了很多方法更新表中数据。以插入记录为例，可以使用INSERT语句、VALUES子句将特定值集合插入一行；可以使用数据库接口API（ADO、OLE DB、ODBC和DB-Library）中的函数进行数据插入等。本实验主要掌握INSERT、UPDATE和DELETE语句的基本用法。

（1）插入操作，其语句的一般格式为

INSERT INTO table\_or\_view \[(colum\_list)\]

VALUES (date\_values);

此语句是使date\_values作为一行数据记录插入已命名的表或视图table\_or\_view中。

Column\_list是由逗号分隔的table\_or\_view的列名列表，用来指定为其提供数据的列。如果没有指定column\_list，表或视图中的所有列都将接收数据。

如果column\_list没有为表或视图中的所有列命名，将在列表中没有命名的任何列中插入一个NULL值（或者在默认情况下为这些列定义的默认值）。在列的列表中没有指定的所有列都必须允许NULL值或者指定的默认值。否则SQL Server将返回一个错误。

（2）修改操作语句的一般格式为

UPDATE 表名 SET <列名>=<表达式>\[,<列名>=<表达式>\]……

\[where <条件>\];

其功能是修改指定表中满足WHERE子句条件的元组。其中SET子句用于指定修改方法，即用<表达式>的值取代相应的属性列值。如果省略WHERE子句，则表示要修改表中的所有元组。

（3）删除语句的一般格式为

DELETE FROM <表名>

\[WHERE <条件>\];

其功能是从指定表中删除满足WHERE子句条件的所有元组。如果省略WHERE子句，则表示要删除表中的所有元组，但表的定义仍在字典中。

（4）视图

视图是根据子模式建立的虚拟表。一个视图可以由一个表构造，也可以由多个表构造。利用集成管理中心和视图创建向导进行创建，查看和修改视图就如同对表的操作一样，非常容易。

视图一经定义，就可以和基本表一样被查询、删除。也可以在一个视图之上再定义新的视图，但对视图的更新（增、删、改）操作则有一定的限制。

视图的作用因应用需求的使用差异性而不同程度地体现。

### 五、实验内容 ###

#### （1）所使用的学生管理库中的三张表： ####

1、STUDENT（学生信息表）

<table> 
 <tbody> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:63pt;"> SNO（学号） </td> 
 <td style="border-color:#000000;vertical-align:top;width:72pt;"> SNAME（姓名） </td> 
 <td style="border-color:#000000;vertical-align:top;width:63pt;"> SEX（性别） </td> 
 <td style="border-color:#000000;vertical-align:top;width:78.15pt;"> SAGE（年龄） </td> 
 <td style="border-color:#000000;vertical-align:top;width:94.5pt;"> SDEPT（所在系） </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:63pt;"> 95001 </td> 
 <td style="vertical-align:top;width:72pt;"> 李勇 </td> 
 <td style="vertical-align:top;width:63pt;"> 男 </td> 
 <td style="vertical-align:top;width:78.15pt;"> 20 </td> 
 <td style="vertical-align:top;width:94.5pt;"> CS </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:63pt;"> 95002 </td> 
 <td style="vertical-align:top;width:72pt;"> 刘晨 </td> 
 <td style="vertical-align:top;width:63pt;"> 女 </td> 
 <td style="vertical-align:top;width:78.15pt;"> 19 </td> 
 <td style="vertical-align:top;width:94.5pt;"> IS </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:63pt;"> 95003 </td> 
 <td style="vertical-align:top;width:72pt;"> 王名 </td> 
 <td style="vertical-align:top;width:63pt;"> 女 </td> 
 <td style="vertical-align:top;width:78.15pt;"> 18 </td> 
 <td style="vertical-align:top;width:94.5pt;"> MA </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:63pt;"> 95004 </td> 
 <td style="vertical-align:top;width:72pt;"> 张立 </td> 
 <td style="vertical-align:top;width:63pt;"> 男 </td> 
 <td style="vertical-align:top;width:78.15pt;"> 19 </td> 
 <td style="vertical-align:top;width:94.5pt;"> IS </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:63pt;"> 95005 </td> 
 <td style="vertical-align:top;width:72pt;"> 李明 </td> 
 <td style="vertical-align:top;width:63pt;"> 男 </td> 
 <td style="vertical-align:top;width:78.15pt;"> 22 </td> 
 <td style="vertical-align:top;width:94.5pt;"> CS </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:63pt;"> 95006 </td> 
 <td style="vertical-align:top;width:72pt;"> 张小梅 </td> 
 <td style="vertical-align:top;width:63pt;"> 女 </td> 
 <td style="vertical-align:top;width:78.15pt;"> 23 </td> 
 <td style="vertical-align:top;width:94.5pt;"> IS </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:63pt;"> 95007 </td> 
 <td style="vertical-align:top;width:72pt;"> 封晓文 </td> 
 <td style="vertical-align:top;width:63pt;"> 女 </td> 
 <td style="vertical-align:top;width:78.15pt;"> 20 </td> 
 <td style="vertical-align:top;width:94.5pt;"> MA </td> 
 </tr> 
 </tbody> 
</table>

2.COURSE（课程表）

<table> 
 <tbody> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:71.4pt;"> CNO（课程号） </td> 
 <td style="border-color:#000000;vertical-align:top;width:111.95pt;"> CNAME（课程名） </td> 
 <td style="border-color:#000000;vertical-align:top;width:77.5pt;"> CPNO（先行课） </td> 
 <td style="border-color:#000000;vertical-align:top;width:95.55pt;"> CCREDIT（学分） </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:71.4pt;"> 1 </td> 
 <td style="vertical-align:top;width:111.95pt;"> 数据库 </td> 
 <td style="vertical-align:top;width:77.5pt;"> 5 </td> 
 <td style="vertical-align:top;width:95.55pt;"> 4 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:71.4pt;"> 2 </td> 
 <td style="vertical-align:top;width:111.95pt;"> 数学 </td> 
 <td style="vertical-align:top;width:77.5pt;"> &nbsp; </td> 
 <td style="vertical-align:top;width:95.55pt;"> 2 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:71.4pt;"> 3 </td> 
 <td style="vertical-align:top;width:111.95pt;"> 信息系统 </td> 
 <td style="vertical-align:top;width:77.5pt;"> 1 </td> 
 <td style="vertical-align:top;width:95.55pt;"> 4 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:71.4pt;"> 4 </td> 
 <td style="vertical-align:top;width:111.95pt;"> 操作系统 </td> 
 <td style="vertical-align:top;width:77.5pt;"> 6 </td> 
 <td style="vertical-align:top;width:95.55pt;"> 3 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:71.4pt;"> 5 </td> 
 <td style="vertical-align:top;width:111.95pt;"> 数据结构 </td> 
 <td style="vertical-align:top;width:77.5pt;"> 7 </td> 
 <td style="vertical-align:top;width:95.55pt;"> 4 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:71.4pt;"> 6 </td> 
 <td style="vertical-align:top;width:111.95pt;"> 数据处理 </td> 
 <td style="vertical-align:top;width:77.5pt;"> &nbsp; </td> 
 <td style="vertical-align:top;width:95.55pt;"> 2 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:71.4pt;"> 7 </td> 
 <td style="vertical-align:top;width:111.95pt;"> PASCAL语言 </td> 
 <td style="vertical-align:top;width:77.5pt;"> 6 </td> 
 <td style="vertical-align:top;width:95.55pt;"> 4 </td> 
 </tr> 
 </tbody> 
</table>

3、SC（选修表）

<table> 
 <tbody> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:117.55pt;"> SNO（学号） </td> 
 <td style="border-color:#000000;vertical-align:top;width:117.6pt;"> CNO（课程号） </td> 
 <td style="border-color:#000000;vertical-align:top;width:100.9pt;"> Grade(成绩) </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:117.55pt;"> 95001 </td> 
 <td style="vertical-align:top;width:117.6pt;"> 1 </td> 
 <td style="vertical-align:top;width:100.9pt;"> 92 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:117.55pt;"> 95001 </td> 
 <td style="vertical-align:top;width:117.6pt;"> 2 </td> 
 <td style="vertical-align:top;width:100.9pt;"> 85 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:117.55pt;"> 95001 </td> 
 <td style="vertical-align:top;width:117.6pt;"> 3 </td> 
 <td style="vertical-align:top;width:100.9pt;"> 88 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:117.55pt;"> 95002 </td> 
 <td style="vertical-align:top;width:117.6pt;"> 2 </td> 
 <td style="vertical-align:top;width:100.9pt;"> 90 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:117.55pt;"> 95002 </td> 
 <td style="vertical-align:top;width:117.6pt;"> 3 </td> 
 <td style="vertical-align:top;width:100.9pt;"> 80 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:117.55pt;"> 95003 </td> 
 <td style="vertical-align:top;width:117.6pt;"> 1 </td> 
 <td style="vertical-align:top;width:100.9pt;"> 78 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:117.55pt;"> 95003 </td> 
 <td style="vertical-align:top;width:117.6pt;"> 2 </td> 
 <td style="vertical-align:top;width:100.9pt;"> 80 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:117.55pt;"> 95004 </td> 
 <td style="vertical-align:top;width:117.6pt;"> 1 </td> 
 <td style="vertical-align:top;width:100.9pt;"> 90 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:117.55pt;"> 95004 </td> 
 <td style="vertical-align:top;width:117.6pt;"> 4 </td> 
 <td style="vertical-align:top;width:100.9pt;"> 60 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:117.55pt;"> 95005 </td> 
 <td style="vertical-align:top;width:117.6pt;"> 1 </td> 
 <td style="vertical-align:top;width:100.9pt;"> 80 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:117.55pt;"> 95005 </td> 
 <td style="vertical-align:top;width:117.6pt;"> 3 </td> 
 <td style="vertical-align:top;width:100.9pt;"> 89 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:117.55pt;"> 95006 </td> 
 <td style="vertical-align:top;width:117.6pt;"> 3 </td> 
 <td style="vertical-align:top;width:100.9pt;"> 80 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;width:117.55pt;"> 95007 </td> 
 <td style="vertical-align:top;width:117.6pt;"> 4 </td> 
 <td style="vertical-align:top;width:100.9pt;"> 65 </td> 
 </tr> 
 </tbody> 
</table>

#### **(2)****数据更新具体完成以下例题：** ####

1.插入一条**学号为“95050”**,**姓名为“陈冬”**的选课记录。

（**注：除学号、姓名为真实数据，其他数据随意。**）

insert

into student(Sno,Sname,Ssex,Sage,Sdept)

values('95050','陈冬','男','18','IS');

![53baae6a398348078418470962af9e37.png][]

2.将一新学生记录(**学号：99999；姓名：风清扬；性别：男；年龄：20；所在系：**

**计算机科学与技术系’CS’**)插入学生表中；

插入一条该生选课记录(**‘99999’,’4’,’60’**)。

insert

into student

values('99999','风清扬','男','20','CS');

insert

into sc

values('99999','4','60');

![7a0d9df6555b41b6a223101d4cb9a60f.png][]

3.对每一个系，求学生的平均年龄，并把结果存入数据库。

**（提示：对于这道题，首先要在数据库中建立一个有两个属性列的新表，其中一列存放系名，另一列存放相应系的学生平均年龄。**

**CREATE TABLE Deptage****（所在系 CHAR（20），平均年龄SMALLINT）；**

**然后对数据库的学生表按所在系分组求平均年龄，再把所在系和平均年龄存入新表中。）**

**create table Dept\_age**

**(Sdept char(15),**

**Avg\_age smallint);**

**insert**

**into dept\_age(Sdept,Avg\_age)**

**select Sdept,avg(Sage)**

**from student**

**group by Sdept;**

![3872c63fb09a46e6be5fd5822080f0e7.png][]

4.将学号“**95001**”的学生年龄改为22岁。

update student

set Sage=22

where Sno='95001';

![dc23aab3bb184d40988df1c8e5eeda98.png][]

5.将所有学生的年龄增加一岁。

update student

set Sage=Sage+1;

![df1509fd7018404a9e48fb11eabe2287.png][]

6.将课程“**数据库**”的成绩用五级制显示，成绩>=90为优秀，90>成绩>=80为良好，80>成绩>=70为中等，70>成绩>=60为及格，成绩<60为不及格。

select Sno,Cno,

(case when grade>=90 then'优秀'when grade>=80 then'良好'

when grade>=70 then'中等'when grade>=60 then'及格'else'不及格'end)as grade

from sc

where Cno='1';

![f71e9fe9291d491b8e888a22da4e7a5c.png][]

7.将计算机科学系“**CS**”全体学生的成绩置零。

select Sno,Cno,

(case when grade>=90 then'优秀'when grade>=80 then'良好'

when grade>=70 then'中等'when grade>=60 then'及格'else'不及格'end)as grade

from sc

where Cno=

(select Cno

from course

where Cname='数据库');

![9db7911c821348218aed6a007c3410c3.png][]

8.删除“**信息系统**”课程成绩小于李明学生成绩的记录。

delete

from sc

where Sno in(

select Sno

from sc

where (Grade<(

select Grade

from sc

where Sno=(

select Sno

from student

where Sname='李明')

and (Cno=(

select Cno

from course

where Cname='信息系统')

)

))

)

![8f7718d4967947679ed52c61635d80b0.png][]

9.删除学号为“**95006**”的学生记录。（**提示：注意外键的约束**）

set foreign\_key\_checks=0;

delete

from student

where Sno='95006';

set foreign\_key\_checks=1;

![dfad7a699d41404682f9070ed64b1c83.png][]

10. 删除计算机科学系“**CS**”所有学生的选课记录

delete

from sc

where Sno in

(select Sno

from student

where Sdept='CS');

![97e1a167ab834d7b85cc79a20b095792.png][]

#### **(3)****视图操作具体完成以下例题:** ####

1. 建立信息系“**IS**”学生的视图

create view IS\_Student

select Sno,Sname,Ssex,Sage

from student

where Sdept='IS';

![3cf9b810652b4ac9a018f3b435a7ce68.png][]

2.建立**信息系**“**IS**”学生的视图，并要求进行修改和插入操作时仍须保证该视图只有信息系的学生。

create view IS\_student

select Sno,Sname,Ssex,Sage

from student

where Sdept='IS'

with check option;

![00a113fe3f914e1ea141c3f28d2e2e4f.png][]

3.建立**信息系**“**IS**”选修了“**1**”号课程的学生视图

create view IS\_S1(Sno,Sname,Grade)

select student.Sno,Sname,Grade

from student,sc

where Sdept='IS'

and student.Sno=sc.Sno

and sc.Cno='1';

![01f0ea469cc14134b778c6f142e580ed.png][]

4. 建立**信息系**“**IS**”选修了“**1**”号课程且成绩在90分以上的学生视图

create view IS\_S2

select Sno,Sname,Grade

from is\_s1

where Grade>=90;

![ded5804080cc45f4b6692ad0d48ef7b6.png][]

5. 定义一个反映学生出生年份的视图（**提示：将年龄数值属性体现为“出生年份”**）

create view BT\_S(Sno,Sname,Sbirth)

select Sno,Sname,2021-Sage

from student;

![cf8a70612f01420cb18254a6218e466f.png][]

### 六、实验结果与分析（概括、分析与总结）： ###

上述结果中，部分题有多解法，这里只写了一种方法，运用到一些关键词update、delete等，以及创建视图create view……，其中还用了一些语句加以修饰，例如：with check option可以表示对视图进行更改等操作时要保证更新、插入或删除的行满足视图定义中的谓词条件；set foreign\_key\_checks=0可以在删除元组时关闭外键的介入进而删除元组，删除后在等于1即可；这些关键词等，可以轻松的解决题目。

### 七、实验心得： ###

本次实验，将本节的数据更新以及视图进行实践。通过实践，不仅可以加强对查询语句的记忆以及其他关键词的用法，使得mysql语句有了更深的记忆，其次对插入、删除或更新等关键词有了进一步的使用，还进而学习到了创建视图。对本次实验，收获颇多，对于今后的学习有了更好的理解和帮助。

[53baae6a398348078418470962af9e37.png]: https://img-blog.csdnimg.cn/53baae6a398348078418470962af9e37.png
[7a0d9df6555b41b6a223101d4cb9a60f.png]: https://img-blog.csdnimg.cn/7a0d9df6555b41b6a223101d4cb9a60f.png
[3872c63fb09a46e6be5fd5822080f0e7.png]: https://img-blog.csdnimg.cn/3872c63fb09a46e6be5fd5822080f0e7.png
[dc23aab3bb184d40988df1c8e5eeda98.png]: https://img-blog.csdnimg.cn/dc23aab3bb184d40988df1c8e5eeda98.png
[df1509fd7018404a9e48fb11eabe2287.png]: https://img-blog.csdnimg.cn/df1509fd7018404a9e48fb11eabe2287.png
[f71e9fe9291d491b8e888a22da4e7a5c.png]: https://img-blog.csdnimg.cn/f71e9fe9291d491b8e888a22da4e7a5c.png
[9db7911c821348218aed6a007c3410c3.png]: https://img-blog.csdnimg.cn/9db7911c821348218aed6a007c3410c3.png
[8f7718d4967947679ed52c61635d80b0.png]: https://img-blog.csdnimg.cn/8f7718d4967947679ed52c61635d80b0.png
[dfad7a699d41404682f9070ed64b1c83.png]: https://img-blog.csdnimg.cn/dfad7a699d41404682f9070ed64b1c83.png
[97e1a167ab834d7b85cc79a20b095792.png]: https://img-blog.csdnimg.cn/97e1a167ab834d7b85cc79a20b095792.png
[3cf9b810652b4ac9a018f3b435a7ce68.png]: https://img-blog.csdnimg.cn/3cf9b810652b4ac9a018f3b435a7ce68.png
[00a113fe3f914e1ea141c3f28d2e2e4f.png]: https://img-blog.csdnimg.cn/00a113fe3f914e1ea141c3f28d2e2e4f.png
[01f0ea469cc14134b778c6f142e580ed.png]: https://img-blog.csdnimg.cn/01f0ea469cc14134b778c6f142e580ed.png
[ded5804080cc45f4b6692ad0d48ef7b6.png]: https://img-blog.csdnimg.cn/ded5804080cc45f4b6692ad0d48ef7b6.png
[cf8a70612f01420cb18254a6218e466f.png]: https://img-blog.csdnimg.cn/cf8a70612f01420cb18254a6218e466f.png