willcool4ever • Linux下Mysql

使用数据库步骤:
1.引用头文件<mysql/mysql.h>
2.makefile 链接加上-lmysqlclient
3.初始化client
4.建立连接
5.client向server发送sql语句,server返回client结果
6.断开连接

一 连接数据库
mysql_init(MYSQL * pmysql)
MYSQL  *mysql_real_connect(MYSQL *pmysql, const char *hostname,const char *username,const char *passwd, const char*dbname,0,0,0);
//连接到MySQL之后必须先调用mysql_init初始化
//之后调用mysql_real_connect连接到数据库
//mysql_real_connect成功返回指向MySQL连接的指针,失败返回NULL

二 执行SQL语句
int mysql_query(MYSQL *pmysql, const char *sql);
//参数pmysql是连接到MySQL的指针
//参数sql是要执行的sql语句
//成功返回0,失败返回非0

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <unistd.h>
#include <mysql/mysql.h>
#include <errno.h>
int main()
{
    MYSQL mysql, *connection;
    mysql_init(&mysql);
    connection = mysql_real_connect(&mysql,"localhost","dbuser1","dbuser1", "db1",0, 0, 0);
    if(connection == NULL)
    {
        printf("connect error, %s\n", mysql_error(&mysql));
        return -1;
    }
    if(mysql_query(connection, "SET NAMES utf8 " ) != 0)
    {
        printf("set character utf8 error, %s\n", mysql_error(&mysql));
        return -1;
    }

    printf("请输入要干掉的名字>");fflush(stdout);
    char SQL[1024];
    memset(SQL, 0,sizeof(SQL));
    char name[1024];
    memset(name, 0,sizeof(name));

    read(STDIN_FILENO,name,sizeof(name));

    name[strlen(name) -1] = 0;
    sprintf(SQL,"DELETE FROM table1 WHERE name = '%s' ",name);
    printf("'%s'\n", SQL);

    if(mysql_query(connection, SQL ) != 0)
    {
        printf("query error, %s\n", mysql_error(&mysql));
        return -1;
    }

    mysql_close(connection);

    return EXIT_SUCCESS;
}

三 执行Select语句
MYSQL_RES *mysql_store_result(MYSQL *pmysql);
//成功返回一个查询结果指针,查询无结果或者错误返回NULL
mysql_free_result(MYSQL_RES *res)
//调用完mysql_store_result,一定要用mysql_free_result释放相关资源

四 查看查询结果
MYSQL_ROW mysql_fetch_row(MYSQL_RES *result);
/*例子*/
while( (row = mysql_fetch_row(result) )!= NULL)
{
    printf("name:%s,sex:%s,age:%s,class:%s\n",row[0],row[1],row[2],row[3]);//尽管age是数字,但row返回的是NULL结尾的字符串
}

void select()
{

    if(mysql_query(connection,"SELECT * FROM table1") != 0)
    {
        printf("query error, %s\n", mysql_error(&mysql));
        return -1;
    }

    MYSQL_RES * result = mysql_store_result(connection);
    MYSQL_ROW  row;
    while(1)
    {
        if(row == NULL)
            break;
        row = mysql_fetch_row(result);
        printf("name:%s,sex:%s,age:%s,class:%s\n",row[0],row[1],row[2],row[3]);//尽管age是数字,但row返回的是NULL结尾的字符串
    }

    mysql_free_result(result);
}

五 查看数据库字段信息
MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *result);
//mysql_fetch_row返回是记录（行）
//mysql_fetch_field返回是字段（列）
/*例子*/
while((sqlField = mysql_fetch_field(result))!=NULL)
{
    printf("%s\n", sqlField->name);

}