baze date si altele

Administrare Baze de Date Web2010 - Conf.dr.ing. Dan Pescaru

1. Introducere MySql.

a. Ce este mySQL? b. Argumente in favoarea mySQL c. Instalare d. Instalare sub Windows e. Rulare sub Windows f. Instalare sub Linux g. Testarea instalarii

2. Algebra Relationala. Proiectarea si crearea unei baze de date a. Proiectarea unei baze de date b. Crearea unui dictionar de date c. Elemente de Algebra Relationala d. Normalizarea unei baze de date e. Crearea/Stergerea/Migrarea unei BD mySQL

3. Conectarea la serverul mySQL a. Conectarea la serverul mySQL b. Folosirea unui client tip linie de comanda c. Verificarea conectarii si a starii serverului d. Crearea si utilizarea unei baze de date e. Crearea unei baze de date f. Utilizarea unei baze de date

4. Tipuri de date. Tabele a. Tipuri de date b. Constante, identificatori, comentarii c. Operator i si functii mySql d. Creare si stergerea tabelelor

5. Gestiunea Accesului a. Sistemul de Privilegii mySql b. Proiectarea unui sistem de gestiune a accesului c. Accesul concurent la date d. Replicarea datelor

6. Gestionarea datelor. a. Inserarea datelor in tabele b. Comanda INSERT c. Utilizare comenzii INSERT d. Modificarea datelor din tabele e. Comanda UPDATE f. Utilizarea comenzii UPDATE g. Stergerea datelor din tabele h. Comanda DELETE i. Utilizarea comenzii DELETE

7. Interogari

a. Interogarea datelor b. Comanda SELECT c. Utilizarea conditiilor de selectie d. Ordonarea rezultatului e. Folosirea alias-urilor (poreclelor)

8. Interogari avansate a. Selectarea datelor din mai multe tabele. Operatia JOIN. b. Selectarea datelor din mai multe tabele. c. Tipuri de JOIN. d. Extragerea informatiilor de sumarizare

9. Biblioteci mySQL. Accesul concurent la date. a. Implemetarea programelor client. b. Suport ODBC. c. Suport JDBC. d. Biblioteca MySQL C. e. Suport Pearl. f. Suport PHP. g. Accesul concurent la date. h. Tranzactii. Blocarea/deblocarea unei tabele. i. Replicarea datelor.

10. Limbajul PHP a. Introducere b. Comparatia cu alte sisteme

11. PHP - Instalare a. Instalarea serverului Apache b. Instalarea suportului PHP pentru serverul Apache c. Testarea instalarii modulului de PHP d. Instalarea mySQL e. Informatii aditionale

12. Tipuri de date. Variabile a. Inserarea codului PHP intr-o pagina HTML. Taguri PHP b. Generarea dinamica de continut HTML c. Tipuri de date. Conversii de tip d. Identificatori. Constante e. Variabile utilizator. Variabile referinta. Domeniu de valabilitate f. Accesarea variabilelor din form-uri HTML

13. Operatori si expresii a. Operatori aritmetici b. Operatori pe siruri de caractere c. Operatori de asignare d. Operatori de referire e. Operatori de comparare f. Operatori logici g. Operatori pe biti h. Operatori aditionali i. Expresii

14. PHP - Structuri conditionale si repetitive a. Instructiuni conditionale b. Instructiunea if c. Instructiunea switch d. Instructiuni repetitive e. Instructiunea while f. Instructiunea do while g. Instructiunea for h. Tablouri PHP. Instructiunea foreach i. Instructiuni de control pentru repetitii

15. PHP - Functii. Formulare. Sesiuni. a. Functii b. Functii de biblioteca c. Functii utilizator d. Transmiterea parametrilor e. Functii recursive f. Reutilizarea codului g. Instructiunea require h. Instructiunea include i. Cotrolul sesiunilor j. Utilizare formulare si sesiuni pentru controlul utilizatorilor

16. Functii. Utilizare fisiere a. Functii de prelucrare tablouri b. Functii de prelucrare date calendaristice c. Functii matematice d. Functii de prelucrare siruri de caractere e. Utilizare fisiere f. Deschiderea si inchiderea unui fisier g. Scrierea intr-un fisier h. Citirea dintr-un fisier i. Functii speciale pentru fisiere

17. Utilizare MySQL in PHP. a. Limbajul SQL b. Baze de date. Tabele c. Utilizatori si privilegii d. Adaugarea, modificarea si stergerea datelor e. Interogari SELECT f. Utilizarea unei baze de date mySQL in PHP g. Conectarea la server h. Adaugarea de informatii i. Cautarea si afisarea rezultatelor

18. Aplicatii Multimedia e-Comerce Complexe a. Tipuri de aplicatii e-Comerce b. Brosuri de prezentare c. Comenzi prin Internet pentru marfuri si servicii d. Comert cu servicii si bunuri digitale

e. Aplicatii de tip "valoare adaugata" f. Riscuri si probleme legate de aplicatiile e-comerce

19. Probleme de securitate a. Utilizare cookies b. Restrictii legate de cookies c. Utilizare cookies in PHP d. Probleme la folosirea cookies e. Probleme de securitate la site-urile PHP f. Securitatea serverului de Web g. Securitatea instalarii PHP h. Gestiunea utilizatorilor in PHP i. Autentificarea utilizand serverul de Web j. Autentificarea folosind PHP

Cap1. mySQL - Introducere

1. Introducere 1.1. Ce este mySQL? 1.2. Argumente in favoarea mySQL 2. Instalare 2.1. Instalare sub Windows 2.2. Rulare sub Windows 2.3. Instalare sub Linux 2.4. Testarea instalarii

1. Introducere

1.1. Ce este mySQL?

MySQL este unul dintre cele mai populare sisteme de gestiune de baze de date (SGBD) in regim OpenSource.

Producatorul sau se numeste MySQL AB si este o companie a carei principala activitate consta in oferirea de servicii legate de acest SGBD.

Cele mai importante caracteristici ale MySQL sunt:

l Este un SGBD. Asigura gestiunea unei colectii structurate de date.

l Este un SGBD relational. Datele sunt organizate in tabele. Intre tabele sunt stabilite relatii si constrangeri de integritate. Pentru gestiunea datelor foloseste limbajul SQL (Structured Query Language) care este un standard in acest domeniu.

l Este un sistem client -server. Serverul de baze de date este separat logic si deseori fizic (pe calculatoare diferite) de programele client care asigura interfata operatiilor cu baza de date. Serverul MySQL suporta o paleta larga de programe client, scrise in diverse limbaje de programare.

l Este distribuit in formula Open Source Software. Aceasta formula permite oricarei persoane sa utilizeze si sa modifice programul, care poate fi luat gratuit de pe Internet. Formula utilizata pentru licentiere este GPL (GNU General Public License). Pentru mai multe informatii accesati site-ul http://www.gnu.org.

1.2. Argumente in favoarea mySQL

l Este rapid. In multe aplicatii este chiar mai rapid dacat alte sisteme foarte scumpe.

l Este fiabil. Asigura o buna protectie a datelor prin mecanisme specifice.

l Este usor de utilizat. Chiar daca ofera o paleta foarte bogata de functii, organizarea acestora si modul in care au fost create faciliteaza folosirea lor.

l Bazat pe fire de executie nucleu.

ABD

5

Poate folosi mai multe procesoare daca sistemul este multi-procesor. l Suport pentru multe limbaje de programare.

Interfete pentru clienti: C, C++, Eiffel, Java, Perl, PHP, Python. l Multiplatforma.

Sunt suportate urmatoarele sisteme de operare: Linux, Win95, Win98, WinNT, Win2000, AIX, DEC Unix, HP-UX, Mac OS XServer, OS2 Warp, Solaris, SCO UnixWare etc.

l Securitate ridicata. Utilizeaza parole criptate.

l Scalabilitate buna. Suporta baze de date de mari dimensiuni. Exista aplicatii cu aproximativ 5 miliarde inregistrari ruland pe MySQL.

2. Instalare

2.1. Instalare sub Windows

Pentru instalare se vor urma pasii:

1. Download fisier mysql-3.23.xx-win.zip de la adresa www.mysql.com/downloads. De preferabil se va aduce versiunea 45 (xx=45) sau mai noua, dar in varianta Stable Release. Diferenta intre subversiuni consta doar in mici cosmetizari si inlaturare de erori.

2. Dezarhivare fisier mysql-3.23.xx-win.zip intr-un director temporar (ex c:\temp). Daca nu aveti instalat un program de dezarhivare puteti lua WinZip sau WinRar gratuit de la adresa http://tucows.dnttm.ro

3. Se va rula programul setup.exe din directorul de dezarhivare. 4. Directorul de instalare va fi intotdeauna C:\mysql. In cazul in care, din diverse motive,

se doreste instalarea in alt director, se va instala in cel implicit si se va muta ulterior cu ajutorul Windows Explorer. Daca s-a mutat, spre exemplu, in directorul D:\progr\mysql, pornirea programului se va face cu comanda:

D:\progr\mysql\bin\mysqld --basedir D:\progr\mysql

5. Crearea fisierului de configurare se va face prin copierea fisierului my-example.cnf din directorul de instalare in radacina discului C sub numele c:\my.cnf.

6. Daca dupa instalare s-a schimbat directorul, se vor activa liniile (prin stergerea marcajului "#")

#basedir = d:/mysql/ #datadir = d:/mysql/data/

si se va inlocui referinta d:/mysql cu directorul unde a fost mutat programul.

2.2. Rulare sub Windows

In Winodws 95/98 se va lansa unul din serverele:

l mysqld - cu suport de depanare si alocare automata de memorie l mysqld-opt - serverul optimizat pentru sisteme cu procesoare Pentium

Serverul se va rula din Command Prompt (MS-DOS Prompt ) (meniul Start/Programs/MS-DOS Prompt ) tastand:

ABD

6

c:\mysql\bin\mysqld

sau

c:\mysql\bin\mysqld-opt

Eventualele mesaje de eroare vor fi gasite in fisierul c:\mysql\mysql.err. Pentru oprirea serverului se va tasta:

c:\mysql\bin\mysqladmin -u root shutdown

Sub Winodws NT/2000 se va instala serverul ca si serviciu (va fi disponibil automat chiar si dupa repornirea sistemului) tastand comanda:

c:\mysql\bin\mysqld-nt -install

Pentru activarea serverului se va tasta in linia de comanda (Command Prompt):

NET START mysql

respectiv, pentru oprire:

NET STOP mysql

2.3. Instalare sub Linux

Pentru instalare se vor urma pasii:

1. Download fisier mysql-3.23.xx.tar.gz de la adresa www.mysql.com/downloads. De preferabil se va aduce versiunea 45 (xx=45) sau mai noua, dar in varianta Stable Release. Diferenta intre subversiuni consta doar in mici cosmetizari si inlaturare de erori. Fisierul se va depune intr-un director temporar, de exemplu /tmp/kitmysql.

2. Dezarhivare fisier mysql-3.23.xx-win.tar.gz prin utilizarea comenzii:

gunzip -c mysql-3.23.xx.tar.gz | tar xvf -

La extragere se va crea automat subdirectorul mysql-3.23.xx.

3. Intrare in subdirector:

cd mysql-3.23.xx

4. Configurare instalare in directorul /usr/local/mysql prin:

./configure --prefix=/usr/local/mysql

5. Compilare executabil:

make

6. Instalare server:

make install

ABD

7

7. Crearea tabelelor implicite:

scripts/mysql_install_db

2.4. Testarea instalarii

Pentru a testa serverul se vor executa urmatoarele comenzi:

l c:\mysql\bin\mysqlshow

care va produce ca rezultat afisarea bazelor de date disponibile pe server:

Dupa cum se observa, la instalare sunt create automat doua baze de date: mysql pentru stocarea utilizatorilor si drepturilor de acces, respectiv test pentru a testa corectitudinea configuratiei curente.

l c:\mysql\bin\mysqlshow -u root mysql

care va produce ca rezultat afisarea tabelelor existente in baza de date mysql:

Database: mysql

l c:\mysql\bin\mysqladmin -u root shutdown

sau, sub NT,

l NET STOP mysql

care va opri serverul.

'2010 conf. dr. ing. Dan Pescaru

Databases

mysql

test

Tables

columns_priv

db

host

tables_priv

user

ABD

8

Cap.2. Algebra relationala. Proiectarea si normalizarea unei baze de date.

1. Elemente de algebra relationala 1.1. Introducere 1.2. Definitii 2. Normalizarea unei baze de date relationale 2.1. Proiectarea unei baze de date relationale 2.2. Dependente functionale 2.3. Forme normale 3. Proiectarea unei baze de date 3.1. Crearea unui dictionar de date

1. Elemente de algebra relationala

1.1. Introducere

Principiile algebrei relationale au fost stabilite de F. C. Codd n 1970. Ea reprezinta fundamentarea matematica a bazelor de date relationale. n conceptia relationala o baza de date este formata dintr-o colectie de relatii (tabele, fisiere de date) asupra carora se aplica o colectie de operatori pentru a gestiona datele continute de relatii. Un operator relational se aplica asupra unor tabele si va avea ca si rezultat tot o tabela. Potrivit algebrei relationale nu este permis accesul direct asupra nregistrarilor dintr-o tabela.

1.2. Definitii

n continuare se vor da cteva definitii a termenilor folositi de algebra relationala.

Constituantii (cmpuri, atribute, caracteristici) sunt informatiile elementare (atomice) ale unei relatii. Domeniul unui constituant (tipul) este ansamblul valorilor pe care l poate lua un constituant. Domeniul este un set de valori atomice.

N-upletul este un ansamblu de constituanti (X1, X2, , XN) sau de date (a1, a2, , an) cu ai in dom(X i). Un N-uplet de constituanti poate fi considerat ca si un constituant compus.

O relatie N-ara R(X) se defineste prin trei elemente:

1. precizarea unui N-uplet de constituanti (X1, X2, , XN); 2. definirea domeniului pentru fiecare constituant Xi;

ABD

9

3. definirea unui predicat logic care pentru orice N-uplet de date (a1, a2, , an) cu ai in dom(Xi) cu i de la 1 la N da o propozitie adevarata sau falsa.

Relatia R(X) este formata din ansamblul N-upletilor pentru care predicatul da propozitii adevarate. Gradul unei relatii este dat de numarul atributelor ce formeaza relatia.

Principalele caracteristici ale unei relatii sunt:

l N-upletii din relatie nu sunt ordonati. l Ordinea valorilor n N-upleti este data de ordinea definirii atributelor n

modelul relatiei. l Valorile atributelor din N-upleti sunt atomice. Un atribut nu poate avea valori

multiple. Sunt permise n schimb valori nule. l O relatie poate fi privita ca o specificare a unui tip compus . Definitia tipului

este data de structura relatiei.

O relatie este definita ca un set de N-upleti distincti (din acest punct de vedere corespunde tipului algebric multime).

Se numeste supercheie (SK) un grup de atribute care identifica n mod unic un N-uplet al relatiei. Exista relatii care au o singura supercheie formata din toate atributele. Se numeste cheie a relatiei R o supercheie minima, cu proprietatea ca, nlocuind sau stergnd orice atribut din ea, se obtine un grup de atribute care nu este supercheie pentru relatia data. Multimea cheilor unei relatii formeaza cheile candidat din care trebuie aleasa o cheie primara .

Cheia primara (PK - primary key) este o cheie aleasa de administratorul bazei de date pentru a identifica nregistrarile. De obicei se pe acest post se alege o cheie cu un numar minim de atribute, daca este posibil chiar un singur atribut. La nevoie se poate introduce o codifcare suplimentara fata de datele initiale ale problemei.

Se numeste cheie externa (FK - foreign key) un grup de atribute care constituie o cheie primara ntr-o alta relatie. O cheie externa ajuta la legarea datelor din cele doua relatii.

Pentru prelucrarea datelor din tabele teoria relationala defineste doua tipuri de operatori: operatori din teoria multimilor - UNION, INTERSECT, DIFERENCE, KHARTEZIAN PRODCT, respectiv operatori specifici algebrei relationale: SELECT, PROJECT, UNION. Acesti operatori stau la baza limbajelor de interogare relationale, dintre care cel mai cunoscut este SQL.

2. Normalizarea unei baze de date relationale

ABD

10

2.1. Proiectarea unei baze de date relationale

Normalizarea unei baze de date consta n principal n descompunerea modelului bazei de date n mai multe relatii astfel nct sa se reduca la maxim redundanta datelor si implicit sa elimine anomaliile de actualizare. Operatia de normalizare se bazeaza pe dependentele functionale care exista ntre datele unei aplicatii. Concret, pasii care trebuie facuti la proiectarea unei baze de date sunt urmatorii:

1. Analiza aplicatiei: analiza circuitului informational, studierea intrarilor si iesirilor, stabilirea claselor de utilizatori.

2. Analiza semanticii atributelor din entitati: identificarea atributelor si a sensului lor functional, gruparea atributelor n relatii pe entitati, stabilirea cheilor primare si externe.

3. Normalizarea relatiilor obtinute la punctul anterior: micsorarea redundantei prin gruparea atributelor n relatii conform definitiilor pentru formele normale, stabilirea de constrngeri pentru eliminarea anomaliilor de actualizare.

4. Scoaterea din relatiile principale a atributelor care au peste 70% valori nule.

2.2. Dependente functionale

O dependenta functionala , notata X->Y, ntre doua seturi de atribute a unei relatii R, specifica o constrngere asupra N-upletilor posibili. Ea se defineste n felul urmator: oricare ar fi t1, t2 in R, t1(X) = t2(X) => t1(Y) = t2(Y)

Se spune n acest caz ca X determina functional pe Y sau ca Y este dependent functional de X. Faptul ca X nu determina functional pe Z se va nota X |-> Z. Pentru determinarea dependentelor functionale se pot aplica urmatoarele reguli de inferenta (prin XY se noteaza concatenarea seturilor de atribute X si Y):

2.3. Forme normale

Normalizarea bazei de date presupune aducerea relatiilor gradual pe diverse forme normale. Fiecare forma normala preia constrngerile formei anterioare la care adauga noi conditii.

Forma normala 1 (1NF) cere:

l domeniul atributelor sa cuprinda valori atomice; se interzic cmpurile

ABD

11

compuse sau "relatii n relatie" l fiecare atribut din N-uplet trebuie sa aibe o singura valoare n domeniu.


l relatia sa fie n 1NF l orice atribut neprim (care nu face parte din cheia primara) din R sa fie

complet dependent functional de cheia primara a relatiei.

O alta varianta: se cere sa nu existe atribute care sa depinda numai de o parte a cheii primare.


l relatia sa fie n 2NF l nu exista nici un atribut neprim care sa fie dependent tranzitiv de cheia

primara a relatiei

Varianta: nu se permit atribute care nu fac parte din cheile candidat ale relatiei si care determina alte atribute. Se poate da si o definitie generalizata pentru forma 3NF. Orice atribut al relatiei ndeplineste:

l este complet dependent functional de orice cheie din R l este dependent netranzitiv de orice cheie din R

O varianta mai restrictiva este BCNF - Boyce Codd Normal Form . O relatie este n BCNF daca, pentru orice dependenta X->Y din R, X este o cheie candidat a lui R.

Forma normala 4 (4NF) cere: l relatia R sa fie n 3NF sau BCNF l R nu are dependente multivaloare

Relatia R(A,B,C) contine o dependenta multivaloare daca:

l A nu determina univoc pe B si C (A |-> B si A |-> C) l A conduce la valori multiple a lui B l A conduce la valori multiple a lui C l B si C sunt independente ntre ele.

Aducerea unei baze de date pe o forma normala superioara presupune extragerea unor atribute din relatiile existente si crearea pe baza lor a unor noi relatii astfel nct rezultatul sa respecte forma normala n cauza. Acest lucru duce la fragmentarea bazei de date dar elimina din anomaliile de actualizare si reduce spatiul pierdut datorita redundantei datelor.

ABD

12

3. Proiectarea unei baze de date

Proiectarea atenta a bazei de date este o premisa esentiala pentru succesul unei aplicatii. Proiectarea va incepe cu analiza problemei si identificarea datelor care intervin. Aceste date sunt apoi puse impreuna intr-o singura tabela temporara. Dupa analiza aplicatiei, primul pas consta in normalizarea bazei de date conform principiilor enuntate anterior. Chiar daca aceasta normalizare este un proces complex si de durata, el contribuie esential la evitarea problemelor si complicatiilor ulterioare. Ca si rezultat se vor obtine mai multe tabele in locul tabelei tempoarare de la analiza. Pentru acestea se vor alege apoi cheile primare. Dupa efectuarea normalizarii, se vor lua in considerare si alti factori care influenteaza proiectarea:

l Considerente de securitate - pot cere scoaterea unor date sensibile din tabele accesate public, chiar daca normalizarea nu o cerea.

l Viteza de cautare a informatiilor - este un factor esential care poate cere chiar incalcarea unor forme normale, prin repetarea datelor in tabele.

l Viteza de adaugare si modificare a datelor - poate cere renuntarea la unii indecsi sau chiar reorganizarea datelor.

In general cel mai important criteriu ramane totusi spatiul pe disc. Chiar daca in zilele noastre discurile au capacitati foarte mari, o baza de date cu redundanta mare, pe langa ca ocupa inutil foarte mult spatiu, este si foarte greu de accesat (viteza de cautare in tabele este in general proportionala cu dimensiunea acesteia, daca nu sunt luate in considerare fisierele index).

3.1. Crearea unui dictionar de date

In urma procesului de proiectare in general va trebui intocmita o documentatie pentru implementare. Cea mai utilizata forma este aceea de Dictionar de Date. Un dictioanar de date cuprinde:

l Descrierea scopului bazei de date si utilizarii acesteia. l Referiri la aspecte precum: dimensiunea prevazuta a diverselor tabele,

fisiere index necesare etc. l Descrierea detaliata a fiecarei tabele, incluzand toate campurile, tipul

acestora, comentarii privind utilitatea etc. l Codul sursa SQL pentru crearea structurii: bazei de date, tabelelor,

utlizatorilor si drepturilor, indecsilor, vederilor etc. Codul de obicei este sub forma unuia sau mai multe fisiere text cu extensia SQL.

Descrierea tabelelor se poate face sub urmatoarea forma:

Pesoane Camp Tip Constrangeri Comentarii

ABD

13

Indecsi necesari pentru cautari: buletin, nume.


buletin caracter(8) cheie primara numarul de buletin (cu tot cu serie)

nume caracter(24) necesar (not null) numele de familie

prenume caracter(32) necesar (not null) prenumele

data_n data data nasterii

BD

14

Cap.3. Conectarea la serverul mySQL

1. Conectarea la serverul mySQL 1.1. Folosirea unui client tip linie de comanda 1.2. Verificarea conectarii si a starii serverului 2. Crearea si utilizarea unei baze de date 2.1. Crearea unei baze de date 2.2 Utilizarea unei baze de date

1. Conectarea la serverul mySQL

1.1. Folosirea unui client tip linie de comanda

Cel mai facil client pentru conectarea la serverul de mySQL este cel tip linie de comanda care face parte din kit-ul standard de instalare mySQL. El se numeste mysql.exe si permite conectarea atat local cat si la un server la distanta.

Sintaxa de apel este:

> mysql -h nume_calculator_server -u nume_utilizator -p

El va fi apelat din subdirectorul bin al directorului de instalare (de obicei c:\mysql\bin). Semificatia parametrilor de apel este urmatoarea:

l -h nume_calculator_server - este numele masinii pe care se gaseste instalat serverul mysql. Daca conectarea se face local se poate renunta la optiunea -h sau se poate folosi 'localhost' pentru identificarea masinii.

l -u nume_utilizator - este numele utilizatorului (contului) care acceseaza serverul. El va stabili drepturile de acces la bazele de date de pe server. Daca conectarea se face local numele poate lipsi (conectare anonima), conectrea facandu-se implicit cu utilizatorul ODBC (cu drept de administrator). Din acest motiv trebuie limitat accesul utlizatorilor la masina pe care este instalat serverul. La conectarea de la distanta numele de utilizator nu poate fi omis. In general conectarea se face specificand complet numele utilizatorului si al masinii: root@localhost sau adi@serverul_meu.domeniu.ro.

l -p - forteaza citirea unei parole la conectare. Poate lipsi daca utilizatorul respectiv nu are stabilita inca o parola sau daca conectarea se face local in mod anonim. Parola efectiva se va citi dupa executarea comenzii.

Deconectarea clientului se poate face cu comanda QUIT.

Clientul mysql.exe se poate utiliza si pentru a rula un fisier text de comenzi SQL. Pentru aceasta se va utiliza sintaxa:

ABD

15

> mysql -h nume_calculator_server -u nume_utilizator -p < nume_fisier.sql

Daca fisierul nu se gaseste pe calea curenta, aceasta cale trebuie adaugata inaintea numelui fisierului.

1.2. Verificarea conectarii si a starii serverului

Pentru verificarea conectarii se poate rula o interogare foarte simpla care va avea ca rezultat afisarea versiunii programului si a datei curente pe server:

mysql>SELECT VERSION(), CURRENT_DATE;

Comanda anterioara este echivalenta cu:

mysql> select -> Version() -> , -> CURRENT_DATE;

O alta interogare utila este "SHOW". Ea poate fi utilizata pentru obtinerea de diverse informatii despre server. Spre exemplu, daca se doreste vizualizarea informatiilor despre bazele de date disponibile se va rula comada:

mysql>SHOW databases;

In aceiasi maniera se pot obtine informatii despre tabelele apartinand unei baze de date (spre exemplu baza de date 'mysql' creata automat la instalare):

mysql>SHOW tables form mysql;

sau despre coloanele unei tabele:

mysql>SHOW columns from user from mysql;

2. Crearea si utilizarea unei baze de date

2.1. Crearea unei baze de date

Crearea unei baze de date se poate face simplu apeland comanda:

mysql>CREATE DATABASE nume_db;

Crearea unei baze de date corespunde crearii unui director in care vor fi pastrate elementele specifice cum ar fi: tabele, indecsi, jurnale etc. Daca deja exista o

De retinut!

l Interogarile se vor termina cu ';'. Ele se pot despartii pe mai multe linii prin apasarea tastei ENTER. Ultima linie trebuie sa se termine cu ';'.

l Limbajul de interogare nu este de tip "case sensitive", deci nu tine cont de litere mari/mici. Practic se poate utiliza orice combinatie de litere mari sau mici.

ABD

16

baza de date cu numele specificat se va da un mesaj de eroare.

Pentru stergerea unei baze de date se poate utiliza sintaxa:

mysql>DROP DATABASE nume_db;

2.2 Utilizarea unei baze de date

Pentru selectarea unei baze de date se va folosi comanda USE. Prin aceasta se stabileste baza de date in contextul careia se vor executa interogarile ulterioare. Ea se va numi baza de date activa.

Sintaxa acestei comenzi este:

mysql>USE nume_db;

Chiar daca o singura baza de date poate fi activa la un moment dat, o interogare poate referi tabele si din alte baze de date prin utilizarea prefixarii cu numele bazei de date respective (ex. db2.tabela5).

Exemplu:

mysql>USE mysql; mysql>SHOW TABLES; mysql>SELECT * FROM db;

3. Tema 2.2

Creati o baza de date numita BANCA. Cautati directorul creat pe disc care corespunde bazei de date create. Rulati comanda: SHOW DATABASES; Stergeti baza de date si creati-o din nou. Selectati aceasta baza de date si rulati comanda: SHOW TABLES; Notati calea unde s-a creat directorul si raspunsul la ultima interogare.


ABD

17

Cap.4. Tipuri de date. Tabele.

1. Tipuri de date 1.1. Tipuri de date numerice 1.2. Tipuri de date data calendaristica si timp 1.3. Tipuri de date sir de caractere 1.4. Tipuri de date binare mari (BLOB) 1.5. Tipuri de date speciale 2. Constante, identificatori, comentarii 2.1. Constante 2.2. Identificatori 2.3. Comentarii 3. Operatori si functii mySQL 3.1. Paranteze de grupare 3.2. Operatori aritmetici 3.3. Operatori logici 3.4. Operatori de comparare 3.5. Functii pe siruri de caractere 3.6. Functii matematice 3.7. Functii de tip data calendaristica si timp 4. Crearea si stergerea tabelelor

1. Tipuri de date

Ca orice limbaj de programare, limbajul SQL suporta anumite tipuri de date. Ele pot fi in general impartite in: numerice, logice, data calendaristica, timp, siruri de caractere si date binare mari (BLOB - binary large object). Din motive de implementare, fiecare sistem de gestiune de baze de date implementeaza propriile subtipuri ale tipurilor de baza. Aceasta situatie poate pune uneori probleme de compatibilitate la trecerea bazelor de date de pe un sistem pe altul. In continuare se vor prezentat tipurile de date suportarte de mySQL. Ca si regula generala, notatia:

tip [(M, D)] [optiuni]

are urmatoarea interpretare: tipul de date referit este afisat pe M pozitii, eventual cu D pozitii in partea zecimala. Toate valorile ce se gasesc in paranteze drepte semnifica marimi optionale. Observatie: optiunile M si D nu influenteaza insa spatiul de stocare necesar pentru memorarea valorilor de acel tip.

Valorile optionale au urmatoarea semnificatie:

l UNSIGNED - permite memorarea doar a valorilor pozitive. l ZEROFILL - la afisare, valorile numerice vor fi precedate de '0' pana la

ABD

18

lungimea de afisare (ex. 0003) l BINARY - specifica faptul ca interpretarea caracterelor se va face in cod

ASCII (la compararea a doua siruri se va face distinctie intre litere mari/mici).

1.1. Tipuri de date numerice

1. TINYINT [(M)] [UNSIGNED] [ZEROFILL] Numar intreg foarte mic. Intervalul de valori posibile este [-128..127]. Daca se specifica UNSIGNED intervalul este [0..255].

2. SMALLINT [(M)] [UNSIGNED] [ZEROFILL] Numar intreg mic. Intervalul de valori posibile este [-32768..32767]. Daca se specifica UNSIGNED intervalul este [0...65535].

3. MEDIUMINT [(M)] [UNSIGNED] [ZEROFILL] Numar intreg mediu. Intervalul de valori posibile este [-8388608..8388607]. Daca se specifica UNSIGNED intervalul este [0..16777215].

4. INT [(M)] [UNSIGNED] [ZEROFILL] Numar intreg normal. Intervalul de valori posibile este [-2147483648..2147483647]. Daca se specifica UNSIGNED intervalul este [0..4294967295].

5. INTEGER [(M)] [UNSIGNED] [ZEROFILL] Sinonim cu INT.

6. BIGINT [(M)] [UNSIGNED] [ZEROFILL] Numar intreg mare. Intervalul de valori posibile este [-9223372036854775808..9223372036854775807]! Daca se specifica UNSIGNED intervalul este [0..18446744073709551615].

7. FLOAT [(M, D) [ZEROFILL] Numar in virgula flotanta simpla precizie (numar real). Intervalul de valori posibile este [-3.402823466E+38.. -1.175494351E-38, 0, 1.175494351E-38..3.402823466E+38].

8. DOUBLE [(M, D) [ZEROFILL] Numar in virgula flotanta dubla precizie. Intervalul de valori posibile este [-1.7976931348623157E+308..-2.2250738585072014E-308, 0, 2.2250738585072014E-308..1.7976931348623157E+308].

9. REAL [(M, D) [ZEROFILL] Sinonim cu DOUBLE.

10. DECIMAL [(M [, D]) [ZEROFILL] Numar in virgula flotanta neinpachetat. Spre deosebire de tipurile precedente, la care valorile sunt stocate binar, acesta retine valorile numerice ca si siruri de cifre ASCII. Daca D lipseste, numarul va fi intreg. Daca si M lipseste, numarul va fi pe maxim 10 cifre zecimale.

11. NUMERIC [(M [, D]) [ZEROFILL] Sinonim cu DECIMAL.

1.2. Tipuri de date data calendaristica si timp

ABD

19

1. DATE Data calendaristica. Intervalul de valori posibile este ['1000-01-01'..'9999-12-31']. Stocarea/afisarea se face implicit in formatul 'YYYY-MM-DD' (an-luna-zi).

2. DATETIME Combinatie de data calendaristica si timp. Intervalul de valori posibile este ['1000-01-01 00:00:00'..'9999-12-31 23:59:59']. Stocarea/afisarea se face implicit in formatul 'YYYY-MM-DD HH:MM:SS' (an-luna-zi ora:minut:secunda).

3. TIMESTAMP [(M)] Moment de timp. Include si data calendaristica. Este util la inregistrarea efectuarii unor operatii gen inserare sau modificare pentru ca retine implicit data efectuarii ultimei operatii. Doar prima coloana de acest tip din tabela in care se face modificarea va fi actualizata automat! Intervalul de valori posibile este ['1970-01-01 00:00:00'..'2037-01-01 00:00:00']. MySQL afiseaza valorile TIMESTAMP in format YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD sau YYMMDD dupa cum M este 14 (sau lipseste), 12, 8 sau 6. Parametrul M nu influenteza insa reprezentarea interna a datelor la stocare.

4. TIME Moment de timp. Intervalul de valori posibile este [-838:59:59'..'838:59:59']. Reprezentarea este in format 'HH:MM:SS' (ora:minut:secunda). Motivul pentru care ora nu este limitata la intervalul [0..24] esta ca acest tip poate fi folosit si pentru a retine intervale de timp intre doua evenimente. Tot din acest motiv ora poate fi si negativa.

5. YEAR [(2|4)] An calendaristic pe 2 sau 4 cifre. Intervalul de valori posibile este [1901..2155] pentru 4 cifre, respectiv [1970..2069] pentru 2 cifre (70-99..00-69).

1.3. Tipuri de date sir de caractere

1. CHAR (M) [BINARY] Sir de caractere de lungime fixa. Daca se memoreaza un sir mai scurt el va fi completat cu spatii la dreapta pana la lungimea M specificata. Lungimea M a sirului poate lua valori in intervalul [1..255]. Spatiile de la sfarsitul sirului sunt indepartate automat la interogarea campului. Daca nu se specifica optiunea BINARY, compararea valorilor se face fara a tine cont de felul literelor (mari/mici).

2. BIT BOOL CHAR Sunt sinonime cu declaratia CHAR(1).

3. VARCHAR (M) [BINARY] Sir de caractere de lungime variabila. La memorare, spatiile de la sfarsitul sirului sunt indepartate. Lungimea M a sirului poate lua valori in intervalul

ABD

20

[1..255]. Daca nu se specifica optiunea BINARY, compararea valorilor se face fara a tine cont de felul literelor (mari/mici).

1.4. Tipuri de date binare mari (BLOB)

Se folosesc pentru a retine obiecte binare (BLOB) de mari dimensiuni (ex. imagini, secvente audio sau video) sau pentru a retine texte de dimensiune in general mai mare de 255 caractere (TEXT). La comparatia campurilor tip TEXT nu se va tine cont de tipul caracterelor (mari/mici).

1. TINYBLOB TINYTEXT Valori BLOB, respectiv TEXT cu lungime de max. 255 elemente.

2. BLOB TEXT Valori BLOB, respectiv TEXT cu lungime de max. 65535 (64 KB) elemente.

3. MEDIUMBLOB MEDIUMTEXT Valori BLOB, respectiv TEXT cu lungime de max. 16777215 (16 MB) elemente.

4. LONGBLOB LONGTEXT Valori BLOB, respectiv TEXT cu lungime de max. 4294967295 (4 TB) elemente.

1.5. Tipuri de date speciale

1. ENUM ('value1','value2',...) Enumerare de elemente tip sir de caracter. Un obiect de acest tip poate avea la un moment dat o singura valoare dintre cele enumerate sau valoarea NULL. Valoarea " " (sir vid) este considerata valoare de eroare. Un tip enumerare poate defini maxim 65535 de valori distincte.

2. SET ('value1','value2',...) Multime de elemente tip sir de caracter. Un obiect de acest tip poate contine la un moment dat mai multe valori distincte dintre cele definite sau poate fi gol (nu contine nici o valoare). Un tip multime poate defini maxim 65 de valori membru.

2. Constante, identificatori, comentarii

2.1. Constante

l Constantele tip sir de caractere se includ intre apostroafe sau ghilimele:

ABD

21

'constnta sir' "alta constanta sir" In interiorul unui sir se pot include caractere speciale precedate de semnul '\' (escape):

\n Trecere la linie noua (new line). \t Tab (caracter de aliniere). \r Salt la inceputul liniei (carriage return). \b Sterge inapoi. \' Caracterul '. \" Caracterul ". \\ Caracterul \. \% Caracterul '%'. Se poate folosi pentru a cauta caracterul %, in

conditiile in care acest caracter este folosit in expresii regulate (subiectul va fi detaliat ulterior).

\_ Caracterul _. Se poate folosi pentru a cauta caracterul _, in conditiile in care acest caracter este folosit in expresii regulate (subiectul va fi detaliat ulterior).

l Constantele intregi sunt reprezentate printr-un sir de cifre zecimale. Constantele reale contin semnul '.' pentru specificarea partii zecimale. Ambele tipuri pot fi precedate de semnul '-' pentru a indica numere negative. Ex: 13534 342.3453

l Constanta NULL este folosita cu semnificatia "nici o valoare". Se face diferenta intre NULL si numarul 0 sau sirul vid "".

2.2. Identificatori

Numele pentru baza de date, tabele, coloane, indecsi sau alias-uri trebuie sa indeplineasca conditiile normale pentru identificatori in limabje de programare: sa fie o combinatie de litere, cifre si semne grafice care incep cu o litera. In plus mySQL impune:

Referirea la o coloana in cadrul unei interogari se poate face in mai multe moduri, functie de contextul existent:

Activitate 1

Rulati secventele:

mysql> SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello' ; mysql> SELECT "hello", "'hello'", "''hello''", "hel""lo", "\"hello" ;

Identificator Lungime maxima Caractere interziseBaza de date 64 '/'Tabela 64 '/' si '.'Coloana 64 Alias 255

Referinta la coloana Semnificatie

ABD

22

Identificatorii pot fi scrisi in general cu orice combinatie de litere mari sau mici. Pentru numele bazei de date si cel al tabelelor unele, implementari impun respectarea tipului de caractere folosit la definirea elementelor respective. Ca si regula generala, este bine sa se pastreze o conventie de notare care sa nu fie schimbata de la o interogare la alta. O regula simpla este sa se scrie comenzile SQL cu litere mari si identificatorii definiti de utilizator cu litere mici.

2.3. Comentarii

MySQL suporta trei tipuri de comentarii prin care se poate explica codul. Ele sunt:

l Comentarii pe o singura linie care incep cu # si continua pana la sfarsitul liniei;

l Comentarii pe o singura linie care incep cu -- si continua pana la sfarsitul liniei. Este necesar cel putin un spatiu dupa semnul --;

l Comentarii pe mai multe linii care incep cu /* si se incheie cu */ ;

3. Operatori si functii mySQL

3.1. Paranteze de grupare

Sunt folosite pentru a schimba ordinea de evaluare in interiorul expresiilor. Parantezele folosite sunt doar paranteze rotunde.

nume_coloanaReferinta la coloana unei tabele din baza de date activa. Se poate folosi daca interogarea nu contine si alte tabele care au coloane cu nume identice

nume_tabela.nume_coloana Referinta explicita la coloana tabelei specificate prin nume_tabela din baza de date activa.

nume_BD.nume_tabela.nume_coloanaReferinta explicita la coloana tabelei specificate prin nume_tabela din baza de date specificata prin nume_BD (nu este necesar sa fie cea activa).

Activitate 2

Rulati secventele:

mysql> SELECT 1+1; # Acest comentariu continua pana la sfarsitul liniei mysql> SELECT 1+1; -- Acest comentariu continua pana la sfarsitul liniei mysql> SELECT 1 /* Acesta este un comentariu imbricat */ + 1; mysql> SELECT 1+ > /* > Acesta este > un comentariu multi-linie > */ > 1;

Activitate 3Rulati secventele:

mysql> SELECT 1+2*3;

ABD

23

3.2. Operatori aritmetici

Operatorii aritmetici opereaza asupra numerelor intregi cu precizia BIGINT (pe 64 biti). Sunt disponibili urmatorii operatori:

l + pentru adunare; l - pentru scadere; l * pentru inmultire; l / pentru impartire; daca se imparte la 0 rezultatul va fi NULL;

3.3. Operatori logici

Operatorii logici intorc 1 pentru adevarat si 0 pentru fals.

l NOT ! - Negatie logica. Intoarce 1 daca argumentul este 0, altfel intoarce 0. Exceptie: NOT NULL intoarce NULL.

l OR || - SAU logic. Intoarce 1 daca cel putin un argument nu este 0 sau NULL.

l AND && - SI logic. Intoarce 0 sau NULL daca cel putin un argument este 0 sau NULL, altfel intoarce 1.

3.4. Operatori de comparare

Operatorii de comparare returneaza 1 pentru adevarat, 0 pentru fals si NULL daca nu se poate efectua comparatia. Ei pot compara atat numere cat si siruri de caractere. La compararea unui numar cu un sir se incearca transformarea sirului in numarul pe care il contine. La compararea a doua siruri nu se va tine cont de litere mari/mici.

mysql> SELECT (1+2)*3;

Activitate 4

Rulati secventele:

mysql> SELECT 3+5, 3-5; mysql> SELECT 18014398509481984*18014398509481984.0; mysql> SELECT 18014398509481984*18014398509481984; /* depasire (numar intreg > 64 bit) */ mysql> SELECT 3/5; mysql> SELECT 5/(3-3);

Activitate 5

Rulati secventele:

mysql> SELECT NOT 1, NOT NULL, ! (1+1), ! 1+1 ; mysql> SELECT 1 OR 0, 0 OR (1-1), 1 || NULL ; mysql> SELECT 1 && NULL, 1 && 0, 1 AND (0+1) ;

ABD

24

l = egalitate; la compararea cu NULL intoarce NULL; l sau != inegalitate; l < - mai mic; l - mai mare; l >= - mai mare sau egal; l - echivalenta; la compararea cu NULL intoarce 0 sau 1 (1 doar la NULL

NULL); daca ambii operanzi sunt diferiti de NULLse comporta ca si =; l IS NULL - testeaza daca operandul are valoarea NULL; l IS NOT NULL - testeaza daca operandul este diferit de NULL; l ISNULL(expr) - testeaza daca expresia are valoarea NULL; l expr BETWEEN min AND max - testeaza daca valoarea expresiei este in

intervalul [min..max]; l expr IN (value,...) - testeaza daca valoarea expresiei este in lista de valori

specificata; l expr NOT IN (value,...) - testeaza daca valoarea expresiei nu este in lista de

valori specificata; l IF(expr1,expr2,expr3) - testeaza valoarea de adevar a expr1 (falsa daca

este 0 sau NULL) si intoarce expr2 pentru adevarat respectiv expr3 pentru fals;

3.5. Functii pe siruri de caractere

Functiile pe siruri considera implicit primul caracter pe pozitia 1.

l ASCII(str) - intoarce codul ASCII al caracterului de pe pozitia 1 din sir. Daca sirul este vid intoarce 0.

l CONV(N, from_base, to_base) - converteste numarul N considerat in baza from_base in valoarea sa in baza to_base.

l CHAR(N,...) - converteste sirul de numere primite intr-un sir de caractere ale caror coduri ASCII sunt egale cu cele din sirul initial.

l CONCAT(str1,str2,...) - concateneaza sirurile primite ca argument, returnand sirul rezultat.

l LENGTH(str) - intoarce lungimea (numarul de caractere) a sirului primit ca si argument.

l LOCATE(substr,str) POSITION(substr IN str) - cauta prima aparitie a sirului substr in sirul str.

Activitate 6

Rulati secventele:

mysql> SELECT 1 = 0, '0.0' = 0, '.01' = 0.01 ; mysql> SELECT .01 '0.01', 'abc' 'abcd' , 'abc' 'ABC' ; mysql> SELECT 0.1 = 2, 5 > '3' ; mysql> SELECT 0 NULL, NULL = NULL, NULL < = > NULL ; mysql> SELECT 0 IS NULL, NULL IS NULL, 0 IS NOT NULL ; mysql> SELECT 1 BETWEEN 2 AND 3, 'b' BETWEEN 'a' AND 'c', 2 BETWEEN 2 AND '3' ; mysql> SELECT 2 IN (0,3,5,'abc') , 'abc' IN (0,3,5,'Abc') ; mysql> SELECT IF(1&gt2,2,3), IF(1&lt2,'da','nu') ;

ABD

25

Daca il gaseste returneaza pozitia, daca nu, returneaza 0. l LEFT(str,len) - intoarce un sir format din primele len caractere din sirul dat. l RIGHT(str,len) - intoarce un sir format din ultimele len caractere din sirul

dat. l SUBSTRING(str,pos,len)

MID(str,pos,len) - intorc subsirul de pe pozitia pos, de lungime len caractere din sirul str.

l SUBSTRING_INDEX(str,delim,count) - intoarce subsirul pana la count aparitii ale caracterului delimitator delim. Daca delim este pozitiv intoarce prima parte a sirului, daca este negativ, ultima parte.

l LTRIM(str) RTRIM(str) TRIM(str) - intoarce sirul str fara spatiile de la inceput (LTRIM), sfarsit (RTRIM) sau din ambele parti (TRIM).

l SPACE(N) - intoarce un sir format din N spatii. l LOWER(str)

UPPER(str) - intorc sirul str cu toate caracterele convertite la litere mici, respectiv litere mari.

l sir (NOT) LIKE tipar - compara sirul sir cu tiparul dat. Intoarce adevarat (1) daca se potrivesc. Tiparele pot include caracterele speciale: '_' care se potriveste cu orice caracter si '%' care poate inlocui orice secventa de 0 sau mai multe caractere. Ex. 'ana' LIKE 'an_', 'ana' LIKE 'a%', 'ana' NOT LIKE 'a%z'.

3.6. Functii matematice

Toate functiile matematice intorc NULL in caz de eroare.

l ABS(X) - valoarea absoluta (fara semn) a lui x. l SIGN(X) - testeaza semnul lui x, intoarce -1 pentru x negativ, 1 pentru x

pozitiv si 0 in caz contrar l MOD(N,M)

% - modulo (restul impartirii) lui N la M. l FLOOR(X) - cea mai mare valoare intreaga mai mica decat x (rotunjire in

jos) l CEILING(X) - cea mai mica valoare intreaga mai mare decat x (rotunjire in

Activitate 7

Rulati secventele:

mysql> SELECT ASCII('0'), CONV("125",10,2), CHAR(77,121) ; mysql> SELECT CONCAT('My', 'S', 'QL'), LENGTH('text'), LOCATE('me', 'Ana are MERE') ; mysql> SELECT LEFT('MySQL', 2), RIGHT('MySQL', 3), SUBSTRING('Pasaport',4,5) ; mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2), CONCAT('A', LTRIM(' na ')) ; mysql> SELECT CONCAT('a',SPACE(6),'b'), LOWER('Ana are MERE'), UPPER('Ana are MERE') ; mysql> SELECT 'ana' LIKE 'An_', 'ana' LIKE 'a%', 'ana' LIKE 'a%z' ;

ABD

26

sus) l ROUND(X) - rotunjire la cel mai apropiat intreg l EXP(X) - exponentiala (e la puterea x) l LOG(X) - logaritm natural din x l LOG10(X) - logaritm in baza 10 din x l POW(X,Y) - x la puterea y l SQRT(X) - radical de ordinul 2 din x l PI() - numarul PI; implicit se afiseaza cu 5 zecimale dar in calcule intervine

cu dubla precizie l COS(X), SIN(X),TAN(X) - cosinus, sinus si tangenta de x l RAND()

RAND(N) - genereaza un numar pseudo-aleator in intervalul [0..1]; prin N se poate specifica o valoare de initializare calcul

l LEAST(X,Y,...) - intoarce cea mai mica valoare din lista valorilor specificate l GREATEST(X,Y,...) - intoarce cea mai mare valoare din lista valorilor

specificate

3.7. Functii de tip data calendaristica si timp

l DAYOFWEEK(date) - indexul zilei din spatamana al datei specificate (1=duminica, 2=luni etc.)

l DAYOFYEAR(date) - numarul zilei din an pentru data specificata l DAYNAME(date) - numele (in engleza) al zilei din data specificata l MONTHNAME(date) - numele (in engleza) al lunii din data specificata l YEAR(date) - extrage anul din data specificata l HOUR(time) - extrage ora din timpul precizat l MINUTE(time) - extrage minutul din timpul precizat l SECOND(time) - extrage secunda din timpul precizat l CURDATE() - intoarce data curenta in format 'YYYY-MM-DD' l CURTIME() - intoarce ora curenta in format 'HH:MM:SS' l NOW()

SYSDATE() - intoarce data si ora curenta in format 'YYYY-MM-DD HH:MM:SS'

l SEC_TO_TIME(seconds) - intoarce timpul in format 'HH:MM:SS' reprezentat de numarul de secunde specificate

l TIME_TO_SEC(time) -converteste timpul specificat in numar de secunde scurse de la ora 00:00:00

Activitate 8

Rulati secventele:

mysql> SELECT ABS(-32), SIGN(-32), MOD(29,10) ; mysql> SELECT FLOOR(1.23), CEILING(1.23), ROUND(1.58) ; mysql> SELECT EXP(3), LOG(3), LOG10(100) ; mysql> SELECT POW(2,5), SQRT(16), PI() ; mysql> SELECT COS(PI()), RAND(20), RAND(20), RAND() ; mysql> SELECT LEAST(2,0), LEAST("B","A","C"), GREATEST(2,0) ;

Rulati secventele:

ABD

27

3.8. Functii speciale

l DATABASE() - numele bazei de date active l USER() - numele utilizatorului coectat la server l VERSION() - returneaza versiunea serverului MySQL instalat l PASSWORD(str) - criptare sir dupa metoda interna prin care MySQL isi

pastreaza parolele; criptarea nu este reversibila (nu se poate calcula sirul de plecare pornind de la cheia criptata)

l FORMAT(N,D) - returneaza numarul N in format '#,###,###.##' cu D zecimale

4. Crearea si stergerea tabelelor

Datele rezultate din procesul de analiza a problemei ce trebuie implementata se impart in tabele prin procesul de normalizare. In acest fel o baza de date va fi formata dintr-o colectie de tabele. Pentru a crea o tabela se va utiliza comanda SQL CREATE TABLE.

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]

l TEMPORARY - specifica crearea unei tabele temporare care va fi stearsa automat la inchiderea conexiunii in care a fost creata; doua conexiuni pot crea doua tabele temporare cu aceleasi nume, ele nu vor interfera.

l IF NOT EXISTS - inhiba mesajul de eroare care se genereaza daca la incercarea de a crea o tabela care mai exista.

l create_definition - pentru definirea coloanelor este un sir de tipul: col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [PRIMARY KEY]

col_name - nume coloana type - tip coloana (vezi sectiunea 1) NOT NULL - nu se permit valori NULL in acest camp (campul trebuie

completat in orice situatie) NULL - permite valori NULL in camp (optiune implicita daca nu se

specifica nimic)

Activitate 9

mysql> SELECT NOW(), DAYOFWEEK(NOW()), DAYOFYEAR(NOW()) ; mysql> SELECT DAYNAME(NOW()), MONTHNAME(NOW()), YEAR(NOW()) ; mysql> SELECT CURTIME(), HOUR(CURTIME()), MINUTE(CURTIME()) ; mysql> SELECT SEC_TO_TIME(66), TIME_TO_SEC("00:01:06") ;

Activitate 10

Rulati secventele:

mysql> SELECT DATABASE(), USER(), VERSION() ; mysql> SELECT FORMAT(345674355.35,3), PASSWORD("parola") ;

ABD

28

DEFAULT default_value - completeaza campul lasat liber cu valoarea implicita default_value

AUTO_INCREMENT - daca nu se insereaza nimic in camp, se va genera automat o valoare mai mare cu o unitate fata de cea mai mare valoare din acel camp. Poate fi specificata o singura coloana cu acest parametru intr-o tabela. Coloanele specificate astfel trebuie indexate.

PRIMARY KEY - specifica cheia primara pentru tabela. Un singur camp din tabela poate avea acest parametru. Coloana declarata ca si cheie primara este indexata automat. Daca tabela are cheia primara formata din mai multe campuri, aceasta va trebui specificata separat, pe post de camp suplimentar, cu sintaxa "primary key (camp1, camp2 ...)".

l create_definition - pentru definirea indexilor are forma: INDEX (coloana1,...)

Pentru a sterge o tabela se va folosi comanda:

DROP TABLE [IF EXISTS] tbl_name

Parametrul IF EXISTS va inhiba mesajul de eroare care apare daca tabela nu exista.

5. Tema 3.1

A) Efectuati cele 11 activitati din acest capitol. Raspunsul va curpinde linii de forma: Activitate 1: hello , "hello" , ""hello"" , hel'lo , 'hello hello , 'hello' , ''hello'' , hel"lo , "hello

Activitate 11

Selectati cu ajutorul comenzii USE baza de date BANCA creata la Tema 2.2. Creati o tabela Deponent ruland comanda:

mysql> CREATE TABLE Deponent ( mysql> nr_buletin char(8) NOT NULL PRIMARY KEY, mysql> cnp char(13) NOT NULL, mysql> nume char(24) NOT NULL, mysql> prenume char(24) NOT NULL, mysql> datan date, mysql> oras char(24) DEFAULT 'Timisoara', mysql> adresa char(32), mysql> telefon char(9), mysql> index (cnp), index (nume) mysql> );

Verificati corectitudinea tabelei create cu ajutorul comenzilor:

mysql> SHOW COLUMNS FROM Deponent ; mysql> SHOW INDEX FROM Deponent ;

ABD

29

Activitate 2: 2 2 2 ... Obs: pentru a rula mai usor secventele se poate face "copy" in fereastra navigatorului si "paste" in fereastra mySQL.

B) Adaugati la baza de date BANCA tabelele Conturi si Operatii cu urmatoarea structura:

Conturi nr_cont char(6) -- de forma 'C23432', cheie primara nr_buletin char(8) -- cheie externa spre tabela Deponenti, nu poate fi null tip_cont char(3) -- tipul contului, ex. 'LEI', 'USD' etc. termen_depozit int(3) -- termen depozit in numar de zile (0 pentru cont curent), implicit 0 sold int -- suma totala din cont, poate fi negativa daca s-a luat un imprumut

si

Operatii nr_cont char(6) -- nu poate fi null, cheie externa spre tabela Conturi suma int -- suma depusa (pozitiva) sau extrasa (negativa) data timestamp -- data efectuarii operatiei cheia primara va fi formata din nr_cont+data

Raspunsul va consta intr-o secventa de comenzi ce va creea si verifica crearea tabelelor.

Pentru a simplifica maniera de scriere a secventelor de cod, acestea vor fi scrise intr-un fisier text cu extensia .sql editat din NOTEPAD sau orice alt editor text, putand apoi sa fie rulate foarte simplu din linia de comanda mysql prin comanda:

mysql> SOURCE cale_spre_fisier/nume_fisier.sql; EX: mysql> SOURCE C:\sql\tema31b.sql;

'2010 confs. dr. ing. Dan Pescaru

ABD

30

Cap.5. Gestiunea Accesului

1. Sistemul de privilegii al MySQL 1.1. Introducere 1.2. Utilizatori si parole MySQL 1.3. Principiul "minimului de privilegii necesare" 1.4. Privilegiile puse la dispizitie de MySQL 2. Crearea utilizatorilor si stabilirea privilegiilor 2.1. Comanda GRANT 2.2. Vizualizarea privilegiilor 2.3. Conectarea la server folosind parola 3. Retragerea privilegiilor

1. Sistemul de privilegii al MySQL

1.1. Introducere

Functia pricipala a sistemul de privilegii al MySQL este aceea de a autentifica si autoriza utilizatorii conectati la server. Autorizarea se refera la permisiunea de a rula interogari precum SELECT, INSERT, UPDATE sau DELETE. O clasa aparte de privilegii se refera la drepturile de administrare si de interactinoare cu sistemul de operare. Termenul privilegiu denota in general un drept al unui utilizator de a actiona intr-un anumit fel asupra unui obiect al bazei de date (tabela, camp, index etc.).

1.2. Utilizatori si parole MySQL

Exista diferente semnificative intre sistemul de utilizatori si parole MySQL si sistemul Unix-ului sau Windows-ului:

l Numele de utilizatori MySQL pot avea 16 caractere semnificative pe cand in majoritatea implementarilor UNIX aceasta valoare este de 8 caractere.

l Numele utilizatorilor si parolele sunt pastrate separat de mySQL si nu au nimic in comun cu cele din sistemul de operare.

l MySQL cripteaza parolele folosind un algoritm diferit de cel utilizat de UNIX sau Windows. Algoritmul de criptare este ireversibil.

La instalarea sistemului este creat implicit un utilizator numit "root" care are toate drepturile activate. Acest utilizator trebuie folosit, din motive de securitate, doar pentru administrare. Pentru fiecare utilizator care va interactiona cu serverul trebuie creat un utilizator.

Foarte important! Desi nu este obligatorie stabilirea unor parole pentru utilizatori la crearea conturilor acestora, aceasta operatie este de cea mai mare importanta. In caz contrar securitatea serverului este grav compromisa.

ABD

31

1.3. Principiul "minimului de privilegii necesare"

Principiul "minimului de privilegii necesare" poate fi folosit pentru a spori securitatea oricarui calculator, nu doar a serverului MySQL. El este foarte simplu, dar este in acelasi timp foarte important sa se tina seama de el:

Un utilizator (sau proces) trebuie sa aiba cel mai scazut nivel de privilegii, suficient pentru a putea executa sarcinile care i-au fost alocate.

1.4. Privilegiile puse la dispizitie de MySQL

Informatiile despre privilegii sunt stocate in tabelele user, db, host, tables_prv si columns_priv din baza de date mysql. Serverul va citi continutul acestor tabele ori de cate ori sistemul de privilegii trebuie sa actioneze. Tabelul urmator prezinta sistemul de privilegii impreuna cu numele coloanelor din tabelele de privilegii:

Cateva dintre aceste privilegii necesita o atentie aparte:

l Privilegiul grant permite utilizatorilor sa dea mai departe privilegiile lor si altor utilizatori. In acest fel, doi utilizatori cu privilegii diferite si le pot combina.

l Privilegiul alter poate fi folosit pentru a redenumi tabele, modificand astfel

Privilegiu Denumire coloana Context Semnificatie

select Select_priv tabele permite selectarea (vizualizarea) datelorinsert Insert_priv tabele permite adaugarea unor noi inregistrariupdate Update_priv tabele permite modificarea datelordelete Delete_priv tabele permite stergerea inregistrarilorindex Index_priv tabele permite crearea/stergerea indecsilor

alter Alter_priv tabele permite redenumire sau modificarea structurii tabelei

create Create_priv baza de date, tabele sau indecsi permite crearea unei baze de date/tabele

drop Drop_priv baza de date, tabele permite stergerea unei baze de date/tabele

grant Grant_priv baza de date, tabele permite delegarea privilegiilor catre alt utilizator

shutdown Shutdown_priv administrare server permite oprirea serverului din programul client

process Process_priv administrare server permite vizualizarea/oprirea proceselor in executie

file File_priv acces la fisiere externe permite schimbul de date intre tabele si fisiere

ABD

32

baza de date si facand astfel inutilizabile programele altor utilizatori. l Privilegiul file poate fi folosit pentru a citi informatii sensibile de pe server. l Privilegiul shutdown da posibilitate opririi serverului de la distanta. l Privilegiul process poate fi utilizat pentru a vedea continutul interogarilor ce

se executa in acel moment, inclusiv cele de setare a parolei. De asemenea el da posibilitatea opririi fortate a conexiunilor altor utilizatori.

Exista si lucruri care nu sunt acoperite se sistemul de privilegii al mySQL, printre care:

l Nu se poate specifica explicit ca unui utilizator i se refuza dreptul de a se conecta.

l Nu se poate da dreptul unui utilizator pentru a crea si sterge tabele intr-o baza de date, dar nu poate sterge si baza de date.

2. Crearea utilizatorilor si stabilirea privilegiilor

2.1. Comanda GRANT

Comanda GRANT permite crearea utilizatorilor si/sau stabilirea, respectiv modificarea privilegiilor acestora pe patru nivele de privilegii:

l Global - se aplica tuturor bazelor de date existente pe un server l Database - se aplica tuturor tabelelor dintr-o baza de date l Table - se aplica tuturor coloanelor dintr-o tabela l Column - se aplica doar coloanelor specificate explicit

Forma generala a comenzii GRANT este:

GRANTprivilegii [coloane] ONcomponenta TOnume_utilizator [IDENTIFIED BY 'parola'] [WITH GRANT OPTINS]

Semnificatia clauzelor (clauzele din paranteze drepte sunt optionale) este urmatoarea:

l privilegii - este o lista de privilegii despartite prin virgula. Ele pot fi alese dintre cele prezentate anterior (SELECT, INSERT, ALTER ...) plus clauza speciala ALL PRIVILEGES (sau simplu ALL) care specifica toate privilegiile posibile. Pentru a crea doar utilizatorul fara sa i se dea nici un drept, se va folosi clauza USAGE.

l coloane - o lista de una sau mai multe coloane. Permite stabilirea

ABD

33

privilegiilor la nivel de coloane. l componenta - este numele unei baze de date sau tabele asupra carora vor

fi stabilite privilegiile. Toate bazele de date se pot specifica prin *.*. Acest nivel se numeste nivel Global de privilegii. Semnul *.* se poate inlocui si cu *, dar in acest caz nu trebuie sa existe nici o baza de date selectata, in caz contrar privilegiile se aplica doar asupra bazei de date respective. Nivelul Database se poate specifica prin nume_bd.*, iar nivelul Table prin nume_bd.nume_tabela. Daca se specifica doar nume_tabela se va interpreta ca fiind o tabela a bazei de date selectate. Nivelul Column se va obtine prin folosirea unei liste de coloane (vezi optiunea precedenta) nevide.

l nume_utilizator - este numele utilizatorului (contul) caruia i se atribuie privilegiile. El poate contine si un nume de statie de pe care are dreptul sa se conecteze. Astfel, utilizatorul "gigel" va fi interpretat ca "gigel@localhost" si va fi diferit de "[email protected]". Acest lucru este util pentru ca pot exista in realitate utilizatori diferiti cu nume identice dar care lucreaza pe statii diferite. Tot de aici se poate restrictiona accesul doar de la anumite statii. Un grup de statii se pot specifica printr-un domeniu generic. Spre exemplu "gigel@'%.ro'" inseamna ca utilizatorul "gigel" se poate conecta de la orice statie din domeniul ".ro". Numele simplu "gigel" este echivalent cu "gige@'%'" si permite conectarea de la orice statie.

l parola - specifica parola cu care utilizatorul se va conecta. Parola trebuie in general sa nu fie usor de ghicit (sa nu fie chiar numele contului sau un cuvant din dictionar). Daca optiunea IDENTIFIED BY lispseste, utilizatorul se va putea conecta fara parola, ceea ce reprezinta o grava lacuna de securitate.

l WITH GRANT OPTION - da dreptul utilizatorului sa dea privilegii echivalente cu ale sale altor utilizatori. Acesta optiune poate fi utilizata pentru a delega dreptul de administrare, inclusiv creare de utilizatori, unor administratori ale unor baze de date particulare de pe server. Acesti administratori nu vor putea insa sa interactioneze cu alte baze de date.

Dupa crearea unui utilizator, parola sa se poate schimba prin comanda:

mysql> SET PASSWORD FOR utilizator = PASSWORD("noua_parola");

2.2. Vizualizarea privilegiilor

Vizualizarea privilegiilor unui utilizator se poate face prin comanda:

mysql> SHOW GRANTS FOR utilizator;

2.3. Conectarea la server folosind parola

Conectarea la server se poate face local sau de la distanta folosind diversi clienti mySQL. Toti acesti clienti vor permite trimiterea spre server a informatiilor legate de numele utilizatorului si parola. Clientul standard din chitul de distributie se va folosi astfel:

ABD

34

mysql -h nume_server -u nume_utilizator-p

Dupa lansarea comenzii clientul va cere parola de conectare:

Enter password: *****

3. Retragerea privilegiilor

Inversul comenazii GRANT este comanda REVOKE. Ea este utilizata pentru a retrage drepturile de la un utilizator. Sintaxa sa este asemanatoare cu cea a comenzii GRANT:

REVOKEprivilegii [coloane] ONcomponenta FROMnume_utilizator

Clauzele din aceasta comanda sunt identice cu cele de la comanda GRANT.

Pentru a retrage privilegiul acordat prin clauza WITH GRANT OPTION, se va utiliza sintaxa:

REVOKE GRANT OPTIONS ONcomponenta FROMnume_utilizator


ABD

35

Cap.6. Gestionarea datelor.

1. Inserarea datelor in tabele 1.1. Comanda INSERT 1.2. Utilizare comenzii INSERT 2. Modificarea datelor din tabele 2.1. Comanda UPDATE 2.2. Utilizarea comenzii UPDATE 3. Stergerea datelor din tabele 3.1. Comanda DELETE 3.2. Utilizarea comenzii DELETE

1. Inserarea datelor in tabele

1.1. Comanda INSERT

Comanda INSERT permite inserarea (adaugarea) de noi inregistrari (randuri) intr-o tabela.

Deoarece sistemul mySQL este pur relational, nu exista nici o diferenta intre inserarea de noi date sau adaugarea lor. In ambele situatii, locul in care se face adaugarea nu este precizat, nefiind relevant. La sistemele care nu sunt pur relationale (cum este dBase sau FoxPro) operatia de adaugare semnifica adaugarea la sfarsitul unei tabele, pe cand insertia inseamna inserarea intre alte doua inregistrari existente.

Comanda INSERT din mySQL are sintaxa de baza:

INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_nume [(col_nume,...)] VALUES (expresie,...),(...),...

Parametrii au urmatoarea semnificatie:

l LOW_PRIORITY - se foloseste pentru a intarzia scrierea efectiva a datelor in tabela pana cand alti utilizatori nu mai citesc date din tabela. Acest lucru avantajeaza citirea dar intarzie, semnificativ pentru o tabela foarte utilizata, scrierea. Efectul este blocarea executiei pana cand se reuseste scrierea efectiva.

l DELAYED - are actiune opusa parametrului precedent. Inregistrarea care trebuie adaugata este pusa intr-o coada de asteptare pe server si controlul revine la client, ca si cum scrierea ar fi fost facuta efectiv. Avantajul consta in timpul mic de executie a comenzii INSERT pe o tabela utilizata intensiv. Dezavantajul consta in incarcarea serverului cu o operatie in plus si in pierderea datelor daca serverul se opreste neasteptat. Din aceasta cauza

ABD

36

parametrul este folosit doar cand este cu adevarat necesar. l IGNORE - este util daca se insereaza mai multe inregistrari simultan (in

acest caz lista de dupa VALUES va contine mai multe seturi de date in paranteze, departite prin virgula). In mod obisnuit, MySQL raporteaza eroare si nu executa comanda care incearca adaugarea a cel putin unei inregistrari care duplica cheia primara sau campuri cu valori unice. Prin folosirea parametrului IGNORE inregistrarile gresite sunt ignorate, dar restul sunt adaugate fara raportarea unei erori. Dupa executia comenzii se va raporta doar numarul de inregistrari a caror adaugare a reusit.

l INTO - este optional, se foloseste pentru compatibilitate cu alte sisteme SQL. Datorita claritatii mai mari a interogarii se prefera totusi utilizarea lui.

l tbl_nume - specifica numele tabelei unde se adauga inregistrarile. l (col_nume,...) - este o lista de coloane care specifica ordinea in care se vor

adauga datele in tabela. Prin aceasta se pot insera doar datele considerate esentiale, restul primind valorile implicite specificate la declararea tabelei. Daca lista lipseste, setul de date furnizat trebuie sa aiba valori pentru toate campurile, in ordinea din declararea tabelei.

l VALUES - specifica seturile de date (inregistrarile) care se vor adauga in tabela.

l (expresie, ...),(...),... - fiecare paranteza specifica datele unei inregistrari ce va fi adaugata. Ele trebuie sa respecte numarul si ordinea coloanelor de la declararea tabelei sau, daca e specificat, numarul si ordinea campurilor din lisata de coloane (col_nume,...) prezentata anterior.

O sintaxa introdusa o data cu versiunea 3.22.10 permite specificarea explicita a corespondentei intre coloane si valorile inserate:

INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_nume SET col_nume1=expresie1, col_nume2=expresie2, ...

O a treia sintaxa permite adugarea automata a inregistrarilor prin selectia lor din alte tabele:

INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name [(col_name,...)] SELECT ...

Optiunea SELECT va fi discutata pe larg in capitolul urmator. In principiu trebuie retinute totusi cateva limitari:

l operatia de selectie nu poate contine clauza ORDER_BY pentru ordonarea rezultatului

l datele nu pot fi selectate din aceeasi tabela in care sunt apoi inserate

1.2. Utilizare comenzii INSERT

ABD

37

Se considera baza de date Banca descrisa in capitolul anterior, cuprinzand tabelele:

Deponent nr_buletin char(8) NOT NULL PRIMARY KEY cnp char(13) NOT NULL nume char(24) NOT NULL prenume char(24) NOT NULL datan date oras char(24) DEFAULT 'Timisoara' adresa char(32) telefon char(9)

Conturi nr_cont char(6) NOT NULL PRIMARY KEY nr_buletin char(8) NOT NULL tip_cont char(3) DEFAULT 'Lei' termen_depozit int(3) DEFAULT 0 sold int DEFAULT 0

Operatii nr_cont char(6) NOT NULL suma int data timestamp

2. Modificarea datelor din tabele

2.1. Comanda UPDATE

Comanda UPDATE permite actualizarea (modificarea) valorilor dintr-o tabela.

Sintaxa acestei comenzi este:

UPDATE [LOW_PRIORITY] [IGNORE] tbl_nume SET col_nume1=expr1,col_nume2=expr2,... [WHERE conditie_de_actualizare]


l LOW_PRIORITY - intarzie executia comenzii UPDATE pana cand nici un alt client nu mai citeste din tabela. Are acelasi efect ca si in cazul comenzii INSERT.

l IGNORE - nu se intrerupe executia in cazul in care se duplica chei unice la actualizare. Inregistrarile care cauzeaza erori sunt pur si simplu ignorate.

l tbl_nume - nume tabela unde are loc actualizarea l SETcol_nume1=expr1,col_nume2=expr2,... - indica acele coloane care

sunt modificate si noile lor valori. Expresiile de actualizare pot cuprinde

ABD

38

vechile valori ale campurilor de actualizat. l WHERE conditie_de_actualizare - indica acele coloane care sunt

actualizate, si anume doar cele pentru care conditie_de_actualizare are valoarea adevarat. Daca clauza WHERE lipseste, vor fi actualizate toate inregistrarile.

2.2. Utilizarea comenzii UPDATE

Considerand tabelele bazei de date Banca si datele adaugate in cadrul activitatii anterioare, se vor exemplifica urmatoarele actualizari:

mysql> UPDATE Deponent SET nume='Predescu' WHERE nr_buletin='TM123456'; mysql> SELECT * FROM Deponent; # Prefixele telefonice ale oraselor se schimba din 0xx in 7xx: mysql> UPDATE Deponent SET telefon=concat('7',right(telefon, length(telefon)-1)); mysql> SELECT * FROM Deponent;

3. Stergerea datelor din tabele

3.1. Comanda DELETE

Pentru stergerea datelor din tabele se foloseste comanda DELETE. Foarte important: datele o data sterse nu mai pot fi recuperate. Totusi, din motive de viteza, datele nu sunt efectiv sterse din fisiere, locul ocupat de ele fiind alocat pentru adaugarea de noi inregistrari la operatia de INSERT.

Sintaxa comazii DELETE este:

DELETE [LOW_PRIORITY] FROM tbl_nume [WHERE conditie_de_stergere]


l LOW_PRIORITY - intarzie executia comenzii DELETE pana cand nici un client nu mai citeste din tabela. Are acelasi efect ca si in cazul comenzii INSERT.

l FROMtbl_nume - specifica tabela de unde se sterg inregistrari. l WHEREconditie_de_stergere - specifica inregistrarile care vor fi sterse, si

anume cele care indeplinesc conditia logica precizata. Atentie! Daca clauza WHERE este omisa se vor sterge toate inregistrarile din tabela.

3.2. Utilizarea comenzii DELETE

Considerand tabelele bazei de date Banca si datele adaugate in cadrul activitatii anterioare, se vor exemplifica urmatoarele stergeri:

ABD

39

mysql> DELETE FROM Deponent WHERE oras='Galati'; mysql> SELECT * FROM Deponent; # Se vor sterge inregistrarile cu numarul de telefon necompletat: mysql> DELETE FROM Deponent WHERE IsNull(telefon); mysql> SELECT * FROM Deponent;


ABD

40

Cap.7. Interogari

1. Interogarea datelor 1.1. Comanda SELECT 1.2. Utilizarea conditiilor de selectie 1.3. Ordonarea rezultatului 1.4. Folosirea alias-urilor (poreclelor)

1. Interogarea datelor

1.1. Comanda SELECT

Comanda SELECT este cea mai utilizata comanda SQL. Ea permite atat regasirea si vizualizarea datelor din tabelele bazei de date cat si calcularea unor expresii care nu au legatura cu datele din tabele. Sintaxa comenzii SELECT pentru calcularea valorii unor expresii este foarte simpla si a fost deja folosita in capitolele anterioare:

SELECT expresie; Ex: SELECT 3*(5+12);

Utilizarea comenzii SELECT pentru interogarea tabelelor este mai complexa. Sintaxa de baza a comenzii in acest caz este:

SELECT [SQL_BUFFER_RESULT] [HIGH_PRIORITY] [DISTINCT | ALL] expresie_de_selectie,... [INTO {OUTFILE | DUMPFILE} 'nume_fisier'] [FROM referinte_tabele [WHERE conditie_selectie] [ORDER BY {intreg_pozitiv | nume_col | formula} [ASC | DESC] ,...]

Parametrii din paranteze drepte sunt optionali. Cei din acolade si despartiti prin '|' sunt obligatorii dar mutual exclusivi (doar unul poate apare). Ei au urmatoarea semnificatie:

l SQL_BUFFER_RESULT - se foloseste pentru a forta serverul sa creeze o tabela temporara cu rezultatul interogarii. Acest lucru este util in cazul in care tabela interogata este utilizata intensiv in retea si se doreste deblocarea rapida a ei pentru accesul altor clienti. In mod obisnuit nu este necesar.

l HIGH_PRIORITY - da prioritate interogarii curente fata de alte interogari INSERT sau UPDATE aflate in coada de asteptare pentru acea tabela.

l DISTINCT - Permite eliminarea randurilor duplicate din selectie.

ABD

41

l ALL - Rezultatul selectiei va cuprinde si randuri duplicate. Daca nu se specifica nici DISTINCT, nici ALL, implicit se considera ALL.

l expresie_de_selectie - expresia de selectie poate cuprinde referinte la coloane sau la functii aplicate asupra acestora. Un caz special il reprezinta caracterul * care semnifica includerea tuturor coloanelor din tabela in rezultat.

l INTO {OUTFILE | DUMPFILE} 'nume_fisier' - permite salvarea rezultatului unei interogari sub forma unui fisier text. Fisierul va fi creat pe server (trebuie sa nu existe), implicit in directorul bazei de date. Optiunea OUTFILE specifica salvarea tuturor randurilor rezultatului formatate pe coloane, pe cand DUMPFILE permite salvarea unui singur rand fara formatare. DUMPFILE este util pentru a salva campuri de tip BLOB (imagini, sune, video).

l FROMreferinte_tabele - specifica tabela sau tabelele din care se selecteaza datele. In cazul in care referinta cuprinde mai multe tabele operatia este una de JOIN.

l WHEREconditie_selectie - specifica conditia de includere a datelor in selectie. Permite selectia doar a datelor care intereseaza la un moment dat. In cazul operatiunii de JOIN aici se vor adauga si constrangerile date de relatiile dintre tabele. Conditia de selectie consta dintr-o expresie logica simpla sau complexa care poate cuprinde functiile prezentate in capitolul 3.

l ORDER BY {intreg_pozitiv | nume_col | formula} [ASC | DESC], ... - permite ordonarea rezultatului dupa anumite coloane sau formule. Daca se specifica o lista de numere, acestea vor fi interpretate ca numere de ordine a coloanelor specificate in SELECT (incepand cu 1). Ordonarea se face dupa prima coloana din lista. In cazul in care doua inregistari au aceiasi valoare pentru coloana respectiva se va lua in considerare a doua coloana din lista (daca este specificata) etc. Parametrii ASC si DESC specifica ordonarea crescatoare, respectiv descrescatoare a inregistrarilor in rezultat.

1.2. Utilizarea conditiilor de selectie

Conditiile de selectie permit regasirea datelor necesare intr-un anumit context. La scrierea acestor conditii vor putea fi folosie functiile prezentate in Capitolul 3. De asemenea, la folosirea operatorilor de comparatie se va tine seama de observatiile facute acolo.

1.3. Ordonarea rezultatului

In multe situatii este necesara ordonarea rezultatelor instructiunii SELECT. Un exemplu concludent este reprezentat de lista rezultatelor la un concurs. Daca la concurs s-au prezentat 2000 de candidati si rezultatul este afisat intr-o lista neordonata, probabil ca ar fi necesar ca un candidat sa piarda jumatate de zi pentru a se regasi pe lista. Ordonarea se poate controla prin clauza ORDER BY. Este important de inteles ca aceasta clauza presupune o operatie suplimentara, mare consumatoare de timp.

ABD

42

1.4. Folosirea alias-urilor (poreclelor)

Pentru a simplifica scrierea instructiunilor se pot folosi denumiri prescurtate, numite alias-uri (porecle). Ele pot fi folosite atat pentru a desemna tabele cat si coloane.

O utilitate deosebita o au in cazul combinarii datelor din mai multe baze de date, pentru a inlocui nume de tipul nume_baza_de_date2.nume_tabela_1.nume_coloana_3, in cazul coloanelor calculate sau in cazul operatiilor de JOIN (vor fi discutate in capitolul urmator).

Sintaxa de definire a unui alias este urmatoarea:

SELECT alias_t1.nume_coloana1 [AS] alias_c1,... FROM tabela1 alias_t1ORDER BY alias_c1 ...;

Dupa cum se observa, alias-urile definite pentru tabele pot fi folosite in partea de specificare a coloanelor, iar alias-urile coloanelor pot fi folosite in clauze ORDER BY sau HAVING (va fi discutata in capitolul urmator).


ABD

43

Cap.8. Interogari avansate

1. Selectarea datelor din mai multe tabele. Operatia JOIN. 1.1.Selectarea datelor din mai multe tabele. 1.2. Tipuri de JOIN. 2. Extragerea informatiilor de sumarizare.

1. Selectarea datelor din mai multe tabele. Operatia de JOIN.

1.1. Selectarea datelor din mai multe tabele.

Comanda SELECT in forma studiata in capitolul anterior permite efectuarea operatiei de selectare a datelor dintr-o singura tabela (implementeaza operatorul SELECT din algebra relationala).

Limbajul SQL foloseste insa tot comanda SELECT pentru implementarea operatorului JOIN. Acest operator este definit de algebra relationala pentru a permite colectarea datelor din tabelele aflate in legaturi relationale. Folosirea comenzii SELECT pentru a face JOIN presupune specificarea in clauza FROM a tabelelor de unde se preiau datele si, in lista de proiectie, a campurilor care vor face parte din rezultat. In acest caz forma generala a instructiunii SELECT este:

SELECT lista_campuri FROM lista_tabele WHERE conditii_join AND conditii_selectie ORDER BY ... ...


l lista_campuri - specifica acele campuri care vor face parte din rezultat. Aceste campuri pot fi din oricare tabela specificata in clauza FROM. In situatia in care un camp din rezultat apare sub acelasi nume in doua sau mai multe tabele el trebuie prefixat cu tabela din care se extrage. In locul numelui tabelei se prefera de obicei folosirea unui alias.

l lista_tabele - specifica tabelele din care se vor aduna datele. Unele tabele de aici pot fi doar tabele de legatura intre alte tabele, ele neavand nici un camp selectat in rezultat. Functie de tipul de operatie JOIN dorit, lista_tabele poate fi doar o lista de nume despartite prin virgula sau poate avea o forma mai execta, dupa cum se va preciza in sectiunea urmatoare.

l conditii_join - specifica conditiile relationale care leaga tabelele. Ele sunt de obicei de forma tabela1.cheie_externa=tabela2.cheie_primara AND .... Daca o legatura lipseste sau este pusa gresit, rezultatul va fi de obiecei un produs cartezian intre tabele (toate combinatiile posibile intre inregistrari, fara a tine seama de legaturile relationale).

ABD

44

l conditii_selectie - specifica conditiile de filtrare a rezultatului dupa adunarea datelor din tabele.

1.2. Tipuri de JOIN

Variantele posibile la operatia de JOIN se refera la modul in care sunt tratate inregistrarile care nu au corespondenta in fiecare tabela legata relational de tabelele in care inregistrarile respective apar.

Principial, rezultatul operatiei de JOIN poate include sau nu inregistrarile fara corespondenta. In primul caz operatia se numeste OUTER JOIN, iar in cel de al doilea INNER JOIN. Operatia de OUTER JOIN va completa automat campurile care lipsesc din tabela corespondenta cu valoarea NULL. Un OUTER JOIN are la randul sau doua varinate. Ambele includ toate inregistrarile care au corespondenta in ambele parti ale relatiei. In plus, LEFT OUTER JOIN va include in rezultat si inregistrarile din tabela stanga care nu au corespondent in tabela din partea dreapta a relatiei. In opozitie, RIGHT OUTER JOIN va include in rezultat si inregistrarile din tabela din dreapta care nu au corespondent in tabela din partea stanga. Operatia de INNER JOIN nu are variate pe stanga sau pe dreapta, pentru ca in ambele cazuri se vor include in rezultat doar capurile care au corespondent la ambele capete ale relatiei. Daca lista de tabele din SELECT cuprinde doar tabele despartite prin virgula se considera implicit o operatie de INNER JOIN.

Specificarea explicita a tipului de JOIN are sintaxa:

SELECT lista_campuri FROM tabela [INNER JOIN | LEFT OUTER JOIN | RIGHT OUTER JOIN] tabela ON conditie_join ... WHERE conditii_selectie ORDER BY ... ...

In cazul acesta se observa ca specificarea conditiei de join (legatura relationala dintre tabele) se face dupa fiecare pereche de tabele printr-o clauza ON separata. In mod natural, clauza WHERE va contine aici doar conditiile suplimentare de filtrare a inregistrarilor dorite.

2. Extragerea informatiilor de sumarizare.

Informatiile de sumarizare reprezinta informatii globale despre datele din tabele. Informatiile de sumarizare se extrag pe grupuri de inregistrari. Mai mult, ele nu au sens decat in contextul gruparii datelor.

Pentru a grupa datele se va utiliza urmatoarea sintaxa pentru instructiunea SELECT:

ABD

45

SELECT lista_campuri FROM lista_tabele WHERE conditii_selectie GROUP BY {colana | expresie} HAVING conditie_includere_grup ORDER BY ... ...

Clauza GROUP BY specifica dupa ce valoare se va face gruparea. In majoritatea cazurilor expresia de grupare este reprezentata de o singura coloana. Toate inregistrarile cu aceeasi valoare pentru expresia de grupare vor fi considerate ca facand parte din acelasi grup. Daca clauza GROUP BY lipseste, dar in lista_campuri apar informatii de sumarizare se considera implicit ca toate inregistrarile fac parte din acelasi grup.

Clauza HAVING permite selectarea grupurilor care sunt luate in considerare. Conditia din HAVING se aplica dupa constituirea grupului, pe cand conditia din clauza WHERE in timpul selectarii inregistrarilor. In plus, acesta clauza poate cuprinde doar referinte la campuri si aliasuri de campuri din lista de campuri a comenzii SELECT sau functii de sumarizare pe grup.

Lista de campuri din comanda SELECT poate include urmatoarele functii de sumarizare:

l COUNT(*) - numara cate inregistrari sunt selectate in fiecare grup. l COUNT(DISTINCT expr) - numara cate inregistrari pentru care expresia are

o valoare distincta sunt selectate in fiecare grup. l AVG(expr) - calculeaza media aritmetica a valorilor expresiei pentru

inregistrarile din fiecare grup. l MIN(expr) - calculeaza valoarea minima a expresiei pentru toate

inregistrarile din fiecare grup. l MIN(expr) - calculeaza valoarea maxima a expresiei pentru toate

inregistrarile din fiecare grup. l SUM(expr) - calculeaza suma valorilor expresiei pentru toate inregistrarile

din fiecare grup.

Atentie! Nu este permisa combinarea functiilor de sumarizare cu campurile din tabele decat in cazul in care campurile respective au o valoare unica in cadrul grupului. In cele mai multe cazuri acestea vor fi campurile din clauza GROUP BY.


ABD

46

Cap.9. Biblioteci mySQL. Accesul concurent la date.

1. Implemetarea programelor client. 1.1. Suport ODBC. 1.2. Suport JDBC. 1.3. Biblioteca MySQL C. 1.4. Suport Pearl. 1.5. Suport PHP. 2. Accesul concurent la date. 2.1. Tranzactii. Blocarea/deblocarea unei tabele. 2.2. Replicarea datelor.

1. Implemetarea programelor client.

1.1. Suport ODBC.

Standardul ODBC (Open DataBase Conectivity) a fost dezvoltat de firma Microsoft in scopul de a permite aplicatiilor Windows sa se conecteze la baze de date relationale. El reprezinta un strat intermediar (un driver) ce se interpune intre client si baza de date.

Rolul ODBC este acela de a transforma interogarile clientului si a returna rezultatele catre acesta. In acest sens driver-ul ODBC preia interogarile client scrise in sintaxa ODBC SQL (apropiata de ANSI SQL) si le transforma in sintaxa particulara a server-ului la care se face conectarea. De asemenea va tranforma rezultatele din formatul returnat de server intr-un format ODBC. In acest fel se realizeaza o "independenta" a clientului fata de server-ul de baza de date, acesta din urma putand fi schimbat fara modificari substantiale la nivelul programului client.

Sistemul MySQL pune la dispozitia programatorilor un driver numit MyODBC care asigura suport pentru acest standard.

MyODBC este un driver de nivel scazut (Level 0) pe 32 de biti si poate fi instalat sub Windows'95, Windows'98, WindowsNT si chiar sub mai multe variante de Unix. Suportul pentru Unix a fost gandit in ideea ca unele sisteme, cum ar fi de exemplu ColdFusion, ruleaza sub Unix dar necesita suport ODBC pentru conectarea la baza de date.

Pentru mai multe informatii despre utilizarea MySQL prin ODBC pot fi consultate la adresele:

l The FreeODBC Pages.

ABD

47

l The UnixODBC Project.

Dupa instalarea driver-ului MyODBC sub Windows se poate trece la crearea legaturii spre baza de date MySQL dorita prin intermediul managerului ODBC care se gaseste in Control Panel. Legatura se numeste DSN (Data Source Name) si poate fi folosita apoi pentru a deschide conexiunea ODBC la server-ul MySQL si apoi pentru a accesa baza de date specificata.

1.2. Suport JDBC.

Standardul JDBC (Java DataBase Conectivity) este asemanator celui prezentat anterior si a fost creat de catre firma SUN pentru a permite conectarea clientilor scrisi in limbajul Java la baze de date relationale.

Pentru MySQL au fost create doua drivere JDBC gratuite (numite "twz" respectiv "mm"). Ele pot fi descarcate de la adresa:

l http://www.mysql.com/Downloads/Contrib/

1.3. Biblioteca MySQL C.

Distributia standard MySQL vine cu biblioteca MySQL C API (Application Programming Interface). Ea cuprinde functii C de conectare si interogare a bazelor de date MySQL. Mai mult chiar, majoritatea clientilor MySQL sunt scrisi in limbajul C.

Fiecare client C va avea alocat un buffer gestionat dinamic pentru comunicarea cu server-ul. Acest buffer creste doar cand este necesar, astfel incat ocuparea resurselor calculatorului este minima.

Biblioteca C defineste o serie de tipuri de date care permit comunicarea cu server-ul:

l MYSQL - structura care gestioneaza conexiunea cu server-ul. l MYSQL_RES - reprezinta raspunsul server-ului la o interogare (result set). l MYSQL_ROW - reprezentarea unei inregistrari din rezultat. l MYSQL_FIELD - informatii despre un camp al unei tabele: nume, tip,

dimensiune etc.

Principalele functii din biblioteca sunt:

l mysql_real_connect() - realizeaza conexiunea cu server-ul

ABD

48

l mysql_close() - inchide conexiunea cu severul l mysql_select_db() - selecteaza o baza de date l mysql_query() - executa o comanda SQL trimisa sub forma unui sir de

caractere l mysql_row_seek() - cauta o valoare in rezultatul unei interogari l mysql_affected_rows() - intoarce numarul de inregistrari afectate de

comenzile INSERT, UPDATE sau DELETE l mysql_errno() - intoarce codul de eroare al ultimei operatii executate l mysql_fetch_row() - preia urmatoarea inregistrare din rezultat

Mai multe informatii despre biblioteca C pentru MySQL pot fi gasite in documentatia de la adresa:

l MySQL documentation

1.4. Suport Pearl.

Suportul Pearl oferit este materializat in interfata DBI cu DBD:mysql.

DBI (DataBase Interface) este o interfata Pearl comuna multor sisteme de gestiune a bazelor de date. In acest fel, clietii Pearl isi pastreaza independenta fata de server-ul de baze de date in maniera asemanatoare cu ODBC si JDBC.

Pentru a se conecta la un anumit server este necesar cun DBD (DataBase Driver). Pentru MySQL driver-ul se numeste DBD:mysql.

Interfata DBI pune la dispozitie o serie de metode printre care:

l connect - realizeaza conexiunea cu server-ul MySQL l disconnect - inchide conexiunea cu server-ul l do - pregateste si executa o comanda SQL l fetchrow_array - preia urmatoarea inregistrare din rezultat sub forma unui

tablou de campuri l rows - informeaza asupra numarului de inregistrari afectate de ultima

comanda

Informatii det

baze date si altele

Documents