SQLite related commands for database management

SQLite is a lightweight database system similar to access, but smaller, faster, more capacity, and more concurrent. Why is SQLite most suitable for CMS (Content management system)? Not that other databases are bad, Oracle, MySQL, SQL Server are also very good DBS, but they have different design objectives, different characteristics, so only a more suitable application scenario, There are no absolute good or bad points.

Technical features of SQLite:

SQLite support for the SQL92 standard includes indexing, throttling, triggering, and viewing. SQLite does not support foreign key restrictions, but supports atomic, consistent, independent, and persistent (acid) transactions (later with more information about acid). This means that transactions are atomic because they are either fully executed or not executed at all. The transaction is also consistent because in an inconsistent state the database has never been retained. Transactions are still independent, so if there are two transactions that perform operations at the same time on the same database, the two transactions do not interfere with each other. And the transaction is persistent, so the database can survive crashes and outages without losing data or damage. SQLite enables independent transaction processing through exclusive and shared locking at the database level. This means that when multiple processes and threads can read data from the same database at the same time, only one can write data. An exclusive lock must be obtained before a process or thread performs a write operation on the database. After an exclusive lock is issued, other read or write operations will not occur again.

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 ');

4. 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.

5. Create views and indexes

Sqlite> CREATE VIEW schema as select * from member;

Sqlite> CREATE index MEMBER_IDX on member (ID)

6. 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.

7. 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

8. 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

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

9. Other Orders

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

sqlite>.tabes//returns 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