SQL

SQL

Limbajul SQL (Structured Query Language) se bazeaza pe studiile lui E.F. Codd, prima implementare a acestui limbaj datand din 1970.

SQL este un limbaj complet standardizat si se poate utiliza pentru a accesa baze de date Oracle, SQL Server, DB2, MySQL s.a.

SQL utilizeaza o sintaxa foarte simpla si usor de folosit. Comenzile SQL sunt grupate in cinci categorii, astfel:

Limbajul de interogare - permite regasirea liniilor memorate in tabelele bazelor de date. Comanda utilizata este SELECT.

Limbajul de manipulare a datelor - permite modificarea continutului tabelelor. Comenzile utilizate sunt:

INSERT - pentru adaugarea de noi linii intr-un tabel;

UPDATE - pentru modificarea valorilor memorate intr-un tabel;

DELETE - pentru stergerea liniilor dintr-un tabel.

Limbajul de definire a datelor - permite definirea structurii tabelelor ce compun bazele de date. Comenzile utilizate sunt:

CREATE - pentru crearea structurii unei baze de date sau a unui tabel;

ALTER - pentru modificarea structurii unei baze de date sau a unui tabel;

DROP - pentru stergerea structurii bazei de date;

RENAME - pentru schimbarea numelui unui tabel;

Limbajul de control al tranzactiilor. Comenzile utilizate sunt:

COMMIT - pentru ca modificarile efectuate asupra bazei de date sa devina permanente;

ROLLBACK - permite renuntarea la ultimele modificari asupra bazei de date;

SAVEPOINT - pentru definirea unui punct de salvare la care se poate reveni, renuntand la modificarile facute dupa acest punct asupra bazei de date.

Limbajul de control a datelor - permite definirea si modificarea drepturilor asupra bazelor de date. Comenzile utilizate sunt:

GRANT - pentru acordarea unor drepturi altor utilizatori asupra bazei de date;

REVOKE - pentru anularea anumitor drepturi ale utilizatorilor.

Principalele elemente care compun o comanda SQL

NUME - toate obiectele dintr-o baza de date, tabele, coloane, indecsi etc., au un nume. Numele poate fi orice sir de maximum 30 de litere, cifre si caractere speciale ("_", "#", "$"), primul caracter fiind obligatoriu o litera.

CUVINTE REZERVATE - la fel ca in orice limbaj si in SQL exista o lista de cuvinte rezervate. Aceste cuvinte nu pot fi utilizate in alt scop decat cel definit initial.

CONSTANTE - o constanta este o valoare fixa care nu poate fi modificata. Exista:

constante numerice, de exemplu 4, 12.34, .9 (se observa ca daca un numar real are partea intreaga egala cu zero, atunci ea nu mai trebuie precizata);

constante alfa-numerice (sau sir de caractere). Constantele sir de caractere sunt scrise intre apostrofuri si sunt case-sensitive ('abc', 'baza de date')

VARIABILE - o variabila este o data care poate avea in timp valori diferite. O variabila are intotdeauna un nume pentru a putea fi referita. SQL accepta 2 tipuri de variabile:

variabile asociate numelor coloanelor;

variabile sistem.

EXPRESII - o expresie este formata din variabile, constante si functii. Operatorii care pot fi utilizati sunt impartiti astfel:

operatori aritmetici;

operatori alfa-numerici;

operatori de comparatie;

operatori logici.

Cu ajutorul comenzii SELECT se pot realiza urmatoarele tipuri de operatii:

Selectia - consta in filtrarea liniilor care vor fi afisate;

Proiectia - consta in alegerea doar a anumitor coloane pentru a fi afisate;

Uniune (JOIN) - consta in prelucrarea datelor din doua sau mai multe tabele "legate" conform unor reguli precizate;

Sintaxa:

SELECT [DISTINCT] lista_de_expresii

FROM nume_tabel

WHERE conditie - clauza optionala

ORDER BY criterii_sortare_rezultat - clauza optionala;

Efectul:

Se parcurg rand pe rand liniile tabelului specificat in clauza FROM. Din fiecare linie, continand date pentru care conditia aflata in clauza WHERE este adevarata, va rezulta o linie in raspunsul cererii.

In cazul in care clauza WHERE lipseste, toate liniile tabelului din clauza FROM vor avea o linie corespondenta in rezultatul cererii. Linia de rezultat este compusa pe baza listei de expresii aflata pe clauza SELECT.

Daca exista cuvantul cheie DISTINCT, din rezultat se elimina liniile duplicat.

Inainte de a trimite rezultatul, serverul il sorteaza in functie de criteriile specificate de clauza ORDER BY. In cazul in care clauza ORDER BY lipseste, liniile din rezultat sunt intr-o ordine independenta de continutul lor sau de ordinea in care ele au fost adaugate in tabel.

Numarul coloanelor din rezultat este egal cu numarul expresiilor din lista clauzei SELECT. Aceste expresii dau si numele coloanelor din rezultat.
Evaluarea valorii de adevar a conditiei din WHERE se face doar pe baza datelor aflate pe linia respectiva. Deoarece parcurgerea liniilor specificata de o cerere SELECT se face dupa un plan de executie generat de server, folosirea clauzei ORDER BY este obligatorie in cazul in care se doreste un rezultat sortat dupa anumite criterii.

Nume de coloane sau *

Ex.1. SELECT nume, prenume, data_nastere

FROM studenti_tabel

Ex.2. SELECT *

FROM studenti_tabel

Expresii aritmetice

Ex.3. SELECT tip, suma, (suma+20)*1.1

FROM bursa_tabel

Expresii concatenate

Ex.4. SELECT `Specializarea` ||nume||` are codul`,cod_spec

FROM specializare_tabel

Alias de coloana

nu poate fi mai lung de 30 de caractere;

incepe cu o litera si contine litere, cifre sau e pus intre ghilimele;

nu poate fi folosit decat in cererea curenta;

sistemul nu stocheaza in baza de date sau altundeva aceste nume alternative.

Ex.5. SELECT tip AS "Tip bursa", `are valoarea` ||suma|| `.lei` AS Descriere

FROM bursa_tabel

Clauza WHERE

Sintaxa: WHERE expresie_logica

Ex.6. SELECT nume, grupa, codspec

FROM studenti_tabel

WHERE an = 4;

Operatori de comparare: (< )mai mic, (<=) mai mic sau egal, (>) mai mare, (>=) mai mare sau egal, (<>, !=, ^=) diferit.

Conditii compuse: (AND, OR, NOT).

Operatorul BETWEEN

Sintaxa: expresie BETWEEN valoare.min AND valoare.max

Ex. 7. SELECT nume, an, punctaj

FROM studenti_tabel

WHERE punctaj BETWEEN 2000 AND 4000;

Operatorul IN

Sintaxa: expresie IN (val_1, val_2, val_3,, val_n)

Ex. 8. SELECT nume, data_nastere

FROM studenti_tabel

WHERE grupa IN (113, 114, 116)

Operatorul IN ignora valorile nule din lista.

Pentru a putea folosi in clauza WHERE siruri de caractere si date calendaristice, acestea trebuie introduse intre ghilimele simple (` `), singura exceptie fiind constantele numerice.

Ex.9. - listeaza toti angajatii care sunt pe pozitia de contabil:

SELECT *

FROM angajati_tabel

WHERE functie = `CONTABIL`

Ex.10. - listeaza toti angajatii care au data de angajare 17-dec-1990:

SELECT *

FROM angajati_tabel

WHERE data_angajare=`17-dec-1990`

Ex.11. - listeaza toti angajatii care sunt in departamentul 10:

SELECT *

FROM angajati_tabel

WHERE nr_dept = 10

Operatorul LIKE

Daca nu se cunoaste valoarea exacta cautata, cu ajutorul conditiei LIKE putem sa selectam randuri care se potrivesc cu un model specificat de caractere. Pentru construirea modelului sirului cautat pot fi folosite doua simboluri:

% - orice secventa de 0 sau mai multe caractere;

_ - un singur caracter.

Ex.12. - listeaza toti angajatii al caror nume incepe cu litera S:

SELECT *

FROM angajati_tabel

WHERE nume LIKE `S%`

Ex.13. - listeaza toti angajatii care au numele din 4 caractere:

SELECT *

FROM angajati_tabel

WHERE nume LIKE `_ _ _ _`

Ex.14. - listeaza toti angajatii care au al doilea caracter din nume `O`:

SELECT *

FROM angajati_tabel

WHERE nume LIKE `_O%`

Conditia IS NULL

Pentru a verifica valori de tip NULL exista conditia IS NULL sau negarea acesteia IS NOT NULL.

O valoare nula nu este la fel cu 0 care este un numar. Daca valoarea NULL este utilizata intr-o comparatie, trebuie sa se foloseasca IS NULL sau IS NOT NULL, altfel rezultatul este intotdeauna FALSE.

Ex.15. - listeaza toti angajatii care nu au comision:

SELECT *

FROM angajati_tabel

WHERE comision IS NULL

Negarea expresiilor:

Prioritate de executie:

1. operatorii aritmetici;
2. operatorii de concatenare;
3. conditiile de comparare;
4. IS NULL, LIKE, IN;
5. BETWEEN;
6. operatorul logic NOT;
7. operatorul logic AND;
8. operatorul logic OR.

Dar ordinea se poate modifica daca se folosesc paranteze.

Clauza ORDER BY

Cu ajutorul clauzei ORDER BY randurile vor fi afisate in ordinea solicitata (cu toate acestea nu se va modifica ordinea interna a randurilor din baza de date). Ea trebuie sa fie ultima clauza din cerere.

Sintaxa: SELECT nume_coloana

FROM nume_tabel

WHERE conditie ORDER BY

[ASC/DES];

Ex.16. - listeaza toti angajatii ordonandu-i dupa data angajarii (in ordine descrescatoare)

SELECT *

FROM angajati_tabel

ORDER BY data_angajare DESC;

Randurile pot fi ordonate dupa mai multe coloane. Numarul maxim de coloane dupa care se pot ordona randurile este numarul maxim de coloane existente in tabel.

Comenzi pentru actualizarea bazelor de date

SQL prezinta comenzi specifice pentru modificarea continutului unui tabel, intelegand prin aceasta trei actiuni prin care se actualizeaza baza de date:

adaugarea de noi linii la cele existente intr-un tabel;

Comanda utilizata pentru adaugarea de inregistrari este INSERT cu formatul:

INSERT

INTO nume_tabel (lista de campuri)

VALUES (lista de valori)

Ex.17. INSERT

INTO clienti_tabel (CodClient, NumeClient, AdresaClient)

VALUES (1009, "UPB-SRL", "spl.Indep.313")

Pentru o utilizare mai usoara putem folosi comanda DESCRIBE clienti_tabel care va afisa campurile tabelului in ordinea lor, precum si tipul fiecarui camp.

stergerea unor linii existente;

Stergerea uneia sau mai multor inregistrari se realizeaza prin comanda DELETE care are sintaxa:

DELETE

FROM nume_tabel

WHERE conditie

Ex.18. DELETE

FROM clienti_tabel

WHERE CodClient=1003

modificarea valorii unui atribut.

Comanda utilizata este UPDATE care are formatul general:

UPDATE nume_tabel

SET atribut = expresie

WHERE conditie

Modificarea se va produce pentru toate liniile tabelului care indeplinesc conditia specificata.

Ex.19. UPDATE facturi_tabel

SET ValoareTotala = 3000

WHERE NrFactura = 1122

Functii

Functiile se clasifica in doua tipuri:

functii referitoare la o singura inregistrare (LCASE() - converteste caracterele din mari in mici, UCASE() - converteste caracterele din mici in mari, CONCAT() - echivalent cu operatorul de concatenare ||, LEN() - returneaza numarul de caractere etc.);

functii referitoare la mai multe inregistrari (AVG() - valoarea medie, COUNT() - numarul de randuri, MAX(), MIN(), SUM() etc).

Functiile referitoare la o singura inregistrare returneaza un singur rezultat pentru fiecare rand al tabelului, pe cand functiile referitoare la mai multe inregistrari returneaza un singur rezultat pentru fiecare grup de inregistrari din tabel.

Ex.20. SELECT AVG(salariu), MAX (salariu), MIN(salariu)

FROM angajati_tabel

WHERE functie = `VANZATOR`

Comenzi pentru descrierea datelor

Comanda CREATE TABLE creeaza un tabel vid, cu o anumita structura. De exemplu:

Ex.21.

CREATE TABLE CLIENTI

(CodClient INT(7) NOT NULL,

NumeClient CHAR(25) NOT NULL,

AdresaCLient CHAR(25),

CodPostal INT(4) NOT NULL DEFAULT 1100,

PRIMARY KEY(CodClient)

Atributul CodClient este de tip INT, valoarea sa fiind reprezentata pe sapte pozitii. Celelalte atribute contin siruri de caractere. Valorile pentru CodClient, NumeClient si CodPostal nu pot fi nule. Pentru CodPostal valoarea implicita, pe care o ia automat la adaugarea unei linii in tabelul CLIENTI, este 1100.

In calitate de cheie primara a relatiei a fost investit atributul CodClient.

Cele mai multe dialecte SQL admit urmatoarele tipuri de date:

Exista posibilitatea adaugarii ulterioare a unui nou atribut la cele existente, stergerii unui atribut sau de modificare a tipului sau lungimii sale. Operatiunea nu este atat de frecventa fiind recomandabil sa se desfasoare cat mai rar; o buna analiza desfasurata in faza de proiectare a bazei de date elimina, de obicei, acest gen de probleme.

Daca in tabelul CLIENTI se doreste pastrarea si a codului fiscal al fiecarui furnizor, este necesara adaugarea atributului CodFiscal, care este un sir de caractere de lungime 8. Comanda utilizata este:

Ex.22.

ALTER TABLE CLIENTI

ADD CodFiscal CHAR(8)

Cheile straine sunt declarate cu ajutorul optiunii FOREIGN KEY. Pentru tabelul FACTURI_EMISE cheie primara este atributul NrFactura, in timp ce CodClient este cheie straina catre tabelul CLIENTI.

Ex.23.

CREATE TABLE FACTURI_EMISE

(NrFactura DECIMAL(8) NOT NULL,

DataFactura DATE,

CodClient DECIMAL(7) NOT NULL,

ValoareTotala DECIMAL(17) NOT NULL,

TVA DECIMAL(14),

PRIMARY KEY(NrFactura)

FOREIGN KEY(CodClienti) REFERENCES CLIENTI)

Stergerea unui tabel din baza de date este realizabila cu ajutorul comenzii DROP TABLE. De obicei aceasta comanda se utilizeaza atunci cand pe parcursul lucrului s-au creat tabele intermediare, temporare.

JOIN-URI

Pentru a prelucra date din mai multe tabele, forma de baza a unei instructiuni SELECT consta in adaugarea unei conditii de legatura (JOIN) in clauza WHERE.

Ex.24.

SELECT table1.column, table2.column

FROM table1, table2

WHERE table1.column = table2.column

Numele coloanei trebuie prefixat de numele tabelului in situatiile cand acelasi nume de coloana apare in mai multe tabele.

Un JOIN cu doua sau mai multe tabele care returneaza doar linii ce au valori echivalente pentru coloanele specificate este denumit SIMPLE JOIN sau INNER JOIN.

Ex.25.

SELECT column.name

FROM table_name1 INNER JOIN teble_name2

ON table_name1.column_name = table_name2.column.name;

Atunci cand un JOIN returneaza atat liniile care se potrivesc cat si cele care nu se potrivesc, acesta se numeste OUTER JOIN.

Exista trei tipuri de OUTER JOIN:

LEFT OUTER JOIN

Ex.26. - sunt afisati si acei angajati care nu au desemnat un id_dept:

SELECT a.nume, a.id_dept, d.denumire

FROM angajati a

LEFT OUTER JOIN departament d

ON a.id_dept = d.id_dept;

RIGHT OUTER JOIN

Ex.27. - sunt afisate si acele departamente care nu au angajati:

SELECT a.nume, a.id_dept, d.denumire

FROM angajati a

RIGHT OUTER JOIN departament d

ON a.id_dept = d.id_dept;

FULL OUTER JOIN

Ex.28. - sunt afisati si acei angajati care nu au un departament si acele departamente care nu au angajati:

SELECT a.nume, a.id_dept, d.denumire

FROM angajati a

FULL OUTER JOIN departament d

ON a.id_dept = d.id_dept;