|
|
|
LIMBAJUL SQL
1. Generalitati
Limbajul structurat de interogare SQL (Structured Query Language) este limbajul standard pentru bazele de date (BD) relationale definit de ANSI in 1986 si adoptat ulterior ca standard international de catre ISO (1992). Peste o suta de sisteme de gestiune a bazelor de date (SGBD) accepta recunosc limbajul SQL.
Ca orice limbaj de baze de date, SQL permite:
Crearea bazei de date relationale si structurarea relatiilor prin componenta sa de definire a datelor (DDL)
Efectuarea operatiilor elementare asupra BD (inserare, stergere, modificare a datelor) si a interogarilor asupra BD, prin componenta de manipulare a datelor (DML)
Limbajul SQL contine comenzi de definire si regasire a datelor (CREATE TABLE, SELECT TABLE, DELETE, INSERT etc.) dar nu contine instructiuni pentru controlul fluxului datelor (IF THEN ELSE, GO TO, DO etc.).
SQL este un limbaj neprocedural cu format liber deoarece precizeaza ce rezultate sunt necesare si nu procedura prin care se obtin acestea.
Prima implementare comerciala a unui SGBD relational bazat pe SQL a fost realizata de corporatia ORACLE. Ulterior au aparut sute de produse de BD bazate pe SQL si dialecte ale acestuia.
SQL ca standard pentru BD a fost inclus ca si componenta in arhitecturile de aplicatii de BD dezvoltate de marile firme producatoare de soft, cum este IBM, si adopta pentru prelucrarea informatiilor in sistemul federal al SUA.
Grupul de acces SQL lucreaza pentru realizarea interoperabilitatii dintre limbajul SQL si alte SGBD disparate.
Imbunatatirea SQL se face prin definirea de noi componente precum standardul de acces la BD de la distanta (RDA - Remote Data Access) si sistemul de dictionare de resurse informationale (IRDS - Information Resource Dictionarz System). Alte imbunatatiri includ suportul pentru BD distribuite in retea, programarea orientata-obiect si acceptarea extensiilor definite de catre utilizator.
2. reguli SQL
Limbajul SQL foloseste termenii de tabele, coloane si randuri in locul celor de relatii, atribute si inregistrari.
O instructiune SQL include cuvinte rezervate si cuvinte definite de utilizator pentru a denumi tabelele, atributele, indexurile etc., nefiind sensibila la formatul literei (majuscula sau litera mica). Totusi SQL devine sensibil la formatul literelor (case sensitive) in cazul inregistrarilor din BD, adica doua inregistrari "CLIENT" si "Client" sunt tratate distinct.
Regulile sau conventiile adoptate in mod universal pentru scrierea instructiunilor SQL pot fi sintetizate astfel:
a. Fiecare clauza a unei instructiuni trebuie scrisa pe o linie noua, cu separare prin virgula.
b. Daca o clauza are mai multe parti, fiecare dintre acestea este scrisa pe o linie noua si indentata pentru a indica relatia cu linia superioara.
c. Majusculele sunt utilizate pentru cuvintele rezervate (SELECT, INSERT, DELETE, GRANT etc.).
d. Literele mici sunt folosite pentru termenii proprii utilizatorului (denumiri de tabele, coloane, campuri etc.).
e. Bara verticala | semnifica operatia logica "SAU" adica alegerea dintre doua sau mai multe optiuni.
f. Acoladele indica un element necesar.
g. Parantezele patrate indica un element optional.
h. Punctele de suspensie () specifica o repetare optionala a unui articol din BD, de zero sau de mai multe ori.
i. In practica, se creeaza mai intai structura BD prin definirea tabelelor si stabilirea formatului datelor, precum si definirea drepturilor de acces ale utilizatorilor, dupa care se trece la popularea BD.
j. Fiecare instructiune se incheie prin caracterul "punct si virgula".
k. Valorile, in general, sunt scrise intre paranteze rotunde, separate prin virgule.
l. Valorile literale sunt incadrate de apostroafe.
In instructiunile SQL apar diversi identificatori care respecta urmatoarele constrangeri:
a. Lucreaza cu setul de caractere prestabilit de ISO: litere mari (A . Z), litere mici (a . z), cifre (0 . 9) si liniuta de subliniere (_ underscore).
b. Sa nu depaseasca lungimea maxima impusa (tipic, 128 de caractere)
c. Sa inceapa cu o litera si nu cu alt caracter (cifra, alt semn etc.)
d. Sa nu contina spatii libere.
3. TIPURI DE DATE SQL
ISO defineste cinci tipuri de date scalare:
I. caracter
CHAR, VARCHAR [lungime]
II. bit
BIT, BIT VARYING [lungime]
III. numeric:
exact:
NUMERIC [precizie [, scala]]
DECIMAL sau DEC [precizie [, scala]]
INTEGER sau INT
SMALL INTEGER sau SMALLINT
aproximativ:
FLOAT [precizia]
REAL
DOUBLE PRECISSION
IV. data si ora
DATE
TIME [precizie_ora] [WITH TIME ZONE]
TIMESTAMP [precizie_ora] [WITH TIME ZONE]
V. interval
INTERVAL camp_data_ora }
Parametrii unui tip de date se scriu intre paranteze rotunde dupa cuvantul care il defineste. Precizia se exprima ca numar de cifre din mantisa (partea intreaga). Scala se exprima ca numar de cifre din exponent (numar de zecimale). Numarul de cifre din campul principal este separat prin virgula de numarul de zecimale prin care se exprima o valoare.
Se poate impune si conditia ca obligatoriu un camp sa fie completat, folosind termenul-cheie NOT NULL. Este cazul cheii primare dintr-o relatie sau a unei chei alternative.
Campurile din instructiunea INTERVAL sunt de forma:
YEAR|MONTH|DAY|HOUR|MINUTE [precizie]
Exemplu:
Instructiunea SQL:
INTERVAL YEAR(1) TO MONTH
semnifica un interval de la 0 ani si 0 luni (timpul prezent) la 9 ani si 11 luni. Numarul de ani poate fi scris in acest caz cu o singura cifra.
4. INSTRUCTIUNI SQL DE DEFINIRE A BAZEI DE DATE
Instrutiunile de creare, modificare si distrugere a structurilor din BD sunt urmatoarele:
CREATE DATABASE DROP DATABASE
CREATE TABLE ALTER TABLE DROP TABLE
CREATE DOMAIN ALTER DOMAIN DROP DOMAIN
CREATE SCHEMA DROP SHEMA
CREATE VIEW DROP VIEW
CREATE INDEX DROP INDEX
Formatul de baza dat de ISO al instructiunii de creare a unui tabel in BD este urmatorul:
CREATE TABLE nume_tabel
[ON UPDATE actiune_referentiala]
[ON DELETE actiune_referentiala]
EXEMPLU:
CREATE TABLE agenti(
cod_agent DEC(3,0) NOT NULL UNIQUE,
nume VARCHAR(20) NOT NULL,
prenume VARCHAR(20) NOT NULL,
cnp DEC(13,0) NOT NULL,
filiala VARCHAR(10),
salariu DEC(5,2),
vechime SMALLINT DEFAULT 0
PRIMARY KEY (cod_agent));
CREATE TABLE proprietati(
cod_proprietate DEC(5,0) NOT NULL UNIQUE,
zona VARCHAR(20) NOT NULL,
tip VARCHAR(10) NOT NULL,
pret INT,
cod_agent DEC(3,0),
cod_proprietar DEC(6,0) NOT NULL,
PRIMARY KEY (cod_proprietate),
FOREIGN KEY (cod_agent) REFERENCES agenti ON DELETE SET NULL ON UPDATE CASCADE,
FOREIGN KEY (cod_proprietar) REFERENCES proprietari);
Observatii:
1. Tipul datelor poate fi declarat separat sub forma unui domeniu de valori si utilizat pentru mai multe variabile de acelasi tip:
CREATE DOMAIN nume_domeniu AS tip_de_date
[DEFAULT optiune_prestabilita]
[CHECK (conditie)];
La crearea tabelului se specifica in locul tipului datelor, numele domeniului scris cu majuscule.
2. Instructiunea DROP elimina articole din BD si poate avea doua optiuni:
DROP ARTICOL nume_articol [RESTRICT|CASCADE]
Optiunea RESTRICT nu va permite stergerea articolului daca de acesta depind alte date din BD. Se evita astfel pierderea de date.
Optiunea CASCADE este una extrema care determina stergerea acelui articol din BD precum si a tuturor datelor care depindeau de acesta. Este utila pentru actualizarea structurii BD dupa o perioada mai lunga de timp sau atunci cand se reproiecteaza aceasta.
3. Indexul este o structura care ofera acces accelerat la inregistrarile din BD pe baza valorilor dintr-una sau mai multe coloane ale unui tabel, imbunatatind astfel performantele de interogare:
CREATE [UNIQUE] INDEX nume_index
ON nume_tabel (coloana [ASC|DESC] [, .])
Utilizarea indexurilor trebuie facuta cu oarecare rezerve intrucat solicita mai multe resurse din partea serverului de BD.
4. Pentru modificarea structurii de coloane a unui tabel deja creat, se foloseste instructiunea:
ALTER TABLE nume_tabel
[ADD [COLUMN] nume_coloana tip_de_date [NOT NULL] [UNIQUE]
[DEFAULT optiune_prestabilita] [CHECK (conditie)]
[DROP [COLUMN] nume_coloana [RESTRICT | CASCADE]]
[ADD [CONSTRAINT [nume_constrangere]] definitie_constrangere]
[DROP CONSTRAINT nume_constrangere [RESTRICT | CASCADE]]
[ALTER [COLUMN] SET DEFAULT optiune_ prestabilita]
[ALTER [COLUMN] DROP DEFAULT]
5. INSTRUCTIUNI SQL DE MANIPULARE A BAZEI DE DATE
Instructiunile de manipulare a datelor din BD sunt urmatoarele:
SELECT pentru interogarea BD;
INSERT pentru introducere de noi inregistrari in BD;
UPDATE pentru reactualizarea BD;
DELETE pentru stergerea de inregistrari din BD.
Fiecare dintre aceste instructiuni contine dupa cuvantul-cheie de definitie diverse clauze cu multiple optiuni. De aceea le vom studia pe fiecare in parte.
5.1 INSTRUCTIUNEA SELECT
Instructiunea SELECT de interogare a BD are forma urmatoare:
SELECT [DISTINCT|ALL]
FROM nume_tabel_sau_vedere [alias] [,.]
[WHERE conditie]
[GROUP BY lista_de_coloane] [HAVING conditie]
[ORDER BY lista_de coloane];
Exemplu:
SELECT *
FROM agenti
WHERE filiala = centru
Rezultatul acestei comenzi SQL va fi afisarea tuturor inregistrarilor din tabelul "agenti" corespunzatoare filialei "centru".
Cuvantul-cheie DISTINCT elimina in cadrul interogarii eventualele dubluri din BD.
In expresiile incluse in comenzile SQL se folosesc operatori scalari si functii specifice:
operatorii aritmetici: +,-,*,/
functia de lungime: BIT_LENGTH, OCTET_LENGTH, CHAR_LENGTH
operatorul de transformare a tipului de date: CAST(tip1 AS tip2)
concatenarea de siruri:
identificarea utilizatorului curent: USER
identificarea sesiunii: SESSION_USER
identificarea sistemului: SYSTEM_USER
scrierea cu litere mici: LOWER
scrierea cu majuscule: UPPER
data sau timp curent: CURRENT_TIME,CURRENT_DATE, CURRENT_TIMESTAMP.
Limbajul SQL mai accepta cinci functii de grup care se aplica pe o coloana intreaga si genereaza o singura valoare:
COUNT numarare
SUM sumare
AVG calculul valorii medii
MIN deducerea valorii minime
MAX deducerea valorii maxime.
Optiunea GROUP BY permite gruparea inregistrarilor cu aceleasi valori pe coloanele precizate in lista de coloane si aplicarea functiilor de grup pe aceste grupuri.
O instructiune SELECT integrata in alta interoare SELECT intr-o clauza WHERE sau HAVING se numeste subinterogare.
Pentru a selecta date din mai multe tabele, in cadrul clauzelor WHERE si HAVING, se folosesc operatiile cu multimi: UNION, INTERSECT, EXCEPT.
Clauza WHERE poate fi urmata de unul din urmatoarele 5 predicate:
Compararea valorilor folosind operatorii de comparare (=, <, >, <=, >=, <> ISO, !=) si/sau operatorii logici (AND, OR, NOT);
Testarea domeniului de valori al unei expresii (BETWEEN/NOT BETWEEN);
Testarea apartenentei la o multime de valori (IN/ NOT IN);
Corespondenta la un anumit model (LIKE/NOT LIKE);
Testarea conditiei de null (IS NULL/IS NOT NULL).
Testele LIKE/NOT LIKE folosesc simbolul "procent" (%) pentru reprezentarea unui sir de zero sau mai multe caractere si caracterul "liniuta de subliniere" pentru reprezentarea oricarui caracter singular.
Exemplu: Clauza:
WHERE nume='A%'
selecteaza toate inregistrarile al caror nume incepe cu litera A.
Clauza ORDER BY poate ordona inregistrarile crescator sau descrescator, alfabetic sau numeric, pe baza uneia sau a mai mutor coloane. Prima coloana constituie cheia majora de sortare, iar urmatoarele sunt chei minore de sortare.
5.2 INSTRUCTIUNEA INSERT
Instructiunea INSERT de introducere a uneia sau a mai multor inregistrari in BD foloseste urmatoarele doua formate:
I. INSERT INTO nume_tabel [(lista_de_coloane)]
VALUES (lista_de_valori);
II. INSERT INTO nume_tabel [(lista_de_coloane)]
SELECT .;
Cel de al doilea format reprezinta o instructiune combinata INSERT . SELECT deci poate folosi toate clauzele instructiunii SELECT pentru copierea mai multor inregistrari din tabele ale BD printr-o singura comanda SQL.
Exemple:
INSERT INTO agenti (nume, prenume, cnp, filiala)
VALUES ('popescu', 'marius', '1900102111111', 'iasi');
INSERT INTO personal
VALUES ('ionescu', 'max', '1900102111111', 'iasi', '0');
5.3 INSTRUCTIUNEA UPDATE
Pentru actualizarea datelor din BD se foloseste instructiunea UPDATE cu urmatorul format:
UPDATE nume_tabel
SET coloana_1 = valoarea_1[, coloana_2 = valoarea_2 .]
[WHERE conditie];
Exemplu:
Pentru majorarea cu 5% a salariilor tuturor agentilor cu vechime de minimum 3 ani se scrie comanda SQL:
UPDATE agenti
SET salariu = salariu*1,05
WHERE vechime >= 3;
5.4 INSTRUCTIUNEA DELETE
Pentru stergerea unor inregistrari din BD se foloseste instructiunea DELETE cu urmatorul format:
DELETE FROM nume_tabel
[WHERE conditie];
6. VEDERI
Prin definitie, o vedere este o relatie virtuala produsa la cerere prin operatii relationale, folosind relatiile existente in baza de date.
O vedere este creata prin instructiunea CREATE VIEW:
CREATE VIEW nume_vedere [(nume_coloana [, .])]
AS SELECT [WITH [CASCADED|LOCAL] CHECK OPTION];
Pentru a crea vederea, utilizatorul trebuie sa aiba drepturi de interogare (SELECT) asupra tuturor tabelelor implicate in subselectie si drepturi de utilizare (USAGE) asupra tuturor coloanelor solicitate.
O vedere care restrange accesul la inregistrarile selectate dintr-unul sau mai multe tabele fara restrictionarea coloanelor, se numeste vedere orizontala.
O vedere verticala restrange accesul la anumite atribute (coloane) dintr-unul sau mai multe tabele. De exemplu, salariile sunt confidentiale si nu pot fi vizualizate de catre agenti intr-o vedere verticala. Acest lucru devine posibil intr-o vedere orizontala care ii permite fiecaruia sa citeasca propria inregistrare din BD.
Instructiunea CREATE VIEW se combina cu instructiunea SELECT si cu clauzele acesteia.
Exemplu
Managerul agentiei doreste sa cunoasca proprietatile gestionate de toti agentii, fara detalii specifice legate de cnp, salariu etc. Presupunem ca in BD exista un tabel "agenti" si un tabel "proprietati":
Agenti (cnp, nume, prenume, filiala, salariu, vechime);
Proprietati (nr_proprietate, zona, tip, suprafata, pret, adresa, cod_proprietar, cnp)
Vederea este creata prin comanda:
CREATE VIEW agenti_proprietati
AS SELECT nr_proprietate, nume_agent, prenume_agent, filiala
FROM agenti.nume, agenti.prenume, agenti.filiala, proprietati.nr_proprietate
WHERE agenti.cnp = proprietati.cnp
GROUP BY agenti.filiala;
O vedere este reactualizabila daca SGBD este capabil sa urmareasca orice rand sau coloana pana la relatia-sursa.
Vederile pot fi folosite pentru a crea noi vederi.
O vedere care face apel la mai multe tabele se numeste vedere unificata.
O vedere care utilizeaza clauza GROUP BY se mai numeste si vedere grupata.
Distrugerea unei vederi se face prin instructiunea:
DROP VIEW nume_vedere [RESTRICT CASCADE]
Optiunea CASCADE determina stergerea tuturor vederilor bazate pe vederea eliminata.
Inregistrarile dintr-o vedere care in urma reactualizarii BD sau inserarii de noi date satisfac sau nu mai satisfac clauza WHERE vor intra sau vor iesi din acea vedere, fiind numite si randuri migratoare. Clauza WITH CHECK OPTION migrarea unui rand in afara vederii, ceea ce asigura o mai buna securitate a datelor incluse in vedere decat in tabelele BD.
7. TRANZACTII
ISO defineste un model de tranzactii bazat pe doua instructiuni SQL: COMMIT (executare) si ROLLBACK (revenire).
Tranzactia este o unitate logica de lucru care contine una sau mai multe comenzi SQL.
Initierea tranzactiei poate fi facuta de catre o persoana sau un program printr-o comanda de initiere de tip SELECT; INSERT; UPDATE.
Pana la completarea tranzactiei, efectele ei nu sunt vizibile.
Incheierea tranzactiei se poate realiza in unul din urmatoarele 4 moduri:
a. Prin instructiunea COMMIT, modificarile din BD sunt permanente.
b. Prin instructiunea ROLLBACK, se abandoneaza modificarile initiate si BD ramane nemodificata.
c. Finalizarea cu succes a programului incheie tranzactia si modificarile au efect chiar daca nu s-a executat instructiunea COMMIT.
d. Abandonarea tranzactiei fara rularea instructiunii ROLLBACK atunci cand se termina anormal programul respectiv.
Formatul unei tranzactii este urmatorul:
SET TRANSACTION
[READ ONLY |READ WRITE] |
[ISOLATION LEVEL READ UNCOMMITTED | READ UNCOMMITTED |
REPEATABLE READ | SERIALIZABLE];
8. CONTROLUL ACCESULUI
Limbajul SQL foloseste doua instructiuni pentru controlul accesului la BD: GRANT si REVOKE.
Mecanismul de securitate al BD se bazeaza pe conceptele de:
a. identificator de autorizatie
b. posesiune
c. privilegiu.
Fiecarui utilizator la BD i se aloca un identificator de autorizatie, asociat cu o parola, utilizat pentru a determina drepturile de acces ale acestuia la obiectele din BD.
Fiecare obiect din BD este proprietatea celui care l-a creat (drept de posesiune).
In clauza AUTHORIZATION din schema careia ii apartine obiectul apare identificatorul proprietarului.
Prin privilegii se inteleg actiunile care ii sunt permise unui utilizator al BD:
SELECT
INSERT [(nume_coloana [, ])]
UPDATE [(nume_coloana [, ])]
DELETE
REFERENCES [(nume_coloana [, ])]
USAGE
Formatul instructiunii GRANT este urmatorul:
GRANT
ON nume_obiect
TO
[WITH GRANT OPTION]
Cuvantul cheie PUBLIC ii desemneaza pe toti utilizatorii BD.
Clauza WITH GRANT OPTION permite transmiterea privilegiilor spre alti utilizatori.
Formatul instructiunii REVOKE este urmatorul:
REVOKE [GRANT OPTION FOR]
ON nume_obiect
FROM [RESTRICT CASCADE]
Optiunea GRANT OPTION FOR permite retragerea separata a drepturilor acordate altor utilizatori prin clauza WITH GRANT OPTION.
