Baze de dateLimbajul SQL
Obiective
Utilizarea limbajului SQL pentru a interoga o baz de date pentru a extrage informaii utile
Sa ne amintimCe este SQL?Funciile SQL (DLL, DML)Structura lexicala (cuvinte cheie, identificatori, constante si caractere speciale)Operatori (aritmetici, de comparatie, logici si relationali)Instructiuni DLLInstructiuni DML
Instructiunea SELECTSe folosete pentru a extrage date din baza de dateRezultatul este stocat ntr-un tabel-rezultat numit result-setSintaxa simplificat a acesteia este: SELECT [ALL/DISTINCT] coloana1, coloana2, FROM tabela1, tabela 2, [WHERE conditie] [clauze secundare]Clauzele secundare: GROUP BY, HAVING, ORDER BY
Instructiunea SELECTExemplu: S se afiseze toate localitatile din judetul Sibiu.SELECT nume_loc FROM LOCALITATIWHERE simbol_judet=SB
Nume_locSibiuMedias
Instruciunea SELECT DISTINCTntr-o tabel, unele coloane pot conine valori duplicate. Totui, uneori vrem s listm doar valorile diferite (distincte) din tabel.Cuvntul cheie DISTINCT poate fi folosit pentru aceasta.SintaxaSELECT DISTINCT coloana1 FROM tabela1
Exemplu SELECT DISTINCTS se afiseze o singura data simbolurile de judet din tabela LOCALITATI.SELECT DISTINCT simbol_judet FROM LOCALITATI
Simbol_judetBVCJPHSBTM
Clauza WHEREFolosit pentru a filtra nregistrriFolosit pentru a extrage doar nregistrrile care ndeplinesc un anumit criteriuSintaxaSELECT coloana1, coloana2,FROM tabel1WHERE coloana1 operator valoare
Exemplu clauza WHEREExemplu: S se afiseze cod_loc, simbol_judet, nume_loc din judetul Cluj.SELECT cod_loc, simbol_judet, nume_locFROM LOCALITATIWHERE simbol_judet ='CJ';
Cod_locSimbol_judetNume_loc313CJCluj-Napoca314CJHuedin315CJCampia Turzii
Ghilimele - apostroafeSQL-ul din ACCESS folosete ghilimele/apostrof pentru a delimita valorile de tip text/stringValorile numerice nu se delimiteaz cu ghilimeleCorect:SELECT * FROM Localitati WHERE Cod_jud=SBSELECT * FROM Facturi WHERE Pret=19650Greit:SELECT * FROM Localitati WHERE Cod_jud=SBSELECT * FROM Facturi WHERE Pret=19500
Operatorii AND i ORSe folosesc pentru a filtra nregistrrile dup mai multe condiiiExemplu:SELECT nume, adresa, banca_client FROM Date_Persoana WHERE nume='Popescu Andrei' AND banca_client='BCR'
SELECT nume, adresa, banca_client FROM Data_Persoana WHERE nume= 'Popescu Andrei' OR banca_client='BCR'
numeadresabanca_clientPopescu AndreiStr. Toamnei nr. 23BCR
numeadresabanca_clientPopescu AndreiStr. Toamnei nr. 23BCRVasilache MariaStr. Padina Nr. 123BCR
NULLReprezint date lips/necunoscute/inaplicabileImplicit o coloan poate conine valoarea NULLOperatori pentru NULL:IS NULLIS NOT NULLCnd valoarea unei coloane este opional putem aduga o nregistrare sau o putem actualiza fr a specifica o valoare pentru coloana respectiva. n acest caz se va salva valoarea NULL
NULLValoarea NULL este tratat diferit fa derestul valorilorSe folosete ca un marcator pentru datenecunoscute/inaplicabileNULL i 0 (zero) nu sunt echivalente saucomparabile
Operatorii IS NULL si IS NOT NULLExemplu:SELECT nume FROM DATE_PERSOANAWHERE email IS NULL;
SELECT nume FROM DATE_PERSOANAWHERE email IS NOT NULL;
numeemailIonescu AnaIordache Eugen
numeemailPopescu [email protected] [email protected]
Operatorii IN si NOT INSe folosesc pentru a filtra nregistrrile dup mai multe condiiiExemplu:SELECT nr_factura,pret FROM FACTURAWHERE pret IN (28000,30000,36000);
SELECT nr_factura,pret FROM FACTURAWHERE pret NOT IN (28000,30000,36000);
Nr_facturapret230000336000
Nr_facturapret1225
Operatorul LIKEFolosit n clauza WHERE pentru a specifica un ablon de cutare ntr-o coloanSintaxa:SELECT coloana1, coloana2, FROM tabel1WHERE coloana1 LIKE ablon Modaliti de utilizare:- pentru o expresie care ncepe cu o anumit liter, de exemplu litera A: LIKE A*;- pentru o expresie care se termin cu o anumit liter, de exemplu litera A: LIKE *A;- pentru o expresie care include o anumit liter, de exemplu litera A: LIKE *A*;
Operatorul LIKEModaliti de utilizare:pentru o expresie care ncepe cu o anumit liter, de exemplu litera A: LIKE A*;Exemplu:SELECT nume, email FROM DATE_PERSOANAWHERE nume LIKE 'I* pentru o expresie care se termin cu o anumit liter, de exemplu litera A: LIKE *A;SELECT nume, email FROM DATE_PERSOANAWHERE nume LIKE '*n
numeIonescu AnaIordache Eugen
numeIordache Eugen
Operatorul LIKEModaliti de utilizare:pentru o expresie care include o anumit liter, de exemplu litera A: LIKE *A*;
Exemplu:SELECT nume, email FROM DATE_PERSOANAWHERE nume LIKE '*ana*
numeIonescu Ana
Operatorul BETWEENFolosit n clauza WHERE pentru a stabili un interval de valori dup care se va face filtrarea datelorCapetele intervalului pot fi numere, text sau date calendaristiceSintaxa:SELECT coloana1, coloana2FROM tabel1WHERE coloana1 BETWEEN value1 AND value2
Operatorul BETWEEN - exempluExemplu:SELECT nr_factura, pret FROM FACTURAWHERE pret BETWEEN 20000 AND 40000;
nr_facturapret230000336000
Clauza ORDER BY - sortareCuvntul cheie ORDER BY se folosete pentru a sorta rezultatul dup o anumit coloan sau coloaneOrdonarea/sortarea se face n mod implicit cresctorOrdonarea descresctoare DESCSintaxa:SELECT coloana1, coloana2 FROM tabel1 ORDER BY coloana2 ASC | DESC
Clauza ORDER BY - exempluSELECT nume, banca_client FROM Date_Persoana ORDER BY nume DESC;
numebanca_clientVasilache MariaBCRPopescu AndreiBCRIordache EugenINGIonescu AnaBNR
Clauza TOPUtila cnd tabelul are foarte multe nregistrriReturnarea unui numr mare de nregistrri poate afecta performanaNu este suportat de toate SGBD-urileSintaxa SQLSELECT TOP numar | procent coloana1, coloana2, FROM tabel1
Clauza TOP - ExempluSELECT TOP 2 nume, adresa, banca_client FROM Data_Persoana
numeadresabanca_clientPopescu AndreiStr. Toameni nr. 23BCRIonescu AnaStr. Banatului nr. 2BRD
AliasSe poate da un nume alias unei tabele sauunei coloaneUtil cnd o tabel sau un cmp are un numelung sau complexUtil cnd vrem s dm un nume unei coloanecu valoare calculatInterogrile devin mai simplu de scris i decitit
AliasSintaxa pentru tabeleSELECT coloana1, coloana2, FROM tabela1AS alias_name
Sintaxa pentru coloaneSELECT nume_coloana AS nume_alias FROM tabela1
Funcii definite n SQLFuncii agregat: COUNT(), SUM(), MAX(), MIN(), AVG();Funcii scalarenumerice: sin(), cos(), tg(), ctg(), log(), ln(), lg(), pow(), etc.funcii pentru iruri de caractere: CONCAT(), LOWER(), UPPER(), LENGTH(), REPLACE(), SUBSTR();funcii pentru data calendaristic: DAY(), MONTH(),YEAR(), DATE();funcii de conversie:TO_CHAR(), TO_NUMBER(), TO_DATE()
Funcia COUNT()Calculeaz numrul de nregistrri carerespect un anumit criteriuValorile NULL nu vor fi numrateSintaxaSELECT COUNT(nume_coloana) FROMnume_tabelaPentru a afla numrul de nregistrri dintabelSELECT COUNT(*) FROM nume_tabela
Funcia COUNT() - exempluExemplu:SELECT COUNT(id_co) FROM CERERI_OFERTE
SELECT COUNT(id_co) AS nr_co FROM CERERI_OFERTE
expr10003
Nr_co3
Funcia AVG()Calculeaz valoarea medie pe o coloanSintaxaSELECT AVG(nume_coloana) FROM nume_tabelaExemplu:SELECT AVG(pret) AS Media FROM Factura WHERE MONTH(data_factura)=03;
Media18112,5
Funcia SUM()Calculeaza suma valorilor dintr-un atribut.SintaxaSELECT SUM(nume_coloana) FROM nume_tabelaExemplu:SELECT SUM(pret) AS Total FROM Factura WHERE MONTH(data_factura)=03;
Total36225
Funcia MAX()Determin cea mai mare valoare dintr-ocoloanSintaxa:SELECT MAX(nume_coloana) FROM nume_tabelaExemplu:SELECT MAX(pret) AS Pret_maximFROM FACTURA;
Pret_maxim36000
Date calendaristiceForma datei pe care ncercm s o insermtrebuie s se potriveasc cu formatul coloaneide tip dat din tabelAtunci cnd apare i partea de timp/orlucrurile se complicExist o serie de funcii predefinite pentruprocesarea informaiile legate de dat/or
Funcii calendaristice YEAR, DAY, MONTHFunciile YEAR, DAY, MONTH rein dintr-un cmp de tip dat calendaristic anul, ziua, respectiv luna.Exemplu:SELECT id_co, data_inreg FROM CERERI_OFERTEWHERE MONTH(data_inreg)=03;
id_codata_inreg110.03.2015216.03.2015326.03.2015
Clauza GROUP BYDe cele mai multe ori funciile de agregarefolosesc clauza GROUP BYAre rolul de a grupa datele dintr-una sau maimulte coloaneSintaxa:SELECT nume_coloana(e),functie_agregat(nume_coloana)FROM nume_tabelaWHERE nume_coloana operator valoareGROUP BY nume_coloana
Clauza GROUP BY - exempluExemplu:Afisati numarul de localitati din fiecare judet.SELECT simbol_judet, COUNT(cod_loc) AS nr_locFROM LOCALITATIGROUP BY simbol_judetRezultat
simbol_judetnr_locBV3SB2CJ2
Clauza HAVINGE nevoie de aceast clauz pentru ca WHEREnu poate fi folosit cu funciile de agregareSintaxaSELECT nume_coloana(e),functie_agregat(nume_coloana)FROM nume_tabelaWHERE nume_coloana operator valoareGROUP BY nume_coloanaHAVING functie_agregat (nume_coloana)operator valoare
Clauza HAVING - exempluExemplu:Afisati numarul de localitati din Brasov si Sibiu.SELECT simbol_judet, COUNT(cod_loc) AS nr_locFROM LOCALITATIGROUP BY simbol_judetHAVING simbol_judet='SB' OR simbol_judet='BV'Rezultat
simbol_judetnr_locSB2BV3
Operatorul UNIONFolosit pentru a combina dou sau mai multeinstruciuni SELECTFiecare instruciune SELECT trebuie s aibacelai numr de coloaneColoanele corespunztoare trebuie s aib iaceleai tipuriColoanele trebuie s fie i n aceeai ordine
Operatorul UNION - sintaxaDoar valorile distincteSELECT coloana1, coloana2, FROM tabela1UNIONSELECT coloana1, coloana2, FROM tabela2Permite valori duplicateSELECT coloana1, coloana2, FROM tabela1 UNION ALLSELECT coloana1, coloana2, FROM tabela2 Numele coloanelor din result-set vor fi numele coloanelor din primul SELECT
Operatorul UNION - exempluExemplu:SELECT simbol_judet, nume_judet FROM judeteUNIONSELECT simbol_judet, nume_judet FROM judete_bis
Rezultat
simbol_judetnume_judetABAlbaBCBacauBVBrasovSBSibiu
simbol_judetnume_judetABAlbaBCBacauBVBrasov
simbol_judetnume_judetBVBrasovSBSibiu
Operatorul UNION ALL - exempluExemplu:SELECT simbol_judet, nume_judet FROM judeteUNION ALLSELECT simbol_judet, nume_judet FROM judete_bis
Rezultat
simbol_judetnume_judetABAlbaBCBacauBVBrasovBVBrasovSBSibiu
simbol_judetnume_judetABAlbaBCBacauBVBrasov
simbol_judetnume_judetBVBrasovSBSibiu
Produsul cartezianPermite concatenarea inregistrarilor din doua sau mai multe tabeleIn practica produsul cartezian se utilizeaza doar impreuna cu alti operatoriSintaxaSELECT coloana1, coloana2, FROM tabela1, tabela2,
Produsul cartezian - exempluExemplu:SELECT nume_judet, cod_loc, nume_locFROM LOCALITATI, JUDETERezultat
nume_judetcod_locnume_locBacau123RupeaBrasov123RupeaSibiu123RupeaBacau124RasnovBrasov124RasnovSibiu124RasnovBacau125Fagaras
JonctiuneSe foloseste pentru a extrage date ntr-un result-set din dou sau mai multe tabele, pe baza unei relaii ntre anumite coloane din aceste tabeleSe realizeaza pe baza valorilor din atribute comune.
Jonctiune mai multe tipuriJOIN/INNER JOIN - extrage linii cnd este celpuin o potrivire n ambele tabeleLEFT JOIN - extrage toate liniile din tabela dinstnga, chiar dac nu au potriviri n tabela dindreaptaRIGHT JOIN - extrage toate liniile din tabeladin dreapta, chiar dac nu au potriviri ntabela din stnga
INNER JOINExtrage linii cnd este cel puin o potrivire nambele tabeleSintaxa:SELECT nume_coloana(e)FROM tabela1 INNER JOIN tabela2ON tabela1.nume_coloana =tabela2.nume_coloana Dac sunt linii n prima tabel care nu au corespondent n a doua atunci ele nu sunt extrase
INNER JOIN - exempluExemplu:Vrem s extragem persoanele pentru care aufost intocmite facturi.
SELECT DATE_PERSOANE.nume,,FACTURA.nr_facture FROM DATE_PERSOANEINNER JOIN FACTURA ON DATE_PERSOANE.cnp=FACTURA.cnpORDER BY DATE_PERSOANE.nume
INNER JOIN - exempluRezultat
FACTURAnr_facturacod_ofertadata_facturacnppretTVA1125.03.20151234567890123225242201.04.2015212345678901230000243331.03.201520123456789013600024
DATE_PERSOANAcnpnumeadresa1234567890123Popescu AndreiStr. Toameni nr. 231412451391023Iordache EugenStr. M. Viteazul nr. 52012345678901Vasilache MariaStr. Padina Nr. 1232123456789012Ionescu AnaStr. Banatului nr. 2
Query1numenr_facturaIonescu Ana2Popescu Andrei1Vasilache Maria3
LEFT JOINExtrage toate nregistrrile din table din stnga, chiar i atunci cnd nu sunt potriviri n tabela din dreaptaSintaxa:SELECT nume_coloana(e)FROM tabela1 LEFT JOIN tabela2 ON tabela1 tabela1. nume_coloana =tabela2 nume_coloana n unele SGBD-uri se numete LEFT OUTERJOIN
LEFT JOIN - exempluVrem s extragem datele tuturor persoanele indiferent dac facturi sau nu.SELECT DATE_PERSOANA.nume,FACTURA.nr_factura FROM DATE_PERSOANALEFT JOIN FACTURA ON DATE_PERSOANA.cnp=FACTURA.cnpORDER BY DATE_PERSOANA.nume
numenr_facturaIonescu Ana2Iordache EugenPopescu Andrei1Vasilache Maria3
RIGHT JOINExtrage toate nregistrrile din tabela dindreapta, chiar i cele pentru care nu suntpotriviri n tabela din stngaSintaxa:SELECT nume_coloana(e)FROM tabela1 LEFT JOIN tabela2 ON tabela1 tabela1. nume_coloana =tabela2 nume_coloana n unele SGBD-uri se numete RIGHT OUTERJOIN
RIGHT JOIN - exempluVrem s extragem datele tuturor persoanele indiferent dac facturi sau nu.SELECT DATE_PERSOANA.nume,FACTURA.nr_factura FROM DATE_PERSOANARIGHT JOIN FACTURA ON DATE_PERSOANA.cnp=FACTURA.cnpORDER BY DATE_PERSOANA.nume
numenr_facturaIonescu Ana2Popescu Andrei1Vasilache Maria3
Sa ne reamintimSELECT ALL/DISTINCTOperatorii: AND si OR, IS si IS NOT, IN si NOT IN, LIKE, BETWEEN, IS NULL si IS NOT NULLClauzele: WHERE, ORDER BY, TOP, GROUP BY, HAVINGAliasFunctii agregat: AVG, SUM, MAX, COUNTFunctii calendaristice: DAY, MONTH, YEAROperatorul UNIONJonctiune: INNER JOIN, LEFT JOIN, RIGHT JOIN