Easy Tutorial
Sqlite Alias Sqlite Like Clause Sqlite Syntax Sqlite Php Sqlite Distinct Keyword Sqlite Functions Sqlite Autoincrement Sqlite Detach Database Sqlite Unions Clause Sqlite Attach Database Sqlite Joins Sqlite Pragma Sqlite Group By Sqlite Where Clause Sqlite Injection Sqlite And Or Clauses Sqlite Operators Sqlite Constraints Sqlite Drop Table Sqlite C Cpp Sqlite Order By Sqlite Subquery Sqlite Expressions Sqlite View Sqlite Indexed By Sqlite Alter Command Sqlite Insert Sqlite Select Sqlite Explain Sqlite Python Sqlite Installation Sqlite Transaction Sqlite Update Sqlite Delete Sqlite Commands Sqlite Date Time Sqlite Null Values Sqlite Create Table Sqlite Java Sqlite Data Types Sqlite Trigger Sqlite Create Database Sqlite Limit Clause Sqlite Intro Sqlite Truncate Table Sqlite Vacuum Sqlite Perl Sqlite Glob Clause Sqlite Index Sqlite Tutorial Sqlite Having Clause
❮ Sqlite Delete Sqlite Date Time ❯

SQLite Commands

This chapter will explain the simple yet useful commands used by SQLite programmers. These commands are known as SQLite dot commands, and the distinguishing feature of these commands is that they do not end with a semicolon ;.

Let's type a simple sqlite3 command at the command prompt. Within the SQLite command prompt, you can use various SQLite commands.

$ sqlite3
SQLite version 3.3.6
Enter ".help" for instructions
sqlite>

To get a list of available dot commands, you can type ".help" at any time. For example:

sqlite>.help

The above command will display a list of various important SQLite dot commands, as shown below:

Command Description
.backup ?DB? FILE Backup DB database (default "main") to FILE file.
.bail ON OFF Stop after hitting an error. Default OFF.
.databases List the names and files of attached databases.
.dump ?TABLE? Dump the database in an SQL text format. If TABLE is specified, only dump tables matching LIKE pattern.
.echo ON OFF Turn echo on or off.
.exit Exit the SQLite prompt.
.explain ON OFF Turn output mode suitable for EXPLAIN on or off. Without parameters, it is EXPLAIN on.
.header(s) ON OFF Turn headers on or off.
.help Display this message.
.import FILE TABLE Import data from FILE into TABLE.
.indices ?TABLE? Show the names of all indices. If TABLE is specified, only show indices for tables matching LIKE pattern.
.load FILE ?ENTRY? Load an extension library.
.log FILE off Turn logging on or off. FILE can be stderr/stdout.
.mode MODE Set output mode where MODE is one of: csv Comma-separated values<br>column Left-aligned columns<br>html HTML <table> code<br>insert SQL insert statements for TABLE<br>line One value per line<br>list Values delimited by .separator string<br>tabs Tab-separated values<br>tcl TCL list elements
.nullvalue STRING Print STRING in place of NULL values.
.output FILENAME Send output to FILENAME.
.output stdout Send output to the screen.
.print STRING... Print literal STRING.
.prompt MAIN CONTINUE Replace the standard prompts.
.quit Exit the SQLite prompt.
.read FILENAME Execute SQL in FILENAME.
.schema ?TABLE? Show the CREATE statements. If TABLE is specified, only show tables matching LIKE pattern.
.separator STRING Change separator used by output mode and .import.
.show Show the current values for various settings.
.stats ON OFF Turn stats on or off.
.tables ?PATTERN? List table names matching LIKE pattern.
.timeout MS Try to open locked tables for MS milliseconds.
.width NUM NUM Set column widths for "column" mode.
.timer ON OFF Turn the CPU timer measurement on or off.

Let's try using the .show command to view the default settings for the SQLite prompt.

sqlite>.show
     echo: off
  explain: off
  headers: off
     mode: column
nullvalue: ""
   output: stdout
separator: "|"
    width:
sqlite>

Ensure there is no space between the sqlite> prompt and the dot command, otherwise it will not work correctly.

Formatting Output

You can use the following dot commands to format the output as listed in this tutorial:

sqlite>.header on sqlite>.mode column sqlite>.timer on sqlite>


The above settings will produce output in the following format:

ID NAME AGE ADDRESS SALARY

1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 CPU Time: user 0.000000 sys 0.000000


## sqlite_master Table

The main table stores key information about database tables and is named **sqlite_master**. To view the table summary, you can perform the following operation:

sqlite>.schema sqlite_master


This will produce the following result:

CREATE TABLE sqlite_master ( type text, name text, tbl_name text, rootpage integer, sql text ); ```

❮ Sqlite Delete Sqlite Date Time ❯