1.简单查询
练习一:查询姓猴的学生名单
练习二:查询姓名中最后一个字是猴的学生名单
练习三:查询姓名中带猴的学生名单
练习四:查询姓孟老师的个数
2.汇总分析
练习五:查询课程编号为‘0002’的总成绩
练习六:查询选了课程的学生人数
练习七:查询各科成绩最高和最低的分
练习八:查询每门课程被选修的学生人数
练习九:查询男生女生人数
练习十:查询平均成绩大于60分学生的学号和平均成绩
练习十一:查询至少选修两门课程的学生学号
练习十二:查询同名同姓学生名单并统计同名人数
练习十三:查询不及格的课程并按照课程号从大到小排列
练习十四:查询每门课程的平均成绩,结果按平均成绩升序排序,平均成绩相同时,按课程号降序排列
练习十五:检索课程编号为“0004”且分数小于60的学生学号,结果按按分数降序排列
练习十六:统计每门课程的学生选修人数(超过2人的课程才统计),要求输出课程号和选修人数,查询结果按人数降序排序,若人数相同,按课程号升序排序
练习十七:查询两门以上不及格课程的同学的学号及其平均成绩
三.复杂查询
练习十八:查询所有课程成绩小于60分学生的学号、姓名
思路:1.查询所有学生的学号,姓名。2.条件是:所有课程的成绩小于60
练习十九:查询没有学全所有课的学生的学号、姓名
练习二十:查询出只选修了两门课程的全部学生的学号和姓名
练习二十一:1990年出生的学生名单
应用日期函数year()
面试题补充:
练习二十二:分组取每组最大值,按课程号分组取求成绩最大值所在行的数据
关联子查询
练习二十三:按课程号分组取成绩最小值所在行的数据
案例:练习二十四:查询各科成绩前两名的记录(topN系列问题)
第1步,先查出有哪些组,按课程号分组。
查询有哪些课程号。
(分别求出各个组的前两名)求课程号'0001' 这一组里成绩前2名。
写出其他组的(其他课程号)取出成绩前2名的sql
0002
0003
使用union all 将每组选出的数据合并到一起,UNION ALL 命令和 UNION 命令几乎是等效的,不过 UNION ALL 命令会列出所有的值。UNION 操作符用于合并两个或多个 SELECT 语句的结果集。默认地,UNION 操作符选取不同的值。如果允许重复的值,使用 UNION ALL。请注意,UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同
(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)
topN系列问题
MySQL中的UNION
UNION在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录,最常见的是过程表与历史表UNION。如
select * from gc_dfys unionselect * from ls_jg_dfys
这个SQL在运行时先取出两个表的结果,再用排序空间进行排序删除重复的记录,最后返回结果集,如果表数据量大的话可能会导致用磁盘进行排序。
使用Union,则所有返回的行都是唯一的,如同对整个结果集合使用了DISTINCT。
MySQL中的UNION ALL
UNION ALL只是简单的将两个结果合并后就返回。这样,如果返回的两个结果集中有重复的数据,那么返回的结果集就会包含重复的数据了。UNION ALL 要比UNION快很多,所以,如果可以确认合并的两个结果集中不包含重复的数据的话,那么就使用UNION ALL,如下:
select * from gc_dfys union all select * from ls_jg_dfys
如果使用ORDER BY或LIMIT子句来对全部UNION结果进行分类或限制,则应对单个地SELECT语句加圆括号,并把ORDER BY或LIMIT放到最后一个的后面
例如
(SELECT a FROM tbl_name WHERE a=10 AND B=1)
UNION
(SELECT a FROM tbl_name WHERE a=11 AND B=2)
ORDER BY a LIMIT 10;
四.多表查询
练习二十五:查询所有学生的学号、姓名、选课数、总成绩
练习二十六:查询平均成绩大于85的所有学生的学号、姓名和平均成绩
练习二十七:查询学生的选课情况:学号,姓名,课程号,课程名称
-- 考察case表达式
练习二十八:查询出每门课程的及格人数和不及格人数
练习二十九:使用分段[100-85],[85-70],[70-60],[‹60]来统计各科成绩,分别统计:各分数段人数,课程号和课程名称
练习三十:查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名
练习三十一:面试题:行列如何互换的问题
下面是学生的成绩表(表名score,列名:学号、课程号、成绩)
使用sql实现将该表行转列为下面的表结构
第1步,使用常量列输出目标表的结构
第2步,使用case表达式,替换常量列为对应的成绩/分组,并使用最大值函数max取出上图每个方块里的最大值