实验报告的书写格式及实验内容Word文件下载.docx
《实验报告的书写格式及实验内容Word文件下载.docx》由会员分享，可在线阅读，更多相关《实验报告的书写格式及实验内容Word文件下载.docx（22页珍藏版）》请在冰点文库上搜索。
,'
女'
））,
check（sagebetween15and30））;
（2）课程表course：
createtablecourse
（cnochar
（2）notnull,
cnamechar（12）,
cpnochar
（2）,
ccreditdecimal
（1）,
primarykey（cno））;
注：
student和course表建完以后，最后建SC表。
因为SC表的SNO和CNO是外码，必须先建立student和course表，才能建SC表，不能颠倒顺序。
（3）选课表SC
createtableSC
cnochar
（2）notnull,
gradedecimal（3）,
primarykey（sno,cno）,
foreignkey（sno）referencesstudent（sno）,
foreignkey（cno）referencescourse（cno）,
check（gradebetween0and100））;
3、给以上三张表插入记录，将第82页各个表中的记录分别插入各个基本表。
以下给出插入记录的命令，也可自行任意添加记录。
insertintostudentvalues（'
200215121'
李勇'
20,'
CS'
）;
200215122'
刘晨'
19,'
IS'
200215123'
王敏'
18,'
MA'
200215125'
张立'
insertintocoursevalues（'
1'
数据库'
5'
4）;
2'
数学'
null,2）;
3'
信息系统'
4'
操作系统'
6'
3）;
数据结构'
7'
数据处理'
PASCAL语言'
insertintoSCvalues（'
92）;
85）;
88）;
90）;
80）;
select*fromstudent;
select*fromcourse;
select*fromsc;
实验2数据操纵
在查询分析器中的设计Insert语句、Delete语句、Update语句完成记录的插入、删除、修改操作。
（1）、①在SC中插入记录（‘200115130’，‘1’）
②在Student表中插入记录（‘200115130’，‘陈冬’，‘男’，‘IS’，18），若先执行①，再执行②，结果会怎么样？
为什么？
（2）、先创建一个基本表Student_AVG（包含学号、姓名和平均成绩），然后将各个学生的学号、姓名和平均成绩插入；
（3）、将所有学生的年龄都增加1岁；
（4）、将计算机系的全体学生的成绩置零；
（5）、删除Student_AVG学号为200212122的学生记录；
（6）、删除计算机系所有学生的选课记录；
（7）、将SC表被删除的记录重新插入。
（8）、修改cno为‘1’的记录的课程名称为‘数据库技术’。
（9）、将pubs数据库中authors的前10条记录的state列更改为ZZ。
USEpubs
GO
UPDATEauthors
SETState=‘ZZ’
FROM（SELECTTOP10*FROMauthorsorderbyau_lname）AST1
WHEREauthors.au_id=T1．au_id
（10）、从coure表中删除所有credit为4的记录。
实验3索引及视图
一、利用查询分析器完成以下索引的建立。
（1）为Student表按学号升序建立唯一索引（取名为StuSno）；
（2）为Course表按课程名的降序建立聚簇索引（取名为CouCname）；
（3）为SC表按学号升序和课程号降序建立唯一索引（取名为SCSnoCno）。
（4）使用系统存储过程sp_helpindex查看student及course上的索引信息。
（5）删除Student中的StuSno索引。
二、利用T-SQL语句创建视图，对视图进行修改和删除操作。
建立计算机系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有计算机系的学生；
建立计算机系选修了1号课程且成绩在90分以上的学生的视图,视图名为：
S_C；
建立一个学生出生年份的视图，视图名为：
S_Year；
将学生的学号及其平均成绩定义为一个视图（取名为S_G）。
⑤建立一个视图，要求给出每一门课的间接先行课。
⑥建立一个男生所在系的视图，视图包括学号、姓名、性别、系别四列。
⑦建立一个视图，给出每一门课的平均成绩。
⑧删除第⑤题建立的视图。
⑨查询第
题建立的视图，只显示姓名及出生年份。
⑩将第
题中的学生“张立”的名字改为“张力”。
⑾在第⑥题的视图中插入新的学生，学号：
200215135，姓名：
王小二，性别：
男，系：
计算机系。
⑿删除第⑥题视图中的‘李勇‘的记录。
⒀查询信息系选修了1号课程的学生学号和姓名（在视图中查询）。
⒁在S_G视图中查询平均成绩在90分以上的学生的学号和平均成绩。
实验4数据查询
一、利用查询分析器完成以下查询。
1．查询全体学生的姓名、学号、出生年份和所在系，并使用中文别名；
2．查询所有年龄在20岁以下的学生姓名、性别及其年龄；
3．查询信息系（IS）、数学系（MA）和计算机系（CS）学生的姓名和性别；
4．查询姓名中第2个字为“立“字的学生的姓名和学号；
5．查询以“PA“开头，且倒数第5个字符为C的课程的详细情况；
6．查询选修了3号课程的学生学号及其成绩，查询结果按分数的降序排列；
7．查询选修了3号课程的学生人数；
8．计算各门课程的平均成绩和选课人数；
9．计算每个学生的平均成绩；
10．查询选修了2号课程且成绩在90分以上的所有学生的学号、姓名和成绩；
11．查询与“刘晨“在同一个系学习的学生的详细情况；
12．查询选修了课程名为“信息系统“的学生学号、姓名和成绩；
13．查询没有选修1号课程的学生姓名、年龄和所在系；
14．查询至少选修了学生“200215122“选修的全部课程的学生的学号；
15．查询选修了1号课程和2号课程的学生的学号和姓名；
16．找出至少选修了一门其先行课号为“5”号课程的学生姓名；
17．查询1号课程的学生最高分数；
18．查询所有不姓刘的学生姓名；
19．从course表中查询出课程号不为“1”、“4”或“7”的课程的课程号、课程名称、以及学分，cno用别名‘课程序号’，cname用别名’课程名称’，credit用别名’学分’；
20．使用SELECT语句查看全局变量的数据，在查询分析器中输入以下代码：
SELECT@@version
21．分别从course中检索出前2条及前面67％的教师的信息，在查询分析器中输入以下代码：
SELECTtop2*FROMcourse;
go
SELECTtop67Percent*FROMcourse;
22．用SQL语句中使用字符串连接符（+）来连接作者的姓和名，在查询分析器中输入以下代码：
。
SELECTcno+′,′+cname+′,′+cpnoFRONcourse
GO
23.建立从student到sc的外连接。
1.SELECTstudent.sno,sname,cno,grade
fromstudentfullOUTERJOINscONstudent.sno=sc.sno//全外连接
2.SELECTstudent.sno,sname,cno,grade
fromstudentleftOUTERJOINscONstudent.sno=sc.sno//左外连接
3.SELECTstudent.sno,sname,cno,grade
fromstudentrightOUTERJOINscONstudent.sno=sc.sno//右外连接
实验5数据完整性及数据安全性
一、添加SQL用户帐号（S101-S115）
declare@iint,@studentchar（4）--声明两个变量，一个为整型，一个为字符型
set@i=101
while@i<
=115
begin
set@student='
S'
+CAST（@iaschar（3））
execsp_addlogin@loginame=@student,@passwd=@student,--创建SQL服务器的登录帐号
@defdb='
stu'
@deflanguage='
english'
--设置登录数据库为stu
usestu--打开stu数据库
EXECsp_grantdbaccess@loginame=@student,@name_in_db=@student--创建数据库用户帐号，与登录帐号一致，才能用登录帐号访问数据库
EXECsp_addrolemember@rolename='
db_owner'
@membername=@student--为数据库角色增加用户帐号
set@i=@i+1
end
go
usestu
go
EXECsp_helpuser--列出当前数据库中所有的用户帐号
二、DAC自主存取控制
1．进行下列授权及收回授权操作：
①．用户S102把查询Student表的权限授给用户s101；
②．用户S101把对Sc表和Course表全部操作权限授给用户s102和s103；
③．S101把对表SC查询权限授给所有用户；
④．S101把查询Student表和修改学生姓名的权限授给用户s104；
⑤．S102把表SC的INSERT权限授给用户s105，并允许将此权限再授予其他用户；
⑥．把用户s104修改学生表姓名的权限收回；
⑦．收回所有用户对表SC的查询权限；
⑧．把用户s105对SC表的INSERT权限收回。
⑨．把s101查询Student表的权限收回；
2．拒绝对方的某些操作。
①．DBA拒绝S101的对student表的操作
denyallonstudenttos101;
/*DBA拒绝S101对Student表的各种操作，执行该命令后，S101在自己查询器中不能对Student表作任何操作。
*/
②．S101要查询Student表。
（s101在自己的查询分析器中输入）
Select*fromStudent;
--运行时出错，s101不能查询student，因对student表的操作被s102拒绝。
③DBA拒绝S102对SC表的所有操作
denyallprivilegesonsctos102;
④．S102不能对SC表进行操作（S102在自己的查询分析器中输入）
Insertintoscvalues（'
--S102不能对SC插入新记录，因为操作被S101拒绝。
3.DBA授予S101和S102权限，对方即可操作。
①．DBA授权S101的对student表的操作。
grantselectonstudenttos101;
②．s101可对student表进行各种操作。
Select*fromstudent;
三、数据完整性的设置
1使用T-SQL语句修改表结构
向Student表增加“入学时间“列，其数据类型为日期型；
将年龄的数据类型改为半字长整数；
增加学生姓名必须取唯一值的约束；
删除“入学时间“列；
删除学生姓名必须取唯一值的约束（注：
用系统存储过程 sp_help+表名 查看已建立的约束的名字）；
⑥给学生表增加“grade”字段并加上CHECK约束，让其不可以大于100。
⑦给学生表中添加“birthday”字段，并且这个日期不能在录入当天的日期之后。
⑧添加具有默认值的可为空的列：
在学生表中加入“matriculationday”字段，并且这一字段的默认值为录入当天的日期。
⑨在删除约束时请先用系统存储过程:
sp_help+表名 查看创建、修改表的基本情况以及建立的约束的名字。
实验6存储过程与触发器
一、存储过程实验步骤：
1、建立SS数据库
CREATEDATABASEss--数据库名为ss
（NAME=ss_data,--数据库的逻辑名为ss_data
\sql\ss_data.mdf'
--数据库的物理名,请事先在E盘上建立sql_data文件夹。
（NAME=ss_log,--日志文件的逻辑名为ss_log
\sql\ss_log.ldf'
FILEGROWTH=1MB）;
2、建立SS库中的四张表profession、student、teach、department
createtableprofession（
pnochar（5）,
professorchar（6）notnull,
foreignkey（pno）referencesteach（tno）,）;
createtablestudent（
snochar（8）primarykey,
dnochar（3）,
snamechar（8）notnull,
sageint
foreignkey（dno）referencesdepartment（dno））;
createtableteach（
tnochar（5）primarykey,
tnamechar（8）notnull,
birthdaydatetime
createtabledepartment（
dnochar（3）primarykey,
dnamechar（20））;
四张表中输入记录如下（注意表与表之间的外码参照）：
3、完成以下实验内容：
1）.建立存储过程，查询50岁以下的职称为教授的教师代号、姓名，年龄和所在系部名称.
CREATEPROClist_famous_teachers
AS
BEGIN
SELECTtno,tname,CAST（GETDATE（）-birthdayASint）/365asage,department.dno,dname
FROMteach,department,profession
WHEREteach.tno=profession.pnoandCAST（GETDATE（）-birthdayASint）/365<
50
ANDteach.dno=department.dnoandprofessor='
教授'
END
执行该存储过程：
execlist_famous_teachers
执行结果：
2）.创建存储过程，从表student（sno,dno,sname,ssex,sage）中查询指定系的学生信息：
CREATEPROClist_student_department@departmentchar（6）
SELECT*FROMstudentWHEREdno=@department
execlist_student_department@department='
002'
3）.创建存储过程，向表student（sno,dno,sname,ssex,sage）中添加学生信息：
createPROCEDUREaddstu（@snochar（8）,@dnochar（3）,@snamechar（8）,@ssexchar
（2）,@sageint）
BEGIN
INSERTINTOstudent（sno,dno,sname,ssex,sage）VALUES（@sno,@dno,@sname,@ssex,@sage）
execaddstu'
950101'
006'
胡桃'
20
1.4
（1）查看list_student_department存储过程代码
EXECsp_helptext'
list_student_department'
;
EXECsp_helptext@objname='
;
1.4
（2）修改存储过程:
将从student表中查询指定系的学生信息改为student表和departmen表进行左外连接。
ALTERPROClist_student_department
@departmentchar（3）
AS
SELECTsno,sname,sage,dname,department.dno
FROMstudentLEFTJOINdepartmentONstudent.dno=department.dno
WHEREstudent.dno=@department
执行修改过的list_student_department存储过程
EXEClist_student_department'
显示结果如下：
二、触发器
1.在STU库中，为“SC”表建立一个名为tri4的insert触发器，其作用是当在SC表插入记录时，显示‘unauthorized’。
/*在STU库的SC表上建立一个TRI4的触发器，当对SC进行insert操作时，tri4触发器被触发*/
CREATETRIGGERtri4
ONsc
FORINSERT
AS
RAISERROR（'
unauthorized'
10,1）
/*当用户向SC表插入记录时，则触发了tri4触发器，显示‘unauthorized’，但数据仍然被插入到SC表。
insertintoscvalues（'
200215124'
30'
2.对student表建立一个update的触发器，该触发器是不允许修改学生的学号。
CREATETRIGGERtri5
ONstudent
FORUPDATE
IFUPDATE（sno）
BEGIN
RAISERROR（'
Unauthorized!
'
ROLLBACKTRANSACTION
END
--建好触发器后试着执行UPDATE操作：
UPDATEstudentSETsno=‘200215138’WHEREsname=‘李勇'
3.在查询分析器中使用SQL语句创建触发器Studentdelete，删除student表中的记录时同时也删除SC表中的相应选课记录。
//注意:
事先在企业管理器中设置级联删除和级联更新。
CREATETRIGGERstudentdelete
FORDELETE
DELETEFROMSC
WHEREsnoIN（SELECTsnoFROMdeleted）
执行删除student表中记录的操作，激发触发器Studentdelete
DELETEFROMstudentWHEREsno='
200212122'
4.向SC表插入一记录时，通过触发器检查记录sno字段的值在STUDENT表中是否存在，若不存在，则取消插入操作。
SETQUOTED_IDENTIFIERON
SETANSI_NULLSON
G
升级会员 