SQLLite学习笔记

嗯。。。前段时间整理的常用知识：

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 通常在 GROUP BY 子句中使用。
(5)如果不使用 GROUP BY 子句，则 HAVING 的行为与 WHERE 子句一样。

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

--举例子说明：查询table表查询每一个班级中年龄大于20，性别为男的人数

select COUNT(*)as '>20岁人数',classid  from Table1 where sex='男' group by classid,age having age>20 

--需要注意说明：当同时含有where子句、group by 子句 、having子句及聚集函数时，执行顺序如下：

--执行where子句查找符合条件的数据；

--使用group by 子句对数据进行分组；对group by 子句形成的组运行聚集函数计算每一组的值；最后用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)不等连接： 在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!&lt;和<>。

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，那么下标就是指字节。