数据库实验.docx

1、数据库实验数据库原理实验 班级： 学号：姓名：实验1 建立基本表和数据完整性实验目的：1. 掌握表（关系）和索引的建立方法；2. 掌握表结构（关系模式）的修改方法；3. 实践DBMS提供的数据完整性功能，加深对数据完整性的理解。实验内容：1. 在studentdb数据库中利用查询分析器创建以下3个表，同时完成数据完整性的定义（实体完整性、参照完整性和用户定义的域完整性）：student（学生信息表）： 主码列名数据类型宽度小数位空否取值范围备 注Pksnochar10N学号snamechar10N姓名ssexchar2Y性别sagesmallintY不小于12年龄sdeptchar15Y系名

2、course（课程表）：主码列名数据类型宽度小数位空否备 注PkcnoChar2N课程号cnameChar20Y课程名称cpnoChar2Y先行课号ccreditsmallintY学分 sc（学生选课表）：主码列名数据类型宽度小数空否外码参照关系取值范围备注PksnoChar10NFkstudent学号cnoChar2NFkcourse课程号gradeDecimal51Y0x100成绩实验代码： create database studentdbuse studentdbgo create table student(sno char(10) not null primary key, -学号

3、sname char (10) not null ,-姓名ssex char (2) null , -性别sage smallint null check(sage=12) , -年龄sdept char(15) , -系名)create table course(cno Char(2) not null primary key ,-课程号cname Char(20) null,- 课程名称cpno Char(2) null , -先行课号ccredit smallint null, -学分)create table sc (sno char(10) not NULL ,-学号cno char

4、(2) not NULL , -课程号grade Decimal(5,1) NULL check(grade=0 AND grade0成绩实验代码：create database spjdbuse spjdbgocreate table S (sno char(2) NOT NULL primary key, -供应商号sname char(10) NOT NULL, -供应商名称status smallint NULL CHECK ( status0), -供应商状态city char(10) NULL , -所在城市)create table P (pno char(2) NOT NULL

5、 primary key, -零件号pname char(10) NOT NULL, -零件名称color char(2) NULL , -颜色weight smallint NULL CHECK (weight0) ,-重量)create table J (jno char(2) NOT NULL PRIMARY KEY , -工程项目号jname char(10) NOT NULL , -工程项目名称city char(10) NULL , -所在城市)create table SPJ (sno Char(2) NOT NULL, -供应商号pno Char(2) NOT NULL, -零

6、件号jno Char(2) NOT NULL , -工程项目号qty smallint NULL 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.修改表结

7、构，具体要求如下：(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 de

8、sc);(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语句的

9、用法；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);INS

10、ERT 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);INSER

11、T 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

12、(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,8

13、0);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) VA

14、LUES (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,

15、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 (J

16、3,弹簧厂,天津); 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 SP

17、J (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,

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

19、,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); in

20、sert 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）用

21、户定义完整性规则 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）

22、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 要求学生能够在查询分析器中

23、使用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

24、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表中的数据

25、，完成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 ccredit8(3)从course表中查询出课程号为1或4或7的课程的课程号、课程名称、以及学分。方法一：select cno ,cname ,ccreditfrom course where cno=1unionselect cno ,cname ,ccreditfrom course where cno=4unionselect cno ,cname ,ccreditfrom course where cno=7方法二： select cno ,cname ,ccredit from course