Hlavní navigace

Seznámení s SQL: MySQL

Petr Hodan

Pokračování seriálu o SQL se zvláštním přihlédnutím k MySQL.

Už od začátku tvorby databází bylo problémem rychle vyhledat žádanou položku v ohromném množství dat. Razantní zrychlení a zjednodušení přinesly klíče.

Co je tedy klíč? Představte si tabulku o třech sloupcích. Budou-li v prvním sloupci nenulové a vzájemně různé hodnoty, lze tento sloupec použít pro tvorbu klíče. Pokud žádný sloupec v tabulce tuto podmínku nesplňuje, je možno vytvořit složený klíč. Složený klíč se skládá ze dvou a více sloupců, v nichž kombinace hodnot je unikátní a nenulová. S pojmem klíčů neodvratně souvisí i pojem „x-tá normální forma tabulky“. V praxi se vyskytují tabulky ve třetí normální formě a vyšší.

Definice nulté normální formy zní: „Tabulka je v nulté normální formě právě tehdy, když existuje alespoň jedno pole, které obsahuje více než jednu hodnotu.“

Tabulka v nulté normální formě by mohla vypadat takto:

+----------------+--------------+---------------------------+
|     znacka     | max_rychlost |      dalsi_vlastnost      |
+----------------+--------------+---------------------------+
|   Audi A 8     |    235       | spolehlive, rychle, hezke |
| Skoda Octavia  |    180       | spolehlive, male, levne   |
+----------------+--------------+---------------------------+

Ve třetím sloupci je výčet vlastností. Dle definice je tedy tato tabulka v nulté normální formě. Existuje několik způsobů, kterým lze naší tabulku převést do vyšších normálních forem. Nejprve ale uveďme definici první normální formy: „Pokud tabulka není v nulté normální formě, pak je alespoň v první normální formě“. V praxi to znamená asi to, že je potřeba vyloučit více hodnot připadající na jedno pole tabulky. V našem případě by cesta mohla vést dvěma způsoby.

První způsob jistě pochopíte z následující upravené tabulky:

+----------------+------------------+---------------------+
|     znacka     |   max_rychlost   |      vlastnost      |
+----------------+------------------+---------------------+
|   Audi A 8     |      235         |       spolehlive    |
|   Audi A 8     |      235         |        rychle       |
|   Audi A 8     |      235         |        hezke        |
| Skoda Octavia  |      180         |      spolehlive     |
| Skoda Octavia  |      180         |         male        |
| Skoda Octavia  |      180         |        levne        |
+----------------+------------------+---------------------+

Tato tabulka je v první normální formě, protože jsme vyloučili více hodnot z jedné buňky tabulky jejich rozepsáním do více řádek.

Druhý způsob je logičtější, v praxi mnohem častěji používanější:

+----------------+------------------+----------+------+
|     znacka     |   max_rychlost   |spolehlive|rychle|
+----------------+------------------+----------+------+
|   Audi A 8     |      235         |   YES    | YES  |
| Skoda Octavia  |      180         |   YES    | NO   |
+----------------+------------------+----------+------+

-----+-----+----+
hezke|levne|male|
-----+-----+----+
 YES | NO  | NO |
 NULL| YES |YES |
-----+-----+----+

Tato tabulka je ve vyšší normální formě než první, dokonce není ani ve druhé ba ani ve třetí. Definice pro druhou normální formu tabulky zní: „Tabulka je ve druhé normální formě, jestliže je v první normální formě, zároveň existuje klíč a současně všechna neklíčová pole jsou funkcí klíče, a nikoliv jen jeho části“. Zní to krkolomně, ale pokud to budeme aplikovat na tabulku aut uvedenou výše, vidíme že klíčem je značka automobilu a ostatní údaje jsou jeho funkcí, či jinak, závisí a jsou různá pro různé značky.

A jak na to v praxi? Existují jisté možnosti, jimiž lze ovlivňovat hodnoty uvedené v polích tabulky. Pokud máme již ve stádiu návrhu tabulky jasno (a to by mělo být), můžeme jistá omezení dát tabulce už do kolébky. Založme nyní tabulku lidé, v níž budeme soustředit všechny možné i nemožné údaje o lidech:

create table lide (rc char(11) not null unique, prijmeni char(25) not null, jmeno char(20) not null, pohlavi char(1) not null, telefon char(20));

Jak je vidět, definovali jsme tabulku o pěti sloupcích. V prvním sloupci bude uvedeno rodné číslo. Jeho typ jsme definovali jako char o velikosti 11 a přidali jsme NOT NULL UNIQUE. To říká, že rc (rodné číslo) nesmí být nulové a musí být unikátní v celém souboru rodných čísle uvedených v tabulce. Převedeno do praxe Vám SQL server nepovolí vložit člověka s duplicitním nebo nulovým rodným číslem. Stejně tak je tomu i u příjmení, jména a pohlaví až na unikátnost. Josefů Nováků zajisté existuje víc. Zkuste se podívat do telefonního seznamu.

Budeme-li chtít, aby rodné číslo tvořilo klíč k přístupu do naší tabulky, vytvoříme si index příkazem:

create unique index rc_index on lide(rc);

syntaxe příkazu je CREATE [UNIQUE] INDEX jmeno_indexu ON jmeno_tabulky(slou­pec). Nyní je zaručeno, že se nepovede vložit člověka s duplicitním rodným číslem. I vyhledávání v takto upravené tabulce podle rodného čísla bude rychlejší, vděčíme za to implementovaným hashovacím funkcím. Malá poznámečka by se ještě hodila. Pokud budete chtít smazat tabulku, nad níž byl vytvořen index (klíč), musíte postupovat opačně. Znamená to že nejdříve smazat klíč a pak tabulku:

alter table lide drop index rc_index;

( Toto funguje pod MySQL, pro jiné servery dle normy by mělo vypadat takto:
„DROP INDEX rc_index;“ )

V aplikaci SQL jazyka systémem MySQL se lze ještě setkat s primárním klíčem (PRIMARY KEY). Jak praví manuál, primární klíč je unikátní klíč, jehož všechny sloupce musejí být definovány jako nenulové (NOT NULL) a jako každý klíč může být složen i z více sloupců. Primární klíč může být v jedné tabulce jen jeden. Jeho aplikace na naší tabulku vypadá asi takto (jen pro MySQL):

alter table lide add primary key rc_key(rc);

Jeho odstranění provedeme rovněž příkazem alter:

alter table lide drop primary key;

Příkaz alter modifikuje strukturu tabulky, umožňuje přidávat sloupce, měnit jejich názvy, datové typy nesoucí informace, přejmenovávat tabulku a přidávat klíče. Jeho základní syntax je:

ALTER TABLE nazev_tabulky akce parametry;

Jako akce se používá ADD COLUMN pro přidávání sloupců, DROP COLUMN pro jejich mazání, ADD INDEX a DROP INDEX pro přidávání respektive mazání klíčů atd. Vše si ukážeme na příkladech. Mějme v naší tabulce lidé uvedeny dva kontakty:

+-------------+----------+-------+---------+-------------+
| rc          | prijmeni | jmeno | pohlavi | telefon     |
+-------------+----------+-------+---------+-------------+
| 790303/0041 | Vonásek  | Alois | m       | 0611-585555 |
| 250808/0087 | Dlabačka | Karel | m       | 02-88888888 |
+-------------+----------+-------+---------+-------------+

Každý člověk pravděpodobně někde bydlí, přidejme nyní sloupec ulice, město a PSČ. Ulice může být nenulová, protože na malých vesnicích většinou nemají ulice názvy.

alter table lide add column ulice char(30);

+-------------+----------+-------+---------+
| rc          | prijmeni | jmeno | pohlavi |
+-------------+----------+-------+---------+
| 790303/0041 | Vonásek  | Alois | m       |
| 250808/0087 | Dlabačka | Karel | m       |
+-------------+----------+-------+---------+

-------------+-------+
 telefon     | ulice |
-------------+-------+
 0611-585555 | NULL  |
 02-88888888 | NULL  |
-------------+-------+

Takto vypadá rozšířená tabulka o ulici. Hodnoty jsou implicitně nastaveny na NULL. Nyní přidáme ještě sloupce město a PSČ.

alter table lide add column mesto char(30) not null;
alter table lide add column psc char(5) not null;

+-------------+----------+-------+---------+
| rc          | prijmeni | jmeno | pohlavi |
+-------------+----------+-------+---------+
| 790303/0041 | Vonásek  | Alois | m       |
| 250808/0087 | Dlabačka | Karel | m       |
+-------------+----------+-------+---------+

-------------+-------+-------+-----+
 telefon     | ulice | mesto | psc |
-------------+-------+-------+-----+
 0611-585555 | NULL  |       |     |
 02-88888888 | NULL  |       |     |
-------------+-------+-------+-----+

Takto vypadá výsledek. Podmínka uvádí pro sloupce mesto a psc NOT NULL, takže implicitně je tam "" neboli prázdno. Přidejme Aloisi Vonáskovi bydliště, a to Růžová 15, Praha 1, 110 00 a Karlu Dlabačkovi bydliště Kolínská 1, Praha 8, 180 00.

update lide set ulice=„Ružová 15“,mesto=„Praha 1“,psc=„11000“ where rc=„790303/0041“;
update lide set ulice=„Kolínská 1“,mesto=„Praha 8“,psc=„18000“ where rc=„250808/0087“;

+-------------+----------+-------+---------+
| rc          | prijmeni | jmeno | pohlavi |
+-------------+----------+-------+---------+
| 790303/0041 | Vonásek  | Alois | m       |
| 250808/0087 | Dlabačka | Karel | m       |
+-------------+----------+-------+---------+

-------------+------------+---------+-------+
 telefon     | ulice      | mesto   | psc   |
-------------+------------+---------+-------+
 0611-585555 | Ružová 15  | Praha 1 | 11000 |
 02-88888888 | Kolínská 1 | Praha 8 | 18000 |
-------------+------------+---------+-------+

A takto vypadá výsledek. K aktualizaci dat v tabulkách se používá příkaz UPDATE. Syntax je: UPDATE tabulka SET co WHERE podmínka. Jako parametr u WHERE se často používá sloupec, nad nímž je nějaký klíč, protože pak celá operace trvá podstatně kratší dobu než kdyby se hledalo podle sloupce, který by nebyl zahrnut do klíče.

Zvídavější čtenáře zajisté napadne, že poslední tabulka by se dala zjednodušit. Ano, je to tak. Zpravidla každému městu připadá jiné poštovní směrovací číslo. Sloupec s PSČ by tedy šel vypustit. Mnozí z Vás si zajisté v praxi všimli, že soused bydlící naproti v domě má jiné PSČ. Určitě musí existovat unikátní dvojice ULICE – MĚSTO, která jednoznačně určuje PSČ. A to je právě jádro problému. Rozklad jedné obsáhlé tabulky na více jednoduchých tabulek, kterým se říká „číselníky“. Tyto číselníky jsou mezi sebou svázány hlavní tabulkou. Výhodou je snadná aktualizovatelnost a snadnost údržby a v neposlední řadě také přehlednost pro jiné vývojáře.

Našli jste v článku chybu?

13. 7. 2007 0:14

Prosim Vas mam problem sa pripojit na moju mysql databazu na linuxoch z ineho PC (win-aplikacia C#-mam mysql.dll). Presiel som navody a nastavil to podla nich nasledovne, hlavne databazu mysql
->

mam databazu bedrock a uzivatela kuba ktory ma pravo select na bedrock a localhost je '%'
dalej som nastavil host kde mam tiez povolene prava na bedrock a host je nastaveny na '%'

string na pripojenie -> network address=kubo;initial catalog='bedrock'; persist security inf…





21. 6. 2007 8:30

Mintaka (neregistrovaný)
V tabulce kde je udaj "Audi A 8 " není dle definice úplně v první normální formě, protože údaj obsahuje informaci o výrobci a o modelu auta.

V praxi je ale spíš důležité, jak se s danými daty bude pracovat.
Datum 23.4.2000 se také většinou nerozděluje na dny měsíce a roky.


Lupa.cz: Levný tarif pro Brno nebude, je to kartel

Levný tarif pro Brno nebude, je to kartel

Vitalia.cz: 7 originálních adventních kalendářů pro mlsné

7 originálních adventních kalendářů pro mlsné

DigiZone.cz: R2B2 a Hybrid uzavřely partnerství

R2B2 a Hybrid uzavřely partnerství

Root.cz: Nová třída SD karet A1 s vysokým výkonem

Nová třída SD karet A1 s vysokým výkonem

DigiZone.cz: SES zajistí HD pro M7 Group

SES zajistí HD pro M7 Group

Root.cz: Certifikáty zadarmo jsou horší než za peníze?

Certifikáty zadarmo jsou horší než za peníze?

120na80.cz: Co všechno ovlivňuje ženskou plodnost?

Co všechno ovlivňuje ženskou plodnost?

120na80.cz: Horní cesty dýchací. Zkuste fytofarmaka

Horní cesty dýchací. Zkuste fytofarmaka

Měšec.cz: Exekuční poradna: ptejte se online

Exekuční poradna: ptejte se online

Podnikatel.cz: Chtějte údaje k dani z nemovitostí do mailu

Chtějte údaje k dani z nemovitostí do mailu

DigiZone.cz: V Plzni odstartovalo Radio 1

V Plzni odstartovalo Radio 1

Lupa.cz: Kdo pochopí vtip, může jít do ČT vyvíjet weby

Kdo pochopí vtip, může jít do ČT vyvíjet weby

Podnikatel.cz: Vládu obejde, kvůli EET rovnou do sněmovny

Vládu obejde, kvůli EET rovnou do sněmovny

Měšec.cz: Jak vymáhat výživné zadarmo?

Jak vymáhat výživné zadarmo?

Lupa.cz: Obchod budoucnosti je bez front, košíků i pokladen

Obchod budoucnosti je bez front, košíků i pokladen

Vitalia.cz: Nejlepší obranou při nachlazení je útok

Nejlepší obranou při nachlazení je útok

Lupa.cz: Co se dá měřit přes Internet věcí

Co se dá měřit přes Internet věcí

DigiZone.cz: Česká televize mění schéma ČT :D

Česká televize mění schéma ČT :D

Vitalia.cz: Proč vás každý zubař posílá na dentální hygienu

Proč vás každý zubař posílá na dentální hygienu

Vitalia.cz: Pamlsková vyhláška bude platit jen na základkách

Pamlsková vyhláška bude platit jen na základkách