Corso MySQL #2: come creare un database

DiMarco Nisticò

PUBBLICATO IL 11 Feb 2019 ALLE 15:00 - AGGIORNATO IL 3 Agosto 2023 ALLE 16:24

Dopo aver visto come rappresentare un database, attraverso il diagramma ER e lo schema logico, è il momento di passare alla pratica e vedere come programmare un database in linguaggio SQL.

Il linguaggio SQL ha una sintassi molto simile a quella di un linguaggio ad oggetti, come il C++ ad esempio. La differenza sta ovviamente nelle istruzioni di base del linguaggio, dedicate alla gestione e modifica del database.

Per poter programmare in SQL e creare un database vero e proprio, c’è bisogno di un client su cui poter effettivamente scrivere ed eseguire il codice sorgente del database, permettendoci di modificarlo e gestirne i vari elementi, e di un server che permetta al PC di poter effettuare una connessione vera e propria al database. I più utilizzati sono sicuramente MySQL Workbench phpMyAdmin (indicato maggiormente per chi possiede siti web). Quest’oggi vedremo come creare un primo piccolo database con MySQL Workbench, scelta consigliata per chi è alle prime armi con il linguaggio per via di un’interfaccia più intuitiva e semplice da utilizzare. Vi spiegheremo anche come generare un database in SQL a partire da un diagramma ER generato proprio con Workbench.

Fase 1: installazione di MySQL Server e Workbench

Per prima cosa è necessario installare il server MySQL, utile per potersi connettere al database tramite Workbench. Potete installare l’ultima versione di MySQL, attualmente la 8.0.15, utilizzando MySQL Installer, un software che permette di scaricare ed installare diversi programmi che si basano su MySQL, tra cui anche Workbench. Dopo aver installato il programma ed avviato, dovrete premere sul pulsante Add… nel menù a destra. Apparirà la schermata seguente:

mysql installer

Sotto la scheda Available Products dovrete cercare sia MySQL Server 8.0.15 che MySQL Workbench 8.0.15 e premere sulla freccia a destra, posta proprio al centro delle due finestre. Quindi i due programmi ora dovranno essere presenti sulla finestra a destra, pronti per essere installati. Dunque premente su Next e poi su Execute. Partirà ora l’installazione sia del server che del client. L’operazione dovrebbe richiedere solamente pochi minuti. Al termine saremo già pronti per accedere al server locale di Workbench ed iniziare ad operare sul nostro primo database.

Configurazione di MySQL Server

Prima di precedere oltre è doveroso configurare correttamente il servizio MySQL, così che non vengano rilevati problemi di alcun genere. Bisognerà aprire nuovamente MySQL Installer e nell’elenco dei software installati cliccare su Reconfigure affianco a MySQL Server. Si aprirà ora il wizard di configurazione. Senza dilungarci troppo tra immagini varie, vi elenchiamo direttamente i settaggi da impostare nelle varie schermate:

  1. Group Replication: Standalone MySQL Server / Classic MySQL Replication.
  2. Type and Networking: impostare Config Type come Development Computer. Mantenere la spunta su TCP/IP e Open Windows Firewall ports for network access. Lasciare invariata anche la porta stessa.
  3. Authentication Method: qui la scelta dipende solamente dal fatto che utilizzate una versione di MySQL più vecchia o recente. Nel nostro caso bisogna selezionare Use Strong Password Encryption for Authentication (RECOMMENDED).
  4. Accounts and Roles: qui bisognerà impostare una password per l’account amministratore (root) per poter accedere al futuro database senza problemi.
  5. Windows Service: mettere la spunta su Configure MySQL Server as a Windows service Start the MySQL Server on Windows Startup per aggiungere il servizio MySQL a Windows ed avviarlo all’avvio automaticamente.
  6. Premere su Execute per applicare le modifiche sopracitate.

Fase 2: configurazione di MySQL Workbench

Adesso è il momento di passare a MySQL Workbench, lo strumento che utilizzeremo per creare un database da zero. Come prima azione preliminare bisogna verificare che il servizio MySQL sia effettivamente attivo sul sistema. Per farlo basta premere la combinazione Windows+R e digitare services.msc nella schermata Esegui. Premere infine OK per accedere all’elenco completo dei servizi di Windows. Qui è necessario controllare che il servizio corrispondente a MySQL sia in esecuzione (solitamente chiamato MySQL o MySQL seguito da un numero casuale scelto da voi). Se tutto è ok si può procedere, altrimenti basta premere con il tasto destro del mouse sul nome del servizio e cliccare su Avvia.

Prima connessione su MySQL Workbench

Una volta fatta questa verifica, torniamo a MySQL Workbench. Al primo avvio del programma si dovrebbe presentare una schermata simile a questa:

mysql workbench schermata principale

Sotto la voce MySQL Connections sono presenti i vari server che andranno ad ospitare determinati database. Visto che noi andremo a programmare su un server ospitato esattamente nel nostro PC, troveremo solamente il server locale (localhost con porta 3306, modificabile tramite il wizard di MySQL Server) con root come amministratore. E’ possibile comunque aggiungere eventuali server remoti cliccando sul simbolo “+” posto poco sopra. Per accedere al server locale, clicchiamo due volte sulla nostra connessione, digitiamo la password impostata precedentemente tramite la configurazione di MySQL e premiamo su OK. Se tutto è andato a buon fine, dovrebbe apparire la schermata di gestione di MySQL Workbench.

mysql workbench query

In alto è presente la classica Barra degli Strumenti, dove è possibile creare un nuovo database, eseguire uno script SQL già compilato precedentemente oppure modificare le impostazioni tecniche del server. A sinistra trovano spazio i database, che vengono rappresentati con una semplice struttura ad albero che ne identifica le tabelle e gli indici. Infine al centro c’è lo spazio di lavoro per sviluppare il codice sorgente.

Ciò che interessa a noi oggi però è la semplice creazione di un database. In seguito vedremo poi come creare una pagina web che possa connettersi al database ed eseguire delle query direttamente dalla pagina in PHP. Ora però concentriamoci sul nostro database. MySQL Workbench offre, oltre ai classici strumenti di gestione/modifica di database, anche la possibilità di creare i diagrammi ER di cui abbiamo già discusso nella prima parte del corso. La particolarità è che in MySQL Workbench si può esportare il diagramma sotto forma di script SQL, che può essere eseguito direttamente per poter generare il database ad esso associato. E’ un enorme vantaggio perché ci permette di risparmiare molto tempo che impiegheremmo per compilare il codice manualmente ma al contrario non permette ai neofiti de linguaggio di prendere mano con le varie istruzioni e sintassi. Il nostro consiglio è dunque quelli di usare i diagrammi ER come riferimento e provare poi da voi a scrivere il codice corrispondente.

I diagrammi ER su MySQL Workbench

Per creare un diagramma ER, tornare alla pagina principale di MySQL Workbench e cliccare sull’icona a forma di diagramma sulla sinistra. Ci troveremo davanti la lista dei modelli (Models), ovviamente ancora vuota. Quindi clicchiamo sul “+” per crearne uno nuovo. Clicchiamo ora due volte sulla voce Add Diagram per accedere alla pagina di creazione del diagramma.

mysql workbench diagramma er

Come si può notare l’interfaccia non è poi così impossibile da interpretare. Le icone per aggiungere le varie entità e relazioni sono facilmente distinguibili. Sono presenti tutti gli strumenti necessari per inserire chiavi primarie e non, relazioni con partecipazione parziale o totale e gruppi di entità.

Primo codice in SQL

Una volta finito il diagramma, è possibile esportare il codice SQL delle varie entità cliccando con il tasto destro del mouse e selezionando Copy SQL to Clipboard. Basterà aprire la connessione MySQL ed incollare il codice appena copiato per poterlo eseguire e creare fisicamente quella stessa entità con quegli specifici attributi. Vediamo un esempio di codice:

CREATE TABLE IF NOT EXISTS `mydb`.`table1` (
  `idtable1` INT NOT NULL,
  `table1col` VARCHAR(45) NULL,
  `table1col1` VARCHAR(45) NULL,
  `table2_idtable2` INT NOT NULL,
  PRIMARY KEY (`idtable1`),
  INDEX `fk_table1_table2_idx` (`table2_idtable2` ASC) VISIBLE,
  CONSTRAINT `fk_table1_table2`
    FOREIGN KEY (`table2_idtable2`)
    REFERENCES `mydb`.`table2` (`idtable2`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

In questo caso verrà creata la tabella table1 nel database rinominato mydb, dotata di 4 attributi (idtable1, table1col, table1col1 e table2_idtable2) di cui tre appartenenti alla tabella e uno richiamato da una seconda tabella tramite l’istruzione FOREIGN KEY. Ciò significa che molto probabilmente tra la tabella 1 e la tabella 2 ci sarà una relazione che le lega. Ovviamente il database può essere rinominato in qualsiasi modo. Per crearlo basta accedere al server MySQL come root e premere la quarta icona partendo da sinistra, appena sotto la barra degli strumenti. Se non riuscite a trovarla, tenete il cursore sulle varie icone e premete quella con la dicitura Create a new schema in the connected server (schema=database). Questa operazione è fondamentale poiché altrimenti ogni codice SQL restituirà un errore dovuto alla mancanza del database corrispondente nel quale creare i vari elementi.

Il codice si può semplificare notevolmente se si esegue l’SQL direttamente nel database di riferimento, senza quindi eseguirlo esternamente. Premere due volte sul nome del database, che verrà contrassegnato in grassetto, e successivamente scrivere il proprio codice SQL. Ecco un altro esempio di come creare diverse tabelle:

CREATE TABLE Studente (

attributo1 TIPO(),
attributo2 TIPO(),
PRIMARY KEY (attributo));

CREATE TABLE Corso (

attributo1 TIPO(),
attributo2 TIPO(),
PRIMARY KEY (attributo));

CREATE TABLE Libro (

attributo1 TIPO(),
attributo2 TIPO(),
PRIMARY KEY (attributo)
FOREIGN KEY (attributo) REFERENCES tabella);

Il codice è molto generico ma permette di capire immediatamente come creare un’entità o relazione in SQL. La funzione CREATE è sicuramente quella che useremo di più, visto che è la base di qualunque database. Per eliminare una tabella invece il codice è davvero semplicissimo: DROP TABLE nometabella;. Se avete già creato un diagramma ER su carta, assicuratevi che tutto sia stato svolto correttamente, così da poter creare il database senza riscontrare errori.

Vediamo infine come poter aggiungere dei valori effettivi all’interno di una tabella. La regola fondamentale è che se una tabella è costituita da un determinato numero di attributi, altrettanti ne dovranno essere inseriti per evitare di avere spazi vuoti che occuperebbero solamente memoria. Il codice per aggiungere un valore ad una tabella è il seguente:

INSERT INTO tabella
VALUES (valore1, valore2,...,valoreX);

Facciamo ora un esempio più pratico. Supponiamo di voler creare una tabella chiamata Famiglia, dove ogni membro è contrassegnato dal nome e un codice identificativo univoco (es. codice fiscale o un numero ordinario). Come si crea la tabella corrispondente e come si aggiungono i rispettivi valori? Di seguito una possibile soluzione:

CREATE TABLE Famiglia(

nome CHAR(10),
ssn CHAR(40),
PRIMARY KEY (ssn));

INSERT INTO Famiglia
VALUES ('Mario Rossi',1);

INSERT INTO Famiglia
VALUES ('Carlo Rossi',2);

INSERT INTO Famiglia
VALUES ('Stefania Bianchi',3);

INSERT INTO Famiglia
VALUES ('Alessandro Rossi',4);

Questo potrebbe essere un possibile codice base per la costruzione della tabella appena menzionata. Abbiamo creato la tabella impostando l’ssn come chiave primaria, visto che è unico per ogni persona. Successivamente abbiamo applicato la funzione INSERT INTO … VALUES per inserire i singoli valori nella tabella. A questo punto abbiamo creato un vero e proprio database, seppur molto piccolo, su cui eseguire eventuali query (richieste), come ad esempio la stampa del nome del familiare corrispondente ad uno specifico ssn.

Con questo si conclude la seconda parte del corso SQL. Nel prossimo articolo andremo a vedere alcuni strumenti più complessi per la gestione di un database.

Di Marco Nisticò

Sviluppatore informatico, cerco sempre di stare al passo con i tempi in un mondo ormai circondato dalla tecnologia.

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *

Questo sito usa Akismet per ridurre lo spam. Scopri come i tuoi dati vengono elaborati.