MySQL日常练习；

操作用的几张表：
共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(*);

参考： MySQL核心技术与最佳实践 孔祥盛