【SQLite】SQLite教程笔记（二）

SQLite Join

交叉连接 – CROSS JOIN

交叉连接（CROSS JOIN）把第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有 x 和 y 行，则结果表有 x*y 行。由于交叉连接（CROSS JOIN）有可能产生非常大的表，使用时必须谨慎，只在适当的时候使用它们。
交叉连接的操作，它们都返回被连接的两个表所有数据行的笛卡尔积，返回到的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。

下面是交叉连接（CROSS JOIN）的语法：

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

示例：

SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;

连接关系抽象示意图：

会产生：（X*Y项连接）
a-1
a-2
a-3
a-4
b-1
b-2
b-3
b-4
c-1
c-2
c-3
c-4
d-1
d-2
d-3
d-4

内连接 – INNER JOIN

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

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

也就是把符合条件的组装起来，然后不符合条件的丢掉。

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

简单来说就是把符合条件的组装起来，然后把左边表中不符合条件的留下，右边的丢掉（左边表留下的时候，不符合条件的右侧表的列对应的值为空）

SQLite触发器（trigger）

SQLite触发器（trigger）是数据库的回调函数，它会在指定的数据库事件发生时自动执行/调用。
SQLite触发器（trigger）的要点：

• SQLite触发器可以在指定的数据库表发生DELETE、INSERT或UPDATE时触发，或在一个/多个指定表的列发生更新时触发。

• SQLite只支持FOR EACH ROW触发器，没有FOR EACH STATEMENT触发器。因此，明确指定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() 可用于触发器程序内抛出异常。

创建触发器

语法：

1. event_name是在所提到的表table_name上的INSERT、DELETE和UPDATE数据库操作。可以在表名后选择指定FOR EACH ROW。

CREATE TRIGGER trigger_name [BEFORE|AFTER] event_name
ON table_nane
BEGIN
--触发器逻辑--
END

2. 在UPDATE操作在表的一个或多个指定列上创建触发器：

CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_nane
BEGIN
--触发器逻辑--
END

3. for each row 是操作语句每影响到一行的时候就触发一次，也就是删了 10 行就触发 10 次，而 for each state 一条操作语句就触发一次，有时没有被影响的行也执行。sqlite 只实现了 for each row 的触发。when 和 for each row 用法是这样的：

CREATE TRIGGER trigger_name 
AFTER UPDATE OF id ON table_1 
FOR EACH ROW 
WHEN new.id>30 
BEGIN 
UPDATE table_2 SET id=new.id WHERE table_2.id=old.id;
END;

--上面的触发器在 table_1 改 id 的时候如果新的 id>30 就把 表table_2 中和表table_1 id 相等的行一起改为新的 id

列出触发器

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

SELECT name FROM sqlite_master
WHERE type = 'trigger';

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

SELECT name FROM sqlite_master
WHERE type = 'trigger' AND tbl_name = 'COMPANY';

删除触发器

DROP TRIGGER trigger_name;