Baze de Date - Universitatea din Craiova

Report
Baze de Date
Anca Ion
2011-2012
Baze de Date
-Limbajul SQL-InterogăriUniversitatea din Craiova,
Facultatea de Automatica, Calculatoare si Electronica
Introducere
Baze de Date
Anca Ion
2011-2012
• Comanda SQL pentru regăsirea informației în baze de date este
SELECT.
•Nu există nici o legatură între comanda SELECT din SQL și operația de
selecție din algebra relațională. Comanda SELECT din SQL are multe
opțiuni care vor fi prezentate la acest curs.
•Spre deosebire de modelul relațional, în SQL este permis ca un tabel să
aiba unul sau mai multe tupluri duplicat; o tabelă în SQL nu este o
mulțime de valori, deoarece intr-o mulțime nu se permit înregistrări
duplicat.
•Unele relații în SQL sunt constrânse să fie mulțimi datorită cheii
primare sau datorită opțiunii DISTINCT care se folosește cu comanda
SELECT.
•Exemplele din acest capitol folosesc baza de date din anexa 4.2 din curs.
Structura unei interogari în SQL
•
Baze de Date
Anca Ion
2011-2012
Interogările în SQL pot fi foarte complexe. Structura de bază a unei
interogări în SQL este formată din 3 clauze: SELECT, FROM, și WHERE.
SELECT <lista atribute>
FROM <lista tabele>
WHERE <condiție>;
unde
< lista atribute> este lista de nume a atributelor ale căror valori vor fi afișate
de interogare.
< lista tabele >este lista cu numele tabelelor necesare pentru a procesa
interogarea.
< condiție > este condiția booleană care selectează tuplurile care trebuiesc
afișate de interogare.
•
În SQL, operatorii logici pentru compararea valorilor atributelor, fie între
ele, fie cu alte valori constante sunt =, <, <=, >, >=, and <>. SQL are mai
mulți operatori de comparație care vor fi prezentați pe parcursul
capitolului.
Exemple de utilizare a comenzii SELECT
Baze de Date
Anca Ion
2011-2012
• Să se afișeze ziua de naștere și adresa angajaților cu numele Ion
Vasile.
SELECT DataNastere, Adresa
FROM Angajat
WHERE
Prenume= ' ion' AND Nume= ' vasile';
• Această interogare folosește doar relația Angajat care apare în clauza
FROM. Interogarea selectează tuplurile din tabela Angajat care
satisfac clauza WHERE, apoi se face o proiecție după atributele
DataNastere și Adresa care apar în clauza SELECT.
• Interogarea de mai sus este similară cu următoarea expresie în
algebra relațională, exceptând faptul că, dacă există duplicate, acestea
vor fi eliminate în algebra relațională.
∏DataNastere, Adresa (σ(Prenume=' ion' AND Nume=‘vasile') (Angajat))
Exemple de utilizare a comenzii SELECT
Baze de Date
Anca Ion
2011-2012
• Să se afișeze numele și adresa angajaților care lucrează la
departamentul 'Cercetare'.
•
•
•
•
SELECT Nume, Prenume,Adresa
FROM Angajat, Departament
WHERE
NrDep = ADep AND DenumireDep='Cercetare';
Interogarea 2 este similară cu operațiile SELECȚIE-PROIECȚIEJONCȚIUNE din algebra relațională.
În clauza WHERE a interogării 2, condiția DenumireDep='Cercetare'
reprezintă condiția de selecție și corespunde operației de selecție din
algebra relațională.
Condiția NrDep = Adep reprezintă condiția de joncțiune, care
corespunde condiției de joncțiune din algebra relațională.
În SQL, de obicei, se specifică într-o singură interogare mai multe
operații de selecție și mai multe joncțiuni.
Exemple de utilizare a comenzii SELECT
Baze de Date
Anca Ion
2011-2012
• În următorul exemplu se observă o interogare selecție-proiecțiejoncțiune folosind 2 condiții de joncțiune.
• Pentru fiecare proiect cu locația în 'Craiova', să se afișeze numărul
proiectului, numele departamentului care controlează proiectul,
numele, adresa și ziua de naștere a managerului de departament.
SELECT NrProiect,DenumireDep,Nume, Adresa, DataNastere
FROM Proiecte, Departament,Angajat
WHERE Pdep=NrDep AND ManagerDep=CNP AND
Locatie='Craiova';
• Condiția de joncțiune Pdep=NrDep realizează legătura dintre
proiectele și departamentele de care sunt controlate, iar condiția de
joncțiune ManagerDep = CNP realizează legătura dintre departament
și angajatul care este manager de departament.
Ambiguitatea numelor, alias, variabile de tuplu
Baze de Date
Anca Ion
2011-2012
•În SQL se pot folosi aceleași nume pentru 2 sau mai multe atribute atâta
timp cât ele sunt în relații diferite.
•Dacă interogarea folosește atribute cu același nume, trebuie prefixat numele
atributelor cu numele relațiilor pentru a preveni ambiguitatea.
•De exemplu, în relațiile Departament și Locatii atributul NrDep apare în
ambele relații cu același nume.
•Sa se afiseze locatiile departamentului Cercetare
SELECT Locatii.Locatie
FROM Departament, Locatii
WHERE
Departament.DenumireDep='Cercetare' AND
Departament.NrDep=Locatii.NrDep;
Sau
SELECT L.Locatie
FROM Departament As D, Locatii As L
WHERE
D.DenumireDep='Cercetare' AND D.NrDep=L.NrDep;
Baze de Date
Anca Ion
2011-2012
•Ambiguitatea poate să apară și în cazurile în care o interogare folosește aceeași
relație de 2 ori, ca în exemplu:
Ambiguitatea numelor, alias, variabile de tuplu
•Pentru fiecare angajat, să se afișeze numele și prenumele său, și numele și
prenumele supervizorului său direct.
SELECT A.Nume, A.Prenume, S. Nume, S.Prenume
FROM Angajat AS A, Angajat AS S
WHERE
A.SCNP=S.CNP;
•În acest caz, este permisă declararea de denumiri alternative A și S – numite
alias-uri sau variabile tuplu- pentru aceeași relație Angajat. Aliasul poate să
apară după cuvântul cheie AS, ca în exemplu precedent, sau poate să apară
direct în clauza FROM după numele relației Angajat A, sau Angajat S.
•A și S sunt copii diferite ale relației Angajat; A reprezintă angajații care sunt
supervizați, iar S reprezintă angajații care sunt supervizori. Între cele 2 copii se
realizează o joncțiune A.SCNP=S.CNP.
•Aceasta este un exemplu de interogare recursivă cu un singur nivel.
Exemple de utilizare a comenzii SELECT
Baze de Date
Anca Ion
2011-2012
•Să se afișeze numele, prenumele, adresa angajaților care lucrează la
departamentul Cercetare.
SELECT A.Nume, A.Prenume, A. Adresa
FROM Angajat A, Departament D
WHERE
D.DenumireDep='Cercetare' AND D.NrDep=A.ADep;
Nespecificarea clazei WHERE și folosirea
Baze de Date
Asterisk-ului Anca Ion
2011-2012
•Când clauza WHERE lipsește, nu există nici o condiție de selecție a tuplurilor. Atunci
toate tuplurile relației specificate in clauza FROM sunt selectate de interogare.
•Dacă una sau mai multe relații sunt specificate în clauza FROM, atunci PRODUSUL
CARTEZIAN –toate combinațiile posibile de tupluri- al relațiilor este realizat.
•Interogarea 7 selectează toate CNP-urile din Angajat, iar interogarea 8 selectează
toate combinațiile posibile ale CNP-urilor din Angajat cu DenumireDep din
Departament.
Interogare7
SELECT CNP
FROM Angajat
Interogare8
SELECT CNP, DenumireDep
FROM Angajat, Departament
Interogarea 8 este similară unor operații de PRODUS CARTEZIAN și PROIECȚIE din
algebra relațională.
Nespecificarea clazei WHERE și folosirea
Baze de Date
Asterisk-ului Anca Ion
2011-2012
•Pentru a afișa toate valorilor atributelor tuplurilor selectate, în SQL nu
trebuie date explicit numele tuturor atributelor, ci se poate folosi asterisk
(*)-care înseamnă toate atributele.
•De exemplu, interogarea 9 afișează toate valorile atibutelor din tabela
Angajat care lucrează în departamentul cu numărul 1.
Interogarea 9
SELECT *
FROM Angajat
WHERE ADep=1;
•Interogarea 10 afișează toate atributele din tabela Angajat și toate
atributele din tabela Departament cu condiția ca angajatul sa lucreze
pentru departamentul cu numele ‘Cercetare'.
Interogarea 10
SELECT *
FROM Angajat, Departament
WHERE DenumireDep='Cercetare' AND NrDep=ADep;
Nespecificarea clazei WHERE și folosirea
Baze de Date
Asterisk-ului Anca Ion
2011-2012
•Interogarea 11 realizează produsul cartezian al înregistrărilor din tabela
Angajat și Departament.
Interogarea 11
SELECT *
FROM Angajat, Departament;
Tabele ca mulțimi în SQL
Baze de Date
Anca Ion
2011-2012
•Așa cum am menționat mai devreme, SQL nu tratează tabelele ca
mulțimi cu înregistrări unice, astfel tuplurile duplicat pot apărea în
tabelă și în rezultatul interogării.
•SQL nu elimină automat tuplurile duplicat din rezultatul interogărilor,
din următoarele motive:
•Eliminarea duplicatelor este o operație consumatoare de timp; o
modalitate de a implementa această operație este de a sorta tuplurile și
apoi de a elimina duplicatele.
•Utilizatorul poate dori să vizualizeze tuplurile duplicat în rezultatul
interogărilor.
•Când o funcție de agregare este aplicată tuplurilor, în cele mai multe
cazuri nu dorim eliminarea duplicatelor.
Tabele ca mulțimi în SQL
Baze de Date
Anca Ion
2011-2012
•O tabelă în SQL cu o cheie este restricționată să fie mulțime, deoarece
valoarea cheii este unică pentru fiecare tuplu.
•Dacă se dorește eliminarea tuplurilor duplicat din rezultatul unei
interogări se folosește opțiunea DISTINCT în clauza SELECT, însemnând
că numai tuplurile distincte trebuie să rămână ca și rezultat.
SELECT DISTINCT Salariu
FROM Angajat
•Spre deosebire de interogarea precedentă, următoarea interogare
afișează salariul fiecărui angajat, chiar dacă mai mulți angajați au
același salariu .
SELECT ALL Salariu
FROM Angajat
Tabele ca mulțimi în SQL
Baze de Date
Anca Ion
2011-2012
SQL include câteva operații pe mulțimi din algebra relațională:
•reuniune pe mulțimi (UNION)
•diferența pe mulțimi (EXCEPT)
•intersecție pe mulțimi (INTERSECT)
! Relațiile rezultate în urma acestor operații sunt mulțimi de tupluri,
tuplurile duplicat fiind eliminate.
! Operațiile pe mulțimi pot fi aplicate doar pe relațiile compatibile cu
reuniunea (care au același număr de atribute și domeniile atributelor
corespunzătoare sunt aceleași).
Tabele ca mulțimi în SQL-Exemple
Baze de Date
Anca Ion
2011-2012
•Să se afișeze lista cu proiectele (NrProiect) la care lucrează un angajat
cu numele ‘Popescu', ca angajat sau manager la departamentul care
controlează proiectul.
(SELECT DISTINCT NrProiect
FROM PROIECTE, DEPARTAMENT, ANGAJAT
WHERE PDep=NrDep AND ManagerDep=CNP AND Nume='popescu')
UNION
(SELECT DISTINCT PROIECTE.NrProiect
FROM PROIECTE, ANGAJATIPROIECTE, ANGAJAT
WHERE PROIECTE.NrProiect=ANGAJATIPROIECTE.NrProiect AND
PCNP=CNP AND Nume ='popescu');
•SQL oferă și operații care nu elimină duplicatele din relația rezultat:
UNION ALL, EXCEPT ALL, INTERSECT ALL.
Operații pe șiruri de caractere în SQL. Operații aritmetice în SQL
Baze de Date
Anca Ion
2011-2012
 Pentru operațiile de comparare a șirurilor de caractere, în SQL se
folosește operatorul de comparație LIKE si NOT Like.
 Subșirurile pot fi specificate folosind 2 caractere rezervate: %înlocuiește un anumit număr de caractere și _(underscore) înlocuiește
un singur caracter.
 Exemple:
 Să se afișeze angajații care au adresa în Craiova.
SELECT Nume, Prenume
FROM Angajat
WHERE
Adresa LIKE '%Craiova%';
Operații pe șiruri de caractere în SQL. Operații aritmetice în SQL
Baze de Date
Anca Ion
2011-2012
 Exemple:
 Să se afișeze angajații care a caror adresa se termina in %.
SELECT Nume, Prenume
FROM Angajat
WHERE
Adresa LIKE '%!%' Escape '!';
 Dacă caracterele % și _ sunt folosite într-un șir de caractere ca și
literali, ele trebuie precedate de caracterul ESCAPE ‘!’, care este
specificat la sfârșitul șirului de caractere.
 Exemplu: 'AB!_CD!%EF' ESCAPE '!' reprezintă șirul de caractere
'AB_CD%EF'.
Operații pe șiruri de caractere în SQL. Operații aritmetice în SQL
Baze de Date
Anca Ion
2011-2012
•De asemenea, este nevoie de o regulă care să specifice apostroful sau
ghilimele simple daca acestea sunt incluse într-un șir de caractere,
deoarece acestea sunt folosite și pentru a indica începutul și sfârșitul
unui șir de caractere.
•Dacă este nevoie de un apostrof ('), acesta trebuie precedat de inca un
apostroaf (‘) ca să nu fie interpretat ca și sfârșit de șir de caractere.
•Exemple:
SELECT Nume, Prenume, Adresa
FROM Angajat
WHERE
Nume LIKE 'io''na';
Operații pe șiruri de caractere în SQL. Operații aritmetice în SQL
Baze de Date
Anca Ion
2011-2012
•O altă caracteristică permisă în SQL, este folosirea operațiilor aritmetice
în interogări. Operațiile aritmetice standard de adunare (+), scădere(-),
înmulțire(*), împărțire(/) pot fi aplicate pe valori numerice sau pe
atributele al căror domenii sunt numerice.
• Exemple:
•De exemplu, să presupunem că se dorește mărirea salariului angajaților
care lucrează la proiectul 'ProductX' cu 10%.
SELECT Nume,Prenume, 1.1*Salariu AS SalariuMarit
FROM Angajat, AngajatiProiecte, Proiecte
WHERE
CNP=PCNP
AND
Proiecte.NrProiect=AngajatiProiecte.
NrProiect AND
DenProiect='ProdusX';
Operații pe șiruri de caractere în SQL. Operații aritmetice în SQL
Baze de Date
Anca Ion
2011-2012
•Un alt operator de comparare este BETWEEN.
•Să se afișeze toți angajații din departamentul 5 cu salariul între
3000000 și 5500000.
SELECT *
FROM Angajat
WHERE (Salariu BETWEEN 3000000 AND 5500000) AND ADep =1;
•Condiția (Salariu BETWEEN 3000000 AND 5500000 ) este echivalentă cu
((Salariu >= 3000000) AND Salariu <= 5500000)).
Ordonarea rezultatelor interogărilor
Baze de Date
Anca Ion
2011-2012
•SQL permite ordonarea tuplurilor din interogări după valorile unuia
sau mai multor atribute, folosind clauza ORDER BY.
•Exemple:
•Să se afișeze lista angajaților și a proiectelor la care ei lucrează, ordonate
după denumirea departamentului la care lucrează, și în cadrul fiecărui
departament să se ordoneze alfabetic după nume și prenume.
SELECT DenumireDep, Nume, Prenume, DenProiect
FROM Departament, Angajat, Proiecte, AngajatiProiecte
WHERE Adep=NrDep and CNP=PCNP and Proiecte.NrProiect =
AngajatiProiecte. NrProiect
ORDER BY DenumireDep, Nume, Prenume
Ordonarea rezultatelor interogărilor
Baze de Date
Anca Ion
2011-2012
•Ordonarea implicită este cea ascendentă a valorilor. Se pot specifica
tipurile de ordonare ascendentă și descendentă folosind cuvintele cheie
ASC, rescpectiv DESC.
•De exemplu, putem ordona descendent după numele departamentului,
și ascendent după nume și prenume:
ORDER BY DenumireDep DESC, Nume ASC, Prenume ASC

similar documents