!! mysql rezumat

Lectia 1

Introducere

Limbajul Sql (Structured Query Language) permite accesarea şi manipularea bazeler de date. Limbajul Sql este un standard ANSI (American National Standards Institute). Dintre programle de gestionare a bayelor de date (MS SQL Server, IBM DB2, Oracle, MySQL şi Microsoft Access) cursul prezinta MySql.

Limbajul MySql nu face direnta intre liter mici si litere mari. Comenzile sunt propoziţii cu o reguli de sintaxă foarte uşoare.

Comenzile se impart în două grupuri: DML (Data Manipulation Language) şi DDL (Data Definition Language).

Comenzi DML: SELECT - extrage date UPDATE - actualizeaza date DELETE - sterge date INSERT INTO - insereaza date

Comenzi DDL: CREATE DATABASE - creaza o baza de date ALTER DATABASE - modifica o baza de date CREATE TABEL - creaza un tabel ALTER TABLE - modifica un tabel DROP TABLE - sterge un tabel CREATE INDEX – creaza o cheie dupa care se indexeaza tabelul

Pornirea mediului

Dupa instalarea pachetului EasyPHP se creaza un shortcut catre cmd.exe (MS-DOS) si seteaza start in la cale\EasyPHP_numar_versiune\mysql\bin. O posibila cale de instalare (cel standard) ar putea fi: C:\Program Files\EasyPHPx-y\mysql\bin. Lansarea MySql se face cu comanda scrisa cu litere mici:

mysql –u root -p

Lectia 2

Comenzi de selectie dupa diferite criterii

Comanda SELECT

Se foloseste pentru a selecta date dintr-un tabel.

SELECT nume_coloane FROM nume_tabel

Pentru exemplificari o sa folosim tabelul scolar cu campii (coloanele): id, nume, prenume, adresa, clasa, media si camin. Cheia primara es coloana id. Pentru a lista intreg tabelul scriem:

SELECT * FROM scolar

+------+-----------+----------+--------------------+-------+-------+-------+| id | nume | prenume | adresa | clasa | media | camin |+------+-----------+----------+--------------------+-------+-------+-------+| 0 | Danes | Petru | Ciucas 7/2 | VIIIB | 5.66 | 1 || 11 | Baba | Robert | Carpati 17/123 | VIIIB | 7.5 | 0 || 31 | Baba | Beatrice | Carpati 17/26 | IXA | 9.66 | 0 || 25 | Vladimir | Fiorosul | Carpati 23/34 | VIIIB | 7.889 | 1 || 111 | Dogaru | Bogdan | Apicultorilor 12/2 | VIIC | 5.66 | 1 |+------+-----------+----------+--------------------+-------+-------+-------+

SELECT prenume, nume, adresa FROM scolar

+-----------+----------+--------------------+| prenume | nume | adresa |+-----------+----------+--------------------+| Petru | Danes | Ciucas 7/2 || Robert | Baba | Carpati 17/123 || Beatrice | Baba | Carpati 17/26 || Fiorosul | Vladimir | Carpati 23/34 || Bogdan | Dogaru | Apicultorilor 12/2 |+-----------+----------+--------------------+

Comanda SELECT DISTINCT

Se foloseste pentru a selecta numai datele distincte dintr-un tabel.

SELECT DISTINCT nume_coloane FROM nume_tabel

Comanda returneaza numai valorile distincte

SELECT DISTINCT clasa FROM scolar

+-------+| clasa |+-------+| VIIIB || IXA || VIIC |+-------+

Comanda SELECT WHERE

Se foloseste pentru a selecta numai acele date care satisfac o anumita conditie.

SELECT nume_coloane FROM nume_tabel WHERE conditie

Pentru a selecta numai elevii din clasa VIIIB vom folosii comanda

SELECT * clasa FROM scolar WHERE clasa=’VIIIB’

+------+-----------+----------+--------------------+-------+-------+-------+| id | nume | prenume | adresa | clasa | media | camin |+------+-----------+----------+--------------------+-------+-------+-------+| 0 | Danes | Petru | Ciucas 7/2 | VIIIB | 5.66 | 1 || 11 | Baba | Robert | Carpati 17/123 | VIIIB | 7.5 | 0 || 25 | Vladimir | Fiorosul | Carpati 23/34 | VIIIB | 7.889 | 1 |+------+-----------+----------+--------------------+-------+-------+-------+

In conditiia selectarii valorile tip sir de caractere trebuie totdeauna incluse intre ghilimele simple (apostrofuri) iar valori numere intregi se scriu fara ghilimele.

SELECT nume, prenume, adresa, camin FROM scolar WHERE camin=1

+-----------+----------+--------------------+-------+| nume | prenume | adresa | camin |+-----------+----------+--------------------+-------+| Danes | Petru | Ciucas 7/2 | 1 || Vladimir | Fiorosul | Carpati 23/34 | 1 || Dogaru | Bogdan | Apicultorilor 12/2 | 1 |+-----------+----------+--------------------+-------+

In condiţia selectării putem folosii următorii operatori relaţionali: =, <>, >, <, >=, <=, LIKE, BETWEEN, IN.

SELECT nume, prenume FROM scolar WHERE media between 7.2 and 10

+-----------+----------+| nume | prenume |+-----------+----------+| Baba | Robert || Baba | Beatrice || Vladimir | Fiorosul |+-----------+----------+

Operatorii logici OR si AND

Se folosesc pentru a rafina si mai mult posibilitatile de selectie dintr-un tabel.

SELECT * FROM scolar WHERE nume=’Baba’ OR nume=’Dogaru’

+------+-----------+----------+--------------------+-------+-------+-------+| id | nume | prenume | adresa | clasa | media | camin |+------+-----------+----------+--------------------+-------+-------+-------+| 11 | Baba | Robert | Carpati 17/123 | VIIIB | 7.5 | 0 || 31 | Baba | Beatrice | Carpati 17/26 | IXA | 9.66 | 0 || 111 | Dogaru | Bogdan | Apicultorilor 12/2 | VIIC | 5.66 | 1 |+------+-----------+----------+--------------------+-------+-------+-------+

Cuvantul cheie ORDER BY

Se foloseste pentru a ordona rezultatul dupa una sau mai multe coloane.

SELECT nume_coloane FROM nume_tabel ORDER BY nume_coloane ASC|DESC

Folosesim cuvinte ASC|DESC pentru ordonarea ascendenta sau descendenta. Daca nu se specifica nimic rezuntatul va fi ordonat ascendent.

SELECT * FROM scolar ORDER BY media DESC

+------+-----------+----------+--------------------+-------+-------+-------+| id | nume | prenume | adresa | clasa | media | camin |+------+-----------+----------+--------------------+-------+-------+-------+| 31 | Baba | Beatrice | Carpati 17/26 | IXA | 9.66 | 0 || 25 | Vladimir | Fiorosul | Carpati 23/34 | VIIIB | 7.889 | 1 || 11 | Baba | Robert | Carpati 17/123 | VIIIB | 7.5 | 0 || 0 | Danes | Petru | Ciucas 7/2 | VIIIB | 5.66 | 1 || 111 | Dogaru | Bogdan | Apicultorilor 12/2 | VIIC | 5.66 | 1 |+------+-----------+----------+--------------------+-------+-------+-------+

SELECT * FROM scolar ORDER BY nume, prenume

+------+-----------+----------+--------------------+-------+-------+-------+| id | nume | prenume | adresa | clasa | media | camin |+------+-----------+----------+--------------------+-------+-------+-------+| 31 | Baba | Beatrice | Carpati 17/26 | IXA | 9.66 | 0 || 11 | Baba | Robert | Carpati 17/123 | VIIIB | 7.5 | 0 || 0 | Danes | Petru | Ciucas 7/2 | VIIIB | 5.66 | 1 || 111 | Dogaru | Bogdan | Apicultorilor 12/2 | VIIC | 5.66 | 1 || 25 | Vladimir | Fiorosul | Carpati 23/34 | VIIIB | 7.889 | 1 |+------+-----------+----------+--------------------+-------+-------+-------+

Lectia 3

Tipuri de date MySql

In MySQL exista 3 tipuri de date: text, number si Date/Time.

Pentru tipul text avem urmatoarele tipuri de date :

CHAR VARCHAR TINYTEXT TEXT BLOB (Binary Large oBjects) MEDIUMTEXT MEDIUMBLOB LONGTEXT LONGBLOB ENUM SET

Cele mai des folosite tipuri de date pentru tipul text:

Tip de data DescriereCHAR(size) Sir de caractere cu lungime fixa. Lungimea este definita de

parametrul size. Maxim 255 de caractere.VARCHAR(size) Sir de caractere cu lungime variabila. Lungimea maxima este

definita de parametrul size. Daca definim o lungime mai mare de 255 de caractere este convertit in tipul TEXT.

TEXT Sir de caractere cu lungime maxima de 65535 de caractere.MEDIUMTEXT Sir de caractere cu lungime maxima de 16777215 de caractere.MEDIUMBLOB BLOB (Binary Large oBjects). Memoreaza 16777215 bytes de

date, aproximativ 16 MB. Poate fi folosit pentru stocarea imaginilor direct in baza de date!

Pentru tipul number avem urmatoarele tipuri de date:

TINYINT SMALLINT MEDIUMINT INT BIGINT FLOAT DOUBLE DECIMAL

Cele mai des folosite tipuri de date pentru tipul number:

Tip de data DescriereTINYINT(size) De la -128 la 127. De la 0 la 255 pentru UNSIGNED. Numarul

maxim de cifre este specificat de parametrul size.SMALLINT(size) De la -32768 la 32767. De la 0 la 65535 pentru UNSIGNED.INT(size) De la -2147483648 la 2147483647. De la 0 la 4294967295 pentru

UNSIGNED.BIGINT(size) De la -9223372036854775808 la 9223372036854775807. De la 0

la 18446744073709551615 pentru UNSIGNED.FLOAT(size,d) Pentru numere reale mici. Numarul maxim de zecimale se

specifica cu ajutorul parametrului d.DOUBLE(size,d) Pentru numere reale mari.

Pentru tipul date date/time avem următoarele tipuri de date:

DATE DATETIME TIMESTAMP TIME YEAR

Cele mai des folosite tipuri de date pentru tipul number:

Tip de data DescriereDATE() Data calendaristica in formatul: YYYY-MM-DDDATETIME() O combinatie intre data si timp. Format: YYYY-MM-DD

HH:MM:SSTIME() Timp. Format: HH:MM:SSYEAR() Un an in format de 2 sau 4 cifre.

Lectia 4

Actualizări de date

Comanda CREATE DATABASE

Se foloseşte pentru a crea o nou bază de date

CREATE nume_baza_de_date

Comanda DROP DATABASE

Se foloseşte pentru a şterge o bază de dată existentă

DROP DATABASE nume_baza_de_date

Comanda SHOW DATABASES

Se foloseşte pentru a lista toate bazele existente pe serverul MySql

SHOW DATABASES

Comanda USE

Se foloseşte pentru a selecta baza de dată activă dintre toate bazele existente pe serverul MySql

USE nume_baza_de_date

Pentru exemplificări o să folosim tabelul scolar cu câmpii: id, nume, prenume, adresa, clasa, media si camin. Cheia primară este coloana id. Pentru a listarea tabelului scriem:



Comanda CREATE TABLE

Se foloseşte pentru a crea un tabel într-o bază de dată, comanda stabileşte şi structura tabelului: denumirea coloanelor şi tipul de date asociate acestora.

CREATE TABLE nume_tabel ( nume_coloana_1 tip_data_1, nume_coloana_2 tip_data_2, ...,

nume_coloana_n tip_data_n,)Pentru crearea tabelului nostru scolar folosim comanda:

CREATE TABLE scolar ( id tinyint(3), nume varchar(20), prenume varchar(20), adresa varchar(50), clasa varchar(5), media float(5,2), camin tinyint(1))

Comanda INSERT INTO

Se foloseste pentru a adauga date (randuri) intr-un tabel existent. Comanda are doua variante de sintaxa:

INSERT INTO nume_tabelVALUES (valoare_1, valoare_2, valoare_3, ...)

Respectiv:

INSERT INTO nume_tabel (nume_coloana_1, nume_coloana_2, ...)VALUES (valoare_1, valoare_2, ...)

Pentru a adauga date in tabelul nostru scolar putem folosii:

INSERT INTO scolarVALUES (11, ‘Baba’, ‘Robert’, ‘Carpati 17/123’, ‘VIIIB’, 7.5, 0)

Pentru a adauga date numai in coloanele specificate putem folosii a doua varianta:

INSERT INTO scolar (id, nume, prenume, clasa)VALUES (69, ‘Scufita’, ‘Rosie’, ‘IVF’)

Daca rulam acum comanda select pentru tabelul scolar:


+------+-----------+----------+--------------------+-------+-------+-------+| id | nume | prenume | adresa | clasa | media | camin |+------+-----------+----------+--------------------+-------+-------+-------+| 0 | Danes | Petru | Ciucas 7/2 | VIIIB | 5.66 | 1 || 11 | Baba | Robert | Carpati 17/123 | VIIIB | 7.5 | 0 || 31 | Baba | Beatrice | Carpati 17/26 | IXA | 9.66 | 0 || 25 | Vladimir | Fiorosul | Carpati 23/34 | VIIIB | 7.889 | 1 || 111 | Dogaru | Bogdan | Apicultorilor 12/2 | VIIC | 5.66 | 1 || 69 | Scufita | Rosie | | IVF | | |+------+-----------+----------+--------------------+-------+-------+-------+

Comanda UPDATE

Se foloseste pentru a modifica valorile intr-un rand al tabelului (pentru o inregistrare).

UPDATE nume_tabelSET nume_coloana_1=valoare_1, nume_coloana_2=valoare_2, ...WHERE conditie

ATENTIE!!! Daca conditia ( de exemplu: nume_coloana_x=valoare_x) este specificat gresit sau este omis atunci se modifica toate randurile tabelului pierzand datele respective.

Petru tabelul scolar daca dorim sa modificam valorile coloanele adresa, media si camin pentru intrarea 69, Scufita Rosie

UPDATE scolarSET adresa=’Padurea Verde 5/7’, media=9.96, camin=0WHERE nume=’Scufita’ AND prenume=’Rosie’

Petru tabelul elevi cu structura si datele de mai jos dorim sa calculam mediile elevilor

+----+-----------+----------+------+------+------+------+-------+| id | nume | prenume | mate | info | arte | engl | media |+----+-----------+----------+------+------+------+------+-------+| 1 | Georgescu | Stefania | 5 | 6 | 7 | 9 | 0 || 2 | Calinescu | Mara | 10 | 10 | 8 | 9 | 0 || 4 | Nistor | Stefania | 10 | 6 | 8 | 9 | 0 || 10 | Pop | Rodica | 6 | 5 | 4 | 9 | 0 |+----+-----------+----------+------+------+------+------+-------+

Comanda pentru modificarea campului medie pentru fiecare elevi

UPDATE eleviSET media=(mate+info+arte+engl)/4

+----+-----------+----------+------+------+------+------+-------+| id | nume | prenume | mate | info | arte | engl | media |+----+-----------+----------+------+------+------+------+-------+| 1 | Georgescu | Stefania | 5 | 6 | 7 | 9 | 6.75 || 2 | Calinescu | Mara | 10 | 10 | 8 | 9 | 9.25 || 4 | Nistor | Stefania | 10 | 6 | 8 | 9 | 8.25 || 10 | Pop | Rodica | 6 | 5 | 4 | 9 | 6 |+----+-----------+----------+------+------+------+------+-------+

Comanda DELETE FROM

Se foloseste pentru a sterge un rand al tabelului (o inregistrare).

DELETE FROM nume_tabelWHERE conditie

Pentru a sterge inregistrarea 69, Scufita Rosie din tabelului scolar

DELETE FROM scolarWHERE id=69

Pentru a sterge elevii cu medii intre 5 si 8 din tabelului elevi

DELETE FROM elevi

WHERE media between 5 and 8

+----+-----------+----------+------+------+------+------+-------+| id | nume | prenume | mate | info | arte | engl | media |+----+-----------+----------+------+------+------+------+-------+| 2 | Calinescu | Mara | 10 | 10 | 8 | 9 | 9.25 || 4 | Nistor | Stefania | 10 | 6 | 8 | 9 | 8.25 |+----+-----------+----------+------+------+------+------+-------+

Lectia 5

Functii MySql

Functii scalare

UCASE() sau UPPER() - Converteste campul la litere mari LCASE() sau LOWER() - Converteste campul la litere mici LENGTH() - Returneaza lungime unui camp de text INSTR() - Cautarea unui subsir intrun alt sir SUBSTRING() - Returneaza un subsir POW() - Ridicare la putere ROUND() - Rotunjeste un numar la numarul de zecimale specificate NOW() - Returneaza data si timpul actual FORMAT() - Formateaza modul de afisare a unui camp

Functiile scalare returneaza o valoare in functie de parametrul (campul) transmis. Iata un exemplu care foloseste doua dintre aceste functii.

SELECT UCASE(nume), FORMAT(media, 1) FROM scolar

+-------------+------------------+| UCASE(nume) | FORMAT(media, 1) |+-------------+------------------+| DANES | 5.6 || BABA | 7.5 || BABA | 9.6 || VLADIMIR | 7.8 || DOGARU | 5.6 |+-------------+------------------+

Putem folosii un alias (o alta denumire) pentru afisariea campilor capului de tabel

SELECT UCASE(nume) AS nume, FORMAT(media, 1) AS media FROM scolar

Functii agregate

AVG() - Returneaza valoare medie COUNT() - Returneaza numarul de randuri MAX() - Returneaza maximul MIN() - Returneaza manimul SUM() - Returneaza suma

Functiile agregate returneaza o valoare pe baza ma multori valori din coloana specificata. Pentru calcularea mediei clasei pentru tabelul scolar

SELECT AVG(media) AS media_clasa FROM scolar

+------------------+| media_clasa |+------------------+| 7.27379989624023 |+------------------+

Continutul tabelul scolar


Afisam numarul de elevi caministi

SELECT COUNT(camin) AS nr_caministi FROM scolar WHERE camin=1

+--------------+| nr_caministi |+--------------+| 3 |+--------------+

Subinterogari

O interogare poate returna o coloana ca in cazurile anterioare, dar poate returna si un singur rand sau mai multe coloane. Interogarile mai complexe se bazeaza pe subinterogari. Totdeauna subinterogarea se efectueaza prima data.

Sa afisam descrescator dupa medii elevii cu medii mai mari decat media clasei

SELECT nume, prenume, adresa, clasa, media FROM scolarWHERE media>=(SELECT AVG(media) FROM scolar) ORDER BY media DESC

+----------+----------+----------------+-------+-------+| nume | prenume | adresa | clasa | media |+----------+----------+----------------+-------+-------+| Baba | Beatrice | Carpati 17/26 | IXA | 9.66 || Vladimir | Fiorosul | Carpati 23/34 | VIIIB | 7.889 || Baba | Robert | Carpati 17/123 | VIIIB | 7.5 |+----------+----------+----------------+-------+-------+

Sa afisam elevii care sunt colegi de clasa cu elevul Baba Robert

SELECT nume, prenume, clasa, media FROM scolarWHERE clasa=(SELECT clasa FROM scolar WHERE id=11)

+----------+----------+-------+-------+| nume | prenume | clasa | media |+----------+----------+-------+-------+| Danes | Petru | VIIIB | 5.66 || Baba | Robert | VIIIB | 7.5 || Vladimir | Fiorosul | VIIIB | 7.889 |+----------+----------+-------+-------+

Sa afisam elevul cu media cea mai mare

SELECT nume, prenume, adresa, clasa, media FROM scolarWHERE media=(SELECT MAX(media) FROM scolar)

+------+----------+---------------+-------+-------+| nume | prenume | adresa | clasa | media |+------+----------+---------------+-------+-------+| Baba | Beatrice | Carpati 17/26 | IXA | 9.66 |+------+----------+---------------+-------+-------+

Gruparea datelor

Gruparea datelor folosit impreuna cu functiile agregate ne permit afisarea acestora grupate pe una sau mai multe coloane. Sintaxa de baza

SELECT nume_coloana, functie_agregat(nume_coloana)FROM nume_tabel WHERE conditie GROUP BY nume_coloana

Sa afisam mediile generale a claselor din tabelul scolar

SELECT clasa, FORMAT(AVG(media), 2) AS media_clasaFROM scolar GROUP BY clasa

+-------+-------------+| clasa | media_clasa |+-------+-------------+| IXA | 9.66 || VIIC | 5.66 || VIIIB | 7.02 |+-------+-------------+

Sa afisam cati elevi locuiesc pe fiecare strada

SELECT SUBSTRING(adresa, 1, INSTR(adresa, ‘ ‘)-1) AS strada, COUNT(*) AS elevi FROM scolar GROUP BY strada

+---------------+-------+| strada | elevi |+---------------+-------+| Apicultorilor | 1 || Carpati | 3 || Ciucas | 1 |+---------------+-------+

Sa afisam cel mai bun (cu media cea mai mare) elev din fiecare clasa

SELECT nume, prenume, FORMAT(MAX(media), 2) AS mediamax, clasaFROM scolar GROUP BY clasa

+--------+----------+----------+-------+| nume | prenume | mediamax | clasa |+--------+----------+----------+-------+| Baba | Beatrice | 9.66 | IXA || Dogaru | Bogdan | 5.66 | VIIC || Danes | Petru | 7.89 | VIIIB |+--------+----------+----------+-------+

Lectia 6

Uniuni de tabele

Comenzi

INNER JOIN – Returnează rândurile care au cel putin o potrivire în ambele tabele

LEFT JOIN – Returnează toate rândurile din tabelul stâng, chiar dacă nu există nicio potrivire în ambele tabele

RIGHT JOIN – Returnează toate rândurile din tabelul drept, chiar dacă nu există nicio potrivire în ambele tabele

Aceste comenzi se folosesc pentru a interoga 2 sau mai multe tabele şi se bazează pe relaţia între 2 sau mai multe coloane din aceste tabele.

SELECT coloana(e) FROM tabel1 INNER JOIN tabel2ON tabel1.coloana_x=tabel2.coloana_y

Pentru a exemplifica aceste comenzi folosim următoarele două tabele persoane şi comenzi:

+---------+-------------+-----------------+-------------+-------------+| id_pers | nume | prenume | adresa | oras |+---------+-------------+-----------------+-------------+-------------+| 12 | Constantin | Petre | Buteanu 10 | Tirgu Mures || 127 | Ionescu | Madalina | Campeni 23 | Tirgu Mures || 10 | Craciunescu | Madalin Ciprian | Vlahuta 788 | Cimpina |+---------+-------------+-----------------+-------------+-------------+

+--------+------------+-------+---------+| id_com | data | numar | id_pers |+--------+------------+-------+---------+| 1 | 1999-10-23 | 12345 | 10 || 2 | 2010-12-30 | 22233 | 12 || 3 | 2012-04-21 | 10000 | 10 || 4 | 2012-01-23 | 99966 | 15 || 5 | 2011-01-23 | 22447 | 12 |+--------+------------+-------+---------+

SELECT persoane.nume, persoane.prenume, comenzi.numarFROM persoane INNER JOIN comenzi ON persoane.id_pers= comenzi.id_pers

+-------------+-----------------+-------+| nume | prenume | numar |+-------------+-----------------+-------+| Craciunescu | Madalin Ciprian | 12345 || Constantin | Petre | 22233 || Craciunescu | Madalin Ciprian | 10000 || Constantin | Petre | 22447 |+-------------+-----------------+-------+

Vedem că înregistarea cu id_pers 127 din tabelul persoane, respectiv înregistarea cu id_com 4 din tabelul comenzi nu apare în rezultat

SELECT persoane.nume, persoane.prenume, comenzi.numarFROM persoane LEFT JOIN comenzi ON persoane.id_pers= comenzi.id_pers

+-------------+-----------------+-------+| nume | prenume | numar |+-------------+-----------------+-------+| Constantin | Petre | 22233 || Constantin | Petre | 22447 || Ionescu | Madalina | NULL || Craciunescu | Madalin Ciprian | 12345 || Craciunescu | Madalin Ciprian | 10000 |+-------------+-----------------+-------+

SELECT persoane.nume, comenzi.numar, comenzi.dataFROM persoane RIGHT JOIN comenzi ON persoane.id_pers= comenzi.id_pers

+-------------+-------+------------+| nume | numar | data |+-------------+-------+------------+| Craciunescu | 12345 | 1999-10-23 || Constantin | 22233 | 2010-12-30 || Craciunescu | 10000 | 2012-04-21 || NULL | 99966 | 2012-01-23 || Constantin | 22447 | 2011-01-23 |+-------------+-------+------------+

!! mysql rezumat

Documents