I comandi Linux: MySQL ( 4 ) | ALTRI capitoli | |||||||||||
Gli statement SQL sono i comandi SQL, attraverso i quali è possibile gestire un database, creando ed eliminando tabelle, inserendo nuovi record in una tabella, aggiornando record già esistenti, estraendo dati da una singola tabella oppure da più tabelle.
Per avere informazioni sui singoli statement SQL, è possibile riferirsi alla guida MySQL 5.5 Reference Manual, oppure, una volta aperto il client
Il comando help è un comando del client mysql, non uno statement SQL. Lo statement SQL:
comprende una serie di comandi per ottenere informazioni preziose su database, tabelle, colonne, server MySQL. Per esempio, una volta creato un nuovo database, con lo statement SQL:
sarà possibile verificare la query inviata al server MySQL, al momento della creazione del database, utilizzando il comando SHOW:
Due degli statement più utilizzati, con SHOW, sono gli statement che elencano gli errori e gli warning ( avvisi ), generati dal motore MySQL nel corso della sessione corrente:
Questo comando riporta l'avviso generato dall'ultimo statement eseguito ( sia esso un errore, un semplice avviso, una nota ). Se l'ultimo statement eseguito non ha generato alcun messaggio di avviso, questo comando non restituisce nulla. Il comando:
invece, mostra solo i messaggi di errore. I comandi strettamente connessi con questi due ultimi sono:
che stampano a video, rispettivamente, il numero degli avvisi e dei messagi di errore, generati nel corso della sessione corrente. Questi due numeri sono memorizzati nelle due variabili:
Per leggerne i valori, utilizzare gli statement SELECT:
Al momento della creazione di un database, ricordate di selezionare, come CHARACTER SET e COLLATE, il set di caratteri UTF8, un set di caratteri universale, che mai darà problemi, a voi o ai vostri utenti, soprattutto internet, di decodifica dei caratteri di testo:
Per avere l'elenco dei set di caratteri e delle collazioni disponibili, eseguire i due comandi SQL:
Entrambi i comandi permettono l'uso dell'operatore LIKE, per selezionare una porzione di output. Per verificare quale set di caratteri siano utilizzati dal client e dal server, eseguire:
Questi due comandi stamperanno a video una serie di variabili MySQL:
esprime il set di caratteri con cui i comandi MySQL ( statement ) partono dal client.
esprimono il set di caratteri e la collazione che il server dovrà utilizzare, quando riceve uno statement dal client: se necessario, il server eseguirà una conversione.
esprime il set di caratteri che il server MySQL utilizzerà, per inviare, al client, i dati di risposta o i messaggi di errore. Le variabili:
esprimono i set di caratteri e le collazioni utilizzate dal server e dal database. Per modificare i valori di queste variabili, utilizzare:
che è equivalente ai comandi:
E' possibile specificare una collazione particolare:
Il comando:
equivale ai comandi:
Il solo account che possa creare database, solitamente, è l'account "root". Sarebbe preferibile creare nuovi database, per poi assegnarne la gestione ad un utente con privilegi limitati. Se non avete ancora creato alcun utente, a cui assegnare un nuovo database, eseguite, come super user ( root ):
Un nuovo utente, di default, viene creato senza l'assegnazione di alcun privilegio. Per assegnare, al nuovo utente, i privilegi necessari alla gestione del nuovo database:
Ricordate che il comando GRANT può essere eseguito anche per database non ancora esistenti. Tutto ciò che viene creato, naturalmente, potrà essere eliminato oppure modificato, in qualsiasi momento:
A questo punto, il server MySQL avrà un utente in più, "paolo", nel nostro esempio, ed un database in più, "DbPaolo", nel nostro esempio. Per costruire il nuovo database, quindi, potremo accedere al server MySQL, in qualità di utente locale, "paolo", nel nostro esempio, aprire il nuovo database, "DbPaolo", nel nostro esempio:
e crearne le tabelle:
La clausola DEFAULT specifica un valore di default per la colonna. Con una sola eccezione, il valore di DEFAULT deve essere rappresentato da una costante; il valore di DEFAULT, quindi, non può contenere una funzione o un'espressione. Non è possibile, quindi, impostare, per una colonna che contenga un tipo di dato DATE ( AAAA-MM-GG ), un valore di DEFAULT rappresentato da una funzione temporale, quale: NOW() o CURRENT_DATE. La sola eccezione è rappresentata dalla funzione CURRENT_TIMESTAMP, che può essere impostata come funzione di DEFAULT per una colonna di tipo TIMESTAMP:
Con questa clausola di DEFAULT, alla colonna viene assegnato il valore CURRENT_TIMESTAMP, il quale valore, però, non viene aggiornato, automaticamente, ad ogni modifica del record ( UPDATE ). Per far sì che il valore del campo venga aggiornato automaticamente ad ogni UPDATE, è necessario aggiungere un CONSTRAINT ON UPDATE alla colonna TIMESTAMP con il valore di default impostato su CURRENT_TIMESTAMP:
oppure, assegnare, in caso di comando INSERT o UPDATE, al campo un valore NULL, sempre che il campo sia di tipo NOT NULL:
Per duplicare una tabella esistente, utilizzare i due comandi CREATE TABLE e SELECT:
Per duplicare solo la struttura di una tabella, senza i dati, utilizzare l'operatore LIKE:
Una tabella può essere modificata, oppure eliminata:
Da una tabella, è possibile estrarre dati, selezionando i record, i campi ( colonne ) e le tabelle dalle quali estrarre i dati:
Ciascuno di questi comandi, restituisce una porzione, più o meno completa, di due tabelle:
Ciascuno dei tre risultati verrà stampato a video, come una sequenza di record che, nel secondo e terzo caso, non coinciderà con l'intero contenuto fisico delle due tabelle. Ciascuno dei tre risultati andrà a comporre un diverso oggetto del database, la cosiddetta VIEW ( vista ). Una VIEW ha l'apparenza di una normale tabella, ma non è una tabella: è semplicemente una query, di tipo SELECT, memorizzata nel database. Se a ciascuna tabella del database corrisponde un file reale, ad una VIEW non corrisponde alcun file, perchè una VIEW non è una serie di record, memorizzati fisicamente, ma una selezione di campi, record e tabelle preesistenti ed ordinate per uno specifico impiego. Una VIEW è un oggetto del database, al pari di una tabella reale. Come una tabella reale, una VIEW può essere creata, modificata ed eliminata:
DEFINER è l'utente al quale associare il comando CREATE VIEW. Può essere definito con il nome utente, oppure specificato come 'CURRENT_USER'. La verifica dei privilegi di accesso (SQL SECURITY) è da effettuarsi sul DEFINER, oppure sull'INVOKER, l'utente che ha invocato la VIEW. Una VIEW può essere utilizzata come fosse una tabella reale:
con alcune piccole limitazioni, pur non occupando alcuno spazio fisico sul disco fisso, se non quello necessario a salvare la query SELECT che l'ha generata. Per verificare la query inviata al server MySQL, al momento della creazione di una VIEW, utilizzare il comando SHOW:
Al fine di velocizzare le operazioni di ricerca e di recupero dei record, MySQL offre la possibilità di creare tabelle indice, per ciascuna tabella di dati esistente:
Una tabella indice deve essere usata per segnalare a MySQL quali colonne di una tabella di dati verranno utilizzate più frequentemente nelle clausole WHERE, all'interno di uno statement SELECT, in modo che ad ogni campo di ciascuna colonna possa essere affiancato il punto esatto della tabella in cui trovarlo. Non appena viene creato un indice per una tabella, infatti, i dati contenuti nella tabella vengono ordinati e numerati. Per esempio, per una ipotetica tabella
Per facilitare il compito di estrazione dei dati, MySQL permette di creare la tabella indice per la tabella "Warehouse":
In questo esempio, abbiamo creato un indice, a partire dalla colonna WAREHOUSE. La tabella indice, quindi, apparirà come segue:
Da questo momento in poi, ogni volta in cui MySQL incontrasse una query come la seguente:
andrebbe immediatamente a consultare l'indice della tabella "Warehouse", e non la tabella dati: in questo modo, troverebbe immediatamente la posizione del record al quale appartiene il campo "Dallas", nella colonna WAREHOUSE, all'interno della tabella "Warehouse", senza dover necessariamente attraversare tutti i record della tabella dati ( "Warehouse" ). Una tabella indice è una vera e propria tabella, che occupa spazio su disco, a volte molto più spazio di quanto ne occupi la tabella dati di origine. Se la creazione di una tabella indice può velocizzare la ricerca e l'acquisizione di un gruppo di dati, è molto probabile che comporterà, come contraltare, una minore velocità di esecuzione di comandi quali UPDATE, INSERT, DELETE, visto che, oltre ad aggiornare la tabella dati, dovrà essere aggiornata, anche, la tabella degli indici. Questo è uno dei motivi per i quali l'utilizzo degli indici è consigliabile solo nei casi in cui la tabella fosse composta da migliaia e migliaia di record. MySQL crea automaticamente gli indici relativi alle colonne PRIMARY KEY e UNIQUE KEY. Un indice, come un qualsiasi altro oggetto MySQL, può essere eliminato e/o modificato:
Per modificare un indice, si utilizzi lo statement:
Per conoscere gli indici presenti per una particolare tabella, utilizzare il comando:
Per inserire, eliminare e aggiornare dati, contenuti in una tabella, MySQL rende disponibili i comandi:
I comandi INSERT e UPDATE permettono di inserire nuovi dati in uno o più campi di una tabella. È bene tenere a mente che alcuni caratteri vengono interpretati in un modo speciale da MySQL: nel caso questi caratteri dovessero far parte di una stringa di testo da memorizzare, sarà necessario avvisare MySQL di non trattarli come caratteri speciali. Per esempio, il carattere
In determinate circostanze può capitare di avere la necessità di effettuare copie di interi record o di loro porzioni, da un database ad un altro:
In questo esempio, stiamo creando un record nella tabella BASI del database corrente (quello dal quale inviamo il comando), copiandolo interamente dal record (COD='GS064') già presente nella tabella BASI del database DB1. Per copiare solo alcuni campi del database di origine, creando nuovi valori per i campi restanti, è sufficiente specificare ciascun campo della tabella:
In questo esempio, abbiamo creato nuovi valori per i campi COD e CAT, mentre abbiamo copiato tutti gli altri campi. Attenzione: con gli statement UPDATE e DELETE, omettere la clausola WHERE, volontariamente o inconsapevolmente, significa aggiornare oppure eliminare l'intera tabella. Per esempio, lo statement:
restituirebbe una tabella "Warehouse", del nostro esempio precedente, con i campi WAREHOUSE-ADDRESS contenenti, tutti, il valore "Via Ponte 34":
Siete certi che fosse quello che volevate? Molto probabilmente, la vostra reale intenzione era di modificare solo l'indirizzo del magazzino di Roma. La vostra query, quindi, avrebbe dovuto essere la seguente:
Non credo che ci sia qualcuno, al mondo, che, almeno una volta nella vita, non abbia fatto un errore del genere. Se non aveste alcuna copia di backup, della vostra tabella, in un caso del genere dovreste riscriverla completamente. MySQL mette a disposizione la variabile d'ambiente:
che, se opportunamente impostata:
impedisce a MySQL di aggiornare una tabella ( UPDATE ) o eliminarne i record ( DELETE ), se la query non contiene una clausola WHERE, oppure una clausola LIMIT. Un utente diverso dall'amministratore, " root ", potrà modificare il valore della variabile per la sola propria sessione corrente. In questo caso, i seguenti comandi sarebbero, quindi, equivalenti:
Per rendere la variazione disponibile a tutti gli utenti, fino alla chiusura del server MySQL, è sufficiente specificare il tipo di variabile come GLOBAL:
ma, per eseguire uno di questi ultimi due comandi, sono necessari i privilegi di amministratore ( root ). La variazione del valore GLOBAL di una variabile d'ambiente non avrà alcun effetto sulle variabili di sessione degli utenti già connessi al sistema, compreso l'utente che ha eseguito il comando SET. Per rendere permamente la variazione, per le sessioni future di tutti gli utenti e del server MySQL, è necessario modificare un file di configurazione di MySQL, quale, per esempio, il file di configurazione utente, che si trova nella HOME dell'utente stesso:
Attualmente, la variabile:
non è direttamente configurabile nel file
che, come specificato dalla pagina di manuale MySQL mysql Options, può essere usata nel file di configurazione, per i comandi:
Per esempio, per far sì che, ogni volta in cui l'utente lancerà il client
Per conoscere il valore delle variabili MySQL, eseguire la query:
In quest'ultimo esempio, l'operatore LIKE ci permette di specificare la variabile ricercata. Il comando ( statement ) SELECT permette di interrogare il database e di estrarre i record che rispondano ad un qualche criterio:
Il comando SELECT, quindi, non modifica, in alcun modo, una tabella, bensì effettua una ricerca all'interno di essa. SELECT può selezionare tutti i record ( righe ) di una tabella:
oppure, grazie alla clausola WHERE, selezionare una serie specifica di record ( righe ), che soddisfino una qualche condizione:
oppure, selezionare solo una o più colonne ( campi ) specifiche:
oppure, selezionare una o più colonne specifiche ed eseguire operazioni sui valori contenuti in ciascuna colonna, per poi riportarne i il risultato in una nuova colonna virtuale:
In questo esempio, stiamo selezionando i campi CLIENTE, MQ, PREZZO, dalla tabella CASE, creando una nuova colonna virtuale, chiamata UNITARIO, in cui apparirà il prodotto della divisione eseguita tra il valore contenuto nel campo PREZZO ed il valore contenuto nel campo MQ. Il risultato sarà una tabella virtuale come la seguente:
La clausola WHERE supporta più operatori di comparazione:
In sequenza: maggiore di, maggiore o uguale a, minore di, minore o uguale a, diverso da, è NULL, non è NULL, compreso tra 6 ed 8 ( inclusi gli estremi: 6 e 8 ), non compreso tra 6 ed 8 ( inclusi gli estremi: 6 e 8 ), esiste in ( TRUE se la subquery restituisce almeno un risultato, anche se si trattasse di un solo record, con tutti i campi impostati a NULL ), non esiste in ( TRUE se la subquery non restituisce alcun risultato ), uguale ad uno dei valori compresi dall'operatore IN. Gli ultimi due esempi sono equivalenti alle espressioni:
Il confronto viene eseguito su ogni membro del gruppo, utilizzando l'operatore di uguaglianza. Gli operatori:
sono equivalenti. Una subquery restituisce TRUE se almeno uno dei valori restituiti dalla subquery soddisfa la condizione di ricerca:
L'operatore IN è il solo, dei tre, che accetta una lista di parametri, come abbiamo visto in esempi precedenti. L'operatore ALL:
restitusce TRUE solo quando tutti i valori restituiti dalla subquery soddisfano la condizione di ricerca ( in questo caso, sono minori di
L'operatore LIKE cerca le stringhe testuali che contengano il pattern specificato. L'operatore NOT LIKE cerca le stringhe testuali che non contengano il pattern specificato. Nel nostro esempio, stiamo cercando i record il cui campo NAME inizi con la stringa "andrea" e sia seguita o da nulla oppure da altri caratteri. La stringa pattern di LIKE deve essere compresa tra virgolette semplici e può contenere il carattere Jolly (
La ricerca verrà effettuata senza preoccuparsi delle lettere maiuscole e minuscole ( case insensitive ). È possibile limitare il numero delle righe restituite da una query SELECT, utilizzando l'operatore LIMIT. L'operatore LIMIT accetta uno o due parametri numerici, di tipo INTEGER non negativo. Quando i parametri sono due, come nel caso della seguente query:
il primo argomento specifica lo scostamento ( offset ) della prima riga ( o record ) da restituire ( 20, nel nostro caso ), mentre il secondo specifica il numero massimo di righe ( o record ) da restituire ( 30, nel nostro caso ). Lo scostamento ( offset ) della prima riga ( o record ) è zero ( non 1 ). Nel nostro esempio, quindi, le righe restituite saranno le righe dalla ventunesima alla cinquantesima. Quando alla clausola LIMIT viene dato un solo argomento o parametro, il valore specifica il numero di righe da restituire, a partire dalla riga zero:
È possibile ordinare i record restituiti dallo statement SELECT, grazie alla clausola ORDER BY:
È possibile ordinare i record restituiti, prendendo come riferimento anche più colonne, come accade in quest'ultimo esempio. Le colonne da utilizzare per l'ordinamento possono essere indicate con il nome ( come nel nostro caso ), compreso l'eventuale alias, oppure con il numero della posizione occupata dalla colonna:
I risultati, in presenza di una clausola ORDER BY, vengono ordinati in ordine crescente:
ma possono essere ordinati in ordine decrescente:
Il comando SELECT, di default, restituisce un numero massimo di righe. Per conoscere questo limite, verificare il valore assegnato, nel corso della sessione, alla variabile:
In questo caso, il comando SELECT non restituirà mai più di 1000 record, per ciascuna interrogazione. Per modificare questo valore, è possibile, per la sola sessione corrente, utilizzare il comando SET, dall'ambiente interattivo del client MySQL:
Non è possibile specificare il valore di questa variabile all'interno di un file di configurazione. Quando l'opzione:
è attiva, è possibile eseguire il client MySQL con l'opzione:
oppure modificare il file di configurazione o globale o utente:
In inglese, JOIN significa: unire. L'operatore MySQL JOIN unisce due o più tabelle. Prendiamo, come esempio, due tabelle viste in precedenza:
Per eseguire un SELECT su entrambe le tabelle, basterà eseguire uno dei due comandi seguenti ( che sono equivalenti ):
Il risultato di entrambi i comandi è il prodotto cartesiano delle due tabelle, che e' una super tabella che contiene tutte le possibili combinazioni delle righe delle due tabelle:
Quando si esegue un JOIN tra una tabella con N record ( righe ) ed un'altra tabella con M record ( righe ), il risultato sarà una tabella con un numero di record pari al prodotto dei due valori:
Se una tabella contenesse, quindi, 3 milioni di record e l'altra ne contenesse 4 milioni, il risultato sarebbe una tabella con 12.000.000.000.000 di record! Oltre ad essere un risultato difficilmente gestibile, sarebbe, anche, un risultato in gran parte inutilizzabile, visto che l'operazione di JOIN affiancherebbe coppie di record che non hanno alcuna attinenza tra loro. Nel nostro esempio, le due sole coppie di record che avrebbero un senso, sono:
poichè sono legate da un vincolo dichiarato in fase di creazione della tabella: come abbiamo già visto, nella trattazione della normalizzazione, il campo:
è una chiave esterna ( foreign key ) che punta al campo primario ( Primary Key ) di un'altra tabella:
L'interrogazione che restituisce in pieno questo rapporto, quindi, è la seguente:
oppure la seguente ( equivalente ):
Due tabelle unite con un JOIN non sono fisicamente collegate: la macro tabella che risulta dal JOIN è una macro tabella virtuale. Un JOIN non ha effetto fisico sulla struttura del database e non lo cambia in alcun modo. Per eseguire un JOIN tra più di due tabelle, la sintassi è molto simile a quella già vista:
La normalizzazione tende a distribuire i dati su molte piccole tabelle. Queste tabelle devono avere una colonna in comune ( FOREIGN KEY => PRIMARY KEY ), tramite la quale si stabilisce la corrispondenza tra righe dell'una e righe dell'altra. La ricerca dell'uguaglianza viene eseguita grazie alla clausola WHERE.
Grazie alle subquery, è possibile interrogare una tabella, restringendo la ricerca in base alle informazioni contenute in un'altra tabella. Un esempio, tratto da SQL for Web Nerds, di Philip Greenspun:
Una subquery è sempre contenuta tra due parentesi e può essere o annidata in una query esterna ( principale ), oppure correlata ad una query esterna ( principale ). Nel meccanismo delle subquery correlate, la query esterna viene eseguita per prima e la subquery viene eseguita su tutte le righe che le vengono passate dalla query esterna. Una subquery si dice correlata, infatti, quando nella sua clausola WHERE contiene un riferimento ad una tabella presente anche nella query esterna. Nel nostro esempio, la query principale è:
che chiede a MySQL di estrarre, dalla tabella "
chiede a MySQL di limitare i record restituiti dalla interrogazione principale ai soli record che abbiano almeno un record corrispondente nella tabella "
che utilizza la funzione EXISTS, al posto della funzione COUNT. La funzione EXISTS restituisce il valore TRUE ( 1 ) se la subquery successiva restituisce almeno un record. La funzione COUNT, invece, conta i record restituiti dallo statement SELECT, risultando, almeno per lo scopo del nostro esempio, troppo dispendioso, in termini di risorse e tempo impiegati. Una subquery annidata viene eseguita una sola volta prima della query esterna e passa ad essa il risultato:
In questo esempio, la subquery restituisce una lista di record, dalla tabella "
In questo esempio, la subquery estrae, dalla tabella
Il costruttore è rappresentato, nel nostro esempio, dalla lista dei campi ( colonne ) dichiarate nella clausola WHERE:
e può essere dichiarato esplicitamente:
Il costruttore può essere invocato solo in presenza di subquery che restituiscano più di un campo. Se la comparazione viene fatta su più campi di una stessa colonna, la subquery potrà restituire un array di valori. Il risultato finale dipenderà, quindi, dall'operatore utilizzato:
Se la comparazione viene fatta su un'intera tabella, sarà necessario utilizzare gli operatori EXISTS e NOT EXISTS.
Se una subquery restituisce un qualsiasi record ( anche se composto da soli campi NULL ), EXISTS restituisce TRUE, mentre NOT EXISTS restituisce FALSE. Naturalmente, è possibile eseguire subquery EXISTS correlate:
In questo esempio, MySQL restituirà i campi
Grazie alle subquery, è possibile eseguire degli statement INSERT, prelevando i dati da una o più tabelle esterne. La sintassi del comando INSERT, in questo caso, è leggermente diversa dal solito:
Nella sua forma più semplice, questo statement può essere utilizzato per copiare tutti i record di una tabella in una tabella differente:
In questo esempio, una volta eseguito lo statement INSERT, la tabella ClientNew conterrà tutti i record presenti nella tabella Clients. Naturalmente, è anche possibile selezionare i record da copiare nella nuova tabella:
In questo esempio, una volta eseguito lo statement INSERT, la tabella ClientNew conterrà, della tabella Clients, solo i record dei clienti residenti a Bologna. È possibile, anche, selezionare i singoli campi di un record da copiare nella nuova tabella:
Questa query ci dice che esiste una tabella di nome
Il comando INSERT inserisce, nella tabella
ed un terzo campo, al quale viene assegnato un valore letterale fisso:
Nella query, inoltre, viene specificato, per ciascuno dei valori inseriti, il campo che andrà ad occupare nella tabella di destinazione:
|
||||||||||||
I comandi Linux: MySQL ( 4 ) | Le guide di .bit: contenuto originale |