SQLite学习笔记

笔记学习于菜鸟教程

一.初级教程

一.SQLite 数据类型

1.SQLite存储类

存储在SQLite数据库中的每个值都具有以下存储类别之一-
1.NULL 该值为NULL值
2.INTEGER 该值是一个有符号整数，根据值的大小存储在1、2、3、4、6或8个字节中。
3.REAL 该值是一个浮点值，存储为8字节IEEE浮点数
4.TEXT 该值是一个文本字符串，使用数据库编码（UTF-8，UTF-16BE或UTF-16LE）存储
5.BLOB 该值是数据的一滴，完全按输入存储。

2.SQLite关联类型

SQLite支持列上的类型相似性的概念。任何列仍然可以存储任何类型的数据，但是列的首选存储类称为亲和力。 SQLite3数据库中的每个表列都分配了以下类型关联性之一

1	TEXT     该列使用存储类NULL，TEXT或BLOB存储所有数据。

2	NUMERIC  该列可能包含使用所有五个存储类的值。

3	INTEGER  行为与具有NUMERIC相关性的列相同，但CAST表达式除外。

4	REAL     行为类似于具有NUMERIC关联性的列，不同之处在于它强制将整数值转换为浮点表示形式。

5	NONE     亲和性为NONE的列不喜欢一个存储类别而不是另一个存储类别，也没有尝试将数据从一个存储类别强制转换为另一个存储类别。

二.SQLite 创建数据库

在SQLite中，sqlite3命令用于创建新的SQLite数据库。您无需具有任何特殊特权即可创建数据库。

1.sqlite3 创建数据库

# 创建数据库
sqlite3 DatabaseName.db
#打开数据库
.open DatabaseName.db
# 查看数据库
.database
# 退出
.quit

2.数据库导出与导入

# 需要先退出数据库
# 数据库导出
sqlite3 DatabaseNane.db .dump > DatabaseName.sql
# 将导出的数据，导入到数据库
sqlite3 DatabaseName.db < DatabaseName.sql

三.SQLite 附加数据库（ATTACH DATABASE）

当您有多个可用数据库并且想一次使用其中任何一个数据库时。SQLite ATTACH DATABASE语句用于选择特定的数据库，执行此命令后，所有SQLite语句将在附加数据库下执行。
语法：

# 打开数据库
.open database.db;
# 附加数据库
ATTACH DATABASE 'DatabaseName' As 'DataName';

注：数据库名称main和temp是为主数据库和保留临时表和其他临时数据对象的数据库保留的。 这两个数据库名称对于每个数据库连接都存在，并且不应用于附件

四.SQLite 分离数据库（DETACH DATABASE）

SQLite DETACH DATABASE语句用于将命名数据库与以前使用ATTACH语句附加的数据库连接分离和取消关联。如果同一个数据库文件附加了多个别名，则DETACH命令将仅断开给定名称的连接，其余附件仍将继续。您不能分离main或temp数据库。

# 打开数据库
.open database.db;
# 分离数据
DETACH DATABASE 'DataName';

注：取消数据库的附加

五.SQLite 创建表

SQLite CREATE TABLE语句用于在任何给定数据库中创建新表。创建基本表包括命名表、定义其列和每列的数据类型。
语法:

# 打开数据库
.open database.db;
# 创建表
CREATE TABLE database_name.table_name(
   column1 datatype PRIMARY KEY(one or more columns),
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype);

创建一个ID为主键的COMPANY表，而NOT NULL是约束条件，表明在此表中创建记录时这些字段不能为NULL。
例：

# 打开数据库
.open database.db;
# 创建表
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);
CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

六.SQLite 删除表（DROP TABLE）

SQLite DROP TABLE语句用于删除表定义以及该表的所有关联数据，索引，触发器，约束和权限规范。

使用此命令时必须小心，因为一旦删除了表，那么表中所有可用的信息也将永远丢失。
语法：

DROP TABLE database_name.table_name;

例：

# 打开数据库
.open database.db;
# 查看表
.tables
# 删除表
DROP TABLE COMPANY;
# 再次查看表，会看不到那个表
.tables

七.SQLite 增删改查

1.SQLite 插入数据

SQLite INSERT INTO语句用于将新的数据行添加到数据库的表中。
语法：

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

例：
在COMPANY表中创建六个记录

#方法一
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'David', 27, 'Texas', 85000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );

# 方法二
INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );

1.使用另一个表填充一个表

您可以通过另一个表上的select语句将数据填充到表中，前提是另一个表具有一组字段，这些字段是填充第一个表所必需的。
语法：

INSERT INTO first_table_name [(column1, column2, ... columnN)] 
   SELECT column1, column2, ...columnN 
   FROM second_table_name
   [WHERE condition];

2.SQLite SELECT 查询

SQLite SELECT语句用于从SQLite数据库表中获取数据，该表以结果表的形式返回数据。这些结果表也称为result sets。
语法:

# 查看全部数据
SELECT * FROM table_name;
# 查看指定字段的数据
SELECT column1, column2, columnN FROM table_name;

例：

在这里插入代码片

使用SELECT语句获取并显示COMPANY的记录

# 设置表头
.header on
# 设置列名
.mode column
# 查询指定字段的数据
SELECT ID, NAME, SALARY FROM COMPANY;
# 查询所有数据
SELECT * FROM COMPANY;

2.1 设置输出列宽

.mode column由于要显示的列的默认宽度，您会遇到与截断输出有关的问题。您可以做的是，可以使用以下.width num, num…命令设置列可显示的列宽：

.width 10, 20, 10

2.2模式信息

由于所有的 dot 命令都可以在 SQLite 提示符下使用，因此在使用 SQLite 编程时，您将使用下面的 SELECT 语句和 SQLite 主表列出在数据库中创建的所有表。

SELECT tbl_name FROM sqlite_master WHERE type = 'table';
# 列出有关COMPANY表的完整信息：
SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'COMPANY';

3.SQLite 表达式

表达式是一个或多个值，运算符以及对一个值求值的SQL函数的组合。

SQL表达式类似于公式，它们以查询语言编写。您还可以用于在数据库中查询一组特定的数据。
语法：

SELECT column1, column2, columnN FROM table_name WHERE [CONDITION | EXPRESSION];

1.SQLite-布尔表达式

SQLite布尔表达式基于匹配的单个值获取数据。
语法：

SELECT column1, column2, columnN FROM table_name WHERE SINGLE VALUE MATCHTING EXPRESSION;

查询薪水等于10000的数据

SELECT * FROM COMPANY WHERE SALARY = 10000;

2.SQLite-数值表达式

这些表达式用于在任何查询中执行任何数学运算。
语法：

SELECT numerical_expression as OPERATION_NAME[FROM table_name WHERE CONDITION] ;

numeric_expression用于数学表达式或任何公式

sqlite> SELECT (15 + 6) AS ADDITION
# 有几个内置函数，如avg()、sum()、count()等，用于对表或特定表列执行所谓的聚合数据计算
 SELECT COUNT(*) AS "RECORDS" FROM COMPANY; 

3.SQLite-日期表达式

日期表达式返回当前系统日期和时间值。

SELECT CURRENT_TIMESTAMP;

4.SQLite WHERE 子句

SQLite WHERE子句用于指定从一个或多个表中获取数据时的条件。

如果满足给定条件，即为true，那么它将从表中返回特定值。您将必须使用WHERE子句来过滤记录并仅提取必要的记录。
语法：

SELECT column1, column2, columnN FROM table_nameWHERE [condition]

例：

# 列出AGE(年龄)大于或等于25 和 SALARY (薪水)大于或等于65000.00的所有记录。
SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;
# 列出AGE(年龄)大于或等于25 或 SALARY (薪水)大于或等于65000.00的所有记录。
SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;
# 列出了AGE(年龄)不为NULL的所有记录
SELECT * FROM COMPANY WHERE AGE IS NOT NULL;
# 列出了NAME以'Ki'开头的所有记录，而与'Ki'之后的任意记录
SELECT * FROM COMPANY WHERE NAME LIKE 'Ki%';
# 列出了NAME以'Ki'开头的所有记录，与'Ki'之后的记录无关
 SELECT * FROM COMPANY WHERE NAME GLOB 'Ki*';
# 列出了AGE(年龄)值为25或27的所有记录
SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );
# 列出了AGE(年龄)值既不是25也不是27的所有记录。
SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );
# 列出了AGE(年龄)值在25和27之间的所有记录。
SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;
#SELECT语句使用SQL子查询，其中子查询查找AGE(年龄)字段的SALARY> 65000的所有记录，随后WHERE子句与EXISTS运算符一起使用，以列出外部查询存在AGE的所有记录在子查询返回的结果中
SELECT AGE FROM COMPANY 
   WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
#  SELECT语句使用SQL子查询，其中子查询查找AGE字段的SALARY> 65000的所有记录，以及与 > 运算符一起使用的where子句列出外部查询的AGE大于子查询返回的结果中的AGE的所有记录。
SELECT * FROM COMPANY 
   WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

5.SQLite AND＆OR 运算符

SQLite AND＆OR运算符用于编译多个条件，以缩小SQLite语句中选定数据的范围。这两个运算符称为合取运算符。

1.AND运算符

AND 运算符允许多个条件在SQLite的声明中存在的WHERE子句。使用AND运算符时，当所有条件都为真时，将假定完全条件为真。
语法：

SELECT column1, column2, columnN FROM table_nameWHERE [condition1] AND [condition2]...AND [conditionN];

例：

# 列出AGE大于或等于25 AND 薪水(SALARY)大于或等于65000.00的所有记录。
SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;

2.OR 运算符

OR运算符还用于在SQLite语句的WHERE子句中组合多个条件。使用OR运算符时，如果至少有一个条件为true，则将假定完全条件为true。
语法：

SELECT column1, column2, columnN FROM table_nameWHERE [condition1] OR [condition2]...OR [conditionN]

例：

# 列出AGE大于或等于25 OR 薪水大于或等于65000.00的所有记录。
 SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;

6.SQLite UPDATE 查询

SQLite UPDATE查询用于修改表中的现有记录。您可以将WHERE子句与UPDATE查询一起使用来更新选定的行，否则所有行都将被更新。
语法：

UPDATE table_nameSET column1 = value1, column2 = value2...., columnN = valueNWHERE [condition];

# 将为ID为6的客户更新ADDRESS
UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6;
# 修改COMPANY表中的所有ADDRESS和SALARY列值，则无需使用WHERE子句
 UPDATE COMPANY SET ADDRESS = 'Texas', SALARY = 20000.00;

6.SQLite DELETE 查询

SQLite DELETE查询用于从表中删除现有记录。您可以将WHERE子句与DELETE查询一起使用以删除所选的行，否则所有记录将被删除。
语法：

DELETE FROM table_nameWHERE [condition];

例：

# 删除ID为7的客户
DELETE FROM COMPANY WHERE ID = 7;
# 删除COMPANY表中的所有记录，则无需将WHERE子句与DELETE查询一起使用
DELETE FROM COMPANY;

二.中级教程

1.SQLite LIKE 子句

SQLite LIKE 操作符用于使用通配符将文本值与模式匹配。如果搜索表达式可以与模式表达式匹配，LIKE 运算符将返回 true，即1。有两个通配符与 LIKE 操作符-一起使用

百分号（％）
下划线(_)
百分号代表零个，一个或多个数字或字符。下划线表示单个数字或字符。这些符号可以组合使用。

语法：

SELECT FROM table_name  WHERE column LIKE 'XXXX%'
or 
SELECT FROM table_name  WHERE column LIKE '%XXXX%'
or 
SELECT FROM table_name WHERE column LIKE 'XXXX_'
or
SELECT FROM table_name WHERE column LIKE '_XXXX'
or
SELECT FROM table_nameWHERE column LIKE '_XXXX_'

例：

# 显示COMPANY表中AGE以2开头的所有记录
SELECT * FROM COMPANY WHERE AGE LIKE '2%';
# 显示COMPANY表中的所有记录，其中ADDRESS在文本内将带有连字符(-)。
SELECT * FROM COMPANY WHERE ADDRESS  LIKE '%-%';

2.SQLite GLOB 子句

SQLite GLOB运算符用于使用通配符仅将文本值与模式匹配。如果搜索表达式可以与模式表达式匹配，则GLOB运算符将返回true，即为1。与LIKE运算符不同，GLOB区分大小写，并且它遵循UNIX的语法来指定THE以下通配符。

星号（*）
问号（？）
星号（*）表示零个或多个数字或字符。问号（？）代表单个数字或字符。

语法：

SELECT FROM table_name WHERE column GLOB 'XXXX*'
or 
SELECT FROM table_name WHERE column GLOB '*XXXX*'
or  
SELECT FROM table_name WHERE column GLOB 'XXXX?'
or  
SELECT FROM table_name WHERE column GLOB '?XXXX'
or  
SELECT FROM table_name WHERE column GLOB '?XXXX?'
or  
SELECT FROM table_name WHERE column GLOB '????'

例：

# 显示COMPANY表中的所有记录，其中AGE以2开头
SELECT * FROM COMPANY WHERE AGE  GLOB '2*';
# 显示COMPANY表中的所有记录，其中ADDRESS在文本内将带有连字符(-)-
SELECT * FROM COMPANY WHERE ADDRESS  GLOB '*-*';

3.SQLite LIMIT 子句

SQLite LIMIT子句用于限制SELECT语句返回的数据量。
语法：

SELECT column1, column2, columnN FROM table_nameLIMIT [no of rows]

LIMIT子句与OFFSET子句一起使用时的语法。

SELECT column1, column2, columnN FROM table_nameLIMIT [no of rows] OFFSET [row num]

例：

# 从表中获取的行数来限制表中的行。
SELECT * FROM COMPANY LIMIT 3;
# 在某些情况下，您可能需要从特定的偏移量获取一组记录。下面是一个实例，它从第三个位置开始获取3条记录
SELECT * FROM COMPANY LIMIT 3 OFFSET 2;

4.SQLite ORDER BY子句

SQLite ORDER BY子句用于根据一个或多个列以升序或降序对数据进行排序。
语法

SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC];

例：

# 将按SALARY降序对结果进行排序
SELECT * FROM COMPANY ORDER BY SALARY ASC;
# 按NAME和SALARY的降序对结果进行排序
SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC;
# 将按照NAME的降序对结果进行排序。
SELECT * FROM COMPANY ORDER BY NAME DESC;

5.SQLite GROUP BY子句

SQLite GROUP BY 子句与SELECT语句配合使用，以将相同的数据分组。

GROUP BY子句在SELECT语句中的WHERE子句之后，并在ORDER BY子句之前。
语法：

SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN

例：

# 想知道每个客户的工资总额，那么GROUP BY查询
SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;
# 再加三条数据
INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00 );
INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00 );
INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00 );
# 使用相同的语句使用NAME列对所有记录进行分组
SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME;
# 使用ORDER BY子句和GROUP BY子句
 SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME DESC;

6.SQLite HAVING 子句

HAVING子句使您可以指定条件，以过滤哪些组结果出现在最终结果中。

WHERE子句在所选列上放置条件，而HAVING子句在GROUP BY子句创建的组上放置条件。
语法：

# 以下是HAVING子句在SELECT查询中的位置。
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

# HAVING子句必须在查询中的GROUP BY子句之后，并且如果使用，还必须在ORDER BY子句之前。以下是SELECT语句的语法，包括HAVING子句。
SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

例：

# 显示名称计数小于2的记录
 SELECT * FROM COMPANY GROUP BY name HAVING count(name) < 2;
# 显示名称计数大于2的记录
SELECT * FROM COMPANY GROUP BY name HAVING count(name) > 2;

7.SQLite DISTINCT关键字

SQLite DISTINCT关键字与SELECT语句结合使用，以消除所有重复记录并仅获取唯一记录。

当表中有多个重复记录时，可能会出现这种情况。在获取此类记录时，仅获取唯一记录而不是获取重复记录更为有意义。
语法：

SELECT DISTINCT column1, column2,.....columnN FROM table_name
WHERE [condition]

例：

# 下面的SELECT查询如何返回重复的薪水记录
 SELECT name FROM COMPANY;
# 使用DISTINCT在上面的SELECT查询中使用关键字并查看结果。
 SELECT DISTINCT name FROM COMPANY;

三.高级教程

1.SQLite PRAGMA

SQLite PRAGMA命令是一个特殊的命令，用于控制SQLite环境中的各种环境变量和状态标志。PRAGMA值可以读取，也可以根据需要进行设置。
语法：

# 查看当前设置
PRAGMA pragma_name;
# 设置新值
PRAGMA pragma_name = value;

附加

1.auto_vacuum PRAGMA

auto_vacuum PRAGMA获取或设置自动真空模式。以下是简单的语法。

2.cache_size Pragma

cache_size编译可以得到或暂时设置在内存中的页面缓存的最大尺寸。

PRAGMA [database.]cache_size;
PRAGMA [database.]cache_size = pages;

pages值表示高速缓存中的页面数。内置页面缓存的默认大小为2,000页，最小大小为10页。

3.case_sensitive_like Pragma

case_sensitive_like pragma控制内置LIKE表达式的大小写敏感。默认情况下，此pragma为false，这意味着内置的LIKE运算符忽略字母大小写。
语法：

PRAGMA case_sensitive_like = [true|false];

4.count_changes Pragma

count_changes pragma获取或设置数据操作语句（例如INSERT，UPDATE和DELETE）的返回值。
语法：

RAGMA count_changes;
PRAGMA count_changes = [true|false];

5.database_list Pragma

database_list实用程序将用于列出所有附加数据库。
语法：

PRAGMA database_list;

6.encoding Pragma

encoding pragma控制字符串是如何编码，并存储在数据库中的文件。

PRAGMA encoding;
PRAGMA encoding = format;

格式值可以是UTF-8, UTF-16le或UTF-16be

7.freelist_count Pragma

freelist_count Pragma返回一个整数，表示许多数据库页当前如何标记为空闲和可用的。

PRAGMA [database.]freelist_count;

格式值可以是UTF-8, UTF-16le或UTF-16be之一

8.index_info Pragma

index_info Pragma返回有关数据库索引的信息。以下是简单的语法。

PRAGMA [database.]index_info( index_name );

结果集将为索引中包含的每一列包含一行，以给出列顺序，表中包含列索引和列名。

9.index_list Pragma

index_list Pragma列出与表关联的所有索引。以下是简单的语法。

PRAGMA [database.]index_list( table_name );

结果集将为每个索引包含一行，从而提供索引序列，索引名称和指示索引是否唯一的标志。

10.journal_mode Pragma

journal_mode Pragma获取或设置控制日志文件如何存储和处理的日志模式。
语法：

PRAGMA journal_mode;
PRAGMA journal_mode = mode;
PRAGMA database.journal_mode;
PRAGMA database.journal_mode = mode;

DELETE      这是默认模式。在交易结束时，日记文件被删除。

TRUNCATE    日志文件被截断为零字节的长度。

PERSIST     日记文件保留在原处，但是标题被覆盖以指示日记不再有效。

MEMORY      日记记录保存在内存中，而不是磁盘上。

OFF         不保留日记记录。

11.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页面大小，则数据库可以增长到1 TB。

12.page_count PRAGMA

page_count PRAGMA返回的数据库的当前页数。

PRAGMA [database.]page_count;

数据库文件的大小应为page_count * page_size

13.page_size PRAGMA

page_size PRAGMA获取或设置数据库页面的大小。
语法：

PRAGMA [database.]page_size;
PRAGMA [database.]page_size = bytes;

默认情况下，允许的大小为512、1024、2048、4096、8192、16384和32768字节。更改现有数据库上页面大小的唯一方法是设置页面大小，然后立即对数据库进行VACUUM。

14.parser_trace PRAGMA

parser_trace PRAGMA指示控制打印调试状态，因为它解析SQL命令。

PRAGMA parser_trace = [true|false];

默认情况下，它设置为false，但是当通过将其设置为true启用时，SQL解析器将在解析SQL命令时打印其状态。

15.recursive_triggers PRAGMA

recursive_triggers PRAGMA获取或设置递归触发功能。如果未启用递归触发器，则触发器操作将不会触发另一个触发器。
语法：

PRAGMA recursive_triggers;
PRAGMA recursive_triggers = [true|false];

16.schema_version PRAGMA

schema_version PRAGMA获取或设置存储在数据库头架构版本值。

PRAGMA [database.]schema_version;
PRAGMA [database.]schema_version = number;

这是一个32位带符号整数值，用于跟踪架构更改。每当执行更改模式的命令（例如CREATE …或DROP …）时，此值都会增加。

17.secure_delete PRAGMA

secure_delete PRAGMA用于控制如何将内容从数据库中删除。

PRAGMA secure_delete;
PRAGMA secure_delete = [true|false];
PRAGMA database.secure_delete;
PRAGMA database.secure_delete = [true|false];

安全删除标志的默认值通常为off，但是可以使用SQLITE_SECURE_DELETE构建选项更改该默认值。

18.sql_trace PRAGMA

sql_trace PRAGMA用于转储SQL跟踪结果到屏幕上。

PRAGMA sql_trace;
PRAGMA sql_trace = [true|false];

必须使用SQLITE_DEBUG指令编译SQLite，才能包含此编译指示。

19.synchronous PRAGMA

synchronous PRAGMA获取或设置当前磁盘同步模式，控制如何积极的SQLite将一路写数据到物理存储。

PRAGMA [database.]synchronous;
PRAGMA [database.]synchronous = mode;

0 or OFF      完全没有同步

1 or NORMAL   在每个关键磁盘操作序列之后进行同步

2 or FULL     每次关键磁盘操作后同步

20.temp_store PRAGMA

temp_store PRAGMA获取或设置临时数据库文件所使用的存储模式。

PRAGMA temp_store;
PRAGMA temp_store = mode;

0 or DEFAULT      使用编译时默认值。通常是FILE。

1 or FILE         使用基于文件的存储。

2 or MEMORY       使用基于内存的存储。

21.temp_store_directory PRAGMA

temp_store_directory PRAGMA获取或设置用于临时数据库文件的位置。

PRAGMA temp_store_directory;
PRAGMA temp_store_directory = 'directory_path';

22.user_version PRAGMA

user_version编译指示获取或设置存储在数据库头中的用户定义的版本值。

PRAGMA [database.]user_version;
PRAGMA [database.]user_version = number;

这是一个32位带符号整数值，开发人员可以出于版本跟踪目的设置该值。

23.writable_schema PRAGMA

writable_schema编译获取或设置修改系统表的能力。

PRAGMA writable_schema;
PRAGMA writable_schema = [true|false];

如果设置了此编译指示，则可以创建和修改以sqlite_开头的表，包括sqlite_master表。使用编译指示时请小心，因为它可能导致数据库完全损坏

二.SQLite 约束

约束是对表的数据列强制执行的规则。这些用于限制可以进入表的数据类型。这样可以确保数据库中数据的准确性和可靠性。

约束可以是列级别或表级别。列级约束仅应用于一列，而表级约束则应用于整个表。
常用约束。

NOT NULL 约束−确保列不能为NULL值。

DEFAULT 约束−如果未指定，则为列提供默认值。

UNIQUE 约束−确保列中的所有值均不同。

PRIMARY Key −唯一标识数据库表中的每一行/记录。

CHECK 约束−确保列中的所有值都满足某些条件。

1.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
);

2.DEFAULT 约束

当INSERT INTO语句未提供特定值时，DEFAULT约束将为列提供默认值。
例：

以下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
);

3.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
);

4.PRIMARY Key约束

PRIMARY KEY约束唯一地标识数据库表中的每个记录。可以有更多的UNIQUE列，但表中只有一个主键。在设计数据库表时，主键很重要。主键是唯一的ID。

我们使用它们来引用表行。在表之间创建关系时，主键成为其他表中的外键。
主键是表中的字段，它唯一地标识数据库表中的每一行/记录。主键必须包含唯一值。主键列不能具有NULL值。
一个表只能有一个主键，它可以由单个或多个字段组成。当多个字段用作主键时，它们称为composite key。
如果表在任何表上定义了主键field(s)，则不能有两个记录具有相同的值field(s)。

例：
创建了以ID为主键的COMPANY表

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

5.CHECK约束

CHECK 约束使条件能够检查输入到记录中的值。如果条件的计算结果为false，则记录违反了约束且未输入到表中。

例：
以下SQLite创建一个名为COMPANY的新表并添加五列。在这里，我们添加了一个带有SALARY的CHECK列，因此您不能有任何SALARY 0

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)
);

3.删除约束

SQLite支持ALTER TABLE的有限子集。SQLite中的ALTER TABLE命令允许用户重命名表或向现有表添加新列。不能重命名列、删除列或在表中添加或删除约束。

三.SQLite 联接

SQLite Joins子句用于合并数据库中两个或多个表中的记录。JOIN是一种通过使用每个表的公用值来组合两个表中的字段的方法。

SQL定义了三种主要的联接类型-

交叉联接

内部联接

外联接

1.CROSS JOIN - 交叉联接

CROSS JOIN将第一个表的每一行与第二个表的每一行匹配。如果输入表分别具有x和y行，则结果表将具有x * y行。由于CROSS JOINs可能会生成极大的表，因此必须注意仅在适当的时候使用它们。
语法：

SELECT ... FROM table1 CROSS JOIN table2 ...

2.INNER JOIN - 内部联接

INNER JOIN通过基于连接谓词组合两个表（table1和table2）的列值来创建新的结果表。该查询将table1的每一行与table2的每一行进行比较，以找到满足join谓词的所有行对。当满足连接谓词时，A和B的每个匹配行对的列值将合并为结果行。

INNER JOIN是最常见的默认连接类型。

SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...

为了避免冗余并缩短短语，可以使用USING表达式声明INNER JOIN条件。此表达式指定一个或多个列的列表。

SELECT ... FROM table1 JOIN table2 USING ( column1 ,... ) ...

3.OUTER JOIN - 外联接

OUTER JOIN是INNER JOIN的扩展。尽管SQL标准定义了三种外部联接类型：LEFT，RIGHT和FULL，但是SQLite仅支持LEFT OUTER JOIN。

外部联接的条件与内部联接的条件相同，使用ON，USING或NATURAL关键字表示。初始结果表的计算方法相同。一旦计算了主JOIN，OUTER JOIN将从一个或两个表中获取所有未连接的行，将它们填充为NULL，然后将它们附加到结果表中。
语法：

SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...

为了避免冗余并缩短短语，可以使用USING表达式声明OUTER JOIN条件。此表达式指定一个或多个列的列表。

SELECT ... FROM table1 LEFT OUTER JOIN table2 USING ( column1 ,... ) ...

四.SQLite UNION子句

SQLite UNION子句/运算符用于合并两个或多个SELECT语句的结果，而不返回任何重复的行。

1.UNION

要使用UNION，每个SELECT必须具有相同数量的选定列，相同数量的列表达式，相同数据类型，并且具有相同的顺序，但是它们的长度不必相同。
语法：

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

2.UNION ALL子句

UNION ALL运算符用于合并两个SELECT语句的结果，包括重复的行。

适用于UNION的相同规则也适用于UNION ALL运算符。
语法：

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION ALL

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

五.SQLite NULL值

SQLite 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值的字段是在记录创建过程中留为空白的字段。

六.SQLite ALIAS语法

您可以通过提供另一个名称(ALIAS)来临时重命名表或列。 表别名的使用意味着在特定的SQLite语句中重命名表。 重命名是一个临时更改，数据库中的实际表名不会更改。

列别名用于重命名表的列，以用于特定的SQLite查询。
语法

# table别名的基本语法
SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];
# column别名的基本语法。
SELECT column_name AS alias_name
FROM table_name
WHERE [condition];

七.SQLite 触发器（Trigger）

SQLite触发器是数据库回调函数，当发生指定的数据库事件时，将自动执行/调用这些函数。 以下是有关SQLite触发器的要点-

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
 -- 触发器逻辑....
END;

event_name可以是对上述表的INSERT，DELETE和UPDATE数据库操作table_name。您可以选择在表名称后指定FOR EACH ROW。

表的一个或多个指定列上的UPDATE操作上创建触发器的语法。

CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name 
ON table_name

BEGIN
   -- 触发逻辑在这里....
END;

例：
我们希望对插入到COMPANY表中的每条记录进行审计试用，这是我们新创建的，如下所示（如果已经有了，则删除COMPANY表）。

CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
只要在COMPANY表中有新记录的条目，我们将创建一个名为AUDIT的新表，在该表中将插入日志消息。

CREATE TABLE AUDIT(
   EMP_ID INT NOT NULL,
   ENTRY_DATE TEXT NOT NULL
);

，ID是AUDIT记录ID，EMP_ID是来自COMPANY表的ID，DATE将在COMPANY表中创建记录时保留时间戳。现在让我们在COMPANY表上创建触发器。

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表中创建一条记录。

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );

列出触发器

您可以从sqlite_master表中列出所有触发器

SELECT name FROM sqlite_master
WHERE type = 'trigger';

列出特定表上的触发器，请使用AND子句和表名

SELECT name FROM sqlite_master
WHERE type = 'trigger' AND tbl_name = 'COMPANY';

删除触发器

以下是DROP命令，可用于删除现有触发器。

DROP TRIGGER trigger_name;

八.SQLite 索引

索引是特殊的查找表，数据库搜索引擎可以使用索引来加速数据检索。简而言之，anindex是指向表中数据的指针。数据库中的索引与书后的索引非常相似。

例如，如果要引用一本书中讨论某个主题的所有页面，则首先要参考索引，该索引按字母顺序列出所有主题，然后引用一个或多个特定的页码。

索引有助于加快SELECT查询和WHERE子句的速度，但它会通过UPDATE和INSERT语句减慢数据输入速度。可以创建或删除索引，而不会影响数据。

创建索引涉及CREATE INDEX语句，该语句使您可以命名索引，指定表以及要索引的列或列，并指示索引是升序还是降序。

索引也可以是唯一的，类似于UNIQUE约束，因为索引可以防止存在索引的列或列组合中的重复条目

1.CREATE INDEX命令

语法：

CREATE INDEX index_name ON table_name;

1.单列索引

单列索引是仅基于一个表列创建的索引。基本语法如下-

CREATE INDEX index_nameON table_name (column_name);

2.唯一索引

唯一索引不仅用于提高性能，而且还用于数据完整性。唯一索引不允许将任何重复的值插入表中。基本语法如下-

CREATE UNIQUE INDEX index_nameon table_name (column_name);

3.复合索引

复合索引是表的两个或多个列上的索引。基本语法如下-

CREATE INDEX index_nameon table_name (column1, column2);

无论是创建单列索引还是复合索引，都要考虑column(s)到您可能在查询的WHERE子句中非常频繁地使用它作为过滤条件。

如果只使用一列，则应选择单列索引。如果WHERE子句中经常使用两个或更多列作为过滤器，则复合索引将是最佳选择。

2.隐式索引

隐式索引是创建对象时由数据库服务器自动创建的索引。自动为主键约束和唯一约束创建索引。
例：

 # 在COMPANY表中为薪水列创建索引
  CREATE INDEX salary_index ON COMPANY (salary);
  # .indices命令列出COMPANY表中所有可用的索引
  .indices COMPANY

3.DROP INDEX命令

可以使用SQLite DROP命令删除索引。删除索引时应小心，因为性能可能会减慢或提高。

语法：

DROP INDEX index_name;

4.什么时候应该避免索引？

尽管索引旨在提高数据库的性能，但有时应避免使用它们。以下准则指示何时应重新考虑使用索引。
索引不得用于
小表。

具有频繁，大量批处理更新或插入操作的表。

包含大量NULL值的列。

经常操作的列。

九.SQLite 子句索引

“ 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_nameWHERE (CONDITION);

在表COMPANY我们将创建一个索引并将其用于执行INDEXED BY操作。

CREATE INDEX salary_index ON COMPANY(salary);
# 从表COMPANY中选择数据，您可以使用INDEXED BY子句
SELECT * FROM COMPANY INDEXED BY salary_index WHERE salary > 5000;

十.SQLite ALTER TABLE命令
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...;

十一.SQLite TRUNCATE TABLE命令

不幸的是，我们在SQLite中没有TRUNCATE TABLE命令，但是您可以使用SQLite DELETE命令从现有表中删除完整的数据，不过建议您使用DROP TABLE命令删除完整的表并重新创建它。
语法：

# DELETE命令的基本语法。
DELETE FROM table_name;
# DROP TABLE的基本语法。
DROP TABLE table_name;

如果使用DELETE TABLE命令删除所有记录，建议使用VACUUM命令清除未使用的空间。
例：

DELETE FROM COMPANY;
VACUUM;

十三.SQLite 视图

视图只不过是数据库中存储的带有相关名称的 SQLite 语句。它实际上是一个预定义 SQLite 查询形式的表的组合。

一个视图可以包含一个表的所有行或一个或多个表中的选定行。可以从一个或多个表创建视图，这取决于编写的SQLite查询来创建视图。

作为虚拟表的视图允许用户-

以用户或用户类别自然或直观的方式来构造数据。

限制对数据的访问，以便用户只能看到有限的数据，而不是完整的表。

汇总各种表中的数据，这些数据可用于生成报告。

SQLite视图是只读的，因此您可能无法在视图上执行DELETE，INSERT或UPDATE语句。但是，您可以在视图上创建触发器，该触发器将在尝试删除，插入或更新视图时触发，并在触发器主体中执行所需的操作

1.创建视图

使用CREATE VIEW语句创建SQLite视图。可以从一个表，多个表或另一个视图创建SQLite视图。

CREATE [TEMP | TEMPORARY] VIEW view_name ASSELECT column1, column2.....FROM table_nameWHERE [condition];

您可以像在普通SQL SELECT查询中使用多个表一样，在SELECT语句中包括多个表。如果存在可选的TEMP或TEMPORARY关键字，则将在temp数据库中创建该视图。

例：
从COMPANY表创建视图的示例

 CREATE VIEW COMPANY_VIEW AS
SELECT ID, NAME, AGE
FROM  COMPANY;
# 可以通过查询实际表的类似方式查询COMPANY_VIEW
SELECT * FROM COMPANY_VIEW;
# 删除创建COMPANY_VIEW视图
DROP VIEW COMPANY_VIEW;

2.删除视图

要删除视图，只需将DROP VIEW语句与一起使用view_name。
语法：

DROP VIEW view_name;

十四.SQLite 事务

事务是针对数据库执行的工作单元。事务是以逻辑顺序完成的单位或工作顺序，无论是由用户手动完成还是由某种数据库程序自动完成。

事务是将一个或多个更改传播到数据库。例如，如果您要在表中创建，更新或删除记录，那么您将在表上执行事务。控制事务以确保数据完整性和处理数据库错误很重要。

实际上，您会将许多SQLite查询组成一个组，并将它们作为事务的一部分一起执行。

1.事务属性

事务具有以下四个标准属性，
原子性(Atomicity)：确保工作单位内的所有操作都成功完成，否则，事务会在出现故障时终止，之前的操作也会回滚到以前的状态。

一致性（Consistency)：确保数据库在成功提交的事务上正确地改变状态。

隔离性(Isolation)：使事务操作相互独立和透明。

持久性(Durability)：确保已提交事务的结果或效果在系统发生故障的情况下仍然存在。

2.事务控制

控制事务的命令

BEGIN TRANSACTION −开始事务。

COMMIT−要保存更改，也可以使用END TRANSACTION命令。

ROLLBACK −回滚更改。

事务控制命令只与 DML 命令 INSERT、UPDATE 和 DELETE 一起使用。他们不能在创建表或删除表时使用，因为这些操作在数据库中是自动提交的。

3.BEGIN TRANSACTION 命令

可以使用BEGIN TRANSACTION或简单地使用BEGIN命令来启动事务。这样的事务通常会持续到遇到下一个COMMIT或ROLLBACK命令为止。但是，如果数据库关闭或发生错误，则事务也将回滚。以下是启动事务的简单语法。

BEGIN;
or 
BEGIN TRANSACTION;

4.COMMIT 命令

COMMIT命令是用于将事务调用的更改保存到数据库的事务性命令。

自上一个COMMIT或ROLLBACK命令以来，COMMIT命令将所有事务保存到数据库中。

以下是COMMIT命令的语法。

COMMIT;
or
END TRANSACTION;

5.ROLLBACK 命令

ROLLBACK命令是用于撤消尚未保存到数据库的事务的事务性命令。

自上次发出COMMIT或ROLLBACK命令以来，ROLLBACK命令只能用于撤消事务。
以下是ROLLBACK命令的语法。

ROLLBACK;

例：

# 让我们开始一个事务并从age = 25的表中删除记录。然后，使用ROLLBACK命令撤消所有更改。
BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
ROLLBACK;

# 从AGE= 25的表中删除记录，最后我们使用COMMIT命令来提交所有更改
BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
COMMIT;

十五.SQLite 子查询

子查询或内部查询或嵌套查询是另一个SQLite查询中的查询，并嵌入在WHERE子句中。

子查询用于返回将在主查询中使用的数据，作为进一步限制要检索的数据的条件。

子查询可以与SELECT，INSERT，UPDATE和DELETE语句以及=，<，>，> =，<=，IN，BETWEEN等运算符一起使用。

有一些子查询必须遵循的规则-

子查询必须放在括号内。

子查询在SELECT子句中只能有一个列，除非主查询中有多个列供子查询比较其选定的列。

尽管主查询可以使用ORDER BY，但是不能在子查询中使用ORDER BY。GROUP BY可用于执行与子查询中的ORDER BY相同的功能。

返回多行的子查询只能与多个值运算符（例如IN运算符）一起使用。

BETWEEN运算符不能与子查询一起使用；但是，可以在子查询中使用BETWEEN。

1.带有SELECT语句的子查询

子查询最常与SELECT语句一起使用。基本语法如下-

SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
   (SELECT column_name [, column_name ]
      FROM table1 [, table2 ]
      [WHERE])

例：

# 使用SELECT语句检查以下子查询
SELECT * 
   FROM COMPANY 
   WHERE ID IN (SELECT ID 
      FROM COMPANY 
      WHERE SALARY > 45000) ;

2.带有INSERT语句的子查询

子查询也可以与INSERT语句一起使用。INSERT语句使用从子查询返回的数据插入另一个表。可以使用任何字符，日期或数字功能修改子查询中的选定数据。
语法：

INSERT INTO table_name [ (column1 [, column2 ]) ]
   SELECT [ *|column1 [, column2 ]
   FROM table1 [, table2 ]
   [ WHERE VALUE OPERATOR ]

例：

# 考虑一个表COMPANY_BKP，其结构与COMPANY表相似，并且可以使用COMPANY_BKP作为表名使用相同的CREATE TABLE创建。要将完整的COMPANY表复制到COMPANY_BKP
INSERT INTO COMPANY_BKP
   SELECT * FROM COMPANY 
   WHERE ID IN (SELECT ID 
      FROM COMPANY) ;

4.带有UPDATE语句的子查询

子查询可以与UPDATE语句结合使用。使用带有UPDATE语句的子查询时，可以更新表中的单列或多列。
语法：

UPDATE tableSET column_name = new_value[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
      FROM TABLE_NAME)
   [ WHERE) ]

例：

# 将COMPANY表中年龄大于或等于27岁的所有客户的薪资更新0.50倍。
UPDATE COMPANY
   SET SALARY = SALARY * 0.50
   WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
      WHERE AGE >= 27 );

5.带有DELETE语句的子查询

子查询可以与DELETE语句一起使用，就像上面提到的任何其他语句一样。
例：

DELETE FROM TABLE_NAME[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
      FROM TABLE_NAME)
   [ WHERE) ]

例：

# 从COMPANY表中删除年龄大于或等于27的所有客户的记录
DELETE FROM COMPANY
   WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
   WHERE AGE > 27 );

十六.SQLite Autoincrement（自动递增）

SQLite AUTOINCREMENT是用于自动递增表中字段值的关键字。我们可以使用AUTOINCREMENT关键字在创建具有特定列名的表时自动递增字段值。

关键字 AUTOINCREMENT 只能与INTEGER字段一起使用。
语法：

CREATE TABLE table_name(
   column1 INTEGER AUTOINCREMENT,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);

十七.SQLite 注入

如果您的站点允许用户通过网页输入，并将输入内容插入到 SQLite 数据库中，这个时候您就面临着一个被称为 SQL 注入的安全问题。本章节将向您讲解如何防止这种情况的发生，确保脚本和 SQLite 语句的安全。

注入通常在请求用户输入时发生，比如需要用户输入姓名，但用户却输入了一个 SQLite 语句，而这语句就会在不知不觉中在数据库上运行。

永远不要相信用户提供的数据，所以只处理通过验证的数据，这项规则是通过模式匹配来完成的。在下面的实例中，用户名 username 被限制为字母数字字符或者下划线，长度必须在 8 到 20 个字符之间 - 请根据需要修改这些规则。

防止SQL注入

在脚本语言中，比如 PERL 和 PHP，您可以巧妙地处理所有的转义字符。编程语言 PHP 提供了字符串函数 SQLite3::escapeString($string) 和 sqlite_escape_string() 来转义对于 SQLite 来说比较特殊的输入字符。
尽管编码使得插入数据变得安全，但是它会呈现简单的文本比较，在查询中，对于包含二进制数据的列，LIKE 子句是不可用的。

请注意，addslashes() 不应该被用在 SQLite 查询中引用字符串，它会在检索数据时导致奇怪的结果

十八.SQLite Explain（解释）

SQLite语句前面可以加关键字“EXPLAIN”或短语“EXPLAIN QUERY PLAN”，用于描述表的详细信息。

这两种修改都会导致 SQLite 语句表现为一个查询，并返回关于如果省略了 EXPLAIN 关键字或短语 SQLite 语句将如何运行的信息。

EXPLAIN和EXPLAIN QUERY PLAN的输出仅用于交互式分析和故障排除。

输出格式的详细信息可能会从一个版本的SQLite更改为下一个版本。

应用程序不应该使用 EXPLAIN 或 EXPLAIN QUERY PLAN，因为其确切的行为是可变的且只有部分会被记录。

语法：

# EXPLAIN的语法如下:
EXPLAIN [SQLite Query]
# EXPLAIN QUERY PLAN的语法如下:
EXPLAIN  QUERY PLAN [SQLite Query]

例：

# 使用SELECT语句检查以下子查询
EXPLAIN SELECT * FROM COMPANY WHERE Salary >= 20000;
# 让我们检查 SELECT 语句中的 Explain Query Plan 使用
EXPLAIN QUERY PLAN SELECT * FROM COMPANY WHERE Salary >= 20000;

十九.SQLite Vacuum

VACUUM 命令通过复制主数据库中的内容到一个临时数据库文件，然后清空主数据库，并从副本中重新载入原始的数据库文件。这消除了空闲页，把表中的数据排列为连续的，另外会清理数据库文件结构。

如果表中没有明确的整型主键（INTEGER PRIMARY KEY），VACUUM 命令可能会改变表中条目的行 ID(ROWID)。VACUUM 命令只适用于主数据库，附加的数据库文件是不可能使用 VACUUM 命令。

如果有一个活动的事务，VACUUM 命令就会失败。VACUUM 命令是一个用于内存数据库的任何操作。由于 VACUUM 命令从头开始重新创建数据库文件，所以 VACUUM 也可以用于修改许多数据库特定的配置参数。

1.手动 Vacuum

以下是从命令提示符为整个数据库发出VACUUM命令语法：

sqlite3 database_name "VACUUM;"

从SQLite提示符运行VACUUM

VACUUM;

可以在特定表上运行VACUUM

 VACUUM table_name;

2.自动 Vacuum(Auto-VACUUM)

SQLite 的 Auto-VACUUM 与 VACUUM 不大一样，它只是把空闲页移到数据库末尾，从而减小数据库大小。通过这样做，它可以明显地把数据库碎片化，而 VACUUM 则是反碎片化。所以 Auto-VACUUM 只会让数据库更小。

在 SQLite 提示符中，您可以通过下面的编译运行，启用/禁用 SQLite 的 Auto-VACUUM

PRAGMA auto_vacuum = NONE; -- 0 means disable auto vacuum

PRAGMA auto_vacuum = FULL; -- 1 means enable full auto vacuum

PRAGMA auto_vacuum = INCREMENTAL; -- 2 means enable incremental vacuum

1.检查Vacuum设置

sqlite3 database_name "PRAGMA auto_vacuum;"

二十.SQLite 日期和时间

1.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, …)	这将根据第一个参数指定的格式字符串返回格式化的日期。具体格式见下边讲解。

五个日期和时间函数把时间字符串作为参数。时间字符串后跟零个或多个 modifier 修饰符。strftime() 函数也可以把格式字符串 format 作为其第一个参数。下面将为您详细讲解不同类型的时间字符串和修饰符

2.时间字符串

一个时间字符串可以采用下面任何一种格式

时间字符串	实例
YYYY-MM-DD	2010-12-30
YYYY-MM-DD HH:MM	2010-12-30 12:10
YYYY-MM-DD HH:MM:SS.SSS	2010-12-30 12:10:04.100
MM-DD-YYYY HH:MM	30-12-2010 12:10
HH:MM	12:10
YYYY-MM-DDTHH:MM	2010-12-30 12:10
HH:MM:SS	12:10:01
YYYYMMDD HHMMSS	20101230 121001
now	2013-05-07

您可以使用 “T” 作为分隔日期和时间的文字字符。

3.格式化

SQLite 提供了非常方便的函数 strftime() 来格式化任何日期和时间。您可以使用以下的替换来格式化日期和时间：

%d	一月中的第几天，01-31
%f	带小数部分的秒，SS.SSS
%H	小时，00-23
%j	一年中的第几天，001-366
%J	儒略日数，DDDD.DDDD
%m	月，00-12
%M	分，00-59
%s	从 1970-01-01 算起的秒数
%S	秒，00-59
%w	一周中的第几天，0-6 (0 is Sunday)
%W	一年中的第几周，01-53
%Y	年，YYYY
%%	% symbol

例：

# 计算当前日期
SELECT date('now');
# 计算当前月份的最后一天
SELECT date('now','start of month','+1 month','-1 day');
# 计算给定UNIX时间戳1092941466的日期和时间
SELECT datetime(1092941466, 'unixepoch');
# 计算给定UNIX时间戳1092941466的日期和时间，并补偿您的本地时区
SELECT datetime(1092941466, 'unixepoch', 'localtime');
# 计算当前的UNIX时间戳。
SELECT strftime('%s','now');
# 计算自美国独立宣言签署以来的天数
SELECT julianday('now') - julianday('1776-07-04');
# 计算自2004年某个特定时刻以来的秒数
SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');
# 计算当年10月的第一个星期二的日期
SELECT date('now','start of year','+9 months','weekday 2');
# 以秒为单位计算自UNIX时代以来的时间（类似于strftime（'％s'，'now'），但包括小数部分）
SELECT (julianday('now') - 2440587.5)*86400.0;
# 在格式化日期时在UTC和本地时间值之间进行转换，请使用utc或localtime修饰符

SELECT time('12:00', 'localtime');

SELECT time('12:00', 'utc');

二十一.SQLite 常用函数

SQLite具有许多内置函数，可以对字符串或数字数据进行处理。以下是一些有用的SQLite内置函数的列表，所有这些函数都不区分大小写，这意味着您可以以小写形式，大写形式或混合形式使用这些函数。

SQLite COUNT 函数      SQLite COUNT聚合函数用于计算数据库表中的行数。

SQLite MAX 函数        SQLite MAX聚合函数使我们能够为特定列选择最高（最大值）值。

SQLite MIN 函数        SQLite MIN聚合函数允许我们为特定列选择最低（最小值）值。

SQLite AVG 函数        SQLite AVG聚合函数选择某些表列的平均值。

SQLite SUM 函数        SQLite SUM聚合函数允许为数字列选择总计。

SQLite RANDOM 函数     SQLite RANDOM函数返回-9223372036854775808和+9223372036854775807之间的伪随机整数。

SQLite ABS 函数        SQLite ABS函数返回数字参数的绝对值。

SQLite UPPER 函数      SQLite UPPER函数将字符串转换为大写字母。

SQLite LOWER 函数      SQLite LOWER函数将字符串转换为小写字母。

SQLite LENGTH 函数     SQLite LENGTH函数返回字符串的长度。

SQLite sqlite_version 函数 SQLite sqlite_version函数返回SQLite库的版本。

1.SQLite COUNT函数

SQLite COUNT聚合函数用于计算数据库表中的行数

SELECT count(*) FROM COMPANY;

2.SQLite MAX函数

SQLite MAX聚合函数使我们能够为特定列选择最高（最大值）值

SELECT max(salary) FROM COMPANY;

3.SQLite MIN函数

SQLite MIN聚合函数允许我们为特定列选择最低（最小值）值。

 SELECT min(salary) FROM COMPANY;

4.SQLite AVG函数

SQLite AVG聚合函数选择某个表列的平均值

SELECT avg(salary) FROM COMPANY;

5.SQLite SUM函数

SQLite SUM聚合函数允许为数字列选择总计

SELECT sum(salary) FROM COMPANY;

6.SQLite RANDOM函数

SQLite RANDOM函数回-9223372036854775808和+9223372036854775807之间的伪随机整数。

SELECT random() AS Random;

7.SQLite ABS 函数

SQLite ABS函数返回数字参数的绝对值

SELECT abs(5), abs(-15), abs(NULL), abs(0), abs("ABC");

8.SQLite UPPER函数

SQLite UPPER函数将字符串转换为大写字母。

SELECT upper(name) FROM COMPANY;

9.SQLite LOWER函数

SQLite LOWER函数将字符串转换为小写字母。

 SELECT lower(name) FROM COMPANY;

10.SQLite LENGTH函数

SQLite LENGTH函数返回字符串的长度。

 SELECT name, length(name) FROM COMPANY;

11.SQLite sqlite_version 函数

SQLite sqlite_version函数返回SQLite库的版本。

SELECT sqlite_version() AS 'SQLite Version';

四.SQLite 数据库

1.安装

在我们的Java程序中开始使用SQLite之前，您需要确保在计算机上设置了SQLite JDBC驱动程序和Java。您可以检查Java教程以在计算机上安装Java。

从sqlite-jdbc存储库下载最新版本的sqlite-jdbc-(VERSION).jar。

将下载的jar文件sqlite-jdbc-(VERSION).jar添加到您的类路径中，或者将其与-classpath选项一起使用，如以下示例中所述。

2.连接到数据库

以下Java程序显示了如何连接到现有数据库。如果数据库不存在，则将创建该数据库，最后将返回一个数据库对象。

import java.sql.*;

public class SQLiteJDBC {
  public static void main( String args[] ) {
      Connection c = null;
      
      try {
         Class.forName("org.sqlite.JDBC");
         c = DriverManager.getConnection("jdbc:sqlite:test.db");
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName() + ": " + e.getMessage() );
         System.exit(0);
      }
      System.out.println("已成功打开数据库");
   }
}

现在，让我们编译并运行上述程序，以test.db在当前目录中创建数据库。您可以根据需要更改路径。我们假定当前路径中提供了最新版本的JDBC驱动程序sqlite-jdbc-3.7.2.jar。

javac SQLiteJDBC.java
java -classpath ".:sqlite-jdbc-3.7.2.jar" SQLiteJDBC

使用Windows计算机，则可以按以下方式编译和运行代码

javac SQLiteJDBC.java
java -classpath ".;sqlite-jdbc-3.7.2.jar" SQLiteJDBC

3.创建表

以下Java程序将用于在先前创建的数据库中创建表

import java.sql.*;

public class SQLiteJDBC {

   public static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;
      
      try {
         Class.forName("org.sqlite.JDBC");
         c = DriverManager.getConnection("jdbc:sqlite:test.db");
         System.out.println("已成功打开数据库");

         stmt = c.createStatement();
         String sql = "CREATE TABLE COMPANY " +
                        "(ID INT PRIMARY KEY     NOT NULL," +
                        " NAME           TEXT    NOT NULL, " + 
                        " AGE            INT     NOT NULL, " + 
                        " ADDRESS        CHAR(50), " + 
                        " SALARY         REAL)"; 
         stmt.executeUpdate(sql);
         stmt.close();
         c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName() + ": " + e.getMessage() );
         System.exit(0);
      }
      System.out.println("表创建成功");
   }
}

4.INSERT 操作

以下Java程序显示了如何在上面的示例中创建的COMPANY表中创建记录。

import java.sql.*;

public class SQLiteJDBC {

   public static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;
      
      try {
         Class.forName("org.sqlite.JDBC");
         c = DriverManager.getConnection("jdbc:sqlite:test.db");
         c.setAutoCommit(false);
         System.out.println("已成功打开数据库");

         stmt = c.createStatement();
         String sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
                        "VALUES (1, 'Paul', 32, 'California', 20000.00 );"; 
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
                  "VALUES (2, 'Allen', 25, 'Texas', 15000.00 );"; 
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
                  "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );"; 
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
                  "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );"; 
         stmt.executeUpdate(sql);

         stmt.close();
         c.commit();
         c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName() + ": " + e.getMessage() );
         System.exit(0);
      }
      System.out.println("记录创建成功");
   }
}

5.SELECT 操作

以下Java程序显示了如何从在上面的示例中创建的COMPANY表中获取和显示记录。

import java.sql.*;

public class SQLiteJDBC {

  public static void main( String args[] ) {

   Connection c = null;
   Statement stmt = null;
   try {
      Class.forName("org.sqlite.JDBC");
      c = DriverManager.getConnection("jdbc:sqlite:test.db");
      c.setAutoCommit(false);
      System.out.println("已成功打开数据库");

      stmt = c.createStatement();
      ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
      
      while ( rs.next() ) {
         int id = rs.getInt("id");
         String  name = rs.getString("name");
         int age  = rs.getInt("age");
         String  address = rs.getString("address");
         float salary = rs.getFloat("salary");
         
         System.out.println( "ID = " + id );
         System.out.println( "NAME = " + name );
         System.out.println( "AGE = " + age );
         System.out.println( "ADDRESS = " + address );
         System.out.println( "SALARY = " + salary );
         System.out.println();
      }
      rs.close();
      stmt.close();
      c.close();
   } catch ( Exception e ) {
      System.err.println( e.getClass().getName() + ": " + e.getMessage() );
      System.exit(0);
   }
   System.out.println("已操作成功");
  }
}

6.UPDATE 操作

以下Java代码显示了如何使用UPDATE语句更新任何记录，然后从COMPANY表中获取并显示更新的记录。

import java.sql.*;

public class SQLiteJDBC {

  public static void main( String args[] ) {
  
   Connection c = null;
   Statement stmt = null;
   
   try {
      Class.forName("org.sqlite.JDBC");
      c = DriverManager.getConnection("jdbc:sqlite:test.db");
      c.setAutoCommit(false);
      System.out.println("已成功打开数据库");

      stmt = c.createStatement();
      String sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1;";
      stmt.executeUpdate(sql);
      c.commit();

      ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
      
      while ( rs.next() ) {
         int id = rs.getInt("id");
         String  name = rs.getString("name");
         int age  = rs.getInt("age");
         String  address = rs.getString("address");
         float salary = rs.getFloat("salary");
         
         System.out.println( "ID = " + id );
         System.out.println( "NAME = " + name );
         System.out.println( "AGE = " + age );
         System.out.println( "ADDRESS = " + address );
         System.out.println( "SALARY = " + salary );
         System.out.println();
      }
      rs.close();
      stmt.close();
      c.close();
   } catch ( Exception e ) {
      System.err.println( e.getClass().getName() + ": " + e.getMessage() );
      System.exit(0);
   }
    System.out.println("操作成功完成");
   }
}

7.删除操作

以下Java代码显示了如何使用DELETE语句删除任何记录，然后从COMPANY表中获取并显示其余记录。

import java.sql.*;

public class SQLiteJDBC {

   public static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;
      
      try {
         Class.forName("org.sqlite.JDBC");
         c = DriverManager.getConnection("jdbc:sqlite:test.db");
         c.setAutoCommit(false);
         System.out.println("已成功打开数据库");

         stmt = c.createStatement();
         String sql = "DELETE from COMPANY where ID=2;";
         stmt.executeUpdate(sql);
         c.commit();

         ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
         
         while ( rs.next() ) {
         int id = rs.getInt("id");
         String  name = rs.getString("name");
         int age  = rs.getInt("age");
         String  address = rs.getString("address");
         float salary = rs.getFloat("salary");
         
         System.out.println( "ID = " + id );
         System.out.println( "NAME = " + name );
         System.out.println( "AGE = " + age );
         System.out.println( "ADDRESS = " + address );
         System.out.println( "SALARY = " + salary );
         System.out.println();
      }
      rs.close();
      stmt.close();
      c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName() + ": " + e.getMessage() );
         System.exit(0);
      }
      System.out.println("已操作成功");
   }
}

五.SQLite Python

1.安装

SQLite3 可使用 sqlite3 模块与 Python 进行集成。sqlite3 模块是由 Gerhard Haring 编写的。它提供了一个与 PEP 249 描述的 DB-API 2.0 规范兼容的 SQL 接口。您不需要单独安装该模块，因为 Python 2.5.x 以上版本默认自带了该模块。

为了使用 sqlite3 模块，您首先必须创建一个表示数据库的连接对象，然后您可以有选择地创建光标对象，这将帮助您执行所有的 SQL 语句。

2.连接数据库

下面的 Python 代码显示了如何连接到一个现有的数据库。如果数据库不存在，那么它就会被创建，最后将返回一个数据库对象。

import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully"

您也可以把数据库名称复制为特定的名称 :memory:，这样就会在 RAM 中创建一个数据库。

3.创建表

下面的 Python 代码段将用于在先前创建的数据库中创建一个表：

import sqlite3
conn = sqlite3.connect('test.db')
print ("Opened database successfully")
c = conn.cursor()
c.execute('''CREATE TABLE COMPANY
       (ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL);''')
print ("Table created successfully")
conn.commit()
conn.close()

4.INSERT 操作

下面的 Python 程序显示了如何在上面创建的 COMPANY 表中创建记录

import sqlite3
conn = sqlite3.connect('test.db')
c = conn.cursor()
print ("Opened database successfully")
c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (1, 'Paul', 32, 'California', 20000.00 )")
c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (2, 'Allen', 25, 'Texas', 15000.00 )")
c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )")
c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )")
conn.commit()
print ("Records created successfully")
conn.close()

6.SELECT 操作

下面的 Python 程序显示了如何从前面创建的 COMPANY 表中获取并显示记录

import sqlite3
conn = sqlite3.connect('test.db')
c = conn.cursor()
print ("Opened database successfully")
cursor = c.execute("SELECT id, name, address, salary  from COMPANY")
for row in cursor:
   print "ID = ", row[0]
   print "NAME = ", row[1]
   print "ADDRESS = ", row[2]
   print "SALARY = ", row[3], "\n"
print ("Operation done successfully")
conn.close()

7.UPDATE 操作

下面的 Python 代码显示了如何使用 UPDATE 语句来更新任何记录，然后从 COMPANY 表中获取并显示更新的记录：

import sqlite3
conn = sqlite3.connect('test.db')
c = conn.cursor()
print ("Opened database successfully")
c.execute("UPDATE COMPANY set SALARY = 25000.00 where ID=1")
conn.commit()
print ("Total number of rows updated :", conn.total_changes)
cursor = conn.execute("SELECT id, name, address, salary  from COMPANY")
for row in cursor:
   print ("ID = ", row[0])
   print ("NAME = ", row[1])
   print ("ADDRESS = ", row[2])
   print ("SALARY = ", row[3], "\n")
print （"Operation done successfully"）
conn.close()

8.DELETE 操作

下面的 Python 代码显示了如何使用 DELETE 语句删除任何记录，然后从 COMPANY 表中获取并显示剩余的记录：

import sqlite3
conn = sqlite3.connect('test.db')
c = conn.cursor()
print("Opened database successfully")
c.execute("DELETE from COMPANY where ID=2;")
conn.commit()
print("Total number of rows deleted :", conn.total_changes)
cursor = conn.execute("SELECT id, name, address, salary  from COMPANY")
for row in cursor:
   print ("ID = ", row[0])
   print ("NAME = ", row[1])
   print ("ADDRESS = ", row[2])
   print ("SALARY = ", row[3], "\n")
print ("Operation done successfully")
conn.close()