MySQL学习笔记——〇三 MySQL习题

在前面讲了MySQL的初步使用方法以后，在这里放出来一些案例来看看怎么做。

先看看database的结构，一共5个表

外键关系：

class的cid是student的class_id的外键，teacher表的tid是course的teacher_id的外键，student表的sid和course表的cid是score表的student_id和course_id的外键，student_id和course_id是联合唯一索引。

写的可能不太清楚，大概就是这么个关系，数据没有完全列出来。

表的创建和数据插入代码

表和数据的代码

下面就是SQL语句的使用

1.查询“生物”课程比“物理”课程成绩高的所有学生的学号

思路：分别获取生物的成绩和物理的成绩，连表以后取student_id相同的数据就是学习了物理和生物的同学，再用where筛选

SELECT

    t1.sid

FROM

    ( SELECT * FROM score LEFT JOIN course ON course.cid = score.course_id WHERE course.cname = '生物' ) AS t1

    LEFT JOIN ( SELECT * FROM score LEFT JOIN course ON course.cid = score.course_id WHERE course.cname = '物理' ) AS t2 ON t1.student_id = t2.student_id

WHERE

    t1.num > t2.num;

2.查询平均成绩大于60分的同学的学号和平均成绩

SELECT

    student_id,

    AVG( num )

FROM

    score

GROUP BY

    student_id

HAVING

    AVG( num )> 60;

3.查询所有同学的学号、姓名、选课数、总成绩

SELECT

    student.sid,

    student.sname,

    t.totle_course,

    t.totle_score

FROM

    ( SELECT student_id, count( course_id ) AS totle_course, sum( num ) AS totle_score FROM score GROUP BY student_id ) AS t

    JOIN student ON t.student_id = student.sid;

方法1

SELECT

    score.student_id,

    student.sname,

    count( student_id ),

    sum( num )

FROM

    score

    LEFT JOIN student ON student_id = student.sid

GROUP BY

    score.student_id;

方法2

4.查询姓“李”的老师的个数

SELECT

    count( 1 )

FROM

    teacher

WHERE

    tname LIKE '李%';

5.查询没学过“李平”老师课的同学的学号、姓名

先获取李平老师的tid，在根据tid获取course里的cid，根据cid获取score表内not in cid的student_id，然后连表查sname就可以了

SELECT

    sid,

    sname

FROM

    student

WHERE

    sid NOT IN (

    SELECT

        student_id

    FROM

        score

    WHERE

        course_id IN ( SELECT cid FROM course LEFT JOIN teacher ON course.teacher_id = teacher.tid WHERE teacher.tname = '李平老师' )

GROUP BY

    student_id);

6.查询学过“李平”老师所教的所有课的同学的学号、姓名

和上一道题差不多，但是是学过全部课程的

SELECT

    t.student_id,

    student.sname

FROM

    (

    SELECT

        student_id

    FROM

        score

    WHERE

        course_id IN ( SELECT cid FROM course LEFT JOIN teacher ON teacher.tid = course.teacher_id WHERE tname = '李平老师' )

    GROUP BY

        student_id

    HAVING

        count( course_id )> 1

    ) AS t

    LEFT JOIN student ON student.sid = t.student_id;

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

SELECT

    t.student_id,

    student.sname

FROM

    ( SELECT student_id FROM score WHERE course_id = 1 OR course_id = 2 GROUP BY student_id HAVING count( course_id )> 1 ) AS t

    LEFT JOIN student ON t.student_id = student.sid;

8.查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名

和第1题思路一样，题里直接给出了课程的id，少了一步索引。反而更简单

SELECT

    t.student_id,

    student.sname

FROM

    (

    SELECT

        t1.student_id

    FROM

        ( SELECT * FROM score WHERE course_id = 1 ) AS t1

        LEFT JOIN ( SELECT * FROM score WHERE course_id = 2 ) AS t2 ON t1.student_id = t2.student_id

    WHERE

        t2.num < t1.num

    ) AS t

    LEFT JOIN student ON student.sid = t.student_id;

9.查询有课程成绩小于60分的同学的学号、姓名

获取了score里num少于60的数据以后student_id里可能会有重复的，如果用distinct的话也是可以的，但是distinct在数据量较大的时候去重效率就会比较低下。所以在这里用了group by。

SELECT

    Id,

NAME

FROM

    (

    SELECT

        student.sid AS Id,

        student.sname AS NAME

    FROM

        score

        LEFT JOIN student ON student.sid = score.student_id

    WHERE

        num < 60

    ) AS t

GROUP BY

    Id;

10.查询没有学全所有课的同学的学号、姓名

这里用动态获取课程数量的方法，没有把课程数量写死。

SELECT

    t.student_id,

    student.sname

FROM

    (

    SELECT

        student_id

    FROM

        score

    GROUP BY

        student_id

    HAVING

        count( course_id )<(

        SELECT

            count( 1 )

        FROM

            course

        )) AS t

    LEFT JOIN student ON student.sid = t.student_id

11.查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名

SELECT

    t.student_id,

    student.sname

FROM

    ( SELECT student_id FROM score WHERE student_id != 1 AND course_id IN ( SELECT course_id FROM score WHERE student_id = 1 ) GROUP BY student_id ) AS t

    LEFT JOIN student ON student.sid = t.student_id;

12.查询至少学过学号为“001”同学所有课的其他同学学号和姓名

SELECT

    t.id,

    student.sname

FROM

    (

    SELECT

        student_id AS id,

        count( 1 )

    FROM

        score

    WHERE

        student_id != 1

        AND course_id IN ( SELECT course_id FROM score WHERE student_id = 1 )

    GROUP BY

        student_id

    HAVING

        count( 1 ) = ( SELECT count( course_id ) FROM score WHERE student_id = 1 )

    ) AS t

    LEFT JOIN student ON student.sid = t.id;

13.查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名

14.删除学习“李平”老师课的SC表记录

DELETE

FROM

    score

WHERE

    course_id IN (

    SELECT

        cid

    FROM

        course

WHERE

    teacher_id IN ( SELECT tid FROM teacher WHERE tname = '李平老师' ));

15.向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩

insert into score(student_id, course_id, num) select sid,2,(select avg(num) from score where course_id = 2)

    from student where sid not in (

        select student_id from score where course_id = 2

    );

16.按平均成绩从低到高显示所有学生的“001”、“002”、“003”三门的课程成绩，按如下形式显示：学生ID,语文,数学,英语,有效课程数,有效平均分（重要方法）；

这个题的解法还是很重要的，一定要掌握，先看看怎么获取各学员的三门成绩

SELECT

    student_id,

    ( SELECT num FROM score AS s2 WHERE s2.student_id = s1.student_id AND course_id = 1 ) AS 'course1_num',

    ( SELECT num FROM score AS s2 WHERE s2.student_id = s1.student_id AND course_id = 2 ) AS 'course2_num',

    ( SELECT num FROM score AS s2 WHERE s2.student_id = s1.student_id AND course_id = 3 ) AS 'course3_num'

FROM

    score AS s1

GROUP BY

    student_id

最后再分下组就可以了

SELECT

    student_id,

    ( SELECT num FROM score AS s2 WHERE s2.student_id = s1.student_id AND course_id = 1 ) AS 'course1_num',

    ( SELECT num FROM score AS s2 WHERE s2.student_id = s1.student_id AND course_id = 2 ) AS 'course2_num',

    ( SELECT num FROM score AS s2 WHERE s2.student_id = s1.student_id AND course_id = 3 ) AS 'course3_num',

    count( s1.course_id ) AS 有效课程数,

    avg( s1.num ) AS 平均分

FROM

    score AS s1

GROUP BY

    student_id

ORDER BY

    平均分 DESC;

16题解法

17.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

select course_id,max(num)as 最高分,min(num) as 最低分 from score group by course_id;

18.按各科平均成绩从低到高和及格率的百分数从高到低顺序（case when else的用法)

思路，在select的时候添加两列数，一列根据num判定是否及格，及格了赋值为1要么就赋值为0另外一列直接count(1),最后两列用sum统计一下再一除就知道及格率是多少了

SELECT

    course_id,

    avg( num ) AS 平均分,

    sum( CASE WHEN num < 60 THEN 0 ELSE 1 END )/ sum( 1 ) AS 及格率

FROM

    score

GROUP BY

    course_id

ORDER BY

    平均分 ASC,及格率 DESC;

19.课程平均分从高到低显示（显示任课老师）

用三元运算的方式限制了一下，防止某人的成绩为空然后运算报错

if(isnull(score.num),0,score.num)

SELECT

    score.course_id,

    avg(if(isnull(score.num),0,score.num)),

    teacher.tname

FROM

    score

    LEFT JOIN course ON score.course_id = course.cid

    LEFT JOIN teacher ON course.teacher_id = teacher.tid

GROUP BY

    course_id

ORDER BY

    avg( num ) DESC;

20.查询各科成绩前三名的记录:(不考虑成绩并列情况)

这里的思路可能比较绕，方法是先按course_id进行分组，获取每门学科的成绩，在group by 去重后进行降序排列后用limit2,1来获取每一科拍第三的成绩，

SELECT

    course_id,

    ( SELECT num FROM score AS s2 WHERE s2.course_id = s1.course_id GROUP BY s2.num DESC LIMIT 2, 1 ) AS num3

FROM

    score AS s1

然后连表到score以后再比较course_id和num，在course_id相同的时候看看如果num大于num3，就满足要求

SELECT

    *

FROM

    (

    SELECT

        student_id,

        course_id,

        num,

        ( SELECT num FROM score AS s2 WHERE s2.course_id = s1.course_id GROUP BY s2.num DESC LIMIT 2, 1 ) AS num3

    FROM

        score AS s1

    ) AS t

WHERE

    num > num3

ORDER BY

    course_id ASC,

    num3 DESC;

21.查询每门课程被选修的学生数

SELECT

    course_id,

    count( 1 )

FROM

    score

GROUP BY

    course_id;

22.查询出只选修了一门课程的全部学生的学号和姓名

SELECT

    student.sid,

    student.sname

FROM

    ( SELECT student_id FROM score GROUP BY student_id HAVING count( 1 ) = 1 ) AS t

    LEFT JOIN student ON student.sid = t.student_id;

23.查询男生、女生的人数

SELECT

    gender,

    count( 1 )

FROM

    student

GROUP BY

    gender;

24.查询姓“张”的学生名单

SELECT

    *

FROM

    student

WHERE

    sname LIKE '张%';

25.查询同名同姓学生名单，并统计同名人数

SELECT

    sname,

    count( 1 )

FROM

    student

GROUP BY

    sname;

26.查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

SELECT

    course_id,

    avg( num )

FROM

    score

GROUP BY

    course_id

ORDER BY

    avg( num ) ASC,

    course_id DESC;

27.查询平均成绩大于85的所有学生的学号、姓名和平均成绩

SELECT

    student.sid,

    student.sname,

    t.average

FROM

    ( SELECT student_id, avg( num ) AS average FROM score GROUP BY student_id HAVING average > 85 ) AS t

    JOIN student ON student.sid = t.student_id;

28.查询课程名称为“生物”，且分数低于60的学生姓名和分数

SELECT

    student.sname,

    t.num

FROM

    ( SELECT student_id, num FROM score WHERE course_id = ( SELECT cid FROM course WHERE cname = '生物' ) AND num < 60 ) AS t

    LEFT JOIN student ON student.sid = t.student_id;

29.查询课程编号为003且课程成绩在80分以上的学生的学号和姓名

SELECT

    student.sid,

    student.sname

FROM

    ( SELECT student_id FROM score WHERE course_id = 3 AND num >= 80 ) AS t

    LEFT JOIN student ON student.sid = t.student_id;

30.求选了课程的学生人数

select student_id from score group by student_id;

31.查询选修“张磊”老师所授课程的学生中，成绩最高的学生姓名及其成绩

SELECT

    student.sname,

    num

FROM

    score

    LEFT JOIN student ON score.student_id = student.sid

WHERE

    course_id = ( SELECT cid FROM course WHERE teacher_id = ( SELECT tid FROM teacher WHERE tname = '张磊老师' ) )

ORDER BY

    num DESC

    LIMIT 1;

32.查询各个课程及相应的选修人数

select course_id,count(1) from score group by course_id;

33.查询不同课程但成绩相同的学生的学号、课程号、学生成绩

这里有个比较有意思的用法，假设我们有一个这样的表(表的名称就叫t吧)

然后我们执行下面的语句

select* from t as s1 ,t as s2;

出来的回事什么样的结论呢？

我们就可以利用这个方式来筛选一行中不同两列数据一样的值是否一样了

SELECT

    s1.student_id,

    s1.course_id,

    s2.course_id,

    s2.num

FROM

    score AS s1,

    score s2

WHERE

    s1.sid != s2.sid

    AND s1.course_id != s2.course_id

    AND s1.num = s2.num

    AND s1.student_id = s2.student_id;

34.查询每门课程成绩最好的前两名

和前面第20题思路一样

SELECT

    *

FROM

    (

    SELECT

        student_id,

        course_id,

        num,

        ( SELECT num FROM score AS s2 WHERE s2.course_id = s1.course_id GROUP BY s2.num DESC LIMIT 1, 1 ) AS num3

    FROM

        score AS s1

    ) AS t

WHERE

    num > num3

ORDER BY

    course_id ASC,

    num3 DESC;

35.检索至少选修两门课程的学生学号

SELECT

    student_id

FROM

    score

GROUP BY

    student_id

HAVING

    count( 1 ) > 1;

36.查询全部学生都选修的课程的课程号和课程名

SELECT

    course.cid,

    course.cname

FROM

    (

    SELECT

        course_id

    FROM

        score

    GROUP BY

        course_id

    HAVING

        count( 1 )=(

        SELECT

            count( 1 )

        FROM

            student

        )) AS t

    LEFT JOIN course ON course.cid = t.course_id;

37.查询没学过“李平”老师讲授的任一门课程的学生姓名

SELECT

    sid,

    sname

FROM

    student

WHERE

    sid NOT IN ((

        SELECT

            student_id

        FROM

            score

        WHERE

            course_id IN (

            SELECT

                cid

            FROM

                course

            WHERE

            teacher_id IN ( SELECT tid FROM teacher WHERE tname = '李平老师' ))

        GROUP BY

        student_id

    ));

38.查询两门以上不及格课程的同学的学号及其平均成绩

SELECT

    student_id,

    avg( num )

FROM

    score

WHERE

    num < 60

GROUP BY

    student_id

HAVING

    count( 1 )> 2;

39.检索“004”课程分数小于60，按分数降序排列的同学学号

SELECT

    student_id

FROM

    score

WHERE

    num < 60

    AND course_id = 4

ORDER BY

    student_id DESC;

40.删除“002”同学的“001”课程的成绩

DELETE from score where student_id=2 and course_id=1;

基本上常用的数据操作的方法都在上面列出来了，但是在日常的使用中我们一般不会有这么多需要连表的地方，一般我们会牺牲一些存储空间来减少数据库来回连表的情况，可以提高一定的效率。

巴特西

MySQL学习笔记——〇三 MySQL习题

最新文章

热门文章