Ubuntu16.04下c++ 使用mysql

参考链接:https://www.cnblogs.com/zhuyongzhe/p/7686098.html
https://blog.youkuaiyun.com/gsl371/article/details/79181856
https://blog.youkuaiyun.com/u014338577/article/details/52373059
https://blog.youkuaiyun.com/qq_28139021/article/details/78548238

1.安装mysql8.0
sudo apt install mysql-server 的方式 默认安装的是MySQL 5.7
MySQL5.7版本最高只适配到Ubuntu17.04，不支持Ubuntu18.04
MySQL8.0可适配到Ubuntu18.04
而且加密方式需要选择5.x的加密，因为有兼容性问题
下载mysql8
sudo dpkg -i mysql-apt-config_0.8.10-1_all.deb
点第一个到里面选8.0然后出来按ok
sudo apt-get update
sudo apt-get install mysql-server
输入密码
选择兼容5.7
测试:mysql -u root -p
登录可以看到mysql版本信息

2.cmake工程中使用mysql
sudo apt-get install msql-client
sudo apt-get install libmysqlclient-dev

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
注释掉bind-address=127.0.0.1

include_directories(
/usr/local/lib
/usr/include/mysql
)
link_directories(/usr/lib/x86_64-linux-gnu)
target_link_libraries(libmysqlclient.so)

mmysql.h:

//
// Created by hhg on 19-4-2.
//

#ifndef GITTEST_MMYSQL_H
#define GITTEST_MMYSQL_H


#include <string>
#include <mysql/mysql.h>
using namespace std;
struct mysqlLogin{
    const char* user;
    const char* passwd;
    const char* dbname;
    const char* host;
    unsigned int port;
    const char* unix_socket;
    long unsigned int client_flag;
};

class mmysql {
public:
    static mmysql *pInstall; // 连接事例
    MYSQL *p;
    struct mysqlLogin login;
    static mmysql* makeInstall();
    // 错误信息
    void print_error();
    // mysql连接
    void mysql_connect(const char* user, const char* passwd, const char* dbname);
    // sql 查询
    void mysql_querys(const char* query);
    // 获取更新行数
    long unsigned int  mysql_cout();
    // 数据读取
    void mysql_read();
    int databaseInsert(const char*cmd);
    int databaseDelete(const char* cmd);
    int databaseUpdate(const char* cmd);
    int databaseQuery(char* cmd,char ** row);
    int databaseClose();
    // 存储读取数据
    string **tep;
    int row;
    int cow;
//    MYSQL *dbHandle = mysql_init(NULL);
private:
    // 错误号
    unsigned int erron;
    mmysql();

public:
    ~mmysql();

};
#endif //GITTEST_MMYSQL_H

mmysql.cpp

//
// Created by hhg on 19-4-2.
//

#include "mmysql.h"
#include <iostream>
#include <cstring>
using namespace std;

mmysql* mmysql::pInstall = new mmysql();

mmysql::mmysql() {
    this->p = mysql_init(NULL);
}
mmysql::~mmysql() {
    mysql_close(this->p);
}

mmysql* mmysql::makeInstall() {
    return pInstall;
}

void mmysql::mysql_connect(const char* user, const char* passwd, const char* dbname) {
    if(mysql_real_connect(this->p, "localhost", user, passwd, dbname, 0, NULL, 0)) {
         cout << "连接成功" << endl;
        // 设置编码
        mysql_query(p, "set names utf8");
    }  else {
        this->erron = 1;
    }
}

void mmysql::print_error() {
    switch(this->erron) {
        case 1:
            cout << "连接失败" << mysql_error(this->p) << endl;
            break;
        case 2:
            cout << "查询失败" << mysql_error(this->p) << endl;
            break;
        default:
            cout << "错误信息不存在" << endl;
            break;
    }
}

void mmysql::mysql_querys(const char* query) {
    if(mysql_query(this->p, query)) {
        this->erron = 2;
    }
}

long unsigned int mmysql::mysql_cout() {
    return mysql_affected_rows(this->p);
}

void mmysql::mysql_read() {
    // 释放数据
    if(this->row != NULL) {
        for(int i = 0; i < this->cow; i++ ) {
            delete[] this->tep[i];
        }
        delete[] this->tep;
    }

    MYSQL_RES *result = mysql_store_result(this->p);
    MYSQL_ROW row;
    int cow_lenth = result->field_count;
    int row_lenth = result->row_count;
    // 数据存储
    this->tep = new string* [row_lenth];
    for( int i=0; i<row_lenth; i++) {
        this->tep[i] = new string[cow_lenth];
    }
    // 行
    this->row = row_lenth;
    // 列
    this->cow = cow_lenth;
    for (int i = 0; i < row_lenth; i++) {
        row = mysql_fetch_row(result);
        for( int j = 0; j < cow_lenth; j++ ) {
            this->tep[i][j] = row[j];
        }
    }
    // 释放数据
    mysql_free_result(result);
}

int mmysql::databaseInsert(const char*cmd)
{
    if(NULL ==cmd)
    {
        cout<<"[insert] cmd error"<<endl;
        return -1;
    }
    int ret=mysql_query(p,cmd);
    if(ret !=0)
    {
        cout<<"database insert info:exist,I am update."<<endl;
        return -1;
    }
    return 0;
}

int mmysql::databaseDelete(const char* cmd)
{
    if(NULL == cmd)
    {
        cout<<"[delete] cmd err"<<endl;
        return -1;
    }
    int ret=mysql_query(p,cmd);
    if(ret!=0)
    {
        cout<<"database delete info : not exist."<<endl;
        return -1;
    }
    return  0;
}

int mmysql::databaseUpdate(const char* cmd)
{
    if(NULL == cmd)
    {
        cout<<"[update] cmd error"<<endl;
        return -1;
    }
    int ret=mysql_query(p,cmd);
    if(ret!=0)
    {
        cout<<"database update info:not exist,I am insert"<<endl;
        return -1;
    }
    return 0;
}

int mmysql::databaseQuery(char* cmd,char ** row)
{
    if(NULL == cmd || NULL ==row)
    {
        cout<<"[Query] cmd error"<<endl;
        return -1;
    }
    mysql_real_query(p,cmd,strlen(cmd));
    MYSQL_RES * result=mysql_store_result(p);
    if(result !=NULL)
    {
        int numLine=mysql_num_rows(result);
        int numList=mysql_num_fields(result);
    }
    else{
        cout<<"[Query] mysql_store_result error !"<<endl;
        return -1;
    }
    while((row=mysql_fetch_row(result)));
    {
        printf("%s,%s,%s,%s\n",row[0],row[1],row[2],row[3]);
    }
    mysql_free_result(result);
    return 0;
}

int mmysql::databaseClose()
{
    mysql_close(p);
    return 0;
}

main.cpp

int main() {
	#include <mysql/mysql.h>
	#include <stdio.h>
	#include <string.h>
	#include <string>
	#include <iostream>
	#include <cstring>
	#include "mmysql.h"
    mmysql *pInstall = mmysql::makeInstall();
    pInstall->mysql_connect("root", "123456", "test");
    //pInstall->databaseUpdate("insert into test (name,score) values('hhg',88)");
	//pInstall->databaseInsert("insert into test (name,score) values('zcc',96)");
	pInstall->databaseDelete("delete from test where (name='hhg' and score=88)");
    return 0;
   }

3.mysql的语句
mysql -u root -p 登录mysql
show databases;查看所有数据库
use mysql;使用mysql数据库
show tables;查看所有表
create database test;创建数据库
use test;
show tables;查看表
create table mathScore(No int(10),Name varchar(40),score int(10)); 根据字段名创建表
根据已有的表创建新表:
create table tab_new like tab_old;使用旧表创建新表
create table tab_new as select col1,col2 … from tab_old definition only;
describe test;查看表的定义描述
drop database dbname;删除数据库
drop table tabname;删除新表
增加一个列:
alter table mathScore add level varchar(30) null;
删除一列
alter table mathScore drop column level;

重命名表:
rename table mathScore to midTest;

插入一条数据:
insert into mathScore (No,name,score) values(1001,‘xiaoming’,88);
insert into mathScore set No=1002,name=‘xiaowang’,score=98;
delete from mathScore where (No=1001);
delete from mathScore where (No=1001 and name=‘xiaoming’);
查询数据：select * from 表明 whrer…;
添加数据：insert into 表名(列名) values(值),(值)…;
修改数据：update 表名 set 列1=值1,… where …;
删除数据：delete from 表名 where …;
exit; / quit; 退出。

添加id字段:
alter table mathScore add id int not null
primary key auto_increment first;

创建表:
create table tb1(goods varchar(20) not null);
create table tb2(price int(10) not null);
设置索引:
alter table tb1 add index idx_tb1 (goods);
alter table tb2 add index idx_tb2 (price);
定义外键:
alter table tb2 add constraint goods_num foreign key (price) references tb1 (goods);
级联更改操作:
alter table tb2 add constraint goods_num foreign key (price) references tb1 (goods) on update cascade;

若后期后改进,后补充~