mysql c 编程实例_mysql-基本命令-c编程实例

1，基本命令：

(说明：数据库名test，表名player_info，列名nickname，total_score(score))

show databases; #查看系统中的数据库

create database test; #新建数据库test

use test; #选中数据库test，以后的操作就是针对test的了

show tables; #查看数据库test中的表

create table player_info (nickname varchar(10) not null primary key, total_score int not null default 0); #在数据库test中新建一张表player_info，包含nickname和total_score两列

describe player_info;

insert into player_info values('user0',''); #插入

select * from player_info where nickname='user0'; #查询

update player_info set total_score='100' where nickname='user0'; #更新

把nickname.txt(每行一个用户名)拷贝到/var/lib/mysql/目录下，然后执行：load data infile "./nickname.txt" into table player_info; #导入外部文件中的数据到表中

alter table player_info modify column nickname varchar(10) binary; #将nickname列修改为区分大小写的

alter table player_info change total_score score int; #将列名total_score修改为score

/etc/init.d/mysqld status/start/stop #查看mysqld的状态；运行；停止

#查看mysqld是否在系统启动时自动运行；修改

chkconfig --list

chkconfig --level 3 mysqld on

chkconfig --level 5 mysqld on

2，c语言编程：

#include

#include

int main()

{

MYSQL mysql;

mysql_init(&mysql);

char szDBServer[] = "localhost";

char szUsr[] = "root";

char szPasswd[] = "";

char szDBName[] = "test";

//mysql_options();

MYSQL *pMysqlCon =

mysql_real_connect(&mysql, szDBServer, szUsr, szPasswd,

szDBName, 0,

NULL, 0);

if (NULL == pMysqlCon)

{

printf("%s/n", mysql_error(&mysql));

return 1;

}

printf("host info: %s/n", mysql_get_host_info(pMysqlCon));

printf("mysql server info: %s(%lu)/n",

mysql_get_server_info(pMysqlCon),

mysql_get_server_version(pMysqlCon));

printf("mysql client info: %s(%lu)/n", mysql_get_client_info(),

mysql_get_client_version());

printf("mysql protocol: %u/n", mysql_get_proto_info(pMysqlCon));

MYSQL_RES *pDBs = mysql_list_dbs(pMysqlCon, NULL);

if (NULL == pDBs)

{

printf("%s/n", mysql_error(pMysqlCon));

mysql_close(pMysqlCon);

return 1;

}

MYSQL_ROW db;

while ((db = mysql_fetch_row(pDBs)) != NULL)

{

printf("%s: ", db[0]);

mysql_select_db(pMysqlCon, db[0]);

MYSQL_RES *pTbls = mysql_list_tables(pMysqlCon, NULL);

if (NULL == pTbls)

{

printf("%s/n", mysql_error(pMysqlCon));

mysql_free_result(pDBs);

mysql_close(pMysqlCon);

return 1;

}

MYSQL_ROW tbl;

while ((tbl = mysql_fetch_row(pTbls)) != NULL)

{

printf("%s ", tbl[0]);

}

printf("/n");

mysql_free_result(pTbls);

}

mysql_free_result(pDBs);

char szStatement[] = "select * from player_info";

//mysql_real_query(); //for szStatements that contain binary data may contain the “/0” character,

int nState = mysql_query(pMysqlCon, szStatement);

if (nState != 0)

{

printf("%s/n", mysql_error(pMysqlCon));

mysql_close(pMysqlCon);

return 1;

}

MYSQL_RES *pRes = mysql_store_result(pMysqlCon);

//mysql_use_result();

if (NULL == pRes)

{

printf("%s/n", mysql_error(pMysqlCon));

mysql_close(pMysqlCon);

return 1;

}

// mysql_pField_count();

my_ulonglong nRecords = mysql_num_rows(pRes);

unsigned int nFields = mysql_num_fields(pRes);

MYSQL_ROW row;

for (my_ulonglong i = 0; i < nRecords; i++)

{

row = mysql_fetch_row(pRes);

for (unsigned int j = 0; j < nFields; j++)

{

printf("%s", row[j]);

(j != nFields - 1) ? printf(", ") : printf("/n");

}

}

MYSQL_FIELD *pField;

while ((pField = mysql_fetch_field(pRes)) != NULL)

{

printf("name: %s, ", pField->name);

printf("size: %lu, ", pField->length);

printf("type: %d, ", pField->type);

printf("max width: %lu, ", pField->max_length);

printf("null allowed: %s, ",

IS_NOT_NULL(pField->flags) ? "no" : "yes");

printf("primary key: %s/n",

IS_PRI_KEY(pField->flags) ? "yes" : "no");

}

mysql_free_result(pRes);

char szStatement2[] =

"update player_info set score='0' where nickname='user0'";

nState = mysql_query(pMysqlCon, szStatement2);

if (nState != 0)

{

printf("%s/n", mysql_error(pMysqlCon));

mysql_close(pMysqlCon);

return 1;

}

printf("%llu rows affected/n",

(unsigned long long) mysql_affected_rows(pMysqlCon));

//mysql_errno()

//mysql_debug()

//mysql_dump_debug_info()

mysql_close(pMysqlCon);

return 0;

}

//g++ t.cpp -o t -g -Wall -O -I/usr/include/mysql -L/usr/lib/mysql -lmysqlclient