一、扩展阅读
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