====== Laborator 01 ======
====== Utilizarea sistemului de gestiune pentru baze de date MySQL ======
===== Obiective ====
* definirea unui sistem de baze de date / sistem pentru de gestiune pentru baze de date; cunoașterea funcționalității puse la dispoziție de acestea
* proiectarea unei baze de date astfel încât structura acesteia să respecte diferite forme normale; identificarea formei normale optime pentru satisfacerea cerințelor unei aplicații de timp real
* familiarizarea cu principalele instrucțiuni de definire și manipulare a datelor din cadrul sistemului de gestiune pentru baze de date MySQL pentru implementarea unor aplicații care realizează nivelul de persistență prin astfel de metode
* folosirea obiectelor din baza de date (rutine stocate, triggere, evenimente, vizualizări) ca instrumente ce furnizează eficient informații, preluând o parte din sarcinile nivelurilor superioare ale aplicațiilor
* utilizarea tranzacțiilor cu nivele de izolare adecvate pentru asigurarea coerenței datelor stocate în cadrul bazei de date
===== Cuvinte Cheie ====
Database Systems, Database Management Systems, Data Definition Language, Data Manipulation Language, View Definition Language, normal forms, 1-NF, 2-NF, 3-NF, BC-NF, stored routine, procedure, function, trigger, event, view, cursor, transactions, read uncommitted, read committed, serializable, repeatable read
===== Materiale Ajutătoare ======
===== Sisteme de Gestiune pentru Baze de Date =====
Un **SGBD = sistem de gestiune pentru baze de date - SGBD** (//eng.// DBMS = Database Management Systems) este o colecţie de programe care are drept scop indicarea structurii bazei de date, popularea și exploatarea sa, asigurând totodată și protecţia datelor. Aceasta reprezintă o aplicaţie informatică complexă ce însoţeşte baza de date, având ca funcţii definirea (specificarea structurii şi a tipurilor de date stocate), construcţia (popularea) şi manipularea ei (diferite operaţii de la implementarea interogărilor pentru regăsirea informaţiilor până la generarea de rapoarte).
Rolul său este de a facilita accesul la date, fapt ce este realizat prin asigurarea transparenţei reprezentării acestora faţă de utilizatori în cadrul procesului de interacţiune cu ele. Din acest punct de vedere, sistemul de gestiune pentru baze de date este interfaţa între nivelul fizic, cel mai scăzut, şi programele prin care sunt transmise aplicaţiei interogările de regăsire a datelor.
Împreună cu datele propriu-zise (prin date înțelegându-se nu doar la colecţiile de informaţii stocate de către utilizator ci şi dicţionarul de date care conţine meta-date: structura bazei de date, definiţiile tipurilor de date / restricţiile de integritate, viziunile, indecşii precum şi fişierele anexe care conţin un context curent de lucru (parametrii de configurare), acesta formează **SBD = sistemul de baze de date** (//eng.// DBS = Database System).
**Arhitectura** asociată unei baze de date este reprezentată pe trei niveluri, având ca scop separarea aplicaţiilor utilizatorului de informaţiile stocate:
* nivelul (schema) intern(ă) specifică structura de stocare fizică a datelor, folosind un model al datelor fizice; acesta descrie atât modul de stocare cât și modul de acces;
* nivelul (schema) conceptual(ă) descrie structura bazei de date prin entităţi şi tipul de date asociat precum şi relaţiile dintre acestea, precizând şi restricţiile de integritate;
* nivelul (schema) extern(ă) prezintă moduri de vizualizare ale bazei de date din perspectiva diferiţilor utilizatori care o accesează.
**Obiectivele** unui sistem de gestiune pentru baze de date sunt:
* asigurarea independenţei datelor, încât modificarea structurii de memorare a datelor sau schimbarea modului de acces la ele (independenţă fizică) precum şi a schemei conceptuale (independenţă logică) să nu presupună rescrierea programelor de aplicaţie;
* asigurarea unei redundanţe minime şi controlate a datelor;
* asigurarea unor facilităţi de utilizare a datelor (concurenţa utilizatorilor, uşurinţa regăsirii datelor prin limbaje apropiate de cel natural, optimizarea timpilor de acces);
* oferirea unui nivel de securitate ridicat împotriva accesului neautorizat;
* asigurarea integrităţii datelor prin proceduri de validare şi printr-un protocol de refacere a bazei de date.
Toate sistemele de gestiune a bazelor de date oferă **un set de funcţii**:
* funcţia de definire a datelor, implementată prin intermediul unui limbaj de definire a datelor (DDL – Data Definition Language) ce permite crearea bazelor de date, specificarea structurii tabelelor unei baze de date prin precizarea atributelor, a tipurilor de date, a legăturilor dintre ele, a restricţiilor sau modalităţile de acces; schema bazei de date descrisă la acest nivel va fi memorată în dicţionarul de date;
* funcţia de manipulare a datelor, realizată printr-un limbaj de manipulare a datelor (DML – Data Manipulation Language) care permite adăugarea, modificarea sau ştergerea unor înregistrări, regăsirea de informaţii potrivit unor criterii şi ordonarea lor;
* funcţia de utilizare ce oferă interfeţe de regăsire a informaţiilor, printr-un limbaj de definire a vizualizărilor (VDL – View Definition Language); există mai multe tipuri de utilizatori:
* beneficiari (utilizatori conversaţionali) care vizualizează informaţiile fără a folosi vreun limbaj de interogare a bazei de date;
* programatori care utilizează limbajele de manipulare, exploatând baza de date prin mecanisme complexe;
* administratorul bazei de date care ia decizii în privinţa funţionării corespunzătoare a întregului sistem.
* funcţia de administrare a bazei de date care permite configurarea diferiţilor parametri (performanţă, securitate) în funcţie de scopul în care se doreşte să se utilizeze baza de date.
**Clasificarea** sistemelor de gestiune a bazelor de date se poate face după mai multe criterii:
* în funcţie de modelul de date utilizat: relaţional, reţea, ierarhizat, obiectual;
* numărul de utilizatori ce pot accesa baza de date simultan: monouser, multiuser;
* modul de stocare al datelor: centralizat, distribuit;
* costurile pentru licenţiere: produse gratuite şi cu cost de licenţiere scăzut / ridicat;
* limbajul pentru scrierea de aplicaţii: medii cu limbaj nativ şi cu limbaj gazdă.
De asemenea, pe lângă sistemele de bază de date convenţionale, au fost dezvoltate sisteme de baze de date evoluate cum ar fi cele paralele, mobile, spaţiale, multimedia, distribuite, dezvoltate pentru aplicaţii specializate, caracterizate prin funcţionalităţi sau tipuri de date care dau specificul sistemului de baze de date respectiv.
===== Proiectarea bazelor de date =====
În proiectarea bazelor de date trebuie să se țină cont de următoarele principii:
* structura bazei de date trebuie să fie **normalizată** (să respecte o formă normală), astfel încât să se evite anomaliile la operaţiile de actualizare în baza de date;
* pentru fiecare tabelă trebuie să se definească o **cheie primară** (formată din unul sau mai multe atribute) care să identifice în mod unic o înregistrare;
* **tipul de date** trebuie ales astfel încât să corespundă necesităţilor, evitând irosirea de memorie pe discul fizic, dar evitând situaţia în care anumite informaţii nu pot fi reţinute datorită unei alocări insuficiente;
* toate tabelele unei baze de date trebuie să fie legate prin legături de tip **cheie străină**: o tabelă care nu are legătură cu celelalte tabele nu face parte din baza de date respectivă.
==== Normalizarea ====
Normalizarea este procesul de proiectare a structurii unei tabele pentru a se minimiza redundanţa datelor, urmărindu-se evitarea anomaliilor în cazul operaţiilor de actualizare. Astfel, schemele de relaţie nesatisfăcătoare sunt descompuse obţinându-se mai multe scheme de relaţie mai mici care să respecte proprietăţile dorite. Normalizarea nu asigură însă în mod necesar buna construcţie a bazei de date.
Din punct de vedere structural, formele normale respectă relaţia FN1 < FN2 < FN3 < FNBC, astfel că o formă normală de ordin superior este mai bună decât o formă normală de ordin inferior, cel puțin din punctul de vedere al anomaliilor ce pot apărea în cadrul procesului de actualizare. Au fost definite şi forme normale superioare, precum forma normală 4 (FN4) şi forma normală 5 (FN5) sau forma normală domeniu-cheie (DKNF - eng. domanin-key normal form), însă acestea nu sunt întâlnite în mediul de afaceri, având o imporanţă strict teoretică.
În stabilirea formei normale pe care o va respecta schema de relaţie trebuie să se aibă în vedere şi principiul vitezei de răspuns a interogărilor, care depinde de numărul de joncţiuni realizat între tabele, acesta fiind invers proporţional cu forma normală. De cele mai multe ori, forma normală 3 este suficientă spre a satisface cerinţele organizaţiilor, realizând cel mai bun compromis între evitarea anomaliilor în operaţiile de manipulare a datelor şi asigurarea unei viteze de răspuns corespunzătoare.
Procesul de normalizare asigură conformitatea unei tabele fată de conceptul de **relaţie bine formată**, caracterizată prin:
- fiecare tabelă corespunde unei singure entităţi, conţinând exclusiv atributele specifice acesteia;
- principiul redundanţei minime şi controlate - nici o informaţie nu va fi reţinută în mai mult de o tabelă în cazul în care nu este necesar;
- atributele non-prime sunt dependente doar de cheia primară astfel că aceasta le identifică în mod unic;
- principiul integrităţii şi consistenţei datelor: nici o tabelă nu conţine anomalii la adăugare, modificare sau ştergere.
O **supercheie** ''S'' într-o relaţie ''R = (a1, a2 ... , an)'' este un set de atribute din ''R'' având proprietatea că nu există două n-tupluri ''t1'' şi ''t2'' în orice instanţă ''r'' a lui ''R'' astfel ca ''t1(S)=t2(S)''. Diferenţa între o cheie şi o supercheie constă în faptul că întotdeauna cheia conţine un număr minim de atribute. Un atribut al relaţiei ''R'' se numeşte prim dacă este membru al unei superchei din ''R'' şi este nonprim dacă nu este un atribut prim. Prin urmare, o cheie primară este o supercheie minimală (ireductibilă).
O **dependenţă funcţională** este o relaţie între două atribute ''X'' şi ''Y'' ale unei relaţii ''R'', notată ''X → Y'', cu proprietatea că fiecare valoare a lui ''X'' determină o singură valoare a lui ''Y''. Cu alte cuvinte, oricare ar fi două tupluri ''t1'' şi ''t2'' din ''R'', astfel încât ''t1(X)=t2(X)'', atunci ''t1(Y)=t2(Y)''.
O dependenţă funcţională ''X → Y'' (cu ''X'' atribut compus) este **completă** dacă prin eliminarea oricărui atribut ''Z ∈ X'' dependenţa funcţională este distrusă.
O dependenţă funcţională ''X → Y'' (cu ''X'' atribut compus) este **parţială** dacă există un atribut (sau set de atribute) ''Z ∈ X'' astfel încât ''X \ {Z} → Y''.
Definiţia poate fi extinsă în cazul în care ambele atribute ale dependenţei funcţionale sunt compuse: o dependenţă funcţională ''X → Y'' (cu ''X'', ''Y'' atribute compuse) este parţială dacă există atribute (sau seturi de atribute) ''Z1 ∈ X'', ''Z2 ∈ Y'' astfel încât ''X \ {Z1} → Y \ {Z2}''.
O dependenţă funcţională ''X → Y'' (cu ''X'' cheie primară) este **tranzitivă** dacă există un atribut (sau set de atribute) ''Z'' care nu fac parte din cheia primară astfel încât ''X → Z'' şi ''Z → Y''.
Dependenţele funcţionale tranzitive pot fi identificate cu uşurinţă în schemele de relaţie unde există dependenţe funcţionale între atribute non-prime. Prin urmare, modificările în structurile tabelelor caracterizate prin această problemă vor porni de la dependenţa funcţională dintre atributele non-prime.
=== Exemplu ===
Se consideră o schemă de relaţie pentru gestiunea proiectelor şi a resurselor umane din cadrul unei organizaţii, reţinând câte ore a lucrat fiecare angajat dintr-un departament la proiectul la care a fost asociat precum şi salariul său tarifar:
''gestiune_organizatie = { id_proiect, nume_proiect, id_angajat, nume_angajat, pozitie_angajat, salariu_tarifar_angajat, ore_lucrate_angajat }''
{{ :laboratoare:laborator01_faraFN.jpg?nolink&600 }}
Această schemă de relaţie este caracterizată prin **inconsistenţa datelor** (acelaşi departament poate fi exprimat prin valori diferite) cât şi prin **redundanţă** (pentru fiecare angajat se reţin de mai multe ori numele, departamentul şi salariul tarifar - dacă a lucrat la mai multe proiecte), ceea ce determină anomalii la principalele operaţii de manipulare a datelor:
* adăugare (pentru proiect/angajat se introduc valori null)
* modificare (datele referitoare la proiect/angajat trebuie actualizate în toate tuplurile care le referă)
* ștergere (atunci când un proiect/angajat este eliminat, se pierd şi informaţiile aferente acestora).
Pentru eliminarea acestor probleme, au fost definite forme normale:
=== Forma Normală 1 (FN1) ===
Forma normală primară (FN1) nu permite ca pentru un atribut de tip cheie să existe mai multe valori identice sau ca schema de relaţie să conţină atribute compuse.
În acest sens, se impune **stabilirea unei chei primare** care să identifice în mod unic tuplurile din schema de relaţie, **punându-se în evidenţă totodată şi dependenţele funcţionale**.
În cazul exemplului, se observă cheia primară ''(id_proiect, id_angajat)'', evidenţiindu-se totodată şi următoarele dependenţe funcţionale:
* dependenţe funcţionale parţiale\\ ''id_proiect → nume_proiect''\\ ''id_angajat → nume_angajat, pozitie_angajat, salariu_tarifar_angajat''
* dependenţe funcţionale tranzitive\\ ''pozitie_angajat → salariu_tarifar_angajat''
Astfel, schema de relaţie în care s-a identificat cheia primară (compusă) şi dependenţele funcţionale, respectă forma
normală 1, reprezentată prin următoarea diagramă funcţională:
{{ :laboratoare:laborator01_FN1.jpg?nolink&600 }}
=== Forma Normală 2 (FN2) ===
Forma normală secundară (FN2) este satisfăcută de schemele de relaţie care îndeplinesc condiţiile formei normale primare şi pentru care orice atribut nonprim este complet dependent funcţional de cheia primară.
În acest sens, se impune **eliminarea dependenţelor funcţionale parţiale** care implică redundanţa datelor şi anomalii la operaţiile de manipulare a acestora. În cazul în care cerinţele legate de performanţă (viteză de răspuns) implică păstrarea dependenţelor de tip parţial, este recomandată trecerea la implementarea unui depozit de date în care redundanţa reprezintă un principiu de proiectare.
**Observaţie**. Orice schemă de relaţie ce respectă FN1 şi are cheia primară formată dintr-un singur atribut respectă în mod automat şi FN2.
Se vor creea noi scheme de relaţie având drept chei primare atributele care determină dependenţele funcţionale parţiale, împreună cu atributele non-prime care se află în relaţie cu ele. Ele vor rămâne şi în schema de relaţie iniţială cu atributele non-prime faţă de care există dependenţe funcţionale complete. Menținerea în schema de relaţie iniţială a tuturor atributelor care alcătuiesc cheia primară este determinată de faptul că acestea vor reprezenta referinţe pentru legăturile ce se vor stabili cu noile scheme de relaţie constituite.
Pentru exemplul de faţă se vor constitui tabelele:
* ''proiecte'' (cu cheia primară ''id_proiect'') din care face parte atributul non-prim determinat ''nume_proiect''
* ''angajati'' (cu cheia primara ''id_angajat'') din care fac parte atributele non-prime determinate ''nume_angajat'', ''pozitie_angajat'', ''salariu_tarifar_angajat''.
În schema de relaţie iniţială (având cheia primară compusă ''(id_proiect, id_angajat)'') rămâne atributul non-prim ''ore_lucrate_angajat'', complet dependent funcţional de cheia primară.
① ''proiecte = {__id_proiect__, nume_proiect}''\\
''id_proiect → nume_proiect''\\
② ''angajati = {__id_angajat__, nume_angajat, pozitie_angajat, salariu_tarifar_angajat}''\\
''id_angajat → nume_angajat, pozitie_angajat, salariu_tarifar_angajat''\\
''pozitie_angajat → salariu_tarifar_angajat''\\
⓪ ''asocieri = {__id_proiect__, __id_angajat__, ore_lucrate_angajat}''\\
''(id_proiect, id_angajat) → ore_lucrate_angajat''
{{ :laboratoare:laborator01_FN2.jpg?nolink&600 }}
=== Forma Normală 3 (FN3) ===
Forma normală terţiară (FN3) este îndeplinită de schemele de relaţie care satisfac condiţiile formei normale secundare şi în care nu există dependenţe tranzitive.
În acest sens, se impune **eliminarea dependenţelor funcţionale tranzitive** care implică redundanţa datelor şi anomalii la operaţiile de manipulare a acestora.
Se vor creea noi scheme de relaţie având drept chei primare atributele care determină dependenţele funcţionale tranzitive, împreună cu atributele ce se află în relaţie cu ele. Ca şi în situaţia descompunerii din cazul formei normale 2, ele vor rămâne şi în schema de relaţie iniţială.
Pentru exemplul de faţă se va constitui tabela ''salarii_pozitii'', având cheia primară ''pozitie_angajat'' și atributul ''salariu_tarifar_angajat'' ce va fi eliminat din schema de relatie ''angajaţi''.
① ''proiecte = {__id_proiect__, nume_proiect}''\\
''id_proiect → nume_proiect''\\
② ''angajati = {__id_angajat__, nume_angajat, pozitie_angajat}''\\
''id_angajat → nume_angajat, pozitie_angajat''\\
③ ''salarii_pozitii = {__pozitie_angajat__, salariu_tarifar_angajat}''\\
''pozitie_angajat → salariu_tarifar_angajat''\\
⓪ ''asocieri = {__id_proiect__, __id_angajat__, ore_lucrate_angajat}''\\
''(id_proiect, id_angajat) → ore_lucrate_angajat''
{{ :laboratoare:laborator01_FN3.jpg?nolink&600 }}
==== ====
În procesul de normalizare a unei scheme de relaţie trebuie asigurate iniţial formele normale inferioare trecându-se apoi la formele normale superioare.
În cazul FN2 se elimină dependenţele funcţionale parţiale iar în cazul FN3 dependenţele funcţionale tranzitive, mecanismul fiind acelaşi: se creează noi scheme de relaţie conţinând atributele implicate în dependenţa funcţională respectivă, păstrând atributele determinante în schema de relaţie iniţială (pentru a servi drept referinţe) şi scoţând atributele determinate.
=== Forma Normală Boyce-Codd ===
Forma normală Boyce-Codd respectă forma normală 3; aşadar orice schemă de relaţie care este în FNBC este şi în FN3, fără ca reciproca să fie valabilă în mod necesar. Condiţia pe care trebuie să o îndeplinească tabela este că pentru orice dependenţă funcţională ''X → Y'', ''X'' este o supercheie, diferenţa faţă de FN3 fiind că aceasta permite ca ''Y'' să fie non-prim dacă ''X'' nu este supercheie.
**Exemplu**. Se consideră o schemă de relaţie pentru determinarea celui mai apropiat magazin de un anume tip faţă de o locaţie dată printr-o denumire.
''magazin_apropiat_tip = { denumire_locatie, tip_magazin, denumire_magazin_apropiat }''
{{ :laboratoare:laborator01_FNBCa.jpg?nolink&300 }}
Candidate pentru chei sunt ''(denumire_locatie, tip_magazin)'' sau ''(denumire_locatie, denumire_magazin_apropiat)''. Toate cele trei atribute sunt prime, deci schema face parte din FN3.
Totuşi, ''tip_magazin'' depinde de ''denumire_magazin''_apropiat care nu este supercheie, deci tabela nu respectă FNBC, ceea ce înseamnă că este susceptibilă de producerea anomaliilor la actualizare.
Vom descompune această schemă de relaţie în altele două:
① ''magazin_apropiat = {__denumire_locatie__, __denumire_magazin_apropiat__}''\\
② ''specializare = {__denumire_magazin_apropiat__, __tip_magazin__}''
{{ :laboratoare:laborator01_FNBCb.jpg?nolink&300 }}
O problemă o reprezintă totuşi faptul că schema de relaţie permite ca pentru o ''denumire_locatie'' să se specifice mai multe ''denumire_magazin_apropiat'' de acelaşi tip.
==== ====
De aceea, se preferă proiectarea unor baze de date conform formei normale 3 (FN3) şi nu conform formei normale Boyce-Codd. Deşi asigură scăderea anomaliilor în cazul operaţiilor de manipulare a datelor, aceasta creşte foarte mult timpul în care se execută interogările întrucât va fi necesar accesul la mai multe tabele simultan împreună cu asocierea informaţiilor din acestea, implicând operaţii de căutare mai complexe.
==== Alegerea cheii primare ====
De regulă, în sistemele de gestiune pentru baze de date, cheile primare sunt implementate sub forma unor arbori B+, astfel încât atunci când se alege un atribut care să se supună acestei constrângeri e integritate, trebuie să se țină cont de tipurile de date asociate, astfel încât acestea să asigure o regăsire mai rapidă a informaţiilor (astfel, se preferă datele numerice faţă de datele de tip şir de caractere avându-se în vedere şi dimensiunea lor).
=== ===
Arborii B+ sunt structuri de date folosite cu precădere la regăsirea informaţiilor (mai ales în sisteme de fişiere). În cadrul acestora, fiecare valoare posibilă a câmpului de căutare apare împreună cu un pointer către date în cadrul nodurilor frunză. Astfel de valori pot fi repetate în nodurile interne pentru a ghida căutarea.
Pentru un arbore B+ de ordin p, sunt îndeplinite următoarele caracteristici:
- nodurile interne au structura ''1, K1, P2, K2, ..., Pq-1, Kq-1, Pq>'', ''q ≤ p'', unde ''Pi'' reprezintă un pointer către un subarbore, iar ''Ki'' reprezintă valori ale cheilor de căutare ordonate crescător: ''K1 < K2 < ... < Kq-1''. Dacă se caută o valoare ''X'' în subardorele referit de ''Pi'' atunci ''Ki-1 < X ≤ Ki'', cu ''i ≠ 1'' şi ''i ≠ q'' (dacă ''i=1'', ''X ≤ K1'', dacă ''i=q'', ''X > Kq-1''). Orice nod intern are cel mult ''p'' si cel puţin ''[ (p/2) ]'' pointeri către subarbori (nodul rădăcină are cel puţin 2 pointeri, dacă este nod intern). Un nod intern cu ''q'' pointeri are ''q-1'' valori ale câmpului de căutare.\\ {{ :laboratoare:laborator01_b_nodintern.jpg?nolink&500 }}
- nodurile frunză au structura ''< 1, Pr1>, 2, Pr2>, ..., q-1, Prq-1>, Purm >'', ''q ≤ p'', unde ''Pri'' reprezintă un pointer către datele cărora le corespunde valoarea cheii de căutare ''Ki'' (acestea fiind de asemenea ordonate crescător ''K1 ≤ K2 ≤ ... ≤ Kq-1''), iar ''Purm'' este un pointer către următorul nod frunză (ţinând cont de faptul că toate nodurile frunză se găsesc pe acelaşi nivel). Fiecare nod frunză are cel puţin ''[ (p/2) ]'' valori.\\ {{ :laboratoare:laborator01_b_nodfrunza.jpg?nolink&500 }}
==== ====
Dacă identificarea unei înregistrări se face prin mai multe atribute, poate fi utilă definirea unei chei artificiale (autoincrementale).
==== Specificarea tipului de date ====
Alegerea tipului de date asociat unui atribut trebuie să țină cont de cerințele din specificații, evitându-se atât irosirea de spațiu de pe disc (se preferă folosirea tipurilor de date variabile) cât și trunchierea informațiilor datorată alocării undei dimensiuni necorespunzătoare.
Se recomandă ca atributele să fie atomice (indivizibile), pentru a se putea realiza analize complexe cu o mai mare uşurinţă. Cerinţele referitoare la granularitate vor determina şi alegerea cheilor primare (care pot fi compuse sau surogate în funcţie de nivelul de detaliu necesar).
==== Relații de tip cheie străină ====
Între toate tabelele din cadrul unei baze de date trebuie să existe o legătură (de tip cheie străină). O tabelă care apare ca fiind izolată în schema conceptuală nu poate fi considerată ca făcând parte din baza de date respectivă.
La definirea unor legături între tabele, trebuie specificat şi comportamentul în cazul operaţiilor de actualizare (''UPDATE'') şi ştergere (''DELETE''), întrucât, în caz contrar, acestea nu vor putea fi executate pentru tabela părinte dacă înregistrarea în cauză este referită în tabela copil.
===== Sistemul de Gestiune pentru Baze de Date MySQL =====
==== Instalare / Configurare ====
Operațiile de instalare și configurare a sistemului de gestiune pentru baze de date MySQL pot fi realizate în mai multe moduri atât pe [[:utile:instalare_mysql#Linux|Linux]] cât și pe [[:utile:instalare_mysql#Windows|Windows]].
==== Tipuri de Date în MySQL ====
În MySQL pot fi folosite trei tipuri de date:
* numerice;
* şiruri de caractere;
* dată calendaristică.
Sunt suportate şi unele extensii pentru date spaţiale.
=== Tipuri Numerice ===
Tipurile de date numerice sunt redate în tabelul de mai jos:
^ **Tipuri de Date Numerice** ^ **Valori Posibile** ^
| ''BIT[(M)]'' | 1 → 64, implicit m=1 |
| ''TINYINT[(M)] [UNSIGNED] [ZEROFILL]'' | -128 → 127 / 0 → 255 (unsigned) |
| ''BOOL, BOOLEAN'' | = ''TINYINT(1)'' / 0=false, non-0=true |
| ''SMALLINT[(M)] [UNSIGNED] [ZEROFILL]'' | -32768 → 32767 / 0 → 65535 (unsigned) |
| ''MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]'' | -8388608 → 8388607\\ 0 → 16777215 (unsigned) |
| ''INT[(M)] [UNSIGNED] [ZEROFILL]'' | -2147483648 → 2147483647\\ 0 → 4294967295 (unsigned) |
| ''INTEGER[(M)] [UNSIGNED] [ZEROFILL]'' | = ''INT'' |
| ''BIGINT[(M)] [UNSIGNED] [ZEROFILL]'' | -9223372036854775808 → 9223372036854775807\\ 0 → 18446744073709551615 (unsigned) |
| ''DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]'' | implicit M=10, D=0; maxim M=65, D=30 |
| ''DEC[(M[,D])] [UNSIGNED] [ZEROFILL]'' | = ''DECIMAL''\\ (''FIXED'' e folosit pentru compatibilitatea cu alte SGBD) |
| ''NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL]'' | ::: |
| ''FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]'' | ::: |
| ''FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]'' | -3.402823466E+38 → -1.175494351E-38\\ 0\\ 1.175494351E-38 → 3.402823466E+38 |
| ''DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]'' | -1.7976931348623157E+308 → -2.2250738585072014E-308\\ 0\\ 2.2250738585072014E-308 → 1.7976931348623157E+308 |
| ''DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL]'' | = ''DOUBLE'' |
| ''REAL[(M,D)] [UNSIGNED] [ZEROFILL]'' | ::: |
| ''FLOAT(p) [UNSIGNED] [ZEROFILL]''| p=0→24 = ''FLOAT''\\ p=25→53 = ''DOUBLE'' |
Atributele datelor de tip numeric sunt:
* ''UNSIGNED'' specifică dacă atributul respectiv reţine informaţii de tip numeric cu sau fără semn (implicit tipurile de date au asociat atributul ''SIGNED'');
* ''ZEROFILL'' indică faptul că pe poziţiile libere dintr-un atribut de tip numeric se vor completa cifre de ’0’; atributele ce au asociată această proprietate vor avea în mod automat şi proprietatea ''UNSIGNED'';
* ''AUTO_INCREMENT'' va completa cu ''valoare+1'' atributul unei înregistrări pentru care se specifică (la adăugare) ''NULL'' sau ''0''.
În schema de mai sus, ''M'' reprezintă numărul total de cifre pe care poate fi reprezentată o valoare de tipul numeric specificat, iar ''D'' numărul de cifre care urmează după virgulă în cazul numerelor reale. În cazul tipurilor de date întregi, ''M'' nu modifică valoarea maximă pe care o poate reţine atributul respectiv, ci se referă la numărul de poziţii care vor fi afişate. O astfel de funcţionalitate poate fi folosită în cazul în care se doreşte să se realizeze o aliniare a informaţiilor vizualizate.
**Exemplu**. Considerăm un atribut cu definiţia ''INT(5) ZEROFILL'' care are valoarea ''1234'' pentru o înregistrare. Acesta se va afişa sub forma ''01234''.
**Observaţie**.
''SERIAL'' reprezintă un alias pentru secvenţa ''BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE''.
''SERIAL DEFAULT VALUE'' reprezintă un alias pentru secvenţa ''NOT NULL AUTO_INCREMENT UNIQUE''.
Toate operaţiile aritmetice se fac folosind valori ''BIGINT'' sau ''DOUBLE'' fără semn, astfel că nu trebuie folosite valori mai mari de posibilitatea de stocare a acestora (63 de biţi).
Funcţiile care pot fi folosite pentru datele de tip numeric sunt:
|< 100% >|
| ''ABS()'' | ''CEIL()'' | ''CRC32()'' | ''LN()'' | ''PI()'' | ''ROUND()'' |
| ''ACOS()'' | ''CEILING()'' | ''DEGREES()'' | ''LOG10()'' | ''POW()'' | ''SIGN()'' |
| ''ASIN()'' | ''CONV()'' | ''DIV()'' | ''LOG2()'' | ''POWER()'' | ''SIN()'' |
| ''ATAN2()'' | ''COS()'' | ''EXP()'' | ''LOG()'' | ''RADIANS()'' | ''SQRT()'' |
| ''ATAN()'' | ''COT()'' | ''FLOOR()'' | ''MOD()'' | ''RAND()'' | ''TAN()'' |
=== Tipuri Șir de Caractere ===
Tipurile de date şir de caractere sunt redate în tabelul de mai jos:
|< 100% >|
^ **Tipuri de Date Şir de Caractere** ^
| ''[NATIONAL] CHAR[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]'' |
| ''[NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]'' |
| ''BINARY(M)'' |
| ''VARBINARY(M)'' |
| ''TINYBLOB'' |
| ''TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name]'' |
| ''BLOB[(M)]'' |
| ''TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]'' |
| ''MEDIUMBLOB'' |
| ''MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]'' |
| ''LONGBLOB'' |
| ''LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]'' |
| ''ENUM('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]'' |
| ''SET('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]'' |
Pentru mai multe tipuri de date şir de caractere se specifică atributul ''CHARACTER SET'' (sau ''CHARSET'') ce indică gama din care fac parte caracterele (frecvent utilizate sunt folosite ''latin1'' sau ''utf8''). În cazul în care se specifică valoarea ''binary'' pentru setul de caractere, se face în mod automat convertirea la tipul de date corespunzător, dacă este cazul: ''CHAR'' → ''BINARY'', ''VARCHAR'' → ''VARBINARY'', ''TEXT'' → ''BLOB''. Nu este şi cazul tipului ''ENUM''. Pentru a vizualiza lista seturilor de caractere disponiile pe sistemul MySQL instalat, se foloseşte instrucţiunea ''SHOW CHARACTER SET''.
Fiecare set de caractere are asociat şi un mod de asamblare (//eng.// ''COLLATION'') care poate fi consultat folosind interogarea ''SHOW COLLATION LIKE 'charset%';'' unde ''charset'' este setul de caractere pentru care se doreşte afişarea modului de asamblare.
Prin cuvântul-cheie ''NATIONAL'' se specifică faptul că tipul de date respectiv trebuie să definească un set de caractere. Implicit, acesta este ''utf8''.
Diferenţa între tipurile de date ''CHAR'' şi ''VARCHAR'' e dată de memoria pe care sistemul de gestiune pentru baze de date o alocă. În cazul tipului de date ''CHAR'' se va aloca un spaţiu de memorie fix, indiferent de valoarea pe care o are atributul în cauză (se adaugă spaţii la dreapta până la dimensiunea specificată). În cazul în care nu se specifică nici o dimensiune, valoarea implicită a lungimii este 1. Pentru tipul de date ''VARCHAR'' spațiul de memorie va fi variabil, depinzând de valoarea pe care o are atributul respectiv (reţinându-se totodată şi dimensiunea acesteia, ca prefix; din acest motiv, în cazul cel mai defavorabil, dimensiunea tipului de date ''VARCHAR'' va fi chiar mai mare decât cea specificată în definiţie, adăugându-se 1-2 octeţi) Dimensiunea maximă pentru tipul ''VARCHAR'' este 65535 octeţi, dar această dimensiune nu poate fi depăşită pentru întregul tuplu. De asemenea, atunci când se foloseşte setul de caractere ''utf8'' (în care reprezentarea unui caracter ocupă până la 3 octeţi), dimensiunea maximă va fi 21844.
În cazul tipului de date ''BINARY'' / ''VARBINARY'' se vor reţine şiruri de caractere sub formă binară, indiferent de setul de caractere utilizat.
Distincţia ''BINARY'' / ''CHAR'' ca stocare a şirurilor de caractere (binar / nonbinar) se regăseşte în diferenţa între ''BLOB'' (Binary Large Object) şi ''TEXT'', ultimele putând reţine şiruri de caractere având dimensiuni foarte mari.
Dimensiunile maxime ale tipurilor de date pentru şiruri de caractere sunt:
|< 100% >|
^ **Tip de Date** ^ **Dimensiune Maximă** ^
| ''TINYBLOB'' / ''TINYTEXT'' | 28 - 1 = 255 octeţi, din care 1 pentru reţinerea dimensiunii |
| ''BLOB'' / ''TEXT'' | 216 - 1 = 65535 octeţi, din care 2 pentru reţinerea dimensiunii |
| ''MEDIUMBLOB'' / ''MEDIUMTEXT'' | 224 - 1 = 16777215 octeţi, din care 3 pentru reţinerea dimensiunii |
| ''LONGBLOB'' / ''LONGTEXT'' | 232 - 1 = 4294067295 octeţi (4GB), din care 4 pentru reţinerea dimensiunii |
Tipul de date ''ENUM'' indică faptul că valoarea atributului poate fi preluată doar din variantele specificate. Teoretic pot fi specificate 65535 valori (practic aproximativ 3000).
Tipul de date ''SET'' se foloseşte pentru câmpuri care pot avea zero sau mai multe valori care fac parte dintr-o serie de variante specificate. Pot fi specificate maxim 64 de valori.
Funcţiile care pot fi folosite pentru datele de tip şir de caractere sunt:
|< 100% >|
| ''ASCII()'' | ''EXPORT_SET()'' | ''LENGTH()'' | ''MID()'' | ''REPEAT()'' | ''SPACE()'' |
| ''BIN()'' | ''FIELD()'' | ''LIKE'' | ''NOT LIKE'' | ''REPLACE()'' | ''STRCMP()'' |
| ''BIT_LENGTH()'' | ''FIND_IN_SET()'' | ''LOAD_FILE()'' | ''NOT REGEXP'' | ''REVERSE()'' | ''SUBSTR()'' |
| ''CHAR_LENGTH()'' | ''FORMAT()'' | ''LOCATE()'' | ''OCT()'' | ''RIGHT()'' | ''SUBSTRING_INDEX()'' |
| ''CHAR()'' | ''HEX()'' | ''LOWER()'' | ''OCTET_LENGTH()'' | ''RLIKE'' | ''SUBSTRING()'' |
| ''CHARACTER_LENGTH()'' | ''INSERT()'' | ''LPAD()'' | ''ORD()'' | ''RPAD()'' | ''TRIM()'' |
| ''CONCAT_WS()'' | ''INSTR()'' | ''LTRIM()'' | ''POSITION()'' | ''RTRIM()'' | ''UCASE()'' |
| ''CONCAT()'' | ''LCASE()'' | ''MAKE_SET()'' | ''QUOTE()'' | ''SOUNDEX()'' | ''UNHEX()'' |
| ''ELT()'' | ''LEFT()'' | ''MATCH'' | ''REGEXP'' | ''SOUNDS LIKE'' | ''UPPER()'' |
=== Tipuri Dată Calendaristică ===
Atributele ce reţin date calendaristice pot avea următoarele tipuri:
|< 100% >|
^ **Tipuri de Date Calendaristice** ^ **Format**\\ **Valori Posibile** ^
| ''DATE'' | ’YYYY-MM-DD’\\ ’1000-01-01’ → ’9999-12-31’ |
| ''DATETIME[(fsp)]'' | ’YYYY-MM-DD HH:MM:SS[.fraction]’\\ ’1000-01-01 00:00:00.000000’ → ’9999-12-31 23:59:59.999999’ |
| ''TIMESTAMP[(fsp)]'' | ’YYYY-MM-DD HH:MM:SS[.fraction]’ UTC\\ ’1970-01-01 00:00:01.000000’ → ’2038-01-19 03:14:07.999999’ |
| ''TIME[(fsp)]'' | ’HH:MM:SS[.fraction]’\\ ’-838:59:59.000000’ → ’838:59:59.000000’ |
| ''YEAR[(2|4)]'' | ’YY’ / ’YYYY’\\ ’70’ → ’69’ (1970 → 2069)\\ ’1901’ → ’2155’ |
Dacă în tipul de date ''DATETIME'' informaţiile sunt reţinute în forma indicată de utilizator, în tipul de date ''TIMESTAMP'' acestea sunt automat convertite la ''UTC'' (Universal Time Coordinated) în funcţie de zona în care se găseşte instalat serverul MySQL, reţinându-se ca număr de secunde care s-au scurs de la 1970-01-01 00:00:00.000000 UTC.
De regulă, tipul de date ''TIMESTAMP'' este folosit ma ales pentru memorarea celei mai recente modificări realizată asupra unei înregistrări (în cazul operaţiilor ''INSERT'' şi ''UPDATE''), fiind utilizată împreună cu clauzele ''DEFAULT CURRENT_TIMESTAMP'' respectiv ''ON UPDATE CURRENT_TIMESTAMP''. Acelaşi comportament poate fi obţinut prin specificarea atributului respectiv ca fiind ''NULL'' (dacă nu violează vreo constrângere de integritate).
Tipul de date ''TIME'' poate avea şi valori negative şi se foloseşte nu numai pentru o anumită oră din zi, ci şi pentru a indica un interval de timp.
Tipurile de date ''TIME'', ''DATETIME'' şi ''TIMESTAMP'' permit specificarea de fracţiuni de secundă (până la 6 zecimale). Implicit, parametrul ''fsp'' are valoarea 0.
În cazul tipului de date ''YEAR(2)'' valorile de la 0 la 69 au semnificaţia anilor 2000-2069 în timp ce valorile de la 70 la 99 reprezintă anii 1970-1999. Totuşi, începând cu versiunea 5.6.6, acest tip de date este considerat depăşit, fiind convertit în mod automat la ''YEAR(4)''.
Funcţiile care pot fi folosite pentru datele de tip dată calendaristică sunt:
|< 100% >|
| ''ADDDATE()'' | ''DATEDIFF()'' | ''LOCALTIME()*'' | ''SECOND()'' | ''TO_DAYS()'' |
| ''ADDTIME()'' | ''DAY()'' | ''LOCALTIMESTAMP()*'' | ''STR_TO_DATE'' | ''TO_SECONDS()'' |
| ''CONVERT_TZ()'' | ''DAYNAME()'' | ''MAKETIME()*'' | ''SUBDATE()'' | ''UNIX_TIMESTAMP()'' |
| ''CURDATE()'' | ''DAYOFMONTH()'' | ''MICROSECOND()'' | ''SUBTIME()'' | ''UTC_DATE()'' |
| ''CURRENT_DATE()*'' | ''DAYOFWEEK()'' | ''MINUTE()'' | ''SYSDATE()'' | ''UTC_TIME()'' |
| ''CURRENT_TIME()*'' | ''DAYOFYEAR()'' | ''MONTH()'' | ''TIME_FORMAT()'' | ''UTC_TIMESTAMP()'' |
| ''CURRENT_TIMESTAMP()*'' | ''EXTRACT()'' | ''MONTHNAME()'' | ''TIME_TO_SEC()'' | ''WEEK()'' |
| ''CURTIME()'' | ''FROM_DAYS()'' | ''NOW()'' | ''TIME()'' | ''WEEKDAY()'' |
| ''DATE_ADD()'' | ''FROM_UNIXTIME()'' | ''PERIOD_ADD()'' | ''TIMEDIFF()'' | ''WEEKOFYEAR()'' |
| ''DATE_FORMAT()'' | ''GET_FORMAT()'' | ''PERIOD_DIF()'' | ''TIMESTAMP()'' | ''YEAR()'' |
| ''DATE_SUB()'' | ''HOUR()'' | ''QUARTER()'' | ''TIMESTAMPADD()'' | ''YEARWEEK()'' |
| ''DATE()'' | ''LAST_DAY'' | ''SEC_TO_TIME()'' | ''TIMESTAMPDIFF()'' |
Funcţiile marcate cu * există şi ca variabile propriu-zise având aceeaşi denumire şi comportament.
==== ====
Conversia între diferite tipuri de date se face explicit folosind funcţia ''CAST'':
CAST(value AS type)
spre exemplu:
CAST('123.45' AS DECIMAL(5,2)) = 123.45
CAST('2013-10-14' AS DATETIME) = '2013-10-14 00:00:00'
CAST(123 AS CHAR) = ’123’
În operaţii în care intervin valori având tipuri de date diferite, în măsura în care este posibil, conversia este realizată în mod implicit de sistemul de gestiune pentru baze de date.
===== Instrucțiuni pentru gestiunea datelor =====
==== Operații de definire a datelor ====
În MySQL, informațiile sunt grupate sub formă de baze de date (= scheme), fiecare dintre acestea putând conține mai multe tabele.
=== CREATE DATABASE ===
Crearea unei baze de date se face prin instrucțiunea:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] database_name
[create_specification] ...
create_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
Operația este realizată cu succes dacă utilizatorul deține dreptul ''CREATE'' asupra bazei de date și dacă baza de date nu există anterior (altfel, trebuie specificată clauza ''IF NOT EXISTS''). Inițial, baza de date este vidă.
O bază de date este stocată sub forma unui director care conține câte un fișier pentru fiecare tabelă și un fișier ''db.opt'' pentru a se reține atribute precum setul de caractere și modul de asamblare (ce pot fi specificate la crearea bazei de date).
=== ALTER DATABASE ===
Prin intermediul comenzii ''ALTER DATABASE'' pot fi modificate atributele bazei de date, cum ar fi setul de caractere și modul de asamblare.
ALTER {DATABASE | SCHEMA} [database_name]
alter_specification ...
alter_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
Operația este realizată cu succes dacă utilizatorul deține dreptul ''ALTER'' asupra bazei de date.
Dacă nu se specifică nici un identificator pentru baza de date, va fi utilizată baza de date implicită (selectată în mod curent).
În cazul în care baza de date conține rutine stocate care utilizează setul de caractere sau modul de asamblare implicite, acestea trebuie redefinite pentru a folosi aceste proprietăți.
=== DROP DATABASE ===
Ștergerea unei baze de date (împreună cu toate tabelele pe care le conține, cu excepția celor marcate ca fiind temporare - care sunt eliminate odată cu sesiunea curentă) se face prin instrucțiunea:
DROP {DATABASE | SCHEMA} [IF EXISTS] database_name;
Operația este realizată cu succes dacă utilizatorul deține dreptul ''DROP'' asupra bazei de date și dacă baza de date există în prealabil (altfel, trebuie specificată clauza ''IF EXISTS'').
Dacă există o bază de date implicită stabilită anterior, în urma execuției acestei instrucțiuni nu va mai exista o bază de date implicită.
=== SHOW DATABASES ===
Vizualizarea listei ce conține bazele de date existente poate fi realizată prin intermediul comenzii:
SHOW {DATABASES | SCHEMAS}
[LIKE 'pattern' | WHERE expr]
Vor fi incluse doar acele baze de date pentru care utilizatorul curent are anumite privilegii (cu excepția cazului în care deține dreptul de acces ''SHOW DATABASES'').
Sunt întoarse rezultate pentru fiecare director întâlnit, chiar dacă acesta nu reprezintă o bază de date propriu-zisă.
Clauza ''LIKE'' permite filtrarea rezultatelor, luând în considerare denumirile care respectă un anumit șablon (expresie regulată).
Clauza ''WHERE'' permite specificarea de condiții folosind atributele incluse în rezultat (în cazul de față, ''Database'').
=== USE ===
În MySQL există o bază de date implicită, la care se vor referi toate operațiile care îi urmează:
USE database_name;
Stabilirea unei baze de date implicite nu restricționează accesul la obiectele altei baze de date, care trebuie să fie prefixate de denumirea bazei de date din care fac parte ''database_name.object_name''.
Instrucţiunea ''USE'' (ca şi instrucţiunea ''QUIT'') nu trebuie terminată prin ';'. În cazul în care într-o sesiune va fi utilizată o singură bază de date, pentru a evita folosirea instrucţiunii ''USE'', se poate specifica baza de date implicită prin comanda ''mysql -u username -p database_name'' în momentul când se reaizează conexiunea.
Instrucțiunea ''SELECT DATABASE()'' indică denumirea bazei de date curente.
=== Specificarea drepturilor de acces ===
Gestiunea drepturilor de acces (privilegiilor) - acordarea / revocarea - se face prin intermediul instrucțiunilor ''GRANT ... TO'' / ''REVOKE ... FROM''.
GRANT | REVOKE
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO | FROM user_specification [, user_specification] ...
object_type:
TABLE
| FUNCTION
| PROCEDURE
priv_level:
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name
user_specification:
user
[
| IDENTIFIED WITH auth_plugin [AS 'auth_string']
IDENTIFIED BY [PASSWORD] 'password'
]
Un utilizator nu poate acorda / revoca drepturi de acces decât dacă deține privilegiul ''GRANT OPTION'' / ''REVOKE OPTION'' precum și drepturile de acces pe care le acordă / revocă.
Privilegiile suportate sunt ''ALL [PRIVILEGES]'', ''ALTER'', ''ALTER ROUTINE'', ''CREATE'', ''CREATE ROUTINE'', ''CREATE TABLESPACE'', ''CREATE TEMPORARY TABLES'', ''CREATE USER'', ''CREATE VIEW'', ''DELETE'', ''DROP'', ''EVENT'', ''EXECUTE'', ''FILE'', ''GRANT OPTION'', ''INDEX'', ''INSERT'', ''LOCK TABLES'', ''PROCESS'', ''PROXY'', ''REFERENCES'', ''RELOAD'', ''REPLICATION CLIENT'', ''REPLICATION SLAVE'', ''SELECT'', ''SHOW DATABASES'', ''SHOW VIEW'', ''SHUTDOWN'', ''SUPER'', ''TRIGGER'', ''UPDATE'' și ''USAGE''.
Privilegiile globale sunt specificate folosind sintaxa ''ON *.*'' și sunt stocate în tabela ''mysql.user''.
Privilegiile la nivel de bază de date sunt specificate folosind sintaxa ''ON database_name.*'' și sunt stocate în tabela ''mysql.db''. Dacă se folosește sintaxa ''ON *'' și există o bază de date implicită, privilegiile specificate se vor referi la aceasta.
Privilegiile la nivel de tabel sunt specificate folosind sintaxa ''ON database_name.table_name'' și sunt stocate în tabela ''mysql.tables_priv''. Există posibilitatea de a nu prefixa denumirea tabelei prin numele bazei de date, dacă există o bază de date implicită și tabela specificată există în contextul acesteia.
Privilegiile la nivel de coloană se fac prin specificarea atributelor respective, între paranteze, după indicarea drepturilor de acces respective. Acestea sunt stocate în tabela ''mysql.columns_priv''.
Privilegiul ''ALL'' se referă la toate drepturile de acces disponibile la nivelul la care se acordă (global, bază de date, tabel, coloană).
Pot fi specificate drepturi de acces pentru obiecte care nu există încă, privilegiile fiind aplicate din momentul în care acestea există în baza de date.
=== CREATE TABLE ===
O tabelă din cadrul bazei de date este creată prin intermediul instrucţiunii ''CREATE TABLE'', având forma:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name
(create_definition,...)
[table_options]
[partition_options]
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name
[(create_definition,...)]
[table_options]
[partition_options]
select_statement
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name
{ LIKE old_table_name | (LIKE old_table_name) }
create_definition:
column_name column_definition
| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_column_name,...)
[index_option] ...
| {INDEX|KEY} [index_name] [index_type] (index_column_name,...)
[index_option] ...
| [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
[index_name] [index_type] (index_column_name,...)
[index_option] ...
| {FULLTEXT} [INDEX|KEY] [index_name] (index_column_name,...)
[index_option] ...
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_column_name,...) reference_definition
| CHECK (expression)
column_definition:
data_type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'message']
[COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
[STORAGE {DISK|MEMORY|DEFAULT}]
[reference_definition]
index_column_name:
column_name [(length)] [ASC | DESC]
index_type:
USING {BTREE | HASH}
reference_definition:
REFERENCES table_name (index_column_name,...)
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION
O tabelă este reprezentată sub forma unui fișier ''.frm'' în directorul corespunzător bazei de date din care face parte. De asemenea, în funcție de motorul de stocare, pot fi create și alte fișiere pentru fiecare tabelă în parte.
Clauza ''TEMPORARY'' specifică o tabelă care va exista doar pe perioada sesiunii curente, urmând a fi ştearsă în momentul în care aceasta este închisă.
Clauza ''IF NOT EXISTS'' previne producerea unor erori în situaţia în care există deja o tabelă cu acelaşi nume în baza de date implicită. Totuși, nu se realizează nici o verificare cu privire la identitatea dintre structura precizată în instrucțiune și structura tabelei existente.
Precizarea unui atribut implică specificarea mai multor proprietăți:
* tipul de date, specificat de ''data_type'' sau ''spatial_type'' poate lua orice valoare din cele disponibile (numerice, șiruri de caractere, date calendaristice, extensii pentru date spațiale);
* clauzele ''NULL'' (implicit) / ''NOT NULL'' controlează permisiunea ca atributul în cauză să accepte și valori nule;
* clauza ''DEFAULT'' specifică valoarea implicită pe care o va avea câmpul respectiv în cazul care nu se indică altfel, aceasta trebuind să fie o constantă (nu o expresie) sau ''CURRENT_TIMESTAMP'' (pentru o coloană având tipul ''DATETIME''; pentru câmpurile de tip ''BLOB'' sau ''TEXT'' nu se poate asocia o valoare implicită;
* clauza ''AUTO_INCREMENT'' implică completarea automată cu următoarea secvență din atributul respectiv (de regulă, incrementare cu 1) dacă se introduce o valoare ''NULL'' sau valoarea 0; se aplică doar pentru tipurile de date întregi sau reale;
* clauza ''COMMENT'' permite documentarea unui atribut printr-un text care nu poate depăşi 1024 de caractere;
* clauza ''COLUMN_FORMAT'' precizează spaţiul de memorie ce se va aloca, acesta putând fi fix (''FIXED''), dinamic (''DYNAMIC'') sau stabilit în funcţie de tipul de dată specificat (''DEFAULT''); implementarea curentă ignoră această clauză;
* clauza ''STORAGE'' specifică locaţia unde va fi stocată informaţia reţinută de câmpul respectiv în cazul mediilor distribuite: pe disc - ''DISK'' sau în memorie - ''MEMORY'' (implicit).
* clauzele ''KEY'' și ''INDEX'' sunt sinonime și denotă un atribut de tip index (un index poate fi creat şi prin comanda ''CREATE INDEX'' şi şters prin comanda ''DROP INDEX'')
* un index cu clauza ''UNIQUE'' impune ca toate valorile câmpului respectiv să fie distincte între ele; sunt permise însă mai multe valori de tip ''NULL'' într-un astfel de atribut, dacă nu se specifică explicit altfel;
* pentru tipurile de date ''CHAR'', ''VARCHAR'', ''BINARY'', ''VARBINARY'' pot fi specificaţi indecşi doar pentru prefixurile valorilor memorate, a căror dimensiune este specificată după denumirea propriu-zisă a atributului ''column_name(length)'', unde ''length'' este dată ca număr de caractere pentru tipurile de date non-binare și ca număr de octeți pentru tipurile de date binare;
* clauzele ''ASC'' / ''DESC'', referitoare la indecși, sunt parsate dar ignoratăemomentan (ordonarea indecşilor se face crescător indiferent de valoarea precizată);
* clauza ''FULLTEXT'' face ca indexarea să se realizeze după întreaga valoare a atributului, opţiunea fiind folosită pentru căutări în text, fiind însă disponibilă numai pentru atribute de tipul ''CHAR'', ''VARCHAR'' și ''TEXT'';
* unele motoare de căutare permit specificarea tipului de index, putând fi folosite arbori B+ (''BTREE'') sau o funcție hash (''HASH'').
* constrângerile asupra câmpurilor ce ar putea fi specificate prin clauza ''CHECK'' nu sunt încă implementate în versiunea MySQL curentă, astfel încât impunerea unor astfel de condiţii trebuie realizată folosind alte mecanisme pe care le pune la dispoziţie sistemul de gestiune pentru baze de date şi anume trigger-ele.
Există o limitare fizică la maxim 4096 de coloane în cadrul unui tabel, însă această valoare depinde şi de memoria necesară pentru fiecare atribut în parte.
O **cheie primară** este un index unic în care toate câmpurile ce o constituie trebuie să aibă proprietatea ''NOT NULL'' (în cazul în care câmpurile componente nu au specificată clauza ''NOT NULL'', sistemul de gestiune pentru baza de date o va specifica în mod implicit transparent faţă de utilizator). O tabelă poate avea asociată o singură cheie primară (însă o cheie primară poate fi formată din mai multe atribute). În cazul în care nu se specifică cheia primară şi aceasta este solicitată de o aplicaţie, sistemul de gestiune pentru baze de date va întoarce un index unic cu proprietatea ''NOT NULL''. Dacă cheia primară este formată dintr-un singur atribut de tip întreg, ea poate fi referită şi prin identificatorul ''_rowid''. Într-o tabelă, cheia primară este plasată la început, urmată de indecșii unici, apoi de indecșii non-unici, celelalte atribute fiind poziționate la sfârșit, astfel încât să se determine cu ușurință ce index este folosit și pentru a se detecta duplicatele în cazul în care astfel de valori nu sunt permise.
O **cheie străină** reprezintă o referinţă între o tabelă copil şi o tabelă părinte prin intermediul unuia sau mai multe câmpuri care au acelaşi tip de date precum şi aceeaşi dimensiune (în cazul tipurilor de date şir de caractere nu este necesară respectarea condiţiei cu privire la dimensiune. Pentru tipurile de date non-binare, trebuie respectat şi setul de caractere precum şi modul de asamblare). Atât cheile străine cât şi câmpurile referite trebuie să aibă indecşi unici asociaţi astfel încât să nu fie necesară scanarea întregii tabele și să nu accepte valori nule. Se permite specificarea unei acţiuni referenţiale în tabela copil în momentul în care se produc operaţii de tip ''UPDATE'' sau ''DELETE'' în tabela părinte. Opţiunile disponibile pentru utilizatori sunt:
* ''CASCADE'' – operaţiile realizate asupra înregistrării referite în tabela părinte sunt propagate asupra înregistrărilor din tabela copil;
* ''SET NULL'' – valorile din tabela copil primesc valoarea ''NULL'' atunci când sunt realizate operaţii asupra înregistrărilor referite din tabela părinte;
* ''RESTRICT'' – împiedică realizarea de operaţii asupra înregistrărilor referite din tabela părinte; acest comportament este echivalent cu situaţia în care nu se specifică nici o regulă în mod explicit;
* ''NO ACTION'' – echivalent cu ''RESTRICT''; termenul este folosit pentru menţinerea compatibilităţii cu alte versiuni de sisteme de baze de date;
* ''SET DEFAULT'' – în prezent nu este suportată de motoarele de baze de date din MySQL.
Momentan, clauzele ''MATCH'' nu sunt recunoscute de motoarele MySQL curente, făcând ca eventualele clauze ''ON UPDATE'' sau ''ON DELETE'' ce îi succed să fie ignorate. Conform standardului SQL standard, clauzele ''MATCH'' au rolul de a permite unei chei străine să conţină şi valori de tip ''NULL'', care nu vor referi nici un fel de înregistrare.
=== ALTER TABLE ===
Structura unei tabele poate fi modificată folosind comanda ''ALTER TABLE'':
ALTER [IGNORE] TABLE tbl_name
[alter_specification [, alter_specification] ...]
alter_specification:
| ADD [COLUMN] column_name column_definition [FIRST | AFTER column_name ]
| ADD [COLUMN] (column_name column_definition,...)
| ADD {INDEX|KEY} [index_name]
[index_type] (index_column_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (index_column_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name]
[index_type] (index_column_name,...) [index_option] ...
| ADD FULLTEXT [INDEX|KEY] [index_name] (index_column_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_column_name,...)
reference_definition
| ALTER [COLUMN] column_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_column_name new_column_name column_definition
[FIRST|AFTER column_name]
| MODIFY [COLUMN] column_name column_definition [FIRST | AFTER column_name]
| DROP [COLUMN] column_name
| DROP PRIMARY KEY
| DROP {INDEX|KEY} index_name
| DROP FOREIGN KEY fk_symbol
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO|AS] new_table_name
| ORDER BY column_name [, column_name] ...
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
Prin intermediul instrucţiunii ''ALTER TABLE'' se pot adăuga sau şterge coloane, se poate schimba tipul de date sau dimensiunea unei coloane, se pot adăuga sau şterge constrângeri de tip cheie primară sau străină respectiv index, se poate redenumi o coloană sau chiar tabela însăşi.
De regulă, instrucțiunea ''ALTER TABLE'' crează o copie temporară a tabelei, operează modificările asupra acesteia și atunci când acestea sunt pregătite, șterge tabela originală și redenumește tabela în care au fost realizate actualizările.\\ Operațiile de citire realizate din alte sesiuni sunt disponibile pe tabela originală până la momentul în care se realizează transferul modificărilor.\\ Operațiile de scriere din alte sesiuni sunt amânate până ce se realizează actualizările specificate.
=== RENAME TABLE ===
Redenumirea uneia sau mai multor tabele se poate face şi prin intermediul comenzii ''RENAME'', executată atomic, astfel încât nici o altă sesiune nu pot accesa tabelele implicate în timpul execuției acesteia. Comanda poate fi folosită pentru interschimbarea denumirilor a două tabele sau pentru mutarea unei baze de date în alta (cu condiția ca acestea să fie stocate folosind același sistem de fișiere).
RENAME TABLE table_name TO new_table_name
[, table_name2 TO new_table_name2] ...
Operația este realizată cu succes cu condiția să nu există triggere definite în cadrul tabelei care este redenumită, să nu existe tabele blocate sau tranzacții în desfășurare și să nu fie implicate tabele temporare.
Vizualizările sunt migrate odată cu procesul de redenumire. În schimb, drepturile de acces (privilegiile) existente nu vor fi păstrate.
În situația în care sunt redenumite mai multe tabele simultan, acestea sunt procesate de la stânga la dreapta. Dacă una dintre operațiile de redenumire eșuează, se restaurează starea anterioară, de dinaintea execuției comenzii.
=== DROP TABLE ===
Ştergerea unei tabele (atât a structurii cât și a conținutului) se face prin comanda ''DROP TABLE'':
DROP [TEMPORARY] TABLE [IF EXISTS]
tbl_name [, tbl_name] ...
[RESTRICT | CASCADE]
Clauza ''TEMPORARY'' șterge tabelele temporare, fără a avea nici un impact asupra tranzacțiilor în desfășurare la momentul respectiv și fără a mai verifica drepturile de acces.
Clauzele ''RESTRICT'' și ''CASCADE'' nu au nici un efect, având rolul de a asigura compatibilitatea cu standardul SQL.
=== SHOW TABLES ===
Vizualizarea listei ce conține tabelele (non-temporare) precum şi vizualizările existente în baza de date implicită poate fi realizată prin intermediul comenzii:
SHOW [FULL] TABLES [{FROM | IN} database_name]
[LIKE 'pattern' | WHERE expression]
Clauza ''FULL'' determină afișarea tipului de obiect (''BASE TABLE'' pentru tabelă și ''VIEW'' pentru vizualizare).
Clauza ''LIKE'' permite filtrarea rezultatelor, luând în considerare denumirile care respectă un anumit șablon (expresie regulată).
Clauza ''WHERE'' permite specificarea de condiții folosind atributele incluse în rezultat (în cazul de față, ''Tables_in_database_name'').
=== DESCRIBE ===
Instrucțiunea ''DESCRIBE'' are un efect similar cu ''SHOW COLUMNS'' și oferă informații cu privire la structura tabelei specificate.
DESCRIBE table_name;
Informațiile conținute în rezultatul instrucțiunii ''DESCRIBE'' sunt denumirea atributului, tipul de date asociat, permisiunea de a lua valori de tip ''NULL'', proprietatea de index (''PRI'' - cheie primară, ''UNI'' - index unic, ''MUL'' - index nonunic ce permite mai multe ocurențe ale unei aceleiași valori), valoarea implicită precum și alte proprietăți (''auto_increment'', ''on update CURRENT_TIMESTAMP'').
=== Exemplu ===
Se doreşte crearea unei baze de date ce conţine informaţii despre animalele de companie tratate în cadrul unei clinici veterinare.
Pentru fiecare animal se vor reține informații cu privire la nume, rasă, gen, data naşterii şi eventual data morţii, aparținător și opțional despre părinți (dacă se află și aceștia în evidența aceleiași unități).
De asemenea, pentru fiecare animal se va stoca fișa medicală conținând fiecare consultație, unde se va preciza data la care a fost efectuată, medicul care a realizat examinarea, diagnosticul pus și tratamentul aplicat.
Se proiectează schema conceptuală a bazei de date în care se evidențiază structura fiecărei tabele, cu cheile primare și legăturile de tip cheie străină:
{{ :laboratoare:laborator01_schemaconceptuala_exemplu.jpg?nolink&600 }}
Pe baza acesteia se emit instrucțiunile pentru crearea bazei de date și a tabelelor componente, alegându-se tipuri de date corespunzătoare pentru atribute și impunându-se constrângerile de integritate necesare:
CREATE DATABASE IF NOT EXISTS veterinary_clinic;
USE veterinary_clinic;
CREATE TABLE IF NOT EXISTS breed (
id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
name VARCHAR(50) NOT NULL,
description VARCHAR(1000)
);
CREATE TABLE IF NOT EXISTS owner (
id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(50),
phone_number INT(10) NOT NULL,
bank_account VARCHAR(50) NOT NULL
);
ALTER TABLE owner ADD CONSTRAINT owner_email_format CHECK (email LIKE '%@%.%');
CREATE TABLE IF NOT EXISTS animal (
id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
name VARCHAR(50) NOT NULL,
breed_id INT(10) UNSIGNED,
gender CHAR(1) DEFAULT 'M',
birth_date DATETIME NOT NULL,
death_date DATETIME,
owner_id INT(10) UNSIGNED,
father_id INT(10) UNSIGNED,
mother_id INT(10) UNSIGNED,
FOREIGN KEY(breed_id) REFERENCES breed(id) ON UPDATE CASCADE ON DELETE SET NULL,
FOREIGN KEY(owner_id) REFERENCES owner(id) ON UPDATE CASCADE ON DELETE SET NULL
);
ALTER TABLE animal ADD CONSTRAINT father_id_foreign_key FOREIGN KEY(father_id) REFERENCES animal(id) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE animal ADD CONSTRAINT mother_id_foreign_key FOREIGN KEY(mother_id) REFERENCES animal(id) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE animal ADD CONSTRAINT sex_possible_values CHECK (gender in ('M', 'F'));
CREATE TABLE IF NOT EXISTS doctor (
id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
title VARCHAR(20) NOT NULL,
speciality VARCHAR(20),
code VARCHAR(20) NOT NULL,
email VARCHAR(50),
phone_number INT(10) NOT NULL
);
ALTER TABLE doctor ADD CONSTRAINT doctor_email_format CHECK (email LIKE '%@%.%');
CREATE TABLE IF NOT EXISTS diagnosis (
id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
name VARCHAR(50) NOT NULL,
description VARCHAR(1000) NOT NULL,
severity VARCHAR(20) NOT NULL,
cure VARCHAR(1000) NOT NULL
);
CREATE TABLE IF NOT EXISTS medical_record (
id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
animal_id INT(10) UNSIGNED,
doctor_id INT(10) UNSIGNED,
diagnosis_id INT(10) UNSIGNED,
date DATETIME NOT NULL,
treatment VARCHAR(1000),
FOREIGN KEY(animal_id) REFERENCES animal(id) ON UPDATE CASCADE ON DELETE SET NULL,
FOREIGN KEY(doctor_id) REFERENCES doctor(id) ON UPDATE CASCADE ON DELETE SET NULL,
FOREIGN KEY(diagnosis_id) REFERENCES diagnosis(id) ON UPDATE CASCADE ON DELETE SET NULL
);
==== Operații de manipulare a datelor ====
=== INSERT ===
Adăugarea de informaţii într-o tabelă poate fi realizată printr-una din variantele comenzii ''INSERT'':
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] table_name
[(column_name,...)]
{VALUES | VALUE} ({expression | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE
column_name=expression
[, column_name=expression] ... ]
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] table_name
SET column_name={expression | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE
column_name=expression
[, column_name=expression] ... ]
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] table_name
[(column_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE
column_name=expression
[, column_name=expression] ... ]
Trebuie să existe o corespondență 1-la-1 între denumirile coloanelor și valorile specificate. În cazul în care nu sunt indicate explicit denumirile coloanelor pentru care sunt introduse datele, clauza ''VALUES'' / ''VALUE'' trebuie să specifice valori pentru toate atributele tabelei (în ordinea specificată la definirea tabelei).
Clauza ''SET'' specifică valori pentru fiecare câmp al tabelei care se doreşte completat.
Prin cuvântul-cheie ''DEFAULT'' se precizează faptul că valoarea coloanei este cea implicită. Un comportament similar se obţine atunci când instrucţiunea se apelează fără parametri atât în privinţa numelor de coloane cât şi a valorilor.
O comandă de tipul ''INSET INTO table_name() VALUES();'' are ca efect introducerea unei înregistrări în care toate atributele au valorile implicite.
Pot fi introduse date specificate prin expresii (referind, spre exemplu, valori care sunt derivate din coloanele anterioare - cu excepția celor definite ca ''AUTO_INCREMENT''), realizându-se în mod automat conversii dacă tipurile de date rezultate în urma operaţiilor implicate nu corespund celor pe care le au coloanele respective.
Pot fi specificate valori pentru mai multe înregistrări simultan, însă fiecare grup (corespunzând unui rând din tabelă) trebuie să fie separat prin paranteze şi virgule.
În cazul în care datele provin dintr-o interogare de tip ''SELECT'', motorul bazei de date va indica numărul de înregistrări pe care le-a întors aceasta, precum şi numărul de valori duplicate care nu au fost adăugate şi erorile produse datorită unor incompatibilităţi în privinţa tipurilor de date.
Instrucţiunea ''INSERT'' permite folosirea următoarelor clauze:
* ''DELAYED'' – datele ce se doresc introduse sunt plasate într-o zonă de memorie şi controlul este redat utilizatorului (operaţia este non-blocantă), informaţiile fiind adăugate în tabela respectivă în momentul în care nu există cereri de citire / scriere asupra acesteia (chiar şi în momentul în care sunt introduse datele în cauză, se verifică periodic faptul că nu există alte cereri de citire / scriere asupra tabelei respective, iar în cazul în care se produc astfel de evenimente, transferul din zona de memorie în tabela respectivă este întrerupt, cedându-se accesul proceselor în cauză); directiva este ignorată atunci când datele provin dintr-o interogare de tip ''SELECT'' sau atunci când este folosită clauza ''ON DUPLICATE KEY UPDATE'';
* ''LOW_PRIORITY'' – operaţia de adăugare a datelor devine blocantă, aşteptând după toate cererile de citire / scriere asupra tabelei, până când astfel de solicitări nu mai există; este recomandat să nu se folosească această opţiune, mai ales în mediile distribuite unde accesul concurent este intens;
* ''HIGH_PRIORITY'' – operaţia de adăugare a datelor se execută imediat, chiar înaintea altor cereri de citire / scriere concurente care se desfăşurau atunci când aceasta este realizată;
* ''IGNORE'' – determină ca erorile produse la adăugarea unor înregistrări să nu mai fie semnalate ca atare, continuându-se operaţia chiar şi în cazul în care astfel de incidente se produc; datele care nu respectă constrângerile tabelei nu vor fi însă introduse, emiţându-se eventual nişte avertismente după ce întreaga operaţie s-a încheiat;
* ''ON DUPLICATE KEY UPDATE'' – în cazul în care sunt identificate date care nu respectă constrângerile de tip ''PRIMARY KEY'' / ''UNIQUE'', înregistrarea în cauză este actualizată cu valorile respective.
**Exemple.** Exemple de populare a tabelelor definite anterior folosind instrucțiuni de tip ''INSERT'' ar putea fi:
USE veterinary_clinic;
INSERT LOW_PRIORITY INTO breed (name, description) VALUES
('dog', 'The domestic dog (Canis lupus familiaris, or Canis familiaris) is a member of the Canidae family of the mammalian order Carnivora. The term \"domestic dog\" is generally used for both domesticated and feral varieties. The dog was the first domesticated animal and has been the most widely kept working, hunting, and pet animal in human history. The word \"dog\" can also refer to the male of a canine species, as opposed to the word \"bitch\" which refers to the female of the species.'),
('cat','The domestic cat (Felis catus or Felis silvestris catus) is a small, usually furry, domesticated, and carnivorous mammal. It is often called a housecat when kept as an indoor pet, or simply a cat when there is no need to distinguish it from other felids and felines. Cats are often valued by humans for companionship, and their ability to hunt vermin and household pests.'),
('guinea pig', NULL),
('horse', 'The horse (Equus ferus caballus) is one of two extant subspecies of Equus ferus. It is an odd-toed ungulate mammal belonging to the taxonomic family Equidae. The horse has evolved over the past 45 to 55 million years from a small multi-toed creature into the large, single-toed animal of today. Humans began to domesticate horses around 4000 BC, and their domestication is believed to have been widespread by 3000 BC. Horses in the subspecies caballus are domesticated, although some domesticated populations live in the wild as feral horses. These feral populations are not true wild horses, as this term is used to describe horses that have never been domesticated, such as the endangered Przewalski\'s horse, a separate subspecies, and the only remaining true wild horse. There is an extensive, specialized vocabulary used to describe equine-related concepts, covering everything from anatomy to life stages, size, colors, markings, breeds, locomotion, and behavior.'),
('gold fish', NULL);
INSERT IGNORE INTO owner
SET first_name='Sarah', last_name='LEE', email='sarah.lee@lavabit.com', phone_number='123456789', bank_account='US01AABB0000000001';
INSERT IGNORE INTO owner
SET first_name='William', last_name='LOPEZ', email='william.lopez@aim.com', phone_number='456789123', bank_account='UK02CCDD0000000002';
INSERT IGNORE INTO owner
SET first_name='Samuel', last_name='JONES', email='samuel.jones@fastmail.com', phone_number='789123456', bank_account='DE03EEFF0000000003';
INSERT IGNORE INTO owner
SET first_name='William', last_name='THOMPSON', email='william.thompson@lmyway.com', phone_number='123789456', bank_account='FR04GGHH0000000004';
INSERT IGNORE INTO owner
SET first_name='Richard', last_name='MARTIN', email='richard.martin@myspace.com', phone_number='789456123', bank_account='IT05IIJJ0000000005';
INSERT HIGH_PRIORITY INTO doctor(first_name, last_name, title, speciality, code, email, phone_number)
SELECT d.first_name, d.last_name, t.name, s.name, d.code, d.email, d.phone_number
FROM physician_catalog.doctor d, physician_catalog.title t, physician_catalog.speciality s WHERE t.id = d.title_id AND s.id = d.speciality_id;
INSERT INTO diagnosis VALUES
(NULL, 'giardiasis', 'Giardiasis is a protozoal, parasitic, gastrointestinal zoonotic disease in humans and domestic and wild animals.', 'medium', 'nitroimidazole derivatives, benzimidazole compounds or acridine dyes'),
(NULL, 'rabies', 'Rabies is a severely fatal, viral, neurological disease of mammals.', 'high', 'There is no treatment once the clinical signs appear.'),
(NULL, 'dermatophytosis', 'Dermatophytosis is a fungal skin disease that commonly affects humans as well as wild and domestic animals', 'low', 'Dermatophyte infections are treated with a variety of topical and oral antifungal drugs.'),
(NULL, 'mycobacteriosis', 'Mycobacteriosis is a bacterial, systemic, granulomatous skin disease that occurs in aquarium and culture food fish and can affect humans.', 'low', ' Antibiotic therapy may be warranted to prevent progression to deep infection.'),
(NULL, 'malignant catarrhal fever', 'Malignant catarrhal fever (MCF) is a serious, often fatal, viral disease affecting cattle, bison, deer, moose, exotic ruminants, and pigs.', 'severe', 'There is no cure discovered so far');
=== LOAD DATA INFILE ===
Adăugarea de informaţii într-o tabelă se poate face și prin instrucţiunea ''LOAD DATA INFILE'', caz în care sunt introduse date direct din fişier, la o viteză foarte mare, specificându-se totodată şi formatul datelor din fişier (pentru specificarea valorilor de tip NULL din cadrul unui fişier, se va completa valoarea '\N'.):
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE table_name
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']]
[IGNORE number {LINES | ROWS}]
[(column_name_or_user_var,...)]
[SET column_name = expression,...]
Instrucţiunea presupune că toate datele din fişier respectă acelaşi set de caractere indiferent de tipul de date pe care îl au câmpurile din tabela unde se doresc introduse, astfel încât trebuie verificat faptul că acestea folosesc un set de caractere suportat (nu pot fi încărcate fişiere care folosesc seturile de caractere ''ucs2'', ''utf16'', ''utf16le'' sau ''utf32'').
Comportamentul în cazul folosirii clauzei ''LOW_PRIORITY'' este acelaşi cu al instrucţiunii ''INSERT'', iar în cazul ''CONCURRENT'' este permis accesul altor fire de execuţie asupra tabelei.
Dacă este folosit cuvântul-cheie ''LOCAL'', atunci fişierul este citit de client de pe maşina locală (putând fi indicată atât calea absolută în care se găseşte fişierul cât şi o cale relativă la locaţia de unde a fost lansat în execuţie clientul) şi transmis către server care realizează o copie a acestuia în directorul temporar al sistemului de operare. În cazul în care nu se specifică această clauză, fişierul trebuie să existe pe server la locaţia absolută / relativă indicată. Încărcarea unui fişier de la client este mai lentă întrucât conţinutul său trebuie transmis prin intermediul reţelei de calculatoare, însă execuţia nu este oprită în cazul producerii de erori (violări ale constrângerilor de integritate), acestea fiind semnalate ca avertismente după terminarea operaţiei.
Clauzele ''REPLACE'' şi ''IGNORE'' reglează comportamentul în cazul nerespectării constrângerilor de integritate de tip ''PRIMARY KEY'' şi ''UNIQUE''. În cazul ''REPLACE'', înregistrările ce au aceleaşi valori ale câmpurilor specificate drept cheie primară sau index unic sunt înlocuite iar în cazul ''IGNORE'' acestea sunt trecute cu vederea, fără a se genera o eroare. În cazul în care nu se precizează nici unul dintre aceste cuvinte-cheie, comportamentul va fi dat de clauza ''LOCAL'' (dacă nu este specificată se va genera eroare pentru valorile duplicate, dacă există valorile vor fi ignorate, continându-se execuţia).
Dacă se folosesc atributele ''FIELDS'' | ''COLUMNS'' sau ''LINES'', ele trebuie însoţite de cel puţin unul dintre câmpurile ''TERMINATED BY'', ''ENCLOSED BY'', ''ESCAPED BY'', ''STARTING BY''. Dacă aceste clauze nu sunt specificate, comportamentul implicit este:
FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''
De asemenea, clauza ''IGNORE'' permite omiterea unui număr de rânduri / linii de la începutul fişierului.
În cazul în care nu sunt specificate coloanele tabelului, datele din fişier trebuie să corespundă structurii acestora. Dacă ordinea din fişier este diferită sau acesta conţine numai anumite valori, coloanele trebuie indicate explicit pentru ca asocierea să se facă corect. De asemenea, în loc de nume de coloane pot fi specificate nume de variabile (precedate de caracterul '@') ce se pot folosi ulterior pentru stabilirea valorii unui atribut prin intermediul unei expresii, utilizând clauza ''SET''. Restricţiile cu privire la utilizarea clauzei ''SET'' se referă la faptul că atribuirile nu pot referi decât coloane ale tabelei, valorile asociate putând fi constante sau valori scalare obţinute în urma unor (sub)interogări care nu pot viza însă tabela în care sunt încărcate informaţiile.
Dacă la încărcare sunt identificate prea puţine câmpuri faţă de numărul de atribute al tabelei, coloanele rămase vor fi completate cu valorile implicite. În cazul în care nu au fost specificate valori implicite pentru câmpul respectiv, vor fi completate valorile implicite corespunzătoare tipului de date asociate atributului: 0 pentru date numerice şi date de tip calendaristic respectiv şirul vid (%%''%%) pentru tipul de date şir de caractere. Dacă există prea multe câmpuri în fişier faţă de cele aşteptate, după completarea tuturor atributelor din coloană, datele suplimentare existente pe linia respectivă vor fi ignorate.
Toate valorile sunt considerate şiruri de caractere astfel că nu se vor putea încărca valori de tip numeric pentru câmpuri care au tipul de date ''ENUM'' sau ''SET''. Așadar, este necesar ca acestea să fie definite ca şiruri de caractere pentru a putea fi folosite împreună cu instrucţiunea ''LOAD DATA INFILE''.
**Exemple.** Exemple de populare a tabelelor definite anterior folosind instrucțiuni de tip ''LOAD DATA INFILE'' ar putea fi:
USE veterinary_clinic;
LOAD DATA LOCAL INFILE 'C:\\Users\\Aipi2014\\animals.txt'
INTO TABLE animal
FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' STARTING BY ''
(name, breed_id, gender, birth_date, owner_id, father_id, mother_id);
LOAD DATA LOCAL INFILE '/home/aipi2014/medical_records.txt'
INTO TABLE medical_record
FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' STARTING BY ''
(animal_id, doctor_id, diagnosis_id)
SET date=CURRENT_DATE, treatment='-';
=== UPDATE ===
Modificarea înregistrărilor din tabelă se face prin instrucţiunea ''UPDATE'', care poate fi folosită pentru actualizarea informaţiilor din mai multe tabele simultan (situație în care clauzele ''ORDER BY'' şi ''LIMIT'' nu vor mai putea fi folosite; între tabelele în cauză trebuie să existe o relaţie de tip cheie străină, ea fiind condiţia de identificare a înregistrărilor din clauza ''WHERE''):
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET column_name1={expression1|DEFAULT} [, column_name2={expression2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET column_name1={expression1|DEFAULT} [, column_name2={expression2|DEFAULT}] ...
[WHERE where_condition]
Sunt actualizate datele aferente coloanelor din clauza ''SET'' dintr-una sau mai multe tabele, care îndeplinesc condiţia specificată în clauza ''WHERE''. În cazul în care clauza ''WHERE'' nu este precizată, vor fi actualizate toate înregistrările tabelei (exceptând cazul în care numărul înregistrărilor ce vor fi modificate este condiţionat prin clauza ''LIMIT'').
Rezultatul pe care îl întoarce instrucțiunea este numărul de înregistrări care au fost actualizate.
Modificatorii ''LOW_PRIORITY'' şi ''IGNORE'' au aceeaşi semnificaţie ca în cazul celorlalte instrucţiuni, amânând operaţia până la momentul în care nu mai există alte cereri de acces asupra tabelei respectiv continuând execuţia comenzii chiar dacă se produc unele erori.
Expresia ''SET'' precizează coloanele care trebuie modificate și valorile care le vor fi atribuite (fie o expresie, fie cuvântul cheie ''DEFAULT'' prin care li se va asocia valoarea implicită a atributului / tipului de dată respectiv).
Clauza ''WHERE'' poate folosi:
* operatori aritmetici (''+'', ''-'', ''*'', ''/'', ''%'');
* operatori pentru comparaţie (''='', ''<>'' sau ''!='', ''<'', ''<='', ''>'', ''>='', ''BETWEEN'', ''IN'', ''IS NULL'', ''IS NOT NULL'', ''LIKE'', ''REGEXP'', ''SOUNDS LIKE'');
* operatori logici (''NOT'' sau ''!'', ''AND'' sau ''&&'', ''OR'' sau ''||'', ''XOR'');
* operatori pe biţi (''&'', ''|'', ''%%<<%%'', ''%%>>%%'', ''^'');
Clauza ''ORDER BY'' are drept efect ordonarea procesului de actualizare putând primi ca parametri atributele ''ASC'' sau ''DESC''. O astfel de opţiune poate fi utilă în cazul în care procesarea înregistrărilor în altă ordine ar putea genera încălcări ale constrângerilor de integritate.
Clauza ''LIMIT'' stabileşte numărul de înregistrări care satisfac clauza ''WHERE'' după care se încheie procesul de actualizare, indiferent dacă modificarea acestora a avut sau nu a avut loc.
În cadrul instrucţiunii de actualizare a unei tabele nu pot fi folosite (sub)interogări care folosesc tabela care este modificată.
**Exemple**. Exemple de actualizare a tabelelor definite anterior folosind instrucțiuni de tip ''UPDATE'' ar putea fi:
UPDATE LOW_PRIORITY animal SET birth_date=CURRENT_DATE
WHERE YEAR(CURRENT_TIMESTAMP) - YEAR(birth_date) - (MONTH(CURRENT_TIMESTAMP) < MONTH(birth_date) OR (MONTH(CURRENT_TIMESTAMP) = MONTH(birth_date) AND DAY(CURRENT_TIMESTAMP) < DAY(birth_date))) < 0
ORDER BY birth_date DESC;
UPDATE medical_record mr, diagnosis d
SET mr.treatment = d.cure
WHERE mr.diagnosis_id = d.id;
=== DELETE ===
Ştergerea unor înregistrări dintr-una sau mai multe tabele se face apelând instrucțiunea DELETE:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM table_name
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
table_name[.*] [, table_name[.*]] ...
FROM table_references
[WHERE where_condition]
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
FROM table_name[.*] [, table_name[.*]] ...
USING table_references
[WHERE where_condition]
Instrucţiunea ''DELETE'' şterge un număr de înregistrări dintr-o tabelă care îndeplinesc condiţia specificată în clauza ''WHERE''. Dacă se dorește ștergerea tuturor înregistrărilor dintr-o tabelă, este mai eficient să se utilizeze instrucțiunea ''TRUNCATE TABLE'' decât ''DELETE'' fără parametrul ''WHERE''.
Comanda întoarce numărul de înregistrări care sunt eliminate din tabelă.
Pentru a adresa diferite probleme de performanţă pe care le poate implica această comandă, se poate specifica constrângerea ''LIMIT'' ce permite ca doar un număr de înregistrări să fie şterse. Totodată, poate fi controlată ordinea în care este executată operaţia prin clauza ''ORDER BY''.
Clauzele ''LOW_PRIORITY'' şi ''IGNORE'' au aceeaşi semnificaţie ca şi în cazul celorlalte instrucţiuni de manipulare a datelor, în timp ce atributul ''QUICK'' poate avea un impact pentru viteza cu care este executată operaţia de ştergere prin faptul că se evită compactarea nodurilor de tip frunză pentru arborii B+ asociaţi unor indecşi. În situaţia în care se şterg numeroase înregistrări din tabelă, este recomandat să se folosească clauza ''QUICK'', urmată de instrucţiunea ''OPTIMIZE_TABLE'' care reconstruieşte indecşii.
Pentru câmpurile care au fost definite folosind atributul ''AUTO_INCREMENT'' valorile care au fost şterse nu vor fi alocate în cazul când se adaugă noi valori. Pentru unele motoare ale sistemului de baze de date MySQL, se pot reseta aceste valori în situaţia în care se şterge întregul conţinut al tabelei respective.
Instrucţiunea ''DELETE'' poate fi folosită inclusiv pentru ştergerea de date din mai multe tabele în funcţie de condiţia din clauza ''WHERE''. Există 2 sintaxe pentru această operaţie, una eliminând rândurile din tabelele de dinaintea clauzei ''FROM'', cealaltă eliminând înregistrările din tabelele precizate în clauza ''FROM''. În cadrul acestei operaţii căutările pot implica şi alte tabele.
În cadrul instrucţiunii de ştergere a unei tabele nu pot fi folosite (sub)interogări care folosesc tabela asupra căreia se operează în mod curent.
**Exemple**. Exemple de ștergere a unor înregistrări din cadrul tabelelor definite anterior folosind instrucțiuni de tip ''DELETE'' ar putea fi:
DELETE QUICK breed, animal
FROM breed, animal
WHERE animal.breed_id = breed.id AND breed.description IS NULL;
DELETE LOW_PRIORITY IGNORE FROM animal, owner
USING animal INNER JOIN owner ON animal.owner_id=owner.id
WHERE animal.death_date IS NOT NULL;
=== SELECT ===
Interogarea uneia sau mai multe tabele dintr-o bază de date se realizează prin instrucțiunea ''SELECT'':
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expression [, select_expression ...]
[FROM table_references
[WHERE where_condition]
[GROUP BY {column_name | expression | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {column_name | expression | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]
export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]]
O instrucţiune de tip ''SELECT'' trebuie să includă cel puţin o expresie, aceasta nefiind legată în mod necesar de atributele unei tabele. Ea poate include coloanele uneia sau mai multor tabele (eventual prefixate de numele tabelelor din care fac parte, în caz de ambiguitate). O expresie poate avea asociat un alias (introdus prin cuvântul-cheie ''AS'' (opţional) şi o denumire), acesta putând fi folosit ulterior pentru expresiile ''GROUP BY'', ''HAVING'' sau ''ORDER BY'', dar nu şi pentru ''WHERE'' (pentru că este posibil să nu fi fost evaluat la momentul respectiv). Dacă se dorește vizualizarea de informații cu privire la toate câmpurile unei / unor tabele, se va folosi masca ''*''.
Ca mod de lucru, atunci când se folosesc alias-uri, este recomandat să se folosească clauza ''AS'' întrucât permite semnalarea de erori dacă se omite o virgulă între câmpurile ale căror valori se doresc a fi vizualizate (altfel, acestea pot fi interpretate ca fiind alias-uri).
Clauza ''FROM'' indică tabelele ale căror date se doresc a fi vizualizate, acestea putând avea asociat la rândul lor un alias (prescurtare) ce poate fi folosit pentru prefixarea numelor de atribute.
Clauza ''WHERE'' specifică condiţiile ce trebuie îndeplinite de atributele ale căror valori se doresc a fi afişate. Dacă această clauză lipseşte, sunt selectate toate informaţiile în cauză.
În situaţia în care se doreşte gruparea informaţiilor după valorile unui anumit câmp, se poate folosi clauza ''GROUP BY''. Aceasta realizează suplimentar şi ordonarea valorilor, ceea ce poate determina o încetinire a vitezei de execuţie. Pentru a forţa motorul bazei de date să nu realizeze şi operaţia de sortare, trebuie adăugată clauza ''ORDER BY NULL''. Parametrii pe care îi poate primi clauza sunt denumiri (respectiv alias-uri) de coloane precum şi valori care indică ordinea lor în expresia ''SELECT''. Clauza ''WITH ROLLUP'' permite vizualizarea de totaluri pentru categoriile care au fost grupate.
Clauza ''ORDER BY'' sortează conţinutul câmpurilor pe care le primeşte ca parametri crescător (implicit) sau descrescător, după cum sunt specificate cuvintele-cheie ''ASC'' sau ''DESC''. În cazul interogărilor imbricate, dacă se realizează sortări după acelaşi câmp, va prevala opţiunea indicată în cazul interogării exterioare, dacă ordinea specificată este diferită.
Clauza ''HAVING'' este folosită de regulă pentru coloane specificate în clauza ''GROUP BY'' sau pentru cele utilizate împreună cu funcţii agregate.
Dacă se doreşte restricţionarea numărului de rezultate întoarse, poate fi utilizată clauza ''LIMIT'' ce poate primi unul sau doi parametri, indicându-se eventual şi înregistrarea de la care se doreşte căutarea, numerotarea rândurilor făcându-se de la 0. Cu alte cuvinte ''LIMIT 0, row_count'' = ''LIMIT row_count''.
Clauza ''PROCEDURE'' specifică rutina stocată care ar trebui să proceseze datele rezultate în urma interogării.
Funcţionalitatea ''SELECT ... INTO'' este utilizată pentru salvarea datelor într-una (sau mai multe) variabile, într-un fişier (folosind un anumit format) sau pentru reţinerea unei singure înregistrări într-un fişier (''DUMPFILE'') fără nici un fel de formatare.
Într-un context distribuit, pot fi folosite clauzele ''FOR UPDATE'' care blochează pentru citire și scriere înregistrările selectate, respectiv ''LOCK IN SHARE MODE'' ce permite citirea acestora, dar nu şi actualizarea / modificarea lor până la sfârşitul tranzacţiei.
Alte clauze ce pot fi folosite cu instrucţiunea ''SELECT'' sunt:
* ''ALL'' (valoare implicită), respectiv ''DISTINCT'' specifică includerea duplicatelor în rezultatele întoarse sau selectarea exclusivă a valorilor unice;
* ''HIGH_PRIORITY'' oferă operaţiei prioritate asupra altor comenzi, de aceea trebuie să fie utilizată doar pentru acele instrucţiuni urgente, al căror timp de execuţie nu este foarte mare;
* ''STRAIGHT_JOIN'' forţează modulul de optimizare a bazei de date să realizeze asocierea tabelelor în ordinea în care acestea sunt precizate în clauza ''FROM'';
* ''SQL_SMALL_RESULT'' şi ''SQL_BIG_RESULT'' sunt utilizate cu ''DISTINCT'' şi ''GROUP BY'' pentru a transmite modulului de optimizare faptul că rezultatul are o dimensiune mică respectiv mare astfel încât acesta să poată folosi pentru reţinerea acestuia tabele temporare pe disc (ceea ce implică o viteză de execuţie crescută) respectiv realizarea operaţiei de sortare după atributele indicate şi evitarea utilizării de resurse suplimentare;
* ''SQL_BUFFER_RESULT'' permite utilizarea de tabele temporare eliberând resurse dacă transmiterea rezultatului la client durează mai mult timp; opţiunea nu poate fi folosită pentru interogări imbricate sau în cazul când se foloseşte clauza ''UNION'';
* ''SQL_CALC_FOUND_ROWS'' forţează calcularea numărului de rezultate întoarse, făcând abstracţie de constrângerea ''LIMIT'';
* ''SQL_CACHE'' şi ''SQL_NO_CACHE'' face ca rezultatul întors să fie reţinut într-o zonă de memorie asociată interogărilor în cazul în care nu există deja (ca urmare a altei operaţii), respectiv nu verifică această posibilitate.
**Exemplu**. În situația în care se dorește afișarea listei de animale pentru care se afișează informații precum denumirea, rasa, sexul, vârsta, numele și prenumele aparținătorului, numele părinților (dacă sunt disponibile), denumirile bolilor de care au suferit, numele și prenumele medicilor care i-au tratat, limitând rezultatele la înregistrările valide (animale cu data nașterii în trecut), s-ar putea folosi următoarea interogare:
SELECT a.name AS name,
b.name AS breed,
a.gender AS gender,
YEAR(CURRENT_TIMESTAMP) - YEAR(a.birth_date) - (MONTH(CURRENT_TIMESTAMP) < MONTH(a.birth_date) OR (MONTH(CURRENT_TIMESTAMP) = MONTH(a.birth_date) AND DAY(CURRENT_TIMESTAMP) < DAY(a.birth_date))) AS age,
CONCAT(o.first_name, ' ' , o.last_name) AS owner,
COALESCE((SELECT name FROM animal where id=COALESCE(a.father_id,'0')),'-') AS father,
COALESCE((SELECT name FROM animal where id=COALESCE(a.mother_id,'0')),'-') AS mother,
(SELECT GROUP_CONCAT(DISTINCT d.name) FROM diagnosis d, medical_record mr WHERE d.id = mr.diagnosis_id AND mr.animal_id=a.id) AS diseases,
(SELECT GROUP_CONCAT(DISTINCT CONCAT(d.first_name,' ', d.last_name)) FROM doctor d, medical_record mr WHERE d.id = mr.doctor_id AND mr.animal_id=a.id) AS physicians
FROM animal a, breed b, owner o
WHERE b.id = a.breed_id AND o.id = a.owner_id
HAVING age > 0;
+-------+-------+--------+------+------------------+--------+--------+---------------------------+----------------------------------------------+
| name | breed | gender | age | owner | father | mother | diseases | physicians |
+-------+-------+--------+------+------------------+--------+--------+---------------------------+----------------------------------------------+
| Rocky | dog | M | 13 | William THOMPSON | - | - | malignant catarrhal fever | Harry LEWIS,Thomas CARTER |
| Koby | dog | M | 9 | William THOMPSON | Rocky | - | NULL | NULL |
| Hutch | horse | M | 4 | Richard MARTIN | - | - | rabies | Richard ALLEN,Rachel RODRIGUEZ,Thomas CARTER |
+-------+-------+--------+------+------------------+--------+--------+---------------------------+----------------------------------------------+
3 rows in set (0.00 sec)
== Joncțiuni între tabele ==
O interogare poate fi făcută pe mai multe tabele din baza de date între care există o relaţie, o astfel de operație purtând numele de joncțiune (//eng.// join).
În MySQL, sintaxa pentru realizarea unor astfel de asocieri este:
table_reference:
table_factor
| join_table
table_factor:
table_name [PARTITION (partition_names)]
[[AS] alias] [index_hint_list]
| table_subquery [AS] alias
| ( table_references )
join_table:
table_reference [INNER | CROSS] JOIN table_factor [join_condition]
| table_reference STRAIGHT_JOIN table_factor
| table_reference STRAIGHT_JOIN table_factor ON conditional_expr
| table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition
| table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor
join_condition:
ON conditional_expr
| USING (column_list)
Există mai multe tipuri de joncțiuni ce se pot stabili între tabele:
* **cross-join**, rezultatul fiind produsul cartezian al tuplurilor din cadrul tabelelor implicate în operaţie; fie ''t1'' şi ''t2'' tabelele pentru care se realizeaza cross-join, având ''m'' şi respectiv ''n'' înregistrări; rezultatul interogării conţine ''m x n'' înregistrări;
SELECT * FROM animal CROSS JOIN owner;
+----+-----------+----------+--------+---------------------+------------+----------+-----------+-----------+----+------------+-----------+-----------------------------+--------------+----------------------+
| id | name | breed_id | gender | birth_date | death_date | owner_id | father_id | mother_id | id | first_name | last_name | email | phone_number | bank_account |
+----+-----------+----------+--------+---------------------+------------+----------+-----------+-----------+----+------------+-----------+-----------------------------+--------------+----------------------+
| 1 | Rocky | 1 | M | 2001-01-20 10:00:00 | NULL | 4 | NULL | NULL | 1 | Sarah | LEE | sarah.lee@lavabit.com | 123456789 | US01AABB0000000001 |
| 1 | Rocky | 1 | M | 2001-01-20 10:00:00 | NULL | 4 | NULL | NULL | 2 | William | LOPEZ | william.lopez@aim.com | 456789123 | UK02CCDD0000000002 |
| 1 | Rocky | 1 | M | 2001-01-20 10:00:00 | NULL | 4 | NULL | NULL | 3 | Samuel | JONES | samuel.jones@fastmail.com | 789123456 | DE03EEFF0000000003 |
| 1 | Rocky | 1 | M | 2001-01-20 10:00:00 | NULL | 4 | NULL | NULL | 4 | William | THOMPSON | william.thompson@lmyway.com | 123789456 | FR04GGHH0000000004 |
| 1 | Rocky | 1 | M | 2001-01-20 10:00:00 | NULL | 4 | NULL | NULL | 6 | Joseph | WILLSON | joseph.willson@fanbox.com | 789456123 | IT05IIIJJJ00000005 |
| 2 | Cleopatra | 5 | F | 2031-12-20 14:00:00 | NULL | 3 | NULL | NULL | 1 | Sarah | LEE | sarah.lee@lavabit.com | 123456789 | US01AABB0000000001 |
| 2 | Cleopatra | 5 | F | 2031-12-20 14:00:00 | NULL | 3 | NULL | NULL | 2 | William | LOPEZ | william.lopez@aim.com | 456789123 | UK02CCDD0000000002 |
| 2 | Cleopatra | 5 | F | 2031-12-20 14:00:00 | NULL | 3 | NULL | NULL | 3 | Samuel | JONES | samuel.jones@fastmail.com | 789123456 | DE03EEFF0000000003 |
| 2 | Cleopatra | 5 | F | 2031-12-20 14:00:00 | NULL | 3 | NULL | NULL | 4 | William | THOMPSON | william.thompson@lmyway.com | 123789456 | FR04GGHH0000000004 |
| 2 | Cleopatra | 5 | F | 2031-12-20 14:00:00 | NULL | 3 | NULL | NULL | 6 | Joseph | WILLSON | joseph.willson@fanbox.com | 789456123 | IT05IIIJJJ00000005 |
| 3 | Lola | 2 | F | 2015-06-20 12:00:00 | NULL | 2 | NULL | NULL | 1 | Sarah | LEE | sarah.lee@lavabit.com | 123456789 | US01AABB0000000001 |
| 3 | Lola | 2 | F | 2015-06-20 12:00:00 | NULL | 2 | NULL | NULL | 2 | William | LOPEZ | william.lopez@aim.com | 456789123 | UK02CCDD0000000002 |
| 3 | Lola | 2 | F | 2015-06-20 12:00:00 | NULL | 2 | NULL | NULL | 3 | Samuel | JONES | samuel.jones@fastmail.com | 789123456 | DE03EEFF0000000003 |
| 3 | Lola | 2 | F | 2015-06-20 12:00:00 | NULL | 2 | NULL | NULL | 4 | William | THOMPSON | william.thompson@lmyway.com | 123789456 | FR04GGHH0000000004 |
| 3 | Lola | 2 | F | 2015-06-20 12:00:00 | NULL | 2 | NULL | NULL | 6 | Joseph | WILLSON | joseph.willson@fanbox.com | 789456123 | IT05IIIJJJ00000005 |
| 4 | Hutch | 4 | M | 2010-03-20 11:00:00 | NULL | NULL | NULL | NULL | 1 | Sarah | LEE | sarah.lee@lavabit.com | 123456789 | US01AABB0000000001 |
| 4 | Hutch | 4 | M | 2010-03-20 11:00:00 | NULL | NULL | NULL | NULL | 2 | William | LOPEZ | william.lopez@aim.com | 456789123 | UK02CCDD0000000002 |
| 4 | Hutch | 4 | M | 2010-03-20 11:00:00 | NULL | NULL | NULL | NULL | 3 | Samuel | JONES | samuel.jones@fastmail.com | 789123456 | DE03EEFF0000000003 |
| 4 | Hutch | 4 | M | 2010-03-20 11:00:00 | NULL | NULL | NULL | NULL | 4 | William | THOMPSON | william.thompson@lmyway.com | 123789456 | FR04GGHH0000000004 |
| 4 | Hutch | 4 | M | 2010-03-20 11:00:00 | NULL | NULL | NULL | NULL | 6 | Joseph | WILLSON | joseph.willson@fanbox.com | 789456123 | IT05IIIJJJ00000005 |
| 5 | Logan | 3 | M | 2020-09-20 13:00:00 | NULL | 1 | NULL | NULL | 1 | Sarah | LEE | sarah.lee@lavabit.com | 123456789 | US01AABB0000000001 |
| 5 | Logan | 3 | M | 2020-09-20 13:00:00 | NULL | 1 | NULL | NULL | 2 | William | LOPEZ | william.lopez@aim.com | 456789123 | UK02CCDD0000000002 |
| 5 | Logan | 3 | M | 2020-09-20 13:00:00 | NULL | 1 | NULL | NULL | 3 | Samuel | JONES | samuel.jones@fastmail.com | 789123456 | DE03EEFF0000000003 |
| 5 | Logan | 3 | M | 2020-09-20 13:00:00 | NULL | 1 | NULL | NULL | 4 | William | THOMPSON | william.thompson@lmyway.com | 123789456 | FR04GGHH0000000004 |
| 5 | Logan | 3 | M | 2020-09-20 13:00:00 | NULL | 1 | NULL | NULL | 6 | Joseph | WILLSON | joseph.willson@fanbox.com | 789456123 | IT05IIIJJJ00000005 |
| 6 | Koby | 1 | M | 2005-06-20 12:00:00 | NULL | 4 | 1 | NULL | 1 | Sarah | LEE | sarah.lee@lavabit.com | 123456789 | US01AABB0000000001 |
| 6 | Koby | 1 | M | 2005-06-20 12:00:00 | NULL | 4 | 1 | NULL | 2 | William | LOPEZ | william.lopez@aim.com | 456789123 | UK02CCDD0000000002 |
| 6 | Koby | 1 | M | 2005-06-20 12:00:00 | NULL | 4 | 1 | NULL | 3 | Samuel | JONES | samuel.jones@fastmail.com | 789123456 | DE03EEFF0000000003 |
| 6 | Koby | 1 | M | 2005-06-20 12:00:00 | NULL | 4 | 1 | NULL | 4 | William | THOMPSON | william.thompson@lmyway.com | 123789456 | FR04GGHH0000000004 |
| 6 | Koby | 1 | M | 2005-06-20 12:00:00 | NULL | 4 | 1 | NULL | 6 | Joseph | WILLSON | joseph.willson@fanbox.com | 789456123 | IT05IIIJJJ00000005 |
| 7 | Ivory | 2 | F | 2025-06-20 14:00:00 | NULL | 2 | NULL | 3 | 1 | Sarah | LEE | sarah.lee@lavabit.com | 123456789 | US01AABB0000000001 |
| 7 | Ivory | 2 | F | 2025-06-20 14:00:00 | NULL | 2 | NULL | 3 | 2 | William | LOPEZ | william.lopez@aim.com | 456789123 | UK02CCDD0000000002 |
| 7 | Ivory | 2 | F | 2025-06-20 14:00:00 | NULL | 2 | NULL | 3 | 3 | Samuel | JONES | samuel.jones@fastmail.com | 789123456 | DE03EEFF0000000003 |
| 7 | Ivory | 2 | F | 2025-06-20 14:00:00 | NULL | 2 | NULL | 3 | 4 | William | THOMPSON | william.thompson@lmyway.com | 123789456 | FR04GGHH0000000004 |
| 7 | Ivory | 2 | F | 2025-06-20 14:00:00 | NULL | 2 | NULL | 3 | 6 | Joseph | WILLSON | joseph.willson@fanbox.com | 789456123 | IT05IIIJJJ00000005 |
+----+-----------+----------+--------+---------------------+------------+----------+-----------+-----------+----+------------+-----------+-----------------------------+--------------+----------------------+
35 rows in set (0.00 sec)
* **inner-join**, ce include înregistrările din tabele pentru care există corespondențe în privința atributului pe care se face asocierea;
SELECT * FROM animal INNER JOIN owner ON animal.owner_id = owner.id
+----+-----------+----------+--------+---------------------+------------+----------+-----------+-----------+----+------------+-----------+-----------------------------+--------------+--------------------+
| id | name | breed_id | gender | birth_date | death_date | owner_id | father_id | mother_id | id | first_name | last_name | email | phone_number | bank_account |
+----+-----------+----------+--------+---------------------+------------+----------+-----------+-----------+----+------------+-----------+-----------------------------+--------------+--------------------+
| 5 | Logan | 3 | M | 2020-09-20 13:00:00 | NULL | 1 | NULL | NULL | 1 | Sarah | LEE | sarah.lee@lavabit.com | 123456789 | US01AABB0000000001 |
| 3 | Lola | 2 | F | 2015-06-20 12:00:00 | NULL | 2 | NULL | NULL | 2 | William | LOPEZ | william.lopez@aim.com | 456789123 | UK02CCDD0000000002 |
| 7 | Ivory | 2 | F | 2025-06-20 14:00:00 | NULL | 2 | NULL | 3 | 2 | William | LOPEZ | william.lopez@aim.com | 456789123 | UK02CCDD0000000002 |
| 2 | Cleopatra | 5 | F | 2031-12-20 14:00:00 | NULL | 3 | NULL | NULL | 3 | Samuel | JONES | samuel.jones@fastmail.com | 789123456 | DE03EEFF0000000003 |
| 1 | Rocky | 1 | M | 2001-01-20 10:00:00 | NULL | 4 | NULL | NULL | 4 | William | THOMPSON | william.thompson@lmyway.com | 123789456 | FR04GGHH0000000004 |
| 6 | Koby | 1 | M | 2005-06-20 12:00:00 | NULL | 4 | 1 | NULL | 4 | William | THOMPSON | william.thompson@lmyway.com | 123789456 | FR04GGHH0000000004 |
+----+-----------+----------+--------+---------------------+------------+----------+-----------+-----------+----+------------+-----------+-----------------------------+--------------+--------------------+
6 rows in set (0.00 sec)
Tot tipuri de inner-join sunt și:
* **equi-join**, în care condiția dintre atributele prin intermediul cărora se face asocierea dintre tabele trebuie să fie reprezentată de o operație de egalitate;
* **natural-join**, care încearcă să realizeze asocierea dintre tabele pe baza unui atribut având aceeași denumire; în situația în care acesta nu este identificat, rezultatul este produsul cartezian, motiv pentru care folosirea acestui tip de joncțiune nu este foarte răspândită.
În MySQL, există o echivalenţă între ''JOIN'', ''CROSS JOIN'' şi ''INNER JOIN'' (spre diferenţă de SQL standard în care există nişte mici diferenţe de sintaxă) căci toate produc ca rezultat produsul cartezian, în lipsa specificării condiţiei.
* **outer-join**, care include înregistrările comune unei laturi a legăturii, completând câmpurile care nu au corespondent pe cealaltă latură cu ''NULL''; în acest caz, se pot folosi cuvintele cheie ''LEFT'' | ''RIGHT'' ''JOIN'', specificând partea relaţiei ce va include toate rezultatele.
* left
SELECT * FROM animal LEFT OUTER JOIN owner ON animal.owner_id = owner.id;
+----+-----------+----------+--------+---------------------+------------+----------+-----------+-----------+------+------------+-----------+-----------------------------+--------------+--------------------+
| id | name | breed_id | gender | birth_date | death_date | owner_id | father_id | mother_id | id | first_name | last_name | email | phone_number | bank_account |
+----+-----------+----------+--------+---------------------+------------+----------+-----------+-----------+------+------------+-----------+-----------------------------+--------------+--------------------+
| 5 | Logan | 3 | M | 2020-09-20 13:00:00 | NULL | 1 | NULL | NULL | 1 | Sarah | LEE | sarah.lee@lavabit.com | 123456789 | US01AABB0000000001 |
| 3 | Lola | 2 | F | 2015-06-20 12:00:00 | NULL | 2 | NULL | NULL | 2 | William | LOPEZ | william.lopez@aim.com | 456789123 | UK02CCDD0000000002 |
| 7 | Ivory | 2 | F | 2025-06-20 14:00:00 | NULL | 2 | NULL | 3 | 2 | William | LOPEZ | william.lopez@aim.com | 456789123 | UK02CCDD0000000002 |
| 2 | Cleopatra | 5 | F | 2031-12-20 14:00:00 | NULL | 3 | NULL | NULL | 3 | Samuel | JONES | samuel.jones@fastmail.com | 789123456 | DE03EEFF0000000003 |
| 1 | Rocky | 1 | M | 2001-01-20 10:00:00 | NULL | 4 | NULL | NULL | 4 | William | THOMPSON | william.thompson@lmyway.com | 123789456 | FR04GGHH0000000004 |
| 6 | Koby | 1 | M | 2005-06-20 12:00:00 | NULL | 4 | 1 | NULL | 4 | William | THOMPSON | william.thompson@lmyway.com | 123789456 | FR04GGHH0000000004 |
| 4 | Hutch | 4 | M | 2010-03-20 11:00:00 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+----+-----------+----------+--------+---------------------+------------+----------+-----------+-----------+------+------------+-----------+-----------------------------+--------------+--------------------+
7 rows in set (0.00 sec)
\\ Sunt incluse toate înregistrările din tabela ''animal'', pentru cele care nu au corespondent în tabela ''owner'' completându-se atributele respective cu valori ''NULL''.
* right
SELECT * FROM animal RIGHT OUTER JOIN owner ON animal.owner_id = owner.id;
+------+-----------+----------+--------+---------------------+------------+----------+-----------+-----------+----+------------+-----------+-----------------------------+--------------+----------------------+
| id | name | breed_id | gender | birth_date | death_date | owner_id | father_id | mother_id | id | first_name | last_name | email | phone_number | bank_account |
+------+-----------+----------+--------+---------------------+------------+----------+-----------+-----------+----+------------+-----------+-----------------------------+--------------+----------------------+
| 5 | Logan | 3 | M | 2020-09-20 13:00:00 | NULL | 1 | NULL | NULL | 1 | Sarah | LEE | sarah.lee@lavabit.com | 123456789 | US01AABB0000000001 |
| 3 | Lola | 2 | F | 2015-06-20 12:00:00 | NULL | 2 | NULL | NULL | 2 | William | LOPEZ | william.lopez@aim.com | 456789123 | UK02CCDD0000000002 |
| 7 | Ivory | 2 | F | 2025-06-20 14:00:00 | NULL | 2 | NULL | 3 | 2 | William | LOPEZ | william.lopez@aim.com | 456789123 | UK02CCDD0000000002 |
| 2 | Cleopatra | 5 | F | 2031-12-20 14:00:00 | NULL | 3 | NULL | NULL | 3 | Samuel | JONES | samuel.jones@fastmail.com | 789123456 | DE03EEFF0000000003 |
| 1 | Rocky | 1 | M | 2001-01-20 10:00:00 | NULL | 4 | NULL | NULL | 4 | William | THOMPSON | william.thompson@lmyway.com | 123789456 | FR04GGHH0000000004 |
| 6 | Koby | 1 | M | 2005-06-20 12:00:00 | NULL | 4 | 1 | NULL | 4 | William | THOMPSON | william.thompson@lmyway.com | 123789456 | FR04GGHH0000000004 |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 6 | Joseph | WILLSON | joseph.willson@fanbox.com | 789456123 | IT05IIIJJJ00000005 |
+------+-----------+----------+--------+---------------------+------------+----------+-----------+-----------+----+------------+-----------+-----------------------------+--------------+----------------------+
7 rows in set (0.00 sec)
\\ Sunt incluse toate înregistrările din tabela ''owner'', pentru cele care nu au corespondent în tabela ''animal'' completându-se atributele respective cu valori ''NULL''.
În MySQL ''NATURAL JOIN'' este echivalent cu ''INNER JOIN'' sau ''LEFT JOIN'' folosit împreună cu clauza ''USING'' în care sunt incluse toate coloanele care există în ambele tabele.
De asemenea, ''STRAIGHT_JOIN'' este echivalent cu ''JOIN'' cu precizarea că tabela din stânga este citită întotdeauna înaintea tabelei din dreapta, această funcționalitate putând fi utilizată în cazul în care modulul de optimizare foloseşte o ordine greşită.
Clauza ''ON'' specifică câmpurile în funcţie de care se face asocierea dintre tabele, clauza ''USING'' putând fi folosită în situaţia în care denumirea atributelor este aceeaşi în toate tabelele care sunt incluse în această asociere.
Tipuri particulare de asocieri sunt:
* **self-join**, care implică duplicarea tabelei prin utilizarea de alias-uri;
SELECT * FROM animal a1 JOIN animal a2 ON a1.father_id = a2.id OR a1.mother_id = a2.id;
+----+-------+----------+--------+---------------------+------------+----------+-----------+-----------+----+-------+----------+--------+---------------------+------------+----------+-----------+-----------+
| id | name | breed_id | gender | birth_date | death_date | owner_id | father_id | mother_id | id | name | breed_id | gender | birth_date | death_date | owner_id | father_id | mother_id |
+----+-------+----------+--------+---------------------+------------+----------+-----------+-----------+----+-------+----------+--------+---------------------+------------+----------+-----------+-----------+
| 6 | Koby | 1 | M | 2005-06-20 12:00:00 | NULL | 4 | 1 | NULL | 1 | Rocky | 1 | M | 2001-01-20 10:00:00 | NULL | 4 | NULL | NULL |
| 7 | Ivory | 2 | F | 2025-06-20 14:00:00 | NULL | 2 | NULL | 3 | 3 | Lola | 2 | F | 2015-06-20 12:00:00 | NULL | 2 | NULL | NULL |
+----+-------+----------+--------+---------------------+------------+----------+-----------+-----------+----+-------+----------+--------+---------------------+------------+----------+-----------+-----------+
* **unions** care presupune adăugarea tuturor înregistrărilor din tabele pentru a determina suma compozită a acestora (numărul de atribute întoarse și denumirea acestora trebuie să fie aceeași pentru a se permite realizarea acestei operații)
SELECT first_name, last_name, email, phone_number, 'Pet Owner' AS role FROM owner
UNION
SELECT first_name, last_name, email, phone_number, 'Physician' AS role FROM doctor;
+------------+-----------+------------------------------+--------------+-----------+
| first_name | last_name | email | phone_number | role |
+------------+-----------+------------------------------+--------------+-----------+
| Sarah | LEE | sarah.lee@lavabit.com | 123456789 | Pet Owner |
| William | LOPEZ | william.lopez@aim.com | 456789123 | Pet Owner |
| Samuel | JONES | samuel.jones@fastmail.com | 789123456 | Pet Owner |
| William | THOMPSON | william.thompson@lmyway.com | 123789456 | Pet Owner |
| Joseph | WILLSON | joseph.willson@fanbox.com | 789456123 | Pet Owner |
| Thomas | CARTER | thomas.carter@google.com | 112233 | Physician |
| Jessica | WHITE | jessica.white@aim.com | 445566 | Physician |
| Rachel | RODRIGUEZ | rachel.rodrigues@hotmail.com | 778899 | Physician |
| Harry | LEWIS | harry.lewis@live.com | 113355 | Physician |
| Richard | ALLEN | richard.allen@space.com | 557799 | Physician |
+------------+-----------+------------------------------+--------------+-----------+
10 rows in set (0.00 sec)
== Interogări imbricate ==
Alternativ la operaţiile de asociere complexe între tabele pot fi folosite interogări imbricate, acestea având avantajul că pot fi înţelese mai uşor având totodată o structură mai flexibilă.
**a.** O **subinterogare** poate întoarce o valoare (un scalar), un rând, o coloană sau un tabel. Ca subinterogări pot fi folosite operaţiile ''INSERT'', ''UPDATE'', ''DELETE'' sau ''SELECT'' cu cele mai multe dintre clauzele lor. O restricţie ce se aplică în acest caz este reprezentată de faptul că într-o interogare internă nu se pot realiza operaţii asupra unei tabele care este folosită şi în interogarea externă.
Cel mai frecvent, subinterogările sunt folosite pentru a realiza comparaţii, având forma:
non_subquery_operand comparison_operator (subquery)
în cazul în care subinterogarea întoarce o singură valoare.
De regulă, operatorii de comparaţie folosiți sunt %%''=''%%, %%''<''%%, %%''>''%%, %%''<=''%%, %%''>=''%%, %%''<>''%%, %%''!=''%%.
**Exemplu**. Determinarea animalului de companie cu vârsta cea mai mare aflat în evidența clinicii veterinare poate fi realizată prin instrucțiunea:
SELECT a.name AS name,
YEAR(CURRENT_TIMESTAMP) - YEAR(a.birth_date) - (MONTH(CURRENT_TIMESTAMP) < MONTH(a.birth_date) OR (MONTH(CURRENT_TIMESTAMP) = MONTH(a.birth_date) AND DAY(CURRENT_TIMESTAMP) < DAY(a.birth_date))) AS age
FROM animal a
WHERE birth_date = (SELECT MIN(birth_date) FROM animal);
Când subinterogarea întoarce mai multe valori, pot fi folosite cuvintele-cheie ''ANY'', ''SOME'', ''IN'' respectiv ''ALL'':
operand comparison_operator ANY (subquery)
operand IN (subquery)
operand comparison_operator SOME (subquery)
operand comparison_operator ALL (subquery)
''SOME'' este un alias pentru ''ANY''. Comparaţiile incluzând aceste cuvinte-cheie sunt îndeplinite dacă condiţiile sunt satisfăcute pentru unele dintre valorile incluse în mulţimea respectivă. În cazul lui ''ALL'', condiţiile trebuie satisfăcute pentru toate valorile din mulţime. Din acest punct de vedere ''IN'' este un alias pentru ''= ANY'' sau ''= SOME'', în timp ce ''NOT IN'' este un alias pentru ''!= ALL''.
**Exemplu**. Determinarea animalului de companie cu vârsta cea mai mare aflat în evidența clinicii veterinare poate fi realizată si prin instrucțiunea:
SELECT a.name AS name,
YEAR(CURRENT_TIMESTAMP) - YEAR(a.birth_date) - (MONTH(CURRENT_TIMESTAMP) < MONTH(a.birth_date) OR (MONTH(CURRENT_TIMESTAMP) = MONTH(a.birth_date) AND DAY(CURRENT_TIMESTAMP) < DAY(a.birth_date))) AS age
FROM animal a
WHERE birth_date <= ALL(SELECT birth_date FROM animal);
Pentru subinterogările care pot întoarce unul sau mai multe rânduri pot fi folosite clauzele ''EXISTS'', respectiv ''NOT EXISTS'';
SELECT ... FROM table_name WHERE EXISTS (SELECT ... FROM table_name);
Astfel de subinterogări pot fi folosite spre a afla categoriile de înregistrări care îndeplinesc o anumită condiţie.
**Exemplu**. Determinarea animalului de companie pentru care au existat consultații în cadrul clinicii veterinare poate fi realizată si prin instrucțiunea:
SELECT a.name AS name,
b.name AS breed
FROM animal a INNER JOIN breed b ON a.breed_id=b.id
WHERE EXISTS (SELECT * FROM medical_record mr WHERE mr.animal_id = a.id);
Pentru situaţia în care interogarea internă (subinterogarea) foloseşte informaţii din interogarea externă, se spune că **interogările sunt corelate**.
**b.** **Subinterogările pot fi apelate şi în clauza ''FROM''** în situaţia în care selecţia se face dintr-un subset de date al unei tabele sau dintr-un set de date obţinut prin intermediul unor prelucrări:
SELECT ... FROM (subquery) [AS] name ...
Rezultatul interogării imbricate trebuie să aibă un nume asociat întrucât orice tabelă trebuie să poată fi identificată. De asemenea, orice coloană dintr-o subinterogare trebuie să aibă asociat un nume unic. În această situaţie nu pot fi folosite interogări corelate cu excepţia cazului în care sunt utilizate cu clauza ''ON'' a unei operaţii de tip ''JOIN''.
**Exemplu**. Determinarea speciei cu cea mai mare vârstă medie dintre cele aflate în evidența clinicii veterinare poate fi realizată prin instrucțiunea:
SELECT b.name,
MAX(average_age)
FROM (SELECT a.breed_id,
AVG(YEAR(CURRENT_TIMESTAMP) - YEAR(a.birth_date) - (MONTH(CURRENT_TIMESTAMP) < MONTH(a.birth_date) OR (MONTH(CURRENT_TIMESTAMP) = MONTH(a.birth_date) AND DAY(CURRENT_TIMESTAMP) < DAY(a.birth_date)))) AS average_age
FROM animal a
GROUP BY a.breed_id) statistics, breed b
WHERE b.id = statistics.breed_id;
Există mai multe optimizări care se pot realiza cu ajutorul subinterogărilor. Ca o regulă generală, este util ca operaţiile de tip join să fie înlocuite prin interogări imbricate. Există şi tipuri de ''LEFT [OUTER] JOIN'' care sunt mai rapide decât subinterogările deoarece sunt optimizate intern. De asemenea, trebuie să se aibă în vedere ca rezultatele întoarse de subinterogări să fie cât mai mic cu putinţă.
== Funcții de grup ==
În MySQL există şi funcţii de grup (funcţii agregate) care pot fi folosite pentru determinarea unor statistici aferente unor seturi de date din cadrul tabelelor:
|< 100% >|
| ''AVG()'' | valoarea medie | ''STD()'' | deviaţia standard a populaţiei |
| ''BIT_AND()'' | AND pe biţi | ''STDDEV_POP()'' | deviaţia standard a populaţiei |
| ''BIT_OR()'' | OR pe biţi | ''STDDEV_SAMP()'' | deviaţia standard a eşantionului |
| ''BIT_XOR()'' | XOR pe biţi | ''STDDEV()'' | deviaţia standard a populaţiei |
| ''COUNT(DISTINCT)'' | numărul valorilor distincte | ''SUM()'' | suma |
| ''COUNT()'' | numărul de rânduri | ''VAR_POP()'' | varianţa standard a populaţiei |
| ''GROUP_CONCAT()'' | şir de caractere concatenat | ''VAR_SAMP()'' | varianţa eşantionului |
| ''MAX()'' | valoarea maximă | ''VARIANCE()'' | varianţa standard a populaţiei |
| ''MIN()'' | valoarea minimă |
**Exemplu**. Determinarea numărului de consultații de care a beneficiat fiecare animal de companie în parte în cadrul clinicii veterinare poate fi realizat prin instrucțiunea:
SELECT a.name AS name,
(SELECT COUNT(*) FROM medical_record mr WHERE mr.animal_id = a.id) AS number_of_examinations
FROM animal a;
===== Obiecte ale bazei de date =====
==== Programe stocate ====
MySQL permite definirea de **programe stocate** ce pot fi clasificate astfel:
* **rutine stocate** (proceduri sau funcţii) invocate prin instrucţiunea ''CALL'';
* **triggere**, obiecte asociate unei tabele care sunt executate atunci când se produc anumite evenimente;
* **evenimente**, sarcini pe care serverul le execută conform unei planificări.
Pentru fiecare dintre aceste tipuri de obiecte există instrucţiuni de tip ''CREATE'', ''ALTER'' şi ''DROP'' asociate care controlează modul în care acestea există şi funcţionează în contextul bazei de date.
=== Rutine stocate ===
O rutină stocată (fie funcţie, fie procedură) are trei componente:
* parametrii de intrare (argumentele);
* parametrii de ieşire (valorile returnate);
* corpul rutinei care conţine instrucţiunile ce trebuie executate, acestea fiind încadrate de cuvintele cheie ''BEGIN'' şi ''END'' şi separate prin caracterul ';'. Întrucât pentru clientul MySQL caracterul ';' reprezintă un delimitator care marchează încheierea unei instrucţiuni, acest tip de delimitator trebuie să fie redefinit astfel încât întreaga definiţie a rutinei stocate să fie transmisă serverului. Acest lucru se face prin intermediul cuvântului-cheie ''DELIMITER''. Un delimitator poate consta dintr-unul sau mai multe caractere.
Pentru a putea crea o rutină, un utilizator trebuie să aibă privilegiul ''CREATE ROUTINE'', iar pentru a o executa, privilegiul ''EXECUTE''.
Diferenţa dintre o procedură şi o funcţie constă în modul prin care rezultatul este întors. O procedură poate realiza modificări asupra tabelelor fără să producă o valoare în mod necesar. În cazul când aceasta trebuie să fie vizibilă către programul care a apelat-o, transferul său se poate realiza prin intermediul parametrilor. O funcţie întoarce rezultat în mod necesar.
Sintaxa pentru creearea acestora este:
CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
CREATE
[DEFINER = { user | CURRENT_USER }]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
func_parameter:
param_name type
type:
Any valid MySQL data type
characteristic:
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
Clauza ''DEFINER'' asociază rutinei stocate un utilizator ale cărui privilegii vor fi verificate la momentul execuţiei sale.
Dacă valoarea ''SQL SECURITY'' e ''DEFINER'' se verifică privilegiile utilizatorului care a creat procedura stocată, iar dacă este ''INVOKER'' se verifică privilegiile utilizatorului care apelează procedura stocată.
Numele rutinei stocate trebuie să fie diferit de cel al rutinelor SQL standard, în caz contrar producându-se o eroare.
Parametrii unei proceduri stocate pot avea tipurile ''IN'' (implicit), ''OUT'' respectiv ''INOUT'' după cum pot avea rolul de parametrii de intrare, ieşire, respectiv de intrare-ieşire. Aceştia trebuie plasaţi între paranteze.
Toţi parametrii unei funcţii stocate au doar rolul de a transmite date şi tipul lor nu mai trebuie specificat. Lista parametrilor unei funcţii trebuie urmată de cuvântul-cheie ''RETURNS'' care specifică tipul de date al rezultatului pe care aceasta îl întoarce. Dacă rezultatul transmis prin clauza ''RETURN'' are alt tip decât cel specificat în antet, conversia se va realiza în mod automat.
O rutină stocată care nu foloseşte nici un fel de parametri va marca acest fapt prin ''()'' după numele său.
Corpul unei rutine poate conţine orice fel de instrucţiuni DDL sau DML, inclusiv instrucţiuni de control şi bucle.
Prin cuvântul-cheie ''COMMENT'' se introduce o descriere a rutinei, iar clauza ''LANGUAGE'' conţine limbajul în care este scrisă. Această clauză este momentan ignorată, fiind permise numai instrucţiuni SQL.
Clauzele ''DETERMINISTIC'' respectiv ''NON-DETERMINISTIC'' (implicit) descriu comportamentul rutinei stocate. O rutină este deterministică dacă produce întotdeauna aceleaşi rezultate pentru aceiaşi parametri de intrare. Totodată, rutina este nedeterministică dacă la execuţii diferite va rula imprevizibil. Spre exemplu, apelul unor funcţii precum ''NOW()'' sau ''RAND()'' poate determina generarea unui comportament nedeterministic. MySQL nu verifică corectitudinea comportamentului declarat, însă declararea sa greşită poate determina modulul de optimizare să realizeze planuri de execuţie neperformante.
Pentru o rutină pot fi precizate nişte clauze care indică modul în care sunt utilizate datele în cadrul rutinei stocate, fără a limita operaţiile permise:
* ''CONTAINS SQL'' – specifică faptul că rutina stocată nu conţine instrucţiuni care citeşte sau scrie date;
* ''NO SQL'' – arată că rutina stocată nu conţine comenzi SQL;
* ''READS SQL DATA'' – indică faptul că rutina stocată conţine instrucţiuni care citesc date (cum ar fi ''SELECT''), dar nu şi instrucţiuni care le scriu;
* ''MODIFIES SQL DATA'' – precizează faptul că rutina stocată poate conţine instrucţiuni care scriu date (precum ''INSERT'', ''UPDATE'' sau ''DELETE'').
Rutinele stocate trebuie să întoarcă un scalar. De asemenea, în timp ce procedurile pot apela instrucţiuni ce întorc mai multe valori, comportamentul nu este permis în cadrul funcţiilor.
**Exemple**. Determinarea numărului de boli de care a suferit, respectiv a numărului de consultații de care a beneficiat fiecare animal de companie în parte în cadrul clinicii veterinare poate fi realizat prin intermediul unei proceduri, respectiv a unei funcții stocate:
DELIMITER //
CREATE PROCEDURE get_number_of_diseases(
IN animal_id INT,
OUT number_of_diseases INT(2)
)
BEGIN
SELECT COUNT(DISTINCT mr.diagnosis_id) INTO number_of_diseases
FROM medical_record mr INNER JOIN animal a ON mr.animal_id = a.id
WHERE a.id = animal_id;
END; //
CALL get_number_of_diseases(2, @result);
SELECT @result;
DELIMITER //
CREATE FUNCTION get_number_of_examinations(animal_id INT)
RETURNS INT(2)
BEGIN
DECLARE number_of_examinations INT(2);
SELECT COUNT(mr.id) INTO number_of_examinations
FROM medical_record mr INNER JOIN animal a ON mr.animal_id = a.id
WHERE a.id = animal_id;
RETURN number_of_examinations;
END; //
SELECT get_number_of_examinations(2);
=== Triggere ===
Un trigger este definit să se activeze atunci când se produce o operaţie de introducere, modificare sau ştergere a datelor pentru a verifica respectarea unor constrângeri sau pentru a realiza anumite calcule asupra unor valori.
Sintaxa pentru creearea unui astfel de obiect este:
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON table_name FOR EACH ROW
trigger_body
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
Pentru fiecare trigger trebuie să se definească tabelul la care se referă precum şi operaţia care va determina lansarea lui în execuţie şi momentul la care se întâmplă aceasta. În MySQL nu pot exista mai multe triggere pentru un tabel având acelaşi tip de eveniment şi acelaşi moment de declanşare.
În corpul unui trigger pot fi folosite alias-urile ''OLD'' pentru a defini valorile de dinaintea unei operaţii de modificare sau ştergere, respectiv ''NEW'', cu referire la valorile de după o operaţie de adăugare sau modificare.
În cazul în care un trigger eşuează, operaţia care îi este asociată nu va fi executată, indiferent de momentul la care acesta este invocat.
Un trigger nu poate folosi apeluri de rutine stocate care întorc valori utilizatorului (pot fi folosite doar proceduri ale căror rezultate pot fi transmise prin parametri de tip ''OUT'' sau ''INOUT'') sau care folosesc SQL dinamic. Totodată nu pot fi folosite operaţii care încep sau termină o tranzacţie (''COMMIT'', ''ROLLBACK'').
**Exemple**. Prin intermediul unor triggere, se poate impune respectarea unor constrângeri de integritate sau se pot corecta anumite anomalii la actualizare:
DELIMITER //
CREATE TRIGGER owner_insert_check
BEFORE INSERT ON owner
FOR EACH ROW
BEGIN
DECLARE message VARCHAR(255);
IF NEW.email IS NOT NULL AND NEW.email NOT LIKE '%@%.%' THEN
SET message = concat('The format of an email should be username@domain.country ', cast(NEW.email AS CHAR));
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = message;
END IF;
END; //
DELIMITER //
CREATE TRIGGER animal_update_check
AFTER UPDATE ON animal
FOR EACH ROW
BEGIN
IF NEW.birth_date > CURRENT_DATE THEN
SET NEW.birth_date = CURRENT_DATE;
ELSEIF YEAR(NEW.birth_date) - YEAR(CURRENT_DATE) > 20 THEN
SET NEW.birth_date = OLD.birth_date;
END IF;
END; //
=== Evenimente ===
Evenimentele sunt sarcini planificate ce sunt executate la un moment dat sau periodic în cazul în care planificatorul de execuţii este pornit. Planificatorul execuţiilor poate avea starea: pornit, oprit sau dezactivat.
De regulă, acestea sunt folosite pentru realizarea unor operaţii de întreţinere fie asupra anumitor tabele din baza de date, fie asupra sistemului de baze de date însuşi.
==== Vizualizări ====
Vizualizările (//eng.// views) sunt interogări stocate care produc rezultate atunci când sunt invocate. De multe ori, ele sunt referite ca tabele virtuale.
Sintaxa pentru creearea unei vizualizări este:
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]
Operaţia ''SELECT'' din cadrul vizualizării poate include tabele de bază sau alte vizualizări. Orice modificări ar fi realizate asupra tabelelor implicate după momentul definirii vizualizării nu sunt luate în considerare.
Clauzele ''DEFINER'' şi ''SQL SECURITY'' specifică contextul de securitate care va fi utilizat când se verifică privilegiile de acces ale vizualizării atunci când aceasta va fi invocată.
Întrucât tabelele de bază şi vizualizările partajează acelaşi spaţiu de nume denumirile acestora trebuie să fie diferite.
O vizualizare trebuie să definească nume unice pentru coloanele sale, putând fi specificate şi alias-uri.
În definirea unei vizualizări trebuie să se ţină seama de unele constrângeri:
* interogările de tip ''SELECT'' nu pot conţine interogări imbricate în cadrul clauzei ''FROM'';
* interogările de tip ''SELECT'' nu pot conţine variabile de sistem sau definite de utilizator;
* în cadrul unui program stocat definiţia nu poate referi parametrii săi sau variabilele locale;
* orice tabelă de bază sau vizualizare referită trebuie să existe; în situaţia când acestea sunt şterse, utilizarea vizualizării care le foloseşte generează o excepţie;
* nu pot fi referite tabele cu atributul ''TEMPORARY'' şi nici nu pot fi create vizualizări temporare;
* unei vizualizări nu i se poate asocia un trigger;
* alias-urile pentru numele coloanelor din instrucţiunile ''SELECT'' nu trebuie să depăşească 64 de caractere;
* clauza ''ORDER BY'' este permisă în definirea unei vizualizări însă în cazul când aceasta există într-o vizualizare referită, aceasta va avea prioritate.
Atributul ''ALGORITHM'' indică modul în care vizualizarea este procesată. Când este precizată valoarea ''MERGE'', porţiuni din definiţia vizualizării sunt copiate în instrucţiunile care o apelează. Pentru ''TEMPTABLE'', se creează o tabelă temporară în care este transferat conţinutul vizualizării, aceasta fiind folosită atunci când este vizualizarea este invocată în diferite instrucţiuni. În cazul când are valoarea ''UNDEFINED'', sistemul de gestiune pentru baze de date decide ce algoritm va fi utilizat. De regulă, se preferă algoritmul ''MERGE'' în detrimentul algoritmului ''TEMPTABLE'' întrucât este mai eficient şi pentru că în cazul folosirii tabelelor temporare vizualizarea nu poate fi actulizată. Utilizarea algoritmului ''TEMPTABLE'' este justificată doar în situaţia în care se doreşte eliberarea resurselor asociate tabelelor referite în cazul vizualizării.
Unele vizualizări pot fi actualizate în sensul că pot fi folosite în operaţii de tip ''INSERT'', ''UPDATE'', ''DELETE'' pentru a opera modificări în tabelele referite. Pentru ca o vizualizare să poată fi actualizată trebuie să existe o relaţie 1:1 între înregistrările sale şi înregistrările tabelei referite. Există numeroase impedimente care determină ca o vizualizare să nu poată fi actualizată: folosirea funcţiilor agregate (''SUM'', ''MIN'', ''MAX'', ''COUNT'', ''AVG''), utilizarea clauzelor ''DISTINCT'', ''GROUP BY'', ''HAVING'', ''UNION'' / ''UNION ALL'', folosirea interogărilor imbricate în ''SELECT'', anumite operatii de tip jonctiune, invocarea unor vizualizări ce nu pot fi actualizate în clauza ''FROM'', folosirea de interogări imbricate în clauza ''WHERE'' care referă aceeaşi tabelă din clauza ''FROM'', referinţe exclusiv la constante, utilizarea algoritmului ''TEMPTABLE'', referinţe multiple la aceeaşi coloană dintr-o tabelă de bază.
Clauza ''WITH CHECK OPTION'' face ca pentru vizualizările ce pot fi actualizate, operaţiile ''INSERT'' sau ''UPDATE'' să nu poată fi realizate decât dacă este îndeplinită condiţia specificată prin clauza ''WHERE''.
Când vizualizarea referă alte vizualizări, cuvintele-cheie ''LOCAL'' şi ''CASCADED'' (implicit) indică nivelul până la care sunt realizate verificările: doar la nivelul vizualizării definite în mod curent sau şi pentru vizualizările imbricate.
CREATE VIEW pets_per_owner AS
SELECT CONCAT(o.first_name,' ', o.last_name) AS name,
(SELECT GROUP_CONCAT(a.name) FROM animal a WHERE a.owner_id=o.id) AS pets
FROM owner o;
SELECT * FROM pets_per_owner;
===== Instrucțiuni pentru controlul fluxului =====
==== Variabile ====
Pot fi utilizate variabile definite de utilizator, a căror durată de viaţă nu depăşeşte însă sesiunea curentă:
SET @variable_name = expression [, @variable_name = expression] ...
Numele de variabile folosesc caractere alfa-numerice precum şi caracterele ''.'', ''_'' şi ''$'', acesta fiind case in-sensitive.
Atribuirea se poate face fie folosind ''='', fie folosind '':=''.
Variabilele pot lua doar valori de tip numeric sau şir de caractere (binar sau non-binar), însă în cazul numerelor reale poate avea loc o pierdere a preciziei. Atribuirile care folosesc tipuri nepermise vor fi convertite în mod automat către un tip permis. De asemenea, o variabilă poate avea valoarea ''NULL''.
O variabilă ce nu a fost iniţializată are valoarea ''NULL'' iar tipul de date asociat este şir de caractere.
==== IF ====
Sintaxa instrucţiunii ''IF'' este:
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF
Dacă condiţia este verificată se execută instrucţiunea de pe ramura ''THEN'', altfel se trece la una din ramurile ''ELSEIF'' sau ''ELSE''.
Are şi variantele ''IFNULL'' şi ''NULLIF'' care primesc ca parametri două expresii:
* ''IFNULL'' întoarce valoarea primei expresii dacă aceasta nu este ''NULL'', altfel returnează valoarea celei de-a doua expresii;
* ''NULLIF'' este evaluată ca ''NULL'' dacă expresiile sunt egale, altfel întoarce valoarea primei expresii.
==== CASE ====
Instrucţiunea ''CASE'' are două variante:
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE
în care o valoare este comparată cu mai multe variante, respectiv sunt evaluate mai multe condiţii, urmând ca una dintre aceste ramificații să fie executate.
Dacă nici una dintre ramuri nu este îndeplinită, se rulează comanda asociată ramurii ''ELSE''.
==== LOOP ====
O structură iterativă clasică este ''LOOP'', care are următoarea sintaxă:
[begin_label:] LOOP
statement_list
END LOOP [end_label]
Trecerea la un nou pas al iteraţiei se face prin instrucţiunea ''ITERATE'' care are asociată eticheta ce identifică acest ciclu, ieşirea din iteraţie (dacă este îndeplinită o condiţie) făcându-se prin comanda ''LEAVE'' sau, în cazul în care ciclul este definit în cadrul unei rutine stocate care întoarce valori prin instrucţiunea ''RETURN''.
==== REPEAT ====
Instrucţiunea ''REPEAT'' este tipul de iteraţie care se execută cel puţin o dată, până la îndeplinirea unei condiţii:
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]
Întrucât există o condiţie naturală pentru părăsirea ciclului etichetarea acestui tip de instrucţiune nu este absolut necesar.
==== WHILE ====
Comanda ''WHILE'' implementează ciclul cu verficarea iniţială a condiţiei, astfel încât există posibilitatea de a nu se executa niciodată.
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
==== Cursoare ====
Un alt mod de a itera asupra unui set de date obţinut printr-o operaţie de tip ''SELECT'' în cadrul unor rutine stocate sunt **cursoarele**.
Operaţiile curente asociate cursoarelor sunt (în ordinea în care acestea trebuie apelate) sunt:
* declararea;
* deschiderea;
* parcurgerea;
* închiderea.
Caracteristicile unui cursor sunt:
- asenzitivitatea: serverul poate realiza sau nu copii ale tabelului care conţine rezultatele care sunt parcurse;
- proprietatea de a nu putea fi suprascrise;
- proprietatea de a putea fi parcurse într-o singură direcţie şi în ordine.
**1.** **Declararea unui cursor** se realizează după cea a variabilelor şi condiţiilor însă înainte de cea a handle-urilor şi are următoarea sintaxă:
DECLARE cursor_name CURSOR FOR select_statement
Operaţia de tip ''SELECT'' care este asociată cursorului nu poate avea clauza ''INTO''. O rutină stocată poate defini mai multe cursoare însă fiecare trebuie identificat printr-o denumire unică.
**2.** **Deschiderea cursorului** (necesară pentru ca acesta să fie folosit) se face folosind comanda ''OPEN'':
OPEN cursor_name
**3.** **Obţinerea valorilor** reţinute în cadrul unui cursor se face prin instrucţiunea ''FETCH'', având sintaxa:
FETCH [[NEXT] FROM] cursor_name INTO variable_name [, variable_name] ...
În momentul în care este apelată, se trece la următoarea înregistrare (dacă aceasta există), iar valorile sunt transferate în cele ale variabilelor definite, numărul lor trebuind să corespundă cu al celor din instrucţiunea ''SELECT'' asociată declarării cursorului.
**4**. **Închiderea cursorului** se face prin instrucţiunea ''CLOSE'':
CLOSE cursor_name
În situaţia în care cursorul nu este dechis, operația va genera o eroare. Dacă nu este închis explicit, acest lucru se va realiza automat la sfârşitul blocului ''BEGIN ... END'' în care a fost definit.
**Exemplu**. Un cursor poate fi folosit pentru a calcula vârsta medie a animalelor de companie diferențiată pe sexe, aplicând corecții asupra datei de naștere acolo unde se detectează valori incorecte:
DELIMITER //
CREATE PROCEDURE average_age_per_gender()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE _gender CHAR(1);
DECLARE _birth_date DATETIME;
DECLARE current_age, male_total_age, male_number, female_total_age, female_number INT;
DECLARE animal_cursor CURSOR FOR SELECT gender, birth_date FROM animal;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
SET male_total_age := 0;
SET male_number := 0;
SET female_total_age := 0;
SET female_number := 0;
OPEN animal_cursor;
this_loop: LOOP
FETCH animal_cursor INTO _gender, _birth_date;
IF done THEN
LEAVE this_loop;
END IF;
SET current_age := YEAR(CURRENT_TIMESTAMP) - YEAR(_birth_date) - (MONTH(CURRENT_TIMESTAMP) < MONTH(_birth_date) OR (MONTH(CURRENT_TIMESTAMP) = MONTH(_birth_date) AND DAY(CURRENT_TIMESTAMP) < DAY(_birth_date)));
IF current_age < 0 THEN
SET current_age := 0;
END IF;
CASE _gender
WHEN 'M' THEN
SET male_total_age := male_total_age + current_age;
SET male_number := male_number + 1;
WHEN 'F' THEN
SET female_total_age := female_total_age + current_age;
SET female_number := female_number + 1;
END CASE;
END LOOP;
SELECT CONCAT('Varsta medie a animalelor de sex masculin este ', male_total_age / male_number,'. Varsta medie a animalelor de sex feminin este ', female_total_age / female_number,'.');
CLOSE animal_cursor;
END; //
CALL average_age_per_gender;
===== Gestiunea tranzacțiilor =====
==== Proprietățile ACID ====
MySQL este un sistem de gestiune pentru baze de date tranzacțional, în sensul că respectă proprietățile ACID:
* atomicitate - o tranzacție trebuie executată complet; cu alte cuvinte, dacă una dintre instrucțiunile care o compun eșuează, sistemul de gestiune pentru baze de date trebuie să fie capabil să restaureze starea anterioară încercării de rulare a tranzacției respective; acestă proprietate este asigurată prin folosirea unor zone de memorie tampon al căror conținut este marcat pe discul local în momentul în care operațiile din cadrul tranzacțiilor au fost realizate;
* coerență - se referă la faptul că baza de date nu trebuie să se găsească în nici un moment într-o stare corespunzătoare unei execuții parțiale a unei tranzacții; un astfel de comportament este realizat prin facilitatea de blocare a obiectelor bazei de date realizate până ce s-au realizat toate modificările;
* izolare - specifică faptul că fiecare tranzacție trebuie să beneficieze de propriul spațiu de lucru, autonom și independent de toate celelalte tranzacții, astfel încât rezultatul execuției sale să devină vizibil numai în momentul în care aceasta a fost executată rulată în totalitate; o astfel de funcționalitate nu se obține însă decât prin mecanisme de tipul blocării la nivel de înregistrare, cu anumite costuri asupra performanței;
* durabilitate - indică faptul că rezultatele unei tranzacții trebuie să fie persistente inclusiv în cazul producerii unei erori, recuperarea dintr-o astfel de situație realizându-se prin intermediul jurnalelor în care sunt stocate operațiile realizate.
==== Operații de lucru cu tranzacții ====
În situația în care mai multe instrucțiuni trebuie realizate împreună (fie toate, fie nici una), acestea vor fi grupate în cadrul unei tranzacții.
START TRANSACTION
[transaction_characteristic [, transaction_characteristic] ...]
transaction_characteristic:
WITH CONSISTENT SNAPSHOT
| READ WRITE
| READ ONLY
BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET autocommit = {0 | 1}
O tranzacție este marcată prin intermediul instrucțiunii ''START TRANSACTION'' sau ''BEGIN [WORK]''. Instrucțiunile din cadrul acesteia vor fi executate împreună în momentul în care se apelează instrucțiunea ''COMMIT'' sau se va reveni la starea anterioară acesteia dacă se apelează ''ROLLBACK''.
{{ :laboratoare:laborator01_tranzactii.jpg?nolink&300 }}
În cadrul unei tranzacții modul autocommit (marcarea modificărilor pe discul local imediat ce este emisă o anumită instrucțiune) - activat în mod implicit - este dezactivat pe toată perioada acesteia. Modificarea acestui comportament se poate controla prin intermediul variabilei de sesiune ''autocommit''.
Nu se pot realiza tranzacții imbricate. O astfel de tentativă va rezulta în terminarea tranzacției vechi și începerea unei noi tranzacții.
Clauza ''AND [NO] CHAIN'' specifică faptului că se va realiza sau nu încă o tranzacție cu același nivel de izolare odată ce sunt executate instrucțiunile ''COMMIT'' sau ''ROLLBACK''.
Clauza ''[NO] RELEASE'' indică faptul că se dorește sau nu deconectarea sesiunii curente odată ce sunt executate instrucțiunile ''COMMIT'' sau ''ROLLBACK''.
De asemenea, se pot specifica proprietăți ce controlează caracteristicile tranzacției:
* ''WITH CONSISTENT SNAPSHOT'' - realizează o citire consistentă dacă nivelul de izolare al tranzacției permite acest lucru;
* ''READ WRITE'' (implicit) - permite tranzacției să modifice sau să obțină drepturi de blocare (pentru operații de citire sau scriere) asupra tabelelor bazei de date;
* ''READ ONLY'' - restricționează tranzacția de la a modifica sau de a obține drepturi de blocare asupra tabelelor tranzacționale sau non-tranzacționale care sunt vizibile la nivelul altor tranzacții.
O tranzacție poate fi împărțită în mai multe zone, delimitată de așa-numitele puncte de salvare (//eng.// save-points), prin intermediul comenzii ''SAVEPOINT identifier''. Astfel, se reține starea de la un anumit moment de timp la care se poate reveni prin instrucțiunea ''ROLLBACK [WORK] TO [SAVEPOINT] identifier''. Realizarea acestei operații nu este însă echivalentă cu realizarea tranzacției, ci doar revenirea la o anumită situație, cu pierderea punctelor intermediare ulterioare. Eliminarea unui astfel de marcaj se realizează prin comanda ''RELEASE SAVEPOINT identifier''.
==== Controlul comportamentului tranzacțional ====
Controlul comportamentului tranzacțional se realizeză prin intermediul a două variabile de sesiune:
- ''AUTOCOMMIT'' ce gestioneză momentul la care sunt executate instrucțiunile
* ''1'' (implicit): modificările sunt vizibile imediat;
* ''0'': modificările sunt stocate pe discul local în momentul în care s-a apelat una din instrucțiunile ''COMMIT'' sau ''ROLLBACK'';
- ''TRANSACTION ISOLATION LEVEL'' care specifică nivelul de izolare pentru tranzacții (gradul de interacțiune cu alte tranzacții)
* ''READ UNCOMMITTED'' - sunt vizibile modificări nesalvate de către alte tranzacții, ceea ce reprezintă o vulnerabilitate față de datele fantomă și citiri irepetabile
* ''READ COMMITTED'' - se operează cu valorile stocate pe discul local de către alte tranzacții ceea ce ridică probleme legate de citirile irepetabile
* ''REPEATABLE READ'' (implicit) - informațiile actualizate de alte tranzacții sunt disponibile numai în momentul în care s-a realizat și tranzacția din care se solicită datele respective
* ''SERIALIZABLE'' - tranzacțiile concurente sunt implementate ca și când acestea s-ar desfășura secvențial, asigurându-se astfel faptul că datele obținute sunt cele stocate în mod real pe discul local
==== Simularea tranzacțiilor prin operații de blocare ====
Este posibilă și realizarea de pseudo-tranzacții, prin blocarea / deblocarea anumitor privilegii (scriere, citire) pentru alte tranzacții la nivel de:
* tabelă;
* pagină = un anumit număr de înregistrări;
* înregistrare
Instucțiunile corespunzătoare unor astfel de operații sunt ''LOCK TABLES'', respectiv ''UNLOCK TABLES'':
LOCK TABLES
table_name [[AS] alias] lock_type
[, table_name [[AS] alias] lock_type] ...
lock_type:
READ [LOCAL]
| [LOW_PRIORITY] WRITE
UNLOCK TABLES
Se observă că este posibilă blocarea concomitentă a mai multor tabele, fiecare pentru operații diferite:
* blocarea în modul citire: atât tranzacția care a realizat blocarea cât și alte tranzacții au doar drepturi de citire asupra tabelei respective;
* blocarea în modul scriere: tranzacția care a realizat blocarea are atât drepturi de citire cât și drepturi de scriere asupra tabelei respective, în timp ce alte tranzacții nu au nici un fel de drepturi asupra acesteia.
===== Alte elemente ale limbajului MySQL =====
==== Comentarii ====
Comentariile se pot întinde fie pe o linie (fiind precedate de ''#'' sau ''-- '' până la sfârşitul liniei în cauză), fie pe mai multe linii (fiind încadrate între ''/*'' şi ''*/''), această variantă putând fi folosită şi pentru comentariile de un singur rând care nu se întind însă până la sfârşitul acesteia.
==== Script-uri specifice ====
Câteodată este util, în special în caz de testare, ca toate comenzile să fie grupate în cadrul unui fişier şi executate secvenţial.
În acest sens, se pot folosi:
* operatorul de indirectare:
mysql -h host -u user -p < filename
* comenzile source sau \.:
mysql> source filename;
mysql> \. filename
===== Activitate de Laborator =====
Se doreşte proiectarea unei baze de date ce va fi folosită în cadrul unui sistem ERP pentru o librărie care comercializează doar cărţi.
O carte este identificată prin numele autorului sau a autorilor săi, titlul volumului, editura unde a fost publicată, anul apariţiei, ediţia, colecţia din care face parte, domeniul în care poate fi încadrată. Totodată, pentru fiecare carte se reţine o descriere şi informaţii cu privire la stocul existent şi la preţ. Este obligatoriu ca informaţiile ediţie, stoc existent şi preţ să fie numere pozitive.
Pentru fiecare autor se va reţine şi o scurtă biografie.
Librăria emite comenzi de aprovizionare către edituri şi facturi la clienţi. Atât comanda de aprovizionare cât şi factura vor fi identificate printr-un cod (unic) – spre exemplu serie şi număr –, data la care a fost emisă, starea precum şi entitatea căreia i se adresează (editură / client). Ele pot conţine mai multe produse, pentru fiecare reţinându-se cantitatea.
O editură este caracterizată prin denumire, cod unic de identificare, descriere, localitatea, regiunea şi ţara în care operează.
Un client este identificat prin CNP, nume, prenume, adresa, telefon, email. Presupunând că librăria dispune şi de o platformă on-line pentru achiziţii, pentru fiecare client se vor reţine tipul (administrator / client), precum şi rolul (super-administrator, administrator simplu / client inregistrat, client verificat). Cu excepţia numărului de telefon, nici un câmp nu poate fi vid. Pentru email trebuie să se respecte formatul ''username@domain.country''.
- [**25 puncte**] Să se proiecteze schema conceptuală a bazei de date.\\ Studenţii care nu participă la ora de laborator şi trimit rezolvările prin email, vor ataşa schema conceptuală a bazei de date ca fişier .pdf.
- [**25 puncte**] Să se implementeze baza de date având schema conceptuală definită folosind sistemul de gestiune pentru baze de date MySQL.\\ Să se populeze corespunzător baza de date.\\ Se vor specifica, pentru fiecare atribut în parte, şi constrângerile indicate folosind clauza ''CHECK'', chiar dacă MySQL nu le implementează încă.
- [**10 puncte**] Să se afişeze conţinutul tabelei care conține cărțile comercializate în librărie. Pentru fiecare carte se va afişa identificatorul, titlul şi autorii.\\ Întrucât o carte poate avea mai mulţi autori, se va folosi funcţia de grup ''GROUP_CONCAT'', a cărei sintaxă este: GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])
- [**10 puncte**] Să se majoreze cu 20% preţul cărţilor apărute înainte de anul 2010. Ulterior se vor lista toate volumele existente în librărie grupate pe ani de apariţie (în ordine cronologică), pentru fiecare an în parte ordonate descrescător în funcţie de preţ.
- [**10 puncte**] Să se afişeze toate cărţile unui autor, fie că le-a redactat singur, fie că acestea au mai mulţi autori.\\ Să se identifice numărul de volume elaborate, precizându-se numărul celor la care e unic autor şi al celor pe care le-a realizat colaborând cu alţi autori.
- [**10 puncte**] Să se creeze două rutine stocate pe server (funcții) care determină valoarea unei comenzi de aprovizionare, respectiv a unei facturi.\\ Folosind aceste rutine stocate, să se identifice primele trei edituri, respectiv primii trei clienţi în funcţie de volumul tranzacţiilor din librărie.
- [**10 puncte**] Să se creeze o procedură stocată care calculează suma tuturor facturilor pentru un client, afişând lista facturilor şi valoarea lor, ordonată cronologic.
- [**10 puncte**] Să se identifice lista celor mai profitabile cărţi, ordonate în funcţie de suma obţinută din vânzări.
- [**5 puncte**] Să se implementeze constrângerile cu privire la atributele tabelelor folosind triggere.
- [**5 puncte**] Să se parcurgă lista de utilizatori folosind un cursor, stabilindu-se vârsta medie a bărbaţilor şi vârsta medie a femeilor.
===== Resurse ======
Gheorghe SABĂU, Vasile AVRAM, Ramona BOLOGA, Mihaela MUNTEAN, Marian DÂRDALĂ, Răzvan BOLOGA – //Baze de Date//, Editura Matrix Rom, Bucureşti, 2008\\
Dorin CÂRSTOIU – //Baze de Date//, Editura Matrix Rom, Bucureşti, 2009\\
Manole VELICANU, Ion LUNGU, Iuliana BOTHA, Adela BÂRA, Anda VELICANU, Emanuil REDNIC – //Sisteme de Baze de Date Evoluate//, Editura ASE, Bucureşti, 2009\\
Vikram VASWANI – //MySQL. Utilizarea şi administrarea bazelor de date MySQL//, traducere de Cristian Alexe Dumitrescu, Editura Rosetti Educational, Bucureşti, 2010\\
Carlos CORONEL, Steven MORRIS, Peter ROB – //Database Systems. Design, Implementation and Management//, 9th Edition, Course Technology, Cengage Learning, Boston, 2011\\
Ramez ELMASRI, Shamkant NAVATHE – //Fundamentals of Database Systems//, 6th Edition, Addison-Wesley, 2011\\
[[http://dev.mysql.com/doc/refman/5.6/en/index.html|MySQL 5.6 Reference Manual]] - în special capitolele 2, 3, 11, 12, 13
===== Soluții =====
[[https://github.com/aipi2014/Laborator01/tree/master/rezolvari|Soluții]]