SQL e Excel - Guida Alla Gestion - Marco Ferrero [PDF]

Zitiervorschau

SQL E EXCEL GUIDA ALLA GESTIONE DEI DATI TRA DATABASE E FOGLI DI CALCOLO

Marco Ferrero

© Apogeo - IF - Idee editoriali Feltrinelli s.r.l. Socio Unico Giangiacomo Feltrinelli Editore s.r.l.

ISBN edizione cartacea: 9788850333745

Il presente file può essere usato esclusivamente per finalità di carattere personale. Tutti i contenuti sono protetti dalla Legge sul diritto d’autore. Nomi e marchi citati nel testo sono generalmente depositati o registrati dalle rispettive case produttrici. L’edizione cartacea è in vendita nelle migliori librerie. ~ Sito web: www.apogeonline.com Scopri le novità di Apogeo su Facebook Seguici su Twitter @apogeonline Collegati con noi su LinkedIn Rimani aggiornato iscrivendoti alla nostra newsletter

Dedicato a Laura, Marco, Federico, Martina, Lorenzo e alle loro splendide mamme.

Introduzione



Presentazione Lo strumento di lavoro più diffuso negli uffici di tutto il mondo è un foglio rettangolare di carta a quadretti, disposto in orizzontale, cioè con il lato lungo in basso. Su questo foglio si scrivono numeri che rappresentano di solito realtà economiche: quantità vendute o prodotte, numero di pezzi in magazzino, costi e prezzi unitari, associando i numeri a una descrizione, come vediamo qui di seguito.

Gli americani, che hanno un nome per tutte le cose, chiamano questo foglio spreadsheet, che vuol dire letteralmente “foglio dispiegato” e se ne servono egregiamente, con la convinta determinazione che hanno di solito quando lavorano. Qui da noi non ha un nome ufficiale o comunemente accettato, possiamo chiamarlo foglio di lavoro, tanto per intenderci. Pur non avendo un nome, è comunque usato parecchio anche in Italia, in tutte le innumerevoli situazioni di lavoro in cui occorre presentare sinteticamente un insieme di informazioni quantitative. Le linee verticali del foglio tracciano colonne e quelle orizzontali righe. Nel punto in cui si intersecano righe e colonne si formano celle, nelle quali si scrivono numeri o descrizioni, come nell’esempio che stiamo vedendo.

Il foglio di lavoro o spreadsheet che dir si voglia è indubbiamente un ottimo strumento per raccogliere dati, ma crea non pochi fastidi quando si sbaglia a inserire un dato in una cella o quando occorre modificare qualcosa. Prendiamo l’esempio che abbiamo davanti. Se nel Magazz01 la giacenza di rondelle di diametro 5 passa da 52.000 pezzi a 50.000, il totale in fondo alla riga di queste rondelle non è più 171.000, ma 169.000. E il totale generale per la colonna del Magazz01 scende a 117.000, mentre si riduce – sempre di 2.000 unità – la colonna dei totali generali (da 472.000 passa a 470.000). Per decenni contabili, analisti di gestione, magazzinieri e altri ancora hanno accettato questo inconveniente in cambio dei grandi vantaggi di chiarezza che offre il foglio di lavoro. L’unico accorgimento possibile per non soffrire troppo consisteva nell’usare sempre fogli di carta robusta, scrivere i numeri soltanto con la matita e tenere a portata di mano una gomma per cancellare. Non è un caso che negli Stati Uniti, dove si fa un uso smodato degli spreadsheet, non si trovino in commercio matite di legno senza il gommino fissato a un’estremità.

Tra cronaca e storia Nel 1978 due giovanotti iscritti al secondo anno del corso per il Master of Business Administration della Harvard Business School si chiesero se non sarebbe stato possibile trovare una soluzione non cartacea e non manuale al problema della compilazione dei fogli di lavoro. Studiare per conseguire l’MBA alla Harvard Business School vuol dire passare intere giornate (e spesso nottate) a riempire decine di fogli di lavoro, trascrivendovi stati patrimoniali, conti economici, schemi di budget, rendiconti di costi e ricavi, per analizzare e capire gli aspetti quantitativi della gestione delle imprese. Da poco più di un anno era sul mercato un trabiccolo formato da un basamento di lamiera che conteneva un po’ di circuiti integrati e nel quale era ricavato l’alloggiamento per una tastiera; sul basamento si appoggiava un monitor monocromatico (di quelli che si usavano per gli oscilloscopi) e il tutto veniva presentato col nome di Apple II: era uno dei primissimi personal computer (ancora non avevano questo nome) nati dalla fertile inventiva di alcuni giovanotti californiani. I nostri amici, che si chiamavano Dan Bricklin e Bob Frankston, stufi marci di mettere giù i numeri a matita, di fare i calcoli a mano e di cancellare e ricancellare i dati parziali per farli quadrare con i totali (o viceversa), decisero di investire la cospicua somma di 1000 dollari e di provare a fare su Apple II un programma che servisse a compilare i fogli di lavoro. Dopo pochi mesi, nella primavera del 1979, il risultato tanto atteso prese forma, gli venne dato il nome di VisiCalc e fu un trionfo. La voce passò fulmineamente di bocca in bocca (l’Harvard Business School è una immensa cassa di risonanza), tutti coloro che avevano a che fare con gli spreadsheet (e negli USA sono legioni) vollero provare se con VisiCalc si potevano davvero ottenere i risultati sperati (numeri giusti subito, calcoli e risultati aggiornati automaticamente) e la società Apple Computer Inc., che aveva fino ad allora venduto poche decine di esemplari di Apple II, fu subissata di richieste e divenne ben presto leader di un fiorente mercato in sviluppo. VisiCalc dominò il mercato per qualche anno, poi comparve una società di software molto aggressiva, di nome Lotus Development Corp., che sviluppò un prodotto concorrente, predisposto per lavorare su macchine più potenti, quali il Personal Computer IBM che nacque nell’agosto del 1981. Il programma creato dalla Lotus per gli spreadsheet si chiamava 1-2-3, per due ragioni: veniva propagandato con lo slogan «facile da usare, come fare 1-2-3...» e perché i suoi creatori erano appassionati di musica (altri prodotti della stessa società si chiamarono poi Symphony e Jazz).

Il valzer di Lotus 1-2-3 durò a lungo: il prodotto era molto ben fatto e appagava completamente tutte le esigenze dei milioni di utenti che usavano un personal computer soltanto per creare e compilare fogli di lavoro. Il software lavorava nell’ambiente a caratteri tipico delle macchine basate sul sistema operativo MS-DOS; pochi fronzoli, molta sostanza, quello che contava era poter costruire in fretta e bene i fogli di calcolo ed essere sicuri che i totali per riga e colonna fossero sempre corretti e aggiornati.

Il ruolo di Microsoft Che cosa faceva, nel frattempo, la Microsoft Corporation? Tramava nell’ombra, verrebbe voglia di dire. Non esattamente. Agli inizi degli Ottanta del secolo scorso, Microsoft era una piccola società che aveva vinto una lotteria. Per un complesso concorso di circostanze era proprietaria del sistema operativo MS-DOS che veniva distribuito insieme con i Personal Computer IBM. Considerato l’enorme successo di vendita di queste macchine e dei milioni di esemplari costruiti dai concorrenti in modo da dare le stesse identiche prestazioni, la richiesta di copie del sistema operativo MSDOS e l’impegno per aggiornarlo e potenziarlo assorbivano totalmente le energie della piccola e grintosa società di Seattle. Per un lungo periodo, quindi, Microsoft si tenne ai margini del mercato del software applicativo, per concentrarsi sul software di sistema, potenziando MS-DOS e preparando un ambiente di lavoro più gradevole e più facile da usare, che sarebbe poi stato Windows. La domanda di programmi per gli spreadsheet, però, era talmente vivace che Microsoft pensò di fare comunque qualcosa e mise a punto nel 1985 un suo prodotto, che chiamò orgogliosamente Excel, destinato a lavorare sulla nuova generazione di macchine create dalla Apple Computer Inc. Queste macchine, che si chiamavano Macintosh (dal nome di una varietà di mele della California), erano strutturalmente diverse dai PC: invece di lavorare con i caratteri presentavano tutto per mezzo di immagini (le lettere e i numeri che si vedevano sul monitor erano in realtà micro disegni), usavano il mouse al posto della tastiera per l’immissione dei comandi più frequenti ed erano in genere molto più semplici e gradevoli da usare degli austeri personal computer IBM e derivati. Excel nacque, quindi, nel contesto grafico dei Macintosh e venne proposto per i PC soltanto quando su queste macchine si rese disponibile un software, Windows, che trasformava l’ambiente di lavoro originario dei PC in un ambiente grafico simile a quello dei computer della Apple. L’accoppiata Windows ed Excel mise fuori dal mercato degli spreadsheet il poderoso concorrente della Lotus (la società sopravvisse occupandosi di altro) e oggi Excel è lo strumento software più diffuso per lavorare su fogli di calcolo con un personal computer dotato del sistema operativo Windows.

I grandi computer e i dati Il tumultuoso sviluppo dei personal computer negli ultimi cinque anni in tutte le loro varianti, fino agli attuali smartphone (che sono computer a tutti gli effetti nei quali è integrato un telefono), tende a far dimenticare che i computer non sono nati come oggetti d’uso personale, ma come macchine industriali di grandi dimensioni, dette genericamente “mainframe”, utilizzate esclusivamente come strumenti per gestire enti e istituzioni. La prima di queste macchine si chiamava UNIVAC ed esordì sul mercato agli inizi degli anni Cinquanta del secolo scorso, seguita poco tempo dopo da altre macchine chiamate 709 e poi 7090 nel decennio successivo e poi ancora negli anni Settanta, Ottanta e Novanta si diffusero nuovi modelli di mainframe fisicamente più grandi e più potenti, prodotti da numerose società che operavano nel mercato informatico di allora, fra le quali primeggiava la International Business Machines meglio nota con la sigla Ibm. Per ottenere qualcosa di utile da un computer – si tratti di un mainframe grande come una sala da ballo sul quale sono attestate reti di migliaia di computer satelliti e di terminali o di un semplice tablet tascabile – è necessario fargli eseguire un programma, vale a dire un insieme ben organizzato di istruzioni che devono essere scritte a mano da una persona che conosca quello che viene definito un po’ pomposamente il “linguaggio” di quel computer, cioè l’insieme delle istruzioni che la macchina è in grado di eseguire. Questo vincolo è stato per tutta la storia dell’informatica il più faticoso e costoso ostacolo da superare per arrivare a un uso ottimale dei computer: il progresso vertiginoso nell’elettronica raddoppiava ogni 24 mesi la velocità dei circuiti, la parte tangibile, l’hardware, dei computer, mentre lo sviluppo nella realizzazione dei programmi, della parte non tangibile, il software dei sistemi, restava lontanissimo da progressi analoghi. Naturalmente anche nel campo della programmazione si sono fatti notevoli passi avanti rispetto agli inizi. Agli albori dell’informatica si davano istruzioni ai computer utilizzando direttamente il cosiddetto “emlinguaggio macchina”, ovverosia il codice delle istruzioni elementari predisposte dai progettisti del nucleo logico del computer, la Central Processing Unit o CPU. Per rendere meno arduo il lavoro si ricorreva a un linguaggio elementare chiamato genericamente Assembler, col quale si potevano scrivere le istruzioni usando una codifica in lettere e numeri. Un programma scritto in Assembler veniva dato in input a un programma speciale, che traduceva le istruzioni nel codice binario che era l’unico “capito” dalle CPU.

Per migliorare la produttività del lavoro di programmazione vennero sviluppati, a partire dalla fine degli anni Cinquanta del secolo scorso, numerosi linguaggi concepiti per semplificare ulteriormente la stesura dei programmi. Diversamente dall’Assembler, i nuovi linguaggi di programmazione non contenevano istruzioni che corrispondevano direttamente con quelle del linguaggio macchina, ma mettevano a disposizione comandi formulati con parole chiave e operatori aritmetici e logici, molto più semplici da capire e da ricordare delle astruse istruzioni dell’Assembler. Le sequenze di caratteri in cui si concretizzavano i programmi scritti usando questi linguaggi venivano acquisite in input da programmi specializzati (scritti in Assembler) che “traducevano” quel flusso di caratteri in istruzioni in linguaggio macchina, che venivano poi passate per l’esecuzione ai computer mainframe. Due linguaggi di programmazione si affermarono col passare del tempo fra i tanti che furono sperimentati, chiamati rispettivamente FORTRAN (da FORmula TRANslation) particolarmente orientato allo sviluppo di programmi per il calcolo scientifico e COBOL (da COmmon Business Oriented Language), concepito per creare programmi per la gestione aziendale. La diffusione e il consolidarsi nell’uso di questi linguaggi ha determinato il rigoglioso sviluppo dell’informatica gestionale, che costituisce tuttora la spina dorsale dei sistemi di gestione delle grandi strutture organizzative (imprese e istituzioni). COBOL e FORTRAN sono linguaggi “procedurali”, vale a dire permettono di definire puntualmente procedure da seguire per elaborare i dati, per esempio preparare fatture partendo da bolle di consegna, listini dei prezzi e indirizzi dei clienti o bollettini di stipendi elaborando dati sugli orari di presenza al lavoro del personale e sulle retribuzioni associate a quegli orari. La disponibilità di questi e altri linguaggi procedurali favorì enormemente lo sviluppo dei sistemi informatici per la gestione di strutture organizzative grandi e piccole, ma il lavoro di sviluppo continuava a restare lento e faticoso, perché comportava comunque un massiccio impiego di risorse umane. Per rendere più spedito e più efficiente il lavoro intrinsecamente lento della scrittura dei programmi dei computer si definirono standard di scrittura e si cercò di individuare elementi ripetitivi nel lavoro di programmazione che potessero essere ricondotti a blocchi predefiniti di istruzioni, da utilizzare come “moduli” già pronti, da inserire nei nuovi sistemi gestionali – detti genericamente “applicazioni” – che si sviluppavano. Tipicamente, uno dei moduli che fu individuato per primo e si prestò meglio di tutti alla standardizzazione fu l’ordinamento. In quasi tutte le applicazioni era necessario ordinare in successione alfabetica o numerica stringhe di caratteri che rappresentavano matricole

di personale o codici di materiali, quindi si crearono e si diffusero con successo programmi di servizio che ricevevano in input una descrizione schematica dei dati da ordinare e in base a quella descrizione acquisivano flussi di dati disordinati che restituivano come sequenze ordinate, pronte per successive elaborazioni di calcolo o di selezione. Il successo anche commerciale di questi primi moduli di software utilizzabili con poche modifiche da chiunque dovesse sviluppare applicazioni che prevedevano di ordinare sequenze di dati condusse gradualmente a individuare altre caratteristiche delle applicazioni che si prestassero ad analoghe standardizzazioni. Alla fine degli anni Sessanta, si individuò con chiarezza una struttura portante comune a tutti i sistemi informatici per la gestione aziendale, che venne chiamata genericamente “base dei dati” o data base in inglese. Col senno di poi sembra una banalità, ma si trattò di una intuizione luminosa che contribuì non poco a rendere più efficiente (e quindi meno costoso e più veloce) lo sviluppo delle applicazioni gestionali. L’idea centrale è che qualunque applicazione gestionale fa riferimento a un insieme di dati, la sua base, che è opportuno gestire separatamente, con uno strumento generico che sia invariante rispetto al contenuto e alla tipologia dei dati, quindi utilizzabile sia da una banca per i suoi conti correnti sia da una catena di supermercati per i suoi magazzini. L’idea era quella di separare la gestione dei dati – acquisizione e manutenzione – dal loro utilizzo, consentendo a chi sviluppava le applicazioni di occuparsi soltanto dell’utilizzo dei dati e non della loro gestione/amministrazione: un altro importante passo avanti nella riduzione dei tempi (e quindi dei costi) dello sviluppo delle applicazioni. Furono messi a punto svariati sistemi per la gestione delle basi di dati, chiamati genericamente Data Base Management Systems, in sigla DBMS, che furono promossi commercialmente con vigore non soltanto dalle società di sviluppo software che li avevano creati ma anche e soprattutto dalle imprese che producevano hardware, che vedevano giustamente nei DBMS un fattore che avrebbe favorito l’adozione dei computer anche in imprese poco propense a farsi carico di notevoli costi per lo sviluppo del software applicativo. Nel 1970 un inglese naturalizzato americano studioso di logica e matematica di nome Edgar F. Codd pubblicò nella rivista istituzionale degli informatici USA (Communications of the Association for Computing Machinery), un articolo intitolato “A Relational Model of Data for Large Shared Data Banks” (“Un modello relazionale di dati per grandi banche dati condivise”) che segnò la nascita formale di una nuova e più

feconda concezione delle basi di dati, che prese il nome di Relational Data Base Management System ovvero RDBMS. Il termine “relational” sta a indicare l’elemento centrale della nuova concezione delle banche dati, concepite come insiemi di relazioni logiche fra dati elementari univoci che fisicamente si presentano come tabelle. Nei dieci anni successivi alla pubblicazione del fondamentale contributo concettuale di Codd alcune società che realizzavano software lavorarono allo sviluppo di un linguaggio di programmazione che consentisse di creare RDBMS e di accedere agevolmente ai dati che contenevano: il risultato di questo lavoro di ricerca prende il nome di Structured Query Language, in sigla SQL, vale a dire “linguaggio per interrogazioni strutturate”. Con SQL si creano e si interrogano (“emquery” in inglese vuol dire domanda, interrogazione) RDBMS. Ciò su cui agisce lo SQL sono tabelle – idealmente identiche ai fogli di lavoro di Excel – composte da righe e colonne, secondo regole rigorose che vedremo in modo approfondito nei prossimi capitoli. I linguaggi che si sono affermati nel tempo, il FORTRAN e il COBOL, di cui abbiamo già parlato, e molti altri ancora, sono accomunati da queste due caratteristiche: sono linguaggi generalizzati e procedurali. Un linguaggio generalizzato permette di far fare al computer qualsiasi cosa: eseguire calcoli matematici, acquisire dati da apparecchiature periferiche (tastiere, lettori di dischi ottici, schede di memoria) e salvarli su disco in forma di file, tracciare segni grafici su un monitor, emettere suoni e altro ancora. Un linguaggio procedurale è dotato di istruzioni e comandi con i quali si indica al computer come deve operare per arrivare al risultato che interessa al programmatore. Un linguaggio procedurale serve per scrivere procedure, che il computer esegue con sovrana imperturbabilità. Lo Structured Query Language, invece, è un linguaggio specializzato e non procedurale. Specializzato: non si può usare SQL per fare grafica o per acquisire dati da trasferire su file, ma lo si usa soltanto per lavorare su tabelle. Non procedurale: con SQL non si creano procedure, non si spiega al computer come deve fare, ma gli si danno comandi che descrivono quello che si vuole ottenere. L’enfasi passa dal come fare al che cosa ottenere. Naturalmente da qualche parte nel computer ci deve pur essere qualcosa che trasformi gli imperiosi comandi SQL in miriadi di istruzioni in linguaggio macchina affinché il

computer riesca a dare i risultati che gli si chiedono, ma questo qualcosa è un interprete di SQL, scritto una sola volta per tutte da una squadra di sviluppatori di software di sistema. Chi vuole usare un database relazionale non deve preoccuparsi di come questo è fatto fisicamente, delle posizioni e dei collegamenti fisici dei record e delle tabelle. Il modello dei dati e della loro organizzazione è invariante rispetto alla loro realtà fisica: con SQL si accede ai dati e li si estrae sempre nello stesso modo, quale che sia la macchina e il database nel quale i dati risiedono.

Lavorare con SQL ed Excel Nato nel mondo dei grandi mainframe – dove tuttora domina come fondamentale strumento di lavoro – lo Structured Query Language è disponibile anche sui personal computer, nel cui mondo è nato Excel, che non è un linguaggio di programmazione ma un ambiente strutturato nel quale collocare dati per memorizzarli ed esaminarne le caratteristiche e le possibili affinità. Nei fogli di lavoro Excel i dati si inseriscono per lo più a mano ma si possono anche acquisire insiemi di dati già esistenti come file e inserirli in fogli di lavoro sui quali eseguire poi calcoli di varia natura – confronti, selezioni, ordinamenti – e ricavare illuminanti rappresentazioni grafiche dalle serie di dati contenuti in uno o più fogli di lavoro. In Excel 2016 un foglio di lavoro può contenere oltre un milione di righe (per l’esattezza 1.048.576) e svilupparsi per 16.384 colonne. Sembrerebbe una capienza più che adeguata per acquisire e analizzare grandi insiemi di dati quali quelli che si generano nei supermercati o nelle banche, ma in realtà non sono sufficienti, perché quel genere di flussi di dati si sviluppa per decine di milioni di record nel giro di poco tempo, creando depositi sterminati di dati elementari chiamati “Big Data” che bisogna affrontare con gli strumenti di gestione tipici dei sistemi per database. Si può concepire una comoda divisione del lavoro, quando è necessario lavorare sui Big Data: acquisirli e organizzarli in tabelle con l’aiuto di SQL e riversarli selettivamente (ricorrendo alle query di SQL) su un foglio di lavoro Excel nel quale lavorare di fino usando gli strumenti grafici di Excel per rappresentarli e metterli a confronto fra loro e ricorrendo agli strumenti di calcolo aritmetico e statistico di Excel per cercare di penetrare il significato di quelle masse di dati. In questo libro presentiamo una rassegna completa dello Structured Query Language, fornendo anche indicazioni su come procurarsene una versione da usare su un personal computer. Dopo questa rassegna, una serie di esempi concreti aiuta a capire come acquisire grandi flussi di dati con SQL e riversarli su fogli di lavoro Excel e quali strumenti intrinseci di Excel conviene usare caso per caso per analizzare quei dati e ricavarne opportune rappresentazioni grafiche.

File degli esempi All’indirizzo http://www.apogeonline.com/libri/9788850333745/scheda i lettori potranno scaricare liberamente i file dei dati utilizzati per gli esempi mostrati nel corso del testo. Questi file

possono essere utilizzati per fare pratica riproducendo le procedure di lavoro con SQL ed Excel. Marco Ferrero [email protected]

Giugno 2016

Capitolo 1

Gli strumenti di lavoro

Per apprendere le tecniche di analisi dei dati con SQL ed Excel occorrono un certo numero di dati dimostrativi, una copia installata e funzionante di Excel 2016 e un sistema per creare e gestire database relazionali utilizzando lo Structured Query Language. I dati dimostrativi sono a disposizione dei lettori all’indirizzo http://www.apogeonline.com/libri/9788850333745/scheda, il programma Excel 2016 dovrebbe far parte della dotazione di software del computer col quale si lavora e quanto agli strumenti per i database ne proponiamo due, disponibili per il download gratuito dal Web e che si chiamano rispettivamente MySQL e SQL Server. Ai fini dell’apprendimento e delle prove, l’uno vale l’altro: in questo libro presenteremo sempre gli esempi nelle forme adeguate per poterli provare su entrambi i sistemi.

MySQL Nato a Helsinki ma cittadino svedese, Ulf Michael Widenius, detto Wolf dagli amici, è un imprenditore e creatore di software che nel 1996 mise a disposizione del pubblico un sistema che aveva creato per la gestione di database relazionali chiamandolo MySQL dal nome della sua prima figlia My. Il sistema MySQL ha avuto uno sviluppo notevole per durata, qualità e successo e attualmente (anno 2016) viene distribuito in due versioni, per le imprese (a pagamento) e per la comunità degli studiosi (a titolo gratuito). Per entrambe le versioni esistono varianti predisposte per operare sotto diversi sistemi operativi, per cui la versione di MySQL che ci interessa va cercata a partire dal sito web: http://dev.mysql.com/downloads/windows/

Quando vi si accede, questo sito presenta la schermata che vediamo nella Figura 1.1.

Figura 1.1 La schermata dalla quale si può attivare il download di MySQL per Windows. NOT A Si sarebbe indotti a pronunciare il nome MySQL separando le due parti e sillabando in inglese “mai es chiù el”, ma gli addetti ai lavori pronunciano “màskl”, come si apprende dai filmati sull’argomento visibili nel Web.

La versione di questo strumento di lavoro che andremo a utilizzare negli esempi del libro è la 5.6, che si ottiene agevolmente iniziando il processo di download con un clic sul collegamento ipertestuale MySQL Installer, In quel contesto possiamo scegliere quale scaricare fra due diversi file di installazione: 1. il file predisposto per eseguire l’installazione mentre si è connessi col Web tramite una connessione a banda larga: mysql-installer-web-community-5.7.11.0.msi 2. il file previsto per installare MySQL senza essere connessi col Web: mysql-installercommunity 5.7.11.0.msi

Dopo aver scaricato uno o l’altro di questi file lo si esegue installando così una versione di MySQL prevista per operare su una macchina a 32 bit: una volta installato, il sistema funziona perfettamente anche su una macchina a 64 bit. Prima di eseguire il file che si è scelto di scaricare è opportuno, già che si è collegati col sito ufficiale di MySQL, individuare e scaricare un altro file da utilizzare per l’installazione, disponibile in due versioni, per macchine a 32 o a 64 bit, chiamate rispettivamente: mysql-workbench-community-6.3.6-winx32.msi mysql-workbench-community-6.3.6-winx64.msi

Questo file si userà dopo aver installato MySQL per ottenere una serie di strumenti grafici che agevolano l’interfaccia dell’utente con il sistema MySQL. Dopo aver scaricato il file mysql-installer nella versione che si è preferita lo si esegue con un doppio clic sul suo nome facendo così avviare una procedura guidata molto semplice da seguire, che propone in successione varie opzioni fra le quali è intuitivo scegliere quelle corrette per ottenere una installazione semplice da utilizzare e adeguata alle finalità didattiche che intendiamo ottenere. In tempi successivi non sarà difficile modificare anche profondamente la versione installata di MySQL per ottenere risultati professionali molto impegnativi. Il concetto base che orienta l’intero processo di download e poi di installazione è che MySQL è un server, vale a dire uno strumento software che agisce come se fosse una macchina separata da quella con la quale stiamo lavorando: con questo server ci si connette presentandosi come client per avere un servizio, nel caso specifico la creazione e la gestione di database. Per procedere correttamente con l’installazione è necessario come prima cosa accettare le condizioni richieste dalla schermata License Agreement, spuntando con un clic la casella di controllo “I accept the license terms”.

La schermata successiva, intitolata Choosing a Setup Type, presenta varie opzioni per il tipo di impostazione da scegliere: è opportuno selezionare l’opzione Developer Default. Potrebbe successivamente comparire una finestra che chiede di impostare manualmente determinati requisiti (detti “requirements”): possiamo ignorare queste richieste procedendo oltre con un clic sul pulsante Next. Compare finalmente la schermata che vediamo nella Figura 1.2, che elenca i componenti detti “products” che verranno installati, l’ultimo dei quali è chiamato Sample and Examples e – come il suo nome lascia intuire – è un insieme di esempi e di dati campione da utilizzare per fini didattici. Un clic sul pulsante Execute avvia il processo di installazione, che non richiede alcun intervento da parte dell’operatore. Terminato questo processo, una nuova schermata intitolata Type and Networking propone una serie di valori predefiniti per le modalità di comunicazione fra server e client, che si possono lasciare come stanno; in un elenco a discesa chiamato Config Type scegliamo – fra quelle disponibili per definire il tipo di configurazione – l’opzione Development Machine, che è quella che ci serve: una macchina per creare database e provare a utilizzarli in un contesto di sviluppo applicativo. Viene successivamente aperta una finestra Accounts and Roles nella quale dobbiamo qualificarci come utenti, scegliendo account, ruoli e password relative. L’organizzazione di un server di database è per sua natura gerarchica e autoritaria, per cui esiste un unico capo supremo, dal quale promanano le nomine di chi è autorizzato ad accedere ai database e con quali ruoli.

Figura 1.2 La schermata per l’installazione dei prodotti che completano MySQL.

L’autorità suprema si chiama per definizione root e la sua identità va protetta con una password che dobbiamo scegliere e, come suggerisce la finestra di dialogo, conservare in un luogo sicuro. È opportuno che la password che andiamo a inserire sia lunga almeno otto caratteri e contenga un assortimento di lettere maiuscole e minuscole, cifre e caratteri speciali. Dopo che l’abbiamo scritta interviene un algoritmo interno di valutazione della sua efficacia, che fa comparire un giudizio scritto in caratteri colorati, che nel caso migliore è Strong. Nella stessa finestra – forti dell’autorità che ci viene dall’essere per definizione titolari dell’account root – possiamo creare nuovi account, attribuendo loro un nome in chiaro, una password e selezionando per ciascuno un certo numero di autorizzazioni scelte da un elenco chiuso. Vediamo il risultato di questa operazione nella Figura 1.3. Terminologia La parola account in inglese vuol dire conto e nel gergo informatico si usa per riferirsi in modo generico a chiunque sia autorizzato ad accedere a un sistema computerizzato. Il termine nacque agli inizi della

multiutenza sui grandi computer degli anni Settanta del secolo scorso, che erano macchine costosissime: a ogni utente corrispondeva una posizione contabile, un account, appunto, che serviva per addebitare a ciascuno i consumi di tempo macchina e l’occupazione di spazio sui dischi, così da ripartire i costi di gestione in modo proporzionale all’utilizzo effettivo da parte dei vari utenti, i quali per similitudine venivano chiamati anch’essi account.

Figura 1.3 L’impostazione degli account root e ausiliari.

Il passo successivo consiste nel configurare MySQL come servizio di Windows, tramite una nuova finestra chiamata Windows Service. A questo scopo bisogna assegnare un nome univoco al servizio Windows da usare per l’istanza di MySQL che si sta installando. Se si installano più istanze serviranno altrettanti nomi univoci. Attribuito il nome al servizio, mettendo il segno di spunta in un’apposita casella di controllo si può scegliere se il server MySQL debba essere avviato oppure no contestualmente all’avvio di Windows.

MySQL Server deve operare sotto un determinato account utente, che può essere individuato personalizzando un account utente già disponibile oppure utilizzando l’account standard del sistema (questa è la soluzione più semplice e anche quella raccomandata nella finestra di dialogo Windows Service) Terminate le selezioni nella finestra di dialogo Windows Service, si apre la finestra Apply Server Configuration, nella quale non c’è altro da fare che premere il pulsante Execute per avviare ed eseguire le operazioni di configurazione, che si svolgono in successione automaticamente; dopo un po’ la finestra viene sostituita da un’altra con lo stesso nome ma con segni di spunta verdi associati ai nomi delle singole operazioni per confermare che è andato tutto bene e si può continuare premendo il pulsante Finish. Con l’esecuzione di questo comando si conclude l’installazione del prodotto MySQL vero e proprio. È opportuno, però, fare un ulteriore passo preliminare, installando anche l’interfaccia grafica per l’utente, lanciando il file mysql-workbench-community.msi con un doppio clic sul suo nome: l’esecuzione è del tutto automatica e il risultato che si ottiene si presenta come nella Figura 1.4.

Figura 1.4 MySQL Workbench: l’interfaccia utente per lavorare con MySQL.

A questo punto della nostra esposizione possiamo trascurare sia i pulsanti colorati che si vedono sul lato destro dello schermo, sia i riferimenti che stanno nell’angolo in basso a sinistra: vedremo più avanti che cosa rappresentano e come si possono utilizzare.

Nell’angolo superiore sinistro un riquadro rettangolare ci informa che è disponibile un’istanza locale del server di database chiamata MySQL56 che consente l’accesso a root e all’host che lo contiene che si chiama localhost 3306 (se non abbiamo modificato questi valori durante il processo di installazione). Un clic su quel riquadro apre finalmente gli strumenti che formano la versione grafica dell’interfaccia utente di MySQL, chiamata genericamente MySQL Workbench e che si articola in un numero notevole di finestre e menu. Se ci si collega al sito ufficiale di MySQL e più specificamente si attiva questo link: http://dev.mysql.com/doc/

si accede a una pagina web dalla quale si possono scaricare: il manuale utente più aggiornato per la versione di MySQL che abbiamo installato, un secondo manuale che descrive in tutti i particolari il funzionamento dell’interfaccia MySQL Workbench e le sue numerosissime finestre e menu. Non sono letture snelle per ragazzini volonterosi: il manuale completo si sviluppa per 4.480 pagine e quello per l’interfaccia di pagine ne contiene 416, i testi sono in inglese e i file sono in formato PDF, quindi leggibili con qualunque computer. Possiamo fermarci qui per quanto riguarda lo strumento MySQL: per utilizzarlo è necessario conoscere alcuni concetti fondamentali sui database relazionali e sulle loro caratteristiche logiche e di funzionamento per arrivare poi a conoscere il lessico e la sintassi dello Structured Query Language con il quale si creano e si utilizzano database. L’int erfaccia a riga di comando I “puri” di MySQL amano usare una interfaccia molto spartana, basata su comandi immessi da tastiera in una semplice finestra interattiva dove i caratteri appaiono in bianco su sfondo nero, che si presenta come nella figura che segue.

Possiamo tranquillamente ignorare le funzionalità offerte da questa austera interfaccia.

SQL Server Il suo nome ufficiale è Microsoft SQL Server ed è l’antesignano di tutti gli strumenti per creare e gestire grandi database in una logica client-server, Passato per numerose versioni nell’arco di oltre trent’anni di vita, anche SQL Server è disponibile in due varianti, una a pagamento per le imprese e un’altra gratuita per finalità di studio e di apprendimento. Per ottenere questo strumento si procede come per MySQL, accedendo al sito web di Microsoft dove è disponibile per il download. I nomi delle versioni di SQL Server sono stati cambiati molte volte da Microsoft, iniziando con la classica numerazione a due cifre che storicamente si usava per distinguere le successive versioni del sistema operativo MS-DOS. In tempi più recenti si è passati a distinguere le nuove versioni con il nome dell’anno nel quale sono state rese pubbliche: quella più recente si chiama Microsoft SQL Server 2016 ma nel momento in cui scriviamo (primavera 2016) è ancora in fase di messa a punto finale. Potremo usare quindi la penultima versione che si chiama Microsoft SQL Server 2014, che è perfettamente adatta ai nostri fini didattici. SQL Server è un prodotto di grande impegno e potenzialmente molto costoso se chi lo installa è un’impresa, ma è disponibile in una versione molto snellita e gratuita per chi non ne fa un uso commerciale. Possiamo quindi ottenere e installare una copia di questo prodotto accedendo al sito web di Microsoft e cercando il file: SQL Server 2014 Express. Si viene portati in una pagina nella quale è facoltativo dare qualche indicazione su chi si è, compreso un indirizzo e-mail e successivamente si sceglie la versione che interessa e la lingua dell’interfaccia utente, come possiamo vedere nella Figura 1.5.

Figura 1.5 Le opzioni disponibili per scaricare una versione gratuita di SQL Server.

Per portare a termine questo download bisogna disporre di una buona connessione Internet, poiché il file da ottenere – che si chiama SQLEXPRADV_x64_ITA.exe – è piuttosto pesante (intorno a 1,2 GB). Ottenuto il file sul proprio computer basta fare un doppio clic sul suo nome per avviare una procedura di installazione del tutto automatica, che non richiede praticamente alcun intervento da parte dell’utente (se si accettano le opzioni predefinite: scelta consigliabile a tutti gli effetti per chi non conosce già molto bene questo prodotto). In particolare, raccomandiamo di optare per la verifica dell’accesso a SQL Server basata su quella predisposta per la macchina Windows con la quale si lavora, risparmiando così il fastidio di avere un’ennesima password da creare e gestire. L’installazione procede abbastanza velocemente e si conclude con la comparsa della finestra di accesso all’interfaccia grafica, che si chiama SQL Server 2014 Management Studio. Un clic su questa finestra apre la schermata di lavoro, nella quale si può lavorare per creare e utilizzare database: come per MySQL, esiste un’alternativa a riga di

comando per accedere ai servizi e alle funzionalità di SQL Server, ma non è il caso di parlarne qui. Se il nostro computer lavora sotto la guida del sistema operativo Windows 10 può far comodo disporre le icone delle finestre d’accesso ai due sistemi nel modo che vediamo qui di seguito.

I database dimostrativi Una volta installato MySQL si può provare a usarlo subito, aprendo un database creato a scopo dimostrativo e che si chiama sakila. Per ottenere questo risultato basta fare un clic sull’icona di MySQL Workbench che è stata creata nel menu Start di Windows e nella finestra che si apre un clic sul riquadro in alto a sinistra avvia la connessione col server, presentando una finestra suddivisa in due fasce verticali. Quella di sinistra, più stretta, si chiama Navigator e si articola in due schede, Management e Schemas. Un clic su Schemas mostra l’elenco dei database disponibili, che a sistema appena installato sono tre: sys, world e sakila. Il primo è un database di sistema ed è meglio lasciarlo stare, world è un piccolo database dimostrativo creato da Oracle (grande società di software per database che di recente ha acquistato MySQL) e il terzo è quello che ci interessa: sakila. Alla sinistra del nome del database c’è un pulsante a forma di triangolo che punta verso destra: un clic su quel pulsante lo trasforma in un triangolo che punta verso il basso e fa sgranare, subito sotto il nome del database, l’elenco delle tabelle che lo compongono. Facendo scorrere il puntatore del mouse senza fare clic accanto al nome di una qualunque tabella si evidenziano tre piccoli pulsanti affiancati a destra del nome della tabella sulla quale si è soffermato il mouse. Il primo di questi pulsanti ha l’icona di una i bianca in campo grigio e sta per “informazione”, il secondo ha l’icona di una chiave esagonale e significa “struttura” e il terzo ha l’icona di una griglia e indica “contenuto della tabella”. Un clic su ciascuna di queste icone genera nel riquadro più grande a destra della sezione Navigator (che si chiama “finestra Query”) le informazioni corrispondenti. Vediamo nella Figura 1.6 il risultato che si ottiene premendo il pulsante con l’icona delle chiave esagonale in corrispondenza della tabella film.

Figura 1.6 La struttura della tabella film evidenziata nella finestra Query.

Può essere più illuminante selezionare il piccolo pulsante con l’icona della griglia, che genera nella parte inferiore della finestra Query l’elenco dei contenuti della tabella selezionata . Il database sakila si articola in 16 tabelle, concepite per rappresentare un ipotetico negozio per il noleggio di DVD cinematografici. Le tabelle, quindi, contengono un catalogo dei film disponibili su DVD, un elenco di clienti e varie informazioni di servizio sui noleggi in corso. Anche per SQL Server esiste un database dimostrativo, chiamato AdventureWorks2014, che non viene installato contestualmente alla installazione del server, ma è disponibile come file da scaricare dal sito web di Microsoft dove si trovano le diverse versioni di SQL Server. Il file da scaricare è un file compresso chiamato Adventure Works 2014 Full .

Database Backup.zip

Dopo averlo scaricato bisogna scompattarlo in una cartella qualunque, dove si otterrà un file chiamato AdventureWorks2014.bak . A questo punto è necessario controllare che SQL Server si trovi nel seguente percorso (se installato in una macchina a 64 bit):

C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS

Fatta questa verifica, aprire il programma di scrittura Blocco note di Windows e trascrivere in un nuovo documento di testo le righe che seguono, assicurandosi di copiare esattamente ogni singolo carattere: USE [master] RESTORE DATABASE AdventureWorks2014 FROM disk= [digitare qui fra virgolette semplici il percorso completo in cui si trova il file AdventureWorks2014.bak Per esempio 'C:\ESEMPI\ AdventureWorks2014.bak'] WITH MOVE 'AdventureWorks2014_data' TO 'C:\Program Files\Microsoft SQL server\MSSQL12.SQLEXPRESS\ MSSQL\DATA\AdventureWorks2014.mdf', MOVE 'AdventureWorks2014_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\ MSSQL\DATA\AdventureWorks2014.ldf' ,REPLACE

Dopo aver scritto e controllato attentamente il testo, salvare il file corrispondente con un nome qualunque e copiarne il contenuto negli Appunti di Windows. Aprire SQL Server attivando la connessione predefinita e fare clic sul pulsante Nuova query nella barra degli strumenti che sta in testa alla finestra. Accanto alla finestra Esplora oggetti che sta sul lato sinistro si affiancherà una finestra vuota chiamata SQL Query.1sql e in questa finestra bisogna incollare il contenuto degli Appunti, ottenendo il risultato che si vede nella Figura 1.7.

Figura 1.7 Lo script per installare il database AdventureWorks2014.

Quando il testo è entrato nella finestra si fa clic sul pulsante Esegui caratterizzato da un punto esclamativo rosso che sta nella barra degli strumenti e se tutto va bene dopo qualche istante il database dimostrativo AdventureWorks2014 è installato e il suo nome compare nella finestra Esplora oggetti ed è pronto per essere utilizzato. Questo database contiene le tabelle per la gestione completa di un business di produzione e vendita di biciclette. La finestra Esplora oggetti di Management Studio di SQL Server è simile alla finestra Navigator di MySQL Workbench, ma offre meno possibilità di esplorazione diretta dei contenuti di un database: è soltanto possibile visualizzare l’elenco a discesa delle tabelle che compongono un database e all’interno di questo elenco si possono visualizzare i nomi dei campi per ciascuna tabella. Per qualunque operazione anche di semplice lettura dei contenuti di una tabella è necessario eseguire espressamente un comando nel linguaggio SQL, come vedremo nei prossimi capitoli.

I file di lavoro Per raggiungere i risultati didattici che ci proponiamo con questo libro è essenziale lavorare con esempi reali, quindi con file di dati ricavati da concrete realtà sociali ed economiche. Abbiamo ottenuto alcuni flussi di dati grezzi da registrazioni di eventi commerciali di due società, una che vende articoli di consumo per corrispondenza e l’altra che fornisce servizi di telefonia cellulare. Per ovvie ragioni di riservatezza abbiamo sostituito ai nomi delle persone e delle merci e servizi nomi fittizi, mantenendo però la struttura dei dati grezzi a disposizione. Questi dati sono fisicamente raccolti in file di testo delimitati da tabulazioni e salvati con l’estensione .txt, cosa che li rende facilmente acquisibili in tabelle di database create con MySQL o con SQL Server. In teoria si potrebbero anche acquisire direttamente in fogli di lavoro Excel, ma le dimensioni di alcune tabelle superano (deliberatamente) la capienza in righe di un foglio di lavoro Excel, quindi è necessario utilizzare prima un sistema per i database per acquisire i dati e selezionarli in vari modi così da trasferirne opportune sintesi in fogli di lavoro Excel. Il file di testo chiamato abbonati.txt raccoglie in un unico flusso di oltre cinque milioni di righe un certo numero di dati riferiti ad abbonati di una società di telefonia cellulare privata. I file ordini.txt, prodotti.txt, righe_ordine.txt e clienti.txt formano un insieme di tabelle opportunamente coordinate fra loro per la gestione degli acquisti fatti dai clienti della società commerciale.

Capitolo 2

Caratteristiche dei database relazionali

Con lo Structured Query Language si creano e si utilizzano database relazionali. Questo vuol dire che i comandi della componente Data Definition Language (DDL) di SQL permettono di creare agevolmente tabelle che nel loro insieme formano un database relazionale, però la struttura e il contenuto delle tabelle devono essere impostati prima di ricorrere al DDL per crearle e questo è un compito separato, che nulla ha a che fare con la programmazione in SQL. Affinché un database si possa considerare relazionale, le tabelle (cioè le relazioni fra dati) che lo compongono devono essere costruite nel rispetto di alcuni vincoli, che vengono genericamente chiamati forme normali (in inglese Normal Forms), per cui le tabelle che rispettano tali vincoli si dicono normalizzate. In molte realtà gestionali si lavora con tabelle di dati, create con strumenti manuali o informatici, che non rispettano le forme normali: per poter gestire quei dati con un database relazionale è necessario riorganizzarli in tabelle normalizzate. La teoria che sta alla base delle forme normali e che orienta le operazioni di normalizzazione delle tabelle è piuttosto ardua e complessa, ma ai fini pratici può essere ricondotta a pochi concetti essenziali, che si possono capire bene con alcuni esempi, che esporremo in questo capitolo. Righe e colonne Nella terminologia dei database relazionali una tabella è composta da righe, i record, e da colonne, i campi.

In tutto il libro useremo indifferentemente i termini record o riga e campo o colonna per parlare dei due elementi che costituiscono una tabella..

Univocità e chiavi primarie Ogni record di una tabella deve essere univoco, vale a dire ciascuna riga deve essere diversa, almeno in una sua colonna, da tutte le altre. Nella tabella che segue abbiamo due record perfettamente identici, che creano ambiguità. Cognome

Nome

Indirizzo

Cit t à

CAP

Provincia

Rossi

Giuseppe

via Manzoni, 40

Milano

20121

MI

Rossi

Anna

via Manzoni, 40

Milano

20121

MI

Rossi

Giuseppe

via Montenapoleone, 12

Milano

20121

MI

Rossi

Anna

via Manzoni, 40

Milano

20121

MI

Per eliminare l’ambiguità (e la ridondanza) bisogna togliere dalla tabella il doppione, ottenendo questo risultato. Cognome

Nome

Indirizzo

Cit t à

CAP

Provincia

Rossi

Giuseppe

via Manzoni, 40

Milano

20121

MI

Rossi

Anna

via Manzoni, 40

Milano

20121

MI

Rossi

Giuseppe

via Montenapoleone, 12

Milano

20121

MI

I record di una tabella non sono numerati e l’ordine col quale si susseguono non è noto, quindi per distinguere i singoli record l’uno dall’altro è necessario esaminare il contenuto dei loro campi. Dato che un record può contenere decine di campi, per ridurre il numero delle letture necessarie per distinguere ciascun record è opportuno identificare in una tabella le colonne che potrebbero avere un contenuto diverso in ogni riga. I campi o colonne che hanno queste caratteristiche si chiamano chiavi candidate e fra queste se ne sceglie una che assume il ruolo e il nome di chiave primaria. Una tabella può avere una sola chiave primaria e la sua presenza garantisce l’univocità dei record; la chiave primaria può essere semplice, se formata da una sola colonna, o composta, quando risulta dalla combinazione di più colonne (non necessariamente contigue). Nell’esempio che stiamo esaminando, nessuna delle sei colonne può essere una chiave candidata, perché ognuna contiene valori ripetuti, quindi l’unica possibilità che abbiamo è definire una chiave primaria composta con le colonne Cognome+Nome+Indirizzo. Che cosa succede, però, se un altro Giuseppe Rossi va ad abitare in via Manzoni, 40 a Milano? Ci troveremmo con un record ridondante, ma in realtà si tratta di una persona diversa, anche se tutti i campi sono uguali. Quando la natura dei dati raccolti in una tabella può creare ambiguità di questo genere, è opportuno aggiungere una nuova colonna alla struttura della tabella, creando con questa una chiave primaria, che potrebbe essere, per esempio, un codice numerico diverso per ogni record; in questo modo si garantisce l’univocità di ciascuna riga, anche se vi fossero righe che contengono gli

stessi valori in tutte le altre colonne. Ecco quindi la nostra tabella con una chiave primaria semplice e diversa per ciascun record. IDPersona

Cognome

Nome

Indirizzo

Cit t à

CAP

Provincia

1

Rossi

Giuseppe

via Manzoni, 40

Milano

20121

MI

2

Rossi

Anna

via Manzoni, 40

Milano

20121

MI

3

Rossi

Giuseppe

via Montenapoleone, 12

Milano

20121

MI

4

Rossi

Giuseppe

via Manzoni, 40

Milano

20121

MI

In considerazione della sua importanza per garantire l’univocità dei record, anche se non è obbligatorio farlo, è sempre opportuno definire una chiave primaria quando si crea una tabella.

Chiavi esterne e relazioni Si crea un database di solito per gestire più tabelle, ciascuna delle quali aggrega dati relativi ad attributi di entità diverse, ma in un qualche modo correlate: Clienti, Prodotti e Ordini, per esempio, possono essere le tipiche tabelle che si utilizzano per gestire l’attività commerciale di un’impresa; in questo caso la correlazione è fra Clienti, che emettono Ordini, i quali si riferiscono a Prodotti. Gli RDBMS consentono di definire una correlazione fra due tabelle utilizzando la chiave primaria di una tabella (che è la tabella genitore o master) e una colonna, chiamata chiave esterna, dell’altra (la tabella figlia). Le relazioni che si stabiliscono in questo modo fra due tabelle possono essere di tre tipi: uno a uno, uno a molti e molti a molti.

Relazione uno a uno Due tabelle sono in una relazione uno a uno se, per ciascuna riga della tabella master, esiste non più di una sola riga nella tabella figlia. Nella realtà organizzativa difficilmente si danno casi di relazioni uno a uno fra famiglie di entità, ma nella pratica operativa dei database una relazione di questo tipo può essere creata deliberatamente per tenere separati su due tabelle attributi diversi riferiti alle stesse entità. È il caso, per esempio, delle tabelle schematizzate qui di seguito, che contengono dati clinici sui pazienti e sono in una relazione uno a uno stabilita, in questo caso, sulla chiave primaria di entrambe le cartelle, che nella seconda funge anche da chiave esterna. La tabella CartelleCliniche può essere esaminata e aggiornata con dati su visite e analisi, senza accedere ai dati che identificano il paziente, conservati nella tabella Pazienti. CartelleCliniche Pazienti CodicePaziente CodicePaziente ← Chiave primaria DataNascita Nome DataRicovero Cognome Sesso TesseraSanitaria VES Indirizzo ECG Città PSA Provincia Bilirubina

Organizzando i dati in questo modo, si possono stabilire per il database regole di accesso diverse per la tabella CartelleCliniche rispetto alla tabella Pazienti, anche se i record sono riferiti alle stesse persone.

Relazione uno a molti

Due tabelle sono in una relazione uno a molti quando, per ciascuna riga della prima tabella, possono esserci zero, uno o molti record nella seconda, ma per ciascun record della seconda esiste uno e un solo record nella prima. Diversamente dal caso precedente, quello della relazione uno a uno, la relazione uno a molti è assai frequente nella realtà organizzativa, si può anzi dire che sia la norma, ed è quindi importante capire in che modo si può stabilire questa relazione fra due tabelle in un database relazionale. Vediamo un semplice esempio. Vogliamo completare la tabella Indirizzi che abbiamo visto in precedenza, aggiungendo un campo per il numero di telefono a quelli già definiti, cioè IDPersona ← chiave primaria Cognome Nome Indirizzo Città CAP Provincia

Ci accorgiamo, però, esaminando la rubrica cartacea dalla quale trascriviamo gli indirizzi, che alcune persone hanno un solo numero di telefono, altre ne hanno due (fisso e cellulare, o fisso di casa e fisso dell’ufficio), altre tre (fisso di casa, fisso dell’ufficio, cellulare) e altre ancora arrivano a quattro, aggiungendo agli ultimi tre anche il numero del telefono fisso della casa al mare o in montagna. Per tener conto di tutte le possibilità dovremmo aggiungere quattro colonne alla tabella: TelefonoCasa TelefonoUfficio TelefonoCellulare TelefonoSecondaCasa

ma non sarebbe una soluzione corretta, perché molte righe avrebbero due o tre colonne vuote e potrebbe poi sempre capitare il caso di una persona che di numeri di telefono ne ha addirittura cinque o sei. La soluzione consiste nell’aggiungere al database una seconda tabella, che chiamiamo Telefoni, che può essere strutturata in questo modo: CodiceTelefono ← chiave primaria Raccordo ← chiave esterna NumeroTelefono

Nella tabella Telefoni il campo CodiceTelefono è la chiave primaria, mentre il campo Raccordo è la chiave esterna e contiene un valore identico a quello del campo IDPersona, che è la chiave primaria della tabella Indirizzi. Ogni persona elencata nella tabella Indirizzi può avere uno o più numeri di telefono, registrati nella tabella Telefoni. La corrispondenza fra i record delle due tabelle è data dal fatto che il valore della chiave primaria di Indirizzi viene utilizzato (mettendo lo stesso valore nel campo Raccordo) per identificare con certezza a chi appartiene ogni singolo numero di telefono.

Quando due tabelle sono legate da una relazione uno a molti, si collocano in uno spazio ideale, dove la tabella master sta a sinistra (detto lato uno della relazione) e la tabella figlia si trova a destra (detto lato molti della relazione). Non è obbligatorio che a tutti i record della tabella master corrisponda un record nella tabella figlia. In termini concreti, potremmo avere nella nostra tabella Indirizzi nomi di persone che non hanno telefono (o delle quali non conosciamo il numero di telefono). È invece tassativamente obbligatorio che nella tabella che si trova dal lato molti della relazione ogni record sia correlato (tramite la sua chiave esterna) a uno e un solo record della tabella che sta nel lato uno (distinto dalla sua chiave primaria). Nel nostro esempio, non avrebbe senso avere un record nella tabella Telefoni che non corrisponda a un record nella tabella Indirizzi. Lo schema della relazione uno a molti è riportato nella Figura 2.1.

Figura 2.1 Una relazione uno a molti.

Relazione molti a molti Si ha questo tipo di relazione quando si aggregano in tabelle informazioni su realtà che per loro natura sono caratterizzate da una molteplicità di relazioni logiche e pratiche. È il caso, per esempio, dei libri scientifici e dei corsi universitari. Molti manuali universitari e libri scientifici in genere sono opera di più autori e gli stessi autori scrivono anche libri da soli o insieme con altri. Concettualmente simile è il caso degli studenti e dei corsi: ogni studente universitario segue più corsi, ma non tutti i corsi sono seguiti dagli stessi studenti.

Per gestire correttamente tabelle che contengono dati di questo tipo – titoli e autori, autori e titoli, oppure studenti e corsi, corsi e studenti – che sono correlati con relazioni molti a molti, è necessario scomporre queste relazioni con l’aiuto di tabelle intermedie, che fungano da raccordo, in modo da poter rientrare nella casistica delle relazioni uno a molti. Vediamo un esempio nella Figura 2.2. In un database utilizzato per gestire una biblioteca abbiamo tre tabelle, Titoli, Autori ed Editori. La tabella Titoli contiene informazioni sui libri e utilizza come chiave primaria il codice ISBN, cioè l’International Standard Book Number, che identifica in modo univoco ciascun libro in circolazione. Questa tabella sta dal lato molti di una relazione uno a molti con la tabella Editori, dove lo stesso codice ISBN è la chiave esterna. La tabella Autori contiene due sole colonne: ID_Au, la chiave primaria che identifica univocamente ciascun autore, e Autore, che contiene il nome di ciascun autore. Dato che fra Autori e Titoli esiste una relazione molti a molti, è stata creata una tabella di raccordo Titolo_Autore, che fa da ponte fra Autori e Titoli, avendo un campo ISBN e un campo ID_Au per ogni coppia autore-libro.

Figura 2.2 Strutture e relazioni delle tabelle Autori, Titolo_Autore, Titoli ed Editori.

Come si può vedere dalla figura, che riproduce una schermata da un RDBMS, le relazioni fra tabelle si rappresentano graficamente come una linea che congiunge la chiave primaria con la chiave esterna, affiancando il simbolo dell’infinito al nome della chiave esterna (lato molti) e il numero 1 al nome della chiave primaria (lato uno).

Normalizzazione e forme normali Le “forme normali” sono criteri rigorosi, basati sulla teoria degli insiemi e la logica matematica, per orientare la progettazione delle tabelle di un database relazionale. Gli studiosi hanno individuato e definito numerose forme normali, che stabiliscono vincoli per livelli progressivi: una tabella strutturata in modo conforme alla terza forma normale rispetta anche la prima e la seconda. Vediamo con un esempio il senso della prima forma normale, in inglese First Formal Norm, in sigla 1FN. Immaginiamo di aver organizzato una tabella in questo modo: Nominat ivo

Indirizzo

Antonio Rossi

via Meravigli, 15 Milano

Pasquale Giordani

piazza Garibaldi, 19 Genova

Enrico Ferravilla

largo Risorgimento, 54 Torino

Mario Rossi

via Uberti, 12 Milano

Anna Giordani

piazza Leopardi, 3 Torino

Elena Buscemi

corso Pirandello, 12 Agrigento

Le entità rappresentate dalle righe sono persone e le colonne contengono due soli attributi di ciascuna entità, Nominativo e Indirizzo. Se volessimo disporre le righe di questa tabella in ordine alfabetico secondo il cognome, non potremmo farlo, perché nel campo Nominativo il cognome è insieme col nome e per di più viene al secondo posto. Avremmo difficoltà anche a estrarre da una tabella fatta in questo modo le righe delle persone che abitano in una determinata città, a Torino, per esempio, o a Milano, perché il campo Indirizzo mette insieme tutti gli elementi di ogni indirizzo. Se riorganizziamo i dati in questo modo: Nome

Cognome

Indirizzo

Cit t à

Antonio

Rossi

via Meravigli, 15

Milano

Pasquale

Giordani

piazza Garibaldi, 19

Genova

Enrico

Ferravilla

largo Risorgimento, 54

Torino

Mario

Rossi

via Uberti, 12

Milano

Anna

Giordani

piazza Leopardi, 3

Torino

Elena

Buscemi

corso Pirandello, 12

Agrigento

possiamo ordinare agevolmente le righe in base al cognome, e altrettanto agevolmente possiamo selezionare soltanto le righe che contengono un determinato valore, Milano o Torino, per esempio, nella colonna Città. Nome

Cognome

Indirizzo

Cit t à

Elena

Buscemi

corso Pirandello, 12

Agrigento

Enrico

Ferravilla

largo Risorgimento, 54

Torino

Anna

Giordani

piazza Leopardi, 3

Torino

Pasquale

Giordani

piazza Garibaldi, 19

Genova

Antonio

Rossi

via Meravigli, 15

Milano

Mario

Rossi

via Uberti, 12

Milano

Nome

Cognome

Indirizzo

Cit t à

Antonio

Rossi

via Meravigli, 15

Milano

Mario

Rossi

via Uberti, 12

Milano

Enrico

Ferravilla

largo Risorgimento, 54

Torino

Anna

Giordani

piazza Leopardi, 3

Torino

Così ristrutturata, la tabella rispetta la 1FN, in base alla quale ogni colonna deve contenere uno e un solo attributo dell’entità rappresentata da ciascuna riga. In altri termini, il contenuto di ciascun campo deve essere atomico, ovvero non ulteriormente suddivisibile. Prendiamo un esempio un po’ più complesso. La tabella che segue, creata per raccogliere dati sulle ore lavorate da ciascun impiegato su vari progetti, non rispetta la prima forma normale, perché i tre campi Nominativo, Progetto e OreLavorate non sono atomici. Mat ricola

Nominat ivo

Proget t o

OreLavorat e

DN-26

Antonio Rossi

ALFA-1; ALFA-3; ALFA-4

25; 40; 30

DN-30

Giovanna Trizzi

ALFA-1; ALFA-3; ALFA-5

5; 35; 60

DN-35

Marco Esposito

ALFA-1; ALFA-2

15; 80

DN-36

Felice Gennari

BETA-1

90

DN-40

Elena Bantini

BETA-2

75

DN-45

Pasquale Panni

ALFA-2; BETA-3

20; 70

Con i dati organizzati in questo modo non è possibile distinguere i singoli progetti e neppure eseguire calcoli sulle ore lavorate per ciascun progetto. Una riorganizzazione dei dati in forma atomica, quindi nel rispetto della 1NF, potrebbe essere la seguente: Mat ricola

Cognome

Nome

P1

OreP1

P2

OreP2

P3

OreP3

DN-26

Rossi

Antonio

ALFA-1

25

ALFA-3

40

ALFA-4

30

DN-30

Trizzi

Giovanna

ALFA-1

5

ALFA-3

35

ALFA-5

60

DN-35

Esposito

Marco

ALFA-1

15

ALFA-2

80

DN-36

Gennari

Felice

BETA-1

90

DN-40

Bantini

Elena

BETA-2

75

DN-45

Panni

Pasquale

ALFA-2

20

BETA-3

70

Questa nuova disposizione dei dati rispetta la 1FN, ma lascia molti campi vuoti; inoltre, se a un impiegato venisse assegnato un quarto progetto, si dovrebbero aggiungere altri due campi, che resterebbero vuoti in tutti gli altri record. La situazione è simile a quella che abbiamo visto nel paragrafo “Relazione uno a molti”, quando abbiamo provato ad aggiungere i numeri di telefono a una tabella di indirizzi. Una migliore e più efficiente organizzazione dei dati richiede, in questo caso, la creazione di due tabelle, entrambe concepite nel rispetto della prima forma normale, correlate da una relazione uno a molti. Avremo quindi una tabella Dipendenti, come questa, nella quale la chiave primaria è data dal campo Matricola. Mat ricola

Cognome

Nome

DN-26

Rossi

Antonio

DN-30

Trizzi

Giovanna

DN-35

Esposito

Marco

DN-36

Gennari

Felice

DN-40

Bantini

Elena

DN-45

Panni

Pasquale

Una seconda tabella, che possiamo chiamare AvanzamentoProgetti, utilizza il campo Matricola come chiave esterna, che la correla con la tabella Dipendenti. Mat ricola

Proget t o

OreLavorat e

DN-26

ALFA-1

25

DN-26

ALFA-3

40

DN-26

ALFA-4

30

DN-30

ALFA-1

5

DN-30

ALFA-3

35

DN-30

ALFA-5

60

DN-35

ALFA-1

15

DN-35

ALFA-2

80

DN-36

BETA-1

90

DN-40

BETA-2

75

DN-45

ALFA-2

20

DN-45

BETA-3

70

Nella tabella AvanzamentoProgetti si può utilizzare una chiave primaria composta, formata dalla coppia di campi Matricola+Progetto. In sintesi, una tabella rispetta la prima forma normale quando ciascuna colonna è atomica e non vi sono gruppi di colonne ripetute. Un livello successivo di normalizzazione, la seconda forma normale (Second Normal Form o 2NF), stabilisce che le colonne di una tabella devono contenere attributi della

sola entità che viene individuata per intero dalla chiave primaria; in altri termini, una tabella non deve contenere campi con informazioni ridondanti. Supponiamo di aver bisogno di associare una descrizione a ciascun progetto; potremmo modificare la tabella AvanzamentoProgetti in questo modo: Mat ricola

Proget t o

Descrizione

OreLavorat e

DN-26

ALFA-1

Sviluppo sito web

25

DN-26

ALFA-3

Sviluppo grafica

40

DN-26

ALFA-4

Acquisizione immagini fisse

30

DN-30

ALFA-1

Sviluppo sito web

5

DN-30

ALFA-3

Sviluppo grafica

35

DN-30

ALFA-5

Acquisizione immagini dinamiche

60

DN-35

ALFA-1

Sviluppo sito web

15

DN-35

ALFA-2

Nuova interfaccia applicativa

80

DN-36

BETA-1

Documentazione sito web

90

DN-40

BETA-2

Sviluppo XML

75

DN-45

ALFA-2

Nuova interfaccia applicativa

20

DN-45

BETA-3

Ottimizzazione query

70

La tabella così modificata rispetta la 1FN, ma contiene una colonna ridondante (la Descrizione) il cui contenuto non dipende dall’intera chiave primaria (Matricola+Progetto), ma dalla sola colonna Progetto. Se la descrizione dei singoli progetti è importante, va gestita con una tabella separata, che potrebbe chiamarsi Progetti e avere questa struttura: Proget t o

Descrizione

ALFA-1

Sviluppo sito web

ALFA-2

Nuova interfaccia applicativa

ALFA-3

Sviluppo grafica

ALFA-4

Acquisizione immagini fisse

ALFA-5

Acquisizione immagini dinamiche

BETA-1

Documentazione sito web

BETA-2

Sviluppo XML

BETA-3

Ottimizzazione query

La seconda forma normale entra in gioco quando la chiave primaria è composta da più di una sola colonna. La terza forma normale (Third Normal Form o 3NF) impone un ulteriore vincolo: nessun attributo può dipendere da un attributo che non sia la chiave primaria. Se volessimo associare ai progetti alcune informazioni sui loro responsabili, l’aggiunta di un paio di colonne alla tabella Progetti fatta nel modo seguente. Proget t o

Descrizione

IDResponsabile

Responsabile

ALFA-1

Sviluppo sito web

G-1

Bianchini

ALFA-2

Nuova interfaccia applicativa

G-2

Gabetti

ALFA-3

Sviluppo grafica

G-2

Gabetti

ALFA-4

Acquisizione immagini fisse

G-2

Gabetti

ALFA-5

Acquisizione immagini dinamiche

G-2

Gabetti

BETA-1

Documentazione sito web

G-3

Russo

BETA-2

Sviluppo XML

G-1

Bianchini

BETA-3

Ottimizzazione query

G-1

Bianchini

violerebbe la 3NF, perché l’attributo Responsabile non dipende dalla chiave primaria Progetto, ma dalla colonna/attributo IDResponsabile. I dati sui responsabili dei progetti vanno quindi gestiti con una tabella separata, che si colloca dal lato uno di una relazione uno a molti con la tabella Progetti, in questo modo: IDResponsabile

Responsabile

G-1

Bianchini

G-2

Gabetti

G-3

Russo

La normalizzazione delle tabelle per arrivare al rispetto della terza forma normale produrrebbe quindi, continuando con l’esempio che stiamo illustrando, un database articolato in quattro tabelle, come si può vedere dalla Figura 2.3.

Figura 2.3 L’insieme delle tabelle rispetta i vincoli della 3NF. NOT A La teoria generale dei database relazionali prende le mosse da un articolo pubblicato da Edgar F. Codd nella rivista Communications of the ACM, volume 13, numero 6, giugno 1970, intitolato “A Relational

Model of Data for Large Shared Data Banks”. L’abstract dell’articolo è molto illuminante, come si può vedere qui di seguito, e anticipa la nascita dello Structured Query Language. “Gli utenti futuri di grandi banche di dati non dovranno essere costretti a sapere in che modo i dati sono rappresentati nella macchina (la loro rappresentazione interna). Un servizio di suggerimenti che fornisca questo tipo di informazioni non è una soluzione soddisfacente. Le attività deli utenti ai terminali e la maggior parte dei programmi applicativi non dovrebbero risentire dei cambiamenti delle rappresentazioni interne e e neppure quando vengono cambiati alcuni aspetti della rappresentazione esterna. Saranno spesso necessari cambi nella rappresentazione dei dati in conseguenza di cambiamenti nel traffico di interrogazioni, aggiornamenti e report e della crescita naturale dei tipi di informazioni immagazzinate. Esistono sistemi per la gestione non inferenziale di dati formattati che forniscono agli utenti file con strutture ad albero o modelli reticolari dei dati leggermente più generali. Nella Sezione 1, si discutono le inadeguatezze di questi sistemi. Vengono introdotti un modello basato su relazioni n_arie, una forma normale per le operazioni sulle relazioni di una base di dati e il concetto di un sottolinguaggio universale per i dati. Nella Sezione 2 alcune operazioni sulle relazioni (diverse dall’inferenza logica) vengono discusse e applicate al problema della ridondanza e della coerenza del modello dell’utente.” Codd ha continuato a scrivere sull’argomento, approfondendo in particolare le forme normali, che con il suo contributo concettuale e quello di altri studiosi di logica e matematica si sono arricchite di ulteriori concetti di base, troppo impegnativi per essere descritti in questa sede. I lettori che fossero interessati all’argomento possono leggere con profitto il volume scritto dallo stesso Codd nel 1990 e intitolato The relational model for database management: version 2¸ Addison-Wesley Longman Publishing Co., Inc., Boston.

Va tenuto presente che le regole previste dalle forme normali sono cumulative: affinché le tabelle di un database rispettino la 3NF devono soddisfare anche i requisiti della 2NF e della 1NF. I teorici dei database relazionali hanno individuato altre forme normali oltre a quelle che abbiamo illustrato, ma nella prassi comune il rispetto delle prime tre è più che sufficiente per ottenere un database normalizzato. Rappresent are le relazioni fra t abelle Nelle figure precedenti abbiamo utilizzato una semplice ma efficace convenzione grafica per rappresentare le relazioni fra tabelle in un database: uno spezzone di linea unisce due tabelle collocando la cifra 1 all’estremità che tocca la tabella che si trova nel lato “uno” della relazione e il segno dell’infinito ∞ all’estremità che tocca la tabella che sta nel lato “molti” della medesima relazione. Questi elementi grafici rappresentano le relazioni fra tabelle presenti nei database di Access, uno strumento per creare e gestire database relazionali che fa parte del pacchetto Office di Microsoft. Anche in MySQL e in SQL Server, i due sistemi per la gestione di database che utilizziamo a scopo dimostrativo in questo libro, è disponibile un’interfaccia grafica che rappresenta in modo simbolico le relazioni fra tabelle, come possiamo vedere nella figura che segue.

Nella progettazione di tabelle e database, l’aiuto concettuale che deriva dalle forme normali è importante, ma l’orientamento principale deve venire da un’accurata analisi dei risultati che si vogliono ottenere; un database non è un fine in sé, ma uno strumento per gestire agevolmente dati che si utilizzano per produrre o vendere merci, per esempio, o per amministrare personale, impianti, magazzini e risorse in genere: in una tabella con righe che rappresentano impiegati, una colonna che contiene l’attributo Età può rispettare tutte e tre le principali forme normali, ma i dati in quella colonna saranno privi di valore un anno dopo essere stati inseriti; meglio, in questo caso, una colonna con l’attributo DataDiNascita, i cui valori possono essere utilizzati in seguito per ricavare correttamente, con una semplice operazione aritmetica, l’età di ciascun impiegato ogni volta che serve. Prendendo spunto da questo semplice esempio, è bene evitare di inserire colonne che contengono valori calcolati. In una tabella con situazioni di magazzino come quella che vediamo qui di seguito. Codice

Descrizione

Quant it à

ValoreUnit ario

ValoreTot ale

BZ05

Bullone zincato 5x10

5.000

€ 0,15

€ 750,00

BZ06

Bullone zincato 6x10

6.000

€ 0,16

€ 960,00

BZ08

Bullone zincato 8x10

4.500

€ 0,18

€ 810,00

BZ10

Bullone zincato10x12

2.800

€ 0,20

€ 560,00

BZ12

Bullone zincato 12x18

3.000

€ 0,22

€ 660,00

RP52

Rondella piana 5x2

9.000

€ 0,05

€ 450,00

RP53

Rondella piana 5x3

9.000

€ 0,05

€ 450,00

RP54

Rondella piana 5x4

8.000

€ 0,05

€ 400,00

RP55

Rondella piana 5x5

7.000

€ 0,06

€ 420,00

RP56

Rondella piana 5x6

5.000

€ 0,07

€ 350,00

DZ05

Dado esag zinc 5

5.500

€ 0,15

€ 825,00

DZ06

Dado esag zinc 6

6.500

€ 0,16

€ 1.040,00

DZ08

Dado esag zinc 8

5.000

€ 0,18

€ 900,00

DZ10

Dado esag zinc 10

3.000

€ 0,20

€ 600,00

DZ12

Dado esag zinc 12

3.200

€ 0,22

€ 704,00

la colonna ValoreTotale è in contrasto con la terza forma normale, perché il suo contenuto dipende da quello delle colonne Quantità e ValoreUnitario, e questo già ne giustificherebbe l’eliminazione. Ma a parte questa considerazione formale, la sua presenza rende la tabella difficile da gestire, perché i contenuti andrebbero ricalcolati ogni volta che interviene una modifica nella quantità o nel valore unitario: meglio, quindi, eliminarla del tutto e generare per altra via il valore totale ogni volta che serve; vedremo più avanti come si eseguono calcoli di questo tipo con gli strumenti dello Structured Query Language.

Le interfacce utente I due sistemi per la gestione di database relazionali che abbiamo presentato all’inizio di questo libro – MySQL e SQL Server – sono dotati di numerose funzionalità che poco hanno a che fare con il linguaggio SQL in senso stretto, ma sono concepite per dare servizi con i quali integrare i database creati con il linguaggio SQL in un contesto di gestione aziendale che può essere anche molto complesso, articolandosi eventualmente in molteplici aggregazioni di server e di client. La presenza di tutte queste funzionalità per integrare database creati con SQL in un contesto di sistemi informatici per la gestione aziendale spiega perché le due interfacce utente, Workbench di MySQL e Management Studio di SQL, contengano un numero così elevato di barre degli strumenti, finestre di dialogo e altro ancora. Tutta questa strumentazione non è rilevante ai fini del nostro libro, dedicato com’è all’uso di SQL in riferimento a grandi basi di dati e all’elaborazione con Excel dei risultati che si possono ottenere con questo linguaggio. Per evitare confusioni e distrazioni, quindi, conviene impostare il modo in cui si presentano le due interfacce utente che abbiamo a disposizione procedendo in questo modo.

MySQL Workbench Aprire la finestra dell’applicazione da Windows 10, che potrebbe presentarsi articolata in tre finestre di dialogo sormontate da una barra degli strumenti, come possiamo vedere nella Figura 2.4.

Figura 2.4 La schermata iniziale di MySQL Workbench presenta tre finestre, una barra dei menu e una barra degli strumenti.

Notiamo una finestra che si sviluppa in verticale occupando tutta la fascia di sinistra della schermata e intitolata Navigator; alla destra di Navigator è presente, in posizione centrale, una finestra chiamata Query 1, suddivisa in due sezioni, quella inferiore si chiama Output; sulla destra, infine, compare una terza finestra intitolata SQLAdditions. All’estremità destra della barra degli strumenti sono presenti quattro pulsanti di comando:

Un clic sul primo pulsante, che ha l’icona di un volante, fa aprire una articolata finestra di dialogo intitolata Workbench Preferences, che – come il suo nome lascia intuire – dà la possibilità di intervenire sugli aspetti e il modo di operare dello strumento che stiamo usando: è opportuno chiudere questa finestra accettando tutte le impostazioni predefinite. I tre pulsanti di comando che seguono sono dei commutatori: un clic apre (se è chiusa) e chiude (se è aperta) la finestra di dialogo che corrisponde all’immagine riportata su ciascun pulsante.

Il suggerimento che diamo è agire su questi pulsanti in modo che compaia soltanto la finestra chiamata Query 1, suddivisa in due fasce orizzontali, come nella Figura 2.5.

Figura 2.5 La finestra di lavoro preferenziale di MySQL Workbvench.

SQL Server Management Studio All’apertura questa interfaccia presenta una sola finestra spostata verso il lato sinistro e chiamata Esplora oggetti, nella quale sono rappresentati i database esistenti, con la stessa convenzione grafica che si usa in Esplora risorse di Windows per rappresentare le cartelle in un disco rigido. Sopra questa finestra campeggiano una barra dei menu e una barra degli strumenti. Le finestre che compaiono in questa interfaccia si possono chiudere con un clic sul pulsante contrassegnato da una X all’estremità destra del loro titolo. Conviene chiudere in questo modo la finestra Esplora oggetti e facendo scendere le opzioni disponibili dal menu Visualizza far aprire la finestra Output, che si andrà a collocare nella parte bassa della finestra principale, occupandola per intero. Premendo il pulsante di comando Nuova query che si trova nella barra degli strumenti si ottiene l’apertura della finestra di lavoro che ci interessa, ottenendo un risultato che vediamo nella Figura 2.6.

Figura 2.6 L’interfaccia utente Management Studio di SQL Server è pronta per ricevere ed eseguire comandi SQL.

Dopo aver predisposto nel modo appena descritto le interfacce degli strumenti di lavoro possiamo procedere adesso a studiare come si usa in concreto lo Structured Query Language.

Capitolo 3

Database e tabelle

Lo standard dello Structured Query Language non prevede un comando per creare un database; la circostanza può sembrare curiosa o strana, ma ha una sua logica: questo linguaggio è uno strumento per creare, gestire e utilizzare tabelle, il database è l’oggetto software che le contiene e non fa parte delle finalità di SQL definire le caratteristiche e il funzionamento di questo contenitore. Dato, però, che un database deve pur esistere, tutte le implementazioni di SQL prevedono almeno un comando per creare un nuovo database vuoto e qualche altro comando per gestire database esistenti considerati come oggetti singoli. L’esecuzione dei comandi per creare database sia in MySQL sia in SQL Server genera una struttura di dati più o meno complessa, il cui schema può essere esaminato direttamente nella finestra Esplora oggetti del Management Studio di SQL Server e nella finestra Navigator del Workbench di MySQL dopo aver selezionato in questa la scheda Schemas. Nella Figura 3.1 vediamo un esempio delle diverse rappresentazioni dei database nei due sistemi. Quando si crea un database MySQL in ambiente Windows viene semplicemente creata una cartella vuota, al cui interno si inseriranno file particolari generati dai comandi per creare le tabelle; lavorando con SQL Server, il comando di creazione di un database genera strutture di dati più complesse di una semplice cartella Windows. È comunque sconsigliabile andare a frugare nel disco di sistema del proprio computer per individuare ed eventualmente esaminare i file dei database e i loro componenti, perché si rischierebbe di devastare le strutture dei dati senza ricavarne alcun vantaggio.

Figura 3.1 La rappresentazione grafica delle strutture dei database presenti in MySQL e in SQL Server.

Convenzioni per la descrizione della sintassi Come tutti i linguaggi di programmazione, lo Structured Query Language ha un lessico e una sintassi, che consistono l’uno di un centinaio di parole chiave e l’altra di qualche decina di regole per comporre con tali parole chiave e opportune stringhe di caratteri le istruzioni per usare i database. Le parole chiave si possono scrivere indifferentemente in maiuscolo o in minuscolo, in tutte le implementazioni di SQL: per chiarezza, utilizzeremo le seguenti convenzioni tipografiche nel descrivere la sintassi: le parole chiave sono in MAIUSCOLO : CREATE TABLE

gli elementi sui quali agiscono i comandi, in generale nomi di tabelle e di colonne, sono scritti in corsivo: CREATE TABLE nome_tabella

un elemento facoltativo, parola chiave o nome, è racchiuso fra parentesi [ quadre ]: CREATE DATABASE [IF NOT EXISTS] nome_database

quando un’istruzione può utilizzare uno fra più elementi in alternativa, questi sono separati da barre | verticali |; quando è facoltativo scegliere uno fra gli elementi disponibili in alternativa, l’elenco è racchiuso fra parentesi [ quadre ]: DROP TABLE nome_tabella [RESTRICT | CASCADE]

quando è obbligatorio scegliere uno fra più elementi disponibili in alternativa, l’elenco è racchiuso fra parentesi { graffe }: UPDATE { nome_tabella | nome_vista}

In diverse istruzioni un elemento può essere ripetuto più volte; questa caratteristica viene rappresentata tipograficamente in questo modo: col1, col2,…

Quando in uno schema sintattico sono presenti parentesi tonde e virgole, questi caratteri fanno parte dell’istruzione e quindi vanno scritti come indicato, mentre le barrette verticali, le parentesi quadre e le graffe non si devono scrivere. Una istruzione SQL può essere molto lunga, sviluppandosi su più righe, che possono andare a capo in qualunque punto in cui lo schema sintattico prevede uno spazio, mentre le parole chiave e i nomi degli elementi non si possono dividere con un a capo. Lo standard SQL prevede che ogni istruzione debba terminare con il carattere punto e virgola, ma quasi tutte le implementazioni del linguaggio – comprese MySQL e SQL

Server – hanno un’interfaccia che accetta le istruzioni anche se non sono chiuse da un punto e virgola. Dimensioni delle t abelle Lo standard SQL non impone vincoli sulle dimensioni delle tabelle e sul numero delle loro colonne, ma le specifiche implementazioni del linguaggio fissano limiti sia al numero di colonne che possono comporre una riga, sia alle dimensioni delle singole colonne. Nel caso degli strumenti di cui ci occupiamo, i limiti sono praticamente infiniti, perché le tabelle di questi RDBMS sono semplici file creati nel rispetto dei vincoli del sistema operativo col quale si sta lavorando. Nulla impedirebbe, perciò, di avere una tabella di un gigabyte, ma una dimensione così spropositata creerebbe sicuramente problemi in fase di elaborazione. Inoltre, le informazioni da gestire con un database vanno sempre organizzate su più tabelle, ciascuna contenente dati omogenei e che appartengono a una determinata famiglia: per esempio, una tabella per i clienti, con informazioni su nomi e indirizzi; un’altra per gli ordini; una diversa tabella per i prodotti che si vendono, con dati su caratteristiche, prezzo e disponibilità e così via. Raggruppando i dati in tabelle omogenee e separate, le loro dimensioni dovrebbero risultare ragionevolmente contenute.

Creare un database Le istruzioni di SQL si scrivono in una finestra dell’interfaccia utente specializzata per la composizione delle istruzioni, che è chiamata Query in entrambi i sistemi che stiamo utilizzando; in tale contesto il sistema riconosce le parole chiave quando vengono scritte e le evidenzia in un colore celeste, lasciando in nero le altre parole che completano l’istruzione. Dopo aver scritto il seguente comando: CREATE DATABASE Prova

diamo l’ordine di esecuzione, che in SQL Server si attiva premendo il pulsante di comando Esegui affiancato da un punto esclamativo rosso che si trova nella barra degli strumenti, mentre in MySQL è il pulsante con l’icona di una saetta subito sotto il bordo superiore sinistro della finestra Query. Le schermate che otterremo potrebbero presentarsi come quelle che si vedono nella Figura 3.2.

Figura 3.2 Il comando CREATE DATABASE eseguito in MySQL e in SQL Server.

Nella parte inferiore delle finestre in cui è stato eseguito il comando compare una fascia orizzontale che dà informazioni sul risultato del comando appena eseguito. Nel caso di SQL Server il comando è stato eseguito correttamente, mentre in MySQL non è stato possibile creare un nuovo database chiamandolo Prova perché ne esiste già uno con quel nome, come spiega con chiarezza il messaggio contenuto nella fascia inferiore della finestra Query. Naturalmente se, prima di dare il comando per creare un nuovo database, avessimo dato un’occhiata alle finestre Schemas e Esplora oggetti di Workbench e di Management Studio avremmo potuto sapere se un database chiamato Prova esisteva oppure no in uno dei due sistemi. Si possono avere informazioni sui database esistenti nei due sistemi utilizzando alcuni comandi che non fanno parte dello standard di SQL. Nel caso di MySQL il comando: SHOW DATABASES

fa comparire una finestra orizzontale al piede della finestra Query, chiamata Result, nella quale sono elencati i nomi dei database presenti nel sistema, fra i quali compare anche il nostro Prova, come possiamo vedere nella Figura 3.3.

Figura 3.3 L’elenco dei database disponibili in MySQL.

Siccome non fanno parte dello standard di SQL, i comandi per visualizzare l’elenco dei database esistenti sono diversi in SQL Server da quello che abbiamo appena visto in MySQL. In SQL Server non esiste il comando SHOW, che non fa parte dello standard del linguaggio SQL. Per avere in SQL Server un risultato simile a quello che abbiamo ottenuto prima in MySQL possiamo ricorrere a un artificio, dando l’ordine di elencare i nomi contenuti in una tabella di sistema che viene utilizzata per gestire i database: questa tabella si chiama sys.sysdatabases e dopo aver scritto nella finestra Query il comando: SELECT name FROM sys.sysdatabases

eseguendolo otteniamo il risultato che si vede nella Figura 3.4.

Figura 3.4 L’esecuzione del comando elenca i nomi di tutti i database presenti in SQL Server, compresi quelli di servizio.

In MySQL si può facilmente evitare il fastidio di scoprire che il nome di un database che vogliamo creare esiste già completando il comando CREATE DATABASE con la clausola IF NOT EXISTS

, in questo modo:

CREATE DATABASE IF NOT EXISTS Prova

Quando viene eseguito, il comando non provoca alcuna segnalazione di errore per effetto della clausola limitativa. La sintassi di questo comando è molto semplice: CREATE DATABASE [IF NOT EXISTS] nome_database

Il nome_database può essere qualunque combinazione di caratteri validi per un nome di cartella Windows, con esclusione dei caratteri /, \, e . (punto), e una lunghezza massima di 64 caratteri. Un comando speculare a quello per creare un database nuovo elimina completamente un database esistente, cioè svuota l’intero contenuto della cartella Windows che contiene

il database ed elimina la cartella stessa. La sua sintassi è elementare: DROP DATABASE [IF EXISTS] nome_database

La clausola facoltativa IF EXISTS serve, naturalmente, per prevenire una eventuale segnalazione di errore. Il comando va dato con cautela, perché la sua esecuzione è irreversibile. Le due clausole IF NOT EXISTS e IF EXISTS non sono disponibili nella versione SQL Server del linguaggio SQL. Per utilizzare un database esistente, bisogna attivarlo, cosa che si ottiene col comando: USE nome_database

Creare tabelle L’istruzione CREATE TABLE è una delle colonne portanti della componente Data Definition Language di SQL. Lo standard SQL99 per questa istruzione è il seguente: CREATE [GLOBAL TEMPORARY | LOCAL TEMPORARY] TABLE nome_tabella [ON COMMIT {PRESERVE ROWS | DELETE ROWS}] (nome_colonna tipo_dati specifiche, [nome_colonna tipo_dati specifiche2,...]) | [LIKE nome_tabella] | [vincolo_tabella][,...n] ]

Tutte le implementazioni di SQL aggiungono numerose altre opzioni a quelle previste dallo standard. Qui vedremo nei particolari come si utilizza il comando CREATE TABLE in MySQL. Per semplicità espositiva procederemo per gradi, presentando prima le funzionalità di base e in seguito le opzioni più complesse. Una tabella deve avere un nome e almeno una colonna, che è identificata anch’essa con un nome (diverso da quello della tabella); dato che le colonne sono contenitori per i dati, per ogni colonna bisogna indicare quale tipo di dato dovrà contenere, precisando anche una o più specifiche che caratterizzano quel tipo di dato. Quindi, ridotta al suo schema essenziale, l’istruzione CREATE TABLE ha questa sintassi: CREATE TABLE nome_tabella (nome_colonna tipo_dati specifiche, [nome_colonna tipo_dati specifiche2,...])

Proviamo quindi a creare con MySQL una tabella nel database Prova, che chiameremo Nomi, articolata su due colonne, Nome e Cognome. L’enunciato necessario per ottenere questo risultato può essere il seguente: CREATE TABLE Nomi (Nome CHAR (15), Cognome CHAR (20))

Nell’esempio che stiamo vedendo, dopo i due elementi nome_colonna, che valgono rispettivamente Nome e Cognome, troviamo il tipo_dati, che per entrambi è CHAR , una parola chiave che deriva dall’abbreviazione della parola inglese character: con questa impostazione si indica che la colonna è destinata a contenere caratteri di testo (che saranno considerati come tali anche se fossero numeri). La specifica, che segue CHAR e va scritta fra parentesi, indica la lunghezza del campo in caratteri. Il tipo di dato CHAR , infatti, è a lunghezza fissa e tale lunghezza va indicata espressamente nella definizione del tipo di dato. Se la si omette, viene impostata una lunghezza predefinita. Eseguiamo l’enunciato dalla Query Window e quindi verifichiamo che la tabella sia stata effettivamente creata eseguendo il comando: SHOW TABLES

Nella scheda Result compare il nome della sola tabella attualmente presente nel database Prova (Figura 3.5).

Figura 3.5 Col comando SHOW TABLES si ottiene un elenco delle tabelle presenti nel database attivo.

Il comando SHOW non fa parte dello standard di SQL, ma è disponibile con varie forme in più implementazioni di SQL. Nel caso di MySQL, può essere utilizzato, facendolo seguire da un’opportuna parola chiave, per avere un elenco dei database, delle tabelle in un database, delle colonne di una tabella e così via. Può dimostrarsi molto comodo in situazioni reali, quando si ha bisogno di controllare rapidamente le caratteristiche di un componente del database, prima di accingersi a creare un enunciato complesso, o quando si hanno segnalazioni di errore e si vuol controllare di aver utilizzato correttamente i nomi dei vari elementi. Possiamo esaminare la struttura della tabella che abbiamo appena creato dando il comando DESCRIBE Nomi

Anche il comando DESCRIBE, come SHOW, non è incluso nello standard di SQL, ma è disponibile in MySQL per agevolare il controllo delle strutture dei dati. L’esecuzione del comando visualizza nella scheda Result lo schema che vediamo riprodotto nella Figura 3.6.

Oltre a confermarci quello che già sappiamo, e cioè che la tabella Nomi appena costruita contiene due campi, Nome e Cognome, entrambi di tipo CHAR e lunghi rispettivamente 15 e 20 caratteri, il comando DESCRIBE Nomi ci dice che: nessuno dei due campi è un indice (non compare alcuna indicazione nella colonna Key); i campi possono contenere valori NULL (YES nella colonna Null); il valore predefinito (Default) dei campi è NULL .

Figura 3.6 Il risultato del comando DESCRIBE Nomi.

Per creare tabelle in un database di SQL Server vale la stessa forma dell’istruzione CREATE TABLE che abbiamo usato in MySQL, quindi, dopo aver attivato col comando USE il database che ci interessa possiamo scrivere ed eseguire nella finestra Query lo

Prova

stesso comando che abbiamo usato in MySQL: CREATE TABLE Nomi (Nome CHAR (15), Cognome CHAR (20))

In SQL Server, però, non sono disponibili i comandi SHOW TABLES e DESCRIBE che abbiamo visto prima per avere informazioni sulle tabelle e la loro struttura. Si possono utilizzare in alternativa varie tecniche, ma per il momento accontentiamoci di una molto semplice,

che consiste nel richiamare uno strumento tipico dei sistemi per la gestione di database relazionali e che si chiama “stored procedure”: come il suo nome lascia intuire, si tratta di un documento di testo immagazzinato ovvero salvato da qualche parte nella struttura del sistema SQL Server e che contiene un certo numero di istruzioni SQL (una procedura, appunto) predisposte in modo che per eseguirle si deve richiamare il nome di questo documento facendolo seguire da un parametro. La stored procedure che ci interessa si chiama sp_columns e per eseguirla basta digitare il suo nome preceduto dal comando di esecuzione e seguito dal nome dell’oggetto sul quale deve agire, in questo modo: exec sp_columns Nomi

Otterremo subito il risultato che possiamo vedere nella Figura 3.7.

Figura 3.7 L’esecuzione della stored procedure sp_columns visualizza una serie di informazioni sulla tabella indicata.

NULL Le colonne di una tabella sono predisposte per ricevere dati di vario tipo (stringhe di caratteri alfabetici o numerici, numeri interi o decimali, date, ore e altro ancora). Oltre a questi è previsto nei database SQL il dato di tipo NULL (scritto anche Null). Non bisogna farsi trarre in inganno dal nome, pensando che sia uno spazio vuoto o uno zero. Null vuol dire “non definito” e quando un campo contiene un valore Null non è possibile elaborare questo contenuto, se non con operazioni logiche per verificare se sia davvero un Null oppure no. Non è neppure pensabile eseguire confronti fra campi con valori NULL, perché ciascun Null è

diverso da ogni altro. Se si calcolano valori aggregando più campi, per esempio si fa una sommatoria o una media, quelli che contengono valori Null vengono ignorati. Ma allora, ci si potrebbe chiedere, a che cosa serve e perché esiste? Esiste perché non è possibile generare fisicamente una tabella senza predisporre uno spazio per ciascuna colonna e serve per occupare lo spazio così predisposto con un ideale gettone vuoto, in attesa di inserirvi un dato significativo. Se in una tabella composta da dati sui prodotti un record contiene un valore Null nel campo Prezzo, non vuol dire che quel prodotto è gratis, ma che il suo prezzo non è stato ancora definito.

La tabella Nomi che abbiamo appena creato è un guscio vuoto, uno schema che può contenere due gruppi di caratteri di testo: per l’esattezza, 15 per il campo Nome e 20 per il campo Cognome. Per inserire dati nella tabella Nomi abbiamo bisogno di un’altra istruzione SQL, chiamata INSERT, la cui sintassi, nella sua forma più semplice, è: INSERT nome_tabella VALUES (valore_effettivo, . . .)

dove nome_tabella non ha bisogno di spiegazioni, la clausola VALUES indica che si intende immettere direttamente i valori nella tabella e valore_effettivo, sono i valori da scrivere fra parentesi tonde, immediatamente dopo VALUES , uno dopo l’altro, separandoli con virgole. Trattandosi, nell’esempio, di stringhe di caratteri (i campi sono di tipo CHAR ), i valori vanno scritti fra virgolette doppie o semplici. Digitiamo quindi questo comando: INSERT Nomi VALUES ("Antonio", "Rossi")

L’esecuzione del comando provoca soltanto una segnalazione di conferma dall’interfaccia grafica. Per verificare se effettivamente il primo (e per ora unico) record della tabella Nomi contiene i dati che abbiamo indicato col comando INSERT, dobbiamo visualizzare il contenuto della tabella, cosa che si ottiene con il comando SELECT, che è il comando principe di SQL, appartiene all’insieme dei comandi che formano il Data Manipulation Language (DML) e, come dice il suo nome, serve per selezionare dati da una tabella. Siccome si utilizza per interrogare un database, un enunciato basato sul comando SELECT si chiama genericamente query, che in inglese vuol dire interrogazione. La sintassi base del comando SELECT è: SELECT qualcosa FROM nome_tabella

dove qualcosa è una specifica (il nome di un campo, per esempio) di quello che si vuole estrarre dalla tabella indicata in nome_tabella. Se si usa un asterisco (*) come valore per qualcosa, si intende tutto il contenuto della tabella: ed è quello che vogliamo ottenere. Digitiamo quindi ed eseguiamo il comando: SELECT * FROM Nomi

col quale otterremo il risultato che si può vedere nella Figura 3.8.

Figura 3.8 Il comando SELECT estrae l’intero contenuto della tabella Nomi.

Proviamo ora a inserire un nuovo record, ricorrendo all’istruzione INSERT formulata nel modo seguente: INSERT Nomi VALUES ("Gianfilippo Aristide Maria", "Di Roncisvalle della Gherardesca")

Anche se abbiamo digitato con cura l’istruzione senza errori di battitura questa non viene eseguita e nella fascia inferiore della finestra Query compare la segnalazione di errore che vediamo riprodotta nella parte superiore della Figura 3.9. In SQL Server valgono le stesse regole che abbiamo descritto per creare campi e inserire valori in una tabella MySQL, per cui il tentativo di inserire nei campi stringhe di lunghezza superiore a quella definita con i parametri CHAR provoca lo stesso errore, che viene segnalato nella parte inferiore della Figura 3.9.

Figura 3.9 Le due diverse segnalazioni di errore da parte di MySQL e di SQL Server per lo stesso errore.

I tipi di dati per le colonne Una tabella in MySQL può contenere un massimo di 4096 colonne, con un ulteriore limite di 65.535 byte per riga. Se non si vuole correre il rischio di perdere informazioni quando si inseriscono dati in una tabella, è necessario scegliere con molta attenzione il tipo dei dati. Un campo destinato a contenere una stringa di caratteri in una tabella SQL può contenere da 1 a 255 caratteri. Quando lo si definisce come tipo CHAR , specificando una lunghezza, questa sarà la lunghezza massima effettiva e il campo occuperà sempre lo stesso spazio su disco, anche quando contiene una stringa di lunghezza inferiore. In altri termini, i campi di tipo CHAR sono a lunghezza fissa. Esiste un altro tipo di dato per memorizzare stringhe di caratteri, chiamato VARCHAR , che utilizza meglio lo spazio su disco. Nell’assegnare un tipo VARCHAR a un campo, si specifica una lunghezza, che non viene considerata fissa, ma rappresenta un limite superiore. Cioè, i campi di tipo VARCHAR sono a lunghezza variabile. In pratica, un campo definito come occuperà sempre 50 caratteri su disco, che ne contenga uno o 50, mentre un

CHAR(50)

campo VARCHAR(50) impegnerà su disco soltanto lo spazio effettivamente richiesto dai dati che contiene, fino al limite superiore di 50 caratteri Se prevediamo di dover inserire occasionalmente stringhe molto lunghe nei campi Nome e Cognome della nostra tabella Nomi, utilizzeremo meglio lo spazio sul disco assegnando una dimensione molto grande, diciamo 50 caratteri, ai campi Nome e Cognome, ma scegliendo il tipo VARCHAR invece del tipo CHAR : in questo modo, nomi e cognomi occuperanno ogni volta soltanto lo spazio corrispondente alla loro lunghezza effettiva, quale che sia. Come si può facilmente capire, è molto importante definire in modo appropriato il tipo di dato per ciascuna colonna che si crea in una tabella con l’istruzione CREATE TABLE: la Tabella 3.1 riepiloga i tipi di dati disponibili in MySQL e in SQL Server. Tabella 3.1 I tipi di dati utilizzabili nello Structured Query Language. MySQL e SQL Server Nome

Funzione

BIGINT

Numeri interi compresi tra ‒263 e + 263 ‒ 1

BINARY

Valori binari con lunghezza fissa fino a 8000 byte.

BIT

Numeri interi con valore 1 o 0.

CHAR

Stringhe di caratteri non Unicode con lunghezza fissa fino a 8000 caratteri

DATETIME

Valori per data e ora compresi tra il 1° gennaio 1753 e il 31 dicembre 9999

DECIMAL

Valori numerici con scala e precisione fisse, compresi tra -1038 +1 e 1038 ‒ 1.

FLOAT

Valori numerici a virgola mobile compresi tra ‒1,79E + 308 e 1,79E + 308.

INT

Numeri interi compresi tra ‒231 e 231 ‒ 1

SMALLINT

Numeri interi compresi tra ‒215 e 215 ‒ 1

TEXT

Stringhe di caratteri non Unicode con lunghezza variabile fino a 231 ‒ 1 caratteri

TIMESTAMP

Numero univoco in tutto il database che viene aggiornato ogni volta che si aggiorna una riga.

TINYINT

Numeri interi compresi tra 0 e 255

VARCHAR

Stringhe di caratteri non Unicode con lunghezza variabile fino a 8000 caratteri

VARBINARY

Valori binari con lunghezza variabile fino a 8.000 byte.

Solo SQL Server Nome

Funzione

IMAGE

Valori binari con lunghezza variabile fino a 231 ‒ 1 byte

MONEY

Valori monetari compresi tra ‒263 e 263 ‒ 1

SMALLMONEY

Valori monetari da ‒214748.3648 a 214748.3647

NCHAR

Stringhe di caratteri Unicode con lunghezza fissa fino ai 4000 caratteri.

NTEXT

Stringhe di caratteri Unicode con lunghezza variabile fino a 230 ‒ 1 caratteri

NUMERIC

Equivalente a DECIMAL.

NVARCHAR

Stringhe di caratteri Unicode con lunghezza variabile fino a 4000 caratteri.

REAL

Valori numerici a virgola mobile compresi tra ‒3,40E + 38 e 3,40E + 38

SMALLDATETIME

Valori per data e ora compresi tra il 1° gennaio 1900 e il 6 giugno 2079

UNIQUEIDENTIFIER

Identificatore univoco globale (GUID, Globally Unique IDentifier).

Solo MySQL Nome

Funzione

BLOB

Valori binari fino a 65535 caratteri

DATE

Valori per date da ‘1000-01-01’ (1° gennaio 1000) a ‘9999-12-31’ (31 dicembre 9999).

DOUBLE

Valori numerici a virgola mobile di dimensione doppia rispetto a FLOAT

ENUM

Elenco di valori (fino a 65535) che possono essere assegnati separatamente

LONGBLOB

Valori binari fino a 4 gigabyte

LONGTEXT

Valori di testo fino a 4 gigabyte

MEDIUMBLOB

Valori binari fino a 16.777.215 caratteri

MEDIUMTEXT

Valori di testo fino a 16.777.215 caratteri

SET

Elenco di valori (fino a 64) che possono essere assegnati separatamente

TIME

Un valore di tempo (ore, minuti e secondi) che va da ‘‒838:59:59’ a ‘838:59:59’.

TINYBLOB

Valore binario fino a 255 caratteri

TINYTEXT

Valore di testo fino a 255 caratteri

YEAR

Rappresenta, su quattro cifre, un anno compreso fra 1901 e 2155

Esplora ogget t i

La finestra Esplora oggetti del Management Studio di SQL Server offre un comodo promemoria per individuare i nomi e le funzioni dei tipi di dati. Si ottiene questo servizio facendo clic sul pulsante che sta a sinistra del nome di un qualsiasi database, nell’elenco che scende si fa la stessa operazione sul pulsante a fianco di Programmabilità e nell’ulteriore elenco a discesa che si apre si fa clic sul pulsante Tipi, arrivando così a un ulteriore livello nel quale sono incolonnati i nomi delle famiglie dei tipi di dati; un clic sul nome di una di queste famiglie fa uscire l’elenco dei nomi dei tipi corrispondenti: quando si porta il puntatore del mouse su uno di questi nomi, senza fare clic, dopo un istante compare una concisa descrizione della funzione di quel tipo di dato.

Per alcuni tipi di dati numerici si devono indicare fra parentesi due parametri, il primo indica la precisione e il secondo la scala. Per esempio DECIMAL(5,2)

definisce un numero decimale con precisione 5 e scala 2: “precisione” indica il numero di cifre significative che verranno memorizzate per i valori, mentre “scala” rappresenta il numero di cifre che vengono dopo la virgola decimale; in una colonna con tipo dati DECIMAL (5,2) i valori possibili vanno da -99,99 a 999,99 (il segno meno occupa una posizione fra quelle indicate dal parametro “precisione”). Il tipo di dati DECIMAL si utilizza per memorizzare importi in denaro, non suscettibili di arrotondamenti,

mentre i vari tipi a virgola mobile (FLOAT, DOUBLE, DOUBLE PRECISION, REAL ) memorizzano valori numerici potenzialmente molto grandi o molto piccoli con la tecnica della notazione scientifica, che introduce sempre qualche tipo di arrotondamento. I tipi di dati BLOB (da Large Binary Object) si riferiscono a file in formato binario, che normalmente contengono immagini o brani musicali. Lo standard SQL prevede un tipo di dati Boolean, per conservare valori logici di tipo Vero o Falso. Questo tipo di dato non esiste in MySQL né in SQL Server e la sua funzione si ottiene con il tipo TINYINT. Not azione scient ifica o a virgola mobile La notazione scientifica si utilizza nei calcoli di ingegneria, astronomia, fisica e di altre scienze per rappresentare in forma compatta grandezze molto elevate o molto piccole. La notazione scientifica rappresenta i numeri mediante lo schema mmmE±eee

dove mmm è una mantissa, che esprime la parte significativa del numero, E è un simbolo ed eee è un esponente utilizzato per indicare l’ordine di grandezza del numero. Per ottenere un numero espresso con questo tipo di notazione si moltiplica la parte a sinistra della E per 10 elevato alla potenza espressa dal numero che sta a destra. Se il numero in notazione scientifica contiene un segno negativo dopo la E, la trasformazione si effettua dividendo il numero che sta a sinistra della E per 10 elevato al numero che sta a destra. Per esempio, 5,83E+5 è uguale a 5,83 moltiplicato per 10 alla 5, cioè 5,83 moltiplicato per 100.000 ovvero 583.000. Il numero 3,82E-6 rappresenta 3,82 diviso 1.000.000, cioè 0,00000382. La notazione scientifica è detta anche rappresentazione a virgola mobile (in inglese floating point) perché sposta la posizione della virgola decimale in funzione del valore espresso a destra del simbolo E. La maggior compattezza che si ottiene con questo tipo di rappresentazione risulta evidente quando si osserva che con questi nove caratteri 5,83E+130 si definisce il numero 583 seguito da 128 zeri. Nei calcoli che si eseguono su numeri reali espressi con la virgola mobile, quando i numeri sono di tipo FLOAT, MySQL e SQL Server tengono conto dei decimali fino alla trentottesima posizione; per i numeri di tipo

DOUBLE

le posizioni decimali considerate arrivano fino a 308. Si tratta, come si vede, di limiti

estremamente elevati, che ha senso prendere in considerazione soltanto quando si creano tabelle per memorizzare dati destinati a calcoli scientifici molto raffinati. Nei database che si utilizzano per le normali applicazioni gestionali non è il caso di utilizzare i tipi di dati FLOAT e DOUBLE, ma quando servono numeri decimali è meglio ricorrere al tipo DECIMAL , che consente di conservare dati in forme decimali prestabilite e senza arrotondamenti.

Creare una tabella complessa L’esempio della tabella Nomi che abbiamo visto in precedenza è servito giusto per iniziare a prendere confidenza con il comando CREATE TABLE; nella realtà i database SQL contengono di solito tabelle articolate in più di due colonne, con vari tipi di dati. Vediamo, quindi, come si presenta un enunciato SQL complesso, per creare una tabella

significativa. La tabella, che chiameremo Movimenti, conterrà dati su operazioni bancarie. Nella finestra Query digitiamo il seguente enunciato: CREATE TABLE Movimenti (Codice INT NOT NULL PRIMARY KEY AUTO_INCREMENT, Banca CHAR(8) NULL, Conto CHAR(5) NULL, Tipo CHAR(3) NULL, Num CHAR(3) NULL, Valuta DATETIME NULL, Importo DECIMAL(8,2) NOT NULL, Causale VARCHAR(20) NULL, Descrizione VARCHAR(50) NULL)

Eseguiamo questo comando nella finestra Query del Workbench di MySQL, e facciamo la stessa cosa nella finestra Query del Management Studio di SQL Server, dopo aver cambiato nel modo evidenziato in grassetto la seconda riga dell’enunciato: CREATE TABLE Movimenti (Codice INT IDENTITY(1,5) PRIMARY KEY, Banca CHAR(8) NULL, Conto CHAR(5) NULL, Tipo CHAR(3) NULL, Num CHAR(3) NULL, Valuta DATETIME NULL, Importo DECIMAL(8, 2) NOT NULL, Causale VARCHAR(20) NULL, Descrizione VARCHAR(50) NULL)

quindi verifichiamo la struttura della tabella Movimenti che dovrebbe presentarsi come nella Figura 3.10.

Figura 3.10 La struttura della tabella Movimenti nei due sistemi.

L’enunciato CREATE TABLE che abbiamo utilizzato ha la stessa struttura logica di quello usato per creare la tabella Nomi, con alcune differenze di contenuto che hanno il seguente significato: sono stati definiti nove campi o colonne, chiamati Codice, Banca, Conto, Tipo, Num, Valuta, Importo, Causale e Descrizione; il tipo dati dei campi Tipo e Num è CHAR , con una lunghezza fissa di tre caratteri; i campi Banca, Conto, Causale e Descrizione hanno il tipo dati VARCHAR , con lunghezze massime variabili caso per caso; il tipo dati del campo Valuta (che è una data, la data di valuta dell’operazione bancaria) è DATETIME, per il quale non si richiedono indicazioni di lunghezza; il tipo dati del campo Importo è DECIMAL , le indicazioni fra parentesi si riferiscono, la prima, alla lunghezza complessiva del campo (8 caratteri) e la seconda al numero dei decimali (2), per cui il numero massimo che può essere contenuto nel campo Importo così definito è 999999.99;

tutti i campi ammettono valori NULL , a eccezione di Importo e Codice; il valore predefinito (Default) per i campi diversi da Importo è NULL , mentre è 0.00 per Importo. Per il campo Codice sono state indicate alcune specifiche addizionali nella versione per MySQL: Codice INT NOT NULL PRIMARY KEY AUTO_INCREMENT

che trovano questo riscontro nella descrizione: Field Codice

Type int(11)

Null

Key PRI

Default NULL

Ext ra auto_increment

La parola chiave INT è il nome di un tipo di dati, Intero (dall’inglese integer). Pur non avendo espresso una lunghezza nel comando, la verifica segnala che sono stati predisposti 11 caratteri per questo valore. Infatti, un campo con tipo INT può contenere un numero intero nell’intervallo fra ‒2147483648 e +2147483647, che può occupare, compreso il segno, esattamente 11 caratteri. Se non si usa il segno, un campo INT può contenere un valore che va da 0 a 4294967295. La clausola NOT NULL specifica che per questo campo non sono ammessi valori NULL ; in termini operativi, nessun record di questa tabella potrà avere un valore che non sia un numero intero nel campo Codice; per questa ragione la cella Null dello schema della struttura è vuota per la colonna Codice. La clausola PRIMARY KEY stabilisce che il campo Codice avrà funzione di chiave primaria nella tabella. Abbiamo visto il concetto di chiave primaria nel paragrafo “Univocità e chiavi primarie” del Capitolo 1. Nello schema di struttura riportato nella Figura 3.10, una sigla nella colonna Key segnala che il campo corrispondente nella tabella è una “chiave”, cioè un elemento utilizzabile per accedere più agevolmente alla tabella e per gestirla. In questo caso, la sigla PRI segnala che la colonna Codice è la chiave primaria della tabella. Considerata l’importanza che assume la chiave primaria, in tutte le implementazioni di SQL si ha a disposizione una funzionalità, chiamata caso per caso con nomi diversi, che consente di generare automaticamente un valore numerico distinto e univoco per la chiave primaria ogni volta che si inserisce un nuovo record. È per ottenere questo servizio che la definizione del campo Codice viene completata con la clausola AUTO_INCREMENT, che viene indicata come Extra nella descrizione del campo dopo la creazione della tabella.

Nella versione su SQL Server, vediamo che la finestra Esplora oggetti rappresenta correttamente il campo Codice come chiave primaria, associandolo all’icona di una chiave Yale. Proviamo ora a vedere che cosa accade quando si inserisce un nuovo record nella tabella Movimenti, utilizzando il comando INSERT, che abbiamo già incontrato nelle pagine precedenti. I valori che vogliamo avere nel nuovo record sono i seguenti: Codice 1 Banca Popolare Conto 44053 Tipo ver Num 000 Valuta 2016/10/16 Importo 1671.9 Causale Locazioni Descrizione Affitto Gandini

Il comando INSERT corrispondente è fatto in questo modo: INSERT Movimenti VALUES (1, 'Popolare', '44053', 'ver', '000', '2016/10/16', 1671.9, 'Locazioni', 'Affitto Gandini')

Esistono alcuni vincoli da rispettare per immettere dati in questo modo in una tabella di un database MySQL: tutti i dati di tipo CHAR o VARCHAR vanno digitati fra virgolette semplici o doppie; i valori numerici, siano essi di tipo INT o DECIMAL , si scrivono senza virgolette. Il separatore dei decimali è sempre il punto, anche se si lavora con una versione italiana di Windows, per la quale il separatore dei decimali è la virgola; i dati di tipo DATETIME vanno immessi fra virgolette semplici e rispettando lo schema AAAA/MM/GG, cioè prima l’anno (in quattro cifre), poi il mese e infine il giorno. Dopo aver eseguito il comando INSERT visualizziamo il contenuto della tabella Movimenti con il comando SELECT * FROM Movimenti, ottenendo una schermata come quella che si vede nella Figura 3.11.

Figura 3.11 Il nuovo record nella tabella Movimenti.

I dati si ripresentano nello stesso formato col quale sono stati immessi, con alcune leggere differenze: tutti i valori di tipo CHAR e VARCHAR sono memorizzati senza virgolette; il valore Importo, scritto come 1671.9, è stato memorizzato nella forma 1671.90, cioè con due decimali, a causa dell’indicazione (8,2) associata al tipo dati DECIMAL attribuito al campo Importo; la data Valuta ha subito una trasformazione notevole: immessa come 2016/10/16 sta nel record della tabella senza virgolette e nella forma 2016-10-16 00:00:00. Le tre coppie di zeri che seguono la data rappresentano i valori hh:mm:ss, cioè l’orario espresso in ora, minuti e secondi. Questo eccesso di informazioni è determinato dal fatto che abbiamo scelto il tipo di dato DATETIME (cioè data e ora) per il campo Valuta. È probabile che, considerata la natura di questi dati, l’informazione aggiuntiva sull’ora fornita dal tipo DATETIME non sia necessaria: possiamo modificare questo tipo di dato della colonna Valuta mediante il comando ALTER TABLE, che può essere scritto in questo modo: ALTER TABLE Movimenti MODIFY Valuta DATE

Dopo aver eseguito questo comando, possiamo visualizzare il contenuto della tabella e verificheremo che adesso il campo Valuta contiene soltanto la data e non più le indicazioni di ora, minuti e secondi. ALTER TABLE è una istruzione SQL molto potente, che nella implementazione MySQL del linguaggio contiene anche clausole non previste dallo standard: in particolare, la clausola MODIFY esiste soltanto in MySQL ed è derivata da un altro importante RDBMS chiamato Oracle. Lo stesso comando in SQL Server ha questa forma: ALTER TABLE Movimenti ALTER COLUMN Valuta DATE

Proviamo ora a creare un nuovo record utilizzando una forma sintattica diversa del comando INSERT. Digitiamo: INSERT Movimenti (Banca, Conto, Tipo, Num, Valuta, Importo, Causale, Descrizione) VALUES('Popolare', '44053', 'ass', '457', '2016/10/17', -185.92, 'Acquisto servizi', 'RAS')

Eseguiamo il comando e successivamente visualizziamo il contenuto della tabella Movimenti, ottenendo il risultato che compare nella Figura 3.12.

Figura 3.12 Un secondo record è stato inserito nella tabella Movimenti.

Rispetto alla forma sintattica che abbiamo usato in precedenza: INSERT nome_tabella VALUES(valore_effettivo, . . . )

la variante del comando INSERT che abbiamo utilizzato ha la forma: INSERT nome_tabella (nome_campo, . . . ) VALUES(valore_effettivo, . . . )

Questa variante consente di specificare anche un solo nome_campo, fra parentesi tonde dopo il nome_tabella, indicando un valore_effettivo, fra parentesi tonde dopo la parola chiave VALUES per ciascun nome_campo indicato in precedenza. Gli elementi nome_campo, e valore_effettivo, vanno scritti separandoli con una virgola. Per i campi che non sono indicati, SQL inserisce il valore predefinito, che è NULL se non è stato prestabilito un vincolo NOT NULL . Nell’esempio, abbiamo specificato un valore per tutti i campi eccetto il primo, Codice. Come possiamo vedere dai risultati, trattandosi di un campo caratterizzato come chiave primaria (quindi NOT NULL per definizione) e definito come AUTO_INCREMENT, ha assunto automaticamente il valore 2. Per capire meglio come funziona il meccanismo di AUTO_INCREMENT, eseguiamo le seguenti operazioni: eliminiamo il record appena inserito e poi reinseriamolo con lo stesso enunciato INSERT di prima. Digitiamo quindi: DELETE FROM Movimenti WHERE Codice=2

In questo modo si elimina il record che ha 2 nel campo Codice. Digitiamo ed eseguiamo di nuovo il comando INSERT: INSERT Movimenti (Banca, Conto, Tipo, Num, Valuta, Importo, Causale, Descrizione) VALUES('Popolare', '44053', 'ass', '457', '2016/10/17', -185.92, 'Acquisto servizi', 'RAS')

Eseguendo successivamente la query SELECT * FROM Movimenti, otteniamo il risultato che vediamo nella Figura 3.13.

Figura 3.13 Il secondo record ha ora un valore diverso nel campo Codice.

Il valore di un campo definito come PRIMARY KEY e anche come AUTO_INCREMENT viene ottenuto incrementando di uno il valore massimo utilizzato per quel campo in occasione dell’ultimo inserimento e questo anche se il record che ha utilizzato quel valore massimo non esiste più. Nel nostro caso, l’ultimo inserimento aveva determinato l’assegnazione del valore 2 al campo Codice del record inserito. Questo record è stato eliminato e poi ne è stato inserito un altro (che per combinazione è uguale a quello eliminato, ma il fatto è irrilevante). Potremmo aspettarci che AUTO_INCREMENT sommi uno al valore di Codice dell’unico record presente (che è 1) e quindi assegni 2 allo stesso campo del nuovo record. Non è così. Essendo una chiave primaria, il campo Codice non può rischiare di avere duplicati, quindi AUTO_INCREMENT fa riferimento non all’ultimo valore esistente, ma all’ultimo utilizzato e genera automaticamente un valore diverso da quello (in questo caso, 3). Se provassimo di nuovo a eliminare il secondo record e poi ne inserissimo ancora una volta uno, uguale o diverso, il suo campo Codice conterrebbe il valore 4. In una tabella può esistere una sola colonna caratterizzata come AUTO_INCREMENT e questa deve essere una chiave, non necessariamente una chiave primaria. Se provassimo a eseguire un enunciato come questo: CREATE TABLE Persone (Nome CHAR(20), Cognome CHAR(25), Successione INT AUTO_INCREMENT)

otterremmo la seguente segnalazione di errore: Incorrect table definition; There can only be one auto column and it must be defined as a key

Come abbiamo visto sopra, l’impostazione del commando CREATE TABLE in ambiente SQL Server è leggermente diversa, vale a dire la riga che definisce e imposta il campo per la chiave primaria ha questa forma: nome_campo INT IDENTITY(s, p) PRIMARY KEY

dovuta al fatto che la clausola AUTO_INCREMENT che consente di generare automaticamente una chiave primaria numerica e univoca non è disponibile in SQL Server.

Per ottenere lo stesso risultato ricorriamo a una funzione chiamata: IDENTITY(s, p)

dove i due valori racchiusi fra parentesi rappresentano rispettivamente il valore iniziale e il passo di incremento dei successivi valori numerici che verranno generati per dare univocità alla chiave primaria. Nella forma che abbiamo usato: Codice INT IDENTITY(1,5) PRIMARY KEY,

la prima volta che si inserisce un record nella tabella il campo della chiave primaria avrà valore 1 e negli inserimenti successivi il valore della chiave primaria sarà incrementato di 5 rispetto al primo valore disponibile. La scelta di un valore da dare al parametro p è del tutto libera e nulla impedisce di scrivere la clausola nella forma IDENTITY (1,1)

ottenendo così un passo di incremento di 1, come quello che viene generato con la clausola AUTO_INCREMENT in MySQL. Naturalmente è possibile – lavorando sia con MySQL sia con SQL Server – utilizzare per la chiave primaria di ogni nuovo record un codice numerico o alfabetico già in uso nel contesto lavorativo in cui si utilizza il database, rinunciando così all’incremento automatico del codice numerico assegnato alla chiave primaria: in tal caso, la responsabilità dell’inserimento di un valore univoco per la chiave primaria passa dal computer all’operatore umano, con tutti i rischi che questo comporta. Chiave primaria composta Possono esistere situazioni nelle quali le informazioni da gestire con una tabella contengono elementi che, combinati fra loro, definiscono in modo univoco un record, costituendo in questo modo una chiave primaria composta, cioè formata da più colonne. In casi di questo genere, non è necessario creare nei record una chiave primaria separata, eventualmente con un campo qualificato come AUTO_INCREMENT o ricorrendo alla clausola IDENTITY, ma basta far sapere a SQL quali sono i campi da considerare collettivamente come chiave primaria. Per esempio, potremmo avere, come nella tabella che segue, un codice che identifica il tipo di progetto e un altro codice che identifica la commessa a fronte della quale è in corso un progetto di quel tipo: i codici numerici associati con il codice Tipo permettono di distinguere in modo univoco le singole attività: T ipo proget t o AU

Codice commessa 1

Descrizione Addestramento in aula

Dat a inizio 31/05/2016

Dat a fine 30/06/2016

AU

2

Addestramento sul campo

30/06/2016

09/08/2016

AU

3

Test di qualificazione

09/08/2016

19/08/2016

SD

1

Sviluppo Database Alfa

31/05/2016

29/08/2016

SD

2

Sviluppo Database Beta

31/05/2016

28/09/2016

AU

4

Assistenza rete

30/06/2016

09/08/2016

Una tabella Progetti, strutturata come nell’esempio, si può creare con il seguente enunciato SQL: CREATE TABLE Progetti (TipoProgetto CHAR(2) NOT NULL, CodiceCommessa INT NOT NULL, PRIMARY KEY(TipoProgetto, CodiceCommessa), Descrizione VARCHAR(30), DataInizio DATE, DataFine DATE)

Per creare una chiave primaria articolata su più campi, la clausola PRIMARY KEY va utilizzata in un modo diverso da quello che abbiamo visto in precedenza, per la tabella Movimenti. Infatti, quando la chiave primaria è su un solo campo, la clausola va scritta sulla stessa riga che definisce il campo; per esempio, con questo enunciato: Codice INT NOT NULL PRIMARY KEY,

si assegna al campo Codice il ruolo di chiave primaria mentre contestualmente se ne definisce il tipo dati come INT e si stabilisce che non può contenere valori Null. Quando, invece, si vuole avere una chiave primaria su più campi, la clausola PRIMARY va scritta separatamente, con lo schema sintattico:

KEY

PRIMARY KEY(campo1, campo2, …),

dopo aver definito in precedenza i vari campo1, campo2 e così via, avendo assegnato il tipo di dati e specificando per ciascuno il vincolo di NOT NULL . Possiamo vedere nella Figura 3.14 la struttura della tabella Progetti dopo che è stata creata con l’enunciato CREATE TABLE Progetti descritto sopra.

Figura 3.14 La struttura della tabella Progetti nei due ambienti.

Possiamo immettere i dati nella tabella Progetti utilizzando il seguente enunciato: INSERT INTO Progetti (TipoProgetto, CodiceCommessa, Descrizione, DataInizio, DataFine) VALUES ('AU',1,'Addestramento in aula','2016/05/31','2016/06/30'), ('AU',2,'Addestramento sul campo','2016/06/30','2016/08/09'), ('AU',3,'Test di qualificazione','2016/08/09','2016/08/19'), ('SD',1,'Sviluppo Database Alfa','2016/05/31','2016/08/29'), ('SD',2,'Sviluppo Database Beta','2016/05/31','2016/09/28'), ('AU',4,'Assistenza Rete','2016/06/30','2016/08/09')

Dopo l’inserimento dei dati nella tabella Progetti, l’esecuzione del comando SELECT * FROM Progetti

produce il risultato che si può vedere nella Figura 3.15.

Figura 3.15 La tabella Progetti con i dati.

Come si può vedere, l’interfaccia grafica evidenzia con lo stesso simbolo di una piccola chiave Yale le due colonne che formano la chiave primaria di questa tabella. La clausola PRIMARY KEY fa parte dello standard SQL ed è disponibile in tutte le implementazioni di SQL. L’utilizzo che abbiamo descritto qui vale sia per MySQL sia per SQL Server.

Tabelle e indici Nel gergo di SQL, il termine “chiave” o “key” è sinonimo di indice e si riferisce a uno strumento interno a SQL, che può essere creato e utilizzato per rendere più veloci le operazioni di selezione dei dati dalle tabelle, cioè le query. Vediamo prima come si crea un indice e poi ne approfondiremo il significato. La tabella Movimenti ha un campo di tipo DATE chiamato Valuta, destinato a contenere una data. Creiamo un indice su quel campo utilizzando una variante del comando ALTER , che può avere questa forma:

TABLE

ALTER TABLE Movimenti ADD INDEX Data (Valuta)

Dopo aver eseguito il comando visualizziamo la struttura modificata della tabella Movimenti, che si presenterà come nella Figura 3.16.

Figura 3.16 La struttura della tabella Movimenti evidenzia la presenza di un indice sul campo Valuta.

La clausola che abbiamo utilizzato nell’istruzione ALTER TABLE ha la seguente struttura sintattica in MySQL: ADD INDEX [nome_indice] (nome_colonna_indice,...)

dove nome_indice è facoltativo e nome_colonna_indice è il nome della colonna sulla quale si vuole costruire l’indice. È possibile costruire un indice composto da più colonne, basta indicarle fra le parentesi tonde, separando i nomi con una virgola. Per esempio, si può costruire un indice NomeCompleto sulle due colonne Cognome e Nome di una tabella Nomi, in questo modo: ALTER TABLE Nomi ADD INDEX NomeCompleto (Cognome, Nome)

Si può utilizzare un comando specifico per creare un indice, invece di una clausola di ALTER TABLE. Il comando ha questa struttura sintattica:

CREATE INDEX nome_indice ON nome_tabella (nome_colonna, nome colonna,...)

per cui, con un enunciato come il seguente: CREATE INDEX NomeCompleto ON Indirizzi (Cognome, Nome)

si ottiene lo stesso risultato generato dall’enunciato precedente, che utilizza ALTER TABLE completato con la clausola ADD INDEX. Il comando CREATE INDEX si può usare in MySQL in alternativa ad ALTER TABLE ed è disponibile in SQL Server, dove si può utilizzare soltanto in questa forma. Una tabella può contenere fino a 16 indici e un indice non può essere più lungo di 256 caratteri. Non è obbligatorio definire un indice per una tabella, però può essere utile: vediamo perché. Quando si esegue una query SQL, con un comando SELECT dove è specificato con la clausola WHERE un criterio di selezione, vengono prese in considerazione tutte le tabelle indicate nel comando, leggendo sequenzialmente le righe (i record) ed esplorando tutte le colonne (i campi) per trovare i record che soddisfano il criterio di selezione espresso dalla clausola WHERE. Se la tabella contiene un indice e la selezione comporta un confronto su un campo indicizzato, l’esplorazione avviene molto più velocemente, perché SQL può posizionarsi direttamente sul primo record che contiene un valore che soddisfa il criterio di ricerca. In termini pratici, la query: SELECT Codice, Importo, Descrizione, Valuta FROM Movimenti WHERE Valuta > '2016-10-16'

si esegue in un tempo molto più veloce se esiste un indice sul campo Valuta, perché l’indice consente a SQL di arrivare in un colpo solo al primo dei record che ha una data posteriore al 16 ottobre 2016 e di estrarre tutti i successivi, senza dover leggere i precedenti. La differenza di velocità si può apprezzare, ovviamente, quando si lavora su tabelle di una certa dimensione, diciamo da 10.000 record in su, mentre è irrilevante nel caso di tabelle più piccole. In termini fisici, un indice è una piccola tabella composta da record con due campi: il primo contiene un valore generato da SQL che identifica univocamente i record della tabella indicizzata e il secondo contiene il valore (la chiave) del campo sul quale è stato fatto l’indice. Il meccanismo di esplorazione attivato da un comando SELECT controlla tutti i secondi campi di ogni record dell’indice e, quando individua una corrispondenza, utilizza il primo campo per posizionarsi sul record corrispondente della tabella indicizzata. La creazione di un indice aggiunge quindi una tabella, sia pure di dimensioni ridotte, al database e ne incrementa le dimensioni.

Quando si modifica il contenuto di una tabella, aggiungendo o eliminando record, gli eventuali indici definiti su quella tabella vanno rigenerati, operazione che allunga a volte in modo sensibile i tempi di aggiornamento effettivi di una tabella. Per queste due ragioni – aumento delle dimensioni del database e allungamento dei tempi di aggiornamento – è opportuno creare indici soltanto quando i vantaggi che se ne possono derivare compensano gli svantaggi. Anche la chiave primaria è un indice, caratterizzato dal fatto che assume un valore diverso, senza alcun duplicato, per ciascun record. Gli altri indici, invece, possono avere valori duplicati; per esempio, in un indice sul campo Provincia di una tabella di indirizzi è molto probabile che compaiano più volte valori come MI, PV oppure TO , corrispondenti a indirizzi di persone che abitano in città diverse, che appartengono alla stessa provincia. Nell’output del comando DESCRIBE, MySQL segnala nella colonna Key la differenza fra un indice chiave primaria e un indice che ammette duplicati: la sigla PRI indica che il campo è parte di una chiave primaria, mentre la sigla MUL segnala che quel campo è una chiave indice che consente duplicati. Come funziona un indice Nella Figura 3.17 vediamo una versione esemplificativa, ridotta a quattro colonne, della tabella Movimenti. A lato è schematizzata la tabella, composta da due sole colonne, che corrisponde all’indice Data, costruito sulla colonna Valuta della tabella Movimenti. La prima colonna contiene i valori Codice, che identificano univocamente ciascun record. La seconda colonna contiene i valori Valuta. L’indice è ordinato sulla seconda colonna, per cui i record si susseguono dalla data più remota (la minima) alla più recente (la massima).

Figura 3.17 La tabella Movimenti e l’indice Data.

Quando SQL riceve il comando SELECT * FROM Movimenti WHERE Valuta = 23/01/2016

si accorge che la tabella ha un indice sulla colonna Valuta e lo esplora, portandosi direttamente sul record che sta al centro dell’indice. Controlla il valore della data e constata che è minore di quella che sta cercando. Questo vuol dire che la metà superiore dell’indice può essere trascurata. Si porta al centro della seconda metà e riesegue il controllo. Anche in questo caso trova una data antecedente a quella che gli serve. Sa quindi che il valore che cerca, se esiste, deve trovarsi nell’ultimo tratto dell’indice. Inizia una lettura sequenziale dei soli record dell’ultimo segmento dell’indice e trova la data che è stata richiesta. Acquisisce il valore del primo campo del record trovato nell’indice e si serve di questo valore per estrarre direttamente dalla tabella Movimenti il record che soddisfa la query.

Questo modo di procedere per successive segmentazioni di un indice in due parti si chiama ricerca binaria e consente di ridurre notevolmente il numero di accessi al disco necessari per estrarre record da tabelle indicizzate. La Figura 3.18 tenta di dare una rappresentazione grafica del meccanismo della ricerca binaria.

Figura 3.18 Il meccanismo della ricerca binaria.

In assenza dell’indice Data, SQL in questo caso avrebbe dovuto leggere 12 record per trovare quello giusto. Con l’indice, ha dovuto fare soltanto 4 letture, un risparmio del 75 per cento. Le implementazioni commerciali di SQL dedicano particolare attenzione alla messa a punto di algoritmi di ricerca binaria molto raffinati, che riescono a ridurre al massimo le operazioni di accesso ai dischi, che sono di solito molto onerose in termini di tempo. Quando l’hardware lo consente, gli indici vengono elaborati interamente in memoria, dove si possono eseguire ripetute segmentazioni binarie a velocità elevatissime, e si accede al disco soltanto per estrarre i record reperiti. Il modo in cui vengono realizzate materialmente le funzionalità previste da SQL va al di là degli obiettivi di questo libro; qui abbiamo fatto un’eccezione, data l’importanza

pratica, oltre che concettuale, degli indici. Valori predefiniti Quando si esamina la struttura di una tabella in MySQL con il comando DESCRIBE riferendolo a una qualunque tabella, viene presentato uno specchietto come quello che si può vedere sopra nella Figura 3.10, che riepiloga alcune informazioni su ciascun campo. La colonna Default dello specchietto indica il valore predefinito che SQL attribuisce a un campo quando si inserisce un nuovo record senza specificare un valore per quel campo. Il valore Default è NULL per tutti i campi non numerici e 0 per quelli numerici. Vediamo un semplice esempio. Con questo enunciato: INSERT Movimenti (Banca, Conto, Importo) VALUES ('Popolare', '455', 5000)

inseriamo un nuovo record nella tabella Movimenti specificando espressamente soltanto il contenuto dei due campi Banca e Conto. Eseguiamo questo enunciato e subito dopo diamo il comando di selezione di tutti i record. Otterremo una schermata simile a quella che vediamo nella Figura 3.19.

Figura 3.19 I campi non specificati per il nuovo record hanno valori predefiniti (Default) pari a Null o a zero.

È possibile ottenere un valore Default diverso specificandolo espressamente come clausola di un enunciato CREATE TABLE o ALTER TABLE. Vediamo un esempio utilizzando il secondo comando. Digitiamo ed eseguiamo: ALTER TABLE Movimenti MODIFY Conto CHAR (5) NOT NULL DEFAULT "nnnnn"

In questo modo, se inseriamo un nuovo record nella tabella Movimenti senza specificare un valore per il campo Conto, invece di Null troveremo la stringa di caratteri .

nnnnn

Verifichiamo il funzionamento di questa modifica, inserendo un nuovo record con due soli campi definiti: INSERT Movimenti (Banca, Importo) VALUES('Popolare', -185.92)

Come si vede nella Figura 3.20, invece di Null troviamo nnnnn nella colonna Conto.

Figura 3.20 L’effetto della definizione di un nuovo valore Default.

Il comando ALTER TABLE prevede una clausola SET DEFAULT più comoda da utilizzare della clausola MODIFY che abbiamo visto sopra. La sintassi di questa variante del comando ALTER TABLE è la seguente: ALTER TABLE nome_tabella ALTER [COLUMN] nome_colonna {SET DEFAULT valore_predefinito | DROP DEFAULT}

dove la parola chiave COLUMN è facoltativa, l’argomento nome_colonna è obbligatorio e deve essere seguito in alternativa da una delle due clausole SET DEFAULT o DROP DEFAULT. La clausola SET DEFAULT deve essere seguita da un valore_predefinito, cioè il valore effettivo da utilizzare come predefinito, mentre non si deve mettere alcuna indicazione se si utilizza la clausola DROP DEFAULT, che elimina il valore predefinito esistente. Se volessimo predisporre un valore diverso da Null per il campo Banca della tabella Movimenti, un enunciato come questo: ALTER TABLE Movimenti ALTER COLUMN Banca SET DEFAULT "Popolare"

inserirebbe la stringa di caratteri Popolare nel campo Banca ogni volta che si inserisce un nuovo record in Movimenti senza specificare un valore per quel campo. Per eliminare il valore predefinito e ripristinare Null, il comando può essere: ALTER TABLE Movimenti ALTER Banca DROP DEFAULT

In SQL Server il comando ALTER TABLE è disponibile con le stesse funzionalità e una sintassi leggermente diversa. Volendo modificare il valore predefinito per una colonna, quale potrebbe essere la colonna Tipo nella tabella Movimenti, il comando da utilizzare è:

ALTER TABLE Movimenti ADD CONSTRAINT Vincolo DEFAULT 'aaa' FOR Tipo;

dove Vincolo è un nome di fantasia per individuare la modifica. Questo nome è da utilizzare per eliminare la modifica, in questo modo: ALTER TABLE Movimenti DROP Vincolo;

Modificare tabelle Le tabelle di un database si possono modificare sia nel loro contenuto, aggiungendo o eliminando record, sia nella loro struttura, inserendo nuove colonne o eliminando colonne esistenti, aggiungendo o togliendo indici. È anche possibile, come abbiamo visto in precedenza, modificare il tipo di dati di un campo mediante l’istruzione: ALTER TABLE tabella MODIFY campo TIPODATO

dove TIPODATO è il nuovo tipo di dato che sostituisce quello esistente di campo. Le operazioni di inserimento e di eliminazione di record non modificano la struttura di una tabella e appartengono quindi alla componente Data Manipulation Language (DML) di SQL, invece che alla componente Data Definition Language (DDL) di cui ci occupiamo in questo capitolo. Le modifiche di struttura si eseguono con i comandi ALTER TABLE e DROP , il primo dei quali prevede numerose clausole e opzioni, mentre il secondo è piuttosto semplice e diretto. Per l’eliminazione di una tabella, lo standard SQL prevede questa sintassi per il comando DROP : DROP TABLE tabella RESTRICT | CASCADE

La clausola RESTRICT inibisce l’esecuzione del comando se esistono connessioni fra la tabella richiamata nel comando e altri elementi del database, mentre la clausola CASCADE propaga a cascata l’eliminazione su tutti gli elementi con i quali la tabella da eliminare è collegata. Non tutte le implementazioni di SQL rispettano lo standard, in particolare MySQL non offre le opzioni RESTRICT e CASCADE per la sua versione del comando DROP TABLE, la cui sintassi è più semplicemente questa: DROP [TEMPORARY] TABLE [IF EXISTS] tabella1 [, tabella2, . . . ]

dove la clausola IF EXISTS previene una segnalazione di errore se il comando viene riferito a una tabella che non esiste. L’opzione TEMPORARY limita ulteriormente la portata del comando alle sole tabelle temporanee. La variante MySQL di questo comando consente di eseguirlo su un numero illimitato di tabelle, invece che su una sola per volta. L’esecuzione di DROP TABLE in MySQL è estremamente pericolosa, perché provoca l’eliminazione senza appello di tutte le tabelle indicate nell’enunciato. Per eliminare un indice, si ha a disposizione il comando DROP INDEX, che in MySQL ha questa sintassi: DROP INDEX nome_indice ON nome_tabella

Lo stesso comando è disponibile come clausola di ALTER TABLE, nella forma: ALTER TABLE nome_tabella DROP INDEX nome_indice

per cui possiamo eliminare l’indice Data dalla tabella Movimenti con uno o l’altro di questi enunciati: DROP INDEX Data ON Movimenti ALTER TABLE Movimenti DROP INDEX Data

Riepilogando, possiamo dire che la parola chiave DROP è il killer di SQL. Quando è utilizzata come clausola nel comando ALTER TABLE elimina definitivamente l’elemento al quale si riferisce. Il comando prevede quattro forme per questa clausola: DROP DEFAULT DROP INDEX nome_indice DROP nome_colonna DROP PRIMARY KEY

ciascuna delle quali elimina l’elemento indicato. La parola chiave ADD è, in un certo senso, l’inverso di DROP e viene utilizzata come clausola del comando ALTER TABLE per aggiungere qualcosa. Dopo aver creato una tabella e aver cominciato a utilizzarla, potremmo accorgerci che i campi in cui si articolano i record, cioè le colonne della tabella, non sono sufficienti per acquisire le informazioni necessarie. Possiamo aggiungere una nuova colonna a una tabella esistente utilizzando il comando ALTER TABLE con la clausola ADD , in questo modo: ALTER TABLE nome_tabella ADD nome_campo TIPODATO

Supponiamo di aver bisogno di aggiungere un’annotazione, un commento, alle righe della tabella Movimenti. Il comando che ci serve può essere formulato in questo modo: ALTER TABLE Movimenti ADD Commento TEXT

Se, in un secondo tempo, ci accorgessimo che questa nuova colonna dopo tutto è inutile, potremmo eliminarla col comando: ALTER TABLE Movimenti DROP Commento

L’eliminazione di una colonna comporta, naturalmente, la perdita di tutti i dati che questa colonna contiene. Si può cambiare il nome di una tabella, lasciando inalterati struttura e contenuto, con la clausola RENAME di ALTER TABLE, usando questa sintassi: ALTER TABLE nome_tabella RENAME [AS] nuovo_nome_tabella

In SQL Server le operazioni di modifica del nome di una tabella o di una colonna si possono eseguire agevolmente anche utilizzando l’interfaccia grafica disponibile nella

finestra Esplora oggetti di Management Studio: un clic destro sul nome dell’elemento da modificare fa uscire un menu di scelta rapida all’interno del quale si seleziona Rinomina abilitando così la modifica diretta da tastiera del nome selezionato, come si vede nella Figura 3.21.

Figura 3.21 Dalla finestra Esplora oggetti di Management Studio si possono agevolmente modificare i nomi di database, tabelle e colonne.

Riepilogo della sintassi Tutte le funzionalità della componente Data Definition Language di SQL sono riconducibili a due sole potentissime istruzioni, CREATE TABLE e ALTER TABLE, che abbiamo visto per parti in questo capitolo. Rispetto allo standard del linguaggio, tutte le implementazioni di SQL esistenti in commercio hanno aggiunto modifiche, arricchimenti e peculiarità, quindi per creare e modificare tabelle è necessario far riferimento alla specifica sintassi adottata per questi due comandi dall’implementazione che si utilizza. Qui di seguito riportiamo lo schema sintattico delle due istruzioni così come risultano implementate in MySQL e in SQL Server (i due sistemi per creare e gestire database relazionali che utilizziamo per sviluppare gli esempi).

Sintassi di ALTER TABLE in MySQL ALTER [IGNORE] TABLE nome_tabella [specifica_di_modifica [, specifica_di_modifica] ...] [opzioni partizione] specifica_di_modifica opzioni_tabella | ADD [COLUMN] nome_colonna definizione_colonna [FIRST | AFTER nome_colonna ] | ADD [COLUMN] (nome_colonna definizione_colonna,...) | ADD {INDEX|KEY} [nome_indice] [tipo_indice] (nome_colonna_indice,...) [opzione _indice] ... | ADD [CONSTRAINT [symbol]] PRIMARY KEY [tipo_indice] (nome_colonna_indice,...) [opzione _indice] ... | ADD [CONSTRAINT [simbolo]] UNIQUE [INDEX|KEY] [nome_indice] [tipo_indice] (nome_colonna_indice,...) [opzione _indice] ... | ADD FULLTEXT [INDEX|KEY] [nome_indice] (nome_colonna_indice,...) [opzione _indice] ... | ADD SPATIAL [INDEX|KEY] [nome_indice] (nome_colonna_indice,...) [opzione _indice] ... | ADD [CONSTRAINT [simbolo]] FOREIGN KEY [nome_indice] (nome_colonna_indice,...) definizione_riferimento | ALGORITHM [=] {DEFAULT|INPLACE|COPY} | ALTER [COLUMN] nome_colonna {SET DEFAULT letterale | DROP DEFAULT} | CHANGE [COLUMN] vecchio_nome_colonna nuovo_nome_colonna definizione_colonna [FIRST|AFTER nome_colonna] | LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE} | MODIFY [COLUMN] nome_colonna definizione_colonna [FIRST | AFTER nome_colonna] | DROP [COLUMN] nome_colonna | DROP PRIMARY KEY | DROP {INDEX|KEY} nome_indice | DROP FOREIGN KEY simbolo_chiave_esterna | DISABLE KEYS | ENABLE KEYS | RENAME [TO|AS] nuovo_nome_tabella | RENAME {INDEX|KEY} vecchio_nome_indice TO nuovo_nome_indice | ORDER BY nome_colonna [, nome_colonn

Sintassi di ALTER TABLE in SQL Server ALTER TABLE [ nome_database . [ nome_schema ] . | nome_schema . ] nome_tabella { ALTER COLUMN nome_colonna { [ nome_tipo_schema. ] nome_tipo [ ( { precisione [ , scala ] | max | raccolta_schema_xml } ) ] [ COLLATE nome_collazione ] [ NULL | NOT NULL ] [ SPARSE ] | {ADD | DROP } { ROWGUIDCOL | PERSISTED | NOT FOR REPLICATION | SPARSE } } | [ WITH { CHECK | NOCHECK } ] | ADD { | | | } [ ,...n ] | DROP { [ CONSTRAINT ] { nome_vincolo [ WITH ( [ ,...n ] ) ] } [ ,...n ] | COLUMN { nome_colonna } [ ,...n ] } [ ,...n ] | [ WITH { CHECK | NOCHECK } ] { CHECK | NOCHECK } CONSTRAINT { ALL | nome_vincolo [ ,...n ] }

Sintassi di CREATE TABLE in MySQL Il comando CREATE TABLE si articola in tre parti, chiamate: create_definition (definizione_della_creazione) table_options (opzioni_della_tabella) partition_options (opzioni_per_la_partizione)

Le opzioni per la partizione hanno a che fare con le funzioni di server e non con il linguaggio SQL e si possono trascurare. La definizione_della_creazione a sua volta si articola in due blocchi: nome_colonna e definizione_colonna CREATE [TEMPORARY] TABLE [IF NOT EXISTS] nome_tabella [(definizione_della_creazione,...)] [opzioni_della_tabella] [opzioni_per_la_partizione] [IGNORE | REPLACE] [AS] espressione query CREATE [TEMPORARY] TABLE [IF NOT EXISTS] nome_tabella { LIKE vecchio_nome_tabella | (LIKE vecchio_nome_tabella) } definizione_della_creazione:

nome_colonna definizione_colonna | [CONSTRAINT [simbolo]] PRIMARY KEY [tipo_indice] (nome_col_indice,...) [opzione_indice] ... | {INDEX|KEY} [nome_indice] [tipo_indice] (nome_col_indice,...) [opzione_indice] ... | [CONSTRAINT [simbolo]] UNIQUE [INDEX|KEY] [nome_indice] [tipo_indice] (nome_col_indice,...) [opzione_indice] ... | {FULLTEXT|SPATIAL} [INDEX|KEY] [nome_indice] (nome_col_indice,...) [opzione_indice] ... | [CONSTRAINT [simbolo]] FOREIGN KEY [nome_indice] (nome_col_indice,...) definizione_riferimento | CHECK (espressione) definizione_colonna tipo_di_dato [NOT NULL | NULL] [DEFAULT valore_predefinito] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT ‘stringa’] [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}] [STORAGE {DISK|MEMORY|DEFAULT}] [definizione_riferimento] | tipo_di_dato [GENERATED ALWAYS] AS (espressione) [VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT commento] [NOT NULL | NULL] [[PRIMARY] KEY] tipo_di_dato: BIT[(lunghezza)] | TINYINT[(lunghezza)] [UNSIGNED] [ZEROFILL] | SMALLINT[(lunghezza)] [UNSIGNED] [ZEROFILL] | MEDIUMINT[(lunghezza)] [UNSIGNED] [ZEROFILL] | INT[(lunghezza)] [UNSIGNED] [ZEROFILL] | INTEGER[(lunghezza)] [UNSIGNED] [ZEROFILL] | BIGINT[(lunghezza)] [UNSIGNED] [ZEROFILL] | REAL[(lunghezza,decimali)] [UNSIGNED] [ZEROFILL] | DOUBLE[(lunghezza,decimali)] [UNSIGNED] [ZEROFILL] | FLOAT[(lunghezza,decimali)] [UNSIGNED] [ZEROFILL] | DECIMAL[(lunghezza[,decimali])] [UNSIGNED] [ZEROFILL] | NUMERIC[(lunghezza[,decimali])] [UNSIGNED] [ZEROFILL] | DATE | TIME[(fsp)] | TIMESTAMP[(fsp)] | DATETIME[(fsp)] | YEAR | CHAR[(lunghezza)] [BINARY] [CHARACTER SET nome_insieme_di_caratteri] [COLLATE nome_collazione] | VARCHAR(lunghezza) [BINARY] [CHARACTER SET nome_insieme_di_caratteri] [COLLATE nome_collazione] | BINARY[(lunghezza)] | VARBINARY(lunghezza) | TINYBLOB | BLOB | MEDIUMBLOB | LONGBLOB | TINYTEXT [BINARY] [CHARACTER SET nome_insieme_di_caratteri] [COLLATE nome_collazione] | TEXT [BINARY] [CHARACTER SET nome_insieme_di_caratteri] [COLLATE nome_collazione] | MEDIUMTEXT [BINARY] [CHARACTER SET nome_insieme_di_caratteri] [COLLATE nome_collazione] | LONGTEXT [BINARY] [CHARACTER SET nome_insieme_di_caratteri] [COLLATE nome_collazione] | ENUM(valore1,valore2,valore3,...) [CHARACTER SET nome_insieme_di_caratteri] [COLLATE nome_collazione] | SET(valore1,valore2,valore3,...) [CHARACTER SET nome_insieme_di_caratteri] [COLLATE nome_collazione]

Sintassi di CREATE TABLE in SQL Server

CREATE TABLE [ nome_database . [ nome_schema ] . | nome_schema . ] nome_tabella [ AS FileTable ] ( { | | | [ ] [ ,...n ] } ) [ ON { nome_schema_partizione ( nome_partizione_colonne) | filegroup | "default" } ] [ { TEXTIMAGE_ON { filegroup | "default" } ] [ FILESTREAM_ON { nome_schema_partizione | filegroup | "default" } ] [ WITH ( [ ,...n ] ) ] [ ; ] ::= nome_colonna [ FILESTREAM ] [ COLLATE nome_collazione ] [ SPARSE ] [ NULL | NOT NULL ] [ [ CONSTRAINT nome_vincolo ] DEFAULT espressione_costante ] | [ IDENTITY [ ( base_incremento ) ] [ NOT FOR REPLICATION ] ] [ ROWGUIDCOL ] [ [ ...n ] ] ::= [ nome_schema_tipo . ] nome_tipo [ ( precisione [ , scala ] | max | [ { CONTENT | DOCUMENT } ] raccolta_schema_xml) ] ::= [ CONSTRAINT nome_vincolo ] { { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] [ WITH FILLFACTOR = fattore_riempimento | WITH ( < opzione_indice > [ , ...n ] ) ] [ ON { nome_schema_partizione ( nome_colonna_partizione) | filegroup | "default" } ] | [ FOREIGN KEY ] REFERENCES [ nome_schema . ] nome_tabella_referenziata [ ( colonna_ref ) ] [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] [ NOT FOR REPLICATION ] | CHECK [ NOT FOR REPLICATION ] ( espressione_logica ) } ::= nome_colonna AS espressione_colonna_calcolata [ PERSISTED [ NOT NULL ] ] [ [ CONSTRAINT nome_vincolo ] { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] [ WITH FILLFACTOR = fattore_di_riempimento | WITH ( [ , ...n ] ) ] [ ON { nome_schema_partizione ( nome_colonna_partizione ) | filegroup | "default" } ] | [ FOREIGN KEY ] REFERENCES nome_tabella_referenziata [ ( ref_column ) ] [ ON DELETE { NO ACTION | CASCADE } ] [ ON UPDATE { NO ACTION } ] [ NOT FOR REPLICATION ] | CHECK [ NOT FOR REPLICATION ] ( espressione logica ) ]

::= nome_insieme_colonna XML COLUMN_SET FOR ALL_SPARSE_COLUMNS < vincoli_tabella > ::= [ CONSTRAINT nome_vincolo] { { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] (column [ ASC | DESC ] [ ,...n ] ) [ WITH FILLFACTOR = fattore_di_riempimento |WITH ( [ , ...n ] ) ] [ ON { nome_schema_partizione (nome_colonna_partizione) | filegroup | "default" } ] | FOREIGN KEY ( colonna [ ,...n ] ) REFERENCES nome_tabella_referenziata [ ( rif_colonna [ ,...n ] ) ] [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] [ NOT FOR REPLICATION ] | CHECK [ NOT FOR REPLICATION ] ( connessione logica) }

Capitolo 4

Interrogare i database

Chi è arrivato a leggere fino a questo capitolo potrebbe essersi chiesto per quale ragione ricorrere a un server per database della potenza (e della conseguente complessità) di MySQL o di SQL Server per costruire tabelle: in fondo tutti abbiamo nei nostri computer una versione più o meno recente di Excel che ci permette di creare agevolmente tabelle (anche molto grandi e impegnative) e dove – una volta inseriti alla rinfusa un po’ di dati in una colonna – è possibile ordinarli con un semplice comando, come si vede nella Figura 4.1.

Figura 4.1 In un foglio di lavoro Excel è semplice e intuitivo inserire dati in una tabella e ordinarli.

La domanda è legittima e merita una risposta adeguata. L’idea di fondo dei sistemi di database relazionali o RDBMS è quella di tenere separata la gestione dei dati dalla loro fruizione: i dati, organizzati in tabelle, una volta creati stanno sempre dove sono stati messi, salvo interventi con il comando ALTER TABLE che abbiamo visto nel Capitolo 3; per consultare quelle tabelle si crea una copia momentanea dei soli dati che servono, prelevati nell’ordine e nella quantità che si desidera esplorando in modo selettivo le tabelle che interessano. Grazie a un’organizzazione di questo genere, le applicazioni gestionali che hanno bisogno di dati possono attingere – in modi diversi e coerenti con le loro specifiche necessità – a un unico grande database, senza doversi preoccupare ogni volta di come gestire i dati che utilizzano, quindi: le applicazioni per le vendite attingeranno alle tabelle Prodotti, Clienti e Ordini,

le applicazioni per la gestione della produzione si riferiranno alle tabelle Prodotti e Magazzini e le applicazioni per la gestione delle risorse umane si avvarranno delle tabelle Personale, OreLavorate, Commesse e così via, ciascuna facendo confluire nelle tabelle che utilizza gli aggiornamenti specifici nati dalla sua attività: consegna dei prodotti, avanzamento delle commesse, movimenti di materiali nei magazzini, calcoli delle ore lavorate per commessa, liquidazione degli stipendi e via enumerando. Tutto questo con le tabelle che si possono costruire con Excel non si potrebbe fare altrettanto agevolmente. Per utilizzare i dati delle tabelle se ne fa una copia temporanea, specificando un criterio di selezione ed eventualmente una modalità di ordinamento: questa copia si chiama vista e viene generata con l’istruzione SELECT dello Structured Query Language, con la quale si formula una domanda (query in inglese) che indica quali dati presentare, da quali tabelle e come visualizzarli. Lo schema sintattico standard di questa istruzione è il seguente: SELECT [ ALL | DISTINCT ] elenco_di_selezione [INTO nuova_tabella] FROM tabella_origine [, . . . ] [JOIN condizione_join ] [WHERE condizione_di_selezione] [GROUP BY espressione_di_raggruppamento] [HAVING condizione_di_ricerca] [ORDER BY criterio_ordinamento [ASC | DESC] ]

prevede sette clausole, una sola delle quali, FROM , è obbligatoria. Per alcune

SELECT

clausole sono previsti parametri aggiuntivi, specificati con le parole chiave ON, IN e AS . In si possono inoltre utilizzare operatori aritmetici, logici e di concatenamento e

SELECT

funzioni matematiche e statistiche. Data la complessità e la versatilità dell’istruzione SELECT, la esporremo per passi successivi, approfondendo gradualmente le caratteristiche e le funzionalità di ciascuna clausola. Nella Figura 4.2 mostriamo una tabella chiamata Indirizzi che andremo a utilizzare per molti esempi che seguono in questo capitolo.

Figura 4.2 La tabella di prova Indirizzi.

Forme semplici di SELECT Nella sua forma più semplice, che abbiamo già utilizzato in alcuni esempi del capitolo precedente, il comando SELECT si compone di due sole clausole: SELECT elenco_di_selezione FROM tabella_origine

dove elenco_di_selezione è un elenco di colonne da estrarre dalla tabella_origine specificata nella clausola FROM . Quando si vogliono estrarre tutte le colonne da una tabella, elenco_di_selezione può essere sostituito dal carattere asterisco, per cui il comando: SELECT * FROM Indirizzi

estrae tutto il contenuto della tabella Indirizzi. Ast erisco e ALL Il carattere asterisco non va confuso con il qualificatore ALL , utilizzabile in alternativa a DISTINCT; questi due qualificatori devono essere sempre seguiti da un elenco_di_selezione, per cui l’enunciato SELECT ALL FROM Indirizzi

è sintatticamente sbagliato e genera una segnalazione di errore. La sua forma corretta è, invece: SELECT ALL Prov FROM Indirizzi

ed estrae tutti i valori della colonna Prov della tabella Indirizzi.

L’argomento elenco_di_selezione deve contenere almeno un nome di colonna, scelto fra le colonne contenute nella tabella_origine indicata come argomento della clausola FROM . Se l’elenco_di_selezione contiene più nomi di colonne, questi vanno separati con virgole: SELECT Appellativo, Nome, Cognome FROM Indirizzi

L’ordine con il quale compaiono le colonne nell’elenco_di_selezione può essere diverso da quello in cui sono disposte le colonne nella tabella_origine e sarà quello col quale verranno forniti i risultati della query; il comando: SELECT Cognome, Prov FROM Indirizzi

produce il risultato che possiamo vedere nella Figura 4.3, dove le colonne Cognome e Prov occupano il primo e il secondo posto, anche se nella tabella Indirizzi si trovano rispettivamente nell’ottava e nella quarta posizione.

Figura 4.3 Le colonne di una query sono disposte nell’ordine indicato nell’elenco_di_selezione dell’istruzione SELECT.

I nomi delle colonne selezionate che fanno da intestazione alle righe del risultato di una query possono essere diversi da quelli presenti nella tabella_origine. Questi nomi diversi sono detti alias e si ottengono utilizzando la parola chiave AS nella formulazione della query. Vediamo come utilizzare gli alias. Nel nostro database abbiamo una tabella Clienti con questa struttura: Campo Tipo di dato ----- -------------------CodCli VARCHAR(5) RagSoc VARCHAR(30) Indirizzo VARCHAR(35) Città VARCHAR(25) CAP VARCHAR(5) Prov CHAR(2) PIVA VARCHAR(11)

I nomi di alcuni campi sono poco comprensibili: desideriamo visualizzare un’intestazione diversa quando estraiamo le colonne corrispondenti.

Digitiamo ed eseguiamo questa query: SELECT CodCli AS 'Codice cliente', RagSoc AS 'Ragione sociale', PIVA AS 'Partita IVA' FROM Clienti

Dovremmo ottenere il risultato che vediamo nella Figura 4.4.

Figura 4.4 La query seleziona le colonne CodCli, RagSoc e PIVA, presentandole con un’intestazione più comprensibile.

È consentito mescolare riferimenti con AS a riferimenti ai nomi effettivi in una stessa clausola SELECT. Per esempio, la query SELECT RagSoc AS 'Ragione sociale', Città, Prov AS 'Provincia' FROM Clienti

produrrebbe questo risultato: Ragione sociale Città Provincia ---------------------------- ----- --------Bertani & C Sas Pavia PV Carpenteria Audace SpA Pinerolo TO Cattabeni Impianti Snc Sappada BL Meccanica Parodi Srl Ivrea TO Mobilificio Pittaluga Snc Origgio VA Prefabbricati Anselmo Sas Leno BS

Gli alias compaiono soltanto nel risultato della query, mentre i campi nelle tabelle mantengono i loro nomi. Il ricorso all’alias può risultare comodo quando il nome del campo è oscuro, complesso o comunque si preferisce presentarlo in una forma differente. In determinate circostanze, come vedremo più avanti, l’alias svolge un ruolo fondamentale per l’esecuzione di alcuni tipi di query. La preposizione AS è un qualificatore del comando SELECT, non una clausola. La clausola SELECT può utilizzare altri due qualificatori oltre ad AS : ALL e DISTINCT. ALL

serve per specificare che la query dovrà estrarre tutti gli elementi indicati, ma in

pratica non si utilizza mai, perché SELECT estrae sempre tutti gli elementi. Con DISTINCT si specifica, invece, che la query dovrà trascurare gli eventuali valori ripetuti, presentando soltanto valori univoci. Vediamo un esempio pratico. La tabella Indirizzi contiene alcuni nomi di persone che risiedono nella stessa provincia. Vogliamo sapere quante sono le province diverse in cui risiedono le persone elencate nella tabella. Possiamo ottenere questo risultato con una query che utilizza il qualificatore DISTINCT e, per avere un risultato più chiaro, anche il qualificatore AS , in questo modo: SELECT DISTINCT Prov AS 'Province diverse' FROM Indirizzi

Vediamo il risultato di questa query nella Figura 4.5. Abbiamo visto fin qui le forme più semplici che può assumere una query, utilizzando soltanto la sua clausola principale, SELECT. Vediamo ora quali risultati si possono ottenere ricorrendo anche alla clausola ORDER BY.

Figura 4.5 La query con il qualificatore DISTINCT presenta soltanto valori univoci e il qualificatore AS ne descrive il contenuto.

La clausola ORDER BY La struttura sintattica di questa clausola è: ORDER BY criterio_ordinamento [ASC | DESC]

e la si utilizza per assegnare alle righe selezionate dalla query un ordine diverso da quello col quale si trovano nella tabella di origine: criterio_ordinamento elenca una o più delle colonne presenti nella tabella_origine, specificate direttamente o tramite un loro alias; la o le colonne scelte come criterio_ordinamento sono dette nel gergo dei database “chiavi di ordinamento”; le parole chiave ASC e DESC indicano il tipo di ordinamento e sono abbreviazioni delle parole inglesi ascending (ordinamento crescente, dal più piccolo al più grande) e descending (decrescente, dal più grande al più piccolo); se non viene specificato il tipo, viene assunto per impostazione predefinita l’ordinamento crescente. Riprendiamo l’esempio che abbiamo visto nel paragrafo precedente e impostiamo la query in modo che l’elenco delle province diverse compaia disposto in ordine decrescente. Formuliamo la nuova query in questo modo: SELECT DISTINCT Prov AS 'Province diverse' FROM Indirizzi ORDER BY Prov DESC

e otteniamo il risultato che possiamo vedere nella Figura 4.6.

Figura 4.6 L’elenco delle province diverse presentate in ordine alfabetico decrescente.

La clausola ORDER BY nel criterio_ordinamento, può far riferimento a una o a più colonne presenti nella tabella specificata come tabella_origine nella clausola FROM , anche se non sono indicate nella clausola SELECT. Quando si specificano più colonne, la prima colonna è detta “chiave di ordinamento primaria”, la successiva è la “chiave di ordinamento secondaria” e così via. Per esempio, con questa query: SELECT Prov, Cognome FROM Indirizzi ORDER BY Prov, Cognome

otteniamo il risultato mostrato nella Figura 4.7. Come possiamo vedere, la colonna Prov è ordinata alfabeticamente e i cognomi dei residenti sono disposti in ordine alfabetico nelle province che hanno più residenti (BG, BS, BZ, CR e MI).

Figura 4.7 Con la clausola ORDER BY si può ordinare su più colonne.

Le funzioni Lo standard SQL prevede un certo numero di funzioni, comandi che hanno la forma: FUNZIONE(argomento)

e restituiscono un valore eseguendo una specifica operazione (definita dalla parola chiave che identifica la funzione) in base a uno o più valori indicati come argomento. Esistono anche funzioni che restituiscono un risultato senza che si debba fornire un argomento (perché ne utilizzano uno intrinseco); per esempio, in MySQL la funzione CURRENT_TIME() restituisce la data e l’ora attuali ricavandole dall’orologio del sistema, mentre la funzione CURRENT_DATE() fornisce la data odierna, per cui con una query scritta in questo modo: SELECT 'Oggi è il: ', CURRENT_DATE(), ' e sono le: ', CURRENT_TIME()

si ottiene il risultato che possiamo vedere qui di seguito:

In SQL Server la funzione equivalente è GETDATE(), che fornisce data e ora del momento in cui viene eseguita. Si può ottenere un risultato simile a quello che abbiamo visto sopra in MySQL scomponendo il risultato fornito dalla funzione GETDATE() di SQL Server con l’aiuto di un’altra funzione, chiamata CONVERT(), in questo modo: SELECT 'Oggi è il: ', CONVERT (date, GETDATE()), ' e sono le: ', CONVERT (time, GETDATE())

Lo standard SQL prevede numerose funzioni, che sono classificate in due grandi famiglie: funzioni di aggregazione: operano su un insieme di valori (per esempio, tutta una colonna) e restituiscono un unico valore riepilogativo; si possono utilizzare soltanto all’interno di istruzioni SELECT; funzioni scalari: agiscono su un solo valore (l’argomento) e restituiscono un valore basato sull’argomento. Le funzioni previste dallo standard sono poco più di una ventina, le versioni di questo linguaggio realizzate con MySQL e SQL Server mettono a disposizione numerose funzioni non standard, concepite per avere in un unico ambiente di sviluppo anche

funzionalità per la gestione del sistema, che per loro natura non fanno parte dello Structured Query Language in senso proprio. Nelle Tabelle che seguono riassumiamo le funzioni disponibili sia in MySQL sia in SQL Server, indipendentemente dal fatto che siano previste oppure no dallo standard del linguaggio. Tabella 4.1 Funzioni matematiche. Quelle indicate in corsivo sono disponibili soltanto in MySQL, tutte le altre sono disponibili sia in MySQL sia in SQL Server. Nome

Descrizione

ABS()

Restituisce il valore assoluto

ACOS()

Restituisce l’arco coseno

ASIN()

Restituisce l’arco seno

ATAN()

Restituisce l’arco tangente

ATAN2(), ATAN()

Restituisce l’arco tangente dei due argomenti

CEILING()

Restituisce il minor valore intero non inferiore all’argomento

CONV()

Converte numeri fra basi numeriche differenti

COS()

Restituisce il coseno

COT()

Restituisce la cotangente

CRC32()

Calcola un valore di controllo basato sulla ridondanza ciclica (CRC)

DEGREES()

Converte radianti in gradi

EXP()

Eleva e (la base dei logaritmi naturali) alla potenza dell’argomento

FLOOR()

Restituisce il maggior valore intero non più grande dell’argomento

LN()

Restituisce il logaritmo naturale dell’argomento

LOG()

Restituisce il logaritmo naturale del primo argomento

LOG10()

Restituisce il logaritmo in base 10 dell’argomento

LOG2()

Restituisce il logaritmo in base 2 dell’argomento

MOD()

Restituisce il resto di una divisione

PI()

Restituisce il valore di pi greco

POW()

Restituisce l’argomento elevato alla potenza specificata. Sinonimo di POWER( )

POWER()

Restituisce l’argomento elevato alla potenza specificata

RADIANS()

Restituisce l’argomento convertito in radianti

RAND()

Restituisce un valore casuale a virgola mobile

ROUND()

Arrotonda l’argomento

SIGN()

Restituisce il segno dell’argomento

SIN()

Restituisce il seno dell’argomento

SQRT()

Restituisce la radice quadrata dell’argomento

TAN()

Restituisce la tangente dell’argomento

TRUNCATE()

Tronca fino al numero di decimali specificato

Tabella 4.2 Funzioni per data e ora. Quelle indicate in corsivo sono disponibili soltanto in MySQL, tutte le altre sono disponibili sia in MySQL sia in SQL Server. Nome Descrizione

ADDDATE()

Somma valori di ora (intervalli) a un valore data

ADDTIME()

Somma valori di ora

CONVERT_TZ()

Converte da un fuso orario a un altro

CURDATE()

Restituisce la data attuale

CURTIME()

Restituisce l’ora attuale

DATE()

Estrae la parte data da un’espressione data o data/ora

DATE_ADD()

Somma valori di ora (intervalli) a un valore data

DATE_FORMAT()

Formatta una data come specificato

DATE_SUB()

Sottrae un valore temporale (intervallo) da una data

DATEDIFF()

Sottrae due date

DAYNAME()

Restituisce il nome del giorno della settimana

DAYOFMONTH()

Restituisce il giorno del mese (0-31)

DAYOFWEEK()

Restituisce l’indice per il giorno della settimana dell’argomento

DAYOFYEAR()

Restituisce il giorno dell’anno (1-366)

EXTRACT()

Estrae parte di una data

FROM_DAYS()

Converte un numero di un giorno in una data

FROM_UNIXTIME()

Formatta la marcatura temporale UNIX come una data

GET_FORMAT()

Restituisce una stringa col formato della data

HOUR()

Estrae l’ora

LAST_DAY

Restituisce l’ultimo giorno del mese per l’argomento

MAKEDATE()

Crea una data a partire da anno e giorno dell’anno

MAKETIME()

Crea un valore di ora in base a ora, minuti, secondi

MICROSECOND()

Restituisce i microsecondi dall’argomento

MINUTE()

Restituisce il minuto dall’argomento

MONTH()

Restituisce il mese dall’argomento data

MONTHNAME()

Restituisce il nome del mese

NOW()

Restituisce data e ora attuali

PERIOD_ADD()

Somma un periodo a un anno-mese

PERIOD_DIFF()

Restituisce il numero di mesi fra periodi

QUARTER()

Restituisce il trimestre da un argomento data

SEC_TO_TIME()

Converte secondi nel formato ‘HH:MM:SS’

SECOND()

Restituisce il secondo (0-59)

STR_TO_DATE()

Converte una stringa in una data

SUBTIME()

Sottrae valori di ora

SYSDATE()

Restituisce l’ora di esecuzione della funzione

TIME()

Estrae la porzione ora dall’espressione indicata

TIME_FORMAT()

Formatta come valore di ora

TIME_TO_SEC()

Restituisce l’argomento convertito in secondi

TIMEDIFF()

Sottrae valori di ora

TIMESTAMP()

Quando ha un solo argomento, questa funzione restituisce la data oppure la data/ora; con due argomenti, la somma degli argomenti

TIMESTAMPADD()

Somma un intervallo a un’espressione data/ora

TIMESTAMPDIFF()

Sottrae un intervallo da un’espressione data/ora

TO_DAYS()

Restituisce l’argomento data convertito in giorni

TO_SECONDS()

Restituisce l’argomento data o data/ora convertito in secondi a partire dall’anno 0

UNIX_TIMESTAMP()

Restituisce una marcatura temporale UNIX

UTC_DATE()

Restituisce la data UTC attuale

UTC_TIME()

Restituisce l’ora UTC attuale

UTC_TIMESTAMP()

Restituisce la data e l’ora UTC attuali

WEEK()

Restituisce il numero della settimana

WEEKDAY()

Restituisce l’indice per il giorno della settimana

WEEKOFYEAR()

Restituisce la settimana di calendario della data (1-53)

YEAR()

Restituisce l’anno

YEARWEEK()

Restituisce l’anno e la settimana

Tabella 4.3 Funzioni per le stringhe. Quelle indicate in corsivo sono disponibili soltanto in MySQL, tutte le altre sono disponibili sia in MySQL sia in SQL Server. Nome

Descrizione

ASCII()

Restituisce il codice ASCII del carattere più a sinistra

BIN()

Restituisce una stringa che contiene la rappresentazione binaria di un numero

BIT_LENGTH()

Restituisce la lunghezza dell’argomento in bit

CHAR()

Restituisce il carattere per ciascun intero dell’argomento

CHAR_LENGTH()

Restituisce il numero dei caratteri nell’argomento

CONCAT()

Restituisce una stringa concatenata

CONCAT_WS()

Restituisce una stringa concatenata con separatori

ELT()

Restituisce una stringa al numero indice

EXPORT_SET()

Restituisce una stringa tale per cui per ogni bit impostato nei bit del valore si ottiene una stringa on e per ciascun bit non impostato si ottiene una stringa off

FIELD()

Restituisce l’indice (posizione) del primo argomento negli argomenti successivi

FIND_IN_SET()

Restituisce la posizione dell’indice del primo argomento entro il secondo argomento

FORMAT()

Restituisce un numero formattato per il numero specificato di posizioni decimali

FROM_BASE64()

Decodifica su una stringa a base 64 e restituisce un risultato

HEX()

Restituisce una rappresentazione esadecimale di una stringa o un decimale

INSERT()

Inserisce una sottostringa nella posizione specificata per il numero specificato di caratteri

INSTR()

Restituisce l’indice della prima occorrenza della sottostringa

LEFT()

Restituisce il numero specificato di caratteri da sinistra

LENGTH()

Restituisce la lunghezza di una stringa in byte

LIKE

Corrispondenza semplice dello schema

LOAD_FILE()

Carica il file indicato

LOCATE()

Restituisce la posizione della prima occorrenza di una sottostringa

LOWER()

Restituisce l’argomento in caratteri minuscoli

LPAD()

Restituisce la stringa dell’argomento con l’aggiunta a sinistra della stringa specificata

LTRIM()

Elimina gli spazi vuoti in testa

MAKE_SET()

Restituisce un insieme di stringhe separate da virgole che hanno impostato il bit corrispondente nell’insieme dei bit

MATCH

Esegue una ricerca di corrispondenza su tutto il testo

MID()

Restituisce una sottostringa che inizia dalla posizione specificata

NOT LIKE

Negazione della corrispondenza semplice dello schema

OCT()

Restituisce una stringa con la rappresentazione in ottale di un numero

ORD()

Restituisce il codice del carattere per il carattere più a sinistra dell’argomento

QUOTE()

Attribuisce un codice di escape all’argomento per poterlo usare in un enunciato SQL

REGEXP

Ricerca di corrispondenza con uno schema utilizzando espressioni regolari

REPEAT()

Ripete una stringa il numero specificato di volte

REPLACE()

Rimpiazza le occorrenze di una stringa specificata

REVERSE()

Inverte i caratteri in una stringa

RIGHT()

Restituisce il numero specificato di caratteri da destra

RPAD()

Accoda una stringa il numero specificato di volte

RTRIM()

Elimina gli spazi vuoti in fondo

Nelle implementazioni commerciali di SQL (DB2, Oracle, MySQL, SQL Server e via enumerando) si possono utilizzare tutte le funzioni SQL standard, più numerosissime altre (in MySQL sono più di cento), soprattutto di tipo matematico, che mettono a disposizione una notevole flessibilità per ricavare valori calcolati dalle tabelle di un database. Fanno inoltre parte dello standard di SQL un certo numero di simboli, chiamati operatori, utilizzati per indicare operazioni aritmetiche o logiche: sono elencati nella Tabella 4.2. Simbolo

Tabella 4.4 Operatori aritmetici e logici di SQL. Ut ilizzo

+

Addizione e concatenamento

-

Sottrazione

*

Moltiplicazione

\

Divisione

=

Uguaglianza

oppure !=

Disuguaglianza




Confronto per maggiore

=

Confronto per maggiore o uguale

Vediamo ora con alcuni esempi, come ottenere maggiori informazioni da una query utilizzando funzioni e operatori aritmetici. Nei record che compongono la tabella Indirizzi c’è un campo DataNascita che contiene la data di nascita della persona. Vogliamo sapere quando cade il compleanno di ciascuna persona elencata nella tabella. In altri termini, desideriamo conoscere il giorno e il mese di nascita di ciascuno. Per ottenere questo risultato possiamo utilizzare una query impostata così: SELECT Nome, DAYOFMONTH(DataNascita) AS Giorno, MONTH(DataNascita) AS Mese, DataNascita FROM Indirizzi

L’esecuzione della query produce il seguente risultato: Nome

Giorno

Mese

Dat aNascit a

Ivo

9

11

1985-11-09

Luigi

28

6

1961-06-28

Bruna

8

10

1958-10-08

Rino

16

11

1959-11-16

Alfredo

1

9

1968-09-01

Aldo

20

9

1963-09-20

Rosario

26

8

1961-08-26

Enrico

17

6

1981-06-17

Enrica

14

2

1958-02-14

Peter

28

8

1958-08-28

Matteo

4

3

1965-03-04

Argia

28

12

1963-12-28

Franz

5

1

1980-01-05

Angelo

31

8

1969-08-31

Giuditta

6

4

1979-04-06

Franco

30

6

1964-06-30

Alina

7

10

1957-10-07

Ivo

11

1

1971-01-11

Gino

8

10

1959-10-08

Mario

25

6

1964-06-25

Romeo

16

8

1958-08-16

Marina

14

7

1958-07-14

Ugo

26

12

1957-12-26

Olga

15

5

1961-05-15

Walter

5

10

1967-10-05

Hu

20

2

1958-02-20

Hussein

16

11

1967-11-16

Antonietta

2

6

1971-06-02

La query utilizza due funzioni di MySQL – DAYOFMONTH() e MONTH() – che prendono come argomento (il riferimento fra le parentesi tonde) il valore della colonna DataNascita, estraendo rispettivamente il mese e il giorno dalla data contenuta in quella colonna. Le intestazioni delle due colonne generate dalle funzioni sono state create con il qualificatore AS . La stessa query in SQL Server si crea utilizzando la funzione DATEPART(), che accetta come argomenti i valori day, month e year che estraggono rispettivamente il giorno, il mese e l’anno da un campo con tipo dato DATE fornito come secondo argomento. SELECT Nome, DATEPART(day,DataNascita) AS Giorno, DATEPART(month,DataNascita) AS Mese, DataNascita FROM Indirizzi

Proviamo ora a lavorare sulle stringhe, utilizzando la funzione CONCAT, che è la versione MySQL e SQL Server della funzione SQL standard CONCATENATE. La sua struttura sintattica è molto semplice CONCAT(stringa1, stringa2, [,...n])

Gli elementi da concatenare sono separati da virgole e possono essere valori di colonne oppure stringhe di caratteri racchiuse fra virgolette semplici. Eseguendo questa query: SELECT CONCAT(Nome, ' ', Cognome) AS 'Nome completo' FROM Indirizzi ORDER BY Cognome

concateniamo i valori delle colonne Nome e Cognome, inserendo fra i due una stringa formata da un carattere spazio, per tenere separato il nome dal cognome (senza questa stringa di separazione, otterremmo MarioRossi invece di Mario Rossi). Già che ci siamo, ordiniamo le righe in base al campo Cognome. Possiamo vedere il risultato della query nella Figura 4.8. Si osservi che la clausola ORDER BY Cognome

ha funzionato correttamente anche se la clausola SELECT ha utilizzato un alias.

È consentito utilizzare una funzione come argomento di un’altra funzione. In casi di questo genere, SQL sviluppa la funzione annidata ricavando un valore che diventa argomento della funzione che fa da contenitore. Con questa query: SELECT Nome, Cognome, CONCAT(DAYOFMONTH(DataNascita), " - ", MONTHNAME(DataNascita)) AS Compleanno, DataNascita FROM Indirizzi ORDER BY Cognome

utilizziamo la funzione MONTHNAME(), che è specifica di MySQL e, come si può vedere nella figura, ricava il nome (in inglese) del mese dall’argomento di tipo Date che le viene fornito (DataNascita, nel nostro caso). La funzione CONCAT() concatena le stringhe generate dalle due funzioni DAYOFMONTH() e , separandole con un trattino, come possiamo vedere dalla Figura 4.9.

MONTHNAME()

Figura 4.8 La colonna alias Nome completo aggrega le due colonne Nome e Cognome.

Figura 4.9 La colonna alias Compleanno aggrega due valori ricavati dalla colonna DataNascita.

Lo stesso risultato si ottiene in SQL Server utilizzando questo enunciato: SELECT Nome, Cognome, CONCAT(DATEPART(day,DataNascita),' - ',DATENAME(month,DataNascita)) AS Compleanno, DataNascita FROM Indirizzi ORDER BY Cognome

In SQL Server, l’equivalente di MONTHNAME() di MySQL è la funzione DATENAME() che prende due argomenti, il nome (in inglese) della componente di data e il nome del campo che contiene una data. Se SQL Server è installato con l’opzione della lingua italiana, il risultato della funzione DATENAME() è in italiano, quindi Novembre invece di November, se il campo al quale si riferisce è di tipo DATE e contiene il valore 11. Proviamo ora a vedere un utilizzo più interessante dell’annidamento di funzioni, cioè di una funzione che ne utilizza un’altra come suo argomento, o, più precisamente, acquisisce come argomento il risultato di un’altra funzione. Vogliamo sapere quanti anni hanno, alla data di oggi, le persone elencate nella tabella Indirizzi. Possiamo ottenere il risultato che ci interessa con questa query: SELECT Nome,

Cognome, YEAR(NOW())-YEAR(DataNascita) AS Età FROM Indirizzi ORDER BY Età

La query si serve di due funzioni di MySQL: YEAR() e NOW(). Con YEAR() si ricava il valore dell’anno da una data (nel caso specifico, il valore del campo DataNascita). La funzione NOW() non chiede un argomento, perché acquisisce la data e l’ora correnti del sistema e li mette a disposizione di SQL. In SQL Server l’equivalente della funzione NOW() di MySQL è la funzione GETDATE(), per cui la query corrispondente ha questa forma: SELECT Nome, Cognome, YEAR(GETDATE())-YEAR(DataNascita) AS Età FROM Indirizzi ORDER BY Età

Funzioni senza argoment i Quasi tutte le funzioni prevedono l’indicazione di uno o più argomenti entro la coppia di parentesi tonde che segue la parola chiave che identifica la funzione. La coppia di parentesi tonde deve essere scritta, senza alcun carattere al suo interno, anche quando la funzione, come NOW(), non richiede argomenti.

Dando alla funzione YEAR() come argomento la funzione NOW(), si ottiene il valore dell’anno della data del sistema. Sottraendo da questo valore l’anno contenuto nel campo DataNascita – ottenuto anche in questo caso mediante la funzione YEAR() – si ricava un valore che corrisponde all’età della persona nell’anno in corso, che viene presentato nel risultato della query (Figura 4.10) con la colonna alias Età.

Figura 4.10 La colonna alias Età è il risultato di un calcolo ottenuto con due funzioni.

Colonne calcolat e Le colonne alias, che si ottengono col qualificatore AS, possono contenere, come abbiamo appena visto, valori calcolati, ricavati mediante funzioni da valori esistenti in altre colonne. Questa funzionalità di SQL va tenuta ben presente quando si progettano le tabelle. I campi di una tabella devono contenere sempre valori indipendenti, non ricavabili da altri campi nella stessa tabella. Se, quando si utilizza il database, serve un valore che si può derivare da un campo esistente, è possibile creare una colonna alias, che dà dinamicamente il valore calcolato. Se avessimo inserito nella struttura della tabella Indirizzi un campo Età, di tipo INT, con l’età di ogni persona, questo valore sarebbe diventato obsoleto dopo pochi mesi. Avendo predisposto un campo DataNascita, possiamo ricavarne in qualunque momento una colonna Età, aggiornata all’anno in corso oppure riferita a un qualsiasi altro anno. Come regola generale, non si devono mai creare campi calcolati quando si struttura una tabella, ma vanno predisposti campi indipendenti, alcuni dei quali si potrebbero utilizzare eventualmente per generare colonne calcolate. Per esempio, in una tabella Prodotti può bastare un solo campo PrezzoUnitario: l’importo dell’IVA si può ricavare moltiplicando PrezzoUnitario per 0.20 (quando l’aliquota IVA è il 20%) e l’importo da fatturare si può ottenere moltiplicando PrezzoUnitario per 1.20.

Il risultato della query dimostra anche che la clausola ORDER BY può essere applicata su qualunque colonna o gruppo di colonne richiamate nella clausola SELECT della query,

quindi anche su una colonna calcolata o alias, come la colonna Età che abbiamo generato con le funzioni YEAR . Esaminiamo nei particolari come si può ottenere una colonna calcolata con un operatore aritmetico. Abbiamo una tabella Dipendenti che contiene gli stessi dati della tabella Indirizzi, con l’aggiunta di tre colonne: Regione, DataAssunzione e Bonus, quest’ultima contenente valori in euro. I dipendenti che risiedono in Piemonte sono stati assegnati a una commessa che durerà parecchio tempo e si realizzerà in Inghilterra, per cui il loro bonus dovrà essere pagato in sterline invece che in euro. Al momento in cui scriviamo (maggio 2016) un euro vale 0,76 sterline: si tratta quindi di individuare i dipendenti da assegnare alla nuova commessa e l’importo in sterline del bonus che hanno maturato. Con questa semplice istruzione otteniamo l’elenco dei dipendenti che ci interessano, insieme con il valore del bonus maturato da ciascuno di loro: SELECT Nome, Cognome, Bonus FROM dipendenti WHERE Regione = 'Piemonte'

Vediamo il risultato nella Figura 4.11.

Figura 4.11 Il risultato della query che individua i dipendenti da trasferire.

In questa query usiamo una nuova e fondamentale clausola dello Structured Query Language che si chiama WHERE e che approfondiremo nel prossimo capitolo: qui ci basterà sapere che con questa clausola si specifica un vincolo all’istruzione SELECT, indicando “dove” (“where” appunto) concentrare la selezione.

Si tratta adesso di eseguire il calcolo che riguarda il bonus dei quattro dipendenti assegnati alla nuova commessa. La query che ci serve può avere questa forma: SELECT Nome, Cognome, Bonus AS 'BonusEuro', Bonus*0.76) AS 'BonusPound' FROM dipendenti WHERE Regione = 'Piemonte'

producendo il seguente risultato: Nome

Cognome

BonusEuro

BonusPound

Matteo

Immuni

600

456.00

Romeo

Sogni

800

608.00

Rosario

Fuzzi

600

456.00

Ugo

Uguali

800

608.00

Abbiamo ottenuto il nuovo valore che ci interessa (la conversione in sterline dell’importo in euro usando come valore di cambio 0,76) usando l’operatore aritmetico asterisco (*), uno degli operatori aritmetici riepilogati sopra nella Tabella 4.2. Supponiamo di voler rateizzare in tre parti uguali il bonus da corrispondere in sterline, per poterlo erogare così rateizzato al verificarsi di tre distinti momenti di verifica dello sviluppo della commessa a Londra. Per eseguire questa divisione possiamo utilizzare l’operatore aritmetico / in questo modo: SELECT Nome, Cognome, Bonus/3) AS 'RataBonusEuro', Bonus*0.76)/3 AS 'RataBonusPound' FROM dipendenti WHERE Regione = 'Piemonte'

Il risultato in SQL Server si presenta in questo modo: Nome

Cognome

Rat aBonusEuro

Rat aBonusPound

Rosario

Fuzzi

200

152

Matteo

Immuni

200

152

Romeo

Sogni

266,666666666667

202,666666666667

Ugo

Uguali

266,666666666667

202,666666666667

e analogamente in MySQL: Nome

Cognome

Rat aBonusEuro

Rat aBonusPound

Rosario

Fuzzi

200.0000

152.000000

Matteo

Immuni

200.0000

152.000000

Romeo

Sogni

266.6667

202.666667

Ugo

Uguali

266.6667

202.666667

Osserviamo che i valori delle rate in euro e in sterline sono rappresentati con molti decimali: trattandosi di importi in denaro, bastano due soli decimali, che potremmo ottenere ricorrendo in MySQL a una fra due funzioni aritmetiche: TRUNCATE o ROUND . Entrambe prevedono due argomenti: TRUNCATE(X,D) ROUND(X,D)

dove X è il valore decimale sul quale agire e D è il numero di decimali che si vogliono ottenere. Se eseguissimo di nuovo la query con la funzione TRUNCATE impostata su due posizioni decimali: SELECT Nome, Cognome, TRUNCATE(Bonus/3,2) AS 'RataBonusEuro', TRUNCATE((Bonus*0.76)/3,2) AS 'RataBonusPound' FROM dipendenti WHERE Regione = 'Piemonte'

otterremmo in MySQL un risultato di questo genere: Nome

Cognome

Rat aBonusEuro

Rat aBonusPound

Rosario

Fuzzi

200.00

152.00

Matteo

Immuni

200.00

152.00

Romeo

Sogni

266.66

202.66

Ugo

Uguali

266.66

202.66

Come possiamo vedere, TRUNCATE() si limita a tagliar via i decimali in eccesso. È un po’ drastico, perché in alcuni record il terzo e il quarto decimale formano un valore maggiore di 50. Ricorriamo allora alla funzione ROUND : SELECT Nome, Cognome, ROUND(Bonus/3,2) AS 'RataBonusEuro', ROUND((Bonus*0.76)/3,2) AS 'RataBonusPound' FROM dipendenti WHERE Regione = 'Piemonte'

che produce un risultato più corretto, come possiamo vedere dalla Figura 4.12. NOT A La funzione TRUNCATE() di MySQL non va confusa con il comando TRUNCATE TABLE, una variante del comando DELETE che abbiamo descritto alla fine del Capitolo 3. La funzione ROUND() esiste anche in SQL

Server e funziona esattamente come quella che abbiamo visto con MySQL. Non è disponibile, invece, in SQL Server la funzione TRUNCATE().

Figura 4.12 Il risultato delle divisioni arrotondato con la funzione ROUND.

Nell’ipotesi che presto o tardi a tutti i dipendenti possa capitare di dover lavorare per un lungo periodo nel Regno Unito, trasformiamo la colonna BonusPound usata fin qui come alias in una colonna effettiva della tabella Dipendenti. Creiamo in primo luogo una nuova colonna nella tabella Dipendenti di MySQL con questo enunciato: ALTER TABLE Dipendenti ADD BonusPound DECIMAL(6,2) NOT NULL

Verifichiamo poi, con: DESCRIBE Dipendenti

che la tabella Dipendenti contenga effettivamente una colonna BonusPound di tipo DECIMAL (6,2). Eseguiamo successivamente questo enunciato: UPDATE Dipendenti SET BonusPound=ROUND(Bonus*0.76,2)

Formulato in questo modo, cioè senza una clausola WHERE, il comando UPDATE esegue l’operazione indicata dalla clausola SET su tutti i record. Questa clausola può contenere un valore assoluto (per esempio una stringa o un numero) oppure una espressione (come in questo caso). La presenza dell’espressione: BonusPound=ROUND(Bonus*0.76,2)

fa sì che il valore da immettere nel campo BonusPound venga ricalcolato per ogni singolo record in funzione del valore del campo Bonus dello stesso record.

Possiamo controllare il risultato eseguendo l’enunciato: SELECT Cognome, Bonus, BonusEuro FROM Dipendenti

In entrambi i sistemi dovremmo vedere lo stesso risultato. In SQL Server la scheda Risultato 1 dovrebbe presentarsi come nella Figura 4.13. Quando si ha bisogno di aggiornare automaticamente il contenuto di un’intera colonna, è sempre opportuno eseguire prima una query SELECT con il qualificatore AS , per controllare che cosa si ottiene nella colonna alias. Quando il risultato è soddisfacente, si può procedere alla modifica effettiva della tabella con il comando UPDATE, utilizzando nella sua clausola SET l’espressione che ha generato la colonna alias. Se, a un certo punto, ritenessimo che la colonna BonusPound non sia più necessaria, perché non ci sono più commesse attive in Inghilterra, possiamo eliminarla con questo semplice enunciato: ALTER TABLE Dipendenti DROP COLUMN BonusPound

Figura 4.13 La colonna BonusPound è stata popolata automaticamente con i valori calcolati.

Funzioni di aggregazione Quelle che abbiamo visto finora sono funzioni scalari, che agiscono su un solo valore, quello indicato come argomento in ciascuno degli esempi. Eseguendo una funzione scalare con un enunciato SELECT, il risultato restituito dalla funzione viene assegnato a ogni singola riga richiamata da SELECT. Le funzioni di aggregazione producono un risultato diverso: non generano righe modificate, ma producono un unico valore, ottenuto prendendo in considerazione tutta la colonna o le colonne indicate come argomento. Vediamo un primo, semplice esempio: vogliamo conoscere l’età media e l’anzianità di servizio media degli impiegati i cui dati si trovano nella tabella Dipendenti, strutturata come si può vedere qui di seguito: Campo

T ipo dat i

IDPersona

int(11)

Appellativo

varchar(5)

Nome

varchar(25)

Cognome

varchar(25)

Indirizzo

varchar(30)

CAP

varchar(5)

Provincia

char(2)

Città

varchar(30)

DataNascita

date

DataAssunzione

date

Regione

varchar(15)

Bonus

decimal(6,2)

Verifichiamo preliminarmente le età e le anzianità di servizio effettive (all’anno in corso) degli impiegati eseguendo questa query in MySQL: SELECT Cognome, DataNascita, DataAssunzione, YEAR(NOW())-YEAR(DataAssunzione) AS Anzianità, YEAR(NOW())-YEAR(DataNascita) AS Età FROM Dipendenti ORDER BY Cognome

Le funzioni scalari YEAR() e NOW() producono il risultato che possiamo vedere nella Figura 4.14.

Figura 4.14 La query calcola e presenta le età e le anzianità dei singoli dipendenti.

Lo stesso risultato si ottiene in SQL Server utilizzando la funzione GETDATE() invece di :

NOW()

SELECT Cognome, DataNascita, DataAssunzione, YEAR(GETDATE())-YEAR(DataAssunzione) AS Anzianità, YEAR(GETDATE())-YEAR(DataNascita) AS Età FROM Dipendenti ORDER BY Cognome

Per ottenere la media delle età e delle anzianità di servizio possiamo usare la funzione di aggregazione AVG(), il cui nome deriva dall’inglese average, che vuol dire appunto media. Non dobbiamo fare altro che racchiudere fra le parentesi tonde della funzione le due funzioni scalari che abbiamo utilizzato prima, con qualche leggera modifica, in questo modo per MySQL: SELECT AVG(YEAR(NOW())-YEAR(DataAssunzione)) AS 'Anzianità media', AVG(YEAR(NOW())-YEAR(DataNascita)) AS 'Età media' FROM Dipendenti

e in quest’altro per SQL Server: SELECT AVG(YEAR(GETDATE())-YEAR(DataAssunzione)) AS 'Anzianità media', AVG(YEAR(GETDATE())-YEAR(DataNascita)) AS 'Età media' FROM Dipendenti

Il risultato in MySQL dovrebbe essere quello riportato nella Figura 4.15:

Figura 4.15 La query calcola e presenta i valori medi di età e di anzianità per l’intera tabella.

La funzione AVG() agisce sull’argomento che viene inserito fra le parentesi tonde, che può essere il nome di una colonna oppure un’espressione (come nella query che stiamo commentando). Oltre ad AVG(), fanno parte dello standard SQL anche le funzioni SUM , COUNT, MAX e MIN, che sono recepite in tutte le implementazioni di SQL. Lo schema sintattico delle funzioni di aggregazione è il seguente: nome_della_funzione ([ALL | DISTINCT] espressione)

La clausola ALL può non essere specificata, perché è il modo di operare predefinito, che prende in considerazione tutti i valori indicati nell’espressione. La clausola DISTINCT fa in modo che la funzione prenda in considerazione soltanto i valori diversi fra quelli indicati. La funzione COUNT consente tre formulazioni diverse: Conteggia tutte le righe di una tabella, comprese quelle che contengono

COUNT(*)

valori NULL . espressione) Conteggia tutte le righe che hanno valori diversi da NULL nella

COUNT(

colonna specificata con l’espressione. COUNT(DISTINCT espressione) Conteggia il numero di righe nella colonna indicata dall’espressione che non contengono valori NULL e che hanno valori diversi. Le funzioni AVG , SUM , MAX e MIN lavorano soltanto su valori numerici e ignorano le righe che contengono NULL . Possiamo utilizzare la funzione ROUND per eliminare i decimali dal risultato. Modificando in questo modo la query:

SELECT ROUND(AVG(YEAR(NOW())-YEAR(DataAssunzione)),0) AS 'Anzianità media', ROUND(AVG(YEAR(NOW())-YEAR(DataNascita)),0) AS 'Età media' FROM Dipendenti

otteniamo questo risultato: Anzianità media Età media --------------- --------27 51

La clausola GROUP BY In molti casi si utilizzano le funzioni di aggregazione insieme con la clausola GROUP BY del comando SELECT, clausola che, come lascia capire il suo nome, ha funzioni di raggruppamento. Quando si utilizza la clausola GROUP BY si deve segnalare sia in SELECT sia in GROUP BY la stessa colonna, che è quella in base alla quale si intende raggruppare il risultato della funzione di aggregazione. Vediamo come si può utilizzare la clausola GROUP BY per conoscere l’età media e l’anzianità di servizio media degli impiegati suddivisi per regione. In MySQL la query può essere formulata in questo modo: SELECT Regione, ROUND(AVG(YEAR(NOW())-YEAR(DataAssunzione)) ) AS 'Anzianità media', ROUND(AVG(YEAR(NOW())-YEAR(DataNascita))) AS 'Età media' FROM Dipendenti GROUP BY Regione

Il risultato dovrebbe presentarsi come nella Figura 4.16.

Figura 4.16 Le funzioni di aggregazione possono dare risultati distinti per gruppi diversi.

In quest’ultima query, la clausola GROUP BY Regione

fa sì che le due medie siano calcolate raggruppando i valori di Età e di Anzianità in base ai valori diversi presenti nel campo Regione della tabella.

Non è indispensabile specificare la colonna di raggruppamento nella clausola SELECT, però, formulata in questo modo: SELECT ROUND(AVG(YEAR(NOW())-YEAR(DataAssunzione)) ) AS 'Anzianità media', ROUND(AVG(YEAR(NOW())-YEAR(DataNascita))) AS 'Età media' FROM Dipendenti GROUP BY Regione

la query produrrebbe un risultato poco comprensibile, anche se aritmeticamente corretto: Anzianit à media

Et à media

32

55

23

49

25

49

32

57

26

49

32

59

È consentito elencare nella clausola SELECT più campi oltre a quello utilizzato per il raggruppamento, ma si ottengono risultati sconcertanti. Per esempio, questa query in MySQL e in SQL Server: SELECT Regione, COUNT(*) AS Dipendenti FROM Dipendenti GROUP BY Regione

fornisce un conteggio delle persone presenti in ciascuna Regione, come illustra la Figura 4.17.

Figura 4.17 La funzione di aggregazione COUNT insieme con la clausola GROUP BY.

Però, se aggiungessimo un altro campo in SELECT, per esempio Cognome, SELECT Regione, Cognome, COUNT(*) AS Dipendenti FROM Dipendenti GROUP BY Regione

otterremmo questo risultato: Regione

Cognome

Dipendent i

Emilia

Ghisalberti

3

Liguria

Yassud

1

Lombardia

Antimi

13

Piemonte

Fuzzi

4

Trentino

Hermann

4

Veneto

Quadri

1

Questo risultato deriva dal fatto che il campo Cognome non è elencato nella clausola GROUP BY per cui, nell’eseguire la query, SQL preleva soltanto il primo valore che trova nel campo Cognome in ogni gruppo regionale. Niente impedisce di indicare più di una colonna nella clausola GROUP BY. Per esempio, questa query:

SELECT Regione, Nome, COUNT(*) AS Dipendenti FROM Dipendenti GROUP BY Regione, Nome

genera in MySQL e in SQL Server il risultato che possiamo vedere nella Figura 4.18. Siccome le 27 persone della tabella Dipendenti hanno tutte un nome diverso, tranne due, GROUP BY ha generato 25 gruppi di una sola persona e 1 gruppo con due persone. In altri termini, se si chiede un raggruppamento su più di una colonna, se ne ottiene comunque uno solo. Non è consentito formulare query che mescolano assieme nella clausola SELECT riferimenti a colonne effettive e a valori da ottenere con funzioni di aggregazione senza specificare anche la clausola GROUP BY. Per esempio, questa query: SELECT Regione, COUNT(*) AS Dipendenti FROM Dipendenti

produce un errore, che in SQL Server viene segnalato in questo modo: La colonna ‘Dipendenti.Regione’ non è valida nell’elenco di selezione perché non è inclusa né in una funzione di aggregazione né nella clausola GROUP BY.

mentre viene eseguita imperturbabilmente in MySQL, dando il bislacco risultato Regione Lombardia

Dipendent i 27

Figura 4.18 Come agisce la clausola di raggruppamento quando si indicano due colonne.

La clausola GROUP BY dispone i risultati della query nell’ordine alfabetico della colonna alias generata dalla funzione di aggregazione sulla quale si fonda, quindi, la query SELECT Regione, SUM(Bonus) AS TotaleBonus FROM Dipendenti GROUP BY Regione

genererebbe questo risultato: Regione

Tot aleBonus

Emilia

2600

Liguria

600

Lombardia

9600

Piemonte

2800

Trentino

2600

Veneto

600

Si può modificare questa impostazione predefinita aggiungendo alla query una clausola ORDER BY: SELECT Regione, SUM(Bonus) AS TotaleBonus FROM Dipendenti GROUP BY Regione ORDER BY TotaleBonus

Il risultato verrebbe presentato in questo modo: Regione

Tot aleBonus

Liguria

600

Veneto

600

Trentino

2600

Emilia

2600

Piemonte

2800

Lombardia

9600

In tutte le query che abbiamo presentato in questo capitolo abbiamo utilizzato una sola volta la clausola WHERE. In assenza di questa clausola, SELECT estrae intere colonne, producendo, quindi, una specie di tabella virtuale, composta da un sottoinsieme delle colonne della tabella indicata nella clausola FROM . Queste tabelle virtuali nel gergo di SQL prendono il nome di proiezioni. Esistono, come vedremo più avanti, comandi SQL con i quali si può trasformare una proiezione in una tabella effettiva. Gli elementi estratti in base a un criterio espresso con la clausola WHERE si chiamano selezioni. Anche le selezioni si possono trasformare in tabelle effettive, ma di solito si generano per ottenere un risultato specifico: una serie di informazioni accurate, estratte da una o più tabelle, secondo un determinato criterio. La clausola WHERE ha un ruolo fondamentale nelle query e la trattiamo separatamente nel prossimo capitolo.

Capitolo 5

Creare selezioni

Con la clausola WHERE del comando SELECT si specifica un criterio in base al quale selezionare il contenuto delle colonne indicate nella clausola SELECT. Il criterio si formula con un’espressione, costruita con gli operatori di confronto: > maggiore di < minore di = uguale a >= maggiore di o uguale a 500

oppure può combinare assieme più criteri, associandoli con gli operatori logici, come in questo caso: WHERE Prezzo > 500 AND Colore = "Rosso"

Gli elementi messi a confronto devono essere omogenei: numeri con numeri, stringhe con stringhe e date con date. Non si possono usare gli operatori di confronto fra elementi con tipo dati NULL . Quando l’espressione utilizzata nella clausola WHERE fa riferimento a stringhe, queste vanno racchiuse fra virgolette semplici. Inoltre, l’espressione può richiamare funzioni e utilizzare operatori aritmetici. Se in un enunciato SELECT la clausola FROM indica più di una sola tabella come origine dei dati, il criterio espresso con WHERE può riferirsi a varie caratteristiche di colonne ricavate da tutte le tabelle interessate dalla query, circoscrivendo in modo molto accurato il risultato che si intende ottenere. In questo capitolo facciamo vedere come si utilizza la clausola WHERE: iniziamo con alcuni esempi semplici per poi passare a forme via via più complesse.

Per agevolare il riscontro dei risultati degli esempi, riportiamo qui di seguito la strttura delle due tabelle sulle quali eseguiremo gran parte delle query dimostrative. La tabella Indirizzi:

La tabella Dipendenti:

Selezionare con criteri semplici Per sapere quali persone nella tabella Dipendenti hanno un bonus superiore a 600 euro, possiamo eseguire questa query: SELECT Nome, Cognome, Bonus FROM Dipendenti WHERE Bonus > 600

Il risultato dovrebbe presentarsi come nella Figura 5.1.

Figura 5.1 Le persone che hanno un bonus superiore a €600.

La clausola WHERE deve essere seguita da almeno un’espressione, che specifica il criterio da rispettare per la selezione. In questo caso, l’espressione è Bonus > 600

Se, invece di conoscere i nomi delle persone che hanno un bonus maggiore di 600 euro, volessimo sapere soltanto quante sono, la query può essere riformulata utilizzando una funzione di aggregazione: SELECT COUNT(Bonus) AS 'Bonus oltre 600' FROM Dipendenti WHERE Bonus > 600

La query stabilisce il criterio di selezione per la funzione COUNT, che esegue il conteggio dei soli record della tabella Dipendenti che hanno un valore maggiore di 600 nel campo Bonus. Il risultato di questa query è riportato nella Figura 5.2.

Figura 5.2 La clausola WHERE delimita l’argomento della funzione COUNT.

Nella query abbiano utilizzato il qualificatore AS per avere un’intestazione più leggibile nel risultato (se mancasse, il risultato comparirebbe con l’intestazione COUNT(Bonus)).

Selezionare valori NULL Gli operatori di confronto non funzionano sui campi che hanno come tipo dato NULL . Per selezionare campi di questo tipo bisogna ricorrere a due operatori speciali: IS NULL e IS NOT NULL

, con i quali, come lasciano intendere le loro parole chiave, si verifica se un

campo contiene oppure no un valore NULL (si ricordi che NULL non è uno zero e neppure una stringa vuota). Modifichiamo un record della tabella Dipendenti, attribuendo il valore NULL al campo Regione del record che ha come chiave primaria il 27, in questo modo: UPDATE Dipendenti SET Regione= Null WHERE IDPersona=27

A questo punto, l’enunciato: SELECT * FROM Dipendenti WHERE Regione IS NOT NULL

estrae 26 record invece dei 27 contenuti nella tabella Dipendenti che abbiamo modificato. Formulando una query in questo modo: SELECT COUNT(*) AS 'Regione non definita' FROM Dipendenti WHERE Regione IS NULL

otteniamo come risultato il valore 1, corrispondente al numero di record nei quali il campo Regione contiene NULL .

Se, invece, usassimo un operatore di confronto al posto dell’operatore IS NULL : SELECT COUNT(*) FROM Dipendenti WHERE Regione = NULL

la query darebbe come risultato zero invece di uno. E lo stesso accadrebbe scrivendo la clausola WHERE in questo modo: WHERE Regione NULL

mentre otterremmo un valore corretto (26), formulando la clausola con l’operatore IS NOT NULL

:

SELECT COUNT(*) FROM Dipendenti WHERE Regione IS NOT NULL

Gli operatori logici In SQL si possono utilizzare gli operatori logici classici, identificati dalle parole chiave AND , OR , NOT, più altri, specifici del linguaggio, che hanno la forma BETWEEN. . . AND e . La regola sintattica per il loro utilizzo è molto semplice e si può cogliere

LIKE

direttamente con alcuni esempi. Con questa query: SELECT IDPersona, Nome, Cognome, Prov FROM Indirizzi WHERE Prov ='MI' OR Prov ='PV' ORDER BY Prov

otteniamo il risultato che possiamo vedere nella Figura 5.3. Abbiamo aggiunto la clausola ORDER BY per elencare separatamente le righe del risultato.

Figura 5.3 Nelle due province di Milano e di Pavia risiedono sette persone.

L’uso dell’operatore logico OR fra i due criteri Prov ='MI' e Prov ='PV' significa che sono da estrarre soltanto i record che hanno la stringa 'MI' oppure la stringa 'PV' nel campo Prov. Vogliamo sapere quali signore laureate sono nate dopo il 1972. Per farlo, eseguiamo questa query: SELECT Appellativo, Nome, Cognome, DataNascita FROM Indirizzi WHERE Appellativo = 'Dr.sa' AND YEAR(DataNascita)> 1970

Il risultato dovrebbe presentarsi come nella Figura 5.4.

Figura 5.4 Esiste una sola dottoressa nata dopo il 1970.

L’utilizzo di un solo criterio di selezione, come in questa query: SELECT Appellativo, Nome, Cognome, DataNascita FROM Indirizzi WHERE Appellativo = 'Dr.sa'

avrebbe generato il risultato che possiamo vedere nella Figura 5.5.

Figura 5.5 La tabella Indirizzi contiene i dati di tre dottoresse.

L’aggiunta dell’operatore logico AND nella clausola WHERE impone che vengano selezionati i record che hanno “Dr.sa” nel campo Appellativo e contemporaneamente un valore maggiore di 1970 nella parte anno del campo DataNascita, per cui la query della Figura 5.5 genera tre righe, mentre quella della Figura 5.4 ne genera una sola. È consentito utilizzare più di un solo operatore logico nello stesso criterio di selezione. Per esempio, con questa query: SELECT Nome, Cognome FROM Dipendenti WHERE DataNascita < '1970-01-01' AND Bonus > 600 AND Provincia = 'MI'

si ottiene un solo record, mentre con quest’altra query:

SELECT Nome, Cognome FROM Dipendenti WHERE DataNascita < '1970-01-01' AND Bonus > 600 OR Provincia = 'MI'

i record che soddisfano la condizione sono dieci: l’aggiunta di un nuovo operatore AND restringe il criterio di selezione, mentre l’aggiunta di un OR lo amplia. Vediamo un altro esempio di utilizzo di più operatori logici in una clausola WHERE un po’ articolata. Vogliamo individuare nella tabella Indirizzi i nomi delle persone che sono nate sotto il segno zodiacale dei Pesci, cioè in una data compresa fra il 19 febbraio e il 20 marzo. Per individuare le componenti del campo DataNascita che ci interessano, il giorno del mese e il mese dell’anno, utilizziamo due funzioni disponibili in MySQL che abbiamo già visto nel Capitolo 4, e precisamente: , che estrae il mese (da 1 a 12) e

MONTH

, che estrae il giorno del mese (da 1 a 31)

DAYOFMONTH

In SQL Server si ottengono gli stessi risultati con la funzione DATEPART(), che ha la seguente sintassi: DATEPART( componente_data, valore_data)

Il parametro componente_data può essere: YEAR

(per ottenere l’anno); per il mese (si ottiene un valore da 1 a 12);

MONTH

per il giorno (si ottiene un valore da 1 a 31);

DAY

DAYOFYEAR

per il giorno dell’anno (si ottiene un numero fra 1 e 366) e

per il giorno della settimana (si ottiene un numero da 1 a 7 dove 1 indica il

WEEKDAY

lunedì). Il parametro valore_data può essere un valore assoluto (una data scritta fra apici come per esempio '2016-09-24') oppure il riferimento a un campo con tipo dato DATE o DATETIME in una tabella. La query che ci interessa può avere questa forma: SELECT Nome, Cognome, DataNascita AS 'Bilancia' FROM Indirizzi WHERE DAYOFMONTH(DataNascita) >= 23 AND MONTH(DataNascita) = 9

OR DAYOFMONTH(DataNascita) = 23 AND DATEPART(MONTH,DataNascita) = 9 OR DATEPART(DAY, DataNascita) 2400

Così facendo, però, si ottiene una segnalazione di errore, che viene denunciata come errore di sintassi sia in MySQL sia in SQL Server. Come che sia, errore di sintassi o no, il fatto vero è che non è consentito ricorrere alla clausola WHERE per stabilire un criterio di selezione riferito a valori raggruppati mediante la clausola GROUP BY. Per ottenere il risultato che ci interessa, dobbiamo servirci della clausola HAVING , che svolge correttamente il ruolo di WHERE in riferimento a un valore ottenuto mediante la clausola di raggruppamento GROUP BY. Struttura sintattica e comportamento di HAVING sono gli stessi di WHERE. Questa query, che utilizza HAVING , genera correttamente in MySQL il risultato della Figura 5.13.

SELECT Regione, SUM(Bonus) AS TotaleBonus FROM Dipendenti GROUP BY Regione HAVING TotaleBonus > 2400

Nella stessa figura vediamo che per ottenere lo stesso risultato in SQL Server la query deve essere formulata in questo modo: SELECT Regione, SUM(Bonus) AS TotaleBonus FROM Dipendenti GROUP BY Regione HAVING SUM(Bonus) > 2400

Figura 5.13 La query di raggruppamento individua soltanto alcuni valori.

La clausola WHERE non si può utilizzare su valori raggruppati, cioè ottenuti con GROUP BY, ma è perfettamente utilizzabile in riferimento a valori sui quali agiscono funzioni di aggregazione, come per esempio: SELECT SUM(Bonus) AS TotaleBonus

FROM Dipendenti WHERE Bonus < 700

Sia in MySQL sia in SQL Server, questa query calcola correttamente e presenta nella colonna alias TotaleBonus un unico valore che è la sommatoria dei valori della colonna Bonus prendendo in considerazione soltanto quelli maggiori di 700. L’utilizzo contemporaneo in una stessa query delle clausole WHERE e HAVING è consentito, purché siano riferite a valori appropriati, però richiede particolare attenzione, perché può indurre a pericolosi errori logici, che in quanto tali non possono essere segnalati da SQL. Vediamo un esempio puntuale. Disponiamo di una tabella Assegni, che ha questo contenuto: IDAss

NumAss

Beneficiario

Dat a

Import o

Causale

1

8.009

New Leasing SpA

2016-01-05

250.75

Rata leasing auto

2

8.010

Anna Rossi

2016-01-10

600.80

Assegno mensile Gennaio

3

8.008

ASM SpA

2016-01-15

150.25

Servizi municipali

4

8.011

Cassa

2016-01-18

120.00

Prelievo contante

5

8.012

Superette Aurora Srl

2016-01-12

600.98

Forniture alimentari

6

8.013

Lina Devoti

2016-01-15

510.80

Servizi domestici

7

8.014

Cassa

2016-01-20

110.00

Prelievo contante

8

8.015

Immobiliare Dante Srl

2016-01-22

1781.78

Affitto I Trimestre

9

8.016

Cassa

2016-01-24

150.00

Prelievo contante

10

8.016

Superette Aurora Srl

2016-01-25

300.15

Forniture alimentari

11

8.018

Cassa

2016-01-30

130.00

Prelievo contante

12

8.019

ASM SpA

2016-01-02

98.92

Servizi municipali

13

8.020

Cassa

2016-01-02

75.50

Prelievo contante

14

8.022

Superette Aurora Srl

2016-01-25

90.75

Materiali pulizia

Eseguiamo questa query in MySQL con una clausola GROUP BY e una clausola HAVING : SELECT Beneficiario, SUM(Importo) as Totale, COUNT(Beneficiario) Numero_assegni FROM Assegni GROUP BY Beneficiario HAVING Totale > 100

La stessa query in SQL Server si presenta in questo modo: SELECT Beneficiario, SUM(Importo) as Totale, COUNT(Beneficiario) Numero_assegni FROM Assegni GROUP BY Beneficiario HAVING SUM(Importo) > 100

Otteniamo in entrambi i sistemi questo risultato: Beneficiario Anna Rossi

Tot ale 600.80

Numero_assegni 1

ASM SpA

249.17

2

Cassa

585.50

5

Immobiliare Dante Srl

1781.78

1

Lina Devoti

510.80

1

New Leasing SpA

250.75

1

Superette Aurora Srl

991.88

3

Modifichiamo la query in MySQL, introducendo anche una clausola WHERE, riferendola correttamente a una colonna non ottenuta per raggruppamento. SELECT Beneficiario, SUM(Importo) Totale, COUNT(Beneficiario) Numero_assegni FROM Assegni WHERE Importo >=150 GROUP BY Beneficiario HAVING Totale > 100

La stessa query in SQL Server si presenta in questo modo: SELECT Beneficiario, SUM(Importo) Totale, COUNT(Beneficiario) Numero_assegni FROM Assegni WHERE Importo >=150 GROUP BY Beneficiario HAVING SUM(Importo) > 100

La query produce un risultato ben diverso, uguale in tutti e due i sistemi: Beneficiario

Tot ale

Numero_assegni

Anna Rossi

600.80

1

ASM SpA

150.25

1

Cassa

150.00

1

Immobiliare Dante Srl

1781.78

1

Lina Devoti

510.80

1

New Leasing SpA

250.75

1

Superette Aurora Srl

901.13

2

Che cosa è accaduto? Quello che accade sempre quando si fa un errore logico: il computer (meglio, lo Structured Query Language, in questo caso) fa quello che gli abbiamo detto di fare, purché sia corretto sintatticamente, anche se è sbagliato logicamente. Per effetto della clausola WHERE, la clausola GROUP BY viene eseguita soltanto sui valori maggiori di o uguali a 150 contenuti nella colonna Importo, quindi i gruppi sono gli stessi (sette in entrambe le query), ma i valori delle colonne alias Totale e Numero_assegni sono diversi nei gruppi ASM SpA, Cassa e Superette Aurora Srl.

La funzione CASE

La funzione CASE, introdotta nello standard del linguaggio soltanto con la versione SQL99, mette a disposizione funzionalità per confronti iterativi (del genere IF...THEN...ELSE) utilizzabili entro un enunciato SELECT o UPDATE. Con CASE si valuta un elenco di condizioni e si ricava un valore da ciascuna delle condizioni valutate. La sintassi di CASE prevede due varianti: una per eseguire un confronto diretto su un valore (di solito, il contenuto di un campo); un’altra per eseguire confronti su valori logici, di solito derivati da una funzione che può restituire un valore logico vero o falso. Questa è la prima variante. CASE valore WHEN valore_di_confronto THEN risultato [WHEN valore_di_confronto THEN risultato] [ELSE risultato_alternativo] END

Questa variante restituisce il risultato quando valore = valore_di_confronto. Il risultato_alternativo viene restituito se valore valore_di_confronto. Questa è la seconda variante. CASE WHEN valore_logico THEN risultato [WHEN valore_logico THEN risultato] [ELSE risultato_alternativo] END

Questa variante restituisce il risultato quando il valore_logico esaminato si dimostra vero. Se nessun valore_logico risulta vero, viene restituito il risultato_alternativo indicato con ELSE. Se non c’è la clausola ELSE, viene restituito NULL . Una query costruita con la prima variante della funzione CASE potrebbe essere scritta in questo modo e funziona in MySQL come in SQL Server: SELECT CASE Appellativo WHEN 'Dr.' THEN 'Laureato' WHEN 'Dr.sa' THEN 'Laureata' WHEN 'Prof.' THEN 'Laureato' WHEN 'Ing.' THEN 'Laureato' ELSE 'Senza laurea' END AS 'Titolo di studio', CONCAT(Appellativo, ' ', Cognome) AS 'Nominativo' FROM Indirizzi

La formula CASE prende in considerazione la colonna Appellativo ed esegue quattro confronti fra il suo valore e altrettante stringhe, ciascuna delle quali è un valore_di_confronto diverso. Quando trova coincidenza fra un valore e un valore_di_confronto sostituisce il valore con il risultato nella riga che va a comporre. Se

nessuno dei quattro confronti produce un risultato, utilizza per la riga il risultato_alternativo. La Figura 5.14 mostra il risultato della query: nella colonna alias Titolo di studio compaiono i risultati o i risultati_alternativi generati dalla funzione CASE, che descrivono il titolo di studio delle persone, elencate nella colonna alias Nominativo, ottenuta per concatenamento delle colonne Appellativo e Cognome.

Figura 5.14 Un esempio di query costruita con la prima variante sintattica della funzione CASE.

Si noti che tutto quanto è compreso fra la riga che inizia con la parola chiave CASE e la riga che contiene la sola parola chiave END , è – in termini di struttura sintattica – un unico elemento della clausola SELECT, che individua una colonna da estrarre, quindi deve essere separato con una virgola dagli altri elementi che compongono la clausola SELECT. La stessa query si può scrivere in questo modo: SELECT CONCAT(Appellativo, " ", Cognome) AS "Nominativo", CASE Appellativo WHEN "Dr." THEN "Laureato" WHEN "Dr.sa" THEN "Laureata" WHEN "Prof." THEN "Laureato" WHEN "Ing." THEN "Laureato" ELSE "Senza laurea" END AS "Titolo di studio" FROM Indirizzi

ottenendo lo stesso risultato della versione precedente, con la sola differenza che la colonna alias “Nominativo” compare in prima posizione e la colonna alias “Titolo di

studio” in seconda posizione. Si osservi che nelle clausole WHEN...THEN non si esplicita un operatore di confronto, che è sottinteso, perché l’unico tipo di confronto possibile in questa variante di CASE è quello per uguaglianza. Diamo ora un esempio per la seconda variante della sintassi della funzione CASE. Nei paragrafi dedicati agli operatori logici e a BETWEEN...AND , abbiamo visto come si può ricavare il segno zodiacale di una persona sulla base del risultato della funzione appropriata per conoscere il numero ordinale del giorno in una data (un valore compreso fra 1 e 365) applicata a un campo che contiene una data di nascita. Come ricordiamo (si veda il paragrafo “Le funzioni” del Capitolo 4) questa funzione è DAYOFYEAR nel caso di MySQL ed è GETPART(DAYOFYEAR, data) in SQL Server, La seconda variante della sintassi di CASE ci consente di creare un’unica query che tiene conto di tutte le possibili coppie di valori numerici che individuano l’inizio e la fine di un segno zodiacale. Le coppie di numeri sui quali si basa la query sono riepilogate nella Tabella 5.1. Tabella 5.1 I segni zodiacali con le loro date e i giorni corrispondenti. Segno

Int ervallo dat e

Primo giorno

Ult imo giorno

Acquario

21 gennaio – 18 febbraio

21

49

Pesci

19 febbraio – 20 marzo

50

80

Ariete

21 marzo – 20 aprile

81

111

Toro

21 aprile – 20 maggio

112

141

Gemelli

21 maggio – 21 giugno

142

173

Cancro

22 giugno – 22 luglio

174

204

Leone

23 luglio – 22 agosto

205

235

Vergine

23 agosto – 22 settembre

236

266

Bilancia

23 settembre – 23 ottobre

267

297

Scorpione

24 ottobre – 22 novembre

298

327

Sagittario

23 novembre – 21 dicembre

328

356

Capricorno

22 dicembre – 20 gennaio

357

20

E questa è la query che individua il segno dello zodiaco ricavandolo dal campo DataNascita di ciascun record della tabella Indirizzi. SELECT CASE WHEN DAYOFYEAR(DataNascita) BETWEEN 21 AND 49 THEN 'Acquario' WHEN DAYOFYEAR(DataNascita) BETWEEN 50 AND 79 THEN 'Pesci' WHEN DAYOFYEAR(DataNascita) BETWEEN 80 AND 110 THEN 'Ariete' WHEN DAYOFYEAR(DataNascita)

BETWEEN 111 AND 140 THEN 'Toro' WHEN DAYOFYEAR(DataNascita) BETWEEN 141 AND 172 THEN 'Gemelli' WHEN DAYOFYEAR(DataNascita) BETWEEN 173 AND 203 THEN 'Cancro' WHEN DAYOFYEAR(DataNascita) BETWEEN 204 AND 235 THEN 'Leone' WHEN DAYOFYEAR(DataNascita) BETWEEN 236 AND 264 THEN 'Vergine' WHEN DAYOFYEAR(DataNascita) BETWEEN 265 AND 296 THEN 'Bilancia' WHEN DAYOFYEAR(DataNascita) BETWEEN 297 AND 326 THEN 'Scorpione' WHEN DAYOFYEAR(DataNascita) BETWEEN 327 AND 355 THEN 'Sagittario' ELSE 'Capricorno' END AS 'Segno zodiacale', Nome, Cognome, DataNascita AS 'Data di nascita' FROM Indirizzi ORDER BY 'Segno zodiacale'

Il risultato di questa query è riportato nella Figura 5.15.

Figura 5.15 La query che individua il segno zodiacale, basata sulla seconda variante della funzione CASE.

La lunghezza di questa query le dà un aspetto formidabile, ma in realtà è molto lineare: la funzione CASE verifica per dodici volte se il numero d’ordine della data di nascita, ottenuto con la funzione DAYOFYEAR , è compreso entro uno specifico intervallo e

ogni volta che trova una corrispondenza inserisce il nome del segno nella colonna alias Segno zodiacale. C’è soltanto un piccolo dettaglio da considerare: la corrispondenza fra il numero d’ordine di una data e un segno zodiacale viene verificata con la clausola BETWEEN…AND , perché per ogni segno il numero del primo giorno è maggiore di quello dell’ultimo. Nel caso, però, del Capricorno, che sta a cavallo fra dicembre e gennaio, il numero d’ordine del primo giorno (22 dicembre) è 357 e quello dell’ultimo giorno (20 gennaio) è 20. Per le date che ricadono nell’intervallo del Capricorno, quindi, l’espressione WHEN DAYOFYEAR(DataNascita) BETWEEN 357 AND 20 THEN 'Capricorno'

non darebbe un risultato corretto. Abbiamo volto a nostro vantaggio questa difficoltà utilizzando la clausola ELSE: qualunque data che non trova riscontro in uno degli undici confronti della prima parte di CASE è per definizione da attribuirsi al Capricorno, quindi la clausola ELSE restituisce direttamente Capricorno senza ricorrere a BETWEEN…AND . La versione per SQL Server della query che stiamo commentando è questa: SELECT CASE WHEN DATEPART(DAYOFYEAR, DataNascita) BETWEEN 21 AND 49 THEN 'Acquario' WHEN DATEPART(DAYOFYEAR, DataNascita) BETWEEN 50 AND 79 THEN 'Pesci' WHEN DATEPART(DAYOFYEAR, DataNascita) BETWEEN 80 AND 110 THEN 'Ariete' WHEN DATEPART(DAYOFYEAR, DataNascita) BETWEEN 111 AND 140 THEN 'Toro' WHEN DATEPART(DAYOFYEAR, DataNascita) BETWEEN 141 AND 172 THEN 'Gemelli' WHEN DATEPART(DAYOFYEAR, DataNascita) BETWEEN 173 AND 203 THEN 'Cancro' WHEN DATEPART(DAYOFYEAR, DataNascita) BETWEEN 204 AND 235 THEN 'Leone' WHEN DATEPART(DAYOFYEAR, DataNascita) BETWEEN 236 AND 264 THEN 'Vergine' WHEN DATEPART(DAYOFYEAR, DataNascita) BETWEEN 265 AND 296 THEN 'Bilancia' WHEN DATEPART(DAYOFYEAR, DataNascita) BETWEEN 297 AND 326 THEN 'Scorpione' WHEN DATEPART(DAYOFYEAR, DataNascita) BETWEEN 327 AND 355 THEN 'Sagittario' ELSE 'Capricorno' END AS 'Segno zodiacale', Nome, Cognome, DataNascita AS 'Data di nascita' FROM Indirizzi ORDER BY 'Segno zodiacale'

NOT A I puristi dello Structured Query Language (che spesso hanno atteggiamenti di tipo fondamentalista) avversano la funzione CASE, perché introduce una componente procedurale in un linguaggio che si caratterizza come non procedurale.

La sintassi delle varianti di CASE che abbiamo illustrato è quella di MySQL e di SQL Server. La funzione CASE in entrambe le sue varianti può essere utilizzata anche in un enunciato basato sul comando UPDATE, per eseguire un aggiornamento automatico dei valori di una colonna esistente. Come si ricorderà, la clausola SET di UPDATE ha questa struttura: SET nome_campo = espressione

dove espressione può essere un’intera clausola CASE. Se volessimo, quindi, inserire automaticamente il segno zodiacale di tutte le persone in una nuova colonna chiamata Segno della tabella Indirizzi, non dovremmo fare altro che creare la nuova colonna con questo enunciato: ALTER TABLE Indirizzi ADD Segno VARCHAR(15)

e poi eseguire questa query di aggiornamento: UPDATE Indirizzi SET Segno = CASE WHEN DAYOFYEAR(DataNascita) BETWEEN 21 AND 49 THEN 'Acquario' WHEN DAYOFYEAR(DataNascita) BETWEEN 50 AND 79 THEN 'Pesci' WHEN DAYOFYEAR(DataNascita) BETWEEN 80 AND 110 THEN 'Ariete' WHEN DAYOFYEAR(DataNascita) BETWEEN 111 AND 140 THEN 'Toro' WHEN DAYOFYEAR(DataNascita) BETWEEN 141 AND 172 THEN 'Gemelli' WHEN DAYOFYEAR(DataNascita) BETWEEN 173 AND 203 THEN 'Cancro' WHEN DAYOFYEAR(DataNascita) BETWEEN 204 AND 235 THEN 'Leone' WHEN DAYOFYEAR(DataNascita) BETWEEN 236 AND 264 THEN 'Vergine' WHEN DAYOFYEAR(DataNascita) BETWEEN 265 AND 296 THEN 'Bilancia' WHEN DAYOFYEAR(DataNascita) BETWEEN 297 AND 326 THEN 'Scorpione' WHEN DAYOFYEAR(DataNascita) BETWEEN 327 AND 355 THEN 'Sagittario' ELSE "Capricorno" END

(Ricordiamo che i rientri delle righe sono facoltativi e non hanno alcun effetto sull’esecuzione dei comandi SQL: qui li utilizziamo per distinguere meglio i vari elementi di enunciati complessi.) La stessa query in SQL Server si presenta in questo modo: UPDATE Indirizzi SET Segno = CASE WHEN DATEPART(DAYOFYEAR, DataNascita) BETWEEN 21 AND 49 THEN 'Acquario' WHEN DATEPART(DAYOFYEAR, DataNascita) BETWEEN 50 AND 79 THEN 'Pesci' WHEN DATEPART(DAYOFYEAR, DataNascita) BETWEEN 80 AND 110 THEN 'Ariete' WHEN DATEPART(DAYOFYEAR, DataNascita) BETWEEN 111 AND 140 THEN 'Toro'

WHEN DATEPART(DAYOFYEAR, DataNascita) BETWEEN 141 AND 172 THEN 'Gemelli' WHEN DATEPART(DAYOFYEAR, DataNascita) BETWEEN 173 AND 203 THEN 'Cancro' WHEN DATEPART(DAYOFYEAR, DataNascita) BETWEEN 204 AND 235 THEN 'Leone' WHEN DATEPART(DAYOFYEAR, DataNascita) BETWEEN 236 AND 264 THEN 'Vergine' WHEN DATEPART(DAYOFYEAR, DataNascita) BETWEEN 265 AND 296 THEN 'Bilancia' WHEN DATEPART(DAYOFYEAR, DataNascita) BETWEEN 297 AND 326 THEN 'Scorpione' WHEN DATEPART(DAYOFYEAR, DataNascita) BETWEEN 327 AND 355 THEN 'Sagittario' ELSE 'Capricorno' END

Dopo aver eseguito i due comandi, possiamo controllare i risultati dell’aggiornamento con una query come questa: SELECT Nome, Cognome, DataNascita AS "Data di nascita", Segno FROM Indirizzi

la quale dovrebbe generare un elenco come quello che è possibile vedere nella Figura 5.16.

Figura 5.16 L’enunciato UPDATE, costruito su una funzione CASE, ha aggiornato selettivamente i valori della colonna Segno.

A stretto rigore, l’operazione che abbiamo appena descritto (creare un campo e riempirlo automaticamente di dati derivati mediante una funzione da un altro campo) potrebbe essere considerata inutile e impropria:

inutile, perché le informazioni sul segno zodiacale si possono ricavare in qualunque momento dal campo DataNascita; impropria, perché il principio generale che regola la creazione delle tabelle di un database SQL stabilisce che ciascuna colonna deve contenere un dato indipendente da tutti gli altri e non derivabile da altre colonne nella stessa tabella. È lo stesso principio che orienta a non utilizzare campi calcolati. In realtà, le informazioni sulle quali si basa l’algoritmo di calcolo del segno zodiacale (quelle della Tabella 5.1) non sono elementari come per esempio quelle che servono per calcolare l’età attuale di una persona a partire dalla sua data di nascita, quindi, qualora facesse comodo avere già predisposto nella tabella il dato sul segno zodiacale, la query di aggiornamento che abbiamo descritto potrebbe rivelarsi utile e opportuna. Possiamo eliminare agevolmente la colonna Segno dalla tabella Indirizzi ricorrendo alla clausola DROP del comando ALTER TABLE: ALTER TABLE Indirizzi DROP Segno

Utilizzare insieme INSERT e SELECT In determinati casi può fare comodo travasare dati da una tabella a un’altra. Questa operazione si può eseguire combinando insieme in un solo enunciato i comandi INSERT e , in una modalità che abbiamo già visto nel Capitolo 3.

SELECT

Facciamo una prova un po’ più complessa, predisponendo in MySQL una tabella che chiameremo Finale, destinataria del travaso, con questo enunciato: CREATE TABLE Finale IDPersona INT NOT NULL PRIMARY KEY AUTO_INCREMENT, Nome VARCHAR(25), Cognome VARCHAR(25), Città VARCHAR(30), Provincia CHAR(2))

La struttura della nuova tabella dovrebbe presentarsi in questo modo, eseguendo il comando DESCRIBE Finale in MySQL: Field

Type

Null

Key PRI

Default

IDPersona

INT(11)

[NULL]

Nome

VARCHAR(25)

YES

[NULL]

Cognome

VARCHAR(25)

YES

[NULL]

Città

VARCHAR(30)

YES

[NULL]

Provincia

CHAR(2)

YES

[NULL]

Ext ra AUTO_INCREMENT

Proviamo ora a travasare dalla tabella Indirizzi dieci record nella tabella Finale, utilizzando questo enunciato: INSERT INTO Finale(Nome, Cognome, Città, Provincia) SELECT Indirizzi.Nome, Indirizzi.Cognome, Indirizzi.Città, Indirizzi.Prov FROM Indirizzi WHERE Indirizzi.Indirizzi_id 1 ORDER BY t.Titolo

La prima parte della query (clausole SELECT e FROM ) compone i record associando correttamente le colonne dalle tre tabelle; nella seconda parte i record vengono raggruppati mediante la clausola GROUP BY, nella quale la clausola HAVING fa considerare soltanto le righe che danno un valore maggiore di uno quando vengono conteggiate con la funzione di aggregazione COUNT.

Dal momento che la query utilizza GROUP BY, per le ragioni che abbiamo illustrato nel paragrafo “La clausola HAVING” del Capitolo 5 non possiamo elencare nella clausola SELECT altre colonne oltre a Titolo (e neppure avrebbe senso farlo, in questo caso).

Le sottoquery In determinate situazioni può essere necessario eseguire una query per ricavare risultati intermedi, da elaborare successivamente con un’altra query. Lo standard SQL consente di eseguire operazioni di questo genere con un solo enunciato, composto da una query principale e un numero illimitato di query subordinate o sottoquery. Per gli esempi di sottoquery utilizzeremo tre tabelle chiamate Ordini, Clienti e Venditori, che hanno le strutture e i contenuti che sono elencati nelle Tabelle 6.7-6.9. Tabella 6.7 Struttura e contenuti della tabella Ordini dell’esempio. Onum

Import o

Cdat a

Cnum

Vnum

301

18.69

2016-03-07

C08

V07

302

1900.10

2016-03-07

C07

V04

303

767.19

2016-03-07

C01

V01

305

5160.45

2016-03-07

C03

V02

306

1098.16

2016-03-07

C08

V07

307

75.75

2016-04-07

C04

V02

308

4723.10

2016-05-07

C06

V01

309

1713.23

2016-04-07

C02

V03

310

1309.95

2016-06-07

C04

V02

311

9891.88

2016-06-07

C06

V01

Cnum

Tabella 6.8 Struttura e contenuti della tabella Clienti dell’esempio. Cnome Cit t à Classe

Vnum

C01

Heredia

Torino

100

V01

C02

Gennari

Roma

200

V03

C03

Lucchini

Genova

200

V02

C04

Gastoni

Verona

300

V02

C06

Carletti

Torino

NULL

V01

C07

Padovani

Roma

100

V04

C08

Camilli

Genova

300

V07

Tabella 6.9 Struttura e contenuti della tabella Venditori dell’esempio. Vnum

Vnome

Cit t à

Perc

V01

Parini

Torino

0,12

V02

Savoldi

Genova

0,13

V03

Astolfi

Bologna

0,10

V04

Masseroni

Torino

0,11

V07

Russo

Milano

0,15

Iniziamo con un esempio semplice: vogliamo elencare i contratti del venditore Masseroni, ma non ricordiamo il suo codice (Vnum) che è chiave primaria nella tabella Venditori e chiave esterna nelle tabelle Ordini e Clienti. Eseguiamo questa query: SELECT * FROM Ordini WHERE Vnum = (SELECT Vnum FROM Venditori WHERE Vnome='Masseroni')

il cui risultato è riportato nella Figura 6.7.

Figura 6.7 La query ha dato un risultato corretto.

In questa query abbiamo due enunciati SELECT: il primo forma la query principale o esterna e il secondo è la sottoquery, detta anche query interna. L’esecuzione dell’enunciato procede dall’interno verso l’esterno, per cui viene prima eseguita la sottoquery: SELECT Vnum FROM Venditori WHERE Vnome='Masseroni')

che estrae il valore V04, il codice Vnum del venditore Masseroni. Questo valore viene fornito alla query principale, facendole assumere la forma: SELECT * FROM Ordini WHERE Vnum='V04'

Così trasformata, la query può estrarre correttamente la riga: 302 1900.10 2016/03/07 C07 V04

l’unica riga della tabella Ordini che trova corrispondenza con il criterio espresso nella clausola WHERE della query esterna. Se formulassimo la query composta in questo modo:

SELECT * FROM Ordini WHERE Vnum = (SELECT Vnum FROM Venditori WHERE Città="Torino")

la sottoquery estrarrebbe due valori (V01 e V04) e la query esterna non potrebbe essere eseguita, in quanto sintatticamente scorretta, perché assumerebbe la forma: SELECT * FROM Ordini WHERE Vnum= "V01" "V04"

In MySQL non si ha alcuna segnalazione di errore, semplicemente la query non restituisce alcun risultato, mentre in SQL Server si riceve questa puntuale segnalazione di errore: Messaggio 512, livello 16, stato 1, riga 1 La sottoquery ha restituito più di un valore. Ciò non è consentito per le sottoquery che seguono i caratteri =, !=, = o utilizzate come espressione.

Come lascia capire il commento esplicativo della segnalazione di errore emessa da SQL Server, bisogna evitare di predisporre nella clausola WHERE della query esterna un operatore di confronto, perché questo può trattare un solo dato e non una serie di valori. Nel paragrafo “Il predicato IN” del Capitolo 5 abbiamo osservato che una query costruita in questo modo: SELECT Nome, Cognome, Provincia FROM Indirizzi WHERE Provincia IN ('PC', 'MI', 'PV') ORDER BY Provincia produce lo stesso risultato di quest’altra: SELECT Nome, Cognome, Provincia FROM Indirizzi WHERE Provincia='PC' OR Provincia='MI' OR Provincia='PV' ORDER BY Provincia

Ciò è dovuto al fatto che il predicato IN è un operatore logico col quale si presenta (inserendoli fra parentesi tonde) un elenco di valori che la clausola WHERE può considerare validi (cioè hanno valore logico TRUE). Tenendo conto di questa funzionalità di IN, possiamo riformulare in questo modo l’enunciato che prima ha dato errore: SELECT * FROM Ordini WHERE Vnum IN (SELECT Vnum FROM Venditori WHERE Città='Torino')

sostituendo, cioè, nella clausola WHERE della query esterna, il segno di uguale con il predicato IN. La query esterna a questo punto non ha difficoltà a gestire l’elenco di valori che riceve dalla sottoquery e produce il risultato che possiamo vedere nella Figura 6.8.

Figura 6.8 La query principale associata a una sottoquery mediante IN genera un risultato corretto.

In termini generali, quando non si può essere sicuri che una sottoquery generi un solo valore, si può mettere il suo risultato a disposizione della clausola WHERE della query esterna mediante l’operatore IN. Potremmo riformulare in questo modo la query del primo esempio: SELECT * FROM Ordini WHERE Vnum IN (SELECT Vnum FROM Venditori WHERE Vnome='Masseroni')

ottenendo lo stesso risultato mostrato nella Figura 6.7, perché l’elenco di valori forniti alla clausola WHERE mediante IN può contenere anche un solo valore. Nel paragrafo “La clausola HAVING” del Capitolo 5 abbiamo visto che HAVING equivale a WHERE e serve per definire criteri di scelta fra un insieme di valori ottenuti per raggruppamento, cioè mediante la clausola GROUP BY. Vediamo come si può sfruttare la funzionalità di HAVING in una query composta. Nel nostro database i clienti sono classificati con un valore Classe che può assumere un valore compreso fra 100 e 300. Vogliamo contare i clienti che hanno una Classe superiore alla media dei clienti di Genova. Prepariamo ed eseguiamo questa query composta: SELECT Classe, Count(Cnum) AS ConteggioClienti

FROM Clienti GROUP BY Classe HAVING Classe > (SELECT AVG(Classe) FROM Clienti WHERE Città = 'Genova');

Il risultato è riportato nella Figura 6.9: vi sono due clienti con una Classe superiore alla media dei clienti di Genova.

Figura 6.9 Una query composta che utilizza HAVING.

Sottoquery correlate Una sottoquery viene detta correlata quando fa riferimento alla tabella richiamata dalla clausola FROM della query esterna e produce un valore ogni volta che la query esterna prende in esame una riga. Le sottoquery correlate sono uno dei concetti più sottili dello Structured Query Language e possono essere molto potenti, a condizione di capire bene come si comportano e che cosa producono. Vogliamo trovare tutti i clienti che hanno emesso ordini il 7 marzo 2016: possiamo ottenere questo risultato in vari modi; proviamo a risolverlo con questa query composta che utilizza una sottoquery correlata: SELECT * FROM Clienti esterna WHERE '2016/03/07' IN (SELECT Odata FROM Ordini interna WHERE esterna.Cnum = interna.Cnum);

Il risultato è riportato nella Figura 6.10.

Figura 6.10 La query composta ha eseguito più volte la sottoquery correlata.

Per rendere più chiaro il funzionamento della query che stiamo commentando, abbiano utilizzato gli alias “esterna” e “interna” rispettivamente per le tabelle Clienti (sulla quale lavora la query principale o esterna) e Ordini (sulla quale si esegue la query correlata o interna). Siccome il valore della colonna Cnum della query esterna varia, la query interna deve essere eseguita separatamente per ciascuna riga della query esterna. La riga della query esterna in base alla quale viene eseguita la query interna si chiama convenzionalmente riga candidata. Seguiamo passo per passo il procedimento. Client i Cnum

Cnome

Cit t à

Classe

Vnum

C01

Heredia

Torino

100

V01

C02

Gennari

Roma

200

V03

C03

Lucchini

Genova

200

V02

C04

Gastoni

Verona

300

V02

C06

Carletti

Torino

NULL

V01

C07

Padovani

Roma

100

V04

C08

Camilli

Genova

300

V07

Ordini Onum

Import o

Cdat a

Cnum

Vnum

301

18.69

2016-03-07

C08

V07

302

1900.10

2016-03-07

C07

V04

303

767.19

2016-03-07

C01

V01

305

5160.45

2016-03-07

C03

V02

306

1098.16

2016-03-07

C08

V07

307

75.75

2016-04-07

C04

V02

308

4723.10

2016-05-07

C06

V01

309

1713.23

2016-04-07

C02

V03

310

1309.95

2016-06-07

C04

V02

311

9891.88

2016-06-07

C06

V01

Seleziona la prima riga dalla tabella Clienti. La memorizza come riga candidata nella tabella alias esterna. Cnum C01

Cnome Heredia

Cit t à Torino

Classe 100

Vnum V01

Esegue la sottoquery, che percorre tutta la tabella Ordini per trovare le righe nelle quali la colonna Cnum ha lo stesso valore di esterna.Cnum. Trova la riga con Onum 303, che ha C01 nella colonna Cnum. Onum 303

Import o 767.19

Odat a 2016/03/10

Cnum C01

VNum V01

Accantona il valore della colonna Odata da ciascuna riga di Ordini che soddisfa la condizione esterna.Cnum = interna.Cnum e crea un insieme di valori Odata. Esamina questo insieme per vedere se ce ne sono col valore 7 marzo 2016. Da quelli che trova (ne trova uno subito) ricava le informazioni per selezionare la riga corrispondente e inviarla come output della query principale. Ripete l’intero ciclo dalla riga successiva di Clienti (Gennari) e continua fino a quando ha controllato tutte le righe di Clienti. Come si vede, con una sottoquery correlata si può eseguire un lavoro notevolmente complesso. Non volendo ricorrere alla sottoquery, si può ottenere lo stesso risultato con un join (cioè con la clausola WHERE), in questo modo: SELECT primo.Cnum, primo.Cnome FROM Clienti primo, Ordini secondo WHERE primo.Cnum=secondo.Cnum AND secondo.Odata = '2016/03/07'

Figura 6.11 La query senza sottoquery come viene eseguita in MySQL.

Come si può vedere dalla Figura 6.11, questa query produce una riga in più della precedente (la riga di Camilli, Cnum C08, compare due volte), perché alla data del 7 marzo Camilli ha due ordini. Specificando nella query il qualificatore DISTINCT, in questo modo: SELECT DISTINCT primo.Cnum, primo.Cnome FROM Clienti primo, Ordini secondo WHERE primo.Cnum=secondo.Cnum AND secondo.Odata = '2016/03/07'

il risultato diventa: Cnum

Cnome

C01

Heredia

C03

Lucchini

C07

Padovani

C08

Camilli

Vediamo un altro esempio di sottoquery correlata. Desideriamo estrarre dalla tabella Ordini le righe corrispondenti agli ordini che hanno importi superiori alla media di ciascun cliente. Ecco la query: SELECT * FROM Ordini esterna WHERE Importo > (SELECT AVG(importo) FROM Ordini interna WHERE interna.Cnum=esterna.Cnum);

Il risultato è quello che si può vedere nella Figura 6.12.

Figura 6.12 La query ha estratto tre ordini.

Quasi tutte le righe della tabella Ordini sono riferite a un solo cliente, quindi vengono scartate dalla query perché la media dell’importo coincide con l’importo stesso e non viene quindi soddisfatta la condizione WHERE. Le medie degli importi per i tre clienti che hanno due ordini sono rispettivamente: C04 692,85 C06 7307,44 C08 558,42 quindi la query ha derivato correttamente nuove informazioni dai dati esistenti. Si noti che questo risultato non è ottenibile con un join, cioè ricorrendo alla sola clausola WHERE, senza utilizzare una sottoquery, perché non è consentito confrontare valori singoli con valori aggregati. Ecco un altro problema che si presta molto bene a essere risolto con una sottoquery correlata: desideriamo conoscere il totale degli importi della tabella Ordini, raggruppati per data, eliminando tutte le date nelle quali la somma non supera almeno di 2000,00 l’importo massimo di ciascun gruppo di date. Questa è la query: SELECT Odata, SUM(Importo) AS TotaleImporti FROM Ordini esterna GROUP BY Odata HAVING SUM(Importo) > (SELECT 2000.00 + MAX(Importo) FROM Ordini interna WHERE esterna.Odata = interna.Odata);

La query produce il seguente risultato: Odat a 2016-03-07

Tot aleImport i 8944.59

Gli ordini sono raggruppabili su quattro date, ma solo un gruppo soddisfa la condizione, quello ottenuto aggregando in base alla data del 7 marzo 2016: 301

18.69

2016-03-07

C08

V07

302

1900.10

2016-03-07

C07

V04

303

767.19

2016-03-07

C01

V01

305

5160.45

2016-03-07

C03

V02

306

1098.16

2016-03-07

C08

V07

Infatti, sommando 2000.00 al valore massimo di questo gruppo (5160.45) si ottiene 7160.45, che è inferiore alla somma dei cinque valori che formano il gruppo. Una sottoquery correlata con la query principale tramite la clausola HAVING viene valutata per ciascun gruppo della query esterna, non per ciascuna riga. Quando la query esterna si basa su GROUP BY e HAVING , la sottoquery correlata deve utilizzare una clausola e non può contenere clausole GROUP BY o HAVING .

WHERE

L’operatore logico EXISTS In questo capitolo e nel precedente abbiamo utilizzato più volte gli operatori logici comuni a tutti i linguaggi di programmazione (AND , OR e NOT) e altri specifici dello Structured Query Language, quali BETWEEN, LIKE e IN. La loro funzionalità è simile a quella delle funzioni, perché restituiscono un valore quando sono associati con uno o più argomenti. Il valore che restituiscono è detto booleano o logico e può essere soltanto TRUE (vero) o FALSE (falso). In questo e nel prossimo paragrafo completiamo la rassegna degli operatori logici disponibili in SQL, mostrando alcuni esempi di utilizzo di EXISTS , ANY, SOME e ALL . L’operatore EXISTS si applica a una sottoquery e restituisce TRUE se la sottoquery genera almeno una riga. Ecco un semplice esempio: prima di lavorare sulla tabella Clienti vogliamo verificare se c’è qualche cliente che risiede a Genova. Possiamo trovare la risposta con una query composta come questa: SELECT Cnum, Cnome, Città FROM Clienti WHERE EXISTS (SELECT * FROM Clienti WHERE Città='Genova');

Il risultato della query è riportato nella Figura 6.13.

Figura 6.13 È confermato che nella tabella Clienti esistono clienti che risiedono a Genova.

Verrebbe fatto di chiedersi perché mai si dovrebbe usare una query come questa, quando la verifica sarebbe più agevole e immediata con SELECT Cnum, Cnome, Città FROM Clienti WHERE Città='Genova';

che non utilizza una sottoquery e produrrebbe questo utile e sintetico risultato: Cnum

Cnome

Cit t à

C03

Lucchini

Genova

C08

Cavilli

Genova

Niente da obiettare: è vero, si può ottenere un risultato migliore e più sintetico, in casi come questo, evitando di utilizzare l’operatore EXISTS . Questo operatore, insieme con altri tre ALL , ANY e SOME), si utilizza per qualificare le sottoquery, ma ha senso servirsene soltanto per lavorare su sottoquery correlate. L’operatore EXISTS riferito a una sottoquery produce un solo risultato fra due possibili: vero o falso. Se la sottoquery estrae qualche dato, una riga o centomila, EXISTS invia il suo risultato (che è TRUE, vero, in questo caso) alla query esterna. Se la sottoquery non estrae dati, EXISTS lo fa sapere alla query esterna inviandole il valore logico FALSE. Nell’esempio elementare che stiamo commentando, EXISTS segnala TRUE alla query esterna, avendo verificato che la sottoquery produce un certo numero di righe. In base al segnale ricevuto, viene eseguita la query esterna

SELECT Cnum, Cnome, Città FROM Clienti

che genera le sette righe riportate nella Figura 6.13. Quando si lavora con una sottoquery correlata, la clausola EXISTS viene valutata separatamente per ciascuna riga della tabella sulla quale si muove la query esterna. In questi casi, EXISTS si dimostra utile, perché può generare una risposta diversa per ciascuna riga esplorata dalla query esterna. Creiamo una query composta con una sottoquery correlata per ottenere questo risultato: sapere quali venditori hanno più di un cliente. SELECT DISTINCT Vnum FROM Clienti esterna WHERE EXISTS (SELECT * FROM Clienti interna WHERE interna.Vnum = esterna.Vnum AND interna.Cnumesterna.Cnum);

Molto sobriamente, la query restituisce questo risultato: Vnum V01 V02

La query interna cerca le righe che corrispondono col valore Vnum (cioè clienti che hanno lo stesso venditore) ma hanno un Cnum diverso (quindi è un altro cliente). Quando la sottoquery trova una riga con queste caratteristiche, EXISTS segnala TRUE alla query esterna, che quindi estrae il valore corrispondente di Vnum. Il qualificatore DISTINCT nella clausola SELECT della query esterna serve per evitare che l’output venga raddoppiato, dato che l’intera query lavora su due istanze (individuate dagli alias esterna e interna) della stessa tabella Clienti. Proviamo a rendere più chiaro il risultato che abbiamo ottenuto, visualizzando anche nome e città dei venditori che sono stati individuati. Ecco la query modificata: SELECT DISTINCT prima.Vnum, Vnome, prima.Città FROM Venditori prima, Clienti seconda WHERE EXISTS (SELECT * FROM Clienti terza WHERE seconda.Vnum = terza.Vnum AND seconda.Cnum terza.Cnum) AND prima.Vnum = seconda.Vnum;

Il risultato della query dovrebbe presentarsi come nella Figura 6.14. La sottoquery è uguale alla precedente, abbiamo soltanto cambiati gli alias perché in questo caso si agisce su due tabelle. La query esterna specifica nella clausola FROM un join fra le tabelle Venditori e Clienti, in modo da poter ricavare i valori di Vnome e di Città dalla tabella Venditori quando la

sottoquery correlata le segnala che ha trovato corrispondenza fra Vnum nei due alias di Clienti, ma contestualmente i valori di Cnum sono diversi negli stessi alias. Il vincolo AND prima.Vnum = seconda.Vnum

che completa la query esterna deve essere soddisfatto perché la condizione WHERE sia valida.

Figura 6.14 La query produce informazioni più complete.

Dal momento che l’operatore EXISTS genera un valore logico TRUE o FALSE, è perfettamente legittimo combinarlo con l’operatore logico NOT, invertendo in questo modo il risultato prodotto da EXISTS . Mentre con le query precedenti abbiamo verificato quali venditori hanno più di un cliente, con questa controlliamo quali venditori non hanno più di un cliente: SELECT DISTINCT Vnum FROM Clienti esterna WHERE NOT EXISTS (SELECT * FROM Clienti interna WHERE interna.Vnum = esterna.Vnum AND interna.Cnumesterna.Cnum);

Come si può immaginare, la query restituisce questo risultato: Vnum V03 V04 V07

Gli operatori logici ANY, SOME e ALL In inglese i termini any e some sono sostanzialmente sinonimi, con differenze determinate occasionalmente dal contesto e dall’uso. Nello standard SQL sono presenti entrambe le parole chiave ANY e SOME utilizzabili nello stesso modo: per indicare un qualunque valore, ma almeno uno, da un possibile elenco. Possiamo considerare ANY (e ) come una variante più elastica di IN, in quanto si può associare ANY con uno qualsiasi

SOME

degli operatori di confronto, cosa che con IN non è consentita. Per esempio, con questa query: SELECT * FROM Venditori WHERE Città = ANY (SELECT Città FROM Clienti);

possiamo sapere quali venditori hanno clienti che risiedono nella loro stessa città, come vediamo dal risultato che si vede nella Figura 6.15.

Figura 6.15 Il risultato di una query con l’operatore ANY.

Quest’altra query: SELECT * FROM Ordini WHERE Importo >= ANY (SELECT Importo FROM Ordini WHERE Odata = '2016/06/07');

estrae tutti gli ordini che hanno un importo maggiore di o uguale ad almeno uno degli ordini del 7 giugno 2016, come si può vedere dalla Figura 6.16.

Figura 6.16 L’operatore ANY può essere associato con qualunque operatore di confronto.

Sostituendo in questa query ANY con SOME si ottiene lo stesso risultato. Concludiamo questa rassegna degli operatori logici con qualche esempio di utilizzo di ALL . Con questa query: SELECT * FROM Clienti WHERE Classe > ALL (SELECT Classe FROM Clienti WHERE Città = 'Roma');

vogliamo sapere quali clienti hanno una classe superiore a quella di tutti i clienti di Roma. Il risultato dovrebbe presentarsi come nella Figura 6.17.

Figura 6.17 Effetto dell’operatore ALL.

Nella tabella Clienti vi sono due clienti di Roma, che hanno Classe 100 e 200. Così la query, correttamente, estrae i record dei clienti il cui valore Classe è maggiore di 200. Normalmente l’operatore ALL si utilizza con operatori di disuguaglianza (> , < e ) perché, come indica il suo nome, stabilisce per la query principale il vincolo che tutti i risultati della sottoquery abbiano una determinata relazione con il criterio di selezione espresso nella clausola WHERE della query principale. La stessa query con l’operatore = al posto di > SELECT * FROM Clienti WHERE Classe = ALL (SELECT Classe FROM Clienti WHERE Città = 'Roma');

non darebbe alcun risultato (e non avrebbe neppure alcun senso). Nel caso specifico, non otterremmo alcun risultato specificando in questo modo la clausola WHERE WHERE Classe < ALL

perché le classi sono 100, 200 e 300 e siccome i due clienti di Roma hanno classe 100 e 200 non ne esistono che abbiano un valore inferiore a 100 o a 200 nel campo Classe. Invece, la query: SELECT * FROM Clienti WHERE Classe < ALL (SELECT Classe FROM Clienti WHERE Città = 'Verona');

produce questo risultato: Cnum C01

Cnome Heredia

Cit t à Torino

Classe 100

Vnum V01

C02

Gennari

Roma

200

V03

C03

Lucchini

Genova

200

V02

C07

Padovani

Roma

100

V04

perché l’unico cliente di Verona ha classe 300. Si osservi che sono stati estratti soltanto quattro record, perché quello del cliente C06 ha NULL nel campo Classe, quindi viene ignorato. La query dalla quale siamo partiti in questo esempio, quella con la clausola WHERE Classe > ALL

può essere riformulata senza l’operatore ALL , utilizzando EXISTS in questo modo: SELECT * FROM Clienti esterna WHERE NOT EXISTS (SELECT * FROM Clienti interna WHERE esterna.classe