Sqlite 學習記錄

標籤：des   io   ar   使用   sp   on   檔案   資料   bs   

Sqlite命令列(CLP)
======================================================================================
.help//擷取協助，會列出可用命令，如下：
.ver//獲得版本
.open test.db //開啟資料庫檔案
.save test.db//儲存一個資料庫或建立
.database//擷取目前的目錄下存在的資料庫檔案，一個檔案表示一個資料庫
.tables//擷取表列表
.indices test//擷取表的索引列表
.schema test//提供表名則擷取表建立語句，不提供則擷取所有表，查詢sqlite_master系統檢視表也可
.show//擷取使用者shell定義的設定
.nullvale NULL //設定null顯示為NULL
.echo on//輸出執行的語句

//匯出為sql檔案
.output file.sql
.dump
.output stdout

//匯入sql檔案
drop table test;
drop view schema;
.read file.sql
或者直接在命令列中用：
sqlite3 test.db .dump > test.sql
sqlite3 test.db < test.sql
sqlite3 -init test.sql test.db

create table test(id integer primary key, value text); //id主鍵且自增
create index test_idx on test(value) //建立索引。

//簡單增刪改查詢，和Mysql類似
insert into test(id,value)values(1, ‘testz‘);
insert into test(value)values(‘testz‘);

.mode column//查詢結果顯示列，可選格式：csv,html,insert,line,tabs,tcl,預設為list
.headers on//顯示頭部資訊（包含.mode column）
select * from test;//查詢資料，不顯示列名的
select last_insert_rowid();//擷取最後插入的ID

//結果輸出為csv，其中.mode csv也可以用.dump輸出備份sql
.output file.csv
.mode csv //或用.separator ,
select * from test;
.output stdout

//匯入到test2表
create table test2(id integer primary key, value text);
.import text.csv test2

//備份源檔案，二進位檔案，但沒sql移植好
sqlite3 test.db vacuum
cp test.db test.Backup

工具
======================================================================================
sqlite3_analyzer 可擷取資料庫磁碟結構的詳細技術資訊。

SQL
======================================================================================
使用;作為命令終結符。

select * from test;
insert into test(value)values(‘testz‘);
delete from test where id =1;
update teable set value="zz" where id=3

//很多和Mysql文法類似，如 ：
like "%test%"、count(*)、group by、having、distinct、as、in、子查詢、複合查詢、

//讀一行從第一行開始。desc或asc，可忽略offset。
select * from test order by desc id limit 1 offset 1

//類似MYSQL的左串連
select * from test a left outer join testb b on a.id=b.id;

欄位設計
======================================================================================
日期的預設值：
current_date YYYY-MM-DD
current_time HH::MM::SS
current_timestamp YYYY-MM-DD HH:MM:SS

//check約束，小於7個字元就報錯。
create table test(
id integer primary key,
name text not null default ‘zzz‘,
unique(name),
check(length(name)>=7));

//外鍵test_types表的id
create table test(
id integer primary key,
type_id integer references test_types(id)
on delete restrict //父ID被刪時，此資料不刪除。
deferrable initially deferred,
name text);

完整規則：
set null:如果父值被刪除或不存在，剩餘子值改為null。
set default:如果父值被刪除或不存在，剩餘子值改為預設值。
casecade:更新或刪除父值時則子值也被更新或刪除。
restrict:更新或刪除父值，可能會出現孤立的子值，從而阻止事務。
no action:不干涉操作執行，只觀察變化。
deferrable:立即強制實施還是延遲到整個事務結束時。

//關於欄位類型
通過值的標記法來判斷其類型。有5類型：integer、real(浮點)、text、blob(二進位x開頭帶引號)、null
一個欄位可以儲存不同類型的值。
select typeof(3.14) 查詢其類型

索引
======================================================================================
//建立name大小寫不敏感的索引
create index a_idx test(name collate nocase);

//使用單個欄位索引的情況: idx on test(a)
column {=|>|>=|<=|<} expression
expression {=|>|>=|<=|<} column
column IN (expression-list)
column IN (subquery)

//多欄位索引的情況： idx on test(a,b,c,d)
只有a和b使用了索引
select * from test where a=1 and b=2 and d=3
運算式a>1稱為最右邊的索引欄位，因為用了不等號,後面的查詢條件無法使用索引。
select * from test where a>1 and b=2 and c=3 and d=3

事務
======================================================================================
begin,commit,rollback

主要分為：讀事務、寫事務。
預設多個事務一起運行時，一個事務的非讀操作需要等另一個事務（如果有語句執行）結束才能提交。

為了避免死結，需要使用正確的事務類型，預設3種：
begin [deferred | immediate | exclusive] transaction;
其中：
deferred
是預設情況，直到必須使用時才擷取鎖。

immediate
在執行時試圖擷取預留鎖，如果成功擷取，其它事務無法修改資料庫只能讀，提交不會被阻礙，提交後其它事務可修改。

exclusive
試著擷取對資料庫的排它鎖，只有等此事務任意操作完並提交後，其它事務才能開始執行查詢或增刪等語句。
exclusive比immediate更高的安全層級，immediate開始就不會產生死結了。

 

Sqlite 學習記錄