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 压缩文件。
创建文件夹 C:\sqlite,并在此文件夹下解压上面两个压缩文件,将得到 sqlite3.def
、sqlite3.dll
和 sqlite3.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 ON | OFF |
.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, ...) | 这将根据第一个参数指定的格式字符串返回格式化的日期。
时间字符串 一个时间字符串可以采用下面任何一种格式:
序号 | 时间字符串 | 实例 |
---|---|---|
1 | YYYY-MM-DD | 2010-12-30 |
2 | YYYY-MM-DD HH:MM | 2010-12-30 12:10 |
3 | YYYY-MM-DD HH:MM:SS.SSS | 2010-12-30 12:10:04.100 |
4 | MM-DD-YYYY HH:MM | 30-12-2010 12:10 |
5 | HH:MM | 12:10 |
6 | YYYY-MM-DDTHH:MM | 2010-12-30 12:10 |
7 | HH:MM:SS | 12:10:01 |
8 | YYYYMMDD HHMMSS | 20101230 121001 |
9 | now | 2013-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