2025.1.2

练习：

1> 创建一个工人信息库，包含工号（主键）、姓名、年龄、薪资。

2> 添加三条工人信息（可以完整信息，也可以非完整信息）

3> 修改某一个工人的薪资（确定的一个）

4> 展示出工资在10000到20000之间的所有工人信息

5> 删除掉指定姓名工人的信息

6> 删除工人信息库

#include<myhead.h>
#include<sqlite3.h>sqlite3 * creat_sqlite() //创建数据库并返回数据库句柄
{const char *p = "./my.db";sqlite3 *ppDb;if(sqlite3_open(p,&ppDb)!=SQLITE_OK) //调用数据库提供的第三方库函数{printf("打开数据库失败\n");printf("%s\n",sqlite3_errmsg(ppDb)); //错误信息printf("%d\n",sqlite3_errcode(ppDb)); //错误码printf("sqlite3_open");}return ppDb; //返回数据库句柄
}
void insert_worker(sqlite3 *ppDb) //插入员工信息
{char sql[1024];char *errmsg; //存储错误信息的指针sprintf(sql,"%s","create table worker(num int primary key,name char,age int,salary int);");if(sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg)!=SQLITE_OK){printf("%s\n",sqlite3_errmsg(ppDb)); //错误信息printf("%d\n",sqlite3_errcode(ppDb)); //错误码}printf("创建表格成功\n");int num;char name[100];int age;int salary;printf("请输入你要添加的工人信息:\n");scanf("%d %s %d %d",&num,name,&age,&salary);getchar();snprintf(sql,sizeof(sql),"insert into worker values(%d,\"%s\",%d,%d);",num,name,age,salary);if(sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg)!=SQLITE_OK){printf("%s\n",sqlite3_errmsg(ppDb)); //错误信息printf("%d\n",sqlite3_errcode(ppDb)); //错误码}printf("添加成功\n");}void update_worker(sqlite3 *ppDb)
{int num;int salary;char * errmsg;char sql[1024];printf("请输入你要修改的工人的工号：\n");scanf("%d",&num);getchar();printf("请你输入改的值：\n");scanf("%d",&salary);getchar();snprintf(sql,sizeof(sql),"update worker set salary=%d where num=%d",salary,num);if(sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg)!=SQLITE_OK){printf("%s\n",sqlite3_errmsg(ppDb));printf("%d\n",sqlite3_errcode(ppDb));}printf("修改成功\n");
}
void show(sqlite3 *ppDb)
{char sql[1024];char * errmsg;int salary;const char *sq = "select * from worker where salary>10000 and salary<20000";sqlite3_stmt *stmt;snprintf(sql,sizeof(sql),"select * from worker where salary>10000 and salary<20000");if(sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg)!=SQLITE_OK){printf("%s\n",sqlite3_errmsg(ppDb));printf("%d\n",sqlite3_errcode(ppDb));}int res = sqlite3_prepare_v2(ppDb,sq,-1,&stmt,NULL);printf("工资在10000到20000的工人:\n");while((res = sqlite3_step(stmt))==SQLITE_ROW){printf("num = %d,name = %s,age = %d,salary = %d\n",sqlite3_column_int(stmt,0),sqlite3_column_text(stmt,1),sqlite3_column_int(stmt,2),sqlite3_column_int(stmt,3));}sqlite3_finalize(stmt);
}
void delete_worker(sqlite3 *ppDb)
{char name[20];char * errmsg;char sql[1024];printf("请输入你要删除的工人的姓名：\n");scanf("%s",name);getchar();snprintf(sql,sizeof(sql),"delete from worker where name = '%s' ",name);if(sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg)!=SQLITE_OK){printf("%s\n",sqlite3_errmsg(ppDb));printf("%d\n",sqlite3_errcode(ppDb));}printf("删除成功\n");
}
void delete(sqlite3 *ppDb)
{char name[20];char * errmsg;char sql[1024];snprintf(sql,sizeof(sql),"drop table worker ");if(sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg)!=SQLITE_OK){printf("%s\n",sqlite3_errmsg(ppDb));printf("%d\n",sqlite3_errcode(ppDb));}printf("删除成功\n");
}
void menu()
{int ch;sqlite3 *ppDb;while(1){printf("\t\t\t1、创建数据库\n");printf("\t\t\t2、添加工人信息\n");printf("\t\t\t3、修改工人信息\n");printf("\t\t\t4、展示所有信息\n");printf("\t\t\t5、删除指定工人信息\n");printf("\t\t\t6、删除整表\n");printf("\t\t\t0、退出\n");printf("请输入你的选择：");scanf("%d",&ch);getchar();switch(ch){case 1:{ppDb = creat_sqlite();}break;case 2:{insert_worker(ppDb);}break;case 3:{update_worker(ppDb);}break;case 4:{show(ppDb);}break;case 5:{delete_worker(ppDb);}break;case 6:{delete(ppDb);}break;case 0:exit(0);break;default:printf("输入错误，请重新输入\n");}}
}
int main(int argc, const char *argv[])
{menu(); //调用菜单函数	return 0;
}