Vicente Hernando's web site

SQLite is a database engine contained in a C library that usually stores its data in a file.

sqlite3 is the command line interface for SQLite version 3.

Install and open a database file

Install sqlite3 (in Debian):
$ sudo aptitude install sqlite3

Open a database file:
$ sqlite3 database_file.db
SQLite version 3.8.8.3 2015-02-25 13:29:11
Enter ".help" for usage hints.

A prompt appears:
sqlite>

SQLITE3 COMMANDS

Show all tables
sqlite> .tables
NAMES CLIENTS SALES PRODUCTS

Quit
sqlite> .quit


Show help:
sqlite> .help
.backup ?DB? FILE Backup DB (default "main") to FILE
.bail on|off Stop after hitting an error. Default OFF
.clone NEWDB Clone data into NEWDB from the existing database
.databases List names and files of attached databases
.dump ?TABLE? ... Dump the database in an SQL text format
If TABLE specified, only dump tables matching
LIKE pattern TABLE.
.echo on|off Turn command echo on or off
....



List all databases and their associated files:
sqlite> .databases


Show schema of all tables:
sqlite> .schema

Show schema of a table:
sqlite> .schema PRODUCTS
CREATE TABLE "PRODUCTS" (Id INTEGER PRIMARY KEY AUTOINCREMENT ,TStamp INTEGER NOT NULL ,Name VARCHAR(60) NOT NULL ,Price DECIMAL(10,5) NOT NULL);

Execute an UNIX command:
sqlite>.system ls -l

SQLite Datatypes

SQlite uses dynamic typing. These are the possible storage classes:

NULL: a NULL value.

INTEGER: a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.

REAL: a floating point value, stored as an 8-byte IEEE floating point number.

TEXT: a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).

BLOB: a blob of data, stored exactly as it was input.

Datatypes in SQLite3


Other types like VARCHAR(N), DECIMAL(N, M) are assimilated to the former storage classes by their affinity (VARCHAR to TEXT, DECIMAL to INTEGER), so their limits (N, M..) are not enforced.

SQL

Attach another database (client) from other file (client.db):
sqlite> ATTACH DATABASE 'client.db' AS client;
Now .database command shows client database too.
We can access client database tables preceded by 'client.' prefix.

Detach a previously attached database:
sqlite> DETACH DATABASE client;

Create a new table:
sqlite> CREATE TABLE VARS (name VARCHAR(128) PRIMARY KEY ,value VARCHAR(512) NOT NULL);

Create the table only if it does not already exist:
sqlite> CREATE TABLE IF NOT EXISTS VARS (name VARCHAR(128) PRIMARY KEY ,value VARCHAR(512) NOT NULL);

Show contents of a table:
sqlite> select * from CLIENTS;
1|Vincent
2|John

Show contents of a table limiting number of rows in the result:
sqlite> select * from CLIENTS LIMIT 3
Display at most three rows.
This LIMIT keyword can be used along ORDER BY to specify a column to order results.
sqlite> select * from CLIENTS ORDER BY Name DESC LIMIT 3


Insert a row in a table:
sqlite> INSERT INTO VARS (name,value) VALUES('color', 'blue');

Insert a row in a table, but if a conflict arises because of a UNIQUE or PRIMARY KEY constraint then replace that row:
sqlite> INSERT OR REPLACE INTO VARS (name,value) VALUES('color', 'blue');
SQL As Understood By SQLite (On conflict)


Copy contents from a table into another one:
sqlite> INSERT INTO CLIENT SELECT * FROM CLIENT_OLD;
Columns returned by select have to match elements expected by insert.
SQL As Understood By SQLite (Insert)

Copy some contents from a table into another one:
sqlite> INSERT INTO CLIENT (name,value,state,address) SELECT name,value,-1,address FROM CLIENT_OLD;


Delete a table:
sqlite> drop table ticket;

Rename a table:
sqlite> .tables
CASHIER
sqlite> alter table CASHIER rename to CLIENTS;
sqlite> .tables
CLIENTS

Add a new column to a table

We add a new Difficulty column to this table:
sqlite> .schema ANALUX
CREATE TABLE ANALUX (Height INTEGER PRIMARY KEY ,Price REAL DEFAULT NULL);

Alter command adds the new column:
sqlite> ALTER TABLE ANALUX ADD Difficulty REAL DEFAULT NULL;

sqlite> .schema ANALUX
CREATE TABLE ANALUX (Height INTEGER PRIMARY KEY ,Price REAL DEFAULT NULL ,Difficulty REAL DEFAULT NULL);


If we want to fill Difficult column with data from other table e.g: BLOCK where Height is primary key for both tables:
sqlite> UPDATE ANALUX SET Difficulty = (SELECT Difficulty FROM BLOCK WHERE BLOCK.Height = ANALUX.Height);

Transactions

Start a transaction:
sqlite>BEGIN

Perform some operations...

Commit the transaction:
sqlite>COMMIT

If something was wrong you can roll back the transaction instead of committing it.
sqlite>ROLLBACK

Execute a SQL command in command line

Insert a row in a table:
(Note we have to escape double quotes)
$ sqlite3 foo.db "INSERT INTO CASHIER VALUES (\"foo\",\"bar\");"

Execute a SQL script in command line

If we write a SQL script in a file named example.sql like this:

CREATE TABLE CASHIER (name    VARCHAR(10) PRIMARY KEY ,pass    VARCHAR(10) NOT NULL);

INSERT INTO CASHIER VALUES ("foo","bar");

SELECT * FROM CASHIER;

We can execute it:
$ sqlite3 foo.db < example.sql


Or using a here document in a shell script:

E.g: example.sh file

#!/bin/bash



sqlite3 foo.db <<EOF

CREATE TABLE CASHIER (name    VARCHAR(10) PRIMARY KEY ,pass    VARCHAR(10) NOT NULL);

INSERT INTO CASHIER VALUES ("foo","bar");

SELECT * FROM CASHIER;

EOF

Then we execute it:
$ bash example.sh

Show execution time of a query

.timer command shows execution time of a query. ON to enable or OFF to disable it.
sqlite> .timer ON

sqlite> SELECT * FROM CASHIER;
...
Run Time: real 0.326 user 0.000523 sys 0.000261

To disable it:
sqlite> .timer OFF

Explain a SQL query

We can get a high level description of how a SQL query is internaly executed.
https://www.sqlite.org/eqp.html

We add EXPLAIN QUERY PLAN at the beginning of the query:
sqlite> EXPLAIN QUERY PLAN SELECT * FROM ADDRESS;

QUERY PLAN
`--SCAN TABLE ADDRESS

REFERENCE

SQLite core functions

SQLite Frequently Asked Questions

SQLite Command Line Interface