sqlite3学习笔记

jupiter
2023-08-16 / 0 评论 / 40 阅读 / 正在检测是否收录...
温馨提示:
本文最后更新于2023年10月31日,已超过116天没有更新,若内容或图片失效,请留言反馈。

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 不支持的特性如下所示:

特性描述
RIGHT OUTER JOIN只实现了 LEFT OUTER JOIN。
FULL OUTER JOIN只实现了 LEFT OUTER JOIN。
ALTER TABLE支持 RENAME TABLE 和 ALTER TABLE 的 ADD COLUMN variants 命令,不支持 DROP COLUMN、ALTER COLUMN、ADD CONSTRAINT。
Trigger 支持支持 FOR EACH ROW 触发器,但不支持 FOR EACH STATEMENT 触发器。
VIEWs在 SQLite 中,视图是只读的。您不可以在视图上执行 DELETE、INSERT 或 UPDATE 语句。
GRANT 和 REVOKE可以应用的唯一的访问权限是底层操作系统的正常文件访问权限。

1.4 SQLite 命令

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

DDL - 数据定义语言

命令描述
CREATE创建一个新的表,一个表的视图,或者数据库中的其他对象。
ALTER修改数据库中的某个已有的数据库对象,比如一个表。
DROP删除整个表,或者表的视图,或者数据库中的其他对象。

DML - 数据操作语言

命令描述
INSERT创建一条记录。
UPDATE修改记录。
DELETE删除记录。

DQL - 数据查询语言

命令描述
SELECT从一个或多个表中检索某些记录。

2.SQLite 命令

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

sqlite>.help

常用命令:

命令描述
.backup ?DB? FILE备份 DB 数据库(默认是 "main")到 FILE 文件。
.databases列出数据库的名称及其所依附的文件。
.dump ?TABLE?以 SQL 文本格式转储数据库。如果指定了 TABLE 表,则只转储匹配 LIKE 模式的 TABLE 表。
.exit退出 SQLite 提示符。
.header(s) ON或OFF开启或关闭头部显示。
.help显示所有命令列表和帮助信息。
.import FILE TABLE导入来自 FILE 文件的数据到 TABLE 表中。
.mode MODE设置输出模式,MODE 可以是下列之一:csv 逗号分隔的值column 左对齐的列html HTML 的 \<table\> 代码insert TABLE 表的 SQL 插入(insert)语句line 每行一个值list 由 .separator 字符串分隔的值tabs 由 Tab 分隔的值tcl TCL 列表元素
.quit退出 SQLite 提示符。
.read FILENAME执行 FILENAME 文件中的 SQL。
.schema ?TABLE?显示 CREATE 语句。如果指定了 TABLE 表,则只显示匹配 LIKE 模式的 TABLE 表。
.separator STRING改变输出模式和 .import 所使用的分隔符。
.show显示各种设置的当前值。
.tables ?PATTERN?列出匹配 LIKE 模式的表的名称。

3.SQLite 数据类型

3.1 SQLite 存储类

每个存储在 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 种不同的不同长度的整数数据类型。

3.2 SQLite 亲和(Affinity)类型

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

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

3.3 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 算起的秒数。

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

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 配置文件中设置datasourcesqlite

# 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项目一致,mybatismybatis-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)
0

评论 (0)

打卡
取消