实验报告的书写格式及实验内容.docx
《实验报告的书写格式及实验内容.docx》由会员分享,可在线阅读,更多相关《实验报告的书写格式及实验内容.docx(22页珍藏版)》请在冰豆网上搜索。
![实验报告的书写格式及实验内容.docx](https://file1.bdocx.com/fileroot1/2023-2/22/6854be02-0b4e-4627-a5ed-8398d5685e43/6854be02-0b4e-4627-a5ed-8398d5685e431.gif)
实验报告的书写格式及实验内容
实验报告的书写格式及实验内容
书写格式:
一、实验目的
二、实验内容
三、实验步骤与结果(见后,先写题目再写代码及运行结果)
实验步骤与结果:
实验1数据库及基本表的建立
1.创建一个student数据库,该数据库存放在E:
\sql_data目录下(如果没有该文件夹,则事先在windows资源管理器中建立该文件夹)。
CREATEDATABASEstu--数据库名为student
ON
(NAME=student_data,--数据库的逻辑名为student_data
FILENAME=‘E:
\sql_data\student_data.mdf‘,--数据库的物理名
SIZE=10MB,--数据库的初始大小为10MB
MAXSIZE=50MB,--数据库的最大容量为50MB
FILEGROWTH=10%--数据库容量自动按10%的比例增长
)
LOGON
(NAME=stu_log,--日志文件的逻辑名为stu_log
FILENAME='E:
\sql_data\student_log.ldf',
SIZE=2MB,
MAXSIZE=5MB,
FILEGROWTH=1MB
)
2.分别创建教材P82页三张表的结构,执行结果如下图所示。
(1)学生表student
createtablestudent
(snochar(9)notnull,
snamechar(8),
ssexchar
(2),
sagesmallint,
sdeptchar
(2),
primarykey(sno),
check(ssexin('男','女')),
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
(snochar(9)notnull,
cnochar
(2)notnull,
gradedecimal(3),
primarykey(sno,cno),
foreignkey(sno)referencesstudent(sno),
foreignkey(cno)referencescourse(cno),
check(gradebetween0and100));
3、给以上三张表插入记录,将第82页各个表中的记录分别插入各个基本表。
以下给出插入记录的命令,也可自行任意添加记录。
insertintostudentvalues('200215121','李勇','男',20,'CS');
insertintostudentvalues('200215122','刘晨','女',19,'IS');
insertintostudentvalues('200215123','王敏','女',18,'MA');
insertintostudentvalues('200215125','张立','男',19,'IS');
insertintocoursevalues('1','数据库','5',4);
insertintocoursevalues('2','数学',null,2);
insertintocoursevalues('3','信息系统','1',4);
insertintocoursevalues('4','操作系统','6',3);
insertintocoursevalues('5','数据结构','7',4);
insertintocoursevalues('6','数据处理',null,2);
insertintocoursevalues('7','PASCAL语言','6',4);
insertintoSCvalues('200215121','1',92);
insertintoSCvalues('200215121','2',85);
insertintoSCvalues('200215121','3',88);
insertintoSCvalues('200215122','2',90);
insertintoSCvalues('200215122','3',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
GO
(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
GO
21.分别从course中检索出前2条及前面67%的教师的信息,在查询分析器中输入以下代码:
SELECTtop2*FROMcourse;
go
SELECTtop67Percent*FROMcourse;
go
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('200215123','3',90);--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
ON
(NAME=ss_data,--数据库的逻辑名为ss_data
FILENAME='E:
\sql\ss_data.mdf',--数据库的物理名,请事先在E盘上建立sql_data文件夹。
SIZE=10MB,--数据库的初始大小为10MB
MAXSIZE=50MB,--数据库的最大容量为50MB
FILEGROWTH=10%--数据库容量自动按10%的比例增长
)
LOGON
(NAME=ss_log,--日志文件的逻辑名为ss_log
FILENAME='E:
\sql\ss_log.ldf',
SIZE=2MB,
MAXSIZE=5MB,
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,
ssexchar
(2),
sageint
foreignkey(dno)referencesdepartment(dno));
createtableteach(
tnochar(5)primarykey,
dnochar(3),
tnamechar(8)notnull,
ssexchar
(2),
birthdaydatetime
foreignkey(dno)referencesdepartment(dno));
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
GO
执行该存储过程:
execlist_famous_teachers
执行结果:
2).创建存储过程,从表student(sno,dno,sname,ssex,sage)中查询指定系的学生信息:
CREATEPROClist_student_department@departmentchar(6)
AS
BEGIN
SELECT*FROMstudentWHEREdno=@department
END
执行该存储过程:
execlist_student_department@department='002'
执行结果:
3).创建存储过程,向表student(sno,dno,sname,ssex,sage)中添加学生信息:
createPROCEDUREaddstu(@snochar(8),@dnochar(3),@snamechar(8),@ssexchar
(2),@sageint)
AS
BEGIN
INSERTINTOstudent(sno,dno,sname,ssex,sage)VALUES(@sno,@dno,@sname,@ssex,@sage)
END
执行该存储过程:
execaddstu'950101','006','胡桃','男',20
执行结果:
1.4
(1)查看list_student_department存储过程代码
EXECsp_helptext'list_student_department';
EXECsp_helptext@objname='list_student_department';
1.4
(2)修改存储过程:
将从student表中查询指定系的学生信息改为student表和departmen表进行左外连接。
ALTERPROClist_student_department
@departmentchar(3)
AS
BEGIN
SELECTsno,sname,sage,dname,department.dno
FROMstudentLEFTJOINdepartmentONstudent.dno=department.dno
WHEREstudent.dno=@department
END
执行修改过的list_student_department存储过程
EXEClist_student_department'006'
显示结果如下:
二、触发器
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','2','30')
2.对student表建立一个update的触发器,该触发器是不允许修改学生的学号。
CREATETRIGGERtri5
ONstudent
FORUPDATE
AS
IFUPDATE(sno)
BEGIN
RAISERROR('Unauthorized!
',10,1)
ROLLBACKTRANSACTION
END
--建好触发器后试着执行UPDATE操作:
UPDATEstudentSETsno=‘200215138’WHEREsname=‘李勇'
3.在查询分析器中使用SQL语句创建触发器Studentdelete,删除student表中的记录时同时也删除SC表中的相应选课记录。
//注意:
事先在企业管理器中设置级联删除和级联更新。
CREATETRIGGERstudentdelete
ONstudent
FORDELETE
AS
DELETEFROMSC
WHEREsnoIN(SELECTsnoFROMdeleted)
执行删除student表中记录的操作,激发触发器Studentdelete
DELETEFROMstudentWHEREsno='200212122';
4.向SC表插入一记录时,通过触发器检查记录sno字段的值在STUDENT表中是否存在,若不存在,则取消插入操作。
SETQUOTED_IDENTIFIERON
GO
SETANSI_NULLSON
G