SQLite

SQLite是一款轻量级，遵守ACID事务原则的关系型数据库。SQLite可以直接嵌入到代码中，无需像MySQL、PostgreSQL需启动独立的服务才能使用。

SQLite将数据存储在单一磁盘文件中，使用方便，适合学习关系型数据库。

SQLite是一种嵌入式数据库，它的数据库就是一个文件。由于SQLite本身是C编写的，体积很小。因此经常被集成到各种应用程序中，甚至在IOS和Android的App中都可以集成。Python就内置了SQLite3。

数据表是数据库中存放关系数据的集合，一个数据库包含多个表，表和表之间通过外键关联。要操作关系数据库，首先需要连接到数据库，一个数据库连接称为Connection。连接到关系数据库后需要打开游标(Cursor)，通过游标执行SQL语句后获得执行结果。

SQLite是一个进程内的库，实现了自给自足的、无服务器的、零配置的、事务性的SQL数据库引擎。SQLite引擎不是一个独立的进程，可按照应用程序需求进行静态或动态连接，SQLite直接访问其存储文件。

原理

SQLite引擎不是个程序与之通信的独立进程，而是连接到程序中成为它的一个主要部分。所以主要的通信协议是在编程语言内的直接API调用。这在消耗总量、延迟时间、整体简单性上具有积极地作用。

整个数据库(定义、表、索引、数据本身)都在宿主主机上存储在一个单一的文件中，它的简单的设计是通过在开始一个事务的时候锁定整个数据文件而完成的。

安装

安装SQLite

进入官网 https://www.sqlite.org/index.html 下载Windows版本

• sqlite-dll-win64-x64-3350300.zip
• sqlite-tools-win32-x86-3350300.zip

解压后将内容移动到 F:\SQLite\bin 文件内，然后将该路径作为SQLITE_HOME环境变量添加到环境变量Path中。

$ sqlite3
SQLite version 3.35.3 2021-03-26 12:12:52
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>

安装可视化的SQLite操作软件

• DB Browser：https://sqlitebrowser.org/
• SQLite Expert Professional ：http://sqliteexpert.com/download.html

点命令

• SQLite点命令不以分号结束

查看帮助

sqlite> .help

创建库

• SQLite的sqlite3命令可用来创建新的SQLite数据库

$ sqlite3 dbname.db

• 数据库名称在RDBMS内应该是唯一的
• 可使用.open命令来建立新的数据库文件

sqlite>.open dbname.db

使用.open dbname.db命令会在当前目录下创建数据库文件dbname.db。

• 查看数据库

sqlite>.databases
main: F:\SQLite\database\test.db r/w

• 退出数据库

sqlite>.quit

导出库

• .dump点命令可用于导出完整的数据库到一个文本文件中

例如：转换test.db数据库内容到SQLite语句中，并将其转储到ASCII文本文件test.sql中。

$ sqlite3 test.db .dump > test.sql

• 从sql文件中恢复数据库

$ sqlite3 test.db < test.sql

命令行方式

• .output导出数据库到SQL脚本
• .dump 导出
• .read 读取文件

备份库

• 备份数据库到文件

$ sqlite3
sqlite>.open test.db
sqlite>.databases
main: F:\SQLite\database\test.db r/w
sqlite>.backup backup.db
sqlite>.quit

• 还原数据库

$ sqlite3 game.db
sqlite>.restore backup.db
sqlite>.tables
sqlite>.quit

查看表

• .tables点命令验证表是否已经创建，可用于列出附加数据库中的所有表。

$ sqlite3 test.db
$ .databases
$ .tables

$ sqlite3
sqlite>.open test.db
sqlite>.databases
sqlite>.tables

• .scheme点命令获取数据表的完整信息

sqlite>.tables
sqlite>.schema company

创建表

• SQLite使用CREATE TABLE语句给特定数据库创建数据表

CREATE TABLE database_name.table_name {
  column1 datatype PRIMARY KEY (one or more columns)
}

例如：从SQL文件中导入

$ sqlite .dump > test.sql
$ vim test.sql

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE company (
    id INT PRIMARY KEY NOT NULL,
    name TEXT NOT NULL,
    pid INT NOT NULL,
    realname CHAR(50),
    salary REAL
);
COMMIT;

$ sqlite3 test.db < test.sql
$ sqlite3 test.db
sqlite>.databases
main: F:\SQLite\database\test.db r/w
sqlite>.tables
company
sqlite>.schema company
CREATE TABLE company (
        id INT PRIMARY KEY NOT NULL,
        name TEXT NOT NULL,
        pid INT NOT NULL,
        realname CHAR(50),
        salary REAL
);

例如：从命令行创建

$ sqlite3 test.db
sqlite>.databases
sqlite>.tables
sqlite> CREATE TABLE users(id INT PRIMARY KEY NOT NULL, name VARCHAR(32) NOT NULL);
sqlite> .schema users
CREATE TABLE users(id INT PRIMARY KEY NOT NULL, name VARCHAR(32) NOT NULL);
sqlite> .tables
users

删除表

$ sqlite3 test.db
sqlite>.tables
company department
sqlite>DROP TABLE company;

数据类型

• SQLite对大小写不敏感

数据类型	类型	描述
BIT	整型	值只能是0、1或空值，用于存储只有两种可能值的数据。
INT	整型	存储-231到231之间的整数，占4个字节。
SMALLINT	整型	存储-215到215之间的整数，占用2个字节。
TINYINT	整型	存储0到255之间的整数，占用1个字节。
NUMERIC	精确数值型	类似于DECIMAL
DECIMAL	精确数值型	存储-1038-1到1038-1固定精度和范围的数值，需指定范围和精度。
MONEY	货币型	存储货币值，精确到货币单位的万分之一。
SMALLMONEY	货币型	存储货币值
FLOAT	近似数值型	供浮点数使用
REAL	近似数值型	浮点数
TEXT	文本字符串	使用数据库编码存储

插入

sqlite> INSERT INTO users(id,name) VALUES(1,"admin"),(2,"manager"),(3,"tester");
sqlite> SELECT * FROM users;
1|admin
2|manager
3|tester

sqlite> .header on
sqlite> SELECT * FROM users;
id|name
1|admin
2|manager
3|tester