Mysql备忘录1

程序中各种语句混杂，只是为了练习各种操作。

#define _CRT_SECURE_NO_WARNINGS
#include <iostream>
#include <mysql.h>
using namespace std;

#define sql01 "insert into student(name,chinese,english,math) values('测试1',85,85,85);"

int main()
{
    int ret = 0;
    MYSQL mysql;
    MYSQL *connect = NULL;
    MYSQL_RES *result;
    MYSQL_ROW sql_row;
    unsigned int num_fields;

    const char user[] = "root";         
    const char pswd[] = "123456";        
    const char host[] = "localhost";    
    const char database[] = "stu_db";       
    unsigned int port = 0; 


    //分配或初始化与mysql_real_connect()相适应的MYSQL对象
    connect = mysql_init(&mysql);
    if (connect == NULL)
    {
        ret = -1;
        cout<<"func mysql_init() err \n";
        goto END;
    }
    cout<<"func mysql_init() ok\n";
    //mysql_real_connect()尝试与运行在主机上的MySQL数据库引擎建立连接
    connect = mysql_real_connect(connect, host, user, pswd, database, port, NULL,0);
    if (connect == NULL)
    {
        ret = -2;
        cout<<"func mysql_real_connect() err \n";
        goto END;
    }
    cout<<"func mysql_real_connect() ok\n";

    //mysql_query执行由“Null终结的字符串”查询指向的SQL查询
    mysql_query(&mysql, "SET NAMES GBK"); //设置编码格式

    ret = mysql_query(&mysql, "select * from student");
    if (ret == 0)
    {
        result = mysql_store_result(&mysql);//查询结果
        if (result)// there are rows
        {
            //获取表头
            {
                //mysql_field_count返回作用在连接上的最近查询的列数, 查询是否应返回结果集
                unsigned int  num = mysql_field_count(&mysql) ;
                //mysql_fetch_fields对于结果集，返回所有MYSQL_FIELD结构的数组
                MYSQL_FIELD  *fields = mysql_fetch_fields(result);

                for (unsigned int i = 0; i < num; i++)
                {
                    printf("%s\t",  fields[i].name);
                }
                printf("\n");
            }
            //mysql_num_fields 返回结果集中的行数
            num_fields = mysql_num_fields(result);
            //mysql_fetch_row检索结果集的下一行
            while (sql_row = mysql_fetch_row(result))//每行结果
            {
                cout<<"name:"<<sql_row[1];
                if (sql_row[5] != NULL)
                {
                    cout<<" total score:"<<sql_row[5];
                }
                cout<<endl;
            }
        }
        else  // // mysql_store_result() returned nothing; should it have?
        {
            unsigned int num_rows;

            if(mysql_field_count(&mysql) == 0)
            {
                // query does not return data
                // (it was not a SELECT)
                //mysql_affected_rows返回上次UPDATE更改的行数，
                //上次DELETE删除的行数，或上次INSERT语句插入的行数
                num_rows = mysql_affected_rows(&mysql);
            }
            else // mysql_store_result() should have returned data
            {
                fprintf(stderr, "Error: %s\n", mysql_error(&mysql));
            }
        }
    }
    else
    {
        ret = -3;
        cout<<"func mysql_query() err\n";
        goto END;
    }

    //insert  默认提交
    ret = mysql_query(&mysql, sql01);
    if (ret != 0)
    {
        printf("mysql_query() err:%d\n", ret);
        goto END;
    }
    else
    {

        cout<<"insert succ"<<endl;
    }


END:
    if (result != NULL)
    {
        mysql_free_result(result);//释放结果集分配的内存
    }

    mysql_close(&mysql);//关闭前面打开的连接

    cout<<"hello mysql.."<<endl;
    system("pause");
    return ret;
}