SQLite database management commands

SQLite is a lightweight database system similar to Access,

SQLite is a lightweight database system similar to Access,

SQLite is a lightweight database system similar to Access, but it is smaller, faster, with larger capacity and higher concurrency. Why is SQLite most suitable for CMS? It doesn't mean that other databases are not good. Oracle, MySQL, and SQLServer are also excellent DBS, but they have different design objectives and features. Therefore, they are only applicable to a specific application scenario, there is no absolute difference between good and bad.

Technical Features of SQLite:

SQLite's support for the SQL92 standard includes indexing, restrictions, triggering, and viewing. SQLite does not support foreign key restrictions, but supports atomic, consistent, independent, and persistent (ACID) Transactions (more information about ACID will be provided later ). This means that transactions are atomic because they are either completely executed or not executed at all. Transactions are consistent because the database is never retained in an inconsistent state. The transaction is still independent. Therefore, if there are two transactions that execute operations on the same database at the same time, the two transactions do not interfere with each other. Transactions are persistent. Therefore, the database can survive the crash and power failure without data loss or damage. SQLite achieves independent transaction processing through database-level isolation and shared locking. This means that when multiple processes and threads can read data from the same database at the same time, but only one can write data. An exclusive lock must be obtained before a certain process or thread writes data to the database. After an exclusive lock is triggered, other read or write operations will not occur again.

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

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

5. Create views and Indexes

Sqlite> create view schema as select * from Member;

Sqlite> create index Member_Idx on Member (id)

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

7. 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 [file] [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

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

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

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