数据库实验1-6参考答案

实验一 SQL Server使用初步

一、实验目的

1、熟悉SQL Server2000的组成及基本功能。 2、掌握SQL Server2000的登录及注册。

3、掌握SQL Server2000企业管理器的使用方法。 4、熟悉查询分析器的基本使用。

二、实验预习

1、什么是数据库管理系统DBMS？你所知道的DBMS有哪些？

答：DBMS是位于用户和操作系统之间的一层数据管理软件。常见的DBMS主要有：Oracle、db2、SQL Server、MySQL、PostgreSQL、SQLite、Firebird等等。

2、SQL Server 2000(2005)的安装步骤？ 答：以企业版安装为例，步骤为：

? 将企业版安装光盘插入光驱后，出现以下提示框。请选择 “安装 SQL Server

2000 组件”

? 出现下面对话框后，选择 \安装数据库服务器\。 ? 选择 \下一步\，然后选择 \本地计算机\进行安装。

? 在 \安装选择\窗口，选择 \创建新的SQL Server实例...\。对于初次安装的

用户，应选用这一安装模式，不需要使用 \高级选项\进行安装。 \高级选项\中的内容均可在安装完成后进行调整。

? 在 \用户信息\窗口，输入用户信息，并接受软件许可证协议。 ? 在 “安装定义”窗口，选择 “服务器和客户端工具” 选项进行安装。 ? 在 “实例名” 窗口，选择 “默认” 的实例名称。

? 在 “安装类型” 窗口，选择 “典型” 安装选项，并指定 “目的文件夹”。 ? 在 \服务账号\窗口，请选择 \对每个服务使用统一账户...\的选项。 ? 在 \身份验证模式\窗口，选择 \混合模式...\选项，并设置管理员\账号

的密码。

? 最后按“下一步”即可完成安装。

? 检测安装：如果安装成功，应该能成功启动SQL Server，并且能和SQL Server

客户端连接上。

? 可以通过服务管理器来进行启动。

三、实验内容和要求

1、注册服务器 基本步骤：

（1）打开企业管理器

（2）右击SQL Server组→新建SQL Server注册

（3）添加可用的服务器（实际数据库服务器的名称或IP地址） （4）选择身份验证模式（选“系统管理员分配给我的登录信息”） （5）输入正确的登录名和密码

（6）选择SQL Server组（选“在现有的SQL Server组中添加SQL Server”） （7）完成注册

若注册成功，则显示注册成功的信息。 2、连接SQL Server服务器

（1）右键单击上面注册的数据库服务器，选择连接，建立与数据库服务器的连接。观察连接后服务器图标的变化；

（2）右键单击选择编辑SQL Server注册属性，观察已注册数据库服务器的属性信息；

（3）右键单击选择删除SQL Server注册。为保证数据库的安全性，使用完毕自己的数据库后，可采取删除的方式，断开与数据库的连接； （4）重复注册服务器的步骤，再次建立与数据库的连接； 3、熟悉企业管理器

（1）单击建立的服务器连接，观察服务器的7个项目，写出它们的名称。通过查看联机帮助，总结7个项目的基本功能。

（2）单击数据库，观察Northwind数据库下的11个项目，写出项目名称，通过联机帮助了解它们的基本功能。

（3）查看Northwind的表项目，单击表，观察表的名称、所有者、类型以及创建日期。回答：

? 这些表的所有者有哪几种？

? 这些表的类型有哪几种？

? 选择表Employees，在右键菜单中选择打开表->返回所有行，观察表中的数

据，说出这些数据的实际含义。观察其他用户类型的表，你还能说出它们数据的实际含义吗？

2

（4）查看Northwind的视图项目，单击视图，观察视图的名称、所有者、类型以及创建日期。选择视图Product Sales for 1997，同上面观察表中数据的方法一样，观察视图中的数据，说出这些数据的意义。

（5）查看Northwind的用户项目，单击用户。回答：

? 有哪类用户？查看它们的属性对话框，它们的角色和权限是否相同？

? 通过联机帮助，写出dbo、Guest用户的区别。

（6）查看Northwind的角色项目，单击角色。回答：

? 有哪些角色类型？通过查看联机帮助，写出它们各自的含义和作用。

（7）查看Northwind的安全性项目，单击项目中的登录，观察不同的登录名称、类型、服务器访问、默认数据库，找到你自己的登录名称，右键单击打开属性对话框，观察对话框中的设置，写出登录属性对话框中的设置作用。

4、熟悉查询分析器

（1）打开查询分析器。基本步骤：

? 单击注册的数据库服务器，确定服务器已经连接； ? 在主菜单“工具”中选择“SQL查询分析器”；

? 在打开的查询分析器窗口中，左侧的对象浏览器为注册的服务器，在工具栏

的数据库选择中选择Northwind数据库为当前数据库；右侧窗口为查询窗口。

（2）分别在查询窗口中输入如下查询语句，观察查询结果，写出结果的数据记录个数：

查询语句1：

SELECT *

FROM Shippers

ORDER BY CompanyName

结果记录数：3 查询语句2：

SELECT FirstName, HomePhone FROM Northwind.dbo.Employees ORDER BY FirstName ASC

结果记录数：9

查询语句3：

3

SELECT ROUND( (UnitPrice * .9), 2) AS DiscountPrice FROM Products

WHERE ProductID = 58

结果记录数：1

查询语句4：

SELECT OrderID,

DATEDIFF(dd, ShippedDate, GETDATE() ) AS DaysSinceShipped FROM Northwind.dbo.Orders WHERE ShippedDate IS NOT NULL

结果记录数：809

查询语句5：

SELECT ProductID, ProductName FROM Northwind.dbo.Products

WHERE CategoryID = 1 OR CategoryID = 4 OR CategoryID = 5

结果记录数：29

四、实验小结

五、评阅成绩

实验预习20% 实验过程20% 实验结果30% 实验报告30% 总成绩

4

实验二 数据定义

一、实验目的

1、掌握SQL数据定义功能：数据库定义、表的定义、索引定义。 2、掌握利用企业管理器和SQL语句定义表、索引的方法。

二、实验预习

1、SQL中基本表定义语句格式：

2、SQL中修改基本表语句格式：

三、实验内容和要求

1、在企业管理器中，利用菜单操作的方式在各自的数据库中建立如下四个基本表： （1）供应商表S： 列名 SNO SNAME STATUS CITY 说明 供应商号 供应商名 供应商状态 所在城市 数据类型 CHAR(6) VARCHAR(20) VARCHAR(50) VARCHAR(50) 约束 PRIMARY KEY NOT NULL （2）零件表P： 列名 PNO PNAME

说明 零件号 零件名 数据类型 CHAR(6) VARCHAR(20) 5

约束 PRIMARY KEY NOT NULL

COLOR WEIGHT 颜色 重量 CHAR(2) NUMERIC(9,2) CHECK(WEIGHT>0 AND WEIGHT<=100) （3）工程项目表J： 列名 JNO JNAME CITY 说明 项目号 项目名 城市 数据类型 CHAR(6) VARCHAR(20) VARCHAR(50) 约束 PRIMARY KEY NOT NULL （4）供应情况表SPJ： 列名 SNO PNO JNO QTY 说明 供应商号 零件号 项目号 供应数量 数据类型 CHAR(6) CHAR(6) CHAR(6) SMALLINT 约束 NOT NULL NOT NULL NOT NULL DEFAULT 100 2、用CREATE语句建立如下三个表，并写出相应的语句。 （1）学生表Student： 列名 SNO SNAME SSEX SAGE SDEPT 说明 学号 姓名 性别 年龄 所在系 数据类型 CHAR(7) CHAR(10) CHAR(2) SMALLINT VARCHAR(20) 约束 主码 NOT NULL 取“男”或“女” 取值15-45 默认“计算机系” 语句：

CREATE TABLE Student( Sno char(7) PRIMARY KEY, Sname char(10) not null, Ssex char(2) CHECK(Ssex='男' or Ssex='女'), Sage smallint CHECK(Sage>=15 and Sage<=45), Sdept char(20) DEFAULT '计算机系'

)

6

（2）课程表Course： 列名 CNO CNAME CCREDIT SEMSTER PERIOD 说明 课程号 课程名 学分 学期 学时 数据类型 CHAR(10) VARCHAR(20) SMALLINT SMALLINT SMALLINT 约束 主码 NOT NULL 大于0 大于0 大于0 语句：

CREATE TABLE Course( Cno char(10) PRIMARY KEY, Cname varchar(20) NOT NULL, Ccredit smallint check(ccredit>0), semster smallint check(semster >0), period smallint check(period>0)

)

（3）选课表Sc： 列名 SNO CNO GRADE 说明 学号 课程号 成绩 数据类型 CHAR(7) CHAR(10) SMALLINT 约束 主码，引用Student的外码 主码，引用Course的外码 大于0 语句： CREATE TABLE SC(

Sno char(7), Cno char(10),

Grade smallint check(grade>=0),

PRIMARY KEY (Sno,Cno),

FOREIGN KEY (Cno) REFERENCES Course(Cno), FOREIGN KEY (Sno) REFERENCES Student(Sno) )

3、利用SQL语句对表结构进行修改。

（1）为零件表P增加一个规格(GUIGE)列，数据类型为字符，长度50；

Alter Table P Add GUIGE char(50)

7

（2）修改课程表Course的CNAME属性列的类型为VARCHAR(30)； Alter Table Course Alter Column CNAME varchar(30)

（3）为供应情况表SPJ添加参照完整性约束； SPJ的SNO列参照S表SNO列

Alter Table SPJ Add Foreign Key (SNO) References S(SNO)

SPJ的PNO列参照P表PNO列

Alter Table SPJ Add Foreign Key (PNO) References P(PNO)

SPJ的JNO列参照J表JNO列

Alter Table SPJ Add Foreign Key (JNO) References J(JNO)

（4）删除零件表P的规格(GUIGE)列 Alter Table P Drop Column GUIGE

4、利用企业管理器向表中添加数据 （1）供应商表S： SNO S1 S2 SNAME 精益 盛锡 STATUS 20 10 CITY 天津 北京

继续往表中增加一条记录：四个字段的数据分别是S1,东方红，30,北京，会出现什么情况？为什么？

不能增加，因为SNO是主键，不能取重复值。

（2）零件表P： PNO P1 P2 P3 PNAME 螺母 螺栓 螺丝刀 COLOR 红 绿 蓝 WEIGHT 12 17 105.5

是否能够正常输入三条数据？如果否，问题出在哪里，为什么？

第三条记录不能输入，因为P3的WEIGHT属性取值违法了其检查约束。

8

（3）工程项目表J： JNO J1 J2 J3 JNAME 一汽 半导体厂 CITY 北京 南京 常州

第三条记录是否能够正常输入，若否，会出现什么问题，为什么？ 第三条记录不能输入，因为JNAME不能取NULL值。

（4）供应情况表SPJ： SN0 S1 S1 S2 PNO P1 P1 P2 JNO J1 J3 J4 QTY 200

输入过程中是否会遇到问题，若有，出在哪，为什么？

第三条记录不能输入，因为在工程项目表J中没有J3，破坏了参考完整性。

四、实验小结

五、评阅成绩

实验预习20% 实验过程20% 实验结果30% 实验报告30% 总成绩 9

实验三 数据查询

一、实验目的

1、掌握查询语句的基本组成和使用方法 2、掌握常用查询技巧

二、实验预习

1、SQL中查询语句格式：

2、连接查询有哪些不同的连接方式？有什么特点。

三、实验内容和要求

1、按照下表中的内容，在企业管理器中为数据库表输入相应的数据。 学生表：Student Sno 9512101 9512103 9521101 9521102 9521103 9531101 9531102 Cno C01 C02 C03 C04 C05

Sname 李勇 王敏 张莉 吴宾 张海 钱小平 王大力 Cname 计算机导论 VB 计算机网络 数据库基础 高等数学 Ssex 男 女 女 男 男 女 男 Ccredit 3 4 4 6 8 10

Sage 19 20 22 21 20 18 19 Semster 1 3 7 6 1 Sdept 计算机系 计算机系 信息系 信息系 信息系 数学系 数学系 Period 3 4 4 4 8 课程表：Course

选课表：SC Sno 9512101 9512103 9512101 9512103 9521101 9521102 9521103 9531101 9531102 9512101 9531102 9512101 9512101 Cno C03 C03 C05 C05 C05 C05 C05 C05 C05 C01 C01 C02 C04 Grade 95 51 80 60 72 80 45 81 94 NULL NULL 87 76

2、完成下列查询

（1）查询全体学生的信息。

select * from student

（2）查询“信息系”学生的学号，姓名和出生年份。

select Sno,Sname,2009-Sage as Birthyear from student where Sdept='信息系'

（3）查询考试不及格的学生的学号。

select Distinct Sno from SC where Grade<60

（4）查询无考试成绩的学生的学号和相应的课程号。

select Sno,Cno from SC where Grade is null

（5）将学生按年龄升序排序。

select * from student order by Sage asc

（6）查询选修了课程的学生的学号和姓名。

11

Select Sno,Sname from Student Where Sno in (Select Sno From Sc) 或：

select distinct student.Sno,Sname from student,SC where student.Sno=SC.Sno

（7）查询年龄在20-23岁之间的学生的姓名，系，年龄。

select Sname,Sage,Sdept from student where Sage between 20 and 23

（8）查询同时选修了“计算机导论”，“高等数学”课程的学生的学号，姓名。

select student.Sno,Sname from student where NOT EXISTS( select* from course where

Cname in('高等数学','计算机导论') AND NOT EXISTS(select * from SC where Sno=student.Sno and Cno=course.Cno)) 或：

select student.Sno,Sname from student,sc,Course where student.sno=sc.sno and sc.cno=course.cno and cname='高等数学' and student.sno in (select sno from sc where cno=(select cno from course where cname='计算机导论')) 或：

select student.Sno,Sname from student where sno in (select sno from sc where cno=(select cno from course where cname='高等数学')) and sno in (select sno from sc where cno=(select cno from course where cname='计算机导论'))

（9）查询姓“张”的学生的基本信息。

select * from student where Sname like '张%'

（10）查询“95211”班学生的选课情况，要求输出学号，姓名，课程名，成绩，按照学号升序排序。

select student.Sno,Sname,Cname,Grade from student,SC,course where student.Sno=SC.Sno and course.Cno=SC.Cno

and student.Sno like '95211%' order by student.Sno 或：

select student.Sno,Sname,Cname,Grade from student,SC,course where student.Sno=SC.Sno and course.Cno=SC.Cno

and left(student.sno,5)= '95211' order by 1

（11）查询选修了课程的学生的总人数。

12

select count(distinct sno) from SC

（12）查询选修了“C05”课程的的学生成绩单，要求输出学号，姓名，成绩，结果按班级升序，成绩降序排列。

select student.Sno,Sname,Grade from student,SC where student.Sno=SC.Sno and Cno='C05' order by left(student.Sno,5) asc,Grade desc

（13）统计各门课程的成绩，要求输出课程代号，课程名，平均成绩，选修人数。（成绩为NULL值的不统计）

select course.Cno,Cname,avg(Grade),count(Sno) from course,Sc where course.Cno=SC.Cno and Grade is not null group by Course.Cno,Cname

（14）统计各门课程的不及格人数，要求输出课程代号，课程名，不及格人数。

select Course.Cno,Cname,count(Sno) from SC,Course where SC.Cno=course.Cno and Grade<60 group by Course.Cno,Cname

（15）查询选修平均成绩在75分以上的学生的学号，姓名，所在系。

select sc.sno,sname,sdept from student,sc where student.sno=sc.sno group by sc.sno,sname,sdept having avg(grade)>75 或：

select sno,sname,sdept from student where Sno in (Select Sno From SC Group By Sno Having Avg(Grade)>75)

（16）查询与“王大力”同一个系的学生的基本信息

select * from student where sdept in(select sdept from student where sname='王大力')

（17）查询选修平均分高于所有学生平均分的学生的学号，并按学号升序排列。

select student.Sno from student,SC where student.Sno=SC.Sno group by student.Sno having avg(Grade)>(select Avg(Grade) from SC) order by student.Sno asc

（18）查询未选修“VB”或“数据库基础”两门课的学生的学号，姓名，系名。（要

13

求用嵌套查询）

select sno,sname,sdept from student where sno not in(select sno from sc where cno in(select cno from course where cname in('VB','数据库基础'))) 或：

select sno,sname,sdept from Student where exists(

select * from Course where cname='VB' and not exists(

select * from SC where sno=Student.sno and cno=Course.cno and Course.cname!='数据库基础'))

（19）查询选修了全部课程的学生的学号，姓名，系名。

select sno,sname,sdept from student

where not exists(select * from course where not exists

(select * from sc where sno=student.sno and cno=course.cno ) ) 或：

select sno,sname,sdept from student

where sno in (select sno from sc group by sno having count(cno)=(select coount(*) from course))

（20）输出“高等数学”课程成绩前三名的学生的学号，姓名，系名

select top 3 student.sno,sname,sdept from student,sc,course where student.sno=sc.sno and sc.cno=course.cno and cname='高等数学' order by grade desc

四、实验小结

五、评阅成绩

实验预习20% 实验过程20% 实验结果30% 实验报告30% 总成绩 14

实验四 数据更新

一、实验目的

1、掌握SQL语言的数据更新操作

2、掌握SQL Server 2000企业管理器的数据导入和导出功能

二、实验预习

1、数据插入语句格式：

2、数据修改语句格式：

3、数据删除语句格式： 4、SQL Server中可进行批量数据导入和导出，可支持哪些格式的数据导入导出？（举常见格式类型）

三、实验内容和要求

（执行操作后，将语句填写在下面的空白处） 1、插入数据

（1）在学生表Student中插入数据：

Sno：9512102 Sname:刘晨 Ssex：男 Sage:20 Sdept：计算机系 insert into Student

values ('9512102','刘晨','男',20,'计算机系')

（2）在课程表Course中插入数据：

Cno：C06 Cname：数据结构 Ccredit：5 Semster：4 insert into Course(cno,cname,ccredit,semster) values ('C06','数据结构',5,4) 或：

insert into Course values ('C06','数据结构',5,4，null)

15

（3）在选课表SC中插入95211班学生选修C04的选课信息。

提示：插入的数据的Sno从Student表中查询而来，插入的Cno为“C04” insert into SC(sno,Cno)

select sno,'c04' from Student where sno like '95211%'

（4）查询高等数学的成绩，包括学号，成绩，并按学号升序排序。将查询的结果输出到一个名为gs_cj的表中。 select sno,grade into gs_cj

from sc join course on sc.cno=course.cno and cname='高等数学' order by sno

（5）将SC表中“C05”课程的选课记录输出至一个新表中，表名为Gs01。 select * into Gs01

from sc where cno='c05'

2、修改数据

（1） 将所有学生的年龄增加1岁。 update student set sage=sage+1

（2）修改“9512101”学生的“C01”课程成绩为85。 update sc set grade=85

where sno='9512101' and cno='c01'

（3）修改“9531102”学生的“C01”课程成绩为70。 update sc set grade=70

where sno='9531102' and cno='c01'

（4）将所有平均分为75分以上的学生的各门课成绩在原来基础上增加1％。 update sc set grade=grade*1.01 where sno in

(select sno from sc group by sno having avg(grade)>=75)

3、删除数据

（1）删除“9531102”学生“C05”课程的成绩记录 delete from sc where sno='9531102' and cno='c05'

（2）删除所有课程为“C05”的选课记录 delete from sc where cno='c05'

16

4、数据的导出

（1）将数据库中的S,P,J,SPJ表导出为一个ACCESS数据库，名为DB_SPJ。

（2）Student表中数据导出到一个文本文件，用,作为数据项的分隔符。

（3）将Course表中数据导出到一个Excel文件中。

5、批量增加数据

（1）利用查询，将其他表中的数据增加到需要的数据表中。 将Gs01表中的数据添加到SC表中。（用Insert 语句实现） insert into sc select * from Gs01

（2）从外部其他数据源导入数据（选做）

? 从文本文件中导入 ? 从ACCESS中导入数据 ? 从EXECEL中导入数据

四、实验小结

五、评阅成绩

实验预习20% 实验过程20% 实验结果30% 实验报告30% 总成绩

17

实验五 数据控制

一、实验目的

1、掌握数据库完整性约束条件的设置 2、掌握触发器的基本使用

3、掌握SQL Server2000的安全性控制方法

二、实验预习

1、关系数据库的完整性约束有哪些？

2、什么是触发器？其作用是什么？SQL Server2000中的触发器主要有哪些？

三、实验内容和要求

1、实体完整性约束

（1）为供应商表SPJ建立完整性约束，约束列为SNO,PNO,JNO。 方法1：在表设计器中建立，请写出建立的步骤：

方法2：写出创建表的同时定义完整性约束的SQL语句。

create table spj (

sno char(6) foreign key references s(sno), pno char(6) foreign key references p(pno), jno char(6) foreign key references j(jno), qty smallint default 100,

18

primary key(sno,pno,jno) )

（2）根据实验2中的内容，输入数据下面的数据，能否成功？为什么？ S1 P1 J1 200

（3）输入下面的数据能否成功，为什么？ S1 NULL J1 200

（4）根据上面实验的结果，请说明实体完整性约束的含义。

2、参照完整性约束

（1）修改Student表中记录，将学号9512101改为9512103，更改是否成功？若不成功请说出原因。

update student set sno='9512103' where sno='9512101'

（2）修改SC表中记录，将学号为9512101的记录均改为学号9512109，是否更改成功？若不成功请说出原因。

update sc set sno='9512109' where sno='9512101'

（3）设置SC表的参照完整性为更新和删除时均为级联。 方法1：在表设计器中设置，请写出设置的步骤：

19

方法2：在创建表时同时设置参照完整性约束，并设置规则为级联。请写出SQL语句。

CREATE TABLE SC( Sno char(10) , Cno char(10), Grade numeric(6,2), PRIMARY KEY (Sno,Cno),

FOREIGN KEY (Cno) REFERENCES Course(Cno) on delete cascade on update cascade,

FOREIGN KEY (Sno) REFERENCES Student(Sno) on delete cascade on update cascade )

（4）修改Student表学号9512101为9512109，观察SC表中相应记录是否更新？

update student set sno='9512109' where sno='9512101' （5）在Course表中删除课程代号为C01的记录，观察SC表中选课C01的记录是否删除？

delete from course where cno='C01'

3、用户自定义完整性 （1）设置Student表的Sno输入长度必须为7个字符的约束。请将CHECK约束子句写在下面：

alter table student add check(sno like '_______') （2）设置student表的Sdept只能为“计算机系”，“数学系”，“信息系”，“物理系”。请将CHECK约束子句写在下面：

20

alter table student add check(sdept in('计算机系','数学系','信息系','物理系'))

4、触发器

（1）定义一个触发器，其基本功能是在SC表中增加或修改一个选课记录时，检查该课程的选课人数是否超过限定（可自行定义一个限定值，根据表中数据的情况而定）。若超过限定值，则拒绝操作。 触发器代码：

CREATE TRIGGER trig1 ON SC FOR INSERT,UPDATE AS

IF(SELECT COUNT(*) FROM SC WHERE CNO=(select CNO from INSERTED))>10 BEGIN

PRINT '选课人数超过上限' ROLLBACK END

（2）定义一个触发器，当删除Student表中数据时，先将删除的数据插入到另一个专门存放已删除数据的表中（实验时，首先定义一个与Student表结构相同的表用来存放删除的数据），然后执行删除操作。 触发器代码：

select top 0 * into s1 from student

create trigger trig2 on student for delete as

insert into s1 select * from deleted

5、用户权限控制

（1）在自己数据库中添加其他用户。

（2）为添加的用户进行授权和权限收回。相互检查是否获得了相应的权限。

sp_adduser 'rj107','rj107'

grant select on student to rj107

21

grant select,update(cname) on course to rj107 revoke select on course from rj107 sp_dropuser 'rj107'

四、实验小结

五、评阅成绩

实验预习20% 实验过程20% 实验结果30% 实验报告30% 总成绩 22

实验六 视图与存储过程

一、实验目的

1、掌握视图的定义及使用

2、掌握存储过程的建立和调用

二、实验预习

1、基本表与视图有什么不同？哪种视图可以更新？

2、视图定义语句格式：

3、什么是存储过程，其作用主要是什么？

三、实验内容和要求

1、视图（将执行的SQL语句写在下面）

（1）建立视图IS_STUDENT,视图中包含信息系全体学生的基本信息。 CREATE VIEW IS_STUDENT AS

SELECT * FROM STUDENT WHERE Sdept='信息系'

（2）建立视图CJ_STUDENT,视图中包含所有成绩不及格的学生的学号，姓名，课程名，成绩。

CREATE VIEW CJ_STUDENT AS

SELECT Student.Sno,Sname,Cname,Grade FROM STUDENT,Course,SC WHERE Grade<60 and Student.Sno=SC.Sno and Course.Cno=SC.Cno

23

（3）建立视图AVG_CJ，视图包括学生的学号以及他们的平均成绩，按成绩降序排列。

CREATE VIEW AVG_CJ(Sno,Gavg) AS

SELECT Sno,AVG(Grade) FROM SC GROUP BY Sno

SELECT * FROM AVG_CJ ORDER BY Gavg DESC

（4）修改视图IS_STUDENT，将年龄均加1。观察基本表Student中相应的数据是否发生变化。

UPDATE IS_STUDENT SET Sage=Sage+1

相应变化

（5）在视图IS_STUDENT中插入新的记录，学号为9531103，姓名为张玉，女，21岁。

INSERT INTO IS_STUDENT

VALUES('9531103','张玉','女',21,'')

（6）根据视图AVG_CJ，查询平均成绩大于60的学生的学号。 SELECT Sno FROM AVG_CJ WHERE Gavg>60

2、存储过程

（1）建立存储过程student_Grade1，功能是查询计算机系学生的成绩，包括学号，姓名，课程名，成绩，按学号升序排序。 存储过程代码：

CREATE PROC STUDENT_GRADE1 AS

SELECT STUDENT.SNO,SNAME,CNAME,GRADE FROM STUDENT,SC,COURSE WHERE STUDENT.SNO=SC.SNO

AND SC.CNO=COURSE.CNO AND SDEPT='计算机系' ORDER BY STUDENT.SNO ASC

执行存储过程代码：EXEC STUDENT_GRADE1

（2）建立存储过程student_Grade2，功能是根据参数提供的系名，查询该系学生的成绩，包括学号，姓名，课程名，成绩，按学号升序排序。

24

存储过程代码：

CREATE PROC STUDENT_GRADE2 @DEPT CHAR(20) AS

SELECT STUDENT.SNO,SNAME,CNAME,GRADE FROM STUDENT,SC,COURSE WHERE STUDENT.SNO=SC.SNO

AND SC.CNO=COURSE.CNO AND SDEPT=@DEPT

ORDER BY STUDENT.SNO ASC

执行存储过程代码：EXEC STUDENT_GRADE2 '信息系'

（3）建立存储过程student_Grade3,功能是根据参数提供的学生的姓名和课程名，查询该学生相应的课程成绩，若存在不为空的成绩，则返回参数值为成绩值，否则返回-1。

存储过程代码：

CREATE PROC STUDENT_GRADE3 @STUDENT_NAME CHAR(7), @COURSE_NAME CHAR(50), @X SMALLINT OUTPUT AS

SELECT @x=grade FROM STUDENT,SC,COURSE WHERE STUDENT.SNO=SC.SNO AND SC.CNO=COURSE.CNO AND SNAME=@STUDENT_NAME AND CNAME=@COURSE_NAME IF(@X IS NOT NULL) RETURN @x ELSE

SET @x=-1 RETURN @x

执行存储过程代码： DECLARE @X SMALLINT

EXEC STUDENT_GRADE3 '李勇','VB',@X OUTPUT PRINT @X 结果输出:87

DECLARE @X SMALLINT

EXEC STUDENT_GRADE3 '李勇','计算机导论',@X OUTPUT PRINT @X 结果输出:-1

25

（4）建立存储过程check_Xk，功能是根据提供的参数学号和课程号，完成选课记录的插入功能。要求如下：

? 首先检查该课程选课人数是否已满（可自己根据表中数据的情况定义一个限

定值），若满，则返回一个0；若不满，继续检查该学生是否已经选满3门课程，若满，则返回-1，否则将选课记录插入到SC表中，并返回1。 存储过程代码：

create procedure check_xk @sno char(10),@cno char(10) as

if (select count(*) from sc where cno=@cno)>=5 return 0 else begin

if (select count(*) from sc where sno=@sno)>=3 return -1 else begin

insert into sc(sno,cno) values(@sno,@cno) return 1 end end

执行存储过程代码： declare @i int

exec @i=check_xk '9531103','C03' print @i

四、实验小结

五、评阅成绩

实验预习20% 实验过程20% 实验结果30% 实验报告30% 总成绩

26

本文来源：https://www.bwwdw.com/article/wwqt.html