sqlite3 PC安装及使用

时间：2022-11-24 22:59:00

 

sqlite3使用

1. 安装sqlite3

sudo apt-get install sqlite3

sudo apt-get install libsqlite3-dev

2. sqlite常用命令

当前目录下建立或打开test.db数据库文件，并进入sqlite命令终端，以sqlite>前缀标识：
#sqlite3 test.db

查看数据库文件信息命令(注意命令前带字符'.')：
sqlite>.database

查看所有表的创建语句：
sqlite>.schema

查看指定表的创建语句：
sqlite>.schema table_name

以sql语句的形式列出表内容：
sqlite>.dump table_name

设置显示信息的分隔符：
sqlite>.separator symble
Example：设置显示信息以‘：'分隔
sqlite>.separator :

设置显示模式：
sqlite>.mode mode_name
Example:默认为list，设置为column，其他模式可通过.help查看mode相关内容
sqlite>.mode column

输出帮助信息：
sqlite>.help

设置每一列的显示宽度：
sqlite>.width width_value
Example:设置宽度为2
sqlite>.width 2

列出当前显示格式的配置：
sqlite>.show

退出sqlite终端命令：
sqlite>.quit
或
sqlite>.exit

3. sqlite3常用指令

sql的指令格式：所有sql指令都是以分号(;)结尾，两个减号(--)则表示注释。
如：
sqlite>create studen_table(Stu_no interger PRIMARY KEY, Name text NOT NULL, Id interger UNIQUE, Age interger CHECK(Age>6), School text DEFAULT 'xx小学);
该语句创建一个记录学生信息的数据表。
3.1 sqlite3存储数据的类型
NULL：标识一个NULL值
INTERGER：整数类型
REAL：浮点数
TEXT：字符串
BLOB：二进制数

3.2 sqlite3存储数据的约束条件
Sqlite常用约束条件如下：
PRIMARY KEY - 主键：
1）主键的值必须唯一，用于标识每一条记录，如学生的学号
2）主键同时也是一个索引，通过主键查找记录速度较快
3）主键如果是整数类型，该列的值可以自动增长
NOT NULL - 非空：
约束列记录不能为空，否则报错
UNIQUE - 唯一：
除主键外，约束其他列的数据的值唯一
CHECK - 条件检查：
约束该列的值必须符合条件才可存入
DEFAULT - 默认值：
列数据中的值基本都是一样的，这样的字段列可设为默认值

3.3 sqlite3常用指令
1）建立数据表
create table table_name(field1 type1, field2 type1, ...);
table_name是要创建数据表名称，fieldx是数据表内字段名称，typex则是字段类型。
例，建立一个简单的学生信息表，它包含学号与姓名等学生信息：
create table student_info(stu_no interger primary key, name text);
2）添加数据记录
insert into table_name(field1, field2, ...) values(val1, val2, ...);
valx为需要存入字段的值。
例，往学生信息表添加数据：
Insert into student_info(stu_no, name) values(0001, alex);

3）修改数据记录
update table_name set field1=val1, field2=val2 where expression;
where是sql语句中用于条件判断的命令，expression为判断表达式
例，修改学生信息表学号为0001的数据记录：
update student_info set stu_no=0001, name=hence where stu_no=0001;

4）删除数据记录
delete from table_name [where expression];
不加判断条件则清空表所有数据记录。
例，删除学生信息表学号为0001的数据记录：
delete from student_info where stu_no=0001;

5）查询数据记录
select指令基本格式：
select columns from table_name [where expression];
a查询输出所有数据记录
select * from table_name;
b限制输出数据记录数量
select * from table_name limit val;
c升序输出数据记录
select * from table_name order by field asc;
d降序输出数据记录
select * from table_name order by field desc;
e条件查询
select * from table_name where expression;
select * from table_name where field in ('val1', 'val2', 'val3');
select * from table_name where field between val1 and val2;
f查询记录数目
select count (*) from table_name;
g区分列数据
select distinct field from table_name;
有一些字段的值可能会重复出现，distinct去掉重复项，将列中各字段值单个列出。

6）建立索引
当说数据表存在大量记录，索引有助于加快查找数据表速度。
create index index_name on table_name(field);
例，针对学生表stu_no字段，建立一个索引：
create index student_index on student_table(stu_no);
建立完成后，sqlite3在对该字段查询时，会自动使用该索引。

7）删除数据表或索引
drop table table_name;
drop index index_name;

4. sqlite3函数

//当数据库文件不存在时，sqlite3_open不会报错，会创建一个空文件

//而sqlite3_open_v2会报错，并且不会产生空的数据库文件

int sqlite3_open(

const char *filename,   /* Database filename (UTF-8) */

sqlite3 **ppDb          /* OUT: SQLite db handle */

);

int sqlite3_open_v2(

const char *filename,   /* Database filename (UTF-8) */

sqlite3 **ppDb,         /* OUT: SQLite db handle */

int flags,              /* Flags */

const char *zVfs        /* Name of VFS module to use */

);

 

int sqlite3_close(sqlite3 *);

 

//回调函数是检测到有一条记录就执行一次，有几条记录就执行几次

//p是&empty, argc是字段个数，argvv是字段名字， argv是字段值

//int rscallback(void *p, int argc, char *argv[], char *argvv[])

typedef int (*sqlite3_callback)(void*,int,char**, char**);

 

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 */

);

** The pointer arguments to [sqlite3_free()] and [sqlite3_realloc()]

** must be either NULL or else pointers obtained from a prior

** invocation of [sqlite3_malloc()] or [sqlite3_realloc()] that have

** not yet been released.

void *sqlite3_malloc(int);

void *sqlite3_realloc(void*, int);

void sqlite3_free(void*);

 

**The sqlite3_mprintf() and sqlite3_vmprintf() routines write their

** results into memory obtained from [sqlite3_malloc()].

** The strings returned by these two routines should be

** released by [sqlite3_free()].  ^Both routines return a

** NULL pointer if [sqlite3_malloc()] is unable to allocate enough

** memory to hold the resulting string.

SQLITE_API char *sqlite3_mprintf(const char*,...);

SQLITE_API char *sqlite3_vmprintf(const char*, va_list);

SQLITE_API char *sqlite3_snprintf(int,char*,const char*, ...);

SQLITE_API char *sqlite3_vsnprintf(int,char*,const char*, va_list);

 

返回结果：

#define SQLITE_OK           0   /* Successful result */

/* beginning-of-error-codes */

#define SQLITE_ERROR        1   /* SQL error or missing database */

#define SQLITE_INTERNAL     2   /* Internal logic error in SQLite */

#define SQLITE_PERM         3   /* Access permission denied */

#define SQLITE_ABORT        4   /* Callback routine requested an abort */

#define SQLITE_BUSY         5   /* The database file is locked */

#define SQLITE_LOCKED       6   /* A table in the database is locked */

#define SQLITE_NOMEM        7   /* A malloc() failed */

#define SQLITE_READONLY     8   /* Attempt to write a readonly database */

#define SQLITE_INTERRUPT    9   /* Operation terminated by sqlite3_interrupt()*/

#define SQLITE_IOERR       10   /* Some kind of disk I/O error occurred */

#define SQLITE_CORRUPT     11   /* The database disk image is malformed */

#define SQLITE_NOTFOUND    12   /* Unknown opcode in sqlite3_file_control() */

#define SQLITE_FULL        13   /* Insertion failed because database is full */

#define SQLITE_CANTOPEN    14   /* Unable to open the database file */

#define SQLITE_PROTOCOL    15   /* Database lock protocol error */

#define SQLITE_EMPTY       16   /* Database is empty */

#define SQLITE_SCHEMA      17   /* The database schema changed */

#define SQLITE_TOOBIG      18   /* String or BLOB exceeds size limit */

#define SQLITE_CONSTRAINT  19   /* Abort due to constraint violation */

#define SQLITE_MISMATCH    20   /* Data type mismatch */

#define SQLITE_MISUSE      21   /* Library used incorrectly */

#define SQLITE_NOLFS       22   /* Uses OS features not supported on host */

#define SQLITE_AUTH        23   /* Authorization denied */

#define SQLITE_FORMAT      24   /* Auxiliary database format error */

#define SQLITE_RANGE       25   /* 2nd parameter to sqlite3_bind out of range */

#define SQLITE_NOTADB      26   /* File opened that is not a database file */

#define SQLITE_ROW         100  /* sqlite3_step() has another row ready */

#define SQLITE_DONE        101  /* sqlite3_step() has finished executing */

/* end-of-error-codes */

 

** ^The sqlite3_errcode() interface returns the numeric [result code] or

** [extended result code] for the most recent failed sqlite3_* API call

** associated with a [database connection]. If a prior API call failed

** but the most recent API call succeeded, the return value from

** sqlite3_errcode() is undefined.  ^The sqlite3_extended_errcode()

** interface is the same except that it always returns the

** [extended result code] even when extended result codes are

** disabled.

**

** ^The sqlite3_errmsg() and sqlite3_errmsg16() return English-language

** text that describes the error, as either UTF-8 or UTF-16 respectively.

** ^(Memory to hold the error message string is managed internally.

** The application does not need to worry about freeing the result.

** However, the error string might be overwritten or deallocated by

** subsequent calls to other SQLite interface functions.)^

**

** When the serialized [threading mode] is in use, it might be the

** case that a second error occurs on a separate thread in between

** the time of the first error and the call to these interfaces.

** When that happens, the second error will be reported since these

** interfaces always report the most recent result.  To avoid

** this, each thread can obtain exclusive use of the [database connection] D

** by invoking [sqlite3_mutex_enter]([sqlite3_db_mutex](D)) before beginning

** to use D and invoking [sqlite3_mutex_leave]([sqlite3_db_mutex](D)) after

** all calls to the interfaces listed here are completed.

**

** If an interface fails with SQLITE_MISUSE, that means the interface

** was invoked incorrectly by the application.  In that case, the

** error code and message may or may not be set.

*/

SQLITE_API int sqlite3_errcode(sqlite3 *db);

SQLITE_API int sqlite3_extended_errcode(sqlite3 *db);

SQLITE_API const char *sqlite3_errmsg(sqlite3*);

SQLITE_API const void *sqlite3_errmsg16(sqlite3*);

5. 举例

5.1  数据库插入数据

#include <sqlite3.h>

#include <stdio.h>

 

int main()

{

      int ret;

      sqlite3 * db;

      char *err = NULL;

      char *sql = NULL;

      char id[10];

      char name[20];

      char age[10];

 

      //当数据库文件不存在时，sqlite3_open不会报错，会创建一个空文件

      //而sqlite3_open_v2会报错，并且不会产生空的数据库文件

//    ret = sqlite3_open_v2("mydatabase1.dat", &db, SQLITE_OPEN_READWRITE, NULL);  

      ret = sqlite3_open("mydatabase1.dat", &db);

      if(ret != SQLITE_OK)

      {

           //printf("open database error!\n");

           fputs(sqlite3_errmsg(db), stderr);

           return -1;

      }

 

      ret = sqlite3_exec(db, "create table student(id integer primary key, name text, age integer);", NULL, NULL, &err);

      if(ret != SQLITE_OK)

      {

           fputs(err, stderr);

           return -1;

      }

 

      printf("please input student info:\n");

      printf("Id:\n");

      scanf("%s", id);

      printf("Name:\n");

      scanf("%s", name);

      printf("Age:\n");

      scanf("%s", age);

 

      sql = sqlite3_mprintf("insert into student values(%s, %Q, %s);",

                 id, name, age);

      ret = sqlite3_exec(db, sql, NULL, NULL, &err);

      /*

      ret = sqlite3_exec(db, "insert into student values(4, 'XiaoLi', 23);", NULL, NULL, &err);*/

      if(ret != SQLITE_OK)

      {

           fputs(err, stderr);

           return -1;

      }

      sqlite3_free(sql);

 

      sqlite3_close(db);

 

      return 0;

}

 

 

5.2  显示数据库内容

#include <sqlite3.h>

#include <stdio.h>

 

//select查询用到的回调函数

//回调函数是检测到有一条记录就执行一次，有几条记录就执行几次

//p是&empty, argc是字段个数，argvv是字段名字， argv是字段值

int rscallback(void *p, int argc, char *argv[], char *argvv[])

{

      int i;

      *(int *)p = 0; //有记录，我们就把empty改变成0

      for(i=0; i<argc; ++i)

           printf("%s=%s ", argvv[i], argv[i]?argv[i]:"null");

      printf("\n");

 

      return 0;

}

 

int main()

{

      int ret;

      sqlite3 * db;

      int empty=1; //为1表示查询结果为空，没有匹配的记录

      char *err = NULL;

 

      ret = sqlite3_open("mydatabase.dat", &db);

      if(ret != SQLITE_OK)

      {

           printf("open database error!\n");

           return -1;

      }

 

      ret = sqlite3_exec(db, "select * from student;", rscallback, &empty, &err);

      if(ret != SQLITE_OK)

      {

           fputs(err, stderr);

           return -1;

      }

      if(empty)

      {

           printf("查询结果为空!\n");

      }

 

      sqlite3_close(db);

 

      return 0;

}

 

 

参考

1. http://www.jb51.net/article/44545.htm  Linux sqlite3 基本命令

2. http://www.cnblogs.com/hnrainll/archive/2011/09/08/2170489.html   Linux下用到数据库sqlite3

标签：

相关文章