sqliteinsert 插入一行： 插入一组： 使用select结果插入 多行插入到新表： 多行插入到新表，一步到位： 非常有用的临时表： sqliteupdate 更新一条记录： update注意约束 sqlitedelete 约束： 唯一约束unique autoincrement 主键约束 域约束：默认值 时间戳默认值 NOTNULL约束 check约束 check约束复杂 外键约束 sqlite 存储类 视图 索引 触发器 视图触发器：可更新的视图 事务 冲突
sqlite insert
查看表结构：
sqlite>.schemafoods CREATETABLEfoods( idintegerprimarykey,type_idinteger,nametext);
插入一行：
sqlite>insertintofoods(name,type_id)values('CinnamonBobka',1); sqlite>insertintofoodsvalues(NULL,1,'BlueberryBobka'); 验证： sqlite>select*fromfoodswherenamelike'%bobka'; idtype_idname ----------------------------------- 101ChocolateBobka 131CinnamonBobka 4131CinnamonBobka 4141BlueberryBobka
插入一组：
sqlite>insertintofoodsvalues(null,(selectidfromfood_typeswherename='Bakery'),'BlackberryBobka'); sqlite>select*fromfoodswherenamelike'%bobka'; idtype_idname ----------------------------------- 101ChocolateBobka 131CinnamonBobka 4131CinnamonBobka 4141BlueberryBobka 4151BlackberryBobk sqlite>
使用select结果插入
sqlite>insertintofoods ...>selectlast_insert_rowid()+1,type_id,namefromfoodswherename='ChocolateBobka'; sqlite>select*fromfoodswherenamelike'%bobka'; idtype_idname ----------------------------------- 101ChocolateBobka 131CinnamonBobka 4131CinnamonBobka 4141BlueberryBobka 4151BlackberryBobk 4161ChocolateBobka sqlite> [注意]：字段个数要匹配，数据类型也要匹配： 否则，失败。 sqlite>.schemafoods CREATETABLEfoods( idintegerprimarykey,nametext); sqlite>insertintofoodsselectlast_insert_rowid()+100,name,type_idfromfoodswherename='ChocolateBobka'; Error:UNIQUEconstraintFailed:foods.id sqlite>
多行插入到新表：
sqlite>createtablefoods2(idint,type_idint,nametext); sqlite>insertintofoods2select*fromfoods; sqlite>selectcount(*)fromfoods2; count(*) ---------- 416 sqlite>
多行插入到新表，一步到位：
sqlite>createtablefoods3asselect*fromfoods; sqlite>selectcount(*)fromfoods3; count(*) ---------- 416 sqlite>
非常有用的临时表：
sqlite> createtemptablelistas selectf.namefood,t.namename,(selectcount(episode_id)fromfoods_episodeswherefood_id=f.id)episodes fromfoodsf,food_typest wheref.type_id=t.id; sqlite>select*fromlist; foodnameepisodes ------------------------------ BagelsBakery1 Bagels,raBakery2 BavarianCBakery1 BearClawsBakery3 BlackandBakery2 Bread(witBakery1 ButterfingBakery1 CarrotCakBakery1 ChipsAhoyBakery1
使用 create table 的这种形式，自增长字段在新表中创建，索引也不会创建，UNIoUE约束都不会被创建。
插入行时的 unique 约束，如果在定义为 unique 的字段中插入重复值，sqlite 会停止并报错。
sqlite>insertintofoodsvalues(416,'ChocolateBobka'); Error:UNIQUEconstraintFailed:foods.id sqlite>
sqlite update
更新一条记录：
sqlite>updatefoodssetname='CHOCOLATEBOBKA'wherename='ChocolateBobka'; sqlite>select*fromfoodswherenamelike'CHOCOLATE%'; idtype_idname ----------------------------------- 101CHOCOLATEBOBKA 111ChocolateEclai 121ChocolateCream 2229Chocolates,Box 2239ChocolateChip 2249ChocolateCover 4161CHOCOLATEBOBKA sqlite>
update 注意 约束
sqlite>.schemafoods CREATETABLEfoods( idintegerprimarykey,nametext); sqlite> sqlite>updatefoodssetid=11wherename='CHOCOLATEBOBKA'; Error:UNIQUEconstraintFailed:foods.id sqlite>
sqlite delete
sqlite>deletefromfoodswherename='CHOCOLATEBOBKA';
约束：
唯一约束unique
createtablecontacts(
intintegerprimarykey,nametextnotnullcollatenocase,phonetextnotnulldefault'UNKNOWN',unique(name,phone)
);
insertintocontacts(name,phone)values('Jerry','UNKNOWN');
autoincrement
autoincrement 并不会使用未被使用的数字，而是自动递增，但不会阻挡你自己提供的值
sqlite>createtablemaxed_out(idintegerprimarykeyautoincrement,xtext); sqlite>insertintomaxed_outvalues(10,'wors'); sqlite>select*frommaxed_out; idx -------------------- 10wors sqlite>insertintomaxed_outvalues(9223372036854775807,'lastone'); sqlite>select*frommaxed_out; idx -------------------- 10wors 9223372036lastone sqlite>insertintomaxed_outvalues(NULL,'willnotwork'); Error:databaSEOrdiskisfull sqlite>
主键约束
约束：主键约束primarykey不允许存在同样的 sqlite>createtablepkey(xtext,ytext,primarykey(x,y)); sqlite>insertintopkeyvalues('x','y'); sqlite>insertintopkeyvalues('x','x'); sqlite>selectrowid,x,yfrompkey; rowidxy ------------------------------ 1xy 2xx sqlite>insertintopkeyvalues('x','x'); Error:UNIQUEconstraintFailed:pkey.x,pkey.y sqlite>
域约束：默认值
域约束：默认值，NULL，NOTNULL，check，sort 1,default默认值 createtablecontacts( intintegerprimarykey,phone) ); sqlite>insertintocontacts(name)values('Tom'); sqlite>select*fromcontacts; intnamephone ------------------------------ 1JerryUNKNOWN 2Jerry021-110 3TomUNKNOWN sqlite>
时间戳默认值
createtabletimes( idint,datenotnulldefaultcurrent_date,timenotnulldefaultcurrent_time,timestampnotnulldefaultcurrent_timestamp); sqlite>insertintotimes(id)values(1); sqlite>insertintotimes(id)values(2); sqlite>select*fromtimes; iddatetimetimestamp ------------------------------------------------- 12017-02-1006:14:302017-02-1006:14:30 22017-02-1006:14:322017-02-1006:14:32 sqlite>
NOT NULL 约束
sqlite>.schemacontacts CREATETABLEcontacts( intintegerprimarykey,phone) ); sqlite>insertintocontacts(phone)values("134-1234-5678"); Error:NOTNULLconstraintFailed:contacts.name sqlite>
check 约束
createtablecontacts( idintegerprimarykey,phonetextnotnulldefault'空号',phone),check(length(phone)>=7) );
check 约束 复杂
check约束复杂 createtablefoo( xinteger,yintegercheck(y>x),zintegercheck(z>abs(y)) ); sqlite>insertintofoovalues(-2,-1,2); sqlite>select*fromfoo; xyz ------------------------------ -2-12 sqlite>updatefoosetz=-3wherez=2; Error:CHECKconstraintFailed:foo sqlite>
外键约束
外键约束http://www.sqlite.org/foreignkeys.html 确保foods的type_id已存在于food_types的id中。 CREATETABLEfood_types( idintegerprimarykey,nametext); CREATETABLEfoods( idintegerprimarykey,type_idintegerreferencesfood_types(id) ondeleterestrict deferrableinitiallydeferred,nametext); NOACTION:Configuring"NOACTION"meansjustthat:whenaparentkeyismodifiedordeletedfromthedatabase,nospecialactionistaken. RESTRICT:The"RESTRICT"actionmeansthattheapplicationisprohibitedfromdeleting(forONDELETERESTRICT)ormodifying(forONUPDATERESTRICT)aparentkeywhenthereexistsoneormorechildkeysmappedtoit.ThedifferencebetweentheeffectofaRESTRICTactionandnormalforeignkeyconstraintenforcementisthattheRESTRICTactionprocessinghappensassoonasthefieldisupdated-notattheendofthecurrentstatementasitwouldwithanimmediateconstraint,orattheendofthecurrenttransactionasitwouldwithadeferredconstraint.Eveniftheforeignkeyconstraintitisattachedtoisdeferred,configuringaRESTRICTactioncausessqlitetoreturnanerrorimmediatelyifaparentkeywithdependentchildkeysisdeletedormodified. SETNULL:Iftheconfiguredactionis"SETNULL",thenwhenaparentkeyisdeleted(forONDELETESETNULL)ormodified(forONUPDATESETNULL),thechildkeycolumnsofallrowsinthechildtablethatmappedtotheparentkeyaresettocontainsqlNULLvalues. SETDEFAULT:The"SETDEFAULT"actionsaresimilarto"SETNULL",exceptthateachofthechildkeycolumnsissettocontainthecolumnsdefaultvalueinsteadofNULL.RefertotheCREATETABLEdocumentationfordetailsonhowdefaultvaluesareassignedtotablecolumns. CASCADE:A"CASCADE"actionpropagatesthedeleteorupdateoperationontheparentkeytoeachdependentchildkey.Foran"ONDELETECASCADE"action,thismeansthateachrowinthechildtablethatwasassociatedwiththedeletedparentrowisalsodeleted.Foran"ONUPDATECASCADE"action,itmeansthatthevaluesstoredineachdependentchildkeyaremodifiedtomatchthenewparentkeyvalues.
4,排序
createtablecontacts( intintegerprimarykey,phone) ); collate定义排序规则： nocase忽略大小写 组合起来就是：'Jerry'与'jerry'在插入的时候会被忽略大小写，然后就是一样的，无法二次插入
sqlite
存储类
存储类 描述 NULL 值是一个NULL值。 INTEGER 值是一个带符号的整数，根据值的大小存储在1、2、3、4、6或8字节中。 REAL 值是一个浮点值，存储为8字节的IEEE浮点数字。 TEXT 值是一个文本字符串，使用数据库编码（UTF-8、UTF-16BE或UTF-16LE）存储。 BLOB 值是一个blob数据，完全根据它的输入存储。
视图
视图即虚拟表，也称为派生表。因为视图的内容来自其他的查询结果。 视图不是基本表，尽管感觉起来与表是一样的。基本表的内容是持久的，视图是动态产生的。 视图的起源：如果需要频繁查此表 selectf.name,ft.name,e.name fromfoodsf innerjoinfood_typesftonf.type_id=ft.id innerjoinfoods_episodesfeonf.id=fe.food_id innerjoinepisodeSEOnfe.episode_id=e.id; 创建视图：sqlite不支持可更新的视图 createviewdetailsas selectf.nameasfd,ft.nameastp,e.nameasep,e.seasonasssn fromfoodsf innerjoinfood_typesftonf.type_id=ft.id innerjoinfoods_episodesfeonf.id=fe.food_id innerjoinepisodeSEOnfe.episode_id=e.id; sqlite>select*fromdetailslimit10; fdtpepssn ---------------------------------------- BagelsBakeryTheStrike9 Bagels,raBakeryTheStrike9 Bagels,raBakeryTheMuffin8 BavarianCBakeryTheSoupN7 BearClawsBakeryTheStrong9 BearClawsBakeryTheSniffi5 BearClawsBakeryTheRainco5 BlackandBakeryTheDinner5 BlackandBakeryTheUnders6 Bread(witBakeryTheApolog9 sqlite> 删除视图： dropviewdetails;
索引
索引： 索引是一种用来在某种条件下加速查询的结构。 创建唯一索引 sqlite>droptablefoo; sqlite>createtablefoo(idinteger,nametext); sqlite>createuniqueindexfoo_indexonfoo(id,name); sqlite>insertintofoo(id,name)values(250,'老王'); sqlite>insertintofoo(id,name)values(11,'张三'); sqlite>insertintofoo(id,'老王'); Error:UNIQUEconstraintFailed:foo.id,foo.name sqlite>select*fromfoo; idname -------------------- 250老王 11张三 sqlite> 删除索引： sqlite>dropindexfoo_index;
创建大小写不敏感索引
sqlite>createindexfoods_name_idxonfoods(namecollatenocase); sqlite>.indices foods_name_idx sqlite>.schemafoods CREATETABLEfoods( idintegerprimarykey,nametext); CREATEINDEXfoods_name_idxonfoods(namecollatenocase); sqlite>
触发器
当具体的表发生特定的数据库事件时，触发器执行对应的sql命令 未更新的行用old引用，已更新的行用new引用 所有属性都可以用点来引用 createtemptablelog(x); createtemptriggerfoods_update_logupdateofnameonfoods begin insertintologvalues('updatefoods:newname='||new.name); end; begin; updatefoodssetname='JUJYFRUIT'wherename='JujyFruit'; select*fromlog; rollback; 执行结果： sqlite>createtemptablelog(x); sqlite>createtemptriggerfoods_update_logupdateofnameonfoods ...>begin ...>insertintologvalues('updatefoods:newname='||new.name); ...>end; sqlite> sqlite>begin; sqlite>updatefoodssetname='JUJYFRUIT'wherename='JujyFruit'; sqlite>select*fromlog; x ------------------------------- updatefoods:newname=JUJYFRUIT sqlite>rollback; sqlite> shell执行结果： chunli@linux:~/work/sqlite$cattrigger.sql createtemptablelog(x); createtemptriggerfoods_update_logupdateofnameonfoods begin insertintologvalues('updatefoods:newname='||new.name); end; begin; updatefoodssetname='JUJYFRUIT'wherename='JujyFruit'; select*fromlog; rollback; chunli@linux:~/work/sqlite$sqlite3foods.db<trigger.sql updatefoods:newname=JUJYFRUIT chunli@linux:~/work/sqlite$
视图触发器：可更新的视图
chunli@linux:~/work/sqlite$cattrigger.sql --创建视图 createviewfoods_viewas selectf.idfid,f.namefname,t.idtid,t.nametname fromfoodsf,food_typest; --创建触发器 createtriggeron_update_foods_viewinsteadofupdateonfoods_view foreachrow begin updatefoodssetname=new.fnamewhereid=new.fid; updatefood_typessetname=new.tnamewhereid=new.tid; end; --小实验,更新视图的数据，自动同步到基础表 begin; updatefoods_viewsetfname='乐事薯片',tname='FastFood'wherefid=413; --查询底层表 select*fromfoodsf,food_typestwheref.type_id=t.idandf.id=413; rollback; --查询底层表 select*fromfoodsf,food_typestwheref.type_id=t.idandf.id=413; dropviewfoods_view; chunli@linux:~/work/sqlite$sqlite3foods.db<trigger.sql 413|1|乐事薯片|1|FastFood 413|1|Whataburger|1|FastFood chunli@linux:~/work/sqlite$
事务
sqlite事务： BEGIN[point]/COMMIT[point]/ROLLBACK[point] 默认情况下：sqlite事务是自动提交，执行成功则提交，执行失败则回滚。 begin事务开始，之后的所有事务都可以取消 commit提交 rollback事务回滚 sqlite>begin; sqlite>deletefromfoods; sqlite>selectcount(*)fromfoods; 0 sqlite>rollback; sqlite>selectcount(*)fromfoods; 414 sqlite> ======================================================= 回滚到事务点： sqlite>begin; sqlite>selectcount(*)fromfoods; 414 sqlite>deletefromfoods; sqlite>selectcount(*)fromfoods; 0 sqlite>savepointpoint1; sqlite>selectcount(*)fromfood_types; 15 sqlite>deletefromfood_types; sqlite>selectcount(*)fromfood_types; 0 sqlite>rollbacktopoint1; sqlite>selectcount(*)fromfood_types; 15 sqlite>rollback; sqlite>selectcount(*)fromfoods; 414 sqlite>
冲突
冲突解决： sqlite提供5种冲突解决方案： replace违反的记录被删除，以新记录代替之 ignore 违反的记录保持原貌，其它记录继续执行 fail 终止命令，违反之前执行的操作得到保存 abort 终止命令，恢复违反之前执行的修改 rollback终止命令和事务，回滚整个事务 语法： 语句级（可覆盖对象级的冲突解决手段） insert/update/createor[resolution]table/index[tbl_name/idx_name]…… [resolution]：replace、ignore、fail、abort、rollback 对象级（定义表格时） createtable/index[tbl_name/idx_name]([field_name][format][constraint]onconflict[resolution]); [constraint]：unique、notnull…… sqlite>updatefoodssetid=800-id; Error:UNIQUEconstraintFailed:foods.id sqlite>updateorabortfoodssetid=800-id; Error:UNIQUEconstraintFailed:foods.id sqlite>updateorreplacefoodssetid=800-id; sqlite>
