[轉]Learn SQLite in 1 hour

轉載說明：

1.原文地址：http://www.askyb.com/sqlite/learn-sqlite-in-1-hour/

2.譯文地址：http://www.oschina.net/question/12_53183（紅薯翻譯）

3.英文原文有5處錯誤，下麵的已經修正過了

原文如下：

Learn SQLite in 1 hour

askyb on May, 9th 2012 in SQLite

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 considering.

（SQLite 是一個開源的嵌入式關係資料庫，實現自包容、零配置、支持事務的SQL資料庫引擎。其特點是高度便攜、使用方便、結構緊湊、高效、可靠。 與其他資料庫管理系統不同，SQLite 的安裝和運行非常簡單，在大多數情況下 - 只要確保SQLite的二進位文件存在即可開始創建、連接和使用資料庫。如果您正在尋找一個嵌入式資料庫項目或解決方案，SQLite是絕對值得考慮。[紅薯 譯]）

2. Installation

SQLite on Windows

1. Navigate to SQLite download page at http://www.sqlite.org/download.html
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;
1|Micheal
2|Jenny
3|Francis
4|Kerk

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