Unlimited Plugins, WordPress themes, videos & courses! Unlimited asset downloads! From $16.50/m
Advertisement
  1. Code
  2. Databases

SQL per Principianti Parte 2

by
Length:LongLanguages:

Italian (Italiano) translation by Piergiorgio Sansone (you can also view the original English article)

E' importante per ogni sviluppatore web avere familiarità con l'interazione con i database. Nella seconda parte di questa serie, continueremo ad esplorare il linguaggio SQL, ed applicheremo quanto appreso su un database MySQL. Studieremo gli Indici, i Tipi di Dato e le strutture di query complesse.

Di cosa abbiamo bisogno?

Fate riferimento al paragrafo "Di cosa abbiamo bisogno" del primo articolo qui: SQL per Principianti (parte 1).

Se volete seguire gli esempi in questo articolo sul vostro server di sviluppo, fate quanto segue:

  1. Aprite la consolle MySQL ed accedete
  2. Se non lo avete già fatto, create un database chiamato "my_first_db" con la query CREATE.
  3. Passiamo al database con l'istruzione  USE.

Indici del Database

Gli indici (o chiavi) sono utilizzanti manualemnte per incrementare la velocità delle operazioni di recupero dati (es. SELECT) sulla tabella.

Costituiscono un elemento importante per una buon disegno  del database, che è difficile classificarli come "ottimizzazione". Nella maggior parte dei casi sono inclusi nel progetto iniziale, ma possono anche essere aggiunti più tardi con una query di ALTER TABLE.

Le ragioni più comuni per l'indicizzazione delle colonne del database sono:

  • Quasi ogni tabella deve avere un indice di PRIMARY KEY (chiave primaria), solitamente come una colonna "id".
  • Se una colonna deve contenere valori univoci, dovrebbe avere un indice UNIQUE (univoco).
  • Se avete intenzione di eseguire spesso ricerche su una colonna (nella clausola WHERE), dovrebbe avere un regolare INDEX (indice).
  • Se una colonna è utilizzata per una relazione con un'altra tabella, dovrebbe esserci una FOREING KEY (chiave esterna) se possibile, o avere diversamente solo un indice regolare.

PRIMARY KEY (Chiave primaria)

Quasi ogni tabella deve avere una chiave primaria, nella maggior parte dei casi come INT con l'opzione AUTO_INCREMET.

Se rivedete il primo articolo, abbiamo creato il campo user_id nella tabella users ed è una PRIMARY KEY. In questo modo, in un'applicazione web possiamo riferirci a tutti gli utenti con il loro numero id.

I valori memorizzati in una colonna di PRIMARY KEY devono essere univoci. Inoltre, non ci possono  essere più di una PRIMARY KEY su ogni tabella.

Vediamo un esempio di query, creazione di una tabella per la lista degli stati USA:

Può essere anche scritta in questo modo:

UNIQUE

Poiché ci aspettiamo che il nome dello stato sia un valore univoco, dobbiamo cambiare un po' il precedente esempio di query:

Per impostazione predefinita, l'indice verrà denominato dopo il nome della colonna. Se si desidera, è possibile assegnargli un nome diverso:

Ora l'indice è denominato 'state_name' invece di 'nome'.

INDEX

Diciamo che vogliamo aggiungere una colonna per rappresentare l'anno che ciascuno Stato ha aderito.

Abbiamo solo aggiunto ed indicizzato la colonna join_year. Questo tipo di indice non possiede la restrizione di unicità.

Potete anche chiamarlo KEY invece di INDEX.

Informazioni sulle prestazioni

Aggiungere un indice riduce le performance delle query di INSERT e UPDATE. Perchè ogni volta che un nuovo dato viene aggiunto nella tabella, l'indice viene anche aggiornato automaticamente, questo richiede un lavoro ulteriore. Superato solitamente di gran lunga dall'incremento delle prestazioni sulle query di SELECT . Ma ancora, non è sufficiente aggiungere gli indici in ogni colonna della tabella singola senza pensare alla query che verrà eseguita.

Tabella di esempio

Prima di andare oltre con altre query, mi piacerebbe creare una tabella di esempio con alcuni dati.

Si tratta di una lista degli Stati degli Stati Uniti, con le loro le date di adesione (la data in cui lo stato  ha ratificato la costituzione degli Stati Uniti o è stato ammesso all'Unione) e le loro popolazioni attuali. Potete copiare ed incollare il seguente codice alla console MySQL:

GROUP BY: Raggruppare i dati 

La clausola GROUP BY raggruppa le righe di dati risultanti in gruppi. Ecco un esempio:

Così che cosa è successo? Abbiamo 50 righe nella tabella, ma sono 34 i risultati che sono stati restituiti dalla query. Questo perché i risultati sono stati raggruppati per la colonna 'join_year'. In altre parole, vediamo solo una riga per ogni valore distinto di join_year. Poiché alcuni Stati hanno la stessa join_year, abbiamo meno di 50 risultati.

Ad esempio, c'era solo una riga per l'anno 1787, ma ci sono 3 stati in questo gruppo:

Così abbiamo tre stati qui, ma solo il nome del Delaware viene  mostrato fino dopo la precedente query di GROUP BY. In realtà, avrebbe potuto essere uno qualsiasi dei tre Stati e non possiamo contare su questo pezzo di dati. Quindi qual è il punto sull' utilizzo della clausola GROUP BY?

Sarebbe per lo più inutile senza l'utilizzo di una funzione di aggregazione come COUNT(). Vediamo quello che alcuni di questi non funzioni e come si possono ottenere alcuni dati utili.

Count(*): Conteggio delle righe

Questa è forse la funzione più comunemente utilizzata insieme alle query di GROUP BY. Restituisce il numero di righe in ogni gruppo.

Ad esempio noi possiamo utilizzarlo per vedere il numero di stati per ogni join_year:

Raggruppamento di tutto

Se si utilizza una funzione di aggregazione GROUP BY e non si specifica una clausola GROUP BY, gli interi risultati saranno rimessi in un unico gruppo.

Numero di tutte le righe nella tabella:

Numero di righe che soddisfano una clausola WHERE:

MIN (), MAX () e AVG()

Queste funzioni restituiscono i valori minimi, massimi e medio:

GROUP_CONCAT()

Questa funzione consente di concatenare tutti i valori all'interno del gruppo in una singola stringa, con un separatore di dato.

Nel primo esempio di query GROUP BY, abbiamo potuto vedere solo un nome di stato ogni anno. È possibile utilizzare questa funzione per vedere tutti i nomi in ogni gruppo:

Se l'immagine ridimensionata è difficile da leggere, questa è la query:

SUM()

È possibile utilizzare questa funzione per sommare i valori numerici.

IF()& CASE: Flusso di controllo

Simile agli altri linguaggi di programmazione, SQL ha alcuni  supporti per il flusso di controllo.

IF()

Si tratta di una funzione che accetta tre argomenti. Il primo argomento è la condizione, il secondo argomento viene utilizzato se la condizione è vera  e il terzo argomento viene utilizzato se la condizione è falsa.

Ecco un esempio più pratico dove lo usiamo con la funzione SUM():

La prima chiamata di SUM() conta il numero di grandi Stati (popolazione oltre 5 milioni) e la seconda conta il numero di piccoli stati. La chiamata di IF() all'interno di queste chiamate di SUM() restituisce 1 o 0, sulla base della condizione.

Ecco il risultato:

CASE

Funziona in modo simile alle istruzioni switch-case che conoscerete se avete familiarità con la programmazione.

Diciamo che vogliamo categorizzare ogni stato in uno dei tre possibili categorie.

Come potete vedere, possiamo effettivamente raggruppare il valore restituito dall'istruzione. Ecco cosa succede:

HAVING: Condizioni su campi nascosti

La clausola HAVING permette di applicare condizioni a campi 'nascosti', come i risultati restituiti di funzioni di aggregazione. Così viene usata solitamente insieme a GROUP BY.

Ad esempio, esaminiamo la query che abbiamo usato per contare il numero degli Stati entro l'anno di registrazione:

Il risultato era 34 righe

Tuttavia, diciamo che siamo solo interessati alle righe che hanno un conteggio superiore a 1. Non possiamo usare la clausola WHERE per questo:

Questo è il momento in cui HAVING diventa utile:

Tieni presente che questa funzione potrebbe non essere disponibile in tutti i sistemi di database.

Sottoquery

È possibile ottenere i risultati da una query e utilizzati per un'altra query.

In questo esempio, otterremo lo stato con la più alta popolazione:

La query interna restituirà il più alta popolazione di tutti gli Stati. E la query esterna cercherà la tabella nuovamente utilizzando tale valore.

Si potrebbe pensare che questo è stato un cattivo esempio, e sono un po ' d'accordo. La stessa query potrebbe essere scritta in modo più efficiente come questo:

I risultati in questo caso sono gli stessi, tuttavia c'è una differenza importante tra questi due tipi di query. Forse un altro esempio lo dimostrerà meglio.

In questo esempio, otterremo gli ultimi Stati che hanno aderito all'Unione:

Ci sono due righe nei risultati di questo tempo. Se avessimo usato la clausola ORDER BY... LIMITE 1 tipo di query qui, non avremmo ricevuto lo stesso risultato.

IN()

A volte se si desidera utilizzare più risultati restituiti dalla query interna.

La query seguente trova gli anni nei quali più Stati hanno aderito all'Unione, e restituisce l'elenco di questi Stati:

Di più sulle Subquery 

Le sottoquery possono diventare abbastanza complesse, quindi non avrò molto maggiori in loro in questo articolo. Se volete saperne di più su di loro, Scopri il manuale di MySQL.

Inoltre vale la pena notare che la sottoquery possono talvolta ridurre le performance, quindi dovrebbero essere usate con cautela.

UNION: Combinazione dei dati

Con una query UNION, potete combinare i risultati delle SELECT multiple.

Questo esempio combina gli stati che iniziano con la lettera 'N' e gli stati con la maggior popolazione

Nota che New York è in entrambi è grande e il suo nome inizia con la lettera ' n '. Ma viene visualizzata solo una volta perché le righe duplicate vengono rimosse dai risultati automaticamente.

Un'altra cosa bella della UNION è che è possibile combinare le query su tabelle diverse.

Supponiamo che abbiamo tabelle per dipendenti, dirigenti e clienti. E ogni tabella ha un campo posta elettronica. Se vogliamo recuperare tutti i messaggi di posta elettronica con una singola query,possiamo eseguire questa:

Questa recupererebbe tutte le email di tutti i dipendenti e dirigenti, ma solo le email dei clienti che hanno sottoscritto per ricevere messaggi di posta elettronica.

INSERT continuato

Abbiamo già parlato della query di INSERT nell'ultimo articolo. Ora che abbiamo esplorato gli indici del database, possiamo parlare delle funzionalità più avanzate dalla query di INSERT.

INSERT... SULL' AGGIORNAMENTO DI CHIAVE DUPLICATA

Questo è quasi come un'istruzione condizionale. La query tenta innanzitutto di eseguire un inserimento, e se non riesce a causa di un valore duplicato per una chiave primaria o chiave univoca, esegue invece un aggiornamento.

Creiamo innanzitutto una tabella di test.

È una tabella per tenere i prodotti. La colonna 'magazzino' è il numero di prodotti che abbiamo in magazzino.

Ora tenta di inserire un valore duplicato e vedi cosa succede.

Abbiamo avuto un errore come previsto.

Diciamo che abbiamo ricevuto la nuova macchina per il pane e desideriamo aggiornare il database, e non sappiamo se il record già esiste. Potremmo controllare i record esistenti e quindi eseguire un'altra query basata su quello. O potremmo semplicemente farlo tutto in una semplice query:

REPLACE INTO

Questo funziona esattamente come la INSERT con una sola eccezione importante. Se viene trovata una riga duplicata, prima la elimina e poi esegue l'inserimento, così non otteniamo messaggi di errore.

Notate che poiché questo è in realtà una riga completamente nuova, l'id è stato incrementato.

INSERT IGNORE

Si tratta di un modo per sopprimere gli errori duplicati, solitamente per impedire all'applicazione una rottura. A volte se si desidera tentare di inserire una nuova riga e lasciarla fallire senza eventuali reclami nel caso in cui venga trovato un duplicato.

Non sono stati restituiti errori e le righe non sono state aggiornate.

Tipi di dati

Ogni colonna della tabella deve avere un tipo di dati. Finora abbiamo usato i tipi INT, VARCHAR e DATE ma non ne parliamo in dettaglio. Inoltre ci sono diversi altri tipi di dati che si debba esplorare.

Innanzitutto, cominciamo con i tipi di dati numerici. Mi piace metterli in due gruppi distinti: interi vs Non-interi.

Tipi di dati Integer

Una colonna integer può contenere solo numeri naturali (senza decimali). Per impostazione predefinita possono essere numeri negativi o positivi. Ma se è impostata l'opzione senza segno, può contenere solo numeri positivi.

MySQL supporta 5 tipi di numeri interi, con varie dimensioni e gamme:

Tipi di dati numerici non interi

Questi tipi di dati possono contenere numeri decimali: FLOAT, DOUBLE e DECIMAL.

FLOAT è di 4 byte, DOUBLE è di 8 byte e funzionano allo stesso modo. Tuttavia DOUBLE ha una migliore precisione.

DECIMAL(M,N) ha una dimensione variabile sulla base del livello di precisione, che può essere personalizzato. M è il numero massimo di cifre, e N è il numero di cifre a destra del separatore decimale.

Ad esempio, DECIMAL(13,4) ha un massimo di 9 cifre intere e 4 cifre frazionarie.

Tipi di dati String

Come suggerisce il nome, possiamo memorizzare le stringhe in queste colonne con tipo di dati.

CHAR(N) può contenere fino a N caratteri e ha una dimensione fissa. Ad esempio CHAR(50) prenderà sempre 50 caratteri di spazio, per ogni riga, indipendentemente dalla dimensione della stringa. Il massimo assoluto è di 255 caratteri

VARCHAR (N) funziona allo stesso modo, ma la dimensione di archiviazione non è fissa. N viene utilizzato solo per la dimensione massima. Se viene memorizzata una stringa inferiore a N caratteri, ci vorrà molto meno spazio sul disco rigido. La dimensione massima assoluta è 65535 caratteri.

Variazioni del tipo di dato TEXT è più adatto per le stringhe lunghe. Il testo ha un limite di 65535 caratteri,  MEDIUMTEXT 16,7 milioni di caratteri e LONGTEXT 4,3 miliardi. MySQL di solito li memorizza su posizioni separate sul server affinché il deposito principale per la tabella rimane relativamente piccolo e veloce.

Tipi di dato

DATE memorizza le date e le visualizza in questo formato 'AAAA-MM-DD' ma non contiene le informazioni di tempo. Ha una gamma di 1001-01-01 a 9999-12-31.

DATETIME contiene sia la data e l'ora e viene visualizzato in questo formato 'AAAA-MM-GG HH'. Dispone di una gamma di ' 1000-01-01 00:00:00 ' a ' 9999-12-31 23:59:59 '. Ci vogliono 8 byte di spazio.

TIMESTAMP funziona come DATETIME con poche eccezioni. Ci vogliono solo 4 byte di spazio e la gamma è ' 1970-01-01 00:00:01 ' UTC al ' 2038-01-19 03:14:07 ' UTC. Quindi, ad esempio potrebbe non essere buono per memorizzare le date di nascita.

TIME memorizza solo il tempo, e YEAR memorizza solo l'anno.

Altri

Ci sono molti altri tipi di dati supportati da MySQL. È possibile visualizzare un loro elenco qui. Si dovrebbe anche verificare le dimensioni di archiviazione di ogni tipo di dati qui.

Conclusione

Grazie per la lettura dell'articolo. SQL è un linguaggio importante e uno strumento nell'arsenale gli sviluppatori web.

Si prega di lasciare i vostri commenti e domande e buona giornata.

Pronti a portare le vostre abilità al livello successivo e iniziare a trarre profitto dal vostro script e componenti? Scoprite  la nostra sorella CodeCanyon sulla Piazza di mercato.

CodeCanyon

Advertisement
Advertisement
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.