MySQL练习：复杂的SQL语句

MySQL练习：复杂的SQL语句

 

这周巩固、拓展了一下MySQL技能。

 

1. 练习环境创建：

1.1  数据库的4个表：

课程表course, 成绩表sc, 学生表student, 教师表teacher。

 

 

1.2 课程表course,

 

 

1.3 成绩表sc,

 

 

1.4 学生表student,

 

 

1.5 教师表teacher。

 

 

 

 

2. 实例

例1.  创建课程情况汇总表

汇总表栏目包括课程ID，课程名称，选学人数，合格人数，不合格人数，平均成绩，通过率，各分数段人数。

要点：在聚合函数内加条件。

select 
    sc.cid as `课程ID`, 
    course.cname as `课程名称`,
    count(1) as `选学人数`,
    count(case when score < 60 then null else 1 end) as `合格人数`,
    count(case when score < 60 then 1 else null end) as `不合格人数`,
    round(avg(score)) as `平均成绩`,
    round(count(case when score < 60 then null else 1 end) / count(1), 2) as `通过率`,
    sum(case when score>= 85 then 1 else 0 end) as `100 - 85`,
    sum(case when score>= 70 and score< 85 then 1 else 0 end) as `85 - 70`,
    sum(case when score>= 60 and score<70 then 1 else 0 end) as `70 - 60`,
    sum(case when score< 60 then 1 else 0 end) as `60 - 0`
from sc, course
where sc.Cid = course.Cid
group by sc.Cid;

查询结果：

 

 

例2. 学生成绩排名表

查询结果包含的栏目是：学号、姓名、平均成绩、排名。

要点：实现并列排名，这里用到了两个内容一样的临时表t1, t2。

select t2.sid, 
    student.sname,
    t2.average,
    1+(select count(average) from 
        (
            select sid, avg(score) as average
            from sc
            group by sid
        ) as t1
        where average > t2.average
    ) as rank
from
    student,
    (
        select sid, avg(score) as average
        from sc
        group by sid
    ) as t2
where t2.sid = student.sid
order by average desc;

查询结果：

 

 

 

例3. 查询学过“001”并且也学过编号“002”课程的同学的学号、姓名

写法1：

SELECT Sid,Sname FROM student 
WHERE (SELECT Sid FROM sc WHERE Cid=001 and Sid = student.Sid) 
AND (SELECT Sid FROM sc WHERE Cid=002 and Sid = student.Sid);

写法2：

SELECT Sid,Sname FROM student 
WHERE Sid IN (SELECT Sid FROM sc WHERE Cid=001) 
AND Sid IN (SELECT Sid FROM sc WHERE Cid=002);

 

例4. 查询“001”课程比“002”课程成绩高的所有学生的学号、及相应的成绩

要点：先形成临时汇总表，然后加上两门课程分数比较的条件。

SELECT * FROM 
(
    select sc.sid, student.sname,
    sum(case cid when 001 then score else null end) as course001,
    sum(case cid when 002 then score else null end) as course002
    from sc join student on sc.sid = student.sid
    group by sid
) as temptable
where course001 > course002;

 

 

如果不需要两门课程的成绩，SQL语句可以简化为：

SELECT Sid,Sname FROM student 
WHERE (SELECT score FROM sc WHERE Cid=001 and Sid = student.Sid) > (SELECT score FROM sc WHERE Cid=002 and Sid = student.Sid);

或者写作：

SELECT student.Sid, student.Sname from sc as scout join student on scout.sid = student.sid
WHERE Cid = 001  AND score > (SELECT score FROM sc as scin WHERE scout.Sid = scin.Sid and Cid = 002);

 

 

例5. 查询每门课程的最高分学生

查询过程：

先产生“每门课程最高分”临时表t1;

然后，联合成绩表sc，查询出每门课程的最高分记录，作为临时表t2；

最后，联合学生表、课程表添加学生名字、课程名称。

select t2.cid, course.Cname, t2.sid, student.sname, t2.score 
from
    student, 
    course, 
    (
        select sc.*
        from (select cid, max(score) as topscore from sc group by cid) as t1
        join sc
        on sc.Cid = t1.cid and sc.score = t1.topscore
    ) as t2
where student.sid = t2.sid and t2.cid=course.cid
order by cid;

 

 

例6. 学过“叶平”老师课的同学的学号、姓名

本例同时用到学生、老师、课程、评分4个表。

要点：整理好SQL语句的层次关系。

SELECT Sid,Sname FROM student
WHERE Sid IN 
(
    SELECT DISTINCT Sid
    FROM 
        sc,
        (
            SELECT Cid 
            FROM course 
            WHERE Tid IN (SELECT Tid FROM teacher WHERE Tname = '叶平')
        ) AS courseofteacher
    WHERE courseofteacher.Cid=sc.Cid
);