Mysql

MYSQL 1. Introducere 1.1. Ce este mySQL? MySQL este unul dintre cele mai populare sisteme de gestiune de baze de date (SGBD) in regim OpenSource. Producatorul sau se numeste MySQL AB si este o companie a carei principala activitate consta in oferirea de servicii legate de acest SGBD. Cele mai importante caracteristici ale MySQL sunt: • • • •

Este un SGBD. Asigura gestiunea unei colectii structurate de date. Este un SGBD relational. Datele sunt organizate in tabele. Intre tabele sunt stabilite relatii si constrangeri de integritate. Pentru gestiunea datelor foloseste limbajul SQL (Structured Query Language) care este un standard in acest domeniu. Este un sistem client-server. Serverul de baze de date este separat logic si deseori fizic (pe calculatoare diferite) de programele client care asigura interfata operatiilor cu baza de date. Serverul MySQL suporta o paleta larga de programe client, scrise in diverse limbaje de programare.

2.1. Crearea unei baze de date Crearea unei baze de date se poate face simplu apeland comanda: mysql> CREATE DATABASE nume_db; Crearea unei baze de date corespunde crearii unui director in care vor fi pastrate elementele specifice cum ar fi: tabele, indecsi, jurnale etc. Daca deja exista o baza de date cu numele specificat se va da un mesaj de eroare. Pentru stergerea unei baze de date se poate utiliza sintaxa: mysql> DROP DATABASE nume_db; 2.2 Utilizarea unei baze de date Pentru selectarea unei baze de date se va folosi comanda USE. Prin aceasta se stabileste baza de date in contextul careia se vor executa interogarile ulterioare. Ea se va numi baza de date activa. Sintaxa acestei comenzi este: mysql> USE nume_db; Chiar daca o singura baza de date poate fi activa la un moment dat, o interogare poate referi tabele si din alte baze de date prin utilizarea prefixarii cu numele bazei de date respective (ex. db2.tabela5). Exemplu: mysql> USE mysql; mysql> SHOW TABLES; mysql> SELECT * FROM db; 2. Tipuri de date

1

2.1. Tipuri de date numerice

1. TINYINT [(M)] [UNSIGNED] [ZEROFILL] 2. 3. 4. 5. 6. 7. 8. 9. 10.

11.

Numar intreg foarte mic. Intervalul de valori posibile este [-128..127]. Daca se specifica UNSIGNED intervalul este [0..255]. SMALLINT [(M)] [UNSIGNED] [ZEROFILL] Numar intreg mic. Intervalul de valori posibile este [-32768..32767]. Daca se specifica UNSIGNED intervalul este [0...65535]. MEDIUMINT [(M)] [UNSIGNED] [ZEROFILL] Numar intreg mediu. Intervalul de valori posibile este [-8388608..8388607]. Daca se specifica UNSIGNED intervalul este [0..16777215]. INT [(M)] [UNSIGNED] [ZEROFILL] Numar intreg normal. Intervalul de valori posibile este [-2147483648..2147483647]. Daca se specifica UNSIGNED intervalul este [0..4294967295]. INTEGER [(M)] [UNSIGNED] [ZEROFILL] Sinonim cu INT. BIGINT [(M)] [UNSIGNED] [ZEROFILL] Numar intreg mare. Intervalul de valori posibile este [-9223372036854775808..9223372036854775807]! Daca se specifica UNSIGNED intervalul este [0..18446744073709551615]. FLOAT [(M, D) [ZEROFILL] Numar in virgula flotanta simpla precizie (numar real). Intervalul de valori posibile este [-3.402823466E+38.. -1.175494351E-38, 0, 1.175494351E-38..3.402823466E+38]. DOUBLE [(M, D) [ZEROFILL] Numar in virgula flotanta dubla precizie. Intervalul de valori posibile este [-1.7976931348623157E+308..2.2250738585072014E-308, 0, 2.2250738585072014E-308..1.7976931348623157E+308]. REAL [(M, D) [ZEROFILL] Sinonim cu DOUBLE. DECIMAL [(M [, D]) [ZEROFILL] Numar in virgula flotanta neinpachetat. Spre deosebire de tipurile precedente, la care valorile sunt stocate binar, acesta retine valorile numerice ca si siruri de cifre ASCII. Daca D lipseste, numarul va fi intreg. Daca si M lipseste, numarul va fi pe maxim 10 cifre zecimale. NUMERIC [(M [, D]) [ZEROFILL] Sinonim cu DECIMAL.

2.2. Tipuri de date data calendaristica si timp

1. DATE 2. 3.

4.

5.

Data calendaristica. Intervalul de valori posibile este ['1000-01-01'..'9999-12-31']. Stocarea/afisarea se face implicit in formatul 'YYYY-MM-DD' (an-luna-zi). DATETIME Combinatie de data calendaristica si timp. Intervalul de valori posibile este ['1000-01-01 00:00:00'..'9999-12-31 23:59:59']. Stocarea/afisarea se face implicit in formatul 'YYYY-MM-DD HH:MM:SS' (an-luna-zi ora:minut:secunda). TIMESTAMP [(M)] Moment de timp. Include si data calendaristica. Este util la inregistrarea efectuarii unor operatii gen inserare sau modificare pentru ca retine implicit data efectuarii ultimei operatii. Doar prima coloana de acest tip din tabela in care se face modificarea va fi actualizata automat! Intervalul de valori posibile este ['1970-01-01 00:00:00'..'2037-01-01 00:00:00']. MySQL afiseaza valorile TIMESTAMP in format YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD sau YYMMDD dupa cum M este 14 (sau lipseste), 12, 8 sau 6. Parametrul M nu influenteza insa reprezentarea interna a datelor la stocare. TIME Moment de timp. Intervalul de valori posibile este [-838:59:59'..'838:59:59']. Reprezentarea este in format 'HH:MM:SS' (ora:minut:secunda). Motivul pentru care ora nu este limitata la intervalul [0..24] esta ca acest tip poate fi folosit si pentru a retine intervale de timp intre doua evenimente. Tot din acest motiv ora poate fi si negativa. YEAR [(2|4)] An calendaristic pe 2 sau 4 cifre. Intervalul de valori posibile este [1901..2155] pentru 4 cifre, respectiv [1970..2069] pentru 2 cifre (70-99..00-69).

2.3. Tipuri de date sir de caractere

2

1. CHAR (M) [BINARY]

2.

3.

Sir de caractere de lungime fixa. Daca se memoreaza un sir mai scurt el va fi completat cu spatii la dreapta pana la lungimea M specificata. Lungimea M a sirului poate lua valori in intervalul [1..255]. Spatiile de la sfarsitul sirului sunt indepartate automat la interogarea campului. Daca nu se specifica optiunea BINARY, compararea valorilor se face fara a tine cont de felul literelor (mari/mici). BIT BOOL CHAR Sunt sinonime cu declaratia CHAR(1). VARCHAR (M) [BINARY] Sir de caractere de lungime variabila. La memorare, spatiile de la sfarsitul sirului sunt indepartate. Lungimea M a sirului poate lua valori in intervalul [1..255]. Daca nu se specifica optiunea BINARY, compararea valorilor se face fara a tine cont de felul literelor (mari/mici).

2.4. Tipuri de date binare mari (BLOB) Se folosesc pentru a retine obiecte binare (BLOB) de mari dimensiuni (ex. imagini, secvente audio sau video) sau pentru a retine texte de dimensiune in general mai mare de 255 caractere (TEXT). La comparatia campurilor tip TEXT nu se va tine cont de tipul caracterelor (mari/mici).

1. TINYBLOB 2. 3. 4.

TINYTEXT Valori BLOB, respectiv TEXT cu lungime de max. 255 elemente. BLOB TEXT Valori BLOB, respectiv TEXT cu lungime de max. 65535 (64 KB) elemente. MEDIUMBLOB MEDIUMTEXT Valori BLOB, respectiv TEXT cu lungime de max. 16777215 (16 MB) elemente. LONGBLOB LONGTEXT Valori BLOB, respectiv TEXT cu lungime de max. 4294967295 (4 TB) elemente.

2.5. Tipuri de date speciale

1. ENUM ('value1','value2',...)

2.

Enumerare de elemente tip sir de caracter. Un obiect de acest tip poate avea la un moment dat o singura valoare dintre cele enumerate sau valoarea NULL. Valoarea " " (sir vid) este considerata valoare de eroare. Un tip enumerare poate defini maxim 65535 de valori distincte. SET ('value1','value2',...) Multime de elemente tip sir de caracter. Un obiect de acest tip poate contine la un moment dat mai multe valori distincte dintre cele definite sau poate fi gol (nu contine nici o valoare). Un tip multime poate defini maxim 65 de valori membru.

3. Constante, identificatori, comentarii 3.1. Constante •

Constantele tip sir de caractere se includ intre apostroafe sau ghilimele: 'constnta sir' "alta constanta sir" In interiorul unui sir se pot include caractere speciale precedate de semnul '\' (escape): o \n Trecere la linie noua (new line). o \t Tab (caracter de aliniere). o \r Salt la inceputul liniei (carriage return). o \b Sterge inapoi. o \' Caracterul '. o \" Caracterul ".

3

o o o

\\ Caracterul \. \% Caracterul '%'. Se poate folosi pentru a cauta caracterul %, in conditiile in care acest caracter este folosit in expresii regulate (subiectul va fi detaliat ulterior). \_ Caracterul _. Se poate folosi pentru a cauta caracterul _, in conditiile in care acest caracter este folosit in expresii regulate (subiectul va fi detaliat ulterior).

Rulati secventele:

Activitate 1

•

•

mysql> SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello' ; mysql> SELECT "hello", "'hello'", "''hello''", "hel""lo", "\"hello" ;

Constantele intregi sunt reprezentate printr-un sir de cifre zecimale. Constantele reale contin semnul '.' pentru specificarea partii zecimale. Ambele tipuri pot fi precedate de semnul '-' pentru a indica numere negative. Ex: 13534 342.3453 Constanta NULL este folosita cu semnificatia "nici o valoare". Se face diferenta intre NULL si numarul 0 sau sirul vid "".

3.2. Identificatori Numele pentru baza de date, tabele, coloane, indecsi sau alias-uri trebuie sa indeplineasca conditiile normale pentru identificatori in limabje de programare: sa fie o combinatie de litere, cifre si semne grafice care incep cu o litera. In plus mySQL impune: Identificator Lungime maxima Caractere interzise Baza de date 64

'/'

Tabela

64

'/' si '.'

Coloana

64

Alias

255

Referirea la o coloana in cadrul unei interogari se poate face in mai multe moduri, functie de contextul existent: Referinta la coloana

Semnificatie

nume_coloana

Referinta la coloana unei tabele din baza de date activa. Se poate folosi daca interogarea nu contine si alte tabele care au coloane cu nume identice

nume_tabela.nume_coloana

Referinta explicita la coloana tabelei specificate prin nume_tabela din baza de date activa.

nume_BD.nume_tabela.nume_coloana

Referinta explicita la coloana tabelei specificate prin nume_tabela din baza de date specificata prin nume_BD (nu este necesar sa fie cea activa).

Identificatorii pot fi scrisi in general cu orice combinatie de litere mari sau mici. Pentru numele bazei de date si cel al tabelelor unele, implementari impun respectarea tipului de caractere folosit la definirea elementelor respective. Ca si regula generala, este bine sa se pastreze o conventie de notare care sa nu fie schimbata de la o interogare la alta. O regula simpla este sa se scrie comenzile SQL cu litere mari si identificatorii definiti de utilizator cu litere mici. 3.3. Comentarii MySQL suporta trei tipuri de comentarii prin care se poate explica codul. Ele sunt: • • •

Comentarii pe o singura linie care incep cu # si continua pana la sfarsitul liniei; Comentarii pe o singura linie care incep cu -- si continua pana la sfarsitul liniei. Este necesar cel putin un spatiu dupa semnul --; Comentarii pe mai multe linii care incep cu /* si se incheie cu */ ;

4

Rulati secventele:

Activitate 2

mysql> SELECT 1+1; # Acest comentariu continua pana la sfarsitul liniei mysql> SELECT 1+1; -- Acest comentariu continua pana la sfarsitul liniei mysql> SELECT 1 /* Acesta este un comentariu imbricat */ + 1; mysql> SELECT 1+ > /* > Acesta este > un comentariu multi-linie > */ > 1;

Operatori si functii mySQL 3.5. Paranteze de grupare Sunt folosite pentru a schimba ordinea de evaluare in interiorul expresiilor. Parantezele folosite sunt doar paranteze rotunde. Rulati secventele:

Activitate 3

mysql> SELECT 1+2*3; mysql> SELECT (1+2)*3;

3.6. Operatori aritmetici Operatorii aritmetici opereaza asupra numerelor intregi cu precizia BIGINT (pe 64 biti). Sunt disponibili urmatorii operatori: • • • •

+ pentru adunare; - pentru scadere; * pentru inmultire; / pentru impartire; daca se imparte la 0 rezultatul va fi NULL; Rulati secventele:

Activitate 4

mysql> SELECT 3+5, 3-5; mysql> SELECT 18014398509481984*18014398509481984.0; mysql> SELECT 18014398509481984*18014398509481984; /* depasire (numar intreg > 64 bit) */ mysql> SELECT 3/5; mysql> SELECT 5/(3-3);

3.7. Operatori logici Operatorii logici intorc 1 pentru adevarat si 0 pentru fals. • • •

NOT ! - Negatie logica. Intoarce 1 daca argumentul este 0, altfel intoarce 0. Exceptie: NOT NULL intoarce NULL. OR || - SAU logic. Intoarce 1 daca cel putin un argument nu este 0 sau NULL. AND && - SI logic. Intoarce 0 sau NULL daca cel putin un argument este 0 sau NULL, altfel intoarce 1. Rulati secventele:

Activitate 5

mysql> SELECT NOT 1, NOT NULL, ! (1+1), ! 1+1 ; mysql> SELECT 1 OR 0, 0 OR (1-1), 1 || NULL ; mysql> SELECT 1 && NULL, 1 && 0, 1 AND (0+1) ;

3.8. Operatori de comparare

5

Operatorii de comparare returneaza 1 pentru adevarat, 0 pentru fals si NULL daca nu se poate efectua comparatia. Ei pot compara atat numere cat si siruri de caractere. La compararea unui numar cu un sir se incearca transformarea sirului in numarul pe care il contine. La compararea a doua siruri nu se va tine cont de litere mari/mici. • • • • • • • • • • • • • •

= egalitate; la compararea cu NULL intoarce NULL; <> sau != inegalitate; < - mai mic; <= - mai mic sau egal; > - mai mare; >= - mai mare sau egal; <=> - echivalenta; la compararea cu NULL intoarce 0 sau 1 (1 doar la NULL <=> NULL); daca ambii operanzi sunt diferiti de NULLse comporta ca si =; IS NULL - testeaza daca operandul are valoarea NULL; IS NOT NULL - testeaza daca operandul este diferit de NULL; ISNULL(expr) - testeaza daca expresia are valoarea NULL; expr BETWEEN min AND max - testeaza daca valoarea expresiei este in intervalul [min..max]; expr IN (value,...) - testeaza daca valoarea expresiei este in lista de valori specificata; expr NOT IN (value,...) - testeaza daca valoarea expresiei nu este in lista de valori specificata; IF(expr1,expr2,expr3) - testeaza valoarea de adevar a expr1 (falsa daca este 0 sau NULL) si intoarce expr2 pentru adevarat respectiv expr3 pentru fals; Rulati secventele:

Activitate 6

mysql> SELECT 1 = 0, '0.0' = 0, '.01' = 0.01 ; mysql> SELECT .01 <> '0.01', 'abc' <> 'abcd' , 'abc' <> 'ABC' ; mysql> SELECT 0.1 <= 2, 2 >= 2, 5 > '3' ; mysql> SELECT 0 <=> NULL, NULL = NULL, NULL <=> NULL ; mysql> SELECT 0 IS NULL, NULL IS NULL, 0 IS NOT NULL ; mysql> SELECT 1 BETWEEN 2 AND 3, 'b' BETWEEN 'a' AND 'c', 2 BETWEEN 2 AND '3' ; mysql> SELECT 2 IN (0,3,5,'abc') , 'abc' IN (0,3,5,'Abc') ; mysql> SELECT IF(1>2,2,3), IF(1<2,'da','nu') ;

3.9. Functii pe siruri de caractere Functiile pe siruri considera implicit primul caracter pe pozitia 1. • • • • • • • • • • • • •

ASCII(str) - intoarce codul ASCII al caracterului de pe pozitia 1 din sir. Daca sirul este vid intoarce 0. CONV(N, from_base, to_base) - converteste numarul N considerat in baza from_base in valoarea sa in baza to_base. CHAR(N,...) - converteste sirul de numere primite intr-un sir de caractere ale caror coduri ASCII sunt egale cu cele din sirul initial. CONCAT(str1,str2,...) - concateneaza sirurile primite ca argument, returnand sirul rezultat. LENGTH(str) - intoarce lungimea (numarul de caractere) a sirului primit ca si argument. LOCATE(substr,str) POSITION(substr IN str) - cauta prima aparitie a sirului substr in sirul str. Daca il gaseste returneaza pozitia, daca nu, returneaza 0. LEFT(str,len) - intoarce un sir format din primele len caractere din sirul dat. RIGHT(str,len) - intoarce un sir format din ultimele len caractere din sirul dat. SUBSTRING(str,pos,len) MID(str,pos,len) - intorc subsirul de pe pozitia pos, de lungime len caractere din sirul str. SUBSTRING_INDEX(str,delim,count) - intoarce subsirul pana la count aparitii ale caracterului delimitator delim. Daca delim este pozitiv intoarce prima parte a sirului, daca este negativ, ultima parte. LTRIM(str) RTRIM(str) TRIM(str) - intoarce sirul str fara spatiile de la inceput (LTRIM), sfarsit (RTRIM) sau din ambele parti (TRIM). SPACE(N) - intoarce un sir format din N spatii. LOWER(str) UPPER(str) - intorc sirul str cu toate caracterele convertite la litere mici, respectiv litere mari.

6

•

sir (NOT) LIKE tipar - compara sirul sir cu tiparul dat. Intoarce adevarat (1) daca se potrivesc. Tiparele pot include caracterele speciale: '_' care se potriveste cu orice caracter si '%' care poate inlocui orice secventa de 0 sau mai multe caractere. Ex. 'ana' LIKE 'an_', 'ana' LIKE 'a%', 'ana' NOT LIKE 'a%z'. Rulati secventele:

Activitate 7

mysql> SELECT ASCII('0'), CONV("125",10,2), CHAR(77,121) ; mysql> SELECT CONCAT('My', 'S', 'QL'), LENGTH('text'), LOCATE('me', 'Ana are MERE') ; mysql> SELECT LEFT('MySQL', 2), RIGHT('MySQL', 3), SUBSTRING('Pasaport',4,5) ; mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2), CONCAT('A', LTRIM(' na ')) ; mysql> SELECT CONCAT('a',SPACE(6),'b'), LOWER('Ana are MERE'), UPPER('Ana are MERE') ; mysql> SELECT 'ana' LIKE 'An_', 'ana' LIKE 'a%', 'ana' LIKE 'a%z' ;

3.10. Functii matematice Toate functiile matematice intorc NULL in caz de eroare. • • • • • • • • • • • • • • • •

ABS(X) - valoarea absoluta (fara semn) a lui x. SIGN(X) - testeaza semnul lui x, intoarce -1 pentru x negativ, 1 pentru x pozitiv si 0 in caz contrar MOD(N,M) % - modulo (restul impartirii) lui N la M. FLOOR(X) - cea mai mare valoare intreaga mai mica decat x (rotunjire in jos) CEILING(X) - cea mai mica valoare intreaga mai mare decat x (rotunjire in sus) ROUND(X) - rotunjire la cel mai apropiat intreg EXP(X) - exponentiala (e la puterea x) LOG(X) - logaritm natural din x LOG10(X) - logaritm in baza 10 din x POW(X,Y) - x la puterea y SQRT(X) - radical de ordinul 2 din x PI() - numarul PI; implicit se afiseaza cu 5 zecimale dar in calcule intervine cu dubla precizie COS(X), SIN(X),TAN(X) - cosinus, sinus si tangenta de x RAND() RAND(N) - genereaza un numar pseudo-aleator in intervalul [0..1]; prin N se poate specifica o valoare de initializare calcul LEAST(X,Y,...) - intoarce cea mai mica valoare din lista valorilor specificate GREATEST(X,Y,...) - intoarce cea mai mare valoare din lista valorilor specificate Rulati secventele:

Activitate 8

mysql> SELECT ABS(-32), SIGN(-32), MOD(29,10) ; mysql> SELECT FLOOR(1.23), CEILING(1.23), ROUND(1.58) ; mysql> SELECT EXP(3), LOG(3), LOG10(100) ; mysql> SELECT POW(2,5), SQRT(16), PI() ; mysql> SELECT COS(PI()), RAND(20), RAND(20), RAND() ; mysql> SELECT LEAST(2,0), LEAST("B","A","C"), GREATEST(2,0) ;

3.11. Functii de tip data calendaristica si timp • • • • • • • • • •

DAYOFWEEK(date) - indexul zilei din spatamana al datei specificate (1=duminica, 2=luni etc.) DAYOFYEAR(date) - numarul zilei din an pentru data specificata DAYNAME(date) - numele (in engleza) al zilei din data specificata MONTHNAME(date) - numele (in engleza) al lunii din data specificata YEAR(date) - extrage anul din data specificata HOUR(time) - extrage ora din timpul precizat MINUTE(time) - extrage minutul din timpul precizat SECOND(time) - extrage secunda din timpul precizat CURDATE() - intoarce data curenta in format 'YYYY-MM-DD' CURTIME() - intoarce ora curenta in format 'HH:MM:SS'

7

• • •

NOW() SYSDATE() - intoarce data si ora curenta in format 'YYYY-MM-DD HH:MM:SS' SEC_TO_TIME(seconds) - intoarce timpul in format 'HH:MM:SS' reprezentat de numarul de secunde specificate TIME_TO_SEC(time) -converteste timpul specificat in numar de secunde scurse de la ora 00:00:00 Rulati secventele:

Activitate 9

mysql> SELECT NOW(), DAYOFWEEK(NOW()), DAYOFYEAR(NOW()) ; mysql> SELECT DAYNAME(NOW()), MONTHNAME(NOW()), YEAR(NOW()) ; mysql> SELECT CURTIME(), HOUR(CURTIME()), MINUTE(CURTIME()) ; mysql> SELECT SEC_TO_TIME(66), TIME_TO_SEC("00:01:06") ;

3.12. Functii speciale • • • • •

DATABASE() - numele bazei de date active USER() - numele utilizatorului coectat la server VERSION() - returneaza versiunea serverului MySQL instalat PASSWORD(str) - criptare sir dupa metoda interna prin care MySQL isi pastreaza parolele; criptarea nu este reversibila (nu se poate calcula sirul de plecare pornind de la cheia criptata) FORMAT(N,D) - returneaza numarul N in format '#,###,###.##' cu D zecimale Rulati secventele:

Activitate 10

mysql> SELECT DATABASE(), USER(), VERSION() ; mysql> SELECT FORMAT(345674355.35,3), PASSWORD("parola") ;

4. Crearea si stergerea tabelelor Datele rezultate din procesul de analiza a problemei ce trebuie implementata se impart in tabele prin procesul de normalizare. In acest fel o baza de date va fi formata dintr-o colectie de tabele. Pentru a crea o tabela se va utiliza comanda SQL CREATE TABLE. CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] • • •

•

TEMPORARY - specifica crearea unei tabele temporare care va fi stearsa automat la inchiderea conexiunii in care a fost creata; doua conexiuni pot crea doua tabele temporare cu aceleasi nume, ele nu vor interfera. IF NOT EXISTS - inhiba mesajul de eroare care se genereaza daca la incercarea de a crea o tabela care mai exista. create_definition - pentru definirea coloanelor este un sir de tipul: col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [PRIMARY KEY] o col_name - nume coloana o type - tip coloana (vezi sectiunea 1) o NOT NULL - nu se permit valori NULL in acest camp (campul trebuie completat in orice situatie) o NULL - permite valori NULL in camp (optiune implicita daca nu se specifica nimic) o DEFAULT default_value - completeaza campul lasat liber cu valoarea implicita default_value o AUTO_INCREMENT - daca nu se insereaza nimic in camp, se va genera automat o valoare mai mare cu o unitate fata de cea mai mare valoare din acel camp. Poate fi specificata o singura coloana cu acest parametru intr-o tabela. Coloanele specificate astfel trebuie indexate. o PRIMARY KEY - specifica cheia primara pentru tabela. Un singur camp din tabela poate avea acest parametru. Coloana declarata ca si cheie primara este indexata automat. Daca tabela are cheia primara formata din mai multe campuri, aceasta va trebui specificata separat, pe post de camp suplimentar, cu sintaxa "primary key (camp1, camp2 ...)". create_definition - pentru definirea indexilor are forma: INDEX (coloana1,...)

Pentru a sterge o tabela se va folosi comanda: DROP TABLE [IF EXISTS] tbl_name

8

Parametrul IF EXISTS va inhiba mesajul de eroare care apare daca tabela nu exista. Selectati cu ajutorul comenzii USE baza de date BANCA creata la Tema 2.2. Creati o tabela Deponent ruland comanda:

Activitate 11

mysql> CREATE TABLE Deponent ( mysql> nr_buletin char(8) NOT NULL PRIMARY KEY, mysql> cnp char(13) NOT NULL, mysql> nume char(24) NOT NULL, mysql> prenume char(24) NOT NULL, mysql> datan date, mysql> oras char(24) DEFAULT 'Timisoara', mysql> adresa char(32), mysql> telefon char(9), mysql> index (cnp), index (nume) mysql> ); Verificati corectitudinea tabelei create cu ajutorul comenzilor: mysql> SHOW COLUMNS FROM Deponent ; mysql> SHOW INDEX FROM Deponent ;

5. Inserarea datelor in tabele 5.1. Comanda INSERT

Comanda INSERT permite inserarea (adaugarea) de noi inregistrari (randuri) intr-o tabela. Deoarece sistemul mySQL este pur relational, nu exista nici o diferenta intre inserarea de noi date sau adaugarea lor. In ambele situatii, locul in care se face adaugarea nu este precizat, nefiind relevant. La sistemele care nu sunt pur relationale (cum este dBase sau FoxPro) operatia de adaugare semnifica adaugarea la sfarsitul unei tabele, pe cand insertia inseamna inserarea intre alte doua inregistrari existente. Comanda INSERT din mySQL are sintaxa de baza: INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_nume [(col_nume,...)] VALUES (expresie,...),(...),... Parametrii au urmatoarea semnificatie: • •

•

• • • •

LOW_PRIORITY - se foloseste pentru a intarzia scrierea efectiva a datelor in tabela pana cand alti utilizatori nu mai citesc date din tabela. Acest lucru avantajeaza citirea dar intarzie, semnificativ pentru o tabela foarte utilizata, scrierea. Efectul este blocarea executiei pana cand se reuseste scrierea efectiva. DELAYED - are actiune opusa parametrului precedent. Inregistrarea care trebuie adaugata este pusa intr-o coada de asteptare pe server si controlul revine la client, ca si cum scrierea ar fi fost facuta efectiv. Avantajul consta in timpul mic de executie a comenzii INSERT pe o tabela utilizata intensiv. Dezavantajul consta in incarcarea serverului cu o operatie in plus si in pierderea datelor daca serverul se opreste neasteptat. Din aceasta cauza parametrul este folosit doar cand este cu adevarat necesar. IGNORE - este util daca se insereaza mai multe inregistrari simultan (in acest caz lista de dupa VALUES va contine mai multe seturi de date in paranteze, departite prin virgula). In mod obisnuit, MySQL raporteaza eroare si nu executa comanda care incearca adaugarea a cel putin unei inregistrari care duplica cheia primara sau campuri cu valori unice. Prin folosirea parametrului IGNORE inregistrarile gresite sunt ignorate, dar restul sunt adaugate fara raportarea unei erori. Dupa executia comenzii se va raporta doar numarul de inregistrari a caror adaugare a reusit. INTO - este optional, se foloseste pentru compatibilitate cu alte sisteme SQL. Datorita claritatii mai mari a interogarii se prefera totusi utilizarea lui. tbl_nume - specifica numele tabelei unde se adauga inregistrarile. (col_nume,...) - este o lista de coloane care specifica ordinea in care se vor adauga datele in tabela. Prin aceasta se pot insera doar datele considerate esentiale, restul primind valorile implicite specificate la declararea tabelei. Daca lista lipseste, setul de date furnizat trebuie sa aiba valori pentru toate campurile, in ordinea din declararea tabelei. VALUES - specifica seturile de date (inregistrarile) care se vor adauga in tabela.

9

•

(expresie, ...),(...),... - fiecare paranteza specifica datele unei inregistrari ce va fi adaugata. Ele trebuie sa respecte numarul si ordinea coloanelor de la declararea tabelei sau, daca e specificat, numarul si ordinea campurilor din lisata de coloane (col_nume,...) prezentata anterior.

O sintaxa introdusa o data cu versiunea 3.22.10 permite specificarea explicita a corespondentei intre coloane si valorile inserate: INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_nume SET col_nume1=expresie1, col_nume2=expresie2, ... O a treia sintaxa permite adugarea automata a inregistrarilor prin selectia lor din alte tabele: INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name [(col_name,...)] SELECT ... Optiunea SELECT va fi discutata pe larg in capitolul urmator. In principiu trebuie retinute totusi cateva limitari: • •

operatia de selectie nu poate contine clauza ORDER_BY pentru ordonarea rezultatului datele nu pot fi selectate din aceeasi tabela in care sunt apoi inserate

5.2. Utilizarea comenzii INSERT

Se considera baza de date Banca, cuprinzand tabelele: Deponent nr_buletin char(8) NOT NULL PRIMARY KEY cnp char(13) NOT NULL nume char(24) NOT NULL prenume char(24) NOT NULL datan date oras char(24) DEFAULT 'Timisoara' adresa char(32) telefon char(9) Conturi nr_cont char(6) NOT NULL PRIMARY KEY nr_buletin char(8) NOT NULL tip_cont char(3) DEFAULT 'Lei' termen_depozit int(3) DEFAULT 0 sold int DEFAULT 0 Operatii nr_cont char(6) NOT NULL suma int data timestamp Activitatea urmatoare prezinta diverse moduri de scriere a instructiunii INSERT. Activitate 1

Rulati secventele: mysql> USE Banca; mysql> INSERT INTO Deponent VALUES ('TM123456', '1221181898381', 'Popescu','Victor', '1962-03-25', 'Timisoara', 'Florilor 4A ap 3', '056123321'), ('BV145456', '1112267568341', 'Pop','Mircea', '1954-07-15', 'Brasov', 'Piatra Craiului nr 8', '045123456'); mysql> INSERT INTO Deponent(nr_buletin, cnp, prenume, nume) VALUES

10

('TM832312', '1112282568441','Florin','Marasescu'), ('TM436613', '0090976761426','Ana','Tipatescu'); mysql> INSERT INTO Deponent SET nume='Gerogescu', prenume='Henrieta', nr_buletin='GL654321', cnp='0111166163476', oras='Galati'; mysql> SELECT * FROM Deponent;

6. Modificarea datelor din tabele 6.1. Comanda UPDATE

Comanda UPDATE permite actualizarea (modificarea) valorilor dintr-o tabela. Sintaxa acestei comenzi este: UPDATE [LOW_PRIORITY] [IGNORE] tbl_nume SET col_nume1=expr1,col_nume2=expr2,... [WHERE conditie_de_actualizare] Parametrii au urmatoarea semnificatie: • • • • •

LOW_PRIORITY - intarzie executia comenzii UPDATE pana cand nici un alt client nu mai citeste din tabela. Are acelasi efect ca si in cazul comenzii INSERT. IGNORE - nu se intrerupe executia in cazul in care se duplica chei unice la actualizare. Inregistrarile care cauzeaza erori sunt pur si simplu ignorate. tbl_nume - nume tabela unde are loc actualizarea SET col_nume1=expr1,col_nume2=expr2,... - indica acele coloane care sunt modificate si noile lor valori. Expresiile de actualizare pot cuprinde vechile valori ale campurilor de actualizat. WHERE conditie_de_actualizare - indica acele coloane care sunt actualizate, si anume doar cele pentru care conditie_de_actualizare are valoarea adevarat. Daca clauza WHERE lipseste, vor fi actualizate toate inregistrarile.

6.2. Utilizarea comenzii UPDATE

Considerand tabelele bazei de date Banca si datele adaugate in cadrul activitatii anterioare, se vor exemplifica urmatoarele actualizari: Rulati actualizarile:

Activitate 2

# Persoana cu buletinul 'TM123456' si-a schimbat numele din # Popescu in Predescu: mysql> UPDATE Deponent SET nume='Predescu' WHERE nr_buletin='TM123456'; mysql> SELECT * FROM Deponent; # Prefixele telefonice ale oraselor se schimba din 0xx in 7xx: mysql> UPDATE Deponent SET telefon=concat('7',right(telefon, length(telefon)-1)); mysql> SELECT * FROM Deponent;

7. Stergerea datelor din tabele 7.1. Comanda DELETE

Pentru stergerea datelor din tabele se foloseste comanda DELETE. Foarte important: datele o data sterse nu mai pot fi recuperate. Totusi, din motive de viteza, datele nu sunt efectiv sterse din fisiere, locul ocupat de ele fiind alocat pentru adaugarea de noi inregistrari la operatia de INSERT.

11

Sintaxa comazii DELETE este: DELETE [LOW_PRIORITY] FROM tbl_nume [WHERE conditie_de_stergere] Parametrii au urmatoarea semnificatie: • • •

LOW_PRIORITY - intarzie executia comenzii DELETE pana cand nici un client nu mai citeste din tabela. Are acelasi efect ca si in cazul comenzii INSERT. FROM tbl_nume - specifica tabela de unde se sterg inregistrari. WHERE conditie_de_stergere - specifica inregistrarile care vor fi sterse, si anume cele care indeplinesc conditia logica precizata. Atentie! Daca clauza WHERE este omisa se vor sterge toate inregistrarile din tabela.

7.2. Utilizarea comenzii DELETE

Considerand tabelele bazei de date Banca si datele adaugate in cadrul activitatii anterioare, se vor exemplifica urmatoarele stergeri: Rulati secventele:

Activitate 3

# Se vor sterge persoanele din Galati: mysql> DELETE FROM Deponent WHERE oras='Galati'; mysql> SELECT * FROM Deponent; # Se vor sterge inregistrarile cu numarul de telefon necompletat: mysql> DELETE FROM Deponent WHERE IsNull(telefon); mysql> SELECT * FROM Deponent;

8. Interogarea datelor 8.1. Comanda SELECT

Comanda SELECT este cea mai utilizata comanda SQL. Ea permite atat regasirea si vizualizarea datelor din tabelele bazei de date cat si calcularea unor expresii care nu au legatura cu datele din tabele. Sintaxa comenzii SELECT pentru calcularea valorii unor expresii este foarte simpla si a fost deja folosita in capitolele anterioare: SELECT expresie; Ex: SELECT 3*(5+12); Utilizarea comenzii SELECT pentru interogarea tabelelor este mai complexa. Sintaxa de baza a comenzii in acest caz este: SELECT [SQL_BUFFER_RESULT] [HIGH_PRIORITY] [DISTINCT | ALL] expresie_de_selectie,... [INTO {OUTFILE | DUMPFILE} 'nume_fisier'] [FROM referinte_tabele [WHERE conditie_selectie] [ORDER BY {intreg_pozitiv | nume_col | formula} [ASC | DESC] ,...] Parametrii din paranteze drepte sunt optionali. Cei din acolade si despartiti prin '|' sunt obligatorii dar mutual exclusivi (doar unul poate apare). Ei au urmatoarea semnificatie:

12

• • • • • •

• •

•

SQL_BUFFER_RESULT - se foloseste pentru a forta serverul sa creeze o tabela temporara cu rezultatul interogarii. Acest lucru este util in cazul in care tabela interogata este utilizata intensiv in retea si se doreste deblocarea rapida a ei pentru accesul altor clienti. In mod obisnuit nu este necesar. HIGH_PRIORITY - da prioritate interogarii curente fata de alte interogari INSERT sau UPDATE aflate in coada de asteptare pentru acea tabela. DISTINCT - Permite eliminarea randurilor duplicate din selectie. ALL - Rezultatul selectiei va cuprinde si randuri duplicate. Daca nu se specifica nici DISTINCT, nici ALL, implicit se considera ALL. expresie_de_selectie - expresia de selectie poate cuprinde referinte la coloane sau la functii aplicate asupra acestora. Un caz special il reprezinta caracterul * care semnifica includerea tuturor coloanelor din tabela in rezultat. INTO {OUTFILE | DUMPFILE} 'nume_fisier' - permite salvarea rezultatului unei interogari sub forma unui fisier text. Fisierul va fi creat pe server (trebuie sa nu existe), implicit in directorul bazei de date. Optiunea OUTFILE specifica salvarea tuturor randurilor rezultatului formatate pe coloane, pe cand DUMPFILE permite salvarea unui singur rand fara formatare. DUMPFILE este util pentru a salva campuri de tip BLOB (imagini, sune, video). FROM referinte_tabele - specifica tabela sau tabelele din care se selecteaza datele. In cazul in care referinta cuprinde mai multe tabele operatia este una de JOIN. WHERE conditie_selectie - specifica conditia de includere a datelor in selectie. Permite selectia doar a datelor care intereseaza la un moment dat. In cazul operatiunii de JOIN aici se vor adauga si constrangerile date de relatiile dintre tabele. Conditia de selectie consta dintr-o expresie logica simpla sau complexa care poate cuprinde functiile prezentate in capitolul 3. ORDER BY {intreg_pozitiv | nume_col | formula} [ASC | DESC], ... - permite ordonarea rezultatului dupa anumite coloane sau formule. Daca se specifica o lista de numere, acestea vor fi interpretate ca numere de ordine a coloanelor specificate in SELECT (incepand cu 1). Ordonarea se face dupa prima coloana din lista. In cazul in care doua inregistari au aceiasi valoare pentru coloana respectiva se va lua in considerare a doua coloana din lista (daca este specificata) etc. Parametrii ASC si DESC specifica ordonarea crescatoare, respectiv descrescatoare a inregistrarilor in rezultat. Rulati secventele:

Activitate 1

mysql> USE Banca; # Selectia tuturor deponentilor cu afisarea tuturor coloanelor mysql> SELECT * FROM Deponent; # Selectia tuturor deponentilor cu afisarea numelor si prenumelor mysql> SELECT nume, prenume FROM Deponent; # Selectia tuturor oraselor din care sunt deponetii mysql> SELECT oras FROM Deponent; # Selectia tuturor oraselor distincte din care sunt deponetii mysql> SELECT DISTINCT oras FROM Deponent;

8.2. Utilizarea conditiilor de selectie

Conditiile de selectie permit regasirea datelor necesare intr-un anumit context. La scrierea acestor conditii vor putea fi folosie functiile prezentate in Capitolul 3. De asemenea, la folosirea operatorilor de comparatie se va tine seama de observatiile facute acolo. Activitatea urmatoare prezinta diverse moduri de scriere a unor conditii de selectie INSERT. Activitate 2

Rulati secventele: # Selectia deponentilor cu numele 'Pop' mysql> SELECT * FROM Deponent WHERE nume='Pop'; # Selectia tuturor deponentilor a caror nume incep cu 'P' mysql> SELECT nr_buletin, nume, prenume FROM Deponent WHERE Left(nume,1)='P'; # Selectia tuturor deponentilor care nu au numar de telefon completat mysql> SELECT nr_buletin, nume, prenume, telefon FROM Deponent WHERE IsNULL(telefon); # Selectia deponentilor a caror prenume este Victor si sunt nascuti dupa 1960

13

mysql> SELECT * FROM Deponent WHERE prenume='victor' and datan>='1960-01-01'; # Selectia deponentilor nascuti intre 1960 si 1970 mysql> SELECT * FROM Deponent WHERE Year(datan)>=1960 and Year(datan)<=1970;

8.3. Ordonarea rezultatului

In multe situatii este necesara ordonarea rezultatelor instructiunii SELECT. Un exemplu concludent este reprezentat de lista rezultatelor la un concurs. Daca la concurs s-au prezentat 2000 de candidati si rezultatul este afisat intr-o lista neordonata, probabil ca ar fi necesar ca un candidat sa piarda jumatate de zi pentru a se regasi pe lista. Ordonarea se poate controla prin clauza ORDER BY. Este important de inteles ca aceasta clauza presupune o operatie suplimentara, mare consumatoare de timp. Activitatea urmatoare prezinta cateva posibilitati de a ordona rezultatul. Rulati secventele:

Activitate 3

# Ordonarea deponentilor dupa nume mysql> SELECT * FROM Deponent ORDER BY nume; # Ordonarea deponentilor dupa prenume si nume mysql> SELECT nr_buletin, nume, prenume, oras FROM Deponent ORDER BY 3,2; # Ordonarea deponentilor dupa anul nasterii si nume mysql> SELECT nume, prenume, datan FROM Deponent ORDER BY Year(datan), nume;

8.4. Folosirea alias-urilor (poreclelor)

Pentru a simplifica scrierea instructiunilor se pot folosi denumiri prescurtate, numite alias-uri (porecle). Ele pot fi folosite atat pentru a desemna tabele cat si coloane. O utilitate deosebita o au in cazul combinarii datelor din mai multe baze de date, pentru a inlocui nume de tipul nume_baza_de_date2.nume_tabela_1.nume_coloana_3, in cazul coloanelor calculate sau in cazul operatiilor de JOIN (vor fi discutate in capitolul urmator). Sintaxa de definire a unui alias este urmatoarea: SELECT alias_t1.nume_coloana1 [AS] alias_c1,... FROM tabela1 alias_t1ORDER BY alias_c1 ...; Dupa cum se observa, alias-urile definite pentru tabele pot fi folosite in partea de specificare a coloanelor, iar alias-urile coloanelor pot fi folosite in clauze ORDER BY sau HAVING (va fi discutata in capitolul urmator). Activitate 4

Rulati secventele: # Alias pentru tabela mysql> SELECT d.nume, d.nr_buletin FROM Deponent d; # Alias pentru tabela si coloana mysql> SELECT d.nr_buletin, d.nume AS num, d.prenume, d.oras AS o

14

FROM Deponent d ORDER BY o, num; # Alias pentru camp calculat mysql> SELECT nume, prenume, Year(datan) AS an FROM Deponent ORDER BY an;

9. Selectarea datelor din mai multe tabele. Operatia de JOIN. 9.1. Selectarea datelor din mai multe tabele.

Comanda SELECT in forma studiata in capitolul anterior permite efectuarea operatiei de selectare a datelor dintr-o singura tabela (implementeaza operatorul SELECT din algebra relationala). Limbajul SQL foloseste insa tot comanda SELECT pentru implementarea operatorului JOIN. Acest operator este definit de algebra relationala pentru a permite colectarea datelor din tabelele aflate in legaturi relationale. Folosirea comenzii SELECT pentru a face JOIN presupune specificarea in clauza FROM a tabelelor de unde se preiau datele si, in lista de proiectie, a campurilor care vor face parte din rezultat. In acest caz forma generala a instructiunii SELECT este: SELECT lista_campuri FROM lista_tabele WHERE conditii_join AND conditii_selectie ORDER BY ...  ... Parametrii au urmatoarea semnificatie: • • •

•

lista_campuri - specifica acele campuri care vor face parte din rezultat. Aceste campuri pot fi din oricare tabela specificata in clauza FROM. In situatia in care un camp din rezultat apare sub acelasi nume in doua sau mai multe tabele el trebuie prefixat cu tabela din care se extrage. In locul numelui tabelei se prefera de obicei folosirea unui alias. lista_tabele - specifica tabelele din care se vor aduna datele. Unele tabele de aici pot fi doar tabele de legatura intre alte tabele, ele neavand nici un camp selectat in rezultat. Functie de tipul de operatie JOIN dorit, lista_tabele poate fi doar o lista de nume despartite prin virgula sau poate avea o forma mai execta, dupa cum se va preciza in sectiunea urmatoare. conditii_join - specifica conditiile relationale care leaga tabelele. Ele sunt de obicei de forma tabela1.cheie_externa=tabela2.cheie_primara AND .... Daca o legatura lipseste sau este pusa gresit, rezultatul va fi de obiecei un produs cartezian intre tabele (toate combinatiile posibile intre inregistrari, fara a tine seama de legaturile relationale). conditii_selectie - specifica conditiile de filtrare a rezultatului dupa adunarea datelor din tabele.

Activitate 1

Adaugati la tabela Operatii cateva operatii pentru fiecare cont din tabela Conturi. Rulati secventele: mysql> USE Banca; # Produs cartezian (prin omiterea clauzei WHERE) mysql> SELECT cnp, nume, nr_cont FROM Deponent, Conturi; # Afisarea conturilor si a persoanelor care le detin. # Problema: campul nr_buletin exista in ambele tabele mysql> SELECT nr_buletin, nume, nr_cont FROM Deponent, Conturi WHERE Deponent.nr_buletin=Conturi.nr_buletin; # Rezolvarea problemei anterioare mysql> SELECT d.nr_buletin, d.nume, c.nr_cont FROM Deponent AS d, Conturi AS c WHERE d.nr_buletin=c.nr_buletin; # Selectarea datelor din trei tabele. Listarea tuturor operatiilor cu informatii despre persoana si cont. mysql> SELECT d.nr_buletin, d.nume, c.nr_cont, c.tip_cont, o.suma, o.data

15

FROM Deponent AS d, Conturi AS c, Operatii AS o WHERE d.nr_buletin=c.nr_buletin AND c.nr_cont=o.nr_cont; # Folosirea tabelei Conturi doar ca tabela de legatura mysql> SELECT d.nr_buletin, d.nume, o.suma, o.data FROM Deponent AS d, Conturi AS c, Operatii AS o WHERE d.nr_buletin=c.nr_buletin AND c.nr_cont=o.nr_cont; # Folosirea conditiei de selectie impreuna cu cea de join: # afisarea doar a operatiilor efectuate luna ianuarie mysql> SELECT d.nr_buletin, d.nume, c.nr_cont, c.tip_cont, o.suma, o.data FROM Deponent AS d, Conturi AS c, Operatii AS o WHERE d.nr_buletin=c.nr_buletin AND c.nr_cont=o.nr_cont AND MONTH(data)=1;

9.2. Tipuri de JOIN

Variantele posibile la operatia de JOIN se refera la modul in care sunt tratate inregistrarile care nu au corespondenta in fiecare tabela legata relational de tabelele in care inregistrarile respective apar. Principial, rezultatul operatiei de JOIN poate include sau nu inregistrarile fara corespondenta. In primul caz operatia se numeste OUTER JOIN, iar in cel de al doilea INNER JOIN. Operatia de OUTER JOIN va completa automat campurile care lipsesc din tabela corespondenta cu valoarea NULL. Un OUTER JOIN are la randul sau doua varinate. Ambele includ toate inregistrarile care au corespondenta in ambele parti ale relatiei. In plus, LEFT OUTER JOIN va include in rezultat si inregistrarile din tabela stanga care nu au corespondent in tabela din partea dreapta a relatiei. In opozitie, RIGHT OUTER JOIN va include in rezultat si inregistrarile din tabela din dreapta care nu au corespondent in tabela din partea stanga. Operatia de INNER JOIN nu are variate pe stanga sau pe dreapta, pentru ca in ambele cazuri se vor include in rezultat doar capurile care au corespondent la ambele capete ale relatiei. Daca lista de tabele din SELECT cuprinde doar tabele despartite prin virgula se considera implicit o operatie de INNER JOIN. Specificarea explicita a tipului de JOIN are sintaxa: SELECT lista_campuri FROM tabela [INNER JOIN | LEFT OUTER JOIN | RIGHT OUTER JOIN] tabela ON conditie_join ... WHERE conditii_selectie ORDER BY ...  ... In cazul acesta se observa ca specificarea conditiei de join (legatura relationala dintre tabele) se face dupa fiecare pereche de tabele printr-o clauza ON separata. In mod natural, clauza WHERE va contine aici doar conditiile suplimentare de filtrare a inregistrarilor dorite. Activitate 2

Rulati secventele: # Operatia de INNER JOIN implicit mysql> SELECT d.nr_buletin, d.nume, c.nr_cont FROM Deponent AS d, Conturi AS c WHERE d.nr_buletin=c.nr_buletin; # Operatia de INNER JOIN explicit mysql> SELECT d.nr_buletin, d.nume, c.nr_cont FROM Deponent AS d INNER JOIN Conturi AS c ON d.nr_buletin=c.nr_buletin; # Operatia de LEFT OUTER JOIN are sens deoarece pot exista persoane # ale caror conturi au fost desfiintate mysql> SELECT d.nr_buletin, d.nume, c.nr_cont FROM Deponent AS d LEFT OUTER JOIN Conturi AS c ON d.nr_buletin=c.nr_buletin; # Operatia de RIGHT OUTER JOIN nu are sens in situatia data deoarece nu pot # exista conturi fara proprietari. Rezultatul in acest caz va fi cel de la INNER JOIN

16

mysql> SELECT d.nr_buletin, d.nume, c.nr_cont FROM Deponent AS d RIGHT OUTER JOIN Conturi AS c ON d.nr_buletin=c.nr_buletin; # Operatia de OUTER JOIN poate fi folosita si pentru a lista toate inregistrarile # fara corespondenta, de ex. persoanele fara cont la un moment dat mysql> SELECT d.nr_buletin, d.nume, c.nr_cont FROM Deponent AS d LEFT OUTER JOIN Conturi AS c ON d.nr_buletin=c.nr_buletin WHERE isNULL(c.nr_cont);

Extragerea informatiilor de sumarizare. Informatiile de sumarizare reprezinta informatii globale despre datele din tabele. Informatiile de sumarizare se extrag pe grupuri de inregistrari. Mai mult, ele nu au sens decat in contextul gruparii datelor. Pentru a grupa datele se va utiliza urmatoarea sintaxa pentru instructiunea SELECT: SELECT lista_campuri FROM lista_tabele WHERE conditii_selectie GROUP BY {colana | expresie} HAVING conditie_includere_grup ORDER BY ...  ... Clauza GROUP BY specifica dupa ce valoare se va face gruparea. In majoritatea cazurilor expresia de grupare este reprezentata de o singura coloana. Toate inregistrarile cu aceeasi valoare pentru expresia de grupare vor fi considerate ca facand parte din acelasi grup. Daca clauza GROUP BY lipseste, dar in lista_campuri apar informatii de sumarizare se considera implicit ca toate inregistrarile fac parte din acelasi grup. Clauza HAVING permite selectarea grupurilor care sunt luate in considerare. Conditia din HAVING se aplica dupa constituirea grupului, pe cand conditia din clauza WHERE in timpul selectarii inregistrarilor. In plus, acesta clauza poate cuprinde doar referinte la campuri si aliasuri de campuri din lista de campuri a comenzii SELECT sau functii de sumarizare pe grup. Lista de campuri din comanda SELECT poate include urmatoarele functii de sumarizare: • • • • • •

COUNT(*) - numara cate inregistrari sunt selectate in fiecare grup. COUNT(DISTINCT expr) - numara cate inregistrari pentru care expresia are o valoare distincta sunt selectate in fiecare grup. AVG(expr) - calculeaza media aritmetica a valorilor expresiei pentru inregistrarile din fiecare grup. MIN(expr) - calculeaza valoarea minima a expresiei pentru toate inregistrarile din fiecare grup. MIN(expr) - calculeaza valoarea maxima a expresiei pentru toate inregistrarile din fiecare grup. SUM(expr) - calculeaza suma valorilor expresiei pentru toate inregistrarile din fiecare grup.

Atentie! Nu este permisa combinarea functiilor de sumarizare cu campurile din tabele decat in cazul in care campurile respective au o valoare unica in cadrul grupului. In cele mai multe cazuri acestea vor fi campurile din clauza GROUP BY.

17

Rulati secventele:

Activitate 3

# functii de sumarizare aplicate global mysql> SELECT COUNT(*) AS NrConturi, COUNT(DISTINCT nr_buletin) AS NrPers, MIN(sold), MAX(sold), AVG(sold), SUM(sold) FROM Conturi; # functii de sumarizare aplicate pe grup, dupa tipul de cont mysql> SELECT tip_cont, COUNT(*) AS NrConturi, MIN(sold), MAX(sold), AVG(sold), SUM(sold) FROM Conturi GROUP BY tip_cont; # functii de sumarizare aplicate pe grup, dupa tipul de cont cu conditie mysql> SELECT tip_cont, COUNT(*) AS NrConturi, MIN(sold), MAX(sold), AVG(sold), SUM(sold) FROM Conturi GROUP BY tip_cont HAVING SUM(sold)>10000; # combinarea gresita de campuri de sumarizare cu campuri cu valori multiple in grup mysql> SELECT tip_cont, COUNT(*) AS NrConturi FROM Conturi; # functii de sumarizare aplicate pe un JOIN # (se numara cate persoane din tabela Deponenti nu au conturi) mysql> SELECT COUNT(*) AS FaraConturi FROM Deponent AS d LEFT OUTER JOIN Conturi AS c ON d.nr_buletin=c.nr_buletin WHERE isNULL(c.nr_cont);

18