《數(shù)據(jù)庫(kù)技術(shù)及應(yīng)用（MySQL）》實(shí)例源文件匯 楊宏霞 第3-9章

《數(shù)據(jù)庫(kù)技術(shù)及應(yīng)用（MySQL）》實(shí)例源文件【例3-1】CREATEDATABASEstudent;【例3-2】CREATEDATABASEIFNOTEXISTSstudent;【例3-3】SHOWDATABASES;【例3-4】USEstudent;【例3-5】ALTERDATABASEstudentDEFAULTCHARACTERSET=gbkDEFAULTCOLLATEgbk_chinese_ci;【例3-6】DROPDATABASEstudent;或者DROPDATABASEIFEXISTSstudent;【例4-1】CREATETABLEstudent(SnoVARCHAR(10)NOTNULL,SnameVARCHAR(20)NOTNULL,SsexCHAR(2)DEFAULT'男',SbirthDATE,ZnoVARCHAR(4),SclassVARCHAR(10),PRIMARYKEY(Sno));【例4-2】CREATETABLEcourse(CnoVARCHAR(8)NOTNULL,CnameVARCHAR(50)NOTNULL,CcreditINT(11),CdeptVARCHAR(20),PRIMARYKEY(Cno));【例4-3】CREATETABLEspecialty(ZnoVARCHAR(4)NOTNULL,ZnameVARCHAR(50)NOTNULL,PRIMARYKEY(Zno));【例4-4】CREATETABLEsc(SnoVARCHAR(10)NOTNULL,CnoVARCHAR(8)NOTNULL,GradeINT(11)NOTNULL,PRIMARYKEY(Sno,Cno),CONSTRAINTfCnoFOREIGNKEY(Cno)REFERENCEScourse(Cno),CONSTRAINTfSnoFOREIGNKEY(Sno)REFERENCESstudent(Sno));或者CREATETABLEsc(SnoVARCHAR(10)NOTNULLREFERENCESstudent(Sno),CnoVARCHAR(8)NOTNULLREFERENCEScourse(Cno),GradeINT(11)NOTNULL,PRIMARYKEY(Sno,Cno));【例4-5】SHOWTABLES;【例4-6】DESCstudent;或者SHOWCOLUMNSFROMstudent;或者SHOWCREATETABLEstudent;【例4-7】ALTERTABLEstudentADDScomeDATETIME;【例4-8】ALTERTABLEstudentMODIFYScomeDATE;【例4-9】ALTERTABLEstudentDROPCOLUMNScome;【例4-10】ALTERTABLEstudentRENAMEASstu;【例4-11】droptablestu;【例4-12】CREATETABLEstudent1(SidINTNOTNULLPRIMARYKEY,SnameVARCHAR(20),SageINT,INDEXname_index(Sname(10)));【例4-13】CREATEINDEXSage_indexONstudent1(Sage);【例4-14】CREATETABLEstudent1(SidINTNOTNULLPRIMARYKEY,SnameVARCHAR(20),SageINT,UNIQUEINDEXid_index(SidASC));【例4-15】CREATEUNIQUEINDEXname_indexONstudent1(Sname);【例4-16】CREATETABLEstudent2(idINTNOTNULLPRIMARYKEYAUTO_INCREMENT,infoCHAR(20),FULLTEXTINDEXinfo_index(info));【例4-17】CREATETABLEstudent3(SidINTNOTNULLPRIMARYKEY,SnameCHAR(20),SageINT,INDEXname_age_index(Sname,Sage));【例4-18】ALTERTABLEtable_nameDROPINDEXindex_name;【例4-19】DROPINDEXname_indexONstudent1;【例4-20】CREATETABLEstudent_new(SidINTNOTNULLAUTO_INCREMENT,SnoVARCHAR(10)NOTNULL,SnameVARCHAR(20)NOTNULL,SsexCHAR(2)DEFAULT'男',SbirthDATENULL,ZonVARCHAR(4)NULL,SclassVARCHAR(10)NULL,PRIMARYKEY(Sid),UNIQUE(Sno));【例4-21】CREATETABLEsc(SnoVARCHAR(10)NOTNULL,CnoVARCHAR(8)NOTNULL,GradeINT(11)NOTNULL,PRIMARYKEY(Sno,Cno),CONSTRAINTfcnoFOREIGNKEY(Cno)REFERENCEScourse(Cno),CONSTRAINTfsnoFOREIGNKEY(Sno)REFERENCESstudent(Sno));【例4-22】CREATETABLEstudent4(SnoVARCHAR(10)NOTNULL,SnameVARCHAR(20)NOTNULL,SageINTCHECK(Sage>=18ANDSage<=22),PRIMARYKEY(Sno));【例4-23】CREATETABLEsc_test(SnoVARCHAR(10)NOTNULL,CnoVARCHAR(8)NOTNULL,GradeINTCHECK(Grade>0ANDGrade<=100),PRIMARYKEY(Sno,Cno),CONSTRAINTfCnoFOREIGNKEY(Cno)REFERENCEScourse(Cno),CONSTRAINTfSnoFOREIGNKEY(Sno)REFERENCESstudent(Sno));【例4-24】INSERTINTOstudent(Sno,Sname,Ssex,Sbirth,Zno,Sclass)VALUES(20231042,'景文青','女','2005-01-08',1002,'計(jì)算機(jī)2301'),(20231043,'謝鑫','男','2005-02-14',1002,'計(jì)算機(jī)2301'),(20231156,'田潤(rùn)卓','男','2005-10-11',1002,'計(jì)算機(jī)2302'),……;或者INSERTINTOstudentVALUES(20231042,'景文青','女','2005-01-08',1002,'計(jì)算機(jī)2301'),(20231043,'謝鑫','男','2005-02-14',1002,'計(jì)算機(jī)2301'),(20231156,'田潤(rùn)卓','男','2005-10-11',1002,'計(jì)算機(jī)2302'),……;【例4-25】INSERTINTOsc(Sno,Cno)VALUES(20232144,18132370);【例4-26】UPDATEstudentSETSclass='通信2301'WHERESname='田潤(rùn)卓';【例4-27】UPDATEcourseSETCcredit=Ccredit+1;【例4-28】UPDATEscSETGrade=Grade+5;【例4-29】DELETEFROMstudentWHERESname='唐曉';【例4-30】DELETEFROMstudentWHERESclass='會(huì)計(jì)2301';【例4-31】TRUNCATETABLEsc;【例5-1】SELECT*FROMstudent;【例5-2】SELECTSno,SnameFROMstudent;【例5-3】SELECTSname,Sno,SclassFROMstudent;【例5-4】SELECTCOUNT(DISTINCTSno)FROMsc;【例5-5】SELECTSname,YEAR(NOW())-YEAR(Sbirth)AS'年齡'FROMstudent;【例5-6】SELECTSnameAS學(xué)生姓名,YEAR(Sbirth)AS出生年份FROMstudent;【例5-7】SELECTSnameFROMstudentWHERESclass='計(jì)算機(jī)2301';【例5-8】SELECTSname,SbirthFROMstudentWHERESbirth<'2004-01-01';【例5-9】SELECTDISTINCTSnoFROMscWHEREGrade<60;【例5-10】SELECTSname,SnoFROMstudentWHERESclass='計(jì)算機(jī)2302'ANDSsex='男';【例5-11】SELECTSname,Sclass,SbirthFROMstudentWHERESbirthBETWEEN'2003-01-01'AND'2004-12-31';【例5-12】SELECTSname,SsexFROMstudentWHERESclassIN('計(jì)算機(jī)2301','通信2301');【例5-13】SELECTSname,SnoFROMstudentWHERESnameLIKE'王%';【例5-14】SELECTSname,SnoFROMstudentWHERESnameLIKE'_文%';【例5-15】SELECTSname,SnoFROMstudentWHERESnameNOTLIKE'王%';【例5-16】SELECTSname,SnoFROMstudentWHERESnameREGEXP'^王';或者SELECTSname,SnoFROMstudentWHERESnameREGEXP'王+';【例5-17】SELECT*FROMscWHEREGradeISNULL;【例5-18】SELECT*FROMscWHEREGradeISNOTNULL;【例5-19】SELECTSno,GradeFROMscWHERECno='18110140'ORDERBYGradeDESC;【例5-20】SELECT*FROMstudentORDERBYSclass,SbirthDESC;【例5-21】SELECTCOUNT(DISTINCTSno)FROMsc;【例5-22】SELECTCOUNT(DISTINCTSno)FROMsc;【例5-23】SELECTCno,COUNT(Sno)AS選課人數(shù)FROMscGROUPBYCno;【例5-24】SELECTSno,COUNT(Cno)AS選課數(shù)量FROMscGROUPBYSnoHAVINGCOUNT(Cno)<2;【例5-25】SELECT*FROMstudentINNERJOINscONstudent.Sno=sc.Sno;等價(jià)寫法：SELECT*FROMstudent,scWHEREstudent.Sno=sc.Sno;【例5-26】SELECTstudent.Sno,Ssex,Sbirth,Sclass,Cno,GradeFROMstudent,scWHEREstudent.Sno=sc.Sno;【例5-27】SELECTStudent.Sno,Ssex,Sbirth,Sclass,Cno,GradeFROMstudent,scWHEREstudent.Sno=sc.SnoANDsc.Cno='18132220'ANDGrade>=90;等價(jià)寫法：SELECTStudent.Sno,Ssex,Sbirth,Sclass,Cno,GradeFROMstudentJOINscONstudent.Sno=sc.SnoWHEREsc.Cno='18132220'ANDsc.Grade>90;【例5-28】SELECTStudent.Sno,Sname,Cname,GradeFROMstudent,sc,courseWHEREstudent.Sno=sc.SnoANDsc.Cno=course.Cno;【例5-29】SELECTstudent.Sno,Sname,Ssex,Sbirth,Sclass,Cno,GradeFROMstudentLEFTOUTERJOINscONstudent.Sno=sc.Sno;【例5-30】SELECTSno,Grade,course.*FROMscRIGHTOUTERJOINcourseONcourse.Cno=sc.Cno;【例5-31】SELECTstudent.Sno,Sname,Ssex,Sbirth,Sclass,Cno,GradeFROMstudentFULLOUTERJOINscONstudent.Sno=sc.Sno;使用UNION和LEFTJOIN以及RIGHTJOIN的組合來(lái)實(shí)現(xiàn)全外連接功能SELECTstudent.Sno,Sname,Ssex,Sbirth,Sclass,Cno,GradeFROMstudentLEFTOUTERJOINscONstudent.Sno=sc.SnoUNIONSELECTstudent.Sno,Sname,Ssex,Sbirth,Sclass,Cno,GradeFROMstudentRIGHTOUTERJOINscONstudent.Sno=sc.Sno;【例5-32】SELECTs1.SnoFROMscASs1JOINscASs2ONs1.Sno=s2.SnoWHEREs1.Cno='18132370'ANDs2.Cno='11110140';【例5-33】SELECT*FROMstudentWHERESnoIN(SELECTSnoFROMscWHEREGrade>ANY(SELECTGradeFROMsc));【例5-34】SELECT*FROMstudentWHERESnoIN(SELECTSnoFROMscWHEREGrade>SOME(SELECTGradeFROMsc));【例5-35】SELECT*FROMstudentWHERESsex='女'ANDSbirth<ALL(SELECTSbirthFROMstudentWHERESsex='男');【例5-36】第一步SELECTSclassFROMstudentWHERESname='謝鑫';第二步SELECT*FROMstudentWHERESclass='計(jì)算機(jī)2301';以上兩個(gè)步驟可以用帶有IN關(guān)鍵字的子查詢來(lái)實(shí)現(xiàn)SELECT*FROMstudentWHERESclassIN(SELECTSclassFROMstudentWHERESname='謝鑫');【例5-37】SELECT*FROMstudentWHERESclass=(SELECTSclassFROMstudentWHERESname='謝鑫');【例5-38】SELECTSnameFROMstudentWHEREEXISTS(SELECT*FROMscWHEREstudent.Sno=sc.SnoANDCno='18132220');【例5-39】SELECT*FROMstudentWHERESbirth<='2005-1-1'UNIONSELECT*FROMstudentWHERESsex='女';【例5-40】CREATEVIEWstudent_view1ASSELECT*FROMstudent;【例5-41】CREATEVIEWstudent_view2(Sname,Cname,Grade)ASSELECTSname,Cname,GradeFROMstudents,coursec,scWHEREs.Sno=sc.SnoANDc.Cno=sc.Cno;【例5-42】SELECT*FROMstudent_view2WHERESname='謝鑫';【例5-43】SHOWCREATEVIEWstudent_view2;或者DESCstudent_view2;【例5-44】CREATEORREPLACEVIEWstudent_view2(姓名,選修課,成績(jī))ASSELECTSname,Cname,GradeFROMstudents,coursec,scWHEREs.Sno=sc.SnoANDc.Cno=sc.Cno;【例5-45】DROPVIEWIFEXISTSstudent_view1;【例6-1】SELECTabs(-1.1),abs(1.1),abs(3);【例6-2】SELECTpi();【例6-3】SELECTceil(8.6),floor(-7.9);【例6-4】SELECTsqrt(5.4);【例6-5】SELECTround(9.68,1);【例6-6】SELECTpow(-3,3),pow(2.56,2),pow(90,1/3);【例6-7】SELECTlength('hello'),char_length('MySQL8.0數(shù)據(jù)庫(kù)'),length('MySQL8.0數(shù)據(jù)庫(kù)');【例6-8】SELECTconcat('MongoDB,','MySQL數(shù)據(jù)庫(kù)');【例6-9】SELECTinsert('DATABASEjxxs,',9,6,'usersm');【例6-10】SELECTlower('CreateTable'),upper('AlterEvent');【例6-11】SELECTltrim('firstsecond'),rtrim('firstsecond'),trim('firstsecond');【例6-12】SELECTstrcmp('Compute','compute'),strcmp('Compute','Compute123');【例6-13】SELECTleft('MongoDBRedisMySQL',7),right('MongoDBRedisMySQL',5),substring('MongoDBRedisMySQL',8,5);【例6-14】SELECTlocate('cu','cucumber'),locate('數(shù)據(jù)庫(kù)','關(guān)系數(shù)據(jù)庫(kù)，非關(guān)系型數(shù)據(jù)庫(kù)');【例6-15】SELECTcurdate(),curtime(),now();【例6-16】SELECTmonth(curdate()),monthname(curdate());【例6-17】SELECTdayofweek('2024-02-09'),dayname('2024-02-09');【例6-18】SELECTweek('2024-01-01'),week('2024-12-31');【例6-19】SELECTdate_add(curdate(),interval'3:02'day_hour)ast1,date_sub('2024-05-0103:10:10',interval'20:30'minute_second)ast2;【例6-20】SELECTdatediff('2024-06-30','2024-01-01');【例6-21】SELECTdate_format('2024-12-01:12:20:30','%e,%M,%y')asd1,date_format('2024-01-01:14:20:30','%y,%M,%e,%h,%w')asd2;【例6-22】SELECTcharset('course');【例6-23】--創(chuàng)建表語(yǔ)句CREATETABLEmember(NOINTAUTO_INCREMENTNOTNULLPRIMARYKEY,NAMEVARCHAR(20));--添加記錄語(yǔ)句INSERTINTOmemberVALUES(NULL,'王小');INSERTINTOmemberVALUES(NULL,'李惠');INSERTINTOmemberVALUES(NULL,'馬茜');--執(zhí)行上述語(yǔ)句后，查詢最后一次執(zhí)行INSERT語(yǔ)句增加記錄時(shí)的自動(dòng)自增列的值SELECTLAST_INSERT_ID();【例6-24】DELIMITER$$CREATEFUNCTIONfindname(idVARCHAR(255))RETURNSVARCHAR(255)DETERMINISTICBEGINRETURN(SELECTSnameFROMstudentWHERESno=id);END$$DELIMITER;【例6-25】SELECTfindname('20231042');【例6-26】DROPFUNCTIONIFEXISTSfindname;【例6-27】DECLAREscore,gradeINTDEFAULT0;【例6-28】DECLAREvar1INT;DECLAREvar2CHAR(30);SETvar1=1;SETvar2='數(shù)據(jù)庫(kù)';【例6-29】DECLAREvar_cnameCHAR(30);SELECTCnameINTOvar_CnameFROMcourseWHERECno='18130320';【例6-30】SHOWVARIABLESLIKE'admin_ssl_c%';【例6-31】SELECT@@version;【例6-32】SETGLOBALmax_connections=1000;【例6-33】SET@name='張華';SELECT@grade:=GradeFROMscWHERESno='20031042'ANDCno='18032370';SELECTZnameFROMspecialtyLIMIT1INTO@specialtyname;SELECT@name,@grade,@specialtyname;【例6-34】DELIMITER$$CREATEFUNCTIONfunc_mul()RETURNSINTDETERMINISTICBEGINDECLAREnum1INT;DECLAREnum2INT;DECLAREmnumINT;SETnum1=5;SETnum2=2;SETmnum=num1*num2;RETURNmnum;END$$DELIMITER;SELECTfunc_mul();【例6-35】DELIMITER$$CREATEFUNCTIONfunc_isPosOrNeg(numINT)RETURNSINTDETERMINISTICBEGINDECLAREXINTDEFAULT0;IFnum>0THENSETX=1;ELSESETX=-1;ENDIF;RETURNX;END$$DELIMITER;SELECTfunc_isPosOrNeg(15);【例6-36】DELIMITER$$CREATEFUNCTIONfunc_exchange(numINT)RETURNSVARCHAR(20)DETERMINISTICBEGINDECLAREseasonVARCHAR(20)DEFAULT'';CASEnumWHEN1THENSETseason='spring';WHEN2THENSETseason='summer';WHEN3THENSETseason='autumn';WHEN4THENSETseason='winter';ELSESETseason='error';ENDCASE;RETURNseason;END$$DELIMITER;SELECTfunc_exchange(4);【例6-37】DELIMITER$$CREATEFUNCTIONfunc_islevel(scoreINT)RETURNSVARCHAR(30)DETERMINISTICBEGINDECLAREgradeVARCHAR(30)DEFAULT'';CASEWHENscore>89THENSETgrade='優(yōu)秀';WHENscore>79THENSETgrade='良好';WHENscore>69THENSETgrade='中等';WHENscore>59THENSETgrade='及格';ELSESETgrade='不及格';ENDCASE;RETURNgrade;END$$DELIMITER;SELECTfunc_islevel(89);【例6-38】DELIMITER$$CREATEFUNCTIONfunc_sum()RETURNSINTDETERMINISTICBEGINDECLAREi,SUMINTDEFAULT0;SIGN:LOOPIFi>100THENLEAVESIGN;ELSESETSUM=SUM+i;SETi=i+1;ENDIF;ENDLOOPSIGN;RETURNSUM;END$$DELIMITER;SELECTfunc_sum();【例6-39】DELIMITER$$CREATEFUNCTIONfunc_muls()RETURNSINTDETERMINISTICBEGINDECLAREi,SUMINTDEFAULT1;WHILEi<11DOSETSUM=SUM*i;SETi=i+1;ENDWHILE;RETURNSUM;END$$DELIMITER;SELECTfunc_muls();【例6-40】DELIMITER$$CREATEFUNCTIONfunc_evens()RETURNSINTDETERMINISTICBEGINDECLAREi,SUMINTDEFAULT0;REPEATIF(i%2=0)THENSETSUM=SUM+i;ENDIF;SETi=i+1;UNTIL(i>10)ENDREPEAT;RETURNSUM;END$$DELIMITER;SELECTfunc_evens();【例7-1】DELIMITER$$CREATEPROCEDUREproSumCno(OUTsumcINT)BEGINSELECTCOUNT(*)INTOscFROMcourse;END$$DELIMITER;【例7-2】DELIMITER$$CREATEPROCEDUREprocStu(INidVARCHAR(20))BEGINSELECT*FROMstudentWHERESno=id;END$$DELIMITER;CALLprocStu('20231160',);【例7-3】SHOWPROCEDURESTATUSlike'pro%'\G;【例7-4】SHOWCREATEPROCEDUREprocStu\G;【例7-5】SELECT*FROMinformation_schema.routinesWHEREroutine_name='procStu'\G;【例7-6】SELECTspecific_name,sql_data_access,security_typeFROMinformation_schema.routinesWHEREroutine_name='procStu';ALTERPROCEDUREprocStuMODIFIESSQLDATASQLSECURITYINVOKER;SELECTspecific_name,sql_data_access,security_typeFROMinformation_schema.routinesWHEREroutine_name='procStu';【例7-7】DROPPROCEDUREIFEXISTSprocStu;【例7-8】DECLAREcur_ZameCURSORFORSELECTZno,ZnameFROMspecialty;【例7-9】OPENcur_Zame;【例7-10】FETCHcur_ZameINTOid,name;【例7-11】CLOSEcur_Zame;【例7-12】DELIMITER$$CREATEPROCEDUREproc_curc()BEGINDECLAREidVARCHAR(20)CHARACTERSETutf8;DECLARENAMEVARCHAR(20)CHARACTERSETutf8;DECLAREcurcCURSORFORSELECTCno,CnameFROMcourseWHERECcredit>=3;OPENcurc;FETCHcurcINTOid,NAME;SELECTid,NAME;CLOSEcurc;END$$DELIMITER;CALLproc_curc();【例7-13】DELIMITER$$CREATEPROCEDUREproc_while_cur()BEGINDECLAREflagINTDEFAULTFALSE;DECLAREidVARCHAR(20)CHARACTERSETutf8;DECLARENAMEVARCHAR(20)CHARACTERSETutf8;DECLAREloccurCURSORFORSELECTCno,CnameFROMcourseWHERECcredit>2;DECLARECONTINUEHANDLERFORNOTFOUNDSETflag=TRUE;OPENloccur;FETCHloccurINTOid,NAME;WHILE(NOTflag)DOSELECTid,NAME;FETCHloccurINTOid,NAME;ENDWHILE;CLOSEloccur;END;$$DELIMITER;CALLproc_while_cur();【例7-14】SET@c=0;CREATETRIGGERtrig_in_couAFTERINSERTONcourseFOREACHROWSET@c=@c+1;INSERTINTOCourseVALUES('20110114','計(jì)算機(jī)組成',3,'計(jì)算機(jī)');SELECT@c;【例7-15】CREATETRIGGERtrig_delete_sourceAFTERDELETEONstudentFOREACHROWDELETEFROMscWHERESno=OLD.Sno;SELECT*FROMscWHERESno='20231160';DELETEFROMstudentWHERESno='20231160';SELECT*FROMscWHERESno='20231160';【例7-16】SHOWTRIGGERS\G;【例7-17】DROPTRIGGERIFEXISTStrig_delete_source;【例7-18】SHOWVARIABLESLIKE'event_scheduler';SETGLOBALevent_scheduler=ON;【例7-19】CREATEEVENTinsert_course_eventONSCHEDULEATCURRENT_TIMESTAMP+INTERVAL10MINUTE+INTERVAL30SECONDONCOMPLETIONPRESERVEDOINSERTINTOcourseVALUES('58130080','云計(jì)算技術(shù)',3);【例7-20】CREATEEVENTdelete_sc_eventONSCHEDULEEVERY5SECONDONCOMPLETIONPRESERVEDODELETEFROMscWHEREGrade<=0;【例7-21】SHOWEVENTS;【例7-22】ALTEREVENTinsert_course_eventONSCHEDULEATCURRENT_TIMESTAMPONCOMPLETIONPRESERVERENAMETOnewinsert_c_eventDOINSERTINTOcourseVALUES('58130080','云計(jì)算技術(shù)',3);【例7-23】DROPEVENTIFEXISTSinsert_course_event;【例8-1】CREATEUSER'zhang'@'localhost'IDENTIFIEDBY'1234','jia'@'localhost'IDENTIFIEDBY'qwe','zhou'@'localhost'IDENTIFIEDBY'x456','yang'@'localhost'IDENTIFIEDBY'asd6';【例8-2】DROPUSER'yang'@'localhost';【例8-3】SETPASSWORDFOR'zhang'@'localhost'='123456';【例8-4】SHOWGRANTSFOR'jia'@'localhost';【例8-5】GRANTSELECT(sno,sname)ONjxxx.studentTO'zhang'@'localhost';【例8-6】--創(chuàng)建用戶CREATEUSER'zhu'@'localhost'IDENTIFIEDBY'1234','ma'@'localhost'IDENTIFIEDBY'5678';--授權(quán)GRANTSELECT,UPDATEONjxxx.studentTO'zhu'@'localhost','ma'@'localhost';【例8-7】GRANTCREATE,DROPON*.*TO'ma'@'localhost';【例8-8】GRANTSELECT,INSERTON*.*TO'zhang'@'localhost';【例8-9】GRANTALLONjxxx.*TO'zhou'@'localhost';【例8-10】GRANTCREATEUSERON*.*TO'zhou'@'localhost';【例8-11】SELECTuser,select_priv,insert_priv,create_priv,drop_priv,create_user_privFROMmysql.user;【例8-12】GRANTINSERT,UPDATEONjxxx.studentTO'ma'@'localhost'WITHGRANTOPTION;【例8-13】REVOKEINSERTONjxxx.studentFROM'zhang'@'localhost';【例8-14】SELECTdb,user,table_name,table_priv,column_privFROMmysql.tables_privWHEREtable_name='student';【例8-15】SHOWBINARYLOGS;PURGEMASTERLOGSTO'DESKTOP-ED41KRK-bin.000024';【例8-16】PURGEMASTERLOGSBEFORE'20240207';RESETMASTER;【例8-17】（1）查看慢查詢?nèi)罩竟δ?，具體SQL語(yǔ)句如下。showvariableslike'%slow%';showvariableslike'long_query_time%';（2）修改my.ini文件，具體修改如下。[mysqld]long_query_time=2slow_query_log=ON（3）重新啟動(dòng)MySQL服務(wù)，使用SHOW語(yǔ)句查看慢查詢?nèi)罩竟δ埽唧wSQL語(yǔ)句如下。showvariableslike'%slow%';showvariableslike'long_query_time%';【例8-18】使用SET語(yǔ)句來(lái)啟動(dòng)MySQL慢查詢?nèi)罩竟δ?。?）查看慢查詢?nèi)罩竟δ?，具體SQL語(yǔ)句如下。showvariableslike'%slow%';showvariableslike'long_query_time%';（2）開(kāi)啟慢查詢?nèi)罩竟δ?，同時(shí)設(shè)置超時(shí)時(shí)長(zhǎng)，具體SQL語(yǔ)句如下。setglobalslow_query_log=on;setgloballong_query_time=2;setsessionlong_query_time=2;（3）重新啟動(dòng)MySQL服務(wù)，使用SHOW語(yǔ)句查看慢查詢?nèi)罩竟δ?，具體SQL語(yǔ)句如下。showvariableslike'%slow%';showvariableslike'long_query_time%';【例8-19】查看MySQL慢查詢?nèi)罩緝?nèi)容。（1）查看慢查詢?nèi)罩疽蟮牟樵兂瑫r(shí)時(shí)長(zhǎng)，具體SQL語(yǔ)句如下，執(zhí)行結(jié)果如圖8-20所示。showvariableslike'long_query_time%';（2）MySQL中提供了一個(gè)計(jì)算表達(dá)式性能的函數(shù)BENCHMARK（count,expr），該函數(shù)會(huì)重復(fù)計(jì)算expr表達(dá)式count次，通過(guò)這種方式就可以模擬時(shí)間較長(zhǎng)的查詢，根據(jù)客戶端提示的執(zhí)行時(shí)間來(lái)得到BENCHMARK總共執(zhí)行所消耗的時(shí)間，只要超過(guò)設(shè)定的2秒就滿足條件。具體SQL語(yǔ)句如下。selectbenchmark(60000000,concat('a','b','1234'));（3）打開(kāi)慢查詢?nèi)罩疚募﨑ESKTOP-ED41KRK-slow.log。【例8-20】備份jxxx數(shù)據(jù)庫(kù)中的student表的數(shù)據(jù)到指定目錄。要求字段值如果是字符就使用雙引號(hào)標(biāo)注，字段值之間使用逗號(hào)隔開(kāi)，每一行以問(wèn)號(hào)作為結(jié)束標(biāo)志。具體的命令如下：select*fromjxxx.studentintooutfile"c:/programdata/mysql/mysqlserver8.0/uploads/student.txt"fieldsterminatedby','optionallyenclosedby'"'