C语言操作MySQL数据库

MySQL C API:  官方英文版 中文参考手册

代码示例:

#include <stdio.h>  #include <stdlib.h>  #include <string.h>  #include <mysql/mysql.h>  #define HOST    "localhost"#define USER    "root"#define PASSWD  "HEpan693640."#define DB_NAME "TESTDB"void my_error(const char *err_string, int line);    // 错误处理函数void mysql_connect(MYSQL *mysql);                   // 连接mysql数据库void show_databases(MYSQL *mysql);                  // 显示所有数据库void change_database(MYSQL *mysql);                 // 改变数据库void drop_database(MYSQL *mysql);                   // 删除数据库void show_tables(MYSQL *mysql);                     // 显示当前数据库所有表void desc_table(MYSQL *mysql);                      // 显示表的字段信息void drop_table(MYSQL *mysql);                      // 删除表void print_table(MYSQL *mysql);                     // 显示表中内容void insert_data(MYSQL *mysql);                     // 插入数据void update_data(MYSQL *mysql);                     // 修改数据void delete_data(MYSQL *mysql);                     // 删除数据void close_connection(MYSQL *mysql);                // 关闭mysql数据库int main(int argc, char *argv[]){      int choice;      char *dir =         "\t1.Show databases\n\        2.Change database\n\        3.Drop database\n\        4.Show tables\n\        5.Desc table\n\        6.Drop table\n\        7.Print table\n\        8.Insert table\n\        9.Update table\n\        10.Delete data";    MYSQL *mysql = mysql_init(NULL);           // 创建一个MYSQL句柄并初始化    if (!mysql) {        my_error("mysql_init", __LINE__);    }    // 连接MYSQL数据库    mysql_connect(mysql);    while (1) {          printf("%s\n",dir);          printf("\nYour choice: ");          scanf("%d", &choice);          switch(choice) {          case 1:            show_databases(mysql);            break;          case 2:            change_database(mysql);            break;          case 3:            drop_database(mysql);            break;          case 4:            show_tables(mysql);            break;          case 5:            desc_table(mysql);            break;          case 6:            drop_table(mysql);            break;        case 7:            print_table(mysql);            break;        case 8:            insert_data(mysql);            break;        case 9:            update_data(mysql);            break;        case 10:            delete_data(mysql);            break;        default:            printf("No your choice!\n");            break;        }      }    close_connection(mysql);    return 0;  }  void my_error(const char *err_string, int line) {    fprintf(stderr, "line: %d ", line);    perror(err_string);    exit(1);}/* 连接mysql数据库 */  void mysql_connect(MYSQL *mysql){      if(!mysql_real_connect(mysql, HOST, USER, PASSWD, DB_NAME, 0, NULL, 0)) {          printf("Failed to connect:%s\n", mysql_error(mysql));          my_error("mysql_real_connect", __LINE__);    }      printf("Connect database sucessfully!\n\n");}  /* 显示所有数据库 */void show_databases(MYSQL *mysql)  {      int t;      char *query ="show databases";    MYSQL_RES *res;         // 返回查询结果    MYSQL_ROW row;          // 返回行数据    t = mysql_real_query(mysql, query, strlen(query));      if (t) {          printf("Failed to query: %s\n", mysql_error(mysql));        return;    }      res = mysql_store_result(mysql);    printf("\n");    while (row = mysql_fetch_row(res)) {        for(t = 0; t < mysql_num_fields(res); t++) {            printf("%s\t",row[t]);        }          printf("\n");    }    mysql_free_result(res);}/* 改变数据库 */  void change_database(MYSQL *mysql)  {      int t;      char *head = "use ";      char query[30];    char db_name[20];    MYSQL_RES *res;         // 返回查询结果    MYSQL_ROW row;          // 返回行数据    printf("Please enter the database to change: ");    scanf("%s", db_name);    sprintf(query, "%s%s", head, db_name);    t = mysql_real_query(mysql, query, strlen(query));    if (t) {          printf("Failed to query: %s\n", mysql_error(mysql));          return;    }      printf("\nChange sucessfully!\n\n");  }/* 删除数据库 */void drop_database(MYSQL *mysql){    int t;      char *head = "drop database ";      char query[50];    char db_name[20];    MYSQL_RES *res;         // 返回查询结果    MYSQL_ROW row;          // 返回行数据    printf("Please enter the database to drop: ");    scanf("%s", db_name);    sprintf(query, "%s%s", head, db_name);    t = mysql_real_query(mysql, query, strlen(query));    if (t) {          printf("Failed to query: %s\n", mysql_error(mysql));          return;    }      printf("\nDrop database sucessfully!\n\n");  }/* 打印当前数据库中的表 */void show_tables(MYSQL *mysql){      int t;      char *query = "show tables";      MYSQL_RES *res;         // 返回查询结果    MYSQL_ROW row;          // 返回行数据    t = mysql_real_query(mysql, query, strlen(query));      if (t) {          printf("Failed to query: %s\n", mysql_error(mysql));        return;    }     res = mysql_store_result(mysql);    printf("\n");    while (row = mysql_fetch_row(res)) {        for(t = 0; t < mysql_num_fields(res); t++) {            printf("%s\t", row[t]);        }        printf("\n");      }      mysql_free_result(res);}/* 打印表的字段 */  void desc_table(MYSQL *mysql){      int t;      char *head = "desc ";    char table_name[20];      char query[30];      MYSQL_RES *res;         // 返回查询结果    MYSQL_ROW row;          // 返回行数据    memset(table_name, '\0', sizeof(table_name));    memset(query, '\0', sizeof(query));    printf("Please enter the table name: ");      scanf("%s", table_name);      sprintf(query, "%s%s", head, table_name);    t = mysql_real_query(mysql, query, strlen(query));      if (t) {          printf("Failed to query: %s\n",mysql_error(mysql));          return;    }      res = mysql_store_result(mysql);    printf("\n");    while (row = mysql_fetch_row(res)) {        for(t = 0; t < mysql_num_fields(res); t++) {              printf("%s\t",row[t]);        }          printf("\n");      }     mysql_free_result(res);}  /* 打印表中数据 */void print_table(MYSQL *mysql)  {      int t;    char *head = "select * from ";    char table_name[20];    char query[50];    MYSQL_RES *res;         // 返回查询结果    MYSQL_ROW row;          // 返回行数据    memset(query, '\0', sizeof(query));    memset(table_name, '\0', sizeof(table_name));    printf("Please enter table name: ");      scanf("%s", table_name);    sprintf(query, "%s%s", head,table_name);    t = mysql_real_query(mysql, query, strlen(query));      if (t) {        printf("Failed to query: %s\n", mysql_error(mysql));          return;    }      printf("\nQuery successfully!\n\n");      res = mysql_store_result(mysql);    while (row = mysql_fetch_row(res)) {          for(t = 0; t < mysql_num_fields(res); t++) {              printf("%s\t", row[t]);          }          printf("\n");    }      mysql_free_result(res);}  /* 删除表 */void drop_table(MYSQL *mysql){    int t;      char *head = "drop table ";      char query[50];    char table_name[20];    MYSQL_RES *res;         // 返回查询结果    MYSQL_ROW row;          // 返回行数据    printf("Please enter the table to drop: ");    scanf("%s", table_name);    sprintf(query, "%s%s", head, table_name);    t = mysql_real_query(mysql, query, strlen(query));    if (t) {          printf("Failed to query: %s\n", mysql_error(mysql));          return;    }      printf("\nDrop table sucessfully!\n\n");  }/* 插入数据 */void insert_data(MYSQL *mysql){      int t;      char *head = "insert into ";      char query[200];    char table_name[20];      char field[50];    char *left = "(";      char *right = ") ";      char *values = "values";      char message[100];      MYSQL_RES *res;         // 返回查询结果    MYSQL_ROW row;          // 返回行数据    memset(field, '\0', sizeof(field));    memset(table_name, '\0', sizeof(table_name));    memset(message, '\0', sizeof(message));    printf("Please enter table_name: ");      scanf("%s", table_name);     printf("Please enter the Field you want to write(split with comma): ");    scanf("%s", field);      printf("Please write the message to the field(split with comma): ");    scanf("%s", message);      /* 把几个变量字符串连接成一个完整的mysql命令 */      sprintf(query, "%s%s%s%s%s%s%s%s%s", head, table_name, left,                             field, right, values, left, message, right);    printf("%s\n", query);      t = mysql_real_query(mysql, query, strlen(query));    if (t) {          printf("Failed to query: %s\n", mysql_error(mysql));          return;    }      printf("\nInsert sucessfully!\n\n");}  /* 删除数据 */void delete_data(MYSQL *mysql){      int t;    char *head = "delete from ";      char query[200];      char table_name[20];      char del_name[20];      MYSQL_RES *res;         // 返回查询结果    MYSQL_ROW row;          // 返回行数据    memset(table_name, '\0', sizeof(table_name));    memset(del_name, '\0', sizeof(del_name));    printf("Please enter the table: ");      scanf("%s",table_name);      printf("please enter del name: ");      scanf("%s",del_name);      sprintf(query, "%s%s where name =\"%s\"", head, table_name, del_name);    printf("%s\n", query);    t = mysql_real_query(mysql, query, strlen(query));    if (t) {          printf("Failed to query: %s\n", mysql_error(mysql));          return;    }    printf("Delete data sucessfully!\n");  }  /* 更新数据 */void update_data(MYSQL *mysql){      int t;    char *head = "update ";    char query[100];    char table_name[20];      char field_name[20];    char up_value[30];    MYSQL_RES *res;         // 返回查询结果    MYSQL_ROW row;          // 返回行数据    memset(table_name, '\0', sizeof(table_name));    memset(field_name, '\0', sizeof(field_name));    memset(up_value, '\0', sizeof(up_value));    printf("Please enter the table_name: ");      scanf("%s", table_name);      printf("please enter field_name: ");    scanf("%s", field_name);      printf("please enter up value: ");    scanf("%s", up_value);      sprintf(query, "%s%s set %s=\"%s\"", head, table_name, field_name, up_value);    printf("%s\n", query);    t = mysql_real_query(mysql, query, strlen(query));    if (t) {          printf("Failed to query: %s\n", mysql_error(mysql));          return;    }    printf("Update data sucessfully!\n");}void close_connection(MYSQL *mysql){    mysql_close(mysql);}

编译并运行:

gcc mysql.c -o mysql -g -L/usr/lib64/mysql -lmysqlclient./mysql