关于sqlite:SQLite简介

SQLite 简介

常见的关系型数据库有 SQLite，MySQL，SQL Server 等，通常学习关系型数据库时不会应用 SQLite，然而 SQLite 是世界上应用最宽泛的数据库引擎。SQLite 内置于所有手机和大多数计算机中，并捆绑在人们每天应用的有数其余应用程序中。SQLite 是一个由 C 语音开发的嵌入式库，具备小型、疾速、自蕴含、高牢靠、功能齐全等特点。

文章以 3.32.2 版本为例讲述 SQLite 的一些基本知识

SQLite 常用命令和示例

在命令行中应用 SQLite 命令，须要先执行 sqlite3 命令，进入SQLite 提示符。

创立或关上数据库

有两种办法能够创立或关上数据库，一个是在 sqlite3 命令前面加上数据库门路，另一个是应用点命令.open

sqlite3 /…/xxx.db 法

通过执行这个命令进入 SQLite 提示符 时，如果数据库文件曾经存在，则间接关上对应数据库，否则不会立刻在对应门路创立 xxx.db 文件。要等到执行了增加数据表，视图等数据库对象的命令之后。

• 示例

  先执行如下命令，此时没有创立出 comms_ease.db 文件

sqlite3 comms_ease.db

​ 再执行如下命令创立一张表，在当前目录呈现 comms_ease.db 文件

.open /…/xxx.db 法

应用 .open 是一个点命令，应用它须要先执行 sqlite3 命令进入 SQLite 提示符。.open 命令的应用形式也是在命令前面追加数据库门路，不过和 sqlite3 /.../xxx.db 法 不同的是，执行 .open 命令后，数据库文件会被间接创立进去，不须要再创立数据库对象。

创立表

SQLite 的创立语句为 CREATE TABLE，残缺的创立表语句内容丰盛，除了创立一般表外，还能具备判断表是否曾经存在，创立长期表等能力。常见的创立一般表的句式为。

CREATE TABLE 表名 (
        列 1 名称 列类型 以空格隔开的一个或多个列束缚, 
        列 2 名称 列类型 以空格隔开的一个或多个列束缚,
        ...
);

默认状况下，一张表的最大列数为 2000，每一行能存下的最大字节数为十亿，能满足绝大多数的需要，创立一般表的示例如下

CREATE TABLE table_comms_ease (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    value VARCHAR
);

例子中，创立了一个名为 table_comms_ease 的数据表，蕴含两列，第一列是id，类型是整形，不能为空，而且是表的主键，并能够主动生成；第二列是value，类型为字符串

在表名曾经存在的状况下，调用 CREATE TABLE 表名 语句会报错，要防止，能够应用 CREATE TABLE IF NOT EXISTS 表名 语句。如果不存在，则创立表，如果存在，则什么都不做。示例如下

CREATE TABLE IF NOT EXISTS table_comms_ease (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    value VARCHAR
);

如果心愿长期存储一些数据，而且只对以后连贯无效，能够试试长期表。长期表的创立语句为CREATE TEMP TABLE。长期表只对以后数据库连贯无效，从新建设连贯或者同时存在的其余连贯都无法访问到。示例如下

CREATE TEMP TABLE temp_table_comms_ease (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    value VARCHAR
);

其它罕用点命令

点命令是 SQLite 数据库独特的命令模式，它们通常比较简单，而且不须要以分号结尾，常见的点命令如下表所示

增

向表内增加一行，即为每一列构建一个值，并填入新的一行中。增加行的命令是 INSERT，增加形式有三种，一种是指定值增加；二是增加 Select 语句的后果；三是默认增加。

1. 指定值增加就是指定局部或者所有列的值，剩下列应用默认值的形式，对于没有通过 DEFAULTE，AUTOINCREMENT 等形式申明默认值的列，如果没有指定 NOT NULL 则填入 NULL，否则报错。 至多须要指定一列的值 ，如需全副填写默认值，能够应用 默认增加 形式。须要留神的是，值的程序要和列的程序保持一致。示例如下：

   INSERT INTO table_comms_ease (value) VALUES ('value of 1');
   INSERT INTO table_comms_ease (value, id) VALUES ('value of 2', 2);

   如果须要指定填入所有列的值，也能够不把列名列举进去，将值按创立表时各个列的程序列举进去即可。示例如下：

   INSERT INTO table_comms_ease VALUES (3, 'value of 3');

2. 通过增加加 Select 语句的后果增加一行的格局为 INSERT INTO 表名 SELECT ... 通过此办法增加一行时，默认值不会被主动填充，SELECT 语句查出的数据量必须和表的列数统一。假如曾经存在表 operator 蕴含列 key 和description而且某一行的 key 为 ’.backup’，description为 备份数据库到文件，给出示例如下：

   INSERT INTO table_comms_ease SELECT  4, description FROM operator WHERE key IS '.backup';

3. 默认增加的格局为 INSERT INTO 表名 DEFAULT VALUES; 为每一列都填入默认值，如果没有特地申明默认值，则填入 NULL。示例如下：

   INSERT INTO table_comms_ease DEFAULT VALUES;

通过创立表章节的示例语句创立出数据表，再顺次调用上述示例语句，则数据表内会呈现 5 行数据，如下

删

删除表内的一行，命令是 DELETE，罕用格局为

DELETE FROM 表名 WHERE 过滤语句

删除命令自身比较简单，指定表名和删除条件即可删除一列，如下示例示意如果 value 列中的值有 value of 结尾，则删除。

DELETE FROM table_comms_ease WHERE value LIKE 'value of %';

如上命令操作后，table_comms_ease表还剩的数据为

改

批改表内数据的命令为 UPDATE，其罕用格局为

UPDATE 表名 SET 一个或多个列的赋值 WHERE 过滤语句

批改多列内容时能够采纳先写出列名，再按程序赋值的形式，也能够采纳一列一列批改的形式。如果要更新 id 为 4 的所在行的值，设置 id 为123，value为new value，两种批改形式别离如下

UPDATE table_comms_ease SET (id, value)= (123, 'new value') WHERE id = 4;

或者

UPDATE table_comms_ease SET id=123, value='new value' WHERE id = 4;

批改后，table_comms_ease表中的数据为

批改单列的办法和批改多列的办法类似，比方将 id 为 5 这一行的 value 也批改为new value，能够如下操作

UPDATE table_comms_ease SET (value)=('new value') WHERE id = 5;

或者

UPDATE table_comms_ease SET value='new value' WHERE id = 5;

批改后，table_comms_ease表中的数据为

查

查问语句的命令是 SELECT，它不会批改数据库，后果的行数在自然数范畴内，每一行代表一个查问后果。SELECT命令的罕用格局为

SELECT 去重策略 列名列表 FROM 表名或者子查问语句 WHERE 过滤语句 ORDER BY 排序策略 LIMIT 数量限度

查问语句中可用的配置比拟多，然而大都不是必须的。查问 table_comms_ease 表的所有内容只须要如下命令即可

SELECT * FROM table_comms_ease;

下面命令中的 * 示意所有列，命令相当于

SELECT id,value FROM table_comms_ease;

后果为

另外，能够通过 VALUES 语句构建一个查问后果，后果的列名为 column1, column2, column3 等等。比方

VALUES (1,2,3),('a','b','c');

的后果为

去重策略

去重策略有两种，一种是默认策略 ALL，代表不去重；另一种是DISTINCT，代表去重。table_comms_ease 表中 value 列的值雷同，应用 ALL 和DISTINCT别离查问 value 列时，命令和后果如下：

ALL 命令

SELECT ALL * FROM table_comms_ease;

后果为

DISTINCT 命令

SELECT DISTINCT value FROM table_comms_ease;

后果为

能够看出在有反复后果时，ALL 策略会保留所有后果，而 DISTINCT 策略只保留其中一个

表名或者子查问语句

查问语句的 FROM 关键字前面能够跟表名或者子查问语句，用于限度查问范畴。当填写表名时，能够填写多个表名，用逗号或者连贯运算符分隔。当填写查问语句时，能够视为先查问出一张表，再从此表中查问出数据。

假如还有一张表 table_comms_ease_1，列信息和table_comms_ease 表雷同，值为

则此字段填写 table_comms_ease,table_comms_ease_1 时失去如下命令

SELECT * FROM table_comms_ease, table_comms_ease_1;

后果为

当此字段填写两个子查问语句，如一个是 id 为 5，另一个是 id 为 123 时，则失去如下命令

SELECT * FROM (SELECT * FROM table_comms_ease WHERE iD=5), (SELECT * FROM table_comms_ease WHERE iD=123)；

后果为

将子查问语句的后果视为一张表，则能够对立对两种填写格局的了解。另外查问命令也反对混合填写表名和查问语句。

排序条件

排序条件决定了后果的排列程序，罕用格局如下

ORDER BY 列名 COLLATE 比拟形式 排序形式 NULL 值的排序形式

比拟形式有三种，别离为 BINARY, NOCASE 和 RTRIM

• BINARY：应用规范 C 库中的 memcmp()函数逐字节比拟
• NOCASE：先把 ASC II 码中的大写字母转为小写字母，再依照 BINARY 形式比拟
• RTRIM：去掉开端空格后依照 BINARY 形式比拟

通过上面命令为表 table_comms_ease 增加几条数据，

INSERT INTO table_comms_ease VALUES (6, 'A'), (7, 'new value'), (8, 'Z');

则表中的数据变为

留神：id 为 7 的一行对应的 value 的开端有一个空格

如下示例展现了三种不同比拟形式的区别

BINARY 命令

SELECT * FROM table_comms_ease ORDER BY table_comms_ease.value COLLATE BINARY;

后果：

NOCASE 命令

SELECT * FROM table_comms_ease ORDER BY table_comms_ease.value COLLATE NOCASE;

后果：

RTRIM 命令

SELECT * FROM table_comms_ease ORDER BY table_comms_ease.value COLLATE RTRIM;

后果：

数量限度

数量限度语句能够限度查问后果的行数，罕用格局如下

LIMIT 数量 OFFET 偏移量

设数量为 n，偏移量为 o, 则下面格局的意义是从第 o+1 条开始，取最多 n 条数据，如果没有符合条件的数据，则后果为空。

限度数量为 3，失去如下表达式

SELECT * FROM table_comms_ease LIMIT 3;

后果为

因为表的总行数是 5，所以如果限度数量≥5，则会查出整张表。

如果限度数量为 3，同时指定偏移量为 1，失去如下表达式

SELECT * FROM table_comms_ease LIMIT 3 OFFSET 1;

后果过滤掉第一条数据(5, newvalue)，并向后取 3 条，失去

如果限度数量为 3，同时指定偏移量为 3，失去如下表达式

SELECT * FROM table_comms_ease LIMIT 3 OFFSET 3;

后果过滤掉前三条数据，并向后取 3 条，然而前面只有 2 条，所以失去

如果偏移量≥5，则什么都查不到

SQLite 常见限度

数据库中的 B 树

B 树与 B + 树简介

B 树是一种均衡多路查找树，每个结点蕴含三个局部：键，值，指向子结点的指针。假如一个 B 树结点中有 n 个键，则它同时有 n 个值。如果这是一个叶子结点，则它没有指向子结点的指针，否则有 n+1 个指向子结点的指针。下图为 n==2 时的结点状况。

图中 键 1 和 键 2 两个值须要满足 键 1 < 键 2 。值 1 和 值 2 别离与 键 1 和 键 2 对应；三个 指向子结点的指针 ，别离指向具备不同范畴的 键的子结点。子结点 1 中的 键都小于 键 1 ；子结点 2 中的 键都大于 键 1 ，且小于 键 2 ；子结点 3 中的 键都大于 键 2 。如下图提供了一个 3 路 B 树的示例。

上图中每个结点有三排，第一排是 键；第二排是 值；第三排是 指向子结点的指针 。根节点有50 和100两个 键，因而它的左子树中结点的 键都小于 50；中子树中结点的 键都大于 50 且小于 100；右子树中结点的 键都大于100。

B+ 树和 B 树相似，然而 B + 树的外部结点中只有 键和 指向子结点的指针 ，而叶子结点具备 键、值 和指向下一组值的指针 ，即只在叶子结点上存储数据。因而父结点中的键还会再呈现在子结点上。而且 B + 树的叶子结点的 指向下一组值的指针 ，将所有 值都串成了一个链表。因为外部结点不须要存储 值，B+ 树能够存储更多的 键。下图用 5 路 B + 树存储了下面 3 路 B 树的内容。

B 树页

数据库文件由一页或多页组成。同一个数据库中，每页的大小雷同，都是 在 512 和 65536 之间，并且为 2 的整数次幂。数据库的页分为锁定字节 (lock-byte) 页、freelist 页、B 树页、负载溢出页和指针映射页。

B 树算法为 SQLite 提供了键值存储模式，而且保障了键的有序性和唯一性。SQLite 数据库中 B 树的结点就是一个页面，所以指向的子结点的指针实际上是对应页面的页码。SQLite 数据库应用了两种 B 树变体，在叶子节点存储数据的 表 B 树 和不存储数据的 索引 B 树 。一颗残缺的树只能是齐全的 表 B 树 或者齐全的 索引 B 树。

表 B 树和索引 B 树

表 B 树相似 B + 树，只将值寄存在叶子结点中；索引 B 树是一颗没有值的树，键就是数据自身，因而索引 B 树其实相似于 B 树。下表列出了它们之间的一些差别。