0
0

Mysql经典例题练习与总结

一、创建数据表并插入数据

-- 1、学生表-- Student(s_id,s_name,s_birth,s_sex) ：学生编号、姓名、年月、性别CREATETABLEIFNOTEXISTS`Student`(`s_id`VARCHAR(20),`s_name`VARCHAR(20)NOTNULLDEFAULT'',`s_birth`VARCHAR(20)NOTNULLDEFAULT'',`s_sex`VARCHAR(10)NOTNULLDEFAULT'',PRIMARYKEY(`s_id`))ENGINE=INNODBDEFAULTCHARSET= utf8;-- 插入数据INSERTINTO Student VALUES('01','赵雷','1990-01-01','男');INSERTINTO Student VALUES('02','钱电','1990-12-21','男');INSERTINTO Student VALUES('03','孙风','1990-05-20','男');INSERTINTO Student VALUES('04','李云','1990-08-06','男');INSERTINTO Student VALUES('05','周梅','1991-12-01','女');INSERTINTO Student VALUES('06','吴兰','1992-03-01','女');INSERTINTO Student VALUES('07','郑竹','1989-07-01','女');INSERTINTO Student VALUES('08','王菊','1990-01-20','女');

-- 2、课程表-- Course(c_id,c_name,t_id) ：课程编号、 课程名称、 教师编号CREATETABLEIFNOTEXISTS`Course`(`c_id`VARCHAR(20),`c_name`VARCHAR(20)NOTNULLDEFAULT'',`t_id`VARCHAR(20)NOTNULL,PRIMARYKEY(`c_id`))ENGINE=INNODBDEFAULTCHARSET= utf8;-- 插入数据INSERTINTO Course VALUES('01','语文','02');INSERTINTO Course VALUES('02','数学','01');INSERTINTO Course VALUES('03','英语','03');

-- 3、教师表-- Teacher(t_id,t_name) ：教师编号、教师姓名CREATETABLEIFNOTEXISTS`Teacher`(`t_id`VARCHAR(20),`t_name`VARCHAR(20)NOTNULLDEFAULT'',PRIMARYKEY(`t_id`))ENGINE=INNODBDEFAULTCHARSET= utf8;-- 插入数据INSERTINTO Teacher VALUES('01','张三');INSERTINTO Teacher VALUES('02','李四');INSERTINTO Teacher VALUES('03','王五');

-- 4、成绩表-- Score(s_id,c_id,s_score) ：学生编号、课程编号、分数CREATETABLEIFNOTEXISTS`Score`(`s_id`VARCHAR(20),`c_id`VARCHAR(20),`s_score`INT(3),PRIMARYKEY(`s_id`,`c_id`))ENGINE=INNODBDEFAULTCHARSET= utf8;-- 插入数据INSERTINTO Score VALUES('01','01',80);INSERTINTO Score VALUES('01','02',90);INSERTINTO Score VALUES('01','03',99);INSERTINTO Score VALUES('02','01',70);INSERTINTO Score VALUES('02','02',60);INSERTINTO Score VALUES('02','03',80);INSERTINTO Score VALUES('03','01',80);INSERTINTO Score VALUES('03','02',80);INSERTINTO Score VALUES('03','03',80);INSERTINTO Score VALUES('04','01',50);INSERTINTO Score VALUES('04','02',30);INSERTINTO Score VALUES('04','03',20);INSERTINTO Score VALUES('05','01',76);INSERTINTO Score VALUES('05','02',87);INSERTINTO Score VALUES('06','01',31);INSERTINTO Score VALUES('06','03',34);INSERTINTO Score VALUES('07','02',89);INSERTINTO Score VALUES('07','03',98);

##

二、开始解题

2.1 题目1：查询"01"课程比"02"课程成绩高的学生的信息、课程分数

思路: 将01课程成绩和02课程成绩要对比,所以必须要2个成绩表对比,
解法1:三表联合 筛选
解法2:根据学号查询两表对比后符合条件的学生

select*FROM student a,score b,score c 
    WHERE a.s_id = b.s_id
        and a.s_id = c.s_id
        and b.c_id ='01'and c.c_id ='02'and b.s_score > c.s_score

2.2 题目2：查询平均成绩大于等于60分且总分大于200分的同学且必须考3门的学生编号和学生姓名和平均成绩

思路:
1.必须考三门 成绩表count(s_score) = 3
2.平均成绩大于等于60 avg(s_score) >= 60
注意,这里因为平均成绩可能是循环小数,所以用到保留函数
round(x,y) X是所要修改的值,y表示修改后的小数位数

3.总分大于200 sum(s_score) > 200
三个都是和成绩有关,而且学生编号是主体,显然按学生分组
group by s_id

select 
    a.s_id,
    a.s_name,ROUND(AVG(b.s_score),2) avg_score,ROUND(SUM(b.s_score),2) sum_score
from 
    student a
JOIN score b  on a.s_id = b.s_id
GROUPBY
    a.s_id
HAVING
     avg_score >=60and sum_score >200andcount(b.s_score)=3;

在这里插入图片描述

2.3 题目3：查询平均成绩小于60分的同学的学生编号、学生姓名、平均成绩（包括有成绩的和无成绩）

思路:
1.平均成绩小于60 avg(s_score) < 60
2.包括无成绩 ,ifnull 如果是NULL 则为0 而且无成绩,是每个学生都要有成绩,所以需要学生表左连接右表 left join on

select 
    a.s_id,
    a.s_name,ROUND(AVG(IFNULL(b.s_score,0)),2) avg_score
from student a
leftjoin score b on a.s_id = b.s_id
groupby a.s_id
having 
    avg_score <60

在这里插入图片描述

题目4：查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

思路:
1.查询学生姓名 student
2.选课总数 count(score.c_id)
3.所有课程总成绩 sum(score.)

select 
    a.s_id 学生编号,
    a.s_name 学生姓名,COUNT(b.c_id) 选课总数,SUM(IFNULL(b.s_score,0)) 总成绩
    from student a
    leftjoin score b on a.s_id = b.s_id
    GROUPBY a.s_id
    ORDERBY 总成绩 desc

在这里插入图片描述

题目5：查询“李”姓老师的数量

思路:like ''李% ’
_代表一个占位符 %代表任意个占位符

select*from teacher
WHERE teacher.t_name LIKE"李%"

题目6：查询学过张三老师授课的同学信息

思路:
解法1.多表联合查询张三老师授课的学生
解法2. 张三在教师表中,从课程表中找出张三课id,然后和成绩表连接,最后取出学生信息

select a.*from student a
join score b
on a.s_id = b.s_id
WHERE b.c_id in(select c.c_id from course c where c.t_id =(SELECT teacher.t_id from teacher WHERE t_name ='张三'))

select a.*from 
    student a
    join score b on a.s_id = b.s_id
    join course c on b.c_id = c.c_id
    join teacher d on c.t_id = d.t_id
    WHERE d.t_name ='张三';

在这里插入图片描述

题目7：找出没有学过张三老师课程的学生

思路 先将学过张三老师课程的学生找出来,再not in

SELECT t.*from student t
WHERE t.s_id notin(select a.s_id
    from student a
    join score b
    on a.s_id = b.s_id
    WHERE b.c_id in(select c.c_id from course c where c.t_id =(SELECT teacher.t_id from teacher WHERE t_name ='张三')))

在这里插入图片描述

题目8：查询学过编号为01，并且学过编号为02课程的学生信息

思路:
方法1 : 成绩表自连接, 左表学01课程,右表学02课程,然后学号相等,就是两个都学过的学生 ,然后student查询
方法2 学过02课程的学生 学号 in 学过01的学生学号 ,然后student 查询

select c.*from score a
join score b on a.s_id = b.s_id and a.c_id ='01'and b.c_id ='02'join student c on a.s_id = c.s_id;

在这里插入图片描述

题目9：查询学过01课程，但是没有学过02课程的学生信息

思路: 学过01课程的学生 not in 学过02课程的学生 ,然后student查询

select a.*from student a
join score b on a.s_id = b.s_id and b.c_id ='01'WHERE a.s_id notin(select c.s_id from score c WHERE c.c_id ='02');

在这里插入图片描述

题目10：查询没有学完全部课程的同学的信息(本篇内容不考虑重修)

思路: 成绩表中,按学号分组,根据每个学生学习过的课程数量与课程总数对比

select a.*,count(b.c_id) cnt 
from student a 
join score b on a.s_id = b.s_id
GROUPBY a.s_id
having cnt <(selectcount(c_id)from course)

在这里插入图片描述

题目11：查询至少有一门课与学号为01的同学所学相同的同学的信息

思路:
同学学习过的课程只需要有一个在学号01学生学过的课程中即可
c_id in( 01学过的课程)

select a.*from student a
join score b on a.s_id = b.s_id and a.s_id !='01'WHERE b.c_id in(select c.c_id from score c where c.c_id ='01')

在这里插入图片描述

题目12：查询和01同学学习的课程完全相同的同学的信息

思路:
方法1:首先 个数要相同 其次 同学没学过的课程01没学过
方法2:使用group_concat将列连接,这样就可以直接进行相等了

select a.*,GROUP_CONCAT(b.c_id) course_t
from student a
join score b on b.s_id = a.s_id and a.s_id !='01'GROUPBY a.s_id
HAVING course_t =(select GROUP_CONCAT(c.c_id)from score c WHERE c.s_id ='01');

在这里插入图片描述

题目13：查询没有修过张三老师讲授的任何一门课程的学生姓名

思路: 这个题的话,找出张三老师教授的课程,然后找出学过的学生,not in 学号

select s.*from student s 
WHERE s.s_id notin(select a.s_id -- 1 2 3 4 5 7 from score a 
        join course b on a.c_id = b.c_id
        join teacher c on c.t_id = b.t_id and c.t_name ='张三')ORDERBY s_id

在这里插入图片描述

题目14：检索01课程分数小于60，按分数降序排列的学生信息

select a.*,b.s_score
from 
    student a  
join 
    score b on a.s_id = b.s_id 
                 and b.s_score <60and b.c_id ='01'orderby b.s_score desc;

题目15：按平均成绩从高到低（降序）显示所有学生的所有课程的成绩以及平均成绩

思路:
注意这里要显示所有课程的成绩,以及平均成绩
方法1: 每个课程的成绩都得查询,并且一个课程一个表,就需要原成绩表 3个单列成绩表 4个表,这样太复杂了
方法2: case when
在这里插入图片描述

select 
        a.s_id
        ,max(casewhen a.c_id ='01'then a.s_score else0end) 语文
        ,max(casewhen a.c_id ='02'then a.s_score else0end) 数学
        ,max(casewhen a.c_id ='03'then a.s_score else0end) 英语
        ,ROUND(AVG(a.s_score)) avgr
from score a
GROUPBY a.s_id
orderBY avgr desc

题目16：查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率（及格：>=60），中等率（中等为：70-80），优良率（优良为：80-90），优秀率（优秀为：>=90）

思路:
根据c_id 分组 然后max(s_score) min(s_score) avg(s_score) case
注意 因为执行顺序 GROUP BY 先于select 所以 select 的每行的数据都是对应group by的,所以这时候对应的每个课程的全部分数

select 
        a.c_id
        ,max(a.s_score),min(a.s_score),round(sum(casewhen a.s_score >=60then1else0end)/sum(casewhen a.s_score  then1else0end),2) 及格率
        ,round(sum(casewhen a.s_score >=70and a.s_score <=80then1else0end)/sum(casewhen a.s_score  then1else0end),2) 中等率
        ,round(sum(casewhen a.s_score >=80and a.s_score <=90then1else0end)/sum(casewhen a.s_score  then1else0end),2) 优良率
        ,round(sum(casewhen a.s_score >=90then1else0end)/sum(casewhen a.s_score  then1else0end),2) 优良率
from score a 
GROUPBY a.c_id

题目17：按照各科成绩进行排序，并且显示排名—比较综合，多看！

思路: 按照各科成绩成绩进行排名, 这个题的话,需要进行union拼接

set@rank=0;select 
        a.c_id
        ,a.s_score
        ,(selectCOUNT(DISTINCT t.s_score)from score t
            WHERE t.s_score >= a.s_score and a.c_id = t.c_id) 语文排名
from score a 
orderby a.c_id,a.s_score  desc;

在这里插入图片描述

题目18：查询学生的总成绩，并进行排名—比较综合，多看！

思路: 先把排名查出来看看,因为要考虑并列情况
按课程号和成绩进行分组,这样就可以按课程和成绩进行组合排名

select 
        a.c_id
        ,a.s_score
        ,(selectCOUNT(DISTINCT t.s_score)from score t
            WHERE t.s_score >= a.s_score and a.c_id = t.c_id) 单科排名
from score a 
orderby a.c_id,a.s_score  desc;

在这里插入图片描述

题目19：查询学生的总成绩，并进行排名—比较综合，多看！
– 整2个总分表 总分表是一个学生对好几门课的成绩，所以按成绩分组了
– 然后总分表进行比较查出该学生的排名

select t1.s_id,t1.sc1_sum,count(*)ASnofrom(select sc1.s_id,SUM(sc1.s_score) sc1_sum
    from score sc1 
    GROUPBY sc1.s_id) t1 
    join(select sc2.s_id,SUM(sc2.s_score) sc2_sum
    from score sc2 
    GROUPBY sc2.s_id) t2 on t1.sc1_sum <= t2.sc2_sum
GROUPBY t1.s_id
orderbyno;

在这里插入图片描述

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

思路: 先按成绩进行排名, 然后按照limit查询出需要的学生信息

select a1.*,t1.s_score,@cid :='01' c_id
from student a1
join(select c1.s_id,c1.s_score 
    from score c1 
    WHERE c1.c_id ='01'ORDERBY c1.s_score
    limit1,2) t1 on a1.s_id = t1.s_id

UNIONselect a2.*,t2.s_score,@cid :='02' c_id
from student a2
join(select c2.s_id,c2.s_score 
    from score c2 
    WHERE c2.c_id ='02'ORDERBY c2.s_score
    limit1,2) t2 on a2.s_id = t2.s_id
    
UNIONselect a3.*,t3.s_score,@cid :='03' c_id
from student a3
join(select c3.s_id,c3.s_score 
    from score c3 
    WHERE c3.c_id ='03'ORDERBY c3.s_score
    limit1,2) t3 on a3.s_id = t3.s_id;

在这里插入图片描述

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

思路: 按课程编号进行分组,然后使用case when 语句分析出每科的相应分段人数
解法只写了一个段的类型占比

select 
        b.c_id
        ,b.c_name
        ,ROUND(SUM(casewhen a.s_score >=85and  a.s_score <=100then1else0end)/count(*),2)'100-80',ROUND(SUM(casewhen a.s_score <=85and  a.s_score >=70then1else0end)/count(*),2)'85-70',ROUND(SUM(casewhen a.s_score <=70and  a.s_score >=60then1else0end)/count(*),2)'70-60',ROUND(SUM(casewhen a.s_score <=60and  a.s_score >=0then1else0end)/count(*),2)'0-60',count(*)from score a
join course b on a.c_id = b.c_id
GROUPBY a.c_id;

在这里插入图片描述

题目22：查询学生的平均成绩及名次—比较综合，多看，定义变量，实现rank函数

-- 分析  先查询平均成绩   select
 a.s_id  -- 学号,@i:=@i+1as'不保留空缺排名'-- 直接i的自加，顺序一直变大，声明变量需加@ 由于要放入列中需要 :=,@k:=(casewhen@i=1or@avg_score=a.avg_s  then@kelse@k+1end)as'保留空缺排名'-- 因为第一次必定是1所以和i一致为1   后面则按分数是否和上一个相同排名,@avg_score:=avg_s as'平均分'-- 表a中的值from(select 
       s_id
       ,round(avg(s_score),2)as avg_s
      from Score 
      groupby s_id
      orderby2desc)a    -- 表a：平均成绩的排序和学号,(select@avg_score:=0,@i:=0,@k:=1) b -- 表b：进行变量初始化，固定写法。--  order by时,把要定义的变量定义在放在后面

在这里插入图片描述

题目23：查询每门课被选修的学生数

思路：根据课程统计有成绩的学生 成绩表和课程表联合查询

select a.c_id,COUNT(b.s_score)from course a
join score b on a.c_id = b.c_id
GROUPBY a.c_id

在这里插入图片描述

题目24：查询出只有两门课程的全部学生的学号和姓名

思路：根据学号对成绩表分组，然后统计每个学生的课程分数 再通过学生表查询即可
在这里插入图片描述

select a.s_id,a.s_name
from student a 
join score b on b.s_id = a.s_id
GROUPBY b.s_id
HAVINGcount(b.s_score)=2;

题目24：查询男女生人数

思路：根据性别分组查询，然后count

select s_sex,COUNT(s_sex)from student a 
GROUPBY s_sex

在这里插入图片描述

题目24：查询名字中含有风字的学生信息

思路： 含有风 使用% %风% % 代指任意个字符包括0个

select a.*from student a 
WHERE a.s_name LIKE'%风%';

在这里插入图片描述

本题选自https://blog.csdn.net/qq_40216188/article/details/118670474

题目24：查询同名同性的学生名单，并统计同名人数

思路： 姓名相同性别相同，要进行比较，学生表自连接， 姓名相同 性别相同 学号不同 查询 distinct姓名

select  a.s_name,COUNT(b.s_id)from student a  
join student b on a.s_name = b.s_name 
                            and a.s_id!= b.s_id 
                            and a.s_sex = b.s_sex
GROUPBY a.s_name

在这里插入图片描述

题目25：查询每门课程的平均成绩，结果按平均成绩降序排列；平均成绩相同时，按课程编号c_id升序排列

思路：查询每门课程的平均成绩，按成绩表的课程号分组 平均

SELECT a.c_id,ROUND(AVG(a.s_score),2)  avgr
from score a 
GROUPBY a.c_id
ORDERBY avgr desc,c_id asc;

在这里插入图片描述

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

思路：

  1. 查询学生的姓名 学生表
  2. 平均成绩 成绩表
  3. 平均成绩， 成绩表按学号分组 group by(score.s_id)
  4. 平均成绩大于85 having avg(s_score) > 85
  5. 子查询或者联合查询
select 
        a.s_id
        ,a.s_name
        ,ROUND(AVG(b.s_score),2) avgr 
from student a 
join score b  on a.s_id = b.s_id
GROUPBY b.s_id
HAVINGAVG(s_score)>85

在这里插入图片描述

题目27：查询所有学生的课程及分数（均分、总分）情况

思路：
1.学生ID是主要的，要查询均分和总分， 所以必定是group by 学生id avg sum
2.因为要查询每门成绩，所以这里需要用case when 否则就得用 多表拼接，太麻烦

select 
        a.s_id
        ,max(caseWHEN c_id ='01'then a.s_score else0END) 语文
        ,max(caseWHEN c_id ='02'then a.s_score else0END) 数学
        ,max(caseWHEN c_id ='03'then a.s_score else0END) 英语
        ,ROUND(AVG(a.s_score),2) 平均分
        ,SUM(a.s_score) 总分
from score a
GROUPBY s_id

题目28：查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

思路：
这个题有点奇怪，如果是多个学生并列的话，应该怎么做呢，假设前提是张三只教一门课程,用having直接过滤选出成绩等于最大的学生ID，然后信息就student查询

select a.s_id,a.s_score,t.*from score a 
join student t on a.s_id = t.s_id
join course b  on a.c_id = b.c_id
join teacher c on b.t_id = c.t_id  
                          and c.t_name ='张三'HAVING a.s_score =max(s_score)ORDERBY a.s_score desc;

在这里插入图片描述

题目29:查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩(不需要看)

思路：
1.不同课程成绩相同 课程表自联结 课程不同就是课程号不同， 成绩相同，
2.同一个学生，所以s_id相同
只能说是破题… 重复问题没法解决

SELECT a.*,b.c_id,b.s_score
from score a 
join score b on a.c_id !=b.c_id 
                        and a.s_id = b.s_id
                        and a.s_score = b.s_score

在这里插入图片描述
题目的要求就是找出每门课的前2名同学—多看，比较综合，解决前几名排序的问题

题目30：找出每门课的前2名同学(值得学习)

解法：成绩表自联结找出每个同学每门课的排名 然后根据having count 找出每门课要求的前几名的同学
求排名的方法：两表自连或者子查询，比较然后通过having count 确定排名
1.存在并列排名，所以表连接条件中成绩比较不能带等号 这样的话，就必须使用左查询了，不然成绩相等的直接去掉了
2.因为是比每门课的成绩 所以按课程号和学生号分组
3.因为存在count = 0的情况，可是排名是不可能为0的，所以需要使用case when 语句对0特殊处理

select a.c_id,a.s_id
        ,(casewhenCOUNT(b.s_id)>0thenCOUNT(b.s_id)else1end) 排名
from score a 
leftjoin score b on a.s_id != b.s_id
                        and a.c_id = b.c_id
                        and a.s_score < b.s_score
GROUPBY c_id,s_id
HAVINGCOUNT(b.s_id)<=2ORDERBY a.c_id,排名

在这里插入图片描述

题目31：统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

思路：
不考虑重修，统计每门课程的选修人数，显然按课程进行分组，count人数 先按人数降序，再按课程号升序

SELECT c_id,COUNT(s_id) 选修人数
from score a 
GROUPBY c_id
orderby2desc,1asc

在这里插入图片描述

题目32：检索至少选修两门课程的学生学号

思路： 成绩表 按学生学号分组，然后统计课程数大于等于2即可

SELECT
    s_id,count(*)FROM
    Score 
GROUPBY
    s_id 
HAVINGcount(*)>=2;

在这里插入图片描述

题目33：查询选修了全部课程的学生信息

思路：因为不考虑重修，所以直接选修课程个数等于最大课程个数即可

select a.*from student a 
WHERE a.s_id in(select b.s_id 
            from score b
            GROUPBY b.s_id
            HAVINGcount(b.c_id)=(selectcount(c_id)from course ))

题目34：查询各学生的年龄：按照出生日期来算，当前月日 >出生年月的月日则，年龄减1

思路：显然这是 控制流语句 条件就是 当前月 日 > 出生年月的月日 case when
case when 当前月 日 > 出生年月的月日 then year(出生)- year(当前) -1 else then year(出生)- year(当前) end

select 
        a.s_id
        ,a.s_name
        ,a.s_birthday
        ,(casewhen DATE_FORMAT(CURRENT_DATE,'%m%d')> DATE_FORMAT(a.s_birthday,'%m%d')thenYEAR(CURRENT_DATE)-YEAR(a.s_birthday)-1elseYEAR(CURRENT_DATE)-YEAR(a.s_birthday)end)from student a

在这里插入图片描述

题目35：查询本周过生日的学生

   解法1  ：查询本周过生日的学生   学生今年过生日的周数 计算需要用到字符串拼接  因为他是今年   月份是生日月份

select     a.*from student a 
WHERE  WEEK(CURRENT_DATE,1)= 
                WEEK( concat(YEAR(CURRENT_DATE),DATE_FORMAT(a.s_birthday,'%m%d')),1)

查询下周过生日的学生

思路：+1

select     a.*from student a 
WHERE  WEEK(CURRENT_DATE,1)+1= 
                WEEK( concat(YEAR(CURRENT_DATE),DATE_FORMAT(a.s_birthday,'%m%d')),1)
标签： mysql 数据库
本文转载自: https://blog.csdn.net/qq_42265608/article/details/131180839
版权归原作者 叫我莫言鸭 所有， 如有侵权，请联系我们删除。
登录后发布评论

“Mysql经典例题练习与总结”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

网络安全法-网络运行安全

使用selenium/drissionpage时如何阻止chrome自动跳转http到https

docker desktop 里部署的Open WebUI 管理员密码忘记了的处理方法

在ubuntu20.04中搭建onsite比赛运行环境

利用开源的低代码表单设计器FcDesigner高效管理和渲染复杂表单结构

Kafka学习笔记

【前端】浏览器输入url到页面呈现发生了什么？

文章导航