[Sqlite] 移动嵌入式数据库Sqlite日报SQL操作语句汇总

,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>

[Sqlite] 移动嵌入式数据库Sqlite日报SQL操作语句汇总----------------------------------------------------------------------------------------------------------------

<版权全部。文章同意转载,但必须以链接方式注明源地址,否则追究法律责任!>
原博客地址:http://blog.csdn.net/mchdba/article/details/39826365
原作者:黄杉 (mchdba)

[Sqlite] 移动嵌入式数据库Sqlite日报SQL操作语句汇总----------------------------------------------------------------------------------------------------------------

參考文章:http://www.w3cschool.cc/sqlite/sqlite-tutorial.html

版权声明:本文博客原创文章,博客,未经同意,不得转载。

上一篇:PetaPoco T4模板修改生成实体


下一篇:深度学习网络层之 Pooling