- Databázy
- MySQL
2.1 Spojenie s databázou
2.2 Ukončenie spojenia s databázou
2.3 Funkcia mysql_query();
2.3.1 Vytváranie a rušenie databáz
2.3.2 Vytváranie a rušenie tabuliek
2.3.3 Modifikácia tabuliek
2.3.4 Vkladanie udajov do tabuliek
2.3.5 Mazanie a zmena vložených údajov v tabuľkách
2.3.6 Výber údajov z tabuliek
2.3.6.1 Agregačné funkcie
2.3.6.2 Spájanie tabuliek - výber z viacerých tabuliek
2.3.6.3 Vnorené dopyty
2.3.6.4 Vytváranie aliasov
2.3.6.5 Vytváranie a rušenie pohľadov
2.4 Funkcia mysql_fetch_array() a mysql_fetch_object() - Záver ... alebo časť úvodu ?
3.1. Zoznam funkcií pre prácu s MySQL
3.1.1 Funkcie spomenuté v článku
3.1.2 Ostatné funkcie
3.1.2.1 mysql_affected_rows();
3.1.2.2 mysql_fetch_row();
3.1.2.3 mysql_num_rows();
3.1.2.4 Ostatné
DATABÁZY
Čo je to databáza ?Počítačová databáza je kolekcia štruktúrovaných dát ,alebo informácii uložených v počítačovom systéme takým spôsobom ,že počítačový program ,alebo človek môže pomocou dopytovacieho jazyka (angl. query language) získať informácie uložené v databáze.
[zdroj : www.wikipedia.org]
S akými databázami vie PHP spolupracovať ?
PHP vie spolupracovať s viacerými typmi databáz.Napr. PostgreSQL, MySQL , MSSQL ... My sa budeme venovať MySQL , keďže je najrozšírenejšia pre webové aplikácie a podporuje ju aj drvivá väčšina webhostingov.
Ak chcete môžte si pozrieť na adrese http://www.mysql.com/customers/ zoznam svetových firiem / inštitúcii (napr. Google, Yahoo) ,ktoré používajú MySQL.
MySQL
MySQL je voľne šíriteľný open-source relačný databázový server SQL. Je implementovaný vo viacerých programovacích jazykoch - PHP , Perl , C++ ... Každá databáza v MySQL má jednu ,alebo viac tabuliek. Stĺpce v tabuľke udávajú dátový typ a riadky reprezentujú jednotlivé hodnoty. Pracovať s touto databázou môžme pomocou tzv. dopytov (query) ,keďže to vychádza z programovacieho jazyka SQL (Structure Query Language).Jednoduchá schéma :
Aktuálna verzia k dátumu vzniku článku je 5.0.45 a všetky použité dopyty sú funkčné na tejto verzii. V predošlých verziách môžu mať niektoré dopyty rozdielny zápis ,aj keď drvivá väčšina bude funkčná aj na starších verziách.
Spojenie s databázou
Na to aby sme vôbec mohli pracovať s údajmi v databáze sa s ňou najprv musíme spojiť. Pre spojenie s databázou sa v PHP používa funkcia mysql_connect();mysql_connect("server","meno","heslo"); //pozn. toto sú najpoužívanejšie parametre
"server" (nepovinné)
je adresa servera na ktorom beží MySQL server. Východzie nastavenie je localhost:3306
"meno" (nepovinné)
je meno používateľa ,ktorý sa prihlasuje na databázový server. Východzie nastavenie je meno používateľa ,ktorý vlastní serverový proces.
"heslo" (nepovinné)
je heslo ,ktoré je nastavené pre prihlásenie používateľa. Východzia hodnota je prázdny reťazec " ".
Príklad 27
| <?php $spojenie = mysql_connect("localhost","root"); //uložíme mysql_connect do premennej if (!$spojenie) { //prefixom "!" zabezpečíme ,že pri chybe nám to vykoná nasledujúci kód echo "Chyba pri pripojení s databázovým serverom : ". mysql_error(); //vypíše chybovú hlášku . die; } ?> |
Takto sa pripojíme k databázovému serveru ,ale my sa ďalej potrebujeme pripojiť ku konkrétnej databáze. Toto zabezpečíme pomocou funkcie mysql_select_db();
Jej povinným parametrom je meno databázy.
Ak by sme chceli náš skript doplniť o výber databázy vyzeral by nasledovne :
Príklad 27
| <?php $spojenie = mysql_connect("localhost","root"); //uložíme mysql_connect do premennej if (!$spojenie) { //prefixom "!" zabezpečíme ,že pri chybe nám to vykoná nasledujúci kód echo "Chyba pri pripojení s databázovým serverom : ".mysql_error(); //vypíše chybovú hlášku . die; } $databaza = mysql_select_db("phpmanual"); if (!$databaza) { echo "Chyba pri výbere tabuľky :".mysql_error(); die; } ?> |
Podľa mňa najelegantnejším riešením ako sa pripojiť do databázy je naprogramovať si funkciu , ktorá by to spravila a jej parametrami by boli jednotlivé prihlasovacie údaje s menom databázy. Po prečítaní a pochopení predchádzajúcich článkov by ste to mali zvládnuť sami , ale pridávam príklad :
Príklad 28
| <?php function pripoj_db($server,$meno,$heslo,$databaza) { //definovanie funkcie $spojenie = mysql_connect($server,$meno,$heslo) if (!@$spojenie) { //prefix "@" nám potlačí východzie varovania a chyby PHP, tým pádom funkcia vypíše len našu chybu za if echo "Chyba pri pripojení do db : ". mysql_error(); die; } $vyber_db = mysql_select_db($databaza) if (!@$vyber_db) { echo "Chyba pri výbere databázy : ".mysql_error(); die; } } //neskoršie volanie pripoj_db("localhost","root","","phpmanual"); ?> |
Ukončenie spojenia s databázou
Spojenie sa ukončí ak skript prestane pracovať s databázou (zavrieme prehliadač) , avšak korektne by sme to mali urobiť pomocou funkcie mysql_close();Kde parametrom tejto funkcie je dané spojenie. Napríklad ak by sme chceli ukončiť predošlé spojenie , použili by sme :
mysql_close($spojenie);
Funkcia mysql_query();
Pre hocijakú operáciu v SQL ,potrebujeme zadať príkaz (pod pojmom príkaz chápte tzv. dopyt - query) v jazyku SQL avšak tento PHP priamym zápisom nepreloží. Na toto použijeme funkciu mysql_query();mysql_query("prikaz","spojenie");
"prikaz" (povinné)
je konkrétny príkaz na SQL - query
"spojenie" (nepovinné)
určuje ,ktoré spojenie sa má pre príkaz použiť. Východzie je nastavené posledné otvorené spojenie.
Vytváranie a rušenie databáz
Na vytváranie databáz sa používa SQL query : "CREATE DATABASE meno_databázy". ak by sme v PHP chceli vytvoriť databázu urobili by sme to pomocou funckie mysql_query. Analogicky by sme databázu zrušili pomocou SQL query : "DROP DATABASE meno_databázy".Nasledujúci príklad vytvorí databázu "skuska" a v prípade chyby ju vypíše , inak potvrdí úspešné vytvorenie. Hneď na to ju zruší.
Príklad 29
| <?php function pripoj_db($server,$meno,$heslo="") { $spojenie = mysql_connect($server,$meno,$heslo); if (!@$spojenie) { echo "Chyba pri pripojení do db : ". mysql_error(); die; } } pripoj_db("localhost","root"); $query = mysql_query("CREATE DATABASE skuska"); if(!$query) { echo "Chyba pri vytváraní db : ".mysql_error(); die; } else { echo "Databáza bola úspešne vytvorená !"; mysql_close(); } pripoj_db("localhost","root",""); $query = mysql_query("DROP DATABASE skuska"); if(!$query) { echo "Chyba pri rušení db : ".mysql_error(); die; } else { echo "<br /> Databáza bola úspešne zmazaná !"; echo $spojenie; mysql_close(); } ?> |
Vytváranie a rušenie tabuliek
Pre vytváranie tabuliek používame SQL query : "CREATE TABLE". Presná forma je napríklad takáto :CREATE TABLE tabulka (
id int(5) auto_increment,
retazec varchar(100) not null,
cislo int(50),
PRIMARY KEY (id) )
Vysvetlenie :
"povedali" sme asi toto : vytvor tabuľku s názvom "tabulka" ,so stĺpcom "id" ,ktorý sa automaticky bude navyšovať o jednu jednotku y+1 (auto_increment) a jeho typ je integer (int), ďalší stĺpec bude mať názov "retazec" , jeho typ bude string (varchar) a maximálny počet znakov sme nastavili na 100 a táto hodnota nemôže byť nulová. Posledný stĺpec bude mať názov "cislo" , jeho typ je integer s maximálnym počtom znakov 50. Stĺpec "id " je primárnym kľúčom čo jednoznačne identifikuje daný riadok.
Pri vytváraní tabuliek môžme nejakému stĺpcu nastaviť východziu hodnotu. Ak užívateľ nezadá vo vstupe danú hodnotu mysql si doplní tú ,ktorá je nastavená pomocou kľúčového slova DEFAULT.
CREATE TABLE tabulka (
skuska varchar(50) DEFAULT 'hodnota', ...
Môže sa stať ,že v tabuľke potrebujeme stĺpec okrem primárneho kľúča ,ktorého hodnota nesmie byť rovnaká. Potrebujeme unikátny stĺpec ,ktorý dosiahneme použitím kľúčového slova UNIQE. Primárnym kľúčom môže byť len jeden stĺpec , ale príznak UNIQUE môže mať viacej stĺpcov.
CREATE TABLE tabulka (
skuska varchar(50) UNIQUE, ...
Tabuľka dátových typov v MySQL :
| Dátový typ MySQL | Veľkosť |
| tinyint | -128 až 127 |
| smallint | -32,768 až 32,767 |
| mediumint | -8388608 až 8388607 |
| int | -2147483647 až 214748347 |
| bigint | 64 bit |
| float | 32 bit desatinné č. |
| double | 64 bit desatinné č. |
| decimal | desatinné č. |
| char | 1 až 255 znakov |
| varchar | 1 až 255 znakov |
| tinytext | 1 až 255 znakov |
| text | 1 až 63535 znakov |
| mediumtext | 1 až 16777215 znakov |
| longtext | 1 až 4294967295 znakov |
| všetky BLOB typy | 1 až 4294967295 bajtov |
| date | dátum bez času |
| datetime | dátum s časom |
| timestamp | dátum s časom |
| time | čas |
| year | rok |
[zdroj : dev.mysql.com]
pozn.: tabuľka neobsahuje všetky dátové typy
Číselným dátovým typom vieme pridať vlastnosť "UNSIGNED" ,ktorá prenesie hodnotu záporných čísel do kladných, čím sa rozsah kladných čísel zdvojnásobí.
Pre rušenie tabuliek použijeme SQL query : "DROP TABLE meno_tabulky".
Nasledujúci príklad vytvorí tabuľku "tabulka" s horeuvedenými parametrami a následne ju zruší. To všetko za predpokladu ,že je vytvorená databáza s názvom "phpmanual".
Príklad 30
| <?php function pripoj_db($server,$meno,$heslo="",$databaza) { $spojenie = mysql_connect($server,$meno,$heslo); if (!@$spojenie) { echo "Chyba pri pripojení s db serverom : ". mysql_error(); die; } $db = mysql_select_db($databaza); if (!@$db) { echo "Chyba pri výbere db : ".mysql_error(); die; } } pripoj_db("localhost","root","","phpmanual"); $query = mysql_query("CREATE TABLE tabulka(id int(5) auto_increment,retazec varchar(100) not null,cislo int(10), primary key(id))"); if (!$query) { echo "Nastala chyba pri vytváraní tabuľky : ".mysql_error(); die; } else { echo "Tabuľka bola úspešne vytvorená"; } $query = mysql_query("DROP TABLE tabulka"); if (!$query) { echo "Nastala chyba pri vytváraní tabuľky : ".mysql_error(); die; } else { echo "Tabuľka bola úspešne vytvorená"; } ?> |
Modifikácia tabuliek
Na modifikáciu tabuliek používame ALTER TABLE. Vytvorme si jednoduchú tabuľku :CREATE TABLE zmen_ma (
nick varchar(30),
vek int(3))
RENAME - Zmena názvu tabuľky
ALTER TABLE zmen_ma RENAME zmeneny_nazov
Naša tabuľka už má názov "zmeneny_nazov".
MODIFY,CHANGE - Zmena vlastností jednotlivých položiek v tabuľke
Zmeníme typ položky "nick" z varchar(30) na char(20) a nastavíme jej príznak NOT NULL pomocou MODIFY a položke vek zmeníme názov z vek na vek_a pomocou CHANGE. (Syntax : CHANGE stary_nazov novy_nazov TYP - typ musí byť uvedený ,ak sa nemení tak uvádzame taký istý ako je nastavený)
ALTER TABLE zmeneny_nazov MODIFY nick char(20) NOT NULL ,
CHANGE vek vek_a int(3)
ADD - Pridávanie stĺpcov a príznaku primary key do tabuľky
Do našej tabuľky pridáme stĺpec s názvom registrácia ,ktorý bude predstavovať rok registrácie , čiže bude typu year.
ALTER TABLE zmeneny_nazov ADD registracia year(4)
Keby sme chceli k stĺpcu s názvom "nick" pridať príznak primárneho kľúča , zadali by sme :
ALTER TABLE zmeneny_nazov ADD PRIMARY KEY(nick)
Pozn. Príznak PRIMARY KEY môžme pridať len stĺpcu ,ktorý ma nastavenie NOT NULL !
DROP - Odstraňovanie stĺpcov z tabuľky
Odstráňme z našej tabuľky nedávno vytvorený stĺpec s názvom registracia.
ALTER TABLE zmeneny_nazov DROP registracia
Vkladanie údajov do tabuliek
Pre vkladanie dát používame sql query : INSERT. Z predošlého textu je jasné ,že údaje vkladáme do tabuľky preto má tento dopyt (query po slovensky) tvar :INSERT INTO nazov_tabulky (
nazov prveho stlpca,
nazov druheho stlpca,
...
nazvo posledneho stlpca)
VALUES (
hodnota prveho stlpca,
hodnota druheho stlpca,
...
hodnota posledneho stlpca)
Ak chcete nemusíte používať časť s názvami stĺpcov ,ale rovno zapisovať hodnoty. Ak chcete zapisovať hodnoty bez tejto časti musíte vyplniť záznam pre každý jeden stĺpec v tabuľke, ak hodnotu pre niektorý stĺpec nechcete zadať musíte zadať prázdne úvodzovky. V príkladoch názvy stĺpcov nie sú použité.
Vkladanie dát nám objasní príklad :
Vytvoríme si tabuľku podľa nasledovného dopytu :
CREATE TABLE zamestnanci (
id int(5) not null auto_increment,
meno varchar(30),
priezvisko varchar(50) not null,
rc int(10) not null,
primary key(id))
PHP kód pre vloženie záznamu by vyzeral nasledovne :
mysql_query("INSERT INTO zamestnanci VALUES ('','Peter','Horný','7744778855')");
Ako ste si všimli pre stĺpec id som neudal hodnotu (len úvodzovky , tie sú potrebné pre každý stĺpec kde nechceme zadať hodnotu. Môžme použiť aj zápis NULL) , je to preto ,že id je automaticky navyšovaná hodnota ,ktorú nie je potrebné definovať. Je to zároveň primárny kľúč ,ktorý jednoznačne identifikuje riadok. Teda musí byť jedinečný.
POZN:
Do každého dopytu môžme vložiť názov databázy.O to sa síce v PHP stará mysql_select_db() , ale ak budete prikazovať priamo cez SQL (tam sa o to zase stará príkaz use :) ) ,ale môžte použiť :
INSERT INTO databaza.tabulka ....
Mazanie a zmena vložených údajov v tabuľkách
Na mazanie údajov používame dopyt DELETE. Základná schéma je :DELETE FROM tabulka WHERE podmienka
Vezmime ,že chceme vymazať údaj ,ktorý sme o kapitolu vyššie vložili do tabuľky zamestnanci.
DELETE FROM zamestnanci WHERE meno LIKE "Peter" AND priezvisko LIKE "Horný"
Zápis : DELETE FROM zamestnanci . nám vymaže obsah tabuľky. Akciu nie je možné vrátiť späť.
Ovšem ak chceme vymazať všetky údaje z tabuľky použijeme rýchlejší príkaz :
TRUNCATE TABLE nazov_tabulky
Ak chceme upraviť vložené údaje , použijeme dopyt UPDATE.
Základná schéma príkazu je :
UPDATE tabuľka SET stĺpec=hodnota WHERE podmienka
Upravme si trošku našu tabuľku a stĺpcu "rc" zmeňme názov na "plat" a zachovajme typ.
ALTER TABLE zamestnanci CHANGE rc plat int(10)
ďalej zmeňme stĺpec "meno" na "funkcia".
ALTER TABLE zamestnanci CHANGE meno funkcia varchar(30)
Vložme do nej údaje , aby vyzerala asi takto :
| id | funkcia | priezvisko | plat |
| 1 | vedúci | Horný | 75000 |
| 2 | vedúci | Dolný | 50000 |
| 3 | asistent | Kávičkár | 14000 |
| 4 | vrátnik | Branný | 12000 |
Najjednoduchším použitím je použitie bez WHERE ,čiže bez podmienky.
UPDATE zamestnanci SET plat=20000
by zmenilo plat všetkým zamestnancom na 20000.Ale to nechceme.
V drvivej väčšine prípadov ,ale potrebujeme zmeniť záznam len pri konkrétnych položkách.
Povedzme ,že prilepšíme plat vrátnikovi o 500 korún :
UPDATE zamestnanci SET plat=plat+500 WHERE funkcia LIKE "vrátnik"
Dajme tomu ,že firme sa darí a chceme zvýšiť platy všetkým zamestnancom, ktorí majú plat pod 60000 o 10%. Ale vrátnikovi už nie.
UPDATE zamestnanci SET plat=plat+(0.10*plat) WHERE plat<60000 AND NOT funkcia LIKE "vrátnik"
Výber údajov z tabuliek
Pre výber dát z databázy používame dopyt SELECT. Základná schéma je takáto :SELECT dáta FROM tabuľka WHERE podmienka GROUP BY zoznam položiek HAVING skupinová podmienka ORDER BY zoraď podľa LIMIT počet záznamov
Pre vysvetlenie dopytu SELECT si vytvorme podľa nasledujúcich kritérií tabuľku (nebudem už vypisovať celý PHP kód na vytvorenie tabuľky, je v kapitole "vytváranie a rušenie tabuliek") :
CREATE TABLE studenti (
meno varchar(30) not null,
priezvisko varchar(50) not null,
vek int(2) not null)
Doplníme si tam údaje podľa nasledujúcich kritérií :
INSERT INTO studenti VALUES (
'Fero','Mrkva','14'),(
'Jano','Nicomny','17'),(
'Jozef','Iny','15')
Úplne základným príkladom SELECT je :
SELECT * FROM studenti ,čo vyberie všetky údaje z tabuľky študenti.
Použijeme WHERE :
SELECT meno,vek FROM studenti WHERE vek > 14 ,
vyberie údaje v stĺpci meno a vek ,kde vek je väčší ako 14. Čiže to bude :
Jano,17
Jozef,15
Pri podmienke za WHERE pri číselných údajoch môžme použiť aj = ,ale ak sa jedná o reťazce použijeme LIKE (ale je možné použiť aj =) :
SELECT meno,vek FROM studenti WHERE meno LIKE "Jano"
Vyberie :
Jano,17
Pri LIKE môžme použiť aj :
A% - kde A určuje začiatočné písmeno reťazca a znak "%" predstavuje zvyšný počet ľubovoľných znakov reťazca
A__ - kde A je takisto začiatočné písmeno a znak _ predstavuje jeden ľubovoľný znak reťazca
_B% - tieto znaky môžme kombinovať
Pri podmienkach môžeme využiť aj logické operátory AND , OR , NOT , napríklad :
SELECT * FROM studenti WHERE meno LIKE "Jano" AND vek>12
Vyberie všetkých ,ktorý majú meno "Jano" a ich vek je väčší ako 12 , čiže z našej tabuľky je to :
Jano,17
Takisto môžme použiť operátor IS NOT NULL ,alebo IS NULL :
SELECT * FROM studenti WHERE meno IS NOT NULL
Samozrejmosťou je použitie operátorov > , < , = , použitých aj v predošlých príkladoch.
Ak chceme limitovať (obmedzovať) výpis s databázy použijeme LIMIT.
SELECT * FROM studenti WHERE vek>12 LIMIT 0,1
Vyberie len jeden záznam ,kde v stĺpci vek je hodnota väčšia ako 12. Ak chceme zväčšiť počet záznamov použijeme napríklad 0,10. Obecne čísla za LIMIT znamenajú od,do.
Na zoradenie výsledku použijeme ORDER BY :
SELECT * FROM studenti ORDER BY meno
Vyberie všetky údaje a zoradí výsledok od najnižšej hodnoty , resp. abecedne.
Ak by sme potrebovali zoradiť údaje opačne , teda od najvyššej hodnoty , resp. abecedne od "z", použijeme kľúčové slovo DESC.
SELECT * FROM studenti ORDER BY meno DESC
Agregačné funkcie
Ešte pred agregáciou si povieme niečo o zoskupení GROUP BY. Zoskupenie sa prevádza podľa jednotlivých hodnôt v stĺpcoch. Zoskupovať môžme podľa hodnôt vo viacerých stĺpcoch - obecne v koľkých chceme.Zoskupenie spočíva v tom ,že rovnaké riadky vo vybratom(ých) stĺpci(och) mysql, prezentuje ako jeden riadok. Na ostatné stĺpce musíme použiť agregačnú funkciu aby nám hodnoty v riadkoch prezentovalo mysql ako jeden riadok.
Asi som to napísal trošku nezrozumiteľne ,ale viac napovedia príklady.
COUNT()
Je najjednoduchšia agregačná funkcia. Táto funkcia nám vráti počet záznamov v tabuľke. Najjednoduchší zápis :
SELECT COUNT(*) FROM studenti
vypíše číslo 3.
Na našu tabuľku "studenti" už zabudnime a vytvorme si ako príklad takúto :
CREATE TABLE knihy (
id int(4) NOT NULL AUTO_INCREMENT,
nazov_knihy varchar(100) NOT NULL,
autor_knihy varchar(100) NOT NULL,
rok_vydania year,
cena int(8),
PRIMARY KEY(id))
Obsah by bol nasledovný :
| id | nazov_knihy | autor_knihy | rok_vydania | cena |
| 1 | Zlatá baňa | Jozef Mak | 2001 | 400 |
| 2 | Zlatá baňa | Fero Kvák |
2001 | 400 |
| 3 | Passians | Marek Grúň | 2001 | 550 |
| 4 | Passians | Fero Kvák | 2001 | 550 |
| 5 | Automobilizmus | Ondrej Automobov | 1999 | 1014 |
Ak by sme chceli vypísať koľko autorov má kniha vo forme "nazov_knihy,pocet autorov" použili by sme takýto dopyt :
SELECT nazov_knihy, COUNT(*) FROM knihy GROUP BY nazov_knihy
SUM(stĺpec)
Vykoná súčet hodnôt v stĺpci. Napríklad by sme chceli spočítať sumu za jednotlivý rok vydania vo forme "rok_vydania,cena". Ja viem je to absurdita ,pretože je suma v stĺpci 2x za tú istú knihu v prípade opakujúceho sa názvu ,ale berte to ako príklad.
SELECT rok_vydania, SUM(cena) FROM knihy GROUP BY rok_vydania
Výber :
| rok_vydania | SUM(cena) |
| 1999 | 1014 |
| 2001 | 1900 |
AVG(stĺpec)
Vypočíta aritmetický priemer v stĺpci. Ak by sme chceli vypočítať aritmetický priemer z ceny kníh vydaných v roku 2001 , použili by sme dopyt (Ja viem ! Ďalšia absurdita :) , ale to je len príklad) :
SELECT rok_vydania, AVG(cena) FROM knihy WHERE rok_vydania=2001 GROUP BY rok_vydania
Výber :
| rok_vydania | AVG(cena) |
| 2001 | 475.0000 |
MIN(stĺpec)
Vráti minimum z hodnôt v zoskupenom stĺpci. Výber najstaršie vydanej knihy vo forme "nazov_knihy,rok_vydania" :
SELECT nazov_knihy , MIN(rok_vydania) FROM knihy GROUP BY nazov_knihy LIMIT 0,1
MAX(stĺpec)
Vráti maximálnu hodnotu z hodnôt v zoskupenom stĺpci. Výber najdrahšej knihy vo forme "nazov_knihy,cena" :
SELECT nazov_knihy, MAX(cena) FROM knihy GROUP BY nazov_knihy LIMIT 0,1
Viacnásobné použitie agregačných funkcií
Ako vybrať záznamy pomocou agregačných funkcií sme si ukázali. Ale niekedy je potrebné v rámci týchto záznamov vybrať ďalšie ,ktoré splňujú ďalšiu podmienku.Na to použijeme HAVING.
Typickou podmienkou za HAVING je agregačná funkcia , ale samozrejme nemusí byť. Ale pokiaľ nevyužívame agregačné f. ,podmienky dávame za WHERE.
Ak by sme chceli vypísať zoznam autorov ,ktorý majú v našej tabuľke viac než jeden záznam použili by sme :
SELECT autor_knihy, COUNT(*) FROM knihy GROUP BY autor_knihy HAVING COUNT(*)>1
Spájanie tabuliek - výber z viacerých tabuliek
Spájanie tabuliek nie je ničím iným než výber údajov z viacerých tabuliek ,ktoré sú prepojené určitou závislosťou.Vnútorné spojenie
Majme dve tabuľky :
autori knihy
| id | autor | nazov | napisal_id | |
| 1 | Peter Železný | Lesné rozprávky | 2 | |
| 2 | Marek Drevený | Tučný škriatok | 1 | |
| 3 | Jozef Plastový | Železná pani | 1 |
V prvej sú autori kníh a v druhej názvy kníh s id autora.
Ako vybrať údaje s jednej tabuľky už vieme , ale čo ak by sme chceli vedieť ,ktorý autor napísal knihu "Lesné rozprávky" ? Samozrejme nebudeme spájať tabuľky do jednej fyzicky ale použijeme tento dopyt :
SELECT autor,nazov FROM autori,knihy WHERE id=napisal_id AND nazov LIKE "Lesné rozprávky"
Výsledok :
| autor | nazov |
| Marek Drevený | Lesné rozprávky |
Prvou podmienkou za WHERE sme prepojili tabuľky a druhou sme si vyfiltrovali potrebný záznam.
Prirodzené spojenie naších dvoch tabuliek vyzerá takto :
| nazov | napisal_id | id | autor |
| Lesné rozprávky | 2 | 2 | Marek Dreveny |
| Tučný škriatok | 1 | 1 | Peter Železný |
| Železná pani | 1 | 1 | Peter Železný |
Vidíme ,že v tabuľke nie je autor "Jozef Plastový" ,pretože nenapísal žiadnu knihu z tabuľky "knihy". Ak chceme vypísať koľko kníh napísal ,ktorý autor , pomocou vnútorného prepojenia vypíšeme len tých ,ktorý majú nejaký záznam v druhej tabuľke - knihy.
SELECT autor, COUNT(*) FROM autori,knihy WHERE id=napisal_id GROUP BY autori
Výsledok :
| autor | COUNT(*) |
| Marek Dreveny | 1 |
| Peter Železný | 2 |
Vonkajšie spojenie
Čo ak chceme vypísať autorov a k ním napísanú knihu ,ale chceme aj tých autorov čo nenapísali žiadnu knihu. Dá sa to , ale použijeme trošku odlišný prístup. Všeobecný zápis by mohol byť takýto :
SELECT údaje FROM tabuľka LEFT/RIGHT JOIN tabuľka ON podmienka prepojenia WHERE podmienka(y) výberu
Kľúčové slovo LEFT ,alebo RIGHT JOIN nám spája tabuľku z prava ,alebo z ľava. Obecne sa dá povedať ,že týmto slovom povolíme výskyt nulových hodnôt v pravej (RIGHT JOIN) ,alebo v ľavej (LEFT JOIN) tabuľke vzhľadom na polohu tabuľky od tohto kľúčového slova.
Pre náš príklad by sme použili dopyt :
SELECT autor,nazov FROM autori LEFT JOIN knihy ON id=napisal_id
Výsledok :
| autor | nazov |
| Peter Železný | Tučný škriatok |
| Peter Železný | Železná pani |
| Marek Drevený | Lesné rozprávky |
| Jozef Plastový | NULL |
Ak by sme použili :
výsledok by bol taký ako pri vnútornom spojení. Bez posledného riadku.
Vnorené dopyty
Vnorený dopyt je jeden SELECT vnorený do druhého. Vysvetlíme si jednoduché vnorené dopyty, čo sú dopyty ,ktoré nám vracajú jednu hodnotu. Vnorený dopyt sa píše do zátvoriek () a je vždy za kľúčovým slovom WHERE. Pri vnorenom dopyte obecne používame agregačnú funkciu, pretože klasický dopyt nemá význam vnoriť,keďže máme WHERE. Všeobecný zápis vyzerá takto :SELECT údaje FROM tabuľka WHERE (SELECT agregačná funkcia FROM tabuľka).
Vysvetlíme si to na príklade. Zoberme si tabuľku "knihy" použitú v predošlej kapitole a trošku ju upravme :
ALTER TABLE knihy ADD cena int(5)
UPDATE knihy SET cena=520 WHERE nazov LIKE "Lesné rozprávky"
UPDATE knihy SET cena=750 WHERE nazov LIKE "Tučný škriatok"
UPDATE knihy SET cena=1110 WHERE nazov LIKE "Železná pani"
Tabuľka knihy vyzerá nasledovne :
| nazov | napisal_id | cena |
| Lesné rozprávky | 2 | 520 |
| Tučný škriatok | 1 | 750 |
| Železná pani | 1 | 1110 |
Chceme zistiť najvyššiu cenu z tabuľky - agregačná funkcia MAX(stĺpec) :
SELECT MAX(cena) FROM knihy
Výsledok je 1110,
teraz chceme vedieť jej názov :
SELECT nazov FROM knihy WHERE cena=1110
Toto samozrejme vieme docieliť pomocou vnoreného dopytu jedným zápisom :
SELECT nazov FROM knihy WHERE cena=(SELECT MAX(cena) FROM knihy)
Ďalší príklad :
Chceme vedieť názov a cenu najlacnejšej knihy :
SELECT nazov,cena FROM knihy WHERE cena=(SELECT MIN(cena) FROM knihy)
Výsledok :
| nazov | cena |
| Lesné rozprávky | 520 |
Použitie operátorov
Okrem klasických relačných operátorov môžme pri vnorovaní použiť ešte operátory IN, ANY , ALL.
Operátor IN
Používa sa ku jednoduchému porovnanie či v stĺpci naľavo sa nachádzajú hodnoty uvedené vpravo. Všeobecný zápis v SELECT :
SELECT údaje FROM tabuľka WHERE podmienka AND stĺpce IN (údaje k porovnaniu)
Upravme si ešte tabuľku "knihy".
ALTER TABLE knihy ADD rok_vydania year(4)
UPDATE knihy SET rok_vydania=2001 WHERE nazov LIKE "Lesné rozprávky"
UPDATE knihy SET rok_vydania=1990 WHERE nazov LIKE "Tučny škriatok"
UPDATE knihy SET rok_vydania=1980 WHERE nazov LIKE "Železná pani"
Tabuľka teraz vyzerá takto :
| nazov | napisal_id | cena | rok_vydania |
| Lesné rozprávky | 2 | 520 | 2001 |
| Tučný škriatok | 1 | 750 | 1990 |
| Železná pani | 1 | 1110 | 1980 |
Osviežme si pamäť a spomeňme si na našu tabuľku "autori" :
| id | autor |
| 1 | Peter Železný |
| 2 | Marek Drevený |
| 3 | Jozef Plastový |
Spomínam ju preto ,že ju využijeme v našom príklade. Teda dúfam ,že Vás nedopletiem ešte so spájaním tabuliek ,ale priemerne inteligentný človek by sa v tom mal vyznať - a to Vy predsa ste (keď nie nadpriemerne :) ).
Takže ku príkladu:
Chceme vedieť názov a autora knihy ,ktoré boli vydané v rokoch 1980 ,1990 a 2001 .(V rámci našej tabuľky by sme použili výber všetkých údajov ,ale berme ,že má údajov viac). Pomocou operátora IN by náš dopyt vyzeral nasledovne :
SELECT autor,nazov FROM autori,knihy WHERE id=napisal_id AND rok_vydania IN (1980,1990,2001)
Ak by sme chceli len diela Petra Železného vydané v týchto rokoch použili by sme vnorený dopyt:
SELECT autor,nazov FROM autori,knihy WHERE id=napisal_id AND rok_vydania IN (SELECT rok_vydania FROM knihy WHERE autor LIKE "Peter Železný")
Čiže výsledok za IN v zátvorkách musí byť rok_vydania ,lebo tento porovnávame a selectom si už zadáme podmienku. Všimnite si ,že vo vnorenom dopyte som už nemusel spájať tabuľky a aj tak príkaz poznal stĺpec "autor".
Operátory ANY(SOME),ALL
Používa sa ak chceme porovnať či aspoň jedna z uvedených hodnôt (alebo hodnôt vrátených vnoreným dopytom) sa nachádza v stĺpci. Všeobecný zápis :
SELECT údaje FROM tabuľka WHERE podmienka AND stĺpec ANY/SOME/ALL (vnorený dopyt, alebo údaje)
Operátor ALL narozdiel od ANY/SOME porovnáva či sa všetky hodnoty vnoreného dopytu nachádzajú v stĺpci.
Príklad :
Chceme vidieť názvy kníh a rok vydania ,ktoré vydal Peter Železný ešte skôr ako vydal Marek Drevený akúkoľvek svoju knihu. (Ja viem Drevený podľa našej tabuľky vydal len 1 knihu ,ale dopyt zostavím bez ohľadu na to) Narozdiel od predošlého príkladu spojím tabuľky aj vo vnorenom dopyte.
SELECT autor,nazov,rok_vydania FROM autori,knihy WHERE id=napisal_id AND nazov LIKE "Peter Železný" AND rok_vydania < ALL(SELECT rok_vydania FROM autori,knihy WHERE id=napisal_id AND nazov LIKE "Marek Drevený")
Ako vidíte zostavovať dopyt pre výber údajov z databázy nie je až také jednoduché ako sa zdalo na začiatku :)
Vytváranie aliasov
Aliasy tabuliekAliasy tabuliek je niekedy potrebné vytvoriť pri pokročilých vnorených dopytoch , najmä keď sa také isté tabuľky nachádzajú v nadradenom SELECT-e.
Vytvoríme ich pomocou prefixu ,ktorý definujeme za názvom tabuľky a potom ho používame pred každým názvom stĺpca ,kde ako separátor používame "." bodku. Príklad :
SELECT * FROM knihy WHERE cena<ALL(SELECT cena FROM knihy k WHERE k.rok_vydania>1980)
Aliasy stĺpcov
Aliasy stĺpcov môžme použiť pri výbere pomocou dopytu SELECT. Všeobecný zápis :SELECT stĺpec AS alias_stĺpec , stĺpec2 AS alias_stĺpec2 ...
Napríklad chceme aby vo výbere z našej tabuľky "knihy" bol stĺpec "napisal_id" nazvaný "autorove_id". Použijeme :
SELECT nazov,napisal_id AS autorove_id,cena,rok_vydania FROM knihy
Takisto môžme použiť matematické operátory (+,-,/) pri výbere a spájaní stĺpcov. Majme tabuľku "matematika" :
| A | B |
| 10 | 10 |
| 15 | 20 |
| 20 | 15 |
SELECT a+b AS sucet FROM matematika , nám vyberie :
| sucet |
| 20 |
| 35 |
| 35 |
Obdobne môžme použiť :
SELECT a-b AS rozdiel FROM matematika , alebo SELECT a/b AS podiel FROM matematika
Vytváranie a rušenie pohľadov
Pohľad je akási virtuálna tabuľka , ktorú vytvoríme z normálnej tabuľky. Z pohľadu práce s MySQL sa pohľad javí ako normálna tabuľka. Pohľad ,ktorý vytvoríme môžme neskôr použiť aj vo vnorenom dopyte. Ak by sme chceli aby užívatelia videli z našej tabuľky "knihy" všetky údaje okrem ceny , vytvorili by sme pohľad ,kde by tento stĺpec už nebol. Všeobecný zápis pre vytvorenie pohľadu je :CREATE VIEW nazov_pohladu (stĺpec,stĺpec2 ...) AS SELECT údaje FROM tabuľka
Časť so stĺpcami za názvom pohľadu uvádzame vtedy ak chceme nejakým spôsobom zmeniť ich názvy.
Napríklad chceme pohľad ,ktorý bude taký istý ako tabuľka "knihy" ,ale nebude obsahovať stĺpec "cena" :
CREATE VIEW knihy_bez_ceny AS SELECT nazov,napisal_id,rok_vydania FROM knihy
S pohľadom môžme pracovať ako s normálnou tabuľkou. Teda aplikujeme na nich UPDATE,INSERT,DELETE a MySQL sa už postará o manipuláciu s normálnymi tabuľkami.
Dajme si príklad so spojením tabuliek : Chceme pohľad kde bude meno autora a názov knihy.
CREATE VIEW autori_knih AS SELECT autor,nazov FROM autori,knihy WHERE id=napisal_id
Ak chceme pohľad zrušiť , použijeme na to taký istý príkaz ako pri tabuľkách DROP , ale s kľúčovým slovom VIEW. Všeobecný zápis :
DROP VIEW nazov_pohladu
Funkcia mysql_fetch_array() a mysql_fetch_object()
Ako sme si povedali pre použitie SQL dopytov v PHP používame funkciu mysql_query("dopyt"); , ale hodnotu vrátenú z použitého dopytu aj tak nevieme preniesť do PHP , keďže nemáme priradenú žiadnu premennú. Funkcia mysql_query tým pádom nesie len nejaké "nereálne" údaje. Na to aby sme výsledok z databázy preniesli do nejakej premennej resp. do poľa používame funkciu mysql_fetch_array(); (poznáme aj funkciu ,ktorá nám prenesie výsledok dopytu do objektu mysql_fetch_object() ,ale tej sa budeme venovať v časti venovanej objektovo orientovanému programovaniu).Všeobecný zápis :
mysql_fetch_array(údaje,typ_poľa);
"údaje" (povinné)
Sú údaje vytiahnuté z databázy, čiže hodnota vrátená funkciou mysql_query.
"typ_poľa" (nepovinné)
Definuje typ poľa :
MYSQL_ASSOC - definuje asociatívne pole
MYSQL_NUM - definuje numerické pole
MYSQL_BOTH - pracuje sa s oboma typmi - východzie nastavenie
Je dôležité vedieť ,že táto funkcia nám vracia iba jeden záznam ! Čiže ak by mal SQL dopyt viacej záznamov funkcia by vrátila len prvý riadok z danej tabuľky. Na vypísanie celého obsahu výberu , používame cyklus.
while ($zaznam = mysql_fetch_array($dopyt)) {
príkazy ....
}
Ukážeme si to na príklade.
Použijeme tabuľku autori a knihy z kapitoly "Spájanie tabuliek". Príklad číslo 31 nám vypíše v tabuľke meno autora ,názov knihy a cenu. A vypíše nám aj autora ,ktorý nenapísal nič.
Príklad 31
| <?php //funkcia pre pripojenie do db , pri zložitejších skriptoch ju môžme uložiť do súboru a zavolať súbor pomocou require/include function pripoj_db($databaza,$server="localhost",$user="root",$heslo="") { $spojenie = MySQL_Connect($server,$user,$heslo); if (!$spojenie) { echo "Pri spojení s databázovým serverin došlo ku chybe.<br />Výpis chyby :".MySQL_Error(); die; } $db = MySQL_Select_db($databaza); if (!$db) { echo "Došlo ku chybe pri výbere databázy !.<br />Výpis chyby :".MySQL_Error(); die; } } //kedze parametere pre pripojenie mame definovane , stačí nám zadať databázu pripoj_db("phpmanual"); //sql dopyt - vonkajšie spojenie tabuliek $dopyt = MySQL_Query("SELECT autor,nazov,cena FROM autori LEFT JOIN knihy ON id=napisal_id"); //začiatok tabuľky echo "<table border='1'>"; echo "<tr><td><strong>Meno autora</strong></td><td><strong>Názov knihy</strong></td><td><strong>Cena</strong></td></tr>"; //naplnenie tabuľky údajmi, tj. naplnenie poľa $udaje pomocou mysql_fetch_array while ($udaje=MySQL_Fetch_Array($dopyt)) { echo "<tr><td>$udaje[0]</td><td>$udaje[1]</td><td>$udaje[2]</td></tr>"; } //koniec tabuľky echo "</table>"; //ukončenie spojenia MySQL_Close(); ?> |
ZÁVER ...ALEBO ČASŤ ÚVODU ?!
Pri čítaní (študovaní) tohto článku majte na pamäti ,že bol písaný na viackrát :). To znamená ,že som ho písal dlho, resp. viacej dní ,preto sa môže stať ,že jednotlivé kapitoly môžu mať rôznu štylistiku ,keďže nemám vyhradený svoj smer. Takisto preto ,je v príkladoch použitých veľmi veľa rôznych tabuliek , pretože som to postupne doplňoval a niekedy aj pred kapitolu ,ktorú som už mal dokončenú. Každopádne to nemení nič na tom ,že príklady svoj účel vysvetlenia plnia.Možno sa pri čítaní kapitoly o funkcii mysql_query , pýtate ,prečo som nepísal php príklady ,ale len dopyty. Je to preto aby som dĺžku článku už zbytočne nenaťahoval a za každým dopytom by to bolo aj zbytočné opakovanie. Na konci v kapitole "Funkcia mysql_fetch_array() a mysql_fetch_object()" je php príklad ako vybrať údaje z tabuľky MySQL. Tento si myslím dostatočne prezentuje použitie SQL dopytov v PHP. Samozrejme je tu aj priestor pre vyjadrenie , otázky, pripomienky a pod. - komentáre.
Zoznam funkcií pre prácu s MySQL :
Funkcie použité v článku
mysql_connect() - kapitola "Spojenie s databázou"mysql_close() - kapitola "Ukončenie spojenia s databázou"
mysql_query() - rozsiahle vysvetlenie v kapitole "Funkcia mysql_query"
mysql_fetch_array() - kapitola "Funkcia mysql_fetch_array a mysql_fetch_object"
Ostatné funkcie
mysql_affected_rows()
vráti počet riadkov ovplyvnených posledným dopytom.mysql_affected_rows(spojenie);
parameter "spojenie" - je nepovinný a obsahuje spojenie pre ,ktoré sa má funkcia použiť. Ak tento parameter nezadáme , funkcia pracuje s posledným otvoreným spojením.
Príklad 32
| <?php //použijeme funkciu s predošlého príkladu, už ju nebudem rozpisovať pripoj_db("phpmanual"); $dopyt = MySQL_Query("INSERT INTO autori VALUES('','Peter Mrkva')"); $vlozene_riadky = MySQL_Affected_Rows(); echo "Počet vložených riadkov :".$vlozene_riadky; ?> |
Výstup skriptu je : "Počet vložených riadkov : 1".
mysql_fetch_row();
Vracia výsledok dopytu ako pole. Narozdiel do mysql_fetch_array , ktorá vracia výsledok ako asociatívne pole (alebo numerické pole).mysql_fetch_row(údaje)
parameter "údaje" je povinný a určuje údaje vybraté funkciou mysql_query. Teda výber uložený do premennej.
Napriklad :
$dopyt = mysql_query(SELECT * FROM osoby WHERE priezvisko LIKE "Lerdorf");
echo mysql_fetch_row($dopyt);
Prakticky má táto funkcia využitie keď je výsledkom výberu len jeden riadok.
mysql_num_rows();
Táto funkcia vracia počet záznamov, resp. riadkov vo výbere. Ak nie je vo výbere ani jeden riadok funkcia vráti FALSE.mysql_num_row(údaje);
parameter "údaje" je povinný a určuje údaje vybraté funkciou mysql_query. Teda výber uložený do premennej.
Napríklad :
$dopyt = mysql_query("SELECT * FROM autori");
echo mysql_num_rows($dopyt); //vypíše počet záznamov v tabuľke "autori"
Ostatné
mysql_num_fields(); - vracia počet položiek vo výbere (výber = dopyt pomocou mysql_query)mysql_error(); - vracia popis chyby predošlej SQL operácie
mysql_pconnect(); - otvára stále spojenie s db serverom
mysql_fetch_assoc(); - vracia výsledok SQL dopytu ako asociatívne pole.
Tento zoznam funkcií nie je kompletný !
Pre kompletný zoznam PHP funkcií pre prácu s MySQL , použite externý odkaz v angličtine na konci článku v sekcii "Odkazy".
Články
Predošlý článok : 10. Session
Nasledujúci článok :
Odkazy
Stiahnite si príklady vypracované v článku
Externý odkaz www.w3schools.com - Kompletný zoznam PHP funkcií pre prácu s MySQL
Komentáre
...