SQLite is an open-source, embedded relational database that implements a self-contained, 0 configuration, transactional SQL database engine. It is characterized by its high portability, ease of use, compact structure, high efficiency and reliability. Unlike other database management systems, SQLite is very simple to install and run, in most cases-just make sure that sqlite binaries exist to start creating, connecting, and using the database.
1. Introduction
SQLite is an open-source, embedded relational database that implements a self-contained, 0 configuration, transactional SQL database engine. It is characterized by its high portability, ease of use, compact structure, high efficiency and reliability. Unlike other database management systems, SQLite is very simple to install and run, in most cases-just make sure that sqlite binaries exist to start creating, connecting, and using the database. If you are looking for an embedded database project or solution, SQLite is definitely worth considering.
2. Installation
The MAC system is equipped with SQLite
3. Create your first SQLite database
In the Command Line window, enter the following command to create a database named Test.db.
- Sqlite3 test.db
To create a table:
- sqlite> Create table mytable (ID integer primary key, value text);
- 2 columns were created.
The table contains a primary key field named ID and a text field named value.
Note: At a minimum, you must create a table or view for the newly created database so that you can save the database to disk or the database will not be created.
Next, write some data into the table:
- sqlite> INSERT INTO mytable (ID, value) values (1, ' Micheal ');
- sqlite> INSERT INTO mytable (ID, value) values (2, ' Jenny ');
- sqlite> INSERT INTO mytable (value) values (' Francis ');
- sqlite> INSERT INTO mytable (value) values (' Kerk ');
Query data:
- Sqlite> SELECT * from test;
- 1| Micheal
- 2| Jenny
- 3| Francis
- 4| Kerk
To set the results of a formatted query:
- sqlite>. Mode column;
- Sqlite>. Header on ;
- Sqlite> SELECT * from test;
- ID value
- ----------- -------------
- 1 micheal
- 2 Jenny
- 3 Francis
- 4 Kerk
The. Mode column is set to the column display mode, and the. Header displays the column name.
To modify the table structure, add columns:
- sqlite> alter table mytable add column Email text not null ' collate nocase;;
To create a view:
- Sqlite> Create view Nameview as select * from mytable;
To create an index:
- sqlite> Create index test_idx on mytable (value);
4. Some useful SQLite commands
Show Table structure:
- Sqlite>. schema [table]
Get all tables and views:
- SQLite >. Tables
Gets the list of indexes for the specified table:
- SQLite > Indices [table]
To export a database to a SQL file:
- SQLite >. output [filename]
- SQLite > Dump
- SQLite >. Output stdout
To import a database from a SQL file:
- SQLite >. read [filename]
Format output data to CSV format:
- SQLite;. output [Filename.csv]
- SQLite >.separator,
- SQLite > SELECT * from test;
- SQLite;. Output stdout
Import data from a CSV file into a table:
- SQLite >Create table newtable (ID integer primary key, value text);
- SQLite >.import [Filename.csv] NewTable
To back up the database:
- /* Usage:sqlite3 [database]. dump > [FileName] * /
- Sqlite3 mytable.db dump > Backup.sql
To recover a database:
- /* Usage:sqlite3 [database] < [filename] * /
- Sqlite3 Mytable.db < Backup.sql
Lan Yi Education SQLite Database