国家开放大学《数据库运维》形考作业-实验2：MySQL数据库对象管理.docx
实验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查看INF0RMATI0N_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)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】删除创建的索引。【实验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*fwd Lirw CSl Uiotetial.*4UllIet g Lgml<SKUJASC 7BLZSX用WM IO Damc UI 2013 6 308 revs in a<t <0.0S *cx5rft v 5-hyjwr-hyI T LU.MLLxrx.嫣“1 rI oM I .by. I rU.U.MLLR13 Q9 11½c nu7BL:I3l72 XUInroJSecntry 23915:24.42 MILWldIO D/wwtr氏U 1C15 ,力 17eotr lagvae蚓二44 XLlLM33 XUlBSt IBL-9：pSJg；,：*5,r-nfreMf¾.<nter<wnn<isrd»!thnrIcjKv>Q.C但mc"gIda7SsV“m>，.：”My<.nMtniySorer.PL)Ce(TrSgM(t>'0X,R9.Crcl*x1m,Hs“ffliz”.11M*av*rM.3”'、d«ad'r0rdr4m,u”Oy*c：AC4)rncfFften，!n论vr*iT11*'bl;:"«r'Bforhelp.T11*,e'eeleertM!”瞰statfftwt.三y¼l×Ml÷etfreeLifo11mi1¢,sCiw.sch÷ct4kflh.OIgCToojw<R.nLu门o<4Myf5/*c*raLd11ml_CrrW1工1-cl_SVtdKbv,ZcLBHic：f8joralexasl_Rr<h“_u，VtfS*o*lrlc:，丸ViIMfQl57CcrwtwtdUVWCS«LkliLC”rt4rt.'pd>a.rf4vtMwx,M<ytcn*rF¼(iu<tFrr<"的0.02newtXiuU41NQttlXUUins÷t.j<t*.r*f÷re*s3237f(x34set'0.OTXe“6<×Wursr<ll”Ul.gsisel*cll133Cf.,UAUleJdHctX一dfwldcccf11,l三jcfdutrW309cl«cl.ImVt.Lplale.xetecvxe>dfvrldCOCXrEgc½r1<h:1.Ur.>.rtr.MvorldccrxrrloM0finalMilXIIJ.*!.I,*r<.>MIr.r0f4wx-ftc.；rota.i9i(O.Saxv-c,vlvlttiBIiUocBMtLtfuXAUa>；l，：d*5Q-57Cus,n3lMU*f*UMdrMUvt1¼5AIMynafrWkfU!N.f<eir11r-t<fU1¼Mkflef<P>w*rj-*tl.MLLBl皿OfMklUrn<l0aekUen¼rMU.KIXMMtllArwl0ekileRtt“L(“”hUXffixXMttlAr*ttl0MXile.rstaL&kMU.BTa:1OfMJtlle“二”10!<Xil.r*ct*Ldi!÷UIBlKXOfgkrtfilsehllt.dxfmwemdranIrnKr<fMJtllArettlseJUleIdXfl«八MAIWNlIiIInFr*sUl¼汕”1emie.dnsuff”KnJrwr<5ef9*1USUa0sellUFJLiMinKn(IWTW,一:RlllaIUffcki.r.11Xfxslle:，Knll11fir.出h232ekiU：dx：X鼠IICS，匕M1J.mrde：5dLUstore0seJu：Vnnaurnrraxillastore0xekllex<1SAiucexx<>cx1.-MUmp皿h>»lore:IdLHtLldxi41C3><Ji-aunr(fa3,yu.uxit'K0»»rxun:MUmetJrPIdtiky0VUGldruan:kuFRtt(Zd)clv>Me*ldC<xkjIi%C»4e.aur三IAervuvldUSMilry0Mxld11¾UIm的建Et>vo*ldcc.uiylctt4Bc0VUCtdPlnIm.UFmcfVOrMi¢:ifttV；rr：!0ne,Mll¼l%.Uw三e*w>*U11.ViltyriXm;-wl4Cctf)<4ev.MUbM6Qi57Cv<n*MMIgalr，l><¾r'*)o<fr*uifnntioe<-i«mCgrXr，7/$，E6C3tH-YM"K口T(MLSTF.W<KSTfPTtW毒UE；3 ko¾ Uniif.l:SJ1> I U1C2Pks&ilih Cbk i 18 IIfSrjVicib kei3 mrrc MX<1U1>LJ CZW.vrr>.Vtf )0 UirlGlc CMg W侬T VXt33r«iw5h:g).Chlm".r JCLg 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 c÷3÷ral a u. i e_ 叫 5Mejr i J12-%能*?EC hrjrjjcnrr.*-c i txs<20 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 XChtfltl c IKCWEq IUii OMXi nexl ui VM)：lEWE.Cl IXliu； gcixcui ui Tn 大灯 b4M"T-。： c 1251JCIqt：_S lo mmyQI 0 mI l<Jlc-cwrlc« opl23j 2÷G÷rl ?: Lj，l23：_WCt J：f .»I2 fJ- f÷a÷r; <1 t tr:÷c÷rel ?; ，G2_j.，”ru : JC j XClJ蠢 UMJ÷i÷ Cl ghllWSOerl： inr.r< B；r5 YrwGrcxl finw DiC Ve I LMviah 卜:G Vt F,jrop>ft Ur *r s'. LKlWraU KQlS I aicon 处“1A0 cp2S2 11 v,. Ekfupx, MO S,必：C÷rrl tr< wn i i l Sv<Hd IS . FL-JT Jjv ：< 门"< 二5 . 0C>.÷5e SQ 经.*S TInLi r uc rui Frm KftrcTW18 I l匕机 Gg不：，一 If “<E Cvtrir 13U 关处;.SE Tinriaiws Tentil Errqprer R3 丸tu>i,U Cllnese TSft 瞌”T Ttrki cb A4X11S Axnraltx HFr rtc<xk UCS3 UucoJe W6 1:S£C0 M1lcx.a< C4irw51cvd.c Ccn->1 Ftfapmt 必 "tl LarspcM 【:SKrpwISO 先分，3 Balt.i F S Cnxc<te 小;“1”3 Ctt .11 i%.VTF U %z” -1<LC 31血 Vlflr r) Yir*o BsHiC ? 3： IaM Bir:* xr(lo Gwuul 史方 US GM C1 SJlS Qr Itr*d>>t Jierrnr V；J$ ：cr BWCgM 3- ir KM；o*u) JUn4'd CA8OSO；O.(IOWj-346277989o1q123456789o123456789q123323367689o 12203656253 666666666666777 777 7 77 7WCOBS6999999443 111 Iiiiiiiiiiiiiiiiiiiiiiii 222eeeeeeeceeeeeeeceee=2eeeeeeee:2eeeeeeexeee"eeeeMySQl5.7CommandIineCfient-Unicodeutfl6croatianciut116utfl6unicode_520ciUtfI6uf16.Viotnflgqpjnufl6utfl61ejeeral.ciutfl61eutfl61ebinutf161ecpl256,general.cicp!256cpl256bincpl256cpl257lithuanian.cicpl257c1257-blncp!257cp1257-general-cicpl257uf32-general-ciutf32uf32binuf32utf32uniCOde_ciutf32utf32_iXelandiJCiutf32utf32_latvian_ciutf32IHf32Olmnian-Ciutf32utf32-slovenianciutf32utf32jx>lBhciutf32utf32.estonianciutf32uf32spanish_ciuf32IKf32_qSdiSh_ciuf32utf32turkish_ciutf32utf32czechciuf32utf32danishciuf32utf32.1ithuanianciutf32utf32slovak.ciutf32utf32.,spanish2ciutf32utf32oDaLCiutf32utf32prsian_ciinf32utf32_sprarno_ciutf32utf32hungarian_ciutf32utf32_sinhala_ciuf32f32_gr®an2ciIKf32utf32croatianciutf32utf32unicode520ciutf32utf32_vietnameseciutf32binarybinarygeostd8eeneraLCigeostd8geosd8btngeostd8cp932_japanese_cicp932cp932.bincp932wcjpoK_japan。SQrCiwcj(xneucjpmsbineucjposgbl8030chineseci的18030gb!8030,bingb18030gb18030unicode_520-cibl803022rowsinset(0.00sec),sql>alterdatabaseschool>defaultcImxactersetutf->defaultcollateutf8.ieneral.ci:eryOK.1rowaffected(0.Olsec),sql>createtablestunfo(->stunoVarChar(IJ,Stuiuunevarchar(8)l->stubirdate.->stuageInt->>:IeryOK.0rowsaffected(0.05sec)oysql>CrRaTetablesumfo(- stunovarchor(10).、3tunamevrchar(8).- >stubirdate,- >StUaeeint- >):QuervOK.Orovsaffected(O.05sec)nysql>createtablecourse<- >coumoVarChal(3).- >couma*evarchari10).- >schyeardate.- >creditint- >);3ueryOK.Orowsaffected(0.04sec)ysql>createtablestuaudcour(一stunovarchar(10).- >coumovarchar(3)t>gradeinteryOK.0rovrsaffected(0.04sec)ysql>describestu-nfo;FieldTyPeNullKeyDefaultExtraLLLL ssstunova*char(10)YES>unas>eVarCharYESSUlbir<tPYESrowsinset(O.Olsec)Bvsql>describecourse:FieldTypeNullKeyDefaultExtracoumoc。UrnaEeschyearcreditvarchar(3)Varchar(IO)dateint(ll)YESYESYESYESNLLLXVLLMLLNLU4rowsinset(0.OOsec)nysql>describeStuandcour:FieldTyNullKeyDfaulExtraMLL NllL NULL3*un0Varchir(IO)YEScournoVarChalYESgradeint(ll)YES-=一=一-Irowsinset(O.OOsec)MySQtS.7CommandlineCi÷nt-UnicodeStUInfoCREATETABLEstuinfo(；stuno'Vdrchar(IO)DEFAULTSiULLstunaxvarchar<8?DEFAULTXllL.StubirdateDEFAU.TNl'LL,stuageint(ll)DEFAULTNllLENGINE=InnoDSDEFAULTCHARSEEKfS1rowinset(O.OOsec)nv3ql>shcreatetablecourse.TableCreateTablecourseCRETETABLEcourse(cournovarchar(3)DEFAILTNVLLcou,11diueWChBE(IO)DEFAULTNULL.schyear'dateDEFA11TNllL,creditint(ll)DEFALLTNllLENGINEInnoDBDSFALXTCHARSET'HfS1 rowinset(O.OOsec)ysql>shocreatetablesrandccur:ERROR1146(42S02):Table*school.Stuandcour,doesn*tnysql>shwcreatetableStuandcoui-:TableCreateTableSxuandcourCREATETABLEswndeourIslunovarchar«10)DEFA11TNllL,cornovarchar(3)DEFAULTNVLLgrad。in(ll)DEFAULTNULL，ENGINE-ImioDBDEFAllTCHARSEEf8lrorinSet00SeC)三ysqluseschool;ysql>alrtableStUnfO一renameStuinfc:JueryOK,Orosaffected(O.06sec)jrsql>altertableStuinfo>addStueendorvarchar(4);JuoryOK.Oaffect(0.06q")Iecords:ODuplicates：OWarnings：Oiy«ql>al:BrtabUcourse->modifycournovarchar(5);)ueyOK.Orowsaffected<0.Olsec)Records:ODuplicates:OVarnings:O)sqltableschool.Stulnfototest,stuinto.school,coursetotest,course,school.Stuandcourtotest.Stuandcour.：000):ErrOQrtta三of.schoolstuittfo,to,.uststlftfo'(trrno:168-l'nknvn.【；】.：:.BTgQ1>crAtedatAb5etest.>er>,0&1rowaffected(O.Olsec)BnQIremit*Vftbleschool.Stuinfotot*8t.itunfo.school.eouretotest,course,school.SttmndcourtotBT.Stuandcour:>aeryOK.Orsaffected<0.04sec)vsqI>usetest:)aibasechangedysql>droptablecourse:JueryOK.0rarsaffected(0.01sec)Btsql>describecourse;KROR1146142S02):Table,test.course,doesn'texistBrsql>createindexindex-stuno->onStuinfo(Stuno)JueryOK,Orarsaffected(O.03sec)Iocords:ODuplicates:OWarnings:OBySqDcreateindexindex-sunoandcourno->onstundcr(stuno.courno):JueryOK.Orowsaffected(0.02sec)Acords:ODuplicates:OWarnings:O,sql>dropindexIndeUtimo:IROR1064(42000):YouhasanerrorinyorSQLsyntax：,sql>dropindexndex.stuno- ontest;【ROR1146(42S02):Tablvtest.tstvdoesn'texist,sql>dropindexindex.stuno- >onstulnfo:eryOK.Orowsaffected(0.02sec)Kords:ODuplicates:OYarniogs:O,sql>dropindextndex.3tUnoandcourno- >3tandcour:ryOK.Oro<2affected»0.Olsee)cords:ODuplicates:OVarnings:O1>CXazorrlacv¼wviw.stuinfoas->select->fromstiofo:TOK.Orosaffct”(0.02se),sql>createviewview-stuinfo-stuancouras一,selectstulnfo.stuno,stuinfo.stunaae,Stuandcour.COUmOvStuandcour.grade->frostinfo,Stuandcour一、EIerestuinfo.Stuno=STuandcour.suo:eryOK.Orowsaffected(0.02sec)ysql,selectfroavicw-stuinfo:InPtySet(0.00C)def“4afa网0iG3S40MUMElr*wcla9mVlMUWUMU工二I*<tve<-3tuiafo-ntacw3VU.MUMUNULL、.drf>orhlcityfXA<0131O20砌0BlDdfg：Idcocnxy5010010WU&，53C4o-hl0co«n:rLv¢u/6553d0Mll£019S*C<mrrwlIivwOSmx-l>wv<lMlt<(t.OSW)FXM,11xyr>*mj5t*)0WCmrno)此*lKfiM*aasvharO>XUtdtt*YE5N11L*Kaag*o(i2MU.4,YF511L卬dnc*havtiffu1nffl.r««sit¼t10.00M<t11,bvf÷*-soinfo-sttfidcovr.'Wl1：记YSOM1T3>,«5:v-l.*w*ircr'd>tcr't<c,vl>靛x"Xvi«v_s:uinfo_atjuct.F"ld2eX：lK"XCAtEvieat«>vaicIi,1CY£SU<tnarWrtBf，*VSMlJceovarctMa3jYESMU.E田<11r(1l>>TSXnJ.:rmv，rXUMXI,sql>SeleCtfrovievstuinfo_5tuancor:xvset(0.00sec)
