SQL面试50题

CREATE TABLE `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`)
) CHARSET = utf8;


CREATE TABLE `Course`
(
    `c_id`   VARCHAR(20),
    `c_name` VARCHAR(20) NOT NULL DEFAULT '',
    `t_id`   VARCHAR(20) NOT NULL,
    PRIMARY KEY (`c_id`)
) CHARSET = utf8;


CREATE TABLE `Teacher`
(
    `t_id`   VARCHAR(20),
    `t_name` VARCHAR(20) NOT NULL DEFAULT '',
    PRIMARY KEY (`t_id`)
) CHARSET = utf8;


CREATE TABLE `Score`
(
    `s_id`    VARCHAR(20),
    `c_id`    VARCHAR(20),
    `s_score` INT(3),
    PRIMARY KEY (`s_id`, `c_id`)
) CHARSET = utf8;

在建立每张表的时候，需要设置CHARSET=utf8，否则出现中文字符时会报错；

>ERROR 1366 (HY000): Incorrect string value: '\xE8\xB5\xB5\xE9\x9B\xB7' for column 'Sname' at row 1

解决方案：分别查看数据库、数据表、报错列字段的字符集，错误就是由于三者不一致造成的，设置一致；一般为了支持中文，设置为utf8；

最后总结：（提示：以下内容都需要首先进入Mysql客户端）

查看mysql的字符集：show variables where Variable_name like '%char%';
查看某一个数据库字符集：show create database test;(注：test为数据库)
查看某一个数据表字符集：show create table Student;(注：Student为数据表)

修改mysql的字符集：

mysql> set character_set_client=utf8;
mysql> set character_set_connection=utf8;
mysql> set character_set_database=utf8;
mysql> set character_set_results=utf8;
mysql> set character_set_server=utf8;
mysql> set character_set_system=utf8;
mysql> set collation_connection=utf8;

修改数据库的字符集：

alter database test character set utf8;

修改数据表的字符集：

alter table Student character set utf8;

修改数据表字段的字符集：

alter table Student change Sname Sname varchar(10) character set utf-8;

1.查询课程编号为''01''的课程比''02''的课程成绩高的所有学生的学号（重点）。

select s1.s_id
from Score s1,
     Score s2
where s1.s_id = s2.s_id
  and s1.c_id = '01'
  and s2.c_id = '02'
  and s1.s_score > s2.s_score;

思路：我想要一张什么样的表。

s_id	c1_score	c2_score
学生id	课程c1的成绩	课程c2的成绩

它解：

select a.s_id 's_no', c.s_name 'name', a.s_score '01', b.s_score '02'
from (select s_id, c_id, s_score from Score where c_id = '01') as a
         inner join (select s_id, c_id, s_score from Score where c_id = '02') as b on a.s_id = b.s_id
         inner join (select s_name, s_id from Student) as c on a.s_id = c.s_id
where a.s_score > b.s_score

总结：自解中直接将两张原始表通过学生学号字段进行链接，笛卡尔积更大，计算量越大，效率越低。在它解中，首先分别查出01编号和02编号的表信息，然后再根据学生id字段进行链接，减少了无意义的链接，减少了计算量。因此，在以后进行表链接前，先思考能否在链接前进行一定的筛选再链接。

关键思想：构建子查询；

2.查询平均成绩大于60分的学生学号和平均成绩


select s_id, avg(s_score) 'avg_score'
from Score
group by s_id
having avg(s_score) > 60

建议：在使用group by的时候，尽量不要将group by中没有使用到的字段添加到select中去；即使成绩不报错，这个select也是没有意义的。

使用group by的时候，使用having进行分组的筛选。

3.查询所有学生的学号、姓名、选课数、总成绩

select a.s_id, a.s_name, count(c_id), sum(s_score)
from Student a,
     Score b
where a.s_id = b.s_id
group by a.s_id

思路：

1.做统计，group by

2.关联两张表，构建学号、姓名、课程编号、这门课的成绩

3.考虑将未选课的学生信息也拿出来，因此我们使用学生表左链接成绩表，即显示所有学生表信息，成绩表中不存在的关联信息，显示为null；

它解：

select a.s_id, a.s_name, count(b.c_is), sum(s_score)
from Student as a
         left join Score as b on a.s_id = b.s_id
group by s_id

疑问：

1.join on之后s_id只有一行了吗？group by后面的s_id字段是哪个表的s_id字段？

2.一般不select，group by未出现的统计字段。因此，我们将s_name也加入统计字段，结果不改变，但是更加严谨。

3.一般只有在group by中使用到的字段，我们才会放到select中进行查询。

它解：

select a.s_id, a.s_name, count(b.c_is), sum(s_score)
from Student as a
         left join Score as b on a.s_id = b.s_id
group by s_id, a.s_name

改进：不希望成绩列出现null值；加上case when 当成绩列出现NULL值时，设置为0，其他时候就是成绩本身；

select a.s_id, a.s_name, count(b.c_id), sum(case when b.s_score is NULL then 0 else b.s_score END)
from Student as a
         left join Score as b on a.s_id = b.s_id
group by s_id, a.s_name

总结：求和sum中用到case when是很常见的！

4.查询姓侯的老师的个数

select count(t_id)
from Teacher
where t_name like '候%'

注意：

姓张的老师名字：张%

包含张这个字的老师名字： %张%

不同的老师名字： count(distinct t_name)

5.查询没学过张三老师课的学生学号、姓名（重点）

查询张山老师课程编号

select c.c_id
from Course c,
     Teacher t
where c.t_id = t.t_id
  and t.t_name = '张三'

查询course表，按照c_id分类，查找出所有选该课程的s_id

select s_id
from Score,
     (select c.c_id
      from Course c,
           Teacher t
      where c.t_id = t.t_id
        and t.t_name = '张三') as a

group by a.c_id

（⚠️：不能查询未group by的字段）

自解：

select s_id, s_name
from Student
where s_id not in (select s1.s_id
                   from Score s1
                            inner join Student s2 on s1.s_id = s2.s_id,
                        (select c.c_id
                         from Course c,
                              Teacher t
                         where c.t_id = t.t_id
                           and t.t_name = '张三') as a
                   where a.c_id = s1.c_id)

它解：找出学过张三老师课程的同学，然后排除掉这些人，剩下就是没学过张三老师课程的人。

找出张三老师的教师编号
根据教师编号，我们去课程表里面找出张三老师所教课程的课程id
根据张三老师所教课程的课程id，我们去成绩表表中，找出所有考了该课程的学生id
根据选该课的学生id，我们查询学生表中学生id not in选课学生id的学生信息。

select s_id, s_name
from Student
where s_id not in (select s_id
                   from Score
                   where c_id = (select c_id from Course where t_id = (select t_id from Teacher where t_name = '张三')))

另解：通过表的关联来进行求解；

select s_id, s_name
from student
where s_id not in (select s_id
                   from Score s
                            inner join Course c on s.c_id = c.c_id
                            inner join Teacher t on c.t_id = t.t_id
                   where t.t_name = '张三')

注意：不能去成绩表中选择课程号!='02'，没有不代表不等于，因为这个同学选择了其他课程也会被选择出来；

6.查询学过张三老师所教的所有课程的同学的学号、姓名(重点)

自解：

select s1.s_id, s1.s_name
from Student s1
         inner join Score s2 on s1.s_id = s2.s_id
         inner join Course c on c.c_id = s2.c_id
         inner join Teacher t on c.t_id = t.t_id
where t.t_name = '张三'

它解：

7.查询学过编号为01的课程并且也学过编号为‘02’的课程的学生的学号、姓名（重点）

自解：

select a.s_id, s.s_name
from Student s,
     (select s_id from Score where c_id = '01') as a
         inner join (select s_id from Score where c_id = '02') as b on a.s_id = b.s_id
where s.s_id = a.s_id

它解：

select s_id, s_name
from Student
where s_id in (select a.s_id
               from (select s_id from Score where c_id = '01') as a (select s_id from Score where c_id='02')as b
               on a.s_id=b.s_id)

优化：最常用的方法就是子查询用零时表来代替；

通过select length(Null)，求Null值的长度；

8.查询课程编号为'02'的总成绩（不重点）

自解：

select sum(s_score), avg(s_score), count(s_score), count(distinct s_id) as total_score_02
from Score
where c_id = '02'

sum，avg，count函数的使用；
它解：

select c_id, sum(s_score), avg(s_score), count(s_score), count(distinct s_id) as total_score_02
from Score
group by c_id
having c_id = '02'

9.查询所有课程成绩小于60分的学生的学号、姓名；

自解：

select s.s_id, s.s_name
from Student s
where s.s_id in (select s_id from Score group by s_id having (max(s_score < 60)))

注意：等号和in的区别，子查询的s_id查询出来是一个列表，前面的是s_id 是in后面的查询结果；

它解：成绩小于60分的课程数和这个学生选的总课程数相等，即该生选的所有课程都小于60分；

select a.s_id, t.s_name
from (select s_id, count(c_id) as cnt from Score where s_score < 60 group by s_id) as a
         inner join (select s_id, count(c_id) as cnt from Score group by s_id) as b on a.s_id = b.s_id
         inner join Student as t on a.s_id = t.s_id
where a.cnt = b.cnt

10.查询没有学全所有课的学生的学号、姓名（重点）

自解1：只要在学生表中查找，学生id不在选课数等于课程数的学生id列表中就可以了

select s_id, s_name
from Student
where s_id not in (select s_id from Score group by s_id having (count(c_id)) = (select count(c_id) from Course))

自解2：错误，子查询没有考虑未选课的学生；

select s_id, s_name
from Student
where s_id in (select s_id from Score group by s_id having (count(c_id)) != (select count(c_id) from Course))

注意：万一该同学补考了，会存在一门课对应多个成绩的情况，因此最好加上distinct；

select s_id
from Score
group by s_id
having count(distinct c_id) < 3

它解：错误，漏掉了一门课都没有选的人！

select s_id, s_name
from Student
where s_id in
      (select s_id from Score group by s_id having count(distinct c_id) < (select count(distinct c_id) from Course))

问题：没有考虑没有选课的学生！

它解：采用左链接，左边的数据永远都会出现。若链接的右边数据不存在，则左边的数据依然会出现，右边的数据就为Null select from Student as st left join Score as sc on st.s_id=sc.s_id

拓展：在b表中插入a表中没有的数据；

insert into b
    (select *
     from a
              left join b on a.id = b.id
     where b.id is null
    )

11.查询至少有一门课与学号为'01'的学生所学课程相同的学生的学号和姓名（重点）

自解：

select s_id, s_name
from Student
where s_id in
      (select distinct s_id from Score where c_id in (select c_id from Score where s_id = '01') and s_id != '01')

首先查看'01'学生选了哪些课程

它解：

inner join
select a.s_id, a.s_name
from Student as a
         inner join(select distinct s_id
                    from Score
                    where c_id in (select c_id from Score where s_id = '01') and s_id != '01') as b on a.s_id = b.s_id

推荐：在表的数据较大的时候，建议用inner join，而不使用in。实际生产中更常使用left join和inner join；join比in效率更高；

12.查询和'01'号同学所学课程完全相同的其他同学的学号(重点)

自解：使用count ❌ 只选出了课程数与01同学所修课程相同的同学，没考虑课程不同但数目相同的情况；

select s_id
from Score
group by s_id
having count(distinct c_id) = (select count(distinct c_id) from Score where s_id = '01')

注意：having后面不能直接跟字段，having与where的区别！

自解：❌ in只选出了至少有一门课程与01同学所修课程相同的同学；

select distinct s_id
from Score
where s_id in (select s_id
               from (select s_id
                     from Score
                     group by s_id
                     having count(distinct c_id) =
                            (select count(distinct c_id) as a from Score where s_id = '01')) as a)
  and c_id in (select c_id from Score where s_id = '01')
  and s_id != '01'

1.选出学了'01'同学课程列表中课程的学生记录

2.然后计算同学的选课数目是否为3

它解：❌ 存在问题：in 只要有一个in就会被筛选出来，所有第一层过滤出来的是学了01同学所修课程中任意一门课程的同学，后面再过滤课程数相同，那么同学2就会被选出来。但是，很明显同学2并不满足条件。

select s_id
from Score
where c_id in (select c_id from Score where s_id = '01 )
and s_id!='01' group by s_id having count(distinct c_id) =(select count(distinct c_id) from Score where s_id='01')

同学1	同学2	同学3	同学4
a	a	a	a
b	b	b	b
b	d	c	c
			d

上面，只有同学3和同学1修了完全相同的课程，同学2课程类别不完全相同，同学4课程数目不同;正确解法：☑️ -- 查找出与01同学选课数目相同的同学

-- 查找出与01同学所修课程不完全相同的同学

select *
from Student
where s_id in (select s_id
               from Score
               group by s_id
               having count(distinct c_id) = (select count(distinct c_id) from Score where s_id = ''01'')
                  and s_id != ''01'')
  and s_id not in (select s_id from Score where c_id not in (select c_id from Score where s_id = ''01''))

01号同学学了01，02，03号课程

1.选出所学的课不在(01,02,03)的同学 -排除；

2.剩下的同学肯定选了 01，02，03中的某几门课，判断所学的课程数是否等于3；

13.查询没学过‘张三’老师教授的任一门课程的学生姓名和47题一样（重点，能做出来）

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

问题：select count(s_score<60) from Score为什么统计处了所有的记录，判断条件为什么无效？

答：select count(0) 答案居然是1，卧槽！count(null)时返回的是0 count(非null)返回是1，0就是非null，卧槽！

自解：

select s.s_id, s.s_name, b.avg_score
from Student s
         inner join (select s_id, avg(s_score) avg_score
                     from Score
                     group by s_id
                     having count(case when s_score < 60 then 1 else null end) >= 2) as b on s.s_id = b.s_id

它解：先查询出所有不及格成绩的记录，然后对这些记录按照学号进行分组，筛选出组内学号大于等于2的学号即有两门及两门以上课程不及格的同学。

select a.s_id, a.s_name, b.avg_score
from Student as a
         inner join (select s_id, avg(s_score) avg_score
                     from Score
                     where s_score < 60
                     group by s_id
                     having count(distinct c_id) >= 2) as b on a.s_id = b.s_id

注意：先筛选，再分组，再筛选；比直接分组筛选效率更高；

select a.s_id, a.s_name, avg(s_score)
from Student as a
         inner join Score as b on a.s_id = b.s_id and a.s_id in (select s_id
                                                                 from Score
                                                                 where s_score < 60
                                                                 group by s_id
                                                                 having count(distinct c_id) >= 2)
group by a.s_id, a.s_name

思路：首先找出有两门课程成绩不及格的学生学号，然后将学生表和成绩表连接起来，进行筛选；

16.检索‘01’课程分数小于60，按分数降序排列的学生信息；

自解：

select *
from Student as a
where a.s_id in (select s_id from Score where c_id = '01' and s_score < 60 order by s_score desc)

它解：采用inner join

select t.*, s.c_id, s.s_score
from Student t
         inner join Score s on t.s_id = s.s_id
where s.c_id = '01'
  and s.s_score < 60
order by s_score desc

降序 desc 升序 asc

注意在使用innner join时理清楚表之间的关系，是一对多还是多对多等等；

17.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩（重点）

自解：

select s.s_id, s.c_id, s.s_score, b.avg_score
from Score s,
     (select s_id, avg(s_score) avg_score from Score group by s_id) as b
where s.s_id = b.s_id
order by b.avg_score desc, s_id

它解：问题：显示结果不佳；

select s.s_id, s.c_id, s.s_score, b.avg_s_score
from Score as s
         inner join (select s_id, avg(s_score) as avg_s_score from Score group by s_id) as b on s.s_id = b.s_id
order by b.avg_s_score desc

理想显示结果：

学生学号	语文	数学	英语	平均成绩
01	80	98	97	xx

它解：理想显示结果

select s_id                                                  '学号',
       Max(case when c_id = '01' then s_score else Null end) '语文',
       Max(case when c_id = '02' then s_score else Null end) '数学',
       Max(case when c_id = '03' then s_score else Null end) '英语',
       Max(case when c_id = '04' then s_score else Null end) '化学',
       avg(s_score)
from Score
group by s_id
order by avg(s_score) desc

注意：select 只能查询group by出现的或者统计函数出现的字段，因此在这里我们使用max函数构造统计函数字段，max函数其实没有用处，只是为了能够取到各门课程的成绩值。这里使用min，avg,sum等函数取值也是可以的，因为这里一个人一门课程只有一个成绩。

18.查询各科成绩最高分、最低分和平均分：以如下形式显示：

课程ID、课程name、最高分、最低分、平均分、及格率、中等率、优良率、优秀率

--及格为>=60、中等为：70-80、优良为80-90、优秀为>=90

自解：

select c.c_id                                                                           '课程ID',
       c_name                                                                           '课程name',
       max(s_score)                                                                     '最高分',
       min(s_score)                                                                     '最低分',
       avg(s_score)                                                                     '平均分',
       count(case when s_score >= 60 then 0 else null end) / count(*)                   '及格率',
       count(case when s_score >= 70 and s_score <= 80 then 0 else null end) / count(*) '中等率',
       count(case when s_score >= 80 and s_score <= 90 then 0 else null end) / count(*) '优良率',
       count(case when s_score >= 90 then 0 else null end) / count(*)                   '优秀率'
from Course c,
     Score s
where c.c_id = s.c_id
group by c.c_id, c_name;

它解：

select s.c_id,
       c.c_name,
       max(s.s_score),
       min(s.s_score),
       avg(s.s_score),
       sum(case when s.s_score >= 60 then 1 else 0 end) / count(s_id)                    '及格率',
       sum(case when s.s_score >= 70 and s.s_score < 80 then 1 else 0 end) / count(s_id) '中等率',
       sum(case when s.s_score >= 80 and s.s_score < 90 then 1 else 0 end) / count(s_id) '优良率',
       sum(case when s.s_score >= 90 then 1 else 0 end) / count(s_id)                    '优秀率'
from Score as s
         inner join Course as c on s.c_id = c.c_id
group by c_id

注意：这里使用sum的原因是，当满足条件为 1，则可以通过sum计数；count中，count(0)=1，只有count(null)=0，因此，使用count函数需要else null 才能不计数；

19.按各科成绩进行排序，并显示排名(重点 row_number)

窗口函数：

row_number() over（order by 列）

简单来说row_number()从1开始，为每一条分组记录返回一个数字。这里的row_number() over (order by slh desc)是把slh列降序，再为降序以后的每条slh记录返回一个序号。

row_number() over (partition by col1 order by col2)表示根据col1分组，在分组内部根据col2排序，而此函数计算的值就表示每组内部排序后的顺序编号，是连续且唯一的。

rank()
dense_rank()

排名row_number()	排名dense_rank()	排名rank()
1	1	1
2	2	2
3	2	2
4	3	4
没有重复值的排序记录相同也是不重复的，可以进行分页使用；	连续排序	跳跃排序符合成绩排法！

select *, rank() over (partition by c_id order by s_score desc) 'rank'
from Score

窗口函数最大的特点就是返回的结果集不发生变化，而且可以返回序号；group by只会返回统计量，不会返回分组后每条记录的值，这是其与group by最大的区别；

mysql数据库没有实现窗口函数，因此需要使用其他手段来解决这个问题；

完成对mysql数据库查询出的结果添加序号列”的需求。

1.row_number()函数效果实现

select s_id,c_id,s_score, (@curRank:=@curRank+1) as 'rank' from Score, (select @curRank:=0) as a order by s_score desc

2.dense_rank()函数效果实现

select s_id,c_id,s_score, if(@prevRank=s_score,@curRank,@curRank:=@curRank+1) as 'rank', @prevRank:=s_score from Score, (select @curRank:=0,@prevRank:=null) as a order by s_score desc

3.rank()函数效果实现

select b.s_id, b.c_id, b.s_score, b.rank from (select s_id, c_id, s_score, @curRank := if(@prevRank = s_score, @curRank, @incRank) as 'Rank', @incRank := @incRank + 1, @prevRank := s_score from Score, (select @curRank := 0, @prevRank := null, @incRank := 1) as a order by s_score desc) as b

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

select s_id '学号', sum(s_score) '总成绩'
from Score
group by s_id
order by 总成绩 Desc

select s_id '学号', sum(s_score) '总成绩'
from Score
group by s_id
order by sum(s_score) Desc

注意：字段别名，as 和引号都是别名的意思，不能同时使用；

区别名的时候加引号，order by的时候不需要加引号；

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

自解：

select s.c_id, c.t_id, t.t_name, c.c_name, avg(s_score) 'avg_score'
from Score s
         inner join Course c on s.c_id = c.c_id
         left join Teacher t on c.t_id = t.t_id
group by s.c_id, c.t_id, c.c_name, t.t_name
order by avg_score desc

它解： 1.以课程为主体，求平均分 2.以老师为主体求平均分

select t.t_id, t.t_name, avg(sc.s_score) as avg_score
from Score as sc
         inner join Course as c on sc.c_id = c.c_id
         inner join Teacher as t on c.t_id = t.t_id
group by t.t_id, t.t_name
order by avg_score DESC

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

select *
from (select st.s_id,
             st.s_name,
             st.s_birth,
             st.s_sex,
             c_id,
             s_score,
             row_number() over (partition by c_id order by s_score desc) m
      from Score sc
               inner join Student st on sc.s_id = st.s_id) a
where m in (2, 3)

自解：

select s_id,
       c_id,
       s_score,
       @courRank := c_id,
       case
           when @courRank = c_id then @curRank := if(@prevRank = s_score, @curRank, @incRank)
           else (@curRank := 0 and @incRank := 1 and @courRank := c_id) end as 'rank',
       @incRank := @incRank + 1,
       @prevRank := s_score
from Score,
     (select @curRank := 0, @prevRank := null, @incRank := 1, @courRank := null) as r
order by c_id, s_score desc

23.使用分段【100-85)、【85-70)、【70-60)、【<60】来统计各科成绩，分别统计各分数段人数：课程ID和课程名称；（重点）

自解：

select s.c_id,
       c.c_name,
       sum(case when s_score >= 85 then 1 else 0 end)                  as '85-100分人数',
       sum(case when s_score >= 70 and s_score < 85 then 1 else 0 end) as '70-85分人数',
       sum(case when s_score >= 60 and s_score < 70 then 1 else 0 end) as '60-70分人数',
       sum(case when s_score < 60 then 1 else 0 end)                   as '60分以下人数'
from Score s
         inner join Course c on s.c_id = c.c_id
group by c.c_name, s.c_id

它解：

select c.c_id,
       c.c_name,
       sum(case when sc.s_score <= 100 and sc.s_score > 85 then 1 else 0 end) as '[100-85]',
       sum(case when sc.s_score <= 85 and sc.s_score > 70 then 1 else 0 end)  as '[85-70]',
       sum(case when sc.s_score <= 70 and sc.s_score > 60 then 1 else 0 end)  as '[70-60]',
       sum(case when sc.s_score <= 60 then 1 else 0 end)                      as '[<=60]'
from Score as sc
         inner join Course as c on sc.c_id = c.c_id gtoup by c.c_id,c.c_name

注意：sum与count进行统计的区别，count统计时，count(0)=1；count（null）=0

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

select s_id, avg(s_score), row_number() over (order by avg(s_score) desc)
from Score
order by s_id

#不想分组，over (order by avg(s_score)

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

select c.c_id, c.c_name, count(distinct sc.s_id)
from Score as sc
         inner join Course as c on sc.c_id = c.c_id
group by c.c_id, c.c_name

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

select sc.s_id, st.s_name
from Score sc
         inner join Student st on sc.s_id = st.s_id
group by sc.s_id
having count(distinct sc.c_id) = 2

子句查询：

select s_id.s_name
from Student
where s_id in (select s_id from Score group by s_id having count(distinct c_id) = 2)

in在数据量较大时效率更低，表连接在数据量较大时效率更高；

28.查询男生、女生人数

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

select s_sex ,count(s_id) from Student group by s_sex

29.查询名字中含有“风”字的学生信息

select *
from Student
where s_name like '%风%' -- 含有风 where s_name like '%风'

-- 以风字结尾的姓名 where s_name like '风%' -- 以风字开头的姓名

like进行模糊查询，like %风%表示查询名字中带有风，like% 风表示

31.查询1990 年出生的学生名单（重点）

时间函数：

year

month

day

select *
from Student
where year(s_birth) = '1990'

时间格式的format转换；

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

select sc.s_id '学号', st.s_name '姓名', avg(sc.s_score) '平均成绩'
from Score sc
         inner join Student st on sc.s_id = st.s_id
group by sc.s_id
having 平均成绩 > 85

group by 用在where语句后面，先用where把常规条件筛选之后，再分组；

分组后，对分组结果进行筛选，使用having；

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

select s.c_id, c.c_name, avg(s.s_score) as avg_score
from Score as s
         inner join Course as c on s.c_id = c.c_id
group by s.c_id
order by avg_score asc, s.c_id desc

34.查询课程名称为‘数学’，且分数低于60的学生姓名和分数；

select st.s_name, c.c_name, sc.s_score
from Score as sc
         inner join Student st on sc.s_id = st.s_id
         inner join Course as c on c.c_id = sc.c_id
where c.c_name = '数学'
  and sc.s_score < 60

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

自解：

select st.s_id,
       st.s_name,
       max(case when S.c_id = '01' then s_score else null end) '语文',
       max(case when S.c_id = '02' then s_score else null end) '数学',
       max(case when S.c_id = '03' then s_score else null end) '英语',
       max(case when S.c_id = '04' then s_score else null end) '化学'
from Student st
         left join Score S on st.s_id = S.s_id
         left join Course C on S.c_id = C.c_id
group by st.s_id, st.s_name

⚠️：查询所有学生的课程及分数情况，为了保证查出所有学生的信息，在进行连接的时候，首先使用学生表左连接课程成绩表，然后这张大表再去左链接课程表；因为第二次连接时，未选课同学的s.c_id=null 与课程表连接时这条记录会被删除，还是不能保证查询出所有学生的选课信息！

select * from Score as sc inner join Course as c on sc.c_id = c.c_id inner join Student as st on st.s_id = sc.s_id

注意：此时表中有sc.c_id、c.c_id、st.s_id、sc.s_id这些都是内链接产生的重复字段，没有去掉重复字段；

⚠️：group by 与case when组合使用的问题：

在group by之后，进行case when后，它会逐条进行判断，它每条判断完成之后，只会返回第一条记录；

例如：

case when c_name='数学'

组内记录逐条进行判断，但是返回的结果是第一条记录的返回值；

1	语文	90	null
	数学	67	67
	英语	46	null

36.查询课程成绩在70分以上课程名称，分数和学生姓名；

自解：

select st.s_name, c.c_name, sc.s_score
from Student st
         inner join Score sc on st.s_id = sc.s_id
         inner join Course c on sc.c_id = c.c_id
where sc.s_score > 70

37.查询不及格的课程并按课程号从大到小排列

自解：

select st.s_id, st.s_name, c.c_name, s.s_score, s.c_id
from Score s
         inner join Course c on s.c_id = c.c_id
         inner join Student st on st.s_id = s.s_id
where s_score < 60
order by s.c_id desc

38、查询课程编号为03且课程成绩在80分以上的学生的学号和姓名（不重要）

select s.s_id, s.s_name, sc.c_id, c.c_name, sc.s_score
from Student s
         inner join Score sc on s.s_id = sc.s_id
         inner join Course c on c.c_id = sc.c_id
where sc.c_id = '03'
  and sc.s_score > 80

39、求每门课程的学生人数（不重要）

select sc.c_id, c.c_name, count(distinct s_id)
from Score sc
         inner join Course c on sc.c_id = c.c_id
group by sc.c_id

40、查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩（重要top）

select st.s_id, st.s_name, sc.s_score, c.c_id, c.c_name
from Student as st
         inner join Score sc on st.s_id = sc.s_id
         inner join Course C on sc.c_id = C.c_id
         inner join Teacher t on c.t_id = t.t_id
where t.t_name = '张三'
order by sc.s_score desc
limit 0,1

note：通过order by desc+limit 实现取出第一名的同学

*limit的用法简介：

limit 0,1

第一个参数代表起始位置，索引从0开始，第二个参数表示取几条；

limit 1

表示取1条；

41.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩（重点）

解释：是找出一个所有课程成绩都相同的学生的学生信息；

select s_id from ( select s_id, s_score from Score group by s_id, s_score ) as a group by s_id having count(s_id) = 1

有问题：某位同学只修了一门课程，不能说明该同学课程成绩都相同；因此，需要选出的同学所修的课程数必须大于2；

自解：

select s_id
from (select s_id, s_score from Score group by s_id, s_score having count(s_id) > 1) as a
group by s_id
having count(s_id) = 1

有个小问题：当Score中一个学生的同一门课程成绩不唯一时（存在补考成绩时），该解法不成立；会将同一个学生，同一门课程，初始考试成绩与补考成绩相同的同学筛选出来；

它解：

select s_id
from (select b.s_id, b.s_score
      from Score as b
               inner join (select s_id from Score group by s_id having count(distinct c_id) > 1) as a on a.s_id = b.s_id
      group by s_id, s_score) as c
group by s_id
having count(s_id) = 1

思路：

1.查询出所修课程数目大于1的学生学号，然后将这些学号和Score表做inner join取交集，此时得到的这张表也就是所修课程大于1的学生；

2.对于这些学生，按照学号和成绩进行分组，作为c表

3.对c表的记录按照s_id学号进行分组，统计分组组内只有一条记录的同学，说明此同学所修课程成绩相同；

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

自解：

select c_id '课程号', count(distinct s_id) '选修人数'
from Score
group by c_id
having count(distinct s_id) > 5
order by '选修人数' desc, '课程号' asc

它解：

44、检索至少选修两门课程的学生学号（不重要）

select s_id, count(distinct c_id)
from Score
group by s_id
having count(distinct c_id) >= 2

45、查询选修了全部课程的学生信息（重点划红线地方）

自解：

select s_id, count(distinct c_id) '所修课程总数'
from Score
group by s_id
having count(distinct c_id) = (select count(distinct c_id) from Course)

它解：

select s_id, count(distinct c_id)
from Score
group by s_id
having count(distinct c_id) = (select count(distinct c_id) from Course)

46、查询各学生的年龄（精确到月份）

DATEDIFF()函数返回两个日期之间的天数；

语法：DATEDIFF(date1,date2)

date1和date2参数是合法的日期或日期/时间表达式

select s_id, s_birth, floor(Datediff(now(), s_birth) / 365) '年龄'
from Student

取整函数：

round（）表示将值X四舍五入，无小数位；

round（X,D）表示将值X四舍五入为小数点后D位的数值，D为小数点后小数位数，若要保留X值小数点左边的D位，可将D设为负值；

floor():表示向下取整，只返回值X对整数部分，小数部分舍弃；

47、查询没学过“张三”老师讲授的任一门课程的学生姓名（还可以，自己写的，答案中没有）

自解：使用子查询

select s_id, s_name
from Student
where s_id not in (select distinct s.s_id
                   from Score s
                   where s.c_id in (select c_id
                                    from Teacher t
                                             inner join Course c on t.t_id = c.t_id
                                    where t.t_name = '张三'))

它解：使用inner join连接表选出学过张三老师课程的学生，然后排除这些学生就可以了；

select *
from Student
where s_id not in (select distinct s_id
                   from Score as sc
                            inner join Course as c on sc.c_id = c.c_id
                            inner join Teacher t on c.t_id = t.t_id
                   where t.t_name = '张三')

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

select s_id, avg(s_score)
from Score
group by s_id
having (sum(s_score < 60)) > 2

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

select * from Student where week(s_birth, 1) = week(date(now()), 1) + 1

⚠️：同样的一天，在不同的年份里，代表的周数不一样。

解决办法：方法：换算为同一年，再进行比较

week函数：查看给定日期属于哪个周数，week(date,mode);

week接受两个参数：

date是要获取周数的日期；
mode是一个可选参数，用于确定周数计算的逻辑。它允许你指定本周是从星期一还是从星期日开始，返回的周数英在0-52之间或0-53之间；

#首先substring取出月日，然后concat拼接年份； -- select concat('2020-', substring('1990-01-20',6,5)) select * from Student where week(concat('2020-', substring(s_birth,6,5)), 1) = week(date(now()), 1) + 1

同一年当中，日期连续，具有可比性；

⚠️：依然存在年末问题，比如12-30是第53周，1月1日是第0周，但是在日历中，其实这两天是同一周；

49.查询本月过生日的人

select * from Student where month(s_birth)=month(now())

month:函数，可以直接取到月份；

50.查询下个月过生日的同学

select * from Student where month(s_birth)=month(now())+1

date(now())：只拿到当前时间的年月日，没有时分秒；

⚠️：没有考虑12月份+1等于13月份的情况；改进做法如下：

select * from Student where case when month(now())=12 then month(s_birth)=1 else month(s_birth)=month(now())+1 end

升职加薪

关注

0
点赞
踩
5

收藏

觉得还不错? 一键收藏
1
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录