Introduction to the use of SQLite database management related commands _mssql

1. Create a database

Start the command line and open the CLP for the shell mode by entering the following command:

Sqlite3 test.db

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

2. Create data tables

Sqlite> CREATE TABLE member (ID integer primary key, name text, age integer,addr text);

　　Note: The ID is the primary key and the column defaults to an automatically growing property.

3. Inserting data

Sqlite> INSERT into member values (0, ' WWL ', 21, ' Shanghai ');//id=0 column must not exist or there will be an error

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 (. Headers and. Mode) before the SELECT statement 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 into SQL format. With no parameters,. Dump exports the entire database as a database definition language (DDL) and Database Manipulation language (DML) command, which is appropriate for recreating database objects and the data in them. If a parameter is supplied, the shell parses the argument as a table name or view, exporting any table or view that matches the given parameter, and those mismatches are ignored.

By default. The output of the dump command is directed to the screen. such as:. Dump

If you want to redirect output to a file, use the. dump[filename command, which redirects all output to the specified file. To restore the output to the screen, just perform the. Output stdout command is 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 want to import. 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 a comma or other delimiter-separated value (comma-swparated values,csv), use the. import[file][table] command, which resolves the specified file and attempts to insert the data into the specified table.

The. Read command is used to import files created by the. Dump command. If you use the previously exported file.sql as a backup file, you need to remove the existing database objects and then re-import them in the following ways:

Sqlite>drop table member;

Sqlite>drop view schema;

Sqlite>.read File.sql

7. Backing Up the database

There are two ways to do a backup of your database, depending on the type of backup you want. SQL dumps are the most portable backups.

The standard way to generate a dump is to use the Clp.dump command: sqlite3 test.db. Dump >test.sql

In the shell, you can redirect the output to an external file, execute a command, and revert to the screen output, such as:

Sqlite>.output File.sql

Sqlite>.dump

Sqlite>.output stdout

Sqlite>.exit

Similarly, it is easy to implement database import for SQL dumps as input streams for CLP:

Sqlite3 test.db <test.sql

Backing up binary database knowledge is a little more work than copying files. You need to clean up the database before backing up, freeing up space for some deleted objects that are no longer in use. The database file becomes smaller, so the binary copy is smaller:

Sqlite3 Test.db Vacuum

CP test.db Test. Backup

8. Other Orders

Sqlite>select Last_insert_rowid (); Get the last inserted automatic growth measure

Sqlite>.tabes//Return all tables and views

Sqlite>.indices member//view index of a table

Sqlite>.schema member//Gets a definition (DDL) statement for a table or view, and returns the definition statement for all database objects (Table,view,index,triger) If no table name is supplied