Cheatography
https://cheatography.com
The most common and useful SQLite Commands (without JOINS)
Basicssqlite filename.db
| Will create a new database |
Create TablesCREATE TABLE name (
colname type constraints,
colname type constraints
)
This will create a new table. For types and constraints look right.
|
Insert DataINSERT 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 TablesUPDATE 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 TablesThe 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 TableSELECT 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 TypesNULL
| 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) |
ConstraintsPRIMARY KEY
| Sets column as primary key. The constraints NOT NULL and UNIQUE will be set automatically. | NOT NULL
| Prevents the column from empty values (null). | UNIQUE
| Each value in the column has to be unique. |
INTEGER PRIMARY KEYs 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