SQL 经典语句实践

每天进步一点点~ (●'◡'●)~

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