《数据库原理》实验.doc

广西大学行健文理学院实验报告数据库原理实验 班级： 学号：姓名：实验1 建立基本表和数据完整性实验目的：1. 掌握表（关系）和索引的建立方法；2. 掌握表结构（关系模式）的修改方法；3. 实践DBMS提供的数据完整性功能，加深对数据完整性的理解。实验内容：1. 在studentdb数据库中利用查询分析器创建以下3个表，同时完成数据完整性的定义（实体完整性、参照完整性和用户定义的域完整性）：student（学生信息表）： 主码列名数据类型宽度小数位空否取值范围备 注Pksnochar10N学号snamechar10N姓名ssexchar2Y性别sagesmallintY不小于12年龄sdeptchar15Y系名 course（课程表）：主码列名数据类型宽度小数位空否备 注PkcnoChar2N课程号cnameChar20Y课程名称cpnoChar2Y先行课号ccreditsmallintY学分 sc（学生选课表）：主码列名数据类型宽度小数空否外码参照关系取值范围备注PksnoChar10NFkstudent学号cnoChar2NFkcourse课程号gradeDecimal51Y0x100成绩实验代码： create database studentdbuse studentdbgo create table student(sno char(10) not null primary key, -学号sname char (10) not null ,-姓名ssexchar(2)null ,-性别sagesmallint null check(sage=12) ,-年龄sdeptchar(15),-系名)create table course(cnoChar(2)not null primary key,-课程号cnameChar(20)null,-课程名称cpnoChar(2)null,-先行课号ccreditsmallint null,-学分)create table sc (sno char(10) not NULL ,-学号cno char(2) not NULL , -课程号grade Decimal(5,1) NULL check(grade=0 AND grade0成绩实验代码：create database spjdbuse spjdbgocreate table S (snochar(2) NOT NULL primary key,-供应商号snamechar(10) NOT NULL,-供应商名称statussmallint NULL CHECK ( status0),-供应商状态citychar(10) NULL ,-所在城市)create table P (pnochar(2) NOT NULL primary key,-零件号pnamechar(10) NOT NULL,-零件名称colorchar(2) NULL ,-颜色weightsmallint NULL CHECK (weight0),-重量)create table J (jnochar(2)NOT NULL PRIMARY KEY ,-工程项目号jnamechar(10) NOT NULL,-工程项目名称citychar(10) NULL,-所在城市)create table SPJ (snoChar(2)NOT NULL, -供应商号pnoChar(2)NOT NULL, -零件号jnoChar(2)NOT NULL , -工程项目号qtysmallintNULL CHECK(qty0),-供应数量primary key(sno,pno,jno),foreign key (sno) references S (sno), /* 表级完整性约束条件，sno是外码，被参照表是S */foreign key (pno) references P (pno), /* 表级完整性约束条件，pno是外码，被参照表是P */foreign key (jno) references J (jno), /* 表级完整性约束条件，jno是外码，被参照表是J */)3.修改表结构，具体要求如下：(1) 将表course的cname列的数据类型改为varchar(40)。alter table coursealter column cname varchar(40);(2) 为表student增加一个新列: birthday(出生日期), 类型为datetime, 默认为空值。alter table studentadd birthday datetime default null;4.分别建立以下索引(如果不能成功建立,请分析原因)(1) 在student表的sname列上建立普通降序索引。create index sname on student(sname desc);(2) 在course表的cname列上建立唯一索引。create unique index cname on course(cname); (3) 在sc表的sno列上建立聚簇索引。create CLUSTERED index sc on sc(sno) /不成功,系统默认给主键分配了一个聚簇索引(4) 在SPJ表的sno(升序), pno(升序)和jno(降序)三列上建立一个普通索引。create index spj on spj(sno asc ,pno asc ,jno desc);实验2 SQL数据操作实验目的：1. 向实验1建立的表中添加数据(元组)，掌握INSERT语句的用法；2. 修改基本表中的数据，掌握UPDATE语句的用法；3. 删除基本表中的数据，掌握DELETE语句的用法；4. 体会数据完整性约束的作用，加深对数据完整性及其约束的理解。实验内容：1. 将教材P82表中的数据添加到数据库studentdb中。 use studentdb go INSERT into student (sno,sname,ssex,sage,sdept) values(200215121,李勇,男,20,CS);INSERT into student (sno,sname,ssex,sage,sdept) values(200215122,刘晨,女,19,CS);INSERT into student (sno,sname,ssex,sage,sdept) values(200215123,王敏,女,18,MA);INSERT into student (sno,sname,ssex,sage,sdept) values(200215125,张立,男,29,IS); use studentdb go INSERT into course (cno,cname,cpno,ccredit) values(1,数据库,5,4);INSERT into course (cno,cname,cpno,ccredit) values(2,数学,null,2);INSERT into course (cno,cname,cpno,ccredit) values(3,信息系统,1,4);INSERT into course (cno,cname,cpno,ccredit) values(4,操作系统,6,3);INSERT into course (cno,cname,cpno,ccredit) values(5,数据结构,7,4);INSERT into course (cno,cname,cpno,ccredit) values(6,数据处理,null,2);INSERT into course (cno,cname,cpno,ccredit) values(7,PASCAL语言,6,4);use studentdb go INSERT into sc (sno,cno,grade)values(200215121,1,92);INSERT into sc (sno,cno,grade) values(200215121,2,85);INSERT into sc (sno,cno,grade) values(200215121,3,88);INSERT into sc (sno,cno,grade) values(200215122,2,90);INSERT into sc (sno,cno,grade) values(200215122,3,80);2. 将教材P75表中的数据添加到数据库spjdb中，体会执行插入操作时检查实体完整性规则、参照完整性规则和用户定义完整性规则的效果。use spjdbgo insert into S (SNO,SNAME,STATUS,CITY) VALUES (S1,精益,20,天津);insert into S (SNO,SNAME,STATUS,CITY) VALUES (S2,盛锡,10,北京);insert into S (SNO,SNAME,STATUS,CITY) VALUES (S3,东方红,30,北京);insert into S (SNO,SNAME,STATUS,CITY) VALUES (S4,丰泰盛,20,天津);insert into S (SNO,SNAME,STATUS,CITY) VALUES (S5,为民,30,上海);use spjdbgo insert into P (PNO,PNAME,COLOR,WEIGHT) VALUES (P1,螺母,红,12);insert into P (PNO,PNAME,COLOR,WEIGHT) VALUES (P2,螺栓,绿,17);insert into P (PNO,PNAME,COLOR,WEIGHT) VALUES (P3,螺丝刀,蓝,14);insert into P (PNO,PNAME,COLOR,WEIGHT) VALUES (P4,螺丝刀,红,14);insert into P (PNO,PNAME,COLOR,WEIGHT) VALUES (P5,凸轮,蓝,40);insert into P (PNO,PNAME,COLOR,WEIGHT) VALUES (P6,齿轮,红,30);use spjdb go insert into J (jno,jname,city) values (J1,三建,北京); insert into J (jno,jname,city) values (J2,一汽,长春); insert into J (jno,jname,city) values (J3,弹簧厂,天津); insert into J (jno,jname,city) values (J4,造船厂,天津); insert into J (jno,jname,city) values (J5,机车厂,唐山); insert into J (jno,jname,city) values (J6,无线电厂,常州); insert into J (jno,jname,city) values (J7,半导体厂,南京);use spjdb go insert into SPJ (sno,pno,jno,qty) values (S1,P1,J1,200); insert into SPJ (sno,pno,jno,qty) values (S1,P1,J3,100); insert into SPJ (sno,pno,jno,qty) values (S1,P1,J4,700); insert into SPJ (sno,pno,jno,qty) values (S1,P2,J2,100); insert into SPJ (sno,pno,jno,qty) values (S2,P3,J1,400); insert into SPJ (sno,pno,jno,qty) values (S2,P3,J2,200); insert into SPJ (sno,pno,jno,qty) values (S2,P3,J4,500); insert into SPJ (sno,pno,jno,qty) values (S2,P3,J5,400); insert into SPJ (sno,pno,jno,qty) values (S2,P5,J1,400); insert into SPJ (sno,pno,jno,qty) values (S2,P5,J2,100); insert into SPJ (sno,pno,jno,qty) values (S3,P1,J1,200); insert into SPJ (sno,pno,jno,qty) values (S3,P3,J1,200); insert into SPJ (sno,pno,jno,qty) values (S4,P5,J1,100); insert into SPJ (sno,pno,jno,qty) values (S4,P6,J3,300); insert into SPJ (sno,pno,jno,qty) values (S4,P6,J4,200); insert into SPJ (sno,pno,jno,qty) values (S5,P2,J4,100); insert into SPJ (sno,pno,jno,qty) values (S5,P3,J1,200); insert into SPJ (sno,pno,jno,qty) values (S5,P6,J2,200); insert into SPJ (sno,pno,jno,qty) values (S5,P6,J4,500); （1）实体完整性规则 use spjdb go insert into S (SNO,SNAME,STATUS,CITY) VALUES (S1,WWW,20,WWW); /S1重复 （2）参照完整性规则use spjdb go insert into SPJ (sno,pno,jno,qty) values (S7,P6,J4,500); /S表里没有S7这条记录（3）用户定义完整性规则 use spjdb go insert into P (PNO,PNAME,COLOR,WEIGHT) VALUES (P7,齿轮,红,-30); /weight小于0 3. 设计几个删除操作（35个），体会执行删除操作时检查参照完整性规则的效果。(1) delete from S where sno=s1 /不能删除(2) drop table s /不能删除(3) delete from student where sno=200215121 /不能删除4. 设计一组更新操作（35个），体会执行更新操作时检查实体完整性规则、参照完整性规则和用户定义完整性规则的效果。（1）insert into SPJ (sno,pno,jno,qty) values (null,P6,J4,500); /sno不能为空（2）insert into SPJ (sno,pno,jno,qty) values (S7,P6,J4,500); /S表里没有S7这条记录（3）insert into P (PNO,PNAME,COLOR,WEIGHT) VALUES (P7,齿轮,红,-30); /weight小于0实验3 SQL数据查询实验目的：1. 熟练掌握SELECT语句，能够运用该语句完成各种查询；2. 观察查询结果, 体会SELECT语句实际应用；3 要求学生能够在查询分析器中使用SELECT语句进行单表查询和多表查询。实验内容：基于实验1和实验2中建立的表，完成如下查询：（1和2选一题，第3题必做）1. 参照教材P75表中的数据，完成P74习题5中(1)(5)的查询。 （1） select distinct sno from spj where jno=J1 （2） select distinct sno from spj where jno=J1 and pno=P1（3）select sno from spj where spj.jno=J1 AND PNO IN(SELECT PNO from p where color=红 );（4）select Jno from spj where jno not in (select jno from spj,p,s where s.city=天津 AND COLOR=红 AND S.SNO=SPJ.SNO AND P.PNO=SPJ.PNO )；（5）select DISTINCT jno from spj Xwhere not exists (select * from spj Y where sno=S1 AND NOT EXISTS ( SELECT *FROM SPJ AS ZWHERE X.JNO=Z.JNO AND Y.PNO=Z.PNO AND sno=S1) 2. 参照教材P75表中的数据，完成P127习题5中(1)(11)的查询。3. 参照教材P82表中的数据，完成以下查询：(1)查询course表中所有学分大于2并且序号小于5的课程信息。 select cname from course where cno2(2)查询学分介于2和8之间的课程的信息。 select * from course where ccredit2 and ccredit=3order by ccredit asc(5)查询每个学生及其选修课情况。 select sno ,cnofrom sc(6)查询选修2号课程且成绩在80分以上的学生信息。select *from sc where cno=2 and grade80(7)查询每个学生的学号、姓名、选修的课程及成绩。 select student.sno ,sname , cno ,gradefrom student left join sc on (student.sno=sc.sno) (8)在studentdb数据库中，查询所有计算机系学生的学号、选修课程号以及分数。(使用IN谓词)。方法一：select student.sno , cno ,gradefrom student left join sc on (student.sno=sc.sno) where student.sdept=cs方法二：select sno,cno,gradefrom scwhere sno in (select sno from student where sdept=cs) (9)查询选修了课程名为“操作系统”的学生学号和姓名。select student.sno ,snamefrom student where sno in(select sno from sc where cno in (select cno from course where cname=操作系统) )(10)在studentdb数据库中，查询所有计算机系学生的学号、选修课程号以及分数（使用EXISTS谓词）。 select sno,cno,gradefrom sc Xwhere not exists ( select sno from sc Ywhere not exists ( select sno from student Zwhere sdept=cs AND Z.SNO=X.SNO)(11)检索所学课程包含学生200215122所学课程的学生的学号。SELECT DISTINCT SNO FROM SC XWHERE NOT EXISTS(select cnofrom sc Ywhere SNO=200215122 AND NOT EXISTS (SELECT * FROM SC ZWHERE Z.SNO=X.SNO AND Z.CNO=Y.CNO)(12)查询李勇成绩高于刘晨的数学成绩的课程名称。SELECT CNAMEFROM COURSE WHERE CNO IN( SELECT CNO FROM SC WHERE SNO =(select student.sno from student where sname=李勇) and GRADE ( select grade from sc where sno=( select student.sno from student where sname=刘晨) and cno=( SELECT COURSE.CNO FROM COURSE WHERE COURSE.CNAME=数学) ) )（13）查询李勇选修的课程中成绩大于刘晨平均成绩的课程名称。SELECT CNAME FROM COURSE WHERE CNO IN( SELECT CNO FROM SC WHERE SNO =( select student.sno from student where sname=李勇) AND grade( select avg(grade) from sc where sno=( select student.sno from student where sname=刘晨) );实验4 数据视图与数据控制操作实验目的：1. 掌握CREATE VIEW语句，能够运用该语句建立数据视图；2. 掌握在视图上进行查询、插入、更新和删除操作；3. 利用GRANT语句对数据库对象的操作权限管理。实验内容：1.基于实验1和实验2中建立的表，参照实验3中的查询，完成如下的视图操作：(1)建立一个计算机系(CS)学生的视图COMPUTE_S，并要求进行修改和插入操作时仍需保证该视图只有计算机系的学生；create view COMPUTE_S as select * from studentwhere sdept=cswith check option(2) 建立一个信息系(IS)选修了1号课程且成绩在80分以上的学生视图IS_S1；create view IS_S1 as select * from studentwhere sdept=is and sno in(select sno from scwhere cno=1 and grade80)(3) 建立一个每个学生的学号、姓名、选修的课名及成绩的视图S_C_GRADE；create view S_C_GRADE as select student.sno ,sname, cno ,gradefrom student left join sc on (student.sno=sc.sno)(4) 建立一个所有计算机系学生的学号、选修课程号以及成绩的视图COMPUTE_GRADE;create view COMPUTE_GRADE as select student.sno ,sname, cno ,gradefrom student left join sc on (student.sno=sc.sno) where sdept=cs(5)用INSERT、UPDATE、DELETE语句更新视图：在查询分析器窗体下完成教材P123-124中例12、13、14，分别运行并观察结果。update COMPUTE_S set sname=刘辰WHERE SNO=200215122更新成功INSERT INTO COMPUTE_S(sno,sname,sage,sdept)values(200215159,赵新,20,cs);插入成功 (指定了计算机系 不指定则不成功)delete from COMPUTE_S where sno=200215129and sdept=cs删除成功（指定了计算机系，不指定则不成功）2. 基于实验1和实验2中建立的表，完成如下的数据授权操作（要求学生分组共同完成本实验）: (1)数据对象操作授权：将对表和视图的查询及其INSERT、UPDATE、DELETE操作权限分不同情况授与其他用户，同时取得其他用户的授权，体会安全控制的作用。 以本地系统用户登录数据库，在 安全性 登录名 新建登录名 u1 、u2、u3选择studentdb数据库 在安全性 用户 新建用户名 u1 登录名选择与之对应的u1 ，同理新建u2、u3（1） GRANT select ON sc TO u1 /将查询权限赋给u1用户，此时u1仅能查看sc表(2) GRANT insert ON sc TO u2 /将插入权限赋给u2用户，此时u1仅能对sc表进行插入操作with grant option(3) GRANT update ON sc TO u3 with grant option / 将sc更新权限赋给u3同时u3有对其他用户赋给更新的权限 (2)在授权过程中, 体会GRANT语句中WITH GRANT OPTION短语的作用。 使用u2 用户登陆 GRANT insert ON sc TO u3with grant option/将u2的sc插入权限赋给u3，并允许u3对其他用户赋给更新的权限/此时u3有插入和更新的权限，并且可以对其他用户赋给更新和插入的权限 (3