SQL SERVER

1. SQL SERVER Modulo 4 Relatore: Stefano Furlan

2. Una premessa • Lezioni pratiche con in mente il progetto • Requisiti • Information retrieval • Non avere paura di usare google • Capacità progettuale • Dove voglio arrivare? (definizione della specifica) • Cosa mi serve per arrivarci? (analisi) • In che ordine lo faccio? (separazione dei task) • Come capisco che sono arrivato? (verifica del risultato) • OVVERO: Buonsenso!

3. Cos’è un DBMS? • In realtà dovremmo chiederci… ? • A COSA CI SERVE? • A quali domande del nostro committente dobbiamo dare risposta?

4. Architettura di un sistema di telecontrollo Dati Sistema di controllo Controllo Pannelli fotovoltaici Utente Applicazione di configurazione Collettore dati DB Sistema di reportistica

5. A cosa serve un DBMS? • Storage Dati • Ma quali dati? • Dati tecnici • Configurazioni • Logs • Siamo fortunati: tipicamente una base dati per il telecontrollo è piuttosto semplice

6. Struttura del DBMS QUERY SQL MOTORE DATI Dati

7. LINGUAGGIO SQL • Structuredquerylanguage • Comandi testuali strutturati per il database • DML (data manipulationlanguage) • DDL (data definitionlanguage) • Vantaggio: compatibilità • TSQL: dialetto di MS Sql Server

8. SQL SERVER • È un sw server! • Come lo contatto? • Meccanismo di istanze (Nomemacchina\nomeserver) • Accesso usando tool SSMS • Interfaccia userfriendly • Query testuali

9. Operazioni preliminari • Accesso con SSMS • Creazione di un DB vuoto • Skip delle impostazioni

10. CREAZIONE DI UNA BASE DATI

11. MAPPARE LA STRUTTURA DELLA REALTA’ • PANNELLI (3 pannelli piccoli e uno grande) • GRUPPI (divisi in due zone) • ENERGIA PRODOTTA (che producono energia ad ogni ora)

12. ENTITÀ FONDAMENTALI in una base dati • TABELLE • Campi (colonne) tipizzati • Chiavi • Indici • RELAZIONI • Chiavi esterne • VISTE • PROCEDURE

13. OPERAZIONI FONDAMENTALI in una base dati • Lettura o selezione (SELECT) • Scrittura o inserimento (INSERT) • Modifica (UPDATE) • Cancellazione (DELETE)

14. TABELLA Esempio di tabella

15. TABELLA • CAMPI tipizzati • Numerici • Interi • Tinyint (1 byte) 0-255 • Smallint (2 bytes) -32000 +32000 • Integer (4 bytes) +-miliardi (+-2^31) • Bigint(8 bytes) +- le particelle dell’universo (+-2^63) • Frazionari • Float (attenzione: errori di arrotondamento) • Decimal(x,y) (preciso ma lento) • Money (4 cifre decimali) • Testuali • Char(x) • Varchar(x) • nvarchar(x) (unicode) • Date • Date • Datetime • Altri (poco usati)

16. TABELLE: chiave primaria • Campo o campi che permettono di individuare univocamente ciascuna riga • Spesso un codice ID intero ad auto incremento CHIAVE PRIMARIA

17. TABELLA: altre features • Identity • Default value • Vincoli check • Indici Univoci • Valori Nulli e campi nullabili • Convenzione: i nomi tabella iniziano con «tab»

18. Un po’ di pratica • Accediamo a SQL server per creare una tabella per i pannelli (Usando SSMS)

19. Interfaccia di creazione tabella

20. Ultime info su SSMS • Gli oggetti non si aggiornano da soli in visualizzazione • Refresh • Ctrl-shift R • Context menu • Inserimento dei dati in tabella manualmente

21. TABELLA: relazioni tra tabelle • Si chiama «relazionale» per qualche motivo • Relazioni tra entità • Integrità referenziale enforced da DB (il db impedisce che nella tabella dei consumi esista mai una riga «orfana»)

22. Il diagramma database • Metodo facilitato di creazione • Permette di tenere sotto controllo in maniera grafica le relazioni • Usato per separare parti diverse di un progetto complesso

23. Esempio di semplice diagramma…

24. Torniamo alla nostra realtà… • PANNELLI (3 pannelli piccoli e uno grande) • GRUPPI (divisi in due zone) • ENERGIA PRODOTTA (che producono energia ad ogni ora)

25. Progettazione di una base dati: STEPS • Individuazione delle entità • (tipicamente ogni entità avrà la sua tabella) • Individuazione delle relazioni • Alcune tabelle saranno collegate ad altre in maniera naturale • Produzione del diagramma Esempio: dati di sistema fotovoltaico • Dati di funzionamento (energia prodotta ogni quarto d’ora) • Configurazioni (quanti e quali pannelli ho?) • Dati di log (listato diagnostico)

26. REGOLE DI PROGETTAZIONE • Buonsenso! • Ogni tabella mappa un’entità (o una relazione) • Deve esistere sempre una chiave primaria • Evitare dati non atomici (concatenazione di dati nello stesso campo) • Mai lo stesso dato contemporaneamente in due posti diversi! • Mai perdere informazioni • Evitare che vengano inserite porcherie • Forme normali

27. TABELLE: alcuni consigli • Usare il tipo di dato più corretto per ogni campo ma pensando con lungimiranza • Impedire l’inserimento di dati scorretti • Se qualcosa può andare storto LO FARA’ • ES: Codici fiscali • Campi di testo libero a Varchar(max) • Vedi la legge di murphy sopra

28. ESERCITAZIONE • Che tipo di dato è bene usare per : • Per la temperatura del corpo umano? Perché? • Per un numero civico? Perché? • Per la potenza istantanea di un impianto? Perché? • Che vincoli mettereste sui dati di cui sopra? • Progettare un SEMPLICE database per gestire un parco fotovoltaico (usando il diagramma) • Entità: pannello, energia prodotta ogni quarto d’ora da ciascun pannello, raggruppamento di pannelli in aree.

29. Esercitazione: data entry • Una volta creata la base dati • Creare 4 pannelli • Creare due zone (una con tre pannelli l’altra con uno) • Inserire dati di energia prodotta per tutti i giorni di Gennaio 2013 per tutti e 4 i pannelli • Possibile utilizzo di excel per il data entry

30. Progettazione di una base dati • PROGETTARE UNA BASE DATI È UN’ATTIVITÀ SOLO APPARENTEMENTE BANALE • MOLTI PERICOLI NASCOSTI • FARE MODIFICHE IN CORSO D’OPERA PUÒ ESSERE DISPENDIOSO • MEGLIO PROGETTARE BENE DALL’INIZIO • Chiara definizione delle entità • Buone namingconventions (prefissi, camel-case)

31. APPROFONDIMENTI • Indici e Considerazioni di performances

32. UTILIZZODI UNA BASE DATI

33. Azioni sulla base dati • Lettura o selezione (SELECT) • Scrittura o inserimento (INSERT) • Modifica (UPDATE) • Cancellazione (DELETE) • QUERY • Frase in pseudo inglese • ES: seleziona <qualcosa> da una <tabella> quando <si verifica una certa condizione>

34. SELECT • La funzione più usata in assoluto • Sintassi usata anche in altre funzionalità • Ritorna un recordset

35. SELECT: sintassi SELECT <lista campi> FROM <tabella> WHERE <condizione> ORDER BY <lista campi ordinamento> Es: SELECT [idPannello] ,[Pannello] ,[Note] FROM tabPannelli Le Keyword in MAIUSCOLO per convenzione

36. SELECT : lista campi • SELECT Nome,PotenzaasPotKWH FROM tabPannelli

37. SELECT: lista campi • Asterisco= tutti i campi • Es: select * from tabella • Alias • Es: SELECT nome asNomePannello from tabPannelli • Sono possibili alcune operazioni in fase di select (concatenazione di stringhe) • ES: SELECT ‘il pannello ‘ + idpannello +’ è rotto’ as Anomalia FROM tabPannelli • 3 part naming convention • Es: SELECT * FROM dbFotovoltaico.dbo.tabPannelli • Parentesi quadre se campo contiene spazio • ES: SELECT nome as [il mio nome] FROM tabPannelli

38. SELECT : condizioni • WHERE <campo/valore><operatore><campo/valore> • Es: WHERE idpannello=1 • WHERE IN (lista elementi) • Es: WHERE idpannello IN (1,2,3) • WHERE IN (query) • Es: WHERE idpannello IN (selectidpannello from tabPannelliwherenomepannello=‘pannello1’) • WHERE LIKE • Es: WHERE nome LIKE ‘Pannello%’ • Uso di valori testuali • Padding • SQL iniection

39. SELECT: condizioni • Usabili tutti gli operatori di confronto (< , > , = , != , <>) • Es: WHERE idPannello=1 • Altri operatori (LIKE, BETWEEN) • Tutti i costrutti booleani (AND, OR, NOT) • Usare sempre le parentesi • Es: SELECT * FROM tabPannelli WHERE idpannello=1 AND (nomepannello=‘Pannello1’ OR nomepannello=‘Pannello2’ )

40. SELECT: ordinamento • La clausola ORDER BY specifica il criterio di ordinamento • Es: SELECT * FROM tabPannelli ORDER BY idPannello ASC ,NomePannello DESC

41. JOIN Come collegare i dati di tabelle distinte? tabEnergia tabPannelli ?

42. JOIN (collegamento di più tabelle) • INNER • È l’opzione più comune • Vengono restituite solo le righe comuni a entrambe le tabelle • Se in una delle tabelle non ho un match la riga «scompare» • Possono essere restituite più righe delle due tabelle! • (tipicamente è un errore)

43. JOIN • LEFTrighe a sxpiù righe a dx che matchano • RIGTHè una leftcon le tabelle scambiate(da non usare per problemi di leggibilità)

44. JOIN • FULL OUTER • CROSS • Fa il cartesiano(tutte le combinazioni). • Con le tabelle sopra sono 18 righe…

45. JOIN: sintassi • SELECT <lista campi> • FROM <tabella> as <Alias> • INNER JOIN <tabella> as <Alias2> • ON Alias1.campo = Alias2.campo • Es: • SELECT * • FROM tabEnergia • INNER JOIN tabPannelli • ON tabEnergia.idPannello=tabPannelli.idPannello

46. ESERCITAZIONE: • Mostrare la tabella consumi ma, al posto dell’idPannello mostrare il nome del pannello

47. JOIN in cascata • È possibile combinare più tabelle in una serie di join successivi • Es: • SELECT * • FROM tabEnergia • INNER JOIN tabPannelli • ON tabEnergia.idPannello=tabPannelli.idPannello • INNER JOIN tabGruppi • ON tabGruppi.idGruppo=tabPannelli.idGruppo

48. Best practices su SELECT e JOIN • Attenzione ai cartesiani! • Attenzione alle INNER su campi non chiave: potrebbero «sparire righe» o uscire «righe in più» • Ordinare le tabelle nella clausola di join a partire dalla più informativa • Usare solo left join e mai right join per facilità di lettura • Formattazione delle query

49. AGGREGAZIONE E RAGGRUPPAMENTO • Che succede se voglio sommare dei valori? • Es: sapere quanto ha prodotto ogni pannello in un certo periodo? • Ma io ho questo:

50. AGGREGAZIONE E RAGGRUPPAMENTO SELECT<lista campi> <lista funzione di aggregazione(Campo)> FROM <tabella> WHERE <condizione> HAVING <condizione su campi raggruppati> Es: SELECT tabPannelli.Pannello , SUM(tabEnergiav2.Energia) AS EnergiaTot FROM tabEnergiav2 INNER JOIN tabPannelli ON tabEnergiav2.idPannello = tabPannelli.idPannello GROUP BY tabPannelli.idPannello , tabPannelli.Pannello