Introduzione a MySQL
(SQL - Structured Query Language)
(prof. Ivaldi Giuliano)

Attenzione! 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)

Creazione e cancellazione Database
• Creazione database​CREATE DATABASE IF NOT EXISTS nome_database;
clausola IF NOT EXISTS, se il database esiste già non genera un errore
• Cancellazione database​DROP DATABASE nome_database;
Esempio,
CREATE DATABASE prova;
DROP DATABASE prova;


Tipi di attributi

VARCHAR(n)​stringhe lunghe 'n'
TINYINT, INT e BIGINT​numeri interi a 1, 4 e 8 bytes FLOAT e DOUBLE​numeri reali (in virgola mobile)
DECIMAL(n,m)​numero con n cifre totali di cui m decimali (a virgola fissa)
DATE​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)


Manipolazione schemi
• Creazione schema
CREATE TABLE IF NOT EXISTS nome_tabella
( attributo1 tipo1 [NOT NULL] [AUTO_INCREMENT], attributo2 tipo2 [NOT NULL] [DEFAULT 'Valore']
[CHECK (attributo2 IN ('Valore1', 'Valore2', ... , 'ValoreN'))],
.​.​.
attributoN tipoN [NOT NULL],
PRIMARY KEY (attributo1, ..., attributoN) );
attributo = campo
NULL = indica che il campo può essere lasciato vuoto
NOT 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,
altrimenti inserirebbe NULL
CHECK = (non funziona in MySQL) imposta vincoli di dominio, cioè limita i valori che un utente può inserire in una colonna (campo)
esempi,
CHECK (attributo2 IN ('Valore1', 'Valore2', ... , 'ValoreN')) CHECK (attributo2 BETWEEN Valore1 AND ValoreN) CHECK (attributo2 > Valore1 AND attributo2 < Valore2)) CHECK (attributo2 LIKE 'stringa')
(stringa può contenere i caratteri jolly
% = carattere jolly, sostituisce un gruppo di caratteri
_ = carattere jolly, sostituisce un carattere) PRIMARY KEY (nome_attributo) = crea la chiave primaria su nome_attributo
• Rimozione tabella​DROP TABLE nome_tabella;
• Modificare un attributo di una tabella
ALTER TABLE nome_tabella CHANGE vecchio_nome_attributo nuovo_nome_attributo tipo_attributo; Esempio, ripreso da esempi seguenti
ALTER TABLE Appartamenti CHANGE Inquilino Nome_Inquilino VARCHAR(30);
• Aggiungere un nuovo attributo ad una tabella​ALTER TABLE nome_tabella ADD nuovo_attributo tipo_attributo; Esempio,​ALTER TABLE Spese ADD Beneficiario VARCHAR(20) ;
• Rimuovere un attributo​ALTER TABLE nome_tabella DROP nome_attributo; Esempio,​ALTER TABLE Proprietari DROP Saldo;
• Aggiungere un nuovo attributo chiave primaria ad una tabella
ALTER 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;
(crea la chiave primaria e le dà automaticamente dei valori nei record già esistenti)
• Rimuovere una chiave primaria​ALTER TABLE nome_tabella DROP PRIMARY KEY;


MySQL
Pagina 11

Esempio​ALTER TABLE Spese DROP PRIMARY KEY;
• Mostra i database presenti​SHOW DATABASES;
• Mostrare le tabelle del database​SHOW TABLES;
• Mostrare la struttura di una tabella​DESCRIBE nome_tabella;

Esempi,
Segue un esempio in cui vengono create tabelle senza utilizzare ancora le relazioni e le chiavi esterne
Schema relazionale CONDOMINIO1
APPARTAMENTI(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)
CREATE TABLE IF NOT EXISTS Appartamenti
( Cod_App​INT NOT NULL AUTO_INCREMENT,
Superficie​FLOAT NOT NULL DEFAULT '0',
Vani​INT NOT NULL DEFAULT '2',
Inquilino​VARCHAR(30),
Proprietario​VARCHAR(30) NOT NULL, PRIMARY KEY (Cod_App));
CREATE TABLE IF NOT EXISTS Proprietari
( Proprietario​VARCHAR(30) NOT NULL,
Indirizzo​VARCHAR(35) NOT NULL,
Telefono​VARCHAR(15),
Saldo​FLOAT NOT NULL,
PRIMARY KEY (Proprietario, Indirizzo));
CREATE TABLE IF NOT EXISTS Pagamenti
( Cod_Pagam​INT NOT NULL AUTO_INCREMENT, Data​DATE NOT NULL,
Importo​FLOAT NOT NULL,
Proprietario​VARCHAR(15) NOT NULL, PRIMARY KEY (Cod_Pagam));
CREATE TABLE IF NOT EXISTS Spese
( Cod_Spesa​INT NOT NULL AUTO_INCREMENT,
Data​DATE NOT NULL,
Voce​VARCHAR(200) NOT NULL
CHECK (Voce IN ('Riscaldamento', 'Scale', 'Cortile', 'Tetto')),
Importo​FLOAT NOT NULL CHECK (Importo>100),
Cod_App​INT NOT NULL,
PRIMARY KEY (Cod_Spesa));

Gestione degli indici
Tipi di indici:
PRIMARY 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;
UNIQUE: simile alla primary key, con la differenza che tollera valori nulli, mentre i duplicati restano vietati;
COLUMN 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.
FULLTEXT: sono indici che permettono di accelerare operazioni onerose, come la ricerca testuale su un intero campo.


• Creare un indice in una nuova tabella
CREATE TABLE IF NOT EXISTS nome_tabella (
...
attributoN INT NOT NULL,
...
INDEX nome_indice_ind (attributo1, ..., attributoN) );

• Aggiungere un indice ad una tabella esistente
ALTER TABLE nome_tab ADD [UNIQUE,FULLTEXT] INDEX nome_indice_ind (attrib1,..., attribN); Esempio​ALTER TABLE Spese ADD INDEX Dati_ind (Voce, Importo);

• Eliminare un indice​ALTER TABLE nome tabella DROP INDEX nome_indice; Esempio​ALTER TABLE Spese DROP INDEX Dati_ind;


MySQL
Pagina 11

Manipolazione delle tabelle

• Inserire una riga in una tabella
INSERT INTO nome_tabella (attributo1 , … , attributoN) VALUES ('valore1', … , 'valoreN');

• Aggiornare una o più righe di una stessa tabella UPDATE nome_tabella
SET​attributo1 = 'valore1'
… attributoN = 'valoreN'
[WHERE condizione];

• Cancellare una o più righe
DELETE FROM nome_tabella [WHERE condizione];


Esempi

a) inserire il proprietario Rossi Mario abitante in via Torino 34 a Savigliano, telefono 011.83.765, con saldo iniziale nullo
INSERT INTO Proprietari
(Proprietario, Indirizzo, Telefono, Saldo)
VALUES ('Rossi Mario', 'via Torino 34 - Savigliano', '01183.765', 0);
b) inserire i proprietari,
Verdi 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
INSERT INTO Proprietari
(Proprietario, Indirizzo, Telefono, Saldo)
VALUES ('Verdi Giuseppe', 'via Roma 120 - Torino', '011.973.34.22', 200), ('Bianchi Luisa', 'via Milano 15 - Ivrea', '011.965.87.44', -100);
c) il proprietario Rossi ha cambiato indirizzo e telefono UPDATE Proprietari
SET Indirizzo='nuovo indirizzo', Telefono='nuovo telefono', Saldo= 10
WHERE Proprietario = 'Rossi Mario';
d) addebitare il 25% di interessi di mora a ciascun proprietario che ha un saldo negativo UPDATE Proprietari
SET​Saldo=1.25*Saldo WHERE Saldo<0;
e) cancellare tutti i pagamenti avvenuti prima del 2058
DELETE FROM Pagamenti WHERE Data<’2058-01-01’;


MySQL
Pagina 11

Creazione delle Relazioni fra tabelle - Le chiavi esterne (Foreign keys)

Le 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.

• Creazione di una tabella con chiave esterna

CREATE TABLE IF NOT EXISTS nome_tabella_primaria
( campo_chiave_esterna tipo1 [NOT NULL] [AUTO_INCREMENT],
.​.​.
attributoN tipoN [NOT NULL],
PRIMARY KEY (attributo1, ..., attributoN) ) [TYPE=InnoDB];

CREATE TABLE IF NOT EXISTS nome_tabella_secondaria
( attributo1 tipo1 [NOT NULL] [AUTO_INCREMENT],
.​.​.
attributoN tipoN [NOT NULL],
PRIMARY KEY (attributo1, ..., attributoN), CONSTRAINT FK_Nome_ForeignKey
FOREIGN KEY (nome_attributo_tab_second) REFERENCES nome_tab_primaria(campo_chiave_esterna) ON DELETE Azione da attivare
ON UPDATE Azione da attivare) [TYPE=InnoDB];


'CONSTRAINT' significa 'Vincolo': più vincoli possono essere scritti uno di seguito all'altro separati da una virgola.

TYPE=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.

Azione da attivare = azione da far attivare in caso di cancellazione o modifica di un record nella tabella primaria:
• CASCADE: la cancellazione o la modifica di una riga nella tabella primaria causerà, a cascata, la medesima modifica nella tabella secondaria;
• 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;
• NO ACTION o RESTRICT: impedisce che la modifica o la cancellazione nella tabella primaria venga eseguita.

Esempi,
Viene ripreso l'esempio già presentato in precedenza, modificato con l'utilizzo delle relazioni e chiavi esterne. Schema relazionale CONDOMINIO2
PROPRIETARI(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)



PROPRIETARI


APPARTAMENTI

1ry key



Relazione
Cod_Prop
Relazione




Cod_App
Saldo
Indirizzo
1ry key



1ry key

Telefono

Chiave esterna

Proprietario
Vani
​
Proprietario
Inquilino
Superficie
Numerico
Chiave esterna

PAGAMENTI

Relazione

Cod_Pagam
Data
Importo
Proprietario
Numerico
​​Chiave


SPESE

1ry key

esterna

Cod_Spesa
Data
Voce
Importo
Cod_App

Numerico


MySQL
Pagina 11

CREATE TABLE IF NOT EXISTS Proprietari
( Cod_Prop​INT NOT NULL AUTO_INCREMENT,
Proprietario​VARCHAR(30) NOT NULL,
Indirizzo​VARCHAR(35) NOT NULL,
Telefono​VARCHAR(15),
Saldo​FLOAT NOT NULL, PRIMARY KEY (Cod_Prop));

CREATE TABLE IF NOT EXISTS Appartamenti
( Cod_App​INT NOT NULL AUTO_INCREMENT,
Superficie​FLOAT NOT NULL,
Vani​INT NOT NULL,
Inquilino​VARCHAR(30),
Proprietario​INT NOT NULL, PRIMARY KEY (Cod_App), CONSTRAINT FK_PropApp
FOREIGN KEY (Proprietario) REFERENCES Proprietari (Cod_Prop) ON DELETE NO ACTION
ON UPDATE NO ACTION);

CREATE TABLE IF NOT EXISTS Pagamenti
(Cod_Pagam​INT NOT NULL AUTO_INCREMENT,
Data​DATE NOT NULL,
Importo​FLOAT NOT NULL,
Proprietario​INT NOT NULL, PRIMARY KEY (Cod_Pagam), CONSTRAINT FK_PropPagam
FOREIGN KEY (Proprietario) REFERENCES Proprietari (Cod_Prop) ON DELETE NO ACTION
ON UPDATE NO ACTION);
CREATE TABLE IF NOT EXISTS Spese
(Cod_Spesa​INT NOT NULL AUTO_INCREMENT,
Data​DATE NOT NULL,
Voce​VARCHAR(200) NOT NULL,
Importo​FLOAT NOT NULL,
Cod_App​INT NOT NULL,
PRIMARY KEY (Cod_Spesa), CONSTRAINT FK_CodApp
FOREIGN KEY (Cod_App) REFERENCES Appartamenti (Cod_App) ON DELETE CASCADE
ON UPDATE CASCADE);



Modificare una tabella esistente aggiungendo chiavi esterne

• Creare una chiave esterna su una tabella già creata ALTER TABLE nome_tab_secondaria
ADD CONSTRAINT FK_nome_ForeignKey
FOREIGN KEY (nome_attributo_tab_second) REFERENCES nome_tab_primaria(campo_chiave_esterna) ON DELETE Azione da attivare
ON UPDATE Azione da attivare;

Esempio
ALTER TABLE Pagamenti
ADD CONSTRAINT FK_PropPagam
FOREIGN KEY (Proprietario) REFERENCES Proprietari (Cod_Prop) ON DELETE CASCADE
ON UPDATE CASCADE;


• Eliminare una chiave esterna
ALTER TABLE nome_tab_secondaria DROP FOREIGN KEY FK_nome_ForeignKey; Esempio​ALTER TABLE Pagamenti DROP FOREIGN KEY FK_PropPagam;


MySQL
Pagina 11