MySQL经典练习题

最新推荐文章于 2023-04-27 10:10:49 发布
最新推荐文章于 2023-04-27 10:10:49 发布
阅读量715 收藏 2
点赞数
分类专栏： 大数据 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44518802/article/details/108009619
版权

数据库及表创建

创建数据库

create database  if not exists test;

创建学生表

CREATE TABLE if not exists student (
    `s_id` varchar(20),
    `s_name` varchar(20) not null default '',
    `s_birth` varchar(20) not null default '',
    `s_sex` varchar(10) not null default '',
    primary key (`s_id`)
);

创建课程表

create table if not exists course(
    c_id varchar(20),
    c_name varchar(20) not null default '',
    t_id varchar(20) not null ,
    primary key (c_id)
);

创建教师表

CREATE TABLE IF NOT EXISTS teacher(
    t_id varchar(20),
    t_name varchar(20) not null default '',
    primary key (t_id)
);

创建成绩表

CREATE TABLE score(
    s_id varchar(20),
    c_id varchar(20),
    s_score int(3),
    primary key (s_id,c_id)

插入测试数据

插入学生表测试数据

insert into student values('01' , '赵雷' , '1990-01-01' , '男');
insert into student values('02' , '钱电' , '1990-12-21' , '男');
insert into student values('03' , '孙风' , '1990-05-20' , '男');
insert into student values('04' , '李云' , '1990-08-06' , '男');
insert into student values('05' , '周梅' , '1991-12-01' , '女');
insert into student values('06' , '吴兰' , '1992-03-01' , '女');
insert into student values('07' , '郑竹' , '1989-07-01' , '女');
insert into student values('08' , '王菊' , '1990-01-20' , '女');

插入课程表测试数据

insert into course values('01' , '语文' , '02');
insert into course values('02' , '数学' , '01');
insert into course values('03' , '英语' , '03');

插入教师表测试数据

insert into teacher values('01' , '张三');
insert into teacher values('02' , '李四');
insert into teacher values('03' , '王五');

插入成绩表测试数据

insert into score values('01' , '01' , 80);
insert into score values('01' , '02' , 90);
insert into score values('01' , '03' , 99);
insert into score values('02' , '01' , 70);
insert into score values('02' , '02' , 60);
insert into score values('02' , '03' , 80);
insert into score values('03' , '01' , 80);
insert into score values('03' , '02' , 80);
insert into score values('03' , '03' , 80);
insert into score values('04' , '01' , 50);
insert into score values('04' , '02' , 30);
insert into score values('04' , '03' , 20);
insert into score values('05' , '01' , 76);
insert into score values('05' , '02' , 87);
insert into score values('06' , '01' , 31);
insert into score values('06' , '03' , 34);
insert into score values('07' , '02' , 89);
insert into score values('07' , '03' , 98);

数据查询语言DQL练习

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

select a.s_id '学生id',a.s_name '学生姓名',a.s_birth '学生生日',a.s_sex '学生性别',b.s_score '分数' 
from student a,(
    select s_id,s_score 
    from score 
    group by s_id 
    having max(s_score)) b 
where a.s_id=b.s_id;
+----------+--------------+--------------+--------------+--------+
| 学生id   | 学生姓名     | 学生生日     | 学生性别     | 分数   |
+----------+--------------+--------------+--------------+--------+
| 01       | 赵雷         | 1990-01-01   | 男           |     80 |
| 02       | 钱电         | 1990-12-21   | 男           |     70 |
| 03       | 孙风         | 1990-05-20   | 男           |     80 |
| 04       | 李云         | 1990-08-06   | 男           |     50 |
| 05       | 周梅         | 1991-12-01   | 女           |     76 |
| 06       | 吴兰         | 1992-03-01   | 女           |     31 |
| 07       | 郑竹         | 1989-07-01   | 女           |     89 |
+----------+--------------+--------------+--------------+--------+

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

select t1.s_id '学生id',t1.s_name '学生姓名',t1.s_birth '学生生日',t1.s_sex '学生性别',t2.课程01分数,t2.课程02分数 
from student t1,(
    select a.s_id,a.s_score '课程01分数',b.s_score '课程02分数' 
    from (
    select s_id,s_score 
    from score 
    where c_id='01') a,(
        select s_id,s_score 
        from score 
        where c_id='02') b
    where a.s_score<b.s_score 
      and a.s_id=b.s_id)t2 
where t1.s_id=t2.s_id;
+----------+--------------+--------------+--------------+----------------+----------------+
| 学生id   | 学生姓名     | 学生生日     | 学生性别     | 课程01分数     | 课程02分数     |
+----------+--------------+--------------+--------------+----------------+----------------+
| 01       | 赵雷         | 1990-01-01   | 男           |             80 |             90 |
| 05       | 周梅         | 1991-12-01   | 女           |             76 |             87 |
+----------+--------------+--------------+--------------+----------------+----------------+

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

select t1.s_name '学生姓名',t1.s_id '学生编号',t2.avgscore '平均成绩'
from student t1, (
    select s_id,avg(s_score) avgscore
    from score
    group by s_id
    having avg(s_score)>=60) t2
where t1.s_id=t2.s_id;
+--------------+--------------+--------------+
| 学生姓名     | 学生编号     | 平均成绩     |
+--------------+--------------+--------------+
| 赵雷         | 01           |      89.6667 |
| 钱电         | 02           |      70.0000 |
| 孙风         | 03           |      80.0000 |
| 周梅         | 05           |      81.5000 |
| 郑竹         | 07           |      93.5000 |
+--------------+--------------+--------------+

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)

select t1.s_id '学生编号',t1.s_name '学生姓名',avg(t2.s_score) '平均成绩'
from student t1,score t2
where t1.s_id=t2.s_id
group by t1.s_id, t1.s_name
having avg(t2.s_score)<60
union
select s_id,s_name,0
from student
where s_id not in (
    select distinct s_id
    from score);
+--------------+--------------+--------------+
| 学生编号     | 学生姓名     | 平均成绩     |
+--------------+--------------+--------------+
| 04           | 李云         |      33.3333 |
| 06           | 吴兰         |      32.5000 |
| 08           | 王菊         |       0.0000 |
+--------------+--------------+--------------+

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

select t1.s_id '学生编号',t1.s_name '学生姓名',count(*) '选课总数',sum(t2.s_score) '课程总成绩' 
from student t1,score t2  
where t1.s_id=t2.s_id 
group by t1.s_id, t1.s_name;
+--------------+--------------+--------------+-----------------+
| 学生编号     | 学生姓名     | 选课总数     | 课程总成绩      |
+--------------+--------------+--------------+-----------------+
| 01           | 赵雷         |            3 |             269 |
| 02           | 钱电         |            3 |             210 |
| 03           | 孙风         |            3 |             240 |
| 04           | 李云         |            3 |             100 |
| 05           | 周梅         |            2 |             163 |
| 06           | 吴兰         |            2 |              65 |
| 07           | 郑竹         |            2 |             187 |
+--------------+--------------+--------------+-----------------+

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

select count(*) '数量'
from teacher
where t_name like '李%';
+--------+
| 数量   |
+--------+
|      1 |
+--------+

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

select student.* 
from teacher,course,score,student 
where t_name='张三' 
  and teacher.t_id=course.t_id 
  and score.c_id=course.c_id 
  and student.s_id=score.s_id;
  +------+--------+------------+-------+
| 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 | 女    |
| 07   | 郑竹   | 1989-07-01 | 女    |
+------+--------+------------+-------+

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

select * 
from student t1 
where t1.s_id not in (
    select s_id 
    from score t2 
        join course t3 
    where t2.c_id=t3.c_id 
      and t3.t_id in (
          select t_id 
          from teacher t4 
          where t4.t_name='张三'));
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 06   | 吴兰   | 1992-03-01 | 女    |
| 08   | 王菊   | 1990-01-20 | 女    |
+------+--------+------------+-------+

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

select * 
from student t1 
where t1.s_id in (
    select s_id 
    from score t2 
    where t2.c_id='01' 
      and t2.s_id in(
          select s_id 
          from score 
          where 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 | 女    |
+------+--------+------------+-------+

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

select t1.* 
from student t1 
where t1.s_id in (
    select s_id 
    from score t2 
    where t2.c_id='01' 
      and t2.s_id not in (
          select t3.s_id 
          from score t3 
          where t3.c_id='02'));
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 06   | 吴兰   | 1992-03-01 | 女    |
+------+--------+------------+-------+

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

select t1.* 
from student t1 
    left join score t2 
        on t1.s_id=t2.s_id 
group by t2.s_id 
having count(*)<(
    select count(*) 
    from course);
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 08   | 王菊   | 1990-01-20 | 女    |
| 05   | 周梅   | 1991-12-01 | 女    |
| 06   | 吴兰   | 1992-03-01 | 女    |
| 07   | 郑竹   | 1989-07-01 | 女    |
+------+--------+------------+-------+

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

select distinct t1.* 
from student t1,score t2 
where t2.c_id in (
    select t2.c_id 
    from score t3 
    where t3.s_id='01' ) 
  and t1.s_id=t2.s_id 
  and t1.s_id!='01';
+------+--------+------------+-------+
| 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 | 女    |
+------+--------+------------+-------+

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

select t1.*
from student t1
where t1.s_id in (
    select distinct s_id 
    from score 
    group by s_id 
    having count(*)=(
        select count(distinct c_id) 
        from score 
        where score.s_id='01' 
        group by s_id))
  and t1.s_id not in(
      select s_id 
      from score 
      where c_id in  (
          select t2.c_id 
          from course t2 
          where t2.c_id not in (
              select t3.c_id 
              from score t3 
              where t3.s_id='01') )) 
  and t1.s_id!='01';
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 02   | 钱电   | 1990-12-21 | 男    |
| 03   | 孙风   | 1990-05-20 | 男    |
| 04   | 李云   | 1990-08-06 | 男    |
+------+--------+------------+-------+

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

select distinct t1.s_name 
from student t1 ,score t2 
where t1.s_id=t2.s_id 
  and t2.c_id not in (
      select c_id 
      from course 
      where t_id = (
          select t_id 
          from teacher 
          where t_name='张三'));
+--------+
| s_name |
+--------+
| 赵雷   |
| 钱电   |
| 孙风   |
| 李云   |
| 周梅   |
| 吴兰   |
| 郑竹   |
+--------+

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

select t1.s_id,t1.s_name,avg(t2.s_score) 
from student t1,score t2 
where t1.s_id in(
    select s_id 
    from score 
    where s_score<60 
    group by s_id 
    having count(*)>=2) 
  and t1.s_id=t2.s_id 
group by t1.s_id;
+------+--------+-----------------+
| s_id | s_name | avg(t2.s_score) |
+------+--------+-----------------+
| 04   | 李云   |         33.3333 |
| 06   | 吴兰   |         32.5000 |
+------+--------+-----------------+

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

select t1.* 
from student t1,score t2 
where t1.s_id=t2.s_id 
  and t2.c_id='01' 
  and t2.s_score<60 
order by t2.s_score desc;
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 04   | 李云   | 1990-08-06 | 男    |
| 06   | 吴兰   | 1992-03-01 | 女    |
+------+--------+------------+-------+

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

select t1.s_id,
       avg(s_score) '平均成绩',
       (select t2.s_score from score t2 where t1.s_id=t2.s_id and t2.c_id='01') '语文',
       (select t3.s_score from score t3 where t1.s_id=t3.s_id and t3.c_id='02') '数学',
       (select t4.s_score from score t4 where t1.s_id=t4.s_id and t4.c_id='02') '英语'
       from score t1 group by t1.s_id;
+------+--------------+--------+--------+--------+
| s_id | 平均成绩     | 语文   | 数学   | 英语   |
+------+--------------+--------+--------+--------+
| 01   |      89.6667 |     80 |     90 |     90 |
| 02   |      70.0000 |     70 |     60 |     60 |
| 03   |      80.0000 |     80 |     80 |     80 |
| 04   |      33.3333 |     50 |     30 |     30 |
| 05   |      81.5000 |     76 |     87 |     87 |
| 06   |      32.5000 |     31 |   NULL |   NULL |
| 07   |      93.5000 |   NULL |     89 |     89 |
+------+--------------+--------+--------+--------+

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

select t1.c_id '课程ID',
       t2.c_name '课程name',
       max(t1.s_score) '最高分',
       min(t1.s_score) '最低分',
       avg(t1.s_score) '平均分',
       round(100*sum(case when t1.s_score>=60 then 1 else null end)/sum(case when t1.s_score then 1 else null end ),2) '及格率',
       round(100*sum(case when t1.s_score >=70 and t1.s_score<80 then 1 else null end )/sum(case when t1.s_score then 1 else null end ),2) '中等率',
       round(100*sum(case when t1.s_score>=80 and t1.s_score<90 then 1 else null end )/sum(case when t1.s_score then 1 else null end),2) '优良率',
       round(100*sum(case when t1.s_score>=90 then 1 else null end)/sum(case when t1.s_score then 1 else null end),2) '优秀率'
       from score t1,course t2 where t1.c_id=t2.c_id group by t1.c_id;
+----------+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 课程ID   | 课程name   | 最高分    | 最低分    | 平均分    | 及格率    | 中等率    | 优良率    | 优秀率    |
+----------+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 01       | 语文       |        80 |        31 |   64.5000 |     66.67 |     33.33 |     33.33 |      NULL |
| 02       | 数学       |        90 |        30 |   72.6667 |     83.33 |      NULL |     50.00 |     16.67 |
| 03       | 英语       |        99 |        20 |   68.5000 |     66.67 |      NULL |     33.33 |     33.33 |
+----------+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+

19、按各科成绩进行排序，并显示排名

MySQL8的方法
select c_id,s_id,s_score,
       rank()  over (partition by c_id order by s_score desc ) '排名rank',
       row_number() over (partition by c_id order by s_score desc ) '排名row_number',
       dense_rank() over (partition by c_id order by s_score desc ) '排名dense_rank'
from score;

MySQL5.6的方法
由于市场上大部分的数据库为MySQL5.5、MySQL5.6和MySQL5.7无法使用开窗函数
select t1.*,@a:=@a+1 '排名' from (select * from score where c_id='01' order by s_score desc) t1,(select @a:=0 )t2
union
select t1.*,@b:=@b+1 '排名' from (select * from score where c_id='02' order by s_score desc) t1,(select @b:=0 )t2
union
select t1.*,@c:=@c+1 '排名' from (select * from score where c_id='03' order by s_score desc) t1,(select @c:=0 )t2;
+------+------+---------+--------+
| s_id | c_id | s_score | 排名   |
+------+------+---------+--------+
| 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 |
+------+------+---------+--------+

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

MySQL8的方法
select 
  s_id,sum(s_score) '总成绩' ,
  rank() over (order by sum(s_score) desc) '排名' 
  from score group by s_id;
  
MySQL5.6的方法
由于市场上大部分的数据库为MySQL5.5、MySQL5.6和MySQL5.7无法使用开窗函数
select t1.*,
       @a:=@a+1 '排名' 
from (
    select s_id,sum(s_score)  
    from score 
    group by s_id 
    order by sum(s_score) desc) t1,(select @a:=0)t2 ;
+------+--------------+--------+
| s_id | sum(s_score) | 排名   |
+------+--------------+--------+
| 01   |          269 |      1 |
| 03   |          240 |      2 |
| 02   |          210 |      3 |
| 07   |          187 |      4 |
| 05   |          163 |      5 |
| 04   |          100 |      6 |
| 06   |           65 |      7 |
+------+--------------+--------+

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

MySQL8的方法
select 
  avg(t1.s_score),
  t3.t_name,
  t1.c_id,rank() over (order by avg(t1.s_score) desc ) '排名' 
  from score t1,course t2,teacher t3 where t1.c_id=t2.c_id and t2.t_id=t3.t_id group by t1.c_id,t3.t_name;
  
MySQL5.6的方法
由于市场上大部分的数据库为MySQL5.5、MySQL5.6和MySQL5.7无法使用开窗函数
select t4.* ,
       @a:=@a+1 '排名'
from (
    select t1.t_name,avg(t2.s_score),t2.c_id 
    from teacher t1,score t2,course t3 
    where t1.t_id=t3.t_id 
      and t2.c_id=t3.c_id 
    group by t2.c_id 
    order by avg(t2.s_score) desc ) t4,(select @a:=0) t5;
+--------+-----------------+------+--------+
| t_name | avg(t2.s_score) | c_id | 排名   |
+--------+-----------------+------+--------+
| 张三   |         72.6667 | 02   |      1 |
| 王五   |         68.5000 | 03   |      2 |
| 李四   |         64.5000 | 01   |      3 |
+--------+-----------------+------+--------+

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

MySQL8的方法
select * from (
    select 
  t1.c_id,
  t1.s_score, 
  t2.*,rank() over (partition by c_id order by s_score desc) a  
  from score t1,student t2 
  where t1.s_id=t2.s_id) t3
where t3.a=2 or t3.a=3;
  
MySQL5.6的方法
由于市场上大部分的数据库为MySQL5.5、MySQL5.6和MySQL5.7无法使用开窗函数
select * from (
select t1.*,@a:=@a+1 a from (select* from score where c_id='01' order by s_score desc)t1 ,(select @a:=0)t2
union
select t1.*,@b:=@b+1 a from (select* from score where c_id='02' order by s_score desc)t1 ,(select @b:=0)t2
union
select t1.*,@c:=@c+1 a from (select* from score where c_id='03' order by s_score desc)t1 ,(select @c:=0)t3) t4,student t5  where a between 2 and 3 and t4.s_id=t5.s_id;
+------+------+---------+------+------+--------+------------+-------+
| s_id | c_id | s_score | a    | s_id | s_name | s_birth    | s_sex |
+------+------+---------+------+------+--------+------------+-------+
| 02   | 03   |      80 |    3 | 02   | 钱电   | 1990-12-21 | 男    |
| 03   | 01   |      80 |    2 | 03   | 孙风   | 1990-05-20 | 男    |
| 05   | 01   |      76 |    3 | 05   | 周梅   | 1991-12-01 | 女    |
| 05   | 02   |      87 |    3 | 05   | 周梅   | 1991-12-01 | 女    |
| 07   | 03   |      98 |    2 | 07   | 郑竹   | 1989-07-01 | 女    |
| 07   | 02   |      89 |    2 | 07   | 郑竹   | 1989-07-01 | 女    |
+------+------+---------+------+------+--------+------------+-------+

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

select c_id ,
       round(100*sum(case when s_score>=0 and s_score<60 then 1 else null end )/sum(case when s_score then 1 else null end ),2) '[0-60]',
       round(100*sum(case when s_score>=60 and s_score<70 then 1 else null end )/sum(case when s_score then 1 else null end ),2) '[60-70]',
       round(100*sum(case when s_score>=70 and s_score<85 then 1 else null end )/sum(case when s_score then 1 else null end ),2) '[70-85]',
       round(100*sum(case when s_score>=85 and s_score<100 then 1 else null end )/sum(case when s_score then 1 else null end ),2) '[85-100]'
from score group by c_id;
+------+--------+---------+---------+----------+
| c_id | [0-60] | [60-70] | [70-85] | [85-100] |
+------+--------+---------+---------+----------+
| 01   |  33.33 |    NULL |   66.67 |     NULL |
| 02   |  16.67 |   16.67 |   16.67 |    50.00 |
| 03   |  33.33 |    NULL |   33.33 |    33.33 |
+------+--------+---------+---------+----------+

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

MySQL8的方法
select * ,
  rank() over (order by avg desc) '排名' 
  from (select s_id,avg(s_score) avg from score group by s_id) t1;
  
MySQL5.6的方法
由于市场上大部分的数据库为MySQL5.5、MySQL5.6和MySQL5.7无法使用开窗函数  
select t1.*,
       @a:=@a+1 '排名' 
from (
    select s_id,avg(s_score) '平均分' 
    from score 
    group by s_id 
    order by avg(s_score) desc)t1,(select @a:=0) t2 ;
+------+-----------+--------+
| s_id | 平均分    | 排名   |
+------+-----------+--------+
| 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 |
+------+-----------+--------+

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

MySQL8的方法
select * from (
  select 
  c_id,
  s_id,
  s_score, 
  row_number() over (partition by c_id order by s_score desc) a from score) t1 where t1.a<=3;

MySQL5.6的方法
由于市场上大部分的数据库为MySQL5.5、MySQL5.6和MySQL5.7无法使用开窗函数  
select * from (
select t1.*,@a:=@a+1 a from (select* from score where c_id='01' order by s_score desc)t1 ,(select @a:=0)t2
union
select t1.*,@b:=@b+1 a from (select* from score where c_id='02' order by s_score desc)t1 ,(select @b:=0)t2
union
select t1.*,@c:=@c+1 a from (select* from score where c_id='03' order by s_score desc)t1 ,(select @c:=0)t3) t4  where a between 1 and 3 ;
+------+------+---------+------+
| s_id | c_id | s_score | a    |
+------+------+---------+------+
| 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(distinct s_id) '学生数' from score group by c_id;
+--------------+-----------+
| 课程编号     | 学生数    |
+--------------+-----------+
| 01           |         6 |
| 02           |         6 |
| 03           |         6 |
+--------------+-----------+

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

select t1.* 
from student t1 
    join (
        select s_id 
        from score 
        group by s_id 
        having count(distinct c_id)=2) t2 on t1.s_id=t2.s_id;
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 05   | 周梅   | 1991-12-01 | 女    |
| 06   | 吴兰   | 1992-03-01 | 女    |
| 07   | 郑竹   | 1989-07-01 | 女    |
+------+--------+------------+-------+

28、查询男生、女生人数

select s_sex,count(1) from student group by s_sex;
+-------+----------+
| s_sex | count(1) |
+-------+----------+
| 女    |        4 |
| 男    |        4 |
+-------+----------+

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

select * from student where s_name like '%风%';
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 03   | 孙风   | 1990-05-20 | 男    |
+------+--------+------------+-------+

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

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

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

select * from student where substr(s_birth,1,4)='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 avg(s_score),c_id from score group by c_id order by avg(s_score) desc ,c_id;
+--------------+------+
| avg(s_score) | c_id |
+--------------+------+
|      72.6667 | 02   |
|      68.5000 | 03   |
|      64.5000 | 01   |
+--------------+------+

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

select t1.s_id,t2.s_name,avg(t1.s_score) 
from score t1,student t2 
where t1.s_id=t2.s_id 
group by t1.s_id 
having avg(t1.s_score) >=85;
+------+--------+-----------------+
| s_id | s_name | avg(t1.s_score) |
+------+--------+-----------------+
| 01   | 赵雷   |         89.6667 |
| 07   | 郑竹   |         93.5000 |
+------+--------+-----------------+

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

select t1.s_name,t3.c_name,t2.s_score
from student t1,score t2,course t3
where t1.s_id=t2.s_id
  and t2.c_id=t3.c_id
  and t3.c_name='数学'
  and t2.s_score<60;
+--------+--------+---------+
| s_name | c_name | s_score |
+--------+--------+---------+
| 李云   | 数学   |      30 |
+--------+--------+---------+

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

select t1.s_id,t2.s_name,
       sum(case t1.c_id when '01' then t1.s_score else 0 end) '语文',
       sum(case t1.c_id when '02' then t1.s_score else 0 end) '数学',
       sum(case t1.c_id when '03' then t1.s_score else 0 end) '英语',
       sum(t1.s_score) '总分'
from score t1,student t2 where t1.s_id=t2.s_id group by t1.s_id;
+------+--------+--------+--------+--------+--------+
| s_id | s_name | 语文   | 数学   | 英语   | 总分   |
+------+--------+--------+--------+--------+--------+
| 01   | 赵雷   |     80 |     90 |     99 |    269 |
| 02   | 钱电   |     70 |     60 |     80 |    210 |
| 03   | 孙风   |     80 |     80 |     80 |    240 |
| 04   | 李云   |     50 |     30 |     20 |    100 |
| 05   | 周梅   |     76 |     87 |      0 |    163 |
| 06   | 吴兰   |     31 |      0 |     34 |     65 |
| 07   | 郑竹   |      0 |     89 |     98 |    187 |
+------+--------+--------+--------+--------+--------+

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

select t1.s_name,t3.c_name,t2.s_score 
from student t1,score t2,course t3 
where t1.s_id=t2.s_id 
and t2.c_id=t3.c_id 
and t2.s_score>=70;
+--------+--------+---------+
| s_name | c_name | s_score |
+--------+--------+---------+
| 赵雷   | 语文   |      80 |
| 钱电   | 语文   |      70 |
| 孙风   | 语文   |      80 |
| 周梅   | 语文   |      76 |
| 赵雷   | 数学   |      90 |
| 孙风   | 数学   |      80 |
| 周梅   | 数学   |      87 |
| 郑竹   | 数学   |      89 |
| 赵雷   | 英语   |      99 |
| 钱电   | 英语   |      80 |
| 孙风   | 英语   |      80 |
| 郑竹   | 英语   |      98 |
+--------+--------+---------+

37、查询不及格的课程

select t1.s_score,t2.s_name,t2.s_id,t3.c_name,t3.c_id 
from score t1,student t2,course t3 
where t1.s_id=t2.s_id 
  and t1.c_id=t3.c_id 
  and t1.s_score<60;
+---------+--------+------+--------+------+
| s_score | s_name | s_id | c_name | c_id |
+---------+--------+------+--------+------+
|      50 | 李云   | 04   | 语文   | 01   |
|      31 | 吴兰   | 06   | 语文   | 01   |
|      30 | 李云   | 04   | 数学   | 02   |
|      20 | 李云   | 04   | 英语   | 03   |
|      34 | 吴兰   | 06   | 英语   | 03   |
+---------+--------+------+--------+------+

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

select t1.s_id,t1.s_name,t2.s_score,t2.c_id 
from student t1,score t2 
where t1.s_id=t2.s_id 
  and t2.s_score>=80 
  and t2.c_id='01';
+------+--------+---------+------+
| s_id | s_name | s_score | c_id |
+------+--------+---------+------+
| 01   | 赵雷   |      80 | 01   |
| 03   | 孙风   |      80 | 01   |
+------+--------+---------+------+

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

select count(*),c_id from score group by c_id;
+----------+------+
| count(*) | c_id |
+----------+------+
|        6 | 01   |
|        6 | 02   |
|        6 | 03   |
+----------+------+

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

select t5.*,t4.max 
from student t5 ,(
    select max(t1.s_score) max,t2.c_id 
    from score t1,course t2,teacher t3 
    where t1.c_id=t2.c_id 
      and t2.t_id=t3.t_id 
      and t3.t_name = '张三' 
    group by t3.t_name,t2.c_id) t4 ,score t6 
where t4.max=t6.s_score 
  and t6.c_id=t4.c_id 
  and t5.s_id=t6.s_id;
+------+--------+------------+-------+------+
| s_id | s_name | s_birth    | s_sex | max  |
+------+--------+------------+-------+------+
| 01   | 赵雷   | 1990-01-01 | 男    |   90 |
+------+--------+------------+-------+------+

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

select s_id,c_id,s_score 
from score  
where s_score in(
    select s_score 
    from score 
    group by s_score 
    having count(*)>1);
+------+------+---------+
| s_id | c_id | s_score |
+------+------+---------+
| 01   | 01   |      80 |
| 02   | 03   |      80 |
| 03   | 01   |      80 |
| 03   | 02   |      80 |
| 03   | 03   |      80 |
+------+------+---------+

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

MySQL8的方法
select 
  c_id,
  s_score,
  s_name, 
  rank() over (partition by c_id order by s_score desc ) '排名' 
  from score t1,student t2 where t1.s_id=t2.s_id;
  
MySQL5.6的方法
由于市场上大部分的数据库为MySQL5.5、MySQL5.6和MySQL5.7无法使用开窗函数  
select * from (
select t1.*,@a:=@a+1 a from (select* from score where c_id='01' order by s_score desc)t1 ,(select @a:=0)t2
union
select t1.*,@b:=@b+1 a from (select* from score where c_id='02' order by s_score desc)t1 ,(select @b:=0)t2
union
select t1.*,@c:=@c+1 a from (select* from score where c_id='03' order by s_score desc)t1 ,(select @c:=0)t3) t4,student t5  where a between 1 and 2 and t4.s_id=t5.s_id;
+------+------+---------+------+------+--------+------------+-------+
| s_id | c_id | s_score | a    | s_id | s_name | s_birth    | s_sex |
+------+------+---------+------+------+--------+------------+-------+
| 01   | 02   |      90 |    1 | 01   | 赵雷   | 1990-01-01 | 男    |
| 01   | 03   |      99 |    1 | 01   | 赵雷   | 1990-01-01 | 男    |
| 01   | 01   |      80 |    1 | 01   | 赵雷   | 1990-01-01 | 男    |
| 03   | 01   |      80 |    2 | 03   | 孙风   | 1990-05-20 | 男    |
| 07   | 02   |      89 |    2 | 07   | 郑竹   | 1989-07-01 | 女    |
| 07   | 03   |      98 |    2 | 07   | 郑竹   | 1989-07-01 | 女    |
+------+------+---------+------+------+--------+------------+-------+

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

select t1.c_id,count(*) 
from score t1 
group by t1.c_id 
having count(*)>5 
order by count(*) desc ,c_id asc;
+------+----------+
| c_id | count(*) |
+------+----------+
| 01   |        6 |
| 02   |        6 |
| 03   |        6 |
+------+----------+

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

select s_id from score group by s_id having count(distinct c_id)>=2;
+------+
| s_id |
+------+
| 01   |
| 02   |
| 03   |
| 04   |
| 05   |
| 06   |
| 07   |
+------+

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

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

先给大家看看当前日期

select date_format(now(),'%Y-%m-%d') '当前年月日';
+-----------------+
| 当前年月日      |
+-----------------+
| 2020-08-14      |
+-----------------+

46、查询各学生的年龄
按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

select (case when dayofyear(now())>dayofyear(s_birth) then year(now())-year(s_birth) else year(now())-year(s_birth)-1 end ) '年龄' ,student.* from student;
+--------+------+--------+------------+-------+
| 年龄   | s_id | s_name | s_birth    | s_sex |
+--------+------+--------+------------+-------+
|     30 | 01   | 赵雷   | 1990-01-01 | 男    |
|     29 | 02   | 钱电   | 1990-12-21 | 男    |
|     30 | 03   | 孙风   | 1990-05-20 | 男    |
|     30 | 04   | 李云   | 1990-08-06 | 男    |
|     28 | 05   | 周梅   | 1991-12-01 | 女    |
|     28 | 06   | 吴兰   | 1992-03-01 | 女    |
|     31 | 07   | 郑竹   | 1989-07-01 | 女    |
|     30 | 08   | 王菊   | 1990-01-20 | 女    |
+--------+------+--------+------------+-------+

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

select * from student where week(s_birth)=week(now());

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

select * from student where weekday(s_birth)=week(now())+1;

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

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

50、查询下月过生日的学生

select * from student where month(s_birth)=month(now())+1;
朱科润
关注
专栏目录
MySQL经典练习题（一）
weixin_39562364的博客
07-02 475
建立学生成绩表scores create table if not exists scores( name varchar(2) not null default ' ', course varchar(4) not null default ' '); 改变表结构，插入列： alter table scores add grade tinyint(1) not null default '0'; 插入数据 单行插入： insert into scores v...
MySQL经典例题
zhangxuelong461的博客
01-10 564
MySQL经典例题 学生表 Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别 课程表 Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号 教师表 Teacher(t_id,t_name) –教师编号,教师姓名 成绩表 Score(s_id,c_id,s_score) –学生编号,课程编号,分数 ...
经典mysql连接查询例题
12-15
MySQL连接查询相信大家都有所了解，连接查询是在数据库查询操作的时候经常用到的，下面就为您介绍MySQL连接查询 mysql连接查询：支持多表连接 对同一张表可以重复连接多次(别名在多次连接同一张表时很重要) 例题1： 下面有2张表 teams表  比赛结果表：result 问题： 得出一张表:主队,客队,比赛成绩,比赛时间 方法一：子查询和连接查询混合   step1: 代码如下:select result.id, t_name as h_name,match_time,result from teams  join result on teams.t_id=result.h_id
mysql经典45道题_MYSQL数据库45道练习题
weixin_35301706的博客
01-28 741
--第一题查询Student表中的所有记录的Sname、Ssex和Class列。select Sname,Ssex,Class from Student;--第二题查询教师所有的单位即不重复的Depart列。select distinct Depart from Teacher;--第三题、 查询Student表的所有记录select * from Student;--第四题、 查询Score表中...
100道MySQL数据库经典面试题解析（收藏版）
森的博客
06-08 1580
前言 100道MySQL数据库经典面试题解析，已经上传github啦 https://github.com/whx123/JavaHome/tree/master/Java%E9%9D%A2%E8%AF%95%E9%A2%98%E9%9B%86%E7%BB%93%E5%8F%B7 公众号：捡田螺的小男孩 数据库 1. MySQL索引使用有哪些注意事项呢？ 可以从三个维度回答这个问题：索引哪些情况会失效，索引不适合哪些场景，索引规则 索引哪些情况会失效 查询条件包含or，可能导致索引失
50道MySQL经典练习题
12-14
这个是网上流传的50道SQL练习题，最近拿来练习，刚做完，这里把我做的答案给大家做个参考，如有错误，还请告知。 另外，做题的时候先不要看答案（答案不唯一，只要满足要求即可），要有自己的一个思考过程，这样做...
mysql经典练习题
10-07
经常练习有好处，很好用的mysql基础练习题，非常建议拿来练手！
MySQL经典练习题.sql
05-27
mysql经典练习题以及答案
MySQL15道经典练习题
weixin_64313980的博客
07-04 4004
前言：本期博客就不标明目录了，我们直接上题目，这一期内容主要是唤醒哪些沉睡在大家心中的之前的SqlServer与oralc的知识点，以及巩固我们的MySQL，其中较难的知识点可能就是联表了，什么内联，外联，左联右联让人头皮发麻，但是不要慌，跟着小刘的步伐慢慢理清思路首先是我们的建表，这里已经把需要的表的代码都交代上了一共四张表,学生，教师，成绩，课程 然后来看看我们的题目： 接下来就是我们的解题部分：01）查询" 01 "课程比" 02 "成绩高的学生的信息及课程分数 语句： 结果： 02）查询同
[Mysql] 经典 50 题
licn188的博客
07-22 7256
Mysql 经典 50题
数据库经典练习题(带答案)
11-26
mysql经典例题,带答案,欢迎下载.分值不高....
MySQL学习：50道经典题目（含参考答案）(更新中）
weixin_42661635的博客
04-27 9186
首先根据course_id将成绩表分为两张表，筛选出" 1 "课程比" 2 "课程成绩高的学生，再与学生表关联。1. 查询" 1 "课程比" 2 "课程成绩高的学生的信息（学号、姓名、性别、出生日期）及课程分数。利用自连接，统计比当前分数高的分数有几个，排名就是几加1。使用case when语句，多个排序条件时用逗号分隔。利用课程数量，计算出所有课程数和每个学生的选课数量。先筛选与" 1 "号同学选课数量相同的同学，再剔除。10. 查询没有学全所有课程的同学的信息。选了" 1 "号同学没选的课的同学。
SQL练习
weixin_30548917的博客
08-23 256
1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数 select stu.*,t1.score as '01score',t2.score as '02score' from (select SId,score from book.sc sc where sc.`CId`='01') as t1,(select SId,score from book.s...
MySQL经典50题_第1到5题
尤尔小屋
05-26 377
出品：尤而小屋作者：Peter编辑：Peter 大家好，我是Peter~ 今天带来的是MySQL经典练习题的第1-5题，题目具体为： 查询"01"课程比"02"课程成绩高的学生的信息、课程分数 查询"01"课程比"02"课程成绩低的学生的信息、课程分数 查询平均成绩大于等于60分的同学的学生编号、学生姓名、平均成绩 查询平均成绩小于60分的同学的学生编号、学生姓名、平均成绩（包括有成绩的和无成绩） 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩 所有的题目牢记这张思维导图中的表和字段信.
MySQL经典50道练习题及全网最详细解析
hujieliang123的博客
01-16 1万+
MySQL经典50道练习全网最详细解析
Mysql经典45道题(详解1-15)
Hudas的博客
02-10 1万+
Mysql经典45道题题解(1-15)
MySQL面试经典100题（收藏版，附答案）
热门推荐
在路上
06-03 2万+
数据库 1. MySQL 索引使用有哪些注意事项呢？ 可以从三个维度回答这个问题：索引哪些情况会失效，索引不适合哪些场景，索引规则 索引哪些情况会失效 查询条件包含or，可能导致索引失效 如何字段类型是字符串，where时一定用引号括起来，否则索引失效 like通配符可能导致索引失效。 联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。 在索引列上使用mysql的内置函数，索引失效。 对索引列运算（如，+、-、*、/），索引失效。...
顺序结构综合实训（专业）
Junds0的博客
10-04 787
本关任务：设银行定期存款的年利率 rate 为 2.25%，并已知存款期为 n 年，存款本金为 capital 元，试编程计算 n 年后的本利之和 deposit。这个符号写在哪里，这个符号前面的输出行结束，符号后面内容将会在新行顶头开始显示，可以在想换行的地方加上这个符号，从而将需要输出的信息分行。本关任务：已知一元二次方程的三个系数，编程求ax 2 +bx+c=0方程的根，系数a，b，c的值由键盘输入，构建一个一元二次方程的实例，假设a，b，c的值，使得 b 2−4ac⩾0成立。
MySQL INSERT对表中数据的操作-插入数据
云源
10-15 3553
MySQL INSERT：插入数据 数据库与表创建成功以后，需要向数据库的表中插入数据。在 MySQL 中可以使用 INSERT 语句向数据库已有的表中插入一行或者多行元组数据。 基本语法 INSERT 语句有两种语法形式，分别是 INSERT…VALUES 语句和 INSERT…SET 语句。 1) INSERT…VALUES语句 INSERT VALUES 的语法格式为： INSERT INTO 表名 （列名1, … 列名n ） VALUES （值1… , 值n）; 2) I..
mysql经典50道练习题pdf
最新发布
08-17
MySQL经典50道练习题pdf是一本经典的MySQL数据库练习题集合，对于想要提高MySQL数据库操作能力的人士来说是非常有价值的。 这本练习题集合涵盖了各种MySQL数据库操作方面的题目，包括数据库创建、表创建、数据插入...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值