SQLite

SQLite是一个开源的嵌入式关系数据库，实现自包容、零配置、支持事务的SQL数据库引擎。其特点是高度便携、使用方便、结构紧凑、高效、可靠。与其他数据库管理系统不同，SQLite的安装和运行非常简单，如果对并发性要求不是特别高，SQLite是一个不错的选择。

SQLite还是单文件数据库引擎，一个文件即是一个数据库，方便存储和转移。

安装SQLite

下面主要介绍如何在Ubuntu和Windows下安装SQLite数据库引擎。

1.Ubuntu

目前，大多数的Linux系统都预安装了SQLite，只需要在shell中输入：sqlite3，如图8-1所示。



图8-1　SQLite（Ubuntu）

如果没有看到图8-1的效果，可以在shell中输入以下命令进行安装：

sudo apt-get install sqlite3

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

2.Windows

首先到SQLite下载页面http://www.sqlite.org/download.html，根据windows系统版本下载sqlite-dll-*.zip和sqlite-tools-win32-*.zip两个压缩包，在硬盘上创建一个文件夹，比如D：\sqlite3，将两个压缩包中的文件解压到D：\sqlite3中，最后将D：\sqlite3添加到环境变量PATH中即可。打开cmd命令行窗口，输入sqlite3，效果如图8-2所示，则证明配置成功。

图8-2　SQLite（Windows）

SQLite SQL语法

CREATE DATABASE用于创建数据库，语法格式：CREATE DATABASE database_name。比如创建名称为first_db的数据库，

SQL语句为CREATE DATABASE first_db。

DROP DATABASE用于删除数据库，语法格式：DROP DATABASE database_name。比如删除名称为first_db的数据库，

SQL语句为DROP DATABASE first_db。

CREATE TABLE语句用于创建数据库中的表，语法格式：

CREATE TABLE表名称（列名称1数据类型，列名称2数据类型，列名称3数据类型，...）。SQL支持的数据类型如表8-2所示。

表8-2　SQL支持的数据类型

比如创建一个名称为student的表，表里面包含5列，列名分别是：

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

CREATE TABLE student（id integer，Name varchar（255），Birth date，Address varchar（255），City varchar（255））。id列的数据类型是integer，包含整数，Birth为日期类型，其余的数据类型是varchar，

最大长度为255个字符。

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

·在表中添加列：ALTER TABLE table_name ADD column_name
datatype·修改表中某一列的数据类型：ALTER TABLE table_name
ALTER COLUMN column_name datatype·删除表中的某一列：ALTER TABLE table_name DROP
COLUMN column_name

例如在之前创建的student表中添加名为class的一列，语句如下：ALTER TABLE student ADD class varchar（255）。接着将class列的数据类型改为char（10），语句如下：ALTER TABLE student

ALTER COLUMN class char（10）。最后将class列删除，语句如下：

ALTER TABLE student DROP COLUMN class。

DROP TABLE语句用于删除表（表的结构、属性以及索引也会被删除），语法格式：DROP TABLE table_name。比如删除表名为student的表，SQL语句为DROP TABLE student。

CREATE INDEX语句用于创建索引，索引有助于加快SELECT查询和WHERE子句，但它会减慢使用UPDATE和INSERT语句时的数据输入。索引可以创建或删除，但不会影响数据。CREATE INDEX的基本语法如下：CREATE INDEX index_name ON table_name。创建索引还分为创建单一索引、唯一索引、组合索引和隐式索引。单一索引指的是在表的某一列设置索引，语法如下：CREATE INDEX

index_nameON table_name（column_name）。唯一索引指的是不允许任何重复的值插入到表中，语法如下：CREATE UNIQUE INDEX
index_name on table_name（column_name）。组合索引可以对一个表中的几列进行索引，语法如下：CREATE INDEX index_name on
table_name（column1，column2）。隐式索引是在创建对象时，由数据库服务器自动创建的索引。比如在之前的student表中对Name添加名称为name_index的索引，语句为：CREATE INDEX name_index
ON student（Name）。

DROP INDEX语句用于删除索引，语法格式为：DROP INDEX
index_name。比如将上面创建的name_index索引删除，语句为：

DROP INDEX name_index。

2.数据操作语言（DML）

对于数据操作语言的定义，主要讲解一下增删改查四个部分的语法：

·SELECT用于查询数据库表中数据。

·UPDATE用于更新数据库表中数据。

·DELETE用于从数据库表中删除数据。

·INSERT INTO用于向数据库表中插入数据。

SELECT用来从表中选取数据，结果存储在一个结果集中。语法

对表中数据进行有条件查找，需要用到WHERE子句，将WHERE子句添加到SELECT语句中。语法格式为：SELECT列名称

FROM表名称WHERE列运算符值。以下运算符可在WHERE子句中使用：

如果我们想选取City为beijing的记录，SQL语句为：SELECT Name，City FROM student WHERE City=’beijing‘。查询结果如下所示：

大家可能注意到了，WHERE City=’beijing‘子句中beijing使用单引号包裹起来了，一般使用文本值进行选取时，需要使用单引号进行包裹，如果使用数值进行选取，则不需要用单引号。比如选取id>2的记录，SQL语句为：SELECT Name，City FROM student WHERE id>2。

上面讲到WHERE语句使用的是单一条件，可以在WHERE子句中添加OR或者AND运算符实现一个以上条件的筛选。AND运算符相连的条件，必须所有条件都成立，才能显示一条有效记录。OR运算符相连的条件，只要有一个条件成立，就能显示一条有效记录。示例如表8-4所示。

表8-4　AND和OR使用示例

使用多个条件进行筛选时，可以使用圆括号组成复杂的表达式。

如果对查询到的数据进行排序，需要和ORDER BY语句配合使用。ORDER BY语句用于根据指定的列对结果集进行排序，默认按照升序ASC对记录进行排序。如果想按照降序对记录进行排序，可以使用DESC关键字。示例如表8-5所示。

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

UPDATE语句用于修改表中的数据。语法格式：UPDATE表名称

SET列名称=新值WHERE列名称=某值。比如想修改id=1这条记录中Name和City表项的内容，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，....）或者指定要插入数据的列：INSERT INTO table_name（列1，列2，...）VALUES（值1，值2，....）。以表8-3为例，向其中插入一条记录，SQL语句为：

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

beijing‘）。结果如下所示：

向指定的列插入一条记录，SQL语句为：INSERT INTO student（id，Name，city）VALUES（6，’Rose‘，’shenzhen‘）。结果如下所示：

SQLite增删改查

讲解完了SQL语法，基本上可以完成大多数数据库的增删改查的操作。下面讲解一下在SQLite的命令行窗口中，进行一系列增删改查的工作。

1.创建数据库和表

在命令行窗口中输入：sqlite3D：\test.db，就可以在D盘创建test.db数据库。接着在数据库中创建person表，包含id，name，age等3列，输入语句：CREATE TABLE person（id integer primary key，

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

图8-3　创建数据库和表

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进行操作。

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

·显示表结构：

sqlite> .schema [table]

·获取所有表和视图：

sqlite > .tables

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

sqlite > .indices [table ]

·导出数据库到SQL文件：

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

·从SQL文件导入数据库：

sqlite > .read [filename ]

·格式化输出数据到CSV格式：

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

·从CSV文件导入数据到表中：

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

·备份数据库：

sqlite3 test.db .dump > backup.sql

·恢复数据库：

sqlite3 test.db < backup.sql

SQLite事务

数据库事务指的是作为单个逻辑工作单元执行的一系列操作，要么完全执行，要么完全不执行。设想一个网上购物的场景，用户付款的过程至少包括以下几步操作：

1）更新客户所购商品的库存信息。

2）保存客户付款信息，同时与银行系统交互。

3）生成订单并且保存到数据库中。

4）更新用户相关信息，例如购物数量等数据。

正常情况下，这些操作完全成功执行，才算一次有效的交易。交易成功后，与交易相关的所有数据库信息也将成功更新。但是以上四步任意一个环节出现了异常，例如网络中断、客户银行帐户存款不足等，都会导致交易的失败。大家可以想象一下，假如数据库更新完第二步，到第三步时操作失败了，就会出现成功付款但是没有买到商品的情况，这是非常不合理的情况。这个时候事务的作用就体现出来了，一旦交易失败，数据库中所有信息都必须保持交易前的状态，即使进行到最后一步才出错，也要恢复到交易前状态，因此事务是用来保证这种情况下交易的平稳性和可预测性的技术。通俗来说，事务是将四个步骤打包成一件事来做，其中任何一个步骤出错，都代表这件事情没完成，数据库就会回滚到之前的状态。

SQLite主要通过以下命令来控制事务：

·BEGIN TRANSACTION：启动事务处理。

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

·ROLLBACK：回滚所做的更改。

控制事务的命令只与DML命令中的INSERT、UPDATE和DELETE一起使用，不能在创建表和删除表时使用，因为这两个操作是数据库自动提交的。

下面在命令行中演示一下如何使用事务，打开之前创建的test.db文件，向person里面插入一条记录，在插入数据之前要先查看一下表中数据，用来进行对比。如图8-5所示。

经过回滚操作，可以看到数据并没有插入到person数据表中。下面使用COMMIT命令进行提交，如图8-6所示。

图8-5　事务回滚

Python操作SQLite(创建/连接/查询/插入/修改/删除)的方法

• sql