数据库制表

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

int create_table(sqlite3 *db)
{
	char *errmsg = NULL;
	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("create error:%s\n",errmsg);
		exit(-1);
	}
}

int insert_record(sqlite3 *db)
{
	char *errmsg = NULL;
	char sql[1024];
	int i;
	char name[20];

	printf("please input id and name:\n");
	scanf("%d",&i);
	scanf("%s",name);

/*	sql = "insert into mytable (id,name) values (1,'xxy');";*/
	sprintf(sql,"insert into mytable (id,name) values (%d,'%s');",i,name);

	if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
	{
		printf("insert error:%s\n",errmsg);
		exit(-1);
	}

/*	sql = "insert into mytable (id,name) values (3,'xy');";

	if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
	{
		printf("insert error:%s\n",errmsg);
		exit(-1);
	}

	sql = "insert into mytable (id,name) values (2,'xx');";

	if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
	{
		printf("insert error:%s\n",errmsg);
		exit(-1);
	}*/

}

int displaycb(void *para,int n_col,char **col_value,char **col_name)
{
	int i;
	printf("total column is %d\n",n_col);
	printf("id  name\n");
	for(i = 0;i < n_col;i++)
	{
		printf("column name:%s -> column value: %s\n",col_name[i],col_value[i]);
		printf("=============================================\n");

	}

	return 0;
}

int delete_table(sqlite3 *db)
{
	char sql[100];
	char *errmsg;
	int id;
	
	printf("please input id:");
	scanf("%d",&id);

	sprintf(sql,"delete from mytable where id = %d;",id);

	if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
	{
		printf("delete error:%s\n",errmsg);
		exit(-1);
	}

}

int inquire_usecb(sqlite3 *db)
{
	char *errmsg = NULL;
	char *sql;

	sql = "select * from mytable;";

	if(SQLITE_OK != sqlite3_exec(db,sql,displaycb,NULL,&errmsg))
	{
		printf("use callback  error:%s\n",errmsg);
		exit(-1);
	}

}

int inquire_nocb(sqlite3 *db)
{
	int nrow,ncolumn;
	char **azresult;
	char *sql;
	char *errmsg;
	int i;

	sql = "select * from mytable;";

	if(SQLITE_OK != sqlite3_get_table(db,sql,&azresult,&nrow,&ncolumn,&errmsg))
	{
		printf("operator failed:%s\n",errmsg);
		exit(-1);
	}

	printf("row:%d    column:%d\n",nrow,ncolumn);
	printf("the result of querying is:\n");
	for(i = 0;i < (nrow + 1) * ncolumn;i++)
	{
		printf("%10s",azresult[i]);
		if((i + 1) % ncolumn == 0)
		{
			printf("\n");
		}
	}
	sqlite3_free_table(azresult);
}

int main()
{
	sqlite3 *db = NULL;
	int ret;
	ret = sqlite3_open("mydatabase.db",&db);
	if(ret != SQLITE_OK)
	{
		perror("sqlite open!\n");
		exit(-1);
	}
	else
	{
		printf("open succeefully!\n");
	}

	create_table(db);
	insert_record(db);
	inquire_usecb(db);
	delete_table(db);
	inquire_nocb(db);
	close(db);

	return 0;
}