Mysql增删改查和查询返回

#ifndef __MYS_H__
#define __MYS_H__

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "/usr/include/mysql/mysql.h"

extern int record_change(const char* db_name,const char* sql_cmd);
extern int record_search_id(const char* db_name, const char* sql_cmd);
extern int record_search_statue(const char* db_name, const char* sql_cmd);

#endif

#include "mys.h"
int main(void)
{
    const char* db_name;
    const char* sql_cmd;
    int id;
    int statue;
    /*往数据库中增删改记录*/
    db_name="smart";
    sql_cmd="insert into User(username,password,statue) values(\"13825259141\",\"ttt\",1)";
    record_change(db_name,sql_cmd);

    /*特定条件查询具体数据库记录*/
    sql_cmd="select user_id from User where username=\"13642245956\"";
    id=record_search_id(db_name,sql_cmd);
    printf("%d\n",id);
    //查找状态
    sql_cmd="select statue from User where username=\"13642245956\"";
    statue=record_search_statue(db_name,sql_cmd);
    printf("%d\n",statue);

    return 0;
}

#include "mys.h"

/*往数据库中增删改记录*/
int record_change(const char* db_name,const char* mysql_cmd)
{
    MYSQL conn;
    int res;
    mysql_init(&conn); //初始化 连接
    if(mysql_real_connect(&conn,"localhost","root","ttt",db_name,0,NULL,CLIENT_FOUND_ROWS)){
        printf("connect success\n");

        res=mysql_query(&conn,mysql_cmd);
        if (!res)
        {
            printf("Inserted %lu rows\n",(unsigned long)mysql_affected_rows(&conn));//里头的函数返回受表中影响的行数
        }
        else{
            fprintf(stderr, "Insert error %d: %s\n",mysql_errno(&conn),mysql_error(&conn)); //分别打印出错误代码及详细信息
        }
        mysql_close(&conn);
    }
    else{
        fprintf(stderr, "Connection failed\n");
        if (mysql_errno(&conn))
        {
            fprintf(stderr, "Connection error %d:%s\n",mysql_errno(&conn),mysql_error(&conn));
        }
    }
    return EXIT_SUCCESS;
}

/*特定条件查询具体数据库记录*/
int record_search_id(const char* db_name, const char* sql_cmd)
{
    MYSQL conn;
    MYSQL_RES *res_ptr; /*指向检索的结果存放地址的指针*/
    MYSQL_ROW sqlrow; /*返回的记录信息*/
    MYSQL_FIELD *fd; /*字段结构指针*/

    int res; /*执行查询操作后的返回标志*/
    int id;

    mysql_init(&conn);

    /*mysql_real_connect(&mysql,host,user,passwd,dbname,0,NULL,0) == NULL)*/
    if (mysql_real_connect(&conn, "localhost", "root","ttt","smart",0,NULL,CLIENT_FOUND_ROWS))
    {
        res = mysql_query(&conn,sql_cmd);
        if (res)
        {
            printf("SELECT error:%s\n",mysql_error(&conn));
        }
        else
        {
            res_ptr=mysql_store_result(&conn);
            sqlrow=mysql_fetch_row(res_ptr);
            id=*sqlrow[0]-'0';

            if (mysql_errno(&conn))
            {
                fprintf(stderr,"Retrive error:s\n",mysql_error(&conn));
            }
            mysql_free_result(res_ptr);
        }
        mysql_close(&conn);
    }
    else
    {
        fprintf(stderr, "Connection failed\n");
        if (mysql_errno(&conn))
        {
            fprintf(stderr, "Connection error %d: %s\n",mysql_errno(&conn),mysql_error(&conn));
        }
    }
    return id;

}
//查找状态
int record_search_statue(const char* db_name, const char* sql_cmd)
{
    MYSQL conn;
    MYSQL_RES *res_ptr; /*指向检索的结果存放地址的指针*/
    MYSQL_ROW sqlrow; /*返回的记录信息*/
    MYSQL_FIELD *fd; /*字段结构指针*/

    int res; /*执行查询操作后的返回标志*/
    int statue;

    mysql_init(&conn);

    /*mysql_real_connect(&mysql,host,user,passwd,dbname,0,NULL,0) == NULL)*/
    if (mysql_real_connect(&conn, "localhost", "root","ttt","smart",0,NULL,CLIENT_FOUND_ROWS))
    {
        res = mysql_query(&conn,sql_cmd);
        if (res)
        {
            printf("SELECT error:%s\n",mysql_error(&conn));
        }
        else
        {
            res_ptr=mysql_store_result(&conn);
            sqlrow=mysql_fetch_row(res_ptr);
            statue=*sqlrow[0]-'0';

            if (mysql_errno(&conn))
            {
                fprintf(stderr,"Retrive error:s\n",mysql_error(&conn));
            }
            mysql_free_result(res_ptr);
        }
        mysql_close(&conn);
    }
    else
    {
        fprintf(stderr, "Connection failed\n");

        if (mysql_errno(&conn))
        {
            fprintf(stderr, "Connection error %d: %s\n",mysql_errno(&conn),mysql_error(&conn));
        }
    }
    return statue;
}

编译：gcc -o test ctrdb.c main.c `mysql_config --cflags --libs`

---

搭建Lamp服务器可以直接操作Mysql数据库。
在Linux使用MySQL示例：

1. mysql -uroot -p

2. create database test;

3. use test;

4.  create table User(user_id int not null primary key auto_increment,username varchar(255) not null unique,password varchar(255) not null,statue int);

5. insert into children values(5,"花儿",10);

6. create table Dev(dev_id int not null primary key auto_increment,devname varchar(255),user_id int,statue int,constraint fk_UserDev foreign key (user_id) references User (user_id));

7. insert into User(username,password,statue) values("13642245956","123456",1);

8. insert into User(username,password,statue) values("18391390367","654321",1);

9. insert into Dev(devname,user_id,statue) values("sunsangGlax6",1,0);