Introduction to Using SQLite

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`