SQLite Reference

This page may be updated as we cover more features of SQLite.

Official Documentation

Here are links to different sections of the official SQLite documentation.

Command Line Shell

External Tutorials

This tutorial has nice concise well-written information.

Interactive Mode

Use the sqlite3 command line tool to open an interactive session. An interactive session opens up a shell where you can run SQL statements and SQLite commands. The most common useful features are described below. See the official Command Line Shell documentation for more detail.

Entering Interactive Mode

If you want interact with the database stored in the file db.sqlite, run the following:

$ sqlite3 db.sqlite

If db.sqlite does not yet exist, it will be created for you as an empty database. Once you have entered the interactive shell you should see something like this:

SQLite version 3.13.0 2016-05-18 10:57:30
Enter ".help" for usage hints.
sqlite>

Running SQLite Commands

Special SQLite commands (called dot commands in the documentation) start with a dot (.). Running the dot command .help will list all of the available dot commands.

When working with small databases it is nice if the results of a SELECT statment has the columns line up and there is a header row. These two dot commands will enable this behavior for your session:

sqlite> .mode column
sqlite> .headers on

Executing SQL Statements

To execute a SQL statement, simply type it in and press enter. You can create a table named test with the integer field f1 and the text field f2 like so:

sqlite> CREATE TABLE test(f1 INTEGER, f2 TEXT);

You can now insert some rows into this table:

sqlite> INSERT INTO test VALUES(100, 'hello');
sqlite> INSERT INTO test VALUES(5, 'goodbye');

See the current rows of the table:

sqlite> SELECT * from TEST;
100|hello
5|goodbye

Each row is displayed with the fields separated by the | character.

Exiting the Shell

To exit the shell you can either run .exit or press Ctrl + d.

Non-interactive Mode

You can run an SQL statement on the command line without entering the interactive shell simply by adding the SQL statement in quotation marks after the name of the database:

$ sqlite3 db.sqlite "SELECT * FROM test"
100|hello
5|goodbye

You can also run the SQL statements contained in a file by directing the contents of the file to the standard input of sqlite3 like this:

$ sqlite3 db.sqlite < commands.sql

Constraints

To create a primary key attribute for a table, use the INTEGER PRIMARY KEY type:

CREATE TABLE student(student_id INTEGER PRIMARY KEY, name TEXT);

Now when inserting you can insert all of the attributes except the student_id field:

INSERT INTO student(name) VALUES('Ada Lovelace');

This will automatically generate a unique student_id for this row.

Other constraints, such as foreign key constraints, are specified at the end of the list of attributes in the CREATE TABLE statement. If there is another table named faculty whose primary key is faculty_id, then we can represent advising relationships in a table named advises like so:

CREATE TABLE advises(faculty_id INTEGER, student_id INTEGER,
                     FOREIGN KEY(faculty_id) REFERENCES faculty(faculty_id),
                     FOREIGN KEY(student_id) REFERENCES student(student_id));

Now to view each pair of names in each faculty/student advising relationship, we can join the tables together with this query:

SELECT faculty.name, student.name
FROM faculty, student, advises
WHERE faculty.faculty_id = advises.faculty_id
      AND student.student_id = advises.student_id;

CS 232