mysql升序和降序语句_MySQL-六-面试题总结

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取出上图每个方块里的最大值

课上练习题共三十一道，结束。