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
Un SGBD = sistem de gestiune pentru baze de date - SGBD (eng. DBMS = Database Management Systems) este o colecţie de programe care are drept scop indicarea structurii bazei de date, popularea și exploatarea sa, asigurând totodată și protecţia datelor. Aceasta reprezintă o aplicaţie informatică complexă ce însoţeşte baza de date, având ca funcţii definirea (specificarea structurii şi a tipurilor de date stocate), construcţia (popularea) şi manipularea ei (diferite operaţii de la implementarea interogărilor pentru regăsirea informaţiilor până la generarea de rapoarte).
Rolul său este de a facilita accesul la date, fapt ce este realizat prin asigurarea transparenţei reprezentării acestora faţă de utilizatori în cadrul procesului de interacţiune cu ele. Din acest punct de vedere, sistemul de gestiune pentru baze de date este interfaţa între nivelul fizic, cel mai scăzut, şi programele prin care sunt transmise aplicaţiei interogările de regăsire a datelor.
Împreună cu datele propriu-zise (prin date înțelegându-se nu doar la colecţiile de informaţii stocate de către utilizator ci şi dicţionarul de date care conţine meta-date: structura bazei de date, definiţiile tipurilor de date / restricţiile de integritate, viziunile, indecşii precum şi fişierele anexe care conţin un context curent de lucru (parametrii de configurare), acesta formează SBD = sistemul de baze de date (eng. DBS = Database System).
Arhitectura asociată unei baze de date este reprezentată pe trei niveluri, având ca scop separarea aplicaţiilor utilizatorului de informaţiile stocate:
Obiectivele unui sistem de gestiune pentru baze de date sunt:
Toate sistemele de gestiune a bazelor de date oferă un set de funcţii:
Clasificarea sistemelor de gestiune a bazelor de date se poate face după mai multe criterii:
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:
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:
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:
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:
id_proiect → nume_proiect
id_angajat → nume_angajat, pozitie_angajat, salariu_tarifar_angajat
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:
<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.< <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:
Sunt suportate şi unele extensii pentru date spaţiale.
Tipurile de date numerice sunt redate în tabelul de mai jos:
Tipuri de Date Numerice | Valori Posibile |
---|---|
BIT[(M)] | 1 → 64, implicit m=1 |
TINYINT[(M)] [UNSIGNED] [ZEROFILL] | -128 → 127 / 0 → 255 (unsigned) |
BOOL, BOOLEAN | = TINYINT(1) / 0=false, non-0=true |
SMALLINT[(M)] [UNSIGNED] [ZEROFILL] | -32768 → 32767 / 0 → 65535 (unsigned) |
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] | -8388608 → 8388607 0 → 16777215 (unsigned) |
INT[(M)] [UNSIGNED] [ZEROFILL] | -2147483648 → 2147483647 0 → 4294967295 (unsigned) |
INTEGER[(M)] [UNSIGNED] [ZEROFILL] | = INT |
BIGINT[(M)] [UNSIGNED] [ZEROFILL] | -9223372036854775808 → 9223372036854775807 0 → 18446744073709551615 (unsigned) |
DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL] | implicit M=10, D=0; maxim M=65, D=30 |
DEC[(M[,D])] [UNSIGNED] [ZEROFILL] | = DECIMAL ( FIXED e folosit pentru compatibilitatea cu alte SGBD) |
NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL] |
|
FIXED[(M[,D])] [UNSIGNED] [ZEROFILL] |
|
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] | -3.402823466E+38 → -1.175494351E-38 0 1.175494351E-38 → 3.402823466E+38 |
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL] | -1.7976931348623157E+308 → -2.2250738585072014E-308 0 2.2250738585072014E-308 → 1.7976931348623157E+308 |
DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL] | = DOUBLE |
REAL[(M,D)] [UNSIGNED] [ZEROFILL] |
|
FLOAT(p) [UNSIGNED] [ZEROFILL] | p=0→24 = FLOAT p=25→53 = DOUBLE |
Atributele datelor de tip numeric sunt:
UNSIGNED
specifică dacă atributul respectiv reţine informaţii de tip numeric cu sau fără semn (implicit tipurile de date au asociat atributul SIGNED
);ZEROFILL
indică faptul că pe poziţiile libere dintr-un atribut de tip numeric se vor completa cifre de ’0’; atributele ce au asociată această proprietate vor avea în mod automat şi proprietatea UNSIGNED
;AUTO_INCREMENT
va completa cu valoare+1
atributul unei înregistrări pentru care se specifică (la adăugare) NULL
sau 0
.
În schema de mai sus, M
reprezintă numărul total de cifre pe care poate fi reprezentată o valoare de tipul numeric specificat, iar D
numărul de cifre care urmează după virgulă în cazul numerelor reale. În cazul tipurilor de date întregi, M
nu modifică valoarea maximă pe care o poate reţine atributul respectiv, ci se referă la numărul de poziţii care vor fi afişate. O astfel de funcţionalitate poate fi folosită în cazul în care se doreşte să se realizeze o aliniare a informaţiilor vizualizate.
Exemplu. Considerăm un atribut cu definiţia INT(5) ZEROFILL
care are valoarea 1234
pentru o înregistrare. Acesta se va afişa sub forma 01234
.
Observaţie.
SERIAL
reprezintă un alias pentru secvenţa BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE
.
SERIAL DEFAULT VALUE
reprezintă un alias pentru secvenţa NOT NULL AUTO_INCREMENT UNIQUE
.
Toate operaţiile aritmetice se fac folosind valori BIGINT
sau DOUBLE
fără semn, astfel că nu trebuie folosite valori mai mari de posibilitatea de stocare a acestora (63 de biţi).
Funcţiile care pot fi folosite pentru datele de tip numeric sunt:
ABS() | CEIL() | CRC32() | LN() | PI() | ROUND() |
ACOS() | CEILING() | DEGREES() | LOG10() | POW() | SIGN() |
ASIN() | CONV() | DIV() | LOG2() | POWER() | SIN() |
ATAN2() | COS() | EXP() | LOG() | RADIANS() | SQRT() |
ATAN() | COT() | FLOOR() | MOD() | RAND() | TAN() |
Tipurile de date şir de caractere sunt redate în tabelul de mai jos:
Tipuri de Date Şir de Caractere |
---|
[NATIONAL] CHAR[(M)] [CHARACTER SET charset_name] [COLLATE collation_name] |
[NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name] |
BINARY(M) |
VARBINARY(M) |
TINYBLOB |
TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name] |
BLOB[(M)] |
TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name] |
MEDIUMBLOB |
MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name] |
LONGBLOB |
LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name] |
ENUM('value1','value2',…) [CHARACTER SET charset_name] [COLLATE collation_name] |
SET('value1','value2',…) [CHARACTER SET charset_name] [COLLATE collation_name] |
Pentru mai multe tipuri de date şir de caractere se specifică atributul CHARACTER SET
(sau CHARSET
) ce indică gama din care fac parte caracterele (frecvent utilizate sunt folosite latin1
sau utf8
). În cazul în care se specifică valoarea binary
pentru setul de caractere, se face în mod automat convertirea la tipul de date corespunzător, dacă este cazul: CHAR
→ BINARY
, VARCHAR
→ VARBINARY
, TEXT
→ BLOB
. Nu este şi cazul tipului ENUM
. Pentru a vizualiza lista seturilor de caractere disponiile pe sistemul MySQL instalat, se foloseşte instrucţiunea SHOW CHARACTER SET
.
Fiecare set de caractere are asociat şi un mod de asamblare (eng. COLLATION
) care poate fi consultat folosind interogarea SHOW COLLATION LIKE 'charset%';
unde charset
este setul de caractere pentru care se doreşte afişarea modului de asamblare.
Prin cuvântul-cheie NATIONAL
se specifică faptul că tipul de date respectiv trebuie să definească un set de caractere. Implicit, acesta este utf8
.
Diferenţa între tipurile de date CHAR
şi VARCHAR
e dată de memoria pe care sistemul de gestiune pentru baze de date o alocă. În cazul tipului de date CHAR
se va aloca un spaţiu de memorie fix, indiferent de valoarea pe care o are atributul în cauză (se adaugă spaţii la dreapta până la dimensiunea specificată). În cazul în care nu se specifică nici o dimensiune, valoarea implicită a lungimii este 1. Pentru tipul de date VARCHAR
spațiul de memorie va fi variabil, depinzând de valoarea pe care o are atributul respectiv (reţinându-se totodată şi dimensiunea acesteia, ca prefix; din acest motiv, în cazul cel mai defavorabil, dimensiunea tipului de date VARCHAR
va fi chiar mai mare decât cea specificată în definiţie, adăugându-se 1-2 octeţi) Dimensiunea maximă pentru tipul VARCHAR
este 65535 octeţi, dar această dimensiune nu poate fi depăşită pentru întregul tuplu. De asemenea, atunci când se foloseşte setul de caractere utf8
(în care reprezentarea unui caracter ocupă până la 3 octeţi), dimensiunea maximă va fi 21844.
În cazul tipului de date BINARY
/ VARBINARY
se vor reţine şiruri de caractere sub formă binară, indiferent de setul de caractere utilizat.
Distincţia BINARY
/ CHAR
ca stocare a şirurilor de caractere (binar / nonbinar) se regăseşte în diferenţa între BLOB
(Binary Large Object) şi TEXT
, ultimele putând reţine şiruri de caractere având dimensiuni foarte mari.
Dimensiunile maxime ale tipurilor de date pentru şiruri de caractere sunt:
Tip de Date | Dimensiune Maximă |
---|---|
TINYBLOB / TINYTEXT | 28 - 1 = 255 octeţi, din care 1 pentru reţinerea dimensiunii |
BLOB / TEXT | 216 - 1 = 65535 octeţi, din care 2 pentru reţinerea dimensiunii |
MEDIUMBLOB / MEDIUMTEXT | 224 - 1 = 16777215 octeţi, din care 3 pentru reţinerea dimensiunii |
LONGBLOB / LONGTEXT | 232 - 1 = 4294067295 octeţi (4GB), din care 4 pentru reţinerea dimensiunii |
Tipul de date ENUM
indică faptul că valoarea atributului poate fi preluată doar din variantele specificate. Teoretic pot fi specificate 65535 valori (practic aproximativ 3000).
Tipul de date SET
se foloseşte pentru câmpuri care pot avea zero sau mai multe valori care fac parte dintr-o serie de variante specificate. Pot fi specificate maxim 64 de valori.
Funcţiile care pot fi folosite pentru datele de tip şir de caractere sunt:
ASCII() | EXPORT_SET() | LENGTH() | MID() | REPEAT() | SPACE() |
BIN() | FIELD() | LIKE | NOT LIKE | REPLACE() | STRCMP() |
BIT_LENGTH() | FIND_IN_SET() | LOAD_FILE() | NOT REGEXP | REVERSE() | SUBSTR() |
CHAR_LENGTH() | FORMAT() | LOCATE() | OCT() | RIGHT() | SUBSTRING_INDEX() |
CHAR() | HEX() | LOWER() | OCTET_LENGTH() | RLIKE | SUBSTRING() |
CHARACTER_LENGTH() | INSERT() | LPAD() | ORD() | RPAD() | TRIM() |
CONCAT_WS() | INSTR() | LTRIM() | POSITION() | RTRIM() | UCASE() |
CONCAT() | LCASE() | MAKE_SET() | QUOTE() | SOUNDEX() | UNHEX() |
ELT() | LEFT() | MATCH | REGEXP | SOUNDS LIKE | UPPER() |
Atributele ce reţin date calendaristice pot avea următoarele tipuri:
Tipuri de Date Calendaristice | Format Valori Posibile |
---|---|
DATE | ’YYYY-MM-DD’ ’1000-01-01’ → ’9999-12-31’ |
DATETIME[(fsp)] | ’YYYY-MM-DD HH:MM:SS[.fraction]’ ’1000-01-01 00:00:00.000000’ → ’9999-12-31 23:59:59.999999’ |
TIMESTAMP[(fsp)] | ’YYYY-MM-DD HH:MM:SS[.fraction]’ UTC ’1970-01-01 00:00:01.000000’ → ’2038-01-19 03:14:07.999999’ |
TIME[(fsp)] | ’HH:MM:SS[.fraction]’ ’-838:59:59.000000’ → ’838:59:59.000000’ |
YEAR[(2|4)] | ’YY’ / ’YYYY’ ’70’ → ’69’ (1970 → 2069) ’1901’ → ’2155’ |
Dacă în tipul de date DATETIME
informaţiile sunt reţinute în forma indicată de utilizator, în tipul de date TIMESTAMP
acestea sunt automat convertite la UTC
(Universal Time Coordinated) în funcţie de zona în care se găseşte instalat serverul MySQL, reţinându-se ca număr de secunde care s-au scurs de la 1970-01-01 00:00:00.000000 UTC.
De regulă, tipul de date TIMESTAMP
este folosit ma ales pentru memorarea celei mai recente modificări realizată asupra unei înregistrări (în cazul operaţiilor INSERT
şi UPDATE
), fiind utilizată împreună cu clauzele DEFAULT CURRENT_TIMESTAMP
respectiv ON UPDATE CURRENT_TIMESTAMP
. Acelaşi comportament poate fi obţinut prin specificarea atributului respectiv ca fiind NULL
(dacă nu violează vreo constrângere de integritate).
Tipul de date TIME
poate avea şi valori negative şi se foloseşte nu numai pentru o anumită oră din zi, ci şi pentru a indica un interval de timp.
Tipurile de date TIME
, DATETIME
şi TIMESTAMP
permit specificarea de fracţiuni de secundă (până la 6 zecimale). Implicit, parametrul fsp
are valoarea 0.
În cazul tipului de date YEAR(2)
valorile de la 0 la 69 au semnificaţia anilor 2000-2069 în timp ce valorile de la 70 la 99 reprezintă anii 1970-1999. Totuşi, începând cu versiunea 5.6.6, acest tip de date este considerat depăşit, fiind convertit în mod automat la YEAR(4)
.
Funcţiile care pot fi folosite pentru datele de tip dată calendaristică sunt:
ADDDATE() | DATEDIFF() | LOCALTIME()* | SECOND() | TO_DAYS() |
ADDTIME() | DAY() | LOCALTIMESTAMP()* | STR_TO_DATE | TO_SECONDS() |
CONVERT_TZ() | DAYNAME() | MAKETIME()* | SUBDATE() | UNIX_TIMESTAMP() |
CURDATE() | DAYOFMONTH() | MICROSECOND() | SUBTIME() | UTC_DATE() |
CURRENT_DATE()* | DAYOFWEEK() | MINUTE() | SYSDATE() | UTC_TIME() |
CURRENT_TIME()* | DAYOFYEAR() | MONTH() | TIME_FORMAT() | UTC_TIMESTAMP() |
CURRENT_TIMESTAMP()* | EXTRACT() | MONTHNAME() | TIME_TO_SEC() | WEEK() |
CURTIME() | FROM_DAYS() | NOW() | TIME() | WEEKDAY() |
DATE_ADD() | FROM_UNIXTIME() | PERIOD_ADD() | TIMEDIFF() | WEEKOFYEAR() |
DATE_FORMAT() | GET_FORMAT() | PERIOD_DIF() | TIMESTAMP() | YEAR() |
DATE_SUB() | HOUR() | QUARTER() | TIMESTAMPADD() | YEARWEEK() |
DATE() | LAST_DAY | SEC_TO_TIME() | TIMESTAMPDIFF() |
Funcţiile marcate cu * există şi ca variabile propriu-zise având aceeaşi denumire şi comportament.
Conversia între diferite tipuri de date se face explicit folosind funcţia CAST
:
CAST(value AS type)
spre exemplu:
CAST('123.45' AS DECIMAL(5,2)) = 123.45 CAST('2013-10-14' AS DATETIME) = '2013-10-14 00:00:00' CAST(123 AS CHAR) = ’123’
În operaţii în care intervin valori având tipuri de date diferite, în măsura în care este posibil, conversia este realizată în mod implicit de sistemul de gestiune pentru baze de date.
Î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;
database_name.object_name
.
Instrucţiunea USE
(ca şi instrucţiunea QUIT
) nu trebuie terminată prin ';'. În cazul în care într-o sesiune va fi utilizată o singură bază de date, pentru a evita folosirea instrucţiunii USE
, se poate specifica baza de date implicită prin comanda mysql -u username -p database_name
în momentul când se reaizează conexiunea.
Instrucțiunea SELECT DATABASE()
indică denumirea bazei de date curente.
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:
data_type
sau spatial_type
poate lua orice valoare din cele disponibile (numerice, șiruri de caractere, date calendaristice, extensii pentru date spațiale);NULL
(implicit) / NOT NULL
controlează permisiunea ca atributul în cauză să accepte și valori nule;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ă;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;COMMENT
permite documentarea unui atribut printr-un text care nu poate depăşi 1024 de caractere;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ă;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).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
)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; 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;ASC
/ DESC
, referitoare la indecși, sunt parsate dar ignoratăemomentan (ordonarea indecşilor se face crescător indiferent de valoarea precizată);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
;BTREE
) sau o funcție hash (HASH
).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.MATCH
nu sunt recunoscute de motoarele MySQL curente, făcând ca eventualele clauze ON UPDATE
sau ON DELETE
ce îi succed să fie ignorate. Conform standardului SQL standard, clauzele MATCH
au rolul de a permite unei chei străine să conţină şi valori de tip NULL
, care nu vor referi nici un fel de înregistrare.
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:
CREATE DATABASE IF NOT EXISTS veterinary_clinic; USE veterinary_clinic; CREATE TABLE IF NOT EXISTS breed ( id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL, name VARCHAR(50) NOT NULL, description VARCHAR(1000) ); CREATE TABLE IF NOT EXISTS owner ( id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, email VARCHAR(50), phone_number INT(10) NOT NULL, bank_account VARCHAR(50) NOT NULL ); ALTER TABLE owner ADD CONSTRAINT owner_email_format CHECK (email LIKE '%@%.%'); CREATE TABLE IF NOT EXISTS animal ( id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL, name VARCHAR(50) NOT NULL, breed_id INT(10) UNSIGNED, gender CHAR(1) DEFAULT 'M', birth_date DATETIME NOT NULL, death_date DATETIME, owner_id INT(10) UNSIGNED, father_id INT(10) UNSIGNED, mother_id INT(10) UNSIGNED, FOREIGN KEY(breed_id) REFERENCES breed(id) ON UPDATE CASCADE ON DELETE SET NULL, FOREIGN KEY(owner_id) REFERENCES owner(id) ON UPDATE CASCADE ON DELETE SET NULL ); ALTER TABLE animal ADD CONSTRAINT father_id_foreign_key FOREIGN KEY(father_id) REFERENCES animal(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE animal ADD CONSTRAINT mother_id_foreign_key FOREIGN KEY(mother_id) REFERENCES animal(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE animal ADD CONSTRAINT sex_possible_values CHECK (gender in ('M', 'F')); CREATE TABLE IF NOT EXISTS doctor ( id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, title VARCHAR(20) NOT NULL, speciality VARCHAR(20), code VARCHAR(20) NOT NULL, email VARCHAR(50), phone_number INT(10) NOT NULL ); ALTER TABLE doctor ADD CONSTRAINT doctor_email_format CHECK (email LIKE '%@%.%'); CREATE TABLE IF NOT EXISTS diagnosis ( id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL, name VARCHAR(50) NOT NULL, description VARCHAR(1000) NOT NULL, severity VARCHAR(20) NOT NULL, cure VARCHAR(1000) NOT NULL ); CREATE TABLE IF NOT EXISTS medical_record ( id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL, animal_id INT(10) UNSIGNED, doctor_id INT(10) UNSIGNED, diagnosis_id INT(10) UNSIGNED, date DATETIME NOT NULL, treatment VARCHAR(1000), FOREIGN KEY(animal_id) REFERENCES animal(id) ON UPDATE CASCADE ON DELETE SET NULL, FOREIGN KEY(doctor_id) REFERENCES doctor(id) ON UPDATE CASCADE ON DELETE SET NULL, FOREIGN KEY(diagnosis_id) REFERENCES diagnosis(id) ON UPDATE CASCADE ON DELETE SET NULL );
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.
INSET INTO table_name() VALUES();
are ca efect introducerea unei înregistrări în care toate atributele au valorile implicite.
Pot fi introduse date specificate prin expresii (referind, spre exemplu, valori care sunt derivate din coloanele anterioare - cu excepția celor definite ca AUTO_INCREMENT
), realizându-se în mod automat conversii dacă tipurile de date rezultate în urma operaţiilor implicate nu corespund celor pe care le au coloanele respective.
Pot fi specificate valori pentru mai multe înregistrări simultan, însă fiecare grup (corespunzând unui rând din tabelă) trebuie să fie separat prin paranteze şi virgule.
În cazul în care datele provin dintr-o interogare de tip SELECT
, motorul bazei de date va indica numărul de înregistrări pe care le-a întors aceasta, precum şi numărul de valori duplicate care nu au fost adăugate şi erorile produse datorită unor incompatibilităţi în privinţa tipurilor de date.
Instrucţiunea INSERT
permite folosirea următoarelor clauze:
DELAYED
– datele ce se doresc introduse sunt plasate într-o zonă de memorie şi controlul este redat utilizatorului (operaţia este non-blocantă), informaţiile fiind adăugate în tabela respectivă în momentul în care nu există cereri de citire / scriere asupra acesteia (chiar şi în momentul în care sunt introduse datele în cauză, se verifică periodic faptul că nu există alte cereri de citire / scriere asupra tabelei respective, iar în cazul în care se produc astfel de evenimente, transferul din zona de memorie în tabela respectivă este întrerupt, cedându-se accesul proceselor în cauză); directiva este ignorată atunci când datele provin dintr-o interogare de tip SELECT
sau atunci când este folosită clauza ON DUPLICATE KEY UPDATE
;LOW_PRIORITY
– operaţia de adăugare a datelor devine blocantă, aşteptând după toate cererile de citire / scriere asupra tabelei, până când astfel de solicitări nu mai există; este recomandat să nu se folosească această opţiune, mai ales în mediile distribuite unde accesul concurent este intens;HIGH_PRIORITY
– operaţia de adăugare a datelor se execută imediat, chiar înaintea altor cereri de citire / scriere concurente care se desfăşurau atunci când aceasta este realizată;IGNORE
– determină ca erorile produse la adăugarea unor înregistrări să nu mai fie semnalate ca atare, continuându-se operaţia chiar şi în cazul în care astfel de incidente se produc; datele care nu respectă constrângerile tabelei nu vor fi însă introduse, emiţându-se eventual nişte avertismente după ce întreaga operaţie s-a încheiat;ON DUPLICATE KEY UPDATE
– în cazul în care sunt identificate date care nu respectă constrângerile de tip PRIMARY KEY
/ UNIQUE
, înregistrarea în cauză este actualizată cu valorile respective.
Exemple. Exemple de populare a tabelelor definite anterior folosind instrucțiuni de tip INSERT
ar putea fi:
USE veterinary_clinic; INSERT LOW_PRIORITY INTO breed (name, description) VALUES ('dog', 'The domestic dog (Canis lupus familiaris, or Canis familiaris) is a member of the Canidae family of the mammalian order Carnivora. The term \"domestic dog\" is generally used for both domesticated and feral varieties. The dog was the first domesticated animal and has been the most widely kept working, hunting, and pet animal in human history. The word \"dog\" can also refer to the male of a canine species, as opposed to the word \"bitch\" which refers to the female of the species.'), ('cat','The domestic cat (Felis catus or Felis silvestris catus) is a small, usually furry, domesticated, and carnivorous mammal. It is often called a housecat when kept as an indoor pet, or simply a cat when there is no need to distinguish it from other felids and felines. Cats are often valued by humans for companionship, and their ability to hunt vermin and household pests.'), ('guinea pig', NULL), ('horse', 'The horse (Equus ferus caballus) is one of two extant subspecies of Equus ferus. It is an odd-toed ungulate mammal belonging to the taxonomic family Equidae. The horse has evolved over the past 45 to 55 million years from a small multi-toed creature into the large, single-toed animal of today. Humans began to domesticate horses around 4000 BC, and their domestication is believed to have been widespread by 3000 BC. Horses in the subspecies caballus are domesticated, although some domesticated populations live in the wild as feral horses. These feral populations are not true wild horses, as this term is used to describe horses that have never been domesticated, such as the endangered Przewalski\'s horse, a separate subspecies, and the only remaining true wild horse. There is an extensive, specialized vocabulary used to describe equine-related concepts, covering everything from anatomy to life stages, size, colors, markings, breeds, locomotion, and behavior.'), ('gold fish', NULL); INSERT IGNORE INTO owner SET first_name='Sarah', last_name='LEE', email='sarah.lee@lavabit.com', phone_number='123456789', bank_account='US01AABB0000000001'; INSERT IGNORE INTO owner SET first_name='William', last_name='LOPEZ', email='william.lopez@aim.com', phone_number='456789123', bank_account='UK02CCDD0000000002'; INSERT IGNORE INTO owner SET first_name='Samuel', last_name='JONES', email='samuel.jones@fastmail.com', phone_number='789123456', bank_account='DE03EEFF0000000003'; INSERT IGNORE INTO owner SET first_name='William', last_name='THOMPSON', email='william.thompson@lmyway.com', phone_number='123789456', bank_account='FR04GGHH0000000004'; INSERT IGNORE INTO owner SET first_name='Richard', last_name='MARTIN', email='richard.martin@myspace.com', phone_number='789456123', bank_account='IT05IIJJ0000000005'; INSERT HIGH_PRIORITY INTO doctor(first_name, last_name, title, speciality, code, email, phone_number) SELECT d.first_name, d.last_name, t.name, s.name, d.code, d.email, d.phone_number FROM physician_catalog.doctor d, physician_catalog.title t, physician_catalog.speciality s WHERE t.id = d.title_id AND s.id = d.speciality_id; INSERT INTO diagnosis VALUES (NULL, 'giardiasis', 'Giardiasis is a protozoal, parasitic, gastrointestinal zoonotic disease in humans and domestic and wild animals.', 'medium', 'nitroimidazole derivatives, benzimidazole compounds or acridine dyes'), (NULL, 'rabies', 'Rabies is a severely fatal, viral, neurological disease of mammals.', 'high', 'There is no treatment once the clinical signs appear.'), (NULL, 'dermatophytosis', 'Dermatophytosis is a fungal skin disease that commonly affects humans as well as wild and domestic animals', 'low', 'Dermatophyte infections are treated with a variety of topical and oral antifungal drugs.'), (NULL, 'mycobacteriosis', 'Mycobacteriosis is a bacterial, systemic, granulomatous skin disease that occurs in aquarium and culture food fish and can affect humans.', 'low', ' Antibiotic therapy may be warranted to prevent progression to deep infection.'), (NULL, 'malignant catarrhal fever', 'Malignant catarrhal fever (MCF) is a serious, often fatal, viral disease affecting cattle, bison, deer, moose, exotic ruminants, and pigs.', 'severe', 'There is no cure discovered so far');
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:
+
, -
, *
, /
, %
);=
, <>
sau !=
, <
, ⇐
, >
, >=
, BETWEEN
, IN
, IS NULL
, IS NOT NULL
, LIKE
, REGEXP
, SOUNDS LIKE
);NOT
sau !
, AND
sau &&
, OR
sau ||
, XOR
);&
, |
, <<
, >>
, ^
);
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 *
.
AS
întrucât permite semnalarea de erori dacă se omite o virgulă între câmpurile ale căror valori se doresc a fi vizualizate (altfel, acestea pot fi interpretate ca fiind alias-uri).
Clauza FROM
indică tabelele ale căror date se doresc a fi vizualizate, acestea putând avea asociat la rândul lor un alias (prescurtare) ce poate fi folosit pentru prefixarea numelor de atribute.
Clauza WHERE
specifică condiţiile ce trebuie îndeplinite de atributele ale căror valori se doresc a fi afişate. Dacă această clauză lipseşte, sunt selectate toate informaţiile în cauză.
În situaţia în care se doreşte gruparea informaţiilor după valorile unui anumit câmp, se poate folosi clauza GROUP BY
. Aceasta realizează suplimentar şi ordonarea valorilor, ceea ce poate determina o încetinire a vitezei de execuţie. Pentru a forţa motorul bazei de date să nu realizeze şi operaţia de sortare, trebuie adăugată clauza ORDER BY NULL
. Parametrii pe care îi poate primi clauza sunt denumiri (respectiv alias-uri) de coloane precum şi valori care indică ordinea lor în expresia SELECT
. Clauza WITH ROLLUP
permite vizualizarea de totaluri pentru categoriile care au fost grupate.
Clauza ORDER BY
sortează conţinutul câmpurilor pe care le primeşte ca parametri crescător (implicit) sau descrescător, după cum sunt specificate cuvintele-cheie ASC
sau DESC
. În cazul interogărilor imbricate, dacă se realizează sortări după acelaşi câmp, va prevala opţiunea indicată în cazul interogării exterioare, dacă ordinea specificată este diferită.
Clauza HAVING
este folosită de regulă pentru coloane specificate în clauza GROUP BY
sau pentru cele utilizate împreună cu funcţii agregate.
Dacă se doreşte restricţionarea numărului de rezultate întoarse, poate fi utilizată clauza LIMIT
ce poate primi unul sau doi parametri, indicându-se eventual şi înregistrarea de la care se doreşte căutarea, numerotarea rândurilor făcându-se de la 0. Cu alte cuvinte LIMIT 0, row_count
= LIMIT row_count
.
Clauza PROCEDURE
specifică rutina stocată care ar trebui să proceseze datele rezultate în urma interogării.
Funcţionalitatea SELECT … INTO
este utilizată pentru salvarea datelor într-una (sau mai multe) variabile, într-un fişier (folosind un anumit format) sau pentru reţinerea unei singure înregistrări într-un fişier (DUMPFILE
) fără nici un fel de formatare.
Într-un context distribuit, pot fi folosite clauzele FOR UPDATE
care blochează pentru citire și scriere înregistrările selectate, respectiv LOCK IN SHARE MODE
ce permite citirea acestora, dar nu şi actualizarea / modificarea lor până la sfârşitul tranzacţiei.
Alte clauze ce pot fi folosite cu instrucţiunea SELECT
sunt:
ALL
(valoare implicită), respectiv DISTINCT
specifică includerea duplicatelor în rezultatele întoarse sau selectarea exclusivă a valorilor unice;HIGH_PRIORITY
oferă operaţiei prioritate asupra altor comenzi, de aceea trebuie să fie utilizată doar pentru acele instrucţiuni urgente, al căror timp de execuţie nu este foarte mare;STRAIGHT_JOIN
forţează modulul de optimizare a bazei de date să realizeze asocierea tabelelor în ordinea în care acestea sunt precizate în clauza FROM
;SQL_SMALL_RESULT
şi SQL_BIG_RESULT
sunt utilizate cu DISTINCT
şi GROUP BY
pentru a transmite modulului de optimizare faptul că rezultatul are o dimensiune mică respectiv mare astfel încât acesta să poată folosi pentru reţinerea acestuia tabele temporare pe disc (ceea ce implică o viteză de execuţie crescută) respectiv realizarea operaţiei de sortare după atributele indicate şi evitarea utilizării de resurse suplimentare;SQL_BUFFER_RESULT
permite utilizarea de tabele temporare eliberând resurse dacă transmiterea rezultatului la client durează mai mult timp; opţiunea nu poate fi folosită pentru interogări imbricate sau în cazul când se foloseşte clauza UNION
;SQL_CALC_FOUND_ROWS
forţează calcularea numărului de rezultate întoarse, făcând abstracţie de constrângerea LIMIT
;SQL_CACHE
şi SQL_NO_CACHE
face ca rezultatul întors să fie reţinut într-o zonă de memorie asociată interogărilor în cazul în care nu există deja (ca urmare a altei operaţii), respectiv nu verifică această posibilitate.Exemplu. În situația în care se dorește afișarea listei de animale pentru care se afișează informații precum denumirea, rasa, sexul, vârsta, numele și prenumele aparținătorului, numele părinților (dacă sunt disponibile), denumirile bolilor de care au suferit, numele și prenumele medicilor care i-au tratat, limitând rezultatele la înregistrările valide (animale cu data nașterii în trecut), s-ar putea folosi următoarea interogare:
SELECT a.name AS name, b.name AS breed, a.gender AS gender, YEAR(CURRENT_TIMESTAMP) - YEAR(a.birth_date) - (MONTH(CURRENT_TIMESTAMP) < MONTH(a.birth_date) OR (MONTH(CURRENT_TIMESTAMP) = MONTH(a.birth_date) AND DAY(CURRENT_TIMESTAMP) < DAY(a.birth_date))) AS age, CONCAT(o.first_name, ' ' , o.last_name) AS owner, COALESCE((SELECT name FROM animal where id=COALESCE(a.father_id,'0')),'-') AS father, COALESCE((SELECT name FROM animal where id=COALESCE(a.mother_id,'0')),'-') AS mother, (SELECT GROUP_CONCAT(DISTINCT d.name) FROM diagnosis d, medical_record mr WHERE d.id = mr.diagnosis_id AND mr.animal_id=a.id) AS diseases, (SELECT GROUP_CONCAT(DISTINCT CONCAT(d.first_name,' ', d.last_name)) FROM doctor d, medical_record mr WHERE d.id = mr.doctor_id AND mr.animal_id=a.id) AS physicians FROM animal a, breed b, owner o WHERE b.id = a.breed_id AND o.id = a.owner_id HAVING age > 0;
+-------+-------+--------+------+------------------+--------+--------+---------------------------+----------------------------------------------+ | name | breed | gender | age | owner | father | mother | diseases | physicians | +-------+-------+--------+------+------------------+--------+--------+---------------------------+----------------------------------------------+ | Rocky | dog | M | 13 | William THOMPSON | - | - | malignant catarrhal fever | Harry LEWIS,Thomas CARTER | | Koby | dog | M | 9 | William THOMPSON | Rocky | - | NULL | NULL | | Hutch | horse | M | 4 | Richard MARTIN | - | - | rabies | Richard ALLEN,Rachel RODRIGUEZ,Thomas CARTER | +-------+-------+--------+------+------------------+--------+--------+---------------------------+----------------------------------------------+ 3 rows in set (0.00 sec)
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:
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)
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:
JOIN
, CROSS JOIN
şi INNER JOIN
(spre diferenţă de SQL standard în care există nişte mici diferenţe de sintaxă) căci toate produc ca rezultat produsul cartezian, în lipsa specificării condiţiei.
NULL
; în acest caz, se pot folosi cuvintele cheie LEFT
| RIGHT
JOIN
, specificând partea relaţiei ce va include toate rezultatele.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
.
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:
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 | +----+-------+----------+--------+---------------------+------------+----------+-----------+-----------+----+-------+----------+--------+---------------------+------------+----------+-----------+-----------+
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:
AVG() | valoarea medie | STD() | deviaţia standard a populaţiei |
BIT_AND() | AND pe biţi | STDDEV_POP() | deviaţia standard a populaţiei |
BIT_OR() | OR pe biţi | STDDEV_SAMP() | deviaţia standard a eşantionului |
BIT_XOR() | XOR pe biţi | STDDEV() | deviaţia standard a populaţiei |
COUNT(DISTINCT) | numărul valorilor distincte | SUM() | suma |
COUNT() | numărul de rânduri | VAR_POP() | varianţa standard a populaţiei |
GROUP_CONCAT() | şir de caractere concatenat | VAR_SAMP() | varianţa eşantionului |
MAX() | valoarea maximă | VARIANCE() | varianţa standard a populaţiei |
MIN() | valoarea minimă |
Exemplu. Determinarea numărului de consultații de care a beneficiat fiecare animal de companie în parte în cadrul clinicii veterinare poate fi realizat prin instrucțiunea:
SELECT a.name AS name, (SELECT COUNT(*) FROM medical_record mr WHERE mr.animal_id = a.id) AS number_of_examinations FROM animal a;
MySQL permite definirea de programe stocate ce pot fi clasificate astfel:
CALL
;
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:
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:
SELECT
nu pot conţine interogări imbricate în cadrul clauzei FROM
;SELECT
nu pot conţine variabile de sistem sau definite de utilizator;TEMPORARY
şi nici nu pot fi create vizualizări temporare;SELECT
nu trebuie să depăşească 64 de caractere;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:
Caracteristicile unui cursor sunt:
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:
Î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:
AUTOCOMMIT
ce gestioneză momentul la care sunt executate instrucțiunile1
(implicit): modificările sunt vizibile imediat;0
: modificările sunt stocate pe discul local în momentul în care s-a apelat una din instrucțiunile COMMIT
sau ROLLBACK
;TRANSACTION ISOLATION LEVEL
care specifică nivelul de izolare pentru tranzacții (gradul de interacțiune cu alte tranzacții)READ UNCOMMITTED
- sunt vizibile modificări nesalvate de către alte tranzacții, ceea ce reprezintă o vulnerabilitate față de datele fantomă și citiri irepetabileREAD COMMITTED
- se operează cu valorile stocate pe discul local de către alte tranzacții ceea ce ridică probleme legate de citirile irepetabileREPEATABLE 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 respectiveSERIALIZABLE
- 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 localEste posibilă și realizarea de pseudo-tranzacții, prin blocarea / deblocarea anumitor privilegii (scriere, citire) pentru alte tranzacții la nivel de:
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:
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 /
), 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:
mysql -h host -u user -p < filename
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 este identificată prin numele autorului sau a autorilor săi, titlul volumului, editura unde a fost publicată, anul apariţiei, ediţia, colecţia din care face parte, domeniul în care poate fi încadrată. Totodată, pentru fiecare carte se reţine o descriere şi informaţii cu privire la stocul existent şi la preţ. Este obligatoriu ca informaţiile ediţie, stoc existent şi preţ să fie numere pozitive.
Pentru fiecare autor se va reţine şi o scurtă biografie.
Librăria emite comenzi de aprovizionare către edituri şi facturi la clienţi. Atât comanda de aprovizionare cât şi factura vor fi identificate printr-un cod (unic) – spre exemplu serie şi număr –, data la care a fost emisă, starea precum şi entitatea căreia i se adresează (editură / client). Ele pot conţine mai multe produse, pentru fiecare reţinându-se cantitatea.
O editură este caracterizată prin denumire, cod unic de identificare, descriere, localitatea, regiunea şi ţara în care operează.
Un client este identificat prin CNP, nume, prenume, adresa, telefon, email. Presupunând că librăria dispune şi de o platformă on-line pentru achiziţii, pentru fiecare client se vor reţine tipul (administrator / client), precum şi rolul (super-administrator, administrator simplu / client inregistrat, client verificat). Cu excepţia numărului de telefon, nici un câmp nu poate fi vid. Pentru email trebuie să se respecte formatul username@domain.country
.
CHECK
, chiar dacă MySQL nu le implementează încă.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])
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