Cheatography
https://cheatography.com
The most common and useful SQLite Commands (without JOINS)
Basics
|
Will create a new database |
Create Tables
CREATE TABLE name (
colname type constraints,
colname type constraints
)
This will create a new table. For types and constraints 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 expression
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 newtablename
Or add a new column with:
ALTER TABLE tablename ADD COLUMN columndefintion
It is not possible to change column definitions of existing tables. |
Select from Table
SELECT table1.column1, table.2column2,
FROM table1, table2
WHERE expression
ORDER BY table1.column1 order
The ORDER BY
is optional, possible ways to order are ASC
(ascending) and DESC
(descending). |
|
|
Data Types
|
null value |
|
Integer value |
|
8-byte floating point number |
|
Text with UTF encoding |
|
Blob-Data (stored like input) |
|
Will be stored as Integer (0 for false, 1 for true) |
Constraints
|
Sets column as primary key. The constraints NOT NULL
and UNIQUE
will be set automatically. |
|
Prevents the column from empty values ( null
). |
|
Each value in the column has to be unique. |
INTEGER PRIMARY KEY
s will automatically become the ROWID
and therefore will be automatically incremented. This column can be accessed as ROWID
, OID
, _ROWID_
or the column name.
|
Created By
blog.fetttobse.de
Metadata
Favourited By
Comments
No comments yet. Add yours below!
Add a Comment
Related Cheat Sheets