SQLite3 Shell Commands slides presentation

Last update on August 19 2022 21:50:30 (UTC/GMT +8 hours)

This slide presentation describes SQLite3 Shell Commands with syntax and examples. Covering all the dot(.) commands in this slides presentation.

Transcript

.backup

This command is used to backup database (default "main") to a file.

Syntax: .backup ?DB? FILE

.backup - Example

D:\sqlite>sqlite3 medical.db
SQLite version 3.8.5 2014-06-04 14:06:34
Enter ".help" for usage hints.
sqlite> .databases
seq name             file
--- --------------   ---------------------------------------------------
0 main D:\sqlite\medical.db
sqlite> .tables
doctornew doctors speciality table_a table_b visits
sqlite> SELECT * FROM doctornew;
210|Dr. John Linga|MD
211|Dr. Peter Hall|MBBS
212|Dr. Ke Gee|MD
213|Dr. Pat Fay|MD
sqlite> .backup D:/sqlite/backup/medical_bkup

.restore

This command is used to restore content of database (default "main") from the backup file.

Syntax : - .restore ?DB? FILE

.restore - Example

D:\sqlite>sqlite3 medical.db
SQLite version 3.8.5 2014-06-04 14:06:34
Enter ".help" for usage hints.
sqlite> .databases
seq name            file
--- ---------       ----------------------------------------------------
0 main D:\sqlite\medical.db
sqlite> .tables
doctornew doctors speciality table_a table_b visits
sqlite> SELECT * FROM doctornew;
210|Dr. John Linga|MD
211|Dr. Peter Hall|MBBS
212|Dr. Ke Gee|MD
213|Dr. Pat Fay|MD
sqlite> .backup D:/sqlite/backup/medical_bkup

.bail

Stop after hitting an error. Default OFF

Syntax : - .bail on|off

.clone

This command is used to clone data into another database from the existing database.

Syntax : - .clone NEWDB

.clone - Example

D:\sqlite>sqlite3 medical1.db
SQLite version 3.8.5 2014-06-04 14:06:34
Enter ".help" for usage hints.
sqlite> .open d:/sqlite/backup/medical1.db
sqlite> .tables
doctors speciality table_a table_b visits
sqlite> .clone d:/sqlite/backup/medical2.db
doctors... done
speciality... done
visits... done
table_a... done
table_b... done
sqlite_autoindex_doctors_1... done

.databases

This command lists names and files of attached databases.

Syntax : - .databases

.databases - Example

sqlite> .databases
seq name            file
--- --------------- ------------------------
0 main D:\sqlite\medical.db
2 hrdb D:\sqlite\hr

.dump

Dump the database in an SQL text format If TABLE specified, only dump tables matching LIKE pattern TABLE.

Syntax : - .dump ?TABLE?

.dump - Example

sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE table1(
id integer,
descrip char(10));
INSERT INTO "table1" VALUES(1,'descrip1');
INSERT INTO "table1" VALUES(2,'descrip2');
CREATE TABLE table2(
id integer,
descrip2 char(20));
INSERT INTO "table2" VALUES(1,'descrip3');
INSERT INTO "table2" VALUES(2,'descrip4');
COMMIT;

.echo

Turn command echo on or off.

Syntax : - .echo on|off

.echo - Example

sqlite> .echo on
sqlite> .tables
.tables
doctors speciality table_a table_b visits
sqlite> .echo off
sqlite> .tables
doctors speciality table_a table_b visits

.eqp

This command is used to Enable or disable automatic explain of query plan.

Syntax: - .eqp on|off

.eqp - Example

sqlite> .eqp on
sqlite> select * from doctors;
--EQP-- 0,0,0,SCAN TABLE doctors
210|Dr. John Linga|MD
211|Dr. Peter Hall|MBBS
212|Dr. Ke Gee|MD
213|Dr. Pat Fay|MD
sqlite> .eqp off
sqlite> select * from doctors;
210|Dr. John Linga|MD
211|Dr. Peter Hall|MBBS
212|Dr. Ke Gee|MD
213|Dr. Pat Fay|MD

.exit

Exit this program.

Syntax: - .exit

.explain

The ".explain" dot command can be used to turn output mode suitable for EXPLAIN on or off. With no args, it turns EXPLAIN on.

Syntax: - .explain ?on|off?

.explain - Example

sqlite> .explain
.explain
sqlite> explain select * from table1;
explain select * from table1;
addr opcode        p1   p2   p3   p4           p5 comment
---- ------------- ---- ---- ---- ------------ -- -------
0    Init          0    9    0                 00
1    OpenRead      0    2    0    2            00
2    Rewind        0    7    0                 00
3    Column        0    0    1                 00
4    Column        0    1    2                 00
5    ResultRow     1    2    0                 00
6    Next          0    3    0                 01
7    Close         0    0    0                 00
8    Halt          0    0    0                 00
9    Transaction   0    0    3    0            01
10   TableLock     0    2    0    table1       00
11   Goto          0    1    0                 00

.headers

Turn display of headers on or off

The column labels that appear on the first two lines of output can be turned on and off using the ".header" dot command. In the following example, the column labels are off.

Syntax: - .headers on|off

.headers - Example

sqlite> .headers off
sqlite> SELECT * FROM doctors;
210 Dr. John Linga MD
211 Dr. Peter Hall MBBS
212 Dr. Ke Gee MD
213 Dr. Pat Fay MD

.help

Show this message.

Syntax: - .help

.import

This command is used to import data from a file into table.

The ".import" command is used to import CSV data into an SQLite table. The ".import" command takes two arguments which are the name of the disk file from which CSV data is to be read and the name of the SQLite table into which the CSV data is to be inserted.

Note that it is important to set the "mode" to "csv" before running the ".import" command.

Syntax: - .import FILE TABLE

.import - Example

sqlite> .tables
doctors speciality table_a table_b visits
sqlite> .mode csv
sqlite> .import D:/sqlite/backup/tempdoctor.csv doctornew
sqlite> .tables
doctornew doctors speciality table_a table_b visits
sqlite> .mode column
sqlite> SELECT * FROM doctornew;
doctor_id  doctor_name     degree
---------- -------------- ----------
210        Dr. John Linga  MD
211        Dr. Peter Hall  MBBS
212        Dr. Ke Gee      MD
213        Dr. Pat Fay     MD

.indices

Show names of all indices If TABLE specified, only show indices for tables matching LIKE pattern TABLE.

Syntax: - .indices ?TABLE?

.indices - Example

sqlite> .indices doctors
sqlite_autoindex_doctors_1

.load

Load an extension library

Syntax: - .load FILE ?ENTRY?

.log

Turn logging on or off. FILE can be stderr/stdout

Syntax: - .log FILE|off

.mode

The sqlite3 program is able to show the results of a query in eight different formats: "csv", "column", "html", "insert", "line", "list", "tabs", and "tcl". You can use the ".mode" dot command to switch between these output formats.

The default output mode is "list". In list mode, each record of a query result is written on one line of output and each column within that record is separated by a specific separator string. The default separator is a pipe symbol ("|").

Syntax: - .mode MODE ?TABLE?

.mode - Example

csv Comma-separated values
column Left-aligned columns. (See .width)
html HTML <table> code
insert SQL insert statements for TABLE
line One value per line
list Values delimited by .separator string
tabs Tab-separated values
tcl TCL list elements

.nullvalue

This command is used to replace the NULL values by a specific string.

Syntax: - .nullvalue STRING

.nullvalue - Example

sqlite> SELECT * FROM visits;
doctor_id  patient_name vdate
---------- ------------ ----------
210        Julia Nayer  2013-10-15
214        TJ Olson     2013-10-14
215        John Seo     2013-10-15
212        James Marlow 2013-10-16
212        Jason Mallin 2013-10-12
216                     2013-10-27

.once

This command is used to send the output for the next SQL command only to named file instead of being printed on the console.

Syntax: - .once FILENAME

.once - Example

sqlite> .header on
sqlite> .mode csv
sqlite> .once d:/sqlite/backup/tempdoctor.csv
sqlite> SELECT * FROM doctors;

.open

Close existing database and reopen FILENAME

Syntax: - .open ?FILENAME?

.open - Example

sqlite> .open d:/sqlite/backup/medical1.db
sqlite> .databases
seq name            file
--- --------------- ------------------------------------
0    main           d:\sqlite\backup\medical1.db
sqlite> .tables
doctors   speciality   table_a   table_b   visits

.output

This command send output to a file or stdout.

Syntax: - .output ?FILENAME?

.output - Example

sqlite> .output d:/sqlite/backup/test1.txt
sqlite> SELECT * FROM doctors;

.print

This command is used to Print literal STRING.

Syntax: - .print STRING...

.print - Example

sqlite> .print "This command is used to print the text."
This command is used to print the text.

.prompt

This command replace the standard prompts.

Syntax: - .prompt MAIN CONTINUE

.prompt - Example

sqlite> .prompt sqlite#
sqlite#

.quit

Exit this program.

Syntax: - .quit

.read

Execute SQL in FILENAME.

Syntax: - .read FILENAME

.save

Write in-memory database into FILE

After creating a new database and tables under this database using the default in-memory storage, the database can be save into a disk file using the ".save" command:

Syntax: - .save FILE

.save - Example

D:\sqlite>sqlite3
SQLite version 3.8.5 2014-06-04 14:06:34
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open medical.db
sqlite> .tables
doctors speciality table_a table_b visits
sqlite> .save D:/sqlite/backup/medical1.db

.schema

Show the CREATE statements. If TABLE specified, only show tables matching LIKE pattern TABLE.

The .schema command is used to see database schema information. This gives return the create command that was used to create the tables. If you created an index on any columns then this will also show up.

Syntax: - .schema ?TABLE?

.schema - Example

sqlite> .schema
CREATE TABLE table1(
id integer,
descrip char(10));
CREATE TABLE table2(
id integer,
descrip2 char(20));
CREATE INDEX id_index ON table1(id);
Here in the following example the table name have been specified.
sqlite> .schema table1
CREATE TABLE table1(
id integer,
descrip char(10));

.separator

Change separator used by output mode and .import

You can use the ".separator" dot command to change the separator for list mode. For example, to change the separator to a comma and a space, you could do this.

Syntax: - .separator STRING

.separator - Example

sqlite> .mode list
sqlite> .header on
sqlite> .separator ','
sqlite> SELECT * FROM doctors;
doctor_id,doctor_name,degree
210,Dr. John Linga,MD
211,Dr. Peter Hall,MBBS
212,Dr. Ke Gee,MD
213,Dr. Pat Fay,MD

.shell

Run CMD ARGS... in a system shell .

Syntax: - .shell CMD ARGS...

.show

Show the current values for various settings.

Syntax: - .show

.show - Example

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

.stats

Turn stats on or off.

Syntax: - .stats on|off

.stats - Example

sqlite> .stats on
sqlite> SELECT * FROM doctors;
doctor_id  doctor_name    degree
---------- -------------- ----------
210        Dr. John Linga  MD
211        Dr. Peter Hall  MBBS
212        Dr. Ke Gee      MD
213        Dr. Pat Fay     MD
Memory Used:                       75640 (max 82424) bytes
Number of Outstanding Allocations: 133 (max 152)
Number of Pcache Overflow Bytes:   3376 (max 6600) bytes
Number of Scratch Overflow Bytes:  0 (max 0) bytes
Largest Allocation:                64000 bytes
Largest Pcache Allocation:         1176 bytes
Largest Scratch Allocation:        0 bytes
Lookaside Slots Used:              8 (max 58)
Successful lookaside attempts:     247
Lookaside failures due to size:    48
Lookaside failures due to OOM:     0
Pager Heap Usage:                  3912 bytes
Page cache hits:                   2
Page cache misses:                 0
Page cache writes:                 0
Schema Heap Usage:                 2864 bytes
Statement Heap/Lookaside Usage:    2368 bytes
Fullscan Steps:                    3
Sort Operations:                   0
Autoindex Inserts:                 0
Virtual Machine Steps:             28

.system

Run CMD ARGS... in a system shell

The .system command works like .once command and in more this will typically bring up a spreadsheet program to display the CSV file.

Syntax: - .system CMD ARGS...

.system - Example

sqlite> .mode csv
sqlite> .once D:/sqlite/backup/newdoctor1.csv
sqlite> select * from doctors;
sqlite> .system D:/sqlite/backup/newdoctor1.csv
sqlite>

.tables

List names of tables If TABLE specified, only list tables matching LIKE pattern TABLE.

Syntax: - .tables ?TABLE?

.tables - Example

sqlite> .tables
doctors speciality table_a table_b visits

.timeout

Try opening locked tables for MS milliseconds.

Syntax: - .timeout MS

.timer

This command turn SQL timer on or off. By default the timer is off.

Syntax: - .timer on|off

.timer - Example

sqlite> .timer on
sqlite> SELECT * FROM doctors;
doctor_id doctor_name degree
---------- -------------- ----------
210 Dr. John Linga MD
211 Dr. Peter Hall MBBS
212 Dr. Ke Gee MD
213 Dr. Pat Fay MD
Run Time: real 0.003 user 0.000000 sys 0.000000
sqlite> .timer off
sqlite> SELECT * FROM doctors;
doctor_id  doctor_name    degree
---------- -------------- ----------
210        Dr. John Linga MD
211        Dr. Peter Hall MBBS
212        Dr. Ke Gee     MD
213        Dr. Pat Fay    MD

.trace

Output each SQL statement as it is run.

Syntax: - .trace FILE|off

.vfsname

Print the name of the VFS stack .

Syntax: - .vfsname ?AUX?

.width

By default, each column is between 1 and 10 characters wide, depending on the column header name and the width of the first column of data. Data that is too wide to fit in a column is truncated. You can adjust the column widths using the ".width" command.

Syntax: - .width NUM1 NUM2 ...

.width - Example

sqlite> .width 15,20,10
sqlite> select * from doctors;
doctor_id       doctor_name    degree
--------------- -------------- --------
210             Dr. John Linga MD
211             Dr. Peter Hall MBBS
212             Dr. Ke Gee     MD
213             Dr. Pat Fay    MD