《数据库原理及应用》实验指导

实验一：数据定义/数据操纵语言

一、实验目的

熟悉SQL Server上机环境以及SQL Server客户端的配置；熟练掌握和使用DDL语言，建立、修改和删除数据库表、主键、外键约束关系和索引；熟练掌握和使用DML语言，对数据进行增加、修改和删除操作。

二、学时数 2学时

三、实验内容

SQL数据定义语句， SQL数据操纵语句。 四、实验要求

1． 熟悉SQL Server上机环境；

2． 建立数据库表，修改数据库表结构，建立、删除索引。

教学管理数据库各表的数据结构和数据如下： CS：计算机系 MA：数学系 IS：信息系 学生情况表 Student

SNO(C,5) 学号 96001 96002 96003 96004 97001 97002 97003 97004 96005 SNAME(VARCHAR,10) 姓名 马小燕 黎明 刘东明 赵志勇 马蓉 李成功 黎明 李丽 司马志明

SDEPT(C,2) 系 CS CS MA IS MA CS IS CS CS

SCLASS(C,2) 班级 01 01 01 02 02 01 03 02 02

SSEX(C,2) 性别 女 男 男 男 女 男 女 女 男

SAGE(N,2) 年龄 21 18 18 20 19 20 19 19 18 主关键字：SNO 非空字段：SNAME，SDEPT，SCLASS

课程名称表 Course 教师授课表 Teach

CNO(C,3) CNAME(C,16) CTIME(N,3)

课程号 课程名称 学时数 001 002 003 004 005 006 007 008 数学分析 普通物理 微机原理 数据结构 操作系统 数据库原理 编译原理 程序设计

144 144 80 72 80 80 60 40

TNAME(C,8) TSEX(C,2) CNO(C,3) TDATE 教师姓名 王成刚 李正科 严敏 赵高 李正科 刘玉兰 王成刚 马悦

性别 男 男 女 男 男 女 男 女

课程号 004 003 001 004 003 006 004 008 授课日期 1999.9.5 1999.9.5 1999.9.5 1999.9.5 TDEPT(C,2) 系 CS CS MA IS 2000.2.23 MA 2000.2.23 CS 2000.2.23 IS 2000.9.6 CS 主关键字：CNO 候选关键字：CNAME 主关键字：TNAME，CNO，TDEPT 参照关系：Course(CNO)

成绩表 Score

CNO(C,3) SCORE(N,5,2) SNO(C,5) CNO(C,3) SCORE(N,5,2) SNO(C,5) CNO(C,3) SCORE(N,5,2) SNO(C,5) 学号

课程号 分数

学号

课程号 分数

学号

课程号 分数

96001 001 77.5 96001 003 96001 004 86 96001 005 82 96002 001 88 96002 003 92.5 96002 006 90 96005 004 92 96005 005 90 96005 006 96005 007 76 96003 001 69 97001 001 96 97001 008 95 96004 001 87 96003 003 91 97002 003 91 97002 004 97002 006 92 97004 005 90 97004 006 85 主关键字：SNO，CNO 参照关系：Student(SNO)，Course(CNO)

（1） SQL数据定义语句：

1-1： (建立数据库表) 建立教学数据库的四个数据库表，其中Student表中不包含SSEX(C,2) 字段，Sname 字段为Sname(VARCHAR,8)且可为空。 1-2： (修改数据库表) 在Student表中增加SSEX(C,2) 字段。

1-3： (修改数据库表) 将Student表中把Sname 字段修改为Sname(VARCHAR,10)且为非空。

1-4： (建立索引) 为Score表按学号升序和课程号降序分别建立索引,索引名分别为SC_NDXSNO和SC_NDXCNO。为Score表按分数降序建立索引，索引名为GRADE_NDX。 1-5： (删除索引) 删除索引SC_NDX_SNO。

（2）SQL数据操纵语句：

2-1： (插入数据) 按前面各表中的数据分别插入到教学数据库的四个数据库表中。 2-2： (多行插入) 将表Student中在计算机系(‘CS’)的学生数据插入到表S1中。 2-3： (修改数据) 将S1表中所有学生的年龄加2。

2-4： (修改数据) 将Course表中‘程序设计’课时数修改成与‘数据结构’的课时数相同。 2-5： (插入数据) 向Score表中插入数据（‘98001’, ‘001’, 95），根据返回信息解释其原因。

2-6： (插入数据) 向Score表中插入数据（‘97001’, ‘010’, 80），根据返回信息解释其原因。

2-7： (删除数据) 删除Score表中学号为‘96001’的成绩信息，根据返回信息解释其原因。

五、实验报告 1． 实验目的

熟悉SQL Server上机环境以及SQL Server客户端的配置；熟练掌握和使用DDL语言，建立、修改和删除数据库表、主键、外键约束关系和索引；熟练掌握和使用DML语言，对数据进行增加、修改和删除操作。 2． 实验内容

① 将实验要求用SQL语句表示 ② 执行SQL语句

③ 记录执行结果（对于数据操纵语句，记录执行前的数据和执行后的数据） 3． 实验总结

① 记录不成功的操作，然后分析原因，再说明怎么做 ② 实验中的问题和提高

③ 收获与体会

实验二：数据查询语言

一、实验目的

学习使用SQL语言，加深对SELECT语言的认识和理解，提高SELECT语言的实际应用能力。

二、学时数 4学时

三、实验内容

SELECT数据查询语句 四、实验要求

对数据库表进行各种查询操作：

1： (选择表中的若干列) 求全体学生的学号、姓名、性别和年龄。 2： (不选择重复行) 求选修了课程的学生学号。

3： (使用表达式) 求全体学生的学号、姓名和出生年份。

4： (使用列的别名) 求学生的学号和出生年份，显示时使用别名“学号”和“出生年份”。

5： (比较大小条件) 求年龄大于19岁的学生的姓名和年龄。

6： (确定范围条件) 求年龄在20岁与22岁(含20岁和22岁)之间的学生的学号和年龄。

7： (匹配查询) 求姓名长度至少是三个汉字且倒数第三个汉字必须是“马”的学生。 8： (匹配查询) 求选修课程001或003，成绩在80至90之间，学号为96xxx的学生的学号、课程号和成绩。

9： (涉及空值查询) 求缺少学习成绩的学生的学号和课程号。 10：(集函数) 求选修了课程的学生人数。

11：(分组查询) 求各门课程的平均成绩与总成绩。

12：(分组查询) 输入以下查询语句并执行，观察出现的其结果并分析其原因。

SELECT SNAME，SDEPT，COUNT（*）FROM STUDENT WHERE SDEPT=’CS’ GROUP BY SDEPT; 13：(连接查询) 求选修了“003”号课程的学生的姓名、课程名和成绩。 14：(自身连接查询) 求年龄大于“李丽”的所有学生的姓名、系和年龄。 15：（子查询ALL） 求比数学系中全体学生年龄大的学生的姓名和系。 16：（相关子查询） 查询所有未选课程的学生姓名和所在系。 17：(集合查询) 列出所有教师和学生的姓名和性别。 18：（排序）求选修了课程名为 ’数据结构’ 的学生的姓名和成绩，查询结果按成绩降序排序。

19：(复合条件查询) 求选修了课程名为 ’数据结构’ 的学生的学号和姓名。 五、实验报告 1． 实验目的

学习使用SQL语言，加深对SELECT语言的认识和理解，提高SELECT语言的实际应用能力。 2． 实验内容

① 将实验需求用SQL语句表示

② 执行SQL语句 ③ 记录执行结果 3． 实验总结

① 记录不成功的操作，然后分析原因，再说明怎么做 ② 实验中的问题和提高 ③ 收获与体会

实验三：视图、授权控制与事务处理

一、实验目的

通过实验进一步理解对视图操作的条件、数据库的权限管理和事务处理功能。 二、学时数 2学时

三、实验内容：

1． SQL视图的定义与操纵； 2． SQL数据控制语句； 3． SQL事务处理。 四、实验要求

1． SQL视图的定义与操纵：

1-1： (建立视图) 建立计算机系的学生的视图STUDENT_CS。

1-2： (建立视图) 建立由学号和他的平均成绩两个字段的视图STUDENT_GR。 1-3： (视图查询) 利用视图STUDENT_CS，求年龄大于19岁的学生的全部信息。 1-4： (视图查询) 利用视图STUDENT_GR，求平均成绩为88分以上的学生的学号和平均成绩。

1-5： (视图更新) 利用视图STUDENT_CS，增加学生( ‘96006’，‘张然’，‘CS’，‘02’，‘男’，19 )。

1-6： (视图更新) 利用视图STUDENT_GR，将平均成绩增加2分。观察其运行结果并分析原因。

1-7： (视图更新) 删除视图STUDENT_CS中学号为 ‘96006’ 的学生的全部数据。 1-8：(删除视图) 删除视图STUDENT_CS和STUDENT_GR。 2． SQL数据控制语句： 新建四个用户“studenta”、“studentb”、“studentc”和“studentd”。

2-1： (授权) 给用户“studenta”和“studentb”授予在表Student上的SELECT权限,并使这两个用户具有给其他用户授予相同权限的权限。

(授权验证) 分别以用户“studenta”和“studentb”身份查询你所授权的表中的

内容，验证上述授权。

2-2： (授权) 给用户“studentc”授予Teach表上的所有权限。

(授权验证) 分别以用户“studenta”、“studentb”和“studentc”身份查询你所授

权的表中的内容，验证上述授权。

2-3： (授权) 给所有用户授予Score表上的SELECT权限。

(授权验证) 分别以用户“studenta”、“studentb”、“studentc”和“studentd”身

份查询你所授权的表中的内容，验证上述授权。

2-5： (收回授权) 收回上面例子中的所有授予的权限。

(授权验证) 分别以用户“studenta”、“studentb”、“studentc”和“studentd”身

份查询各表中的内容，验证是否收回上述授权。

3． SQL事务处理：

3-1： (事务回退) 将课程名称表中的 ‘程序设计’ 课程学时数修改为80、 ‘编译原理’ 课程学时数修改为70学时，查询全部课程的总学时数后,取消所有修改(ROLLBACK)。再次查询全部课程的总学时数。注意比较分析两次查询的结果。

3-2： (事务提交) 将课程名称表中的 ‘程序设计’ 课程学时数修改为80、 ‘编译原理’ 课程学时数修改为70学时，查询全部课程的总学时数后,确认所有修改(COMMIT)。再次查询全部课程的总学时数。注意比较分析两次查询的结果。

五、实验报告 1． 实验目的

通过实验进一步理解对视图操作的条件、数据库的权限管理和事务处理功能。 2． 实验内容

① 将实验需求用SQL语句表示 ② 执行SQL语句 ③ 记录执行结果 3． 实验总结

① 记录不成功的操作，然后分析原因，再说明怎么做 ② 对重点实验结果进行分析 ③ 实验中的问题和提高 ④ 收获与体会