【SQLite】SQLite教程笔记（一）

SQLite命令

DDL – 数据定义语言

命令	描述
CREATE	创建一个新的表、一个新的视图或者数据库中的其他对象。
ALTER	修改数据库中的某个已有数据库对象，比如一个表
DROP	删除整个表、表的试图或数据库中的其他对象

DML – 数据操作语言

命令	描述
INSERT	创建一条记录
UPDATE	修改记录
DELETE	删除记录

DQL – 数据查询语言

命令	描述
SELECT	从一个或多个表中检索某些记录。

SQLite语法

• SQLite是不区分大小写的，但是有一些命令是大小写敏感的，比如GLOB和glob在SQLite的语句中有不同的含义。
• 注释：SQLite的注释以两个连续的“-”字符开始，并扩展至下一个换行符或直到输入结束，以先到者为准。【也可以使用C风格的注释/**/】SQLite的注释可以跨越多行。

SQLite语句

• 所有的SQLite语句可以以任何关键字开始，如DELECT、INSERT、UPDATE、ALTER、DROP等，所有的语句以分号“;”结束。

ANALYZE语句

SQLite中的ANALYZE命令用于分析数据表和索引表中的数据，并将统计结果存放于SQLite的内部系统表中，以便于查询优化器可以根据分析后的统计数据选择最优的查询执行路径，从而提高整个查询的效率

ANALYZE;	--如果ANALYZE命令之后没有指定任何参数，则分析当前连接中所有Attached数据库中的表和索引。
ANALYSE 数据库;	--如果指定数据库作为ANALYZE的参数，那么该数据库下的所有表和索引都将被分析并生成统计数据
ANALYZE main.table1;	--如果指定了数据库中的某个表或索引作为ANALYZE的参数，那么该表和其所有关联的索引都将被分析

AND/OR子句

SELECT Column1, Column2, ..., ColumnN
FROM table_name
WHERE CONDITION_1 {AND|OR} CONDITION_2;

ALTER TABLE语句

修改表的属性 – 添加列：

ALTER TABLE table_name ADD COLUMN column_def...;

重命名表：

ALTER TABLE table_name RENAME TO new_table_name;

ATTACH DATABASE语句

如果数据库尚未被创建，那么该语句会创建一个数据库；如果数据库已经存在，则把数据库文件名称与逻辑数据库‘UserName’绑定在一起。

ATTACH DATABASE 'DatabaseName' As 'UserName';

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

BEGIN TRANSACTION语句

BEGIN;
或
BEGIN EXCLUSIVE TRANSACTION;

BETWEEN子句

从table_name表中查找column_name对应的值在val_1和val_2之间的项

SELECT Column1, Column2, ..., ColumnN
FROM table_name
WHERE column_name BETWEEN val_1 AND val_2;

COMMIT语句

COMMIT;

CREATE INDEX语句

CREATE INDEX index_name
ON table_name ( column_name COLLATE NOCASE );

CREATE TABLE语句

该语句用于在任何给定的数据库创建一个新表。
创建基本表，涉及到命名表、定义列及每一列的数据类型。

CREATE TABLE table_name(
	column1 datatype,
	column2 datatype,
	......
	columnN datatype,
	PRIMARY KEY( one or more columns)
);

示例：

CREATE TABLE company(
	ID INT PRIMARY KEY NOT NULL,		--ID设为主键，NOT NULL约束该字段不能为空
	NAME TEXT NOT NULL,
	AGE INT NOT NULL,
	ADDRESS CHAR(50),
	SALARY REAL
);

CREATE TRIGGER语句

创建触发器

CREATE TRIGGER database_name.trigger_name 
BEFORE INSERT ON table_name FOR EACH ROW
BEGIN 
   stmt1; 
   stmt2;
   ....
END;

CREATE VIEW 语句

创建视图

CREATE VIEW database_name.view_name  AS
SELECT statement....;

CREATE VIRTUAL TABLE语句

该语句用来删除表定义及其所有相关数据、索引、触发器、约束和该表的权限规范。
注意，一旦一个表被删除，表中所有信息也将永远丢失。

CREATE VIRTUAL TABLE database_name.table_name USING weblog( access.log );
or
CREATE VIRTUAL TABLE database_name.table_name USING fts3( );

COUNT子句

用于计算个数

SELECT COUNT(Column_name)
FROM table_name
WHERE CONDITION;

DELETE语句

SQLite 的 DELETE 查询用于删除表中已有的记录。可以使用带有 WHERE 子句的 DELETE 查询来删除选定行，否则所有的记录都会被删除。

DELETE FROM table_name
WHERE {CONDITION};

DETACH DATABASE语句

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

DETACH DATABASE 'UserName';

UserName与ATTACH DATABASE语句中的名字一致。

DISTINCT 子句

SQLite 的 DISTINCT 关键字与 SELECT 语句一起使用，来消除所有重复的记录，并只获取唯一一次记录。

有可能出现一种情况，在一个表中有多个重复的记录。当提取这样的记录时，DISTINCT 关键字就显得特别有意义，它只获取唯一一次记录，而不是获取重复记录。

SELECT DISTINCT column1, column2....columnN
FROM   table_name;

DROP INDEX 语句

DROP INDEX database_name.index_name;

DROP TABLE 语句

删除表

DROP TABLE database_name.table_name;

DROP VIEW 语句

删除视图

DROP VIEW view_name;

DROP TRIGGER 语句

删除触发器

DROP TRIGGER trigger_name

EXISTS 子句

• 不相关子查询：子查询的查询条件不依赖于父查询的称为不相关子查询。
• 相关子查询：子查询的查询条件依赖于外层父查询的某个属性值的称为相关子查询，带EXISTS 的子查询就是相关子查询。
• EXISTS表示存在量词：带有EXISTS的子查询不返回任何记录的数据，只返回逻辑值“True”或“False”(非空时为true，为空返回false)。

SELECT column1, column2....columnN
FROM   table_name
WHERE  column_name EXISTS (SELECT * FROM   table_name );

EXPLAIN 语句

在SQLite语句之前，可以使用"EXPLAIN"关键字或"EXPLAIN QUERY PLAN"短语，用于描述表的细节。
如果省略了EXPLAIN关键字或短语，任何的修改都会引起SQLite语句的查询行为，并返回有关SQLite语句如何操作的信息。

• 来自EXPLAIN和EXPLAIN QUERY PLAN的输出只用于交互分析和排除故障；
• 输出格式的细节可能会随着SQLite版本的不同而有所变化；
• 应用程序不应该使用EXPLAIN或EXPLAIN QUERY PLAN，因为其确切的行为是可变的且只有部分会被记录

EXPLAIN INSERT statement...;
or 
EXPLAIN QUERY PLAN SELECT statement...;

GLOB 子句

SQLite 的 GLOB 运算符是用来匹配通配符指定模式的文本值。如果搜索表达式与模式表达式匹配，GLOB 运算符将返回真（true），也就是 1。与 LIKE 运算符不同的是，GLOB 是大小写敏感的，对于下面的通配符，它遵循 UNIX 的语法。

• 星号 （*）
• 问号 （?）

星号（*）代表零个、一个或多个数字或字符。问号（?）代表一个单一的数字或字符。这些符号可以被组合使用。

SELECT column1, column2....columnN
FROM   table_name
WHERE  column_name GLOB { PATTERN };

GROUP BY 子句

SQLite 的 GROUP BY 子句用于与 SELECT 语句一起使用，来对相同的数据进行分组。

在 SELECT 语句中，GROUP BY 子句放在 WHERE 子句之后，放在 ORDER BY 子句之前。

SELECT SUM(column_name)
FROM   table_name
WHERE  CONDITION
GROUP BY column_name;

HAVING 子句

HAVING 子句允许指定条件来过滤将出现在最终结果中的分组结果。

WHERE 子句在所选列上设置条件，而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件。

在一个查询中，HAVING 子句必须放在 GROUP BY 子句之后，必须放在 ORDER BY 子句之前。

SELECT SUM(column_name)
FROM   table_name
WHERE  CONDITION
GROUP BY column_name
HAVING (arithematic function condition);

INSERT INTO 语句

该语句用于向数据库中的某个表添加新的数据行。

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

在这里，column1, column2,…columnN 是要插入数据的表中的列的名称。

如果要为表中的所有列添加值，您也可以不需要在 SQLite 查询中指定列名称。但要确保值的顺序与列在表中的顺序一致。SQLite 的 INSERT INTO 语法如下：

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

可以通过在一个有一组字段的表上使用 select 语句，填充数据到另一个表中。下面是语法：

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

Like 子句

SQLite 的 LIKE 运算符是用来匹配通配符指定模式的文本值。如果搜索表达式与模式表达式匹配，LIKE 运算符将返回真（true），也就是 1。这里有两个通配符与 LIKE 运算符一起使用：

• 百分号 （%）
• 下划线 （_）

百分号（%）代表零个、一个或多个数字或字符。下划线（_）代表一个单一的数字或字符。这些符号可以被组合使用。

SELECT column1, column2....columnN
FROM   table_name
WHERE  column_name LIKE { PATTERN };

Limit子句

带有 LIMIT 子句的 SELECT 语句的基本语法如下：

SELECT column1, column2, columnN 
FROM table_name
LIMIT [no of rows];

下面是 LIMIT 子句与 OFFSET 子句一起使用时的语法：

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

SQLite 引擎将返回从下一行开始直到给定的 OFFSET 为止的所有行。

NOT IN 子句

不满足匹配条件，同逻辑非类似

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

ORDER BY 子句

ORDER BY 子句是用来基于一个或多个列按升序或降序顺序排列数据。

SELECT column1, column2....columnN
FROM   table_name
WHERE  CONDITION
ORDER BY column_name {ASC|DESC};

PRAGMA 语句

SQLite 的 PRAGMA 命令是一个特殊的命令，可以用在 SQLite 环境内控制各种环境变量和状态标志。一个 PRAGMA 值可以被读取，也可以根据需求进行设置。

PRAGMA pragma_name;

For example:

PRAGMA page_size;		--查找page_size的值
PRAGMA cache_size = 1024;  --设置cache_size 为指定的值
PRAGMA table_info(table_name);

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 进行覆盖。

RELEASE SAVEPOINT 语句

RELEASE savepoint_name;

REINDEX 语句

REINDEX collation_name;
REINDEX database_name.index_name;
REINDEX database_name.table_name;

ROLLBACK 语句

ROLLBACK;
or
ROLLBACK TO SAVEPOINT savepoint_name;

SAVEPOINT 语句

SAVEPOINT savepoint_name;

SELECT 语句

SELECT column1, column2....columnN
FROM   table_name;

使用下面的带有 sqlite_master 表的 SELECT 语句来列出所有在数据库中创建的表：

SELECT tbl_name FROM sqlite_master WHERE type = 'table';

可以列出关于 COMPANY 表的完整信息，如下所示：

SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'COMPANY';

假设在 testDB.db 中已经存在唯一的 COMPANY 表，则将产生以下结果：

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

UPDATE 语句

SQLite 的 UPDATE 查询用于修改表中已有的记录。可以使用带有 WHERE 子句的 UPDATE 查询来更新选定行，否则所有的行都会被更新。

UPDATE table_name
SET column1 = value1, column2 = value2....columnN=valueN
[ WHERE  CONDITION ];

VACUUM 语句

VACUUM;

WHERE 子句

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

如果满足给定的条件，即为真（true）时，则从表中返回特定的值。您可以使用 WHERE 子句来过滤记录，只获取需要的记录。

WHERE 子句不仅可用在 SELECT 语句中，它也可用在 UPDATE、DELETE 语句中，等等。

SELECT column1, column2....columnN
FROM   table_name
WHERE  CONDITION;

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支持列的亲和类型概念。任何列仍然可以存储任何类型的数据，当数据插入时，该字段的数据将会优先采用亲缘类型作为该值的存储方式。SQLite目前的版本支持以下五种亲缘类型：

亲和类型	描述
TEXT	数值型数据在被插入之前，需要先被转换为文本格式，之后再插入到目标字段中。
NUMERIC	当文本数据被插入到亲缘性为NUMERIC的字段中时，如果转换操作不会导致数据信息丢失以及完全可逆，那么SQLite就会将该文本数据转换为INTEGER或REAL类型的数据，如果转换失败，SQLite仍会以TEXT方式存储该数据。对于NULL或BLOB类型的新数据，SQLite将不做任何转换，直接以NULL或BLOB的方式存储该数据。需要额外说明的是，对于浮点格式的常量文本，如"30000.0"，如果该值可以转换为INTEGER同时又不会丢失数值信息，那么SQLite就会将其转换为INTEGER的存储方式。
INTEGER	对于亲缘类型为INTEGER的字段，其规则等同于NUMERIC，唯一差别是在执行CAST表达式时。
REAL	其规则基本等同于NUMERIC，唯一的差别是不会将"30000.0"这样的文本数据转换为INTEGER存储方式。
NONE	不做任何的转换，直接以该数据所属的数据类型进行存储。

下表列出了当创建 SQLite3 表时可使用的各种数据类型名称，同时也显示了相应的亲和类型：

数据类型	亲和类型
INT、INTEGER、TINYINT、SMALLINT、MEDIUMINT、BIGINT、UNSIGNED BIG INT、INT2、INT8	INTEGER
CHARACTER(20)、VARCHAR(255)、VARYING CHARACTER(255)、NCHAR(55)、NATIVE CHARACTER(70)、NVARCHAR(100)、TEXT、CLOB	TEXT
BLOB、no datatype specified	NONE
REAL、DOUBLE、DOUBLE PRECISION、FLOAT	REAL
NUMERIC、DECIMAL(10,5)、BOOLEAN、DATE、DATETIME	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运算符

SQLite算数运算符

支持： + - * / %

SQLite比较运算符

运算符	描述	实例[假设变量a=10,b=20]
==	检查两个操作数的值是否相等	(a==b)不为真
=	检查两个操作数的值是否相等，如果相等则条件为真。	(a = b) 不为真。
!= 检查两个操作数的值是否相等，如果不相等则条件为真。	(a != b) 为真。
<>	检查两个操作数的值是否相等，如果不相等则条件为真。	(a <> b) 为真。
>	检查左操作数的值是否大于右操作数的值，如果是则条件为真。	(a > b) 不为真。
< 、检查左操作数的值是否小于右操作数的值，如果是则条件为真。	(a < b) 为真。
>=	检查左操作数的值是否大于等于右操作数的值，如果是则条件为真。	(a >= b) 不为真。
<=	检查左操作数的值是否小于等于右操作数的值，如果是则条件为真。	(a <= b) 为真。
!<	检查左操作数的值是否不小于右操作数的值，如果是则条件为真。	(a !< b) 为假。
!>	检查左操作数的值是否不大于右操作数的值，如果是则条件为真。	(a !> b) 为真。

SQLite逻辑运算符

运算符	描述
AND	AND 运算符允许在一个 SQL 语句的 WHERE 子句中的多个条件的存在。
BETWEEN	BETWEEN 运算符用于在给定最小值和最大值范围内的一系列值中搜索值。
EXISTS	EXISTS 运算符用于在满足一定条件的指定表中搜索行的存在。
IN	IN 运算符用于把某个值与一系列指定列表的值进行比较。
NOT IN	IN 运算符的对立面，用于把某个值与不在一系列指定列表的值进行比较。
LIKE	LIKE 运算符用于把某个值与使用通配符运算符的相似值进行比较。
GLOB	GLOB 运算符用于把某个值与使用通配符运算符的相似值进行比较。GLOB 与 LIKE 不同之处在于，它是大小写敏感的。
NOT	NOT 运算符是所用的逻辑运算符的对立面。比如 NOT EXISTS、NOT BETWEEN、NOT IN，等等。它是否定运算符。
OR	OR 运算符用于结合一个 SQL 语句的 WHERE 子句中的多个条件。
IS NULL	NULL 运算符用于把某个值与 NULL 值进行比较。
IS	IS 运算符与 = 相似。
IS NOT	IS NOT 运算符与 != 相似。
||	连接两个不同的字符串，得到一个新的字符串。
UNIQUE	UNIQUE 运算符搜索指定表中的每一行，确保唯一性（无重复）。

SQLite位运算

位运算符作用于位，并逐位执行操作。真值表 & 和 | 如下：

p	q	p & q	p|q
0	0	0	0
0	1	0	1
1	1	1	1
1	0	0	1

运算符	描述	实例[假设变量 A=60，变量 B=13]
&	如果同时存在于两个操作数中，二进制 AND 运算符复制一位到结果中。	(A & B) 将得到 12，即为 0000 1100
|	如果存在于任一操作数中，二进制 OR 运算符复制一位到结果中。	(A | B) 将得到 61，即为 0011 1101
~	二进制补码运算符是一元运算符，具有"翻转"位效应，即0变成1，1变成0。 (~A ) 将得到 -61，即为 1100 0011，一个有符号二进制数的补码形式。
<<	二进制左移运算符。左操作数的值向左移动右操作数指定的位数。	A << 2 将得到 240，即为 1111 0000
>>	二进制右移运算符。左操作数的值向右移动右操作数指定的位数。	A >> 2 将得到 15，即为 0000 1111

SQLite表达式

SQLite日期表达式

SELECT CURRENT_TIMESTAMP;		--返回当前系统日期和时间值

SQLite约束

约束是在表的数据列上强制执行的规则。这些是用来限制可以插入到表中的数据类型。这确保了数据库中数据的准确性和可靠性。

约束可以是列级或表级。列级约束仅适用于列，表级约束被应用到整个表。

以下是在 SQLite 中常用的约束。

NOT NULL 约束

默认情况下，列可以保存 NULL 值。如果您不想某列有 NULL 值，那么需要在该列上定义此约束，指定在该列上不允许 NULL 值。

NULL 与没有数据是不一样的，它代表着未知的数据。

DEFAULT 约束

DEFAULT 约束在 INSERT INTO 语句没有提供一个特定的值时，为列提供一个默认值。

UNIQUE 约束

UNIQUE 约束防止在一个特定的列存在两个记录具有相同的值。

PRIMARY Key 约束

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

我们使用主键来引用表中的行。可通过把主键设置为其他表的外键，来创建表之间的关系。由于"长期存在编码监督"，在 SQLite 中，主键可以是 NULL，这是与其他数据库不同的地方。

主键是表中的一个字段，唯一标识数据库表中的各行/记录。主键必须包含唯一值。主键列不能有 NULL 值。

一个表只能有一个主键，它可以由一个或多个字段组成。当多个字段作为主键，它们被称为复合键。

如果一个表在任何字段上定义了一个主键，那么在这些字段上不能有两个记录具有相同的值。

CHECK 约束

CHECK 约束启用输入一条记录要检查值的条件。如果条件值为 false，则记录违反了约束，且不能输入到表。

删除约束

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