数据库练习

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


int create_table(sqlite3 *db)
{
char *errmsg = NULL;
char *sql;
sql = "create table if not exists mytable(id integer primary key,name text);";
if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
{
printf("operator failed:%s\n",errmsg);
exit(-1);
}
}


int insert_record(sqlite3 *db)
{
char *errmsg = NULL;
char sql[100];
int num1;
char name1[20];
int n;
    printf("how many members:\n");
scanf("%d",&n);
int i;
for(i=0;i<n;i++)
    {  
   printf("please enter a name\n");
   scanf("%s",name1);
    printf("please enter a id_num\n");
      scanf("%d",&num1);
     sprintf(sql,"insert into mytable(id,name) values (%d,'%s');",num1,name1);
        if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
   {
   printf("insert failed:%s\n",errmsg);
   exit(-1);
   }
}
}
int displaycd(void *para,int col_cnt,char **col_value,char **col_name)
{
int i;
printf("total column is %d\n",col_cnt);
for(i = 0; i < col_cnt ; i++)
{
printf("col_name:%s-->col_value:%s\n",col_name[i],col_value[i]);
}
printf("______________________________________\n");
return 0;
}


int delete_record (sqlite3 *db)
{


int n;
char *errmsg = NULL;
char sql[50];
printf("please enter a number u want delete\n");
scanf("%d",&n);
sprintf(sql,"delete from mytable where id = %d;",n);
if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))//一定要放循环里,不然无法
{
printf("delete failed:%s\n",errmsg);
exit(-1);
}
}


int inquire_usecb (sqlite3 *db)
{
char *errmsg = NULL;
char *sql;
sql = "select *from mytable";
if(SQLITE_OK != sqlite3_exec(db,sql,displaycd,NULL,&errmsg))
{
printf("iquire error:%s\n",errmsg);
}
}


int inquire_nocb (sqlite3 *db)
{
int nrow,ncolumn;
char **azresult;
char *sql;
char *errmsg;
int i;
sql = "select *from mytable";
if(SQLITE_OK!=sqlite3_get_table(db,sql,&azresult,&nrow,&ncolumn,&errmsg))
{
printf("operator error:%s\n",errmsg);
exit(-1);
}
printf("row:%d  column:%d\n",nrow,ncolumn);
printf("the result of querying is:\n");
for(i=0;i<(nrow+1)*ncolumn;i++)
{
printf("%10s",azresult[i]);
if((i+1)%ncolumn==0)
{
printf("\n");
}
}
sqlite3_free_table(azresult);
}
int main()
{
    sqlite3 *db = NULL;


int ret;//定义函数返回值
ret = sqlite3_open("mydatabase.db",&db);


if (ret != SQLITE_OK)
{
perror("sqlite open");
exit(-1);
}
else
{
printf("u have open a sqlites database successfully.\n");
}
create_table (db);
insert_record (db);
inquire_usecb (db);
inquire_nocb (db);
delete_record (db);
inquire_usecb (db);
    return 0;
}