MySQL教程（新体系-综合应用实例视频）（第4版） 习题-第08章-答案

参考答案习题-第08章一、选择题 1.C 2.A 3.B 4.D 5.C 6.D 7.D 8.A 9.D 10.B二、说明题 1.好处：MySQL利用索引加速了WHERE子句中与条件相匹配的行的搜索，或者说在执行连接时加快了与其他表中的行匹配的行的搜索。弊端：首先，索引是以文件的形式存储的，索引文件要占用磁盘空间。如果有大量的索引，索引文件可能会比数据文件更快地达到最大的文件尺寸。其次，在更新表中索引列上的数据时，对索引也需要更新，这可能需要重新组织一个索引，如果表中的索引很多，这是很浪费时间的。也就是说，这样就降低了添加、删除、修改和其他写入操作的效率。表中的索引越多，则更新表的时间就越长。 2.索引有普通索引（INDEX）、唯一性索引（UNIQUE）、主键（PRIMARYKEY）、前缀索引、全文索引（FULLTEXT）、哈希索引（HASH）等。 前缀索引：对文本的前几个字符（具体是几个在建立索引时指定）建立索引，这样建立起来的索引文件更小、检索速度更快。但是不能在ORDERBY或GROUPBY中使用前缀索引。 全文索引：在定义这种索引的列上支持值的全文检索，允许在这些索引列中插入重复值和空值。全文索引只能在char、varchar或text类型的列上创建，并且只能在MyISAM表中创建。 3.三者的区别： （1）主键唯一标识一条记录，不能有重复的，不允许为空；外键是另一表的主键，可以有重复的，也可以是空值；索引字段没有重复值，但可以有一个空值。 （2）主键用来保证数据完整性；外键用来和其他表建立关联；索引则是为了提高查询排序的速度。 （3）主键只能有一个；外键可以有多个；一个表可以有多个惟一索引。 PRIMARYKEY是主键索引，UNIQUE是唯一性索引，主键其实是唯一性索引的一种，与之区别在于：每个表只能有一个主键，但可以有多个唯一性索引。 4.有以下3种创建方法 （1）CREATEINDEX语句：可以在一个已有表上创建索引，一个表可以创建多个索引。 （2）ALTERTABLE语句：在修改表的时候向其中添加索引。 （3）CREATETABLE语句：在创建表的语句中包含索引的定义。三、编程题 1.查询成绩（cj）表中指定课程号（@mykch）的学号、成绩和等级。USExscj;SET@mykch='101';SELECT学号,成绩, (CASEWHEN成绩>=90THEN'优秀' ELSE(CASEWHEN成绩>=80THEN'良好' ELSE(CASEWHEN成绩>=70THEN'中等' ELSE(CASEWHEN成绩>=60THEN'及格' ELSE'不及格' END) END) END) END)AS等级 FROMcj WHERE课程号=@mykch; 2.查询成绩（cj）表指定年级（@mynj）的学号、成绩和满分150分成绩。 3.统计学生表（xs）总学分在50分以上的人数。SELECTCOUNT(学号)AS'总学分50以上的人数' FROMxs WHERE总学分>50; 4.查询成绩（cj）表指定课程号（@mykch）的学生的最高分、最低分和平均分。SET@mykch='101';SELECTMAX(成绩)AS'最高分',MIN(成绩)AS'最低分',AVG(成绩)AS'平均分' FROMcj WHERE课程号=@mykch; 5.查询所有学生选过的课程名和课程号。SELECT课程号,课程名 FROMkc WHERE课程号IN ( SELECTDISTINCT(课程号) FROMcj ); 6.查询选修了指定课程号（@mykch）且成绩在80分以上的学生姓名及成绩。SET@mykch='206';SELECT姓名,成绩 FROMxs,cj WHERExs.学号=cj.学号 AND课程号=@mykch AND成绩>80; 7.查询选修了指定课程名（@mykcm）课程且成绩在80分以上的学生学号、姓名、课程名及成绩。SET@mykcm='离散数学';SELECTxs.学号,姓名,课程名,成绩 FROMxs,kc,cj WHERExs.学号=cj.学号 ANDkc.课程号=cj.课程号 AND课程名=@mykcm AND成绩>80; 8.查询所有学生选过的课程名。SELECT课程名 FROMkc WHERE课程号IN ( SELECTDISTINCT(课程号) FROMcj ); 9.查询所有姓“王”的学号倒数或者学号第二个数字为0的学生的学号、姓名及专业名。SELECT学号,姓名,专业名 FROMxs WHERE姓名LIKE'王%' AND( SUBSTRING(学号,1,1)='0' ORSUBSTRING(学号,-2,1)='0' ); 10.查询学号里包含4、5、6或者学号以08结尾的学生学号、姓名和专业名。SELECT学号,姓名,专业名 FROMxs WHERELOCATE('4',学号)>0 ORLOCATE('5',学号)>0 ORLOCATE('6',学号)>0 OR学号LIKE'%08'; 11.查询在1993年1月～6月出生的学生情况。SELECT*FROMxs WHERE出生日期BETWEEN'1993-01-01'AND'1993-06-30'; 12.查询“计算机”或“通信工程”的学生的情况。 13.查询未选修指定课程名（@mykcm）的学生的姓名、学号、专业名。SET@mykcm='计算机基础';SELECT姓名,学号,专业名 FROMxs WHERE学号NOTIN ( SELECTxs.学号 FROMxs,kc,cj WHERExs.学号=cj.学号 ANDkc.课程号=cj.课程号 AND课程名=@mykcm ); 14.查询比所有计算机专业的学生年龄都大的学生学号、姓名、专业名、出生日期。SELECT学号,姓名,专业名,出生日期 FROMxs WHERE出生日期< ( SELECTMIN(出生日期) FROMxs WHERE专业名='计算机' ); 15.查询所有女学生的姓名、学号以及与指定学号（@myxh）学生的年龄差距。SET@myxh='081202';SELECT姓名,学号, FLOOR( DATEDIFF ( 出生日期,(SELECT出生日期FROMxsWHERE学号=@myxh) )/365)AS年龄差距 FROMxs WHERE性别=0; 16.统计各专业的学生数。SELECT专业名,COUNT(*)AS学生数 FROMxs GROUPBY专业名; 17.统计选修的各门课程的平均成绩和选修该课程的人数。SELECT课程号,AVG(成绩)AS平均成绩,COUNT(学号)AS选修人数 FROMcj GROUPBY课程号; 18.产生一个结果集，包括每个专业的男生人数、女生人数、总人数，以及学生总人数。SELECT专业名,性别,COUNT(*)AS人数 FROMxs GROUPBY专业名,性别 WITHROLLUP; 19.产生一个结果集，包括每门课程各专业的平均成绩、每门课程的总平均成绩和所有课程的总平均成绩。SELECT课程名,专业名,AVG(成绩)AS平均成绩 FROMcj,kc,xs WHEREcj.课程号=kc.课程号ANDcj.学号=xs.学号 GROUPBY课程名,专业名 WITHROLLUP; 20.计算平均成绩在85分以上的学生的学号和平均成绩。SELECT学号,AVG(成绩)AS平均成绩 FROMcj GROUPBY学号 HAVINGAVG(成绩)>=85; 21.查询选修课程超过2门且成绩都在80分以上的学生的学号。SELECT学号 FROMcj WHERE成绩>=80 GROUPBY学号 HAVINGCOUNT(*)>2; 22.查询通信工程专业平均成绩在85分以上的学生的学号和平均成绩。SELECT学号,AVG(成绩)AS平均成绩 FROMcj WHERE学号IN ( SELECT学号 FROMxs WHERE专业名='通信工程' ) GROUPBY学号 HAVINGAVG(成绩)>=85; 23.将计算机专业学生的“计算机基础”课程成绩按降序排列。SELECT姓名,课程名,成绩 FROMxs,kc,cj WHERExs.学号=cj.学号 ANDcj.课程号=kc.课程号 AND课程名='计算机基础' AND专业名='计算机' ORDERBY成绩DESC; 24.将计算机专业学生按其平均成绩排列。SELECT学号,姓名,专业名 FROMxs WHERE专业名='计算机' ORDERBY( SELECTAVG(成绩) FROMcj GROUPBYcj.学号 HAVINGxs.学号=cj.学号 ); 25.先用WITH查询2个学生的所有考试成绩，然后把它们联合起来。WITHcte_stu1_cjAS( SELECT姓名,课程名,成绩 FROMxs,kc,cj WHERExs.学号=cj.学号 ANDkc.课程号=cj.课程号 ANDxs.学号='081113'),cte_stu2_cjAS( SELECT姓名,课程名,成绩 FROMxs,kc,cj WHERExs.学号=cj.学号 ANDkc.课程号=cj.课程号 ANDxs.学号='081202')SELECT*FROMcte_stu1_cj UNIONSELECT*FROMcte_stu2_cj; 26.向准备计算指定学生的平均成绩，然后计算2个学生的平均成绩。 27.创建计算机专业学生的平均成绩视图view_avg，包括学号和平均成绩：CREATEVIEWview_avg AS SELECTxs.学号,AVG(成绩)AS平均成绩 FROMcj,xs WHERE专业名='计算机' ANDxs.学号=cj.学号 GROUPBYcj.学号; 基于view_avg查询平均成绩大于80的学生情况：SELECTxs.学号,姓名,专业名,性别,出生日期,年龄,总学分,平均成绩 FROMxs,view_avg WHERExs.学号=view_avg.学号 AND平均成绩>80; 28.创建计算机专业学生视图view_jsjCREATEVIEWview_jsj AS SELECT*FROMxs WHERE专业名='计算机'