数据data.zip

测试SQL执行时间

set profiling=1;  // 开启记录mysql语句执行时间
XXX; 							 // 你的语句
show profiles;    // 查看之前执行的语句
set profiling=0;   // 关闭记录行为


1

查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

SELECT 
    `Student`.`SId` AS `SID`,
    `Student`.`Sname`,
    `Student`.`Sage`,
    `Student`.`Ssex`,
    `t`.`C1score`,
    `t`.`C2score`
FROM `Student`
INNER JOIN (
    SELECT
        `C1`.`sid` AS `SID`,
        `C1`.`score` AS `C1score`,
        `C2`.`score` AS `C2score`
    FROM
        (SELECT `sid`,`score` FROM `SC` WHERE `CId`=01) AS `C1`
    INNER JOIN
        (SELECT `sid`,`score` FROM `SC` WHERE `CId`=02) AS `C2`
    ON `C1`.`sid`=`C2`.`SId`
    WHERE
        `C1`.`score`>`C2`.`score`
) AS `t`
ON `Student`.`SID`=`t`.`sid`;


2

查询同时存在" 01 "课程和" 02 "课程的情况

SELECT `sid`,count(*) AS `cnt`
FROM `SC`
WHERE `CId` IN (01,02)
GROUP BY `sid`
HAVING `cnt`>1;


3

查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

SELECT 
    `sid` AS `C1Stud`,
    `C2Stud`
FROM `SC`
LEFT JOIN (SELECT `sid` AS `C2Stud` FROM `sc` WHERE `CId`=02) AS `c1`
ON `SC`.`SId`=`c1`.`C2Stud`
WHERE `SC`.`CId`=01;


4

查询不存在" 01 "课程但存在" 02 "课程的情况

SELECT *
FROM `SC`
WHERE 
    `SC`.`SId` NOT IN (SELECT `sid` FROM `SC` WHERE `cid`=01)
    AND `CId`=02;


5

查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

SELECT
    `SC`.`Sid`,
    `Student`.`Sname`,
    AVG(`score`) AS `avgScore`
FROM `SC`
    INNER JOIN `Student` 
    ON `SC`.`SId`=`Student`.`sid`
GROUP BY `sid`
HAVING `avgScore`>60;


6

查询在 SC 表存在成绩的学生信息

SELECT *
FROM `Student`
WHERE `Student`.`sid` IN 
    (SELECT `sid` FROM `SC` GROUP BY `sid`);


7

查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

SELECT 
    `Student`.`sid`,
    `Sname`,
    `t`.`cnt`,
    `t`.`sum`
FROM `Student`
LEFT JOIN (
    SELECT 
        `sid`,
        count(`cid`) AS `cnt`,
        sum(`score`) AS `sum`
    FROM `SC`
    GROUP BY `sid`
) AS `t`
ON `Student`.`SId`=`t`.`sid`;


8

查有成绩的学生信息

SELECT *
FROM `Student`
WHERE `sid` IN (SELECT `sid` FROM `sc` GROUP BY `sid`)


9

查询「李」姓老师的数量

SELECT count(*) 
FROM `Teacher`
WHERE `Tname` LIKE "李%"


10

查询学过「张三」老师授课的同学的信息

SELECT *
FROM `Student`
WHERE `SId` IN (
    SELECT `sid`
    FROM `SC`
    WHERE `cid` IN (
    SELECT `cid`
        FROM `Course`
        WHERE `tid` IN 
            (SELECT `tid` FROM `Teacher` WHERE `Tname`="张三")
    )
)

SELECT `s`.* FROM `Teacher` AS `t`
LEFT JOIN `Course` AS `c`
ON `t`.`tid`=`c`.`tid`
LEFT JOIN `SC`
ON `sc`.`cid`=`c`.`cid`
LEFT JOIN `Student` AS `s`
ON `s`.`sid`=`sc`.`sid`
WHERE `t`.`Tname`='张三'


11

查询没有学全所有课程的同学的信息

SELECT `s`.*
FROM `Student` AS `s`
LEFT JOIN (
    SELECT `sid`
    FROM `SC`
    GROUP BY `sid`
    HAVING COUNT(`cid`)=(SELECT count(*) FROM `Course` AS `c`)
) AS `t`
ON `t`.`sid`=`s`.`sid`
WHERE `t`.`sid` IS null


12

查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

SELECT DISTINCT `s`.* 
FROM `Student` AS `s`
INNER JOIN `sc`
ON `s`.`sid`=`sc`.`sid`
WHERE `sc`.`cid` IN (
    SELECT `cid` FROM `sc` WHERE `sid`=01
)
AND `s`.`sid`<> 01;


13

查询和" 01 "号的同学学习的课程完全相同的其他同学的信息

SELECT `Student`.*
FROM `Student`
LEFT JOIN (
    SELECT DISTINCT `t2`.`sid`
    FROM (
        SELECT 
            `s`.`sid`,
            `t1`.`cid`
        FROM 
            `Student` AS `s`,
            (
                SELECT `sc`.`cid` 
                FROM `sc` 
                WHERE `sc`.`sid`=01
            ) AS `t1`
    ) AS `t2`
    LEFT JOIN `sc`
    ON `t2`.`sid`=`sc`.`sid` AND `t2`.`cid`=`sc`.`cid`
    WHERE `sc`.`sid` IS NULL
) AS `t3`
ON `Student`.`sid`=`t3`.`sid`
WHERE 
    `t3`.`sid` IS NULL AND `Student`.`sid`<> 01


14

查询没学过"张三"老师讲授的任一门课程的学生姓名

SELECT *
FROM `Student`
WHERE `sid` NOT IN (
    SELECT `sc`.`sid`
    FROM `sc`
    RIGHT JOIN (
        SELECT `c`.`cid`
        FROM `Course` AS `c`
        WHERE `c`.`tid` IN (
            SELECT `tid`
            FROM `Teacher`
            WHERE `Tname`="张三"
        )
    ) AS `t1`
    ON `sc`.`cid`=`t1`.`cid`
);


15

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

SELECT 
    `t`.*,
    `Student`.`Sname`
FROM `Student`
RIGHT JOIN (
    SELECT 
        `sid`,
        AVG(`score`) AS `avgScore`
    FROM `sc`
    WHERE `score`<60
    GROUP BY `sid`
    HAVING count(1)>=2
) AS `t`
ON `t`.`sid`=`Student`.`sid`


16

检索" 01 "课程分数小于 60,按分数降序排列的学生信息

SELECT 
    `sc`.`sid`,
    `Student`.*
FROM `sc`
LEFT JOIN `Student`
ON `Student`.`sid`=`sc`.`sid`
WHERE `cid`=01 AND `score`<60
ORDER BY `score` Desc;


17

按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT 
    `sc`.`score`,
    `t`.`avgScore`
FROM `sc`
INNER JOIN (
    SELECT 
        `sid`,
        AVG(`score`) AS `avgScore`
    FROM sc
    GROUP BY `sid`
) AS `t`
ON `t`.`sid`=`sc`.`sid`
ORDER BY `t`.`avgScore` Desc


18

查询各科成绩最高分、最低分和平均分:

以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率

及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90,选修人数要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
SELECT 
    `sc`.`cid`,
    `c`.`Cname`,
    MAX(`score`) AS `max`,
    MIN(`score`) AS `min`,
    AVG(`score`) AS `avg`,
    SUM(CASE WHEN `score`>=60 THEN 1 ELSE 0 END)/COUNT(*) AS `及格率`,
    SUM(CASE WHEN `score` BETWEEN 70 AND 80 THEN 1 ELSE 0 END)/COUNT(*) AS `中等率`,
    SUM(CASE WHEN `score` BETWEEN 80 AND 90 THEN 1 ELSE 0 END)/COUNT(*) AS `优良率`,
    SUM(CASE WHEN `score`>=90 THEN 1 ELSE 0 END)/COUNT(*) AS `优秀率`,
    COUNT(*) AS `count`
FROM `sc`
LEFT JOIN `Course` AS `c`
ON `c`.`cid`=`sc`.`cid`
GROUP BY `cid`
ORDER BY `count` DESC,`cid` ASC


19

按各科成绩进行排序,并显示排名,Score 重复时保留名次空缺

SELECT 
    cid, 
    score, 
    (@rownum:=@rownum+1) AS rownum 
FROM sc,(SELECT @rownum:=0) AS `r`
ORDER BY score DESC;


20

按各科成绩进行排序,并显示排名, Score 重复时合并名次

SELECT 
    `SC`.`cid`,
    (CASE 
        WHEN @fontscore=score THEN @curRank 
        WHEN @fontscore:=score THEN @curRank:=@curRank+1 
    END) AS `rank`,
    `sc`.`score`
FROM 
    `sc`,
    (SELECT @curRank:=0 ,@fontage:=NULL) AS `t`
ORDER BY `sc`.`score` DESC;


21

查询学生的总成绩,并进行排名,总分重复时保留名次空缺

SELECT 
    `t`.*,
    (@rownum:=@rownum+1) AS `rank`
FROM (
    SELECT 
        `sid`,
        sum(`score`) AS `sum`
    FROM `sc`
    GROUP BY `sc`.`sid`
    ORDER BY `sum` DESC
) AS `t`,(SELECT @rownum:=0) AS `r`


22

查询学生的总成绩,并进行排名,总分重复时不保留名次空缺

SELECT 
    `t`.*,
    (CASE 
        WHEN @fontscore=`t`.`sum` THEN @curRank 
        WHEN @fontscore:=`t`.`sum` THEN @curRank:=@curRank+1 
    END) AS `Rank`
FROM (
    SELECT 
        `sid`,
        sum(`score`) AS `sum`
    FROM `sc`
    GROUP BY `sc`.`sid`
    ORDER BY `sum` DESC
) AS `t`,(SELECT @curRank:=0, @fontage:=NULL) AS `r`;


23

统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比

SELECT 
    `Course`.`Cname`,
    `t`.*
FROM `Course`
RIGHT JOIN (
    SELECT
        `cid`,
        sum(CASE WHEN `score`>85 AND `score`<=100 THEN 1 ELSE 0 END) AS `(80,100] cnt`,
        sum(CASE WHEN `score`>85 AND `score`<=100 THEN 1 ELSE 0 END)/count(*) AS `(80,100] %`,
        sum(CASE WHEN `score`>70 AND `score`<=85 THEN 1 ELSE 0 END) AS `(70,85] cnt`,
        sum(CASE WHEN `score`>70 AND `score`<=85 THEN 1 ELSE 0 END)/count(*) AS `(70,85] %`,
        sum(CASE WHEN `score`>60 AND `score`<=70 THEN 1 ELSE 0 END) AS `(60,70] cnt`,
        sum(CASE WHEN `score`>60 AND `score`<=70 THEN 1 ELSE 0 END)/count(*) AS `(60,70] %`,
        sum(CASE WHEN `score`<=60 THEN 1 ELSE 0 END) AS `[0,60] cnt`,
        sum(CASE WHEN `score`<=60 THEN 1 ELSE 0 END)/count(*) AS `[0,60] %`
    FROM `sc`
    GROUP BY `cid`
) AS `t`
ON `t`.`cid`=`Course`.`cid`


24

查询各科成绩前三名的记录

SELECT `a`.*
FROM `SC` AS `a`
LEFT JOIN `SC` AS `b`
ON `a`.`cid`=`b`.`cid`
    AND `a`.`score`<`b`.`score`
GROUP BY `a`.`cid`, `a`.`sid`
HAVING count(`b`.`cid`)<3
ORDER BY `a`.`cid`


25

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

SELECT `cid`,count(`sid`)
FROM `SC`
GROUP BY `cid`


26

查询出只选修两门课程的学生学号和姓名

SELECT `sid`,`Sname`
FROM `Student`
WHERE `sid` IN (
    SELECT `sid`
    FROM `sc`
    GROUP BY `sid`
    HAVING count(`cid`)=2
)


27

查询男生、女生人数

SELECT 
    count(`sid`) AS `cnt`,
    `Ssex`
FROM `Student`
GROUP BY `Ssex`


28

查询名字中含有「风」字的学生信息

SELECT *
FROM `Student`
WHERE `Sname` LIKE "%风%"


29

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

SELECT 
    `Sname`,
    count(*) AS `cnt`
FROM `Student`
GROUP BY `Sname`
HAVING COUNT(*)>1


30

查询 1990 年出生的学生名单

SELECT * 
FROM `Student`
WHERE YEAR(`Sage`)=1990


31

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

SELECT
    `cid`, 
    AVG(`score`) AS `avg`
FROM `sc`
GROUP BY `cid`
ORDER BY AVG(`score`) DESC, `cid` 


32

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

SELECT 
    `t`.*,
    `Student`.`Sname`
FROM `Student`
RIGHT JOIN (
    SELECT 
        `sid`,
        AVG(`score`) AS `AVG`
    FROM `sc`
    GROUP BY `sid`
    HAVING AVG(`score`)>=85
) AS `t`
ON `Student`.`sid`=`t`.`sid`


33

查询课程名称为「数学」,且分数低于 60 的学生姓名和分数

SELECT 
    `Student`.`Sname`,
    `sc`.`score`
FROM `sc`
INNER JOIN (
    SELECT `cid`
    FROM `Course`
    WHERE `Cname`="数学"
) AS `t`
ON `SC`.`cid`=`t`.`cid` AND `sc`.`score`<60
INNER JOIN `Student`
ON `Student`.`sid`=`sc`.`sid`


34

查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)

SELECT
    `sname`,
    `sc`.`cid`,
    `SC`.`score`
FROM `Student` AS `s`
LEFT JOIN `SC`
ON `s`.`sid`=`sc`.`sid`


35

查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

SELECT 
    `SC`.`score`,
    `Student`.`Sname`,
    `c`.`Cname`
FROM `sc`
INNER JOIN `Student`
ON `Student`.`sid`=`SC`.`sid` AND `SC`.`score`>70
INNER JOIN `Course` AS `c`
ON `c`.`cid`=`sc`.`cid`


36

查询不及格的课程

SELECT DISTINCT `cid`
FROM `sc`
WHERE `score`<60


37

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

SELECT 
    `Student`.`sid`,
    `Student`.`Sname`
FROM `sc`
INNER JOIN `Student`
ON `Student`.`sid`=`sc`.`sid` 
    AND `sc`.`score`>80
    AND `sc`.`cid`=01;


38

求每门课程的学生人数

SELECT
    `cid`, 
    COUNT(*) AS `cnt`
FROM `SC`
GROUP BY `cid`


39

成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩

SELECT 
    `s`.*,
    `sc`.`score`
FROM `Course` AS `c`
INNER JOIN `Teacher`
ON `Teacher`.`tid`=`c`.`tid` 
    AND `Teacher`.`Tname`="张三"
INNER JOIN `SC`
ON `sc`.`cid`=`c`.`cid`
INNER JOIN `Student` AS `s`
ON `s`.`sid`=`sc`.`sid`
ORDER BY `sc`.`score` DESC
LIMIT 1


40

成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩

## 改数据,原数据 
UPDATE `sc`
SET `score`=90
WHERE `sid` = "07" AND `cid` ="02";

SELECT student.*, sc.score, sc.cid 
FROM student, teacher, course,sc 
WHERE teacher.tid = course.tid
    AND sc.sid = student.sid
    AND sc.cid = course.cid
    AND teacher.tname = "张三"
    AND sc.score = (
        SELECT Max(sc.score) 
        FROM sc,student, teacher, course
        WHERE teacher.tid = course.tid
            AND sc.sid = student.sid
            AND sc.cid = course.cid
            AND teacher.tname = "张三"
    )
;

## 复原数据
UPDATE `sc`
SET `score`=89.0
WHERE `sid` = "07" AND `cid` ="02";


41

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

SELECT `t1`.*
FROM `SC` AS `t1`
INNER JOIN `sc` AS `t2`
ON `t1`.`sid`=`t2`.`sid`
    AND `t1`.`cid`!=`t2`.`cid`
    AND `t1`.`score`=`t2`.`score`
GROUP BY `cid`,`sid`;


42

查询每门功成绩最好的前两名

SELECT `a`.*
FROM `SC` AS `a`
LEFT JOIN `SC` AS `b`
ON `a`.`cid`=`b`.`cid`
    AND `a`.`score`<`b`.`score`
GROUP BY `a`.`cid`, `a`.`sid`
HAVING count(`b`.`cid`)<2
ORDER BY `a`.`cid`


43

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

SELECT `sid`
FROM `sc`
GROUP BY `sid`
HAVING count(*)>=2


44

查询选修了全部课程的学生信息

SELECT `s`.*
FROM `sc`
LEFT JOIN `Student` AS `s`
ON `s`.`sid`=`sc`.`sid`
GROUP BY `sc`.`sid`
HAVING count(*)=(
    SELECT COUNT(*) AS `cnt` 
    FROM `Course` AS `c`
)


45

查询各学生的年龄,只按年份来算

SELECT
    `sid`,
    `Sname`,
    YEAR(CURDATE()) - YEAR(`Sage`) - (RIGHT(CURDATE(), 5) < RIGHT(`sage`, 5)) AS `age`
FROM `student`


46

按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一

SELECT
    `sid`,
    `Sname`,
    TIMESTAMPDIFF(YEAR, `Student`.`Sage`, CURDATE()) AS `age`
FROM `student`;


47

查询本周过生日的学生

SELECT *
FROM `Student`
WHERE YEARWEEK(`Sage`)=YEARWEEK(CURDATE());


48

查询下周过生日的学生

SELECT *
FROM `Student`
WHERE YEARWEEK(`Sage`)=YEARWEEK(CURDATE()+1);


49

查询本月过生日的学生

SELECT *
FROM `Student`
WHERE date_format(`sage`,"%m")=date_format(NOW(),"%m");


50

查询下月过生日的学生

SELECT *
FROM `Student`
WHERE date_format(`sage`,"%m")=date_format(NOW()+INTERVAL 1 MONTH,"%m");


SQL还是不太熟悉,得练练,其次还有性能的优化等问题需要考虑