【数据库】SQLite 数据库

参考文档：sqlite 官方文档：https://www.sqlite.org/docs.html

一、简介

SQLite 是一个进程内的库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。它是一个零配置的数据库，这意味着与其他数据库不一样，不需要在系统中配置。

就像其他数据库，SQLite 引擎不是一个独立的进程，可以按应用程序需求进行静态或动态连接。SQLite 直接访问其存储文件。

1. sqlite 优势

• 不需要一个单独的服务器进程或操作的系统（无服务器的）。

  SQLite 不需要配置，这意味着不需要安装或管理。

  一个完整的 SQLite 数据库是存储在一个单一的跨平台的磁盘文件。

  SQLite 是非常小的，是轻量级的，完全配置时小于 400KiB，省略可选功能配置时小于250KiB。

  SQLite 是自给自足的，这意味着不需要任何外部的依赖。

  SQLite 事务是完全兼容 ACID 的，允许从多个进程或线程安全访问。

  SQLite 支持 SQL92（SQL2）标准的大多数查询语言的功能。

  SQLite 使用 ANSI-C 编写的，并提供了简单和易于使用的 API。

  SQLite 可在 UNIX（Linux, Mac OS-X, Android, iOS）和 Windows（Win32, WinCE,
  WinRT）中运行。

2. 命令分类

与关系数据库进行交互的标准 SQLite 命令类似于 SQL。命令包括 CREATE、SELECT、INSERT、UPDATE、DELETE 和 DROP。这些命令基于它们的操作性质可分为以下几种：

DDL - 数据定义语言	DML - 数据操作语言	DQL - 数据查询语言
CREATE 创建一个新的表，一个表的视图，或者数据库中的其他对象。	INSERT 创建一条记录。	SELECT 从一个或多个表中检索某些记录。
ALTER 修改数据库中的某个已有的数据库对象，比如一个表。	UPDATE 修改记录。
DROP 删除整个表，或者表的视图，或者数据库中的其他对象。	DELETE 删除记录。

二、SQLite 命令

 # sqlite3 安装
 # 大部分 Linux 安装有 sqlite3 
 sudo apt install sqlite3

1. 简单的 SQLite 命令

提示符与点命令之间没有空格，否则将无法正常工作。

# 1. 启动 sqlite3
sqlite3

# 2. 查看命令清单
.help

# 3. 查看 SQLite 命令提示符的默认设置
.show

# 4. 更改设置
# 例如：
.header on
.mode column
.timer on

# 5. 
# .schema + 表名
.schema flightplan
# sqlite_master 是SQLite内部使用的一个特殊表，它存储了数据库元数据信息，包括所有的表、索引、触发器、视图和存储过程的详细信息。当你执行 .schema sqlite_master 命令时，SQLite将返回创建 sqlite_master 表的SQL语句。这通常看起来像一个 CREATE TABLE 语句，它会告诉你这个表的列名、数据类型以及任何约束条件（如主键、唯一性、默认值等）。

2. SQLite 语法

1. 大小写敏感性

SQLite 是不区分大小写的，但也有一些命令是大小写敏感的，比如 GLOB 和 glob 在 SQLite 的语句中有不同的含义。

2. 注释

SQLite 注释是附加的注释，可以在 SQLite 代码中添加注释以增加其可读性，可以出现在任何空白处，包括在表达式内和其他 SQL 语句的中间，但不能嵌套。

SQL 注释以两个连续的 “-” 字符（ASCII 0x2d）开始，并扩展至下一个换行符（ASCII 0x0a）或直到输入结束，以先到者为准。

也可以使用 C 风格的注释，以 “/" 开始，并扩展至下一个 "/” 字符对或直到输入结束，以先到者为准。SQLite的注释可以跨越多行。

3. SQLite 语句 — 待更新

所有的 SQLite 语句可以以任何关键字开始，如 SELECT、INSERT、UPDATE、DELETE、ALTER、DROP 等，所有的语句以分号 ; 结束。

PS: 这里可以用作简单快速的查询，下一章会有具体示例。

1. ANALYZE 语句

在 SQLite 中，ANALYZE 命令用于收集关于数据库内容的统计信息。这些信息被存储在特殊的表 sqlite_stat1, sqlite_stat2, 和 sqlite_stat3 中（具体取决于 SQLite 的版本和你的数据库模式），并被查询规划器用来做出更优的决策，决定如何执行 SQL 查询。

# 1,
# 当不指定数据库或表而运行 ANALYZE 命令时，它会分析整个数据库： 
ANALYZE;

# 2.
# 如果想要针对特定的数据库进行分析
# 由于 SQLite 通常不按照传统意义上的多个数据库工作（每个 SQLite 数据库通常都包含在一个单独的文件中）
# 这种形式的命令通常没有必要，除非正在处理附加的数据库。
ANALYZE database_name;

# 3.
# 要针对特定的表进行分析
ANALYZE database_name.table_name;
# 如果只涉及到一个数据库，你可以省略 database_name 部分
ANALYZE table_name;

2. AND/OR 子句

SELECT column1, column2....columnN  --- 
FROM   table_name
WHERE  CONDITION-1 {
   AND|OR} CONDITION-2;

3. ALTER TABLE 语句

# 1.
ALTER TABLE table_name ADD COLUMN column_def...;
# 2.
ALTER TABLE table_name RENAME TO new_table_name;

4. ATTACH DATABASE 语句

4. SQLite 数据类型

SQLite 数据类型是一个用来指定任何对象的数据类型的属性。SQLite 中的每一列，每个变量和表达式都有相关的数据类型。

在创建表的同时使用这些数据类型。SQLite 使用一个更普遍的动态类型系统。在 SQLite 中，值的数据类型与值本身是相关的，而不是与它的容器相关。

1. 存储类

每个存储在 SQLite 数据库中的值都具有以下存储类之一：

存储类	描述
NULL	值是一个 NULL 值。
INTEGER	值是一个带符号的整数，根据值的大小存储在 1、2、3、4、6 或 8 字节中。
REAL	值是一个浮点值，存储为 8 字节的 IEEE 浮点数字。
TEXT	值是一个文本字符串，使用数据库编码（UTF-8、UTF-16BE 或 UTF-16LE）存储。
BLOB	值是一个 blob 数据，完全根据它的输入存储。

SQLite 的存储类稍微比数据类型更普遍。INTEGER 存储类，例如，包含 6 种不同的不同长度的整数数据类型。

2. 亲和(Affinity)类型及类型名称

SQLite支持列的亲和类型概念。任何列仍然可以存储任何类型的数据，当数据插入时，该字段的数据将会优先采用亲缘类型作为该值的存储方式。SQLite目前的版本支持以下五种亲缘类型:

亲和类型	描述
TEXT	数值型数据在被插入之前，需要先被转换为文本格式，之后再插入到目标字段中。
NUMERIC	当文本数据被插入到亲缘性为NUMERIC的字段中时，如果转换操作不会导致数据信息丢失以及完全可逆，那么SQLite就会将该文本数据转换为INTEGER或REAL类型的数据，如果转换失败，SQLite仍会以TEXT方式存储该数据。对于NULL或BLOB类型的新数据，SQLite将不做任何转换，直接以NULL或BLOB的方式存储该数据。需要额外说明的是，对于浮点格式的常量文本，如"30000.0"，如果该值可以转换为INTEGER同时又不会丢失数值信息，那么SQLite就会将其转换为INTEGER的存储方式 。
INTEGER	对于亲缘类型为INTEGER的字段，其规则等同于NUMERIC，唯一差别是在执行CAST表达式时。
REAL	其规则基本等同于NUMERIC，唯一的差别是不会将"30000.0"这样的文本数据转换为INTEGER存储方式。
NONE	不做任何的转换，直接以该数据所属的数据类型进行存储。

下表列出了当创建 SQLite3 表时可使用的各种数据类型名称，同时也显示了相应的亲和类型：

数据类型	亲和类型
INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8	INTEGER
CHARACTER(20) VARCHAR(255) VARYING CHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT CLOB	TEXT
BLOB 未指定类型	BLOB
REAL DOUBLE DOUBLE PRECISION FLOAT	REAL
NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME	NUMERIC

3. Boolean 数据类型

SQLite 没有单独的 Boolean 存储类。相反，布尔值被存储为整数 0（false）和 1（true）。

4. Date 与 Time 数据类型

SQLite 没有一个单独的用于存储日期和/或时间的存储类，但 SQLite 能够把日期和时间存储为 TEXT、REAL 或 INTEGER 值。

存储类	日期格式
TEXT	格式为 “YYYY-MM-DD HH:MM:SS.SSS” 的日期。
REAL	从公元前 4714 年 11 月 24 日格林尼治时间的正午开始算起的天数。
INTEGER	从 1970-01-01 00:00:00 UTC 算起的秒数。

5. 创建数据库

1.sqlite3 和 .open 创建

# 通常情况下，数据库名称在 RDBMS 内应该是唯一的。
sqlite3 DatabaseName.db
# 另外我们也可以使用 .open 来建立新的数据库文件：
.open test.db

上面的命令创建了数据库文件 test.db，位于 sqlite3 命令同一目录下。

打开已存在数据库也是用 .open 命令，以上命令如果 test.db 存在则直接会打开，不存在就创建它。

2. .dump 导出

# .dump
# 可以在命令提示符中使用 SQLite .dump 点命令来导出完整的数据库在一个文本文件中
sqlite3 testDB.db .dump > testDB.sql
# 通过简单的方式从生成的 testDB.sql 恢复
sqlite3 testDB.db < testDB.sql

6. 附加 / 分离 数据库

当在同一时间有多个数据库可用， ATTACH DATABASE 语句是用来选择一个特定的数据库，附加后可以用附加名指代数据库。

# 如果数据库尚未被创建，将创建一个数据库
# 如果数据库已存在，则把数据库文件名称与逻辑数据库 'Alias-Name' 绑定在一起。
# 打开的数据库和使用 ATTACH附加进来的数据库的必须位于同一文件夹下。
ATTACH DATABASE file_name AS database_name;

DETACH DATABASE 语句是用来把命名数据库从一个数据库连接分离和游离出来。

# 如果同一个数据库文件已经被附加上多个别名，DETACH 命令将只断开给定名称的连接
# 而其余的仍然有效。无法分离 main 或 temp 数据库
DETACH DATABASE database_name

数据库名称 main 和 temp 被保留用于主数据库和存储临时表及其他临时数据对象的数据库。
这两个数据库名称可用于每个数据库连接，且不应该被用于附加和分离，否则将得到一个警告消息。

7. 创建表

CREATE TABLE 语句用于在任何给定的数据库创建一个新表。创建基本表，涉及到命名表、定义列及每一列的数据类型。

8. 删除表

DROP TABLE 语句用来删除表定义及其所有相关数据、索引、触发器、约束和该表的权限规范。

PS: sqlite3 以 ‘；’ 结尾，如果没有 ‘;’ 会继续输入。

9. Insert 语句

INSERT INTO 语句基本语法:

# 1.
INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]  
VALUES (value1, value2, value3,...valueN);

# 2.
# 如果要为表中的所有列添加值,不需要在 SQLite 查询中指定列名称。但要确保值的顺序与列在表中的顺序一致
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

10. Select 语句

SELECT 语句用于从 SQLite 数据库表中获取数据，以结果表的形式返回数据。这些结果表也被称为结果集。

# 获取所有字段
SELECT * FROM table_name;
# 查询部分数据
SELECT column1, column2, columnN FROM table_name;

11. 运算符

运算符是一个保留字或字符，主要用于 SQLite 语句的 WHERE 子句中执行操作，如比较和算术运算。

运算符用于指定 SQLite 语句中的条件，并在语句中连接多个条件。

1. 算术运算符

运算符	描述
+	加法 - 把运算符两边的值相加
-	减法 - 左操作数减去右操作数
*	乘法 - 把运算符两边的值相乘
/	除法 - 左操作数除以右操作数
%	取模 - 左操作数除以右操作数后得到的余数

PS: 其他的同理

2. 比较运算符

假设变量 a=10，变量 b=20

运算符	描述	结果
==	检查两个操作数的值是否相等，如果相等则条件为真。	(a == b) 不为真。
=	检查两个操作数的值是否相等，如果相等则条件为真。	(a = b) 不为真。
!=	检查两个操作数的值是否相等，如果不相等则条件为真。	(a != b) 为真。
<>	检查两个操作数的值是否相等，如果不相等则条件为真。	a <> b) 为真。
>	检查左操作数的值是否大于右操作数的值，如果是则条件为真。	(a > b) 不为真。
<	检查左操作数的值是否小于右操作数的值，如果是则条件为真。	(a < b) 为真。
>=	检查左操作数的值是否大于等于右操作数的值，如果是则条件为真。	(a >= b) 不为真。
<=	检查左操作数的值是否小于等于右操作数的值，如果是则条件为真。	(a <= b) 为真。
!<	检查左操作数的值是否不小于右操作数的值，如果是则条件为真。	(a !< b) 为假。
!>	检查左操作数的值是否不大于右操作数的值，如果是则条件为真。	(a !> b) 为真。

PS: 其他的类似

3. 逻辑运算符

运算符	描述
AND	AND 运算符允许在一个 SQL 语句的 WHERE 子句中的多个条件的存在。
BETWEEN	BETWEEN 运算符用于在给定最小值和最大值范围内的一系列值中搜索值。
EXISTS	EXISTS 运算符用于在满足一定条件的指定表中搜索行的存在。
IN	IN 运算符用于把某个值与一系列指定列表的值进行比较。
NOT IN	IN 运算符的对立面，用于把某个值与不在一系列指定列表的值进行比较。
LIKE	LIKE 运算符用于把某个值与使用通配符运算符的相似值进行比较。
GLOB	GLOB 运算符用于把某个值与使用通配符运算符的相似值进行比较。GLOB 与 LIKE 不同之处在于，它是大小写敏感的。
NOT	NOT 运算符是所用的逻辑运算符的对立面。比如 NOT EXISTS、NOT BETWEEN、NOT IN，等等。它是否定运算符。
OR	OR 运算符用于结合一个 SQL 语句的 WHERE 子句中的多个条件。
IS NULL	NULL 运算符用于把某个值与 NULL 值进行比较。
IS	IS 运算符与 = 相似。
IS NOT	I