高级SQL语句
改变数据
insert record
- 插入一行
insert into foods (name, type_id) values ('Cinnamon Bobka', 1);
如果在insert语句中为每一列都提供了值，可以省去列名，顺序是表创建时的顺序。
insert into foods values(NULL, 1, 'Blueberry Bobka');
- 插入 a set of rows
子查询（subqueries）可以在insert语句中使用。既可以作为要插入值的一部分，也可以作为完整的要插入值。
insert into foods
values (null,
(select id from food_types where name='Bakery'),
'Blackberry Bobka');
作为一部分。
select * from foods where name like '%Bobka';
作为全部。
- 插入多列
insert into foods
select last_insert_rowid()+1, type_id, name from foods
where name='Chocolate Bobka';
这种创建方法(Create Table As Select),会丢失约束信息，包括自增列、indexes、UNIQUE约束等等。
update record
update table set update_list where predicate;
delete records
delete from table where predicate;
数据完整性(data integrity)
数据完整性是值创建和保持table之间的关系。有四种完整性
- domain integrity。列中的数据
- entity integrity。表中行的数据。
- referential integrity。表间列的关系，比如外键。
- user-defined integrity。catchall for everything else.
数据完整性通过约束实现。在SQLite中，数据完整性包含了对冲突解决的支持。
entity integrity
主值是为了保证每一个数据都可以被寻址。主值包括一列或更多列，其中有unique约束。
-
unique约束 -
primary key约束。创建table时候一定会被创建，无论是你是否定义。这个列名是rowid，有两个别名_rowid_和oid。
如果一列的约束是integer primary key，那么SQLite会为这列创建一个默认值，保证和其他行的值不同。这里也是rowid的别名。由于SQLite使用有符号64位整数来存储主值，因此主值的最大值是9，223，372，036，854，775，807。如果删除了某条记录，这个rowid会被循环利用。因此，新创建的rowid不一定是严格的递增顺序。
如果希望rowid为严格的递增，可以在
integer primary key后加上autoincrement关键字。-
如果主值定义中包括
autoinrement，那么rowid不会被重复利用，只会产生比当前最大值大的值。SQLite会保存最大的rowid在系统table中，叫做sqlite_sequence。如果大于int64max，在插入时会产生SQLITE_FULL错误。
sqlite> select * from sqlite_sequence;
name seq
maxed_out 10
sqlite> insert into maxed_out values(2120, 'works');
sqlite> insert into maxed_out values(null, 'works');
Error: database or disk is full
###Domain integrity
包括type和range两个方面。
- 默认值
`default`关键字提供了默认值。还有三个保留字
- current_time. (HH:MM:SS)格式
- current_data. (YYYY-MM-DD)格式
- current_timestamp. (YYYY-MM-DD HH:MM:SS)格式
- `NOT NULL`约束
- check constraints
`check`约束在任何更改生效之前被检查。触发器可以实现`check`约束的功能，并且可以做的更多。
###外键约束
create table table_name
( column_definition refernces foreign_table(column_name)
on {delete|update} integrity_action
[not] deferrable [initially {deferred|immeiate},]
...);
integrity_action有五种。
1. `set null`
2. `set default`
3. `cascade`
4. `restrict`
5. `no action
###Collation
Collation是指文本是如何比较的。
1. `binary`。 使用memcmp()函数比较
2. `nocase`. 大小写一样。
3. `reverse`. reverse of `binary` collation.
`collate`关键字定义了某一行的collation
##Storage class
1. integer. vary in size 1,2,3,4,6,8 bytes.SQLite会根据数值大小判断空间大小。
2. real. 有小数部分或指数部分，存储方式为8字节浮点数。
3. text. 支持各种字符编码(UTF8,UTF16)。
4. bolb. Binary large object。`x'ABCD'`开头。
5. NULL.
在一列中可以包含不同*storage class*的值。比较方式如下：
1. NULL最小。NULL之间没有特定顺序。
2. integer和real次之。
3. text次之。text之间比较由collation决定。
4. blob最大。使用`memcmp()`函数比较。
##Views
view是虚拟的表，也被称为*derived tables*，因为值是由其他表生成的，在使用时候被动态生成。
create view name as select-stmt
view在SQLite中不支持update，可以用触发器来达到同样效果。
##indexes(索引)
索引用来在某些情况下提高查询的速度。
索引会增加数据库的大小，因为拷贝了需要索引的列。
索引需要被维护。因此会降低插入、更新等操作的速度。
create index [unique] index_name on table_name (columns)
`unique`会增加约束给索引以及索引列。
`drop index index_name`
##触发器
create [temp|temporary] trigger name
[before|after] [insert|delete|update|update of columns] on table
action
- update触发器
可以为某几列指定触发条件。
`create trigger name [before|after] update of column on table action`
- 错误处理
`raise(resolution, error_message);`
resolution是冲突解决策略(`abort`,`fail`,`ignore`,`rollback`...)
- 可更新的view
trigger可以在view上用`instead of`关键字定义
##事务(transactions)
事务定义了一组SQL命令的边界，这组命令或者全部成功，或者全部失败。数据库完整性的`atomic`原则。
###transaction scopes
事务和三个命令有关
1. `begin`
2. `commit`
3. `rollback`
默认每一个SQL语句在自己的事务中运行，每一个语句都会成功或失败后回滚。这种操作类型被称为*autocommit mode*
SQLite支持`savepoint`和`release`命令。
`savepoint`用户在事务中设置一个点，SQLite可以revert到这个点。
savepoint justincase
rollback [transaction] to justincase
##冲突解决
以严格程度排序如下
1. `replace`.
- `unique`约束。移除旧的列，插入新的列。SQL操作继续进行。
- `not null`约束。 `NULL`值被默认值替代。如果没有默认值，使用`abort`策略。
- 删除时不会触发触发器。
2. `ignore`.约束被违反时，命令继续进行，触发违反行保持不变。
3. `fail`. 违反约束时，transaction结束，已经发生的改变不会被回滚。
4. `abort`. 恢复当前命令已经进行的操作，transaction结束。默认的策略。
5. `rollback`. abort当前命令和整个transaction。
`update or resolution table set (value_list) where predicate`
##数据库锁
锁和事务是紧密相关的。SQLite使用粗粒度的锁，当一个session在向数据库中写数据时，其他的session都被锁定。
1. `unlocked`
2. `shared`.读数据库时需要先获取`shared lock`
3. `reserved`.写数据之前要先获取`reserved lock`。获取`reserved lock`后，可以写数据了，但是不会被写入到磁盘中，会写入到缓存里。
4. `pending`. 写完数据后，要获取`exclusive lock`之前要获取`pending lock`。此时其他共享锁不能不被获取，可以继续读取。
5. `exclusive`. 其他所有的共享锁结束后，可以获取互斥锁。所有缓存的改变被写入内存。
###死锁
事务类型
1. `deferred`。不获取任何锁，直到必须获取 。(默认的)
2. `immediate`. 获取`reserved lock`当`begin`开始时。
3. `exclusive`.获取互斥锁在事务开始时。
当只有一个session时，使用默认的即可。
##数据库管理
###attaching databases
·attach [database] filename as database_name·
###cleaning Databases
- `reindex`. rebuild index.
- `vacuum`. 通过重建数据库文件来清除未使用空间。
##数据库配置
SQLite没有配置文件，所有的配置通过`pragmas`实现。
###数据库连接缓存大小
一个session可以在内存中缓存多少数据库页(pages).
sqlite> pragma cache_size = 10000;
sqlite> pragma cache_size;
10000
###获取数据库信息
- database_list
- index_info
- index_list
- table_info
###同步写
1. **FULL** 在关键时刻会暂停，保证数据已经被写入磁盘。如果操作系统崩溃或掉电，数据库仍会在重启后不会被破坏。
2. **Normal** 在大多数关键时刻会暂停。掉电后可能会被破坏。
3. **Off** 在把数据交给操作系统后会继续执行。可能会加快操作速度。如果程序崩溃，数据是安全的。如果操作系统崩溃或电脑掉电，数据库可能会被破坏。
###Temporary Storage
**temp_store:**, `default`,`file`或`memory`。临时数据的位置
**temp_store_directory:**,临时数据存放文件的位置。
###page size， encoding， autovacuum
必须在创建数据库之前设置。
###debugging
###system catalog
**sqlite_master**是包括表、view、index、trigger的系统表。
###查看查询计划(viewsing query plans)
sqlite> explain query plan select * from sqlite_master;
selectid order from detail
0 0 0 SCAN TABLE sqlite_master
