MySQL: A tutorial - unisannio.it...La clausola USING (column_list) elenca una lista di colonne che...
Transcript of MySQL: A tutorial - unisannio.it...La clausola USING (column_list) elenca una lista di colonne che...
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'); -> ‘[email protected]'
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