SQLite資料庫操作實踐

SQLite存儲類型

null - 空值

integer - 有符号整數,自增列定義 id integer primary key autoincrement

real - 浮點數,存儲為8-byte IEEE浮點數

text - 文本字元串使用資料庫編碼（UTF-8, UTF-16BE 或 UTF-16LE）進行存儲

blob - 二進制資料，如圖檔、聲音等等

也可以接受其它資料類型例如datetime,varchar,nvarchar,decimal,float,double,bigint 等等

Last login: Tue Jan 7 19:05:33 2014 from 192.168.10.1

[[email protected] ~]$ su

Password:

[[email protected] myth]# pwd //顯示目前工作目錄

/home/myth

進入SQLite指令行建立資料庫并且打開資料庫

[[email protected] myth]# sqlite3 bookstore.db

SQLite version 3.6.20

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite> .databases

seq name file

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

0 main /home/myth/bookstore.db

sqlite>

sqlite> .quit //退出SQLite指令行環境

[[email protected] myth]#

打開資料庫

[[email protected] myth]# sqlite3 bookstore.db //如果資料庫已經存在了，則打開資料庫

SQLite version 3.6.20

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite> .help //檢視幫助資訊

.backup ?DB? FILE Backup DB (default "main") to FILE

.bail ON|OFF Stop after hitting an error. Default OFF

.databases List names and files of attached databases

.dump ?TABLE? ... Dump the database in an SQL text format

If TABLE specified, only dump tables matching

LIKE pattern TABLE.

.echo ON|OFF Turn command echo on or off

.exit Exit this program

.explain ON|OFF Turn output mode suitable for EXPLAIN on or off.

.genfkey ?OPTIONS? Options are:

--no-drop: Do not drop old fkey triggers.

--ignore-errors: Ignore tables with fkey errors

--exec: Execute generated SQL immediately

See file tool/genfkey.README in the source

distribution for further information.

.header(s) ON|OFF Turn display of headers on or off

.help Show this message

.import FILE TABLE Import data from FILE into TABLE

.indices ?TABLE? Show names of all indices

If TABLE specified, only show indices for tables

matching LIKE pattern TABLE.

.load FILE ?ENTRY? Load an extension library

.mode MODE ?TABLE? Set output mode where MODE is one of:

csv Comma-separated values

column Left-aligned columns. (See .width)

html HTML <table> code

insert SQL insert statements for TABLE

line One value per line

list Values delimited by .separator string

tabs Tab-separated values

tcl TCL list elements

.nullvalue STRING Print STRING in place of NULL values

.output FILENAME Send output to FILENAME

.output stdout Send output to the screen

.prompt MAIN CONTINUE Replace the standard prompts

.quit Exit this program

.read FILENAME Execute SQL in FILENAME

.restore ?DB? FILE Restore content of DB (default "main") from FILE

.schema ?TABLE? Show the CREATE statements

If TABLE specified, only show tables matching

LIKE pattern TABLE.

.separator STRING Change separator used by output mode and .import

.show Show the current values for various settings

.tables ?TABLE? List names of tables

If TABLE specified, only list tables matching

LIKE pattern TABLE.

.timeout MS Try opening locked tables for MS milliseconds

.width NUM NUM ... Set column widths for "column" mode

.timer ON|OFF Turn the CPU timer measurement on or off

sqlite>

建立表

sqlite> create table books(id integer primary key, title nvarchar(50),pubdate datetime);

sqlite> create table publishers(id integer primary key, name varchar(20));

sqlite> .tables //檢視資料庫中存在的表

books publishers

sqlite> .schema books //顯示表結構

CREATE TABLE books(id integer primary key, title nvarchar(50),pubdate datetime);

sqlite>

sqlite> alter table books add column publisher text not null default '' collate nocase; //增加表字段

sqlite> .schema books //顯示表結構

CREATE TABLE books(id integer primary key, title nvarchar(50),pubdate datetime, publisher text not null default '' collate nocase);

sqlite>

sqlite> alter table publishers rename to authors; //修改表名

sqlite> .tables //檢視資料庫中存在的表

authors books

sqlite>

修改字段名稱

//修改字段名稱和資料類型(删除字段也類似)，更簡單的做法：如果沒有重要資料，可以直接删除表重新建立

sqlite> alter table authors rename to authorsOld;

sqlite> create table authors (id integer primary key, author nvarchar(20));

sqlite> insert into authors select id, name from authorsOld;

sqlite> drop table authorsOld;

sqlite> .schema authors

CREATE TABLE authors (id integer primary key, author nvarchar(20));

//還可以把資料導出成insert語句，然後删除表，重新建立表，執行所有insert語句添加資料

顯示資料庫所有表名稱

sqlite> select name from sqlite_master where type='table' order by name; //顯示資料庫所有表名稱,sqlite_master表是隻讀的

authors

books

sqlite> select name from

...> (SELECT * from sqlite_master union all select * from sqlite_temp_master)

...> where type='table' order by name; //顯示資料庫所有表名稱(永久和臨時)

authors

books

sqlite>

删除表

sqlite> drop table authors;

sqlite> .tables //檢視資料庫中存在的表

books

sqlite>

往表中插入資料

sqlite> insert into books(id,title,pubdate,publisher) values(1,'交換機.路由器.防火牆','2007-08-28','電子工業出版社');

sqlite> insert into books(id,title,pubdate,publisher) values(2, '網絡硬體搭建與配置實踐','2007-01-20','電子工業出版社');

sqlite>

sqlite> insert into books(title,pubdate,publisher) values('英語沙龍 ENGLISH SALON 實戰版','2005-12-31','世界知識出版社');

sqlite> insert into books(title,pubdate,publisher) values('英語沙龍 ENGLISH SALON 朗讀版','2006-12-20','世界知識出版社');

sqlite>

sqlite> insert into books

...> select 100,'軟體項目管理理論與案例分析','2007-08-30','中國電力出版社' union all

...> select 105,'初級會計電算化','2006-03-27','中國财政經濟出版社';

sqlite>

sqlite> insert into books(id,title,pubdate,publisher)

...> select 300,'供應鍊管理 ------戰略、規劃與營運','2003-02-28','社會科學文獻出版社' union all

...> select 301,'UML實戰教程 ------面向.NET開發人員','2006-12-31','清華大學出版社' union all

...> select 302,'Project 2003 企業項目管理快譯通','2007-02-28','人民郵電出版社';

sqlite>

sqlite> select * from books;

1|交換機.路由器.防火牆|2007-08-28|電子工業出版社

2|網絡硬體搭建與配置實踐|2007-01-20|電子工業出版社

3|英語沙龍 ENGLISH SALON 實戰版|2005-12-31|世界知識出版社

4|英語沙龍 ENGLISH SALON 朗讀版|2006-12-20|世界知識出版社

100|軟體項目管理理論與案例分析|2007-08-30|中國電力出版社

105|初級會計電算化|2006-03-27|中國财政經濟出版社

300|供應鍊管理 ------戰略、規劃與營運|2003-02-28|社會科學文獻出版社

301|UML實戰教程 ------面向.NET開發人員|2006-12-31|清華大學出版社

302|Project 2003 企業項目管理快譯通|2007-02-28|人民郵電出版社

sqlite>

查詢分頁

sqlite> select * from books limit 0,5;

1|交換機.路由器.防火牆|2007-08-28|電子工業出版社

2|網絡硬體搭建與配置實踐|2007-01-20|電子工業出版社

3|英語沙龍 ENGLISH SALON 實戰版|2005-12-31|世界知識出版社

4|英語沙龍 ENGLISH SALON 朗讀版|2006-12-20|世界知識出版社

100|軟體項目管理理論與案例分析|2007-08-30|中國電力出版社

sqlite>

sqlite> select * from books limit 5,5;

105|初級會計電算化|2006-03-27|中國财政經濟出版社

300|供應鍊管理 ------戰略、規劃與營運|2003-02-28|社會科學文獻出版社

301|UML實戰教程 ------面向.NET開發人員|2006-12-31|清華大學出版社

302|Project 2003 企業項目管理快譯通|2007-02-28|人民郵電出版社

sqlite>

查詢分頁

sqlite> select * from books limit 5 offset 0;

1|交換機.路由器.防火牆|2007-08-28|電子工業出版社

2|網絡硬體搭建與配置實踐|2007-01-20|電子工業出版社

3|英語沙龍 ENGLISH SALON 實戰版|2005-12-31|世界知識出版社

4|英語沙龍 ENGLISH SALON 朗讀版|2006-12-20|世界知識出版社

100|軟體項目管理理論與案例分析|2007-08-30|中國電力出版社

sqlite>

sqlite> select * from books limit 5 offset 5;

105|初級會計電算化|2006-03-27|中國财政經濟出版社

300|供應鍊管理 ------戰略、規劃與營運|2003-02-28|社會科學文獻出版社

301|UML實戰教程 ------面向.NET開發人員|2006-12-31|清華大學出版社

302|Project 2003 企業項目管理快譯通|2007-02-28|人民郵電出版社

sqlite>

查詢分頁(推薦此法)

sqlite> select * from books where id >=(select id from books order by id limit 5*0,1) limit 5;

1|交換機.路由器.防火牆|2007-08-28|電子工業出版社

2|網絡硬體搭建與配置實踐|2007-01-20|電子工業出版社

3|英語沙龍 ENGLISH SALON 實戰版|2005-12-31|世界知識出版社

4|英語沙龍 ENGLISH SALON 朗讀版|2006-12-20|世界知識出版社

100|軟體項目管理理論與案例分析|2007-08-30|中國電力出版社

sqlite>

sqlite> select * from books where id >=(select id from books order by id limit 5*1,1) limit 5;

105|初級會計電算化|2006-03-27|中國财政經濟出版社

300|供應鍊管理 ------戰略、規劃與營運|2003-02-28|社會科學文獻出版社

301|UML實戰教程 ------面向.NET開發人員|2006-12-31|清華大學出版社

302|Project 2003 企業項目管理快譯通|2007-02-28|人民郵電出版社

sqlite>

設定格式化查詢結果

sqlite> .mode column

sqlite> .headers on

sqlite> .mode line

sqlite>

sqlite> .show

echo: off

explain: off

headers: on

mode: line

nullvalue: ""

output: stdout

separator: "\t"

width:

sqlite> select * from books;

id = 1

title = 交換機.路由器.防火牆

pubdate = 2007-08-28

publisher = 電子工業出版社

id = 2

title = 網絡硬體搭建與配置實踐

pubdate = 2007-01-20

publisher = 電子工業出版社

id = 3

title = 英語沙龍 ENGLISH SALON 實戰版

pubdate = 2005-12-31

publisher = 世界知識出版社

id = 4

title = 英語沙龍 ENGLISH SALON 朗讀版

pubdate = 2006-12-20

publisher = 世界知識出版社

id = 100

title = 軟體項目管理理論與案例分析

pubdate = 2007-08-30

publisher = 中國電力出版社

id = 105

title = 初級會計電算化

pubdate = 2006-03-27

publisher = 中國财政經濟出版社

id = 300

title = 供應鍊管理 ------戰略、規劃與營運

pubdate = 2003-02-28

publisher = 社會科學文獻出版社

id = 301

title = UML實戰教程 ------面向.NET開發人員

pubdate = 2006-12-31

publisher = 清華大學出版社

id = 302

title = Project 2003 企業項目管理快譯通

pubdate = 2007-02-28

publisher = 人民郵電出版社

sqlite>

設定格式化查詢結果"insert"模式下，傳回的查詢結果将以SQL的INSERT語句形式進行顯示

sqlite> .mode insert rebooks

sqlite> select * from books;

INSERT INTO rebooks VALUES(1,'交換機.路由器.防火牆','2007-08-28','電子工業出版社');

INSERT INTO rebooks VALUES(2,'網絡硬體搭建與配置實踐','2007-01-20','電子工業出版社');

INSERT INTO rebooks VALUES(3,'英語沙龍 ENGLISH SALON 實戰版','2005-12-31','世界知識出版社');

INSERT INTO rebooks VALUES(4,'英語沙龍 ENGLISH SALON 朗讀版','2006-12-20','世界知識出版社');

INSERT INTO rebooks VALUES(100,'軟體項目管理理論與案例分析','2007-08-30','中國電力出版社');

INSERT INTO rebooks VALUES(105,'初級會計電算化','2006-03-27','中國财政經濟出版社');

INSERT INTO rebooks VALUES(300,'供應鍊管理 ------戰略、規劃與營運','2003-02-28','社會科學文獻出版社');

INSERT INTO rebooks VALUES(301,'UML實戰教程 ------面向.NET開發人員','2006-12-31','清華大學出版社');

INSERT INTO rebooks VALUES(302,'Project 2003 企業項目管理快譯通','2007-02-28','人民郵電出版社');

sqlite>

設定格式化查詢結果,将查詢結果寫入建立的空檔案out_books.txt中

sqlite> .mode list

sqlite> .separator |

sqlite> .output out_books.txt

sqlite> select * from books;

sqlite> .exit

[[email protected] myth]# cat out_books.txt //檢視寫入的檔案内容

id|title|pubdate|publisher

1|交換機.路由器.防火牆|2007-08-28|電子工業出版社

2|網絡硬體搭建與配置實踐|2007-01-20|電子工業出版社

3|英語沙龍 ENGLISH SALON 實戰版|2005-12-31|世界知識出版社

4|英語沙龍 ENGLISH SALON 朗讀版|2006-12-20|世界知識出版社

100|軟體項目管理理論與案例分析|2007-08-30|中國電力出版社

105|初級會計電算化|2006-03-27|中國财政經濟出版社

300|供應鍊管理 ------戰略、規劃與營運|2003-02-28|社會科學文獻出版社

301|UML實戰教程 ------面向.NET開發人員|2006-12-31|清華大學出版社

302|Project 2003 企業項目管理快譯通|2007-02-28|人民郵電出版社

[[email protected] myth]#

建立視圖

[[email protected] myth]# sqlite3 bookstore.db

SQLite version 3.6.20

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite> create view v_books as select * from books;

sqlite> .tables

books v_books

sqlite> .schema v_books

CREATE VIEW v_books as select * from books;

sqlite>

建立索引

sqlite> create index index_books on books(publisher);

sqlite> .indices books

index_books

sqlite> .dump //列印目前資料庫所有操作内容

PRAGMA foreign_keys=OFF;

BEGIN TRANSACTION;

CREATE TABLE books(id integer primary key, title nvarchar(50),pubdate datetime, publisher text not null default '' collate nocase);

INSERT INTO "books" VALUES(1,'交換機.路由器.防火牆','2007-08-28','電子工業出版社');

INSERT INTO "books" VALUES(2,'網絡硬體搭建與配置實踐','2007-01-20','電子工業出版社');

INSERT INTO "books" VALUES(3,'英語沙龍 ENGLISH SALON 實戰版','2005-12-31','世界知識出版社');

INSERT INTO "books" VALUES(4,'英語沙龍 ENGLISH SALON 朗讀版','2006-12-20','世界知識出版社');

INSERT INTO "books" VALUES(100,'軟體項目管理理論與案例分析','2007-08-30','中國電力出版社');

INSERT INTO "books" VALUES(105,'初級會計電算化','2006-03-27','中國财政經濟出版社');

INSERT INTO "books" VALUES(300,'供應鍊管理 ------戰略、規劃與營運','2003-02-28','社會科學文獻出版社');

INSERT INTO "books" VALUES(301,'UML實戰教程 ------面向.NET開發人員','2006-12-31','清華大學出版社');

INSERT INTO "books" VALUES(302,'Project 2003 企業項目管理快譯通','2007-02-28','人民郵電出版社');

CREATE VIEW v_books as select * from books;

CREATE INDEX index_books on books(publisher);

COMMIT;

sqlite>

導出資料庫到 SQL 檔案

sqlite> .output bookstore.sql //建立一個空檔案bookstore.sql

sqlite> .dump //将dump顯示的内容寫入空檔案bookstore.sql

sqlite> .output stdout //恢複螢幕輸出，不再将後面其它操作輸出内容寫入到檔案bookstore.sql中

sqlite> .exit

[[email protected] myth]#

[[email protected] myth]# cat bookstore.sql //檢視寫入的檔案内容