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 :
- 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.
- Sic e thame dhe me larte,InnoDB suporton transaksionet.
- 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.
- MyISAM ben lockim ne nivel tabele ndersa InnoDB arrin te beje dhe Lockim ne nivel rrjeshti.
- Thuhet se InnoDB rekuperohet me mire pas nje crash.
- 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. Normalizimi i shkalles 1
Qe nje tabele te quhet e normalizuar e shkalles se pare duhet qe te plotesohen keto kushte:
- Te mos kete rrjeshta te perseritura
- Cdo qelize te kete nje vlere te vetme
- Vlerat e nje kolone te jene te te njejtit tip
- 2. Normalizimi i shkalles 2
Nje tabele quhet e normalizuar e shkalles 2 ne momentin qe :
- Eshte e normalizuar e shkalles 1
- Te gjithe elementet jo celes te nje tabele te jene te varura vetem nga elementi celes i tabeles perkatese
- 3. Normalizimi i shkalles 3
Nje tabele quhet e normalizuar e shkalles 3 kur :
- Eshte e normalizuar e shkalles 2
- Nuk ekzistojne varesi transistive ne nje tabele
Me varesi transistive kuptojme : A zoteron B, B zoteron C keshtu qe dhe A zoteron C.
- 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.
- 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.
- 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
- Shpejtesi me e larte per shkak se ka tabela te vogla
- Updatimi I tabelave behet me shpejte
- Vendosja e te dhenave tek tabelat behet me shpejte
- Selektimi i te dhenave nqs kemi kerkese per te dhena brenda nje tabele
- Nuk ka duplikim te te dhenave
Normalizimi Kunder
- Lidhja e tabelave nuk shkon mire me strategjite e indeximit
- Vonon nese kerkohen te dhena te cilat gjenden ne tabela te ndryshme
- Query shume komplekse
Denormalizimi Pro
- Te dhenat ndodhen ne nje tabele dhe si rrjedhoje kerkimi shume me i shpejte
- Lejon strategjite e indeksimit
- Query shume te thjeshta
Denormalizimi Kundra
- Duplikimi I te dhenave
- 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.
- Vendosim nje trigger ne tabelen Products e cila do te updatoje product_name sa here updatohet tabela Products.
- 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 :
- Kufizojne psh puntoret te shohin te dhenat qe indexojne punen e tyre
- 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.