[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','Rich-Mond',65000); INSERT INTO "COMPANY" VALUES(5,'David',27,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); 1，分组统计排序GROUP BY进行分组统计数据，命令如下： sqlite> SELECT NAME,SUM(SALARY) SALARY_SUM,COUNT(1) COUNT_NUM FROM COMPANY GROUP BY NAME; ORDER BY进行排序，命令如下： sqlite> SELECT NAME,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；如下图所示： 3Glob匹配字句SQLite的GLOB运算符是用来匹配通配符指定模式的文本值。如果搜索表达式与模式表达式匹配，GLOB运算符将返回真（true），也就是1。与LIKE运算符不同的是，GLOB是大小写敏感的，对于下面的通配符，它遵循UNIX的语法。 星号 （*） 问号 （?） 星号（*）代表零个、一个或多个数字或字符。问号（?）代表一个单一的数字或字符。这些符号可以被组合使用。 下面一些实例演示了 带有'*'和'?'运算符的GLOB子句不同的地方： 下面是一个实例，它显示COMPANY表中AGE以2开头的所有记录，如下所示： 下面是一个实例，它显示COMPANY表中ADDRESS文本里包含一个连字符（-）的所有记录： 4Distinct关键字过滤重复记录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值的处理往表里面录入值 sqlite> INSERT INTO COMPANY(ID,NAME,AGE,ADDRESS,SALARY) VALUES(8,18000); sqlite> 修改某个字段为null值 sqlite> UPDATE COMPANY SET SALARY = NULL WHERE ID=8; sqlite> 查询为的记录 sqlite> SELECT * FROM COMPANY WHERE ADDRESS IS NULL; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 7 James 24 10000 8 Xiaoteng 29 sqlite> 查询不为的记录 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 ); 8EXPLAIN分析没有建立索引之前，分析都是表扫描： 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,2)); INSERT INTO "COMPANY" VALUES(2,'Houston',10000); INSERT INTO "COMPANY" VALUES(7,28,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://www.w3cschool.cc/sqlite/sqlite-tutorial.html （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |