SQLite 3 Beyond the Basics

This tutorial delves into SQLite 3, building upon the introductory concepts of database and table creation. We'll explore advanced features and functionalities, comparing them to those found in other database systems.

This guide assumes familiarity with SQLite 3 basics.

Key Concepts:

• Standard SQL Compliance: SQLite 3 supports core SQL commands (SELECT, WHERE, ORDER BY, LIMIT) for efficient data manipulation.
• ACID Properties: Transactions ensure data integrity through atomicity, consistency, isolation, and durability.
• Multi-Database Management: Attaching and detaching databases allows concurrent operations across multiple datasets.
• Nested Transactions (Savepoints): Granular control over database changes with rollback capabilities to specific points within a transaction.
• Data Import/Export: Streamlined data backup and restoration using .dump and .read commands.

SELECT Queries and Clauses:

The SELECT statement retrieves data. We'll create a Users table in a Library.db database to illustrate:

CREATE TABLE Users (
  SerialNo INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  Name TEXT NOT NULL,
  Id TEXT NOT NULL UNIQUE,
  Age INTEGER NOT NULL,
  DOJ TEXT NOT NULL
);

Data insertion can be done individually using INSERT INTO, or efficiently using .read to execute SQL commands from a file (e.g., newusers.sql). The SELECT query retrieves data, with options to customize column display using .header ON, .mode column, and column aliases:

SELECT Id AS 'User ID', Name, DOJ AS 'Date of Joining'
FROM NewUsers;

.schema displays table structures.

WHERE Clause and Operators:

The WHERE clause filters results. SQLite supports various operators: || (concatenation), arithmetic operators, comparison operators, logical operators (AND, OR, NOT), BETWEEN, IN, LIKE, GLOB, EXISTS, IS, IS NOT. Examples:

SELECT * FROM NewUsers WHERE Age >= 20;  -- Age 20 or greater
SELECT * FROM NewUsers WHERE DOJ BETWEEN '2011-12-30' AND '2014-12-30'; -- Date range

ORDER BY and LIMIT Clauses:

ORDER BY sorts results (ASC/DESC). LIMIT restricts the number of returned rows, optionally with OFFSET to skip initial rows.

GROUP BY and HAVING Clauses:

GROUP BY groups rows based on specified columns. HAVING filters grouped results.

SELECT Name, COUNT(Name) FROM NewUsers GROUP BY Name HAVING COUNT(Name) > 1; -- Duplicate names

DISTINCT Keyword:

DISTINCT returns unique values.

Attaching and Detaching Databases:

ATTACH DATABASE adds a database with an alias, enabling queries across multiple databases within a single session. .databases lists attached databases. DETACH DATABASE removes an alias.

Transactions:

SQLite supports transactions (ACID compliant). BEGIN TRANSACTION, COMMIT, ROLLBACK control transaction flow. SAVEPOINT creates nested transactions for granular control, allowing rollback to specific points using ROLLBACK TO SAVEPOINT and release using RELEASE SAVEPOINT. Autocommit mode is the default, executing each query as a separate transaction.

Exporting Databases:

.dump exports database content to SQL format, optionally for a specific table. .output redirects query output to a file.

Conclusion:

This tutorial covered advanced SQLite 3 features, enhancing your ability to manage and query data efficiently. The FAQ section further clarifies savepoint management.

The above is the detailed content of SQLite 3 Beyond the Basics. For more information, please follow other related articles on the PHP Chinese website!