Cheat Sheet

Tipi dati

Nota sulle lettere maiuscole e minuscole

Il linguaggio SQL non fa distinzione tra maiuscole e minuscole. Questo ha portato a diversi stili di scrittura dei comandi, tutti sintatticamente validi.

Chi usa le lettere maiuscole ritiene che sia più chiaro e facile distinguere le keyword dal resto dei comandi.

Chi usa le lettere minuscole lo ritiene più in linea con la sintassi degli attuali linguaggi di programmazione, considerando che spesso si usano degli editor che si occupano di evidenziare le parole chiavi.

Personalmente preferisco usare le lettere minuscole, ma se usate le maiuscole va bene lo stesso. L'importante è che presa una scelta, siate coerenti con la scelta fatta (almeno all'interno della stessa query!!).

Convenzioni sui nomi

Seguiamo le seguenti convenzioni:

• per i nomi delle tabelle, usiamo il plurale (es. utenti)
• se possibile, per le tabelle usare un nome collettivo (es. personale invece di dipendenti)
• per i nomi delle colonne, usiamo il singolare (es. nome)
• per le chiavi primarie, usiamo il nome della tabella al singolare seguito da _id (es. utente_id)

Data Definition Language (DDL)

Il Data Definition Language (DDL) è la parte del linguaggio SQL che si occupa di creare, modificare o eliminare elementi nella schema logico del database. In altre parole, contiene i comandi per creare o modificare databases e tabelle. Le parole chiave usate nel DDL sono:

• create per creare elementi
• alter per modificare elementi
• drop per eliminare elementi

show databases;
Elenca tutti i database del DBMS

show databases;

show tables;
Elenca tutte le tabelle nel database

show tables;

show columns from nome_tabella;
Elenca le colonne di una tabella e le caratteristiche di ogni colonna

show columns from utenti;

use nome_database;
Seleziona un database

use calendario;

use bar;

CREATE

create table nome_tabella (nome_colonna tipo , nome_colonna2 tipo);
Crea una tabella all'interno del database

create table eventi (titolo varchar(255), data int);

create table studenti (nome varchar(100), cognome varchar(100), eta int unsigned);

primary key
Indica una o più colonne che identificano in modo univoco una riga. In italiano: chiave primaria.

create table cittadini (codice_fiscale char(16) primary key, nome varchar(255));

create table utenti (username varchar(50) primary key, password varchar(255));

create table telefoni (modello char(10) primary key, disponibilita int);

auto_increment
Incrementa il valore di una colonna ogni volta che viene aggiunta una riga

create table prodotti (id int primary key auto_increment, nome varchar(255));

foreign key
Crea un riferimento ad una chiave primaria di un'altra tabella. In italiano: chiave esterna.

Spesso le tabelle hanno dei riferimenti le une con le altre. Pensiamo ad esempio alle tabelle "eventi" e "utenti". Ogni evento ha un organizzatore (uno solo), quindi vorremmo poter inserire questa informazione nel nostro database. Per fare questa operazione abbiamo bisogno di una chiave esterna.

create table eventi (
    titolo varchar(100),
    evento_id int auto_increment,
    organizzatore int,
    foreign key (organizzatore) references utenti(utente_id)
);

DROP

DROP TABLE nome_tabella;
Elimina una tabella dal database

drop table prodotti;

drop table utenti;

drop table studenti;

ALTER

alter table nome_tabella rename to nuovo_nome_tabella;
Cambia il nome di una tabella

alter table utenti rename studenti;

alter table ata rename personale_ata ;

RENAME

rename table nome_tabella to nuovo_nome_tabella, nome_tabella_2 to nuovo_nome etc...;
Cambia il nome di una o più tabelle (funziona solo in MySQL)

rename table utenti to professori;

rename table ny_times to pubblicazioni_ny_times, the_guardian to pubblicazioni_the_guardian;

Data Manipulation Language

Il Data Manipulation Language (DML) è la parte del linguaggio SQL che serve per modificare, aggiornare e cancellare il contenuto delle tabelle, senza alternarne però lo schema logico. Si compone prevalentemente di:

• insert per inserire una nuova riga in una tabella
• update per aggiornare una o più colonne di una riga
• delete per cancellare una o più righe

INSERT

insert into nome_tabella (nome_colonna, nome_colonna2...) values (valore, valore2...);
Inserisce una riga nella tabella

insert into studenti (nome, cognome) values ('mario', 'rossi');

insert into targhe (targa) values ('ab123cd');

insert into prodotti (nome, costo, disponibilita) values ('acqua', 0.50);

UPDATE

update nome_tabella set (nome_colonna = valore, nome_colonna2 = valore, ...) where condizione;
modifica una riga o più righe nella tabella

update studenti set nome='claudio' where cognome='rossi';

DELETE

delete from nome_tabella where condizione;
elimina una o più righe nella tabella

delete from studenti where cognome='rossi';

Data Query Language

Il Data Query Language (DQL) è la parte del linguaggio SQL che serve per interrogare il database. Si compone essenzialmente del comando select con tutte le sue diverse forme e clausole.

SELECT

select nome_colonna, nome_colonna2... from nome_tabella;
Seleziona (filtrando) dati da una tabella

select nome, cognome from dipendenti;

select costo from merendine;

select * from video -- "*" significa "tutte le colonne";

WHERE

where condizione;
where introduce una o più condizioni per filtrare le righe

select nome, cognome from cittadini where regione='lazio';

select nome, indirizzo from hotel where costo < 150.00 and stanze_libere > 2;

select nome, iban from libri where review between 3 and 5;

ORDER BY

order by colonna1,colonna2,... [asc|desc];
*order by ordina i risultati in base ad una colonna; si possono ordinare i risultati in ordine crescente (asc, default) o decrescente (desc). si possono specificare più colonne in sequenza, in questo caso ordinerà prima per la colonna1, poi per la colonna2, etc.

select nome, cognome
from cittadini
order by cognome;

select nome, cognome
from cittadini
order by cognome,nome,età desc;

JOIN

Finora abbiamo trattato interrogazioni con una sola tabella. Molto spesso, tuttavia, serve fare interrogazioni su più tabelle contemporaneamente. Riprendiamo l'esempio della tabella eventi e la tabella utenti, e che ogni evento abbia esattamente un organizzatore.

Per fare interrogazioni su più tabelle, si usa la clausola join.

Vediamo come si crea un'interrogazione con la join. Si comincia sempre con il comando select. Dopo aver specificato come al solito le colonne che vogliamo selezionare, usiamo:

1. la clausola from con la tabella che contiene la chiave esterna
2. quindi aggiungiamo la clausola join con la tabella a cui fa riferimento la chiave esterna
3. aggiungiamo infine la keyword on con la condizione sulle chiavi che si vogliono unire

Quando usiamo colonne da più tabelle, è bene specificare sempre anche la tabella quando richiamiamo una colonna, usando la notazione punto, ad esempio eventi.organizzatore.

Se non ci sono conflitti tra i nomi delle colonne, non sarebbe necessario specificare la tabella; tuttavia è una buona pratica diffusa e consolidata specificare sempre la tabella.

Tecnicamente, si può partire da una qualsiasi tabella, non solo quella con le chiavi esterne. Però per convenzione partiamo da quella, in modo da darci un'ordine mentale.

Se e solo se la colonna con la chiave esterna ha lo stesso nome della colonna con la chiave primaria, posso usare la clausola using anziché on. Questo è particolarmente utile nelle tabelle associative, che vedremo in seguito.

select eventi.titolo, utenti.nome, utenti.cognome
from eventi
join utenti on eventi.organizzatore=utenti.utente_id;

select eventi.titolo, utenti.nome, utenti.cognome
from partecipanti
join eventi using(evento_id)
join utenti using(utente_id);

La clausola join può essere usata insieme alle clausole where o order by per filtrare ed organizzare ulteriormente i risultati.

select eventi.titolo, eventi.costo, utenti.nome, utenti.cognome
from eventi
join utenti on eventi.organizzatore=utenti.utente_id
where eventi.costo > 0
order by utenti.cognome asc;

AS

Come si può intuire dalle ultime query, le richieste complesse possono diventare anche molto lunghe. Per questo motivo è possibile abbreviare i nomi delle tabelle o delle colonne con il comando as.

Attenzione ad alcune cose in particolare:

• a volte l'alias viene utilizzato prima di essere definito, questo può sembrare contro-intuitivo ma è il comportamento normale
• per convenzione, l'alias delle tabelle è il primo carattere della tabella; se ci dovessero essere più tabelle che iniziano con lo stesso nome, si possono usare le prime due lettere oppure un numero incrementale (es. s1,s2, etc.)

-- comando as per rinominare le tabelle
select e.titolo, e.costo, u.nome, u.cognome
from eventi as e 
join utenti as u on e.organizzatore=u.utente_id
where e.costo > 0 
order by u.cognome asc;

-- comando as per rinominare le colonne
-- in questo caso la tabella risultante avrà nelle colonne i nuovi nomi degli alias
select e.titolo as titolo_evento,
    u.nome as nome_organizzatore,
    u.cognome as cognome_organizzatore
from eventi as e 
join utenti as u on e.organizzatore=u.utente_id
where e.costo > 0 
order by u.cognome asc;

Drop table if exists

In fase di sviluppo, può essere utile fare il drop delle tabelle e ricrearle da capo, invece di modificarle. Se si fa il drop di una tabella che non esiste, però, SQL ci ritorna un errore; questo è particolarmente fastidioso quando scriviamo uno script. Per evitare questo problema esiste il drop condizionato all'esistenza effettiva della tabella.

On delete

Cosa succede se cancelliamo una riga che è un riferimento in un'altra tabella? Consideriamo ad esempio che ho diversi eventi organizzati da Mario Rossi. Ad un certo punto, cancello l'utente Mario Rossi. Cosa succede a tutti gli eventi organizzati da lui?

Esistono varie strategie, essenzialmente:

• se la chiave esterna è facoltativa, si può sostituire con NULL
• cancello la riga con la chiave esterna

create table eventi (
    -- ...
    -- se viene cancellato l'organizzatore, nell'evento viene impostato a NULL
    foreign key(organizzatore) references utenti(utente_id) ON DELETE SET NULL

create table eventi (
    -- ...
    -- se viene cancellato l'organizzatore, viene cancellato anche l'evento
    foreign key(organizzatore) references utenti(utente_id) ON DELETE CASCADE