6.16. phpMyAdmin - zálohy, funkce, pohledy, spouště

6.16.1. Opakování

• XAMPP - servery Apache, MySQL
• Správa MySQL přes phpMyAdmin - úrovně Home/Databáze/Tabulka, typy dat, relace, dotazy

1	Stáhněte si přes FTP z fileserveru pomocí účtu anonym soubor mysql04.zip a extrahujte jej tak, aby rozbalený soubor byl ve složce c:\temp\mysql04	FileZilla
2	Editujte v PSPadu soubor c:\temp\mysql04\jprijmeni_funkce.sql a prověřte existenci příkazů pro vytvoření tabulek a vložení řádků (všimněte si i příkazu transakce)	PSPad
3	Spusťte z řídícího panelu XAMPP servery Apache a MySQL	XAMPP
4	Spusťte phpMyAdmin tlačítkem Admin a všimněte si IP adresy v prohlížeči	XAMPP
5	Vytvořte v phpMyAdmin novou databázi jprijmeni_funkce s porovnáváním  utf8_czech_ci, kde jprijmeni nahraďte svým prvním písmenem ze jména a příjmením bez diakritiky	phpMyAdmin

6.16.2. Obnova a zálohování databáze - import, export

• import do databáze ze souboru *.sql - vytvoření tabulek a načtení dat do tabulek - provádí se při obnovení databáze ze zálohy

6	Nastavte novou databázi jako aktuální a importujte soubor c:\temp\mysql04\jprijmeni_funkce.sql do nově vytvořené databáze pomocí záložky Import a proveďte kontrolu načtení dat
7	Nastavte u pole ctenari.foto MIME typ: Image/JPEG a podobně Transformaci při prohlížení na Image/JPEG inline a proveďte kontrolu zobrazení obrázků v režimu Projít

• vložení řádků a dotaz

8	Najděte a poznamenejte si primární klíče čtenáře Aloise Kalaše a knih Markéta Lazarová a Válka s mloky
9	Zaevidujte tomuto čtenáři výpůjčky těchto dvou knih s dnešním datem
10	Vypište aktuální výpůjčky dotazem (vrácené knihy se nevypisují): 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 where vypujcky.vraceno is null

• export databáze = záloha (backup) databáze - vhodné je naplánování úlohy pro automatické spouštění v noci (také se provádí pomocí mysqldump)
• zálohu možno komprimovat

11

Přejděte do menu na úrovni databáze jprijmeni_funkce a proveďte export databáze s vlastním nastavením (vypněte komentáře, přidejte transakci) do souboru c:\temp\mysql04\jprijmeni_export.sql pomocí záložky Export (jprijmeni nahraďte jako obvykle) - proveďte kontrolu existence a obsahu souboru ve složce

6.16.3. Funkce, seskupování pomocí GROUP BY

• opakování SQL - select-core, select-stmt
• teorie - viz předchozí látka
• manuál funkcí - http://dev.mysql.com/doc/refman/5.0/en/functions.html
• count() - vrací počet hodnot

12	Vypište celkový počet výpůjčených knih: select count(*) from vypujcky where vraceno is null;

• GROUP BY sloupec - seskupí řádky se stejnou hodnotou

13	Vypište jména čtenářů a u každého uveďte počet výpůjček (včetně vrácených): select jmeno, count(*) from ctenari join vypujcky on ctenari.id_ctenar=vypujcky.id_ctenar group by jmeno

• Alias - as - slouží pro nové pojmenování sloupce - např. právě vytvořeného

14	Příkaz upravte, aby se výpis řadil podle počtu výpůjček sestupně: ..., count(*) as pocet ... order by pocet desc;

• CHAR_LENGTH(a) - počet znaků, LENGTH(a) - počet bytů

15	Vypište z tabulky knihy názvy knih seřazené od nejkratších názvů vzestupně k nejdelším (použijte úpravu SQL dotazu po Projít): ... ORDER BY CHAR_LENGTH(nazev)
16	Upravte a proveďte předchozí příkaz, aby se názvy řadily od nejdelších názvů sestupně k nejkratším (sestupně)
17	Proveďte profilování (zaškrtávátko vpravo pod SQL příkazem) a rozeberte výsledek

• IF(a,b,c) - jestliže a je true, pak výsledek je b, jinak je c
• LEFT(a, b), RIGHT(a, b) - část řetězce - b je délka (podobně také SUBSTR)
• CONCAT(a,b,...) - spojení řetězců

18	Upravte SQL dotaz tak, aby se názvy delší než 18 znaků vypisovaly zkrácené na 15 znaků a byly doplněny 3 tečkami: SELECT if (char_length(nazev)>18,concat(left(nazev,15),'...'),nazev) FROM knihy ORDER BY char_length(nazev) DESC

Další podobné funkce - např.

• CASE - vícenásobné větvení
• RAND() - náhodná hodnota
• REPLACE(a,b,c) - náhrada řetězců - místo b bude c v řetězci a
• LOCATE(a,b) - pozice a v řetězci b

19	Vypište pomocí funkce rand() náhodný název knihy z tabulky knihy: ... ORDER BY rand() ...

Datum a čas

• čas se automaticky přepočítává na lokální (ne jako u SQLite)
• DATEDIFF, TIMEDIFF - počet dnů (...) mezi časy
• NOW - aktuální datum a čas (také CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP)

20	Změňte u dvou vybraných výpůjček datum půjčení na datum před 7 dny
21	Vypište kolik dnů je každá kniha vypůjčená: SELECT nazev, autor, pujceno, DATEDIFF( NOW( ) , pujceno ) AS pujceno_dnu FROM knihy JOIN vypujcky ON knihy.id_knihy = vypujcky.id_knihy WHERE vraceno IS NULL

Další příbuzné funkce - např.

• DATE_FORMAT a TIME_FORMAT - mění formát datumu a času
• ADDDATE - manuál - přičtení k datumu, podobně ADDTIME, SUBDATE, SUBTIME
• formátovací proměnné - MySQL manuál

6.16.4. Views - pohledy

• Pohledy = uložené dotazy, se kterými lze zacházet jako s tabulkami - data se vždy znovu generují (používá se pro často pokládané složité dotazy)
• create view jmeno_pohledu as select ... - diagram
• v myPHPAdmin se pohled vytvoří provedením SQL a poté odkazem "Vytvořit pohled" - nápověda je možná otazníkem v dialogu
  • algorithm se ponechá undefined - automatický výběr optimálního způsobu zpracování
  • při nahrazování pohledu se zaškrtne OR REPLACE

22	Vytvořte pohled pujceno_dnu z předchozího dotazu, ke kterému přidejte výpis jmen čtenářů: SELECT nazev, autor, pujceno, DATEDIFF( NOW( ) , pujceno ) AS pujceno_dnu, jmeno FROM knihy JOIN vypujcky ON knihy.id_knihy = vypujcky.id_knihy JOIN ctenari ON ctenari.id_ctenar = vypujcky.id_ctenar WHERE vraceno IS NULL
23	Ověřte funkčnost pohledu jeho vybráním ze stromu vlevo a výpis uložte pomocí Náhledu pro tisk jako pujceno_dnu.pdf

• smazání pohledu - DROP VIEW

6.16.5. Triggery - spouště

• slouží k automatickému spouštění příkazů (mezi BEGIN a END) před nebo po vkládání, aktualizaci, mazání v tabulce (INSERT, UPDATE, DELETE) při určené podmínce (WHEN) - manuál
• např. CREATE TRIGGER database_name.trigger_name BEFORE INSERT ON table_name FOR EACH ROW BEGIN stmt1; stmt2; END;
• před / po = BEFORE / AFTER
• přístup v triggeru ke staré hodnotě přes OLD.sloupec, k nové hodnotě přes NEW.sloupec
• používají se např. ke kontrole vkládaných hodnot (pokud nestačí CHECK), vytváření logů (protokolování), aktualizace dat v jiných tabulkách
• smazání spouště - DROP TRIGGER
• triggery mají vliv na výkon databáze
• Správa v phpMyAdmin - menu Spouště - Přidat spoušť

Příklad logování pomocí spouště

24	Vytvořte v databázi jprijmeni_funkce tabulku log s poli id_log (INT primary key AI), popis (TEXT), datum_logu (TIMESTAMP)
25	Vytvořte spoušť (Spouště - Přidat spoušť) pro logování změn jmen čtenářů: Název spouště: trigger_update_ctenar Tabulka: ctenari Spouštění: AFTER UPDATE Definice: INSERT INTO log (popis) VALUES (concat('Update čtenáře: ',old.jmeno,' na ',new.jmeno))
26	Prověřte logování změnou jmen alespoň dvou čtenářů a výpisem (procházením) tabulky log - výpis tabulky log uložte pomocí Náhledu pro tisk jako log.pdf

• další příklad na kontrolu rozsahu proměnné je v manuálu

27	Vytvořte dokumentaci k databázi jprijmeni_funkce ve formě dokumentu c:\temp\mysql04\funkce.pdf (pomocí záložky Struktura na úrovni databáze a odkazu Datový slovník)	Struktura - Datový slovník - Náhled pro tisk
28	Soubory jprijmeni_export.sql + 3x PDF (pujceno_dnu, log, funkce) ze složky c:\temp\mysql04 uložte na fileserver do nově očíslované složky	FileZilla