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

1、实验报告的书写格式及实验内容实验报告的书写格式及实验内容书写格式：一、实验目的二、实验内容三、实验步骤与结果（见后，先写题目再写代码及运行结果）实验步骤与结果：实验1数据库及基本表的建立1创建一个student数据库，该数据库存放在E:sql_data目录下（如果没有该文件夹，则事先在windows资源管理器中建立该文件夹）。CREATE DATABASE stu -数据库名为studentON (NAME=student_data, -数据库的逻辑名为student_dataFILENAME=E:sql_datastudent_data.mdf , -数据库的物理名SIZE=10MB, -数

2、据库的初始大小为10MBMAXSIZE=50MB, -数据库的最大容量为50MBFILEGROWTH=10% -数据库容量自动按10 %的比例增长)LOG ON(NAME=stu_log, -日志文件的逻辑名为stu_logFILENAME=E:sql_datastudent_log.ldf ,SIZE=2MB,MAXSIZE=5MB,FILEGROWTH=1MB)2.分别创建教材P82页三张表的结构，执行结果如下图所示。 (1)学生表studentcreate table student(sno char(9) not null,sname char(8), ssex char(2), sa

3、ge 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表，不能颠倒顺序

4、。(3)选课表SCcreate 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

5、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);insert into course values(1,数据库,5,4);insert into course values(2,数学,null,2);insert into course values(3,信息系统,1,4);insert int

6、o 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)

7、;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_AVG（包含学号、姓名和平均成绩），然后将各个学生的学号、姓名和平均成绩插入；

8、(3)、将所有学生的年龄都增加1岁；(4)、将计算机系的全体学生的成绩置零；(5)、删除Student_AVG学号为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 =

9、T1au_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语句创建视图，对视图进行修改和删除操作。建立计算机系学生的视图，并要求进行修改和插入操

10、作时仍需保证该视图只有计算机系的学生；建立计算机系选修了1号课程且成绩在90分以上的学生的视图,视图名为：S_C；建立一个学生出生年份的视图，视图名为：S_Year；将学生的学号及其平均成绩定义为一个视图（取名为S_G）。建立一个视图，要求给出每一门课的间接先行课。建立一个男生所在系的视图，视图包括学号、姓名、性别、系别四列。建立一个视图，给出每一门课的平均成绩。删除第题建立的视图。查询第题建立的视图，只显示姓名及出生年份。将第题中的学生“张立”的名字改为“张力”。在第题的视图中插入新的学生，学号：200215135，姓名：王小二，性别：男，系：计算机系。删除第题视图中的李勇的记录。查询信息系

11、选修了1号课程的学生学号和姓名（在视图中查询）。在S_G视图中查询平均成绩在90分以上的学生的学号和平均成绩。实验4 数据查询一、利用查询分析器完成以下查询。1查询全体学生的姓名、学号、出生年份和所在系，并使用中文别名；2查询所有年龄在20岁以下的学生姓名、性别及其年龄；3查询信息系（IS）、数学系(MA)和计算机系(CS)学生的姓名和性别；4查询姓名中第2个字为“立“字的学生的姓名和学号；5查询以“PA“开头，且倒数第5个字符为C的课程的详细情况；6查询选修了3号课程的学生学号及其成绩，查询结果按分数的降序排列；7查询选修了3号课程的学生人数；8计算各门课程的平均成绩和选课人数；9计算每个学

12、生的平均成绩；10查询选修了2号课程且成绩在90分以上的所有学生的学号、姓名和成绩；11查询与“刘晨“在同一个系学习的学生的详细情况；12查询选修了课程名为“信息系统“的学生学号、姓名和成绩；13查询没有选修1号课程的学生姓名、年龄和所在系；14查询至少选修了学生“200215122“选修的全部课程的学生的学号；15查询选修了1号课程和2号课程的学生的学号和姓名；16找出至少选修了一门其先行课号为“5”号课程的学生姓名；17查询 1 号课程的学生最高分数；18查询所有不姓刘的学生姓名；19从course表中查询出课程号不为“1”、“4”或“7”的课程的课程号、课程名称、以及学分，cno用别名课

13、程序号，cname 用别名课程名称，credit用别名学分； 20使用SELECT语句查看全局变量的数据，在查询分析器中输入以下代码： SELECT version GO21分别从course中检索出前2条及前面67的教师的信息，在查询分析器中输入以下代码： SELECT top 2* FROM course; go SELECT top 67 Percent* FROM course; go 22用SQL语句中使用字符串连接符（+）来连接作者的姓和名，在查询分析器中输入以下代码：。SELECT cno+, +cname+, +cpno FRON courseGO 23.建立从student到

14、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

15、 数据完整性及数据安全性一、添加SQL用户帐号（S101-S115）declare i int,student char(4) -声明两个变量，一个为整型，一个为字符型set i=101while i=115begin 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=st

16、udent, name_in_db=student -创建数据库用户帐号，与登录帐号一致，才能用登录帐号访问数据库 EXEC sp_addrolemember rolename=db_owner, membername=student -为数据库角色增加用户帐号 set i=i+1endgouse stugo EXEC sp_helpuser -列出当前数据库中所有的用户帐号 二、DAC自主存取控制1进行下列授权及收回授权操作：用户S102把查询Student表的权限授给用户s101；用户S101把对Sc表和Course表全部操作权限授给用户s102和s103； S101把对表SC查询权限授给

17、所有用户； 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要

18、查询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表的操

19、作。grant select on student to s101;s101可对student 表进行各种操作。 Select * from student; 三、数据完整性的设置1使用T-SQL语句修改表结构向Student表增加“入学时间“列，其数据类型为日期型；将年龄的数据类型改为半字长整数；增加学生姓名必须取唯一值的约束；删除“入学时间“列；删除学生姓名必须取唯一值的约束(注：用系统存储过程sp_help+表名查看已建立的约束的名字)；给学生表增加“grade”字段并加上CHECK约束，让其不可以大于100。给学生表中添加“birthday”字段，并且这个日期不能在录入当天的日期之后。

20、添加具有默认值的可为空的列：在学生表中加入“matriculationday”字段，并且这一字段的默认值为录入当天的日期。在删除约束时请先用系统存储过程:sp_help+表名查看创建、修改表的基本情况以及建立的约束的名字。实验6 存储过程与触发器一、存储过程实验步骤：1、建立SS数据库CREATE DATABASE ss -数据库名为ssON (NAME=ss_data, -数据库的逻辑名为ss_dataFILENAME=E:sqlss_data.mdf, -数据库的物理名,请事先在E盘上建立sql_data文件夹。SIZE=10MB, -数据库的初始大小为10MBMAXSIZE=50MB,

21、-数据库的最大容量为50MBFILEGROWTH=10% -数据库容量自动按10 %的比例增长)LOG ON(NAME=ss_log, -日志文件的逻辑名为ss_logFILENAME=E:sqlss_log.ldf,SIZE=2MB,MAXSIZE=5MB,FILEGROWTH=1MB);2、建立SS库中的四张表profession、student、teach、departmentcreate table profession( pno char(5) , professor char(6) not null, foreign key(pno) references teach(tno),)

22、;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 depa

23、rtment(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,

24、profession WHERE teach.tno=profession.pno and CAST(GETDATE()-birthday AS int)/36550 AND teach.dno=department.dno and professor=教授ENDGO执行该存储过程： exec list_famous_teachers 执行结果：2）.创建存储过程，从表student (sno,dno,sname,ssex,sage)中查询指定系的学生信息：CREATE PROC list_student_department department char(6)AS BEGIN SELECT

25、 * FROM student WHERE dno=departmentEND执行该存储过程：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) VALUES (sno,

26、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 depa

27、rtment char(3) ASBEGIN SELECT sno,sname,sage,dname,department.dno FROM student LEFT JOIN department ON student.dno=department.dno WHERE student.dno=departmentEND执行修改过的list_student_department存储过程EXEC list_student_department 006显示结果如下：二、触发器1.在STU库中，为“SC”表建立一个名为tri4的insert触发器，其作用是当在SC表插入记录时，显示unauthori

28、zed。 /*在STU库的SC表上建立一个TRI4的触发器，当对SC进行insert操作时，tri4触发器被触发*/CREATE TRIGGER tri4ON sc FOR INSERT AS RAISERROR(unauthorized,10,1)/*当用户向SC表插入记录时，则触发了tri4触发器，显示unauthorized，但数据仍然被插入到SC表。*/ insert into sc values(200215124,2,30)2. 对student表建立一个update的触发器，该触发器是不允许修改学生的学号。CREATE TRIGGER tri5ON studentFOR UPDA

29、TEAS 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 studentFOR DELETEASDELETE FROM SCWHERE sno IN(SELECT sno FROM deleted)执行删除student表中记录的操作，激发触发器StudentdeleteDELETE FROM student WHERE sno=200212122;4.向SC表插入一记录时，通过触发器检查记录sno字段的值在STUDENT表中是否存在，若不存在，则取消插入操作。SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON G