数据库sql2000的第三-第八次实验报告

1、.;实验38：题目：对数据库实现插入、修改、查询、删除等基本操作以及对建立索引、视图和创建存储过程、触发器的实现一、实验目的1. 掌握使用T_SQL语句和企业管理器对数据表进行插入、修改和删除数据的操作，并 体会数据完整性约束的作用，加深对数据完整性及其约束的理解。2. 通过本实验，要熟练掌握INSERT、UPDATE、DELETE语句的语法和使用方法。掌握简单表的数据查询、数据排序和多表操作时数据连接查询的操作方法。掌握嵌套查询和统计查询的操作方法。3. 理解了索引和视图的概念并且掌握了他们的使用方法4. 理解了存储过程的概念，掌握了存储过程的使用方法5. 学习使用触发器，体会触发器的执行的

2、时机，加深了对触发器的功能和作用的理解二、实验内容1. 分别使用T-SQL语句和企业管理器，向数据库JWGL、数据库Market中各张表输入一些记录。2. 使用T-SQL语句进行插入、修改和删除记录的操作。（1）插入数据时既要插入一些不违背数据完整性约束的记录，也要插入一些违背数据完整性约束的记录，观察插入后的结果，并利用相关知识解释此现象。在涉及参照完整性约束的参照表中插入数据时，理解系统是如何保证完整性的。（2）修改数据时除使用正确数据外，也可以对修改后的新值使用一些违背数据完整性约束的数据，观察系统执行后的结果。另外进一步观察在涉及参照完整性约束的参照表和被参照表中修改数据时系统的执行情

3、况，如果违背参照完整性那么观察结果。（3）删除记录时一般不会出现问题，但要是在有参照完整性约束的被参照表中删除数据时也存在破坏完整性的情况。观察删除记录系统的执行结果。3. 简单查询操作。该试验包括投影、选择表达、数据排序等。（1）在数据库JWGL中的学生表Student、课程表Course、学生选课表SC中完成第3章例题3.10-例题3.28的查询操作。（2）在数据库Market的客户表Customers、货品表Goods、订单表Orders中完成第3章习题6中（1）-（4）的查询。4. 连接查询操作。该实验包括等值连接、自然连接、一般连接（内连接）、外连接、左外连接、右外连接和自然连接等。

4、(1）在数据库JWGL中的学生表Student、课程表Course、学生选课表SC中完成第3章例题3.35-例题3.39中的查询操作。（2）在数据库Market的客户表Customers、货品表Goods、订单表Orders中完成第3章习题6中（5）的查询。5. 在数据库JWGL的学生表Student、课程表Course、学生选课表SC中完成第3章例题3.40-例题3.47中的查询操作。6. 在数据库Market的客户表Customers、货品表Goods、订单表Orders中完成第3章习 题6中（6）-（9）的查询。7. 建立索引，对JWGL数据库的学生选课表SC建立索引，要求按照Cno升序

5、、Grade降序建立一个名为SC_ind的索引8. 在JWGL数据库中建立视图，实现对视图的建立、修改、插入、删除的基本操作9. 在JWGL数据库中建立存储过程的操作，并使用exec语句调用存储过程，观察他们的执行的结果10. 在JWGL数据库上创建触发器，然后在想相关的表中执行插入、删除、更新等的语句，观察他们的执行的结果三、实验环境 装有SQL Server 2000的实验机四、实验前的准备 1要明确能够创建数据库的用户必须是系统管理员，或是被授权使用CREATE DATABASE语句的用户。2创建数据库必须要确定数据库名、所有者（即创建数据库的用户）、数据库大小（最初的大小、最大的大小、

6、是否允许增长及增长的方式）和存储数据的文件。3 确定数据库包含哪些表以及包含的各表的结构，还要了解SQL Server 的常用数据类型，以创建数据库的表。4了解常用的创建数据库和表的方法。五、实验步骤本实验中所用到的学生-课程数据库表如下：Student学号Sno姓名Sname性别Ssex年龄Sage所在系Sdept950001李大勇男20CS950002刘晓晨女19CS950003王敏女18MA950004张成功男19ISCourse课程号Cno课程名Cname先修课Cpno学分Ccredit1数据库542数学23信息系统144操作系统635数据结构766数据处理27C语言64SC学号Sno

7、课程号Cno成绩Grade950001190950001288950001379950002293950002380具体的实验操作如下所示：/*查询操作*/*查询student、SC、Course表的全部信息*/*查询学生的姓名和年龄*/select Sname ,Agefrom student;select Sno,Sname,lower(Sdept) from student;/*查询计算机系所有学生的学号和姓名*/select Sno,Sname from student where Sdept=CS;/*查询考试及格的*/select Sno from SC where Grade60

8、;/*查询计算机系数学系信息系的学生姓名和年龄*/select Sname,Age from student where Sdept in(CS,MA,IS);/*查询既不是数学系也不是计算机系的学生姓名和性别*/select Sname,Age from student where Sdept not in(MA,CS);/*查询信息系的所有学生的情况*/select *from student where Sdept like IS;/*查询计算机系年龄在20岁以下的学生的学号，姓名和性别*/select Sno,Sname,Sex from student where Sdept=CS

9、and Age90;/*嵌套查询 查询1号课程的学生的姓名*/select Sname from student where Sno in(select Sno from SC where Cno=1);/*查询和张明在同一个系学习的学生的学号和姓名*/select Sno,Sname from student where Sdept=(select Sdept from student where Sname=张成功);/*相关子查询*/*查询每个学生超过他所选的课程平均成绩的课程号*/select Sno,Cno from SC x where Grade=(select avg(Grad

10、e) from SC y where y.Cno=x.Cno);/*带有exists谓词的子查询*/*查询所有选修了3号课程的学生和姓名*/select Sname from student where exists(select *from SC where Sno=student.Sno and Cno=3);/*修改数据:将所有学生的年龄增加1岁*/update student set Age=Age+1;select *from student;/*视图的建立*/* 建立计算机系的学生视图*/create view c_studentas select Sno,Sname,Sex,Ag

11、efrom studentwhere Sdept=CS with check optionselect *from c_student;/*建立选择2号课程成绩在80分以上的学生视图*/create view student_course_2(Sno,Sname,Sex,Age,Cno,Grade)as select student.Sno,Sname,Sex,Age,Cno,Gradefrom student,SCwhere Student.Sno=SC.Sno and Grade=80 and Cno=2select *from student_course_2;/*建立反映学生的出生年份

12、的视图*/create view student_birth(Sno,Sname,Sbirth)as select Sno,Sname,2008-Agefrom studentselect *from student_birth;/*建立索引*/if exists(select name from sysindexes where name=SC_ind)DROP INDEX SC. SC_ind;GO/*建立索引，按照Cno升序，Grade降序*/create index SC_ind on SC(Cno asc,Grade desc);/*查询视图*/*在计算机系的视图上查询年龄在18-2

13、0岁之间的学生学号和姓名*/select Sno,Sname from c_student where Age between 18 and 20;/*更新视图*/*在视图中将学号为950002的学生姓名改为张良的信息*/update c_student set Sname=张良 where Sno=950002;select *from c_student;/*存储过程的创建*/*在学生课程表上创建存储过程，查看”张明“的有关信息和选课的情况*/create procedure student_courseas select student.Sno,Sname,Cname,Gradefrom

14、 student,Course,SCwhere student.Sno=SC.Sno and Course.Cno=SC.Cno and Sname=张成功;/*执行该存储过程*/execute student_course;/*创建一个存储过程用于向学生表中插入记录*/create procedure student_insert xh char(6),name varchar(8),sex char(2),age int,tel varchar(11),sdep char(10) asinsert into studentvalues(xh,name,sex,age,tel,sdep)/*

15、调用该存储过程*/execute student_insert 950005,黎明,男,20,null,CS;/*创建一个存储过程，返回某门课程的学生人数*/create procedure student_countCourseName varchar(20),StudentSum int outputas select StudentSum=count(*)from Course,SCwhere Course.Cno=SC.Cno and Cname=CourseName;/*执行该存储过程*/declare studentNum int;execute student_count 数据结

16、构,studentNum output;select The result is:,studentNum;/*触发器*/*创建触发器，当学生表中的记录被更新时，显示表中的所有记录*/create trigger student_change on student after insert,update,deleteas select *from student/*在学生表中创建delete触发器，实现student与SC表的级联删除*/create trigger studentDelete on studentafter deleteas delete from SCwhere Sno in

17、(select deleted.Sno from deleted);/*创建insert触发器，当检查学生的Sno是否存在时。存在则插入*/create trigger sc_insert on SCafter insert as if(select count(*) from student,insertedwhere student.Sno=inserted.Sno)=0begin print 学号不存在，不能进行插入操作rollback transaction end/*创建update触发器，禁止对student表中的学生的学号进行修改*/create trigger student_update