MySQL - Úvod

MySQL je open-source databázový systém založený na tabulkách. Řádky zastupují jednotlivé záznamy, ve sloupcích jsou pak obsaženy jednotlivé hodnoty. Tento způsob zápisu je ideální pro různé seznamy, ceníky, zkrátka jakákoli data zapisovatelná pomocí tabulky.

Velkou výhodou MySQL je rychlost a jednoduchost. Běží totiž, jako samostatný server a k práci s daty se většinou využívá skriptování na straně serveru (např. pomocí PHP). I proto našel velké využití právě na internetu.

Vyjma MySQL existují ještě jiné databázové systémy založené na SQL, například PostgreSQL, nebo MSSQL.

S MySQL se pracuje pomocí příkazů, které se zakončují středníkem. Základním prvkem MySQL je databáze. Ta obsahuje tabulky, které mají definovaný počet sloupců. Do řádků v tabulkách se pak ukládají samotná data. Ta mohou mít různý formát, který ale musí odpovídat parametrům daného sloupce.

MySQL - databáze a tabulky

Databáze

Vytvoření databáze

Základním prvkem MySQL je databáze a tvoří se příkazem CREATE DATABASE:

CREATE DATABASE nazev_databaze;

Například:

CREATE DATABASE knihy;

Odstranění databáze

Pro odstranění databáze včetně všech tabulek a dat, které obsahuje, použijeme příkaz DROP DATABASE:

DROP DATABASE nazev_databaze;

Například:

DROP DATABASE knihy;

Přejmenování databáze

A pokud budeme chtít databázi přejmenovat, použijeme příkaz RENAME DATABASE:

RENAME DATABASE stary_nazev novy_nazev;

Například:

RENAME DATABASE knihy publikace;

Tabulky

Vytvoření tabulky

Pro vytvoření tabulky slouží funkce CREATE TABLE:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] nazev_tabulky ( parametry_sloupcu ) [ parametry_tabulky ] ;

TEMPORARY, pokud je uvedeno, znamená, že se jedná jen o dočasnou tabulku.

IF NOT EXISTS, pokud je uvedeno, znamená, že se tabulka vytvoří jen tehdy, pokud tabulka se stejným názvem v databázi ještě nevyskytuje. Pokud bychom se pokusili vytvořit již existující tabulku a IF NOT EXISTS by nebylo uvedeno, vrátí MySQL chybu.

Parametry sloupců

Každá tabulka má pevně definované sloupce s určitými parametry. Všechna data, která budou v daném sloupci nahrána, musí těmto parametrům odpovídat. Pokud je v tabulce sloupců více, musí se jejich definice oddělovat čárkou (za poslední definicí se čárka nedělá). Syntaxe pro definování sloupce je následující:

nazev_sloupce typ_sloupce [dalsi parametry sloupce]

Nejčastější typy sloupce shrnuje následující tabulka:

U typů označených hvězdičkou * lze ještě bezprostředně za název typu vložit do kulatých závorek číslo, které udává maximální počet znaků, či cifer, jakých smí hodnota v tomto sloupci nabývat.

Názvy databází, tabulek a jejich sloupců nesmí obsahovat žádné mezery, ani speciální znaky vyjma pomlček a podtržítek. Název však musí začínat písmenem.

Například:

CREATE TABLE knihy ( cisloknihy INT, nazev VARCHAR(100), cena INT(6));

Odstranění tabulky

Pro odstranění tabulky včetně všech dat použijeme následující příkaz

DELETE nazev_tabulky;

Například:

DELETE knihy;

MySQL - vkládání dat

Každý datový záznam odpovídá jednomu řádku tabulky. Proto se právě po jednotlivých řádcích data vkládají, upravují i mažou.

Zápis dat

Nejjednodušší zápis dat se provádí pomocí příkazu INSERT. Možností zápisu je hned několik.

Zápis po jednotlivých sloupcích

Tento způsob zápisu vyžaduje znalost pořadí jednotlivých sloupců tabulky. Ve stejném pořadí se pak k jednotlivým sloupcům daného řádku vkládají data. V tomto případě můžeme pro příkaz použít následující šablonu:

INSERT INTO nazev_tabulky VALUES ('hodnota1', 'hodnota2', ... 'hodnota3');

Například:

INSERT INTO knihy VALUES ('Lucie Grusová', 'XML pro úplné začátečníky', 'Computer Press');

Zápis po jednotlivých sloupcích s určením jejich pořadí

Narozdíl od předchozího způsobu je tento o něco pružnější. Můžeme si u něj totiž určit, v jakém pořadí budeme jednotlivé sloupce vkládat, navíc nemusíme definovat úplně všechny. Šablona pro tuto vriantu vypadá takto:

INSERT INTO nazev_tabulky ('sloupec1', 'sloupec2', ... 'sloupec3') VALUES ('hodnota1', 'hodnota2', ... 'hodnota3');

V našem případě může zápis vypadat následovně:

INSERT INTO knihy (autor, nazev, vydal) VALUES ('Jiří Bráza', 'PHP4 - Učebnice základů jazyka', 'Grada');

INSERT INTO knihy (nazev, autor, vydal) VALUES ('PHP4 - Učebnice základů jazyka', 'Jiří Bráza', 'Grada');

INSERT INTO knihy (autor, nazev) VALUES ('Jiří Bráza', 'PHP4 - Učebnice základů jazyka');

Zápis přiřazením

Poslední forma zápisu se moc často nepoužívá, přesto si ji zmíníme. Spočívá v přiřazení hodnot jednotlivým sloupcům a její struktura se proto od předchozích dvou výrazně liší:

INSERT INTO nazev_tabulky SET sloupec1='hodnota1', sloupec2='hodnota2', ... sloupec3='hodnota3';

Například:

INSERT INTO nazev_tabulky SET autor='Lucie Grusová', nazev='XML pro úplné začátečníky', vydal='Computer Press';

MySQL - výpis dat

Pro výpis dat se používá funkce SELECT:

SELECT
[DISTINCT | ALL]
výběr_sloupců, ...
FROM název_tabulky
[WHERE definice_where]
[ASC | DESC], ...
[ORDER BY {název_sloupce} [ASC | DESC] ,...]
[LIMIT [integer]

Začneme jednoduchým příkazem, který vypíše všechna data z tabulky knihy:

SELECT * FROM knihy;

Tak vypíšeme celou tabulku v nezměněné podobě. Přestože je tento příkaz prakticky ze všech nejjednodušší, pravděpodobně ho použijeme nejméně. MySQL umí data podat v přesně takové podobě, kterou potřebujeme. K optimálnímu výpisu vede ještě několik parametrů, které se postupně naučíme upravovat. První z nich bude určení pořadí sloupců při výpisu:

SELECT nazev, autor FROM knihy;

Přestože může být u každé knihy nepřeberné množství informací, nás teď zajímá jen její název a autor, takže v příkazu SELECT vybereme jen tyto sloupce.

Definice WHERE

Výpis z tabulky můžeme také omezit jen na řádky, které splňují určité parametry, například můžeme vypsat jen knihy z určitého nakladatelství.

SELECT nazev, autor FROM knihy WHERE nakladatelstvi = 'Grada';

V definici WHERE můžeme používat i logické operátory AND a OR, tedy můžeme vypsat buď knihy od určitého nakladatelství od určitého autora.

SELECT nazev, autor FROM knihy WHERE nakladatelstvi = 'Grada' AND autor = 'Jiří Bráza';

V tomto případě jsme použili operátor AND, což znamená a. Místo něj můžeme ale použít i OR (= nebo).

SELECT nazev, autor FROM knihy WHERE nakladatelstvi = 'Grada' OR autor = 'Jiří Bráza';

Vypíší se tedy všechny knihy, které jsou buď od určitého nakladatelství, nebo od určitého autora.

Místo znaménka = můžeme použít v případě číselných polí i <, >, <=, >=. Pokud chceme říci, že se hodnota pole nesmí rovnat danému výrazu, můžeme použít <>, nebo !=.

Řazení výsledků pomocí ORDER BY

Výsledky můžeme seřadit pomocí ORDER BY, kde jako parametr uvedeme název sloupce a údaj, jestli se mají podle tohoto klíče řadit výsledky vzestupně (ASC), nebo sestupně (DESC).

SELECT nazev, autor FROM knihy ORDER BY autor ASC;

Uvedený příklad zobrazí seznam knih řazený abecedně podle jejich autorů.

Omezování výpisu LIMIT

Pokud chceme výpis omezit na určitý počet záznamů, použijeme LIMIT.

SELECT nazev, autor FROM knihy LIMIT 10;

Tento příkaz vypíše prvních 10 záznamů z tabulky. Pokud bychom je nechtěli vypisovat od začátku, ale např. od 20. místa, bude příkaz vypadat takto:

SELECT nazev, autor FROM knihy LIMIT 20, 10;

MySQL v PHP

Kombinaci PHP - MySQL dnes používá většina velkých obsáhlých webů. Důvody jsou celkem jasné - spolehlivost, výkonnost, rychlost, flexibilita. Práce s daty MySQL je v PHP jednoduchá a potřebujeme k ní znát jen několik základních funkcí.

Abychom vůbec mohli s databází pracovat, musíme se k ní nejprve připojit. K tomu slouží funkce mysql_connect(). Nejprve jí ale musíme zadat název serveru (adresu, IP), jméno uživatele, pod kterým se k databázi přihlásíme, a samozřejmě heslo. Celý příkaz vypadá jednoduše a ve skutečnosti to není o mnoho složitější:

mysql_connect('mysql.webzdarma.cz','gymnazium','1234');

Příkaz nás připojí k databázi na serveru webzdarma, jako uživatele gymnazium, který má heslo 1234.

Než začneme provádět MySQL příkazy, musíme zvolit databázi. To se provádí funkcí mysql_select_db(), přičemž jediný parametr je název naší databáze:

Kódování češtiny v MySQL

Z českého pohledu je jednou z nejvýznamnějších změn v MySQL 4.1 podpora různých kódových stránek, a to nejen na úrovni databázového serveru, ale i na úrovni jednotlivých databází, tabulek a sloupců. Syntaxe je poměrně jednoduchá, stačí za obvyklé příkazy dopsat COLLATE a odpovídající kódovou stránku spolu s výchozím tříděním:

databáze: CREATE DATABASE db_name COLLATE utf8_czech_ci

tabulka: CREATE TABLE table_name (…) COLLATE utf8_czech_ci

sloupec: jmeno varchar(50) COLLATE utf8_czech_ci

Kódování se nenastavuje pouze datům uloženým v databázi, ale i těm přenášeným. Pokud mají data v databázi jinou kódovou stránku než data předaná v dotazu, dojde k chybě kódování. Říkáte si, proč je výchozí kódování právě latin1_swedish_ci? No hádejte, odkud je MySQL…?

Kromě uložených dat lze tedy určit ještě kódování klienta (tedy v jakém kódování se data posílají) a přenášených a vracených dat. Všechna tři kódování lze nastavit příkazem SET CHARACTER SET. Pro optimální výkon je vhodné používat všude stejné kódování – na serveru, během přenosu i u klienta.

mysql_query("SET character_set_client=utf8");

mysql_query("SET character_set_connection=utf8");

mysql_query("SET character_set_results=utf8");

Výběr databáze

Než začneme provádět MySQL příkazy, musíme zvolit databázi. To se provádí funkcí mysql_select_db(), přičemž jediný parametr je název naší databáze:

mysql_select_db('knihovna');

Zpracování příkazu

Už jsme se připojili k serveru, dokonce jsme si i vybrali databázi - knihovna. Nyní už můžeme zadávat MySQL příkazy. K tomu slouží další funkce - mysql_query().

mysql_query('SELECT * FROM knihy');

Pokud příkaz provede nějakou akci - přidá záznam, upraví tabulku atd., a akce proběhne úspěšně, vrátí funkce mysql_query() true. Pokud ale získáváme data z databáze, musíme je ještě dále "rozpracovat". Je to proces, který se provádí krok za krokem, nejprve ale opravíme náš PHP příkaz, protože potřebujeme výsledek naší akce uložit do nějaké proměnné:

$vysledek = mysql_query('SELECT * FROM knihy');

Proměnná $vysledek nyní slouží, jako identifikátor výsledku našeho příkazu - tedy obsahu tabulky knihy. Pokud má tato proměnná hodnotu false, při provádění příkazu se vyskytla chyba. Pokud tento příkaz proběhl správně, obsahuje výsledek určitý počet řádků (i 0 řádků, pokud je tabulka prázdná), který zjistíme přes funkci mysql_num_rows(), kde jako parametr uvedeme identifikátor výsledku:

echo('Počet řádků výsledku je ' . mysql_num_rows($vysledek));

Tento počet řádků je velmi důležitý - hned si vysvětlíme, proč. Vrácená data musíme zpracovávat po jednom řádku, kde nám funkce mysql_fetch_array() uloží obsah aktuálního řádku do pole, kde jedna položka je jeden sloupec. Abychom přečetli všechny řádky použijeme cyklus while. Celý čtecí mechanismus pak může vypadat nějak takto:

$vysledek = mysql_query('SELECT * FROM knihy');
$row = mysql_fetch_array($vysledek);

echo "<table>";
while ($row) {
	     $autor = $row["autor"];
	     $nazev = $row["nazev"];
	     $nakladatelstvi = $row["nakladatelstvi"];
	     $vypujcen = $row["vypujcen"];
	
	     echo "<tr><td>".$autor."</td>
		<td>".$nazev."</td><td>".$nakladatelstvi."</td><td>".$vypujcen."</td></tr>";
	     $row = mysql_fetch_array($vysledek);	
	}

echo "</table">;

Vzorové řešení

Závěrem si ukážeme kompletní PHP kód, který zjistí některá data a vypíše je:

mysql_query("SET character_set_client=utf8");

mysql_query("SET character_set_connection=utf8");

mysql_query("SET character_set_results=utf8");

mysql_connect('mysql.webzdarma.cz','gymnazium','1234');
mysql_select_db('knihy');
$vysledek = mysql_query('SELECT * FROM knihy');

$row = mysql_fetch_array($vysledek);


echo "<table">;

while ($row) {
	     $autor = $row["autor"];
	     $nazev = $row["nazev"];
	     $nakladatelstvi = $row["nakladatelstvi"];
	     $vypujcen = $row["vypujcen"];

	     echo "<tr><td>".$autor."</td><td>".$nazev."</td>
		<td>".$nakladatelstvi."</td><td>".$vypujcen."</td></tr>";
	     $row = mysql_fetch_array($vysledek);	
	}

echo "</table">;