Introduzione Sql con Microsoft Access

Introduzione Sql con Microsoft Access
SQL (Structured Query Language) è un linguaggio per l’interrogazione del database che permette anche
di leggere, modificare, creare, eliminare e gestire i dati all’interno del database. Prima di parlare del
linguaggio SQL (che si legge SIQUEL), devo spiegarvi cosa sono le basi di dati e un po' di Access.
Le basi di dati (il Database)
Le basi di dati (in inglese, Database, abbreviato: DB) si può definire come un archivio di dati in cui
vengono inserite delle informazioni.
I DB vengono spesso utilizzati in ufficio, e al suo interno vengono scritte le varie informazioni utili che gli
utenti possono leggere e alcuni (in base ai privilegi di accesso) possono modificare ed eliminare.
Proviamo a fare un piccolo esempio per capire bene cos’è un Database:
In una scuola c’è uno scaffale con due cassetti. Il primo cassetto è contrassegnato con la
scritta Docenti, mentre il secondo riguarda gli Studenti.
All’interno di questi cassetti ci sono dei fascicoli, uno per ogni docente e per ogni studente. All’interno di
questi fascicoli, si trovano le varie informazioni relative al docente/studente (Cognome, Nome, Data di
nascita, Luogo di nascita ecc.).
Il preside però si stanca presto di amministrare da solo questi archivi, allora assume un’assistente che si
occupi di questo e, quando il preside lo richiede, gli porti su un foglio le informazioni contenute in questo
archivio, in base a criteri scelti dal preside stesso.
Riprendiamo le parole scritte in grassetto, e vediamo come cambiano il nome in Microsoft Office
Access (programma per la gestione e la modifica dei Database):
Scaffale --> Database
Cassetti --> Tabelle
Fascicoli --> Record
Informazioni --> Campi
Assistente --> Maschera/Query (capirete più avanti perché le ho messe insieme ma non sono la stessa
cosa!)
Foglio --> Report
Access si trova nel pacchetto di Microsoft Office Professional.
Primo avvio e chiave primaria
Apriamo Access e creiamo un nuovo Database vuoto.
Ci ritroveremo nella Tabella1 del Database.
In alto scegliamo Strumenti tabella --> Campi --> Visualizza --> Visualizzazione struttura.
Ora ci verrà chiesto di salvare la tabella con un nome: inseriamo “prova”.
La tabella verrà salvata con nome prova.
Verrà creato automaticamente un Campo chiamato ID. Questo campo è attualmente la chiave
primaria della tabella (notate il simbolo di chiave vicino al nome del campo).
Per spiegarvi cos’è la chiave primaria vi illustro prima un problema che potrebbe verificarsi con i
Database.
I Database sono utilizzati (da utenti o programmi) per cercare delle informazioni. Se però noi abbiamo
due Record uguali all’interno del nostro Database?
Vi faccio l’esempio con la scuola. Se nella scuola ci sono due studenti con lo stesso nome e cognome,
come si fa a distinguerli tra loro?
Per ovviare a questo problema nei Database esiste un identificatore univoco (ovvero un valore che è
unico per ogni record) che ci permette di distinguere con certezza i vari record.
Questo identificatore univoco è appunto la chiave primaria. La chiave primaria deve essere di
tipo long (Intero lungo) e deve avere un incremento per ogni nuovo record (più precisamente può essere
anche una String (stringa alfanumerica) che secondo dei criteri stabiliti dall’utente resti comunque
univoco per ogni record. In ogni caso conviene usare il tipo contatore (long) che vedremo più avanti).
Ovvero ad ogni record che viene aggiunto la chiave primaria avrà lo stesso valore del record precedente
aumentato di uno.
La chiave primaria deve essere sempre presente all’interno di una tabella.
Tipi di visualizzazione di un tabella
Esistono quattro tipi differenti di visualizzazione di una tabella, questi sono:
-Visualizzazione
-Visualizzazione
-Visualizzazione
-Visualizzazione
foglio dati
tabella pivot (che non vedremo in questa guida)
grafico pivot (che non vedremo in questa guida)
struttura
Il primo ci permette di vedere i dati contenuti nel Database e, se si possiedono le autorizzazioni, di
modificarli ed eliminarli; Il quarto ci fa vedere i vari campi per modificare le varie proprietà del campo.
Relazioni tra tabelle
Creare una relazione tra tabelle significa correlare uno o più campi di una tabella con uno o più campi
dell’altra.
Questo serve per evitare troppe ripetizioni dei dati identici in più record. Ad esempio:
Una scuola ha un database con le informazioni dei vari insegnanti, tra cui il luogo di nascita. All’interno
del database ci sono 2 tabelle: Docenti e Città. Per evitare troppe ripetizioni nella tabella docenti del tipo:
Nome: Cognome: Luogo di nascita: Regione: Codice postale:
Mario Rossi Roma Lazio 100
Anna Casini Roma Lazio 100
Pinco Pallo Milano Lombardia 20100
Come vede facendo così nella tabella docenti andrebbero a ripetersi Lazio e 100 in Regione e Codice
postale.
Per evitare questo possiamo creare una relazione tra la tabella Docenti e la tabella Città. Nella
tabella Città avremo:
Città: Regione: Codice postale:
Roma Lazio 100
Milano Lombardia 20100
(Queste tabelle verranno utilizzate negli esempi sul linguaggio SQL più avanti).
Invece la tabella Docenti apparirà come la precedente ma senza i campi Regione e Codice postale.
Ora come facciamo a mettere in relazione le due tabelle in modo che se nel campo Luogo di nascita della
tabella Docenti c’è ad esempio Roma, il database associ il record automaticamente alla regione Lazio e al
codice postale 100?
Clicchiamo su Relazioni in Strumenti tabella --> Tabella e selezioniamo le tabelle Docenti e Città.
Adesso prendiamo il campo Luogo di nascita della tabella Docenti e trasciniamolo sul campo Città della
tabella Città.
Ci comparirà una finestra che ci permette di personalizzare le relazioni. Non modifichiamo niente e
clicchiamo su Crea.
I due campi verranno uniti da una linea.
Ora verificheremo la relazione con una query.
Le query
Una query ci permette di eseguire delle indagini nelle tabelle, infatti servono a rispondere a precise
domande sui dati contenuti nel database.
Possiamo creare una query con la creazione guidata ma visto che preferisco la creazione in
visualizzazione struttura vi spiegherò quest’ultima.
Clicchiamo su Struttura query nel menu Crea.
Aggiungiamo entrambe le tabelle (Docenti e Città) quelle create in precedenza.
Vedremo anche la relazione che prima abbiamo creato, sempre con la solita linea nera.
In basso selezioniamo i campi: Nome della tabella Docenti; Cognome della tabella Docenti; Luogo di
nascita della tabella Docenti; Città della tabellaCittà; Regione della tabella Città; Codice postale della
tabella Città.
In luogo di nascita impostiamo Ordinamento su Crescente.
Adesso andiamo nella visualizzazione foglio dati della query e vedremo i dati messi in ordine di Luogo di
nascita con la relazione tra le due tabelle.
Compare però due volte il luogo di nascita (in Luogo di nascita e in Città). Andiamo quindi
nella visualizzazione struttura e impostiamo Mostra su Falseper il campo Città.
Tornando in visualizzazione foglio dati noteremo che il campo Città non viene più visualizzato.
Generalità su maschere e report
Non spenderò molto tempo sulle maschere e i report solo qualche informazione generale su cosa sono.
Le maschere sono delle GUI (Graphical User Inteface, Interfacce grafiche) che ci permettono di
visualizzare in modo più ordinato i dati contenuti nel database.
I report sono dei “rapporti” dove le varie informazioni vengono inserite, in base a dei criteri scelti,
all’interno di un unico foglio stampabile.
Maschere e report possono essere creati con MS Access dal menu Crea.
Tipi di dato in SQL
Ora vediamo come vengono chiamati i tipi di dato in SQL.
Text --> è una stringa alfanumerica (composta da numeri e lettere) che accetta fino ad un massimo di
255 caratteri (spazi e simboli compresi), ma questo valore può essere modificando inserendo tra due
parentesi dopo TEXT la dimensione. (in Access: Testo).
Memo --> anche questo contiene stringhe alfanumeriche, ma a differenza del tipo TEXT questo tipo può
contenere anche stringhe di lunghezze molto superiori (in Access: Memo).
Integer/Float/Double/Byte in base alle dimensioni --> Contiene valori numerici (in Access:
Numerico).
DateTime --> Contiene delle date o degli orari o entrambi contemporaneamente (in Access: Data/ora).
Currency --> è sempre un numero intero o decimale ma con simboli davanti ($, €) e vengono
solitamente utilizzati per rappresentare delle valute. Permette però di scrivere anche numero in
percentuale o in notazione scientifica (elementare, vedi Wikipedia). (in Access: Valuta).
AutoIncrement --> è il tipo che viene solitamente dato alla chiave primaria (identificatore univoco dei
campi nel database. Ovvero ID nelle tabelle d'esempio). Ha per default (ovvero, impostato
come standard) un incremento automatico unitario per nuovo record (in Access: Numerazione automatica
o Contatore)
Bit --> tipo booleano che può contenere solo due valori, True e False (vero o falso) (in Access: Sì/No).
Il linguaggio SQL
In SQL le tabelle Docenti e Città possono essere rispettivamente rappresentate nel seguente modo:
Codice:
Docenti (*ID, Nome, Cognome, Luogo di nasciata)
Codice:
Città (*ID, Città, Regione, Codice postale)
Gli asterischi indicano le chiavi primarie.
Come in tutti i linguaggi esistono vari operatori in SQL. Questi operatori non sono, per la maggior parte,
molto diversi da quelli del Visual Basic.
Gli operatori di confronto sono: =, LIKE (esprime somiglianza tra 2
valori), <, >, <=, >=, <>, BETWEEN (Recupera un valore compreso tra 2 valori).
Gli operatori aritmetici sono: +, -, *, /.
L’unico operatore condizionale è WHERE e serve a definire criteri di ricerca mirati.
Gli operatori logici sono: AND, OR.
Esistono poi dei comandi SQL che ci permettono si eseguire varie operazioni.
CREATE – Creazione di una tabella
La sintassi del comando CREATE è la seguente:
Codice:
CREATE TABLE nome_tabella (nome_campo1 tipo_campo1 [NOT] NULL, …)
Ad esempio:
Codice:
CREATE TABLE Docenti (ID AutoIncrement, Nome Text (255), Cognome Text (255), Luogo_di_nascita
Text (255)
Come vedete per non generare un errore ho dovuto scrivere Luogo_di_nascita invece di Luogo di nascita.
Se si vuole lasciare lo spazio è possibile ma bisogna racchiudere tutti i nomi tra le parentesi quadre (è
consigliato comunque evitarlo). Ovvero:
Codice:
CREATE TABLE [Docenti] ([ID] AutoIncrement, [Nome] Text (255), [Cognome] Text (255), [Luogo
di nascita] Text (255)
Alla fine è possibile aggiungere anche un NULL o un NOT NULL, che rispettivamente significano NON
RICHIESTO e OBBLIGATORIO.
ALTER – Modifica strutturale di una tabella
Con modifica strutturale si intende l’aggiunta di un campo.
Il comando ALTER ha la seguente sintassi:
Codice:
ALTER TABLE nome_tabella ADD/MODIFY/DROP COLUMN nome_campo1 tipo_campo1, …
Non farò esempi visto che è molto semplice. Basta sapere che con ADD COLUMN viene aggiunto un
campo, con MODIFY COLUMN viene modificato un campo con un diverso tipo e con DROP COLUMN viene
eliminato un campo (quest’ultimo non necessita del parametro tipo_campo).
DROP – Cancellazione di una tabella
Sintassi:
Codice:
DROP TABLE nome_tabella
INSERT – Inserimento di dati in una tabella
Sintassi:
Codice:
INSERT INTO nome_tabella (nome_campo1, …) VALUES (valore_campo1, …)
Ad esempio:
Codice:
INSERT INTO Docenti (Nome, Cognome, Luogo_di_nascita) VALUES (‘Mario’, ‘Rossi’, ‘Roma’)
Se sono di tipo testo o memo i valori devono essere inseriti tra gli apici (‘’)
SELECT – Interrogazione di una tabella
Con SELECT possiamo creare delle query.
La sintassi base è la seguente:
Codice:
SELECT nome_campo1 …/* FROM nome_tabella
L’asterisco sta per tutti i campi.
Ad esempio:
Codice:
SELECT * FROM Docenti
Questa estrae tutti I campi della tabella docenti.
Altro esempio:
Codice:
SELECT Nome, Cognome FROM Docenti
Possiamo anche inserire dei filtri inserendo degli operatori. Ad esempio:
Codice:
SELECT Nome, Cognome FROM Docenti WHERE ID = 1
Ovvero prende il nome è il cognome dal record della tabella Docenti che ha come ID = 1.
Altro esempio:
Codice:
SELECT Nome FROM Docenti WHERE ID = 1 AND Cognome LIKE ‘R*’
Prende il nome dal della tabella Docenti che ha come ID = 1 e Cognome che comincia con R.
Codice:
SELECT * FROM Docenti WHERE ID BETWEEN 20 AND 40
Prende tutti i dati della tabella Docenti che hanno l’ID compreso tra 20 e 40.
Con UNION possiamo invece unire i risultati di due tabella in un unico risultato. Esempio:
Codice:
SELECT * FROM Docenti UNION SELECT * FROM Città
Con DISTINCT possiamo decidere di estrarre solo una volta quando il dato può comparire più volte
uguale. Ad esempio:
Codice:
SELECT DISTINCT Nome FROM Docenti WHERE Nome = ‘Mario’
UPDATE – Aggiornamento dei dati di una tabella
Sintassi:
Codice:
UPDATE nome_tabella SET nome_campo = ‘valore’ WHERE campo_condizione = ‘valore_condizione
Potete capirlo anche senza esempi.
DELETE – Cancellazione di dati da una tabella
Sintassi:
Codice:
DELETE * FROM nome_tabella WHERE campo_condizione = valore_condizione
JOIN – Relazioni tra più tabelle
Con JOIN possiamo creare le relazioni tra le tabelle. Oltre che con JOIN (o meglio INNER, JOIN) è
possibile farlo anche con SELECT…FROM…WHERE. Ora li analizzeremo entrambi.
Per indicare un campo contenuto in una determinata tabella in SQL si usa nome_tabella.nome_campo.
Codice:
SELECT Docenti.Nome, Docenti.Cognome, Docenti.Luogo_di_nascita, Città.Regione,
Città.Codice_postale FROM Docenti, Città WHERE Docenti.Luogo_di_nascita = Città.Città
Questa è la stessa relazione che avevamo creato con Access. Tradotta in italiano sarebbe:
SELEZIONA il campo Nome dalla tabella Docenti, il campo Cognome dalla tabella Docenti, il campo
Luogo_di_nascita dalla tabella Docenti, il campo Regione dalla tabella Città, il campo Codice_postale dalla
tabella Città DALLE tabelle Docenti e Città DOVE il campo Luogo_di_nascita della tabella Docenti è uguale
al campo Città della tabella Città.
Ora vediamo come fare con INNER JOIN:
Codice:
SELECT Docenti.Nome, Docenti.Cognome, Docenti.Luogo_di_nascita, Città.Regione,
Città.Codice_postale FROM Docenti INNER JOIN Città ON Docenti.Luogo_di_nascita = Città.Città
Funziona praticamente allo stesso modo.
Funzioni di aggregazione
Esistono delle funzioni in SQL che ci permettono di ottenere valori numerici e di effettuare calcoli in
funzione di query specifiche. Ecco una lista delle varie funzioni:
AVG() --> Restituisce la media tra più valori
COUNT() --> Restituisce il numero di record trovati
MAX() --> Restituisce il valore massimo tra più valori
MIN() --> Restituisce il valore minimo tra più valori
SUM() --> Restituisce la somma tra più record dello stesso campo
Sintassi:
Codice:
SELECT FUNC(nome_campo) AS temp FROM nome_tabella
AS temp non è necessario ma è consigliato. temp diventa così l’alias del risultato (vedremo tra poco
cosa è).
Funzioni di gestine delle stringhe
Sono solo due: LEFT() e RIGHT(), e servono per definire delle sottostringhe a partire, rispettivamente,
dalla sinistra e dalla destra di una stringa.
Sintassi:
Codice:
SELECT LEFT/RIGHT(nome_campo, lenght) FROM nome_tabella WHERE …
Ad esempio:
Codice:
SELECT LEFT(Nome, 3) FROM Docenti WHERE ID = 1
Se al campo Nome del record con ID = 1 c’è scritto Mario allora verranno prese solo le prime tre lettere,
ovvero Mar.
Con LEN() possiamo invece calcolare la lunghezza di una stringa. Sintassi:
Codice:
SELECT LEN(nome_campo) FROM nome_tabella WHERE …
Con TOP() invece possiamo estrarre i primi N dati da una tabella. Sintassi:
Codice:
SELECT TOP N nomi_campi FROM nome_tabella
Gli Alias
Già preannunciati, gli Alias sono delle “variabili” che andranno a contenere dei valori appena calcolati.
Sintassi:
Codice:
SELECT OPERAZIONI(parametri) AS nome_alias FROM nome_tabella
GROUP BY e HAVING
GROUP BY è simile ad ORDER BY con la differenza con non ordina i dati in funzione di una campo ma li
raggruppa in funzione del campo specificato. Esempio:
Codice:
SELECT COUNT(*) AS quanti FROM Docenti GROUP BY ID
HAVING è simile a WHERE ma serve ad effettuare operazioni utilizzando come clausole condizionali
funzioni di aggregazione piuttosto che valori definiti staticamente. Esempio (aggiungo un campo Età alla
tabella Docenti):
Codice:
SELECT COUNT(*) AS quanti FROM Docenti HAVING MAX(Età) < 30
Ovvero mette in quanti la quantità di docenti che hanno un’età inferiore ai 30 anni.
Sarebbe equivalente scrivere:
Codice:
SELECT COUNT(*) AS quanti FROM Docenti WHERE Età < 30
Con la differenza che nel primo caso abbiamo usato una funzione di aggregazione (MAX) mentre nel
secondo abbiamo solo applicato una disugualianza (<).