MySQL實(shí)驗(yàn)指導(dǎo)設(shè)計(jì)性實(shí)驗(yàn)參考答案

MySQL實(shí)驗(yàn)指導(dǎo)設(shè)計(jì)性實(shí)驗(yàn)參考答案實(shí)驗(yàn)一（1）設(shè)計(jì)能夠表示出生產(chǎn)廠商和產(chǎn)品關(guān)系的數(shù)據(jù)模型。其中生產(chǎn)廠商包括廠商名稱、地址、電話；產(chǎn)品包括品牌、型號(hào)、價(jià)格；生產(chǎn)廠商生產(chǎn)某產(chǎn)品的數(shù)量和日期。①確定產(chǎn)品實(shí)體和生產(chǎn)廠商實(shí)體的屬性和碼。生產(chǎn)廠商：廠商名稱、地址、電話，碼為廠商名稱產(chǎn)品：品牌、型號(hào)、價(jià)格，碼為品牌和型號(hào)②確定產(chǎn)品和生產(chǎn)廠商之間的聯(lián)系，給聯(lián)系命名并指出聯(lián)系的類型。一個(gè)生產(chǎn)廠商可以生產(chǎn)多種產(chǎn)品，一種產(chǎn)品也可以有多個(gè)生產(chǎn)廠商生產(chǎn)，所以產(chǎn)品和生產(chǎn)廠商間是多對(duì)多關(guān)系，即m：n，聯(lián)系名稱：生產(chǎn)。③確定聯(lián)系本身的屬性。聯(lián)系“生產(chǎn)”的屬性有數(shù)量和日期。④畫出產(chǎn)品與生產(chǎn)廠商關(guān)系的E-R圖。nmnm生產(chǎn)廠商廠商名稱yxkg號(hào)地址電話生產(chǎn)產(chǎn)品價(jià)格品牌日期數(shù)量型號(hào)（2）設(shè)計(jì)能夠表現(xiàn)出車隊(duì)、車輛和司機(jī)關(guān)系的數(shù)據(jù)模型。①確定車隊(duì)、車輛和司機(jī)實(shí)體的屬性和碼。車隊(duì)：車隊(duì)號(hào)、車隊(duì)名，碼為車隊(duì)號(hào)

車輛：車牌號(hào)、廠家，出廠日期，碼為車牌號(hào)

司機(jī)：司機(jī)編號(hào)、姓名、電話，碼為司機(jī)編號(hào)②確定實(shí)體之間的聯(lián)系，給聯(lián)系命名并指出聯(lián)系的類型。每個(gè)車隊(duì)可聘用若干司機(jī)，但每個(gè)司機(jī)只能應(yīng)聘于一個(gè)車隊(duì)，車隊(duì)與司機(jī)聯(lián)系類型是1：n，聯(lián)系名稱：聘用；每個(gè)車隊(duì)可以擁有若干車輛，但每輛車只能屬于一個(gè)車隊(duì)，車隊(duì)與車輛聯(lián)系類型是1：n，聯(lián)系名稱：擁有；每個(gè)司機(jī)可以使用多輛車，每輛車可被多個(gè)司機(jī)使用，車輛與司機(jī)聯(lián)系類型是m:n，聯(lián)系名稱為：使用。③確定聯(lián)系本身的屬性。聯(lián)系“聘用”的屬性有“聘用開始時(shí)間”和“聘期”兩個(gè)屬性，聯(lián)系“使用“有“使用日期”和“公里數(shù)”兩個(gè)屬性。④畫出E-R圖。11車隊(duì)電話廠家使用車輛司機(jī)編號(hào)牌照號(hào)司機(jī)出廠日期姓名車隊(duì)號(hào)車隊(duì)名擁有聘用nmn1n聘用開始時(shí)間聘期使用日期公里數(shù)實(shí)驗(yàn)三1.CREATEDATABASEstudentsdb;2.USEstudentsdb;3.CREATETABLEstudent_info(學(xué)號(hào)char(4)NOTNULLPRIMARYKEY,姓名char(8)NOTNULL,性別char(2),出生日期date,家庭住址varchar(50));ALTERTABLEstudent_infoMODIFY姓名char(8)NULL;ALTERTABLEstudent_infoCHANGE家庭住址地址varchar(50);ALTERTABLEstudent_infoADD備注varchar(50);ALTERTABLEstudent_infoDROP出生日期;CREATETABLEstuSELECT*FROMstudent_info;DROPTABLEstu,student_info;DROPDATABASEstudentsdb;實(shí)驗(yàn)四1.INSERTINTOsalaryVALUES('7369',8000,1320),('7499',12000,1463),('7521',11500,1463),('7566',13000,1566);ALTERTABLEsalaryADDsalFLOAT;3.SETSQL_SAFE_UPDATES=0;UPDATEsalarySETsal=income-outcome;4.DELETEFROMsalaryWHEREincome>=8000ANDincome<=10000;5.DROPTABLEsalary;6.DROPDATABASEyggl;實(shí)驗(yàn)五SELECTCOUNT(DISTINCT學(xué)號(hào))選課人數(shù)FROMgrade;SELECT姓名,出生日期FROMstudent_infoWHERE學(xué)號(hào)IN('0001','0002','0003','0004');3.INSERTINTOgrade(學(xué)號(hào),課程編號(hào))VALUES('0004','0001');4.SELECT學(xué)號(hào),課程編號(hào)FROMgradeWHERE分?jǐn)?shù)ISNULL;5.SETSQL_SAFE_UPDATES=0;DELETEFROMgradeWHERE分?jǐn)?shù)ISNULL;SELECT*FROMgradeWHERE分?jǐn)?shù)>=90and(課程編號(hào)='0001'or課程編號(hào)='0002');7.SELECT學(xué)號(hào),SUM(分?jǐn)?shù))總分FROMgradeGROUPBY學(xué)號(hào)HAVINGSUM(分?jǐn)?shù))>400ORDERBY總分;8.SELECTMAX(分?jǐn)?shù))最高分,MIN(分?jǐn)?shù))最低分,MAX(分?jǐn)?shù))-MIN(分?jǐn)?shù))分差FROMgradeWHERE課程編號(hào)='0001';9.SELECT學(xué)號(hào),COUNT(*)課程數(shù)FROMgradeWHERE分?jǐn)?shù)>70GROUPBY學(xué)號(hào)HAVING課程數(shù)>=3;10.SELECT課程編號(hào),AVG(分?jǐn)?shù))平均分FROMgradeGROUPBY課程編號(hào)HAVING平均分BETWEEN80AND90;實(shí)驗(yàn)六1.SELECTs.學(xué)號(hào),姓名,分?jǐn)?shù)FROMstudent_infos,curriculumc,gradegWHEREs.學(xué)號(hào)=g.學(xué)號(hào)ANDg.課程編號(hào)=c.課程編號(hào)AND課程名稱='C語(yǔ)言程序設(shè)計(jì)'AND分?jǐn)?shù)>(SELECTavg(分?jǐn)?shù))FROMgradeg,curriculumcWHEREg.課程編號(hào)=c.課程編號(hào)AND課程名稱='C語(yǔ)言程序設(shè)計(jì)');2.SELECT姓名,課程名稱,分?jǐn)?shù)FROMstudent_infosLEFTOUTERJOINgradegONs.學(xué)號(hào)=g.學(xué)號(hào)LEFTOUTERJOINcurriculumcONg.課程編號(hào)=c.課程編號(hào);3.SELECT課程名稱FROMcurriculumWHERE課程編號(hào)IN(SELECT課程編號(hào)FROMgradeWHERE學(xué)號(hào)=(SELECT學(xué)號(hào)FROMstudent_infoWHERE姓名='張青平'));4.SELECTCOUNT(*)FROMgradeWHERE課程編號(hào)='0001'AND分?jǐn)?shù)=(SELECTMAX(分?jǐn)?shù))FROMgradeWHERE課程編號(hào)='0001');5.SELECT姓名FROMstudent_infosWHERE學(xué)號(hào)IN(SELECT學(xué)號(hào)FROMgradeWHERE課程編號(hào)='0001')ORDERBY(SELECT分?jǐn)?shù)FROMgradegWHERE課程編號(hào)='0001'ANDs.學(xué)號(hào)=g.學(xué)號(hào))DESC;實(shí)驗(yàn)七CREATEDATABASEjob;USEjob;CREATETABLEuser(useridINTNOTNULL,usernameVARCHAR(20)NOTNULL,passwdVARCHAR(20)NOTNULL,infoTEXT,UNIQUEINDEXindex_uid(useridDESC),INDEXindex_user(username,passwd),FULLTEXTINDEXindex_info(info));3.CREATETABLEinformation(idINTNOTNULL,nameVARCHAR(20)NOTNULL,sexVARCHAR(4)NOTNULL,birthdayDATE,addressVARCHAR(50),telVARCHAR(20),picBLOB);4.CREATEINDEXindex_nameONinformation(name);5.CREATEINDEXindex_birONinformation(birthday,address);6.ALTERTABLEinformationADDINDEXindex_id(idASC);7.DROPINDEXindex_userONuser;8.ALTERTABLEinformationDROPINDEXindex_id;實(shí)驗(yàn)八1.USEstudentsdb;CREATEVIEWv_studentASSELECT學(xué)號(hào),姓名,出生日期,家庭住址FROMstudent_infoWHERE姓名LIKE'張%'AND出生日期>='2000/01/01';SELECT*FROMv_student;2.CREATEVIEWv_cntASSELECT課程編號(hào),COUNT(*)人數(shù)FROMgradeWHERE分?jǐn)?shù)>=90GROUPBY課程編號(hào);SELECT*FROMv_cnt;3.CREATEVIEWv_gradeASSELECT課程名稱,MAX(分?jǐn)?shù))最高分,MIN(分?jǐn)?shù))最低分,AVG(分?jǐn)?shù))平均分FROMgradegINNERJOINcurriculumcONg.課程編號(hào)=c.課程編號(hào)GROUPBY課程名稱;SELECT*FROMv_grade;4.INSERTINTOv_student(學(xué)號(hào),姓名,出生日期)VALUES('0010','張三豐','2000-03-23');SELECT*FROMv_student;SELECT*FROMstudent_info;5.UPDATEv_studentSET家庭住址='廣州市中山路3號(hào)'WHERE學(xué)號(hào)='0010';6.INSERTINTOv_student(學(xué)號(hào),姓名,出生日期)VALUES('0011','趙海棠','2001-11-12');SELECT*FROMv_student;SELECT*FROMstudent_info;7.ALTERVIEWv_studentASSELECT學(xué)號(hào),姓名,出生日期,家庭住址FROMstudent_infoWHERE姓名LIKE'張%'AND出生日期>='2000/01/01'WITHCHECKOPTION;8.INSERTINTOv_student(學(xué)號(hào),姓名,出生日期)VALUES('0012','李春桃','2000-1-12');插入失敗，因?yàn)樗迦氲男彰c創(chuàng)建視圖的條件不符。9.DELETEFROMv_studentWHERE學(xué)號(hào)='0010';10.DELETEFROMstudent_infoWHERE學(xué)號(hào)='0011';不能通過視圖v_student刪除0011的記錄。實(shí)驗(yàn)九1.USEstudentsdb;CREATETABLEcASSELECT*FROMcurriculum;CREATETABLEgASSELECT*FROMgrade;2.ALTERTABLEcADDPRIMARYKEY(課程名稱);3.INSERTINTOc(課程編號(hào),課程名稱)VALUES('0006','計(jì)算機(jī)應(yīng)用基礎(chǔ)');4.ALTERTABLECDROPPRIMARYKEY;5.ALTERTABLEcADDPRIMARYKEY(課程編號(hào));ALTERTABLEgADDFOREIGNKEY(課程編號(hào))REFERENCESc(課程編號(hào))ONDELETECASCADE;6.DELETEFROMcWHERE課程編號(hào)='0001';SELECT*FROMg;ALTERTABLEgADDCHECK(分?jǐn)?shù)>=0);INSERTINTOgVALUES('0004','0001',-80);ALTERTABLEcADDCONSTRAINTuq_nameUNIQUE(課程名稱);DROPTABLEc,g;實(shí)驗(yàn)十1.SELECT姓名,year(current_date())-year(出生日期)年齡FROMstudent_info;2.SELECT姓名,CASEWHEN分?jǐn)?shù)>=90THEN'優(yōu)秀'WHEN分?jǐn)?shù)>=80THEN'良好'WHEN分?jǐn)?shù)>=70THEN'中'WHEN分?jǐn)?shù)>=60THEN'及格'ELSE'不及格'ENDAS成績(jī)等級(jí)FROMstudent_infos,gradegWHEREs.學(xué)號(hào)=g.學(xué)號(hào)and課程編號(hào)='0001';3.DELIMITER@@CREATEFUNCTIONnum_func(cnameVARCHAR(50))RETURNSINTBEGINDECLAREnumINT;SELECTCOUNT(*)INTOnumFROMgradeg,curriculumc WHEREg.課程編號(hào)=c.課程編號(hào)and課程名稱=cname;RETURNnum;END@@SELECTnum_func('C語(yǔ)言程序設(shè)計(jì)');4.DELIMITER@@CREATEFUNCTIONf_fac(nint)RETURNSINTBEGINDECLAREi,sINT;SETi=1;SETs=1;WHILEi<=nDOSETs=s*i;SETi=i+1;ENDWHILE;RETURNs;END@@DELIMITER;SELECTf_fac(4);5.DELIMITER@@CREATEFUNCTIONf_stu(sidchar(4),typeint)RETURNSVARCHAR(50)BEGINCASEtypeWHEN1THENRETURN(SELECT姓名FROMstudent_infoWHERE學(xué)號(hào)=sid);WHEN2THENRETURN(SELECT家庭住址FROMstudent_infoWHERE學(xué)號(hào)=sid);ELSERETURN('ERROR');ENDCASE;END@@DELIMITER;SELECTf_stu('0001',1);DROPFUNCTIONf_stu;實(shí)驗(yàn)十一1.（1）DELIMITER@@CREATEPROCEDUREc_name(INnamevarCHAR(50))BEGINSELECT課程名稱,count(*)不及格人數(shù)FROMgradeg,curriculumcWHEREg.課程編號(hào)=c.課程編號(hào)and課程名稱=nameand分?jǐn)?shù)>=90;END@@（2）DELIMITER;CALLc_name('C語(yǔ)言程序設(shè)計(jì)');2.（1）DELIMITER@@CREATEPROCEDUREc_proc(INcnoCHAR(4),OUTcnamevarchar(50),OUTcxfint)BEGINSELECT課程名稱,學(xué)分INTOcname,cxfFROMcurriculumWHERE課程編號(hào)=cno;END@@（2）DELIMITER;CALLc_proc('0002',@name,@xf);（3）SELECT@name,@xf;3.DELIMITER@@CREATEPROCEDUREcurrAdd(INcidchar(4),INcnameVARCHAR(50),INcreditint)BEGININSERTINTOcurriculumVALUES(cid,cname,credit);END@@DELIMITER;CALLcurrAdd('0006','Java程序設(shè)計(jì)','3');SELECT*FROMcurriculumWHERE課程編號(hào)='0006';4.DELIMITER@@CREATEPROCEDUREcomp(INcid1char(4),INcid2char(4),OUTresultint)BEGINDECLAREmax1,max2int;SETmax1=(SELECTMAX(分?jǐn)?shù))FROMgradeWHERE課程編號(hào)=cid1);SETmax2=(SELECTMAX(分?jǐn)?shù))FROMgradeWHERE課程編號(hào)=cid2);IFmax1>max2THENSETresult=0;ELSESETresult=1;ENDIF;END@@CALLcomp('0001','0002',@result);SELECT@result;實(shí)驗(yàn)十二2.DELIMITER@@CREATEFUNCTIONcount_sch()RETURNSINTBEGINRETURN(SELECTCOUNT(*)FROMsch);END@@DELIMITER;SELECTcount_sch();3.DELIMITER@@CREATEPROCEDUREadd_id(OUTcountINT,OUTsumINT)BEGINDECLAREiINT;DECLAREitmpINT;DECLAREcur_idCURSORFORSELECTidFROMsch;SELECTcount_sch()INTOcount;SETi=1;SETsum=0;OPENcur_id;WHILEi<=countDOFETCHcur_idINTOitmp;SETsum=sum+itmp;SETi=i+1;ENDWHILE;CLOSEcur_id;END@@CALLadd_id(@x,@y);select@x,@y;4.DELIMITER@@CREATEPROCEDUREcal(INcidCHAR(4),OUTresultFLOAT)BEGINDECLAREiFLOATDEFAULT0;DECLAREsFLOATDEFAULT0;DECLAREscoreINT;DECLAREflagBOOLEANDEFAULTTRUE;DECLAREs_curCURSORFORSELECT分?jǐn)?shù)FROMgradeWHERE課程編號(hào)=cid;DECLARECONTINUEHANDLERFORNOTFOUNDSETflag=FALSE;OPENs_cur;FETCHs_curINTOscore;WHILEflagDOSETs=s+1;IFscore>=90THENSETi=i+1;ENDIF;FETCHs_curINTOscore;ENDWHILE;SETresult=i/s;END@@CALLcal('0002',@result);SELECT@result;實(shí)驗(yàn)十三1.DELIMITER@@CREATETRIGGERcno_triAFTERUPDATEONcourseFOREACHROWBEGINUPDATEscSET課程編號(hào)=new.課程編號(hào)WHERE課程編號(hào)=old.課程編號(hào);END@@2.DELIMITER;UPDATEcourseSET課程編號(hào)='0008'WHERE課程編號(hào)='0002';SELECT*FROMscWHERE課程編號(hào)='0008';3.CREATETABLEdel_course(課程編號(hào)char(4),課程名稱varchar(50));CREATETRIGGERcourse_triAFTERDELETEONcourseFOREACHROWINSERTINTOdel_courseVALUES(old.課程編號(hào),old.課程名稱);DELETEFROMcourseWHERE課程編號(hào)='0008';SELECT*FROMdel_course;4.DELIMITER@@CREATEPROCEDUREtran_save()BEGINSTARTTRANSACTION;INSERTINTOcourseVALUES('0010','Python數(shù)據(jù)分析',3);SAVEPOINTsp01;DELETEFROMcourseWHERE課程編號(hào)='0010';ROLLBACKTOSAVEPOINTsp01;SELECT*FROMcourseWHERE課程編號(hào)='0010';END@@DELIMITER;CALLtran_save();5.LOCKTABLEScourseREAD;6.LOCKTABLESscWRITE;7.UNLOCKTABLES;實(shí)驗(yàn)十四1.usemysql;2.CREATEUSERnewAdmin@localhostIDENTIFIEDBY'pw1';3.GRANTSELECT,UPDATE(分?jǐn)?shù))ONstudentsdb.gradeTO'newAdmin'@'localhost';4.select*fromstudentsdb.grade;5.REVOKESELECT,UPDATEONstudentsdb.gradeFROM'newAdmin'@'localhost';6.DROPUSER'newAdmin'@'localhost';實(shí)驗(yàn)十六1.CREATEDATABASEBook;2.USEBook;CREATETABLEbook(bnochar(4)notnullprimarykey,bnamechar(20),authorchar(10),publishchar(20),pubdatedatetime);INSERTINTObookVALUES('0001','數(shù)據(jù)庫(kù)原理','張小海','人民郵電出版社','2020-10-01'),('0002','軟件工程','李妙莎','高等教育出版社','2020-08-09'),('0003','操作系統(tǒng)','錢東升','人民郵電出版社','2021-03-06'),('0004','數(shù)據(jù)結(jié)構(gòu)','魯明浩','清華大學(xué)出版社','2021-05-28'),('0005','編譯原理','張悅','高等教育出版社','2020-10-30');CREATETABLEreader(rnochar(4)notnullprimarykey,rnamechar(10));INSERTINTOreaderVALUES('0001','全志忠'),('0002','孫佳佳'),('0003','司馬靜');CREATETABLEborrow(borrownointnotnullprimarykey,bnochar(4),rnochar(4),borrowdatedatetime);INSERTINTOborrowVALUES(1,'0001','0001','2021-11-15'),(2,'0002','0001','2021-11-20'),(3,'0002','0002','2021-11-30'),(4,'0003','0002','2021-12-05'),(5,'0003','0001','2021-12-12'),(6,'0004','0001','2021-12-21');3.altertableborrowaddconstraintFK_BNOforeignkey(bno)referencesbook(bno);altertableborrowaddconstraintFK_RNOforeignkey(rno)referencesreader(rno);4.selectpubdatefrombookwhereauthor='張小海'andbname='數(shù)據(jù)庫(kù)原理';5.select*fromborrowwherebnoin(selectbnofrombookwherebname='軟件工程');6.select*frombookorderbypublishdesc,pubdatea