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 -j8sudo make install二、命令行操作数据库
1、创建数据库
sqlite3 test.db2、创建数据表
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.06、其他指令自行查阅
三、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 这个函数官网总结章节没有列出来，在绑定章节有说明
另外还有一些不常用接口拓展函数，感兴趣的可以自己了解：
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;
}
五、文章推荐
避免重复造论子，推荐以下文章：
1、sqlite3_exec()详细用法
深入理解SQLite3之sqlite3_exec及回调函数_vs2015中使用sqlite3数据库求某列的最大值,使用回调函数-CSDN博客
2、sqlite3_bind()数据绑定的插入绑定用法
sqlite3：sqlite3_bind 函数_sqlite3 bind text-CSDN博客
3、sqlite3_column()数据绑定的查询绑定用法
sqlite3：sqlite3_column 函数-CSDN博客
原文地址：https://blog.csdn.net/yuxipro/article/details/144392107
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！