Corso MariaDB / Definire query riutilizzabili con VIEW

Una VIEW memorizza una definizione SELECT come oggetto del database con un nome, interrogabile da altre istruzioni. Una vista ordinaria non conserva separatamente una copia delle righe risultanti: occorre verificarne contesto dei privilegi, dipendenze e possibilità di aggiornamento, senza trattarla come una tabella di risultati in cache.

Distinguere una definizione persistente da una CTE

Il calcolo delle merci degli ordini pagati, usato da più rapporti, può diventare una vista. Una CTE dà un nome a un risultato intermedio all'interno di un'istruzione; una vista creata rimane disponibile anche per istruzioni successive. Scegliere le colonne esposte pensando alle query che dipenderanno da esse. La guida a WITH e alle espressioni di tabella comuni descrive invece i nomi necessari soltanto all'interno di una singola istruzione.

Oggetto Che cosa memorizza o denomina Durata
VIEW ordinaria Definizione SELECT Fino a DROP VIEW
CTE Nome di una query intermedia Istruzione SQL che la contiene
Tabella Righe di dati Secondo le regole di conservazione ed eliminazione

La definizione salvata facilita il riuso, ma il calcolo continua a leggere le fonti secondo l'esecuzione della query e le regole della transazione. La documentazione MariaDB di CREATE VIEW descrive sintassi, privilegi di creazione e contesto di sicurezza.

Preparare tabelle di base e un nome distinto per la vista

Collegarsi al server di esercitazione come course_user e selezionare shopcourse. La guida al client e ai file SQL UTF-8 spiega come salvare il codice ed eseguirlo con SOURCE C:/mariadb-lab/lesson.sql;. Sostituire il percorso con quello reale ed eseguire SQL nel client MariaDB.

Prima di creare c34_customers, c34_products, c34_orders e c34_items, controllare SELECT VERSION(), DATABASE(); e SHOW TABLES;. Se gli oggetti esistono già, esaminarne definizioni e righe. Usare soltanto tabelle di esercitazione eliminabili e conservare quelle applicative. L'account necessita di CREATE VIEW e dei privilegi richiesti sulle fonti, senza richiedere privilegi amministrativi globali.

USE shopcourse;
CREATE TABLE c34_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c34_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c34_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c34_order_customer FOREIGN KEY (customer_id)
  REFERENCES c34_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c34_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c34_item_order FOREIGN KEY (order_id)
  REFERENCES c34_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c34_item_product FOREIGN KEY (product_id)
  REFERENCES c34_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c34_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c34_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c34_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c34_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

Il codice conserva nomi e città coreani della fonte. I prodotti 1–4 sono quaderno, penna, tazza e lampada. I clienti 1 e 3 appartengono a Seoul, il cliente 2 a Busan e il cliente 4 a Daejeon. Mantenere i testi in UTF-8 e usare gli ID per confrontare i risultati.

Controllare anche SHOW FULL TABLES per accertare che c34_paid_order_totals non esista già. L'esercizio crea un oggetto nuovo: non usare CREATE OR REPLACE per sovrascrivere una definizione non esaminata. Prima di cambiare una vista esistente, salvarne SHOW CREATE VIEW e individuare le query che la usano. Le quattro tabelle di base rendono l'esempio autonomo.

Creare e interrogare la vista con INVOKER

CREATE SQL SECURITY INVOKER VIEW c34_paid_order_totals AS
SELECT o.order_id,o.customer_id,o.ordered_at,
 SUM(i.quantity*i.unit_price) AS goods_total
FROM c34_orders o JOIN c34_items i ON i.order_id=o.order_id
WHERE o.status='paid' GROUP BY o.order_id,o.customer_id,o.ordered_at;
SELECT order_id,goods_total FROM c34_paid_order_totals ORDER BY order_id;
SHOW CREATE VIEW c34_paid_order_totals;

I totali risultanti sono 101:11500.00, 102:8000.00 e 103:19500.00. Specificare ORDER BY quando si legge la vista, senza dipendere da un ordine interno. La definizione comprende soltanto ordini pagati con righe articolo, perché usa un INNER JOIN.

SQL SECURITY INVOKER verifica l'accesso alle fonti usando i privilegi dell'account che esegue la query. Il lettore necessita dell'accesso alla vista e dei privilegi richiesti sulle tabelle di origine. Concedere SELECT soltanto sul nome della vista non consente di superare i controlli sulle fonti in modalità INVOKER. Se l'accesso è negato, esaminare SHOW CREATE VIEW, account chiamante e privilegi sulle fonti.

Osservare modifiche e annullamento sulla stessa connessione

START TRANSACTION;
UPDATE c34_items SET quantity=3 WHERE order_id=101 AND product_id=1;
SELECT goods_total FROM c34_paid_order_totals WHERE order_id=101;
ROLLBACK;
SELECT goods_total FROM c34_paid_order_totals WHERE order_id=101;

La quantità del prodotto 1 nell'ordine 101 passa da due a tre e il totale della vista diventa 15000.00 nella transazione. ROLLBACK lo riporta a 11500.00. Il calcolo legge le modifiche delle tabelle di base; non aggiorna una copia storica separata nella vista.

Eseguire l'intero blocco sulla stessa connessione affinché modifica e ROLLBACK appartengano alla medesima transazione. La lettura da un'altra connessione dipende da isolamento e stato del commit. Non presumere che un valore visibile allo scrittore sia già visibile ad altri lettori. Al termine verificare che quantity sia nuovamente due.

Valutare sicurezza, aggiornabilità e prestazioni

Questa vista usa GROUP BY e SUM e non consente di aggiornare direttamente goods_total. Alcune viste semplici sono aggiornabili, ma la possibilità non vale per tutte le definizioni. Mantenere distinto il percorso di modifica dei dati di base dalla query del rapporto aggregato.

Una vista con SQL SECURITY DEFINER verifica i privilegi nel contesto dell'account definente. Ripristinare una definizione in un ambiente privo di quell'account può causare errori. Esaminare l'attributo durante backup e ripristino e valutare i privilegi necessari secondo l'uso effettivo.

Creare una vista non memorizza automaticamente i risultati in cache e non rende automaticamente più veloce la query. Leggibilità e riuso sono vantaggi distinti dalle prestazioni: se necessario, esaminare il piano di esecuzione. Modificare una tabella sorgente o una colonna referenziata può rendere non valida la definizione anche quando il nome della vista esiste ancora.

Modificare con cautela ed eliminare prima la vista

Rinominare o eliminare colonne può interrompere le query che dipendono dalla vista. Scegliere esplicitamente le colonne e individuare chi le usa prima di modificare la definizione. Nella pulizia dell'esercizio eliminare la vista prima delle tabelle sorgenti. DROP VIEW rimuove soltanto la definizione, lasciando le righe delle tabelle di base.

DROP VIEW shopcourse.c34_paid_order_totals;

Dopo DROP VIEW, verificare che tabelle e righe di base esistano ancora prima di proseguire con la pulizia. Prima di ripetere l'esercizio, controllare database selezionato, SHOW CREATE TABLE e dati da conservare, effettuando un backup se necessario. Eliminare soltanto gli oggetti c34_ di questo articolo, partendo dalle tabelle figlie. DROP TABLE elimina struttura e dati e non viene annullato da un normale ROLLBACK: separare la pulizia dalle prove sulle transazioni.

DROP TABLE shopcourse.c34_items;
DROP TABLE shopcourse.c34_orders;
DROP TABLE shopcourse.c34_products;
DROP TABLE shopcourse.c34_customers;