sqlite3学习笔记

1.sqlite3简介

1.1 什么是 SQLite？

SQLite，是一款轻型的数据库，是遵守ACID的关系型数据库管理系统，它包含在一个相对小的C库中。它是D.RichardHipp建立的公有领域项目。它的设计目标是嵌入式的，而且目前已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix等等主流的操作系统，同时能够跟很多程序语言相结合，比如 Tcl、C#、PHP、Java等，还有ODBC接口，同样比起Mysql、PostgreSQL这两款开源的世界著名数据库管理系统来讲，它的处理速度比他们都快。SQLite第一个Alpha版本诞生于2000年5月。 至2015年已经有15个年头，SQLite也迎来了一个版本 SQLite 3已经发布。

1.2 为什么要用 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）中运行。

1.3 SQLite 局限性

在 SQLite 中，SQL92 不支持的特性如下所示：

1.4 SQLite 命令

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

DDL - 数据定义语言

DML - 数据操作语言

DQL - 数据查询语言

2.SQLite 命令

如需获取可用的点命令的清单，可以在任何时候输入 ".help"。例如：

sqlite>.help

常用命令：

3.SQLite 数据类型

3.1 SQLite 存储类

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

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

3.2 SQLite 亲和(Affinity)类型

SQLite支持列的亲和类型概念。任何列仍然可以存储任何类型的数据，当数据插入时，该字段的数据将会优先采用亲缘类型作为该值的存储方式。下表列出了当创建 SQLite3 表时可使用的各种数据类型名称，同时也显示了相应的亲和类型：

3.3 Date 与 Time 数据类型

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

您可以以任何上述格式来存储日期和时间，并且可以使用内置的日期和时间函数来自由转换不同格式。

4.SQLite 创建数据库

4.1 语法

sqlite3 命令的基本语法如下：

$ sqlite3 DatabaseName.db

通常情况下，数据库名称在 RDBMS 内应该是唯一的。

另外我们也可以使用 .open 来建立新的数据库文件：

sqlite>.open test.db

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

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

4.2 dump 命令

您可以在命令提示符中使用 SQLite .dump 点命令来导出完整的数据库在一个文本文件中，如下所示：

$sqlite3 testDB.db .dump > testDB.sql

上面的命令将转换整个 testDB.db 数据库的内容到 SQLite 的语句中，并将其转储到 ASCII 文本文件 testDB.sql 中。您可以通过简单的方式从生成的 testDB.sql 恢复，如下所示：

$sqlite3 testDB.db < testDB.sql

4.SQLite表管理命令

4.1 创建表

CREATE TABLE database_name.table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

• 示例

create table department(
    id integer not null,
    dept_name text not null,
    emp_id integet not null,
    primary key(id)
);

sqlite> .tables
department

4.2 查看表的完整信息

可以使用 SQLite的 .schema 命令得到表的完整信息。

sqlite> .schema department
CREATE TABLE department(
id integer not null,
    dept_name text not null,
    emp_id integet not null,
    primary key(id)
);

4.3 删除表

DROP TABLE 语句的基本语法如下。您可以选择指定带有表名的数据库名称，如下所示：

DROP TABLE database_name.table_name;

4.4 修改表Alter 命令

用来重命名已有的表的 ALTER TABLE 的基本语法如下：

ALTER TABLE database_name.table_name RENAME TO new_table_name;

用来在已有的表中添加一个新的列的 ALTER TABLE 的基本语法如下（新添加的列是以 NULL 值来填充的。）：

ALTER TABLE database_name.table_name ADD COLUMN column_def...;

4.5 清空表Truncate操作

在 SQLite 中，并没有 TRUNCATE TABLE 命令，但可以使用 SQLite 的 DELETE 命令从已有的表中删除全部的数据，但建议使用 DROP TABLE 命令删除整个表，然后再重新创建一遍。

5.SQLite表数据增删改查、视图、事务

• 与mysql等数据库基本保持一致

6.java spring项目使用SQLite

6.1 pom.xml

<dependency>
    <groupId>org.xerial</groupId>
    <artifactId>sqlite-jdbc</artifactId>
    <version>3.25.2</version>
</dependency>

6.2 配置文件中设置datasource为sqlite

# application-dev.yaml
server:
  port: 80

spring:
  datasource:
    # url最容易出错，如果使用相对于项目的相对地址，那么填入 jdbc:sqlite::resource:sqlit数据库所在位置
    # 注：
    # :resource: 指向项目的 resources 路径（resource前后两个 `:` 不能省略）
    url: jdbc:sqlite::resource:db/user.sqlite
    driver-class-name: org.sqlite.JDBC
    # username: 选用 sqlite 数据库不需要配置此项
    # password: 选用 sqlite 数据库不需要配置此项
    continue-on-error: true
mybatis-plus:
  mapper-locations: classpath:mapper/*Mapper.xml
  type-aliases-package: com.example.springbootsqlite.model

数据源配好以后，其他操作跟普通 springboot项目一致，mybatis及mybatis-plus 可用

完整项目参考:https://gitee.com/cphovo/springboot-sqlite

参考资料

1. SQLite 教程 | 菜鸟教程 (runoob.com)
2. sqlite3数据库小白入门（Linux） - 知乎 (zhihu.com)
3. SQLite 教程_w3cschool
4. Spring Boot With SQLite | Baeldung
5. IDEA中使用SQLite数据库_idea sqlite_牛右刀薛面的博客-CSDN博客
6. Springboot 整合 Sqlite - kosihpc - 博客园 (cnblogs.com)
7. springboot-sqlite: 一个以 sqlite 为数据库的 springboot-demo (gitee.com)