Linux C语言实现对MySQL数据库中表的内容进行增删查改操作

C API Data Structures

MYSQL

This structure represents the handler for one database connection. It is used for almost all MySQL functions. Do not try to make a copy of a MYSQL structure. There is no guarantee that such a copy will be usable.

MYSQL_RES

This structure represents the result of a query that returns rows (SELECT, SHOW, DESCRIBE, EXPLAIN). The information returned from a query is called the result set in the remainder of this section.

MYSQL_ROW

This is a type-safe representation of one row of data. It is currently implemented as an array of counted byte strings. (You cannot treat these as null-terminated strings if field values may contain binary data, because such values may contain null bytes internally.) Rows are obtained by calling mysql_fetch_row().

C API Function Overview

mysql_init():          Gets or initializes a MYSQL structure.
mysql_real_connect():  Connects to a MySQL server. 
mysql_query():         Executes an SQL query specified as a null-terminated string.
mysql_use_result():    Initiates a row-by-row result set retrieval.
mysql_fetch_row():     Fetches the next row from the result set
mysql_num_fields():    Returns the number of columns in a result set.
mysql_free_result():   Frees memory used by a result set.
mysql_store_result():  Retrieves a complete result set to the client.
mysql_num_rows():      Returns the number of rows in a result set.
mysql_close():         Closes a server connection
mysql_error():         Returns the error message for the most recently invoked MySQL function.
mysql_affected_rows(): Returns the number of rows changed/deleted/inserted by the last UPDATE, DELETE, or INSERT query.

query.c

#include <mysql/mysql.h>
#include <stdio.h>
#include <string.h>
int main(int argc,char* argv[])
{
    if(argc!=2)
    {
        printf("error args\n");
        return -1;
    }
    MYSQL *conn;     
    MYSQL_RES *res;   //保存查询结果的指针
    MYSQL_ROW row;  
    char* server="localhost";
    char* user="root";
    char* password="123";
    char* database="test";//要访问的数据库名称
    char query[300]="select * from person where name='";
    sprintf(query,"%s%s%s",query, argv[1],"'");  //SQL语句
    /* strcpy(query,"select * from Person"); */
    puts(query);     //检查SQL语句本身有没有语法问题
    unsigned int t,r;
    conn=mysql_init(NULL);    //获得或初始化一个MYSQL结构
    if(!mysql_real_connect(conn,server,user,password,database,0,NULL,0))  //连接一个MySQL服务器
    {
        printf("Error connecting to database:%s\n",mysql_error(conn));
        return -1;
    }
    else
    {
        printf("Connected...\n");
    }
    //将SQL语句通过conn传递给数据库
    t=mysql_query(conn,query);  //执行指定为一个空结尾的字符串的SQL查询
    if(t)  //t>0表示不成功
    {
        printf("Error making query:%s\n",mysql_error(conn));
    }
    else
    {

        //用mysql_num_rows可以得到查询的结果集有几行
        //要配合mysql_store_result使用
        //结果保存在res中
        //res = mysql_store_result(conn);   //检索一个完整的结果集合给客户
        //printf("mysql_num_rows=%ld\n",mysql_num_rows(res));  //返回一个结果集合中的行的数量
        
        //不保存，每次使用直接去数据库那边去取
        res=mysql_use_result(conn);  //初始化一个一行一行的结果集合的检索
        row = mysql_fetch_row(res);   //从结果集合中取得下一行
        if(row == NULL)
        {
            printf("Didn't find data\n");
        }
        else
        {
            do{	
                //printf("num=%d\n",mysql_num_fields(res));//列数
                for(t=0;t<mysql_num_fields(res);t++) //返回一个结果集合中的列的数量
                {
                    printf("%8s ",row[t]);  
                }
                printf("\n");
            }while((row=mysql_fetch_row(res))!=NULL);  //从结果集合中取得下一行
        }
        mysql_free_result(res);  //释放一个结果集合使用的内存
    }
    mysql_close(conn);  //关闭一个服务器连接
    return 0;
}

insert.c

#include <mysql/mysql.h>
#include <stdio.h>
#include <string.h>
int main(int argc,char* argv[])
{
    MYSQL *conn;
    MYSQL_RES *res;
    MYSQL_ROW row;
    char* server="localhost";
    char* user="root";
    char* password="123";
    char* database="test";
    char query[200]="insert into person(name,english,science) values('bluk',60,60)";
    int t,r;
    conn=mysql_init(NULL);
    if(!mysql_real_connect(conn,server,user,password,database,0,NULL,0))
    {
        printf("Error connecting to database:%s\n",mysql_error(conn));
    }
    else
    {
        printf("Connected...\n");
    }
    //将SQL语句通过conn传递给数据库
    //执行指定为一个空结尾的字符串的SQL查询
    t=mysql_query(conn,query);
    if(t)   //t>0表示不成功
    {
        printf("Error making query:%s\n",mysql_error(conn));
    }else{
        printf("insert success\n");
    }
    mysql_close(conn);    //关闭一个服务器连接
    return 0;
}

update.c

#include <mysql/mysql.h>
#include <stdio.h>
#include <string.h>
int main(int argc,char* argv[])
{
    if(argc!=2)
    {
        printf("error args\n");
        return -1;
    }
    MYSQL *conn;
    MYSQL_RES *res;
    MYSQL_ROW row;
    char* server="localhost";
    char* user="root";
    char* password="123";
    char* database="test";
    char query[200]="update Person set LastName='"; 
    sprintf(query,"%s%s%s",query,argv[1],"' where personID=5");
    puts(query);
    int t,r;
    conn=mysql_init(NULL);
    if(!mysql_real_connect(conn,server,user,password,database,0,NULL,0))
    {
        printf("Error connecting to database:%s\n",mysql_error(conn));
    }
    else
    {
        printf("Connected...\n");
    }
    t=mysql_query(conn,query);
    if(t)
    {
        printf("Error making query:%s\n",mysql_error(conn));
    }
    else
    {
        printf("update success\n");
    }
    mysql_close(conn);
    return 0;
}

delete.c

#include <mysql/mysql.h>
#include <stdio.h>
#include <string.h>
int main(int argc,char* argv[])
{
    MYSQL *conn;
    MYSQL_RES *res;
    MYSQL_ROW row;
    char* server="localhost";
    char* user="root";
    char* password="123";
    char* database="test";
    char query[200]="delete from Person where FirstName='xiong'";
    int t,r;
    conn=mysql_init(NULL);
    if(!mysql_real_connect(conn,server,user,password,database,0,NULL,0))
    {
        printf("Error connecting to database:%s\n",mysql_error(conn));
    }
    else
    {
        printf("Connected...\n");
    }
    t=mysql_query(conn,query);
    if(t)
    {
        printf("Error making query:%s\n",mysql_error(conn));
    }
    else
    {
        printf("delete success,delete row=%ld\n",(long)mysql_affected_rows(conn));
    }
    mysql_close(conn);
    return 0;
}