Linux C语言操作sqlite3数据库

一、环境配置

1、下载源码：sqlite-autoconf-3470200.tar.gz

2、解压，cd到源码主目录

3、配置参数

./configure --prefix=/usr/local/

如果是交叉编译环境

./configure CC=/opt/rk3288/gcc-linaro/bin/arm-linux-gnueabihf-gcc --host=arm-linux --prefix=/opt/rk3288/sqlite3/

4、编译 & 安装

make -j8

sudo make install

二、命令行操作数据库

1、创建数据库

sqlite3 test.db

2、创建数据表

sqlite> CREATE TABLE COMPANY(ID INT PRIMARY KEY     NOT NULL,NAME           TEXT    NOT NULL,AGE            INT     NOT NULL,ADDRESS        CHAR(50),SALARY         REAL
);

3、查看数据表

sqlite>.tables
COMPANY     DEPARTMENT

4、查看表结构

sqlite>.schema COMPANY
CREATE TABLE COMPANY(ID INT PRIMARY KEY     NOT NULL,NAME           TEXT    NOT NULL,AGE            INT     NOT NULL,ADDRESS        CHAR(50),SALARY         REAL
);

5、插入表数据

sqlite>INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (1, 'Paul', 32, 'California', 20000.00 );sqlite>INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (2, 'Allen', 25, 'Texas', 15000.00 );sqlite>INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );

5、查看表数据

sqlite> select * from COMPANY;
1|Paul|32|California|20000.0
2|Allen|25|Texas|15000.0
3|Teddy|23|Norway|20000.0

6、其他指令自行查阅

三、C语言sqlite3接口

1、接口类型和函数

sqlite常用接口函数很少，官网文档可以直接看到

sqlite3 → The database connection object. Created by sqlite3_open() and destroyed by sqlite3_close().
sqlite3_stmt → The prepared statement object. Created by sqlite3_prepare() and destroyed by sqlite3_finalize().
sqlite3_open() → Open a connection to a new or existing SQLite database. The constructor for sqlite3.
sqlite3_prepare() → Compile SQL text into byte-code that will do the work of querying or updating the database. The constructor for sqlite3_stmt.
sqlite3_bind() → Store application data into parameters of the original SQL.
sqlite3_step() → Advance an sqlite3_stmt to the next result row or to completion.
sqlite3_column() → Column values in the current result row for an sqlite3_stmt.
sqlite3_finalize() → Destructor for sqlite3_stmt.
sqlite3_close() → Destructor for 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_reset() reset bind 这个函数官网总结章节没有列出来，在绑定章节有说明

另外还有一些不常用接口拓展函数，感兴趣的可以自己了解：

sqlite3_create_collation()
sqlite3_create_function()
sqlite3_create_module()
sqlite3_vfs_register()

sqlite3_aggregate_context()
sqlite3_result()
sqlite3_user_data()
sqlite3_value()

2、接口分类

我个人将接口分为两类

1）直接操作sql函数（初阶用法，更接近sql命令行）

sqlite3_open()打开数据库

sqlite3_exec()执行sql命令并返回结果

sqlite3_close()关闭数据库

2）sql数据绑定（高阶用法，更方便程序操作）

数据绑定分为查询绑定和插入绑定

sqlite3_prepare() 绑定sql命令

sqlite3_finalize() 结束绑定

sqlite3_reset() 重置绑定

插入绑定：

sqlite3_bind() 设置绑定列数据

sqlite3_step() 切换下一条记录

查询绑定：

sqlite3_column() 返回绑定列数据

sqlite3_step() 切换下一条记录

四、实例代码

1、打开关闭数据库

#include <stdio.h>
#include <sqlite3.h>int main(int argc, char* argv[])
{sqlite3 *db;char *zErrMsg = 0;int rc;rc = sqlite3_open("test.db", &db);if( rc ){fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));exit(0);}else{fprintf(stderr, "Opened database successfully\n");}sqlite3_close(db);
}

2、执行sql指令

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h> static int callback(void *NotUsed, int argc, char **argv, char **azColName){int i;for(i=0; i<argc; i++){printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");}printf("\n");return 0;
}int main(int argc, char* argv[])
{sqlite3 *db;char *zErrMsg = 0;int  rc;char *sql;/* Open database */rc = sqlite3_open("test.db", &db);if( rc ){fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));exit(0);}else{fprintf(stdout, "Opened database successfully\n");}/* Create SQL statement */sql = "CREATE TABLE COMPANY("  \"ID INT PRIMARY KEY     NOT NULL," \"NAME           TEXT    NOT NULL," \"AGE            INT     NOT NULL," \"ADDRESS        CHAR(50)," \"SALARY         REAL );";/* Execute SQL statement */rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);if( rc != SQLITE_OK ){fprintf(stderr, "SQL error: %s\n", zErrMsg);sqlite3_free(zErrMsg);}else{fprintf(stdout, "Table created successfully\n");}sqlite3_close(db);return 0;
}