Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to analyze the use of commands related to SQLite database management. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

1. Create a database

Start the command line and open CLP in Shell mode by entering the following command:

Sqlite3 test.db

Although we provided the database name, if the database does not exist, SQLite does not actually create the database, and SQLite does not create the database until something is created inside the database.

two。 Create a datasheet

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 attribute of automatic growth by default.

3. Insert data

Sqlite > insert into Member values; / / column of id=0 must not exist, otherwise an error will occur.

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 in front of 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

Use the .dump command to export database objects to SQL format. With no parameters, .dump exports the entire database as database definition language (DDL) and database manipulation language (DML) commands, which are suitable for recreating database objects and the data in them. If parameters are provided, Shell parses the parameters as table names or views, and exports any tables or views that match the given parameters, and those that do not match will be ignored.

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

If you want to redirect output to a file, use the .dump [filename] command, which redirects all output to the specified file. To revert to the output of the screen, simply execute the .output stdout command and OK.

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 you are importing. If the file consists of SQL statements, you can use the .read command to import the commands contained in the file. If the file contains values (comma-swparated values,CSV) separated by commas or other delimiters, use the .import download address [table] command, which parses the specified file and attempts 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 previously as a backup file, you need to remove the existing database objects and then re-import them using the following methods:

Sqlite > drop table Member

Sqlite > drop view schema

Sqlite > .read file.sql

7. Backup database

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

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

In Shell, you can redirect output to an external file, execute commands, and restore to screen output, such as:

Sqlite > .output file.sql

Sqlite > .dump

Sqlite > .output stdout

Sqlite > .exit

Similarly, it is easy to import SQL dumps into the database as an input stream for CLP:

Sqlite3 test.db select last_insert_rowid (); / / get the last inserted auto-increment value

Sqlite > .tabes / / returns all tables and views

Sqlite > .indexes Member / / View the index of a table

Sqlite > .schema Member / / gets a DDL statement for a table or view. If no table name is provided, the definition statement for all database objects (table,view,index,triger) is returned.

About how to carry on the SQLite database management related command use analysis to share here, hope that the above content can have some help to everyone, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.