数据库基本操作
建立数据库
create database basename; #建立
create database if not exists basename; #判断存在否再建立
显示现有数据库
show databases;
使用数据库
use <database>;
查看数据库中的表
show tables;
创建表
create table datatable(
ID int primary key auto_increment, //自增null
Area decimal(7,2), //面积
class_num int //类别
);
查看表结构
数据库与C++链接
int mian()
{
MYSQL* mysql;
// MYSQL* connect = NULL;
mysql = mysql_init(nullptr);
if (mysql == nullptr)
{
printf("mysql_init error, %s\n");
return -1;
}
printf("mysql_init ok...\n");
// connect = mysql_real_connect(connect, "localhost", "root", "123456", "mydb", 0, NULL, 0);
if (mysql_real_connect(mysql, "localhost", "root", "123456", "mydb", 0, NULL, 0) == nullptr)
{
printf("mysql_real_connect error, err is: %s\n", mysql_error(mysql));
return -1;
}
// 配置为和数据库同步的utf8字符集
mysql_set_character_set(mysql, "utf8");
printf("mysql_real_connect ok...\n");
getchar();
mysql_close(mysql);
return 0;
}
C++调用mysql需要拼接成完整的sql语句
// 将double转为string
std::string double2string(const double& d)
{
std::stringstream s_tmp;
s_tmp << d;
std::string s = s_tmp.str();
return s;
}
添加键值,第一项自增
// 添加键值函数
int add_key_to_datatable(MYSQL* mysql, double Area, int class_num )
{
// 将传入的参数处理为一个完整的sql语句
// 因为第一个编号参数,配置的是自增,所以需要传入null
std::string sql_cmd = "insert into data value (null,'";
sql_cmd += double2string(Area);
sql_cmd += "',";
sql_cmd += std::to_string(class_num);
sql_cmd += ");";
cout << "[INFO] " << sql_cmd << endl;
// 执行语句
int ret = mysql_query(mysql, sql_cmd.c_str());
if (ret != 0)
{
cerr << "[ERR] mysql insert error: " << mysql_error(mysql) << endl;
}
return ret;
}
查询
// 返回用户的所有信息,DataID为空返回所有
void get_all_in_datatable(MYSQL* mysql, const std::string& DataID = "")
{
std::string sql_cmd = "select * from ";
sql_cmd += "data";
if (PillID.size() != 0)
{
sql_cmd += " where DataID='";
sql_cmd += data;
sql_cmd += "'";
}
sql_cmd += ";";
cout << "[INFO] " << sql_cmd << endl;
int ret = mysql_query(mysql, sql_cmd.c_str());
if (ret != 0)
{
cerr << "[ERR] mysql query error: " << mysql_error(mysql) << endl;
return;
}
// 获取结果
MYSQL_RES* res = mysql_store_result(mysql);
if (res == nullptr)
{
cerr << "[ERR] mysql store_result error: " << mysql_error(mysql) << endl;
return;
}
int row = mysql_num_rows(res); // 行
int col = mysql_num_fields(res); //列
printf("%10s%10s %10s\n", "DataID", "Area", "class_num");
for (int i = 0; i < row; i++)
{
MYSQL_ROW row_data = mysql_fetch_row(res);
for (int i = 0; i < col; i++)
{
printf("%10s", row_data[i]);
}
printf("\n");
}
// 释放结果
mysql_free_result(res);
}