mysql实验手册

一．Mysql操作基础

1.测试服务器

$ mysql –u root mysql

如果得出mysql>提示符，服务器就正常运行了。

2.修改管理员密码:

$ mysqladmin –u root password newpassword

3.登入管理员帐号

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 failed\n”);

return EXIT_FAILURE;

}

conn_ptr = mysql_real_connect(conn_ptr, “localhost”, “neusoft”, “neusoft”, “neusoftdb”, 0, NULL, 0);

if (conn_ptr)

{

printf(“Connection success\n”);

}

else

{

printf(“Connection failed\n”);

}

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 success\n”);

mysql_close(&my_connection);

}

else

{

fprintf(stderr, “Connection failed\n”);

if (mysql_errno(&my_connection))

{

fprintf(stderr, “Connection error %d: %s\n”, mysql_errno(&my_connection), mysql_error(&my_connection));

}

}

return EXIT_SUCCESS;

}

执行：

$ ./connect2

Connection filed

Connection error 1045: Access denied for user:

…neusoft@localhost? (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 success\n”);

res = mysql_query(&my_connection, “INSERT INTO children(fname, age) V ALUES(…Ann?, 3)”);

if (!res)

{

printf(“Inserted %lu rows\n”, (unsigned long)mysql_affected_rows(&my_connection));

}

else

{

fprintf(stderr, “Insert error %d: %s\n”, mysql_errno(&my_connection), mysql_error(&my_connection));

}

mysql_close(&my_connection);

}

else

{

fprintf(stderr, “Connection failed\n”);

if (mysql_errno(&my_connection))

{

fprintf(stderr, “Connection error %d: %s\n”, 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 success\n”);

res = mysql_query(&my_connection, “INSERT INTO children(fname, age) V ALUES(…Robert?, 8)”);

if (!res)

{

printf(“Inserted %lu rows\n”, (unsigned long) mysql_affected_rows(&my_connection));

}

else

{

fprintf(stderr, “Insert error %d: %s\n”, mysql_errno(&my_connection), mysql_error(&my_connection));

}

res = mysql_query (&my_connection, “SELECT LAST_INSERT_ID()”);

if (res)

{

printf(“SELECT error: %s\n”, 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 %s\n”, sqlrow[0]);

}

mysql_free_result(res_ptr);

}

}

mysql_close(&my_connection);

}

else

{

fprintf(stderr, “Connection failed\n”);

if (mysql_errno(&my_connecton))

{

fprintf(stderr, “Connection error %d: %s\n”, mysql_errno(&my_connection), mysql_error(&my_connection));

}

}

return EXIT_SUCCESS;

}

编译：

$ gcc –I/usr/include/mysql insert2.c –lmysqlclient –o insert2

$ ./insert2

Connection success

Inserted 1 rows

We inserted childNo 3

$./insert2

Connection success

Inserted 1 rows

We inserted childNo 4

5．提取数据

例程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 success\n”);

res = mysql_query(&my_connection, “SELECT childNo, fname, age FROM children WHERE age > 5”);

if (res)

{

printf(“SELECT error: %s\n”, mysql_error(&my_connection));

}

else

{

res_ptr = mysql_store_result(&my_connection);

if (res_ptr)

{

printf(“Retrieved %lu rows\n”, (unsigned long) mysql_num_rows(res_ptr));

while ((sqlrow = mysql_fetch_row(res_ptr)))

{

printf(“Fetched data…\n”);

}

if (mysql_errno(&my_connection))

{

fprintf(stderr, “Retrive error: %s\n”, mysql_error(&my_connection));

}

}

mysql_free_result(res_ptr);

}

mysql_close(&my_connection);

}

else

{

fprintf(stderr, “Connection failed\n”);

if (mysql_errno(&my_connection))

{

fprintf(stderr, “Connection error %d: %s\n”, 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 success\n”);

res = mysql_query(&my_connection, “SELECT childNo, fname, age FROM children WHERE age > 5”);

if (res)

{

fprintf(stderr, “SELECT error: %s\n”, 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: %s\n”, mysql_error(&my_connection));

}

}

mysql_free_result(res_ptr);

}

mysql_close(&my_connection);

}

else

{

fprintf(stderr, “Connection failed\n”);

if (mysql_errno(&my_connection))

{

fprintf(stderr, “Connection error %d: %s\n”,mysql_errno(&my_connection),

mysql_error(&my_connection));

}

}

return EXIT_SUCCESS;

}

void display_header()

{

MYSQL_FIELD *field_ptr;

printf(“Colum details:\n”);

while ((field_ptr = mysql_fetch_field(res_ptr)) != NULL)

{

printf(“\t Name: %s\n”, field_ptr->name);

printf(“\t Type: “);

if (IS_NUM(field_ptr->type))

{

printf(“Numeric field\n”);

}

else

{

switch(field_ptr->type){

case FIELD_TYPE_V AR_STRING:

printf("V ARCHAR\n");

break;

case FIELD_TYPE_LONG:

printf("LONG\n");

break;

default:

printf("Type is %d,check in mysql_com.h\n",field_ptr->type);

}

}

printf("\t Max width %ld\n",field_ptr->length);

if(field_ptr->flags & AUTO_INCREMENT_FLAG)

printf("\t Auto increments\n");

printf("\n");

}

}

void display_row(){

unsigned int field_count;

field_count =0;

while(field_count < mysql_field_count(&my_connection)){ if(sqlrow[field_count])

{

printf("%s ",sqlrow[field_count]);

}

else

{

printf("NULL");

}

field_count++;

}

printf("\n");

}