簡介：

SQLite 是實現了SQL 92標準的一個大子集的嵌入式資料庫.其以在一個庫中組合了資料庫引擎和介面,能將所有資料儲存於單個檔案中而著名.我覺得SQLite的功能一定程度上居於MySQL 和PostgreSQL之間.儘管如此,在效能上面,SQLite常常快2-3倍 (甚至更多).這利益於其高度調整了的內部架構,因為它除去了伺服器端到客戶端和客戶端到伺服器端的通訊。

而令人印象深刻的特點是你可將你的整個資料庫系統放在其中.利用非常高效的記憶體組織,SQLite只需在很小的記憶體中維護其很小的尺寸,遠遠比其它任何資料庫系統都小.這些特點使得其成為在需要高效地應用資料庫的任務中一個非常方便的工具.

SQLite優勢：

一 . 除了速度和效率,SQLite還有其它好多的優勢使得其能成為許多工中一個理想的解決方案.因為SQLite的資料庫都是簡單檔案,因此無須一個管理隊伍花時間來構造複雜的許可權結構來保護使用者的資料庫.因為許可權透過檔案系統自動進行.這也同時意味著(資料庫空間的大小隻與環境有關,與本身無關)無段特殊的規則來了解使用者磁碟空間.使用者可以從建立他們想要的任意多的資料庫和對其對這些資料庫的絕對控制權而得到好處.

二 . 資料庫就是一個檔案的事實使用SQLite可以輕易地在伺服器間移動.SQLite也除去了需要大量記憶體和其它系統資源的伺候程式.即使當資料庫在大量地使用時也是如此.

1，安裝

下載地址： Wget

開始安裝：

tar xvfz sqlite-autoconf-3080403.tar.gz

cd sqlite-autoconf-3080403

./configure --prefix=/usr/local

make

make install

2，登入

不過因為一般centos的linux系統自帶了版本比較低的sqlite，所以你不安裝，直接執行sqlite3就可以登入進去：

[root@mysqlvm2 ~]# sqlite3

SQLite version 3.3.6.3 2014-04-03 16:53:12

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite> .exit

[root@mysqlvm2 ~]#

為了使用新版本，我們這裡用自己的全路徑或者在設定PATH路徑

[root@mysqlvm2 ~]# /root/sqlite-autoconf-3080403/sqlite3

SQLite version 3.8.4.3 2014-04-03 16:53:12

Enter ".help" for usage hints.

Connected to a transient in-memory database.

Use ".open FILENAME" to reopen on a persistent database.

sqlite>

3，建立資料庫和表

3.1，預設登入

預設登入時，臨時操作的是臨時記憶體庫，等退出的時候，建立的臨時庫以及表以及資料會被釋放的，使用.databases命令展示的時候會看到file列下面是null的沒有資料庫記錄，如下所示：

[root@localhost sqlite-autoconf-3080403]# sqlite3

SQLite version 3.8.4.3 2014-04-03 16:53:12

Enter ".help" for usage hints.

Connected to a transient in-memory database.

Use ".open FILENAME" to reopen on a persistent database.

sqlite> .databases

seq name file

--- --------------- ----------------------------------------------------------

0 main

sqlite>

sqlite> .tables

sqlite> create table t1(id int);

sqlite> insert into t1 select 1;

sqlite> insert into t1 select 2;

sqlite> select * from t1;

1

2

sqlite> .table

t1

sqlite>

我們建立了一張表t1，並且錄入了資料，退出後重新登入，表t1已經不存在了，如下所示：

[root@localhost sqlite-autoconf-3080403]# sqlite3

SQLite version 3.8.4.3 2014-04-03 16:53:12

Enter ".help" for usage hints.

Connected to a transient in-memory database.

Use ".open FILENAME" to reopen on a persistent database.

sqlite> .table

sqlite> .tables

sqlite> select * from t1;

Error: no such table: t1

sqlite>

3.2，建立資料庫

直接登入時候帶上資料庫名tim，就表示建庫tim，注意:這時tim資料庫確實已經建立好了注意:這時test.db資料庫確實已經建立好了但是這時還是看不到這個資料庫，還是個臨時的，所以要輸入SQL命令，等表格建立完後關閉sqlite3退出來的時候，就可以看到這個庫了。

[root@localhost ~]# sqlite3 tim

SQLite version 3.8.4.3 2014-04-03 16:53:12

Enter ".help" for usage hints.

sqlite> .schema

sqlite> CREATE TABLE test_auto_incre(id INT AUTO_INCREMENT primary key,msg_to VARCHAR(20), TYPE CHAR(1), create_date DATE );

sqlite> .schema

CREATE TABLE test_auto_incre(id INT AUTO_INCREMENT,msg_to VARCHAR(20), TYPE CHAR(1), create_date DATE,PRIMARY KEY (id) );

sqlite> .tables

test_auto_incre

sqlite>

sqlite> exit

...> ;

Error: near "exit": syntax error

sqlite>

用.exit或者.quit退出來，或者使用CTRL+D 按鈕退出sqlite命令列操作介面。
此時在sqlite3的安裝根目錄下，你就會看到tim庫的資料夾，一般通常以庫名字命名，如下所示

[root@localhost sqlite-autoconf-3080403]# ll -t

total 11152

-rw-r--r-- 1 root root 3072 Aug 27 16:52 tim

再次登入進去檢視已經存在的庫以及表：

sqlite> .databases

seq name file

--- --------------- ----------------------------------------------------------

0 main /root/sqlite-autoconf-3080403/tim

sqlite>

sqlite> .tables

test test_auto_incre

sqlite>

3.3，建表

[root@localhost sqlite-autoconf-3080403]# sqlite3 tim

SQLite version 3.8.4.3 2014-04-03 16:53:12

Enter ".help" for usage hints.

sqlite> .tables

test_auto_incre

sqlite> create table test(id integer default 0, username text);

sqlite> .tables

test test_auto_incre

sqlite>

3.4，插入資料記錄

sqlite> inser into test(id,username)values(1,'test');

Run Time: real 0.000 user 0.000000 sys 0.000000

Error: near "inser": syntax error

sqlite> insert into test(id,username)values(1,'test');

Run Time: real 0.102 user 0.000000 sys 0.002000

sqlite> select * from test;

id username

---------- ----------

1 test

Run Time: real 0.000 user 0.000000 sys 0.000000

sqlite> insert into test(id,username)values(2,'tman');

Run Time: real 0.070 user 0.001000 sys 0.000000

sqlite> select * from test;

id username

---------- ----------

1 test

2 tman

Run Time: real 0.000 user 0.000000 sys 0.000000

3.3，刪除資料

sqlite> delete from test where id=1;

Run Time: real 0.081 user 0.000000 sys 0.002000

sqlite> select * from test;

id username

---------- ----------

2 tman

Run Time: real 0.001 user 0.001000 sys 0.000000

sqlite>

3.4，更新資料表記錄

sqlite> update test set username='tmanupdate' where id=2;

sqlite>

sqlite> select * from test;

2|tmanupdate

sqlite>

查詢SQllite的VIRTUAL Table碰到問題：

CREATE VIRTUAL TABLE fts_message_table_0 USING fts3

(usernameid INTEGER DEFAULT 0, MesLocalID INTEGER, CreateTime INTEGER DEFAULT 0, Message TEXT,

reservedInt INTEGER DEFAULT 0, reservedText TEXT, PRIMARY KEY(usernameid,MesLocalID,CreateTime), tokenize=one_or_binary_tokenizer);

sqlite> select * from fts_message_table_0 where fts_message_table_0 match '27';

Error: unknown tokenizer: one_or_binary_tokenizer

sqlite>

4，修改表結構

4.1 新增一個欄位

sqlite> .timer on

sqlite> ALTER TABLE test ADD COLUMN address varchar(20) ;

Run Time: real 0.068 user 0.000000 sys 0.000000

sqlite>

4.2 刪除列

新增一列address，然後刪除它

sqlite> create table test(id integer default 0, username text);

sqlite> select * from test;

sqlite> insert into test select 1,'a';

sqlite> insert into test select 2,'b';

sqlite> alter table test add column address varchar(20);

sqlite> insert into test select 3,'c','caddre';

sqlite> select * from test;

1|a|

2|b|

3|c|caddre

sqlite>

sqlite> alter table test drop column address;

Error: near "drop": syntax error

sqlite>

不識別drop操作標示符，怎麼辦？去官網看到如下資訊：

SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table. It is not possible to rename a column, remove a column, or add or remove constraints from a table.

Sqlite 其實是不支援drop column的方法來刪除列的

(1).根據原表建立一張新表

create table test_tmp(id integer primary key, username text default ‘’);這個表沒有列address了。

(2).從原來表把資料錄入新臨時表，

sqlite> insert into test_tmp select id,username from test;

sqlite> select * from test_tmp;

1|a

2|b

3|c

sqlite>

(3).刪除原表

sqlite> drop table if exists test;

(4).將新臨時表重名為舊錶的名稱

sqlite> alter table test_tmp rename to test;

sqlite> select * from test;

1|a

2|b

3|c

sqlite>

這樣就實現了，刪除一個欄位address，得到了我們想要的drop column的目的了。
PS：不建議create table test_tmp as select id, username from test;的方式建立臨時表，因為這樣的話，主鍵約束就不存在了。

4.3，索引管理

建立普通索引

sqlite> create index idx_username on test(username);

建立複合索引

sqlite> alter table test add column addr varchar(60) not null default '';

sqlite> create index idx_addr on test(addr,username);

建立唯一鍵索引

sqlite> create unique index idx_unaddr on test(addr);

測試是不是真的唯一約束，新增addr欄位值都為’a1’，第一次成功，第二次失敗，檢視執行結果，如下：

sqlite> insert into test select 4,'a','a1';

sqlite> insert into test select 5,'a','a1';

Error: UNIQUE constraint failed: test.addr

sqlite>

報錯了，驗證了，唯一約束idx_undaddr已經生效。

PS： Sqlite不支援聚集索引，android預設需要一個_id欄位，這保證了你插入的資料會按“_id”的整數順序插入，這個integer型別的主鍵就會扮演和聚集索引一樣的角色。所以不要再在對於宣告為：INTEGER PRIMARY KEY的主鍵上建立索引。

4.4，自增主鍵

CREATE TABLE test_auto_incre(id INT AUTO_INCREMENT,msg_to VARCHAR(20), TYPE CHAR(1), create_date DATE,PRIMARY KEY (id) );

4.5 檢視所有表結構

方法一：

sqlite> .schema

CREATE TABLE "test"(id integer primary key, username text default ‘’, addr varchar(60) not null default '');

CREATE INDEX idx_username on test(username);

CREATE INDEX idx_addr on test(addr,username);

CREATE UNIQUE INDEX idx_unaddr on test(addr);

CREATE TABLE test_auto_incre(id INT AUTO_INCREMENT,msg_to VARCHAR(20), TYPE CHAR(1), create_date DATE,PRIMARY KEY (id) );

sqlite>

方法二：

sqlite> .header on –加上顯示的表頭列名

sqlite> select * from sqlite_master;

type|name|tbl_name|rootpage|sql

table|test|test|4|CREATE TABLE "test"(id integer primary key, username text default ‘’, addr varchar(60) not null default '')

index|idx_username|test|2|CREATE INDEX idx_username on test(username)

index|idx_addr|test|5|CREATE INDEX idx_addr on test(addr,username)

index|idx_unaddr|test|6|CREATE UNIQUE INDEX idx_unaddr on test(addr)

table|test_auto_incre|test_auto_incre|7|CREATE TABLE test_auto_incre(id INT AUTO_INCREMENT,msg_to VARCHAR(20), TYPE CHAR(1), create_date DATE,PRIMARY KEY (id) )

index|sqlite_autoindex_test_auto_incre_1|test_auto_incre|8|

sqlite>

4.6 檢視當前庫下所有的表名字

sqlite> .table

test test_auto_incre

sqlite> .tables

test test_auto_incre

4.7 檢視某一張表的結構，比如test_auto_incre表：

方法一：查詢系統表

select * from sqlite_master where name="test_auto_incre";

sqlite> .header on –加上顯示的表頭列名

sqlite> select * from sqlite_master where name="test_auto_incre";

type|name|tbl_name|rootpage|sql

table|test_auto_incre|test_auto_incre|7|CREATE TABLE test_auto_incre(id INT AUTO_INCREMENT,msg_to VARCHAR(20), TYPE CHAR(1), create_date DATE,PRIMARY KEY (id) )

sqlite>

方法二：使用.schema命令

sqlite> .schema test_auto_incre

CREATE TABLE test_auto_incre(id INT AUTO_INCREMENT,msg_to VARCHAR(20), TYPE CHAR(1), create_date DATE,PRIMARY KEY (id) );

sqlite>

sqlite> .schema test

CREATE TABLE "test"(id integer primary key, username text default ‘’, addr varchar(60) not null default '');

CREATE INDEX idx_username on test(username);

CREATE INDEX idx_addr on test(addr,username);

CREATE UNIQUE INDEX idx_unaddr on test(addr);

sqlite>

4.8 建立虛擬表

CREATE VIRTUAL TABLE fts_message_table_0_tmp USING fts3(usernameid INTEGER DEFAULT 0, MesLocalID INTEGER, CreateTime INTEGER DEFAULT 0, Message TEXT, reservedInt INTEGER DEFAULT 0, reservedText TEXT, PRIMARY KEY(usernameid,MesLocalID,CreateTime));

4.9 建立檢視

CREATE TABLE test(id INT AUTO_INCREMENT,msg_to VARCHAR(20), TYPE CHAR(1), create_date DATE,PRIMARY KEY (id) );

INSERT INTO test(msg_to,TYPE,create_date) SELECT 'm1','a','2014-08-18 12:12:23';

INSERT INTO test(msg_to,TYPE,create_date) SELECT 'm1','b','2014-08-18 13:12:23';

INSERT INTO test(msg_to,TYPE,create_date) SELECT 'm1','c','2014-08-18 10:12:23';

INSERT INTO test(msg_to,TYPE,create_date) SELECT 'm2','b','2014-08-20 12:12:23';

INSERT INTO test(msg_to,TYPE,create_date) SELECT 'm3','c','2014-08-19 12:12:23';

CREATE VIEW TEST_VIEW SELECT MSG_TO,CREATE_DATE FROM test;

5 Sqlite資料匯出匯入操作

5.1 資料匯入.import命令

命令：.import FILE TABLE Import data from FILE into TABLE
建立了Sqlite資料庫後就要建立表輸入資料了，多數時候資料量較大，手動輸入不可能，必須用匯入語句，匯入逗號分隔csv格式資料，首先建立一個表，比如test.db中建表test，如果表已經存在，可以利用命令".schema"檢視該表結構，比如：
sqlite>.schema test，結果得到test表的結構，因為要匯入的資料必須具有相似的結構，所以必須明瞭目標表的結構。

sqlite> .schema test

CREATE TABLE test(id integer default 0, username text);

sqlite>
比如test表具有下面的資料
1|a1
2|a2

另一個csv文字檔案為 test.csv，內容如下：
3,a3
4,a4

注意1: 不要忘了開頭的點.import

注意2: 這條語句不能用分號結束. 非SQL不需要分號結束.

注意3: 需要檢視預設的分隔符separator. 必須一致，檢視分隔符使用命令.show，如果不一致可能導致sqlite欄位分割錯誤，利用”.separator”命令轉換sqlite預設分隔符，比如.separator ","這一句就將分隔符改變為逗號，與預匯入資料一致才能順利匯入，如下所示：

sqlite> .show

echo: off

eqp: off

explain: off

headers: off

mode: list

nullvalue: ""

output: stdout

separator: "|"

stats: off

width:

sqlite>

分隔符不是”|”，而檔案test.csv的分隔符是”,”，所以要先轉換分隔符，在匯入：

sqlite> .separator ","

sqlite> .table

t2 test test_auto_incre

sqlite> .import test.csv test

匯入成功，檢視結果

sqlite> select * from test;

1,a1

2,a2

3,a3

4,a4

sqlite> .separator "|"

sqlite> select * from test;

1|a1

2|a2

3|a3

4|a4

sqlite>
這樣表test就多了從csv檔案匯入的兩行記錄，sqlite最新版本已經預設使用事件，因此海量資料匯入也很輕鬆高效，同樣的方法可以匯入海量txt文字檔案。

5.2，資料匯出.output命令

命令： .output FILENAME Send output to FILENAME

sqlite> .output a.txt

然後輸入sql語句, 查詢出要導的資料. 查詢後,資料不會顯示在螢幕上,而直接寫入檔案.

結束後,輸入

sqlite> .output stdout

將輸出重定向至螢幕，如下所示：

[root@localhost sqlite-autoconf-3080403]#

[root@localhost sqlite-autoconf-3080403]# sqlite3 ti

SQLite version 3.8.4.3 2014-04-03 16:53:12

Enter ".help" for usage hints.

sqlite>

sqlite> .output test.sql

sqlite> select * from test;

sqlite> .exit

[root@localhost sqlite-autoconf-3080403]# more test.sql

1|a1

2|a2

3|a3

4|a4

[root@localhost sqlite-autoconf-3080403]#

5.3 使用5.2匯出的檔案恢復test表

先登入刪除test表

[root@localhost sqlite-autoconf-3080403]# sqlite3 ti

SQLite version 3.8.4.3 2014-04-03 16:53:12

Enter ".help" for usage hints.

sqlite>

select * from test;

1|a1

2|a2

3|a3

4|a4

sqlite> delete from test;

sqlite> select * from test;

sqlite>

再匯入test.sql檔案的資料，如下所示

sqlite> .show

echo: off

eqp: off

explain: off

headers: off

mode: list

nullvalue: ""

output: stdout

separator: "|"

stats: off

width:

sqlite> .import test.sql test

sqlite> select * from test;

1|a1

2|a2

3|a3

4|a4

sqlite>

查詢資料，顯示匯入表test成功。

參考文件：

Sqlite嵌入式資料庫的安裝、建庫、建表、更新表結構以及資料匯入匯出等等詳細過程記錄

相關文章