SQLite3详细介绍
介绍
SQLite 是一个嵌入式 SQL 数据库引擎，它实现了一个自包含、无服务器、零配置、事务性 SQL 数据库引擎。 SQLite 的代码属于公共领域，因此可以免费用于任何商业或私人目的。 SQLite 是世界上部署最广泛的数据库，其应用程序数量之多，数不胜数。
特点
- 零配置
- 占用空间少，源代码仅有2MB
- 速度快，占用内存少
- 嵌入式数据库引擎，不需要服务器进程，一个数据库就是磁盘上的一个文件
- 没有用户名和密码的概念
- 开放源码，任何人都可以使用
- 跨平台——可以在 32 位和 64 位系统之间或在大端和小端架构之间自由复制数据库。
- 由国际开发团队全职开发
安装
安装可参考官网 https://www.sqlite.org/download.html
Source Code 是SQLite的源代码
Documentation是官方文档的html下载
Precompiled Binaries是编译好的二进制文件，也是我们需要下载的内容
下载完成后直接运行sqlite3(Windows下是sqlite3.exe)即可进入SQLite
控制台操作
进入SQLite数据库
代码语言：javascript复制$~: sqlite3执行如下命令可以进入并打开一个数据库 如果demo.db不存在则会在当前目录下创建一个demo.db文件
代码语言：javascript复制$~: sqlite3 demo.db也可以指定数据库文件的路径 如果该路径下不存在demo.db则执行后续命令时会报错
代码语言：javascript复制$~: sqlite3 ./db/demo.dbSQLite命令
SQLite命令都以.开头
创建和打开数据库
执行以下命令会在当前目录（执行SQLite3命令时的目录）创建一个新的数据库 如果数据库文件已存在，将会打开数据库文件
代码语言：javascript复制sqlite> .open demo.db当创建好数据库后，我们就可以在数据库中执行sql语句了
代码语言：javascript复制sqlite> CREATE TABLE stu(id int);
sqlite> INSERT INTO stu VALUES(1);
sqlite> SELECT * FROM stu;列出所有打开的数据库
相当于mysql的show databases命令
代码语言：javascript复制sqlite> .databases列出所有表
相当于mysql的show tables命令
代码语言：javascript复制sqlite> .tables退出SQLite
退出SQLite命令为.quit或.exit
代码语言：javascript复制sqlite> .quitsqlite> .exit备份与恢复
SQLite备份数据库的命令为.backup或者.save,其作用是将当前数据库备份到指定的文件中
代码语言：javascript复制sqlite> .backup demo_backup.bak
sqlite> .backup demo_save.bak执行.exit退出SQLite,执行ls查看当前目录下的文件，可以发现当前目录下多出了demo_back.bak和demo_save.bak两个文件 重新进入一个新数据库，使用.restore命令恢复数据库
代码语言：javascript复制sqlite> .restore demo_backup.bak
sqlite> .restore demo_save.bak查看所有的命令
SQLite还有很多常见的命令 执行.help可以查看SQLite中所有的命令
代码语言：javascript复制sqlite> .help图形化操作
这里选择使用idea自带的数据库管理器为例
连接SQLite数据库
- 点击idea最右侧的数据库图标，点击 号，选择数据源，选择SQLite
- 在打开的窗口中选择文件旁的三点图标
- 选择已有的数据库文件,点击确定
- 测试连接 在第二步中，我们也可以直接点击确定，idea会帮助我们在当前项目目录下创建一个数据库文件
- 当连接成功后，我们就可以使用图形化的方式操作数据库了
点击idea最右侧的数据库图标，点击 号，选择数据源，选择SQLite 
在打开的窗口中选择文件旁的三点图标
选择已有的数据库文件,点击确定 
连接完成后，界面如下SQL语法
SQLite具体的SQL语法可以在官网查看 https://sqlite.org/lang.html SQLite中很多SQL语法都与MySQL类似，以下内容偏重于介绍SQLite中和MySQL不同的地方
创建表
SQLite创建表可以不指定数据类型，可以在其中保存任意类型的数据
代码语言：javascript复制CREATE TABLE user
(
a,
b,
c,
d
);
INSERT INTO user (a, b, c, d)
VALUES (1, 'a', CURRENT_TIME, 1.732);
INSERT INTO user (a, b, c, d)
VALUES (CURRENT_TIMESTAMP, pi(), NULL, NULL);SQLite创建表可以随便指定不存在的类型,不会报错
代码语言：javascript复制CREATE TABLE user_5
(
a effs,
b afsf,
c qwrqr,
d po4og
);
INSERT INTO user_5 (a, b, c, d)
VALUES (1, 'a', CURRENT_TIME, 1.732);
INSERT INTO user_5 (a, b, c, d)
VALUES (CURRENT_TIMESTAMP, pi(), NULL, NULL);
SELECT *
FROM user_5;数据类型
相比于mysql,SQLite支持的数据类型很少
- SQLite支持以下数据类型： Blob，Integer，Null，Text，Real。
- MySQL支持下面提到的数据类型： Tinyint, Smallint, Mediumint, Int, Bigint, Double, Float, Real, Decimal, Double precision, Numeric, Timestamp, Date, Datetime, Char, Varchar, Year, Tinytext, Tinyblob, Blob, Text, MediumBlob, MediumText, Enum, Set, Longblob, Longtext.
删除列
3.35.0版本之前，SQLite是不支持使用ALTER TABLE DROP COLUMN直接删除列的 3.35.0版本后，SQLite开始支持删除列，但有八条限制 官方对此有说明：
The DROP COLUMN syntax is used to remove an existing column from a table. The DROP COLUMN command removes the named column from the table, and rewrites its content to purge the data associated with that column. The DROP COLUMN command only works if the column is not referenced by any other parts of the schema and is not a PRIMARY KEY and does not have a UNIQUE constraint. Possible reasons why the DROP COLUMN command can fail include:
- The column is a PRIMARY KEY or part of one.
- The column has a UNIQUE constraint.
- The column is indexed.
- The column is named in the WHERE clause of a partial index.
- The column is named in a table or column CHECK constraint not associated with the column being dropped.
- The column is used in a foreign key constraint.
- The column is used in the expression of a generated column.
- The column appears in a trigger or view.
简单翻译一下，SQLite可以直接删除列,但有以下八种情况会导致删除列失败
- 该列是 PRIMARY KEY 或其中的一部分。
- 该列具有 UNIQUE 约束。
- 该列已编入索引。
- 该列在部分索引的 WHERE 子句中命名。
- 该列在与要删除的列无关的表或列 CHECK 约束中命名。
- 该列用于外键约束。
- 该列用于生成列的表达式。
- 该列出现在触发器或视图中。
对SQLite中删除列进行测试
首先创建一张测试表，用于测试SQLite的删除列功能
代码语言：javascript复制-- 创建表test,有id,name,age三个字段
CREATE TABLE test
(
id integer PRIMARY KEY AUTOINCREMENT,
name text,
age integer
);
-- 给test表中插入测试数据
INSERT INTO test
VALUES (NULL, 'a', 18);测试直接删除age列 由于我使用的SQLite版本是3.38.2，没有报错
代码语言：javascript复制-- 删除test表中的age字段
ALTER TABLE test DROP COLUMN age;如果删除的是id列，直接删除就会报错，原因是id是主键，属于上述八种情况之一 对于上述不支持直接删除列的情况,可以采用如下方式来删除 同时，3.35.0版本之前，我们也只能通过以下方式来删除列
代码语言：javascript复制-- 删除test表中id列,由于id是主键,所以会报错
ALTER TABLE test DROP COLUMN id;
-- 创建表test_tmp,只有name字段
CREATE TABLE test_tmp
(
name text
);
-- 将test的数据复制到test_tmp中
INSERT INTO test_tmp
SELECT name
FROM test;
-- 删除test表
DROP TABLE test;
-- 将test_tmp表名改为test
ALTER TABLE test_tmp
RENAME TO test;关于SQLite的其它的更新记录，我们可以在官网查看 https://www.sqlite.org/changes.html
多表连接
SQLite从3.39.0版本之后（2022-06-25）才开始支持右连接和全连接 在之前的版本中SQLite中只有内连接和左外连接
代码语言：javascript复制SELECT * FROM student JOIN sc s on student.sno = s.sno;
SELECT * FROM student LEFT JOIN sc s on student.sno = s.sno;
-- 3.39.0版本之前会报错
SELECT * FROM student RIGHT JOIN sc s on student.sno = s.sno;
SELECT * FROM student FULL JOIN sc s on student.sno = s.sno;存储过程和函数
SQLite 没有存储函数/存储过程语言 用户不可以自定义存储过程/函数
用户管理
SQLite中没有任何特定的用户管理功能，因此不适合多用户访问。
SQLite中的事务
官网对于SQLite中的事务有详细的介绍,具体可参考 https://www.sqlite.org/lang_transaction.html https://www.sqlite.org/lang_savepoint.html
SQLite 默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作
因此要显式地开启一个事务务须使用命令 BEGIN TRANSACTION
SQLite 中可以使用下面的命令来控制事务
语句 | 功能 |
|---|---|
BEGIN TRANSACTION; BEGIN DEFERRED TRANSACTION; BEGIN IMMEDIATE TRANSACTION; BEGIN EXCLUSIVE TRANSACTION; | 开始事务处理 |
COMMIT END TRANSACTION | 保存更改 |
ROLLBACK | 回滚所做的更改 |
SAVEPOINT | 保存点 |
RELEASE | 释放保存点 |
ROLLBACK TO | 回滚到保存点 |
事务控制命令只与 DML 命令 INSERT、UPDATE 和 DELETE 一起使用
他们不能在创建表或删除表时使用，因为这些操作在数据库中是自动提交的
BEGIN TRANSACTION 命令
事务 ( Transaction ) 可以使用 BEGIN TRANSACTION 命令或 BEGIN 命令来启动 具体来说,SQLite可以开启三类不同的事务,分别是:
代码语言：javascript复制BEGIN DEFERRED TRANSACTION;
BEGIN IMMEDIATE TRANSACTION;
BEGIN EXCLUSIVE TRANSACTION;默认情况下是第一种方式
- deferred（延迟的）: 延迟提交,即事务提交时,不会立即执行,而是等到下一次提交时才执行
- immediate（立即的）: 立即提交,即事务提交时,会立即执行
- exclusive（排他的）: EXCLUSIVE事务也会立即提交。EXCLUSIVE 和 IMMEDIATE 在 WAL 模式下是一样的，但在其他日志模式下，EXCLUSIVE 会阻止其他数据库连接在事务进行时读取数据库。
一般来说，我们使用的都是第一种方式
COMMIT 命令； END TRANSACTION 命令
COMMIT 命令和END TRANSACTION命令用于提交更改，结束事务 不能重复使用BEGIN TRANSACTION命令来开启事务,只有上一次的事务使用COMMIT 命令或END TRANSACTION命令结束后才能开启新的事务
SAVEPOINT 命令
我们不能重复使用BEGIN TRANSACTION命令来在事务里开启事务，但可以使用SAVEPOINT 命令在事务里启动一个具有名称的新事务。 对于SAVEPOINT，有几点值得关注
- SAVEPOINT 命令启动一个具有名称的新事务。事务名称不必是唯一的。
- SAVEPOINT 可以在 BEGIN...COMMIT 之内或之外启动。
- 当 SAVEPOINT 是最外层的保存点并且它不在 BEGIN...COMMIT 中时，行为与 BEGIN DEFERRED TRANSACTION 相同
RELEASE 命令
RELEASE 命令类似于 SAVEPOINT 中的 COMMIT RELEASE 命令可用于释放指定的保存点并提交事务，而COMMIT 命令可用于释放所有保存点并提交事务
ROLLBACK TO 命令
相当于SAVEPOINT 中的 ROLLBACK ROLLBACK TO 命令将数据库的状态恢复到在相应的 SAVEPOINT 之后的状态。
实例
为了测试事务，我们首先要创建一张账户表，用于模拟两个人转账的操作
代码语言：javascript复制CREATE TABLE account
(
user text not null,
money integer not null
);
INSERT INTO account
VALUES ('a', 100);
INSERT INTO account
VALUES ('b', 100);定义一个数据库初始化的操作，用于重新初始化账户表 方便在测试后恢复数据
代码语言：javascript复制-- 数据库初始化
DELETE
FROM account;
INSERT INTO account
VALUES ('a', 100);
INSERT INTO account
VALUES ('b', 100);- 直接提交
BEGIN;
--等价于BEGIN DEFERRED TRANSACTION
-- 初始状态 a:100 b:100
UPDATE account
SET money = money - 50
WHERE user = 'a';
UPDATE account
SET money = money 50
WHERE user = 'b';
-- a:50 b:150
SELECT *
FROM account;
COMMIT;
-- 等价于END TRANSACTION;
-- a:50 b:150
SELECT *
FROM account;- 回滚操作
BEGIN;
--等价于BEGIN DEFERRED TRANSACTION
-- 初始状态 a:100 b:100
UPDATE account
SET money = money - 50
WHERE user = 'a';
UPDATE account
SET money = money 50
WHERE user = 'b';
-- a:50 b:150
SELECT *
FROM account;
ROLLBACK;
-- a:100 b:100
SELECT *
FROM account;- 使用保存点
实例1：没有回滚操作
代码语言：sql复制-- a:100 b:100
BEGIN TRANSACTION;
UPDATE account SET money = money - 50 WHERE user = 'a';
UPDATE account SET money = money 50 WHERE user = 'b';
-- a:50 b:150
-- 设置保存点sp1，相当于在事务中开启了一个新事务
SAVEPOINT sp1;
UPDATE account SET money = money - 50 WHERE user = 'a';
UPDATE account SET money = money 50 WHERE user = 'b';
-- a:50 b:150
-- 释放保存点，相当于commit这个事务
RELEASE sp1;
END TRANSACTION;实例2：在保存点中进行回滚操作，在外层事务中进行提交
代码语言：sql复制-- a:100 b:100
BEGIN TRANSACTION;
UPDATE account SET money = money - 50 WHERE user = 'a';
UPDATE account SET money = money 50 WHERE user = 'b';
-- a:50 b:150
-- 设置保存点sp1，相当于在事务中开启了一个新事务
SAVEPOINT sp1;
UPDATE account SET money = money - 50 WHERE user = 'a';
UPDATE account SET money = money 50 WHERE user = 'b';
-- a:0 b:200
-- 回滚
ROLLBACK TO sp1;
-- a:50 b:150
END TRANSACTION;实例3：在保存点中进行提交（释放保存点），在外层事务中进行回滚
代码语言：sql复制-- a:100 b:100
BEGIN TRANSACTION;
UPDATE account SET money = money - 50 WHERE user = 'a';
UPDATE account SET money = money 50 WHERE user = 'b';
-- a:50 b:150
-- 设置保存点sp1，相当于在事务中开启了一个新事务
SAVEPOINT sp1;
UPDATE account SET money = money - 50 WHERE user = 'a';
UPDATE account SET money = money 50 WHERE user = 'b';
-- a:0 b:200
-- 释放保存点
RELEASE sp1;
-- a:0 b:200
ROLLBACK;
-- a:100 b:100SQLite中数据的备份与恢复
方法一：直接进行文件的复制粘贴
由于SQLite一个数据库就是磁盘上的一个文件，所以直接复制粘贴该文件就是最简单的备份数据库的方式 使用.clone命令也可以达到复制粘贴的效果
方法二：通过.backup 命令和.restore 命令
- 备份命令: .backup或者.save,功能是将数据库备份到指定的文件
sqlite> .backup demo_backup.bak
sqlite> .backup demo_save.bak- 恢复命令: .restore,将数据库文件用生成的备份文件恢复
sqlite> .restore demo_backup.bak
sqlite> .restore demo_save.bak方法三：通过生成SQL脚本
直接使用.dump命令，会将SQL脚本数据到控制台中
代码语言：javascript复制sqlite> .dump可以在.dump命令中指定表名，只会生成指定表的SQL脚本
代码语言：javascript复制sqlite> .dump user可以使用输出重定向的方式将该SQL脚本写入到文件中(需要先退出SQLite)
代码语言：javascript复制sqlite3 demo.db ".dump" > demo.sqlSQLite与Java交互
SQLite与Java交互需要下载JDBC，可以在github上直接下载 https://github.com/xerial/SQLite-jdbc/tree/3.36.0.3
在项目导入jar包后，就可以使用JDBC连接SQLite了
代码语言：javascript复制import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
public static void main(String[] args) {
try {
Class.forName("org.sqlite.JDBC");
String url = "jdbc:sqlite:demo.db";
Connection connection = DriverManager.getConnection(url);
Statement statement = connection.createStatement();
String sql = "SELECT * FROM student";
ResultSet rs = statement.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString("sno") " " rs.getString("sname") " " rs.getString("sage"));
}
} catch (Exception e) {
e.printStackTrace();
}
}
}关于数据库连接url的说明
SQLite中由于一个数据库是一个单一的磁盘文件，所以在url中不需要像MySQL一样指定ip地址和端口号。 我们只需要在url中指定数据库文件的路径即可。 格式如下: jdbc:sqlite:数据库文件路径
如果我们将数据库文件放在当前项目目录下，那么在url中就不需要指定路径了，直接使用如下格式: jdbc:sqlite:数据库文件名
代码语言：javascript复制String url="jdbc:sqlite:demo.db";
// 等价于
String url="jdbc:sqlite:./demo.db";如果使用绝对地址，则推荐使用如下方式拼接得到绝对地址
代码语言：javascript复制String pwd=System.getProperty("user.dir");
String url="jdbc:sqlite:" pwd "/demo.db";关于数据库连接用户名和密码的说明
SQLite中没有用户名和密码的概念，我们在连接数据库时不需要指定用户名和密码。
