9. Baze de date. MySQL. - PROIECT MDB-MARE …proiectmdb.ucoz.com/baze_de_date_MySQL.pdf · 186 9....

12
186 9. Baze de date. MySQL. 9.1 Introducere 9.2 SQL şi MySQL 9.3 Clienti MySQL 9.4 Crearea unei baze de date. Organizarea informaţiei în tabele 9.5 Structura (design-ul) unei baze de date 9.1 Introducere O bază de date este o colecţie de înregistrări sau de informaţii introduse şi stocate într-un calculator într-un mod sistematic (structurat). O bază de date poate fi interogată (întrebată) de către noi sau de către un program prin intermediul unui limbaj relativ simplu (în general SQL) şi răspunde cu informaţie, în funcţie de care se iau decizii. Pentru valorificarea informaţiei ce poate fi extrasa dintr-o baza de date, este esenţial modul în care organizăm şi stocam datele intr-o bază de date. Aplicaţii ale bazelor de date pe Web Orice site care are un modul de login sau înregistrare utilizatori, orice magazin virtual, catalog de produse, forum, sistem de newsletter, articole, ştiri, etc. are în mod sigur o baza de date în care este ţinută informaţia în mod structurat. Cea mai mare parte a site-urilor de pe Internet care trec de nivelul de site de prezentare, şi vând produse, servicii sau conţinut informaţional (cursuri, articole) către utilizatorii site-ului folosesc baze de date. Aplicaţii ale bazelor de date in alte domenii O marte parte din aplicaţiile software existente folosesc baze de date pentru stocarea şi extragerea informaţiilor. Date de identificare, istoria plăţilor la telefonia mobila, electricitate, etc. sunt ţinute în mod structurat în bazele de date ale furnizorilor de servicii. În Statele Unite, informaţiile medicale, istoria creditelor, istoria angajărilor, precum şi alte informaţii aparţinând unei persoane, se pot obţine de instituţiile guvernamentale, pe baza numărului de asigurare socială al persoanei respective (SSN - social security number). Recensământul, rezultatele la vot, plus alte informaţii sociale, sunt ţinute în baze de date. Aceste baze de date se interoghează ulterior pentru obţinerea de diverse statistici. DBMS - Database Management System Aplicaţia care este folosită pentru a realiza, a administra şi a interoga o bază de date este numit sistemul de management al bazei de date (DBMS - Database Management System).

Transcript of 9. Baze de date. MySQL. - PROIECT MDB-MARE …proiectmdb.ucoz.com/baze_de_date_MySQL.pdf · 186 9....

Page 1: 9. Baze de date. MySQL. - PROIECT MDB-MARE …proiectmdb.ucoz.com/baze_de_date_MySQL.pdf · 186 9. Baze de date. MySQL. 9.1 Introducere 9.2 SQL şi MySQL 9.3 Clienti MySQL 9.4 Crearea

186

9. Baze de date. MySQL. 9.1 Introducere 9.2 SQL şi MySQL 9.3 Clienti MySQL 9.4 Crearea unei baze de date. Organizarea informaţiei în tabele 9.5 Structura (design-ul) unei baze de date

9.1 Introducere

O bază de date este o colecţie de înregistrări sau de informaţii introduse şi stocate într-un calculator într-un mod sistematic (structurat). O bază de date poate fi interogată (întrebată) de către noi sau de către un program prin intermediul unui limbaj relativ simplu (în general SQL) şi răspunde cu informaţie, în funcţie de care se iau decizii. Pentru valorificarea informaţiei ce poate fi extrasa dintr-o baza de date, este esenţial modul în care organizăm şi stocam datele intr-o bază de date. Aplicaţii ale bazelor de date pe Web

Orice site care are un modul de login sau înregistrare utilizatori, orice magazin virtual, catalog de produse, forum, sistem de newsletter, articole, ştiri, etc. are în mod sigur o baza de date în care este ţinută informaţia în mod structurat. Cea mai mare parte a site-urilor de pe Internet care trec de nivelul de site de prezentare, şi vând produse, servicii sau conţinut informaţional (cursuri, articole) către utilizatorii site-ului folosesc baze de date. Aplicaţii ale bazelor de date in alte domenii

O marte parte din aplicaţiile software existente folosesc baze de date pentru stocarea şi extragerea informaţiilor. Date de identificare, istoria plăţilor la telefonia mobila, electricitate, etc. sunt ţinute în mod structurat în bazele de date ale furnizorilor de servicii. În Statele Unite, informaţiile medicale, istoria creditelor, istoria angajărilor, precum şi alte informaţii aparţinând unei persoane, se pot obţine de instituţiile guvernamentale, pe baza numărului de asigurare socială al persoanei respective (SSN - social security number). Recensământul, rezultatele la vot, plus alte informaţii sociale, sunt ţinute în baze de date. Aceste baze de date se interoghează ulterior pentru obţinerea de diverse statistici.

DBMS - Database Management System

Aplicaţia care este folosită pentru a realiza, a administra şi a interoga o bază de date este numit sistemul de management al bazei de date (DBMS - Database Management System).

Page 2: 9. Baze de date. MySQL. - PROIECT MDB-MARE …proiectmdb.ucoz.com/baze_de_date_MySQL.pdf · 186 9. Baze de date. MySQL. 9.1 Introducere 9.2 SQL şi MySQL 9.3 Clienti MySQL 9.4 Crearea

187

Modelul unei baze de date este o specificaţie tehnică acceptată de mai mulţi furnizori de programe de baze de date (DBMS) ce se referă la modul în care sunt stocate informaţiile în baza de date şi modul în care sunt folosite. Exemple de modele sunt: modelul relaţional, modelul orientat-obiect, modelul ierarhic, etc. Cel mai răspândit în prezent este modelul relaţional. Bazele de date relaţionale au informaţiile organizate în tabele, iar între informaţiile din aceste tabele pot fi stabilite legături. Primele sisteme de baze de date relaţionale au apărut în 1970. Cele mai populare DBMS relaţionale sunt: Oracle, Microsoft SQL Server, MySQL. Toate aceste sisteme de baze de date relaţionale au în comun limbajul standard de interogare a bazei de date numit SQL.

9.2 SQL şi MySQL

SQL - Structured Query Language este un limbaj de baze de date realizat pentru a extrage informaţii şi a administra bazele de date relaţionale. Limbajul SQL a devenit standard ANSI (American National Standards Institute) în 1986.

Fiecare sistem de management al bazei de date (RDBMS -Relational Database Management System) comercial are propria versiune de limbaj SQL, bazată pe standardul SQL.

Astfel, limbajul SQL folosit în MySQL, fată de limbajul SQL folosit în PostgreSQL sau Oracle, deşi asemănătoare, au elemente distincte, specifice acelui RDBMS.

MySQL este o aplicaţie comercială pentru managementul bazelor de date relaţionale (pe scurt un RDBMS) foarte populară, mai ales în dezvoltarea aplicaţiilor web. MySQL este dezvoltată de firma suedeză MySQL AB ce a fost între timp cumpărata de Sun Microsystems. Echipele ce au dezvoltat limbajul PHP şi baza de date MySQL au colaborat cu succes de-a lungul timpului pentru a oferi o interoperabilitate ridicată între cele două programe, astfel încât prima preferinţă a programatorilor dezvoltatori în PHP pentru baze de date este MySQL. În plus, PHP are extensii (set de funcţii) pentru a lucra şi cu alte baze de date: PostgreSQL, Oracle, SQL Server, etc.

9.3 Clienţi MySQL

Sistemele de baze de date sunt concepute într-o arhitectura client-server. Astfel, serverul de baze de date este programul principal ce stochează şi manipulează datele, şi răspunde clienţilor (programe de tip

Page 3: 9. Baze de date. MySQL. - PROIECT MDB-MARE …proiectmdb.ucoz.com/baze_de_date_MySQL.pdf · 186 9. Baze de date. MySQL. 9.1 Introducere 9.2 SQL şi MySQL 9.3 Clienti MySQL 9.4 Crearea

188

client) ce se conectează la acesta pentru a cere informaţii sau pentru a trimite cereri de altă natură (adăugări, modificări, etc).

Serverul MySQL şi clientul MySQL folosit pentru interogare pot fi instalate pe acelaşi calculator, dar nu neapărat. Dacă lucrăm local (pe calculatorul propriu) şi folosim un program ca WAMP server, atât serverul MySQL cât şi clientul MySQL pe care-l alegem, vor fi instalate pe calculatorul nostru. În momentul când mutăm baza de date pe un server de web hosting, serverul MySQL va fi pe acel server de web hosting iar clientul MySQL poate fi tot pe acel server (de exemplu phpMyAdmin) sau ne putem conecta cu un client MySQL instalat pe calculatorul nostru (phpMyAdmin, MySQL Query Browser).

Pentru a administra o baza de date MySQL, un programator developer are în general urmatoarele optiuni: 1. phpMyAdmin este unul dintre cei mai folosiţi clienţi MySQL. Este disponibil în interfaţa web (web-based), uşor de folosit, şi instalat implicit pe marea majoritate a serverelor de găzduire. Astfel, un programator web ce a instalat aplicaţia WAMP Server (vezi anexa Instalare si configurare WAMP) pe calculatorul local, are automat instalat şi phpMyAdmin. 2. Executabilul mysql este un client fără interfaţă grafică, folosit din linia de comandă. Această variantă de administrare şi folosire a MySQL este folosită mai puţin de dezvoltatorii web insă sunt unele cazuri când se dovedeşte superioară celorlalte variante.

Pentru a executa mysql din linia de comanda vom parcurge următorii paşi:

• Mergeţi în Start -> Run, tastaţi cmd apoi Enter • Aici, dacă tastaţi mysql şi nu este recunoscută comanda

trebuie să continuaţi cu paşii următori, să adăugaţi folderul în care se află binarul mysql în variabila Path

• Mergeţi în Control Panel -> System -> Advanced -> Environment Variables -> System Variables, click Path -> Edit ;i adăugaţi calea către folderul respectiv, la "Variable value", folosind punct şi virgula înaintea ei astfel: ;c:\wamp\bin\mysql\mysql5.0.51b\bin

• Redeschideţi un terminal (linie de comandă) şi ar trebui când tastaţi mysql să intraţi în linia de comandă mysql ca în printscreen-ul următor

Page 4: 9. Baze de date. MySQL. - PROIECT MDB-MARE …proiectmdb.ucoz.com/baze_de_date_MySQL.pdf · 186 9. Baze de date. MySQL. 9.1 Introducere 9.2 SQL şi MySQL 9.3 Clienti MySQL 9.4 Crearea

189

• Pentru a avea drepturi depline, vă puteti conecta cu root, folosind sintaxa: mysql -u root -p

3. MySQL GUI Tools este o suită compusă din 3 aplicaţii cu interfaţa grafică (ce se instalează pe calculatorul local) pentru administrarea MySQL. Cele 3 aplicaţii sunt: MySQL Administrator , MySQL Query Browser, MySQL Migration Toolkit.

Page 5: 9. Baze de date. MySQL. - PROIECT MDB-MARE …proiectmdb.ucoz.com/baze_de_date_MySQL.pdf · 186 9. Baze de date. MySQL. 9.1 Introducere 9.2 SQL şi MySQL 9.3 Clienti MySQL 9.4 Crearea

190

9.4 Crearea unei baze de date. Organizarea informaţiei în tabele

Urmărind un exemplu simplu, vom vedea cum este organizată informaţia în MySQL (sau în alte baze de date relaţionale).

• Baza de date - serverul MySQL conţine mai multe baze de date. În funcţie de privilegiile cu care clientul MySQL se conectează la server, pot avea acces la toate bazele de date sau doar la o parte din ele. De cele mai multe ori o aplicaţie (web) foloseşte o singură bază de date la care se conectează pentru a actualiza sau extrage informaţii. Aplicaţiile mai complexe pot folosi mai multe baze de date la care se conectează simultan sau pe rând.

Folosind phpMyAdmin, o bază de date (goală, fară tabele) se

creează simplu, scriind numele bazei de date în câmpul "Create new

database" şi apăsând butonul "Create". Baza de date pe care o realizăm

în acest exemplu se numeşte "mybank".

Page 6: 9. Baze de date. MySQL. - PROIECT MDB-MARE …proiectmdb.ucoz.com/baze_de_date_MySQL.pdf · 186 9. Baze de date. MySQL. 9.1 Introducere 9.2 SQL şi MySQL 9.3 Clienti MySQL 9.4 Crearea

191

• Tabelul - o bază de date este compusă din mai multe tabele. Într-o aplicaţie pot avea câte un tabel pentru: utilizatori, produse, categorii de produse, încasări, etc. Dacă avem 100 de utilizatori, vom avea 100 de înregistrări (rânduri) în tabelul utilizatori. Nu vom intra în detalii deocamdată referitor la sintaxa sql de creare a unui tabel sau la posibilităţile interfeţei phpMyAdmin.

Pentru realizarea celor două tabele ale acestei baze de date, vom alege baza de date "mybank", apăsând pe linkul (butonul) SQL din meniul de mai sus, şi executând următorul codul sql:

-- -- Table structure for table `utilizatori` -- DROP TABLE IF EXISTS `utilizatori`; CREATE TABLE `utilizatori` ( `utilizatorId` int(11) unsigned NOT NULL auto_increment, `username` varchar(20) NOT NULL, `email` varchar(25) NOT NULL, `anNastere` smallint(4) unsigned default NULL, `sex` enum('m','f') NOT NULL,

Page 7: 9. Baze de date. MySQL. - PROIECT MDB-MARE …proiectmdb.ucoz.com/baze_de_date_MySQL.pdf · 186 9. Baze de date. MySQL. 9.1 Introducere 9.2 SQL şi MySQL 9.3 Clienti MySQL 9.4 Crearea

192

`dataAdaugarii` date NOT NULL, PRIMARY KEY (`utilizatorId`), UNIQUE KEY `username` (`username`), KEY `email` (`email`) ); -- -- Dumping data for table `utilizatori` -- -- -- Table structure for table `mesaje` -- DROP TABLE IF EXISTS `mesaje`; CREATE TABLE `mesaje` ( `mesajId` int(11) unsigned NOT NULL auto_increment, `utilizatorId` int(11) unsigned NOT NULL, `subiect` varchar(255) NOT NULL default 'fara subiect', `mesaj` text NOT NULL, `dataMesaj` datetime NOT NULL, PRIMARY KEY (`mesajId`) ); -- -- Dumping data for table `mesaje` -- Aceste instrucţiuni sql vor realiza două tabele în baza de date

mybank, numite utilizatori si mesaje şi introducem câteva rânduri în fiecare din aceste tabele.

-- -- Dumping data for table `utilizatori` -- INSERT INTO `utilizatori` (`utilizatorId`, `username`, `email`,

`anNastere`, `sex`, `dataAct`) VALUES (1, 'horia', '[email protected]', 1981, 'm', '2009-04-22');

INSERT INTO `utilizatori` (`utilizatorId`, `username`, `email`, `anNastere`, `sex`, `dataAct`) VALUES (2, 'luca', '[email protected]', 1978, 'f', '2009-04-23');

Page 8: 9. Baze de date. MySQL. - PROIECT MDB-MARE …proiectmdb.ucoz.com/baze_de_date_MySQL.pdf · 186 9. Baze de date. MySQL. 9.1 Introducere 9.2 SQL şi MySQL 9.3 Clienti MySQL 9.4 Crearea

193

INSERT INTO `utilizatori` (`utilizatorId`, `username`, `email`, `anNastere`, `sex`, `dataAct`) VALUES (3, 'noni', '[email protected]', 1969, 'm', '2008-09-01');

-- -- Dumping data for table `mesaje` -- INSERT INTO `mesaje` (`mesajId`, `utilizatorId`, `subiect`,

`mesaj`, `dataMes`) VALUES (1, 1, 'salut', 'Bine-ati venit pe forum !', '2009-04-25 15:00:18');

INSERT INTO `mesaje` (`mesajId`, `utilizatorId`, `subiect`, `mesaj`, `dataMes`) VALUES (2, 2, 'salut si aici', 'Salut,….', '2009-04-26 15:01:21');

INSERT INTO `mesaje` (`mesajId`, `utilizatorId`, `subiect`, `mesaj`, `dataMes`) VALUES (3, 3, 'întrebare?', 'despre ce se ..?', '2009-04-27 00:02:20');

INSERT INTO `mesaje` (`mesajId`, `utilizatorId`, `subiect`, `mesaj`, `dataMes`) VALUES (4, 1, 'despre...', 'despre baze de date', '2009-04-27 09:02:20');

INSERT INTO `mesaje` (`mesajId`, `utilizatorId`, `subiect`, `mesaj`, `dataMes`) VALUES (5, 1, 'ceva', 'despre mysql...', '2009-04-27 09:05:20');

Page 9: 9. Baze de date. MySQL. - PROIECT MDB-MARE …proiectmdb.ucoz.com/baze_de_date_MySQL.pdf · 186 9. Baze de date. MySQL. 9.1 Introducere 9.2 SQL şi MySQL 9.3 Clienti MySQL 9.4 Crearea

194

• Rândurile şi coloanele.

Un tabel al unei baze de date relaţionale, are rânduri şi coloane. Având în phpMyAdmin baza de date mybank selectăm, pentru a vedea înregistrările (informaţiile) din tabelul utilizatori, dând click pe iconiţa din stânga tabelului în lista de tabele, sau pe linkul (butonul) Browse din meniul de sus. Apăsând linkul Structure din meniu, putem vedea coloanele sau structura tabelului respectiv, precum şi tipul de date şi atributele fiecărei coloane în parte.

Înregistrările (sau rândurile) dintr-un tabel al unei baze de date relaţionale reprezintă o colecţie de date pentru o anumita categorie de entităţi. De exemplu, un tabel numit persoane poate ţine mai multe înregistrări, câte una pentru o persoana. Un tabel numit produse va avea o înregistrare pentru fiecare produs. Fiecare din aceste înregistrări are acelaşi set de atribute (coloanele tabelului), aceste atribute desemnând structura tabelului. Astfel, înregistrările (rândurile) unui tabel înseamnă informaţii, iar atributele (coloanele) unui tabel (sau unei înregistrări) înseamnă meta -informaţie sau structura informaţiei. În tabelul utilizatori avem atributul (coloana) utilizator de tipul varchar(20). Asta înseamnă că pentru fiecare înregistrare trebuie să am o informaţie pentru acest atribut, informaţie ce va desemna numele de utilizator, de tip sir de caractere, ce va avea maxim 20 de caractere. Intersecţia dintre o coloana şi un rând îl numim câmpul unei înregistrări. Exemplu, câmpul username al primei înregistrări este: "horia".

Structura (descrierea atributelor) tabelului utilizatoriîin phpMyAdmin

Page 10: 9. Baze de date. MySQL. - PROIECT MDB-MARE …proiectmdb.ucoz.com/baze_de_date_MySQL.pdf · 186 9. Baze de date. MySQL. 9.1 Introducere 9.2 SQL şi MySQL 9.3 Clienti MySQL 9.4 Crearea

195

9.5 Structura (design-ul) unei baze de date

• query - instrucţiune sql • insert - instrucţiune sql sau operaţie ce desemnează adăugarea

unei înregistrări intr-un tabel • update - instrucţiune sql sau operaţie ce desemnează

actualizarea unei înregistrări intr-un tabel • delete - instrucţiune sql sau operaţie ce desemnează ştergerea

unei înregistrări într-un tabel

Structura (design-ul) unei baze de date relaţionale constă în organizarea informaţiilor în tabele astfel încât informaţiile să poată fi extrase, să nu existe redundanţa informaţiilor (sau să fie foarte micî), să nu existe aşa-numitele anomalii de introducere, actualizare sau ştergere a înregistrărilor. Acest proces, de optimizare sistematică a structuri unei baze de date, a fost studiat si documentat de-a lungul timpului, şi denumit normalizare (sau database normalization).

Normalizarea unei baze de date înseamnă "spargerea" informaţiilor în tabele astfel încât datele să fie uşor de extras prin interogări (query) şi să se evite redundanţa şi anomaliile operaţiilor de introducere înregistrări (insert), actualizare (update) şi ştergere (delete).

Vom ilustra acest concept într-un exemplu simplu, folosind baza de date mybank.

Considerăm organizarea informaţiilor într-un singur tabel Teoretic, ar fi posibil să organizăm informaţiile unor baze de date

relaţionale într-un singur tabel. Informaţia in acest caz este nenormalizată, şi în folosirea ei în

practică într-o bază de date relaţională apar următoarele probleme: • Probleme de logica şi extragere a informaţiei. Este ilogic să

avem informaţiile a două entităţi distincte (utilizator şi mesaj) ţntr-un singur mesaj. Nu putem de exemplu număra printr-o interogare simplă utilizatorii înscrişi, pentru că numărul de rânduri din acest tabel indică de fapt numărul de mesaje. Nu pot realiza printr-o interogare simplă media vârstei utilizatorilor acestui forum. etc.

• Probleme de redundanţă (duplicare a informaţiei). Duplicarea informaţiei generează nu doar anomaliile de mai jos şi

alte probleme de logică, ci şi probleme de spaţiu de stocare pentru această bază de date. Un site de succes ce găzduieşte un forum cu mii de utilizatori şi sute de mii de mesaje ar ocupa spaţiu în plus. Pentru fiecare

Page 11: 9. Baze de date. MySQL. - PROIECT MDB-MARE …proiectmdb.ucoz.com/baze_de_date_MySQL.pdf · 186 9. Baze de date. MySQL. 9.1 Introducere 9.2 SQL şi MySQL 9.3 Clienti MySQL 9.4 Crearea

196

mesaj trimis, se stochează în baza de date şi toate datele utilizatorului respectiv. Spaţiul necesar stocării acestei baze de date ar fi mult mai mare decât în mod normal, şi orice operaţii asupra bazei de date ar fi mult mai incete.

• Anomalie de update - de fiecare dată când utilizatorul horia scrie un mesaj, îi scriem toate datele din nou, e posibil să greşim datele lui la un moment dat. Astfel, baza de date devine inconsistentă, adică unele rânduri arată anumite informaţii despre acest utilizator, alte rânduri arată informaţii greşite. Dacă dorim să actualizăm o informaţie legată de un utilizator, să presupunem că horia îşi schimbă emailul, trebuie să căutăm în acest tabel toate apariţiile lui horia şi să facem actualizarea în mai multe locuri.

• Anomalie de insert - avem poate utilizatori ce nu vor să scrie nici un mesaj, şi atunci nu putem ţine datele despre ei pentru că nu au scris nici un mesaj iar acest tabel aşa cum e compus cere şi datele unui mesaj. Am putea să adăugam un rând cu datele utilizatorului şi cu date goale pentru mesaj dar atunci am avea alte probleme (de aplicaţie) cum ar fi de exemplu prezenţa pe forum a unui mesaj gol (fără subiect, fără text, etc).

• Anomalie de delete - dacă ştergem toate mesajele lui horia, pierdem automat şi informaţia acestui utilizator

Normalizarea bazei de date - "spargerea" informaţiei în mai multe tabele.

Având problemele de mai sus, ideal ar fi să organizăm informaţia în două tabele: utilizatori şi mesaje. Fiecărui tabel i-am adăugat o coloana de tip întreg, ce identifică în mod unic rândul respectiv prin atribuirea unui număr. De atribuirea şi păstrarea unicităţii acestui număr se ocupă serverul MySQL prin setarea atributului AUTO_INCRE-MENT pentru această coloană. Pentru tabelul mesaje acea coloană este mesajId, iar pentru utilizatori este utilizatorId. Într-o bază de date relaţionala, este foarte indicat (chiar obligatoriu in practica) să existe o astfel de coloana care să nu aibă valori nule şi să nu aibă valori ce se repeta deci să determine în mod unic o înregistrare. Această coloana se numeşte cheie primara (sau primary key sau PK). Ea nu trebuie neapărat să fie de tip întreg, deşi în general aşa se obişnuieşte.

Observăm că fiecare utilizator are un id (un număr unic). În tabelul mesaje, pentru a şti ce utilizator a postat un anume mesaj, trebuie să adăugam o coloana în care să ţinem valoarea id-ului unui utilizator, astfel încât să facem legătura logică cu tabelul de utilizatori. În acest fel,

Page 12: 9. Baze de date. MySQL. - PROIECT MDB-MARE …proiectmdb.ucoz.com/baze_de_date_MySQL.pdf · 186 9. Baze de date. MySQL. 9.1 Introducere 9.2 SQL şi MySQL 9.3 Clienti MySQL 9.4 Crearea

197

toate mesajele trimise de horia, vor avea în dreptul coloanei utilizatorId valoarea 1, id-ul corespunzător acestui utilizator. Această coloană se numeşte cheie străină (sau foreign key sau FK) şi indica prezenţa în alt tabel a valorilor unei coloane ce e cheie primară într-un tabel iniţial.