数据库应用实验教案

课程

实验教案

课程编号：

总学时：

适用年级专业（学科类）：

开课时间

使用教材：

授课教师姓名：

实验二：数据库定义实验

一、实验目的

掌握在SQL Server Query Analyzer中利用CREATE、DROP、ALTER等SQL语句创建和删除数据库，创建、删除、更新基本表。

二、实验内容

1、利用查询分析器创建学生课程数据库（student），保存在学生学号命名的文件夹下。

注意观察数据库文件的后缀名。

2、利用查询分析器在student数据库中，创建基本表学生（Student）、课程（Course）、选课（SC）,各表属性具体描述如下：

学生（学号，姓名，年龄，性别，所在系）；/*学号是主码，姓名取值惟一*/

课程（课程号，课程名，先行课，学分）；/*课程号是主码*/

选课（学号，课程号，成绩）。/*学号和课程号为主码*/

Student(Sno char(10), Sname char(10), Ssex char(2), Sage int, Sdept Varchar(10) )

Course(Cno char(4), Cname char(20), Cpno char(4), Ccredit int)

SC(Sno char(10), Cno char(4), Grade int)

要求：

为属性选择合适的数据类型。

定义必要的列级约束。

建库、建表、建立表间联系（表间完整性约束）。

注意：

（1）将建立表SQL语句保存为以表名命名的sql脚本文件。

（2）Allow Null和Default Value在定义表中的约束作用是什么？

3、在查询分析器窗体下键入如下命令DROP TABLE Student及DROP TABLE SC命令,运行后观察数据库student中的表有什么变化？再重新运行student.sql及sc.sql，再观察数据库student中的表有什么变化？分析DROP TABLE Student及DROP TABLE SC命令的功能。

4、在查询分析器窗体下完成下列操作命令：

（1）在Student表中增加一个新的属性“专业”—Smajor char(20);

（2）将Student表中的Sdept字段的长度更改为20；

（3）删除Student表中刚增加的属性“专业”；

（4）删除Student表、SC表和Course表。

三、实验学时

2学时

四、实验设备与环境

Windows 2003平台+ SQL Server 2005系统

实验三：数据更新实验

一、实验目的

了解企业管理器中数据表数据的输入、修改和删除操作，熟练掌握使用SQL语句向数据库输入数据、修改数据和删除数据的方法。

二、实验内容

学生课程数据库的三个基本表为学生（学号，姓名，年龄，性别，所在系）；课程（课程号，课程名，先行课，学分）；选课（学号，课程号，成绩）。

1、利用企业管理器更新数据

在学生课程数据库的学生、课程和选课３个表中各输入下列表中的记录。

2、使用查询分析器更新数据，写出相应的SQL语句，并查看、分析执行的结果。（1）使用SQL语句删除Student表、SC表和Course表中的所有数据；

（2）使用SQL语句将上面三个表中的数据添加到Student表、SC表和Course表中；（3）将Student表中每个学生的年龄都增加1岁；

（4）将SC表中每个学生的每门课程成绩都乘以0.8。

（5）思考：删除表和删除表中的数据使用的SQL语言有什么不同？

三、实验学时

2学时

四、实验设备与环境

Windows 2003平台+ SQL Server 2005系统

实验四：数据查询实验

一、实验目的

了解在企业管理器或查询分析器中执行数据查询的方法；掌握SQL Server Query Analyzer中简单查询、连接查询、嵌套查询操作方法。

二、实验内容

1、使用企业管理器进行查询（验证性，了解部分）

（1）查询数据表的全部数据

在企业管理器中，选择需要查询数据的表，用鼠标右键单击该表，从弹出的快捷菜单中选择“打开表”命令，这时将显示该表的全部数据。

（2）使用SELECT语句查询数据表的数据

在企业管理器中，选择需要查询数据的表，用鼠标右键单击该表，从弹出的快捷菜单中选择“打开表”命令，在打开返回表内容的子窗口中，单击工具栏按钮“显示/隐藏SQL窗格”，把子窗口分为上下两部分，上面部分能输入不同的SQL命令。执行时单击工具栏按钮“运行”即可。

（3）学习和使用QBE查询

在企业管理器中，选择需要查询数据的表，用鼠标右键单击该表，从弹出的快捷菜单中选择“打开表”命令，在打开返回表内容的子窗口中，单击工具栏按钮“显示/隐藏条件窗格”，把子窗口分为上下两部分，上面部分能选择QBE查询条件，执行时单击工具栏按钮“运行”即可。若单击工具栏按钮“显示/隐藏SQL窗格”，则在SQL窗格中会显示自动生成的对应SQL 语句。

2、使用查询分析器进行查询（验证性）

（1）查询Student表中所有学生的学号、姓名和性别。

SELECT Sno, Sname, Ssex

FROM Student

（2）可以用“*”来选取数据表的全部列

例如：查询Student表中所有学生的基本情况。

SELECT *

FROM Student

（3）在查询结果中增加计算列，还可修改数据列的显示名称。

例如：查询Student表中所有学生的学号、姓名、性别和出生年份。

SELECT Sno 学号, Sname 姓名, Ssex 性别, Year(GetDate()) - Sage 出生年份

FROM Student

（4）使用WHERE子句，可以选择满足条件的部分记录

例如：查询成绩在85～90分之间的学生情况。

SELECT Sno, Cno, Grade

FROM SC

WHERE Grade >= 85 AND Grade <= 90

（5）使用DISTINCT关键字，可以消除重复记录

例如：查询有成绩的学生的学号。

SELECT DISTINCT Sno

FROM SC

（6）使用IN关键字，选择不连续条件的记录

例如：查询学生成绩为80或85的学生的学号。

SELECT *

FROM SC

WHERE Grade IN (80, 85)

（7）使用谓词LIKE和通配符“%”或“_”，实现模糊查询

例如：查询姓“张”的学生的基本情况。

SELECT *

FROM Student

WHERE Sname LIKE '张%'

注意：“%”代表0个或多个字符，“_”代表一个字符。有的书上说，一个汉字占两个字符，但这里一个汉字只占一个字符位置，这与系统的设置有关。

（8）使用ORDER BY子句，对查询结果进行排序

例如：查询所有学生的2号课的成绩，并按成绩由高向低排序。

SELECT *

FROM SC

WHERE Cno =’ 2’

ORDER BY Grade DESC

（9）使用TOP关键字，选择查询结果的前几条记录

例如：查询2号课成绩最高的学生记录。

SELECT TOP 1 *

FROM SC

WHERE Cno = '2'

ORDER BY Grade DESC

注意：如果2号课成绩存在并列最高，则使用下面的SQL语句。

SELECT TOP 1 WITH TIES *

FROM SC

WHERE Cno = '2'

ORDER BY Grade DESC

（10）用WHERE子句指定连接条件

例如：查询所有有2号课程成绩的学生的学号、姓名和成绩。

SELECT Student.Sno, Sname, Grade

FROM Student, SC

WHERE Student.Sno = SC.Sno AND Cno = '2'

(11)将查询结果存储到表中

例如：将总分在200分以上的学生的情况（学号、姓名、性别、总成绩）存储到数据表GradeList中，假设数据表GradeList不存在。

SELECT A.Sno, Sname, Ssex, SUM(Grade) AS sumGrade

INTO GradeList

FROM Student A, SC

WHERE A.Sno = SC.Sno

GROUP BY A.Sno, Sname, Ssex

HAVING (SUM(SC.Grade) >= 200)

该语句执行成功后，在企业管理器中可以看到已经创建的数据表GradeList，并将查询结果是存储在该表中了。

（12）将查询结果保存到变量中

例如：查询学号为200215121的学生1号课的成绩，将其保存到变量Grade中。

DECLARE @Grade INT --定义变量

SELECT @Grade = Grade --给变量赋值

FROM SC

WHERE Sno = '200215121'

PRINT @Grade --显示变量的值

注意：要将查询结果保存到变量中，只能将查询结果集中第一条记录的值赋给变量。

3、分析设计部分

在学生选课库中实现下列数据查询操作，并写出相应的SQL脚本。

(1)求计算机系学生的学号和姓名

(2)求选修3号课程的学生学号和成绩,结果按成绩降序排列,如成绩同按学号升序排列

(3)求选修课程2成绩在80-90之间的学生学号和成绩,并将成绩乘以0.8输出

(4)求数学或计算机系姓’张’的学生的信息

(5)求缺少了成绩的学生的学号和课程号

(6)查询各门课程及相应的选课人数。

(7)查询总成绩在200分以上的学生的学号、总成绩和平均成绩

(8)在FROM子句中用INNER JOIN连接符指定连接条件查询所有有2号课程成绩的学生的学号、姓名和成绩。

(9)查询学生中年龄相同的学生情况（使用自连接查询）。

(10)查询所有学生的总成绩（包括没有成绩的学生）、学号和姓名（外部连接查询）。

(11)查询某课程成绩在90分以上的学生的学号和姓名（使用谓词IN连接子查询）。

(12)查询有课程成绩的学生的学号和姓名(使用谓词EXISTS连接子查询)。

三、实验学时

2学时

四、实验设备与环境

Windows 2003平台+ SQL Server 2005系统

实验五：用户权限管理实验

一、实验目的

掌握SQL Server中有关用户、角色及操作权限的设置方法。

二、实验内容

1. 用超级用户登录学生数据库student。

2. 建立两个新用户:用户名stu1,密码stu1（登录名login1）；用户名stu2,密码stu2（登录名login2）和用户名stu3,密码stu3（登录名login3）。

3. 授予stu1对SC具有select和insert权限,授予stu1对student具有select,update,insert 和delete权限, 授予stu1对Course具有select,update,insert和delete权限。

4．授予stu2对SC具有select权限,授予stu2对student具有select权限, 授予stu2对Course 具有select权限；授予stu2对Course具有更新属性Ccredit的权限。

5．测试授权

◆以用户Stu1登录学生数据库：

（1）查询SC、student和Course表中的所有数据；

（2）分别向SC、student和Course表中插入一组合法数据；

（3）分别对SC、student和Course表更改一个元组；

（4）分别对SC、student和Course表删除一个元组。

观察运行结果并分析原因。

◆以用户Stu2登录学生数据库：

（1）分别向SC、student和Course表中插入一组合法数据；

（2）更新Course表中课程号为’1’的课程的名称为’人工智能’；

（3）更新Course表中课程号为’1’的课程的学分为3。

观察运行结果并分析原因。

◆以用户Stu1登录学生数据库：

（1）收回stu1对SC表的select权限，对student表的update和insert权限，对Course 表的delete权限；

（2）查询SC、student和Course表中的数据，观察运行结果；

（3）分别向SC、student和Course表中插入一组合法数据，观察运行结果；

（4）分别对SC、student和Course表更改一个元组，观察运行结果；

（5）分别对SC、student和Course表删除一个元组，观察运行结果。思考Course是否具有delete权限执行效果有何不同？

6．创建数据库角色Myrole，设置访问student表的select和insert权限，并添加用户成员stu3。以stu3登录查看验证。

7．使用SQL命令完成：

删除登录名login1、login2和login3；删除用户名stu1、stu2和stu3；删除角色Myrole。

三、实验学时

2学时

四、实验设备与环境

Windows 2003平台+ SQL Server 2005系统

实验六：数据完整性实验

一、实验目的：

掌握使用SQL中的PRIMARY KEY、CHECK、FOREIGN KEY……REFERENCES、NOT NULL、UNIQUE等关键字是现实体完整性、参照完整性及用户定义完整性约束定义。

二、实验步骤：

（一）验证分析部分

1、利用SQL查询分析器用PRIMARY KEY子句保证实体完整性。

在查询分析器窗体下键入如下命令：

CREATE TABLE Student(

sno CHAR(5) NOT NULL UNIQUE,

sname CHAR(8),

ssex CHAR(1),

sage INT,

sdept CHAR(20)，

CONSTRAINT PK_Student PRIMARY KEY(sno));

UPDATE Student SET sno=’‘ WHERE sdept=’CS’;

UPDATE Student SET sno=’200215121 ‘WHERE sname=’王敏’;

运行并观察结果。

2、利用SQL查询分析器用FOREIGN KEY……REFERENCES子句保证实体完整性。

在查询分析器窗体下键入如下命令：

(1)先删除原来SC表中关于Sno 的外键，再将SC的Sno外键修改为：

alter TABLE SC add CONSTRAINT FK_Sno FOREIGN KEY (sno)

REFERENCES Student(sno)

ON update CASCADE;

然后执行以下语句

Update Student SET sno=’200215128 ‘ WHERE sno=’200215121’;

(2) 先删除原来SC表中关于Sno 的外键，再将SC的Sno外键修改为：

alter TABLE SC add CONSTRAINT FK_Sno FOREIGN KEY (sno)

REFERENCES Student(sno)

ON delete CASCADE;

然后执行以下语句

delete from student WHERE sno=’200215122’;

运行并观察结果。

3、利用SQL查询分析器用短语NOT NULL、UNIQUE、CHECK保证用户定义完整性。

CREATE TABLE Student(

sno CHAR(5) ,

sname CHAR(8) CONSTRAINT U1 UNIQUE,

ssex CHAR(1),

sage INT CONSTRAINT U2 CHECK FOR sage<=28,

sdept CHAR(20)，

CONSTRAINT PK_Student PRIMARY KEY(sno))

执行以下SQL语句

Insert into values(‘2002101’,’li’,’女’,89,’DS’)

运行并观察结果。思考：如果要求sdept输入内容只能为字母该如何约束？

（二）设计分析部分

在Student数据库中，完成以下完整性约束设置。

1、设置Student表中Ssex的取值只能是‘男’或‘女’；

2、设置Student表中Sdept的取值默认为‘CS’；

3、删除操作测试

（1）设置Course表和SC表之间的关系，实现cascade delete related records。

（2）删除Course 表中记录，查看SC表中数据的变化（SC表中Cno有与Course表中Cno对应的记录删除和无对应记录的删除都要测试），有什么结果，为什么？

4. 修改操作测试

（1）在SC表中，将课程号为3的改为33，都有什么结果？为什么？

（2）将Course表和sc表的课程号为3的都改为33，请用完整性约束实现？

5. 插入数据测试

（1）在STUDENT表中插入一条新纪录：

95006 李三男21 IS

（2）在SC表中插入一条新纪录：

95008 3 80

插入执行后都有什么结果？为什么？如何完成上述操作？

三、实验学时

2学时

四、实验设备与环境

Windows 2003平台+ SQL Server 2005系统

实验七：触发器设计实验

一、实验目的：

掌握触发器编写方法，能运用设计的触发器实现对数据表中数据完整性约束的控制。二、实验内容：

要求为Course表编写一个名为CourseTrigger插入触发器，当插入记录到Course表时，该触发器检查新插入记录的userid字段，如果插入这个字段的值不存在于Teacher表中，则从Course表中删除这个新插入记录。

Teacher(userid char(20), name char(20),department char(20), age)

Course(courseid int, userid char(20), name char(20), grade int)

说明：

1．对表Teacher和Course之间不允许建立外键约束；

2．本实验属于设计型实验，要求学生设计一个符合实验内容要求的触发器。

三、实验学时

2学时

四、实验设备与环境

Windows 2003平台+ SQL Server 2005系统

课外补充：

在Student、Course和SC表中创建以下触发器

(1)创建一个after触发器，在Student表中删除某学生的记录时，删除其相应的选课记录(不允许使用外键级联删除约束)。

(2)创建一个触发器，不允许删除Course表中的任何数据。

实验八、概念数据模型设计实验

一、实验目的：

熟悉PowerDesigner环境，掌握创建概念数据模型的方法。

二、实验内容：

1．设计出版社出版图书管理系统的概念数据模型，其设计CDM图结构如下图所示。完

成下图所示的CDM图，并对图中的实体和标识符进行完善和补充。

2．将实验2中创建的CDM模型转换成SQL SERVER对应的逻辑模型。

3．实验报告中写出完善的E-R图和对应的逻辑模型图。

三、实验学时

2学时

四、实验设备与环境

Windows 2003平台+ PowerDesigner + SQL Server 2005系统

实验九存储过程设计

一、实验目的

掌握使用SQL Server2005平台设计存储过程的方法。

二、实验内容

设计要求：

1、使用STUDENT数据库中的学生表、课程表、选课表，创建一个带参数的存储过程—userproc。该存储过程的作用是：当任意输入一个学生的姓名时，将从三个表中返回该学生的学号、选修的课程名称和课程成绩。

2、将姓名为XXX的学生所有课程的成绩减少10分（其中学生姓名为存储过程的参数）。

三、实验学时

2学时

四、实验设备与环境

Windows 2003平台+ SQL Server 2005系统

存储过程设计辅助实验

一实验目的

通过实验掌握SQL SERVER存储过程的基本设计方法。

二、内容和方法

1. 创建存储过程步骤

1) 写SQL语句

例如：查询所有学生的记录

select * from student

2) 测试SQL语句

执行这些SQL语句。确认符合要求。

3) 若得到所需结果，则创建过程

如果发现符合要求，则按照存储过程的语法，定义该存储过程。

create procedure dem1

as

select * from student

4) 执行过程

执行存储过程，验证正确性。

EXEC dem1

检查是否已经存在存储过程

例如：检查是否已经存在spp，如果存在则删除它。

if exists SELECT name FROM sysobjects WHERE name = 'spp' AND type = 'P' drop procedure spp

2. 无参数传递存储过程

执行时，不需要向存储过程传递参数。

先阅读下列程序，并且运行之，如果结果正确，将其写入存储过程SPP1中。

Select * from sc

WHILE (SELECT AVG(grade) FROM sc) < 90

BEGIN

UPDATE sc

SET grade = grade*1.1;

SELECT MAX(grade) FROM sc;

IF (SELECT MAX(grade) FROM sc) > 98

BREAK

ELSE

CONTINUE

END

请注意观察结果。

3. 带参数传递存储过程

阅读并试运行下列程序：

create procedure dem1 (@sno1 char(20))

as

select avg(grade),@sno1 from sc where sno=@sno1 group by sno

执行存储过程：

exec dem1 95001 ---记录结果，查询95002的平均成绩。