SQLite Introduction

SQLite Introduction Notes

Introduction

• SQLite is a database that every developer should have experience with.
• The lecture will cover reasons to use SQLite and basic commands.

Resources

• Free intro to software development career course via CourseCareers:
  • Introduction to a larger course covering software development.
  • Specializations include front end, back end, or DevOps.
• Ad-free and downloadable videos available on the instructor's website.
• Code from the video is available on GitHub.

Pronunciation and Overview

• The author of SQLite pronounces it "SQLite."
• SQLite.org is the official webpage.
• SQLite is often called an embedded database, and it's one of the most popular databases due to its widespread integration with software.
• It is available on mobile phones, computers and bundled with many applications.
• SQLite doesn't create a separate process for the database.
• It is embedded in the application.
• SQLite is "serverless" because the database engine runs within the same process, thread, and address space as the application.
• There is no message passing or network activity involved.

Comparison with Typical Web Development Databases

• Typical setup: separate database server shared by all users of a web page through a back end.
• SQLite is suitable for desktop software, distributed IoT software, or appliances where many instances of the database are needed without central connection.
• Consider SQLite for pairing a database with software for distribution instead of hosting as a service.

Use Cases and Benefits

• SQLite is effective for web development but may not be the first choice compared to Postgres or other relational databases.
• SQLite can support hundreds of thousands of visits for websites.
• Free to use in production and commercial use.
• Licenses are available for legal proof of usage rights.
• SQLite is easy to work with and suitable for many uses.

Downloading and Setup

• Download SQLite from the downloads page.
• On a Mac, use Homebrew via the command: brew install SQLite.
• Install Homebrew, if needed, by visiting brew.sh and pasting the provided command in the terminal.
• For non-Mac or Linux systems, use one of the options on the SQLite download page.
• The lesson will cover using SQLite from an application in an upcoming lesson.
• For now, use SQLite directly from the terminal.
• After installation, add SQLite to your path by running the command provided in the terminal.

Basic Commands

• Launch SQLite and create a database file:
  • sqlite3 intro.db
• Clear the screen: .shell clear
• Use up and down arrows to access previous commands.

Dot Commands

• Special commands for SQLite begin with a dot.
• These commands are not SQL but provide information about the database or perform auxiliary operations.
• Documentation: SQLite.org/CLI.HTML.
• Examples:
  • .help: List available dot commands.
  • .tables: List tables in the database.
  • .show: Display current settings.
  • .databases: Show the path to the database file.
  • .schema: Display the schema of the database.

Creating Tables

• A table describes an entity.
• Command: create table.
• Example: Create a table for user information.
• Example:
  CREATE TABLE users ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, username TEXT NOT NULL UNIQUE, email TEXT, age INTEGER, created_at DATETIME DEFAULT CURRENT_TIMESTAMP );
• Each column definition includes the name, data type, and constraints.
• PRIMARY KEY: uniquely identifies each row in the table.
• NOT NULL: requires that the column must have a value.
• UNIQUE: ensures that no two rows have the same value in this column.
• DEFAULT: specifies a default value for the column if none is provided during insertion.

Working with Files

• Working inside the terminal can be difficult, consider working in a file and then executing that file.
• Use a code editor like Visual Studio Code.
• Create a new file with a .sql extension (e.g., intro.sql).
• Run the SQL file from the terminal using the .read command:
  • sqlite3 intro.db
  • .read intro.sql

Modifying Table Structure

• ALTER TABLE: modifies the structure of an existing table.
• Example: Add a status column to the users table.
  sql ALTER TABLE users ADD COLUMN status TEXT;

Deleting Tables

• DROP TABLE: deletes a table.
• Example:
  sql DROP TABLE users;

DDL Commands

• Data Definition Language (DDL) commands:
  • CREATE TABLE
  • ALTER TABLE
  • DROP TABLE

Data Manipulation Language (DML)

• DML is used to insert, update, and remove data.

Inserting Data

• INSERT INTO: adds new rows to a table.
• Specify the table name and the columns to insert values into.
• Example:
  sql INSERT INTO users (name, username) VALUES ('Caleb Curry', 'calcur123');
• Only NOT NULL columns are required.

Selecting Data

• SELECT: retrieves data from one or more tables.
• Example: Select all columns and rows from the users table.
  sql SELECT * FROM users;
• WHERE: filters rows based on a condition.
• LIMIT: limits the number of rows returned.

Updating Data

• UPDATE: modifies existing data in a table.
• SET: specifies the columns to update and their new values.
• WHERE: specifies which rows to update.
• Example:
  sql UPDATE users SET email = 'newemail@gmail.com' WHERE id = 1;

Deleting Data

• DELETE FROM: removes rows from a table.
• WHERE: specifies which rows to delete.
• Example:
  sql DELETE FROM users WHERE id = 2;

Foreign Keys

• Create a relationship across two tables.
• Create a table for posts.
• Each post is created by a user, so use the users table.
• Example:
  sql CREATE TABLE posts ( id INTEGER PRIMARY KEY, user_id INTEGER REFERENCES users(id), title TEXT NOT NULL, body TEXT NOT NULL );
• REFERENCES: establishes a foreign key relationship to the users table.

Joins

• To retrieve related data from multiple tables, use JOIN.
• Example:
  sql SELECT * FROM posts JOIN users ON posts.user_id = users.id;
• This query retrieves all columns from both the posts and users tables where the user_id in posts matches the id in users.
• Aliases can be used to shorten table names.
• Example:
  sql SELECT p.body, u.username FROM posts p JOIN users u ON p.user_id = u.id;

Views

• Create a virtual table based on a query.
• Example:
  sql CREATE VIEW posts_info AS SELECT p.body, u.username FROM posts p JOIN users u ON p.user_id = u.id;
• Now, you can query the view as if it were a table:
  • SELECT * FROM posts_info;

Conclusion

• SQLite Overview.
• Additional resources, such as a video about using SQLite from an application, will be created.
• Like, subscribe, turn on notifications.