linux sqlite3 数据库的安装与使用

1, 本地安装，官网下载

sudo dpkg -i *.deb

2, 在线安装

sudo apt-get install sqlite3
sudo apt-get install libsqlite3-dev

安装完成后通过命令操作：
创建数据库
sqlite3 stu.db

在该数据库中创建表 stu
create table stu(id integer, name char, score integer);

查看有数据库表的结构图
.schema

查看
.table

插入数据
insert into stu values(1001, "zhangsan", 80);

查询
select * from stu

插入部分数据
insert into stu (name, score)values(1003, "wangwu");

删除数据
delete from stu

更新一个数据
update stu set name='wangwu' where id=1001;

查看打开的数据库
.databases

更改表, 插入一列
alter table stu add column address char;

删除一列，需要新建表，将原有表数据导入
create table stul as select id, name, score from stu;

删除表
drop table stu;

更改表的名字
alter table stu1 rename to stu；

通过c语言代码操作：
先通过命令创建表
create table usr (date text, chat text, from1 int, to1 int, state int);

插入数据
sprintf 通过拼接字符串发数据库命令
get_date(date);
sprintf(buf, "insert into usr values('%s', %d, %d, %d)", date, client_in.buf, client_in.host,client_in.dst, state);
if (sqlite3_exec(db, buf, NULL, NULL, &errmsg) != SQLITE_OK)
{

printf("%s", errmsg);
return -1;

}

简单的例子：

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

#define DATABASE  "stu.db"

int do_insert(sqlite3 *db)
{
    int id;
    char name[32] = {};
    int score;
    char *errmsg;
    char sql[128] = {};

    printf("input id:");
    scanf("%d", &id);

    printf("input name");
    scanf("%s", name);

    printf("input score");
    scanf("%d", &score);
    
    sprintf(sql, "insert into stu values(%d, %s, %d);", id, name, score);
    
    if (sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        printf("errmsg: %s\n", errmsg);
        return -1;
    }
    printf("insert success\n");
    return 0;
}


int do_delect(sqlite3 *db)
{
    int id;
    char *errmsg;
    char sql[128] = {};

    printf("input id:");
    scanf("%d", &id);
    
    sprintf(sql, "delete from stu where id = %d;", id);
    
    if (sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        printf("errmsg: %s\n", errmsg);
        return -1;
    }
    printf("delect done\n");
    return 0;
}


int do_update(sqlite3 *db)
{
    int id;
    int score;
    char *errmsg;
    char sql[128] = {};

    printf("update id:");
    scanf("%d", &id);

    printf("update score");
    scanf("%d", &score);
    
    sprintf(sql, "update stu set score = %d where id = %d;", score, id);
    
    if (sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        printf("errmsg: %s\n", errmsg);
        return -1;
    }
    printf("update done\n");
    return 0;
}

int callback(void *para, int f_num, char **f_value, char **f_name)
{
    int i = 0;

    for (i = 0; i < f_num; i++)
    {
        printf("%-11s", f_value[i]);
    }
    putchar(10);

    return 0;
}

int do_query(sqlite3 *db)
{
    char sql[128] = {};
    char *errmsg;
    
    sprintf(sql, "select * from stu;");

    if (sqlite3_exec(db, sql, callback, NULL, &errmsg) != SQLITE_OK)
    {
        printf("%s\n", errmsg);
    }
    else
    {
        printf("query done\n");
    }
}

int do_quit(sqlite3 *db)
{
    sqlite3_close(db);
}

int do_query1(sqlite3 *db)
{
    int i, j;
    char sql[128] = {};
    char **pazResult;
    int pnRow;       /* Number of result rows written here */
     int pnColumn; 
    char *errmsg;
    int index = 0;

    sprintf(sql, "select * from stu;");

    if (sqlite3_get_table(db, sql, &pazResult, &pnRow, &pnColumn, &errmsg)!= SQLITE_OK)
    {
        printf("%s\n", errmsg);
    }
    
    for (i = 0; i < pnRow; i++)
    {
        for(j = 0; j < pnColumn; j++)
            printf("%-11s ", pazResult[index++]);
        putchar(10);
    }
    return 0;
}

int main(int arge, char *argv[])
{
    sqlite3 *db;
    char *errmsg;
    int cmd;

    if ((sqlite3_open(DATABASE, &db)) != SQLITE_OK)
    {
        printf("%s\n", sqlite3_errmsg(db));
        return -1;
    }

    if (sqlite3_exec(db, "create table stu (id integer, name char, score integer);", NULL, NULL, &errmsg) != SQLITE_OK)
    {
        printf("%s\n", errmsg);
    }

    printf("creaete table or open success\n");
    printf("%d\n", SQLITE_OK);
    while(1)
    {
        printf("*******************\n");
        printf("1:insert 2:delect 3:query 4:update 5:quit\n");
        scanf("%d", &cmd);

        switch(cmd)
        {
            case 1:
                do_insert(db);
                break;
            case 2:
                do_delect(db);
                break;
            case 3:
                do_query(db);
                //do_query1(db);
                break;
            case 4:
                do_update(db);
                break;
            case 5:
                do_quit(db);
                return 0;
            default:
                printf("ERROR cmd \n");
        }
    }
    return 0;
}