SQLite 学习之路 (2) – 检索数据

Posted on by
目录 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
>>>

 

四、扩展阅读

关键字： SELECT, FROM,  DISTINCT,  LIMIT, OFFSET,

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.