Corso MySQL #4: query principali e gestione del CRUD

In questa quarta parte del corso su MySQL proseguiremo verso lo studio del linguaggio con l’introduzione del concetto di CRUD e delle query basilari per poter iniziare già da subito a popolare il vostro database (dopo aver imparato come crearlo nella seconda parte del nostro corso).

Che cosa significa CRUD?

L’acronimo CRUD è probabilmente uno dei concetti principali del linguaggio SQL, in quanto racchiude le operazioni principali che è possibile svolgere su un database. CRUD, infatti, sta per CreateReadUpdateDelete, che in SQL corrispondono ai comandi SELECT, CREATE, INSERT, UPDATE e DELETE. Tralasciando eventuali sintassi più avanzate, che vedremo più in là, il linguaggio SQL si basa principalmente su queste quattro query base per poter eseguire gran parte delle operazioni di creazione e gestione di un database. Vediamole singolarmente.

CREATE

Il comando CREATE, che abbiamo avuto modo già di affrontare in un articolo precedente in maniera piuttosto generica, viene utilizzato solitamente per la creazione iniziale di un database e le relative tabelle, applicando il comando:

CREATE DATABASE db_employee

CREATE TABLE Employee
  (
  employee_id int auto_increment primary key,
  employee_first_name varchar(500) NOT null,
  employee_last_name varchar(500) NOT null,
  employee_emailID varchar(500)
  ); 

Con questo codice andiamo a creare un database chiamato db_employee contenente una tabella Employee formata da quattro colonne:

• employee_id di tipo intero come chiave primaria e auto incrementale
• employee_first_name di tipo stringa che non accetta valori null
• employee_last_name di tipo stringa che non accetta valori null
• employee_emailID di tipo stringa che accetta valori null

Differenza tra chiave primaria, chiave secondaria e indici

Quando andiamo a creare una tabella, è fondamentale impostare una chiave primaria su una delle colonne (solitamente l’ID delle righe) in quanto permette di evitare le righe duplicate nella tabella e quindi di avere un database più pulito. E’ possibile configurare la chiave primaria su uno o più campi della tabella, così che i record inseriti siano univoci sulla base di una singola colonna o anche tuple di colonne. Per assegnare la chiave primaria a una colonna, è possibile definirla come PRIMARY KEY direttamente accanto al nome della colonna oppure applicare la seguente dicitura se vogliamo utilizzare più campi come chiave primaria:

CREATE TABLE Employee
  (
  employee_id int auto_increment,
  employee_first_name varchar(500) NOT null,
  employee_last_name varchar(500) NOT null,
  employee_emailID varchar(500)

  PRIMARY KEY (employee_id , employee_emailID)); 

La chiave primaria è uno strumento fondamentale non solo per rendere univoci i record di una tabella ma anche per mettere in relazione tale tabella con altre che hanno bisogno di un riferimento al campo univoco definito dalla chiave primaria. Ad esempio, se definiamo una tabella Generi_Film dove ogni genere è costituito da un suo ID univoco, potremmo voler definire una tabella Film dove ogni record contiene le varie informazioni sul film e il genere viene memorizzato tramite l’ID della tabella Generi_Film, creando quindi una relazione uno-a-molti in quanto a ogni genere possono corrispondere più film. In questo caso l’ID del genere nella tabella Film viene detto chiave secondaria in quanto è un riferimento alla chiave primaria di un’altra tabella. Oltre alla relazione uno-a-molti è possibile avere anche una relazione uno-a-uno o molti-a-molti a seconda delle esigenze sul database di partenza.

Lo strumento base per poter mettere in relazione due tabelle è l’INDICE, oltre che impostare la relazione tramite dati corretti e coerenti, che in MySQL è possibile creare sfruttando la seguente sintassi:

//Direttamente alla creazione della tabella

CREATE TABLE nome_tabella (

nome_campo INT NOT NULL


INDEX nome_indice (nome_campo) );

//Separatamente alla creazione della tabella

CREATE INDEX nome_Indice ON nome_tabella (nome_campo) ;

L’indice è anche un ottimo strumento per ottimizzare il caricamento dei dati oltre che mettere in relazione due tabelle, in quanto la lettura sarà estremamente più veloce rispetto a una tabella priva di indici.

SELECT

Con il comando SELECT semplicemente andiamo a selezionare degli specifici dati all’interno di un tabella, potendo scegliere se visualizzare tutte le colonne (sfruttando anche la wildcard “*”) o solo alcune:

//Selezione massiva

SELECT * FROM nome_tabella -> è possibile anche specificare manualmente il nome di tutti i campi

//Selezione puntuale

SELECT campo1, campo2 FROM nome_tabella

Consideriamo ad esempio la seguente tabella:

Utilizzando la selezione massiva otterremo esattamente la tabella così com’è, in quanto andiamo a selezionare tutti i dati e le colonne mentre con la selezione puntuale avremo il risultato:

Filtri e ordinamenti

Alla clausola SELECT è possibile anche applicare ulteriori filtri per visualizzare dei dati sotto opportune condizioni sulle colonne della tabella, utilizzando la parola chiave WHERE:

SELECT * FROM nome_tabella WHERE condizione

E’ possibile anche applicare più condizioni alla clausola WHERE, separandole tramite la parola chiave AND (es. WHERE condizione1 AND condizione2). Facendo un esempio concreto sfruttando la tabella iniziale e scrivendo:

SELECT campo1, campo2 FROM nome_tabella WHERE campo1=1

In questo caso otterremo una selezione delle prime due colonne della tabella e solamente le righe per cui la colonna campo1 assume il valore 1.

Tramite la clausola ORDER BY (da scrivere obbligatoriamente dopo il WHERE) è possibile eventualmente ordinare i record impostando opportune regole. Si possono inoltre raggruppare i record usando GROUP BY (da posizionare tra il WHERE e l’ORDER BY), quasi sempre utilizzato in una SELECT che sfrutta le cosiddette funzioni di aggregazione, ovvero funzioni che permettono di sommare (SUM), calcolare medie (AVG) o contare il numero di record in una tabella (COUNT).

SELECT nome_colonna
FROM nome_tabella
WHERE condizione
GROUP BY nome_colonna
ORDER BY nome_colonna;

Prendiamo come esempio finale la tabella:

Se vogliamo calcolare quante righe abbiano il campo1 a 1 e quante abbiano tale campo a 0, ci basterà applicare la SELECT:

SELECT campo1, COUNT(*) as count
FROM nome_tabella
GROUP BY campo1
ORDER BY campo1

E il risultato sarà una tabella dove per il campo1=1 avremo un conteggio di 3 record mentre per il campo1=0 otterremo un conteggio di 2:

INSERT

La clausola INSERT (più precisamente INSERT INTO) è quella che permette l’inserimento vero e proprio dei dati all’interno della tabella. La sintassi è:

INSERT INTO tabella (nome_campi) VALUES (valore_campi)

Nel caso in cui si inseriscano dei valori per tutte le colonne, non è necessario specificare il nome dei campi prima dei valori. C’è però una regola fondamentale sull’utilizzo di questo comando SQL. Qualora vengano specificati i campi su cui applicare dei valori è obbligatorio indicarli nello stesso ordine con cui sono dichiarati in tabella e ovviamente anche i valori dovranno rispettare lo stesso ordine per evitare errori relativi a tipi non corrispondenti o record non coerenti. Nel caso in cui i campi non vengano dichiarati (quindi si applica l’inserimento su tutte le colonne), i valori seguiranno lo stesso ordine dichiarato nella tabella in fase di creazione.

Prendiamo come esempio la tabella seguente:

Se vogliamo inserire, per sempio, la persona Leonardo Verdi di Verona, possiamo procedere su due strade:

INSERT INTO tabella (Nome, Cognome, Citta) VALUES ('Leonardo','Verdi','Verona')

oppure

INSERT INTO tabella VALUES ('Leonardo','Verdi','Verona')

Alla fine otterremo la seguente tabella:

UPDATE

La parola chiave UPDATE aggiorna una tabella o un database già esistente, permettendo non solo di modificare uno o più record di una tabella ma anche di applicare modifiche al database stesso aggiungendo indici, colonne e molto altro. Per quanto riguarda le tabelle, la sintassi per modificare uno o più record è:

UPDATE nome_tabella SET campo1=valore1, campo2=valore2 

L’update sottostà alle stesse regole della SELECT per quanto riguarda l’utilizzo di regole di filtro per poter aggiornare solamente specifici record. E’ un’operazione molto delicata in quanto basta veramente poco per modificare un’intera base dati con un singolo comando andando a perdere completamente traccia dei dati precedenti. Infatti, spesso l’UPDATE viene utilizzato in abbinamento con il WHERE per specificare esattamente quali righe modificare, altrimenti vengono modificate tutte. Ovviamente utilizzando l’UPDATE bisogna sempre far sì che i nuovi valori che inseriamo rispecchino le regole di tipizzazione definite in fase di creazione della tabella, anche se solitamente viene segnalato un errore direttamente dall’ambiente di sviluppo qualora si inserisca un valore non permesso (es. un intero su una stringa, data in un formato non corrispondente ecc.).

DELETE e DROP

DELETE e DROP sono i due comandi principali per eliminare dati da un database. La differenza tra i due è che mentre il DELETE elimina dei record da una tabella, il DROP riguarda esclusivamente la cancellazione di un’intera tabella o addirittura di un database. La sintassi è:

DROP TABLE tabella -> elimina completamente una tabella
DROP DATABASE db -> elimina completamente il db
DELETE FROM tabella -> elimina tutti i dati in una tabella (da usare possibilmente con il WHERE)

Il DELETE, come l’UPDATE, permette di applicare delle specifiche condizioni tramite il WHERE per poter eliminare righe ben definite. Raramente viene usato il DELETE per rimuovere tutti i dati di una tabella.

Differenza tra DELETE e TRUNCATE

In MySQL, come anche in SQL Server, esiste anche un altro comando apparentemente identico al DELETE che permette di cancellare tutto il contenuto di una tabella: TRUNCATE TABLE. Applicare DELETE TABLE tabella oppure TRUNCATE TABLE tabella produrrà lo stesso effetto a livello di record, rimuovendo ogni singola riga. C’è, però, una differenza fondamentale tra le due istruzioni, in quanto il TRUNCATE TABLE permette anche di resettare la numerazione gli ID delle righe mentre il DELETE TABLE memorizza l’ultimo ID per i futuri inserimenti. In poche parole, se una tabella contiene 10 record con ID da 1 a 10 (chiave primaria con incremento automatico di 1 a ogni inserimento), effettuando un DELETE TABLE qualora andassimo a inserire un nuovo record questo avrebbe come ID=11 mentre utilizzando il TRUNCATE TABLE si ritornerebbe a ID=1.

Con questo articolo abbiamo voluto introdurvi alle query principali del linguaggio MySQL, con cui potrete già fare pratica sfruttando software come MySQL Workbench per poter iniziare a creare e gestire il vostro primo database.