UP | HOME

~/cstml/documents/cheatsheets/SQLite
Cheatsheets for SQLite.

Table of Contents

Headers

Command What it does
.help to display all the commands
.headers on Puts the headers at the top of each query
.mode column s

Structural Queries

Command What it does
.schema Displays the schema of the database
.schema tablename Displays the schema of the specific table in the database
.table Check the tables that are in the database

General commands

  • Creating a table
    • CREATE TABLE table_name (name1 TYPE1(size), name2 TYPE2());
    • CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, str TEXT);
  • Inserting into a table
    • INSERT INTO table name (name1, name2) VALUES (val1,val2),(val2,val3);
  • Selecting from a table
    • SELECT row FROM table_name WHERE row1 LIKE "%e_"
    • For more on the LIKE command SQLite LIKE
  • Data types in SQLite:
    • In SQLite there are 4 main data types and all the other ones are cast to the 4
      1. NULL
      2. INTEGER
        • INT
        • INTEGER
        • TINYINT
        • SMALLINT
        • MEDIUMINT
        • BIGINT
        • UNSIGNED BIG INT
        • INT2
        • INT8
        • SMALLINT
      3. BLOB
        • no data type
      4. REAL
        • REAL
        • DOUBLE
        • DOUBLE PRECISION
        • FLOAT
      5. NUMERIC
        • NUMERIC
        • DECIMAL(10,5)
        • BOOLEAN
        • DATE
        • DATETIME

Comments

  • are marked with --

Altering a table

  • you cannot alter a table in SQLite
  • you need to rename it - create a new table - copy across

SQLite LIKE

LIKE

  • % means as many characters before
  • _ means 1 character

GLOB

Uses the standard UNIX System for GLOB

  • [abc] any of the abc letters
  • [!abc] not in the list
  • [ :lower: ] matches lowercase letters
  • [ :upper: ] matches uppercase letters
  • [a-e] the list of letter between a-e
  • * as many letters (including 0)
  • ? matches 1 random character