频道栏目
首页 > 程序开发 > Web开发 > PHP教程 > PHP与数据库 > MYSQL > 正文
MySQL数据库基础知识日常练习
2018-03-02 14:42:35         来源:happy_bigqiang的博客  
收藏   我要投稿

操作用的几张表:
共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主从复制的工作原理
下一篇:SQL Server基础之存储过程
相关文章
图文推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站