数据库入门之常用模板汇总

69 阅读 0 评论 46 点赞

我是靠谱客的博主健忘纸飞机，最近开发中收集的这篇文章主要介绍数据库入门之常用模板汇总，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

Oracle脚本案例

-- 01 创建表空间
-- 注意表空间的路径 根据实际安装环境进行调整
CREATE TABLESPACE ts_myscott01 
    LOGGING 
    DATAFILE '/home/oracle/ts_myscott01.dbf' SIZE 10M 
    EXTENT MANAGEMENT LOCAL;

CREATE TABLESPACE ts_myscott02 
    LOGGING 
    DATAFILE '/home/oracle/ts_myscott02.dbf' SIZE 20M
    EXTENT MANAGEMENT LOCAL;

ALTER DATABASE DATAFILE '/home/oracle/ts_myscott01.dbf' AUTOEXTEND ON NEXT  10M MAXSIZE UNLIMITED;
ALTER DATABASE DATAFILE '/home/oracle/ts_myscott02.dbf' AUTOEXTEND ON NEXT  20M MAXSIZE UNLIMITED; 

commit;

-- 02 创建方案 (创建用户)
CREATE USER MYSCOTT  PROFILE DEFAULT 
    IDENTIFIED BY MYSCOTT DEFAULT TABLESPACE USERS 
    ACCOUNT UNLOCK;

-- 资源和登录权限
GRANT RESOURCE TO MYSCOTT;
GRANT create session TO MYSCOTT;


-- 03 创建表 
-- 创建部门表 并赋值
CREATE TABLE MYSCOTT.DEPT(
    DEPTNO      NUMBER(2) PRIMARY KEY,
    DNAME       VARCHAR2(14) NOT NULL,
    LOC         VARCHAR2(13)
)TABLESPACE ts_myscott01;

INSERT INTO MYSCOTT.dept VALUES(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO MYSCOTT.dept VALUES(20, 'RESEARCH', 'DALLAS');
INSERT INTO MYSCOTT.dept VALUES(30, 'SALES', 'CHICAGO');
INSERT INTO MYSCOTT.dept VALUES(40, 'OPERATIONS', 'BOSTON');

commit;

-- 创建员工表 并赋值
CREATE TABLE MYSCOTT.EMP(
    EMPNO           NUMBER(4) constraint emp_empno_pk PRIMARY KEY,
    ENAME           VARCHAR2(10) constraint emp_ename_notnull NOT NULL,
    JOB             VARCHAR2(9),
    MGR             NUMBER(4),
    HIREDATE        DATE,
    SAL             NUMBER(7,2) constraint emp_sal_check check (SAL>0),
    COMM            NUMBER(7,2),
    DEPTNO          NUMBER(2) constraint emp_deptno_fk references MYSCOTT.dept(deptno) 
)TABLESPACE ts_myscott01;

--创建索引 在新的表空间上
CREATE  INDEX MYSCOTT.IX_CAtbAuditOperInfo_OT ON MYSCOTT.EMP(ENAME) TABLESPACE ts_myscott02;


INSERT INTO MYSCOTT.EMP VALUES(7369, 'SMITH', 'CLERK', 7902, '17-12月-80', 800, NULL, 20);
INSERT INTO MYSCOTT.EMP VALUES(7499, 'ALLEN', 'SALESMAN', 7698, '20-2月 -81', 1600, 300, 30);
INSERT INTO MYSCOTT.EMP VALUES(7521, 'WARD', 'SALESMAN', 7698, '22-2月 -81', 1250, 500, 30);

INSERT INTO MYSCOTT.EMP VALUES(7566, 'JONES', 'MANAGER', 7839, '02-4月 -81', 2975, NULL, 20);
INSERT INTO MYSCOTT.EMP VALUES(7654, 'MARTIN', 'SALESMAN', 7698, '28-9月 -81', 1250, 1400, 30);
INSERT INTO MYSCOTT.EMP VALUES(7698, 'BLAKE', 'MANAGER', 7839, '01-5月 -81', 2850, NULL, 30);

INSERT INTO MYSCOTT.EMP VALUES(7782, 'CLARK', 'MANAGER', 7839, '09-6月 -81', 2450, NULL, 10);
INSERT INTO MYSCOTT.EMP VALUES(7788, 'SCOTT', 'ANALYST', 7566, '19-4月 -87', 3000, NULL, 20);
INSERT INTO MYSCOTT.EMP VALUES(7839, 'KING', 'PRESIDENT', NULL, '17-11月-81', 5000, NULL, 10);

INSERT INTO MYSCOTT.EMP VALUES(7844, 'TURNER', 'SALESMAN', 7698, '08-9月 -81', 1500, 0, 30);
INSERT INTO MYSCOTT.EMP VALUES(7876, 'ADAMS', 'CLERK', 7788, '23-5月 -87', 1100, NULL, 20);
INSERT INTO MYSCOTT.EMP VALUES(7900, 'JAMES', 'CLERK', 7698, '03-12月-81', 950, NULL, 30);

INSERT INTO MYSCOTT.EMP VALUES(7902, 'FORD', 'ANALYST', 7566, '03-12月-81', 3000, NULL, 20);
INSERT INTO MYSCOTT.EMP VALUES(7934, 'MILLER', 'CLERK', 7782, '23-1月 -82', 1300, NULL, 10);

commit;

-- 创建工资级别表 并赋值
CREATE TABLE MYSCOTT.SALGRADE(
    GRADE           NUMBER,
    LOSAL           NUMBER,
    HISAL           NUMBER
)TABLESPACE ts_myscott01;

INSERT INTO MYSCOTT.SALGRADE VALUES(1, 700, 1200);
INSERT INTO MYSCOTT.SALGRADE VALUES(2, 1201, 1400);
INSERT INTO MYSCOTT.SALGRADE VALUES(3, 1401, 2000);
INSERT INTO MYSCOTT.SALGRADE VALUES(4, 2001, 3000);
INSERT INTO MYSCOTT.SALGRADE VALUES(5, 3001, 9999);
commit;

--创建奖金表
CREATE TABLE MYSCOTT.BONUS(
    ENAME      VARCHAR2(10),
    JOB       VARCHAR2(9),
    SAL         NUMBER,
    COMM      NUMBER
)TABLESPACE ts_myscott01;

------停止-----
-- 04创建新用户方案    通过MYSCOTTUSER1来访问数据库, 权限配置演示  
CREATE USER "MYSCOTTUSER1"  PROFILE "DEFAULT" IDENTIFIED BY "123456" DEFAULT TABLESPACE "USERS" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "MYSCOTTUSER1";
GRANT SELECT ANY TABLE TO "MYSCOTTUSER1";

GRANT DELETE ON MYSCOTT.DEPT TO "MYSCOTTUSER1";
GRANT INSERT ON MYSCOTT.DEPT TO "MYSCOTTUSER1";
GRANT UPDATE ON MYSCOTT.DEPT TO "MYSCOTTUSER1";

GRANT DELETE ON MYSCOTT.EMP TO "MYSCOTTUSER1";
GRANT INSERT ON MYSCOTT.EMP TO "MYSCOTTUSER1";
GRANT UPDATE ON MYSCOTT.EMP TO "MYSCOTTUSER1";

commit;

Oracle实现命令行工具

/* 包含C头文件 */  
#include <stdio.h>  
#include <string.h>  
#include <stdlib.h>  
#include <setjmp.h>  
#include <sqlcpr.h>  

/* 包含SQLDA和SQLCA结构 */  
#include <sqlda.h>  
#include <sqlca.h>  

/* 定义绑定变量和选择列表项的最大个数 */  
#define MAX_ITEMS       40  

/* 定义绑定变量和选择列表项名称的最大长度 */  
#define MAX_VNAME_LEN   30  

/* 定义指示变量名称的最大长度 */  
#define MAX_INAME_LEN   30  

void connect();  
void sql_error();  
void alloc_descriptors(int , int , int);  
void dealloc_descriptors();  
void set_bind_variables();  
void process_select_list();  

/* 定义绑定描述区和选择描述区 */  
SQLDA* bind_dp;  
SQLDA* select_dp;  

/* 定义输入宿主变量：存放动态SQL语句 */  
char sql_stat[100];   
char current_date[20];     

int main()  
{  
    /* 安装错误处理句柄 */  
    exec sql whenever sqlerror do sql_error();  

    /* 连接到数据库 */  
    connect2();  

    /* 分配绑定描述区和选择描述区 */  
    alloc_descriptors(MAX_ITEMS , MAX_VNAME_LEN , MAX_INAME_LEN);  

    for( ; ; )  
    {  
        printf("请输入动态SQL语句(exit:退出):");  
        gets(sql_stat);  

        /* EXIT(exit):退出 */  
        if(0 == strncmp(sql_stat , "exit" , 4) || 0 == strncmp(sql_stat , "EXIT" , 4))  
            break;  

        /* 准备动态SQL语句 */  
        exec sql prepare s from :sql_stat;  

        /* 定义游标 */  
        exec sql declare c cursor for s;  

        /* 出错，继续下一循环 */  
        if(0 != sqlca.sqlcode)  
            continue;  

        /* 设置绑定变量 */  
        set_bind_variables();  

        /* 
         * 打开游标 
         * select语句：生成结果集 
         * 其他SQL语句：执行语句 
         */  
        exec sql open c using descriptor bind_dp;  

        /* 
         * select语句 
         */  
        if(0 == strncmp(sql_stat , "select" , 6) || 0 == strncmp(sql_stat , "SELECT" , 6)) 
        {  
            process_select_list();   
        }  
        /* 关闭游标 */  
        exec sql close c;  
    }  

    /* 释放选择描述区和选择描述区 */  
    dealloc_descriptors();  

    /* 提交事务，断开连接 */  
    exec sql commit work release;  
    puts("谢谢使用Oracle动态SQL方法四!n");  

    return 0;  
}  


void connect2()  
{  
    /* 定义宿主变量 */  
    char username[20] , password[20] , server[20];  

    /* 输入用户名、口令和网络服务名 */  
    printf("输入用户名：");  
    gets(username);  

    printf("输入口令：");  
    gets(password);  

    printf("输入网络服务名：");  
    gets(server);  

    /* 连接到数据库 */  
    EXEC SQL CONNECT :username identified by :password using :server;  
}  

void sql_error()  
{  
    /* 显示SQL错误信息 */  
    printf("%.*sn" , sqlca.sqlerrm.sqlerrml , sqlca.sqlerrm.sqlerrmc);  
}  

void alloc_descriptors(int size , int max_vname_len , int max_iname_len)  
{  

    int i;  

    /* 分配绑定描述区和选择描述区 */  
    bind_dp = SQLSQLDAAlloc(0 , size , MAX_VNAME_LEN , MAX_INAME_LEN);  
    select_dp = SQLSQLDAAlloc(0 , size , MAX_VNAME_LEN , MAX_INAME_LEN);  


    /* 为指示变量、绑定变量和选择列表项分配内存 */  
    for(i = 0 ; i != MAX_ITEMS ; ++i)  
    {  
        bind_dp->I[i] = (short*)malloc(sizeof(short));  
        select_dp->I[i] = (short*)malloc(sizeof(short));  

        bind_dp->V[i] = (char*)malloc(1);  
        select_dp->V[i] = (char*)malloc(1);  
    }  
}  

void dealloc_descriptors()  
{  
    int i;  

    /* 释放指示变量、绑定变量和选择列表项占用的内存 */  
    for(i = 0 ; i != MAX_ITEMS ; ++i)  
    {  
        if(bind_dp->V[i] != (char*)0)  
            free(bind_dp->V[i]);  
        free(bind_dp->I[i]);  

        if(select_dp->V[i] != (char*)0)  
            free(select_dp->V[i]);  
        free(select_dp->I[i]);  
    }  

    /* 释放绑定描述区和选择描述区 */  
    SQLSQLDAFree(0 , bind_dp);  
    SQLSQLDAFree(0 , select_dp);  
}  

void set_bind_variables()  
{  
    int i;  
    char bind_var[64];  

    /* 设置绑定变量最大个数 */  
    bind_dp->N = MAX_ITEMS;  

    /* 绑定变量名称: 绑定描述区 */  
    exec sql describe bind variables for s into bind_dp;  

    /* 设置绑定变量实际个数 */  
    bind_dp->N = bind_dp->F;  

    /* 循环处理绑定变量 */  
    for(i = 0 ; i != bind_dp->F ; ++i)  
    {  
        /* 显示绑定变量名 */  
        printf("请输入绑定变量%.*s的值：" , (int)bind_dp->C[i] , bind_dp->S[i]);  

        /* 输入绑定变量的值 */  
        gets(bind_var);  

        /* 设置绑定变量的长度成员 */  
        bind_dp->L[i] = strlen(bind_var);  

        /* 为绑定变量数据缓冲区重新分配内存(多一位，留给'') */  
        bind_dp->V[i] = (char*)realloc(bind_dp->V[i] , bind_dp->L[i] + 1);  

        /* 绑定变量数据: 数据缓冲区 */  
        strcpy(bind_dp->V[i] , bind_var);  

        /* 设置指示变量，处理NULL */  
        if(0 == strncmp(bind_var , "NULL" , 4) || 0 == strncmp(bind_var , "null" , 4))  
            *bind_dp->I[i] = -1;  
        else  
            *bind_dp->I[i] = 0;  

        /* 设置数据缓冲区数据类型代码->char */  
        bind_dp->T[i] = 1;  
    }  
}  

void process_select_list()  
{  
    int i , null_ok , precision , scale;  
    char title[MAX_VNAME_LEN];  

    /* 设置选择列表项的最大个数 */  
    select_dp->N = MAX_ITEMS;  

    /* 选择列表项: 选择描述区 */  
    exec sql describe select list for s into select_dp;  

    /* 设置选择列表项的实际个数 */  
    select_dp->N = select_dp->F;  

    /* 循环处理选择列表项 */  
    for(i = 0 ; i != select_dp->F ; ++i)  
    {  
        /* 清除select_dp->T[i]的高位->null */  
        SQLColumnNullCheck(0 , (unsigned short*)&select_dp->T[i]  
            , (unsigned short*)&select_dp->T[i] , &null_ok);  

        /* 根据内部数据类型确定外部类型数据长度(显示长度) */  
        switch(select_dp->T[i])  
        {  
        case 2:  
            /* number类型，取得精度与标度 */  
            //SQLNumberPrecV6(0 , (unsigned short*)&select_dp->T[i] , &precision , &scale); 
            SQLNumberPrecV6(0 , (unsigned long *)&select_dp->L[i] , &precision , &scale);  //wangbaoming modify 201409

            if(scale > 0)  
                /* 实数: 显示长度:float  */  
                select_dp->L[i] = sizeof(float);  
            else  
                /* 整数: 显示长度 int */  
                select_dp->L[i] = sizeof(int);  
            break;  
        case 12:  
            /* DATA数据类型(DD-MON-YY) */  
            select_dp->L[i] = 9;  
            break;  
        }  

        /* 根据变量长度，重新为选择列表项数据缓冲区分配内存 */  
        if(2 != select_dp->T[i])  
            /* 其他类型 */  
            select_dp->V[i] = (char*)realloc(select_dp->V[i] , select_dp->L[i] + 1);  
        else  
            /* number类型 */  
            select_dp->V[i] = (char*)realloc(select_dp->V[i] , select_dp->L[i]);  

        /* 初始化title */  
        memset(title , ' ' , MAX_VNAME_LEN);  

        /* 选择列表项名称: title */  
        strncpy(title , select_dp->S[i] , select_dp->C[i]);  

        /* 显示列名 */  
        if(2 == select_dp->T[i])  
            if(scale > 0)  
                printf("t%.*s" , select_dp->L[i] + 3, title);  
            else  
                printf("t%.*s" , select_dp->L[i] , title);  
        else  
            printf("t%-.*s" , select_dp->L[i] , title);  

        /* 根据Oracle内部类型确定外部数据类型(显示类型) */  
        if( 2 == select_dp->T[i])  
        {  
            /* number 类型*/  
            if(scale > 0)  
                /* float */  
                select_dp->T[i] = 4;  
            else  
                /* int */  
                select_dp->T[i] = 3;  
        }  
        else  
            /* char */  
            select_dp->T[i] = 1;  
    }  

    printf("n");  

    /* 提取数据完毕->结束循环 */  
    exec sql whenever not found do break;  

    /* 循环处理选择列表数据 */  
    for( ; ; )  
    {  
        /* 数据->选择描述区 */  
        exec sql fetch c using descriptor select_dp;  

        /* 显示数据 */  
        for( i = 0 ; i != select_dp->F ; ++i)  
        {  
            if(*select_dp->I[i] < 0){  
                /* 处理NULL */  
                printf("tNULL");  
            }else{  
                if(3 == select_dp->T[i]){  
                    /* int */  
                    printf("t%d" , *(int*)select_dp->V[i]);  
                }else if(4 == select_dp->T[i]){  
                    /* float */  
                    printf("t%8.2f" , *(float*)select_dp->V[i]);  
                }else{  
                    /* char */  
                    printf("t%.*s" , select_dp->L[i] , select_dp->V[i]);  
                }  
                }  
        }  
        printf("n");  
    }  
}

MySQL脚本案例




-- 用root用户登录系统，执行脚本

-- 创建数据库
create database mydb61;

-- 选择数据库
use mydb61;

-- 增加 dbuser1 用户
--  创建用户‘dbuser61’密码为 ‘dbuser61’拥有操作数据库mydb61的所有权限
    GRANT ALL ON mydb61.* TO dbuser61 IDENTIFIED BY "dbuser61";
    flush privileges;

-- grant select,insert,update,delete on mydb61.* to dbuser61@localhost identified by "dbuser61";
-- grant select,insert,update,delete on mydb61.* to dbuser61@'%' identified by "dbuser61";

-- 创建表

-- 创建部门表 并赋值
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`(
    `deptno`     int(2) PRIMARY KEY,
    `dname`       varchar(14) NOT NULL,
    `loc`           varchar(13)
)DEFAULT CHARSET=utf8;

INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES(30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES(40, 'OPERATIONS', 'BOSTON');

commit;

-- 注意mysql的sql语言 约束如果起名字，需要单独写在表后面
-- 创建员工表 并赋值
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp`(
    -- `empno`           int(4) constraint emp_empno_pk PRIMARY KEY,
    `empno`           int(4) PRIMARY KEY,
    `ename`             varchar(10) NOT NULL,
    `job`               varchar(9),
    `mgr`               int(4),
    `hiredate`          DATE,
    `sal`               int ,
    `comm`              int,
    `deptno`            int(2) ,
    constraint emp_deptno_fk  foreign key(deptno) references dept(deptno)
)DEFAULT CHARSET=utf8;

--创建索引 
-- CREATE  INDEX MYSCOTT.IX_CAtbAuditOperInfo_OT ON MYSCOTT.EMP(ENAME) TABLESPACE ts_myscott2;
create index emp_ename_index on emp(ename);

--注意 日期格式不一样
INSERT INTO emp VALUES(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20);
INSERT INTO emp VALUES(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
-- INSERT INTO emp(empno, ename, job, mgr, hiredate, sal,  deptno) VALUES(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 30);
INSERT INTO emp VALUES(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);            
INSERT INTO emp VALUES(7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20);
INSERT INTO emp VALUES(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
INSERT INTO emp VALUES(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30);

INSERT INTO emp VALUES(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10);
INSERT INTO emp VALUES(7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, NULL, 20);
INSERT INTO emp VALUES(7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);

INSERT INTO emp VALUES(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
INSERT INTO emp VALUES(7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, NULL, 20);
INSERT INTO emp VALUES(7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30);

INSERT INTO emp VALUES(7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20);
INSERT INTO emp VALUES(7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);
commit;

-- 创建工资级别表 并赋值
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade`(
    `grade`         int,
    `losal`           int,
    `hisal`           int
)DEFAULT CHARSET=utf8;

INSERT INTO salgrade VALUES(1, 700, 1200);
INSERT INTO salgrade VALUES(2, 1201, 1400);
INSERT INTO salgrade VALUES(3, 1401, 2000);
INSERT INTO salgrade VALUES(4, 2001, 3000);
INSERT INTO salgrade VALUES(5, 3001, 9999);
commit;

--创建奖金表
DROP TABLE IF EXISTS `bonus`;
CREATE TABLE bonus(
    `ename`      VARCHAR(10),
    `job`       VARCHAR(9),
    `sal`       int,
    `comm`      int
)DEFAULT CHARSET=utf8;

commit;

MySQL实现命令行工具



#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <dlfcn.h>
#include <mysql/mysql.h>

#include <stdio.h>
#include <stdlib.h>
#include <unistd.h>
#include <string.h>
#include <errno.h>
#include <termios.h>
#include <mysql/mysql.h>

#include <termios.h>




struct termios oldterm;
void setstty2()//设置输入退格键，不回显
{
    //system("stty erase ^H");//执行shell命令，也可以 用来设置读取用户键盘输入的时候，退格键不回显

    struct termios term;
    if(tcgetattr(STDIN_FILENO, &term) == -1)//得到系统termion的设置
    {
        printf("tcgetattr error is %sn", strerror(errno));
        return;
    }

    oldterm = term;//保留当前termios设置，以便程序退出的时候可以恢复termios

    /*
    term.c_lflag &= ~ICANON;//取消ICANON选项（不规范输入）
    term.c_lflag |= ICANON;//设置ICANON选项(规范输入)
    term.c_cc字段为要设置的具体特殊输入字符，如c_cc[VERASE]代表退格键,
    term.c_cc[VERASE] = 'b';意思为把退格键修改为'b'
    VERASE代表向前擦出一个字符,VINTR代表发送ctrl + C中断信号，ctrl + c的ASCII码为3
    例如：term.c_cc[VINTR] = 't';意思为将tab键设置为终端信号
    tcsetattr中，第二个参数说明，TCSAFLUSH：发送了所有输出后更改才生效，在更改发生时，未读取的所有输入数据都被删除
    TCSANOW:更改立即生效
    TCSADRAIN:发送了所有输出后更改才发生，如果更改输出参数则应该使用该选项
    */
    term.c_cc[VERASE] = 'b';//'b'为退格键的ASCII码
    if (tcsetattr(STDIN_FILENO, TCSANOW, &term) == -1)//设置系统termion
    {
        printf("tcsetattr error is %sn", strerror(errno));
    }
    return;
}

void setstty()//设置输入退格键，不回显
{
    system("stty erase ^H");//执行shell命令，也可以 用来设置读取用户键盘输入的时候，退格键不回显

}

void returnstty()//恢复系统的termios设置
{
    if (tcsetattr(STDIN_FILENO, TCSAFLUSH, &oldterm) == -1)//设置系统termion
    {
        printf("tcsetattr error is %sn", strerror(errno));
    }
    return;
}


int main(int arg, char *args[])
{
    int             ret = 0, i=0;
    MYSQL           mysql;
    MYSQL           *connect;
    MYSQL_RES       *result;
    MYSQL_ROW       row;
    MYSQL_FIELD     *fields;
    unsigned int    num_fields;

    if (arg < 4)
    {
        printf("please enter: %s localhost user password dbnamen", args[0]);
        return -1;
    }

    setstty();//设置输入退格键，不回显

    mysql_init(&mysql);

    //连接到mysql server
    connect = mysql_real_connect(&mysql, args[1], args[2], args[3], args[4],0, 0, 0);
    if (connect == NULL)
    {
        printf("connect error, %sn", mysql_error(&mysql));
        return -1;
    }

    ret = mysql_query(connect, "SET NAMES utf8");       //设置字符集为UTF8
    if (ret != 0)
    {
        printf("设置字符集错误, %sn", mysql_error(&mysql));
        return ret;
    }

    char buf[4096];
    for( ; ; )  
    {  
        memset(buf, 0, sizeof(buf));
        //strcpy(buf, "mysql>");
        //write(STDOUT_FILENO, buf, strlen(buf));
        //memset(buf, 0, sizeof(buf));
        //read(STDIN_FILENO, buf, sizeof(buf));

        printf("nmysql>");
        gets(buf);  

        /* EXIT(exit):退出 */  
        if ( 0 == strncmp(buf , "exit" , 4) || 0 == strncmp(buf , "EXIT" , 4) ||
             0 == strncmp(buf , "quit" , 4) || 0 == strncmp(buf , "QUIT" , 4) ) 
        {
            break;
        }    

        //printf("buf:%s n", buf);
         ret = mysql_query(connect, buf); 
         if (ret != 0 )
         {
            printf("func mysql_query() err: %s", mysql_error(&mysql) );
            continue;
         }
         else
         {
            //printf("okn");
         }

         if ( (strncmp(buf, "select", 6) == 0) || (strncmp(buf, "SELECT", 6) == 0) ||
              (strncmp(buf, "show", 4) == 0) || (strncmp(buf, "SHOW", 4) == 0) ||
              (strncmp(buf, "desc", 4) == 0) || (strncmp(buf, "DESC", 4) == 0) )
         {
                //获取查询结果
                result = mysql_store_result(&mysql); 

                //获取列表头信息信息
                fields = mysql_fetch_fields(result);
                num_fields = mysql_num_fields(result);

                for (i=0; i<num_fields; i++)
                {
                   printf("%st", fields[i].name);
                }
                printf("n");

                //按照行获取数据 检索结果集的下一行。
                while(row = mysql_fetch_row(result))
                {
                    for (i=0; i<mysql_num_fields(result); i++)
                    {
                        printf("%st ",row[i]);
                    }
                    printf("n");
                }

                mysql_free_result(result);     //free result after you get the result
         }
         else
         {
            //printf("")mysql_affected_rows(connect); 
         }
    }   

     mysql_close(connect);      //断开与SQL server的连接
    // returnstty();//恢复系统的termios设置

    return 0;
}