编译执行
代码语言：javascript复制emacs@ubuntu:~/c$ alias gtc
alias gtc='gcc -Wall -g -o'
emacs@ubuntu:~/c$ gtc sqlite.x sqlite.c -lsqlite3
emacs@ubuntu:~/c$ 代码语言：javascript复制Note: 在编译过程中一定要加上
-lsqlite3参数，否则不加载相关的库，会有依赖关系的报错
emacs@ubuntu:~/c$ gtc sqlite.x sqlite.c
/tmp/ccjGMlsR.o: In function `showall':
/home/emacs/c/sqlite.c:35: undefined reference to `sqlite3_exec'
/home/emacs/c/sqlite.c:37: undefined reference to `sqlite3_errmsg'
/tmp/ccjGMlsR.o: In function `query':
/home/emacs/c/sqlite.c:67: undefined reference to `sqlite3_exec'
/home/emacs/c/sqlite.c:69: undefined reference to `sqlite3_errmsg'
/tmp/ccjGMlsR.o: In function `update':
/home/emacs/c/sqlite.c:99: undefined reference to `sqlite3_exec'
/home/emacs/c/sqlite.c:101: undefined reference to `sqlite3_errmsg'
/tmp/ccjGMlsR.o: In function `delete':
/home/emacs/c/sqlite.c:129: undefined reference to `sqlite3_exec'
/home/emacs/c/sqlite.c:131: undefined reference to `sqlite3_errmsg'
/tmp/ccjGMlsR.o: In function `insert':
/home/emacs/c/sqlite.c:162: undefined reference to `sqlite3_exec'
/home/emacs/c/sqlite.c:164: undefined reference to `sqlite3_errmsg'
/tmp/ccjGMlsR.o: In function `main':
/home/emacs/c/sqlite.c:185: undefined reference to `sqlite3_open'
/home/emacs/c/sqlite.c:187: undefined reference to `sqlite3_errmsg'
/home/emacs/c/sqlite.c:188: undefined reference to `sqlite3_close'
/home/emacs/c/sqlite.c:195: undefined reference to `sqlite3_exec'
/home/emacs/c/sqlite.c:197: undefined reference to `sqlite3_errmsg'
/home/emacs/c/sqlite.c:221: undefined reference to `sqlite3_close'
/home/emacs/c/sqlite.c:223: undefined reference to `sqlite3_errmsg'
collect2: ld returned 1 exit status
emacs@ubuntu:~/c$进行执行
代码语言：javascript复制emacs@ubuntu:~/c$ gtc sqlite.x sqlite.c -lsqlite3
emacs@ubuntu:~/c$ ./sqlite.x
Open database test.db successfully...
Can't create table students:table students already exists
Please take follow action:
0.exit 1.insert 2.delete 3.update 4.query 5.showall
5
Please take follow action:
0.back 1.showall
1
ready to process ...
[ select * from students; ]
sid name score
99 io 2334
sid name score
98 iii 91
sid name score
34 ui 89
sid name score
12 ii 89.3
sid name score
89 jj 89.2
Select successfully...
Please take follow action:
0.exit 1.insert 2.delete 3.update 4.query 5.showall
1
Please take follow action:
0.back 1.insert
1
Please input SID,NAME,SCORE of the student:
SID NAME SCORE
77 wilmos 100
ready to process ...
[ insert into students(sid,name,score) values(77,'wilmos',100.00); ]
Insert successfully...
Please take follow action:
0.exit 1.insert 2.delete 3.update 4.query 5.showall
4
Please take follow action:
0.back 1.query
1
Please input SID of the student:
SID
77
ready to process ...
[ select * from students where sid=77; ]
sid name score
77 wilmos 100
Select successfully...
Please take follow action:
0.exit 1.insert 2.delete 3.update 4.query 5.showall
3
Please take follow action:
0.back 1.update
1
Please input SID,NAME,SCORE of the student to be update:
SID NAME SCORE
77 fang 99
ready to process ...
[ update students set name='fang',score=99.00 where sid=77; ]
Update successfully...
Please take follow action:
0.exit 1.insert 2.delete 3.update 4.query 5.showall
4
Please take follow action:
0.back 1.query
1
Please input SID of the student:
SID
77
ready to process ...
[ select * from students where sid=77; ]
sid name score
77 fang 99
Select successfully...
Please take follow action:
0.exit 1.insert 2.delete 3.update 4.query 5.showall
5
Please take follow action:
0.back 1.showall
1
ready to process ...
[ select * from students; ]
sid name score
99 io 2334
sid name score
98 iii 91
sid name score
34 ui 89
sid name score
12 ii 89.3
sid name score
89 jj 89.2
sid name score
77 fang 99
Select successfully...
Please take follow action:
0.exit 1.insert 2.delete 3.update 4.query 5.showall
2
Please take follow action:
0.back 1.delete
1
Please input SID of the student:
SID
89
ready to process ...
[ delete from students where sid=89; ]
Delete successfully...
Please take follow action:
0.exit 1.insert 2.delete 3.update 4.query 5.showall
5
Please take follow action:
0.back 1.showall
1
ready to process ...
[ select * from students; ]
sid name score
99 io 2334
sid name score
98 iii 91
sid name score
34 ui 89
sid name score
12 ii 89.3
sid name score
77 fang 99
Select successfully...
Please take follow action:
0.exit 1.insert 2.delete 3.update 4.query 5.showall
0
ready to exit...
Close database test.db successfully...
emacs@ubuntu:~/c$编译执行过程中没有报错，从结果来看，符合预期
sqlite3_open
sqlite3.h 中有关于 sqlite3_open 的声明
/*
** Open the sqlite database file "filename". The "filename" is UTF-8
** encoded for sqlite3_open() and UTF-16 encoded in the native byte order
** for sqlite3_open16(). An sqlite3* handle is returned in *ppDb, even
** if an error occurs. If the database is opened (or created) successfully,
** then SQLITE_OK is returned. Otherwise an error code is returned. The
** sqlite3_errmsg() or sqlite3_errmsg16() routines can be used to obtain
** an English language description of the error.
**
** If the database file does not exist, then a new database is created.
** The encoding for the database is UTF-8 if sqlite3_open() is called and
** UTF-16 if sqlite3_open16 is used.
**
** Whether or not an error occurs when it is opened, resources associated
** with the sqlite3* handle should be released by passing it to
** sqlite3_close() when it is no longer required.
*/
int sqlite3_open(
const char *filename, /* Database filename (UTF-8) */
sqlite3 **ppDb /* OUT: SQLite db handle */
);这个函数用来打开指定的数据库
sqlite3_open() 和 sqlite3_open_v2() 使用 UTF-8 来解析文件名
sqlite3_open16() 使用 UTF-16 来解析文件名
返回值 ： 成功 SQLITE_OK ，失败会返回错误代码，错误代码可以使用 sqlite3_errmsg() 和 sqlite3_errmsg16() 来获取解析
Note:
ppDb是指针的指针
SQLITE_OK
sqlite3.h 中有关于返回码的定义
/*
** Return values for sqlite3_exec() and sqlite3_step()
*/
#define SQLITE_OK 0 /* Successful result */
#define SQLITE_ERROR 1 /* SQL error or missing database */
#define SQLITE_INTERNAL 2 /* An 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 /* (Internal Only) Table or record not found */
#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 /* Too much data for one row of a table */
#define SQLITE_CONSTRAINT 19 /* Abort due to contraint 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 */更全面和详细的解释可以参看 Result and Error Codes
sqlite3_errmsg
sqlite3.h 中有关于 sqlite3_errmsg 的声明
/*
** Return a pointer to a UTF-8 encoded string describing in english the
** error condition for the most recent sqlite3_* API call. The returned
** string is always terminated by an 0x00 byte.
**
** The string "not an error" is returned when the most recent API call was
** successful.
*/
const char *sqlite3_errmsg(sqlite3*);这个函数返回一串关于此错误的英文描述(事实上是一个字符指针，指向一个字符串)
参数为一个数据库指针
sqlite3_close
sqlite3.h 中有关于 sqlite3_close 的声明
/*
** A function to close the database.
**
** Call this function with a pointer to a structure that was previously
** returned from sqlite3_open() and the corresponding database will by closed.
**
** All SQL statements prepared using sqlite3_prepare() or
** sqlite3_prepare16() must be deallocated using sqlite3_finalize() before
** this routine is called. Otherwise, SQLITE_BUSY is returned and the
** database connection remains open.
*/
int sqlite3_close(sqlite3 *);这个函数用于关闭一个数据库
参数为一个数据库指针
sqlite3_exec
sqlite3.h 中有关于 sqlite3_exec 的声明
/*
** A function to executes one or more statements of SQL.
**
** If one or more of the SQL statements are queries, then
** the callback function specified by the 3rd parameter is
** invoked once for each row of the query result. This callback
** should normally return 0. If the callback returns a non-zero
** value then the query is aborted, all subsequent SQL statements
** are skipped and the sqlite3_exec() function returns the SQLITE_ABORT.
**
** The 4th parameter is an arbitrary pointer that is passed
** to the callback function as its first parameter.
**
** The 2nd parameter to the callback function is the number of
** columns in the query result. The 3rd parameter to the callback
** is an array of strings holding the values for each column.
** The 4th parameter to the callback is an array of strings holding
** the names of each column.
**
** The callback function may be NULL, even for queries. A NULL
** callback is not an error. It just means that no callback
** will be invoked.
**
** If an error occurs while parsing or evaluating the SQL (but
** not while executing the callback) then an appropriate error
** message is written into memory obtained from malloc() and
** *errmsg is made to point to that message. The calling function
** is responsible for freeing the memory that holds the error
** message. Use sqlite3_free() for this. If errmsg==NULL,
** then no error message is ever written.
**
** The return value is is SQLITE_OK if there are no errors and
** some other return code if there is an error. The particular
** return value depends on the type of error.
**
** If the query could not be executed because a database file is
** locked or busy, then this function returns SQLITE_BUSY. (This
** behavior can be modified somewhat using the sqlite3_busy_handler()
** and sqlite3_busy_timeout() functions below.)
*/
int sqlite3_exec(
sqlite3*, /* An open database */
const char *sql, /* SQL to be executed */
sqlite3_callback, /* Callback function */
void *, /* 1st argument to callback function */
char **errmsg /* Error msg written here */
);这个函数用来执行 SQL 语句
sqlite3 指定一个已经打开的数据库
sql 指定一个将要被执行的SQL语句
sqlite3_callback 回调函数
void * 回调函数的第一个参数
errmsg 错误信息的指针
sqlite3_callback
sqlite3.h 中有关于 sqlite3_callback 的声明
/*
** The type for a callback function.
*/
typedef int (*sqlite3_callback)(void*,int,char**, char**);回调函数是作为 sqlite3_exec 第三个参数而存在的，它的作用在于处理返回的每条结果
一般而言，回调函数的返回结果都是0，如果结果是非0，那么查询过程会中断，所有接下来的查询会中断并且跳过，sqlite3_exec() 的返回结果会是 SQLITE_ABORT
第一个参数 void* 由 sqlite3_exec() 传过来的第四个参数，作为自己的第一个参数
第二个参数 int 查询结果中当条记录的字段数
第三个参数 char** 字段名形成的字符串指针数组
第四个参数 char** 结果集形成的字符串指针数组
sprintf
stdio.h 中有关于 sprintf 的声明
/* Write formatted output to S. */
extern int sprintf (char *__restrict __s,
__const char *__restrict __format, ...) __THROW;在构建 SQL 语句的过程中会经常使用到 sprintf ，它和 printf 的用法相似，但是将结果写到一个字符数组中，而不是直接打印到了终端上，这样便于后期的处理
总结
以下函数可以对sqlite数据库进行创建与控制，是存储数据的基础操作
- sqlite3_open
- sqlite3_errmsg
- sqlite3_close
- sqlite3_exec
- sqlite3_callback
通过各方面资料弄懂其参数的意义和返回值的类型，是熟练掌握的基础
原文地址
