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>