美文网首页
MySQL练习题

MySQL练习题

作者: 你好旧时光912 | 来源:发表于2019-04-04 14:29 被阅读0次

    SELECT * FROM course

    SELECT * FROM score

    SELECT * FROM teacher

    SELECT * FROM student

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

    第一步:查询"01"课程的信息

    SELECT * FROM score WHERE c_id ='01'

    第二步: 查询"02"课程的信息

    SELECT * FROM score WHERE c_id='02'

    第三步:  查询"01"课程比"02"课程成绩高的学生号(左连接)

    SELECT a.s_id

    FROM

    (SELECT * FROM score WHERE c_id ='01') AS a

    LEFT JOIN

    (SELECT * FROM score WHERE c_id='02') AS b

    ON a.s_id=b.s_id

    WHERE a.s_score>b.s_score OR b.s_id IS NULL

    第四步:根据第三步的学生号查询学生的信息及课程号、课程分数

    SELECT student.*,score.c_id,score.s_score

    FROM student

    JOIN score

    ON student.s_id=score.s_id

    WHERE student.s_id IN(SELECT a.s_id

    FROM (SELECT * FROM score WHERE c_id ='01') AS a

    LEFT JOIN  (SELECT * FROM score WHERE c_id='02') AS b

    ON a.s_id=b.s_id

    WHERE a.s_score>b.s_score OR b.s_id IS NULL)


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

    第一步

    SELECT * FROM student WHERE c_id='01'

    第二步

    SELECT * FROM student WHERE c_id='02'

    第三步(右连接)

    SELECT b.s_id

    FROM (SELECT * FROM score WHERE c_id='01') AS a

    RIGHT JOIN (SELECT * FROM score WHERE c_id='02') AS b

    ON a.s_id =b.s_id

    WHERE a.s_score<b.s_score OR a.s_id IS NULL

    第四步

    SELECT student.*,score.c_id,score.s_score

    FROM student

    JOIN score

    ON student.s_id=score.s_id

    WHERE student.s_id IN(SELECT b.s_id

    FROM (SELECT * FROM score WHERE c_id='01') AS a

    RIGHT JOIN (SELECT * FROM score WHERE c_id='02') AS b

    ON a.s_id =b.s_id

    WHERE a.s_score<b.s_score OR a.s_id IS NULL)


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

    内连接

    SELECT s.s_id,s.s_name,AVG(sc.s_score)

    FROM student AS s

    JOIN score AS sc

    ON s.s_id = sc.s_id

    GROUP BY s.s_id

    HAVING AVG(sc.s_score)>=60


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

    (包括有成绩的和无成绩的)

    (左连接)

    SELECT s.s_id,s.s_name,AVG(sc.s_score)

    FROM student AS s

    LEFT JOIN score AS sc

    ON s.s_id=sc.s_id

    GROUP BY s.s_id

    HAVING AVG(sc.s_score)<60 OR AVG(sc.s_score) IS NULL


    5所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

    SELECT s.s_id,s.s_name,COUNT(*) AS 选课总数,SUM(sc.s_score) AS 总成绩

    FROM student AS s

    JOIN score AS sc

    ON s.s_id=sc.s_id

    GROUP BY s.s_id


    6查询"李"姓老师的数量

    SELECT COUNT(*) AS 数量

    FROM teacher

    WHERE t_name LIKE  '李%'


    7查询学过"张三"老师授课的同学的信息

    第一步:找出学过“张三老师”的学生学号

    SELECT sc.s_id

    FROM score AS sc,teacher AS t,course AS c

    WHERE sc.c_id=c.c_id

    AND t.t_id=c.t_id

    AND t.t_name='张三'

    第二步

    SELECT s.*

    FROM student AS s

    WHERE s.s_id IN

    (SELECT sc.s_id

    FROM score AS sc,teacher AS t,course AS c

    WHERE sc.c_id=c.c_id

    AND t.t_id=c.t_id

    AND t.t_name='张三')


    8查询没学过"张三"老师授课的同学的信息

    第一步:找出学过“张三老师”的学生学号

    SELECT sc.s_id

    FROM score AS sc,teacher AS t,course AS c

    WHERE sc.c_id=c.c_id

    AND t.t_id=c.t_id

    AND t.t_name='张三'

    第二步

    SELECT s.*

    FROM student AS s

    WHERE s.s_id NOT IN

    (SELECT sc.s_id

    FROM score AS sc,teacher AS t,course AS c

    WHERE sc.c_id=c.c_id

    AND t.t_id=c.t_id

    AND t.t_name='张三')


    9学过编号为"01"并且也学过编号为"02"的课程的同学的信息

    第一种写法

    第一步

    SELECT s_id FROM score WHERE c_id='01'

    第二步

    SELECT s_id FROM score WHERE c_id='02'

    第三步

    SELECT a.s_id

    FROM (SELECT s_id FROM score WHERE c_id='01') AS a

    JOIN (SELECT s_id FROM score WHERE c_id='02') AS b

    ON a.s_id=b.s_id

    第四步

    SELECT student.*

    FROM student

    WHERE s_id IN(SELECT a.s_id

    FROM (SELECT s_id FROM score WHERE c_id='01') AS a

    JOIN (SELECT s_id FROM score WHERE c_id='02') AS b

    ON a.s_id=b.s_id)

    第二种写法

    SELECT s.*

    FROM student AS s,

    (SELECT score.s_id

    FROM score

    WHERE score.c_id='01') AS a,

    (SELECT score.s_id

    FROM score

    WHERE score.c_id='02') AS b

    WHERE s.s_id=a.s_id

    AND s.s_id=b.s_id


    10学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

    第一步

    SELECT s_id FROM score WHERE c_id='01'

    第二步

    SELECT s_id FROM score WHERE c_id='02'

    第三步:(左连接,找出学过‘01’但没有学过‘02’的学生编号)

    SELECT a.s_id

    FROM (SELECT s_id FROM score WHERE c_id='01') AS a

    LEFT JOIN (SELECT s_id FROM score WHERE c_id='02') AS b

    ON a.s_id=b.s_id

    WHERE b.s_id IS NULL

    第四步

    SELECT student.*

    FROM student

    WHERE s_id IN(SELECT a.s_id

    FROM (SELECT s_id FROM score WHERE c_id='01') AS a

    LEFT JOIN (SELECT s_id FROM score WHERE c_id='02') AS b

    ON a.s_id=b.s_id

    WHERE b.s_id IS NULL)


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

    第一步

    SELECT s_id

    FROM score

    GROUP BY s_id

    HAVING COUNT(*)!=(SELECT COUNT(DISTINCT c_id) FROM course)

    第二步

    SELECT student.*

    FROM student

    WHERE s_id IN(SELECT s_id

    FROM score

    GROUP BY s_id

    HAVING COUNT(*)!=(SELECT COUNT(DISTINCT c_id)   FROM course))


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

    第一步

    SELECT c_id

    FROM score

    WHERE s_id='01'

    第二步

    SELECT student.*

    FROM student

    JOIN score

    ON student.s_id=score.s_id

    WHERE score.c_id IN (SELECT c_id   FROM score  WHERE s_id='01')

    AND student.s_id !='01'

    GROUP BY student.s_id


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

    SELECT *

    FROM student

    JOIN score

    WHERE student.s_id=score.s_id

    GROUP BY student.s_id

    HAVING score.c_id IN (SELECT c_id   FROM score   WHERE s_id='01')

    AND student.s_id !='01'

    AND COUNT(score.c_id)=(SELECT COUNT(score.c_id) FROM score WHERE s_id='01')


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

    第一步

    SELECT sc.s_id

    FROM teacher AS t,course AS c,score AS sc

    WHERE t.t_id=c.t_id

    AND sc.c_id=c.c_id

    AND t.t_name='张三'

    第二步

    SELECT student.*

    FROM student

    WHERE student.s_id NOT IN

    (SELECT sc.s_id

    FROM teacher AS t,course AS c,score AS sc

    WHERE t.t_id=c.t_id

    AND sc.c_id=c.c_id

    AND t.t_name='张三')


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

    第一步

    SELECT s_id

    FROM score

    WHERE s_score<60

    GROUP BY s_id

    HAVING COUNT(*)>=2

    第二步

    SELECT s.s_id,s.s_name,AVG(sc.s_score)

    FROM student AS s

    JOIN score AS sc

    ON s.s_id=sc.s_id

    GROUP BY s.s_id

    HAVING s.s_id IN

    (SELECT s_id

    FROM score

    WHERE s_score<60

    GROUP BY s_id

    HAVING COUNT(*)>=2)


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

    SELECT  student.*

    FROM student

    JOIN score

    ON student.s_id=score.s_id

    WHERE score.c_id='01'

    AND score.s_score<60

    ORDER BY score.s_score DESC


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

    SELECT s_id AS sid,

    (SELECT s_score FROM score WHERE s_id=sid AND c_id='01') AS 语文,

    (SELECT s_score FROM score WHERE s_id=sid AND c_id='02') AS 数学,

    (SELECT s_score FROM score WHERE s_id=sid AND c_id='03') AS 英语,

    AVG(s_score) AS 平均成绩

    FROM score

    GROUP BY s_id

    ORDER BY 平均成绩 DESC


    18.查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率

    SELECT

    sc.c_id,

    c.c_name,

    MAX(s_score) AS 最高分,

    MIN(s_score) AS 最低分,

    AVG(s_score) AS 平均分,

    SUM(CASE WHEN s_score>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格率,

    SUM(CASE WHEN s_score>=70 AND s_score<80 THEN 1 ELSE 0 END)/COUNT(*) AS 中等率,

    SUM(CASE WHEN s_score>=80 AND s_Score<90 THEN 1 ELSE 0 END)/COUNT(*) AS 优良率,

    SUM(CASE WHEN s_score>90 THEN 1 ELSE 0 END)/COUNT(*) AS 优秀率

    FROM score AS sc

    JOIN course AS c

    ON sc.c_id=c.c_id

    GROUP BY sc.c_id


    19按各科成绩进行排序,并显示排名(实现不完全)


    20查询学生的总成绩并进行排名


    21查询不同老师所教不同课程平均分从高到低显示

    SELECT t.t_id, c.c_id,AVG(sc.s_score) AS 平均成绩

    FROM score AS sc,teacher AS t,course AS c

    WHERE sc.c_id=c.c_id

    AND t.t_id=c.t_id

    GROUP BY t.t_id,c.c_id

    ORDER BY 平均成绩 DESC


    22查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

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

    SELECT COUNT(*),

          c.c_id,

          c.c_name,

          SUM(CASE WHEN sc.s_score>=85 AND sc.s_score<=100 THEN 1 ELSE 0 END)/COUNT(*) AS '[85-100]',

          SUM(CASE WHEN sc.s_score>=70 AND sc.s_score<85 THEN 1 ELSE 0 END)/COUNT(*)  AS '[85-70]',

          SUM(CASE WHEN sc.s_score>=60 AND sc.s_score<70 THEN 1 ELSE 0 END)/COUNT(*) AS '[70-60]',

          SUM(CASE WHEN sc.s_score>=0 AND sc.s_score<60 THEN 1 ELSE 0 END)/COUNT(*) AS '[0-60]'

    FROM score AS sc

    JOIN course c

    ON c.c_id=sc.c_id

    GROUP BY c.c_id


    24查询学生平均成绩及其名次


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

    SELECT c_id AS cid,

    (SELECT s_score FROM score WHERE c_id =cid ORDER BY s_score DESC  LIMIT 1) AS 第一名,

    (SELECT s_score FROM score WHERE c_id =cid ORDER BY s_score DESC LIMIT 1,1) AS 第二名,

    (SELECT s_score FROM score WHERE c_id =cid ORDER BY s_score DESC LIMIT 2,1) AS 第三名

    FROM score

    GROUP BY cid


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

    SELECT c_id,COUNT(*) AS 人数

    FROM score

    GROUP BY c_id


    27查询出只有两门课程的全部学生的学号和姓名

    SELECT s.s_id,s.s_name

    FROM student AS s

    JOIN score AS sc

    ON s.s_id=sc.s_id

    GROUP BY s.s_id

    HAVING COUNT(*)=2


    28查询男生、女生人数

    SELECT s_sex,COUNT(*)

    FROM student

    GROUP BY s_sex


    29查询名字中含有"风"字的学生信息

    SELECT *

    FROM student

    WHERE s_name LIKE '%风%'


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

    SELECT a.s_name,a.s_sex,COUNT(*) AS 同名人数

    FROM student AS a

    JOIN student AS b

    ON a.s_id !=b.s_id

    AND a.s_name=b.s_name

    AND a.s_sex=b.s_sex

    GROUP BY a.s_name


    31查询1990年出生的学生名单

    第一种表达方式:

    SELECT student.*

    FROM student

    WHERE YEAR(s_birth)=1990

    第二种表达方式 :                                                                  

    select student.*

    FROM student

    WHERE DATE_FORMAT(s_birth,'%Y')=1990


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

    SELECT c_id, AVG(s_score)

    FROM score

    GROUP BY c_id

    ORDER BY AVG(s_score) DESC,c_id ASC


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

    SELECT s.s_id,s.s_name,AVG(sc.s_score)

    FROM student AS s

    JOIN score AS sc

    ON s.s_id=sc.s_id

    GROUP BY s.s_id

    HAVING AVG(sc.s_score)>=85


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

    SELECT s.s_name,sc.s_score

    FROM student AS s

    JOIN score AS sc

    ON s.s_id=sc.s_id

    JOIN course AS c

    ON c.c_id=sc.c_id

    WHERE c.c_name='数学'

    AND sc.s_score<60


    35查询所有学生的课程及分数情况

    SELECT student.s_id AS sid,

          student.s_name,

    (SELECT s_score FROM score WHERE s_id=sid AND c_id='01') AS 语文,

    (SELECT s_score FROM score WHERE s_id=sid AND c_id='02') AS 数学,

    (SELECT s_score FROM score WHERE s_id=sid AND c_id='03') AS 英语

    FROM student

    JOIN score

    ON student.s_id=score.s_id

    GROUP BY student.s_id


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

    SELECT s.s_name,c.c_name,sc.s_score

    FROM student AS s

    JOIN score AS sc

    ON s.s_id =sc.s_id

    JOIN course AS c

    ON c.c_id=sc.c_id

    WHERE sc.s_score>70


    37查询不及格的课程

    SELECT s.s_name, c.c_name, sc.s_score

    FROM score AS sc

    JOIN course AS c

    ON sc.c_id=c.c_id

    JOIN student AS s

    ON sc.s_id=s.s_id

    WHERE sc.s_score<60


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

    SELECT s.s_id,s.s_name

    FROM student AS s

    JOIN score AS sc

    ON s.s_id=sc.s_id

    WHERE sc.c_id='01'

    AND sc.s_score>80


    39求每门课程的学生人数

    SELECT c_id,COUNT(*) AS 学生人数

    FROM score

    GROUP BY c_id


    40查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩

    SELECT student.*,MAX(score.s_score)

    FROM student

    JOIN score

    ON student.s_id=score.s_id

    WHERE student.s_id IN(SELECT sc.s_id

    FROM course AS c

    JOIN teacher AS t

    ON c.t_id=t.t_id

    JOIN score AS sc

    ON c.c_id=sc.c_id

    WHERE t.t_name='张三')


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

    SELECT DISTINCT a.s_id,a.c_id,a.s_score

    FROM score AS a

    JOIN score AS b

    ON a.s_id=b.s_id

    WHERE a.c_id!=b.c_id

    AND a.s_score=b.s_score


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

    SELECT c_id AS cid,

    (SELECT s_score FROM score WHERE c_id=cid ORDER BY s_score DESC LIMIT 1) AS 第一名,

    (SELECT s_score FROM score WHERE c_id=cid ORDER BY s_score DESC LIMIT 1,1) AS 第二名

    FROM score

    GROUP BY c_id


    43统计每门课程的学生选修人数(超过5人的课程才统计)

    要求输出课程号和选修人数,查询结果按人数降序排列

    若人数相同,按课程号升序排列

    SELECT c_id,COUNT(*) AS 选修人数

    FROM score

    GROUP BY c_id

    HAVING COUNT(*)>5

    ORDER BY 选修人数 DESC,c_id ASC


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

    SELECT s_id

    FROM score

    GROUP BY s_id

    HAVING COUNT(*)>=2


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

    SELECT student.*

    FROM student

    JOIN score

    ON student.s_id=score.s_id

    GROUP BY student.s_id

    HAVING COUNT(*)=(SELECT COUNT(DISTINCT c_id)

                    FROM course)


    46查询各学生的年龄

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

    SELECT s_name,s_birth,

    (DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(s_birth,'%Y')-

    (CASE WHEN DATE_FORMAT(NOW(),'%m%d')<DATE_FORMAT(s_birth,'%m%d') THEN 1 ELSE 0 END))  AS 年龄

    FROM student


    47查询本周过生日的学生

    SELECT student.*

    FROM student

    WHERE WEEK(s_birth,1)=WEEK(CURDATE(),1)

    SELECT DATE(NOW())

    SELECT CURDATE()


    48查询下周过生日的学生

    SELECT student.*

    FROM student

    WHERE WEEK(s_birth,1)=WEEK(CURDATE(),1)+1


    49查询本月过生日的学生

    SELECT student.*

    FROM student

    WHERE MONTH(s_birth)=MONTH(CURDATE())


    50查询下月过生日的学生

    SELECT student.*

    FROM student

    WHERE DATE_FORMAT(s_birth,'%m')=DATE_FORMAT(NOW(),'%m')+1

    SELECT student.*

    FROM student

    WHERE MONTH(s_birth)=MONTH(CURDATE())+1

    SELECT(NOW())

    SELECT(DATE(NOW()))

    SELECT(YEAR(NOW()))

    SELECT(MONTH(NOW()))

    SELECT(DAY(NOW()))

    SELECT(TIME(NOW()))

    SELECT(DATE_FORMAT(NOW(),'%Y'))

    SELECT(YEAR(NOW()))

    相关文章

      网友评论

          本文标题:MySQL练习题

          本文链接:https://www.haomeiwen.com/subject/yjgfiqtx.html