>第三章介绍了select语句，本章介绍SQL语言的其它功能，包括修改数据的insert 、update 和delete，保护数据的约束，以及更高级的主题

(一)修改数据

1. 插入数据
   insert ： insert into table (column_list) values (value_list);

• 标准用法 insert into foods (name, type_id) values ('Clinsmon Bobka',1);
• 省略列写法insert into foods values (NULL,1,'Clinsmon Bobka');全字段赋值顺序和建表顺序一致
• value值可以使用查询产生insert into foods values (NULL,()select id from food_types where name = 'Bakery'),'Blackberry Bobka');
• 插入多行 insert into foods2 select * from foods; food1和food2表结构相同（也可以在创建foods2是一步完成 create table foods2 as select * from foods;）

2. 更新记录
   update: update table set update_list where predicate

• 标准用法 update foods set name = 'CHOCOLATE BOBKA' where name='Chocolate Bobka';

3. 删除记录
   delete: delete from table where predicate

• 标准用法 delete from foods where name='Chocolate Bobka';

(二)数据完整性

数据完整性用于定义和保护表内或表之间的数据关系。一般有四种完整性：域完整性、实体完整性、引用完整性、用户定义完整性。

1. 实体完整性

• 唯一性约束： unique
• 主键约束 ： primary key 不设置主键系统会默认设置 （autoincrement 自增主键）（64bit单符号整数）（1非自增到达最大值后会使用空闲id值 2自增 最大值后插入失败）

2. 域完整性（类型检查 范围检查）

* 默认值 default   default current_date/default current_time 插入当前时间
* not null 约束  对应自定为空插入失败或更新失败
* check约束 满足check条件才可以插入或更新字段值 check（length(phone)>=7）

3. 引用完整性-外键约束

>SQLite支持关系理论中的关系完整性概念。通过外键约束支持这个概念，它确保了一个表中的关键值必须从另一个表中引用，且该数据必须在另一个表中实际存在（也就是需要维护）

type_id integer references food_type(id) on delete restrict 新建表中的type_id字段引用food_types表中id字段使用delete restrict 阻止任何删除行为。如果food_type中删除某行，将导致foods表中id没有父id的存在，完整性的规则：
* set null 父值被删除或不存在剩余子值将改为null
* set default 复制删除或不存在剩余的子值将修改默认值
* cascade 更新父值 更新所有匹配的子值 删除父值 删除所有对应的子值 cascade的删除功能可能会出现意想不到的结果
* restrict 更新或删除父值可能会出现孤立的子值 从而阻止事务的执行
* no action 使用一种松弛的方法 不干涉执行 只观察变化 在整个语句的结尾报出错误

4. 排序规则
   • 默认二进制排序 使用C函数memcmp()逐字节比较文本值
   • nocase ASC码字符的非大小写敏感排序算法
   • reverse 与二进制排序相反
   • SQLite的C API提供自定义排序规则
5. 存储类

SQLite有5个原始的数据类型 integer、 real、text、 bobl、NULL。通过表示法来判断其类型

 * 语句中使用单括号或双括号起来的文字被指派为text
 * 如果文字是􏰁用引号括起来的数据，并且没有小数点和指数，被指派为 INTEGER。
 * 如果文字是􏰁用引号括起来的数据，并且带有小数点或指数，被指派为 REAL 。
 * 用 NULL 说明的值被指派为 NULL 存储类。
 * 如果一个值的格式为 X'ABCD'，其中 ABCD 为 16 进制数字，则该值被指派为 BLOB。

存储类的类值的问题（不同类型的值需要排序）
* NULL 存储类具有最低的类值。一个具有 NULL 存储类的值比所有其它值都小(包括其它 具有 NULL 存储类的值)。在 NULL 值之间，没有特别的可排序值。
* INTEGER 或 REAL 存储类值高于 NULL，它们的类值相等。INTEGER 值和 REAL 值通 过其数值进行比较。
* TEXT 存储类的值比 INTEGER 和 REAL 高。数值永远比字符串的值低。当两个 TEXT 值 进行比较时，其值大小由“排序法”决定。
* BLOB 存储类具有最高的类值。具有 BLOB 类的值大于其它所有类的值。BLOB 值之间在 比较时使用 C 函数 memcmp()。
X 前缀大小写皆可。

6. 索引

索引是一种用来在某种条件下加速查询的结构 其实现依赖B-tree树
create index [unique] index_name on table_name (columns)

 * 排序规则 
 * 使用索引 多字段索引按顺序 比如abcd 其中abd建立了索引 这样只有ab可以用索引 cd就不能使用了

7. 触发器

当具体的表发生特定的数据库事件时，触发器质性对应的SQL命令
* 触发器创建 create [temp|temporary] trigger name [before|after] [insert|delete|update of columns] ontable action
* 更新触发器 create trigger name [before|after] update of column on table action
* reverse 与二进制排序相反
* SQLite的C API提供自定义排序规则

(三)事务

定义一组操作，整体执行或整体都不执行 由begin commit roolback三个指令组成。默认情况下每条sql语句自成事务

违反约束的五个解决方案:REPLACE、IGNORE、FAIL、ABORT 和 ROLLBACK。

1. REPLACE: 当发违反了唯一完整性，SQLite 将造成这种违反的记录删除，替代以新插
   入或修改的新记录，SQL 继续执行，不报错。
2. IGNORE：当约束违反发生时，SQLite允许命令继续执行，违反约束的行保持不变和它之前和之后的记录都会继续修改且不报错。
3. FAIL: 当约束违反发生时，SQLite终止命令，但是补恢复约束违反之前已经修改的记录，
4. ABORT：当约束违反发生时，SQLite恢复命令所做的所有改变并终止命令。但是不会影响事务的其它指令
5. ROLLBACK：当约束违反发生时，SQLite执行回滚 ---终止当前命令和整个事物，最终结果就是当前命令所做的改变和事务中之前命令的改变都会被回滚。

冲突解决策可以在表和索引的定义中执行
冲突解决策可以insert 或update命令后面

insert or resolution into table (column_list) values (values_list);

(四)数据库锁

SQLite 使用锁逐步上升 机制，为了写数据库，连接需要逐级地获得排它锁。 SQLite 有 5 个 不同的锁状态:􏰁未加锁(UNLOCKED )、共享(SHARED )、保留 (RESERVED)、􏰁未决(PENDING )和排它(EXCLUSIVE)。

1. 未加锁 没有链接连接数据库时就是未加锁的状态
2. 共享锁 当有连接读取数据库时需要拿到共享锁才能读取 共享锁可以被多个连接共有
3. 保留锁 如果有连接需要写数据库的操作 需要获取到保留锁 保留锁只允许一个连接持有 保留锁可以和共享锁共存（保留锁不阻止连接获取共享锁）
4. 未决锁 一旦一个连接获取到了保留锁之后就可以开始处理数据库操作了，尽管这些修改暂时只能在缓存区进行没有写到磁盘中。如果想要修改的数据提交到磁盘中就需要升级保留锁为排它锁，为了得到排它锁，还必须首 先将保留锁提升为未􏰁决锁。获得􏰁未决锁之后，其它连接就不能再获得新的共享锁了，但已经 拥有共享锁的连接仍然可以继续正常读数据库。此时，拥有􏰁决锁的连接等待其它拥有共享 锁的连接完成工作并释放其共享锁。
5. 一旦所有其它共享锁都被释放，拥有􏰁未决锁的连接就可以将其锁提升至排它锁，此时就可以 自由地对数据库进行修改了。所有以前对缓冲区所做的修改都会被写到数据库文件。

死锁

死锁产生的原因：多个事务需要进行写操作，但又都不愿意放弃手中已获取到的锁（共享锁）。使用正确的事务类型来避免死锁的产生 。 有三种不同的事务，使用不同的锁状态。事务可以开始于:DEFERRED、MMEDIATE 或 EXCLUSIVE。事务类型在 BEGIN 命令中指定:
BEGIN [ DEFERRED | IMMEDIATE | EXCLUSIVE ] TRANSACTION;

• DEFERRED 事务不获取任何锁(直到它需要锁的时候)，BEGIN 语句􏰀身也不会做什么 事情——它开始于 UNLOCK 状态。默认情况下就是这样的，如果仅仅用 BEGIN 开始一个 事务，那么事务就是 DEFERRED 的，同时它不会获取任何锁，当对数据库进行第一次读操 作时，它会获取 SHARED 锁;同样，当进行第一次写操作时，它会获取 RESERVED 锁。
• IMMEDIATE BEGIN 开始的 IMMEDIATE 事务会尝试 获取 RESERVED 锁 。如果 成功，BEGIN IMMEDIATE 保证没有别的连接可以写数据库。但是，别的连接可以对数据库进行读操作; 但是，RESERVED 锁会阻止其它连接的 BEGIN IMMEDIATE 或者 BEGIN EXCLUSIVE 命 令，当其它连接执行上述命令时，会返回 SQLITE_BUSY 错误。这时你就可以对数据库进 行修改操作了，但是你还不能提交，当你 COMMIT 时，会返回 SQLITE_BUSY 错误，这意 味着还有其它的读事务没有完成，得等它们执行完后才能提交事务。
• EXCLUSIVE 事务会试着获取对数据库的 EXCLUSIVE 锁。这与 IMMEDIATE 类似，但是一 旦成功，EXCLUSIVE 事务保证没有其它的连接，所以就可对数据库进行读写操作了。 上节那个例子的问题在于两个连接最终都想写数据库，但是它们都没有放弃各自原来的锁， 最终，SHARED 锁导致了问题的出现。如果两个连接都以 BEGIN IMMEDIATE 开始事务， 那么死锁就不会发生。在这种情况下，在同一时刻只能有一个连接进入BEGIN IMMEDIATE， 其它的连接就得等待。BEGIN IMMEDIATE 和 BEGIN EXCLUSIVE 通常被写事务使用。就 像同步机制一样，它防止了死锁的产生。

基􏰀的准则是:如果你正在使用的数据库没有其它的连接，用 BEGIN 就足够了。但是，如 果你使用的数据库有其它的连接也会对数据库进行写操作，就得使用 BEGIN IMMEDIATE 或 BEGIN EXCLUSIVE 开始你的事务。

附加数据库

SQLite允许使用attach命令将多个数据库 附加到当前连接

attach [database] filename as database_name;

数据库清理

两个数据库清理指令

reindex collation_name;
reindex table_name|index_name;//重建索引

vacuum重建数据库清理没有使用的空间

临时存储器

临时表 索引 和其它对象

查看查询计划

explain query plan 查看sqlite执行查询时访问处理表与数据的具体步䠫
explain query plan select * from foods;