Sql_ limbajul bazelor de date relationale

SQL - Structured Query Language

Curs IIISQL - LIMBAJUL BAZELOR DE DATE

RELATIONALE


Obiective

Aparitia limbajului SQL pentru baze de date.

Tipuri de instructiuni SQL.

Interogarea bazelor de date - SELECT SQL.

Clauzele principale ale instructiunii SELECT.

Moduri de a extrage date din mai multe tabele relationate din baza

de date.

Functii in SQL.

Grupuri de date si functii de grup.


Obiective

Subinterogari simple.

Subinterogari multicoloana.

Interogari ce folosesc operatorii de multimi.

Categorii de instructiuni DML:

– inserari

– actualizari

– stergeri

Controlul tranzactiilor.


Definitii SQL

SQL - “Structured Query Language” : – Numele generic al unei familii de limbaje de definitie, de

manipulare si de control pentru o baza de date relationala.

– SQL a fost conceput ca un limbaj standard de descriere a datelor si acces la informatiile din baza de date.

– Ulterior s-a dezvoltat ca o adevarata tehnologie dedicata arhitecturilor sistemelor de baze de date client-server.

SQL este un limbaj standardizat (ANSI):– Diversele SGBD-uri au implementari proprii, mai mult sau mai

putin diferite de standard.

SQL este atit un limbaj interactiv cit si integrat: – folosit din alte limbaje si medii cum ar fi xBase, C/C++, Java,

Pascal etc


Istoric SQL

Utilizat initial de firma IBM, SQL a devenit la mijlocul deceniului trecut un standard în domeniu.

Primul standard SQL a fost creat în 1989 de catre ANSI (Institutul National American pentru standarde) fiind cunoscut sub numele de ANSI-SQL ’89.

Proliferarea în timp a diferitelor implementari ale limbajului SQL a determinat formarea consortiului SAG (“The SQL Access Group”), cu scopul de a concepe un set de standarde SQL, care sa aiba la baza un subset al standardului ANSI-SQL’89 si sa rezolve probleme de implementarea acestora în arhitecturi client/server cum ar fi conectivitatea si interfetele de programare.

Fiecare SGBD implementeaza limbajul standard SQL (sau un subset mai redus de instructiuni) la care adauga sau modifica instructiunile proprii.


Proprietati ale BDR

O baza de date relationala:– contine o colectie de tabele fara pointeri fizici– poate fi accesata si modificata de instructiuni SQL– foloseste un set de operatori relationali pentru implementarea

instructiunilor SQL

Modelul Relational SQL– Relatie Tabel– Domeniu ---– Atribut Coloana– Tuplu Linie– Cheie primara Cheie primara– Cheie externa Referinta


Relationarea tabelelor

Fiecare linie dintr-un tabel este identificata unic de cheia primara (PK).

Se pot relationa logic date din tabele diferite prin cheile straine (FK).

Tabelul: Tabelul: ANGAJATANGAJAT Tabelul: Tabelul: DEPARTAMENTDEPARTAMENT

Cheie primaraCheie primara Cheie primaraCheie primaraCheie externaCheie externa

MARCA NUME FUNCTIE NRDEPT

7839 KING PRESIDENT 10

7698 BLAKE MANAGER 30

7782 CLARK MANAGER 10

7566 JONES MANAGER 20

NRDEPT DENUMIERE LOCALITATE

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON


Comunicarea SQL cu SGBD

DatabaseDatabase

SQL> SELECT localitate 2 FROM departament;

SQL> SELECT localitate 2 FROM departament;

Instructiunea SQLInstructiunea SQLInstructiunea este Instructiunea este

trimisa la BDtrimisa la BD

LOCALITATE

-------------

NEW YORK

DALLAS

CHICAGO

BOSTON

LOCALITATE

-------------

NEW YORK

DALLAS

CHICAGO

BOSTON

Afisarea rezultatelorAfisarea rezultatelor


Tipuri de instructiuni SQL

SELECT

INSERTUPDATEDELETE

CREATEALTERDROPRENAMETRUNCATE

COMMITROLLBACKSAVEPOINT

GRANTREVOKE

Data retrieval Data retrieval

Data manipulation language (DML)Data manipulation language (DML)

Data definition language (DDL)Data definition language (DDL)

Transaction controlTransaction control

Data control language (DCL)Data control language (DCL)


Tabele folosite in exemple

MARCA NUME FUNCTIE SEF DATA_ANG SAL COM NRDEPT

--------- ---------- --------- --------- --------- --------- --------- ---------

7839 KING PRESIDENT 17-NOV-81 5000 10

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 1500 10

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7900 JAMES CLERK 7698 03-DEC-81 950 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7369 SMITH CLERK 7902 17-DEC-80 800 20

7788 SCOTT ANALYST 7566 09-DEC-82 3000 20

7876 ADAMS CLERK 7788 12-JAN-83 1100 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

MARCA NUME FUNCTIE SEF DATA_ANG SAL COM NRDEPT

--------- ---------- --------- --------- --------- --------- --------- ---------

7839 KING PRESIDENT 17-NOV-81 5000 10

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 1500 10

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7900 JAMES CLERK 7698 03-DEC-81 950 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7369 SMITH CLERK 7902 17-DEC-80 800 20

7788 SCOTT ANALYST 7566 09-DEC-82 3000 20

7876 ADAMS CLERK 7788 12-JAN-83 1100 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

ANGAJATANGAJAT

DEPARTDEPART

NRDEPT DENUMIRE LOCALITATE

--------- -------------- ----------

10 ACCOUNTING NEW

YORK

20 RESEARCH DALLAS

30 SALES CHICAGO


NRDEPT DENUMIRE LOCALITATE

--------- -------------- ----------

10 ACCOUNTING NEW

YORK

20 RESEARCH DALLAS

30 SALES CHICAGO


GRAD SALMIN SALMAX

--------- --------- ---------

1 700 1200

2 1201 1400

3 1401 2000

4 2001 3000

5 3001 9999

GRAD SALMIN SALMAX

--------- --------- ---------

1 700 1200

2 1201 1400

3 1401 2000

4 2001 3000

5 3001 9999

GRADESALGRADESAL


Interogarea bazelor de date

Interogarea bazelor de date relationale se face cu instructiunea SQL SELECT

Instructiunea SELECT este complexa, formata din numeroase clauze si optiuni

SELECT SQL implementeaza o serie de operatori relationali (selectie, proiectie, join)

Instructiunea SELECT poate extrage date din una sau mai multe tabele ale bazei de date


Operatori relationali

SelectionSelection ProjectionProjection

Tabela 1Tabela 1 Tabela 2Tabela 2

Tabela 1Tabela 1 Tabela 1Tabela 1JoinJoin


Instructiunea de baza SELECT

SELECT identifica ce coloane sunt afisate FROM identifica din care tabele sunt datele Instructiunile SQL nu sunt ‘case sensitive’ Pot fi scrise pe mai multe linii Cuvintele cheie nu pot fi abreviate Clauzele se pun pe linii separate pentru

lizibilitate

SELECT [DISTINCT] {*, coloana [alias],...}FROM tabel;

SELECT [DISTINCT] {*, coloana [alias],...}FROM tabel;


Valori NULL

O valoare NULL este valoarea inexistenta, necunoscuta.

Valoare NULL difera de sirul vid sau de 0.

SQL> SELECT nume, functie, com 2 FROM angajat;

NUME FUNCTIE COM---------- --------- ---------KING PRESIDENTBLAKE MANAGER...TURNER SALESMAN 0...14 rows selected.


Aliasuri de coloana

Redenumeste numele unei coloane la afisare Este folositor la afisarea coloanelor calculate Se adauga dupa coloana Trebuie introdus intre ghilimele duble daca are

spatii sau este ‘case sensitive’SQL> SELECT nume as Nume, 2 sal*12 "Salar Anual" 3 FROM emp;

NUME Salar Anual

------------- -------------

...


Eliminarea duplicatelor

Liniile duplicate se elimina prin folosirea optiunii DISTINCT in clauza SELECT

SQL> SELECT DISTINCT nrdept 2 FROM angajat;

NRDEPT--------- 10 20 30


Limitarea liniilor rezultate

Limitarea liniilor rezultate dintr-o instructiune SELECT se face prin clauza WHERE.

Clauza WHERE urmeaza clauzei FROM. Clauza WHERE contine conditii logice simple

sau complexe (legate cu operatorii logici AND si OR).

SELECT [DISTINCT] {*, coloana [alias], ...}FROM tabe;[WHERE conditii];


Operatori de comparatie

Semnificatie

Egal

Mai mare

Mai mare sau egal

Mai mic

Mai mic sau egal

Diferit

Operator

=

>

>=

<

<=

<>

Operator

BETWEEN

...AND...

IN(list)

LIKE

IS NULL

Semnificatie

Intre 2 valori (inclusiv)

Cuprins intr-o lista de

valori

Corespunde unui

sablon de caractere

Este o valoare null


Exemplu ORDER BY

Afiseaza ordonat rezultatele interogarii: Este ultima clauza din instructiunea SELECT

SQL> SELECT nume, nrdept, sal 2 FROM angajat 3 ORDER BY nrdept, sal DESC;

NUME NRDEPT SAL---------- --------- ---------KING 10 5000CLARK 10 2450MILLER 10 1300FORD 20 3000...14 rows selected.


Date din tabele multiple

MARCA NRDEPT LOCALITATE----- ------- -------- 7839 10 NEW YORK 7698 30 CHICAGO 7782 10 NEW YORK 7566 20 DALLAS 7654 30 CHICAGO 7499 30 CHICAGO...14 rows selected.

MARCA NRDEPT LOCALITATE----- ------- -------- 7839 10 NEW YORK 7698 30 CHICAGO 7782 10 NEW YORK 7566 20 DALLAS 7654 30 CHICAGO 7499 30 CHICAGO...14 rows selected.

ANGAJAT ANGAJAT DEPART DEPART MARCA NUME ... NRDEPT------ ----- ... ------ 7839 KING ... 10 7698 BLAKE ... 30 ... 7934 MILLER ... 10

NRDEPT DENUMIRE LOCALIT ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON


Ce este un JOIN ?

Interogare care extrage date din mai multe tabele:

Conditiile de legatura se scriu in clauza WHERE.

Daca o coloana are acelasi nume in tabele diferite, se prefixeaza numele coloanei cu numele tabelei de unde provine.

SELECT tabel1.coloana, tabel2.coloanaFROM tabel1, tabel2WHERE tabel1.coloana1 = tabel2.coloana2;

SELECT tabel1.coloana, tabel2.coloanaFROM tabel1, tabel2WHERE tabel1.coloana1 = tabel2.coloana2;


Produs Cartezian

Se efectueaza un produs cartezian atunci cind:– o conditie de join este omisa– o conditie de join este invalida– toate liniile din prima tabela sunt unite cu toate liniile din a

doua tabela

Pentru evitarea unui produs cartezian se introduce o conditie de legatura valida in clauza WHERE a instructiunii SELECT.


Tipuri de Join

EquijoinEquijoin Non-equijoinNon-equijoin Outer joinOuter join Self joinSelf join


Equijoin

ANGAJAT ANGAJAT DEPART DEPART MARCA NUME NRDEPT------ ------- ------- 7839 KING 10 7698 BLAKE 30 7782 CLARK 10 7566 JONES 20 7654 MARTIN 30 7499 ALLEN 30 7844 TURNER 30 7900 JAMES 30 7521 WARD 30 7902 FORD 20 7369 SMITH 20...14 rows selected.

NRDEPT DENUMIRE LOCALIT ------- ---------- -------- 10 ACCOUNTING NEW YORK 30 SALES CHICAGO 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICAGO 20 RESEARCH DALLAS 20 RESEARCH DALLAS...14 rows selected.

Primary keyPrimary key Foreign keyForeign key


Non-equijoin

ANGAJATANGAJAT GRADSALARGRADSALAR

““salarul in tabela ANGAJAT salarul in tabela ANGAJAT este intre salarul minimeste intre salarul minimsi salarul maxim in si salarul maxim in tabela GRADSALAR”tabela GRADSALAR”

MARCA NUME SAL------ ------- ------ 7839 KING 5000 7698 BLAKE 2850 7782 CLARK 2450 7566 JONES 2975 7654 MARTIN 1250 7499 ALLEN 1600 7844 TURNER 1500 7900 JAMES 950...14 rows selected.

GRAD SALMIN SAL----- ----- ------1 700 12002 1201 14003 1401 20004 2001 30005 3001 9999


Exemplu Non-equijoin

NUME SAL GRAD---------- --------- ---------JAMES 950 1SMITH 800 1ADAMS 1100 1...14 rows selected.

SQL> SELECT a.nume, a.sal, b.grad

2 FROM angajat a, gradsalar s

3 WHERE a.sal

4 BETWEEN b.salmin AND b.salmax;


Outer-join

ANGAJAT ANGAJAT DEPART DEPART

Nu exista angajati in Nu exista angajati in departamentul OPERATIONSdepartamentul OPERATIONS

NUME NRDEPT----- ------KING 10BLAKE 30CLARK 10JONES 20...

NRDEPT DENUMIRE------ ----------10 ACCOUNTING30 SALES10 ACCOUNTING20 RESEARCH...40 OPERATIONS


Outer-join

Se foloseste pentru a afisa linii care nu indeplinesc conditii de join normale (equijoin).

In SQL standard cuvintele cheie sunt: LEFT OUTER JOIN, RIGHT OUTER JOIN

Operatorul outer-join specific Oracle este (+).

SELECT table.column, table.column

FROM table1, table2

WHERE table1.column(+) = table2.column;


FROM table1, table2

WHERE table1.column(+) = table2.column;


FROM table1, table2

WHERE table1.column = table2.column(+);


FROM table1, table2

WHERE table1.column = table2.column(+);


Exemplu Outer-join

SQL> SELECT a.nume, d.nrdept, d.denumire

2 FROM angajat a, depart d

3 WHERE a.nrdept(+) = d.nrdept

4 ORDER BY a.nrdept;

NUME NRDEPT DENUMIRE---------- --------- -------------KING 10 ACCOUNTINGCLARK 10 ACCOUNTING... 40 OPERATIONS15 rows selected.


Self-join

ANGAJAT (LUCRATOR)ANGAJAT (LUCRATOR) ANGAJAT (MANAGER)ANGAJAT (MANAGER)

““SEF din tabela LUCRATOR este egal cu MARCA din SEF din tabela LUCRATOR este egal cu MARCA din tabela MANAGER " tabela MANAGER "

MARCA NUME SEF----- ------ ---- 7839 KING 7698 BLAKE 7839 7782 CLARK 7839 7566 JONES 7839 7654 MARTIN 7698 7499 ALLEN 7698

MARCA NUME----- --------

7839 KING 7839 KING 7839 KING 7698 BLAKE 7698 BLAKE


Caracteristici functii SQL

Confera interogarilor putere si flexibiltate.

Functiile pot fi folosite la:– Efectuarea de calcule asupra datelor din tabele

– Gestionarea liniilor individuale de date

– Gestionarea grupurilor de date

– Conversia tipurilor de date ale coloanelor unei tabele

Functiile SQL accepta argumente si returneaza valori. Functiile SQL sunt specifice fiecarui SGBD. Sunt prezentate functiile implementate de serverul

Oracle.


Structura unei functii

FunctieFunctieIntrare

arg 1arg 1

arg 2arg 2

arg narg n

Functia executa o actiune

Iesire

ValoareaValoarea rezultatarezultata


Tipuri de functii SQL

FunctiiFunctii

Functii de un Functii de un singur rindsingur rind

Functii de mai Functii de mai multe rindurimulte rinduri


Functia de un singur rind

Nume_Functie (coloana|expresie, [arg1, arg2,...])Nume_Functie (coloana|expresie, [arg1, arg2,...])

Preia argumente si intoarce o valoare.

Actioneaza asupra unui singur rind.

Intoarce un rezultat pentru fiecare rind.

Poate fi apelata imbricat.

Poate modifica tipul de date al unei expresii.


Functii de un singur rind

ConversieConversie

Siruri de Siruri de caracterecaractere

NumericeNumerice

Date Date calendaristicecalendaristice

GeneraleGeneraleFunctii de un Functii de un

singur rindsingur rind


Folosirea datelor calendaristice

Serverul Oracle stocheaza valorile date calendaristice in formatul: secol, an, luna, zi, ora, minute, secunde.

Formatul predefinit este ‘DD-MON-YY’. Functia care intoarce data sistemului este

SYSDATE. Daca se aduna un numar la o data rezulta o

data calendaristica. Diferenta intre doua date reprezinta numarul

de zile dintre cele doua date calendaristice.


Functii de conversie

Conversii impliciteConversii implicitede tipuri de datede tipuri de date

Conversii explicite Conversii explicite de tipuri de datede tipuri de date

Conversii de Conversii de tipuri de datetipuri de date


Conversii explicite

NUMBERNUMBER CHARACTERCHARACTER

TO_CHARTO_CHAR

TO_NUMBERTO_NUMBER

DATEDATE

TO_CHARTO_CHAR

TO_DATETO_DATE


Convertirea datelor calendaristice in caractere

Primul argument este data calendaristica care trebuie convertita.

Al doilea argument este sirul de formatare. Sirul de formatare:

– trebuie inclus intre apostroafe simple si este ‘case sensitive’– poate include orice format valid de data calendaristica– poate include subsirul ‘fm’ pentru eliminarea spatiilor

TO_CHAR(date, 'fmt')TO_CHAR(date, 'fmt')


Functia TO_CHAR cu date calendaristice

SQL> SELECT nume, 2 TO_CHAR(data_ang, 'fmDD Month YYYY') DATA_ANG 3 FROM angajat;

NUME DATA_ANG---------- -----------------KING 17 November 1981BLAKE 1 May 1981CLARK 9 June 1981JONES 2 April 1981MARTIN 28 September 1981ALLEN 20 February 1981...14 rows selected.


Functia NVL

Converteste o valoare NULL la o valoare data. Tipurile de date acceptate sunt datele

calendaristice, sirurile de caractere si numerele

SQL> SELECT nume, sal, com, (sal*12)+NVL(com,0) VENIT 2 FROM angajat;

NUME SAL COM VENIT---------- --------- --------- --------------------KING 5000 60000BLAKE 2850 34200CLARK 2450 29400JONES 2975 35700MARTIN 1250 1400 16400ALLEN 1600 300 19500


Functia DECODE

Functie de cautare conditionala echivalenta cu instructiunile CASE sau IF-THEN-ELSE.

Primul argument este coloana sau expresia dupa care se face cautarea.

Al doilea argument reprezinta valoare de comparatie, urmatorul este valoarea intoarsa in cazul in care conditia este adevarata.

Ultimul argument este valoarea intoarsa daca toate conditiile sunt false.

DECODE(col/expresie, cautare1, rezultat1 [, cautare2, rezultat2,...,] [, predefinit])

DECODE(col/expresie, cautare1, rezultat1 [, cautare2, rezultat2,...,] [, predefinit])


Functiile de un singur rind pot fi imbricate. Functia care se evalueaza prima este cea cu

nivelul de imbricare cel mai mare.

Using the IN OperatorUsing the IN OperatorImbricarea functiilor

F3(F2(F1(col,arg1),arg2),arg3)

Pas 1 = Rezultat 1

Pas 2 = Rezultat 2

Pas 3 = Rezultat 3


Functii de grup

ANGAJATANGAJAT

““salarul maxim salarul maxim din tabelul EMP”din tabelul EMP”

NRDEPT SAL--------- --------- 10 2450 10 5000 10 1300 20 800 20 1100 20 3000 20 3000 20 2975 30 1600 30 2850 30 1250 30 950 30 1500 30 1250

MAX(SAL)

---------

5000


Folosirea functiilor de grup

Tipuri de functii de grup:– SUM– AVG– COUNT– MAX– MIN

SELECT coloana, functie_grup(coloana)FROM tabel[WHERE conditie][ORDER BY coloana];


Grupuri de date

ANGAJATANGAJAT

““salarul mediusalarul mediupe fiecare pe fiecare

departament ”departament ”

2916.66672916.6667

21752175

1566.66671566.6667

NRDEPT SAL--------- --------- 10 2450 10 5000 10 1300 20 800 20 1100 20 3000 20 3000 20 2975 30 1600 30 2850 30 1250 30 950 30 1500 30 1250

NRDEPT AVG(SAL)

------- ---------

10 2916.6667

20 2175

30 1566.6667


Clauza GROUP BY

Se pot imparti liniile unei tabele in grupuri folosind clauza GROUP BY.

Orice coloana care apare in clauza SELECT si nu este intr-o functie de grup atunci trebuie sa apara in clauza GROUP BY.

SELECT coloana, functie_grup(coloana)FROM tabel[WHERE conditie][GROUP BY expresie_group_by][ORDER BY coloana];


Filtrarea grupurilor de date

““salarul maxim salarul maxim pe departament pe departament sa fie mai mare sa fie mai mare

ca 2900”ca 2900”

EMPEMP

50005000

30003000

28502850

DEPTNO SAL

--------- ---------

10 2450

10 5000

10 1300

20 800

20 1100

20 3000

20 3000

20 2975

30 1600

30 2850

30 1250

30 950

30 1500

30 1250

DEPTNO MAX(SAL)

--------- ---------

10 5000

20 3000


Clauza HAVING

Se foloseste clauza HAVING restrictioneaza grupurile de date:– liniile sunt grupate– sunt aplicate functiile de grup– grupurile care indeplinesc conditiile din clauza HAVING sunt

afisate

SELECT column, group_functionFROM table[WHERE condition][GROUP BY group_by_expression][HAVING group_condition][ORDER BY column];


Subinterogari

“Cine are salarul mai mare ca salarul lui Jones ?”

“Care angajat are salarul mai mare ca salarul lui Jones ?”

Interogarea principala

??

“Ce salar are Jones ?”??

Subinterogare


Sintaxa subinterogarii

Interogarea interna se executa o singura data, inaintea interogarii principale.

Rezultatele subinterogarii sunt folosite de interogarea principala.

SELECT coloaneFROM tabelWHERE expresie operator

(SELECT coloane FROM tabel)


Reguli pentru subinterogari

Subinterogarile se includ intre paranteze. Subinterogarea se plaseaza in partea dreapta

a operatorului de comparatie. Clauza ORDER BY nu este permisa in

subinterogare. Folositi operatori de o singura linie pentru

subinterogari de o singura linie. Folositi operatori multi-linie pentru

subinterogari care intorc mai multe linii.


Subinterogari in clauza FROM

NUME SAL NRDEPT SALMED---------- --------- --------- ----------KING 5000 10 2916.6667JONES 2975 20 2175SCOTT 3000 20 2175...6 rows selected.

NUME SAL NRDEPT SALMED---------- --------- --------- ----------KING 5000 10 2916.6667JONES 2975 20 2175SCOTT 3000 20 2175...6 rows selected.

SQL> SELECT a.nume, a.sal, a.nrdept, b.salmed 2 FROM angajat a, (SELECT nrdept, avg(sal) salmed 3 FROM angajat 4 GROUP BY nrdept) b 5 WHERE a.nrdept = b.nrdept 6 AND a.sal > b.salmed;


Operatori cu multimi

IntersectIntersect

AA BB

AA BB

UnionUnion / Union All/ Union All

AA BB

AA BB

MinusMinus


Reguli pentru operatorii de multime

Expresiile din clauzele SELECT trebuie sa se potriveasca la numar si ca tip de date.

Liniile duplicate sunt eliminate, exceptie facind operatorul UNION ALL.

Numele coloanelor din prima interogare apar in rezultat.

Predefinit, rezultatul este ordonat crescator. Se pot folosi parantezele pentru a schimba

ordinea de executie.


Limbajul de manipulare a datelor

O instructiune DML este executata cind: se adauga o linie la o tabela se modifica o linie existenta se sterge o linie dintr-o tabela

O tranzactie consta intr-o colectie de instructiuni DML care alcatuiesc un modul logic de lucru.


Tipuri instructiuni DML

Adaugarea datelor:– INSERT …

Modificarea datelor:– UPDATE …

Stergerea datelor:– DELETE …

Instructiuni pentru controlul tranzactiilor:– COMMIT– ROLLBACK– SAVEPOINT


Sintaxa instructiunii INSERT

Insereaza o linie noua in tabela. Daca nu se afiseaza lista coloanelor trebuie sa se

listeze valori pentru fiecare coloana din tabel. Ordinea valorilor trebuie sa corespunda cu ordinea

coloanelor din tabel. Optional se poate afisa si lista coloanelor. Valorile sir de caractere si data calendaristica se

introduc intre apostroafe.

INSERT INTO tabel [(coloana [, coloana...])]VALUES (valoare [, valoare...]);

INSERT INTO tabel [(coloana [, coloana...])]VALUES (valoare [, valoare...]);


Actualizarea datelor

Se modifica valorile dintr-un tabel. Daca nu se specifica o clauza WHERE atunci

vor fi actualizate toate liniile din tabel. In functie de conditia din clauza WHERE se pot

actualiza 0, 1 sau mai multe linii din tabel.

UPDATE tabelSET coloana = valoare [, coloana = valoare][WHERE conditie];

UPDATE tabelSET coloana = valoare [, coloana = valoare][WHERE conditie];


Constringeri de integritate

UPDATE angajat *ERROR at line 1:ORA-02291: integrity constraint (USR.ANGAJAT_DEPT_FK) violated - parent key not found

UPDATE angajat *ERROR at line 1:ORA-02291: integrity constraint (USR.ANGAJAT_DEPT_FK) violated - parent key not found

SQL> UPDATE angajat 2 SET nrdept = 55 3 WHERE nrdept = 10;

SQL> UPDATE angajat 2 SET nrdept = 55 3 WHERE nrdept = 10;

Departamentul c

u numarul 55 nu exis

ta !

Departamentul c

u numarul 55 nu exis

ta !


Sintaxa instructiunii de stergere

Se pot sterge linii din tabel cu DELETE. Liniile sterse sunt cele care indeplinesc

conditiile din clauza WHERE. Daca nu exista clauza WHERE se sterg toate

liniile din tabel.

DELETE [FROM] tabel[WHERE conditie];

DELETE [FROM] tabel[WHERE conditie];


Tranzactii ale bazei de date

O tranzactie a bazei de date consta in:– instructiuni DML care executa modificari consistente ale datelor– o instructiune DDL– o instructiune DCL

O tranzactie incepe cu prima instructiune DML. O tranzactie se termina la unul din urmatoarele

evenimente:– COMMIT sau ROLLBACK– instructiuni DDL sau DCL (se executa un COMMIT automat)– deconectarea utilizatorului– pica sistemul


Zavoarele

Zavoarele Oracle: previn interactiunile distructive intre tranzactiile

concurente nu necesita actiunea utilizatorului automat folosesc cel mai jos nivel de

restrictivitate sunt mentinute pe durata unei tranzactii sunt de doua tipuri:

– exclusive– partajate


Tranzactii concurente

Fiecare tranzactie executata complet trebuie sa lase baze de date intr-o stare consistenta:

– utilizatorii specifica restrictiile de integritate pe care SGBD-ul le aplica– SGBD-ul nu intelege semnificatia datelor– Utilizatorul are responsabilitatea ca o tranzactie sa lase baza de date intr-o

stare consistenta

SGBD asigura ca executia {T1, ... , Tn} este serializabila.– Inainte de a scrie/citi un obiect, o tranzactie cere un zavor (lock) asupra

obiectului, si asteapta pana SGBD asigura acel zavor. Toate zavoarele sunt sterse la sfarsitul tranzactiei.

– Ideea: daca actiunea tranzactiei Ti (de scriere a lui X) afecteaza Tj (care citeste X), Ti va obtine zavorul pe X iar Tj este fortata sa astepte pana Ti se termina; acest mecanism ordoneaza tranzactiile.

– Putem avea cazul cand Tj are deja un zavor pe Y iar Ti va cere la randul ei un zavor pe Y. In acest caz avem ‘Deadlock’. Ti sau Tj este restartata.


Axiome ACID

AA tomicitate: Toate actiunile unei tranzactii se produc toate sau nici una (“all-or-nothing”).

CC onsistenta: Daca fiecare tranzactie este consitenta si daca baza de date se starteaza consistenta atunci ea ramane consistenta in orice moment.

II zolarea: Executia unei tranzactii este izolata de celalte tranzactii.

DD urabilitate: Daca o tranzactie comite atunci efectele sale sunt permanentizate.


Avantajele COMMIT si ROLLBACK

COMMIT - permanentizeaza schimbarile in baza de date.

ROLLBACK - anuleaza schimbarile facute in tranzactia curenta si aduce baza de date in starea anterioara tranzactiei curente.

Asigura consistenta datelor. Permit vizualizarea datelor inainte de a

permanentiza schimbarile in baza de date. Grupeaza operatiile relationate logic.


Controlul tranzactiilor

DELETEDELETE

TranzactieTranzactie

Savepoint ASavepoint A

ROLLBACK to Savepoint BROLLBACK to Savepoint B

DELETEDELETE

Savepoint BSavepoint BCOMMITCOMMIT

INSERTINSERTUPDATEUPDATE

ROLLBACK to Savepoint AROLLBACK to Savepoint A

INSERTINSERTUPDATEUPDATEINSERTINSERT

ROLLBACKROLLBACK

INSERTINSERT


Procesarea implicita a tranzactiilor

Se executa un COMMIT automat in urmatoarele conditii:– este lansata o instructiune DDL– este lansata o instructiune DCL– utilizatorul de deconecteaza in mod normal de la

baza de date

Se executa un ROLLBACK automat in cazul unei deconectari anormale sau cind sistemul pica (software sau hardware).


Starea datelor inainte de ROLLBACK sau COMMIT

Poate fi restaurata starea anterioara a datelor. Utilizatorul poate vizualiza modificarile facute

prin instructiunile DML prin instructiuni SELECT.

Alt utilizator nu poate vedea modificarile facute de utilizatorul curent.

Liniile modificate sunt blocate. Alt utilizator nu poate modifica datele schimbate de utilizatorul curent.


Starea datelor dupa COMMIT

Schimbarile efectuate sunt permanentizate in baza de date.

Starea anterioara a datelor este pierduta. Toti utilizatorii pot vizualiza datele modificate. Liniile blocate sunt accesibile si pentru alti

utilizatori pentru modificare. Sunt sterse toate punctele de salvare marcate

pe parcursul unei tranzactii.


Starea datelor dupa ROLLBACK

Se anuleaza schimbarile prin comanda ROLLBACK:– sunt anulate modificarile asupra datelor– starea anterioara a datelor este restaurata– liniile blocate sunt eliberate pentru modificari


ROLLBACK la nivel de instructiune DML

Daca o instructiune DML nu reuseste sa se execute cu succes, numai acea instructiune este anulata.

Serverul Oracle implementeaza un punct de salvare implicit.

Toate celelalte schimbari sunt mentinute. Utilizatorul trebuie sa incheie tranzactia printr-o

instructiune explicita de COMMIT sau ROLLBACK.


Consistenta la citire

Consitenta la citire garanteaza o vedere consistenta a datelor in orice moment.

Schimbarile efectuate de un utilizator nu intra in conflict cu schimbarile facute de alt utilizator.

Se asigura ca pentru aceleasi date: ‘cititorii’ nu asteapte ‘scriitorii’ ‘scriitorii’ nu asteapta ‘cititorii’


Implementarea consitentei la citire

UPDATE angajatUPDATE angajatSET sal = 2000SET sal = 2000WHERE nume = WHERE nume = 'SCOTTSCOTT';;

DataDatablocksblocks

RollbackRollbacksegmentssegments

changedchangedand and unchanged unchanged datadata

before before changechange“old” data“old” data

User AUser A

User BUser B

ReadReadconsistentconsistentimageimage

SELECT *SELECT *FROM angajat;FROM angajat;


Concluzii

S-au introdus definitii ale limbajului SQL. S-au urmarit principalele etape in istoricul

aparitiei limbajului SQL. S-au enumerat principalele tipuri de instructiuni

SQL. Sintaxa de baza SELECT SQL Clauzele instructiunii de interogare: SELECT,

FROM, WHERE, ORDER BY. Extragerea datelor din mai multe tabele.


Concluzii

Functii SQL de un singur rind:– pentru siruri de caractere– pentru date calendaristice– pentru numere– functii de conversie– functii generale

Grupuri de date si functii de grup. Subinterogari simple, multi-linie si multi-

coloana. Interogari ce folosesc operatori de multimi:

reuniunea, intersectia, diferenta.


Concluzii

Limbajul de interogare a datelor (DML):– adaugarea datelor (INSERT)– modificarea datelor (UPDATE)– stergerea datelor (DELETE)

Controlul tranzactiilor:– COMMIT– ROLLBACK– puncte de salvare (SAVEPOINT)– consistenta la citire– zavoare Oracle

Date post:	27-Oct-2015
Category:	Documents
Upload:	ciprian-andrei-nicusor
View:	81 times
Download:	9 times

Sql_ limbajul bazelor de date relationale

Documents