实验二数据定义

1、数据库实验报告实验数据定义、实验目的1、掌握使用SQL语句创建和删除数据表，创建各种完整性约束，修改表的结构;2、掌握索引的创建和删除方法。二、实验内容1. 使用SQL语句建立4个关系(可参考课本P74习题5),如下: 供应商表 S (Sno,Sname,Status,Ctiy零件表 P (Pno,Pname,Color,Weigh) 工程项目表 J (Jno,Jname,City 供应情况表 SPJ(Sno,Pno,Jno,QTY要求：创建每个关系的主键，有外键的创建外键。S表中的Sname属性列的取值唯一P表中weight属性列的取值范围在1-50之间J表中的Jname取值不能为空并且是唯

2、一的(1)(2)(3)(4)SPJ表中QTY属性列的数据类型必须为 NUMBERcreate table ECSko varchar (10) primary k*jr, Sn额e varckar (20) imi que Status number.Ci ty varchar QO)名称筷型上-一一蚩VASCHAK2 (10)E_ vkR5O2(£0)"STAT VS. I MEERCITY VAJICHAR2 CaO)-create table FCpnc V世char1 Cl) primrr key,pnam 电 var char 吃口氏clcr V址eh址，weig

3、ht integer check (wei£ht= 1 and weightIphahe_ m 呃 VAlimfi2 VEIGJn I tNIEGER *心creta table JCJno verchiT (10) primary k«y , Jname varchar (10) not null unique. City vsrch址 CZO')也聊丸漳；丿J工»_ HAR2C10) ” rJ jTfAWIEVARCHAR2 CIO)厂I CITYVAItCHAR21£0)*啟兄*Fcreate tails SFJ CSno varckar

4、(10) Pno varcLar (10), Jus varcha (10 pty iLumberjpPncJno) foreign keyCSno) references E foreign tejCPno) refsrencts f (pno), foreign ky (Jno ) ref &renc&s J CTno).名称1类型可为空rPM lcldfi210)-rUjrao lVAllCUE2ao)-r-QTF iMEZRFSTkpP类型所有看Icreate table S (Sno varchar(IO) p rimary key.Sname varchar(20)

5、 unique,Status number,City varchar(20) create table P (pno varchar(10) p rimary key, p name varchar(20), color varchar(5).weight integer check (weight>= 1 and weight <= 50) create table J (Jno varchar(10) p rimary key ,Jname varchar(10) not null unique,City varchar(20) create table SPJ (Sno va

6、rchar(10),Pno varchar(10),Jno varchar(10),Qty number, p rimary key(Sno, Pno,Jno), foreign key(Sno) references S(Sno), foreign key (P no) references P(p no), foreign key(Jno) references J(Jno) )2. 修改表的结构用SQL语句完成以下操作:1 +(1)给S表增加Sphone和Semail两个属性列，分别用来存放供应商的联系电 话和电子信箱。名称I类型Hjy"S5g VAKCHAE2 (10) *S

7、TATUS SUHEERCITY VkECHAE2 C20)-SFHOHE VAKCHAii2 (10) *SEMAIL” alter table S add Sp hone varchar(IO) alter table S add Semail varchar(30)(2) 删除Jname属性列取值唯一的约束。attr J dropSYS_C00SU9rrin>y :“"三1 & alter table J drop constraints SYS_C005149(3)将QTY属性列的数据类型修改为Integer型。名称类型PNOJW'QTYVARCHAI1

8、2 (10) VARCHAR CIO) VARCHARZ CIO) IBTEGRJ 、 n - I 誉厂rFFalter table SPJ modify Qty integer(4)删除S表中的属性列Semail t! SHAME mCHAE£ (20)jEUra OTBZKCKT VAHAK(20)sfhohe' VAHA CIO)-厂Palter table S drop column Semail3.创建、删除索引用SQL语句在S表中的Sname属性列上建立唯一索引(升序)。 试在P表的Pname属性列上建立唯一索引，建立此索引后数据录入有(2)无问题，如果有问题，如

9、何做表中的数据才能正常录入?create unique index Sna on S(Sname asc)create unique index Pna on P(P name)，所有者名称类型卜:窗 T i PFAtjii qiLft *"lu ；S¥S_C005K6Uhiqus 剩 d列 O 前毀按廃反转存储PHAWE厂Ltablespiceuse: -PHO厂厂tablespaceuse:P芦create uni qn# index ssna on s (sname); create unique index ppna on p Cpnsme) j=e 36:36I&

10、#39;eMST耗时 d0?3 秒'2 7 4 4-001- 1- 11 1- 4 0-红绿蓝红蓝红同 1 2 3 4 5 6 F P P P F F F在P表的Pname属性列上建立唯一索弓I,建立此索引后数据录入有问题， 因为违反了唯一性约束条件，PNAME只能是唯一值。只有把唯一性约束取消, 表中的数据才能正常录入。4.参照课本中的数据，完成数据录入。SHO(SFAHES STATUS CITY |S SFHOHE (MID< 精益£C天圍-1s2盛榻10北京m3东方红30北京泌丰泰盛20天徑-为良30上海1> !iza_ 4D絹-3D 詞口 卢口 

11、4;口 Ft口 采JHPjTJ2J3j4JL jT三建-弾薔厂-造尉厂机车厂crrYjEO肛北京 长奇 天淳 天逮 唐山无线电二常坦二 ¥导1舫三一 1-7- 1 4200500400400100_ 200 '200- 一 - 一 1-3-4一2 12 4 5 1-2Y-1 A J J J J J j J J J A - Ji>«U1001 1 1 z 3-3 3 一一 3 5 5 135卫FtFPFFFPF卫上卩- 111122222 O-T 334 5 s s s s s 5 E 5 s- 5" 5- ?j3300j4200j4100jl

12、3;00j2 jl200500e4as4p6s5P2s5p3s5p£j5p&1名称1类型11被韓译pH JTABLE£011-10-15 16:09:31iiiKTABli2011-10-15 17:06:571徒jsTABLE2011-10-15 16:09142i TABLE2011-10-15 ie；09：35缓冲区大小CEE)ffher子句号出可执行文件|C: lorAcle'produgtllO. £, 色| Q输出文件 |C：and S电lAiit沪'JkJmiiii £tr 莅tc导出 IOracle 写岀 I SQ

13、L 插入 | FL/SQL Developer | 日志 | 肓压缩 n蒸容 p约束 n直接正在执行Oracle导出程降请等特完咸li.dm P实验三数据查询、实验目的掌握查询语句的使用方法，重点掌握连接查询和嵌套查询，理解查询的执行过程。二、预备知识查询语句的基本格式如下：-需要哪些列-来自于哪些表-根据什么条件-分组-筛选-排序SELECT 目标列名序列FROM 数据源WHERE 检索条件GROUP BY 分组依据列HAVING 组提取条件ORDER BY 排序依据列三、实验内容1、导入实验一和实验二中创建的数据表。2、在实验二创建的表中完成以下查询：（1）查询所有供应商所在的城市。產5Q

14、L 窗口- select distinct city from s.I|sel«ct di stinct ci ty©i1CITY 1I! 2天潼! 3上海-select distinct cityfrom s（2）查询零件重量在10-20之间（包括10和20）的零件名和颜色。select P name,color from p where weight>=10 and weightv=20select pnaine, colorfrom pP NAME£辺红绿蓝红wh电re wei gKtlO and tfeigiit=EO I螺母據栓 曙缨JL二(3)

15、查询工程项目的总个数。5 elect count QlIETIHCTitojselect count(DISTINCT jno) from j(4) 查询所有零件的平均重量。select avj(weight)from p©_ 1AVG (HEIGHT)121.1665566656667select avg(weight)from p(5)查询供应商S3供应的零件号。E电丄 6Ct pikOfrom spj*her4；|PHO Ij 1Fl1 EF3select pnofrom spj where sno='S3'(6) 查询各个供应商号及其供应了多少类零件。sele

16、ct count (EISTIKCT pnoJ from spjgroup by sno1SHO ICOUHT aiSTIMCTPKO) |1 1££2 32!4一5ss 131select sno,count(DISTINCT pno)from spj group by sno(7) 查询供应了 2类以上零件的供应商号。E,电丄ecT snofrom spjgroup ty snoLaving count OISTIHCT pno)汇|、/!smo I1 55 select sno from spj group by sno having count(DISTINCT p

17、no)>2(8) 查询零件名以“螺”字开头的零件信息。select *from pwher & pname lik蛭弥 |12 3-4 P P F- Fi WEIGHT t f12ITI1414select * from p where p name like'螺 %'（9）查询工程项目名中最后一个字为“厂”字的工程项目所在的城市。DibllWUI city from j where jnam电 like* |from jwhere spj.p no=p.pnowhere jname like'% 厂'(10) 查询给每个工程供应零件的供应商的个数

18、。select count C DISTIHCT silo)spj尹Clip by jnol11I COTfflT ODISTIHCTSMO)(1523324451select count( DISTINCT sno) from spj group by jno(11)查询供应数量在100200之间(包括100和200)的零件名称。uiTi IqtyJ左 BE spj, pfKer« spj. pno-p. pnoproup byigpinr smnfat¥)hi&twe&n 1000 and 勿00rri!©7NAWE1 sun (QTY)J肌1

19、齿鞄1200'2蛭丝刀-19003螺母 1200select p name,sum(qty) from spj,p group by p name having sum(qty) between 1000 and 2000(12)完成课本P127页第5题的前七个小题。(1)select snme ci ty£vo(n £© '丹AMEIcm II1 罕 J1 2 nIjJ 1 3东方红r -J r kJ I r4串泰盛-天j 至-5上晦-.select sname,cityfrom s(2)EC丄ect pnane, color, weiglit

20、from p41£=3456刀刀红绿嗑红蓝红12IT I14 I144030select p name,color,weight from p(3)serecT jno from spj wh&r« snc= ST上123select jno from spj where sno='S1'(4)select p. pnam&j spj. qty from p, spjwhere p.pno=spj.pnjoand spj jn(3='©FKAHE1QT¥ 1V1-looi 1i- 2003凸轮QD4齿轮 200se

21、lect p.p name,spj.qtyfrom p ,spj where p.p no=spj .pno and spj.jno='J2'(5)select distinct pno From spj wRer« sno in(select snofrom zwhere city='上海')1IIPHD 11£P33?Sselect distinct pnofrom spj where sno in (select sno from s where city-上海') select jname from j from spj wh

22、ere sno in from swhere jno in(select distinct jno(select snowhere city-上海')select jnamefrom j where jno inselect distinct jnc£rom spj where sno inCs«l«Ct ZMfrom sInhere ciW='卜海'Vi.1123JKAJJE I一汽 造船厂 三建(7)select distinct jno from spj*he" jno not in (select jno from spj

23、where sno inCs«l«ct sno from s whwi电上itg*天崖)1©iLj 11> 1select distinct jnofrom spj where jno not in(select jno from spj where sno in (select sno from s where city-天津')3、在实验一创建的表中完成以下查询:(1)查询是“选修课”的课程号、课程名。from courseselect cno,cnameselect cna# crtame from course12345e7e001人工智能0

24、2网貉褊稈003DE Desi04操ft乘蚩充005计算與M絡006JavaiS 言007輪译原理008Internet基础CMCI 匚NAMEwhere cno='002'(2)查询学号为“ 09105103”学生所学课程的课程名与学分。1234E£el«ct course,sc. gradefromscwlier e ceurse. cnc=sc. eno ud sc sn(j=* 09105103*酸CHAHE1GEAJJE11卜1人工智能TT2操作磁-II.003DB_Besi gn J|100 select ame,sc.grade from co

25、urse,sc where o=o and sc.sno='09105103'(3) 查询选修课程号为“ 002”或“ 004”的学生的学号。E电lect LUOfrom sc*here 002''gionselect iiiofrom sccn()=" 004'SBP _ 09105101 09105102 09105103 09105106 09105107 select sno from sc union select sno from sc where cno='004'(4) 查询选修了课程号为“ 002”和“ 004

26、”的学生的学号。select snc fromi 'SC vfhert eno二002" intersect select sno from sc where cne=' 004ISHO ' 1select sno from sc where cno='002' intersect select sno from sc where cno='004(5) 查询学习全部课程的学生姓名。select snamefrom studentwhere not existstselect *1from coursewhere n&t exi

27、sts(select#¥SMIE select sname from student where not exists (select * from course where not exists from sc and cno=o)(select*where sno=student.sno实验四数据更新与视图commitinsertinto spj(sno ,p no,jno,qty)、实验目的1、掌握数据更新语句的使用；2、掌握视图操作的基本方法和应用，理解基于视图的查询和数据更新操作的过 程。二、实验内容1. 在实验二创建的表中使用SQL语句完成以下操作:（1）现有一供应商，代

28、码为S9姓名为英特尔、所在城市西安，供应情况如下： 供应零件P5给工程J7数量为600,供应零件P4给工程J4数量为500,请将此 供应商的信息和供应信息插入数据库。insertinto s (snosn am a, city valuer C S9 , 英特尔Y西安)commi insertinto spj fsno, pno, jno, qt萨) valuesCS9 /fS / J76OOJ insertinto spj tsno, pno, jno, qty)J4SOOJ©ISHO1 SHAME1 STATUS |cm1藉益£0i天津2盛锯10北京3东方红30北京4丰

29、泰盛20天津530上海6英特尔西安coiniFii t|w9sz土 -WF5J*JIh w40010S2F5J210011S3FlJIZOO12E3F3JI20013E4F5jilOQ14S4P6J330015S4P6丁42001&S5F2J410017SSP3/1£00ISSSF6j2£0019S5P6J450020E9FSJ760021E9F4J4500insert into s(sno,sname,city) valuesCS9','英特尔','西安')valuesCS9',' P5','

30、J7',600)commitinsertinto spj(sno ,p no,jno,qty)valuesCS9',' P4','J4',500)commit(2)请将北京供应商的供应数量加 20update spjset qt7=q.ty+20 wh&r电 sno inelect snofrom swhere cit产'北京')1z345&78g1011121314. 115IG1718«19齒71SpHCIjTfe I QTYII FlFliP2|F3P3 jF3iF3IF5'P5!F1F3I

31、F5'P6'P6'F2IF3'F6'P6P5iP4SIElsTSI E£S£EgSZ S£E£E3翌£4S45455 西SSS5E9 qq4 4 12 2 1如竺叩m初如理如mm如级晅 hF I r . -H. -I r 1 . i, i, I . LT' 1 > I i, I 0-000 o o o o o o G3 2 1 2一 2 5 GEnnup date spjset qty=qty+20where sno in(select snofrom swhere city-北京'

32、)commit(3)零件P3已经停产，请将P3的相关信息从数据库中删除。deletefrom spjwhere pno=' P3'commitFMO IfNAHE 向® HEIGHT jiSHOjrwojjwImIfrom spj w her fl png* F3'commi t2 34飞PlF2F4F5F&1R母栓丝轮轮螺冬據凸齿红绿红蓝红2 7 4 0 011 1i 114-5delftsfrom p wlier* piw>=" F3'eommi t=U IsTTT091011一12"13141一 1125515

33、6-626654 F-F F F F F F r-F-F F F-F F Fo-oooonunuo-l-li I I I I 典山盟煌El w旳莎m药命MM（4）创建零件名为螺丝刀的供应情况的视图P_ls，件名（Pn ame,零件重量（Weight），工程项目代码（Jno）,供应数量（QTY .包括供应商名Sname,零create 小W F_15asselect snamepAameqtyfrom 环 p” 5PJwhere s. sno-spj . snoand p：pnoand p. gamg"媒丝刀"J,SME jfHAHE柑EIGHTjiajgTV ftW轉尔-蛭

34、丝刀*14 J4 II 500commitcreate view P 1sasselect sname ,p name,weight,jno,qtyfrom s, p,spjwhere s.sno=spj.snoand p.p no=spj .pnoand p.pname='螺丝刀'commit（5）在视图P_ls中查询供应数量为500的供应商姓名。 deletefrom pvfhere pnc=" ?3"!SHAMEselect snamefrom P1swhere qty=500(6)完成课本P127第5题的(8)-( 11)小题和P128第11题。up

35、date Pset col.0ir='蓝' #her6 color-红'2345FHOcommi t» 50:13 已芫成FlF2F4F5FS! TRANE 辣母 螺栓 蛭丝刀 凸轮 齿轮卫蓝绿蓝嗑蓝-£7400-1114 3-T 一- up date pset color='蓝'where color='红'commit(9)update spj wet suH S3' whew jnH JV5(1S5'1£34卜-567Frr1"910111£13142 17 1-4

36、12132 1-£-565up date spjset sno='S3'where jno='J4'and pno=' P6'and sno='S5'commit(10)deletefrom spjwhere sno='S2'commitdeletefrom swhere sno='S2'commitdelete frffin spj wWe ZM- S£commitdel fete from s where sno=' 32*12345益血蘇民彬 精东丰为英I ST扎T

37、VS 莎30起30comnuiK ：|T1:1FlIjl£00riJ3100riJ4TOF2J2100FlIJlZ20PSjl100F6IJ3300F6J4200IJ4100F6J2£00F6J4500F5n600F4IJ4500FjBO I 匹阻 n(11)insert into s(5no3 values C S2insertintc spj (sn。jn® png qty) valuem ('S才 / JIS" r F牢 ” 200) cornmi t=e丽I己芜成，耗旳VT* wF6"*8FegP£10P611P6if

38、F513F4frF430J200100£00500eoo500200insertinto s(sno)valuesCS2')insertinto spj(sno,jno ,p no,qty)values('S2','J6',' P4',200) commit11.create vi卅 S_i电5select siLC pno, qty from spjwhere jno in(sel&ct jna from j*her* jnsH 三逹'123SHOfPlR)|Qn.l!SI力200S3Fl22aS4FS100c

39、reate view Sas(select jnoselect sno ,p no,qtyfrom spjwhere jno infrom jwhere jname='三建')(1)select pno, sum (qty) from S_jSroup by pno12PHO1 芯 UWCQTY)iH420PS100select p no,sum(qty) from S group by pno(2)sel&ct + £ro*n £_iMitaW SV select * from S1 Isi5M0 I： FHDFl£00where sno

40、='S1'2.在实验一创建的表中使用 SQL语句完成以下操作:(1)学生孙天要退学，请删除该学生的所有记录。deletewhere sno inEBO1 SHAMEismISAGE IeIETHET1 DEPTHElir 1(select sno10910510220 1987-1-9农学from Etudeikt20910510320 i1967-4-0水建-1whre snam-'孙天'309L05104IT!1960-4-IT生龟 二1|4O91OSlOSIS i19eg-6-13T资环delete产11S0910S10623 11964-1-2+信息-frcm student60910S10T20'1967-7-15F信息丨where 百nam” jJiX709105 IOS空3 11964-7-20G资环Tcommit e09105109白晓鹏20,1367-4-22+外语系'deletefrom scwhere sno in(select snofrom studentwhere sname='孙天')delete from student where sname='孙天' commit