数据库系统概论实验教案.doc

1、1数据库系统概论实验教案类别 专业 开课学期 项目数 总学时本科 计算机科学与技术 6 5 12开课周次 序号 题目 学时6 1 交互式 SQL 28，10 2 安全性和完整性 412 3 存储过程 214 4 查询优化 216 5 ODBC 方式访问数据库 2实验一：交互式 SQL实验学时：2 学时实验目的：1. 掌握使用 Transact-SQL 语言创建数据库和表及对表进行插入、修改和删除数据的操作。2. 实验目的：掌握使用 Transact-SQL 语言实现对表的各种查询操作实验内容：1. 使用 Transact-SQL 语言分别创建学生数据库、学籍表、学生成绩表、班级信息表、年级课程

2、信息表和系统用户表。并建立表间的参照关系。Student_lnfo 学籍信息表Result_ lnfo 学生成绩信息表2Class_lnfo 班级信息表Gradecourse_Info 年级课程设置表Coure_Info 课程基本信息表User_lnfo 系统用户表2. 查看和修改表的结构。3. 使用 Transact-SQL 语言向各个表插入至少 4 条记录4. 修改表中某个字段的字段值：将学生成绩信息表中所有学生的“数据结构”课程的成绩都加 2 分。5. SQL 命令完成以下操作，并将命令保存在 sql.txt 中。（1） 单表查询1） 查询所有学生信息2） 查询所有女同学的姓名及出生年份

3、33） 查询年龄不在 2023 岁（包括 20 岁和 23 岁）之间的学生的姓名和年龄4） 查询所有不姓王的学生姓名5） 查询年龄小于 25 岁的男同学的姓名和年龄6） 查询选课但没有参加考试（即有课程记录但没有考试成绩）的学生的学号和相应的课程名称（2） 连接查询1） 查询每个学生及其所学课程的情况2） 查询选修“数据结构”课程且成绩在 78 分以上的所有学生3） 查询每个学生的学号、姓名、选修的课程名及成绩（3） 统计函数 GROUP BY、ORDER BY 子句的使用1） 求学生的平均年龄2） 求选修“数据结构”课程的总成绩3） 求各学生情况按“数据结构”课程的成绩由高到低排列命令：1.

4、创建数据库和表create database xsglon primary(name=xsgl_dat,filename=f:xsglxsgl.mdf,size=10,maxsize=20,filegrowth=5%),filegroup data1(name=xsglgroup_dat,filename=f:xsglxsglgroup.ndf,size=2,maxsize=100,filegrowth=1)Log on(name=xsgl_log1,filename=f:xsglxsgl_log1.ldf,size=10MB,maxsize=25MB,filegrowth=5%)create

5、 table student_info(student_ID char(4) primary key,student_Name char(10),student_Sex char(2),born_Date datetime,class_NO char(10),tele_Number char(10) NULL,ru_Date datetime null,address varchar(50) null,comment varchar(200)create table class_info(class_no char(10) primary key,grade char(10),director

6、 char(10) null,classroom_no char(10) null)create table course_info(course_no char(4) primary key,course_name char(10) null,4course_type char(10) null,course_des char(50) null )create table gradecourse_info(grade char(10) ,course_name char(10) not null,course_no char(4) not null,credit smallint not n

7、ull,primary key (grade,course_no)create table result_info(Exam_No char(10),student_ID char(4) not null,Class_No char(10) not null,Course_No char(10) null,Result float null)create table user_info(user_id char(10) primary key,user_pwd char(10) not null,user_des char(10) null)2. 查看和修改表的结构sp_help studen

8、t_infosp_columns student_info将 student_info 的 tele_number 属性列的类型改为 char(12)：alter table student_infoalter column tele_number char(12)3. 使用 Transact-SQL 语言向各个表插入至少 4 条记录insert into course_info values(jsj0004,数据结构,必修, 专业必修)insert into course_info values(jsj0005,操作系统,必修, 专业必修)insert into course_info va

9、lues(jsj0006,计算机网络,必修, 专业必修)4. 修改表中某个字段的字段值：将学生成绩信息表中所有学生的“数据结构”课程的成绩都加 2 分。update result_infoset result=result+2where course_no= jsj00055.查询命令：5（1） 单表查询1） 查询所有学生信息Select * from student_info2） 查询所有女同学的姓名及出生年份select student_name,born_datefrom student_infowhere student_sex=女3） 查询年龄不在 2023 岁（包括 20 岁和 2

10、3 岁）之间的学生的姓名和年龄select student_name,2012-year(born_date)from student_infowhere 2012-YEAR(born_date) not between 20 and 234） 查询所有不姓王的学生姓名select student_namefrom student_infowhere student_name not like 王 %5） 查询年龄小于 25 岁的男同学的姓名和年龄select student_name,2012-year(born_date)from student_infowhere 2012-year(b

11、orn_date)783） 查询每个学生的学号、姓名、选修的课程名及成绩select student_info.student_id,student_info.student_name,course_info.course_name,result_info.resultfrom student_info,course_info,result_infowhere student_info.student_id=result_info.student_id andresult_info.course_no=course_info.course_no6（3） 统计函数 GROUP BY、ORDER

12、BY 子句的使用1） 求学生的平均年龄select avg(2012-year(born_date)from student_info2） 求选修“数据结构”课程的总成绩select sum(result)from result_info,course_infowhere result_info.course_no=course_info.course_noand course_info.course_name=数据结构3） 求各学生情况按“数据结构”课程的成绩由高到低排列select student_id,resultfrom result_info,course_infowhere res

13、ult_info.course_no=course_info.course_noand course_info.course_name=数据结构order by result desc实验二 安全性和完整性实验学时：4 学时实验目的：1.掌握数据库用户的建立、授权和权限回收的方法；2.掌握 SQL SERVER 2005 中实体完整性、参照完整性及用户自定义完整性约束定义。实验内容：一、数据库安全性 1. 授权。在 SQL Sever 中建立多个用户，给他们赋予不同的权限，然后查看是否真正拥有被授予的权限了。 1 ） 建立数据库登陆用户 U1、U2、U3 、U4 、U5，设置为对 xsgl 数

14、据库具有访问权。服务器角色设置为 securityadminstrators ，serveradminstrators，setupadminstrators，processadminstrators。 2 ） 在 SYSTEM （即 DBA）与五个数据库用户之间进行授权。 例 1-1 把查询 Student_info 表的权限授给用户 U1。 因为当前用户即为 SYSTEM（即 DBA ，所以直接写授权语句即可。 GRANT SELECT ON Student_info TO U1; 例 1-2 把对 Student_info 表和 Course_info 表的全部操作权限授予用户 U2 和

15、U3。 GRANT ALL PRIVILEGES ON Student_infoTO U2,U3; GRANT ALL PRIVILEGES ON Course_info 7TO U2,U3; 例 1-3 把对表 result_info 的查询权限授予所有用户。 GRANT SELECT ON TABLE result_info TO PUBLIC; 例 1-4 把查询 Student_info 表和修改学生学号的权限授给用户 U4。 GRANT UPDATE (Student_id) , SELECT ON Student_info TO U4; 例 1-5 把对表 result_info

16、的 INSERT 权限授予 U4，并允许 U4 将此权限再授予其他用户。GRANT INSERT ON result_info TO U4 WITH GRANT OPTION; 例 1-6 重新连接查询分析器，按数据库登陆用户 u4 登陆，将对表 result_info 的 INSERT 权限继续授予 U5。 GRANT INSERT ON result_info TO U5 3） 接着查看不同的数据库用户是否真正拥有被授予的权限了。 例如按登陆用户 u2 连接查询分析器：对 student_info 表进行 select 操作：select * from student_info / 成功执

17、行命令对 gradecourse_info 表进行 select 操作：select * from gradecourse_info /拒绝了对对象 gradecourse_info（数据库 xsgl，所有者 dbo）的 SELECT 权限。2. 回收权限。将【例 1 】授予的权限部分收回，检查回收后，该用户是否真正丧失了对数据的相应权限。 1 ） 回收权限。 例 2-1 收回用户 U4 修改学生学号的权限。当前用户为 SYSTEM ，直接执行下列语句：REVOKE UPDATE (student_id) ON Student_info FROM U4; 例 2-2 收回所有用户对表 resu

18、lt_info 的查询权限。 REVOKE SELECT ON result_info FROM PUBLIC; 3.创建角色1）首先创建一个角色 r1sp_addrole r12）使用 grant 语句，使角色 r1 拥有 student_info 表的查询权限8grant select on student_info to r13）使用 grant 语句，使角色 r1 增加对 student_info 表的删除权限grant delete on student_info to r14）将 r1 角色授予 u1，u2sp_addrolemember r1,u1sp_addrolemember

19、 r1,u25）收回角色 r1 对 student_info 表的 select 权限revoke select on student_info from r1二、完整性约束定义在 xsgl 数据库中操作:1. 创建参照完整性约束在 result_info 表上添加参照完整性约束：student_id 字段参照 student_info 表的主键，class_no 字段参照 class_info 表的主键，course_no 字段参照 course_info 表的主键。操作成功后通过企业管理器的表设计查看参照完整性约束。alter table result_infoadd constraint

20、 fk_student_id foreign key (student_id) references student_info(student_id),constraint fk_class_no foreign key (class_no) references class_info(class_no),constraint fk_course_no foreign key (course_no) references course_info(course_no)2.创建用户自定义完整性约束在学生信息表 student_info 中添加用户自定义约束，其中输入性别字段 student_sex

21、 值时，只能接受“ 男” 或者“ 女”，并且为 tele_number 字段创建检查约束，限制只能输入类似01080798654 之类的数据，而不能随意输入其他数据。alter table student_infoadd constraint chk_sex check(student_sex in (男,女),Constraint chk_phonenum check(tele_number like 0100-90-90-90-90-90-90-90-9)3.删除约束删除学生信息表中的约束 chk_phonenum：ALTER TABLE studentDROP CONSTRAINT ch

22、k_phonenum;三、 触发器触发器可以看成是一类特殊的存储过程，在满足某个特定条件时自动触发执行，是提高数据库服务器性能的有力工具。 触发器分为三类，更新触发器、插入触发器和删除触发器。9能够定义触发器的用户有： 1 ） 表的所有者； 2 ） 系统管理员； 3 ） 拥有创建触发器的权限，且拥有对操作对象的相应的操作权限的用户。给 result_info 表创建触发器，检测 result 字段的输入值，当大于 100 或者小于 0 时打印提示信息，并将 result 字段置空。create trigger insert_update_resulton result_infofor inse

23、rt,updateas declare 新成绩 numeric,新考试编号 char(10)beginselect 新考试编号=exam_no,新成绩= result from insertedif 新成绩100 or 新成绩=60 and result=70 and result =80 and result =90 and result =90 then Aelse end; update result_info set 等级分数=xgrade where student_id=csno and course_no =ccno;fetch mysor into csno,ccno,cgra

24、de; end /alter table result_info drop column result; close mysor;end(3) 执行存储过程Execute djz;12(4) sp_rename result_info.等级分数,grade,column;实验四： 查询优化（2 学时）实验学时：2 学时实验目的：了解数据查询优化方法和查询计划的概念;学会分析查询的代价,并通过建立索引或修改 SQL 语句来降低查询代价。实验内容：（一）本实验中，要求：表 Student_info 共有 30 条记录，表 Course_info 共有 20 条记录，表 result_info 共有

25、 100 条记录；insert into student_info(student_id,student_name,student_sex,born_date,class_no) values(0904,张 4,男,1992-09-09,0902)insert into student_info(student_id,student_name,student_sex,born_date,class_no) values(0905,张 5,男,1993-09-09,0902)insert into student_info(student_id,student_name,student_sex,

26、born_date,class_no) values(0906,张 6,男,1991-09-09,0902)insert into student_info(student_id,student_name,student_sex,born_date,class_no) values(0907,张 7,男,1994-09-09,0902)insert into student_info(student_id,student_name,student_sex,born_date,class_no) values(0908,张 8,男,1993-09-09,0902)insert into stud

27、ent_info(student_id,student_name,student_sex,born_date,class_no) values(0909,张 9,男,1993-09-09,0901)insert into student_info(student_id,student_name,student_sex,born_date,class_no) values(0910,张 10,男,1992-09-09,0902)insert into student_info(student_id,student_name,student_sex,born_date,class_no) valu

28、es(0911,张 11,男,1993-09-09,0902)insert into student_info(student_id,student_name,student_sex,born_date,class_no) values(0912,张 12,男,1991-09-09,0902)insert into student_info(student_id,student_name,student_sex,born_date,class_no) values(0913,张 13,男,1994-09-09,0902)insert into student_info(student_id,s

29、tudent_name,student_sex,born_date,class_no) values(0914,张 14,男,1993-09-09,0902)insert into student_info(student_id,student_name,student_sex,born_date,class_no) values(0915,张 15,男,1993-09-09,0901)insert into student_info(student_id,student_name,student_sex,born_date,class_no) values(0916,张 16,男,1992-

30、09-09,0902)insert into student_info(student_id,student_name,student_sex,born_date,class_no) values(0917,张 17,男,1993-09-09,0902)insert into student_info(student_id,student_name,student_sex,born_date,class_no) values(0918,张 18,男,1991-09-09,0902)insert into student_info(student_id,student_name,student_

31、sex,born_date,class_no) values(0919,13张 19,男,1994-09-09,0902)insert into student_info(student_id,student_name,student_sex,born_date,class_no) values(0920,张 20,男,1993-09-09,0902)insert into student_info(student_id,student_name,student_sex,born_date,class_no) values(0921,张 21,男,1993-09-09,0901)insert

32、into student_info(student_id,student_name,student_sex,born_date,class_no) values(0922,张 22,男,1992-09-09,0902)insert into student_info(student_id,student_name,student_sex,born_date,class_no) values(0923,张 23,男,1993-09-09,0902)insert into student_info(student_id,student_name,student_sex,born_date,clas

33、s_no) values(0924,张 24,男,1991-09-09,0902)insert into student_info(student_id,student_name,student_sex,born_date,class_no) values(0925,张 25,男,1994-09-09,0902)insert into student_info(student_id,student_name,student_sex,born_date,class_no) values(0926,张 26,男,1993-09-09,0902)insert into student_info(st

34、udent_id,student_name,student_sex,born_date,class_no) values(0927,张 27,男,1993-09-09,0901)insert into course_info values(0006,离散数学,主修,null)insert into course_info values(0007,高等数学 2,主修,null)insert into course_info values(0008,大学英语 1,主修,null)insert into course_info values(0009,大学英语 2,主修,null)insert in

35、to course_info values(0010,大学英语 3,主修,null)insert into course_info values(0011,大学英语 4,主修,null)insert into course_info values(0012,体育 1,主修,null)insert into course_info values(0013,体育 2,主修,null)insert into course_info values(0014,体育 3,主修,null)insert into course_info values(0015,大学语文,主修,null)insert into

36、 course_info values(0016,动态网站,选修,null)insert into course_info values(0017,c 语言,主修,null)insert into course_info values(0018,c+实验,限修,null)insert into course_info values(0019,计算机基础,选修,null)insert into course_info values(0020,马哲,主修,null)insert into course_info values(0021,java 语言,主修,null)insert into cou

37、rse_info values(0022,c+,限修,null)insert into course_info values(0023,计算机 2,选修,null)insert into course_info values(0024,马哲 2,主修,null)insert into course_info values(0025,java 实验,主修,null)insert into result_info values(0904,0904,0902,0018, null)insert into result_info values(0905,0905,0902,0019,null)inse

38、rt into result_info values(0906,0906,0902,0020,null)insert into result_info values(0907,0907,0902,0021,null)insert into result_info values(0908,0908,0902,0018,null)14insert into result_info values(0909,0909,0901,0019,null)insert into result_info values(0910,0910,0902,0020,null)insert into result_inf

39、o values(0911,0911,0902,0021,null)insert into result_info values(0912,0912,0902,0018,null)insert into result_info values(0913,0913,0902,0019,null)insert into result_info values(0914,0914,0902,0020,null)insert into result_info values(0915,0915,0901,0021,null)insert into result_info values(0916,0916,0

40、902,0018, null)insert into result_info values(0917,0917,0902,0019,null)insert into result_info values(0918,0918,0902,0020,null)insert into result_info values(0919,0919,0902,0021,null)insert into result_info values(0920,0920,0902,0018,null)insert into result_info values(0921,0921,0901,0019,null)inser

41、t into result_info values(0922,0922,0902,0020,null)insert into result_info values(0923,0923,0902,0021,null)insert into result_info values(0924,0924,0902,0018,null)insert into result_info values(0925,0925,0902,0019,null)insert into result_info values(0926,0926,0902,0020,null)insert into result_info v

42、alues(0927,0927,0901,0021,null)insert into result_info values(0904,0904,0902,0008,null)insert into result_info values(0905,0905, 0902,0009,null)insert into result_info values(0906,0906, 0902,0010,null)insert into result_info values(0907,0907,0902,0011,null)insert into result_info values(0908,0908,09

43、02,0008,null)insert into result_info values(0909,0909,0901,0009,null)insert into result_info values(0910,0910,0902,0010,null)insert into result_info values(0911,0911,0902,0011,null)insert into result_info values(0912,0912,0902,0008,null)insert into result_info values(0913,0913,0902,0009,null)insert

44、into result_info values(0914,0914,0902,0010,null)insert into result_info values(0915,0915,0901,0011,null)insert into result_info values(0916,0916,0902,0008,null)insert into result_info values(0917,0917,0902,0009,null)insert into result_info values(0918,0918,0902,0010,null)insert into result_info val

45、ues(0919,0919,0902,0011,null)insert into result_info values(0920,0920,0902,0008,null)insert into result_info values(0921,0921,0901,0009,null)insert into result_info values(0922,0922,0902,0010,null)insert into result_info values(0923,0923,0902,0011,null)insert into result_info values(0924,0924,0902,0

46、008,null)insert into result_info values(0925,0925,0902,0009,null)insert into result_info values(0926,0926,0902,0010,null)15insert into result_info values(0927,0927,0901,0011,null)insert into result_info values(0904,0904,0902,0012,null)insert into result_info values(0905,0905,0902,0013,null)insert into result_info values(0906,0906,0902,0014,null)insert into result_info values(0907,0907,0902,0015,null)insert into result_info values(0908,0908,0902,0012,null)insert into result_info values(0909,0909,0901,0013,null)insert into result_info values(0910