数据库操作
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