数据库实验指导书含解答

1、数据库数据库 实实 验验 指指 导导 书书 目目 录录 实验一实验一 SQLSQL SEVERSEVER 20002000 的系统工具及用户管理、使用交互方式建库、的系统工具及用户管理、使用交互方式建库、 建表建表.3 3 实验二实验二 T TSQLSQL 的简单查询、连接查询的简单查询、连接查询.1616 实验三实验三 子查询及组合子查询及组合.2424 实验四实验四 数据库的数据库的更新、更新、视图的定义及使用视图的定义及使用. 2626 实验五实验五 数据导入数据导入/ /导出、数据备份和恢复导出、数据备份和恢复.2828 实验六实验六 数据完整性和数据安全性数据完整性和数据安全性.29

2、29 实验七实验七 存储过程和触发器存储过程和触发器.3131 实验八实验八 嵌入式嵌入式 SQLSQL .3333 实验一实验一SQLSQL SEVERSEVER 20002000 的系统工具及用户管理、使用交的系统工具及用户管理、使用交 互方式建库、建表互方式建库、建表 一、实验目的：一、实验目的： 认识 SQL SEVER 2000 的服务管理器，了解客户端和数据库服务器的连接过 程，初步掌握和使用 Enterpriser Manager 以交互方式创建数据库、表、索引 和修改表结构，熟练掌握使用 Enterpriser Manager 向数据库输入数据、修给 数据和删除数据的操作。 二

3、、实验内容：二、实验内容： 1启动数据库服务软件 SQL Server 2000： 在程序菜单中选择 Microsoft SQL Server，如图 1-1 所示： 图 1-1 再选中服务管理器，如图 1-2 所示： 图 1-2 点击服务管理器后，出现 SQL Server 服务管理器，如图 1-3 所示： 图 1-3 点击“开始/继续”按钮，启动 SQL Server 2000 数据库服务。 启动成功后，在时钟旁边出现一个符号，如图 1-4 所示。 图 1-4 2在 SQL Server 2000 中建立数据库： 在程序菜单中选择 Microsoft SQL Server，如图 1-5 所示

4、： 图 1-5 再选中企业管理器，如图 1-6 所示： 图 1-6 点击企业管理器后，出现企业管理器的主界面，如图 1-7 所示： 图 1-7 点击左边树状控制栏的+/号可以打开和关闭 SQL Server 组中的本地服务 项目，在选中数据库服务项目后，单击鼠标右键，出现数据库的菜单，如图 1-8 所示： 图 1-8 点击新建数据库，出现新建数据库窗口,如图 1-9 所示： 图 1-9 输入数据库名称：Test，点击数据文件选项卡，如图 1-10 所示： 图 1-10 在数据文件中，你可以设定数据文件的名称，所在目录以及文件的大小等 信息。 完成后点击事务日志选项卡，如图 1-11 所示： 图

5、 1-11 事务日志中，你可以设定日志文件的名称，所在目录以及文件的大小等信 息。 接受系统的缺省值，点击确定按钮，完成数据库的新建，如图 1-12 所示。 图 1-12 3.在 Test 数据库上建立表： 点击数据库 Test 前面的号，打开数据库的各种属性，如图 1-13 所示： 图 1-13 选中表的属性，单击鼠标右键，出现表的菜单，如图 1-14 所示： 图 1-14 点击新建表按钮，出现新建表窗口，如图 1-15 所示： 图 1-15 输入列名，数据类型，长度等信息，点击图标，出现选择表名窗口， 如图 1-16 所示： 图 1-16 输入表名，点击确定按钮，将新建的表存盘。 三、实验

6、任务：三、实验任务： 1用数据库 SQL Server 2000 的服务管理器启动数据库服务； 2用数据库 SQL Server 2000 的企业管理器建立一个数据文件名为 Studentdata、日志文件名为 Studentlog、大小 25M 的新数据库，该数据库名为 Student。 3在数据库 Student 上，建立如下所示的表 Student,Course,SC 的结构： Student： 表 1-1 字段名类型长度含义 Snovarchar9 学号 Snamevarchar20 姓名 Ssexvarchar2 性别 Sagesnallint4 年龄 Sdeptvarchar10,

7、2 系 Course： 表 1-2 字段名类型长度含义 Cnovarchar4 课程号 Cnamevarchar40 课程名 Cpnovarchar4 先行课 Ccreditsmallint4 学分 SC: 表 1-3 字段名类型长度含义 Snovarchar9 课程号 Cnovarchar4 成绩 Gradevarcharsmallint 根据以上表的结构，输入以下内容： Student: 表 1-4 学号 Sno 姓名 Smane 性别 Ssex 年龄 Sage 所在系 Sdept 李勇男20CS 刘晨女19CS 王敏女18MA 张立男19IS Course: 表 1-5 课程号Cno课程

8、名Cname先行课Cpno学分Credit 1数据库54 2数学2 3信息系统14 4操作系统63 5数据结构74 6数据处理2 7PASCAL 语言64 SC: 表 1-6 学号Cno课程号Cno成绩Grade 192 285 388 290 380 4.定义数据库 Create DB 数据库名; 5.定义模式 CREATE SCHEMA 模式名 AUTHORIZATION 用户名 6.定义基本表 CREATE TABLE表名（列名数据类型 列级完整性约束条件 ，列名数据类型列级完整性约束条件 ，表级完整性约束条件）； 例例建立一个建立一个“学生学生”表表 Student。 CREATE T

9、ABLE Student ( Sno CHAR(9) PRIMARY KEY, Sname CHAR(20) UNIQUE, Ssex CHAR(2), Sage SMALLINT, Sdept CHAR(20) ); 例例建立一个建立一个“课程课程”表表 Course。 CREATE TABLE Course ( Cno CHAR(9) PRIMARY KEY, Cname CHAR(40), Spno CHAR(4), Ccredit SMALLINT, FOREING KEY Cpno REFERENCES Course(Cno) ); 例例建立学生选课表。建立学生选课表。 CREATE

10、 TABLE SC Sno CHAR(9), Cno CHAR(9), Grade SMALLINT, PRINARY KEY(Sno,Cno), FOREING KEY (Sno) REFERENCES Student(Sno), FOREING KEY (Cno) REFERENCES Course(Cno) ); 实验二实验二 T TSQLSQL 的简单查询、连接查询的简单查询、连接查询 一、实验目的：一、实验目的： 掌握SQL Server Query Analyze 的使用方法，加深对SQL的语句，特别是对 查询语句的理解。 本次实验了解 SQL Server 2000 查询分析器的

11、启动，熟悉如何在 SQL Server 2000 查询分析器中建表、插入记录、查询记录。学会 SQL Server 2000 的查询分析器中建表、插入记录、查询记录。 二、实验内容二、实验内容: : 1启动数据库服务软件 SQL Server 2000 的查询分析器： 在程序菜单中选择 Microsoft SQL Server，如图 2-1 所示： 图 2-1 再选中查询分析器，如图 2-2 所示： 图 2-2 点击查询分析器后，出现连接到 SQL Server 窗口，如图 2-3 所示： 图 2-3 点击按钮，出现选择服务器窗口，如图 2-4 所示。 图 2-4 选择本地服务(Local)，

12、点击确定按钮。再点击连接到 SQL Server 窗口的确 定按钮。出现 SQL 查询分析器主界面，如图 2-5 所示。 图 2-5 选择查询菜单，点击更改数据库，如图 2-6 所示。 图 2-6 出现选择数据库窗口，如图 2-7 所示。 图 2-7 选择在上次实验中建立的数据库 Test，点确定按钮。 2在查询分析器中建立表： 在查询分析器的查询窗口中输入 SQL 语句，如图 2-8 所示。 图 2-8 点击按钮，执行该 SQL 语句，在查询窗口下部出现一个输出窗口， 如图 2-9 所示。 图 2-9 提示命令成功完成，或者报告出错信息。 3.查询分析器中向表添加数据： 在查询分析器的查询窗

13、口中输入 SQL 语句，如图 2-10 所示。 图 2-10 点击按钮，执行该 SQL 语句，在查询窗口下部出现一个输出窗口， 如图 2-11 所示。 图 2-11 1.从表中查询数据： 在查询分析器的查询窗口中输入 SQL 语句，如图 2-12 所示。 图 2-12 点击按钮，执行该 SQL 语句，在查询窗口下部出现一个输出窗口， 如图 2-13 所示。 图 2-13 三、实验任务三、实验任务 1打开数据库 SQL Server 2000 的查询分析器，利用实验一所建立的库 Student，针对改库中的表 Student, Course, Sc 作如下操作： 1查询全体学生的学号与姓名 答：

14、答：selectselect sno,snamesno,sname fromfrom studentstudent 2.查询全体学生的姓名、学号、所在系 答：答：selectselect sname,sno,sdeptsname,sno,sdept fromfrom studentstudent 3.查询全体学生的详细记录 答：答： selectselect * * fromfrom studentstudent 4.查询全体学生的姓名及其出生年份 答：答：selectselect sname,2010-sagesname,2010-sage fromfrom studentstudent 5

15、.查询全体学生的姓名、出生年份和所在的院系，要求用小写字母表示所 有系名。 答：答：selectselect sname,yearsname,year ofof birth:,2010-sage,lower(sdept)birth:,2010-sage,lower(sdept) fromfrom studentstudent 6.查询选修了课程的学生学号 答：答：selectselect distinctdistinct snosno fromfrom scsc 7.查询计算机科学系全体学生的名单 答：答：selectselect snamesname fromfrom studentstud

16、ent wherewhere sdept=sdept= cscs 8.查询所有年龄在 20 岁以下的学生姓名及其年龄 答：答：selectselect snamesname，sagesage fromfrom studentstudent wherewhere sage20sage20 9.查询考试成绩有不及格的学生的学号 答：答：selectselect distinctdistinct snosno fromfrom scsc wherewhere grade60grade=(selectgrade=(select avg(grade)avg(grade) fromfrom scsc y

17、y wherewhere y.sno=x.sno)y.sno=x.sno) 4.查询其他系中比计算机科学系某一个学生年龄小的学生的姓名和年龄 答：答：selectselect sname,sagesname,sage fromfrom studentstudent wherewhere sageany(selectsageany(select sagesage fromfrom studentstudent wherewhere sdept=cs)sdept=cs) andand sdeptcssdeptcs 5.查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄 答答: : sele

18、ctselect sname,sagesname,sage fromfrom studentstudent wherewhere sageall(selectsageall(select sagesage fromfrom studentstudent wherewhere sdept=cs)sdept=cs) andand sdeptcssdeptcs 6.查询所有选修了 1 号课程的学生姓名 答：答： selectselect snamesname fromfrom studentstudent wherewhere existsexists (select(select * * from

19、from scsc wherewhere sno=student.snosno=student.sno andand cno=1)cno=1) 7.查询没有选修 1 号课程的学生姓名 答：答： selectselect snamesname fromfrom studentstudent wherewhere notnot existsexists (select(select * * fromfrom scsc wherewhere sno=student.snosno=student.sno andand cno=1)cno=1) 8.查询选修了全部的学生姓名 答：答：selectsele

20、ct snamesname fromfrom studentstudent wherewhere notnot exists(selectexists(select * * fromfrom coursecourse wherewhere notnot exists(selectexists(select * * fromfrom scsc wherewhere sno=student.snosno=student.sno andand cno=o)cno=o) 9.查询至少选修了学生选修的全部课程的学生号码 答：答：selectselect distinctdistinct snosno f

21、romfrom scsc scxscx wherewhere notnot exists(selectexists(select * * fromfrom scsc scyscy wherewhere scy.sno=andscy.sno=and notnot existsexists (select(select * * fromfrom scsc sczscz wherewhere scz.sno=scx.snoscz.sno=scx.sno andand o=o)o=o) 10 查询计算机科学系的学生及年龄不大于 19 岁的学生 答：答：selectselect * * fromfrom

22、 studentstudent wherewhere sdept=cssdept=cs unionunion selectselect * * fromfrom studentstudent wherewhere sage=19sage=19 11.查询选修了课程 1 或者选修了课程 2 的学生 答：答：selectselect snosno fromfrom scsc wherewhere cno=1cno=1 unionunion selectselect snosno fromfrom scsc wherewhere cno=2cno=2 12.查询计算机科学系的学生与年龄不大于 19

23、岁的学生的交集 答：答：selectselect * * fromfrom studentstudent wherewhere sdept=csandsdept=csand sage=19sage19sage19 实验四实验四 数据库的数据库的更新、更新、视图的定义及使用视图的定义及使用 一、实验目的：一、实验目的： 掌握 SQL 中的插入、修改和删除语句的用法；掌握视图创建语句和视图创 建向导的使用方法，加深对视图作用的理解。 二、实验内容：二、实验内容： 1启动数据库服务软件 SQL Server 2000 的查询分析器，用如下语句 对表进行操作，详细的语法格式参看课本相应章节： Inse

24、rt into 表名 插入数据 Update 表名 Set 修改数据 Delete From 表名删除数据 Drop Table 删除表 Alter Table 更改表 2用如下语句对视图进行操作，详细的语法格式参看课本相应章节： Create View 建视图 Drop View 删除视图 三、实验任务三、实验任务 1.打开数据库 SQL Server 2000 的查询分析器，根据实验一：所建立的数 据库 Student,在此库下所建立的三个表 Student, SC, Course。完成如下的功 能的实验： 1.将一个新学生元组（学号：；姓名：陈冬；性别：男；所在系：IS；年 龄：18 岁

25、）插入到 Student 表中 答：答：insertinsert intointo studentstudent (sno,sname,ssex,sdept,sage)(sno,sname,ssex,sdept,sage) values(,values(,陈冬陈冬,男男,is,18),is,18) 2.将学生张成民的信息插入到 Student 表中 答：答：insertinsert intointo studentstudent values(,values(,张成民张成民,男男,18,cs),18,cs) 3.插入一条选课记录（ ， 1 ） 答：答：insertinsert intointo

26、 scsc (sno,cno)(sno,cno) values(,1)values(,1) 4.对每一个系，求学生的平均年龄，并把结果存入数据库 答：答：createcreate tabletable dept_agedept_age (sdept(sdept char(15),char(15), avg_ageavg_age smallint)smallint) insertinsert intointo dept_age(sdept,avg_age)dept_age(sdept,avg_age) selectselect sdept,avg(sage)sdept,avg(sage) fro

27、mfrom studentstudent groupgroup byby sdeptsdept 5.将一个学生的年龄增加 1 岁 答：答：updateupdate studentstudent setset sage=sage+1sage=sage+1 6.将计算机科学系全体学生的成绩置零 答：答：updateupdate scsc setset grade=0grade=0 wherecs=(wherecs=( selectselect sdeptsdept fromfrom studentstudent wherewhere student.sno=sc.sno)student.sno=s

28、c.sno) 7.删除学号为的学生的记录 答答; ; deletedelete fromfrom studentstudent wherewhere sno=sno= 8.删除所有的学生选课记录 答：答：deletedelete fromfrom scsc 9.删除计算机科学系所有学生的选课记 答：答：deletedelete fromfrom scsc wherecs=(selectwherecs=(select sdeptsdept fromfrom studentstudent wherewhere student.sno=sc.sno)student.sno=sc.sno) 10.建立

29、信息系学生的视图 答：答：createcreate viewview IS_StudentIS_Student asas selectselect sno,sname,sagesno,sname,sage fromfrom studentstudent wherewhere sdept=ISsdept=IS 11.建立信息系选修了 1 号课程的学生的视图 答：答：createcreate viewview IS_S1(sno,sname,grade)IS_S1(sno,sname,grade) asas selectselect student.sno,sname,gradestudent.s

30、no,sname,grade fromfrom student,scstudent,sc wherewhere sdept=ISandsdept=ISand student.sno=sc.snostudent.sno=sc.sno andand o=o=1 12 定义一个反映学生出生年份的视图 答：答：createcreate viewview BT_S(sno,sname,sbirth)BT_S(sno,sname,sbirth) asas selectselect sno,sname,2004-sagesno,sname,2004-sage fromfrom studentstudent

31、13.将学生的学号及他的平均成绩定义为一个视图 答：答： CreatCreat viewview S_G(sno,Gavg)S_G(sno,Gavg) AsAs SelectSelect sno,avg(grade)sno,avg(grade) FromFrom scsc GroupGroup byby snosno 14.将 Student 表中所有女生记录定义为一个视图 答：答：createcreate viewview F_Student(F_sno,sname,ssex,sage,sdept)F_Student(F_sno,sname,ssex,sage,sdept) asas sel

32、ectselect * * fromfrom studentstudent wherewhere ssex=ssex=女女 15用 Drop View 删除上述所建立的视图。 答：答：drop view BT_S; drop view IS_S1 cascade 16在信息系学生的视图中找出年龄小于 20 岁的学生 答：答：selectselect sno,sagesno,sage fromfrom IS_studentIS_student wherewhere sage20sage“操作”-“所有任务”-备份数据库-选择要备份的数据 库-备份到添加按钮-文件名-单击确定。 2.2.还原数据库

33、：还原数据库： 操作步骤：操作步骤： 选中数据库-操作-所有任务-选择从备份-选择设备-添加-文件名- 还原数据库保存为 Student-显示数据库备份 student-单击确定。 三、实验任务：三、实验任务： 实验六实验六 数据完整性和数据安全性数据完整性和数据安全性 一、实验目的：一、实验目的： 通过实验使学生加深对数据安全性和完整性的理解，并掌握 SQL Server 中有关用户、角色及操作权限的管理方法，学会创建和使用规则、缺省。 二、实验内容：二、实验内容： 1使用 SQL 对数据进行安全性控制，包括：授权和权利回收，语法格 式如下： GRANT To |PUBLIC PUBLIC

34、指数据库的所有用户。 2使用 SQL 对数据进行完整性控制（三类完整性，HECK 短语， CONSTRAINT 子句，触发器）。用实验证实，当操作违反了完整性约束条件时， 系统是如何处理的，法格式如下： REVOKE |ALL PRIVILEGES ON FROM |PUBLIC ON 用于被回收特权的对象，ALL PRIVILEGES 指回收所有特权，PUBLIC 指 数据库的所有用户。 三、实验任务：三、实验任务： 1为了完成数据库数据的完整性，利用 SQL Server 2000 的查询分析 器，在实验一所建立的数据库 Student 中建立三张表，分别为 Student1，Course

35、1，Sc1； 所建表的 SQL 命令： (1). CREATE TABLE Student1 ( Sno CHAR(9) PRIMARY KEY, Sname CHAR(20) NOT NULL, Ssex CHAR(2), Sdept CHAR(20) ); (2). CREATE TABLE Course1 (Cno CHAR(4) PRIMARY KEY, Cname CHAR(40), Cpno CHAR(4), Ccredit SMALLINT, FOREIGN KEY Cpno REFERENCES Course1(Cno) ); (3). CREATE TABLE SC1 (Sn

36、o CHAR(9), Cno CHAR(4), Grade SMALLINT, PRIMARY KEY(Sno,Cno), FOREIGN KEY(Sno)REFERENCES Student1(Sno) FOREIGN KEY(Cno)REFERENCES Course1(Cno) ; 2.把查询 Student 表的权限授给用户 U1 Exec sp_addlogin Alex, Exec sp_adduser Alex,u1 Grant select on student to u1 3.把对 Student 表和 Course 表的全部操作权限授予用户 U2 和 U3 Exec sp_

37、addlogin Shally, Exec sp_adduser Shally,u2 Exec sp_addlogin Bill, Exec sp_adduser Bill,u3 Grant all privileges on student1 To u2 Grant all privileges on student1 To u3 Grant all privileges on course1 To u3 Grant all privileges on course1 To u2 4.把对表 SC 的查询权限授予所有用户 Grant all privileges on sc To publi

38、c 5.把查询 Student 表和修改学生学号的权限授给用户 U4 Exec sp_addlogin Stronger,bb Exec sp_adduserStronger,u4 Grant update(sno),select on student to u4 6.把对表 SC 的 INSERT 权限授予 U5 用户，并允许将此权限再授序其他用户 Exec sp_addlogin God,bb Exec sp_adduserGod,u5 Grant insert on sc To u5 With grant option 7.把用户 U4 修改学生学号的权限收回 Revoke update

39、 (sno) on student from u4 8.收回所有用户对表 SC 的查询权限 Revoke select on sc from public 9.把用户 U5 对 SC 表的 INSERT 权限收回 Revoke insert on sc from u5 10. （1）创建一个角色 R1 Exec sp_addrole r1 （2）然后使用 GRANT 语句，使角色 R1 拥有 Student 表的 SELECT、UPDATE、INSERT 权限 Grant select,update,insert on student to r1 （3）将这个角色授予王平，张明。使他们具有 R

40、1 所包含的全部权限 Exec sp_addloginvb,yy Exec sp_adduservb,王平王平 Exec sp_addlogintb,yy Exec sp_addusertb,张明张明 Exec sp_addrolemember R1，王平，王平 Exec sp_addrolemember R1，张明，张明 实验七实验七 存储过程和触发器存储过程和触发器 一、实验目的：一、实验目的： 通过实验使学生掌握 SQL Server 中建立存储过程和触发器的方法。 二、实验内容：二、实验内容： 启动数据库服务软件 SQL Server 2000 的查询分析器，在 RDMBS 中建立一

41、个数据库，进行实验所要求的各种操作，所有的 SQL 操作均在此建立的新库里 进行。 1.CREATE PROCEDURE 创建存储过程 2.CREATE TRIGGER 创建触发器 3.DROP TRIGGER 删除触发器 三、实验任务：三、实验任务： 1.定义存储过程 Create procedure owner.存储过程名 As 子查询 2.定义触发器 Create trigger before|afteron For eachRow|Statement when 例 1 定义一个 before 行级触发器,为教师表 Teacher 定义完整性规则教授 的工资不得低于 4000 元,如果低

42、于 4000 元,自动改为 4000 元. CreateCreate triggertrigger insert_update_salinsert_update_sal OnOn teacherteacher AfterAfter insert,updateinsert,update AsAs declaredeclare jobjob char(8),salchar(8),sal numeric(7,2)numeric(7,2) SelectSelect job=job,sal=saljob=job,sal=sal fromfrom insertedinserted IfIf (job=(j

43、ob=教授教授 andand sal4000)sal4000) SetSet sal=4000sal=4000 UpdateUpdate teacherteacher setset sal=salsal=sal WhereWhere job=jobjob=job 例 2定义 After 行级触发器,当教师表 Teacher 的工资发生变化后就自动 在工资变化表 sal_log 中增加一条相应记录. Create Table sal_log ( Eno NUMERIC(4) REFERENCES teacher (Eno), Sal NUMERIC(7,2), Username char(10), DateTime TIMESTAMP ); Create trigger insert_sal After insert on teacher For each row As begin Insert into sal_log values ( New.Eno,new.sal, current_user,current_timestamp); End; Create trigger update_sal After update on teac