wxp0909
随笔 - 54,  文章 - 2,  评论 - 8,  阅读 - 1566
2022-08-17 第四小组 王星苹 学习笔记

学习心得

DQL查询语言，比之前的查询多了类似嵌套的结构，叫做子查询

  • 标量子查询：结果集只有一行一列（单行子查询）

  • 列子查询：结果集有一列多行

  • 行子查询：结果集有一行多列

  • 表子查询：结果集多行多列

掌握情况：还行，多练习一些题会比较好

总结：

  • where型子查询，如果是where 列 = （内层sql），则内层的sql返回的必须是单行单列，单个值。

  • where型子查询，如果是where (列1,列2) = （内层sql），内层的sql返回的必须是单列，可以是多行。

经验分享：分析需求，拆步骤，分布写sql，整合拼接sql。而且如果可以不用子查询就不要用了，因为可读性低

今日多多练习，都是熟能生巧

复制代码
-- 3.查询每个同学的最高成绩和科目名称****
SELECT
    t.id,
    t.NAME,
    c.id,
    c.NAME,
    r.score 
FROM
    (
    SELECT
        s.id,
        s.NAME,(
        SELECT
            max( score ) 
        FROM
            scores r 
        WHERE
            r.s_id = s.id 
        ) score 
    FROM
        student s 
    ) t
    LEFT JOIN scores r ON r.s_id = t.id 
    AND r.score = t.score
    LEFT JOIN course c ON r.c_id = c.id;
-- 5.查询每个课程的最高分的学生信息*****
SELECT
    * 
FROM
    student s 
WHERE
    id IN (
    SELECT DISTINCT
        r.s_id 
    FROM
        (
        SELECT
            c.id,
            c.NAME,
            max( score ) score 
        FROM
            student s
            LEFT JOIN scores r ON r.s_id = s.id
            LEFT JOIN course c ON c.id = r.c_id 
        GROUP BY
            c.id,
            c.NAME 
        ) t
        LEFT JOIN scores r ON r.c_id = t.id 
    AND t.score = r.score 
    )
复制代码
复制代码
-- 6.查询名字中含有'张'或'李'字的学生的信息和各科成绩。
SELECT
    s.id,
    s.NAME sname,
    sc.score,
    c.NAME 
FROM
    student s
    LEFT JOIN scores sc ON s.id = sc.s_id
    LEFT JOIN course c ON sc.c_id = c.id 
WHERE
    s.NAME LIKE '%张%' 
    OR s.NAME LIKE '%李%';
-- 7.查询平均成绩及格的同学的信息。（子查询）
SELECT
    * 
FROM
    student 
WHERE
    id IN (
    SELECT
        sc.s_id 
    FROM
        scores sc 
    GROUP BY
        sc.s_id 
    HAVING
    avg( sc.score ) >= 70 
    )
-- 8.将学生按照总分数进行排名。（从高到低）
SELECT
    s.id,
    s.NAME,
    sum( sc.score ) score 
FROM
    student s
    LEFT JOIN scores sc ON s.id = sc.s_id 
GROUP BY
    s.id,
    s.NAME 
ORDER BY
    score DESC,
    s.id ASC;
-- 9.查询数学成绩的最高分、最低分、平均分。
SELECT
    c.NAME,
    max( sc.score ),
    min( sc.score ),
    avg( sc.score ) 
FROM
    course c
    LEFT JOIN scores sc ON c.id = sc.c_id 
WHERE
    c.NAME = '数学';
-- 10.将各科目按照平均分排序。
SELECT
    c.id,
    c.NAME,
    avg( sc.score ) score 
FROM
    course c
    LEFT JOIN scores sc ON c.id = sc.c_id 
GROUP BY
    c.id,
    c.NAME 
ORDER BY
    score DESC;
复制代码
复制代码
-- 11.查询老师的信息和他所带的科目的平均分
SELECT
    t.id,
    t.NAME,
    c.id cid,
    c.NAME cname,
    avg( r.score ) 
FROM
    teacher t
    LEFT JOIN course c ON t.id = c.t_id
    LEFT JOIN scores r ON r.c_id = c.id 
GROUP BY
    t.id,
    t.NAME,
    c.id,
    c.NAME;
-- 12.查询被"Tom"和"Jerry"教的课程的最高分和最低分
SELECT
    t.id,
    t.NAME,
    c.id cid,
    c.NAME cname,
    max( r.score ),
    min( r.score ) 
FROM
    teacher t
    LEFT JOIN course c ON t.id = c.t_id
    LEFT JOIN scores r ON r.c_id = c.id 
GROUP BY
    t.id,
    t.NAME,
    c.id,
    c.NAME 
HAVING
    t.NAME IN ( 'Tom', 'Jerry' );
-- 13.查询每个学生的最好成绩的科目名称（子查询）
SELECT
    t.id,
    t.sname,
    r.c_id,
    c.NAME,
    t.score 
FROM
    (
    SELECT
        s.id,
        s.NAME sname,
        max( r.score ) score 
    FROM
        student s
        LEFT JOIN scores r ON r.s_id = s.id 
    GROUP BY
        s.id,
        s.NAME 
    ) t
    LEFT JOIN scores r ON r.s_id = t.id 
    AND r.score = t.score
    LEFT JOIN course c ON r.c_id = c.id;
-- 14.查询所有学生的课程及分数
SELECT
    s.id,
    s.NAME,
    c.id,
    c.NAME,
    r.score 
FROM
    student s
    LEFT JOIN scores r ON s.id = r.s_id
    LEFT JOIN course c ON c.id = r.c_id;
-- 15.查询课程编号为1且课程成绩在60分以上的学生的学号和姓名（子查询）
SELECT
    * 
FROM
    student s 
WHERE
    s.id IN (
    SELECT
        r.s_id 
    FROM
        scores r 
    WHERE
    r.c_id = 1 
    AND r.score > 60)
--------------------------------------------------------
SELECT
    s.*,
    r.* 
FROM
    student s
    LEFT JOIN scores r ON s.id = r.s_id 
WHERE
    r.c_id = 1 
    AND r.score > 60
复制代码
复制代码
-- 16. 查询平均成绩大于等于70的所有学生学号、姓名和平均成绩
SELECT
    s.id,
    s.NAME,
    t.score 
FROM
    student s
    LEFT JOIN ( SELECT r.s_id, avg( r.score ) score FROM scores r GROUP BY r.s_id ) t ON s.id = t.s_id 
WHERE
    t.score >= 70;
-- 17.查询有不及格课程的学生信息
SELECT
    * 
FROM
    student s 
WHERE
    id IN ( SELECT r.s_id FROM scores r GROUP BY r.s_id HAVING min( r.score ) < 60 );
-- 18.查询每门课程有成绩的学生人数
SELECT
    c.id,
    c.NAME,
    t.number 
FROM
    course c
    LEFT JOIN ( SELECT r.c_id, count(*) number FROM scores r GROUP BY r.c_id ) t ON c.id = t.c_id;
----------------------------------------------------
SELECT
    c.id,
    c.NAME,
    count(*) 
FROM
    course c
    LEFT JOIN scores r ON c.id = r.c_id 
GROUP BY
    c.id,
    c.NAME;
-- 19.查询每门课程的平均成绩，结果按照平均成绩降序排列，如果平均成绩相同，再按照课程编号升序排列
SELECT
    c.id,
    c.NAME,
    avg( score ) score 
FROM
    course c
    LEFT JOIN scores r ON c.id = r.c_id 
GROUP BY
    c.id,
    c.NAME 
ORDER BY
    score DESC,
    c.id ASC;
-- 20.查询平均成绩大于60分的同学的学生编号和学生姓名和平均成绩
SELECT
    s.id,
    s.NAME sname,
    avg( r.score ) score 
FROM
    student s
    LEFT JOIN scores r ON r.s_id = s.id
    LEFT JOIN course c ON c.id = r.c_id 
GROUP BY
    s.id,
    s.NAME 
HAVING
    score > 65;
复制代码
复制代码
-- 21.查询有且仅有一门课程成绩在80分以上的学生信息
SELECT
    * 
FROM
    student 
WHERE
    id IN ( SELECT r.s_id FROM scores r WHERE r.score > 80 GROUP BY r.s_id HAVING COUNT(*) = 1 );
----------------------------------------------------------------------------
SELECT
    s.id,
    s.NAME,
    s.gender 
FROM
    student s
    LEFT JOIN scores r ON s.id = r.s_id 
WHERE
    r.score > 80 
GROUP BY
    s.id,
    s.NAME,
    s.gender 
HAVING
    count(*) = 1
-- 22.查询出只有三门课程的学生的学号和姓名
SELECT
    * 
FROM
    student s 
WHERE
    id IN ( SELECT r.s_id FROM scores r GROUP BY r.s_id HAVING count(*) = 3 );
----------------------------------------------------------------------------
SELECT
    s.id,
    s.NAME,
    s.gender 
FROM
    student s
    LEFT JOIN scores r ON s.id = r.s_id 
GROUP BY
    s.id,
    s.NAME,
    s.gender 
HAVING
    count(*) = 3
-- 23.查询有不及格课程的课程信息
SELECT
    * 
FROM
    course c 
WHERE
    id IN (
    SELECT
        r.c_id 
    FROM
        scores r 
    GROUP BY
        r.c_id 
    HAVING
    min( r.score ) < 60 
    )
----------------------------------------------------------------------------
SELECT
    c.id,
    c.NAME 
FROM
    course c
    LEFT JOIN scores sc ON c.id = sc.c_id 
GROUP BY
    sc.c_id,
    c.NAME 
HAVING
    min( sc.score ) < 60;
-- 24.查询至少选择4门课程的学生信息
SELECT
    * 
FROM
    student 
WHERE
    id IN (
    SELECT
        r.s_id 
    FROM
        scores r 
    GROUP BY
        r.s_id 
    HAVING
    count(*) >= 4 
    )
----------------------------------------------------------------------------
SELECT
    s.id,
    s.NAME 
FROM
    student s
    LEFT JOIN scores r ON s.id = r.s_id 
GROUP BY
    s.id,
    s.NAME 
HAVING
    count(*) >= 4;
-- 25.查询没有选全所有课程的同学的信息
SELECT
    * 
FROM
    student 
WHERE
    id IN (
    SELECT
        r.s_id 
    FROM
        scores r 
    GROUP BY
        r.s_id 
    HAVING
    count(*) != 5
    )
复制代码
复制代码
-- 26.查询选全所有课程的同学的信息
SELECT
    s.id,
    s.NAME,
    count(*) number 
FROM
    student s
    LEFT JOIN scores r ON s.id = r.s_id 
GROUP BY
    s.id,
    s.NAME 
HAVING
    number = ( SELECT count(*) FROM course );
-- 27.查询各学生都选了多少门课
SELECT
    s.id,
    s.NAME,
    count(*) number 
FROM
    student s
    LEFT JOIN scores r ON s.id = r.s_id 
GROUP BY
    s.id,
    s.NAME
-- 28.查询课程名称为"java"，且分数低于60分的学生姓名和分数
SELECT
    s.id,
    s.NAME,
    r.score 
FROM
    student s
    LEFT JOIN scores r ON s.id = r.s_id
    LEFT JOIN course c ON r.c_id = c.id 
WHERE
    c.NAME = 'java' 
    AND r.score < 60;
-- 29.查询学过"Tony"老师授课的同学的信息
SELECT
    s.id,
    s.NAME 
FROM
    student s
    LEFT JOIN scores r ON r.s_id = s.id
    LEFT JOIN course c ON c.id = r.c_id
    LEFT JOIN teacher t ON t.id = c.t_id 
WHERE
    t.NAME = 'Tom';
-- 30.查询没学过"Tony"老师授课的学生信息
SELECT
    * 
FROM
    student 
WHERE
    id NOT IN (
    SELECT DISTINCT
        s.id 
    FROM
        student s
        LEFT JOIN scores r ON r.s_id = s.id
        LEFT JOIN course c ON c.id = r.c_id
        LEFT JOIN teacher t ON t.id = c.t_id 
    WHERE
    t.NAME = 'Tom' 
    )
复制代码

还有没有用到的日期格式，可以看一下

 

posted on   骐琳  阅读(21)  评论(0)  编辑  收藏  举报
相关博文：
·  2022-08-16 第四小组 王星苹 学习笔记
·  2022-08-18 第四小组 王星苹 学习笔记
·  2022-08-17 第六小组 高佳誉 学习笔记
·  2022-08-17第七组薛雯匀
·  2022-08-17 第四组 王佳齐 学习笔记
阅读排行：
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· 阿里巴巴 QwQ-32B真的超越了 DeepSeek R-1吗？
· 【译】Visual Studio 中新的强大生产力特性
· 10年+ .NET Coder 心语 ── 封装的思维：从隐藏、稳定开始理解其本质意义
· 【设计模式】告别冗长if-else语句：使用策略模式优化代码结构

< 2025年3月 >
日 一 二 三 四 五 六
23 24 25 26 27 28 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31 1 2 3 4 5
Copyright © 2025 骐琳
Powered by .NET 9.0 on Kubernetes Powered By博客园
你点我就回上面去了ヾ(≧O≦)〃嗷~
点击右上角即可分享
微信分享提示
SQL AI 助手