Operatori di confronto - 1 Operatore Significato Esempio Uguale SELECT * FROM attori WHERE codice_attore = 1; Diverso SELECT * FROM film WHERE codice_regista <> 1; Minore o uguale SELECT * FROM film WHERE anno <= 1945; Minore SELECT * FROM film WHERE voto < 1945; Maggiore o uguale SELECT * FROM prestiti WHERE data_prestito >= ‘2003-10-01’; Maggiore SELECT * FROM film WHERE voto > 1945; IS NULL E’ nullo SELECT * FROM prestiti WHERE data_restituzione IS NULL; IS NOT NULL Non è nullo SELECT * FROM prestiti WHERE data_restituzione IS NOT NULL; BETWEEN Compreso SELECT * FROM film WHERE anno BETWEEN 1900 AND 1950; NOT BETWEEN Non compreso SELECT * FROM film WHERE anno NOT BETWEEN 1900 AND 1950; X=Y <> != <= < >= > CORSI DI FORMAZIONE - Basi di Dati: MySql - Parte 3 - Novembre 2005 1 Operatori di confronto - 2 Operatore Significato Esempio COALESCE Primo elemento non nullo in lista SELECT COALESCE(NULL,3,1); GREATEST Il più grande SELECT GREATEST(NULL,3,34,1); IN Nella lista SELECT * FROM film WHERE anno IN(1945, 1946); NOT IN Fuori lista SELECT * FROM film WHERE anno NOT IN (1945,1946); INTERVAL Posizione relativa del primo valore SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200); LEAST Il più piccolo SELECT LEAST(NULL,3,34,1); Come SELECT * FROM clienti WHERE cognome LIKE ‘T%’; SELECT * FROM prestiti WHERE data_prestito LIKE “2003-10-%”; Non come SELECT * FROM clienti WHERE cognome NOT LIKE ‘T%’; LIKE NOT LIKE CORSI DI FORMAZIONE - Basi di Dati: MySql - Parte 3 - Novembre 2005 2 Operatori logici In MySQL gli esiti di un’espressione sono VERO o FALSO e vengono implementati anche con 1 (TRUE) oppure 0 (FALSE). Se l’esito dell’espressione è sconosciuto, con NULL. Operatore Significato Esempio Non è SELECT NOT 0; SELECT NOT 1; E SELECT * FROM film WHERE anno <= 1950 AND codice_regista = 1; OR , || Oppure (inclusivo) SELECT * FROM film WHERE anno <= 1990 OR codice_regista = 2; XOR Oppure (esclusivo) SELECT * FROM film WHERE anno <= 2000 XOR codice_regista = 2; NOT ! AND , && CORSI DI FORMAZIONE - Basi di Dati: MySql - Parte 3 - Novembre 2005 3 Principali funzioni stringa - 1 Funzione Restituisce Esempio ASCII Codice ASCII SELECT ASCII(‘A’); BIN Valore binario SELECT BIN(124); Carattere corrispon. SELECT CHAR(65,76,84); Lunghezza stringa SELECT cognome, CHAR_LENGTH(cognome) FROM clienti; CONCAT Stringhe concatenate SELECT CONCAT(cognome,’ ‘,nome) AS nominativo FROM clienti; CONCAT_WS Stringhe concatenate con separat. SELECT CONCAT_WS(‘ ‘,cognome,nome) AS nominativo FROM clienti; Posizione relativa di una sottostringa SELECT INSTR('Quel ramo del lago di...', 'del'); Minuscolo SELECT LCASE(CONCAT_WS(‘ ‘, cognome,nome)) AS nominativo FROM clienti; CHAR CHAR_LENGTH INSTR LCASE (anche LOWER) CORSI DI FORMAZIONE - Basi di Dati: MySql - Parte 3 - Novembre 2005 4 Principali funzioni stringa - 2 Funzione Restituisce Esempio Caratteri a sinistra SELECT cognome, LEFT(cognome,3) FROM clienti; Lunghezza stringa (conta + bytes per i caratteri multibyte) SELECT cognome, LENGTH(cognome) FROM clienti; LPAD Stringa con riempimento a sinistra SELECT LPAD(‘2000’,9,’0’); LTRIM Stringa senza spazi a sinistra SELECT LTRIM(‘ MID oppure SUBSTRING Caratteri in mezzo ad una stringa SELECT cognome, MID(cognome,2,3) FROM clienti; Stringa ripetuta SELECT REPEAT(‘Ahi ! ’,70); LEFT LENGHT REPEAT CORSI DI FORMAZIONE - Basi di Dati: MySql - Parte 3 - Novembre 2005 Test Spazi’) AS esito; 5 Principali funzioni stringa - 3 Funzione Restituisce Esempio RIGHT Caratteri a destra SELECT cognome, RIGHT(cognome,3) FROM clienti; RPAD Stringa con riempimento a destra SELECT CONCAT(RPAD(Nome,9,’ ’), Cognome) AS nominativo FROM clienti; RTRIM Stringa senza spazi a destra SELECT RTRIM(‘Test Spazi TRIM Stringa senza spazi a destra e a sinistra SELECT TRIM(‘ Maiuscolo SELECT UCASE(CONCAT_WS(‘ ‘, cognome,nome)) AS nominativo FROM clienti; Stringa con sottostringa sostituita SELECT REPLACE(‘Stringa prima della sostituzione’,’prima del’, ’dopo ’); LCASE (anche UPPER) REPLACE CORSI DI FORMAZIONE - Basi di Dati: MySql - Parte 3 - Novembre 2005 Test Spazi ’) AS esito; ’) AS esito; 6 Principali funzioni numeriche - 1 Funzione Restituisce Esempio Addizione Sottrazione Moltiplicazione Divisione SELECT 120.5+130; SELECT 12-13; SELECT 12*13; SELECT (26+3)/13; (Le parentesi tonde definiscono la priorità) DIV Numero intero da una divisione SELECT 5 DIV 2; ABS Valore assoluto SELECT ABS(-3); CEILING Intero più piccolo maggiore dell’argomento SELECT CEILING(3.75); FLOOR Intero più grande minore dell’argomento SELECT FLOOR(3.75); Resto della divisione SELECT 5 MOD 4; SELECT 5 % 4; SELECT MOD(5,4); Potenza di un numero SELECT POWER(3,2); + * / MOD oppure % POWER CORSI DI FORMAZIONE - Basi di Dati: MySql - Parte 3 - Novembre 2005 7 Principali funzioni numeriche - 2 Funzione RAND ROUND TRUNCATE Restituisce Esempio Numero casuale SELECT RAND(); (senza seme) SELECT RAND(3); (con seme,per la stessa seq.) Valore arrotondato all’intero più prossimo. Il secondo argomento indica: -il numero di decimali in output, se positivo; -arrotondamento alle decine, centinaia … se negativo SELECT SELECT SELECT SELECT SELECT SELECT SELECT SELECT SELECT Valore troncato SELECT TRUNCATE(2.23,1); SELECT TRUNCATE(-2.23,0); SELECT TRUNCATE(74356.89,-2); ROUND(-1.43); ROUND(-2.52); ROUND(1.58); ROUND(1.39, 1); ROUND(1.39, 0); ROUND(333.39, -1); ROUND(353.39, -2); ROUND(350, -2); ROUND(351, -2); CORSI DI FORMAZIONE - Basi di Dati: MySql - Parte 3 - Novembre 2005 8 Principali funzioni data/ora - 1 Funzione Restituisce Esempio ADDDATE o DATE_ADD Data incrementata SELECT data_prestito, ADDDATE(data_prestito, INTERVAL 3 DAY) AS nuova_data FROM prestiti; (l’intervallo può essere DAY, MONTH, YEAR …) ADDTIME Data incrementata SELECT ADDTIME(‘2004-12-31 23:59:59.999999', '1 1:1:1.000002'); CURDATE() Data corrente SELECT CURDATE(); CURTIME() Ora corrente SELECT CURTIME(); Data e ora corrente SELECT NOW(); DATE Parte data di una data e ora SELECT DATE(‘2004-12-31 23:59:59.999999‘); DATEDIFF Differenza in giorni tra date SELECT data_prestito, DATEDIFF(CURDATE(),data_prestito) FROM prestiti WHERE data_restituzione IS NULL; NOW() CORSI DI FORMAZIONE - Basi di Dati: MySql - Parte 3 - Novembre 2005 9 Principali funzioni data/ora - 2 Funzione Restituisce Esempio Data decrementata SELECT data_prestito, data_restituzione, DATE_SUB (data_prestito, INTERVAL 31 DAY) AS nuova_data FROM prestiti WHERE data_restituzione IS NOT NULL; DATE_FORMAT Data formattata SELECT DATE_FORMAT(‘2005-10-10 22:23:00', '%W %M %Y'); Il secondo argomento è una stringa di formattazione i cui specificatori sono descritti nella manualistica DAY Il giorno di una data SELECT data_prestito, DAY(data_prestito) FROM prestiti; DAYOFWEEK Il giorno della settimana SELECT data_prestito, DAYOFWEEK(data_prestito) FROM prestiti; 1=Domenica, 2=Lunedì, 3=Martedì … HOUR L’ora di una data /ora SELECT CURTIME(), HOUR(CURTIME()); LAST_DAY Data ultimo giorno del mese SELECT LAST_DAY(CURDATE()); DATE_SUB CORSI DI FORMAZIONE - Basi di Dati: MySql - Parte 3 - Novembre 2005 10 Principali funzioni data/ora - 3 Funzione Restituisce Esempio MINUTE Il minuto di una data /ora SELECT CURTIME(), MINUTE(CURTIME()); MONTH Il mese di una data SELECT CURDATE(), MONTH(CURDATE()); SECOND Il secondo di una data /ora SELECT CURTIME(), SECOND(CURTIME()); Parte ora di una data e ora SELECT TIME(‘2004-12-31 23:59:59.999999‘); WEEKDAY Il giorno della settimana SELECT data_prestito, WEEKDAY(data_prestito) FROM prestiti; 0=Lunedì, 1=Martedì, 2=Mercoledì … YEAR L’anno di una data SELECT CURDATE(), YEAR(CURDATE()); TIME CORSI DI FORMAZIONE - Basi di Dati: MySql - Parte 3 - Novembre 2005 11 Funzioni di aggregazione Nella selezione dei dati è possibile usare le funzioni di aggregazione, cioè funzioni che effettuano determinate operazioni su gruppi di record. Funzione Restituisce Esempio AVG Media dei valori SELECT AVG(prezzo) FROM prestiti; COUNT Conteggio record SELECT COUNT(*) FROM film; INSERT INTO film VALUES (10,'Jurassic Park 2',2,2,1999); SELECT codice_regista, COUNT(*) FROM film GROUP BY codice_regista; MIN Valore minimo SELECT MIN(anno) FROM film; MAX Valore massimo SELECT MAX(anno) FROM film; SUM Somma SELECT codice_cliente, SUM(prezzo), COUNT(*), AVG(prezzo) FROM prestiti GROUP BY codice_cliente; CORSI DI FORMAZIONE - Basi di Dati: MySql - Parte 3 - Novembre 2005 12 Esercitazione 3.1 Scrivere le istruzioni SQL per estrarre: 1. l’elenco dei prestiti effettuati tra il ‘2003-09-01’ e ‘2003-10-31’; 2. l’elenco degli attori il cui cognome inizia per ‘S’; 3. la lunghezza, in numero di caratteri, di nome e cognome degli attori; 4. il cognome e la prima lettera del nome puntata dalla tabella ‘clienti’ (esempio “Verdi E.”); 5. il risultato di 3 elevato a 6; 6. il resto della divisione 236545 / 342; 7. il numero di giorni intercorsi tra oggi ed il ‘1900-01-01’; CORSI DI FORMAZIONE - Basi di Dati: MySql - Parte 3 - Novembre 2005 13