Scarica sql istruzioni principali e più Sintesi del corso in PDF di Programmazione e Tecnologie Web solo su Docsity!
SQL SELECT. Con l'istruzione SELECT di SQL possiamo creare degli script di interrogazione al database, interrogazioni dette query. La sintassi base di una SELECT SQL è la seguente: SELECT * FROM nome_tabella Copia dove * sta per tutti i campi. La query SELECT id, autore FROM autori Copia estrae tutti i campi dalla tabella autori. Al posto di * posso usare i nomi dei campi che mi interessa estrarre. Inseriamo adesso un filtro nella query utilizzando l'operatore WHERE: SELECT autore FROM autori WHERE id = 1 Copia Il risultato sarà J.R.R. Tolkien. Possiamo anche decidere di conoscere l'id di una tabella in funzione di un altro parametro di ricerca. Ad esempio: SELECT id FROM autori WHERE autore = 'J.R.R. Tolkien' Copia Attenzione: effettuando una ricerca in funzione di un dato di tipo numerico non dobbiamo utilizzare gli apici per racchiudere il valore; in SQL i singoli apici delimitano una stringa. Creiamo adesso una serie di query sulla tabella libri che, essendo più ampia, ci permette di giocare un po di più. Estraiamo tutti i titoli dalla tabella libri dove l'id dell'autore è 1 ed il titolo inizia per I utilizzando gli operatori AND per stabilire due condizioni entrambe vere e LIKE per effettuare una ricerca generica: SELECT titolo FROM libri WHERE id_autore = 1 AND titolo LIKE 'I' Copia L'operatore LIKE necessita del sotto-operatore * per identificare tutto il resto della stringa. 'I' vuol dire tutto ciò che inizia per I. LIKE permette di effettuare ricerche su stringhe a partire dall'inizio della stringa, dalla fine della stringa o dalla fine. Rispettivamente potremmo avere: SELECT * FROM libri WHERE titolo LIKE 'Anelli' Copia e/o SELECT * FROM libri WHERE titolo LIKE 'Signore*' Copia impostando semplicemente * come conviene. In altri DBMS il simbolo * per il LIKE viene sostituito da %. Proviamo ad effettuare quattro ricerche in cui il prezzo è: inferiore a 50 euro; superiore a 50 euro; diverso da 50 euro; compreso tra 30 e 60 euro. Avremo rispettivamente: SELECT * FROM libri WHERE prezzo < 50 Copia SELECT * FROM libri WHERE prezzo > 50 Copia SELECT * FROM libri WHERE prezzo <> 50 Copia SELECT * FROM libri WHERE prezzo BETWEEN 30 AND 60 Copia
Esiste poi il modo di unire i risultati di due tabelle in un unico risultato con le query di unione, grazie alla clausola UNION. Vediamo un esempio: SELECT * FROM autori UNION SELECT * FROM libri Copia Per fare altri esempi dobbiamo immaginare un diverso caso di studio. Create la tabella utenti composta dai campi id (AutoIncrement), nome (Text) e cognome (Text). In un simile caso possiamo avere molti nomi o cognomi uguali; se ad esempio avessimo una tabella dove ci sono molti utenti di nome Luca e volessimo estrarre solo una volta il dato Luca in una ricerca, dovremmo utilizzare la clausola DISTINCT come segue: SELECT DISTINCT nome FROM utenti WHERE nome = 'Luca
Nella lezione precedente abbiamo esaminato i costrutti che SQL mette a disposizione per inserire i dati in un database relazionale. In questa e nelle prossime lezioni ci concentreremo sulle istruzioni che ci permettono di estrarre i dati che ci interessano. L’istruzione SQL preposta a tale scopo è SELECT. L’interrogazione è la funzionalità più usata di un database e le clausole di cui dispone l’istruzione SELECT sono numerose e a volte possono dar luogo a combinazioni piuttosto complicate. La sintassi completa è la seguente: SELECT [DISTINCT ] lista_elementi_selezione FROM lista_riferimenti_tabella [ WHERE espressione_condizionale ] [ GROUP BY lista_colonne ] [ HAVING espressione_condizionale ] [ ORDER BY lista_colonne ]
Copy
Come si vede, le uniche clausole obbligatorie sono SELECT e FROM. Quest’ultima, detta clausola di selezione , stabilisce da quale tabella (o da quali) estrarre i dati, mentre la prima è detta clausola di proiezione e stabilisce quali colonne devono essere riportate nel risultato finale. La clausola WHERE definisce invece un filtro sulle righe che verranno analizzate, mentre ORDER BY indica l’ ordinamento da applicare al risultato finale. Se WHERE non viene specificata, non viene applicato alcun filtro sulle righe, che quindi vengono esaminate tutte. Se invece non viene specificato alcun ordinamento, le righe vengono restituite senza un ordine definito, generalmente così come vengono trovate in tabella. Vedremo filtro e ordinamento in dettaglio nella prossima lezione. Nella lezione 11 analizzeremo, invece, le clausole GROUP BY e HAVING, entrambe riguardanti le aggregazioni. Per quanto riguarda selezione e la proiezione, di cui ci occupiamo in questa lezione, vediamo subito un esempio: SELECT Person.surname FROM Person
Copy
Ci sono funzioni per lavorare con le date, con i numeri, con le stringhe di testo o per gestire i valori NULL. Ad esempio, la funzione COALESCE restituisce il primo valore non nullo tra le espressioni indicate: SELECT COALESCE(surname, given_names, 'Senza nome') AS name FROM Person
Copy
Esistono anche funzioni condizionali, come ad esempio CASE, che restituisce un valore in base al soddisfacimento di un certo predicato. Per esempio, la seguente query calcola l’iniziale solo quando (CASE WHEN) il nome della persona è più lungo (LENGTH) di cinque caratteri: SELECT CASE WHEN LENGTH(given_names) > 5 THEN CONCAT(SUBSTRING(given_names, 1 , 1 ), '.') ELSE given_names END AS Abbrev FROM Person
Copy
Si possono concatenare più espressioni CASE WHEN, esattamente come un’espressione else if del C o degli altri linguaggi imperativi. Nella lezione 11 vedremo una particolare classe di funzioni, dette di aggregazione , che possono essere usate per eseguire operazioni quali conteggi, somme, medie.
DISTINCT, eliminare i duplicati
La prima query che abbiamo visto restituirebbe righe duplicate nel caso in cui nella tabella fossero presenti persone con lo stesso cognome. Per evitare ciò occorre specificare l’opzione DISTINCT : SELECT DISTINCT surname FROM Person
Copy
Questa opzione fa sì che nel risultato non ci siano righe uguali tra loro, ed è utile soprattutto quando si lavora con i join. Un join (congiunzione) è una funzione che stabilisce come combinare righe di due tabelle diverse per ottenere una tabella unica. Si distinguono generalmente tre tipi di join, che vediamo di seguito.
Inner join
Questa operazione, che in SQL è implementata dalla clausola INNER JOIN o semplicemente JOIN, combina solo le righe delle due tabelle che soddisfano un certo predicato di confronto, come in un’operazione di intersezione. Si possono effettuare confronti tra date o numeri; ad esempio per avere tutti i libri usciti dopo la data di pubblicazione di ogni articolo: SELECT Book.title, Article.title FROM Book JOIN Article on Article.pub_year > Book.pub_year
Copy
Comunque nella maggior parte dei casi questo predicato è un’uguaglianza tra ID: SELECT surname, given_names, title FROM Person JOIN Author on person.ID = Author.personID JOIN Book on Book.ID = Author.publicationID
Copy
Questa query restituisce i nominativi e i titoli dei libri pubblicati.
Outer join
La query precedente restituirà solo i libri e le persone per cui esiste un collegamento. Quindi non restituirà né le persone che non hanno scritto libri né i libri senza autore. A seconda se vogliamo anche questi avremo rispettivamente un LEFT OUTER o un RIGHT OUTER JOIN. Ad esempio: SELECT surname, given_names, title FROM Person LEFT JOIN Author on person.ID = Author.personID LEFT JOIN Book on Book.ID = Author.publicationID
Copy
Come si vede la parola chiave OUTER è opzionale. In questo caso, per le persone che non hanno pubblicato un libro, la colonna title avrà valore NULL. MySQL non supporta nativamente il FULL OUTER JOIN, che permette di fare un join destro e sinistro simultaneamente, per ottenere, nel nostro caso, oltre agli autori e ai libri anche i libri pubblicati anonimamente e le persone che non hanno scritto libri: SELECT surname, given_names, title FROM Person FULL JOIN Author on person.ID = Author.personID FULL JOIN Book on Book.ID = Author.publicationID
Copy
Cross join
Rappresenta l’operazione sistemistica del prodotto cartesiano. Significa che per ogni riga della prima tabella vengono considerate tante righe quante ne ha la seconda combinandole. Quindi alla fine, date due tabelle rispettivamente di dimensioni N ed M, otterremo un risultato di NxM righe. Si usa questo tipo di join quando si vogliono tutte le combinazioni possibili. In SQL, per fare un cross join basta separare i nomi della tabelle con una virgola: SELECT Person.surname, Publisher.name FROM Person, Publisher
Copy
I cross join vengono usati anche quando si vogliono fare join molto particolari, non ottenibili con le altre clausole di join, specificando le condizioni nella clausola WHERE.
Select
Affianco al questo comando vengono indicate le colonne che si
vogliono visualizzare. L’asterisco (*) seleziona tutte le colonne
indicate nel comando FROM. Il comando DISTINCT posto tra Select e
il nome della colonna toglie tutte le righe doppie della colonna. Il
comando AS cambia il nome di visualizzazione della colonna, si
possono anche inserire delle colonne calcolate scrivendo l’operazione
e dandogli un nome con AS.
From
Indica la tabella o le tabelle in cui deve operare il comando Select.
Where
Indica la condizione logica con cui vengon filtrate le colonne. Tra
parentesi quadre [] si può indicare un parametro da chiedere prima
della selezione. Nel caso vi siano due o più tabelle selezionate le si
può mettere in relazione indicando in un’eguaglianza i campi voluti.
WHERE CodiceNome = Codice
Group by
Raggruppa le righe con lo stesso valore indicate nella clausola.
SELECT Dipartimento, SUM(Stipendio)
FROM Impiegati
GROUP BY Dipartimento;
Having
Da delle condizioni ai gruppi creati con Group by:
SELECT Dipartimento, SUM(Stipendio)
FROM Impiegati
GROUP BY Dipartimento;
HAVING SUM(stipendio)>
Order by
Ordina i record della colonna indicata, se si vuole un ordinamento
decrescente si scrive DESC dopo il nome della colonna.Anzichè il nome
della colonna si può indicare il numero corrispondente nel comando
Select.
ORDER BY stipendio DESC, Cognome;
Tabelle Creazione
Con il comando CREATE TABLE si possono creare delle tabelle e
definire dei campi, questo comando va eseguito nelle Query. Le
istruzioni che seguono definiscono le proprietà dei campi.
CREATE TABLE Impiegati
(ID smallint Primari key,
Cognome char(30) not null,
Città char(20) default ‘Verona’,
Stipendio decimal(9,2),
Reparto char(4) references RepartoMag(codice) );
Primary key
Definisce la chiave primaria.
Not null
Rende il campo obbligatorio.
Default
Definisce il valore predefinito per il campo.
Insert
INSERT INTO impiegati
(ID, Nome, Cognome)
VALUES (20, ;Mario, Rossi);
Update
UPDATE Impiegati
SET Cognome = ‘Rossi’, Nome = ‘Mario’
WHERE ID = 20
L’istruzione SET aggiorna i dati e WHERE indica quali dati modificare.
Delete
DELETE FROM Impiegati
WHERE Nome = ‘Marco’
Cancella i record con il nome ‘Marco’.
Join
LEFT JOIN…ON seleziona tutti i record della tabella di sinistra, in
questo caso la tabella Impiegati.
SELECT Impiegati.Nome, Dipartimento.Descrizione
FROM Impiegati LEFT JOIN Dipartimento
ON Dipartimento.codice = Impiegati.Dipartimento;
RIGHT JOIN…ON seleziona tutti i record della tabella di destra,
Dipartimento.
SELECT Impiegati.Nome, Dipartimento.Descrizione
FROM Impiegati RIGHT JOIN Dipartimento
ON Dipartimento.codice = Impiegati.Dipartimento;
Nel caso si vogliano visualizzare sia tutti i records delle due tabelle
bisogna unire i codici precedenti con il comando UNION.
Funzioni di aggregazione
All’interno del comando Select si possono fare alcuni calcoli di
aggregazione sulle colonne.
COUNT: Conta i record della colonna selezionata.
SELECT COUNT (Nome)
FROM Impiegati;
SUM: Somma tutti i record della colonna, in questo caso quelli con
Nome maggiore di M.
SELECT SUM(Stipendio)
FROM impiegati
WHERE Nome > ‘M’;
AVG: calcola la media.
SELECT AVG(Stipendio)
FROM impiegati, Dipartimento
WHERE Nome > ‘M’ and Dipartimento = codice;
FROM Impiegati
WHERE Reparto = ‘Mag’;
ORDER BY Nome
Condizioni di Ricerca Between
WHERE Stipendio BETWEEN 3000 AND 45000
In
Controlla i valori della colonna e seleziona quelli che appartengono alla
lista.
WHERE Città IN (‘Verona’, ‘Milano’, ‘Genova’)
Like
Controlla il valore di un campo, ( _ ) indica un solo carattere qualsiasi,
mentre ( % ) indica zero o più caratteri. Es. LIKE ‘abd%’ o ‘%abc’ o
‘%abc%’ o ‘_abc’
WHERE Cognome LIKE ‘R%’
Is Null
WHERE Cognome IS (NOT) NULL
Interrogazioni nidificate
Le interrogazioni sono importanti per stabilire delle selezioni con
campi calcolati.
SELECT Nome, Cognome
FROM Impiegati
WHERE Stipendio > (SELECT AVG (Stipendio) FROM impiegati);
INTRODUZIONE AGLI RDBMS E A MYSQL
Uno dei più grandi contributi che i sistemi informatici offrono al genere umano è la memorizzazione di dati in maniera persistente. Quotidianamente, immense quantità di informazioni vengono affidate a tecnologie che ne garantiscono la conservazione duratura ed un recupero efficiente che ne permetta l’analisi. Da anni, questo ruolo viene interpretato molto bene da un prodotto software completo, efficiente ed affidabile: MySQL. Nel seguito chiariremo sin da subito che cos’è esattamente, a cosa serve e come utilizzarlo, illustrandone anche le principali caratteristiche e potenzialità.
Database e DBMS
I concetti centrali in tema di memorizzazione di dati sono due: database e DMBS. Il primo indica un sistema di file finalizzato a memorizzare informazioni a supporto di un qualsivoglia software. La struttura interna di un database deve rispettare una certa architettura di immagazzinamento dei dati per poterne permettere il corretto salvataggio, il rispetto dei tipi impiegati e soprattutto agevolarne il recupero, un’operazione generalmente molto onerosa. Un DBMS è un servizio software, realizzato in genere come server in esecuzione continua, che gestisce uno o più database. I programmi che dovranno interagire quindi con una base di dati non potranno farlo direttamente, ma dovranno dialogare con il DBMS. Esso sarà l’unico ad accedere fisicamente alle informazioni. Quanto detto implica che il DBMS è il componente che si occupa di tutte le politiche di accesso, gestione, sicurezza ed ottimizzazione dei database.
Database relazionali e RDBMS
I DBMS esistenti non sono tutti della stessa tipologia. Al giorno d’oggi, ad esempio, si parla molto di DBMS NoSQL , nati per venire incontro alle esigenze dei più recenti servizi Web. Eppure un filone molto nutrito di DBMS, cui si deve il funzionamento della
Figura 2. Relazione tra tabelle in dettaglio (click per ingrandire) Esistono vari tipi di relazioni. Quello appena descritto è un esempio di relazione uno-a-molti , in quanto ad un autore possono corrispondere più libri. Nel nostro esempio si è assunto che un libro possa avere un solo autore, ma sfruttando relazioni di tipo differente si potranno rappresentare situazioni più vicine alla realtà. Nel seguito della guida, si approfondiranno tali argomenti fino a vedere una progettazione completa di un database. Protagonista importante di questa guida, e strumento fondamentale nell’interazione con i DBMS relazionali, è il linguaggio SQL (Structured Query Language). Si tratta di un formalismo che permette di indicare al DBMS quali operazioni svolgere sui database che gestisce. Tramite SQL si può attivare qualsiasi tipo di operazione, sia sui dati che sulla struttura interna del database, sebbene le principali (e più frequenti) operazioni ricadono in una delle seguenti quattro tipologie: inserimento, lettura, modifica e cancellazione di dati, tipicamente indicate con l’acronimo CRUD (Create-Read-Update-Delete). Il seguito della guida mostrerà un’ampia panoramica dei comandi SQL, nonché diversi esempi di utilizzo.
MySQL
MySQL è un RDBMS open source e libero , e rappresenta una delle tecnologie più note e diffuse nel mondo dell’IT. MySQL nacque nel 1996 per opera dell’azienda svedese Tcx, basato su un DBMS relazionale preesistente, chiamato mSQL. Il progetto venne distribuito in modalità open source per favorirne la crescita.
Dal 1996 ad oggi, MySQL si è affermato molto velocemente prestando le sue capacità a moltissimi software e siti Internet. I motivi di tale successo risiedono nella sua capacità di mantenere fede agli impegni presi sin dall’inizio: alta efficienza nonostante le moli di dati affidate; integrazione di tutte le funzionalità che offrono i migliori DBMS: indici , trigger e stored procedure ne sono un esempio, e saranno approfonditi nel corso della guida; altissima capacità di integrazione con i principali linguaggi di programmazione, ambienti di sviluppo e suite di programmi da ufficio.
