SQLite3深入浅出

来源：互联网发布：淘宝达人在哪里找编辑：程序博客网时间：2024/06/06 03:25

文章目录：

1、sqlite3 基础语句

2、sqlite3 API

3、sqlite3 线程安全

4、FMDB

1、基础语句：

学习sqlite3的基础在于SQL语句，开始前请输入$ sqlite3 验证你的电脑是否已经安装了sqlite3

首先我们需要创建一个数据库文件，打开终端，在合适的目录下，输入：

$ sqlite3 studyDB.db
.database

1.1、创建表

以id为主键并自动增加，创建一个名为book的表：

create table if not exists book (id integer primary key autoincrement, bookNumber integer, bookName text, authorID integer, pressName text);

输入如下命令查看数据表是否创建成功：

.tables

1.2、insert

有了数据表以后，就可以愉快地写SQL语句来测试了，先从基础的增删改查开始：

输入如下指令，向数据表中插入一条记录：

insert into book (bookNumber, bookName, authorID, pressName) values (1001, '三国演义', 10, '长江出版社');

1.3、select

然后做查询操作，看上一条记录是否插入成功：

select * from book;

然而在终端练习SQL语句，看起来并不那么清晰，所以接下来我们用一个可视化工具MesaSQLite来练习SQL语句，解压之后请阅读Serial.txt，如图：

安装MesaSQLite

接下来我们多插入几条记录，以便演示操作：

insert into book (bookNumber, bookName, authorID, pressName) values (1002, '水浒传', 11, '黄河出版社');
insert into book (bookNumber, bookName, authorID, pressName) values (1003, '西游记', 12, '长沙出版社');
insert into book (bookNumber, bookName, authorID, pressName) values (1004, '红楼梦', 13, '武汉出版社');
insert into book (bookNumber, bookName, authorID, pressName) values (1005, '琅琊榜', 14, '黄河出版社');
insert into book (bookNumber, bookName, authorID, pressName) values (1006, '伪装者', 15, '长江出版社');
insert into book (bookNumber, bookName, authorID, pressName) values (1007, '简爱', 16, '长江出版社');
insert into book (bookNumber, bookName, authorID, pressName) values (1008, '大主宰', 14, '武汉出版社');

执行以上SQL语句以后，我们的数据表中的数据应该是这样的：

book

1.4、where

条件语句where，查询bookNumber为1003的记录：

select * from book where bookNumber = 1003;

1.5、update

修改bookNumber为1002的记录，然后查询所有记录：

update book set pressName = '清华大学出版社' where bookNumber = 1002;
select * from book;

1.6、delete

删除红楼梦，然后查询：

delete from book where bookName = '红楼梦';
select * from book;

1.7、and

逻辑运算符and，查询pressName为黄河出版社，并且authorID为14的记录：

select * from book where pressName = '黄河出版社' and authorID = 14;

1.8、or

逻辑运算符or，查询authorID为14，或者pressName为长江出版社的记录：

select* from book where pressName= '长江出版社' or authorID = 14;

1.9、like

模糊查询指令like：

select * from book where pressName like '长%';
select * from book where authorID like '_4';

1.10、in

查询authorID为14或16的记录：

select * from book where authorID in (14, 16);

1.11、not in

查询pressName不为“长江出版社”的记录：

select * from book where pressName not in ('长江出版社');

1.12、between

查询authorID在14到20之间的记录：

select * from book where authorID between 14 and 20;

1.13、count

查询pressName为“长江出版社”的记录条数：

select count(pressName) from book where pressName = '长江出版社';

为了方便演示，我们创建另一个数据表author：

create table if not exists author (id integer primary key autoincrement, authorName text, authorID integer, age integer);

执行如下SQL语句：

insert into author (authorName, authorID, age) values ('jack', 21, 45);
insert into author (authorName, authorID, age) values ('dave', 10, 33);
insert into author (authorName, authorID, age) values ('rose', 14, 24);
insert into author (authorName, authorID, age) values ('jim', 16, 56);
insert into author (authorName, authorID, age) values ('ivan', 13, 22);

最后我们的author表中的数据应该是这样的：

author

1.14、sum

查询所有作者的年龄总和：

select sum(age) from author;

1.15、avg

查询作者的平均年龄:

select avg(age) from author;

1.16、max

查询最大的作者年龄:

select max(age) from author;

1.17、min

查询最小的作者年龄:

select min(age) from author;

1.18、order by

查询所有的记录，并按年龄升序排列：

select * from author order by age asc;

查询所有的记录，并按年龄降序排列：

select * from author order by age desc;

1.19、语句嵌套

查询年龄小于平均年龄的记录：

select * from author where age < (select avg(age) from author);

1.20、多表联合查询

查询年龄小于平均年龄的作者姓名、图书名、出版社：

select author.authorName, book.bookName, book.pressName from author, book where author.authorID = book.authorID and age< (select avg(age) from author);

2、sqlite3 API

要使用sqlite3 API，需要导入libsqlite3.tbd，然后#import 就可以使用sqlite了。

使用的过程根据使用的函数大致分为如下几个过程：

sqlite3_open()

sqlite3_prepare()

sqlite3_step()

sqlite3_column()

sqlite3_finalize()

sqlite3_close()

这几个过程是概念上的说法，而不完全是程序运行的过程，如sqlite3_column()表示的是对查询获得一行里面的数据的列的各个操作统称，实际上在sqlite中并不存在这个函数。

2.1、sqlite3_open

函数定义：

SQLITE_API int SQLITE_STDCALL sqlite3_open(
const char *filename, /* Database filename (UTF-8) */
sqlite3 **ppDb /* OUT: SQLite db handle */
);

在操作数据库之前，首先要打开数据库。这个函数打开一个sqlite数据库文件，并且返回一个数据库连接对象。假如这个要被打开的数据文件不存在，则一个同名的数据库文件将被创建。如果使用sqlite3_open和sqlite3_open_v2的话，数据库将采用UTF-8的编码方式，sqlite3_open16采用UTF-16的编码方式。如果sqlite数据库被成功打开（或创建），将会返回SQLITE_OK，否则将会返回错误码。

filename：需要被打开的数据库文件的文件名，在sqlite3_open和sqlite3_open_v2中这个参数采用UTF-8编码，而在sqlite3_open16中则采用UTF-16编码。

ppDb：一个数据库连接句柄被返回到这个参数，即使发生错误。唯一的异常是如果sqlite不能分配内存来存放sqlite对象，ppDb将会被返回一个NULL值。

2.2、sqlite3_prepare

函数定义：

SQLITE_API int SQLITE_STDCALL sqlite3_prepare(
sqlite3 *db, /* Database handle */
const char *zSql, /* SQL statement, UTF-8 encoded */
int nByte, /* Maximum length of zSql in bytes. */
sqlite3_stmt **ppStmt, /* OUT: Statement handle */
const char **pzTail /* OUT: Pointer to unused portion of zSql */
);

这个函数将sql文本转换成一个准备语句（prepared statement）对象，同时返回语句对象的句柄。这个接口需要一个数据库连接指针以及一个要准备的包含SQL语句的文本。它实际上并不执行（evaluate）这个SQL语句，它仅仅为执行准备这个sql语句。

db：数据库连接指针。

zSql：sql语句，使用UTF-8编码。

nByte：如果nByte小于0，则函数取出zSql中从开始到第一个0终止符的内容；如果nByte不是负的，那么它就是这个函数能从zSql中读取的字节数的最大值。如果nBytes非负，zSql在第一次遇见‘/000/’或‘u000’的时候终止。

pzTail：上面提到zSql在遇见终止符或者是达到设定的nByte之后结束，假如zSql还有剩余的内容，那么这些剩余的内容被存放到pZTail中，不包括终止符。

ppStmt：能够使用sqlite3_step()执行的编译好的准备语句的指针，如果错误发生，它被置为NULL，如假如输入的文本不包括sql语句。调用过程必须负责在编译好的sql语句完成使用后使用sqlite3_finalize()删除它。

2.3、sqlite3_step

函数定义：

SQLITE_API int SQLITE_STDCALL sqlite3_step(sqlite3_stmt*);

这个过程用于执行有前面sqlite3_prepare创建的准备语句。这个语句执行到结果的第一行可用的位置。继续前进到结果的第二行的话，只需再次调用sqlite3_setp()。继续调用sqlite3_setp()直到这个语句完成，那些不返回结果的语句（如：INSERT，UPDATE，或DELETE），sqlite3_step()只执行一次就返回。

2.4、sqlite3_column

函数定义：

SQLITE_API const void* SQLITE_STDCALL sqlite3_column_blob(sqlite3_stmt*,intiCol);
SQLITE_API int SQLITE_STDCALL sqlite3_column_bytes(sqlite3_stmt*,intiCol);
SQLITE_API int SQLITE_STDCALL sqlite3_column_bytes16(sqlite3_stmt*,intiCol);
SQLITE_API double SQLITE_STDCALL sqlite3_column_double(sqlite3_stmt*,intiCol);
SQLITE_API int SQLITE_STDCALL sqlite3_column_int(sqlite3_stmt*,intiCol);
SQLITE_API sqlite3_int64 SQLITE_STDCALL sqlite3_column_int64(sqlite3_stmt*,intiCol);
SQLITE_API const unsigned char* SQLITE_STDCALL sqlite3_column_text(sqlite3_stmt*,intiCol);
SQLITE_API const void* SQLITE_STDCALL sqlite3_column_text16(sqlite3_stmt*,intiCol);
SQLITE_API int SQLITE_STDCALL sqlite3_column_type(sqlite3_stmt*,intiCol);
SQLITE_API sqlite3_value* SQLITE_STDCALL sqlite3_column_value(sqlite3_stmt*,intiCol);

这个过程从执行sqlite3_step()执行一个准备语句得到的结果集的当前行中返回一个列。每次sqlite3_step得到一个结果集的列停下后，这个过程就可以被多次调用去查询这个行的各列的值。对列操作是有多个函数，均以sqlite3_column为前缀。

第一个参数为从sqlite3_prepare返回来的prepared statement对象的指针。

第二参数指定这一行中的想要被返回的列的索引。最左边的一列的索引号是0，行的列数可以使用sqlite3_colum_count()获得。

2.5、sqlite3_finalize

函数定义：

SQLITE_API int SQLITE_STDCALL sqlite3_finalize(sqlite3_stmt *pStmt);

这个过程销毁前面被sqlite3_prepare创建的准备语句，每个准备语句都必须使用这个函数去销毁以防止内存泄露。

2.6、sqlite3_exec

函数定义：

SQLITE_API int SQLITE_STDCALL sqlite3_exec(
sqlite3*, /* An open database */
const char*sql, /* SQL to be evaluated */
int(*callback)(void*,int,char**,char**), /* Callback function */
void*, /* 1st argument to callback */
char **errmsg /* Error msg written here */
);

sqlite3_step 和 sqlite3_exec 都可以用于执行SQL语句，他们的区别在于后者是sqlite3_prepare()、sqlite3_step() 和 sqlite3_finalize() 的封装，能让程序多次执行sql语句而不要写许多重复的代码，然后提供一个回调函数进行结果的处理。

第1个参数：数据库连接指针。

第2个参数：是一条sql语句。

第3个参数：是一个函数指针，当这条语句执行之后，sqlite3会去调用你提供的这个函数。

第4个参数：是你所提供的指针，你可以传递任何一个指针参数到这里，这个参数最终会传到回调函数里面，如果不需要传递指针给回调函数，可以填NULL。等下我们再看回调函数的写法，以及这个参数的使用。

第5个参数：是错误信息。注意是指针的指针。sqlite3里面有很多固定的错误信息。执行sqlite3_exec 之后，执行失败时可以查阅这个指针。

2.7、sqlite3_close

函数定义

SQLITE_API int SQLITE_STDCALL sqlite3_close(sqlite3*);

这个过程用于关闭数据库

代码演示：

NSString *select_stmt = [NSString stringWithFormat:
@"select * from people where name = \"%@\"",
self.name.text
];
sqlite3_stmt *stmt;
sqlite3_prepare(db,select_stmt.UTF8String,-1,&stmt,NULL);
while(sqlite3_step(stmt) ==SQLITE_ROW) {
NSString*name = [NSString stringWithUTF8String:(const char*)sqlite3_column_text(stmt,1)];
NSString*address = [NSString stringWithUTF8String:(const char*)sqlite3_column_text(stmt,2)];
NSString*age = [NSString stringWithUTF8String:(const char*)sqlite3_column_text(stmt,3)];
self.name.text= name;
self.address.text= address;
self.age.text= age;
}
sqlite3_finalize(stmt);
sqlite3_close(db);

完整的SQLite3 API 代码戳这里

3、线程安全

在iOS开发时，为了不阻塞主线程，数据库访问必须移到子线程中。从3.3.1版本开始，SQLite就是线程安全的了。而iOS的SQLite版本没有低于这个版本的：

3.4.0 - iPhone OS 2.2.1
3.6.12 - iPhone OS 3.0 / 3.1
3.6.22 - iPhone OS 4.0
3.6.23.2 - iPhone OS 4.1 / 4.2
3.7.2 - iPhone OS 4.3
3.7.7 - iPhone OS 5.0

SQLite支持3种线程模式：

单线程：禁用所有的mutex锁，并发使用时会出错。当SQLite编译时加了SQLITE_THREADSAFE=0参数，或者在初始化SQLite前调用sqlite3_config(SQLITE_CONFIG_SINGLETHREAD)时启用。

多线程：只要一个数据库连接不被多个线程同时使用就是安全的。源码中是启用bCoreMutex，禁用bFullMutex。实际上就是禁用数据库连接和prepared statement（准备好的语句）上的锁，因此不能在多个线程中并发使用同一个数据库连接或prepared statement。当SQLite编译时加了SQLITE_THREADSAFE=2参数时默认启用。若SQLITE_THREADSAFE不为0，可以在初始化SQLite前，调用sqlite3_config(SQLITE_CONFIG_MULTITHREAD)启用；或者在创建数据库连接时，设置SQLITE_OPEN_NOMUTEX flag。

串行：启用所有的锁，包括bCoreMutex和bFullMutex。因为数据库连接和prepared statement都已加锁，所以多线程使用这些对象时没法并发，也就变成串行了。当SQLite编译时加了SQLITE_THREADSAFE=1参数时默认启用。若SQLITE_THREADSAFE不为0，可以在初始化SQLite前，调用sqlite3_config(SQLITE_CONFIG_SERIALIZED)启用；或者在创建数据库连接时，设置SQLITE_OPEN_FULLMUTEX flag。

而这里所说的初始化是指调用sqlite3_initialize()函数，这个函数在调用sqlite3_open()时会自动调用，且只有第一次调用是有效的。

另一个要说明的是prepared statement，它是由数据库连接（的pager）来管理的，使用它也可看成使用这个数据库连接。因此在多线程模式下，并发对同一个数据库连接调用sqlite3_prepare_v2()来创建prepared statement，或者对同一个数据库连接的任何prepared statement并发调用sqlite3_bind_*()和sqlite3_step()等函数都会出错（在iOS上，该线程会出现EXC_BAD_ACCESS而中止）。这种错误无关读写，就是只读也会出错。安全使用规则是：没有事务正在等待执行的话，所有prepared statement都要被finalized。

调用sqlite3_threadsafe()可以获得编译期的SQLITE_THREADSAFE参数。标准发行版是1，也就是串行模式；而iOS上是2，也就是多线程模式。

一段存在线程安全隐患的代码：

iOS上的SQLite默认是多线程模式，多个线程同时使用同一个数据库连接对象，将会产生异常，解决办法的一种就是在sqlite3_open前加上sqlite3_config(SQLITE_CONFIG_SERIALIZED)。

完整的线程安全代码戳这里

4、MFDB

一个使用FMDB的代码示例戳这里

需要说明的是 FMDatabaseQueue 解决了线程安全问题

参考链接：

http://www.runoob.com/sqlite/sqlite-installation.html

http://www.cnblogs.com/kfqcome/archive/2011/06/27/2136999.html

https://www.keakon.net/2011/10/25/SQLite%E5%9C%A8%E5%A4%9A%E7%BA%BF%E7%A8%8B%E7%8E%AF%E5%A2%83%E4%B8%8B%E7%9A%84%E5%BA%94%E7%94%A8

0 0