数据定义与操作

信息工程学院1数据库原理与应用(SQLServer2008)实验/实习报告一、实验目的1、掌握使用SQL语句创建和删除数据库；2、掌握使用SQL语句创建和删除数据表，创建各种完整性约束，修改表的结构；3、掌握索引的创建和删除方法。4、掌握查询语句的使用方法，重点掌握连接查询和嵌套查询，理解查询的执行过程。5、掌握数据更新语句的使用；6、掌握视图操作的基本方法和应用，理解基于视图的查询和数据更新操作的过程。二、预备知识1、数据库的建立与删除可以使用SQL语句建立数据库，语句格式为：CREATEDATABASE〈数据库名〉其中CREATEDATABASE为关键字，＜数据库名〉为用户创建的数据库的名称，由用户自定。使用DROPDATABASE语句可以删除数据库，语句格式为：DROPDATABASE〈数据库名〉其中DROPDATABASE为关键字，＜数据库名〉为要删除的数据库的名称。当数据库被删除时，数据库中的所有数据对象也一起被删除掉。2、数据表的创建与删除使用CreateTable语句创建数据表，语句格式为：CREATETABLE〈表名〉(＜列名＞＜数据类型〉[列级完整性约束条件][,＜列名＞＜数据类型〉[列级完整性约束条件]]…[,＜表级完整性约束条件＞]);其中CREATETABLE为关键字，＜表名〉为数据表的名称，由用户确定，＜列名〉为组成这个表的属性列的名称，由用户确定，＜数据类型＞为属性列的取值的类型，由用户在具体的DBMS所提供的数据类型中选择。使用DropTable语句删除数据表，语句格式为：DROPTABLE＜表名＞实验报告的内容与格式按任课教师的要求书写。注意：删除一个表，表的定义以及表中的所有数据，以及该表的索引、约束、触发器等将自动删除，并且与表相关联的规则和默认对象也将失去与它的关联关系。3、修改基本表使用ALTERTABLE语句修改已建立好的基本表，该语句可以实现添加、修改、删除基本表中的属性列、约束等操作，下面分别给出语句格式及说明：（1）添加操作在ALTERTABLE语句中使用ADD子句添加新列和新的完整性约束，添加新列的语句格式如下：ALTERTABLE〈表名〉ADD<新列名><数据类型〉添加新的完整性约束语句格式如下：ALTERTABLE〈表名〉ADD<完整性约束规则〉（2）修改操作在ALTERTABLE语句中使用ALTERCOLUMN子句修改原有列的定义，语句格式如下：ALTERTABLE〈表名〉ALTERCOLUMN<列名><数据类型〉（3）删除操作在ALTERTABLE语句中使用DROP子句删除已有的列和完整性约束条件，删除列的语句格式如下：ALTERTABLE〈表名〉DROPCOLUMN<列名〉删除完整性约束条件的语句格式为：ALTERTABLE〈表名〉DROP<约束名称〉关于ALTERTABLE语句更详细的语法格式请参照“联机丛书”，下面通过实例给出ALTERTABLE语句的具体用法，如下：操作类型实例添加添加新列AltertablesaddSidchar(20)添加新的完整性约束Altertablesaddunique(sid)修改修改原有列的定义AltertablesaltercolumnSidchar(30)删除删除已有完整性约束Altertablesdropuq_sid删除已有列AltertablesdropcolumnSid4、索引的创建与删除使用CREATEINDEX语句创建索引，语句格式为：CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX〈索引名称〉ON〈表名〉（列名[ASCIDESC][,...n]）其中UNIQUE、CLUSTERED、NONCLUSTERED指出所建索引的类型，分别为唯一性索引、聚集索引和非聚集索引。注意：不能在数据类型为text、ntext和image的列上建立索引。使用DROPINDEX语句删除索引，语句格式为：DROPINDEX〈表名.索引名〉注意：DropIndex语句能够删除CreateIndex语句所建立的索引，但是它不能删除SQLSever为PrimaryKey或Unique约束所建立的索引，这些索引只能通过删除约束或表的方法来删除。5、查询语句的基本格式如下：SELECT＜目标列名序列〉--需要哪些列FROM〈数据源〉--来自于哪些表[WHERE〈检索条件〉]--根据什么条件[GROUPBY〈分组依据列〉]--分组[HAVING〈组提取条件〉]--筛选[ORDERBY〈排序依据列〉]--排序6、数据更新基本语句及其格式（1）插入操作SQL的数据插入语句INSERT有两种形式：①插入单个元组，使用命令：INSERTINTO基本表名（列名表）VALUES（元组）②插入子查询的结果：INSERTINTO基本表名（列表名）查询语句（2）修改操作当需要修改指定关系中元组的值时，可使用下列语句实现：UPDATE基本表名SET列名1=值表达式1[,列名2=值表达式2…][WHERE条件表达式]（3）删除操作删除关系中满足条件的元组语句格式如下：DELETEFROM〈表名〉Where〈条件表达式〉三、实验内容（一）数据库的建立使用CREATEDATABASE语句创建名为“gongcheng”的数据库。SQLQueryl.s..-rator(52))*日createdacabasegongenengL(二)数据表操作1.建立数据表在创建的“gongcheng”数据库中使用SQL语句建立4个关系(可参考课本P74习题5)，如下:供应商表S(Sno,Sname,Status,Ctiy)SQLQueryl.s...rator(52))*日createdatabasegcngcheng白createtableEi5hq矿己工口五mr20ikeyr5name^arcnar-LC1dniqae..StatusvarcjTiar,-Cityvarc/iar[23)r写完这个语句后，我已经按了执行，但是在左上角那没选中'gongcheng”这个数据库，所以出现了以下错误。把代码剪切下来，然后在左边列表把S表删除，再重新粘贴改代码，选中“gongcheng”数据库，这时候执行就没问题了。

SQLQueryl.s...ator(52))*日createdatabasegongcaeng[=1createtab-le5(5novarcriarJiprimaryRey,5nejne‘/Mr□二己二(20jli匚二口，已.Dtatus-Z3rcr:ar12jrCity(231编JI_3消息命令已成功完成-SOLQueryl.s...atde(52))*日createdaLabesegcngcneng白createcable5(Encvarcjrar23}世里土哄工¥Ifey.Snamevarc?:aiL5}口口二q-j,已,otat_；3varc?:arL0:{Cityvarc?:ar-23}L}jms消息消息-14,技别住，状态&第1行数据库中已存在名为是，的对彖。S表建成:寸览宜抻菖理舜F旱#孽伊，为句、一丑呈-02JL\FC(J^8.(5QLServer10.D.-」加8厚*季藐微据库t一敷据降惧第-JEonjch&ntf/数据废美莎肉-：表壬_J系墉表士73JU*+一1被:朋-［同义调干技玳蜥性_JServiceBtoIce!e土二存儒4一I安全饮tjStudent壬」立全性■」UH■器对色+_j昼刮•」苣璀1-3SQLSecuer代理SQLQuery]-3ator(52>)*：fLicreattda?;a£；a.31u_r(jjiitric口i±reate-table2■SacVAZchar2QiprxauLZy虹了|SEtuiev&TchmriOiuhiiej；j.ebStatesvar-chir10iC^zyv<Tch>r20；l「iI-createtafileFJ备令已成功完成.零件表P(Pno,Pname,Color,Weight)SQLQueryl.s...ator(52))*jtitusvm口混=LJ}fCityv己rc?:ar20icreatetsb_eFPr.cvers'-.cri2?jcri^.c.xy:ey,Fnairuevarcliax(10},Calcrvarciiart€},Weightintcheck(weig'h.t>=lan.d.weight<=50□createtableJ(Jnovarchar(20JpriiaarykeyfJnainevarchar(jnotllqJ.1uniqaerCityvarchar(20)—i(~5消息命令已成功完成。

-2WC04B.^SQLServer10.□.:-口数锯库+3系统数据库[+EJ数据摩快照-,gangcheng+口叛据庠关系图-LJ表4LJ系统表4_Jdbo.F4_Jdbo.S七口视图+口同义词+口可编程性+_jServiceESroker+na存储4-f专荃悍工程项目表J(Jno,Jname,City)SQL^uery111...>tor(52))*varcSifliE120)primary，已F海me110)PColor7a^ch.ari"Eht,WeigibE-IilTsheckweight->-A.az；dweicjhEDcreateJ|(JnovarcharCJO>而a™：varcbsr(10)primaryfceyrfnot-nullUEiique^tiisvvaictiarC2Q)□ereace-tjfcleStJSn.ovarctiar30J*tPn白^o-iSdBiaE120s!rJe^ova.rch^ri：2G}rnrrviitr-2MAPC04S.(SQLServer10.0.:-一I数据库+一J系统数据库+D数据库快照-Jgongcheng+二数据库关系图-二表+一i系统表+二dbo.J十_|dbo.P十_|dbo.S十O视图十一|同义词十□司编程性+_|ServiceBroker+_J存储<一;十一|安全性+|Student+LJ妥全性<一;供应情况表SPJ(Sno,Pno,Jno,QTY)SQLQueryl.s...ator(52))*City'zarcrzar(23)-itc-reatetableSPJ(SuqPhq'Jncr,QTYprimaryfareignfar-ei^nforeignivarctiar-(20>varciiarJ20}intrk:eyJSnorPno,Jno}keyk:已早(Sna>(Pno>(Jno)-refereneesreferencesreferencesS(Sno}TP(Pno}fJ(Jno}altertable5add5p:ione氓二_、消息命令已成功完成-SPJ表创建完成:

-21UPCa48.(SQLServer10.0.:-口数据库+lD系统数据库+iD数据库快照-.got-Lgclierig〔数据库关系图-表+U系统表+25dbo.J+dbo.P+23dbo.S+23dbo.SFJ十一I视图十一I同义词十一I可编程性要求:创建每个关系的主键，有外键的创建外键。S表中的Sname属性列的取值唯一P表中weight属性列的取值范围在1-50之间J表中的Jname取值不能为空并且是唯一的SPJ表中QTY属性列的数据类型必须为int修改表的结构用SQL语句完成以下操作:SQLQueryl.s...ator(52))(QTYme,primaryforeLgnfforeignk:e\-r(5nofPncfJno)rIreykeykeyCSna)(Pno}CCelq)e^ercesreferencesE(Snc)/P以口见rJ(Jnc)altertable5add5phonevarciiar(20);1-a._ter3口己mm^.-quuii^q_;e;<11==二:-J消息命令已成功完成H给S表增加Sphone和Semail两个属性列，分别用来存放供应商的联系电话和电子信添加完成:

衣由目国国日□田田@0-系统表dbo.衣由目国国日□田田@0-ySno(PK,varch.ar值HSnajite(varchar(10)IStatus(varchar(1C|T|City(varcliar(20^ISplione(varchar(2C圄Semail(varchar(2C一I键J约束删除Jname属性列取值唯一的约束。primaryfcre^^nfUMg口foreignkey(Snc^Fnc,Jnoi/primaryfcre^^nfUMg口foreignkey(Snc^Fnc,Jnoi/keykeyRey(Sno}(Fuq}(JncjreierercesS(SD.C)fP(PILQ)rb(JlLG)altertableSaddEphciie7a^c：?:ar(2Oi;altercableJdrcpUQJ5klBB6B6iaB3735E;1-aleercableSalte^-ccLumiiQf!VomaLL^nt;_』消息命令已成功完成-删除完成：-_Jdbo.J+D列-口健yPK__J__C4D1DF36亡i约束(3)将QTY属性列的数据类型修改为Smallint型。

S9LQucr)rl.事...m”(52*foreignkeyJ~no|^eferenceaJ|Jnc»卜Jal"eititle3&dd3pr.zel*Vbtz!ial20oHterLatlESeT-i-l1vcxcha-r20:■ItertAtlE7dxap_<_£rtl?S-ta^LOSS^953?ttlcerLdble3PJaltercol-uznnQTYJjmILlu5rtU命令■已成功商.修改成功：_'■_■■■_■30.SPJJ列7Sno(PKjFKjvarchar(20)j'Fno(PK,FE,uarGharf^O),?Jno(PK,珏与uarchar(20^国QTY(Einallint,null)J键J约束(4)删除S表中的属性列SemailaltertableSdxopGol-unmemail;创建、删除索引通过SQL语句分别在表S、P、J表中的Sno,Pno,Jno属性列上建立唯一索引(升序)createuniquecreateuniqueindexcreateaniqueindexcxeat-euniqueindex.SafeOtTS(Snaasc);Pah-OMP^Phqasc};CabONJ(Jhq熟d);I可消息命令已成功完成。索引创建完成:-口表A+一I系统表-蜀dbo.J+iD列+i□键D约束+iD触岌器-I□索引患Jab（唯一，APK__J__C4D+i口统计信息-.3dbo.P+1口列+1口键+1口约束+1口触发器T口索引JiPab（唯一，APK__P__C57l刊D统计信息-_Jdbo.S+iD列+i口懿+iD约束+iD触发器-I口索引APK__S__CAU五腿（唯一，m患Sab（唯一，五UQ__S__527：+1□统计信息-_3dbo.SPJ-I口列数据录入操作在EXCEl中录入四个表的数据（具体数据可参照课本P75四个表中的数据），用数据导入功能将EXCEL中的数据分别导入到S,P,J和SPJ表中。选择数据库，右键，出现以下界面：5.备份gongcheng数据库。还原实习二中创建的“gongcheng”数据库和实习一中创建的“学生选课管理”数据库。在“gongcheng”数据库中完成以下查询：(1)查询所有供应商所在的城市。selectdistinctcityfromSSQLQueryZ.s...atarC52))*selectdistinctcityfS-5菇果3消息cit/「质］j上海天津(2)查询零件重量在10-20之间(包括10和20)的零件名和颜色。selectPname,colorfromPwhereWeightbetween10and20J结果J消息PnamegqIqt1:螺母:红2螺栓绿3螺箜力蓝4螺丝刀红查询工程项目的总个数。selectCOUNT(distinctJno)fromJselectC0TJ1TT(distinctJno}fromdJ结果_3消息列名｝17查询所有零件的平均重量。selectAVG(Weight)fromPselect：AVG(Weight：｝from.Fselectpncfrcrr.5FCwiiere5no=15311-selectSnc,,FncIzsomBPJ:无列倒i21查询供应商S3供应的零件号。selectpnofromSPJwhereSno='S3'selectpnofromSPJwhereSno=lS3四结果阻捎息pno[>1””-j'两查询各个供应商号及其供应了多少类零件。selectSno,COUNT(distinctPno)fromSPJgroupbySnose1ectSnafCOUNTtdistinctPnaJ-fraraSPJgraupbySuese_ecv5nafromSPJg^cupty5nciiav^ngCCLT-II[*】:2aelect.PnameSno；无列名)i2S2S3S4S5

查询供应了2类以上零件的供应商号。selectSnofromSPJgroupbySnohavingCOUNT(*)>2selectSnofxcniSFJgiaupbySnohavingCOWT(*}>2J结果J消息■Sn口|SIj'"S2-….S4S5查询零件名以“螺”字开头的零件信息。selectPno,Pname,Color,WeightfromPwherePnamelike'螺％selee匚Pno,PsdLise：,Color,KeighEfrcaPwherePnaselike'嬲罟・旦铸果L4j消息PhaPnameColorWeighTOC\o"1-5"\h\zipi螺母红也p?螺栓维nP3螺理刀S14P4螺丝口钗14查询工程项目名中最后一个字为“厂”字的工程项目所在的城市。selectJname,cityfromJwhereJnamelike'%厂'

seleccJnamEfG-ity£ramGwiiereInamElils巳,君「J结果_j消息Jnamecily[弹簧厂天津2造船厂天津3机车厂唐山4无短电厂&半导体厂南京查询给每个工程供应零件的供应商的个数。selectJno,COUNT(distinctSno)fromSPJgroupbyJnoselectJno,CCTUTT(d.j_3t.j_njCtSno)fromSPJgroupb-yJan归菇果憧消息Jno洗列名)TOC\o"1-5"\h\z[JI““j5J2-3J32J44J51(11)查询供应数量在1000—2000之间(包括1000和2000)的零件名称。selectPnamefromPwherePnoin(selectPnofromSPJgroupbyPnohavingsum(QTY)between1000and2000)

Pnarwi'蚯矿(12)完成课本P127页第5题的前七个小题。selectSname,cityfromSselectSnamefcityfremSJ结果_J消息SnarnecityiP'SS一…j天津:”窟禹i北京3东方红北京」丰泰盛天津5为民上海selectPname,color,weightfromPselectPnemefcolorFweightfromFJ结果J］消息PnamecolorweightselectJnofromSPJwhereSno='S1'

selectJnofrona5FJwhere5no=,51艺结果上消息Jfi&■■■■■■■■■■■■■aTOC\o"1-5"\h\z1JIi"H''J4J2selectPname,QTYfromP,SPJwhereP.pno=SPJ.pnoandSPJ.Jno='j2'二I踌臬道拿JB户nan中QTY叔栓；100TOC\o"1-5"\h\zflirtjf"2DD凸莉1KA有椅selectdistinctPnofromSPJ,SwhereSPJ.Sno=S.SnoandS.City='上海'e：JL甦ex■之卜j□塔果1..鞋PnoZF2i1JPiPGselectdistinctJnamefromJ,Spj,SwhereJ.Jno=Spj.JnoandS.Sno=Spj.SnoandS.City='上海'|与皂1胃己七dLml:暨nr任J匕占吊-frain!i1\兽口，,£霍曲b上旦』林了曰—§甲项・上1曰昌业日1-：813&・：5早，-鸟日日&业=1§.2昼七肾・・tabors1T造尚「

Selectdistinct(Jno)fromJWhereJnonotin(selectJnofromSPJ,SWhereS.Sno=SPJ.SnoandS.City='天津')8.在“学生选课管理”数据库中完成以下查询:(1)查询是“选修课”的课程号、课程名。So,amefromc,o=o130D5计管机网络14liit&fnet15{HJ6Java语言16DD4操作系统17计萱机网络1BM7编译原理19WQ网谿编程enocname1l0021网锵蝙程!■2叩3DB_Desig3005计耸机网给4(W7编样原理5001人工智能6皿网洛编程7Jiavaind£Irrtefnet3OT1九工智能W0G3DB_Desig11{HM拱作系藐12QD1人工智能查询学号为“09105103”学生所学课程的课程名与学分。selectSno,Cname,CreditfromS,CwhereSno='09105103'12Sno:[KKJ510G00105103Cname§人工智能网绪编程Credit32.5309105103DBwDaslg24(K105103躁作条藐2&09105103计算机网貉3.56091051032.5Q9W51D3编译藤理33D91051S3Intemet2查询选修课程号为“002”或“004”的学生的学号。SelectSnofromSCwhereCno='002'UNIONselectSnofromSCwhereCno='004''田结果低亟i?Sno!而而质而-~I、・・・■■■■■■■■■■■■■■■■■■■■■■■■■■■rD9105102DS1O51OSD9105HK脂1051W查询选修了课程号为“002”和“004”的学生的学号。SelectSnofromSCwhereCno='002'intersectselectSnofromSCwhereCno='004房结果阻巽邑Sno查询学习全部课程的学生姓名。selectSnamefromSwherenotexists(select*fromCwherenotexists(select*fromSCwhereSno=S.SnoandCno=C.Cno))三皓果△消息Sname在实习二创建的“gongcheng”数据库中使用SQL语句完成以下操作：现有一供应商，代码为S9、姓名为英特尔、所在城市西安，供应情况如下：供应零件P5给工程J7数量为600,供应零件P4给工程J4数量为500,请将此供应商的信息和供应信息插入数据库。insertintos(Sno,sname,city)values('s9','英特尔','西安')_J1消息2UPC04S.eangchene-dbo.SSQLQuery2.srator(52))*SnoSndneStatusCitySpheneSI精益20天津NUS.LS2盛踢10北京MJiL53东方红30北京MJ1LS4丰泰盛2Q天津MALS5为民30上海MJ1Le9英特尔西安ML来MJS1ALCZAU2MJiLinsertintospj(sno,pno,jno,qty)values('s9','p5','j7',600)&Sp4j4汹s9p5J7600MP，Lj消息（1行受彩响）insertintospj(sno,pno,jno,qty)values('s9','p4','j4',500)鬲消息］<1行受季响)p4衬500s?p_5j7600土■JRiJOTnJA-ffBTJiffJTJAEJT」请将北京供应商的供应数量加20updatespjsetqty=qty+20wheresnoin(selectsnofromswherecity='北京')零件P3已经停产，请将P3的相关信息从数据库中删除。deletefromPwherePno='P3'deletefromSPJwherePno='P3'Jsa消恩瓠别券，状参山笔2行逐句号药食"计_£站__—0_皿场MS"冲夹.流冲类发生田麒据库七皿铲土〜?孔熹:。心-；语句已襄Lb您行甄响,（4）把全部红色零件的颜色改成蓝色。updatePsetColor=蓝’wherecolor='红2MAPC048-gonechenE-dbo.F2MAPC04S.go...g-dbo.SPJSQ：PmPnameColorWeight-爆母蓝12P2绿17P3螺丝刀r蓝MP4螺丝刀蓝14P5凸艳蓝4JP6齿抡蓝30质MLAW(5)由S5供给J4的零件P6改为由S3供应，请作必要的修改。updateSPJsetsno='s3'whereJno='j4'andPno='p6'andSno='s5'_J消息M行受影■响，(6)请将(S2,J6,P4,200)插入供应情况表。insertintoSPJ(Sno,Jno,Pno,QTY)values('S2','J6,,'P4',200)消息(1行受贤响)（7）创建零件名为螺丝刀的供应情况的视图P_ls，包括供应商名（Sname），零件名（Pname），零件重量（Weight），工程项目代码（Jno），供应数量（QTY）。g。createviewp_ls（sname,pname,weight,jno,qty）as

selectsname,pname,weight,jno,qtyfroms,p,spjwheres.Sno=spj.snoandp.pno=spj.pnoandp.pname='螺丝刀21APC04S-eo---£—dbo_p_ls2MAPC048.gongcheng-dbo.P_JMAPC048.g(snamepnameweightjnoqty英特尔心I500ALEi在视图P_ls中查询供应数量为500的供应商姓名。selectSnamefromp_lswhereqty='500'园结果国消Sname1「；诵宗：「・・・■■■■■■■■■■■■■■■■■■■请为三建工程项目建立一个供应情况的视图，包括sno,pno和qty。针对该视图找出供应商S1的供应情况。createviewS_sanjian(Sno,Pno,QTY)asselectdistinctSno,Pno,QTYfromJ,SPJandj.jno=spj.jnowhereJname='三建'在实习一创建的“学生选课管理”数据库中使用SQL语句完成以下操作:（1）学生孙天要退学，请删除该学生的所有记录。deletefromscwhereSno=(selectSnofromswheresname='孙天');deletefromswheresname='孙天’■:消息K行受响)ti行受霰响】SnoSnsmeSsewSageDeptment091J05U02覃祥女2J0农学Q9UO5U03陈旭男20水建09105104李丽女17生命0910510&男13推环09105106田野男23信息09105107也永亮男20信息O91JO5U0S侯玉女23