Corso MariaDB / Ordinare e impaginare i risultati con ORDER BY e LIMIT
ORDER BY definisce l'ordine del risultato e LIMIT limita il numero di righe restituite e la posizione iniziale. Quando i valori di ordinamento coincidono, aggiungere una chiave secondaria univoca per determinare con precisione classifiche e confini delle pagine su dati che non cambiano.
Specificare sia l'ordine sia il numero di risultati
I tre prodotti più costosi richiedono un ordinamento decrescente per prezzo e un limite di tre righe. LIMIT 3 da solo non stabilisce quali prodotti debbano comparire per primi. L'ordinamento descrive il risultato e non riordina fisicamente le righe memorizzate. La guida a WHERE e alle condizioni spiega come scegliere le righe prima di ordinarle.
| Espressione | Significato | Esempio di utilizzo |
|---|---|---|
| ASC | Crescente; direzione predefinita | ID minore per primo a parità di prezzo |
| DESC | Decrescente | Prezzo maggiore per primo |
| LIMIT 3 | Al massimo tre righe | Elenco dei primi prodotti |
| LIMIT 2 OFFSET 2 | Saltare due righe e prenderne al massimo due | Seconda pagina di due righe |
Ogni espressione di ordinamento ha la propria direzione. In ORDER BY price DESC, product_id ASC, DESC non si applica a entrambe le colonne. Una stringa che sembra numerica segue comunque il proprio tipo e le proprie regole di confronto.
I riferimenti LIMIT e ORDER BY descrivono la sintassi usata nell'esercizio.
Preparare righe con prezzi intenzionalmente uguali
Collegarsi al server di studio come course_user e selezionare shopcourse. La guida al terminale e ai file SQL UTF-8 spiega come salvare il codice ed eseguirlo con SOURCE C:/mariadb-lab/lesson.sql, sostituendo il percorso con quello reale. Eseguire SQL nel client MariaDB, non al prompt del sistema operativo.
Controllare SELECT VERSION(), DATABASE() e SHOW TABLES prima di creare products_17_lab. Il nome appartiene soltanto a questo esercizio. Se esiste già, verificarne definizione e righe; eliminarlo solo dopo aver confermato che sia un oggetto di esercitazione eliminabile. Non sostituire tabelle applicative esistenti.
USE shopcourse;
CREATE TABLE products_17_lab (
product_id INT NOT NULL AUTO_INCREMENT,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(30) NOT NULL,
price DECIMAL(10,2) NOT NULL,
stock INT NOT NULL DEFAULT 0,
note VARCHAR(100) NULL DEFAULT NULL,
PRIMARY KEY (product_id),
CONSTRAINT ck_p17_price CHECK (price >= 0),
CONSTRAINT ck_p17_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO products_17_lab
(product_id, product_name, category, price, stock, note)
VALUES (1, '노트', '문구', 3500.00, 20, NULL),
(2, '볼펜', '문구', 1500.00, 0, ''),
(3, '머그컵', '생활', 8000.00, 12, '선물 포장'),
(4, '스탠드', '생활', 25000.00, 5, NULL),
(5, '파일', '문구', 1500.00, 50, ''),
(6, '케이블', '전자', 8000.00, 0, '호환 확인');
SQL conserva le stringhe coreane originali per verificare UTF-8 in modo coerente. Gli ID da 1 a 6 rappresentano quaderno, penna, tazza, lampada da tavolo, cartellina e cavo. Le categorie significano cancelleria, articoli per la casa ed elettronica. Inserire le stringhe come mostrate, confrontare i risultati tramite gli ID e salvare il file in UTF-8.
Gli ID 3 e 6 costano entrambi 8000, mentre gli ID 2 e 5 costano entrambi 1500. Queste parità rendono osservabile l'ordinamento secondario. product_id identifica univocamente ogni riga ed è quindi adatto come criterio finale per risolvere le parità in questi dati.
Aggiungere un criterio univoco per risolvere le parità di prezzo
SELECT product_id, product_name, price FROM products_17_lab
ORDER BY price DESC, product_id ASC LIMIT 3;
| Posizione | ID prodotto | Significato | Prezzo |
|---|---|---|---|
| 1 | 4 | Lampada da tavolo | 25000.00 |
| 2 | 3 | Tazza | 8000.00 |
| 3 | 6 | Cavo | 8000.00 |
MariaDB confronta prima il prezzo e poi product_id quando i prezzi coincidono. Cambiare soltanto la direzione dell'ID in DESC per mettere 6 prima di 3, quindi ripristinare ASC. Un insieme senza parità oggi può averne in futuro: omettere il criterio finale perché i risultati attuali sembrano stabili non definisce una regola duratura.
Comprendere OFFSET e i confini delle pagine che cambiano
SELECT product_id, price FROM products_17_lab
ORDER BY price DESC, product_id ASC LIMIT 2 OFFSET 0;
SELECT product_id, price FROM products_17_lab
ORDER BY price DESC, product_id ASC LIMIT 2 OFFSET 2;
La prima pagina restituisce gli ID 4 e 3; la seconda restituisce 6 e 1. OFFSET conta le righe saltate, non le pagine. Con due righe per pagina, la terza pagina inizia all'offset quattro. LIMIT 2, 2 esprime anche la seconda query, ma la forma LIMIT quantità OFFSET posizione chiarisce il ruolo dei due numeri.
Il criterio univoco non congela i dati tra richieste. Inserimenti, eliminazioni o variazioni di prezzo eseguiti nel frattempo possono spostare i confini delle pagine. Offset elevati possono anche richiedere lavoro sulle righe precedenti: restituire poche righe non garantisce che ordinamento e filtro abbiano esaminato soltanto quel numero di righe.
Usare gli ultimi valori di ordinamento e scegliere la posizione di NULL
Se la pagina precedente termina al prezzo 8000 e all'ID 3, le righe successive hanno un prezzo inferiore oppure lo stesso prezzo e un ID maggiore. La condizione del cursore deve corrispondere a entrambe le direzioni di ordinamento:
SELECT product_id, price FROM products_17_lab
WHERE price < 8000 OR (price = 8000 AND product_id > 3)
ORDER BY price DESC, product_id ASC LIMIT 2;
Gli ID 6 e 1 coincidono con l'esempio OFFSET. Questa tecnica è la paginazione basata su chiave, o keyset pagination: conservare gli ultimi valori di ordinamento per la richiesta successiva. Ha requisiti diversi dal salto diretto a un numero di pagina arbitrario. Un elenco completamente fisso durante scritture concorrenti richiede una politica di coerenza aggiuntiva.
SELECT product_id, note FROM products_17_lab
ORDER BY note IS NULL ASC, note ASC, product_id ASC;
In un normale ordinamento crescente, NULL è considerato inferiore ai valori non NULL. L'espressione booleana esplicita dell'esempio colloca prima le note non NULL, poi ordina i valori delle note e gli ID. Gli ID 2 e 5, con note vuote, precedono le note contenenti testo; le note NULL agli ID 1 e 4 sono alla fine. La collazione determina l'ordine dettagliato del testo: osservare la politica esplicita per NULL e il criterio finale univoco.
Diagnosticare l'ordine inatteso e limitare le opzioni dell'utente
Rimuovere temporaneamente LIMIT e mostrare la chiave primaria insieme a tutte le espressioni di ordinamento. Controllare direzioni, alias, tipi e criterio univoco. L'ordine apparente di un indice non equivale a un ORDER BY implicito. Se l'applicazione permette di scegliere l'ordinamento, associare le scelte soltanto a colonne e direzioni consentite. I parametri per i valori non servono generalmente a sostituire nomi arbitrari di identificatori. Validare le dimensioni delle pagine come interi entro limiti appropriati.
Prima di ripetere, controllare database selezionato, definizione effettiva della tabella e righe memorizzate, salvando ciò che va conservato. Eliminare solo products_17_lab di questo articolo. DROP TABLE rimuove definizione e righe e non viene annullato da un normale ROLLBACK: tenere la pulizia separata dagli esperimenti sulle transazioni.
DROP TABLE shopcourse.products_17_lab;




