創建數據庫

在數據庫中創建表

向表中插入幾條數據

返回插入的內容

創建索引和視圖

得到一個表或者視圖的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修改前會將其寫入日志。