6.15. phpMyAdmin - relace, dotazy

6.15.1. Opakování

• XAMPP - www server Apache (IP 127.0.0.1 alias localhost) včetně podpory PHP, MySQL databázový server - spuštění přes XAMPP Control panel
  • Start/Stop MySQL
  • Admin - správa MySQL přes www - phpMyAdmin

• phpMyAdmin - http://localhost/phpmyadmin/
  • úrovně MySQL, databáze, tabulka
  • vytvoření databáze, tabulky - typy dat - celé číslo, reálné číslo, textové řetězce, blob, datum a čas, enum (výčet předem nastavených hodnot), set (podobně jako enum, ale do jednoho pole lze uložit více hodnot)
  • vložení dat

6.15.2 Vytvoření databáze a mateřských tabulek

• Úkolem je vytvořit databázi pro evidenci výpůjček čtenářů
• potřebné tabulky: mateřské tabulky ctenari a knihy, tabulka vypujcky s odkazy

4	Vytvořte databázi jprijmeni_knihovna (dosaďte své jméno a příjmení) se znakovou sadou utf8_czech_ci	Home - zál. Databáze
5	Zvolte tuto databázi jako aktuální a vytvořte v ní tabulku ctenari s poli podle schématu níže (volte úložiště InnoDB)	zál. Struktura
6	Vložte do tabulky minimálně 3 čtenáře s vymyšlenými hodnotami (vložte i fotky) a ověřte výpisem tabulky správnost hodnot	zál. Vložit, Projít
7	Vytvořte tabulku knihy s poli podle schématu níže (volte úložiště InnoDB)	zál. Struktura
8	Editujte soubor c:\temp\mysql03\knihovna_mysql.sql a ověřte správnost příkazů	PSPad
9	Importujte tyto data do databáze knihovna a ověřte výpisem tabulky správnost importu	phpMyAdmin - zál. Import

6.15.3. Svazování tabulek - relace, referenční integrita

Teorie relací 1:N, N:N, referenční integrity probrána v 1. pololetí - viz kapitola relace

• úkolem v knihovně je evidovat výpůjčky s možností evidence již vrácených výpůjček - je potřeba použít relaci N:N podle následujícího schématu:

• v tabulce vypujcky jsou pole id_knihy a id_ctenar tzv. cizí klíče (foreign keys) - odkazují na řádky (primární klíče) do mateřských tabulek - klíče musí být stejného typu (např. int(11)) a musí existovat (odkazované pole musí být primární klíč nebo index)

10	Vytvořte tabulku vypujcky s poli podle schématu níže (volte úložiště InnoDB)	zál. Struktura

• definice relací - je možno použít grafický Návrhář (v menu na úrovni databáze) nebo ve struktuře tabulky odkaz Zobrazit relace
• referenční integrita (omezení cizího klíče) - pokud se má smazat čtenář ("rodič") s výpůjčkami, je třeba rozhodnout, co se stane s odkazovanou výpůjčkou - "dítětem" (podobně u knihy, která má výpůjčku) - při návrhu relace se nastaví co s dítětem při mazání (updatu) rodiče:
  • ON DELETE CASCADE - smazat čtenáře i s výpůjčkou (resp. všechny svázané výpůjčky)
  • ON DELETE RESTRICT - nelze smazat čtenáře s výpůjčkou - zobrazí se chybové hlášení
  • ON DELETE SET NULL - smazat čtenáře a u výpůjčky nastavit místo odkazů NULL
  • ON DELETE NO ACTION - u výpůjčky se nechá chybný odkaz
• podobný význam má ON UPDATE při změnách svazovaných hodnot
• hlídání referenční integrity se zapíná proměnnou foreign_key_checks
  • nutno zkontrolovat její hodnotu SHOW VARIABLES LIKE 'foreign_key_checks';
  • nastavení kontroly je možné příkazem SET foreign_key_checks=1;

11	Ověřte zapnutí hlídání referenční integrity příkazem: show variables like 'foreign_key_checks';	zál. SQL
12	Přejděte do grafického Návrháře (menu na úrovni databáze) a uspořádejte si tabulky zleva doprava - knihy - výpůjčky - čtenáři	zál. Návrhář
13	Zapněte ikonu Vytvořit relaci a vyberte odkazovaný klíč knihy.id_knihy a pak cizí klíč vypujcky.id_knihy.  Typ relací vyberte ON DELETE RESTRICT, ON UPDATE CASCADE. Relace se graficky zobrazí (viz obr. níže). Podobně vytvořte relaci na čtenáře.
14	Proveďte ověření relací i v režimu struktury tabulky vypujcky - odkaz Zobrazit relace	zál. Struktura
15	Evidejte alespoň 3 výpůjčky knih vybraným čtenářům (předtím si vyhledejte příslušná id knihy a čtenáře) - záložka Vložit v rámci tabulky vypujcky	zál. Vložit
16	Projděte si tabulku vypujcky, jestli se řádky vytvořily správně. Vyzkoušejte smazání čtenáře s výpůjčkou (referenční integrita by tomu měla zabránit).	zál. Projít

6.15.4. Dotazy

Projít (na úrovni tabulky)

• k procházení tabulek po řádcích slouží záložka Projít (browse) - select from knihy limit 0, 30; - výpis lze stránkovat nebo přejít na začátek/konec, lze označit řádky a dále s nimi pracovat, lze zobrazit jen určité sloupce
• pro nastavení řazení se klikne na záhlaví sloupce select from knihy order by autor limit 0, 30;
• další kliknutí na stejném záhlaví sloupce obrací pořadí řazení (sestupně/vzestupně - DESC/ASC)
• SQL dotaz lze upravit, přidat do oblíbených

Vyhledávání (na úrovni tabulky)

• pro hledání se zadáním podmínky pomocí formuláře slouží záložka Vyhledávání (search, seek, find)
• u operátoru like lze použít zástupný znak % - select from knihy where autor like 'B%';
• upřesnění dotazu lze provést pomocí odkazu Nastavení (distinct, řazení, ...)

Dotaz (na úrovni databáze)

• slouží pro vyhledávání s podmínkami z více tabulek s možností využití logických operátorů OR, AND
• po vyplnění polí a tlačítku Aktualizovat dotaz se dotaz objeví ve formě SQL příkazu - ten se provede tl. Provést dotaz

• lze použít i Vizuální návrhář (odkaz nahoře vlevo z Dotazu)

SQL

• záložka SQL na úrovni tabulky - zprava je možno přenášet názvy polí, přidat do oblíbených
• také záložka SQL na úrovni databáze
• Pozn. pro spojování tabulek se používá operátor JOIN - viz kapitola relace: SELECT * FROM table1 JOIN table2 ON conditional_expression
• Např. select * from knihy join vypujcky on knihy.id_knihy=vypujcky.id_knihy

• Nápověda:
  select * from ctenari join vypujcky on ctenari.id_ctenar=vypujcky.id_ctenar
  select autor,nazev,pujceno from knihy join vypujcky on knihy.id_knihy=vypujcky.id_knihy
  select nazev,autor,pujceno,jmeno from knihy join vypujcky on knihy.id_knihy=vypujcky.id_knihy join ctenari on ctenari.id_ctenar=vypujcky.id_ctenar