SQL pro pokročilé - 1.díl

Při uvedení článku o SQL Injection jsem zjistil, že velmi mnoho čtenářů by se o SQL rádo dozvědelo více. Proto jsem se rozhodl napsat pár článků o pokročilých technikách SQL, které se v učebnicích příliš nepublikují, a ukázat pár vzorových příkladů.

Dnešním tématem budiž dočasné tabulky a triky s nimi.

Dočasná tabulka, neboli TEMPORARY TABLE, se až na jednu podstatnou vlastnost neliší od normální tabulky. Tou vlastností je, jak už název napovídá, dočasnost. Tabulka je po ukončení spojení mezi PHP a MySQL databází nenávratně smazána. V praxi to znamená, že tabulka existuje po dobu, kdy máme platný identifikátor na MySQL spojení. Tento identifikátor vrací funkce Mysql_connect.

Uvedu příklad z praxe:

Mám dvě tabulky rozdílné struktury obsahující data o předpovědi počasí pro lyžařská střediska. Potřebuji vytvořit seznam TOP 10 středisek s nejvyšší sněhovou pokrývkou.

Vypadalo by to asi takto:

( SELECT  p.`vyska_snehu`,s.`stredisko`
          FROM `predpoved1` AS p
          LEFT JOIN `stredisko` AS s ON s.`id_stredisko` = p.`id_stredisko`  
          WHERE (TO_DAYS(NOW())=TO_DAYS(`datum`))
          GROUP BY p.`id_stredisko` 
)
 UNION
( SELECT p.`vyska_snehu`,s.`stredisko`
          FROM `predpoved2` AS p
          LEFT JOIN `stredisko` AS s ON s.`id_stredisko` = p.`id_stredisko`  
          WHERE (TO_DAYS(NOW())=TO_DAYS(`datum`))
          GROUP BY p.`id_stredisko` 
)
ORDER BY `vyska_snehu` DESC LIMIT 10

Jak vidíte, načteme data z tabulky předpovědi a seskupíme řádky pomocí id_střediska. Tím získáme jeden záznam pro každé středisko - vždy ten nejnovější. Tento select složíme s druhým selectem, který musí mít stejnou strukturu výstupu.

Tento kód je vcelku dobrý, bohužel má ale jednu chybu: Přestane správně fungovat ve chvíli, kdy se v obou tabulkách předpovědí objeví totéž středisko. A pokud možno ještě budeme dostávat trochu odlišná data do každé předpovědi. Pak se nám může stát, že v seznamu TOP 10 středisek bude některé středisko dvakrát...

Jak to řešit? Pomocí dočasných tabulek - TEMPORARY TABLE.

Začneme vytvořením:

CREATE TEMPORARY TABLE `top` (vyska_snehu INT, stredisko VARCHAR(100))

Jak vidíte, je to téměř totožné jako vytváření běžných tabulek.

Následuje mírné přepsání selectů:

INSERT INTO `top` ( SELECT  p.`vyska_snehu`,s.`stredisko`
          FROM `predpoved1` AS p
          LEFT JOIN `stredisko` AS s ON s.`id_stredisko` = p.`id_stredisko`  
          WHERE (TO_DAYS(NOW())=TO_DAYS(`datum`))
          GROUP BY p.`id_stredisko` 
)
 UNION
( SELECT p.`vyska_snehu`,s.`stredisko`
          FROM `predpoved2` AS p
          LEFT JOIN `stredisko` AS s ON s.`id_stredisko` = p.`id_stredisko`  
          WHERE (TO_DAYS(NOW())=TO_DAYS(`datum`))
          GROUP BY p.`id_stredisko` 
)
ORDER BY `vyska_snehu` DESC

Opět žádná novinka, pouze vkládáme výsledek selectů do naší dočasné tabulky. Jediný rozdíl je odstranění LIMIT na konci dotazu.

A nakonec přijde select z dočasné tabulky:

SELECT * FROM `top` GROUP BY stredisko ORDER BY 1 DESC LIMIT 10

Tím je problém vyřešen. Data vložíme do dočasné tabulky a tam je poté opět seskupíme podle středisek. Žádné duplicity už tedy nevzniknou...

Píše Daniel Milde aka Dundee

Rubriky

• Bezpečnost & Hacking
• Fotografování
• Hardware
• Linux
• O Blogu
• O Mně
• Ostatní
• Paragliding
• Politika
• Programování
• Software
• Webdesign
• Webhosting