数据库实验.docx
《数据库实验.docx》由会员分享，可在线阅读，更多相关《数据库实验.docx（24页珍藏版）》请在冰点文库上搜索。
数据库实验
《数据库原理》实验
班级：
学号：
姓名：
实验1建立基本表和数据完整性
实验目的：
1.掌握表（关系）和索引的建立方法；
2.掌握表结构（关系模式）的修改方法；
3.实践DBMS提供的数据完整性功能，加深对数据完整性的理解。
实验内容：
1.在studentdb数据库中利用查询分析器创建以下3个表，同时完成数据完整性的定义（实体完整性、参照完整性和用户定义的域完整性）：
student（学生信息表）：
主码
列名
数据类型
宽度
小数位
空否
取值范围
备注
Pk
sno
char
10
N
学号
sname
char
10
N
姓名
ssex
char
2
Y
性别
sage
smallint
Y
不小于12
年龄
sdept
char
15
Y
系名
course（课程表）：
主码
列名
数据类型
宽度
小数位
空否
备注
Pk
cno
Char
2
N
课程号
cname
Char
20
Y
课程名称
cpno
Char
2
Y
先行课号
ccredit
smallint
Y
学分
sc（学生选课表）：
主码
列名
数据类型
宽度
小数
空否
外码
参照关系
取值范围
备注
Pk
sno
Char
10
N
Fk
student
学号
cno
Char
2
N
Fk
course
课程号
grade
Decimal
5
1
Y
0≤x≤100
成绩
实验代码：
createdatabasestudentdb
usestudentdb
go
createtablestudent（
snochar（10）notnullprimarykey,--学号
snamechar（10）notnull,--姓名
ssexchar
（2）null,--性别
sagesmallintnullcheck（sage>=12）,--年龄
sdeptchar（15）,--系名
）
createtablecourse（
cnoChar
（2）notnullprimarykey,--课程号
cnameChar（20）null,--课程名称
cpnoChar
（2）null,--先行课号
ccreditsmallintnull,--学分
）
createtablesc（
snochar（10）notNULL,--学号
cnochar
（2）notNULL,--课程号
gradeDecimal（5,1）NULLcheck（grade>=0ANDgrade<=100）,--成绩
primarykey（sno,cno）,
/*主码由两个属性构成，必须作为表级完整性进行定义*/
foreignkey（sno）referencesstudent（sno）,
/*表级完整性约束条件，sno是外码，被参照表是student*/
foreignkey（cno）referencescourse（cno）,
/*表级完整性约束条件，cno是外码，被参照表是course*/
）
go
2.在spjdb数据库中利用查询分析器创建以下4个表，同时完成数据完整性的定义（实体完整性、参照完整性和用户定义的域完整性）：
S（供应商信息表）：
主码
列名
数据类型
宽度
小数位
空否
取值范围
备注
Pk
sno
char
2
N
供应商号
sname
char
10
N
供应商名称
status
smallint
Y
大于0
供应商状态
city
char
10
Y
所在城市
P（零件信息表）：
主码
列名
数据类型
宽度
小数位
空否
取值范围
备注
Pk
pno
char
2
N
零件号
pname
char
10
N
零件名称
color
char
2
Y
颜色
weight
smallint
Y
大于0
重量
J（工程项目表）：
主码
列名
数据类型
宽度
小数位
空否
取值范围
备注
Pk
jno
char
2
N
工程项目号
jname
char
10
N
工程项目名称
city
char
10
Y
所在城市
SPJ（供应情况表）：
主码
列名
数据类型
宽度
小数
空否
外码
参照关系
取值范围
备注
Pk
sno
Char
2
N
Fk
S
供应商号
pno
Char
2
N
Fk
P
零件号
jno
Char
2
N
Fk
J
工程项目号
qty
smallint
Y
x>0
成绩
实验代码：
createdatabasespjdb
usespjdb
go
createtableS（
snochar
（2）NOTNULLprimarykey,--供应商号
snamechar（10）NOTNULL,--供应商名称
statussmallintNULLCHECK（status>0）,--供应商状态
citychar（10）NULL,--所在城市
）
createtableP（
pnochar
（2）NOTNULLprimarykey,--零件号
pnamechar（10）NOTNULL,--零件名称
colorchar
（2）NULL,--颜色
weightsmallintNULLCHECK（weight>0）,--重量
）
createtableJ（
jnochar
（2）NOTNULLPRIMARYKEY,--工程项目号
jnamechar（10）NOTNULL,--工程项目名称
citychar（10）NULL,--所在城市
）
createtableSPJ（
snoChar
（2）NOTNULL,--供应商号
pnoChar
（2）NOTNULL,--零件号
jnoChar
（2）NOTNULL,--工程项目号
qtysmallintNULLCHECK（qty>0）,--供应数量
primarykey（sno,pno,jno）,
foreignkey（sno）referencesS（sno）,
/*表级完整性约束条件，sno是外码，被参照表是S*/
foreignkey（pno）referencesP（pno）,
/*表级完整性约束条件，pno是外码，被参照表是P*/
foreignkey（jno）referencesJ（jno）,
/*表级完整性约束条件，jno是外码，被参照表是J*/
）
3.修改表结构，具体要求如下：
（1）将表course的cname列的数据类型改为varchar（40）。
altertablecourse
altercolumncnamevarchar（40）;
（2）为表student增加一个新列:
birthday（出生日期）,类型为datetime,默认为空值。
altertablestudent
addbirthdaydatetimedefaultnull;
4.分别建立以下索引（如果不能成功建立,请分析原因）
（1）在student表的sname列上建立普通降序索引。
createindexsnameonstudent（snamedesc）;
（2）在course表的cname列上建立唯一索引。
createuniqueindexcnameoncourse（cname）;
（3）在sc表的sno列上建立聚簇索引。
createCLUSTEREDindexsconsc（sno）
//不成功,系统默认给主键分配了一个聚簇索引
（4）在SPJ表的sno（升序）,pno（升序）和jno（降序）三列上建立一个普通索引。
createindexspjonspj（snoasc,pnoasc,jnodesc）;
实验2SQL数据操作
实验目的：
1.向实验1建立的表中添加数据（元组），掌握INSERT语句的用法；
2.修改基本表中的数据，掌握UPDATE语句的用法；
3.删除基本表中的数据，掌握DELETE语句的用法；
4.体会数据完整性约束的作用，加深对数据完整性及其约束的理解。
实验内容：
1.将教材P82表中的数据添加到数据库studentdb中。
usestudentdb
go
INSERTintostudent（sno,sname,ssex,sage,sdept）values（,'李勇','男',20,'CS'）;
INSERTintostudent（sno,sname,ssex,sage,sdept）values（,'刘晨','女',19,'CS'）;
INSERTintostudent（sno,sname,ssex,sage,sdept）values（,'王敏','女',18,'MA'）;
INSERTintostudent（sno,sname,ssex,sage,sdept）values（,'张立','男',29,'IS'）;
usestudentdb
go
INSERTintocourse（cno,cname,cpno,ccredit）values（1,'数据库',5,4）;
INSERTintocourse（cno,cname,cpno,ccredit）values（2,'数学',null,2）;
INSERTintocourse（cno,cname,cpno,ccredit）values（3,'信息系统',1,4）;
INSERTintocourse（cno,cname,cpno,ccredit）values（4,'操作系统',6,3）;
INSERTintocourse（cno,cname,cpno,ccredit）values（5,'数据结构',7,4）;
INSERTintocourse（cno,cname,cpno,ccredit）values（6,'数据处理',null,2）;
INSERTintocourse（cno,cname,cpno,ccredit）values（7,'PASCAL语言',6,4）;
usestudentdb
go
INSERTintosc（sno,cno,grade）values（,1,92）;
INSERTintosc（sno,cno,grade）values（,2,85）;
INSERTintosc（sno,cno,grade）values（,3,88）;
INSERTintosc（sno,cno,grade）values（,2,90）;
INSERTintosc（sno,cno,grade）values（,3,80）;
2.将教材P75表中的数据添加到数据库spjdb中，体会执行插入操作时检查实体完整性规则、参照完整性规则和用户定义完整性规则的效果。
usespjdb
go
insertintoS（SNO,SNAME,STATUS,CITY）VALUES（'S1','精益',20,'天津'）;
insertintoS（SNO,SNAME,STATUS,CITY）VALUES（'S2','盛锡',10,'北京'）;
insertintoS（SNO,SNAME,STATUS,CITY）VALUES（'S3','东方红',30,'北京'）;
insertintoS（SNO,SNAME,STATUS,CITY）VALUES（'S4','丰泰盛',20,'天津'）;
insertintoS（SNO,SNAME,STATUS,CITY）VALUES（'S5','为民',30,'上海'）;
usespjdb
go
insertintoP（PNO,PNAME,COLOR,WEIGHT）VALUES（'P1','螺母','红',12）;
insertintoP（PNO,PNAME,COLOR,WEIGHT）VALUES（'P2','螺栓','绿',17）;
insertintoP（PNO,PNAME,COLOR,WEIGHT）VALUES（'P3','螺丝刀','蓝',14）;
insertintoP（PNO,PNAME,COLOR,WEIGHT）VALUES（'P4','螺丝刀','红',14）;
insertintoP（PNO,PNAME,COLOR,WEIGHT）VALUES（'P5','凸轮','蓝',40）;
insertintoP（PNO,PNAME,COLOR,WEIGHT）VALUES（'P6','齿轮','红',30）;
usespjdb
go
insertintoJ（jno,jname,city）values（'J1','三建','北京'）;
insertintoJ（jno,jname,city）values（'J2','一汽','长春'）;
insertintoJ（jno,jname,city）values（'J3','弹簧厂','天津'）;
insertintoJ（jno,jname,city）values（'J4','造船厂','天津'）;
insertintoJ（jno,jname,city）values（'J5','机车厂','唐山'）;
insertintoJ（jno,jname,city）values（'J6','无线电厂','常州'）;
insertintoJ（jno,jname,city）values（'J7','半导体厂','南京'）;
usespjdb
go
insertintoSPJ（sno,pno,jno,qty）values（'S1','P1','J1',200）;
insertintoSPJ（sno,pno,jno,qty）values（'S1','P1','J3',100）;
insertintoSPJ（sno,pno,jno,qty）values（'S1','P1','J4',700）;
insertintoSPJ（sno,pno,jno,qty）values（'S1','P2','J2',100）;
insertintoSPJ（sno,pno,jno,qty）values（'S2','P3','J1',400）;
insertintoSPJ（sno,pno,jno,qty）values（'S2','P3','J2',200）;
insertintoSPJ（sno,pno,jno,qty）values（'S2','P3','J4',500）;
insertintoSPJ（sno,pno,jno,qty）values（'S2','P3','J5',400）;
insertintoSPJ（sno,pno,jno,qty）values（'S2','P5','J1',400）;
insertintoSPJ（sno,pno,jno,qty）values（'S2','P5','J2',100）;
insertintoSPJ（sno,pno,jno,qty）values（'S3','P1','J1',200）;
insertintoSPJ（sno,pno,jno,qty）values（'S3','P3','J1',200）;
insertintoSPJ（sno,pno,jno,qty）values（'S4','P5','J1',100）;
insertintoSPJ（sno,pno,jno,qty）values（'S4','P6','J3',300）;
insertintoSPJ（sno,pno,jno,qty）values（'S4','P6','J4',200）;
insertintoSPJ（sno,pno,jno,qty）values（'S5','P2','J4',100）;
insertintoSPJ（sno,pno,jno,qty）values（'S5','P3','J1',200）;
insertintoSPJ（sno,pno,jno,qty）values（'S5','P6','J2',200）;
insertintoSPJ（sno,pno,jno,qty）values（'S5','P6','J4',500）;
（1）实体完整性规则
usespjdb
go
insertintoS（SNO,SNAME,STATUS,CITY）VALUES（'S1','WWW',20,'WWW'）;//S1重复
（2）参照完整性规则
usespjdb
go
insertintoSPJ（sno,pno,jno,qty）values（'S7','P6','J4',500）;//S表里没有S7这条记录
（3）用户定义完整性规则
usespjdb
go
insertintoP（PNO,PNAME,COLOR,WEIGHT）VALUES（'P7','齿轮','红',-30）;//weight小于0
3.设计几个删除操作（3~5个），体会执行删除操作时检查参照完整性规则的效果。
（1）deletefromSwheresno='s1'//不能删除
（2）droptables//不能删除
（3）deletefromstudentwheresno=//不能删除
4.设计一组更新操作（3~5个），体会执行更新操作时检查实体完整性规则、参照完整性规则和用户定义完整性规则的效果。
（1）insertintoSPJ（sno,pno,jno,qty）
values（null,'P6','J4',500）;//sno不能为空
（2）insertintoSPJ（sno,pno,jno,qty）
values（'S7','P6','J4',500）;//S表里没有S7这条记录
（3）insertintoP（PNO,PNAME,COLOR,WEIGHT）
VALUES（'P7','齿轮','红',-30）;//weight小于0
实验3SQL数据查询
实验目的：
1.熟练掌握SELECT语句，能够运用该语句完成各种查询；
2.观察查询结果,体会SELECT语句实际应用；
3要求学生能够在查询分析器中使用SELECT语句进行单表查询和多表查询。
实验内容：
基于实验1和实验2中建立的表，完成如下查询：
（1和2选一题，第3题必做）
1.参照教材P75表中的数据，完成P74习题5中
（1）~（5）的查询。
（1）
selectdistinctsno
fromspj
wherejno='J1'
（2）
selectdistinctsno
fromspj
wherejno='J1'andpno='P1'
（3）
selectsno
fromspj
wherespj.jno='J1'ANDPNO
IN（
SELECTPNO
fromp
wherecolor='红'）;
（4）
selectJno
fromspj
wherejnonotin（
selectjno
fromspj,p,s
wheres.city='天津'
ANDCOLOR='红'
ANDS.SNO=SPJ.SNO
ANDP.PNO=SPJ.PNO）；
（5）
selectDISTINCTjno
fromspjX
wherenotexists
（
select*
fromspjY
wheresno='S1'AND
NOTEXISTS（
SELECT*
FROMSPJASZ
WHEREX.JNO=Z.JNOAND
Y.PNO=Z.PNOAND
sno='S1'
））
2.参照教材P75表中的数据，完成P127习题5中
（1）~（11）的查询。
3.参照教材P82表中的数据，完成以下查询：
（1）查询course表中所有学分大于2并且序号小于5的课程信息。
selectcname
fromcourse
wherecno<5andccredit>2
（2）查询学分介于2和8之间的课程的信息。
select*
fromcourse
whereccredit>2andccredit<8
（3）从course表中查询出课程号为1或4或7的课程的课程号、课程名称、以及学分。
方法一：
selectcno,cname,ccredit
fromcourse
wherecno='1'
union
selectcno,cname,ccredit
fromcourse
wherecno='4'
union
selectcno,cname,ccredit
fromcourse
wherecno='7'
方法二：
selectcno,cname,ccredit
fromcourse
wherecno='1'orcno='4'orcno='7'
（4）从course表中查询学分大于等于3的课程信息，并按学分升序排列。
select*
fromcourse
whereccredit>=3
orderbycc
升级会员 