MySQL职工管理系统

#include <mysql.h>
#include <iostream>
#include <string>


using namespace std;


MYSQL *mysql;
int id; //职工号
int age;//年龄
int postcode;//邮编
int salary;//工资


string name;//姓名
string sex;//性别
string department;//部门


int w_id; //职工号
int w_age;//年龄
int w_postcode;//邮编
int w_salary;//工资


string w_name;//姓名
string w_sex;//性别
string w_department;//部门 int num;


void menu();
void add();
void dele();
void search();
void change();
void sal_sort();
void visitworker();


int main()
{
mysql = mysql_init(NULL);



if (NULL == mysql)
{
cout<<"error..."<<mysql_error(mysql)<< endl;
}


my_bool reconnect = true;
mysql_options(mysql, MYSQL_OPT_RECONNECT, &reconnect);
mysql_options(mysql, MYSQL_SET_CHARSET_NAME, "gbk");


mysql_real_connect(mysql, "localhost", "root", "123456", "work", 3306, NULL, 0);



int num;


while (1)
{
menu();
cout << "请输入你要选择的功能选项(0-7)：" << endl;
cin >> num;


switch(num)
{
case 1:
{
add();
break;
}
case 2:
{
change();
break;
}
case 3:
{
dele();
break;
}
case 4:
{
sal_sort();
break;
}
case 5:
{
search();
break;
}
case 6:
{
visitworker();
break;
}
case 0:
{
exit(1);
}
default:
{
cout << "输入值无效，请重新输入" << endl;
}
}
}



mysql_close(mysql);


return 0;


}


void add() //注册职工信息
{
cout <<"请输入职工的职工号 :"<< endl;
cin >> w_id;


cout <<"请输入职工的年龄 :"<< endl;
cin >> w_age;


cout <<"请输入职工的邮编 :"<< endl;
cin >> w_postcode;

cout <<"请输入职工的工资 :"<< endl;
cin >> w_salary;


cout <<"请输入职工的姓名 :"<< endl;
cin >> w_name;


cout <<"请输入职工的性别 :"<< endl;
cin >> w_sex;


cout <<"请输入职工的部门 :"<< endl;
cin >> w_department;


//int ret = mysql_query(mysql, "insert into values (id, age, postcode, salary, 'name', 'sex', 'department'");


char *insert_str = "insert into workers (id, age, postcode, salary, name, sex, department) values (%d, %d, %d, %d, '%s', '%s', '%s')";

char str[1024];
int ret;
//插入注册信息到workers表
sprintf(str, insert_str, w_id, w_age, w_postcode, w_salary, w_name.c_str(), w_sex.c_str(), w_department.c_str());


ret = mysql_query(mysql, str);
if (0 != ret)
{
   cout<<"error---"<<mysql_error(mysql)<< endl;
}
else
{
cout << "**  信息注册成功  **" << endl;
}


}


void dele()//删除职工信息(通过id删除)
{
int w_id;


cout << "请输入你要删除的职工id :" << endl;
cin >> w_id; //输入你要删除的职工id;


char *dele_str = "delete from workers where id = %d";
char str[1024];
sprintf(str, dele_str, w_id);


int ret = mysql_query(mysql, str);
if (0!= ret)
{
cout<<"error+++"<<mysql_error(mysql)<< endl;
}
else
{
cout << "**  信息删除成功  **" << endl;
}

}


void search()
{
string w_name; //定义要查询的名字
string w_department; //定义要查询的部门
char str[1024];
char *search_str = "select * from work.workers where name = '%s'";
char *search_str2 = "select * from work.workers where department = '%s'";


int num; //功能选项


cout << "**   1.通过名字查询   **" << endl;
cout << "**   2.通过部门查询   **" << endl;


do 
{
cout <<"********************************"<< endl;
cout <<"请输入你的选择: " << endl;
cin >> num;


} while (num < 1 || num > 2 );


switch(num)
{
case 1:
{
cout << "请输入名字 : " << endl;
cin >> w_name;


sprintf(str, search_str, w_name.c_str());
// cout << "22222" << endl;
MYSQL_RES *mysql_res;
MYSQL_ROW mysql_row;

int ret = mysql_query(mysql, str);
if (0!= ret)
{
cout<<"error***"<<mysql_error(mysql)<< endl;
}
else
{
   mysql_res = mysql_store_result(mysql);
//获取字段个数
int col = mysql_num_fields(mysql_res);
//cout << col << endl;


//获取返回结果的行数
int row = mysql_num_rows(mysql_res);
cout << "共查询到" << row << "条信息：" << endl;
cout << endl;
cout << "工号" << "\t" << "年龄" << "\t" << "邮编" << "\t" << "工资" << "\t" << "名字" << "\t" << "性别" << "\t" << "部门" << endl;


while((mysql_row = mysql_fetch_row(mysql_res)))
{
for (int i = 0; i < col ;i++)
{
cout << ( mysql_row[i] ? mysql_row[i] : "NULL") <<"\t";
}
cout << endl;
}


}
break;
}
case 2:
{
cout << "请输入部门 : " << endl;
cin >> w_department;


sprintf(str, search_str2, w_department.c_str());


MYSQL_RES *mysql_res;

MYSQL_ROW mysql_row;


int ret = mysql_query(mysql, str);
if (0!= ret)
{
cout<<"error///"<<mysql_error(mysql)<< endl;
}
else
{
  mysql_res = mysql_store_result(mysql);
//获取字段个数
int col = mysql_num_fields(mysql_res);
//cout << col << endl;


//获取返回结果的行数
int row = mysql_num_rows(mysql_res);
cout << "共查询到" << row << "条信息：" << endl;
cout << endl;
cout << "工号" << "\t" << "年龄" << "\t" << "邮编" << "\t" << "工资" << "\t" << "名字" << "\t" << "性别" << "\t" << "部门" << endl;


while((mysql_row = mysql_fetch_row(mysql_res)))
{
for (int i = 0; i < col ;i++)
{
cout << ( mysql_row[i] ? mysql_row[i] : "NULL") <<"\t";
}
cout << endl;
}


}


break;
}
}
}


void change()//根据ID修改职工信息
{
int w_id; //职工号
int w_age;//年龄
int w_postcode;//邮编
int w_salary;//工资


string w_name;//姓名
string w_sex;//性别
string w_department;//部门
int num;
int flag = 1;


cout <<"请输入职工号:"<<endl;
cin >> w_id;


if (flag == 1)
{
cout <<"--------------------------"<< endl;
cout <<"|       1.修改年龄       |"<< endl;
cout <<"|       2.修改邮编       |"<< endl; 
cout <<"|       3.修改工资       |"<< endl;
cout <<"|       4.修改姓名       |"<< endl;
cout <<"|       5.修改性别       |"<< endl;
cout <<"|       6.修改部门       |"<< endl;
cout <<"|       7.返回菜单       |"<< endl;
cout <<"--------------------------"<< endl;


cout << endl;


do 
{
cout << "请输入你要选择的操作(1-7) :" << endl;
cin >> num;


} while (num < 1 || num > 7 );


switch(num)
{
case 1:
{
cout << "请输入你要修改的年龄：" << endl;
cin >> w_age;
char str[1024];
char *up_str = "update work.workers set age = %d where id = %d";
sprintf(str, up_str, w_age, w_id);


int ret = mysql_query(mysql, str);
if (ret != 0)
{
cout << "error : update" << endl;
}
else
{
cout << " 修改成功！" << endl;
}
break;
}
case 2:
{
cout << "请输入你要修改的邮编：" << endl;
cin >> w_postcode;
char str[1024];
char *up_str = "update work.workers set postcode = %d where id = %d";
sprintf(str, up_str, w_postcode, w_id);


int ret = mysql_query(mysql, str);
if (ret != 0)
{
cout << "error : update" << endl;
}
else
{
cout << "修改成功！" << endl;
}
break;
}
case 3:
{
cout << "请输入你要修改的工资：" << endl;
cin >> w_salary;
char str[1024];
char *up_str = "update work.workers set salary = %d where id = %d";
sprintf(str, up_str, w_salary, w_id);


int ret = mysql_query(mysql, str);
if (ret != 0)
{
cout << "error : update" << endl;
}
else
{
cout << "修改成功！" << endl;
}
break;
}
case 4:
{
cout << "请输入你要修改的姓名：" << endl;
cin >> w_name;
char str[1024];
char *up_str = "update work.workers set name = '%s' where id = %d";
sprintf(str, up_str, w_name.c_str(), w_id);


int ret = mysql_query(mysql, str);
if (ret != 0)
{
cout << "error : update" << endl;
}
else
{
cout << "修改成功！" << endl;
}
break;
}
case 5:
{
cout << "请输入你要修改的性别：" << endl;
cin >> w_sex;
char str[1024];
char *up_str = "update work.workers set sex = '%s' where id = %d";
sprintf(str, up_str, w_sex.c_str(), w_id);


int ret = mysql_query(mysql, str);
if (ret != 0)
{
cout << "error : update" << endl;
}
else
{
cout << "修改成功！" << endl;
}
break;
break;
}
case 6:
{
cout << "请输入你要修改的部门：" << endl;
cin >> w_department;
char str[1024];
char *up_str = "update work.workers set department = '%s' where id = %d";
sprintf(str, up_str, w_department.c_str(), w_id);


int ret = mysql_query(mysql, str);
if (ret != 0)
{
cout << "error : update" << endl;
}
else
{
cout << "修改成功！" << endl;
}
break;
break;
}
case 7:
{
return ;
break;
}
}
}


}


void sal_sort()//按照工资排序
{
char *sort_str = "select * from work.workers order by salary";
char str[1024];
sprintf(str, sort_str);


MYSQL_RES *mysql_res;
MYSQL_ROW mysql_row;


int ret = mysql_query(mysql, str);
if (0!= ret)
{
cout<<"error***"<<mysql_error(mysql)<< endl;
}
else
{
cout << "工号" << "\t" << "年龄" << "\t" << "邮编" << "\t" << "工资" << "\t" << "名字" << "\t" << "性别" << "\t" << "部门" << endl;
mysql_res = mysql_store_result(mysql);
//获取字段个数
int col = mysql_num_fields(mysql_res);
//cout << col << endl;


//获取返回结果的行数
int row = mysql_num_rows(mysql_res);

while((mysql_row = mysql_fetch_row(mysql_res)))
{
for (int i = 0; i < col ;i++)
{
cout << ( mysql_row[i] ? mysql_row[i] : "NULL") <<"\t";
}
cout << endl;
}


}


}


void visitworker()//职工信息显示
{


char *sel_str = "select * from work.workers";
char str[1024];
sprintf(str, sel_str);


int ret = mysql_query(mysql, str);
if (ret != 0)
{
cout << "error :" << endl;
}
else
{


MYSQL_RES *mysql_res;
MYSQL_ROW mysql_row;


cout << "工号" << "\t" << "年龄" << "\t" << "邮编" << "\t" << "工资" << "\t" << "名字" << "\t" << "性别" << "\t" << "部门" << endl;
mysql_res = mysql_store_result(mysql);
//获取字段个数
int col = mysql_num_fields(mysql_res);
//cout << col << endl;


//获取返回结果的行数
int row = mysql_num_rows(mysql_res);


while((mysql_row = mysql_fetch_row(mysql_res)))
{
for (int i = 0; i < col ;i++)
{
cout << ( mysql_row[i] ? mysql_row[i] : "NULL") <<"\t";
}
cout << endl;
}
}
}




void menu() //菜单
{
cout<<endl;


cout<<endl;


cout<<"***********************************************"<<endl;


cout<<"** **"<<endl;


cout<<"** 职工信息管理系统主菜单 **"<<endl;


cout<<"** **"<<endl;


cout<<"** 1.职工信息添加 **"<<endl;


cout<<"** 2.职工信息修改 **"<<endl;


cout<<"** 3.职工信息删除 **"<<endl;


cout<<"** 4.职工薪资排序 **"<<endl;


cout<<"** 5.职工信息查找 **"<<endl;


cout<<"** 6.职工信息显示 **"<<endl;


cout<<"** 0.退出 **"<<endl;


cout<<"** **"<<endl;


cout<<"***********************************************"<<endl;


cout<<endl;
}