SQLite 基础

数据库操作

SQLite 官方给出一系列命令行工具（简称 CLP），可以通过命令行来操作 SQLite，下载地址：https://sqlite.org/download.html

下载名为：sqlite-tools-xxx的文件，下面以 windows 为例：
解压后可以使用目录中的 sqlite3.exe 进入数据库操作。

基础命令

.open 打开一个 SQLite 文件，若没有则创建。
.h 查看帮助
.exit 退出命令行工具

创建数据库

数据库可以使用 Navicat 等数据库管理工具，直接创建一个文件，也可以使用命令行工具创建：

PS E:\Tools> sqlite3 test.db

虽然我们指定了数据库的名称，但目前位置SQLite 并未创建该数据库，直到数据库内部创建一些内容时，Sqlite 才会创建该数据库。
这样做的好处是在数据库保存在磁盘之前，可以进行各种永久性设置，数据库一旦创建，一些设置例如页面大小、字符集是不能轻易改变的。

要将数据库文件写入磁盘，只需要创建一个表：

sqlite> create table test(id integer primary key,value text);

现在创建了一个 test.db 数据库文件，数据库包含 test 表，表中有两列：

id 主键列，当定义一个整形的主键列之后，SQLite 会在该列上应用自增函数。
value ，一个简单的文本域

插入数据

sqlite> insert into test(value) values('张三');
sqlite> insert into test(value) values('李四');
sqlite> insert into test(value) values('王五');
sqlite> insert into test(value) values('赵六');

查询数据

sqlite> .mode column
sqlite> select * from test;
id  value
--  -----
1   张三
2   李四
3   王五
4   赵六

退出前创建一个索引和视图

sqlite> create index test_idx on test(value);
sqlite> create view schema as select * FROM sqlite_master;

退出 SQLite 命令行

.exit
windows 快捷键 Ctrl+C
Linux 快捷键 Ctrl + D

数据库信息

获取表与视图： .tables

查看索引： indices [表名称]

查看DDL：

使用 .schema [table name] 可以得到一个表或视图的定义语句，如果没有提供表名，则返回所有数据库对象 (table、index、view、trigger) 的定义语句：

sqlite> .schema test
CREATE TABLE test(id integer primary key,value text);
CREATE INDEX test_idx on test(value);
sqlite> .schema
CREATE TABLE test(id integer primary key,value text);
CREATE INDEX test_idx on test(value);
CREATE VIEW schema as select * FROM sqlite_master

sqlite_master

通过查询系统视图 sqlite_master 表可以获取更详细的信息：

字段名	说明
type	对象类型（table、index、trigger、view）
name	对象名称
tbl_name	对象关联的表
Rootpage	对象跟页面所在的数据库索引
sql	对象的 SQL 定义

查询返回结果如下：

sqlite> .mode column
sqlite> .headers on
sqlite> select * from sqlite_master;
type   name      tbl_name  rootpage  sql
-----  --------  --------  --------  ----------------------------------------------------
table  test      test      2         CREATE TABLE test(id integer primary key,value text)
index  test_idx  test      3         CREATE INDEX test_idx on test(value)
view   schema    schema    0         CREATE VIEW schema as select * FROM sqlite_master
sqlite>

导出数据

.dump [table_name|table_view]

使用 .dump 命令可以将数据库对象导出成 SQL 格式。不带任何参数时，.dump 将导出 DDL 和 DML 命令，或指定需要导出的表名及视图。如果导出的表或视图不存在则会被忽略，在 Shell 模式中 dump 命令默认输出到屏幕，也可以通过 .output [filename] 的方式将数据导出到文件：

-- 输出到 test.sql 文件,文件不存在将被创建，如果存在则会被覆盖
sqlite> .output test.sql
sqlite> .dump
-- 恢复输出到屏幕
sqlite> .output stdout

通过 SQL 重定向的各种设置，可以很大程度控制导出的数据。

导入数据

导入数据支持 SQL 文件和 CSV 文件，可以使用 .read 命令导入 .dump 命令生成的 SQL 文件。使用 .import [file][table] 命令可以导入 CSV 文件。

导入SQL

sqlite> drop table test;
sqlite> drop view schema;
sqlite> .read test.sql

导入 CSV
注意：导入CSV时，分隔符需要使用 ,，否则无法导入。

sqlite> CREATE TABLE test2(id integer primary key,value text);
sqlite> .separator ,
sqlite> .import file2.csv test2
sqlite> select * FROM test2;

格式化

命令行提供了格式化命令的选项，使结果集输出的更加整齐。

.mode 命令可以设置结果数据中的几种输出格式，可选格式有CSV、column、html、insert、line、list、tabs 和 tcl。默认格式是 list 模式显示结果集，并通过管道符分割字段列，每种格式有不同的用途。

如果想以 CSV 格式导出数据可以如下操作：

sqlite> .output file.csv
sqlite> .mode csv
sqlite> select * FROM test;
sqlite> .output stdout

也可以通过设置分割符的方式导出 CSV：

sqlite> .output .file2.csv
sqlite> .separator ,
sqlite> select * FROM test;
sqlite> .output stdout

区别在于 CSV 模式将自动换行字段值并且加上双引号，而列表模式不加。

无人值守维护

个人理解的无人值守其实就是在不进入 sqlite3 命令行工具的情况下，进行一些数据库操作。
需要注意的是，输出文件的编码是根据终端编码来的，比如使用CMD导出的文件编码是gbk，如果使用utf-8 打开就会乱码。建议使用 git bash 等工具操作。

导出数据到文件

PS E:\Tools> sqlite3 test.db .dump > test.sql

这个操作会将所有 DDL 和 DML 语句导出到SQL文件。如果只需某个表的所有记录可以执行如下命令：

sqlite3 test.db "select * FROM test" > test.sql

通过导出文件创建数据库

$ sqlite3 test2.db < test.sql

备份数据库

备份数据库有两种方法，一种就是使用 SQLite 无人值守导出功能，直接导出数据库。

还有一种方法，就是简单粗暴的将数据库的db文件复制一份。当然官方提供了一个清理工具，在备份前执行一下可以清除掉一些被释放掉的对象，减少数据库文件体积。

Administrator@DESKTOP-7ROTTMK MINGW64 /e/Tools
$ sqlite3 test.db vacuum

Administrator@DESKTOP-7ROTTMK MINGW64 /e/Tools
$ cp test.db tets.backup

重建数据库

VACUUM 命令可以重建数据库文件，将其重建并写入到磁盘替换源数据库文件。执行此操作的原因有多种：

除非 SQLite 在 auto_vacuum=FULL 模式下运行，否则从数据库文件中删除大量数据时，会留下很多空闲的数据页。这意味着数据库文件会占额外的磁盘空间，运行 VACUUM 来重建数据库，会回收该空间以减小数据库文件的大小。
频繁的插入、更新和删除操作会导致数据库文件变得碎片化（单个表或索引的数据分散在数据库文件中），运行 VACUUM 可以保证每个表的索引在很大程度上连续存储在数据库文件中。在某些情况下，VACUUM 还可以减少数据库中部分填充的页数，从而进一步减小数据库文件的大小。
在 SQLite 数据库中删除记录时，物理文件中不会被删除，而是将保存的内容标记删除。这样可能会其他人在非法获取到数据库文件时，恢复已删除的内容。运行 VACUUM 可以将已删除的内容在物理层面从数据库中清除，从而防止数据库文件泄漏时，其他人通过特殊手段恢复数据。
一些数据库配置项只允许在创建时设置，而 VACUUM 命令会从头创建新的数据库文件，所以也可以用于修改数据库配置信息。

工作原理

VACUUM 命令的工作原理是将数据库中的内容复制到临时数据中，然后使用临时文件的内容覆盖原始文件。这就意味着对数据库进程 VACUUM 操作时，所需的磁盘空间是原始数据库文件的两倍。

sqlite3_analyzer

官方的工具包中提供了一个工具，可以查看数据库磁盘结构的详细信息。

这些信息包括数据库、表和索引分类的单个对象，以及聚合的统计信息。还提供了如页面大小、表总数、索引、文件大小、和页面平均密度到单个数据库对象的详细说明等信息。

Administrator@DESKTOP-7ROTTMK MINGW64 /e/Tools
$ sqlite3_analyzer test.db
/** Disk-Space Utilization Report For test.db

Page size in bytes................................ 4096
Pages in the whole file (measured)................ 4
Pages in the whole file (calculated).............. 4
Pages that store data............................. 4          100.0%
Pages on the freelist (per header)................ 0            0.0%
Pages on the freelist (calculated)................ 0            0.0%
Pages of auto-vacuum overhead..................... 0            0.0%
Number of tables in the database.................. 3
Number of indices................................. 1
Number of defined indices......................... 1
...

语法

命令

SQL 由命令组成，每条命令以 ; 结束（SQL语句需要，而 SQLite 命令不需要）。例如：

SELECT * FROM users;

命令由一系列记号组成，记号可以是常量、关键字、标识符、表达式或者其他特殊字符。令牌符号以空格分开，例如空格、tab 和新一行。

关键字

关键字是指具 SQL 中具有含义的单词，这些单词包括 select、update、insert、create、drop 和 begin 等。

标识符是指数据库中具体的对象，如表或者索引，关键字是保留单词，不允许用作标识符。SQLite 不区分关键字和标识符的大小写。

常量

SQLite 支持三种常量类型：字符串常量、数字常量、二进制常量。

字符串常量： SQLIite 支持单引号和双引号定义字符串，但是建议只使用单引号。如果字符串本身包含单引号，则需要使用两个单引号： 'kanny ''s chicken'
数值常量： 整数、十进制数和科学计数法表述的数，下面是一些举例：
- -1
- 3.142
- 6.0221415E23
二进制常量： 二进制值使用 x'0000' 的表示方法，其中每一位都是一个16进制数。二进制值必须由两个16进制的整倍数（8bits）组成，下面是一些例子：
- x'01'
- x'0fff'
- x'0F0EFF'
- x'0f0effab'

SQLite 默认的字符常量值是大小写敏感的，比如 Mike 与 mike 是不同的。

注释

支持单行 -- 和多行注释 /* */

表操作

创建数据表

语法

create [temp] table table_name (column_definitions [, constrainst]);

temp 表示这表是临时表，只存在于当前会话中，断开连接会自动销毁。

sqlite> create table contacts (
   ...> id integer primary key,
   ...> name text not null collate nocase,
   ...> phone text not null default 'UNKNOWN',
   ...> unique(name,phone));
sqlite>

语法说明如下：

id 为主键，sqlite 会自动为整型主键使用自增长。
name text 类型，约束不能为 null，并且排序不区分大小写。
phone text 类型
unique() 对 name 和 phone 做唯一约束

SQLite 中有五种本地类型，integer、real、test、blob 和 null，类型部分会在后面的 "存储类" 中介绍。

自增 ID

SQLite 中有两种自增 ID，它们在实际使用时有一些区别，建议使用 AUTOINCREMENT 关键字的方式创建自增 ID

使用 INTEGER PRIMARY KEY

CREATE TABLE table_name (
    id INTEGER PRIMARY KEY,
    name TEXT
);

将 ID 列设置为 INTEGER PRIMARY KEY 时，他会成为一个自增的 ID 列，它的自增原理是取 ID 这一列最大的值，然后 +1 用作插入数据的唯一 ID，如果没有则从 1 开始。

从这个自增规则中可以发现一个问题，例如数据表中当前的自增 ID 是2，接着插入一条数据，这条数据的 ID 将会是 3。接着我们删掉了这条数据，再插入一条新数据，我们会发现重新插入的数据 ID 依然是 3 。这样就会造成 ID 的不唯一性，如果用于数据关联就会出现异常。

使用 AUTOINCREMENT

CREATE TABLE table_name (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT
);

在这种情况下，自增 ID 会记录在 sqlite_sequence 表中，即使删除行后，再次插入的数据 ID 也会按照我们理想中的状态去正常递增。

sqlite> select * from sqlite_sequence;
name      seq
--------  ---
my_table  5

查看表信息

查看所有表

使用 .tables 命令可以显示当前数据库中所有表

sqlite> .tables
users

查看表结构

使用 .schema 命令可以查看表的完整信息。

sqlite> .schema users
CREATE TABLE users(
id integer primary key,
name text not null,
age integer default 0,
sex integer not null default 0,
unique(name));
sqlite>

修改表名称

语法：

alert table table_name rename to new_table_name

花括号表示在列表中必须选一个做为关键字

alter table test2 rename to test2021;

删除表

DROP TABLE table_name;

截断表

SQLite 中并没有 TRUNCATE TABLE 命令，但是可以使用 DELETE 语句从数据表中删除全部数据。当 DELETE 中省略 WHERE 条件或者 RETURNING 子句并且要删除的表中没有触发器时，SQLite 会使用类似于截断的方式来删除所有数据，无需获取目标表中的数据用来判断是否符合删除条件，从而提升删除效率。

DELETE 命令的基本语法如下：

DELETE FROM table_name;

但这种方式不会将自增数归零，如果要将自增数归零，则需要在删除数据后再删掉自增:

DELETE FROM sqlite_sequence WHERE name = 'table_name';

添加字段

语法如下

alert table table_name add column column_def

sqlite> alter table contacts
   ...> add column email text not null default '' collate nocase;

删除或修改字段

遗憾的是 SQLite 的 ALTER TABLE 语句仅支持添加字段表、重命名，但我们仍然可以通过其他方法来实现删除字段或修改字段，步骤如下：

将现有表名改为其他表名

ALTER TABLE "test" RENAME TO "temp_test";

创建新表

CREATE TABLE "test" ("Id"  INTEGER PRIMARY KEY AUTOINCREMENT, "Name"  Text);

将临时表数据导入新表

INSERT INTO "test" ("Id", "Name") SELECT "Id", "Title" FROM "temp_test";

修改表自增长值

由于在 Sqlite 中使用自增长字段,引擎会自动产生一个 sqlite_sequence 表,用于记录每个表的自增长字段的已使用的最大值，所以要一起更新下。如果有没有设置自增长，则跳过此步骤。

UPDATE "sqlite_sequence" SET seq = 3 WHERE name = 'test';

删除掉临时表

DROP TABLE temp_test;

数据操作

数据操作部分与 MySQL 差别不大

插入数据

insert into table_name (field1,field2...) values(value1,value2,value3);

如果插入所有的值，则可以忽略字段列表。

插入多条

可以插入多条，使用逗号分隔。

insert into table_name (field1,field2...) values(value1,value2,value3),(value1,value2,value3);

也可以将查询结果使用 insert 语句插入，只要字段对齐即可

insert into table_name (field1,field2) select field1,field2 from table_name;

修改数据

update table_name set update_list where predicate;

删除数据

delete from table_name where predicate;

:::alert-warning
需要注意的是，如果有自增 id，那么删除数据不会重置自增值，需要手动重置，参考 "截断表" 章节。
:::

查询数据

设计数据库与数据表的最终目的是使用数据，数据操作语句成为 DML，DML 的核心命令是 SELECT 命令，它也是查询数据库的唯一命令。

Select 命令

从语法上说，SELECT 命令用一系列子句将很多关系操作组合在一起，每个子句代表一种特定的关系操作。

SELECT 查询语法如下：

SELECT [distinct] heading
    FROM tables
    WHERE predicate
    GROUP BY columns
    HAVING predicate
    ORDER BY columns
    LIMIT COUNT,OFFSET

除 SELECT 外所有的子句都是可选的。

算术运算符

运算符	作用
`+`	加
`-`	减
`*`	乘
`/`	除
`%`	取模

比较运算符

运算符	作用
`==`、`=`	相等
`!=`、`<>`	不相等
`<`、`>`	大于、小于
`<=`、`>=`	大于等于、小于等于
`!<`、`!>`	不大于、不小于

逻辑运算符

运算符	作用
`AND`	并且，拼接多个 WHERE 条件
`BETWEEN`	范围查找
`EXISTS`	子查询，返回布尔值
`IN`	在一个给定的列表中查询
`NOT IN`	查询不在该列表中的数据
`LIKE`	模糊查找,<br/>`%` 表示零个、一个、多个数字或字符 <br/> `_` 表示一个数字或字符
`BLOB`	大小写敏感的模糊查询 `*` 表示零个、一个、多个数字或字符 <br/> `?` 表示一个数字或字符
`NOT`	取反
`OR`	或
`IS NULL`	比较值是否是 NULL
`IS`	等于
`IS NOT`	不等于
`		`	拼接字符串
`UNIQUE`	搜索指定表中的每一行，确保唯一

位运算符

运算符	作用
`&`	二进制 AND 运算符
`	`	二进制或运算符
`~`	补码运算符，0变1，1变0
`<<`	左移运算符
`>>`	右移运算符

LIKE 与 GLOB

Like 运算符用于模糊匹配符合规则的文本值，如果值与表达式匹配则返回 1 否则返回 0，Glob 运算符与 Like 效果类似，区别在于 Glob 是大小写敏感的，且通配符略有不同。

Like	Glob	说明
`%`	`*`	代表零个、一个或多个数字或字符
`_`	`?`	代表一个单一的数字或字符

使用示例

Like 语句	Glob 语句	描述
`WHERE SALARY LIKE '200%'`	`WHERE SALARY GLOB '200*'`	查找以 200 开头的任意值
`WHERE SALARY LIKE '%200%'`	`WHERE SALARY GLOB '200'`	查找任意位置包含 200 的任意值
`WHERE SALARY LIKE '_00%'`	`WHERE SALARY GLOB '?00*'`	查找第二位和第三位为 00 的任意值
`WHERE SALARY LIKE '2__%'`	`WHERE SALARY GLOB '2??'`	查找以 2 开头，且长度至少为 3 个字符的任意值
`WHERE SALARY LIKE '%2'`	`WHERE SALARY GLOB '*2'`	查找以 2 结尾的任意值
`WHERE SALARY LIKE '_2%3'`	`WHERE SALARY GLOB '?2*3'`	查找第二位为 2，且以 3 结尾的任意值
`WHERE SALARY LIKE '2___3'`	`WHERE SALARY GLOB '2???3'`	查找长度为 5 位数，且以 2 开头以 3 结尾的任意值

大小写敏感示例

sqlite> select 'Abcd' LIKE 'a%';
'Abcd' LIKE 'a%'
----------------
1
sqlite> select 'Abcd' Glob 'a?';
'Abcd' Glob 'a?'
----------------
0
sqlite> select 'Abcd' Glob 'A?';
'Abcd' Glob 'A?'
----------------
0

排序和限制

使用 ORDER BY 语句，可以对结果进行排序，ASC 是默认的升序，DESC 降序，多个字段逗号分隔。

SELECT * FROM bank ORDER BY number DESC, id asc

使用 limit 和 offset 可以限制结果集的范围：

limit 返回条数
offset 指定偏移的记录数，越过多少条

例如下面的命令就是返回 bank 表中 id 倒数第三大的记录：

SELECT * FROM bank ORDER BY id DESC LIMIT 1 OFFSET 2

也可以使用下面的方式省略 offset，与上面语句等同：

SELECT * FROM bank ORDER BY id DESC LIMIT 2,1

::: alert-w

limit 和 offset 不是 ANSI 中的标准 SQL 关键字。不同数据库中语法会有所不同。
offset 总是依赖于 limit，可以只用 limit 不带 offset，但反过来却不行。
:::

聚合查询

聚合函数是一类特殊的函数，它从一组记录中计算聚合值。
标准的聚合函数包括 sum()、avg()、count()、min()和 max()

比如获取工商银行的数量：

SELECT count(*) FROM bank WHERE bankTypeName = '工商银行'

分组

聚合的主要部分就是分组，不只是能计算整个结果集的聚合值，也可以将结果集分成多个组，然后计算每个组的聚合值。

分组需要使用到 GROUP BY 子句，下面就获取每个银行的网点数量为例：

SELECT count(*),bankTypeName FROM bank GROUP BY bankTypeName

HAVING

如果我们想在上面 SQL 的基础上只显示网点数量大于 100 家的银行，可以使用 HAVING 子句，用法与 WHERE 相同，唯一的区别是 HAVING 是针对聚合值进行过滤：

SELECT count(*),bankTypeName FROM bank 
GROUP BY bankTypeName HAVING count(*) > 100

去除重复值

distinct 处理 SELECT 的结果并过滤掉其中重复的行。

例如想要获取所有不同的银行：

SELECT DISTINCT bankTypeName FROM "bank"

多表连接

多表连接是关系型数据块工作的关键，join 是 select 命令的第一个操作，将 join 的结果作为输入，供后续 WHERE 条件用于过滤。

交叉连接

如果两个表没有通过任何方式关联，SELECT 会产生一种更基础的表连接，也成为交叉连接、笛卡尔积或者交叉乘积。笛卡尔积是最基本的运算关系，他说强制的几乎无意义的连接方式，也就是将 table1 中所有行与 table2 中所有行关联起来。

隐式交叉连接
语法：SELECT <columns...> FROM table1,table2 [WHERE...]

以查出电影上映年份和电影简介为例：

sqlite> SELECT film.release_year,film.title,film_text.description FROM film,film_text WHERE film.film_id = film_text.film_id limit 10;
+--------------+------------------+-----------------------------------------------------------------------------------------------------------------------+
| release_year | title            | description                                                                                                           |
+--------------+------------------+-----------------------------------------------------------------------------------------------------------------------+
| 2006         | ACADEMY DINOSAUR | A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies                      |
| 2006         | ACE GOLDFINGER   | A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China                  |
| 2006         | ADAPTATION HOLES | A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory                      |
| 2006         | AFFAIR PREJUDICE | A Fanciful Documentary of a Frisbee And a Lumberjack who must Chase a Monkey in A Shark Tank                          |
| 2006         | AFRICAN EGG      | A Fast-Paced Documentary of a Pastry Chef And a Dentist who must Pursue a Forensic Psychologist in The Gulf of Mexico |
| 2006         | AGENT TRUMAN     | A Intrepid Panorama of a Robot And a Boy who must Escape a Sumo Wrestler in Ancient China                             |
| 2006         | AIRPLANE SIERRA  | A Touching Saga of a Hunter And a Butler who must Discover a Butler in A Jet Boat                                     |
| 2006         | AIRPORT POLLOCK  | A Epic Tale of a Moose And a Girl who must Confront a Monkey in Ancient India                                         |
| 2006         | ALABAMA DEVIL    | A Thoughtful Panorama of a Database Administrator And a Mad Scientist who must Outgun a Mad Scientist in A Jet Boat   |
| 2006         | ALADDIN CALENDAR | A Action-Packed Tale of a Man And a Lumberjack who must Reach a Feminist in Ancient China                             |
+--------------+------------------+-----------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.10 sec)

显式交叉连接

sqlite> SELECT film.release_year,film.title,film_text.description FROM film 
    -> CROSS JOIN film_text ON film.film_id = film_text.film_id 
    -> limit 10;
+--------------+------------------+-----------------------------------------------------------------------------------------------------------------------+
| release_year | title            | description                                                                                                           |
+--------------+------------------+-----------------------------------------------------------------------------------------------------------------------+
| 2006         | ACADEMY DINOSAUR | A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies                      |
| 2006         | ACE GOLDFINGER   | A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China                  |
| 2006         | ADAPTATION HOLES | A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory                      |
| 2006         | AFFAIR PREJUDICE | A Fanciful Documentary of a Frisbee And a Lumberjack who must Chase a Monkey in A Shark Tank                          |
| 2006         | AFRICAN EGG      | A Fast-Paced Documentary of a Pastry Chef And a Dentist who must Pursue a Forensic Psychologist in The Gulf of Mexico |
| 2006         | AGENT TRUMAN     | A Intrepid Panorama of a Robot And a Boy who must Escape a Sumo Wrestler in Ancient China                             |
| 2006         | AIRPLANE SIERRA  | A Touching Saga of a Hunter And a Butler who must Discover a Butler in A Jet Boat                                     |
| 2006         | AIRPORT POLLOCK  | A Epic Tale of a Moose And a Girl who must Confront a Monkey in Ancient India                                         |
| 2006         | ALABAMA DEVIL    | A Thoughtful Panorama of a Database Administrator And a Mad Scientist who must Outgun a Mad Scientist in A Jet Boat   |
| 2006         | ALADDIN CALENDAR | A Action-Packed Tale of a Man And a Lumberjack who must Reach a Feminist in Ancient China                             |
+--------------+------------------+-----------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.09 sec)

内连接

内连接就是两张表都匹配时候，才会返回

SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...

外连接

外连接是内连接的扩展，虽然SQL定义了三种（左连接、右连接、全连接）但SQLite 只支持左连接。

SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...

子查询

子查询可以用来嵌套SQL语句，在表达式不适用的地方使用，特别是结合 in 查询的用途：

SELECT * FROM actor WHERE actor_id IN(SELECT actor_id FROM film_actor WHERE film_id = 1);

复合查询

复合查询与子查询相反，它是使用三种特殊关系操作符（联合、交叉连接和差集）处理多个查询的结果，在 SQLite 中对应使用的关键字 union、intersect 和 except。

复合查询有如下要求：

关联结果集字段数量必须相同。
只能有一个 order by 结果集，并且在符合查询最末尾，对联合结果进行排序。

union 合并结果集

使用 union 可以合并多个结果集，并且在去重后返回，使用方式如下：

例 users 表数据如下：

sqlite> select * from users;
id  name      age
--  --------  ---
1   zhangsan  18
2   lisi      20
3   wangwu    28

需要查询出年龄最大和最小的用户，并用使用 union 将两个结果集合并，使用方式如下：

sqlite> select * from (select name,age from users order by age limit 1) union select * from (select name,age from users order by age desc limit 1);
name      age
--------  ---
wangwu    28
zhangsan  18

union all 合并结果集

union all 运算符用法与 union 类似，但不会对结果集进行去重，效果如下：

sqlite> select * from users union select * from users;
id  name      age
--  --------  ---
1   zhangsan  18
2   lisi      20
3   wangwu    28
sqlite> select * from users union all select * from users;
id  name      age
--  --------  ---
1   zhangsan  18
2   lisi      20
3   wangwu    28
1   zhangsan  18
2   lisi      20
3   wangwu    28

intersect 合并取交集

intersect 运算符的作用是取两个结果集的交集，效果如下：

sqlite> select * from users where id != 3;
id  name      age
--  --------  ---
1   zhangsan  18
2   lisi      20
sqlite> select * from users where id != 1;
id  name    age
--  ------  ---
2   lisi    20
3   wangwu  28
sqlite> select * from users where id != 3 intersect select * from users where id != 1;
id  name  age
--  ----  ---
2   lisi  20
sqlite>

except 合并取差集

except 运算符的作用是找出在第一个结果集中存在，在第二个结果集中不存在的数据，效果如下：

sqlite> select * from users where id != 3 except select * from users where id != 1;
id  name      age
--  --------  ---
1   zhangsan  18

case 条件

case 表达式可以根据条件不配不同的结果并返回，它的用法与 MySQL 类似有两种用法：

第一种用法，使用 case 表达式处理解析不同的字段值，并返回对应的字符串效果如下：

sqlite> select * from users;
id  name      age  sex
--  --------  ---  ---
1   zhangsan  18   1
2   lisi      20   2
3   wangwu    28   0
sqlite> select name || case sex when 1 then ' is Male' when 2 then ' is Female' else ' is unknown' end as 'User Sex' from users;
User Sex
-----------------
zhangsan is Male
lisi is Female
wangwu is unknown

第二种用法，将表达式写在 when 中：

sqlite> select name || case when sex = 1 then ' is Male' when sex = 2 then ' is Female' else ' is unknown' end as 'User Sex' from users;
User Sex
-----------------
zhangsan is Male
lisi is Female
wangwu is unknown

处理 Null

在大多数关系型数据库中，null 都表示未知的概念，null 是缺失信息的占位符，本身并不是值。null 表示该位置没有值。下面是 sqlite 中 null 的三条规则：

为了表在逻辑表达式中使用 null，效果如下

表达式	结果
`Null AND True`	Null
`Null OR True`	True
`Null AND False`	False
`Null OR False`	Null
`Null AND NULL`	NULL
`Null OR NULL`	NULL

通过 is null 或者 is not null 操作符来判断值是否是 null。
记住 Null 不等于任何值，包括 Null，不能将 Null 用于比较运算，这样写通常不会返回任何数据。

视图

视图也称派生表，因为它们的内容派生自其他表的查询结果。虽然视图看起来感觉就像基本表一样，但它们不是基本表。因为基本表的内容是持久化的，而视图表的内容是根据基础表动态生成的。

创建视图

视图表的创建语法如下：

create view name as select-stmt

视图的名称通过 name 指定，其定义由 select-stmt 定义，最终是视图看起来就像名为 name 的基本表一样。

下面是基于 users 表创建 users_view 视图的示例,通过视图将字段名称、字段值进行转换，从而简化查询过程。

sqlite> select * from users;
id  name       age  sex
--  ---------  ---  ---
1   zhangsan   18   1
3   zhangsan3  18   1
4   zhangsan4  18   1
sqlite>
sqlite> create view users_view as
   ...>  select name as '姓名',age as '年龄',
   ...>  case sex when 1 then '男' when 2 then '女' else '未知' end as '性别'
   ...> from users;
sqlite>
sqlite> select * from users_view;
姓名         年龄  性别
---------  --  --
zhangsan   18  男
zhangsan3  18  男
zhangsan4  18  男

删除视图

视图删除通过 drop view 命令实现。

sqlite> drop view users_view;

索引

索引可以用来加速查询，与其他数据库类似，SQLite 使用 B-Tree（B树）做索引。需要注意的是索引也会增加数据库的大小，从字面意义上理解，索引就是将字段值多复制了一份，如果所有字段都创建索引，表的大小可能会翻倍。另外也需要考虑索引的维护，进行 Insert、Update、Delete 操作时除了修改表，对应的索引也必须修改，虽然索引可以加快查询速度，但它们会降低 Insert、Update、Delete 的速度。

创建索引

创建索引命令如下：

create [unique] index index_name on table_name (columns)

index_name: 索引名称
table_name: 索引所在的表名称
columns: 索引字段，若多个字段使用逗号分隔
unique: 唯一索引标识

创建一个名为 idx_name_age 由 users 表 name 和 age 字段组成的唯一索引。

create unique index idx_name_age on users(name,age);

删除索引

使用 drop index index_name 命令:

sqlite> drop index idx_name_age;

使用索引

SQLite 的索引使用规则与 MySQL 一致，遵守最左原则。

参考资料
SQLite官方文档：https://sqlite.org/docs.html
SQLite权威指南（第二版) https://book.douban.com/subject/7061934/
SQLite 教程（菜鸟教程）https://www.runoob.com/sqlite/sqlite-tutorial.html

Catalog