MySQL: A tutorial

Ing. Francesco Mercaldo

Corso di Basi di Dati Dipartimento di Ingegneria

Università degli Studi del Sannio

Piattaforma di riferimento Dal link http://dev.mysql.com/ eseguire il

download di MySQL: ◦ MySQL 5.0 per diverse piattaforme tra cui

Linux e Windows; ◦ Altri tool e driver utili: MySQL Administrator; MySQL Query Browser; MySQL Connector/J (JDBC). MySQL Migration Toolkit MySQL Workbench HeidiSQL Navicat

Connessione/disconnessione Per connettersi al server è necessario fornire

login e password shell> mysql -h host -u user -p Enter password: ********

host and user rappresentano: ◦ l’hostname dove risiede MySQL; ◦ lo username di un utente che possiede un account sul

server; -p specifica al server la richiesta della password

all’utente.

C:\>mysql -u francesco -p Enter password: ******* Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 19 to server version: 4.0.18-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>

Per avere l’help: C:\>mysql –help Per uscire: mysql> QUIT

Creazione di un account Tipicamente viene eseguita dall’utente

root, mediante l’uso del comando GRANT. C:\>mysql -u root -p Enter password: ******* mysql> GRANT ALL ON nomeDB.* to -> ‘user’@’localhost’ IDENTIFIED BY -> ‘nome_password’;

Connessione da un client Per consentire la connessione da un server specifico.

mysql> GRANT ALL ON nomeDB.* to -> ‘user’@’nome_server’ IDENTIFIED BY

-> ‘nome_password’;

Esempi mysql> SELECT VERSION(), CURRENT_DATE; +-----------+--------------+ | VERSION() | CURRENT_DATE | +-----------+--------------+ | 4.0.18-nt | 2004-11-02 | +-----------+--------------+ 1 row in set (0.06 sec) mysql> SELECT SIN(PI()/4), (4+1)*5; +-------------+---------+ | SIN(PI()/4) | (4+1)*5 | +-------------+---------+ | 0.707107 | 25 | +-------------+---------+

mysql> select user() -> , -> current_date; +-------------------+--------------+ | user() | current_date | +-------------------+--------------+ | alfredo@localhost | 2004-11-02 | +-------------------+--------------+ 1 row in set (0.00 sec) mysql>

Selezione di un database Comandi show - use mysql> show databases; +----------+ | Database | +----------+ | sicurfer | | mysql | | test | +----------+ 3 rows in set (0.00 sec) mysql> use sicurfer; Database changed

Creazione di un database L’amministratore crea il database; mysql> CREATE DATABASE menagerie;

Successivamente l’utente crea le tabelle che desidera: CREATE TABLE pet ( name VARCHAR(20),

owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE );

Describe mysql> describe pet; +---------+-------------+------+-----+---------+-------

+ | Field | Type | Null | Key | Default | Extra

| +---------+-------------+------+-----+---------+-------

+ | name | varchar(20) | YES | | NULL |

| | owner | varchar(20) | YES | | NULL |

| | species | varchar(20) | YES | | NULL |

| | sex | char(1) | YES | | NULL |

| | birth | date | YES | | NULL |

| | death | date | YES | | NULL |

| +---------+-------------+------+-----+---------+-------

+ 6 rows in set (0.02 sec)

Query: esempi mysql> SELECT * FROM pet; +----------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+--------+---------+------+------------+------------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Fang | Benny | dog | m | 1990-08-27 | NULL | | Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 | | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | | Slim | Benny | snake | m | 1996-04-29 | NULL | | Puffball | Diane | hamster | f | 1999-03-30 | NULL | +----------+--------+---------+------+------------+------------+

Query: esempi mysql> SELECT * FROM pet WHERE name = 'Bowser'; +--------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+-------+---------+------+------------+------------+ | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | +--------+-------+---------+------+------------+------------+ mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm') -> OR (species = 'dog' AND sex = 'f'); +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+

Query: esempi mysql> SELECT name, species, birth FROM pet -> WHERE species = 'dog' OR species = 'cat';

+--------+---------+------------+ | name | species | birth | +--------+---------+------------+ | Fluffy | cat | 1993-02-04 | | Claws | cat | 1994-03-17 | | Buffy | dog | 1989-05-13 | | Fang | dog | 1990-08-27 | | Bowser | dog | 1989-08-31 | +--------+---------+------------+

Query: esempi mysql> SELECT name, birth FROM pet ORDER BY birth

DESC; +----------+------------+ | name | birth | +----------+------------+ | Puffball | 1999-03-30 | | Chirpy | 1998-09-11 | | Whistler | 1997-12-09 | | Slim | 1996-04-29 | | Claws | 1994-03-17 | | Fluffy | 1993-02-04 | | Fang | 1990-08-27 | | Bowser | 1989-08-31 | | Buffy | 1989-05-13 | +----------+------------+

Query: esempi mysql> SELECT name, species, birth FROM pet -> ORDER BY species, birth DESC; +----------+---------+------------+ | name | species | birth | +----------+---------+------------+ | Chirpy | bird | 1998-09-11 | | Whistler | bird | 1997-12-09 | | Claws | cat | 1994-03-17 | | Fluffy | cat | 1993-02-04 | | Fang | dog | 1990-08-27 | | Bowser | dog | 1989-08-31 | | Buffy | dog | 1989-05-13 | | Puffball | hamster | 1999-03-30 | | Slim | snake | 1996-04-29 | +----------+---------+------------+

Query: esempi mysql> SELECT name, birth, CURDATE(), -> (YEAR(CURDATE())-YEAR(birth)) -> - (RIGHT(CURDATE(),5)<RIGHT(birth,5)) -> AS age -> FROM pet; +----------+------------+------------+------+ | name | birth | CURDATE() | age | +----------+------------+------------+------+ | Fluffy | 1993-02-04 | 2003-08-19 | 10 | | Claws | 1994-03-17 | 2003-08-19 | 9 | | Buffy | 1989-05-13 | 2003-08-19 | 14 | | Fang | 1990-08-27 | 2003-08-19 | 12 | | Bowser | 1989-08-31 | 2003-08-19 | 13 | | Chirpy | 1998-09-11 | 2003-08-19 | 4 | | Whistler | 1997-12-09 | 2003-08-19 | 5 | | Slim | 1996-04-29 | 2003-08-19 | 7 | | Puffball | 1999-03-30 | 2003-08-19 | 4 | +----------+------------+------------+------+

Query: esempi

mysql> SELECT name, birth, death, -> (YEAR(death)-YEAR(birth))-(RIGHT(death,5)<RIGHT(birth,5)) -> AS age -> FROM pet WHERE death IS NOT NULL ORDER BY age; +--------+------------+------------+------+ | name | birth | death | age | +--------+------------+------------+------+ | Bowser | 1989-08-31 | 1995-07-29 | 5 | +--------+------------+------------+------+ mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5; +-------+------------+ | name | birth | +-------+------------+ | Buffy | 1989-05-13 | +-------+------------+

Il valore NULL per un campo assume il seguente significato: mancante, sconosciuto ed è trattato diversamente dagli altri valori.

Per testare il valore NULL non possono essere usati gli operatori di confronto quali =, <, o <>.

Esempio: mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL; +----------+-----------+----------+----------+ | 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL | +----------+-----------+----------+----------+ | NULL | NULL | NULL | NULL | +----------+-----------+----------+----------+

Con l’uso di IS NULL e IS NOT NULL si ottiene:

mysql> SELECT 1 IS NULL, 1 IS NOT NULL; +-----------+---------------+ | 1 IS NULL | 1 IS NOT NULL | +-----------+---------------+ | 0 | 1 | +-----------+---------------+

Quando si usa ORDER BY, i valori NULL sono inseriti all’inizio con ASC ed alla fine con ORDER BY ... DESC.

mysql> SELECT * FROM pet WHERE name LIKE 'b%'; +--------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+------------+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | +--------+--------+---------+------+------------+------------+ mysql> SELECT * FROM pet WHERE name LIKE '%fy'; +--------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+-------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +--------+--------+---------+------+------------+-------+

mysql> SELECT * FROM pet WHERE name LIKE '%w%'; +----------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+------------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | +----------+-------+---------+------+------------+------------+ mysql> SELECT * FROM pet WHERE name LIKE '_____'; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+

mysql> SELECT COUNT(*) FROM pet; +----------+ | COUNT(*) | +----------+ | 9 | +----------+ mysql> SELECT owner, COUNT(*) FROM pet GROUP BY

owner; +--------+----------+ | owner | COUNT(*) | +--------+----------+ | Benny | 2 | | Diane | 2 | | Gwen | 3 | | Harold | 2 | +--------+----------+

mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex; +---------+------+----------+ | species | sex | COUNT(*) | +---------+------+----------+ | bird | NULL | 1 | | bird | f | 1 | | cat | f | 1 | | cat | m | 1 | | dog | f | 1 | | dog | m | 2 | | hamster | f | 1 | | snake | m | 1 | +---------+------+----------+

mysql> SELECT species, sex, COUNT(*) FROM pet WHERE species = 'dog' OR species = 'cat' -> GROUP BY species, sex; +---------+------+----------+ | species | sex | COUNT(*) | +---------+------+----------+ | cat | f | 1 | | cat | m | 1 | | dog | f | 1 | | dog | m | 2 | +---------+------+----------+

mysql> CREATE TABLE event (name VARCHAR(20),

-> date DATE,

-> type VARCHAR(15),

-> remark VARCHAR(255));

mysql> SELECT pet.name, -> (YEAR(date)-YEAR(birth)) -

(RIGHT(date,5)<RIGHT(birth,5)) AS age, -> remark -> FROM pet, event -> WHERE pet.name = event.name AND type =

'litter'; +--------+------+-----------------------------+ | name | age | remark | +--------+------+-----------------------------+ | Fluffy | 2 | 4 kittens, 3 female, 1 male | | Buffy | 4 | 5 puppies, 2 female, 3 male | | Buffy | 5 | 3 puppies, 3 female | +--------+------+-----------------------------+

mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species -> FROM pet AS p1, pet AS p2 -> WHERE p1.species = p2.species AND p1.sex = 'f' AND p2.sex =

'm'; +--------+------+--------+------+---------+ | name | sex | name | sex | species | +--------+------+--------+------+---------+ | Fluffy | f | Claws | m | cat | | Buffy | f | Fang | m | dog | | Buffy | f | Bowser | m | dog | +--------+------+--------+------+---------+

Tipi di dati: Numerici INTEGER DOUBLE PRECISION REAL DECIMAL NUMERIC

Per il tipo DECIMAL il range dipende da

M e D, occupa M+2 byte.

CREATE DATABASE CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification [, create_specification] ...]

create_specification: [DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name

CREATE DATABASE crea un database con il nome dato;

Per poter eseguire il comando bisogna avere il privilegio CREATE per creazione database.

DROP DATABASE DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

CREATE TABLE

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [select_statement]

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(] LIKE old_tbl_name [)];

CREATE TABLE crea una tabella nel db; Per poter eseguire il comando bisogna

avere il privilegio CREATE per le tabelle.

CREATE TABLE student

(

name VARCHAR(20) NOT NULL,

sex ENUM('F','M') NOT NULL,

student_id INT UNSIGNED NOT NULL AUTO_INCREMENT,

PRIMARY KEY (student_id)

);

DROP/RENAME TABLE DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ... [RESTRICT | CASCADE] RENAME TABLE tbl_name TO new_tbl_name [, tbl_name2

TO new_tbl_name2] ... Esempio:

CREATE TABLE new_table (...); RENAME TABLE old_table TO backup_table, new_table TO old_table;

Esempio mysql> SELECT CONCAT(last_name,', ',first_name) AS full_name

-> FROM mytable ORDER BY full_name;

Le operazioni di Join

Right Outer Join Left Outer Join Join

Le righe che soddisfano la join

Le righe escluse dalla join della tabella a sx

Le righe escluse dalla join della tabella a dx

Full outer join

Rappresentazione visuale dei Join

mysql> SELECT table1.* FROM table1

-> LEFT JOIN table2 ON table1.id=table2.id

-> WHERE table2.id IS NULL;

Questo esempio trova tutte le righe della table1 che non sono presenti in table2 (ovvero tutte le righe di table1 che non hanno un corrispondente in table2).

La clausola USING (column_list) elenca una lista di colonne che devono essere presenti in entrambe le tabelle.

Le seguenti clausole sono semanticamente identiche: a LEFT JOIN b USING (c1,c2,c3) a LEFT JOIN b ON a.c1=b.c1 AND a.c2=b.c2 AND a.c3=b.c3 NATURAL [LEFT] JOIN di due tabelle è semanticamente equivalente a

INNER JOIN o LEFT JOIN con la clausola USING che elenca i campi delle due tabelle.

RIGHT JOIN opera analogamente a LEFT JOIN. Si consiglia di usare LEFT

JOIN invece di RIGHT JOIN, per compatibilità con altri sistemi. STRAIGHT_JOIN è identica alla JOIN, ad eccezione che le tabella sinistra

è sempre letta prima della tabella destra. Questo può essere utile nei casi in cui l’ottimizzatore posiziona le tabelle nell’ordine sbagliato.

In una query possono essere specificate delle regole (hints) relativamente agli indici da usare durante l’esecuzione di una query.

Specificando USE INDEX (key_list), si può dire al MySQL di usare solamente gli indici specificati in key_list per trovare le righe nella tabella.

La sintassi alternativa IGNORE INDEX (key_list) può essere usata per dire al MySQL di non usare un particolare indice.

Queste regole sono utili se il comando EXPLAIN mostra che MySQL usa in maniera non ottimale gli indici.

Si può usare FORCE INDEX. Opera in modo simile a USE INDEX (key_list) ma in più assume che un full table scan è una operazione molto dispendiosa.

USE KEY, IGNORE KEY, e FORCE KEY sono sinonimi di USE INDEX, IGNORE INDEX, and FORCE INDEX.

Sottoquery

“Quali impiegati hanno un salario maggiore del salario di ‘Jones’?”

Query Principale

?

“Quale e’ il salario di ‘Jones’?”

? Sottoquery

Sottoquery

◦ L’output di una sottoquery viene dato in input alla query principale.

SELECT select_expr FROM table_references WHERE expr operator (SELECT select_expr FROM table_references);

2975

mysql> SELECT ename -> FROM emp -> WHERE sal > -> (SELECT sal -> FROM emp -> WHERE empno=7566);

Uso di Sottoquery

Tipi di Sottoquery ◦ sottoquery su riga singola

Query principale

Sottoquery ritorna

Singolo valore

• subquery su riga Multipla

Un elenco di valori riferiti ad un campo

Query principale

Sottoquery ritorna

• Sottoquery su colonna Multipla

Elenco di valori

Riferiti a diverisi campi

Query principale

Sottoquery ritorna

Sottoquery su righe multiple ◦ Restituiscono più di una riga ◦ Vanno usate con operatori di riga multipla

Operatore

IN

ANY/SOME

ALL

Significato

Uguale ad un elemento della lista

Vero se almeno uno degli elementi soddisfa la condizione

Confronta il valore con tutti gli elemeti restituiti dalla sottoquery

Esempi Che tipi di negozi sono presenti in una o più città?

SELECT DISTINCT store_type FROM Stores WHERE EXISTS (SELECT * FROM Cities_Stores WHERE Cities_Stores.store_type = Stores.store_type);

Quali tipi di negozi non sono presenti nelle città?

SELECT DISTINCT store_type FROM Stores WHERE NOT EXISTS (SELECT * FROM Cities_Stores WHERE

Cities_Stores.store_type = Stores.store_type);

Esempi Quali tipi di negozi sono presenti in TUTTE le città?

SELECT DISTINCT store_type FROM Stores WHERE NOT EXISTS ( SELECT * FROM Cities WHERE NOT EXISTS ( SELECT * FROM Cities_Stores WHERE Cities_Stores.city = Cities.city AND Cities_Stores.store_type = Stores.store_type));

Doppio NOT EXISTS. Ha una clausola NOT EXISTS dentro una clausola NOT EXISTS.

Nell’output non esiste una città che non ha un tipo di negozio (store) dei tipi elencati.

Sottoquery a Colonne multiple

Visualizzare: id ordine, id prodotto, e quantità nella tabella item dove id prodotto e quantità coincidono entrambi con id prodotto e quantità di un elemento nell’ordine 605.

mysql> SELECT ordid, prodid, qty -> FROM item -> WHERE (prodid, qty) IN -> (SELECT prodid, qty -> FROM item -> WHERE ordid = 605) -> AND ordid <> 605;

mysql> SELECT a.ename, a.sal, a.deptno, b.salavg -> FROM emp a, (SELECT deptno, avg(sal) salavg -> FROM emp -> GROUP BY deptno) b -> WHERE a.deptno = b.deptno -> AND a.sal > b.salavg;

Uso di una sottoquery nella clausola FROM

Funzioni SQL

Funzioni Input

arg 1

arg 2

arg n

Elaborazione

Output

Valori dei Risultati

Due Tipi di Funzioni SQL

Funzioni

Funzioni Single-row

Funzioni Multiple-row

Funzioni

Conversione

Stringhe

Numeri

Date

Generali Funzioni

(Single-Row)

Operatori di confronto = , <> != , <= < >= , > <=> (NULL-safe), IS NULL, IS NOT NULL expr BETWEEN min AND max expr NOT BETWEEN min AND max

◦ Equivalente a NOT (expr BETWEEN min AND max). expr IN (value,...) expr NOT IN (value,...)

◦ Equivalente a NOT (expr IN (value,...)). ISNULL(expr) COALESCE(list)

◦ Ritorna il primo elemento non-NULL nella lista: mysql> SELECT COALESCE(NULL,1); -> 1 mysql> SELECT COALESCE(NULL,NULL,NULL); -> NULL

INTERVAL(N,N1,N2,N3,...)

◦ Ritorna 0 se N < N1, 1 se N < N2 ecc.o -1 se N è NULL. N1 < N2 < N3 < ... < Nn

mysql> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200); -> 3 mysql> SELECT INTERVAL(10, 1, 10, 100, 1000); -> 2 mysql> SELECT INTERVAL(22, 23, 30, 44, 200); -> 0

Controllo di flusso CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE

result] END CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END

mysql> SELECT CASE 1 WHEN 1 THEN "one" WHEN 2 THEN "two" ELSE "more" END; -> "one"

mysql> SELECT CASE WHEN 1>0 THEN "true" ELSE "false" END; -> "true"

mysql> SELECT CASE BINARY "B" WHEN "a" THEN 1 WHEN "b" THEN 2 END;

-> NULL

IF(expr1,expr2,expr3)

mysql> SELECT IF(1>2,2,3); -> 3

mysql> SELECT IF(1<2,'yes','no'); -> 'yes'

mysql> SELECT IF(STRCMP('test','test1'),'no','yes'); -> 'no'

Altre funzioni ◦ IFNULL(expr1,expr2) ◦ NULLIF(expr1,expr2)

Funzioni su Stringhe

Funzioni su stringhe

LOWER UPPER INITCAP

CONCAT SUBSTR LENGTH INSTR LPAD TRIM RIGHT

Funzioni Conversioni Case

Funzioni di Manipolazione Stringhe

Funzioni di manipolazione stringhe

ASCII(str) BIN(N) BIT_LENGTH(str) CHAR(N,...) CHAR_LENGTH(str) CHARACTER_LENGTH(str) ◦ CHARACTER_LENGTH() sinonimo di CHAR_LENGTH().

COMPRESS(string_to_compress)

CONCAT(str1,str2,...) ◦ Ritorna la stringa risultato della concatenazione delle stringhe contenute come argomento. Restituisce NULL se uno

qualisasi degli argomenti è NULL.

mysql> SELECT CONCAT(‘apulvirenti', ‘@', ‘dmi.unict.it'); -> ‘apulvirenti@dmi.unict.it'

mysql> SELECT CONCAT('My', NULL, 'QL'); -> NULL mysql> SELECT CONCAT(14.3); -> '14.3'

CONCAT_WS(separator, str1, str2,...) CONV(N,from_base,to_base)

◦ Converte numeri tra due differenti basi. Ritorna NULL se uno dei due argomenti è NULL.

mysql> SELECT CONV("a",16,2); -> '1010' mysql> SELECT CONV("6E",18,8); -> '172' mysql> SELECT CONV(-17,10,-18); -> '-H' mysql> SELECT CONV(10+"10"+'10'+0xa,10,10); -> '40'

ELT(N,str1,str2,str3,...)

◦ Ritorna str1 se N = 1, str2 if N = 2, ecc. Ritorna NULL se N è minore di 1 o maggiore del numero di argomenti.

mysql> SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo'); -> 'ej' mysql> SELECT ELT(4, 'ej', 'Heja', 'hej', 'foo'); -> 'foo'

FIELD(str,str1,str2,str3,...) ◦ Complementare di ELT(). Ritorna l’indice della stringa str in str1, str2,ecc.

Ritorna 0 se non viene trovata. mysql> SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');

-> 2 mysql> SELECT FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo');

-> 0

FIND_IN_SET(str,strlist) HEX(N_or_S)

INSERT(str,pos,len,newstr) mysql> SELECT INSERT('Quadratic', 3, 4, 'What'); -> 'QuWhattic'

INSTR(str,substr) mysql> SELECT INSTR('foobarbar', 'bar'); -> 4 mysql> SELECT INSTR('xbar', 'foobar'); -> 0

LCASE(str) – UCASE(str) ◦ Sinonimo di LOWER() – UPPER().

LEFT(str,len) mysql> SELECT LEFT('foobarbar', 5); -> 'fooba'

LENGTH(str) mysql> SELECT LENGTH('text'); -> 4

Funzioni matematiche

ABS(X) FLOOR(X), CEILING(X) SIN(X), COS(X)… LN(X), LOG(X), LOG(B,X) Ecc.

Operatori logici NOT (! ) AND (&& ) OR (|| ) XOR

View

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

[DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

DBMS attivi vs passivi I DBMS tradizionali sono passivi

Eseguono esclusivamente operazioni su richiesta

Spesso si ha la necessità di avere capacità reattive: il DBMS reagisce autonomamente ad alcuni eventi ed esegue determinate operazioni

In questo ultimo caso parleremo di DBMS attivi (ADBMS), per cui è possibile definire regole attive o trigger

62

Funzioni (stored function) Restituiscono un singolo valore, oltre ad accettare parametri di ingresso e di uscita

Procedure (stored procedure)

Non restituiscono valore, ma accettano parametri di ingresso e di uscita

Trigger Sono attivati da eventi

Il paradigma ECA…

Il paradigma più noto per la definizione dei trigger è quello: Evento-Condizione-Azione (ECA)

Evento: ◦ se si verifica provoca l’attivazione del trigger

Condizione: ◦ se è soddisfatta, l’azione del trigger è eseguita

Azione: ◦ sequenza di operazioni che può anche modificare la base di dati,

viene eseguita solo se la condizione è vera

63

…il paradigma ECA La forma più comune di trigger è quindi:

ON evento IF condizione THEN azione 1. se si verifica l’evento, la condizione è valutata 2. se la condizione è soddisfatta l’azione viene

eseguita

Le regole attive hanno origine dalle regole dell’ Intelligenza Artificiale Tali regole normalmente non hanno eventi,

sono della forma (CA):

IF condizione THEN azione

64

Granularità del processo reattivo Due modalità: ◦ orientata all’istanza (instance oriented): la regola

attivata è eseguita (azione) per ogni elemento della base di dati che attiva la regola e soddisfa la condizione operano a livello di tupla

◦ orientata all’insieme (set oriented): la regola è eseguita una volta per l’insieme di tali elementi operano a livello di statement

possono esserci differenze nel risultato 65

Esecuzione delle regole Esempio: relazione Impiegati regola R: ◦ azione = sostituire il valore dell’attributo Stipendio delle tuple

inserite con il valore medio + 5 di Stipendio calcolato su tutte le tuple della relazione Impiegati

esecuzione orientata all’insieme: tutti gli impiegati appena inseriti avranno lo stesso valore per l’attributo Stipendio

esecuzione orientata all’istanza: gli impiegati appena inseriti avranno valori di Stipendio diversi 66

Tipi di trigger

Before : action da eseguire prima dell’evento

After : action da eseguire dopo l’evento Instead of : action da eseguire al posto

dell’evento Non implementato in MySQL 5.x

In base all’evento innescante avremo quindi diverse combinazioni: Before insert Before update Before delete 67

Stato nei trigger Per ogni trigger possono venire adoperate delle tabelle di transizione atte a

memorizzare lo stato precedente e successivo del target del trigger.

per il delete trigger è prevista una tabella di transizione detta old transition table per l’ insert trigger una tabella detta new

transition table per l’ update trigger sono previste

entrambe le tabelle 68

Definizione di un trigger…

Sintassi:

Esempio:

69

…definizione di un trigger su ognuna delle righe da modificare viene

controllato il valore che sta per essere assegnato al campo amount, per verificare che sia compreso fra 0 e 100; in caso contrario viene riportato entro tali limiti.

NEW : si riferisce al valore aggiornato OLD : si riferisce al valore precedente la

modifica

Per lavorare sui trigger è attualmente necessario il privilegio SUPER.

70

Utilizzi di NEW e di OLD In un INSERT trigger, può essere usata

solo la variabile NEW.

In un DELETE trigger, può essere usata solo la variabile OLD.

In un BEFORE trigger, si può usare il

comando SET NEW.column = value, per la modifica o l’inserimento di un nuovo valore all’interno della tupla. 71

Gestione trigger SHOW TRIGGERS

Visualizza le informazioni relative ai trigger associati al database

DROP TRIGGER

[databaseName.]triggerName Permette l’eliminazione di un trigger

I trigger, come le stored procedure vengono salvati in fase di dump della base di dati

72

Gestione degli errori Se un BEFORE trigger fallisce, l’operazione

che ha generato l’evento di attivazione, non viene eseguita;

Un AFTER trigger viene eseguito, se precedentemente sono stati eseguiti i BEFORE trigger e la primitiva di attivazione;

Un errore durante un BEFORE o un AFTER trigger, genera il fallimento dell’intera procedura che causa l’attivazione del trigger.

73

Limitazioni sui trigger

In MySql non possono essere creati due trigger

con clausola temporale e tipo evento uguali, su una stessa tabella target.

Esempio: non possono essere definiti due BEFORE INSERT trigger o due AFTER UPDATE trigger per una stessa tabella. ma un trigger before ed uno after sullo

stesso evento non danno eccezioni 74

caso di studio: audit log

Tenere traccia delle variazioni che si susseguono all’interno di una tavola

75

Element Description Timestamp transaction date and time

user@host transaction host and user

Operation transaction type Table table name Id row of table Column column name of table oldValue old value field (null for insert) newValue new value field (null for delete)

Tabella da monitorare

Tabella camere

monitorare le operazioni di INSERT, UPDATE e DELETE (necessari 3 trigger)

76

Trigger di controllo sulla UPDATE

Una tabella di log per ogni colonna monitorata

77

esercizio

Realizzare un audit log che conservi tutte la transazioni che avvengono su una tabella

Prevedere tabelle ad uso esclusivo del log

Monitorare le operazioni di insert, update, delete

Vantaggi e svantaggi della soluzione

78

Stored Routine… Una stored routine (SQL-invoked routines) è un programma scritto

generalmente in SQL mantenuto nel database

Esistono estensioni di SQL dedicate a questo scopo PL/pgSQL di PostgreSQL o il PL/SQL di Oracle

Generalmente il DBMS compila le stored procedure Alcuni DBMS consentono di criptare le stored procedure

http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.pdf 79

Funzioni (stored function) Restituiscono un singolo valore, oltre ad accettare parametri di ingresso e di uscita

Procedure (stored procedure)

Non restituiscono valore, ma accettano parametri di ingresso e di uscita

Trigger Sono attivati da eventi

…Stored Routine Un SR è, per impostazione predefinita,

creata nel DB corrente. Al fine di creare un SR in un altro DB il

nome del DB deve essere preposto al nome di routine.

Quando un DB è «droppato», tutte le stored routine ad esso associato vengono eliminate Allo stesso modo il dump del database è

comprensivo delle procedure in esso memorizzate 80

dichiarativo vs procedurale

SQL è un linguaggio dichiarativo Le stored procedure rappresentano una

sua estensione procedurale linguaggio dichiarativo o logico: le istruzioni descrivono le relazioni

che intercorrono tra i dati; lo sviluppatore in questo caso descrive l’insieme delle relazioni che sussistono tra i dati e il risultato atteso.

linguaggio procedurale: per esso non si ragiona in termini di relazioni tra dati ma in termini di assegnazione di valori ad uno spazio di memoria; i linguaggi procedurali fanno infatti parte della più ampia famiglia dei linguaggi imperativi.

81

Stored procedure

Un nome di una SP deve essere univoco, in caso contrario è necessario inserire uno spazio tra il nome e la parentesi al momento della definizione.

Un SP è memorizzata in una tabella speciale chiamata mysql.proc all'interno di un database

82

Vantaggi…

evita al client di riscrivere query complesse offrendo la possibilità di richiamare una procedura archiviata all'interno del database.

il numero di informazioni che saranno scambiate tra client e server sarà minore a tutto vantaggio delle prestazioni.

trattandosi di linguaggi strutturati, diventano possibili elaborazioni complesse non altrimenti realizzabili usando unicamente query SQL.

vengono compilate solo una volta nel momento in cui vengono inserite; da quel momento in poi esse potranno essere eseguite attraverso una semplice chiamata con non pochi vantaggi a livello di performances.

83

…vantaggi Una stored procedure è una libreria di funzioni sempre disponibile;

questo vuol dire che sarà possibile utilizzarla per interrogare strutture di dati anche molto complesse avendone soltanto una conoscenza parziale.

Se sono disponibili le stored procedures non sarà più necessario assegnare agli utenti i permessi per la lettura e la modifica delle tabelle, basterà concedere loro l’utilizzo delle routines.

Le stored procedures risolvono il problema della riscrittura delle interrogazioni; ciò implica un minore flusso di dati con il DBMS e un conseguente aumento delle risorse disponibili.

84

svantaggi

aumentano il carico di lavoro per il server.

A volte non si può utilizzare il linguaggio desiderato, perché il DBMS non lo supporta.

La logica del programma viene spostata sul server; questo non è necessariamente uno svantaggio, ma non è compatibile con il modello logico di applicativi a tre livelli.

85

Quando non usarle ?

Evitare di inglobare la logica dell'applicazione nel DBMS tramite stored procedure

le stored procedure non devono mai implementare funzionalità che non hanno direttamente a che fare con i dati 86

Quale versione ?

Le stored procedure sono disponibili sulla piattaforma MySQL a partire dalla versione 5.0.x in ritardo rispetto ai competitor

87

database di esempio

Creazione di un database di prova per testare le nostre stored procedure

Creazione di una tabella

Popolamento

88

I delimitatori

carattere o stringa di caratteri utilizzata per informare il client mysql che l’istruzione SQL è completa e quindi può essere inoltrata al dbms.

Per anni è stato utilizzato il punto e virgola Ma in una stored procedure si possono

avere molte dichiarazioni, ed ognuna di esse deve terminare con un punto e virgola.

Solitamente si usa //

89

Un esempio di procedure

Procedura che esegue una select create procedure: istruzione di creazione

procedura p1(): nome della procedura (non è case

sensitive) select * from tabella: codice SQL da

eseguire all’atto della invocazione della procedura

90

Istruzioni non ammesse CREATE PROCEDURE ALTER PROCEDURE DROP PROCEDURE CREATE FUNCTION DROP FUNCTION CREATE TRIGGER DROP TRIGGER

91

Dov’è l’errore ?

Invocare una procedura

Si usa la clausola CALL seguita dal nome della procedura con gli eventuali argomenti

Eseguire p1() equivale ad eseguire la select

92

Uso di più statements

Uso di BEGIN/END per definire PROCEDURE composte da più statements

93

Gestione stored procedure

Visualizzare procedure memorizzate nel dbms Varie info: utente@host, timestamp…

Eliminare una procedura Senza argomenti, il nome è univoco

94

Variabili in MYSQL Variabili locali

visibilità all’interno del blocco nel quale sono dichiarate

Variabili di sessione visibilità nell’ambito della sessione con il

server Si antepone @ al nome della variabile

Variabili globali condivisibili fra più connessioni Usano la keyword GLOBAL SET GLOBAL max_connections = 300; 95

La clausola DECLARE

Utilizzata per definire variabili in statement composti

96

Variabile locale Non sono state definite variabili nella

stored procedure Sono state definite nel blocco BEGIN/END

Non sono come le variabili di sessione Infatti non iniziano con il simbolo @

La differenza tra una variabile locale ed una variabile di sessione è che la prima viene reinizializzata a NULL ogni volta che viene chiamata la procedura, a differenza della variabile di sessione. 97

IF-THEN-ELSE

Il ramo eseguito dipende dal paramento passato in input

98

CASE

Oltre all’ IF, c'è un altro modo per verificare le condizioni e prendere percorsi a seconda che le espressioni siano vere o meno.

99

WHILE

L’uscita dal ciclo avviene quando la variabile v è uguale a 5

Il conteggio si applica solo per l’ultima

INSERT. 100

REPEAT…UNTIL

Effettua 5 inserimenti

101

Eccezioni

Il corpo delle procedure se conforme alla sintassi SQL non restituisce errori in compilazione controllo semantica eseguito a runtime

Gli HANDLER hanno il compito di gestire il verificarsi di eventi non previsti

Che succede se la tabella «verifica» non esiste nel db?

102

Esempio : validazione dati utente

Le operazioni previste sono: Inserimento dati utente Validazione dati inseriti

Per queste due operazioni sono stati creati un trigger che a sua volta attiva una function.

103

Inserimento utente

Creazione tavola utenti

104

Validazione stringa inserita

Una function per la validazione

105

Trigger che attiva la validazione

Trigger di validazione

106

Risultato della validazione

Inserimenti eseguiti ad hoc ai fini di testing:

Risultato:

107

Any questions ?

108