一、 简单查询

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 '孟%';

二、 汇总分析

1. 查询课程编号为‘0002’的总成绩
select 课程号,sum(成绩) as 总成绩
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 课程号
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(课程号)
3. 查询出只选修了两门课程的全部学生的学号和姓名
select 学号,姓名
from student
where 学号 in
(select 学号
from score
group by `学号`
having count(课程号)=2);
4. 查询1990年出生的学生名单
select *
from student
where year(出生日期)=1990;
5. 每门课程的最高分
select *
from score as S1
where 成绩 in (select max(成绩)
from score as S2
where S1.课程号=S2.课程号
group by 课程号)
6. 每门课的最低分
select *
from score as S1
where 成绩 in (select min(成绩)
from score as S2
where S1.课程号=S2.课程号
group by 课程号);
7. 查询各科成绩前两名的记录
(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);

四、 多表查询

1. 找出不近视的学生
select a.姓名
from 学生表 as a left join 近视学生表 as b
on a.学号=b.学号
where b.学号 is null;
2. 找出所有从不订购任何东西的客户
select NAME
from CUTSTOMERS as a left join ORDERS as b
on a.ID =b.CUTSTOMERSID
where b.ID is null;
3. 查询所有学生的学号、姓名、选课数、总成绩
select a.学号,a.姓名,count(b.课程号) as 选课数,sum(b.成绩) as 总成绩
from student as a left join score as b
on a.学号=b.学号
group by a.学号,a.姓名;
4. 查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select a.学号,a.姓名,avg(b.成绩) as 平均成绩
from student as a left join score as b
on a.学号=b.学号
group by a.学号,a.姓名
having avg(b.成绩)>85;
5. 查询每门课程的及格人数和不及格人数
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 课程号;
6. 使用分段[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 inner join course as b
on a.课程号=b.课程号
group by a.课程号,b.课程名称;
7. 查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名
select b.学号,b.姓名
from score as a inner join student as b
on a.学号=b.学号
where a.课程号='0003' and 成绩>80;
8. 把以下表的内容行列互换
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 学号;

五、 提高SQL查询效率的建议

1. SELECT语句中尽量避免使用*

SELECT*是全选整个表的数据，当数据量比较大的时候，运行起来会比较慢。需要用到什么列名就选什么列，养成良好的习惯。

2. Where子句比较符号左侧避免使用函数

Where子句是筛选行，需要整表扫描以确定所选的行在哪里。因此，在比较符号左侧使用函数的话，会增加电脑的运行量和运行时间。因此，要把运算公式都搬到比较符号的右侧。如where 分数+5>90 应该转换成 where 分数>90-5，如此类推。

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

in 和 not in 也会全表扫描，增加运行时间。

4. 尽量避免使用or

or 也会全表扫描，可以用下列方法替代or：

从成绩表中选出成绩是是88分或89分学生的学号：

select 学号
from 成绩表
where 成绩 = 88 or 成绩 = 89
优化后：
select 学号 from 成绩表 where 成绩 = 88
union
select 学号 from 成绩表 where 成绩 = 89

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

当返回的数据行数过多的时候，会增加运行时间。因此，我们也应该根据实际需要，需要多少行数据就返回多少行数据，而不是返回全表。