This SQLite tutorial teaches you everything you need to know to start using SQLite effectively. In this tutorial, you will learn SQLite step by step through extensive hands-on practices.
This SQLite tutorial is designed for developers who want to use SQLite as the back-end database or to use SQLite to manage structured data in applications including desktop, web, and mobile apps.
SQLite is an open-source, zero-configuration, self-contained, stand-alone, transaction relational database engine designed to be embedded into an application.
Getting started with SQLite
You should go through this section if this is the first time you have worked with SQLite. Follow these 4-easy steps to get started with SQLite fast.
- First, help you answer the first and important question: what is SQLite? You will have a brief overview of SQLite.
- Second, show you step by step how to download and install the SQLite tools on your computer.
- Third, introduce you to an SQLite sample database and walk you through the steps of using the sample database for practicing.
- Finally, guide you on how to use the sqlite3 commands.
Basic SQLite tutorial
This section presents basic SQL statements that you can use with SQLite. You will first start querying data from the sample database. If you are already familiar with SQL, you will notice the differences between SQL standard and the SQL dialect used in SQLite.
Section 1. Simple query
- Select – query data from a single table using
Section 2. Sorting rows
- Order By – sort the result set in ascending or descending order.
Section 3. Filtering data
- Select Distinct – query unique rows from a table using the
- Where – filter rows of a result set using various conditions.
- Limit – constrain the number of rows returned by a query and how to get only the necessary data from a table.
- Between – test whether a value is in a range of values.
- In – check if a value matches any value in a list of values or subquery.
- Like – query data based on pattern matching using wildcard characters: percent sign (
%) and underscore (
- Glob – determine whether a string matches a specific UNIX-pattern.
- IS NULL – check if a value is null or not.
Section 4. Joining tables
- SQLite join – learn the overview of joins including inner join, left join, and cross join.
- Inner Join – query data from multiple tables using the inner join clause.
- Left Join – combine data from multiple tables using the left join clause.
- Cross Join – show you how to use the cross join clause to produce a cartesian product of result sets of the tables involved in the join.
- Self Join – join a table to itself to create a result set that joins rows with other rows within the same table.
- Full Outer Join – show you how to emulate the full outer join in the SQLite using left join and union clauses.
Section 6. Set operators
- Union – combine result sets of multiple queries into a single result set. We also discuss the differences between
- Except – compare the result sets of two queries and returns distinct rows from the left query that are not output by the right query.
- Intersect – compare the result sets of two queries and returns distinct rows that are output by both queries.
Section 8. More querying techniques
- Case – add conditional logic to the query.
Section 9. Changing data
This section guides you on how to update data in the table using insert, update, delete, and replace statements.
Section 10. Transactions
- Transaction – show you how to handle transactions in SQLite.
Section 11. Data definition
In this section, you’ll learn how to create database objects such as tables, views, and indexes using SQL data definition language.
- SQLite Data Types – introduce you to the SQLite dynamic type system and its important concepts: storage classes, manifest typing, and type affinity.
- Create Table – show you how to create a new table in the database.
- Alter Table – show you how to use modify the structure of an existing table.
- Rename column – learn step by step how to rename a column of a table.
- Drop Table – guide you on how to remove a table from the database.
- VACUUM – show you how to optimize database files.
Section 12. Constraints
- Primary Key – show you how to define the primary key for a table.
- NOT NULL constraint – learn how to enforce values of columns that are not NULL.
- UNIQUE constraint – ensure values in a column or a group of columns are unique.
- CHECK constraint – ensure the values in a column meet a specified condition defined by an expression.
- AUTOINCREMENT – explain how the
AUTOINCREMENTcolumn attribute works and why you should avoid using it.
Section 16. Full-text search
- Full-text search – get started with the full-text search in SQLite.
Section 17. SQLite tools
- SQLite Commands – show you the most commonly used command in the sqlite3 program.
- SQLite Show Tables – list all tables in a database.
- SQLite Describe Table – show the structure of a table.
- SQLite Dump – how to use the
.dumpcommand to back up and restore a database.
- SQLite Import CSV – import CSV files into a table.
- SQLite Export CSV – export an SQLite database to CSV files.