Tutorial i shpejte per MySql

C’eshte databaza, sistemet RDBMS.

Databaza eshte nje sistem i organizuar i te dhenave dixhitale. Te dhenat zakonisht organizohen ne modele te ndryshme gjithmone sipas nevojave, ne menyre qe te suportoje kerkesat qe behen per keto te dhena. Termi database lidhet ngushte me te dhenat dhe strukturen e tyre dhe jo me database management system (DBMS).

Ne nje term te pergjithshem sistemet DBMS, jane sisteme softwerike komplekse te cilat plotesojne shume kerkesa, dhe databazat qe ato mbajne jane te medha dhe komplekse.

Databaza relacionare eshte nje set tabelash te mire organizuara nga te cilat mund te terhiqen me lehtesi te dhenat.

 

Motoret e ruajtjes  MyIsam dhe InnoDB.

 

MyISAM

 

MyISAM eshte bazuar ne motorin me te vjeter te ruajtjes, ISAM, I cili nuk eshte me I perdorshem por qe ka disa shtesa shume te vlefshme.

Storage limits 256TB Transactions No Locking granularity Table
MVCC No Geospatial data type support Yes Geospatial indexing support Yes
B-tree indexes Yes Hash indexes No Full-text search indexes Yes
Clustered indexes No Data caches No Index caches Yes
Compressed data Yes[a] Encrypted data[b] Yes Cluster database support No
Replication support[c] Yes Foreign key support No Backup / point-in-time recovery[d] Yes
Query cache support Yes Update statistics for data dictionary Yes

Tab.1 Karakteristikat e motorit te ruajtjes MyIsam

 

Cdo tabele MyISAM ruhet ne disk e ndare ne tre file. Emrat e fileve fillojne me emrin e tabeles dhe kane nje prapashtese qe tregon tipin e file. Nje file .frm ruan formatin e tabeles. File I te dhenave “Data File” ka nje prapashtese .MYD (MyData). Filet e indexit kane nje prapashtese .MYI (MyIndex).

Ne mund ta percaktojme nqs duam te krijojme nje tabele per MyISAM me ane te komandes sql :

CREATE TABLE t (i INT) ENGINE = MYISAM;

Ne mund ti kontrollojme ose ti riparojme tabelat MyISAM duke perdorur komandat mysqlcheck ose myisamchk. Ne gjithashtu mund ti kompresojme keto tabela duke perdorur myisampack, gje e cila do te lironte shume vend ne databaze.

 

InnoDB

InnoDB eshte motori default I ruajtjes. Ai konsiderohet sin je safe store engine (kompilator ACID)  I cili eshte I afte te  mbaj commit, rollback dhe crash-recovery per ti ofruar perdoruesit ruajtjen e te dhenave. Innodb ofron nje nivel row-locking sipas stilit oracle, duke rritur keshtu konkurencen dhe  performancen e multiuserave.

InnoDb I ruan te dhenat e perdoruesve ne indekse te grupuara ne menyre qe te uli numrin e input – outputeve  per queryt e bazuara ne celesin primar. Per te ruajtur integritetin e te dhenav, InnoDB gjithashtu suporton dhe forign key. Ju mund mixoni tabelat e InnoDB me tabela nga motore kerkimi te tjere, madje dhe me te njejtin statement. Per te pare nese nje tabele perdor InnoDB ose jo perdoret komanda SHOW ENGINES.

 

Disa nga karakteristikat e InnoDB

 

Storage limits 64TB Transactions Yes Locking granularity Row
MVCC Yes Geospatial data type support Yes Geospatial indexing support No
B-tree indexes Yes Hash indexes No[a] Full-text search indexes No
Clustered indexes Yes Data caches Yes Index caches Yes
Compressed data Yes[b] Encrypted data[c] Yes Cluster database support No
Replication support[d] Yes Foreign key support Yes Backup / point-in-time recovery[e] Yes
Query cache support Yes Update statistics for data dictionary Yes

 

 

MyISAM vs InnoDB

Ndryshimi themelor ndermjet te dyjave eshte se InnoDB suporton transaksionet ndersa MyISAM jo.

Gjithsesi ka shume ndryshime te tjera, kryesoret e te cilave po I rendis me poshte :

  1. MyISAM eshte konsideruar me e shpejte ne kerkim, por gjithsesi me ndryshimet e fundit tek InnoDB dhe ky gap po zvogelohet dhe konkurenca po rritet.
  2. Sic e thame dhe me larte,InnoDB suporton transaksionet.
  3. InnoDB I menaxhon ndryshe indexet, duke ruajtur celesin primar sin je pjese te cdo indeksi, duke bere qe indexet te okupojne me teper memorje ne disk.
  4. MyISAM ben lockim ne nivel tabele ndersa InnoDB arrin te beje dhe Lockim ne nivel rrjeshti.
  5. Thuhet se InnoDB  rekuperohet me mire pas nje crash.
  6. Te dhenat ruhen ndryshe ne disk.

 

 

Skemat relacionare te dabazave, relacionet, entitetet.

 

Relacioni, mund te konsiderohet si nje fushe e perbashket identifikuese ndermjet dy ose me shume tabelave, e cila sherben per te nderlidhur keto tabela me njera tjetren.

Entitetet mund te konsiderohen si tabela qe kane vecori te perbashketa me tabelat e tjera por qe dhe mund te ekzistojne te vetme, prandaj  shikojme zakonisht lidhje nje me nje ndermjet entiteteve.

Ne skemat klasike relacionare, zakonisht entitetet paraqiten drejtkendesha ndersa relacionet me rombe.

 

Tipet e te dhenave.

 

Kur krijojme nje tabele ne databaze fillimisht duhet te shkruajm emrin e fushavfe te saj dhe me pas ti percaktojme nga nje tip seciles fushe. Percaktimi I tipit eshte shume I rendesishem pasi ajo percakton se cfar funksioni ka nje fushe ne tabele.

Kemi disa tipe te te dhenave ne nje tabele:

  • Tipet numerike
  • Tipet e string
  • Tipet Date and Time

Disa nga tipet numerike kryesore jane:

Small Int – Nje integer I vogel shtrirja e te cilit varion nga -32768 deri ne 32767

MediumInt – I cili eshte nje integer me madhesi te mesme. Shtrirja e tij varion nga              – 8388608 ne 8388608.

Int – Nje integer I madhesise normale shtrirja e te cilit varion nga – 2147483648 deri ne 2147483647

BigInt – I cili perdoret kur kemi per te ruajtur vlera te medha numerike. Shtrirja e tij  varion nga 9223372036854775808 ne  9223372036854775807.

Boolean  Perdoren nqs deshirojme te ruajm vetem nje vlere 1 ose 0. Nqs vlera eshte 0 atehere parametric I lidhur me kete vlere konsiderohet false ndersa ne qofte se eshte 1 parametri I lidhur me kete vlere konsiderohet true. Eshte sinonim i TinyInt

Decimal  Perdoret atehere kur kemi nje numer me presje fikse.

Float  Pak a shume e njejte me decimalin vetem se kemi nje presje te levizeshme.

Disa nga tipet kryesore te stringjeve jane:

Char – Perdoret atehere kur duam te ruajm nje string me gjatesi fikse.

Varchar – Perdoret atehere kur duam te ruajm nje string te cilit nuk ia dijme gjatesine.

Ndryshimi ndermjet Char dhe Varchar eshte se tek tipi char, ne momentin qe ne I percaktojme madhesine per nje string te caktuar, edhe nqs Stringu mund te kete madhesi me te vogel, ai do te zej po aq vend ne databaze sa I eshte percaktuar. Ndersa per tipin varchar ndodh e kunderta, pra pavarsisht se cfare madhesie I percaktojme ne ne fillim, vendi qe ai do te zere ne databaze do te jete I njejte me madhesine e tij.

Blob dhe Tekst – Blob eshte nje objekt I madh binary I cili mund te mbaje sasi te ndryshme te dhenash. Ai ndahet ne akter tipe te cilat jane : TINYBLOB, BLOB,MEDIUMBLOB, dhe LONGBLOB. Ato ndryshojne vetem nga gjatesia maksimale e vlerave qe mbajne. Ndersa kater llojet e tipit tekst jane TINYTEXT, TEXT, MEDIUMTEKST            dhe LONGTEKST. Ato korespondojne me kater tipet e BLOB dhe kane te njejten gjatesi maksimale si dhe te njejtat kersa ruajtjeje.

Ndryshimet ndermjet blob dhe tekst :

 

Vlerat e BLOB trajtohen si vargje binare (Vargje bitesh). Ato nuk permbajne karaktere, dhe renditja dhe krahasimet behen ne baze te  vlerave numerike qe permbajne kolonat e tyre. Ndersa persa I perket vlerave te tipit Tekst, ato trajtohen nuk trajtohen si vargje binare por si vargje karakteresh. Ato permbajne nje set karakteresh , renditen ne baze te krahasimit ndermjet karaktereve.

Ne qofte se strict sql mode nuk eshte aktivizuar dhe ne I japim nje vlere tipit blob ose tekst e cila kalon madhesine e kolones, vlera copetohet dhe nje paralajmerim paraqitet.

 

Tipet  kryesore tek Date and Time :

 

DATETIME –  Perdoret atehere kur duam te ruajm velrat per oren dhe daten. Eshte ne formatin “YYYY-MM-DD HH:MM:SS”. Shtrirja e saj eshte nga 1000-01-01 00:00:00 deri ne           9999-12-31 23:59:59.

TIMESTAMP –  Ka nje shtrirje nga ’1970-01-01 00:00:01′ UTC deri ne ’2038-01-09 03:14:07′ UTC.

Ndryshimet ndermjet TIMESTAMP DHE DATETIME

1. TIMESTAMP eshte 4 bytes perkundrejt  8 byteve te DATETIME.

2. TIMESTAMPzen me pak memorje ne databaze dhe si rrjedhim indexohet me shpejte.

3. DATETIME eshte konstante ndersa TIMESTAMP varet nga ora lokale.

 

Normalizimi i db, shkallet e normalizimit.

 

Normalizimi i databazave eshte procesi i organizimit te fushave dhe tabelave ne menyre qe te rritet eficenca, te rritet shpejtesia, te ulen perseritjet e te dhenave si dhe te ulen vartesite. Zakonisht normalizimi konsiston ne copezimin e tabelave te medha ne tabela me te vogla dhe te organizuara.

Procesi I normalizimit kalon ne disa hapa:

  1. 1.    Normalizimi i shkalles  1

Qe nje tabele te quhet e normalizuar e shkalles se pare duhet qe te plotesohen keto kushte:

  1. Te mos kete rrjeshta te perseritura
  2. Cdo qelize te kete nje vlere te vetme
  3. Vlerat e nje kolone te jene te te njejtit tip
  4. 2.    Normalizimi i shkalles 2

Nje tabele quhet e normalizuar e shkalles 2 ne momentin qe :

  1. Eshte e normalizuar e shkalles 1
  2. Te gjithe elementet jo celes te nje tabele te jene te varura vetem nga elementi celes i tabeles perkatese
  3. 3.    Normalizimi i shkalles 3

Nje tabele quhet e normalizuar e shkalles 3 kur :

  1. Eshte e normalizuar e shkalles 2
  2. Nuk ekzistojne varesi transistive ne nje tabele

Me varesi transistive kuptojme : A zoteron B, B zoteron C keshtu qe dhe A zoteron C.

  1. 4.    Normalizimi Boyce-Codd (BCNF)

Normalizimi Boyce-Codd konsiston ne faktin se cdo determinante duhet te jete nje candidate per celes. Pra qe nje tabele te jete e normalizuar sipas formes Boyce-Codd duhet te jete e normalizuar e shkalles 3 dhe cdo determinante e saj duhet te jete kandidat per celes.

  1. 5.    Normalizimi i formes se 4

Qe nje tabele te quhet e normalizuar e formes se 4 duhet qe te jete e normalizuar e formes Boyce-Cood dhe mos te kete varesi shume vlereshe.

  1. 6.    Normalizimi i formes se 5

Qe nje tabele te jete e normalizuar e formes se 5, gjithashtu e quajtur “forma normale e lidhjes se projektuar” duhet qe te jete e normalizuar e formes se 4 si dhe qe cdo vartesi e lidhjeve te vije si shkak I celesave kandidat.

 

Denormalizimi i db

 

Procesi I denormalizimit eshte pak a shume  kunderta e normalizimit. Duke normalizuar tabelat ne I ndajme ato ne tabela me te vogla duke rritur performacen dhe shpejtesine ne updatimin e te dhenave. Shtrohet pyetja: Po nqs kemi nje kerkese me te madhe per lexueshmeri tabelash dhe jo per updatim. Nqs do te perdornim tabelat e normalizuara ne kete rast do na duhej qe te preknim shum tabela per nje kerkim te thjeshte duke e bere queryn shume komplekse dhe si rrjedhoje te ngadalte. Per kete na sherben procesi I denormalizimit. Pra me pak fjale procesi I denormalizimit sherben per te optimizuar performancen dhe shpejtesine e kerkimit te te dhenave ne momentin qe kemi nje mbingarkese lexueshmerie nga ana e perdorueseve.

Normalizimi VS Denormalizimi

 

 Normalizimi Pro

  1. Shpejtesi me e larte per shkak se ka tabela te vogla
  2. Updatimi I tabelave behet me shpejte
  3. Vendosja e te dhenave tek tabelat behet me shpejte
  4. Selektimi i te dhenave nqs kemi kerkese per te dhena brenda nje tabele
  5. Nuk ka duplikim te te dhenave

Normalizimi Kunder

  1. Lidhja e tabelave nuk shkon mire me strategjite e indeximit
  2. Vonon nese kerkohen te dhena te cilat gjenden ne tabela te ndryshme
  3. Query shume komplekse

Denormalizimi Pro

  1. Te dhenat ndodhen ne nje tabele dhe si rrjedhoje kerkimi shume me i shpejte
  2. Lejon strategjite e indeksimit
  3. Query shume te thjeshta

Denormalizimi Kundra

  1. Duplikimi I te dhenave
  2. Updatimi dhe Vendosja e te dhenave ne tabele e ngadalte dhe komplekse

Shtrohet pyetja : Si do ta zgjidhim problemin e bashkjeteses se Normalizimit dhe Denormalizimit ne nje database ?

Marim rastin e dy tabelave : Products dhe Orders. Ne kete rast kemi fushen product_name e cila gjendet dhe tek tabela Product dhe tek tabela Orders. Ne moment qe hyn nje product I ri na duhet te updatojm tabelen Products por ne kete rast duhet te updatohet gjithashtu dhe tabela Orders. Zgjidhja eshte perdorimi i Triggers.

  1. Vendosim nje trigger ne tabelen Products e cila do te updatoje product_name sa here updatohet tabela Products.
  2. Ekzekutojme queryn e update ne tabelen Product. Do te shohim se eshte updatuar automatikisht dhe fusha product_name qe ndodhet ne tabelen Order

 

Aspekti praktik (MySQL)

Nje query e thjeshte per krijimin e tabelave

Create Table users(

Uid int not null auto_increment,

Emer varchar(30),

Mbiemer varchar(30),

Mosha int ,

Aid int,

Primary Key (uid),

Foreign key (Aid) references Address (Aid)

);

Create table Address (

Aid int not null auto_increment,

Shteti varchar (30) ,

Qyteti  varchar (30),

Kodi_Zip int not null,

Primary key (Aid)

);

Ne shembullin e mesiperm gjenden dy kode Sql te cilat perdoren per te kriuar tabelat ne database. Queryt e mesiperme bejne pjese tek kategoria e DDL (Data definition language) e cila perdoret per krijimin e tabelave, per ti bere drop atyre ose per ti modifikuar(Alter).

Verejme perdorimin e primary key, foreign key dhe auto_increment. Dy te parat jane komanda kyce ne krijimin e modeleve relacionare te databazave pasi sherbejne per te bere lidhjen e tyre. Primary key perdoret per te identifikuar nje fushe te tabeles si nje celes primar, fushat e te cilit duhet te jene unik dhe sherbejne per te identifikuar ne menyre te vecante cdo element te tabeles. Gjithashtu fushat qe jane celes primar te tabeles mund te perdoren dhe si foreign key tek tabelat e tjetra duke sherbyer keshtu per nderlidhjen ndermjet tabelave. Ne queryn e mesiperme kemi dy celesa primar Uid dhe Aid si dhe foreign key Aid qe sherben per te bere lidhjen ndermjet dy tabelave.

Ne kete query kemi gjithashtu dhe komanden Auto_increment e cila sherben per te inkrementar me nje fushen ne te cilen eshte implementuar sa here qe shtohet nje element I ri ne tabele.

INSERT INTO `address` (`Aid`, `Shteti`, `Qyteti`, `Kodi_Zip`) VALUES ('Shqiperi', 'Durres', '2001');
INSERT INTO `users` ( `Emer`, `Mbiemer`, `Mosha`, `Aid`) VALUES ( 'Krisel', 'Tola', '22', '2');

 

Dy queryt e mesiperme sherbejne per te shtuar te dhena ne tabele. Ato hyjne tek kategoria DML (Data Manipulation Language). DML perfshin komandat Insert, Update dhe Delete.

Select * from Users where mosha = 22 ;

Query e mesiperme do ten a selektoje te gjithe ato persona ne tabelen users te cilet kane moshen 22 vjec. Kjo eshte nje tjeter query e thjeshte dhe hyn ke kategoria e DQL (Data Query Language) e cila perfshin  komandat Select, Show dhe Help por me kryesorja nga keto eshte komanda Select pasi ajo perdoret me shpesh nga te gjitha.

Querite me komplekse

 

Disa query me funksione Agregate

Pasi kemi shtuar dis ate dhena te tjera ne tabelat e mesiperme duam te gjejme moshen mesatare te
perdorueseve tane.

Select Avg(mosha) as mosha_mesatare from users

Kjo query do ten a nxjerre moshen mesatare te te gjithe perdoruesve tane.

Per tabelat e mesiperme bejme nje query kerkimi pak me te komplikuar.

Na duhet te selektojme te gjith perdoruesit qe jane nga Tirana

Select * from users where Aid in (select Aid from address where qyteti = 'Tirane');

Krijojme nje tabele te re per blerjet qe kan bere keto tre perdorues

Create table Orders(

Order_Id int  NOT NULL  Primary Key Auto_Increment,

Order_Date Date,

Order_Price int,

Uid int,

Foreign Key (Uid) references users(Uid)

)

E mbushim kete tabele me te dhena. Kerkesa eshte qe te shfaqim totalin e blerjeve per secilin klient.

SELECT users.Emer, users.Mbiemer, SUM( Order_Price ) as Shuma_Totale

FROM orders

INNER JOIN users ON orders.uId = users.uId

GROUP BY orders.uid ;

Ndryshimi ndermjet Join , Left Join dhe Outer Join eshte tek menyra se si I shfaqin te dhenat. Join shfaq ato te dhena nga dy ose me shume tabela, qe kane te pakten nje lidhje me njera tjetren.  Left Join shfaq te gjitha te dhenat nga tabela e pare pa pasur nevoj te kete se sben nje lidhje. Right join shfaq te gjitha te dhenat nga tabela e dyte pa pasur nevoj te kete lidhje me tabelen e pare. Ndersa outter join I shfaq te gjitha te dhenat nga te dyja tabelat pa pasur nevoj te kene lidhje me njera tjetren.

Persa I perket query me Union perdoret per te bashkuar te dhenat e dy tabelave ne nje tabele te vetme. Me kete komand duhet pasur kujdes pasi nqs k ate dhena te njejta ne te dyja tabelat e liston vetem nje here ate. Per te bashkuar dhe shfaqur te gjitha te dhenat duhet query Union All.

Procedurat dhe funksionet

 

Perkufizimi i Stored Procedure

 

Nje proçedure e ruajtur quhet nje segment kodi Sql, I cili ruhet ne database. Kjo procedure mund te thirret nga programuesi, nga nje trigger ose nga nje procedure tjeter. Nje procedure e cila therret vetveten quhet nje procedure rekursive. Gati te gjitha RDBMS e suportojne mjaft mire procedure recursive pervec MYSQL.

Avantazhet

  • Procedurat e ruajtura rrisin performance e aplikacioneve. Pasi krijohet, procedura ruhet ne katalogun e databazes. Ajo ekzekutohet me shpejte se queryt e thjeshta qe I dergohen databazes nga aplikacioni.
  • Procedurat ulin ndjeshem trafikun ndermjet Databazes dhe Aplikacionit sepse ne vend qe te dergoj rrjeshta kodi te gjate dhe te pa kompiluar, Aplikacionit I duhet vetem qe te dergoj emrin e procedures dhe te marre mbrapsht te dhenat.
  • Procedurat mund te perdoren ne me shume se nje aplikacion. Ato jane transparente dhe shfaqin nderfaqen e databazes, keshtu qe programuesit nuk do i duhet te rishkruaj funksione qe jane te gateshme.
  • Procedurat jane te sigurta. Kjo ndodh pasi administrator mund ti jap akses te drejtperdrejte nje aplikacioni vetem ne procedure dhe ne asnje tabele tjeter te asaj database.

Pas ketyre avantazheve kemi dhe disa disavantazhe:

Disavantazhet

  • Procedurat e ngarkojne shume si memorjen ashtu dhe procesoret e databazes/
  • Procedurat e ruajtura permbajne vetem sql deklarative keshtu qe eshte shume e veshtire per te shkruajtur procedura me komplekse sic mund te shkruhen ne Java ose C#.
  • Eshte shume e veshtire ti besh Debug procedurave dhe ky funksion nuk ofrohet ne te gjithe RDBMS.
  • Nuk eshte e lehte ti shkruash dhe ti mirembash.

Si cdo gje tjeter dhe procedurat kane Avantazhet dhe Disavantazhet. Pra perpara se te fillohet me zhvillimin e nje aplikacioni duhet bere nje balancim per te vendosur nese te perdoren ose jo procedurat.

 

Tipet e te dhenave ne stored procedure:

 

Tipi I te dhenave Pershkrimi
BigInt Nje Integer 64 bit –sh I pa shenjuar. Ky tip te dhene ka nje shtrirje nga-9,223,372,036,854,775,808 deri ne 9,223,372,036,854,775,807.
Boolean Nje vlere booleane, true ose false
Char Nje tip te dhene qe permban nje varg me stringje.
Currency Nje vlere kembimi qe varion nga -922,337,203,685,477.5808 deri ne 922,337,203,685,477.5807 me nje saktesi deri ne 10 mije monedha.
Date Perdoret per te treguar datren. Ruhet sin je vlere Double.
DBDate Shfaq daten ne formatin  yyyymmdd.
DBTime Shfaq oren ne formatin hhmmss.
DBTimeStamp Shfaq oren dhe daten ne formatin yyyymmddhhmmss.
Decimal Nje vlere me precision fiks dhe shkalle numerike ndermjet -10^38 -1 dhe 10^38 -1.
Empty Pa vlere.
FileTime Nje integer 64 bit I cili prezanton nje numer me interval 100 nano sekonda qe nga 1 Janar 1601.
GUID Nje Identifikues unik global ose “GUID”, nje Integer 128-bit (16 bytes) , I cili mund te perdoret ndermjet te gjithe kompjuterave dhe rrjetave kudo qe nje identifikues unik kerkuhet. Nje identifikues I tille ka shume pak mundesi te dyfishohet.
Integer Nje Integer 32 bit,I cili ka nje shtrirje nga-2,147,483,648 deri ne  2,147,483,647.
LongVarBinary Nje vlere e gjate binare,e cila mban nje vector te gjate integerash me nje shtrirje nga 0 deri ne 255.
LongVarChar Nje vlere e gjate stringu qe nuk mbaron.
LongVarWChar Nje vlere e gjate stringu UNICODE qe nuk mbaron.
Numeric Nje vlere ekzakte numerike me shkalle shtrirje ndermjet -10^38 -1 dhe 10^38 -1.
Single Nje numer nme presje me nje shtrirje  nga -3.40E +38 deri ne  3.40E +38.
SmallInt Nje Integer 16 bit I nenshkruar I cili permban vlera duke filluar nga -32768 deri tek 32767.
TinyInt Nje integer 8 bit I nenshkruar I cili ka nje shtrirje vlerash nga – 128 deri ne 127.
UnsignedBigInt Nje Integer 64 bit I pa nenshkruar I cili ka nje shtrirje vlerash nga 0 deri ne 18,446,744,073,709,551,615.
UnsignedInt Nje Integer 32 bit I pa nenshkruar I cili ka nje shtrirje vlerash nga 0 deri ne 4,294,967,295.
UnsignedSmallInt Nje Integer 16 bit I pa nenshkruar I cili ka nje shtrirje vlerash nga 0 deri ne 65535.
UnsignedTinyInt Nje Integer 8 bit I pa nenshkruar I cili ka nje shtrirje vlerash nga 0 deri ne 255.
UserDefined Lejon perdoruesin te shtoj tipin scalar te serverit te databazes. UDT –te mund te mbajne shume element te cilet mund te kene sjellje te ndryshme, duke I diferencuar ato nga aliaset e te dhenave tradicionale te cilet mund te kishin vetem nje tip te sistemit. Jane te perdoreshme per krijimin e dative, kohes, currency, tippet numerike te shtuara ose per te punuar me te dhene te enkriptuarar.
VarBinary Perdoret per te krijuar nje array bitesh.
VarChar Variabel qe permban nje numer te pa fundem karakteresh jo Unicode
Variant Nje tip special te shenash qe mund te permbaj vlera numerike, string, binare ose daten si dhe disa vlera special si Empty ose Null
VarNumeric Nje variabel e gjate e cila permban vlera numerike qe shtrihen nga -10^38 -1 dhe 10^ 38 -1.
VarWChar Nje variabel e gjate, e pa fundme vlerash karakteresh unikode.
WChar Nje varg karakteresh Unicode

 

Krijimi i nje stored procedure

DELIMITER //

CREATE PROCEDURE ShowAllUsers()

BEGIN

SELECT *  FROM users;

END //

DELIMITER ;

 

Fillimisht shohim komanden Delimiter, e cila na sherben per te ndryshuar ndaresin standart. Pre delimiter nga ; qe eshte kalon ne //.

Fillimi I nje procedure behet me ane te komandes Create Procedure emir_procedures(). I gjithe kodi ndermjet Begin dhe End do jete ajo cfare ne te vertete procedura do kryej. Ne rastin e mesiperm, procedura e krijuiar do na shfaq te gjithe elementet ne tabelen users qe kemi krijuar me pare.

Thirrja e stored procedure do te behet ne kete menyre:

Call ShowAllUsers();

Deklarimi i variablave

Variablat perdoren ne Stored Procedures per te ruajtur rezultatin e nxjerre nga procedura. Deklarimi I tyre behet keshtu :

DECLARE emri_variables tipi(madhesia) DEFAULT vlera_default;

DECLARE numri_perdorueseve int(11) DEFAULT 0;

 

Variables mund ti vendosim nje vlere duke perdorur komanden SET

DECLARE numri_userave int(11) DEFAULT 0;
SET numri_userave = 3;

Ose mund tja japim asaj me ante te nje procedure

DELIMITER //

CREATE PROCEDURE CountAllUsers()

BEGIN

SELECT COUNT(*) INTO numri_userave FROM users;

END //

DELIMITER ;

 

Cdo variable ka qellimin e saj. Nqs nje variable e deklarojme Brenda nje procedure puna e saj mbaron bashke me endin e procedures.

Variablat qe fillojne me @ perpara quhen variabla sesioni.

Kursoret

 

Kursoret perdoren per te kaluar mes rrjeshtave, te cilet kthehen nga nje kerkese (query), dhe te procesoje nje rrjesht individual. Krusoret kane vecorite e meposhteme:

  • Read Only – Kjo do te thote qe nuk mund  ta updatojme nje krusor.
  • Mund te ece vetem ne nje drejtim, nuk mund te anashkaloje ose te shkoje mbrapsht.
  • Asensitiv – Ju duhet te evitoni updatimin e tabelave nderkohe qe kemi nje krusor te hapur pasi mund te nxjerre rezultate te papritura.

 

Deklarimi I krusoreve behet ne kete menyre :

DECLARE emir_krusorit FOR kerkesa_select ;

 

Perpara se te filloje kapja e rrjeshtave duhet hapur krusori :

OPEN emri_krusorit ;

 

Me pas  themi krusorit te kap nje rrjesht dhe te kaloj tek rrjeshti tjeter I kapur.

FETCH emri_krusorit            INTO    lista_variablave ;

 

Dhe me ne fund e mbyllim krusorin me ane te komandes Close

CLOSE emri_krusorit;

Nje pike tjeter e rendesioshme eshte perdorimi i NOT FOUND HANDLER per te shmangur nje gjendje fatale”no data to fetch”

 

Me posht kemi nje shembul mbi perdorimin e krusorit :

DELIMITER $$

DROP PROCEDURE IF EXISTS CursorProc$$

CREATE PROCEDURE CursorProc()

BEGIN

DECLARE  no_more_products, quantity_in_stock INT DEFAULT 0;

DECLARE  prd_code VARCHAR(255);

DECLARE  cur_product CURSOR FOR

SELECT  productCode FROM products;

DECLARE  CONTINUE HANDLER FOR NOT FOUND

SET  no_more_products = 1;

 

/* for  loggging information */

CREATE  TABLE infologs (

Id int(11) NOT NULL AUTO_INCREMENT,

Msg varchar(255) NOT NULL,

PRIMARY KEY (Id)

);

OPEN  cur_product;

 

FETCH  cur_product INTO prd_code;

REPEAT

SELECT  quantityInStock INTO quantity_in_stock

FROM  products

WHERE  productCode = prd_code;

 

IF  quantity_in_stock < 100 THEN

INSERT  INTO infologs(msg)

VALUES  (prd_code);

END  IF;

FETCH  cur_product INTO prd_code;

UNTIL  no_more_products = 1

END REPEAT;

CLOSE  cur_product;

SELECT *  FROM infologs;

DROP TABLE  infologs;

END$$

DELIMITER;

 

Strukturat e kontrollit ne Stored Procedures ne Mysql

 

While Loop

DELIMITER $$

DROP PROCEDURE IF EXISTS WhileLoopProc$$

CREATE PROCEDURE WhileLoopProc()

BEGIN

DECLARE x  INT;

DECLARE str  VARCHAR(255);

SET x = 1;

SET str =  '';

WHILE x  <= 5 DO

SET  str = CONCAT(str,x,',');

SET  x = x + 1;

END WHILE;

SELECT str;

END$$

DELIMITER ;

 

Sic e shikojme loop while ben pjese tek strukturat e kontrollit per stored procedures. Po ta verejme me kujdes gjeja e pare qe ben ky cikel eshte kontrolli I kushtit. Per sa kohe qe kushti eshte true ai vazhdon e ekzekutohet. Ne rastin e mesiperm kushti eshte true deri ne momentin qe x shkon me e madhe se 5. Ne ate moment kushti shendrohet ne false dhe cikli mbaron. Problemi me I zakonshem qe haset ne keto raste eshte rasti kur nuk inicializohet x. Ne kete rast vlera e x qendron gjithmone null dhe cikli perseritet ne pafundesi deri kur te ndodh nje stack overflow dhe serveri te bej crash.

 

Repeat loop

 

Ne ndryshim nga Loop –I While ketu fillimisht ekzekutohet kerkesa dhe me pas shihet kushti.

 

DELIMITER $$

DROP PROCEDURE IF EXISTS RepeatLoopProc$$

CREATE PROCEDURE RepeatLoopProc()

BEGIN

DECLARE x  INT;

DECLARE str  VARCHAR(255);

SET x = 1;

SET str =  '';

REPEAT

SET  str = CONCAT(str,x,',');

SET  x = x + 1;

UNTIL x  > 5

END REPEAT;

SELECT str;

END$$

DELIMITER ;

 

 

LOOP loop

 

DELIMITER $$

DROP PROCEDURE IF EXISTS LOOPLoopProc$$

CREATE PROCEDURE LOOPLoopProc()

BEGIN

DECLARE x  INT;

DECLARE str  VARCHAR(255);

SET x = 1;

SET str =  '';

loop_label:  LOOP

IF  x > 10 THEN

LEAVE  loop_label;

END  IF;

SET  x = x + 1;

IF  (x mod 2) THEN

ITERATE  loop_label;

ELSE

SET  str = CONCAT(str,x,',');

END  IF;

END LOOP;

SELECT str;

END$$

DELIMITER ;

Ne kete cikel shikojme komanden Loop dhe Leave. Nqs kushti I vendosur plotesohet atehere hyn ne pune komanda leave e cila ben nxjerrjen nga cikli. Verejme qe kemi dhe komanden Iterate. Ne kete rast kushti ne te cilen perdoret Iterate thote qe nqs numri eshte cift atehere injoroje dhe kthehu larte, ne te kundert numri cikli vazhdon pas pjeses else.

 

Prepared Statements

 

Prepared statements bazohet ne 3 statement kryesore :

 

  • Prepare
  • Execute
  • Deallocate Prepare

 

E para pergatit statementin per exekutim. E dyta e ekzekuton ndersa e treat e fshin statementin e krijuar me pare.

PREPARE pstatus from “select * from users where uid = ?” ;

SET @a := “4”;

EXECUTE pstatus Using @a;

 

Pra te shpjegojme kodin e mesiperm.

Fillimisht pergatitet statement pstatus. Ne te vendoset nje query e cila kerkon te selektoje te gjitha te dhenat nga tabela users ku uid e ka vleren te shenuar me ?. Pikpyetja  eshte nje variable te ciles ne ja japim vleren me poshte. Set @a := “4” I jep pra vleren varialbles @a.

Me poshte ekzekutohet statement i krijuar duke perdorur  komanden Execute e cila therret variablen e mesiperme @a.

Tabelat e perkohshme

 

Tabelat e perkoheshme mund te na vijne shpoesh ne ndihme. Gjeja me kryesore qe duhet mbajtur mend ne lidhje me to eshte se kto tabela jane te perkoheshme, pra do te thote qe ne momentin qe mbyllet sesioni I klientit te lidhur me databazen dhe keto tabela fshihen. Psh nqs keto tabela I krijojme me ane ten je kodi php, tabelat fshihen ne momentin qe kodi mbaron ekzekutimin.

 

Shembull :

mysql> CREATE TEMPORARY TABLE SalesSummary (

-> product_name VARCHAR(50) NOT NULL

-> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00

-> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00

-> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0

);

Query OK, 0 rows affected (0.00 sec)

 

mysql> INSERT INTO SalesSummary

-> (product_name, total_sales, avg_unit_price, total_units_sold)

-> VALUES

-> ('cucumber', 100.25, 90, 2);

 

mysql> SELECT * FROM SalesSummary;

 

product_name total_sales avg_unit_price total_units_sold
cucumber 100.25 90.00 2

 

1 row in set (0.00 sec)

Ne momentin qe ne shkruajm komanden sql Show tables kjo tabele e sapo krijuar nuk shfaqet ne liste. Ndersa persa I perket fshirjes, pavarsisht e mysql I fshin vet tabelat kur mbaron lidhja, ato mund ti fshijme dhe me komanden Drop Table.

 

 

Krijimi i view-ve

Views perdoren per disa arsye :

  1. Kufizojne psh puntoret te shohin te dhenat qe indexojne punen  e tyre
  2. Krijimin e join per kolona te shume tabelave.

Krijimi I nje view

CREATE VIEW nameview

AS

SELECT Emer from users

Shikojme ate qe kemi krijuar

Select * from nameview

-Materialized Views

Nje Materialized View eshte nje rezultat i parallogaritur i nje query –je. Ne ndryshim nga View e thjeshte, rezultati i MV ruhet diku, zakonisht ne nje tabele. MV perdoren zakonisht kur duhet nje pergjigje e menjehereshme dhe kur kerkesa (query) ku Materialized View bazohet do shume kohe qe te nxjerr nje rezultat. MV duhet te rifreskohen here pas here. Rifreskimi varet nga kerkesat, pra sa I aktualizuar duhet te jete kodi i tij. Teorikisht nje MV mund te rifreskohet menjehere ose pas nje kohe te caktuar. MySql nuk e ofron vete kete funksion per eshte e thjeshte te implementojme nje te tille vete.

-Implementimi I MV

 

Po paraqesim nje shembull se si mund te implementohet nje Materialized View.

SELECT COUNT(*)

FROM MyISAM_table;

 

Query e mesiperme na kthen nje rezultat te menjehershem pasi numeruesi ndodhet ne tabelen kryesore. Por kerkesa e mesiperme kerkon disa sekonda deri ne minuta per tu ekzekutuar.

 

Nje zgjidhje per kete do te ishte te krijonim nje tabele InnoDB ku te gjithe numerimet e rrjeshtave te ruhenin aty.

CREATE TABLE innodb_row_count (

id          INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY

, schema_name VARCHAR(64)  NOT NULL

, table_name  VARCHAR(64)  NOT NULL

, row_count   INT UNSIGNED NOT NULL

);

Ne varesi te kerkesave per aktualitet tabela e mesiperme mund te rifresohej nje here ne dite (nxjerr shume errore ne perfundim), nje here ne ore ose pas cdo ndryshimi (shume e avashte).

-Rifreskimi i MV

Materialized views mund te rifreskohen ne menyra te ndryshme :

  • Asnjehere (Vetem ne fillim, kur te dhenat duhen statike)
  • Sipas kerkeses (si pershembull nje here ne dite, pas mesnate)
  • Menjehere (pas cdo ndryshimi)

Nje rifreskim mund te behet sipas menyrave te meposhteme :

  • I gjithi (shume i ngadalte, nga fillimi)
  • Nje pjese e percaktuar (E shpejte, nga nje tabele log)

 

-Krijimi I nje MV I cili e rifreskon kodin pas çdo ndryshimi

Per ta kuptuar me mire nje Materialized View duhet te “bejme duart pis”. Pra te fillojme te kodojme.

Krijojme nje tabele te quajtur sales, I vendosim te dhena asaj dhe me pas I shfaqim ato

CREATE TABLE sales (

sales_id       INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY

, product_name   VARCHAR(128) NOT NULL

, product_price  DECIMAL(8,2) NOT NULL

, product_amount SMALLINT     NOT NULL

);

 

INSERT INTO sales VALUES

(NULL, 'Apple', 1.25, 1), (NULL, 'Apple', 2.40, 2)

, (NULL, 'Apple', 4.05, 3), (NULL, 'Pear', 6.30, 2)

, (NULL, 'Pear', 12.20, 4), (NULL, 'Plum', 4.85, 3)

;

SELECT * FROM sales;

 

Me pas fillojme me krijimin e MV tone.

DROP TABLE sales_mv;

CREATE TABLE sales_mv (product_name VARCHAR(128)  NOT NULL, price_sum    DECIMAL(10,2) NOT NULL, amount_sum   INT  NOT NULL, price_avg    FLOAT         NOT NULL, amount_avg   FLOAT   NOT NULL, sales_cnt INT          NOT NULL

,UNIQUE INDEX product (product_name)

);

INSERT INTO sales_mv

SELECT product_name, SUM(product_price), SUM(product_amount), AVG(product_price), AVG(product_amount), COUNT(*) FROM sales

GROUP BY product_name;

 

Tani do te krijojme proçedurat te cilat do ten a mundesojne rifreskimin e MV sipas deshires.

Rifreskimi pas cdo ndryshimi :

DELIMITER $$

CREATE PROCEDURE refresh_mv_now (

OUT rc INT

)

BEGIN

TRUNCATE TABLE sales_mv;

INSERT INTO sales_mv

SELECT product_name , SUM(product_price), SUM(product_amount),         AVG(product_price), AVG(product_amount), COUNT(*)  FROM sales GROUP BY product_name;

 

SET rc = 0;

END;

$$

 

DELIMITER ;

Vendosim te dhenat te tabelen sales dhe shohim nese do ten a updatohet tabela sales_mv me ane te thirrjes se procedures se mesiperme.

INSERT INTO sales VALUES

(NULL, 'Apple', 2.25, 3), (NULL, 'Plum', 3.35, 1)

, (NULL, 'Pear', 1.80, 2);

CALL refresh_mv_now(@rc);

SELECT * FROM sales_mv;
product_name price_sum amount_sum price_avg amount_avg sales_cnt
Apple 9.95 9 2.4875 2.25 4
Pear 20.30 8 6.76667 2.66667 3
Plum 8.20 4 4.1 2 3

 

Perfundime

 

  • Trigers ne MySql nuk jane shume te shpejte
  • MV ndihmojne ne pershpejtimin e disa kerkesave te cilat mund te ngarkojne databazat mbi te cilat ekzekutohen.
  • Nqs shpejtesia e vendosjes se te dhenave te reja ne database nuk eshte ceshtje primare, perdorimi i MV ul ndjeshem kohen e thirrjes se te dhenave per lexim.
  • Do pak pune per tu implementuar.
  • MV duhet te perdoret vetem atehere kur databazat kane shume te dhena.

Leave a Reply

Your email address will not be published. Required fields are marked *