SQL强化练习

SQL实练

数据环境准备

数据表创建

 -- 学生表
 -- 学生编号,姓名，出生年月，性别
 create table Student(
     SId varchar(10),
     Sname varchar(10),
     Sage datetime,
     Ssex varchar(10)
 ); 
 
 -- 课程表
 -- 课程编号，课程名称，教师编号
 create table Course(
     CId varchar(10),
     Cname nvarchar(10),
     TId varchar(10)
 );
 
 -- 教师表
 -- 教师编号，教师姓名
 create table Teacher(
     TId varchar(10),
     Tname varchar(10)
 );
 
 -- 成绩表
 -- 学生编号，课程编号，成绩
 create table SC(
     SId varchar(10),
     CId varchar(10),
     score decimal(18,1)
 );

数据初始化

 -- 学生表 Student
 -- 学生表 Student
 insert into Student values('01' , '赵雷' , '1990-01-01' , '男'); 
 insert into Student values('02' , '钱电' , '1990-12-21' , '男'); 
 insert into Student values('03' , '孙风' , '1990-12-20' , '男'); 
 insert into Student values('04' , '李云' , '1990-12-06' , '男'); 
 insert into Student values('05' , '周梅' , '1991-12-01' , '女'); 
 insert into Student values('06' , '吴兰' , '1992-01-01' , '女'); 
 insert into Student values('07' , '郑竹' , '1989-01-01' , '女'); 
 insert into Student values('09' , '张三' , '2017-12-20' , '女'); 
 insert into Student values('10' , '李四' , '2017-12-25' , '女'); 
 insert into Student values('11' , '李四' , '2012-06-06' , '女'); 
 insert into Student values('12' , '赵六' , '2013-06-13' , '女'); 
 insert into Student values('13' , '孙七' , '2014-06-01' , '女'); 
 
 -- 课程表 Course 
 insert into Course values('01' , '语文' , '02'); 
 insert into Course values('02' , '数学' , '01'); 
 insert into Course values('03' , '英语' , '03');
 
 -- 教师表 Teacher 
 insert into Teacher values('01' , '张三'); 
 insert into Teacher values('02' , '李四'); 
 insert into Teacher values('03' , '王五'); 
 
 -- 成绩表 SC 
 insert into SC values('01' , '01' , 80); 
 insert into SC values('01' , '02' , 90); 
 insert into SC values('01' , '03' , 99); 
 insert into SC values('02' , '01' , 70); 
 insert into SC values('02' , '02' , 60); 
 insert into SC values('02' , '03' , 80); 
 insert into SC values('03' , '01' , 80); 
 insert into SC values('03' , '02' , 80); 
 insert into SC values('03' , '03' , 80); 
 insert into SC values('04' , '01' , 50); 
 insert into SC values('04' , '02' , 30); 
 insert into SC values('04' , '03' , 20); 
 insert into SC values('05' , '01' , 76); 
 insert into SC values('05' , '02' , 87); 
 insert into SC values('06' , '01' , 31); 
 insert into SC values('06' , '03' , 34); 
 insert into SC values('07' , '02' , 89); 
 insert into SC values('07' , '03' , 98);

练习题

常规题

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

 -- 1.查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
 -- 查询 01 课程成绩的学生 t1
 select sc.sid, sc.score
 from SC sc
 where sc.cid = '01';
 
 -- 查询 02 课程成绩的学生 t2
 select sc.sid, sc.score
 from SC sc
 where sc.cid = '02';
 
 -- t1左连接t2, 组合，加上t1.score>t2.score
 SELECT t1.SId,
        t1.CId,
        t1.score as '语文',
        t2.score as '数学'
 FROM (SELECT SId,
              CId,
              score
       FROM SC
       WHERE CId = '01') t1
          LEFT JOIN
      (SELECT SId,
              CId,
              score
       FROM SC
       WHERE CId = '02') t2
      ON t1.SId = t2.SId
 WHERE t1.score > t2.score;
 
 -- 组合学生表查询学生信息
 SELECT tt1.SId
      , tt2.Sname
      , tt3.CId
      , tt3.score
 FROM (SELECT t1.SId
       FROM (SELECT SId
                  , CId
                  , score
             FROM SC
             where CId = '01') t1
                LEFT JOIN(SELECT SId
                               , CId
                               , score
                          FROM SC
                          WHERE CId = '02') t2
                         ON t1.SId = t2.SId
       WHERE t1.Score > t2.Score) tt1
          JOIN Student tt2 ON tt1.SId = tt2.SId
          JOIN SC tt3 ON tt1.SId = tt3.SId;

2.查询同时存在" 01 “课程和” 02 "课程的情况

 -- 查询 01 课程成绩的学生 t1
 select sc.sid, sc.score
 from SC sc
 where sc.cid = '01';
 
 -- 查询 02 课程成绩的学生 t2
 select sc.sid, sc.score
 from SC sc
 where sc.cid = '02';
 
 -- 内连接
 SELECT t1.SId
 FROM (SELECT SId
       FROM SC
       WHERE CId = '01') AS t1
          JOIN (SELECT SId
                FROM SC
                WHERE CId = '02') AS t2
               ON t1.SId = t2.SId;

3.查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )

 -- 左连接
 SELECT t1.SId
      , t1.CId
      , t1.score
      , t2.CId   AS t2CId
      , t2.score AS t2Score
 FROM (SELECT SId
            , CId
            , score
       FROM SC
       WHERE CId = '01') t1
          LEFT JOIN(SELECT SId
                         , CId
                         , score
                    FROM SC
                    WHERE CId = '02') t2
                   ON t1.SId = t2.SId;

4.查询不存在" 01 “课程但存在” 02 "课程的情况

 -- 右连接
 SELECT t1.SId
      , t1.CId
      , t1.score
      , t2.CId   AS t2CId
      , t2.score AS t2Score
 FROM (SELECT SId
            , CId
            , score
       FROM SC
       WHERE CId = '01') t1
          RIGHT JOIN(SELECT SId
                          , CId
                          , score
                     FROM SC
                     WHERE CId = '02') t2
                    ON t1.SId = t2.SId;

5.查询平均成绩于大于 60 分的同学的学生编号和学生姓名和平均成绩

 -- round(Number,2)：保留2位小数
 -- 查询表SC，按照学生编号分组取平均成绩，并加条件：平均成绩>60
 select sc.sid, round(avg(sc.score), 2) as avgScore
 from SC sc
 group by sc.sid
 having round(avg(sc.score), 2) > 60;
 -- 联合学生表
 select tt.sid, s.sname, tt.avgScore
 from (select sc.sid, round(avg(sc.score), 2) as avgScore
       from SC sc
       group by sc.sid
       having round(avg(sc.score), 2) > 60) tt
 join Student s on tt.SId=s.SId

6.查询在 SC 表存在成绩的学生信息

 -- distinct去重查询sid
 select distinct sc.sid
 from SC sc;
 -- 查询学生信息
 select s.*
 from Student s
 where s.SId in (select distinct sc.sid from SC sc)

7.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩，没成绩的显示为 null

 -- 按照sid分组统计选课总数
 select sc.sid, count(1) as courseNum
 from SC sc
 group by sc.sid;
 -- 按照sid分组统计总成绩
 select sc.sid, SUM(sc.score) as SumScore
 from SC sc
 group by sc.sid;
 
 -- 上述2个可以一起查询
 select sc.sid, count(1) as courseNum, SUM(sc.score) as SumScore
 from SC sc
 group by sc.sid;
 -- 联合学生表信息查询
 select s.sid, s.sname, t1.courseNum, t1.SumScore
 from Student s
          left join (select sc.sid, count(1) as courseNum, SUM(sc.score) as SumScore
                     from SC sc
                     group by sc.sid) t1 on s.sid = t1.SId;

8.查询「李」姓老师的数量

 select count(1) from Teacher t where t.Tname like '李%'

9.查询学过「张三」老师授课的同学的信息

 -- 1.查询张三老师的tid
 select t.TId
 from Teacher t
 where t.Tname = '张三';
 
 -- 2.查询张三老师教的课程id
 select c.CId
 from Course c
 where c.TId = (select t.TId from Teacher t where t.Tname = '张三');
 
 -- 3.查询学了这些课程的学生sid
 select distinct sc.sid
 from SC sc
 where sc.CId in (select c.CId from Course c where c.TId = (select t.TId from Teacher t where t.Tname = '张三'));
 
 -- 4.查询学生信息
 -- 子查询
 select s.*
 from Student s
 where s.SId in (select distinct sc.sid
                 from SC sc
                 where sc.CId in
                       (select c.CId from Course c where c.TId = (select t.TId from Teacher t where t.Tname = '张三')));
                       
 -- 或者使用内连接
 select s.*
 from Student s
          join(select distinct sc.sid
               from SC sc
               where sc.CId in
                     (select c.CId from Course c where c.TId = (select t.TId from Teacher t where t.Tname = '张三'))) t1
              on s.SId = t1.SId;

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

 -- 1.按照学生sid分组统计课程数量
 select sc.sid, count(1) as courseNum
 from SC sc
 group by sc.sid;
 
 -- 统计所有课程数量
 select count(1) as totalCourseNum
 from Course;
 
 -- 联合学生信息
 select s.*, t1.courseNum
 from Student s
          left join (select sc.sid, count(1) as courseNum from SC sc group by sc.sid) t1
                    on s.SId = t1.SId
 where t1.courseNum<3 ||t1.courseNum is null;

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

 -- 1.查询学号为" 01 "的同学所学课程信息
 select sc.CId
 from SC sc
 where sc.SId = '01';
 
 -- 查询课程编号在上述表的学生sid，排除掉sid=01的
 select distinct sc.sid
 from SC sc
 where sc.CId in (select sc.CId from SC sc where sc.SId = '01')
   and sc.SId != '01';
 
 -- 2.查询学生信息
 select *
 from Student s
 where s.SId in (select distinct sc.sid
                 from SC sc
                 where sc.CId in (select sc.CId from SC sc where sc.SId = '01')
                   and sc.SId != '01');

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

 -- 1.查询学号为" 01 "的同学所学课程信息
 select sc.CId
 from SC sc
 where sc.SId = '01';
 
 -- 查询其他同学的课程编号信息
 SELECT SId,
        CId
 FROM SC
 WHERE SId != '01';
 
 -- 课程数量要和" 01 "号的同学的一样，并且课程编号一样
 select sc.sid, count(1) as courseNumCount
 from SC sc
 group by sc.sid
 having (count(1)) = (select count(1)
                      from SC sc
                      where sc.SId = '01');
 -- 查询符合条件的学生编号
 select distinct t1.SId
 from (select sc.sid, count(1) as courseNumCount
       from SC sc
       group by sc.sid
       having (count(1)) = (select count(1)
                            from SC sc
                            where sc.SId = '01')) t1
          join(select sc.sid
               from SC sc
               where sc.SId != '01'
                 and sc.CId in (select sc.CId
                                from SC sc
                                where sc.SId = '01')) t2 on t1.SId=t2.SId;
 
 -- 查询学生信息
 select s.* from Student s join (select distinct t1.SId
 from (select sc.sid, count(1) as courseNumCount
       from SC sc
       group by sc.sid
       having (count(1)) = (select count(1)
                            from SC sc
                            where sc.SId = '01')) t1
          join(select sc.sid
               from SC sc
               where sc.SId != '01'
                 and sc.CId in (select sc.CId
                                from SC sc
                                where sc.SId = '01')) t2 on t1.SId=t2.SId) tt on s.SId=tt.SId;

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

 -- 1.查询张三老师教的课程
 select c.CId
 from Course c
 where c.TId = (select t.TId from Teacher t where t.Tname = '张三');
 
 select group_concat('%', c.cid, '%')
 from Course c
 where c.TId = (select t.TId from Teacher t where t.Tname = '张三');
 
 
 -- 查询学生课程信息
 select sc.SId, group_concat(sc.CId order by sc.CId asc)
 from SC sc
 group by sc.SId;
 
 select s.SId, s.Sname, t1.CId
 from Student s
          left join (select sc.SId, group_concat(sc.CId order by sc.CId asc) as cid
                     from SC sc
                     group by sc.SId) t1 on s.SId = t1.SId;
 
 -- 拼接条件：没学过"张三"老师讲授的任一门课程
 select s.SId, s.Sname, t1.CId
 from Student s
          left join (select sc.SId, group_concat(sc.CId order by sc.CId asc) as cid from SC sc group by sc.SId) t1
                    on s.SId = t1.SId
 where t1.CId not like (select group_concat('%', c.cid, '%')
                        from Course c
                        where c.TId = (select t.TId from Teacher t where t.Tname = '张三'))
    or t1.cid is null;

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

 -- 查询不及格的同学及成绩
 select *
 from SC sc
 where sc.score < 60;
 
 -- 按照sid分组统计
 select sc.sid, count(1) as courseNum, avg(sc.score)
 from (select * from SC sc where sc.score < 60) sc
 group by sc.sid
 having (count(1)) >= 2;
 
 -- 联合学生表查询
 select s.SId, s.Sname, t1.avgScore
 from Student s
          join (select sc.sid, count(1) as courseNum, avg(sc.score) as avgScore
                from (select * from SC sc where sc.score < 60) sc
                group by sc.sid
                having (count(1)) >= 2) t1
               on s.SId = t1.SId;

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

-- 1." 01 "课程分数小于 60
select *
from SC sc
where sc.CId = '01'
  and sc.score < 60;

-- 关联学生信息表
SELECT t2.SId
     ,t2.Sname
     ,t1.score
FROM
    SC t1 JOIN Student t2
               ON t1.SId = t2.SId
                   AND t1.CId = '01' AND t1.score < 60
ORDER BY t1.score DESC;

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

-- 查询平均成绩
select sc.SId, avg(sc.score) as avgScore
from SC sc
group by sc.SId;

-- 查询语文成绩:课程编号01
select sc.sid, sc.score as '语文成绩'
from SC sc
where sc.CId = '01';

-- 查询数学成绩:课程编号02
select sc.sid, sc.score as '数学成绩'
from SC sc
where sc.CId = '02';

-- 查询英语成绩:课程编号03
select sc.sid, sc.score as '英语成绩'
from SC sc
where sc.CId = '03';

-- 联合以上表查询
select tt1.sid, tt1.avgScore, tt2.语文成绩, tt3.数学成绩, tt4.英语成绩
from (select sc.SId, avg(sc.score) as avgScore from SC sc group by sc.SId) tt1
         left join (select sc.sid
                         , sc.score as '语文成绩'
                    from SC sc
                    where sc.CId = '01') tt2
                   on tt1.sid = tt2.sid
         left join (select sc.sid, sc.score as '数学成绩' from SC sc where sc.CId = '01') tt3 on tt1.sid = tt3.sid
         left join (select sc.sid, sc.score as '英语成绩' from SC sc where sc.CId = '03') tt4 on tt1.sid = tt4.sid
         order by tt1.avgScore desc;

-- with as 临时表改写
with tt as (select tt1.sid, tt1.avgScore, tt2.语文成绩, tt3.数学成绩, tt4.英语成绩
            from (select sc.SId, avg(sc.score) as avgScore from SC sc group by sc.SId) tt1
                     left join (select sc.sid
                                     , sc.score as '语文成绩'
                                from SC sc
                                where sc.CId = '01') tt2
                               on tt1.sid = tt2.sid
                     left join (select sc.sid, sc.score as '数学成绩' from SC sc where sc.CId = '01') tt3
                               on tt1.sid = tt3.sid
                     left join (select sc.sid, sc.score as '英语成绩' from SC sc where sc.CId = '03') tt4
                               on tt1.sid = tt4.sid)
select tt.sid, tt.avgScore, tt.语文成绩, tt.数学成绩, tt.英语成绩 from tt order by tt.avgScore desc;

17.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程 ID，课程 name，最分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

-- 17.查询各科成绩最高分、最低分和平均分：
-- 以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
-- 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

-- 1.最高分、最低分、平均分查询
select sc.CId,
       MAX(sc.score)           as maxScore,
       MIN(sc.score)           as minScore,
       round(avg(sc.score), 2) as avgScore
from SC sc
group by sc.CId;

-- 2.及格率，中等率，优良率，优秀率
select sc.CId,
       concat(round((select count(1) from SC t1 where sc.CId = t1.CId and t1.score >= 60) /
                    (select count(1) from SC t1 where sc.CId = t1.CId), 4) * 100, '%') as '及格率',
       concat(round((select count(1) from SC t1 where sc.CId = t1.CId and t1.score >= 70 and t1.score <= 80) /
                    (select count(1) from SC t1 where sc.CId = t1.CId), 4) * 100, '%') as '中等率',
       concat(round((select count(1) from SC t1 where sc.CId = t1.CId and t1.score >= 80 and t1.score <= 90) /
                    (select count(1) from SC t1 where sc.CId = t1.CId), 4) * 100, '%') as '优良率',
       concat(round((select count(1) from SC t1 where sc.CId = t1.CId and t1.score >= 90) /
                    (select count(1) from SC t1 where sc.CId = t1.CId), 4) * 100, '%') as '优秀率'
from SC sc
group by sc.CId;

-- 3.课程编号和选课人数
select sc.CId,
       count(1) as '选课人数'
from SC sc
group by sc.CId;

-- 联合以上表
select t1.cid      as '课程 ID',
       (case
            when t1.CId = '01' then '语文'
            when t1.CId = '02' then '数学'
            when t1.CId = '03' then '英语'
            else ''
           end)    as '课程名称',
       t1.maxScore as '最高分',
       t1.minScore as '最低分',
       t1.avgScore as '平均分',
       t2.及格率,
       t2.中等率,
       t2.优良率,
       t2.优秀率,
       t3.选课人数
from (select sc.CId                  as cid,
             MAX(sc.score)           as maxScore,
             MIN(sc.score)           as minScore,
             round(avg(sc.score), 2) as avgScore
      from SC sc
      group by sc.CId) t1
         join (select sc.CId,
                      concat(round((select count(1) from SC t1 where sc.CId = t1.CId and t1.score >= 60) /
                                   (select count(1) from SC t1 where sc.CId = t1.CId), 4) * 100, '%') as '及格率',
                      concat(round((select count(1)
                                    from SC t1
                                    where sc.CId = t1.CId
                                      and t1.score >= 70
                                      and t1.score <= 80) /
                                   (select count(1) from SC t1 where sc.CId = t1.CId), 4) * 100, '%') as '中等率',
                      concat(round((select count(1)
                                    from SC t1
                                    where sc.CId = t1.CId
                                      and t1.score >= 80
                                      and t1.score <= 90) /
                                   (select count(1) from SC t1 where sc.CId = t1.CId), 4) * 100, '%') as '优良率',
                      concat(round((select count(1) from SC t1 where sc.CId = t1.CId and t1.score >= 90) /
                                   (select count(1) from SC t1 where sc.CId = t1.CId), 4) * 100, '%') as '优秀率'
               from SC sc
               group by sc.CId) t2 on t1.cid = t2.CId
         join (select sc.CId,
                      count(1) as '选课人数'
               from SC sc
               group by sc.CId) t3 on t1.cid = t3.CId
order by t3.选课人数 desc, t1.cid asc;

18.按各科平均成绩进行排序，并显示排名

 -- 查询各科平均成绩
 select sc.cid,
        round(avg(sc.score), 2) as avgScore
 from SC sc
 group by sc.cid
 order by avgScore desc;
 
 -- 定义一个变量@i然后将上表作为t1表进行排名计算，查询出结果
 set @i := 0;
 select t1.CId,
        t1.avgScore  as '平均成绩',
        @i := @i + 1 as '排名'
 from (select sc.cid,
              round(avg(sc.score), 2) as avgScore
       from SC sc
       group by sc.cid
       order by avgScore desc) t1

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

 -- 1.总分相同时并列，并且排名不连续
 -- 按照学生sid分组，查询总成绩
 select sc.SId, SUM(sc.score) as totalScore
 from SC sc
 group by sc.SId;
 
 -- 使用rank()函数进行排名，总分相同时并列，并且排名不连续
 select rank() over (order by t1.totalScore desc) as '排名',
        t1.SId,
        t1.totalScore                             as '总成绩'
 from (select sc.SId, SUM(sc.score) as totalScore
       from SC sc
       group by sc.SId) t1;
 
 -- 使用dense_rank()函数进行排名，总分相同时并列，并且排名连续
 select dense_rank() over (order by t1.totalScore desc) as '排名',
        t1.SId,
        t1.totalScore                             as '总成绩'
 from (select sc.SId, SUM(sc.score) as totalScore
       from SC sc
       group by sc.SId) t1;

22.统计各科成绩各分数段人数：课程编号，课程名称，[100-85），[85-70），[70-60），[60-0）及所占百分

 -- 22.统计各科成绩各分数段人数：课程编号，课程名称，[100-85），[85-70），[70-60），[60-0）及所占百分
 
 -- 1.统计各科各分数段人数
 select sc.CId,
        concat(round((select count(1) from SC t1 where t1.CId = sc.CId and t1.score > 85 and t1.score <= 100) /
                     (select count(1) from SC t2 where t2.CId = sc.CId), 4) * 100, '%') as '[100-85）',
        concat(round((select count(1) from SC t1 where t1.CId = sc.CId and t1.score > 70 and t1.score <= 85) /
                     (select count(1) from SC t2 where t2.CId = sc.CId), 4) * 100, '%') as '[85-70）',
        concat(round((select count(1) from SC t1 where t1.CId = sc.CId and t1.score > 60 and t1.score <= 70) /
                     (select count(1) from SC t2 where t2.CId = sc.CId), 4) * 100, '%') as '[70-60）',
        concat(round((select count(1) from SC t1 where t1.CId = sc.CId and t1.score > 0 and t1.score <= 60) /
                     (select count(1) from SC t2 where t2.CId = sc.CId), 4) * 100, '%') as '[60-0）'
 from SC sc
 group by sc.CId;
 
 -- 关联上述查询结果
 
 select t1.CId,
        (case
             when t1.CId = '01' then '语文'
             when t1.CId = '02' then '数学'
             when t1.CId = '03' then '英语'
             else ''
            end) as '课程名称',
     t1.`[100-85）`,
     t1.`[85-70）`,
     t1.`[70-60）`,
     t1.`[60-0）`
 from (select sc.CId,
              concat(round((select count(1) from SC t1 where t1.CId = sc.CId and t1.score > 85 and t1.score <= 100) /
                           (select count(1) from SC t2 where t2.CId = sc.CId), 4) * 100, '%') as '[100-85）',
              concat(round((select count(1) from SC t1 where t1.CId = sc.CId and t1.score > 70 and t1.score <= 85) /
                           (select count(1) from SC t2 where t2.CId = sc.CId), 4) * 100, '%') as '[85-70）',
              concat(round((select count(1) from SC t1 where t1.CId = sc.CId and t1.score > 60 and t1.score <= 70) /
                           (select count(1) from SC t2 where t2.CId = sc.CId), 4) * 100, '%') as '[70-60）',
              concat(round((select count(1) from SC t1 where t1.CId = sc.CId and t1.score > 0 and t1.score <= 60) /
                           (select count(1) from SC t2 where t2.CId = sc.CId), 4) * 100, '%') as '[60-0）'
       from SC sc
       group by sc.CId) t1;

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

 -- 1.查询各科成绩并排序
 select
     sc.CId,
     sc.score
 from SC sc
 order by sc.CId asc,sc.score desc;
 
 -- 使用排序函数排序
 select
     row_number() over (partition by t1.CId order by t1.score desc) as rn,
     t1.sid,
     t1.sname,
     t1.CId,
     t1.score
 from (select
           sc.SId as sid,
           (select s.Sname from Student s where s.SId=sc.SId) as sname,
           sc.CId,
           sc.score
       from SC sc
       order by sc.CId asc,sc.score desc) t1;
 
 -- 再封装一层，取前三行的数据
 select * from (select
                    row_number() over (partition by t1.CId order by t1.score desc) as rn,
                    t1.sid,
                    t1.sname,
                    t1.CId,
                    t1.score
                from (select
                          sc.SId as sid,
                          (select s.Sname from Student s where s.SId=sc.SId) as sname,
                          sc.CId,
                          sc.score
                      from SC sc
                      order by sc.CId asc,sc.score desc) t1) t2
 where t2.rn<=3;

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

 select sc.CId, count(1) as '选课人数'
 from SC sc
 group by sc.CId;

25.查询出只选修两门课程的学生学号和姓名

 -- 1.查询学生学号和选课数量
 select sc.SId, count(1) as '学生选课数量'
 from SC sc
 group by sc.SId;
 
 -- 2.加入条件：只选修两门课程
 select sc.SId, count(1) as '学生选课数量'
 from SC sc
 group by sc.SId
 having count(1)=2;
 
 -- 关联学生表
 select s.SId,Sname from Student s
 join (select sc.SId, count(1) as '学生选课数量'
       from SC sc
       group by sc.SId
       having count(1)=2) t on s.SId=t.SId;

26.查询男生、女生人数

 select s.Ssex, count(1) as '性别人数'
 from Student s
 group by s.Ssex;

28.查询同名同性学生名单，并统计同名同性人数

 select s.Sname, Ssex, count(1)
 from Student s
 group by s.Sname, Ssex
 having count(1) > 1;

29.查询 1990 年出生的学生名单

 -- 实现一：模糊查询
 select *
 from Student s
 where s.Sage like '1990%';
 
 -- 实现二：日期区间
 select *
 from Student s
 where s.Sage between '1990-01-01' and '1990-12-31';

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

 -- 1.查询每门课程的平均成绩
 select sc.CId, avg(sc.score)
 from SC sc
 group by sc.CId;
 
 -- 2.添加排序
 select sc.CId, avg(sc.score)
 from SC sc
 group by sc.CId
 order by avg(sc.score) desc, sc.CId asc;

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

 -- 1.查询每个学生的平均成绩
 select sc.SId, avg(sc.score)
 from SC sc
 group by sc.SId;
 
 -- 2.加入条件：平均成绩大于等于 85
 
 select sc.SId, avg(sc.score)
 from SC sc
 group by sc.SId
 having avg(sc.score) >= 85;
 
 -- 关联学生信息表
 select s.SId,Sname,t.avgScore as '平均成绩'
 from Student s
          join(select sc.SId, avg(sc.score) as avgScore
               from SC sc
               group by sc.SId
               having avg(sc.score) >= 85) t on s.SId = t.SId;

32.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

-- 1.查询分数低于60的数据
select sc.*
from SC sc
where sc.score < 60;

-- 2.查询课程名称为「数学」的课程编号
select c.CId
from Course c
where c.Cname = '数学';

-- 3.关联上述2张表得到课程名称为「数学」，且分数低于 60 的数据
select sc.SId, sc.score
from SC sc
         join (select c.CId from Course c where c.Cname = '数学') t on sc.CId = t.CId
where sc.score < 60;

-- 4.关联学生表，查询学生信息
select s.SId, Sname, tt.score
from Student s
         join(select sc.SId, sc.score
              from SC sc
                       join (select c.CId from Course c where c.Cname = '数学') t on sc.CId = t.CId
              where sc.score < 60) tt
             on s.SId = tt.SId

33.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

 -- 1.查询成绩表SC，获取每个学生的每门课程成绩
 -- 实现一：
 SELECT SId,
        SUM(CASE CId WHEN '01' THEN score ELSE 0 END) AS '语文成绩',
        SUM(CASE CId WHEN '02' THEN score ELSE 0 END) AS '数学成绩',
        SUM(CASE CId WHEN '03' THEN score ELSE 0 END) AS '英语成绩'
 FROM SC
 GROUP BY SId;
 
 -- 实现二：
 select SId,
        SUM(if(cid = '01', score, 0)) as '语文成绩',
        SUM(if(cid = '02', score, 0)) as '数学成绩',
        SUM(if(cid = '03', score, 0)) as '英语成绩'
 from SC
 GROUP BY SId;
 
 -- 关联学生信息表
 select s.SId,
        s.Sname,
        t.语文成绩 as '语文成绩',
        t.数学成绩 as '数学成绩',
        t.英语成绩 as '英语成绩'
 from Student s
          left join (select SId,
                            SUM(if(cid = '01', score, 0)) as '语文成绩',
                            SUM(if(cid = '02', score, 0)) as '数学成绩',
                            SUM(if(cid = '03', score, 0)) as '英语成绩'
                     from SC
                     GROUP BY SId) t on s.SId = t.SId;
 
 -- 如果没选课视作0，可以再包裹一层
 select
     tt.SId,
     tt.Sname,
     if(tt.语文成绩 is null ,0,tt.语文成绩) as '语文成绩',
     if(tt.数学成绩 is null ,0,tt.数学成绩) as '数学成绩',
     if(tt.英语成绩 is null ,0,tt.英语成绩) as '英语成绩'
 from (select s.SId,
        s.Sname,
        t.语文成绩 as '语文成绩',
        t.数学成绩 as '数学成绩',
        t.英语成绩 as '英语成绩'
 from Student s
          left join (select SId,
                            SUM(if(cid = '01', score, 0)) as '语文成绩',
                            SUM(if(cid = '02', score, 0)) as '数学成绩',
                            SUM(if(cid = '03', score, 0)) as '英语成绩'
                     from SC
                     GROUP BY SId) t on s.SId = t.SId) tt;

三种常见的排名

ROW_NUMBER：可以做为序号

 -- 1 2 3 4 5 6 7 没有重复排名，依次递增
 -- MySQL 5.7
 set @i := 0;
 SELECT t1.SId,
        t1.CId,
        t1.score,
        @i := @i + 1 as '排名'
 from (
     SELECT
     SId, CId, score
     from SC
     order by score desc
     ) t1;
     
 -- mysql 8.0以上
 select row_number() over (order by t1.score desc) as '排名',
        t1.SId,
        t1.CId,
        t1.score
 from SC t1

DENSE_RANK

 -- 1 2 3 3 3 4 5 6 7 有重复时并列排名，最终排名也是连续的
 -- MySQL 5.7
 SET @i := 0;
 SET @p := 0;
 SET @q := 0;
 SELECT t1.SId,
        t1.CId,
        t1.score,
        @p := t1.score,
        if(@p = @q, @i, @i := @i + 1) as '排名',
     @q :=@p
 from (
     SELECT SId,
     CId,
     score
     from SC
     order by score desc
     ) t1;
     
 -- mysql 8.0以上
 -- dense_rank()函数
 select dense_rank() over (order by t1.score desc) as '排名',
        t1.SId,
        t1.CId,
        t1.score
 from SC t1;

RANK

 -- 1 2 3 3 3 6 7 8 有重复时并列排名，最终排名不连续
 -- MySQL 5.7
 SET @i := 0;
 SET @j := 0;
 SET @p := 0;
 SET @q := 0;
 SELECT
       t1.SId,
       t1.CId,
       t1.score,
       @j := @j + 1,
       @p := t1.score,
       if(@p=@q,@i,@i := @j) as '排名',
       @q :=@p
 from (
     SELECT  SId
         ,CId
         ,score
     from SC
     order by score desc
     ) t1;
     
 -- mysql 8.0以上
 -- rank()函数
 select rank() over (order by t1.score desc) as '排名',
        t1.SId,
        t1.CId,
        t1.score
 from SC t1;

sql查询常用函数

GROUP_CONCAT 拼接函数

 -- 查询学生选课情况，课程编号升序，按照逗号拼接
 select sc.SId, group_concat(sc.CId order by sc.CId asc) as cid
 from SC sc
 group by sc.SId

ifnull函数和Case when函数

 -- ifnull函数，可以接收两个参数，如果第一个参数不为null，则返回第一个参数,否则返回第二个参数。
 
 select s.SId    as '学生编号',
        s.Sname  as '学生姓名',
        (case
             when S2.cid = '01' then '语文'
             when S2.cid = '02' then '数学'
             when S2.cid = '03' then '英语'
             else ''
            end) as '课程名称',
        ifnull(S2.score, '缺考')
 from Student s
          left join SC S2 on s.SId = S2.SId

with as 临时表使用

 -- with as临时表用法
 -- 如下使用with as改写
 select s.SId, s.Sname, t1.avgScore
 from Student s
          join (select sc.sid, count(1) as courseNum, avg(sc.score) as avgScore
                from (select * from SC sc where sc.score < 60) sc
                group by sc.sid
                having (count(1)) >= 2) t1
               on s.SId = t1.SId;
 
 with t1 as (select sc.sid, count(1) as courseNum, avg(sc.score) as avgScore
             from (select * from SC sc where sc.score < 60) sc
             group by sc.sid
             having (count(1)) >= 2)
 select s.SId, s.Sname, t1.avgScore
 from Student s join t1 on s.SId = t1.SId;

存储过程

 -- 数据表准备
 drop table if exists `mystudy`.`t_model`;
 Create table `mystudy`.`t_model`
 (
     `id`        bigint NOT NULL AUTO_INCREMENT COMMENT '自增主键',
     `uid`       bigint COMMENT '业务主键',
     `modelid`   varchar(50) COMMENT '字符主键',
     `modelname` varchar(50) COMMENT '名称',
     `desc`      varchar(50) COMMENT '描述',
     primary key (`id`),
     UNIQUE index `uid_unique` (`uid`),
     key `modelid_index` (`modelid`) USING BTREE
 ) ENGINE = InnoDB
   charset = utf8
   collate = utf8_bin;
   
 -- 1.创建存储过程
 create procedure xunhuancharu()
 begin
     DECLARE i INT DEFAULT 1;
     WHILE (i <= 10)
         DO
             -- -- 函数内容开始
             insert into t_model (uid,modelid,modelname,`desc`) value (i,CONCAT('id20170831',i),CONCAT('name',i),'desc');
             -- -- 函数内容结束
             SET i = i + 1;
         END WHILE;
 end;
 
 -- 2.调用存储过程
 call xunhuancharu();
 
 -- 3.创建完了记得删除储存过程，避免不必要的bug
 drop procedure xunhuancharu;

SQL调优实战

数据环境准备

 -- 示例表
 CREATE TABLE `employees` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
   `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
   `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
   `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
   PRIMARY KEY (`id`),
   KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';
  
 INSERT INTO employees(name,age,position,hire_time) VALUES('itwxe',22,'manager',NOW());
 INSERT INTO employees(name,age,position,hire_time) VALUES('weiwei', 23,'test',NOW());
 INSERT INTO employees(name,age,position,hire_time) VALUES('leilei',23,'dev',NOW());
  
 -- 插入10w条测试数据
 drop procedure if exists insert_employees; 
 delimiter $$
 create procedure insert_employees()        
 begin
   declare i int;                    
   set i = 1;                          
   while(i <= 100000)do                 
     insert into employees(name, age, position) values(CONCAT('itwxe', i), rand() * 42 + 18, 'dev');  
     set i = i + 1;                       
   end while;
 end$$
 delimiter ;
 call insert_employees();

1. 尽量全值匹配

 -- 上述表的索引字段是name, age, position，查询时尽量全值匹配，例如：
 explain select * from employees where name = 'itwxe';
 explain select * from employees where name = 'itwxe' and age = 22;
 explain select * from employees where name = 'itwxe' and age = 22 and position = 'manager';

2. 最左前缀原则

 -- 在使用联合索引的时候要特别注意最左前缀原则，即查询从联合索引的最左前列开始并且不跳过索引中的列。
 -- 索引列是：name, age, position
 -- 满足最左前缀原则且不跳过中间索引列
 explain select * from employees where name = 'itwxe' and age = '18';
 
 -- 满足最左前缀原则，使用的索引字段是name
 explain select * from employees where name = 'itwxe' and position = 'manager';
 
 -- 不满足最左前缀原则，没有使用索引
 explain select * from employees where position = 'manager';

3. 不在索引列上做任何操作(计算、函数、(自动/手动)类型转换)，会导致索引失效而转向全表扫描

 -- 需要注意的是这里说的索引列任何操作(计算、函数、(自动/手动)类型转换)不做操作指的是 where 条件之后的，而不是查询结果字段里面的。
 -- 不对where条件后的索引列做任何操作，使用索引
 explain select * from employees where name = 'weiwei';
 
 -- 对where条件后的索引列做left操作，索引失效
 explain select * from employees where left(name,6) = 'weiwei';

4. 存储引擎不能使用索引中范围条件右边的列

 -- 满足使用索引条件，name, age, position三个字段都有使用到索引
 explain select * from employees where name = 'itwxe' and age = 22 and position = 'manager';
 
 -- 因为age加了范围判断，所以后面的列position索引失效，只是name, age两个字段有使用到索引
 explain select * from employees where name = 'itwxe' and age > 22 and position = 'manager';

5. 尽量使用覆盖索引，减少select *语句

 -- 尽量使用覆盖索引，即只访问索引列，减少不必要的select *语句。
 -- 如果结果只需要name,age字段，则只查询这2个，避免使用select *语句
 explain select name,age from employees where name = 'itwxe' and age = '18';

6. MySQL 在使用不等于( != 或者 <> )，not in，not exists 的时候无法使用索引会导致全表扫描

 -- 使用不等于( != 或者 <> )，not in，not exists 的时候，索引失效
 explain select * from employees where name != 'itwxe';
 
 explain select * from employees where name not in('itwxe');

7. is null 和 is not null 一般情况下也无法使用索引

 -- is null 和 is not null 一般情况下也会导致索引失效
 explain select * from employees where name is null;
 explain select * from employees where name is not null;

8. like 以通配符开头(’%itwxe…’) MySQL 索引失效会变成全表扫描操作

 -- like 以通配符开头(’%itwxe…’)导致索引失效
 -- 通配符在后面，走索引
 explain select * from employees where name like 'wei%';
 
 -- 以通配符开头，索引失效
 explain select * from employees where name like '%wei';

9. 类型不匹配 MySQL 自动转型导致索引失效(隐式类型转换)

10. 少用 or 或 in ，用它查询时，MySQL 不一定使用索引

索引使用总结

posted @ 2022-11-14 15:00 moonlighter 阅读(16) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

moonlighter

SQL强化练习

SQL实练

数据环境准备

数据表创建

数据初始化

练习题

常规题

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

2.查询同时存在" 01 “课程和” 02 "课程的情况

3.查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )

4.查询不存在" 01 “课程但存在” 02 "课程的情况

5.查询平均成绩于大于 60 分的同学的学生编号和学生姓名和平均成绩

6.查询在 SC 表存在成绩的学生信息

7.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩，没成绩的显示为 null

8.查询「李」姓老师的数量

9.查询学过「张三」老师授课的同学的信息

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

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

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

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

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

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

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

18.按各科平均成绩进行排序，并显示排名

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

22.统计各科成绩各分数段人数：课程编号，课程名称，[100-85），[85-70），[70-60），[60-0）及所占百分

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

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

25.查询出只选修两门课程的学生学号和姓名

26.查询男生、女生人数

28.查询同名同性学生名单，并统计同名同性人数

29.查询 1990 年出生的学生名单

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

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

32.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

33.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

三种常见的排名

ROW_NUMBER：可以做为序号

DENSE_RANK

RANK

sql查询常用函数

GROUP_CONCAT 拼接函数

ifnull函数和Case when函数

with as 临时表使用

存储过程

SQL调优实战

数据环境准备

1. 尽量全值匹配

2. 最左前缀原则

3. 不在索引列上做任何操作(计算、函数、(自动/手动)类型转换)，会导致索引失效而转向全表扫描

4. 存储引擎不能使用索引中范围条件右边的列

5. 尽量使用覆盖索引，减少select *语句

6. MySQL 在使用不等于( != 或者 <> )，not in，not exists 的时候无法使用索引会导致全表扫描

7. is null 和 is not null 一般情况下也无法使用索引

8. like 以通配符开头(’%itwxe…’) MySQL 索引失效会变成全表扫描操作

9. 类型不匹配 MySQL 自动转型导致索引失效(隐式类型转换)

10. 少用 or 或 in ，用它查询时，MySQL 不一定使用索引

索引使用总结

公告

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