SQLite Tutorial

The SQLite Command Line tool is the best way to learn about SQLite and its SQL syntax. It is easy to download for Windows, Linux, or Macintosh, and is easy to use. It will expose you directly to the SQL commands that you will work with when you are using the database for real. Go to the downloads section of sqlite.org and download the command line program for Windows, Linux, or MacOS X. (For other platforms, you will have to download the source and build it). The command line is a single file program. Just put the program somewhere on your system path, or if you can't do that, then just copy it into the directory in which you are working. That's it.

The command line program can be run in interactive mode or batch mode. To start the program in interactive mode just go:

>sqlite3 learn.db
sqlite> .quit
>

This will create the database file called "learn.db".
Here are some important commands that you'll use often:

.quit - This command gets you out of the command shell. .exit also works for this.

.dump - This command dumps the entire contents of the database, including schemas (complete with tables, views, indices, triggers, and constraints), and data.

.schema - This command dumps the schema of all of the tables. If you have a table called "Names", and you run ".schema Names", it will just dump the schema for that table. This command is great if you don't remember which tables you have or the names of their fields.

.help - This command lists all of the SQLite command-line commands available.

create table table_name (column_name, type_name, ...); This is an important command that lets you create new tables, each with columns.

insert into table_name values (val1, val2...); - This command lets you insert rows into the table. The number and types of values must correspond to what was defined in the table schema.

select * from table_name; - This allows you to show all rows from a table.

delete from table_name; - This allows you to delete all rows from a table.

drop table table_name; - This allows you to get rid of a table.

Try it out. Fire up your sqlite3 command line and let's go through a sequence to see what happens:

[jgodse@localhost learn]$ sqlite3 learn.db
SQLite version 3.5.6
Enter ".help" for instructions
sqlite> .schema
sqlite> create table learnTb (a integer, b string);
sqlite> .schema
CREATE TABLE learnTb (a integer, b string);
sqlite>insert into learnTb values (1,"Jay");
sqlite> insert into learnTb values (3,"Kay");
sqlite> insert into learnTb values (5,"May");
sqlite> insert into learnTb values (6,"Ray");
sqlite> .dump
BEGIN TRANSACTION;
CREATE TABLE learnTb (a integer, b string);
INSERT INTO "learnTb" VALUES(1,'Jay');
INSERT INTO "learnTb" VALUES(3,'Kay');
INSERT INTO "learnTb" VALUES(5,'May');
INSERT INTO "learnTb" VALUES(6,'Ray');
COMMIT;
sqlite> select * from learnTb;
1|Jay
3|Kay
5|May
6|Ray
sqlite> delete from learnTb;
sqlite> select * from learnTb;
sqlite> .schema
CREATE TABLE learnTb (a integer, b string);
sqlite> drop table learnTb;
sqlite> .schema
sqlite> .help
.bail ON|OFF Stop after hitting an error. Default OFF
.databases List names and files of attached databases
.dump ?TABLE? ... Dump the database in an SQL text format
.echo ON|OFF Turn command echo on or off
.exit Exit this program
.explain ON|OFF Turn output mode suitable for EXPLAIN on or off.
.header(s) ON|OFF Turn display of headers on or off
.help Show this message
.import FILE TABLE Import data from FILE into TABLE
.indices TABLE Show names of all indices on TABLE
.load FILE ?ENTRY? Load an extension library
.mode MODE ?TABLE? Set output mode where MODE is one of:
csv Comma-separated values
column Left-aligned columns. (See .width)
html HTML code
insert SQL insert statements for TABLE
line One value per line
list Values delimited by .separator string
tabs Tab-separated values
tcl TCL list elements
.nullvalue STRING Print STRING in place of NULL values
.output FILENAME Send output to FILENAME
.output stdout Send output to the screen
.prompt MAIN CONTINUE Replace the standard prompts
.quit Exit this program
.read FILENAME Execute SQL in FILENAME
.schema ?TABLE? Show the CREATE statements
.separator STRING Change separator used by output mode and .import
.show Show the current values for various settings
.tables ?PATTERN? List names of tables matching a LIKE pattern
.timeout MS Try opening locked tables for MS milliseconds
.timer ON|OFF Turn the CPU timer measurement on or off
.width NUM NUM ... Set column widths for "column" mode
sqlite> .quit
[jgodse@localhost learn]$

This session demonstrates the basics of using the SQLite command line program. Once you master these basic commands, it will become easier to learn more and more SQL via the command line. Just remember that SQL commands must always with a ";", or the command line will not execute it.

If a command runs on your command line, it will run in your program, so this tool is also a great debugging tool for spplications that use SQLite.

With SQLite, you often take a lot of time to populate a database with tables and data. During software development and testing, as well as during software upgrades, you need to use the same data. The SQLite command line provides utilities to let you save a database to a text file, and then to restore the database using other commands. Try it out. Fire up your sqlite3 command line and let's go through a sequence to see what happens:

C:\Users\Jay\SQLite> sqlite3 dr.db
SQLite version 3.5.6
Enter ".help" for instructions
sqlite> .dump
BEGIN TRANSACTION;
CREATE TABLE names (id integer unique, name string);
INSERT INTO "names" VALUES(1,'Jay');
INSERT INTO "names" VALUES(2,'Kay');
INSERT INTO "names" VALUES(3,'Ray');
CREATE TABLE phones (id integer unique, number string);
INSERT INTO "phones" VALUES(1,'492-9999');
INSERT INTO "phones" VALUES(2,'271-0071');
INSERT INTO "phones" VALUES(3,'555-1234');
COMMIT;
sqlite>

This is the database that I want to save. Here is how to save it:

sqlite> .output phone_dump.txt;
sqlite> .dump
sqlite> .output stdout
sqlite> .quit

The first .output command redirects the output of the command line to a file called phone_dump.txt. The .dump command dumps the database to that file. The second .output command redirects the output back to the screen as it was originally. The last command gets you out of the database.

Now check your directory and you'll notice a new file. Print it out to the screen, and you'll see that it is a plain text file.

C:\Users\Jay\SQLite>dir
Volume in drive C is XX
Volume Serial Number is xxxxxxxxxxxxxxx

Directory of C:\Users\Jay\SQLite

16/07/2009 03:18 PM .
16/07/2009 03:18 PM ..
16/07/2009 02:20 PM 5,120 dr.db
16/07/2009 03:17 PM 376 phone_dump.txt

C:\Users\Jay\SQLite>type phone_dump.txt
BEGIN TRANSACTION;
CREATE TABLE names (id integer unique, name string);
INSERT INTO "names" VALUES(1,'Jay');
INSERT INTO "names" VALUES(2,'Kay');
INSERT INTO "names" VALUES(3,'Ray');
CREATE TABLE phones (id integer unique, number string);
INSERT INTO "phones" VALUES(1,'492-9999');
INSERT INTO "phones" VALUES(2,'271-0071');
INSERT INTO "phones" VALUES(3,'555-1234');
COMMIT;

C:\Users\Jay\SQLite>

So far, so good. But how do we import this data back into a database? Start by creating a new database, and then import the data.

C:\Users\Jay\SQLite>sqlite3 aa.db
SQLite version 3.5.6
Enter ".help" for instructions
sqlite> .schema
sqlite> .read phone_dump.txt
sqlite> .dump
BEGIN TRANSACTION;
CREATE TABLE names (id integer unique, name string);
INSERT INTO "names" VALUES(1,'Jay');
INSERT INTO "names" VALUES(2,'Kay');
INSERT INTO "names" VALUES(3,'Ray');
CREATE TABLE phones (id integer unique, number string);
INSERT INTO "phones" VALUES(1,'492-9999');
INSERT INTO "phones" VALUES(2,'271-0071');
INSERT INTO "phones" VALUES(3,'555-1234');
COMMIT;
sqlite>.quit
C:\Users\Jay\SQLite>dir
Volume in drive C is XX
Volume Serial Number is XXXXXXXXX

Directory of C:\Users\Jay\SQLite

16/07/2009 11:28 PM .
16/07/2009 11:28 PM ..
16/07/2009 03:18 PM 5,120 aa.db
16/07/2009 02:20 PM 5,120 dr.db
16/07/2009 03:17 PM 376 phone_dump.txt

C:\Users\Jay\SQLite>

Upon opening the new database, the empty result for .schema says that there is no data in the table. The .read command reads in the data from phone_dump.txt, and the subsequent .dump command shows that the restore worked. The .quit gets you out of the database. The dir command shows a new database file called aa.db which is the same size as the original database file.

Using these utilities will help you develop your SQLite application a bit faster.