函数没有“as”子句;假定返回类型为 object。_SQL学习-面试题类型

本次回顾复习了四种查询类型，并对提高SQL查询效率做了简单的介绍。

题目为之前创建的4个表：学生表、成绩表、课程表、教室表

使用函数 insert into...values...

e.g.

insert into student(学号,姓名,出生日期,性别) 
values('0001' , '猴子' , '1989-01-01' , '男');

insert into student(学号,姓名,出生日期,性别) 
values('0002' , '猴子' , '1990-12-21' , '女');

insert into student(学号,姓名,出生日期,性别) 
values('0003' , '马云' , '1991-12-21' , '男');

insert into student(学号,姓名,出生日期,性别) 
values('0004' , '王思聪' , '1990-05-20' , '男');

一、简单查询

使用select、from、where、like、%等关键词

（1）查询姓“猴”的学生名单：

select *
from student
where 姓名 like '猴%';

（2）查询姓名中最后一个字是“猴”的学生名单：

select *
from student
where 姓名 like '%猴';

（3）查询姓名中带“猴”的学生名单：

select *
from student
where 姓名 like '%猴%';

（4）查询姓“孟”老师个数：

select count(教师姓名)
from teacher
where 教师姓名 like '孟%';

二、汇总分析

使用distinct、sum、group by、having、order by等关键词

（1）查询课程编号为‘0002’的总成绩：

select sum(成绩)
from score
where 课程号='0002';

（2）查询选了课程的学生人数：

select count(DISTINCT 学号) as 学生人数
from score;

（3）查询各科成绩最高和最低的分：

select 课程号,max(成绩) as 最高分, min(成绩) as 最低分
from score
group by 课程号;

（4）查询每门课程被选修的学生数：

select 课程号, count(学号) as 选课人数
from score
group by 课程号;

（5）查询男生、女生人数：

select 性别,count(*) as 人数
from student 
group by 性别;

（6）查询平均成绩大于60分学生的学号和平均成绩：

select 学号, avg(成绩) as 平均成绩
from score 
group by 学号
having avg(成绩)>60;

（7）查询至少选修两门课程的学生学号：

select 学号, count(课程号) as 选课数
from score 
group by 学号 
having count(课程号)>=2;

（8）查询同名同姓学生名单并统计同名人数：

select 姓名, count(学号) as 同名人数
from student 
group by 姓名 
having count(学号)>1;

（9）查询不及格的课程并按课程号从大到小排列：

select distinct 课程号
from score 
where 成绩>60
order by 课程号 desc;

（10）查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列：

select 课程号, avg(成绩) as 平均成绩
from score 
group by 课程号 
order by 平均成绩 asc, 课程号 desc;

（11）检索课程编号为“0004”且分数小于60的学生学号，结果按按分数降序排列：

select 学号
from score 
where 课程号='0004' and 成绩<60
order by 成绩 desc;

（12）统计每门课程的学生选修人数(超过2人的课程才统计)

要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序

select 课程号, count(学号) as 选修人数
from score
group by 课程号
having count(学号)>2
order by 选修人数 desc, 课程号 asc;

（13）查询两门以上不及格课程的同学的学号，以及不及格课程的平均成绩：

select 学号, avg(成绩) as 平均成绩
from score 
where 成绩 < 60
group by 学号 
having count(成绩)>2;

三、复杂查询

子查询问题

（1） 查询所有课程成绩小于60分学生的学号，姓名

select 学号,姓名 from student 
where 学号 in(
select 学号 from score group by 课程号 having max(成绩) < 60);

（2）查询没有学全所有课的学生的学号，姓名

select 学号,姓名 from student
where 学号 in ( 
select 学号 from score group by 学号 
having count(课程号) < (select count(课程号) from course));

（3）查询出只选修了两门课程的全部学生的学号和姓名

select 学号,姓名 from student
where 学号 in (
select 学号 from score group by 学号 having count(课程号) = 2);

（5）1990年出生的学生名单

select 学号,姓名 from student
where year(出生日期) = 1990;

面试题类型：topN 问题

分组取魅族最大值、最小值、每组最大的N条（topN）记录

关联子查询

（1）分组取每组最大值

e.g.

按课程分组取成绩最大值所在行的数据

select * from score as a 
where 成绩 = (
select max(成绩) from score as b where b.课程号 = a.课程号 group by 课程号);

（2）分组取每类最小值

e.g.

按课程号分组取成绩最小值所在行的数据

select * from score as a
where 成绩 = (
select min(成绩) from score as b where b.课程号 = a.课程号 group by 课程号);

（3）每组最大的N条记录

e.g.

查询各科成绩前两名的记录

(select * from score 
where 课程号 = '0001' 
order by 成绩 desc limit 2)
union all
(select * from score 
where 课程号 = '0002' 
order by 成绩 desc limit 2)
union all
(select * from score 
where 课程号 = '0003' 
order by 成绩 desc limit 2);

四、多表查询

inner join、left join、right join、full join等关键词

（1）查询所有学生的学号、姓名、选课数、总成绩

select a.学号,a.姓名,count(b.课程号) as 选课数,sum(b.成绩) as 总成绩
from student as a left join score as b
on a.学号 = b.学号
group by a.学号;

（2）查询平均成绩大于85的所有学生的学号、姓名和平均成绩

select a.学号,a.姓名, avg(b.成绩) as 平均成绩
from student as a left join score as b
on a.学号 = b.学号
group by a.学号
having avg(b.成绩)›85;

（3）查询学生的选课情况：学号，姓名，课程号，课程名称

select a.学号, a.姓名, c.课程号,c.课程名称
from student a inner join score b on a.学号=b.学号
inner join course c on b.课程号=c.课程号;

（4）查询出每门课程的及格人数和不及格人数（case when ... else ... end）

select 课程号,
sum(case when 成绩›=60 then 1 
  else 0 
    end) as 及格人数,
sum(case when 成绩 ‹  60 then 1 
  else 0 
    end) as 不及格人数
from score
group by 课程号;

（5）使用分段[100-85],[85-70],[70-60],[‹60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称

select a.课程号,b.课程名称,
sum(case when 成绩 between 85 and 100 
  then 1 else 0 end) as '[100-85]',
sum(case when 成绩 ›=70 and 成绩‹85 
  then 1 else 0 end) as '[85-70]',
sum(case when 成绩›=60 and 成绩‹70  
  then 1 else 0 end) as '[70-60]',
sum(case when 成绩‹60 then 1 else 0 end) as '[‹60]'
from score as a right join course as b 
on a.课程号=b.课程号
group by a.课程号,b.课程名称;

（6）查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名

select a.学号,a.姓名
from student as a inner join score as b on a.学号=b.学号
where b.课程号='0003' and b.成绩›80;

五、提高SQL查询效率

平时需要养成SQL查询语句书写的习惯：

1. select 子句中尽量避免使用*

（代表选择查询全部数据，且select *用于多表联结时成本巨大）

2.where子句比较符号的左边 尽量避免使用函数

（如果where子句的左边出现表达式或函数，会导致数据库引擎进行全表扫描，从而增加运行时间。）

优化方案：为了提高效率，where子句中的函数或表达式尽量放在比较符右侧

e.g.

比如在成绩表中给每个人加5分，'成绩在90分以上'的条件查询
where 成绩 + 5 >90

优化方案：
where 成绩 > 90 -5

3.尽量避免使用in 和 not in

（in 和 not in会导致数据库进行全表搜索，增加运行时间）

e.g.

比如，查询第8，9个人的学号和成绩
select 学号,成绩 
from score 
where 学号 in（8,9);

优化方案如下：
select 学号,成绩 
from score 
where 学号 between 8 and 9;

4.尽量避免使用or

（or 会导致数据库引擎的全表搜索，增加运行时间）

e.g.

从成绩表中选出成绩是88分或者89分学生的学号
select 学号 
from score 
where 成绩 = 88 or 成绩 = 89;

优化方案如下：
select 学号 from score where 成绩 = 88
union 
select 学号 from score where 成绩 = 88;

5. 使用limit子句限制返回的数据行数

（如果前台只需要是多行的数据，不使用limit限制行数会返回上万行的情况下，应使用limit限制返回行数。）