SQLite 入门教程

发表于 1月以前  | 总阅读数:210 次

不是 MySQL 用不起,而是 SQLite 更有性价比,绝大多数的应用 SQLite 都可以满足。

SQLite 是一个用 C 语言编写的开源、轻量级、快速、独立且高可靠性的 SQL 数据库引擎,它提供了功能齐全的数据库解决方案。SQLite 几乎可以在所有的手机和计算机上运行,它被嵌入到无数人每天都在使用的众多应用程序中。

此外,SQLite 还具有稳定的文件格式、跨平台能力和向后兼容性等特点。SQLite 的开发者承诺,至少在 2050 年之前保持该文件格式不变。

本文将介绍 SQLite 的基础知识和使用方法。

SQLite 安装

在 SQLite 官方页面[1](https://sqlite.org/download.html) 下载适合你目标系统的压缩包。

下载并解压后,无论是在 Windows、Linux 还是 Mac OS 系统上,你都可以得到一个 sqlite3 命令行工具。

以下是在 Mac OS 上解压后得到的命令行工具示例:

➜  sqlite-tools-osx-x64-3450100 ls -l
total 14952
-rwxr-xr-x@ 1 darcy  staff  1907136  1 31 00:27 sqldiff
-rwxr-xr-x@ 1 darcy  staff  2263792  1 31 00:25 sqlite3
-rwxr-xr-x@ 1 darcy  staff  3478872  1 31 00:27 sqlite3_analyzer

SQLite 使用场景

SQLite 与客户端/服务器类型的 SQL 数据库引擎(例如 MySQL、Oracle、PostgreSQL 或 SQL Server)不同,它们解决的问题也不同。

服务器端的 SQL 数据库引擎旨在实现企业级数据的共享存储,它们强调的是可扩展性、并发性、集中化和控制性。相比之下,SQLite 通常用于为个人应用程序和设备提供本地数据存储,它强调的是经济、高效、可靠、独立和简单

SQLite 的使用场景:

  1. 1. 嵌入式设备和物联网SQLite 不需要额外的管理或服务启动,非常适合用在手机、电视、机顶盒、游戏机、相机、手表等智能设备上。
  2. 2. 网站多数低流量网站可以使用 SQLite 作为数据库。根据官方网站的介绍,通常每天访问量少于 10 万次的网站都可以很好地运行 SQLite。SQLite 的官方网站(https://www.sqlite.org/)自身就使用 SQLite 作为数据库引擎,每天处理大约 50 万 HTTP 请求,其中约 15-20% 的请求涉及数据库查询
  3. 3. 数据分析SQLite3 命令行工具能方便地与 CSV 和 Excel 文件进行交互操作,适合分析大数据集。同时,许多语言(如 Python)都内置了 SQLite 支持,可以轻松编写脚本进行数据操作。
  4. 4. 缓存SQLite 可以作为应用服务的缓存,减轻对中心数据库的压力。
  5. 5. 内存或者临时数据库得益于 SQLite 的简单快速,非常使用程序演示或者日常测试。

SQLite 不适合的场景包括

  1. 1. 需要通过网络访问数据库的情况。SQLite 是一个本地文件数据库,没有提供远程访问功能。
  2. 2. 要求高可用性和可扩展性的场合。SQLite 简单易用,但不可扩展。
  3. 3. 数据量极大时。尽管 SQLite 数据库大小的限制高达 281 TB,但所有数据都必须存储在单个磁盘上。
  4. 4. 写入操作高并发时。SQLite 在任何时刻只允许一个写入操作执行,其他写入操作需要排队。

SQLite3 命令操作

SQLite 提供了 sqlite3(在windows 为 sqlite3.exe)命令行工具,通过该工具可以执行 SQLite 数据库操作和 SQL 语句。

直接在命令提示符下执行 ./sqlite3 启动 sqlite3 程序,然后可以通过输入 .help 查看帮助指南,或者输入 .help 关键词 获取特定关键词的帮助信息。

部分命令列表如下:

sqlite> .help
.databases               List names and files of attached databases
.dbconfig ?op? ?val?     List or change sqlite3_db_config() options
.dbinfo ?DB?             Show status information about the database
.excel                   Display the output of next command in spreadsheet
.exit ?CODE?             Exit this program with return-code CODE
.expert                  EXPERIMENTAL. Suggest indexes for queries
.explain ?on|off|auto?   Change the EXPLAIN formatting mode.  Default: auto
.help ?-all? ?PATTERN?   Show help text for PATTERN
.hex-rekey OLD NEW NEW   Change the encryption key using hexadecimal
.indexes ?TABLE?         Show names of indexes
.mode MODE ?OPTIONS?     Set output mode
.open ?OPTIONS? ?FILE?   Close existing database and reopen FILE
.output ?FILE?           Send output to FILE or stdout if FILE is omitted
.quit                    Exit this program
.read FILE               Read input from FILE or command output
.schema ?PATTERN?        Show the CREATE statements matching PATTERN
.show                    Show the current values for various settings
.tables ?TABLE?          List names of tables matching LIKE pattern TABLE
.......

sqlite3 只是读取输入行信息,然后传递给 SQLite 库来执行,SQL 语句都要以分号 ; 结尾才会开始执行,因此你可以自由的分行输入。

在 sqlite3 中,SQL 语句需以分号 ; 结尾才会执行,允许跨行输入。特殊的点命令(如 .help.tables)以小数点 . 开头,不需要分号

SQLite 新建数据库

直接执行 sqlite3 filename 打开或创建一个 SQLite 数据库。如果文件不存在,SQLite 会自动创建它。

示例:打开或创建名为 my_sqlite.db 的 SQLite 数据库文件。

$ sqlite3 my_sqlite.db
SQLite version 3.39.5 2022-10-14 20:58:05
Enter ".help" for usage hints.
sqlite>

也可以首先创建一个空白文件,然后使用 sqlite3 命令打开它。接下来使用 CREATE TABLE 命令创建一个名为 user 的表,用 .tables 命令查看现有表格,使用 .exit 退出 sqlite3 工具。

$ touch test.db
$ sqlite3 test.db
SQLite version 3.39.5 2022-10-14 20:58:05
Enter ".help" for usage hints.
sqlite> create table user(name text,age int);
sqlite> .tables
user
sqlite>

SQLite 查看当前数据库

使用点命令 .databases 查看当前打开的数据库。

sqlite> .databases
main: /Users/darcy/develop/sqlite-tools-osx-x86-3420000/my_sqlite.db r/w
sqlite>

SQLite 增删改查

SQLite 几乎完全兼容常见的 SQL 语句规范,因此可以直接编写和执行标准的 SQL 语句。

创建表:

sqlite> create table user(name text,age int);
sqlite>

插入数据:

sqlite> insert into user values('aLang',20);
sqlite> insert into user values('Darcy',30);
sqlite> insert into user values('XiaoMing',40);sqlite> select * from user;
aLang|20
Darcy|30
XiaoMing|40

查询数据:

sqlite> create index user_name on user(name);

添加索引,为 user 表的 name 创建名为 user_name 的索引:

<pre style="color: rgb(171, 178, 191);background: rgb(40, 44, 52);font-size: 14px;letter-spacing: normal;text-align: left;line-height: 1.5;font-family: -apple-system-font, BlinkMacSystemFont, "Helvetica Neue", "PingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;overflow-x: auto;border-radius: 8px;margin: 10px 8px;"><span style="padding: initial;display: block;height: 25px;background-color: transparent;background-image: url("https://mmbiz.qpic.cn/mmbiz_svg/5mxuSU5RGhYBpo23VmJT9wVDjg1JrJjrGp88wZPI23stQgOTrNhYvTuD7Lb8ajHiaR9y16HgWsEbGEQagZStclf7MDnamaV30/640?wx_fmt=svg&from=appmsg");background-position: 14px 10px;background-repeat: no-repeat;background-size: 40px;">```
sqlite> <span style="color: rgb(198, 120, 221);">create index user_name <span style="color: rgb(198, 120, 221);">on <span style="color: rgb(198, 120, 221);">user(name);


SQLite 更改输出格式
-------------

在查询数据时,SQLite 默认使用 `|` 分割每列数据,这可能不便于阅读。实际上,sqlite3 工具支持多种输出格式,默认为 `list` 模式。

以下是可用的输出格式:ascii、**box**、csv、**column**、html、insert、**json**、line、list、markdown、quote、**table**。

可以使用 `.mode` 命令更改输出格式。

Box 格式:

sqlite> .mode box sqlite> select * from user; ┌──────────┬─────┐ │ name │ age │ ├──────────┼─────┤ │ aLang │ 20 │ │ Darcy │ 30 │ │ XiaoMing │ 40 │ └──────────┴─────┘


json 格式:

sqlite> .mode json sqlite> select * from user; [{"name":"aLang","age":20}, {"name":"Darcy","age":30}, {"name":"XiaoMing","age":40}]


column 格式:

sqlite> .mode column sqlite> select * from user; name age


aLang 20 Darcy 30 XiaoMing 40


table 格式:

sqlite> .mode table sqlite> select * from user; +----------+-----+ | name | age | +----------+-----+ | aLang | 20 | | Darcy | 30 | | XiaoMing | 40 | +----------+-----+ sqlite>



查询 Schema
---------

sqlite3 工具提供了几个方便的命令,可用于查看数据库的 schema ,这些命令纯粹作为快捷方式提供。

例如,`.table` 查看数据库中的所有表:

sqlite> .table user


点命令 `.table` 相当于下面的查询语句。

sqlite> SELECT name FROM sqliteschema ...> WHERE type IN ('table','view') AND name NOT LIKE 'sqlite%' ...> ; user


`sqlite_master` 是 SQLite 中的一个特殊表,其中包含了数据库的 schema 信息。你可以查询这个表以获取表的创建语句和索引信息。

sqlite> .mode table sqlite> select * from sqlite_schema; +-------+-----------+----------+----------+--------------------------------------+ | type | name | tbl_name | rootpage | sql | +-------+-----------+----------+----------+--------------------------------------+ | table | user | user | 2 | CREATE TABLE user(name text,age int) | | index | user_name | user | 3 | CREATE INDEX user_name on user(name) | +-------+-----------+----------+----------+--------------------------------------+


使用 `.indexes` 查看索引,使用 `.schema` 查看 schema 详情。

sqlite> .indexes user_name

sqlite> .schema CREATE TABLE user(name text,age int); CREATE INDEX user_name on user(name);



结果写出到文件
-------

使用 `.output filename` 命令将查询结果写入指定文件。

下面是一个示例,先使用 `.mode json` 更改输出为 JSON 格式,然后在查询表写出到 `sql_result.json`。

sqlite> .output sql_result.json sqlite> .mode json sqlite> select * from user; sqlite> .exit

$ cat sql_result.json [{"name":"aLang","age":20}, {"name":"Darcy","age":30}, {"name":"XiaoMing","age":40}]


\*\*写出并打开 EXCEL \*\*

使用 `.excel` 会让下一个查询语句输出到 Excel 中。

sqlite> .excel sqlite> select * from sqlite_schema;


![](https://oss-cn-hangzhou.aliyuncs.com/codingsky/cdn/img/2024-02-23/3635095389574fb420dec197c62bd4dc "null")
结果写出到文件

sqlite> .output sql_result.txt sqlite> select from sqlite_schema; sqlite> select from user;



读取运行 SQL 脚本
-----------

使用 `.read` 可以读取指定文件中的 SQL 语句并运行,这在需要批量执行 SQL 脚本的场景中非常有用。

创建SQL文件:

$ echo "select from user" > sql_query.sql $ cat sql_query.sql select from user

$ ./sqlite3 my_sqlite.db SQLite version 3.42.0 2023-05-16 12:36:15 Enter ".help" for usage hints.

sqlite> .mode table sqlite> .read sql_query.sql +----------+-----+ | name | age | +----------+-----+ | aLang | 20 | | Darcy | 30 | | XiaoMing | 40 | +----------+-----+ sqlite>



SQLite 备份与恢复
------------

在涉及数据库操作时,备份和恢复是至关重要的步骤,它们用于防止数据丢失并确保数据的持续性。SQLite 提供了简单的方法来备份和恢复你的数据库。

在 SQLite 中可以通过导出整个数据库为一个 SQL 脚本来备份数据库。此功能使用 `.dump` 命令实现。

$ ./sqlite3 my_sqlite.db SQLite version 3.42.0 2023-05-16 12:36:15 Enter ".help" for usage hints. sqlite> .output backup.sql sqlite> .dump sqlite> .exit

$ cat backup.sql PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE user(name text,age int); INSERT INTO user VALUES('aLang',20); INSERT INTO user VALUES('Darcy',30); INSERT INTO user VALUES('XiaoMing',40); CREATE INDEX user_name on user(name); COMMIT;


这将导出整个 `my_sqlite.db` 数据库到 `backup.sql` 文件中。此 SQL 文件包含了重建数据库所需的所有 SQL 语句。要恢复数据库,只需在 `sqlite3` 中运行这个脚本。

示例:恢复数据到库 `my_sqlite_2` 中。

$ ./sqlite3 my_sqlite_2.db SQLite version 3.42.0 2023-05-16 12:36:15 Enter ".help" for usage hints. sqlite> .read backup.sql sqlite> select * from user; aLang|20 Darcy|30 XiaoMing|40



这将执行 `backup.sql` 文件中的所有 SQL 语句,重建数据库。通过以上的备份与恢复方法,你可以确保你的 SQLite 数据库资料得到可靠的保护,且在需要时能够迅速恢复。


SQLite 可视化工具
------------

命令行操作总归不太直观,如果你喜欢可视化操作,可以下载 SQLite Database Browser 进行操作。

下载页面:https://sqlitebrowser.org/dl/

![](https://oss-cn-hangzhou.aliyuncs.com/codingsky/cdn/img/2024-02-23/48d648536724a0a83965f1760d1dcea5 "null")

附录
--

SQLite 常用函数列表,见名知意不写注释了。

![](https://oss-cn-hangzhou.aliyuncs.com/codingsky/cdn/img/2024-02-23/f3c1ffe72b134665b4e76e236ad6b356)

参考
--

1.  SQLite 开源代码:https://www.sqlite.org/cgi/src/dir?ci=trunk
2. SQLite 文件格式介绍:https://sqlite.org/fileformat2.html
3. SQLite 可视化工具:https://sqlitebrowser.org/dl/
4. SQL 函数文档:https://www.sqlite.org/lang\_corefunc.html


#### 引用链接

`[1]` SQLite 官方页面: *https://sqlite.org/download.html*

本文由哈喽比特于1月以前收录,如有侵权请联系我们。
文章来源:https://mp.weixin.qq.com/s/3kVS9-Y02vtMh3rNCiW2Wg

 相关推荐

刘强东夫妇:“移民美国”传言被驳斥

京东创始人刘强东和其妻子章泽天最近成为了互联网舆论关注的焦点。有关他们“移民美国”和在美国购买豪宅的传言在互联网上广泛传播。然而,京东官方通过微博发言人发布的消息澄清了这些传言,称这些言论纯属虚假信息和蓄意捏造。

发布于:7月以前  |  808次阅读  |  详细内容 »

博主曝三大运营商,将集体采购百万台华为Mate60系列

日前,据博主“@超能数码君老周”爆料,国内三大运营商中国移动、中国电信和中国联通预计将集体采购百万台规模的华为Mate60系列手机。

发布于:7月以前  |  770次阅读  |  详细内容 »

ASML CEO警告:出口管制不是可行做法,不要“逼迫中国大陆创新”

据报道,荷兰半导体设备公司ASML正看到美国对华遏制政策的负面影响。阿斯麦(ASML)CEO彼得·温宁克在一档电视节目中分享了他对中国大陆问题以及该公司面临的出口管制和保护主义的看法。彼得曾在多个场合表达了他对出口管制以及中荷经济关系的担忧。

发布于:7月以前  |  756次阅读  |  详细内容 »

抖音中长视频App青桃更名抖音精选,字节再发力对抗B站

今年早些时候,抖音悄然上线了一款名为“青桃”的 App,Slogan 为“看见你的热爱”,根据应用介绍可知,“青桃”是一个属于年轻人的兴趣知识视频平台,由抖音官方出品的中长视频关联版本,整体风格有些类似B站。

发布于:7月以前  |  648次阅读  |  详细内容 »

威马CDO:中国每百户家庭仅17户有车

日前,威马汽车首席数据官梅松林转发了一份“世界各国地区拥车率排行榜”,同时,他发文表示:中国汽车普及率低于非洲国家尼日利亚,每百户家庭仅17户有车。意大利世界排名第一,每十户中九户有车。

发布于:7月以前  |  589次阅读  |  详细内容 »

研究发现维生素 C 等抗氧化剂会刺激癌症生长和转移

近日,一项新的研究发现,维生素 C 和 E 等抗氧化剂会激活一种机制,刺激癌症肿瘤中新血管的生长,帮助它们生长和扩散。

发布于:7月以前  |  449次阅读  |  详细内容 »

苹果据称正引入3D打印技术,用以生产智能手表的钢质底盘

据媒体援引消息人士报道,苹果公司正在测试使用3D打印技术来生产其智能手表的钢质底盘。消息传出后,3D系统一度大涨超10%,不过截至周三收盘,该股涨幅回落至2%以内。

发布于:7月以前  |  446次阅读  |  详细内容 »

千万级抖音网红秀才账号被封禁

9月2日,坐拥千万粉丝的网红主播“秀才”账号被封禁,在社交媒体平台上引发热议。平台相关负责人表示,“秀才”账号违反平台相关规定,已封禁。据知情人士透露,秀才近期被举报存在违法行为,这可能是他被封禁的部分原因。据悉,“秀才”年龄39岁,是安徽省亳州市蒙城县人,抖音网红,粉丝数量超1200万。他曾被称为“中老年...

发布于:7月以前  |  445次阅读  |  详细内容 »

亚马逊股东起诉公司和贝索斯,称其在购买卫星发射服务时忽视了 SpaceX

9月3日消息,亚马逊的一些股东,包括持有该公司股票的一家养老基金,日前对亚马逊、其创始人贝索斯和其董事会提起诉讼,指控他们在为 Project Kuiper 卫星星座项目购买发射服务时“违反了信义义务”。

发布于:7月以前  |  444次阅读  |  详细内容 »

苹果上线AppsbyApple网站,以推广自家应用程序

据消息,为推广自家应用,苹果现推出了一个名为“Apps by Apple”的网站,展示了苹果为旗下产品(如 iPhone、iPad、Apple Watch、Mac 和 Apple TV)开发的各种应用程序。

发布于:7月以前  |  442次阅读  |  详细内容 »

特斯拉美国降价引发投资者不满:“这是短期麻醉剂”

特斯拉本周在美国大幅下调Model S和X售价,引发了该公司一些最坚定支持者的不满。知名特斯拉多头、未来基金(Future Fund)管理合伙人加里·布莱克发帖称,降价是一种“短期麻醉剂”,会让潜在客户等待进一步降价。

发布于:7月以前  |  441次阅读  |  详细内容 »

光刻机巨头阿斯麦:拿到许可,继续对华出口

据外媒9月2日报道,荷兰半导体设备制造商阿斯麦称,尽管荷兰政府颁布的半导体设备出口管制新规9月正式生效,但该公司已获得在2023年底以前向中国运送受限制芯片制造机器的许可。

发布于:7月以前  |  437次阅读  |  详细内容 »

马斯克与库克首次隔空合作:为苹果提供卫星服务

近日,根据美国证券交易委员会的文件显示,苹果卫星服务提供商 Globalstar 近期向马斯克旗下的 SpaceX 支付 6400 万美元(约 4.65 亿元人民币)。用于在 2023-2025 年期间,发射卫星,进一步扩展苹果 iPhone 系列的 SOS 卫星服务。

发布于:7月以前  |  430次阅读  |  详细内容 »

𝕏(推特)调整隐私政策,可拿用户发布的信息训练 AI 模型

据报道,马斯克旗下社交平台𝕏(推特)日前调整了隐私政策,允许 𝕏 使用用户发布的信息来训练其人工智能(AI)模型。新的隐私政策将于 9 月 29 日生效。新政策规定,𝕏可能会使用所收集到的平台信息和公开可用的信息,来帮助训练 𝕏 的机器学习或人工智能模型。

发布于:7月以前  |  428次阅读  |  详细内容 »

荣耀CEO谈华为手机回归:替老同事们高兴,对行业也是好事

9月2日,荣耀CEO赵明在采访中谈及华为手机回归时表示,替老同事们高兴,觉得手机行业,由于华为的回归,让竞争充满了更多的可能性和更多的魅力,对行业来说也是件好事。

发布于:7月以前  |  423次阅读  |  详细内容 »

AI操控无人机能力超越人类冠军

《自然》30日发表的一篇论文报道了一个名为Swift的人工智能(AI)系统,该系统驾驶无人机的能力可在真实世界中一对一冠军赛里战胜人类对手。

发布于:7月以前  |  423次阅读  |  详细内容 »

AI生成的蘑菇科普书存在可致命错误

近日,非营利组织纽约真菌学会(NYMS)发出警告,表示亚马逊为代表的电商平台上,充斥着各种AI生成的蘑菇觅食科普书籍,其中存在诸多错误。

发布于:7月以前  |  420次阅读  |  详细内容 »

社交媒体平台𝕏计划收集用户生物识别数据与工作教育经历

社交媒体平台𝕏(原推特)新隐私政策提到:“在您同意的情况下,我们可能出于安全、安保和身份识别目的收集和使用您的生物识别信息。”

发布于:7月以前  |  411次阅读  |  详细内容 »

国产扫地机器人热销欧洲,国产割草机器人抢占欧洲草坪

2023年德国柏林消费电子展上,各大企业都带来了最新的理念和产品,而高端化、本土化的中国产品正在不断吸引欧洲等国际市场的目光。

发布于:7月以前  |  406次阅读  |  详细内容 »

罗永浩吐槽iPhone15和14不会有区别,除了序列号变了

罗永浩日前在直播中吐槽苹果即将推出的 iPhone 新品,具体内容为:“以我对我‘子公司’的了解,我认为 iPhone 15 跟 iPhone 14 不会有什么区别的,除了序(列)号变了,这个‘不要脸’的东西,这个‘臭厨子’。

发布于:7月以前  |  398次阅读  |  详细内容 »
 相关文章
Android插件化方案 5年以前  |  236868次阅读
vscode超好用的代码书签插件Bookmarks 1年以前  |  6863次阅读
 目录