SQLite command
Point command
Use. help to get a list of point commands
| command | option | describe |
|---|---|---|
| .backup | ?DB? FILE | Back up the DB database (the default is "main") to the FILE file. |
| .bail | ON|OFF | Stop when an error occurs. The default is OFF. |
| .databases | Lists the name of the database and the files to which it is attached. | |
| .dump | ?TABLE? | Dump the database in SQL text format. If a TABLE table is specified, only TABLE tables that match the LIKE pattern are dumped. |
| .echo | ON|OFF | Turns the echo command on or off. |
| .exit | Exit the SQLite prompt. | |
| .explain | ON|OFF | Turn on or off the output mode suitable for EXPLAIN. If there is no parameter, it is EXPLAIN on, that is, EXPLAIN is enabled. |
| .header(s) | ON|OFF | Turns the head display on or off. |
| .help | Displays a message. | |
| .import | FILE TABLE | Import data from the FILE file into the TABLE table. |
| .indices | ?TABLE? | Displays the names of all indexes. If a TABLE table is specified, only the indexes of TABLE tables that match the LIKE pattern are displayed. |
| .load | FILE ?ENTRY? | Load an extension library. |
| .log | FILE|off | Turn logs on or off. The FILE file can be stderr (standard error) / stdout (standard output). |
| .mode | MODE | Set the output MODE, which can be one of the following:
|
| .nullvalue | STRING | The STRING string is output where the NULL value is. |
| .output | FILENAME | Send output to FILENAME file. |
| .output | stdout | Send output to screen. |
| STRING... | Output STRING strings verbatim. | |
| .prompt | MAIN CONTINUE | Replace the standard prompt. |
| .quit | Exit the SQLite prompt. | |
| .read | FILENAME | Execute SQL in FILENAME file. |
| .schema | ?TABLE? | Displays the CREATE statement. If a TABLE table is specified, only TABLE tables that match the LIKE pattern are displayed. |
| .separator | STRING | Change the separator used by the output mode and. import. |
| .show | Displays the current values of various settings. | |
| .stats | ON|OFF | Turn statistics on or off. |
| .tables | ?PATTERN? | Lists the names of the tables that match the LIKE pattern. |
| .timeout | MS | An attempt was made to open a locked table. |
| .width | NUM NUM | Set the column width for column mode. |
| .timer | ON|OFF | Turns the CPU timer on or off. |
sqlite> .databases
main: E:\myfile\python\pycharm\test\sqlite3.db
sqlite>
sqlite> .dump book
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE book(
id int primary key,
sort int,
name text,
price real,
category int,
foreign key (category) references category(id) on update cascade on delete cascade
);
INSERT INTO book VALUES(2,3,'Python Intro',17.5,2);
COMMIT;
sqlite>
Format output
sqlite>.header on sqlite>.mode column sqlite>.timer on sqlite>
SQLite syntax
| grammar | explain |
|---|---|
| Case | SQLite is not case sensitive, but individual commands should be distinguished. For example, glob and glob are different. |
| notes |
|
| sentence | Start with keyword and end with semicolon. Common keywords
|
data type
Common types
In SQLite, every column, every variable and every expression has related data types.
You can use these data types when creating tables.
| Storage class | describe |
|---|---|
| NULL | Value is a NULL value. |
| INTEGER | A value is a signed integer stored in 1, 2, 3, 4, 6, or 8 bytes depending on the size of the value. |
| REAL | Value is a floating-point value stored as an 8-byte IEEE floating-point number. |
| TEXT | The value is a text string stored using database encoding (UTF-8, UTF-16BE, or UTF-16LE). |
| BLOB | Value is a blob data, stored entirely according to its input. |
Affinity type
| Affinity type | describe |
|---|---|
| TEXT | |
| NUMERIC | |
| INTEGER | |
| REAL | |
| NONE |
Affinity type and type name
boolean data type
SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).
Date and Time data types
SQLite does not have a separate storage class for storing date and / or time, but SQLite can store date and time as TEXT, REAL or INTEGER values.
Create database
establish
PS E:\myfile\python\pycharm\test> sqlite3.exe test.db SQLite version 3.27.2 2019-02-25 16:06:06 Enter ".help" for usage hints. sqlite> sqlite> .databases main: E:\myfile\python\pycharm\test\test.db
export
PS E:\myfile\python\pycharm\test> sqlite3 test.db .dump > test.db.sql PS E:\myfile\python\pycharm\test> cat test.db.sql PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; COMMIT; PS E:\myfile\python\pycharm\test>
You can also recover the database from the. sql file
$SQLite3 test.db < test.db.sql # note: this command is not successful in window for unknown reasons.
Additional database
PS E:\myfile\python\pycharm\test> sqlite3 sqlite3.db SQLite version 3.27.2 2019-02-25 16:06:06 Enter ".help" for usage hints. sqlite> sqlite> .database main: E:\myfile\python\pycharm\test\sqlite3.db sqlite> sqlite> attach database 'test.db' as 'test'; sqlite> sqlite> .database main: E:\myfile\python\pycharm\test\sqlite3.db test: E:\myfile\python\pycharm\test\test.db sqlite>
Note that the names main and temp are reserved, so they cannot be attach ed
sqlite> attach database 'test.db' as 'temp'; Error: database temp is already in use sqlite> sqlite> attach database 'test.db' as 'main'; Error: database main is already in use sqlite>
Detach database
Cannot separate main and temp
If it is an in memory database or temporary database, the corresponding database will be destroyed and the content will be lost after detach.
sqlite>.databases seq name file --- --------------- ---------------------- 0 main /home/sqlite/testDB.db 2 test /home/sqlite/testDB.db 3 currentDB /home/sqlite/testDB.db sqlite> sqlite> DETACH DATABASE 'currentDB'; sqlite> .databases seq name file --- --------------- ---------------------- 0 main /home/sqlite/testDB.db 2 test /home/sqlite/testDB.db