关闭

MariaDB学习笔记(3)--排序检索数据

205人阅读 评论(0) 收藏 举报
分类:

排序检索数据

3.1排序数据

可以看到title的输出并没有特定的顺序

MariaDB [test]> SELECT title 
    -> From books;
title
The Fellowship of the Ring
The Two Towers
The Return of the King
The Sum of All Men
Brotherhood of the Wolf
Wizardborn
The Hobbit
Lair of Bones
8 rows in set (0.00 sec)

那么我们就需要用到ORDER BY语句
【waring】ORDER BY语句的位置
在制定一条ORDER BY 子句时应确保它是SELECT语句的最后一句,否则会报错

MariaDB [test]> SELECT title 
    -> From books
    -> ORDER BY title;
title
Brotherhood of the Wolf
Lair of Bones
The Fellowship of the Ring
The Hobbit
The Return of the King
The Sum of All Men
The Two Towers
Wizardborn
8 rows in set (0.00 sec)

3.2按多个列排序

要按多个列排序,简单制定列名,列名之间用逗号分隔开就好

MariaDB [test]> SELECT title,  AuthorID,SeriesID   
    -> FROM books
    -> ORDER BY AuthorID,SeriesID;
title AuthorID SeriesID
The Hobbit 1 0
The Fellowship of the Ring 1 1
The Two Towers 1 1
The Return of the King 1 1
The Sum of All Men 2 2
Brotherhood of the Wolf 2 2
Wizardborn 2 2
Lair of Bones 2 2
8 rows in set (0.00 sec)

这里是先按照AuthorID进行排序,之后按SeriesID排序

3.3按选择列的相对位置排序

这里title是对应的第一个元素,与之对应AuthorID和SeriesID分别为2,3

MariaDB [test]> SELECT title,AuthorID,SeriesID
    -> FROM books
    -> ORDER BY 2,3;
title AuthorID SeriesID
The Hobbit 1 0
The Fellowship of the Ring 1 1
The Two Towers 1 1
The Return of the King 1 1
The Sum of All Men 2 2
Brotherhood of the Wolf 2 2
Wizardborn 2 2
Lair of Bones 2 2
8 rows in set (0.00 sec)

3.4制定排序方向

DESC关键字

DESC是DESCENDINGDE缩写
MariaDB [test]> SELECT BookID,title,AuthorID
    -> FROM books
    -> ORDER BY BookID DESC;
BookID title AuthorID
8 Lair of Bones 2
7 The Hobbit 1
6 Wizardborn 2
5 Brotherhood of the Wolf 2
4 The Sum of All Men 2
3 The Return of the King 1
2 The Two Towers 1
1 The Fellowship of the Ring 1
8 rows in set (0.00 sec)
MariaDB [test]> SELECT title,AuthorID,SeriesID
    -> FROM books
    -> ORDER BY title, AuthorID DESC;
title AuthorID SeriesID
Brotherhood of the Wolf 2 2
Lair of Bones 2 2
The Fellowship of the Ring 1 1
The Hobbit 1 0
The Return of the King 1 1
The Sum of All Men 2 2
The Two Towers 1 1
Wizardborn 2 2
8 rows in set (0.00 sec)

【waring】DESC关键字只应用到直接位于其前面的列名,如果想在多个列上进行降序排列必须对每一列指定DESC关键字。

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:2597次
    • 积分:85
    • 等级:
    • 排名:千里之外
    • 原创:5篇
    • 转载:0篇
    • 译文:0篇
    • 评论:0条
    文章分类
    文章存档