[TOC]
SQL語句
表相關(guān)語句
創(chuàng)建表
**create [temp|temporary] table [if not exists] table_name(column_definition, ... [, constraints]);**
eg: create table contacts (id integer primary key, name text not null collate nocase, phone text not null default 'UNKNOWN', unique (name, phone));
temp, temporary:表示創(chuàng)建的表是臨時表,只在當(dāng)前會話有效,數(shù)據(jù)庫連接斷開時,該表被自動銷毀.
column_definition:字段定義由列名,域,字段約束組成.
constraints:表一級的約束, unique (name, phone)定義在字段name和phone上.
修改表
**alert table table_name {rename to name | add column column_definition};**
rename to name:重新對表命名;
add column column_definition:新增一列;
另外:SQLite不支持對表字段的重命名,刪除,修改操作.
刪除表
**drop table table_name [if exists]**
查詢數(shù)據(jù)
select
**select [distinct] heading from tables where predicate group by columns having predicate order by columns limit count1 offset count2;**
select語句最常見的語法是select heading from tables where predicate;
distinct:表示是否去除重復(fù)數(shù)據(jù),同時此處還可以使用top count表示取前count的數(shù)據(jù);
where子句需要注意的是like和glob操作符,like使用%(多個)和(單個)作為通配符,大小寫不敏感,glob使用*(多個)和(單個)作為通配符,大小寫不敏感;
limit count1 offset count2 等價于 limit count2, count1.
多表連接:
**select heading from left_table join_type right_table on join_condition**
內(nèi)連接:
隱式實現(xiàn):select * from foods, food_types where foods.id = food_types.food_id;
顯示實現(xiàn):select * from foods inner join food_types on foods.id = food_types.food_id;
左外連接:
select * from foods left join food_types on foods.id = food_types.food_id;
右外連接:
select * from foods right join food_types on foods.id = food_types.food_id;
全外連接:
select * from foods cross join food_types;
SQLite不支持右外連接和全外連接,右外連接可以使用左外連接代替,而全外連接可以通過復(fù)合查詢來實現(xiàn).
子查詢
子查詢是指select語句中嵌套select語句.子查詢可以用在一般表達(dá)式不能使用的地方,也可以應(yīng)用在select語句中的各個位子.
- select count(*) from foods where type_id in (select id from food_types where name = 'A');
- select name, (select count(id) from foods_episodes where food_id = f.id) count from foods f order by count desc limit 10;
- select * from foods f order by (select count(type_id) from foods where type_id = f.type_id) desc;
- select f.name, types.name from foods f inner join (select * from food_types where id = 6 ) types on f.type_id = types.id;
- etc.
復(fù)合查詢
復(fù)合查詢使用三種特殊的關(guān)系操作符(聯(lián)合,交叉連接和差集)處理多個查詢的結(jié)果,在SQLite中聯(lián)合-union,交叉連接-intersect,差集-except.復(fù)合查詢的關(guān)系是從左到右處理的,同時復(fù)合查詢需要滿足以下條件:
涉及的關(guān)系的字段數(shù)目必須相同;
-
只能有一個order by子句,并且在復(fù)合查詢的最后,對復(fù)合查詢的結(jié)果進(jìn)行排序.
union操作輸入兩個關(guān)系: A和B, 將兩者聯(lián)合成一個只包含A和B中非重復(fù)字段的單一關(guān)系.默認(rèn)情況下,union會消除重復(fù)數(shù)據(jù),如果想要保留結(jié)果中的重復(fù)數(shù)據(jù),可以使用union all.
intersect操作輸入兩個關(guān)系: A和B, 選擇那些既在A也在B的行.
因為復(fù)合查詢只允許句末有一個order by,可以通過子查詢繞過這一限制,子查詢獨立于復(fù)合查詢的運行.
except操作輸入兩個關(guān)系: A和B,找出所有在A但不在B的行.
條件結(jié)果
case表達(dá)式允許在select語句中處理各種情況,case表達(dá)式中,只執(zhí)行一個條件,如果滿足的條件超過一個,也只執(zhí)行第一個;如果沒有滿足條件,同時沒有定義else條件,case返回null.它有兩種形式:
- case value
when x then value_x
when y then value_y
when z then value_z
else default_value
接收靜態(tài)值并列出各種情況下的case返回值.
- case value
when condition1 then value1
when condition2 then value2
when condition3 then value3
else default_value
在when使用表達(dá)式,并返回不同情況下的返回值.
null
null是一種支持"未知"或"不可知"的特殊占位符,表示缺失信息,本身不是值. null表示該位置沒有值, null不是什么其他值, null不是真,不是假,不是零,也不是空字符串,null就是null.
- 為了在邏輯表達(dá)式中使用null, SQLite使用三值(三態(tài))邏輯, null是真假值之一;
- 可以通過is null 或者 is not null操作符檢測null是否存在;
- null不等于其他任何值,包括null,不可以將null與其他值進(jìn)行比較, null不會大于或者小于,甚至等于其他任何null.
修改數(shù)據(jù)
insert
**insert into table_name(column_list) values(value_list);**
使用select形式的insert可以一次插入多行數(shù)據(jù), eg: insert into foods2 select * from foods;只要字段匹配,就可以插入select結(jié)果集中的所有行.
??定義為unique的字段插入重復(fù)值時.SQLite會停止報錯(沖突,停止報錯是默認(rèn)操作).
??可以將創(chuàng)建表和插入表的兩步操作合并為一步:create temp table foods2 as select * from foods;但使用這種形式時,源表中的任何約束都不會定義在新表中,自增長字段不會在新表中創(chuàng)建,索引也不會創(chuàng)建,unique約束等也不會創(chuàng)建.
update
**update table_name set update_list where predicate;**
delete
**delete from table_name where predicate;**
數(shù)據(jù)完整性
數(shù)據(jù)完整性用來定義和保護(hù)表內(nèi)和表之間的數(shù)據(jù)關(guān)系闹司。數(shù)據(jù)完整性有四種：域完整性错维，實體完整性，引用完整性（外鍵關(guān)系）任内，用戶自定義完整性赎瞎。數(shù)據(jù)完整性是通過約束實現(xiàn)的牌里。
域完整性涉及控制字段內(nèi)的值，實體完整性涉及表中的行务甥，引用完整性涉及表之間的行牡辽，實際上就是外鍵關(guān)系，用戶自定義完整性則十分廣泛敞临。
約束
- unique 唯一性約束
在SQLite中unique約束的字段可以插入任意多個null态辛，但是在某些數(shù)據(jù)庫中只允許插入一個null，甚至DB2禁止插入null哟绊。
- primary key 主鍵約束
主鍵是一個64bit的整型字段因妙，SQLite為主鍵提供自增長功能，當(dāng)插入數(shù)據(jù)時未給定值時票髓，SQLite會創(chuàng)建默認(rèn)值攀涵，并確保該值是唯一的。組件的最大值為2^63 - 1 (9223372036854775807)洽沟。
主鍵并不是嚴(yán)格的順序增長以故，當(dāng)刪除表中的數(shù)據(jù)時，后面的新增操作可能會使用回收的主鍵使用裆操，如果需要使用唯一的自動主鍵值怒详，而不是“填補空白”，需要在主鍵定義integer primary key后加上關(guān)鍵字autoincrement踪区。
當(dāng)主鍵值沒有可用的時插入新的數(shù)據(jù)時昆烁，SQLite會返回SQLITE_FULL錯誤。
- default 默認(rèn)值
- not null 非空約束
- check約束
check約束允許定義表達(dá)式來測試要插入或者更新的字段值缎岗，如果值不滿足設(shè)定的表達(dá)式標(biāo)準(zhǔn)静尼，SQLite會報違反約束錯誤。
- 外鍵約束
排序規(guī)則
排序規(guī)則涉及文本值如何比較传泊。SQLite有三種排序規(guī)則：默認(rèn)的是二進(jìn)制排序規(guī)則鼠渺，該規(guī)則使用C函數(shù)memcmp()逐字節(jié)比較文本值，大小寫敏感眷细；nocase是拉丁字母中使用的26個ASCII字符的非大小寫敏感排序算法拦盹；reverse排序規(guī)則，與二進(jìn)制排序規(guī)則相反溪椎。
存儲類
SQLite通過值的表示法來判斷其類型普舆，SQL函數(shù)typeof()根據(jù)值的表示法返回其存儲類恬口。
不同存儲類的值通過它們各自類的“類值”進(jìn)行排序，規(guī)則如下：
- null存儲類具有最低的類值沼侣，在null值之間楷兽，沒有具體的排序順序；
- integer或real存儲類值高于null华临，它們的類值相等芯杀，integer和real通過其數(shù)值大小比較；
- text存儲類值比integer和real高雅潭，即數(shù)值永遠(yuǎn)比字符串的值低揭厚，當(dāng)兩個text值進(jìn)行比較時，其值大小由該值定義的排序法決定扶供；
- blob存儲類具有最高的類值筛圆。blob之間的比較使用C函數(shù)memcmp()。
視圖
視圖椿浓，虛擬表太援，也稱為派生表，因為視圖的內(nèi)容都派生自其他表的查詢結(jié)果扳碍。視圖的內(nèi)容與基本表一樣提岔，但是基本表的內(nèi)容是持久的，而視圖的內(nèi)容是使用時動態(tài)產(chǎn)生的笋敞。
**create view view_name as select-stmt;**
**drop view view_name;**
視圖的內(nèi)容是動態(tài)生成的碱蒙，當(dāng)每次使用視圖時，會基于數(shù)據(jù)庫的當(dāng)前數(shù)據(jù)執(zhí)行相關(guān)的sql語句夯巷，產(chǎn)生視圖內(nèi)容赛惩。
SQLite不支持可更新的視圖，即不能對視圖進(jìn)行insert或者update操作趁餐。如果需要喷兼，可以使用觸發(fā)器實現(xiàn)類似的功能。
索引
索引是一種用來在某種條件下加速查詢的結(jié)構(gòu), SQLite使用B-tree實現(xiàn)索引后雷。
索引的缺點：
- 索引會增加數(shù)據(jù)庫的大小;
- 索引的維護(hù)是自動的季惯，進(jìn)行insert，update喷面，delete操作時星瘾，除了修改表走孽，數(shù)據(jù)庫還會對相應(yīng)的索引進(jìn)行修改惧辈，因此雖然索引可以加速查詢，但是降低了insert磕瓷，update盒齿，delete操作的性能念逞。
**create index [unique] index_name on table_name(columns);**
**drop index index_name;**
排序規(guī)則
索引中的每個字段都有相關(guān)的排序規(guī)則，當(dāng)需要索引中的字段按照大小寫無關(guān)排序時可以使用：create index [unique] index_name on table_name(column_name collate nocase)边翁。
索引使用
當(dāng)where子句中出現(xiàn)下列表達(dá)式中翎承，SQLite將使用單個字段索引：
column {= | > | < | <= | >=} expression
expression {= | > | < | <= | >=} column
column in (expression-list)
column in (subquery)
SQLite多字段索引的處理邏輯是從左到右智能地使用字段，即從左邊的字段開始符匾，查詢使用字段的條件叨咖，然后移動到第二個字段，以此類推啊胶，直到where子句中無法找到有效的條件甸各。
eg: create index index_name on table_name(a, b, c, d);
- select * from index_name where a = 1 and b = 1 and d = 1;
此時只有a, b使用索引，d沒有使用索引是因為沒有條件使用c來縮小到d的差距焰坪； - select 8 from index_name where a > 1 and b =1 and c = 1 and d = 1;
只有a使用索引趣倾，a > 1被成為最右邊的索引字段，因為表達(dá)式使用了不等號某饰，在此之后的所有字段作為結(jié)果都是不合法的儒恋； - select 8 from index_name where a = 1 and b >1 and c = 1 and d = 1;
a, b使用了索引，原因同上黔漂。
觸發(fā)器
當(dāng)具體的表發(fā)生特定的數(shù)據(jù)庫事件時诫尽，觸發(fā)器執(zhí)行相應(yīng)的sql命令。
**create [temp | temporary] trigger name [before | after] [insert | delete | update | update of columns] on table action;**
觸發(fā)器可以用來創(chuàng)建自定義完整性約束炬守，日志改變箱锐，更新表和其它事情。
事務(wù)
事務(wù)定義了一組sql命令的邊界劳较，這組命令或者作為一個整體全部執(zhí)行驹止，或者都不執(zhí)行，這被成為數(shù)據(jù)庫完整性的原子性原則观蜗。
事務(wù)的范圍
事務(wù)由3個命令控制: begin, commit, rollback臊恋。
begin開始一個事務(wù)，begin之后的所有操作都可以取消墓捻，如果連接終止前沒有發(fā)出commit抖仅，begin之后的操作會被取消;
commit提交事務(wù)開始后的所有已執(zhí)行的操作;
rollback還原事務(wù)開始后的所有已執(zhí)行的操作。
SQLite還支持savepoint和release命令砖第，用來擴展事務(wù)的靈活性撤卢。
可以創(chuàng)建多個savepoint，命令savepoint savepoint_name;
當(dāng)不需要回滾整個事務(wù)時梧兼，使用rollback [transaction] to savepoint_name;來回滾到特定的節(jié)點放吩。
沖突解決
SQLite有獨特的方法允許指定不同的方式來處理約束違反(或者說從約束違反中恢復(fù))，這種功能叫做沖突解決羽杰。SQLite默認(rèn)行為是終止命令并回滾所有的修改渡紫，保存事務(wù)的完整性到推。
SQLite提供5種可能的沖突解決方法或策略：replace, ignore, fail, abort, rollback。
5種方法定義了錯誤容忍范圍或敏感度惕澎，從最寬松的replace莉测，到最嚴(yán)格的rollback。
- replace:當(dāng)違反了唯一性約束時,SQLite將原始數(shù)據(jù)刪除,插入新的數(shù)據(jù)替代,sql繼續(xù)運行且不會報錯;當(dāng)違反了not null約束時,使用該字段的默認(rèn)值代替null,如果該字段沒有默認(rèn)值,SQLite應(yīng)用abort策略.
當(dāng)沖突解決策略為了滿足約束而刪除記錄時,該行的刪除觸發(fā)器不會觸發(fā);
- ignore:當(dāng)約束違反發(fā)生時,SQLite允許命令繼續(xù)執(zhí)行,違反約束的行保持不變,其他命令繼續(xù)執(zhí)行;
- fail:當(dāng)約束違反發(fā)生時,SQLite終止命令,但不恢復(fù)約束違反之前已經(jīng)執(zhí)行的記錄,即在約束違反之前發(fā)生的改變都保存;
fail需要注意的是數(shù)據(jù)更新的操作順序是不確定的,無法確定SQLite以何種順序處理它們.因此在大多數(shù)情況下,使用ignore會更好.
- abort:當(dāng)約束違反發(fā)生時,SQLite恢復(fù)命令所做的所有改變并終止命令;
abort是SQLite中所有操作的默認(rèn)沖突解決方法,也是sql標(biāo)準(zhǔn)定義的行為.
- rollback:當(dāng)約束違反發(fā)生時,SQLite執(zhí)行回滾,終止當(dāng)前命令和整個事務(wù),最終的結(jié)果是當(dāng)前命令所做的改變以及事務(wù)中約束違反之前命令造成的改變都被回滾.
rollback是最嚴(yán)格的沖突解決方法,單個約束違反導(dǎo)致事務(wù)中執(zhí)行的所有操作都回滾.
沖突解決方法可以在sql命令中指定,也可以再表和索引的定義中指定,即沖突解決方發(fā)可以在insert,update,create table和create index中指定.
沖突解決方法在inset和update中的語法:
**insert or resolution into table(column-list) values(value-list);**
**update or resolution table set(column-list) where predicate;**
沖突解決方法緊跟在insert或update命令后面,并在之間加入or, insert or replace表達(dá)式可以縮寫成replace.
沖突解決方法是語句級(DML)的,可以覆蓋對象級(DDL)定義的.eg:
create table a(name text unique on conflict rollback);
insert or replace into a values('a');
對象級(DDL):表a中的name字段,該字段的沖突解決方法;
語句級(DML):insert or replace命令中的replace;
因此,replace沖突解決方法會覆蓋a.name中定義的rollback沖突解決方法.
數(shù)據(jù)庫鎖
SQLite使用鎖逐步提升機制,讀數(shù)據(jù)庫只需要獲得共享鎖,而為了寫數(shù)據(jù)庫,連接需要主機獲取排它鎖.SQLite有5種鎖狀態(tài):未加鎖(unlocked),共享(shared),預(yù)留(reserved),未決(pending),排它鎖(exclusive),每個數(shù)據(jù)庫連接在同一時刻只能處于一種狀態(tài),除未加鎖狀態(tài)外,其它每一種狀態(tài)都有一種鎖與之對應(yīng).
- 最初的狀態(tài)是未加鎖狀態(tài),在此狀態(tài)下,連接還沒有訪問數(shù)據(jù)庫,當(dāng)連接一個數(shù)據(jù)庫,甚至已經(jīng)用begin開始了一個事務(wù)時,連接都還處于未加鎖狀態(tài);
- 未加鎖狀態(tài)的下一個狀態(tài)是共享狀態(tài),為了能夠在數(shù)據(jù)庫中讀數(shù)據(jù),連接必須進(jìn)入共享狀態(tài),即獲得一個共享鎖.多個連接可以同時獲得并保持共享鎖,也就是多個連接可以同時從同一個數(shù)據(jù)庫中讀數(shù)據(jù),但只要有一個共享鎖沒有釋放,也不允許任何連接寫數(shù)據(jù)庫;
- 當(dāng)一個連接想要寫數(shù)據(jù)庫操作時,必須首先獲得一個預(yù)留鎖,在此階段,SQLite在緩存區(qū)進(jìn)行數(shù)據(jù)庫修改操作,對讀出內(nèi)容所做的修改保存在內(nèi)存緩存區(qū)中,而不是實際寫到磁盤;
一個數(shù)據(jù)庫同時只能有一個預(yù)留鎖,預(yù)留鎖可以與共享鎖共存,預(yù)留鎖既不阻止其他擁有共享鎖的連接讀取數(shù)據(jù)庫,也不阻止其它連接獲取新的共享鎖.
- 當(dāng)連接想要提交修改時,預(yù)留鎖提升為未決鎖,,在這個階段,其它連接不再能夠獲取新的共享鎖,但已經(jīng)擁有的共享鎖的連接能夠繼續(xù)正常的讀數(shù)據(jù)庫,此時,擁有未決鎖的連接等待其它擁有共享鎖的連接完成工作并釋放其共享鎖;
未決鎖可以理解為一個中間狀態(tài)唧喉，從限制小的狀態(tài)往限制高的狀態(tài)變化的一個過程.
- 一旦其它共享鎖都被釋放,未決鎖提升為排它鎖,此時,該連接可以對數(shù)據(jù)庫進(jìn)行修改,所有以前所緩存的修改都會被寫到數(shù)據(jù)庫文件中.
當(dāng)某個連接擁有預(yù)留鎖,而其它連接的共享鎖始終無法被釋放時,數(shù)據(jù)庫會陷入死鎖.
事務(wù)的類型
SQLite有三種不同的事務(wù)類型,它們以不同的鎖狀態(tài)啟動事務(wù).事務(wù)可以開始于:deferred,immediate,exclusive.事務(wù)類型在begin命令中指定:
**begin [deferred | immediate | exclusive] transaction;**
- deferred:直到必須使用時才獲取鎖;
對于延遲事務(wù),begin語句本身不會做什么事情,它從未鎖定狀態(tài)開始,這是默認(rèn)情況.如果僅僅使用begin開始一個事務(wù),那么事務(wù)就是延遲的,停留在未鎖定狀態(tài).多個連接可以在同一時刻未創(chuàng)建任何鎖的情況下開始延遲事務(wù),這種情況下,第一個對數(shù)據(jù)庫的讀操作獲取共享鎖,第一個對數(shù)據(jù)庫的寫操作試圖獲取預(yù)留鎖.
- immediate:在begin執(zhí)行時試圖獲取預(yù)留鎖;
如果由begin開始的immediate事務(wù)獲取預(yù)留鎖成功,begin immediate保證沒有其它的連接可以寫數(shù)據(jù)庫,這也導(dǎo)致了沒有其它連接能成功啟動begin immediate或者begin exclusive命令,當(dāng)其它連接執(zhí)行這些命令時SQLite會返回SQLITE_BUSY錯誤,但這些命令會不斷嘗試以確保它們最終能開始immediate事務(wù).
- exclusive:在begin執(zhí)行時試圖獲取排它鎖;
如果由begin開始的exclusive事務(wù)獲取排它鎖成功,則保證了數(shù)據(jù)庫中沒有其它的活動連接.exclusive的工作方式和immediate工作方式類似.
事務(wù)類型使用的基本準(zhǔn)則:如果使用的數(shù)據(jù)庫沒有其它連接,用begin就可以了,但如果使用的數(shù)據(jù)庫還有其它對數(shù)據(jù)庫進(jìn)行寫操作的連接,就需要使用begin immediate或者begin exclusive開始事務(wù).