MYSQL 操作简介



//Cmake

include_directories("C:/msys64/mingw64/include/mysql")
target_link_libraries(MySQL_testall mysqlclient)

#include <iostream>
#include "mysql_manager.h"
#include <sstream>
#include <algorithm>


int main()
{
    mysql_manager::getinstance().init(1, 100, "localhost", "root", "root", "test", 3306, NULL, 0);
    auto m_sql = &*mysql_manager::getinstance().get_connect();
    mysql_query(m_sql, "CREATE TABLE TAB3(ID INT PRIMARY KEY AUTO_INCREMENT NOT NULL,"
            "TIME DATETIME,TITLE VARCHAR(128) NOT NULL,ARTICLE TEXT NOT NULL)");
    //mysql_query(m_sql, "INSERT INTO TAB3 (TIME,TITLE,ARTICLE) VALUES(NOW(),\"First Article\",\"hello we are the world \")");
    //mysql_query(m_sql, "INSERT INTO TAB3 (TIME,TITLE,ARTICLE) VALUES(NOW(),\"Second Article\",\"hello we are the world ! I am weifuliang .\")");
    //mysql_query(m_sql,"SELECT * FROM TAB2");
    //mysql_query(m_sql,"SELECT * FROM TAB3");
    std::stringstream ss_query;
    std::string str_title, str_article, str_id;
    str_title = "weifuliangsucuihuang";
    str_article = "12341234123412341234";
    str_id = "2";
    ss_query << "UPDATE TAB3 SET TIME=NOW(),TITLE='" << str_title << "',ARTICLE='" << str_article << "'WHERE ID='"<< str_id << "'" ;
    mysql_query(m_sql,ss_query.str().c_str());
    mysql_query(m_sql,"SELECT * FROM TAB3");

    //std::shared_ptr<MYSQL> m_sql= mysql_manager::getinstance().get_connect();

    //mysql_query(&(*m_sql),"CREATE TABLE COMPANY(ID INT PRIMARY KEY NOT NULL, ARTICLE TEXT NOT NULL)");
    //mysql_query(&(*m_sql),"INSERT INTO COMPANY VALUES(1,\"Hello world! I am weifuliang.\")");
    //mysql_query(&(*m_sql),"DELETE FROM COMPANY WHERE ID = 1");
    //mysql_query(&(*m_sql),"SELECT ARTICLE FROM COMPANY");
//    mysql_query(m_sql,
//                "CREATE TABLE LOGIN(ID INT PRIMARY KEY NOT NULL, USERNAME VARCHAR(32) NOT NULL,PASSWORD VARCHAR(32) NOT NULL)");
//    mysql_query(m_sql, "INSERT INTO LOGIN VALUES(1,\"123@qq.com\",\"123123\")");

//    std::string str_name = "123@qq.com", str_password = "123123", str_query;
//    str_query = "SELECT USERNAME,PASSWORD FROM LOGIN WHERE USERNAME='" + str_name + "'AND PASSWORD='" + str_password + "'";
//    mysql_query(&(*m_sql), str_query.c_str());


//    std::ostringstream sstream;  //#include <sstream>
//    sstream<<"SELECT USERNAME,PASSWORD FROM LOGIN WHERE USERNAME='"<<str_name<<"'AND'"<<str_password<<"'";
//    mysql_query(&(*m_sql),sstream.str().c_str());

    //mysql_query(&(*m_sql),"CREATE table tb2(NAME VARCHAR(20),AGE INT(3))");
    //mysql_query(&(*m_sql), "SELECT ID AS NAMWID FROM tb");  //列名的替换
    //mysql_query(&(*m_sql), "SELECT concat(id,name) from tb");  //concat 用于连接字符串
    //mysql_query(&(*m_sql), "SELECT left(name,3) from tb");  //left 用于显示字符串的前几个字符
    //mysql_query(&(*m_sql), "SELECT right(name,3) from tb");  //right 用于显示字符串的后面个字符包括空格
//    mysql_query(&(*m_sql), "SELECT upper(name) from tb");  //upper 用于显示字符串转换成大写
    //mysql_query(&(*m_sql), "SELECT lower(name) from tb");  //lower 用于显示字符串转换成小写
    // mysql_query(&(*m_sql), "SELECT substring(name,2,4) from tb");  //substring 用于显示字符串从第几个到第几个
    //mysql_query(&(*m_sql), "SELECT LTRIM(name) from tb");  //LTRIM 用于删除字符串前面的空格
    //mysql_query(&(*m_sql), "SELECT RTRIM(name) from tb");  //RTRIM 用于删除字符串后面的空格
    //mysql_query(&(*m_sql), "SELECT NOW()");  //NOW 用于查看系统时间
    //mysql_query(&(*m_sql), "SELECT date_format('2017-8-9','%d')");  //date_format 显示当月的第几天
//     mysql_query(&(*m_sql), "SELECT DATEDIFF(NOW(),'2016-3-5')");  //DATEDIFF 用于求某一时间段的天数
    //mysql_query(&(*m_sql), "SELECT round(123.123456789,3)");  //round 用于求某数据的精确度 保留几位小数
    //mysql_query(&(*m_sql), "SELECT round(123.123456789,-1)");  //round 用于求某数据的精确度 保留几位小数  负数代表小数点左半边的
    // mysql_query(&(*m_sql), "SELECT round(pi(),3)");  //round 用于求某数据的精确度 保留几位小数  pi() 代表无理数pi 3.14...
    // mysql_query(&(*m_sql), "SELECT round(PI(),100)");  //round 用于求某数据的精确度 保留几位小数 pi 最多保留小数点后15位
    //mysql_query(&(*m_sql), "SELECT power(2,10)");  //power 用于求某数据的次幂运算  如2的10次幂
    //mysql_query(&(*m_sql), "SELECT CAST('123' AS int)");  //CAST 用于转换数据类型  但是可能在wondows 上不好使 不知道为啥 可略过哦
    //mysql_query(&(*m_sql), "INSERT INTO tb VALUES(100,NULL,33)");
    //mysql_query(&(*m_sql), "SELECT IFNULL(NAME,'noname') AS name FROM tb");   //IFNULL用来填充一些null的值进行一系列的替换
    //mysql_query(&(*m_sql), "SELECT NAME FROM tb ORDER BY   NAME");    //  （列名 ORDER BY 列名 ） 排序 默认升序（ASC） 降序（DESC）
    //mysql_query(&(*m_sql), "SELECT NAME FROM tb ORDER BY   ID,NAME ");    //  （列名 ORDER BY 列名  firstname,endname） 排序 默认升序（ASC） 降序（DESC）





    MYSQL_RES *result = mysql_store_result(m_sql);
    if(!mysql_num_rows(result))
    {
        std::cout << "Can't find ! "<< std::endl;
    }
    MYSQL_ROW row;
    unsigned int num_fields;       //这是代表列数
    unsigned int i;
    num_fields = mysql_num_fields(result);     //获取列数

    MYSQL_FIELD *fiedl = mysql_fetch_fields(result);
    //MYSQL_FIELD_OFFSET file_offset = mysql_field_tell(result);

    for (int i = 0; i < num_fields; ++i)
    {
        std::cout << fiedl[i].name << "  ";
    }

    std::cout << std::endl;

    while ((row = mysql_fetch_row(result)))      //从结果集中捕获一行,给row
    {
        unsigned long *lengths;
        lengths = mysql_fetch_lengths(result);     //返回结果集当前行的列长度;返回的ulong* 指针
        std::string str_;
//        for (i = 0; i < num_fields; i++)
//        {
            str_ += row[i];
//            std::cout << "row[ " << i<< " ] :" << row[i]<< std::endl;
//        }
        //std::cout << "str__ :" << str_ << std::endl;

        for (i = 0; i < num_fields; i++)
        {
            printf("%.*s ", (int) lengths[i], row[i] ? row[i] : "NULL");   //根据每个列长度输出对应行值
        }
        printf("\n");
    }
    mysql_free_result(result); //释放结果集;
//    MY_CHARSET_INFO cs;
//    mysql_get_character_set_info(&*m_sql, &cs);
//    //std::cout<<__CONCAT("as","asd")<<std::endl;
//    std::cout << mysql_get_client_info() << std::endl;


    return 0;
}