当前位置：Gxlcms > 数据库问题 > MySQL增、删、改、查基础操作（C++）

MySQL增、删、改、查基础操作（C++）

时间：2021-07-01 10:21:17 帮助过：30人阅读

host, string user, string passwd, string db, unsigned port) { m_host = host; m_user = user; m_passwd = passwd; m_dbname = db; m_port = port; mysql_init(&connect); } bool MySQL::connectDB() { if (!mysql_real_connect(&connect, m_host.c_str(), m_user.c_str(), m_passwd.c_str(), m_dbname.c_str(), m_port, NULL, 0)) { cout << "mysql connect error with " << mysql_errno(&connect) << endl; return false; } return true; }

增：

  1. <span style="color: #0000ff">bool</span> MySQL::Insert(<span style="color: #0000ff">string</span> table, map<<span style="color: #0000ff">string</span>, <span style="color: #0000ff">string</span>><span style="color: #000000"> values)
  2. {
  3.     sql.clear();
  4.     </span><span style="color: #0000ff">if</span> (values.size() == <span style="color: #800080">0</span><span style="color: #000000">)
  5.     {
  6.         cout </span><< <span style="color: #800000">"</span><span style="color: #800000">Input Error!!!</span><span style="color: #800000">"</span> <<<span style="color: #000000"> endl;
  7.         </span><span style="color: #0000ff">return</span> <span style="color: #0000ff">false</span><span style="color: #000000">;
  8.     }
  9.     </span><span style="color: #0000ff">else</span><span style="color: #000000">
  10.     {
  11.         sql </span>= <span style="color: #800000">"</span><span style="color: #800000">INSERT INTO </span><span style="color: #800000">"</span> + table + <span style="color: #800000">"</span><span style="color: #800000">(</span><span style="color: #800000">"</span><span style="color: #000000">;
  12.         auto iter </span>=<span style="color: #000000"> values.begin();
  13.         </span><span style="color: #0000ff">while</span> (iter !=<span style="color: #000000"> values.end())
  14.         {
  15.             sql </span>+= iter->first + <span style="color: #800000">‘</span><span style="color: #800000">,</span><span style="color: #800000">‘</span><span style="color: #000000">;
  16.             iter</span>++<span style="color: #000000">;
  17.         }
  18.         sql </span>= sql.substr(<span style="color: #800080">0</span>, sql.rfind(<span style="color: #800000">‘</span><span style="color: #800000">,</span><span style="color: #800000">‘</span>)) + <span style="color: #800000">"</span><span style="color: #800000">) VALUES(</span><span style="color: #800000">"</span><span style="color: #000000">;
  19.         iter </span>=<span style="color: #000000"> values.begin();
  20.         </span><span style="color: #0000ff">while</span> (iter !=<span style="color: #000000"> values.end())
  21.         {
  22.             sql </span>+= <span style="color: #0000ff">string</span>(<span style="color: #800000">"</span><span style="color: #800000">\"</span><span style="color: #800000">"</span>) + iter->second + <span style="color: #800000">"</span><span style="color: #800000">\",</span><span style="color: #800000">"</span><span style="color: #000000">;
  23.             iter</span>++<span style="color: #000000">;
  24.         }
  25.         sql </span>= sql.substr(<span style="color: #800080">0</span>, sql.rfind(<span style="color: #800000">‘</span><span style="color: #800000">,</span><span style="color: #800000">‘</span>)) + <span style="color: #800000">"</span><span style="color: #800000">);</span><span style="color: #800000">"</span><span style="color: #000000">;
  26.         ret </span>= mysql_query(&<span style="color: #000000">connect, sql.c_str());
  27.         </span><span style="color: #0000ff">if</span> (ret != <span style="color: #800080">0</span><span style="color: #000000">)
  28.         {
  29.             cout </span><< <span style="color: #800000">"</span><span style="color: #800000">mysql query error with </span><span style="color: #800000">"</span> << ret << <span style="color: #800000">"</span><span style="color: #800000"> Reason: </span><span style="color: #800000">"</span> << mysql_error(&connect) <<<span style="color: #000000"> endl;
  30.             </span><span style="color: #0000ff">return</span> <span style="color: #0000ff">false</span><span style="color: #000000">;
  31.         }
  32.         ret </span>= mysql_query(&connect, <span style="color: #800000">"</span><span style="color: #800000">commit</span><span style="color: #800000">"</span><span style="color: #000000">);
  33.         </span><span style="color: #0000ff">return</span> <span style="color: #0000ff">true</span><span style="color: #000000">;
  34.     }
  35. }</span>

删：

  1. <span style="color: #0000ff">bool</span> MySQL::Delete(<span style="color: #0000ff">string</span> table, map<<span style="color: #0000ff">string</span>, <span style="color: #0000ff">string</span>><span style="color: #000000"> values)
  2. {
  3.     sql.clear();
  4.     </span><span style="color: #0000ff">if</span> (values.size() == <span style="color: #800080">0</span><span style="color: #000000">)
  5.     {
  6.         cout </span><< <span style="color: #800000">"</span><span style="color: #800000">Input Error!!!</span><span style="color: #800000">"</span> <<<span style="color: #000000"> endl;
  7.         </span><span style="color: #0000ff">return</span> <span style="color: #0000ff">false</span><span style="color: #000000">;
  8.     }
  9.     </span><span style="color: #0000ff">else</span><span style="color: #000000">
  10.     {
  11.         sql </span>= <span style="color: #800000">"</span><span style="color: #800000">DELETE FROM </span><span style="color: #800000">"</span> + table + <span style="color: #800000">"</span><span style="color: #800000"> WHERE </span><span style="color: #800000">"</span><span style="color: #000000">;
  12.         auto iter </span>=<span style="color: #000000"> values.begin();
  13.         </span><span style="color: #0000ff">while</span> (iter !=<span style="color: #000000"> values.end())
  14.         {
  15.             sql </span>+= iter->first + <span style="color: #800000">"</span><span style="color: #800000"> = </span><span style="color: #800000">"</span> + iter->second + <span style="color: #800000">"</span><span style="color: #800000"> and </span><span style="color: #800000">"</span><span style="color: #000000">;
  16.             iter</span>++<span style="color: #000000">;
  17.         }
  18.         sql </span>= sql.substr(<span style="color: #800080">0</span>, sql.rfind(<span style="color: #800000">"</span><span style="color: #800000">and</span><span style="color: #800000">"</span>)) + <span style="color: #800000">"</span><span style="color: #800000">;</span><span style="color: #800000">"</span><span style="color: #000000">;
  19.         ret </span>= mysql_query(&<span style="color: #000000">connect, sql.c_str());
  20.         </span><span style="color: #0000ff">if</span> (ret != <span style="color: #800080">0</span><span style="color: #000000">)
  21.         {
  22.             cout </span><< <span style="color: #800000">"</span><span style="color: #800000">mysql query error with </span><span style="color: #800000">"</span> << ret << <span style="color: #800000">"</span><span style="color: #800000"> Reason: </span><span style="color: #800000">"</span> << mysql_error(&connect) <<<span style="color: #000000"> endl;
  23.             </span><span style="color: #0000ff">return</span> <span style="color: #0000ff">false</span><span style="color: #000000">;
  24.         }
  25.         ret </span>= mysql_query(&connect, <span style="color: #800000">"</span><span style="color: #800000">commit</span><span style="color: #800000">"</span><span style="color: #000000">);
  26.         </span><span style="color: #0000ff">return</span> <span style="color: #0000ff">true</span><span style="color: #000000">;
  27.     }
  28. }</span>

改：

  1. <span style="color: #0000ff">bool</span> MySQL::Update(<span style="color: #0000ff">string</span> table, map<<span style="color: #0000ff">string</span>, <span style="color: #0000ff">string</span>><span style="color: #000000"> values)
  2. {
  3.     sql.clear();
  4.     </span><span style="color: #0000ff">if</span> (values.size() == <span style="color: #800080">0</span><span style="color: #000000">)
  5.     {
  6.         cout </span><< <span style="color: #800000">"</span><span style="color: #800000">Input Error!!!</span><span style="color: #800000">"</span> <<<span style="color: #000000"> endl;
  7.         </span><span style="color: #0000ff">return</span> <span style="color: #0000ff">false</span><span style="color: #000000">;
  8.     }
  9.     </span><span style="color: #0000ff">else</span><span style="color: #000000">
  10.     {
  11.         sql </span>= <span style="color: #800000">"</span><span style="color: #800000">UPDATE </span><span style="color: #800000">"</span> + table + <span style="color: #800000">"</span><span style="color: #800000"> SET </span><span style="color: #800000">"</span><span style="color: #000000">;
  12.         auto iter </span>=<span style="color: #000000"> values.begin();
  13.         </span><span style="color: #0000ff">while</span> (iter !=<span style="color: #000000"> values.end())
  14.         {
  15.             sql </span>+= iter->first + <span style="color: #800000">"</span><span style="color: #800000"> = \"</span><span style="color: #800000">"</span> + iter->second + <span style="color: #800000">"</span><span style="color: #800000">\" and </span><span style="color: #800000">"</span><span style="color: #000000">;
  16.             iter</span>++<span style="color: #000000">;
  17.         }
  18.         sql </span>= sql.substr(<span style="color: #800080">0</span>, sql.rfind(<span style="color: #800000">"</span><span style="color: #800000">and</span><span style="color: #800000">"</span>)) + <span style="color: #800000">"</span><span style="color: #800000">;</span><span style="color: #800000">"</span><span style="color: #000000">;
  19.         ret </span>= mysql_query(&<span style="color: #000000">connect, sql.c_str());
  20.         </span><span style="color: #0000ff">if</span> (ret != <span style="color: #800080">0</span><span style="color: #000000">)
  21.         {
  22.             cout </span><< <span style="color: #800000">"</span><span style="color: #800000">mysql query error with </span><span style="color: #800000">"</span> << ret << <span style="color: #800000">"</span><span style="color: #800000"> Reason: </span><span style="color: #800000">"</span> << mysql_error(&connect) <<<span style="color: #000000"> endl;
  23.             </span><span style="color: #0000ff">return</span> <span style="color: #0000ff">false</span><span style="color: #000000">;
  24.         }
  25.         ret </span>= mysql_query(&connect, <span style="color: #800000">"</span><span style="color: #800000">commit</span><span style="color: #800000">"</span><span style="color: #000000">);
  26.         </span><span style="color: #0000ff">return</span> <span style="color: #0000ff">true</span><span style="color: #000000">;
  27.     }
  28. }</span>

查：

  1. <span style="color: #0000ff">bool</span> MySQL::Select(<span style="color: #0000ff">string</span> table, map<<span style="color: #0000ff">string</span>, <span style="color: #0000ff">string</span>><span style="color: #000000"> values)
  2. {
  3.     sql.clear();
  4.     </span><span style="color: #0000ff">if</span>(values.size() == <span style="color: #800080">0</span><span style="color: #000000">)
  5.         sql </span>= <span style="color: #800000">"</span><span style="color: #800000">SELECT * FROM </span><span style="color: #800000">"</span> + table + <span style="color: #800000">"</span><span style="color: #800000"> ;</span><span style="color: #800000">"</span><span style="color: #000000">;
  6.     </span><span style="color: #0000ff">else</span><span style="color: #000000">
  7.     {
  8.         sql </span>= <span style="color: #800000">"</span><span style="color: #800000">SELECT * FROM </span><span style="color: #800000">"</span> + table + <span style="color: #800000">"</span><span style="color: #800000"> where </span><span style="color: #800000">"</span><span style="color: #000000">;
  9.         auto iter </span>=<span style="color: #000000"> values.begin();
  10.         </span><span style="color: #0000ff">while</span> (iter !=<span style="color: #000000"> values.end())
  11.         {
  12.             sql </span>+= iter->first + <span style="color: #800000">"</span><span style="color: #800000"> = \"</span><span style="color: #800000">"</span> + iter->second + <span style="color: #800000">"</span><span style="color: #800000">\" and </span><span style="color: #800000">"</span><span style="color: #000000">;
  13.             iter</span>++<span style="color: #000000">;
  14.         }
  15.         sql </span>= sql.substr(<span style="color: #800080">0</span>, sql.rfind(<span style="color: #800000">"</span><span style="color: #800000">and</span><span style="color: #800000">"</span>)) + <span style="color: #800000">"</span><span style="color: #800000">;</span><span style="color: #800000">"</span><span style="color: #000000">;
  16.     }
  17.     ret </span>= mysql_query(&<span style="color: #000000">connect, sql.c_str());
  18.     </span><span style="color: #0000ff">if</span> (ret != <span style="color: #800080">0</span><span style="color: #000000">)
  19.     {
  20.         cout </span><< <span style="color: #800000">"</span><span style="color: #800000">mysql query error with </span><span style="color: #800000">"</span> << ret << <span style="color: #800000">"</span><span style="color: #800000"> Reason: </span><span style="color: #800000">"</span> << mysql_error(&connect) <<<span style="color: #000000"> endl;
  21.         </span><span style="color: #0000ff">return</span> <span style="color: #0000ff">false</span><span style="color: #000000">;
  22.     }
  23.     showDetails(connect);
  24.     </span><span style="color: #0000ff">return</span> <span style="color: #0000ff">true</span><span style="color: #000000">;
  25. }</span>

自己编写sql语句查询：

  1. <span style="color: #0000ff">bool</span> MySQL::Query(<span style="color: #0000ff">string</span><span style="color: #000000"> cmd)
  2. {
  3.     size_t pos </span>= cmd.find(<span style="color: #800000">"</span><span style="color: #800000">select</span><span style="color: #800000">"</span><span style="color: #000000">);
  4.     </span><span style="color: #0000ff">if</span> (pos < <span style="color: #800080">0</span> || pos ><span style="color: #000000"> cmd.size())
  5.     {
  6.         sql </span>=<span style="color: #000000"> cmd;
  7.         ret </span>= mysql_query(&<span style="color: #000000">connect, sql.c_str());
  8.         </span><span style="color: #0000ff">if</span> (ret != <span style="color: #800080">0</span><span style="color: #000000">)
  9.         {
  10.             cout </span><< <span style="color: #800000">"</span><span style="color: #800000">mysql query error with </span><span style="color: #800000">"</span> << ret << <span style="color: #800000">"</span><span style="color: #800000"> Reason: </span><span style="color: #800000">"</span> << mysql_error(&connect) <<<span style="color: #000000"> endl;
  11.             </span><span style="color: #0000ff">return</span> <span style="color: #0000ff">false</span><span style="color: #000000">;
  12.         }
  13.         </span><span style="color: #0000ff">return</span> <span style="color: #0000ff">true</span><span style="color: #000000">;
  14.     }
  15.     </span><span style="color: #0000ff">else</span><span style="color: #000000">
  16.     {
  17.         ret </span>= mysql_query(&<span style="color: #000000">connect, sql.c_str());
  18.         </span><span style="color: #0000ff">if</span> (ret != <span style="color: #800080">0</span><span style="color: #000000">)
  19.         {
  20.             cout </span><< <span style="color: #800000">"</span><span style="color: #800000">mysql query error with </span><span style="color: #800000">"</span> << ret << <span style="color: #800000">"</span><span style="color: #800000"> Reason: </span><span style="color: #800000">"</span> << mysql_error(&connect) <<<span style="color: #000000"> endl;
  21.             </span><span style="color: #0000ff">return</span> <span style="color: #0000ff">false</span><span style="color: #000000">;
  22.         }
  23.         showDetails(connect);
  24.         </span><span style="color: #0000ff">return</span> <span style="color: #0000ff">true</span><span style="color: #000000">;
  25.     }
  26. }</span>

MySQL执行完查询语句后会返回一个结果集，使用showDetails()函数输出结果集：

  1. <span style="color: #0000ff">void</span><span style="color: #000000"> MySQL::showDetails(MYSQL connect)
  2. {
  3.     </span><span style="color: #0000ff">int</span> col = mysql_field_count(&<span style="color: #000000">connect);
  4.     res </span>= mysql_store_result(&<span style="color: #000000">connect);
  5.     </span><span style="color: #0000ff">if</span> (res ==<span style="color: #000000"> NULL)
  6.     {
  7.         cout </span><< <span style="color: #800000">"</span><span style="color: #800000">mysql store result error with </span><span style="color: #800000">"</span> << mysql_error(&connect) <<<span style="color: #000000"> endl;
  8.         exit(</span>-<span style="color: #800080">1</span><span style="color: #000000">);
  9.     }
  10.     fields </span>=<span style="color: #000000"> mysql_fetch_fields(res);
  11.     </span><span style="color: #0000ff">for</span> (<span style="color: #0000ff">int</span> i = <span style="color: #800080">0</span>; i < col; ++<span style="color: #000000">i)
  12.         cout </span><< setw(<span style="color: #800080">10</span>) << fields[i].name << <span style="color: #800000">"</span><span style="color: #800000"> | </span><span style="color: #800000">"</span><span style="color: #000000">;
  13.     cout </span><<<span style="color: #000000"> endl;
  14.     </span><span style="color: #0000ff">while</span> ((row =<span style="color: #000000"> mysql_fetch_row(res)))
  15.     {
  16.         </span><span style="color: #0000ff">for</span> (<span style="color: #0000ff">int</span> i = <span style="color: #800080">0</span>; i < col; ++<span style="color: #000000">i)
  17.         {
  18.             </span><span style="color: #0000ff">if</span> (row[i] ==<span style="color: #000000"> NULL)
  19.                 cout </span><< setw(<span style="color: #800080">10</span>) << <span style="color: #800000">"</span><span style="color: #800000">NULL | </span><span style="color: #800000">"</span><span style="color: #000000">;
  20.             </span><span style="color: #0000ff">else</span><span style="color: #000000">
  21.                 cout </span><< setw(<span style="color: #800080">10</span>) << row[i] << <span style="color: #800000">"</span><span style="color: #800000"> | </span><span style="color: #800000">"</span><span style="color: #000000">;
  22.         }
  23.         cout </span><<<span style="color: #000000"> endl;
  24.     }
  25. }</span>

完整代码详见GitHub：https://github.com/MasterMeng/MySQLOperate

MySQL增、删、改、查基础操作（C++）

标签：details   false   end   passwd   操作   fetch   val   数据库   rate   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！