SQLite3 数据库基本操作

发布时间：2020-12-12 23:33:58 所属栏目：百科来源：网络整理

导读：SQLite3 Database manipulate !-- /* Font Definitions */ @font-face {font-family:宋体; panose-1:2 1 6 0 3 1 1 1 1 1; mso-font-alt:SimSun; mso-font-charset:134; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 13513

SQLite3 Database manipulate

Table of Contents

1. Database basics – SQL

2. The core C API – basics

1. Database basics

<1> Creating a database that we will call gps.db:

# sqlite3 gps.db

SQLite version 3.6.23.1

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite> .exit

<2> Now you have a database file on disk called gps.db

# ls

gps.db test

<3> Create a table. Issue the following statement from the shell:

sqlite> create table pos(pos_x varchar(10),pos_y varchar(10));

<4> Add a few rows to the table:

sqlite> insert into pos values('234.2','678.8');

sqlite> insert into pos values('824.5','926.7');

sqlite> insert into pos values('176.5','278.0');

<5> Now fetch them back:

sqlite> .headers on

sqlite> select * from pos;

pos_x|pos_y

234.2|678.8

824.5|926.7

176.5|278.0

<5> Getting table information:

sqlite> .tables

pos

sqlite> .schema

CREATE TABLE pos(pos_x varchar(10),pos_y varchar(10));

sqlite> .indices

<6> Exporting Data:

sqlite> .output gps.sql

<7> View configure information:

sqlite> .show

echo: off

explain: off

headers: on

mode: list

nullvalue: ""

output: stdout

separator: "|"

width:

2. The core C API

<1>some basics API

Opening A New Database Connection

int sqlite3_open(

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

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

);

int sqlite3_open16(

const void *filename, /* Database filename (UTF-16) */

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

);

Compiling An SQL Statement

int sqlite3_prepare(

sqlite3 *db, /* Database handle */

const char *zSql, /* SQL statement,UTF-8 encoded */

int nByte, /* Maximum length of zSql in bytes. */

sqlite3_stmt **ppStmt, /* OUT: Statement handle */

const char **pzTail /* OUT: Pointer to unused portion of zSql */

);

int sqlite3_prepare_v2(

sqlite3 *db, /* OUT: Statement handle */

const char **pzTail /* OUT: Pointer to unused portion of zSql */

);

int sqlite3_prepare16(

sqlite3 *db, /* Database handle */

const void *zSql,UTF-16 encoded */

int nByte, /* OUT: Statement handle */

const void **pzTail /* OUT: Pointer to unused portion of zSql */

);

int sqlite3_prepare16_v2(

sqlite3 *db, /* SQL statement, /* OUT: Statement handle */

const void **pzTail /* OUT: Pointer to unused portion of zSql */

);

Destroy A Prepared Statement Object

int sqlite3_finalize(sqlite3_stmt *pStmt);

Closing A Database Connection

int sqlite3_close(sqlite3 *);

Convenience Routines For Running Queries

int sqlite3_get_table(

sqlite3 *db, /* An open database */

const char *zSql, /* SQL to be evaluated */

char ***pazResult, /* Results of the query */

int *pnRow, /* Number of result rows written here */

int *pnColumn, /* Number of result columns written here */

char **pzErrmsg /* Error msg written here */

);

void sqlite3_free_table(char **result);

One-Step Query Execution Interface

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

);

<2>program

Compile arm format file:

arm-linux-gcc -I/home/furtherchan/work/sqlite_porting/sqlite_build/include -L/home/furtherchan/work/sqlite_porting/sqlite_build/lib -o c_api_sqlite c_api_sqlite.c -lsqlite3

furtherchan@further:~/work/sqlite_porting$ ll c_api_sqlite

-rwxr-xr-x 1 furtherchan furtherchan 9488 2010-06-23 15:25 c_api_sqlite

furtherchan@further:~/work/sqlite_porting$ arm-linux-strip c_api_sqlite

furtherchan@further:~/work/sqlite_porting$ ll c_api_sqlite

-rwxr-xr-x 1 furtherchan furtherchan 4040 2010-06-23 15:26 c_api_sqlite

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!