-- ########## 01、综合练习 ##########

-- 使用的表结构来自前面创建的"教师授课、学生选课并有课程成绩"这个数据库设计

-- studentinfo、teacherinfo、courseinfo、scoreinfo

-- 1、查询姓张的老师的数量

SELECT COUNT(teacherid) AS 姓张的老师的数量

FROM teacherinfo

WHERE teachername LIKE '张%';

-- 2、查询每门功课选修的学生数量

-- 写法1、使用左连接

SELECT c.`coursename` AS 课程名称, temp.选修的学生数量

FROM courseinfo AS c

LEFT JOIN

(

    SELECT courseid, COUNT(studentid) AS 选修的学生数量

    FROM scoreinfo

    GROUP BY courseid

) AS temp

ON c.courseid = temp.courseid;

-- 写法2、使用子查询

SELECT

    c.`coursename` AS 课程名称,

    (

        SELECT COUNT(studentid)

        FROM scoreinfo AS sc

        WHERE sc.courseid = c.`courseid`

    ) AS 选修的学生数量

FROM courseinfo AS c;

-- 3、查询个人平均成绩高于60分的学生编号、学生姓名 和 个人平均成绩（如果得到的人数超过2人，显示第二条记录和第三条记录）

SELECT temp.studentid AS 学生编号, s.studentname AS 学生姓名, temp.个人平均成绩

FROM

(

    SELECT studentid, AVG(score) AS 个人平均成绩

    FROM scoreinfo

    GROUP BY studentid

    HAVING AVG(score) > 60

) AS temp

LEFT JOIN studentinfo AS s

ON temp.studentid = s.studentid

LIMIT 1, 2;

-- 4、查询男生的人数 和 女生的人数

SELECT studentgender AS 性别, COUNT(studentid) AS 人数

FROM studentinfo

GROUP BY studentgender;

-- 5、查询同名同姓的学生人数

SELECT * FROM studentinfo;

INSERT INTO studentinfo VALUES(NULL, '甄姬', '女', 38);

SELECT studentname AS 重名学生姓名, COUNT(studentid) AS 重名学生人数

FROM studentinfo

GROUP BY studentname

HAVING COUNT(studentid) > 1;

-- 6、查询每门功课的平均成绩，结果按每门功课平均成绩升序排列，成绩相同时，按课程编号倒序排列

SELECT c.`coursename` AS 课程名称, temp.平均成绩

FROM courseinfo AS c

LEFT JOIN

(

    SELECT courseid, AVG(score) AS 平均成绩

    FROM scoreinfo

    GROUP BY courseid

) AS temp

ON c.courseid = temp.courseid

ORDER BY temp.平均成绩 ASC, c.`courseid` DESC;

-- 7、查询课程名称为数学，且数学成绩低于60分的学生姓名和分数

SELECT c.`coursename` AS 课程名称, s.`studentname` AS 学生姓名, sc.`score` AS 分数

FROM scoreinfo AS sc

INNER JOIN courseinfo AS c ON sc.courseid = c.courseid AND c.coursename = '数学' AND sc.score < 60

INNER JOIN studentinfo AS s ON sc.studentid = s.studentid;

-- 8、查询所有学生的选课信息（显示为：学生编号、学生姓名、课程名称，并单行显示）

SELECT temp.学生编号, temp.学生姓名, GROUP_CONCAT(temp.课程名称) AS 课程名称

FROM

(

    SELECT s.`studentid` AS 学生编号, s.`studentname` AS 学生姓名, c.`coursename` AS 课程名称

    FROM studentinfo AS s

    LEFT JOIN scoreinfo AS sc ON s.studentid = sc.studentid

    LEFT JOIN courseinfo AS c ON sc.courseid = c.courseid

) AS temp

GROUP BY temp.学生编号;

-- 9、查询任何一门课程成绩在60分以上的学生姓名、课程名称及成绩

SELECT s.`studentname` AS 学生姓名, c.`coursename` AS 课程名称, sc.`score` AS 分数

FROM scoreinfo AS sc

INNER JOIN courseinfo AS c ON sc.courseid = c.courseid

INNER JOIN studentinfo AS s ON sc.studentid = s.studentid

WHERE sc.score > 60;

-- 10、查询至少选修了两门课程的学生信息

-- 写法1、使用独立子查询

SELECT s.`studentid` AS 学生编号, s.`studentname` AS 学生姓名, s.`studentgender` AS 学生性别, s.`studentage` AS 学生年龄

FROM studentinfo AS s

WHERE s.studentid IN

(

    SELECT studentid

    FROM scoreinfo

    GROUP BY studentid

    HAVING COUNT(courseid) >= 2

);

-- 写法2、使用内连接

SELECT s.`studentid` AS 学生编号, s.`studentname` AS 学生姓名, s.`studentgender` AS 学生性别, s.`studentage` AS 学生年龄

FROM studentinfo AS s

INNER JOIN

(

    SELECT studentid

    FROM scoreinfo

    GROUP BY studentid

    HAVING COUNT(courseid) >= 2

) AS temp

ON s.studentid = temp.studentid;

-- 11、查询全部学生都选修了的课程编号以及课程名称（基于无脏数据）

SELECT courseid AS 课程编号, coursename AS 课程名称

FROM courseinfo

WHERE courseid IN

(

    -- 在成绩信息表中，按课程编号分组，统计每组的学生编号数量，看看哪组的数量和学生信息表中学生数量一致，一致就说明是全部学生都选修的课程

    SELECT courseid

    FROM scoreinfo

    GROUP BY courseid

    HAVING COUNT(studentid) = (SELECT COUNT(studentid) FROM studentinfo)

);

-- 12、查询个人的英语成绩比数学成绩高的学生信息

-- 思路：在成绩信息表中对行数据进行获取比较，操作起来比较麻烦

--          考虑进行【行转列】的操作，这样就可以在一行中对不同的列的内容进行比较

--          【行转列】技巧：从成绩信息表中通过课程名称对应的课程编号形成两个独立的集合，再把这两个集合根据学生编号进行内连接，

--                          这样就得到同一个学生的不同课程的新集合，即得到同一行中有不同课程成绩的新集合

SELECT s.`studentid` AS 学生编号, s.`studentname` AS 学生姓名, s.`studentgender` AS 学生性别, s.`studentage` AS 学生年龄

FROM studentinfo AS s

WHERE s.`studentid` IN

(

    SELECT temp1.studentid

    FROM

    (

    -- 从成绩信息表中获取的个人英语成绩集合

    SELECT studentid, score

    FROM scoreinfo

    WHERE courseid = (SELECT courseid FROM courseinfo WHERE coursename = '英语')

    ) AS temp1

    INNER JOIN

    (

    -- 从成绩信息表中获取的个人数学成绩集合

    SELECT studentid, score

    FROM scoreinfo

    WHERE courseid = (SELECT courseid FROM courseinfo WHERE coursename = '数学')

    ) AS temp2

    ON temp1.studentid = temp2.studentid AND temp1.score > temp2.score

);

-- 13、查询所有学生的编号、姓名、选课数量、总成绩

SELECT s.`studentid` AS 学生编号, s.`studentname` AS 学生姓名, COUNT(sc.`courseid`) AS 选课数量, SUM(sc.`score`) AS 总成绩

FROM studentinfo AS s

LEFT JOIN scoreinfo AS sc ON s.studentid = sc.studentid

-- 按照学生编号进行分组，语法OK

GROUP BY s.studentid;

-- 按照学生编号 和 学生姓名进行分组，语法也OK，因为studentid是主键，久可以唯一标识记录了，加上studentname属于锦上添花

-- GROUP BY s.studentid, s.`studentname`;

-- 按照学生姓名进行分组，语法就不OK了，因为学生姓名有重名时，就会分到一组中了

-- group by s.studentname;

-- 14、查询没有选修过张老师课程的学生信息

-- 思路：没有选修过张老师课程的学生有两种：选修了课程但是选的不是张老师的课程的学生  和 没有选修课程的学生

--          这里正向思考比较麻烦，所以考虑逆向思考

--          从学生集合中剔除那些选修了张老师课程的学生，剩下的就是没有选修张老师课程的学生

SELECT s.`studentid` AS 学生编号, s.`studentname` AS 学生姓名, s.`studentgender` AS 学生性别, s.`studentage` AS 学生年龄

FROM studentinfo AS s

WHERE s.studentid NOT IN

(

    -- 选修了张老师课程的学生

    SELECT sc.studentid

    FROM scoreinfo AS sc

    INNER JOIN courseinfo AS c ON sc.courseid = c.courseid

    INNER JOIN teacherinfo AS t ON c.teacherid = t.teacherid AND t.teachername = '张老师'

);

-- 15、查询学过语文也学过数学的学生信息

SELECT s.`studentid` AS 学生编号, s.`studentname` AS 学生姓名, s.`studentgender` AS 学生性别, s.`studentage` AS 学生年龄

FROM studentinfo AS s

INNER JOIN scoreinfo AS sc1

ON s.studentid = sc1.studentid AND sc1.courseid = (SELECT courseid FROM courseinfo WHERE coursename = '语文')

INNER JOIN scoreinfo AS sc2

ON s.studentid = sc2.studentid AND sc2.courseid = (SELECT courseid FROM courseinfo WHERE coursename = '数学');

-- 16、查询个人成绩中每门功课都不及格的学生信息

-- 可能性1、无成绩的也算满足条件

SELECT s.`studentid` AS 学生编号, s.`studentname` AS 学生姓名, s.`studentgender` AS 学生性别, s.`studentage` AS 学生年龄

FROM studentinfo AS s

WHERE s.studentid NOT IN

(

    SELECT studentid

    FROM scoreinfo

    WHERE score >= 60

);

-- 可能性2、无成绩的不算满足条件

SELECT s.`studentid` AS 学生编号, s.`studentname` AS 学生姓名, s.`studentgender` AS 学生性别, s.`studentage` AS 学生年龄

FROM studentinfo AS s

WHERE s.studentid IN

(

    -- 如下写法不正确：因为这样会把部分课程不及格部分课程及格的同学也筛选出来

    -- SELECT studentid

    --     FROM scoreinfo

    --     WHERE score < 60

    -- 正确写法：按照学生编号分组，分组后组里最高的课程分数还小于60分，意味着所有课程都不及格

    SELECT studentid

    FROM scoreinfo

    GROUP BY studentid

    HAVING MAX(score) < 60

);

-- 17、查询每门功课的分数段人数，显示为：课程编号、课程名称、选课人数、[优秀90~100]、[良好80~90]、[一般70~80]、[及格60~70]、[不及格0~60]

SELECT

    sc.`courseid` AS 课程编号,

    c.`coursename` AS 课程名称,

    COUNT(sc.`studentid`) AS 选课人数,

    SUM(CASE WHEN sc.score >= 90 AND sc.score <= 100 THEN 1 ELSE 0 END) AS `[优秀90~100]`,

    SUM(CASE WHEN sc.score >= 80 AND sc.score < 90 THEN 1 ELSE 0 END) AS `[良好80~90]`,

    SUM(CASE WHEN sc.score >= 70 AND sc.score < 80 THEN 1 ELSE 0 END) AS `[一般70~80]`,

    SUM(CASE WHEN sc.score >= 60 AND sc.score < 70 THEN 1 ELSE 0 END) AS `[及格60~70]`,

    SUM(CASE WHEN sc.score >= 0 AND sc.score < 60 THEN 1 ELSE 0 END) AS `[不及格0~60]`

FROM scoreinfo AS sc

INNER JOIN courseinfo AS c ON sc.`courseid` = c.`courseid`

GROUP BY sc.`courseid`;

-- 18、查询没有选修全部课程的学生信息

SELECT s.`studentid` AS 学生编号, s.`studentname` AS 学生姓名, s.`studentgender` AS 学生性别, s.`studentage` AS 学生年龄

FROM studentinfo AS s

WHERE s.studentid IN

(

    -- 按照学生编号分组，每组中的课程统计数量小于课程信息表中课程数量的就是没有选修全部课程的学生

    SELECT studentid

    FROM scoreinfo

    GROUP BY studentid

    HAVING COUNT(courseid) < (SELECT COUNT(courseid) FROM courseinfo)

);

-- 19、查询和刘备（学生编号1）至少一起选修了一门课程的学生编号和学生姓名

SELECT s.`studentid` AS 学生编号, s.`studentname` AS 学生姓名, s.`studentgender` AS 学生性别, s.`studentage` AS 学生年龄

FROM studentinfo AS s

WHERE s.studentid IN

(

    -- 至少和刘备一起选修了一门课程

    SELECT studentid

    FROM scoreinfo

    WHERE courseid IN

    (

        -- 查询出刘备选的课程

        SELECT courseid

        FROM scoreinfo AS sc

        INNER JOIN studentinfo AS s

        ON s.`studentid` = sc.`studentid` AND s.`studentid` = 1 AND s.`studentname` = '刘备'

    )

);

-- 20、查询和张飞（学生编号3）选修的课程完全相同的学生编号和学生姓名

-- 思路：

-- 1）首先制作两个成绩信息表的连接，右表为张飞（学生编号3）的课程及成绩

--       此时会得到 完全和张飞课程相同的同学 和 部分和张飞课程相同的同学

SELECT *

FROM scoreinfo AS sc1

INNER JOIN scoreinfo AS sc2 ON sc1.`courseid` = sc2.`courseid` AND sc2.`studentid` = 3 AND sc1.`studentid` <> sc2.`studentid`

-- 2）在此基础上，在新生成的集合中按照学生编号进行分组，如果有学生的课程数量和张飞的课程数量一致的，那就张飞（学生编号3）选修的课程完全相同的学生

SELECT s.`studentid` AS 学生编号, s.`studentname` AS 学生姓名, s.`studentgender` AS 学生性别, s.`studentage` AS 学生年龄

FROM studentinfo AS s

WHERE s.studentid IN

(

    SELECT sc1.`studentid`

    FROM scoreinfo AS sc1

    INNER JOIN scoreinfo AS sc2 ON sc1.`courseid` = sc2.`courseid` AND sc2.`studentid` = 3 AND sc1.`studentid` <> sc2.`studentid`

    GROUP BY sc1.`studentid`, sc2.`studentid`

    HAVING COUNT(sc1.`courseid`) = (SELECT COUNT(courseid) FROM scoreinfo WHERE studentid = 3)

);

-- 21、按个人平均成绩降序排列显示学生的语文、数学、英语三门功课的成绩（选修了几门计算几门的平均分，未选修的课程显示未选）

--        显示为：学生编号、学生姓名、平均成绩、语文成绩、数学成绩、英语成绩

SELECT

    sc.`studentid` AS 学生编号,

    s.`studentname` AS 学生姓名,

    AVG(sc.`score`) AS 平均成绩,

    IFNULL((

            SELECT sc1.score

            FROM scoreinfo AS sc1

            WHERE sc1.studentid = sc.`studentid`

            AND sc1.courseid = (SELECT courseid FROM courseinfo WHERE coursename = '语文'))

    , '未选') AS 语文成绩,

    IFNULL((

            SELECT sc2.score

            FROM scoreinfo AS sc2

            WHERE sc2.studentid = sc.`studentid`

            AND sc2.courseid = (SELECT courseid FROM courseinfo WHERE coursename = '数学'))

    , '未选') AS 数学成绩,

    IFNULL((

            SELECT sc3.score

            FROM scoreinfo AS sc3

            WHERE sc3.studentid = sc.`studentid`

            AND sc3.courseid = (SELECT courseid FROM courseinfo WHERE coursename = '英语'))

    , '未选') AS 英语成绩

FROM scoreinfo AS sc

INNER JOIN studentinfo AS s

ON sc.`studentid` = s.`studentid`

GROUP BY sc.`studentid`

-- order by AVG(sc.`score`) desc;

-- 上面写法和下面写法均可，因为ORDER BY子句在SELECT子句之后执行的

ORDER BY 平均成绩 DESC;

-- 22、查询每门功课的最高分和最低分，显示为：课程编号、课程名称、最高分、最低分

SELECT

    sc.courseid AS 课程编号,

    c.`coursename` AS 课程名称,

    MAX(sc.`score`) AS 最高分,

    MIN(sc.`score`) AS 最低分

FROM scoreinfo AS sc

INNER JOIN courseinfo AS c

ON sc.courseid = c.courseid

GROUP BY sc.courseid;

-- 23、查询只选修了一门课程的学生的学生编号和学生姓名

SELECT

    sc.`studentid` AS 学生编号,

    s.`studentname` AS 学生姓名

FROM scoreinfo AS sc

INNER JOIN studentinfo AS s

ON sc.studentid = s.studentid

GROUP BY sc.studentid

HAVING COUNT(sc.courseid) = 1;

-- 24、查询学过张老师教的全部课程的学生的学生编号和学生姓名

SELECT

    sc.`studentid` AS 学生编号,

    s.`studentname` AS 学生姓名

FROM scoreinfo AS sc

INNER JOIN courseinfo AS c ON sc.courseid = c.courseid

INNER JOIN teacherinfo AS t ON c.teacherid = t.teacherid AND t.teachername = '张老师'

INNER JOIN studentinfo AS s ON sc.studentid = s.studentid

GROUP BY sc.studentid

HAVING COUNT(sc.courseid) = (

    -- 张老师教的课程数量

    SELECT COUNT(courseinfo.`courseid`)

    FROM courseinfo

    INNER JOIN teacherinfo AS t ON courseinfo.teacherid = t.teacherid AND t.teachername = '张老师'

);

-- 25、学生信息表中被人删除了若干条记录，现在需要查询出第4行至第6行的记录来使用（考虑使用多种实现方式，提示：使用LIMIT 和 不使用LIMIT）

-- delete from studentinfo where studentid = 3 or studentid = 7;

-- 写法1、直接使用LIMIT关键字

SELECT * FROM studentinfo LIMIT 3, 3;

-- 写法2、考虑取出前6行，进行倒序排列，再取出前3行，再倒序

SELECT *

FROM

(

    SELECT *

    FROM

    (

        SELECT *

        FROM (SELECT * FROM studentinfo LIMIT 0, 6) AS temp1

        ORDER BY temp1.studentid DESC

    ) AS temp2 LIMIT 0, 3

) AS temp3

ORDER BY temp3.studentid ASC;

-- 写法3、不使用LIMIT关键字

SELECT temp.`studentid` AS 学生编号, temp.`studentname` AS 学生姓名, temp.`studentgender` AS 学生性别, temp.`studentage` AS 学生年龄

FROM

(

    SELECT

        *,

        (SELECT COUNT(*) FROM studentinfo AS s2 WHERE s2.studentid <= s1.`studentid`) AS rownum

    FROM studentinfo AS s1

) AS temp

WHERE temp.rownum BETWEEN 4 AND 6;

最新文章

  1. Blender 之 Splash 代码分析
  2. Qt程序打包（使用Enigma Virtual Box和BoxedApp Packer封包）
  3. Hadoop YARN中内存的设置
  4. android之服务
  5. Rehashing
  6. Android教程说明-夜神模拟器连接IDE更新让Delphi发现你的手机或夜神模拟器
  7. EL表达式隐含对象
  8. bzoj4028
  9. css3字体
  10. 一天搞定HTML----列表标签03
  11. 【转】TCP/IP报文格式
  12. C语言学习之选择排序
  13. IIS与ASP.NET对请求的处理
  14. JavaScript中的转译符
  15. 爬取qq号
  16. python3 读取dbf文件报错 UnicodeDecodeError: &#39;gbk&#39; codec can&#39;t decode
  17. CentOS 7 之 Systemd 入门教程：命令篇
  18. leetcode34
  19. socket心跳超时检测，快速处理新思路（适用于超大量TCP连接情况下）
  20. HDU1312 Red and Black（DFS） 2016-07-24 13:49 64人阅读 评论(0) 收藏

热门文章

  1. 通过wscript运行的JS脚本，如何引入另一个JS文件
  2. 开源录屏软件Open Broadcaster Software
  3. nginx查看并发数量
  4. 求助，在gmssl中添加第三方库engine的问题
  5. Golang基础(8)：go interface接口
  6. Egret入门学习日记 --- 第五篇（书中 3.5节 内容）
  7. js实现动画（移动方块）
  8. [BeiJingWc2008]Gate Of Babylon
  9. 常用DOS命令及编程软件fa
  10. 创建B树，动态添加节点，并使用三种遍历算法对树进行遍历