sqlite 数据库使用笔记

sqlite是一个轻便的数据库，当前公司有项目需要用到轻便型数据库，这个比较方便，当前是属于预研，刚好手里有时间，就做一

下。

sqlite 可以安装在linux下，先下载：

wget http://www.sqlite.org/sqlite-3.6.16.tar.gz //可以在linux下执行，不行的话，在windows浏览器里面输入该网址就可以下载了。

执行如下命令安装：

tar -zxvf sqlite-3.6.16.tar.gz

cd sqlite-3.6.16

编译源码： 

./configure

make;make install

ln -s /usr/local/bin/sqlite3 /usr/bin/sqlite

然后就可以进入sqlite了。如下图：

编写一个简单的.c文件，在.c文件中去操作数据库：

编译命令如下：

gcc sqlite.c -o sqlite -lsqlite3

需要去链接sqlite3动态库，不然会找不到一些sqlite的符号，如下所示：

/usr/lib64/gcc/x86_64-suse-linux/4.8/../../../../x86_64-suse-linux/bin/ld: /tmp/ccprZio4.o: in function `delete':
sqlite.c:(.text+0x6b): undefined reference to `sqlite3_exec'
/usr/lib64/gcc/x86_64-suse-linux/4.8/../../../../x86_64-suse-linux/bin/ld: /tmp/ccprZio4.o: in function `display_usecb':
sqlite.c:(.text+0x161): undefined reference to `sqlite3_exec'
/usr/lib64/gcc/x86_64-suse-linux/4.8/../../../../x86_64-suse-linux/bin/ld: /tmp/ccprZio4.o: in function `insert_record':
sqlite.c:(.text+0x224): undefined reference to `sqlite3_exec'
/usr/lib64/gcc/x86_64-suse-linux/4.8/../../../../x86_64-suse-linux/bin/ld: /tmp/ccprZio4.o: in function `creat_table':
sqlite.c:(.text+0x289): undefined reference to `sqlite3_exec'
/usr/lib64/gcc/x86_64-suse-linux/4.8/../../../../x86_64-suse-linux/bin/ld: /tmp/ccprZio4.o: in function `display_nocb':
sqlite.c:(.text+0x2f3): undefined reference to `sqlite3_get_table'
/usr/lib64/gcc/x86_64-suse-linux/4.8/../../../../x86_64-suse-linux/bin/ld: sqlite.c:(.text+0x3a4): undefined reference to `sqlite3_free_table'
/usr/lib64/gcc/x86_64-suse-linux/4.8/../../../../x86_64-suse-linux/bin/ld: /tmp/ccprZio4.o: in function `main':
sqlite.c:(.text+0x3bf): undefined reference to `sqlite3_open'

 

编译结束之后，在对应目录下就生成一个可执行文件sqlite，在当前目录下执行：./sqlite 即可执行该文件

如下图：

 可能会遇到权限问题，使用chmod 777 *添加权限。

 

sqlite使用的是数据库语句，这里附上sql语句的常用用法：

https://www.cnblogs.com/HJQ2016/p/5791144.html

sqlite.c文件就是调用sqlite的库函数，执行数据库语句：

该代码段转自别的同学，提供给大家参考：

 
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
 
int delete(sqlite3 *db)
{
 
	char *errmsg;
	char sql[100];
	int id;
 
	printf("input a id you want to delete!\n");
	scanf("%d",&id);
 
	sprintf(sql,"delete from mytable where id = %d;",id);
 
	if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
	{
		printf("fail:%s\n",errmsg);
		printf("\n");
		exit(-1);
	}
}
 
int display(void *para,int col_count,char **col_value,char **col_name)
{
	int i;
 
	printf("total column is %d\n",col_count);
 
	for(i = 0; i < col_count; i++)
	{
		printf("%s: %s: \n",col_name[i],col_value[i]);
	}
 
	printf("********************\n");
 
	return 0;
}
 
int display_usecb(sqlite3 *db)
{
 
	char *errmsg;
	char *sql;
 
	sql = "select * from mytable;";
 
	if(SQLITE_OK != sqlite3_exec(db,sql,display,NULL,&errmsg))
	{
		printf("fail:%s\n",errmsg);
		printf("\n");
		exit(-1);
	}
}
 
int insert_record(sqlite3 *db)
{
	
	char *errmsg;
	char sql[100];
	
	int id;
	char name[10];
 
	printf("input a id you want to insert!\n");
	scanf("%d",&id);
 
	printf("input a name you want to insert!\n");
	scanf("%s",name);
 
	sprintf(sql,"insert into mytable (id,name) values (%d,'%s');",id,name);
 
	if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
	{
		printf("fail:%s\n",errmsg);
		printf("\n");
		exit(-1);
	}
	
/*	sql = "insert into mytable (id,name) values (NULL,'qi');";
	if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
	{
		printf("fail:%s\n",errmsg);
		printf("\n");
	}
	
	sql = "insert into mytable (id,name) values (NULL,'fei');";
	if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
	{
		printf("fail:%s\n",errmsg);
		printf("\n");
	}*/
}
 
int creat_table(sqlite3 *db)
{
	char *errmsg;
	char *sql;
 
	sql = "create table if not exists mytable (id integer primary key,name text);";
 
	if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
	{
		printf("fail:%s\n",errmsg);
		printf("\n");
		exit(-1);
	}
}
 
int display_nocb(sqlite3 *db)
{
	int nrow;
	int ncolumn;
	int i;
	char *errmsg;
	char **aresult;
	char *sql;
	
	sql = "select * from mytable;";
 
	if(SQLITE_OK != sqlite3_get_table(db,sql,&aresult,&nrow,&ncolumn,&errmsg))
	{
		printf("file : %s\n",errmsg);
		printf("\n");
		exit(-1);
	}
 
	printf("row : %d	column : %d\n",nrow,ncolumn);
 
	for(i = 0; i < (nrow + 1) * ncolumn; i++)
	{
		printf("%10s",aresult[i]);
 
		if((i + 1) % ncolumn == 0)
		{
			printf("\n");
		}
	}
 
	sqlite3_free_table(aresult);
 
}
 
int main()
{
	int ret;
	sqlite3 *db;
 
	ret = sqlite3_open("test.db",&db);
 
	if(ret != SQLITE_OK)
	{
		perror("sqlite open:");
		exit(-1);
	}
 
	creat_table(db);
	insert_record(db);
	display_usecb(db);
	delete(db);
	//display_usecb(db);
	display_nocb(db);
    return 0;
}