sql基础问题

sql基础问题

问题来源 https://www.cnblogs.com/wupeiqi/articles/5729934.html

解题答案 https://www.cnblogs.com/wupeiqi/articles/5748496.html

虽然已经有了解题答案但是还是要通过自己再过一遍加深自己的理解与思想。

导出数据库数据

mysqldump -u用户名 -p密码 数据库名称 >导出文件路径 # 结构+数据
mysqldump -u用户名 -p密码 -d 数据库名称 >导出文件路径 # 结构

导入现有数据库数据:

mysqldump -uroot -p密码 数据库名称 < 文件路径

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

思路就是创建两个临时表
查询学号与生物成绩名为为sw
查询学号与物理成绩名为为wl
通过 学生学号链接两张表 在比较sw成成绩大于wl的

SELECT
    A.student_id,
    sw,
    wl
FROM (
    SELECT
        student_id,
        num AS sw
    FROM
        score
    LEFT JOIN course ON score.course_id = course.cid
WHERE
    course.cname = '生物') AS A
    LEFT JOIN (
        SELECT
            student_id, num AS wl
        FROM
            score
            LEFT JOIN course ON score.course_id = course.cid
        WHERE
            course.cname = '物理') AS B ON A.student_id = B.student_id
WHERE
    sw > if(isnull(wl), 0, wl);

注意的点 sw.student_id 如果不指定一个表报错

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

思路 根据学生分组num,使用avg获取平均值,

have 的作用类似where 用于在 group by中使用

SELECT
    student_id,
    avg(num)
FROM
    score
GROUP BY
    student_id
HAVING
    avg(num) > 60

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

思路 先按照学生id 分组 然后计算 总成绩 选课数量用sum统计

SELECT
    score.student_id,
    student.sname,
    SUM(score.course_id) as coursenum,
    SUM(score.num) AS score
FROM
    score
    LEFT JOIN student ON score.student_id = student.sid
GROUP BY score.student_id

查询姓“李”的老师的个数;

思路 用count(计数) %限定条件

SELECT
    COUNT(1)
FROM
    teacher
WHERE
    tname LIKE '李%'

查询没学过“叶平”老师课的同学的学号、姓名;

思路 就是先查出学过的学生id 然后在not in


SELECT * FROM student WHERE sid NOT in( SELECT DISTINCT student_id FROM score WHERE score.course_id in( SELECT cid FROM course LEFT JOIN teacher ON course.teacher_id = teacher.tid WHERE tname = '李平老师'))

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

思路 先对学生分组 然后查询course_id = 001 和002的 然后count() 如果等与2就证明两门都学了

SELECT
    student_id,
    sname
FROM (
    SELECT
        student_id,
        course_id
    FROM
        score
    WHERE
        course_id = 1
        OR course_id = 2) AS B
    LEFT JOIN student ON B.student_id = student.sid
GROUP BY
    student_id
HAVING
    count(student_id) > 1

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

思路 吧not in 换成in 就可以了

SELECT
    *
FROM
    student
WHERE
    sid in( SELECT DISTINCT
            student_id FROM score
        WHERE
            score.course_id in(
                SELECT
                    cid FROM course
                LEFT JOIN teacher ON course.teacher_id = teacher.tid
            WHERE
                tname = '李平老师'))

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

SELECT
    sw.student_id,
    s02,
    s01
FROM (
    SELECT
        student_id,
        num AS s01
    FROM
        score
    LEFT JOIN course ON score.course_id = course_id
WHERE
    course.cid = "1") AS sw
    LEFT JOIN (
        SELECT
            student_id, num AS s02
        FROM
            score
            LEFT JOIN course ON score.course_id = course_id
        WHERE
            course.cid = "2") AS wl ON sw.student_id = wl.student_id
WHERE
    s02 > s01

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

SELECT
    sid,
    sname
FROM
    student
WHERE
    sid in( SELECT DISTINCT
            student_id FROM score
        WHERE num < 60)

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

思路 由于课程学生有关联的只有成绩表所以说利用该表统计要成绩数量与课程数量相同就是学了所有课程

SELECT
    student_id,
    sname
FROM
    score
    LEFT JOIN student ON score.student_id = student.sid
GROUP BY
    student_id
HAVING
    count(course_id) != (
        SELECT
            count(1)
        FROM
            course)

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

思路 子查询查询001学的课程,

SELECT
    student_id,
    sname,
    count(course_id)
FROM
    score
    LEFT JOIN student ON score.student_id = student.sid
WHERE
    student_id != 1
    AND score.course_id in(
        SELECT
            course_id FROM score
        WHERE
            score.student_id = '1')
    GROUP BY student_id

注意 要排除本身

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

select student_id,sname, count(course_id)
        from score left join student on score.student_id = student.sid
        where student_id != 1 and course_id in (select course_id from score where student_id = 1) group by student_id

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

SELECT
    student_id,
    sname
FROM
    score
    LEFT JOIN student ON score.student_id = student.sid
WHERE
    student_id in(
        SELECT
            student_id FROM score
        WHERE
            student_id != 1
        GROUP BY
            student_id
        HAVING
            count(course_id) = (
                SELECT
                    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(course_id) = (
        SELECT
            count(1)
        FROM
            score
        WHERE
            student_id = 1)

删除学习“李平”老师课的score表记录;

思路 连表子查询 然后delete

DELETE FROM score
WHERE course_id in(
        SELECT
            cid FROM course
        LEFT JOIN teacher ON course.teacher_id = teacher.tid
    WHERE
        teacher.name = '李平老师')

注意 搜索条件要完整名称

查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分;

SELECT
    course_id,
    max(num) AS max_num,
    min(num) AS min_num
FROM
    score
GROUP BY
    course_id;

课程平均分从高到低显示(现实任课老师);

高版本的mysql 会报错的解决方法
https://blog.csdn.net/yalishadaa/article/details/72861737

select avg(if(isnull(score.num),0,score.num)),teacher.tname from course
    left join score on course.cid = score.course_id
    left join teacher on course.teacher_id = teacher.tid

    group by score.course_id

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

SELECT
    score.sid,
    score.course_id,
    score.num,
    T.first_num,
    T.second_num
FROM
    score
    LEFT JOIN (
        SELECT
            sid,
            (
                SELECT
                    num
                FROM
                    score AS s2
                WHERE
                    s2.course_id = s1.course_id
                ORDER BY
                    num DESC
                LIMIT 0,
                1) AS first_num,
            (
                SELECT
                    num
                FROM
                    score AS s2
                WHERE
                    s2.course_id = s1.course_id
                ORDER BY
                    num DESC
                LIMIT 3,
                1) AS second_num
        FROM
            score AS s1) AS T ON score.sid = T.sid
WHERE
    score.num <= T.first_num
    AND score.num >= T.second_num

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

思路 根据课程id 分组然后count()

SELECT
    course_id,
    count(1)
FROM
    score
GROUP BY
    course_id;

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

思路就是查找count

SELECT
    course_id,
    count(1)
FROM
    score
GROUP BY
    course_id;

查询姓“张”的学生名单;

SELECT
    *
FROM
    student
WHERE
    student.sname LIKE '张%'

查询同名同姓学生名单,并统计同名人数;

思路 利用group 会把相同的放到一组里的特性

SELECT
    sname,
    count(1) AS count
FROM
    student
GROUP BY
    sname;

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

SELECT
    course_id,
    avg( if(isnull(num), 0, num)) AS avg
FROM
    score
GROUP BY
    score.course_id
ORDER BY
    AVG ASC,
    course_id DESC

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

SELECT
    student_id,
    sname,
    avg( if(isnull(num), 0, num))
FROM
    score
    LEFT JOIN student ON score.student_id = student.sid
GROUP BY
    student_id
HAVING
    avg( if(isnull(num), 0, num)) > 85;

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

思路 连表即可

SELECT
    student.sname,num
FROM
    score
    LEFT JOIN student ON score.student_id = student.sid
    LEFT JOIN course ON score.course_id = course.cid
WHERE
    score.num < 60
    AND course.cname = '生物';

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

SELECT
    student.sid,
    student.sname
FROM
    score
    LEFT JOIN student ON score.student_id = student.sid
WHERE
    score.student_id = 3
    AND score.num > 80

求选了课程的学生人数

查询选修“杨艳”老师所授课程的学生中,成绩最高的学生姓名及其成绩;

注意 在sqlserver 里面的 top muysql 是limit

SELECT
    student.sname,
    num
FROM
    score
    LEFT JOIN student ON score.student_id = student.sid
WHERE
    score.course_id = (
        SELECT
            course.cid
        FROM
            course
        LEFT JOIN teacher ON course.teacher_id = teacher.tid
    WHERE
        teacher.tname = '刘海燕老师')
ORDER BY
    num DESC
LIMIT 1

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

思路 groupby 进行分组 count统计

SELECT
    course.cname,
    COUNT(1)
FROM
    score
    LEFT JOIN course ON score.course_id = course.cid
GROUP BY
    score.course_id

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

思路 查询时分出两个结果集。就可以对比了

SELECT DISTINCT
    s1.student_id,
    s1.course_id,
    s2.student_id,
    s2.course_id,
    s1.num,
    s2.num
FROM
    score AS s1,
    score AS s2
WHERE
    s1.num = s2.num
    AND s1.course_id != s2.course_id

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

SELECT
    score.sid,
    score.course_id,
    score.num,
    T.first_num,
    T.second_num
FROM
    score
    LEFT JOIN (
        SELECT
            sid,
            (
                SELECT
                    num
                FROM
                    score AS s2
                WHERE
                    s2.course_id = s1.course_id
                ORDER BY
                    num DESC
                LIMIT 0,
                1) AS first_num,
            (
                SELECT
                    num
                FROM
                    score AS s2
                WHERE
                    s2.course_id = s1.course_id
                ORDER BY
                    num DESC
                LIMIT 1,
                1) AS second_num
        FROM
            score AS s1) AS T ON score.sid = T.sid
WHERE
    score.num <= T.first_num

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

思路 根据学生分租 计算每个学生的选课数量然后having筛选

SELECT
    student_id
FROM
    score
GROUP BY
    student_id
HAVING
    count(course_id) > 1

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

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

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

思路就是 子查询和 利用not in

SELECT 
    student.sname
FROM
    score
    LEFT JOIN student ON score.student_id = student.sid
WHERE
    score.course_id NOT in(
        SELECT
            course.cid FROM course
        LEFT JOIN teacher ON course.teacher_id = teacher.tid
    WHERE
        teacher.tname = '李平老师')
    GROUP BY score.student_id
SELECT 
    DISTINCT
    student.sname
FROM
    score
    LEFT JOIN student ON score.student_id = student.sid
WHERE
    score.course_id NOT in(
        SELECT
            course.cid FROM course
        LEFT JOIN teacher ON course.teacher_id = teacher.tid
    WHERE
        teacher.tname = '李平老师')

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

思路先通过 student id 分组 having筛选出两门的

SELECT
    student_id,
    count(1)
FROM
    score
WHERE
    num < 60
GROUP BY
    student_id
HAVING
    count(1) > 2

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

SELECT
    score.student_id
FROM
    score
WHERE
    score.num < 60
    AND score.course_id = 4
ORDER BY
    num DESC;

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

DELETE FROM score
WHERE course_id = 1
    AND student_id = 2;

发表评论

电子邮件地址不会被公开。 必填项已用*标注