Question

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

8.1.1　安装SQLite

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

1.Ubuntu

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

如果没有看到图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-1　SQLite（Ubuntu）

图8-2　SQLite（Windows）

8.1.2　SQL语法

进行数据库操作，必然要了解SQL语法。SQL是一门ANSI的标准计算机语言，用来访问和操作数据库系统，用于取回和更新数据库中的数据，并与数据库程序协同工作，比如MS Access、DB2、Informix、MS SQL Server、Oracle、Sybase以及其他数据库系统。

不幸的是，存在着很多不同版本的SQL语言，每个数据库都有一些它们独特的SQL语法，但是为了与ANSI标准相兼容，它们必须以相似的方式共同地来支持一些主要的关键词，比如SELECT、UPDATE、DELETE、INSERT、WHERE等等。因此本小节主要讲解一些常见的SQL语法。

SQL语言主要分为两个部分：数据定义语言（DDL）和数据操作语言（DML）。数据定义语言（DDL）使我们有能力创建或删除表格，也可以定义索引（键），规定表之间的链接，以及施加表间的约束。数据操作语言（DML）用于执行查询、更新、插入和删除记录。有一点需要注意：SQL语法对大小写不敏感。

1.数据定义语言（DDL）

对于数据定义语言，主要讲解表8-1所示内容。

表8-1　数据定义语言

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用来从表中选取数据，结果存储在一个结果集中。语法格式：SELECT列名称1，列名称2，...FROM表名称以及SELECT FROM表名称。以表8-3的student表为例：

表8-3　student表

比如我们想获取student表中Name和City列的内容，SQL语句为：SELECT Name，City FROM student。最后查询的结果如下所示：

如果想获取student表中的所有列，使用通配符*来代替列名称，SQL语句为：SELECT FROM student。

在表中可能会包含重复值，关键词DISTINCT用于返回唯一不同的值。语法格式为：SELECT DISTINCT列名称1，列名称2，...FROM表名称。上面的查询结果marry出现两次。去重复可以使用如下SQL语句：SELECT DISTINCT Name，City FROM student。查询结果如下所示：

对表中数据进行有条件查找，需要用到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‘）。结果如下所示：

8.1.3　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

8.1.4　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　事务回滚

8.1.5　Python操作SQLite

1.导入sqlite数据库模块

Python中使用sqlite3模块操作SQLite。从Python 2.5以后，sqlite3成为内置模块，不需要额外安装，只需要导入即可。

  import sqlite3

图8-6　事务提交

2.创建/打开数据库

sqlite3模块中使用connect方法创建/打开数据库，需要指定数据库路径，如果数据库存在则打开，不存在则创建一个新的数据库。

  con = sqlite3.connect('D:\test.db')

不仅可以在硬盘上创建数据库文件，还可以在内存中创建。

  con = sqlite3.connect(':memory:')

3.数据库连接对象

上面通过connect方法返回的con对象，即是数据库连接对象，它提供了以下方法：

·cursor（）方法用来创建一个游标对象。

·commit（）方法用来事务提交。

·rollback（）方法用来事务回滚。

·close（）方法用来关闭一个数据库连接。

4.游标对象的使用

对数据库的查询需要使用到游标对象，首先通过cursor（）方法创建一个游标对象：

  cur = con.cursor()

游标对象有以下方法支持数据库的操作：

·execute（）用来执行sql语句。

·executemany（）用来执行多条sql语句。

·close（）用来关闭游标。

·fetchone（）用来从结果中取一条记录，并将游标指向下一条记录。

·fetchmany（）用来从结果中取多条记录。

·fetchall（）用来从结果中取出所有记录。

·scroll（）用于游标滚动。

5.建表

首先使用游标对象创建一个person表，包含id、name、age等3列，代码如下：

cur.execute(' CREATE TABLE person (id integer primary key,name varchar(20),age integer)')

6.插入数据

向person表中插入两条数据。插入数据一般有两种做法，第一种做法是直接构造一个插入的SQL语句，代码如下：

  data="0,'qiye',20"
     cur.execute(' INSERT INTO person VALUES (%s)'%data)

但是这种做法是非常不安全的，容易导致SQL注入。另一种做法使用占位符“”的方式来规避这个问题，代码如下：

  cur.execute(' INSERT INTO person VALUES (,,)',(0,'qiye',20))

还可以使用executemany（）执行多条SQL语句，使用executemany（）方法比循环使用execute（）方法执行多条SQL语句效率高很多。

  cur.executemany(' INSERT INTO person VALUES (,,)',[(3,'marry',20),(4,'jack',20)])

这两种方法插入数据都不会立即生效，需要使用数据库对象con进行提交操作：

如果出现错误，还可以使用回滚操作：

  con.commit()

7.查询数据

查询person表中的所有数据，代码如下：

  cur.execute('SELECT * FROM person')

要提取查询数据，游标对象提供了fetchall（）和fetchone（）方法。fetchall（）方法获取所有数据，返回一个二维的列表。fetchone（）方法获取其中的一个结果，返回一个元组。使用方法如下：

  cur.execute('SELECT * FROM person')
  res = cur.fetchall()
  for line in res:
  print line
  cur.execute('SELECT * FROM person')
  res = cur.fetchone()
  print res

8.修改和删除数据

  cur.execute('UPDATE person SET name= WHERE id=',('rose',1))
  cur.execute('DELETE FROM person WHERE id=',(0,))
  con.commit()
  con.close() 

注意 　执行完所有操作记得关闭数据库，插入或者修改中文数据时，记得在中文字符串之前加上“u”。