SGBD ACCESS si limbajul SQL

SGBD ACCESS si limbajul SQL

SGBD-ACCESS:

− Face parte din pachetul de programe Microsoft Office;

− Lucreaza sub sistemul de operare Windows;

− Are o interfata atractiva, prietenoasa si usor de folosit;

− Contine o colectie de obiecte:

Table - obiect pentru stocarea datelor definite de utilizator;

Form - obiect pentru introducerea, vizualizarea si controlul

datelor;

Query - obiect pentru chestionarea, extragerea si vizualizarea

informatiilor obtinute din tabele sau interogari utilizand

limbajul SQL sau grila QBE;

Report - obiect pentru imprimarea documentelor ce contin

informatii obtinute din tabele si interogari;

Module - obiect ce contine proceduri scrise in cod Visual

Basic, pentru aplicatiile mai complexe;

Macro - obiect pentru automatizarea anumitor actiuni dintr-o

aplicatie.

− Obiectele SGBD Access sunt stocate in fisiere cu extensia.mdb;

− Permite accesarea BD concurent de catre mai multi utilizatori, pe

diferite nivele de acces;

− Permite publicarea datelor in pagini HTML dinamice pe WEB;

− Este compatibil cu tehnologia ActiveX (accepta realizarea de aplicatii client/server);

− Cu ajutorul lui se pot crea aplicatii care sa acceseze baze de date stocate in SQL Server.

Sistemul de gestiune a bazelor de date ACCESS accepta utilizarea limbajului SQL.

QBE (Query by Example-interogare prin exemplu) permite proiectarea unor interogari complexe. Informatia din grila QBE este automat transformata folosind SQL in instructiuni.

Sintaxa instructiunilor SQL:

− Comenzile SQL se incheie cu; (punct si virgula);

− Daca intr-o interogare SQL se folosesc campuri din mai multe tabele, numele tabelului se separa de numele campului prin, (virgula);

− Daca numele campurilor contin spatii sau simboluri neacceptate de SQL atunci acestea se incadreaza in parantezele drepte;

− Parametrii din liste se separa prin virgula;

− Valorile de tip sir se marcheaza prin apostrof sau ghilimele;

− Inegalitatile din cadrul clauzelor se incadreaza in "<>";

− Caracterele de inlocuire se specifica prin simbolurile ? si *;

− Caracterul # se foloseste pentru a evidentia valorile de tip data/timp.

Deschiderea ferestrei SQL

1. Se lanseaza programul ACCESS;

2. Se selecteaza crearea unei noi baze de date;

3. Din panoul din stanga al ferestrei ACCESS, din lista OBJECTS se selecteaza QUERIES (Interogari);

4. In fereastra nou deschisa se selecteaza optiunea CREATE QUERY IN DESIGN VIEW;

5. Se inchide caseta SHOW TABLE;

6. Din meniul View se selecteaza modul de vizualizare SQL View sau de pe bara de instrumente se selecteaza butonul vizualizare SQL. Se deschide fereastra SQL.

Crearea unei interogari

1. Se repeta pasii 1-4 de la deschiderea unei ferestre SQL;

2. Din caseta SHOW TABLE se aleg cu butonul Add: tabelele (Tables); interogarile (Queries) sau ambele (Both) care vor fi folosite in interogarea SQL;

3. Din meniul View se selecteaza modul de vizualizare SQL View sau de pe bara de instrumente se selecteaza butonul vizualizare SQL;

4. In fereastra deschisa se tasteaza instructiunile SQL.

Executia unei interogari:

− Cu butonul din bara Query Design;

− Cu comanda Run, meniul Query.

Observatie. Daca cererea de interogare SQL ACCESS are erori de sintaxa sau rezultatul nu este cel asteptat se revine in modul de afisare SQL View si se fac corecturile necesare.

Pentru a-si asigura exclusivitatea, majoritatea producatorilor de sisteme de gestiune a bazelor de date isi creeaza propriile extensii ale limbajului SQL. SQL ACCESS nu face exceptie, astfel, desi respecta indeaproape standardul ANSI si el prezinta modificari, cum ar fi:

− Limbajul DCL nu este suportat de ACCESS. Controlul si permisiunile de securitate acceptate de ACCESS nu au fost implementate prin DCL;

− Access are suport limitat pentru limbajul DDL, la crearea tabelelor folosindu-se in general instrumentele grafice.

a. Clauze pentru definirea datelor

Crearea unei tabele cu SQL in Access:

Clauza CREATE TABLE

sintaxa: CREATE TABLE nume_tabela (camp1 tip_data [NOTNULL], camp2 tip_data [NOT NULL],);

Observatii asupra numelui tabelei:

− Este unic.

− Nu poate fi ales dintre cuvintele rezervate.

− Poate avea restrictii asupra numarului de caractere, natura caracterului de inceput, folosirea literelor mici sau mari etc.

Observatii asupra numelor campurilor:

− Poate fi duplicat in cadrul bazei de date;

− Este unic in tabela;

− Nu poate fi cuvant rezervat.

Exemple

1. Sa se creeze doua tabele de date utilizand SQL, astfel:

TABELA1 cu numele CARTI avand campurile:

ID_CARTE(numar), AUTOR_1, TITLU, EDITURA,AN_APARITIE(numar), AUTOR_2

TABELA2 cu numele STUDENT, avand campurile:

ID_STUDENT(numar), NUME, AN_STUDIU(numar), ID_CARTE(numar)

2. Sa se creeze tabela 3 numita CARTE3 cu campurile:

COD_CARTE (numar), NUME_CARTE(text), NUMAR_PAGINI(numar, nu poate ramane necompletat)

Rezolvare

1. Create table CARTI (ID_CARTE number, AUTOR_1 text, TITLU text, EDITURA text, AN_APARITIE number, AUTOR_2 text);

Create table STUDENT (ID_STUDENT number, NUME text, AN_STUDIU number, ID_CARTE number);

2. Create table CARTE3 (COD_CARTE number, NUME_CARTE text, NUMAR_PAGINI number not null);

Modificarea structurii unui tabel cu SQL in Access:

Clauza ALTER TABLE

sintaxa: ALTER TABLE nume_tabela ADD nume_camp tip_data; (se adauga in structura tabelei un camp nou specificat dupa ADD.)

Observatie: Cu comanda ALTER se pot face adaugari de campuri, modificari de dimensiuni ale campurilor, etc.

Exemple

1. Sa se completeze tabela 1 cu campul: NR_EXEMPLARE(numar) si sa se specifice pentru campul NR_EXEMPLARE ca nu poate ramane necompletat.

2. Sa se completeze tabela 2 cu campurile: ORAS NATAL(text), AN NASTERE(numar).

Rezolvare

1. ALTER TABLE CARTI ADD NR_EXEMPLARE number not null;

2. ALTER TABLE STUDENT ADD ORAS_NATAL text, AN_NASTERE numar.

Stergerea unei tabele:

Clauza DROP TABLE

sintaxa: DROP TABLE nume_tabela.

Exemplu

Sa se stearga tabela3.

Rezolvare

DROP TABLE CARTE3

Datele de lucru pentru cele doua tabele vor fi cele de mai jos, iar chei primare vor fi ID_CARTE, ID_STUDENT.

b. Clauze pentru interogarea datelor

Clauza SELECT pentru selectarea datelor

Scopul unei interogari este sa selecteze (extraga) informatii din baza de date. Aceasta se realizeaza cu:

Clauza SELECT

sintaxa: SELECT lista_selectie

FROM nume_tabela1, nume_tabela2,

[WHERE criteriul_de_selectie]

[ORDER BY campuri_criteriu [ASC|DESC]]

[GROUP BY camp_de_grupare

[HAVING criteriul_de_ grupare]]

Lista_selectie = lista campurilor (coloanelor) in ordinea in care vor aparea in rezultatele interogarii.

Exemplu

Sa se selecteze din tabela 2 campurile TITLU, NR_EXEMPLARE, EDITURA

Rezolvare

SELECT TITLU, NR_EXEMPLARE, EDITURA

FROM CARTI.

Daca se lucreaza cu toate campurile, se inlocuieste lista de selectie cu simbolul *.

Exemplu

Sa se creeze o interogare in SQL care sa contina toata tabela 2.

Rezolvare

SELECT *

FROM CARTI;

Observatii

1. Campurile selectate pot fi redenumite in rezultatul interogarii folosind clauza AS:

SELECT camp1 AS nume1

FROM nume_tabela1;

Exemplu

Sa se selecteze din tabela 2 campurile TITLU, AUTOR_1, astfel incat campul AUTOR_1 sa apara sub numele AUTOR

Rezolvare

SELECT TITLU, AUTOR_1 AS AUTOR

FROM CARTI;

2. Campurile pot fi combinate (concatenate) astfel incat mai multe campuri, sa formeze un singur camp in rezultatul interogarii:

SELECT camp1 + " " + camp2 + " " + camp3 AS campcompus,

FROM nume_tabela1;

Se utilizeaza in special pentru date de tip text. Pentru campurile care nu sunt de tip text, acestea se convertesc intai in text si apoi se aplica concatenarea.

Exemple

1. Sa se afiseze informatiile din campurile ID_CARTE si separat TITLU, AUTOR_1 intr-un singur camp numit DATE_CARTE

2. Sa se concateneze campurile: TITLU, AUTOR_1, AN_APARITIE intr-un camp numit DATE_CARTE

Rezolvare

1. SELECT TITLU, AUTOR_1 AS AUTOR

FROM CARTI;

SELECT ID_CARTE, TITLU+" , " +AUTOR_1 AS DATE_CARTE

FROM CARTI;

2. PAS 1. SELECT STR(AN_APARITIE) AS AN_APARITIE2, *

FROM QUERY1;

PAS 2. SELECT TITLU+" "+ AUTOR_1 + " "+AN_APARITIE2

AS DATE_CARTE

FROM QUERY5;

Clauza FROM:

− Specifica numele tabelei, tabelelor sau interogarilor deja create care vor forma noua interogare.

− Daca lista_de selectie cuprinde campuri din mai multe tabele, in fata numelui campului se trece numele tabelei din care provin.

Numele de tabele se separa prin ",".

Clauza WHERE (optionala)

− Utilizata la afisarea inregistrarilor care indeplinesc un anumit criteriu de selectie, in uniunea tabelelor;

− Nu opereaza cu functii totalizatoare;

− Poate fi utilizata impreuna cu operatorii: AND, OR, NOT, IN, BETWEEN, LIKE. Acestia permit combinarea mai multor criterii;

− Operatorul de comparatie LIKE se utilizeaza impreuna cu caracterul * (wildcard).

SELECT camp1 + " " + camp2 + " " + camp3 AS campcompus,

FROM nume_tabela1

WHERE camp3 = "f" And camp4 Like "M*"

Presupunand ca in camp3 avem sexul respondentilor unui chestionar, codificat f - feminin si m - masculin si in camp4 avem numele respondentilor, clauza WHERE specifica selectia tuturor respondentilor de sex feminin care au prima litera a numelui M.

Exemple

1. Sa se selecteze din QUERY1 toate informatiile despre cartile care sunt in biblioteca in 5 exemplare.

2. Sa se selecteze din QUERY1 toate informatiile despre cartile care incep cu litera "G".

3. Sa se selecteze din QUERY1 toate informatiile despre cartile care incep cu litera "S" si sunt in biblioteca in 5 exemplare.

Rezolvare

1. SELECT *

FROM QUERY1

WHERE NR_EXEMPLARE=5;

2. SELECT *

FROM QUERY1

WHERE TITLU LIKE "G*";

3. SELECT *

FROM QUERY1

WHERE NR_EXEMPLARE=5 AND TITLU LIKE "S*";

Clauza ORDER BY (optionala)

− Utilizata la sortarea rezultatelor interogarii in mod crescator (ASC) sau descrescator (DESC), sau combinat.

− Se poate realiza dupa unul sau mai multe campuri_criteriu (definite drept chei de sortare). In general, ordinea campurilor in lista de sortare va da si ordinea efectuarii sortarilor multiple (nu este obligatoriu)

− Componenta BY a clauzei este obligatorie in SQL ACCESS.

− Implicit sortarea se face ascendent.

SELECT camp1 + " " + camp2 + " " + camp3 AS campcompus,

FROM nume_tabela1

WHERE camp3 = "f"

ORDER BY camp4 Desc

Pastrand semnificatiile campurilor din exemplul de la clauza Where, selectia se va face astfel: toti respondentii de sex feminin sortati descrescator.

Exemple

1. Sa se ordoneze toate informatiile din Query1 dupa autor 1.

2. Sa se ordoneze toate informatiile din Query1 dupa autor 1 descrescator.

3. Sa se selecteze toate informatiile despre cartile al caror nume de autor_1 incepe cu litera "A" si sa se ordoneze dupa anul aparitiei.

4. Sa se selecteze toate informatiile despre cartile care incep cu litera "A" si sa se ordoneze descrescator dupa anul aparitiei.

Rezolvare

1. SELECT *

FROM QUERY1

ORDER BY AUTOR_1;

2. SELECT *

FROM QUERY1

ORDER BY AUTOR_1 DESC;

3. SELECT *

FROM QUERY1

WHERE AUTOR_1 LIKE "A*"

ORDER BY AN_APARITIE;

4. SELECT *

FROM QUERY1

WHERE TITLU LIKE "A*"

ORDER BY AN_APARITIE DESC;

Clauza GROUP BY

− Precizeaza campul sau campurile dupa care se face gruparea inregistrarilor, sumarizarea lor. Echivalentul clauzei in grila QBE este randul Total. Este primul pas in agregarea datelor.

− In majoritatea cazurilor GROUP BY se utilizeaza impreuna cu o functie de agregare si cu clauza AS pentru a specifica numele coloanei in rezultatul interogarii.

Functii totalizatoare sau de agregare sunt:

- COUNT = numara inregistrarile din fiecare grup, cu respectarea conditiilor din clauza WHERE;

- SUM = suma tuturor valorilor dintr-un camp numeric;

- AVG = valoarea medie a unui camp numeric, cu respectarea conditiilor din clauza WHERE;

- MAX = valoarea maxima dintr-un camp, cu respectarea conditiilor din clauza WHERE;

- MIN = valoarea minima dintr-un camp, cu respectarea conditiilor din clauza WHERE.

Clauza HAVING

− Este utilizata in cazul in care criteriul de filtrare se aplica rezultatelor obtinute prin agregare si nu se aplica fiecarei surse de inregistrari ca in clauza WHERE.

− Daca este necesara si o conditie la utilizarea clauzei GROUP BY se va utiliza clauza HAVING care opereaza dupa grupare si nu WHERE, care opereaza inainte de a se efectua gruparea inregistrarilor.

Observatie: este admisa utilizarea unei functii agregat care nu apare in lista de selectie si apelarea la mai multe criterii de grupare.

Clauza JOIN (grupeaza si foloseste date din tabele diferite)

Sintaxa:

SELECT [domeniu] lista_selectie

FROM nume_tabela1 JOIN nume_tabela2

ON criteriul_de_asociere

[ JOIN nume_tabela3

ON criteriul_de_asociere]

[WHERE criteriul_de_selectie]

[ORDER BY campuri_criteriu [ASC|DESC]]

Operatiile de asociere induse de clauza JOIN au ca rezultat producerea tuturor combinatiilor posibile, pentru continutul informational al fiecarei tabele. Noile inregistrari care rezulta in urma jonctiunii vor deveni disponibile pentru selectiile ulterioare. La o asociere pot participa mai mult de doua tabele.

Semnificatia elementelor de sintaxa descrise mai sus este urmatoarea:

- INNER, LEFT OUTER, RIGHT OUTER sunt tipurile de jonctiuni (interna, externa de stanga, externa de dreapta). SQL ACCESS accepta scrierea interogarilor externe fara specificarea explicita a lui OUTER;

- JOIN specifica tabela care va fi asociata (nume_tabela2, nume_tabela3) tabelei precizata in clauza FROM;

- ON criteriul de asociere arata relatia dintre campurile pe care se bazeaza jonctiunea. Unul se afla in tabela asociata, iar celalalt exista intr-o alta tabela din lista cu numele tabelelor. Expresia criteriul_de_asociere contine un operator de comparatie (=,<,>,<>,<=,>=) si va returna valorile logice TRUE sau FALSE.

Exemple

1. Selectati, folosind uniunea interna (INNER JOIN), autorii care sunt ceruti de studenti la imprumut si au cartile aparute in FRM.

2. Analog, dar sa se listeze si numele studentilor.

3. Analog, dar sa fie sortate dupa carte.

4. Analog, dar sa fie sortate dupa carte descrescator.

Rezolvare

1. SELECT CARTI.AUTOR_1, CARTI.TITLU

FROM CARTI INNER JOIN STUDENT ON

CARTI.ID_CARTE=STUDENT.ID_CARTE

WHERE EDITURA="FRM";

2. SELECT CARTI.AUTOR_1, STUDENT.NUME, CARTI.TITLU

FROM CARTI INNER JOIN STUDENT ON

CARTI.ID_CARTE=STUDENT.ID_CARTE

WHERE EDITURA="FRM";

3. SELECT CARTI.AUTOR_1, STUDENT.NUME, CARTI.TITLU

FROM CARTI INNER JOIN STUDENT ON

CARTI.ID_CARTE=STUDENT.ID_CARTE

WHERE EDITURA="FRM"

ORDER BY TITLU;

4. SELECT CARTI.AUTOR_1, STUDENT.NUME, CARTI.TITLU

FROM CARTI INNER JOIN STUDENT ON

CARTI.ID_CARTE=STUDENT.ID_CARTE

WHERE EDITURA="FRM"

ORDER BY TITLU DESC.

c. Clauze de modificare a datelor

Clauza INSERT (Adauga inregistrari dintr-o tabela in alta)

Sintaxa:

INSERT INTO nume_tabela (camp1, camp2)

VALUES (valoare1,valoare2)

Observatii:

− valorile din clauza VALUES sunt de aceeasi natura cu campurile din clauza INTO;

− marimea valorii corespunzatoare fiecarui camp va fi mai mica decat dimensiunea campului;

− nu este obligatorie specificarea denumirii campurilor, SQL ACCESS asociaza listei de valori campurile in ordinea din structura inregistrarii (prima valoare se va introduce in primul camp, a doua valoare, in al doilea camp s.a.m.d.).

Clauza DELETE (sterge partial sau total inregistrarile dintr-o tabela)

Sintaxa:

DELETE FROM nume_tabela [WHERE criteriul_de_stergere]

Observatie: se sterg doar inregistrari din tabela nu si tabela.

Clauza UPDATE (insereaza noi inregistrari si modifica valorile campurilor din inregistrarile existente)

Sintaxa:

UPDATE nume_tabela

SET nume_camp1 = valoare1 [,nume_camp2 = valoare2]

[WHERE criteriul_de_actualizare]

Ca si in cazul instructiunii INSERT, se va urmari daca in campul cu valori de actualizat sunt permise numai valori unice. Atunci cand se doreste actualizarea datelor din mai multe campuri se foloseste virgula ca separator intre campuri si valorile acestora.

Se pot utiliza mai multe conditii WHERE apeland la operatorul logic AND pentru a limita actualizarea la inregistrari mai bine specificate.

Subinterogari

O subinterogare se obtine prin scrierea unei interogari in cadrul alteia; rezultatele obtinute in urma unei interogari va reprezenta argumentul pentru alta interogare. Utilizatorul poate sa creeze legaturi intre mai multe interogari SQL ACCESS, pe baza unor campuri unice, cu rol de cautare in structura tabelelor (subinterogarile inlocuiesc interogarile imbricate cu care lucrau versiunile precedente de SQL si au performante mult mai bune). Ele se pot realiza si cu macheta grafica QBE Access.

Sintaxa:

SELECT * FROM Tabela1

WHERE Tabela1.nume_ camp = (SELECT nume_camp FROM Tabela2 WHERE criteriul_de_selectie);

Tabela1 si Tabela2 vor avea un camp comun (nume_camp) care va reprezenta de fapt campul de legatura ce sta la baza construirii subinterogarii. Clauza SELECT din subinterogare va avea acelasi numar de campuri si de natura similara cu cele din clauza WHERE a interogarii.