实验报告的书写格式及实验内容

实验报告的书写格式及实验内容

书写格式：

一、实验目的

二、实验内容

三、实验步骤与结果（见后，先写题目再写代码及运行结果）

实验步骤与结果：

实验1数据库及基本表的建立

1．创建一个student数据库，该数据库存放在E:\sql_data目录下（如果没有该文件夹，则事先在windows资源管理器中建立该文件夹）。

CREATE DATABASE stu --数据库名为student

ON

(NAME=student_data, --数据库的逻辑名为student_data

FILENAME=…E:\sql_data\student_data.mdf …, --数据库的物理名

SIZE=10MB, --数据库的初始大小为10MB

MAXSIZE=50MB, --数据库的最大容量为50MB

FILEGROWTH=10% --数据库容量自动按10 %的比例增长)

LOG ON

(NAME=stu_log, --日志文件的逻辑名为stu_log

FILENAME='E:\sql_data\student_log.ldf ',

SIZE=2MB,

MAXSIZE=5MB,

FILEGROWTH=1MB

)

2.分别创建教材P82页三张表的结构，执行结果如下图所示。

(1)学生表student

create table student

(sno char(9) not null,

sname char(8),

ssex char(2),

sage smallint,

sdept char(2),

primary key(sno),

check(ssex in ('男','女')),

check(sage between 15 and 30));

(2)课程表course：

create table course

(cno char(2) not null,

cname char(12),

cpno char(2),

ccredit decimal(1),

primary key(cno));

注：student和course表建完以后，最后建SC表。因为SC表的SNO和CNO是外码，必须先建立student和course表，才能建SC表，不能颠倒顺序。

(3)选课表SC

create table SC

(sno char(9) not null,

cno char(2) not null,

grade decimal(3),

primary key(sno,cno),

foreign key(sno) references student(sno),

foreign key(cno) references course(cno),

check(grade between 0 and 100));

3、给以上三张表插入记录，将第82页各个表中的记录分别插入各个基本表。以下给出插入记录的命令，也可自行任意添加记录。

insert into student values('200215121','李勇','男',20,'CS');

insert into student values('200215122','刘晨','女',19,'IS');

insert into student values('200215123','王敏','女',18,'MA');

insert into student values('200215125','张立','男',19,'IS');

i nsert into course values('1','数据库','5',4);

insert into course values('2','数学',null,2);

insert into course values('3','信息系统','1',4);

insert into course values('4','操作系统','6',3);

insert into course values('5','数据结构','7',4);

insert into course values('6','数据处理',null,2);

insert into course values('7','PASCAL语言','6',4);

insert into SC values('200215121','1',92);

insert into SC values('200215121','2',85);

insert into SC values('200215121','3',88);

insert into SC values('200215122','2',90);

insert into SC values('200215122','3',80);

select * from student;

select * from course;

select * from sc;

实验2 数据操纵

在查询分析器中的设计Insert语句、Delete语句、Update语句完成记录的插入、删除、修改操作。

(1)、①在SC中插入记录（‘200115130’，‘1’）

②在Student表中插入记录（‘200115130’，‘陈冬’，‘男’，‘IS’，18），若先执行①，再执行②，结果会怎么样？为什么？

(2)、先创建一个基本表Student_A VG（包含学号、姓名和平均成绩），然后将各

个学生的学号、姓名和平均成绩插入；

(3)、将所有学生的年龄都增加1岁；

(4)、将计算机系的全体学生的成绩置零；

(5)、删除Student_A VG学号为200212122的学生记录；

(6)、删除计算机系所有学生的选课记录；

(7)、将SC表被删除的记录重新插入。

(8)、修改cno为‘1’的记录的课程名称为‘数据库技术’。

(9)、将pubs数据库中authors的前10条记录的state列更改为ZZ。

USE pubs

GO

UPDATE authors

SET State=‘ZZ’

FROM (SELECT TOP 10 * FROM authors order by au_lname) AS T1

WHERE authors.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％的教师的信息，在查询分析器中输入以下代码：

SELECT top 2* FROM course;

go

SELECT top 67 Percent* FROM course;

go

22．用SQL语句中使用字符串连接符（+）来连接作者的姓和名，在查询分析器中输入以下代码：。

SELECT cno+′, ′+cname+′, ′+cpno FRON course

GO

23.建立从student到sc的外连接。

1.SELECT student.sno,sname,cno,grade

from student full OUTER JOIN sc ON student.sno=sc.sno //全外连接

2.SELECT student.sno,sname,cno,grade

from student left OUTER JOIN sc ON student.sno=sc.sno //左外连接

3.SELECT student.sno,sname,cno,grade

from student right OUTER JOIN sc ON student.sno=sc.sno //右外连接

实验5 数据完整性及数据安全性

一、添加SQL用户帐号（S101-S115）

declare @i int,@student char(4) --声明两个变量，一个为整型，一个为字符型set @i=101

while @i<=115

begin

set @student='S'+CAST(@i as char(3))

exec sp_addlogin @loginame=@student,@passwd=@student,--创建SQL服务器的登录帐号

@defdb='stu',@deflanguage='english' --设置登录数据库为stu

use stu --打开stu数据库

EXEC sp_grantdbaccess @loginame=@student, @name_in_db=@student --创建数据库用户帐号，与登录帐号一致，才能用登录帐号访问数据库

EXEC sp_addrolemember @rolename='db_owner', @membername=@student --为数据库角色增加用户帐号

set @i=@i+1

end

go

use stu

go

EXEC sp_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表的操作

deny all on student to s101 ; /* DBA拒绝S101对Student

表的各种操作，执行该命令后，S101

在自己查询器中不能对Student表

作任何操作。*/

②．S101要查询Student表。(s101在自己的查询分析器中输入)

Select * from Student; --运行时出错，s101不能查询student，因对

student表的操作被s102拒绝。

③DBA拒绝S102对SC表的所有操作

deny all privileges on sc to s102;

④．S102不能对SC表进行操作（S102在自己的查询分析器中输入）

Insert into sc values('200215123','3',90); --S102不能对SC插入新记

录，因为操作被

S101拒绝。

3. DBA授予S101和S102权限，对方即可操作。

①．DBA授权S101的对student表的操作。

grant select on student to s101;

②．s101可对student 表进行各种操作。

Select * from student;

三、数据完整性的设置

1使用T-SQL语句修改表结构

①向Student表增加“入学时间“列，其数据类型为日期型；

②将年龄的数据类型改为半字长整数；

③增加学生姓名必须取唯一值的约束；

④删除“入学时间“列；

⑤删除学生姓名必须取唯一值的约束(注：用系统存储过程sp_help+表名查看已建立的约束的名字)；

⑥给学生表增加“grade”字段并加上CHECK约束，让其不可以大于100。

⑦给学生表中添加“birthday”字段，并且这个日期不能在录入当天的日期之后。

⑧添加具有默认值的可为空的列：在学生表中加入“matriculationday”字段，并且这一字段的默认值为录入当天的日期。

⑨在删除约束时请先用系统存储过程:sp_help+表名查看创建、修改表的基本情况以及建立的约束的名字。

实验6 存储过程与触发器

一、存储过程实验步骤：

1、建立SS数据库

CREATE DATABASE ss --数据库名为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 %的比例增长)

LOG ON

(NAME=ss_log, --日志文件的逻辑名为ss_log FILENAME='E:\sql\ss_log.ldf',

SIZE=2MB,

MAXSIZE=5MB,

FILEGROWTH=1MB);

2、建立SS库中的四张表profession、student、teach、department

create table profession(

pno char(5) ,

professor char(6) not null,

foreign key(pno) references teach(tno),);

create table student(

sno char(8) primary key,

dno char(3) ,

sname char(8) not null,

ssex char(2),

sage int

foreign key(dno) references department(dno));

create table teach(

tno char(5) primary key,

dno char(3) ,

tname char(8) not null,

ssex char(2),

birthday datetime

foreign key(dno) references department(dno));

create table department(

dno char(3) primary key,

dname char(20));

四张表中输入记录如下（注意表与表之间的外码参照）：

3、完成以下实验内容：

1）.建立存储过程，查询50岁以下的职称为教授的教师代号、姓名，年龄和所在系部名称. CREATE PROC list_famous_teachers

AS

BEGIN

SELECT tno,tname,CAST(GETDATE()-birthday AS int)/365 as age,department.dno,dname FROM teach,department,profession

WHERE teach.tno=profession.pno and CAST(GETDATE()-birthday AS int)/365<50 AND teach.dno=department.dno and professor='教授'

END

GO

执行该存储过程：

exec list_famous_teachers

执行结果：

2）.创建存储过程，从表student (sno,dno,sname,ssex,sage)中查询指定系的学生信息：CREATE PROC list_student_department @department char(6)

AS

BEGIN

SELECT * FROM student WHERE dno=@department

END

执行该存储过程：

exec list_student_department @department='002'

执行结果：

3）.创建存储过程，向表student (sno,dno,sname,ssex,sage)中添加学生信息：

create PROCEDURE addstu(@sno char(8),@dno char(3),@sname char(8), @ssex char(2),@sage int)

AS

BEGIN

INSERT INTO student(sno,dno,sname,ssex,sage) V ALUES (@sno,@dno,@sname,@ssex,@sage)

END

执行该存储过程：

exec addstu '950101','006','胡桃','男',20

执行结果：

1.4（1）查看list_student_department存储过程代码

EXEC sp_helptext 'list_student_department' ;

EXEC sp_helptext @objname='list_student_department';

1.4（2）修改存储过程:将从student 表中查询指定系的学生信息改为student 表和departmen表进行左外连接。

ALTER PROC list_student_department

@department char(3)

AS

BEGIN

SELECT sno,sname,sage,dname,department.dno

FROM student LEFT JOIN department ON student.dno=department.dno

WHERE student.dno=@department

END

执行修改过的list_student_department存储过程

EXEC list_student_department '006'

显示结果如下：

二、触发器

1.在STU库中，为“SC”表建立一个名为tri4的insert触发器，其作用是当在SC表插入记录时，显示…unauthorized?。

/*在STU库的SC表上建立一个TRI4的触发器，当对SC进行insert操作时，tri4触发器被触发*/

CREATE TRIGGER tri4

ON sc

FOR INSERT

AS

RAISERROR('unauthorized',10,1)

/*当用户向SC表插入记录时，则触发了tri4触发器，显示…unauthorized?，但数据仍然被插入到SC表。*/

insert into sc values('200215124','2','30')

2.对student表建立一个update的触发器，该触发器是不允许修改学生的学号。

CREATE TRIGGER tri5

ON student

FOR UPDATE

AS

IF UPDATE (sno)

BEGIN

RAISERROR('Unauthorized!',10,1)

ROLLBACK TRANSACTION

END

--建好触发器后试着执行UPDATE操作：

UPDATE student SET sno=…200215138? WHERE sname=…李勇'

3.在查询分析器中使用SQL语句创建触发器Studentdelete，删除student 表中的记录时同时也删除SC表中的相应选课记录。//注意:事先在企业管理器中设置级联删除和级联更新。

CREATE TRIGGER studentdelete

ON student

FOR DELETE

AS

DELETE FROM SC

WHERE sno IN(SELECT sno FROM deleted)

执行删除student表中记录的操作，激发触发器Studentdelete

DELETE FROM student WHERE sno='200212122';

4.向SC表插入一记录时，通过触发器检查记录sno字段的值在STUDENT 表中是否存在，若不存在，则取消插入操作。

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

CREATE TRIGGER sc_tri on sc

FOR INSERT

AS

BEGIN

IF ((SELECT ins.sno from inserted ins) NOT IN /*inserted为逻辑表*/

(SELECT sno FROM student))

ROLLBACK

END

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

实验七 T-SQL程序设计和事务设计

一、在查询分析中利用Transact-SQL进行简单的程序设计。

1．显示学生表中的学号、姓名和性别。在显示性别时，如果是男生则输出“male”，如果是女生则输出“female”。

（1）打开查询分析器，并在查询窗口中输入下面的T-SQL程序：SELECT Sno AS 学号,Sname AS 姓名,性别=

CASE

WHEN Ssex='男' THEN 'male'

WHEN Ssex='女' THEN 'female'

END

FROM Student

GO

2.从数据库stu中查询所有同学选课成绩情况：姓名、课程名、成绩。要求：凡成绩为空者输出“未考”、小于60分的输出“不及格”；60分至70分的输出“及格”；70分至80分的输出“中”；80分至90分的输出“良好”；90分至100分的输出“优秀”。并且输出记录按下列要求排序：先按SNO升序，再按CNO号升序，最后按成绩降序。

(1) 打开查询分析器，并在查询窗口中输入下面的T_SQL程序：

USE STU

SELECT Sname AS 姓名,Cname AS课程名

成绩=

CASE

WHEN Grade IS NULL THEN '未考'

WHEN Grade<60 THEN '不及格'

WHEN Grade>=60 AND Grade<70 THEN '及格'

WHEN Grade>=70 AND Grade<80 THEN '中'

WHEN Grade>=80 AND Grade<90 THEN '良好'

WHEN Grade>=90 THEN '优'

END

FROM Student S,Course C,SC

WHERE S.Sno=SC.Sno AND https://www.360docs.net/doc/d82865488.html,o=https://www.360docs.net/doc/d82865488.html,o

ORDER BY S.Sno,https://www.360docs.net/doc/d82865488.html,o,Grade DESC

GO

二、设计并执行事务：将课程“数据结构”的课程号与“数据库原理”的课程号互换。

在查询窗口中输入事务程序：

BEGIN TRANSACTION InterconvertCno

USE STU

GO

DECLARE @CNO1 CHAR(6),@CNO2 CHAR(6)

SELECT @CNO1=Cno FROM Course WHERE Cname='数据结构'

SELECT @CNO2=Cno FROM Course WHERE Cname='数据库原理' UPDATE Course SET Cno='999' WHERE Cname='数据结构'

UPDATE Course SET Cpno='999' WHERE Cpno=@CNO1

--因为Cno为主键,不能重复,所以先将数据结构的课程号改为999 UPDATE Course SET Cno=@CNO1 WHERE Cname='数据库原理'

UPDATE Course SET Cpno=@CNO1 WHERE Cpno=@CNO2

UPDATE Course SET Cno=@CNO2 WHERE Cname='数据结构'

UPDATE Course SET Cpno=@CNO2 WHERE Cpno='999'

GO

COMMIT

GO

实验八数据备份与恢复

1.使用T-SQL语言执行完全数据库备份及其简单恢复：第一步：对数据库stu 执行完全备份SQLFullBackup_mybase2；第二步：删除表SC；第三步：用第一步中的备份恢复数据库；第四步：查询表SC是否存在。

（1）第一步：对数据库STU执行完全备份SQLFullBackup_mybase2：启动查询分析器，在查询窗口中输入如下T_SQL语句并执行：

USE stu

GO

BACKUP DATABASE stu TO DISK='d:\数据库备份\SQLFullBackup_mybase2' GO

（2）第二步：删除表SC。在查询窗口输入如下语句并执行：

USE stu

GO

DROP TABLE SC

GO

第三步：用第一步中的备份恢复数据库。停止对库stu的一切操作，在查询窗口输入如下语句并执行

RESTORE DATABASE STU FROM DISK='d:\数据库备份\SQLFullBackup_mybase2' GO

（4）第四步：查询表SC是否存在。在查询窗口查询表SC并执行，发现表SC 存在。

2.使用T_SQL执行数据库差异备份及其恢复：第一步：在Student表中插入一条周晓芳同学的记录；第二步：进行差异备份DiffBackup_mybase2；第三步：删除表Student中周晓芳同学的记录；第四步：利用前面的完全备份FullBack_mybase2和差异备份DiffBackup_mybase2恢复数据库；第五步：查询Student表中记录。

第一步：在Student表中插入一条周晓芳同学的记录。打开查询分析器，在查询查询窗口输入如下语句并执行。

USE STU

INSERT INTO Student VALUES('200215138','周晓芳','男',21,'PH ')

GO

第二步：进行差异备份DiffBackup_mybase2。在查询窗口中输入如下SQL语句并执行：

USE STU

BACKUP DATABASE STU

TO DISK='D:\数据库备份\SQLDiffBackup_mybase2'

WITH DIFFERENTIAL

GO

第三步：删除表Student中周晓芳同学的记录。

USE STU

DELETE FROM Student WHERE Sname='周晓芳'

GO

第四步：利用前面的完全备份FullBack_mybase2和差异备份DiffBackup_mybase2恢复数据库。在查询窗口输入如下语句并执行：

USE master

GO

RESTORE DATABASE STU FROM DISK='D:\数据库备份\FullBackup_mybase2' WITH NORECOVERY

GO

RESTORE DATABASE STU FROM DISK='D:\数据库备份\SQLDiffBackup_mybase2' GO

第五步：查询Student表中记录。