SQLite Commands

Definition of SQLite Commands

SQLite is a lightweight database and it provides different useful commands to the SQLite developers. These all commands are also called dot commands and they are not terminated by using a semicolon (;). SQLite database provides simple command line commands that are helpful to users to manually execute the different SQL statements. If we need to start the SQLite then we can just type SQLite on command prompt, as well as we open with the SQLite database name but if SQLite database does not exist then it will be automatically created. We can also create a temporary database then it is automatically deleted when we exit the SQLite.

Commands of SQLite

Now let’s see the different useful commands of SQLite as follows.

If we need to know the currently installed version of the SQLite database at that time we can execute the following command as follows.

Example

sqlite3

 Explanation: In the above example we use sqlite3 command to see the currently installed version of SQLite. The output of the above command is illustrated by using the following screenshot.

If we need to check what dot commands are available then we can use the following command as follows.

Example

.help

Explanation: When we execute the above command then it shows the different commands listed as follows.

• .backup? DB? FILE: The above command is used to back up the database into the main file and it is a default file.
• .bail ON|OFF: This command is used to stop the execution when an error may occur and by default, it is off.
• .database: The above-mentioned command of SQLite database is used to list all names and files that are related to the SQLite database.
• .dump? Table?: Basically dump database command is an SQL text format and uses the LIKE clauses to match table patterns.
• .echo ON | OFF: This command turns SQL command on or off, we set echo on any SQL commands before execution.
• .exit: The .exit command is used to exit the SQLite prompt id we required.
• .explain ON | OFF: The above command is used to turn the explain mode on or off, when we turn on then there is no argument in the SQL statement.
• .header(s) ON | OFF: This command is used to display the headers we can turn on or off as per the requirement.
• .import FILE TABLE: If we need to import the data from a text file into the table at that time we can use the above command.
• .indices? TABLE ?: The above command is used to show all names of indices that are present in the database. If we specify the table then it shows the only matching indices by using the LIKE pattern.
• .load FILE ? ENTRY ?: Sometimes if we need to load some library at that time we can use the above-mentioned commands.
• .log FILE | off: We can turn on or off log files by using this command.
• .mode MODE: By using this command we set the output mode of the SQL statement, different output modes are available as follows.
  • CSV: The CSV means Comma-separated values that mean we store data in the tabular format.
  • column: if we need to align data in some way at that time we can use this output mode and basically column is used to left-aligned.
  • html: if we need to show output in html mode at that time we can use this output mode that is html <table> code.
  • line: If we need to show output per line that means a single value per line at that we can use this output mode.
  • list: If we need to show output in array format that means values delimited by using a separator string, then we can use this output mode.
  • tabs: This output mode is used to separate values by using the tab mode.
  • tcl: if we need to show output in the list at that time we use tcl output mode.

• .null value STRING: If we need to print string values instead of null values at that time we can use this SQLite command.
• .output FILENAME: If we need to send output to the desired filename at that time we use the above command.
• .output stdout: By using this command we can send output to the screen.
• .print STRING…..: If we need to print the literal string at that time we can use this command.
• .prompt MAIN CONTINUE: By using this command we can replace the standard prompts as per the requirement.
• .quit: It is used to exit the SQLite prompt.
• .read FILENAME: If we need to use the database at that time we can use the read filename command to execute the SQL statement.
• .schema ? TABLE?: If we need to show the create statement that we can use this command, if we specified a table then we can show matching content from the specified table by using the LIKE pattern.
• .separator STRING: By using this command we can change the separator by using different output modes and import functions.
• .show: If we need to see the current setting values at that time we can use this command to see the setting.
• .stats ON | OFF: We can turn stats on or off.
• .table? PATTERN?: If we need to list table names as per the requirement if a specific pattern matches that is LIKE. At that, we can use this command.
• .timeout MS: With the help of the above command we can open the lock table for a particular time period.
• .width NUM NUM: By using this command we can see the width of the column. If column width is 0 then it selects automatically.
• .timer ON | OFF: By using this command we turn the CPU timer on or off.

Conclusion

We hope from this article you have understood SQLite commands. From this article, we learned different commands with their uses in SQLite commands. We also see how we can handle the SQLite. From this article, we learned how and when we use SQLite commands.

Recommended Articles

This is a guide to SQLite Commands. Here we also discuss the definition and different useful commands of SQLite along with an explanation. You may also have a look at the following articles to learn more –

1. PostgreSQL vs SQLite
2. React Native SQLite
3. Python SQLite
4. Python SQLite Create Database