totn SQLite

SQLite Tutorial

SQLite is a popular relational database technology that is contained in a C programming library. SQLite is a compact library that reads and writes directly to ordinary disk files. It is, in essence, a serverless SQL database.

Our tutorial will start with the basics of SQLite such as how to retrieve and manipulate data. Then we will move to the more advanced topics such as how to create tables. We will conclude with a review of the functions that are proprietary to SQLite.

With this tutorial, you should be on your way to becoming proficient in SQLite and its programming language.

Prerequisites

There are no prequisities for this SQLite tutorial. You should be able to easily understand this tutorial and learn the basic concepts of MySQL as you progress to the more advanced topics.

Now, let's get started!

Start Tutorial

Or jump directly to a topic in SQLite:

SQLite Functions

Functions - Alphabetical SQLite Functions listed alphabetically
Functions - Category SQLite Functions listed by category

SQLite Keys, Constraints and Indexes

Primary Keys Create, add, and drop primary keys
Foreign Keys Create foreign keys
Indexes Create, drop, and rename indexes (Performance tuning)
Unique Constraints Create, add, and drop unique constraints
ANALYZE Command Gather statistical information about tables and indexes (used by query optimizer)

SQLite Database Administration

ATTACH DATABASE Attach another database file to your connection
DETACH DATABASE Detach an attached database from your connection
Vacuum Reclaim unused space in database
Auto_Vacuum Process that automatically reclaims unused space in database

SQLite Programming

Literals (Constants) String, number, date, time, boolean literals
Comments within SQL How to create comments within your SQL statement

SQLite Comparison Operators

Comparison Operators Operators such as =, <>, !=, >, <, and so on

SQLite Query Types

SELECT Statement Retrieve records from a table
SELECT LIMIT Statement Retrieve records from a table and limit results
INSERT Statement Insert records into a table
UPDATE Statement Update records in a table
DELETE Statement Delete records from a table
TRUNCATE TABLE Statement Delete all records from a table (with optimizer)
UNION Operator Combine 2 result sets (removes duplicates)
UNION ALL Operator Combine 2 result sets (includes duplicates)
INTERSECT Operator Intersection of 2 result sets
EXCEPT Operator Result set of one minus the result set of another
Subqueries A query within a query

SQLite Joins

JOIN Tables Inner and Outer joins

SQLite Aliases

ALIASES Create a temporary name for a column or table

SQLite Clauses

DISTINCT Clause Retrieve unique records
FROM Clause List tables and join information
WHERE Clause Filter results
ORDER BY Clause Sort query results
GROUP BY Clause Group by one or more columns
HAVING Clause Restrict the groups of returned rows

SQLite SQL Functions

COUNT Function Return the count of an expression
SUM Function Return the sum of an expression
MIN Function Return the min of an expression
MAX Function Return the max of an expression
AVG Function Return the average of an expression

SQLite Conditions

AND Condition 2 or more conditions to be met
OR Condition Any one of the conditions are met
AND and OR Combine AND and OR conditions
LIKE Condition Use wildcards in a WHERE clause
IN Condition Alternative to multiple OR conditions
NOT Condition Negate a condition
IS NULL Condition Test for a NULL value
IS NOT NULL Condition Test for a NOT NULL value
BETWEEN Condition Retrieve within a range (inclusive)
EXISTS Condition Condition is met if subquery returns at least one row

SQLite Tables and Views

CREATE TABLE Create a table
CREATE TABLE AS Create a table from another table's definition and data
ALTER TABLE Add, modify, delete columns in a table; rename a table
DROP TABLE Delete a table
VIEW Virtual tables (views of other tables)

SQLite Data Types

Data Types Data Types in SQLite

SQLite System Tables

SQLite System Tables SQLite system tables and descriptions