操作用的几张表:
共5张表:teacher,student,course,classes,choose;
查询union的使用:
#rollup的使用 #查询每个班级有多少学生 SELECT classes.class_no,COUNT(student.student_no) FROM classes LEFT JOIN student ON student.class_no = classes.class_no GROUP BY classes.class_no WITH ROLLUP; #union的使用 SELECT student_contact,student_name,student_no FROM student UNION ALL SELECT teacher_name,teacher_contact,teacher_no FROM teacher;
不用连接的多表查询:
SELECT * FROM student,choose WHERE student.student_no=choose.student_no AND student_name='张三';
子查询
#检索成绩比学生张三平均分高的所有学生及课程的信息 SELECT * FROM student LEFT JOIN choose ON student.student_no = choose.student_no LEFT JOIN course ON choose.course_no = course.course_no WHERE score > ( SELECT AVG(score) FROM student,choose WHERE student.student_no = choose.student_no AND student.student_name = '张三' ); #检索平均成绩比学生张三平均分高的所有学生及课程的信息 SELECT AVG(score) FROM student LEFT JOIN choose ON student.student_no = choose.student_no LEFT JOIN course ON course.course_no = choose.course_no GROUP BY student.student_no HAVING avg(score) > ( SELECT avg(score) FROM student LEFT JOIN choose ON student.student_no = choose.student_no WHERE student_name = '张三' ); #检索2012自动化一斑的所有学生的成绩 #LEFT JOIN 后面忘记写on了 SELECT * FROM student LEFT JOIN choose ON student.student_no = choose.student_no WHERE student.student_no IN ( SELECT student_no FROM student LEFT JOIN classes ON student.class_no = classes.class_no WHERE classes.class_name = '2012自动化1班' ); #插入数据后期测试 INSERT INTO teacher VALUES('004','马老师','1000000'); #检索没有申请选修课的教师的信息 SELECT * FROM teacher WHERE NOT EXISTS( SELECT * FROM course WHERE course.teacher_no= teacher.teacher_no); #为啥SELECT * FROM teacher INNER JOIN course ON course.teacher_no= teacher.teacher_no查询结果为空?? #检索'2012自动化2班’比‘2012自动化1班’最低分高的学生信息 SELECT * FROM student LEFT JOIN classes ON student.class_no = classes.class_no LEFT JOIN choose ON student.student_no = choose.student_no WHERE class_name='2012自动化2班' AND score> ANY( SELECT score FROM student LEFT JOIN classes ON student.class_no = classes.class_no LEFT JOIN choose ON student.student_no = choose.student_no WHERE class_name='2012自动化1班' ); #检索‘2012自动化2班’比‘2012自动化1班’最高分高的学生信息 SELECT * FROM student LEFT JOIN classes ON student.class_no = classes.class_no LEFT JOIN choose ON student.student_no = choose.student_no WHERE class_name='2012自动化2班' AND score> ALL( SELECT score FROM student LEFT JOIN classes ON student.class_no = classes.class_no LEFT JOIN choose ON student.student_no = choose.student_no WHERE class_name='2012自动化1班' );
5.8选课系统综合查询
#给定一个学生,统计该生已经选修了几门课程 #其实不用连接两张表,一张就能解决的 SELECT *,COUNT(*) FROM student LEFT JOIN choose ON student.student_no = choose.student_no WHERE student.student_no='2012001'; #给定一个学生,统计该生选修了那些课程 SELECT * FROM choose LEFT JOIN course ON choose.course_no = course.course_no WHERE choose.student_no='2012001'; #给定一门课程,统计哪些学生选修了这门课程; SELECT * FROM choose LEFT JOIN course ON course.course_no = choose.course_no LEFT JOIN student ON student.student_no = choose.student_no LEFT JOIN classes ON student.class_no = classes.class_no WHERE course.course_no = '1' ORDER BY department_name,class_name,student.student_no; #统计哪些课程已经报满,其他学生不能选修 SELECT * FROM choose LEFT JOIN course ON choose.course_no = course.course_no #没想到用分组 GROUP BY course.course_no HAVING up_limit = COUNT(*);