SQLite 相关

date

Aug 16, 2021

slug

SQLite

status

Published

开发者工具

Mac 下自带 sqlite3 工具，可执行执行 sqlite3 .help 查看相关信息。

$sqlite3 -help //帮助信息

$sqlite3 test.db //打开，若不存在，则创建并打开

sqlite> // 为进入 sqlite 环境
sqlite> .help //等同于 $sqlite3 -help

sqlite> select * from sqlite_master; // 查询所有表

常用配置

sqlite> .show // 查看配置信息

.header on //展示表头
.mode column //展示模式
.width 3 20 10 //列宽，单位字符数

$sqlite3 Kv.db .dump > Kv.sql // 导出数据库到 sql 文件
$sqlite3 Kv.db < Kv.sql // 导入 sql 数据

⚠️

每一条 SQL 语句结束必须带 ;

外键约束

sqlite 默认不开启外键约束，需手动执行命令。

sqlite> PRAGMA foreign_keys = ON;

外键约束需要在创建的时候对某个列指定外键约束的具体情况，也就是 on update on delete 后的枚举值。

共有以下几种

NO ACTION 不做任何操作

RESTRICT 当子表中有对主表的引用时，任何对主表中的被引用行的删除、修改都被限制

SET NULL 当 update 或 delete 时，设置当前列为 null

SET DEFAULT 同 set null，不过不是设置为 null 而是设置为当前列的默认值

CASCADE，跟随主表同步修改，主表 update，这里的引用也就 update，主表 delete，当前表中的引用行就会被删除

CREATE TABLE contrace (
	_id integer PRIMARY KEY autoincrement NOT NULL,
	address text NOT NULL,
	phone text NOT NULL,
	user_name text NOT NULL,
	FOREIGN KEY (user_name) REFERENCES user(name) ON UPDATE CASCADE ON DELETE CASCADE
);

后端开发中，一般为避免性能瓶颈，不使用外键。

一对一的查询使用 left join 就能查到相关信息，如果是一对多的话，单独使用 SQL 语句是不行的，需要配合数据库的相关框架才能将一对多表现为一个 Object 包含自己信息的同时含有子元素的列表。比如后端的 Mybatis，Android 端的一些 ORM 框架也能做，纯 SQL 是不行的。

-- 表结构
CREATE TABLE class (
	_id integer PRIMARY KEY autoincrement NOT NULL,
	name text NOT NULL
);

CREATE TABLE student (
	_id integer PRIMARY KEY autoincrement NOT NULL,
	name text NOT NULL,
	age integer NOT NULL,
	class_id integer NOT NULL
);

-- 查询关联信息
SELECT
	student._id,
	student. "name",
	student.age,
	"class"."name"
FROM
	student
	LEFT OUTER JOIN "class" ON student.class_id = "class"._id
WHERE
	"class"._id = 1;

Select from 后面不只是可以跟表名，还可以跟子语句的查询结果。需要注意的是子语句的查询结果必须要有个临时表名，不然没法从临时表中查询数据。

SELECT a.eId,
         LastName
FROM 
    (SELECT EmployeeId AS eId,
         LastName,
         FirstName,
         Address
    FROM employees) a
WHERE a.eId = 1;

插入二进制数据「图片等」

可以用 SQLite 自带的 blob 数据类型，进行插入。Android 端，ContentValues 可插入，查询时拿到 Cursor 后，也可用过其获取到二进制文件的字节数组。

使用 base64 编码后再插入，不推荐，因为 base64 会将原数据放大 1/3。

Android 开发相关

在不确定 SQL 语句是否可以的情况下，可以把 SQLite 文件导出，然后使用数据库软件或是命令行进入，执行相关语句。也可以使用 Android Studio 自带的 Database Inspector 进行查询。

Database Inspector 可能会出现报红的异常，一般可能是表名写错了，或是关联表查询的时候 join 的表名有问题。

使用 Count 函数

// select count(*) from resource where status = Success
val successCount = DatabaseUtils.queryNumEntries(db, "resource", "status = ?", arrayOf("Success"))