当前位置:首页 > 开发 > 数据库 > 正文

MySQL: Basic Order by & Limit sub-clause

发表于: 2013-04-10   作者:DavyJones2010   来源:转载   浏览次数:
摘要: 1. Using basic 'order by' # Order stu by stu_score as the order of descendant select * from stu order by stu_score desc;   2. Using 'order by' to order multiple columns # Order table 'stu' b

1. Using basic 'order by'

# Order stu by stu_score as the order of descendant
select * from stu order by stu_score desc;

 

2. Using 'order by' to order multiple columns

# Order table 'stu' by stu_score
# If two row's stu_score are the same, then use stu_name as secondary order restriction
select * from stu order by stu_score desc, stu_name desc;

 

3. Using 'order by' together with 'limit'

# Select the highest two stu_score
select * from stu order by stu_score asc limit 2;

 

4. Limit [offset], [N]

    offset: default 0;

    N:        The number of item to be selected.

    <Limit is useful in paging>

 

    Comment:

        How can we fetch the stu_course for each student whose highest score belongs to this course?

 

    Answer:

        1. First, order the table stu as the stu_name as primary order regulator.

        2. Second, set the stu_score as the secondary order regulator.

        3. Last, regard the result set as a virtual table and the execute the query based on this table.

select * from (select * from stu order by stu_name asc, stu_score desc) as temp group by stu_name;

 

    Summary:

        1) where expression

            To see if the expression is true on each row then fatch this row.

            =, !=, <>, >, <, <=, >=

            in, between and

            or, and, not

        2) group by

            Is used for grouping, usually used together with statistic function.

            sum, count, max, min, avg

        3) having expression

           If the expression is true when used in where clause, then it can be applied in having expression.

           But if the expression is based on virtual table(or result set), then it can only applied in having expression. While expression can only applied for real table that stored on disk.

        4) order by

           Can be applied for order real table or result set as the order of [asc] or [desc].

           Multiple compare rules are enabled.

        5) limit

          Can be applied for result set for paging or limit the shown item.

           

MySQL: Basic Order by & Limit sub-clause

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
sql注入在组合拼接方面一直都很灵活,因为语法本身就很灵活,不过Mysql的Limit下一直没办法注入,国外
写在前面 上篇文章介绍mysql的增删改查操作,这篇将介绍group和order by操作。 系列文章 mysql之创
这两天让一个数据查询难了。主要是对group by 理解的不够深入。才出现这样的情况 这种需求,我想很
mysql order by 索引: 知道的大神求解啊。另外order by 效率问题有没有解决方案啊,求分享 直接 上
ORDER BY 可以匹配索引是限制的 具体可以参考下http://dev.mysql.com/doc/refman/5.6/en/order-by-o
ORDER BY 可以匹配索引是限制的 具体可以参考下http://dev.mysql.com/doc/refman/5.6/en/order-by-o
ORDER BY 可以匹配索引是限制的 具体可以参考下http://dev.mysql.com/doc/refman/5.6/en/order-by-o
1、MYSQL中LIMIT用法_百度知道 limit是mysql的语法select * from table limit m,n 其中m是指记录开
select count(*),count(id) from tables count(*) 会查询空行,count(id) 不会查询空行 如果payment
在之前的一篇日志里(http://blog.csdn.net/u010501526/article/details/8875446),我将重新构建LO
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号