LINUXSOFT.cz Přeskoč levou lištu
Uživatel: Heslo:  
   CZUKPL

> 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 23909×

Komerční sdělení: Pořádáme Kurzy PgSQL

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

pridej.cz

 

DISKUZE

NATURAL JOIN 25.3.2005 16:22 Lukáš Jelínek
L Re: NATURAL JOIN 25.3.2005 16:51 MaReK Olšavský
Relační 31.3.2005 14:54 Lukáš Zapletal




Příspívat do diskuze mohou pouze registrovaní uživatelé.
> Vyhledávání software
> Vyhledávání článků

15.5.2017 23:50 /František Kučera

Máš rád svobodný software a hardware nebo se o nich chceš něco dozvědět? Zajímá tě DIY, CNC, SDR nebo morseovka? Přijď na sraz spolku OpenAlt, který se bude konat ve čtvrtek 18. května od 18:00 v Radegastovně Perón (Stroupežnického 20, Praha 5).


Přidat komentář

12.5.2017 16:42 /Honza Javorek
PyCon CZ, česká konference o programovacím jazyce Python, se po dvou úspěšných ročnících v Brně bude letos konat v Praze, a to 8. až 10. června. Na konferenci letos zavítá např. i Armin Ronacher, známý především jako autor frameworku Flask, šablon Jinja2/Twig, a dalších projektů. Těšit se můžete na přednášky o datové analytice, tvorbě webu, testování, tvorbě API, učení a mentorování programování, přednášky o rozvoji komunity, o použití Pythonu ve vědě nebo k ovládání nejrůznějších zařízení (MicroPython). Na vlastní prsty si můžete na workshopech vyzkoušet postavit Pythonem ovládaného robota, naučit se učit šestileté děti programovat, efektivně testovat nebo si v Pythonu pohrát s kartografickým materiálem. Kupujte lístky, dokud jsou.
Přidat komentář

2.5.2017 9:20 /Eva Rázgová
Putovní konference československé Drupal komunity "DrupalCamp Československo" se tentokrát koná 27. 5.2017 na VUT FIT v Brně. Můžete načerpat a vyměnit si zkušenosti z oblasti Drupalu 7 a 8, UX, SEO, managementu týmového vývoje, využití Dockeru pro Drupal a dalších. Vítáni jsou nováčci i experti. Akci pořádají Slovenská Drupal Asociácia a česká Asociace pro Drupal. Registrace na webu .
Přidat komentář

1.5.2017 20:31 /Pavel `Goldenfish' Kysilka
PR: 25.5.2017 proběhne v Praze konference na téma Firemní informační systémy. Hlavními tématy jsou: Informační systémy s vlastní inteligencí, efektivní práce s dokumenty, mobilní přístup k datům nebo využívání cloudu.
Přidat komentář

15.4.2017 15:20 /František Kučera
Máš rád svobodný software a hardware nebo se o nich chceš něco dozvědět? Zajímá tě IoT a radiokomunikace? Přijď na sraz spolku OpenAlt, který se bude konat ve středu 19. dubna od 18:30 v Šenkovně (Sokolská 60, Praha 2).
Přidat komentář

5.3.2017 19:12 /Redakce Linuxsoft.cz
PR: 23. března proběhne v Praze konferenci na téma Cloud computing v praxi. Hlavními tématy jsou: Nejžhavější trendy v oblasti cloudu a cloudových řešení, Moderní cloudové služby, Infrastruktura současných cloudů, Efektivní využití cloudu, Nástrahy cloudových řešení a jak se jim vyhnout.
Přidat komentář

27.2.2017 22:12 /František Kučera
Pozvánka na 137. sraz OpenAlt – Praha: Tentokrát jsme si pro vás připravili neobvyklou akci. Ve středu 1.3. v 17:30 nás přivítá sdružení CZ.NIC ve svých prostorách v Milešovské ulici číslo 5 na Praze 3, kde si pro nás připravili krátkou prezentaci jejich činnosti. Následně navštívíme jejich datacentrum pod Žižkovskou věží. Provedou nás prostory, které jsou běžnému smrtelníkovi nedostupné!
Po ukončení prohlídky se všchni odebereme do hostince U vodoucha, Jagelonská 21, Praha 3 pochutnat si na některém z vybraných piv či dát si něco na zub. Rezervaci máme od 19:30, heslo je OpenAlt.
Ale pozor! Do prostor datového centra máme omezený přístup, dostane se tam pouze 10 lidí! Takže kdo přijde dříve, ten má přednost, a občanky s sebou! Kdo nebude chtít na prohlídku datového centra, může se pomalu přesunout do hostince U vodoucha a u nepřeberné nabídky piv počkat na ostatní.
Přidat komentář

18.1.2017 0:49 /František Kučera
Členové a příznivci spolku OpenAlt se pravidelně schází v Praze a Brně. Fotky z pražských srazů za uplynulý rok si můžete prohlédnout na stránkách spolku. Příští sraz se koná už 19. ledna – tentokrát je tématem ergonomie ovládání počítače – tzn. klávesnice, myši a další zařízení. Také budete mít příležitost si prohlédnout pražský hackerspace Brmlab.
Přidat komentář

   Více ...   Přidat zprávičku

> Poslední diskuze

15.6.2017 9:34 / Ondřej Havlas
php,

10.6.2017 10:39 / Temple
sell home for cash

11.5.2017 23:32 / lelo
Re: Problém se správcem balíčků

11.5.2017 5:45 / davd mašek
Re: Problém se správcem balíčků

10.5.2017 22:54 / lelo
Re: Problém se správcem balíčků

Více ...

ISSN 1801-3805 | Provozovatel: Pavel Kysilka, IČ: 72868490 (2003-2017) | mail at linuxsoft dot cz | Design: www.megadesign.cz | Textová verze