sqlite[转]Learn SQLite in 1 hour







Learn SQLite in 1 hour

askyb on May, 9th 2012 in

1. Introduction

SQLite is an open source, embedded relational database which implements
a self-contained, serverless, zero-configuration,transactional SQL
database engine. SQLite has a well-deserved reputation for being highly
portable, easy to use, compact, efficient, and reliable. Unlike
client–server database management systems, installing and running of
SQLite database is pretty straightforward in most cases — just make sure
the SQLite binaries file exists anywhere you want and start to create,
connect, and using the database. If you are looking for an embedded
database for your projects or solutions, SQLite is definitely worth

与其余数据库管理种类不一样,SQLite 的设置和运行极度简单,在一大半景况下 –

2. Installation

SQLite on Windows

  1. Navigate to SQLite download page at
  2. Download the following Precompiled Binaries For Windows:

    • sqlite-shell-win32-x86-<build#>.zip
    • sqlite-dll-win32-x86-<build#>.zip

    Note: <build#> is the running build number of sqlite.

  3. Unpack the ZIP files into your favourite folder. Add folder path to
    the PATH system variable to make the SQLite command line shell
    available within the environment.
  4. OPTIONAL: If you plan to develop any application that host a
    sqlite database then you will need to download the source code in
    order to compile and utilize its API .

    • sqlite-amalgamation-<build#>.zip

SQLite on Linux
SQLite binaries can be obtained in a variety of ways depending on the
Linux distro that you are using.

/* For Debian or Ubuntu /*
$ sudo apt-get install sqlite3 libsqlite3-dev

/* For RedHat, CentOS, or Fedora/*
$ yum install SQLite3 libsqlite3-dev

SQLite on Mac OS X
If you are running a Mac OS Leopard or later, then it alraedy have
pre-installed SQLite.

3. Create you first SQLite Database

you now should have the SQLite binaries ready and time to create your
first SQLite database now. Type the following command in windows’s
command prompt or Linux’s terminal.

To create a new database called test.db:

sqlite3 test.db

To create a table in the database:

sqlite> create table mytable(id integer primary key, value text);

2 columns were created.A primary key column called “id” which has the
ability to automatically generate value by default and a simple text
field called “value”.

NOTE: At least 1 table or view
need to be created in order to commit the new database to disk.
Otherwise, it won’t database won’t be created.

To insert data into mytable:

sqlite> insert into mytable(id, value) values(1, 'Micheal');
sqlite> insert into mytable(id, value) values(2, 'Jenny');
sqlite> insert into mytable(value) values('Francis');
sqlite> insert into mytable(value) values('Kerk');

To fetch data from mytable:

sqlite> select * from mytable;

To fetch data from mytable by improving the formatting a little:

sqlite> .mode column
sqlite> .header on
sqlite> select * from mytable;
id          value
----------- -------------
1           Micheal
2           Jenny
3           Francis
4           Kerk

The .mode column will display data into column format.
The .header on will display table’s column name.

To add additional column into mytable:

sqlite> alter table mytable add column email text not null '' collate nocase;

To create a view for mytable:

sqlite> create view nameview as select * from mytable;

To create an index for mytable:

sqlite> create index test_idx on mytable(value);

4. Useful SQLite’s command

Display table schema:

sqlite> .schema [table]

Retrieve a list of tables (and views):

sqlite> .tables

Retrieve a list indexes for a given table:

sqlite> .indices [table]

Export database objects to SQL format:

sqlite> .output [filename]
sqlite> .dump
sqlite> .output stdout

Import database objects(SQL format) to database:

sqlite> .read [filename]

Formatting exported data into CSV format:

sqlite>.output [filename.csv]
sqlite>.separator ,
sqlite> select * from mytable;
sqlite>.output stdout

Import CSV formatted data to a new table:

sqlite>create table newtable(id integer primary key, value text);
sqlite>.import [filename.csv] newtable

To backup database:

/* usage: sqlite3 [database] .dump > [filename] */
sqlite3 mytable.db .dump > backup.sql

To restore database:

/* usage: sqlite3 [database] < [filename] */
sqlite3 mytable.db < backup.sql