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,\"[e-mail]\",\"123123\")");//    std::string str_name = "[e-mail]", 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;}