Limbajul SQL

Limbajul SQL

Limbajul SQL (Structured Query Language) este limbajul neprocedural  (declarativ) cel mai utilizat la interogarea bazelor de date relationale. El respecta cel mai bine specificatiile algebrei relationale.

SQL cuprinde un set redus de comenzi care însa sunt suficiente pentru:

- crearea, modificarea si stergerea elementelor unei baze de date: tabele, vederi si indecsi

- adaugarea, modificarea si stergerea înregistrarilor din tabele

- interogarea bazei de date prin întrebari structurate

- comenzi de securizare, control al accesului concurent si asigurarea integritatii

Toate informatiile referitoare la o baza de date se retin în tabele sistem: structura de tabele si vederi, definitii de câmpuri din tabele, fisiere index, informatii de acces etc.

Limbajul SQL fiind un limbaj declarativ nu permite implementarea unor algoritmi de prelucrare care sa parcurga înregistrarile într-o anumita secventa. Din acest motiv toate implementarile practice combina limbajul SQL cu un limbaj procedural sau functional (Visual dBase, Oracle PL/SQL, Visual Basic).

Limbajul SQL standard defineste mai multe tipuri de interogari:

• pentru crearea structurii bazei de date: CREATE DATABASE, CREATE TABLE, CREATE VIEW, DROP TABLE, DROP VIEW, ALTER TABLE etc.
• pentru gestionarea bazei de date: START DATABASE, STOP DATABASE, GRANT, REVOKE PRIVILEGE, SET TRANSACTION, ROLLBACK, COMMIT
• pentru gestionarea înregistrarilor: INSERT, DELETE, UPDATE
• pentru cautari sau interogari de date: SELECT.

Implementarea SQL în Visual dBASE

In Visual dBase se implementeaza  doua variante de limbaj SQL:

• Local SQL care se aplica asupra fisierelor de date existente în dBase si care sunt vazute ca tabele ale Bazei de date
• SQL extern care transmite spre un server extern de Baze de Date (Oracle, Microsoft SQL Server, DB2 IBM) o comanda de interogare, care va fi executata pe serverul extern si se va primi rezultatul ca o tabela. Dupa conectarea la serverul extern se utilizeaza comanda SQLEXEC pentru a transmite întrebarea , care va respecta sintaxa serverului extern si va indica numele fisierului .dbf  (tabela) care va primi rezultatul:

da= SQLEXEC('SELECT P.Nume, P.Adresa, M.Nrm, M.Tip,M.Cap_c ;

FROM stud P, Masini M WHERE P.Nrm=M.Nrm AND M.Cap_c >1600','REZ.dbf')

If da=0

Use REZ

Browse

ENDIF

use

return

Implementarea Local SQL din Visual dBase combina într-o forma simpla un dialect de SQL cu limbajul xBase.

În acest fel se pot mixa comenzi SQL cu comenzi xBase pentru accesul la tabelele locale.

Se recomanda:

Comenzile xBase pentru crearea tabelelor, inserarea, afisarea, stergerea  si actualizarea înregistrarilor prin dialog cu utilizatorul folosind interfata grafica

Comanda SELECT  din SQL pentru interogari complexe, care returneaza rezultatul

într-o tabela ce poate fi afisata folosind comenzi xBase

Comenzile implementate de Local SQL pentru accesul la tabele locale (DBF) sunt urmatoarele:

CREATE TABLE - crearea unei noi tabele DBF

DROP TABLE                  - stergerea unei tabele

ALTER TABLE   - permite modificarea câmpurilor unei tabele

CREATE INDEX - crearea unor indecsi în fisierul multiindex asociat

DROP INDEX                  - stergerea unui index din fisierul multiidex asociat

INSERT INTO                 - inserarea de înregistrari într-o tabela

DELETE FROM - stergerea conditionata a unor înregistrari

UPDATE - modificarea conditionata a datelor dintr-o tabela

SELECT - selectarea datelor dintr-o tabela

Cea mai utila comanda, prin prisma avantajelor lor fata de comenzile xBase este comanda SELECT. Ea va fi prezentata mai pe larg în continuare, pentru restul comenzilor putând fi consultata documentatia Help a sistemului.

Baza de date pentru local SQL poate fi considerata formata din toate fisiere de date (.dbf) numite tabele si de index create anterior folosind comenzile xBase. Aceste tabele se pot prelucra folosind atât comenzi xBase cât si comenzi SQL.

Comanda SELECT din Local SQL permite extragerea datelor din una sau mai multe tabele bazata pe anumite criterii. O comanda SELECT care extrage date din mai mult de o tabela realizeaza operatia de JOIN din algebra relationala.

Rezultatul unei comenzi SELECT este o noua tabela temporara. Comanda SELECT actioneaza ca si comanda USE, prin crearea structurii tabelei temporare si completarea ei cu rezultatul selectiei. Aceasta va fi deschisa în prima zona libera, care va deveni automat zona curenta. Dupa executia ei în aceasta zona vor putea fi folosite toate comenzile xBase obisnuite pentru accesarea înregistrarilor. Tabela temporara va fi stearsa automat când va fi închisa din zona respectiva. Din motive de optimizari nu tot timpul se va crea o tabela temporara. Astfel, daca se va interoga o singura tabela fara a se redenumi câmpurile, se va deschide chiar tabela în cauza iar clauza de selectie va fi simulata prin setari SET FILTER si SET FIELDS.

Sintaxa comenziiSELECT

SELECT [DISTINCT] lista_coloane [AS redenumire_coloane]

FROM <lista_tabele>

[WHERE  conditie_de_selectie]

[GROUP BY  lista_coloane]

[ORDER BY lista_coloane]

[HAVING  conditie_de_grup]

[SAVE TO nume_fisier]

[ALIAS  nume_alias]

Parametrii au urmatoarele semnificatii:

DISTINCT – va elimina rândurile care au valori duplicate în coloanele specificate în lista_col.

În lista_coloane câmpurile vor fi prefixate daca este necesar de numele tabelei de care apartin. Pentru a include în rezultat toate câmpurile se poate folosi notatia ”*”.

AS redenumire_coloane – folosita daca se doreste redenumirea unor coloane în rezultat.

FROM <lista_tabele> - specifica tabelele din care sunt obtinute datele. Pe post de tabele se pot folosi si rezultate ale unor operatii de outer sau inner join conform SQL-92.

WHERE conditie_de_selectie – specifica conditia care dicteaza ce înregistrari vor fi incluse în rezultat. Se poate folosi operatorul IN pentru a testa apartenenta unui câmp la o multime precizata (ex. ”an_studiu IN (1,3,4)”). Sintaxa Local SQL nu permite includerea de subinterogari în clauza WHERE.

GROUP BY lista_coloane – specifica conditii de grupare a înregistrarilor pentru functiile de agregare. Numai câmpurile specificate aici pot sa apara si în lista SELECT.

ORDER BY lista_coloane – specifica ordonarea înregistrarilor în tabela rezultat

HAVING conditie_de_grup – specifica o conditie de grup ce trebuie indeplinita. Trebuie sa aiba valoarea .T. pentru gruparile care se doresc selectate. Poate aparea doar în combinatie cu parametrul GROUP BY.

SAVE TO nume_fisier – specifica un fisier unde se va depune tabela rezultat. În caz contrar se va crea câte o tabela temporara cu numele SQL_1,2, pentru fiecare SELECT care va fi stearsa la închidere.

ALIAS nume_alias – specifica un alias care va fi dat zonei în care se depune rezultatul. În felul acesta rezultatul se poate utiliza usor în corelatie cu alte tabele din baza de date.

Variabilele pot apare într-o comanda Local SQL daca sunt prefixate cu ”:”.

Comanda SELECT creeaza o tabela temporara într-o zona de lucru pe care o  blocheaza. Este indicat ca dupa utilizarea rezultatului sa se foloseasca comanda USE, pentru a închide tabela din zona rezultat.

În local SQL se pot utiliza functiile:

• pentru siruri - UPPER, LOWER, TRIM;
• pentru date calendaristice – EXTRACT YEAR | MONTH | DAY | HOUR | MINUTE | SECOND FROM câmp_data.

Exemple:

Se considera existente tabelele Stud.dbf si Masini.dbf din capitolele anterioare.

Se selecteaza si se afiseaza  studentii cu bursa mai mare de o anumita valoare data de la consola:

INPUT 'Bursa: ' TO Vbursa

SELECT Cods, Nume, adresa, Data_n, Bursa FROM Stud ;

WHERE bursa > :Vbursa alias studB

* LIST

Browse

USE

Return

Afisare alfabetica a studentilor nascuti înainte de  01.01.1981 si dupa

SELECT Cods, Nume, Adresa, Data_n, Bursa FROM stud WHERE data_n < '01/01/1981' ORDER BY nume alias StudA

SELECT Cods, Nume, Adresa, Data_n, Bursa FROM stud WHERE data_n > '01/01/1981' ORDER BY nume alias studB

? ‘Studenti nascuti inainte de 1981’

select studA

LIST

? ‘Studenti nascuti dupa 1981’

Select studB

LIST

SELECT Cods, Nume, Adresa, Data_n, Bursa FROM stud

LIST

Close All

Return

* Afisare studenti si datele despre masini

SELECT P.Nume, P.Adresa, M.Nrm, M.Tip,M.Cap_c ;

FROM stud P, Masini M WHERE P.Nrm=M.Nrm AND M.Cap_c >1600

browse

use

return

Comanda SELECT poate fi folosita si pentru a extrage informatii de grup prin intermediul functiilor agregat: SUM, AVERAGE, MIN, MAX, COUNT.

Se afiseaza numarul de studentibursieri, bursa minima, maxima, medie, suma burselor pentru fiecare facultate (primele 2 caractere din Cods) care are mai mult de 3 studenti bursieri.

SELECT count(bursa) Nr_Stud, min(bursa) Bmin, max(bursa) Bmax,;

avg(bursa) Bmedie, sum(bursa) Bsuma FROM stud WHERE bursa>0;

GROUP BY substr(cods,1,2);

HAVING Count(bursa) >3

Browse

USE

Return

In lista de selectie se accepta numai functiile agregat, iar daca se utilizeaza GROUP BY pot apare si câmpurile dupa care se face gruparea (facultate, sectie,..).

Daca lipseste clauza GROUP BY se calculeaza min, max, pentru tot fisierul.

Clauza HAVING precizeaza conditia pentru ca un grup sa fie selectat. In conditia de group pot apare numai functii agregat.