一、 简单查询
1.创建学校数据库的表
1）学生表
2）成绩表
3）课程表
4）教师表
2.查找学生
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、汇总查询
1）查询课程编号为“0002”的总成绩
-- 查询课程为“0002”的总成绩
select 课程号,sum(成绩)
from score
where 课程号='0002'
2）查询选了课程的学生人数
-- 查询选了课程的学生人数
select count(distinct(学号)) as 学生人数
from score;
2、分组查询
1）查询各科成绩最高和最低分，以如下的形式显示：课程号，最高分，最低分
-- 查询各科成绩最高和最低分，以如下的形式显示：课程号，最高分，最低分
select 课程号,max(成绩) as 最高分,min(成绩) as 最低分
from score
group by 课程号
2）查询每门课程被选修的学生数
-- 查询每门课程被选修的学生数
select 课程号,count(学号)
from score
group by 课程号;
3）查询男生、女生数
-- 查询男生、女生数
select 性别,count(*) as 人数
from student
group by 性别
3、分组结果的条件
1、查询平均成绩大于60分学生的学号和平均成绩
-- 查询平均成绩大于60分学生的学号和平均成绩
select 学号,avg(成绩)
from score
group by 学号
having avg(成绩)>60
2、查询至少选修两门课程的学生学号
-- 查询至少选修两门课程的学生学号
select 学号,count(课程号) as 选修课程数
from score
group by 学号
having count(课程号)>=2;
3、查询同名同姓学生名单并统计同名人数
-- 查询同名同姓学生名单并统计同名人数
select 姓名,count(姓名)as 人数
from student
group by 姓名
having count(姓名)>=2
4、查询不及格的课程并按课程号从大到小排列
-- 查询不及格的课程并按课程号从大到小排列
select 课程号
from score
where 成绩<60
order by 课程号 desc;
5、 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排 序
-- 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排 序
select 课程号,avg(成绩) as 平均成绩
from score
group by 课程号
order by avg(成绩) asc,课程号 desc;
6、 检索课程编号为“0004”且分数小于60的学生学号，结果按分数降序排列
-- 检索课程编号为“0004”且分数小于60的学生学号，结果按分数降序排列
select 学号
from score
where 课程号='0004' and 成绩<60
order by 成绩 desc;
7、 统计每门课程的学生选修人数（超过2人的课程才统计）
要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序
/*
统计每门课程的学生选修人数（超过2人的课程才统计）
要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序
*/
select 课程号,count(学号) as 选修人数
from score
group by 课程号
having count(学号)>2
order by count(学号) desc,课程号 asc;
8、查询两门以上不及格课程的同学的学号，以及不及格课程的平均成绩
-- 查询两门以上不及格课程的同学的学号，以及不及格课程的平均成绩
select 学号,avg(成绩) as 平均成绩
from score
where 成绩<60
group by 学号
having count(成绩)>=2
三、复杂查询
1、 查询所有课程成绩小于60分学生的学号、姓名
`-- 查询所有课程成绩小于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);
4、 1990年出生的学生名单
-- 1990年出生的学生名单
select 学号,姓名,出生日期,性别
from student
where year(出生日期)=1990
5、 分组取每组最大值
案例1：按课程号分组取成绩最大值所在行的数据
-- 按课程号分组取成绩最大值所在行的数据
select 课程号，max（成绩）
from score
group by 课程号
案例2：按课程号分组取成绩最小值所在行的数据
-- 按课程号分组取成绩最小值所在行的数据
select 课程号，min（成绩）
from score
group by 课程号
6、每组最大的N条记录
案例：查询各科成绩前两名的记录
-- 查询各科成绩前两名的记录
(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);
四、 多表查询
题目
下面是学生的名单，表名为“学生表”；近视学生的名单，表名为“近视学生表”。请问不是近视眼的学生都是谁？
（“学生表”表中的学号与“近视学生”表中的学生学号一一对应）
/*
下面是学生的名单，表名为“学生表”；近视学生的名单，
表名为“近视学生表”。请问不是近视眼的学生都是谁？
*/
select a.学号,a.姓名
from 学生表 as a left join 近视学生表 as b on a.学号=b.学生学号
where b.学生学号 is null
【举一反三】
查找“不在表里的数据”应用案例“”：
某网站包含两个表，顾客姓名表（表名customers）和“购买记录表（表名orders）。
找出所有从不订购任何东西的客户。
（“顾客姓名表”中的id 与“购买记录”表中的学生学号customerid一一对应）
/*
查找“不在表里的数据”应用案例“”：
某网站包含两个表，顾客姓名表（表名customers）和“购买记录表（表名orders）。
找出所有从不订购任何东西的客户。
*/
select a.ID,a.Name
from 顾客姓名表 as a left join 购买记录表 as b on a.ID=b.CustomerID
where CustomerID is null
练习
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的所有学生的学号、姓名和平均成绩
-- 查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select a.学号,a.姓名,avg(b.成绩)
from student as a inner join score as b on a.学号=b.学号
group by a.学号
having avg(b.成绩)>85
3、查询学生的选课情况：学号，姓名，课程号，课程名称
-- 查询学生的选课情况：学号，姓名，课程号，课程名称
select a.学号,a.姓名,b.课程号,c.课程名称
from student as a left join score as b on a.学号=b.学号
inner join course as c on b.课程号=c.课程号
4、查询出每门课程的及格人数和不及格人数
-- 查询出每门课程的及格人数和不及格人数
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、查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名
-- 查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名
select a.学号,a.姓名
from student as a inner join score as b on a.学号=b.学号
where b.课程号='0003' and b.成绩>80;
6、下面是学生的成绩表（表名score，列名：学号、课程号、成绩）
使用SQL 实现将该表行转列为下面的表结构
-- 使用SQL 实现将该表行转列为下面的表结构
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查询的效率
【题目】
我们公司的数据量非常大，需要的不仅仅是提取数据，要了解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万行，那么这适合最好使用limit子句限制查询返回的数据行数。
【本题考点】
在面试中，当面试官提出这一类问题，按照上述的方法进行回答都是没有问题的，但不仅在面试中，平时练习就养成习惯是最好的。
大多数同学都会觉得“麻烦”、“不做也没有什么影响”，但是习惯就慢慢养成了。
拥有好习惯，未来在工作中，面对不同的数据量，就可以游刃有余地选择不同的方法来降低完成时间，从而提升工作效率。
