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 xCREATE
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 KurosawaWHERE
Query data and filter results with a condition.
sqlite> SELECT firstname, lastname FROM director WHERE lastname = 'Kurosawa' ;
firstname lastname
---------- ----------
Kiyoshi Kurosawa
Akira KurosawaCan 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 BongCan 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 KurosawaLIKE
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 ParkCan be combined with NOT.
sqlite> SELECT firstname, lastname FROM director WHERE lastname NOT LIKE 'K%' ;
firstname lastname
---------- ----------
Chan-wook Park
Terrence Malick
Joon-ho BongUPDATE
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 LineDELETE
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 OldboyORDER 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 EarthIf 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 ParkASC / 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 LifeJOIN
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 KurosawaIt 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 ParkUNION
Will be done one day…