🔥排序的应用场景 🔥
1、学生身高从高到低进行排列
2、双十一某商城商品交易量排行榜
3、博客中的文章按时间先后顺序进行显示
🔥OrderBy使用场景 🔥
在SQL中,使用order by 对查询结果集进行排序,可以按照一列或多列进行排序。
order by 语法
SELECT column_name1,column_name2 FROM table_name1,table_name2 ORDER BY column_name,column_name [ASC|DESC]
说明:
1、ASC表示按升序排列,DESC表示按降序排列。
2、默认情况下,对列按升序排列。
mysql> select * from employee order by sex , salary desc; +----+--------+------+--------+ | id | name | sex | salary | +----+--------+------+--------+ | 3 | 张小妹 | 女 | 4500 | | 1 | 张三 | 男 | 5500 | | 2 | 李四 | 男 | 4500 | +----+--------+------+--------+ 3 rows in set (0.00 sec) mysql> select * from employee order by sex , salary asc; +----+--------+------+--------+ | id | name | sex | salary | +----+--------+------+--------+ | 3 | 张小妹 | 女 | 4500 | | 2 | 李四 | 男 | 4500 | | 1 | 张三 | 男 | 5500 | +----+--------+------+--------+ 3 rows in set (0.00 sec)
🔥 limit的使用 🔥
在SELECT语句中使用LIMIT子句来约束要返回的记录数,通常使用LIMIT实现分页。
limit语法
SELECT column_name1,column_name2,FROM table_name1,table_name2 LIMIT [offset,] row_count
说明:
1、offset指定要返回的第一行的偏移量。第一行的偏移量是0,而不是1。
2、row_count指定要返回的最大行数。
select *from employee; +----+--------+------+--------+ | id | name | sex | salary | +----+--------+------+--------+ | 1 | 张三 | 男 | 5500 | | 2 | 李四 | 男 | 4500 | | 3 | 张小妹 | 女 | 4500 | +----+--------+------+--------+ 3 rows in set (0.00 sec)
select * from employee order by salary; +----+--------+------+--------+ | id | name | sex | salary | +----+--------+------+--------+ | 2 | 李四 | 男 | 4500 | | 3 | 张小妹 | 女 | 4500 | | 1 | 张三 | 男 | 5500 | +----+--------+------+--------+ 3 rows in set (0.00 sec)
mysql> select * from employee order by salary desc; +----+--------+------+--------+ | id | name | sex | salary | +----+--------+------+--------+ | 1 | 张三 | 男 | 5500 | | 2 | 李四 | 男 | 4500 | | 3 | 张小妹 | 女 | 4500 | +----+--------+------+--------+ 3 rows in set (0.00 sec) mysql> select * from employee order by salary asc; +----+--------+------+--------+ | id | name | sex | salary | +----+--------+------+--------+ | 2 | 李四 | 男 | 4500 | | 3 | 张小妹 | 女 | 4500 | | 1 | 张三 | 男 | 5500 | +----+--------+------+--------+ 3 rows in set (0.00 sec)
select * from employee limit 2; +----+------+------+--------+ | id | name | sex | salary | +----+------+------+--------+ | 1 | 张三 | 男 | 5500 | | 2 | 李四 | 男 | 4500 | +----+------+------+--------+ 2 rows in set (0.00 sec)
mysql> select * from employee limit 2,3; +----+--------+------+--------+ | id | name | sex | salary | +----+--------+------+--------+ | 3 | 张小妹 | 女 | 4500 | +----+--------+------+--------+ 1 row in set (0.00 sec) mysql> select * from employee limit 1,3; +----+--------+------+--------+ | id | name | sex | salary | +----+--------+------+--------+ | 2 | 李四 | 男 | 4500 | | 3 | 张小妹 | 女 | 4500 | +----+--------+------+--------+ 2 rows in set (0.00 sec) mysql> select * from employee limit 0,3; +----+--------+------+--------+ | id | name | sex | salary | +----+--------+------+--------+ | 1 | 张三 | 男 | 5500 | | 2 | 李四 | 男 | 4500 | | 3 | 张小妹 | 女 | 4500 | +----+--------+------+--------+ 3 rows in set (0.00 sec)