SQL – pe intelesul tuturor

Structured Query Language (SQL) constituie standardul care unifica toate sistemele de gestiune a bazelor de date. Se poate considera ca in momentul in care cunosti bine acest limbaj vei sti sa manipulezi orice tip de baza de date, aceasta putandu-se numi Oracle, SQL Server, MySQL, DB2 sau Ingress.

Conceptul relational a dominat in mod indiscutabil lumea bazelor de date (caci exista o asemenea lume) si a aplicatiilor din acest domeniu. Initiatorul acestui proiect a fost E.F. Codd, cercetator la IBM, care la inceputul anilor “70 a definit primele elemente de algebra relationala, elemente care au stat mai tarziu la baza standardului SQL.

Structured Query Language este un limbaj care permite realizarea tuturor operatiunilor asupra bazelor de date cum ar fi crearea si modificarea tabelelor, definirea indecsilor, selectia si modificarea datelor etc. Cel mai important lucru de subliniat in legatura cu SQL este faptul ca este un limbaj standardizat de prestigiosul Institut National American de Standarde (ANSI). Deci se poate concluziona ca in momentul in care cunosti SQL, vei putea sa manipulezi datele din orice sistem relational. Totusi trebuie sa precizam ca exista deosebiri intre diversi producatori de sisteme de gestiune a bazelor de date relationale, dar lucrurile esentiale raman aceleasi fie ca discutam de Oracle, fie de SQL Server sau de DB2.

In acest material ne propunem a face o prezentare a limbajului SQL, urmarind elementele teoretice dar punand indeosebi accent pe aspectele de ordin practic. Vom crea doua baze de date si vom incerca sa lamurim principalele elemente ale comenzilor SQL. Ca suport pentru exemplele noastre vom utiliza mediul MySql, care este actualmente cel mai “la moda” sistem relational, fiind utilizat in special pentru crearea unor site-uri Web interactive in configuratia MySql – server de baze de date, PHP – program de script si Apache – server Web. Acest mediu prezinta doua avantaje esentiale: primul constand in faptul ca este gratuit (il puteti obtine de pe site-ul www.mysql.com), iar al doilea consta in faptul ca singura metoda de interactiune cu bazele de date o constituie introducerea de comenzi SQL din prompt (in acest mod nu veti mai fi “tentati” de alte metode vizuale de creare a tabelelor si realizarea interogarilor, care sunt oferite din belsug de alte sisteme relationale – spre exemplu de Access).

Daca toate aceste amanunte nu va vor determina sa invatati SQL, fiind multumiti sa creati vizual interogarile in alte medii, mai precizam ca in situatia in care veti dori sa creati site-uri interactive utilizand scripturi PHP sau ASP.NET, veti fi obligat sa utilizati limbajul SQL pentru realizarea operatiunilor cu bazele de date.

Elemente de algebra relationala
Dorim sa lamurim, mai intai, cateva elemente esentiale privind conceptul relational.
Informatia ca element primordial, este stocata intr-o baza de date. O baza de date contine unul sau mai multe tabele. Fiecare tabela prezinta mai multe campuri sau atribute, iar fiecare camp prezinta, la randul lui, o serie de proprietati privind tipul si lungimea acestuia (astfel numele este un atribut text, iar salariu un atribut numeric). Fiecare tabela include un numar de inregistrari, care reprezinta practic informatia stocata in tabela respectiva (acest numar fiind limitat la sistemele moderne de gestiune a bazelor de date numai de capacitatea de stocare). In fiecare tabel un camp este definit cheie primara, aceasta fiind identificata unic – practic nefiind admise dublurile in baza de date (un exemplu de cheie primara este atributul marca din nomenclatorul de personal). De asemenea, se mai poate defini in fiecare tabela si o cheie secundara, aceasta fiind cheie primara in alt tabel si nu este identificata unic (atributul Marca in tabela in care se inregistreaza platile catre salariati).

Intre tabele sunt stabilite corelatii, care, potrivit conceptului relational, pot fi de trei tipuri: corelatii 1-1 (cea mai simpla corelatie a unei inregistrari din tabelul 1 ii corespunde o singura inregistrare in tabelul 2), corelatiile 1-n (unei singure inregistrari din tabela 1 ii corespund una sau mai multe inregistrari in tabela 2) si corelatiile n-1 (uneia sau mai multor inregistrari din tabela 1 le corespunde o singura inregistrare din tabela 2).

Corelatiile de tip n-n (mai multor inregistrari din tabelul 1 le corespund mai multe inregistrari din tabelul 2) nu sunt posibile in bazele de date relationale, in acest caz fiind necesara introducerea unei entitati intermediare si ruperea corelatiei n-n in doua corelatii: una
n-1 si respectiv una inversa 1-n.

Crearea bazelor de date si a tabelelor
Presupunand ca aveti MySQL instalat, fie sub Linux fie sub Windows, putem sa mergem cu rationamentul mai departe.
Astfel, vom construi pentru inceput o baza de date simpla numita Zoo. Baza de date include doua tabele in care sunt stocate informatiile despre animalele dintr-o gradina zoologica virtuala. Avem doua tabele, in prima inregistrand animalele din gradina, iar in cea de a doua inregistram evenimentele care apar pentru fiecare animal in parte (nasteri, imbolnaviri etc.). Cele doua tabele sunt corelate prin intermediul campului Cod, camp care este cheie primara in tabela Animale si cheie secundara in tabela Evenimente (corelatia definita in acest caz este de tip 1-n). In fig.1 este prezentata structura bazei de date.

Instructiunile MySql se introduc din promptul programului (o instructiune putand continua si pe mai multe linii) cu specificatia faptului ca ea se incheie prin caracterul punct si virgula. Limbajul MySql nu este case sensitive, deci instructiunile pot fi introduse cu orice fel de litere, fie mici sau mari.

Comenzile utilizate sunt Create database, Show Databases – afisarea bazelor de date si Use – pentru deschiderea unei baze de date.
Pentru tipul datelor, MySql ofera o multitudine de solutii dintre care: tipul de data numeric (tipurile Integer, Float, Double, Real, Decimal), tipul de data calendaristica (tipurile Date, Datetime, Time, Year) si tipul de data text (Char, Varchar, Text, LongText, Enum).
Considerand ca datele si explicatiile de mai sus sunt suficiente pentru intelegerea sistemului MySQL, prezentam in continuare sintaxa generala a comenzii Create Table:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] nume_tabela
Nume_camp tip_camp [NOT NULL | NULL] [DEFAULT default_value] AUTO_INCREMENT]
[PRIMARY KEY] [reference_definition]
Pentru fiecare camp se stabileste numele si tipul acestuia (unul din tipurile enumerate mai sus), putandu-se nominaliza, de asemenea, si o serie de parametri facultativi (sunt acceptate sau nu valorile nule, setarea valorii implicite, campul sa fie autoincrementat sau sa fie creat drept cheie primara).
Crearea tabelei Animale din baza noastra de date se inregistreaza prin instructiunea:
create table animale (cod integer not null auto_increment,nume varchar(20) not null,specie varchar(20) not null,ingrijitor varchar(20) not null,
sex char(1) not null,data_nast date,data_dec date,primary key (cod));
In mod asemanator veti crea si tabela Evenimente, structura acesteia fiind prezentata in figura.
Pentru modificarea structurii unei tabele se utilizeaza comanda Alter table:
ALTER TABLE ADD|DROP|MODIFY (specificatii privind coloana modificata sau nou creata);
Pentru popularea tabelelor vom utiliza instructiunea Insert, care prezinta urmatoarea sintaxa generala:
INSERT INTO [()]
VALUES ();
In cazul exemplului nostru instructiunea de populare a bazei de date ar avea urmatoarea structura:
insert into animale values
(last_insert_id(), “BumboÕ, “ElefantÕ, ÕCosticaÕ, ÕfÕ,Õ1997-01-01Õ,Null)
Aceasta instructiune poate primi ca argument in locul listei de valori o instructiune Select, in acest mod avand posibilitatea popularii tabelelor cu inregistrari existente in alte tabele din baza de date.
In plus, MySql ofera posibilitatea importului inregistrarilor din fisiere text, fiecare rand reprezentand o inregistrare, iar datele fiind separate prin identificatori tab. Pentru realizarea acestei operatiuni utilizam instructiunea:
Load data local infile into table
Pentru stergerea unei tabele din baza de date utilizam instructiunea Drop Table, care are drept argument numele tabelei.

Selectia si modificarea datelor
Cea mai importanta instructiune SQL este SELECT, prin intermediul careia se poate realiza selectia, gruparea si ordonarea datelor:
SELECT [DISTINCT]
FROM
[WHERE ]
[GROUP BY ]
[HAVING ]
[ORDER BY [ASC|DESC]];
Instructiunea Select este cea mai complexa din cele utilizate pana acum in prezentul material. Prin intermediul acesteia se realizeaza selectia pe baza criteriilor stabilite de clauza where, corelarea datelor din mai multe tabele, gruparea si ordonarea datelor, totalizarea pe anumite campuri.
In cele ce urmeaza vom prezenta cateva posibilitati de utilizare a instructiunilor Select pentru interogarea inregistrarilor din cele doua tabele create.
Prin intermediul instructiunea “select * from animale” vom obtine lista completa a tuturor campurilor si inregistrarilor din tabela.
Pentru aflarea animalelor din specia elefant din gradina zoologica vom crea urmatoarea instructiune:
Select * from animale where specie=”Elefant”
Animalele nascute dupa o anumita data aleatoare se obtin prin instructiunea:
Select * from animale where data_nast>”1998-1-15″
Conditiile introduse pentru clauza Where pot fi legate prin operatori logici, interpretati dupa felul lor (in cazul operatorului And toate conditiile trebuie sa fie adevarate, in cazul operatorului Or fiind suficient ca una din conditii sa fie adevarata).
In plus este respectata si ordinea generala de interpretare (sunt testate mai intai conditiile din paranteze), dupa cum putem vedea si din exemplu urmator:
Select * from animale where (specie=”elefant” and sex=”f”) or (specie=”cimpanzeu” and sex=”m”)
Ordonarea datelor se realizeaza prin operatorul Order by, ordinea implicita fiind ascendenta (pentru selectarea in ordine descendenta trebuie utilizata clauza desc):
Select nume,specie from animale order by data_nast desc

MySql ofera o multime de functii interesante dintre care putem distinge cu usurinta: functii matematice, statistice, de lucru cu date calendaristice si cele pentru sirurile de caractere. Aceste functii pot fi utilizate asupra unor date din tabele sau asupra unor constante, prin utilizarea operatorului select urmat de enuntul functiei. Presupunand ca dorim sa obtinem o lista care sa contina animalele din baza noastra de date ordonate descrescator in ordinea varstei vom utiliza functiile: to_days() care transforma data pe care o primeste ca argument intr-un numar de zile trecute de la nasterea lui Cristos (anul 1) si respectiv functia now() care returneaza data curenta a calculatorului. Deci vom realiza urmatoarea interogare:
Select nume,specie,(to_days(now())-to_days (data_nast))/365 as varsta from animale order by varsta desc
Mai putem utiliza functiile year, month, dayofmonth -spre exemplificare putand realiza urmatoarea interogare:
Select nume,specie from animale where month(data_nast)=6
Functia date_add adauga la data specificata un interval. in interogarea urmatoare vom obtine toate animalele nascute in luna urmatoare datei curente:
Select nume,specie,data_nast from animale where month(data_nast) = month(date_
add(now(),interval 1 month))

Un operator interesant de utilizat in interogarea bazelor de date il constituie Like, necesar in special pentru campurile codificate sau de tip text.
Prin intermediul acestui operator putand astfel obtine lista tuturor animalelor al caror nume incepe cu litera “A”:
Select nume,specie where nume like “A%Õ
Caracterul de substitutie “%” va inlocui toate caracterele din sir, iar caracterul “_” va inlocui un singur caracter.
Pentru realizarea gruparilor utilizam clauza group by. Dorind sa obtinem numarul animalelor existente in gradina zoologica din fiecare specie realizam urmatoarea instructiune care utilizeaza functia Count si grupeaza datele dupa specie:
Select specie,count(*) from animale group by specie
In cazul in care ne intereseaza numarul pe specie si sex vom utiliza o grupare atat dupa specie cat si dupa sex:
Select specie,sex,count(*) from animale group by specie,sex
Complicand un pic lucrurile vom trece la lucrul concomitent cu cele doua tabele corelate. Pentru stabilirea corelatiei includem in clauza where o conditie de egalitate intre atributele de legatura din cele doua tabele.
Spre exemplu, daca dorim sa aflam toate evenimentele aparute la animalele supravegheate de un ingrijitor nominalizat, cream instructiunea:
select animale.nume, evenimete.tip_eveniment, evenimete.data_eveniment, evenimente.observatii from animale ,evenimente
where animale.cod = evenimente.cod and animale.ingrijitor=”Costica”
Se poate observa cu usurinta ca specificatiile facute la campuri se realizeaza prin includerea si a numelui tabelului, acest mod de adresare fiind obligatoriu in situatia in care exista nume de campuri identice in ambele tabele. Este insa indicat sa se foloseasca aceasta notatie pentru concretetea codului, in special in situatiile in care interogarea include corelatii si extrage date din foarte multe tabele.
Sa presupunem ca dorim sa obtinem toate nasterile care au avut loc in “gradina”, precum si varsta puilor nou nascuti:
Select a.nume,b.data_eveniment, b.observatii, (to_days(now())-to_days (a.data_nast))/
365 as varsta from animale ,evenimente
from animale as a, evenimente as b
where a.cod = b.cod and b.tip_eveniment=”Nastere”
Pentru reducerea codului, in aceasta instructiune am utilizat aliasul bazelor de date.
Corelatia poate fi realizata cu o tabela diferita, dar se poate realiza o interogare a unei tabele cu ea insasi. Acest lucru poate parea absurd, la prima vedere, dar uneori devine imperios necesar, sa vedem ce a rezultat din interogarea urmatoare:
Select a.nume,a.sex,b.nume,b.sex,a.specie where animale as a,animale as b
Where a.specie=b.specie and a.sex=”m” and sex=”f”
Aceasta interogare este un exemplu interesant al corelatiei tabelei cu ea insasi (in oglinda), prin intermediul ei obtinand imperecherea animalelor dintr-o anumita specie.
Alte instructiuni utilizate in SQL sunt Update si Delete. Update este utilizata pentru modificarea datelor din tabela:
UPDATE
SET =
[WHERE ];
Pentru stergerea datelor din tabele utilizam urmatoarea instructiune
DELETE FROM WHERE ;

Leave a Comment

Your email address will not be published. Required fields are marked *