SQLite Tutorial

SQLite Tutorial

Updated: Thu Jan 11 17:35:19 EST 2007

Common Commands

To create a database file, run the command "sqlite3", followed by the database name. For example, to create the database "test.db", run the sqlite3 command as follows:

     $ sqlite3 test.db
    SQLite version 3.0.8
    Enter ".help" for instructions
    sqlite> .quit
    $

The database file test.db will be created, if it does not already exist. Running this command will leave you in the sqlite3 environment. There are three ways to safely exit this environment: .q, .quit, and .exit.

You do not have to enter the sqlite3 interactive environment. Instead, you could perform all commands at the shell prompt, which is ideal when running bash scripts and commands in an ssh string. Here is an example of how you would create a simple table from the command prompt:

     $ sqlite3 test.db  "create table t1 (t1key INTEGER
                 PRIMARY KEY,data TEXT,num double,timeEnter DATE);"

After table t1 has been created, data can be inserted as follows:

     $ sqlite3 test.db  "insert into t1 (data,num) values ('This is sample data',3);"
    $ sqlite3 test.db  "insert into t1 (data,num) values ('More sample data',6);"
    $ sqlite3 test.db  "insert into t1 (data,num) values ('And a little more',9);"

As expected, doing a select returns the data in the table. Note that the primary key "t1key" auto increments; however, there are no default values for timeEnter. To populate the timeEnter field with the time, an update trigger is needed. Note that you should not use the abbreviation "INT" when working with the PRIMARY KEY. You must use "INTEGER" for the primary key to update.