删除数据库

/*****************************************************
copyright (C), 2014-2015, Lighting Studio. Co.,     Ltd. 
File name：
Author：Jerey_Jobs    Version:0.1    Date: 
Description：
Funcion List: 
*****************************************************/

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>

void create_table(sqlite3 *pdb)
{
    char * errmsg = NULL;
    char * sql;
    int ret;

    sql = "create table if not exists mytable (id integer primary key,name text);";//创建表,if not exists,不存在创建

    ret = sqlite3_exec(pdb,sql,NULL,NULL,&errmsg);

     if(SQLITE_OK != ret)//打开失败
    {
        printf("create table  error! %s\n",errmsg);//打开失败，分析错误原因
        exit(-1);
    }
}

void insert_record(sqlite3 *pdb)
{
    char * errmsg = NULL;
    char  sql[100];
    int ret;
    int id;
    char name[20];

    printf("please input id and name!\n");
    scanf("%d%s",&id,name);

    sprintf(sql,"insert into mytable (id,name) values (%d,'%s');",id,name);

 //   sql = "insert into mytable (id,name) values (NULL,'chen');";//插入

    ret = sqlite3_exec(pdb,sql,NULL,NULL,&errmsg);

     if(SQLITE_OK != ret)//打开失败
    {
        printf("insert record1 error! %s\n",errmsg);//插入失败，分析错误原因
        exit(-1);
    }
#if 0
    sql = "insert into mytable (id,name) values (NULL,'zhang');";//创建表

    ret = sqlite3_exec(pdb,sql,NULL,NULL,&errmsg);

     if(SQLITE_OK != ret)//失败
    {
        printf("insert record3 error! %s\n",errmsg);//，分析错误原因
        exit(-1);
    }

    sql = "insert into mytable (id,name) values (NULL,'liu');";//

    ret = sqlite3_exec(pdb,sql,NULL,NULL,&errmsg);

     if(SQLITE_OK != ret)//失败
    {
        printf("insert record3 error! %s\n",errmsg);//分析错误原因
        exit(-1);
    }
#endif
}

/*回调函数*/
int displaycb(void * para,int col,char *col_value[],char *col_name[])//后面两个是指针数组
{
    int i;

    int * ptr = NULL;//定义整形指针

    ptr = (int *)para;

    if(0 == *ptr )//列数和表头只显示一次
    {
        *ptr = 1;

        printf("total column is : %d\n",col);//有多少列

        for(i = 0;i < col;i++)
        {   
            printf("%10s",col_name[i]);
        }
        printf("\n**************************************\n");
    }

 /*   printf("total column is : %d\n",col);//有多少列

    for(i = 0;i < col;i++)
    {
        printf("%10s",col_name[i]);
    }
    printf("\n");
*/
    for(i = 0;i< col;i++)
    {
        printf("%10s",col_value[i]);
    }
    printf("\n");

    return 0;//return SQLITE_OK;
}


void inquire_usecb(sqlite3 * pdb)
{
    char * errmsg = NULL;
    char * sql;
    int ret;
    int flag = 0;

    sql = "select * from mytable;";//查询

    ret = sqlite3_exec(pdb,sql,displaycb,(void *)&flag,&errmsg);//回调函数名字为displaycb

     if(SQLITE_OK != ret)//失败
    {
        printf("select use call back function error! %s\n",errmsg);//查询失败，分析错误原因
        exit(-1);
    }
}

void delete_record(sqlite3 * pdb)
{
    char * errmsg = NULL;
    char sql[100];
    int ret;
    int id_del;

    printf("please input id to delete:\n");
    scanf("%d",&id_del);

    sprintf(sql,"delete from mytable where id = %d;",id_del);//删除

    ret = sqlite3_exec(pdb,sql,NULL,NULL,&errmsg);

     if(SQLITE_OK != ret)//打开失败
    {
        printf("delete recorderror! %s\n",errmsg);//删除失败，分析错误原因
        exit(-1);
    }
}

void inquire_nocb(sqlite3 * pdb)
{
    int nrow,ncol;
    char **result;//不知道大小，所以不能数组，要指针
    char * sql;
    char *errmsg;
    int i;
    int ret;

    sql = "select * from mytable;";
    
    ret = sqlite3_get_table(pdb,sql,&result,&nrow,&ncol,&errmsg);

    if(SQLITE_OK != ret)//失败
    {
        printf("get table error! %s\n",errmsg);//失败，分析错误原因
        exit(-1);
    }

    printf("row = %d,column = %d\n",nrow,ncol);//告诉你多少行多少列

    printf("the result is:\n");

    for(i = 0;i < (nrow + 1) * ncol;i++)//显示表头
   // for(i = ncol;i < nrow * ncol;i++)//显示的是记录,0的话应该+1,现在这个是不带表头的
    {
        printf("%10s",result[i]);

        if((i + 1) % ncol == 0)//换行
        {
            printf("\n");
        }
    }
    
    sqlite3_free_table(result);
}

int main()
{   
    sqlite3 *pdb = NULL;
    int ret;

    ret = sqlite3_open("mydatabase.db",&pdb);//打开

    if(SQLITE_OK != ret)//打开失败
    {
        printf("open error! %s\n",sqlite3_errmsg(pdb));//打开失败，分析错误原因
        exit(-1);
    }
    else
    {
        printf("you have open database successfully!\n");
    }

    create_table(pdb);//创建数据库的里面的表
    insert_record(pdb);//插入记录
    inquire_usecb(pdb);//用回调函数查询
    delete_record(pdb);//删除记录

    inquire_nocb(pdb);//不用回调函数查询
    sqlite3_close(pdb);
    return 0;
}