Warning: Undefined variable $v in E:\home\LocalUser\424681\www\blog\wordpress\wp-content\plugins\wp-content-index\wp-content-index.php on line 357

Warning: Undefined variable $v in E:\home\LocalUser\424681\www\blog\wordpress\wp-content\plugins\wp-content-index\wp-content-index.php on line 357

Warning: Undefined variable $v in E:\home\LocalUser\424681\www\blog\wordpress\wp-content\plugins\wp-content-index\wp-content-index.php on line 357

目录 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

LT

Revolutionary Road

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