SQLite3详细介绍

原创

.Vin

修改于 2022-07-01 15:16:15

1.8K0

修改于 2022-07-01 15:16:15

文章被收录于专栏：Rabbit的专栏Rabbit的专栏

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

$~: sqlite3

执行如下命令可以进入并打开一个数据库如果demo.db不存在则会在当前目录下创建一个demo.db文件

$~: sqlite3 demo.db

也可以指定数据库文件的路径如果该路径下不存在demo.db则执行后续命令时会报错

$~: sqlite3 ./db/demo.db

SQLite命令

SQLite命令都以.开头

创建和打开数据库

执行以下命令会在当前目录（执行SQLite3命令时的目录）创建一个新的数据库如果数据库文件已存在，将会打开数据库文件

sqlite> .open demo.db

当创建好数据库后，我们就可以在数据库中执行sql语句了

sqlite> CREATE TABLE stu(id int);
sqlite> INSERT INTO stu VALUES(1);
sqlite> SELECT * FROM stu;

列出所有打开的数据库

相当于mysql的show databases命令

sqlite> .databases

列出所有表

相当于mysql的show tables命令

sqlite> .tables

退出SQLite

退出SQLite命令为.quit或.exit

sqlite> .quit

sqlite> .exit

备份与恢复

SQLite备份数据库的命令为.backup或者.save,其作用是将当前数据库备份到指定的文件中

sqlite> .backup demo_backup.bak
sqlite> .backup demo_save.bak

执行.exit退出SQLite,执行ls查看当前目录下的文件，可以发现当前目录下多出了demo_back.bak和demo_save.bak两个文件重新进入一个新数据库，使用.restore命令恢复数据库

sqlite> .restore demo_backup.bak
sqlite> .restore demo_save.bak

查看所有的命令

SQLite还有很多常见的命令执行.help可以查看SQLite中所有的命令

sqlite> .help

图形化操作

这里选择使用idea自带的数据库管理器为例

连接SQLite数据库

点击idea最右侧的数据库图标，点击+号，选择数据源，选择SQLite
在打开的窗口中选择文件旁的三点图标
选择已有的数据库文件,点击确定
测试连接在第二步中，我们也可以直接点击确定，idea会帮助我们在当前项目目录下创建一个数据库文件
当连接成功后，我们就可以使用图形化的方式操作数据库了

SQL语法

SQLite具体的SQL语法可以在官网查看 https://sqlite.org/lang.html SQLite中很多SQL语法都与MySQL类似，以下内容偏重于介绍SQLite中和MySQL不同的地方

创建表

SQLite创建表可以不指定数据类型，可以在其中保存任意类型的数据

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创建表可以随便指定不存在的类型,不会报错

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的删除列功能

-- 创建表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，没有报错

-- 删除test表中的age字段
ALTER TABLE test DROP COLUMN age;

如果删除的是id列，直接删除就会报错，原因是id是主键，属于上述八种情况之一对于上述不支持直接删除列的情况,可以采用如下方式来删除同时，3.35.0版本之前，我们也只能通过以下方式来删除列

-- 删除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中只有内连接和左外连接

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可以开启三类不同的事务,分别是:

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 之后的状态。

实例

为了测试事务，我们首先要创建一张账户表，用于模拟两个人转账的操作

CREATE TABLE account
(
    user  text    not null,
    money integer not null
);
INSERT INTO account
VALUES ('a', 100);
INSERT INTO account
VALUES ('b', 100);

定义一个数据库初始化的操作，用于重新初始化账户表方便在测试后恢复数据

-- 数据库初始化
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：没有回滚操作

-- 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：在保存点中进行回滚操作，在外层事务中进行提交

-- 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：在保存点中进行提交（释放保存点），在外层事务中进行回滚

-- 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:100

SQLite中数据的备份与恢复

方法一：直接进行文件的复制粘贴

由于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脚本数据到控制台中

sqlite> .dump

可以在.dump命令中指定表名，只会生成指定表的SQL脚本

sqlite> .dump user

可以使用输出重定向的方式将该SQL脚本写入到文件中(需要先退出SQLite)

sqlite3 demo.db ".dump" > demo.sql

SQLite与Java交互

SQLite与Java交互需要下载JDBC，可以在github上直接下载 https://github.com/xerial/SQLite-jdbc/tree/3.36.0.3

在项目导入jar包后，就可以使用JDBC连接SQLite了

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:数据库文件名

String url="jdbc:sqlite:demo.db";
// 等价于
        String url="jdbc:sqlite:./demo.db";

如果使用绝对地址，则推荐使用如下方式拼接得到绝对地址

String pwd=System.getProperty("user.dir");
        String url="jdbc:sqlite:"+pwd+"/demo.db";

关于数据库连接用户名和密码的说明

SQLite中没有用户名和密码的概念，我们在连接数据库时不需要指定用户名和密码。

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

sqlite

sql

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

sqlite

sql

登录后参与评论

0 条评论

热度

SQLite3详细介绍

SQLite3详细介绍

SQLite3详细介绍

介绍

特点

安装

控制台操作

进入SQLite数据库

SQLite命令

创建和打开数据库

列出所有打开的数据库

列出所有表

退出SQLite

备份与恢复

查看所有的命令

图形化操作

连接SQLite数据库

SQL语法

创建表

数据类型

删除列

对SQLite中删除列进行测试

多表连接

存储过程和函数

用户管理

SQLite中的事务

BEGIN TRANSACTION 命令

COMMIT 命令； END TRANSACTION 命令

SAVEPOINT 命令

RELEASE 命令

ROLLBACK TO 命令

实例

SQLite中数据的备份与恢复

方法一：直接进行文件的复制粘贴

方法二：通过.backup 命令和.restore 命令

方法三：通过生成SQL脚本

SQLite与Java交互

关于数据库连接url的说明

关于数据库连接用户名和密码的说明

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐