Basic SQLite Commands Tutorial

By /

Basic SQLite Commands Tutorial: Your First Steps with Databases

SQLite is a lightweight, serverless, self-contained, and highly reliable SQL database engine. It’s perfect for learning SQL, developing small to medium-sized applications, and embedding databases in devices like smartphones, IoT devices, and even desktop applications. This tutorial will guide you through the essential SQLite commands, providing a solid foundation for working with databases.

1. Installation and Setup

Unlike many database systems, SQLite doesn’t require a separate server process. It’s often already included with operating systems (like macOS and many Linux distributions) or programming languages (like Python). If it’s not installed, you can download it from the official SQLite website: https://www.sqlite.org/download.html

Download the precompiled binary appropriate for your operating system. After downloading, you can typically just extract the executable (e.g., sqlite3.exe on Windows, sqlite3 on macOS/Linux).

2. Starting the SQLite Command Line Interface (CLI)

To interact with SQLite, you use its command-line interface. Open your terminal (or command prompt on Windows) and navigate to the directory where you extracted the SQLite executable. Then, run:

  • To create or open a new database file:

    bash
    sqlite3 mydatabase.db

    This command does the following:
    * If mydatabase.db exists, it opens the database file.
    * If mydatabase.db doesn’t exist, it creates a new database file with that name.

  • To open an existing database file:

    bash
    sqlite3 existing_database.db
    Simply replace existing_database.db with the actual name of your database file.

You should now see the SQLite prompt: sqlite>. You can start entering SQL commands.

3. Essential SQLite Commands

Let’s explore some fundamental commands.

3.1. .help (Display Help)

The .help command is your best friend. It lists all available SQLite dot commands (commands that start with a period).

sqlite
.help

3.2. .databases (List Databases)

This command shows the currently attached databases. Initially, it will show the main database you opened (or created).

sqlite
.databases

3.3. .tables (List Tables)

This command lists all tables within the current database. If you just created a new database, it won’t show anything yet.

sqlite
.tables

3.4. CREATE TABLE (Create a Table)

This is your first SQL command (as opposed to a dot command). It’s used to define the structure of a new table.

sqlite
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
first_name TEXT NOT NULL,
last_name TEXT,
department TEXT,
salary REAL
);

Let’s break this down:

  • CREATE TABLE employees: Creates a table named “employees”.
  • id INTEGER PRIMARY KEY: Creates a column named “id” that stores integers. PRIMARY KEY means this column uniquely identifies each row in the table, and SQLite will automatically manage its values (usually incrementing them).
  • first_name TEXT NOT NULL: Creates a text column named “first_name”. NOT NULL means this column must have a value for every row; you can’t leave it blank.
  • last_name TEXT: Creates a text column named “last_name”.
  • department TEXT: Creates a text column named “department”.
  • salary REAL: Creates a column named “salary” that stores floating-point numbers (numbers with decimal points).
  • ;: The semicolon marks the end of the SQL statement.

3.5. INSERT INTO (Add Data to a Table)

This command adds new rows (records) to your table.

“`sqlite
INSERT INTO employees (first_name, last_name, department, salary)
VALUES (‘John’, ‘Doe’, ‘Sales’, 50000.00);

INSERT INTO employees (first_name, last_name, department, salary)
VALUES (‘Jane’, ‘Smith’, ‘Marketing’, 60000.00);

INSERT INTO employees (first_name, last_name, department, salary)
VALUES (‘Peter’, ‘Jones’, ‘IT’, 75000.00);
“`

  • INSERT INTO employees: Specifies the table you’re inserting data into.
  • (first_name, last_name, department, salary): Lists the columns you’re providing values for. You don’t need to list all columns, but you must provide values for any NOT NULL columns. You also don’t need to provide values for the id column because it is a PRIMARY KEY and is managed automatically.
  • VALUES ('John', 'Doe', 'Sales', 50000.00): Provides the values for the specified columns, in the same order. Text values are enclosed in single quotes (').

3.6. SELECT (Retrieve Data from a Table)

The SELECT statement is the core of querying data. It allows you to retrieve data based on various criteria.

  • Retrieve all columns and all rows:

    sqlite
    SELECT * FROM employees;

    The asterisk (*) means “select all columns”.

  • Retrieve specific columns:

    sqlite
    SELECT first_name, last_name FROM employees;

    This retrieves only the first_name and last_name columns.

  • Retrieve data with a WHERE clause (filtering):

    sqlite
    SELECT * FROM employees WHERE department = 'Sales';

    This retrieves all columns for employees in the ‘Sales’ department.

    sqlite
    SELECT first_name, last_name FROM employees WHERE salary > 60000.00;
    This retrieves the first and last names of employees with a salary greater than 60000.00.

  • Retrieve data with ORDER BY (sorting)

    sqlite
    SELECT * FROM employees ORDER BY last_name;
    Sorts the results alphabetically by last name (ascending order by default).

    sqlite
    SELECT * FROM employees ORDER BY salary DESC;
    Sorts the results by salary in descending order (highest salary first).
    * Retrieve data with LIMIT (limit number of rows)
    sqlite
    SELECT * FROM employees LIMIT 2;
    Retrieve only the first two rows.

3.7. UPDATE (Modify Existing Data)

The UPDATE statement changes the values in existing rows.

sqlite
UPDATE employees
SET salary = 55000.00
WHERE id = 1;

  • UPDATE employees: Specifies the table to update.
  • SET salary = 55000.00: Sets the salary column to 55000.00.
  • WHERE id = 1: Specifies which row(s) to update. This is crucial! Without a WHERE clause, you would update every row in the table.

3.8. DELETE (Remove Data)

The DELETE statement removes rows from a table.

sqlite
DELETE FROM employees
WHERE id = 3;

  • DELETE FROM employees: Specifies the table to delete from.
  • WHERE id = 3: Specifies which row(s) to delete. Again, the WHERE clause is essential to avoid deleting all your data.

3.9. .schema (Show Table Schema)

This command shows the CREATE TABLE statement that was used to create a table (or all tables). This is very useful for understanding the structure of a table.

sqlite
.schema employees
Or, to see the schema for all tables:
sqlite
.schema

3.10. .quit or .exit (Exit SQLite)

These commands exit the SQLite CLI.

sqlite
.quit

Or

sqlite
.exit

4. Important Concepts

  • Data Types: SQLite supports several data types, including INTEGER (whole numbers), REAL (floating-point numbers), TEXT (strings), BLOB (binary data), and NULL (represents a missing or unknown value). SQLite is dynamically typed, meaning you don’t have to strictly declare the type, but it’s good practice to do so.
  • Primary Key: A column (or a combination of columns) that uniquely identifies each row in a table.
  • Constraints: Rules that enforce data integrity. NOT NULL is a constraint that ensures a column cannot contain a NULL value. UNIQUE ensures that all values in a column (or combination of columns) are unique.
  • Transactions: A sequence of SQL statements that are treated as a single unit of work. SQLite supports transactions, ensuring data consistency.

5. Example: Creating and Populating a products Table

Let’s create another table and practice some more commands.

“`sqlite
— Create the products table
CREATE TABLE products (
product_id INTEGER PRIMARY KEY,
product_name TEXT NOT NULL,
price REAL,
category TEXT
);

— Insert some products
INSERT INTO products (product_name, price, category) VALUES (‘Laptop’, 1200.00, ‘Electronics’);
INSERT INTO products (product_name, price, category) VALUES (‘T-Shirt’, 25.00, ‘Clothing’);
INSERT INTO products (product_name, price, category) VALUES (‘Coffee Maker’, 75.00, ‘Appliances’);
INSERT INTO products (product_name, price, category) VALUES (‘Headphones’, 100.00, ‘Electronics’);

— Retrieve all products
SELECT * FROM products;

— Retrieve products in the ‘Electronics’ category
SELECT * FROM products WHERE category = ‘Electronics’;

— Update the price of the laptop
UPDATE products SET price = 1100.00 WHERE product_name = ‘Laptop’;

— Delete the T-Shirt
DELETE FROM products WHERE product_name = ‘T-Shirt’;

— Show the updated products table
SELECT * FROM products;
“`

This tutorial provides a basic introduction to SQLite commands. There’s much more to learn, including more complex queries, joins (combining data from multiple tables), indexes (for faster data retrieval), and other advanced features. However, this foundation should give you the confidence to start working with SQLite and exploring the world of databases! Remember to use .help frequently and experiment with different commands. Good luck!

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top