SQLite

Getting started
sqlite3 works with a local database file. Within this local database, multiple tables can be created. SQL commands are case insensitive, but are shown here in upper case. Dot commands are case sensitive.
A command history is kept in ~/.sqlite_history
A config file can be established as ~/.sqliterc. It should generally only contain meta-commands.
To invoke sqlite in interactive mode use:
$ sqlite3

To quit interactive mode use one of:
sqlite> .q sqlite> .quit sqlite> .exit sqlite> ^D

To invoke sqlite in interactive mode and also create or use an existing database:
$ sqlite3 </path/to/database/file> $ sqlite3 /home/me/mydata.db

To create or use an existing database from interactive mode:
sqlite> ATTACH DATABASE "<path/to/database/file>" AS <alias>

To get help on the dot commands use:
sqlite> .help

Basic stuff
To make a table:
sqlite> CREATE TABLE t1 (t1key INTEGER PRIMARY KEY,data TEXT,num DOUBLE,timeEnter DATE);

To add data to the table:
sqlite> INSERT INTO t1 (data,num) VALUES ('This is sample data',3);
To view the list of databases:
sqlite> .databases

To view tables

SQL Features That SQLite Does Not Implement
Only the RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are supported. Other kinds of ALTER TABLE operations such as DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT, and so forth are omitted.

Backup and Restore
A good way to make an archival copy of a database is this:
$ echo '.dump' | sqlite3 ex1 | gzip -c >ex1.dump.gz

This generates a file named ex1.dump.gz that contains everything you need to reconstruct the database at a later time, or on another machine. To reconstruct the database, just type:
$ zcat ex1.dump.gz | sqlite3 ex2

The text format is pure SQL so you can also use the .dump command to export an SQLite database into other popular SQL database engines. Like this:
$ createdb ex2 $ sqlite3 ex1 .dump | psql ex2

Last revised Sun, 27 May 2007 09:32:31 -0400