数据库 1.docx

1、数据库 1 实验一create database StuDB-在SQL Server2000中建立一个StuDB数据库：（请先在D盘下创建DB文件夹）-有一个数据文件：逻辑名为StuData，文件名为“d:dbStuDat.mdf”，文件初始大小为MB，文件的最大大小不受限制，文件的增长率为MB；on primary(name=StuData,filename=d:dbStuDat.mdf,size=3,filegrowth=2)-有一个日志文件，逻辑名为StuLog，文件名为“d:dbStuLog.ldf”，文件初始大小为MB，文件的最大大小为MB，文件的增长率为%log on(name=S

2、tuLog,filename=d:dbStuLog.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；use StuDBcreate table s(class char(5),sno char(2),name char(10) unique,s

3、ex char(2),age int,primary key(class,sno)-(2) 插入一个记录班号李明，男性，岁；insert into s(CLASS,SNO, NAME, SEX, AGE)values(95031,25,李明,男,21);-再插入一个记录班号王丽，女性，岁；insert into s(CLASS,SNO, NAME, SEX, AGE)values(95101,10,王丽,女,20);-(3) 插入班座号为，名为郑和的学生记录；insert into s(CLASS,SNO, NAME)values(95031,30,郑和);-(4) 将年龄的数据类型改为sma

4、llint；alter table s alter column age smallint; -(5) 向S表添加“入学时间（comedate）”列，其数据类型为日期型（datetime）；alter table s add comedate datetime;-(6) 对表S，按年龄降序建索引（索引名为inxage）；create unique index inxage on s(age);-(7) 删除S表的inxage索引；drop index s.inxage;-(8) 删除S表；drop table s;-(9) 按照数据库系统概论(第四版)P56页的学生课程数据库创建STUDENT

5、、COURSE和SC三张表，每一张表都必须有主码约束，合理使用列级完整性约束和表级完整性。并输入相关数据。create table student (sno char(9)primary key,sname char(20) unique,ssex char(2),sage smallint,sdept char(20);create table course(cno char(4) primary key,cname char(40),cpno char(4),ccredit smallint,foreign key (cpno) references course(cno);create

6、table sc(sno char(9),cno char(4),grade smallint,primary key(sno,cno),foreign key (sno) references student(sno),foreign key (cno) references course(cno);insertinto student(sno,sname,ssex,sage,sdept)values(95001,李勇,男,20,CS);insertinto student(sno,sname,ssex,sage,sdept)values(95002,刘晨,女,19,IS);insertin

7、to student(sno,sname,ssex,sage,sdept)values(95003,王敏,女,18,MA);insertinto student(sno,sname,ssex,sage,sdept)values(95004,张立,男,19,IS);insertinto course(cno,cname,cpno,ccredit)values(2,数学,NULL,2);insertinto course(cno,cname,cpno,ccredit)values(6,数据处理,NULL,2);insertinto course(cno,cname,cpno,ccredit)val

8、ues(4,操作系统,6,3);insertinto course(cno,cname,cpno,ccredit)values(7,PASCAL语言,6,4);insertinto course(cno,cname,cpno,ccredit)values(5,数据结构,7,4);insertinto course(cno,cname,cpno,ccredit)values(1,数据库,5,4);insertinto course(cno,cname,cpno,ccredit)values(3,信息系统,1,4);insertinto sc(sno,cno,grade)values(95001,

9、1,92);insertinto sc(sno,cno,grade)values(95001,2,85);insertinto sc(sno,cno,grade)values(95001,3,88);insertinto sc(sno,cno,grade)values(95002,2,90);insertinto sc(sno,cno,grade)values(95002,3,80);-按照数据库系统概论(第四版)P74页习题的SPJ数据库。创建SPJ数据库，create database spj-有一个数据文件：逻辑名为StuData，文件名为“d:dbStuDat.mdf”，文件初始大小为

10、MB，文件的最大大小不受限制，文件的增长率为MB；on primary(name=SPJData,filename=d:dbSPJDat.maf,size=3,filegrowth=2)-有一个日志文件，逻辑名为StuLog，文件名为“d:dbStuLog.ldf”，文件初始大小为MB，文件的最大大小为MB，文件的增长率为%log on(name=SPJLog,filename=d:dbSPJLog.ldf,size=1,maxsize=10,filegrowth=10%)-并在其中创建S、P、J和SPJ四张表。-每一张表都必须有主码约束，合理使用列级完整性约束和表级完整性use spjcre

11、ate table s (sno char(9)primary key,sname char(20) unique,status int,city char(20);create table p (pno char(9)primary key,pname char(20),color char(20),weight int);create table j (jno char(9)primary key,jname char(20) unique,city char(20);create table spj(sno char(9),pno char(9),jno char(9),qty int,

12、primary key(sno,pno,jno),foreign key (sno) references s(sno),foreign key (pno) references p(pno),foreign key (jno) references j(jno);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,北京

13、);insert into s(sno,sname,status,city)values(S4,丰泰盛,20,天津);insert into s(sno,sname,status,city)values(S5,为民,30,上海);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(pn

14、o,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);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 i

15、nto 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,半导体厂,南京);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

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

17、,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,q

18、ty)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); 实验二-(一) 按照

19、数据库系统概论P82页中的学生课程数据库及数据实现以下查询：use StuDB-1.查询所有课程的课号，课程名。select cno 课号,cname 课程名from course;-2.查询计算机系年龄不到岁的所有男生的姓名和出生年份。select sname 姓名,(2011-sage) 出生年份from studentwhere sdept=csand sage3;-(三)按照数据库系统概论P82页中的学生课程数据库和P75页中的SPJ数据库完成以下查询-(1)查询使用红色零件的工程名称。use spjselect distinct jname 工程名称from spj,j,pwhere

20、 spj.jno=j.jno and spj.pno=p.pno and color=红;-(2)查询每个工程的信息及相应的供应信息（包括列出尚未被供应零件的那些工程）。select j.jno,jname,city,sno,pno,qtyfrom j left outer join spj on(j.jno=spj.jno);-(3)查询供应工程J1红色零件的供应商号SNO，请用两种方法实现。-方法一select sno 供应商号from spj,pwhere jno=j1and spj.pno=p.pno and color=红;-方法二select sno 供应商号from spjwhe

21、re jno=j1 and pno in (select pno from p where color=红);-(4)求供应工程J1零件的供应商的完整信息。select *from swhere sno in (select sno from spj where jno=j1);-(5)查询使用北京供应商供应零件的工程信息。select *from jwhere jno in (select jno from spj,s where spj.sno=s.sno and s.city=北京);-(6)查询选修号课程且成绩在85分以上的所有学生。use StuDBselect *from scwh

22、ere cno=3 and grade85;-(7)查询先行课的学分为4的课程信息。select *from course first,course secondwhere first.cpno=o and second.ccredit=4;-(8)查询课程与其间接先行课的名称。select ame,amefrom course first,course secondwhere first.cpno=o-(9)查询其他系中比计算机科学系所有学生年龄都小- 的学生完整信息，用两种方法实现。-方法一：select *from studentwhere sageall (select sage fr

23、om student where sdept=cs) and sdeptcs;-方法二：select *from studentwhere sage (select min(sage) from student where sdept=cs) and sdeptcs;-(10)查询其他系中比计算机科学系某一学生年龄大- 的学生姓名与年龄，用两种方法实现。-方法一：select sname,sagefrom studentwhere sageany(select sage from student where sdept=cs) and sdeptcs;-方法二：select sname,sag

24、efrom studentwhere sage (select min(sage) from student where sdept=cs) and sdeptcs;-(11)查询至少用了供应商S1所供应的所有零件- 的工程号JNO，用带EXISTS谓词的子查询实现。use spjselect jnofrom spj spjxwhere not exists (select * from spj spjy where spjy.sno=s1and not exists (select * from spj spjz where spjz.sno=spjx.sno and spjz.pno=sp

25、jy.pno);-(12)查询使用了全部零件的工程号JNO，- 用带EXISTS谓词的子查询实现。select jnofrom spj where not exists (select * from spj group by jno having count(*)6);-查询所有使用了P3零件的工程号JNO，-用带EXISTS谓词的子查询实现。select distinct jnofrom jwhere exists (select * from spj where jno=j.jno and pno=p3); 实验三-（一）数据更新-插入一个学生：张红，女，信息系，岁use StuDBins

26、ertinto student(sno,sname,ssex,sage,sdept)values(200215135,张红,女,20,IS);-插入一个选课录：，成绩未定insertinto sc(sno,cno)values(200215135,1);-将计算机系所有学生的年龄加岁update studentset sage=sage+1where sdept=cs;-删除所有学生的选课记录deletefrom scwhere sno=200215130;-删除所有姓张的同学的信息deletefrom studentwhere sname=张%;-请自行设计案例对学生课程数据库的数据更新，并观察是否有违反数据的完整性