美文网首页数据库学习提升模版
SQL 经典语句实践

SQL 经典语句实践

作者: python与数据分析 | 来源:发表于2020-05-15 15:10 被阅读0次
    每天进步一点点~ (●'◡'●)~

    01 建表语句

    create table Student(sid varchar(10),sname varchar(10),sage datetime,ssex nvarchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
create table Course(cid varchar(10),cname varchar(10),tid varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
create table Teacher(tid varchar(10),tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
create table SC(sid varchar(10),cid varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

    02 表结构预览

    --学生表
    Student(SId,Sname,Sage,Ssex)
    --SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
    --课程表
    Course(CId,Cname,TId)
    --CId 课程编号,Cname 课程名称,TId 教师编号
    --教师表
    Teacher(TId,Tname)
    --TId 教师编号,Tname 教师姓名
    --成绩表
    SC(SId,CId,score)
    --SId 学生编号,CId 课程编号,score 分数

    1. 查询“01”课程比“02”课程成绩高的所有学生的学号；
    SELECT
    t1.sid
FROM
    (SELECT * FROM sc WHERE cid = 01) AS t1
LEFT JOIN (SELECT * FROM sc WHERE cid = 02) AS t2 ON t1.sid = t2.sid
WHERE
    t1.score > t2.score
    2. 查询平均成绩大于60分的同学的学号和平均成绩；
    SELECT
    sid,
    AVG(score)
FROM
    sc
GROUP BY
    sid
HAVING
    AVG(score) > 60
    3. 查询所有同学的学号、姓名、选课数、总成绩
    SELECT
    s.sid,
    s.sname,
    COUNT(c.cid),
    SUM(c.score)
FROM
    sc AS c
LEFT JOIN student AS s ON c.sid = s.sid
GROUP BY
    c.sid
    4. 查询姓“李”的老师的个数；
    SELECT
    COUNT(tid)
FROM
    teacher
WHERE
    tname LIKE '李%'
    5. 查询没学过“张三”老师课的同学的学号、姓名；
    SELECT
    sid,
    sname
FROM
    student
WHERE
    sid NOT IN (
        SELECT
            sid
        FROM
            teacher
        LEFT JOIN course ON teacher.tid = course.tid
        LEFT JOIN sc ON course.cid = sc.cid
        WHERE
            teacher.tname = '张三'
    )
    6、查询学过“01”并且也学过编号“02”课程的同学的学号、姓名；
    SELECT
    s.sid,
    s.sname
FROM
    (
        SELECT
            sid
        FROM
            sc
        GROUP BY
            sid
        HAVING
            COUNT(IF(cid = '01', score, NULL) > 0)
        AND COUNT(IF(cid = '02', score, NULL) > 0)
    ) AS t
LEFT JOIN student AS s ON t.sid = s.sid
    7. 查询学过“张三”老师所教的课的同学的学号、姓名；
    SELECT
    student.sid,
    student.sname
FROM
    (
        SELECT
            course.cid
        FROM
            teacher
        LEFT JOIN course ON teacher.tid = course.tid
        WHERE
            teacher.tname = '张三'
    ) t
LEFT JOIN sc ON t.cid = sc.cid
LEFT JOIN student ON sc.sid = student.sid
    8. 查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名；
    SELECT
    t1.sid,
    sname
FROM
    (
        SELECT DISTINCT
            t1.sid AS sid
        FROM
            (SELECT * FROM sc WHERE cid = '01') t1
        LEFT JOIN (SELECT * FROM sc WHERE cid = '02') t2 ON t1.sid = t2.sid
        WHERE
            t1.score < t2.score
    ) t1
LEFT JOIN student ON t1.sid = student.sid
    9. 查询所有课程成绩小于60分的同学的学号、姓名；
    #①一种方式
SELECT
    t1.sid,
    sname
FROM
    (
        SELECT
            sid
        FROM
            sc
        GROUP BY
            sid
        HAVING
            AVG(score) < 60
    ) AS t1
LEFT JOIN student ON t1.sid = student.sid

    # ② 第二种方式
SELECT
    t1.sid,
    sname
FROM
    (
        SELECT
            sid
        FROM
            sc
        GROUP BY
            sid
        HAVING
            max(score < 60)
    ) t1
LEFT JOIN student ON t1.sid = student.sid
    10. 查询没有学全所有课的同学的学号、姓名；
    SELECT
    s.sid,
    s.sname
FROM
    (
        SELECT
            sid
        FROM
            sc
        GROUP BY
            sid
        HAVING
            COUNT(cid) < (SELECT COUNT(*) FROM course)
    ) t1
LEFT JOIN student s ON t1.sid = s.sid
    11. 查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名；
    SELECT DISTINCT
    sc.sid
FROM
    (SELECT cid FROM sc WHERE sid = 01) t1
LEFT JOIN sc ON t1.cid = sc.cid
    12. 查询和"01"号的同学学习的课程完全相同的其他同学的学号和姓名
    SELECT
    sc.sid,
    COUNT(sc.cid)
FROM
    sc
LEFT JOIN (
    SELECT
        cid
    FROM
        sc
    WHERE
        sid = '01'
) t1 ON sc.cid = t1.cid
GROUP BY
    sc.sid
HAVING
    COUNT(sc.cid) = 3
AND sc.sid != '01';
    14. 查询没学过"张三"老师讲授的任一门课程的学生姓名
    SELECT
    sid,
    sname
FROM
    student
WHERE
    sid NOT IN (
        SELECT
            sid
        FROM
            sc
        LEFT JOIN course AS c ON sc.cid = c.cid
        LEFT JOIN teacher AS t ON c.tid = t.tid
        WHERE
            tname = '张三'
    )
    15. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
    SELECT
    sid,
    avg(score),
    COUNT(IF(score < 60, cid, NULL)) AS num
FROM
    sc
GROUP BY
    sid
HAVING
    COUNT(IF(score < 60, cid, NULL)) >= 2
    16. 检索"01"课程分数小于60，按分数降序排列的学生信息
    SELECT
    *
FROM
    sc
WHERE
    cid = '01'
GROUP BY
    sid
HAVING
    score < 60
ORDER BY
    score DESC
    17. 按平均成绩从高到低显示所有学生的平均成绩
    SELECT
    sid,
    avg(score) AS av
FROM
    sc
GROUP BY
    sid
ORDER BY
    av DESC
    18. 查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率
    SELECT
    sc.cid,
    c.cname,
    MAX(sc.score),
    MIN(sc.score),
    AVG(sc.score),
    count(IF(score >= 60, sid, NULL)) / count(sid) AS pass_rate
FROM
    sc
LEFT JOIN course AS c ON sc.cid = c.cid
GROUP BY
    sc.cid
    19. 按各科平均成绩从低到高和及格率的百分数从高到低顺序
    select 
   cid
,avg(score) as avg_score
    ,count(if(score>=60,sid,null))/count(sid) as pass_rate
from sc
group by cid
order by avg_score,pass_rate desc
    20. 查询学生的总成绩并进行排名
    SELECT
    sid,
    SUM(score) AS zh
FROM
    sc
GROUP BY
    sid
ORDER BY
    zh DESC
    21. 查询不同老师所教不同课程平均分从高到低显示
    SELECT
    c.tid,
    avg(score) AS cj
FROM
    sc
LEFT JOIN course AS c ON sc.cid = c.cid
GROUP BY
    sc.cid
ORDER BY
    cj DESC
    22. 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
    SELECT
    sid,
    rank_num,
    score,
    cid
FROM
    (
        SELECT
            rank () over (
                PARTITION BY cid
                ORDER BY
                    score DESC
            ) AS rank_num,
            sid,
            score,
            cid
        FROM
            sc
    ) t
WHERE
    rank_num IN (2, 3)
    23. 统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
    select
    sc.cid
    ,cname
    ,count(if(score between 85 and 100,sid,null))/count(sid)
    ,count(if(score between 70 and 85,sid,null))/count(sid)
    ,count(if(score between 60 and 70,sid,null))/count(sid)
    ,count(if(score between 0 and 60,sid,null))/count(sid)
from sc
left join course
    on sc.cid=course.cid
group by sc.cid,cname
    24. 查询学生平均成绩及其名次
    SELECT
    sid,
    avg_cj,
    rank () over (ORDER BY avg_cj DESC) AS rank_num
FROM
    (
        SELECT
            sid,
            avg(score) AS avg_cj
        from sc
        GROUP BY
            sid
    ) t
    25. 查询各科成绩前三名的记录
    SELECT sid,cid,cj_num,score
FROM
    (
        SELECT
            sid,
            cid,
            rank () over (
                PARTITION BY cid
                ORDER BY
                    score DESC
            ) AS cj_num,
            score
        FROM
            sc
    ) t
WHERE
    cj_num <= 3

    相关文章

      网友评论

        本文标题：SQL 经典语句实践

        本文链接：https://www.haomeiwen.com/subject/lbtcohtx.html

        延伸阅读

        深度阅读

        • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

        栏目导航

        热点阅读

        数据库

        学习提升

        模版

        关于我们|服务条款|联系我们|SQL 经典语句实践|投稿指南|网站地图|RSS订阅|排版工具|手机版

        提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

        Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

        备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

        本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

        所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！