sqlite 学习

• mySQL：主要用于网站开发的数据库。（好几百兆）
• sqlite：用于嵌入式开发，（轻量级数据库）
  • 支持数据库大小至 2TB
  • 足够小，全部源码大致 3 万行 c 代码，250kb。

1、sqlite 安装（在线下载）

aptitude install aqlite3
或者 apt-get install aqlite3

2、使用 sqlite 创建一个数据库（名字叫做 student）（数据库以 db 结尾）

sqlite3 	student.db

---

一、sqlite3 基本命令

• 数据库的命令基本相同
• 进入 sqlite3 之后，大部分命令是 以 . 开头 （系统命令）
• 也有部分命令不以 . 开头，以 ; 结尾 （ sql 命令）

1、系统命令（以 '.' 开头的命令）
	.help	帮助
	.quit	退出
	.exit	退出
	.schema	查看表的结构图
	.table 	查看表

2、sql 命令
	创建一张数据库表etu
	create table stu(id Integer，nane char，score Integer) ,

	插入一条记录
	insert into stu values(1001,' zhangsan' ,80)，
	insert into stu (natme, score)values("wangw", 89); 11 部分字段的插入操作

	查询记录
	select * from stu;

	查询所有字段的结果
	select name, score from stu. //查询数据库中的部分字段的内容
	select * from stu where score=80;
	select * from stu where score=80 and name = zhangsan'
	select * from stu where score=80 or name =’1isi' ;

	删除一条记录
	delete from stu where score=' 90’:

	更新一条记录
	update stu set name=' wangwr' where id*1001:
	update stu set name=' wangwu'，score = 88 where id=1001;

	插入一列
	alter table stu add colum address char.

	删除一列
	gqlite3不支持，直接删除一列
	（1）创建一张新的表
	create table stul ag select id, name， score from stu,
	（2）删除原有的表
	drop table stu;
	（3）将新的表名字改成原有的旧表的名字
	alter table stul renams to stu;

---

1、创建一个表 （ sql命令 —— 以分号结尾）

create table stu (id Integer, name char , socre Integer);

• stu 是表的名称
• id Integer ：是一个字段，id 是字段名字， Integer 是字段类型。 同理 name char， 其中 name 是字段名字，char 是字段类型。
• 每个字段以逗号分隔开来。

---

2、 查看一下表的结构图（系统命令）

.schema

---

3、 向创建的表当中，插入一条数据 （以分号结尾）

insert into stu values(1001, 'tony',80);
insert into stu values(1002, 'Aimy',90);

• insert into xxx values：插入的命令
• stu ：插入表的名称

或者插入部分字段：

insert into stu (id,name) values(1003, "jacky" );

---

4、查看已经插入表的信息

select   * from stu;  // 查询所有的字段

查看部分字段：（把 * 号，更换为 (name, score) 部分字段的名称）

select  name, score from stu;  // 查询字段为 name、score 的字段

加一些特定的要求（where score=80 and id=1001;）

select * from stu where score=80 and id=1001; // 同时满足使用 and
select * from stu where score=80 or id=1002; // 只满足一个即可使用 or

---

5、向创建的表当中，删除一条数据信息

delete from stu where id=1001;   // 删除id 为 1001 的记录
delete from stu where id=1001 or id = 1002; // 删除id 为 1001 和 1002 的记录 
delete form stu;  // 删除整张表 

---

6、更改一条数据信息

update stu set name="wangwu" where id = 1001;
update stu set name="wangwu" , score = 88 where id = 1001;

• set name=“wangwu” ：将名字改为 ”wangwu“。
• set name=“wangwu” , score = 88 ：同时修改 name 和 score
• where id = 1001 ：指定需要更改的数据信息

---

二、进阶命令

1、增加一个新的字段（增加新的一列）

alter table stu add column address char;

• alter table stu ：改变 stu 这个表
• add column ：增加一个纵列
• address char ：新的字段，address 为字段名称，char 为字段类型。

---

2、删除一列（sqlite 本身不支持，想一些其他办法）

我想把 score、address 两列删除。

第一步：创建一个新的表 stu1， 并且将 name、score 从 stu 继承下来。

create table stu1 as select id, name from stu;

• as select id, name ：选择继承 id 、 name 两个字段
• from stu ：从旧表 stu 当中。

第二步：删除原来的旧表

drop table stu；

第三步：将新的表改为原有的旧表。

alter table stu1 rename to stu;

---

三、使用 C 语言代码来操作数据库

官方网址：List Of SQLite Functions

知乎笔记网址：SQLite数据库C编程 - 知乎 (zhihu.com)

在Linux 当中部署 sqlite 的环境：

• 添加头文件：
• 添加链接库文件：

方法一：直接使用命令安装：

apt-get install libsqlite3-dev

方法二：自己编译源码：

1、下载源码包：找那种包含配置文件的源码包

2、查看源码目录

3、查看编译后生成的文件：

命令执行过程

1、解压安装包
tar -zxvf sqlite-autoconf-3360000.tar.gz
2、执行配置文件
./configure --prefix=/opt/sqlite (x86编译)
./configure CC=arm-linux-gcc –prefix=/opt/sqlite –host=arm-linux （交叉编译，arm 平台使用）
3、编译 sqlite
make
4、安装 sqlite
make install

5、部署头文件：
cp ./include/* /usr/include/ -f
6、部署库文件：
cp ./include/* /usr/include/ -f

---

1、打开一个数据库的接口

int sqlite3_open(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb          /* OUT: SQLite db handle */
);

• 参数

  • const char *filename ：数据库路径名
  • sqlite3 **ppDb ： 得到一个文件句柄。（输出型参数）

• 返回值

  • 成功返回： SQLITE_OK。
  • 失败返回： 错误码。

---

2、关闭一个数据库的接口

int sqlite3_close ( sqlite3* );

• 参数：
  • sqlite3* ：文件句柄
• 返回值：
  • 成功返回： SQLITE_OK。
  • 失败返回： 错误码。

---

3、得到错误信息：

const char *sqlite3_errmsg( sqlite3* );

• 参数：
  • sqlite3* ：文件句柄
• 返回值：
  • const char * ：返回字符串的首地址

---

4、执行一条 sqlite 的语句

int sqlite3_exec(
  sqlite3*,                                  /* An open database */
  const char *sql,                           /* SQL to be evaluated */
  int (*callback)(void*,int,char**,char**),  /* Callback function */
  void *agr,                                    /* 1st argument to callback */
  char **errmsg                              /* Error msg written here */
);

• 参数

  • sqlite3* ： 文件句柄
  • const char *sql ： sqlite 当中具体的指令语句。
  • int (callback)(void,int,char**,char**)： 回调函数。
  • void *agr : 回调函数的参数。
  • char **errmsg ：二级指针，（输出型参数）

  回调函数就是一个通过函数指针调用的函数。

  如果你把函数的指针（地址）作为参数传递给另一个函数，当这个指针被用来调用其所指向的函数时，我们就说这是回调函数。

---

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

const char *sql_create_cmd = "create table stu(id Integer,name char,score Integer)"; // 定义创建表的命令


// 创建一个表的基本类型：
typedef struct student
{
    int id;
    char name[32];
    int score;
}STU,* PSTU;


int do_insert(sqlite3 *db)
{
    STU s1;
    char sql_cmd[128];
    char *errmsg; // 存放错误信息

    printf("Input id: ");
    scanf("%d",&s1.id);
    getchar();

    printf("Input name: ");
    scanf("%s",s1.name);
    getchar();

    printf("Input score: ");
    scanf("%d",&s1.score);
    getchar();


    // 因为每次插入指令不同，所以每次 sql_insert_cmd 并不是字符串常量，所以需要进行字符串的拼接
    sprintf(sql_cmd,"insert into stu values(%d,'%s',%d);",s1.id,s1.name,s1.score);

    if(SQLITE_OK != sqlite3_exec(db,sql_cmd,NULL,NULL,&errmsg) ) // 使用插入语句
    {
        printf("%s \n",errmsg); // 这个 errmsg
        return -1;
    }
    else
    {
        printf("sqlite insert success \n");
    }
    return 0;
}


int do_delete(sqlite3 *db)  // 直接基于插入修改 （我们按照 id 来进行删除）
{
    STU s1;
    char sql_cmd[128];
    char *errmsg; // 存放错误信息

    printf("Input delete id:");
    scanf("%d",&s1.id);
    getchar();

    // 因为每次插入指令不同，所以每次 sql_insert_cmd 并不是字符串常量，所以需要进行字符串的拼接
    sprintf(sql_cmd,"delete from stu where id=%d;",s1.id);

    if(SQLITE_OK != sqlite3_exec(db,sql_cmd,NULL,NULL,&errmsg) ) // 使用插入语句
    {
        printf("%s \n",errmsg); // 这个 errmsg
        return -1;
    }
    else
    {
        printf("delete success \n");
    }
    return 0;
}

int do_updata(sqlite3 *db)  // 根据 id 来更新 score
{
    STU s1;
    char sql_cmd[128];
    char *errmsg; // 存放错误信息

    printf("Input updata id: ");
    scanf("%d",&s1.id);
    getchar();

    printf("updata score: ");
    scanf("%d",&s1.score);
    getchar();

    // 因为每次插入指令不同，所以每次 sql_insert_cmd 并不是字符串常量，所以需要进行字符串的拼接
    sprintf(sql_cmd,"updata stu set score=%d where id = %d;",s1.score,s1.id);

    if(SQLITE_OK != sqlite3_exec(db,sql_cmd,NULL,NULL,&errmsg) ) // 使用插入语句
    {
        printf("%s \n",errmsg); // 这个 errmsg
        return -1;
    }
    else
    {
        printf("Updata success \n");
    }
    return 0;
}


// SQLITE_API int sqlite3_exec(
//   sqlite3*,                                  /* An open database */
//   const char *sql,                           /* SQL to be evaluated */
//   int (*callback)(void*,int,char**,char**),  /* Callback function */
//   void *,                                    /* 1st argument to callback */
//   char **errmsg                              /* Error msg written here */
// );

// 此处回调函数的作用：每找到一个记录就自动执行一次回调函数
// void* : 传递给回调函数的参数
// int：记录中包含的字段数目
// char ** ：包含每个字段值的指针数组 （值）
// char ** : 包含每个字段名称的指针数组 （名称）


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]);   // 长度不够 11 的，以空格补齐，超出 11 长度，不删除也不补齐
    }
    putchar(10);
    return 0;
}


int do_query(sqlite3 *db) // 查询的时候，sqlite3_exec 要有回调函数
{
    char sql_cmd[128];
    char *errmsg; // 存放错误信息
    sprintf(sql_cmd,"select * from stu;");

    if(SQLITE_OK != sqlite3_exec(db,sql_cmd,callback,NULL,&errmsg) ) // 使用插入语句
    {
        printf("%s \n",errmsg); // 这个 errmsg
        return -1;
    }
    else
    {
        printf("query success \n");
    }
    return 0;
}

// 不使用回调函数的查询函数：
//  SQLITE_API int sqlite3_get_table(
//  sqlite3 *db,          /* An open database */  数据库句柄
//  const char *zSql,     /* SQL to be evaluated */ sql指令
//  char ***pazResult,    /* Results of the query */ 用来指向 sql 执行结果的指针
//  int *pnRow,           /* Number of result rows written here */ 满足条件的记录数目
//  int *pnColumn,        /* Number of result columns written here */ 每条记录包含的字段数目
//  char **pzErrmsg       /* Error msg written here */ 错误信息指针的地址
// );

int do_query1(sqlite3 *db) // 查询的时候，sqlite3_exec 要有回调函数
{
    char sql_cmd[128];
    char *errmsg; // 存放错误信息
    char **pazResult = NULL;
    int nrow;
    int ncloumn;

    int index;
    int i,j;

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

    if(SQLITE_OK != sqlite3_get_table(db,sql_cmd,&pazResult,&nrow,&ncloumn,&errmsg))
    {
        printf("%s \n",errmsg); // 这个 errmsg
        return -1;
    }
    else
    {
        printf("query success \n");
    }
    // 打印最上面的字段
    for(j = 0; j < ncloumn; j++)
    {
        printf("%-11s",pazResult[j]); 
    }
    printf("\n");

    index = ncloumn; // 跳过最上面的三个字段（只打印表当中的数据）
    for(i = 0; i < nrow; i++)
    {
        for(j = 0; j < ncloumn; j++)
        {
            printf("%-11s",pazResult[index++]); //二维数组，每一维度指一行数据
        }
        printf("\n");
    }
    return 0;
}

void usage(void)
{
    printf("***********************************************************\n");
    printf("1:insert  2:delete  3:updata  4:query  5:quit  6：return\n");         // 一级菜单
    printf("***********************************************************\n");
}



int main(int argc, const char *argv[])
{
    sqlite3 *db;
    char *errmsg; // 存放错误信息
    int cmd = 0; // 用于命令分支
// 1、建立一个数据库
    if(SQLITE_OK != sqlite3_open("test.db", &db))
    {
        printf("数据库连接失败！ %s \n",sqlite3_errmsg(db));
        return -1; // 打开失败，直接退出
    }
    else
    {
        printf("数据库连接成功！\n");
    }


// 2、建立一张表
    if(SQLITE_OK != sqlite3_exec(db,sql_create_cmd,NULL,NULL,&errmsg) )
    {
        printf("%s \n",errmsg); // 这个 errmsg
    }
    else
    {
        printf("sqlite create table success \n");
    }

// 3、一级菜单
    while(1)
    {
        printf("***********************************************************\n");
        printf("1:insert  2:delete  3:updata  4:query  5:quit  6：return\n");         // 一级菜单
        printf("***********************************************************\n");

        scanf("%d",&cmd);
        getchar(); // 吃掉回车

        switch(cmd)
        {
            case 1:
            {
                do_insert(db); break;
            }
            case 2:
            {
                do_delete(db); break;
            }
            case 3:
            {
                do_updata(db); break;
            }
            case 4:
            {
                do_query1(db); break;
            }
            case 5:
            {
                sqlite3_close(db); // 先关闭数据库
                exit(0); // 正常退出
                break;
            }
            case 6:
            {
                sqlite3_exec(db,"drop table stu;",NULL,NULL,NULL);
                break;
            }
            default:
                printf("Error cmd \n");
                usage(); break;
        }
    }



    
    return 0;
}

在线词典

原理分析

---

我们要实现的效果：

• 用户注册和登陆验证
• 服务器将用户信息和历史记录保存在数据库当中。
• 客户端输入用户名和密码，服务器端在数据库当中查找、匹配、返回结果。
• 单词在线翻译。
• 历史记录查询。

项目流程：

• 定义数据库中表的结构
• 定义消息结构体
• 分析服务器和客户端流程
• 编码实现

---