I had to learn how to use SQLite as part of install PHP iAddressbook. Here's my cheatsheet 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 (show 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 data base

  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 of the tables in the open database

  sqlite> .tables
  addressbook         addressbook_cat     addressbook_catmap`