Feuille de chaleur SQLite

SQLite - Moteur de base de données léger

SQLite est une bibliothèque en langage C qui implémente un petit moteur de base de données SQL, rapide, autonome et fiable. SQLite est le moteur de base de données le plus utilisé au monde.

Copier toutes les commandes Générer PDF

Sommaire

• [Installation] (LINK_0)
• [Commandes de base] (LINK_0)
• [Opérations de base de données] (LINK_0)
• [Table des opérations] (__LINK_0___)
• Types de données
• [Opérations du CRUD] (LINK_0)
• [Opérations de secours] (LINK_0)
• [Indexes] (LINK_0)
• Vues
• [Triggers] (LINK_0)
• [Transactions] (LINK_0)
• Retour et restauration
• [Optimisation du rendement] (LINK_0)
• [Extensions SQLite] (LINK_0)
• [Interfaces de programmation] (LINK_0)
• [Meilleures pratiques] (LINK_0)

Installation

Ubuntu/Debian

# Install SQLite
sudo apt-get update
sudo apt-get install sqlite3

# Install development libraries
sudo apt-get install libsqlite3-dev

# Check version
sqlite3 --version

CentOS/RHEL/Fedora

# Install SQLite
sudo yum install sqlite
# or
sudo dnf install sqlite

# Install development libraries
sudo yum install sqlite-devel
# or
sudo dnf install sqlite-devel
```_

### MACOS
```bash
# SQLite is pre-installed on macOS
sqlite3 --version

# Install via Homebrew for latest version
brew install sqlite
```_

### Fenêtres
```bash
# Download from https://www.sqlite.org/download.html
# Extract sqlite3.exe to a directory in your PATH

# Or install via Chocolatey
choco install sqlite

Source

# Download source
wget https://www.sqlite.org/2023/sqlite-autoconf-3420000.tar.gz
tar -xzf sqlite-autoconf-3420000.tar.gz
cd sqlite-autoconf-3420000

# Compile and install
./configure
make
sudo make install

Commandes de base

Démarrer SQLite

# Start SQLite with a database file
sqlite3 mydatabase.db

# Start SQLite in memory
sqlite3 :memory:

# Start SQLite with options
sqlite3 -header -column mydatabase.db

# Execute SQL from command line
sqlite3 mydatabase.db "SELECT * FROM users;"

# Execute SQL from file
sqlite3 mydatabase.db < script.sql

Commandes SQLite Shell

-- Show help
.help

-- Show databases
.databases

-- Show tables
.tables

-- Show schema for all tables
.schema

-- Show schema for specific table
.schema users

-- Show indexes
.indexes

-- Show current settings
.show

-- Set output mode
.mode column
.mode csv
.mode html
.mode json
.mode line
.mode list
.mode tabs

-- Set headers on/off
.headers on
.headers off

-- Set column width
.width 10 20 15

-- Import CSV file
.mode csv
.import data.csv users

-- Export to CSV
.mode csv
.output users.csv
SELECT * FROM users;
.output stdout

-- Execute shell command
.shell ls -la

-- Quit SQLite
.quit
.exit

Opérations de bases de données

Création et ouverture de bases de données

-- Create/open database (from command line)
sqlite3 mydatabase.db

-- Attach additional database
ATTACH DATABASE 'other.db' AS other;

-- Detach database
DETACH DATABASE other;

-- List attached databases
.databases

-- Backup database
.backup backup.db

-- Restore database
.restore backup.db

Informations sur la base de données

-- Get SQLite version
SELECT sqlite_version();

-- Get database file size
SELECT page_count * page_size as size FROM pragma_page_count(), pragma_page_size();

-- Get database statistics
PRAGMA database_list;
PRAGMA table_info(users);
PRAGMA index_list(users);
PRAGMA foreign_key_list(users);

-- Analyze database
ANALYZE;

-- Vacuum database (reclaim space)
VACUUM;

-- Incremental vacuum
PRAGMA auto_vacuum = INCREMENTAL;
PRAGMA incremental_vacuum(1000);

Tableau des opérations

Création de tableaux

-- Basic table creation
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    age INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Table with foreign key
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    amount REAL,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Table with check constraint
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL CHECK(price > 0),
    category TEXT DEFAULT 'general'
);

-- Temporary table
CREATE TEMPORARY TABLE temp_data (
    id INTEGER,
    value TEXT
);

-- Create table from query
CREATE TABLE user_summary AS
SELECT 
    age,
    COUNT(*) as count,
    AVG(age) as avg_age
FROM users
GROUP BY age;

Modifier les tableaux

-- Add column
ALTER TABLE users ADD COLUMN phone TEXT;

-- Rename table
ALTER TABLE users RENAME TO customers;

-- Rename column (SQLite 3.25.0+)
ALTER TABLE users RENAME COLUMN name TO full_name;

-- Drop column (SQLite 3.35.0+)
ALTER TABLE users DROP COLUMN phone;

-- Drop table
DROP TABLE IF EXISTS temp_data;

Renseignements sur le tableau

-- Show table structure
.schema users
PRAGMA table_info(users);

-- Show table statistics
SELECT 
    name,
    sql
FROM sqlite_master 
WHERE type = 'table' AND name = 'users';

-- Count rows in table
SELECT COUNT(*) FROM users;

-- Get table size
SELECT 
    name,
    COUNT(*) as row_count
FROM sqlite_master sm
JOIN pragma_table_info(sm.name) pti
WHERE sm.type = 'table'
GROUP BY sm.name;

Types de données

Types de données SQLite

-- SQLite has dynamic typing with storage classes:
-- NULL, INTEGER, REAL, TEXT, BLOB

-- Type affinity examples
CREATE TABLE type_examples (
    id INTEGER PRIMARY KEY,           -- INTEGER affinity
    name TEXT,                        -- TEXT affinity
    price REAL,                       -- REAL affinity
    data BLOB,                        -- BLOB affinity
    flag BOOLEAN,                     -- NUMERIC affinity (stored as INTEGER)
    created_date DATE,                -- NUMERIC affinity
    created_time DATETIME,            -- NUMERIC affinity
    amount DECIMAL(10,2),             -- NUMERIC affinity
    description VARCHAR(255)          -- TEXT affinity
);

-- Date and time functions
INSERT INTO events VALUES (
    1,
    'Event 1',
    date('now'),                      -- Current date
    datetime('now'),                  -- Current datetime
    time('now'),                      -- Current time
    datetime('now', '+1 day'),        -- Tomorrow
    datetime('now', '-1 month'),      -- Last month
    strftime('%Y-%m-%d %H:%M:%S', 'now')  -- Formatted datetime
);

CRUD Opérations

Insérer les opérations

-- Insert single row
INSERT INTO users (name, email, age) 
VALUES ('John Doe', 'john@example.com', 30);

-- Insert multiple rows
INSERT INTO users (name, email, age) VALUES
    ('Alice Smith', 'alice@example.com', 25),
    ('Bob Johnson', 'bob@example.com', 35),
    ('Carol Brown', 'carol@example.com', 28);

-- Insert with default values
INSERT INTO users (name, email) 
VALUES ('Default User', 'default@example.com');

-- Insert or replace
INSERT OR REPLACE INTO users (id, name, email, age)
VALUES (1, 'John Updated', 'john.new@example.com', 31);

-- Insert or ignore
INSERT OR IGNORE INTO users (name, email, age)
VALUES ('Duplicate', 'john@example.com', 30);

-- Insert from select
INSERT INTO user_backup 
SELECT * FROM users WHERE age > 25;

Sélectionner les opérations

-- Basic select
SELECT * FROM users;

-- Select specific columns
SELECT name, email FROM users;

-- Select with alias
SELECT name AS full_name, email AS email_address FROM users;

-- Select with conditions
SELECT * FROM users WHERE age > 25;
SELECT * FROM users WHERE name LIKE 'John%';
SELECT * FROM users WHERE email IS NOT NULL;

-- Select with multiple conditions
SELECT * FROM users 
WHERE age BETWEEN 25 AND 35 
AND email LIKE '%@example.com';

-- Select with ordering
SELECT * FROM users ORDER BY age DESC;
SELECT * FROM users ORDER BY name ASC, age DESC;

-- Select with limit
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;

-- Select distinct
SELECT DISTINCT age FROM users;

-- Select with aggregation
SELECT COUNT(*) FROM users;
SELECT AVG(age) FROM users;
SELECT MIN(age), MAX(age) FROM users;
SELECT age, COUNT(*) FROM users GROUP BY age;
SELECT age, COUNT(*) FROM users GROUP BY age HAVING COUNT(*) > 1;

Mise à jour des opérations

-- Update single row
UPDATE users 
SET age = 31 
WHERE id = 1;

-- Update multiple columns
UPDATE users 
SET name = 'John Smith', age = 32 
WHERE id = 1;

-- Update with conditions
UPDATE users 
SET age = age + 1 
WHERE age < 30;

-- Update with subquery
UPDATE users 
SET age = (SELECT AVG(age) FROM users) 
WHERE age IS NULL;

-- Update or insert (upsert)
INSERT INTO users (id, name, email, age) 
VALUES (1, 'John', 'john@example.com', 30)
ON CONFLICT(id) DO UPDATE SET
    name = excluded.name,
    email = excluded.email,
    age = excluded.age;

Supprimer les opérations

-- Delete specific rows
DELETE FROM users WHERE id = 1;

-- Delete with conditions
DELETE FROM users WHERE age < 18;

-- Delete all rows
DELETE FROM users;

-- Delete with subquery
DELETE FROM users 
WHERE id IN (SELECT id FROM users WHERE age > 65);

Opérations de requêtes

Rejoignez

-- Inner join
SELECT u.name, o.amount, o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- Left join
SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- Cross join
SELECT u.name, p.name
FROM users u
CROSS JOIN products p;

-- Self join
SELECT u1.name, u2.name AS manager
FROM users u1
LEFT JOIN users u2 ON u1.manager_id = u2.id;

Sous-requêtes

-- Subquery in WHERE clause
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

-- Subquery in SELECT clause
SELECT 
    name,
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count
FROM users;

-- Correlated subquery
SELECT * FROM users u1
WHERE age > (SELECT AVG(age) FROM users u2 WHERE u2.department = u1.department);

-- EXISTS subquery
SELECT * FROM users
WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = users.id);

Fonctions de fenêtre (SQLite 3.25.0+)

-- Row number
SELECT 
    name,
    age,
    ROW_NUMBER() OVER (ORDER BY age) AS row_num
FROM users;

-- Rank
SELECT 
    name,
    age,
    RANK() OVER (ORDER BY age DESC) AS rank
FROM users;

-- Dense rank
SELECT 
    name,
    age,
    DENSE_RANK() OVER (ORDER BY age DESC) AS dense_rank
FROM users;

-- Partition by
SELECT 
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

-- Running total
SELECT 
    name,
    amount,
    SUM(amount) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) AS running_total
FROM orders;

Expressions communes de tableaux (ECT)

-- Basic CTE
WITH young_users AS (
    SELECT * FROM users WHERE age < 30
)
SELECT * FROM young_users WHERE name LIKE 'A%';

-- Recursive CTE
WITH RECURSIVE fibonacci(n, fib_n, next_fib_n) AS (
    SELECT 1, 0, 1
    UNION ALL
    SELECT n+1, next_fib_n, fib_n + next_fib_n 
    FROM fibonacci 
    WHERE n < 10
)
SELECT n, fib_n FROM fibonacci;

-- Multiple CTEs
WITH 
high_value_orders AS (
    SELECT * FROM orders WHERE amount > 1000
),
premium_users AS (
    SELECT DISTINCT user_id FROM high_value_orders
)
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
JOIN premium_users pu ON u.id = pu.user_id
JOIN high_value_orders o ON u.id = o.user_id
GROUP BY u.name;

Indices

Création d'index

-- Simple index
CREATE INDEX idx_users_email ON users(email);

-- Composite index
CREATE INDEX idx_users_age_name ON users(age, name);

-- Unique index
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- Partial index
CREATE INDEX idx_active_users ON users(name) WHERE active = 1;

-- Expression index
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- Covering index
CREATE INDEX idx_users_covering ON users(age) INCLUDE (name, email);

Gestion des index

-- List indexes
.indexes
SELECT name FROM sqlite_master WHERE type = 'index';

-- Show index info
PRAGMA index_info(idx_users_email);
PRAGMA index_list(users);

-- Drop index
DROP INDEX idx_users_email;

-- Analyze index usage
EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'john@example.com';

-- Reindex
REINDEX;
REINDEX idx_users_email;

Vues

Création de vues

-- Simple view
CREATE VIEW active_users AS
SELECT id, name, email 
FROM users 
WHERE active = 1;

-- Complex view with joins
CREATE VIEW user_order_summary AS
SELECT 
    u.id,
    u.name,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.amount), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- Updatable view
CREATE VIEW young_users AS
SELECT id, name, email, age
FROM users
WHERE age < 30;

Gestion des vues

-- List views
SELECT name FROM sqlite_master WHERE type = 'view';

-- Show view definition
.schema active_users

-- Drop view
DROP VIEW active_users;

-- Update through view (if updatable)
UPDATE young_users SET age = 25 WHERE id = 1;

Déclencheurs

Création de déclencheurs

-- Before insert trigger
CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    UPDATE users SET created_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

-- After update trigger
CREATE TRIGGER after_user_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_audit (user_id, action, old_value, new_value, timestamp)
    VALUES (NEW.id, 'UPDATE', OLD.name, NEW.name, CURRENT_TIMESTAMP);
END;

-- Instead of trigger (for views)
CREATE TRIGGER instead_of_user_view_insert
INSTEAD OF INSERT ON user_view
FOR EACH ROW
BEGIN
    INSERT INTO users (name, email) VALUES (NEW.name, NEW.email);
END;

-- Conditional trigger
CREATE TRIGGER validate_email
BEFORE INSERT ON users
FOR EACH ROW
WHEN NEW.email NOT LIKE '%@%'
BEGIN
    SELECT RAISE(ABORT, 'Invalid email format');
END;

Gestion des déclencheurs

-- List triggers
SELECT name FROM sqlite_master WHERE type = 'trigger';

-- Show trigger definition
.schema before_user_insert

-- Drop trigger
DROP TRIGGER before_user_insert;

Opérations

Opérations de base

-- Begin transaction
BEGIN TRANSACTION;

-- Perform operations
INSERT INTO users (name, email) VALUES ('Test User', 'test@example.com');
UPDATE users SET age = 25 WHERE name = 'Test User';

-- Commit transaction
COMMIT;

-- Rollback transaction
ROLLBACK;

Types de transactions

-- Deferred transaction (default)
BEGIN DEFERRED TRANSACTION;

-- Immediate transaction
BEGIN IMMEDIATE TRANSACTION;

-- Exclusive transaction
BEGIN EXCLUSIVE TRANSACTION;

-- Savepoints
BEGIN TRANSACTION;
INSERT INTO users (name, email) VALUES ('User 1', 'user1@example.com');
SAVEPOINT sp1;
INSERT INTO users (name, email) VALUES ('User 2', 'user2@example.com');
ROLLBACK TO sp1;  -- Rollback to savepoint
COMMIT;

Contrôle des transactions

-- Check if in transaction
SELECT sqlite_version(), 
       CASE WHEN sqlite_compileoption_used('THREADSAFE') 
            THEN 'threadsafe' 
            ELSE 'not threadsafe' 
       END;

-- Set transaction mode
PRAGMA journal_mode = WAL;  -- Write-Ahead Logging
PRAGMA synchronous = NORMAL;

Sauvegarde et restauration

Méthodes de sauvegarde

-- SQL dump backup
.output backup.sql
.dump
.output stdout

-- Specific table backup
.output users_backup.sql
.dump users
.output stdout

-- Binary backup (from shell)
sqlite3 mydatabase.db ".backup backup.db"

-- Online backup using VACUUM
VACUUM INTO 'backup.db';

Restaurer les méthodes

-- Restore from SQL dump
sqlite3 newdatabase.db < backup.sql

-- Restore from binary backup
sqlite3 newdatabase.db ".restore backup.db"

-- Copy database file (when not in use)
cp mydatabase.db backup.db

Sauvegarde progressive

-- Enable WAL mode for incremental backup
PRAGMA journal_mode = WAL;

-- Backup WAL file
-- Copy both .db and .db-wal files

Optimisation des performances

Optimisation des requêtes

-- Use EXPLAIN QUERY PLAN
EXPLAIN QUERY PLAN 
SELECT * FROM users WHERE email = 'john@example.com';

-- Create appropriate indexes
CREATE INDEX idx_users_email ON users(email);

-- Use ANALYZE to update statistics
ANALYZE;

-- Optimize queries with LIMIT
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

-- Use covering indexes
CREATE INDEX idx_users_covering ON users(email) INCLUDE (name, age);

Configuration de la base de données

-- Set page size (before creating database)
PRAGMA page_size = 4096;

-- Set cache size
PRAGMA cache_size = 10000;  -- 10000 pages

-- Set journal mode
PRAGMA journal_mode = WAL;  -- Write-Ahead Logging

-- Set synchronous mode
PRAGMA synchronous = NORMAL;  -- FULL, NORMAL, OFF

-- Set temp store
PRAGMA temp_store = MEMORY;  -- MEMORY, FILE

-- Set mmap size
PRAGMA mmap_size = 268435456;  -- 256MB

-- Auto vacuum
PRAGMA auto_vacuum = INCREMENTAL;

Opérations en vrac

-- Disable auto-commit for bulk inserts
BEGIN TRANSACTION;
-- Insert many rows
INSERT INTO users (name, email) VALUES ('User1', 'user1@example.com');
INSERT INTO users (name, email) VALUES ('User2', 'user2@example.com');
-- ... many more inserts
COMMIT;

-- Use prepared statements (in application code)
-- Prepare once, execute many times

-- Bulk insert from CSV
.mode csv
.import large_file.csv users

Extensions SQLite

Chargement des extensions

-- Enable extension loading
.load ./extension_name

-- Common extensions
.load ./fts5        -- Full-text search
.load ./rtree       -- R-tree spatial index
.load ./json1       -- JSON functions

Fonctions JSON (extension Json1)

-- JSON functions
SELECT json('{"name":"John","age":30}');
SELECT json_extract('{"name":"John","age":30}', '$.name');
SELECT json_array('a', 'b', 'c');
SELECT json_object('name', 'John', 'age', 30);

-- JSON table
CREATE TABLE users_json (
    id INTEGER PRIMARY KEY,
    data JSON
);

INSERT INTO users_json (data) VALUES 
('{"name":"John","age":30,"city":"New York"}'),
('{"name":"Jane","age":25,"city":"Boston"}');

SELECT 
    id,
    json_extract(data, '$.name') AS name,
    json_extract(data, '$.age') AS age
FROM users_json;

Recherche en texte intégral (FTS5)

-- Create FTS table
CREATE VIRTUAL TABLE documents USING fts5(title, content);

-- Insert documents
INSERT INTO documents VALUES 
('SQLite Tutorial', 'Learn SQLite database management'),
('Python Guide', 'Python programming tutorial');

-- Search documents
SELECT * FROM documents WHERE documents MATCH 'sqlite';
SELECT * FROM documents WHERE documents MATCH 'tutorial OR guide';

-- Highlight matches
SELECT highlight(documents, 0, '<b>', '</b>') AS title
FROM documents WHERE documents MATCH 'sqlite';

Interfaces de programmation

Python (sqlite3)

import sqlite3

# Connect to database
conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()

# Execute query
cursor.execute("SELECT * FROM users WHERE age > ?", (25,))
rows = cursor.fetchall()

# Insert data
cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", 
               ("John Doe", "john@example.com"))

# Commit and close
conn.commit()
conn.close()

# Context manager
with sqlite3.connect('mydatabase.db') as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users")
    rows = cursor.fetchall()

Node.js (sqlite3)

const sqlite3 = require('sqlite3').verbose();

// Open database
const db = new sqlite3.Database('mydatabase.db');

// Run query
db.all("SELECT * FROM users WHERE age > ?", [25], (err, rows) => {
    if (err) {
        console.error(err);
    } else {
        console.log(rows);
    }
});

// Insert data
db.run("INSERT INTO users (name, email) VALUES (?, ?)", 
       ["John Doe", "john@example.com"], function(err) {
    if (err) {
        console.error(err);
    } else {
        console.log(`Row inserted with ID: ${this.lastID}`);
    }
});

// Close database
db.close();

C/C++

#include <sqlite3.h>
#include <stdio.h>

int main() {
    sqlite3 *db;
    char *err_msg = 0;

    // Open database
    int rc = sqlite3_open("mydatabase.db", &db);

    if (rc != SQLITE_OK) {
        fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return 1;
    }

    // Execute SQL
    char *sql = "SELECT * FROM users";
    rc = sqlite3_exec(db, sql, callback, 0, &err_msg);

    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s\n", err_msg);
        sqlite3_free(err_msg);
    }

    // Close database
    sqlite3_close(db);
    return 0;
}

// Callback function
static int callback(void *data, int argc, char **argv, char **azColName) {
    for (int i = 0; i < argc; i++) {
        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    printf("\n");
    return 0;
}

Meilleures pratiques

Conception du schéma

-- Use appropriate data types
CREATE TABLE users (
    id INTEGER PRIMARY KEY,           -- Use INTEGER for auto-increment
    name TEXT NOT NULL,               -- Use TEXT for strings
    age INTEGER CHECK(age >= 0),      -- Use constraints
    email TEXT UNIQUE,                -- Use UNIQUE for unique values
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Use foreign keys
PRAGMA foreign_keys = ON;
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Normalize data appropriately
-- Don't over-normalize for SQLite (it's not a server database)

Meilleures pratiques en matière de rendement

-- Create indexes for frequently queried columns
CREATE INDEX idx_users_email ON users(email);

-- Use LIMIT for large result sets
SELECT * FROM users ORDER BY created_at DESC LIMIT 100;

-- Use transactions for bulk operations
BEGIN TRANSACTION;
-- Multiple INSERT/UPDATE/DELETE statements
COMMIT;

-- Use prepared statements to prevent SQL injection
-- (in application code)

-- Analyze database regularly
ANALYZE;

-- Vacuum database periodically
VACUUM;

Pratiques exemplaires en matière de sécurité

-- Use parameterized queries (in application code)
-- Never concatenate user input into SQL strings

-- Validate input data
CREATE TABLE users (
    email TEXT CHECK(email LIKE '%@%')
);

-- Use triggers for additional validation
CREATE TRIGGER validate_user_age
BEFORE INSERT ON users
FOR EACH ROW
WHEN NEW.age < 0 OR NEW.age > 150
BEGIN
    SELECT RAISE(ABORT, 'Invalid age');
END;

Pratiques exemplaires de maintenance

-- Regular maintenance tasks
PRAGMA integrity_check;  -- Check database integrity
ANALYZE;                 -- Update query planner statistics
VACUUM;                  -- Reclaim unused space

-- Monitor database size
SELECT page_count * page_size as size FROM pragma_page_count(), pragma_page_size();

-- Backup regularly
.backup backup.db

-- Use WAL mode for better concurrency
PRAGMA journal_mode = WAL;

---

Résumé

SQLite est un moteur de base de données léger et intégré qui est parfait pour les applications qui ont besoin d'une base de données simple et fiable sans le survol d'un serveur de base de données complet. Cette triche couvre les commandes SQLite essentielles et les meilleures pratiques pour une gestion efficace des bases de données.

Principales forces: - Légèreté: Pas de configuration du serveur nécessaire, la base de données est un seul fichier - Acid Compliant: prise en charge complète des transactions avec des capacités de retour - Platforme de choc: Fonctionne sur pratiquement tout système d'exploitation - ** Configuration de Zero: Aucune installation ou administration nécessaire - ** Autocontenu : Aucune dépendance externe

Cas de la meilleure utilisation: - Applications mobiles (iOS, Android) - Applications bureautiques - Sites Web de petite à moyenne taille - Prototypage et développement - Analyse et communication des données - Systèmes embarqués

** Considérations importantes :** - Ne convient pas aux scénarios d'écriture à haute devises - Limité à un seul auteur, accès à plusieurs lecteurs - Pas de gestion d'utilisateur ou d'accès réseau intégré - Taille de la base de données pratiquement limitée à quelques téraoctets - Certaines fonctionnalités SQL ne sont pas prises en charge (RIGHT JOIN, FULL OUTER JOIN, etc.)

En suivant les pratiques et techniques de cette triche, vous pouvez utiliser efficacement SQLite pour un large éventail d'applications tout en conservant une bonne performance et intégrité des données.