SQL查询练习题

DanceDonkey

已于 2022-05-11 22:54:47 修改

阅读量781

点赞数 1

分类专栏： Mysql 数据库文章标签：数据库

于 2022-05-04 21:35:27 首次发布

本文链接：https://blog.csdn.net/qq_43750656/article/details/124575275

版权

Mysql 同时被 2 个专栏收录

24 篇文章 1 订阅

订阅专栏

数据库

21 篇文章 0 订阅

订阅专栏

经典sql50练习题

测试数据

/*
Navicat MySQL Data Transfer

Source Server         : 阿里云47.93.56.27
Source Server Version : 50735
Source Host           : 47.93.56.27:14217
Source Database       : testdb

Target Server Type    : MYSQL
Target Server Version : 50735
File Encoding         : 65001

Date: 2022-05-11 22:53:24
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cid` varchar(10) DEFAULT NULL,
  `cname` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `tid` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('01', '语文', '02');
INSERT INTO `course` VALUES ('02', '数学', '01');
INSERT INTO `course` VALUES ('03', '英语', '03');

-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
  `sid` varchar(10) DEFAULT NULL,
  `cid` varchar(10) DEFAULT NULL,
  `score` decimal(18,1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES ('01', '01', '80.0');
INSERT INTO `sc` VALUES ('01', '02', '90.0');
INSERT INTO `sc` VALUES ('01', '03', '99.0');
INSERT INTO `sc` VALUES ('02', '01', '70.0');
INSERT INTO `sc` VALUES ('02', '02', '60.0');
INSERT INTO `sc` VALUES ('02', '03', '80.0');
INSERT INTO `sc` VALUES ('03', '01', '80.0');
INSERT INTO `sc` VALUES ('03', '02', '80.0');
INSERT INTO `sc` VALUES ('03', '03', '80.0');
INSERT INTO `sc` VALUES ('04', '01', '50.0');
INSERT INTO `sc` VALUES ('04', '02', '30.0');
INSERT INTO `sc` VALUES ('04', '03', '20.0');
INSERT INTO `sc` VALUES ('05', '01', '76.0');
INSERT INTO `sc` VALUES ('05', '02', '87.0');
INSERT INTO `sc` VALUES ('06', '01', '31.0');
INSERT INTO `sc` VALUES ('06', '03', '34.0');
INSERT INTO `sc` VALUES ('07', '02', '89.0');
INSERT INTO `sc` VALUES ('07', '03', '98.0');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sid` varchar(10) DEFAULT NULL,
  `sname` varchar(10) DEFAULT NULL,
  `sage` datetime DEFAULT NULL,
  `ssex` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('01', '赵雷', '1990-01-01 00:00:00', '男');
INSERT INTO `student` VALUES ('02', '钱电', '1990-12-21 00:00:00', '男');
INSERT INTO `student` VALUES ('03', '孙风', '1990-05-20 00:00:00', '男');
INSERT INTO `student` VALUES ('04', '李云', '1990-08-06 00:00:00', '男');
INSERT INTO `student` VALUES ('05', '周梅', '1991-12-01 00:00:00', '女');
INSERT INTO `student` VALUES ('06', '吴兰', '1992-03-01 00:00:00', '女');
INSERT INTO `student` VALUES ('07', '郑竹', '1989-07-01 00:00:00', '女');
INSERT INTO `student` VALUES ('09', '张三', '2017-12-20 00:00:00', '女');
INSERT INTO `student` VALUES ('10', '李四', '2017-12-25 00:00:00', '女');
INSERT INTO `student` VALUES ('11', '李四', '2017-12-30 00:00:00', '女');
INSERT INTO `student` VALUES ('12', '赵六', '2017-01-01 00:00:00', '女');
INSERT INTO `student` VALUES ('13', '孙七', '2018-01-01 00:00:00', '女');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tid` varchar(10) DEFAULT NULL,
  `tname` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('01', '张三');
INSERT INTO `teacher` VALUES ('02', '李四');
INSERT INTO `teacher` VALUES ('03', '王五');

-- 1.查询" 01 “课程比” 02 “课程成绩高的学生的信息及课程分数
use testdb;
select *
from student s
where (select score from sc where s.sid = sc.sid and sc.cid = '01') >
      (select score from sc where s.sid = sc.sid and sc.cid = '02')

select s.*
from student s
         join sc sc1 on s.sid = sc1.sid and sc1.cid = '01'
         join sc sc2 on s.sid = sc2.sid and sc2.cid = '02'
where sc1.score > sc2.score

-- 2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select s.sid, s.sname, avg(s2.score)
from student s
         join sc s2 on s.sid = s2.sid
group by s.sid, s.sname

-- 3.查询在 SC 表存在成绩的学生信息
select *
from student
where sid in (select sid from sc);

-- 4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
# 一组一组的进行统计，根据要筛选的列， 遍历每条数据，根据本条数据 进行计算，每组得出一个结果展示
select s.sid, s.sname, count(s2.sid), sum(s2.score)
from student s
         left join sc s2 on s.sid = s2.sid
group by s.sid, s.sname


-- 5.查询「李」姓老师的数量
select count(*)
from teacher
where tname like '李%';

-- 6.查询学过「张三」老师授课的同学的信息
select *
from student
where sid in (
    select sid
    from sc
    where cid in (
        select cid
        from course
        where tid = (
            select tid
            from teacher
            where tname = '张三'
        )
    )
);

select s.*
from student s
         join sc on sc.sid = s.sid
         join course c on sc.cid = c.cid
         join teacher t on c.tid = t.tid
where t.tname = '张三';


-- 7. 查询没有学全所有课程的同学的信息
select *
from student
where sid not in (
    select sid
    from sc
    group by sid
    having count(*) = (select count(*) from course)
)

--
select *
from student s
         join (select sid
               from sc
               group by sid
               having count(*) = (select count(*) from course)) all_s
              on all_s.sid = s.sid;

-- 8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
select distinct s.*
from student s
         join sc s2 on s.sid = s2.sid
where s2.cid in (select cid from sc where sc.sid = '01');

-- 9 查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
-- 学生表和成绩表联查 过滤出课程不在01同学课程的同学，这样保证剩下的同学学的课都在01同学学的范围之中
-- 然后再分组，取每个同学学的课的数量等于01同学学的课的数量 == 这样便可保证
select *
from student
where sid = any (
    select s.sid
    from student s
             join sc s2 on s.sid = s2.sid
    where s2.cid in (select cid from sc where sc.sid = '01')
      and s.sid != '01'
    group by s.sid
    having count(*) = (select count(*) from sc scc where scc.sid = '01')
)

-- 10.查询没学过"张三"老师讲授的任一门课程的学生姓名
select *
from student
where sid not in (
    select student.sid
    from student
             join sc s on student.sid = s.sid
    where s.cid in (select cid from course where tid = (select tid from teacher where tname = '张三'))
);

-- 11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select s.sid, s.sname, avg(s2.score)
from student s
         join sc s2 on s.sid = s2.sid
where s2.score < 60
group by s.sid, s.sname
having count(*) > 1

-- 12.检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select s.*, s2.score
from student s
         join sc s2 on s.sid = s2.sid
where s2.cid = '01'
  and s2.score < 60
order by s2.score desc


-- 13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select s.*, s2.cid, (select cname from course c where c.cid = s2.cid) as cname, s2.score, ss.avg_sc
from student s
         join sc s2 on s.sid = s2.sid
         join (select sid, avg(score) avg_sc from sc group by sid) ss on s.sid = ss.sid
order by ss.avg_sc desc;

-- 14.查询各科成绩最高分、最低分和平均分
select cid, max(score), min(score), avg(score)
from sc
group by cid;

-- 15.以如下形式显示：课程 ID，课程 name，
# 最高分，最低分，平均分，及格率，中等率，优良率，优秀率
# 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
# 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select c.cid,
       c.cname,
       max(s2.score),
       min(s2.score),
       avg(s2.score),
       count(*)                                                   cs,
       sum(if(s2.score >= 60, 1, 0)) / count(*)                   jgl,
       sum(if(s2.score >= 70 and s2.score < 80, 1, 0)) / count(*) zdl,
       sum(if(s2.score >= 80 and s2.score < 90, 1, 0)) / count(*) yll,
       sum(if(s2.score >= 90, 1, 0)) / count(*)                   yxl
from sc s2
         join course c on s2.cid = c.cid
group by c.cid, c.cname
order by cs desc, c.cid


-- 16.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
select cid, rank() over (partition by cid order by score desc) rank,score
from sc


-- 18.要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select cid, count(*) num
from sc
group by sc.cid
order by num desc, cid asc;

-- 19.查询学生的总成绩，并进行排名，总分重复时保留名次空缺
select *,
       (select num
        from (select sid, sum(score) num
              from sc
              group by sid) sss
        where sss.sid = s.sid) sum_score
from student s

-- 21.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
select cid,
       (select cname from course c where c.cid = s.cid) as   cname,
       sum(if(score <= 100 and score > 85, 1, 0))            num_100_85,
       sum(if(score <= 100 and score > 85, 1, 0)) / count(*) num_100_85_pre,
       sum(if(score <= 85 and score > 70, 1, 0))             num_85_70,
       sum(if(score <= 85 and score > 70, 1, 0)) / count(*)  num_85_70_pre,
       sum(if(score <= 70 and score > 60, 1, 0))             num_70_60,
       sum(if(score <= 70 and score > 60, 1, 0)) / count(*)  num_70_60_pre,
       sum(if(score <= 60 and score > 0, 1, 0))              num_60_0,
       sum(if(score <= 60 and score > 0, 1, 0)) / count(*)   num_60_0_pre
from sc s
group by s.cid

-- 22.查询各科成绩前三名的记录
select cid,
       max(score)                                                                               sc_1,
       (select distinct score from sc scc where scc.cid = sc.cid order by score desc limit 1,1) sc_2,
       (select distinct score from sc scc where scc.cid = sc.cid order by score desc limit 2,1) sc_3
from sc
group by cid

select distinct cid, score
from sc
where score in
      (
       (select distinct score from sc scc where scc.cid = sc.cid order by score desc limit 0,1),
       (select distinct score from sc scc where scc.cid = sc.cid order by score desc limit 1,1),
       (select distinct score from sc scc where scc.cid = sc.cid order by score desc limit 2,1)
          )
order by cid, score desc


-- 23.查询每门课程被选修的学生数
select cid, count(*)
from sc
group by cid

-- 24.查询出只选修两门课程的学生学号和姓名
select s.*
from student s
         join sc s2 on s.sid = s2.sid
group by s.sid, s.sname


-- 25.查询男生、女生人数
select ssex, count(*)
from student
group by ssex

select (select count(*) from student where ssex = '男') as '男',
       (select count(*) from student where ssex = '女') as '女'
from dual

-- 26.查询名字中含有「风」字的学生信息
select *
from student
where sname like '%凤%'

-- 27.查询同名同性学生名单，并统计同名人数
select sname, ssex, count(*)
from student
group by sname, ssex
having count(*) > 1

select s1.sname, s1.ssex, count(*)
from student s1,
     student s2
where s1.sid != s2.sid
  and s1.sname = s2.sname
  and s1.ssex = s2.ssex
group by s1.sname, s1.ssex


-- 28.查询 1990 年出生的学生名单
select *
from student
where year(sage) = '1990'


-- 29.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select cid, avg(score) avg_score
from sc
group by cid
order by avg_score desc, cid;

-- 30.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select sid, (select sname from student where student.sid = sc.sid), avg(score) avg_score
from sc
group by sid
having avg_score >= 85

-- 31.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
select (select sname from student where sc.sid = student.sid) stu_name, score
from sc
where cid = (select cid from course where cname = '数学')
  and score < 60

select s.sname, s2.score
from student s
         join sc s2 on s.sid = s2.sid
         join course c on s2.cid = c.cid
where c.cname = '数学'
  and s2.score < 60

-- 32.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
select *
from student s
         left join sc s2 on s.sid = s2.sid

-- 33.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select *
from student s
         join sc s2 on s.sid = s2.sid
where s2.score > 70

-- 34.查询不及格的课程
select *
from student s
         join sc s2 on s.sid = s2.sid
where s2.score < 60

-- 35.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
select s.sid, s.sname
from student s
         join sc s2 on s.sid = s2.sid
where s2.cid = '01'
  and s2.score >= 80

-- 36.求每门课程的学生人数
select cid, count(*)
from sc
group by cid

-- 37.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select sid, score
from sc
where cid in (select cid from course where tid = (select tid from teacher where tname = '张三'))
order by score desc
limit 0,1

select *
from student s
         join (select sid, score
               from sc
               where cid in (select cid from course where tid = (select tid from teacher where tname = '张三'))
               order by score desc
               limit 0,1) zs_s
              on s.sid = zs_s.sid


-- 38.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select *
from student s
         join sc s2 on s.sid = s2.sid
         join (select cid, max(score) max_score
               from sc
               where cid in (select cid from course where tid = (select tid from teacher where tname = '张三'))
               group by cid) ss
              on ss.cid = s2.cid
where s2.score = ss.max_score


-- 39.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select distinct s1.*
from sc s1
         join sc s2 on s1.score = s2.score and s1.cid != s2.cid and s1.sid = s2.sid

-- 40.查询每门课程成绩最好的前两名
select distinct cid, score
from sc
where score >= all
      (select score
       from sc scc
       where scc.cid = sc.cid
         and scc.score != (select max(sc2.score) from sc sc2 where sc2.cid = sc.cid))
order by sc.cid,score desc

-- 41.统计每门课程的学生选修人数（超过 5 人的课程才统计）。
select cid,count(*)
from sc
group by cid
having count(*) > 0

-- 42.检索至少选修两门课程的学生学号
select sid,count(*)
from sc
group by sid
having count(*) > 1

-- 43.查询选修了全部课程的学生信息
select sid,count(*)
from sc
group by sid
having count(*) = (select count(*) from course)

-- 44.查询各学生的年龄，只按年份来算
select   (year(now()) - year(sage)) age
from student;

-- 45.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select  sid,  (
    year(now()) - year(sage) -
    case when month(sage) < month(now()) then 0
    when month(sage) = month(now()) and dayofmonth(sage) < dayofmonth(now()) then 0
    when month(sage) = month(now()) and dayofmonth(sage) = dayofmonth(now()) then 1
    else 1
    end
    ) curage
from student;


-- 46.查询本周过生日的学生
select *
from student
where weekofyear(sage) = weekofyear(now())

-- 47.查询下周过生日的学生
select *
from student
where weekofyear(sage) = weekofyear(now()) + 1

-- 48查询本月过生日的学生
select *
from student
where month(sage) = month(now())

-- 49.查询下月过生日的学生
select *
from student
where month(sage) = month(now()) + 1

sql执行顺序

from: from后可以跟多张表，也可以写left join等（根据from的表生成一张虚拟表，如果是多表联查，则会使用笛卡尔积，可以用on筛选 vt1）
where:where后面只是作为各种条件来筛选数据，根据当前这条数据，使用各种函数也好，直接比较也好，或者相关子查询也好，总之where后所做的操作就是决定当前这条数据要不要，过滤后的结果为 vt2。
group by:对vt2虚拟表进行分组，如果是分组查询的话，可以理解成将来的select是一组一组来计算的，每组数据只有一行数据，我们可以使用各种聚合函数来计算该组数据的各种值。如各个部门的员工工资等级，各个课程的各个分数段的人数。
having:having和where一样的都可以对数据进行过滤，只不过having是对分组后的数据进行过滤，注意是分组后的数据。
select:到了select就可以选择要获取的列，这个列可以是表中存在的列，也可以是根据某一个或某几个字段计算出来的列，也可以是根据当前遍历的这条数据使用子查询查询到的一个列，注意，select后的子查询只能返回一条数据，否则会报subquery result more than one。select后面写的最终决定了返回的列。
order by 对select的结果进行排序，这个时候可以用上select 的列
limit 分页获取数据

子查询：

子查询用在where后面，就是起过滤数据的作用，无论是一般子查询还是相关子查询。
子查询用在from后面，就是充当一个表的作用，可以和其他表关联查询。
子查询用在having后面也是过滤数据的，只不过是过滤分组数据的。
子查询用在select后面，那就是展示列结果的。

如果是分组查询，是一组一组计算的，select后的聚合函数也是一条一条的数据遍历计算出来的。

count：累加本组数据
avg：求本组数据平均值
sum：求和本组数据
max：本组数据最大值
min：本组数据最小值

sum(if(age > 10,1,0)) / count(*) ：此表达式还可以求本组数据age>10的占本组数据总共的百分比。

DanceDonkey

关注

1
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
SQL查询练习题

经典sql50练习题-- 1.查询" 01 “课程比” 02 “课程成绩高的学生的信息及课程分数use testdb;select *from student swhere (select score from sc where s.sid = sc.sid and sc.cid = '01') > (select score from sc where s.sid = sc.sid and sc.cid = '02')select s.*from student s.
复制链接

扫一扫