SQLite Basics

Discussion


On a Mac, SQLite is already installed. You can run sqlite3 and play at the interactive console. Here is the session where I play with Rails application development database in sqlite3 interactive console:

$cd ~/projects/lafon/db 
$sqlite3 development.sqlite3
SQLite version 3.7.13 2012-07-17 17:46:21
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select count(*) from articles;
25
sqlite> select id from articles;
15
20
22
26
...
more output snipped

Selecting a Record

sqlite> select * from articles where id=15;
15|TDD Basics : Writing your First Test|Pre-requisites
=========

  - Ruby 2 or above must be installed
  - Your favorite text editor
  - RSpec gem 2 or above. Testing

Objective
==========
...
more output snipped

Dot-Commands


We don't know the database fields from the output. Let's use the shell-specific command .headers:

sqlite> .headers on

sqlite> select * from articles where id=15;
id|title|body|created_at|updated_at
15|TDD Basics : Writing your First Test|Pre-requisites
=========

  - Ruby 2 or above must be installed
  - Your favorite text editor
  - RSpec gem 2 or above. Testing

Objective
==========
...
more output snipped

To view the help you can run the .help dot-command:

sqlite> .help

Show Tables


To view all tables:

sqlite> .tables
articles           users              schema_migrations

Formatting Output


Using the column mode, we see the results are displayed as a table:

sqlite> .mode column
sqlite> select id, name, email from users;
id          name        email     
----------  ----------  ----------
1           Duck        duck@examp
2           Bugs        bunny@disn
3           Carrot      lover@disn

The email column is snipped off after 10 characters.

Declarative vs Imperative


Jay A Kreibich in Using SQLite book says:

In a declarative language, you state what you want the results to be and allow the language processor to figure out how to deliver the desired results.
In imperative languages, each step of an operation must be explicitly written out, leaving it up to the programmer to lead the program, step by step, to the correct conclusion.

SQL is a declarative language whereas languages like C, C++, Perl, Java are imperative languages. Most SQL commands has the verb-subject form such as CREATE TABLE, DROP INDEX etc. The English-like, declarative nature of SQL makes it easy to understand.

Summary


In this article you learned the SQLite basics. You can learn more by experimenting in the SQLite interactive console. Read the documentation for the details of all the available commands.

Reference


Using SQLite by Jay A. Kreibich


Related Articles


Ace the Technical Interview

  • Easily find the gaps in your knowledge
  • Get customized lessons based on where you are
  • Take consistent action everyday
  • Builtin accountability to keep you on track
  • You will solve bigger problems over time
  • Get the job of your dreams

Take the 30 Day Coding Skills Challenge

Gain confidence to attend the interview

No spam ever. Unsubscribe anytime.