sqlite命令的使用

创建数据库

在数据库中创建表

向表中插入几条数据

返回插入的内容

创建索引和视图

得到一个表或者视图的DDL（schema信息）

查询当前数据库的sqlite_master表（即更详细的schema信息，记得使用命令.mode column和.headers on）

导出数据

将整个数据库导出为数据库定义语言（DDL）和数据操作语言（DML）

导入数据

如果文件由sql语句构成，则使用.read命令导入数据。

如果文件包含逗号或者其他分隔符的值，如CSV，可以使用.import命令，此命令解析指定的文件并将数据插入到指定的表中。它通过使用管道符作为分隔符来导入文件的每一行。可以使用.separator命令来指定作为分隔符的符号，使用.show查询当前分隔符的值。

导出数据到文件

.mode使用示例

sqlite> .mode list
sqlite> select * from stu;
1001|小龙|20
1002|hello|22
1003|world|23
sqlite> .mode line
sqlite> select * from stu;
number = 1001
  name = 小龙
   age = 20

number = 1002
  name = hello
   age = 22

number = 1003
  name = world
   age = 23
sqlite> 

将数据保存到.csv文件中(输出流，分隔符，查数据（查找到到数据会被存放到输出流对应的实体中，可以是一个文件，也可以是stdout）)

将csv文件中的数据导入到数据库当中

应用dump命令将输出重定向到文件

adogdeMacBook-Air:Desktop adog$ sqlite3 test02.db .dump > test.sql
adogdeMacBook-Air:Desktop adog$ ls
Snip20190521_15.png file3.csv           test01.db
file.csv            file3.html          test02.db
file2.csv           test.sql            文档
adogdeMacBook-Air:Desktop adog$ cat test.sql
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE stu(
number int,
name varchar(12),
age int
);
INSERT INTO stu VALUES(1001,'小龙',20);
INSERT INTO stu VALUES(1002,'hello',22);
INSERT INTO stu VALUES(1003,'world',23);
CREATE TABLE demo(
id integer not null primary key, 
‘name’ text not null, 
‘sex’ text not null, 
‘age’ integer not null
);
INSERT INTO demo VALUES(1,'mingming','男',20);
COMMIT;

文件test.sql现包含数据库test.db中所有可读的DDL和DML语句。

同样地，查询test02.db数据库中stu表里所有的记录

adogdeMacBook-Air:Desktop adog$ sqlite3 test02.db "select * from stu"
1001|小龙|20
1002|hello|22
1003|world|23

使用导出的sql文件test.sql创建数据库

adogdeMacBook-Air:Desktop adog$ sqlite3 test3.db < test.sql

备份数据库

sql转储也许是移植性最好的备份的方式，如

备份二进制数据库只是比复制文件稍微多做一点工作。备份之前需要先清理数据库，这样可以释放一些已删除对象不再使用的空间，数据库文件就会变小，二进制的副本也会较小。

adogdeMacBook-Air:Desktop adog$ sqlite3 test02.db vacuum
adogdeMacBook-Air:Desktop adog$ cp test02.db test.Backup

一般而言，备份二进制文件没有备份SQL移植性好。总体而言，SQLite具有良好的向后兼容性，并且所有平台上给定的数据库而是的二进制是兼容的。然而，对于长期备份，使用SQL形式才是长久之计。如果文件大小是一个大问题，SQL格式（原始文本）能产生较好的压缩比。

SQLite提供单增长值，可以使用SQLite内建的last_insert_rowid()验证这一点，该函数返回最后一个自增长值

sqlite> select last_insert_rowid();
184

上图这种NULL现象会粗发主键的自增长。

rowid

如果autoincrement了，则rowid不会自动回收了

typeof的用法

sqlite> .mode column                                                            
sqlite> .header on                                                              sqlite> select typeof(3.14),typeof('3.14'),typeof(314),typeof(x'3142'),typeof(NULL);
typeof(3.14)  typeof('3.14')  typeof(314)  typeof(x'3142')  typeof(NULL)
------------  --------------  -----------  ---------------  ------------
real          text            integer      blob             null        
sqlite> 

在SQLite中，一个字段存储的值可以有不同的类型

sqlite> create table domain(x);
sqlite> insert into domain(x) values(3.142);
sqlite> insert into domain(x) values('3.142');
sqlite> insert into domain(x) values(3142);
sqlite> insert into domain(x) values(x'3142');
sqlite> insert into domain(x) values(null);
sqlite> Select rowid,x,typeof(x) from domain;
rowid       x           typeof(x) 
----------  ----------  ----------
1           3.142       real      
2           3.142       text      
3           3142        integer   
4           1B          blob      
5                       null    

不同值类型存储在一个字段中

在SQLite中，具有不同存储类的值可以存储在同一个字段中。可以被排序，因为这些值可以相互比较。SQLite实现了完善的定义规则来做这件事。不同的存储类的值可以通过它们各自类的“类值”进行排序

索引

SQLite使用B-tree做索引。索引也会增加数据库的大小。进行Insert、update、和delete操作时，除了修改表，数据库也必须修改对应的索引。虽然索引可以加速查询，但是他们可能降低insert、update和类似操作的速度。

创建索引，其中columns是一个字段或以逗号分隔的多个字段。

在foods.name上创建大小写不敏感的索引，可以使用如下命令：

可以在shell中使用SQL命令.indices列出表中的索引。

多字段索引有更复杂的条件

sqlite> create table foo(a,b,c,d);
sqlite> create index foo_idx on foo(a,b,c,d);
sqlite> select * from foo where a=1 and b=2 and d=3;

只有第一个和第二个条件将使用索引。不使用第三个条件的原因是没有条件使用c去缩小到d的差距。

触发器

Create temp table log(x);
--创建触发器
create temp trigger stu_update_log after update of name on stu
begin
  insert into log values('updated stu: new name= ' || new.name);
end;
--开始事务
Begin;
Update stu set name='JUJYFRUIT' where rowid=185;
Select * form log;
--回滚事务
Rollback;

 其中rollbakck可以回滚事务到begin处。

创建视图触发器

事务

冲突解决

SQLite提供5种可能的冲突解决方案或策略

锁

SQLite有5种不同的锁状态：未加锁（unlocked）、共享（shared）、预留（reserved）、未决（pending）和排它（exclusive）。

最初的状态是未加锁状态，在此状态下，连接还没有访问数据库。当连接一个数据库甚至已经用BEGIN开始了一个事务时，连接都还处于未加锁状态。未加锁状态的下一个状态是共享状态。如果一个连接想要写数据库，它必须首先获得一个预留锁。一个数据库同时只能有一个预留锁，该预留锁可以与共享锁共存。一旦一个连接获得了预留锁，它就可以开始处理数据库修改操作了，尽管这些修改只能在缓冲区中进行，而不是实际写到磁盘，对读出内容所做的修改保存在内存缓冲区中。当连接想要提交修改时，需要将预留锁提升为排它锁。为了得到排它锁，还必须首先将预留锁提升为未决锁。获得未决锁之后，其他连接就不能再获得新的共享锁了，但已经拥有共享锁的连接可以继续正常读数据库。此时，拥有未决锁的连接等待其他拥有共享锁的连接完成工作并释放其共享锁。

一旦所有其他共享锁都被释放，拥有未决锁的连接就可以将其锁提升至排它锁，此时就可以自由地对数据库进行修改。所有以前所缓存的修改都会被写到数据库文件中。

死锁

假设的死锁情况

附加数据库

sqlite> attach database '/Users/adog/Desktop/test01.db' as test01_hi;
sqlite> .database
main: /Users/adog/Desktop/test02.db
temp: 
test01_hi: /Users/adog/Desktop/test01.db
sqlite> .table
demo           foo            temp.log     
domain         stu            test01_hi.stu

 其中test01_hi是附加的数据库

也可以分离数据库

数据库清理

SQLite有两个命令用于清理数据库  reindex和vacuum。reindex用于重建索引，它有如下两种形式：

第一种形式重建所有指定排序名称的索引。当要改变用户定义的排序行为（例如中文的多排序顺序）时才需要这种形式。要重建表中所有索引（或指定名称的索引），可以使用第二种形式的命令。vacuum通过重构数据库文件清理哪些未使用的空间如果存在处于开放状态的事务，vacuum不会执行。一种替代手动运行vacuum的方法是使用autovacuum。该功能可以通过auto_vacuum编译指示启用。

数据库配置

SQLite没有配置文件，它的所有配置参数都是用编译指示（pragma）来实现的。

 

临时存储器

临时存储器就是SQLite保存临时性数据，例如临时表、索引和其他对象的地方。默认情况下，SQLite使用内编译的位置，该位置根据平台不同而有所变化。有两个编译指示控制临时存储器：temp_store和temp_store_directory。

页大小、编码和自动清理

数据库页大小、编码和自动清理必须在创建数据库前设置。也就是说，要修改默认设置，在数据库中创建任何对象前都必须设置这些编译指示。SQLite支持的页大小范围是512字节到32768字节。SQLite使用的页面大小默认是1024B。

数据库的大小可以通过编译指示auto_vacuum自动保持在最小值。通常情况下，从数据库删除数据的事务提交时，数据库的大小不变。启用auto_vacuum时，当删除数据的事务提交时，数据库文件会缩小。为了支持这种功能，数据库内部需要存储额外的信息，这将导致数据库文件比不启用auto_vacuum的稍微大一点。vacuum命令对哪些使用auto_vacuum的数据库不起作用。

编译指示

系统目录

sqlite_master是系统表，它包含数据库中所有表、视图、索引和触发器信息。

sqlite> .headers on
sqlite> .mode col
sqlite> select type,name,rootpage from sqlite_master;
type        name        rootpage  
----------  ----------  ----------
table       stu         2         
table       demo        3         
table       domain      4         
table       foo         5         
index       foo_idx     6         
     

type字段说明对象的类型，name字段就是对象的名称，rootpage指对象的第一个B-tree页面在数据库文件中的位置。

使用explain query plan命令

连接和语句

B-tree和Pager

为代码说明SQLite执行一个查询的通用处理

参数绑定

事务生命周期

代码和事务都要考虑一些事情，首先要直到事务在哪些对象上运行。接着就是持续时间问题-----事务何时开始和何时结束，在什么时候开始开始影响其他连接？第一个问题与API直接相关，第二个问题一般与SQL和SQLite的特定实现相关

编译指示cache_size配置缓存

关于日志

当连接进入到保留状态时，pager初始化回滚日志。回滚日志是一个文件，用于回滚和故障恢复。具体地说，它拥有将数据库还原到事务开始之前的原来状态的数据库页。当B-tree修改页时，pager将这些数据库页都存放在日志文件。也就是说，日志拥有事务开始之前的一些数据库内容，从而，要撤销事务时，pager只是简单地将日志文件中的内容复制回数据库中。这样，数据库就还原到事务开始前的状态。

保留状态下，pager实际上管理三种页：已修改页、未修改页和日志页。已修改页是包含B-tree已改变记录的页，这些页存储在页缓存中。未修改页是B-tree读取但并未改变的页，它们是诸如select命令之类的结果。最后是日志页，它就是已修改页的原始版本。日志页不会存储在页面缓存中，但B-tree修改前会将其写入日志。