mysql实验手册.doc

一Mysql操作基础1. 测试服务器$ mysql u root mysql如果得出mysql提示符，服务器就正常运行了。2. 修改管理员密码:$ mysqladmin u root password newpassword3. 登入管理员帐号4. 创建新用户 创建来自192.168.181网段的登入 5创建并使用数据库6.创建表格7. 添加信息到表格里二. c语言访问mysql数据库1.以用户名neusoft和密码neusoft来连接本机服务器上名为neusoftdb的数据库 例程1：#include #include #include “mysql.h”int main(int argc, char *argv)MYSQL *conn_ptr;conn_ptr = mysql_init(NULL);if (!conn_ptr) fprintf(stderr, “mysql_init failedn”); return EXIT_FAILURE;conn_ptr = mysql_real_connect(conn_ptr, “localhost”, “neusoft”, “neusoft”, “neusoftdb”, 0, NULL, 0);if (conn_ptr) printf(“Connection successn”);else printf(“Connection failedn”);mysql_close(conn_ptr);return EXIT_SUCCESS;编译命令为 $gcc I /usr/include/mysql connect1.c lmysqlclient o connect1执行： $ ./connect1 Connection success $2. 反馈连接失败错误例程2：#include #include #include “mysql.h”int main(int argc, char *argv) MYSQL my_connection; mysql_init(&my_connection); if (mysql_real_connect(&my_connection, “localhost”, “neusoft”, “ “, “neusoftdb”, 0, NULL, 0) printf(“Connection successn”); mysql_close(&my_connection);else fprintf(stderr, “Connection failedn”); if (mysql_errno(&my_connection) fprintf(stderr, “Connection error %d: %sn”, mysql_errno(&my_connection), mysql_error(&my_connection); return EXIT_SUCCESS;执行：$ ./connect2Connection filedConnection error 1045: Access denied for user: neusoftlocalhost (Using password: YES)$3. 插入数据操作#include #include #include “mysql.h”int main(int argc, char *argv)MYSQL my_connection;int res;mysql_init(&my_connection);if (mysql_real_connect(&my_connection, “localhost”, “neusoft”, “neusoft”, “neusoftdb”, 0, NULL, 0) printf(“Connection successn”); res = mysql_query(&my_connection, “INSERT INTO children(fname, age) VALUES(Ann, 3)”); if (!res) printf(“Inserted %lu rowsn”, (unsigned long)mysql_affected_rows(&my_connection); else fprintf(stderr, “Insert error %d: %sn”, mysql_errno(&my_connection), mysql_error(&my_connection); mysql_close(&my_connection);else fprintf(stderr, “Connection failedn”); if (mysql_errno(&my_connection) fprintf(stderr, “Connection error %d: %sn”, mysql_errno(&my_connection), mysql_error(&my_connection); return EXIT_SUCCESS;4.查找插入数据内容 例程4：#include #include #include “mysql.h”int main(int argc, char *argv) MYSQL my_connection; MYSQL_RES *res_ptr; MYSQL_ROW sqlrow; int res; mysql_init(&my_connection); if (mysql_real_connect(&my_connection, “localhost”, “neusoft”, “neusoft”,”neusoftdb”, 0, NULL, 0) printf(“Connection successn”); res = mysql_query(&my_connection, “INSERT INTO children(fname, age) VALUES(Robert, 8)”); if (!res) printf(“Inserted %lu rowsn”, (unsigned long) mysql_affected_rows(&my_connection); else fprintf(stderr, “Insert error %d: %sn”, mysql_errno(&my_connection), mysql_error(&my_connection); res = mysql_query (&my_connection, “SELECT LAST_INSERT_ID()”); if (res) printf(“SELECT error: %sn”, mysql_error(&my_connection); else res_ptr = mysql_use_result(&my_connection); if (res_ptr) while (sqlrow = mysql_fetch_row(res_ptr) printf(“We inserted childNo %sn”, sqlrow0); mysql_free_result(res_ptr); mysql_close(&my_connection);else fprintf(stderr, “Connection failedn”); if (mysql_errno(&my_connecton) fprintf(stderr, “Connection error %d: %sn”, mysql_errno(&my_connection), mysql_error(&my_connection); return EXIT_SUCCESS;编译：$ gcc I/usr/include/mysql insert2.c lmysqlclient o insert2$ ./insert2Connection successInserted 1 rowsWe inserted childNo 3$./insert2Connection success Inserted 1 rowsWe inserted childNo 45提取数据例程5：#include #include #include “mysql.h”MYSQL my_connection;MYSQL_RES *res_ptr;MYSQL_ROW sqlrow;int main(int argc, char *argv)int res;mysql_init(&my_connection);if (mysql_real_connect(&my_connection, “localhost”, “neusoft”, “neusoft”, “neusoftdb”, 0, NULL, 0) printf(“Connection successn”);res = mysql_query(&my_connection, “SELECT childNo, fname, age FROM children WHERE age 5”);if (res) printf(“SELECT error: %sn”, mysql_error(&my_connection); else res_ptr = mysql_store_result(&my_connection); if (res_ptr) printf(“Retrieved %lu rowsn”, (unsigned long) mysql_num_rows(res_ptr); while (sqlrow = mysql_fetch_row(res_ptr) printf(“Fetched datan”); if (mysql_errno(&my_connection) fprintf(stderr, “Retrive error: %sn”, mysql_error(&my_connection); mysql_free_result(res_ptr);mysql_close(&my_connection);else fprintf(stderr, “Connection failedn”); if (mysql_errno(&my_connection) fprintf(stderr, “Connection error %d: %sn”, mysql_errno(&my_connection), mysql_error(&my_connection); retrun EXIT_SUCCESS;6. 显示数据#include #include #include “mysql.h”MYSQL my_connection;MYSQL_RES *res_ptr;MYSQL_ROW sqlrow;void display_header();void display_row();int main(int argc, char *argv)int res;int first_row = 1;mysql_init(&my_connection);if (mysql_real_connect(&my_connection, “localhost”, “neusoft”, “neusoft”, “neusoftdb”, 0, NULL, 0) printf(“Connection successn”); res = mysql_query(&my_connection, “SELECT childNo, fname, age FROM children WHERE age 5”); if (res) fprintf(stderr, “SELECT error: %sn”, mysql_error(&my_connection); else res_ptr = mysql_use_result(&my_connection); if (res_ptr) display_header(); while (sqlrow = mysql_fetch_row(res_ptr) if (first_row) display_header(); first_row = 0; display_row(); if (mysql_errno(&my_connection) fprintf(stderr, “Retrive error: %sn”, mysql_error(&my_connection); mysql_free_result(res_ptr); mysql_close(&my_connection); else fprintf(stderr, “Connection failedn”); if (mysql_errno(&my_connection) fprintf(stderr, “Connection error %d: %sn”,mysql_errno(&my_connection), mysql_error(&my_connection); re