curs baze de date

19
SGBD Access 2013: SQL Limbajul SQL SQL ( Structured Structured Query Query Language Language) este un limbaj declarativ (neprocedural) prin care utilizatorul descrie informaţiile pe care vrea să le obţină în urma interogării, fără a preciza algoritmii necesari pentru obţinerea rezultatelor dorite . SQL nu este un limbaj de programare .Faptul că este un limbaj standard a condus la recunoaşterea principalelor sale instrucţiuni de către mai multe SGBD-uri (Oracle, Access, Dbase, INFORMIX, DB2, Visual FoxPro.)

description

curs 6 bd

Transcript of curs baze de date

Page 1: curs baze de date

SGBD Access 2013: SQL

Limbajul SQLSQL (StructuredStructured QueryQuery LanguageLanguage) este un limbaj declarativ (neprocedural) prin care utilizatorul descrie informaţiile pe care vrea să le obţină în urma interogării, fără a preciza algoritmii necesari pentru obţinerea rezultatelor

dorite.

SQL nu este un limbaj de programare .Faptul că este un limbaj standard a condus la recunoaşterea principalelor sale instrucţiuni de către mai multe SGBD-uri (Oracle, Access, Dbase, INFORMIX, DB2, Visual FoxPro.)

Page 2: curs baze de date

SGBD Access 2013: SQL

Pe lângă manipularea şi regăsirea datelor, SQL efectuează şi operaţii complexe privind actualizarea şi administrarea bazei de date.În funcţie de rolul lor în manipularea datelor şi tranzacţiilor, instrucţiunile SQL, pot fi grupate în:

• instrucţiuni de definire a datelordefinire a datelor care permit descrierea structurii bazei de date;• instrucţiuni de manipulare a datelormanipulare a datelor în sensul adăugării, modificării şi ştergerii înregistrărilor;• instrucţiuni de selecţie a datelorselecţie a datelor care permit consultarea bazei de date;• instrucţiuni de procesare a tranzacţiilorprocesare a tranzacţiilor care privesc unităţile logice de prelucrare şi constituie în fapt, operaţii multiple de manipulare a datelor;• instrucţiuni de control al cursoruluicontrol al cursorului; • instrucţiuni privind controlul accesului la datecontrolul accesului la date.

Page 3: curs baze de date

SGBD Access 2013: SQL

Cuvintele cheie ale “vocabularului” SQL (fraza SQL) sunt:instrucţiunileinstrucţiunile, clauzeleclauzele, funcţiilefuncţiile şi operatoriioperatorii..

• InstrucţiunileInstrucţiunile: au cel mai important rol, deoarece determină executarea unei acţiuni (SELECT; CREATE; INSERT; DELETE; UPDATE).

• ClauzeleClauzele restricţionează aria valorică a entităţilor ce participă la interogare (WHERE; ORDER BY; GROUP BY; HAVING).

• FuncţiileFuncţiile îmbunătăţesc capacităţile SQL de a manipula datele (Sum; Max; Avg; Count; Iif).

• OperatoriiOperatorii efectuează o comparare a valorilor selecţiei (=; >; >=, >; >= And; Or; Not; Between; Like; In; .

Page 4: curs baze de date

SGBD Access 2013: SQL

Reguli de sintaxă ale unei fraze SQL:

• Orice frază SQL se va termina cu semnul “;”

• Se utilizează punctul (“..”) ca separator între numele tabelei şi numele câmpului, atunci când o interogare are ca surse de date mai multe tabele (SELECT Materiale..Cod_Material);• Se utilizează parantezele drepte (“[][]”) pentru a încadra nume de câmpuri interspaţiate sau purtătoare de caractere neaceptate de SQL (SELECT Materiale.[Denumire Material])

• Se utilizează virgula (“,,”) pentru a delimita elementele (parametri) unei liste (SELECT Cod_Mat, Den_Mat, etc.)• Elementele de tip şir de caractere se vor marca între

ghilimele(“text”), iar valorile de tip dată/timp se vor marca între #.• Caracterele de înlocuire generice sunt “?” sau “*”

Page 5: curs baze de date

SGBD Access 2013: SQL SELECT

Limbajul de manipulare a datelor: Interogări (Interogări (simplesimple) de selecţie) de selecţie

SELECT [TOP n] [ALL/DISTINCT/DISTINCTROW] listă selecţie câmpuriFROM nume tabelă(e)[WHERE criteriu de selecţie][ORDER BY listă câmpuri criterii de ordonare {ASC/DESC}];

SELECT [TOP n] [ALL/DISTINCT/DISTINCTROW] listă selecţie câmpuriFROM nume tabelă(e)[WHERE criteriu de selecţie][ORDER BY listă câmpuri criterii de ordonare {ASC/DESC}];

DomeniulDomeniul determină modalităţile de manipulare a înregistrărilor din BD asupra căreia operează selecţia. Domeniul poate fi:

• ALLALL (implicit) include toate înregistrările care îndeplinesc condiţiile impuse;

• DISTINCTDISTINCT elimină înregistrările care au valori duplicate în câmpurile selectate (se va afişa doar o apariţie a datei multiple)

•DISTINCTROWDISTINCTROW elimină înregistrările duplicate în ansamblul lor (nu numai pe acelea care au câmpuri duplicate)

Page 6: curs baze de date

SGBD Access 2013: SQL SELECT

SELECT [ALL/DISTINCT/DISTINCTROW] listă selecţie câmpuriFROM nume tabelă(e)[WHERE criteriu de selecţie][WHERE criteriu de selecţie][ORDER BY listă câmpuri criterii de ordonare {ASC/DESC}];[ORDER BY listă câmpuri criterii de ordonare {ASC/DESC}];

Clauza FROM nume tabelăFROM nume tabelă precizează tabela sau tabelele (sau interogări deja create) din care fac parte câmpurile ce fac obiectul proiecţiei BD (,)

Listă selecţie câmpuriListă selecţie câmpuri reprezintă proiecţia BD, cuprinzând toate câmpurile care vor apărea în tabela cu rezultatele interogării (,)

Clauza WHEREWHERE precizează criteriul de selecţie sub forma unei expresii. Clauza este opţională şi nu operează cu funcţii totalizatoare

Clauza ORDER BYORDER BY precizează criteriul de ordonare a înregistrărilor selectate. Fiecare câmp precizat în Clauza ORDER BY constituie o cheie de sortare (sensul sortării se precizează prin ASC (implicit) sau DESC)

Page 7: curs baze de date

SGBD Access 2013: SQL SELECT : Exemple

Tabela A

a1 a2 a3 a4 a5 a6

Selectarea câmpurilor a1 şi a2 din A

SELECT DISTINCTROW a1,a2 FROM A;

Selectarea (fără dubluri) a lui a1 şi a5 pentru care a5> 1000

SELECT DISTINCT a1,a5FROM AWHERE a5>1000;

Selectarea (fără dubluri a) lui a5 > 1000 şi a3=şir

SELECT DISTINCT a5FROM AWHERE a5>1000 AND a3=”text”;

Selectarea lui a5 pt care a3 are o rădăcină precizată

SELECT DISTINCT a5FROM AWHERE a3 LIKE ”*text”;

Page 8: curs baze de date

SGBD Access 2013: SQL SELECT : Exemple

Tabela A

a1 a2 a3 a4 a5 a6

Selectarea (fără dubluri a) lui a5 <> (100,1000)

SELECT DISTINCT a5FROM AWHERE a5 NOT BETWEEN 100 AND 1000;

Selectarea lui a5 şi a rezultatului unui produs dintre înregistrările tabelei A

SELECT DISTINCT a1, a2*a5 AS PRODUSFROM A;

Selectarea câmpurilor a2, a4 şi a5 din A pt. care a2 să ia valorile 1500, 13000 şi 14000, cu ordonare crescătoare a lui a1 şi descrescătoare a lui a5

SELECT a2,a4,a5 FROM A WHERE a2 IN (1500, 13000, 14000)ORDER BY a2 ASC, a5 DESC;

Page 9: curs baze de date

SGBD Access 2013: SQL SELECT : ExempleTabela A

a1 a2 a3 a4 a5 a6

SELECT DISTINCTROW Max(a5) AS [a5_Maxim], Min(a5) AS [a5_Minim], Avg(a5) AS [a5_Medie]FROM A;

SELECT COUNT(*) AS [Număr de tupluri]FROM A;

SELECT a1, a5, a6, IIF(a5>=a6;a5-a6;a6-a5) AS [Rezultat evaluare]FROM AWHERE a2 IS NOT NULL;

Selectarea celei mai mari / mai mici / şi medii valori a lui a5 din tabela A

Numărarea înregistrărilor din tabela A

Selectarea rezultatului evaluării unei condiţii, pentru care a2 contine in mod obligatoriu o valoare

SELECT funcţie_agregatfuncţie_agregat1 AS [Alias], ...2,...FROM nume tabelă(e)WHERE criteriu de selecţie

Obs. În lipsa opţiunii GROUP BY, la utilizarea funcţiilor agregat, rezultatul va conţine o singură linie

Count (<>Null);Sum Σ;Min ;Max

Avg;Iif

Page 10: curs baze de date

SGBD Access 2013: SQL SELECT

Interogări (complexe) de selecţie şi Interogări (complexe) de selecţie şi gruparegrupare

SELECT [domeniu] [listă selecţie],[funcţie agregată(nume câmp) AS alias]FROM nume tabelă(e)[WHERE criteriu de selecţie]

[GROUP BY câmp(uri) de grupare][HAVING criteriul de grupare][ORDER BY listă câmpuri criterii de ordonare {ASC/DESC}];

SELECT [domeniu] [listă selecţie],[funcţie agregată(nume câmp) AS alias]FROM nume tabelă(e)[WHERE criteriu de selecţie]

[GROUP BY câmp(uri) de grupare][HAVING criteriul de grupare][ORDER BY listă câmpuri criterii de ordonare {ASC/DESC}];

Funcţiile de grup (agregat) permit construirea unor interogări SQL prin care utilizatorul poate să efectueze diverse diverse calcule pentru calcule pentru grupuri de înregistrărigrupuri de înregistrări care au câmpuri de aceeaşi valoare care au câmpuri de aceeaşi valoare.

Listă selecţieListă selecţie se referă la una sau mai multe funcţii agregatefuncţii agregate care au ca argumente nume de câmpuri ale tabelei(lor) bazei de date.

AS ALIASAS ALIAS asociază un pseudonimpseudonim aferent rezultatuluirezultatului unui calcul simplu sau unei funcţii agregat.

Page 11: curs baze de date

SGBD Access 2013: SQL SELECT

Interogări (complexe) de selecţie şi Interogări (complexe) de selecţie şi gruparegrupare

ClauzaClauza GROUP BY GROUP BY precizează câmpul sau câmpurile pe baza cărora se va efectua gruparea înregistrărilor. ( cu linia de Total în QBE)

ClauzaClauza GROUP BY GROUP BY formează grupuri de tuplurigrupuri de tupluri ale unei relaţii, pe baza valorilor comune luate de un atribut.

Rezultatul unei fraze SELECTSELECT ce conţine clauza GROUP BYGROUP BY se obţine prin regruparea tuturor liniilor din tabelele enumerate în FROMFROM, extrăgându-se câte o singură apariţie pentru fiecare valoare distinctă a coloanei sau a grupului de coloane

HAVINGHAVING se referă la restricţia aplicată câmpului de grupare. Clauza WHERE acţionează înainte de gruparea înregistrărilor, în timp ce HAVING activează restricţia după gruparea acestora.

Page 12: curs baze de date

SGBD Access 2013: SQL SELECT

Interogări (complexe) de selecţie şi Interogări (complexe) de selecţie şi gruparegrupare

Tabela A (Conţinut Factură)

nrfactura

Codp cant pret

100

100

100

305 25 125000

208 10 200000

85 30 100000

101

101

208 8 200000

74 10 300000

103

103

90 5 50000

74 25 300000

SELECT nrfactura, SUM(cant*pret) AS valoareFROM [continut factura]GROUP BY nrfactura;

1.Se ordonează liniile tabelei după nrfactura;2. Se constituie un grup pentru fiecare valoare distinctă aferentă atributului nrfactura;3. Se aplică funcţia agregată SUMSUM asupra grupurilor;4. Se obţine rezultatul, al cărui număr de linii coincide cu valorile distincte ale atributului nrfactura

Page 13: curs baze de date

SGBD Access 2013: SQL SELECT: JOIN

Tabela A

a1 a2 a3 a4 a5 a6

Tabela B

b1 b2 b3 b4 b5 a1

Tabela C

c1 c2 c3 c4 C5 a1

SELECT [domeniu] listă selecţie câmpuri din tabele diferiteFROM nume tabele[WHERE criteriu de compunere [şi de selecţie][ORDER BY listă câmpuri criterii de ordonare {ASC/DESC}];

LMD:LMD: Interogări de asociere (joncţiune / compunere) internă Interogări de asociere (joncţiune / compunere) internă

Compunerile echivalente (EchiCompunerile)Compunerile echivalente (EchiCompunerile) -> utilizează clauza WHERE (pt -> utilizează clauza WHERE (pt selecţia înregistrărilor) asociată cu o egalitate a valorilor în câmpurile de selecţia înregistrărilor) asociată cu o egalitate a valorilor în câmpurile de legătură. legătură.

• A compus cu B şi A compus cu cu C

SELECT A.a1, A.a2, B.b1, C.c1, C.c3FROM A, B, CWHERE A.a1=B.a1 AND A.a1=C.a1ORDER BY C.c3;

SELECT A.a1, B.b2* B.b3 AS TotalFROM A, BWHERE A.a1=B.a1 AND A.a5>=10000;

T1, T2,.....

T1.a1,

T2.a1,

.........

Page 14: curs baze de date

Exemple:

SELECTSELECT FacturiFacturi.[Numar Factura], FacturiFacturi.[Data Facturii], FacturiFacturi.[Cod Fiscal],[Continut FacturaContinut Factura].[Cod ISBN], [Continut Continut FacturaFactura].Cantitate, [Continut FacturaContinut Factura].[Pret f], [Cantitate]*[Pret f] AS ValoareValoareFROMFROM FacturiFacturi,[Continut FacturaContinut Factura]WHEREWHERE FacturiFacturi.[Numar Factura] = [Continut FacturaContinut Factura].[Numar Factura];

Compunerea a 2 tabele (WHERE)Compunerea a 2 tabele (WHERE)

Page 15: curs baze de date

Exemple:

Compunerea a 3 tabele (WHERE)Compunerea a 3 tabele (WHERE)

SELECTSELECT FacturiFacturi.[Numar Factura], FacturiFacturi.[Data Facturii], [Continut FacturaContinut Factura].Cantitate, [Continut FacturaContinut Factura].[Pret f],[Cantitate]*[Pret f] AS ValoareValoare, ClientiClienti.[Cod Fiscal], ClientiClienti.[Denumire Client],

FROMFROM FacturiFacturi, [Continut FacturaContinut Factura],ClientiClientiWHEREWHERE FacturiFacturi.[Numar Factura]==[Continut FacturaContinut Factura].[Numar Factura] ANDAND ClientiClienti.[Cod Fiscal] == FacturiFacturi.[Cod Fiscal];

Page 16: curs baze de date

SGBD Access 2013: SQL SELECT: JOIN

O compunere internă (INNERINNER) sau echivalentă (echicompunere) este aceea în care liniile unui tabel sunt combinate cu liniile altui tabel, rezultând un număr total de linii egal cu produsul numărului de linii din fiecare tabel (cazul 1 din Join Properties).

LEFT OUTER JOINLEFT OUTER JOIN (11n) include toate înregistrările din tabela AA (cardinalitate “11”) şi numai acele înregistrări din tabela BB (cardinalitate “nn”) pentru care valorile atributelor cheie (a1a1) sunt egale (cazul 2 din Join Properties).

RIGHT OUTER JOINRIGHT OUTER JOIN (11nn) include toate înregistrările din tabela “BB” (cardinalitate “nn”) şi numai acele înregistrări din tabela “AA” (cardinalitate “11”) pentru care valorile atributelor cheie (a1a1) sunt egale (cazul 3 din Join Properties).

Tabela A

a1 a2 a3 a4 a5 a6

Tabela B

b1 b2 b3 b4 b5 a1

Page 17: curs baze de date

SGBD Access 2013: SQL SELECT : JOIN

SELECT [domeniu] listă selecţie câmpuri din tabele diferiteFROM nume tabelă_1

{INNER/LEFT OUTER/RIGHT OUTER } JOIN nume tabelă_2ON criteriu asociere[WHERE criteriu de selecţie][ORDER BY listă câmpuri criterii de ordonare {ASC/DESC}];

SELECT A.a5, B.b3, (A.a5*B.b3) AS [Produs]FROM A

INNER JOININNER JOIN B ONON A.a1=B.a1;

Tabela A

a1 a2 a3 a4 a5 a6

Tabela B

b1 b2 b3 b4 b5 a1

Tabela C

c1 c2 c3 c4 c5 b1

Page 18: curs baze de date

Exemple:

SELECTSELECT FacturiFacturi.[Numar Factura], FacturiFacturi.[Data Facturii], FacturiFacturi.[Cod Fiscal], [Continut FacturaContinut Factura].[Cod ISBN], [Continut Continut FacturaFactura].Cantitate, [Continut FacturaContinut Factura].[Pret f], [Cantitate]*[Pret f] AS ValoareFROMFROM FacturiFacturi INNER JOININNER JOIN [Continut FacturaContinut Factura] ONON FacturiFacturi.[Numar Factura] = [Continut FacturaContinut Factura].[Numar Factura];

Compunerea a 2 tabele (INNER)Compunerea a 2 tabele (INNER)

Page 19: curs baze de date

Exemple:

SELECT Facturi.[Numar Factura], [Continut Factura].Cantitate, [Continut Factura].[Pret f], [Cantitate]*[Pret f] AS Valoare, IIf([IIf([Valoare]<5000000,0,[]<5000000,0,[Valoare]*0.15) AS ]*0.15) AS ReducereFROM Facturi INNER JOIN [Continut Factura] ON Facturi.[Numar Factura] = [Continut Factura].[Numar Factura];

DISCOUNT ÎN LINIE DE FACTURĂDISCOUNT ÎN LINIE DE FACTURĂ