Eprogram informatica V anno Introduzione a SQL Introduzione SQL (Structured Query Language) è un linguaggio non procedurale che opera sui dati memorizzati in una base di dati relazionale, consente di: - inserire, - ricercare, - aggiornare, - cancellare. Modalità utilizzo SQL SQL può essere utilizzato: - - in modo interattivo: quando l’utente digita il comando, questo viene immediatamente eseguito in modo embedded: cioè all’interno di programmi scritti con linguaggi tradizionali (per esempio Visual Basic). I risultati delle istruzioni in questo caso non sono immediatamente visibili all’utente, ma vengono elaborati dal programma “ospite”. Creazione di tabelle L’istruzione CREATE TABLE permette di creare una tabella in cui registrare dati: nome-colonna1 e nome-colonnaN sono i nomi delle colonne della tabella. Accanto a OGNI nome di colonna deve essere specificato il tipo di dati (in questo caso NOT NULL) e, se necessario, altre clausole. Clausole Come detto accanto al nome di ogni colonna possono essere specificate alcune clausole. Le principali clausole che possono essere definite sono: - PRIMARY KEY definisce il campo come chiave primaria (univoca); FOREIGN KEY definisce il campo come chiave straniera (esterna); UNIQUE definisce che il campo NON può avere valori duplicati; NOT NULL indica che il campo DEVE avere sempre il valore. I dati in SQL In SQL possono essere definiti: - dati numerici (interi e reali), - dati alfanumerici (stringhe e date) - oggetti. Esistono poi differenti tipi standard per l’utilizzo. I dati in SQL I dati in SQL possono essere: - - SMALLINT numero compreso tra -32768 e 32767; INTEGER numero compreso tra -2147483648 e 2147483647; DECIMAL (p,q) numero con un massimo di p cifre di cui q cifre dopo il punto decimale; FLOAT (p) numero compreso tra -5.4,10E79 e 7.2,10E75; CHAR (n) stringa di massimo n caratteri (0 < n < 255); ogni valore della colonna richiede n caratteri; VARCHAR (n) stringa di massimo n caratteri (0 < n < 2048); ogni valore della colonna richiede uno spazio pari al numero di caratteri effettivi del campo; INTERVAL periodo di tempo (anni, mesi, giorni, ore, minuti, secondi e frazioni di secondo); DATE e TIME preciso istante temporale. Ricerca con SQL, gli indici Per reperire uno o più record in una tabella, SQL normalmente effettua un accesso sequenziale alla tabella leggendo una riga per volta. Se si cerca una sola riga e la tabella è composta da numerose righe, questo metodo richiede ovviamente molto tempo ed è poco efficace. Per velocizzare la ricerca è necessario effettuare un accesso diretto ai dati, ma per poterlo fare bisogna definire un indice sul campo di ricerca. CREATE INDEX L’istruzione CREATE INDEX serve per creare un indice su una tabella esistente. La sua sintassi è la seguente: Durante l’elaborazione, è SQL a determinare se utilizzare o meno uno degli indici presenti. Quando si aggiornano, si inseriscono o si cancellano righe di una tabella, SQL deve rivedere gli indici definiti sulla stessa. Ciò significa che, se da un lato le operazioni di ricerca sono più veloci, dall’altro il tempo di elaborazione delle istru- zioni di modifica dei dati è maggiore. Indici, pro e contro Come detto, l’uso degli indici è molto importante per rendere più veloci le operazioni di interrogazione della base di dati. Quando si aggiorna una tabella, però, SQL DEVE rivedere gli indici definiti sulla stessa. Ciò significa che il tempo di elaborazione delle istruzioni di modifica dei dati è maggiore. UNIQUE UNIQUE specifica che NON si desiderano valori duplicati per l’indice. Quando è omessa, i duplicati sono ammessi. È possibile definire più indici sulla stessa tabella. Se si prevede di effettuare frequentemente ricerche utilizzando due o più campi alla volta, è possibile creare un indice per quella combinazione di campi (indice multi-colonna) inserendo i nomi dei campi separati dalla virgola all’interno della clausola ON: Utilizzare SQL con Access, passo 1 Per scrivere le query SQL possiamo utilizzare Access nel seguente modo: 1. nella finestra principale di Microsoft Access selezionare da menu Crea e scegliere Struttura query: Utilizzare SQL con Access, passo 2 2. dalla finestra Mostra tabella non inserire alcuna tabella, ma Chiudi: Utilizzare SQL con Access, passo 3 3. dal menu scegliere Home e poi SQL: Utilizzare SQL con Access, passo 4 4. si apre una finestra dove è possibile scrivere il comando SQL, in seguito è possibile salvare la query ed eseguirla come una normale query Access. Modificare lo schema, ALTER TABLE Lo schema dei dati viene modificato sia eliminando tabelle o indici, sia modificando la struttura stessa della tabella. L’istruzione ALTER TABLE permette all’utente di modificare la struttura di una tabella. La clausola successiva indica il tipo di modifica che si vuole effettuare sulla tabella (aggiunta, eliminazione e modifica.) Per inserire in un secondo momento una colonna in una tabella, si usa il comando: ALTER TABLE… ADD, inserire una colonna Per inserire in un secondo momento una colonna in una tabella, si usa il comando: ALTER TABLE… DROP, cancellare una colonna Per cancellare una colonna da una tabella già esistente: ALTER TABLE… CHANGE, cambio tipo colonna Per modificare una colonna cambiandone il tipo: DROP TABLE, cancellare una tabella Per cancellare una tabella: Modifica dati È possibile anche effettuare operazioni sui dati come: - Inserimento - Modifica - Cancellazione INSERT INTO… VALUES, inserire nuove righe È possibile inserire nuove righe in una tabella mediante il comando: UPDATE… SET… WHERE, modifica valori I valori contenuti in una tabella si possono modificare mediante l’istruzione UPDATE: Quando è specificata la clausola WHERE la modifica interessa SOLO quelle righe della tabella che soddisfano il criterio di selezione. In sua assenza, la modifica avrà effetto su TUTTE le righe della tabella. DELETE… FROM… WHERE, elimina righe È possibile eliminare le righe di una tabella con l’istruzione DELETE: Anche in questo caso la presenza della clausola WHERE permette di eliminare SOLO i record che rispettano certe condizioni. L’assenza della clausola WHERE provoca lo svuotamento di tutta la tabella. La tabella NON verrà cancellata, ma risulterà vuota. SELECT L’istruzione più importante e più frequentemente utilizzata in SQL è SELECT, usata per la ricerca dei dati contenuti in una tabella. Il risultato restituito da questa istruzione è sempre una tabella che successivamente può essere ancora elaborata. Il formato completo dell’istruzione SELECT è: Clausola WHERE La clausola WHERE definisce la condizione a cui devono sottostare le righe da ricercare. Una condizione può essere: - di confronto semplice (=, <, >, >=, <=, <>) - di confronto composto quando sono presenti gli operatori AND, OR, NOT. Predicato della clausola WHERE Il predicato della clausola WHERE può essere anche: Altri usi istruzione SELECT Nelle applicazioni può capitare di dover fare calcoli al volo sui campi dei record estratti come per esempio visualizzare un prezzo convertito in vari cambi (euro, dollari, sterline...) oppure calcolare una percentuale o una frazione. Anche in questo caso l’istruzione SELECT mette a disposizione dell’utente una sintassi molto semplice. Vediamo un esempio: SELECT… INTO, creare una tabella Come abbiamo già detto, il risultato di una query è una tabella di tipo temporaneo, che scompare al termine dell’esecuzione. Se si desidera conservarla, è possibile creare una tabella permanente a partire dai risultati della query usando la clausola INTO. Operazione di Join Il JOIN è una clausola del linguaggio SQL che serve a combinare (unire) le righe di due o più tabelle di un database. La congiunzione collega le tabelle che hanno un attributo in comune. Il risultato di questa operazione è una nuova tabella che ha tutte le colonne della prima e della seconda tabella. La sintassi è: N.B.: la seconda sintassi è molto più semplice quando bisogna impostare relazioni tra più di due tabelle. Operazione di Join Il JOIN è una clausola del linguaggio SQL che serve a combinare (unire) le righe di due o più tabelle di un database. La congiunzione collega le tabelle che hanno un attributo in comune. Il risultato di questa operazione è una nuova tabella che ha tutte le colonne della prima e della seconda tabella. La sintassi è: N.B.: la seconda sintassi è molto più semplice quando bisogna impostare relazioni tra più di due tabelle. INNER JOIN, OUTER JOIN Abbiamo visto che quando dobbiamo selezionare dati da due o più tabelle per avere dei risultati completi occorre effettuare delle operazioni di JOIN. - INNER JOIN selezioniamo tutti i valori corrispondenti tra le tabelle, OUTER JOIN avremo come risultato anche quelli che non hanno una corrispondenza. La OUTER JOIN ha come parametri da aggiungere RIGHT e LEFT che ne determinano l’ordinamento: - LEFT JOIN visualizzeremo le righe presenti nella prima tabella (LEFT) che non trovano corrispondenza nella seconda tabella (RIGHT) - viceversa nel caso della RIGHT JOIN. Funzioni predefinite SQL SQL mette a disposizione alcune funzioni predefinite che agiscono sui valori di insieme di alcune colonne di una tabella: - - COUNT conta il numero di valori contenuti in una colonna o il numero di righe di una tabella; MIN determina il valore minimo contenuto in una colonna; MAX determina il valore massimo contenuto in una colonna; SUM determina la somma dei valori contenuti in una colonna; AVG determina la media aritmetica dei valori di una colonna. Ordinamento La clausola ORDER BY ordina i dati visualizzati secondo uno o più campi specificati in ordine crescente o decrescente. L’ordinamento è crescente per default; se lo si vuole decrescente, bisogna specificare DESC accanto agli attributi sui quali si vuole eseguire l’ordinamento. Nell’esempio seguente il risultato della query sarà una tabella con i nomi e i relativi prezzi dei prodotti ordinati in modo crescente in base al nome del prodotto. Raggruppamento La clausola GROUP BY raggruppa le righe in base ai valori uguali delle colonne specificate. Questa opzione produce una riga di risultato per ogni raggruppamento. Clausola HAVING La clausola HAVING è simile alla clausola WHERE nell’istruzione SELECT, ma, mentre la prima agisce sulla singola riga di una tabella, la seconda agisce su gruppi di righe che prima sono stati selezionati con una clausola GROUP BY.