数据库原理及应用实验教案

数据库原理及应用课程实验教案课程编号：020235总学时：54+18 周学时：2（单周）、4（双周）适用年级专业（学科类）：07计本开课时间：2009—2010 学年第一学期使用教材：数据库系统概论实验一：数据库定义实验一、实验目的掌握在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、利用企业管理器更新数据在学生课程数据库的学生、课程和选课３个表中各输入下列表中的记录。

Course表SC表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, SsexFROM Student（2）可以用‚*‛来选取数据表的全部列例如：查询Student表中所有学生的基本情况。

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

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

SELECT Sno 学号, Sname 姓名, Ssex 性别, Year(GetDate()) - Sage 出生年份FROM Student（4）使用WHERE子句，可以选择满足条件的部分记录例如：查询成绩在85～90分之间的学生情况。

SELECT Sno, Cno, GradeFROM SCWHERE Grade >= 85 AND Grade <= 90（5）使用DISTINCT关键字，可以消除重复记录例如：查询有成绩的学生的学号。

SELECT DISTINCT SnoFROM SC（6）使用IN关键字，选择不连续条件的记录例如：查询学生成绩为80或85的学生的学号。

SELECT *FROM SCWHERE Grade IN (80, 85)（7）使用谓词LIKE和通配符‚%‛或‚_‛，实现模糊查询例如：查询姓‚张‛的学生的基本情况。

SELECT *FROM StudentWHERE Sname LIKE '张%'注意：‚%‛代表0个或多个字符，‚_‛代表一个字符。

有的书上说，一个汉字占两个字符，但这里一个汉字只占一个字符位置，这与系统的设置有关。

（8）使用ORDER BY子句，对查询结果进行排序例如：查询所有学生的2号课的成绩，并按成绩由高向低排序。

SELECT *FROM SCWHERE Cno =’ 2’ORDER BY Grade DESC（9）使用TOP关键字，选择查询结果的前几条记录例如：查询2号课成绩最高的学生记录。

SELECT TOP 1 *FROM SCWHERE Cno = '2'ORDER BY Grade DESC注意：如果2号课成绩存在并列最高，则使用下面的SQL语句。

SELECT TOP 1 WITH TIES *FROM SCWHERE Cno = '2'ORDER BY Grade DESC（10）用WHERE子句指定连接条件例如：查询所有有2号课程成绩的学生的学号、姓名和成绩。

SELECT Student.Sno, Sname, GradeFROM Student, SCWHERE Student.Sno = SC.Sno AND Cno = '2'(11)将查询结果存储到表中例如：将总分在200分以上的学生的情况（学号、姓名、性别、总成绩）存储到数据表GradeList中，假设数据表GradeList不存在。

SELECT A.Sno, Sname, Ssex, SUM(Grade) AS sumGradeINTO GradeListFROM Student A, SCWHERE A.Sno = SC.SnoGROUP BY A.Sno, Sname, SsexHAVING (SUM(SC.Grade) >= 200)该语句执行成功后，在企业管理器中可以看到已经创建的数据表GradeList，并将查询结果是存储在该表中了。

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

DECLARE @Grade INT --定义变量SELECT @Grade = Grade --给变量赋值FROM SCWHERE 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权限。