Commands in SQLite

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.

 

Please Share