SELECT I a VIEW

Jak už jsem dříve psal, nejdůležitější SQL příkaz je SELECT Toto je jedna z prvních kapitol, kde se mu budu věnovat podrobně. Navíc zbyde ještě trochu času na použití SELECTu v kombinaci s jinými SQL příkazy (CREATE TABLE a INSERT INTO) a na nový databázový objekt VIEW, který, zjednodušeně řečeno, ze SELECTu vytvoří virtuální tabulku.

Výběr sloupců
- Tabulka dual
Spojování tabulek
Pohledy (CREATE VIEW)
CREATE TALBE AS
INSERT INTO
MySQL /MariaDB
SQLite
Oracle

Výběr sloupců

O příkazu SELECT jsem se poprvé zmínil ve třetí kapitole. Tento příkaz se používá pro získávání dat z databáze. Umí toho ale mnohem více, než jenom zobrazit data z tabulky tak, jak jsou uložená. Ukazoval jsem například, jak z databáze získat jen některé řádky, pomocí podmínky WHERE.

Teď ukáži, jak vybrat jen některé sloupce. Jak se to dělá jste už mohli vidět v lekci o úpravách tabulek, v části o SQLite, kde se plnila tabulka daty pomocí INSERT INTO … SELECT, který v této kapitole ještě připomenu.

Budu vycházet z příkladu z předchozí lekce.

Pokud za klíčovým slovem SELECT uvedete hvězdičku, vypíší se všechny sloupce v tabulce. Pokud chcete vypsat jen některé sloupce, zapište jejich jména místo hvězdičky. Sloupce se budou vypisovat v tom pořadí, v jakém jste je zapsali. Názvy sloupců můžete navíc uzavřít do dvojitých uvozovek. To pro případ, že by název sloupce obsahoval nějaké nestandardní znaky, které by SQL parser nerozpoznal jako součást identifikátoru (názvu sloupce), například mezery.

Dvojité uvozovky vlastně můžete používat i k označení dalších identifikátorů, jako je třeba název tabulky.

Pokud se vám nelíbí název nějakého sloupce, můžete ho nechat vytisknout pod jiným jménem pomocí klauzule AS. (Jméno sloupce v tabulce to nijak neovlivní.)

rimmer1=> SELECT prijmeni AS "potřebují přidat", rodne_cislo
FROM zamestnanci WHERE plat <= 10000;
potřebují přidat | rodne_cislo
------------------+-------------
Pavova | 8001010601
Pavova | 8001010602
Osel | 8001010610
(3 řádky)

V tomto příkladu už jsem musel použít dvojité uvozovky pro název sloupce za AS, protože název obsahuje mezeru. Bez uvozovek by se to SQL parser snažil interpretovat jako dva identifikátory zasebou, což by vedlo k nepochopení SQL příkazu a následné chybě.

Díky uvozovkám můžete vytvořit tabulku s názvem obsahujícím mezeru, nebo sloupce s mezerou. Nedělejte to. Jen si tím zavaříte na problémy a ostatní programátoři, co budou pracovat s vaší databází, vás nebudou mít rádi. Pokud chcete mít název tabulky nebo sloupce složený z více slov, spojujte je podtržítky (nazev_tabulky). Raději pro žádné identifikátory nepoužívejte ani českou diakritiku (kdo ví, třeba budete mít jednou úspěch a s vaším produktem budou pracovat i cizinci, kteří nebudou mít českou diakritiku k dispozici).

Místo názvu sloupce můžete za SELECT uvést i nějaký výraz, který se může a nemusí odkazovat na nějaký sloupeček z tabulky.

Všiměte si, že první sloupeček je obyčejná řetězcová konstanta. Řetězce se uvozují do jednoduchých uvozovek.
Všiměte si taky názvu sloupců nad řetězcovou konstantou (první sloupec) a nad matematickým výrazem (poslední sloupec). Nejsou moc hezké, že? Od toho tu je AS! Pomocí AS můžete dokonce přejmenovat i tabulku, ačkoliv v následujícím příkladu je to zcela zbytečné.

Identifikátor nemůže být prázdný řetězec, proto jsem pojmenoval první sloupec jako mezeru " ".

Tabulka dual

Asi vás napadlo, že můžete použít SELECT jako kalkulačku. Kolik je třeba 3 na třetí?

rimmer1=> select 3^3 as "3^3" FROM oddeleni;
select 3^3 as "3^3" from oddeleni;
3^3
-----
27
27
27
(3 řádky)

Jupí, ono to funguje! Akorát se výsledek vypsal pro každý řádek z tabulky oddeleni. Chudák tabulka, takhle zneužitá. Chtělo by to nějakou univerzální tabulku s jedním řádkem.

A ejhle, taková tabulka existuje. Ale ne v PostgreSQL. MySQL a Oracle k tomuto účelu poskytují tabulku dual. Jedná se o „virtuální“ tabulku, kterou nenajdete ve výpisu mezi svými tabulkami, ale přesto nad ní můžete provádět SELECTy.

Takovou tabulku si samozřejmě můžete vytvořit („nevirtuálně“) i v PostgreSQL …

CREATE TABLE dual (dummy CHAR(1) NOT NULL UNIQUE CHECK (dummy = 'X'));
INSERT INTO DUAL VALUES('X');

Tabulku jsem vytvořil schválně tak, aby do ní šel vložit jen jeden řádek. Né že by to bylo nezbytně nutné, ale případným čtenářům mé databáze rychleji dojde, k čemu že ta tabulka slouží. (Nebo jim to třeba nedojde vůbec, když neznají dual z jiných DBMS, za nic neručím :-)

rimmer1=> SELECT 3^3 AS "3^3" FROM dual;
3^3
-----
27
(1 řádka)

A jedna dobrá (?) zpráva na konec. PostgreSQL dokáže udělat nestandardní SELECT, který neobsahuje klauzuli FROM. Takže se vlastně bez tabulky dual obejdete.

rimmer1=> SELECT 3^3;
?column?
----------
27
(1 řádka)

rimmer1=> select pi() as "PI";
PI
------------------
3.14159265358979
(1 řádka)

Smyslem databází není nahrazovat matlab nebo excel, ale i tak má PostgreSQL docela slušnou výbavičku matematických operátorů a funkcí. Funkce, jako je třeba ta pi(), proberu v kapitole o funkcích.

Spojování tabulek

V minulé kapitole jsem vytvořil dvě tabulky: oddeleni a zamestnanci. Nyní se pokusím vybrat informace z obou tabulek naráz.

To se dělá tak, že se v příkazu SELECT za klíčovým slovem FROM zapíší jména obou tabulek oddělených čárkou. Výsledek pak bude kartézský součin obou tabulek.

To znamená, že se vytvoří řádky obsahující sloupce z obou tabulek (resp. ze všech, které vyjmenujete za FROM) a řádky se pospojují každý s každým (to je kartézký součin).

V našem případě jsou v tabulce oddělení tři řádky a v tabulce zaměstnanců 10 řádků. Takže ve výsledku bude každý řádek z tabulky oddělení spojení s každým řádkem z tabulky zaměstnanci (3 * 10 = 30 řádků).

rimmer1=> SELECT jmeno,prijmeni,oddeleni_id,prim_klic,nazev AS oddeleni
FROM oddeleni, zamestnanci;
jmeno | prijmeni | oddeleni_id | prim_klic | oddeleni
---------+----------+-------------+-----------+-----------------
Lenka | Pavova | 1 | 1 | Sekretariat
Jana | Pavova | 1 | 1 | Sekretariat
Jana | Mala | 1 | 1 | Sekretariat
Lenka | Pavova | 2 | 1 | Sekretariat
Tom | Jerry | 2 | 1 | Sekretariat
Martin | Luter | 2 | 1 | Sekretariat
Leopold | King | 2 | 1 | Sekretariat
Tomas | Mann | 3 | 1 | Sekretariat
Vasek | Trn | 3 | 1 | Sekretariat
Stary | Osel | 3 | 1 | Sekretariat
Lenka | Pavova | 1 | 2 | Pravni oddeleni
Jana | Pavova | 1 | 2 | Pravni oddeleni
Jana | Mala | 1 | 2 | Pravni oddeleni
....
Stary | Osel | 3 | 3 | Pravni oddeleni
(30 rows)

Výpis je zkrácen. První tři sloupce jsou vybrány z tabulky zamestnanci, poslední dva z tabulky oddeleni.

Možná vás už napadlo, jak vybrat tabulku s hodnotami, které budou dávat smysl. Využijte primárního klíče z tabulky oddělení a cizího klíče z tabulky zaměstnanci, abyste spojili řádky zaměstnanců jen s odpovídajícím oddělením.
Takto se využívá relace mezi tabulkami.

rimmer1=> SELECT jmeno, prijmeni, oddeleni_id, prim_klic, nazev AS oddeleni
FROM oddeleni, zamestnanci
WHERE oddeleni_id = prim_klic;
jmeno | prijmeni | oddeleni_id | prim_klic | oddeleni
---------+----------+-------------+-----------+-----------------
Lenka | Pavova | 1 | 1 | Sekretariat
Jana | Pavova | 1 | 1 | Sekretariat
Jana | Mala | 1 | 1 | Sekretariat
Lenka | Pavova | 2 | 2 | Pravni oddeleni
Tom | Jerry | 2 | 2 | Pravni oddeleni
Martin | Luter | 2 | 2 | Pravni oddeleni
Leopold | King | 2 | 2 | Pravni oddeleni
Tomas | Mann | 3 | 3 | Pravni oddeleni
Vasek | Trn | 3 | 3 | Pravni oddeleni
Stary | Osel | 3 | 3 | Pravni oddeleni
(10 rows)

Teď máte krásný přehled o tom, kdo pracuje v jakém oddělení. Jistě už pro vás nebude problém zjistit, v jakém oddělení pracuje zaměstnanec Vasek Trn:

rimmer1=> SELECT nazev FROM oddeleni, zamestnanci
WHERE oddeleni_id = prim_klic AND jmeno='Vasek' AND prijmeni='Trn';
nazev
-----------------
Pravni oddeleni
(1 row)

V případě že se ve dvou tabulkách schodují názvy sloupců, můžete se na ně odkazovat přes tzv. tečkovou notaci:

SELECT nazev_tabulky1.nazev_sloupce, nazev_tabulky2.nazev_sloupce
FROM nazev_tabulky1, nazev_tabulky2;

Například:

SELECT oddeleni.nazev
FROM oddeleni, zamestnanci
WHERE oddeleni_id = prim_klic AND jmeno='Vasek' AND prijmeni='Trn';

A ještě něo o AS. Když přejmenujete tabulku pomocí AS, už se na ní musíte v SELECTu odkazovat novým jménem.

rimmer1=> SELECT o.nazev FROM oddeleni AS o;
nazev
-----------------
Sekretariat
Pravni oddeleni
Pravni oddeleni
(3 řádky)
rimmer1=> SELECT oddeleni.nazev FROM oddeleni AS o;
ERROR: invalid reference to FROM-clause entry for table "oddeleni"
ŘÁDKA 1: SELECT oddeleni.nazev FROM oddeleni AS o;
^
DOPORUČENÍ: Perhaps you meant to reference the table alias "o".

Jo a ještě něco. Klíčové slovo AS je nepovinné, takže ho nemusíte uvádět (ani u tabulek, ani u sloupců). Následující dva SELECTy dělají úplně to samé.

SELECT o.nazev AS "oddeleni" FROM oddeleni AS o;
SELECT o.nazev "oddeleni" FROM oddeleni o;

Pohledy (CREATE VIEW)

Všimli jste si, že když provedete nějaký SELECT, že jeho výsledkem je vlastně tabulka? Nebylo by skvělé, kdyby jste s takovou tabulkou mohli pracovat jako s jakoukoliv jinou?

Pomocí příkazu CREATE VIEW můžete uložit pohled na data vytvořený příkazem SELECT. Tento pohled se chová podobně jako skutečná tabulka. Za určitých, hodně specifických, okolností se do něj dají i vkládat data, ale důležitější je, že z něj můžete data získávat pomocí dalšího příkazu SELECT.

Syntaxe příkazu je následující:

CREATE VIEW nazev_pohledu AS SELECT ...

Až tak jednoduché to je. Stačí před SELECT napsat CREATE VIEW nazev_pohledu AS a je hotovo.

Na příkaz SELECT neexistují při vytváření pohledů žádná omezení. Můžete používat klauzule jako WHERE, můžete tabulky spojovat atd.

Pohled (VIEW) není skutečná tabulka, ve které by byly data. Pohled je spíše jako uložený SELECT pod nějakým jménem. Změníte-li tedy hodnoty tabulek ze kterých jste pohled vytvářeli, změní se i hodnoty v pohledu, která tato data zobrazuje.

CREATE VIEW pracovni_pozice AS
SELECT rodne_cislo, prijmeni, jmeno, nazev AS oddeleni
FROM oddeleni, zamestnanci
WHERE oddeleni_id = prim_klic;

Nyní máte vytvořen pohled pracovni_pozice a můžete s ním pracovat skoro stejně, jako s jakoukoliv jinou tabulkou.

Všimněte si, že můžete z tohoto pohledu zjistit, kdo pracuje v právním oddělení, ale nemůžeme zjistit, v kterém (v databázi jsou dvě právní oddělení), protože pohled neobsahuje prim_klic právního oddělení.

Pohledy můžete vypsat metapříkazem \dv (nebo \d). Definici pohledu (SELECT, který view definuje), zobrazíte metapříkazem \d+ nazev_pohledu.

Pohled smažete příkazem DROP VIEW nazev_pohledu;

DROP VIEW pracovni_pozice;

Kdykoliv provedete SELECT nad pohledem, musí se provést nejdříve SELECT, který definuje pohled. Vytvořením pohledu tedy rozhodně získávání dat z databáze nezrychlíte (spíš naopak). DBMS se snaží SELECTy optimalizovat, s větším či menším úspěchem. Když uděláte SELECT nad SELECTem, který přidává nějakou podmínku do WHERE, DBMS někdy dokáže vytvořit z obou SELECTů jen jeden (který spojí všechny podmínky do jedné klauzule WHERE) a ten pak provede. Použití SELECTu nad SELECTem pak není pomalejší, než kdybyste si vytvořili jeden SELECT sami. Ale to je to nejlepší, co se o „zlepšování výkonu“ použitím pohledů dá říct.

V pohledu se může objevit opravdu jakýkoliv SELECT, takže i SELECT z jiného pohledu. Takovéto řetězení pohledů DBMS optimalizaci určitě neulehčuje, takže si to raději 2x rozmyslete, než SELECTnete.

Postgres od verze 9.3 umožňuje nad jednoduchými pohledy provádět DML operace (INSERT, UPDATE, DELETE).

CREATE TABLE AS

Jako můžete vytvořit pohled do tabulek pomocí CREATE VIEW, můžete obdobně vytvořit i novou tabulku. Do nové tabulky přenesete data z původní tabulky (nebo tabulek) pomocí SELECT.

CREATE TABLE AS vytvoří skutečnou tabulku, která nebude mít s původní tabulkou (tabulkami) již nic společného. Pokud původní tabulku aktualizujete, této nové tabulky se to nijak nedotkne! Taky proč? Už je to jiná tabulka.

Vytvářet novou tabulku tímto způsobem se hodí například v případě, kdy chcete uložit nějaký pohled do databáze v čase, aby se už neměnil – prostě taková slabší záloha.

Pokud vytvoříte tabulku tímto způsobem, žádná integritní omezení ani indexy se v notvé tabulce z té staré nepřenesou. Pokud je chcete, musíte je k tabulce přidat. O tom jak se to dá udělat zase až někdy příště.

INSERT INTO

Použití INSERT INTO … SELECT jsem už ukazoval v SQLite části o ALTER TABLE (jak přejmenovat nebo smazat sloupec).

Nevýhoda nutnosti přidávat dodatečně všechna integritní omezení a indexy do tabulky u příkazu CREATE TABLE AS se dá obejít tak, že nejdřív vytvoříte tabulku pomocí CREATE TABLE tak jak chcete a pak jí naplníte daty z jiné tabulky (nebo jiných tabulek) pomocí příkazu INSERT INTO … SELECT.

DROP TABLE platy2013;
CREATE TABLE platy2013 (
oddeleni_id INTEGER REFERENCES oddeleni(prim_klic),
prijmeni varchar(20),
jmeno varchar(20),
hruba_mzda integer CHECK (hruba_mzda >= 0),
cista_mzda integer CHECK (cista_mzda >= 0)
);
INSERT INTO platy2013 SELECT oddeleni_id, prijmeni,
jmeno, plat, plat-(plat/100)*20
FROM zamestnanci;

Při vkládání hodnot do tabulky můžete vyjmenovat sloupečky, do kterých chcete něco vložit. Předchozí příklad by se dal přepsat takto (schválně jsem pomíchal pořadí sloupečků, na výsledek to ale nemá vliv):

INSERT INTO platy2013(prijmeni,jmeno,oddeleni_id,cista_mzda,hruba_mzda)
SELECT prijmeni, jmeno, oddeleni_id, plat-(plat/100)*20, plat
FROM zamestnanci;

Musíte si dávat pozor jen na to, aby počet a pořadí sloupečků vyjmenovaných za názvem tabulky do které se vkládá a sloupečky v SELECTu byli totožné (to dá rozum).

MySQL / MariaDB

Asi největší opruz z novinek v této kaptile jsou překvapivě uvozovky. Každý DBMS má trochu jinou strategii uvozování textových řetězců a identifikátorů, což je další hřebíček do rakve přenositelnosti SQL dotazů.

MySQL používá primárně pro uvozování identifkátorů zpětné uvozovky (`), pro uvozování řetězců jednoduché (') nebo dvojité (") uvozovky.

SELECT * FROM `oddeleni`;

Při přejmenovávání sloupce nebo tabulky pomocí AS se může v MySQL použít libovolná uvozovka ze všech tří možností (zpětná, jednoduchá i dvojitá).

V MySQL se v názvu sloupců výrazů bez použití AS nepoužije ?column?, ale MySQL použije samotný výraz (nebo jeho část).

Pokud v MySQL použijete pro název sloupce jméno " " (mezeru), MySQL zobrazí varování Name ' ' has become ''. Tohle varování (warning) ničemu nevadí, ale někoho by to mohlo vylekat, tak se o tom raději zmiňuji. (Jiná varování můžou znamenat opravdový problém). Pokud vám to varování vadí, použijte jako název ''.

V MySQL existuje „virtuální“ tabulka dual. Nemůžete z ní vybrat žádnou hodnotu (sloupeček), ale můžete jí použít v SELECTu s aritmetickými výrazy. Můžete použít i nestandardní formu SELECTu, kde se vypouští FROM (jako v Postgresu).

mysql> SELECT * FROM dual;
ERROR 1096 (HY000): No tables used

mysql> SELECT 3^3 FROM dual;
+-----+
| 3^3 |
+-----+
| 0 |
+-----+
mysql> SELECT pi();
+----------+
| pi() |
+----------+
| 3.141593 |
+----------+

V příkladu výše si můžete všimnout, že MySQL má jiné aritmetické operátory a funkce. Zatímco v PostgreSQL je 3^3 tři na třetí, tj. 9, v MySQL je to logická operace XOR (pro kterou používá PostgreSQL operátor #). MySQL má na exponenty zase metodu POWER(). Zkrátka, čtěte dokumentaci.

SQLite

V SQLite můžete pro uvození jména tabulky použít libovolné uvozovky (zpětné uvozovky (`),jednoduché (') nebo dvojité (")), stejně tak pro přejmenování sloupce (za AS). Ale pro jméno sloupce můžete použít jen dvojitou nebo zpětnou uvozovku. (V jednoduchých uvozovkách je uzavřený řetězec).

Pokud nepřejmenujete sloupec s aritmetickým výrazem nebo řetězcem, nepoužije SQLite ?column?, ale jako MySQL použije výraz, který sloupec tvoří.

sqlite> SELECT 'Zaměstnanec:', prijmeni, jmeno, plat, plat-(plat/100)*20
FROM zamestnanci;
'Zaměstnanec:' prijmeni jmeno plat plat-(plat/100)*20
-------------- -------- ------ ----- ------------------
Zaměstnanec: Pavova Lenka 10000 8000
...

SQLite podporuje tyto aritmetické operátory: +, -, *, /, % (modulo)
a bitové operátory: <<, >>, &, |
V dokumentaci k základním funkcím najdete i nějaké matematické funkce.

Oracle

V Oracle můžete používat pro uvozování identifkátorů jen dvojité uvozovky. Ale pozor! Jak jsem dříve psal, že na velikosti písmen v identifikátorech nezáleží (jen u jmen tabulek v MySQL a to ještě jen v Linuxu), tak v Oracle na velikosti záleží.

Dokud nepoužijete uvozovky, Oracle automaticky převádí identifikátory na velká písmena. Když například napíšete AS oddeleni, ve výsledku se sloupeček bude jmenovat ODDELENI, ale AS "oddeleni" zobrazí oddeleni.

Pokud jste při definici tabulky nepoužili pro názvy sloupců či tabulek uvozovky, pak jsou názvy velkými písmeny. Pokud použijete uvozovky, musíte napsat identifikátory ve správné velikosti!

SELECT * FROM "ODDELENI";
SELECT "PRIM_KLIC", nazev FROM oddeleni;

Pokud nepřejmenujete sloupec s aritmetickým výrazem nebo řetězcem, nepoužije Oracle?column?, ale jako MySQL či SQLite použije výraz, který sloupec tvoří.

I Oracle má svou sadu aritmetických operátorů a funkcí. (Nemá operátor ^, ale má funkci POWER()).

Oracle nepodporuje nestandardní SELECT bez FROM, ale vždy má k dispozici tabulku dual.

SELECT POWER(3, 3) FROM dual;
POWER(3, 3)
-----------
27

V Oracle při přejmenování tabulek nemůžete použít AS, ale bez AS to funguje:

SELECT o.nazev FROM oddeleni o; -- nemůže být ...oddeleni AS o;

Oracle nepodporuje IF EXISTS klauzuli u příkazu DROP VIEW. Vyřešit se to dá zase trochou programování (jako řeba u DROP TABLE – rozdíl je jen v SQLCODE).

BEGIN
EXECUTE IMMEDIATE 'DROP VIEW pracovni_pozice';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -00942 THEN
RAISE;
END IF;
END;
/