目录 Content
[hide]
一、准备工作
在进入正题之前,了解下怎么获取数据库的一些信息
1.列出SQLite的版本
sqlite> select sqlite_version(); sqlite_version() ---------------- 3.8.10.2 sqlite>
2.列出数据库中包含的表
sqlite> .table Customers OrderItems Orders Products Vendors sqlite>
二、数据检索
1.作者建议
- 将SQL语句分行书写更为方便阅读
- SELECT返回的顺寻并无特定
- 多条语句用 ; 分开
- 语句会忽略空格
- 语句不区分大小写
2.检索单列
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>
3.检索多个列
sqlite> SELECT prod_id,prod_name,prod_price ...> FROM Products; prod_id prod_name prod_price ---------- ----------------- ---------- BR01 8 inch teddy bear 5.99 BR02 12 inch teddy bea 8.99 BR03 18 inch teddy bea 11.99 BNBG01 Fish bean bag toy 3.49 BNBG02 Bird bean bag toy 3.49 BNBG03 Rabbit bean bag t 3.49 RGAN01 Raggedy Ann 4.99 RYL01 King doll 9.49 RYL02 Queen doll 9.49 sqlite>
4.检索所有列
sqlite> SELECT * ...> FROM Products; prod_id vend_id prod_name prod_price prod_desc ---------- ---------- ----------------- ---------- -------------------------------------------- BR01 BRS01 8 inch teddy bear 5.99 8 inch teddy bear, comes with cap and jacket BR02 BRS01 12 inch teddy bea 8.99 12 inch teddy bear, comes with cap and jacke BR03 BRS01 18 inch teddy bea 11.99 18 inch teddy bear, comes with cap and jacke BNBG01 DLL01 Fish bean bag toy 3.49 Fish bean bag toy, complete with bean bag wo BNBG02 DLL01 Bird bean bag toy 3.49 Bird bean bag toy, eggs are not included BNBG03 DLL01 Rabbit bean bag t 3.49 Rabbit bean bag toy, comes with bean bag car RGAN01 DLL01 Raggedy Ann 4.99 18 inch Raggedy Ann doll RYL01 FNG01 King doll 9.49 12 inch king doll with royal garments and cr RYL02 FNG01 Queen doll 9.49 12 inch queen doll with royal garments and c sqlite>
5.检索不同值
如上例,vend_id 只有3种,若对其检索,则会显示9行
sqlite> SELECT vend_id ...> FROM Products; vend_id ---------- BRS01 BRS01 BRS01 DLL01 DLL01 DLL01 DLL01 FNG01 FNG01 sqlite>
若使用 DISTINCT 关键字,则只返回不同的值。DISTINCT 作用于多个列,除非列完全相同,否则所有的行还是会显示出来。
sqlite> SELECT DISTINCT vend_id ...> FROM Products; vend_id ---------- BRS01 DLL01 FNG01 sqlite>
6.限制结果
使用 LIMIT 可限制显示的行数
sqlite> SELECT prod_name ...> FROM Products ...> LIMIT 5; prod_name ----------------- 8 inch teddy bear 12 inch teddy bea 18 inch teddy bea Fish bean bag toy Bird bean bag toy sqlite>
为了得到后面的5行,使用 OFFSET 指定偏移位置
sqlite> SELECT prod_name ...> FROM Products ...> LIMIT 5 OFFSET 5; prod_name ------------------- Rabbit bean bag toy Raggedy Ann King doll Queen doll sqlite>
7.使用注释
sqlite> #这是一条注释 ...> SELECT prod_name --这是单行注释 ...> FROM Products; Error: near "#这是一条注释": syntax error 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>
# 类型的注释并不适合在shell中使用,但是可以在脚本中使用。
三、扩展应用
1.列出表的声明语句
sqlite> .schema Products CREATE TABLE Products ( prod_id char(10) NOT NULL , vend_id char(10) NOT NULL , prod_name char(255) NOT NULL , prod_price decimal(8,2) NOT NULL , prod_desc text NULL , PRIMARY KEY (prod_id) , FOREIGN KEY (vend_id) REFERENCES Vendors (vend_id) ); sqlite>
2.导出表的内容
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> .output prod.sql sqlite> .dump Products sqlite> .quit
可查看prod.sql的内容
C:\Users\liangtao>type prod.sql PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE Products ( prod_id char(10) NOT NULL , vend_id char(10) NOT NULL , prod_name char(255) NOT NULL , prod_price decimal(8,2) NOT NULL , prod_desc text NULL , PRIMARY KEY (prod_id) , FOREIGN KEY (vend_id) REFERENCES Vendors (vend_id) ); INSERT INTO "Products" VALUES('BR01','BRS01','8 inch teddy bear',5.99,'8 inch teddy bear, comes with cap and jacket'); INSERT INTO "Products" VALUES('BR02','BRS01','12 inch teddy bear',8.99,'12 inch teddy bear, comes with cap and jacket'); INSERT INTO "Products" VALUES('BR03','BRS01','18 inch teddy bear',11.99,'18 inch teddy bear, comes with cap and jacket') ; INSERT INTO "Products" VALUES('BNBG01','DLL01','Fish bean bag toy',3.49,'Fish bean bag toy, complete with bean bag worms with which to feed it'); INSERT INTO "Products" VALUES('BNBG02','DLL01','Bird bean bag toy',3.49,'Bird bean bag toy, eggs are not included'); INSERT INTO "Products" VALUES('BNBG03','DLL01','Rabbit bean bag toy',3.49,'Rabbit bean bag toy, comes with bean bag carr ots'); INSERT INTO "Products" VALUES('RGAN01','DLL01','Raggedy Ann',4.99,'18 inch Raggedy Ann doll'); INSERT INTO "Products" VALUES('RYL01','FNG01','King doll',9.49,'12 inch king doll with royal garments and crown'); INSERT INTO "Products" VALUES('RYL02','FNG01','Queen doll',9.49,'12 inch queen doll with royal garments and crown'); COMMIT; C:\Users\liangtao>
3.读入表的内容
C:\Users\liangtao>sqlite3 -- Loading resources from C:\Users\liangtao/.sqliterc SQLite version 3.8.10.2 2015-05-20 18:17:19 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .table sqlite> .read prod.sql sqlite> .table Products sqlite> SELECT * ...> FROM Products; prod_id vend_id prod_name prod_price prod_desc ---------- ---------- ----------------- ---------- -------------------------------------------- BR01 BRS01 8 inch teddy bear 5.99 8 inch teddy bear, comes with cap and jacket BR02 BRS01 12 inch teddy bea 8.99 12 inch teddy bear, comes with cap and jacke BR03 BRS01 18 inch teddy bea 11.99 18 inch teddy bear, comes with cap and jacke BNBG01 DLL01 Fish bean bag toy 3.49 Fish bean bag toy, complete with bean bag wo BNBG02 DLL01 Bird bean bag toy 3.49 Bird bean bag toy, eggs are not included BNBG03 DLL01 Rabbit bean bag t 3.49 Rabbit bean bag toy, comes with bean bag car RGAN01 DLL01 Raggedy Ann 4.99 18 inch Raggedy Ann doll RYL01 FNG01 King doll 9.49 12 inch king doll with royal garments and cr RYL02 FNG01 Queen doll 9.49 12 inch queen doll with royal garments and c sqlite>
4.Python读取表
#!/usr/bin/python import sqlite3 as lite import sys con = lite.connect('tysql.sqlite') with con: cur = con.cursor() cur.execute("SELECT * FROM Products") rows = cur.fetchall() for row in rows: print row[0],row[1],row[2],row[3],row[4]
运行后,结果如下
Python 2.7.8 (default, Jun 30 2014, 16:03:49) [MSC v.1500 32 bit (Intel)] on win32 Type "copyright", "credits" or "license()" for more information. >>> ================================ RESTART ================================ >>> BR01 BRS01 8 inch teddy bear 5.99 8 inch teddy bear, comes with cap and jacket BR02 BRS01 12 inch teddy bear 8.99 12 inch teddy bear, comes with cap and jacket BR03 BRS01 18 inch teddy bear 11.99 18 inch teddy bear, comes with cap and jacket BNBG01 DLL01 Fish bean bag toy 3.49 Fish bean bag toy, complete with bean bag worms with which to feed it BNBG02 DLL01 Bird bean bag toy 3.49 Bird bean bag toy, eggs are not included BNBG03 DLL01 Rabbit bean bag toy 3.49 Rabbit bean bag toy, comes with bean bag carrots RGAN01 DLL01 Raggedy Ann 4.99 18 inch Raggedy Ann doll RYL01 FNG01 King doll 9.49 12 inch king doll with royal garments and crown RYL02 FNG01 Queen doll 9.49 12 inch queen doll with royal garments and crown >>>
四、扩展阅读
- SQLite tutorial
http://zetcode.com/db/sqlite/ - SQLite Python tutorial
http://zetcode.com/db/sqlitepythontutorial/
关键字: SELECT, FROM, DISTINCT, LIMIT, OFFSET,