curs baze de date
-
Upload
anca-preda -
Category
Documents
-
view
35 -
download
1
description
Transcript of 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.)
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.
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; .
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 “*”
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)
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)
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”;
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;
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
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.
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.
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
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,
.........
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)
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];
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
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
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)
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Ă