[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 CO
序言：
嵌入式数据库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
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
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
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
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>
参考文章：http://www.w3cschool.cc/sqlite/sqlite-tutorial.html
热AI工具
Undresser.AI Undress
人工智能驱动的应用程序，用于创建逼真的裸体照片
AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。
Undress AI Tool
免费脱衣服图片
Clothoff.io
AI脱衣机
AI Hentai Generator
免费生成ai无尽的。
热门文章
热工具
记事本++7.3.1
好用且免费的代码编辑器
SublimeText3汉化版
中文版，非常好用
禅工作室 13.0.1
功能强大的PHP集成开发环境
Dreamweaver CS6
视觉化网页开发工具
SublimeText3 Mac版
神级代码编辑软件(SublimeText3)
Hibernate 框架中 HQL 和 SQL 的区别是什么？
Apr 17, 2024 pm 02:57 PM
HQL和SQL在Hibernate框架中进行比较：HQL（1.面向对象语法，2.数据库无关的查询，3.类型安全），而SQL直接操作数据库（1.与数据库无关的标准，2.可执行复杂查询和数据操作）。
6000 毫安硅负极电池！小米 15Pro 升级再曝料
Jul 24, 2024 pm 12:45 PM
7月23日消息，博主数码闲聊站爆料称，小米15Pro电池容量增大至6000mAh，支持90W有线闪充，这将是小米数字系列电池最大的Pro机型。此前数码闲聊站透露，小米15Pro的电池拥有超高能量密度，硅含量远高于竞品。硅基电池在2023年大规模试水后，第二代硅负极电池被确定为行业未来发展方向，今年将迎来直接竞争的高峰。1.硅的理论克容量可达4200mAh/g，是石墨克容量的10倍以上（石墨的理论克容量372mAh/g）。对于负极而言，当锂离子嵌入量达到最大时的容量为理论克容量，这意味着相同重量下
在PHP中使用MySQLi建立数据库连接的详尽教程
Jun 04, 2024 pm 01:42 PM
如何在PHP中使用MySQLi建立数据库连接：包含MySQLi扩展（require_once）创建连接函数（functionconnect_to_db）调用连接函数（$conn=connect_to_db()）执行查询（$result=$conn->query()）关闭连接（$conn->close()）
Hibernate 如何实现多态映射？
Apr 17, 2024 pm 12:09 PM
Hibernate多态映射可映射继承类到数据库，提供以下映射类型：joined-subclass：为子类创建单独表，包含父类所有列。table-per-class：为子类创建单独表，仅包含子类特有列。union-subclass：类似joined-subclass，但父类表联合所有子类列。
iOS 18 新增'已恢复”相册功能 可找回丢失或损坏的照片
Jul 18, 2024 am 05:48 AM
苹果公司最新发布的iOS18、iPadOS18以及macOSSequoia系统为Photos应用增添了一项重要功能，旨在帮助用户轻松恢复因各种原因丢失或损坏的照片和视频。这项新功能在Photos应用的"工具"部分引入了一个名为"已恢复"的相册，当用户设备中存在未纳入其照片库的图片或视频时，该相册将自动显示。"已恢复"相册的出现为因数据库损坏、相机应用未正确保存至照片库或第三方应用管理照片库时照片和视频丢失提供了解决方案。用户只需简单几步
SPARKLE 撼与推出英特尔锐炫 Arc 嵌入式显卡，提供最长 5 年支持服务
Apr 22, 2024 am 11:52 AM
本站4月22日消息，SPARKLE撼与科技宣布推出英特尔锐炫Arc嵌入式显卡，包含PCIe版的A310E、A380E及MXM版的A350E、A370E显卡，承诺最长5年支持服务。SPARKLE英特尔锐炫ArcA380E显卡IA30GC-TN4E该显卡TBP（本站注：TotalBoardPower）为75W，采用双槽单风扇设计、6GBGDDR6显存，提供1个HDMI与3个DisplayPort接口，5年支持服务。SPARKLE英特尔锐炫ArcA380E半高显卡IA30GBL-TN4E这款半高显卡T
深入解析HTML如何读取数据库
Apr 09, 2024 pm 12:36 PM
HTML无法直接读取数据库，但可以通过JavaScript和AJAX实现。其步骤包括建立数据库连接、发送查询、处理响应和更新页面。本文提供了利用JavaScript、AJAX和PHP来从MySQL数据库读取数据的实战示例，展示了如何在HTML页面中动态显示查询结果。该示例使用XMLHttpRequest建立数据库连接，发送查询并处理响应，从而将数据填充到页面元素中，实现了HTML读取数据库的功能。
如何在PHP中处理数据库连接错误
Jun 05, 2024 pm 02:16 PM
PHP中处理数据库连接报错，可以使用以下步骤：使用mysqli_connect_errno()获取错误代码。使用mysqli_connect_error()获取错误消息。通过捕获并记录这些错误信息，可以轻松识别并解决数据库连接问题，确保应用程序的顺畅运行。
