sqllite.docx
《sqllite.docx》由会员分享,可在线阅读,更多相关《sqllite.docx(13页珍藏版)》请在冰豆网上搜索。
![sqllite.docx](https://file1.bdocx.com/fileroot1/2023-4/17/35d4246c-0aa7-42b3-b700-12620c0ff777/35d4246c-0aa7-42b3-b700-12620c0ff7771.gif)
sqllite
Sqlite的基本日常SQL操作语句汇总
嵌入式数据库Sqlite的基本sql使用汇总,使用测试起来,与关系型数据库mysql在语法上有很多的相似之处,先准备测试数据:
CREATETABLECOMPANY(IDINTNOTNULL,NAMEVARCHAR(20),AGEINT,ADDRESSVARCHAR(20),SALARYDECIMAL(7,2));
INSERTINTO"COMPANY"VALUES(1,'Paul',32,'California',20000);
INSERTINTO"COMPANY"VALUES(2,'Allen',25,'Texas',15000);
INSERTINTO"COMPANY"VALUES(3,'Teddy',23,'Norway',20000);
INSERTINTO"COMPANY"VALUES(4,'Mark',25,'Rich-Mond',65000);
INSERTINTO"COMPANY"VALUES(5,'David',27,'Texas',85000);
INSERTINTO"COMPANY"VALUES(6,'Kim',22,'South-Hall',45000);
INSERTINTO"COMPANY"VALUES(7,'James',24,NULL,10000);
INSERTINTO"COMPANY"VALUES(8,'Xiaoteng',29,NULL,NULL);
1,分组统计排序
GROUPBY 进行分组统计数据,命令如下:
sqlite>SELECTNAME,SUM(SALARY)SALARY_SUM,COUNT
(1)COUNT_NUMFROMCOMPANYGROUPBYNAME;
ORDERBY 进行排序,命令如下:
sqlite>SELECTNAME,SUM(SALARY)SALARY_SUM,COUNT
(1)COUNT_NUMFROMCOMPANYGROUPBYNAMEORDERBYSALARY_SUMASC;
HAVING 字句过滤数据记录,命令如下:
SELECTc.*,COUNT
(1)COUNT_NUMFROMCOMPANYcGROUPBYc.NAMEHAVING(COUNT_NUM)>1ORDERBYCOUNT_NUM;
PS:
在一个查询中,HAVING 子句必须放在 GROUPBY 子句之后,必须放在 ORDERBY 子句之前。
下面是包含 HAVING 子句的 SELECT 语句的语法:
2,Limit分页统计语句
SQLite 的 LIMIT 子句用于限制由 SELECT 语句返回的数据数量。
第一页取值SQL:
SELECT*FROMCOMPANYORDERBYIDLIMIT0,3; 也可以从一个特定的偏移开始提取记录,从第四位开始提取 3 个记录,使用OFFSET关键字,SELECT*FROMCOMPANYORDERBYIDLIMIT3OFFSET0;PS:
首页从0开始取值。
第二页取值SQL:
SELECT*FROMCOMPANYORDERBYIDLIMIT3,3; 也可以从一个特定的偏移开始提取记录,从第四位开始提取 3 个记录,使用OFFSET关键字,SELECT*FROMCOMPANYORDERBYIDLIMIT3OFFSET3;如下图所示:
3,Glob匹配字句
SQLite 的 GLOB 运算符是用来匹配通配符指定模式的文本值。
如果搜索表达式与模式表达式匹配,GLOB 运算符将返回真(true),也就是 1。
与 LIKE 运算符不同的是,GLOB 是大小写敏感的,对于下面的通配符,它遵循 UNIX 的语法。
星号(*)
问号(?
)
星号(*)代表零个、一个或多个数字或字符。
问号(?
)代表一个单一的数字或字符。
这些符号可以被组合使用。
下面一些实例演示了带有 '*' 和 '?
' 运算符的 GLOB 子句不同的地方:
下面是一个实例,它显示 COMPANY 表中 AGE 以 2 开头的所有记录,如下所示:
下面是一个实例,它显示 COMPANY 表中 ADDRESS 文本里包含一个连字符(-)的所有记录:
4,Distinct关键字过滤重复记录
SQLite的 DISTINCT 关键字与SELECT语句一起使用,来消除所有重复的记录,并只获取唯一一次记录。
有可能出现一种情况,在一个表中有多个重复的记录。
当提取这样的记录时,DISTINCT关键字就显得特别有意义,它只获取唯一一次记录,而不是获取重复记录。
5,字符串连接操作
问题地址:
sqlite>CREATETABLEt1(idint,namevarchar(60));
sqlite>INSERTINTO"t1"VALUES(4,'1@');
sqlite>select*fromt1;
id name
---------- ----------
4 1@
sqlite>updatet1setname=(id/2)||substr(name,instr(name,'@'),length(name)-instr(name,'@')+1) whereid=4;
sqlite>select*fromt1;
id name
---------- ----------
4 2@
sqlite>
6,对Null值的处理
往表里面录入Null值
sqlite>INSERTINTOCOMPANY(ID,NAME,AGE,ADDRESS,SALARY)VALUES(8,'Xiaoteng',29,NULL,18000);
sqlite>
修改某个字段为null值
sqlite>UPDATECOMPANYSETSALARY=NULLWHEREID=8;
sqlite>
查询为null的记录
sqlite>SELECT*FROMCOMPANYWHEREADDRESSISNULL;
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
7 James 24 10000
8 Xiaoteng 29
sqlite>
查询不为null的记录
sqlite>SELECT*FROMCOMPANYWHEREADDRESSISNOTNULL;
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中的基本语法如下:
SELECTcolumn_name[,column_name]
FROM table1[,table2]
WHERE column_nameOPERATOR
(SELECTcolumn_name[,column_name]
FROMtable1[,table2]
[WHERE])
实例如下:
sqlite>SELECT*FROMCOMPANYWHEREIDIN(SELECTIDFROMCOMPANYWHERESALARY>45000);
INSERT语句中的子查询使用,基本语法:
INSERTINTOtable_name[(column1[,column2])]
SELECT[*|column1[,column2]
FROMtable1[,table2]
[WHEREVALUEOPERATOR]
实例如下:
sqlite>INSERTINTOCOMPANY_BKP
SELECT*FROMCOMPANY
WHEREIDIN(SELECTID
FROMCOMPANY);
UPDATE语句中的子查询使用,基本语法如下:
UPDATEtable
SETcolumn_name=new_value
[WHEREOPERATOR[VALUE]
(SELECTCOLUMN_NAME
FROMTABLE_NAME)
[WHERE)]
实例如下:
sqlite>UPDATECOMPANY
SETSALARY=SALARY*0.50
WHEREAGEIN(SELECTAGEFROMCOMPANY_BKP
WHEREAGE>=27);
DELETE语句中的子查询使用,语法如下:
DELETEFROMTABLE_NAME
[WHEREOPERATOR[VALUE]
(SELECTCOLUMN_NAME
FROMTABLE_NAME)
[WHERE)]
实例如下:
sqlite>DELETEFROMCOMPANY
WHEREAGEIN(SELECTAGEFROMCOMPANY_BKP
WHEREAGE>27);
8,EXPLAIN分析
没有建立索引之前,分析都是表扫描:
sqlite>EXPLAINSELECT* FROMCOMPANY WHERESalary<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>EXPLAINQUERYPLANSELECT*FROMCOMPANYWHERESalary<20000;
order from detail
---------- ---------- -------------
0 0 TABLECOMPANY
sqlite>
建立索引,再进行EXPLAIN分析查看结果,走了idx_sal索引扫描:
sqlite>CREATEINDEXidx_salONCOMPANY(SALARY);
sqlite>EXPLAINSELECT* FROMCOMPANY WHERESalary<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