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 <stdio.h>
#include <mysql.h>

 

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