5分钟快速了解SQLite

杨建荣的学习笔记 2024-01-18

366

这是学习笔记的第 2480篇文章

我们总是听到Oracle、MySQL、PostgreSQL等数据库，似乎大家都没有回过神来，其实SQLite是全球用户最多的数据库产品，只是没有太在意而已，比如我们使用的微信里面也有SQLite的身影，Python里面也默认安装了sqlite，Django默认调用的就是sqlite驱动，就连数据库领域内也有很多默认安装的存在形式，比如ProxySQL的档案库也是默认基于SQLite存储。

这样一个看起来无处不在的数据库，没有引起大家的注意，我一直在设想这可能是一个玩具数据库，只能存储一些简单的数据等等。但是至少对于我来说，其实远远忽略了它的潜力，它在嵌入式领域还是很普遍的，对于资源的使用率极低，能从各种数据库中杀出重围真是不易。

任何一个数据库产品的起源似乎都带有一些偶然性，也带有一丝宿命。

SQLite 诞生的契机就是典型的程序员开发的故事剧本。作者 Richard 最开始在一艘军舰上做外包。他们的程序跑在军舰安装的电脑上，电脑上装的是 informix。Richard 的工作就是把 informix 的数据拿出来进行计算然后展示到电脑屏幕上。难就难在 informix 不够稳定，经常崩溃连不上，部队里的长官可不懂什么底层技术原理，至少报错是从Richard的功能里面抛出来的，可见Richard受了不少夹板气。于是他决定自己从头写一个无需外部连接的数据库来解决这个问题，难得是他这么干了，而且坚持下来了。。。

直到Richard 把 SQLite 发布到网上，收到了好评。。。接下来的故事和 Linux 诞生有点类似了，好像都是傻人有傻福，你的目的性越强，功利性越强，越没戏。

目前SQLite最新版本是SQLite version 3.44.2，发布时间是2023-11-24, 我简单测试了一些功能，感觉SQL语法的支持蛮全的。我们来快速演示一下。

安装数据库

默认其实SQLite是Linux操作系统自带的，当然版本要低一些（SQLite version 3.7.17 2013-05-20）

如果需要安装最新版本，可以有多种方式，我使用的是源码安装，下载最新的安装包，编译即可。

wget https://www.sqlite.org/2023/sqlite-autoconf-3440200.tar.gz --no-check-certificate
./configure --prefix=/usr/local/sqlite3
make
make install

创建数据库

创建数据库不需要create database这样的语句，你指定一个文件即可，默认就是main数据库

# usr/local/sqlite3/bin/sqlite3 testdb.db 
SQLite version 3.44.2 2023-11-24 11:41:44
Enter ".help" for usage hints.
sqlite>

这样就默认你创建了一个数据库，当然还有另外两类操作需要说明下，比如给数据库起个别名或者绑定多个数据库，就可以使用attach命令。

sqlite> attach database 'testdb.db' as 'testdb';
sqlite> .databases
main: /data/sqlite3/sqlite_data/testdb.db r/w
testdb: /data/sqlite3/sqlite_data/testdb.db r/w

创建数据表

创建数据库给我带来了一些小惊喜，因为MySQL的语法竟然完全可以兼容，比如我创建了一张表，使用了主键，数据类型，都完全兼容，自增列的命名不大一样，是autoincrement，没有下引号。

sqlite>  CREATE TABLE COMPANY(
...>    ID INTEGER PRIMARY KEY   AUTOINCREMENT,
...>    NAME           TEXT      NOT NULL,
...>    AGE            INT       NOT NULL,
...>    ADDRESS        CHAR(50),
...>    SALARY         REAL
...> );


sqlite> INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
   ...> VALUES ( 'Paul', 32, 'California', 20000.00 );
sqlite> 
sqlite> INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
   ...> VALUES ('Allen', 25, 'Texas', 15000.00 );
sqlite> select * from company;
ID  NAME   AGE  ADDRESS     SALARY 
--  -----  ---  ----------  -------
1   Paul   32   California  20000.0
2   Allen  25   Texas       15000.0

快速查看帮助

SQLite的帮助是比较简单的，通常初学者在第一次使用SQLite的时候都会有些抓狂，那就是无法退出数据库，无奈之下只能CTRL+D取消推出，其实exit,quit都是可以使用的，可以使用.help的命令来查看，其实可以看到SQLite的风格就是以小数点来作为引导的。

sqlite> .help  #查看数据库基本命令的方法
sqlite> exit;
Parse error: near "exit": syntax error
  exit;
  ^--- error here
sqlite> quit;
Parse error: near "quit": syntax error
  quit;
  ^--- error here
sqlite> .exit   #正确退出数据库的姿势

顺着这个思路，其实可以看到新版本的一些特性内容，比如当前使用的内存才180KB左右。

sqlite> .stats
Memory Used:                         186088 (max 186176) bytes
Number of Outstanding Allocations:   262 (max 264)
Number of Pcache Overflow Bytes:     5136 (max 5136) bytes
Largest Allocation:                  87360 bytes

格式化命令行输出

如果使用命令行查看SQLite数据，默认的数据格式看起来有些不够友好，可以做一下格式化。

sqlite> select * from testdata3;  ##显示效果比较单一
1|aa|2024-01-17 00:00:00


sqlite> .header on 
sqlite> .mode column
sqlite> .timer on 
sqlite> select * from testdata3 limit 2;   ##效果明显好一些
id  name  create_time        
--  ----  -------------------
1   aa    2024-01-17 00:00:00
Run Time: real 0.001 user 0.000091 sys 0.000046

SQL支持

SQLite支持标准化SQL语句，所以order by ,group by 等基础的SQL语法大部分都是兼容支持的，比如触发器，视图等高级特性也是支持的，目前我测试了下只有truncate table不支持。

查看执行计划的部分，SQLite的显示效果蛮清晰的。

还有glob语法，感觉和like是很相似的。

sqlite> select * from company where address glob '*ali*';
ID  NAME  AGE  ADDRESS     SALARY 
--  ----  ---  ----------  -------
1   Paul  32   California  20000.0