Mysql —— C语言链接mysql数据库，命令行形式(getopt()函数)，用户、用户组增删改查（用户组表内有用户控制的策略字段）
生活随笔
收集整理的這篇文章主要介紹了
Mysql —— C语言链接mysql数据库，命令行形式(getopt()函数)，用户、用户组增删改查（用户组表内有用户控制的策略字段）
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
函數說明——getopt():
函數說明 getopt()用來分析命令行參數。參數argc和argv分別代表參數個數和內容，跟main（）函數的命令行參數是一樣的。
數據庫內定義的表，以及每個表的字段：
表內字段的截圖：
命令行操作樣式-h打印信息：
[root@localhost workwork]# ./userGroupUser users -hUsage: ------------------------------------------S show , show user information!-A add , add user information!-D delete , delete user information!-E edit , edit user information!-h help , display this help note!-n name , user`s name!-p password , user`s password!-a authentication , authentication type(0:pass 1:KEY 2:third)!-g(+) group , the group to which the user belongs to!-e(+) enable , usergroups`s enable type(0:enable 1:disable)!-o(+) policy , usergroups`s policy type!-r(+) remark , remark!可以進行的操作：
顯示操作： -S
//-S顯示名為xxx用戶具體信息（所在組、改組定義了什么策略） 提示必須加-n參數 ./userGroupUser users -S //顯示用戶表 用戶組表 所有信息 ./userGroupUser users -S -n root //顯示用戶表內 name=root的用戶的信息//可以顯示該用戶對應用戶組內 所在的組 定義的策略信息[root@localhost workwork]# ./userGroupUser users -S
[root@localhost workwork]# ./userGroupUser users -S -n root
新增操作： -A
/* 全：./userGroupUser users -A s -n test3 -p test3 -a 2 -groot -rasdc*/ /**添加用戶 (-g(+) group ; -r(+) remark)**/ //新增用戶的時候必須有-n|-p|-a選項 name password authentication //一次性輸入所有選項也是錯誤的 需要為每個參數賦值// printf("ADD -- Please enter necessary parameters -n|-p|-a,and assign a value to each parameter!\n"); //-g的參數必須緊跟 否則改用戶會默認加入root組； //-r的參數必須緊跟 否則判定為輸入為空 //當-g選項沒有加參數時候 默認該用戶屬于root組； //-g后的參數不存在則加入失敗，提示該用戶組不存在 ./userGroupUser users -A -n ddf -p aa -a 1//添加用戶 name=ddf authentication=1 默認加到用戶組root//insert into users values(7,'ddf','aa','2018-7-2 16:23:39',1,1,' ','KEY認證需要的文件',''); ./userGroupUser users -A -n dds -a 1 -p sd -rkj -gaa //添加用戶 name=dds authentication=1 group=aa //insert into users values(6,'dds','sd','2018-7-2 16:20:33',1,1,' ','KEY認證需要的文件','kj');//-g后的參數 用戶組名不存在不允許加入users usergroups表 /**添加用戶組 (-e(+) enable; -o(+) policy; -r(+) remark)**/ /*全： ./userGroupUser usergroups -A -n dd -e1 -oWEB,HTTP -rddWH*/ //新增用戶時候有必須參數-n選項 name //只有-n參數時候 策略狀態( enable_type_)為0 默認策略( policy_type_)為所有策略資源 //其他參數 -e(+) 策略狀態enable; -o(+)策略類型policy; -r(+) 標記內容remark ./userGroupUser usergroups -A //提示 Please enter other parameters when you want to add someone!! ./userGroupUser usergroups -A -n aa //用戶組表中添加name=aa policy默認如下 remark默認為空 的用戶組信息//insert into usergroups values(2,'aa','2018-7-2 16:14:19',0,'GET,HEAD,POST,PUT,DELETE,OPTIONS','') ./userGroupUser usergroups -A -n aa -oGET -rasd //向用戶組中添加 name=aa policy=GET remark=asd的用戶//insert into usergroups values(2,'aa','2018-7-2 16:14:46',0,'GET','asd');# ./userGroupUser usergroups -A? -n dd -e1 -oWEB,HTTP? -rusername-name-dd
# ./userGroupUser users -A s -n test3 -p test3 -a 2 -gdd -ras
修改操作： -E
/**修改用戶**/ /*全：./userGroupUser users -E -n test33 -p test33 -a 1 -gaa -rtest3totest33 where name test3 password test3*/ //修改用戶操作必須有where name password 字符；且where后個數需為偶數； name password參數需參數值 //判斷要進行改的用戶是不是管理員用戶，禁止對管理員用戶進行任何操作 //參數 ：-n newname；-p newpassword;-a authentication; -ggroup; -rremark; ./userGroupUser users -E -n test22 where name test2 password test2 //把名為test2密碼為test2的用戶名改為test22 /**修改用戶組**/ /*全：./userGroupUser usergroups -E -n aaa -e1 -oGET,HEAD -raatoaaa where name aa*/ //修改用戶組操作必須有where name字符；且where后個數需為偶數； name 參數需參數值 //判斷要進行改的用戶是不是管理員用戶，禁止對管理員用戶進行任何操作 //參數 ： -n newname；-eenabled ; -opolicy; -rremark; ./userGroupUser usergroups -E -n AA where name aa//把aa用戶名改為AA ./userGroupUser usergroups -E -n csa -e1 -oAAA,WEB,HTTP -rAAA where name aaa//修改 用戶組name為aaa為 name=csa enable=1 policy=AAA,WEB,HTTP remark=AAA# ./userGroupUser users -E -n test34 -p test33 -a 0 -groot -rtest3totest34? where name test3 password test3
# ./userGroupUser usergroups -E -n ddd -e0 -oGET,HEAD -rddtoddd? where name dd
刪除操作： -D
./userGroupUser users -D // 提示 Please enter other parameters when you want to add someone!! ./userGroupUser users -D -n root -p r //root用戶不允許刪除 //name或passwd 不正確不允許刪除 ./userGroupUser usergroups -D //提示 Please enter other parameters when you want to add someone!! ./userGroupUser usergroups -D -n as//判斷是否有用戶屬于該用戶組 若有用戶屬于該用戶組 不允許刪除該用戶組//root 用戶組不允許刪除# ./userGroupUser users -D -n test34 -p test33
# ./userGroupUser usergroups -D -n ddd
所有的命令：
代碼下載鏈接：https://download.csdn.net/download/weixin_42167759/10523075
/********************************************* 編譯命令：gcc userGroupUser.c -lmysqlclient -o userGroupUser* 執行命令：./db* ******************************************/ #include <stdio.h> #include <stdlib.h> #include <string.h> #include <time.h> #include <unistd.h> #include "mysql/mysql.h"MYSQL *g_conn;//mysql 鏈接 MYSQL_RES *g_res;//mysql 記錄集 MYSQL_ROW g_row;//字符串數組，mysql 記錄行const char *g_host_name = "localhost"; const char *g_user_name = "root"; const char *g_password = "asdfgh"; const char *g_db_name = "test"; const unsigned int g_db_port = 3306;#define MAX_BUF_SIZE 1024 //緩沖區最大字節數 #define ALL_POLICY_RESOURCES "GET,HEAD,POST,PUT,DELETE,OPTIONS" //所有策略資源 #define IF_AND_ENDIF 0 //測試代碼的注釋 char sql[MAX_BUF_SIZE]; char Time[MAX_BUF_SIZE]; int auty; int polistate = 0; //用戶組策略 默認狀態是開啟的int iNum_rows = 0;//mysql語句執行結果返回行數賦初值 int flag = 0;//管理員權限開關 int i = 1;//系統運行開關 //登錄使用的結構體 struct Login {char name[24];char password[20]; }login; //用戶組對應策略的開關 （0:enable 1:disabled） enum poliState{poliEnable,poliDisable}; //認證的方式 口令認證、KEY認證、第三方認證 enum authType{Passauth=0,Keyauth=1,Thpaauth=2}; //增刪改查的操作控制 enum {MODE_SHOW = 1, //showMODE_ADD, //addMODE_DELETE, //deleteMODE_EDIT, //deitMODE_HELP = 100, //helpPARAMETER_ERROR = 200 }; //操作使用的結構體 struct Operation { char tables[24]; char name[24]; char passwd[20]; char role[24]; char remark[20]; char group[255]; char authtype[255]; //可以根據變量的類型 做認證操作char passauth[255];char keyauth[255];char policy[255]; //輸入的策略char oldname[255];//修改操作中用于校驗的用戶名char oldpass[255];//修改操作中用于校驗的密碼 }ope; /***************************************************** time : 20180622* addby : swj* function :print_mysql_error() 打印錯誤信息* ******************************************************/ void print_mysql_error(const char *msg) {if(msg)printf("%s: %s\n",msg,mysql_error(g_conn));elseputs(mysql_error(g_conn)); } /***************************************************** time : 20180622* addby : swj* function :executesql() 執行sql語句，成功返回0，失敗返回-1 ******************************************************/ int executesql(const char * sql) {if(mysql_real_query(g_conn,sql,strlen(sql)))return -1;return 0; } /***************************************************** time : 20180622* addby : swj* function :init_mysql() 初始化鏈接******************************************************/ int init_mysql() {//init the database connection g_conn = mysql_init(NULL);//connection the database if(!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; //返回成功 }/***************************************************** time : 20180622* addby : swj* function :create_database() 選擇數據庫 沒有的時候 創建數據;有的時候 進去數據庫 * ******************************************************/ void create_database() {sprintf(sql,"use workDatable");if(executesql(sql) == -1){puts("create database");executesql("create database workDatable;");print_mysql_error(NULL);puts("choice database");executesql("use workDatable;");print_mysql_error(NULL);puts("!!!Initialize the success!!!");}else{executesql("use workDatable;");print_mysql_error(NULL);}} /***************************************************** time : 20180622* addby : swj* function :create_table() 創建表 * ******************************************************/ void create_table() {sprintf(sql,"show tables;");executesql(sql);g_res = mysql_store_result(g_conn);// printf("g_res = %d\n",g_res);iNum_rows = mysql_num_rows(g_res);// printf("iNum_rows = %d\n",iNum_rows);if(iNum_rows == 0){puts("create users table");executesql("create table users(id_ int(11) unsigned primary key auto_increment,name_ char(255) not null unique,password_ char(32) not null,create_time_ datetime,creator_id_ int(11) unsigned,auth_type_ int(11) not null,dyn_sn_ char(10),dyn_pass_sn_ text,remark_ varchar(200),foreign key(creator_id_) references users(id_));");puts("create usergroups table"); executesql("create table usergroups(id_ int(11) unsigned primary key auto_increment,name_ char(255) not null unique,create_time_ datetime,enable_type_ int(11) not null,policy_type_ char(255),remark_ varchar(200));"); puts("create userGroupUser table");executesql("create table userGroupUser(user_id_ int(11) unsigned,usergroup_id_ int(11) unsigned,primary key(user_id_,usergroup_id_),foreign key(user_id_) references users(id_),foreign key(usergroup_id_ ) references usergroups (id_));");}mysql_free_result(g_res);//釋放結果集 } /***************************************************** time : 20180622* addby : swj* function :init_administrator() 初始化管理員賬戶* 管理員用戶名：root 密碼：root* ******************************************************/ void init_administrator() {//查詢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");//插入管理員用戶//printf("Passauth = %d\n",Passauth);sprintf(sql,"insert into users values(1,'root','root','2017-08-18 12:21:11',1,0,'','','0:VIP 1:local pwd 2:local cert');"); //0:VIP 1:local pwd 2:local cert 4:2-fa/ cert+pw 5:2-fa / dyn + pw');executesql(sql);}mysql_free_result(g_res); //釋放結果集//查詢usergroups表sprintf(sql,"select * from usergroups 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 usergroups");//插入管理員所在用戶組sprintf(sql,"insert into usergroups values(1,'root','2017-06-26 12:21:11',0,'GHPPDOP','0:enable 1:disabled');"); executesql(sql);}mysql_free_result(g_res); //釋放結果集//查詢userGroupUser表sprintf(sql,"select * from userGroupUser where user_id_='1' and usergroup_id_='1';");executesql(sql);g_res = mysql_store_result(g_conn);iNum_rows = mysql_num_rows(g_res);if(iNum_rows == 0){puts("Init userGroupUser");//插入管理員用戶所在組sprintf(sql,"insert into userGroupUser values(1,1);");executesql(sql);}mysql_free_result(g_res); // 釋放結果集 } /***************************************************** time : 20180629* addby : swj* function :displayAll() 顯示所有表的信息 * ******************************************************/ void displayAll() {//查詢users表 sprintf(sql,"select * from users;"); executesql(sql); g_res = mysql_store_result(g_conn); // 從服務器傳送結果集至本地，mysql_use_result直接使用服務器上的記錄集 iNum_rows = mysql_num_rows(g_res); // 得到記錄的行數 int iNum_fields = mysql_num_fields(g_res); // 得到記錄的列數 printf("☆ ★ ☆ ★ ☆ ★ ☆ ★ ☆ ★ ☆ ★ ☆ ★ ☆ ★ ★ ☆ ★ ☆ ★ ☆ ★ ★ ☆ ★ ☆ ★ ☆ ★ ☆ ★ ☆ ★ ☆ ★ ☆ ★ ☆ ★ ☆ ★ ☆ ★ ☆ ★ ☆ ★ ☆ ★ ★ ☆ ★ ☆ ★ ☆ ★ ☆"); printf("\n\t\033[31m+-----+-------+------------------------+----------------------+--------------+---------------------+\033[0m"); printf("\n\t\033[41;32m| ---users table--- |\033[0m");printf("\n\t\033[31m+-----+-------+------------------------+----------------------+--------------+---------------------+\033[0m");puts("\n\tid_ | name_ |password_| create_time_ |creator_id_ | auth_type_ |dyn_sn_| dyn_pass_sn_ |remark_ "); while((g_row=mysql_fetch_row(g_res))) // 打印結果集printf("\n\t%s\t%s\t%s\t%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],g_row[6],g_row[7],g_row[8]); //查詢usergroups表 sprintf(sql,"select * from usergroups;"); executesql(sql); g_res = mysql_store_result(g_conn); // 從服務器傳送結果集至本地，mysql_use_result直接使用服務器上的記錄集 iNum_rows = mysql_num_rows(g_res); // 得到記錄的行數 iNum_fields = mysql_num_fields(g_res); // 得到記錄的列數printf("\n\n ☆ ★ ☆ ★ ☆ ★ ☆ ★ ☆ ★ ☆ ★ ☆ ★ ☆ ★ ★ ☆ ★ ☆ ★ ☆ ★ ★ ☆ ★ ☆ ★ ☆ ★ ☆ ★ ☆ ★ ☆ ★ ☆ ★ ☆ ★ ★ ☆ ★ ☆ ★ ☆ ★ ☆ ★ ☆ ★ ☆ ★ "); printf("\n\t\033[31m+-----+-------+------------------------------------------+--------------+---------------------+\033[0m"); printf("\n\t\033[46;31m| ------------------------usergroups table------------------------ |\033[0m");printf("\n\t\033[31m+-----+-------+------------------------------------------+--------------+---------------------+\033[0m");puts("\n\tid_ | name_ | create_time_ | enable_type_ | policy_type_ |remark_ "); while((g_row=mysql_fetch_row(g_res))) // 打印結果集printf("\n\t%s\t%s\t%s\t%s\t\t%s\t\t%s\t\n",g_row[0],g_row[1],g_row[2],g_row[3],g_row[4],g_row[5]); mysql_free_result(g_res); } /***************************************************** time : 20180702* addby : swj* function :displayUser() 顯示所有表的信息 * ******************************************************/ void displayUser() {//根據用戶名 找用戶所屬于的組 找該用戶的策略//根據用戶名 在用戶表中找到用戶idsprintf(sql,"select id_ from users where name_='%s';",ope.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))){ #if IF_AND_ENDIFprintf("g_row[0] = %s\n",g_row[0]); #endif//根據用戶id找到 在用戶用戶組關系表中找到 用戶組id sprintf(sql,"select usergroup_id_ from userGroupUser where user_id_=%s;",g_row[0]);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))){ #if IF_AND_ENDIFprintf("g_row[0] = %s\n",g_row[0]); #endif//根據用戶組id找 在用戶組表中找到 用戶組規定的策略 用戶組名稱 #if IF_AND_ENDIF//sprintf(sql,"select policy_type_ from usergroups where id_=%s;",g_row[0]);//只找策略 #endifsprintf(sql,"select * from usergroups 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 IF_AND_ENDIF// printf("g_row[0] = %s\n",g_row[0]); //只找策略// sprintf(ope.policy,"%s",g_row[0]); //只找策略// printf("g_row[1]=%s g_row[4]=%s\n",g_row[1],g_row[4]); #endifsprintf(ope.policy,"%s",g_row[4]); sprintf(ope.group,"%s",g_row[1]); }}}//顯示某一用戶 或者用戶組信息 參數中必須加-n參數 iif(strlen(ope.name) == 0){printf("When you want to display sombody,please enter necessary parameters -n!\n");return;}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); // 得到記錄的行數 if(iNum_rows == 0)puts("\n\t\t\tNo such person!");else{if(strcmp(ope.tables,"users") == 0){int iNum_fields = mysql_num_fields(g_res); // 得到記錄的列數 puts("\n\n\t id_ | name_ |password_| create_time_ |creator_id_ | auth_type_ |dyn_sn_| dyn_pass_sn_ |remark_ | group_ |policy_type_ ");while((g_row=mysql_fetch_row(g_res))) // 打印結果集printf("\n\t%s\t%s\t%s\t%s\t%s\t%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],g_row[6],g_row[7],g_row[8],ope.group,ope.policy);}else{int iNum_fields = mysql_num_fields(g_res); // 得到記錄的列數 puts("\n\n\tid_ | name_ | create_time_ | enable_type_ | policy_type_ |remark_ ");while((g_row=mysql_fetch_row(g_res))) // 打印結果集printf("\n\t%s\t%s\t%s\t%s\t\t%s\t\t%s\t\n",g_row[0],g_row[1],g_row[2],g_row[3],g_row[4],g_row[5]);}}mysql_free_result(g_res); } /***************************************************** * time : 20180702* addby : swj* function :add_usergroup_msg() 添加用戶組信息函數* ******************************************************/ void add_usergroup_msg() {char NEWID[20];if((strlen(ope.name)==0)){printf("add_usergroup_msg -- Please enter necessary parameters -n,and assign a value to the parameter!\n");return;}//根據當前已有用戶的行數判斷，新建的用戶id應為Id_中最大值+1sprintf(sql,"select (@id_:=id_+1) as idnum_,usergroups.* from usergroups where id_ = (select max(id_) from usergroups);");executesql(sql);g_res = mysql_store_result(g_conn);iNum_rows = mysql_num_rows(g_res); // 得到記錄的行數 while((g_row = mysql_fetch_row(g_res))){sprintf(NEWID,"%s",g_row[0]);}//增加時候，若改用戶名存在（給出用戶存在的提示信息）sprintf(sql,"select id_ from usergroups where name_='%s';",ope.name);executesql(sql);g_res = mysql_store_result(g_conn);iNum_rows = mysql_num_rows(g_res);if(iNum_rows != 0){puts("\n\t\t\t!!!Username already exists !!!! ");return ;}//獲取系統時間，作為創建時間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);//輸入用戶組 策略的狀態 （0:enable 1:disabled） #if IF_AND_ENDIFprintf("polistate = %d\n",polistate); #endif//輸入策略 如果-o為空 未輸入具體 則添加所有策略if(strlen(ope.policy) == 0){sprintf(ope.policy, ALL_POLICY_RESOURCES);} #if IF_AND_ENDIF printf("ope.group = %s\n",ope.policy); #endifsprintf(sql,"insert into usergroups values(%s,'%s','%s',%d,'%s','%s');",NEWID,ope.name,Time,polistate,ope.policy,ope.remark); #if IF_AND_ENDIFprintf("%s\n",sql); #endifexecutesql(sql);printf("\n\t\t\tADD ------- SUCCESS!!!\n");mysql_free_result(g_res); } /***************************************************** time : 201800702* addby : swj* function :add_user_msg() 添加用戶信息函數* ******************************************************/ void add_user_msg(){char ID[20];char NEWID[20];if((strlen(ope.name)==0) || (strlen(ope.passwd)==0) || (strlen(ope.authtype)==0)){printf("add_user_msg -- Please enter necessary parameters -n|-p|-a,and assign a value to each parameter!\n");return;}//根據當前已有用戶的行數判斷，新建的用戶id應為Id_中最大值+1sprintf(sql,"select (@id_:=id_+1) as idnum_,users.* from users where id_ = (select max(id_) from users);");executesql(sql);g_res = mysql_store_result(g_conn);iNum_rows = mysql_num_rows(g_res); // 得到記錄的行數 while((g_row = mysql_fetch_row(g_res))){sprintf(NEWID,"%s",g_row[0]);}//增加時候，若改用戶名存在（給出用戶存在的提示信息）sprintf(sql,"select id_ from users where name_='%s';",ope.name);executesql(sql);g_res = mysql_store_result(g_conn);iNum_rows = mysql_num_rows(g_res);if(iNum_rows != 0){puts("\n\t\t\t!!!Username already exists !!!! ");return ;}//獲取系統時間，作為創建時間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);//通過root用戶名字獲取用戶id，作為創建者id使用 sprintf(sql,"select id_ from users where name_='root';");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]);}sprintf(sql,"insert into users values(%s,'%s','%s','%s',%s,%d,'%s','%s','%s');",NEWID,ope.name,ope.passwd,Time,ID,auty,ope.passauth,ope.keyauth,ope.remark); #if IF_AND_ENDIFprintf("%s\n",sql); #endifexecutesql(sql);//輸入用戶所屬的組 如果沒有-g選項 則默認屬于root用戶組if(strlen(ope.group) == 0){sprintf(ope.group, "root");}sprintf(sql,"select id_ from usergroups where name_='%s';",ope.group);executesql(sql);g_res = mysql_store_result(g_conn);iNum_rows = mysql_num_rows(g_res); #if IF_AND_ENDIFprintf("iNum_rows =%d\n",iNum_rows); #endif//用戶組組名不存在 -g后的參數不存在 不添加此用戶if(iNum_rows == 0){sprintf(sql,"delete from users where id_=%s;",NEWID);executesql(sql);printf("Assign the correct parameter value to -g(usergroup name not exit)!\n");return ;}iNum_fields = mysql_num_fields(g_res);while((g_row=mysql_fetch_row(g_res))){//將用戶id與用戶組id寫入 用戶與用戶組角色表sprintf(sql,"insert into userGroupUser values(%s,%s);",NEWID,g_row[0]);executesql(sql); #if IF_AND_ENDIFprintf("%s\n",sql); #endif}printf("\n\t\t\tADD ------- SUCCESS!!!\n");mysql_free_result(g_res); } /***************************************************** time : 201800702* addby : swj* function :del_user_msg() 添加用戶信息函數* ******************************************************/ void del_usergroup_msg() {char ID[20];if(strlen(ope.name)== 0){printf("del_usergroup_mag -- Please enter necessary parameters -n,and assign a value to the parameter!\n");return;}//判斷要進行刪改的用戶是不是管理員用戶，禁止對管理員用戶進行刪改操作if(strcmp(ope.name,"root") == 0){puts("\n\t\t\tAdministrator user deletion is prohibited");return ;}//通過用戶名查看用戶組表中是否有該用戶組sprintf(sql,"select id_ from usergroups where name_='%s';",ope.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);//將該用戶組id取出來備用while((g_row=mysql_fetch_row(g_res))){sprintf(ID,"%s",g_row[0]);}//沒有查到if(iNum_rows == 0){puts("\n\t\t\t!!!No such person!!!");puts("\n\t\t\t!!!Please check the name or password enterd!!!");return ; }//判斷是否有用戶屬于該用戶組 若有用戶屬于該用戶組 不允許刪除該用戶組sprintf(sql,"select user_id_ from userGroupUser where usergroup_id_=%s;",ID);executesql(sql);g_res = mysql_store_result(g_conn);iNum_rows = mysql_num_rows(g_res); // 得到記錄的行數if(iNum_rows != 0){puts("\n\t\t\tBe quoted!User belongs to that user group!");puts("\n\t\t\tIt`s can`t be delete !");return ;}else{sprintf(sql,"delete from usergroups where id_=%s;",ID);executesql(sql);}printf("\n\t\t\tDELETE ------- SUCCESS!!!\n");mysql_free_result(g_res); } /***************************************************** time : 201800702* addby : swj* function :del_user_msg() 添加用戶信息函數* ******************************************************/ void del_user_msg() {char ID[20];if((strlen(ope.name)==0) || (strlen(ope.passwd)==0)){printf("del_user_mag -- Please enter necessary parameters -n|-p,and assign a value to each parameter!\n");return;}//判斷要進行刪改的用戶是不是管理員用戶，禁止對管理員用戶進行刪改操作if(strcmp(ope.name,"root") == 0){puts("\n\t\t\tAdministrator user deletion is prohibited");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("\n\t\t\t!!!No such person!!!");puts("\n\t\t\t!!!Please check the name or password enterd!!!");return; }//需要先刪除用戶 用戶組表 關系當中的信息，才可刪除用戶表中的信息sprintf(sql,"delete from userGroupUser where user_id_=%s;",ID);executesql(sql);sprintf(sql,"delete from users where id_=%s;",ID);executesql(sql);printf("\n\t\t\tDELETE ------- SUCCESS!!!\n");mysql_free_result(g_res); } /***************************************************** time : 20180704* addby : swj* function :edi_usergroup_msg() 修改用戶組表中用戶的信息* ******************************************************/ edi_usergroup_msg() { #if IF_AND_ENDIFprintf("ope.tables=%s ope.oldname=%s\n",ope.tables,ope.oldname);printf("ope.name = %s\n",ope.name);printf("ope.policy = %s\n",ope.policy);printf("ope.remark = %s\n",ope.remark); #endifchar ID[20];//通過用戶名和密碼查看用戶組表中是否有該用戶 sprintf(sql,"select id_ from usergroups where name_='%s';",ope.oldname);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]);}//printf("ID = %s\n",ID);if(iNum_rows == 0){puts("\n\t\t\t!!!No such person!!!");puts("\n\t\t\t!!!Please check the name or password enterd!!!");return;}//判斷要進行改的用戶是不是管理員用戶，禁止對管理員用戶進行操作if(strcmp(ope.oldname,"root") == 0){puts("\n\t\t\tForbid action other password change for administrator user groups!\n");return;}//修改用戶組名稱if(strlen(ope.name) != 0){sprintf(sql,"update usergroups set name_='%s' where id_=%s",ope.name,ID);executesql(sql);} //修改策略狀態if(polistate == 0){sprintf(sql,"update usergroups set enable_type_=%d where id_=%s",polistate,ID);executesql(sql);}else{sprintf(sql,"update usergroups set enable_type_=%d where id_=%s",polistate,ID);executesql(sql);}//修改策略的內容if(strlen(ope.policy) != 0){sprintf(sql,"update usergroups set policy_type_='%s' where id_=%s",ope.policy,ID);executesql(sql);} //修改用戶組的備注信息if(strlen(ope.remark) != 0){sprintf(sql,"update usergroups set remark_='%s' where id_=%s",ope.remark,ID);executesql(sql);} printf("\n\t\t\tEDIT ------- SUCCESS!!!\n"); } /***************************************************** time : 20180703* addby : swj* function :edi_user_msg() 修改用戶表中用戶的信息* ******************************************************/ edi_user_msg() {char ID[20];//通過用戶名和密碼查看用戶表中是否有該用戶 sprintf(sql,"select id_ from users where name_='%s' and password_='%s';",ope.oldname,ope.oldpass);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 IF_AND_ENDIFprintf("ID = %s\n",ID); #endifif(iNum_rows == 0){puts("\n\t\t\t!!!No such person!!!");puts("\n\t\t\t!!!Please check the name or password enterd!!!");return;}//修改用戶密碼if(strlen(ope.passwd) != 0){sprintf(sql,"update users set password_='%s' where id_=%s;",ope.passwd,ID);executesql(sql);}//判斷要進行改的用戶是不是管理員用戶，禁止對管理員用戶進行除修改密碼以外操作if(strcmp(ope.oldname,"root") == 0){puts("\n\t\t\tForbid action other password change for administrator users!\n");return;}//修改用戶名if(strlen(ope.name) != 0){sprintf(sql,"update users set name_='%s' where id_=%s",ope.name,ID);executesql(sql);} //修改認證方式if(strlen(ope.authtype) != 0){if(strcmp(ope.authtype,"Passauth") == 0){auty = Passauth;sprintf(ope.authtype,"Passauth");sprintf(ope.passauth,"INSERT口令認證需要的口令");sprintf(ope.keyauth," ");}else if(strcmp(ope.authtype,"Keyauth") == 0){auty = Keyauth;sprintf(ope.authtype,"Keyauth");sprintf(ope.passauth," ");sprintf(ope.keyauth,"KEY認證需要的文件");}else if(strcmp(ope.authtype,"Thpaauth") == 0){auty = Thpaauth;sprintf(ope.authtype,"Thpaauth");sprintf(ope.passauth," ");sprintf(ope.keyauth," ");}else{printf("Error authentication type you choice!\n");}sprintf(sql,"update users set auth_type_=%d where id_=%s;",auty,ID);executesql(sql);sprintf(sql,"update users set dyn_sn_='%s' where id_=%s;",ope.passauth,ID);executesql(sql);sprintf(sql,"update users set dyn_pass_sn_='%s' where id_=%s;",ope.keyauth,ID);executesql(sql);} //修改用戶的備注信息if(strlen(ope.remark) != 0){sprintf(sql,"update users set remark_='%s' where id_=%s",ope.remark,ID);executesql(sql);} //修改用戶所屬組//先判斷要更改的用戶組的名字是否存在 存在則在usergroups表中取出改用戶組id 不存在 提示先添加改用戶組if(strlen(ope.group) != 0){sprintf(sql,"select id_ from usergroups where name_='%s';",ope.group);executesql(sql);g_res = mysql_store_result(g_conn);iNum_rows = mysql_num_rows(g_res); // 得到記錄的行數iNum_fields = mysql_num_fields(g_res);if(iNum_rows == 0){printf("Please add a user group named '%s' first!\n",ope.group);}//被修改用戶的id為之前定義的ID 更新用戶與用戶組關系表中ID對應的用戶組idwhile((g_row=mysql_fetch_row(g_res))){sprintf(sql,"update userGroupUser set usergroup_id_='%s' where user_id_=%s;",g_row[0],ID); #if IF_AND_ENDIFprintf("%s\n",sql); #endifexecutesql(sql); }}printf("\n\t\t\tEDIT ------- SUCCESS!!!\n"); } /***************************************************** time : 20180629* addby : swj* function :print_help() 顯示幫助菜單* ******************************************************/ void print_help() {//./filename operate [tablename] [option] printf("Usage:\n-----------------------------------------\n");printf("\t-S\tshow , show user information!\n");//-s所有表的信息 -suser_name 用戶策略的信息(需要有參數-n時候 才可以) -stable_name 所選表的信息printf("\t-A\tadd , add user information!\n");printf("\t-D\tdelete , delete user information!\n");printf("\t-E\tedit , edit user information!\n");printf("\t-h\thelp , display this help note!\n");printf("\t-n \tname , user`s name!\n");printf("\t-p\tpassword , user`s password!\n");//printf("\t-t\ttime , user`s create time(eg:2017-08-18 12:21:11)!\n");//printf("\t-c\tcreator , user`s creator id!\n");printf("\t-a\tauthentication , authentication type(0:pass 1:KEY 2:third)!\n");printf("\t-g(+)\tgroup , the group to which the user belongs to!\n");//printf("\t-s\tshibboleth , user`s login password!\n");//printf("\t-f \tfile , user`s login KEY certificate!\n");printf("\t-e(+)\tenable , usergroups`s enable type(0:enable 1:disable)!\n");printf("\t-o(+)\tpolicy , usergroups`s policy type!\n");printf("\t-r(+)\tremark , remark!\n"); } int main(int argc,char** argv) {int ch;int mode = 0;//mode 增刪改查 選項的定義int flag = 0;//flag 基本操作 是否有 附件條件的判斷int error = 0;//authentication 參數是否是0|1|2//puts("!!!The system is initializing!!!");//初始化鏈接 if(init_mysql())print_mysql_error(NULL);//當鏈接數據庫時候 有錯誤 會報錯//選擇數據庫 沒有的時候 創建數據庫 有的時候 進去數據庫create_database();//創建表create_table(); //初始化管理員賬戶init_administrator();if(argc < 2){printf("Invalid parameter number,please check your command......\n");return -1;}//printf("argv[1] = %s\n",argv[1]);//如果輸入的argv[1]不是users usergroups '-t' 提示參數的類型有誤if((strcmp(argv[1],"users") != 0) && (strcmp(argv[1],"usergroups") != 0) && (strcmp(argv[1],"-h") != 0)){printf("Invalid parameter type,please check your command......\n");return -1;}//要操作的表寫入 結構體sprintf(ope.tables,"%s",argv[1]);//opterr = 0;//-: 按照順序掃描命令行參數的個數遇到有效選項時會正常處理，而遇到Operands時卻是這樣處理的：返回1，optarg賦值為Operands的首地址；//-：還可以無效選項或者是丟失選項參數錯誤；while((ch=getopt(argc,argv,"-:hSADEn:p:a:r::g::e::o::")) != -1) {switch(ch){case 'h':mode = MODE_HELP;break;case 'S':mode = MODE_SHOW;break;case 'A':mode = MODE_ADD;break;case 'D':mode = MODE_DELETE;break;case 'E':mode = MODE_EDIT;break;case 'n':flag = 1;sprintf(ope.name,"%s",optarg); break;case 'p':flag = 1;sprintf(ope.passwd,"%s",optarg); break;case 'a':flag = 1;sprintf(ope.authtype,"%s",optarg); if(strcmp(optarg,"0") == 0){auty = Passauth;sprintf(ope.authtype,"Passauth");sprintf(ope.passauth,"INSERT口令認證需要的口令");sprintf(ope.keyauth," ");}else if(strcmp(optarg,"1") == 0){auty = Keyauth;sprintf(ope.authtype,"Keyauth");sprintf(ope.passauth," ");sprintf(ope.keyauth,"KEY認證需要的文件");}else if(strcmp(optarg,"2") == 0){auty = Thpaauth; sprintf(ope.authtype,"Thpaauth");sprintf(ope.passauth," ");sprintf(ope.keyauth," ");}else{error = 1;/*printf("Error type you choice!\n");*/}break;case 'r':flag = 1;if(optarg != NULL){ sprintf(ope.remark,"%s",optarg);}break;case 'g':flag = 1;if(optarg != NULL){sprintf(ope.group,"%s",optarg);}break;case 'e':flag = 1;if(optarg != NULL){if(strcmp(optarg,"0") == 0){polistate = poliEnable;}else if(strcmp(optarg,"1") == 0){polistate = poliDisable;}else{error = 2;/*printf("Only allowed to enter 0 or 1(0:enable 1:disable)!\n");*/}}break;case 'o':flag = 1;if(optarg != NULL){sprintf(ope.policy,"%s",optarg);}break;default://printf("Get unknow option(%c),operation abort...\n",ch);break; }if(ch == 63) //? 無法識別的選項的判斷? ? ? ? ? ? ? ? {printf("./a.out: invalid option -- '%c'\n\n",optopt);}if(ch == 58) //: 丟失選項參數的判斷? ? ? ? ? ? ? ?{printf("./a.out: option requires an argument -- '%c'\n",optopt);}//如果是help參數 直接跳出不需要檢測其他參數 if(mode == MODE_HELP)break;} #if IF_AND_ENDIFprintf("mode = %d\n",mode);printf("flag = %d\n",flag);printf("error = %d\n",error); #endif #if IF_AND_ENDIFprintf("switch(mode) ---- ope.table=%s ope.name=%s\n",ope.tables,ope.name); printf("switch(mode) ---- ope.passwd=%s ope.authtype=%s\n",ope.passwd,ope.authtype); printf("switch(mode) ---- ope.group=%s ope.remark=%s\n",ope.group,ope.remark); #endif//檢測參數是否正確(未定義？ 必須的參數未寫入)if(error == 1){printf("The paeameter '-a' Only allows to enter 0 or 1 or 2 after you know it(0:pass 1:KEY 2:third)!\n");return;}if(error == 2){printf("The paeameter '-e' Only allows to enter 0 or 1 after you know it(0:enable 1:disable)!\n");return;}switch(mode){ case MODE_HELP:print_help();break;case MODE_SHOW:if(flag == 1) {displayUser();}else{displayAll();}break;case MODE_ADD:if(flag == 1) {if(error == 1){printf("Error authentication type you choice!\n");break;}if(strcmp(ope.tables,"users") == 0){add_user_msg();}else{add_usergroup_msg();}}else{printf("Please enter other parameters when you want to add someone!! \n");}break;case MODE_DELETE:if(flag == 1){if(strcmp(ope.tables,"users") == 0){del_user_msg();}else{del_usergroup_msg();}}else{printf("Please enter other parameters when you want to add someone!! \n");}break;case MODE_EDIT:if(flag == 1){ #if IF_AND_ENDIFprintf("argc = %d\n",argc); #endif//i用于遍歷命令行字符串 j記錄'where'字符后有參數個數 m標記'where'后的參數內容int i = 0,j = 0,m = 0;int nv = 0,pv = 0;int whereflag = 0,nameflag = 0,passflag = 0;for(i=0;i<argc;i++){//printf("argv[%d] = %s\n",i,argv[i]);if(strcmp(argv[i],"where") == 0){whereflag = 1;m = i;j=argc-i-1;} #if IF_AND_ENDIFprintf("m = %d\n",m); #endifif((m > 0) && (m <= argc)) // 'name'字符在'where'字符之后{if(strcmp(argv[i],"name") == 0){nameflag = 1;nv = i; #if IF_AND_ENDIFprintf("nv = %d argv[nv+1]=%s\n",nv,argv[nv+1]); #endif//如果'name'字符后有值 則把nv值設置為1并把之前的用戶名name 賦值給ope.oldnameif(argv[nv+1] != NULL){sprintf(ope.oldname,"%s",argv[nv+1]);nv = 1;}}if(strcmp(argv[i],"password") == 0){passflag = 1;pv = i;if(argv[pv+1] != NULL){sprintf(ope.oldpass,"%s",argv[pv+1]);pv = 1;}}m++;}}//printf("whereflag = %d\n",whereflag);//判斷命令行內是否有'where'字符if(whereflag == 0){printf("Modification operations must have 'where' characters!\n");break;}//判斷 where 后 的參數個數為偶數個printf("j = %d\n",j);if((j>0) && (j % 2 == 0)){}else{printf("Incorrect number of parameters after 'where' character(Even number)!\n");break;}//判斷命令行內'where'字符后 是否有'name'字符//printf("nameflag = %d\n",nameflag);if(nameflag == 0){printf("The 'where' character must be followed by a 'name' character!\n");break;}//判斷'name'字符后是否有值if(nv != 1){printf("Must have a value after the 'name' parameter!\n");break;}if(strcmp(ope.tables,"users") == 0){//判斷命令行內'where'字符后 是否有'password'字符if(passflag == 0){printf("The 'where' character must be followed by a 'password' character!\n");break;}//判斷'password'字符后是否有值if(pv != 1){printf("Must have a value after the 'password' parameter!\n");break;} #if IF_AND_ENDIFprintf("ope.oldname = %s\n",ope.oldname);printf("ope.oldpass = %s\n",ope.oldpass);printf("ope.tables = %s ope.name = %s\n",ope.tables,ope.name); #endifedi_user_msg();}else{edi_usergroup_msg();}}else{printf("Please enter other parameters when you want to edit someone!! \n");} break;default:printf("Please enter other parameters(eg:-h|-S|-A|-D|-E)!!\n");break;}mysql_close(g_conn);return EXIT_SUCCESS; }總結
以上是生活随笔為你收集整理的Mysql —— C语言链接mysql数据库，命令行形式(getopt()函数)，用户、用户组增删改查（用户组表内有用户控制的策略字段）的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Mysql —— C语言链接mysql数
- 下一篇： 函数 —— 分析命令行参数 getopt