MySQL实验指导设计性实验参考答案

MySQL实验指导设计性实验参考答案实验一（1）设计能够表示出生产厂商和产品关系的数据模型。其中生产厂商包括厂商名称、地址、电话；产品包括品牌、型号、价格；生产厂商生产某产品的数量和日期。①确定产品实体和生产厂商实体的属性和码。生产厂商：厂商名称、地址、电话，码为厂商名称产品：品牌、型号、价格，码为品牌和型号②确定产品和生产厂商之间的联系，给联系命名并指出联系的类型。一个生产厂商可以生产多种产品，一种产品也可以有多个生产厂商生产，所以产品和生产厂商间是多对多关系，即m：n，联系名称：生产。③确定联系本身的属性。联系“生产”的属性有数量和日期。④画出产品与生产厂商关系的E-R图。nmnm生产厂商厂商名称yxkg号地址电话生产产品价格品牌日期数量型号（2）设计能够表现出车队、车辆和司机关系的数据模型。①确定车队、车辆和司机实体的属性和码。车队：车队号、车队名，码为车队号

车辆：车牌号、厂家，出厂日期，码为车牌号

司机：司机编号、姓名、电话，码为司机编号②确定实体之间的联系，给联系命名并指出联系的类型。每个车队可聘用若干司机，但每个司机只能应聘于一个车队，车队与司机联系类型是1：n，联系名称：聘用；每个车队可以拥有若干车辆，但每辆车只能属于一个车队，车队与车辆联系类型是1：n，联系名称：拥有；每个司机可以使用多辆车，每辆车可被多个司机使用，车辆与司机联系类型是m:n，联系名称为：使用。③确定联系本身的属性。联系“聘用”的属性有“聘用开始时间”和“聘期”两个属性，联系“使用“有“使用日期”和“公里数”两个属性。④画出E-R图。11车队电话厂家使用车辆司机编号牌照号司机出厂日期姓名车队号车队名拥有聘用nmn1n聘用开始时间聘期使用日期公里数实验三1.CREATEDATABASEstudentsdb;2.USEstudentsdb;3.CREATETABLEstudent_info(学号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学号)选课人数FROMgrade;SELECT姓名,出生日期FROMstudent_infoWHERE学号IN('0001','0002','0003','0004');3.INSERTINTOgrade(学号,课程编号)VALUES('0004','0001');4.SELECT学号,课程编号FROMgradeWHERE分数ISNULL;5.SETSQL_SAFE_UPDATES=0;DELETEFROMgradeWHERE分数ISNULL;SELECT*FROMgradeWHERE分数>=90and(课程编号='0001'or课程编号='0002');7.SELECT学号,SUM(分数)总分FROMgradeGROUPBY学号HAVINGSUM(分数)>400ORDERBY总分;8.SELECTMAX(分数)最高分,MIN(分数)最低分,MAX(分数)-MIN(分数)分差FROMgradeWHERE课程编号='0001';9.SELECT学号,COUNT(*)课程数FROMgradeWHERE分数>70GROUPBY学号HAVING课程数>=3;10.SELECT课程编号,AVG(分数)平均分FROMgradeGROUPBY课程编号HAVING平均分BETWEEN80AND90;实验六1.SELECTs.学号,姓名,分数FROMstudent_infos,curriculumc,gradegWHEREs.学号=g.学号ANDg.课程编号=c.课程编号AND课程名称='C语言程序设计'AND分数>(SELECTavg(分数)FROMgradeg,curriculumcWHEREg.课程编号=c.课程编号AND课程名称='C语言程序设计');2.SELECT姓名,课程名称,分数FROMstudent_infosLEFTOUTERJOINgradegONs.学号=g.学号LEFTOUTERJOINcurriculumcONg.课程编号=c.课程编号;3.SELECT课程名称FROMcurriculumWHERE课程编号IN(SELECT课程编号FROMgradeWHERE学号=(SELECT学号FROMstudent_infoWHERE姓名='张青平'));4.SELECTCOUNT(*)FROMgradeWHERE课程编号='0001'AND分数=(SELECTMAX(分数)FROMgradeWHERE课程编号='0001');5.SELECT姓名FROMstudent_infosWHERE学号IN(SELECT学号FROMgradeWHERE课程编号='0001')ORDERBY(SELECT分数FROMgradegWHERE课程编号='0001'ANDs.学号=g.学号)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学号,姓名,出生日期,家庭住址FROMstudent_infoWHERE姓名LIKE'张%'AND出生日期>='2000/01/01';SELECT*FROMv_student;2.CREATEVIEWv_cntASSELECT课程编号,COUNT(*)人数FROMgradeWHERE分数>=90GROUPBY课程编号;SELECT*FROMv_cnt;3.CREATEVIEWv_gradeASSELECT课程名称,MAX(分数)最高分,MIN(分数)最低分,AVG(分数)平均分FROMgradegINNERJOINcurriculumcONg.课程编号=c.课程编号GROUPBY课程名称;SELECT*FROMv_grade;4.INSERTINTOv_student(学号,姓名,出生日期)VALUES('0010','张三丰','2000-03-23');SELECT*FROMv_student;SELECT*FROMstudent_info;5.UPDATEv_studentSET家庭住址='广州市中山路3号'WHERE学号='0010';6.INSERTINTOv_student(学号,姓名,出生日期)VALUES('0011','赵海棠','2001-11-12');SELECT*FROMv_student;SELECT*FROMstudent_info;7.ALTERVIEWv_studentASSELECT学号,姓名,出生日期,家庭住址FROMstudent_infoWHERE姓名LIKE'张%'AND出生日期>='2000/01/01'WITHCHECKOPTION;8.INSERTINTOv_student(学号,姓名,出生日期)VALUES('0012','李春桃','2000-1-12');插入失败，因为所插入的姓名与创建视图的条件不符。9.DELETEFROMv_studentWHERE学号='0010';10.DELETEFROMstudent_infoWHERE学号='0011';不能通过视图v_student删除0011的记录。实验九1.USEstudentsdb;CREATETABLEcASSELECT*FROMcurriculum;CREATETABLEgASSELECT*FROMgrade;2.ALTERTABLEcADDPRIMARYKEY(课程名称);3.INSERTINTOc(课程编号,课程名称)VALUES('0006','计算机应用基础');4.ALTERTABLECDROPPRIMARYKEY;5.ALTERTABLEcADDPRIMARYKEY(课程编号);ALTERTABLEgADDFOREIGNKEY(课程编号)REFERENCESc(课程编号)ONDELETECASCADE;6.DELETEFROMcWHERE课程编号='0001';SELECT*FROMg;ALTERTABLEgADDCHECK(分数>=0);INSERTINTOgVALUES('0004','0001',-80);ALTERTABLEcADDCONSTRAINTuq_nameUNIQUE(课程名称);DROPTABLEc,g;实验十1.SELECT姓名,year(current_date())-year(出生日期)年龄FROMstudent_info;2.SELECT姓名,CASEWHEN分数>=90THEN'优秀'WHEN分数>=80THEN'良好'WHEN分数>=70THEN'中'WHEN分数>=60THEN'及格'ELSE'不及格'ENDAS成绩等级FROMstudent_infos,gradegWHEREs.学号=g.学号and课程编号='0001';3.DELIMITER@@CREATEFUNCTIONnum_func(cnameVARCHAR(50))RETURNSINTBEGINDECLAREnumINT;SELECTCOUNT(*)INTOnumFROMgradeg,curriculumc WHEREg.课程编号=c.课程编号and课程名称=cname;RETURNnum;END@@SELECTnum_func('C语言程序设计');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学号=sid);WHEN2THENRETURN(SELECT家庭住址FROMstudent_infoWHERE学号=sid);ELSERETURN('ERROR');ENDCASE;END@@DELIMITER;SELECTf_stu('0001',1);DROPFUNCTIONf_stu;实验十一1.（1）DELIMITER@@CREATEPROCEDUREc_name(INnamevarCHAR(50))BEGINSELECT课程名称,count(*)不及格人数FROMgradeg,curriculumcWHEREg.课程编号=c.课程编号and课程名称=nameand分数>=90;END@@（2）DELIMITER;CALLc_name('C语言程序设计');2.（1）DELIMITER@@CREATEPROCEDUREc_proc(INcnoCHAR(4),OUTcnamevarchar(50),OUTcxfint)BEGINSELECT课程名称,学分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程序设计','3');SELECT*FROMcurriculumWHERE课程编号='0006';4.DELIMITER@@CREATEPROCEDUREcomp(INcid1char(4),INcid2char(4),OUTresultint)BEGINDECLAREmax1,max2int;SETmax1=(SELECTMAX(分数)FROMgradeWHERE课程编号=cid1);SETmax2=(SELECTMAX(分数)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分数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数据分析',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(分数)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','数据库原理','张小海','人民邮电出版社','2020-10-01'),('0002','软件工程','李妙莎','高等教育出版社','2020-08-09'),('0003','操作系统','钱东升','人民邮电出版社','2021-03-06'),('0004','数据结构','鲁明浩','清华大学出版社','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='数据库原理';5.select*fromborrowwherebnoin(selectbnofrombookwherebname='软件工程');6.select*frombookorderbypublishdesc,pubdatea