SQLite3 is a powerful, embedded, relational database management system. It is a self-contained, serverless, zero-configuration, transactional SQL database engine. This guide provides essential command-line operations for managing SQLite3 databases.

Database Creation and Shell Access

To create a new SQLite3 database file or open an existing one and launch the interactive shell, use the following command:

sqlite3 <database>

Replace <database> with the desired name for your database file (e.g., my_database.db).

Table Management

Creating Tables

Define the structure of your tables using SQL's CREATE TABLE statement. Here's an example of creating an 'os' table:

sqlite3 <database> "create table os(id integer primary key, name text, year integer);"

Describing Table Schema

To view the schema of an existing table, use the .schema command:

sqlite3 <database> ".schema 'os'"

Listing All Tables

Get a list of all tables within the current database:

sqlite3 <database> ".tables"

Data Manipulation

Inserting Data

Add new records to your tables using the INSERT INTO statement:

sqlite3 <database> "insert into 'os' values(1,'linux',1991);"

Viewing Records

Retrieve all records from a table:

sqlite3 <database> "select * from 'os';"

Conditional Data Retrieval

Filter records based on specific criteria using the WHERE clause:

sqlite3 <database> "select * from 'os' where year='1991';"

Fuzzy Matching with LIKE

Use the LIKE operator for pattern matching in your queries:

sqlite3 <database> "select * from 'os' where year like '19%';"

Importing Data

Importing CSV Files

Easily import data from a CSV file into a new or existing table:

sqlite3 <database> ".import /path/to/city.csv cities"

Ensure the CSV file is correctly formatted and the target table (cities in this example) exists or is created beforehand.

For more advanced operations and a deeper understanding of SQLite3, refer to the official SQLite documentation.