SQLite database SQLite3 command

The SQLite library contains a command line named sqlite3, which allows you to manually enter and execute SQL commands for the SQLite database. This document provides an example of using sqlite3

The SQLite library contains a command line named sqlite3, which allows you to manually enter and execute SQL commands for the SQLite database. This document provides an example of using sqlite3

The SQLite library contains a command line named sqlite3, which allows you to manually enter and execute SQL commands for the SQLite database. This document provides a brief description of sqlite3.
Start

To start the sqlite3 program, you only need to input the "sqlite3" command with the SQLite database name. If the file does not exist, a new (database) file is created. Then, the sqlite3 program prompts you to enter SQL. Enter the SQL statement (ended with a semicolon (;). After you press the Enter key, the SQL statement is executed.

For example, to create an SQLite database that contains a table "tb11" named "ex1", you can do this:

$ Sqlite3 ex1

SQLite version 3.3.17

Enter ". help" for instructions

Sqlite> create table tbl1 (one varchar (10), two smallint );

Sqlite> insert into tbl1 values ('Hello! ', 10 );

Sqlite> insert into tbl1 values ('Goodbye ', 20 );

Sqlite> select * from tbl1;

Hello! | 10

Goodbye | 20

Sqlite>

You can knock on the file terminator (usually Ctrl + D) or interrupt character (usually Ctrl + C) of your system ). To terminate the sqlite3 program. Are you sure you want to input a semicolon at the end of each SQL statement! The sqlite3 program queries a semicolon to determine the end of an SQL statement. If you omit the score, sqlite3 will give you a continuous command prompt and wait for you to add more text to the current SQL command. This feature allows you to enter multiple SQL statements with multiple rows, for example:

Sqlite> create table tbl2 (

...> F1 varchar (30) primary key,

...> F2 text,

...> F3 real

...> );

Sqlite>

Question: query the SQLITE_MASTER table

The SQLite database framework is stored in a special table named "sqlite_master. You can query this special table by executing "SELECT" like querying other tables. For example:

$ Sqlite3 ex1

SQlite vresion 3.3.10

Enter ". help" for instructions

Sqlite> select * from sqlite_master;

Type = table

Name = tbl1

Tbl_name = tbl1

Rootpage = 3

SQL = create table tbl1 (one varchar (10), two smallint)

Sqlite>

However, you cannot execute commands such as drop table, UPDATE, INSERT, or DELETE in the sqlite_master TABLE. The sqlite_master table is automatically updated when you create, delete, and index a database. You cannot manually change the sqlite_master table.

The structure of the TEMPORARY table is not stored in the "sqlite_master" table, because the TEMPORARY table is invisible to the application, rather than the table created by the application. The TEMPORARY table structure is stored in another table named "sqlite_temp_master. "Sqlite_temp_master" is the current table itself.

Special sqlite3 commands

In most cases, sqlite3 reads the input lines and passes them to the SQLite library for running. However, if the input line starts with a vertex ("."), the row is intercepted and interpreted by the sqlite3 program. These "Point commands" are usually used to change the query output format, or to execute a query statement for pre-packaging (prepackaged.

You can enter ". help" at any time to list available point commands. For example

Sqlite>. help

. Bail ON | OFF Stop after hitting an error. Default OFF

. Databases List names and files of attached databases

. Dump? TABLE? ... Dump the database in an SQL text format

. Echo ON | OFF Turn command echo on or off

. Exit Exit this program

. Explain ON | OFF Turn output mode suitable for EXPLAIN on or off.

. Header (s) ON | OFF Turn display of headers on or off

. Help Show this message

. Import file table Import data from FILE into TABLE

. Indices TABLE Show names of all indices on TABLE

. Load FILE? ENTRY? Load an extension library

. Mode MODE? TABLE? Set output mode where MODE is one:

Csv Comma-separated values

Column Left-aligned columns. (See. width)

Html HTML

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 STRING Print STRING in place of NULL values

. Output FILENAME Send output to FILENAME

. Output stdout Send output to the screen

. Prompt main continue Replace the standard prompts

. Quit Exit this program

. Read FILENAME Execute SQL in FILENAME

. Schema? TABLE? Show the CREATE statements

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

. Show Show show the current values for various settings

. Tables? PATTERN? List names of tables matching a LIKE pattern

. Timeout MS Try opening locked tables for MS milliseconds

. Width NUM... Set column widths for "column" mode

Sqlite>

Code,