• 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

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

aipi2015-lab00b-slides.pdf

Un SGBD = sistem de gestiune pentru baze de date (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.

Î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 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:

1. fiecare tabelă corespunde unei singure entităţi, conţinând exclusiv atributele specifice acesteia;
2. 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;
3. atributele non-prime sunt dependente doar de cheia primară astfel că aceasta le identifică în mod unic;
4. 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.

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 }

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ă 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ă:

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

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

Î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 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 }

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}

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.

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:

1. nodurile interne au structura <P1, 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.
   
2. nodurile frunză au structura < <K1, Pr1>, <K2, Pr2>, …, <Kq-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.
   

Dacă identificarea unei înregistrări se face prin mai multe atribute, poate fi utilă definirea unei chei artificiale (autoincrementale).

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).

Î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.

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 Linux cât și pe Windows.

În MySQL pot fi folosite trei tipuri de date:

• numerice;
• şiruri de caractere;
• dată calendaristică.

Sunt suportate şi unele extensii pentru date spaţiale.

Tipurile de date numerice sunt redate în tabelul de mai jos:

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:

Tipurile de date şir de caractere sunt redate în tabelul de mai jos:

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:

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:

Atributele ce reţin date calendaristice pot avea următoarele tipuri:

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:

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.

În MySQL, informațiile sunt grupate sub formă de baze de date (= scheme), fiecare dintre acestea putând conține mai multe tabele.

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).

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.

Ș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ă.

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).

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).

În MySQL există o bază de date implicită, la care se vor referi toate operațiile care îi urmează:

USE database_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.

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.

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.

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.

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.

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.

Ş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.

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).

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).

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ă:

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:

aipi2015-lab00b-DDL.sql

CREATE DATABASE IF NOT EXISTS veterinary_clinic;
 
USE veterinary_clinic;
 
CREATE DATABASE IF NOT EXISTS veterinary_clinic;
 
USE veterinary_clinic;
 
CREATE TABLE IF NOT EXISTS breed (
    id              INT(10) UNSIGNED AUTO_INCREMENT NOT NULL,
    name            VARCHAR(50) NOT NULL,
    description     VARCHAR(1000),
    KEY (id)
);
ALTER TABLE breed ADD CONSTRAINT pk_breed_id PRIMARY KEY (id);
 
CREATE TABLE IF NOT EXISTS owner (
    id              INT(10) UNSIGNED AUTO_INCREMENT 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,
    KEY (id)
);
ALTER TABLE owner ADD CONSTRAINT pk_owner_id PRIMARY KEY (id);
ALTER TABLE owner ADD CONSTRAINT owner_email_format CHECK (email LIKE '%@%.%');
 
CREATE TABLE IF NOT EXISTS animal (
    id              INT(10) UNSIGNED AUTO_INCREMENT NOT NULL,
    name            VARCHAR(50) NOT NULL,
    breed_id        INT(10) UNSIGNED,
    gender          CHAR(1) DEFAULT 'M' NOT NULL,
    birth_date      DATETIME NOT NULL,
    death_date      DATETIME,
    owner_id        INT(10) UNSIGNED,
    father_id       INT(10) UNSIGNED,
    mother_id       INT(10) UNSIGNED,
    KEY (id)
);
ALTER TABLE animal ADD CONSTRAINT pk_animal_id PRIMARY KEY (id);
ALTER TABLE animal ADD CONSTRAINT fk_animal_breed_id FOREIGN KEY(breed_id) REFERENCES breed(id) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE animal ADD CONSTRAINT fk_animal_ownder_id FOREIGN KEY(owner_id) REFERENCES owner(id) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE animal ADD CONSTRAINT fk_animal_father_id FOREIGN KEY(father_id) REFERENCES animal(id) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE animal ADD CONSTRAINT fk_animal_mother_id FOREIGN KEY(mother_id) REFERENCES animal(id) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE animal ADD CONSTRAINT chk_animal_sex_possible_values CHECK (gender in ('M', 'F'));
 
CREATE TABLE IF NOT EXISTS doctor (
    id              INT(10) UNSIGNED AUTO_INCREMENT 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,
    KEY (id)
);
ALTER TABLE doctor ADD CONSTRAINT pk_doctor_id PRIMARY KEY (id);
ALTER TABLE doctor ADD CONSTRAINT chk_doctor_email_format CHECK (email LIKE '%@%.%');
 
CREATE TABLE IF NOT EXISTS diagnosis (
    id              INT(10) UNSIGNED AUTO_INCREMENT NOT NULL,
    name            VARCHAR(50) NOT NULL,
    description     VARCHAR(1000) NOT NULL,
    severity        VARCHAR(20) NOT NULL,
    cure            VARCHAR(1000) NOT NULL,
    KEY (id)
);
ALTER TABLE diagnosis ADD CONSTRAINT pk_diagnosis_id PRIMARY KEY (id);
 
CREATE TABLE IF NOT EXISTS medical_record (
    id              INT(10) UNSIGNED AUTO_INCREMENT NOT NULL,
    animal_id       INT(10) UNSIGNED,
    doctor_id       INT(10) UNSIGNED,
    diagnosis_id    INT(10) UNSIGNED,
    date            DATETIME NOT NULL,
    treatment       VARCHAR(1000),
    KEY (id)
);
ALTER TABLE medical_record ADD CONSTRAINT pk_medical_record_id PRIMARY KEY (id);
ALTER TABLE medical_record ADD CONSTRAINT fk_medical_record_animal_id FOREIGN KEY(animal_id) REFERENCES animal(id) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE medical_record ADD CONSTRAINT fk_medical_record_doctor_id FOREIGN KEY(doctor_id) REFERENCES doctor(id) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE medical_record ADD CONSTRAINT fk_medicak_record_diagnosis_id FOREIGN KEY(diagnosis_id) REFERENCES diagnosis(id) ON UPDATE CASCADE ON DELETE SET NULL; 

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.

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');

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='-';

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.

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;

Ş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.

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;

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 *.

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)

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ă.

• 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)

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ţă.

Î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:

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;

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.

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);

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; //

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ă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;

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.

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.

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.

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.

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.

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]

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:

1. asenzitivitatea: serverul poate realiza sau nu copii ale tabelului care conţine rezultatele care sunt parcurse;
2. proprietatea de a nu putea fi suprascrise;
3. 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;

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.

Î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.

Î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 se realizeză prin intermediul a două variabile de sesiune:

1. 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;
2. 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

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.

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.

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

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 poate avea unul, nici unul sau mai mulți autori, un titlu, un subtitlu și o descriere, fiind caracterizată printr-un număr de ediție, anul în care a fost tipărită și colecția din care face parte. Cartea poate fi disponibilă în mai multe formate (tipărit, electronic). Pentru fiecare format se reține un isbn, limba în care este scrisă cartea, stocul existent precum și prețul. De asemenea, unei cărți i se pot specifica una, nici una sau mai multe categorii. O categorie este definită printr-un nume și o descriere.

Este obligatoriu ca informațiile număr de ediție, stoc existent și preț să fie numere pozitive.

De asemenea, pentru fiecare autor se va reţine şi o scurtă biografie.

O colecție aparține unei edituri, pentru aceasta reținându-se un nume și o descriere. Pentru fiecare editură sunt stocate denumirea, codul unic de identificare, descrierea, adresa poștală, codul zip, țara și pagina Internet de unde pot fi obținute informații suplimentare.

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.

Un client este identificat prin cod numeric personal, 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) și credențialele sub forma unui nume de utilizator și a unei parole. 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.

O schemă conceptuală a bazei de date descrisă mai sus din punct de vedere funcțional poate fi următoarea:

1. Să se cloneze în directorul de pe discul local conținutul depozitului la distanță de la https://www.github.com/aipi2015/Laborator00. În urma acestei operații, directorul Laborator00 va trebui să conțină subdirectoarele samples și labtasks, fișierele README.md și LICENSE.

student@aipi2015:~$ git clone https://www.github.com/aipi2015/Laborator00

2. Să se ruleze, în ordine, script-urile:

• aipi2015-lab00b-DDL.sql care conține instrucțiunile necesare pentru definirea bazei de date bookstore;
• aipi2015-lab00b-DML.sql care conține instrucțiunile necesare pentru popularea tabelelor din baza de date bookstore.

Se poate folosi utilitarul MySQL Workbench sau fișierele se pot încărca cu ajutorul unui client MySQL, fie prin redirectarea intrării, fie prin folosirea comenzilor source sau \..

3. Să se afişeze conţinutul tabelei care reține cărțile comercializate în librărie. Pentru fiecare carte se va afişa identificatorul, titlul şi autorii, denumirea colecției, denumirea editurii, numărul de ediție și anul tipăririi precum și denumirea țării. Î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])

4. Să se majoreze cu 15% preţul cărţilor care sunt disponibile în mai mult de trei formate diferite. Să se afișeze cărțile pentru care s-au majorat prețurile, grupate pe formate. Informațiile care vor fi vizualizate sunt identificatorul cărții, titlul și subtitlul, denumirea formatului, prețul actualizat.

5. 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 afișeze, în cadrul aceleiași interogări, 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.
Vor fi incluși doar autorii care au elaborat mai mult de 4 titluri din care mai mult de 2 sunt individuale.

6. Să se șteargă din baza de date acei scriitori pentru care nu se găsește nici o carte în baza de date.

7. Să se creeze o rutină stocată pe server (funcție) care determină valoarea unei facturi.
Folosind această rutină stocată, să se identifice primii trei clienţi în funcţie de volumul tranzacţiilor din librărie.

8. Să se creeze o rutină stocată pe server (procedură) care calculează suma tuturor facturilor pentru un client, aceasta fiind întoarsă sub forma unui parametru.

9. Folosind doar rutina stocată și având acces doar la tabela de utilizatori, să se determine utilizatorul pentru care suma facturilor este cea mai mare.

<spoiler|Indicații de Rezolvare> Este necesar să se folosească un cursor. </spoiler>

10. Să se implementeze un trigger la actualizarea tabelei ce conține diferitele formate în care sunt dispuse cărțile, astfel încât atunci când este modificat prețul, dacă acesta depășește media prețurilor celorlalte cărți având același format, să se modifice prețul la valoarea mediei, afișându-se și un mesaj corespunzător.

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
MySQL 5.6 Reference Manual - în special capitolele 2, 3, 11, 12, 13

Rezolvări