SQL: creare le tabelle (DDL)

SQL (Structured Query Language, si legge "esse-cu-elle" o "sìquel") è il linguaggio con cui si parla a un database relazionale. È lo stesso in MySQL, MariaDB, PostgreSQL, SQL Server, con piccole differenze. I suoi comandi si dividono in gruppi:

In questa lezione trasformiamo lo schema logico della biblioteca in tabelle vere, scrivendo il DDL a mano.

Dove si scrive l'SQL

In phpMyAdmin apri la scheda SQL, scrivi i comandi e premi Esegui (o Ctrl+Invio). Alcune regole:

• ogni comando finisce con il punto e virgola ;, così se ne possono eseguire più di uno insieme;
• le parole chiave (CREATE, TABLE…) si possono scrivere maiuscole o minuscole; per convenzione si scrivono maiuscole, i nomi di tabelle e colonne minuscoli;
• i commenti iniziano con -- (due trattini e uno spazio);
• le stringhe vanno tra apici singoli: 'Primo Levi'.

Per non toccare il database del corso, in questa lezione lavoriamo su un database nuovo, mia_biblioteca. Il database biblioteca importato nella lezione 1 resta com'è.

Creare il database

CREATE DATABASE mia_biblioteca
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE mia_biblioteca;

• CHARACTER SET utf8mb4 permette di salvare tutti i caratteri: lettere accentate, alfabeti diversi, anche le emoji. Usalo sempre.
• La collation utf8mb4_unicode_ci decide come si confrontano e ordinano le stringhe; ci significa case insensitive: 'rossi' e 'Rossi' sono uguali in una ricerca.
• USE sceglie il database su cui lavorare. In phpMyAdmin si ottiene lo stesso cliccando il database a sinistra prima di aprire la scheda SQL.

I tipi di dato

Ogni colonna ha un tipo: il DBMS rifiuta i valori che non c'entrano e sa come ordinarli e confrontarli. I più usati:

Le date si scrivono sempre all'americana, anno-mese-giorno: '2026-10-01' è il 1° ottobre. Il formato italiano si ottiene solo quando le si mostra (lo faremo in PHP).

Creare una tabella

La sintassi è: nome della tabella, poi tra parentesi le colonne separate da virgole, ciascuna con il suo tipo e i suoi vincoli.

CREATE TABLE autori (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(50) NOT NULL,
  cognome VARCHAR(50) NOT NULL,
  nazionalita VARCHAR(40)
) ENGINE=InnoDB;

• PRIMARY KEY: la chiave primaria. Unica e mai vuota.
• AUTO_INCREMENT: il DBMS assegna da solo 1, 2, 3… a ogni nuova riga. Non dobbiamo inventarci gli id.
• NOT NULL: la colonna è obbligatoria. nazionalita non ce l'ha, quindi può restare vuota (NULL).
• ENGINE=InnoDB: il "motore" di memorizzazione. InnoDB è quello predefinito e l'unico che controlla davvero le chiavi esterne: scriverlo non costa nulla e lo rende esplicito.

NULL non è zero e non è stringa vuota: significa "valore sconosciuto o mancante". Uno studente senza telefono ha NULL, non 0.

I vincoli

Ecco la tabella dei libri con tutti i vincoli:

CREATE TABLE libri (
  id INT AUTO_INCREMENT PRIMARY KEY,
  isbn CHAR(13) NOT NULL UNIQUE,
  titolo VARCHAR(150) NOT NULL,
  anno SMALLINT,
  genere VARCHAR(30) NOT NULL,
  copie TINYINT UNSIGNED NOT NULL DEFAULT 1,
  autore_id INT NOT NULL,
  CHECK (copie > 0),
  FOREIGN KEY (autore_id) REFERENCES autori(id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
) ENGINE=InnoDB;

• isbn è UNIQUE: non si possono inserire due libri con lo stesso ISBN. Tentare di farlo dà l'errore 1062 Duplicate entry, che in PHP intercetteremo per mostrare un messaggio chiaro.
• copie vale 1 se non viene indicata, e il CHECK impedisce di scrivere 0.
• La chiave esterna collega autore_id all'id della tabella autori. La colonna deve avere lo stesso tipo della chiave a cui punta (qui INT).
• ON DELETE RESTRICT: non si può cancellare un autore che ha libri. ON UPDATE CASCADE: se cambia l'id di un autore (raro), si aggiorna anche nei libri.

L'ordine conta: la tabella autori va creata prima di libri, perché la chiave esterna deve puntare a una tabella che esiste già. Per lo stesso motivo, quando si cancellano, si toglie prima libri e poi autori.

Le altre due tabelle

CREATE TABLE studenti (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(50) NOT NULL,
  cognome VARCHAR(50) NOT NULL,
  classe VARCHAR(5) NOT NULL,
  email VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE prestiti (
  id INT AUTO_INCREMENT PRIMARY KEY,
  libro_id INT NOT NULL,
  studente_id INT NOT NULL,
  data_prestito DATE NOT NULL,
  data_restituzione DATE NULL,
  CHECK (data_restituzione IS NULL OR data_restituzione >= data_prestito),
  FOREIGN KEY (libro_id) REFERENCES libri(id) ON DELETE RESTRICT,
  FOREIGN KEY (studente_id) REFERENCES studenti(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

La tabella prestiti ha due chiavi esterne: è la traduzione dell'associazione N:M. Il CHECK traduce in SQL uno dei vincoli che avevamo scritto a parole nella lezione 2: la restituzione non può essere prima del prestito.

I CHECK sono rispettati da MySQL dalla versione 8.0.16 e da MariaDB (quella di XAMPP) dalla 10.2. Nelle versioni più vecchie venivano accettati ma ignorati: per questo i controlli importanti si ripetono anche in PHP.

Vuoi vedere com'è fatta una tabella? DESCRIBE libri; mostra colonne, tipi e chiavi. SHOW TABLES; elenca le tabelle del database.

Modificare una tabella: ALTER TABLE

Le tabelle si possono cambiare anche quando contengono già dati.

-- aggiungere una colonna
ALTER TABLE studenti ADD telefono VARCHAR(20);

-- cambiare il tipo di una colonna
ALTER TABLE studenti MODIFY telefono VARCHAR(30);

-- rinominare una colonna
ALTER TABLE studenti RENAME COLUMN telefono TO cellulare;

-- togliere una colonna (e tutti i suoi dati!)
ALTER TABLE studenti DROP COLUMN cellulare;

-- aggiungere un vincolo dopo
ALTER TABLE libri ADD CONSTRAINT anno_valido CHECK (anno <= 2100);

Dare un nome ai vincoli (CONSTRAINT anno_valido) è utile: il messaggio di errore lo cita e così si capisce subito quale regola è stata violata.

Eliminare: DROP

DROP TABLE prestiti;              -- cancella la tabella e tutti i suoi dati
DROP TABLE IF EXISTS prestiti;    -- come sopra, ma senza errore se non esiste
DROP DATABASE mia_biblioteca;     -- cancella l'intero database

DROP non chiede conferma in SQL e non si annulla: prima di usarlo su dati veri si fa una copia (phpMyAdmin → Esporta). Il file biblioteca.sql del corso inizia proprio con DROP DATABASE IF EXISTS biblioteca: per questo, reimportandolo, si riparte da zero.

Esercizi

CREATE TABLE prodotti (
  id INT AUTO_INCREMENT PRIMARY KEY,
  codice_barre CHAR(13) NOT NULL UNIQUE,
  nome VARCHAR(80) NOT NULL,
  prezzo DECIMAL(5,2) NOT NULL,
  quantita SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  ultimo_rifornimento DATE,
  disponibile BOOLEAN NOT NULL DEFAULT TRUE,
  CHECK (prezzo >= 0)
) ENGINE=InnoDB;

CREATE TABLE docenti (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(50) NOT NULL,
  cognome VARCHAR(50) NOT NULL,
  materia VARCHAR(50) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE classi (
  id INT AUTO_INCREMENT PRIMARY KEY,
  anno TINYINT NOT NULL,
  sezione CHAR(1) NOT NULL,
  numero_studenti TINYINT UNSIGNED NOT NULL,
  UNIQUE (anno, sezione),
  CHECK (anno BETWEEN 1 AND 5)
) ENGINE=InnoDB;

CREATE TABLE gite (
  id INT AUTO_INCREMENT PRIMARY KEY,
  meta VARCHAR(80) NOT NULL,
  data_partenza DATE NOT NULL,
  giorni TINYINT UNSIGNED NOT NULL DEFAULT 1,
  costo DECIMAL(7,2) NOT NULL,
  docente_id INT NOT NULL,
  FOREIGN KEY (docente_id) REFERENCES docenti(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE partecipazioni (
  classe_id INT NOT NULL,
  gita_id INT NOT NULL,
  studenti_partecipanti TINYINT UNSIGNED,
  PRIMARY KEY (classe_id, gita_id),
  FOREIGN KEY (classe_id) REFERENCES classi(id) ON DELETE CASCADE,
  FOREIGN KEY (gita_id) REFERENCES gite(id) ON DELETE CASCADE
) ENGINE=InnoDB;