数据库 1.docx
《数据库 1.docx》由会员分享，可在线阅读，更多相关《数据库 1.docx（19页珍藏版）》请在冰豆网上搜索。
数据库1
实验一
createdatabaseStuDB
--在SQLServer2000中建立一个StuDB数据库：
（请先在D盘下创建DB文件夹）
--有一个数据文件：
逻辑名为StuData，文件名为“d:
\db\StuDat.mdf”，文件初始大小为MB，文件的最大大小不受限制，文件的增长率为MB；
onprimary
（name=StuData,
filename='d:
\db\StuDat.mdf',
size=3,
filegrowth=2）
--有一个日志文件，逻辑名为StuLog，文件名为“d:
\db\StuLog.ldf”，文件初始大小为MB，文件的最大大小为MB，文件的增长率为%
logon
（
name=StuLog,
filename='d:
\db\StuLog.ldf',
size=1,
maxsize=10,
filegrowth=10%
）
--设有如下关系表S：
S（CLASS,SNO,NAME,SEX,AGE），
--其中：
CLASS为班号，char（5）；SNO为座号，char
（2）；NAME为姓名，char（10），设姓名的取值唯一；SEX为性别，char
（2）；AGE为年龄，int，表中主码为班号+座号。
写出实现下列功能的SQL语句。
--
（1）创建表S；
useStuDB
createtables
（
classchar（5）,
snochar
（2）,
namechar（10）unique,
sexchar
（2）,
ageint,
primarykey（class,sno）
）
--
（2）插入一个记录班号李明，男性，岁；
insert
intos（CLASS,SNO,NAME,SEX,AGE）
values（'95031','25','李明','男',21）;
--再插入一个记录班号王丽，女性，岁；
insert
intos（CLASS,SNO,NAME,SEX,AGE）
values（'95101','10','王丽','女',20）;
--（3）插入班座号为，名为郑和的学生记录；
insert
intos（CLASS,SNO,NAME）
values（'95031','30','郑和'）;
--（4）将年龄的数据类型改为smallint；
altertablesaltercolumnagesmallint;
--（5）向S表添加“入学时间（comedate）”列，其数据类型为日期型（datetime）；
altertablesaddcomedatedatetime;
--（6）对表S，按年龄降序建索引（索引名为inxage）；
createuniqueindexinxageons（age）;
--（7）删除S表的inxage索引；
dropindexs.inxage;
--（8）删除S表；
droptables;
--（9）按照《数据库系统概论》（第四版）P56页的学生－课程数据库创建STUDENT、COURSE和SC三张表，每一张表都必须有主码约束，合理使用列级完整性约束和表级完整性。
并输入相关数据。
createtablestudent
（
snochar（9）primarykey,
snamechar（20）unique,
ssexchar
（2）,
sagesmallint,
sdeptchar（20）
）;
createtablecourse
（
cnochar（4）primarykey,
cnamechar（40）,
cpnochar（4）,
ccreditsmallint,
foreignkey（cpno）referencescourse（cno）
）;
createtablesc
（
snochar（9）,
cnochar（4）,
gradesmallint,
primarykey（sno,cno）,
foreignkey（sno）referencesstudent（sno）,
foreignkey（cno）referencescourse（cno）
）;
insert
intostudent（sno,sname,ssex,sage,sdept）
values（'95001','李勇','男',20,'CS'）;
insert
intostudent（sno,sname,ssex,sage,sdept）
values（'95002','刘晨','女',19,'IS'）;
insert
intostudent（sno,sname,ssex,sage,sdept）
values（'95003','王敏','女',18,'MA'）;
insert
intostudent（sno,sname,ssex,sage,sdept）
values（'95004','张立','男',19,'IS'）;
insert
intocourse（cno,cname,cpno,ccredit）
values（'2','数学',NULL,2）;
insert
intocourse（cno,cname,cpno,ccredit）
values（'6','数据处理',NULL,2）;
insert
intocourse（cno,cname,cpno,ccredit）
values（'4','操作系统','6',3）;
insert
intocourse（cno,cname,cpno,ccredit）
values（'7','PASCAL语言','6',4）;
insert
intocourse（cno,cname,cpno,ccredit）
values（'5','数据结构','7',4）;
insert
intocourse（cno,cname,cpno,ccredit）
values（'1','数据库','5',4）;
insert
intocourse（cno,cname,cpno,ccredit）
values（'3','信息系统','1',4）;
insert
intosc（sno,cno,grade）
values（'95001','1',92）;
insert
intosc（sno,cno,grade）
values（'95001','2',85）;
insert
intosc（sno,cno,grade）
values（'95001','3',88）;
insert
intosc（sno,cno,grade）
values（'95002','2',90）;
insert
intosc（sno,cno,grade）
values（'95002','3',80）;
--按照《数据库系统概论》（第四版）P74页习题的SPJ数据库。
创建SPJ数据库，
createdatabasespj
--有一个数据文件：
逻辑名为StuData，文件名为“d:
\db\StuDat.mdf”，文件初始大小为MB，文件的最大大小不受限制，文件的增长率为MB；
onprimary
（name=SPJData,
filename='d:
\db\SPJDat.maf',
size=3,
filegrowth=2）
--有一个日志文件，逻辑名为StuLog，文件名为“d:
\db\StuLog.ldf”，文件初始大小为MB，文件的最大大小为MB，文件的增长率为%
logon
（
name=SPJLog,
filename='d:
\db\SPJLog.ldf',
size=1,
maxsize=10,
filegrowth=10%
）
--并在其中创建S、P、J和SPJ四张表。
--每一张表都必须有主码约束，合理使用列级完整性约束和表级完整性
usespj
createtables
（
snochar（9）primarykey,
snamechar（20）unique,
statusint,
citychar（20）
）;
createtablep
（
pnochar（9）primarykey,
pnamechar（20）,
colorchar（20）,
weightint
）;
createtablej
（
jnochar（9）primarykey,
jnamechar（20）unique,
citychar（20）
）;
createtablespj
（
snochar（9）,
pnochar（9）,
jnochar（9）,
qtyint,
primarykey（sno,pno,jno）,
foreignkey（sno）referencess（sno）,
foreignkey（pno）referencesp（pno）,
foreignkey（jno）referencesj（jno）
）;
insert
intos（sno,sname,status,city）
values（'S1','精益',20,'天津'）;
insert
intos（sno,sname,status,city）
values（'S2','盛锡',10,'北京'）;
insert
intos（sno,sname,status,city）
values（'S3','东方红',30,'北京'）;
insert
intos（sno,sname,status,city）
values（'S4','丰泰盛',20,'天津'）;
insert
intos（sno,sname,status,city）
values（'S5','为民',30,'上海'）;
insert
intop（pno,pname,color,weight）
values（'P1','螺母','红',12）;
insert
intop（pno,pname,color,weight）
values（'P2','螺栓','绿',17）;
insert
intop（pno,pname,color,weight）
values（'P3','螺丝刀','蓝',14）;
insert
intop（pno,pname,color,weight）
values（'P4','螺丝刀','红',14）;
insert
intop（pno,pname,color,weight）
values（'P5','凸轮','蓝',40）;
insert
intop（pno,pname,color,weight）
values（'P6','齿轮','红',30）;
insert
intoj（jno,jname,city）
values（'J1','三建','北京'）;
insert
intoj（jno,jname,city）
values（'J2','一汽','长春'）;
insert
intoj（jno,jname,city）
values（'J3','弹簧厂','天津'）;
insert
intoj（jno,jname,city）
values（'J4','造船厂','天津'）;
insert
intoj（jno,jname,city）
values（'J5','机车厂','唐山'）;
insert
intoj（jno,jname,city）
values（'J6','无线电厂','常州'）;
insert
intoj（jno,jname,city）
values（'J7','半导体厂','南京'）;
insert
intospj（sno,pno,jno,qty）
values（'s1','p1','j1',200）;
insert
intospj（sno,pno,jno,qty）
values（'s1','p1','j3',100）;
insert
intospj（sno,pno,jno,qty）
values（'s1','p1','j4',700）;
insert
intospj（sno,pno,jno,qty）
values（'s1','p2','j2',100）;
insert
intospj（sno,pno,jno,qty）
values（'s2','p3','j1',400）;
insert
intospj（sno,pno,jno,qty）
values（'s2','p3','j2',200）;
insert
intospj（sno,pno,jno,qty）
values（'s2','p3','j4',500）;
insert
intospj（sno,pno,jno,qty）
values（'s2','p3','j5',400）;
insert
intospj（sno,pno,jno,qty）
values（'s2','p5','j1',400）;
insert
intospj（sno,pno,jno,qty）
values（'s2','p5','j2',100）;
insert
intospj（sno,pno,jno,qty）
values（'s3','p1','j1',200）;
insert
intospj（sno,pno,jno,qty）
values（'s3','p3','j1',200）;
insert
intospj（sno,pno,jno,qty）
values（'s4','p5','j1',100）;
insert
intospj（sno,pno,jno,qty）
values（'s4','p6','j3',300）;
insert
intospj（sno,pno,jno,qty）
values（'s4','p6','j4',200）;
insert
intospj（sno,pno,jno,qty）
values（'s5','p2','j4',100）;
insert
intospj（sno,pno,jno,qty）
values（'s5','p3','j1',200）;
insert
intospj（sno,pno,jno,qty）
values（'s5','p6','j2',200）;
insert
intospj（sno,pno,jno,qty）
values（'s5','p6','j4',500）;
实验二
--
（一）按照《数据库系统概论》P82页中的学生课程数据库及数据实现以下查询：
useStuDB
--1.查询所有课程的课号，课程名。
selectcno课号,cname课程名
fromcourse;
--2.查询计算机系年龄不到岁的所有男生的姓名和出生年份。
selectsname姓名,（2011-sage）出生年份
fromstudent
wheresdept='cs'andsage<21andssex='男';
--3.查询已被学生所选课程的课程号。
selectdistinctcno课程号
fromsc;
--4.查询所有有成绩的学生学号和课程号，
--并为各列分别取相应的中文别名。
selectsno学号,cno课程号
fromsc
wheregradeisnotnull;
--
（二）按照《数据库系统概论》P75页中的SPJ数据库及数据实现以下查询：
usespj
--1.查询地点在‘北京’、‘上海’的供应商代码和供应商名字。
selectsno供应商代码,sname供应商名字
froms
wherecity='北京'orcity='上海';
--2.查询供应商名中包含‘方’这个汉字的供应商信息。
select*
froms
wheresnamelike'%方%';
--3.查询零件名以‘刀’字结尾，包含三个汉字的零件信息。
select*
fromp
wherepnamelike'__刀';
--4.查询使用了供应商S1所供应零件的工程项目数。
selectcount（*）工程项目数
fromspj
wheresno='s1';
--5.查询各个供应商所供应的零件的总数，要求对查询结果降序显示。
selectsno供应商号,sum（qty）零件总数
fromspj
groupbysno
orderbysum（qty）desc;
--6.查询供应了3个以上项目的供应商代码和供应的项目总数。
selectsno供应商代码,count（*）项目总数
fromspj
groupbysno
havingcount（*）>3;
--（三）按照《数据库系统概论》P82页中的学生课程数据库和P75页中的SPJ数据库完成以下查询
--
（1）查询使用红色零件的工程名称。
usespj
selectdistinctjname工程名称
fromspj,j,p
wherespj.jno=j.jnoandspj.pno=p.pnoandcolor='红';
--
（2）查询每个工程的信息及相应的供应信息（包括列出尚未被供应零件的那些工程）。
selectj.jno,jname,city,sno,pno,qty
fromjleftouterjoinspjon（j.jno=spj.jno）;
--（3）查询供应工程J1红色零件的供应商号SNO，请用两种方法实现。
--方法一
selectsno供应商号
fromspj,p
wherejno='j1'andspj.pno=p.pnoandcolor='红';
--方法二
selectsno供应商号
fromspj
wherejno='j1'andpnoin（selectpnofrompwherecolor='红'）;
--（4）求供应工程J1零件的供应商的完整信息。
select*
froms
wheresnoin
（selectsno
fromspj
wherejno='j1'）;
--（5）查询使用北京供应商供应零件的工程信息。
select*
fromj
wherejnoin
（selectjno
fromspj,s
wherespj.sno=s.sno
ands.city='北京'）;
--（6）查询选修号课程且成绩在85分以上的所有学生。
useStuDB
select*
fromsc
wherecno=3andgrade>85;
--（7）查询先行课的学分为4的课程信息。
select*
fromcoursefirst,coursesecond
wherefirst.cpno=o
andsecond.ccredit=4;
--（8）查询课程与其间接先行课的名称。
selectame,ame
fromcoursefirst,coursesecond
wherefirst.cpno=o
--（9）查询其他系中比计算机科学系所有学生年龄都小
--的学生完整信息，用两种方法实现。
--方法一：
select*
fromstudent
wheresage（selectsage
fromstudent
wheresdept='cs'）
andsdept<>'cs';
--方法二：
select*
fromstudent
wheresage<
（selectmin（sage）
fromstudent
wheresdept='cs'）
andsdept<>'cs';
--（10）查询其他系中比计算机科学系某一学生年龄大
--的学生姓名与年龄，用两种方法实现。
--方法一：
selectsname,sage
fromstudent
wheresage>any（selectsage
fromstudent
wheresdept='cs'）
andsdept<>'cs';
--方法二：
selectsname,sage
fromstudent
wheresage>
（selectmin（sage）
fromstudent
wheresdept='cs'）
andsdept<>'cs';
--（11）查询至少用了供应商S1所供应的所有零件
--的工程号JNO，用带EXISTS谓词的子查询实现。
usespj
selectjno
fromspjspjx
wherenotexists
（select*
fromspjspjy
wherespjy.sno='s1'and
notexists
（select*
fromspjspjz
wherespjz.sno=spjx.snoand
spjz.pno=spjy.pno））;
--（12）查询使用了全部零件的工程号JNO，
--用带EXISTS谓词的子查询实现。
selectjno
fromspj
wherenotexists
（select*
fromspj
groupbyjno
havingcount（*）<6）;
--查询所有使用了P3零件的工程号JNO，
--用带EXISTS谓词的子查询实现。
selectdistinctjno
fromj
whereexists
（select*
fromspj
wherejno=j.jno
andpno='p3'）;
实验三
--
（一）数据更新
--插入一个学生：
张红，女，，信息系，岁
useStuDB
insert
intostudent（sno,sname,ssex,sage,sdept）
values（'200215135','张红','女',20,'IS'）;
--插入一个选课录：
，，成绩未定
insert
intosc（sno,cno）
values（'200215135','1'）;
--将计算机系所有学生的年龄加岁
updatestudent
setsage=sage+1
wheresdept='cs';
--删除所有学生的选课记录
delete
fromsc
wheresno='200215130';
--删除所有姓张的同学的信息
delete
fromstudent
wheresname='张%';
--请自行设计案例对学生－课程数据库的数据更新，并观察是否有违反数据的完整性
升级会员 