SQLite PRAGMA
SQLite 的 PRAGMA 命令是一个特殊的命令，可以用在 SQLite 环境内控制各种环境变量和状态标志。一个 PRAGMA 值可以被读取，也可以根据需求进行设置。
语法
要查询当前的 PRAGMA 值，只需要提供该 pragma 的名字：
PRAGMA pragma_name;
要为 PRAGMA 设置一个新的值，语法如下：
PRAGMA pragma_name = value;
设置模式，可以是名称或等值的整数，但返回的值将始终是一个整数。
auto_vacuum Pragma
auto_vacuum Pragma 获取或设置 auto-vacuum 模式。语法如下：
PRAGMA [database.]auto_vacuum; PRAGMA [database.]auto_vacuum = mode;
其中，mode 可以是以下任何一种：
| Pragma 值 | 描述 |
|---|---|
| 0 或 NONE | 禁用 Auto-vacuum。这是默认模式，意味着数据库文件尺寸大小不会缩小，除非手动使用 VACUUM 命令。 |
| 1 或 FULL | 启用 Auto-vacuum，是全自动的。在该模式下，允许数据库文件随着数据从数据库移除而缩小。 |
| 2 或 INCREMENTAL | 启用 Auto-vacuum，但是必须手动激活。在该模式下，引用数据被维持，免费页面只放在免费列表中。这些页面可在任何时候使用 incremental_vacuum pragma 进行覆盖。 |
cache_size Pragma
cache_size Pragma 可获取或暂时设置在内存中页面缓存的最大尺寸。语法如下：
PRAGMA [database.]cache_size; PRAGMA [database.]cache_size = pages;
pages 值表示在缓存中的页面数。内置页面缓存的默认大小为 2,000 页，最小尺寸为 10 页。
case_sensitive_like Pragma
case_sensitive_like Pragma 控制内置的 LIKE 表达式的大小写敏感度。默认情况下，该 Pragma 为 false，这意味着，内置的 LIKE 操作符忽略字母的大小写。语法如下：
PRAGMA case_sensitive_like = [true|false];
目前没有办法查询该 Pragma 的当前状态。
count_changes Pragma
count_changes Pragma 获取或设置数据操作语句的返回值，如 INSERT、UPDATE 和 DELETE。语法如下：
PRAGMA count_changes; PRAGMA count_changes = [true|false];
默认情况下，该 Pragma 为 false，这些语句不返回任何东西。如果设置为 true，每个所提到的语句将返回一个单行单列的表，由一个单一的整数值组成，该整数表示操作影响的行。
database_list Pragma
database_list Pragma 将用于列出了所有的数据库连接。语法如下：
PRAGMA database_list;
该 Pragma 将返回一个单行三列的表格，每当打开或附加数据库时，会给出数据库中的序列号，它的名称和相关的文件。
encoding Pragma
encoding Pragma 控制字符串如何编码及存储在数据库文件中。语法如下：
PRAGMA encoding; PRAGMA encoding = format;
格式值可以是 UTF-8、UTF-16le 或 UTF-16be 之一。
freelist_count Pragma
freelist_count Pragma 返回一个整数，表示当前被标记为免费和可用的数据库页数。语法如下：
PRAGMA [database.]freelist_count;
格式值可以是 UTF-8、UTF-16le 或 UTF-16be 之一。
index_info Pragma
index_info Pragma 返回关于数据库索引的信息。语法如下：
PRAGMA [database.]index_info( index_name );
结果集将为每个包含在给出列序列的索引、表格内的列索引、列名称的列显示一行。
index_list Pragma
index_list Pragma 列出所有与表相关联的索引。语法如下：
PRAGMA [database.]index_list( table_name );
结果集将为每个给出列序列的索引、索引名称、表示索引是否唯一的标识显示一行。
journal_mode Pragma
journal_mode Pragma 获取或设置控制日志文件如何存储和处理的日志模式。语法如下：:
PRAGMA journal_mode; PRAGMA journal_mode = mode; PRAGMA database.journal_mode; PRAGMA database.journal_mode = mode;
这里支持五种日志模式：
| Pragma 值 | 描述 |
|---|---|
| DELETE | 默认模式。在该模式下，在事务结束时，日志文件将被删除。 |
| TRUNCATE | 日志文件被阶段为零字节长度。 |
| PERSIST | 日志文件被留在原地，但头部被重写，表明日志不再有效。 |
| MEMORY | 日志记录保留在内存中，而不是磁盘上。 |
| OFF | 不保留任何日志记录。 |
max_page_count Pragma
max_page_count Pragma 为数据库获取或设置允许的最大页数。语法如下：
PRAGMA [database.]max_page_count; PRAGMA [database.]max_page_count = max_page;
默认值是 1,073,741,823，这是一个千兆的页面，即如果默认 1 KB 的页面大小，那么数据库中增长起来的一个兆字节。
page_count Pragma
page_count Pragma 返回当前数据库中的网页数量。语法如下：
PRAGMA [database.]page_count;
数据库文件的大小应该是 page_count * page_size。
page_size Pragma
page_size Pragma 获取或设置数据库页面的大小。语法如下：
PRAGMA [database.]page_size; PRAGMA [database.]page_size = bytes;
默认情况下，允许的尺寸是 512、1024、2048、4096、8192、16384、32768 字节。改变现有数据库页面大小的唯一方法就是设置页面大小，然后立即 VACUUM 该数据库。
parser_trace Pragma
parser_trace Pragma 随着它解析 SQL 命令来控制打印的调试状态，语法如下：
PRAGMA parser_trace = [true|false];
默认情况下，它被设置为 false，但设置为 true 时则启用，此时 SQL 解析器会随着它解析 SQL 命令来打印出它的状态。
recursive_triggers Pragma
recursive_triggers Pragma 获取或设置递归触发器功能。如果未启用递归触发器，一个触发动作将不会触发另一个触发。语法如下：
PRAGMA recursive_triggers; PRAGMA recursive_triggers = [true|false];
schema_version Pragma
schema_version Pragma 获取或设置存储在数据库头中的的架构版本值。语法如下：
PRAGMA [database.]schema_version; PRAGMA [database.]schema_version = number;
这是一个 32 位有符号整数值，用来跟踪架构的变化。每当一个架构改变命令执行（比如 CREATE... 或 DROP...）时，这个值会递增。
secure_delete Pragma
secure_delete Pragma 用来控制内容是如何从数据库中删除。语法如下：
PRAGMA secure_delete; PRAGMA secure_delete = [true|false]; PRAGMA database.secure_delete; PRAGMA database.secure_delete = [true|false];
安全删除标志的默认值通常是关闭的，但是这是可以通过 SQLITE_SECURE_DELETE 构建选项来改变的。
sql_trace Pragma
sql_trace Pragma 用于把 SQL 跟踪结果转储到屏幕上。语法如下：
PRAGMA sql_trace; PRAGMA sql_trace = [true|false];
SQLite 必须通过 SQLITE_DEBUG 指令来编译要引用的该 Pragma。
synchronous Pragma
synchronous Pragma 获取或设置当前磁盘的同步模式，该模式控制积极的 SQLite 如何将数据写入物理存储。语法如下：
PRAGMA [database.]synchronous; PRAGMA [database.]synchronous = mode;
SQLite 支持下列同步模式：
| Pragma 值 | 描述 |
|---|---|
| 0 或 OFF | 不进行同步。 |
| 1 或 NORMAL | 在关键的磁盘操作的每个序列后同步。 |
| 2 或 FULL | 在每个关键的磁盘操作后同步。 |
temp_store Pragma
temp_store Pragma 获取或设置临时数据库文件所使用的存储模式。语法如下：
PRAGMA temp_store; PRAGMA temp_store = mode;
SQLite 支持下列存储模式：
| Pragma 值 | 描述 |
|---|---|
| 0 或 DEFAULT | 默认使用编译时的模式。通常是 FILE。 |
| 1 或 FILE | 使用基于文件的存储。 |
| 2 或 MEMORY | 使用基于内存的存储。 |
temp_store_directory Pragma
temp_store_directory Pragma 获取或设置用于临时数据库文件的位置。语法如下：
PRAGMA temp_store_directory; PRAGMA temp_store_directory = 'directory_path';
user_version Pragma
user_version Pragma 获取或设置存储在数据库头的用户自定义的版本值。语法如下：
PRAGMA [database.]user_version; PRAGMA [database.]user_version = number;
这是一个 32 位的有符号整数值，可以由开发人员设置，用于版本跟踪的目的。
writable_schema Pragma
writable_schema Pragma 获取或设置是否能够修改系统表。语法如下：
PRAGMA writable_schema; PRAGMA writable_schema = [true|false];
如果设置了该 Pragma，则表以 sqlite_ 开始，可以创建和修改，包括 sqlite_master 表。使用该 Pragma 时要注意，因为它可能导致整个数据库损坏。
SQLite 约束
约束是在表的数据列上强制执行的规则。这些是用来限制可以插入到表中的数据类型。这确保了数据库中数据的准确性和可靠性。
约束可以是列级或表级。列级约束仅适用于列，表级约束被应用到整个表。
以下是在 SQLite 中常用的约束。
-
NOT NULL 约束：确保某列不能有 NULL 值。
-
DEFAULT 约束：当某列没有指定值时，为该列提供默认值。
-
UNIQUE 约束：确保某列中的所有值是不同的。
-
PRIMARY Key 约束：唯一标识数据库表中的各行/记录。
-
CHECK 约束：CHECK 约束确保某列中的所有值满足一定条件。
NOT NULL 约束
默认情况下，列可以保存 NULL 值。如果您不想某列有 NULL 值，那么需要在该列上定义此约束，指定在该列上不允许 NULL 值。
NULL 与没有数据是不一样的，它代表着未知的数据。
实例
例如，下面的 SQLite 语句创建一个新的表 COMPANY，并增加了五列，其中 ID、NAME 和 AGE 三列指定不接受 NULL 值：
CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
DEFAULT 约束
DEFAULT 约束在 INSERT INTO 语句没有提供一个特定的值时，为列提供一个默认值。
实例
例如，下面的 SQLite 语句创建一个新的表 COMPANY，并增加了五列。在这里，SALARY 列默认设置为 5000.00。所以当 INSERT INTO 语句没有为该列提供值时，该列将被设置为 5000.00。
CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL DEFAULT 50000.00 );
UNIQUE 约束
UNIQUE 约束防止在一个特定的列存在两个记录具有相同的值。在 COMPANY 表中，例如，您可能要防止两个或两个以上的人具有相同的年龄。
实例
例如，下面的 SQLite 语句创建一个新的表 COMPANY，并增加了五列。在这里，AGE 列设置为 UNIQUE，所以不能有两个相同年龄的记录：
CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL UNIQUE, ADDRESS CHAR(50), SALARY REAL DEFAULT 50000.00 );
PRIMARY KEY 约束
PRIMARY KEY 约束唯一标识数据库表中的每个记录。在一个表中可以有多个 UNIQUE 列，但只能有一个主键。在设计数据库表时，主键是很重要的。主键是唯一的 ID。
我们使用主键来引用表中的行。可通过把主键设置为其他表的外键，来创建表之间的关系。由于"长期存在编码监督"，在 SQLite 中，主键可以是 NULL，这是与其他数据库不同的地方。
主键是表中的一个字段，唯一标识数据库表中的各行/记录。主键必须包含唯一值。主键列不能有 NULL 值。
一个表只能有一个主键，它可以由一个或多个字段组成。当多个字段作为主键，它们被称为复合键。
如果一个表在任何字段上定义了一个主键，那么在这些字段上不能有两个记录具有相同的值。
实例
已经看到了我们创建以 ID 作为主键的 COMAPNY 表的各种实例：
CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
CHECK 约束
CHECK 约束启用输入一条记录要检查值的条件。如果条件值为 false，则记录违反了约束，且不能输入到表。
实例
例如，下面的 SQLite 创建一个新的表 COMPANY，并增加了五列。在这里，我们为 SALARY 列添加 CHECK，所以工资不能为零：
CREATE TABLE COMPANY3( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL CHECK(SALARY > 0) );
删除约束
SQLite 支持 ALTER TABLE 的有限子集。在 SQLite 中，ALTER TABLE 命令允许用户重命名表，或向现有表添加一个新的列。重命名列，删除一列，或从一个表中添加或删除约束都是不可能的。
SQLite Joins
SQLite 的 Joins 子句用于结合两个或多个数据库中表的记录。JOIN 是一种通过共同值来结合两个表中字段的手段。
SQL 定义了三种主要类型的连接：
-
交叉连接 - CROSS JOIN
-
内连接 - INNER JOIN
-
外连接 - OUTER JOIN
在我们继续之前，让我们假设有两个表 COMPANY 和 DEPARTMENT。我们已经看到了用来填充 COMPANY 表的 INSERT 语句。现在让我们假设 COMPANY 表的记录列表如下：
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
另一个表是 DEPARTMENT，定义如下：
CREATE TABLE DEPARTMENT( ID INT PRIMARY KEY NOT NULL, DEPT CHAR(50) NOT NULL, EMP_ID INT NOT NULL );
下面是填充 DEPARTMENT 表的 INSERT 语句：
INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (1, 'IT Billing', 1 ); INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (2, 'Engineering', 2 ); INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (3, 'Finance', 7 );
最后，我们在 DEPARTMENT 表中有下列的记录列表：
ID DEPT EMP_ID ---------- ---------- ---------- 1 IT Billing 1 2 Engineerin 2 3 Finance 7
交叉连接 - CROSS JOIN
交叉连接（CROSS JOIN）把第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有 x 和 y 列，则结果表有 x+y 列。由于交叉连接（CROSS JOIN）有可能产生非常大的表，使用时必须谨慎，只在适当的时候使用它们。
下面是交叉连接（CROSS JOIN）的语法：
SELECT ... FROM table1 CROSS JOIN table2 ...
基于上面的表，我们可以写一个交叉连接（CROSS JOIN），如下所示：
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;
上面的查询会产生以下结果：
EMP_ID NAME DEPT ---------- ---------- ---------- 1 Paul IT Billing 2 Paul Engineerin 7 Paul Finance 1 Allen IT Billing 2 Allen Engineerin 7 Allen Finance 1 Teddy IT Billing 2 Teddy Engineerin 7 Teddy Finance 1 Mark IT Billing 2 Mark Engineerin 7 Mark Finance 1 David IT Billing 2 David Engineerin 7 David Finance 1 Kim IT Billing 2 Kim Engineerin 7 Kim Finance 1 James IT Billing 2 James Engineerin 7 James Finance
内连接 - INNER JOIN
内连接（INNER JOIN）根据连接谓词结合两个表（table1 和 table2）的列值来创建一个新的结果表。查询会把 table1 中的每一行与 table2 中的每一行进行比较，找到所有满足连接谓词的行的匹配对。当满足连接谓词时，A 和 B 行的每个匹配对的列值会合并成一个结果行。
内连接（INNER JOIN）是最常见的连接类型，是默认的连接类型。INNER 关键字是可选的。
下面是内连接（INNER JOIN）的语法：
SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...
为了避免冗余，并保持较短的措辞，可以使用 USING 表达式声明内连接（INNER JOIN）条件。这个表达式指定一个或多个列的列表：
SELECT ... FROM table1 JOIN table2 USING ( column1 ,... ) ...
自然连接（NATURAL JOIN）类似于 JOIN...USING，只是它会自动测试存在两个表中的每一列的值之间相等值：
SELECT ... FROM table1 NATURAL JOIN table2...
基于上面的表，我们可以写一个内连接（INNER JOIN），如下所示：
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;
上面的查询会产生以下结果：
EMP_ID NAME DEPT ---------- ---------- ---------- 1 Paul IT Billing 2 Allen Engineerin 7 James Finance
外连接 - OUTER JOIN
外连接（OUTER JOIN）是内连接（INNER JOIN）的扩展。虽然 SQL 标准定义了三种类型的外连接：LEFT、RIGHT、FULL，但 SQLite 只支持 左外连接（LEFT OUTER JOIN）。
外连接（OUTER JOIN）声明条件的方法与内连接（INNER JOIN）是相同的，使用 ON、USING 或 NATURAL 关键字来表达。最初的结果表以相同的方式进行计算。一旦主连接计算完成，外连接（OUTER JOIN）将从一个或两个表中任何未连接的行合并进来，外连接的列使用 NULL 值，将它们附加到结果表中。
下面是左外连接（LEFT OUTER JOIN）的语法：
SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...
为了避免冗余，并保持较短的措辞，可以使用 USING 表达式声明外连接（OUTER JOIN）条件。这个表达式指定一个或多个列的列表：
SELECT ... FROM table1 LEFT OUTER JOIN table2 USING ( column1 ,... ) ...
基于上面的表，我们可以写一个外连接（OUTER JOIN），如下所示：
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;
上面的查询会产生以下结果：
EMP_ID NAME DEPT
---------- ---------- ----------
1 Paul IT Billing
2 Allen Engineerin
Teddy
Mark
David
Kim
7 James Finance
SQLite Unions 子句
SQLite的 UNION 子句/运算符用于合并两个或多个 SELECT 语句的结果，不返回任何重复的行。
为了使用 UNION，每个 SELECT 被选择的列数必须是相同的，相同数目的列表达式，相同的数据类型，并确保它们有相同的顺序，但它们不必具有相同的长度。
语法
UNION 的基本语法如下：
SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] UNION SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]
这里给定的条件根据需要可以是任何表达式。
实例
假设有下面两个表，（1）COMPANY 表如下所示：
sqlite> select * from COMPANY; ID NAME AGE ADDRESS SALARY ---------- -------------------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
（2）另一个表是 DEPARTMENT，如下所示：
ID DEPT EMP_ID ---------- -------------------- ---------- 1 IT Billing 1 2 Engineering 2 3 Finance 7 4 Engineering 3 5 Finance 4 6 Engineering 5 7 Finance 6
现在，让我们使用 SELECT 语句及 UNION 子句来连接两个表，如下所示：
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID
UNION
SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;
这将产生以下结果：
EMP_ID NAME DEPT ---------- -------------------- ---------- 1 Paul IT Billing 2 Allen Engineerin 3 Teddy Engineerin 4 Mark Finance 5 David Engineerin 6 Kim Finance 7 James Finance
UNION ALL 子句
UNION ALL 运算符用于结合两个 SELECT 语句的结果，包括重复行。
适用于 UNION 的规则同样适用于 UNION ALL 运算符。
语法
UNION ALL 的基本语法如下：
SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] UNION ALL SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]
这里给定的条件根据需要可以是任何表达式。
实例
现在，让我们使用 SELECT 语句及 UNION ALL 子句来连接两个表，如下所示：
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID
UNION ALL
SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;
这将产生以下结果：
EMP_ID NAME DEPT ---------- -------------------- ---------- 1 Paul IT Billing 2 Allen Engineerin 3 Teddy Engineerin 4 Mark Finance 5 David Engineerin 6 Kim Finance 7 James Finance 1 Paul IT Billing 2 Allen Engineerin 3 Teddy Engineerin 4 Mark Finance 5 David Engineerin 6 Kim Finance 7 James Finance
SQLite NULL 值
SQLite 的 NULL 是用来表示一个缺失值的项。表中的一个 NULL 值是在字段中显示为空白的一个值。
带有 NULL 值的字段是一个不带有值的字段。NULL 值与零值或包含空格的字段是不同的，理解这点是非常重要的。
语法
创建表时使用 NULL 的基本语法如下：
SQLite> CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
在这里，NOT NULL 表示列总是接受给定数据类型的显式值。这里有两个列我们没有使用 NOT NULL，这意味着这两个列不能为 NULL。
带有 NULL 值的字段在记录创建的时候可以保留为空。
实例
NULL 值在选择数据时会引起问题，因为当把一个未知的值与另一个值进行比较时，结果总是未知的，且不会包含在最后的结果中。假设有下面的表，COMPANY 的记录如下所示：
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
让我们使用 UPDATE 语句来设置一些允许空值的值为 NULL，如下所示：
sqlite> UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN(6,7);
现在，COMPANY 表的记录如下所示：
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 7 James 24
接下来，让我们看看 IS NOT NULL 运算符的用法，它用来列出所有 SALARY 不为 NULL 的记录：
sqlite> SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM COMPANY
WHERE SALARY IS NOT NULL;
上面的 SQLite 语句将产生下面的结果：
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
下面是 IS NULL 运算符的用法，将列出所有 SALARY 为 NULL 的记录：
sqlite> SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM COMPANY
WHERE SALARY IS NULL;
上面的 SQLite 语句将产生下面的结果：
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 6 Kim 22 7 James 24
SQLite 别名
您可以暂时把表或列重命名为另一个名字，这被称为别名。使用表别名是指在一个特定的 SQLite 语句中重命名表。重命名是临时的改变，在数据库中实际的表的名称不会改变。
列别名用来为某个特定的 SQLite 语句重命名表中的列。
语法
表 别名的基本语法如下：
SELECT column1, column2.... FROM table_name AS alias_name WHERE [condition];
列 别名的基本语法如下：
SELECT column_name AS alias_name FROM table_name WHERE [condition];
实例
假设有下面两个表，（1）COMPANY 表如下所示：
sqlite> select * from COMPANY; ID NAME AGE ADDRESS SALARY ---------- -------------------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
（2）另一个表是 DEPARTMENT，如下所示：
ID DEPT EMP_ID ---------- -------------------- ---------- 1 IT Billing 1 2 Engineering 2 3 Finance 7 4 Engineering 3 5 Finance 4 6 Engineering 5 7 Finance 6
现在，下面是 表别名 的用法，在这里我们使用 C 和 D 分别作为 COMPANY 和 DEPARTMENT 表的别名：
sqlite> SELECT C.ID, C.NAME, C.AGE, D.DEPT
FROM COMPANY AS C, DEPARTMENT AS D
WHERE C.ID = D.EMP_ID;
上面的 SQLite 语句将产生下面的结果：
ID NAME AGE DEPT ---------- ---------- ---------- ---------- 1 Paul 32 IT Billing 2 Allen 25 Engineerin 3 Teddy 23 Engineerin 4 Mark 25 Finance 5 David 27 Engineerin 6 Kim 22 Finance 7 James 24 Finance
让我们看一个 列别名 的实例，在这里 COMPANY_ID 是 ID 列的别名，COMPANY_NAME 是 name 列的别名：
sqlite> SELECT C.ID AS COMPANY_ID, C.NAME AS COMPANY_NAME, C.AGE, D.DEPT
FROM COMPANY AS C, DEPARTMENT AS D
WHERE C.ID = D.EMP_ID;
上面的 SQLite 语句将产生下面的结果：
COMPANY_ID COMPANY_NAME AGE DEPT ---------- ------------ ---------- ---------- 1 Paul 32 IT Billing 2 Allen 25 Engineerin 3 Teddy 23 Engineerin 4 Mark 25 Finance 5 David 27 Engineerin 6 Kim 22 Finance 7 James 24 Finance
SQLite 触发器（Trigger）
SQLite 的触发器是数据库的回调函数，它会自动执行/指定的数据库事件发生时调用。以下是关于SQLite的触发器的要点： SQLite 触发器（Trigger）是数据库的回调函数，它会在指定的数据库事件发生时自动执行/调用。以下是关于 SQLite 的触发器（Trigger）的要点：
-
SQLite 的触发器（Trigger）可以指定在特定的数据库表发生 DELETE、INSERT 或 UPDATE 时触发，或在一个或多个指定表的列发生更新时触发。
-
SQLite 只支持 FOR EACH ROW 触发器（Trigger），没有 FOR EACH STATEMENT 触发器（Trigger）。因此，明确指定 FOR EACH ROW 是可选的。
-
WHEN 子句和触发器（Trigger）动作可能访问使用表单 NEW.column-name 和 OLD.column-name 的引用插入、删除或更新的行元素，其中 column-name 是从与触发器关联的表的列的名称。
-
如果提供 WHEN 子句，则只针对 WHEN 子句为真的指定行执行 SQL 语句。如果没有提供 WHEN 子句，则针对所有行执行 SQL 语句。
-
BEFORE 或 AFTER 关键字决定何时执行触发器动作，决定是在关联行的插入、修改或删除之前或者之后执行触发器动作。
-
当触发器相关联的表删除时，自动删除触发器（Trigger）。
-
要修改的表必须存在于同一数据库中，作为触发器被附加的表或视图，且必须只使用 tablename，而不是 database.tablename。
-
一个特殊的 SQL 函数 RAISE() 可用于触发器程序内抛出异常。
语法
创建 触发器（Trigger） 的基本语法如下：
CREATE TRIGGER trigger_name [BEFORE|AFTER] event_name ON table_name BEGIN -- Trigger logic goes here.... END;
在这里，event_name 可以是在所提到的表 table_name 上的 INSERT、DELETE 和 UPDATE 数据库操作。您可以在表名后选择指定 FOR EACH ROW。
以下是在 UPDATE 操作上在表的一个或多个指定列上创建触发器（Trigger）的语法：
CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name ON table_name BEGIN -- Trigger logic goes here.... END;
实例
让我们假设一个情况，我们要为被插入到新创建的 COMPANY 表（如果已经存在，则删除重新创建）中的每一个记录保持审计试验：
sqlite> CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
为了保持审计试验，我们将创建一个名为 AUDIT 的新表。每当 COMPANY 表中有一个新的记录项时，日志消息将被插入其中：
sqlite> CREATE TABLE AUDIT(
EMP_ID INT NOT NULL,
ENTRY_DATE TEXT NOT NULL
);
在这里，ID 是 AUDIT 记录的 ID，EMP_ID 是来自 COMPANY 表的 ID，DATE 将保持 COMPANY 中记录被创建时的时间戳。所以，现在让我们在 COMPANY 表上创建一个触发器，如下所示：
sqlite> CREATE TRIGGER audit_log AFTER INSERT
ON COMPANY
BEGIN
INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));
END;
现在，我们将开始在 COMPANY 表中插入记录，这将导致在 AUDIT 表中创建一个审计日志记录。因此，让我们在 COMPANY 表中创建一个记录，如下所示：
sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 );
这将在 COMPANY 表中创建如下一个记录：
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0
同时，将在 AUDIT 表中创建一个记录。这个纪录是触发器的结果，这是我们在 COMPANY 表上的 INSERT 操作上创建的触发器（Trigger）。类似的，可以根据需要在 UPDATE 和 DELETE 操作上创建触发器（Trigger）。
EMP_ID ENTRY_DATE ---------- ------------------- 1 2013-04-05 06:26:00
列出触发器（TRIGGERS）
您可以从 sqlite_master 表中列出所有触发器，如下所示：
sqlite> SELECT name FROM sqlite_master WHERE type = 'trigger';
上面的 SQLite 语句只会列出一个条目，如下：
name ---------- audit_log
如果您想要列出特定表上的触发器，则使用 AND 子句连接表名，如下所示：
sqlite> SELECT name FROM sqlite_master WHERE type = 'trigger' AND tbl_name = 'COMPANY';
上面的 SQLite 语句只会列出一个条目，如下：
name ---------- audit_log
删除触发器（TRIGGERS）
下面是 DROP 命令，可用于删除已有的触发器：
sqlite> DROP TRIGGER trigger_name;
SQLite 索引（Index）
索引（Index）是一种特殊的查找表，数据库搜索引擎用来加快数据检索。简单地说，索引是一个指向表中数据的指针。一个数据库中的索引与一本书后边的索引是非常相似的。
例如，如果您想在一本讨论某个话题的书中引用所有页面，您首先需要指向索引，索引按字母顺序列出了所有主题，然后指向一个或多个特定的页码。
索引有助于加快 SELECT 查询和 WHERE 子句，但它会减慢使用 UPDATE 和 INSERT 语句时的数据输入。索引可以创建或删除，但不会影响数据。
使用 CREATE INDEX 语句创建索引，它允许命名索引，指定表及要索引的一列或多列，并指示索引是升序排列还是降序排列。
索引也可以是唯一的，与 UNIQUE 约束类似，在列上或列组合上防止重复条目。
CREATE INDEX 命令
CREATE INDEX 的基本语法如下：
CREATE INDEX index_name ON table_name;
单列索引
单列索引是一个只基于表的一个列上创建的索引。基本语法如下：
CREATE INDEX index_name ON table_name (column_name);
唯一索引
使用唯一索引不仅是为了性能，同时也为了数据的完整性。唯一索引不允许任何重复的值插入到表中。基本语法如下：
CREATE INDEX index_name on table_name (column_name);
组合索引
组合索引是基于一个表的两个或多个列上创建的索引。基本语法如下：
CREATE INDEX index_name on table_name (column1, column2);
是否要创建一个单列索引还是组合索引，要考虑到您在作为查询过滤条件的 WHERE 子句中使用非常频繁的列。
如果值使用到一个列，则选择使用单列索引。如果在作为过滤的 WHERE 子句中有两个或多个列经常使用，则选择使用组合索引。
隐式索引
隐式索引是在创建对象时，由数据库服务器自动创建的索引。索引自动创建为主键约束和唯一约束。
实例
下面是一个例子，我们将在 COMPANY 表的 salary 列上创建一个索引：
sqlite> CREATE INDEX salary_index ON COMPANY (salary);
现在，让我们使用 .indices 命令列出 COMPANY 表上所有可用的索引，如下所示：
sqlite> .indices COMPANY
这将产生如下结果，其中 sqlite_autoindex_COMPANY_1 是创建表时创建的隐式索引。
salary_index sqlite_autoindex_COMPANY_1
您可以列出数据库范围的所有索引，如下所示：
sqlite> SELECT * FROM sqlite_master WHERE type = 'index';
DROP INDEX 命令
一个索引可以使用 SQLite 的 DROP 命令删除。当删除索引时应特别注意，因为性能可能会下降或提高。
基本语法如下：
DROP INDEX index_name;
您可以使用下面的语句来删除之前创建的索引：
sqlite> DROP INDEX salary_index;
什么情况下要避免使用索引？
虽然索引的目的在于提高数据库的性能，但这里有几个情况需要避免使用索引。使用索引时，应重新考虑下列准则：
-
索引不应该使用在较小的表上。
-
索引不应该使用在有频繁的大批量的更新或插入操作的表上。
-
索引不应该使用在含有大量的 NULL 值的列上。
-
索引不应该使用在频繁操作的列上。
SQLite Indexed By
"INDEXED BY index-name" 子句规定必须需要命名的索引来查找前面表中值。
如果索引名 index-name 不存在或不能用于查询，然后 SQLite 语句的准备失败。
"NOT INDEXED" 子句规定当访问前面的表（包括由 UNIQUE 和 PRIMARY KEY 约束创建的隐式索引）时，没有使用索引。
然而，即使指定了 "NOT INDEXED"，INTEGER PRIMARY KEY 仍然可以被用于查找条目。
语法
下面是 INDEXED BY 子句的语法，它可以与 DELETE、UPDATE 或 SELECT 语句一起使用：
SELECT|DELETE|UPDATE column1, column2... INDEXED BY (index_name) table_name WHERE (CONDITION);
实例
假设有表 COMPANY，我们将创建一个索引，并用它进行 INDEXED BY 操作。
sqlite> CREATE INDEX salary_index ON COMPANY(salary); sqlite>
现在使用 INDEXED BY 子句从表 COMPANY 中选择数据，如下所示：
sqlite> SELECT * FROM COMPANY INDEXED BY salary_index WHERE salary > 5000;
SQLite Alter 命令
SQLite 的 ALTER TABLE 命令不通过执行一个完整的转储和数据的重载来修改已有的表。您可以使用 ALTER TABLE 语句重命名表，使用 ALTER TABLE 语句还可以在已有的表中添加额外的列。
在 SQLite 中，除了重命名表和在已有的表中添加列，ALTER TABLE 命令不支持其他操作。
语法
用来重命名已有的表的 ALTER TABLE 的基本语法如下：
ALTER TABLE database_name.table_name RENAME TO new_table_name;
用来在已有的表中添加一个新的列的 ALTER TABLE 的基本语法如下：
ALTER TABLE database_name.table_name ADD COLUMN column_def...;
实例
假设我们的 COMPANY 表有如下记录：
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
现在，让我们尝试使用 ALTER TABLE 语句重命名该表，如下所示：
sqlite> ALTER TABLE COMPANY RENAME TO OLD_COMPANY;
上面的 SQLite 语句将重命名 COMPANY 表为 OLD_COMPANY。现在，让我们尝试在 OLD_COMPANY 表中添加一个新的列，如下所示：
sqlite> ALTER TABLE OLD_COMPANY ADD COLUMN SEX char(1);
现在，COMPANY 表已经改变，使用 SELECT 语句输出如下：
ID NAME AGE ADDRESS SALARY SEX ---------- ---------- ---------- ---------- ---------- --- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
请注意，新添加的列是以 NULL 值来填充的。
977
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
