Osnove MySQL

V tem poglavju se bomo spoznali z uporabo podatkovnih baz, ki nam omogočajo shranjevanje podatkov ter tudi zelo učinkovito iskanje po shranjenih podatkih.

Ko govorimo o podatkovnih bazah, ki komunicirajo s PHP programsko kodo, ne moremo mimo izredno priljubljene opcije: MySQL.

Kaj je MySQL

MySQL je odprtokodna različica podatkovne baze, ki deluje z jezikom SQL (Structured Query Language). Običajno imamo na strežniku poleg spletnega strežnika postavljen tudi MySQL strežnik, do katerega dostopamo z odjemalcem (ang. client).

Vsak MySQL strežnik lahko vsebuje poljubno število podatkovnih baz. Vsaka podatkovna baza ima svoje ime. Z določenim uporabniškim imenom in geslom lahko dostopamo do tistih podatkovnih baz, za katere imamo ustrezne pravice. Uporabnik root (administrator z vsemi pravicami) pa lahko dostopa do vseh baz.

V vsaki podatkovni bazi imamo lahko poljubno število tabel. Vsaka tabela ima prav tako svoje ime. Tabele so enote, ki vsebujejo podatke. Obliko tabele določi uporabnik. Uporabnik prav tako vnaša, spreminja ter briše podatke v tabeli. Vsaka tabela vsebuje poljubno število stolpcev, vsak stolpec pa ima določeno ime in podatkovni tip. Ko v tabelo vstavljamo nove podatke, jih vstavljamo v obliki nove vrstice.

Primer tabele zaposleni v podatkovni bazi:

ime (VARCHAR)	priimek (VARCHAR)	poklic (VARCHAR)	urna postavka (DECIMAL)
Janez	Novak	programer	60.00
Nina	Žnidaršič	oblikovalec	35.00
Vasja	Veselko	preizkuševalec	16.50

Zgornja tabela zaposleni ima 4 stolpce (vsak stolpec ima svoje ime ter podatkovni tip) in 3 vrstice (podatke o treh zaposlenih).

Podatkovni tipi

Podobno kot PHP ima tudi MySQL različne podatkovne tipe. Glede na izbrani podatkovni tip se spremenijo tudi naše možnosti za delo s podatki. Naštejmo nekaj najbolj pogostih podatkovnih tipov:

• INT — uporablja se za cela števila
• DECIMAL — uporablja se za decimalna števila
• VARCHAR — uporablja se za krajše nize (npr. iz <input type="text">)
• TEXT — uporablja se za daljše besedilo (npr. iz <textarea>)
• DATETIME — uporablja se za shranjevanje točnega datuma in ure
• ENUM — seznam dovoljenih vrednosti (če v podatkovno bazo poskusimo zapisati vrednost, ki je ni na seznamu dovoljenih vrednosti, se bo zaradi varnosti v bazo shranila prazna vrednost)

Pri nekaterih podatkovnih tipih moramo določiti tudi njihovo največjo dovoljeno dolžino. Za VARCHAR je to tipično 255 znakov, za INT pa 6.

Primarni ključ

Običajno imamo v vsaki tabeli nek stolpec z imenom id (podatkovnega tipa INT), ki nam služi kot primarni ključ. Na ta način ima vsaka vrstica v tabeli svojo zaporedno številko, s katero je enolično določena.

Samodejno štetje vrstic si zagotovimo z lastnostjo auto_increment, ki jo dodelimo primarnemu ključu id.

Indeksi

Poleg primarnega ključa uporabljamo še nekaj drugih vrst indeksov — to so stolpci, ki nam olajšajo in zelo pohitrijo iskanje podatkov v tabeli. Običajno uporabljamo tri tipe indeksov:

• INDEX — z njim označimo stolpec, ki ga pogosto uporabljamo pri iskanju in ga želimo najučinkoviteje shraniti
• UNIQUE — podobno kot INDEX, le da se vrednosti v takem stolpcu ne smejo podvajati
  Primer: Če imamo zelo veliko tabelo in iščemo nek podatek, ki se nahaja v stolpcu tipa UNIQUE, bo MySQL strežnik vedel, da obstaja samo en tak podatek in ne bo po nepotrebnem preiskal cele tabele.¹
• FULLTEXT — z njim označimo stolpec, kjer shranjujemo daljše besedilo, po katerem lahko kasneje učinkoviteje iščemo

Delo s podatki

Največkrat želimo podatke v tabele vstavljati, spreminjati in brisati. V MySQL jeziku opišemo, katere podatke želimo iz podatkovne baze, nato pa jih obdelamo z ustreznimi funkcijami v PHP.

Primer tabele z imenom izdelki s tremi stolpci za podatke in enim za primarni ključ:

ime (VARCHAR)	cena (DECIMAL)	zaloga (INT)	id (INT)
Tipkovnica	89.99	40	1
Monitor	349.99	15	2
Tiskalnik	149.99	20	3

Branje podatkov

Če želimo iz tabele prebrati neke podatke (npr. vrstice), uporabimo SQL stavek SELECT. S stavkom SELECT iz tabele takole preberemo vse vrstice z vrednostmi iz vseh stolpcev:

SELECT * FROM izdelki;

Namig: Znak * v SQL stavku lahko v mislih preberemo kot "everything".


Če želimo iz tabele izbrati samo stolpca ime in cena, lahko namesto zvezdice * zapišemo imena posameznih stolpcev, ločena z vejico:

SELECT ime, cena FROM izdelki;

Kaj pa, če želimo iz tabele izbrati samo izdelke, ki so dražji od 120 € (monitor in tiskalnik)? V takem primeru uporabimo ukaz WHERE, s katerim lahko določimo pogoj.

SELECT * FROM izdelki WHERE cena > 120;

Takole pa izberemo vse vrstice, kjer je podatek v stolpcu ime enak nizu "Tipkovnica":

SELECT * FROM izdelki WHERE ime = 'Tipkovnica';

V našem primeru gre za eno vrstico (en izdelek), kjer je ta pogoj izpolnjen.

Opomba: Pri primerjanju v jeziku SQL uporabljamo enojni enačaj =. Za primerjanje v jeziku PHP pa uporabljamo dvojni enačaj ==.


Če želimo izbrati vse vrstice, ki se začnejo z nizom "Ti", namesto enačaja uporabimo ukaz LIKE in znak % (wildcard):

SELECT * FROM izdelki WHERE ime LIKE 'Ti%';

Več pogojev lahko združimo na enak način kot v jeziku PHP — z uporabo AND in OR.

Primer, kjer iz tabele izdelki izberemo samo vrstice, kjer je cena večja ali enaka 150 in zaloga večja ali enaka 10:

SELECT * FROM izdelki WHERE cena >= 150 AND zaloga >= 10;

Vrstni red vrnjenih podatkov

Vrstni red vrstic, ki nam jih ob poizvedbi SELECT vrne MySQL strežnik, določimo tako, da na koncu SQL stavka dodamo ukaz ORDER BY stolpec ASC / DESC:

SELECT * FROM izdelki ORDER BY cena ASC;

Tako bodo izdelki izpisani v naraščajočem vrstnem redu od tistega z najnižjo ceno pa do tistega z najvišjo. Če želimo obraten vrstni red izpisa, namesto ASC (ang. ascending, naraščajoče) uporabimo DESC (ang. descending, padajoče).

Omejimo lahko tudi število vrnjenih vrstic. To storimo z ukazom LIMIT, ki mu sledi številka vrstice, pri kateri se branje prične (štetje se začne pri 0), nato vejica in na koncu še število vrstic, ki jih želimo prebrati.

Če v poizvedbi že uporabljamo ukaz ORDER BY, ukaz LIMIT postavimo za njim:

SELECT * FROM izdelki ORDER BY cena ASC LIMIT 0, 2;

Zgornji stavek bo iz tabele izbral samo tipkovnico in tiskalnik (dva najcenejša izdelka), v tem vrstnem redu.

Vstavljanje podatkov

V tabelo lahko, če imamo ustrezne pravice, vstavimo novo vrstico (ang. row). To naredimo s stavkom INSERT.

Primer vstavljanja izdelka z imenom "Miška, ki stane 39.99 € in jih imamo na zalogi 35:

INSERT INTO izdelki (ime, cena, zaloga) VALUES ('Miška', 39.99, 35);

V prvem delu (med prvim parom oklepajev) navedemo vrstni red stolpcev, ki jim bomo v enakem vrstnem redu nato priredili vrednosti.² Pri tem ni potrebno, da vstavimo podatke v vse stolpce. V našem primeru smo izpustili stolpec id, ker se njegova vrednost za vsako vrstico določi samodejno preko auto_increment števca.


MySQL ima tudi velik nabor vgrajenih funkcij, ki nam olajšajo delo.

Primer: Če želimo v tabelo novice shraniti naslov novice, besedilo novice ter cas dodajanja novice, lahko trenutni čas (datum in uro) vstavimo z uporabo funkcije NOW():

INSERT INTO novice (naslov, besedilo, cas) VALUES ('Testni naslov', 'Novička.', NOW());

Ostale pogosto uporabljane vgrajene MySQL funkcije so še:

• COUNT() — prešteje vrnjene vrstice
• SUM() — sešteje vrednosti v podanem polju v vseh vrnjenih vrsticah
• AVG() — izračuna povprečno vrednost v podanem polju v vseh vrnjenih vrsticah
• ROUND() — zaokroži vrednost podanega numeričnega polja
• TRIM() — "očisti" presledke z začetka in konca podanega niza
• SUBSTRING() — vrne določen podniz iz podanega niza

Primer: Če želimo preveriti, koliko tipkovnic in monitorjev imamo na zalogi, to storimo z uporabo MySQL funkcije SUM():

SELECT SUM(zaloga) FROM izdelki WHERE ime = 'Tipkovnica' OR ime = 'Monitor';

Po zgornjem ukazu nam MySQL strežnik vrne vrednost 55, saj imamo na zalogi 40 tipkovnic in 15 monitorjev.

Posodabljanje in brisanje podatkov

Če želimo število tipkovnic na zalogi iz 40 zmanjšati na 39, uporabimo stavek UPDATE:

UPDATE izdelki SET zaloga = 39 WHERE ime = 'Tipkovnica';

Ta stavek se torej vsaj delno obnaša podobno kot stavek SELECT — navesti moramo kriterije za izbiro vrstic (1 ali več), z ukazom SET pa nato spremenimo določene vrednosti stolpcev. Če želimo spremeniti vrednosti večim stolpcem, jih po ukazu SET ločimo z vejicami.

Zgornji primer lahko zapišemo tudi bolj "elegantno" — s sledečim SQL stavkom vrednost zaloga zmanjšamo za 1:

UPDATE izdelki SET zaloga = zaloga - 1 WHERE ime = 'Tipkovnica';

Pri uporabi tega stavka MySQL strežnik pogleda trenutno vrednost v stolpcu zaloga in jo zmanjša za 1.


Če želimo vsem izdelkom v tabeli spremeniti vrednost zaloga na 0, preprosto zapišemo:

UPDATE izdelki SET zaloga = 0;

Določene vrstice iz tabele izbrišemo z ukazom DELETE, ki se prav tako obnaša podobno kot ukaza SELECT in UPDATE:

DELETE FROM izdelki WHERE ime = 'Tipkovnica';

Z zgornjim ukazom izbrišemo vse vrstice z imenom 'Tipkovnica', v našem primeru gre za eno izbrisano vrstico (en izdelek).

Ustvarjanje podatkovnih baz in tabel

Novo podatkovno bazo ustvarimo s sledečim ukazom:

CREATE DATABASE ime_baze;

Če želimo za to podatkovno bazo ustvariti novega uporabnika, ki ima dostop samo do te podatkovne baze, to storimo takole:

GRANT ALL PRIVILEGES ON ime_baze.* TO username@localhost IDENTIFIED BY 'geslo';

Ustvarjanje posameznih tabel v podatkovni bazi je nekoliko bolj zahtevno opravilo. Običajno tabelo znotraj podatkovne baze ustvarimo takole:

CREATE TABLE izdelki
(
   ime VARCHAR(255),
   cena DECIMAL(6, 2),
   zaloga INT(6),
   id INT(6) NOT NULL auto_increment,
   PRIMARY KEY(id)
);

V tem poglavju smo spoznali teoretične osnove uporabe podatkovnih baz. Prav tako smo spoznali, kaj nam omogoča jezik SQL za delo s podatkovnimi bazami.

Zdaj pa je čas, da začnemo uporabljati MySQL podatkovno bazo v navezi z našo PHP programsko kodo...

Prejšnje poglavje
Branje in pisanje datotek