SQLite(5) 高级用法大全

1. SQLite 约束

• NOT NULL 约束：确保某列不能有 NULL 值。
• DEFAULT 约束：当某列没有指定值时，为该列提供默认值。
• UNIQUE 约束：确保某列中的所有值是不同的。
• PRIMARY Key 约束：唯一标识数据库表中的各行/记录。
• CHECK 约束：CHECK 约束确保某列中的所有值满足一定条件。

-- 示例CREATE TABLE COMPANY3(   ID           INT PRIMARY KEY     NOT NULL,   NAME         TEXT                NOT NULL,   AGE          INT                 NOT NULL UNIQUE,   ADDRESS      CHAR(50),   SALARY       REAL                CHECK(SALARY > 0));

2. SQLite Joins 联合查询

SQLite 的 Joins 子句用于结合两个或多个数据库中表的记录。JOIN 是一种通过共同值来结合两个表中字段的手段。
SQL 定义了三种主要类型的连接：

• 交叉连接 - CROSS JOIN
• 内连接 - INNER JOIN
• 外连接 - OUTER JOIN

2.1. 交叉连接 - CROSS JOIN

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

-- 示例SELECT EMP_ID, NAME, DEPT FROM table1 CROSS JOIN table2;

2.2. 内连接 - INNER JOIN

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

-- 写法1SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...;-- 写法2SELECT ... FROM table1 JOIN table2 USING ( column1 ,... ) ...;-- 写法3SELECT ... FROM table1 NATURAL JOIN table2...;

SELECT EMP_ID, NAME, DEPT FROM table1 INNER JOIN table2 ON table1.ID = table2.EMP_ID;

2.3. 外连接 - OUTER JOIN

外连接（OUTER JOIN）是内连接（INNER JOIN）的扩展。虽然 SQL 标准定义了三种类型的外连接：LEFT、RIGHT、FULL，但 SQLite 只支持 左外连接（LEFT OUTER JOIN）。

外连接（OUTER JOIN）声明条件的方法与内连接（INNER JOIN）是相同的，使用 ON、USING 或 NATURAL 关键字来表达。最初的结果表以相同的方式进行计算。一旦主连接计算完成，外连接（OUTER JOIN）将从一个或两个表中任何未连接的行合并进来，外连接的列使用 NULL 值，将它们附加到结果表中。

-- 写法1SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...;-- 写法2SELECT ... FROM table1 LEFT OUTER JOIN table2 USING ( column1 ,... ) ...;

-- 示例SELECT EMP_ID, NAME, DEPT FROM table1 LEFT OUTER JOIN table2 ON table1.ID = table2.EMP_ID;

3. SQLite Unions 合并

SQLite的 UNION 子句/运算符用于合并两个或多个 SELECT 语句的结果，不返回任何重复的行。

为了使用 UNION，每个 SELECT 被选择的列数必须是相同的，相同数目的列表达式，相同的数据类型，并确保它们有相同的顺序，但它们不必具有相同的长度。

未完待续………

4. SQLite NULL 值

SQLite 的 NULL 是用来表示一个缺失值的项。表中的一个 NULL 值是在字段中显示为空白的一个值。

带有 NULL 值的字段是一个不带有值的字段。NULL 值与零值或包含空格的字段是不同的，理解这点是非常重要的。

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

在这里，NOT NULL 表示列总是接受给定数据类型的显式值。这里有两个列我们没有使用 NOT NULL，这意味着这两个列不能为 NULL。
带有 NULL 值的字段在记录创建的时候可以保留为空。

-- 使用 UPDATE 语句来设置一些允许空值的值为 NULLUPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN(6,7);

IS NOT NULL 运算符的用法，它用来列出所有 SALARY 不为 NULL 的记录：

SELECT  ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY WHERE SALARY IS NOT NULL;

5. SQLite AS 别名

您可以暂时把表或列重命名为另一个名字，这被称为别名。使用表别名是指在一个特定的 SQLite 语句中重命名表。重命名是临时的改变，在数据库中实际的表的名称不会改变。

• 表 别名

SELECT column1, column2.... FROM table_name AS new_table_name WHERE [condition];

-- 表别名 的用法，在这里我们使用 C 和 D 分别作为 COMPANY 和 DEPARTMENT 表的别名：SELECT C.ID, C.NAME, C.AGE, D.DEPT FROM COMPANY AS C, DEPARTMENT AS D WHERE  C.ID = D.EMP_ID;

• 列 别名

SELECT column_name AS new_colum_name FROM table_name WHERE [condition];

-- 列别名 的实例，在这里 COMPANY_ID 是 ID 列的别名，COMPANY_NAME 是 name 列的别名：SELECT C.ID AS COMPANY_ID, C.NAME AS COMPANY_NAME, C.AGE, D.DEPT FROM COMPANY AS C, DEPARTMENT AS D WHERE  C.ID = D.EMP_ID;

6. SQLite Trigger 触发器

未完待续………

7. SQLite Index 索引

索引（Index）是一种特殊的查找表，数据库搜索引擎用来加快数据检索。

索引有助于加快 SELECT 查询和 WHERE 子句，但它会减慢使用 UPDATE 和 INSERT 语句时的数据输入。索引可以创建或删除，但不会影响数据。

使用 CREATE INDEX 语句创建索引，它允许命名索引，指定表及要索引的一列或多列，并指示索引是升序排列还是降序排列。

索引也可以是唯一的，与 UNIQUE 约束类似，在列上或列组合上防止重复条目。

• 单列索引: 单列索引是一个只基于表的一个列上创建的索引。

CREATE INDEX index_name ON table_name (column_name);-- 示例CREATE INDEX salary_index ON COMPANY (salary);

• 唯一索引: 使用唯一索引不仅是为了性能，同时也为了数据的完整性。唯一索引不允许任何重复的值插入到表中。

CREATE UNIQUE INDEX index_name on table_name (column_name);

• 组合索引: 组合索引: 组合索引是基于一个表的两个或多个列上创建的索引。

CREATE INDEX index_name on table_name (column1, column2);

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

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

DROP INDEX index_name;

8. SQLite Indexed By

“INDEXED BY index-name” 子句规定必须需要命名的索引来查找前面表中值。可以与 DELETE、UPDATE 或 SELECT 语句一起使用：

SELECT|DELETE|UPDATE column1, column2... INDEXED BY (index_name) table_name WHERE (CONDITION);-- 示例CREATE INDEX salary_index ON COMPANY(salary);-- 示例SELECT * FROM COMPANY INDEXED BY salary_index WHERE salary > 5000;

9. SQLite 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 语句重命名该表：ALTER TABLE COMPANY RENAME TO OLD_COMPANY;--上面的 SQLite 语句将重命名 COMPANY 表为 OLD_COMPANY。现在，让我们尝试在 OLD_COMPANY 表中添加一个新的列ALTER TABLE OLD_COMPANY ADD COLUMN SEX char(1);

10. SQLite View 模板

视图（View）是一种虚表，允许用户实现以下几点：

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

SQLite 视图是只读的，因此可能无法在视图上执行 DELETE、INSERT 或 UPDATE 语句。

-- 创建视图CREATE [TEMP | TEMPORARY] VIEW view_name AS SELECT column1, column2..... FROM table_name WHERE [condition];

ID NAME AGE ADDRESS SALARY 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0

//

-- 从 COMPANY 表创建视图的实例。视图只从 COMPANY 表中选取几列：CREATE VIEW COMPANY_VIEW AS SELECT ID, NAME, AGE FROM  COMPANY;-- 查询 COMPANY_VIEW，与查询实际表的方式类似SELECT * FROM COMPANY_VIEW;

删除视图
要删除视图，只需使用带有 view_name 的 DROP VIEW 语句。
DROP VIEW view_name;
DROP VIEW COMPANY_VIEW;

11. SQLite Transaction 事务

事务（Transaction）是一个对数据库执行工作单元。事务（Transaction）是以逻辑顺序完成的工作单位或序列，可以是由用户手动操作完成，也可以是由某种数据库程序自动完成。

11.1. 事务的属性

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

11.2. 事务控制

• BEGIN TRANSACTION：开始事务处理。
• COMMIT：保存更改，或者可以使用 END TRANSACTION 命令。
• ROLLBACK：回滚所做的更改。
• 事务控制命令只与 DML 命令 INSERT、UPDATE 和 DELETE 一起使用。他们不能在创建或删除表时使用，因为这些操作在数据库中是自动提交的。

11.3. BEGIN TRANSACTION 开始事务

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

BEGIN;or BEGIN TRANSACTION;

11.4. COMMIT 提交/结束事务

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

COMMIT;orEND TRANSACTION;

11.5. ROLLBACK 撤销事务

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

ROLLBACK;

-- 开始一个事务，并从表中删除 age = 25 的记录，最后，我们使用 ROLLBACK 命令撤消所有的更改。BEGIN;DELETE FROM COMPANY WHERE AGE = 25;ROLLBACK;

-- 开始另一个事务，从表中删除 age = 25 的记录，最后我们使用 COMMIT 命令提交所有的更改。BEGIN;DELETE FROM COMPANY WHERE AGE = 25;COMMIT;

12. SQLite 子查询(嵌套查询)

ID NAME AGE ADDRESS SALARY 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0

//

12.1. SELECT 语句中的子查询使用

SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY WHERE SALARY > 45000) ;

• 结果

ID NAME AGE ADDRESS SALARY 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0

12.2. INSERT 语句中的子查询使用

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

假设 COMPANY_BKP 的结构与 COMPANY 表相似，且可使用相同的 CREATE TABLE 进行创建，只是表名改为 COMPANY_BKP。现在把整个 COMPANY 表复制到 COMPANY_BKP

-- 示例INSERT INTO COMPANY_BKP SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY) ;

12.3. UPDATE 语句中的子查询使用

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

有 COMPANY_BKP 表，是 COMPANY 表的备份。下面的实例把 COMPANY 表中所有 AGE 大于或等于 27 的客户的 SALARY 更新为原来的 0.50 倍：

-- 示例UPDATE COMPANY SET SALARY = SALARY * 0.50 WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE >= 27 );

12.4. DELETE 语句中的子查询使用

-- 语法DELETE FROM TABLE_NAME WHERE OPERATOR [ VALUE ] (SELECT COLUMN_NAME FROM TABLE_NAME WHERE ) 

有 COMPANY_BKP 表，是 COMPANY 表的备份。下面的实例删除 COMPANY 表中所有 AGE 大于或等于 27 的客户记录：

DELETE FROM COMPANY WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE > 27 );

13. SQLite Autoincrement（自动递增）

- SQLite 的 AUTOINCREMENT 是一个关键字，用于表中的字段值自动递增。我们可以在创建表时在特定的列名称上使用.只能用于整型（INTEGER）字段。

-- 示例CREATE TABLE COMPANY(   ID          INTEGER PRIMARY KEY   AUTOINCREMENT,   NAME        TEXT                  NOT NULL,   AGE         INT                   NOT NULL,   ADDRESS     CHAR(50),   SALARY      REAL);

14. SQLite 注入与安全

如果您的站点允许用户通过网页输入，并将输入内容插入到 SQLite 数据库中，这个时候您就面临着一个被称为 SQL 注入的安全问题。

注入通常在请求用户输入时发生，比如需要用户输入姓名，但用户却输入了一个 SQLite 语句，而这语句就会在不知不觉中在数据库上运行。

15. SQLite 日期 & 时间

SQLite 支持以下五个日期和时间函数：

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

//

• 时间字符串

序号时间字符串实例1YYYY-MM-DD2010-12-302YYYY-MM-DD HH:MM2010-12-30 12:103YYYY-MM-DD HH:MM:SS.SSS2010-12-30 12:10:04.1004MM-DD-YYYY HH:MM30-12-2010 12:105HH:MM12:106YYYY-MM-DDTHH:MM2010-12-30 12:107HH:MM:SS12:10:018YYYYMMDD HHMMSS20101230 1210019now2013-05-07

//

• 格式化
  

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');2017-12-13

-- 下面是计算当前月份的最后一天：SELECT date('now','start of month','+1 month','-1 day');2017-12-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 strftime('%s','now');1367926057

-- 下面是计算从 2004 年某一特定时刻以来的秒数：SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');295001572

16. SQLite 常用函数

SQLite 有许多内置函数用于处理字符串或数字数据。下面列出了一些有用的 SQLite 内置函数，且所有函数都是大小写不敏感，

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

//表

ID NAME AGE ADDRESS SALARY 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0

//

16.1. SQLite COUNT 函数

-- SQLite COUNT 聚集函数是用来计算一个数据库表中的行数。SELECT count(*) FROM COMPANY;

16.2. SQLite MAX 函数

-- SQLite MAX 聚合函数允许我们选择某列的最大值。SELECT max(salary) FROM COMPANY;

16.3. SQLite MIN 函数

-- SQLite MIN 聚合函数允许我们选择某列的最小值。SELECT min(salary) FROM COMPANY;

16.4. SQLite AVG 函数

-- SQLite AVG 聚合函数计算某列的平均值。下面是实例：SELECT avg(salary) FROM COMPANY;

16.5. SQLite SUM 函数

-- SQLite SUM 聚合函数允许为一个数值列计算总和。下面是实例：SELECT sum(salary) FROM COMPANY;

16.6. SQLite RANDOM 函数

-- SQLite RANDOM 函数返回一个介于 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数。SELECT random() AS Random;

16.7. SQLite ABS 函数

-- SQLite ABS 函数返回数值参数的绝对值。sqlite> SELECT abs(5), abs(-15), abs(NULL), abs(0), abs("ABC");

16.8. SQLite UPPER 函数

-- SQLite UPPER 函数把字符串转换为大写字母sqlite> SELECT upper(name) FROM COMPANY;

16.9. SQLite LOWER 函数

SQLite LOWER 函数把字符串转换为小写字母。SELECT lower(name) FROM COMPANY;

16.10. SQLite LENGTH 函数

-- SQLite LENGTH 函数返回字符串的长度。下面是实例：SELECT name, length(name) FROM COMPANY;