pantz.org banner
SQLite commands and general usage
Posted on 05-25-2009 19:13:35 UTC | Updated on 05-26-2009 01:40:57 UTC
Section: /software/sqlite/ | Permanent Link

SQLite is an embedded open source relational database (db). It is very portable, easy to use, compact, efficient, and reliable. Being an embedded database it becomes part of the program that hosts it. It is embedded in many popular programs used today. Most use the SQLite C API to interface with the SQLite db. Many scripting/programming languages use the API like the Perl module DBI::SQLite, PHP's data objects with the SQLite driver, or just straight C programs. Not only can these languages (and many more) use the SQLite C API to access the SQLite db but most OS's have a statically linked native binary that can also be controlled completely from the command line. How cool is that?

Installing

SQLite can be installed by going to the SQLite website and downloading a copy and installing it. They have binaries for most major platforms (Windows, MAC, or Linux). If your using a OS with a package manager then there is a good chance you can just install it by using your package manager.

There are 2 versions of SQLite. Version 2 and version 3. Version 2 is (as you may have guessed) is the older version. SQLite 3 databases are not compatible with SQLite 2 databases. Use SQLite 3 if at all possible. If your going to install the SQLite command line version then here is something to remember. The package for SQLite 2 is just called "SQLite". The package for SQLite version 3 is called "SQLite3". These are also the names for the command line binaries. You access a version 2 db with the "SQLite" binary (program). You access a version 3 db with the "SQLite3" binary.

Here are some examples of installing SQLite version 3 on some Linux and BSD's.

On Linux distros with the apt-get package manager (Ex: Debian or Ubuntu).

sudo apt-get install SQLite3

On Linux distros with the yum package manager (Ex: RedHat, CentOS, or Fedora).

yum install SQLite3

On OpenBSD 4.4.

pkg_add -v ftp://ftp.openbsd.org/pub/OpenBSD/4.4/packages/i386/SQLite3-3.5.9p0.tgz

You get the idea. Just install the SQLite3 package for whatever OS you have. If your OS does not have a package just go to the SQLite website then download and install their binary.

SQLite command line

The following commands will be using the statically linked command-line program. This is what was installed in the install section above. You can completely control an SQLite database with this program and it's special built in shell. Some commands in the following examples will be invoked from a Linux shell and and from the SQLite shell. The SQLite shell will consider any command with . (period) in front of it a SQLite command. You can get a complete list of valid SQLite commands by typing .help at the SQLite shell. The commands that don't begin with a period are just regular SQL commands. They should work with most databases.

The examples below are done from a Linux machine with version 3.5.9 of SQLite.

Below when you see user@host:~$ it means execute the command from the shell. When you see sqlite> it means from a SQLite prompt (shell) after selecting a db.

Making/accessing a SQLite database.

This command will not make the db (the file test.db) until you create something in it (Ex: table or view) or until you run a few select commands from the SQLite prompt (like .database). This is so you can change things like page size or character encoding before the database structure is written to the file. After executing the command you will see a SQLite prompt. Make a table or a view to have the file actually written to disk.

If you already have a database file called test.db then you can select and access it with the same command. In summary if the db file does not exist this command will create it and if it does exist then it will select it to be accessed.

user@host:~$ sqlite3 test.db

Make your output pretty

Before we start creating and looking at data we are going to change a few default settings to make the ouput of the commands look better. We will turn on column mode (.mode col). There are many other modes other than col. See .help for more output modes. We will also turn on column headers (.headers on). Both stay on until you exit the SQLite shell or change them to something else.

sqlite> .mode col
sqlite> .headers on

Creating tables

Let's make a table called test. After the table is made the database file will exist on disk. The table named test and have 2 columns. One called ids and one called value. The ids column is an integer primary key, SQLite will make this an auto increment column. Which means if there is no number then SQLite will generate a number for the column on the first insert. After the first insert it will auto increment the numbers from there. All of this is done with the SQL create command.

sqlite> create table test (ids integer primary key, value text);

Creating views

Let's create a view. Views are like virtual tables. People also call them derived tables. This is because their contents are derived from other tables. Views look like regular tables, but they are not. Views are dynamically generated. A view is made up of relational expressions and takes other tables to produce a new table.

The following example will create a view called testview and will select everything from the test table we just created. It's a silly example of what views are good for but it will work. Most views are good for queries you keep using over and over again. You create the view to use as a temporary table and then query the view just like a table. The view eliminates the need to keep using that same redundant query.

Creating views like creating tables is done with the SQL create command.

sqlite> create view testview AS select * from test;

Creating indexes

Indexes designed to speed up queries under certain conditions. If our test table had 100,000 entries and we wanted to find a small group of entries the database would have to scan all 100,000 entries looking for our small group. If we make an index of all these entries then we will be able to scan all the entries much faster.

The bad thing is that indexes increase the size of the database because they keep a copy of all the columns they index. One other bad thing is that if you insert, update or delete a record the database has to modify the record and the index of that table. This means that indexes can slow down inserts, updates, deletes, etc. If used wisely indexes can give you a huge performance boost. You will just have to test them for yourself. Here is how to make an index for our table test on column value.

Creating indexes like creating tables and views is done with the SQL create command.

sqlite> create index testindex on test (value);

Listing views, tables, and indexes

To see all the tables and views in the database we use the SQLite .tables command. You can also use the SQL like operator to find tables and views with a pattern. Using the % symbol in the example below we show all the tables and views beginning with the letter t.

sqlite> .tables

test      testview
sqlite> .tables t%
test

To view any indexes a table has we can use the SQLite .indices command. To see them for our test table we would do the following.

sqlite> .indices test

testindex

Listing schema information

To see database schema information we use the SQLite .schema command. This give you the create command that was used to create the tables. If you created an index on any columns then this will also show up.

sqlite> .schema test

CREATE TABLE test (ids integer primary key, value text);
CREATE INDEX testindex on test (value);

Inserting data into tables

Now let's put in some data in the db with some SQL insert statements.

sqlite> insert into test (value) values('value1');
sqlite> insert into test (value) values('value2');
sqlite> insert into test (value) values('value3');
sqlite> insert into test (value) values('value4');

Seeing the data

Let's use the SQL select command to look at the data we inserted. Remember we turned on column mode (.mode col) and column headers (.headers on) at the beginning of this page so it's easier to read the output.

sqlite> select * from test;

ids         value
----------  ----------
1           value1
2           value2
3           value3
4           value4

Seeing your SQLite databases

To see the databases that are currently open use the SQLite .databases command. It will show the main and temp databases and where they are on the system.

sqlite> .databases

seq  name             file
---  ---------------  ------------------------------------
0    main             /home/user/test.db
1    temp             /var/tmp/etilqs_oCUXLLbZO4TjUNI

Exporting your data

You can export all of your database objects to a file for backup using the SQLite .output and .dump commands. These commands will output all of the data from the database. The first .output line directs any output from now on to the file /tmp/test.sql. The second line .dump will export all of the data in the objects in the database. If the file is already there it will overwrite that file. The last command will set the output back to the screen like we had it.

sqlite> .output /tmp/test.sql
sqlite> .dump
sqlite> .output stdout

To look at what the file looks like open a new shell and cat that file.

user@host:~$ cat /tmp/test.sql

BEGIN TRANSACTION;
CREATE TABLE test (ids integer primary key, value text);
INSERT INTO "test" VALUES(1,'value1');
INSERT INTO "test" VALUES(2,'value2');
INSERT INTO "test" VALUES(3,'value3');
INSERT INTO "test" VALUES(4,'value4');
CREATE VIEW testview AS select * from test;
CREATE INDEX testindex on test (value);
COMMIT;

The file contains all the information you need to re-create your database. From creating the tables to inserting all the data. This is used mostly to backup databases or to export the data from one database type to another. For backing up purposes it is usually easier to just copy the SQLite3 db file to another location.

Importing external data

If you would like to import the data you exported using the SQLite .dump command it's as easy as using the SQLite .read command. In the example below the first command makes a new database from the system command line (shell). The second command imports the data we exported in the previous example. The last command shows all the data in the new database.

user@host:~$ sqlite3 testrecover.db
sqlite> .read /tmp/test.sql
sqlite> select * from test;

ids         value
----------  ----------
1           value1
2           value2
3           value3
4           value4

If you want to import data that is in csv (comma separated value) format into a table you can do it using the SQLite .import command. The csv file has to use separators that SQLite understands. To see the current separators you can use the .show command. SQLite uses the pipe | as the default separator.

sqlite> .show

     echo: off
  explain: off
  headers: on
     mode: column
nullvalue: ""
   output: stdout
separator: "|"
    width:

To change the separator type use the .separator command. This will change it for output mode and for .import.

sqlite> .separator ,
sqlite> .show

     echo: off
  explain: off
  headers: on
     mode: column
nullvalue: ""
   output: stdout
separator: ","
    width:

Now that the separator is changed to a comma we can import data from an external csv file. We will import more data into our current table (test) with the .import command. Make sure your csv file sections match up with your tables and that you have the same number of columns. Also, note that when you export a file now it will be exported with a comma as the separator.

Here is what the test.csv file looks like.

5,value5
6,value6
7,value7

Here is the import from the csv file and what the test table looks like after import.

sqlite> .import /tmp/test.csv test
sqlite> select * from test;

ids         value
----------  ----------
1           value1
2           value2
3           value3
4           value4
5           value5
6           value6
7           value7

Removing tables, triggers, views, and indexes

To remove any index, table, trigger, or view you have made use the SQL drop command. The syntax is the word drop then what you want to drop and then the name of the thing you want to drop. Below is and example of dropping one of each kind.

drop index indexname
drop table tablename
drop trigger triggername
drop view viewname

Exiting

To exit out of the SQLite prompt use the SQLite .exit or .quit command. Both work the same way.

sqlite> .exit
user@host:~$

SQLite from the shell

Intro

The following commands will be using the statically linked SQLite command-line program. This is what was installed in the install section above. You can completely control an SQLite database with this program right from the Linux shell. No need to invoke the SQLite shell we introduced above. Being able to access SQLite databases from a shell lets you incorporate SQLite into your shell scripts without the need for external libraries.

We will use the same test.db database that we made in the examples above for this demo.

Usage options

The SQLite binary is for version 3 is called SQLite3. You can use this program from the command line just like any other UNIX program. If you want to look at the options the binary has just issue the --help option.

user@host:~$ sqlite3 --help

Usage: SQLite3 [OPTIONS] FILENAME [SQL]
FILENAME is the name of an SQLite database. A new database is created
if the file does not previously exist.
OPTIONS include:
   -init filename       read/process named file
   -echo                print commands before execution
   -[no]header          turn headers on or off
   -bail                stop after hitting an error
   -interactive         force interactive I/O
   -batch               force batch I/O
   -column              set output mode to 'column'
   -csv                 set output mode to 'csv'
   -html                set output mode to HTML
   -line                set output mode to 'line'
   -list                set output mode to 'list'
   -separator 'x'       set output field separator (|)
   -nullvalue 'text'    set text string for NULL values
   -version             show SQLite version

You can see syntax you need to use and the different options available.

NOTE THE ABOVE: If the database does not exist it will be created on any command given. Even on commands that fail.

Execute SQL statements

You can execute SQL statements right from the shell. Let's look at the data in our test database test.db. We are going to turn on headers and also turn on column mode so the ouput looks nice.

user@host:~$ sqlite3 -header -column test.db 'select * from test'

ids         value
----------  ----------
1           value1
2           value2
3           value3
4           value4
5           value5
6           value6
7           value7

You can execute any SQL statement against a database like this.

Execute SQLite commands

You can execute SQLite commands the same way you execute SQL commands. The example below uses the .schema SQLite command on the test.db file.

user@host:~$ sqlite3 -header -column test.db '.schema'

CREATE TABLE test (ids integer primary key, value text);
CREATE VIEW testview AS select * from test;
CREATE INDEX testindex on test (value);

Export a database

You can export (backup) your database using the command-line program and .dump. It works the same way it does from the SQLite shell. But we are going to want the output to go to a file and not the screen so we will redirect it to a file called dbbackup.

user@host:~$ sqlite3 test.db '.dump' > dbbackup

You could also compress the output before writing it disk by running .dump's ouput through gzip, bzip2, or whatever. The following is an example using bzip. It is highly recommended that you compress your dumps.

user@host:~$ sqlite3 test.db '.dump' | gzip -c > dbbackup.gz

Import a database

If you have used the SQLite .dump command we mentioned earlier to create a file with SQL statements you can input those statements into your database from the command line. This can be done with any SQL statements not just the ones from the .dump command. The following example will use a file called statements.sql and import them into our test.db file.

user@host:~$ sqlite3 test.db < statements.sql

To import that gzip compressed dump (backup) of SQL statements from above.

user@host:~$ gunzip -c dbbackup.gz | SQLite3 test.db

As a speed note. If your importing a lot of insert statements wrap those in a transaction to get a major speedup.

More information

Here are some great links from the SQLite website to help you further.

SQL commands understood by SQLite

SQLite understands most of the standard SQL language. But there is some syntax differences sometimes. There are also some SQL commands that SQLite has added that are unique. These are commands like VACUUM or PRAGMA. Make sure your look at the PRAGMA page as it is used to tweak settings in your SQLite database.

http://www.SQLite.org/lang.html

Functions

Core Functions, Aggregate functions, and Date time functions are all functions you can use with SQLite. You can also have an application define additional functions that are written in C and have them added to the database engine using the SQLite3_create_function() API.

http://www.sqlite.org/lang_corefunc.html

http://www.sqlite.org/lang_aggfunc.html

http://www.sqlite.org/lang_datefunc.html

Del.icio.us! | Digg Me! | Reddit!

Related stories


RSS Feed RSS feed logo
About


3com
3ware
alsa
alsactl
alsamixer
amd
android
apache
areca
arm
ati
auditd
awk
badblocks
bash
bind
bios
bonnie
cable
carp
cat5
cdrom
cellphone
centos
chart
cifs
cisco
cloudera
comcast
commands
comodo
compiz-fusion
corsair
cpufreq
cpufrequtils
cpuspeed
cron
crontab
crossover
cu
cups
cvs
database
dbus
dd
dd_rescue
ddclient
debian
decimal
dhclient
dhcp
diagnostic
diskexplorer
disks
dns
dos
dovecot
drac
dsniff
dvdauthor
e-mail
echo
editor
emerald
ethernet
expect
ext3
fat32
fedora
fetchmail
fiber
filesystems
firefox
firewall
flac
flexlm
floppy
flowtools
fonts
format
freebsd
ftp
gdm
gnome
greasemonkey
greylisting
growisofs
grub
hacking
hadoop
harddrive
hba
hex
hfsc
html
html5
http
idl
ie
ilo
intel
ios
iperf
ipmi
iptables
ipv6
irix
javascript
kde
kernel
kickstart
kmail
kprinter
krecord
kubuntu
kvm
lame
ldap
linux
logfile
lp
lpq
lpr
maradns
matlab
memory
mencoder
mhdd
mkinitrd
mkisofs
moinmoin
motherboard
mouse
movemail
mplayer
multitail
mutt
myodbc
mysql
mythtv
nagios
nameserver
netflow
nginx
nic
ntfs
ntp
nvidia
odbc
openbsd
openntpd
openoffice
openssh
openssl
opteron
parted
partimage
patch
perl
pf
pfflowd
pfsync
photorec
php
pop3
pop3s
ports
postfix
power
procmail
proftpd
proxy
pulseaudio
putty
pxe
python
qemu
r-studio
raid
recovery
redhat
router
rpc
rsync
samba
schedule
scsi
seagate
seatools
sed
sendmail
sgi
shell
siw
smtp
snort
solaris
soundcard
sox
spam
spamd
sql
sqlite
squid
ssh
ssh.com
ssl
su
subnet
subversion
sudo
sun
supermicro
switches
symbols
syslinux
syslog
systemrescuecd
t1
tcpip
tcpwrappers
telnet
terminal
testdisk
tftp
thttpd
thunderbird
timezone
ting
tools
tr
trac
tuning
tunnel
vi
wget
wiki
windows
windowsxp
wireless
wpa_supplicant
x
xauth
xfree86
xfs
xinearama
xmms
youtube
zdump
zic
zlib