参考： http://www.runoob.com/sqlite/sqlite-tutorial.html @H_301_6@1. sqlite PRAGMA：可以用在 sqlite 环境内控制各种环境变量和状态标志。

@H_301_6@一个 PRAGMA 值可以被读取，也可以根据需求进行设置。

@H_301_6@（1）读取语法：只需要提供该 pragma 的名字

PRAGMA pragma_name;

@H_301_6@（2）设置语法：

PRAGMA pragma_name = value;

@H_301_6@（3）举几个例子：pragma.txt

@H_301_6@详情请参考：http://www.runoob.com/sqlite/sqlite-pragma.html

@H_301_6@pragma auto_vacuum; -- 这里是查看

@H_301_6@pragma cache_size;

@H_301_6@pragma case_sensitive_like;

@H_301_6@pragma count_changes;

@H_301_6@pragma database_list;

@H_301_6@pragma encoding;

@H_301_6@pragma freelist_count;

@H_301_6@pragma auto_vacuum = FULL; -- 这里是设置

@H_301_6@pragma cache_size = 10;

@H_301_6@pragma case_sensitive_like = true;

@H_301_6@pragma count_changes = true;

@H_301_6@2. sqlite 约束：约束是在表的数据列上强制执行的规则

@H_301_6@约束可以是列级或表级。列级约束仅适用于列，表级约束被应用到整个表

@H_301_6@（1）以下是在 sqlite 中常用的约束

• NOT NULL 约束：确保某列不能有 NULL 值。

• DEFAULT 约束：当某列没有指定值时，为该列提供默认值。

• UNIQUE 约束：确保某列中的所有值是不同的。

• PRIMARY Key 约束：唯一标识数据库表中的各行/记录。

• CHECK 约束：CHECK 约束确保某列中的所有值满足一定条件

@H_301_6@（2）primary key约束：

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

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

@H_301_6@　　在设计数据库表时，主键是很重要的。主键是唯一的 ID。

@H_301_6@　　在 sqlite 中，主键可以是 NULL，这是与其他数据库不同的地方。

@H_301_6@　　主键是表中的一个字段，唯一标识数据库表中的各行/记录。主键必须包含唯一值。主键列不能有 NULL 值。

@H_301_6@　　一个表只能有一个主键，它可以由一个或多个字段组成。当多个字段作为主键，它们被称为复合键。

@H_301_6@　　如果一个表在任何字段上定义了一个主键，那么在这些字段上不能有两个记录具有相同的值。

@H_301_6@（3）实例： constraint.txt

@H_301_6@create table tab_test
(
ID INT PRIMARY KEY NOT NULL,-- 主键 非空
NAME TEXT NOT NULL UNIQUE,-- 非空， 不相等
AGE INT CHECK(AGE > 0),-- check约束，AGE必须大于0
ADDRESS CHAR(50),
SALARY REAL DEFAULT 5000.00 -- 默认约束
);

@H_301_6@（4）删除约束：在 sqlite 中，ALTER TABLE 命令允许用户重命名表，或向现有表添加一个新的列。重命名列，删除一列，或从一个表中添加或删除约束都是不可能的。

@H_301_6@3. sqlite joins：用于结合两个或多个数据库中表的记录。JOIN 是一种通过共同值来结合两个表中字段的手段

@H_301_6@（1）主要有三种连接方式：

• 交叉连接 - CROSS JOIN

• 内连接 - INNER JOIN

• 外连接 - OUTER JOIN

@H_301_6@（2）为了练习需要新建一个表： create_department.txt

@H_301_6@create table department(

@H_301_6@ID INT PRIMARY KEY NOT NULL,

@H_301_6@DEPT CHAR(50) NOT NULL,

@H_301_6@EMP_ID INT NOT NULL );

@H_301_6@-- 插入一些数据

@H_301_6@insert into department values(1,'IT Billing',1);

@H_301_6@insert into department values(2,'Engineering',2);

@H_301_6@insert into department values(3,'Finance',7);

@H_301_6@select * from department;

@H_301_6@（3）交叉连接：把第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有 x 和 y 列，则结果表有 x*y 列。有时会特别庞大

@H_301_6@语法：

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

@H_301_6@（4）内连接 inner join：根据连接谓词结合两个表（table1 和 table2）的列值来创建一个新的结果表。

@H_301_6@查询会把 table1 中的每一行与 table2 中的每一行进行比较，找到所有满足连接谓词的行的匹配对。

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

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

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

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

@H_301_6@语法：

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

@H_301_6@（5）外连接outer join：虽然 sql 标准定义了三种类型的外连接：LEFT、RIGHT、FULL，但 sqlite 只支持左外连接（LEFT OUTER JOIN）。

@H_301_6@外连接（OUTER JOIN）声明条件的方法与内连接（INNER JOIN）是相同的，使用 ON、USING 或 NATURAL 关键字来表达。

@H_301_6@最初的结果表以相同的方式进行计算。一旦主连接计算完成，外连接（OUTER JOIN）将从一个或两个表中任何未连接的行合并进来，外连接的列使用 NULL 值，将它们附加到结果表中。

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

@H_301_6@SELECT ... FROM table1 LEFT OUTER JOIN table2 USING ( column1 ,... ) ...

@H_301_6@语法：

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

@H_301_6@（6）实例： join.txt

@H_301_6@-- 交叉连接，表1与表2的所有列进行一一匹配

@H_301_6@select EMP_ID,NAME,DEPT from company cross join department;

@H_301_6@-- 内连接，满足连接谓词时就生成一个新的结果

@H_301_6@select EMP_ID,DEPT from company inner join department on company.ID = department.EMP_ID;

@H_301_6@-- 左外连接，从一个或两个表中任何未连接的行合并进来，外连接的列使用 NULL 值，将它们附加到结果表中。

@H_301_6@select EMP_ID,DEPT from company left outer join department on company.ID = department.EMP_ID;

@H_301_6@结果：

@H_301_6@（7）比较：（不知道这样算不算正确，我也不是理解的太深刻）

@H_301_6@交叉连接后结果非常多，慎用

@H_301_6@左外连接会填充NULL，内连接不会

@H_301_6@4,sqlite Unions子句：用于合并两个或多个 SELECT 语句的结果，不返回任何重复的行。

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

@H_301_6@（1）UNION基本语法：

SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] UNION SELECT column1 [, table2 ] [WHERE condition]

@H_301_6@（2）union all基本语法：用于结合两个 SELECT 语句的结果，包括重复行。适用于 UNION 的规则同样适用于 UNION ALL 运算符。

SELECT column1 [, table2 ] [WHERE condition] UNION ALL SELECT column1 [, table2 ] [WHERE condition]

@H_301_6@（3）实例：unions.txt

@H_301_6@select EMP_ID,DEPT from company inner join department -- 内连接

@H_301_6@on company.ID = department.EMP_ID

@H_301_6@UNION

@H_301_6@select EMP_ID,DEPT from company left outer join department -- 左外连接

@H_301_6@on company.ID = department.EMP_ID;

@H_301_6@select EMP_ID,DEPT from company inner join department -- 内连接

@H_301_6@on company.ID = department.EMP_ID

@H_301_6@UNION ALL

@H_301_6@select EMP_ID,DEPT from company left outer join department -- 左外连接

@H_301_6@on company.ID = department.EMP_ID;

@H_301_6@5. sqlite NULL值：

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

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

@H_301_6@（1）带有 NULL 值的字段在记录创建的时候可以保留为空。

@H_301_6@（2）NULL 值在选择数据时会引起问题，因为当把一个未知的值与另一个值进行比较时，结果总是未知的，且不会包含在最后的结果中。

@H_301_6@6. sqlite 别名：暂时把表或列重命名为另一个名字，这被称为别名。

重命名是临时的改变，在数据库中实际的表的名称不会改变。

列别名用来为某个特定的 sqlite 语句重命名表中的列。

（1）语法：

表别名的基本语法如下：

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

列别名的基本语法如下：

SELECT column_name AS alias_name
FROM table_name
WHERE [condition];

@H_301_6@（2）实例：

@H_301_6@select C.ID as company_ID,C.NAME as company_NAME,D.DEPT as department_DEPT -- 这里是给列取别名
from company as C,department as D -- 这里是给表取别名
where C.ID = D.EMP_ID; - 并且使用

@H_301_6@（3）效果：

@H_301_6@7. sqlite触发器： Trigger sqlite 的触发器是数据库的回调函数，它会自动执行/指定的数据库事件发生时调用。

@H_301_6@（1）要点：

• 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() 可用于触发器程序内抛出异常。

@H_301_6@（2）语法：

CREATE  TRIGGER trigger_name [BEFORE|AFTER] event_name ON table_name BEGIN -- Trigger logic goes here.... END;

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

@H_301_6@以update为例：

CREATE  TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name ON table_name BEGIN -- Trigger logic goes here.... -- 这里是执行update时的操作，可以写进一张表里 END; 

@H_301_6@（3）实例：以插入数据为例：

@H_301_6@先创建一张表，存储插入信息： create_audit.txt

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

@H_301_6@再创建触发器： trigger.txt

@H_301_6@create trigger audit_log after insert
on company
begin
insert into audit (EMP_ID,ENIRY_DATA) values(new.ID,datetime('now')); -- 每次插入数据，就往audit中插入一条数据
end;

@H_301_6@注意： new代表新的， 还可以用old，表示操作之前的信息。

@H_301_6@这个例子中，如果insert换成delete，插入时就必须用 old.ID了，因为记录已经被删除了，new已经没有意义了。

@H_301_6@再把company表中的数据清空：

@H_301_6@delete from company;

@H_301_6@最后运行插入数据： insert_company.txt

@H_301_6@可以看到audit中的数据：

@H_301_6@（4）列出 触发器：列出所有的触发器

@H_301_6@select name from sqlite_master where type = 'trigger'; -- 列出所有的触发器

@H_301_6@select name from sqlite_master where type = 'trigger' AND tbl_name='company'; -- 找出特定表中的触发器

@H_301_6@（5）删除触发器：drop命令

@H_301_6@如： drop trigger trigger_name;

@H_301_6@8.sqlite 索引：是一种特殊的查找表，数据库搜索引擎用来加快数据检索

@H_301_6@索引是一个指向表中数据的指针。一个数据库中的索引与一本书后边的索引是非常相似的。

@H_301_6@索引有助于加快 SELECT 查询和 WHERE 子句，但它会减慢使用 UPDATE 和 INSERT 语句时的数据输入。

@H_301_6@索引可以创建或删除，但不会影响数据。

@H_301_6@（1）基本语法

CREATE INDEX index_name ON table_name;

@H_301_6@（2）

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

CREATE INDEX index_name
ON table_name (column_name);

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

CREATE INDEX index_name
on table_name (column_name);

@H_301_6@组合索引：组合索引是基于一个表的两个或多个列上创建的索引

CREATE INDEX index_name
on table_name (column1, column2);

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

@H_301_6@（3）实例： index.txt

@H_301_6@在company的SALARY上建立索引：

@H_301_6@CREATE INDEX salary_index ON COMPANY (SALARY);

@H_301_6@（4）实例： find_index.txt

@H_301_6@.indices company -- .indices命令 列出company表上可用的所有的索引

@H_301_6@select * from sqlite_master where type='index'; -- 列出数据库范围内的所有索引

@H_301_6@（5）删除索引： drop index index_name;

@H_301_6@（6）什么情况下要避免使用索引：

• 索引不应该使用在较小的表上。

• 索引不应该使用在有频繁的大批量的更新或插入操作的表上。

• 索引不应该使用在含有大量的 NULL 值的列上。

• 索引不应该使用在频繁操作的列上。

@H_301_6@9、sqlite index by："INDEXED BY index-name" 子句规定必须需要命名的索引来查找前面表中值

@H_301_6@如果索引名 index-name 不存在或不能用于查询，然后 sqlite 语句的准备失败。

@H_301_6@（1）语法：

SELECT|DELETE|UPDATE column1, column2... from table_name INDEXED BY (index_name） WHERE (CONDITION);

@H_301_6@（2）、实例：indexby.txt

@H_301_6@create index salary_index on company(SALARY); -- 创建索引
select * from company INDEXED BY salary_index where SALARY > 4000; -- 利用索引进行查询

@H_301_6@