SQLite Quick Start Guide _ database Other

1. Introduce

SQLite is an open source embedded relational database that implements the SQL database engine for self containment, 0 configuration, and support transactions. Its features are highly portable, easy to use, compact structure, efficient and reliable. Unlike other database management systems, SQLite is very simple to install and run, and in most cases you can start creating, connecting, and working with databases as long as you ensure that the SQLite binaries exist. If you are looking for an embedded database project or solution, SQLite is definitely worth considering.

2. Installation

SQLite on Windows

Go to SQL download page: http://www.sqlite.org/download.html

Download the precompiled binary package under Windows:

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

Note: <build#> is the compiled version number of SQLite

Unzip the zip file to your disk and add the extracted directory to the system's PATH variable to facilitate the execution of the SQLite command at the command line.

Optional: If you plan to publish an application based on the SQLite database, you will also need to download the source code to compile and leverage its API

• Sqlite-amalgamation-<build#>.zip

SQLite on Linux

Provides convenient commands for SQLite in multiple Linux distributions:

* for Debian or Ubuntu/*
$ sudo apt-get install sqlite3 sqlite3-dev/
 
* for RedHat, CentOS, or fedora/*
$ yum Install SQLite3 Sqlite3-dev

SQLite on Mac OS X

If you are using the Mac OS Snow Leopard or the newer version of the system, then the system is already equipped with SQLite.

3. Create the first SQLite database

Now that you have installed the SQLite database, next we create the first database. Enter the following command in the Command Line window to create a database named Test.db.

Sqlite3 test.db

To create a table:

Sqlite> CREATE TABLE MyTable (ID integer primary key, value text);

The table contains a primary key field named ID and a text field named value.

Note: You must create at least one table or view for the newly created database so that you can save the database to disk or the database will not be created.

Next, write some data into the table:

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 ');

Query data:

Sqlite> select * FROM MyTable;
1| Micheal
2| Jenny
3| Francis
4| Kerk

To set the results of a formatted query:

sqlite>. mode column;
Sqlite>. header on;
Sqlite> select * FROM MyTable;
ID     value
------------------------
1      micheal
2      Jenny
3      Francis
4      Kerk

. mode column is set to column display mode,. Header displays the column name.

Modify the table structure to add columns:

Sqlite> ALTER TABLE mytable add column email text not NULL ' collate nocase;;

To create a view:

Sqlite> CREATE VIEW Nameview as SELECT * FROM MyTable;

To create an index:

Sqlite> CREATE index Test_idx on mytable (value);

4. Some useful SQLite commands

Show Table structure:

sqlite>. Schema [table]

Get all tables and views:

SQLite > Tables 

Gets the list of indexes for the specified table:

SQLite > indices [table]

To export a database to a SQL file:

SQLite > Output [filename] 
sqlite > Dump 
sqlite >. Output stdout

To import a database from a SQL file:

SQLite > read [filename]

Format output data to CSV format:

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

To import data from a CSV file into a table:

SQLite >create table newtable (ID integer primary key, value text); 
SQLite >.import [Filename.csv] NewTable 

Back up the database:

/* Usage:sqlite3 [Database]. dump > [filename] */
sqlite3 mytable.db. Dump > Backup.sql

To recover a database:

/* Usage:sqlite3 [Database] < [filename] */ 
sqlite3 mytable.db < Backup.sql