[Sqlite] Sqlite的基本日常SQL操作語句彙總

序言：

嵌入式資料庫Sqlite的基本sql使用匯總，使用測試起來，與關係型資料庫mysql在語法上有很多的相似之處，先準備測試資料：

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，分組統計排序

GROUP BY 進行分組統計資料，命令如下：

sqlite> SELECT NAME, SUM(SALARY) SALARY_SUM, COUNT(1) COUNT_NUM FROM COMPANY GROUP BY NAME;

ORDER BY 進行排序，命令如下：

sqlite> SELECT NAME, SUM(SALARY) SALARY_SUM, COUNT(1) COUNT_NUM FROM COMPANY GROUP BY NAME ORDER BY SALARY_SUM ASC;

HAVING 字句過濾資料記錄，命令如下：

SELECT c.*,COUNT(1) COUNT_NUM FROM COMPANY c GROUP BY c.NAME HAVING (COUNT_NUM) > 1 ORDER BY COUNT_NUM ;

PS：在一個查詢中，HAVING 子句必須放在 GROUP BY 子句之後，必須放在 ORDER BY 子句之前。下面是包含 HAVING 子句的 SELECT 語句的語法：

2，Limit分頁統計語句

SQLite 的 LIMIT 子句用於限制由 SELECT 語句返回的資料數量。

第一頁取值SQL： SELECT * FROM COMPANY ORDER BY ID LIMIT 0,3; 也可以從一個特定的偏移開始提取記錄，從第四位開始提取 3 個記錄，使用OFFSET關鍵字，SELECT * FROM COMPANY ORDER BY ID LIMIT 3 OFFSET 0；PS：首頁從0開始取值。

第二頁取值SQL： SELECT * FROM COMPANY ORDER BY ID LIMIT 3,3; 也可以從一個特定的偏移開始提取記錄，從第四位開始提取 3 個記錄，使用OFFSET關鍵字，SELECT * FROM COMPANY ORDER BY ID LIMIT 3 OFFSET 3；如下圖所示：

3，Glob匹配字句

SQLite 的 GLOB 運算子是用來匹配萬用字元指定模式的文字值。如果搜尋表示式與模式表示式匹配，GLOB 運算子將返回真（true），也就是 1。與 LIKE 運算子不同的是，GLOB 是大小寫敏感的，對於下面的萬用字元，它遵循 UNIX 的語法。

星號（*）

問號（?）

星號（*）代表零個、一個或多個數字或字元。問號（?）代表一個單一的數字或字元。這些符號可以被組合使用。

下面一些例項演示了帶有 '*' 和 '?' 運算子的 GLOB 子句不同的地方：

下面是一個例項，它顯示 COMPANY 表中 AGE 以 2 開頭的所有記錄，如下所示：

下面是一個例項，它顯示 COMPANY 表中 ADDRESS 文字里包含一個連字元（-）的所有記錄：

4，Distinct關鍵字過濾重複記錄

SQLite 的 DISTINCT 關鍵字與 SELECT 語句一起使用，來消除所有重複的記錄，並只獲取唯一一次記錄。

有可能出現一種情況，在一個表中有多個重複的記錄。當提取這樣的記錄時，DISTINCT 關鍵字就顯得特別有意義，它只獲取唯一一次記錄，而不是獲取重複記錄。

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> 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，對Null值的處理

往表裡面錄入Null值

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

sqlite>

修改某個欄位為null值

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

sqlite>

查詢為null的記錄

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])

例項如下：

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 ]

例項如下：

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) ]

例項如下：

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) ]

例項如下：

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>

建立索引，再進行EXPLAIN分析檢視結果，走了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，刪除重複資料但是一條記錄

錄入測試資料

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>

檢視重複記錄數

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來刪除重複記錄

sqlite> DELETE FROM COMPANY WHERE rowid NOT IN(SELECT MAX(rowid) rowid FROM COMPANY GROUP BY NAME);

sqlite>

再檢視最新的資料記錄，已經刪除了重複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>

----------------------------------------------------------------------------------------------------------------

原部落格地址：http://blog.itpub.net/26230597/abstract/1/
原作者：黃杉 (mchdba)

----------------------------------------------------------------------------------------------------------------

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26230597/viewspace-1289857/，如需轉載，請註明出處，否則將追究法律責任。

[Sqlite] Sqlite的基本日常SQL操作語句彙總

1，分組統計排序

2，Limit分頁統計語句

3，Glob匹配字句

4，Distinct關鍵字過濾重複記錄

5，字串連線操作

6，對Null值的處理

7，子查詢

8，EXPLAIN分析

相關文章