SQLite Commands
SQLite commands can be either of the three types:
1. Data Definition Language (DDL):
It includes three commands:
CREATE:
To create a table, a view of a table or other object in the database.
ALTER:
To modify an existing database object like a table.
DROP:
To delete an entire table, a view of a table or other object in the database.
2. Data Manipulation Language (DML):
It also includes three commands:
INSERT:
To create a record.
UPDATE:
To modify the records.
DELETE:
To delete records.
3. Data Query Language (DQL):
It includes only one command:
SELECT:
To retrieve certain records from one or more tables.
SQLite dot Commands:
The dot commands are not terminated by a semicolon (;). To check the list of dot commands use the “.help” command. Below is a list of some popular SQLite dot commands.
|
COMMAND |
USES |
|
.backup ?db? file |
To backup DB to file. |
|
.bail on|off |
Stops after hitting an error. Its default state is off. |
|
.databases |
To list the names and the files of the attached databases. |
|
.dump ?table? |
To dump the database in an sql text format. It only dumps the tables matching like pattern table, if it is specified. |
|
.echo on|off |
To turn the command echo on or off. |
|
.exit |
To exit the sqlite prompt. |
|
.explain on|off |
To turn the output mode suitable for explain on or off. Its default state is on. |
|
.header(s) on|off |
To turn the display of headers on or off. |
|
.help |
To check the list of dot commands. |
|
.import file table |
To import data from file into table. |
|
.indices ?table? |
To show names of all indices or to show the indices for tables matching like pattern table, if thee table iis specified. |
|
.load file ?entry? |
To load an extension library. |
|
.log file|off |
To turn logging on or off. |
|
.mode mode |
To set the output mode. |
|
.nullvalue string |
To print string in place of null values. |
|
.output filename |
To send output to a file. |
|
.output stdout |
To send output to the screen. |
|
.print string… |
To print literal string. |
|
.prompt main continue |
To replace the standard prompts. |
|
.quit |
To exit the sqlite prompt. |
|
.read filename |
To execute the sql in filename. |
|
.schema ?table? |
To show the create statements. |
|
.separator string |
To change the separator used by output mode and .import. |
|
.show |
To show the current values for various settings. |
|
.stats on|off |
To turn the stats on or off. |
|
.tables ?pattern? |
To list names of tables matching like a pattern. |
|
.timeout ms |
To try opening locked tables for ms milliseconds. |
|
.width num num |
To set the column widths for “column” mode. |
|
.timer on|off |
To turn the cpu timer measurement on or off. |
Special dot commands:
There are three special dot commands including; .header on, .mode column and .timer on. There are special as they are used to format the output.