Ubuntu版本:ubuntu-gnome-16.04-desktop-amd64,gnome版
sqlie:sqlite3
-----------------------------------------------------------------------------------
1. 安装
1.1 下载源码
https://www.sqlite.org/download.html
1.2 编译安装
1) ./configure
2) ./make
3) ./make install
注:如果用apt-get install sqlite3进行安装,只会安装bin文件,不会安装库、头文件等。
1.3 查看是否安装成功: sqlite3 --version
2. 测试
以下demo实现了:创建数据库、创建表格、插入数据、查询数据、删除数据
gcc -o sqlite-test sqlite-test.c -lsqlite3
注:链接加上-lsqlite3
3. 代码
// by baoli
// 2017.05.15
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#define _DEBUG_
int main( void )
{
sqlite3 *db = NULL;
char *zErrMsg = 0;
int rc;
rc = sqlite3_open("test.db", &db); //打开指定的数据库文件,如果不存在将创建一个同名的数据库文件
if( rc )
{
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}
else
printf("\nYou have opened a sqlite3 database named test.db successfully!\n\n");
//创建一个表,如果该表存在,则不创建,并给出提示信息,存储在 zErrMsg 中
char *sql = "CREATE TABLE SensorData(ID INTEGER PRIMARY KEY, SensorID INTEGER, SiteNum INTEGER, Time VARCHAR(12), SensorParameter REAL );" ;
sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );
#ifdef _DEBUG_
if(zErrMsg != NULL) printf("zErrMsg = %s \n", zErrMsg);
#endif
//插入数据
sql = "INSERT INTO \"SensorData\" VALUES(NULL , 11 , 1 , '201705011206', 18.9 );" ;
sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );
sql = "INSERT INTO \"SensorData\" VALUES(NULL , 23 , 45 , '201705011306', 16.4 );" ;
sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );
sql = "INSERT INTO \"SensorData\" VALUES(NULL , 34 , 45 , '201705011306', 15.4 );" ;
sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );
int nrow = 0, ncolumn = 0;
char **azResult; //二维数组存放结果
//查询数据
/*
int sqlite3_get_table(sqlite3*, const char *sql,char***result , int *nrow , int *ncolumn ,char **errmsg );
result中是以数组的形式存放你所查询的数据,首先是表名,再是数据。
nrow ,ncolumn分别为查询语句返回的结果集的行数,列数,没有查到结果时返回0
*/
//查询数据
sql = "SELECT * FROM SensorData ";
sqlite3_get_table( db , sql , &azResult , &nrow , &ncolumn , &zErrMsg );
#ifdef _DEBUG_
if(zErrMsg != NULL) printf("zErrMsg = %s \n", zErrMsg);
#endif
int i = 0 ,j = 0, k = 0 ;
printf( "row=%d column=%d \n" , nrow , ncolumn );
printf( "The result of query is : \n" );
// for( i=0 ; i<( nrow + 1 ) * ncolumn ; i++ )
// printf( "azResult[%d] = %s\n", i , azResult[i] );
for(i = 0; i < nrow + 1; i++)
{
for(j = 0; j < ncolumn; j ++)
{
printf("%-15s ", azResult[k++]);
}
printf("\n");
}
k = 0;
//删除数据
sql = "DELETE FROM SensorData WHERE SensorID = 11 ;" ;
sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );
#ifdef _DEBUG_
if(zErrMsg != NULL) printf("zErrMsg = %s \n", zErrMsg);
#endif
sql = "SELECT * FROM SensorData ";
sqlite3_get_table( db , sql , &azResult , &nrow , &ncolumn , &zErrMsg );
printf( "\n\nrow=%d column=%d " , nrow , ncolumn );
printf( "\nAfter deleting , the result of querying is :\n" );
for(i = 0; i < nrow + 1; i++)
{
for(j = 0; j < ncolumn; j ++)
{
printf("%-15s ", azResult[k++]);
}
printf("\n");
}
k = 0;
//释放掉 azResult 的内存空间
sqlite3_free_table( azResult );
#ifdef _DEBUG_
if(zErrMsg != NULL) printf("zErrMsg = %s \n", zErrMsg);
#endif
sqlite3_close(db); //关闭数据库
return 0;
}