{"id":1235,"date":"2017-06-09T22:18:21","date_gmt":"2017-06-09T15:18:21","guid":{"rendered":"http:\/\/www.sqlitetutorial.net\/?page_id=1235"},"modified":"2024-05-08T22:32:27","modified_gmt":"2024-05-08T15:32:27","slug":"sqlite-cheat-sheet","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-cheat-sheet\/","title":{"rendered":"SQLite Cheat Sheet"},"content":{"rendered":"\n

SQLite cheat sheet<\/strong> lists the most common SQLite statements that help you work with SQLite more quickly and effectively.<\/p>\n\n\n\n

Managing databases<\/h2>\n\n\n\n

Attach another database to the current database connection:<\/p>\n\n\n

ATTACH DATABASE file_name AS database_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Optimize the database:<\/p>\n\n\n
VACUUM<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Managing Tables<\/h2>\n\n\n\n
Create a new table:<\/a><\/p>\n\n\n
CREATE<\/span> TABLE<\/span> [IF<\/span> NOT<\/span> EXISTS<\/span>] table<\/span>(\n   primary_key INTEGER<\/span> PRIMARY KEY<\/span>,\n   column_name type<\/span> NOT<\/span> NULL<\/span>,\n   column_name type<\/span> NULL<\/span>,\n   ...\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Rename a table:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> table_name RENAME<\/span> TO<\/span> new_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Add a new column to a table:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> table<\/span> ADD<\/span> COLUMN<\/span> column_definition;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Drop an existing column in a table:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> table<\/span> DROP<\/span> COLUMN<\/span> column_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Drop a table and its data:<\/p>\n\n\n
DROP<\/span> TABLE<\/span> [IF<\/span> EXISTS<\/span>] table_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Managing indexes<\/h2>\n\n\n\n
Creating an index<\/a><\/p>\n\n\n
CREATE<\/span> [UNIQUE<\/span>] INDEX<\/span> index_name\nON<\/span> table_name (c1,c2,...)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Delete an index:<\/p>\n\n\n
DROP<\/span> INDEX<\/span> index_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Create an expression index:<\/p>\n\n\n
CREATE<\/span> INDEX<\/span> index_name ON<\/span> table_name(expression);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Querying Data<\/h2>\n\n\n\n
Query all data<\/a> from a table<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> table_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Query data from the specified column of a table:<\/p>\n\n\n
SELECT<\/span> c1, c2\nFROM<\/span> table_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> DISTINCT<\/span> (c1)\nFROM<\/span> table_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> *\nFROM<\/span> table_name\nWHERE<\/span> condition;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Rename the column in the query’s output:<\/p>\n\n\n
SELECT<\/span> c1 AS<\/span> new_name\nFROM<\/span> table_name;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> * \nFROM<\/span> table_name_1\nINNER<\/span> JOIN<\/span> table_name_2 ON<\/span> condition;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n
SELECT<\/span> * \nFROM<\/span> table_name_1\nLEFT<\/span> JOIN<\/span> table_name_2 ON<\/span> condition;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> COUNT<\/span> (*)\nFROM<\/span> table_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> c1, c2\nFROM<\/span> table_name\nORDER<\/span> BY<\/span> c1 ASC<\/span> [DESC<\/span>], c2 ASC<\/span> [DESC<\/span>],...;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> *\nFROM<\/span> table_name\nGROUP<\/span> BY<\/span> c1, c2, ...;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> c1, aggregate<\/span>(c2)\nFROM<\/span> table_name\nGROUP<\/span> BY<\/span> c1\nHAVING<\/span> condition;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Changing Data<\/h2>\n\n\n\n
Insert a row into a table<\/a>:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> table_name(column1,column2,...)\nVALUES<\/span>(value_1,value_2,...);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Insert multiple rows into a table in a single statement:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> table_name(column1,column2,...)\nVALUES<\/span>(value_1,value_2,...),\n      (value_1,value_2,...),\n      (value_1,value_2,...)...\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
UPDATE<\/span> table_name\nSET<\/span> c1 = v1,\n    ...<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Update rows that match with a condition:<\/p>\n\n\n
UPDATE<\/span> table_name\nSET<\/span> c1 = v1,\n    ...\nWHERE<\/span> condition;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
DELETE<\/span> FROM<\/span> table<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Delete rows specified by a condition:<\/p>\n\n\n
DELETE<\/span> FROM<\/span> table<\/span>\nWHERE<\/span> condition;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Search<\/h2>\n\n\n\n
Search using the LIKE operator<\/a>:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> table<\/span>\nWHERE<\/span> column<\/span> LIKE<\/span> '%value%'<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> * \nFROM<\/span> table<\/span>\nWHERE<\/span> table<\/span> MATCH<\/span> 'search_query'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>
\n\t
\n\t\t
Was this tutorial helpful ?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
Search using full-text search<\/a>:<\/p>\n\n\n
Delete all rows in a table<\/a><\/p>\n\n\n
Update all rows in a table<\/a>:<\/p>\n\n\n
Filter group of rows using HAVING<\/a> clause.<\/p>\n\n\n
Group rows using the GROUP BY<\/a> clause.<\/p>\n\n\n
Sort rows using the ORDER BY<\/a> clause:<\/p>\n\n\n
Count<\/a> rows returned by a query:<\/p>\n\n\n
Query data from multiple tables using inner join<\/a> and left join<\/a><\/p>\n\n\n
Query rows that match a condition using a WHERE clause<\/a>.<\/p>\n\n\n
Query unique<\/a> rows<\/p>\n\n\n