项目功能:主要能实现用户对学生信息的增、删、改、查以及排序等
项目名称:学生管理系统
开发环境:linux /C
开发工具:GCC/GDB
开发流程:
编译流程:
1.cc *.c -lsqlite3
运行l流程:
1. ./a.out
调试效果:
项目源文件由以下三个组成:
1. stu_sys.h
#ifndef __STU_SYS_H__
#define __STU_SYS_H__
#include<stdio.h>
#include<stdlib.h>
#include<string.h>
#include<sqlite3.h>
typedef struct {
char name[10];
char password[10];
}USER;
typedef struct {
char name[10];
int id;
int age;
int score;
}STU;
#define USER_TABLE "user_table"
#define STU_TABLE "stu_table"
#define DB_NAME "student.db"
#endif
2. .stu_sys.c
#include"stu_sys.h"
int sql_exec_string(char *sql_buf,sqlite3 *pdb)
{
int ret ;
char *errmsg;
ret = sqlite3_exec(pdb,sql_buf,NULL,NULL,&errmsg);
if(ret != SQLITE_OK)
{
if(strstr(errmsg,"exist") == NULL)
{
fprintf(stderr,"Fail to sql_exec_string,%s\n",errmsg);
return -1;
}
}
return 0;
}
int input_user_info(USER *info)
{
again:
printf("Name\tPassword\n");
if(scanf("%s%s",info->name,info->password) != 2)
{
printf("Imput error,please input again\n");
while(getchar() != '\n');
goto again;
}
return 0;
}
int insert_user_info(sqlite3 *pdb,USER *pinfo)
{
char sql_buf[1024];
char *errmsg;
sprintf(sql_buf,"insert into %s values('%s','%s');",
USER_TABLE,pinfo->name,pinfo->password);
if(sqlite3_exec(pdb,sql_buf,NULL,NULL,&errmsg) != SQLITE_OK)
{
fprintf(stderr,"Fail to sqlite3_exec(%s),%s\n",sql_buf,errmsg);
return -1;
}
return 0;
}
int select_user_info(sqlite3 *pdb,USER *pinfo)
{
char sql_buf[1024];
int ret;
char **resultp;
int n_row = 0;
int n_cloumn;
char *errmsg;
sprintf(sql_buf,"select * from %s where name='%s' and password='%s';",USER_TABLE,pinfo->name,pinfo->password);
ret = sqlite3_get_table(pdb,sql_buf,&resultp,&n_row,&n_cloumn,&errmsg);
if(ret != SQLITE_OK)
{
fprintf(stderr,"Fail to exec_sql(%s),%s\n",sql_buf,errmsg);
}
if(n_row == 0)
return -1;
return 0;
}
/**************************************
* student manage
*************************************/
int input_stu_info(STU *stu)
{
next:
printf("NAME\tID\tAGE\tSCORE\n");
if(scanf("%s%d%d%d",stu->name,&stu->id,&stu->age,&stu->score) != 4)
{
fprintf(stderr,"Input error,please input again\n");
while(getchar() != '\n');
goto next;
}
return 0;
}
int select_stu_db(sqlite3 *pdb,char *sql_buf)
{
int ret,i,j;
int index;
int n_row = 0,n_cloumn;
char **resultp;
char *errmsg;
ret = sqlite3_get_table(pdb,sql_buf,&resultp,&n_row,
&n_cloumn,&errmsg);
if(ret < 0)
{
fprintf(stderr,"Fail to sqlite3_get_table\n");
}
if(n_row > 0)
{
for(i = 0; i < n_cloumn; i ++)
{
printf("%s\t",resultp[i]);
}
putchar('\n');
index = n_cloumn;
for(i = 0; i < n_row ; i ++)
{
for(j = 0; j < n_cloumn ; j++)
{
printf("%s\t",resultp[index]);
index ++;
}
putchar('\n');
}
sqlite3_free_table(resultp);
return 0;
}
// printf("Fail to select\n");
return -1;
}
3.main.c
#include"stu_sys.h"
int do_quit()
{
printf("system close\n");
exit(0);
}
int system_end()
{
return 0;
}
int do_regiser(sqlite3 *pdb)
{
char sql_buf[1024];
USER info;
input_user_info(&info);
if(select_user_info(pdb,&info) == 0)
{
fprintf(stderr,"The user is exited,please register again\n");
return -1;
}
if(insert_user_info(pdb,&info) < 0)
{
fprintf(stderr,"Fail to register\n");
return -1;
}
return 0;
}
int do_login(sqlite3 *pdb)
{
char sql_buf[1024];
USER info;
input_user_info(&info);
if(select_user_info(pdb,&info) < 0)
{
fprintf(stderr," Login fail, please register\n");
return -1;
}
return 0;
}
int sys_help_info1()
{
printf("*******************\n");
printf("1.register 2.login 3.quit\n");
printf("*******************\n");
return 0;
}
int sys_help_info2()
{
printf("*********************\n");
printf("1.insert_stu_info 2.display_stu_info 3.display_stu_cond\n");
printf("4.delete_stu_cond 5.sort_stu_cond 6.exit stu_system\n");
printf("*********************\n");
return 0;
}
int display_stu_info(sqlite3 *pdb)
{
char sql_buf[1024];
sprintf(sql_buf,"select * from %s;",STU_TABLE);
if(select_stu_db(pdb,sql_buf) < 0)
{
fprintf(stderr,"Fail to display_stu_info\n");
}
return 0;
}
int insert_stu_info(sqlite3 *pdb)
{
char sql_buf[1024];
STU stu;
input_stu_info(&stu);
sprintf(sql_buf,"insert into %s values('%s',%d,%d,%d);",
STU_TABLE,stu.name,stu.id,stu.age,stu.score);
if(sql_exec_string(sql_buf,pdb) < 0)
{
fprintf(stderr,"Fail to insert_stu_info\n");
}
return 0;
}
int display_stu_cond(sqlite3 *pdb)
{
char sql_buf[1024];
char temp[1024];
STU stu;
int cmd;
again:
printf("Please input a condition\n");
printf("*************************\n");
printf("1.name 2.id 3.age 4.score\n");
printf("*************************\n");
if(scanf("%d",&cmd) != 1 || cmd > 4 || cmd <1)
{
fprintf(stderr,"Input error,cmd must [1 - 4]\n");
while(getchar() != '\n');
goto again;
}
switch(cmd)
{
case 1:
printf("Please input name>");
scanf("%s",temp);
while(getchar() != '\n');
sprintf(sql_buf,"select * from %s where name='%s';",STU_TABLE,temp);
select_stu_db(pdb,sql_buf);
break;
case 2:
printf("Please input id>");
scanf("%s",temp);
while(getchar() != '\n');
sprintf(sql_buf,"select * from %s where id=%d;",STU_TABLE,atoi(temp));
select_stu_db(pdb,sql_buf);
break;
case 3:
printf("Please input age>");
scanf("%s",temp);
while(getchar() != '\n');
sprintf(sql_buf,"select * from %s where age>=%d;",STU_TABLE,atoi(temp));
select_stu_db(pdb,sql_buf);
break;
case 4:
printf("Please input score>");
scanf("%s",temp);
while(getchar() != '\n');
sprintf(sql_buf,"select * from %s where score>=%d;",STU_TABLE,atoi(temp));
select_stu_db(pdb,sql_buf);
break;
default:
printf("Unkown cmd\n");
break;
}
return 0;
}
int delete_stu_cond(sqlite3 *pdb)
{
char sql_buf[1024];
char temp[1024];
STU stu;
int cmd;
again:
printf("Please input a condition [1.name 2.id]\n");
printf(">");
if(scanf("%d",&cmd) != 1 || cmd > 4 || cmd <1)
{
fprintf(stderr,"Input error,cmd must [1 - 4]\n");
while(getchar() != '\n');
goto again;
}
switch (cmd)
{
case 1:
scanf("%s",temp);
while(getchar() != '\n');
sprintf(sql_buf,"delete from %s where name='%s';",
STU_TABLE,temp);
select_stu_db(pdb,sql_buf);
break;
case 2:
scanf("%s",temp);
while(getchar() != '\n');
sprintf(sql_buf,"delete from %s where id=%d;",
STU_TABLE,atoi(temp));
select_stu_db(pdb,sql_buf);
break;
default:
printf("Unkown cmd\n");
break;
}
return 0;
}
int sort_stu_cond(sqlite3 *pdb)
{
char sql_buf[1024];
int temp;
STU stu;
int cmd;
again:
printf("Please input a condition [1.id 2.score]\n");
printf(">");
if(scanf("%d",&cmd) != 1 || cmd > 2 || cmd <1)
{
fprintf(stderr,"Input error,cmd must [1 - 4]\n");
while(getchar() != '\n');
goto again;
}
switch (cmd)
{
case 1:
sprintf(sql_buf,"select * from %s order by id;",STU_TABLE);
select_stu_db(pdb,sql_buf);
break;
case 2:
sprintf(sql_buf,"select * from %s order by score;",STU_TABLE);
select_stu_db(pdb,sql_buf);
break;
default:
printf("Unkown cmd\n");
break;
}
return 0;
}
int stu_manage(sqlite3 *pdb)
{
int cmd;
while(1)
{
sys_help_info2();
printf("Please choose:");
if(scanf("%d",&cmd) != 1 || cmd > 6 || cmd < 1)
{
fprintf(stderr,"Input error,must [1 - 6]\n");
while(getchar() != '\n');
continue;
}
switch(cmd)
{
case 1:
insert_stu_info(pdb);
break;
case 2:
display_stu_info(pdb);
break;
case 3:
display_stu_cond(pdb);
break;
case 4:
delete_stu_cond(pdb);
break;
case 5:
sort_stu_cond(pdb);
break;
case 6:
do_quit();
exit(EXIT_SUCCESS);
}
}
return 0;
}
int system_start(sqlite3 *pdb)
{
int cmd;
while(1)
{
sys_help_info1();
printf("Please choose:");
if(scanf("%d",&cmd) != 1 || cmd >3 || cmd < 1)
{
fprintf(stderr,"Input error,must [1 - 3]\n");
while(getchar() != '\n');
continue;
}
switch (cmd)
{
case 1:
do_regiser(pdb);
break;
case 2:
if(do_login(pdb) == 0)
stu_manage(pdb);
break;
case 3:
do_quit();
break;
default:
printf("Unkown cmd\n");
break;
}
}
return 0;
}
int stu_sys_init(sqlite3 **pdb)
{
int ret ;
char sql_buf[1024];
ret = sqlite3_open(DB_NAME,pdb);
if(ret != SQLITE_OK)
{
fprintf(stderr,"Fail to sqlite3_open,%s\n",sqlite3_errmsg(*pdb));
return -1;
}
sprintf(sql_buf,"create table %s(name ,password);",
USER_TABLE);
if(sql_exec_string(sql_buf,*pdb) < 0)
{
fprintf(stderr,"Fail to sql_exec_string\n");
return -1;
}
sprintf(sql_buf,"create table %s(name ,id primary key,age,score);",
STU_TABLE);
if(sql_exec_string(sql_buf,*pdb) < 0)
{
fprintf(stderr,"Fail to sql_exec_string\n");
return -1;
}
return 0;
}
int main(int argc, const char *argv[])
{
sqlite3 *pdb;
if(stu_sys_init(&pdb) < 0)
{
fprintf(stderr,"Fail to stu_sys_init\n");
return -1;
}
system_start(pdb);
system_end();
}