ubuntu下C语言SQLite开发

​ SQLite是轻量级数据库，所有的数据全部存储在一个本地文件中，不支持远程查询。然而，麻雀虽小，五脏俱全，性能相当出色。

一、开发环境搭建

1.安装SQLite

sudo apt-get install sqlite3

2.安装C语言环境

sudo apt-get install libsqlite3-dev

3.测试

a. 编写test.c

#include <sqlite3.h>
void main(){}

b.编译

​ gcc test.c -lsqlite3

4注意事项

​ a.安装的时候指定为SQLite，为2版本；指定SQLite3，为3版本
​ b.链接的时候记得 -lsqlite3

二、实例程序

1.代码样板

/*************************************************************************
    > File Name: test.c
    > Author: peter
    > Mail: [e-mail]
    > Created Time: 2017年04月02日 星期日 15时55分47秒
 ************************************************************************/

#include <stdio.h>
#include <sqlite3.h>

int main(int argc,char *argv[]){
    //创建SQL语句字符串
        const char *sql_create_table="create table hello(id int primary key,msg varchar(128))";
        char sql_insert_table[128] = "insert into hello values(18, \"peter\")" ;

    //错误
        char *errmsg = 0;
    //返回值
        int ret = 0;
    //数据库指针
        sqlite3 *db = 0;
    //打开数据库
        ret = sqlite3_open("./sqlite3-demo.db",&db);
    //如果失败
    if(ret != SQLITE_OK){
                fprintf(stderr,"Cannot open db: %s\n",sqlite3_errmsg(db));
                return 1;
    }
    //成功
        printf("Open database\n");
    //执行SQL 
        ret = sqlite3_exec(db,sql_create_table,NULL,NULL,&errmsg);
    //如果失败 记录错误日志
    if(ret != SQLITE_OK){
                fprintf(stderr,"create table fail: %s\n",errmsg);
    }
    //执行SQL 
        ret = sqlite3_exec(db,sql_insert_table,NULL,NULL,&errmsg);
    //如果失败 记录错误日志
    if(ret != SQLITE_OK){
                fprintf(stderr,"create table fail: %s\n",errmsg);       
    }
    //
        sqlite3_free(errmsg);
    //关闭数据库
        sqlite3_close(db);

        printf("Close database\n");

        return 0;

}

2.官方文档

​ http://www.sqlite.org/cintro.html

​

a.Summary

The following two objects and eight methods comprise the essential elements of the SQLite interface:

​ 以下两个对象和八个方法构成了SQLite接口的基本元素：

sqlite3 → The database connection object. Created by sqlite3_open() and destroyed by sqlite3_close().

​ sqlite3→数据库连接对象。由sqlite3_open（）创建并由sqlite3_close（）销毁。

sqlite3_stmt → The prepared statement object. Created by sqlite3_prepare() and destroyed by sqlite3_finalize().

​ 准备好的语句对象。由sqlite3_prepare（）创建并由sqlite3_finalize（）销毁。

sqlite3_open() → Open a connection to a new or existing SQLite database. The constructor for sqlite3.

​ sqlite3_open（）→打开一个新的或现有的SQLite数据库的连接。 sqlite3的构造函数

sqlite3_prepare() → Compile SQL text into byte-code that will do the work of querying or updating the database. The constructor for sqlite3stmt.

​ sqlite3_prepare（）→将SQL文本编译成将执行查询或更新数据库工作的字节码。 sqlite3_stmt的构造函数。

sqlite3_bind() → Store application data into parameters of the original SQL.

​ sqlite3_bind（）→将应用程序数据存储到原始SQL的参数中。

sqlite3_step() → Advance an sqlite3_stmt to the next result row or to completion.

​ sqlite3_step（）→将sqlite3_stmt提前到下一个结果行或完成。

sqlite3_column() → Column values in the current result row for an sqlite3_stmt.

​ sqlite3_column（）→sqlite3_stmt的当前结果行中的列值。

sqlite3_finalize() → Destructor for sqlite3_stmt.

​ sqlite3_finalize（）→析构函数为sqlite3_stmt。

sqlite3_close() → Destructor for sqlite3.

​ sqlite3_close（）→析构函数为sqlite3。

sqlite3_exec() → A wrapper function that does sqlite3_prepare(), sqlite3_step(), sqlite3_column(), and

sqlite3_finalize() for a string of one or more SQL statements.

​ sqlite3_exec（）→对于一个或多个SQL语句的字符串，使用sqlite3_prepare（），sqlite3_step（），sqlite3_column（）和sqlite3_finalize（）的包装函数。

b.Introduction

​ SQLite has more than 225 APIs. However, most of the APIs are optional and very specialized and can be ignored by beginners. The core API is small, simple, and easy to learn. This article summarizes the core API.

​ A separate document, The SQLite C/C++ Interface, provides detailed specifications for all C/C++ APIs for SQLite. Once the reader understands the basic principles of operation for SQLite, that document should be used as a reference guide. This article is intended as introduction only and is neither a complete nor authoritative reference for the SQLite API.

​ 介绍

​ SQLite有超过225个API。 然而，大多数API是可选的，非常专业，可以被初学者忽略。 核心API是小巧，简单，易于学习。 本文总结了核心API。

​ 一个单独的文档，SQLite C / C ++接口，为SQLite的所有C / C ++ API提供了详细的规范。 一旦读者了解SQLite的基本操作原理，该文档应该被用作参考指南。 本文仅作为介绍，既不是SQLite API的完整描述，也不是权威参考。

c.Core Objects And Interfaces

​ The principal task of an SQL database engine is to evaluate SQL statements of SQL. To accomplish this, the developer needs two objects:

​ The database connection object: sqlite3
​ The prepared statement object: sqlite3_stmt
​ Strictly speaking, the prepared statement object is not required since the convenience wrapper interfaces, sqlite3_exec or sqlite3_get_table, can be used and these convenience wrappers encapsulate and hide the prepared statement object. Nevertheless, an understanding of prepared statements is needed to make full use of SQLite.

​ 核心对象和接口

​ SQL数据库引擎的主要任务是执行SQL的SQL语句。为了实现这一点，开发人员需要两个对象：

​ 数据库连接对象：sqlite3 准备好的语句对象：sqlite3_stmt
​ 严格来说，由于可以使用更为方便的封装接口sqlite3_exec或sqlite3_get_table，因此准备好的语句对象sqlite3_stmt并不是必须要用，这些方便的接口封装并隐藏了准备好的语句对象。然而，需要对准备语句的理解才能充分利用SQLite。

​ （prepared statement object翻译为准备好的语句对象，其实就是SQL语句对象，我没有找到太贴切的翻译描述，如果有，请留言。）

​ The database connection and prepared statement objects are controlled by a small set of C/C++ interface routine listed below.

​ 数据库连接和准备语句对象由下面列出的一小组C / C ++接口例程控制。

sqlite3_open(), sqlite3_prepare(), sqlite3_step(), sqlite3_column(), sqlite3_finalize(), sqlite3_close()

​ Note that the list of routines above is conceptual rather than actual. Many of these routines come in multiple versions. For example, the list above shows a single routine named sqlite3_open() when in fact there are three separate routines that accomplish the same thing in slightly different ways: sqlite3_open(), sqlite3_open16() and sqlite3_open_v2(). The list mentions sqlite3_column() when in fact no such routine exists. The “sqlite3_column()” shown in the list is place holders for an entire family of routines to be used for extracting column data in various datatypes.

​ 请注意，上面的例程列表是概念性的而不是实际的。许多这些例程都有多个版本。例如，上面的列表显示了一个名为sqlite3*open()的例程，实际上有三个单独的例程可以通过稍微不同的方式完成同样的事情：sqlite3*open()，sqlite3*open16()和sqlite3*open_v2()。列表中提到sqlite3*column()，实际上没有这样的例程存在。列表中显示的“sqlite3*column()”是用于提取各种数据类型的列数据的整个系列族的占位符。

​ Here is a summary of what the core interfaces do:

​ sqlite3_open(): This routine opens a connection to an SQLite database file and returns a database connection object. This is often the first SQLite API call that an application makes and is a prerequisite for most other SQLite APIs. Many SQLite interfaces require a pointer to the database connection object as their first parameter and can be thought of as methods on the database connection object. This routine is the constructor for the database connection object.

​ sqlite3_open（） 此例程打开与SQLite数据库文件的连接，并返回数据库连接对象。这通常是应用程序第一个SQLite API调用，并且是大多数其他SQLite API的先决条件。许多SQLite接口需要指向数据库连接对象的指针作为其第一个参数，并且可以被认为是数据库连接对象上的方法。此例程是数据库连接对象的构造函数。

​ sqlite3_prepare(): This routine converts SQL text into a prepared statement object and returns a pointer to that object. This interface requires a database connection pointer created by a prior call to sqlite3_open() and a text string containing the SQL statement to be prepared. This API does not actually evaluate the SQL statement. It merely prepares the SQL statement for evaluation.

​ Think of each SQL statement as a small computer program. The purpose of sqlite3_prepare() is to compile that program into object code. The prepared statement is the object code. The sqlite3_step() interface then runs the object code to get a result.

​ New applications should always invoke sqlite3_prepare_v2() instead of sqlite3_prepare(). The older sqlite3_prepare() is retained for backwards compatibility. But sqlite3_prepare_v2() provides a much better interface.

​ sqlite3_prepare（）此例程将SQL文本转换为准备好的语句对象，并返回指向该对象的指针。此接口需要通过先前调用sqlite3_open（）创建的数据库连接指针和包含要准备的SQL语句的文本字符串。该API实际上并不执行SQL语句。它只是准备用于执行的SQL语句。

​ 将每个SQL语句视为一个小型计算机程序。 sqlite3_prepare（）的目的是将该程序编译成目标代码。准备好的语句是目标代码。 sqlite3_step（）接口然后运行目标代码以获得结果。

​ 新应用程序应始终调用sqlite3_prepare_v2（）而不是sqlite3_prepare（）。旧的sqlite3_prepare（）被保留用于向后兼容。但是sqlite3_prepare_v2（）提供了更好的界面。

​ sqlite3_step(): This routine is used to evaluate a prepared statement that has been previously created by the sqlite3_prepare() interface. The statement is evaluated up to the point where the first row of results are available. To advance to the second row of results, invoke sqlite3_step() again. Continue invoking sqlite3_step() until the statement is complete. Statements that do not return results (ex: INSERT, UPDATE, or DELETE statements) run to completion on a single call to sqlite3_step().

​ sqlite3_step（）此例程用于执行先前由sqlite3_prepare（）接口创建的准备语句。该语句被执行直到第一行结果可用。要进入第二行结果，请再次调用sqlite3_step（）。继续调用sqlite3_step（）直到语句完成。不会返回结果的语句（例如：INSERT，UPDATE或DELETE语句）在单次调用sqlite3_step（）时运行到完成。

​ sqlite3_column(): This routine returns a single column from the current row of a result set for a prepared statement that is being evaluated by sqlite3_step(). Each time sqlite3_step() stops with a new result set row, this routine can be called multiple times to find the values of all columns in that row.

​ As noted above, there really is no such thing as a “sqlite3_column()” function in the SQLite API. Instead, what we here call “sqlite3_column()” is a place-holder for an entire family of functions that return a value from the result set in various data types. There are also routines in this family that return the size of the result (if it is a string or BLOB) and the number of columns in the result set.

​ sqlite3_column（）此例程从正在由sqlite3_step（）执行的准备语句的结果集的当前行中返回单个列。每次sqlite3_step（）停止一个新的结果集行，可以多次调用此例程来查找该行中所有列的值。

​ （step函数每次获得一个记录，而这个函数获得单个列植，通过多次获得列值获得整条记录数据）

​ 如上所述，SQLite API中没有像“sqlite3_column（）”这样的东西。相反，我们称之为“sqlite3_column（）”是整个函数系列的占位符，可以从各种数据类型的结果集返回值。在这个系列中还有一些例程可以返回结果的大小（如果它是一个字符串或BLOB）和结果集中的列数。

​ sqlite3_column_blob(), sqlite3_column_bytes(), sqlite3_column_bytes16(), sqlite3_column_count()
​ sqlite3_column_double(), sqlite3_column_int(), sqlite3_column_int64(), sqlite3_column_text()
​ sqlite3_column_text16(), sqlite3_column_type(), sqlite3_column_value(), _

​ _sqlite3_finalize() This routine destroys a prepared statement created by a prior call to sqlite3_prepare(). Every prepared statement must be destroyed using a call to this routine in order to avoid memory leaks.

​ 此例程会破坏由先前调用sqlite3_prepare（）创建的准备语句。必须使用对此例程的调用来销毁每个准备好的语句，以避免内存泄漏

​ sqlite3_close(): This routine closes a database connection previously opened by a call to sqlite3_open(). All prepared statements associated with the connection should be finalized prior to closing the connection.

​ sqlite3_close（） 此例程关闭以前通过调用sqlite3_open（）打开的数据库连接。与连接相关联的所有准备好的语句应在关闭连接之前完成。