#include
#include
#include
#include
void add_student(MYSQL *conn, const char *name, int score) {
MYSQL_STMT *stmt;
MYSQL_BIND bind[2];
// 准备SQL语句
stmt = mysql_stmt_init(conn);
if (stmt == NULL) {
fprintf(stderr, "mysql_stmt_init() failedn");
return;
}
const char *query = "INSERT INTO students (name, score) VALUES (?, ?)";
if (mysql_stmt_prepare(stmt, query, strlen(query)) != 0) {
fprintf(stderr, "mysql_stmt_prepare() failedn");
mysql_stmt_close(stmt);
return;
}
// 绑定参数
memset(bind, 0, sizeof(bind));
bind[0].buffer_type = MYSQL_TYPE_STRING;
bind[0].buffer = (char *)name;
bind[0].buffer_length = strlen(name);
bind[1].buffer_type = MYSQL_TYPE_LONG;
bind[1].buffer = (char *)&score;
if (mysql_stmt_bind_param(stmt, bind) != 0) {
fprintf(stderr, "mysql_stmt_bind_param() failedn");
mysql_stmt_close(stmt);
return;
}
// 执行查询
if (mysql_stmt_execute(stmt) != 0) {
fprintf(stderr, "mysql_stmt_execute() failedn");
} else {
printf("Student added successfullyn");
}
// 关闭SQL语句
mysql_stmt_close(stmt);
}
void query_students(MYSQL *conn) {
MYSQL_RES *res;
MYSQL_ROW row;
// 执行查询
if (mysql_query(conn, "SELECT * FROM students")) {
fprintf(stderr, "SELECT * FROM students failed. Error: %sn", mysql_error(conn));
return;
}
// 获取查询结果
res = mysql_store_result(conn);
if (res == NULL) {
fprintf(stderr, "mysql_store_result() failed. Error: %sn", mysql_error(conn));
return;
}
// 处理查询结果
int num_fields = mysql_num_fields(res);
while ((row = mysql_fetch_row(res))) {
for (int i = 0; i < num_fields; i++) {
printf("%s ", row[i] ? row[i] : "NULL");
}
printf("n");
}
// 释放结果集
mysql_free_result(res);
}
void delete_student(MYSQL *conn, int id) {
MYSQL_STMT *stmt;
MYSQL_BIND bind[1];
// 准备SQL语句
stmt = mysql_stmt_init(conn);
if (stmt == NULL) {
fprintf(stderr, "mysql_stmt_init() failedn");
return;
}
const char *query = "DELETE FROM students WHERE id = ?";
if (mysql_stmt_prepare(stmt, query, strlen(query)) != 0) {
fprintf(stderr, "mysql_stmt_prepare() failedn");
mysql_stmt_close(stmt);
return;
}
// 绑定参数
memset(bind, 0, sizeof(bind));
bind[0].buffer_type = MYSQL_TYPE_LONG;
bind[0].buffer = (char *)&id;
if (mysql_stmt_bind_param(stmt, bind) != 0) {
fprintf(stderr, "mysql_stmt_bind_param() failedn");
mysql_stmt_close(stmt);
return;
}
// 执行查询
if (mysql_stmt_execute(stmt) != 0) {
fprintf(stderr, "mysql_stmt_execute() failedn");
} else {
printf("Student deleted successfullyn");
}
// 关闭SQL语句
mysql_stmt_close(stmt);
}
int main() {
MYSQL *conn;
// 初始化MySQL对象
conn = mysql_init(NULL);
if (conn == NULL) {
fprintf(stderr, "mysql_init() failedn");
exit(EXIT_FAILURE);
}
// 连接到数据库
if (mysql_real_connect(conn, "localhost", "username", "password", "database_name", 0, NULL, 0) == NULL) {
fprintf(stderr, "mysql_real_connect() failedn");
mysql_close(conn);
exit(EXIT_FAILURE);
}
// 添加学生
add_student(conn, "Alice", 90);
add_student(conn, "Bob", 85);
// 查询学生
query_students(conn);
// 删除学生
delete_student(conn, 1);
// 查询学生
query_students(conn);
// 关闭数据库连接
mysql_close(conn);
exit(EXIT_SUCCESS);
}