Basi di dati (Sistemi Informativi) Scuola di Dottorato in Scienze Veterinarie per la Salute Animale e la Sicurezza Alimentare Lezione 2 B Ing. Mauro Zaninelli – [email protected] a.a. 2005 - 2006 1 Concetti Fondamentali Introduzione Creazione ed eliminazione di bd Creazione ed eliminazione di tabelle Inserimenti di ennuple Interrogazioni clausola SELECT clausola FROM clausola WHERE clausola ORDER BY metodo di scrittura Cancellazioni Aggiornamenti 2 Introduzione SQL (“Structured Query Language”) DDL (“Data Definition Language”) creazione degli oggetti dello schema DCL (“Data Control Language”) linguaggio per l’interazione con il DBMS tutte le operazioni vengono specificate in SQL controllo degli utenti e delle autorizzazioni DML (“Data Manipulation Language”) manipolazione dell’istanza della base di dati (interrogazioni e aggiornamenti) 3 Storia dello Standard Prime implementazioni Primi prodotti commerciali IBM System/R 1979 (SEQUEL) IBM SQL/DS, Oracle 1981 SQL-86 prima versione dello standard, basata sul dialetto IBM 4 Storia dello Standard SQL-89 (SQL-1) vincoli di integrità livello1 e livello2 SQL-92 (SQL-2) entry intermediate full SQL-92, full SQL-92, intermediate SQL-92, entry SQL-89, livello 2 SQL-89, livello 1 5 Le Istruzioni Fondamentali DDL creazione della base di dati CREATE DATABASE creazione delle tabelle CREATE TABLE DML inserimento delle ennuple INSERT INTO interrogazioni SELECT eliminazione delle ennuple DELETE modifica della ennuple UPDATE 6 Creazione ed Eliminazione di BD Istruzioni del DDL Sintassi CREATE DATABASE <nome>; DROP DATABASE <nome>; Esempio CREATE DATABASE universita; DROP DATABASE universita; 7 Creazione ed Eliminazione di Tabelle Istruzioni del DDL CREATE TABLE DROP TABLE Sintassi CREATE TABLE <nome> (<schema>); DROP TABLE <nome>; 8 Creazione ed Eliminazione di Tabelle Esempio: la tabella Professori CREATE TABLE Professori ( cod char(4) PRIMARY KEY, cognome varchar(20) NOT NULL, nome varchar(20) NOT NULL, qualifica char(15), facolta char(10) ); DROP TABLE Professori; 9 Creazione ed Eliminazione di Tabelle Esempio: la tabella Esami CREATE TABLE Esami ( studente integer, corso char(3), voto integer, lode bool, PRIMARY KEY (studente, corso)); 10 Creazione ed Eliminazione di Tabelle <schema> Definizione di attributo una o più definizioni di attributo zero o più definizioni di vincoli di tabella <nomeattributo> <tipo> [<vincoli di colonna>] Definizioni di vincoli di tabella normalmente vincoli relativi a più attributi 11 Creazione ed Eliminazione di Tabelle <nomeattributo> identificatore <tipo> INT, INTEGER REAL, FLOAT DECIMAL(lung,dec) DOUBLE PRECISION CHAR(n), CHARACTER(n) VARCHAR(n) LONG VARCHAR, TEXT BOOLEAN, BOOL DATE TIME TIMESTAMP BINARY(n), BIT(n) VARBINARY(n), VARBIT(n) LONG VARBINARY, BLOB 12 Creazione ed Eliminazione di Tabelle Vincoli di colonna PRIMARY KEY UNIQUE NOT NULL REFERENCES <chiave della tabella dest.> [ON update CASCADE] [ON delete CASCADE] CHECK (<espressione>) 13 Creazione ed Eliminazione di Tabelle Vincoli di tabella (su più attributi) PRIMARY KEY (<lista attributi>) UNIQUE (<lista attributi>) FOREIGN KEY (<lista attributi>) REFERENCES <chiave esterna> [ON update CASCADE] [ON delete CASCADE] CHECK (<espressione>) 14 CREATE TABLE Professori ( CREATE TABLE Tutorato ( cod char(4) PRIMARY KEY, studente integer cognome varchar(20) NOT NULL, REFERENCES Studenti(matr), nome varchar(20) NOT NULL, tutor integer qualifica char(15), REFERENCES Studenti(matr), facolta char(10) ); PRIMARY KEY (studente,tutor)); CREATE TABLE Esami ( CREATE TABLE Studenti ( studente integer matr integer PRIMARY KEY, REFERENCES Studenti(matr) cognome varchar(20) NOT NULL, ON DELETE cascade nome varchar(20) NOT NULL, ON UPDATE cascade, ciclo char(20), corso char(3) anno integer, REFERENCES Corsi(cod), relatore char(4) voto integer, REFERENCES Professori(cod) lode bool, ); CHECK (voto>=18 and voto<=30), CHECK (not lode or voto=30), CREATE TABLE Corsi ( PRIMARY KEY (studente, corso)); cod char(3) PRIMARY KEY, titolo varchar(20) NOT NULL, CREATE TABLE Numeri ( ciclo char(20), professore char(4) docente char(4) REFERENCES Professori(cod), REFERENCES Professori(cod) numero char(9), ); PRIMARY KEY (professore,numero)); 15 Inserimenti Istruzione del DML Sintassi INSERT INSERT INTO <tabella> VALUES (<valori>); Semantica inserimento della ennupla nella tabella corrispondenza ordinata tra valori e attributi (notazione posizionale) 16 Inserimenti Esempi: INSERT INTO Professori VALUES (‘FT’, ‘Totti’, ‘Francesco’, ‘ordinario’, ‘Ingegneria’); INSERT INTO Studenti VALUES (111, ‘Rossi’, ‘Mario’, ‘laurea tr.’, 3, null); INSERT INTO Corsi VALUES (‘PR1’, ‘Programmazione 1’, ‘laurea tr.’, ‘FT’); 17 Interrogazioni Istruzione del DML SELECT sintassi concreta per specificare operatori dell’algebra Filosofia parzialmente dichiarativa si specificano gli operatori da applicare, non l’ordine in cui devono essere applicati l’ottimizzatore sceglie la strategia ottima 18 Interrogazioni Tre “clausole” principali (nucleo) SELECT proiezioni, ridenominazioni, elimin. duplicati FROM SELECT, FROM, WHERE tabelle, join, prodotti cartesiani, alias WHERE selezioni 19 Esempi “Studenti della laurea triennale di anni successivi al primo” SELECT * FROM Studenti WHERE ciclo=‘laurea tr.’ AND anno>1; 20 Esempi “Matricola e cognome degli studenti che hanno sostenuto l’esame di informatica teorica” SELECT matricola, cognome FROM Studenti JOIN Esami ON matr=studente JOIN Corsi ON cod=corso WHERE titolo=‘Inform. t.’; 21 Clausola ORDER BY Serve a specificare Sintassi ordinamenti finali ORDER BY <attributi> Dove <attributi> è una lista di attributi di tabelle che compaiono nella clausola FROM 22 Clausola ORDER BY Esempio SELECT * FROM Studenti ORDER BY cognome, annoCorso Normalemente: l’ordinamento è crescente Ordinamento decrescente: DESC ORDER BY cognome, annoCorso DESC 23 Cancellazioni Istruzione del DML Sintassi DELETE DELETE FROM <tabella> [<clausola WHERE>]; <clausola WHERE>: identica a quella vista Semantica elimina dalla tabella tutte le ennuple (che soddisfano la condizione se è specificata) 24 Cancellazioni Esempi: DELETE FROM Numeri; DELETE FROM Studenti WHERE matr=111; DELETE FROM Corsi WHERE ciclo=‘laurea tr.’ AND docente=‘FT’; 25 Aggiornamenti Istruzione del DML Sintassi UPDATE UPDATE <tabella> SET <attributo>=<espressione> [<clausola WHERE>] Semantica aggiorna il valore dell’attributo di tutte le ennuple (che soddisfano la condizione se è specificata) 26 Aggiornamenti Esempi: UPDATE Studenti SET anno=anno+1; UPDATE Studenti SET matr=11111 WHERE matr=111; UPDATE Corsi SET docente=‘VC’ WHERE ciclo=‘laurea tr.’ AND docente=‘FT’; 27 CREATE TABLE Professori ( CREATE TABLE Tutorato ( cod char(4) PRIMARY KEY, studente integer cognome varchar(20) NOT NULL, REFERENCES Studenti(matr), nome varchar(20) NOT NULL, tutor integer qualifica char(15), REFERENCES Studenti(matr), facolta char(10) ); PRIMARY KEY (studente,tutor)); CREATE TABLE Esami ( CREATE TABLE Studenti ( studente integer matr integer PRIMARY KEY, REFERENCES Studenti(matr) cognome varchar(20) NOT NULL, ON DELETE cascade nome varchar(20) NOT NULL, ON UPDATE cascade, ciclo char(20), corso char(3) anno integer, REFERENCES Corsi(cod), relatore char(4) voto integer, REFERENCES Professori(cod) lode bool, ); CHECK (voto>=18 and voto<=30), CHECK (not lode or voto=30), CREATE TABLE Corsi ( PRIMARY KEY (studente, corso)); cod char(3) PRIMARY KEY, titolo varchar(20) NOT NULL, CREATE TABLE Numeri ( ciclo char(20), professore char(4) docente char(4) REFERENCES Professori(cod), REFERENCES Professori(cod) numero char(9), ); PRIMARY KEY (professore,numero)); 28 Corsi T codice CHAR(3) PK Esami T titolo VARCHAR(20) Numeri T corso CHAR(3) PK, FK ciclo CHAR(20) numero CHAR(9) PK studente INTEGER PK, FK docente CHAR(4) docente CHAR(4) PK, FK FK voto INTEGER lode BOOL Professori T cod CHAR(4) PK cognome VARCHAR(20) Studenti T matr INTEGER PK cognome VARCHAR(20) nome VARCHAR(20) qualifica CHAR(15) facolta CHAR(10) nome VARCHAR(20) ciclo CHAR(20) anno INTEGER relatore CHAR(4) FK Tutorato T studente INTEGER PK, FK tutor INTEGER PK, FK 29 Professori Studenti Corsi cod cognome nome qualifica facolta FT Totti Francesco ordinario Ingegneria CV Vieri Christian associato Scienze ADP Del Piero Alessandro supplente null matr cognome nome ciclo anno relatore 111 Rossi Mario laurea tr. 1 null 222 Neri Paolo laurea tr. 2 null 333 Rossi Maria laurea tr. 1 null 444 Pinco Palla laurea tr. 3 FT 77777 Bruno Pasquale laurea sp. 1 FT 88888 Pinco Pietro laurea sp. 1 CV cod titolo ciclo docente PR1 Programmazione I laurea tr. FT ASD Algoritmi e Str. Dati laurea tr. CV INFT Informatica Teorica laurea sp. ADP 30 Tutorato Esami studente tutor professore numero 111 77777 FT 0971205145 222 77777 FT 347123456 333 88888 VC 0971205227 444 88888 ADP 0971205363 ADP 338123456 Numeri studente corso voto lode 111 PR1 27 false 222 ASD 30 true 111 INFT 24 false 77777 PR1 21 false 77777 ASD 20 false 88888 ASD 28 false 88888 PR1 30 false 88888 INFT 30 true 31 Termini della Licenza Original Work BY: G. Mecca – Università della Basilicata [email protected] This work is licensed under the Creative Commons AttributionShareAlike License. To view a copy of this license, visit http://creativecommons.org/licenses/by-sa/1.0/ or send a letter to Creative Commons, 559 Nathan Abbott Way, Stanford, California 94305, USA. Questo lavoro viene concesso in uso secondo i termini della licenza “Attribution-ShareAlike” di Creative Commons. Per ottenere una copia della licenza, è possibile visitare http://creativecommons.org/licenses/by-sa/1.0/ oppure inviare una lettera all’indirizzo Creative Commons, 559 Nathan Abbott Way, Stanford, California 94305, USA. 32