MySQL:Basic Order by & Limit sub-clause 1. Using basic 'order by' Java代碼 # 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 Java代碼 # 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' Sql代碼 # 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. Sql代碼 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.
