Show Menu

SQLite Cheat Sheet by

The most common and useful SQLite Commands (without JOINS)
sql     sqlite

Basics

sqlite filena­me.db
Will create a new database

Create Tables

CREATE TABLE name (
colname type constr­aints,
colname type constr­aints
)

This will create a new table. For types and constr­aints look right.

Insert Data

INSERT INTO tablename (column2, column4)
VALUES
(data1, data2),
(data3, data4);

You don't have to fill every column of the table. It is often useful to leave out columns with primary keys or default value insertion.

Update Tables

UPDATE tablename
SET column5 = value5
WHERE expres­sion

This will update the given column with the given value, where the expression evaluates true (usually specific primary key).
 

Alter Tables

The ALTER TABLE function is limited in SQLite.

You can either rename the table with:
ALTER TABLE tablename RENAME TO newtab­lename

Or add a new column with:
ALTER TABLE tablename ADD COLUMN column­def­intion

It is not possible to change column defini­tions of existing tables.

Drop Table

DROP TABLE tablename

Select from Table

SELECT table1.co­lumn1, table.2­co­lumn2,
FROM table1, table2
WHERE expres­sion
ORDER BY table1.co­lumn1 order

The ORDER BY is optional, possible ways to order are ASC (ascen­ding) and DESC (desce­nding).
 

Data Types

NULL
null value
INTEGER
Integer value
REAL
8-byte floating point number
TEXT
Text with UTF encoding
BLOB
Blob-Data (stored like input)
BOOLEAN
Will be stored as Integer (0 for false, 1 for true)

Constr­aints

PRIMARY KEY
Sets column as primary key. The constr­aints NOT NULL and UNIQUE will be set automa­tic­ally.
NOT NULL
Prevents the column from empty values (null).
UNIQUE
Each value in the column has to be unique.
INTEGER PRIMARY KEYs will automa­tically become the ROWID and therefore will be automa­tically increm­ented. This column can be accessed as ROWID, OID, _ROWID_ or the column name.

Download the SQLite Cheat Sheet

1 Page
//media.cheatography.com/storage/thumb/fetttobse_sqlite.750.jpg

PDF (recommended)

Alternative Downloads

Share This Cheat Sheet!

 

Comments

No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          Related Cheat Sheets

          SQL Server Cheat Sheet
          Essential MySQL Cheat Sheet
          Oracle SQL Developer Keyboard Shortcuts