Sqlite中sql高级用法整理

一、主键约束

PRIMARY KEY 约束唯一标识数据库表中的每个记录。

• 在一个表中可以有多个 UNIQUE 列，但只能有一个主键。

• 在设计数据库表时，主键是很重要的。主键是唯一的 ID。
  我们使用主键来引用表中的行。

• 可通过把主键设置为其他表的外键，来创建表之间的关系。

• 由于”长期存在编码监督”，在 SQLite 中 主键值 可 以是 NULLL，这是与其他数据库不同的地方。NULL 与没有数 据是不一样的，它代表着未知的数据。

• 一个表只能有一个主键，它可以由一个或多个字段组成。当多个字段作为主键，它们被称为复合键。
• 如果一个表在任何字段上定义了一个主键，那么在这些字段上不能有两个记录具有相同的值。

==注意：在做排序操作时，升序NULL排最前面，降序NULL排最后面。==

二、ALTER TABLE 语句

ALTER TABLE 语句用于在已有的表中添加、修改或删除列。

SQL ALTER TABLE 语法如下:

如需在表中添加列，请使用下列语法:

ALTER TABLE table_nameADD column_name datatype

要删除表中的列，请使用下列语法：

ALTER TABLE table_name DROP COLUMN column_name

注释：某些数据库系统不允许这种在数据库表中删除列的方式 (DROP COLUMN column_name)。SQLite 支持 ALTER TABLE 的有限子集。在 SQLite 中，ALTER TABLE 命令允许用户重命名表，或向现有表添加一个新的列。重命名列，删除一列，或从一个表中添加或删除约束都是不可能的。

要改变表中列的数据类型，请使用下列语法：

ALTER TABLE table_nameALTER COLUMN column_name datatype

三、连接

SQL 定义了三种主要类型的连接：

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

1.交叉连接

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

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

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

2.内连接

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

SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...

为了避免冗余，并保持较短的措辞，可以使用 USING 表达式声明内连接（INNER JOIN）条件。这个表达式指定一个或多个列的列表：

SELECT ... FROM table1 JOIN table2 USING ( column1 ,...)...

　　自然连接（NATURAL JOIN）类似于 JOIN…USING，只是它会自动测试存在两个表中的每一列的值之间相等值：

SELECT ... FROM table1 NATURAL JOIN table2...

3.外连接（难点）

　　外连接（OUTER JOIN）是内连接（INNER JOIN）的扩展。虽然 SQL 标准定义了三种类型的外连接：LEFT、RIGHT、FULL，但 SQLite 只支持 左外连接（LEFT OUTER JOIN）。
　　外连接（OUTER JOIN）声明条件的方法与内连接（INNER JOIN）是相同的，使用 ON、USING 或 NATURAL 关键字来表达。最初的结果表以相同的方式进行计算。一旦主连接计算完成，如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。 这里这么说，可能不是很好理解,我们先看看做外连接的语法，接着看一个实例帮助理解。
　　
下面是左外连接（LEFT OUTER JOIN）的语法：

SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...

下面看一个具体的例子：

a表 ：

id name 1 张三 2 李四 3 王五

b表：

id job parent_id 1 23 1 2 34 2 3 34 4

查询语句：

select   a.*,b.*   from   a   left   join   b     on   a.id=b.parent_id`  

结果为：

a.id a.name b.id b.job b.parent_id` 1 张三 1 23 1 2 李四 2 34 2 3 王武

四、触发器（Trigger）

　　SQLite 的触发器是数据库的回调函数，它会自动执行/指定的数据库事件发生时调用。
　　它会在指定的数据库事件发生时自动执行/调用。以下是关于 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 是从与触发器关联的表的列的名称。

---

NEW 与 OLD 详解
NEW 和 OLD用来表示触发器的所在表中，触发了触发器的那一行数据。
具体地：
在 INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
在 UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改为的新数据；
在 DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据；
使用方法： NEW.columnName （columnName 为相应数据表某一列名）
另外，OLD 是只读的，而 NEW 则可以在触发器中使用 SET 赋值，这样不会再次触发触发器，造成循环调用（如每插入一个学生前，都在其学号前加“2013”）。

---

• 如果提供 WHEN 子句，则只针对 WHEN 子句为真的指定行执行 SQL 语句。如果没有提供 WHEN 子句，则针对所有行执行 SQL 语句。

• BEFORE 或 AFTER 关键字决定何时执行触发器动作，决定是在关联行的插入、修改或删除之前或者之后执行触发器动作。

• 当触发器相关联的表删除时，自动删除触发器（Trigger）。

• 要修改的表必须存在于同一数据库中，作为触发器被附加的表或视图，且必须只使用 tablename，而不是database.tablename。

• 一个特殊的 SQL 函数 RAISE() 可用于触发器程序内抛出异常。
  创建 触发器（Trigger） 的基本语法如下：

CREATE OR REPLACE TRIGGER TG_NAME　　BEFORE | AFTER　　INSERT OR DELETE OR UPDATE [OF COL1,COL2,...]　　ON 表名　　[FOR EACH ROW]　　[WHEN 条件]　　DECLARE　　...　　BEGIN　　....　　EXCEPTION　　...　　END;

注释:　A) FOR EACH ROW 代表行级触发器.

　　B) WHEN: 只有符合该条件的时候才进行触发.

　　C) 可以通过INSERTING,DELETING,UPDATING来识别INSERT,DELETE,UPDATE操作.

　　D) 用new 和old(称为伪记录)来访问数据变更前后的值。

　　E) 触发器中的操作与触发事件(INSERT,UPDATE,DELETE)一起进行COMMIT和ROLLBACK;

以下是在 UPDATE 操作上在表的一个或多个指定列上创建触发器（Trigger）的语法：

CREATE  TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name ON table_nameBEGIN -- Trigger logic goes here....END;

实例

　　让我们假设一个情况，我们要为被插入到新创建的 COMPANY 表（如果已经存在，则删除重新创建）中的每一个记录保持审计试验：

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

　　为了保持审计试验，我们将创建一个名为 AUDIT 的新表。每当 COMPANY 表中有一个新的记录项时，日志消息将被插入其中：

sqlite> CREATE TABLE AUDIT(    EMP_ID INT NOT NULL,    ENTRY_DATE TEXT NOT NULL);

　　在这里，ID 是 AUDIT 记录的 ID，EMP_ID 是来自 COMPANY 表的 ID，DATE 将保持 COMPANY 中记录被创建时的时间戳。所以，现在让我们在 COMPANY 表上创建一个触发器，如下所示：

sqlite> CREATE TRIGGER audit_log AFTER INSERT ON COMPANYBEGIN   INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));END;

五、视图（View）

　　视图（View）只不过是通过相关的名称存储在数据库中的一个 SQLite 语句。视图（View）实际上是一个以预定义的 SQLite 查询形式存在的表的组合。

• 视图（View）可以包含一个表的所有行或从一个或多个表选定行。
• 视图（View）可以从一个或多个表创建，这取决于要创建视图的 SQLite 查询。
• 视图（View）是一种虚表，允许用户实现以下几点：
  
  1. 用户或用户组查找结构数据的方式更自然或直观。
  2. 限制数据访问，用户只能看到有限的数据，而不是完整的表。
  3. 汇总各种表中的数据，用于生成报告。
  4. SQLite 视图是只读的，因此可能无法在视图上执行 DELETE、INSERT 或 UPDATE 语句。但是可以在视图上创建一个触发器，当尝试 DELETE、INSERT 或 UPDATE 视图时触发，需要做的动作在触发器内容中定义。

创建视图:

　　SQLite 的视图是使用 CREATE VIEW 语句创建的。SQLite 视图可以从一个单一的表、多个表或其他视图创建。
　　CREATE VIEW 的基本语法如下：

CREATE [TEMP | TEMPORARY] VIEW view_name ASSELECT column1, column2.....FROM table_nameWHERE [condition];

　　您可以在 SELECT 语句中包含多个表，这与在正常的 SQL SELECT 查询中的方式非常相似。如果使用了可选的 TEMP 或 TEMPORARY 关键字，则将在临时数据库中创建视图。

---

sqlite内存数据库、临时数据库

• 内存数据库： 在SQLite中，数据库通常是存储在磁盘文件中的。然而在有些情况下，我们可以让数据库始终驻留在内存中。在创建并打开数据库后，不会有任何磁盘文件被生成，取而代之的是，一个新的数据库在纯内存中被成功创建了。由于没有持久化，该数据库在当前数据库连接被关闭后就会立刻消失。
• 临时数据库： 和内存数据库一样，在连接关闭后，临时数据库将自动消失，其底层文件也将被自动删除。尽管磁盘文件被创建用于存储临时数据库中的数据信息，但是实际上临时数据库也会和内存数据库一样通常驻留在内存中，唯一不同的是，当临时数据库中数据量过大时，SQLite为了保证有更多的内存可用于其它操作，因此会将临时数据库中的部分数据写到磁盘文件中，而内存数据库则始终会将数据存放在内存中。

---

六、索引（Index)

　　索引（Index）是一种特殊的查找表，数据库搜索引擎用来加快数据检索。简单地说，索引是一个指向表中数据的指针。一个数据库中的索引与一本书后边的索引是非常相似的。
　　 引有助于加快 SELECT 查询和 WHERE 子句，但它会减慢使用 UPDATE 和 INSERT 语句时的数据输入。索引可以创建或删除，但不会影响数据。
　　使用 CREATE INDEX 语句创建索引，它允许命名索引，指定表及要索引的一列或多列，并指示索引是升序排列还是降序排列。
　　索引也可以是唯一的，与 UNIQUE 约束类似，在列上或列组合上防止重复条目。
CREATE INDEX 基本命令：

CREATE INDEX index_name ON table_name;

1.单列索引

单列索引是一个只基于表的一个列上创建的索引。基本语法如下：

CREATE INDEX index_nameON table_name (column_name);

2.唯一索引

　　唯一性索引，该索引规则和数据表的唯一性约束的规则相同，即NULL和任何值都不同，包括NULL本身。

　　使用唯一索引不仅是为了性能，同时也为了数据的完整性。唯一索引不允许任何重复的值插入到表中。基本语法如下：

CREATE UNIQUE INDEX index_nameon table_name (column_name);

3.组合索引

　　组合索引是基于一个表的两个或多个列上创建的索引。基本语法如下：

CREATE INDEX index_nameon table_name (column1, column2);

　　是否要创建一个单列索引还是组合索引，要考虑到您在作为查询过滤条件的 WHERE 子句中使用非常频繁的列。如果值使用到一个列，则选择使用单列索引。如果在作为过滤的 WHERE 子句中有两个或多个列经常使用，则选择使用组合索引。

4.隐式索引

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

5.DROP INDEX 命令

　　一个索引可以使用 SQLite 的 DROP 命令删除。当删除索引时应特别注意，因为性能可能会下降或提高。
基本语法如下：

DROP INDEX index_name;

6.什么情况下要避免使用索引？

　　虽然索引的目的在于提高数据库的性能，但这里有几个情况需要避免使用索引。使用索引时，应重新考虑下列准则：
索引不应该使用在较小的表上。
索引不应该使用在有频繁的大批量的更新或插入操作的表上。
索引不应该使用在含有大量的 NULL 值的列上。
索引不应该使用在频繁操作的列上。

---

批注：
在表被删除的时候，表上附着的触发器、索引会被删除，而视图则不会被删除

---

参考引用：http://www.w3school.com.cn/