ARCHIV

PostgreSQL (7) - Výběr dat z databáze

Data uložená v databázi je třeba nějakým relativně rozumným způsobem vybírat, filtrovaně a s možností využití relací na další data z jiných tabulek.

25.3.2005 15:00 | MaReK Olšavský | Články autora | přečteno 28583×

Základy výběru

Výběr dat z databáze je jednoduše realizován pomocí příkazu SELECT sloupce FROM tabulka, kde sloupce označují data, která budou ve výběru, názvy se oddělují čárkami, data z tabulky je možné ihned zpracovat funkcemi a náhradní znak, pokud je třeba vybrat celé řádky je * (hvězdička). Tabulka označuje tabulku, ze které jsou data vybírána. Příkaz SELECT má několik dalších volitelných parametrů, které budou uvedeny níže. Název tabulky je možné vložit pomocí prostého názvu (například users).

V praxi je znacne nevhodne vybirat z tabulky vsechna data, proto je vhodné vyjmenovat jen sloupce, které jsou třeba v dalším kroku zpracovaných dat. Toto souvisí především s rychlostí. V příštím díle bude vysvětleno urychlení výběrů pomocí indexace databáze a další zrychlení souvisí s pohledy (views), které budou vysvětleny v některém z pozdějších dílů.

V tabulce, která byla vytvořena v minulém díle lze nyní, pokud v ní jsou nějaká data, provést několik výběrů. Níže je uvedena ukázka výběru všech položek a druhým dotazem výběr pouze jména a stavu uživatele.

SELECT * FROM users;
 id | login  |       pwd        | visible |  hash               
----+--------+------------------+---------+---------
  5 | petr   | petr             | t       |
  1 | jana   | jana             | f       |
  2 | petra  | petra            | f       |
  0 | vladka | 123              | f       |
(4 řádek)

SELECT login, visible FROM users;
 login  | visible 
--------+---------
 petr   | t
 jana   | f
 petra  | f
 vladka | f
(4 řádek)

Pomocí klíčového slova AS lze ovlivnit, pod jakým názvem budou navráceny sloupečky tabulky, prřípadně při spojování tabulek lze takto definovat zástupné jméno tabulek.

Klauzule WHERE

SQL umožňuje zůžit výběr vět, které databáze vrátí k dalšímu zpracování. Ke zúžení slouží klauzule WHERE, za kterou se napíší podmínky, které je možné spojovat pomocí logických spojek. Pro tabulky, které byly vytvořeny pro tento text, lze výběrem SELECT * FROM users WHERE visible="t"; získat všechna data o uživatelích, kteří jsou dostupní, viditelní. Dotazem SELECT * FROM users WHERE ((visible="f") AND (id>=2)) data nedostupných uživatelů s id>=2.

Pomocí restrikce WHERE lze tabulky i spojovat do relací, ale k tomuto by měl sloužit především příkaz JOIN, který bude vysvětlen níže.

V definované tabulce lze nyní provést výběr uživatelů, kteří mají parametr visible nastavený na "True" (PosgtreSQL jej vrací jako "t"), v dalším příkladě je výběr loginu a hesla uživatelů s parametrem id>=2 a v posledním příkladě je výběr vět, které splňují podmínky, kdy je jejich viditelnost nastavena na "False" a id>=2 a login bude vrácen jako jméno.

SELECT * FROM users WHERE visible='t';
 id | login |       pwd        | visible |      hash
----+-------+------------------+---------+------------------
  5 | petr  | petr             | t       |
(1 řádka)

SELECT login, pwd FROM users WHERE id>=2;
 login |       pwd           
-------+----------------------
 petr  | petr
 petra | petra
(2 řádek)

SELECT login AS jmeno, pwd FROM users WHERE id>=2 AND visible='t';
 jmeno |        pwd
-------+-------------------
 petr  | petr
(1 řádka)

Klauzule ORDER BY

Pořadí vět ve výsledku lze ovlivnit pomocí přidání Klausule ORDER BY (seřadit dle), za kterou je uveden sloupeček podle kterého se má řadit, případně lze uvést směr řazení na sestupný (ASC), nebo sestupný (DESC), tato direktiva se udává za název sloupečku. Je možné nastavit řazení podle více sloupečků, v tom případě se neudává znovu klauzule order, ale jen název sloupečku, podle kterého se má řadit a případně direktivu směru. Výchozí směr je nastaven na vzestupný (ASC). Udání směru třídění se dáva za omezení vět ve výběru pomocí WHERE.

SELECT id, login AS jmeno, pwd AS heslo FROM users WHERE id>=1 ORDER BY id DESC;
 id | jmeno |      heslo
----+-------+-----------------
  5 | petr  | petr
  2 | petra | petra
  1 | jana  | jana
(3 řádek)

Sdružování záznamů - GROUP BY a HAVING

Ke sdružení řádků, podle stejných hodnot lze použít 2 funkce - GROUP BY a HAVING. Použití těchto funkcí s sebou přináší drobnou nesnáz, že je možné je použít jen v dotazech obsahujících agregační funkce (SUM, AVG, ...), které budou vysvětleny v dalším textu. Vhodným příkladem je užití k analýze návštěv WWW serveru, kdy záznamy jednotlivých návštěv jsou potřeba málokdy, ale většinou je vypovídající statistika kolik mávštěv přišlo ve které dny, případně dělení na hodiny.

GROUP BY slouží jen ke sloučení řádků podle shodných hodnot ve sloupci/sloupcích, které jsou uvedeny za klíčovým spojením GROUP BY, pro sdružování je možné použít nejen názvy sloupců, ale i funkce ypracovávající hodnotu ze sloupce. Použití seskupování pomocí GROUP BY s sebou nese několik omezení v tom, co smí být ve výběru sloupců za příkazem SELECT:

• konstanty
• hodnoty, které jsou konstantní v rámci sdružované skupiny bez ohledu na to, jedná-li se o přímo o hodnotu, nebo o hodnotu zpracovanou některou z funkcí.
• skupiny hodnot zpracované agregačními funkcemi (o těchto funkcích bude text některého z pozdějších dílů)

Klauzule HAVING doplnuje sdružování o filtraci pomocí agregačních funkcí, tzn., že zahrnuty budou pouze ty souhrné řádky, které vyhoví podmínce v klauzyli HAVING.

Pro další příklady je nutné definovat si další data do tabulky userdetails a vytvořit novou tabulku, kde budou jednotlivé objednávky (především kvůli detailům adresy, na kterou má být obědnávka po uzavření odeslána) a tabulka s detaily jednotlivých položek objednávky. SQL script, ve kterém je potřebný stav databáze naleznete ke stažení zde. Důvod k ukládání ceny produktu v momentě je ten, že změní-li se cena, je neslušné změnit cenu pro zákazníka (samozřejmě snížení je slušné promítnout a změnit všude, kde je v obědnávkách vyšší, při ukládání do ceníku, například triggerem).

Následující příklady ukazují slučování pomocí GROUP BY a HAVING, první příklad ukazuje výsledek, je-li mimo groupovací podmínku, nebo pomocí agregační funkci některý ze sloupců.

SELECT id_category, visible FROM products GROUP BY id_category;
ERROR:  column "products.visible" must appear in the GROUP BY 
clause or be used in an aggregate function

SELECT id_category, visible FROM products GROUP BY id_category, visible;
id_category | visible 
------------+---------
          2 | t
          3 | t

SELECT id_category, visible, SUM(pieces*unitprice) AS hodnota_na_sklade FROM
products GROUP BY id_category,visible HAVING SUM(pieces*unitprice)>=100;
id_category | visible | hodnota_na_sklade 
------------+---------+-------------------
          2 | t       |            150.00
          3 | t       |           2750.00

Spojování tabulek

Data z tabulek lze spojovat do větších celků pomocí relací (odtud název relační databáze). Do standardu ANSI-92 se dá použít spojování pomocí restriktoru WHERE, od vzniku této normy funguje pro spojování databází standardizovaný příkaz JOIN, který má několik modifikátorů ovlivňujících spojování tabulek. S použitím WHERE se lze setkat především starších programátorů a pro začátečníky může být o něco přehlednější (zejména při spojování více tabulek). Použití JOIN má větší možnosti ovlivnění způsobu spojení tabulek.

Při spojování tabulek je nutné uvést názvy spojovaných tabulek a spojovací podmínky pro tyto tabulky. Při vyjmenovávání sloupečků je třeba předejít nejednoznačnostem v názvech (server tyto případy vyhodnotí jako chybu a dotaz není provede) buď pomocí názvu tabulky, za který se napíše název sloupce oddělený tečkou, nebo pomocí AS lze vytvořit zástupné jméno pro dotaz a použití je stejné jako u předchozího případu. Ve výběru se poté často používá klíčové slovo AS pro spřehlednění výstupních dat. Použití spojení pomocí WHERE i JOIN bude provedeno na tabulkách a datech připravených pro tento tutoriál.

Při použití WHERE je syntaxe SELECT sloupecky FROM tabulky WHERE spojovaci podminky AND dalsi podminky. Příklady osvětlí toto spojování jasněji, než teorie. V prvním příkladě je záměrně zavedená chyba v porušení nejednoznačnosti vybíraných řádků (id je v každé tabulce a PgSQL nedokáže rozlišit, ze které jej má vzít), názvy sloupečků ve výstupu jsou zpřehledněny použitím klauzule AS.

Spojování tabulek pomocí klauzule JOIN se vkládá za FROM místo názvu tabulky. Syntaxe je SELECT sloupce FROM tabulka1 JOIN tabulka2 ON spojovaci_podminka WHERE ostatni_podminky. JOIN se vždy nepoužívá ve své čisté formě nepoužívá, je k dispozici několik modifikací tohoto příkazu. Jsou shrnuty v následující tabulce:

tvar JOIN	popis
INNER JOIN	Pravděpodobně nejpoužívanější tvar, funkčně shodný s použitím jen JOIN. Do výstupu zahrne pouze řádky vyhovující spojovací podmínce za ON. Toto spojení tabulek je stejné, jako při použití vazební podmínky ve WHERE.
CROSS JOIN	Kartézký součin řádků tabulky, neboli na výstupu jsou řádku z první tabulky postupně přiřazeny všechny hodnoty z tabulky druhé. Používá se bez spojovací podmínky ON. Toto propojení je realizovatelné i použitím SELECT sloupce FROM tabulka1, tabulka2 bez uvedení propojovací podmínky za WHERE.
LEFT [OUTER] JOIN	Tabulka zleva je na výstup vrácena celá, doplněná o hodnoty z tabulky uvedené vpravo z vazební podmínky, pokud není vpravo odpovídající přes vazební podmínku, jsou hodnoty dopněny pomocí NULL v počtu odpovídajícím vybíraným sloupcům z levé strany výrazu. Klíčové slovo OUTER je nepovinné.
RIGHT [OUTER] JOIN	Podobné jako v předchozím případě, ale kompletně je zobrazena tabulka nepravo, doplněná o hodnoty, případně o NULL, zleva.

Pro spojování 3 a více tabulek je třeba použít postupného spojení SELECT sloupecky FROM (tabulka1 LEFT JOIN tabulka2 ON vazeb_podminka) LEFT JOIN tabulka3 ON vazeb_podminka2. Přidání čtvrté a další tabulky se dělá obdobně.

SELECT id, login, first_name, sure_name, email, visible FROM users,
userdetails WHERE users.id=userdetails.id_user;
ERROR:  column reference "id" is ambiguous

SELECT users.id AS id, login, first_name, surename, email, visible
FROM users, userdetails WHERE users.id=userdetails.id_user;
id | login | first_name | surename |     email      | visible 
---+-------+------------+----------+----------------+---------
 5 | petr  | Petr       | Novák    | petrik@neco.cz | t
 5 | petr  | Petr       | Novák    | novak@prvni.cz | t
(2 řádek)

SELECT t1.id AS id, login, first_name, surename, email, visible FROM users AS t1
LEFT OUTER JOIN userdetails AS t2 ON (t1.id=t2.id_user);
id | login  | first_name | surename |     email      | visible 
---+--------+------------+----------+----------------+---------
 5 | petr   | Petr       | Novák    | petrik@neco.cz | t
 5 | petr   | Petr       | Novák    | novak@prvni.cz | t
 1 | jana   |            |          |                | f
 2 | petra  |            |          |                | f
 0 | vladka |            |          |                | f
 3 | root   |            |          |                | t
(6 řádek)

Join poskytuje na výstup vždy kartézský součin řádků přes odpovídající vazební podmínku. Pokud jsou v zůžení sloupců jen data, která jsou pro několik vět stejná, na výstupu je několik opticky stejných řádků, čehož lze docílit například vynecháním sloupce email v dotazu z předchozího příkladu. Tyto duplicity, nebo spíše multiplicity, se odstraňují doplněním DISTINCT, nebo DISTINCT BY za příkaz SELECT. DISTINCT jen "zruší" duplicitní záznamy, DISTINCT BY umožňuje vyjmenovat do, závorek, sloupečky, podle nichž se odstraní duplicity, nemusí to být sloupečky, která jsou ve výstupu.

DISTINCT BY je rozšíření, které zavádí PgSQL na rámec normy. SQL92 definuje i příkaz DISTINCTROW, který sloučí pouze ty řádky ze spojení tabulek, které si odpovídají ve všech sloupcích.

Spojení téže tabulky

Tabulku lze spojit samu se sebou. Případ použití je získání seznamu, který je v jedné tabulce a věta obsahuje odkaz na větu, která je hierarchicky nad ní. V databázi, která je v tomto materiálu příkladová je to tabulka kategorií produktů. Toto spojení je pro začátečníky trochu obtížněji pochopitelné.

SELECT DISTINCT t1.id AS id, login, first_name, surename, visible FROM users AS t1
RIGHT JOIN userdetails AS t2 ON (t1.id=t2.id_user);
id | login | first_name | surename | visible 
---+-------+------------+----------+---------
 5 | petr  | Petr       | Novák    | t
(1 řádka)

SELECT DISTINCT ON (t2.email) t1.id AS id, login, first_name,
surename, visible FROM users AS t1 RIGHT JOIN userdetails
AS t2 ON (t1.id=t2.id_user);
id | login | first_name | surename | visible 
---+-------+------------+----------+---------
 5 | petr  | Petr       | Novák    | t
 5 | petr  | Petr       | Novák    | t
(2 řádek)

SELECT DISTINCT t1.title, t1.description, t1.id, t1.id_parent FROM prodcategory AS
t1 LEFT JOIN prodcategory AS t2 ON t1.id=t2.id_parent ORDER BY id_parent;
    title     |                    description                    | id | id_parent 
--------------+---------------------------------------------------+----+-----------
 Linux        | Linux distributions                               |  1 |         0
 Slackware    | Linuxová distribuce, která nemyslí                |    |
              | za uživatele při instalaci                        |  3 |         1
 SLAX         | Mini Live založená na Slackware                   |  4 |         3
 Vector Linux | Linuxová distribuce založená na Slackware         |  2 |         3
(4 řádek)

V posledním příkladu stačí teoreticky nastavit pořáteční podmínku pro získání hierarchie kategorií pro určitý výrobek. Například má-li zákazník zájem o SLAX, vidí jeho zařazení pod Slackware a to zařazené pod Linux (Linux má uveden id_parent=0, tzn. že je top level kategorie). V praxi se vytvoření této cesty dělá pomocí scriptu.

Závěrem

V tomto díle bylo vysvětlená základní práce s dotazem SELECT při získávání dat. V příštím díle bude další práce s příkazem SELECT a složené SELECTy.

Verze pro tisk

DISKUZE NATURAL JOIN 25.3.2005 16:22 Lukáš Jelínek Re: NATURAL JOIN 25.3.2005 16:51 MaReK Olšavský Relační 31.3.2005 14:54 Lukáš Zapletal Zobrazit označené Zobrazit vše Příspívat do diskuze mohou pouze registrovaní uživatelé.