SQLite entry 3 Common commands

3. Data Display commands

3.1 set the separator:. separator

Help:

?

1	. Separator STRING Change separator used by output mode and. import

 

Example:

The default Delimiter is |?

1 2 3 4 5 6 7 8 9

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 19 20

Sqlite>. separator-sqlite> SELECT * FROM Cars; 1-Audi-52642 2-Mercedes-57127 3-sko00009000 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 set the display mode:. mode

?

1 2 3 4 5 6 7 8 9

. 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. separator string tabs Tab-separated values tcl list elements

 

The default display mode is list.

?

1 2 3 4 5 6 7 8 9

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

 

Display Mode

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 19 20

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

?

1	. Echo ON | OFF Turn command echo on or off

 

Display title bar

?

1 2 3 4 5 6 7 8 9 10 11

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 title bar

?

1 2 3 4 5 6 7 8 9 10

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...

?

1	. Width NUM1 NUM2... Set column widths for "column" mode

This command is not required for displaying the default width.

3.5 set the NULL value to what it looks like:. nullvalue the format of the NULL value you want

By default, the NULL value is not displayed. You can set it to what you want.

?

1 2 3 4 5

Sqlite> select null, NULL, NULL; sqlite>. nullvalue null sqlite> select null, NULL, NULL; null

 

3.6 list the current display format settings:. show

Help :?

1	. Show Show show the current values for various settings

 

Example:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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:

3.7 configuration file. sqliterc

If we need to reset the display format every time we enter the command line, it is very troublesome. all settings listed by the show command can be saved to one. in the sqliterc file, it is automatically set every time you enter the command line.

. The sqlterc file is stored in the Home directory of the user in Linux and can be saved to any directory in Windows. However, you need to set the environment variable so that the database engine can find it. This is not an example, for more information, see help.