MENU

SQLite 基础

October 6, 2023 • Read: 958 • 编码

数据库操作

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 默认的字符常量值是大小写敏感的,比如 Mikemike 是不同的。

注释

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

表操作

创建数据表

语法

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 是大小写敏感的,且通配符略有不同。

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

使用示例

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 中对应使用的关键字 unionintersectexcept

复合查询有如下要求:

  • 关联结果集字段数量必须相同。
  • 只能有一个 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 TrueNull
Null OR TrueTrue
Null AND FalseFalse
Null OR FalseNull
Null AND NULLNULL
Null OR NULLNULL
  • 通过 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