04/13课后作业

 (没有查)

#include "head.h"

void do_insert();
void do_delete();
void do_update();
void do_select();
char sql[128] = "";
sqlite3* db = NULL;
char* errmsg = NULL;

int main(int argc, const char *argv[])
{	
	//如果数据库不存在，则创建后直接打开
	//如果存在则直接打开
	if(sqlite3_open("./my.db",&db) != SQLITE_OK)
	{
		fprintf(stderr,"sqlite3_open : %s errcode : %d\n",sqlite3_errmsg(db),sqlite3_errcode(db));
		return -1;
	}
	printf("sqlite3_open success\n");

	//创建一个表
	//注意：C代码中编写的sql语句与在数据库中编写一致
	strcat(sql,"create table if not exists stu (id int,name char,score float);");

	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK)
	{
		fprintf(stderr,"line: %d  sqlite3_exec: %s\n",__LINE__,errmsg);
		return -1;
	}
	printf("create table stu success\n");

	char c = 0;
	while(1)
	{
		//system("clear");
		printf("------------------------\n");
		printf("--------1. 增-----------\n");
		printf("--------2. 删-----------\n");
		printf("--------3. 改-----------\n");
		printf("--------4. 查-----------\n");
		printf("--------5. 退出---------\n");
		printf("------------------------\n");
		printf("请选择功能>>> ");
		c = getchar();
		while(getchar() != 10);

		switch(c)
		{
			case '1':
				do_insert();
				break;
			case '2':
				do_delete();
				break;
			case '3':
				do_update();
				break;
			case '4':
				do_select();
				break;
			case '5':
				goto END;
				break;
			default:
				printf("输入错误，请重新输入\n");
		}
	//	printf("输入任意字符清屏>>> \n");
		while(getchar() != 10);

	}

END:
	//关闭数据库
	if(sqlite3_close(db) != SQLITE_OK)
	{
		fprintf(stderr,"sqlite3_close : %s errcode : %d\n",sqlite3_errmsg(db),sqlite3_errcode(db));
		return -1;
	}

	return 0;
}

void do_insert()
{
	bzero(sql,sizeof(sql));
	strcat(sql,"INSERT INTO stu VALUES (");
	char input[32] = "";
	printf("输入格式为---> id, 'name', sore >>> ");
	scanf("%s",input);
	strcat(sql,input);
	strcat(sql,");");
	//printf("%s\n",sql);	
	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK)
	{
		fprintf(stderr,"line: %d  sqlite3_exec: %s\n",__LINE__,errmsg);
		return ;
	}
	printf("增 成功\n");

	return ;
}
void do_delete()
{
	bzero(sql,sizeof(sql));
	strcat(sql,"DELETE FROM stu WHERE id=");
	char input[32] = "";
	printf("输入要删除信息的id号>>> ");
	scanf("%s",input);
	strcat(sql,input);
	strcat(sql,";");
	//printf("%s\n",sql);	
	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK)
	{
		fprintf(stderr,"line: %d  sqlite3_exec: %s\n",__LINE__,errmsg);
		return ;
	}
	printf("删 成功\n");

	return ;
}
void do_update()
{
	bzero(sql,sizeof(sql));
	strcat(sql,"UPDATE stu SET ");
	char input[32] = "";
	char id[8] = "";
	printf("输入修改的字段和修改后的值以及id号>>> ");
	scanf("%s",input);
	strcat(sql,input);
	scanf("%s",id);
	strcat(sql," WHERE id=");
	strcat(sql,id);
	strcat(sql,";");
	//printf("%s\n",sql);	
	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK)
	{
		fprintf(stderr,"line: %d  sqlite3_exec: %s\n",__LINE__,errmsg);
		return ;
	}
	printf("改 成功\n");

	return ;
}
void do_select()
{
/*
	bzero(sql,sizeof(sql));
	strcat(sql,".header on");
	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK)
	{
		fprintf(stderr,"line: %d  sqlite3_exec: %s\n",__LINE__,errmsg);
		return ;
	}

	bzero(sql,sizeof(sql));
	strcat(sql,".mode column");
	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK)
	{
		fprintf(stderr,"line: %d  sqlite3_exec: %s\n",__LINE__,errmsg);
		return ;
	}
*/
	bzero(sql,sizeof(sql));
	strcat(sql,"SELECT * FROM stu WHERE ");
	char input[32] = "";
	printf("输入查找的条件>>> ");
	scanf("%s",input);
	strcat(sql,input);
	strcat(sql,";");
	//printf("%s\n",sql);		
	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK)
	{
		fprintf(stderr,"line: %d  sqlite3_exec: %s\n",__LINE__,errmsg);
		return ;
	}
	printf("查 成功\n");

	return ;
}