Clasele 9-10 lecția 33 - 10 iun 2015

From Algopedia
Jump to navigationJump to search

Baze de date

Teorie

La modul general, o bază de date este orice colecție structurată de date. De exemplu, următoarele sunt baze de date.

  • un fișier text în care îmi notez persoane și numere de telefon
  • agenda telefonului meu, stocată într-un format mai general
  • un spreadsheet
  • o bibliotecă din lumea fizică
  • un fișier XML

Bazele de date pot urma diverse modele:

  • ierarhice (fișierele XML)
  • network (grafuri de date)
  • relaționale (cele despre care vom vorbi astăzi)

Bazele de date relaționale sunt ca niște tabele cu linii și coloane:

  • baza de date constă din mai multe tabele
  • fiecare tabel este o relație între coloanele sale
  • fiecare linie este o înregistrare
  • fiecare coloană este un atribut (o proprietate)
  • toate valorile de pe o coloană au același tip
  • ordinea liniilor nu contează (desigur, le putem sorta noi, dar ca stocare ordinea nu contează)
  • ordinea coloanelor nu contează
  • fiecare coloană are un nume unic

Restul lecției este o scurtă introducere în MySQL

MySQL - generalități

MySQL este un sistem de gestiune a bazelor de date relaționale, software liber. Prima versiune a apărut în 1995, iar programul este încă dezvoltat activ.

MySQL folosește un dialect al limbajului SQL. SQL este un limbaj pentru a gestiona datele dintr-o bază de date. Este convenabil pentru că, deși sistemele de baze de date folosesc formate proprii (MySQL, PostgreSQL, Oracle...), toate știu să își exporte datele ca o suită de comenzi MySQL. Acesta este cel mai simplu mod de a migra date între diverse sisteme.

MySQL constă dintr-un server (care rulează tot timpul) și diverse tipuri de clienți:

  • Interfețe grafice pentru crearea și administrarea bazelor de date. Nu vom vorbi despre ele.
  • Linia de comandă
  • Biblioteci pentru o mulțime de limbaje (PHP, Python, C etc.). Aceasta este o cărămidă de bază a site-urilor web care au în spate o bază de date.

Lansarea MySQL

Dacă apelăm doar mysql fără argumente, încercăm să lansăm un client ca utilizatorul local, fără parolă: 

mysql

După caz, putem să folosim alt user, cu sau fără parolă, și să deschidem direct o bază de date aparte: 

mysql -u <utilizator> -p <baza_de_date>

(Precizez că -p nu este urmat de parolă, ci doar îi spune lui mysql să citească parola de la tastatură).

Serverul de MySQL are diverse protecții, cum ar fi că anumiți useri pot accesa doar anumite baze de date. Mai mult, ei pot avea drept de citire, dar nu neapărat și de modificare / inserare / ștergere. Aceste protecții sunt utile când avem mai multe site-uri pe același server. Dacă unul dintre ele, să zicem Wordpress, are o vulnerabilitate și un atacator poate rula cod arbitrar în baza de date, ne asigurăm măcar că permisiunile lui nu se extind și la un alt site, să zicem MediaWiki.

De aici încolo, suntem în shell-ul MySQL. Toate comenzile trebuie terminate cu ; și putem tasta mai multe comenzi pe aceeași linie.

Crearea unei baze de date

Creăm o bază de date cu comanda 

create database <nume>;

Eventual, pentru a ne asigura că datele de tip text vor fi stocate în alfabetul UTF-8, putem specifica: 

create database <nume> charset utf8;

Crearea unui tabel

Pentru a crea un tabel, trebuie să specificăm schema tabelului, adică în esență:

  1. numele fiecărei coloane,
  2. tipul fiecărei coloane și
  3. eventualii indecși pe coloane sau pe combinații de coloane.

Indexul pe o coloană sau pe o combinație de coloane este util când avem de gând să facem căutări după valorile din acea coloană sau grup de coloane.

Dacă greșim ceva, nu este o problemă, schema poate fi modificată ulterior (vezi comanda alter table).

Să ne alegem un mini-proiect: o listă de trupe rock și date despre ele (am ales asta pentru că cei mai tari profesori întotdeauna dau exemple cu trupe rock). 

create table band (
  id int not null auto_increment,
  name varchar(255) not null,
  country varchar(255),
  yearFormed int,
  yearBrokeUp int,

  primary key(id),
  key(name)
);

Trebuie să explicăm deja multe lucruri:

  • Specificația unei coloane constă din numele ei urmat de tip.
  • Există multe tipuri de date.
  • Am specificat un câmp id. Este bine ca orice înregistrare să aibă și un număr unic de ordine, care să garanteze că oricare două rânduri nu sunt perfect identice. Acesta este un identificator asignat de noi oricum dorim (nu este preluat de pe AllMusic sau altundeva).
  • not null înseamnă că atributul respectiv nu poate lipsi. De exemplu, am ales ca numele trupei să fie neapărat nenul, dar ca anii și țara să poată lipsi, în caz că nu le știm.
  • auto_increment înseamnă că, dacă nu specific explicit o valoare pentru acea coloană când inserez o linie, ea va căpăta 1 + valoarea maximă deja existentă. În general, câmpul id are această proprietate.
  • Am folosit varchar (lungime variabilă). Aceste șiruri sunt stocate ca lungime (2 octeți) + datele. Ele sunt mai eficiente decât tipul char, care are lungime fixă.
  • primary key este un index unic, la fel ca și unique key. Cu alte cuvinte, nu pot exista două trupe cu același ID și primim o eroare dacă încercăm să inserăm o trupă cu un ID deja existent.
  • Pentru câmpul name, indexul nu este unic, căci pot exista trupe cu același nume, în țări diferite sau chiar în aceeași țară.
  • Putem scrie comanda pe o singură linie sau pe mai multe linii. Clientul de MySQL parsează linia până la caracterul „;”.

Putem examina oricând structura tabelului cu comanda describe 

mysql> describe band;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| name        | varchar(255) | NO   | MUL | NULL    |                |
| country     | varchar(255) | YES  |     | NULL    |                |
| yearFormed  | int(11)      | YES  |     | NULL    |                |
| yearBrokeUp | int(11)      | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

Inserarea

Există câteva sintaxe legale. Amintim trei din ele:

INSERT INTO <tabel> VALUES (<valori>)

Aici suplinim valori pentru fiecare câmp, în ordine. Pentru câmpuri cu auto_increment putem suplini valoarea 0 și sistemul va genera următoarea valoare disponibilă. 

mysql> insert into band values (0, 'Phoenix', 'România', 1962, null);
mysql> insert into band values (0, 'Rammstein', 'Germania', 1994, null);
mysql> insert into band values (0, 'Led Zeppelin', 'Anglia', 1968, 1980);
mysql> select * from band;
+----+--------------+----------+------------+-------------+
| id | name         | country  | yearFormed | yearBrokeUp |
+----+--------------+----------+------------+-------------+
|  1 | Phoenix      | România  |       1962 |        NULL |
|  2 | Rammstein    | Germania |       1994 |        NULL |
|  3 | Led Zeppelin | Anglia   |       1968 |        1980 |
+----+--------------+----------+------------+-------------+
3 rows in set (0.00 sec)

INSERT INTO <tabel> (<câmpuri>) VALUES (<valori>)

Aici suplinim valori pentru o parte din câmpuri, iar restul capătă valoarea implicită (care poate fi null). 

mysql> insert into band (name, country, yearFormed) values ('Phoenix', 'România', 1962);
mysql> insert into band (name, country, yearFormed) values ('Rammstein', 'Germania', 1994);
mysql> insert into band (name, country, yearFormed, yearBrokeUp) values ('Led Zeppelin', 'Anglia', 1968, 1980);

INSERT INTO <tabel> <câmp> = <valoare>, <câmp> = <valoare> ...

mysql> insert into band set name = 'Phoenix', country = 'România', yearFormed = 1962;
mysql> insert into band set name = 'Rammstein', country = 'Germania', yearFormed = 1994;
mysql> insert into band set name = 'Led Zeppelin', country = 'Anglia', yearFormed = 1968, yearBrokeUp = 1980;

Interogări

Interogările se fac cu comanda select. Iată un exemplu simplu: 

mysql> select * from band where yearFormed < 1970;
+----+--------------+----------+------------+-------------+
| id | name         | country  | yearFormed | yearBrokeUp |
+----+--------------+----------+------------+-------------+
|  1 | Phoenix      | România  |       1962 |        NULL |
|  3 | Led Zeppelin | Anglia   |       1968 |        1980 |
+----+--------------+----------+------------+-------------+
2 rows in set (0.00 sec)

Comanda select este foarte stufoasă. Înainte să încercăm diverse opțiuni, să populăm tabela cu mai multe date: 

truncate table band;
insert into band (name, country, yearFormed, yearBrokeUp) values
  ("AC/DC", "Australia", 1973, null),
  ("Aerosmith", "SUA", 1970, null),
  ("Beach Boys", "SUA", 1961, null),
  ("The Beatles", "Anglia", 1960, 1970),
  ("Bon Jovi", "SUA", 1983, null),
  ("Creedence Clearwater Revival", "SUA", 1967, 1972), 
  ("Deep Purple", "Anglia", 1968, null),
  ("The Doors", "SUA", 1965, 1973),
  ("Fleetwood Mac", "Anglia-SUA", 1967, null),
  ("The Grateful Dead", "SUA", 1965, 1995),
  ("Guns N' Roses", "SUA", 1985, null),
  ("Iron Maiden", "Anglia", 1975, null),
  ("Jethro Tull", "Marea Britanie", 1967, 2014),
  ("Journey", "SUA", 1973, null),
  ("INXS", "Australia", 1977, 2012),
  ("Led Zeppelin", "Anglia", 1968, 1980),
  ("Metallica", "SUA", 1981, null),
  ("Nirvana", "SUA", 1987, 1994),
  ("Pink Floyd", "Anglia", 1965, 2014),
  ("Phoenix", "România", 1962, null),
  ("Queen", "Marea Britanie", 1970, 1991),
  ("Rammstein", "Germania", 1994, null),
  ("Red Hot Chili Peppers", "SUA", 1983, null),
  ("U2", "Irlanda", 1976, null),
  ("The Who", "Anglia", 1964, null),
  ("ZZ Top", "SUA", 1969, null);

Câteva din opțiunile des folosite sunt:

  • selectăm doar anumite câmpuri: select name from band where yearFormed < 1970;
  • selectăm înregistrări după condiții compuse: select * from band where yearFormed < 1970 and yearBrokeUp is null;
  • returnăm doar numărul de înregistrări: select count(*) from band where yearFormed < 1970;
  • ordonăm înregistrările după un câmp: select * from band order by yearFormed;
  • idem, dar descrescător: select * from band order by yearFormed desc;
  • alegem doar primele 5 rezultate: select * from band order by yearFormed limit 5;
  • alegem valorile distincte dintr-o coloană: select distinct country from band;

Gruparea înregistrărilor

Putem face interogări mai complexe cu clauzele GROUP BY și HAVING.

Selectăm țările și numărul de trupe din fiecare țară: 

select country, count(*) from band group by country;

La fel, dar dăm un alias coloanei cu numărul, pentru a putea sorta după acea coloană: 

select country, count(*) as c from band group by country order by c desc;

Păstrăm doar țările din care avem cel puțin trei trupe: 

select country, count(*) as c from band group by country having c >= 3 order by c desc;

Subinterogări

Să spunem că dorim să introducem o tabelă separată pentru țări. Aceasta este bine din două motive:

  1. Reducem riscul unei greșeli de tipar
  2. Facilităm traducerea tabelei în alte limbi
create table country (
  id int not null auto_increment,
  name varchar(255),

  primary key(id)
);
insert into country (name) values ('Australia'), ('SUA'), ('Anglia'), ('Anglia-SUA'), ('Marea Britanie'), ('România'), ('Germania'), ('Irlanda');

Acum, în locul câmpului band.country vom introduce un câmp band.countryId. 

alter table band add countryId int after country;

În continuare, dorim să populăm câmpul countryId cu id-ul țării corespunzătoare fiecărei linii. Ca să nu facem de mână aceasta, dorim să exprimăm în SQL interogarea: „pentru fiecare linie din band, caută valoarea band.country în coloana country.name și copiază câmpul country.id corespunzător în câmpul band.countryId”: 

update band set countryId = (select id from country where name = band.country);

La final, putem renunța la câmpul text band.country. El poate fi dedus din band.countryId și din country.name, iar o regulă a bazelor de date este să evităm redundanța. 

alter table band drop country;

Join (joncțiuni)

Marea putere a limbajului SQL provine din capacitatea de a căuta în mai multe tabele simultan.

Dorim să obținem o listă a trupelor cu țara de origine a fiecăreia. Dar tocmai am mutat țările într-o tabelă separată, iar tabela <band> conține doar ID-uri de țări, care sunt neprietenoase. Iată cum putem combina cele două tabele pentru a obține și numele țării: 

select band.name, country.name from band join country on band.countryId = country.id;

Putem da fiecărei tabele un alias pentru a scurta interogarea. De asemenea, unde nu există ambiguitate putem lista doar câmpul, nu și tabela (dar atenție la lizibilitate): 

select b.name, c.name from band b join country c on countryId = c.id;

O formă mai puțin recomandată evită clauza join și enumeră pur și simplu tabelele, folosind o clauză where pentru a arăta cum sunt conectate: 

select b.name, c.name from band b, country c where countryId = c.id;

Pentru a găsi toate trupele australiene: 

select b.name from band b join country c on countryId = c.id where c.name = 'Australia';

Modificări, ștergeri

mysql> update band set yearBrokeUp = '2020' where name = 'Phoenix';
mysql> delete from band where name = 'Led Zeppelin';

Designul unei scheme bune

Să observăm că întotdeauna putem rafina o schemă pentru a reflecta și mai bine realitatea. Până la urmă, este o decizie de design unde să ne oprim. De exemplu, Fleetwood Mac este o trupă britanico-americană. Poate asta înseamnă că relația trupă-țară nu este n:1 ci n:m. Cu alte cuvinte, o trupă poate aparține de mai multe țări, iar o țară poate avea mai multe trupe.

Interfațarea cu alte limbaje de programare

Voi da un exemplu din PHP, din motive de comoditate (l-am folosit intens, mai mult decât pe cel de C/C++). 

<?php

$db = new PDO('mysql:host=localhost;dbname=test', 'root', '');

$query = 'select b.name, b.yearFormed ' .
  'from band b ' .
  'join country c on countryId = c.id ' .
  'where c.name = "Australia"';

$results = $db->query($query);

foreach($results as $row) {
  printf("Trupa %s fondată în %d\n", $row['name'], $row['yearFormed']);
}

unset($db);

?>
Retrieved from "https://www.algopedia.ro/wiki/index.php?title=Clasele_9-10_lecția_33_-_10_iun_2015&oldid=12333"