sqlit3的命令开发与C语言开发

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

一、开发环境配置

1.window环境

	https://www.sqlite.org/download.html
	下载sqlite3源码文件、tools文件、dll文件，后在添加sqlite.exe的环境变量
	可以下载sqlite可视化工具SQLiteStudio

2.Linux环境

	sudo apt-get install sqlite3 	
	apt-get install libsqlite3-dev 

二、命令

1. 系统命令

	.database(查看数据库信息)
	.tables(查看数据库里面有哪些表格)
	.schema(查看表格的结构)
	.help(查看系统命令帮助)
	.q/.quit(退出)

2.sql命令

	create table student(num integer,name varchar,class integer,sex char);	//创建student表格
	drop table student;														//删除student表格
	 insert into student(num,name,class,sex) values (1,'zhangsan',701,'m'); //插入张三的数据
	 select * from student where sex='m';									//显示性别为男的
	 select * from student;													//显示所有
	 select * from student where sex='m' and class=701;						//显示701班男生
	delete from student where class=701;									//删除701班所有记录
	delete from student;													//删除表
	update student set sex='w' where name='zhangsan'						//把张三变成女的

三、C语言开发

sqlite3_open("MyTest.db", &db);						//打开创建,返回值0代表失败
sqlite3_close(db);									//关闭数据库
sqlite3_errmsg(db);									//错误原因

int sqlite3_exec(
   sqlite3*,                                  //数据库指针
   const char *sql,                           //sql命令字符串
   int (*callback)(void*,int,char**,char**),  //回调函数地址
   void *,                                    //回调函数的形参地址
   char **errmsg                              //错误信息
);

int sqlite3_get_table(
	sqlite3 *db,		//打开的数据库的句柄
	const char *zSql,	//要执行的SQL语句
	char ***pazResult,	//结果写入该指针指向的char***
	int *pnRow,			//结果集中行的数目
	int *pnColumn,		//结果集中列的数目
	char **pzErrmsg		//错误信息
);

gcc编译sqlite3程序
gcc xxxx.c -l sqlite3
gcc xxxx.out -o xxx.c -l sqlite3

四、开发例程

#include <stdio.h>
#include <string.h>
#include <sqlite3.h>
#include <stdlib.h>

static int callback(void *NotUsed, int argc, char **argv, char **azColName){	//便利展现成员是需要用回调函数
	int i;
	for(i=0; i<argc; i++){
		printf("%-11s",argv[i]);
	}
	printf("\n");
	return 0;
}

int main(void)
{
	sqlite3 *db;
	char *zErrMsg = 0;
	int rc;

	rc = sqlite3_open("MyTest.db", &db);
	if( rc ){
		fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
		exit(0);
	}else{
		fprintf(stderr, "Opened database successfully\n");
	}
	char sql[128]={};
	strcpy(sql,"create table student(num integer,name varchar,class integer,sex char);");
	if(sqlite3_exec(db,sql,NULL,NULL,&zErrMsg)!=SQLITE_OK){	
		printf("%s\n",zErrMsg);
	}
	else{
		printf("create table ok\n");
	}
	while(1)
	{
		printf("input cmd:\n");
		printf("----------------------------------\n");
		printf("1:insert  2:delete  3:show  4:quit\n");
		printf("----------------------------------\n");
		int cmd;
		scanf("%d",&cmd);
		getchar();
		if(cmd==1){
			memset(sql,0,128);
			int num,class_num;
			char name[20]={},sex[10];
			printf("insert a memble\n");
			printf("input id:\n");
			scanf("%d",&num);
			getchar();

			printf("input name:\n");
			scanf("%s",name);
			getchar();

			printf("input class_num:\n");
			scanf("%d",&class_num);
			getchar();

			printf("input sex:\n");
			scanf("%s",sex);
			getchar();
			memset(sql,0,128);
			sprintf(sql,"insert into student values (%d,'%s',%d,%s);",num,name,class_num,sex);
			if(sqlite3_exec(db,sql,NULL,NULL,&zErrMsg)!=SQLITE_OK){
				printf("%s\n",zErrMsg);
			}
			else{
				printf("insert memble ok\n");
			}
		}
		else if(cmd==2){
			int num;
			printf("input delete num:\n");
			scanf("%d",&num);
			getchar();
			memset(sql,0,128);
			sprintf(sql,"delete from student where num=%d;",num);
			if(sqlite3_exec(db,sql,NULL,NULL,&zErrMsg)!=SQLITE_OK){
				printf("%s\n",zErrMsg);
			}
			else{
				printf("delete memble ok\n");
			}
		}

		else if(cmd==3){
			memset(sql,0,128);
			if(sqlite3_exec(db,"select * from student",callback,NULL,&zErrMsg)!=SQLITE_OK){
				printf("%s\n",zErrMsg);
			}
			else{
				printf("show memble ok\n");
			}
		}
		else if(cmd==4){
			sqlite3_close(db);
			exit(0);
		}
		else{
			printf("input cmd error");
		}
	}
	return 0;
}

总结

sqliite3在嵌入式比较常用，学会可备不时之需，没必要深入需要用时再看。