Vlog


Sleduj ma


Twitter


Základy práce z databázou MySQL

Spojenie databázy MySQL a skriptovacieho jazyka PHP je veľmi silný nástroj pre tvorbu dynamických webových aplikácií. MySQL nájdeme na každom hostingu a freehostingu. PHP komunikuje z databázou pomocou SQL jazyka, čo je skratka pre Structured Query Language.

MySQL sa nemusí striktne využívať len v kombinácii z PHP, ale nájde uplatnenie aj pri programoch ktoré z databázou spolupracujú. S touto open source databázou dokáže spolupracovať aj napríklad výborný kancelársky balík OpenOffice.org. No a já sa teraz pokúsim zhrnúť základné príkazy na prácu s MySQL. Skúsený používateľ tu asi nič nové a prevratné nenájde, avšak začiatočníkom môžu tento návod celkom dobre poslúžiť. Ešte upozorňujem, že toto nieje návrh ako MySQL server nainštalovať, ako spustiť démona mysqld a podobne.

Presne naopak, venujeme sa klientovi mysql. Systém mi beží na os WindowsXP. MySQL môžete samozrejme používať aj na všetkých ostatných systémoch ako je Linux, Unix, Solaris, MAC OS a pod.

Návrh

MySQL je relačným databázovým systémom, preto sa skladá z databáz, tie obsahujú tabuľky no a v tabuľkách sú v stĺpcoch uložene jednotlivé dáta. Preto pri návrhu tabuľky musíme dbať na jej správny návrh. Do stĺpcov tabuľky môžeme ukladať:

My si teraz vytvoríme fiktívnu tabuľku napríklad z adresárom a informáciami o užívateľoch pre náš elektronický obchod.

+------------------------+-----------------------+
| stĺpec                 | príklad vložených dát |
+------------------------+-----------------------+
| id_uzivatela           | 2567                  |
| nick                   | alian                 |
| meno                   | Fero                  |
| priezvisko             | Volar                 |
| adresa                 | Slovakia, 900 61      |
| email                  | fero@volar.info      |
| heslo                  | prisnetajne           |
| datum_zaregistrovania  | 2005-03-14 13:15      |
+------------------------+-----------------------+

Teraz si dokážeme predstaviť, ktorý stĺpec bude bude obsahovať text (napr. nick), ktorý číslo (napr. id_uzivatela) a ktorý dátum a čas (datum_zaregistrovania). Podľa toho už vieme určiť dátový typ stĺpca. Spomeniem popis len tých najdôležitejších: text:

čísla:

dátum a čas: DATE - dátum vo formáte YYYY-MM-DD DATETIM - dátum a čas vo formáte YYYY-MM-DD HH:MM:SS TIMESTAMP - dátum a čas vo formáte YYYYMMDDHHMMSS Teraz môžeme jednotlivým stĺpcom prideliť ich dátové typy napríklad takto:

+------------------------+------------------------------+
| stĺpec                 | dátový typ                   |
+------------------------+------------------------------+
| id_uzivatela           | MEDIUMINT UNSIGNET NOT NULL  |
| nick                   | VARCHAR (15) NOT NULL        |
| meno                   | VARCHAR (15) NOT NULL        |
| priezvisko             | VARCHAR (30) NOT NULL        |
| adresa                 | VARCHAR (40)                 |
| email                  | VARCHAR (40)                 |
| heslo                  | CHAR (10) NOT NULL           |
| datum_zaregistrovania  | DATETIME NOT NULL            |
+------------------------+------------------------------+

Samozrejme mať pri návrhu treba na pamäti, že ak napríklad do dátového typu VARCHAR (40) vložíme 50 znakov posledných 10 bude odrezaných. Okrem už spomínaných dátových typoch sú za nimi takzvané indexy a kľúče (napr. NOT NULL). Tie nás momentálne nezajímajú.

Klient mysql (alebo MySQL monitor)

To bola teória, teraz troška viacej prakticky. Z démonom mysqld môžeme komunikovať napríklad cez výborné PHP rozhranie phpMyAdmin, ktorému môžeme taktiež zadávať SQL príkazy (výborne sa ovláda myšou). My sa však chceme naučiť nejaké tie SQL príkazy, preto budeme používať klienta mysql dodávaného priamo zo systémom MySQL. Klient sa najčastejšie nachádza v adresári mysql/bin teda tam, kde sme systém nainštalovali. Spúšťame ho z terminálu - v mojom prípade z konzoly cmd.exe takto:

Crogram FilesPHP Home Edition 2mysqlbin>mysql.exe -u root -p
Enter password: **********
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 46 to server version: 4.0.15-nt

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql>

Tu vidíme že mám nastavenú absolútnu cestu ku klientovi Crogram FilesPHP Home Edition 2mysqlbin>mysql.exe, prihlasujem sa ako užívateľ root - mysql.exe -u root. Potom nasleduje parameter - p pre zadanie hesla. Samozrejme vaše užívateľské meno a heslo bude iné, záleží od nastavenia MySQL. Užívateľ musí mať nastavené práva na vytváranie databáz a tabuliek.

mysql> use teest;
ERROR 1049: Unknown database 'teest'
mysql> use test;
Database changed
mysql> quit
Bye

Crogram FilesPHP Home Edition 2mysqlbin>

V tomto príklade sme sa pokúsili vybrať databázu teest príkazom use teest;. Lenže systém databázu teest neobsahuje, preto sme na to boli upozornený. ( Ešte som nespomenul, že každý príkaz jazyka SQL ukončujeme bodkočiarkou ;). Kedže databaza teest nie je vyberieme inú, tento raz už zo správnym názvom test. Táto databáza sa vytvorí pri väčšine inštalácii automaticky a slúži práve na spomenuté testovanie. O úspešnom vybratí databázy test, s ktorou môžeme ďalej pracovať sme upozornený: Database changed. Potom som zadal príkaz quit, ale môžeme použiť aj exit. Za týmyto príkazmi bodkočiarku písať nemusíme, tak isto ako aj napríklad za príkazom help, ktorý zobrazí nápovedu.

Tvorba databáz a tabuliek

Databázy aj tabuľky sa tvoria príkazom CREATE. Novú databázu vytvoríme príkazom CREATE DATABASE nazovdatabazy; no a novú tabuľku obdobne príkazom CREATE TABLE nazovtabulky;. Prakticky to vyzerá asi takto:

mysql> CREATE DATABASE mojadb;
Query OK, 1 row affected (0.11 sec)

mysql> USE mojadb;
Database changed

mysql> CREATE TABLE zakaznici (
    -> id_uzivatela MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> nick VARCHAR(15) NOT NULL,
    -> meno VARCHAR(15) NOT NULL,
    -> priezvisko VARCHAR(30) NOT NULL,
    -> adresa VARCHAR(40),
    -> email VARCHAR(40),
    -> heslo CHAR(10),
    -> datum_zaregistrovania DATETIME NOT NULL,
    -> PRIMARY KEY (id_uzivatela)
    -> );
Query OK, 0 rows affected (0.12 sec)

mysql>

Tak čo sme to urobili? Myslím, že je to celkom jasné: vytvorili sme databázu (mojadb), nastavili sa do nej, v nej vytvorili tabuľku (zakaznici) aj z požadovanými stĺpcami. Ešte môžeme overiť pre nás vizuálne, či sa vytvorila požadovaná tabuľka:

mysql> SHOW TABLES;
+------------------+
| Tables_in_mojadb |
+------------------+
| zakaznici        |
+------------------+
1 row in set (0.08 sec)

Príkaz SHOW TABLES; zobrazí všetky tabuľky v databáze mojadb. Ak chceme vidieť všetky stĺpce tabuľky zákazníci zadáme príkaz SHOW COLUMNS FROM zakaznici;

Vkladanie dát

Všeko máme pripravené na naplnenie tabuľky dátami, preto si teraz ukážeme vloženie prvých, v našom prípade fiktívných, údajov. Na vkladanie slúži príkaz INSERT:

mysql> INSERT INTO zakaznici (nick, meno, priezvisko, adresa, email,
heslo, datum_zaregistrovania)
    -> VALUES ('alian','Fero','Volar','Slovakia','fero@volar.info',
    'prisnetajne',NOW());
Query OK, 1 row affected (0.04 sec)

Týmto spôsobom som pridal prvé dáta do do tabuľky. Toto je ale len jeden (podla mňa ten jednoduchší) spôsob vkladania dát. Tie nemusia byť vkladané vždy do všetkých stĺpcov, bezpodmienečne však do tých ktoré obsahujú index NULL. Časť INTO z príkazu INSERT INTO je nových systémoch MySQL nepovinná. Povkladajte si zopár dát do tabuľky, nech si ďalej môžeme ukázať prácu z výberom dát. (Ja som sa inšpiroval marcovým DigiRevue...)

Výber dát

Na výber dát používame príkaz SELECT, potom je syntax nasledovná SELECT stlpce FROM tabulka. Inak to že príkazy píšem veľkými písmenami neznamená, že tak musia byť napísané - uľahčuje to potom orientáciu, že čo je príkaz a čo parameter, či atribút. Ten najjednosuchší výber vyzará takto: SELECT * FROM zakaznici; a zabezpečí vybratie všetkých (*) stĺpcov z taluľky zakaznici. Ak chceme prezerať napríklad len stĺpce priezvisko a email zadáme príkaz SELECT priezvisko,email FROM zakaznici;. Výstup bude vyzerať takto:

mysql> SELECT priezvisko,email FROM zakaznici;
+------------+-----------------------+
| priezvisko | email                 |
+------------+-----------------------+
| Volar      | fero@volar.info       |
| Simpson    | bart@simpson.com      |
| Simpson    | homer@simpson.com     |
| Drobny     | martin@drobny.sk      |
| Drobny     | martin@drobny.sk      |
| Bezak      | branislav@bezak.sk    |
| Moynahan   | bridgget@moynahan.com |
| Smith      | will@smith.com        |
| Mocarthy   | anne@smith.com        |
| Scully     | jay@xfiles.com        |
| Berenger   | tom@berenger.com      |
| Mann       | byron@mann.com        |
| Willis     | bruce@willis.com      |
+------------+-----------------------+
13 rows in set (0.00 sec)

Je taktiež dobré vedieť, že aké poradie stĺpcou zadáme na vstupe také dostaneme na výstupe. SELECT zvládne vracať ten istý stĺpec viackrát, to sa hodi pri pokročilejších technikách manipulácie z dátmi.

Triedenie dát

Jednoduchý výber dát príkazom SELECT však vracia vo výstupoch celé stĺpce, čo ak chceme napríklad zobraziť emaily , ktoré za majú ako meno zákazníkov uvedené ako Bruce?

mysql> SELECT email FROM zakaznici WHERE meno='Bruce';
+------------------+
| email            |
+------------------+
| bruce@willis.com |
+------------------+
1 row in set (0.00 sec)

Jednoduché však. No a čo tak prezývky zákazníkov ktorý sa zaregistrovali napríklad za jeden určitý deň?

mysql> SELECT nick FROM zakaznici WHERE (datum_zaregistrovania >
'2005-03-16 00:00:00') AND (datum_zaregistrovania <
'2005-03-17 00:00:00');
+---------+
| nick    |
+---------+
| mdrobny |
| mdrobny |
| bbezak  |
| briget  |
| willy   |
| anne    |
| jaaay   |
| tommy   |
| baroon  |
| bruce   |
+---------+
10 rows in set (0.01 sec)

Pri výbere dát sú taktiež znaky takzvaného hromadného výberu. Znak _ nahrádza práve jeden akýkoľvek znak a znak percenta % skupinu znakov. Pre ich použitie existujú operátory LIKE a NOT LIKE. Ukážka použitia:

mysql> SELECT priezvisko, meno FROM zakaznici WHERE 
email LIKE '%@%.com';
+------------+---------+
| priezvisko | meno    |
+------------+---------+
| Simpson    | Bart    |
| Simpson    | Homer   |
| Moynahan   | Brigget |
| Smith      | Will    |
| Mocarthy   | Anne    |
| Scully     | Jay     |
| Berenger   | Tom     |
| Mann       | Byron   |
| Willis     | Bruce   |
+------------+---------+
9 rows in set (0.05 sec)

Príkazom sme vybrali všetkých zákazníkov z tabuľky zákazníci, ktorých email má doménu .com a vypísali ich priezvisko a meno. Už vieme požadované informácie z tabuľky vydolovať, ešte si ukážeme ako ich usporiadať. K tomuto účelu máme príkaz ORDER BY .

mysql> SELECT priezvisko, meno FROM zakaznici ORDER BY 
priezvisko;
+------------+-----------+
| priezvisko | meno      |
+------------+-----------+
| Berenger   | Tom       |
| Bezak      | Branislav |
| Drobny     | Martin    |
| Drobny     | Martin    |
| Mann       | Byron     |
| Mocarthy   | Anne      |
| Moynahan   | Brigget   |
| Scully     | Jay       |
| Simpson    | Bart      |
| Simpson    | Homer     |
| Smith      | Will      |
| Volar      | Fero      |
| Willis     | Bruce     |
+------------+-----------+
13 rows in set (0.00 sec)

Na príklade vidíme príkaz SELECT priezvisko, meno FROM zakaznici ORDER BY priezvisko;. Jeho úlohou je na výstupe zobraziť najprv priezvisko a potom meno, pričom priezviská budú zobrazené vzostupne. Taký istý výsledok by sme dosiahli aj príkazom SELECT priezvisko, meno FROM zakaznici ORDER BY priezvisko ASC;, kde sme pridali len operátor ASC (ascending). Ten však štandardne písať nemusíme. Opakom je operátor DESC (descending), ktorý radí výstup zostupne. Vďaka tomu môžeme výsledky zoradiť podľa viacerých stĺpcou. V praxi tiež pomerne často využívame obmedzenie počtu záznamov na výstupe. Napríklad pri zobrazení posledných 10 komentárov, prípadne pri stránkovaní väčšieho počtu článkov na stránke. Počet výstupných záznamov obmedzujeme príkazom LIMIT. Teraz si to ukážeme na príklade:

mysql> SELECT priezvisko,datum_zaregistrovania FROM 
zakaznici ORDER BY datum_zaregistrovania DESC LIMIT 3;
+------------+-----------------------+
| priezvisko | datum_zaregistrovania |
+------------+-----------------------+
| Willis     | 2005-03-16 09:32:24   |
| Mann       | 2005-03-16 09:31:38   |
| Berenger   | 2005-03-16 09:30:46   |
+------------+-----------------------+
3 rows in set (0.00 sec)

Na výstupe vidíme priezviská posledných troch zaregistrovaných zákazníkov. Ak by sme chceli zobraziť ďalších troch v poradí uprvíme príkaz takto: SELECT priezvisko,datum_zaregistrovania FROM zakaznici ORDER BY datum_zaregistrovania DESC LIMIT 2,3;

mysql> SELECT priezvisko,datum_zaregistrovania 
FROM zakaznici ORDER BY datum_zaregistrovania DESC 
LIMIT 3,3;
+------------+-----------------------+
| priezvisko | datum_zaregistrovania |
+------------+-----------------------+
| Scully     | 2005-03-16 09:29:21   |
| Mocarthy   | 2005-03-16 09:28:33   |
| Smith      | 2005-03-16 09:26:13   |
+------------+-----------------------+
3 rows in set (0.00 sec)

LIMIT 3,3 zanemená že budú vypísané tri položky, ktoré nasledujú za treťou položkou od začiatku. Pre overenie správneho poradia a nadväznosti si môžeme zobraziť posledných šesť položiek:

mysql> SELECT priezvisko,datum_zaregistrovania 
FROM zakaznici ORDER BY datum_zaregistrovania DESC 
LIMIT 6;
+------------+-----------------------+
| priezvisko | datum_zaregistrovania |
+------------+-----------------------+
| Willis     | 2005-03-16 09:32:24   |
| Mann       | 2005-03-16 09:31:38   |
| Berenger   | 2005-03-16 09:30:46   |
| Scully     | 2005-03-16 09:29:21   |
| Mocarthy   | 2005-03-16 09:28:33   |
| Smith      | 2005-03-16 09:26:13   |
+------------+-----------------------+
6 rows in set (0.00 sec)

Aktualizácia dát

Dáta ktoré sa zadávajú do tabuliek databázy potrebujeme tiež aktualizovať. Najprv doporučujem položku (položky), ktoré ideme aktualizovať vybrať pomocou príkazu SELECT a až potom aktualizovať pomocou príkazu UPDATE. Syntax vyzerá nasledovne: UPDATE tabulka SET stlpec='hodnota'. Príklad:

mysql> SELECT nick FROM zakaznici WHERE 
id_uzivatela='10';
+-------+
| nick  |
+-------+
| jaaay |
+-------+
1 row in set (0.01 sec)

mysql> UPDATE zakaznici SET nick='jay' 
WHERE id_uzivatela='10';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT nick FROM zakaznici 
WHERE id_uzivatela='10';
+------+
| nick |
+------+
| jay  |
+------+
1 row in set (0.00 sec) 

Z príkladu vidíme že nick uživateľa jaay ktorého id je 10 sme zmenili na jay.

Odstránenie dát

Poslednú operáciu ktorú si za dátami ukážeme je ich odstránenie. Syntax príkazu je nasledovná DELETE FROM tabulka WHERE stlpec='hodnota'. Pre odstránenie tabuľky použijeme príkaz DROP TABLE tabulka a pre databázu DROP DATABASE nazovdatabazy. Ukázali sme si, že pomocou pár príkazov sa dá pomerne efektívne pracovať z databázou MySQL. Samozrejme ich výpočet a ukážka ich použitia v tomto tutoriály je naozaj minimálna. Všetkým, ktorý to myslia z MySQL a jazykom SQL vážne doporučujem poobzerať sa po nejakej kvalitnej publikácii ale hlavne študovať manuálové stránky projektu MySQL, ktoré sú ako inak než zadarmo k dispozícii.


Mohli by vás zaujímať aj tieto články

Weekly #15

Technológie Aurora Serverless MySQL Generally Available Ubuntu, Debian, RHEL, and CentOS Linux Now Patched Against "Foreshadow" Attacks Prac

Konečne funkčný open-source balík pre vlastný mailový server

Myslím, že najmä v roku 2003, keď Microsoft uvoľnil Windows Small Business Server s rovnakým rokom v názve bol najväčší boom alternatívnych riešení po

Froxlor je jednoduchý panel na správu web servera

Webu stále dominujú LAMP aplikácie, takže má stále zmysel v tomto segmente vyvíjať administračné rozhrania pre servre. Dnes by som rád v skratke preds

Publikované 18.03.2005
TOPlist TOPlist