SQL commands cheatsheet by Ypnose

The commands where successfully tested on SQLite 3.31.1.

Data Types

Numeric

                   INT  |  Numeric value from -2,147,483,648 to 2,147,483,647
               INTEGER  |  Same as INT
               TINYINT  |  Numeric value from 0 to 255
              SMALLINT  |  Numeric value from -32,768 to 32,767
                BIGINT  |  Numeric value from 9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
 NUMERIC ( p [ , e ] )  |  Number of p digits including e digits to the right of the decimal point
 DECIMAL ( p [ , e ] )  |  Same as NUMERIC ( p [ , e ] )
                 FLOAT  |  Floating-point number

Character / String

              CHAR (n)  |  Fixed-size string data, n defines the string size in bytes
         CHARACTER (n)  |  Same as CHAR (n)
           VARCHAR (n)  |  Variable-size string data. n defines the string size in bytes
                  TEXT  |  Variable-length non-Unicode data with a maximum string length of 2,147,483,647

Date / Time

                  DATE  |  Date in the format YYYY-MM-DD (ISO 8601)
                  TIME  |  Time in the format HH:MM:SS
             TIMESTAMP  |  Number of seconds passed since the Unix epoch

Database example

DROP TABLE IF EXISTS asso_movie_director ;
DROP TABLE IF EXISTS movie ;
DROP TABLE IF EXISTS director ;

CREATE TABLE director
(
    id          INTEGER         PRIMARY KEY ,
    lastname    VARCHAR (12)    NOT NULL ,
    firstname   VARCHAR (12)    NOT NULL
);

CREATE TABLE movie
(
    id          INTEGER         PRIMARY KEY ,
    title       VARCHAR (30)    NOT NULL
);

CREATE TABLE asso_movie_director
(
    movie_id        INTEGER     NOT NULL REFERENCES movie ( id ) ,
    director_id     INTEGER     NOT NULL REFERENCES director ( id )
);

INSERT INTO director
    ( id    , lastname      , firstname     ) VALUES
    ( 1     , 'Park'        , 'Chan-wook'   ) ,
    ( 2     , 'Kurosawa'    , 'Kiyoshi'     ) ,
    ( 3     , 'Malick'      , 'Terrence'    ) ,
    ( 4     , 'Bong'        , 'Joon-ho'     ) ,
    ( 5     , 'Kurosawa'    , 'Akira'       ) ;

INSERT INTO movie
    ( id    , title                         ) VALUES
    ( 1     , 'The Thin Red Line'           ) ,
    ( 2     , 'Creepy'                      ) ,
    ( 3     , 'The Handmaiden'              ) ,
    ( 4     , 'Song to Song'                ) ,
    ( 5     , 'Snowpiercer'                 ) ,
    ( 6     , 'Before We Vanish'            ) ,
    ( 7     , 'To the Ends of the Earth'    ) ,
    ( 8     , 'Foreboding'                  ) ,
    ( 9     , 'Parasite'                    ) ,
    ( 10    , 'A Hidden Life'               ) ,
    ( 11    , 'Oldboy'                      ) ,
    ( 12    , 'The Hidden Fortress'         ) ;

INSERT INTO asso_movie_director
    ( movie_id  , director_id    ) VALUES
    ( 1         , 3              ) ,
    ( 2         , 2              ) ,
    ( 3         , 1              ) ,
    ( 4         , 3              ) ,
    ( 5         , 4              ) ,
    ( 6         , 2              ) ,
    ( 7         , 2              ) ,
    ( 8         , 2              ) ,
    ( 9         , 4              ) ,
    ( 10        , 3              ) ,
    ( 11        , 1              ) ,
    ( 12        , 5              ) ;

Commands

Some useful sqlite meta-commands :

.headers on
.mode column
.nullvalue NULL
.width x

CREATE

Create a new table.

sqlite> CREATE TABLE director
   ...> (
   ...> id          INTEGER         PRIMARY KEY ,
   ...> lastname    VARCHAR (12)    NOT NULL ,
   ...> firstname   VARCHAR (12)    NOT NULL
   ...> );

Constraints

               DEFAULT  |  Default value for a column if not specified
                  NULL  |  Column can contain NULL value (default)
              NOT NULL  |  Column cannot contain NULL value
           PRIMARY KEY  |  Column is the primary key
            REFERENCES  |  Column is a foreign key
                UNIQUE  |  Column values have to be different

INSERT

Insert data in one or more rows.

sqlite> INSERT INTO director VALUES
   ...> ( 1     , 'Park'        , 'Chan-wook'   ) ,
   ...> ( 2     , 'Kurosawa'    , 'Kiyoshi'     ) ;

DROP

Delete a table from the database. Be careful, all data in the table will be DELETED.

sqlite> DROP TABLE director ;

Can be combined with IF EXISTS to avoid error if the table is missing.

sqlite> DROP TABLE IF EXISTS director ;

SELECT

Query data from a table.

sqlite> SELECT firstname, lastname FROM director ;
firstname   lastname
----------  ----------
Chan-wook   Park
Kiyoshi     Kurosawa
Terrence    Malick
Joon-ho     Bong
Akira       Kurosawa

WHERE

Query data and filter results with a condition.

sqlite> SELECT firstname, lastname FROM director WHERE lastname = 'Kurosawa' ;
firstname   lastname
----------  ----------
Kiyoshi     Kurosawa
Akira       Kurosawa

Can be combined with AND, OR or NOT.

sqlite> SELECT firstname, lastname FROM director WHERE lastname = 'Malick' OR firstname = 'Joon-ho' ;
firstname   lastname
----------  ----------
Terrence    Malick
Joon-ho     Bong

Can be also combined with IS NULL and IS NOT NULL.

sqlite> SELECT firstname, lastname FROM director WHERE firstname IS NOT NULL ;
firstname   lastname
----------  ----------
Chan-wook   Park
Kiyoshi     Kurosawa
Terrence    Malick
Joon-ho     Bong
Akira       Kurosawa

LIKE

Query data using patter matching. - matches an unique character, % matches any characters (one or more).

sqlite> SELECT firstname, lastname FROM director WHERE firstname LIKE 'C%' ;
firstname   lastname
----------  ----------
Chan-wook   Park

Can be combined with NOT.

sqlite> SELECT firstname, lastname FROM director WHERE lastname NOT LIKE 'K%' ;
firstname   lastname
----------  ----------
Chan-wook   Park
Terrence    Malick
Joon-ho     Bong

UPDATE

Modify one or more columns of line according to the WHERE criteria. Be careful, if WHERE is missing, all lines will be modified.

sqlite> .width 0 30
sqlite> SELECT * FROM movie WHERE title LIKE 'The Thi%' ;
id          title
----------  ------------------------------
1           The Thin Red Line
sqlite> UPDATE movie SET title = 'The Thick Red Line' WHERE id = 1 ;
sqlite> SELECT * FROM movie WHERE title LIKE 'The Thi%' ;
id          title
----------  ------------------------------
1           The Thick Red Line

DELETE

Delete table lines according to the WHERE criteria. Be careful, if WHERE is missing, all lines will be deleted.

sqlite> .width 0 30
sqlite> SELECT * FROM movie WHERE title LIKE '% Hidden%' ;
id          title
----------  ------------------------------
10          A Hidden Life
12          The Hidden Fortress
sqlite> DELETE FROM movie WHERE id = 10 OR id = 12 ;
sqlite> SELECT * FROM movie ;
id          title
----------  ------------------------------
1           The Thick Red Line
2           Creepy
3           The Handmaiden
4           Song to Song
5           Snowpiercer
6           Before We Vanish
7           To the Ends of the Earth
8           Foreboding
9           Parasite
11          Oldboy

ORDER BY

Sort results for one or more columns. The sorting is done for the first column.

sqlite> .width 30
sqlite> SELECT title FROM movie ORDER BY title ;
title
------------------------------
A Hidden Life
Before We Vanish
Creepy
Foreboding
Oldboy
Parasite
Snowpiercer
Song to Song
The Handmaiden
The Hidden Fortress
The Thin Red Line
To the Ends of the Earth

If the result is identical, it is done for the second column and so on. A valid SQL query with ORDER BY should always mention all columns.

sqlite> SELECT firstname, lastname FROM director ORDER BY lastname, firstname ;
firstname   lastname
----------  ----------
Joon-ho     Bong
Akira       Kurosawa
Kiyoshi     Kurosawa
Terrence    Malick
Chan-wook   Park

ASC / DESC

Can be used with ORDER BY to sort the results in ASC ascending (default) or DESC descending order.

sqlite> .width 30
sqlite> SELECT title FROM movie ORDER BY title DESC ;
title
------------------------------
To the Ends of the Earth
The Thin Red Line
The Hidden Fortress
The Handmaiden
Song to Song
Snowpiercer
Parasite
Oldboy
Foreboding
Creepy
Before We Vanish
A Hidden Life

JOIN

Combine data from two or more tables.

sqlite> .width 30
sqlite> SELECT movie.title , director.firstname , director.lastname
   ...> FROM asso_movie_director
   ...> JOIN movie ON movie.id = asso_movie_director.movie_id
   ...> JOIN director ON director.id = asso_movie_director.director_id ;
title                           firstname   lastname
------------------------------  ----------  ----------
The Thin Red Line               Terrence    Malick
Creepy                          Kiyoshi     Kurosawa
The Handmaiden                  Chan-wook   Park
Song to Song                    Terrence    Malick
Snowpiercer                     Joon-ho     Bong
Before We Vanish                Kiyoshi     Kurosawa
To the Ends of the Earth        Kiyoshi     Kurosawa
Foreboding                      Kiyoshi     Kurosawa
Parasite                        Joon-ho     Bong
A Hidden Life                   Terrence    Malick
Oldboy                          Chan-wook   Park
The Hidden Fortress             Akira       Kurosawa

It can be then used with ORDER BY.

sqlite> SELECT movie.title , director.firstname , director.lastname
   ...> FROM asso_movie_director
   ...> JOIN movie ON movie.id = asso_movie_director.movie_id
   ...> JOIN director ON director.id = asso_movie_director.director_id
   ...> ORDER BY director.lastname , director.firstname , movie.title ;
title                           firstname   lastname
------------------------------  ----------  ----------
Parasite                        Joon-ho     Bong
Snowpiercer                     Joon-ho     Bong
The Hidden Fortress             Akira       Kurosawa
Before We Vanish                Kiyoshi     Kurosawa
Creepy                          Kiyoshi     Kurosawa
Foreboding                      Kiyoshi     Kurosawa
To the Ends of the Earth        Kiyoshi     Kurosawa
A Hidden Life                   Terrence    Malick
Song to Song                    Terrence    Malick
The Thin Red Line               Terrence    Malick
Oldboy                          Chan-wook   Park
The Handmaiden                  Chan-wook   Park

UNION

Will be done one day…