数据库实验华中科技大学.docx

数据库实验华中科技大学.docx

《数据库实验华中科技大学.docx》由会员分享，可在线阅读，更多相关《数据库实验华中科技大学.docx（20页珍藏版）》请在冰点文库上搜索。

数据库实验华中科技大学

数据库实验报告

Xxxx

2012-6-1

一、实验目的

掌握SQLServer2000的工具使用

掌握DDL的使用方法

掌握DML的使用方法

掌握SELECT命令使用方法

掌握DCL的使用方法

掌握数据库的备份和恢复

二、实验原理

1．

SQLServer2000的工具

1）服务管理器（ServerManager）

在进行任何数据库操作前，都必须启动服务器，ServerManager可以方便启动、停止、暂停本地或远程服务器。

图6是打开的ServerManager窗口。

适当配置窗口中的选项，点击“启动”按钮即可启动SQLServer服务器。

SQLServer2000安装以后，每次开机时，Windows都会自动启动服务管理器。

在任务栏上有一个图标表示服务管理器的状态。

如图7所示。

2）SQLServer2000查询分析器（QueryAnalyzer）

图11查询分析器

查询分析器是一个重要工具，实验中的所有SQL语言命令均需在查询分析器中输入、编辑运行。

从Program－>SQLServer->QueryAnalyzer可以打开查询分析器，如图11所示。

它是一个多文档程序，在其上可以同时打开多个查询程序（窗口），在查询分析器的工具栏中的绿色4按钮为执行当前窗口中SQL语句按钮。

点击它即可逐行执行。

2.DDL使用方法

1）数据库创建

在查询分析器中执行下列语句即可在默认的设备上创建新的数据库sch。

CREATEDATABASEdatabase_name

2）基本表的建立

创建基本表的命令为：

CREATETABLEtable_name，在该命令中定义主码和外码时，可以使用列约束（ColumnConstraint）或表约束（TableConstraint）子句。

创建基本表时，应先选择包含表的数据库。

3）视图的建立

视图是组成数据库体系结构——三级模式两级映像结构中的外模式的基本单元，SQL-Server的视图定义命令为：

CREATEVIEWview-nameASSELECTstatement

视图是用于定义终端用户数据来源的。

在视图定义中可以使用复杂的SELECT命令。

机视图。

3.DML使用方法

SQL的DML包括插入（INSERT）、删除（DELETE）、修改（UPDATE）等命令。

DML命令的执行是可能造成数据库不一致的根源。

因此，每一条语句在执行前，SQL-Server都要验证语句是否符合完整性要求，包括实体完整性、参照完整性、用户定义完整性。

1）INSERT命令

SQL语言的插入命令：

INSERTINTOtable-name（column-list）VALUES（values-list），可以完成数据输入功能。

注意：

在执行过程中，如果有一条语句出错误，再次执行时，在出错语句前面的语句就会被拒绝执行。

因为，在查询分析器中的命令在执行时按顺序逐条执行，当执行到出错语句时停止执行，此时，在出错语句的前面各条语句的结果已经存入表中，故当重复执行时，它们违反了实体完整性。

2）DELETE命令

SQL的删除命令为：

DELETEFROMtable-name[WHEREcondition-expression];

执行DELETE命令后，系统会删除满足命令中条件表达式的所有元组。

这种删除只是逻辑的。

因此，当再次插入一个与被删除的元组具有相同关键字的元组时，被认为违反了实体完整性。

3）UPDATE命令

SQL的修改命令为：

UPDATEtable-name

SETcolumn-name=expression

[WHEREcondition-expression];

执行UPDATE命令后，系统会按照SET子句修改满足命令中条件表达式的所有元组。

如果使用修改命令更新关键字的值，而该关键字在其他表中作为外码存在时，操作可能违反参照完整性。

系统将拒绝执行。

4）SELECT命令

SELECT命令是SQL语言中使用最频繁的命令，其变化形式繁多，灵活运用SELECT命令可以完成任意复杂的查询要求。

SELECT命令的基本语法为:

SELECTquery_expression|column-list|*

FROMtable_name_list|view_name_list

[WHEREcondition-expression]

[GROUPBY[HAVINGcondition-expression]]

[ORDERBYorder_expression|column[ASC|DESC]][,…n];

由于SELECT命令非常复杂,上面的语法描述还无法完全表达，请参考TransactionSQL的帮助。

具体查询方法参考教材。

4.DCL的使用方法

1）SQLServer登录认证

∙新登录的创建

新的登录者可以是WINDOWS的用户或用户组，也可以是使用SQLServer认证模式的登录帐号，设定WINDOWS的用户为SQLServer登录者时使用sp_grantlogin。

创建新的使用SQLServer认证模式的登录帐号时使用sp_addlogins。

∙登录的删除

删除基于使用SQLServer认证模式的SQLServer的登录可以使用sp_droplogin。

但是，如果与该登录匹配的数据库用户仍存在sysuser表中，则不能删除该登录账号。

删除WINDOWS的用户或用户组时，使用sp_revokelogin，拒绝WINDOWS的用户或用户组连接到SQLServer时，使用sp_decnylogin。

∙登陆的查看

sp_helplogins可以用来显示SQLServer的所有登录者的信息。

如图29是显示数据库ems的所有登录者信息的代码。

2）用户管理

SQLServer利用下列系统过程管理数据库用户：

sp_addusersp_grangdbaccesssp_dropuserSp_dropusersp_revokedbaccesssp_helpuser。

其中sp_adduserSp_dropuser是为了保持与以前版本相兼容，因此不主张使用，在SQLServer2000中，建议使用sp_grangdbaccesssp_revokedbaccess。

∙创建新数据库用户

所有的数据库用户（除了guest）均必须与某一登录账号相匹配，因此在使用系统过程创建新数据库用户时，不但要指出新数据库用户的名称，还必须指出一个已经存在的登录账号。

系统过程sp_grantdbaccess用于为SQLServer登录用户或NT用户或用户组建立相匹配的数据库用户帐号。

∙删除数据库用户

系统过程sp_revokedbaccess用于将数据库用户从当前数据库中删除，删除后与其匹配的登录即无法再访问该数据库了。

∙查看数据库用户信息

sp_helpuser可用来显示当前数据库的指定用户信息。

3）用户授权管理

对于登录到SQLServer的合法数据库用户，必须获得对数据库操作的授权。

在SQLServer中包括两种类型的权限，即对象权限和语句权限。

对象权限总是针对表、视图、存储过程而言，它决定了能对这些对象执行哪些操作（如UPDATEDELETEINSERTSELECTEXECUTE）。

不同类型的对象支持不同类型的操作，表3为各种对象的可能操作列举。

表3：

对象权限总结表

对象

操作

表

SELECTUPDATEDELETEINSERTREFERENCE

视图

SELECTUPDATEDELETEINSERT

存储过程

EXECUTE

列

UPDATESELECT

表4语句权限总结表

语句

含义

CREATEDTATBASE

创建数据库

CREATETABLE

创建表

CREATEVIEW

创建视图

CREATERULE

创建规则

CREATEDEFAULT

创建缺省

CREATEPROCEDURE

创建存储过程

BACKUPDATABASE

备份数据库

BACKUPLOG

创建事务日志

语句权限指数据库用户执行某种语句的操作权，如创建数据库、表、存储过程等。

这些语句虽然（如CREATE命令）包含有操作对象，但这些对象在操作前并不存在于数据库中。

表4为所有权语句清单。

5.数据库的备份和恢复

备份是指对SQLServer数据库或事务日志进行拷贝，如果数据库因意外而损坏，备份文件可以用来恢复数据库。

SQLServer2000中有四种备份类型，分别是：

数据库备份、事务日志备份、差异备份、文件和文件组备份。

在使用是可以根据实际情况选择一种或几种的混合应用。

1）备份设备

在进行备份以前必须创建备份设备。

备份设备是用来存储数据库、事务日志、文件和文件组备份的存储介质。

可以是磁盘、磁带或管道。

SQLServer2000提供了两种创建备份的途径，即可视化方式和命令方式。

用命令备份数据库

使用BACKUP命令进行备份操作。

●备份完整数据库

/*EXECsp_addumpdevice'DISK','backupdevice_name','d:

\backupdev\ems.bak'目标路径*/

BACKUPDATABASEdatabaseTObackupdevice_name

2）恢复数据库

使用命令恢复数据库

数据库恢复命令为：

RESTOREDATABASE/LOG。

二、实验内容

实验1基本表的创建、数据插入

i.建立教学管理中的三个基本表：

Students（SNO，SNAME，AGE，SEX）学生（学号，姓名，年龄，性别）

Courses（CNO，CNAME，SCORE，PC＃）课程（课程号，课程名，学分，先行课号）

SC（SNO，CNO，GRADE）选修（学号，课程号，成绩）

（2）用INSERT命令输入数据。

表6基本表Students的数据：

表7基本表Courses的数据

表8基本表SC的数据（空格为未选修，NULL为选修但还未有成绩）：

SNO

CNO

S1

S2

S3

S4

S6

S8

C1

85

90

89

84

88

87

C2

73

NULL

86

82

75

85

C3

88

80

90

NULL

C4

89

85

NULL

92

88

C5

73

NULL

87

实验2：

数据查询

（1）列出选修课程号为C2的学生学号与姓名。

ii.检索选修课程名为“数学”的学生学号与姓名。

iii.检索没有选修C2课程的学生姓名与年龄。

iv.检索选修全部课程的学生姓名。

实验3：

数据修改、删除

（1）把C2课程的非空成绩提高10％。

（2）在SC表中删除课程名为“物理”的成绩所对应的元组。

（3）在S和SC表中删除学号为S8的所有数据。

实验4：

视图的操作

（1）建立男生学生的视图，属性包括学号，姓名，选修课程名和成绩。

（2）在男生视图中查询平均成绩大于80分的学生学号和姓名。

实验5：

库函数，授权控制

（1）计算每个学生选修课程的门数、平均成绩。

（2）建立一个合法的用户，将SC表的查询权限授予该用户。

（3）使用GRANT语句，把对基本表students、Courses、SC的使用权限授予其他用户。

实验6：

数据库的备份、恢复

（1）使用完全备份将你的实验数据库备份到软盘。

（2）删除你所建立的数据库。

（3）恢复你的数据库。

（4）在恢复后的数据库上撤销你建立的基本表和视图。

三、实验结果及代码

实验1基本表的创建、数据插入

i.由上分析数据库数据定义如下，下划线为主键，

Students（SNO，SNAME，AGE，SEX）

Courses（CNO，CNAME，SCORE，PC＃）

SC（SNO，CNO，GRADE）外键为sno，cno

代码为：

createdatabasesch

usesch;

go

createtablestudents（snochar（9）primarykey,

snamechar（20）notnull,

agechar（3）,

sexchar（6））;

createtablecourses（cnochar（9）primarykey,

cnamechar（20）notnull,

scoreint,

pcchar（3））;

createtablesc（snochar（9）foreignkeyreferencesstudents（sno）,

cnochar（9）,

gradeint,

foreignkey（cno）referencescourses（cno））;

（2）用INSERT命令输入数据。

根据表格输入数据代码为：

usesch;

go

insertintostudentsvalues（'S1','LU',20,'M'）;

insertintostudentsvalues（'S2','YIN',19,'M'）;

insertintostudentsvalues（'S3','XU',18,'F'）;

insertintostudentsvalues（'S4','QU',18,'F'）;

insertintostudentsvalues（'S6','PAN',14,'M'）;

insertintostudentsvalues（'S8','DONG',24,'M'）;

insertintoCoursesvalues（'C1','数学',4,'M'）;

insertintoCoursesvalues（'C2','英语',8,'M'）;

insertintoCoursesvalues（'C3','数据结构',4,'F'）;

insertintoCoursesvalues（'C4','数据库',3.5,'F'）;

insertintoCoursesvalues（'C5','网络',4,'M'）;

insertintoSCvalues（'S1','C1',85）;

insertintoSCvalues（'S2','C1',90）;

insertintoSCvalues（'S3','C1',89）;

insertintoSCvalues（'S4','C1',84）;

insertintoSCvalues（'S6','C1',88）;

insertintoSCvalues（'S8','C1',87）;

insertintoSCvalues（'S1','C2',73）;

insertintoSCvalues（'S2','C2',NULL）;

insertintoSCvalues（'S3','C2',86）;

insertintoSCvalues（'S4','C2',82）;

insertintoSCvalues（'S6','C2',75）;

insertintoSCvalues（'S8','C2',85）;

insertintoSCvalues（'S1','C3',88）;

insertintoSCvalues（'S2','C3',80）;

insertintoSCvalues（'S6','C3',90）;

insertintoSCvalues（'S8','C3',NULL）;

insertintoSCvalues（'S1','C4',89）;

insertintoSCvalues（'S2','C4',85）;

insertintoSCvalues（'S4','C4',NULL）;

insertintoSCvalues（'S6','C4',92）;

insertintoSCvalues（'S8','C4',88）;

insertintoSCvalues（'S1','C5',73）;

insertintoSCvalues（'S2','C5',NULL）;

insertintoSCvalues（'S8','C5',87）;

结果：

其他均已成功输入数据

实验2：

数据查询

（1）列出选修课程号为C2的学生学号与姓名。

代码为：

结果为：

usesch;

go

selectsc.sno,sname

fromstudents,sc

whereo='C2'andsc.sno=students.sno

（2）检索选修课程名为“数学”的学生学号与姓名。

代码为：

结果为：

usesch;

go

selectsc.sno,sname

fromstudents,sc,courses

whereame='数学'

ando=o

andstudents.sno=sc.sno

（3）检索没有选修C2课程的学生姓名与年龄。

代码为：

输出结果：

usesch;

go

selectsname,age

fromstudents

wherenotexists（select*

fromsc

whereo='c3'andsno=students.sno）;

（4）检索选修全部课程的学生姓名。

代码为：

输出结果：

usesch;

go

selectsname

fromstudents

wherenotexists（select*

fromcourses

wherenotexists（

select*

fromsc

wheresno=students.snoandcno=o））;

实验3：

数据修改、删除

（1）把C2课程的非空成绩提高10％。

代码为：

updatesc

setgrade=grade*1.1

whereo='c2'andoisnotnull

查询c2行代码结果如下成绩均提高10%

（2）在SC表中删除课程名为“物理”的成绩所对应的元组。

代码为：

deletefromSC

wherecnoin（selectcno

fromcourses

wherecname='物理'）

结果：

影响行为0

（3）在S和SC表中删除学号为S8的所有数据。

代码分别为：

deletefromsc

wheresc.sno='s8'

deletefromstudents

wherestudents.sno='s8'

实验4：

视图的操作

（1）建立男生学生的视图，属性包括学号，姓名，选修课程名和成绩。

代码为：

createviewstudent_m（sno,sname,cname,grade）

as

selectstudents.sno,students.sname,cname,grade

fromsc,students,courses

wherestudents.sno=sc.snoando=oandsex='M'

（2）在男生视图中查询平均成绩大于80分的学生学号和姓名。

代码为：

selectdistinctstudents.sno,students.sname

fromstudent_m,students

wherestudent_m.sno=students.snoandgrade>80

结果为：

（s8信息已在前面删除）

实验5：

库函数，授权控制

（1）计算每个学生选修课程的门数、平均成绩。

代码为：

selectstudents.sno,students.sname,count（cno）选修门数,avg（grade）平均成绩

fromstudents,sc

wherestudents.sno=sc.sno

groupbystudents.sno,sname

输出结果：

（2）建立一个合法的用户，将SC表的查询权限授予该用户。

usesch

go

execsp_addloginsch,sch

usesch

go

execsp_grantdbaccess'sch','sch'

GRANTSELECT

ONSC

TOsch;

（3）使用GRANT语句，把对基本表students、Courses、SC的使用权限授予其他用户。

usesch

go

GRANTallprivileges--授予权限

ONCourses

TOguest;

GRANTallprivileges--授予权限

ONstudents

TOguest;

GRANTallprivileges--授予权限

ONsc

TOguest;

实验6：

数据库的备份、恢复

（1）使用完全备份将你的实验数据库备份到软盘。

将数据库ems完全备份到设备backupdevice_ems上，在驱动器D上建立backupdev子目录执行下面代码：

usesch

go

EXECsp_addumpdevice'DISK','backupdevice_sch','d:

\backupdev\sch.bak'

BACKUPDATABASEschTObackupdevice_sch

结果：

已为数据库'sch'，文件'sch'（位于文件1上）处理了184页。

已为数据库'sch'，文件'sch_log'（位于文件1上）处理了2页。

BACKUPDATABASE成功处理了186页，花费0.389秒（3.728MB/秒）。

成功执行

将数据库sch的日志备份到设备backupdevice_sch上

/*EXECsp_addumpdevice'DISK','backupdevice_sch','d:

\backupdev\sch.bak'*/

BACKUPLOGschTObackupdevice_sch

（2）删除你所建立的数据库。

在对象资源管理器中找到数据库sch右键删除数据库sch。

（3）恢复你的数据库。

用下面代码实现恢复数据库：

restoredatabaseschfrombackupdevice_sch

（4）在恢复后的数据库上撤销你建立的基本表和视图。

删除基本表和视图代码：

usesch