原文地址：http://blog.itpub.net/26230597/viewspace-1289857/
序言：
** ****嵌入式數(shù)據(jù)庫****Sqlite****的基本****sql****使用匯總鱼的，使用測(cè)試起來，與關(guān)系型數(shù)據(jù)庫****mysql****在語法上有很多的相似之處痘煤，先準(zhǔn)備測(cè)試數(shù)據(jù)：**
CREATE TABLE COMPANY(ID INT NOT NULL, NAME VARCHAR(20),AGE INT,ADDRESS VARCHAR(20),SALARY DECIMAL(7,2));
INSERT INTO "COMPANY" VALUES(1,'Paul',32,'California',20000);
INSERT INTO "COMPANY" VALUES(2,'Allen',25,'Texas',15000);
INSERT INTO "COMPANY" VALUES(3,'Teddy',23,'Norway',20000);
INSERT INTO "COMPANY" VALUES(4,'Mark',25,'Rich-Mond',65000);
INSERT INTO "COMPANY" VALUES(5,'David',27,'Texas',85000);
INSERT INTO "COMPANY" VALUES(6,'Kim',22,'South-Hall',45000);
INSERT INTO "COMPANY" VALUES(7,'James',24,NULL,10000);
INSERT INTO "COMPANY" VALUES(8,'Xiaoteng',29,NULL,NULL);
1凑阶，分組統(tǒng)計(jì)排序
GROUP BY ****進(jìn)行分組統(tǒng)計(jì)數(shù)據(jù)，命令如下：
sqlite> SELECT NAME, SUM(SALARY) SALARY_SUM, COUNT(1) COUNT_NUM FROM COMPANY GROUP BY NAME;

****

2，Limit分頁統(tǒng)計(jì)語句
SQLite 的 LIMIT 子句用于限制由 SELECT 語句返回的數(shù)據(jù)數(shù)量都伪。
第一頁取值SQL： SELECT * FROM COMPANY ORDER BY ID LIMIT 0,3; 也可以從一個(gè)特定的偏移開始提取記錄呕乎，從第四位開始提取 3 個(gè)記錄，使用OFFSET關(guān)鍵字陨晶，SELECT * FROM COMPANY ORDER BY ID LIMIT 3 OFFSET 0猬仁；PS：首頁從0開始取值。

第二頁取值SQL： SELECT * FROM COMPANY ORDER BY ID LIMIT 3,3; 也可以從一個(gè)特定的偏移開始提取記錄先誉，從第四位開始提取 3 個(gè)記錄湿刽，使用OFFSET關(guān)鍵字，SELECT * FROM COMPANY ORDER BY ID LIMIT 3 OFFSET 3褐耳；如下圖所示：

3诈闺，Glob匹配字句
SQLite 的 GLOB 運(yùn)算符是用來匹配通配符指定模式的文本值。如果搜索表達(dá)式與模式表達(dá)式匹配铃芦，GLOB 運(yùn)算符將返回真（true）雅镊，也就是 1。與 LIKE 運(yùn)算符不同的是刃滓，GLOB 是大小寫敏感的仁烹，對(duì)于下面的通配符，它遵循 UNIX 的語法咧虎。
星號(hào) （）
問號(hào) （?）
星號(hào)（）代表零個(gè)卓缰、一個(gè)或多個(gè)數(shù)字或字符。問號(hào)（?）代表一個(gè)單一的數(shù)字或字符。這些符號(hào)可以被組合使用征唬。
下面一些實(shí)例演示了 帶有 '*' 和 '?' 運(yùn)算符的 GLOB 子句不同的地方：

下面是一個(gè)實(shí)例震叮，它顯示 COMPANY 表中 AGE 以 2 開頭的所有記錄，如下所示：

下面是一個(gè)實(shí)例鳍鸵，它顯示 COMPANY 表中 ADDRESS 文本里包含一個(gè)連字符（-）的所有記錄：

5囱怕，字符串連接操作
問題地址：http://bbs.csdn.net/topics/390886865
sqlite> CREATE TABLE t1(id int, name varchar(60));
sqlite> INSERT INTO "t1" VALUES(4,'1@test.cn');
sqlite> select * from t1;

id name

4 1@test.cn

sqlite>

sqlite> update t1 set name=(id/2)||substr(name,instr(name,'@'),length(name)-instr(name,'@')+1)  where id=4;
sqlite> select * from t1;

id name

4 2@test.cn
sqlite>

6霍弹，對(duì)Null值的處理
往表里面錄入Null值

sqlite> INSERT INTO COMPANY(ID,NAME,AGE,ADDRESS,SALARY) VALUES(8,'Xiaoteng',29,NULL,18000);

sqlite>
修改某個(gè)字段為null值

sqlite> UPDATE COMPANY SET SALARY = NULL WHERE ID=8;

sqlite>
查詢?yōu)閚ull的記錄

sqlite> SELECT * FROM COMPANY WHERE ADDRESS IS NULL;

ID NAME AGE ADDRESS SALARY

7 James 24 10000
8 Xiaoteng 29
sqlite>
查詢不為null的記錄

sqlite> SELECT * FROM COMPANY WHERE ADDRESS IS NOT NULL;

ID NAME AGE ADDRESS SALARY

1 Paul 32 California 20000
2 Allen 25 Texas 15000
3 Teddy 23 Norway 20000
4 Mark 25 Rich-Mond 65000
5 David 27 Texas 85000
6 Kim 22 South-Hall 45000
sqlite>

7，子查詢
SELECt中的基本語法如下：
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE])
實(shí)例如下：
sqlite> SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY WHERE SALARY > 45000);

INSERT語句中的子查詢使用娃弓，基本語法：
INSERT INTO table_name [ (column1 [, column2 ]) ]
SELECT [ *|column1 [, column2 ]
FROM table1 [, table2 ]
[ WHERE VALUE OPERATOR ]
實(shí)例如下：
sqlite> INSERT INTO COMPANY_BKP
SELECT * FROM COMPANY
WHERE ID IN (SELECT ID
FROM COMPANY) ;
UPDATE語句中的子查詢使用典格，基本語法如下：
UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]
實(shí)例如下：
sqlite> UPDATE COMPANY
SET SALARY = SALARY * 0.50
WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
WHERE AGE >= 27 );
DELETE語句中的子查詢使用，語法如下：
DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]
實(shí)例如下：
sqlite> DELETE FROM COMPANY
WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
WHERE AGE > 27 );

8台丛，EXPLAIN分析
沒有建立索引之前耍缴，分析都是表掃描：
sqlite> EXPLAIN SELECT * FROM COMPANY WHERE Salary < 20000;
addr opcode p1 p2 p3 p4 p5 comment

0 Trace 0 0 0 00
1 Integer 20000 1 0 00
2 Goto 0 16 0 00
3 OpenRead 0 2 0 5 00
4 Rewind 0 14 0 00
5 Column 0 4 2 00
6 Ge 1 13 2 collseq(BI 6b
7 Column 0 0 4 00
8 Column 0 1 5 00
9 Column 0 2 6 00
10 Column 0 3 7 00
11 Column 0 4 8 00
12 ResultRow 4 5 0 00
13 Next 0 5 0 01
14 Close 0 0 0 00
15 Halt 0 0 0 00
16 Transactio 0 0 0 00
17 VerifyCook 0 1 0 00
18 TableLock 0 2 0 COMPANY 00
19 Goto 0 3 0 00
sqlite> EXPLAIN QUERY PLAN SELECT * FROM COMPANY WHERE Salary < 20000;
order from detail

0 0 TABLE COMPANY
sqlite>

建立索引，再進(jìn)行****EXPLAIN****分析查看結(jié)果挽霉，走了****idx_sal****索引掃描：
sqlite> CREATE INDEX idx_sal ON COMPANY(SALARY);
sqlite> EXPLAIN SELECT * FROM COMPANY WHERE Salary < 20000;
addr opcode p1 p2 p3 p4 p5 comment

0 Trace 0 0 0 00
1 Integer 20000 1 0 00
2 Goto 0 25 0 00
3 OpenRead 0 2 0 5 00
4 OpenRead 1 3 0 keyinfo(1, 00
5 Affinity 2 0 0 cb 00
6 Rewind 1 22 2 0 00
7 SCopy 1 2 0 00
8 IsNull 2 22 0 00
9 Affinity 2 1 0 cb 00
10 IdxGE 1 22 2 1 00
11 Column 1 0 3 00
12 IsNull 3 21 0 00
13 IdxRowid 1 3 0 00
14 Seek 0 3 0 00
15 Column 0 0 4 00
16 Column 0 1 5 00
17 Column 0 2 6 00
18 Column 0 3 7 00
19 Column 1 0 8 00
20 ResultRow 4 5 0 00
21 Next 1 10 0 00
22 Close 0 0 0 00
23 Close 1 0 0 00
24 Halt 0 0 0 00
25 Transactio 0 0 0 00
26 VerifyCook 0 2 0 00
27 TableLock 0 2 0 COMPANY 00
28 Goto 0 3 0 00
sqlite> EXPLAIN QUERY PLAN SELECT * FROM COMPANY WHERE Salary < 20000;
order from detail

0 0 TABLE COMPANY WITH INDEX idx_sal
sqlite>******9****防嗡，刪除重復(fù)數(shù)據(jù)但是一條記錄**
錄入測(cè)試數(shù)據(jù)
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE COMPANY(ID INT NOT NULL, NAME VARCHAR(20),AGE INT,ADDRESS VARCHAR(20),SALARY DECIMAL(7,2));
INSERT INTO "COMPANY" VALUES(2,'Allen',25,'Texas',15000);
INSERT INTO "COMPANY" VALUES(3,'Teddy',23,'Norway',20000);
INSERT INTO "COMPANY" VALUES(4,'Mark',25,'Rich-Mond',65000);
INSERT INTO "COMPANY" VALUES(5,'David',27,'Texas',85000);
INSERT INTO "COMPANY" VALUES(6,'Kim',22,'South-Hall',45000);
INSERT INTO "COMPANY" VALUES(7,'James',24,'Houston',10000);
INSERT INTO "COMPANY" VALUES(7,'James',28,'Houston',20000);
INSERT INTO "COMPANY" VALUES(4,'Mark',29,'Rich-Mond',95000);
COMMIT;
sqlite>

查看重復(fù)記錄數(shù)
sqlite> select * from company order by name;
ID NAME AGE ADDRESS SALARY

2 Allen 25 Texas 15000
5 David 27 Texas 85000
7 James 24 Houston 10000
7 James 28 Houston 20000
6 Kim 22 South-Hall 45000
4 Mark 25 Rich-Mond 65000
4 Mark 29 Rich-Mond 95000
3 Teddy 23 Norway 20000
sqlite>

通過rowid來刪除重復(fù)記錄
sqlite> DELETE FROM COMPANY WHERE rowid NOT IN(SELECT MAX(rowid) rowid FROM COMPANY GROUP BY NAME);
sqlite>

再查看最新的數(shù)據(jù)記錄，已經(jīng)刪除了重復(fù)NAME的記錄
sqlite> select * from company;
ID NAME AGE ADDRESS SALARY

2 Allen 25 Texas 15000
3 Teddy 23 Norway 20000
5 David 27 Texas 85000
6 Kim 22 South-Hall 45000
7 James 28 Houston 20000
4 Mark 29 Rich-Mond 95000
sqlite>

<版權(quán)所有侠坎，文章允許轉(zhuǎn)載蚁趁，但必須以鏈接方式注明源地址，否則追究法律責(zé)任!>原博客地址：http://blog.itpub.net/26230597/abstract/1/原作者：黃杉 (mchdba)