score.c

linux平台下基于sqlite数据库的学生成绩管理系统程序

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

#define BUFSIZE 100
#define INSERT 1
#define DELETE 2
#define UPDATE 3
#define SORT 4

int rscallback1(void *p, int argc, char **argv, char **argvv)
{
	int i;

	*(int *)p = 0;
	return 0;
}

int rscallback2(void *p, int argc, char **argv, char **argvv)
{
	int i, j, sum;
	static int k = 0;

	if (*(int *)p)
	{
		*(int *)p = 0;
		printf("No\t");
		for (i = 0; i < argc; i++)
		{
			printf("%s\t", argvv[i]);
		}
		printf("Sum\n");
		for (i = 0; i < 20; i++)
		{
			printf("===");
		}
		putchar('\n');
		k = 0;
	}
	++k;
	printf("%d\t", k);
	for (i = 0; i < argc; i++)
	{
		printf("%s\t", argv[i] ? argv[i] : "NULL");
	}
	sum = 0;
	for (i = 2; i < argc; i++)
	{
		sum += atoi(argv[i]);		
	}
	printf("%d\n", sum);

	return 0; 
}

int main(void)
{
	sqlite3 *db;
	char *err;
	int ret;
	int empty;
	int select;
	char name[20];
	char gender[7];
	int scorea, scoreb, scorec, sum;
	char selectbuf[4], dmlbuf[BUFSIZE];

	ret = sqlite3_open("./score.db", &db);
	if (ret != SQLITE_OK)	
		goto error;

	while (1)
	{
		err = NULL;
		ret = 0;
		empty = 1;
		select = -1;
		sum = 0;
		printf("1. Insert Record\n");
		printf("2. Delete Record\n");
		printf("3. Update Record\n");
		printf("4. Sort\n");
		printf("Please Select [1-4]: ");
		scanf("%s", selectbuf);
		putchar('\n');
		select = atoi(selectbuf);
		if (select!=INSERT && select!=DELETE && select!=UPDATE && select!=SORT)
			continue;
		
		if (select == INSERT)
		{
			printf("Now Inserting Record...\n");
			printf("Input Name: "); 
			scanf("%s", name);
			putchar('\n');
			printf("Input Gender: ");
			scanf("%s", gender);
			putchar('\n');
			printf("Input Score A: ");
			scanf("%d", &scorea);
			putchar('\n');
			printf("Input Score B: ");
			scanf("%d", &scoreb);
			putchar('\n');
			printf("Input Score C: ");
			scanf("%d", &scorec);
			putchar('\n');
			
			snprintf(dmlbuf,BUFSIZE,"insert into stud_score values ('%s', '%s', %d, %d, %d);", name, gender, scorea, scoreb, scorec);
			ret = sqlite3_exec(db, dmlbuf, NULL, NULL, &err);
			if (ret != SQLITE_OK)
				goto error;
			printf("Record Inserted\n");
			continue;
		}
		if (select == DELETE)
		{
			snprintf(dmlbuf, BUFSIZE, "select * from stud_score;");
			ret = sqlite3_exec(db, dmlbuf, rscallback1, &empty, &err);
			if (ret != SQLITE_OK)
				goto error;
			if(empty)
			{
				fputs("table stud_score is empty\n", stderr);
				continue;
			}
			printf("Input Name: ");
			scanf("%s", name);
			putchar('\n');
			ret = 0;
			empty = 1;
			err = NULL;
			snprintf(dmlbuf, BUFSIZE, "select * from stud_score where name='%s';", name);
			ret = sqlite3_exec(db, dmlbuf, rscallback1, &empty, &err);
			if (ret != SQLITE_OK)
				goto error;
			if(empty)
			{
				printf("No such Record!\n");
				continue;
			}
			ret = 0;
			err = NULL;
			snprintf(dmlbuf, BUFSIZE, "delete from stud_score where name='%s';", name);
			ret = sqlite3_exec(db, dmlbuf, NULL, NULL, &err);
			if (ret != SQLITE_OK)
				goto error;
			printf("Record Deleted\n");
			continue;
		}
		if (select == UPDATE)
		{
			
			snprintf(dmlbuf, BUFSIZE, "select * from stud_score;");
			ret = sqlite3_exec(db, dmlbuf, rscallback1, &empty, &err);
			if (ret != SQLITE_OK)
				goto error;
			if(empty)
			{
				fputs("table stud_score is empty\n", stderr);
				continue;
			}
			ret = 0;
			empty = 1;
			err = NULL;
			printf("Input Name: ");
			scanf("%s", name);
			putchar('\n');
			snprintf(dmlbuf, BUFSIZE, "select * from stud_score where name='%s';", name);
			ret = sqlite3_exec(db, dmlbuf, rscallback1, &empty, &err);
			if (ret != SQLITE_OK)
				goto error;
			if(empty)
			{
				printf("No such Record!\n");
				continue;
			}
			err = NULL;
			ret = 0;
			snprintf(dmlbuf,BUFSIZE,"delete from stud_score where name='%s';",name);
			ret = sqlite3_exec(db, dmlbuf, NULL, NULL, &err);
			if (ret != SQLITE_OK)
				goto error;
			err = NULL;
			ret = 0;
			printf("Now Updating Record...\n");
			printf("Input Name: ");
			scanf("%s", name);
			putchar('\n');
			printf("Input Gender: ");
			scanf("%s", gender);
			putchar('\n');
			printf("Input Score A: ");
			scanf("%d", &scorea);
			putchar('\n');
			printf("Input Score B: ");
			scanf("%d", &scoreb);
			putchar('\n');
			printf("Input Scoe C: ");
			scanf("%d", &scorec);
			putchar('\n');

			snprintf(dmlbuf,BUFSIZE,"insert into stud_score values('%s', '%s', %d, %d, %d);",name,gender,scorea,scoreb,scorec);
			ret = sqlite3_exec(db, dmlbuf, NULL, NULL, &err);
			if (ret != SQLITE_OK)
				goto error;
			printf("Record Updated\n");
			continue;
		}
		if (select == SORT)
		{
			snprintf(dmlbuf,BUFSIZE,"select * from stud_score order by ScoreA+ScoreB+ScoreC desc;");
			ret = sqlite3_exec(db, dmlbuf, rscallback2, &empty, &err);
			if (ret != SQLITE_OK)
				goto error;
			if(empty)
			{
				fputs("table stud_score is empty\n", stderr);
			}
		}
		continue;

error:
	fputs(err, stderr);
	fputs("\n", stderr);
	sqlite3_close(db);
	exit(1);
	}
}