Jade Dungeon

Sqlite

安装
- 查看帮助
- Sqlite数据库的sqlite_master表
基础
- Sqlite语法规则
- Sqlite数据类型
数据库操作
数据表操作
CRUD操作
SQLite运算符
SQLite表达式
SQLite约束
表连接 Join
Unions子句
- UNION ALL子句
触发器（Trigger）
- 列出触发器（TRIGGERS）
- 删除触发器（TRIGGERS）
SQLite索引（Index）
SQLite视图（View）
- 创建视图
- 删除视图
SQLite事务（Transaction）
- 事务控制
SQLite子查询
SQLite常用函数
SQLite Explain（解释）

安装

sudo apt-get install sqlite3

查看帮助

$ sqlite3

SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

.backup ?DB? FILE ：备份DB数据库（默认是”main”）到FILE文件。
.bail ON/OFF ：发生错误后停止。默认为OFF。
.databases ：列出附加数据库的名称和文件。
.dump ?TABLE? ：以SQL文本格式转储数据库。如果指定了TABLE表，则只转储匹配LIKE模式的TABLE表。
.echo ON/OFF ：开启或关闭echo命令。
.exit ：退出SQLite提示符。
.explain ON/OFF ：开启或关闭适合于EXPLAIN的输出模式。如果没有带参数，则为EXPLAIN on，及开启EXPLAIN。
.header(s) ON/OFF ：开启或关闭头部显示。
.help ：显示消息。
.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 HTML的<table>代码；
- insert TABLE表的SQL插入（insert）语句；
- line 每行一个值；
- list 由 .separator字符串分隔的值；
- tabs 由Tab分隔的值；
- tcl TCL列表元素。
.nullvalue STRING ：在NULL值的地方输出STRING字符串。
.output FILENAME ：发送输出到FILENAME文件。
.output stdout ：发送输出到屏幕。
.print STRING... ：逐字地输出STRING字符串。
.prompt MAIN CONTINUE ：替换标准提示符。
.quit ：退出SQLite提示符。
.read FILENAME ：执行FILENAME文件中的SQL。
.schema ?TABLE? ：显示CREATE语句。如果指定了TABLE表，则只显示匹配LIKE模式的TABLE表。
.separator STRING ：改变输出模式和.import所使用的分隔符。
.show ：显示各种设置的当前值。
.stats ON/OFF ：开启或关闭统计。
.tables ?PATTERN? ：列出匹配LIKE模式的表的名称。
.timeout MS ：尝试打开锁定的表MS微秒。
.width NUM NUM ：为”column”模式设置列宽度。
.timer ON/OFF ：开启或关闭CPU定时器测量。

Sqlite数据库的sqlite_master表

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

sqlite>.schema sqlite_master

基础

Sqlite语法规则

SQLite是遵循一套独特的称为语法的规则和准则。

SQLite是不区分大小写的，但也有一些命令是大小写敏感的，比如GLOB和glob在SQLite的语句中有不同的含义。

注释：

-- 单行注释

/*
  多行注释
	 */

Sqlite数据类型

在SQLite中，值的数据类型与值本身是相关的，而不是与它的容器相关。

存储类

SQLite有5个原始的数据类型，被称为存储类。存储类这个词表明了一个值在磁盘上存储的格式，其实就是类型或数据类型的同义词。如下即是存储类：

存储类	格式	Description
NULL	`NULL`	值是一个NULL值。
INTEGER	`1`，`100`	值是一个带符号的整数，根据值的大小存储在1、2、3、4、6 或8字节中。
REAL	`2.2`，`3.14`	值是一个浮点值，存储为8字节的IEEE浮点数字。
TEXT	`'THis is Test'`	值是一个文本字符串，使用数据库编码（UTF-8、UTF-16BE或UTF-16LE）存储。
BLOB	`X'FFFF`，`x'FFFF`	值是一个blob数据，完全根据它的输入存储。

例子：

sqlite> SELECT typeof(1.2), typeof(12), typeof('1.2'), typeof(null), 
   ...> typeof(x'12'), typeof(x'FF'), typeof(x'ff');

real|integer|text|null|blob|blob|blob

SQLite单独的一个字段可能包含不同存储类的值。例如：

sqlite> CREATE TABLE table_test(temp);

sqlite> INSERT INTO table_test VALUES (12);
sqlite> INSERT INTO table_test VALUES ('12');
sqlite> INSERT INTO table_test VALUES (1.2);
sqlite> INSERT INTO table_test VALUES (x'12');
sqlite> INSERT INTO table_test VALUES (null);

sqlite> SELECT temp, typeof(temp) FROM table_test;
12|integer
12|text
1.2|real
#|blob
|null

同字段可以有不同类型的值，这样的字段在排序时这是样比较值大小的：

BLOB > TEXT > INTEGER = REAL > NULL

弱类型（manifest typing）

建表时字段可以指定类型，插入记录时会尝试自动转换：

sqlite> CREATE TABLE table_test_2(x INTEGER, y TEXT, Z REAL);

sqlite> INSERT INTO table_test_2 VALUES ('1', '1', '1');

sqlite> SELECT * FROM table_test_2;
1|1|1.0

不指定类型的话，会存入不同的类型：

sqlite> CREATE TABLE table_test_3(x, y, z);

sqlite> INSERT INTO table_test_3 VALUES ('1', '1', '1');
sqlite> INSERT INTO table_test_3 VALUES (1, 1.0, x'10');

sqlite> SELECT * FROM table_test_3;
1|1|1
1|1.0|

类型亲和性(Type Affinity)

在SQLite中，字段没有类型或域。当给一个字段声明了类型，该字段实际上仅仅具有了该类型的亲和性。声明类型和类型亲和性是两回事。类型亲和性预定SQLite用什么存储类在字段中存储值。在存储一个给定的值时到底SQLite会在该字段中用什么存储类决定于值的存储类和字段亲和性的结合。

任何列可以存储任何类型的数据，但列的首选存储类是它的affinity。在SQLite3数据库中，每个表的列分配为以下类型的affinity之一：

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

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

数据类型	Affinity
INT、NTEGER、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」算起的秒数。

日期和时间函数

函数	实例
date(timestring, modifiers…)	以YYYY-MM-DD格式返回日期。
time(timestring, modifiers…)	以 HH:MM:SS 格式返回时间。
datetime(timestring, modifiers…)	以 YYYY-MM-DD HH:MM:SS 格式返回。
julianday(timestring, modifiers…)	这将返回从格林尼治时间的公元前4714年11月24日正午算起的天数。
strftime(timestring, modifiers…)	这将根据第一个参数指定的格式字符串返回格式化的日期。具体格式见下边讲解。

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

时间字符串

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

时间字符串	实例
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作为分隔日期和时间的文字字符。

修饰符（Modifiers）

时间字符串后边可跟着零个或多个的修饰符，这将改变有上述五个函数返回的日期和/或时间。任何上述五大功能返回时间。修饰符应从左到右使用，下面列出了可在SQLite中使用的修饰符：

NNN days
NNN hours
NNN minutes
NNN.NNNN seconds
NNN months
NNN years
start of month
start of year
start of day
weekday N
unixepoch
localtime
utc

格式化

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');
-- 2013-05-07
 
-- 计算当前月份的最后一天：
SELECT date('now','start of month','+1 month','-1 day');
-- 2013-05-31
 
-- 计算给定UNIX时间戳1092941466的日期和时间：
SELECT datetime(1092941466, 'unixepoch');
-- 2004-08-19 18:51:06
 
-- 计算给定UNIX时间戳1092941466相对本地时区的日期和时间：
SELECT datetime(1092941466, 'unixepoch', 'localtime');
-- 2004-08-19 11:51:06
 
-- 计算当前的UNIX时间戳：
SELECT datetime(1092941466, 'unixepoch', 'localtime');
-- 1367926057
 
-- 计算美国"独立宣言"签署以来的天数：
SELECT julianday('now') - julianday('1776-07-04');
-- 86504.4775830326
 
-- 计算从2004年某一特定时刻以来的秒数：
SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');
-- 295001572
 
-- 计算当年10月的第一个星期二的日期：
SELECT date('now','start of year','+9 months','weekday 2');
-- 2013-10-01
 
-- 计算从UNIX纪元算起的以秒为单位的时间（类似 strftime('%s','now') ，不同的是这里有包括小数部分）：
SELECT (julianday('now') - 2440587.5)*86400.0;
-- 1367926077.12598
 
-- 在UTC与本地时间值之间进行转换，当格式化日期时，使用utc或localtime修饰符，如下所示：
SELECT time('12:00', 'localtime');
-- 05:00:00
 
SELECT time('12:00', 'utc');
-- 19:00:00

NULL值

带有NULL值的字段在记录创建的时候可以保留为空。NULL值在选择数据时会引起问题，因为当把一个未知的值与另一个值进行比较时，结果总是未知的，且不会包含在最后的结果中。

字段类型和亲和性

首先，每个字段都具有一种亲和性。共有五种亲和性：

NUMERIC
INTEGER
REAL
TEXT
NONE

一个字段的亲和性由它预声明的类型决定。所以，当你为字段声明了类型，从根本上说是为字段指定了亲和性。SQLite按下面的规则为字段指派亲和性：

默认的，一个字段默认的亲和性是NUMERIC。如果一个字段不是INTEGER、TEXT、REAL或NONE的，那它自动地被指派为NUMERIC亲和性。
如果为字段声明的类型中包含了INT(无论大小写)，该字段被指派为INTEGER亲和性。
如果为字段声明的类型中包含了CHAR、CLOB或TEXT(无论大小写)，该字段被指派为TEXT亲和性。如VARCHAR包含了CHAR，所以被指派为TEXT亲和性。
如果为字段声明的类型中包含了BLOB(无论大小写)，或者没有为该字段声明类型，该字段被指派为NONE亲和性。

注意：如果没有为字段声明类型，该字段的亲和性为NONE，在这种情况下，所有的值都将以它们本身的(或从它们的表示法中推断的)存储类存储。如果你暂时还不确定要往一个字段里放什么内容，或准备将来修改，用NONE亲和性是一个好的选择。但SQLite默认的亲和性是NUMERIC。例如，如果为一定字段声明了类型JUJYFRUIT，该字段的亲和性不是NONE，因为SQLite不认识这种类型，会给它指派默认的NUMERIC亲和性。所以，与其用一个不认识的类型最终得到NUMERIC亲和性，还不如不为它指定类型，从而使它得到NONE亲和性。

亲和性和存储

亲和性对值如何存储到字段有影响，规则如下：

一个NUMERIC字段可能包括所有5种存储类。一个NUMERIC字段具有数字存储类的偏好(INTEGER和REAL)。当一个TEXT值被插入到一个NUMERIC字段，将会试图将其转化为INTEGER存储类；如果转化失败，将会试图将其转化为REAL存储类；如果还是失败，将会用TEXT存储类来存储。
一个INTEGER字段的处理很像NUMERIC字段。一个INTEGER字段会将REAL值按REAL存储类存储。也就是说，如果这个REAL值没有小数部分，就会被转化为INTEGER存储类。INTEGER字段将会试着将TEXT值按REAL存储；如果转化失败，将会试图将其转化为INTEGER存储类；如果还是失败，将会用TEXT存储类来存储。
一个TEXT字段将会把所有的INTEGER或REAL值转化为TEXT。
一个NONE字段不试图做任何类型转化。所有值按它们本身的存储类存储。
没有字段试图向NULL或BLOB值转化——如无论用什么亲和性。NULL和BLOB值永远都按本来的方式存储在所有字段。

这些规则初看起来比较复杂，但总的设计目标很简单，如果你需要，SQLite会尽量模仿其它的关系型数据库。也就是说，如果你将SQLite看成是一个传统数据库，类型亲和性将会按你的期望来存储值。如果你声明了一个INTEGER字段，并向里面放一个整数，就会按整数来存储。如果你声明了一个具有TEXT, CHAR或VARCHAR类型的字段并向里放一个整数，整数将会转化为TEXT。可是，如果你不遵守这些规定，SQLite也会找到办法来存储你的值。

如下例子展示了亲和性是如何工作的：

sqlite> CREATE TABLE table_test_22(i INT, n NUMBERIC, t TEXT, b BLOB);

sqlite> INSERT INTO table_test_22 VALUES (1.2, 1.2, 1.2, 1.2);
sqlite> INSERT INTO table_test_22 VALUES ('1.2', '1.2', '1.2', '1.2');
sqlite> INSERT INTO table_test_22 VALUES (12, 12, 12, 12);
sqlite> INSERT INTO table_test_22 VALUES (x'12', x'12', x'12', x'12');
sqlite> INSERT INTO table_test_22 VALUES (null, null, null, null);

sqlite> SELECT typeof(i), typeof(n), typeof(t), typeof(b) FROM table_test_22;
real|real|text|real
real|real|text|text
integer|integer|text|integer
blob|blob|blob|blob
null|null|null|null

存储类和类型转换

关于存储类，需要关注的另一件事是存储类有时会影响到值如何进行比较。特别是SQLite有时在进行比较之前，会将值在数字存储类(INTEGER和REAL)和TEXT之间进行转换。为进行二进制的比较，遵循如下规则：

当一个字段值与一个表达式的结果进行比较，字段的亲和性会在比较之前应用于表达式的结果。
当两个字段值进行比较，如果一个字段拥有INTEGER或NUMERIC亲和性而另一个没有，NUMERIC亲和性会应用于非NUMERIC字段的TEXT值。
当两个表达式进行比较，SQLite不做任何转换。如果两个表达式有相似的存储类，则直接按它们的值进行比较；否则按类值进行比较。

数据库操作

创建数据库

格式：

sqlite3 数据库名.db

例子：

$ sqlite3 testdb.db                           -- 创建数据库

SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

sqlite> .databases                            -- 查看现有的数据库
seq  name             file                                                      
---  ---------------  ----------------------------------------------------------
0    main             /home/jade/testdb.db

数据库导入与导出

$ sqlite3 testdb.db .dump > testdb.sql       # 导出数据库到文件
$ sqlite3 testdb.db < testdb.sql             # 文件导入数据库

附加数据库

从多个可用的数据库中选一个：

ATTACH DATABASE '数据库名' AS '别名';

如果名字是:memory:表示视为内存数据库，内存数据库无法持久化到磁盘文件上。
如果操作Attached数据库中的表，则需要在表名前加数据库名，如dbname.table_name。
如果一个事务包含多个Attached数据库操作，那么该事务仍然是原子的。

$ sqlite3 testdb1.db                                 # 创建数据库testdb1
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

sqlite> CREATE TABLE tableyb (qid INTEGER);
sqlite> INSERT INTO tableyb VALUES (1);
sqlite> .exit                                        # 退出数据库


$ sqlite3 testdb2.db                                 # 创建数据库testdb2
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

sqlite> CREATE TABLE tableyb (qid INTEGER);
sqlite> INSERT INTO tableyb VALUES (2);
sqlite> INSERT INTO tableyb VALUES (1);

sqlite> ATTACH DATABASE 'testdb1.db' as testdb1;     # 导入数据库testdb1

sqlite> SELECT tb2.qid FROM tableyb tb2, testdb1.tableyb tb1 
   ...> where tb1.qid == tb2.qid;
1

分离数据库

格式：

DETACH DATABASE 'Alias-Name';

分离数据库用来把ATTACH附加上的数据库连接分离和游离出来。
如果同一个数据库文件已经附加上多个别名，分离命令只断开指定名称的连接。
main和temp数据库无法被分离。
内存数据库与临时数据库分离后会被摧毁，内容会丢失。

例子：

sqlite> .databases
seq  name             file                                                      
---  ---------------  ----------------------------------------------------------
0    main             /home/jade/testdb2.db                                     
2    testdb1          /home/jade/testdb1.db                                     
sqlite> DETACH DATABASE testdb1;

数据表操作

创建数据表

格式：

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

设置了AUTOINCREMENT后insert或者update时不需要指定值，自动的，而且是从1开始的。

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

删除数据表

SQLite的DROP TABLE语句用来删除表定义及其所有相关数据、索引、触发器、约束和该表的权限规范。使用此命令时要特别注意，因为一旦一个表被删除，表中所有信息也将永远丢失。

删除表语法：

DROP TABLE database_name.table_name;

例子：

sqlite> .tables                          -- 查看现有的表
tableyb   tableyb2

sqlite> DROP TABLE main.tableyb2;        -- 删除表

sqlite> .tables
tableyb

sqlite> .schema                          -- 查看表结构
CREATE TABLE tableyb (qid INTEGER);

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

如下展示示例：

ALTER TABLE teamTable RENAME TO androidTeamTable;
ALTER TABLE androidTeamTable ADD COLUMN sex char(1);

新添加的列是以NULL值来填充的

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 androidTeamTable;
VACUUM;

CRUD操作

Select

SELECT column1, column2, columnN FROM table_name;

SELECT * FROM table_name;

例子：

sqlite> .header on                         -- 设置格式：显示表头
sqlite> .mode column                       -- 设置格式：按列对齐

sqlite> select * from tableyb;
qid       
----------
2         
1

Insert

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

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

根据其他的表创建：

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

Update语句

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

语法：

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

Delete语句

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

语法：

DELETE FROM table_name WHERE [condition];

Like子句

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

百分号%`代表零个、一个或多个数字或字符。
下划线_`代表一个单一的数字或字符。

这些符号可以被组合使用。

语法：

SELECT FROM table_name WHERE column LIKE 'XXXX%';
 
SELECT FROM table_name WHERE column LIKE 'XXXX_';

Glob子句

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

星号*代表零个、一个或多个数字或字符。
问号?代表一个单一的数字或字符。

这些符号可以被组合使用。

语法：

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

Limit子句

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

带有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]

Order By关键字

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

语法：

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

Group By关键字

SQLite的GROUP BY子句用于与SELECT语句一起使用，来对相同的数据进行分组。在SELECT语句中，GROUP BY子句放在WHERE子句之后，放在ORDER BY子句之前。

语法：

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

GROUP BY子句必须放在WHERE子句中的条件之后，必须放在ORDER BY子句之前。

Having子句

HAVING子句允许指定条件来过滤将出现在最终结果中的分组结果。WHERE子句在所选列上设置条件，而HAVING子句则在由GROUP BY子句创建的分组上设置条件。

下面是HAVING子句在SELECT查询中的位置：

SELECT FROM WHERE GROUP BY HAVING ORDER BY

在一个查询中，HAVING子句必须放在GROUP BY子句之后，必须放在ORDER BY子句之前。下面是包含HAVING子句的SELECT语句的语法：

SELECT column1, column2
	FROM table1, table2
	WHERE [ conditions ]
	GROUP BY column1, column2
	HAVING [ conditions ]
	ORDER BY column1, column2;

Distinct关键字

SQLite的DISTINCT关键字与SELECT语句一起使用，来消除所有重复的记录，并只获取唯一一次记录。有可能出现一种情况，在一个表中有多个重复的记录。当提取这样的记录时，DISTINCT关键字就显得特别有意义，它只获取唯一一次记录，而不是获取重复记录。

语法：

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

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语言支持的位运算符。

运算符	描述
`&`	二进制AND运算符。
竖线	二进制OR运算符。
`~`	二进制补码运算符是一元运算符，具有”翻转”位效应。
`<<`	左操作数的值向左移动右操作数指定的位数。
`>>`	右操作数的值向右移动右操作数指定的位数。

SQLite表达式

布尔表达式

例，在where条件中用布尔表达式：

select * from employee where age = 15;

数值表达式

SELECT numerical_expression as OPERATION_NAME 
	[FROM table_name WHERE CONDITION] ;

例：

select sum(salary) as 'toatlSalary' from employee where age > 25;

日期表达式

sqlite> SELECT CURRENT_TIMESTAMP;
CURRENT_TIMESTAMP  
-------------------
2015-10-13 17:00:07

SQLite约束

NOT NULL约束

示例语法：

CREATE TABLE AndroidTeam(
   id INT PRIMARY KEY     NOT NULL,
   name           TEXT    NOT NULL,
   age            INT     NOT NULL,
   address        CHAR(50),
   money         REAL
);

DEFAULT约束

示例语法：

CREATE TABLE AndroidTeam(
   id INT PRIMARY KEY     NOT NULL,
   name           TEXT    NOT NULL,
   age            INT     NOT NULL,
   address        CHAR(50),
   money          REAL    DEFAULT 4500.0
);

UNIQUE约束

示例语法：

CREATE TABLE AndroidTeam(
   id INT PRIMARY KEY     NOT NULL,
   name           TEXT    NOT NULL,
   age            INT     NOT NULL UNIQUE,
   address        CHAR(50),
   money         REAL    DEFAULT 4500.0
);

PRIMARY KEY约束

示例语法：

CREATE TABLE AndroidTeam(
   id INT PRIMARY KEY     NOT NULL,
   name           TEXT    NOT NULL,
   age            INT     NOT NULL,
   address        CHAR(50),
   money         REAL
);

CHECK约束

示例语法：

CREATE TABLE AndroidTeam(
   id INT PRIMARY KEY     NOT NULL,
   name           TEXT    NOT NULL,
   age            INT     NOT NULL,
   address        CHAR(50),
   money         REAL    CHECK(money > 0)
);

删除约束

SQLite支持ALTER TABLE的有限子集。在SQLite中，ALTER TABLE命令允许用户：

重命名表
向现有表添加一个新的列。

不支持：

重命名列
删除一列
从一个表中添加或删除约束都是不可能的。

表连接 Join

交叉连接：Cross Join

交叉连接（CROSS JOIN）把第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有 x 和 y 列，则结果表有 x+y 列。由于交叉连接（CROSS JOIN）有可能产生非常大的表，使用时必须谨慎，只在适当的时候使用它们。

语法：

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

内连接INNER JOIN

内连接（INNER JOIN）根据连接谓词结合两个表（table1和table2）的列值来创建一个新的结果表。查询会把table1中的每一行与table2中的每一行进行比较，找到所有满足连接谓词的行的匹配对。当满足连接谓词时，A和B行的每个匹配对的列值会合并成一个结果行。

内连接（INNER JOIN）是最常见的连接类型，是默认的连接类型。INNER 关键字是可选的。

语法：

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

外连接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, ... ) ...;

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语句的结果，包括重复行。适用于UNION的规则同样适用于UNION ALL运算符。

语法：

SELECT column1 [, column2 ]
	FROM table1 [, table2 ]
	[WHERE condition]
UNION ALL
SELECT column1 [, column2 ]
	FROM table1 [, table2 ]
	[WHERE condition];

这里给定的条件根据需要可以是任何表达式。

触发器（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
	-- 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;

列出触发器（TRIGGERS）

您可以从sqlite_master表中列出所有触发器，如下所示：

> SELECT name FROM sqlite_master WHERE type = 'trigger';
name
----------
audit_log

如果您想要列出特定表上的触发器，则使用AND子句连接表名，如下所示：

> SELECT name FROM sqlite_master WHERE type = 'trigger' AND tbl_name = 'teamTable';
name
----------
audit_log

删除触发器（TRIGGERS）

下面是DROP命令，可用于删除已有的触发器：

DROP TRIGGER trigger_name;

SQLite索引（Index）

CREATE INDEX基本语法：

CREATE INDEX index_name ON table_name;

单列索引

单列索引是一个只基于表的一个列上创建的索引。基本语法：

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

是否要创建一个单列索引还是组合索引，要考虑到您在作为查询过滤条件的WHERE子句中使用频繁的列。如果值使用到一个列，则选择使用单列索引。如果在作为过滤的WHERE子句中有两个或多个列经常使用，则选择使用组合索引。

隐式索引

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

如下实例演示：

CREATE INDEX money_index ON teamTable (money);

查看索引：

> .indices teamTable
salary_index

创建表时创建的隐式索引：

sqlite_autoindex_COMPANY_1

列出数据库范围的所有索引：

SELECT * FROM sqlite_master WHERE type = 'index';

DROP INDEX命令：

一个索引可以使用SQLite的DROP命令删除。当删除索引时应特别注意，因为性能可能会下降或提高。

基本语法：

DROP INDEX index_name;

什么情况下避免使用索引

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

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

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

如下实例：

假设有表teamTable表，我们将创建一个索引，并用它进行INDEXED BY操作：

CREATE INDEX money_index ON teamTable(money);

现在使用INDEXED BY子句从表teamTable中选择数据：

SELECT * FROM teamTable INDEXED BY money_index WHERE money > 5000;

SQLite视图（View）

视图（View）只不过是通过相关的名称存储在数据库中的一个SQLite语句。视图（View）实际上是一个以预定义的SQLite查询形式存在的表的组合。视图（View）可以包含一个表的所有行或从一个或多个表选定行。视图（View）可以从一个或多个表创建，这取决于要创建视图的SQLite查询。视图（View）是一种虚表，允许用户实现以下几点：

用户或用户组查找结构数据的方式更自然或直观。
限制数据访问，用户只能看到有限的数据，而不是完整的表。
汇总各种表中的数据，用于生成报告。

SQLite视图是只读的，因此可能无法在视图上执行DELETE、INSERT或UPDATE语句。但是可以在视图上创建一个触发器，当尝试DELETE、INSERT或UPDATE视图时触发，需要做的动作在触发器内容中定义。

创建视图

SQLite的视图是使用CREATE VIEW语句创建的。SQLite视图可以从一个单一的表、多个表或其他视图创建。

CREATE VIEW基本语法：

CREATE [TEMP | TEMPORARY] VIEW view_name AS SELECT column1, column2..... FROM table_name WHERE [condition];

您可以在SELECT语句中包含多个表，这与在正常的SQL SELECT查询中的方式非常相似。如果使用了可选的TEMP或TEMPORARY关键字，则将在临时数据库中创建视图。

如下实例展示：

CREATE VIEW test_view AS SELECT id, name, age FROM androidTeamTable;
 
SELECT * FROM test_view;

删除视图

要删除视图，只需使用带有view_name的DROP VIEW语句。DROP VIEW的基本语法如下：

DROP VIEW view_name;

SQLite事务（Transaction）

事务（Transaction）是一个对数据库执行工作单元。事务（Transaction）是以逻辑顺序完成的工作单位或序列，可以是由用户手动操作完成，也可以是由某种数据库程序自动完成。事务（Transaction）是指一个或多个更改数据库的扩展。例如，如果您正在创建一个记录或者更新一个记录或者从表中删除一个记录，那么您正在该表上执行事务。重要的是要控制事务以确保数据的完整性和处理数据库错误。实际上，您可以把许多的SQLite查询联合成一组，把所有这些放在一起作为事务的一部分进行执行。事务的属性

事务（Transaction）具有以下四个标准属性，通常根据首字母缩写为ACID：

原子性（Atomicity）：确保工作单位内的所有操作都成功完成，否则，事务会在出现故障时终止，之前的操作也会回滚到以前的状态。
一致性（Consistency)：确保数据库在成功提交的事务上正确地改变状态。
隔离性（Isolation）：使事务操作相互独立和透明。
持久性（Durability）：确保已提交事务的结果或效果在系统发生故障的情况下仍然存在。

事务控制

使用下面的命令来控制事务：

COMMIT：保存更改，或者可以使用END TRANSACTION命令。
ROLLBACK：回滚所做的更改。

开始事务处理。

BEGIN TRANSACTION;

开始事务处理。

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

BEGIN TRANSACTION命令

事务（Transaction）可以使用BEGIN TRANSACTION命令或简单的BEGIN命令来启动。此类事务通常会持续执行下去，直到遇到下一个COMMIT或ROLLBACK命令。不过在数据库关闭或发生错误时，事务处理也会回滚。以下是启动一个事务的简单语法：

BEGIN; 或者 BEGIN TRANSACTION;

COMMIT命令

COMMIT命令是用于把事务调用的更改保存到数据库中的事务命令。COMMIT命令把自上次COMMIT或ROLLBACK命令以来的所有事务保存到数据库。

语法：

COMMIT; 或者 END TRANSACTION;

ROLLBACK命令

ROLLBACK命令是用于撤消尚未保存到数据库的事务的事务命令。ROLLBACK命令只能用于撤销自上次发出COMMIT或ROLLBACK命令以来的事务。

语法：

ROLLBACK;

SQLite子查询

子查询或内部查询或嵌套查询是在另一个SQLite查询内嵌入在WHERE子句中的查询。使用子查询返回的数据将被用在主查询中作为条件，以进一步限制要检索的数据。子查询可以与SELECT、INSERT、UPDATE和DELETE语句一起使用，可伴随着使用运算符如 =、<、>、>=、<=、IN、BETWEEN等。

以下是子查询必须遵循的几个规则：

子查询必须用括号括起来。
子查询在SELECT子句中只能有一个列，除非在主查询中有多列与子查询的所选列进行比较。
ORDER BY不能用在子查询中，虽然主查询可以使用ORDER BY。
子查询返回多于一行，只能与多值运算符一起使用，如IN运算符。
BETWEEN运算符不能与子查询一起使用，但是BETWEEN可在子查询内使用。

SELECT语句中的子查询使用*

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

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

如下示例：

SELECT * FROM teamTable WHERE id IN (SELECT id FROM teamTable WHERE money > 45000);

INSERT语句中的子查询使用

子查询也可以与INSERT语句一起使用。INSERT语句使用子查询返回的数据插入到另一个表中。在子查询中所选择的数据可以用任何字符、日期或数字函数修改。

基本语法如下：

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

把一个表的数据全部插入到另一个bck表中：

INSERT INTO teamTable_bak SELECT * FROM teamTable WHERE id IN (SELECT id FROM teamTable);

UPDATE语句中的子查询使用

子查询可以与UPDATE语句结合使用。当通过UPDATE语句使用子查询时，表中单个或多个列被更新。

基本语法如下：

UPDATE table
	SET column_name = new_value
	[ WHERE OPERATOR [ VALUE ]
	(SELECT COLUMN_NAME
		FROM TABLE_NAME)
		[ WHERE) ];

如下示例：

UPDATE teamTable SET money = money * 0.50 WHERE age IN (
		SELECT age FROM teamTable_bck WHERE age >= 24);

DELETE语句中的子查询使用

子查询可以与DELETE语句结合使用，就像上面提到的其他语句一样。

基本语法如下：

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

如下示例：

DELETE FROM teamTable WHERE age IN (
		SELECT age FROM teamTable_bck WHERE age > 24);

SQLite常用函数

SQLite有许多内置函数用于处理字符串或数字数据。下面列出了一些有用的SQLite内置函数，且所有函数都是大小写不敏感，这意味着您可以使用这些函数的小写形式或大写形式或混合形式。欲了解更多详情，请查看SQLite的官方文档。

函数	描述
COUNT	SQLite COUNT聚集函数是用来计算一个数据库表中的行数。
MAX	SQLite MAX聚合函数允许我们选择某列的最大值。
MIN	SQLite MIN聚合函数允许我们选择某列的最小值。
AVG	SQLite AVG聚合函数计算某列的平均值。
SUM	SQLite SUM聚合函数允许为一个数值列计算总和。
RANDOM	SQLite RANDOM函数返回一个介于-9223372036854775808和+9223372036854775807之间的伪随机整数。
ABS	SQLite ABS函数返回数值参数的绝对值。
UPPER	SQLite UPPER函数把字符串转换为大写字母。
LOWER	SQLite LOWER函数把字符串转换为小写字母。
LENGTH	SQLite LENGTH函数返回字符串的长度。
sqlite_version	SQLite sqlite_version函数返回SQLite库的版本。

SQLite Explain（解释）

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

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

EXPLAIN的语法如下：

EXPLAIN [SQLite Query]

EXPLAIN QUERY PLAN 的语法如下：

EXPLAIN  QUERY PLAN [SQLite Query]
SQLite PRAGMA

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

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

PRAGMA pragma_name;

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

PRAGMA pragma_name = value;

设置模式，可以是名称或等值的整数，但返回的值将始终是一个整数。