select count(*) from返回的类型_SQL面试题类型整理
1.简单查询
查找练习(学生)
- 查询姓猴的学生名单
select *
from student
where 姓名 like '猴%';
- 查询名字最后一个字是猴的学生名单
select *
from student
where 姓名 like '%猴';
- 查询名字中带有猴的学生名单
select *
from student
where 姓名 like '%猴%';
2.汇总分析
汇总练习
- 查询程序编号为‘0002’的总成绩
select sum(成绩)
from score
where 课程号 = '0002';
- 查询选了课程的学生人数
select count(distinct 学号) as 学生人数
from score;
分组练习
- 查询每科成绩的最高最低分
select 课程号,max(成绩) as 最高分,min(成绩) as 最低分
from score
group by 课程号;
- 查询每门课程被选修的学生数
select 课程号,count(学号)
from score
group by 课程号;
- 查询男生、女生人数
select 性别,count(*)
from student
group by 性别;
分组结果带条件练习
- 查询平均成绩大于60分学生的学号和平均成绩
select 学号,avg(成绩)
from score
group by 学号
having avg(成绩)>60;
- 查询至少选修两门课程的学生学号
select 学号,count(课程号) as 选修课程数目
from score
group by 学号
having count(课程号)>=2;
- 查询同名同姓学生名单并统计同名人数
select 姓名,count(姓名) as 人数
from student
group by 姓名
having count(姓名)>=2;
- 查询两门以上不及格课程的同学的学号,以及不及格课程的平均成绩
select 学号,avg(成绩) as 平均成绩
from score
where 成绩< 60
group by 学号
having count(课程号)>2;
3.复杂查询
- 查询所有课程成绩小于60分学生的学号、姓名
select 学号,姓名
from student where 学号 in(
select 学号
from score
group by 学号
having max(成绩)<60
);
- topN问题(每个类别用户最喜欢的产品是哪个,点击最多的五个商品是什么等)
分组取最大/小值案例:按课程号分组取成绩最大值所在行的数据
select *
from score as a
where 成绩 = (
select max(成绩) --select min(成绩)
from score as b
where a.课程号= b.课程号
group by 课程号);
每组最大的N条记录案例:查询各科成绩前两名的记录
第一步:查出有哪些组
select 课程号,max(成绩) as 最大成绩
from score
group by 课程号;
第2步:先使用order by子句按成绩降序排序(desc),然后使用limt子句返回topN(对应这个问题返回的成绩前两名)
-- 课程号'0001' 这一组里成绩前2名(其他组也是一样的代码只更改课程号)
select *
from score
where 课程号 = '0001'
order by 成绩 desc
limit 2;
第3步,使用union all 将每组选出的数据合并到一起
(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);
前面我们使用order by子句按某个列降序排序(desc)得到的是每组最大的N个记录。如果想要达到每组最小的N个记录,将order by子句按某个列升序排序(asc)即可。
4.多表查询
例题:
我们要找的是不在表里的数据,也就是在表A里的数据,但是不在表B里的数据。
select a.姓名 as 不近视的学生名单
from 学生表 as a left join 近视学生表 as b on a.学号=b.学生学号
where b.学生学号 is null;
例题:
select a.Name as Customers
from Customers as a left join Orders as b on a.Id=b.CustomerId
where b.CustomerId is null;
联结练习
例题:查询平均成绩大于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;
例题:查询学生的选课情况:学号,姓名,课程号,课程名称
select a.学号, a.姓名, c.课程号,c.课程名称
from student a inner join score b on a.学号=b.学号
inner join course c on b.课程号=c.课程号;
例题:使用分段[100-85],[85-70],[70-60],[‹60]来统计各科成绩,分别统计:各分数段人数,课程号和课程名称
-- 考察case表达式
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.课程名称;
例题:
这类题目属于行列如何互换,解题思路如下:
【解答】
1)第1步,使用常量列输出目标表的结构
可以看到查询结果已经和目标表非常接近了
select 学号,'课程号0001','课程号0002','课程号0003'
from score;
2)第2步,使用case表达式,替换常量列为对应的成绩
select 学号,
(case 课程号 when '0001' then 成绩 else 0 end) as '课程号0001',
(case 课程号 when '0002' then 成绩 else 0 end) as '课程号0002',
(case 课程号 when '0003' then 成绩 else 0 end) as '课程号0003'
from score;
在这个查询结果中,每一行表示了某个学生某一门课程的成绩。比如第一行是'学号0001'选修'课程号00001'的成绩,而其他两列的'课程号0002'和'课程号0003'成绩为0。
每个学生选修某门课程的成绩在下图的每个方块内。我们可以通过分组,取出每门课程的成绩。
3)第3步,分组
分组,并使用最大值函数max取出上图每个方块里的最大值
select 学号,
max(case 课程号 when '0001' then 成绩 else 0 end) as '课程号0001',
max(case 课程号 when '0002' then 成绩 else 0 end) as '课程号0002',
max(case 课程号 when '0003' then 成绩 else 0 end) as '课程号0003'
from score
group by 学号;
这样我们就得到了目标表(行列互换)
5.提高SQL查询的效率
【题目】
我们公司的数据量非常大,需要的不仅仅是提取数据,要了解SQL方案优化的。一般在写SQL时需要注意哪些问题,可以提高查询的效率?
【解题思路】
数据量大的情况下,不同的SQL语句,消耗的时间相差很大。按下面方法可以提高查询的效果。
1. select子句中尽量避免使用*
select子句中,*是选择全部数据的意思。比如语句:“select * from 成绩表”,意思是选择成绩表中所有列的数据。
在我们平时的练习中,往往没有那么多数据,所以很多同学会图方便使用*。而在处理公司事务时,动辄十万、百万,甚至上千万的数据,这个时候再用*,那么接下来的几分钟就只能看着电脑屏幕发呆了。
所以,在我们平常的练习中,就要养成好的习惯,最后需要哪些列的数据,就提取哪些列的数据。尽量少用*来获取数据。
另外,如果select * 用于多表联结,会造成更大的成本开销。
2. where子句比较符号左侧避免函数
尽量避免在where条件子句中,比较符号的左侧出现表达式、函数等操作。因为这会导致数据库引擎进行全表扫描,从而增加运行时间。
举个例子,下图是10名学生的成绩表,老师突然发现因为参考答案出错,给所有人都少加了5分,现在需要查询:给每人加5分后,成绩依然在90分以上的同学的学号。
按照题目的思路直接书写,“给每人加5分后,成绩90分以上”的条件很多人会这样写:
where 成绩 + 5 › 90 (表达式在比较符号的左侧)
优化方法:
where 成绩 › 90 – 5(表达式在比较符号的右侧)
所以,为了提高效率,where子句中遇到函数或加减乘除的运算,应当将其移到比较符号的右侧。
3. 尽量避免使用in和not in
in和not in也会导致数据库进行全表搜索,增加运行时间。
比如,我想看看第8、9个人的学号和成绩,大多数同学会用这个语句:
select 学号, 成绩
from 成绩表
where 学号 in (8, 9)
这一类语句,优化方法如下:
select 学号, 成绩
from 成绩表
where 学号 between 8 and 9
4. 尽量避免使用or
or同样会导致数据库进项全表搜索。在工作中,如果你只想用or从几十万语句中取几条出来,是非常划不来的,怎么办呢?下面的方法可替代or。
从成绩表中选出成绩是是88分或89分学生的学号:
select 学号
from 成绩表
where 成绩 = 88 or 成绩 = 89
优化后:
select 学号 from 成绩表 where 成绩 = 88
union
select 学号 from 成绩表 where 成绩 = 89
语句虽然变长了一点,但处理大量数据时,可以省下很多时间,是非常值得的。
5.使用limit子句限制返回的数据行数
如果前台只需要显示15行数据,而你的查询结果集返回了1万行,那么这适合最好使用limt子句来限制查询返回的数据行数。
【本题考点】
在面试中,当面试官提出这一类问题,按照上述的方法进行回答都是没有问题的,但不仅在面试中,平时练习就养成习惯是最好的。
大多数同学都会觉得“麻烦”、“不做也没有什么影响”,但是习惯总是慢慢养成的。
拥有好习惯,未来在工作中,面对不同的数据量,就可以游刃有余地选择不同的方法来降低完成时间,从而提升工作效率。