Filtrare e Ordinare i Dati

Bentornato.

Nella lezione precedente hai imparato tipi di dati e vincoli.

Hai imparato che PostgreSQL non dovrebbe accettare assurdità.

Niente età banana.

Niente prezzi negativi.

Niente email duplicate.

Niente utenti misteriosi e vuoti che entrano nel database come se fosse casa loro.

Molto bene.

Oggi impariamo a trovare i dati che ci servono davvero.

Perché quando una tabella ha molte righe, questo non basta:

SELECT * FROM products;

Questo restituisce tutto.

A volte non vuoi tutto.

Vuoi:

• prodotti più economici di 100;
• utenti di una città;
• clienti attivi;
• libri pubblicati dopo un certo anno;
• corsi che contengono la parola JavaScript;
• i prodotti più costosi;
• solo i primi cinque risultati.

Qui filtrare e ordinare diventano importanti.

Un database è utile non solo perché salva dati.

Un database è utile perché ti aiuta a trovare i dati giusti.

Altrimenti è solo un cassetto molto costoso.

Cosa Imparerai

In questa lezione imparerai:

• come filtrare righe con WHERE;
• come funzionano gli operatori di confronto;
• come usare =;
• come usare !=;
• come usare >;
• come usare <;
• come usare >=;
• come usare <=;
• come combinare condizioni con AND;
• come combinare condizioni con OR;
• come cercare testo con LIKE;
• come cercare testo senza differenza tra maiuscole e minuscole con ILIKE;
• come ordinare dati con ORDER BY;
• come limitare risultati con LIMIT;
• come saltare risultati con OFFSET;
• come evitare errori comuni nel filtraggio.

Alla fine di questa lezione saprai fare domande più precise a PostgreSQL.

Non:

Dammi tutto.

Ma:

Dammi i clienti attivi di Milano, ordinati per nome, ma solo i primi 10.

Molto meglio.

Molto più professionale.

Meno urla al database.

Prepara il Database

Apri PostgreSQL:

sudo -iu postgres psql

Connettiti al database:

\c learning_postgresql

Se non hai questo database, crealo:

CREATE DATABASE learning_postgresql;

Poi connettiti:

\c learning_postgresql

Ora crea una tabella per questa lezione.

Useremo products.

Prima rimuovi la vecchia tabella se esiste:

DROP TABLE IF EXISTS products;

Ora creala:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  category VARCHAR(100),
  price NUMERIC(10, 2) CHECK (price >= 0),
  available BOOLEAN DEFAULT true,
  rating INTEGER CHECK (rating >= 1 AND rating <= 5)
);

Questa tabella ha:

• id;
• name;
• category;
• price;
• available;
• rating.

Ora inserisci dati:

INSERT INTO products (name, category, price, available, rating)
VALUES
  ('Laptop', 'Electronics', 900.00, true, 5),
  ('Mouse', 'Electronics', 25.00, true, 4),
  ('Keyboard', 'Electronics', 70.00, false, 4),
  ('Desk Chair', 'Furniture', 150.00, true, 5),
  ('Notebook', 'Office', 5.00, true, 3),
  ('Pen', 'Office', 2.00, true, 3),
  ('Desk Lamp', 'Furniture', 45.00, true, 4),
  ('Monitor', 'Electronics', 250.00, true, 5),
  ('Bookshelf', 'Furniture', 120.00, false, 4),
  ('USB Cable', 'Electronics', 10.00, true, 2);

Controlla i dati:

SELECT * FROM products;

Ora abbiamo abbastanza dati per praticare.

Non milioni di righe.

Ma abbastanza perché PostgreSQL faccia un po’ di stretching.

Tipo yoga da database.

WHERE

WHERE filtra le righe.

Senza WHERE, PostgreSQL restituisce tutte le righe:

SELECT * FROM products;

Con WHERE, PostgreSQL restituisce solo le righe che rispettano una condizione:

SELECT * FROM products
WHERE category = 'Electronics';

Questo significa:

Dammi solo i prodotti dove la categoria è Electronics.

Dovresti vedere prodotti come:

• Laptop;
• Mouse;
• Keyboard;
• Monitor;
• USB Cable.

WHERE è una delle parti più importanti di SQL.

Ti aiuta a fare domande specifiche.

E le domande specifiche ottengono risposte utili.

Grande lezione di vita.

Anche grande lezione SQL.

Filtrare per Testo

Per filtrare per testo, usa virgolette singole.

Esempio:

SELECT * FROM products
WHERE category = 'Office';

Questo restituisce prodotti nella categoria Office.

Altro esempio:

SELECT * FROM products
WHERE name = 'Laptop';

Questo restituisce il prodotto chiamato Laptop.

Importante:

I valori testuali hanno bisogno di virgolette singole.

Corretto:

WHERE name = 'Laptop'

Sbagliato:

WHERE name = Laptop

Senza virgolette, PostgreSQL pensa che Laptop sia il nome di una colonna.

PostgreSQL non indovina.

È un database.

Non un lettore della mente.

Filtrare per Boolean

Le colonne boolean salvano true o false.

Esempio:

SELECT * FROM products
WHERE available = true;

Questo restituisce i prodotti disponibili.

Puoi anche trovare prodotti non disponibili:

SELECT * FROM products
WHERE available = false;

È utile per valori come:

• active;
• published;
• completed;
• available;
• verified.

Non salvare questo tipo di dati come testo:

yes
no
maybe

Usa BOOLEAN.

Le query saranno più pulite.

Il database protesterà meno.

Vincono tutti.

Quasi.

Operatori di Confronto

PostgreSQL ti permette di confrontare valori.

Operatori comuni:

=   uguale
!=  diverso
>   maggiore di
<   minore di
>=  maggiore o uguale
<=  minore o uguale

Nel testo normale di MDX, ricordati di scrivere operatori come <, <=, > e >= dentro backticks.

Altrimenti MDX può pensare che tu stia cercando di scrivere JSX.

E poi può esplodere emotivamente.

I blocchi SQL sono sicuri.

Il testo Markdown è il quartiere pericoloso.

Maggiore Di

Trova prodotti con prezzo maggiore di 100:

SELECT * FROM products
WHERE price > 100;

Questo restituisce prodotti come:

• Laptop;
• Desk Chair;
• Monitor;
• Bookshelf.

È utile quando vuoi trovare prodotti costosi.

O quando vuoi capire perché il portafoglio sta piangendo.

Minore Di

Trova prodotti con prezzo minore di 50:

SELECT * FROM products
WHERE price < 50;

Questo restituisce prodotti più economici come:

• Mouse;
• Notebook;
• Pen;
• Desk Lamp;
• USB Cable.

È utile per sconti, budget, filtri e momenti in cui la vita dice:

Forse non il laptop da 900 euro oggi.

Triste.

Ma finanziariamente responsabile.

Maggiore o Uguale

Trova prodotti con rating maggiore o uguale a 4:

SELECT * FROM products
WHERE rating >= 4;

Questo restituisce prodotti con rating 4 e 5.

L’operatore >= significa:

maggiore o uguale

Quindi 4 è incluso.

È utile quando vuoi prodotti buoni.

Non perfetti.

Ma abbastanza buoni.

Come molti progetti reali.

Minore o Uguale

Trova prodotti con prezzo minore o uguale a 25:

SELECT * FROM products
WHERE price <= 25;

Questo include prodotti con prezzo 25.

Quindi Mouse appare perché il suo prezzo è esattamente 25.

L’operatore <= significa:

minore o uguale

Simboli piccoli.

Significato grande.

I database sono pieni di queste cose.

Diverso

Trova prodotti che non sono nella categoria Electronics:

SELECT * FROM products
WHERE category != 'Electronics';

Questo restituisce prodotti di altre categorie.

Puoi anche usare:

SELECT * FROM products
WHERE category <> 'Electronics';

Sia != che <> possono significare diverso in PostgreSQL.

In questo corso useremo di solito != perché è facile da leggere.

PostgreSQL dà opzioni.

A volte troppe.

Ma queste vanno bene.

AND

AND combina condizioni.

Tutte le condizioni devono essere vere.

Esempio:

SELECT * FROM products
WHERE category = 'Electronics'
AND price < 100;

Questo significa:

Dammi i prodotti dove la categoria è Electronics e il prezzo è minore di 100.

Entrambe le condizioni devono essere vere.

Quindi potresti ottenere:

• Mouse;
• Keyboard;
• USB Cable.

Ma non Laptop.

Laptop è Electronics, sì.

Ma Laptop non costa meno di 100.

Laptop ha scelto uno stile di vita costoso.

Altri Esempi con AND

Trova prodotti Electronics disponibili:

SELECT * FROM products
WHERE category = 'Electronics'
AND available = true;

Trova prodotti Furniture con rating 5:

SELECT * FROM products
WHERE category = 'Furniture'
AND rating = 5;

Trova prodotti più economici di 100 e con rating almeno 4:

SELECT * FROM products
WHERE price < 100
AND rating >= 4;

AND è severo.

Ogni condizione deve passare.

Come una checklist molto seria.

Nessuna condizione lasciata indietro.

OR

OR combina condizioni dove almeno una condizione deve essere vera.

Esempio:

SELECT * FROM products
WHERE category = 'Office'
OR category = 'Furniture';

Questo significa:

Dammi i prodotti dove la categoria è Office oppure Furniture.

Un prodotto può rispettare una delle due condizioni.

Non deve rispettarle entrambe.

Perché qualcosa di solito non può essere sia Office che Furniture.

A meno che la tua sedia da ufficio non abbia una crisi d’identità.

Altri Esempi con OR

Trova prodotti molto economici o con rating alto:

SELECT * FROM products
WHERE price < 10
OR rating = 5;

Questo restituisce prodotti che rispettano almeno una condizione.

Quindi un prodotto appare se:

• il prezzo è minore di 10;
• oppure il rating è 5;
• oppure entrambi.

OR è più flessibile di AND.

A volte troppo flessibile.

Usalo con attenzione.

AND e OR Insieme

Puoi combinare AND e OR.

Ma devi stare attento.

Esempio:

SELECT * FROM products
WHERE category = 'Electronics'
AND price < 100
OR rating = 5;

Questo potrebbe non significare quello che pensi.

PostgreSQL valuta AND prima di OR.

Quindi la query viene interpretata così:

SELECT * FROM products
WHERE (category = 'Electronics' AND price < 100)
OR rating = 5;

Significa:

Electronics più economici di 100, oppure qualsiasi cosa con rating 5.

Se vuoi un significato diverso, usa parentesi.

Le parentesi rendono chiara la tua intenzione.

Il tuo io futuro ti ringrazierà.

Forse con caffè.

Parentesi

Usa parentesi per raggruppare condizioni.

Esempio:

SELECT * FROM products
WHERE category = 'Electronics'
AND (price < 100 OR rating = 5);

Questo significa:

Dammi prodotti Electronics dove il prezzo è minore di 100 oppure il rating è 5.

Ora il prodotto deve essere Electronics.

Poi deve anche rispettare una delle condizioni dentro le parentesi.

Le parentesi non sono decorazione.

Sono recinti logici.

Usale quando mescoli AND e OR.

Altrimenti la query può diventare un animale selvatico.

LIKE

LIKE cerca pattern di testo.

Esempio:

SELECT * FROM products
WHERE name LIKE 'Desk%';

Questo trova nomi che iniziano con Desk.

Il simbolo % significa:

qui può esserci qualsiasi cosa

Quindi Desk% trova:

• Desk Chair;
• Desk Lamp.

Altro esempio:

SELECT * FROM products
WHERE name LIKE '%book%';

Questo trova nomi che contengono book.

Può trovare:

• Notebook;
• Bookshelf.

Ma c’è una cosa importante.

LIKE è spesso case-sensitive.

Quindi book potrebbe non trovare Book.

PostgreSQL ha un’altra opzione.

Si chiama ILIKE.

ILIKE

ILIKE è come LIKE, ma case-insensitive.

Esempio:

SELECT * FROM products
WHERE name ILIKE '%book%';

Questo può trovare:

• Notebook;
• Bookshelf;
• BOOKSHELF;
• notebook.

Molto utile.

Soprattutto quando gli utenti scrivono come esseri umani.

Gli esseri umani non sono coerenti.

A volte scrivono laptop.

A volte Laptop.

A volte LAPTOP.

A volte lapotp.

Quest’ultimo non è colpa di PostgreSQL.

ORDER BY

ORDER BY ordina i risultati.

Ordina per prezzo dal più basso al più alto:

SELECT * FROM products
ORDER BY price;

Uguale a:

SELECT * FROM products
ORDER BY price ASC;

Ordina per prezzo dal più alto al più basso:

SELECT * FROM products
ORDER BY price DESC;

Ordina per nome alfabeticamente:

SELECT * FROM products
ORDER BY name;

L’ordinamento rende i dati più facili da leggere.

Senza ordinamento, le righe arrivano nell’ordine che PostgreSQL decide di restituire.

Non fidarti dell’ordine naturale.

L’ordine naturale non è un contratto.

È un umore.

ORDER BY con WHERE

Puoi filtrare e ordinare nello stesso momento.

Esempio:

SELECT * FROM products
WHERE category = 'Electronics'
ORDER BY price DESC;

Questo significa:

Dammi prodotti Electronics, ordinati dal più costoso al più economico.

Altro esempio:

SELECT name, price FROM products
WHERE available = true
ORDER BY price ASC;

Questo significa:

Dammi prodotti disponibili, ordinati per prezzo dal basso all’alto.

È molto comune nelle applicazioni reali.

Filtra prima.

Ordina i risultati.

Mostra all’utente.

L’utente pensa che il sito sia intelligente.

In realtà è SQL che fa il lavoro pesante.

In silenzio.

Come un magazziniere stanco.

LIMIT

LIMIT controlla quante righe vengono restituite.

Esempio:

SELECT * FROM products
LIMIT 3;

Questo restituisce solo tre righe.

Trova i tre prodotti più costosi:

SELECT * FROM products
ORDER BY price DESC
LIMIT 3;

È molto utile per:

• top prodotti;
• ultimi post;
• prima pagina di risultati;
• piccole anteprime;
• dashboard.

Senza LIMIT, una query può restituire troppi dati.

E troppi dati sono come troppa pasta.

All’inizio emozionante.

Poi pericoloso.

OFFSET

OFFSET salta righe.

Esempio:

SELECT * FROM products
ORDER BY id
LIMIT 3 OFFSET 3;

Questo significa:

Salta le prime 3 righe, poi restituisci 3 righe.

È utile per la paginazione.

Pagina 1:

SELECT * FROM products
ORDER BY id
LIMIT 3 OFFSET 0;

Pagina 2:

SELECT * FROM products
ORDER BY id
LIMIT 3 OFFSET 3;

Pagina 3:

SELECT * FROM products
ORDER BY id
LIMIT 3 OFFSET 6;

La paginazione è il modo in cui i siti mostrano risultati a pagine.

Non tutto insieme.

Perché nessuno vuole caricare 5000 prodotti solo per trovare un mouse.

Tranne forse il database.

Il database non si lamenta.

Ma gli utenti sì.

Combinare Tutto

Ora combiniamo filtraggio, ordinamento e limite.

Esempio:

SELECT name, category, price, rating
FROM products
WHERE available = true
AND price >= 10
ORDER BY rating DESC, price ASC
LIMIT 5;

Questo significa:

Dammi prodotti disponibili con prezzo almeno 10.
Mostra solo name, category, price e rating.
Ordina per rating dal più alto al più basso.
Se i rating sono uguali, ordina per prezzo dal più basso al più alto.
Restituisci solo 5 righe.

Questa è una query reale.

Non teoria giocattolo.

È il tipo di cosa che le applicazioni reali fanno continuamente.

Cerca.

Filtra.

Ordina.

Limita.

Mostra.

Ripeti.

Lo sviluppo web spesso è questo con bottoni più belli.

Errori Comuni

Dimenticare le Virgolette per il Testo

Sbagliato:

SELECT * FROM products
WHERE category = Electronics;

Corretto:

SELECT * FROM products
WHERE category = 'Electronics';

I valori testuali hanno bisogno di virgolette singole.

I nomi delle colonne no.

PostgreSQL non indovinerà.

Di nuovo.

Ha dei limiti.

Buon per PostgreSQL.

Confondere AND e OR

Questo:

WHERE category = 'Office'
OR category = 'Furniture'

significa una delle due.

Questo:

WHERE category = 'Office'
AND category = 'Furniture'

di solito non restituisce niente.

Perché un prodotto di solito non può avere entrambe le categorie nello stesso momento.

AND significa tutte le condizioni.

OR significa almeno una condizione.

Parole piccole.

Conseguenze grandi.

Come “sì” e “no” nei contratti.

Dimenticare le Parentesi

Pericoloso:

WHERE category = 'Electronics'
AND price < 100
OR rating = 5

Meglio:

WHERE category = 'Electronics'
AND (price < 100 OR rating = 5)

Le parentesi rendono chiara la logica.

Logica chiara significa meno sorprese.

Meno sorprese significa meno urla al terminale.

Sano.

Usare LIKE Quando Serve ILIKE

Questo può perdere risultati per via delle maiuscole/minuscole:

WHERE name LIKE '%book%'

Questo spesso è meglio per la ricerca utente:

WHERE name ILIKE '%book%'

Agli utenti non interessa la tua case sensitivity.

Vogliono risultati.

PostgreSQL può aiutare.

Lascialo fare.

Pratica

Scrivi query per la tabella products.

Trova tutti i prodotti Electronics:

SELECT * FROM products
WHERE category = 'Electronics';

Trova prodotti più economici di 50:

SELECT * FROM products
WHERE price < 50;

Trova prodotti disponibili con rating almeno 4:

SELECT * FROM products
WHERE available = true
AND rating >= 4;

Trova prodotti con nomi che contengono desk:

SELECT * FROM products
WHERE name ILIKE '%desk%';

Trova i tre prodotti più costosi:

SELECT * FROM products
ORDER BY price DESC
LIMIT 3;

Esegui ogni query.

Cambia valori.

Rompi cose.

Sistema cose.

È così che SQL diventa normale.

Mini Challenge

Crea una tabella chiamata students.

Deve avere:

• id;
• name;
• city;
• age;
• active;
• score.

Inserisci almeno otto studenti.

Poi scrivi query per:

• mostrare tutti gli studenti;
• mostrare studenti di una città;
• mostrare studenti con più di 18 anni;
• mostrare studenti attivi;
• mostrare studenti con score maggiore o uguale a 80;
• mostrare studenti di una città e attivi;
• mostrare studenti il cui nome contiene an;
• ordinare studenti per score dal più alto al più basso;
• mostrare solo i migliori tre studenti per score.

Questa challenge è importante.

Perché filtering e sorting sono ovunque.

Se sai scrivere queste query, non stai più solo salvando dati.

Stai controllando dati.

Con attenzione.

Si spera.

Riepilogo

Oggi hai imparato:

• WHERE filtra righe;
• = controlla uguaglianza;
• != controlla diverso;
• > controlla maggiore di;
• < controlla minore di;
• >= controlla maggiore o uguale;
• <= controlla minore o uguale;
• AND richiede che tutte le condizioni siano vere;
• OR richiede che almeno una condizione sia vera;
• le parentesi rendono chiara la logica complessa;
• LIKE cerca pattern di testo;
• ILIKE cerca pattern di testo senza sensibilità a maiuscole/minuscole;
• ORDER BY ordina risultati;
• ASC ordina in modo ascendente;
• DESC ordina in modo discendente;
• LIMIT limita le righe restituite;
• OFFSET salta righe.

Questo è un grande passo.

Ora puoi fare domande precise a PostgreSQL.

Non solo:

SELECT * FROM products;

Ma:

SELECT name, price
FROM products
WHERE available = true
AND price < 100
ORDER BY price ASC
LIMIT 5;

Questo è vero potere database.

Query piccola.

Grande utilità.

Molto PostgreSQL.

Prossima Lezione

Nella prossima lezione impareremo le relazioni con primary keys e foreign keys.

Qui le tabelle iniziano a parlarsi.

Studenti e corsi.

Utenti e ordini.

Prodotti e categorie.

Una tabella è utile.

Tabelle collegate sono il punto in cui i database relazionali diventano potenti.

E leggermente più drammatici.