数据库 数据操作语言DML实验.docx

1、数据库 数据操作语言DML实验数据库系统原理实验报告实验名称：数据操作语言DML实验任课教师：*学号：*完成日期：2012.10.3一、实验目的1、建立基本表并进行DML操作。2、数据查询：单表查询、连接查询、嵌套查询、集合查询和统计等功能。3、数据更新：增加、删除、查询和修改功能。二、实验过程与分析 1、用Create、Insert语句创建基本表S，P，J和SPJ (1)、基本表S create table S( SNO char(3) primary key, SNAME char(10), STATUS char(2), CITY char(10);(2)、基本表Pcreate tabl

2、e P(PNO char(3) primary key, PNAME char(10), COLOR char(4), WEIGHT int);(3)、基本表Jcreate table J(JNO char(3) primary key, JNAME char(10), CITY char(10); (4)、基本表SPJ create table SPJ(SNO char(3), PNO char(3), JNO char(3), QTY int, primary key (SNO,PNO,JNO), foreign key (SNO) references S (SNO), foreign

3、key (PNO) references P (PNO), foreign key (JNO) references J (JNO); 2、插入数据 (1)、向S表插入数据insert into S values(S1,精益,20,天津);insert into S values(S2,盛锡,10,北京);insert into S values(S3,东方红,30,北京);insert into S values(S4,丰泰盛,30,天津);insert into S values(S5,为民,30,上海);(2)、向P表插入数据insert into P values(P1,螺母,红,12

4、);insert into P values(P2,螺楦,绿,17);insert into P values(P3,螺丝刀,蓝,14);insert into P values(P4,螺丝刀,红,14);insert into P values(P5,凸轮,蓝,40);insert into P values(P6,齿轮,红,30);(3)、向J表插入数据insert into J values(J1,三建,北京);insert into J values(J2,一汽,长春);insert into J values(J3,弹簧厂,天津);insert into J values(J4,造船

5、厂,天津);insert into J values(J5,机车厂,唐山);insert into J values(J6,无线电厂,常州);insert into J values(J7,半导体厂,南京);(4)、向SPJ表插入数据insert into SPJ values(S1,P1,J1,200);insert into SPJ values(S1,P1,J3,100);insert into SPJ values(S1,P1,J4,700);insert into SPJ values(S1,P2,J1,100);insert into SPJ values(S2,P3,J1,400

6、);insert into SPJ values(S2,P2,J4,200);insert into SPJ values(S2,P3,J4,500);insert into SPJ values(S2,P3,J5,400);insert into SPJ values(S2,P5,J1,400);insert into SPJ values(S2,P5,J2,100);insert into SPJ values(S3,P1,J1,200);insert into SPJ values(S3,P3,J1,200);insert into SPJ values(S4,P2,J1,100);in

7、sert into SPJ values(S4,P2,J3,300);insert into SPJ values(S4,P6,J4,200);insert into SPJ values(S5,P2,J4,100);insert into SPJ values(S5,P3,J1,200);insert into SPJ values(S5,P6,J2,200);insert into SPJ values(S5,P6,J4,500);运行结果：Oracle8 Enterprise Edition Release 8.0.5.0.0 - ProductionPL/SQL Release 8.0

8、.5.0.0 - Production *SQLWKS create table S 2 ( 3 SNO char(3) primary key, 4 SNAME char(10), 5 STATUS char(2), 6 CITY char(10) 7 );语句已处理。SQLWKS SQLWKS create table P 2 ( 3 PNO char(3) primary key, 4 PNAME char(10), 5 COLOR char(4), 6 WEIGHT int 7 );语句已处理。SQLWKS SQLWKS create table J 2 ( 3 JNO char(3)

9、 primary key, 4 JNAME char(10), 5 CITY char(10) 6 );语句已处理。SQLWKS SQLWKS create table SPJ 2 ( 3 SNO char(3), 4 PNO char(3), 5 JNO char(3), 6 QTY int, 7 primary key (SNO,PNO,JNO), 8 foreign key (SNO) references S (SNO), 9 foreign key (PNO) references P (PNO), 10 foreign key (JNO) references J (JNO) 11

10、 );语句已处理。SQLWKS SQLWKS insert into S values(S1,精益,20,天津);处理了 1 行。SQLWKS insert into S values(S2,盛锡,10,北京);处理了 1 行。SQLWKS insert into S values(S3,东方红,30,北京);处理了 1 行。SQLWKS insert into S values(S4,丰泰盛,30,天津);处理了 1 行。SQLWKS insert into S values(S5,为民,30,上海);处理了 1 行。SQLWKS SQLWKS insert into P values(P1

11、,螺母,红,12);处理了 1 行。SQLWKS insert into P values(P2,螺楦,绿,17);处理了 1 行。SQLWKS insert into P values(P3,螺丝刀,蓝,14);处理了 1 行。SQLWKS insert into P values(P4,螺丝刀,红,14);处理了 1 行。SQLWKS insert into P values(P5,凸轮,蓝,40);处理了 1 行。SQLWKS insert into P values(P6,齿轮,红,30);处理了 1 行。SQLWKS SQLWKS insert into J values(J1,三建

12、,北京);处理了 1 行。SQLWKS insert into J values(J2,一汽,长春);处理了 1 行。SQLWKS insert into J values(J3,弹簧厂,天津);处理了 1 行。SQLWKS insert into J values(J4,造船厂,天津);处理了 1 行。SQLWKS insert into J values(J5,机车厂,唐山);处理了 1 行。SQLWKS insert into J values(J6,无线电厂,常州);处理了 1 行。SQLWKS insert into J values(J7,半导体厂,南京);处理了 1 行。SQLW

13、KS SQLWKS insert into SPJ values(S1,P1,J1,200);处理了 1 行。SQLWKS insert into SPJ values(S1,P1,J3,100);处理了 1 行。SQLWKS insert into SPJ values(S1,P1,J4,700);处理了 1 行。SQLWKS insert into SPJ values(S1,P2,J1,100);处理了 1 行。SQLWKS insert into SPJ values(S2,P3,J1,400);处理了 1 行。SQLWKS insert into SPJ values(S2,P2,J

14、4,200);处理了 1 行。SQLWKS insert into SPJ values(S2,P3,J4,500);处理了 1 行。SQLWKS insert into SPJ values(S2,P3,J5,400);处理了 1 行。SQLWKS insert into SPJ values(S2,P5,J1,400);处理了 1 行。SQLWKS insert into SPJ values(S2,P5,J2,100);处理了 1 行。SQLWKS insert into SPJ values(S3,P1,J1,200);处理了 1 行。SQLWKS insert into SPJ va

15、lues(S3,P3,J1,200);处理了 1 行。SQLWKS insert into SPJ values(S4,P2,J1,100);处理了 1 行。SQLWKS insert into SPJ values(S4,P2,J3,300);处理了 1 行。SQLWKS insert into SPJ values(S4,P6,J4,200);处理了 1 行。SQLWKS insert into SPJ values(S5,P2,J4,100);处理了 1 行。SQLWKS insert into SPJ values(S5,P3,J1,200);处理了 1 行。SQLWKS insert

16、 into SPJ values(S5,P6,J2,200);处理了 1 行。SQLWKS insert into SPJ values(S5,P6,J4,500);处理了 1 行。3、用Select查询数据（1）、求供应工程J1零件的供应商号SNO;运行结果：SQLWKS select SNO from SPJ 2 where SPJ.JNO=J1; SNO-S1 S2 S3 S4 S5 已选择5行。（2）、求供应工程J1零件P1的供应商号SNO； 运行结果：SQLWKS select distinct SNO from SPJ 2 where SPJ.JNO=J1 and SPJ.PNO=

17、P1; SNO-S1 S3 已选择2行。（3）、求供应工程J1零件为红色的供应商号SNO;运行结果：SQLWKS select distinct SNO from SPJ ,P 2 where SPJ.JNO=J1 and P.COLOR=红 and P.PNO=SPJ.PNO; SNO-S1 S3 已选择2行。（4）、求没有使用天津供应商生产的红色零件的工程号JNO; SQLWKS select jno 2 from spj 3 where jno not in4(select jno5 from spj,p,s 6 where city=天津 and color=红 and 7 s.sno

18、=spj.sno and p,pno=spj,pno) 8JNO- J2 J5 已选择2行。（5）、求至少用了供应商S1所供应的全部零件的工程号JNO;SQLWKS select distinct pno 2 from spj 3 where sno =S1 4 from s JNO-P1P2已选择2行。SQLWKS select distinct jno 2 from spj 3 where pno =P1 and jno in 4 (select jno 5 from spj6where pno=P27 JNO-J1 J3 J4 已选择3行。（6）、找出所有供应商的姓名和所在城市； SQL

19、WKS select sname,city 2 from s;SNAME CITY - -精益 天津 盛锡 北京 东方红 北京 丰泰盛 天津 为民 上海 已选择5行。（7）、找出所有零件的名称、颜色、重量； SQLWKS select pname,color,weight 2 from p;PNAME COLO WEIGHT - - -螺母 红 12螺楦 绿 17螺丝刀 蓝 14螺丝刀 红 14凸轮 蓝 40齿轮 红 30已选择6行。（8）、找出使用供应商S1所供应零件的工程号；SQLWKS select distinct JNO 2 from SPJ 3 where SNO=S1;JNO-J

20、1 J3 J4 已选择3行。（9）、找出工程项目J2使用的各种临近的名称及其重量 ；SQLWKS select PNAME,QTY 2 from P,SPJ 3 where SPJ.JNO=J2 and SPJ.PNO=P.PNO;PNAME QTY - -凸轮 100齿轮 200已选择2行。（10）、找出上海厂商供应的所以零件的零件号；SQLWKS select distinct PNO 2 from SPJ,S 3 where S.CITY=上海 and S.SNO=SPJ.SNO 4 and SPJ.JNO=J.JNO 5JNO-P2P3P6已选择3行。（11）、找出使用上海产的零件的工

21、程名称；SQLWKS select distinct JNAME 2 from S,J,SPJ 3 where SPJ.JNO=J.JNO and SPJ.SNO=(select SNO 4 from S 5 where S.CITY=上海) 6 JNAME -三建 一汽 造船厂 已选择3行。（12）、找出没有使用天津产的零件的工程号；SQLWKS select distinct JNO 2 fromS,J,SPJ 3 where JNO 4 (select jno 5 from S.SPJ 6 where S,CITY=天津 and S.SNO=SPJ.SNO) 7JNO -J2J5 已选择

22、2行。（13）、列出包含SNo，SNAME，PNO，PNAME，JNO，JNAME，QTY属性的清单； （14）、统计各工程项目所用不同零件的数量； （15）、统计各供应商供应的各种零件数量； 4、用Insert、Delete和Update语句实现如下数据更新 （1）、将全部红色零件的颜色改为蓝色； update pset color=蓝where color=红；select * from p； （2）、将工程J3的城市改为上海； update jset city=上海where j.jno=J3；select * from j； （3）、有S5供给J4的零件P6改为由S3供应； updat

23、e spjset sno=S3where spj.sno=S5and pno=P6and jno=J4select * from spj （4）、从供应商关系中删除S2的元组，并从供应关系中删除相应元组； deletefrom spjwhere sno=S2;delete from swhere sno=S2;select * from s;select *from spj; （5）、请将S2向工程项目J6供应200个P4零件的信息加入到供应关系； insert into s values(S2,西风,15,北京);insert into spj values(S2,P4,J6,200);se

24、lect * from s;select *from spj; （6）、请将S6向项目J8供应500个P7零件的信息加入到供应关系； insert into s values(S6,中华,20,上海);insert into p values(P7,扳手,黑,15);insert into j values(J8,电力厂,广州);insert into spj values(S6,P7,J8,600); 5、用Insert、Delete和Update语句实现如下数据更新 create table Student(sno char(5) primary key,sname varchar(10)

25、 unique,ssex char(4) not null,sage number(3) default 0,sdept varchar(10);create table Course(cno char(5) primary key,cname varchar2(15) not null,cpno char(5),ccredit number(4),foreign key (cpno) references Course(cno);create table SC(sno char(5),cno char(5),grade number(3),primary key (sno,cno),fore

26、ign key (sno) references Student(sno),foreign key (cno) references Course(cno);alter table Student add jiguan varchar2(20);alter table Student add shijian date;alter table Course modify cno char(4);alter table Course modify cpno char(4);alter table SC modify cno char(4);alter table Student drop uniq

27、ue(sname);create unique index Stusname on Student(sname);create unique index Coucname on Course(cname);create index Sgrade on SC(sno asc,grade desc);insert into Student values(95001,李勇,男20,cs,北京,27-2月-05);insert into Student values(95002,张三,女,21,cs,天津,27-2月-05);insert into Student values(95003,李四,女,20,cs,上海,27-2月-05);insert into St