SQLyog基本操作（六）-排序、分页、子查询与嵌套查询、分组过滤、select小结

4.6 排序 order by

 SELECT `SubjectNo`,`ClassHour` FROM `subject` -- 从subject表中选择哪些字段进行显示
 -- 排序：ORDER BY，其中，ASC表示升序，DESC表示降序
 ORDER BY `ClassHour` ASC -- 根据ClassHour进行升序显示
 -- ORDER BY `ClassHour` DESC -- 根据ClassHour进行降序显示

输出结果：

（1）升序结果

（2）降序结果

 

4.7 分页 limit

 -- 为什么要分页？缓解数据库压力，给人更好的体验，瀑布流需要不断加载
 SELECT `SubjectNo`,`ClassHour` FROM `subject`
 ORDER BY `ClassHour` ASC
 -- 语法：limit 起始值 页面的大小
 -- 每页只显示5条数据
 limit 0,5 -- 1-5条数据，下标从零开始，表示第1条数据 表示第1页
 -- limit 1,5 -- 2-6条数据
 -- limit 5,5 -- 6-10条数据 表示第2页
 -- limit 10,5 -- 11-15条数据 表示第3页
 -- LIMIT (n-1)*5,5 -- (n-1)*5+1-(n-1)*5+5条数据 表示第n页
 -- 详细语法：LIMIT (n-1)*pageSize,pageSize，其中，pageSize表示页面大小，(n-1)*pageSize表示起始值，n表示当前页，总页数=数据总数/页面大小 

语法：

LIMIT (n-1)×pageSize,pageSize，pageSize表示页面大小，其中，(n-1)*pageSize表示起始值，n表示当前页，总页数=数据总数/页面大小

输出结果：

（1）第1页

（2）第2页

练习题：

      查询JAVA第一学年课程成绩排名前十的同学，并且分数要大于80的学生信息，输出：学号、姓名、课程名称、分数。

 SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
 FROM `student` s
 INNER JOIN `result` r
 ON s.`StudentNo`=r.`StudentNo`
 INNER JOIN `subject` sub
 ON r.`SubjectNo`=sub.`SubjectNo`
 WHERE `SubjectName`= 'JAVA第一学年' AND `StudentResult`>=80
 ORDER BY `StudentResult` DESC
 LIMIT 0,10

 

4.8 子查询与嵌套查询

之前我们在写sql，查询条件中的值都是固定的，如果将条件中的值换成计算出来的话，就是子查询了。

本质：在where条件下嵌套一个查询语句

 -- 1.查询“数据库结构-1”的所有考试结果，输出：学号、科目编号、成绩，降序排列
 -- 方式1：使用连接查询
 SELECT `StudentNo`,r.`SubjectNo`,`StudentResult`
 FROM `result` r
 INNER JOIN `subject` sub
 ON r.`SubjectNo`=sub.`SubjectName`
 WHERE `SubjectName`='数据库结构-1'
 ORDER BY `StudentResult` DESC
 ​
 -- 方式2：使用子查询（由里及外）
 -- 查询所有“数据库结构-1”的学生学号
 SELECT `StudentNo`,`SubjectNo`,`StudentResult`
 FROM `result`
 WHERE `SubjectNo`= ANY(
   SELECT `SubjectNo` FROM `subject`
   WHERE `SubjectName` = '数据库结构-1'
 )
 ORDER BY `StudentResult` DESC
 -- Subquery returns more than 1 row:表示子查询返回了多行数据
 -- 解决方法：在()前加ANY
 ​
 -- 2.查询不小于80分的学生的学号和姓名
 SELECT DISTINCT s.`StudentNo`,`StudentName`
 FROM `student` s
 INNER JOIN `result` r
 ON s.`StudentNo`=r.`StudentNo`
 WHERE `StudentResult`>80
 ​
 -- 3.在2的基础上，增加一个科目：高等数学-2
 -- 查询“高等数学-2”的编号
 SELECT DISTINCT s.`StudentNo`,`StudentName`
 FROM `student` s
 INNER JOIN `result` r
 ON s.`StudentNo`=s.`StudentNo`
 WHERE `StudentResult`>80 AND `SubjectNo`=(
   SELECT `SubjectNo` FROM `subject`
   WHERE `StudentName`='高等数学-2'
 )
 ​
 -- 改造（由里及外）
 SELECT `StudentNo`,`StudentName` FROM `student` WHERE `StudentNo` IN (
   SELECT `StudentNo` FROM `result` WHERE `StudentResult`>80 AND `SubjectNo`= (
     SELECT `SubjectNo` FROM `subject` WHERE `SubjectName` = '高等数学-2'
   )
 )
 ​
 -- 用连接查询实现
 SELECT s.`StudentNo`,`StudentName`
 FROM `student` s
 INNER JOIN `result` r
 ON s.`StudentNo`=r.`StudentNo`
 INNER JOIN `subject` sub
 ON r.`SubjectNo`=sub.`SubjectNo`
 WHERE `SubjectName`='高等数学-2' AND `StudentResult`>=80
 ​
 -- 练习：使用子查询查询“C语言-1”前5名同学的成绩信息：学号、姓名、分数
 SELECT DISTINCT s.`StudentNo`,`StudentName`,`StudentResult` 
 FROM `student` s
 INNER JOIN `result` r
 ON s.`StudentNo`=r.`StudentNo`
 WHERE `SubjectNo` = ANY(
   SELECT `SubjectNo` FROM `subject` WHERE `SubjectName`='C语言-1'
 )
 ORDER BY `StudentResult` DESC
 LIMIT 0,5

4.9 分组和过滤

 -- 查询不同课程的平均分、最高分、最低分，平均分大于80的记录
 -- 核心：根据不同课程进行分组
 SELECT `SubjectName`,AVG(`StudentResult`) AS `平均分`,MAX(`StudentResult`) AS `最高分`,MIN(`StudentResult`) AS `最低分`
 FROM `result` r
 INNER JOIN `subject` sub
 ON r.`SubjectNo`=sub.`SubjectNo`
 GROUP BY r.`SubjectNo` -- 通过什么字段来查询
 HAVING `平均分`>80

4.10 select语法小结

SELECT语法

 SELECT [ALL | DISTINCT]
 {* | table.* | [table.field1[AS aliasl][,table.field2[AS alias2]][,...]]}
 FROM table_name [AS table_alias]
   [LEFT | RIGHT | INNER JOIN table_name2] -- 联合查询
   [WHERE ...] -- 指定结果需满足的条件
   [GROUP BY ...] -- 指定结果按照哪几个字段来分组
   [HAVING] -- 过滤分组的记录必须满足的次要条件
   [ORDER BY ...] -- 指定查询记录按一个或多个条件排序
   [LIMIT {[OFFSET,]ROW_COUNT | row_countoffset OFFSET}];
   -- 指定查询的记录从哪条至哪条
   -- 注意：[]括号代表可选的，{}括号代表必选的  
 -- all是默认的，查询所所有，distinct是消除重复的；
 -- xxx join：代表要连接的表，on表示等值判断；
 -- where：条件子句，后面跟查询的值或者子查询语句（注意不能跟聚合函数共同使用）；
 -- group by：通过某个字段来分组；
 -- having： 过滤分组后的信息，作用和where差不多，只是放置的位置不同，且having能跟聚合函数一起使用；
 -- order by：通过某个字段来排序，[升序（asc）/ 降序（desc）]；
 -- limit：分页，从索引为某个值开始，查多少条数据 ；
 ​
 -- 顺序很重要
 select 去重 要查询的字段 from 表（注意：表和字段可以起别名，注意别名要用``可不是‘’括起来）
 xxx join 要连接的表 on 等值判断
 where 具体的值，子查询语句
 group by 通过哪个字段进行分组
 having 过滤分组后的信息，条件和where是一样的，只是位置不同而已
 order by 通过哪个字段进行排序，升序asc、降序desc
 limit stratindex，pagesize 起始位置，页面大小  进行分页
 ​
 -- 业务层面的查询：跨表、跨数据库......
 ​