SQLite 是一个软件库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎

一、SQLite 数据类型

SQLite 支持 5 种数据类型，如下：

存储类型 描述
NULL 值是一个 NULL 值。
INTEGER 值是一个带符号的整数，根据值的大小存储在 1、2、3、4、6 或 8 字节中。
REAL 值是一个带符号的整数，根据值的大小存储在 1、2、3、4、6 或 8 字节中。
TEXT 值是一个带符号的整数，根据值的大小存储在 1、2、3、4、6 或 8 字节中。
BLOB 值是一个 blob 数据，完全根据它的输入存储。

前几种都是常见的数据类型，BLOB 可以存储自定义数据，比如图像数据、列表、时间对象等等。

二、SQLite 数据表操作

1、创建表

SQLite 创建表的语法为： 

1
2
3
4
5
6
7
CREATE TABLE TABLE_NAME(
    column1 datatype [PRIMARY KEY] [NOT NULL] [DEFAULT] [CHECK] [UNIQUE],
    column2 datatype,
    column3 datatype,
    ...
    columnN datatype
);

其中 column1 - N 为列的名称，例如编号、名字、年龄等等，datatype 为该列数据的数据类型，为 TEXT、INTEGER、REAL、BLOB 中的一种。列名和列数据类型，这两个是必选的，也就是创建一个列必须要有这些信息。

后面跟随的 PRIMARY KEY、NOT NULL、DEFAULT、CHECK、UNIQUE 等都是该列数据上执行的强制规则即约束，是可选的。这些约束用来检查插入到该列的数据，是否是符合规则的，以保证数据的的准确性和可靠性。其含义分别如下：

约束 说明 例子
PRIMARY KEY 主键，用来唯一标示数据库表中的各行记录 编号 INTEGER PRIMARY KEY
NOT NULL 确保该列数据不能有 NULL 值 年龄 INTEGER NOT NULL
DEFAULT 当该列没有制定值时为该列提供默认值 薪水 INTEGER DEFAULT 5000
UNIQUE 确保该列中所有值时不同的 ID INTEGER UNIQUE
CHECK 用来检查该列中的所有值满足某些条件 性别 TEXT CHECK(性别 in (“男”, “女”)

另外如果列的数据类型为 INTEGER 还可以使用 AUTOINCREMENT 关键字，实现该字段值的自动增加。不过 AUTOINCREMENT 关键字除了要求列的数据类型为 INTEGER 外，还要求该列必须是主键即有 PRIMARY KEY 约束，两者必须配合使用。另外在插入数据到数据表的时候不要指定该列，即可实现字段的自动增加，详细请参考 3.1 节。

例如：

1
2
3
4
5
6
7
8
9
CREATE TABLE 员工(
    编号 INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    姓名 TEXT NOT NULL,
    性别 TEXT NOT NULL CHECK(性别 IN ("男","女")),
    年龄 INTEGER NOT NULL,
    薪水 INTEGER NOT NULL DEFAULT 5000,
    工号 INTEGER NOT NULL UNIQUE,
    图像 BLOB
);

2、删除表

删除表的语法为：

1
DROP TABLE TABLE_NAME;

例如：

1
DROP TABLE 员工;

三、SQLite 数据行操作

1、添加数据行

创建了表之后就可以向表中添加数据了，每条数据都成为表中的一行，其中包含了创建表时制定的各列的数据。添加数据行的语法为：

1
2
INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]
VALUES (value1, value2, value3, .... valueN);

添加数据行时可以选择指定要添加的列名称，然后 在VALUE 中的值要与指定的列一一对应。这种情况通常用忽略有默认值、可以为 NULL、以及有 AUTOINCREMENT 关键字的行。如果不指定列的名称那么 VALUE 的值必须与列在表中的顺序一一对应。

例如：

1
2
INSERT INTO 员工 VALUES(1, "张三", "男",  32, 5000, 18001, NULL);
INSERT INTO 员工(姓名, 性别, 年龄, 工号) VALUES("张三", "男", 32, 18001);

以上两个语句是等效的，第一个语句没有指明列名，因此需要按照顺序来排列 VALUE 值。第二个插入语句忽略了编号、薪水、图像，编号具有 AUTOINCREMENT 关键字，如果不指定会自动增加，而薪水有 DEFAULT 约束如果没有指定值的话则使用默认值 5000，图像没有 NOT NULL 约束因为可以为 NULL。

对于 BLOB 类型的数据插入以 Python 为例通过一下方法进行：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
import sqlite3

# Open avatar file and read
with open("avatar.png", "rb") as fp:
    avatar = fp.read()
    
# Open sqlite database
conn = sqlite3.connect("test.db")
cursor = conn.cursor()

# Insert a record with blob data
cursor.execute("INSERT INTO 员工 ('姓名', '性别', '年龄', '工号', '图像')"
               "VALUES ('张三', '男', 32, 18001, ?)", [sqlite3.Binary(avatar)])

# Commit save
conn.commit()
coon.close()

2、更新数据行

添加了数据行之后，如果需要更新数据行则需要使用 UPDATE 语句，语法如下：

1
2
3
UPDATE TABLE_NAME
SET column1 = value1,  column2 = value2 ...., columnN = valueN
WHERE [condition];

更新语句需要指定要更新那些列的数据，以「列名=值」的方式方式进行更新，同时还可以设置更新操作的条件即 WHERE 语句后面跟随的条件，如果不指明条件则会更新表中所有的列数据。

例如：

1
2
UPDATE 员工 SET 薪水=8000;
UPDATE 员工 SET 薪水=8000 WHERE 姓名="张三";

第一条语句没有指定条件，所以表中所有员工的薪水都将统一调整为 8000，第二条语句指定了条件 「姓名=”张三”」所以只有张三的薪水调整为 8000，而其他的员工则不影响。

3、删除数据行

删除数据行的语法如下：

1
2
DELETE FROM TABLE_NAME
WHERE [condition];

注意：和更新语句类似，如果没有指定删除的条件，则会删除所有的数据行即清空数据表。

例如：

1
2
DELETE FROM 员工;
DELETE FROM 员工 WHERE 工号=18001;

第一条语句是删除员工表中所有的数据，即清空员工表。第二条语句则是删除工号为 18001 的员工。

4、选择数据行

选择数据行可能是 SQLite 中最常用的操作，当建好数据表并存储了数据之后，就需要从数据库中筛选数据了，使用 SELECT 语句来筛选数据了，其语法如下：

1
2
3
SELECT column1, column2, ... columnN
FROM TABLE_NAME
WHERE [condition];

SELECT 语句可以从数据表中获取指定的列，并指定相关的条件来筛选数据。例如获取性别为男的所有员工的薪水、获取所有女员工的年龄等等。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 列出所有员工信息
SELECT * FROM 员工;

# 列出所有员工的姓名和年龄信息
SELECT 姓名, 年龄 FROM 员工;

# 列出所有男员工的姓名和薪水
SELECT 姓名, 薪水 FROM 员工 WHERE 性别="男";

# 列出所有姓张的男性员工的姓名、年龄和薪水
SELECT 姓名, 年龄, 薪水 FROM 员工 WHERE 姓名 GLOB "张*";

# 列出所有年龄大于 25 岁的女员工的姓名、年龄和薪水 
SELECT 姓名, 年龄, 薪水 FROM 员工 WHERE 性别="女" AND 年龄 > 25;

四、SQLite 其他杂项操作

1、获取数据库中所有的表

在 SQLite 中可以使用 .tables 指令获取数据库所有表的名称, 也可以使用 SELECT 语句：

1
2
3
4
5
.tables 

or 

SELECT name FROM sqlite_master WHERE type="table" AND name != "sqlite_sequence";

2、获取数据表的 Schema 信息

在 SQLite 中可以使用 .schema 指令获取指定数据表的纲要信息，也可以使用 PRAGMA 语句来获取表的纲要信息，语法如下：

1
2
3
4
5
6

.schema 员工

or

PRAGMA table_info(员工);

使用 .schema 列出表的信息是创建数据表时的 CREATE 语句的原始内容，比较适合人阅读，如果使用程序来解析，PRAGMA 则更为合适。其列出的内容是一个 6 列 N 行的数据表，每一行代表数据表中每一列的属性，其中包含列编号（cid）、列名称（name）、列数据类型（type）、不能为空（notnull）、默认值（dflt_value）、是否是主键值（pk）等。

例如，在 2.1 中创建的「员工」表列出的表信息如下：

cid name type notnull dflt_value pk
0 编号 INTEGER 1 1
1 姓名 TEXT 1 0
2 性别 TEXT 1 0
3 年龄 INTEGER 1 0
4 薪水 INTEGER 1 5000 0
5 工号 INTEGER 1 0
6 头像 BLOB 0 0

五、使用 Python 接口操作 SQLite

Python 可以使用 sqlite3 模块来操作 SQLite, 其集成了一个 DB-API 2.0 规范兼容的 SQL 接口，该模块在 Python 的标准库，可直接调来使用。其常用操作如下：

1、打开/链接数据库、创建 cursor

1
2
3
4
import sqlite3

conn = sqlite3.connect("test.db")
cursor = conn.cursor()

2、执行 SQL 语句获取执行结果

1
2
cursor.execute("SELECT * FROM 员工;")
records = cursor.fetchall()

3、提交数据关闭

1
2
conn.commit()
conn.close()

更多的操作可以参考我的 PyAppFramework 仓库中 SQLiteDatabase 模块。其提供了 SQLite 常用操作的简单封装，如：获取数据库所有表名称、获取数据表信息、获取数据表列名称、获取数据表 PRIMARY KEY、获取表的所有数据、创建表、删除表、插入数据列、更新数据列、删除数据列、刷选数据等操作。