介绍一种乌班图中使用sqlite的用法,非常简单,下面的例子是在乌班图12.04中实现的:
1,先安装两个东西:
sudo apt-get install sqlite sqlite3
sudo apt-get install libsqlite3-dev // 不然可能会报 没有头文件 sqlite3.h
2,C/C++接口:一般用到下面这三个,详情请参考sqlite官方文档。
(1)sqlite3_open(const char *filename, sqlite3 **ppDb):
打开一个数据库连接, 返回sqlite3对象。
(2)sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void *data, char **errmsg):
解析并执行由 sql 参数所给的每个命令,直到字符串结束或者遇到错误为止。
(3)sqlite3_close(sqlite3*):
关闭之前打开的数据库。
3、打开数据库并创建表
#include<stdio.h> #include<sqlite3.h>// 暂时先不管 static int callback(void *NotUsed, int argc, char **argv, char **azColName) {int i = 0;for(i = 0; i < argc; i++){printf("%s = %s\n",azColName[i], argv[i]?argv[i]:"NULL");}printf("\n");return 0; }int main() {sqlite3* db;char *zErrMsg = 0;int rc;char *sql;rc = sqlite3_open("test.db", &db); // 打开数据库if(rc){fprintf(stderr, "Can't open database:%s\n", sqlite3_errmsg(db)); // exit(0);return -1;}else{fprintf(stderr,"open database succeddfully\n");}sql = "create table company(ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE TEXT NOT NULL, ADDRESS CHAR(50))";rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg); // 执行上面sql中的命令if(SQLITE_OK != rc){fprintf(stderr, "SQL error: %s\n", zErrMsg);}else{fprintf(stdout, "create table successfully\n");}sqlite3_close(db);return 0; }
运行:需要链接sqlite3库,下面的其他操作也是如此。
gcc sqlite3test.c -o create -l sqlite3
4、插入数据:
#include<stdio.h> #include<sqlite3.h>static int callback(void *NotUsed, int argc, char **argv, char **azColName) {int i = 0;for(i = 0; i < argc; i++){printf("%s = %s\n",azColName[i], argv[i]?argv[i]:"NULL");}printf("\n");return 0; }int main() {sqlite3* db;char *zErrMsg = 0;int rc;char *sql;rc = sqlite3_open("test.db", &db);if(rc){fprintf(stderr, "Can't open database:%s\n", sqlite3_errmsg(db)); // exit(0);return -1;}else{fprintf(stderr,"open database succeddfully\n");}// sql = "create table company(ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE TEXT NOT NULL, ADDRESS CHAR(50))"; sql = "insert into company(ID, NAME, AGE, ADDRESS) values(1, 'Paul', 25, 'USA');\insert into company(ID, NAME, AGE, ADDRESS) values(2, 'James', 28, 'JAP');\insert into company(ID, NAME, AGE, ADDRESS) values(3, 'Yao', 30, 'CHA');\insert into company(ID, NAME, AGE, ADDRESS) values(4, 'kobe', 38, 'USA');"; rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);if(SQLITE_OK != rc){fprintf(stderr, "SQL error: %s\n", zErrMsg);}else{fprintf(stdout, "insert table successfully\n");}sqlite3_close(db);return 0; }
5、查询数据:
注意:这里的callback函数,是sqlite的一个回调函数,格式如下(必须按照这个格式):
int sqlite_callback(
void* NotUsed, /* 由 sqlite3_exec() 的第四个参数传递而来 */
int argc, /* 表的列数 */
char** argv, /* 指向查询结果的指针数组, 可以由 sqlite3_column_text() 得到 */
char** azColName /* 指向表头名的指针数组, 可以由 sqlite3_column_name() 得到 */
);
执行sqlite3_exec() 时作为参数传进去。
说明:
参数:
NotUsed:由sqlite3_exec传递的初始化参数
argc:表头的列数
azColName:表头的名字数组指针,在我们的例子就是指向{ID, NAME, AGE, ADDRESS}的指针
argv:表头的数据数组指针
返回值:
1:中断查找
0:继续列举查询到的数据
#include<stdio.h> #include<sqlite3.h>static int callback(void *NotUsed, int argc, char **argv, char **azColName) {int i = 0;for(i = 0; i < argc; i++){printf("%s = %s\n",azColName[i], argv[i]?argv[i]:"NULL");}printf("\n");return 0; }int main() {sqlite3* db;char *zErrMsg = 0;int rc;char *sql;rc = sqlite3_open("test.db", &db);if(rc){fprintf(stderr, "Can't open database:%s\n", sqlite3_errmsg(db)); // exit(0);return -1;}else{fprintf(stderr,"open database succeddfully\n");}sql = "select * from company;";rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);if(SQLITE_OK != rc){fprintf(stderr, "SQL error: %s\n", zErrMsg);}else{fprintf(stdout, "select table successfully\n");}sqlite3_close(db);return 0; }
请看输出:
6、更新并查询:
#include<stdio.h> #include<sqlite3.h>static int callback(void *NotUsed, int argc, char **argv, char **azColName) {int i = 0;for(i = 0; i < argc; i++){printf("%s = %s\n",azColName[i], argv[i]?argv[i]:"NULL");}printf("\n");return 0; }int main() {sqlite3* db;char *zErrMsg = 0;int rc;char *sql;rc = sqlite3_open("test.db", &db);if(rc){fprintf(stderr, "Can't open database:%s\n", sqlite3_errmsg(db)); // exit(0);return -1;}else{fprintf(stderr,"open database succeddfully\n");}sql = "update company set ADDRESS = 'RUS' where ID = 3;\select * from company;"; rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);if(SQLITE_OK != rc){fprintf(stderr, "SQL error: %s\n", zErrMsg);}else{fprintf(stdout, "update table successfully\n");}sqlite3_close(db);return 0; }
7、删除并查询:
#include<stdio.h> #include<sqlite3.h>static int callback(void *NotUsed, int argc, char **argv, char **azColName) {int i = 0;for(i = 0; i < argc; i++){printf("%s = %s\n",azColName[i], argv[i]?argv[i]:"NULL");}printf("\n");return 0; }int main() {sqlite3* db;char *zErrMsg = 0;int rc;char *sql;rc = sqlite3_open("test.db", &db);if(rc){fprintf(stderr, "Can't open database:%s\n", sqlite3_errmsg(db)); // exit(0);return -1;}else{fprintf(stderr,"open database succeddfully\n");}sql = "delete from company where ID = 2;\select * from company;"; rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);if(SQLITE_OK != rc){fprintf(stderr, "SQL error: %s\n", zErrMsg);}else{fprintf(stdout, "delete table successfully\n");}sqlite3_close(db);return 0; }
8,上面的例子可在下面找到:
https://github.com/xcywt/MySQLite