国家开放大学《数据库运维》形考作业-实验2：MySQL数据库对象管理.docx

《国家开放大学《数据库运维》形考作业-实验2：MySQL数据库对象管理.docx》由会员分享，可在线阅读，更多相关《国家开放大学《数据库运维》形考作业-实验2：MySQL数据库对象管理.docx（10页珍藏版）》请在课桌文档上搜索。

1、实验2MySQL数据库对象管理1 .实验目的理解数据字典、表、索引、视图的作用，掌握数据字典的操纵方式，掌握库、表、索引、视图的操作方法。2 .实验内容【实验2-1】查看INFORMATlON-Schemaschemata表中的信息。【实验2-2】查看INFoRMATIoNSCHEMA.TABLES表中的信息。【实验2-3】查看INFoRMATloN_SCHEMA.COLUMNS表中的信息。【实验2-4】查看INFORMATIon_schema.statistics表中的信息。【实验2-5】查看INFORMATION_SCHEMA.CHARACTER_SETS表中的信息。【实验2-6查看INF

2、0RMATI0N_SCHEMA.C0LLATI0NS表中的信息。【实验2-7使用CREATEDABASE语句创建school数据库。【实验2-8】将school数据库的字符集设为utf-8【实验2-9】在school数据库下创建stu,info表,包括stunoVarCharQ0）、Stunamevarchar(8)xStubirdate、Stuageint6U建表course,包括cournovarchar(3)xcournamevarchar(10).schyeardate,creditint;仓(J建Stuandcour表，包括stunoVarchar(IO)xcournovarchar

3、(3)xgradeint0【实验2-10】使用SHOW、DESCRlBE语句查看所建的表。【实验2-11使用ALTERTABLE语句将Stuinfo表的表名改为Stuinfo,并且增加StugenderVarChar(4)字段，将course表中courno字段的类型改为varchar(5)0【实验2-12将前边建的表复制到test数据库中，如果没有test数据库就新建一个。【实验2-13删除test数据库中的CoUrSe表。【实验2-14】创建StUinfO表上StUno的索引。【实验2-15创建Stuandcour表上Stuno字段和courno字段上的多列索引。【实验2-16】删除创建的

4、索引。【实验2-17】创建StUinf。表上的单源视图。【实验2-18仓U建Stuinfo表和Stuandcour上的多源视图，包含stunoxStunamexcournoxgradeo【实验2-19分别通过访问information.SChema的views表和tables表，查看已经创建好的视图吏用DESCRlBE语句查看已经创建好的视图。【实验220】使用SELECT语句查询创建好的视图。3 .实验步骤及结果截图MKQI57CrtvredGrWCSwiI-OircOr9：HEM-XA电 IlydM(XVdb 二*yg，&raw : c,)30JVSSQLJTH Mj4Ql 7 frv*f

5、wd Lirw CSl Uiotetial.*4UllIet g LgmlSKUJASC 7BLZSX用WM IO Damc UI 2013 6 308 revs in at 0.0S *cx5rft v 5-hyjwr-hyI T LU.MLLxrx.嫣“1 rI oM I .by. I rU.U.MLLR13 Q9 11c nu7BL:I3l72 XUInroJSecntry 23915:24.42 MILWldIO D/wwtr氏U 1C15 ,力 17eotr lagvae蚓二44 XLlLM33 XUlBSt IBL-9：pSJg；,：*5,r-nfreMf.nterwnnQ.C但m

6、cgIda7SsV“m，.：”My0X,R9.Crcl*x1m,Hs“ffliz”.11M*av*rM.3”、dadr0rdr4m,u”Oy*c：AC4)rncfFften，!n论vr*iT11*bl;:rBforhelp.T11*,eeeleertM!”瞰statfftwt.三ylMletfreeLifo11mi1,sCiw.schct4kflh.OIgCToojwR.nLu门o4Myf5/*c*raLd11ml_CrrW1工1-cl_SVtdKbv,ZcLBHic：f8joralexasl_Rra.rf4vtMwx,Mytcn*rF(iutFrr的0.02newtXiuU41NQttlXU

7、Uinst.jt*.r*fre*s3237f(x34set0.OTXe“6WursrdfvrldCOCXrEgcr1.rtr.MvorldccrxrrloM0finalMilXIIJ.*!.I,*rMIr.r0f4wx-ftc.；rota.i9i(O.Saxv-c,vlvlttiBIiUocBMtLtfuXAUa；l，：d*5Q-57Cus,n3lMU*f*UMdrMUvt15AIMynafrWkfU!N.feir11r-tfU1Mkflefw*rj-*tl.MLLBl皿OfMklUrnl0aekUenrMU.KIXMMtllArwl0ekileRtt“L(“”hUXffixXMttlAr*t

8、tl0MXile.rstaL&kMU.BTa:1OfMJtlle“二”10!Xil.r*ct*Ldi!UIBlKXOfgkrtfilsehllt.dxfmwemdranIrnKrfMJtllArettlseJUleIdXfl八MAIWNlIiIInFr*sUl汕”1emie.dnsuff”KnJrwr5ef9*1USUa0sellUFJLiMinKn(IWTW,一:RlllaIUffcki.r.11Xfxslle:，Knll11fir.出h232ekiU：dx：X鼠IICS，匕M1J.mrde：5dLUstore0seJu：Vnnaurnrraxillastore0xekllex1SAiuce

9、xxcx1.-MUmp皿hlore:IdLHtLldxi41C3;Me*ldCvo*ldcc.uiylctt4Bc0VUCtdPlnIm.UFmcfVOrMi:ifttV；rr：!0ne,Mlll%.Uw三e*w*U11.ViltyriXm;-wl4Cctf)4ev.MUbM6Qi57Cvr*)ofr*uifnntioe-imCgrXr，7/$，E6C3tH-YMK口T(MLSTF.W I U1C2Pks&ilih Cbk i 18 IIfSrjVicib kei3 mrrc MXLJ CZW.vrr.Vtf )0 UirlGlc CMg W侬T VXt33riw5h:g).Chlm.r JC

10、Lg KAVd 4”4 CPbMLeolGWq ilpS ct/ld= J Koi3r.c*nl ct it inlau*tii d,_c i :炉广?1 “c，_二 3d i oh.I sen c3ral a u. i e_ 叫 5Mejr i J12-%能*?EC hrjrjjcnrr.*-c i txs20 mu ci-a018j cakal ci gh”I=mc_c ； ：.f I Cl rpl , *l*-fnrrr3,- U. cLLlext Ci Irti nh,ti br i .44. 13 Cenejtl c! “，舟ULs2 rae;Bl uiT。I_d kWL2 XCht

11、fltl c IKCWEq IUii OMXi nexl ui VM)：lEWE.Cl IXliu； gcixcui ui Tn 大灯 b4MT-。： c 1251JCIqt：_S lo mmyQI 0 mI lJlc-cwrlc opl23j 2Grl ?: Lj，l23：_WCt J：f .I2 fJ- far; 1 t tr:crel ?; ，G2_j.，”ru : JC j XClJ蠢 UMJi Cl ghllWSOerl： inr.rft Ur *r s. LKlWraU KQlS I aicon 处“1A0 cp2S2 11 v,. Ekfupx, MO S,必：Crrl tr w

12、n i i l SvHd IS . FL-JT Jjv ： 门.5e SQ 经.*S TInLi r uc rui Frm KftrcTW18 I l匕机 Gg不：，一 If “i,U Cllnese TSft 瞌”T Ttrki cb A4X11S Axnraltx HFr rtcxk UCS3 UucoJe W6 1:SC0 M1lcx.a1 Ftfapmt 必 tl LarspcM 【:SKrpwISO 先分，3 Balt.i F S Cnxcte 小;“1”3 Ctt .11 i%.VTF U %z” -1t Jierrnr V；J$ ：cr BWCgM 3- ir KM；o*u) J

13、Un4d CA8OSO；O.(IOWj-346277989o1q123456789o123456789q123323367689o 12203656253 666666666666777 777 7 77 7WCOBS6999999443 111 Iiiiiiiiiiiiiiiiiiiiiiii 222eeeeeeeceeeeeeeceee=2eeeeeeee:2eeeeeeexeeeeeeeMySQl5.7CommandIineCfient-Unicodeutfl6croatianciut116utfl6unicode_520ciUtfI6uf16.Viotnflgqpjnufl6utfl6

14、1ejeeral.ciutfl61eutfl61ebinutf161ecpl256,general.cicp!256cpl256bincpl256cpl257lithuanian.cicpl257c1257-blncp!257cp1257-general-cicpl257uf32-general-ciutf32uf32binuf32utf32uniCOde_ciutf32utf32_iXelandiJCiutf32utf32_latvian_ciutf32IHf32Olmnian-Ciutf32utf32-slovenianciutf32utf32jxlBhciutf32utf32.eston

15、ianciutf32uf32spanish_ciuf32IKf32_qSdiSh_ciuf32utf32turkish_ciutf32utf32czechciuf32utf32danishciuf32utf32.1ithuanianciutf32utf32slovak.ciutf32utf32.,spanish2ciutf32utf32oDaLCiutf32utf32prsian_ciinf32utf32_sprarno_ciutf32utf32hungarian_ciutf32utf32_sinhala_ciuf32f32_gran2ciIKf32utf32croatianciutf32ut

16、f32unicode520ciutf32utf32_vietnameseciutf32binarybinarygeostd8eeneraLCigeostd8geosd8btngeostd8cp932_japanese_cicp932cp932.bincp932wcjpoK_japan。SQrCiwcj(xneucjpmsbineucjposgbl8030chineseci的18030gb!8030,bingb18030gb18030unicode_520-cibl803022rowsinset(0.00sec),sqlalterdatabaseschooldefaultcImxacterset

17、utf-defaultcollateutf8.ieneral.ci:eryOK.1rowaffected(0.Olsec),sqlcreatetablestunfo(-stunoVarChar(IJ,Stuiuunevarchar(8)l-stubirdate.-stuageInt-:IeryOK.0rowsaffected(0.05sec)oysqlCrRaTetablesumfo(- stunovarchor(10).、3tunamevrchar(8).- stubirdate,- StUaeeint- ):QuervOK.Orovsaffected(O.05sec)nysqlcreate

18、tablecoursecoumoVarChal(3).- couma*evarchari10).- schyeardate.- creditint- );3ueryOK.Orowsaffected(0.04sec)ysqlcreatetablestuaudcour(一stunovarchar(10).- coumovarchar(3)tgradeinteryOK.0rovrsaffected(0.04sec)ysqldescribestu-nfo;FieldTyPeNullKeyDefaultExtraLLLL ssstunova*char(10)YESunaseVarCharYESSUlbi

19、rdescribecourse:FieldTypeNullKeyDefaultExtracoumoc。UrnaEeschyearcreditvarchar(3)Varchar(IO)dateint(ll)YESYESYESYESNLLLXVLLMLLNLU4rowsinset(0.OOsec)nysqldescribeStuandcour:FieldTyNullKeyDfaulExtraMLL NllL NULL3*un0Varchir(IO)YEScournoVarChalYESgradeint(ll)YES-=一=一-Irowsinset(O.OOsec)MySQtS.7Commandli

20、neCint-UnicodeStUInfoCREATETABLEstuinfo(；stunoVdrchar(IO)DEFAULTSiULLstunaxvarcharshcreatetablecourse.TableCreateTablecourseCRETETABLEcourse(cournovarchar(3)DEFAILTNVLLcou,11diueWChBE(IO)DEFAULTNULL.schyeardateDEFA11TNllL,creditint(ll)DEFALLTNllLENGINEInnoDBDSFALXTCHARSETHfS1 rowinset(O.OOsec)ysqlsh

21、ocreatetablesrandccur:ERROR1146(42S02):Table*school.Stuandcour,doesn*tnysqlshwcreatetableStuandcoui-:TableCreateTableSxuandcourCREATETABLEswndeourIslunovarchar10)DEFA11TNllL,cornovarchar(3)DEFAULTNVLLgrad。in(ll)DEFAULTNULL，ENGINE-ImioDBDEFAllTCHARSEEf8lrorinSet00SeC)三ysqluseschool;ysqlalrtableStUnfO

22、一renameStuinfc:JueryOK,Orosaffected(O.06sec)jrsqlaltertableStuinfoaddStueendorvarchar(4);JuoryOK.Oaffect(0.06q)Iecords:ODuplicates：OWarnings：Oiyqlal:BrtabUcourse-modifycournovarchar(5);)ueyOK.OrowsaffectedcrAtedatAb5etest.er,0&1rowaffected(O.Olsec)BnQIremit*Vftbleschool.Stuinfotot*8t.itunfo.school.e

23、ouretotest,course,school.SttmndcourtotBT.Stuandcour:aeryOK.Orsaffectedusetest:)aibasechangedysqldroptablecourse:JueryOK.0rarsaffected(0.01sec)Btsqldescribecourse;KROR1146142S02):Table,test.course,doesntexistBrsqlcreateindexindex-stuno-onStuinfo(Stuno)JueryOK,Orarsaffected(O.03sec)Iocords:ODuplicates

24、:OWarnings:OBySqDcreateindexindex-sunoandcourno-onstundcr(stuno.courno):JueryOK.Orowsaffected(0.02sec)Acords:ODuplicates:OWarnings:O,sqldropindexIndeUtimo:IROR1064(42000):YouhasanerrorinyorSQLsyntax：,sqldropindexndex.stuno- ontest;【ROR1146(42S02):Tablvtest.tstvdoesntexist,sqldropindexindex.stuno- on

25、stulnfo:eryOK.Orowsaffected(0.02sec)Kords:ODuplicates:OYarniogs:O,sqldropindextndex.3tUnoandcourno- 3tandcour:ryOK.OroCXazorrlacvwviw.stuinfoas-select-fromstiofo:TOK.Orosaffct”(0.02se),sqlcreateviewview-stuinfo-stuancouras一,selectstulnfo.stuno,stuinfo.stunaae,Stuandcour.COUmOvStuandcour.grade-frosti

26、nfo,Stuandcour一、EIerestuinfo.Stuno=STuandcour.suo:eryOK.Orowsaffected(0.02sec)ysql,selectfroavicw-stuinfo:InPtySet(0.00C)def“4afa网0iG3S40MUMElr*wcla9mVlMUWUMU工二I*tveorhlcityfXA0131O20砌0BlDdfg：Idcocnxy5010010WU&，53C4o-hl0co;n:rLvu/6553d0Mll019S*CwvlMlt*mj5t*)0WCmrno)此*lKfiM*aasvharOXUtdtt*YE5N11L*Kaag*o(i2MU.4,YF511L卬dnc*havtiffu1nffl.rsitt10.00M,5:v-l.*w*ircrdtcrt靛xXviv_s:uinfo_atjuct.Fld2eX：lKXCAtEvieatvaicIi,1CYSUtnarWrtBf，*VSMlJceovarctMa3jYESMU.E田TSXnJ.:rmv，rXUMXI,sqlSeleCtfrovievstuinfo_5tuancor:xvset(0.00sec)