2020-08-11

最新推荐文章于 2021-11-22 14:54:53 发布

weixin_46062329

最新推荐文章于 2021-11-22 14:54:53 发布

阅读量108

点赞数

文章标签： mysql

原文链接：https://blog.csdn.net/fashion2014/article/details/78826299/

版权

SQL练习题

mysql> select * from student;
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 01   | 赵雷   | 1990-01-01 | 男    |
| 02   | 钱电   | 1990-12-21 | 男    |
| 03   | 孙风   | 1990-05-20 | 男    |
| 04   | 李云   | 1990-08-06 | 男    |
| 05   | 周梅   | 1991-12-01 | 女    |
| 06   | 吴兰   | 1992-03-01 | 女    |
| 07   | 郑竹   | 1989-07-01 | 女    |
| 08   | 王菊   | 1990-01-20 | 女    |
+------+--------+------------+-------+
8 rows in set (0.00 sec)


mysql> select * from score;
+------+------+---------+
| s_id | c_id | s_score |
+------+------+---------+
| 01   | 01   |      80 |
| 01   | 02   |      90 |
| 01   | 03   |      99 |
| 02   | 01   |      70 |
| 02   | 02   |      60 |
| 02   | 03   |      80 |
| 03   | 01   |      80 |
| 03   | 02   |      80 |
| 03   | 03   |      80 |
| 04   | 01   |      50 |
| 04   | 02   |      30 |
| 04   | 03   |      20 |
| 05   | 01   |      76 |
| 05   | 02   |      87 |
| 06   | 01   |      31 |
| 06   | 03   |      34 |
| 07   | 02   |      89 |
| 07   | 03   |      98 |
+------+------+---------+
18 rows in set (0.00 sec)


mysql> select * from course;
+------+--------+------+
| c_id | c_name | t_id |
+------+--------+------+
| 01   | 语文   | 02   |
| 02   | 数学   | 01   |
| 03   | 英语   | 03   |
+------+--------+------+
3 rows in set (0.00 sec)


mysql> select * from teacher;
+------+--------+
| t_id | t_name |
+------+--------+
| 01   | 张三   |
| 02   | 李四   |
| 03   | 王五   |
+------+--------+
3 rows in set (0.00 sec)



-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
方法一：先将score和score按照sc.s_id连接起来，再寻找满足c_id = '01' 和 c_id = '02'的s_id,最后在student中寻找对应id的信息。

select st.* 
from student st where st.s_id in 
(select sc.s_id from score sc inner join score sc2 on sc.s_id = sc2.s_id where sc.c_id = '01' and sc2.c_id = '02');
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 01   | 赵雷   | 1990-01-01 | 男    |
| 02   | 钱电   | 1990-12-21 | 男    |
| 03   | 孙风   | 1990-05-20 | 男    |
| 04   | 李云   | 1990-08-06 | 男    |
| 05   | 周梅   | 1991-12-01 | 女    |
+------+--------+------------+-------+

方法二：直接从student,score,course组成的表中选择。注意这三个表不加条件组成一个8x18x3=432行的表。

 select st.* from student st,score sc,score sc2
 where st.s_id = sc.s_id and sc.s_id = sc2.s_id and sc.c_id = '01' and sc2.c_id = '02';

方法三：
select st.* from student st 
inner join score sc on sc.s_id = st.s_id
inner join course c on c.c_id=sc.c_id and c.c_id="01"
where st.s_id in (
select st2.s_id from student st2 
inner join score sc2 on sc2.s_id = st2.s_id
inner join course c2 on c2.c_id=sc2.c_id and c2.c_id="02"
)

方法四：
SELECT st.*
FROM student st
INNER JOIN score sc ON sc.`s_id`=st.`s_id`
GROUP BY st.`s_id`
HAVING SUM(IF(sc.`c_id`="01" OR sc.`c_id`="02" ,1,0))>1

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

select * from student where s_id in 
        (select s_id from score where c_id = '01') 
        and s_id not in (select s_id from score where c_id = '02');

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

方法一：
 select * from student where s_id not in
         (select s_id from score group by s_id having count(c_id) = 3);
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 05   | 周梅   | 1991-12-01 | 女    |
| 06   | 吴兰   | 1992-03-01 | 女    |
| 07   | 郑竹   | 1989-07-01 | 女    |
| 08   | 王菊   | 1990-01-20 | 女    |
+------+--------+------------+-------+

方法二：
select st.* from student st 
    left join score sc on st.s_id = sc.s_id 
    group by st.s_id having count(sc.c_id) < 3; #先按照st.s_id分组后才能求和，不分组会对所有                                    
                                                   的c_id求和

这里的课程总数量可以写为： (select count(c_id) from Course)

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

方法一：
select st.* from student st 
    left join score sc on st.s_id = sc.s_id where sc.c_id in 
           (select c_id from score where s_id = '01') 
            and st.s_id != '01' group by st.s_id;

+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 02   | 钱电   | 1990-12-21 | 男    |
| 03   | 孙风   | 1990-05-20 | 男    |
| 04   | 李云   | 1990-08-06 | 男    |
| 05   | 周梅   | 1991-12-01 | 女    |
| 06   | 吴兰   | 1992-03-01 | 女    |
| 07   | 郑竹   | 1989-07-01 | 女    |
+------+--------+------------+-------+
方法二：
select distinct st.* from student st 
    left join score sc on st.s_id = sc.s_id where sc.c_id in 
           (select c_id from score where s_id = '01') 
            and st.s_id != '01';

-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息 
方法一：利用group_concat函数得到01同学学习的所有课程，再和所有同学学习的课程比较
select  st.* from student st 
    left join score sc on sc.s_id=st.s_id
    group by st.s_id
    having group_concat(sc.c_id) = (
        select  group_concat(sc2.c_id) from student st2
            left join score sc2 on sc2.s_id=st2.s_id
                 where st2.s_id ='01')
提示：select st.s_id,group_concat(sc.c_id) from student st left join score sc on st.s_id = sc.s_id group by st.s_id;  返回所有同学学习的课程，用，分隔

+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 02   | 钱电   | 1990-12-21 | 男    |
| 03   | 孙风   | 1990-05-20 | 男    |
| 04   | 李云   | 1990-08-06 | 男    |
+------+--------+------------+-------+

方法二：

select * from student where s_id in 
	(select s_id from score group by s_id having count(c_id) =     #找到和01同学学习课程数相同的同学id
		(select count(c_id) from score where s_id = '01') and s_id not in  #在课程数相同的前提下，要保证01没学的课，其他同学也没学过
			(select s_id from score where c_id not in 
				(select c_id from score where s_id = '01'))) and s_id != '01';

-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名
分析：
第一步：学过张三的课的信息
select * from score sc 
left join course c on c.c_id = sc.c_id 
left join teacher t on t.t_id = c.t_id 
where t.t_name = '张三';   #注意这里是left join，生成的表以左表为准后面不满足的信息会用NULL填充，所以需要用where来选择t_name = '张三'的行

或者写为：
select * from score sc 
inner join course c on c.c_id = sc.c_id 
inner join teacher t on t.t_id = c.t_id and t.t_name = '张三';  #采用inner join代替left join，后用and 或 where选择t_name='张三'的行

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

select st.s_id,st.s_name,ROUND(AVG(sc.s_score),2) as svg_score from student st 
    left join score sc on st.s_id = sc.s_id 
        where sc.s_score < 60 or sc.s_score is NULL 
            group by st.s_id 
                having count(sc.s_score) >= 2; #这里也可以用count(1)

+------+--------+-----------+
| s_id | s_name | svg_score |
+------+--------+-----------+
| 04   | 李云   |     33.33 |
| 06   | 吴兰   |     32.50 |
+------+--------+-----------+


count()是一个聚合函数，对于返回的结果集，会逐行判断，若返回的不是 NULL，就会加 1，否则不加。
count(*)、count(主键 id)和count(1)都表示返回满足条件的结果集的总行数；而count(字段），
则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。
按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)

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

select st.* from student st where st.s_id in 
        (select sc.s_id from score sc inner join course c on sc.c_id = c.c_id and c.c_id = '01' 
                where sc.s_score < 60 
                        order by sc.s_score desc);
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 04   | 李云   | 1990-08-06 | 男    |
| 06   | 吴兰   | 1992-03-01 | 女    |
+------+--------+------------+-------+

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

方法一：
select st.s_id,st.s_name,sc.s_score as '语文',sc1.s_score as '数学',sc2.s_score '英语',ROUND(AVG(sc3.s_score),2) as '平均分' from student st 
    left join score sc on st.s_id = sc.s_id and sc.c_id = '01' 
    left join score sc1 on st.s_id=sc1.s_id and sc1.c_id = '02' 
    left join score sc2 on st.s_id=sc2.s_id and sc2.c_id = '03'
    left join score sc3 on st.s_id = sc3.s_id group by st.s_id  #group by 表示按照id分组求平均值，否则会对所有的数求平均
    order by AVG(sc3.s_score) desc;
+------+--------+------+------+------+--------+
| s_id | s_name | 语文 | 数学 | 英语 | 平均分 |
+------+--------+------+------+------+--------+
| 07   | 郑竹   | NULL |   89 |   98 |  93.50 |
| 01   | 赵雷   |   80 |   90 |   99 |  89.67 |
| 05   | 周梅   |   76 |   87 | NULL |  81.50 |
| 03   | 孙风   |   80 |   80 |   80 |  80.00 |
| 02   | 钱电   |   70 |   60 |   80 |  70.00 |
| 04   | 李云   |   50 |   30 |   20 |  33.33 |
| 06   | 吴兰   |   31 | NULL |   34 |  32.50 |
| 08   | 王菊   | NULL | NULL | NULL |   NULL |
+------+--------+------+------+------+--------+

方法二：  利用case when then else end

select st.s_id,st.s_name,
    (case when sc.c_id = '01' then sc.s_score else NULL end) as '语文', #记录每个id对应的成绩，没有的记为NULL
    (case when sc.c_id = '02' then sc.s_score else NULL end) as '数学',
    (case when sc.c_id = '03' then sc.s_score else NULL end) as '英语',ROUND(AVG(sc.s_score),2) as '平均分' 
        from student st left join score sc on st.s_id = sc.s_id group by st.s_id 
            order by SUM(sc.s_score) desc; #用sum来排序，因为有的人只有部分成绩

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

select 
c.c_id,
c.c_name,
max(sc.s_score) as 最高分,
min(sc.s_score) '最低分',
round(avg(case when sc.s_score is not NULL then sc.s_score else 0 end),2) as '平均分',
round(100*sum(case when sc.s_score >= 60 then 1 else 0 end)/count(sc.s_id),2) as '及格率(%)', 
round(100*sum(case when sc.s_score>= 70 and sc.s_score < 80 then 1 else 0 end)/count(sc.s_id),2) as '中等率(%)', 
round(100*sum(case when sc.s_score >= 80 and sc.s_score < 90 then 1 else 0 end)/count(sc.s_id),2) as '优良率(%)',round(100*sum(case when sc.s_score >= 90 then 1 else 0 end)/count(sc.s_id),2) as '优秀率(%)' 
from course c left join score sc on c.c_id = sc.c_id 
group by c.c_id;    #***按照c_id 分组，计算最高最低以及平均分等***


+------+--------+--------+--------+--------+-----------+-----------+-----------+-----------+
| c_id | c_name | 最高分 |  最低分 |  平均分 | 及格率(%)| 中等率(%) |  优良率(%) | 优秀率(%) |
+------+--------+--------+--------+--------+-----------+-----------+-----------+-----------+
| 01   | 语文   |     80 |     31 |  64.50 |     66.67 |     33.33 |     33.33 |      0.00 |
| 02   | 数学   |     90 |     30 |  72.67 |     83.33 |      0.00 |     50.00 |     16.67 |
| 03   | 英语   |     99 |     20 |  68.50 |     66.67 |      0.00 |     33.33 |     33.33 |
+------+--------+--------+--------+--------+-----------+-----------+-----------+-----------+

-- 19、按各科成绩进行排序，并显示排名
方法一：
select c1.s_id,c1.c_id,c1.c_name,c1.s_score,@i:=@i+1 from 
    (select sc.*,c.c_name from score sc left join course c on sc.c_id = c.c_id where c.c_id = '01'order by sc.s_score desc) c1,(select @i:= 0) a
union all
select c2.s_id,c2.c_id,c2.c_name,c2.s_score,@i2:=@i2+1 from 
    (select sc.*,c.c_name from score sc left join course c on sc.c_id = c.c_id where c.c_id = '02'order by sc.s_score desc) c2, (select @i2:=0) a2
union all
select c3.s_id,c3.c_id,c3.c_name,c3.s_score,@i3:=@i3+1 from 
    (select sc.*,c.c_name from score sc left join course c on sc.c_id = c.c_id where c.c_id = '03'order by sc.s_score desc) c3, (select @i3:=0) a3;
+------+------+--------+---------+----------+
| s_id | c_id | c_name | s_score | @i:=@i+1 |
+------+------+--------+---------+----------+
| 01   | 01   | 语文   |      80 |        1 |
| 03   | 01   | 语文   |      80 |        2 |
| 05   | 01   | 语文   |      76 |        3 |
| 02   | 01   | 语文   |      70 |        4 |
| 04   | 01   | 语文   |      50 |        5 |
| 06   | 01   | 语文   |      31 |        6 |
| 01   | 02   | 数学   |      90 |        1 |
| 07   | 02   | 数学   |      89 |        2 |
| 05   | 02   | 数学   |      87 |        3 |
| 03   | 02   | 数学   |      80 |        4 |
| 02   | 02   | 数学   |      60 |        5 |
| 04   | 02   | 数学   |      30 |        6 |
| 01   | 03   | 英语   |      99 |        1 |
| 07   | 03   | 英语   |      98 |        2 |
| 02   | 03   | 英语   |      80 |        3 |
| 03   | 03   | 英语   |      80 |        4 |
| 06   | 03   | 英语   |      34 |        5 |
| 04   | 03   | 英语   |      20 |        6 |
+------+------+--------+---------+----------+


分析：设置了用户变量@i显示排名，@i:=@i+1表示逐行加一，因为i设置后要设置初始值，所以生成一个i的表（select @i:=0）这样每次排名从1开始

方法二：设置两个用户变量，@pre_c_id 和 @rank, @pre_c_id表示当前进行排名的课程，用于对排名rank进行重新赋值为1开始

set @pre_c_id := '01'; #设置用户变量，并赋初值
set @rank := 0;
select tb2.s_id, tb2.c_id,tb2.c_name,tb2.s_score,tb2.排名 from 
    (select *,
        (case when tb1.c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名, 
        (case when @pre_c_id = tb1.c_id then @pre_c_id else @pre_c_id:=tb1.c_id end) as pre_c_id from 
            (select sc.*,c.c_name from score sc left join course c on sc.c_id = c.c_id 
                order by c_id,s_score desc) tb1)
                    tb2;

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


set @rank:=0; #设置用户变量并赋初值

select tb.s_id,tb.s_name,tb.总分,(@rank:=@rank+1) as 排名 from 
    (select st.*,sum(sc.s_score) as 总分  
        from student st left join score sc on st.s_id = sc.s_id 
            group by st.s_id 
                order by sum(sc.s_score) desc) 
                    tb;
+------+--------+------+------+
| s_id | s_name | 总分 | 排名 |
+------+--------+------+------+
| 01   | 赵雷   |  269 |    1 |
| 03   | 孙风   |  240 |    2 |
| 02   | 钱电   |  210 |    3 |
| 07   | 郑竹   |  187 |    4 |
| 05   | 周梅   |  163 |    5 |
| 04   | 李云   |  100 |    6 |
| 06   | 吴兰   |   65 |    7 |
| 08   | 王菊   | NULL |    8 |
+------+--------+------+------+

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

select c.c_id,c.c_name,t.t_id,t.t_name, round(avg(sc.s_score),2) as 平均分 from course c 
    left join teacher t on t.t_id = c.t_id 
    left join score sc on c.c_id = sc.c_id 
        group by c.c_id 
            order by 平均分 desc;

+------+--------+------+--------+--------+
| c_id | c_name | t_id | t_name | 平均分 |
+------+--------+------+--------+--------+
| 02   | 数学   | 01   | 张三   |  72.67 |
| 03   | 英语   | 03   | 王五   |  68.50 |
| 01   | 语文   | 02   | 李四   |  64.50 |
+------+--------+------+--------+--------+

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

方法一：

select tb2.s_id,st.s_name,tb2.c_id,tb2.s_score,tb2.排名 from 
    (select tb1.*,
        (case when tb1.c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,
        (case when @pre_c_id = tb1.c_id then @pre_c_id else @pre_c_id := tb1.c_id end) as pre_c_id from 
            (select * from score order by c_id,s_score desc) tb1)tb2 
                left join student st on tb2.s_id = st.s_id 
                    where 排名=2 or 排名 = 3;

+------+--------+------+---------+------+
| s_id | s_name | c_id | s_score | 排名 |
+------+--------+------+---------+------+
| 03   | 孙风   | 01   |      80 |    2 |
| 05   | 周梅   | 01   |      76 |    3 |
| 07   | 郑竹   | 02   |      89 |    2 |
| 05   | 周梅   | 02   |      87 |    3 |
| 07   | 郑竹   | 03   |      98 |    2 |
| 02   | 钱电   | 03   |      80 |    3 |
+------+--------+------+---------+------+


方法二：利用LIMIT函数选择排名第二到第三的，limit后又两个整数参数，第一个表示行偏移量，第二个表示返回记录行的最大数量

select a.* from (
    select st.*,c.c_id,c.c_name,sc.s_score from student st
        left join score sc on sc.s_id=st.s_id
        inner join course c on c.c_id =sc.c_id and c.c_id="01"
            order by sc.s_score desc LIMIT 1,2 ) a
union all
select b.* from (
    select st.*,c.c_id,c.c_name,sc.s_score from student st
        left join score sc on sc.s_id=st.s_id
        inner join course c on c.c_id =sc.c_id and c.c_id="02"
            order by sc.s_score desc LIMIT 1,2) b
union all
select c.* from (
    select st.*,c.c_id,c.c_name,sc.s_score from student st
        left join score sc on sc.s_id=st.s_id
        inner join course c on c.c_id =sc.c_id and c.c_id="03"
            order by sc.s_score desc LIMIT 1,2) c

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

select 
    c.c_id,c.c_name,
    sum(case when sc.s_score >= 85 then 1 else 0 end) as '[85-100]',
    concat(round(100*sum(case when sc.s_score >= 85 then 1 else 0 end)/count(*),2), '%') as 百分比,
    sum(case when sc.s_score >= 70 and  sc.s_score < 85 then 1 else 0 end) as '[70,85)',
    concat(round(100*sum(case when sc.s_score>=70 and sc.s_score<85 then 1 else 0 end)/count(*),2), '%') as 百分比,
    sum(case when sc.s_score >= 60 and sc.s_score < 70 then 1 else 0 end) as '[60,70)',
    concat(round(100*sum(case when sc.s_score >= 60 and sc.s_score < 70 then 1 else 0 end)/count(*),2),'%') as 百分比,
    sum(case when sc.s_score < 60 then 1 else 0 end) as '(0,60)',
    concat(round(100*sum(case when sc.s_score < 60 then 1 else 0 end)/count(*),2),'%') as 百分比 
        from course c left join score sc on sc.c_id = c.c_id group by c.c_id;

+------+--------+----------+--------+---------+--------+---------+--------+--------+--------+
| c_id | c_name | [85-100] | 百分比 | [70,85) | 百分比 | [60,70) | 百分比 | (0,60) | 百分比 |
+------+--------+----------+--------+---------+--------+---------+--------+--------+--------+
| 01   | 语文   |        0 | 0.00%  |       4 | 66.67% |       0 | 0.00%  |      2 | 33.33% |
| 02   | 数学   |        3 | 50.00% |       1 | 16.67% |       1 | 16.67% |      1 | 16.67% |
| 03   | 英语   |        2 | 33.33% |       2 | 33.33% |       0 | 0.00%  |      2 | 33.33% |
+------+--------+----------+--------+---------+--------+---------+--------+--------+--------+

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

set @rank:=0;

select tb.s_id,tb.s_name,tb.平均分,@rank:=@rank + 1 as 排名 from 
    (select st.s_id,st.s_name,avg(sc.s_score) as 平均分 from student st left join score sc on st.s_id = sc.s_id 
        group by sc.s_id order by 平均分 desc) tb;
+------+--------+---------+------+
| s_id | s_name | 平均分  | 排名 |
+------+--------+---------+------+
| 07   | 郑竹   | 93.5000 |    1 |
| 01   | 赵雷   | 89.6667 |    2 |
| 05   | 周梅   | 81.5000 |    3 |
| 03   | 孙风   | 80.0000 |    4 |
| 02   | 钱电   | 70.0000 |    5 |
| 04   | 李云   | 33.3333 |    6 |
| 06   | 吴兰   | 32.5000 |    7 |
| 08   | 王菊   |    NULL |    8 |
+------+--------+---------+------+

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

set @pre_c_id:= '01';
set @rank:=0;

select tb2.s_id,st.s_name,tb2.c_id,tb2.c_name,tb2.s_score,tb2.排名 from 
    (select *,
    (case when tb1.c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,
    (case when @pre_c_id = tb1.c_id then @pre_c_id else @pre_c_id:=tb1.c_id end) as pre_c_id from 
        (select sc.s_id,c.c_id,c.c_name,sc.s_score from course c left join score sc on sc.c_id = c.c_id 
            order by sc.c_id,sc.s_score desc) tb1
                ) tb2 left join student st on st.s_id = tb2.s_id where 排名<4;


+------+--------+------+--------+---------+------+
| s_id | s_name | c_id | c_name | s_score | 排名 |
+------+--------+------+--------+---------+------+
| 01   | 赵雷   | 01   | 语文   |      80 |    1 |
| 03   | 孙风   | 01   | 语文   |      80 |    2 |
| 05   | 周梅   | 01   | 语文   |      76 |    3 |
| 01   | 赵雷   | 02   | 数学   |      90 |    1 |
| 07   | 郑竹   | 02   | 数学   |      89 |    2 |
| 05   | 周梅   | 02   | 数学   |      87 |    3 |
| 01   | 赵雷   | 03   | 英语   |      99 |    1 |
| 07   | 郑竹   | 03   | 英语   |      98 |    2 |
| 02   | 钱电   | 03   | 英语   |      80 |    3 |
+------+--------+------+--------+---------+------+

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

select c_id,count(*) as 选修人数 from score group by c_id;

+------+----------+
| c_id | 选修人数 |
+------+----------+
| 01   |        6 |
| 02   |        6 |
| 03   |        6 |
+------+----------+

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

方法一：

select tb.s_id, tb.s_name from 
    (select st.s_id,st.s_name,count(*) as count from score sc left join student st on sc.s_id = st.s_id 
    group by sc.s_id) tb 
        where tb.count = 2;
+------+--------+
| s_id | s_name |
+------+--------+
| 05   | 周梅   |
| 06   | 吴兰   |
| 07   | 郑竹   |
+------+--------+

方法二：
select st.s_id,st.s_name from score sc left join student st on sc.s_id = st.s_id 
    group by sc.s_id having count(*) = 2;

-- 28、查询男生、女生人数

select sum(case when s_sex = '男' then 1 else 0 end) as 男生人数,
    sum(case when s_sex = '女' then 1 else 0 end) as 女生人数 from student;

+----------+----------+
| 男生人数 | 女生人数 |
+----------+----------+
|        4 |        4 |
+----------+----------+

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

select * from student where s_name like '%风%';

+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 03   | 孙风   | 1990-05-20 | 男    |
+------+--------+------------+-------+

-- 30、查询同名同性学生名单，并统计同名人数


select *,count(*) from student st group by s_name,s_sex having count(*) > 1;

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

select * from student where s_birth like '1990%';
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 01   | 赵雷   | 1990-01-01 | 男    |
| 02   | 钱电   | 1990-12-21 | 男    |
| 03   | 孙风   | 1990-05-20 | 男    |
| 04   | 李云   | 1990-08-06 | 男    |
| 08   | 王菊   | 1990-01-20 | 女    |
+------+--------+------------+-------+

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

select c_id,round(avg(s_score), 2) as 平均成绩 from score group by c_id order by 平均成绩 desc,c_id;

+------+----------+
| c_id | 平均成绩 |
+------+----------+
| 02   |    72.67 |
| 03   |    68.50 |
| 01   |    64.50 |
+------+----------+

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

select st.s_id,st.s_name,round(avg(sc.s_score),2) as 平均成绩 
    from student st left join score sc on st.s_id = sc.s_id group by st.s_id having 平均成绩 > 85;

+------+--------+----------+
| s_id | s_name | 平均成绩 |
+------+--------+----------+
| 01   | 赵雷   |    89.67 |
| 07   | 郑竹   |    93.50 |
+------+--------+----------+

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

方法一：
select st.s_name,tb.s_score from 
    (select sc.*,c.c_name from score sc left join course c on sc.c_id = c.c_id where c.c_name = '数学') tb 
        left join student st on tb.s_id = st.s_id where tb.s_score < 60;

+--------+---------+
| s_name | s_score |
+--------+---------+
| 李云   |      30 |
+--------+---------+

方法二：
 select st.s_name,sc.s_score from score sc 
    left join student st on sc.s_id = st.s_id 
        where sc.c_id = (select c_id from course where c_name = '数学') and sc.s_score < 60;

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

select st.s_id,st.s_name,
sum(case when sc.c_id = '01' then sc.s_score else null end) as 语文,
sum(case when sc.c_id = '02' then sc.s_score else null end) as 数学,
sum(case when sc.c_id = '03' then sc.s_score else null end) as 英语 from student st 
    left join score sc on st.s_id = sc.s_id group by st.s_id;

+------+--------+------+------+------+
| s_id | s_name | 语文 | 数学 | 英语 |
+------+--------+------+------+------+
| 01   | 赵雷   |   80 |   90 |   99 |
| 02   | 钱电   |   70 |   60 |   80 |
| 03   | 孙风   |   80 |   80 |   80 |
| 04   | 李云   |   50 |   30 |   20 |
| 05   | 周梅   |   76 |   87 | NULL |
| 06   | 吴兰   |   31 | NULL |   34 |
| 07   | 郑竹   | NULL |   89 |   98 |
| 08   | 王菊   | NULL | NULL | NULL |
+------+--------+------+------+------+

***注意：在这里case when 判断c_id时会对相应的s_score更改（满足是s_score否则是null），所以后面的值会变成null。解决方法：只需利用sum函数，不用sum得到的结果是：
+------+--------+------+------+------+
| s_id | s_name | 语文 | 数学 | 英语 |
+------+--------+------+------+------+
| 01   | 赵雷   |   80 | NULL | NULL |
| 02   | 钱电   |   70 | NULL | NULL |
| 03   | 孙风   |   80 | NULL | NULL |
| 04   | 李云   |   50 | NULL | NULL |
| 05   | 周梅   |   76 | NULL | NULL |
| 06   | 吴兰   |   31 | NULL | NULL |
| 07   | 郑竹   | NULL |   89 | NULL |
| 08   | 王菊   | NULL | NULL | NULL |
+------+--------+------+------+------+

后面的成绩被覆盖了

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

方法一：
select st.s_id,st.s_name,
sum(case when sc.c_id = '01' then sc.s_score else null end) as 语文,
sum(case when sc.c_id = '02' then sc.s_score else null end) as 数学,
sum(case when sc.c_id = '03' then sc.s_score else null end) as 英语 from student st 
    left join score sc on st.s_id = sc.s_id group by st.s_id 
        having 语文>70 or 数学>70 or 英语>70;

+------+--------+------+------+------+
| s_id | s_name | 语文 | 数学 | 英语 |
+------+--------+------+------+------+
| 01   | 赵雷   |   80 |   90 |   99 |
| 02   | 钱电   |   70 |   60 |   80 |
| 03   | 孙风   |   80 |   80 |   80 |
| 05   | 周梅   |   76 |   87 | NULL |
| 07   | 郑竹   | NULL |   89 |   98 |
+------+--------+------+------+------+


方法二：
select a.s_name,b.c_name,c.s_score from course b 
    left join score c on b.c_id = c.c_id
    left join student a on a.s_id=c.s_id where c.s_score>=70;

+--------+--------+---------+
| s_name | c_name | s_score |
+--------+--------+---------+
| 赵雷   | 语文   |      80 |
| 赵雷   | 数学   |      90 |
| 赵雷   | 英语   |      99 |
| 钱电   | 语文   |      70 |
| 钱电   | 英语   |      80 |
| 孙风   | 语文   |      80 |
| 孙风   | 数学   |      80 |
| 孙风   | 英语   |      80 |
| 周梅   | 语文   |      76 |
| 周梅   | 数学   |      87 |
| 郑竹   | 数学   |      89 |
| 郑竹   | 英语   |      98 |
+--------+--------+---------+

-- 37、查询不及格的课程

select sc.s_id,sc.c_id,c.c_name,sc.s_score from score sc 
    left join course c on sc.c_id = c.c_id where sc.s_score < 60;

+------+------+--------+---------+
| s_id | c_id | c_name | s_score |
+------+------+--------+---------+
| 04   | 01   | 语文   |      50 |
| 06   | 01   | 语文   |      31 |
| 04   | 02   | 数学   |      30 |
| 04   | 03   | 英语   |      20 |
| 06   | 03   | 英语   |      34 |
+------+------+--------+---------+

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

select sc.s_id,st.s_name from score sc left join student st on sc.s_id = st.s_id  where c_id = '01' and s_score >= 80;

-- 39、求每门课程的学生人数 

select c_id,count(*) as 人数 from score group by c_id;

+------+------+
| c_id | 人数 |
+------+------+
| 01   |    6 |
| 02   |    6 |
| 03   |    6 |
+------+------+

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

方法一：
select st.*,sc.c_id,max(sc.s_score) as 最高成绩 from student st 
    left join score sc on st.s_id = sc.s_id 
        group by sc.c_id 
        having sc.c_id = (select c.c_id from teacher t left join course c on c.t_id = t.t_id where t.t_name = '张三');
+------+--------+------------+-------+------+----------+
| s_id | s_name | s_birth    | s_sex | c_id | 最高成绩 |
+------+--------+------------+-------+------+----------+
| 01   | 赵雷   | 1990-01-01 | 男    | 02   |       90 |
+------+--------+------------+-------+------+----------+


方法二：
select st.*,sc.c_id,max(sc.s_score) as 最高成绩 from student st 
    left join score sc on st.s_id = sc.s_id 
    group by sc.c_id 
    having sc.c_id = (select c_id from course where t_id = (select t_id from teacher where t_name = '张三'));



********注意：
GROUP BY的规定：

1、GROUP BY 后面可以包含多个列，这就是嵌套。

2、如果GROUP BY进行了嵌套，数据将在最后一个分组上进行汇总。

3、GROUP BY子句中列出来的每个列必须是检索列或有效的表达式（但不能是聚集函数），如果在SELECT中使用了表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。

4、除了聚集语句外，SELECT语句中的每一个列都必须在GROUP BY子句中给出。

5、如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多个NULL，它们将作为一个分组返回。

6、GROUP BY子句必须在WHERE 子句之后，ORDER BY 子句之前。***********************

*******where 和 group by*********
我们知道WHERE 子句用于过滤结果，但是对于分组的过滤WHERE子句不行。

因为WHERE子句，是针对行的过滤。要对分组结果进行过滤，必须使用HAVING子句，HAVING子句能针对分组的结果进行过滤。

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

select distinct a.s_id,a.c_id,a.s_score 
    from score a left join score b on a.c_id != b.c_id and a.s_score = b.s_score;
+------+------+---------+
| s_id | c_id | s_score |
+------+------+---------+
| 02   | 03   |      80 |
| 03   | 02   |      80 |
| 03   | 03   |      80 |
| 01   | 01   |      80 |
| 03   | 01   |      80 |
| 01   | 02   |      90 |
| 01   | 03   |      99 |
| 02   | 01   |      70 |
| 02   | 02   |      60 |
| 04   | 01   |      50 |
| 04   | 02   |      30 |
| 04   | 03   |      20 |
| 05   | 01   |      76 |
| 05   | 02   |      87 |
| 06   | 01   |      31 |
| 06   | 03   |      34 |
| 07   | 02   |      89 |
| 07   | 03   |      98 |
+------+------+---------+

-- 42、查询每门课程成绩最好的前两名

方法一：
select tb2.s_id,st.s_name,tb2.c_id,tb2.s_score,tb2.排名 
    from (select *,
        (case when tb1.c_id = @pre_c_id then @rank:=@rank+1 else @rank:= 1 end) as 排名, 
        (case when @pre_c_id = tb1.c_id then @pre_c_id else @pre_c_id := tb1.c_id end) as pre_c_id 
            from (select * from score order by c_id,s_score desc) tb1
                ) tb2 left join student st on tb2.s_id = st.s_id where 排名<3;
+------+--------+------+---------+------+
| s_id | s_name | c_id | s_score | 排名 |
+------+--------+------+---------+------+
| 01   | 赵雷   | 01   |      80 |    1 |
| 03   | 孙风   | 01   |      80 |    2 |
| 01   | 赵雷   | 02   |      90 |    1 |
| 07   | 郑竹   | 02   |      89 |    2 |
| 01   | 赵雷   | 03   |      99 |    1 |
| 07   | 郑竹   | 03   |      98 |    2 |
+------+--------+------+---------+------+

方法二：

select tb.s_id,st.s_name,tb.c_id,tb.s_score from (
    (select * from score where c_id = '01' order by s_score desc limit 0,2) 
    union all 
    (select * from score where c_id = '02' order by s_score desc limit 0,2) 
    union all 
    (select * from score sc where c_id = '03' order by s_score desc limit 0,2)
        )tb left join student st on tb.s_id = st.s_id;

+------+--------+------+---------+
| s_id | s_name | c_id | s_score |
+------+--------+------+---------+
| 01   | 赵雷   | 01   |      80 |
| 03   | 孙风   | 01   |      80 |
| 01   | 赵雷   | 02   |      90 |
| 07   | 郑竹   | 02   |      89 |
| 01   | 赵雷   | 03   |      99 |
| 07   | 郑竹   | 03   |      98 |
+------+--------+------+---------+

方法三：
select a.s_id,a.c_id,a.s_score from score a
		where (select COUNT(1) from score b where b.c_id=a.c_id and b.s_score>=a.s_score)<=2 ORDER BY a.c_id

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

select c_id,count(1) as 人数 from score group by c_id having count(1)>5 order by 人数 desc,c_id;

+------+------+
| c_id | 人数 |
+------+------+
| 01   |    6 |
| 02   |    6 |
| 03   |    6 |
+------+------+

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

select s_id,count(*) as 选修课程数 from score group by s_id having count(*)>=2;
+------+------------+
| s_id | 选修课程数 |
+------+------------+
| 01   |          3 |
| 02   |          3 |
| 03   |          3 |
| 04   |          3 |
| 05   |          2 |
| 06   |          2 |
| 07   |          2 |
+------+------------+

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

select * from student where s_id in 
    (select s_id from score group by s_id having count(*)=3);
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 01   | 赵雷   | 1990-01-01 | 男    |
| 02   | 钱电   | 1990-12-21 | 男    |
| 03   | 孙风   | 1990-05-20 | 男    |
| 04   | 李云   | 1990-08-06 | 男    |
+------+--------+------------+-------+

--46、查询各学生的年龄

 select s_id,s_name,
    (date_format(now(),'%Y') - date_format(s_birth, '%Y') + (case when date_format(now(),'%m%d')>=date_format(s_birth,'%m%d') then 0 else -1 end)) as age from student;
+------+--------+------+
| s_id | s_name | age  |
+------+--------+------+
| 01   | 赵雷   |   30 |
| 02   | 钱电   |   29 |
| 03   | 孙风   |   30 |
| 04   | 李云   |   30 |
| 05   | 周梅   |   28 |
| 06   | 吴兰   |   28 |
| 07   | 郑竹   |   31 |
| 08   | 王菊   |   30 |
+------+--------+------+

-- 47、查询本周过生日的学生

select * from student where week(now()) = week(date_format(s_birth,'%Y%m%d'));

-- 48、查询下周过生日的学生

 select * from student where week(now())+1 = week(date_format(s_birth,'%Y%m%d'));

-- 49、查询本月过生日的学生

 select * from student where date_format(now(),'%m') = date_format(s_birth,'%m');
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 04   | 李云   | 1990-08-06 | 男    |
+------+--------+------------+-------+

-- 50、查询下月过生日的学生
select * from student where date_format(now(),'%m')+1 = date_format(s_birth,'%m');

weixin_46062329

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
2020-08-11

SQL练习题mysql> select * from student;+------+--------+------------+-------+| s_id | s_name | s_birth | s_sex |+------+--------+------------+-------+| 01 | 赵雷 | 1990-01-01 | 男 || 02 | 钱电 | 1990-12-21 | 男 || 03 | 孙风 | 1990-05-.
复制链接

扫一扫