Introduction to SQLite
I recently had to learn how to use SQLite, here's my cheat sheet on how to do the basics.
Overview
SQLite is a very simple database which supports most of the SQL92 standard. It does not require a daemon process to run (similar to Berkeley DB) so can be a great alternative for lightweight database applications.
It does not have any support for users and instead relies on file system based permissions for controlling access to the database.
Administer the Database
There are two modes for administrating a SQLite database. From the command line (shown below by the #
prompt) and from inside the SQLite console (shown by the sqlite>
prompt).
In the console mode, all administration command begin with a leading .
(eg. .help
and .quit
).
Create a new database and enter the console
# sqlite /tmp/new.sqlite
Open a console for an existing database
# sqlite /tmp/exists.sqlite
Import a SQL schema into the database
sqlite> .read /tmp/schema.sql
Print out a databases schema to standard out
# sqlite /tmp/exists.sqlite .dump
Dump out a database to a text file
# sqlite /tmp/exists.sqlite .dump > /tmp/backup.sql
Import a backup into a new database
# sqlite /tmp/new.sqlite
sqlite> .read /tmp/backup.sql
Quit the console
sqlite> .quit
Ask for help
sqlite> .help
Use the Database
List all of the open databases
sqlite> .databases
seq name file
--- --------------- ----------------------------------------------------------
0 main /tmp/new.sqlite
1 temp /var/tmp/sqlite_4aPDXkbVYL95nqt
List all the tables in the open database
sqlite> .tables
addressbook addressbook_cat addressbook_catmap