introducere in sql

BAZE DE DATE

NOTE DE CURS

Liviu Şerbănescu UNIVERSITATEA HYPERION

1

CUPRINS

1. Utilizarea unui modul de administrare a bazelor de date,

instalare, conectare, prezentare, comenzi simple. Organizarea

SGBD PostgreSQL;

2. Crearea bazelor de date, a sesiunilor şi a utilizatorilor.

Creare tabele cu diverse tipuri de câmpuri;

3 Interogarea bazei de date (cazul unui singur tabel)

4 Popularea bazei de date.Lucrul cu date de tip XML.

5 Combinarea interogărilor

6 Condiţii de selecţie a datelor

7 Expresii condiţionale

8 Tipuri de asocieri între înregistrările unor tabele

9 Interogarea mai multor tabele

10 Moştenirea;

11 Subinterogări

12 Tipul CAST

13 Funcţii SQL la nivel de server

14 Gestiunea accesului la BD

2

UTILIZAREA UNUI MODUL DE ADMINISTRARE A BAZELOR DE DATE,

INSTALARE, CONECTARE, PREZENTARE, COMENZI SIMPLE.

ORGANIZAREA SGBD PostgreSQL;

Definire SGBDTermenul de bază de date (database) reprezintă o colecţie

de informaţii corelate, relaţiile logice dintre aceste informaţii şi tehnicile de prelucrare corespunzătoare (căutare, sortare, ştergere, inserare, modificare, etc.).

Sistemul de gestiune a bazelor de date S.G.B.D. (Database Management System - DBMS) reprezintă sistemul de programe care permite accesarea bazei de date de către utilizatori, accesare ce permite realizarea de operaţii asupra bazei de date.

2.Arhitecturi SGBDClasificări SGBD

➔ Clasificare dupa modelul de date:●Modelul de ierarhic de date - legăturile dintre

date sunt ordonate unic, accesul se face numai prin vârful ierarhiei, un subordonat nu poate avea decât un singur superior direct şi nu se poate ajunge la el decât pe o singură cale;

●Modelul de date retea - datele sunt reprezentate ca într-o mulţime de ierarhii, în care un membru al ei poate avea oricâţi superiori, iar la un subordonat se poate ajunge pe mai multe căi;

●Modelul relational (aplicaţii comerciale, volum de date mare si tipuri de date simple) - structura de bază a datelor este aceea de relaţie – tabel, limbajul SQL (Structured Query Language) este specializat în comenzi de manipulare la nivel de tabel;

●Modelul obiect-orientat (aplicaţiilor ştiintifice, multimedia)-Aceste modele sunt orientate pe reprezentarea semnificaţiei datelor. Structura de bază folosită pentru reprezentarea datelor este cea de clasă de obiecte definită prin abstractizare din entitatea fizică pe care o regăsim în lumea reală. Aici există entităţi simple şi clase de entităţi care se reprezintă prin obiecte simple sau clase de obiecte, ordonate în ierarhii de clase şi subclase. Acest tip de bază

3

de date a apărut din necesitatea gestionării obiectelor complexe: texte, grafice, hărţi, imagini, sunete (aplicaţii multimedia) şi a gestionării obiectelor dinamice: programe, simulări

●Modelul obiect-relational➔ Clasificare dupa numarul de utilizatori

●Sisteme mono-utilizator●Sisteme multi-utilizator

➔ Clasificare dupa numarul de statii pe care este memorată baza de date:

●Baze de date centralizate ( toate datele (si SGBD) sunt memorate pe un singur host)

●Baze de date distribuite ( atât datele cât şi SGBD-ul sunt memorate pe mai multe host-uri, conectate printr-o retea de comunicaţie)

➔ Arhitectura client-server:●Server (back-end): SGBD-ul si baza de date●Client (front-end): program (programe) de aplicatie

➔ Arhitectură locală (fişiere comune mapate în interiorul reţelei) – pentru controlul accesului se folosesc fisiere system

O baza de date trebuie să asigure: ✔ abstractizarea datelor (date unice), ✔ integrarea datelor (controlul corelaţiei datelor), ✔ integritatea datelor (respectarea restricţiile de

integritate a datelor pe parcursul operaţiilor SQL), ✔ securitatea datelor (controlul accesului la baza de

date), ✔ partajarea datelor (datele pot fi accesate de mai mulţi

utilizatori, eventual în acelaşi timp),

Un SGBD trebuie să asigure următoarele activităţi:✗ definirea şi descrierea structurii bazei de date – se

realizează printr-un limbaj propriu, limbaj de definire a datelor (LDD);

✗ încărcarea datelor în baza de date – se realizează prin comenzi în limbaj propriu(limbaj de manipulare a datelor (LMD));

✗ accesul la date – se realizează prin comenzi specifice din limbajul de manipulare a datelor (SQL). Accesul la date se referă la operaţiile de interogare şi actualizare.Interogarea este complexă şi presupune vizualizarea, consultarea, editarea de situaţii de ieşire (rapoarte, liste, regăsiri punctuale).Actualizarea presupune 3 operaţiuni: adăugare, modificare

4

efectuate prin respectarea restricţiilor de integritate ale BD şi ştergere;

✗ întreţinerea bazei de date – se realizează prin utilitare proprii ale SGBD;

✗ securitatea datelor – se referă la asigurarea confidenţialităţii datelor prin autorizarea şi controlul accesului la date pe mai multe nivele, criptarea datelor.

Arhitectura PostgreSQL

PostgreSQL utilizează un model client/server. O sesiune PostgreSQL constă în mai multe procese:

➔ Un proces server, care gestionează fişierele bazei de date, acceptă conexiuni la bazele de date dinspre aplicaţiile client, realizează actiunile cerute de client pe bazele de date. Serverul de base de date se numeşte postgres .

➔ Aplicaţia client a utilizatorului care doreşte sa execute operaţii pe baza de date. Aplicaţiile client pot fi foarte diverse: un client poate fi un utilitar linie de comandă, o aplicaţie grafică, un server web care accesează baza de date pentru a afişa pagini de web sau un utilitar specializat de gestiune a bazelor de date. Unele aplicaţii client sunt puse la dispoziţie în distribuţia PostgreSQL. Majoritatea sunt dezvoltate de utilizatori.

Ca şi în cazul altor aplicaţii client/server, clientul si server-ul pot fi pe host-uri diferite. În acest caz ele comunică prin conexiuni TCP/IP.

Serverul de PostgreSQL poate gestiona multiple conexiuni concurente de la clienţi având chiar versiuni diferite pentru PostgreSQL. Pentru aceasta el porneşte câte un nou proces pentru fiecare conexiune. Din acel punct, clientul si un proces al server-ului comunică fară intervenţia procesului iniţial postgres. Astfel procesul principal al server-ului continuă să aştepte noi clienţi.

Modul de lucru al serverului PostgreSQL

Postgresql foloseste un model client/server numit “proces per-user”. O sesiune Postgres consta din cooperarea urmatoarelor procese (programe): Un proces daemon de supervizare (postmaster), aplicatia frontend a userului (programul psql), si unul sau mai multe servere backend de baze de date (procesul postgres el insusi)

5

Un singur proces postmaster conduce o colectie de baze de date pe o singura gazda.

Aplicatiile frontend care doresc sa acceseze o baza de date printr-o instalare face apelul la librarie.

Libraria trimite cererea userului prin retea la postmaster. Paşii prin care trece o cerere SQL pentru a se obţine un rezultat:

1.O conexiune dinspre o aplicaţie la server-ul de PostgreSQL a fost stabilită. Clientul trimite o cerere către server şi aşteaptă să primească rezultatele trimise de server (aplicatia frontend trimite o cerere la postmaster prin soket-ul de retea). Când o conexiune este stabilita, aceasta porneste un nou proces server backend si conecteaza procesul frontend la un nou server. Din acel punct, procesul frontend si serverul backend comunica fara interventia postmasterului. Cu toate ca, postmasterul merge intotdeauna, asteptand cereri, procesele frontend si backend vin si pleaca. Libraria libpq permite unui singur frontend sa faca multiple conexiuni la procesele backend. Aplicatia frontend este un singur fir de executie suportat prin intermediul libpq.

postmasterul creaza un server backend (porneste un nou proces server backend si conecteaza procesul frontend la un nou server).

2.Parser-ul verifică cererea trimisă de aplicaţie si corectitudinea sintaxei şi creează un query tree.

3.Sistemul de rescriere preia query tree-ul creat de parser şi caută eventualele reguli (depozitate în cataloagele sistemului) care trebuie aplicate query tree-ului. Acesta aplică transformarile descrise în regulile respective.

4.Planificatorul/Optimizatorul preia query tree-ul rescris si creează un plan de execuţie al cererii care va fi de fapt intrarea pentru sistemul de execuţie a comenzilor.

5.Server-ul execută recursiv plan tree-ul.

Postmasterul si backendul ruleaza pe o aceeasi masina

(serverul de date), in timp ce aplicatia frontend poate rula

oriunde.

Limbajul SQL(Structured Query Language)

SQL(Structured Query Language) este limbajul de baze de date cel mai frecvent utilizat iar cele mai utilizate sunt

6

standardele SQL92(DBase, FoxPro, MSAcces, Paradox, etc) şi SQL2003(PostgreSQL - OpenSource, Oracle, MS-SQL, etc) . Tipurile de baze de date vor fi detaliate în cursul “Arhitecturi SGBD”.

Limbajul SQL este “case insensitive” (nu ţine cont de litere mari şi litere mici), însă pentru anumite denumiri pot exista reguli specifice fiecărei baze de date.

7

CREAREA BAZELOR DE DATE, A SESIUNILOR ŞI A UTILIZATORILOR.

CREARE TABELE CU DIVERSE TIPURI DE CÂMPURI;

1-1 TABELE (fără legături între ele)1-1-1 Definire tabele

Tabelele sunt entităţi logice de reprezentare a informaţiei stocate asemănătoare foilor de calcul tabelare. Pentru bazele de date avansate ele nu au o corespondenţă fizică cu un anumit fişier. Coloanele dintr-un tabel corespund câmpurilor (fields) din cadrul bazelor de date iar rândurile din tabel corespund înregistrărilor (records). Crearea unei baze de date începe cu definirea şi crearea tabelelor necesare.1-1-2 Tipuri de date predefinite, frecvent utilizate

TIPURI DE DATE FRECVENT UTILIZATE

Baze de date ce au la bază SQL92

Ex: PostgreSQL

Ex:Alias pgSQL

Descriere

bigint int8 întreg, cu semn pe 8 octeţi

boolean boolean bool logic (True sau False)

bytes[n] bytea

stocare binară (utilizată inclusiv pentru stocarea imaginilor în diverse formate sau a metafişierelor)

character(n)

character varying [ (n) ]

varchar [ (n) ] şir cu maxim n caractere

character [ (n) ]

char [ (n) ] şir cu exact n caractere

date date calendar date (year, month, day)

double precision float8 real, precizie 15 dublă

inet IPv4 sau IPv6 adresă gazdă (host address)

integer integer int, int4 întreg, cu semn pe 4 octeţi

8

macaddr adresă MAC

money money “currency “(monedă)

float(p,s)numeric [ (p, s) ]

decimal [ (p, s) ]

numeric cu precizie prestabilită

real float4 real, precizie simplă

smallint smallint int2 intreg pe 2 octeţi

autoinc serial serial4autoincrementare, intreg 4 octeţi

bigserial serial8autoincrementare, intreg 8 octeţi

text şir de caractere cu lungime variabilă

time time [ (p) ] timp (ore,minute,secunde, ms)

timestamptimestamp [ (p) ] dată şi timp împreună

xml Date format XML

9

1-1-3 Creare tabele Ex:

Sintaxa (simplificată):

Notă: parantezele drepte din expresie indică o expresie opţională iar acoladele o secvenţă ce se repetă şi este separată prin virgulă. De asemenea simbolul “|” semnifică “sau”.Iar column_constraint [ ... ] este :

unde:✔ PRIMARY KEY reprezintă cheia primară de ordonare(câmpul

sau câmpurile după care se face ordonarea)✔ NOT NULL semnifică faptul că nu se acceptă valori vide

(fără date) pentru câmpul respectiv✔ CHECK permite verificări suplimentare (de exemplu:

temp_max<50)Ex: Crearea unui tabel cu denumirea parm in cadrul schemei c2, câmpul data este de tipul dată calendaristică și va fi inițializat cu data curentă atunci când se adaugă o nouă înregistrare, la fel se procedează și pentru câmpul moment, câmp ce contine ora, minutul secunda și fractiunea de secundă. Tabelul conține valorile măsurate dintr-un proces.

Notă: Câmpul sau câmpurile care constituie cheia primară nu pot avea valori NULL

1-1-4 Stergere tabele

10

CREATE TABLE c2.parm(data date DEFAULT CURRENT_DATE, moment time DEFAULT CURRENT_TIME,modul varchar(10), pct varchar(50),par varchar(15),val numeric,um char(10), idx serial,CONSTRAINT k_parm PRIMARY KEY(idx));

CREATE TABLE c2.parm(data date DEFAULT CURRENT_DATE, moment time DEFAULT CURRENT_TIME,modul varchar(10), pct varchar(50),par varchar(15),val numeric,um char(10), idx serial,CONSTRAINT k_parm PRIMARY KEY(idx));

CREATE [TEMPORARY|TEMP] TABLE table_name ([{ column_name data_type [ column_constraint [ ... ] ]}])

CREATE [TEMPORARY|TEMP] TABLE table_name ([{ column_name data_type [ column_constraint [ ... ] ]}])

CREATE TABLE vremea(oras varchar(80),temp_min int,temp_max int,precipitatii real,data date);CREATE TABLE vremea(oras varchar(80),temp_min int,temp_max int,precipitatii real,data date);

[ CONSTRAINT constraint_name ] {NOT NULL|NULL|UNIQUE index_parameters| PRIMARY KEY index_parameters | CHECK ( expression )}

[ CONSTRAINT constraint_name ] {NOT NULL|NULL|UNIQUE index_parameters| PRIMARY KEY index_parameters | CHECK ( expression )}

CREATE TABLE vremea2(oras varchar(80) NOT NULL, temp_min int,temp_max int CHECK(temp_max<50) ,precipitatii real,data date PRIMARY KEY);

CREATE TABLE vremea2(oras varchar(80) NOT NULL, temp_min int,temp_max int CHECK(temp_max<50) ,precipitatii real,data date PRIMARY KEY);

Ex: Sintaxa:

1-1-5 Creare indecsiTabelele pot fi indexate în scopul obţinerii unor date ordonate după unul sau mai multe criterii (de obicei se ordonează după unul sau mai multe câmpuri). Indexarea NU schimbă ordinea fizică a înregistrărilor. Prin definirea indecsilor se crează automat tabele de corespondenţă dintre ordinea fizică a înregistrărilor şi cea logică (corespunzătoare ordonării).

✗ simpli, ex:

✗ multipli, ex:

✗ parţiali, ex:

Ordonarea dată de indecşi poate fi crescătoare (ASC – implicit) sau descrescătoare (DESC) – în exemplul dat, câmpul data va fi ordonat crescător iar câmpul orașul va fi ordonat descrescător


unde: ✔ ASC / DESC semnifică tipul de ordonare

ascendentă/descendentă ;✔ NULL FIRST → valorile NULL (vide) sunt puse la început

iar (implicit pentru ASC);✔ NULL LAST → valorile NULL (vide) sunt puse la sfârşit

(implicit pentru DESC). 1-1-5 Stergere indecsi

Ex:


11

DROP vremeaDROP vremea DROP TABLE tablenameDROP TABLE tablename

CREATE INDEX indexptrVremea ON vremea(data)CREATE INDEX indexptrVremea ON vremea(data)

CREATE INDEX indexptrVremea2 ON vremea(data,oras)CREATE INDEX indexptrVremea2 ON vremea(data,oras)

CREATE INDEX indexptrVremea3 ON vremea(data,oras) WHERE (data<'2.1.2008')CREATE INDEX indexptrVremea3 ON vremea(data,oras) WHERE (data<'2.1.2008')

CREATE INDEX indexptrVremea4 ON vremea(data, oras DESC)CREATE INDEX indexptrVremea4 ON vremea(data, oras DESC)

CREATE[UNIQUE]INDEX name ON table(column[ASC|DESC ] [ NULLS { FIRST | LAST } ]) [WHERE predicate]CREATE[UNIQUE]INDEX name ON table(column[ASC|DESC ] [ NULLS { FIRST | LAST } ]) [WHERE predicate]

DROP indexptrVremeaDROP indexptrVremea

DROP INDEX [ IF EXISTS ] nameDROP INDEX [ IF EXISTS ] name

INTEROGAREA BAZEI DE DATE

(cazul unui singur tabel)

1-2-1 PRELUAREA DATELOR DIN TABEL

Preluarea datelor din tabel (interogarea bazei de date) se realizează prin utilizarea instrucţiunii SELECT. Notă: Nu contează dacă codul SQL este scris pe un singur rand sau pe mai multe rânuri şi nici dacă între cuvinte există un spaţiu sau mai multe. Se recomandă scriere astfel încât codul să se citească cât mai uşor.Ex: preia întregul tabel vremea

( * specifică preluarea tuturor câmpurilor )

Ex: afișează toate câmpurile și toate înregistrările din tabel

Ex: preia doar câmpurile specificate în interogare

✗ preia numai ce îndeplineşte condiţiilor din cadrul “WHERE” (condiţii la nivel de înregistrare)

✗ ordonează date descrescător (ex:data) şi crescător (ex; oras)

✗ grupează datele după anumite câmpuri în vederea realizării anumitor operaţii

(ex. calc. mediei temp. min.)

✗ aplică condiţii la nivel de grup

(ex: doar orasele cu media temp. min >5)

12

SELECT * FROM vremeaSELECT * FROM vremea

SELECT * FROM c2.parm ORDER BY idx;SELECT * FROM c2.parm ORDER BY idx;

SELECT oras, data FROM vremeaSELECT oras, data FROM vremea

SELECT oras, data, precipitatii FROM vremea WHERE((temp_min>=0)AND(temp_max<10))OR(precipitatii>0)ORDER BY data DESC, oras

SELECT oras, data, precipitatii FROM vremea WHERE((temp_min>=0)AND(temp_max<10))OR(precipitatii>0)ORDER BY data DESC, oras

SELECT oras, avg(temp_min) FROM vremea GROUP BY orasSELECT oras, avg(temp_min) FROM vremea GROUP BY oras

✗ aplică condiţii/restricţii atât la nivel de grup cât şi la nivel de înregistrare

Notă: Toate câmpurile din cadrul SELECT trebuie să se regăsească în GROUP BY, altfel nu este posibilă

gruparea acestora ✗ efectuează operaţii între câmpuri şi afişează rezultatul

într-un câmp nou

✗ întoarce un număr maxim de înregistrări (ex: primele 10 obţinute în urma ordonării)


1-2-2 Funcţii ce pot fi aplicate asupra câmpurilor în cadrul SELECT (selecţie)

Funcţia Descriere

avg(expresie) Media aritmetică a expresiei

count(*) Numărul de înregistrări

count(expresi Numărul de înregistrări pentru care valoarea

13

SELECT oras, avg(temp_min) FROM vremea GROUP BY oras HAVING avg(temp_min)>5SELECT oras, avg(temp_min) FROM vremea GROUP BY oras HAVING avg(temp_min)>5

SELECT oras, avg(temp_min) WHERE data > '20.11.2007' FROM vremea GROUP BY oras HAVING avg(temp_min)>5SELECT oras, avg(temp_min) WHERE data > '20.11.2007' FROM vremea GROUP BY oras HAVING avg(temp_min)>5

SELECT oras, temp_min, temp_max, (temp_min+temp_max)/2.0 AS media FROM vremea SELECT oras, temp_min, temp_max, (temp_min+temp_max)/2.0 AS media FROM vremea

SELECT oras, data, precipitatii FROM vremea ORDER BY data,oras LIMIT 10SELECT oras, data, precipitatii FROM vremea ORDER BY data,oras LIMIT 10

SELECT [ ALL | DISTINCT ] * | expression [ AS output_name ] [ FROM from_item [, ...] ] [ WHERE condition ] [ GROUP BY expression [, ...] ] [ HAVING condition [, ...] ] [ ORDER BY expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ]] [ LIMIT { count | ALL } ]

SELECT [ ALL | DISTINCT ] * | expression [ AS output_name ] [ FROM from_item [, ...] ] [ WHERE condition ] [ GROUP BY expression [, ...] ] [ HAVING condition [, ...] ] [ ORDER BY expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ]] [ LIMIT { count | ALL } ]

e) expresiei nu este nulă

max(expresie) Valoarea maximă pentru expresie

min(expresie) Valoarea minimmă pentru expresie

sum(expresie) Suma expresiei

Notă: (expresie) poate fi un câmp sau rezultatul unor operaţii aplicate mai multor câmpuri

Exemplu:

CREATE TABLE c2.parm(data date DEFAULT CURRENT_DATE,moment time DEFAULT CURRENT_TIME,modul varchar(10), pct varchar(50),par varchar(15),val numeric,um char(10),idx serial, CONSTRAINT k_parm PRIMARY KEY(idx));

select * FROM c2.parm order by idx;

INSERT INTO c2.parm(modul,pct,par,val,um) VALUES ('M1','P1','par1',123.4,'V'),('M3','P4','par1',13.4,'V'),('M2','P1','par1',153.4,'V'),('M1','P2','par2',1.4,'V'),('M1','P2','par3',53.4,'V'),('M2','P1','par2',31.4,'V'),('M4','P1','par1',3.14,'A');

--scade 5 zile pentru data in cazul in care 10<val<100;UPDATE c2.parm SET data=data-5 WHERE val>10 and val <100;

--afiseaza valorile medii zilnice ptr. fiecare modul-pct-parSELECT data,modul,pct,par, AVG(val) as valm FROM c2.parm GROUP BY data,modul,pct,par;

-- afiseaza valorile medii la nivel de modul

SELECT modul,um, AVG(val) as valm FROM c2.parm GROUP BY modul,um;

--afisare module ce au valori >50

SELECT modul,val FROM c2.parm WHERE val>50

--afiseaza modulele pentru care valorile medii sunt >10

SELECT modul,AVG(val) as valm FROM c2.parm GROUP BY modul HAVING AVG(val)>10

--afiseaza modulele pentru care valorile medii sunt >10, iar valorile componente sunt <120SELECT modul,AVG(val) as valm FROM c2.parm WHERE val<120 GROUP BY modul HAVING AVG(val)>10

14

--cate masuratori au valorile intre 20 si 100?SELECT count(*) FROM c2.parm WHERE val>20 and val <100

--afiseaza numarul de parametrii identici pentru fiecare modul

SELECT modul,par,count(par) as nr FROM c2.parm GROUP BY modul,par;

--INSERT INTO c2.parm(modul,pct,par,val,um,idx) VALUES ('M4','P0','par1',123.4,'V',8)

--select modul,pct,par, sum(val) as suma FROM c2.parm GROUP BY modul,pct,par;

-----inserrare inregistrare cu data calendaristica

INSERT INTO c2.parm(data,modul,pct,par,val) VALUES(to_date('31.12.2012','dd.mm.yyyy'),'MY','p','par3',65.5);--sauINSERT INTO c2.parm(data,modul,pct,par,val) VALUES('2011-12-31','MY','p','par3',65.5);

15

POPULAREA BAZEI DE DATE

Popularea bazei de date cu înregistrări noi se poate realiza în două moduri:

Prin utilizarea cuvântului cheie INSERT

Prin utilizarea cuvântului cheie COPY

1-3-1 Adăugarea de înregistrări ( INSERT )

1-3-1 -1 Adăugarea unei singure înregistrări

Ex

În acest caz, tipul şi ordinea valorilor introduse trebuie să corespundă cu cea din CREATE TABLE

Exemplul de mai sus se mai poate scrie:

Ex:

În acest caz, tipul şi ordinea valorilor introduse în “vremea()” trebuie să corespundă cu cea din “VALUES()”

Se pot adăuga doar anumite câmpuri din tabel (se adaugă obligatoriu câmpurile din cheia primară – excepţie fac câmpurile de tip autoincrementare )

Ex:

1-3-1 -2 Adăugarea mai multor înregistrări, explicit

16

INSERT INTO vremea VALUES (’Bacau’, -20, 42, 0.25, ’30.12.2007’);INSERT INTO vremea VALUES (’Bacau’, -20, 42, 0.25, ’30.12.2007’);

INSERT INTO vremea(oras,temp_min,temp_max, precipitatii,data) VALUES (’Bacau’, -20, 42, 0.25, ’30.12.2007’);

INSERT INTO vremea(oras,temp_min,temp_max, precipitatii,data) VALUES (’Bacau’, -20, 42, 0.25, ’30.12.2007’);

INSERT INTO vremea(oras,precipitatii,data) VALUES (’Bacau’, 0.25, ’30.12.2007’);INSERT INTO vremea(oras,precipitatii,data) VALUES (’Bacau’, 0.25, ’30.12.2007’);

INSERT INTO vremea(oras,precipitatii,data) VALUES (’Bacau’, NULL, ’30.12.2007’);INSERT INTO vremea(oras,precipitatii,data) VALUES (’Bacau’, NULL, ’30.12.2007’);

INSERT INTO vremea(oras,precipitatii,data) VALUES (’Bacau’, 0.25, ’30.12.2007’), (’Iasi’, NULL, ’31.12.2007’), (’Caracal’, 0.11, ’29.12.2007’);

INSERT INTO vremea(oras,precipitatii,data) VALUES (’Bacau’, 0.25, ’30.12.2007’), (’Iasi’, NULL, ’31.12.2007’), (’Caracal’, 0.11, ’29.12.2007’);

1-3-1 -3 Adăugarea mai multor înregistrări, dintr-un alt tabel

Presupunem că există un alt tabel denumit vremea2 cu o structură asemănătoare cu primul tabel (vreamea)

Ex:

În cazul în care cele două tabele au aceeaşi ordine şi aceleaşi tipuri de date se poate scrie

Ex:

sau

Ex:

Sintaxa simplificată:

1-3-2 Copierea datelor în tabel utilizând comada COPY (importul datelor)

Se pot copia fişiere întregi cu date într-un tabel. Aceste fişiere, de regulă, sunt fişiere format CSV (comma-separated values ) - fişiere text având valorile separate prin virgulă, iar valorile ce conţin caracterul “,” sunt scrise între ghilimele.

Ex:

În acest exemplu tabelul vremea este populat cu datele din

17

INSERT INTO vremea(oras,temp_min,temp_max, precipitatii,data) SELECT oras,temp_min,temp_max,precipitatii, data FROM vremea2 WHERE data > '20.11.2007';

INSERT INTO vremea(oras,temp_min,temp_max, precipitatii,data) SELECT oras,temp_min,temp_max,precipitatii, data FROM vremea2 WHERE data > '20.11.2007';

INSERT INTO vremea(oras,temp_min,temp_max ,precipitatii,data) SELECT * FROM vremea2 WHERE data > '20.11.2007';

INSERT INTO vremea(oras,temp_min,temp_max ,precipitatii,data) SELECT * FROM vremea2 WHERE data > '20.11.2007';

INSERT INTO vremea SELECT * FROM vremea2 WHERE data > '20.11.2007';INSERT INTO vremea SELECT * FROM vremea2 WHERE data > '20.11.2007';

INSERT INTO table [ ( column [, ...] ) ] { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }

INSERT INTO table [ ( column [, ...] ) ] { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }

COPY vremea FROM ’C:/test/tempvremea.csv’ WITH csv;COPY vremea FROM ’C:/test/tempvremea.csv’ WITH csv;

fişierul tempvremea.csv.


unde:

✗ DELIMITER specifică un alt delimitator între valorile din fişierul CSV (implicit fiind virgula)

✗ NULL specifică sirul ce reprezintă valoarea NULL (implicit nu se scrie nimic – adică după virgulă urmează tot virgulă)

✗ CSV specifică faptul că este vorba de un fişier format CSV (pot exista transferuri şi cu fişiere binare sau alte fişiere text – aceste cazuri nu sunt tratate în prezentul curs)

✗ HEADER specifică dacă primul rând din cadrul fişierului format CSV este reprezentat de numele coloanelor/ câmpurilor

✗ QUOTE specifică caracterele între care sunt puse valorile ce conţin şi caracterul separator (implicit caracterul “).

De obicei, această comandă este utilizată la preluarea iniţială a datelor, date existente în alte baze de date sau în fişiere de tip XLS / text. Comanda COPY adaugă noile date la cele vechi. De asemenea, în cursurile următoare se va detalia utilizarea comenzii COPY pentru exportul datelor în format CSV.

1-3-3 Ştergerea înregistrărilor dintr-un tabel

Ex:

şterge toate înregistrările din tabelul vremea.

Ex:

18

COPY tablename [ ( column [, ...] ) ] FROM ’filename’ [[ WITH ] [ DELIMITER [ AS ] ’delimiter’ ] [ NULL [ AS ] ’null string’ ] [ CSV [ HEADER ] [ QUOTE [ AS ] ’quote’ ]

COPY tablename [ ( column [, ...] ) ] FROM ’filename’ [[ WITH ] [ DELIMITER [ AS ] ’delimiter’ ] [ NULL [ AS ] ’null string’ ] [ CSV [ HEADER ] [ QUOTE [ AS ] ’quote’ ]

DELETE FROM vremea ;DELETE FROM vremea ;

DELETE FROM vremea WHERE data < '20.11.2007';DELETE FROM vremea WHERE data < '20.11.2007';

şterge toate înregistrările anterioare datei specificate


unde

✗ ONLY este utilizat în cazul tabelelor ierarhizate pentru a nu şterge şi înregistrările din “tabelele copil” ce depind de tabelul respectiv

✗ USING specifică alte tabele utilizate în condiţia WHERE

Ex: presupunem existenţa unui nou tabel municipii

Ex

:

şi ştergem toate înregistrările din vremea care apar şi în tabelul municipii

Ex:

1-3-4 Actualizarea datelor dintr-un tabel

Comanda UPDATE actualizează valorile din tabelul dat, pentru înregistrările rezultate în urma aplicării condiţiei din cadrul WHERE, cu valorile şi pentru câmpurile explicitate în secţiunea SET

Ex:

Sunt modificate doar valorile câmpurilor date de SET pentru înregistrările date de WHERE


19

DELETE FROM [ ONLY ] table [ [ AS ] alias ][ USING usinglist ][ WHERE condition ]DELETE FROM [ ONLY ] table [ [ AS ] alias ][ USING usinglist ][ WHERE condition ]

CREATE TABLE municipii (denumire_oras character varying(80) NOT NULL, populatie integer, suprafata numeric)

CREATE TABLE municipii (denumire_oras character varying(80) NOT NULL, populatie integer, suprafata numeric)

DELETE FROM vremea USING municipii WHERE vremea.oras=municipii.denumire_orasDELETE FROM vremea USING municipii WHERE vremea.oras=municipii.denumire_oras

UPDATE vremea SET temp_min = temp_min-1, temp_max = temp_min+20, precipitatii = 0WHERE (oras = 'Iasi') AND (data > '1.07.2007') AND (data < '31.08.2007');

UPDATE vremea SET temp_min = temp_min-1, temp_max = temp_min+20, precipitatii = 0WHERE (oras = 'Iasi') AND (data > '1.07.2007') AND (data < '31.08.2007');

UPDATE [ ONLY ] table [ [ AS ] alias ]SET { column = { expression | DEFAULT } } [, ...][ FROM fromlist ] [ WHERE condition ]

UPDATE [ ONLY ] table [ [ AS ] alias ]SET { column = { expression | DEFAULT } } [, ...][ FROM fromlist ] [ WHERE condition ]

unde:

✗ ONLY este utilizat în cazul tabelelor ierarhizate pentru a nu modifica şi înregistrările din “tabelele copil” ce depind de tabelul respectiv

✗ FROM specifică alte tabele utilizate în condiţia WHERE

✗ DEFAULT – preia valorile implicite sau NULL dacă nu sunt specificate

Ex:

Exemplu:

--mai multe tabele:--in tabel cu intrarile--st tabel cu stocurile--bp tabel cu rezervarile (borderou de preparare) --- tabelul se transforma in bl--bl tabel cu livrarile--tr tabel cu transf. intre magazii --dif tabel cu diferentele aparute --nom tabel nomenclator

CREATE TABLE c3.in(id serial, sn varchar(20),cod varchar(20),furnizor varchar(50), cda varchar(10), data date DEFAULT CURRENT_DATE,qty numeric, um varchar(10),dep varchar(10),amp varchar(10),CONSTRAINT k_in PRIMARY KEY(id));CREATE TABLE c3.st(dep varchar(10),amp varchar(10),id int,qtyfiz numeric,qtydisp numeric, CONSTRAINT k_st PRIMARY KEY(dep,amp,id));create table c3.bp(nrbp int,dep varchar(10),amp varchar(10),id int,qty numeric, data date DEFAULT CURRENT_DATE,dest varchar(50), CONSTRAINT k_bp PRIMARY KEY(nrbp,dep,amp,id));

CREATE table c3.bl(nrbl int,dep varchar(10),amp varchar(10),id int,qty numeric, data date DEFAULT CURRENT_DATE,dest varchar(50), CONSTRAINT k_bl PRIMARY KEY(nrbl,dep,amp,id));create table c3.tr(data date DEFAULT CURRENT_DATE,depsrs varchar(10),ampsrs varchar(10), depdest varchar(10),ampdest varchar(10),id int, qty

20

UPDATE vremea SET temp_min = temp_min-1, temp_max = temp_min+20, precipitatii = DEFAULTFROM municipiiWHERE(vremea.oras = municipii.denumire_oras )AND(data>'1.07.2007')AND(data<'31.08.2007');

UPDATE vremea SET temp_min = temp_min-1, temp_max = temp_min+20, precipitatii = DEFAULTFROM municipiiWHERE(vremea.oras = municipii.denumire_oras )AND(data>'1.07.2007')AND(data<'31.08.2007');

numeric,idx serial, CONSTRAINT k_tr PRIMARY KEY(idx));create table c3.dif(data date DEFAULT CURRENT_DATE, dep varchar(10),amp varchar(10), id int,qty numeric,idx serial, CONSTRAINT k_dif PRIMARY KEY(idx)); create table c3.nom(cod varchar(20),descriere varchar(100),ref_tehnica varchar(50), ref_comerciala varchar(50),CONSTRAINT k_nom PRIMARY KEY(cod));

INSERT INTO c3.in(sn,cod,furnizor,cda,qty,um,dep,amp) VALUES('s1','cod1','furnizor1',NULL,1,'buc','A1','A101'), ('s12','cod14','furnizor1A',NULL,1,'buc','A1','A101'), ('s13','cod1','furnizor1',NULL,1,'buc','A1','A101'), (NULL,'cod01','furnizor1',NULL,5,'buc','A1','A102'), (NULL,'cod02','furnizor1',NULL,10,'buc','A1','A101'), (NULL,'cod1','furnizor1',NULL,1,'buc','A1','A101');select * from c3.in ALTER TABLE c3.in ADD COLUMN br int; update c3.in set br=1 where data='2011-03-11';

21

LUCRUL CU DATE DE TIP XML

EXEMPLE COMANDATE

create schema c5;

CREATE TABLE c5.t1(a serial PRIMARY KEY, b xml);

-- adauga un xml cu un singur nod

--continutul XML este delimitat de caracterele

apostrof(dreapta)

INSERT INTO c5.t1(b) VALUES ('<A0> UN NOD </A0>');

-- PostgreSQL verifica /valideaza fiecare data XML adaugata

-- adauga un xml cu un nod si doua atribute

INSERT INTO c5.t1(b) VALUES ('<A0 m="123" n="76"> UN TEST

</A0>');

select * FROM c5.t1;

-- adauga un xml cu un nod parinte si noduri copil

INSERT INTO c5.t1(b) VALUES ('<A0> <A11/> <A12>Al doilea

rand</A12> </A0>');

select * from c5.t1;

--adauga un XHTML

INSERT INTO c5.t1(b) VALUES ('<HTML> <br/>

<TABLE>

<tr><td>A11</td><td>A12</td></tr>



</TABLE> </HTML>');

22

-- creaza un element de tip XML cu un singur nod

SELECT xmlelement(name A0);

--CAUTARE IN STRUCTURI DE TIP XML

-- se utilizeaza functia xpath, functie ce preaia calea +

numele nodului si campul pe care se face cautarea

-- functia intoarce structura XML gasita pentru calea+nodul

date.

-- afiseaza arborele/valoarea nodului A0, din cadrul campului

b

SELECT xpath('//A0',b) FROM c5.t1;

--afiseaza valoarea nodului A0, din cadrul campului b ca si

text (sir de caractere)

-- daca nodul este nod parinte se afiseaza un sir vid

SELECT xpath('//A0/text()',b) FROM c5.t1;

SELECT xpatth('//HTML',b) FROM c5.t1;

--afisare XML care sa aiba o anumita valoare a nodurilor

-- prin utilizarea CAST structura de tip nod intoarsa de

catre xpath este covertita la tipul text,

-- in acest mod fiind posibila utilizarea instructiunii ILIKE

-- cauta toate nodurile cu numele A12 din campul b ce contin

in cadrul valorii subsirul "ra"

--afisare XML ca sir

SELECT xpath('//A12/text()',b) FROM c5.t1 WHERE

CAST(xpath('//A12/text()',b) as text) ILIKE '%ra%';

//afisare XML simpla

23

SELECT xpath('//A12',b) FROM c5.t1 WHERE

CAST(xpath('//A12/text()',b) as text) ILIKE '%ra%';

--in cazul in care cautarea gaseste mai multe noduri cu acel

nume, intr-o sinfura inregistrare

-- si intr-un singur camp, acesta le va pune (XML-urile)

intr-un vector {}

SELECT xpath('//td',b) FROM c5.t1;

-- si in caz rezultatele pentru aparitia multipla a nodurilor

vor aparea intr-un vector

SELECT xpath('//td/text()',b) FROM c5.t1;

-- nu are nici un efect suplimentar (doar timpul de cautare

difera)

SELECT CAST(xpath('//td/text()',b) as text) FROM c5.t1;

CREATE TABLE c5.t2(a serial PRIMARY KEY, b xml);

-- adauga un xml cu un singur nod

INSERT INTO c5.t2(b) VALUES ('<A0> UN NOD </A0>');

-- adauga un xml cu un nod si doua atribute

INSERT INTO c5.t2(b) VALUES ('<A0 m="123" n="76"> UN TEST

</A0>');

-- adauga un xml cu un nod parinte si noduri copil

INSERT INTO c5.t2(b) VALUES ('<A0> <A11/> <A12>Al doilea

rand</A12> <A13/> </A0>');

-- comparatie intre noduri

---SELECT * FROM c5.t1,c5.t2 WHERE t1.b::xml=t2.b::xml;

-- simbolul (xml)=(xml) nu este inca definit (8.3)

24

--SELECT * FROM c5.t1,c5.t2 WHERE

xpath('//A0',t1.b)=xpath('//A0',t2.b);

-- intoarce xml si nu inca este definit operator = pentru xml

--SELECT * FROM c5.t1,c5.t2 WHERE

xpath('//A0/text()',t1.b)=xpath('//A0/text()',t2.b);

-- /text() nu este schivalent cu un CAST la tipul text

-- comparatia se face la nivel text

SELECT * FROM c5.t1,c5.t2 WHERE CAST(xpath('//A0/text()',t1.b)

as text)=CAST(xpath('//A0/text()',t2.b) as text);

25

COMBINAREA INTEROGĂRILOR

a) Uniune

Implicit (DISTINCT), prin uniunea celor două interogări, înregistrările multiple, identice, vor apărea o singură dată. Prin utilizarea comenzii ALL acestea nu vor mai fi eliminate.

Ex:

Putem avea :

Denumirea câmpului rezultat va fi cea din prima interogare

b) Intersecţie

Prin utilizarea comenzii INTERSECT vor fi selectate doar înregistrările comune din cadrul interogărilor

Ex:

c) Diferenţă

Prin utilizarea comenzii EXCEPT vor fi selectate doar înregistrările care se regăsesc în rezultatul primei interogări dar care nu se regăsec şi în rezultatul celei de a doua interogări.

26

interogare#1 UNION [ALL] interogare#2interogare#1 UNION [ALL] interogare#2

SELECT oras FROM vremea UNION SELECT denumire_oras FROM municipii

SELECT oras FROM vremea UNION SELECT denumire_oras FROM municipii

interogare#1 UNION interogare#2 ... UNION interogare#ninterogare#1 UNION interogare#2 ... UNION interogare#n

interogare#1 INTERSECT [ALL] interogare#2interogare#1 INTERSECT [ALL] interogare#2

SELECT oras FROM vremea INTERSECT SELECT denumire_oras FROM municipiiSELECT oras FROM vremea INTERSECT SELECT denumire_oras FROM municipii

interogare#1 EXCEPT [ALL] interogare#2interogare#1 EXCEPT [ALL] interogare#2

Ex:

În toate cele trei cazuri câmpurile din cadrul selecţiilor trebuie să fie de acelasi timp, altfel fiind necesară convertirea acestora la tipul respectiv prin utilizarea operatorului CAST.

1-5 Operatorul CAST

Este utilizat pentru convertirea tipului datelor

Ex:

Ex:

Ex:

Ex:


27

SELECT oras FROM vremea EXCEPT SELECT denumire_oras FROM municipii

SELECT oras FROM vremea EXCEPT SELECT denumire_oras FROM municipii

SELECT 3.21 AS "numeric" UNION SELECT 1;SELECT 3.21 AS "numeric" UNION SELECT 1;

SELECT 1 AS "numeric" UNION SELECT CAST('3.14' AS numeric);


SELECT 'Bacau' AS "varchar(20)" UNION SELECT CAST('3.14' AS varchar(20));


SELECT oras, cast(temp_min as varchar(10)) AS temp_min_ch FROM vremea SELECT oras, cast(temp_min as varchar(10)) AS temp_min_ch FROM vremea

CAST ( expression AS type )CAST ( expression AS type )

CONDIŢII DE SELECŢIE A DATELOR

Este utilizat pentru convertirea tipului datelor

Ex:

Ex:

Ex:

Ex:


28

SELECT 3.21 AS "numeric" UNION SELECT 1;SELECT 3.21 AS "numeric" UNION SELECT 1;





SELECT oras, cast(temp_min as varchar(10)) AS temp_min_ch FROM vremea SELECT oras, cast(temp_min as varchar(10)) AS temp_min_ch FROM vremea


EXPRESII CONDIŢIONALE


Ex:

Rezultatul expresiei este pus în noul câmp evaluare.

-- TRANZACTIONS SQL --DROP TABLE c10.t1;CREATE TABLE c10.t1(a serial PRIMARY KEY, b int);

INSERT INTO c10.t1(b) VALUES (5),(7),(8);BEGIN;INSERT INTO c10.t1(b) VALUES (9),(10),(11); --SAVEPOINT abc;--se simuleaza o eroareINSERT INTO c10.t1(b) VALUES (12),(13),(a14);--ROLLBACK TO abc;INSERT INTO c10.t1(b) VALUES (15),(15),(17);UPDATE c10.t1 SET b=b+1000;COMMIT;--select * FROM c10.t1;

-- atentie la BEGIN -- nu se va selecta in blocul de sus, ptr. executie-- raman primele trei inreg nemodificate !

29

CASE WHEN condition THEN result [WHEN ...] [ELSE result]END

CASE WHEN condition THEN result [WHEN ...] [ELSE result]END

SELECT (temp_min+temp_max)/2.0 as temp_medie, CASE WHEN((temp_min+temp_max)/2.0 )<=0 THEN 'GER' WHEN(((temp_min+temp_max)/2.0 )>0)AND (((temp_min+temp_max)/2.0 )<15) THEN 'FRIG' WHEN ((temp_min+temp_max)/2.0 )>30 THEN 'CALD' ELSE 'NORMAL' END AS evaluareFROM vremea

SELECT (temp_min+temp_max)/2.0 as temp_medie, CASE WHEN((temp_min+temp_max)/2.0 )<=0 THEN 'GER' WHEN(((temp_min+temp_max)/2.0 )>0)AND (((temp_min+temp_max)/2.0 )<15) THEN 'FRIG' WHEN ((temp_min+temp_max)/2.0 )>30 THEN 'CALD' ELSE 'NORMAL' END AS evaluareFROM vremea

TIPURI DE ASOCIERI ÎNTRE ÎNREGISTRĂRILE UNOR TABELE

Într-o bază de date relaţională tabelele sunt corelate, pentru ca datele memorate în tabele diferite să poată fi asociate corect atunci când din baza de date se solicită anumite informaţii. Se pot realiza asocieri datele din tabele după criterii logice şi de înrudire a datelor. Asocierile sunt posibile în faza de definire a structurii tabelelor. Câmpurile comune prin care se face corelarea sunt date de cheia primară pentru un tabel şi respectiv chei externe pentru tabelele asociate.

Orice tabel cuprine unul sau mai multe câmpuri, care intră în componenţa unei chei primare, utilizată pentru diferenţierea unei înregistrări de celelalte. Asocierea a două tabele (tabel părinte şi tabel copil) se face printr-un câmp special cu o trimitere la cheia primară a tabelului subordonat (tabelul copil).

O bază de date poate fi formată din mai multe tabele având diferite legături între acestea. Tipurile de legături dintre două tabele pot fi:

● asocierea( legătura ) de tip unu la unu (1:1) – unei înregistrări dintr-un tabel îi corespunde o singură înregistrare în cealalt tabel;

● asocierea( legătura ) de tip unu la mai mulţi (1:M) – unei înregistrări dintr-un tabel îi corespund mai multe înregistrări în cealalt tabel;

● asocierea( legătura ) de tip mulţi la mulţi (M:N) – mai multor înregistrări dintr-un tabel îi corespund mai multe înregistrări în cealalt tabel.

Asocierea( legătura ) de tip unu la unu (1:1)

Înregistrările din două tabele se află în asocierea unu la unu dacă unei înregistrări dintr-un tabel îi corespunde (sau nu) o singură înregistrare din celălalt tabel. Legatura dintre cele doua tabele se face pe baza cheilor primare.

Acest tip de asociere este utilizată mai rar. Există, totuşi, cazuri în care este necesară şi utilă stabilirea unei astfel de relaţii.

Ex:

30

Asocierea( legătura ) de tip unu la mai mulţi (1:N)

Două tabele A şi B se află în asociere 1:N dacă unei înregistrări din tabelul A îi corespund mai multe înregistrări în tabelul B. Cheia primara din tabelul “parinte”(A) se adaugă în tabelul “copil”(B) sub forma de cheie externă.

Ex:

Asocierea( legătura ) de tip mulţi la mulţi (M:N) – mai multor înregistrări dintr-un tabel le corespund mai multe înregistrări în cealalt tabel. În vederea implementării practice se adaugă un tabel suplimentar care va contine cheile primare ale tabelelor initiale si campuri referitoare la asocierea dintre tabelele “parinti”. Cheia primară din tabela intermediară va fi o cheie compusa.

31

VREMEAorastemp_mintemp_maxprecipitatii

MUNICIPIIdenumire_oraspopulatiesuprafata

1:1

JUDETnumesuprafatapopulatie

LOCALITATEdenumiresuprafatapopulatiepozitie_geogr.

1:N

AUTORInumetitluedituraan_aparitie

BIBLIOTECAcotatitluautor1edituraan_aparitie

M:N

Crearea cheilor primare şi a cheilor externe

Cheia primară din tabel impune unicitatea valorilor câmpurilor din cadrul cheii primare pentru fiecare înregistrare. Specificarea cheii primare se face cu modificatorul PRIMARY KEY. Cheia străină sau externă se declară prin modificatorul FOREIGN KEY.

Ex:

sau

Atât pentru PRIMARY KEY cât şi pentru FOREIGN KEY putem avea unul sau mai multe câmpuri, separate prin virgulă.

32

CREATE TABLE municipii(denumire_oras varchar(80) PRIMARY KEY,suprafata numeric,populatie int);

CREATE TABLE municipii(denumire_oras varchar(80) PRIMARY KEY,suprafata numeric,populatie int);

CREATE TABLE vremea2(oras varchar(80) PRIMARY KEY REFERENCES municipii(denumire_oras),temp_min int,temp_max int,precipitatii real,data date);

CREATE TABLE vremea2(oras varchar(80) PRIMARY KEY REFERENCES municipii(denumire_oras),temp_min int,temp_max int,precipitatii real,data date);

CREATE TABLE vremea(oras varchar(80) REFERENCES municipii(denumire_oras),temp_min int,temp_max int,precipitatii real,data date);

CREATE TABLE vremea(oras varchar(80) REFERENCES municipii(denumire_oras),temp_min int,temp_max int,precipitatii real,data date);

CREATE TABLE vremea2( oras character varying(80) NOT NULL, temp_min integer, temp_max integer, precipitatii real, data date, CONSTRAINT vremea2_pkey PRIMARY KEY (oras), CONSTRAINT vremea2_oras_fkey FOREIGN KEY (oras) REFERENCES municipii (denumire_oras) );

CREATE TABLE vremea2( oras character varying(80) NOT NULL, temp_min integer, temp_max integer, precipitatii real, data date, CONSTRAINT vremea2_pkey PRIMARY KEY (oras), CONSTRAINT vremea2_oras_fkey FOREIGN KEY (oras) REFERENCES municipii (denumire_oras) );

Asigurarea integrităţii referenţiale

Una din problemele importante ale unei baze de date este asigurarea consistenţei şi corelării datelor. Pentru aceasta putem impune respectarea unei restricţii la adăugarea sau eliminarea înregistrărilor în tabelele corelate. Aplicarea acestor restricţii ne dă posibilitatea să asigurăm o proprietate importantă a bazelor de date relaţionale numită integritate referenţială.

Aceste restricţii se referă la ştergerea datelor, la inserarea/adăugarea datelor sau modificarea lor.

Operaţiile de ştergere şi inserare au în clauza WHERE şi câmpurile ce constituie cheile primare. De asemenea operaţia de adăugare impune specificarea obligatorie a câmpurilor din cheia primară, acestea fiind diferite de NULL şi fiind unice în cadrul tabelului.

Exemplu:

INSERT INTO c4.in SELECT * FROM c3.inINSERT INTO c4.st SELECT * FROM c3.st

select * from c4.inselect * from c4.stselect * from rezervariselect * from c4.bpselect * from selectieselect * from c4.blselect * from c4.tr--update selectie set nrbp=101 where nrbp=1 -- afiseaza cantitati rezervateselect * from c4.st where qtyfiz<>qtydisp

--adaugarea borderoului de receptie curent in stoc

INSERT INTO c4.st(dep,amp,id,qtyfiz,qtydisp) SELECT dep,amp,id,qty,qty FROM c4.in WHERE br=1;

-- rezervarea cantitatilor si crearea liniilor din bp

--pp . ca rezervam id 2,3 si din 5 rezervam 3 buc

-- ceea ce rezervam punem intr-un tabel temporar (ptr.selectie)CREATE TEMPORARY TABLE rezervari(dep varchar(10), amp varchar(10),id integer, qty numeric);INSERT INTO rezervari VALUES('A1','A101',2,1),('A1','A101',3,1),('A1','A101',5,3);

33

-- cazul a) din stoc se scade toata linia => linia va fi stearsa-- cazul b) din stoc, de pe o linie se rezerva doar o parte din cantitate UPDATE c4.st SET qtydisp=qtydisp-qty FROM rezervari WHERE st.id=rezervari.id and st.dep=rezervari.dep and st.amp=rezervari.amp

insert into c4.bp SELECT 101 as nrbp, dep,amp,id,qty,CURRENT_DATE,'Dest1' as dest FROM rezervari;

-- validarea bp--selectieCREATE TEMPORARY TABLE selectie(nrbp integer, dep varchar(10), amp varchar(10),id integer, qty_confirmata numeric);--se presupune ca nu se mai livreaza trei si se livreaza numai 2 bucINSERT INTO selectie VALUES(101,'A1','A101',2,1),(101,'A1','A101',3,1),(101,'A1','A101',5,2);

--validareUPDATE c4.st SET qtydisp=qtydisp+bp.qty-qty_confirmata FROM selectie,c4.bp WHERE st.id=selectie.id and st.dep=selectie.dep and st.amp=selectie.amp and st.id=bp.id and st.dep=bp.dep and st.amp=bp.amp and bp.nrbp=101; --pp. ca nr.bp este 101

-- inserare in blINSERT INTO c4.bl SELECT bp.nrbp,bp.dep,bp.amp,bp.id,qty_confirmata,bp.data,bp.dest FROM c4.bp,selectie WHERE selectie.nrbp=101 and selectie.nrbp=bp.nrbp and selectie.dep=bp.dep and selectie.amp=bp.amp and selectie.id=bp.id; --pp. ca nr.bp este 101

--stergere bp validatDELETE FROM c4.bp WHERE nrbp=101;

-- TRANSFERUL INTRE MAGAZII--a)-- pp transferul qty=1 ptr. ID=4 de pe amplasamentul A1 /A102 pe A2/A203 --si a qty=1 ptr. ID=1 de pe amplasamentul A1/A101 pe A2/204ALTER TABLE c4.tr ADD COLUMN btr int; INSERT INTO c4.tr(btr,data,depsrs,ampsrs,depdest,ampdest,id,qty) VALUES(401,CURRENT_DATE,'A1','A101','A2','A203',4,1), (401,CURRENT_DATE,'A1','A101','A2','A204',1,1);

34

--verifica daca exista destinatie (dep,amp,id) == (DepDest, AmpDest,Id)--SELECT EXISTS(SELECT * FROM c4.st, WHERE dep-- TRANSFERUL INTRE MAGAZII--a)-- pp transferul qty=1 ptr. ID=4 de pe amplasamentul A1 /A102 pe A2/A203 --si a qty=1 ptr. ID=1 de pe amplasamentul A1/A101 pe A2/204--ALTER TABLE c4.tr ADD COLUMN btr int; --INSERT INTO c4.tr(btr,data,depsrs,ampsrs,depdest,ampdest,id,qty) -- VALUES(401,CURRENT_DATE,'A1','A101','A2','A203',4,1), -- (401,CURRENT_DATE,'A1','A101','A2','A204',1,1);--verifica daca exista destinatie (dep,amp,id) == (DepDest, AmpDest,Id)select * from c4.st;select * from c4.tr;

SELECT EXISTS(SELECT * FROM c4.st WHERE dep='A2' and amp='A203');

-- pentru a nu lucra inregistrare cu inregistrare--selectam datele din transfer care nu exista in stoc si le insaeram in stocINSERT INTO c4.st(dep,amp,id,qtyfiz,qtydisp) SELECT tr.depdest,tr.ampdest,tr.id,qty,qty FROM c4.tr WHERE NOT EXISTS (SELECT * FROM c4.st,c4.tr WHERE tr.depdest=st.dep AND tr.ampdest=st.amp AND tr.id=st.id) AND tr.btr=401;--scade pe cele din locatia sursa

35

INTEROGAREA MAI MULTOR TABELE

Atunci când în clauza FROM a unei comenzi SELECT apar mai multe tabele se realizează produsul cartezian al acestora. De aceea numărul de linii rezultat creşte considerabil, fiind necesară restricţionarea acestora cu o clauza WHERE.

Atunci când este necesară obţinerea de informaţii din mai multe tabele se utilizează clauza JOIN. În acest fel liniile dintr-un tabel pot fi puse în legătura cu cele din alt tabel conform valorilor comune ale unor coloane.

Clauza JOIN

Clauza JOIN este utilizată pentru preluarea informaţiilor din două sau mai multe tabele în condiţiile existenţei unor legături logice între anumite câmpuri din cadrrul tabelelor.

JOIN întoarce înregistrările ce respectă condiţiile impuse de JOIN între tabele. JOIN nu se referă la intersecţii ci la produse carteziene. Diversele tipuri de JOIN reduc produsele punând diverse condiţii pe una sau mai multe dintre mulţimi.

Presupunem că un tabel T1 cu N înregistrări şi un tabel T2 cu M înregistrări.

CROSS JOIN

T1 CROSS JOIN T2 întoarce pentru fiecare înregistrare din T1 toate înregistrările din T2. Tabelul rezultat va avea NxM înregistrări

Sintaxa:

Clauzele INNER şi OUTER sunt optionale. INNER este implicit, iar LEFT, RIGHT sau FULL implică OUTER.

Clauza ON este asemănătoare cu WHERE.

36

T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON boolean_expressionT1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON boolean_expression

T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 USING ( join column list )T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 USING ( join column list )

T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2

Principalele forme ale clauzei JOIN:

✔ INNER JOIN (joncţiune internă – de tip egalitate) - pentru fiecare rînd (înregistrare) R1 din tabelul T1, tabelul rezultat în urma aplicării clauzei JOIN va avea o înregistrare din tabelul R2 care îndeplineşte condiţia de jonctiune cu R1.

Ex:

echivalent d.p.d.v. al scrierii cu:

✔ LEFT OUTER JOIN (joncţiune externă la stânga) Pentru început se realizează o joncţiune internă. Apoi, fiecare rând din T1 (T1 stânga, T2 dreapta) care nu satisface joncţiunea cu T2 este adăugat având valori NULL pentru câmpurile corespondente din T2. Tabelul rezultat va avea, cel putin rândurile din tabelul T1.

✔ RIGHT OUTER JOIN (joncţiune externă la stânga) Pentru început se realizează o joncţiune internă. Apoi, fiecare rând din T2 (T1 stânga, T2 dreapta) care nu satisface joncţiunea cu T1 este adăugat având valori NULL pentru câmpurile corespondente din T1. Tabelul rezultat va avea, cel putin rândurile din tabelul T2.

✔ FULL OUTER JOIN (joncţiune totală) Pentru început se realizează o joncţiune internă. Apoi, fiecare rând din T2 (T1 stânga, T2 dreapta) care nu satisface joncţiunea cu T1 este adăugat având valori NULL pentru câmpurile corespondente din T1. Apoi, fiecare rând din T2 (T1

37

SELECT v.oras,v.data FROM vremea v INNER JOIN municipii m ON v.oras=m.denumire_orasSELECT v.oras,v.data FROM vremea v INNER JOIN municipii m ON v.oras=m.denumire_oras

SELECT v.oras,v.data FROM vremea v, municipii m WHERE v.oras=m.denumire_orasSELECT v.oras,v.data FROM vremea v, municipii m WHERE v.oras=m.denumire_oras

SELECT v.oras,v.data FROM vremea v LEFT OUTER JOIN municipii m ON v.oras=m.denumire_orasSELECT v.oras,v.data FROM vremea v LEFT OUTER JOIN municipii m ON v.oras=m.denumire_oras

SELECT v.oras,v.data,m.populatie FROM vremea1 v RIGHT OUTER JOIN municipii m ON v.oras=m.denumire_oras

SELECT v.oras,v.data,m.populatie FROM vremea1 v RIGHT OUTER JOIN municipii m ON v.oras=m.denumire_oras

stânga, T2 dreapta) care nu satisface joncţiunea cu T1 este adăugat având valori NULL pentru câmpurile corespondente din T1.

De asemenea, se pot lega mai multe clauze de tipul ON într-o instrucțiune de tipul JOIN, utilizând, de exemplu, sintaxa:

O operație de tip LEFT JOIN sau RIGHT JOIN se poate imbrica într-o operație de tipul INNER JOIN, dar o operație de tipul INNER JOIN nu se poate imbrica într-o operație de tipul LEFT JOIN sau RIGHT JOIN.

Exemplu:-- se creaza 2 tabele CREATE TABLE c5_1.t1(a1 varchar(10) PRIMARY KEY, a2 int);CREATE TABLE c5_1.t2(b1 varchar(10) PRIMARY KEY, b2 int, b3 int);-- se populeaza tabeleleINSERT INTO c5_1.t1(a1,a2) VALUES ('aaa1',5),('aaa2',6),('aaa3',7),('aaa4',6);INSERT INTO c5_1.t2(b1,b2,b3) VALUES ('bb1',55,1),('bb2',56,2),('aaa3',56,3),('aaa4',6,3),('aaa1',5,3);-- se verifica continutul tabelelorselect * from c5_1.t1;select * from c5_1.t2;

--- INTERSECTIA

--intersectia celor 2 tabele dupa un camp : punem campul a1 == b1--varianta 1SELECT * FROM c5_1.t1,c5_1.t2 WHERE t1.a1=t2.b1;--varianta 2SELECT * FROM c5_1.t1 INNER JOIN c5_1.t2 ON t1.a1=t2.b1;

-- fara conditie de legatura intre tabeleSELECT * FROM c5_1.t1,c5_1.t2 ;--=> produs cartezian 5 x 4 = 20

38

SELECT v.oras,v.data,m.populatie FROM vremea1 v FULL OUTER JOIN municipii m ON v.oras=m.denumire_oras

SELECT v.oras,v.data,m.populatie FROM vremea1 v FULL OUTER JOIN municipii m ON v.oras=m.denumire_oras

SELECT câmpuriFROM tabel1 INNER JOIN tabel2((ON tabel1.câmp1 operator de comparație tabel2.câmp1 ANDON tabel1.câmp2 operator de comparație tabel2.câmp2) ORON tabel1.câmp3 operator de comparație tabel2.câmp3);

SELECT câmpuriFROM tabel1 INNER JOIN tabel2((ON tabel1.câmp1 operator de comparație tabel2.câmp1 ANDON tabel1.câmp2 operator de comparație tabel2.câmp2) ORON tabel1.câmp3 operator de comparație tabel2.câmp3);

inregistrari

-- intersectia celor 2 tabele dupa 2 campuri a1 cu b1 si a2 cu b2-- varianta 1SELECT * FROM c5_1.t1,c5_1.t2 WHERE t1.a1=t2.b1 AND t1.a2=t2.b2;--varianta 2SELECT * FROM c5_1.t1 INNER JOIN c5_1.t2 ON t1.a1=t2.b1 AND t1.a2=t2.b2;--varianta 3SELECT * FROM c5_1.t1 INNER JOIN c5_1.t2 ON t1.a1=t2.b1 WHERE t1.a2=t2.b2;

-- CONCATENARE TABELE-- varianta 1 ==> 9 inreg SELECT a1,a2 FROM c5_1.t1UNIONSELECT cast(b2 as text),b3 FROM c5_1.t2; -- sau ===> 7 inreg (3 inreg. sunt identice)SELECT a1,a2 FROM c5_1.t1UNIONSELECT b1,b2 FROM c5_1.t2;

-- sau ===> 12 inreg (3 inreg. sunt identice)SELECT a1,a2 FROM c5_1.t1UNIONSELECT b1,b2 FROM c5_1.t2UNIONSELECT b1,b3 FROM c5_1.t2;

--EXCEPT-- t1 - t2SELECT a1,a2 FROM c5_1.t1EXCEPTSELECT b1,b2 FROM c5_1.t2;-- t2 - t1 (ce avem in t2 si nu avem in t1)SELECT b1,b2 FROM c5_1.t2EXCEPTSELECT a1,a2 FROM c5_1.t1;

-- JOIN --- EXTERN-- LEFT OUTER JOIN-- vers 1: tot tabelul t1 + camp coresp. din t2 ptr. t1, altfel umple b1 si b2 cu NULL SELECT a1,a2,b1,b2 FROM c5_1.t1 LEFT OUTER JOIN c5_1.t2 ON t1.a1=t2.b1;--vers. 2SELECT a1,a2,b1,b2 FROM c5_1.t2 LEFT OUTER JOIN c5_1.t1 ON t1.a1=t2.b1;

-- RIGHT OUTER JOIN

39

-- vers 1 === vers 2 (LEFT JOIN)SELECT a1,a2,b1,b2 FROM c5_1.t1 RIGHT OUTER JOIN c5_1.t2 ON t1.a1=t2.b1;-- vers 2 === vers 1 (LEFT JOIN)SELECT a1,a2,b1,b2 FROM c5_1.t2 RIGHT OUTER JOIN c5_1.t1 ON t1.a1=t2.b1;

--FULL OUTER JOIN--vers1 (idem cu vers2) t1 + t2 (pe coloane ) -- sunt eliminate duplicatele dupa campurile de potrivireSELECT a1,a2,b1,b2,b3 FROM c5_1.t1 FULL OUTER JOIN c5_1.t2 ON t1.a1=t2.b1;--vers2 -- idemSELECT a1,a2,b1,b2,b3 FROM c5_1.t2 FULL OUTER JOIN c5_1.t1 ON t1.a1=t2.b1;

40

MOŞTENIREA

Acest concept a apărut în standardul SQL99 .şi este specific sistemelor de baze de date orientate obiect.

Ex:

Cele două formulări pot fi înlocuite prin:

Un tabel poate să moştenească unul sau mai multe tabele.

În cazul în care se face referire la un anumit tabel din cadrul moştenirii se utilizează clauza ONLY

Aceasta clauză poate fi utilizată în combinaţie cu : SELECT, UPDATE sau DELETE.

Comanda va afişa câmpurile: oras, populatie, suprafata, judet. De

asemenea va afişa numai rândurile adăugate cu comanda INSERT în cadrul tabelului municipii_2.

41

CREATE TABLE municipii_1 (judet character varying(100) NOT NULL, oras character varying(80) NOT NULL, populatie integer, suprafata numeric);

CREATE TABLE municipii_1 (judet character varying(100) NOT NULL, oras character varying(80) NOT NULL, populatie integer, suprafata numeric);

CREATE TABLE capitale_1 (tara character varying(100) NOT NULL, oras character varying(80) NOT NULL, populatie integer, suprafata numeric);

CREATE TABLE capitale_1 (tara character varying(100) NOT NULL, oras character varying(80) NOT NULL, populatie integer, suprafata numeric);

CREATE TABLE orase (oras character varying(80) NOT NULL, populatie integer, suprafata numeric);

CREATE TABLE orase (oras character varying(80) NOT NULL, populatie integer, suprafata numeric);

CREATE TABLE municipii_2 (judet character varying(100) NOT NULL) INHERITS (orase);CREATE TABLE municipii_2 (judet character varying(100) NOT NULL) INHERITS (orase);

CREATE TABLE capitale_2 (tara character varying(100) NOT NULL) INHERITS (orase);CREATE TABLE capitale_2 (tara character varying(100) NOT NULL) INHERITS (orase);

SELECT * FROM municipii_2SELECT * FROM municipii_2

Comanda va afişa câmpurile: oras, populatie, suprafata, tara. De

asemenea va afişa numai rândurile adăugate cu comanda INSERT în cadrul tabelului capitale_2.

Comanda va afişa câmpurile: oras, populatie, suprafata. De asemenea va afişa

toate rândurile adăugate cu comanda INSERT, indiferent dacă a fost realizată în cadrul tabelului capitale_2, municipii_2 sau a tabelului orase.

De asemenea, comanda va afişa doar rândurile adăugate cu comanda INSERT în cadrul tabelului orase.

De exemplu, dacă în tabelul orase sunt adăugate 5 înregistrări, în tabelul municipii_2 sunt adăugate 4 înregistrări, iar în tabelul capitale_2 sunt adăugate 2 înregistrări tabelul orase va conţine 11 înregistrări, tabelul municipii_2 va conţine 4 înregistrări iar tabelul capitale_2 va conţine 2 înregistrări,

Ştergerea unei înregistrări din tabelele municipii_2 sau capitale_2 , fără utilizarea clauzei ONLY, va duce şi la ştergerea înregistrărilor respective din tabelul orase.

Ştergerea unei înregistrări din tabelul orase, fără utilizarea clauzei ONLY, va duce şi la ştergerea înregistrărilor corespondente - dacă există - din tabelele municipii_2 sau capitale_2 .

Aceste observaţii sunt valabile şi în cazul modificărilor (utilizarea comenzii UPDATE).

Ex:

--Pentru monotorizarea unui proces tehnologic sunt utilizate urmatoarele tabele: mas(masuratori) si par(parametrii)--Codurile punctelor de masura(pctMas) sunt unice la nivel de modul, iar codurile parametrilor masurati (codPar) sunt unice la nivelul de masura--De asemenea presupunem ca mai exista inca trei tabele mas1,mas2 si mas3 avand structura identica cu mas.

42

SELECT * FROM capitale_2SELECT * FROM capitale_2

SELECT * FROM oraseSELECT * FROM orase

SELECT * ONLY FROM oraseSELECT * ONLY FROM orase

--1.scrieti comanda SQL pt crearea tabelelor mas si par(denumirile campurilor, tipurile de date,cheile primare si externe)

CREATE TABLE c6.mas(data date DEFAULT CURRENT_DATE, ora time DEFAULT CURRENT_TIME,

codunicpar varchar(20),val numeric,operator varchar(30),CONSTRAINT k_mas PRIMARY KEY(data,ora,codunicpar),CONSTRAINT f_maspar FOREIGN KEY(codunicpar) REFERENCES

c6.par(codunicpar));CREATE TABLE c6.par(modul varchar(10), pctmas varchar(10),codpar varchar(10),

codunicpar varchar(20), um varchar(10),valmin numeric, valmax numeric,

CONSTRAINT k_par PRIMARY KEY(codunicpar));

--2.scrieti comanda SQL pt inserarea unei inregistrari in tabelul mas

INSERT INTO c6.par VALUES('M1','PM1','par1','A00p','V',0.4,11.7), ('M3','PM1','par1','A01p','V',-0.5,17.7), ('M1','PM2','par2','A02p','V',0.1,10.3);

INSERT INTO c6.mas(codunicpar,val, operator)VALUES('A01p',51.3,'op1'),('A00p',52.1,'op2');

INSERT INTO c6.mas(codunicpar,val, operator)VALUES ('A01p',15.3,'op1'),('A00p',51.3,'op1');



--3.scrieti comanda SQL pentri afisarea tuturor masuratorilor din ziua curenta (din tabelul mas)

SELECT * FROM c6.mas WHERE data=CURRENT_DATE;

--4.scrieti comanda SQL pentru inlocuirea valorii 'A00p' a camplului codunicpar, din tabelul mas, cu valoarea 'A02p'

UPDATE c6.mas SET codunicpar='A02p' WHERE codunicpar='A00p';

--5.scrieti comanda SQL pentru stergerea inregistrarilor, anterioarea zilei curente, care nu au punctul val completat

DELETE FROM c6.mas WHERE data=CURRENT_DATE-1 AND val IS NULL;

--6.scrieti comanda SQL pentru afisarea valorilor medii pentru parametrii masurati, in ziua anterioarea de catre operatorul user3

43

SELECT codunicpar,avg(val) FROM c6.mas WHERE data=CURRENT_DATE-1 AND= operator='user3' GROUP BY codunicpar;

--7.scrieti comanda SQL pentru afisarea numarilui de masuratori din ziua curenta, pentru fiecare punct de masura

SELECT modul,pctmas,count(mas.*) as nr_mas FROM c6.mas,c6.par WHERE mas.codunicpar=par.codunicpar

AND data=CURRENT_DATE GROUP BY modul, pctmas;

44

SUBINTEROGĂRI

Subinterogările permit crearea unui tabel în cadrul unei interogări. Acestea sunt scrise între paranteze rotunde şi au un ALIAS. Subinterogări scalare - Acestea sunt interogările care întorc un singur rând şi o singură coloană. Ele se scriu între paranteze rotunde. În cazul în care suinterogarea întoarce mai multe înregistrări vom avea eroare. În cazul în care nu întoarce nici o înregistrare valoarea rezultată a interogării este considerată NULL.

Ex:

Expresii specifice subinterogărilor

EXISTS

Intoarce true daca subinterogarea are ca rezultat cel puţin o înregistrare, alfel întoarce false.

IN caz în care subinterogarea întoarce o singură coloană

Verifică dacă expresia se alflă în rezultatul subinterogării

sau caz în care subinterogare întoarce acelaţi număr de coloane ca şi

constructorul de înregistrare (row constructor)

Obs: Constructorul de înregistrare formează o înregistrare utlizând cuvântul ROW, de ex:

Ex:

45

SELECT oras,(SELECT avg(precipitatii) FROM vremea WHERE capitale_2.oras=vremea.oras) AS media FROM capitale_2;

SELECT oras,(SELECT avg(precipitatii) FROM vremea WHERE capitale_2.oras=vremea.oras) AS media FROM capitale_2;

EXISTS (subquery)EXISTS (subquery)

expression IN (subquery)expression IN (subquery)

Row constructor IN (subquery)Row constructor IN (subquery)

ROW(12,14,'TEST')ROW(12,14,'TEST')

SELECT ROW(12,14,'IASI') IN(SELECT temp_min,temp_max,oras FROM vremea) AS VERIFICA

SELECT ROW(12,14,'IASI') IN(SELECT temp_min,temp_max,oras FROM vremea) AS VERIFICA

NOT IN asemănătoare cu IN dar cu rezultatul negat.

ALL caz în care subinterogarea întoarce o singură coloană

Verifică dacă expresia satisface condiţia pentru fiecare înregistrare din subinterogare

sau caz în care subinterogare întoarce acelaţi număr de coloane ca şi

constructorul de înregistrare (row constructor)

46

expression ALL (subquery)expression ALL (subquery)

Row constructor ALL (subquery)Row constructor ALL (subquery)

TIPUL CAST

Este utilizat pentru conversia tipurilor de date, în vederea evaluării unor expresii în care intră datele respective sau pentru adăugarea acestora în tabele ce au un alt format de date.

Ex:

Un caz aparte de cast este funcţia to_date( test, text ) care realizează conversia din şir de caractere în .format de tip dată calendaristică.

Ex:

Ex:

--1create table c7.bibl(cota varchar(10),autori varchar(50), editura varchar(30), an int, localitatea varchar(20), constraint k_bibl PRIMARY KEY(cota)); --2create table c7.oper(cota varchar(10), idx serial, nume varchar(20), data_im date DEFAULT CURRENT_DATE, durata int, data_re date, constraint k_oper PRIMARY KEY(idx), constraint f_oper FOREIGN KEY(cota) REFERENCES c7.bibl(cota)); --3INSERT INTO c7.bibl VALUES('01-21134','a11,a12','Tehnica',1991,'Bucuresti'), ('01-22234','a21,a22','Tehnica',1995,'Bucuresti'), ('01-35134','a31,a32','Polirom',1980,'Iasi'), ('01-

47


SELECT oras, cast(temp_min as numeric), cast(precipitatii as integer) as PrecInt, cast(temp_max as varchar(15)) FROM vremea

SELECT oras, cast(temp_min as numeric), cast(precipitatii as integer) as PrecInt, cast(temp_max as varchar(15)) FROM vremea

INSERT INTO vremea(oras,data) VALUES('Titu', to_date('28.10.2008','dd.mm.yyyy')) INSERT INTO vremea(oras,data) VALUES('Titu', to_date('28.10.2008','dd.mm.yyyy'))

41134','a41,a42','EduSoft',1982,'Bacau'), ('01-51133','a51,a52','Eurobit',1991,'Timisoara'), ('01-61131','a61,a62','Solness',1980,'Timisoara'); INSERT INTO c7.oper(cota,nume,data_im,durata,data_re) VALUES('01-41134','nume1','2004-05-12',8,NULL), ('01-22234','nume1','2004-05-12',1,NULL), ('01-51133','nume2','2005-05-22',1,'2006-06-23'), ('01-51133','nume3','2006-06-23',4,NULL), ('01-51133','nume1','2006-07-24',2,NULL), ('01-22234','nume4','2007-05-20',1,NULL); --select * from c7.bibl--select * from c7.oper

-- delete from c7.bibl-- delete from c7.oper where idx>12-- s-a sters cheia externa ptr. simplificare--4. delete from c7.bibl where an<1994 and an>1981; --5select * from c7.bibl where editura like 'E%';--6select data_im as data, count (oper.*) as nr_carti from c7.oper group by data_im--7select bibl.cota, bibl.autori, bibl.editura FROM c7.bibl,c7.oper where bibl.cota=oper.cota and oper.data_im<'2006-01-01'and oper.data_re is null; --8 update c7.bibl set editura='Eurobit' FROM c7.oper where oper.cota=bibl.cota and an < 1990 and editura='Solness' and oper.data_re is not null--9delete from c7.oper using c7.bibl where oper.cota=bibl.cota and oper.data_re is null and an<1995

--10select bibl.cota,bibl.autori,bibl.editura from c7.bibl,c7.oper where oper.cota=bibl.cota and oper.data_re is not null and bibl.editura ilike '%bit%';

--11select bibl.cota, bibl.autori, bibl.editura from c7.bibl, (select cota from c7.bibl EXCEPT select cota from c7.oper) as par

48

where bibl.cota=par.cota;

--sau CREATE TEMP TABLE tmp(cota varchar(10)); INSERT INTO tmp SELECT cota from c7.bibl EXCEPT select cota from c7.oper; SELECT bibl.cota,bibl.autori,bibl.editura FROM c7.bibl,tmp WHERE bibl.cota=tmp.cota;

--12select bibl.cota,bibl.autori,bibl.editura FROM c7.bibl,c7.oper where bibl.cota=oper.cota and CURRENT_DATE+2=data_im+durata;

--13

SELECT bibl.cota, sum(par.nr_zile_imprumut) as nr_zile_imprumut FROM c7.bibl, (SELECT cota, CASE WHEN data_re IS NOT NULL THEN sum(data_re-data_im) WHEN data_re IS NULL THEN sum(CURRENT_DATE-data_im) END as nr_zile_imprumut FROM c7.oper GROUP BY cota,data_re )as par WHERE par.cota=bibl.cota GROUP BY bibl.cota

--14select bibl.cota from c7.bibl,c7.oper where (oper.cota is not null) and (bibl.cota is not null);

49

FUNCŢII SQL LA NIVEL DE SERVER;

In acest capitol sunt prezentate un set de functii simple

la nivel server. Pentru rularea acestor exemple se va creea

schema c10_2.

Pentru fiecare functie explicitata este detaliat si apelul

acesteia .

Fiecare functie are un corp (delimitat cu $BODY$ sau $$) si un RETURN.

EXEMPLE COMENTATE DE FUNCTII SI PROCEDURI

CREATE SCHEMA c9;--- FUNCTII ----- FUNCTII CE CONTIN COD SQL -- CREATE OR REPLACE function c9.ftest0()RETURNS void AS$$ CREATE TABLE c9.t1(a1 serial PRIMARY KEY ,a2 int, a3 int, a4 varchar(20));$$LANGUAGE 'sql';-- corpul functiei este incadrat de $$ sau $BODY$ si contine doar o comanda SQL--------------------------------------------------------------------------- apelul functieiselect c9.ftest0();-------------------------------------------------------------------------

CREATE OR REPLACE function c9.ftest1()RETURNS void AS$$--CREATE TABLE c9.t1(a1 serial PRIMARY KEY ,a2 int, a3 int, a4 varchar(20));INSERT INTO c9.t1(a2,a3,a4) VALUES (7,8,'AA1'),(12,7,'AA2'),(11,5,'AA3');$$LANGUAGE 'sql';-------------------------------------------------------------------------

50

--apel functie varianta 1SELECT c9.ftest1();--------------------------------------------------------------------------- functie SQL ptr. afisarea continutului lui t1 --VARIANTA1CREATE OR REPLACE function c9.ftest2()RETURNS SETOF c9.t1 AS -- intoarce o grupare de inreg. cu structura inregistrarii definita de c9.t1$$SELECT * FROM c9.t1$$LANGUAGE 'sql';-----------------------------------------------------------------------------apel functieSELECT c9.ftest2(); -- (1,7,...) are semnificatie de structura-- intoarce o singura colana ce contine ca data o structura c9.t1-------------------------------------------------------------------

-- functie SQL ptr. afisarea continutului lui t1 ---VARIANTA2CREATE OR REPLACE function c9.ftest3()RETURNS SETOF RECORD AS -- RECORD este tipul inregistrare$$SELECT * FROM c9.t1$$LANGUAGE 'sql';-------------------------------------------------------------------------------apel functie --- varianta ASELECT c9.ftest3(); -- rezultat pe o singura coloana-- apel functie --- varianta BSELECT a1,a2,a3,a4 FROM c9.ftest3() AS (a1 int,a2 int, a3 int, a4 varchar(20));--rezultatul apare pe patru coloane

-------------------------------------------------------------------------------- functie SQL ptr. afisarea continutului lui t1 --VARIANTA3CREATE OR REPLACE function c9.ftest4()RETURNS SETOF c9.t1 AS$$SELECT a1,a2,a3,a4 FROM c9.t1$$LANGUAGE 'sql';-----------------------------------------------------------------------------apel functie --- varianta ASELECT c9.ftest4(); -- campurile sunt agregate intr-un singur camp de tip structura de date-- apel functie --- varianta B

51

SELECT a1,a2,a3,a4 FROM c9.ftest4(); -- campurile sunt afisate distinct--tipul fiecarui camp este cunoscut din "RETURNS SETOF c9.t1"------------------------------------------------------------------------------ functie SQL ptr. afisarea continutului lui t1 --VARIANTA4CREATE OR REPLACE function c9.ftest5()RETURNS SETOF RECORD AS$$SELECT a1,a2,a3,a4 FROM c9.t1$$LANGUAGE 'sql';-----------------------------------------------------------------------------apel functie --- varianta ASELECT c9.ftest5(); -- grupeaza campurile-- apel functie --- varianta BSELECT a1,a2,a3,a4 FROM c9.ftest5() AS (a1 int,a2 int, a3 int, a4 varchar(20));-------------------------------------------------------------------

-- functie SQL --- transfer prin argument-- ex: functie ce intoarce un subset de inregistrariCREATE OR REPLACE function c9.ftest6(b2 int, b3 int) -- tipul variabilei se pune dupa numele acesteia - invers CRETURNS SETOF RECORD AS$$SELECT a1,a2,a3,a4 FROM c9.t1 WHERE a2>$1 AND a3<$2; -- $1 -- primul argument(b2) , $2 - al doilea argument (b3)$$LANGUAGE 'sql';--------------------------------------------------------------------- apel functieSELECT a1,a2,a3,a4 FROM c9.ftest6(10,15) AS (a1 int,a2 int, a3 int, a4 varchar(20));-------------------------------------------------------------------

--- functie SQL ce intoarce o valoare de tip booleanCREATE OR REPLACE function c9.ftest7(b2 int)RETURNS BOOLEAN AS$$SELECT EXISTS(SELECT * FROM c9.t1 WHERE a2>$1); -- intoarce TRUE daca exista cel putin o inregistrare in cadrul celui de al doilea SELECT altfel intoarce FALSE$$LANGUAGE 'sql';-------------------------------------------------------------------SELECT c9.ftest7(2) as f1;

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

52

----- PROCEDURI plpgsql --------------

-- sunt de cateva ori mai lente decat functiile SQL insa sunt mult mai flexibile

-- Function: c9.gtest1()

-- DROP FUNCTION c9.gtest1();

CREATE OR REPLACE FUNCTION c9.gtest1() RETURNS void AS$$BEGINCREATE TABLE c9.t2(a1 serial PRIMARY KEY ,a2 int, a3 int, a4 varchar(20));INSERT INTO c9.t2(a2,a3,a4) VALUES (17,18,'AA11'),(112,17,'AA12'),(111,15,'AA13');END$$ LANGUAGE 'plpgsql'; -- apelulselect c9.gtest1();

---un alt test SQL vs plpgsql -- executie imposibila -- se realizeaza o analiza a codului SQL si nu gaseste c9.t3 pentru cda INSERT--ceea ce in cazul plpgsql nu duce la eroare

CREATE OR REPLACE FUNCTION c9.gtest1sql() RETURNS void AS$$CREATE TABLE c9.t3(a1 serial PRIMARY KEY ,a2 int, a3 int, a4 varchar(20));INSERT INTO c9.t3(a2,a3,a4) VALUES (17,18,'AA11'),(112,17,'AA12'),(111,15,'AA13');$$ LANGUAGE 'sql'; -- apelulselect c9.gtest1sql();

-----------------------------------------------------------EXEMPLU ADAUGA o INREGISTRARE DACA NR. ACESTORA (inainte de adaugare) ESTE IMPAR--SI DOUA INREG. DACA NR. DE INREG ESTE par

53

-- Function: c9.gtest2()

-- DROP FUNCTION c9.gtest2();

CREATE OR REPLACE FUNCTION c9.gtest2() RETURNS void AS$$DECLARE -- deschide blocul pentru declaratiinr_inreg int;BEGINnr_inreg:=-1; -- atribuire -- nu are nici un efectSELECT count(*) FROM c9.t1 INTO nr_inreg;--instructiune conditionala IF conditie THEN instructiuni_1 [ELSE instructiuni_2 ] END IF;IF NOT (nr_inreg%2)=0 THEN -- daca este impar INSERT INTO c9.t1(a2,a3,a4) VALUES (1001,0,'---');ELSE INSERT INTO c9.t1(a2,a3,a4) VALUES (1002,0,'M'),(1003,17,'N');END IF;END$$LANGUAGE plpgsql;------------------------------------------------------------------ apelulselect c9.gtest2();

--plpgsql (continuare)create schema c10;create or replace function c10.f1(IN a int, IN b int,OUT c int) returns int as$$BEGIN c=a+b; -- de obicei se scrie c:=a+b; altfel poate fi interpretat ca si == -- nu este necesara specificatia return c; deoarece exista OUT c;END$$language plpgsql;select c10.f1(5,7);-------------------------------------------------------------------create or replace function c10.f2(a int, IN b int,OUT suma int, OUT produs int, OUT impartire numeric) as$$BEGIN suma:=a+b; produs:=a*b; impartire:=a/(b*1.0);END$$

54

language plpgsql;

-- IN (INPUT) este optional, acesta fiind tipul implicit ptr. arguumenteselect suma, produs, impartire from c10.f2(5,7);--------------------------------------------------------------------- operatii elementare cu notificari in plpgsql

create or replace function c10.f3() returns text as$$DECLAREnr_ins int;nr_up int;achR text;total int;BEGIN achR:='OK';DROP TABLE IF EXISTS c10.t1;CREATE table c10.t1(a serial PRIMARY KEY, b int, c varchar(10));INSERT INTO c10.t1(b,c) VALUES (11,'A1'),(21,'B2'),(31,'B3'),(41,'C3');

GET DIAGNOSTICS nr_ins = ROW_COUNT; RAISE NOTICE 'Au fost adaugate: % inregistrari',nr_ins; UPDATE c10.t1 SET b=b+2 WHERE c LIKE 'B%';

GET DIAGNOSTICS nr_up = ROW_COUNT; RAISE NOTICE 'Au fost actualizate: % inregistrari',nr_up; SELECT sum(b) FROM c10.t1 INTO total;if total < 60 then achR:='NOK'; end if;return achR;END$$ language plpgsql;-- select nextval('c10.t1_a_seq');select c10.f3();

-- a se testa cu debuger-ul asociat!!!

---interogari construite dinamic --- mai multe siruri de caracatere sunt unite si se creaza o comanda dinamica

create or replace function c10.f4(a text) returns int as $$declarerez int;achQ text;beginachQ='select '||a;EXECUTE achQ INTO rez;return rez;end$$ language plpgsql;

55

select c10.f4('5+9');

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

create or replace function c10.f5(a text, b text) returns int as $$declarerez int;achQ text;beginachQ='select '||a||'+'||b;EXECUTE achQ INTO rez;return rez;end$$ language plpgsql;select c10.f5('5','9');

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

create schema c11;

create table c11.t1(a serial PRIMARY KEY, b int, c varchar(10));INSERT INTO c11.t1(b,c) VALUES(10,'A10'),(17,'A17'),(14,'A14'),(13,'A13'),(11,'A11');select * from c11.t1;

--functie care citeste valorile din tabel : varianta 1create or replace function c11.f1() RETURNS SETOF RECORD AS$$DECLAREr record;BEGIN FOR r IN SELECT a,b,c FROM c11.t1

LOOPRETURN NEXT r;

END LOOP;END$$ language plpgsql;

select a,b,c FROM c11.f1() AS (a int, b int, c varchar(10));

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

--functie care afiseaza numai inregistrarile a caror valoare b -- este mmai mica decat valoarea anterioara a acestuiacreate or replace function c11.f2() RETURNS SETOF RECORD AS$$DECLAREr record;

56

b1 int; -- memoreaza valoarea anterioara BEGIN b1:=0; FOR r IN SELECT a,b,c FROM c11.t1

LOOP if r.b < b1 THEN b1:=r.b; RETURN NEXT r; ELSE b1:=r.b; end if; END LOOP;

END$$ language plpgsql;

select a,b,c FROM c11.f2() AS (a int, b int, c varchar(10));

-- PROBLEMA1 --- CALCULUL DEBITELOR MEDII ZILNICE

CREATE table c11.d1(datam date, ora int, val numeric, CONSTRAINT k_d1 PRIMARY KEY(datam,ora)); delete from c11.d1;INSERT INTO c11.d1 VALUES('2000-01-01',6,8),('2000-01-01',14,12),('2000-01-01',22,16), ('2000-01-02',6,24),('2000-01-02',14,26),('2000-01-02',22,32), ('2000-01-03',6,44),('2000-01-03',14,90),('2000-01-03',22,120), ('2000-01-04',6,122),('2000-01-04',14,129),('2000-01-04',22,135);select * from c11.d1;

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

create or replace function c11.f3() RETURNS SETOF RECORD AS$$DECLAREr RECORD;r1 RECORD; -- ultima inregistrare cu data de ieri k int;BEGIN k=0; FOR r IN SELECT datam,ora,val,0.001 as q FROM c11.d1 -- q debit mediu orar la nivelul unei zile LOOP if r.ora=6 and k>1 then k:=k+1; r.q=(r.val-r1.val)/24.0; r1=r; return next r; end if; if r.ora=6 and k=1 then k:=k+1; r1.q=r1.val/24.0; r.q=(r.val-r1.val)/24.0; return next r1; r1=r; return next r;

57

end if; if r.ora=6 and k=0 then k:=k+1;r1=r;end if; END LOOP; END $$ language plpgsql;

SELECT datam,ora,val,q FROM c11.f3() AS (datam date, ora int, val numeric,q numeric);

58

GESTIUNEA ACCESULUI LA BAZA DE DATE

EXEMPLE COMENTATE:create schema c6;--GROUP ROLES --- grupuri de acces, un utilizator (LOGIN ROLES) poate sa apartina unui grup sau mai multor grupuri de acces-- accesul este:-- la nivel de baza de date-- la nivel de schema--la nivel de tabel--la nivel de functii--la nivel de secvente--etc--Tipurile de acces variaza in functie de tipul obiectului caruia i se adreseaza-- de ex:

--la nivel de baza de date avem acces pentru: conectare si creare,

--la nivel de schema : utilizare si creare (de obiecte in cadrul schemei)

--la nivel de tabel : select, insert, update,delete, evenimente triger, etc

--DREPTURILE de ACCES se pot da fie la nivel de grup (recomandat) fie la nivel de utilizator

--creare grupuri de acces-- DROP ROLE "CONSULTARE2";

CREATE ROLE "CONSULTARE2" NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE;CREATE ROLE "EDITARE2" NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE;CREATE ROLE "SUPERVIZOR2" SUPERUSER INHERIT CREATEDB CREATEROLE;

--creare utilizatori--DROP ROLE student21; CREATE ROLE student21 LOGIN PASSWORD '123' -- (student21 este <username>) NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE CONNECTION LIMIT 1; -- CONNECTION LIMIT 1 --->este permisa o singura conexiune pentru utilizator (nu pot intra simultan doi utilizatotori cu acelasi <username> )--atrbuire pentru student21 a drepturilor de acces specifice grupului "CONSULTARE2"

-- LOGIN face deosebirea intre grup de acces si utilizator (grupurile nu se pot 'loga')GRANT "CONSULTARE2" TO student21;

59

CREATE ROLE student22 LOGIN PASSWORD '12' CONNECTION LIMIT 1;GRANT "CONSULTARE2" TO student22;GRANT "EDITARE2" TO student22;

-- DROP ROLE student23CREATE ROLE student23 LOGIN PASSWORD '1' CONNECTION LIMIT 5;GRANT "CONSULTARE2" TO student23;GRANT "EDITARE2" TO student23;GRANT "SUPERVIZOR2" TO student23;

-- cream un tabel cu diverse drepturi de acces

CREATE TABLE c6.t1(a serial PRIMARY KEY, b varchar(10),c int);INSERT INTO c6.t1(b,c) VALUES('A1',10),('A2',20),('A3',30);select * from c6.t1;

--Pentru un GRUP--1. Se ofera acces la nivel de BAZA DE DATEGRANT CONNECT ON DATABASE "CURS_BD2012" TO "CONSULTARE2";GRANT CONNECT ON DATABASE "CURS_BD2012" TO "EDITARE2";GRANT ALL ON DATABASE "CURS_BD2012" TO "SUPERVIZOR2";

/* NOTA:PENTRU TESTAREA DREPTURILOR DE ACCES DIN pgAdmin3 se va deconecta serverul de baze de date curentsi se va reintra cu numele si parola utilizatorului ales-- se vor testa drepturi ptr.: creare obiecte(database,schema, tabel,etc)

stergere inregistrariactualizare inregistrariinserare inregistrari

pentru cele 3 campuri ale tabelului.*/

--2. Se ofera acces la nivel de schemaGRANT USAGE ON SCHEMA c6 TO "CONSULTARE2";GRANT USAGE ON SCHEMA c6 TO "EDITARE2";GRANT ALL ON SCHEMA c6 TO "SUPERVIZOR2";

--3.Se ofera acces la nivel de tabel, sequence, function, etc.

GRANT ALL ON TABLE c6.t1 TO "SUPERVIZOR2";GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE c6.t1 TO "EDITARE2";GRANT SELECT ON TABLE c6.t1 TO "CONSULTARE2";

60

introducere in sql

Documents