Sqlite移动嵌入式数据库Sqlite的日常SQL操作语句汇总|江阴雨辰互联

2023年6月27日发(作者：)

Sqlite移动嵌⼊式数据库Sqlite的⽇常SQL操作语句汇总序⾔：嵌⼊式数据库Sqlite的基本sql使⽤汇总，使⽤测试起来，与关系型数据库mysql在语法上有很多的相似之处，先准备测试数据：CREATE TABLE COMPANY(ID INT NOT NULL, NAME VARCHAR(20),AGE INT,ADDRESS VARCHAR(20),SALARYDECIMAL(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 ORDERBY SALARY_SUM ASC;

HAVING 字句过滤数据记录，命令如下：SELECT c.*,COUNT(1) COUNT_NUM FROM COMPANY c GROUP BY HAVING (COUNT_NUM) > 1 ORDER BYCOUNT_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，字符串连接操作sqlite> CREATE TABLE t1(id int, name varchar(60));sqlite> INSERT INTO "t1" VALUES(4,'1@');sqlite> select * from t1;id name

---------- ----------4 1@te> 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@te>

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_BKPSELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY) ;UPDATE语句中的⼦查询使⽤，基本语法如下：UPDATE tableSET 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 COMPANYsqlite>

建⽴索引，再进⾏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_salsqlite>

9，删除重复数据并且保留最新⼀条记录

录⼊测试数据sqlite> .dumpPRAGMA foreign_keys=OFF;BEGIN TRANSACTION;CREATE TABLE COMPANY(ID INT NOT NULL, NAME VARCHAR(20),AGE INT,ADDRESS VARCHAR(20),SALARYDECIMAL(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