排序检索数据 – SQLite 学习之路 (3)

目录 Content
[hide]

一、扩展阅读

1. 检测数据库完整性

PRAGMA 是一个常见的关键字,在sqlite里也有。

C:\Users\liangtao>sqlite3 tysql.sqlite
-- Loading resources from C:\Users\liangtao/.sqliterc

SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
sqlite> PRAGMA integrity_check;
integrity_check
---------------
ok
sqlite>

关于 PRAGMA 更详细的资料,移步 http://www.sqlite.org/pragma.html

 二、排序检索数据

1.默认排序

作者提出:关系型数据库设计理论认为,如果不明确规定排序顺序,则不应该假定检索出的数据的顺序有任何意义。

sqlite> SELECT prod_name
   ...> FROM Products;
prod_name
-----------------
8 inch teddy bear
12 inch teddy bea
18 inch teddy bea
Fish bean bag toy
Bird bean bag toy
Rabbit bean bag t
Raggedy Ann
King doll
Queen doll
sqlite>

注意看到 R->K->Q 顺序就乱了。

为了明确,可使用 ORDER BY 子句,默认 A-Z 顺序排序。ORDER BY 必须是最后一个子句。使用 ORDER BY 的子句中列不一定是要显示的列。

sqlite> SELECT prod_name
   ...> FROM Products
   ...> ORDER BY prod_name;
prod_name
------------------
12 inch teddy bear
18 inch teddy bear
8 inch teddy bear
Bird bean bag toy
Fish bean bag toy
King doll
Queen doll
Rabbit bean bag to
Raggedy Ann
sqlite>

2. 按多个列排序

sqlite> SELECT prod_id,prod_price,prod_name
   ...> FROM Products
   ...> ORDER BY prod_price,prod_name;
prod_id     prod_price  prod_name
----------  ----------  -----------------
BNBG02      3.49        Bird bean bag toy
BNBG01      3.49        Fish bean bag toy
BNBG03      3.49        Rabbit bean bag t
RGAN01      4.99        Raggedy Ann
BR01        5.99        8 inch teddy bear
BR02        8.99        12 inch teddy bea
RYL01       9.49        King doll
RYL02       9.49        Queen doll
BR03        11.99       18 inch teddy bea
sqlite>

先按 prod_price 排序,再按 prod_name 排序。

3. 按列位置排序

顾名思义,就是列的位置,而不是名称

sqlite> SELECT prod_id,prod_price,prod_name
   ...> FROM Products
   ...> ORDER BY 2,3
   ...> ;
prod_id     prod_price  prod_name
----------  ----------  -----------------
BNBG02      3.49        Bird bean bag toy
BNBG01      3.49        Fish bean bag toy
BNBG03      3.49        Rabbit bean bag t
RGAN01      4.99        Raggedy Ann
BR01        5.99        8 inch teddy bear
BR02        8.99        12 inch teddy bea
RYL01       9.49        King doll
RYL02       9.49        Queen doll
BR03        11.99       18 inch teddy bea
sqlite>

4.指定排序方向

默认的排序方向是 A-Z ,这是升序。如果要降序,Z-A,则需要关键字 DESC

sqlite> SELECT prod_id,prod_price,prod_name
   ...> FROM Products
   ...> ORDER BY prod_price DESC;
prod_id     prod_price  prod_name
----------  ----------  ------------------
BR03        11.99       18 inch teddy bear
RYL01       9.49        King doll
RYL02       9.49        Queen doll
BR02        8.99        12 inch teddy bear
BR01        5.99        8 inch teddy bear
RGAN01      4.99        Raggedy Ann
BNBG01      3.49        Fish bean bag toy
BNBG02      3.49        Bird bean bag toy
BNBG03      3.49        Rabbit bean bag to
sqlite>

如果指定多列

sqlite> SELECT prod_id,prod_price,prod_name
   ...> FROM Products
   ...> ORDER BY prod_price DESC, prod_name;
prod_id     prod_price  prod_name
----------  ----------  ------------------
BR03        11.99       18 inch teddy bear
RYL01       9.49        King doll
RYL02       9.49        Queen doll
BR02        8.99        12 inch teddy bear
BR01        5.99        8 inch teddy bear
RGAN01      4.99        Raggedy Ann
BNBG02      3.49        Bird bean bag toy
BNBG01      3.49        Fish bean bag toy
BNBG03      3.49        Rabbit bean bag to
sqlite>

再看

sqlite> SELECT prod_id,prod_price,prod_name
   ...> FROM Products
   ...> ORDER BY prod_price,prod_name DESC;
prod_id     prod_price  prod_name
----------  ----------  -------------------
BNBG03      3.49        Rabbit bean bag toy
BNBG01      3.49        Fish bean bag toy
BNBG02      3.49        Bird bean bag toy
RGAN01      4.99        Raggedy Ann
BR01        5.99        8 inch teddy bear
BR02        8.99        12 inch teddy bear
RYL02       9.49        Queen doll
RYL01       9.49        King doll
BR03        11.99       18 inch teddy bear
sqlite>

只对 prod_name 起作用。

如果要对多列降序,必须分别指定 DESC

sqlite> SELECT prod_id,prod_price,prod_name
   ...> FROM Products
   ...> ORDER BY prod_price DESC ,prod_name DESC;
prod_id     prod_price  prod_name
----------  ----------  ------------------
BR03        11.99       18 inch teddy bear
RYL02       9.49        Queen doll
RYL01       9.49        King doll
BR02        8.99        12 inch teddy bear
BR01        5.99        8 inch teddy bear
RGAN01      4.99        Raggedy Ann
BNBG03      3.49        Rabbit bean bag to
BNBG01      3.49        Fish bean bag toy
BNBG02      3.49        Bird bean bag toy
sqlite>

 关键字:ORDER BY, DESC, 子句, PRAGMA, integrity_check

 

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.