notiuni avansate mysql - infoeducatie 2008

Notiuni avansate Mysql

Notiuni avansate MySQLMysqlTabeleIndecsiForeign key-uriTriggereVIEWSComenzi sql avansateJOIN-uriDESCRIBE

Notiuni avansate MySQL

Mihai Oaida <[email protected]> 01

MySQL3.23 - vechi4.1x - stabil, putine feature-uri5.0x - GA5.1 - RC6.0x - alfa



TabeleTipuri de tabele

MyISAMInnoDBMemory / HEAPMERGE, MRG_MyISAMBlack holeCSVARCHIVE



CREATE TABLE t( columns) ENGINE = sauALTER TABLE t ENGINE =

Indecsi• Ordonam tabelul dupa 1 sau n coloane• Tipuri de indecsi

PRIMARY KEYIndexUniqueFull Text

• Tipuri de algoritmi : BTREE si HASH• Aplicate pe 1 sau mai multe coloane (multiple index)



CREATE INDEX i_name ON table_name(col1,col2,..)

Full text indexCautare in texte



SELECT id,titlu FROM table WHERE

MATCH(titlu) AGAINST(‘xml’)

SELECT id,titlu FROM table WHERE

MATCH(titlu) AGAINST(‘xml’ WITH QUERY EXPANSION)

Foreign key

Ex : InnoDBSe poate pune la create tabele sau ulterior



FOREIGN KEY(cat_id) REFERENCES cat(id)

Creeaza legaturile intre tabele care la proiectarele creem prin conventie. Ex : news.cat_id – cat.id

TriggereObiect asociat unui tabel care se apeleaza la un

anumit eveniment



CREATE TRIGGER trig_name {BEFORE| AFTER} {INSERT,REPLACE,UPDATE, DELETE} on t

FOR EACH ROW BEGIN..sql..

END;

Exemplu

CREATE TRIGGER updateCateg AFTER INSERT ONproduseFOR EACH ROW BEGIN

UPDATE cats SET nr=nr+1 WHERE id=NEW.cat_id;

END;



VIEWSCREATE VIEW view_name AS [query]DROP VIEW view_name



CREATE VIEW stats_l AS SELECT l.id,l.nume, ( SELECT count( c.id ) FROM comentarii c WHERE c.lectie_id = l.id ) AS nr_comentarii , ( SELECT count( r.id ) FROM rezolvari r WHERE r.lectie_id = l.id ) AS nr_rezolvari FROM lectii l ORDER BY id ASC

SELECT * FROM stats_l

GROUP BY

SELECT cat_id,count(id) FROM posts GROUP BY id



Numarul de post-uri din fiecare categorie

HAVING

SELECT cat_id,count(id) FROM posts GROUP BY id HAVING count(id)<=100



Numarul de post-uri din fiecare categorie care au mai putin de 100 de post-uri

SubQuery-uriSELECT count(sum) FROM (SELECT nr AS sum FROM cats WHERE cat_id=3);



SELECT lectii.nume,(SELECT count(id) FROM

rezolvari WHERE lectii.id=rezolvari.lectie_id)

AS nr FROM lectii;

JOIN-uriJoin - produs cardinal intre 2 tabele

- conditie de join



SELECT camp1,camp2,.. FROM t1 [tip-join] t2 ON conditii [tip-join] t3ON conditii … [WHERE conditii]

Tipuri join

INNER JOIN - st, dr se pun daca respecta conditiaLEFT JOIN - st se pune, dr daca respecta conditiaRIGHT JOIN – dr se pune, st daca respecta conditia

Exemple – INNER JOINSELECT elevi.id,comentarii.comentariu FROM

elevi INNER JOIN comentarii ON elevi.id=comentarii.elev_id



SELECT elevi.id,comentarii.comentariu FROM

elevi , comentarii WHERE elevi.id=comentarii.elev_id

SELECT e.id,c.comentariu FROM

elevi e INNER JOIN comentarii c

ON e.id=c.elev_id

LEFT JOIN

Selecteaza toti elevii pe coloana din stanga sicomentarii daca au



SELECT e.username,c.comentariu FROM

elevi e LEFT JOIN comentarii c

ON e.id=c.elev_id

RIGHT Join

• Opusul lui left join• Selecteaza toate comentariile in coloana din

dreapta si pe coloana din stanga username-ul daca relatia este satisfacuta.

Daca nu se afiseaza NULL



SELECT e.username,c.comentariu FROM

elevi e LEFT JOIN comentarii c

ON e.id=c.elev_id

DESCRIBE• Arata pentru fiecare tabel din sql : indecsi

folositi,tipul cautarii, nr de randuri prin care cauta,etc

• DESCRIBE [sql]• Campuri

Select_typeTablePossible_keysKeyKey_lenRowsExtra



Referintehttp://dev.mysql.com/doc/refman/5.0/en/Mysql workbench



Intrebari?



notiuni avansate mysql - infoeducatie 2008

Technology