linux下C连接mysql

insert.c

#include<stdio.h>
#include "/usr/include/mysql/mysql.h"//locate mysql.h查找路径
int main()
{
      MYSQL conn;
      int res;
      mysql_init(&conn);
      if(mysql_real_connect(&conn,"localhost","root","111111","sqlinkdb",0,NULL,CLIENT_FOUND_ROWS))
      {
                 printf("connection success\n");
                 res=mysql_query(&conn,"insert into student values('1010')");
                //res=mysql_query(&conn,"update student set num=num+1");
                //res=mysql_query(&conn,"delete from student where num=1003");
                 if(res)
                 {
                         printf("connection failed\n");
                         mysql_close(&conn);
                 }
                else
                {
                        printf("connection success\n");
                       mysql_close(&conn);
                  }
       }
 return 0;
}
编译：gcc -o insert insert.c `mysql_config --cflags --libs`
      ./insert

search.c

#include<stdio.h>
#include<stdlib.h>
#include "/usr/include/mysql/mysql.h"
void query_sql(char* sql)
{
          MYSQL conn;
          int res;
          MYSQL_RES *res_ptr;
          MYSQL_FIELD *field;
          MYSQL_ROW result_row;
          int row,column;
          int i,j;
          mysql_init(&conn);
          if(mysql_real_connect(&conn,"localhost","root","111111","sqlinkdb",0,NULL,0))
          {
                    printf("数据库连接成功！\n");
                    mysql_query(&conn,"set names utf8");
                    res=mysql_query(&conn,sql);
                    if(res)
                    {
                             printf("mysql_query failed\n");
                    }
                    else
                    {
                            res_ptr=mysql_store_result(&conn);
                            if(res_ptr)
                            {
                                      column=mysql_num_fields(res_ptr);
                                      row=mysql_num_rows(res_ptr)+1;
                                      printf("查询到%lu行\n",row);
                                      for(i=0;field=mysql_fetch_field(res_ptr);i++)
                                     {
                                                    printf("%s\t",field->name);
                                     }
                                     printf("\n");
                                     for(i=1;i<row;i++)
                                     {
                                                  result_row=mysql_fetch_row(res_ptr);
                                                  for(j=0;j<column;j++)
                                                  {
                                                             printf("%s\t",result_row[j]);
                                                  }
                                                 printf("\n");
                                     }
                             }
               }
            mysql_close(&conn);
        }
}
int main()
{
             char *query="select * from student";
             query_sql(query);
             return 0;
}

编译：gcc -o search search.c `mysql_config --cflags --libs`
      ./insert

------------------------------------------------------------------------------------------
 

 

 

 

 

*************************************************************
总结：C连接mysql

***************************
1、首先得了解MYSQL

---------------
MYSQL
以下代码块是用来连接数据库的通讯过程

typedef struct st_mysql {
  NET           net;            /* Communication parameters */
  gptr          connector_fd;   /* ConnectorFd for SSL */
  char          *host,*user,*passwd,*unix_socket,
                *server_version,*host_info,*info,*db;
  unsigned int  port,client_flag,server_capabilities;
  unsigned int  protocol_version;
  unsigned int  field_count;
  unsigned int  server_status;
  unsigned long thread_id;      /* Id for connection in server */
  my_ulonglong affected_rows;
  my_ulonglong insert_id;       /* id if insert on table with NEXTNR */
  my_ulonglong extra_info;              /* Used by mysqlshow */
  unsigned long packet_length;
  enum mysql_status status;
  MYSQL_FIELD   *fields;
  MEM_ROOT      field_alloc;
  my_bool       free_me;        /* If free in mysql_close */
  my_bool       reconnect;      /* set to 1 if automatic reconnect */
  struct st_mysql_options options;
  char          scramble_buff[9];
  struct charset_info_st *charset;
  unsigned int  server_language;
} MYSQL;

-------------------
MYSQL_RES
这段代码返回查询结果的行。返回的数据称为“数据集”

typedef struct st_mysql_res {
  my_ulonglong row_count;
  unsigned int  field_count, current_field;
  MYSQL_FIELD   *fields;
  MYSQL_DATA    *data;
  MYSQL_ROWS    *data_cursor;
  MEM_ROOT      field_alloc;
  MYSQL_ROW     row;            /* If unbuffered read */
  MYSQL_ROW     current_row;    /* buffer to current row */
  unsigned long *lengths;       /* column lengths of current row */
  MYSQL         *handle;        /* for unbuffered reads */
  my_bool       eof;            /* Used my mysql_fetch_row */
} MYSQL_RES;

------------------
MYSQL_ROW
这个结构是数据行的一个安全表示法。你无法使用以空字符结束的串，因为数据在这个串可以是二进制, 也许没有包括任何字符。

typedef struct st_mysql_field {
  char *name;                   /* Name of column */
  char *table;                  /* Table of column if column was a field */
  char *def;                    /* Default value (set by mysql_list_fields) */
  enum enum_field_types type;   /* Type of field. Se mysql_com.h for types */
  unsigned int length;          /* Width of column */
  unsigned int max_length;      /* Max width of selected set */
  unsigned int flags;           /* Div flags */
  unsigned int decimals;        /* Number of decimals in field */
} MYSQL_FIELD;

 

**********************************
2、在mysql里用到的一些函数

mysql_init() 获得或初始化一个MYSQL结构。

mysql_real_connect() 连接一个MySQL服务器。

mysql_close() 关闭一个服务器连接。

mysql_query() 执行指定为一个空结尾的字符串的SQL查询。

mysql_store_result() 检索一个完整的结果集合给客户。

mysql_num_fields() 返回一个结果集合重的列的数量。

mysql_num_rows() 返回一个结果集合中的行的数量。

mysql_fetch_row() 从结果集合中取得下一行。

mysql_affected_rows() 返回被最新的UPDATE, DELETE或INSERT查询影响的行数。

 

********************************
3、C语言对单个表的操作
   对于插入、删除、更新都是同一类型的，是直接在mysql里面的表里操作
   模式：
 MYSQL  conn;//mysql连接
 int res;
 mysql_init(&conn);//初始化mysql连接conn
 //连接成功
 if(mysql_real_connect(&conn,"localhost","root","111111","sqlinkdb",0,NULL,0))
 {
  //database成功
    ———————————————————
  res=  mysql_query(&conn)                                 |
  if(res)                                                                      |
  {                                                                                |
   //table成功                                                             |
  }                                                                               |
  else                                                                        |
  {                                                                               |-->若删除...等可无，查询输出各个数        |   据，具体可由循环，如下。
   //table失败                                                             |
  }                                                                               |
  .                                                                               |
  .                                                                               |
  .                                                                               |
  . _________________________________|
  mysql_close(&conn);
 }
 else
 {
         printf("failed\n");
         mysql_close(&conn);
 }

   查询输出mysql的数据
 
 lie=mysql_num_fields(str);//在上面函数部分有解释
 hang=mysql_num_rows(str);
 
 for(i=0;field=mysql_fetch_field(str);i++)
 {
        printf("%s\t",field->name);
 }
 printf("\n");
 for(i=0;i<hang;i++)
 {
        row=mysql_fetch_row(str);
        for(j=0;j<lie;j++)
  {
         printf("%s\t",row[j]);
   }
  printf("\n");
 }

 

----------------------------
实例：

#include<stdio.h>
#include<stdlib.h>
#include "/usr/include/mysql/mysql.h"
void indeup_mysql(char *sql);
void select_mysql(char *sql);
int main()
{
             char *s1="insert into student values('1001')";
             indeup_mysql(s1);
             char *s2="delete from student where num=1017";
             indeup_mysql(s2);
             char *s3="update student set num=num+1";
             indeup_mysql(s3);
             char *s4="select * from student";
             select_mysql(s4);
             return 0;
}
void indeup_mysql(char *sql)
{
             MYSQL conn;
             int m;
             mysql_init(&conn);
             if(mysql_real_connect(&conn,"localhost","root","111111","sqlinkdb",0,NULL,0))
             {
                              printf("database success\n");
                              m=mysql_query(&conn,sql);
                              if(m)
                              {
                                         printf("table failed\n");
                                          mysql_close(&conn);
                               }
                              else
                               {
                                            printf("table success\n");
                                            mysql_close(&conn);
                               }
              }
            else
            {
                          printf("database failed\n");
                          mysql_close(&conn);
             }
}
void select_mysql(char *sql)
{
             MYSQL conn;
             int n,R,C,i,j;
             MYSQL_RES *chaxun;
             MYSQL_FIELD *field;
             MYSQL_ROW row;
             mysql_init(&conn);
             if(mysql_real_connect(&conn,"localhost","root","111111","sqlinkdb",0,NULL,0))
             {
                           printf("database success\n");
                           n=mysql_query(&conn,sql);
                           if(n)
                           {
                                       printf("table failed\n");
                                       mysql_close(&conn);
                           }
                          else
                          {
                                      printf("table success\n");
                                      chaxun=mysql_store_result(&conn);
                                      if(chaxun)
                                      {
                                                    C=mysql_num_fields(chaxun);
                                                    R=mysql_num_rows(chaxun);
                                                    for(i=0;field=mysql_fetch_field(chaxun);i++)
                                                    {
                                                                    printf("%s\t",field->name);
                                                     }
                                                     printf("\n");
                                                    for(i=0;i<R;i++)
                                                    {
                                                                       row=mysql_fetch_row(chaxun);
                                                                       for(j=0;j<C;j++)
                                                                       {
                                                                                     printf("%s\t",row[j]);
                                                                       }
                                                                       printf("\n");
                                                      }
                                                    mysql_close(&conn);
                                       }
                                      else
                                      {
                                                      printf("NULL\n");
                                                     mysql_close(&conn);
                                       }
                      }
             }
           else
            {
                              printf("database failed\n");
                             mysql_close(&conn);
              }
}
   

*******************************
4、C对多表的各个操作
 
  其操作方法与对单表类似，但在对多表执行的时候的命令不同。
  在网上搜了很多，看的很多，起到作用的就是执行命令
 
 
#include<stdio.h>
#include<stdlib.h>
#include "/usr/include/mysql/mysql.h"
int main()
{
                 MYSQL conn;
                 int res,hang,lie,i,j;
                MYSQL_FIELD *field;
                MYSQL_RES *str;
                MYSQL_ROW row;
                mysql_init(&conn);
                if(mysql_real_connect(&conn,"localhost","root","111111","sqlinkdb",0,NULL,0))
                {
                                 printf("database success\n");
                                  //查询
                                 res=mysql_query(&conn,"select table3.num,name,table3.score from table2,table3 where table3.num=table2.num;");

 

                                  if(res)
                                  {
                                                printf("table failed\n");
                                                mysql_close(&conn);
                                   }
                                 else
                                 {
                                               printf("connection success\n");
                                               str=mysql_store_result(&conn);
                                               if(str)
                                               {
                                                             lie=mysql_num_fields(str);
                                                             hang=mysql_num_rows(str);
                                                             for(i=0;field=mysql_fetch_field(str);i++)
                                                             {
                                                                                printf("%s\t",field->name);
                                                             }
                                                             printf("\n");
                                                             for(i=0;i<hang;i++)
                                                             {
                                                                              row=mysql_fetch_row(str);
                                                                              for(j=0;j<lie;j++)
                                                                              {
                                                                                            printf("%s\t",row[j]);
                                                                              }
                                                                              printf("\n");
                                                              }
                                               }
                                              else
                                               {
                                                         printf("failed\n");
                                               }
                                               mysql_close(&conn);
                                    }
                 }
                 else
                  {
                                  printf("database failed\n");
                                  mysql_close(&conn);
                    }
                  return 0;
}

[root@nana mysqlink]# ./mian
database success
connection success
num     name    score
1003    rr      90
1004    tt      78

 

 

//删除
res=mysql_query(&conn,"delete table1 from table1,table2 where table1.num=table2.num");
//修改、更新
res=mysql_query(&conn,"update table2,table3 set table3.score=table2.score where table2.num=table3.num;");
//查询
res=mysql_query(&conn,"select table3.num,name,table3.score from table2,table3 where table3.num=table2.num;");

 

******************************
需要注意的地方：

①、查找mysql.h的路径，头文件要正确
    用locate mysql.h查找

②、column 'XXXX' in field list is ambiguous
原因：表中包含共同字段'xxxx'

如：select name from a,b where a.id=b.id    因为a、b2表里都有name字段

解决：字段前加上具体表名，如： select a.name from a,b where a.id=b.id

③、编译时要注意gcc -o insert insert.c `mysql_config --cflags --libs`
    这里不是单引号！！！

 

 

 

 

 

但是效率太低啊！！！！

 

 

 

 

 

修改后的