Transcript Baze de Date - Universitatea din Craiova
Baze de Date -Limbajul SQL-Intero gări-
Universitatea din Craiova, Facultatea de Automatica, Calculatoare si Electronica
Baze de Date Anca Ion 2011-2012
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 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ȚIE JONCȚIUNE din algebra relațională.
În clauza WHERE a interogării 2, condiția reprezintă condiția de selecție și corespunde operației de selecție din algebra relațională.
DenumireDep='Cercetare' 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ție joncț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 joncțiune Pdep=NrDep realizează legătura dintre proiectele și departamentele de care sunt controlate, iar condiția de 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 WHERE Locatii.Locatie
FROM Departament, Locatii Departament.DenumireDep='Cercetare' Departament.NrDep=Locatii.NrDep
; AND Sau SELECT WHERE L.Locatie
FROM Departament As D, Locatii As L D.DenumireDep='Cercetare' AND D.NrDep=L.NrDep
;
Ambiguitatea numelor, alias, variabile de tuplu
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: •
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 Asterisk-ului
Baze de Date 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 CARTEZIAN PRODUSUL –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 Asterisk-ului
Baze de Date 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 Asterisk-ului
Baze de Date 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 FROM Angajat Salariu • 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
SQL include câteva operații pe mulțimi din algebra relațională: Baze de Date Anca Ion 2011-2012 •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