sqlite3笔记

本地安装

sudo dpkg -i *.deb

测试是否安装成功

输入命令sqlite3
若出现sqlite版本等信息就证明安装成功。

在线安装

必须能连接互联网

sudo apt-get install sqlite3

创建数据库

创建一个名为student的数据库

sqlite3 student.db

数据库基本命令

1、系统命令

.help //帮助文档
.quit //退出
.exit //退出
.table //查看数据库表
.databases //查看打开的数据库

2、sql命令

2.0 创建数据库

sqlite3 student.db  

2.1创建一个表

create table stu(id integetr, name char, score interger); //创建一个表，表已经存在则创建失败
create table if not exists student(id integetr, name char, score interger); //创建一个表如果该表不存在的话

stu — 表的名字

id、name、score — 字段名字

interger、char — 字段的类型

2.2 向表插入数据

insert into stu values(100, 'zhangsan', 80); //完整插入

insert into stu (name, score)values("ares", 100); //特定字段插入

2.3 查看表内容

select * from stu; //查看表所有内容

select name from stu; //查询部分字段的内容

select * from stu where score=80;  //查询score字段为80的内容

where后面为查找条件，还可以多个条件的逻辑与或逻辑或进行查询

2.4 删除记录

delete from stu;//删除整个表
delete from stu where name="zhangsan"; //删除特定的一条记录

2.5 修改字段内容

update stu set name='cainiao' where id=1002;

2.6 插入一列

alter table stu add column address char //向表stu插入一列字段address

2.7 删除一列

sqlite3无法直接删除一列，所以需要一些特殊操作。

1、创建一张新的表

create table stdu1 as select id, name, score from stu; //创建新表stud1,并从stu中复制id,name,score字段插入到新表stu1中

2、删除旧的表

drop table stu;  //stu表的名字   删除就的表

3、将新的表重命名位旧表的名字

alter table stu1 rename to stu; //将新表stu1命名位stu

---

代码中使用sqlite3要安装sqlite3函数库：

sudo apt-get install libsqlite3-dev

基于sqlite3数据库的简单实例

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

#define ARR_SIZE(a)   (sizeof(a)/sizeof(a[0]))

#define DATBASE_PATH "student.db"
#define TABLE_NAME   "student"

enum OpItem {
    ADD_INFO = 0x01,
    DEL_INFO = 0x02,
    SHOW_INFO = 0x03,
    MODIF_INFO = 0x04,
    DEL_TABLE = 0x05,
    EXIT_SYS  = 0x06,
};

struct option {
    int op;
    void (*op_callback)(void);
};

struct student
{
    int id;
    int score;
    char name[32];
};

sqlite3 *db;

int insert_student_tbl(char *tblname, int id, char *name, int score)
{
    int ret;
    char **err_msg;

    char sql[512] = {0};

    sprintf(sql, "insert into %s values(%d, \"%s\", %d);", tblname, id, name, score);
	
          //执行sql语句
    ret = sqlite3_exec(db, sql, NULL, 0, NULL);
    if (ret != SQLITE_OK)
    {
        fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(db));
    }
    else
    {
        fprintf(stdout, "insert successfully\n");
    }
    return ret;
}

int delete_student_by_name(char *tblname, char *name)
{
    int ret;
    char **err_msg;

    char sql[512] = {0};

    sprintf(sql, "delete from %s where name=\"%s\";", tblname, name);

    ret = sqlite3_exec(db, sql, NULL, 0, NULL);
    if (ret != SQLITE_OK)
    {
        fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(db));
    }
    else
    {
        fprintf(stdout, "delete successfully\n");
    }
    return ret;
}

int delete_student_by_id(char *tblname, int id)
{
    int ret;
    char **err_msg;

    char sql[512] = {0};

    sprintf(sql, "delete from %s where id=%d;", tblname, id);

    ret = sqlite3_exec(db, sql, NULL, 0, NULL);
    if (ret != SQLITE_OK)
    {
        fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(db));
    }
    else
    {
        fprintf(stdout, "delete successfully\n");
    }
    return ret;
}

static int callback(void *NotUsed, int column_Count, char **column_Val, char **column_name)
{
    int i;
    printf("argc %d\r\n", column_Count);
    for (i = 0; i < (column_Count); i++)
    {
        printf("  %s : %s  ", column_name[i], column_Val[i] ? column_Val[i] : "NULL");
    }
    printf("\n");
    return 0;
}

int delete_student_tbl(char *tblname)
{
    int ret;

    char sql[512] = {0};
    sprintf(sql, "delete from %s", tblname);

    ret = sqlite3_exec(db, sql, NULL, 0, NULL);
    if (ret != SQLITE_OK)
    {
        fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(db));
    }
    else
    {
        fprintf(stdout, "delete table successfully\n");
    }
    return ret;
}

int get_op(void)
{
    int op;

    scanf("%d", &op);
    return op;
}

int show_all_student(char *tblname)
{
    int ret;

    char sql[512] = {0};
    sprintf(sql, "select * from %s", tblname);

    ret = sqlite3_exec(db, sql, callback, 0, NULL);
    if (ret != SQLITE_OK)
    {
        fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(db));
    }
    else
    {
        fprintf(stdout, "select successfully\n");
    }
    return ret;
}

void add_student_info_cb(void)
{
    struct student s;
    printf("请输入学生名字：");
    scanf("%s", s.name);
    printf("请输入学生学号：");
    scanf("%d", &s.id);
    printf("请输入学生成绩：");
    scanf("%d", &s.score);
    insert_student_tbl(TABLE_NAME, s.id, s.name, s.score);
}

void del_menu_init(void)
{
    printf("**************1、选择通过姓名删除*****************\n");
    printf("**************2、选择通过学号删除*****************\n");
}

void del_student_info_cb(void)
{
    char name[32];
    int id;

    del_menu_init();

    int op = get_op();

    if (op > 2) 
    {
        printf("无效操作\n");
        return;
    }

    switch(op)
    {
        case 1 : 
        {
            printf("输入删除学生的名字: \n");
            scanf("%s", name);
            delete_student_by_name(TABLE_NAME, name);
            break;
        }
        case 2 :
        {
            printf("输入删除学生的学号:\n");
            scanf("%s", name);
            id = atoi(name);
            delete_student_by_id(TABLE_NAME, id); 
            break;
        }
    }
}

void show_student_info_cb(void)
{
    show_all_student(TABLE_NAME);
}


void modify_student_info_cb(void)
{
    
}

void exit_student_sys_cb(void)
{
    exit(0);
    sqlite3_close(db);
}

void del_student_table_cb(void)
{
    delete_student_tbl(TABLE_NAME);
    sqlite3_close(db);
    exit(0);
}


struct option options[] = {
    {.op = ADD_INFO,   .op_callback = add_student_info_cb},
    {.op = DEL_INFO,   .op_callback = del_student_info_cb},
    {.op = SHOW_INFO,  .op_callback = show_student_info_cb},
    {.op = MODIF_INFO, .op_callback = modify_student_info_cb},
    {.op = DEL_TABLE,  .op_callback = del_student_table_cb}, 
    {.op = EXIT_SYS,  .op_callback = exit_student_sys_cb}, 
};


void optiong_handle(struct option *ops, uint32_t size, int op)
{
    int index = 0;

    for (index = 0; index < size; index++)
    {
        if (ops[index].op == op)
        {
            if (ops[index].op_callback)
            {
                ops[index].op_callback();
            }
            break;
        }
    }
}

/*
  * @brief:         创建表    
  * @param[in]:     None
  * @retval[out]:   None
  * @note:              
  * @author:        guangjieMVP
  * @github:        https://github.com/guangjieMVP
*/
int creat_student_tbl(char *tblname)
{
    int ret;
    char **err_msg;

    char sql[512] = {0};
    sprintf(sql, "create table if not exists %s(id integetr, name char, score interger, weigth interger);", tblname);

    ret = sqlite3_exec(db, sql, NULL, 0, NULL);
    if (ret != SQLITE_OK)
    {
        fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(db));
    }
    else
    {
        fprintf(stdout, "Table created successfully\n");
    }
    return ret;
}


/*
  * @brief:         初始化数据库  
  * @param[in]:     None
  * @retval[out]:   None
  * @note:              
  * @author:        guangjieMVP
  * @github:        https://github.com/guangjieMVP
*/
void student_database_init()
{
    int ret;

    ret = sqlite3_open(DATBASE_PATH, &db); //打开数据库，不存在则创建
    if (ret)
    {
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
    }
    else
    {
        fprintf(stderr, "Opened database successfully\n");
        creat_student_tbl(TABLE_NAME);
    }
}

void memu_init(void)
{
    printf("**************1、添加学生信息*****************\n");
    printf("**************2、删除学生信息*****************\n");
    printf("**************3、显示学生信息*****************\n");
    printf("**************4、修改学生信息*****************\n");
    printf("**************5、删除所有学生信息**************\n");
    printf("**************6、退出系统*********************\n");
}

int main(int argc, char **argv)
{
    student_database_init();

    while (1)
    {
        memu_init();
        int op = get_op();
        optiong_handle(options, ARR_SIZE(options), op);
    }
    
    return 0;
}

编译：

gcc student_manager.c -o student_manager -lsqlite3