SQLite

SQLite是一個開源的嵌入式關(guān)系數(shù)據(jù)庫，實現(xiàn)自包容、零配置、支持事務(wù)的SQL數(shù)據(jù)庫引擎。其特點是高度便攜、使用方便、結(jié)構(gòu)緊湊、高效、可靠。與其他數(shù)據(jù)庫管理系統(tǒng)不同，SQLite的安裝和運(yùn)行非常簡單，如果對并發(fā)性要求不是特別高，SQLite是一個不錯的選擇。

SQLite還是單文件數(shù)據(jù)庫引擎，一個文件即是一個數(shù)據(jù)庫，方便存儲和轉(zhuǎn)移。

安裝SQLite

下面主要介紹如何在Ubuntu和Windows下安裝SQLite數(shù)據(jù)庫引擎。

1.Ubuntu

目前，大多數(shù)的Linux系統(tǒng)都預(yù)安裝了SQLite，只需要在shell中輸入：sqlite3，如圖8-1所示。



圖8-1　SQLite（Ubuntu）

如果沒有看到圖8-1的效果，可以在shell中輸入以下命令進(jìn)行安裝：

sudo apt-get install sqlite3

安裝完成后可以使用sqlite3-version命令查看SQLite的版本信息。

2.Windows

首先到SQLite下載頁面http://www.sqlite.org/download.html，根據(jù)windows系統(tǒng)版本下載sqlite-dll-*.zip和sqlite-tools-win32-*.zip兩個壓縮包，在硬盤上創(chuàng)建一個文件夾，比如D：\sqlite3，將兩個壓縮包中的文件解壓到D：\sqlite3中，最后將D：\sqlite3添加到環(huán)境變量PATH中即可。打開cmd命令行窗口，輸入sqlite3，效果如圖8-2所示，則證明配置成功。

圖8-2　SQLite（Windows）

SQLite SQL語法

CREATE DATABASE用于創(chuàng)建數(shù)據(jù)庫，語法格式：CREATE DATABASE database_name。比如創(chuàng)建名稱為first_db的數(shù)據(jù)庫，

SQL語句為CREATE DATABASE first_db。

DROP DATABASE用于刪除數(shù)據(jù)庫，語法格式：DROP DATABASE database_name。比如刪除名稱為first_db的數(shù)據(jù)庫，

SQL語句為DROP DATABASE first_db。

CREATE TABLE語句用于創(chuàng)建數(shù)據(jù)庫中的表，語法格式：

CREATE TABLE表名稱（列名稱1數(shù)據(jù)類型，列名稱2數(shù)據(jù)類型，列名稱3數(shù)據(jù)類型，...）。SQL支持的數(shù)據(jù)類型如表8-2所示。

表8-2　SQL支持的數(shù)據(jù)類型

比如創(chuàng)建一個名稱為student的表，表里面包含5列，列名分別是：

“id”、“Name”、“Birth”、“Address”以及“City”。語句如下：

CREATE TABLE student（id integer，Name varchar（255），Birth date，Address varchar（255），City varchar（255））。id列的數(shù)據(jù)類型是integer，包含整數(shù)，Birth為日期類型，其余的數(shù)據(jù)類型是varchar，

最大長度為255個字符。

ALTER TABLE語句用于在已有的表中添加、修改或刪除列。

·在表中添加列：ALTER TABLE table_name ADD column_name
datatype·修改表中某一列的數(shù)據(jù)類型：ALTER TABLE table_name
ALTER COLUMN column_name datatype·刪除表中的某一列：ALTER TABLE table_name DROP
COLUMN column_name

例如在之前創(chuàng)建的student表中添加名為class的一列，語句如下：ALTER TABLE student ADD class varchar（255）。接著將class列的數(shù)據(jù)類型改為char（10），語句如下：ALTER TABLE student

ALTER COLUMN class char（10）。最后將class列刪除，語句如下：

ALTER TABLE student DROP COLUMN class。

DROP TABLE語句用于刪除表（表的結(jié)構(gòu)、屬性以及索引也會被刪除），語法格式：DROP TABLE table_name。比如刪除表名為student的表，SQL語句為DROP TABLE student。

CREATE INDEX語句用于創(chuàng)建索引，索引有助于加快SELECT查詢和WHERE子句，但它會減慢使用UPDATE和INSERT語句時的數(shù)據(jù)輸入。索引可以創(chuàng)建或刪除，但不會影響數(shù)據(jù)。CREATE INDEX的基本語法如下：CREATE INDEX index_name ON table_name。創(chuàng)建索引還分為創(chuàng)建單一索引、唯一索引、組合索引和隱式索引。單一索引指的是在表的某一列設(shè)置索引，語法如下：CREATE INDEX

index_nameON table_name（column_name）。唯一索引指的是不允許任何重復(fù)的值插入到表中，語法如下：CREATE UNIQUE INDEX
index_name on table_name（column_name）。組合索引可以對一個表中的幾列進(jìn)行索引，語法如下：CREATE INDEX index_name on
table_name（column1，column2）。隱式索引是在創(chuàng)建對象時，由數(shù)據(jù)庫服務(wù)器自動創(chuàng)建的索引。比如在之前的student表中對Name添加名稱為name_index的索引，語句為：CREATE INDEX name_index
ON student（Name）。

DROP INDEX語句用于刪除索引，語法格式為：DROP INDEX
index_name。比如將上面創(chuàng)建的name_index索引刪除，語句為：

DROP INDEX name_index。

2.數(shù)據(jù)操作語言（DML）

對于數(shù)據(jù)操作語言的定義，主要講解一下增刪改查四個部分的語法：

·SELECT用于查詢數(shù)據(jù)庫表中數(shù)據(jù)。

·UPDATE用于更新數(shù)據(jù)庫表中數(shù)據(jù)。

·DELETE用于從數(shù)據(jù)庫表中刪除數(shù)據(jù)。

·INSERT INTO用于向數(shù)據(jù)庫表中插入數(shù)據(jù)。

SELECT用來從表中選取數(shù)據(jù)，結(jié)果存儲在一個結(jié)果集中。語法

對表中數(shù)據(jù)進(jìn)行有條件查找，需要用到WHERE子句，將WHERE子句添加到SELECT語句中。語法格式為：SELECT列名稱

FROM表名稱WHERE列運(yùn)算符值。以下運(yùn)算符可在WHERE子句中使用：

如果我們想選取City為beijing的記錄，SQL語句為：SELECT Name，City FROM student WHERE City=’beijing‘。查詢結(jié)果如下所示：

大家可能注意到了，WHERE City=’beijing‘子句中beijing使用單引號包裹起來了，一般使用文本值進(jìn)行選取時，需要使用單引號進(jìn)行包裹，如果使用數(shù)值進(jìn)行選取，則不需要用單引號。比如選取id>2的記錄，SQL語句為：SELECT Name，City FROM student WHERE id>2。

上面講到WHERE語句使用的是單一條件，可以在WHERE子句中添加OR或者AND運(yùn)算符實現(xiàn)一個以上條件的篩選。AND運(yùn)算符相連的條件，必須所有條件都成立，才能顯示一條有效記錄。OR運(yùn)算符相連的條件，只要有一個條件成立，就能顯示一條有效記錄。示例如表8-4所示。

表8-4　AND和OR使用示例

使用多個條件進(jìn)行篩選時，可以使用圓括號組成復(fù)雜的表達(dá)式。

如果對查詢到的數(shù)據(jù)進(jìn)行排序，需要和ORDER BY語句配合使用。ORDER BY語句用于根據(jù)指定的列對結(jié)果集進(jìn)行排序，默認(rèn)按照升序ASC對記錄進(jìn)行排序。如果想按照降序?qū)τ涗涍M(jìn)行排序，可以使用DESC關(guān)鍵字。示例如表8-5所示。

表8-5　OROER BY語句使用示例

UPDATE語句用于修改表中的數(shù)據(jù)。語法格式：UPDATE表名稱

SET列名稱=新值WHERE列名稱=某值。比如想修改id=1這條記錄中Name和City表項的內(nèi)容，SQL語句如下：UPDATE student SET Name=’jack‘，City=’Nanjing‘WHERE id=1。

DELETE語句用于刪除表中的行。語法格式：DELETE FROM表名稱WHERE列名稱=值。比如刪除Name為jack這條記錄，SQL語句為：DELETE FROM student WHERE Name=’jack‘。

INSERT INTO語句用于向表格中插入新的行。語法格式：

INSERT INTO表名稱VALUES（值1，值2，....）或者指定要插入數(shù)據(jù)的列：INSERT INTO table_name（列1，列2，...）VALUES（值1，值2，....）。以表8-3為例，向其中插入一條記錄，SQL語句為：

INSERT INTO student VALUES（5，’Bill‘，’1999-8-10‘，’

beijing‘）。結(jié)果如下所示：

向指定的列插入一條記錄，SQL語句為：INSERT INTO student（id，Name，city）VALUES（6，’Rose‘，’shenzhen‘）。結(jié)果如下所示：

SQLite增刪改查

講解完了SQL語法，基本上可以完成大多數(shù)數(shù)據(jù)庫的增刪改查的操作。下面講解一下在SQLite的命令行窗口中，進(jìn)行一系列增刪改查的工作。

1.創(chuàng)建數(shù)據(jù)庫和表

在命令行窗口中輸入：sqlite3D：\test.db，就可以在D盤創(chuàng)建test.db數(shù)據(jù)庫。接著在數(shù)據(jù)庫中創(chuàng)建person表，包含id，name，age等3列，輸入語句：CREATE TABLE person（id integer primary key，

name varchar（20），age integer）；，效果如圖8-3所示。

圖8-3　創(chuàng)建數(shù)據(jù)庫和表

2.增刪改查操作

增加：插入一條name為qiye，age為20的記錄：

INSERT INTO person(name,age) VALUES('qiye',20);

修改：將name為qiye的記錄中age修改為17：

UPDATE person SET age=17 WHERE name='qiye';

查詢：查詢表中的記錄：

SELECT * FROM person;

刪除name為qiye的記錄：

DELETE FROM person WHERE name='qiye';

以上操作如圖8-4所示。

圖8-4　增刪改查操作

3.常用SQLite命令

下面主要說一下常用的SQLite命令，方便大家對SQLite進(jìn)行操作。

以下均是在命令行中的效果：

·顯示表結(jié)構(gòu)：

sqlite> .schema [table]

·獲取所有表和視圖：

sqlite > .tables

·獲取指定表的索引列表：

sqlite > .indices [table ]

·導(dǎo)出數(shù)據(jù)庫到SQL文件：

sqlite > .output [filename ] sqlite > .dump
qlite > .output stdout

·從SQL文件導(dǎo)入數(shù)據(jù)庫：

sqlite > .read [filename ]

·格式化輸出數(shù)據(jù)到CSV格式：

sqlite >.output [filename.csv ] sqlite >.separator , sqlite > select * from test;
sqlite >.output stdout

·從CSV文件導(dǎo)入數(shù)據(jù)到表中：

sqlite >create table newtable (id integer primary key,name varchar(20),age
integer );
sqlite >.import [filename.csv ] newtable

·備份數(shù)據(jù)庫：

sqlite3 test.db .dump > backup.sql

·恢復(fù)數(shù)據(jù)庫：

sqlite3 test.db < backup.sql

SQLite事務(wù)

數(shù)據(jù)庫事務(wù)指的是作為單個邏輯工作單元執(zhí)行的一系列操作，要么完全執(zhí)行，要么完全不執(zhí)行。設(shè)想一個網(wǎng)上購物的場景，用戶付款的過程至少包括以下幾步操作：

1）更新客戶所購商品的庫存信息。

2）保存客戶付款信息，同時與銀行系統(tǒng)交互。

3）生成訂單并且保存到數(shù)據(jù)庫中。

4）更新用戶相關(guān)信息，例如購物數(shù)量等數(shù)據(jù)。

正常情況下，這些操作完全成功執(zhí)行，才算一次有效的交易。交易成功后，與交易相關(guān)的所有數(shù)據(jù)庫信息也將成功更新。但是以上四步任意一個環(huán)節(jié)出現(xiàn)了異常，例如網(wǎng)絡(luò)中斷、客戶銀行帳戶存款不足等，都會導(dǎo)致交易的失敗。大家可以想象一下，假如數(shù)據(jù)庫更新完第二步，到第三步時操作失敗了，就會出現(xiàn)成功付款但是沒有買到商品的情況，這是非常不合理的情況。這個時候事務(wù)的作用就體現(xiàn)出來了，一旦交易失敗，數(shù)據(jù)庫中所有信息都必須保持交易前的狀態(tài)，即使進(jìn)行到最后一步才出錯，也要恢復(fù)到交易前狀態(tài)，因此事務(wù)是用來保證這種情況下交易的平穩(wěn)性和可預(yù)測性的技術(shù)。通俗來說，事務(wù)是將四個步驟打包成一件事來做，其中任何一個步驟出錯，都代表這件事情沒完成，數(shù)據(jù)庫就會回滾到之前的狀態(tài)。

SQLite主要通過以下命令來控制事務(wù)：

·BEGIN TRANSACTION：啟動事務(wù)處理。

·COMMIT：保存更改，或者使用END TRANSACTION命令。

·ROLLBACK：回滾所做的更改。

控制事務(wù)的命令只與DML命令中的INSERT、UPDATE和DELETE一起使用，不能在創(chuàng)建表和刪除表時使用，因為這兩個操作是數(shù)據(jù)庫自動提交的。

下面在命令行中演示一下如何使用事務(wù)，打開之前創(chuàng)建的test.db文件，向person里面插入一條記錄，在插入數(shù)據(jù)之前要先查看一下表中數(shù)據(jù)，用來進(jìn)行對比。如圖8-5所示。

經(jīng)過回滾操作，可以看到數(shù)據(jù)并沒有插入到person數(shù)據(jù)表中。下面使用COMMIT命令進(jìn)行提交，如圖8-6所示。

圖8-5　事務(wù)回滾

Python操作SQLite(創(chuàng)建/連接/查詢/插入/修改/刪除)的方法

• sql