本文共 8011 字，大约阅读时间需要 26 分钟。

本地安装

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 
   
    #include 
    
     #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

转载地址：http://lozcz.baihongyu.com/