MySQL数据插入

MySQL数据插入

向MySQL数据表插入数据通用的 INSERT INTO SQL语法：

INSERT INTO table_name ( field1, field2,...fieldN )

VALUES

( value1, value2,...valueN );

如果数据是字符型，必须使用单引号或者双引号，如："value"。

代码实操：

MYSQL* mysql = new MYSQL();

//MYSQL mysql;//占用的是栈上的内存

MYSQL* pDB = mysql_init(mysql);

if (pDB == NULL) {

std::cout << "mysql_init failed!" << std::endl;

return -1;

}

pDB = mysql_real_connect(pDB, "localhost", "root", "FengPan12#$56", "mysql", 3306, NULL, 0);

std::cout << pDB << std::endl;

if (pDB) {

std::string sql = "CREATE DATABASE hello";

int ret = mysql_real_query(pDB, sql.c_str(), (unsigned long)sql.size());

if (ret != 0) {

std::cout << "mysql error:" << mysql_error(pDB) << std::endl;

}

MYSQL_RES* result = mysql_use_result(mysql);

if (result != NULL) {

show_result(result);

std::cout << "===================================================" << std::endl;

mysql_free_result(result);

}

//授予权限

sql = "GRANT ALL ON hello.* TO 'hello'@'localhost';";

ret = mysql_real_query(pDB, sql.c_str(), (unsigned long)sql.size());

if (ret != 0) {

std::cout << "mysql error:" << mysql_error(pDB) << std::endl;

return -1;

}

result = mysql_use_result(mysql);

if (result != NULL) {

show_result(result);

std::cout << "===================================================" << std::endl;

mysql_free_result(result);

}

sql = "USE hello;";

ret = mysql_real_query(pDB, sql.c_str(), (unsigned long)sql.size());

if (ret != 0) {

std::cout << "mysql error:" << mysql_error(pDB) << std::endl;

return -1;

}

result = mysql_use_result(mysql);

if (result != NULL) {

show_result(result);

std::cout << "===================================================" << std::endl;

mysql_free_result(result);

}

sql = "CREATE TABLE IF NOT EXISTS `hello` (`编号` NVARCHAR(16) PRIMARY KEY)ENGINE=InnoDB DEFAULT CHARSET=utf8;";

ret = mysql_real_query(pDB, sql.c_str(), (unsigned long)sql.size());

if (ret != 0) {

std::cout << "mysql error:" << mysql_error(pDB) << std::endl;

return -1;

}

sql = "INSERT INTO `hello` (`编号`) VALUES (\"9527\");";

ret = mysql_real_query(pDB, sql.c_str(), (unsigned long)sql.size());

if (ret != 0) {

std::cout << "mysql error:" << mysql_error(pDB) << std::endl;

return -1;

}

sql = "DROP TABLE `hello`;";

ret = mysql_real_query(pDB, sql.c_str(), (unsigned long)sql.size());

if (ret != 0) {

std::cout << "mysql error:" << mysql_error(pDB) << std::endl;

return -1;

}

sql = "DROP DATABASE hello;";

ret = mysql_real_query(pDB, sql.c_str(), (unsigned long)sql.size());

if (ret != 0) {

std::cout << "mysql error:" << mysql_error(pDB) << std::endl;

return -1;

}

mysql_close(pDB);

}

delete mysql;

return 0;