平台：linux

开发语言：c

需要 sqlite3 软件环境

在程序目录先建立 数据文件：

在终端输入命令：sqlite3 student.db--》打开sqlite3 并建立数据文件student.db

在sqlite3 中创建数据表：create tablestudent(id integer primary key,name text,sex text,age text,Chinese integer,English integer,Math integer,avergae integer);

表中一共8个字段：id， name ，sex，age，语文成绩，英语成绩，数学成绩，平均成绩

#include
#include
#include
#include

#include
#include

#define FILENAME "student.db"

void menu_print(void)
{
printf("==================================\n");
printf("1.insert one student's info.\n");
printf("2.delete one student's info.\n");
printf("3.display all student's info.\n");
printf("4.sort student's info.\n");
printf("5.quit.\n");
printf("------------Please input your choice [1-5] :");
}

void delete_menu(void)
{
printf("\n");
printf("\t\t1.delete by name.\n");
printf("\t\t2.delete by id.\n");
printf("\n");
}

int delete_back(void *p,int argc,char *argv[],char *argvv[])
{
*(int *)p = 0;

return 0;
}

int display_back(void *p,char *argvv[])
{
int i;
*(int *)p = 0;

printf("%-3s",argv[0]?argv[0]:"null");
printf(" %-16s",argv[1]?argv[1]:"null");
printf(" %-7s",argv[2]?argv[2]:"null");
printf(" %-4s",argv[3]?argv[3]:"null");
printf(" %-5s",argv[4]?argv[4]:"null");
printf(" %-5s",argv[5]?argv[5]:"null");
printf(" %-5s",argv[6]?argv[6]:"null");
printf(" %-5s",argv[7]?argv[7]:"null");

printf("\n");

return 0;
}

void insert_info(void)
{
int ret;
int id;
int age;
int score[4];
char name[64];
char sex[16];
char *err;
char *sql;
sqlite3 *db;

printf("input your infomation.\n");
printf("name : ");
scanf("%s",name);
printf("sex : ");
scanf("%s",sex);
printf("age : ");
scanf("%d",&age);
printf("Chinese sorce : ");
scanf("%d",&score[0]);
printf("English sorce : ");
scanf("%d",&score[1]);
printf("Math sorce : ");
scanf("%d",&score[2]);

score[3] = (score[0] + score[1] + score[2])/3;

ret = sqlite3_open_v2(FILENAME,&db,sqlITE_OPEN_READWRITE,NULL);
if (ret != sqlITE_OK) {
fputs(sqlite3_errmsg(db),stderr);
putchar('\n');
exit(1);
}

sql = sqlite3_mprintf("insert into student(name,sex,age,Chinese,English,Math,"\
"avergae) values(%Q,%Q,%d,%d);",
name,score[0],score[1],score[2],score[3]);
printf("%s\n",sql);

ret = sqlite3_exec(db,sql,NULL,&err);
if (ret != sqlITE_OK) {
fputs(err,stderr);
putchar('\n');
exit(1);
}

sqlite3_free(sql);
sqlite3_close(db);
}

void delete_by_name(void)
{
int ret;
int p = 1;
char name[64];
char *err = NULL;
char *sql = NULL;
sqlite3 *db;

ret = sqlite3_open_v2(FILENAME,stderr);
printf("\n");
exit(1);
}

printf("Entry the name you want to delete : ");
scanf("%s",name);

sql = sqlite3_mprintf("select * from student where name=%Q;",name);
ret = sqlite3_exec(db,delete_back,&p,stderr);
putchar('\n');
exit(1);
} else if (p == 1) {
printf("No such name!\n");
sqlite3_free(sql);
sleep(1);
return;
}

sqlite3_free(sql);

sql = sqlite3_mprintf("delete from student where name=%Q;",name);
printf("%s\n",sql);

ret = sqlite3_exec(db,stderr);
putchar('\n');
exit(1);
}

sqlite3_free(sql);
sqlite3_close(db);
}

void delete_by_id(void)
{
int ret,id;
int p = 1;
char *err = NULL;
char *sql = NULL;
sqlite3 *db;

ret = sqlite3_open_v2(FILENAME,stderr);
printf("\n");
exit(1);
}

printf("Entry the name you want to delete : ");
scanf("%d",&id);

sql = sqlite3_mprintf("select * from student where name=%d;",id);
ret = sqlite3_exec(db,stderr);
putchar('\n');
exit(1);
} else if (p == 1) {
printf("No such id!\n");
sqlite3_free(sql);
sleep(1);
return;
}

sqlite3_free(sql);

sql = sqlite3_mprintf("delete from student where name=%Q;",id);
printf("%s\n",stderr);
putchar('\n');
exit(1);
}

sqlite3_free(sql);
sqlite3_close(db);
}

int is_read(void)
{
int i;

ioctl(0,FIONREAD,&i);

return i;
}

void delete_choice(void)
{
int delet,i;
char tmp = 'n';
char buf[1024];

while (1) {
delete_menu();
gets(buf);

if (strlen(buf) > 1 || buf[0] < '0' || buf[0] > '3') {
printf("Can't recognition your input message,input again!\n");
printf("\t\tpush Y(continue)\n\t\tpush N(cancle).\n");

system("stty raw");
while (1) {
char ch;
while (is_read() == 0) {
}

ch = getchar();

if ((ch == 'Y' || ch == 'y') && i == 1) {
break;
} else if ((ch == 'n'|| ch == 'N') && i == 1) {
system("stty cooked");
return;
} else if (ch == 13){
printf("\r "
" \r");
fflush(stdout);
i = 0;
}

i++;
}

system("stty cooked");

} else if (buf[0] > '0' && buf[0] < '3') {
delet = buf[0] - '0';
break;
}
}

if (delet == 1) {
delete_by_name();
} else if (delet == 2){
delete_by_id();
}
}

void display_info(void)
{
int ret;
int p = 1;
char *sql;
char *err;
sqlite3 *db;

ret = sqlite3_open_v2(FILENAME,stderr);
printf("\n");
exit(1);
}

printf("---------------------------------------------------------------------\n");
printf("%-3s %-16s %-7s %-4s %-8s %-8s %-8s %-8s\n",
"id","name","sex","age","Chinese","English","Math","average");

ret = sqlite3_exec(db,"select * from student;",display_back,stderr);
printf("\n");
exit(1);
}
printf("---------------------------------------------------------------------\n");

sqlite3_close(db);
}

void sort_info(void)
{
int ret;
int p = 1;
char *err;
sqlite3 *db;

ret = sqlite3_open_v2(FILENAME,stderr);
exit(1);
}

printf("---------------------------------------------------------------------\n");
printf("%-3s %-16s %-7s %-4s %-8s %-8s %-8s %-8s\n","avergae");

ret = sqlite3_exec(db,"select * from student order by avergae desc;",
display_back,stderr);
printf("\n");
exit(1);
} else if (p == 1) {
printf("Datebase is empty!\n");
return;
}
printf("---------------------------------------------------------------------\n");

sqlite3_close(db);
}

int main(int argc,const char *argv[])
{
int choice = 0;
char buf[1024];

while (choice != 5) {
menu_print();

while (1) {
gets(buf);
if (strlen(buf) > 1) {
printf("Can't recognition your input message,input again!\n\n");
menu_print();
continue;
} else {
if (buf[0] > '0' && buf[0] < '6') {
choice = buf[0]- '0';
break;
} else {
printf("Can't recognition your input message,input again!\n\n");
menu_print();
}
}
}

switch(choice) {
case 1:
insert_info();
gets(buf);
break;
case 2:
delete_choice();
break;
case 3:
display_info();
break;
case 4:
sort_info();
break;
case 5:
printf("\t\t\t\t\tBye...\n");
break;
default:
break;
}
}

return 0; }