Defindit Docs and Howto Home
This page last modified: Aug 22 2008
keywords:sql,sql,sequel,database,db,rdbms,relational,database,sqlite3
description:Sample session transcript of running SQLIte version 3, creating a table, inserting record, and selecting records.
title:SQLite example for beginners
In this example I have logged in to a computer called "zeus". If you
are using Linux or OSX, run your terminal program (instead of "logging
in".
On OSX the terminal is called "Terminal" and can be found in the
Applications folder. Linux and KDE is "Terminal", and is found under
the "System" item in the Start (K, Fedora) menu. (The Linux
application's name is "konsole"; the menu item doesn't match the
actual name of the program.)
In this example, the file holding my database is "first.db". SQLite is
transactional, and ACID compliant, and thus is a true RDBMS. All
tables are contained in a single file, and the SQL engine is a single
executable. There is no server. No configuration is necessary. The
database file is portable to other platforms (other operating
systems.) SQLite is really amazing.
The SQLite home page:
http://www.sqlite.org/index.html
If you are new to the OSX or Linux command line (we call it a
"shell"), you may enjoy my Intro to Unix, Linux and Mac OSX.
http://itc.virginia.edu/achs/documents/intro_unix.html
(All trademarks are properties of their respective owners.)
What follows is a session transcript:
[zeus ~]$ sqlite3 first.db
SQLite version 3.4.0
Enter ".help" for instructions
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
.mode MODE ?TABLE? Set output mode where MODE is one of:
csv Comma-separated values
column Left-aligned columns. (See .width)
html HTML <table> 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
.width NUM NUM ... Set column widths for "column" mode
sqlite> .header on
sqlite> .mode column
sqlite> create table address (street1 text, street2 text, city text, state text, country text, zip text);
sqlite> .schema address
CREATE TABLE address (street1 text, street2 text, city text, state text, country text, zip text);
sqlite> insert into address values ('123 Locust Road', '', 'Eden', 'VA', 'USA', '22123');
sqlite> insert into address values ('123 Murky Hollow', 'Box 456', 'Eden', 'VA', 'USA', '22123');
sqlite> select * from address;
street1 street2 city state country zip
--------------- ---------- ---------- ---------- ---------- ----------
123 Locust Road Eden VA USA 22123
123 Murky Hollo Box 456 Eden VA USA 22123
sqlite> .quit
[zeus ~]$ sqlite3 first.db
SQLite version 3.4.0
Enter ".help" for instructions
sqlite> .mode column
sqlite> .header on
sqlite> select * from address;
street1 street2 city state country zip
--------------- ---------- ---------- ---------- ---------- ----------
123 Locust Road Eden VA USA 22123
123 Murky Hollo Box 456 Eden VA USA 22123
sqlite> .quit
[zeus ~]$