跳到主要内容

SQLite学习

SQLite 是什么

SQLite 是一跨 嵌入式数据库,数据库引擎作为应用程序的一部分运行,一般就应用在本地应用程序中(例如各种 APP),它不像 MySQL 这类数据库那么庞大,完全配置时小于 400KiB,省略可选功能配置时小于250KiB,与其说它像一个完整的数据库,不如说它更像一个 lib。所以只适合一些简单的应用(在 SpringBoot 上也可以搭配 MyBatis 使用)

换句话说可以使用 MySQL 做一个数据库服务器,它需要执行独立的服务进程(MySQL需要运行服务器,MySQL 将需要客户端和服务器架构通过网络进行交互),而 SQLite 则是直接集成在宿主服务上一起共享同一个进程

不过正因为它小,所以很多复杂的语句是不支持的(使用的是 SQL)它支持以下命令

DDL - 数据定义语言

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

DML - 数据操作语言

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

DQL - 数据查询语言

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

配置环境

官网 下载页面

如果在 Win10 上运行,下载 lib 之后配置下环境变量,首先需要下载 sqlite-tools-win32-.zip 和 sqlite-dll-win32-.zip 压缩文件。

SQLite 官网下载页

创建文件夹 C:\sqlite,并在此文件夹下解压上面两个压缩文件,将得到 sqlite3.defsqlite3.dllsqlite3.exe 文件。

添加 C:\sqlite 到 PATH 环境变量,最后在命令提示符下,使用 sqlite3 命令,将显示如下结果。

或者直接在 Ubuntu 安装

# 但是这样的版本好旧啊,想要最新的还是去官网下载吧
apt install sqlite3

# 输入 sqlite3 检查是否安装成功
sqlite3

进入终端后,需获取可用的点命令的清单,可以在任何时候输入 .help

.exit 退出

快速使用

可以使用 sqlite3 + 数据库文件名 来查看生成的数据库(.db 后缀的文件)

在 sqlite3 提示符界面,SQLite 命令已点号(.)开头,比如:

.databases 列出数据库的名称(数据库文件路径)。 .tables 列出数据库里的表(也可以使用 .table)。 .schema {表名} 列出表的建表语句。

sqlite> .table
User

sqlite> .schema User
CREATE TABLE User(Name text, Age integer);

.indices {表名} 列出表的索引名称。 .exit 退出 SQLite 提示符。 .quit 退出 SQLite 提示符。 .head on 打开显示列名的开关

sqlite> .head on

# 查找 `Age > 20` 的记录;
sqlite> SELECT * FROM User WHERE Age > 20;
Name|Age
Jack|25

# 统计记录个数。
sqlite> SELECT COUNT(*) FROM User;
COUNT(*)
2

常用命令(大全)

命令描述
.backup ?DB? FILE备份 DB 数据库(默认是 "main")到 FILE 文件。
.bail ON|OFF发生错误后停止。默认为 OFF。
.databases列出数据库的名称及其所依附的文件。
.dump ?TABLE?以 SQL 文本格式转储数据库。如果指定了 TABLE 表,则只转储匹配 LIKE 模式的 TABLE 表。
.echo ONOFF
.exit退出 SQLite 提示符。
.explain ON|OFF开启或关闭适合于 EXPLAIN 的输出模式。如果没有带参数,则为 EXPLAIN on,即开启 EXPLAIN。
.header(s) ON|OFF开启或关闭头部显示。
.help显示消息。
.import FILE TABLE导入来自 FILE 文件的数据到 TABLE 表中。
.indices ?TABLE?显示所有索引的名称。如果指定了 TABLE 表,则只显示匹配 LIKE 模式的 TABLE 表的索引。
.load FILE ?ENTRY?加载一个扩展库。
.log FILE|off开启或关闭日志。FILE 文件可以是 stderr(标准错误)/stdout(标准输出)。
.nullvalue STRING在 NULL 值的地方输出 STRING 字符串。
.output FILENAME发送输出到 FILENAME 文件。
.output stdout发送输出到屏幕。
.print STRING...逐字地输出 STRING 字符串。
.prompt MAIN CONTINUE替换标准提示符。
.quit退出 SQLite 提示符。
.read FILENAME执行 FILENAME 文件中的 SQL。
.schema ?TABLE?显示 CREATE 语句。如果指定了 TABLE 表,则只显示匹配 LIKE 模式的 TABLE 表。
.separator STRING改变输出模式和 .import 所使用的分隔符。
.show显示各种设置的当前值。
.stats ON|OFF开启或关闭统计。
.tables ?PATTERN?列出匹配 LIKE 模式的表的名称。
.timeout MS尝试打开锁定的表 MS 毫秒。
.width NUM NUM为 "column" 模式设置列宽度。
.timer ON|OFF开启或关闭 CPU 定时器。

.mode MODE 设置输出模式,MODE 可以是下列之一:

  • csv 逗号分隔的值
  • column 左对齐的列
  • html HTML 的 <table> 代码
  • insert TABLE 表的 SQL 插入(insert)语句
  • line 每行一个值
  • list 由 .separator 字符串分隔的值
  • tabs 由 Tab 分隔的值
  • tcl TCL 列表元素

例子:使输出格式化

sqlite>.header on
sqlite>.mode column
sqlite>.timer on
sqlite>

查看所有表(要先指定数据库进去才行)

sqlite3 studysqlite.db
select * from sqlite_master;

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 作为存储类 型,0 为 false,1 为 true

Sqlite 也没有另外为存储日期和时间设定一个存储类集,内置的 sqlite 日期和时间函数能够将日期和时间以 TEXT、REAL 或 INTEGER 形式存放

存储时间

参考资料 菜鸟教程 SQLite 存储时间

SQLite 支持以下五个日期和时间函数: 函数 | 实例 ----|--- date(timestring, modifier, modifier, ...) | 以 YYYY-MM-DD 格式返回日期。 time(timestring, modifier, modifier, ...) | 以 HH:MM:SS 格式返回时间。 datetime(timestring, modifier, modifier, ...) | 以 YYYY-MM-DD HH:MM:SS 格式返回。 julianday(timestring, modifier, modifier, ...) | 这将返回从格林尼治时间的公元前 4714 年 11 月 24 日正午算起的天数。 strftime(format, timestring, modifier, modifier, ...) | 这将根据第一个参数指定的格式字符串返回格式化的日期。

时间字符串 一个时间字符串可以采用下面任何一种格式:

序号时间字符串实例
1YYYY-MM-DD2010-12-30
2YYYY-MM-DD HH:MM2010-12-30 12:10
3YYYY-MM-DD HH:MM:SS.SSS2010-12-30 12:10:04.100
4MM-DD-YYYY HH:MM30-12-2010 12:10
5HH:MM12:10
6YYYY-MM-DDTHH:MM2010-12-30 12:10
7HH:MM:SS12:10:01
8YYYYMMDD HHMMSS20101230 121001
9now2013-05-07

使用 "T" 作为分隔日期和时间的文字字符

SELECT date('now');
-- 2020-12-12
SELECT datetime(1092941466, 'unixepoch');
-- 2004-08-19 18:51:06
SELECT datetime(1092941466, 'unixepoch', 'localtime');
-- 2004-08-19 11:51:06
SELECT strftime('%s','now'); -- 计算当前的 UNIX 时间戳
-- 1367926057
-- 例如使用 Integer 来存储
CREATE TABLE IF NOT EXISTS Journal(
id INTEGER PRIMARY KEY AUTOINCREMENT,
content TEXT,
writeTime INTEGER
);

综上,存储时间需要使用其它的格式

连接上 SQLite

连接文件系统数据库的 URL 语法:

jdbc:sqlite:database_file_path

其中 database_file_path 可以是相对路径,也可以是绝对路径

例如下面这个:

jdbc:sqlite:C:\Users\alsritter\Desktop\temp\studysqlite.db

至于像 MySQL 那样 URL 后面还要跟一个数据库名称,完全没有必要,因为 SQLite 一般都是单个文件就对应单个库(默认的 main),而 MySQL 它是一个独立的 数据库服务器,能够同时处理多个数据库的操作

操作数据库

创建数据库

SQLite 的 sqlite3 命令被用来创建新的 SQLite 数据库(不需要任何特殊的权限即可创建一个数据)

$sqlite3 DatabaseName.db

注意,创建后好像暂时看不到数据库文件的,可以通过 .databases 命令查看,或者直接退出命令行 .quit 就会出现这个文件

查看表结构

.schema table_name

附加数据库

就是同时使用多个数据库,在 MySQL 这类数据库是默认帮忙管理多个数据库的,而在 SQLite 则是一个数据库对应一个文件,如何同时操作多个数据库呢?

可以使用 SQLite 的 ATTACH DATABASE 语句附加其它的数据库

-- 也可以给这个数据库起别名
ATTACH DATABASE file_name AS database_name;

如果数据库尚未被创建,上面的命令将创建一个数据库,如果数据库已存在,则把数据库文件名称与逻辑数据库 'database_name' 绑定在一起。

这时再查看数据库就会发现有两个数据库了

sqlite> .database
seq name file
--- --------------- ----------------------
0 main /home/sqlite/testDB.db
2 test /home/sqlite/testDB.db

分离数据库

有了附加那就有分离,SQLite 的 DETACH DATABASE 语句是用来把命名数据库从一个数据库连接分离和游离出来,连接是之前使用 ATTACH 语句附加的。如果同一个数据库文件已经被附加上多个别名,DETACH 命令将只断开给定名称的连接,而其余的仍然有效

DETACH DATABASE 'Alias-Name';
sqlite>.databases
seq name file
--- --------------- ----------------------
0 main /home/sqlite/testDB.db
2 test /home/sqlite/testDB.db
3 currentDB /home/sqlite/testDB.db
sqlite> DETACH DATABASE 'currentDB'; -- 分离这个 currentDB
sqlite>.databases
seq name file
--- --------------- ----------------------
0 main /home/sqlite/testDB.db
2 test /home/sqlite/testDB.db