数据库实验指导书.docx

1、数据库实验指导书巢湖学院计算机与信息工程学院数据库实验指导书指导者：刘拥二零一二年二月目 录实验一SQL SEVER 2005的系统工具及用户管理、使用交互方式建库、建表.3实验二 TSQL的简单查询、连接查询.15 实验三 子查询及组合.23实验四 数据库的更新、视图的定义及使用. 224实验五 数据导入/导出、数据备份和恢复.26实验六 数据完整性和数据安全性 .33实验七 存储过程和触发器 .35实验八 嵌入式SQL .37实验一 SQL SEVER 2005的系统工具及用户管理、使用交互方式建库、建表一、实验目的：认识SQL SEVER 2000的服务管理器，了解客户端和数据库服务器的

2、连接过程，初步掌握和使用 Enterpriser Manager 以交互方式创建数据库、表、索引和修改表结构，熟练掌握使用Enterpriser Manager 向数据库输入数据、修给数据和删除数据的操作。二、实验内容：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

3、-42在SQL Server 2000中建立数据库：在程序菜单中选择Microsoft SQL Server，如图1-5所示：图1-5再选中企业管理器，如图1-6所示：图1-6点击企业管理器后，出现企业管理器的主界面，如图1-7所示：图1-7点击左边树状控制栏的+/号可以打开和关闭SQL Server组中的本地服务项目，在选中数据库服务项目后，单击鼠标右键，出现数据库的菜单，如图1-8所示：图1-8点击新建数据库，出现新建数据库窗口,如图1-9所示：图1-9输入数据库名称：Test（以Test为例），点击数据文件选项卡，如图1-10所示：图1-10在数据文件中，你可以设定数据文件的名称，所在目

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

5、务：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,2系Course：表1-2字段名类型长度含义Cnovarchar4课程号C

6、namevarchar40课程名Cpnovarchar4先行课Ccreditsmallint4学分SC:表1-3字段名类型长度含义Snovarchar9课程号Cnovarchar4成绩Gradevarcharsmallint根据以上表的结构，输入以下内容：Student:表1-4学号Sno姓名Smane性别Ssex年龄Sage所在系Sdept200215121李勇男20CS200215122刘晨女19CS200215123王敏女18MA200215125张立男19ISCourse:表1-5课程号Cno课程名Cname先行课Cpno学分Credit1数据库542数学23信息系统144操作系统63

7、5数据结构746数据处理27PASCAL语言64SC:表1-6学号Cno课程号Cno成绩Grade2002151211922002151212852002151213882002151222902002151223804.定义数据库Create DB 数据库名;5.定义模式CREATE SCHEMA 模式名 AUTHORIZATION 用户名6.定义基本表CREATE TABLE表名（列名数据类型 列级完整性约束条件，列名数据类型列级完整性约束条件，表级完整性约束条件）；例建立一个“学生”表Student。CREATE TABLE Student( Sno CHAR(9) PRIMARY KE

8、Y,Sname CHAR(20) UNIQUE,Ssex CHAR(2),Sage SMALLINT,Sdept CHAR(20);例建立一个“课程”表Course。CREATE TABLE Course( Cno CHAR(9) PRIMARY KEY,Cname CHAR(40),Cpno CHAR(4),Ccredit SMALLINT,FOREIGN KEY (Cpno) REFERENCES Course(Cno);例建立学生选课表。CREATE TABLE SCSno CHAR(9),Cno CHAR(9),Grade SMALLINT,PRINARY KEY(Sno,Cno),F

9、OREING KEY (Sno) REFERENCES Student(Sno),FOREING KEY (Cno) REFERENCES Course(Cno);实验二 TSQL的简单查询、连接查询一、实验目的：掌握SQL Server Query Analyze 的使用方法，加深对SQL的语句，特别是对查询语句的理解。本次实验了解SQL Server 2000查询分析器的启动，熟悉如何在SQL Server 2000查询分析器中建表、插入记录、查询记录。学会SQL Server 2000的查询分析器中建表、插入记录、查询记录。二、实验内容:1启动数据库服务软件SQL Server 2000

10、的查询分析器：在程序菜单中选择Microsoft SQL Server，如图2-1所示：图2-1再选中查询分析器，如图2-2所示：图2-2点击查询分析器后，出现连接到SQL Server窗口，如图2-3所示：图2-3点击按钮，出现选择服务器窗口，如图2-4所示。图2-4选择本地服务(Local)，点击确定按钮。再点击连接到SQL Server窗口的确定按钮。出现SQL查询分析器主界面，如图2-5所示。图2-5选择查询菜单，点击更改数据库，如图2-6所示。图2-6出现选择数据库窗口，如图2-7所示。图2-7选择在上次实验中建立的数据库Test，点确定按钮。2在查询分析器中建立表：在查询分析器的查

11、询窗口中输入SQL语句，如图2-8所示。图2-8点击按钮，执行该SQL语句，在查询窗口下部出现一个输出窗口，如图2-9所示。图2-9提示命令成功完成，或者报告出错信息。3.查询分析器中向表添加数据：在查询分析器的查询窗口中输入SQL语句，如图2-10所示。图2-10点击按钮，执行该SQL语句，在查询窗口下部出现一个输出窗口，如图2-11所示。图2-111.从表中查询数据：在查询分析器的查询窗口中输入SQL语句，如图2-12所示。图2-12点击按钮，执行该SQL语句，在查询窗口下部出现一个输出窗口，如图2-13所示。图2-13三、实验任务1打开数据库SQL Server 2000的查询分析器，利

12、用实验一所建立的库Student，针对改库中的表Student, Course, Sc 作如下操作：1查询全体学生的学号与姓名2.查询全体学生的姓名、学号、所在系3.查询全体学生的详细记录4.查询全体学生的姓名及其出生年份5.查询全体学生的姓名、出生年份和所在的院系，要求用小写字母表示所有系名。6.查询选修了课程的学生学号7.查询计算机科学系全体学生的名单8.查询所有年龄在20岁以下的学生姓名及其年龄9.查询考试成绩有不及格的学生的学号10查询年龄在2030岁之间的学生的姓名、系别和年龄。11.查询既不是计算机科学系、数学系，也不是信息系的学生的姓名和性别12.查询学号为200215121的学

13、生的详细情况13.查询所有姓刘的学生的姓名、学号和性别14.查询某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。15.查询所有成绩的学生的学号和课程号16.查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列17查询选修课程的学生人数18查询选修了1号课程的学生最高分数19.求各个课程及相应的选课人数20.查询每个学生及其选修课程的情况21查询每一门课的间接选修课22.查询每个学生的学号、姓名、选修的课程名及成绩实验三 子查询及组合一、实验目的：进一步掌握Query Analyzer的使用方法，加深对SQL和嵌套查询语句的理解，熟练掌握数据查询中分组、统计、计算和组

14、合的操作方法。二、实验内容：1启动数据库服务软件SQL Server 2000的查询分析器，在RDMBS中建立一个数据库，进行实验所要求的各种操作，所有的SQL操作均在此建立的新库里进行。2根据以下要求认真进行实验，记录所有的实验用例。数据定义：基本表的定义。数据操作：完成子查询（分组，统计，计算和组合）。三、实验任务：打开数据库SQL Server 2000的查询分析器，根据实验一：所建立的数据库Student,在此库下所建立的三个表Student, SC, Course。完成如下的功能的实验：（1）.带有IN谓词的子查询（2）.带有比较运算符的子查询（3）.带有ANY（SOME）或ALL谓

15、词的子查询（4）.带有EXISTS谓词的子查询实验的步骤如下：1.查询与“刘晨”在同一个系学习的学生2.查询选修了课程名为“信息系统”的学生学号和姓名3.找出每个学生超过他选修课程平均成绩的课程号4.查询其他系中比计算机科学系某一个学生年龄小的学生的姓名和年龄5.查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄6.查询所有选修了1号课程的学生姓名7.查询没有选修1号课程的学生姓名8.查询选修了全部的学生姓名9.查询至少选修了学生200215122选修的全部课程的学生号码10查询计算机科学系的学生及年龄不大于19岁的学生11.查询选修了课程1或者选修了课程2的学生12.查询计算机科学系

16、的学生与年龄不大于19岁的学生的交集13.查询既选修了课程1又选修了课程2的学生。14.查询计算机科学系的学生与年龄不大于19岁的学生的差集实验四 数据库的更新、视图的定义及使用一、实验目的：掌握SQL中的插入、修改和删除语句的用法；掌握视图创建语句和视图创建向导的使用方法，加深对视图作用的理解。二、实验内容：1 启动数据库服务软件SQL Server 2000的查询分析器，用如下语句对表进行操作，详细的语法格式参看课本相应章节：Insert into 表名 插入数据Update 表名 Set 修改数据Delete From 表名删除数据Drop Table 删除表Alter Table 更改

17、表2 用如下语句对视图进行操作，详细的语法格式参看课本相应章节：Create View 建视图Drop View 删除视图三、实验任务1.打开数据库SQL Server 2000的查询分析器，根据实验一：所建立的数据库Student,在此库下所建立的三个表Student, SC, Course。完成如下的功能的实验：1.将一个新学生元组（学号：200215128；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中2.将学生张成民的信息插入到Student表中3.插入一条选课记录（200215128，1）4.对每一个系，求学生的平均年龄，并把结果存入数据库5.将一个学生2

18、00215121的年龄增加1岁6.将计算机科学系全体学生的成绩置零7.删除学号为200215128的学生的记录8.删除所有的学生选课记录9.删除计算机科学系所有学生的选课记录10.建立信息系学生的视图11.建立信息系选修了1号课程的学生的视图12定义一个反映学生出生年份的视图13.将学生的学号及他的平均成绩定义为一个视图14.将Student表中所有女生记录定义为一个视图15用Drop View 删除上述所建立的视图。16在信息系学生的视图中找出年龄小于20岁的学生17.查询选修了1号课程的信息系学生18.将信息系学生视图IS_Student中学号为200215122的学生姓名改为“刘晨” 1

19、9将以上的SQL语言存盘，以备老师检查。实验五 数据的导入导出、数据库备份和恢复一、实验目的：掌握SQL Server数据的导入导出，了解SQL Server的数据备份和恢复机制，掌握SQL Server中数据库备份和恢复的方法。二、实验内容：1.备份数据库：操作步骤：（1）选中所要备份的数据库（2）点击菜单工具栏上“操作”选项（3）在弹出的菜单中选中“所有任务”（4）在“所有任务”一栏中选中“备份数据库”单出如图5-1所示的图5-1（5）在数据库（B）中选中要备份的数据库，更改备份名称，点击添加按钮单出如图5-2所示：图5-2（6）选择备份操作要使用的文件名或备份设备，单击“确定”按钮（7）

20、回到SQL SERVER备份界面，单击“确定”。2.还原数据库：操作步骤：（1）新建一个数据库名和还原数据库名称相同（2）点击菜单工具栏上“操作”选项（3）在弹出的菜单中选中“所有任务”（4）在“所有任务”一栏中选中“还原数据库”单出如图5-3所示的图5-3（5）从还原数据库的界面，选中需要“还原为数据库”名称，选中“从设备”，点击“选择设备”，弹出如图5-4所示：图5-4（6）单击“添加（A）”，弹出如图5-5所示：图5-5（7）输入需要的备份文件，单击“确定”。3.数据的导出：操作步骤：（1）选中所要导出的数据库（2）点击菜单工具栏上“操作”选项（3）在弹出的菜单中选中“所有任务”（4）在

21、“所有任务”一栏中选中“导出数据”，弹出如图5-6所示：图5-6（5）单击“下一步”，弹出如图5-7所示：图5-7（6）选中“数据源（D）、服务器、数据库（B）名称，点击“下一步”，弹出如下图所示（图5-8）：图5-8（7）选择“目的（T）”、服务器、“数据库（D）”（可以新建），单击“下一步”（8）选择数据库中所要导出的数据，单击“确定”4.数据的导入： 操作步骤：（和数据的导出相似，省略）实验六 数据完整性和数据安全性一、实验目的：通过实验使学生加深对数据安全性和完整性的理解，并掌握SQL Server 中有关用户、角色及操作权限的管理方法，学会创建和使用规则、缺省。二、实验内容：1 使用

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

23、ent中建立三张表，分别为Student1，Course1，Sc1；所建表的SQL 命令： (1). CREATE TABLE Student( Sno CHAR(9) PRIMARY KEY, Sname CHAR(20) NOT NULL, Ssex CHAR(2), Sdept CHAR(20);(2). CREATE TABLE Course (Cno CHAR(4) PRIMARY KEY,Cname CHAR(40),Cpno CHAR(4),Ccredit SMALLINT,FOREIGN KEY Cpno REFERENCES Course(Cno);(3). CREATE T

24、ABLE SC (Sno CHAR(9), Cno CHAR(4), Grade SMALLINT,PRIMARY KEY(Sno,Cno),FOREIGN KEY(Sno)REFERENCES Student(Sno)FOREIGN KEY(Cno)REFERENCES Course(Cno);2.把查询Student表的权限授给用户U13.把对Student表和Course表的全部操作权限授予用户U2和U34.把对表SC的查询权限授予所有用户5.把查询Student表和修改学生学号的权限授给用户U46.把对表SC的INSERT权限授予U5用户，并允许将此权限再授序其他用户7.把用户U4修改

25、学生学号的权限收回8.收回所有用户对表SC的查询权限9.把用户U5对SC表的INSERT权限收回10.（1）创建一个角色R1（2）然后使用GRANT语句，使角色R1拥有Student表的SELECT、UPDATE、INSERT权限（3）将这个角色授予王平，张明。使他们具有R1所包含的全部权限实验七 存储过程和触发器一、实验目的：通过实验使学生掌握SQL Server中建立存储过程和触发器的方法。二、实验内容： 启动数据库服务软件SQL Server 2000的查询分析器，在RDMBS中建立一个数据库，进行实验所要求的各种操作，所有的SQL操作均在此建立的新库里进行。1.CREATE PROCE

26、DURE 创建存储过程2.CREATE TRIGGER 创建触发器3.DROP TRIGGER 删除触发器三、实验任务：1.定义存储过程Create procedure owner.存储过程名As子查询2.定义触发器Create trigger before|afteronFor eachRow|Statementwhen例1 定义一个before 行级触发器,为教师表Teacher定义完整性规则教授的工资不得低于4000元,如果低于4000元,自动改为4000元.Create trigger insert_or_update_salBefore insert or update on tea

27、cher For each row As begin If (new.job=教授) and (new.sal4000) Then new.sal:=4000;End if ;End;例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_salAfter

28、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_salAfter update on teacher For each row As begin If(new.salold.sal) then insert into sal_log values(New.Eno,new.sal, current_user,current_timestamp);End if;End;