MySql
curs programare web
Consultarea datelor din bazele de date
Operatorul de joncţiune în SQL Operatorul de joncţiune în SQL realizează legătura(relaţia) între două tabele după un câmp comun. De exemplu, dacă avem tabelele Agenti(cod, denumire, adresa etc) şi NRSP(seria, nrs, cod, anulat) putem face legătura între ele folosind câmpul comun cod. Unde sunt găsite valori comune ale câmpului cod este făcută legătura. Rezultatul il puteţi vedea scriind codul exemplului de mai jos în fereastra SQL. Căror agenţii le-au fost listate facturile şi care sunt seriile şi numerele speciale alocate acestora? SELECT DISTINCT Agenti.cod, Agenţi.nume, Agenţi. Adresa, NRSP.seria, NRSP.nrs; FROM Agenti, NRSP; WHERE Agenţi.cod=NRSP.cod sau SELECT DISTINCT Agenti.cod, nume, Adresa, seria, nrs; FROM Agenti, NRSP; WHERE Agenţi.cod=NRSP.cod Echijoncţiunea poate fi realizată prin clauza INNER JOIN plasată în clauza FROM, fără clauza WHERE. Astfel, ultima frază SELECT poate fi scrisă astfel: SELECT *; FROM Agenti INNER JOIN NRSP ON; Agenţi.cod=NRSP.cod In ce zone ale oraşului se găsesc agentii economici cărora li s-a facturat o sumă mai mare de 1.000.000 lei pentru apa potabilă? SELECT DISTINCT Zona; FROM Agenti, Facturi; WHERE Tip=12 AND Val>1000000 AND ; Agenti.cod=Facturi.cod
1
curs programare web
Funcţiile predefinite: COUNT, SUM, AVG, MAX, MIN Formatul general al unei fraze SELECT ce conţine funcţii predefinite este:
SELECT fcţ_pred1, fcţ_pred2,..fcţ_predn; FROM listă de tabele; WHERE condiţii În lipsa opţiunii GROUP BY, dacă în clauza SELECT este prezentă o funcţie predefinită, tabelul rezultat va conţine o singură linie.
Funcţia COUNT Câţi clienţi are societatea? (nr de clienţi înseamnă nr de agenţii adică nr de înregistrări din tabelul agenti) SELECT COUNT (cod) AS nr_clienti; FROM Agenti Câte formulare de facturi au fost anulate? SELECT COUNT (anulat); FROM NRSP; WHERE anulat="Da" Câte facturi s-au emis în data de 29.01.2009? SELECT COUNT (DISTINCT numar); FROM FACTURI; WHERE Data=”2009-01-29”
2
curs programare web Funcţia SUM Calculează suma valorilor unei coloane. Care este valoarea totală a facturilor emise? SELECT SUM(Val) AS Total_fe; FROM Facturi Care este valoarea apei potabile facturate? SELECT SUM(Val) AS Total_fe; FROM Facturi; WHERE tip=1 Care este valoarea facturii emise pentru AKROM AKAL? SELECT SUM(val) AS Total_ELCO; FROM FACTURI, AGENTI; WHERE FACTURI.cod=AGENTI.cod AND AGENŢI.nume="AKROM AKAL" sau SELECT SUM(val) AS Total_ELCO; FROM FACTURI; WHERE cod IN; (SELECT cod; FROM AGENTI; WHERE nume="AKROM AKAL" )
3
curs programare web Funcţia AVG Calculează media aritmetică a unei coloane într-un tabel oarecare, prin divizarea sumei valorilor coloanei respective la numărul de valori nenule ale acesteia. Care este valoarea medie a prestaţiilor facturate în factura cu numărul 10589? SELECT AVG (Pret*Cant) AS Medie_10589; FROM FACTURI; WHERE Numar=10589 Observaţie: Valoarea medie a tuturor facturilor nu poate fi calculată cu funcţia AVG. In acest caz este necesară utilizarea opţiunii GROUP BY. Care este valoarea medie a serviciilor de canalizare facturate? SELECT AVG (Pret*Cant) AS Medie_canal; FROM FACTURI; WHERE tip=2 Care este valoarea medie facturată pentru serviciile de canal în zonele Burdujeni şi Iţcani? SELECT AVG (Pret*Cant) AS Medie_canal; FROM FACTURI; WHERE tip=2 AND cod IN; (SELECT cod; FROM AGENTI; WHERE Zona IN ("BURDUJENI","Iţcani")
4
curs programare web Funcţiile MAX şi MIN Determină valorile maxime, respectiv minime ale unei coloane în cadrul unui tabel. Care este codul agentului pentru care s-a facturat cea mai mare cantitate de apă? SELECT cod ; FROM Facturi; WHERE val IN; (SELECT MAX (Val); FROM Facturi; WHERE tip=2)
Gruparea tuplurilor. Clauzele GROUP BY şi HAVING SQL permite utilizarea clauzei GROUP BY pentru a forma grupuri de tupluri într-o relaţie pe baza valorilor comune ale unei coloane. Prin asocierea unei clauze HAVING la o clauză GROUP BY este posibilă selectarea anumitor grupe de tupluri care îndeplinesc un criteriu. Formatul general al clauzei GROUP BY este: SELECT col1, col2,..coln; FROM tabel; GROUP BY coloană de grupare Care este totalul valorilor facturate pentru fiecare serviciu prestat? SELECT Tip, Produs, SUM(val) FROM FACTURI GROUP BY Tip În acest caz tabelul rezultat va avea un număr de linii egal cu numărul tipurilor de prestaţii distincte din tabelul Facturi. Pentru toate poziţiile din facturi care se referă la un anumit produs se va calcula suma valorilor deoarece se foloseşte funcţia SUM. Care este numărul facturilor trimise în fiecare zonă a orasului? SELECT Zona, COUNT(numar); FROM AGENTI, NRSP; WHERE AGENTI.cod=NRSP.cod; GROUP BY AGENTI.Zona
5
curs programare web Care este valoarea totală a facturilor emise, pe zile, pentru fiecare zona ? SELECT Zona, Data, SUM (val); FROM Facturi, Agenti; WHERE Facturi.cod=Agenţi.cod ; GROUP BY Zona, Data
Clauza HAVING Clauza HAVING lucrează împreună cu o clauză GROUP BY, fiind practic o clauză WHERE aplicată acesteia. Formatul general este: SELECT col1, col2,..coln; FROM tabel; GROUP BY coloană de regrupare; HAVING caracteristică de grup Pentru facturile emise, interesează valoarea zilnică, dar numai dacă această valoare este mai mare de 5.000.000 lei. SELECT Data, SUM(val); FROM Facturi; GROUP BY Data; HAVING SUM(val)>5000000 Care sunt agenţii pentru care în facturi există mai mult de două poziţii pentru un produs furnizat? SELECT nume; FROM Agenti; WHERE cod IN; (SELECT cod; FROM FACTURI; GROUP BY tip, cod; HAVING COUNT(*)>2)
6
curs programare web TEMĂ Tabelul prieteni tine evidenţa amicilor pe care îi aveţi. Dacă doriţi să înregistraţi alte informaţii despre ei, cum ar fi evenimente din viaţa lor, precum vizitele sau data căsătoriei, naşterii, sau data naşterii copiilor lor, folosiţi un alt tabel. Cum ar trebui sa arate acest tabel? El va trebui să: • • • •
Conţină numele prietenului căruia îi corespunde fiecare eveniment. O dată, pentru a şti când s-a petrecut evenimentul. Un câmp pentru a descrie evenimentul. Un câmp care să desemneze tipul evenimentului, dacă doriţi să puteţi clasifica evenimentele.
1. 2. 3. 4. 5. 6. 7.
Să se afişeze prietenii care sunt căsătoriţi Să se afişeze cei necăsătoriţi şi nr. lor Să se determine pentru fiecare dintre prieteni câţi copii are şi data naşterii lor Să se afişeze prietenii sub formă de perechi băiat-fată Să se afişeze vârsta fiecărui prieten folosind data naşterii lui Să se afişeze prietenii al căror nume începe cu “C” Să se numere prietenii născuţi în 1992
7