How to use commands related to SQLite Database Management

1. Create a database

Start the command line and run the following command to open the Shell mode CLP:

Sqlite3 test. db

Although we provide the database name, if the database does not exist, SQLite does not actually create the database until it creates some content in the database.

2. Create a data table

Sqlite> create table Member (id integer primary key, name text, age integer, addr text );

　　Note: id is the primary key, and This column has the property of automatic growth by default.

3. insert data

Sqlite> insert into Member values (0, 'wwl', 21, 'shanghai'); // The column id = 0 must not exist; otherwise, an error occurs.

Or sqlite> insert into Member (name, age, addr) values ('wwl', 21, 'shanghai ');

3. query data

Sqlite>. mode column

Sqlite>. headers on

Sqlite> select * from Member;

　　Note: The two commands before the select statement (. headers and. mode) are used to improve the display format.

4. Create views and Indexes

Sqlite> create view schema as select * from Member;

Sqlite> create index Member_Idx on Member (id)

5. Export data

You can use the. dump command to export database objects to the SQL format. Without any parameters,. dump exports the entire database as a database Definition Language (DDL) or database operation language (DML) command. It is suitable for recreating database objects and data in the database. If a parameter is provided, Shell parses the parameter as the table name or view and exports any tables or views that match the given parameter. Otherwise, those that do not match will be ignored.

By default, the output of the. dump command is directed to the screen. For example,. dump

To redirect the output to a file, run the. dump [filename] command to redirect all the output to the specified file. To restore the output to the screen, run the. output stdout command.

Sqlite>. output file. SQL

Sqlite>. dump

Sqlite>. output stdout

NOTE: If file. SQL does not exist, the file will be created in the current working directory. If the file exists, it will be overwritten.

6. Import Data

There are two ways to import data, depending on the file format to be imported. If the file is composed of SQL statements, you can use the. read command to import the commands contained in the file. If the file contains values separated by commas (,) or other delimiters (comma-swparated values, CSV), you can use. import [table] command, which parses the specified file and tries to insert data into the specified table.

The. read command is used to import files created by the. dump command. If you use the file. SQL exported as a backup file, you must first remove the existing database object and then re-import it using the following method:

Sqlite> drop table Member;

Sqlite> drop view schema;

Sqlite>. read file. SQL

7. Back up the database

There are two ways to back up the database, depending on the type of backup you want. SQL dumping is the best portable backup.

The standard way to generate a dump is to use the CLP. dump command: sqlite3 test. db. dump> test. SQL

In Shell, you can redirect the output to an external file and execute the command to restore it to the screen output, for example:

Sqlite>. output file. SQL

Sqlite>. dump

Sqlite>. output stdout

Sqlite>. exit

Similarly, it is easy to use SQL dump as the CLP input stream to import databases:

Sqlite3 test. db <test. SQL

The knowledge of backing up a binary database is a little bit more work than copying files. You need to clear the database before the backup, so that you can release some deleted objects that are no longer in use. This database file will become smaller, so the binary copy will be smaller:

Sqlite3 test. db vacuum

Cp test. db test. Backup

8. Other commands

Sqlite> select last_insert_rowid (); // obtain the auto-increment value of the last insert.

Sqlite>. tabes // return all tables and views

Sqlite>. indices Member // view the index of a table

Sqlite>. schema Member // get a table or view definition (DDL) statement. If the table name is not provided, return the Definition Statement of All database objects (table, view, index, triger ).