MySQL實驗指導(dǎo)設(shè)計性實驗參考答案

MySQL實驗指導(dǎo)設(shè)計性實驗參考答案實驗一（1）設(shè)計能夠表示出生產(chǎn)廠商和產(chǎn)品關(guān)系的數(shù)據(jù)模型。其中生產(chǎn)廠商包括廠商名稱、地址、電話；產(chǎn)品包括品牌、型號、價格；生產(chǎn)廠商生產(chǎn)某產(chǎn)品的數(shù)量和日期。①確定產(chǎn)品實體和生產(chǎn)廠商實體的屬性和碼。生產(chǎn)廠商：廠商名稱、地址、電話，碼為廠商名稱產(chǎn)品：品牌、型號、價格，碼為品牌和型號②確定產(chǎn)品和生產(chǎn)廠商之間的聯(lián)系，給聯(lián)系命名并指出聯(lián)系的類型。一個生產(chǎn)廠商可以生產(chǎn)多種產(chǎn)品，一種產(chǎn)品也可以有多個生產(chǎn)廠商生產(chǎn)，所以產(chǎn)品和生產(chǎn)廠商間是多對多關(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號地址電話生產(chǎn)產(chǎn)品價格品牌日期數(shù)量型號（2）設(shè)計能夠表現(xiàn)出車隊、車輛和司機關(guān)系的數(shù)據(jù)模型。①確定車隊、車輛和司機實體的屬性和碼。車隊：車隊號、車隊名，碼為車隊號

車輛：車牌號、廠家，出廠日期，碼為車牌號

司機：司機編號、姓名、電話，碼為司機編號②確定實體之間的聯(lián)系，給聯(lián)系命名并指出聯(lián)系的類型。每個車隊可聘用若干司機，但每個司機只能應(yīng)聘于一個車隊，車隊與司機聯(lián)系類型是1：n，聯(lián)系名稱：聘用；每個車隊可以擁有若干車輛，但每輛車只能屬于一個車隊，車隊與車輛聯(lián)系類型是1：n，聯(lián)系名稱：擁有；每個司機可以使用多輛車，每輛車可被多個司機使用，車輛與司機聯(lián)系類型是m:n，聯(lián)系名稱為：使用。③確定聯(lián)系本身的屬性。聯(lián)系“聘用”的屬性有“聘用開始時間”和“聘期”兩個屬性，聯(lián)系“使用“有“使用日期”和“公里數(shù)”兩個屬性。④畫出E-R圖。11車隊電話廠家使用車輛司機編號牌照號司機出廠日期姓名車隊號車隊名擁有聘用nmn1n聘用開始時間聘期使用日期公里數(shù)實驗三1.CREATEDATABASEstudentsdb;2.USEstudentsdb;3.CREATETABLEstudent_info(學(xué)號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;實驗四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;實驗五SELECTCOUNT(DISTINCT學(xué)號)選課人數(shù)FROMgrade;SELECT姓名,出生日期FROMstudent_infoWHERE學(xué)號IN('0001','0002','0003','0004');3.INSERTINTOgrade(學(xué)號,課程編號)VALUES('0004','0001');4.SELECT學(xué)號,課程編號FROMgradeWHERE分數(shù)ISNULL;5.SETSQL_SAFE_UPDATES=0;DELETEFROMgradeWHERE分數(shù)ISNULL;SELECT*FROMgradeWHERE分數(shù)>=90and(課程編號='0001'or課程編號='0002');7.SELECT學(xué)號,SUM(分數(shù))總分FROMgradeGROUPBY學(xué)號HAVINGSUM(分數(shù))>400ORDERBY總分;8.SELECTMAX(分數(shù))最高分,MIN(分數(shù))最低分,MAX(分數(shù))-MIN(分數(shù))分差FROMgradeWHERE課程編號='0001';9.SELECT學(xué)號,COUNT(*)課程數(shù)FROMgradeWHERE分數(shù)>70GROUPBY學(xué)號HAVING課程數(shù)>=3;10.SELECT課程編號,AVG(分數(shù))平均分FROMgradeGROUPBY課程編號HAVING平均分BETWEEN80AND90;實驗六1.SELECTs.學(xué)號,姓名,分數(shù)FROMstudent_infos,curriculumc,gradegWHEREs.學(xué)號=g.學(xué)號ANDg.課程編號=c.課程編號AND課程名稱='C語言程序設(shè)計'AND分數(shù)>(SELECTavg(分數(shù))FROMgradeg,curriculumcWHEREg.課程編號=c.課程編號AND課程名稱='C語言程序設(shè)計');2.SELECT姓名,課程名稱,分數(shù)FROMstudent_infosLEFTOUTERJOINgradegONs.學(xué)號=g.學(xué)號LEFTOUTERJOINcurriculumcONg.課程編號=c.課程編號;3.SELECT課程名稱FROMcurriculumWHERE課程編號IN(SELECT課程編號FROMgradeWHERE學(xué)號=(SELECT學(xué)號FROMstudent_infoWHERE姓名='張青平'));4.SELECTCOUNT(*)FROMgradeWHERE課程編號='0001'AND分數(shù)=(SELECTMAX(分數(shù))FROMgradeWHERE課程編號='0001');5.SELECT姓名FROMstudent_infosWHERE學(xué)號IN(SELECT學(xué)號FROMgradeWHERE課程編號='0001')ORDERBY(SELECT分數(shù)FROMgradegWHERE課程編號='0001'ANDs.學(xué)號=g.學(xué)號)DESC;實驗七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;實驗八1.USEstudentsdb;CREATEVIEWv_studentASSELECT學(xué)號,姓名,出生日期,家庭住址FROMstudent_infoWHERE姓名LIKE'張%'AND出生日期>='2000/01/01';SELECT*FROMv_student;2.CREATEVIEWv_cntASSELECT課程編號,COUNT(*)人數(shù)FROMgradeWHERE分數(shù)>=90GROUPBY課程編號;SELECT*FROMv_cnt;3.CREATEVIEWv_gradeASSELECT課程名稱,MAX(分數(shù))最高分,MIN(分數(shù))最低分,AVG(分數(shù))平均分FROMgradegINNERJOINcurriculumcONg.課程編號=c.課程編號GROUPBY課程名稱;SELECT*FROMv_grade;4.INSERTINTOv_student(學(xué)號,姓名,出生日期)VALUES('0010','張三豐','2000-03-23');SELECT*FROMv_student;SELECT*FROMstudent_info;5.UPDATEv_studentSET家庭住址='廣州市中山路3號'WHERE學(xué)號='0010';6.INSERTINTOv_student(學(xué)號,姓名,出生日期)VALUES('0011','趙海棠','2001-11-12');SELECT*FROMv_student;SELECT*FROMstudent_info;7.ALTERVIEWv_studentASSELECT學(xué)號,姓名,出生日期,家庭住址FROMstudent_infoWHERE姓名LIKE'張%'AND出生日期>='2000/01/01'WITHCHECKOPTION;8.INSERTINTOv_student(學(xué)號,姓名,出生日期)VALUES('0012','李春桃','2000-1-12');插入失敗，因為所插入的姓名與創(chuàng)建視圖的條件不符。9.DELETEFROMv_studentWHERE學(xué)號='0010';10.DELETEFROMstudent_infoWHERE學(xué)號='0011';不能通過視圖v_student刪除0011的記錄。實驗九1.USEstudentsdb;CREATETABLEcASSELECT*FROMcurriculum;CREATETABLEgASSELECT*FROMgrade;2.ALTERTABLEcADDPRIMARYKEY(課程名稱);3.INSERTINTOc(課程編號,課程名稱)VALUES('0006','計算機應(yīng)用基礎(chǔ)');4.ALTERTABLECDROPPRIMARYKEY;5.ALTERTABLEcADDPRIMARYKEY(課程編號);ALTERTABLEgADDFOREIGNKEY(課程編號)REFERENCESc(課程編號)ONDELETECASCADE;6.DELETEFROMcWHERE課程編號='0001';SELECT*FROMg;ALTERTABLEgADDCHECK(分數(shù)>=0);INSERTINTOgVALUES('0004','0001',-80);ALTERTABLEcADDCONSTRAINTuq_nameUNIQUE(課程名稱);DROPTABLEc,g;實驗十1.SELECT姓名,year(current_date())-year(出生日期)年齡FROMstudent_info;2.SELECT姓名,CASEWHEN分數(shù)>=90THEN'優(yōu)秀'WHEN分數(shù)>=80THEN'良好'WHEN分數(shù)>=70THEN'中'WHEN分數(shù)>=60THEN'及格'ELSE'不及格'ENDAS成績等級FROMstudent_infos,gradegWHEREs.學(xué)號=g.學(xué)號and課程編號='0001';3.DELIMITER@@CREATEFUNCTIONnum_func(cnameVARCHAR(50))RETURNSINTBEGINDECLAREnumINT;SELECTCOUNT(*)INTOnumFROMgradeg,curriculumc WHEREg.課程編號=c.課程編號and課程名稱=cname;RETURNnum;END@@SELECTnum_func('C語言程序設(shè)計');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é)號=sid);WHEN2THENRETURN(SELECT家庭住址FROMstudent_infoWHERE學(xué)號=sid);ELSERETURN('ERROR');ENDCASE;END@@DELIMITER;SELECTf_stu('0001',1);DROPFUNCTIONf_stu;實驗十一1.（1）DELIMITER@@CREATEPROCEDUREc_name(INnamevarCHAR(50))BEGINSELECT課程名稱,count(*)不及格人數(shù)FROMgradeg,curriculumcWHEREg.課程編號=c.課程編號and課程名稱=nameand分數(shù)>=90;END@@（2）DELIMITER;CALLc_name('C語言程序設(shè)計');2.（1）DELIMITER@@CREATEPROCEDUREc_proc(INcnoCHAR(4),OUTcnamevarchar(50),OUTcxfint)BEGINSELECT課程名稱,學(xué)分INTOcname,cxfFROMcurriculumWHERE課程編號=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è)計','3');SELECT*FROMcurriculumWHERE課程編號='0006';4.DELIMITER@@CREATEPROCEDUREcomp(INcid1char(4),INcid2char(4),OUTresultint)BEGINDECLAREmax1,max2int;SETmax1=(SELECTMAX(分數(shù))FROMgradeWHERE課程編號=cid1);SETmax2=(SELECTMAX(分數(shù))FROMgradeWHERE課程編號=cid2);IFmax1>max2THENSETresult=0;ELSESETresult=1;ENDIF;END@@CALLcomp('0001','0002',@result);SELECT@result;實驗十二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分數(shù)FROMgradeWHERE課程編號=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;實驗十三1.DELIMITER@@CREATETRIGGERcno_triAFTERUPDATEONcourseFOREACHROWBEGINUPDATEscSET課程編號=new.課程編號WHERE課程編號=old.課程編號;END@@2.DELIMITER;UPDATEcourseSET課程編號='0008'WHERE課程編號='0002';SELECT*FROMscWHERE課程編號='0008';3.CREATETABLEdel_course(課程編號char(4),課程名稱varchar(50));CREATETRIGGERcourse_triAFTERDELETEONcourseFOREACHROWINSERTINTOdel_courseVALUES(old.課程編號,old.課程名稱);DELETEFROMcourseWHERE課程編號='0008';SELECT*FROMdel_course;4.DELIMITER@@CREATEPROCEDUREtran_save()BEGINSTARTTRANSACTION;INSERTINTOcourseVALUES('0010','Python數(shù)據(jù)分析',3);SAVEPOINTsp01;DELETEFROMcourseWHERE課程編號='0010';ROLLBACKTOSAVEPOINTsp01;SELECT*FROMcourseWHERE課程編號='0010';END@@DELIMITER;CALLtran_save();5.LOCKTABLEScourseREAD;6.LOCKTABLESscWRITE;7.UNLOCKTABLES;實驗十四1.usemysql;2.CREATEUSERnewAdmin@localhostIDENTIFIEDBY'pw1';3.GRANTSELECT,UPDATE(分數(shù))ONstudentsdb.gradeTO'newAdmin'@'localhost';4.select*fromstudentsdb.grade;5.REVOKESELECT,UPDATEONstudentsdb.gradeFROM'newAdmin'@'localhost';6.DROPUSER'newAdmin'@'localhost';實驗十六1.CREATEDATABASEBook;2.USEBook;CREATETABLEbook(bnochar(4)notnullprimarykey,bnamechar(20),authorchar(10),publishchar(20),pubdatedatetime);INSERTINTObookVALUES('0001','數(shù)據(jù)庫原理','張小海','人民郵電出版社','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ù)庫原理';5.select*fromborrowwherebnoin(selectbnofrombookwherebname='軟件工程');6.select*frombookorderbypublishdesc,pubdatea