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!
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 |
Advertisements