用数据库写简单的通信录

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

int create_table(sqlite3*db);//表若不存在，则创建
int menu(sqlite3*db);//菜单函数
int insert_table(sqlite3*db);//插入用户信息
int delete_table(sqlite3*db);//删除用户信息
int update_table(sqlite3*db);//修改用户信息
int select_table(sqlite3*db);//查询用户信息
int displaycb(void*para,int col_count,char**col_value,char**col_name);
int select_alltable(sqlite3*db);//显示所有用户信息
int quit(sqlite3*db);//退出函数

int main()//主函数
{
	sqlite3 * db = NULL;
	int ret;

	ret = sqlite3_open("mydatabase.db",&db);//打开数据库，不存在创建

	if(ret != SQLITE_OK)
	{
		perror("打开数据库失败!\n");
		exit(-1);
	}
	else
	{
		printf("成功打开数据库!\n");
	}
	create_table(db);

	menu(db);

	sqlite3_close(db);

    return 0;
}

int create_table(sqlite3*db)//表若不存在，创建表
{
	char *errmsg = NULL;
	char *sql;

	sql = "create table if not exists mytable (name text primary key,age text,qq text,phone text);";

	if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
	{
		printf("创建表失败!%s\n",errmsg);
		exit(-1);
	}
	else
	{
		printf("此时表已经在数据库中！\n");
	}

	return 0;
}
int menu(sqlite3*db)
{
	char ch;

	printf("/*************通信录v1.3**********/\n");
	printf("/**********(1)新建用户信息********/\n");
	printf("/**********(2)删除用户信息********/\n");
	printf("/**********(3)修改用户信息********/\n");
	printf("/**********(4)查找用户信息********/\n");
	printf("/**********(5)显示用户信息********/\n");
	printf("/**********(6)退出程序************/\n");

	while(1)
	{
		printf("请输入操作序号:");
		scanf(" %c",&ch);
		if(ch <= '0' || ch >= '7' )
		{
			printf("输入序号有误，重新输入！\n");
		}
		else
		{
			break;
		}
	}
	switch(ch)
	{
		case '1': insert_table(db);break;
		case '2': delete_table(db);break;
		case '3': update_table(db);break;
		case '4': select_table(db);break;
		case '5': select_alltable(db);break;
		case '6': quit(db);break;
	}
	return 0;
}
int insert_table(sqlite3*db)//创建用户信息
{
	char *errmsg = NULL;
	char sql[100];

	char name[10];
	char age[10];
	char qq[20];
	char phone[20];

	char ch;
	
	printf("输入姓名:");
	scanf("%s",name);
	printf("输入年龄:");
    scanf("%s",age);
	printf("输入QQ:");
	scanf("%s",qq);
	printf("输入电话:");
	scanf("%s",phone);
	
	sprintf(sql,"insert into mytable (name,age,qq,phone) values ('%s','%s','%s','%s');",name,age,qq,phone);

	if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
	{
		printf("创建新用户失败!%s\n",errmsg);
		exit(-1);
	}
	else
	{
		printf("成功创建新用户!\n");
	}

	while(1)
	{
		printf("是(1)否(2)继续操作!\n");
		printf("请输入操作序号:");
		scanf(" %c",&ch);
		if(ch == '1' || ch == '2')
		{
			break;
		}
		else
		{
			printf("输入操作序号有误!\n");
		}
	}

	if(ch == '1')
	{
		insert_table(db);
	}
	else
	{
		menu(db);
	}

	return 0;

}

int delete_table(sqlite3 * db)//删除一行表记录
{
	char *errmsg = NULL;
	char sql[100];
	char name[10];
	char qq[20];
	char phone[20];
	char ret;
	char ch;

	printf("请输入删除用户信息的方式\n");
	printf("       (1)姓名          \n");
	printf("       (2)QQ            \n");
	printf("       (3)电话          \n");
	while(1)
	{
		printf("请输入操作的序号:");
		scanf(" %c",&ret);
		if(ret >= '1' && ret <= '3' )
		{
			break;
		}
		else
		{
			printf("输入序号有误!\n");
		}
	}

	switch(ret)
	{
		case '1':printf("输入删除用户的姓名:");
	              scanf("%s",name);
	              sprintf(sql,"delete from mytable where name = '%s';",name);
				  break;
		case '2':printf("输入删除用户的QQ:");
	              scanf("%s",qq);
	              sprintf(sql,"delete from mytable where qq = '%s';",qq);
				  break;
		case '3':printf("输入删除用户的电话:");
	              scanf("%s",phone);
	              sprintf(sql,"delete from mytable where phone = '%s';",phone);
				  break;
	}

	if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
	{
		printf("删除用户信息失败!%s\n",errmsg);
		exit(-1);
	}
	else
	{
		printf("成功删除用户信息!\n");
	}

	while(1)
	{
		printf("是(1)否(2)继续操作!\n");
		printf("请输入操作序号:");
		scanf(" %c",&ch);
		if(ch == '1'|| ch == '2')
		{
			break;
		}
		else
		{
			printf("输入操作序号有误!\n");
		}
	}

	if(ch == '1')
	{
		delete_table(db);
	}
	else
	{
		menu(db);
	}

	return 0;
}
int update_table(sqlite3 * db)
{
	char *errmsg = NULL;
	char sql[100];

	char oldname[10];
	char name[10];
	char qq[20];
	char phone[20];

	char ch;
	char ret;

	printf("输入修改信息的用户姓名:");
	scanf("%s",oldname);
	printf("请输入需要修改的项目\n");
	printf("       (1)姓名      \n");
	printf("       (2)QQ        \n");
	printf("       (3)电话      \n");
	while(1)
	{
		printf("请输入操作的序号:");
		scanf(" %c",&ret);
		if(ret >= '1' && ret <= '3' )
		{
			break;
		}
		else
		{
			printf("输入序号有误!\n");
		}
	}

	switch(ret)
	{
		case '1':printf("输入新的姓名:");
 	  		     scanf("%s",name);
			     sprintf(sql,"update mytable set name = '%s' where name = '%s';",name,oldname);
			     break;
		case '2':printf("输入新的QQ:");
 	  		     scanf("%s",qq);
			     sprintf(sql,"update mytable set qq = '%s' where name = '%s';",qq,oldname);
			     break;
		case '3':printf("输入新的电话:");
	   		     scanf("%s",phone);
			     sprintf(sql,"update mytable set phone = '%s' where name = '%s';",phone,oldname);
			     break;
	}

	if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
	{
		printf("修改信息失败!%s\n",errmsg);
		exit(-1);
	}
	else
	{
		printf("成功修改用户信息!\n");
	}

	while(1)
	{
		printf("是(1)否(2)继续操作!\n");
		printf("请输入操作序号:");
		scanf(" %c",&ch);
		if(ch == '1' || ch == '2')
		{
			break;
		}
		else
		{
			printf("输入操作序号有误!\n");
		}
	}

	if(ch == '1')
	{
		update_table(db);
	}
	else
	{
		menu(db);
	}

	return 0;
}

int select_table(sqlite3*db)
{
	char *errmsg = NULL;
	char sql[100];

	char name[10];
	char qq[20];
	char phone[20];
	char ch;
	char ret;
	
	printf("请输入查询用户信息的方式\n");
	printf("       (1)姓名      \n");
	printf("       (2)QQ        \n");
	printf("       (3)电话      \n");
	while(1)
	{
		printf("请输入操作的序号:");
		scanf(" %c",&ret);
		if(ret >= '1' && ret <= '3' )
		{
			break;
		}
		else
		{
			printf("输入序号有误!\n");
		}
	}

	switch(ret)
	{
		case '1':printf("输入查询用户的姓名:");
				 scanf("%s",name);
	             sprintf(sql, "select * from mytable where name = '%s';",name);
				 break;
		case '2':printf("输入查询用户的QQ:");
 	             scanf("%s",qq);
	             sprintf(sql, "select * from mytable where qq = '%s';",qq);
				 break;
		case '3':printf("输入查询用户的电话:");
  	             scanf("%s",phone);
	             sprintf(sql, "select * from mytable where phone = '%s';",phone);
				 break;
	}
			

	if(SQLITE_OK != sqlite3_exec(db,sql,displaycb,NULL,&errmsg))
	{
		printf("查询表失败%s\n",errmsg);
		exit(-1);
	}
	else
	{
		printf("成功查询用户信息!\n");
	}

	while(1)
	{
		printf("是(1)否(2)继续操作!\n");
		printf("请输入操作序号:");
		scanf(" %c",&ch);
		if(ch == '1' || ch == '2')
		{
			break;
		}
		else
		{
			printf("输入操作序号有误!\n");
		}
	}

	if(ch == '1')
	{
		select_table(db);
	}
	else
	{
		menu(db);
	}

	return 0;
}
int displaycb(void*para,int col_count,char**col_value,char**col_name)//显示表记录的回调函数
{
	int i;
	for(i = 0; i < col_count; i++)
	{
		printf("%20s",col_value[i]);
	}
	printf("\n");

	return 0;
}
int select_alltable(sqlite3*db)//显示表记录，用sqlite3_get_table函数
{
	int row;
	int column;
	char **aresult;
	char *errmsg = NULL;
	char *sql;
	int i;
	char ch;

	sql = "select * from mytable;";

	if(SQLITE_OK != sqlite3_get_table(db,sql,&aresult,&row,&column,&errmsg))
	{
		printf("显示所有用户信息失败!%s\n",errmsg);
		exit(-1);
	}

	if(row == 0)
	{
		printf("表中没有用户信息!\n");
	}
	else
	{
		for(i = 0; i < (row + 1) * column ; i++)
		{
			printf("%20s",aresult[i]);
		    if((i + 1) % column == 0)
		    {
				printf("\n");
		    }
		}
	}

	sqlite3_free_table(aresult);

	while(1)
	{
		printf("是(1)否(2)继续操作!\n");
		printf("请输入操作序号:");
		scanf(" %c",&ch);
		if(ch == '1'|| ch == '2')
		{
			break;
		}
		else
		{
			printf("输入操作序号有误!\n");
		}
	}

	if(ch == '1')
	{
		select_alltable(db);
	}
	else
	{
		menu(db);
	}

	return 0;
}
int quit(sqlite3*db)
{
	printf("非常感谢你的使用!\n");
	return 0;
}