SqlLite 简明教程

SQL DML 和 DDL
可以把 SQL 分为两个部分：数据操作语言 (DML) 和 数据定义语言 (DDL)。
注:"--"双减号为行注释
SQL (结构化查询语言)是用于执行查询的语法。但是 SQL 语言也包含用于更新、插入和删除记录的语法。
查询和更新指令构成了 SQL 的 DML 部分：
SELECT - 从数据库表中获取数据
UPDATE - 更新数据库表中的数据
DELETE - 从数据库表中删除数据
INSERT INTO - 向数据库表中插入数据
SQL 的数据定义语言 (DDL) 部分使我们有能力创建或删除表格。我们也可以定义索引（键），规定表之间的链接，以及施加表间的约束。
SQL 中最重要的 DDL 语句:
CREATE DATABASE - 创建新数据库
ALTER DATABASE - 修改数据库
CREATE TABLE - 创建新表
ALTER TABLE - 变更（改变）数据库表
DROP TABLE - 删除表
CREATE INDEX - 创建索引（搜索键）
DROP INDEX - 删除索引
数据操作语言(DML)
运算符 注释 运算对象 运算符 注释 运算对象
|| 字符串连接 字符串 < 关系小于 全
* 算数乘 数字 <= 关系小于等于 全
/ 算数除 数字 > 关系大于 全
% 算数取余 数字 >= 关系大于等于 全
+ 算数加 数字 =,== 关系等于 全
- 算数减 数字 !=,<> 关系不等于 全
<< 位运算右位移 数字 - 取负 数字
>> 位运算左位移 数字 + 取正 数字
& 位运算与 数字 !(NOT) 非(NOT) 数字
| 位运算或 数字 ~ 位异或 数字
操作符 注释
AND 条件与
OR 条件或
LIKE 模式匹配
BETWEEN 范围值匹配
IN 匹配多个值
AS 别名定义
LIMIT 限定结果集记录数
ORDER BY 排序
IS [NOT] NULL 判断字段是否为或不为NULL
AND 和 OR 运算符
AND 和 OR 可在 WHERE 子语句中把两个或多个条件结合起来。如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。
LIMIT 操作符
LIMIT 子句用于规定要返回的记录的数目。对于拥有数千条记录的大型表来说，LIMIT 子句是非常有用的。
WHERE 表达式 LIMIT number
LIKE 操作符
LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。LIKE匹配模式,NOT LIKE不匹配模式,"%" 可用于定义通配符（多个字母）"_"单个字母("__"双下划线表示匹配一个汉字)。
语法:
WHERE 字段 [NOT] LIKE 模式字符串
IN 操作符
IN 操作符允许我们在 WHERE 子句中规定多个值。
语法:
WHERE 字段名 IN (值1,值2...)
BETWEEN 操作符
操作符 BETWEEN ... AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。
语法:
WHERE 字段名 BETWEEN 起始值AND 终止值
Alias（别名 AS）
通过使用 SQL，可以为字段和基本表指定别名（AS）,就是更改字段/基本表显示名称。
语法:
字段名 AS 字段别名
基表名 AS 基表别名
ORDER BY 语句
ORDER BY 语句用于根据指定的列对结果集进行排序。ORDER BY 语句默认按照升序对记录进行排序。如果您希望按照字母降序对记录进行排序，可以使用 DESC 关键字,数字逆序用ASC关键字。
语法:
WHERE 表达式 ORDER BY 字段名 [DESC|ASC]
SELECT 语句
SELECT 语句用于从表中选取数据,结果被存储在一个结果表中（称为结果集）。
SQL SELECT 语法:
SELECT字段名|表名.字段名|*|DISTINCT 字段名FROM 基表名
DISTINCT 关键字
在表中，字段可能会包含重复值。这并不成问题，不过，有时您也许希望仅仅列出不同（distinct）的值。关键词 DISTINCT 用于返回唯一不同的字段值。
语法：
SELECT DISTINCT 字段名 FROM 基表名
WHERE 子句
如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句。
语法:
SELECT 字段 FROM 基表名 WHERE 表达式
Update 语句
Update 语句用于修改表中的数据。
语法：
UPDATE 基表名 SET 字段 = 表达式[,字段2 = 表达式....] WHERE 表达式(用于确定某一或一组字段)
DELETE 语句
DELETE 语句用于删除表中的行。
语法:
DELETE FROM 基表名 WHERE 表达式
可以在不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的：
DELETE FROM 基表名
INSERT INTO 语句
INSERT INTO 语句用于向表格中插入新的行(记录)。INSERT语句有两种基本形式。一种带有"VALUES"关键字，在已有表中插入一个新的行。若不定义字段列表，那么值的数目将与表中的字段数目相同。否则值的数目须与字段列表中的字段数目相同。不在字段列表中的字段被赋予缺省值或NULL(当未定义缺省值)。
INSERT的第二种形式从SELECT语句中获取数据。若未定义字段列表，则从SELECT得到的字段的数目必须与表中的字段数目相同，否则应与定义的字段列表中的字段数目相同。SELECT的每一行结果在表中插入一个新的条目。SELECT可以是简单的或者复合的。如果SELECT语句带有ORDER BY子句，ORDER BY会被忽略。
在使用这一命令时，利用可选的ON CONFLICT子句可以定义替代的约束冲突判定算法。更多信息，参见 ON CONFLICT 。为了兼容MySQL，可以使用REPLACE代替"INSERT OR REPLACE".
语法:
INSERT INTO 基表名 [(字段1, 字段2,...) ]VALUES (字段值1, 字段值2,....)
我们也可以指定所要插入数据的字段：
INSERT INTO 基表名 [(字段1, 字段2,...) ]VALUESSELECT语句
UNION 操作符
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意，UNION 内部的 SELECT 语句必须拥有相同数量的字段。字段也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
语法:
SELECT 字段 FROM 基表
UNION
SELECT 字段 FROM 基表
默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
SELECT 字段 FROM 基表
UNION ALL
SELECT 字段 FROM 基表
数据定义语言(DDL)
SQL约束共7种:
SQL NOT NULL 约束
NOT NULL 约束强制列不接受 NULL 值。NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新纪录或者更新记录。
SQL NULL 约束
NULL 值是遗漏的未知数据。默认地，表的列可以存放 NULL 值。
SQL UNIQUE 约束
UNIQUE 约束唯一标识数据库表中的每条记录。UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。PRIMARY KEY 拥有自动定义的 UNIQUE 约束。
请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。
SQL PRIMARY KEY 约束
PRIMARY KEY 约束唯一标识数据库表中的每条记录。主键必须包含唯一的值。主键列不能包含 NULL 值。每个表应该都一个主键，并且每个表只能有一个主键。
SQL AUTOINCREMENT 约束
我们通常希望在每次插入新纪录时，自动地创建主键字段的值。我们可以在表中创建一个 autoincrement 字段。自动递增，起始值为1。
SQL CHECK约束
CHECK 约束用于限制字段中的值的范围。如果对单个列定义 CHECK 约束，那么该列只允许特定的值。如果对一个表定义 CHECK 约束，那么此约束会在特定的字段中对值进行限制。
CHECK (字段 值域范围表达式[and 字段 值域范围表达式…])
SQL DEFAULT [diˈfɔ:lt] 约束
DEFAULT 约束用于向列中插入默认值。如果没有规定其他的值，那么会将默认值添加到所有的新记录。
DEFAULT 默认值
CREATE TABLE 语句
CREATE TABLE 语句用于创建数据库中的表。
语法:
CREATE TABLE 基表名
(字段1 数据类型 约束列表[,字段2 数据类型 约束列表]....)
ALTER TABLE 语句
SQLite版本的的ALTER TABLE命令允许用户重命名或添加新的字段到已有表中，不能从表中删除字段。
RENAME TO语法用于重命名表名。这一命令不能用于在附加数据库之间移动表，只能在同一个数据库中对表进行重命名。若需要重命名的表有触发器或索引，在重命名后它们依然属于该表。但若定义了视图，或触发器执行的语句中有提到 表的名字，则它们不会被自动改为使用新的表名。若要进行这一类的修改，则需手工撤销并使用新的表名重建触发器或视图。
ADD [COLUMN]语法用于在已有表中添加新的字段。新字段总是添加到已有字段列表的末尾,[COLUMN]是只读属性。字段定义可以是CREATE TABLE中定义字段允许出现的任何形式，且须符合如下限制：
· 字段不能有主键或唯一约束。
· 字段不能有这些缺省值：CURRENT_TIME, CURRENT_DATE 或CURRENT_TIMESTAMP
· 若定义了NOT NULL约束，则字段必须有一个非空的缺省值。
ALTER TABLE语句的执行时间与表中的数据量无关，它在操作一个有一千万行的表时的运行时间与操作仅有一行的表时是一样的。
在对数据库运行ADD COLUMN之后，该数据库将无法由SQLite 3.1.3及更早版本读取，除非运行VACUUM命令。
语法:
ALTER TABLE 表名
RENAME TO 重命名的表名 |ADD [COLUMN] 字段定义语句(字段名 数据类型 约束)
DROP TABLE 语句
DROP TABLE语句删除由CREATE TABLE语句创建的表。表将从数据库结构和磁盘文件中完全删除，且不能恢复。该表的所有索引也同时被删除。DROP TABLE语句在缺省模式下不减小数据库文件的大小。空间会留给后来的INSERT语句使用。要释放删除产生的空间，可以使用 VACUUM 命令。若AUTOVACUUM模式开启，则空间会自动被DROP TABLE释放。若使用可选的IF EXISTS子句，在删除的表不存在时就不会报错。
语法:
DROP TABLE [IF EXISTS] 基表名
CREATE INDEX 语句
CREATE INDEX 语句用于在表中创建索引。在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据。您可以在表中创建索引，以便更加快速高效地查询数据。用户无法看到索引，它们只能被用来加速搜索/查询。
注释：更新一个包含索引的表需要比更新一个没有索引的表更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。
语法:
CREATE [UNIQUE] INDEX 索引名
ON 基表名 (字段 [DESC|ASC] [,字段2 [DESC|ASC]])
UNIQUE 值不唯一;DESC 字符降序;ASC 数字降序
DROP INDEX 语句
DROP INDEX语句删除由CREATE INDEX 语句创建的索引。索引将从数据库结构和磁盘文件中完全删除，唯一的恢复方法是重新输入相应的CREATE INDEX命令。DROP TABLE语句在缺省模式下不减小数据库文件的大小。空间会留给后来的INSERT语句使用。要释放删除产生的空间，可以使用VACUUM命令。若AUTOVACUUM模式开启，则空间会自动被DROP INDEX释放。
语法:
DROP INDEX [IF EXISTS] 索引名
CREATE VIEW 语句
在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。
视图包含记录和字段，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。
注释：视图总是显示最近的数据。每当用户查询视图时，数据库引擎通过使用 SQL 语句来重建数据。
语法:
CREATE VIEW 视图名 AS SELECT查询语句
DROP VIEW 语句
DROP VIEW语句删除由CREATE VIEW 创建的视图。视图从数据库的schema中删除，表中的数据不会被更改。
语法:
DROP VIEW 视图名
GROUP BY 语句
GROUP BY 语句用于结合合计函数，根据一个或多个字段对结果集进行分组。
HAVING 子句
在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。
常用SQLLite内置函数
常用数学函数
abs(X) 返回参数X的绝对值。
avg(X) 返回一组中非空的X的平均值。非数字值作0处理。avg()的结果总是一个浮点数，即使所有的输入变量都是整数。
count(*) 返回该组中的行数。
count(X) 返回一组中X是非空值的次数。
max(X) 返回一组中的最大值。大小由常用排序法决定。
min(X) 返回一组中最小的非空值。大小由常用排序法决定。仅在所有值为空时返回NULL。
sum(X) 返回一组中所有非空值的数字和。若没有非空行，sum()返回NULL,sum()可以为整数，当所有非空输入均为整数时，和是精确的。若sum()的任意一个输入既非整数也非NULL或计算中产生整数类型的溢出时，sum()返回接近真和的浮点数。
total(X) 返回一组中所有非空值的数字和。若没有非空行，total()返回0.0 ,total()的返回值式中为浮点数。
max(X,Y,...) 返回最大值。参数可以不仅仅为数字，可以为字符串。大小顺序由常用的排序法则决定。注意，max()在有2个或更多参数时为简单函数(返回一组字段的最大值)，但当仅给出一个参数时它变为聚集函数(返回该字段的最大值)。
min(X,Y,...) 返回最小值。与max(X,Y,...)类似。
random(*) 返回介于-2147483648和 +2147483647之间的随机整数。
round(X) 将整数X四舍五入
round(X,Y) 将X四舍五入，保留小数点后Y位。若忽略Y参数，则默认其为0
常用字符串函数
ifnull(X,Y) 判断X是否为空,是返回Y,否返回X。
length(X) 返回X的长度，以字符计。如果SQLite被配置为支持UTF-8，则返回UTF-8字符数而不是字节数。
lower(X) 返回X字符串的所有字符小写化版本。对UTF-8字符不能提供好的支持。
upper(X) 返回X字符串的所有字符大写化版本。对UTF-8字符不能提供好的支持。
nullif(X,Y) 当两参数不同时返回X，否则返回NULL.
sqlite_version(*) 返回所运行的SQLite库的版本号字符串。如 "2.8.0"。
substr(X,Y,Z) 返回输入字符串X中以第Y个字符开始，Z个字符长的子串。X最左端的字符序号为1。若Y为负，则从右至左数起。若SQLite配置支持UTF-8，则“字符”代表的是UTF-8字符而非字节。
typeof(X) 返回表达式X的类型。返回值可能为"null", "integer", "real", "text", 以及 "blob". SQLite的类型处理参见SQLite3的数据类型.
时间函数(共5个):
date(日期时间字符串或字段, 修正符, 修正符, ……)
time(日期时间字符串或字段, 修正符, 修正符, ……)
datetime(日期时间字符串或字段, 修正符, 修正符, ……)
julianday(日期时间字符串或字段, 修正符, 修正符, ……)
strftime(日期时间格式, 日期时间字符串, 修正符, 修正符, ……)
上述五个函数需要一个日期时间字符串做参数，后面可以跟零到多个修正符参数。而 strftime() 函数还需要一个日期时间格式字符串做第一个参数。
date() 函数返回一个以 “YYYY-MM-DD” 为格式的日期；
time() 函数返回一个以 “HH:MM:SS” 为格式的时间；
datetime()函数返回一个以 “YYYY-MM-DD HH:MM:SS” 为格式的日期时间；
julianday() 函数返回一个天数，从格林威治时间公元前4714年11月24号开始算起；
strftime() 函数返回一个经过格式话的日期时间，它可以用下面的符号对日期和时间进行格式化：
%d一月中的第几天 01-31
%f小数形式的秒，SS.SSSS
%H小时 00-24
%j一年中的第几天 01-366
%JJulian Day Numbers
%m月份 01-12
%M分钟 00-59
%s从 1970-01-01日开始计算的秒数
%S秒 00-59
%w星期，0-6，0是星期天
%W一年中的第几周 00-53
%Y年份 0000-9999
%%% 百分号
其他四个函数都可以用 strftime() 函数来表示：
date(…) -> strftime(“%Y-%m-%d”,…)
time(…) -> strftime(“%H:%M:%S”,…)
datetime(…) -> strftime(“%Y-%m-%d %H:%M:%S”,…)
julianday(…) -> strftime(“%J”,…)
日期时间字符串可以用以下几种格式：
YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
DDDD.DDDD
在第五种到第七种格式中的“T”是一个分割日期和时间的字符；第八种到第十种格式只代表2000-01-01日的时间，第十一种格式的’now’表示返回一个当前的日期和时间，使用格林威治时间(UTC）；第十二种格式表示一个 Julian Day Numbers。
修正符
日期和时间可以使用下面的修正符来更改日期或时间：
NNN years (年增量date)
NNN months (月份增量date)
NNN days (天数增量date)
NNN hours (小时增量time)
NNN minutes (分钟增量time)
NNN.NNNN seconds (秒增量time)
start of month(当前月的开始1号,D)
start of year (当前年的开始1月1号,D)
start of week (当前星期的开始,D)
start of day (当前天的开始,T)
weekday N (下一个星期是N的日期,D)
Unixepoch(返回从1970-01-01开始算起的秒数)
localtime (本地时间datatime)
Utc
下面举一些例子：
计算机当前时间
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 jolianday(‘now’)-jolianday(‘1981-12-23’)
两个日期时间之间相差多少秒
SELECT julianday('now')*86400 - julianday('2004-01-01 02:34:56')*86400
计算今年十月份第一个星期二的日期
SELECT date('now','start of year','+9 months','weekday 2');