SQLite three common commands for getting Started

3. Data display related commands

3.1 Set separator:. Separator separator

Help

Separator STRING Change separator used by output mode and. Import

Example

Default separator is |

sqlite> SELECT * FROM Cars;
1|Audi|52642
2|Mercedes|57127
3|Skoda|9000
4|Volvo|29000
5|Bentley|350000
6|Citroen|21000
7|Hummer|41400
8|Volkswagen|21600

Custom Separator

sqlite> .separator -
sqlite> SELECT * FROM Cars;
1-Audi-52642
2-Mercedes-57127
3-Skoda-9000
4-Volvo-29000
5-Bentley-350000
6-Citroen-21000
7-Hummer-41400
8-Volkswagen-21600
sqlite> .separator ;
sqlite> SELECT * FROM Cars;
1;Audi;52642
2;Mercedes;57127
3;Skoda;9000
4;Volvo;29000
5;Bentley;350000
6;Citroen;21000
7;Hummer;41400
8;Volkswagen;21600

3.2 Setting display mode:. Mode mode

?

.mode MODE ?TABLE?     Set output mode where MODE is one of:
                         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

Default is List display mode

sqlite> SELECT * FROM Cars;
1|Audi|52642
2|Mercedes|57127
3|Skoda|9000
4|Volvo|29000
5|Bentley|350000
6|Citroen|21000
7|Hummer|41400
8|Volkswagen|21600

More Forth display mode

sqlite> .mode csv
sqlite> SELECT * FROM Cars;
1,Audi,52642
2,Mercedes,57127
3,Skoda,9000
4,Volvo,29000
5,Bentley,350000
6,Citroen,21000
7,Hummer,41400
8,Volkswagen,21600
sqlite> .mode tabs
sqlite> SELECT * FROM Cars;
1   Audi    52642
2   Mercedes    57127
3   Skoda   9000
4   Volvo   29000
5   Bentley 350000
6   Citroen 21000
7   Hummer  41400
8   Volkswagen  21600

3.3 Display title bar:. Headers on

 Echo on| Off Turn command echo on or off

Show title bar

sqlite> .headers on
sqlite> SELECT * FROM Cars;
Id  Name    Cost
1   Audi    52642
2   Mercedes    57127
3   Skoda   9000
4   Volvo   29000
5   Bentley 350000
6   Citroen 21000
7   Hummer  41400
8   Volkswagen  21600

Do not display the title bar

sqlite> .headers off
sqlite> SELECT * FROM Cars;
1   Audi    52642
2   Mercedes    57127
3   Skoda   9000
4   Volvo   29000
5   Bentley 350000
6   Citroen 21000
7   Hummer  41400
8   Volkswagen  21600

3.4 Set the display width of each column:. width NUM1 NUM2 ...

 width NUM1 NUM2 ... Set column widths for "column" mode

The default width does not appear to require this command

3.5 Set the null value to show what it looks like:. Nullvalue you want the null value format

By default, null values do not show anything, you can set it to the way you want it

sqlite> SELECT NULL,NULL,NULL;

sqlite> . nullvalue null
sqlite> SELECT NULL,NULL,NULL;
null    null    null

3.6 Lists the current display formatting settings:. Show

.show                  Show the current values for various settings

Example

bixiaopeng@bixiaopeng db$ sqlite3 wirelessqa.db
SQLite version 3.7.13 2012-07-17 17:46:21
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .show
     echo: off
  explain: off
  headers: off
     mode: list
nullvalue: ""
   output: stdout
separator: "|"
    stats: off
    width:

If we want to reset the display format every time we go to the command line, it's a hassle, actually. All settings listed in the show command can be saved to a. sqliterc file so that it is automatically set each time you enter the command line.

The. Sqlterc file is saved under Linux in the user's home directory and can be saved to any directory under Windows, but you need to set the environment variable so that the database engine can find it, this is not an example, interested can see help.