MySQL数据库技术及实验指导(实验参考)

1MySQL数据库技术与实验指导（第1版）钱雪忠、王燕玲、张平编著清华大学出版社2011.092实验1数据库系统基础操作进入MySQL的官方下载页面：如果想找旧的发布版本，可进入页面：例1-1有一个脚本文件（test.sql），文件内容：Showdatabases;Createdatabasetest;Usetest;Createtabletable_1(Iint)ENGINE=MyISAM;执行之。解：C:\mysql-hlocalhost-uroot-pc:\test.sql或mysqlsourcec:\test.sql实验2MySQL数据库基础操作例2-1创建jxgl数据库。解：mysqlcreatedatabasejxgl;例2-2查看本机服务器上数据库。解：mysqlshowdatabases;或：mysqlshowdatabaseslike'my%';例2-3进入jxgl数据库。解：mysqlUSEjxgl;例2-4在命令行环境中，创建和删除数据库jxgl：解：创建数据库：C:\mysqladmin-hlocalhost-uroot-pcreatejxgl删除数据库：C:\mysqladmin-hlocalhost-uroot-pdropjxgl实验3表、ER图、索引与视图的基础操作CREATETABLEIFNOTEXISTS`jxgl`.`sc`(`sno`CHAR(7)NOTNULL,3`cno`CHAR(2)NOTNULL,`grade`INTNULL,PRIMARYKEY(`sno`,`cno`),INDEX`sc_ibfk_1`(`sno`ASC),INDEX`sc_ibfk_2`(`cno`ASC),CONSTRAINT`sc_ibfk_1`FOREIGNKEY(`sno`)REFERENCES`jxgl`.`student`(`sno`)ONDELETERESTRICTONUPDATERESTRICT,CONSTRAINT`sc_ibfk_2`FOREIGNKEY(`cno`)REFERENCES`jxgl`.`course`(`cno`)ONDELETERESTRICTONUPDATERESTRICT)ENGINE=InnoDB;例3-1列出jxgl数据库中所有表。解：mysqlusejxgl;mysqlshowtables;或：C:\mysqlshow-hlocalhost-uroot-pjxgl例3-2列出jxgl数据库中表student的列。解：mysqlusejxgl;mysqlshowcolumnsfromstudent;或：mysqlshowcolumnsfromjxgl.student;或：C:\mysqlshow-hlocalhost-uroot-pjxglstudent例3-3列出jxgl数据库中表的详细信息。解：mysqlusejxgl;mysqlshowtablestatus;或：C:\mysqlshow--status-hlocalhost-uroot-pjxgl例3-4列出jxgl数据库中表sc的索引。解：mysqlusejxgl;mysqlshowindexfromsc;或：mysqlshowindexfromjxgl.sc;例3-5使用SQL语句创建示例数据库（jxgl）：其中，学生表要求学号为主键，性别默认为男，取值必须为男或女，年龄取值在15到45之间。课程表（course）要求主键为课程编号，外键为先修课号，参照课程表的主键（cno）。选修表（sc）要求主键为（学号，课程编号），学号为外键，参照学生表中的学号，课程编号为外键，参照课程表中的课程编号；成绩不为空时必须在0到100之间。解：CreateTableStudent(SnoCHAR(7)NOTNULL,SnameVARCHAR(16),SsexCHAR(2)DEFAULT'男'CHECK(Ssex='男'ORSsex='女'),SageSMALLINTCHECK(Sage=15ANDSage=45),SdeptCHAR(2),PRIMARYKEY(Sno))ENGINE=InnoDB;4CreateTableCOURSE(CnoCHAR(2)NOTNULL,CnameVARCHAR(20),CpnoCHAR(2),CreditSMALLINT,PRIMARYKEY(Cno),foreignkey(cpno)referencescourse(cno))ENGINE=InnoDB;Createtablesc(snochar(7)notnull,cnochar(2)notnull,gradesmallintnullcheck(gradeisnullor(gradebetween0and100)),Primarykey(sno,cno),Foreignkey(sno)referencesstudent(sno),Foreignkey(cno)referencescourse(cno))ENGINE=InnoDB;例3-6在表student中增加属性生日（birthday）。解：ALTERTABLEstudentADDbirthdaydatetime;例3-7删除例3-6中增加的属性生日（birthday）。解：ALTERTABLEstudentDROPbirthday;例3-8在表student中属性sname上建立索引(sn)。解：altertablestudentadduniquesn(sname);例3-9删除表sc。解：DROPTABLEsc;例3-10在数据库jxgl中创建视图v，查询学生姓名，课程名及其所学课程的成绩。解：mysqlusejxgl--先选择jxgl数据库为当前数据库Databasechangedmysqlcreateviewv(sname,cname,grade)asselectsname,cname,gradefromstudent,course,sc-wherestudent.sno=sc.snoandsc.cno=ccourse.cno;例3-11显示数据库jxgl中视图v创建的信息。解：mysqlSHOWCREATEVIEWv;实验4SQL语言——SELECT查询操作例4-1查询考试成绩大于等于90的学生学号。解：SELECTDISTINCTSNO5FROMSCWHEREGRADE=90;例4-2查年龄大于18，并且不是信息系（IS）与数学系（MA）的学生姓名和性别。解：SELECTSNAME,SSEXFROMSTUDENTWHERESAGE18ANDSDEPTNOTIN('IS','MA');例4-3查以“MIS_”开头，且倒数第二个汉字为“导”字的课程的详细信息。解：SELECT*FROMCOURSEWHERECNAMELIKE'MIS#_%导_'ESCAPE'#';例4-4查询选修计算机系（CS）选修了2门及以上课程的学生学号。解：SELECTSTUDENT.SNOFROMSTUDENT,SCWHERESDEPT='CS'ANDSTUDENT.SNO=SC.SNOGROUPBYSTUDENT.SNOHAVINGCOUNT(*)=2;例4-5查询student表与sc表的广义笛卡尔积。解：SELECTSTUDENT.*,SC.*FROMSTUDENTCROSSJOINSC;例4-6查询student表与sc表基于学号sno的等值连接。解：SELECT*FROMSTUDENT,SCWHERESTUDENT.SNO=SC.SNO;例4-7查询student表与sc表基于学号sno的自然连接。解：SELECTSTUDENT.*,SC.CNO,SC.GRADEFROMSTUDENT,SCWHERESTUDENT.SNO=SC.SNO;例4-8查询课程号的间接先修课程号。解：SELECTFIRST.CNO,SECOND.CNOFROMCOURSEFIRST,COURSESECONDWHEREFIRST.CPNO=SECOND.CNO;例4-9查询学生及其课程、成绩等情况（不管是否选课，均需列出学生信息）。解：SELECTSTUDENT.SNO,SNAME,SSEX,SAGE,SDEPT,CNO,GRADEFROMSTUDENTLEFTOUTERJOINSCONSTUDENT.SNO=SC.SNO;例4-10查询学生及其课程成绩与课程及其学生选修成绩的明细情况（要求学生与课程均全部列出）。解：SELECTSTUDENT.SNO,SNAME,SSEX,SAGE,SDEPT,COURSE.CNO,GRADE,CNAME,CPNO,CCREDITFROMSTUDENTLEFTOUTERJOINSCONSTUDENT.SNO=SC.SNOFULLOUTERJOINCOURSEONSC.CNO=COURSE.CNO;说明：因MySQL不支持“FULLOUTERJOIN”，为此上命令运行会出错的。可以把“FULLOUTERJOIN”用“…LEFTOUTERJOIN…UNION…RIGHTOUTERJOIN…”来变通实现，为此，查询命令可改为：SELECTa.SNO,a.SNAME,a.SSEX,a.SAGE,a.SDEPT,C.CNO,b.GRADE,c.CNAME,c.CPNO,6c.CREDITFROMSTUDENTaLEFTOUTERJOINSCbONa.SNO=b.SNOLEFTOUTERJOINCOURSEcONb.CNO=C.CNOUNIONSELECTa2.SNO,a2.SNAME,a2.SSEX,a2.SAGE,a2.SDEPT,c2.CNO,b2.GRADE,c2.CNAME,c2.CPNO,c2.CREDITFROMSTUDENTa2LEFTOUTERJOINSCb2ONa2.SNO=b2.SNORIGHTOUTERJOINCOURSEc2ONb2.CNO=C2.CNO;例4-11查询性别为男、课程成绩及格的学生信息及课程号、成绩。解：SELECTSTUDENT.*,CNO,GRADEFROMSTUDENTINNERJOINSCONSTUDENT.SNO=SC.SNOWHERESSEX='男'ANDGRADE=60;例4-12查询与“钱横”在同一系学习的学生信息。解：SELECT*FROMSTUDENTWHERESDEPTIN(SELECTSDEPTFROMSTUDENTWHERESNAME='钱横')；例4-13找出同系、同年龄、同性别的学生。解：SELECTT.*FROMSTUDENTASTWHERE(T.sdept,T.SAGE,T.SSEX)IN(SELECTSDEPT,SAGE,SSEXFROMSTUDENTASSWHERES.SNOT.SNO);例4-14查询选修了课程名为“数据库系统”的学生学号，姓名和所在系。解：SELECTSNO,SNAME,SDEPTFROMSTUDENTWHERESNOIN(SELECTSNOFROMSCWHERECNOIN(SELECTCNOFROMCOURSEWHERECNAME='数据库系统'));或SELECTSTUDENT.SNO,SNAME,SDEPTFROMSTUDENTINNERJOINSCONSTUDENT.SNO=SC.SNOINNERJOINCOURSEONSC.CNO=COURSE.CNO;例4-15检索至少不学2和4课程的学生学号和姓名。解：SELECTSNO,SNAMEFROMSTUDENTWHERESNONOTIN(SELECTSNOFROMSCWHERECNOIN('2','4'));例4-16查询其他系中比信息系IS所有学生年龄均大的学生名单，并排序输出。解：SELECTSNAMEFRO