SQLite

SQLite Command Line Interface

• SQLite is a DBMS that stores the database in a single file

• SQLite has a command line interface CLI to interact with a database file

• The command to start the CLI on the Unix server is:

  $ sqlite3
  SQLite version 3.6.20
  Enter ".help" for instructions
  Enter SQL statements terminated with a ";"
  sqlite>

• Note that the dollar sign indicates the Unix command line prompt.

SQLite commands

• .show: displays the current settings for various paramters

• .quit: quit the CLI program

• .tables: show the tables in the database

• .schema: display the table schema

• .header: display or hide the column headers

• .mode: select the mode for output

• .dump: dump the database as SQL text

SQLite Datatypes

• NULL: the value is NULL

• INTEGER: the NULL value

• REAL: the value is a floating point number

• TEXT: the value is a text string

• BLOB: the value is a blob of data

Create a New Database File

• Create a database file named example.db

  $ sqlite3 example.db

• Note that the dollar sign indicates the Unix command line prompt.

Import Data from a CSV File

• Import data from a file named data.csv into a table named example_table (this assumes that the table exists)

  sqlite> .mode csv
  sqlite> .import data.csv example_table

Execute SQL Statements From a File

• Execute a SQL statement stored in a file named example.sql

  sqlite> .read example.sql

Dump the Database to a File

• Dump the SQL statements to recreate the database to a file

  sqlite> .output example.sql
  sqlite> .dump
  sqlite> .exit

• Dump a specific table

  sqlite> .output example.sql
  sqlite> .dump users
  sqlite> .exit

• Dump the schema

  sqlite> .output example.sql
  sqlite> .schema
  sqlite> .exit

Print Columns with Headers

• Print SQL query results in tabular form with column headers

  sqlite> .header on
  sqlite> .mode column