Curs 4-BD SQL PDF

28.10.2021 Instrucțiunea SELECT (C4) SELECT - instrucțiune de interogare, prin care se regăsesc informațiile din unul sau mai multe tabele ale bazei de date după un criteriu (conditie) dat Sintaxa generală: SELECT [DISTINCT] lista_coloane...

28.10.2021 Instrucțiunea SELECT (C4) SELECT - instrucțiune de interogare, prin care se regăsesc informațiile din unul sau mai multe tabele ale bazei de date după un criteriu (conditie) dat Sintaxa generală: SELECT [DISTINCT] lista_coloane [FROM lista_tabele] [WHERE conditie] [clauze_secundare]; SELECT returneaza un tabel cu coloanele din “lista_coloane” ale acelor linii (tupluri) ale produsului cartezian al tabelelor din “lista_tabele” pentru care expresia logică “conditie” este adevărată (are valoarea TRUE). Instrucțiunea SELECT are următoarele secţiuni (clauze): Clauza SELECT defineşte lista de coloane a tabelului rezultat Clauza FROM indică lista de tabele din care se selectează rezultatul Clauza WHERE definește condiția pe care trebuie să o îndeplinească fiecare linie a tabelului rezultat Clauze secundare (ORDER BY, GROUP BY, HAVING): permit ordonări sau grupări ale tuplurilor (liniilor) rezultate Cap. 2 - Baze de date relationale 21 21 Clauza SELECT Clauza SELECT specifică: lista coloanelor unor tabele (date în “lista_tabele”) expresii care vor fi calculate şi afişate Exemple: SELECT ID, Name, CountryCode, District from city; SELECT 3*4, cos(45), floor(12.45); Eliminarea liniilor duplicat – cu parametrul DISTINCT. Exemplu: SELECT DISTINCT CountryCode FROM city; Selectarea tuturor coloanelor produsului cartezian al tabelelor date - cu caracterul * ca și “lista_coloane”. Exemplu: SELECT * FROM city; În clauza SELECT se pot redenumi tabelele și coloanele tabelelor sau se pot specifica nume pentru expresii, folosind următoarea sintaxă: SELECT nume1 [AS] noul_nume1 [,...n] FROM lista_tabele [alte_clauze]; SELECT ID, Name Oras, CountryCode ‘Cod Tara’ FROM city; Cap. 2 - Baze de date relationale 22 22  28.10.2021 Funcții agregat În clauza SELECT se pot introduce şi funcţii agregat (totalizatoare). Funcţiile agregat definite în limbajul SQL2 sunt următoarele: Functia Valoarea returnata COUNT Numarul de linii al tabelului rezultat SUM Suma valorilor din coloana dată ca argument MAX Valoarea maxima din coloana dată ca argument MIN Valoarea minima din coloana dată ca argument AVG Valoarea medie din coloana dată ca argument Exemple: SELECT COUNT(*) FROM city; -- returneaza numarul de linii din tabel SELECT COUNT(col) FROM city; -- return nr de valori dif de null din acea col. SELECT MAX(Population) FROM city; SELECT MIN(Population) FROM city; SELECT AVG(Population) FROM city; Cap. 2 - Baze de date relationale 23 23 Clauzele FROM și WHERE Clauza FROM specifică “lista_tabele” din care se selectează rezultatul Numele coloanelor din “lista_coloane” (clauza SELECT) trebuie să fie distincte Dacă nu sunt distincte, se califică unele coloane cu numele tabelului căruia îi aparţin - folosind operatorul “punct” (.). De exemplu: SELECT ANGAJATI.Nume, SECTII.Nume FROM ANGAJATI, SECTII; Clauza WHERE specifica “conditia” pe care trebuie sa o îndeplinească rezultatul: condiția este o expresie logică compusa din valori logice, operatori logici (NOT, AND, OR) şi paranteze o valoare logică se obtine ca rezultat al comparaţiei între doi operanzi folosind un operator de comparaţie un operand poate fi un atribut (nume de coloană), o constantă, valoarea unei expresii aritmetice sau o valoare returnată de o funcţie operatorii de comparaţie pot fi operatori aritmetici sau operatori SQL de comparaţie Exemple: SELECT * FROM city WHERE Population > 1000; SELECT * FROM city WHERE (Population > 200000) AND (CountryCode=‘ROM‘); Cap. 2 - Baze de date relationale 24 24  28.10.2021 Clauze secundare Clauza ORDER BY specifică numele atributului după care se face ordonarea liniilor tabelului rezultat SELECT * FROM city order by CountryCode; Ordonarea în ordine crescătoare: parametrul ASC (implicit); în ordine descrescătoare: DESC. Exemplu: SELECT * FROM city order by CountryCode DESC; Clauza GROUP BY se foloseşte pentru gruparea rezultatelor funcţiilor agregat dupa valoarea uneia sau mai multor coloane. Exemplu: SELECT CountryCode, AVG(Population) FROM city GROUP BY CountryCode; Clauza HAVING înlocuiește clauza WHERE atunci când în condiția care trebuie să fie îndeplinită se folosesc funcții agregat. Exemplu: SELECT CountryCode, AVG(Population) FROM city GROUP BY CountryCode HAVING AVG(Population) >800000; Cap. 2 - Baze de date relationale 25 25 Instrucțiunea INSERT Instrucțiunea INSERT se folosește pentru introducerea datelor în tabele şi are următoarea sintaxă: INSERT INTO nume_tabel (col1,col2,...coln) VALUES(val1,val2,...valn); Între valori şi numele de coloane trebuie să existe o corespondență pozițională. De exemplu: INSERT INTO SECTII (Numar, Nume, Buget) VALUES (1,‘Productie’, 40000); Lista de coloane poate să lipsească dacă se introduc valori în toate coloanele tabelului și în această situatie: ordinea valorilor introduse trebuie să respecte ordinea coloanelor tabelului ordinea coloanelor provine din ordinea de definire a atributelor prin instrucţiunea CREATE TABLE, precum şi din operaţiile ulterioare de alterare a tabelului ordinea coloanelor se poate afla prin instrucţiunea DESCRIBE nume_tabel. De exemplu, introducerea unei linii cu toate valorile în tabelul ANGAJATI (IdAngajat,Nume,Prenume,DataNasterii,Adresa,Functia,Salariu) se poate face cu instrucţiunea: INSERT INTO ANGAJATI VALUES(100,‘Mihailescu’, ‘Mihai’,‘1950-04-05’,‘Craiova’,’Inginer’, 3000); Cap. 2 - Baze de date relationale 26 26  28.10.2021 Instrucțiunile UPDATE și DELETE Instrucțiunea UPDATE permite actualizarea valorilor coloanelor (atributelor) din una sau mai multe linii ale unui tabel și are sintaxa: UPDATE nume_tabel SET col1 = expr1 [,... n] [WHERE conditie]; Clauza WHERE: actualizarea valorilor se efectuează numai asupra acelor linii care îndeplinesc condiția dată. Exemplu: UPDATE ANGAJATI SET Adresa = ‘Bucuresti’ WHERE Nume = ‘Popescu’; Dacă este omisă clauza WHERE, vor fi modificate valorile coloanelor din toate liniile tabelului. Instrucţiunea DELETE permite ştergerea uneia sau mai multor linii dintr-un tabel şi are sintaxa: DELETE FROM nume_tabel [WHERE conditie]; Din tabel se şterg acele linii care îndeplinesc condiţia dată în clauza WHERE. Dacă este omisă clauza WHERE, vor fi sterse toate liniile din tabel. Exemplu: DELETE FROM ANGAJATI WHERE Nume =‘Ionescu’; Cap. 2 - Baze de date relationale 27 27 Constrângeri de integritate (1) Constrângerile de integritate (integrity constraints) sunt reguli care: se definesc la proiectarea bazei de date trebuie să fie respectate de orice stare a acesteia astfel încât datele memorate să corespundă cat mai bine celor din realitate Clasificare după locul unde se definesc: constrângeri de coloana și constrângeri de tabel (în instrucțiunea CREATE TABLE) Clasificare după numărul de relații implicate: constrângeri intra-relaţie şi constrângeri inter-relaţii. Constrângerile intra-relaţie - reguli care se impun în cadrul unei singure relații; de trei categorii: Constrângeri de domeniu - condiţii ce se impun valorilor domeniilor atributelor Constrângeri de tuplu - condiţii ce se impun tuplurilor unei relaţii prin chei (primare sau secundare) Constrângeri impuse prin dependenţe de date (dependenţe funcţionale, multivalorice sau de joncţiune); acestea sunt constrângeri intre valorile atributelor dintr-o relaţie Constrângerile inter-relaţii - reguli care se impun între două sau mai multe relaţii; asigura integritarea referenţială prin intermediul cheilor străine Cap. 2 - Baze de date relationale 28 28  28.10.2021 Constrângeri de integritate (2) Clasificare din punct de vedere al modului de definire: constrângeri inerente, implicite şi explicite. Constrângerile inerente sunt cele ale modelului de date însuși, care nu trebuie să fie specificate la definirea relațiilor, dar sunt respectate prin modul în care se construiesc relaţiile De exemplu: în modelul relaţional constrângerea ca valoarea fiecărui atribut să fie atomică (indivizibilă) este o constrângere inerentă Constrângerile implicite sunt reguli care se definesc odată cu definirea schemelor relaţiilor, sunt memorate în baza de date şi sistemul de gestiune verifică şi impune automat respectarea acestora Exemple: connstrângerile de domeniu, constrângerile de tuplu şi constrângerile de integritate referenţială sunt constrângeri implicite. Constrângerile explicite sunt constrângeri suplimentare pe care trebuie să le respecte relaţiile unei baze de date şi care nu sunt impuse automat de sistemul SGBD, ci necesită proceduri speciale de verificare şi impunere Exemple: dependențele de date care nu sunt determinate de cheile relațiilor Cap. 2 - Baze de date relationale 29 29 Constrângeri de domeniu (1) Constrângerile de domeniu: constrângerea NOT NULL, constrângerea de valoare implicită (DEFAULT), constrângerea de verificare (CHECK) Constrângerea NOT NULL însemna că atributul respectiv nu poate lua valoarea NULL în nici un tuplu al relaţiei. Valoarea NULL a unui atribut într-un tuplu semnifică faptul că valoarea acelui atribut nu este cunoscută pentru acel tuplu. Exemple: nu se cunoaște deloc data de naștere a unei personalitati istorice; nu se cunoaște valoarea unui atribut în momentul inserarii tuplului, dar aceasta va fi cunoscuta și completată ulterior La crearea unui tabel opţiunea NULL este implicită (nu se specifică nimic), sau se poate introduce explicit; optiunea NOT NULL se introduce explicit. Optiunile NULL și NOT NULL se introduc ca și constrângeri de coloana în instructiunea SQL CREATE TABLE. Exemplu: CREATE TABLE ANGAJATI ( Nume varchar2(30) NOT NULL, Prenume varchar2(30) NOT NULL, DataNasterii date NULL, Functie varchar2(20), Salariu number); Cap. 2 - Baze de date relationale 30 30  28.10.2021 Constrângeri de domeniu (2) Constrângerea de valoare implicită a unui atribut (DEFAULT): dacă la inserarea unui tuplu nu se specifică valoarea unui atribut, atunci: atributul primește valoarea implicită, dacă a fost definită atributul primește valoarea NULL, dacă nu a fost definită valoare implicită, dar sunt admise valori NULL se generează o eroare, dacă nu a fost definită o valoare implicită şi nici nu sunt admise valori NULL. Exemplu: CREATE TABLE STUDENTI ( Nume varchar2 (20) NOT NULL, Prenume varchar2 (20) NOT NULL, Tara varchar2 (20) NULL DEFAULT ‘Romania’ ) ; Constrângerea de verificare (CHECK) – pentru verificarea valorilor atributelor printr-o condiție care trebuie sa ia valoarea TRUE. Se introduce ca o constrângere de tabel în instrucțiunea CREATE TABLE: [CONSTRAINT nume_constrangere] CHECK (conditie); Exemplu: CREATE TABLE ANGAJATI ( Nume varchar2(20) NOT NULL, Prenume varchar2(20) NOT NULL, Salariu number(5), CONSTRAINT Verificare_Salariu CHECK (Salariu >= 1500 )); MySql anterior versiunii 8.0.16 nu face verificarea CHECK, chiar dacă admite acest cuvânt cheie Cap. 2 - Baze de date relationale 31 31 Constrângeri de tuplu O relație = mulțime de tupluri tuplurile unei relaţii trebuie să fie distincte (nu pot exista două sau mai multe tupluri identice) Pentru ca tuplurile unei relații să fie distincte se folosește câte o cheie primară (primary key) în fiecare relație O cheie primară PK a unei relații este un atribut (simplu sau compus) al acelei relații care are proprietatea de unicitate, adică fiecare valoare a cheii primare este unică în acea relație. Aceasta înseamnă că: Nu există două tupluri distincte (diferite) care să aibă aceeași valoare a cheii primare (sau combinație de valori, dacă cheia primară este un atribut compus) pentru orice stare a relației, adică: ti[PK] ≠ tj[PK] dacă i ≠ j, unde ti și tj sunt 2 tupluri ale relației Fiecare tuplu poate fi identificat dacă se cunoaște valoarea cheii primare Proprietatea de unicitate a cheii primare este o constrângere de integritate a tuplurilor Cheia primară se definește la crearea tabelului corespunzător Se pot defini chei primare naturale sau chei primare artificiale, cu condiția ca acestea să îndeplinească condiția de unicitate Cap. 2 - Baze de date relationale 32 32  28.10.2021 Cheia primară (1) O cheie primară naturală este un atribut (simplu sau compus) al relației: reprezintă o proprietate a tipului de entitate (sau asociere) reprezentat de acea relație are în mod natural valori unice, adică nu există două tupluri cu aceeași valoare a cheii primare O cheie primară artificială este un atribut (de obicei simplu) care nu reprezintă o proprietate a tipului de entitate sau asociere reprezentat de relație, ci se adaugă în schema relaţiei special pentru identificarea unică a tuplurilor Exemplu: ANGAJATI (IdAngajat, CNP, Nume, Prenume, DataNasterii, Adresa, Functia, Salariu): IdAngajat este o cheie primară artificială Ar putea fi definite și chei primare naturale prin atribute simple sau compuse care au proprietatea de unicitate în anumite condiții: atributul simplu {CNP} atributul compus {Nume, Prenume, DataNasterii, Adresa} Din motive de eficiență a operațiilor de identificare a tuplurilor se preferă chei primare cu un număr cât mai mic de atribute (atribut simplu) Cap. 2 - Baze de date relationale 33 33 Cheia primară (2) Cheia primară are următoarele restricții: Nici o valoare a atributelor cheii primare nu poate fi modificată prin operaţii de actualizare (UPDATE) Nu se admit valori de NULL pentru nici unul dintre atributele cheii primare Cheia primară se definește prin instrucțiunea CREATE TABLE Cheia primară se poate defini ca o constrângere de tabel sau ca o constrângere de tuplu Definirea cheii primare ca o constrângere de tabel: [CONSTRAINT nume_constr] PRIMARY KEY (lista_atribute) Exemplu: CREATE TABLE SECTII ( IdSectie number(3), Nume varchar2(50) NOT NULL, number, Buget CONSTRAINT PK PRIMARY KEY (IdSectie) ); Cap. 2 - Baze de date relationale 34 34  28.10.2021 Cheia primară (3) Dacă cheia primară este simplă (formată dintr-un singur atribut), ea se poate specifica și ca o constrângere de coloană; exemplu: CREATE TABLE ANGAJATI ( IdAngajat int PRIMARY KEY AUTO_INCREMENT, Nume varchar(20) NOT NULL, Prenume varchar(20) NOT NULL, DataNasterii Date, Adresa varchar(50), Salariu numeric) ; Modul de asigurare a unicității valorii cheii primare artificiale depinde de sistemul SGBD folosit. De exemplu: În Microsoft SQL Server se pot obține valori unice ale cheii primare folosind parametrul IDENTITY, care asigură incrementarea valorii atributului cheii la introducerea fiecărei linii noi În sistemele Oracle se pot genera chei artificiale folosind obiecte SEQUENCE; un obiect SEQUENCE generează un număr unic la fiecare apel al metodei NEXTVAL În MySQL, se folosește parametrul AUTO_INCREMENT pentru generarea numerelor unice pentru cheile primare. SGBD-urile interzic introducerea liniilor (tuplurilor) care iau valori identice ale cheilor candidate (primare sau secundare) Cap. 2 - Baze de date relationale 35 35 Superchei, chei candidate O supercheie (superkey) este o submulţime SK de atribute ale unei relaţii care prezintă proprietatea de unicitate (orice combinaţie de valori ale atributelor supercheii este unică pentru orice stare a relaţiei) Dacă se cunoaște valoarea (combinaţia de valori ale atributelor) supercheii, atunci acel tuplu poate fi identificat în mod unic Orice relație are cel puțin o supercheie, care este mulțimea tuturor atributelor sale O cheie candidată (candidate key) este o supercheie ireductibilă: Unicitate: nu există două tupluri diferite ale relaţiei care să conţină aceeaşi combinaţie de valori ale atributelor cheii CK; Ireductibilitate: nu există nici o submulţime proprie, nevidă a cheii CK care să aibă proprietatea de unicitate O cheie candidată poate fi simplă (un atribut), sau compusă (mai multe atribute) Exemplu: ANGAJATI (IdAngajat, CNP, Nume, Prenume, DataNasterii, Adresa, Functia, Salariu) SK1 = {IdAngajat, CNP, Nume, Prenume, DataNasterii, Adresa, Functia, Salariu} SK2 = {CNP, Nume, Prenume, DataNasterii, Adresa}; SK3 = {IdAngajat, CNP} CK1= {Nume, Prenume, DataNasterii, Adresa}; CK2 = {CNP}; CK3 ={IdAngajat} Cap. 2 - Baze de date relationale 36 36  28.10.2021 Chei secundare Atunci când există mai multe chei candidate, una dintre ele se alege ca şi cheie primară, celelalte chei candidate fiind numite chei secundare O cheie secundară (alternativă, unică) (secondary, alternate, unique key) este o cheie candidată care nu a fost desemnată ca şi cheie primară Cheile secundare compuse admit valori NULL pentru unele din atributele lor Alegerea cheii primare dintre mai multe chei candidate este arbitrară, dar, din motive de eficiență, se alege cheia cu cel mai mic număr de atribute Cheile secundare se definesc în instrucțiunea CREATE TABLE folosind specificatorul UNIQUE [KEY] în loc de PRIMARY KEY Cap. 2 - Baze de date relationale 37 37 Constrângeri inter-relații Asocierile (relaționships) 1: N între două mulțimi de entităţi (din modelul Entitate-Asociere) se realizează în modelul relaţional prin chei străine Exemplu: Pentru a realiza asocierea 1: N dintre relaţiile SECTII și ANGAJATI, se adaugă în relaţia ANGAJATI cheia străină IdSectie, care reprezintă identificatorul (numărul) secţiei în care lucrează angajatul respectiv: ANGAJATI (IdAngajat, Nume, Prenume, DataNasterii, Adresa, Salariu, IdSectie) SECTII IdSectie Nume Buget 1 N SECTII ANGAJATI 1 Productie 400000 2 Proiectare 300000 Diagrama E-A 3 Cercetare 200000 4 Documentare 100000 ANGAJATI IdAngajat Nume Prenume DataNasterii Adresa Functia Salariul IdSectie 1 Ionescu Ion 1960.01.05 Bucuresti inginer 4000 1 2 Popa Petre 1965.02.97 Bucuresti tehnician 3200 1 3 Carol Ana 1961.03.06 Bucuresti secretara 2000 2 4 Marin Radu 1970.03.98 Bucuresti inginer 4000 3 Cap. 2 - Baze de date relationale 38 38  28.10.2021 Cheia străină Fie două relații R1 și R2, între care exista o asociere cu raportul 1: N. O cheie străină (foreign key) este o submulţime FK de atribute ale relaţiei R2 care referă cheia CK din relaţia R1 şi satisface urm. condiţii: atributele cheii străine FK sunt definite pe domenii compatibile cu cele ale atributelor unei cheii candidate CK a relaţiei R1 valorile atributelor FK într-un tuplu din relaţia R2, fie sunt identice cu valorile atributelor CK a unui tuplu oarecare din starea curentă a relaţiei R1, fie sunt NULL Două domenii sunt compatibile dacă ele sunt compatibile din punct de vedere al tipului de date și compatibile semantic (are sens să fie comparate) În limbajul SQL verificarea domeniilor se rezumă la verificarea tipurilor de date, iar compatibilitatea semantică trebuie să fie asigurată de proiectant Cheia străină reprezintă o constrângere referenţială între cele 2 relații Relația referită (R1) – relație părinte, relația care referă (R2) – relație fiu Cheia străină se specifică în comanda CREATE TABLE sau ALTER TABLE: [CONSTRAINT nume_constr] FOREIGN KEY (cheie_străina) REFERENCES relația_referita (cheie_candidata) Exemplu: CREATE TABLE ANGAJATI ( IdAngajat int PRIMARY KEY, Nume varchar(20) NOT NULL, Prenume varchar(20) NOT NULL, IdSecţie int, CONSTRAINT FK FOREIGN KEY (IdSectie) REFERENCES SECTII(IdSectie)); Cap. 2 - Baze de date relationale 39 39 Mentinerea integrității referențiale a relațiilor (1) Integritatea referenţială (referential integrity) este proprietatea bazei de date prin care orice cheie străină: fie are o valoare care se regăsește printre valorile cheii candidate referite fie are valoarea NULL Pentru menținerea integrității referențiale trebuie să fie impuse restricții operațiilor de modificare a stării relațiilor (INSERT, DELETE, UPDATE) Restricțiile care se impun operațiilor de modificare a relaţiilor depind de rolul relaţiei (relaţie care referă, relaţie referită, sau poate avea ambele roluri) Operaţia INSERT: Într-o relaţie care nu referă altă relație, inserarea se poate face fără restricţii Într-o relaţie care referă (care conţine o cheie străină): SGBD-ul permite introducerea unui tuplu nou numai dacă: (a) valoarea cheii străine a tuplului nou este NULL sau (b) există o valoare a cheii referite egală cu valoarea cheii străine a tuplului nou Operatia DELETE: Într-o relație care nu este referită ștergerea se poate face fără restricții Într-o relație referită se admite: ştergere restricţionată, ştergere în cascadă, anularea (SET NULL) a cheilor străine care refereau tuplul șters Ştergerea restricţionată interzice ştergerea unui tuplu din relaţia referită dacă acesta este referit de un tuplu din relaţia care o referă Cap. 2 - Baze de date relationale 40 40  28.10.2021 Mentinerea integrității referențiale a relațiilor (2) Ștergerea în cascadă permite ştergerea unui tuplu din relaţia referită; dacă tuplul şters era referit de unul sau mai multe tupluri, atunci se şterg şi acestea din relaţia care o referă; dacă tuplurile şterse din relaţia care referă sunt, la rândul lor referite de alte tupluri, atunci trebuie să fie şterse şi acestea, ş.a.m.d.; se execută deci o ştegere în cascadă Operaţia UPDATE este o ştergere urmată de o introducere, deci restricţiile de actualizare reprezintă combinaţia restricţiilor de introducere şi de ştergere În limbajul SQL se specifică opţiunile ON DELETE și ON UPDATE constrângerii de cheie străină; valorile posibile ale acestor opţiuni sunt: RESTRICT – ștergerea restricționată (este valoare implicită) CASCADE – ștergerea în cascadă; SET NULL – anularea cheilor străine care refereau tuplul șters Exemplu: CREATE TABLE ANGAJATI ( IdAngajat int PRIMARY KEY, Nume varchar (20) NOT NULL,........................ Sectie int, CONSTRAINT FK FOREIGN KEY (Sectie) REFERENCES SECTII (IdSectii) , ON DELETE CASCADE ON UPDATE RESTRICT); Cap. 2 - Baze de date relationale 41 41