/******************************************************************** * 標題：C語言鏈接mysql數據庫，實現可以增刪改查的角色權限登錄系統 * 作者：尹肖飛 聯系方式： QQ 794401375 * 描述：本代碼可在安裝GCC編譯環境和mysql集成環境下直接編譯運行，根據不同使用者的mysql賬戶可修改代碼前面的內容以成功連接mysql數據庫 * 編譯命令：gcc mysqltest.c -lmysqlclient -o mysqltest ********************************************************************/#include <stdio.h> #include <stdlib.h> #include <string.h> #include <time.h> #include "mysql/mysql.h"MYSQL *g_conn;//mysql 鏈接 MYSQL_RES *g_res;//mysql 記錄集 MYSQL_ROW g_row;//字符串數組，mysql 記錄行#define MAX_BUF_SIZE 1024 //緩沖區最大字節數 /*=================================================================*/ /**/const char *g_host_name = "localhost"; /**/const char *g_user_name = "yinxiaofei"; /**/const char *g_password = "yinxiaofei"; /**/const char *g_db_name = "test"; /**/const unsigned int g_db_port = 3306; /*=================================================================*/ char sql[MAX_BUF_SIZE]; char Time[MAX_BUF_SIZE];int iNum_rows = 0;//mysql語句執行結果返回行數賦初值 int flag = 0;//管理員權限開關 int i = 1;//系統運行開關//登錄使用的結構體 struct Login {char name[24];char password[20]; }login; //操作使用的結構體 struct Operation {char tables[24];char name[24];char passwd[20];char role[24];char remark[20]; }ope; //打印錯誤信息 void print_mysql_error(const char *msg) {if(msg)printf("%s: %s\n",msg,mysql_error(g_conn));elseputs(mysql_error(g_conn)); } //執行sql語句，成功返回0，失敗返回-1 int executesql(const char * sql) {//if(mysql_real_query(g_conn,sql,strlen(sql)))return -1;return 0; } //初始化鏈接 int init_mysql() {//init the database connectiong_conn = mysql_init(NULL);//connection the databaseif(!mysql_real_connect(g_conn,g_host_name,g_user_name,g_password,g_db_name,g_db_port,NULL,0))return -1;//鏈接失敗//檢查是否可以使用if(executesql("set names utf8"))return -1;return 0;//返回成功 } //選擇數據庫，沒有的時候創建 void create_database() {sprintf(sql,"use project");if(executesql(sql) == -1){puts("create database");executesql("create database project;");print_mysql_error(NULL);puts("choice database");executesql("use project;");print_mysql_error(NULL);puts("!!!Initialize the success!!!");}else{executesql("use project;");print_mysql_error(NULL);} } //查看表格完整性 void create_table() {//users表的檢查與創建sprintf(sql,"show tables;");executesql(sql);g_res = mysql_store_result(g_conn);iNum_rows = mysql_num_rows(g_res);if(iNum_rows == 0){puts("create users table");executesql("create table users(id_ smallint unsigned primary key auto_increment,name_ varchar(24) not null unique,password_ char(20) not null,create_time_ datetime not null,creator_id_ smallint unsigned,remark_ varchar(200),foreign key(creator_id_) references users(id_));");puts("create roles table");executesql("create table roles(id_ smallint unsigned primary key auto_increment,name_ varchar(24) not null unique,remark_ varchar(200));");puts("create authoritys table");executesql("create table authoritys(id_ smallint unsigned primary key auto_increment,name_ varchar(24) not null unique,remark_ varchar(200));");puts("create roleAuthority table");executesql("create table roleAuthority(role_id_ smallint unsigned,authority_id_ smallint unsigned,primary key(role_id_,authority_id_),foreign key(role_id_ ) references roles(id_ ),foreign key(authority_id_) references authoritys(id_));");puts("create userRole table");executesql("create table userRole(user_id_ smallint unsigned,role_id_ smallint unsigned,primary key(user_id_,role_id_),foreign key(user_id_) references users(id_),foreign key(role_id_ ) references roles(id_));");}mysql_free_result(g_res); // 釋放結果集 } //初始化管理員賬戶 void init_Administrtor() {//查詢users表sprintf(sql,"select * from users where id_='1' and name_='root';");executesql(sql);g_res = mysql_store_result(g_conn);iNum_rows = mysql_num_rows(g_res);if(iNum_rows == 0){puts("Init Administrtor User");//插入管理員用戶sprintf(sql,"insert into users values(1,'root','root','2017-08-18 12:21:11',1,'administrtor');");executesql(sql); }mysql_free_result(g_res); // 釋放結果集//查詢roles表sprintf(sql,"select * from roles;");executesql(sql);g_res = mysql_store_result(g_conn);iNum_rows = mysql_num_rows(g_res);if(iNum_rows < 3){puts("Init System Role");//插入系統角色sprintf(sql,"insert into roles values(1,'ADMINISTRTOR','administrtor');");executesql(sql);sprintf(sql,"insert into roles values(2,'USER','user');");executesql(sql);sprintf(sql,"insert into roles values(3,'OTHER','other');");executesql(sql); }mysql_free_result(g_res); // 釋放結果集//查詢authoritys表sprintf(sql,"select * from authoritys;");executesql(sql);g_res = mysql_store_result(g_conn);iNum_rows = mysql_num_rows(g_res);if(iNum_rows < 3){puts("Init System Authority");//插入系統權限sprintf(sql,"insert into authoritys values(1,'QUERY','query');");executesql(sql);sprintf(sql,"insert into authoritys values(2,'ADD','addmsg');");executesql(sql);sprintf(sql,"insert into authoritys values(3,'ADD&QUERY','query and addmsg');");executesql(sql);sprintf(sql,"insert into authoritys values(4,'DEL&ALT','delete and alter');");executesql(sql);sprintf(sql,"insert into authoritys values(5,'QUERY&DEL&ALT','query and delete and alter');");executesql(sql);sprintf(sql,"insert into authoritys values(6,'ADD&DEL&ALT','addmsg and delete and alter');");executesql(sql);sprintf(sql,"insert into authoritys values(7,'QUERY&ADD&DEL&ALT','query and addmsg and delete and alter');");executesql(sql);}mysql_free_result(g_res); // 釋放結果集//查詢roleAuthority表sprintf(sql,"select * from roleAuthority;");executesql(sql);g_res = mysql_store_result(g_conn);iNum_rows = mysql_num_rows(g_res);if(iNum_rows < 3){puts("Init RoleAuthority");//插入角色權限sprintf(sql,"insert into roleAuthority values(1,7);");executesql(sql);sprintf(sql,"insert into roleAuthority values(2,3);");executesql(sql);sprintf(sql,"insert into roleAuthority values(3,1);");executesql(sql); }mysql_free_result(g_res); // 釋放結果集//查詢userRole表sprintf(sql,"select * from userRole where user_id_='1' and role_id_='1';");executesql(sql);g_res = mysql_store_result(g_conn);iNum_rows = mysql_num_rows(g_res);if(iNum_rows == 0){puts("Init User Role");//插入管理員用戶sprintf(sql,"insert into userRole values(1,1);");executesql(sql); }mysql_free_result(g_res); // 釋放結果集 } //用戶登錄 void user_login() {puts("Init success! Please press any key to continue");while(1){while ((getchar()) != '\n');system("clear");puts("!!!Login System!!!");//輸入賬戶和密碼printf("Name：");scanf("%s",login.name);printf("Passwd：");scanf("%s",login.password);//在數據庫中查詢，可查詢到信息即表明users表中有賬號信息，登錄成功sprintf(sql,"select * from users where name_='%s' and password_='%s';",login.name,login.password);executesql(sql);g_res = mysql_store_result(g_conn);iNum_rows = mysql_num_rows(g_res);if(iNum_rows != 0){puts("!!! Login Success !!! ");while ((getchar()) != '\n');getchar();break;}else{puts("!!!Login Failed!!! Check name or password!");while ((getchar()) != '\n');}}mysql_free_result(g_res); // 釋放結果集 } //查詢函數 void query_msg() {while(1){int i,j;int choice;system("clear");puts("!!! Query_msg !!! ");puts("!!! choice table !!! ");puts("!!! 1: users !!! ");puts("!!! 2: roles !!! ");puts("!!! 3: userRole !!! ");puts("!!! 0: Return !!! ");scanf("%d",&choice); switch(choice){case 1:sprintf(ope.tables,"users");system("clear");puts("!!! enter name !!! ");printf("Name：");scanf("%s",ope.name);//在指定表中查詢用戶名相關信息sprintf(sql,"select * from %s where name_='%s';",ope.tables,ope.name);executesql(sql);g_res = mysql_store_result(g_conn);iNum_rows = mysql_num_rows(g_res); // 得到記錄的行數system("clear");if(iNum_rows == 0)puts("No such person!");else{int iNum_fields = mysql_num_fields(g_res); // 得到記錄的列數puts("id_ | name_ |password_| create_time_ |creator_id_ | remark_ ");while((g_row=mysql_fetch_row(g_res)))printf("%s\t%s\t%s\t%s\t%s\t%s\n",g_row[0],g_row[1],g_row[2],g_row[3],g_row[4],g_row[5]);}mysql_free_result(g_res); while ((getchar()) != '\n');getchar();break;case 2:sprintf(ope.tables,"roles");system("clear");//查詢角色表sprintf(sql,"select * from %s;",ope.tables);executesql(sql);g_res = mysql_store_result(g_conn);iNum_rows = mysql_num_rows(g_res); // 得到記錄的行數system("clear");if(iNum_rows == 0)puts("No such person!");else{int iNum_fields = mysql_num_fields(g_res); // 得到記錄的列數puts(" id_ | name_ | remark_ ");while((g_row=mysql_fetch_row(g_res)))printf("%s\t%s\t\t%s\n",g_row[0],g_row[1],g_row[2]);}mysql_free_result(g_res); while ((getchar()) != '\n');getchar();break;case 3:sprintf(ope.tables,"userRole");system("clear");//查詢用戶角色表sprintf(sql,"select * from %s;",ope.tables);executesql(sql);g_res = mysql_store_result(g_conn);iNum_rows = mysql_num_rows(g_res); // 得到記錄的行數system("clear");if(iNum_rows == 0)puts("No such person!");else{int iNum_fields = mysql_num_fields(g_res); // 得到記錄的列數puts(" user_id_ | role_id_ ");while((g_row=mysql_fetch_row(g_res)))printf("\t%s\t%s\n",g_row[0],g_row[1]);}mysql_free_result(g_res); while ((getchar()) != '\n');getchar();break;case 0:return ;default :puts("!!! enter right choice !!! ");while ((getchar()) != '\n');getchar();break;}} } //權限判定函數 int judge(int authority) {int auth = 0;//通過當前登錄的用戶名字獲取用戶idsprintf(sql,"select id_ from users where name_='%s';",login.name);executesql(sql);g_res = mysql_store_result(g_conn);iNum_rows = mysql_num_rows(g_res);int iNum_fields = mysql_num_fields(g_res);while((g_row=mysql_fetch_row(g_res))){//通過當前登錄用戶的id查詢這個用戶的角色idsprintf(sql,"select role_id_ from userRole where user_id_='%s';",g_row[0]);}executesql(sql);g_res = mysql_store_result(g_conn);iNum_rows = mysql_num_rows(g_res);iNum_fields = mysql_num_fields(g_res);while((g_row=mysql_fetch_row(g_res))){//通過當前用戶的角色id查詢該用戶的權限idsprintf(sql,"select authority_id_ from roleAuthority where role_id_='%s';",g_row[0]);}executesql(sql);g_res = mysql_store_result(g_conn);iNum_rows = mysql_num_rows(g_res);iNum_fields = mysql_num_fields(g_res);while((g_row=mysql_fetch_row(g_res))){//通過當前用戶的權限id查詢權限級別sprintf(sql,"select name_ from authoritys where id_='%s';",g_row[0]);}executesql(sql);g_res = mysql_store_result(g_conn);iNum_rows = mysql_num_rows(g_res);iNum_fields = mysql_num_fields(g_res);while((g_row=mysql_fetch_row(g_res))){if(strcmp(g_row[0],"QUERY") == 0)auth = 1;if(strcmp(g_row[0],"ADD") == 0)auth = 2;if(strcmp(g_row[0],"ADD&QUERY") == 0)auth = 3;if(strcmp(g_row[0],"DEL&ALT") == 0)auth = 4;if(strcmp(g_row[0],"QUERY&DEL&ALT") == 0)auth = 5;if(strcmp(g_row[0],"ADD&DEL&ALT") == 0)auth = 6;if(strcmp(g_row[0],"QUERY&ADD&DEL&ALT") == 0)auth = 7;if(auth >= 4)flag = 1;//管理員權限開關//判斷該用戶權限級別能否執行將要進行的操作if(auth < authority){mysql_free_result(g_res); // 釋放結果集return 0;}else{mysql_free_result(g_res); // 釋放結果集return 1;}} } //添加函數 void add_msg() {int o;char ID[20];//通過權限判定函數的返回值決定是否可以進行添加用戶操作if(judge(2) == 0){puts("!!!Insufficient permissions!!! ");while ((getchar()) != '\n');getchar();//權限不夠，退出函數return ;}//可以執行system("clear");puts("!!! Add_user !!! ");//根據當前已有用戶的行數判斷，新建的用戶id應為行數+1sprintf(sql,"select id_ from users;");executesql(sql);g_res = mysql_store_result(g_conn);iNum_rows = mysql_num_rows(g_res); // 得到記錄的行數int i = iNum_rows + 1;//新用戶id//輸入賬戶和密碼printf(" Name：");scanf("%s",ope.name);printf("Password：");scanf("%s",ope.passwd);//獲取系統時間，作為創建時間time_t temp;struct tm *t;time(&temp);t = localtime(&temp);sprintf(Time,"%d-%d-%d %d:%d:%d",t->tm_year+1900,t->tm_mon+1,t->tm_mday,t->tm_hour,t->tm_min,t->tm_sec);//通過當前登錄的用戶名字獲取用戶id，作為創建者id使用 sprintf(sql,"select id_ from users where name_='%s';",login.name);executesql(sql);g_res = mysql_store_result(g_conn);iNum_rows = mysql_num_rows(g_res);int iNum_fields = mysql_num_fields(g_res);while((g_row=mysql_fetch_row(g_res))){sprintf(ID,"%s",g_row[0]);}//備注 printf(" Remark：");scanf("%s",ope.remark);//向用戶表中插入一個新的用戶的信息sprintf(sql,"insert into users values(%d,'%s','%s','%s',%s,'%s');",i,ope.name,ope.passwd,Time,ID,ope.remark);executesql(sql);//管理員用戶設定權限if(flag){while(1){system("clear");puts("!!! ROLE !!! ");puts("!!! 1: USER !!! ");puts("!!! 2:OTHER !!! ");printf("Choice the Role of user %s：",ope.name);scanf("%d",&o);switch(o){case 1:sprintf(ope.role,"USER");break;case 2:sprintf(ope.role,"OTHER");break;default :puts("!!! enter right choice !!! ");while ((getchar()) != '\n');getchar();}break;}//通過角色名找到角色idsprintf(sql,"select id_ from roles where name_='%s';",ope.role);executesql(sql);g_res = mysql_store_result(g_conn);iNum_rows = mysql_num_rows(g_res);iNum_fields = mysql_num_fields(g_res);while((g_row=mysql_fetch_row(g_res))){//將用戶id和角色id寫入用戶角色表sprintf(sql,"insert into userRole values(%d,%s);",i,g_row[0]);executesql(sql);} }//如果沒有管理員權限，默認添加的用戶角色為otherelse{sprintf(sql,"insert into userRole values(%d,3);",i);executesql(sql);}flag = 0;//管理員權限開關puts("!!! success !!! ");while ((getchar()) != '\n');getchar(); } //刪改函數 void del_alt_msg() {int o,op;char p;char ID[20];//通過權限判定函數的返回值決定是否可以進行刪改用戶操作if(judge(4) == 0){puts("!!!Insufficient permissions!!! ");while ((getchar()) != '\n');getchar();//權限不夠，退出函數return ;}//可以執行system("clear");puts("!!! del_alt_msg !!! ");printf(" Name：");scanf("%s",ope.name);printf("Password：");scanf("%s",ope.passwd);//判斷要進行刪改的用戶是不是管理員用戶，禁止對管理員用戶進行刪改操作if(strcmp(ope.name,"root") == 0){puts("Administrator user deletion is prohibited");while ((getchar()) != '\n');getchar();return;}//通過用戶名和密碼查看用戶表中是否有該用戶sprintf(sql,"select id_ from users where name_='%s' and password_='%s';",ope.name,ope.passwd);executesql(sql);g_res = mysql_store_result(g_conn);iNum_rows = mysql_num_rows(g_res); // 得到記錄的行數int iNum_fields = mysql_num_fields(g_res);//將該用戶id取出來備用while((g_row=mysql_fetch_row(g_res))){sprintf(ID,"%s",g_row[0]);}//沒有查到if(iNum_rows == 0){puts("No such person!");puts("!!! enter right choice !!! ");while ((getchar()) != '\n');getchar();}//進入刪改選擇else{system("clear");puts("!!! del_alt_msg !!! ");puts("!!! 1:change name !!! ");puts("!!! 2:change passwd !!! ");puts("!!! 3:change role !!! ");puts("!!! 4:change remark !!! ");puts("!!! 5:delete user !!! ");printf("!!! choice： !");scanf("%d",&o);switch(o){case 1:system("clear");puts("!!! del_alt_msg !!! ");printf("!!! enter name: ");scanf("%s",ope.name);//更新用戶名sprintf(sql,"update users set name_='%s' where id_=%s;",ope.name,ID);executesql(sql);break; case 2:system("clear");puts("!!! del_alt_msg !!! ");printf("!!! enter password: ");scanf("%s",ope.passwd);//更新密碼sprintf(sql,"update users set password_='%s' where id_=%s;",ope.passwd,ID);executesql(sql);break; case 3:system("clear");puts("!!! del_alt_msg !!! ");puts("!!! 1.USER !!! ");puts("!!! 2.OTHER !!! ");printf("!!! choice： !");scanf("%d",&op);switch(op){case 1://設置角色為用戶sprintf(sql,"update userRole set role_id_=2 where user_id_=%s;",ID);executesql(sql);break;case 2://設置角色為其他人sprintf(sql,"update userRole set role_id_=3 where user_id_=%s;",ID);executesql(sql);break;default :puts("!!! enter right choice !!! ");while ((getchar()) != '\n');getchar();}break;case 4:system("clear");puts("!!! del_alt_msg !!! ");printf("!!! enter remark: ");scanf("%s",ope.remark);//更新備注sprintf(sql,"update users set remark_='%s' where id_=%s;",ope.remark,ID);executesql(sql);break;case 5:system("clear");puts("!!! del_alt_msg !!! ");printf("!!! sure delete? (Y/N):");scanf("%s",&p);switch(p){case 'Y':case 'y'://需要先刪除用戶角色表當中的信息，才可刪除用戶表中的信息sprintf(sql,"delete from userRole where user_id_=%s;",ID);executesql(sql);sprintf(sql,"delete from users where id_=%s;",ID);executesql(sql);break;case 'N':case 'n':return;}break;default :puts("!!! enter right choice !!! ");while ((getchar()) != '\n');getchar();}puts("!!! success !!! ");mysql_free_result(g_res);while ((getchar()) != '\n');getchar();} } //顯示所有用戶及用戶角色函數 void display() {//通過權限判定函數的返回值決定是否可以進行顯示所有用戶操作，other用戶不可使用此功能if(judge(2) == 0){puts("!!!Insufficient permissions!!! ");while ((getchar()) != '\n');getchar();//權限不夠，退出函數return ;}//可以執行//查詢users表sprintf(sql,"select * from users;");executesql(sql);g_res = mysql_store_result(g_conn);iNum_rows = mysql_num_rows(g_res); // 得到記錄的行數int iNum_fields = mysql_num_fields(g_res); // 得到記錄的列數system("clear");puts("!!! users table !!! \n");puts("id_ | name_ |password_| create_time_ |creator_id_ | remark_ ");while((g_row=mysql_fetch_row(g_res)))printf("%s\t%s\t%s\t%s\t%s\t%s\n",g_row[0],g_row[1],g_row[2],g_row[3],g_row[4],g_row[5]);//查詢roles表sprintf(sql,"select * from roles;");executesql(sql);g_res = mysql_store_result(g_conn);iNum_rows = mysql_num_rows(g_res); // 得到記錄的行數iNum_fields = mysql_num_fields(g_res); // 得到記錄的列數puts("\n\n!!! roles table !!! \n");puts(" id_ | name_ | remark_ ");while((g_row=mysql_fetch_row(g_res)))printf("%s\t%s\t\t%s\n",g_row[0],g_row[1],g_row[2]);//查詢userRole表sprintf(sql,"select * from userRole;");executesql(sql);g_res = mysql_store_result(g_conn);iNum_rows = mysql_num_rows(g_res); // 得到記錄的行數iNum_fields = mysql_num_fields(g_res); // 得到記錄的列數puts("\n\n!!! userRole table !!! \n");puts(" user_id_ | role_id_ ");while((g_row=mysql_fetch_row(g_res)))printf("\t%s\t%s\n",g_row[0],g_row[1]);mysql_free_result(g_res);while ((getchar()) != '\n');getchar(); } //操作菜單 void menu() {while(i){int choice;system("clear");puts("!!! choice： !!! ");puts("!!! 1:query msg !!! ");puts("!!! 2: add user !!! ");puts("!!! 3:del|alt msg !!! ");puts("!!! 4:display all !!! ");puts("!!! 5:exit login !!! ");puts("!!! 0:exit system !!! ");scanf("%d",&choice);switch(choice){case 1:query_msg();//無需判斷權限，所有角色均可使用此查詢break;case 2:add_msg();//添加用戶操作break;case 3:del_alt_msg();//刪改用戶操作break;case 4:display();//顯示所有用戶及用戶角色break;case 5://退出登錄flag = 0;//管理員權限開關return;case 0:puts("!!! thank you for using !!! ");//退出系統i = 0;break;default :puts("!!! enter right choice !!! ");while ((getchar()) != '\n');getchar();break;}} } //主函數 int main(void) {while(i){puts("!!!The system is initializing!!!");//初始化鏈接if(init_mysql())print_mysql_error(NULL);create_database();create_table();//初始化管理員賬戶init_Administrtor();//用戶登錄user_login();//操作菜單menu();}// 關閉鏈接mysql_close(g_conn); return EXIT_SUCCESS; }

存在的問題：

1、新增用戶時候id ，刪除后再增加會出錯，id有沖突； 2、刪除用戶時候，若該用戶創建過其他用戶（不能改此用戶名、不能改此用戶角色，不能刪除此用戶）； 3、（下一步加入）增加時候，若改用戶名存在（給出用戶存在的提示信息）；

解決辦法見下一篇文章：? ? ??

Mysql —— C語言鏈接mysql數據庫，用戶 角色 權限（用戶根據角色的不同擁有增刪改查的權限、用戶有三種認證方式）

總結

以上是生活随笔為你收集整理的Mysql —— C语言链接mysql数据库，实现可以增删改查的角色权限登录系统的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。