sqlite详细介绍

------------------------------------------------------------------------------
SQLite简介
------------------------------------------------------------------------------
>> SQLite是一个软件库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。SQLite是一个增长最快的数据库引擎，这是在普及方面的增长，与它的尺寸大小无关。SQLite 源代码不受版权限制。

>> 什么是 SQLite？
SQLite是一个进程内的库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。它是一个零配置的数据库，这意味着与其他数据库一样，您不需要在系统中配置。
就像其他数据库，SQLite 引擎不是一个独立的进程，可以按应用程序需求进行静态或动态连接。SQLite 直接访问其存储文件。

>> 为什么要用 SQLite？
不需要一个单独的服务器进程或操作的系统（无服务器的）。

SQLite 不需要配置，这意味着不需要安装或管理。
一个完整的 SQLite 数据库是存储在一个单一的跨平台的磁盘文件。
SQLite 是非常小的，是轻量级的，完全配置时小于 400KiB，省略可选功能配置时小于250KiB。
SQLite 是自给自足的，这意味着不需要任何外部的依赖。
SQLite 事务是完全兼容 ACID 的，允许从多个进程或线程安全访问。
SQLite 支持 SQL92（SQL2）标准的大多数查询语言的功能。
SQLite 使用 ANSI-C 编写的，并提供了简单和易于使用的 API。
SQLite 可在 UNIX（Linux, Mac OS-X, Android, iOS）和 Windows（Win32, WinCE, WinRT）中运行。

>> SQLite 局限性
在 SQLite 中，SQL92 不支持的特性如下所示：

RIGHT OUTER JOIN 只实现了 LEFT OUTER JOIN。~~只支持left join，right join的效果用left join也能实现，确实没必要多个api
FULL OUTER JOIN 只实现了 LEFT OUTER JOIN。
ALTER TABLE 支持 RENAME TABLE 和 ALTER TABLE 的 ADD COLUMN variants 命令，不支持 DROP COLUMN、ALTER COLUMN、ADD CONSTRAINT。~~只支持添加列，不支持列的修改和删除，汗！那若需要修改表结构，就麻烦了。
Trigger 支持 支持 FOR EACH ROW 触发器，但不支持 FOR EACH STATEMENT 触发器。
VIEWs 在 SQLite 中，视图是只读的。您不可以在视图上执行 DELETE、INSERT 或 UPDATE 语句。~~~SqlServer中来自单一表的视图是可更新的

------------------------------------------------------------------------------
SQLite 命令
------------------------------------------------------------------------------
>> DDL - 数据定义语言
命令 描述
CREATE 创建一个新的表，一个表的视图，或者数据库中的其他对象。
ALTER 修改数据库中的某个已有的数据库对象，比如一个表。
DROP 删除整个表，或者表的视图，或者数据库中的其他对象。

>> DML - 数据操作语言
命令 描述
INSERT 创建一条记录。
UPDATE 修改记录。
DELETE 删除记录。

>> DQL - 数据查询语言
SELECT 从一个或多个表中检索某些记录。
------------------------------------------------------------------------------
SQLite 安装
------------------------------------------------------------------------------
打开 http://www.sqlite.org/download.html 下载 for Windows 预编译的二进制文件。
sqlite-shell-win32-*.zip 和 sqlite-dll-win32-*.zip 压缩文件。
C:\>sqlite，并在此文件夹下解压上面两个压缩文件，将得到 sqlite3.def、sqlite3.dll 和 sqlite3.exe 文件,放到sqlite文件夹下。
添加 C:\>sqlite 到 PATH 环境变量，最后在命令提示符下，使用 sqlite3 命令，将显示如下结果。显示版本和帮助信息。

c:\>sqlite3

------------------------------------------------------------------------------
SQLite 命令
------------------------------------------------------------------------------
本章将向您讲解 SQLite 编程人员所使用的简单却有用的命令。些命令被称为 SQLite 的点命令，这些命令的不同之处在于它们不以分号（;）结束。

查看帮助信息
--进入sqlite上下文环境
c:\>sqlite3
--用点命令
sqlite>.help

.backup ?DB? FILE 备份 DB 数据库（默认是 "main"）到 FILE 文件。
如：.backup test.db

.bail ON|OFF 默认为 OFF。发生错误后停止。

.databases 列出附加数据库的名称和文件。 ~~常用

.dump ?TABLE? 以 SQL 文本格式转储数据库。如果指定了 TABLE 表，则只转储匹配 LIKE 模式的 TABLE 表。 ~~数据表导出为sql

.echo ON|OFF 开启或关闭 echo 命令。 ~~~有的命令(.header off)会显示执行结果，若 .echo off 后，则这些结果就不显示了

.exit 退出 SQLite 提示符。

.explain ON|OFF 开启或关闭适合于 EXPLAIN 的输出模式。如果没有带参数，则为 EXPLAIN on，及开启 EXPLAIN。

.header(s) ON|OFF 开启或关闭头部显示。 ~~~select * from company; 执行类似查询时是否显示表头

.import FILE TABLE 导入来自 FILE 文件的数据到 TABLE 表中。

.indices ?TABLE? 显示所有索引的名称。如果指定了 TABLE 表，则只显示匹配 LIKE 模式的 TABLE 表的索引。

.load FILE ?ENTRY? 加载一个扩展库。

.log FILE|off 开启或关闭日志。FILE 文件可以是 stderr（标准错误）/stdout（标准输出）。

.mode MODE 设置输出模式 MODE=csv/column/html/insert/line/list/tabs/tcl/

.nullvalue STRING 在 NULL 值的地方输出 STRING 字符串。

.output FILENAME 发送输出到 FILENAME 文件。 ~~~把命令(如 .show)的返回的信息输出到文件

.output stdout 发送输出到屏幕。 ~~~把命令(如 .show) 的返回的信息输出到文件

.print STRING... 逐字地输出 STRING 字符串。~~useful

.prompt string 替换标准提示符。~~sqlite上下文的提示符默认为 sqlite>, .prompt sqliteEnv> 修改提示符

.quit 退出 SQLite 提示符。~~~同 .exit

.read FILENAME 执行 FILENAME 文件中的 SQL。

.schema ?TABLE? 显示 CREATE 语句。如果指定了 TABLE 表，则只显示匹配 LIKE 模式的 TABLE 表。~~~useful .schema tablename 显示创建该表的create语句

.separator STRING 改变输出模式和 .import 所使用的分隔符。

.show 显示各种设置的当前值。~~useful

.stats ON|OFF 开启或关闭统计。

.tables ?PATTERN? 列出匹配 LIKE 模式的表的名称。~~useful 显示当前数据库的表

.timeout MS 尝试打开锁定的表 MS 微秒。

sqlite_master 表格
主表中保存数据库表的关键信息，并把它命名为 sqlite_master。如要查看表概要，可按如下操作：

sqlite>.schema sqlite_master

------------------------------------------------------------------------------
SQLite 语法
------------------------------------------------------------------------------
>> 大小写敏感性
有个重要的点值得注意，SQLite 是不区分大小写的，但也有一些命令是大小写敏感的，比如 GLOB 和 glob 在 SQLite 的语句中有不同的含义。

>> 注释
同SQLSERVER --comment content 或 /* ... */
sqlite>.help -- This is a single line comment
------------------------------------------------------------------------------
SQLite 语句
------------------------------------------------------------------------------
所有的 SQLite 语句可以以任何关键字开始，如 SELECT、INSERT、UPDATE、DELETE、ALTER、DROP 等，所有的语句以分号（;）结束。

>> SQLite ANALYZE 语句：
ANALYZE;
or
ANALYZE database_name;
or
ANALYZE database_name.table_name;

>> SQLite AND/OR 子句：
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION-1 {AND|OR} CONDITION-2;

>> SQLite ALTER TABLE 语句：
ALTER TABLE table_name ADD COLUMN column_def...;
SQLite ALTER TABLE 语句（Rename）：
ALTER TABLE table_name RENAME TO new_table_name;

>> SQLite ATTACH DATABASE 语句：
ATTACH DATABASE ‘DatabaseName‘ As ‘Alias-Name‘;

>> SQLite BEGIN TRANSACTION 语句：
BEGIN;
or
BEGIN EXCLUSIVE TRANSACTION;

>> SQLite BETWEEN 子句：
SELECT column1, column2....columnN
FROM table_name
WHERE column_name BETWEEN val-1 AND val-2;

>> SQLite COMMIT 语句：
COMMIT;

>> SQLite CREATE INDEX 语句：
CREATE INDEX index_name
ON table_name ( column_name COLLATE NOCASE );

>> SQLite CREATE UNIQUE INDEX 语句：
CREATE UNIQUE INDEX index_name
ON table_name ( column1, column2,...columnN);

>> SQLite CREATE TABLE 语句：
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns )
);

>> SQLite CREATE TRIGGER 语句：
CREATE TRIGGER database_name.trigger_name
BEFORE INSERT ON table_name FOR EACH ROW
BEGIN
stmt1;
stmt2;
....
END;

>> SQLite CREATE VIEW 语句：
CREATE VIEW database_name.view_name AS
SELECT statement....;

>> SQLite CREATE VIRTUAL TABLE 语句：
CREATE VIRTUAL TABLE database_name.table_name USING weblog( access.log );
or
CREATE VIRTUAL TABLE database_name.table_name USING fts3( );

>> SQLite COUNT 子句：
SELECT COUNT(column_name)
FROM table_name
WHERE CONDITION;

>> SQLite DELETE 语句：
DELETE FROM table_name
WHERE {CONDITION};

>> SQLite DETACH DATABASE 语句：
DETACH DATABASE ‘Alias-Name‘;

>> SQLite DISTINCT 子句：
SELECT DISTINCT column1, column2....columnN
FROM table_name;

>> SQLite DROP INDEX 语句：
DROP INDEX database_name.index_name;

>> SQLite DROP TABLE 语句：
DROP TABLE database_name.table_name;

>> SQLite DROP VIEW 语句：
DROP INDEX database_name.view_name;

>> SQLite DROP TRIGGER 语句：
DROP INDEX database_name.trigger_name;

>> SQLite EXISTS 子句：
SELECT column1, column2....columnN
FROM table_name
WHERE column_name EXISTS (SELECT * FROM table_name );

>> SQLite EXPLAIN 语句：
EXPLAIN INSERT statement...;
or
EXPLAIN QUERY PLAN SELECT statement...;

>> SQLite GLOB 子句：
SELECT column1, column2....columnN
FROM table_name
WHERE column_name GLOB { PATTERN };

>> SQLite GROUP BY 子句：
SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name;

>> SQLite HAVING 子句：
SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name
HAVING (arithematic function condition);

>> SQLite INSERT INTO 语句：
INSERT INTO table_name( column1, column2....columnN)
VALUES ( value1, value2....valueN);

>> SQLite IN 子句：
SELECT column1, column2....columnN
FROM table_name
WHERE column_name IN (val-1, val-2,...val-N);

>> SQLite Like 子句：
SELECT column1, column2....columnN
FROM table_name
WHERE column_name LIKE { PATTERN };

>> SQLite NOT IN 子句：
SELECT column1, column2....columnN
FROM table_name
WHERE column_name NOT IN (val-1, val-2,...val-N);

>> SQLite ORDER BY 子句：
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION
ORDER BY column_name {ASC|DESC};

>> SQLite PRAGMA 语句：
PRAGMA pragma_name;
For example:
PRAGMA page_size;
PRAGMA cache_size = 1024;
PRAGMA table_info(table_name);

>> SQLite RELEASE SAVEPOINT 语句：
RELEASE savepoint_name;

>> SQLite REINDEX 语句：
REINDEX collation_name;
REINDEX database_name.index_name;
REINDEX database_name.table_name;

>> SQLite ROLLBACK 语句：
ROLLBACK;
or
ROLLBACK TO SAVEPOINT savepoint_name;

>> SQLite SAVEPOINT 语句：
SAVEPOINT savepoint_name;

>> SQLite SELECT 语句：
SELECT column1, column2....columnN
FROM table_name;

>> SQLite UPDATE 语句：
UPDATE table_name
SET column1 = value1, column2 = value2....columnN=valueN
[ WHERE CONDITION ];

>> SQLite VACUUM 语句：
VACUUM;

>> SQLite WHERE 子句：
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION;
------------------------------------------------------------------------------
SQLite 数据类型
------------------------------------------------------------------------------
SQLite 中的每一列，每个变量和表达式都有相关的数据类型。
您可以在创建表的同时使用这些数据类型。SQLite 使用一个更普遍的动态类型系统。在 SQLite 中，值的数据类型与值本身是相关的，而不是与它的容器相关。

>> SQLite 存储类
每个存储在 SQLite 数据库中的值都具有以下存储类之一：
NULL 值是一个 NULL 值。
INTEGER 值是一个带符号的整数，根据值的大小存储在 1、2、3、4、6 或 8 字节中。
REAL 值是一个浮点值，存储为 8 字节的 IEEE 浮点数字。
TEXT 值是一个文本字符串，使用数据库编码（UTF-8、UTF-16BE 或 UTF-16LE）存储。
BLOB 值是一个 blob 数据，完全根据它的输入存储。
SQLite 的存储类稍微比数据类型更普遍。INTEGER 存储类，例如，包含 6 种不同的不同长度的整数数据类型。

>> SQLite Affinity 类型
SQLite 支持列上的类型 affinity 概念。任何列仍然可以存储任何类型的数据，但列的首选存储类是它的 affinity。在 SQLite3 数据库中，每个表的列分配为以下类型的 affinity 之一：

Affinity 描述
TEXT 该列使用存储类 NULL、TEXT 或 BLOB 存储所有数据。
NUMERIC 该列可以包含使用所有五个存储类的值。
INTEGER 与带有 NUMERIC affinity 的列相同，在 CAST 表达式中带有异常。
REAL 与带有 NUMERIC affinity 的列相似，不同的是，它会强制把整数值转换为浮点表示。
NONE 带有 affinity NONE 的列，不会优先使用哪个存储类，也不会尝试把数据从一个存储类强制转换为另一个存储类。

>> SQLite Affinity 及类型名称
下表列出了当创建 SQLite3 表时可使用的各种数据类型名称，同时也显示了相应的应用 Affinity：
TINYINT,SMALLINT,INT,INTEGER,MEDIUMINT,BIGINT,UNSIGNED BIG INT,INT2,INT8 --> 对应Affinity INTEGER
CHARACTER(20),VARCHAR(255),VARYING CHARACTER(255),NCHAR(55),NATIVE CHARACTER(70),NVARCHAR(100),TEXT,CLOB --> 对应Affinity TEXT
BLOB,no datatype specified --> 对应Affinity NONE
REAL,DOUBLE,DOUBLE PRECISION,FLOAT --> 对应Affinity REAL
NUMERIC,DECIMAL(10,5),BOOLEAN,DATE,DATETIME --> 对应Affinity NUMERIC

>> Boolean 数据类型
SQLite 没有单独的 Boolean 存储类。相反，布尔值被存储为整数 0（false）和 1（true）。

>> Date 与 Time 数据类型
SQLite 没有一个单独的用于存储日期和/或时间的存储类，但 SQLite 能够把日期和时间存储为 TEXT、REAL 或 INTEGER 值。
TEXT 格式为 "YYYY-MM-DD HH:MM:SS.SSS" 的日期。
REAL 从公元前 4714 年 11 月 24 日格林尼治时间的正午开始算起的天数。
INTEGER 从 1970-01-01 00:00:00 UTC 算起的秒数。

您可以以任何上述格式来存储日期和时间，并且可以使用内置的日期和时间函数来自由转换不同格式。
------------------------------------------------------------------------------
SQLite 创建数据库
------------------------------------------------------------------------------
您不需要任何特殊的权限即可创建一个数据库。~~~sqlite3命令创建数据库

--在当前目录下创建了 mytest.db 的数据库文件，并进入sqlite上下文。
D:\sqlite\src>sqlite3 mytest.db
SQLite version 3.8.7.1 2014-10-29 13:59:56
Enter ".help" for usage hints.
sqlite> .databases
seq name file
--- --------------- ------------------------------------
0 main D:\sqlite\src\mytest.db

>> ~~数据库的备份和恢复
.dump 命令
您可以在命令提示符中使用 SQLite .dump 点命令来导出完整的数据库在一个文本文件中，如下所示：

$sqlite3 testDB.db .dump > testDB.sql
上面的命令将转换整个 testDB.db 数据库的内容到 SQLite 的语句中，并将其转储到 ASCII 文本文件 testDB.sql 中。您可以通过简单的方式从生成的 testDB.sql 恢复，如下所示：

$sqlite3 testDB.db < testDB.sql

>> SQLite 附加数据库 ~~~类似SQLSERVER的 use dbname

假设这样一种情况，当在同一时间有多个数据库可用，您想使用其中的任何一个。SQLite 的 ATTACH DTABASE 语句是用来选择一个特定的数据库，使用该命令后，所有的 SQLite 语句将在附加的数据库下执行。

语法
SQLite 的 ATTACH DATABASE 语句的基本语法如下：

ATTACH DATABASE ‘DatabaseName‘ As ‘Alias-Name‘;
如果数据库尚未被创建，上面的命令将创建一个数据库，如果数据库已存在，则把数据库文件名称与逻辑数据库 ‘Alias-Name‘ 绑定在一起。
如：
sqlite> ATTACH DATABASE ‘testDB.db‘ as ‘TEST‘;
sqlite> DETACH DATABASE ‘TEST‘;

数据库名称 main 和 temp 被保留用于主数据库和存储临时表及其他临时数据对象的数据库。这两个数据库名称可用于每个数据库连接，且不应该被用于附加，否则将得到一个警告消息，如下所示：

sqlite> ATTACH DATABASE ‘testDB.db‘ as ‘TEMP‘;
Error: database TEMP is already in use
sqlite> ATTACH DATABASE ‘testDB.db‘ as ‘main‘;
Error: database TEMP is already in use

>> SQLite 分离数据库

SQLite的 DETACH DTABASE 语句是用来把命名数据库从一个数据库连接分离和游离出来，连接是之前使用 ATTACH 语句附加的。如果同一个数据库文件已经被附加上多个别名，DETACH 命令将只断开给定名称的连接，而其余的仍然有效。您无法分离 main 或 temp 数据库。

DETACH DATABASE ‘Alias-Name‘;
------------------------------------------------------------------------------
SQLite 创建表
------------------------------------------------------------------------------
CREATE TABLE database_name.table_name(
column1 datatype PRIMARY KEY(one or more columns),
column2 datatype,
column3 datatype,
.....
columnN datatype,
);

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

sqlite>.tables --显示数据库的表
COMPANY DEPARTMENT

sqlite>.schema COMPANY --查看表结构
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);

------------------------------------------------------------------------------
SQLite 删除表
------------------------------------------------------------------------------
SQLite 的 DROP TABLE 语句用来删除表定义及其所有相关数据、索引、触发器、约束和该表的权限规范。
DROP TABLE database_name.table_name;

>> SQLite Insert 语句

SQLite 的 INSERT INTO 语句用于向数据库的某个表中添加新的数据行。
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)]
VALUES (value1, value2, value3,...valueN);

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

使用一个表来填充另一个表 ~~~first_table_name这个表需要已存在
INSERT INTO first_table_name [(column1, column2, ... columnN)]
SELECT column1, column2, ...columnN
FROM second_table_name
[WHERE condition];

>> SQLite Select 语句
SELECT column1, column2, columnN FROM table_name;
sqlite>.header on
sqlite>.mode column
sqlite> SELECT * FROM COMPANY;

设置输出列的宽度
有时，由于要显示的列的默认宽度导致 .mode column，这种情况下，输出被截断。此时，您可以使用 .width num, num.... 命令设置显示列的宽度，如下所示：

sqlite>.width 10, 20, 10
sqlite>SELECT * FROM COMPANY;

>> Schema 信息
因为所有的点命令只在 SQLite 提示符中可用，所以当您进行带有 SQLite 的编程时，您要使用下面的带有 sqlite_master 表的 SELECT 语句来列出所有在数据库中创建的表：

sqlite> SELECT tbl_name FROM sqlite_master WHERE type = ‘table‘; ~~~等效于sqlite提示符下 .tables

您可以列出关于 COMPANY 表的完整信息，如下所示：
sqlite> SELECT sql FROM sqlite_master WHERE type = ‘table‘ AND tbl_name = ‘COMPANY‘; ~~~等价于 .schema company
------------------------------------------------------------------------------
SQLite 运算符
------------------------------------------------------------------------------
算术运算符 + - * / %
sqlite> select 10 + 20;

比较运算符 > >= < <= = <> == != !> !<
sqlite> SELECT * FROM COMPANY WHERE SALARY > 50000;

逻辑运算符 and, between .. and .. , exists(..), in, not in, like, GLOB(相当于大小写敏感的like), not , or, is, is null, ||(连接两个不同的字符串，得到一个新的字符串), unique(搜索指定表中的每一行，确保唯一性（无重复）)
select ‘welcome‘||‘here‘ ~~字符串连接

位运算符 & | ~ >> <<
------------------------------------------------------------------------------
SQLite 表达式
------------------------------------------------------------------------------
表达式是一个或多个值、运算符和计算值的SQL函数的组合。

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

sqlite> SELECT * FROM COMPANY WHERE SALARY = 10000; ~~~SALARY = 10000; 逻辑表达式
sqlite> SELECT (15 + 6) AS ADDITION ~~(15 + 6)数值表达式
sqlite> SELECT COUNT(*) AS "RECORDS" FROM COMPANY; ~~COUNT(*) 函数表达式
sqlite> SELECT CURRENT_TIMESTAMP; ~~日期表达式

>> SQLite Where 子句

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

>> SQLite Update 语句

SQLite 的 UPDATE 查询用于修改表中已有的记录。
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

>> SQLite Delete 语句

SQLite 的 DELETE 查询用于删除表中已有的记录。
DELETE FROM table_name
WHERE [condition];

>> SQLite Like 子句

SQLite 的 LIKE 运算符指定匹配模式。SQLite只是通配符 % _

>> SQLite Glob 子句 作用和 like一样，与 LIKE 运算符不同的是，GLOB 是大小写敏感的，对于下面的通配符，它遵循 UNIX 的语法。通配符为 * ?

>> SQLite Limit 子句
SQLite 的 LIMIT 子句用于限制由 SELECT 语句返回的数据数量。
SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows]

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

>> SQLite Order By
SQLite 的 ORDER BY 子句是用来基于一个或多个列按升序或降序顺序排列数据。
SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

>> SQLite Group By
SQLite 的 GROUP BY 子句用于与 SELECT 语句一起使用，来对相同的数据进行分组。
SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN

>> SQLite Having 子句
HAVING 子句允许指定条件来过滤将出现在最终结果中的分组结果。
SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) < 2;

>> SQLite Distinct 关键字
SQLite 的 DISTINCT 关键字与 SELECT 语句一起使用，来消除所有重复的记录，并只获取唯一一次记录。
SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]
------------------------------------------------------------------------------
SQLite PRAGMA
------------------------------------------------------------------------------
SQLite 的 PRAGMA 命令是一个特殊的命令，可以用在 SQLite 环境内控制各种环境变量和状态标志。一个 PRAGMA 值可以被读取，也可以根据需求进行设置。

要查询当前的 PRAGMA 值，只需要提供该 pragma 的名字：
PRAGMA pragma_name;

要为 PRAGMA 设置一个新的值，语法如下：
PRAGMA pragma_name = value;

auto_vacuum Pragma 获取或设置 auto-vacuum 模式。语法如下：

PRAGMA [database.]auto_vacuum;
PRAGMA [database.]auto_vacuum = mode;
PRAGMA [database.]cache_size;
PRAGMA case_sensitive_like = [true|false];
PRAGMA count_changes;
PRAGMA count_changes = [true|false];
PRAGMA database_list;
PRAGMA encoding;
PRAGMA encoding = format;
PRAGMA [database.]freelist_count;
PRAGMA [database.]index_info( index_name );
PRAGMA [database.]index_list( table_name );
PRAGMA parser_trace = [true|false];
PRAGMA [database.]cache_size = pages;
------------------------------------------------------------------------------
SQLite 约束
------------------------------------------------------------------------------
约束是在表的数据列上强制执行的规则。这些是用来限制可以插入到表中的数据类型。这确保了数据库中数据的准确性和可靠性。
约束可以是列级或表级。列级约束仅适用于列，表级约束被应用到整个表。

以下是在 SQLite 中常用的约束。
NOT NULL 约束：确保某列不能有 NULL 值。
DEFAULT 约束：当某列没有指定值时，为该列提供默认值。
UNIQUE 约束：确保某列中的所有值是不同的。
PRIMARY Key 约束：唯一标识数据库表中的各行/记录。
CHECK 约束：CHECK 约束确保某列中的所有值满足一定条件。

>> NOT NULL 约束
默认情况下，列可以保存 NULL 值。如果您不想某列有 NULL 值，那么需要在该列上定义此约束，指定在该列上不允许 NULL 值。
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 语句没有提供一个特定的值时，为列提供一个默认值。
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 表中，例如，您可能要防止两个或两个以上的人具有相同的年龄。
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。
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，则记录违反了约束，且不能输入到表。
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

>> 交叉连接 - CROSS JOIN ~~~卡笛尔积
交叉连接（CROSS JOIN）把第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有 x 和 y 列，则结果表有 x+y 列。由于交叉连接（CROSS JOIN）有可能产生非常大的表，使用时必须谨慎，只在适当的时候使用它们。
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;

>> 内连接 - INNER JOIN ~~~卡笛尔积后 根据连接条件筛选记录
内连接（INNER JOIN）根据连接谓词结合两个表（table1 和 table2）的列值来创建一个新的结果表。查询会把 table1 中的每一行与 table2 中的每一行进行比较，找到所有满足连接谓词的行的匹配对。当满足连接谓词时，A 和 B 行的每个匹配对的列值会合并成一个结果行。
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;

>> 外连接 - OUTER JOIN ~~~相对 inner join而言 较宽松的筛选
外连接（OUTER JOIN）是内连接（INNER JOIN）的扩展。虽然 SQL 标准定义了三种类型的外连接：LEFT、RIGHT、FULL，但 SQLite 只支持 左外连接（LEFT OUTER JOIN）。
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;
------------------------------------------------------------------------------
SQLite Unions 子句
------------------------------------------------------------------------------
SQLite的 UNION 子句/运算符用于合并两个或多个 SELECT 语句的结果，不返回任何重复的行。

为了使用 UNION，每个 SELECT 被选择的列数必须是相同的，相同数目的列表达式，相同的数据类型，并确保它们有相同的顺序，但它们不必具有相同的长度。

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

UNION

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

>> UNION ALL 子句
UNION ALL 运算符用于结合两个 SELECT 语句的结果，包括重复行。
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION ALL

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
------------------------------------------------------------------------------
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() 可用于触发器程序内抛出异常。

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;

sqlite> CREATE TRIGGER audit_log AFTER INSERT
ON COMPANY
BEGIN
INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime(‘now‘));
END;

>> 列出触发器（TRIGGERS）
sqlite> SELECT name FROM sqlite_master
WHERE type = ‘trigger‘;

>> 删除触发器（TRIGGERS）
sqlite> DROP TRIGGER trigger_name;

------------------------------------------------------------------------------
SQLite 索引（Index）
------------------------------------------------------------------------------
索引（Index）是一种特殊的查找表，数据库搜索引擎用来加快数据检索。简单地说，索引是一个指向表中数据的指针。一个数据库中的索引与一本书后边的索引是非常相似的。
例如，如果您想在一本讨论某个话题的书中引用所有页面，您首先需要指向索引，索引按字母顺序列出了所有主题，然后指向一个或多个特定的页码。
索引有助于加快 SELECT 查询和 WHERE 子句，但它会减慢使用 UPDATE 和 INSERT 语句时的数据输入。索引可以创建或删除，但不会影响数据。
使用 CREATE INDEX 语句创建索引，它允许命名索引，指定表及要索引的一列或多列，并指示索引是升序排列还是降序排列。
索引也可以是唯一的，与 UNIQUE 约束类似，在列上或列组合上防止重复条目。

>> CREATE INDEX 命令 （组合索引 唯一索引 自动创建的隐式索引 如primary key)
CREATE INDEX index_name
ON table_name (column_name);

CREATE UNIQUE INDEX index_name
ON table_name (column_name);

CREATE INDEX index_name
ON table_name (column1_name, column2_name);

sqlite> CREATE INDEX salary_index ON COMPANY (salary);

>> 删除索引
sqlite> DROP INDEX salary_index;

什么情况下要避免使用索引？
虽然索引的目的在于提高数据库的性能，但这里有几个情况需要避免使用索引。使用索引时，应重新考虑下列准则：

索引不应该使用在较小的表上。
索引不应该使用在有频繁的大批量的更新或插入操作的表上。
索引不应该使用在含有大量的 NULL 值的列上。
索引不应该使用在频繁操作的列上。