mysql高级查询练习题_高级查询MYsql（二） 练习

一.单词部分

①exist存在②temp临时的③district区域

④content内容⑤temporary暂时的

二.预习部分

1.表连接都可以用子查询替换吗

是的

2.检测某列是否存在某个范围可以在子查询中使用什么关键字

EXISTS

3.哪些sql语句可以嵌套子查询

较复杂的数据查询语句 需要多个表的数据的时候

子查询可以出现在任何表达式出现的位置

三.练习部分

1.查询S2学员考试成绩信息

#上机1

SELECT `studentNo`,`subjectNo`,`studentResult`,`exameDate` FROM `result`

WHERE EXISTS(SELECT `studentNo` FROM `student` WHERE gradeId=2)

AND studentNo IN(SELECT `studentNo` FROM `student` WHERE gradeId=2)

2.制作学生成绩单

#上机2

SELECT `studentName` AS 姓名,`gradeName` AS 课程所属年级,`subjectName` AS 课程名称,`exameDate` AS 考试日期 FROM (

SELECT `studentName`,`gradeName`,`subjectName`,`exameDate` FROM `grade` AS gr,`result` AS re,`student` AS stu,`subject` AS sub

WHERE gr.`gradeID`=stu.`gradeID` AND re.`studentNo`=stu.`studentNo`

AND re.`subjectNo`=sub.`subjectNo`

) AS tempt;

3.统计Logic Java课程最近一次考试学生应到人数，实到人数和缺考人数

提取结果到临时表

#上机3

#select subjectNo from `subject` where `subjectName`='Logic Java';

#select max(`exameDate`) from result inner join `subject` on `result`.`subjectNo`=`subject`.`subjectNo`

#where `subjectName`='Logic Java';

#select `gradeID` from `subject` where `subjectName`='Logic Java';

#应到人数

SELECT COUNT(*) AS 应到人数 FROM student

WHERE `gradeID`=(SELECT `gradeID` FROM `subject` WHERE `subjectName`='Logic Java');

#实到人数

SELECT COUNT(*) AS 实到人数 FROM result

WHERE `subjectNo`=(SELECT subjectNo FROM `subject` WHERE `subjectName`='Logic Java')

AND exameDate=(SELECT MAX(`exameDate`) FROM result INNER JOIN `subject` ON `result`.`subjectNo`=`subject`.`subjectNo`

WHERE `subjectName`='Logic Java');

#缺考人数

SELECT((SELECT COUNT(*) FROM student

WHERE `gradeID`=(SELECT `gradeID` FROM `subject` WHERE `subjectName`='Logic Java'))-

(SELECT COUNT(*) FROM result