KnrPnHTQ

1Introduzione a MySQL
2(SQL - Structured Query Language)
3(prof. Ivaldi Giuliano)
4 
5Attenzione! La sintassi delle istruzioni seguenti può leggermente variare in base al DBMS utilizzato (attenzione soprattutto agli '-apici in un eventuale copia e incolla delle istruzioni)
6 
7Creazione e cancellazione Database
8• Creazione database​CREATE DATABASE IF NOT EXISTS nome_database;
9clausola IF NOT EXISTS, se il database esiste già non genera un errore
10• Cancellazione database​DROP DATABASE nome_database;
11Esempio,
12CREATE DATABASE prova;
13DROP DATABASE prova;
14 
15 
16Tipi di attributi
17
18VARCHAR(n)​stringhe lunghe 'n'
19TINYINT, INT e BIGINT​numeri interi a 1, 4 e 8 bytes FLOAT e DOUBLE​numeri reali (in virgola mobile)
20DECIMAL(n,m)​numero con n cifre totali di cui m decimali (a virgola fissa)
21DATE​date nel formato ‘AAAA-MM-GG' (es. '2055-03-01', le virgolette sono necessarie) TINYINT o VARCHAR(1) usati come tipo booleano (true o false, 0 o 1)
22
23 
24Manipolazione schemi
25• Creazione schema
26CREATE TABLE IF NOT EXISTS nome_tabella
27( attributo1 tipo1 [NOT NULL] [AUTO_INCREMENT], attributo2 tipo2 [NOT NULL] [DEFAULT 'Valore']
28[CHECK (attributo2 IN ('Valore1', 'Valore2', ... , 'ValoreN'))],
29.​.​.
30attributoN tipoN [NOT NULL],
31PRIMARY KEY (attributo1, ..., attributoN) );
32attributo = campo
33NULL = indica che il campo può essere lasciato vuoto
34NOT NULL = indica che il campo è obbligatorio, bisogna cioè dargli un valore AUTO_INCREMENT = indica che verrà dato un valore automatico in ordine progressivo DEFAULT 'Valore' = imposta il valore di Default, nel caso non venga inserito nessun valore,
35altrimenti inserirebbe NULL
36CHECK = (non funziona in MySQL) imposta vincoli di dominio, cioè limita i valori che un utente può inserire in una colonna (campo)
37esempi,
38CHECK (attributo2 IN ('Valore1', 'Valore2', ... , 'ValoreN')) CHECK (attributo2 BETWEEN Valore1 AND ValoreN) CHECK (attributo2 > Valore1 AND attributo2 < Valore2)) CHECK (attributo2 LIKE 'stringa')
39(stringa può contenere i caratteri jolly
40% = carattere jolly, sostituisce un gruppo di caratteri
41_ = carattere jolly, sostituisce un carattere) PRIMARY KEY (nome_attributo) = crea la chiave primaria su nome_attributo
42• Rimozione tabella​DROP TABLE nome_tabella;
43• Modificare un attributo di una tabella
44ALTER TABLE nome_tabella CHANGE vecchio_nome_attributo nuovo_nome_attributo tipo_attributo; Esempio, ripreso da esempi seguenti
45ALTER TABLE Appartamenti CHANGE Inquilino Nome_Inquilino VARCHAR(30);
46• Aggiungere un nuovo attributo ad una tabella​ALTER TABLE nome_tabella ADD nuovo_attributo tipo_attributo; Esempio,​ALTER TABLE Spese ADD Beneficiario VARCHAR(20) ;
47• Rimuovere un attributo​ALTER TABLE nome_tabella DROP nome_attributo; Esempio,​ALTER TABLE Proprietari DROP Saldo;
48• Aggiungere un nuovo attributo chiave primaria ad una tabella
49ALTER TABLE nome_tabella ADD nome_attributo INT NOT NULL AUTO_INCREMENT PRIMARY KEY; Esempio​ALTER TABLE Spese ADD Cod_Spesa INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
50(crea la chiave primaria e le dà automaticamente dei valori nei record già esistenti)
51• Rimuovere una chiave primaria​ALTER TABLE nome_tabella DROP PRIMARY KEY;
52 
53
54MySQL
55Pagina 11
56 
57Esempio​ALTER TABLE Spese DROP PRIMARY KEY;
58• Mostra i database presenti​SHOW DATABASES;
59• Mostrare le tabelle del database​SHOW TABLES;
60• Mostrare la struttura di una tabella​DESCRIBE nome_tabella;
61 
62Esempi,
63Segue un esempio in cui vengono create tabelle senza utilizzare ancora le relazioni e le chiavi esterne
64Schema relazionale CONDOMINIO1
65APPARTAMENTI(Cod_App,Superficie,Vani,Inquilino,Proprietario) PROPRIETARI(Proprietario, Indirizzo,Telefono,Saldo) PAGAMENTI(Cod_Pagam,Data,Importo,Proprietario) SPESE(Cod_Spesa,Data,Voce,Importo,Cod_App)
66CREATE TABLE IF NOT EXISTS Appartamenti
67( Cod_App​INT NOT NULL AUTO_INCREMENT,
68Superficie​FLOAT NOT NULL DEFAULT '0',
69Vani​INT NOT NULL DEFAULT '2',
70Inquilino​VARCHAR(30),
71Proprietario​VARCHAR(30) NOT NULL, PRIMARY KEY (Cod_App));
72CREATE TABLE IF NOT EXISTS Proprietari
73( Proprietario​VARCHAR(30) NOT NULL,
74Indirizzo​VARCHAR(35) NOT NULL,
75Telefono​VARCHAR(15),
76Saldo​FLOAT NOT NULL,
77PRIMARY KEY (Proprietario, Indirizzo));
78CREATE TABLE IF NOT EXISTS Pagamenti
79( Cod_Pagam​INT NOT NULL AUTO_INCREMENT, Data​DATE NOT NULL,
80Importo​FLOAT NOT NULL,
81Proprietario​VARCHAR(15) NOT NULL, PRIMARY KEY (Cod_Pagam));
82CREATE TABLE IF NOT EXISTS Spese
83( Cod_Spesa​INT NOT NULL AUTO_INCREMENT,
84Data​DATE NOT NULL,
85Voce​VARCHAR(200) NOT NULL
86CHECK (Voce IN ('Riscaldamento', 'Scale', 'Cortile', 'Tetto')),
87Importo​FLOAT NOT NULL CHECK (Importo>100),
88Cod_App​INT NOT NULL,
89PRIMARY KEY (Cod_Spesa));
90 
91Gestione degli indici
92Tipi di indici:
93PRIMARY KEY: applicato ad uno o più campi di una tabella permette di distinguere univocamente ogni riga; il campo sottoposto all’indice primary key non ammette duplicati né campi nulli;
94UNIQUE: simile alla primary key, con la differenza che tollera valori nulli, mentre i duplicati restano vietati;
95COLUMN INDEX: sono gli indici più comuni, applicati ad un campo di una tabella, hanno puramente lo scopo di velocizzarne l’accesso permettendo valori duplicati e nulli; come variante, possono esistere indici “multicolonna”, che includono quindi più campi della tabella.
96FULLTEXT: sono indici che permettono di accelerare operazioni onerose, come la ricerca testuale su un intero campo.
97 
98 
99• Creare un indice in una nuova tabella
100CREATE TABLE IF NOT EXISTS nome_tabella (
101...
102attributoN INT NOT NULL,
103...
104INDEX nome_indice_ind (attributo1, ..., attributoN) );
105 
106• Aggiungere un indice ad una tabella esistente
107ALTER TABLE nome_tab ADD [UNIQUE,FULLTEXT] INDEX nome_indice_ind (attrib1,..., attribN); Esempio​ALTER TABLE Spese ADD INDEX Dati_ind (Voce, Importo);
108 
109• Eliminare un indice​ALTER TABLE nome tabella DROP INDEX nome_indice; Esempio​ALTER TABLE Spese DROP INDEX Dati_ind;
110 
111
112MySQL
113Pagina 11
114 
115Manipolazione delle tabelle
116 
117• Inserire una riga in una tabella
118INSERT INTO nome_tabella (attributo1 , … , attributoN) VALUES ('valore1', … , 'valoreN');
119 
120• Aggiornare una o più righe di una stessa tabella UPDATE nome_tabella
121SET​attributo1 = 'valore1'
122… attributoN = 'valoreN'
123[WHERE condizione];
124 
125• Cancellare una o più righe
126DELETE FROM nome_tabella [WHERE condizione];
127 
128 
129Esempi
130 
131a) inserire il proprietario Rossi Mario abitante in via Torino 34 a Savigliano, telefono 011.83.765, con saldo iniziale nullo
132INSERT INTO Proprietari
133(Proprietario, Indirizzo, Telefono, Saldo)
134VALUES ('Rossi Mario', 'via Torino 34 - Savigliano', '01183.765', 0);
135b) inserire i proprietari,
136Verdi Giuseppe abitante in via Roma 120 a Torino, telefono 011.973.34.22, con saldo iniziale 200 Bianchi Luisa abitante in via Milano 15 a Ivrea, telefono 011.965.87.44, con saldo iniziale -100
137INSERT INTO Proprietari
138(Proprietario, Indirizzo, Telefono, Saldo)
139VALUES ('Verdi Giuseppe', 'via Roma 120 - Torino', '011.973.34.22', 200), ('Bianchi Luisa', 'via Milano 15 - Ivrea', '011.965.87.44', -100);
140c) il proprietario Rossi ha cambiato indirizzo e telefono UPDATE Proprietari
141SET Indirizzo='nuovo indirizzo', Telefono='nuovo telefono', Saldo= 10
142WHERE Proprietario = 'Rossi Mario';
143d) addebitare il 25% di interessi di mora a ciascun proprietario che ha un saldo negativo UPDATE Proprietari
144SET​Saldo=1.25*Saldo WHERE Saldo<0;
145e) cancellare tutti i pagamenti avvenuti prima del 2058
146DELETE FROM Pagamenti WHERE Data<’2058-01-01’;
147
148
149MySQL
150Pagina 11
151 
152Creazione delle Relazioni fra tabelle - Le chiavi esterne (Foreign keys)
153 
154Le chiavi esterne sono costrutti che sfruttano gli indici per collegare due tabelle mediante l’associazione di campi; applicare vincoli ai vari campi di diverse tabelle consente di mantenere la consistenza dei dati.
155 
156• Creazione di una tabella con chiave esterna
157 
158CREATE TABLE IF NOT EXISTS nome_tabella_primaria
159( campo_chiave_esterna tipo1 [NOT NULL] [AUTO_INCREMENT],
160.​.​.
161attributoN tipoN [NOT NULL],
162PRIMARY KEY (attributo1, ..., attributoN) ) [TYPE=InnoDB];
163 
164CREATE TABLE IF NOT EXISTS nome_tabella_secondaria
165( attributo1 tipo1 [NOT NULL] [AUTO_INCREMENT],
166.​.​.
167attributoN tipoN [NOT NULL],
168PRIMARY KEY (attributo1, ..., attributoN), CONSTRAINT FK_Nome_ForeignKey
169FOREIGN KEY (nome_attributo_tab_second) REFERENCES nome_tab_primaria(campo_chiave_esterna) ON DELETE Azione da attivare
170ON UPDATE Azione da attivare) [TYPE=InnoDB];
171 
172 
173'CONSTRAINT' significa 'Vincolo': più vincoli possono essere scritti uno di seguito all'altro separati da una virgola.
174 
175TYPE=InnoDB: perché si possano impostare le chiavi esterne, entrambe le tabelle, primaria e secondaria, devono avere come Storage Engine, InnoDB (Storage Engine = motore di memorizzazione dei dati), ma ciò avviene di default, quindi questa opzione si può omettere.
176 
177Azione da attivare = azione da far attivare in caso di cancellazione o modifica di un record nella tabella primaria:
178• CASCADE: la cancellazione o la modifica di una riga nella tabella primaria causerà, a cascata, la medesima modifica nella tabella secondaria;
179• SET NULL: il campo oggetto della relazione nella tabella secondaria verrà impostato a NULL; in questo caso, è necessario che tale campo non sia stato qualificato come NOT NULL in fase di creazione;
180• NO ACTION o RESTRICT: impedisce che la modifica o la cancellazione nella tabella primaria venga eseguita.
181 
182Esempi,
183Viene ripreso l'esempio già presentato in precedenza, modificato con l'utilizzo delle relazioni e chiavi esterne. Schema relazionale CONDOMINIO2
184PROPRIETARI(Cod_Prop, Proprietario, Indirizzo, Telefono, Saldo) APPARTAMENTI(Cod_App, Superficie, Vani, Inquilino, Proprietario) PAGAMENTI(Cod_Pagam, Data, Importo, Proprietario) SPESE(Cod_Spesa, Data, Voce, Importo, Cod_App)
185 
186 
187 
188PROPRIETARI
189 
190 
191APPARTAMENTI
192
1931ry key
194 
195 
196
197Relazione
198Cod_Prop
199Relazione
200
201 
202 
203 
204Cod_App
205Saldo
206Indirizzo
2071ry key
208 
209 
210 
2111ry key
212
213Telefono
214 
215Chiave esterna
216 
217Proprietario
218Vani
219​
220Proprietario
221Inquilino
222Superficie
223Numerico
224Chiave esterna
225 
226PAGAMENTI
227
228Relazione
229
230Cod_Pagam
231Data
232Importo
233Proprietario
234Numerico
235​​Chiave 
236 
237 
238SPESE
239
2401ry key
241
242esterna
243 
244Cod_Spesa
245Data
246Voce
247Importo
248Cod_App
249 
250Numerico
251 
252
253MySQL
254Pagina 11
255 
256CREATE TABLE IF NOT EXISTS Proprietari
257( Cod_Prop​INT NOT NULL AUTO_INCREMENT,
258Proprietario​VARCHAR(30) NOT NULL,
259Indirizzo​VARCHAR(35) NOT NULL,
260Telefono​VARCHAR(15),
261Saldo​FLOAT NOT NULL, PRIMARY KEY (Cod_Prop));
262 
263CREATE TABLE IF NOT EXISTS Appartamenti
264( Cod_App​INT NOT NULL AUTO_INCREMENT,
265Superficie​FLOAT NOT NULL,
266Vani​INT NOT NULL,
267Inquilino​VARCHAR(30),
268Proprietario​INT NOT NULL, PRIMARY KEY (Cod_App), CONSTRAINT FK_PropApp
269FOREIGN KEY (Proprietario) REFERENCES Proprietari (Cod_Prop) ON DELETE NO ACTION
270ON UPDATE NO ACTION);
271 
272CREATE TABLE IF NOT EXISTS Pagamenti
273(Cod_Pagam​INT NOT NULL AUTO_INCREMENT,
274Data​DATE NOT NULL,
275Importo​FLOAT NOT NULL,
276Proprietario​INT NOT NULL, PRIMARY KEY (Cod_Pagam), CONSTRAINT FK_PropPagam
277FOREIGN KEY (Proprietario) REFERENCES Proprietari (Cod_Prop) ON DELETE NO ACTION
278ON UPDATE NO ACTION);
279CREATE TABLE IF NOT EXISTS Spese
280(Cod_Spesa​INT NOT NULL AUTO_INCREMENT,
281Data​DATE NOT NULL,
282Voce​VARCHAR(200) NOT NULL,
283Importo​FLOAT NOT NULL,
284Cod_App​INT NOT NULL,
285PRIMARY KEY (Cod_Spesa), CONSTRAINT FK_CodApp
286FOREIGN KEY (Cod_App) REFERENCES Appartamenti (Cod_App) ON DELETE CASCADE
287ON UPDATE CASCADE);
288 
289 
290 
291Modificare una tabella esistente aggiungendo chiavi esterne
292 
293• Creare una chiave esterna su una tabella già creata ALTER TABLE nome_tab_secondaria
294ADD CONSTRAINT FK_nome_ForeignKey
295FOREIGN KEY (nome_attributo_tab_second) REFERENCES nome_tab_primaria(campo_chiave_esterna) ON DELETE Azione da attivare
296ON UPDATE Azione da attivare;
297 
298Esempio
299ALTER TABLE Pagamenti
300ADD CONSTRAINT FK_PropPagam
301FOREIGN KEY (Proprietario) REFERENCES Proprietari (Cod_Prop) ON DELETE CASCADE
302ON UPDATE CASCADE;
303 
304 
305• Eliminare una chiave esterna
306ALTER TABLE nome_tab_secondaria DROP FOREIGN KEY FK_nome_ForeignKey; Esempio​ALTER TABLE Pagamenti DROP FOREIGN KEY FK_PropPagam;
307 
308
309MySQL
310Pagina 11