Share
È importante che ogni sviluppatore web abbia familiarità con le interazioni con il database. Nella seconda parte della serie, continueremo ad esplorare il linguaggio SQL e applicheremo ciò che abbiamo appreso su un database MySQL. Impareremo su indici, tipi di dati e strutture di query più complesse.
Si prega di fare riferimento alla sezione "What You Need" nel primo articolo qui: SQL For Beginners (parte 1).
Se si desidera seguire gli esempi in questo articolo sul proprio server di sviluppo, effettuare le seguenti operazioni:
Gli indici (o le chiavi) vengono utilizzati principalmente per migliorare la velocità delle operazioni di recupero dei dati (ad esempio SELECT) sulle tabelle.
Sono una parte così importante di un buon design del database, è difficile classificarli come "ottimizzazione". Nella maggior parte dei casi sono inclusi nella progettazione iniziale, ma possono anche essere aggiunti in seguito con una query ALTER TABLE.
I motivi più comuni per l'indicizzazione delle colonne del database sono:
Quasi ogni tabella dovrebbe avere una CHIAVE PRIMARIA, nella maggior parte dei casi come INT con l'opzione AUTO_INCREMET.
Se ricordi dal primo articolo, abbiamo creato un campo "user_id" nella tabella degli utenti ed era un PRIMARY KEY. In questo modo, in un'applicazione web possiamo fare riferimento a tutti gli utenti in base al loro numero di identificazione.
I valori memorizzati in una colonna PRIMARY KEY devono essere univoci. Inoltre, non ci può essere più di una PRIMARY KEY su ogni tabella.
Vediamo una query di esempio, creando una tabella per l'elenco degli stati USA:
CREATE TABLE states (id INT AUTO_INCREMENT PRIMARY KEY, nome VARCHAR (20));
Può anche essere scritto in questo modo:
Stati CREATE TABLE (id INT AUTO_INCREMENT, nome VARCHAR (20), PRIMARY KEY (id));
Poiché ci aspettiamo che il nome dello stato sia un valore univoco, dovremmo modificare un po 'l'esempio precedente della query:
Stati CREATE TABLE (id INT AUTO_INCREMENT, nome VARCHAR (20), PRIMARY KEY (id), UNICO (nome));
Per impostazione predefinita, l'indice sarà chiamato dopo il nome della colonna. Se lo desideri, puoi assegnare un nome diverso ad esso:
Stati CREATE TABLE (id INT AUTO_INCREMENT, nome VARCHAR (20), PRIMARY KEY (id), UNIQUE nome_stato (nome));
Ora l'indice è chiamato 'state_name' invece di 'nome'.
Diciamo che vogliamo aggiungere una colonna per rappresentare l'anno in cui ogni stato si è unito.
Stati di CREATE TABLE (id INT AUTO_INCREMENT, nome VARCHAR (20), join_year INT, PRIMARY KEY (id), UNICO (nome), INDICE (join_year));
Ho appena aggiunto la colonna join_year e l'ho indicizzata. Questo tipo di indice non ha la restrizione di unicità.
Puoi anche chiamarlo KEY anziché INDICE.
Stati di CREATE TABLE (id INT AUTO_INCREMENT, nome VARCHAR (20), join_year INT, PRIMARY KEY (id), UNIQUE (nome), KEY (join_year));
L'aggiunta di un indice riduce le prestazioni delle query INSERT e UPDATE. Perché ogni volta che vengono aggiunti nuovi dati alla tabella, anche i dati dell'indice vengono aggiornati automaticamente, il che richiede un lavoro aggiuntivo. I guadagni di prestazioni sulle query SELECT in genere superano di gran lunga questo. Tuttavia, non aggiungere solo indici su ogni singola colonna della tabella senza pensare alle query che verranno eseguite.
Prima di andare oltre con più query, vorrei creare una tabella di esempio con alcuni dati.
Questa sarà una lista di stati degli Stati Uniti, con le loro date di iscrizione (la data in cui lo stato ha ratificato la Costituzione degli Stati Uniti o è stata ammessa nell'Unione) e le loro attuali popolazioni. Puoi copiare incollare quanto segue alla tua console MySQL:
Stati di CREATE TABLE (id INT AUTO_INCREMENT, nome VARCHAR (20), join_year INT, popolazione INT, PRIMARY KEY (id), UNIQUE (nome), KEY (join_year)); INSERIRE INTO VALORI (1, 'Alabama', 1819, 4661900), (2, 'Alaska', 1959, 686293), (3, 'Arizona', 1912, 6500180), (4, 'Arkansas', 1836, 2855390 ), (5, "California", 1850, 36756666), (6, "Colorado", 1876, 4939456), (7, "Connecticut", 1788, 3501252), (8, "Delaware", 1787, 873092), (9, 'Florida', 1845, 18328340), (10, 'Georgia', 1788, 9685744), (11, 'Hawaii', 1959, 1288198), (12, 'Idaho', 1890, 1523816), (13 , "Illinois", 1818, 12901563), (14, "Indiana", 1816, 6376792), (15, "Iowa", 1846, 3002555), (16, "Kansas", 1861, 2802134), (17, " Kentucky ", 1792, 4269245), (18," Louisiana ", 1812, 4410796), (19," Maine ", 1820, 1316456), (20," Maryland ", 1788, 5633597), (21," Massachusetts " , 1788, 6497967), (22, "Michigan", 1837, 10003422), (23, "Minnesota", 1858, 5220393), (24, "Mississippi", 1817, 2938618), (25, "Missouri", 1821 , 5911605), (26, "Montana", 1889, 967440), (27, "Nebraska", 1867, 1783432), (28, "Nevada", 1864, 2600167), (29, "New Hampshire", 1788, 1315809), (30, "New Jersey", 1787, 8682 661), (31, 'New Mexico', 1912, 1984356), (32, 'New York', 1788, 19490297), (33, 'North Carolina', 1789, 9222414), (34, 'North Dakota', 1889, 641481), (35, "Ohio", 1803, 11485910), (36, "Oklahoma", 1907, 3642361), (37, "Oregon", 1859, 3790060), (38, "Pennsylvania", 1787, 12448279), (39, 'Rhode Island', 1790, 1050788), (40, 'South Carolina', 1788, 4479800), (41, 'South Dakota', 1889, 804194), (42, 'Tennessee', 1796 , 6214888), (43, "Texas", 1845, 24326974), (44, "Utah", 1896, 2736424), (45, "Vermont", 1791, 621270), (46, "Virginia", 1788, 7769089 ), (47, "Washington", 1889, 6549224), (48, "West Virginia", 1863, 1814468), (49, "Wisconsin", 1848, 5627967), (50, "Wyoming", 1890, 532668) ;
La clausola GROUP BY raggruppa le righe di dati risultanti in gruppi. Ecco un esempio:
Quindi cos'è successo? Abbiamo 50 righe nella tabella, ma 34 risultati sono stati restituiti da questa query. Questo perché i risultati sono stati raggruppati dalla colonna "join_year". In altre parole, vediamo solo una riga per ogni valore distinto di join_year. Poiché alcuni stati hanno lo stesso join_year, abbiamo ottenuto meno di 50 risultati.
Ad esempio, c'era solo una riga per l'anno 1787, ma ci sono 3 stati in quel gruppo:
Quindi ci sono tre stati qui, ma solo il nome di Delaware è apparso dopo la query GROUP BY in precedenza. In realtà, poteva essere uno dei tre stati e non possiamo fare affidamento su questo dato. Allora a che serve usare la clausola GROUP BY?
Sarebbe in gran parte inutile senza utilizzare una funzione di aggregazione come COUNT (). Vediamo cosa fanno alcune di queste funzioni e come possono procurarci alcuni dati utili.
Questa è forse la funzione più comunemente utilizzata insieme alle query GROUP BY. Restituisce il numero di righe in ciascun gruppo.
Ad esempio possiamo usarlo per vedere il numero di stati per ogni join_year:
Se si utilizza una funzione di aggregazione GROUP BY e non si specifica una clausola GROUP BY, l'intero risultato verrà inserito in un singolo gruppo.
Numero di tutte le righe nella tabella:
Numero di righe che soddisfano una clausola WHERE:
Queste funzioni restituiscono i valori minimo, massimo e medio:
Questa funzione concatena tutti i valori all'interno del gruppo in una singola stringa, con un dato separatore.
Nel primo esempio di query GROUP BY, è stato possibile visualizzare solo un nome di stato all'anno. Puoi usare questa funzione per vedere tutti i nomi di ogni gruppo:
Se l'immagine ridimensionata è difficile da leggere, questa è la query:
SELECT GROUP_CONCAT (name SEPARATOR ','), join_year FROM states GROUP BY join_year;
Puoi usare questo per sommare i valori numerici.
Simile ad altri linguaggi di programmazione, SQL ha un certo supporto per il controllo del flusso.
Questa è una funzione che accetta tre argomenti. Il primo argomento è la condizione, il secondo argomento è usato se la condizione è vera e il terzo argomento è usato se la condizione è falsa.
Ecco un esempio più pratico in cui viene utilizzato con la funzione SUM ():
SELECT SUM (IF (popolazione> 5000000, 1, 0)) AS big_states, SUM (IF (popolazione <= 5000000, 1, 0) ) AS small_states FROM states;
La prima chiamata SUM () conta il numero di grandi stati (popolazione oltre 5 milioni) e la seconda conta il numero di stati piccoli. La chiamata IF () all'interno di queste chiamate SUM () restituisce 1 o 0 in base alla condizione.
Ecco il risultato:
Funziona in modo simile alle istruzioni switch-case che potresti avere familiarità con la programmazione.
Diciamo che vogliamo classificare ogni stato in una delle tre possibili categorie.
SELECT COUNT (*), CASE WHEN population> 5000000 THEN 'big' WHEN population> 1000000 THEN 'medium' ELSE 'small' END AS state_size FROM stati GROUP BY state_size;
Come puoi vedere, possiamo effettivamente GROUP BY il valore restituito dall'istruzione CASE. Ecco cosa succede:
La clausola HAVING ci consente di applicare le condizioni ai campi "nascosti", come i risultati restituiti delle funzioni di aggregazione. Di solito viene utilizzato insieme a GROUP BY.
Ad esempio, esaminiamo la query che abbiamo utilizzato per il conteggio del numero di stati per anno di join:
SELECT COUNT (*), join_year FROM stati GROUP BY join_year;
Il risultato era 34 righe.
Tuttavia, diciamo che siamo interessati solo alle righe con un conteggio superiore a 1. Non possiamo utilizzare la clausola WHERE per questo:
È qui che HAVING diventa utile:
Tieni presente che questa funzione potrebbe non essere disponibile in tutti i sistemi di database.
È possibile ottenere i risultati di una query e utilizzarla per un'altra query.
In questo esempio, otterremo lo stato con la popolazione più alta:
SELECT * FROM states WHERE population = (SELECT MAX (popolazione) FROM stati);
La query interna restituirà la popolazione più alta di tutti gli stati. E la query esterna cercherà nuovamente la tabella usando quel valore.
Potresti pensare che questo sia stato un cattivo esempio e sono un po 'd'accordo. La stessa query potrebbe essere scritta in modo più efficiente in questo modo:
SELECT * FROM states ORDER BY population DESC LIMIT 1;
I risultati in questo caso sono gli stessi, tuttavia c'è una differenza importante tra questi due tipi di query. Forse un altro esempio dimostrerà che meglio.
In questo esempio, otterremo gli ultimi stati che hanno aderito all'Unione:
SELECT * FROM states WHERE join_year = (SELECT MAX (join_year) FROM stati);
Ci sono due righe nei risultati questa volta. Se avessimo utilizzato il tipo di query ORDER BY ... LIMIT 1 qui, non avremmo ricevuto lo stesso risultato.
A volte potresti voler utilizzare più risultati restituiti dalla query interna.
La seguente query trova gli anni in cui più stati entrano nell'Unione e restituisce l'elenco di tali stati:
SELECT * FROM states WHERE join_year IN (SELECT join_year FROM stati GROUP BY join_year HAVING COUNT (*)> 1) ORDINE BY join_year;
Le sottoquery possono diventare piuttosto complesse, quindi non approfondirò ulteriormente la questione in questo articolo. Se vuoi saperne di più su di loro, consulta il manuale MySQL.
Inoltre vale la pena notare che a volte le sottoquery possono avere prestazioni non buone, quindi dovrebbero essere utilizzate con cautela.
Con una query UNION, possiamo combinare i risultati di più query SELECT.
Questo esempio combina stati che iniziano con la lettera "N" e indica con grandi popolazioni:
(SELEZIONA * FROM stati WHERE nome LIKE 'n%') UNION (SELECT * FROM states WHERE population> 10000000);
Notare che New York è sia grande che il suo nome inizia con la lettera "N". Ma si presenta solo una volta perché le righe duplicate vengono rimosse automaticamente dai risultati.
Un'altra cosa bella di UNION è che puoi combinare le query su tabelle diverse.
Supponiamo di avere tabelle per dipendenti, manager e clienti. E ogni tabella ha un campo di posta elettronica. Se vogliamo recuperare tutte le e-mail con una singola query, possiamo eseguire questo:
(SELEZIONA email dai dipendenti) UNIONE (SELEZIONA email da gestori) UNIONE (SELEZIONA email da parte dei clienti DOVE sottoscritto = 1);
Raccoglierebbe tutte le email di tutti i dipendenti e manager, ma solo le e-mail dei clienti che si sono iscritti per ricevere e-mail.
Abbiamo già parlato della query INSERT nell'ultimo articolo. Ora che abbiamo esplorato gli indici dei database oggi, possiamo parlare delle funzionalità più avanzate della query INSERT.
Questo è quasi come un'affermazione condizionale. La query tenta prima di eseguire un determinato INSERT e, se fallisce a causa di un valore duplicato per un PRIMARY KEY o UNQUE KEY, esegue invece un UPDATE.
Creiamo prima un tavolo di prova.
È un tavolo per contenere i prodotti. La colonna 'magazzino' è il numero di prodotti che abbiamo in magazzino.
Ora prova ad inserire un valore duplicato e guarda cosa succede.
Abbiamo ricevuto un errore come previsto.
Diciamo che abbiamo ricevuto un nuovo breadmaker e vogliamo aggiornare il database e non sappiamo se esiste già un record per questo. Potremmo controllare i record esistenti e poi fare un'altra query basata su quello. Oppure potremmo fare tutto ciò in una semplice query:
Funziona esattamente come INSERT con un'eccezione importante. Se viene trovata una riga duplicata, la elimina prima e quindi esegue l'INSERT, quindi non riceviamo messaggi di errore.
Si noti che poiché questa è in realtà una riga interamente nuova, l'ID è stato incrementato.
Questo è un modo per sopprimere gli errori duplicati, in genere per impedire l'interruzione dell'applicazione. A volte potresti voler provare ad inserire una nuova riga e lasciarla fallire senza lamentele nel caso in cui sia stato trovato un duplicato.
Nessun errore restituito e nessuna riga è stata aggiornata.
Ogni colonna della tabella deve avere un tipo di dati. Finora abbiamo utilizzato i tipi INT, VARCHAR e DATE ma non ne abbiamo parlato in dettaglio. Inoltre ci sono molti altri tipi di dati che dovremmo esplorare.
Innanzitutto, iniziamo con i tipi di dati numerici. Mi piace metterli in due gruppi separati: interi e non interi.
Una colonna intera può contenere solo numeri naturali (senza decimali). Di default possono essere numeri negativi o positivi. Ma se l'opzione UNSIGNED è impostata, può contenere solo numeri positivi.
MySQL supporta 5 tipi di numeri interi, con varie dimensioni e intervalli:
Questi tipi di dati possono contenere numeri decimali: FLOAT, DOUBLE e DECIMAL.
FLOAT è 4 byte, DOUBLE è 8 byte e funzionano in modo simile. Tuttavia, DOUBLE ha una precisione migliore.
DECIMAL (M, N) ha una dimensione variabile in base al livello di precisione, che può essere personalizzato. M è il numero massimo di cifre e N è il numero di cifre a destra del punto decimale.
Ad esempio, DECIMAL (13,4) ha un massimo di 9 cifre intere e 4 cifre frazionarie.
Come suggerisce il nome, possiamo memorizzare stringhe in queste colonne di tipo di dati.
CHAR (N) può contenere fino a N caratteri e ha una dimensione fissa. Ad esempio, CHAR (50) avrà sempre 50 caratteri di spazio, per riga, indipendentemente dalla dimensione della stringa in essa contenuta. Il massimo assoluto è 255 caratteri
VARCHAR (N) funziona allo stesso modo, ma la dimensione di archiviazione non è fissa. N è usato solo per le dimensioni massime. Se una stringa più breve di N caratteri è memorizzata, ci vorrà molto meno spazio sul disco rigido. La dimensione massima assoluta è 65535 caratteri.
Le variazioni del tipo di dati TEXT sono più adatte per le stringhe lunghe. TEXT ha un limite di 65535 caratteri, MEDIUMTEXT 16,7 milioni di caratteri e LONGTEXT 4,3 miliardi di caratteri. MySQL solitamente li memorizza su posizioni separate sul server in modo che la memoria principale per la tabella rimanga relativamente piccola e veloce.
DATE memorizza le date e le visualizza in questo formato 'AAAA-MM-GG' ma non contiene le informazioni sull'ora. Ha un intervallo compreso tra 1001-01-01 e 9999-12-31.
DATETIME contiene sia la data che l'ora e viene visualizzato in questo formato 'AAAA-MM-GG HH: MM: SS'. Ha un intervallo 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 alcune eccezioni. Sono necessari solo 4 byte di spazio e l'intervallo è '1970-01-01 00:00:01' UTC su '2038-01-19 03:14:07' UTC. Quindi, ad esempio, potrebbe non essere utile per memorizzare le date di nascita.
TIME memorizza solo l'ora e YEAR memorizza solo l'anno.
Esistono altri tipi di dati supportati da MySQL. Puoi vedere un elenco di loro qui. Dovresti anche controllare le dimensioni di archiviazione di ciascun tipo di dati qui.
Grazie per aver letto l'articolo SQL è un linguaggio importante e uno strumento nell'arsenale degli sviluppatori web.
Si prega di lasciare i vostri commenti e domande, e buona giornata!
Sei pronto a portare le tue abilità a un livello superiore e a trarre profitto dagli script e dai componenti? Dai un'occhiata al nostro marketplace, CodeCanyon.
Accentsconagua