SQLLite进修笔记
SQLite，是一款轻型的数据库，是恪守ACID的关联型数据库治理体系，它包括在一个相对小的C库中。它能够支撑Windows/Linux/Unix等等主流的操纵体系，同时能够跟许多顺序言语相连系，比方 Tcl、C#、PHP、Java等。它的处置惩罚速率异常快。
一、基础操纵
建表：
关键字：AUTOINCREMENT可用于某一列得值自动递增，只能用于整形字段，可在建表得时刻到场这个关键字
删除表：
向表中插进去新数据
或许
第二种写法必需确保值得递次与列在表中得递次一致
查询表
背面能够跟WHERE 举行前提查询，“*”示意查询表中一切列，也能够如许
--子查询或许内部查询或许嵌套查询时在另一个SQL利特查询内嵌入在where子句中得查询。
以下是子查询必需遵照的几个划定规矩：
· 子查询必需用括号括起来。
· 子查询在 SELECT 子句中只能有一个列，除非在主查询中有多列，与子查询的所选列举行比较。
· ORDER BY 不能用在子查询中，虽然主查询能够运用 ORDER BY。能够在子查询中运用 GROUP BY，功用与 ORDER BY 雷同。
· 子查询返回多于一行，只能与多值运算符一同运用，如 IN 运算符。
· BETWEEN 运算符不能与子查询一同运用，然则，BETWEEN 可在子查询内运用。
修正表数据
删除表数据
二、运算符
假定变量 a=10，变量b=20，则：
算数运算符
运算符 |
形貌 |
实例 |
+ |
加法 - 把运算符双方的值相加 |
a + b 将获得 30 |
- |
减法 - 左操纵数减去右操纵数 |
a - b 将获得 -10 |
* |
乘法 - 把运算符双方的值相乘 |
a * b 将获得 200 |
/ |
除法 - 左操纵数除以右操纵数 |
b / a 将获得 2 |
% |
取模 - 左操纵数除以右操纵数后获得的余数 |
b % a will give 0 |
比较运算符
运算符 |
形貌 |
实例 |
== |
搜检两个操纵数的值是不是相称，假如相称则前提为真。 |
(a == b) 不为真。 |
= |
搜检两个操纵数的值是不是相称，假如相称则前提为真。 |
(a = b) 不为真。 |
!= |
搜检两个操纵数的值是不是相称，假如不相称则前提为真。 |
(a != b) 为真。 |
<> |
搜检两个操纵数的值是不是相称，假如不相称则前提为真。 |
(a <> b) 为真。 |
> |
搜检左操纵数的值是不是大于右操纵数的值，假如是则前提为真。 |
(a > b) 不为真。 |
< |
搜检左操纵数的值是不是小于右操纵数的值，假如是则前提为真。 |
(a < b) 为真。 |
>= |
搜检左操纵数的值是不是大于即是右操纵数的值，假如是则前提为真。 |
(a >= b) 不为真。 |
<= |
搜检左操纵数的值是不是小于即是右操纵数的值，假如是则前提为真。 |
(a <= b) 为真。 |
!< |
搜检左操纵数的值是不是不小于右操纵数的值，假如是则前提为真。 |
(a !< b) 为假。 |
!> |
搜检左操纵数的值是不是不大于右操纵数的值，假如是则前提为真。 |
(a !> b) 为真。 |
逻辑运算符
运算符 |
形貌 |
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 运算符搜刮指定表中的每一行，确保唯一性（无反复）。 |
1、位运算符
下表中列出了 SQLite 言语支撑的位运算符。假定变量 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 |
三、不常常运用的子句
1、GLOB
在日常平凡我们的sql中，并不常常运用GLOB，GLOB的作用与LIKE基础一致，最大的差别是GLOB辨别大小写。星号（*）代表零个、一个或多个数字或字符。问号（?）代表一个单一的数字或字符。
与LIKE对比：GLOB的*与LIKE的%作用一致
GLOB的？与LIKE的_作用一致
2、HAVING
指定前提过滤，就如许看来我们能够就会联想到WHERE，那末WHERE与HAVING有什么差别呢？实在两者区分实在挺大的。
（1）HAVING 子句许可指定前提来过滤将出如今终究效果中的分组效果。
（2）WHERE 子句在所选列上设置前提，而 HAVING 子句则在由 GROUP BY 子句建立的分组上设置前提。
(3)HAVING只能与SELECT语句一同运用。
(4)HAVING通常在GROUPBY子句中运用。
(5)假如不运用GROUPBY子句，则HAVING的行动与WHERE子句一样。
(6)在一个查询中，HAVING 子句必需放在 GROUP BY 子句今后，必需放在 ORDER BY 子句之前。
--举例子申明：查询table表查询每一个班级中岁数大于20，性别为男的人数
selectCOUNT(*)as'>20岁人数',classidfromTable1wheresex='男'groupbyclassid,agehavingage>20
--须要注重申明：当同时含有where子句、groupby子句、having子句及群集函数时，实行递次以下：
--实行where子句查找相符前提的数据；
--运用groupby子句对数据举行分组；对groupby子句构成的组运转群集函数盘算每一组的值；末了用having子句去掉不相符前提的组。
--having子句中的每一个元素也必需出如今select列表中。有些数据库破例，如oracle.
--having子句和where子句都能够用来设定限定前提以使查询效果满足肯定的前提限定。
--having子句限定的是组，而不是行。where子句中不能运用群集函数，而having子句中能够。
四、Distinct 关键字
SQLite 的 DISTINCT 关键字与 SELECT 语句一同运用，来消弭一切反复的纪录，并只猎取唯一一次纪录。
有能够涌现一种状况，在一个表中有多个反复的纪录。当提取如许的纪录时，DISTINCT 关键字就显得迥殊有意义，它只猎取唯一一次纪录，而不是猎取反复纪录。
五、束缚
束缚是在表的数据列上强制实行的划定规矩。这些是用来限定能够插进去到表中的数据范例。这确保了数据库中数据的准确性和可靠性。
束缚能够是列级或表级。列级束缚仅适用于列，表级束缚被应用到悉数表。
以下是在 SQLite 中常常运用的束缚。
· NOT NULL 束缚：确保某列不能有 NULL 值。
· DEFAULT 束缚：当某列没有指定值时，为该列供应默许值。
· UNIQUE 束缚：确保某列中的一切值是差别的。
· PRIMARY Key 束缚：唯一标识数据库表中的各行/纪录。
· CHECK 束缚：CHECK 束缚确保某列中的一切值满足肯定前提。
六、SQLite Joins
SQLite 的 Joins 子句用于连系两个或多个数据库中表的纪录。JOIN 是一种经由过程配合值来连系两个表中字段的手腕。
SQL 定义了三种重要范例的衔接：
· 交织衔接 - CROSS JOIN
· 内衔接 - INNER JOIN
· 外衔接 - OUTER JOIN
1、交织衔接CROSS JOIN
交织衔接(CROSSJOIN)没有WHERE 子句，它返回衔接表中一切数据行的笛卡尔积，其效果鸠合中的数据行数即是第一个表中相符查询前提的数据行数乘以第二个表中相符查询前提的数据行数。因为交织衔接（CROSS JOIN）有能够发作异常大的表，运用时必需郑重，只在恰当的时刻运用它们。
2、内衔接inner join
内衔接的效果是从两个或许两个以上的表的组合中挑选出相符衔接前提的数据。假如数据没法满足衔接前提则将其抛弃。在内衔接中，介入衔接的表的职位是同等的。
内衔接查询操纵列出与衔接前提婚配的数据行，它运用比较运算符比较被衔接列的列值。内衔接分三种：
1)等值衔接：在衔接前提中运用即是号(=)运算符比较被衔接列的列值，其查询效果中列出被衔接表中的一切列，包括个中的反复列。
2)不等衔接： 在衔接前提运用除即是运算符之外的别的比较运算符比较被衔接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。
3)天然衔接：在衔接前提中运用即是(=)运算符比较被衔接列的列值，但它运用挑选列表指出查询效果鸠合中所包括的列，并删除衔接表中的反复列。
3、外衔接
外衔接（OUTERJOIN）是内衔接（INNER JOIN）的扩大。虽然 SQL规范定义了三种范例的外衔接：LEFT、RIGHT、FULL，但 SQLite 只支撑 左外衔接（LEFT OUTER JOIN）。外衔接（OUTER JOIN）声明前提的要领与内衔接（INNER JOIN）是雷同的，运用 ON、USING 或 NATURAL 关键字来表达。最初的效果表以雷同的体式格局举行盘算。一旦主衔接盘算完成，外衔接（OUTER JOIN）将从一个或两个表中任何未衔接的行兼并进来，外衔接的列运用 NULL值，将它们附加到效果表中。
七、unions子句
1、SQLite的 UNION 子句/运算符用于兼并两个或多个 SELECT 语句的效果，不返回任何反复的行。
为了运用UNION，每一个 SELECT 被挑选的列数必需是雷同的，雷同数量的列表达式，雷同的数据范例，并确保它们有雷同的递次，但它们没必要具有雷同的长度。
2、UNIONALL 子句
UNION ALL 运算符用于连系两个 SELECT 语句的效果，包括反复行。
适用于UNION 的划定规矩一样适用于 UNION ALL 运算符。
八、触发器
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() 可用于触发器顺序内抛出异常。
示例：
SELECT name FROM sqlite_master WHEREtype = 'trigger';可检察一切的触发器
for each row 是操纵语句每影响到一行的时刻就触发一次，也就是删了 10 行就触发 10 次，而 for each state 一条操纵语句就触发一次，偶然没有被影响的行也实行。sqlite 只完成了 for each row 的触发。when 和 for each row 用法是如许的：
CREATETRIGGER trigger_name
AFTERUPDATE OF id ON table_1
FOR EACHROW
WHEN new.id>30
BEGIN
UPDATEtable_2 SET id=new.id WHEREtable_2.id=old.id;
END;
上面的触发器在 table_1 改 id 的时刻假如新的 id>30 就把 表table_2 中和表table_1 id 相称的行一同改成新的 id
九、索引
索引（Index）是一种特别的查找表，数据库搜刮引擎用来加速数据检索。索引有助于加速 SELECT 查询和WHERE 子句，但它会减慢运用 UPDATE 和INSERT 语句时的数据输入。索引能够建立或删除，但不会影响数据。
单列索引
单列索引是一个只基于表的一个列上建立的索引。基础语法以下：
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 子句中有两个或多个列常常运用，则挑选运用组合索引。
隐式索引
隐式索引是在建立对象时，由数据库服务器自动建立的索引。索引自动建立为主键束缚和唯一束缚。
SELECT * FROM sqlite_master WHEREtype = 'index';检察数据库中的一切索引
DROP INDEX index_name;一个索引能够运用 SQLite 的 DROP 敕令删除
什么状况下要防止运用索引？
虽然索引的目标在于进步数据库的机能，但这里有几个状况须要防止运用索引。运用索引时，应重新考虑以下原则：
· 索引不应该运用在较小的表上。
· 索引不应该运用在有频仍的大批量的更新或插进去操纵的表上。
· 索引不应该运用在含有大批的 NULL 值的列上。
· 索引不应该运用在频仍操纵的列上。
Indexed By
"INDEXEDBY index-name" 子句划定必需须要定名的索引来查找前面表中值。
假如索引名 index-name 不存在或不能用于查询，然后 SQLite 语句的预备失利。
"NOTINDEXED" 子句划定当接见前面的表（包括由 UNIQUE 和 PRIMARYKEY 束缚建立的隐式索引）时，没有运用索引。
但是，纵然指定了 "NOT INDEXED"，INTEGER PRIMARY KEY依然能够被用于查找条目。
INDEXED BY能够与 DELETE、UPDATE 或SELECT 语句一同运用：
SELECT|DELETE|UPDATE column1, column2...
INDEXED BY (index_name)
table_name
WHERE (CONDITION);
十、重定名表或向表中插进去新的字段
用来重定名已有的表的 ALTERTABLE 的基础语法以下：
ALTERTABLE database_name.table_name RENAME TOnew_table_name;
用来在已有的表中增添一个新的列的 ALTERTABLE 的基础语法以下：
ALTERTABLE database_name.table_name ADD COLUMNcolumn_def...;
十一、视图
视图（View）只不过是经由过程相干的称号存储在数据库中的一个 SQLite 语句。视图（View）现实上是一个以预定义的 SQLite 查询情势存在的表的组合。
视图（View）能够包括一个表的一切行或从一个或多个表选定行。视图（View）能够从一个或多个表建立，这取决于要建立视图的 SQLite 查询。、
视图（View）是一种虚表，许可用户完成以下几点：
① 用户或用户组查找构造数据的体式格局更天然或直观。
② 限定数据接见，用户只能看到有限的数据，而不是完全的表。
③ 汇总种种表中的数据，用于生成报告。
SQLite 视图是只读的，因而能够没法在视图上实行 DELETE、INSERT 或 UPDATE 语句。然则可以在视图上建立一个触发器，当尝试 DELETE、INSERT 或 UPDATE 视图时触发，须要做的行动在触发器内容中定义。
1、建立视图
CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];
2、删除视图
DROP VIEW view_name;
十二、日期时和时候
SQLite 支撑以下五个日期和时候函数：
序号 |
函数 |
实例 |
1 |
date(timestring, modifier, modifier, ...) |
以 YYYY-MM-DD 花样返回日期。 |
2 |
time(timestring, modifier, modifier, ...) |
以 HH:MM:SS 花样返回时候。 |
3 |
datetime(timestring, modifier, modifier, ...) |
以 YYYY-MM-DD HH:MM:SS 花样返回。 |
4 |
julianday(timestring, modifier, modifier, ...) |
这将返回从格林尼治时候的公元前 4714 年 11 月 24 日中午算起的天数。 |
5 |
strftime(format, timestring, modifier, modifier, ...) |
这将依据第一个参数指定的花样字符串返回花样化的日期。细致花样见下边解说。 |
上述五个日期和时候函数把时候字符串作为参数。时候字符串后跟零个或多个 modifier 润饰符。strftime() 函数也能够把花样字符串 format 作为其第一个参数。下面将为您细致解说差别范例的时候字符串和润饰符。
时候字符串
一个时候字符串能够采纳下面任何一种花样：
序号 |
时候字符串 |
实例 |
1 |
YYYY-MM-DD |
2010-12-30 |
2 |
YYYY-MM-DD HH:MM |
2010-12-30 12:10 |
3 |
YYYY-MM-DD HH:MM:SS.SSS |
2010-12-30 12:10:04.100 |
4 |
MM-DD-YYYY HH:MM |
30-12-2010 12:10 |
5 |
HH:MM |
12:10 |
6 |
YYYY-MM-DDTHH:MM |
2010-12-30 12:10 |
7 |
HH:MM:SS |
12:10:01 |
8 |
YYYYMMDD HHMMSS |
20101230 121001 |
9 |
now |
2013-05-07 |
您能够运用 "T" 作为分开日期和时候的笔墨字符。
润饰符（Modifier）
时候字符串后边可随着零个或多个的润饰符，这将转变有上述五个函数返回的日期和/或时候。任何上述五大功用返回时候。润饰符应从左到右运用，下面列出了可在 SQLite 中运用的润饰符：
(1)增添指定数值的日期和时候（正负数都能够）
· NNN days
· NNN hours
· NNN minutes
· NNN.NNNN seconds
· NNN months
· NNN years
(2)返回当前日期的最先
· start of month
· start of year
· start of day
(3)返回下一个礼拜是N的日期和时候
· weekday N
(4)返回从1970-01-01最先算起的秒数
· unixepoch
(5)返回当地时候
· localtime
（6）国际规范时候
· 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 |
selectdatetime('now','localtime');--当前时候读取当地得时候
selectdatetime('now','start of month');--本月第一天
selectdatetime('now','start of month','+1 month','-1 day');--本月末了一天
selectdatetime('now','start of year','+1 year','start of month','-1 day');--本年末了一天
SELECTjulianday(date('now','localtime')) - julianday('2018-03-20');；--盘算本日到2018-03-20相差若干天
十三、函数大全
1、count 用于盘算一个数据库表中得行数
如：select count（*）from k_user
2、max 挑选某列最大值
3、min 挑选某列最小值
4、avg 盘算某列平均值
5、sum 许可一个数值列盘算总和
6、random 返回一个介于 -9223372036854775808 和 +9223372036854775807之间的伪随机整数
7、abs 返回数值参数得绝对值
8、upper 吧字符串转换为大写字母
9、lower 把字符串转换为小写字母
10、length 返回字符串长度
11、sqlite_version 返回数据库版本
12、coalesce(X,Y,……) 返回第一个非空参数的副本。若一切的参数均为NULL，返回NULL。最少2个参数。
13、ifnull(X,Y) 返回第一个非空参数的副本。若两个参数均为NULL，返回NULL。
14、last_insert_rowid() 返回当前数据库衔接末了插进去行的RowID。
15、nullif(X,Y) 当两参数差别时返回X，不然返回NULL。
16、quote(X) 返回参数的适于插进去其他SQL语句中的值。字符串会被增添单引号。
17、round(X)或round(X,Y) 将X四舍五入，保存小数点后Y位。若疏忽Y参数，则默许其为0。
18、zeroblob(N) 返回一个 N 字节长、悉数由 0x00 构成的 BLOB。SQLite 或以很有用的构造这些 zeroblob。它能够被用于为今后的运用预留空间。今后能够运用 incremental BLOB I/O 来写入 BLOB 数据。
19、typeof(X) 返回表达式X的范例
20、change_count() 返回受上一语句影响的行数。（彷佛不能用）
21、total(X) 返回一组中一切非空值的数字和。若没有非空行，sum()返回null而total()返回0.0。total()的返回值为浮点数，sum()能够为整数。
22、ltrim(X)
lrtrim(X,Y) 返回从 X 的左边边去除一切出如今 Y 中字符今后的字符串。假如省略 Y，则去除空格。
23、 replace(X,Y,Z) 返回一个将 X 字符串中每一个涌现 Y 的位置替代为 Z 后的字符串。它运用二进制对比序列举行比较。
24、randomblob(N) 返回一个 N 字节长的包括伪随机字节的 BLOG。 N 应该是正整数
25、rtrim(X)
rtrim(X,Y) 返回从 X 的右侧去除一切出如今 Y 中字符今后的字符串。假如省略 Y，则去除空格。
26、soundex(X) 盘算字符串 X的读音编码。假如参数为 NULL，则返回 "?000"。默许状况下 SQLite 疏忽该函数。仅当在编译时指定 -DSQLITE_SOUNDEX=1 时才有用。
27、substr(X,Y,Z)
substr(X,Y) 返回字符串 X 从第 Y 个字符最先，长度为 Z 的字符串。假如省略 Z， 将返回直到字符串末端的字符串。 X 的第一个字符从 1 最先。假如 Y是负数，则从右侧最先数。假如 X 是 UTF-8 字符串，那末，下标值将指现实的 UTF-8 characters 字符，而不是字节。假如 X 是一个 BLOB，那末下标就是指字节。
本文解说了SQLLite相干内容，更多相干内容请关注ki4网。
相干引荐：
Spark SQL完成日记离线批处置惩罚
关于MySQL-检索数据的详解
sql 相邻2条纪录时候差比较
以上就是SQLLite相干内容的细致内容，更多请关注ki4网别的相干文章！
