---
name: sql-mysql
description: Crea, spiega e revisiona query MySQL 8 per studenti, inclusi schemi da zero o estensioni di file .sql esistenti, CREATE TABLE, relazioni, SELECT, INSERT, UPDATE e DELETE. Usa questa skill quando occorre progettare o interrogare un database MySQL rispettando convenzioni SQL coerenti, sicure e leggibili.
---

# SQL MySQL 8

Produci SQL MySQL 8 chiaro, eseguibile e accompagnato da una spiegazione breve in italiano semplice. Applica sempre le convenzioni sotto, salvo richiesta esplicita incompatibile dell'utente.

## Analizza prima il contesto

1. Cerca un file `.sql` pertinente tra i file disponibili nel progetto o nel contesto dell'agente. Se l'utente indica un file, usa quello.
2. Leggi lo schema prima di proporre query: identifica tabelle, colonne, PK, FK, indici, vincoli e convenzioni di naming.
3. Se lo schema esiste, preservane nomi e struttura e proponi query compatibili. Non rinominare o migrare automaticamente; chiedi conferma se è necessario cambiare strutture esistenti.
4. Se non esiste uno schema, raccogli le entità, i dati, le relazioni e le regole di business essenziali prima di progettare le tabelle.

## Regole generali

- Usa MySQL 8.
- Racchiudi sempre ogni identificatore SQL tra backtick: database, tabelle, colonne, alias, indici e riferimenti nelle chiavi esterne.
- Usa nomi inglesi in `snake_case`. Usa nomi plurali per le tabelle e nomi descrittivi per le colonne.
- Indenta ogni livello con 4 spazi.
- Non scrivere `NOT NULL` esplicitamente.
- Preferisci, quando appropriato: `INT(11)`, `DECIMAL(8,2)`, `VARCHAR(255)`, `TEXT`, `DATE`, `DATETIME`, `TIME`. Usa `POINT` solo per coordinate geometriche.
- Usa `TEXT` per contenuti che saranno consultati o interrogati raramente; per valori spesso filtrati, uniti o ordinati scegli un tipo più mirato.
- Non proporre `ENUM`: suggerisci un vincolo `CHECK`. Usa `ENUM` soltanto se l'utente lo richiede esplicitamente.
- Usa `UNIQUE` nelle tabelle normali solo quando il dominio richiede che un valore non possa ripetersi.
- Spiega brevemente i rischi delle operazioni che eliminano dati o modificano molte righe.

## Convenzioni per le tabelle

### Tabelle normali

- Inizia con `` `id` INT(11) AUTO_INCREMENT PRIMARY KEY ``.
- Nomina una chiave esterna `` `id_<nome_tabella_singolare>` ``, per esempio `` `id_category` ``.
- Per un booleano usa un nome con prefisso `is_` e il formato `` `is_deleted` INT(1) CHECK (`is_deleted` IN (0, 1)) DEFAULT 0 ``.
- Aggiungi sempre, eccetto nelle tabelle pivot, questi campi:

```sql
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME ON UPDATE CURRENT_TIMESTAMP DEFAULT NULL
```

### Tabelle pivot

- Riconosci una pivot quando serve solo a collegare due tabelle in una relazione molti-a-molti.
- Se contiene soltanto le due FK, non aggiungere un campo `` `id` `` e non aggiungere `UNIQUE`.
- Usa le due FK come chiave primaria composta.
- Aggiungi un `` `id` `` solo se la pivot ha una propria identità (per esempio viene referenziata da altre tabelle) o se è realmente necessario per il dominio.
- Non aggiungere automaticamente `` `created_at` `` e `` `updated_at` `` alle pivot.

## Creazione di tabelle

Genera il blocco seguente solo quando l'utente chiede di creare una o più tabelle. Avvisa che `DROP TABLE IF EXISTS` elimina la tabella esistente e i suoi dati.

- Prima riga: `SET FOREIGN_KEY_CHECKS = 0;`.
- Per ogni tabella, usa prima `DROP TABLE IF EXISTS` e poi `CREATE TABLE IF NOT EXISTS`.
- Quando sono presenti relazioni, crea prima le tabelle referenziate e poi quelle che contengono le FK; esegui i `DROP` nell'ordine inverso.
- Ultima riga: `SET FOREIGN_KEY_CHECKS = 1;`.

Esempio di tabella normale:

```sql
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `products`;

CREATE TABLE IF NOT EXISTS `products` (
    `id` INT(11) AUTO_INCREMENT PRIMARY KEY,
    `id_category` INT(11),
    `title` VARCHAR(255),
    `price` DECIMAL(8,2),
    `description` TEXT,
    `discount_until` DATE,
    `is_deleted` INT(1) CHECK (`is_deleted` IN (0, 1)) DEFAULT 0,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME ON UPDATE CURRENT_TIMESTAMP DEFAULT NULL,
    FOREIGN KEY (`id_category`) REFERENCES `categories` (`id`)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

SET FOREIGN_KEY_CHECKS = 1;
```

Esempio di tabella pivot:

```sql
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `product_tags`;

CREATE TABLE IF NOT EXISTS `product_tags` (
    `id_product` INT(11),
    `id_tag` INT(11),
    PRIMARY KEY (`id_product`, `id_tag`),
    FOREIGN KEY (`id_product`) REFERENCES `products` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (`id_tag`) REFERENCES `tags` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

SET FOREIGN_KEY_CHECKS = 1;
```

## Query CRUD

Non aggiungere `DROP TABLE`, `CREATE TABLE` o `FOREIGN_KEY_CHECKS` alle query CRUD, salvo che l'utente richieda anche la creazione delle tabelle.

### SELECT

- Usa `SELECT *`, salvo richiesta esplicita.
- Usa `WHERE` per filtrare, `ORDER BY` per ordinare e `LIMIT` per limitare i risultati quando utile.
- Usa `JOIN` sulle FK e senza alias.

```sql
SELECT * FROM `products`
INNER JOIN `categories` ON `categories`.`id` = `products`.`id_category`
WHERE `products`.`is_deleted` = 0
ORDER BY `products`.`title` ASC
LIMIT 20;
```

### INSERT

- Elenca sempre le colonne e mantieni valori nello stesso ordine.
- Non inserire `` `id` ``, timestamp o campi con un default, a meno che l'utente lo richieda.

```sql
INSERT INTO `products` (`id_category`, `title`, `price`, `description`) VALUES (1, 'Example product', 19.90, 'Short product description');
```

### UPDATE

- Prima mostra una `SELECT` con lo stesso `WHERE` per verificare le righe interessate.
- Usa sempre `WHERE` negli esempi di aggiornamento; non generare aggiornamenti globali senza conferma esplicita.

```sql
SELECT *
FROM `products`
WHERE `id` = 1;

UPDATE `products`
SET `price` = 17.90
WHERE `id` = 1;
```

### DELETE

- Prima mostra una `SELECT` con lo stesso `WHERE` per verificare le righe interessate.
- Usa sempre `WHERE` negli esempi e ricorda di controllare le azioni `ON DELETE` delle FK.
- Se il modello prevede una cancellazione logica, suggerisci l'aggiornamento di un campo `is_deleted` invece di `DELETE`.

```sql
SELECT *
FROM `products`
WHERE `id` = 1;

DELETE FROM `products`
WHERE `id` = 1;
```

## Controllo finale

Prima di consegnare SQL, verifica:

- identificatori sempre tra backtick;
- indentazione di 4 spazi;
- nomi inglesi in `snake_case`;
- nessun `NOT NULL` esplicito;
- nessun `ENUM` se non richiesto;
- booleani `is_` con `INT(1)`, `CHECK` e `DEFAULT 0`;
- timestamp nelle sole tabelle non pivot;
- pivot essenziali con PK composta, senza `` `id` `` e senza `UNIQUE`;
- `WHERE` negli esempi di `UPDATE` e `DELETE`;
- blocco `FOREIGN_KEY_CHECKS` e sequenza `DROP`/`CREATE` solo nelle richieste di creazione tabella.



## Esempio di query

-- ============================================
-- Schema MySQL 8 per la gestione di Prodotti e Categorie
-- ============================================

SET FOREIGN_KEY_CHECKS = 0;

-- Tabella delle categorie
DROP TABLE IF EXISTS `categories`;

CREATE TABLE IF NOT EXISTS `categories` (
    `id` INT(11) AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255),
    `description` TEXT,
    `is_deleted` INT(1) CHECK (`is_deleted` IN (0, 1)) DEFAULT 0,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME ON UPDATE CURRENT_TIMESTAMP DEFAULT NULL
);

-- Tabella dei prodotti
DROP TABLE IF EXISTS `products`;

CREATE TABLE IF NOT EXISTS `products` (
    `id` INT(11) AUTO_INCREMENT PRIMARY KEY,
    `id_category` INT(11),
    `name` VARCHAR(255),
    `price` DECIMAL(8,2),
    `description` TEXT,
    `stock_quantity` INT(11) DEFAULT 0,
    `is_deleted` INT(1) CHECK (`is_deleted` IN (0, 1)) DEFAULT 0,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME ON UPDATE CURRENT_TIMESTAMP DEFAULT NULL,
    FOREIGN KEY (`id_category`) REFERENCES `categories` (`id`)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================
-- Esempi di operazioni CRUD
-- ============================================

-- 1. INSERIMENTO DATI DI ESEMPIO

-- Inserimento categorie
INSERT INTO `categories` (`name`, `description`) VALUES ('Elettronica', 'Prodotti elettronici come smartphone, laptop, ecc.'),
INSERT INTO `categories` (`name`, `description`) VALUES ('Abbigliamento', 'Capi di abbigliamento per uomo, donna e bambino.'),
INSERT INTO `categories` (`name`, `description`) VALUES ('Alimentari', 'Prodotti alimentari e bevande.');

-- Inserimento prodotti
INSERT INTO `products` (`id_category`, `name`, `price`, `description`, `stock_quantity`) VALUES (1, 'Smartphone Samsung Galaxy S23', 899.99, 'Smartphone di ultima generazione con 256GB di memoria.', 50),
INSERT INTO `products` (`id_category`, `name`, `price`, `description`, `stock_quantity`) VALUES (1, 'Laptop Lenovo ThinkPad', 1299.99, 'Laptop professionale con processore Intel i7.', 30),
INSERT INTO `products` (`id_category`, `name`, `price`, `description`, `stock_quantity`) VALUES (2, 'Maglietta Uomo', 24.99, 'Maglietta in cotone 100% urbano.', 100),
INSERT INTO `products` (`id_category`, `name`, `price`, `description`, `stock_quantity`) VALUES (3, 'Pasta Barilla', 1.99, 'Pasta di semola di grano duro, 500g.', 200);

-- ============================================
-- 2. QUERY DI SELECT
-- ============================================

-- Selezione di tutti i prodotti con il nome della categoria
SELECT * FROM `products`;

-- Selezione dei prodotti con prezzo maggiore di 500 euro
SELECT * FROM `products` WHERE `products`.`price` > 500.00;

-- Selezione dei prodotti in una specifica categoria (es. Elettronica)
SELECT * FROM `products`
INNER JOIN `categories` ON `categories`.`id` = `products`.`id_category`
WHERE `categories`.`name` = 'Elettronica';

-- ============================================
-- 3. AGGIORNAMENTO DATI
-- ============================================

-- Aggiornamento del prezzo di un prodotto
UPDATE `products` SET `price` = 849.99 WHERE `id` = 1;

-- Aggiornamento della quantita di magazzino
UPDATE `products` SET `stock_quantity` = `stock_quantity` - 5 WHERE `id` = 2;

-- ============================================
-- 4. CANCELLAZIONE DATI
-- ============================================

-- Cancellazione logica di un prodotto (consigliato)
DELETE FROM `products` WHERE `id` = 4;