Lekce 2 - PostgreSQL - Vytvoření databáze a tabulky

V minulé lekci, PostgreSQL - Úvod a příprava prostředí, jsme si řekli něco o relačních databázích a připravili jsme si prostředí.

V dnešním PostgreSQL tutoriálu si vytvoříme vlastní databázi a do ní nějakou tabulku.

Vytvoření databáze

V pgAdmin nejprve klikneme pravým tlačítkem myši na Databases, zvolíme Create a poté Database...:

Otevře se okno, v němž vyplníme název nové databáze (např. databaze_pro_web) a potvrdíme:

V databázích je zvykem pojmenovávat položky bez diakritiky, malými písmeny a s podtržítkovou notací.

Nyní máme vše připraveno k tomu, abychom se mohli začít učit jazyk SQL.

Jazyk SQL

SQL označuje Structured Query Language, tedy strukturovaný dotazovací jazyk. SQL je tzv. jazyk deklarativní. Zatímco u imperativních jazyků počítači vlastně říkáme krok po kroku co má udělat, u jazyků deklarativních pouze říkáme co má být výsledkem a již nás nezajímá, jak tohoto výsledku počítač dosáhne.
Díky tomu jsou databázové dotazy zjednodušeny na příkaz typu "Vrať mi 10 uživatelů s nejvyšším hodnocením". Databáze takový dotaz pochopí, rozloží si ho na nějaké své instrukce a tak jej zpracuje. Nám poté opravdu vrátí výsledek, aniž bychom tušili, jak k němu došla. Pokud vám příkaz přišel jako nadsázka, tak tomu tak není a příkazy opravdu takto vypadají. Jen jsou anglicky.

SQL se původně jmenovalo SEQUEL (Structured English Query Language) a vzniklo v laboratořích společnosti IBM s cílem vytvořit jazyk, kterým by se dalo komunikovat s databází jednoduchou angličtinou. SQL (relační) databáze se poté rozšiřovaly a ujaly. Dnes se prakticky nic jiného nepoužívá a i když má SQL v objektovém programování značné nevýhody, firmám se nechce přecházet na nic jiného (i když existují alternativní řešení). Ale to jsme odbočili.

Naklikáním databáze nám pgAdmin vygeneroval a spustil příkaz v SQL, který vytvořil novou databázi. Tento SQL příkaz by mohl vypadat asi takto:

CREATE DATABASE databaze_pro_web;

Přesnou verzi kódu, který byl použit pro vytvoření databáze můžeme vidět, když klikneme v pgAdminu na databázi a v pravém okně se přepneme na záložku SQL:

Hodnoty jako ENCODING, TABLESPACE, LC_COLLATE atd. jsou použity defaultní a není potřeba se jimi nyní zabývat.

V SQL se většinou píší příkazy velkými písmeny, to proto, že je to lépe odliší od zbytku dotazu nebo od kódu naší aplikace (např. v PHP či Javě). Názvy tabulek, sloupců a další identifikátory jsou naopak malými písmeny a podtržítkovou notací.

Smazání databáze

Zkusme si databázi odstranit (kliknout pravým tlačítkem na databázi, poté zvolit Delete/Drop a potvrdit):

Odstranit databázi se vám nemusí povést, pokud ji někdo používá (je do ní přihlášen).

SQL příkaz pro smazání databáze je:

DROP DATABASE databaze_pro_web;

Už umíme 2 SQL příkazy, vytvoření databáze a její odstranění. Ani jeden ale v naší aplikaci asi používat nebudeme, protože databázi si stačí vytvořit jen jednou a můžeme to udělat takto jednoduše v administračním nástroji. To samé platí pro tvorbu tabulek. Teprve samotná práce s daty v SQL pro nás bude klíčová, brzy se k ní dostaneme.

Spuštění SQL dotazu

S daty budeme pracovat především pomocí SQL dotazů. Ukážeme si, kde je budeme psát a volat.

Nejprve si databázi opět vytvoříme (viz postup výše). Poté na ni klikneme pravým tlačítkem a zvolíme Query Tool:

V nové záložce se nám otevře okno s Query Editor:

Do okna Query Editor je možné zadávat SQL příkazy, které se spouští tlačítkem Execute/Refresh (ikona trojúhelníčku). V okně Data Output se pak zobrazuje výsledek.

Tvorba tabulky

Nyní si vytvoříme tabulku. Vzpomeneme si na příklad tabulky uživatelů, co jsme si již ukázali. Měla sloupce jméno, příjmení, datum narození a počet článků.
Již jsme nakousli, že by každá tabulka měla mít sloupec, jehož hodnota je pro každou položku unikátní. Sloupců bude tedy dohromady 5, tabulka se bude jmenovat uzivatel. Zda budete při názvosloví tabulek používat jednotné či množné číslo záleží zcela na vás. Doporučuji ale být v tomto konzistentní. Pokud tabulky budete pojmenovávat v jednotném čísle, používejte to tak pro všechny tabulky. Pokud se rozhodnete pro množné číslo, platí to také. Hlavně nekombinujte oba způsoby. Tabulku je možné vyklikat i v pgAdmin.

Okno pro vytvoření nové tabulky otevřeme tak, že klikneme pravým tlačítkem na složku Tables (databáze > Schemas > public > Tables), zvolíme Create a poté Table...:

V záložce General vyplníme název nové tabulky (Name):

Přepneme se do záložky pro definování sloupců (Columns). Nové sloupce tabulky zde přidáváme pomocí tlačítka Add new row (ikona plus, sloupce tabulky se zde zobrazují jako řádky). Přidáme tedy požadovaný počet sloupců a rovnou je pojmenujeme (uzivatele_id, jmeno, prijmeni, datum_narozeni a pocet_clanku):

Je dobré pojmenovávat ID s prefixem tabulky.

Přesuňme se ke druhému sloupci, kde jsou datové typy jednotlivých sloupců tabulku. Typů je opravdu mnoho, ale my si dlouho (asi až do konce seriálu) vystačíme jen s několika.

• uzivatele_id nastavíme na integer, což jsou celá čísla
• jmeno a prijmeni na character varying, to je krátký text
• Datum narození na date
• Poslední, pocet_clanku, bude typu integer

Časem si popíšeme i další datové typy, ale teď si s nimi nebudeme motat hlavu

U určitých datových typů je třeba uvést i velikost daného datového typu (třetí sloupec Length/Precisi­on). To má pro nás smysl jen u character varying, u kterého udává maximální počet jeho znaků, jméno i příjmení nastavíme na 60 znaků:

Další sloupec Not NULL? udává, jestli daný sloupec tabulky smí nebo nesmí nabývat hodnoty NULL. Tuto hodnotu si vysvětlíme dále v kurzu. Pro teď nám stačí vědět, že když tuto možnost zaškrtneme, tak hodnotu daného sloupce tabulky budeme muset vždy zadat.

Nakonec je tu ještě sloupec Primary key?. Pomocí něj můžeme tabulce přiřadit tzv. primární klíč. Takový primární klíč by měla mít každá tabulka (i když teoreticky nemusí) a nejčastěji se nastavuje sloupci s ID, pro nás uzivatel_id. Když jej nastavíme, tak databáze bude kontrolovat, zda se v tomto sloupci nenachází stejné hodnoty.

Pokud bychom do sloupce, označeného jako primární klíč, chtěli vložit hodnotu, která se tu již nachází, dostali bychom chybu.

Primární klíč je forma omezení (constraint), které se aplikuje na daný sloupec. Primární klíč znamená, že se v daném sloupci nachází jedinečné hodnoty. Když budeme chtít uživatele např. vymazat, vymažeme ho podle tohoto klíče. Kdybychom ho mazali podle jména, smazali bychom několik položek, protože třeba Janů Nováků tam může být více. Podle ID vymažeme vždy jen toho jednoho.

Sloupec uzivatel_id tedy označíme jako primární klíč a zároveň zaškrtneme možnost Not NULL?, jelikož chceme, aby každý záznam měl vždy své ID:

Aby se uživatelé číslovali automaticky, tak sloupci uzivatel_id ještě nastavíme tzv. identity. Rozklikneme si pokročilejší editaci sloupce pomocí tlačítka Edit row a v nové nabídce se přepneme na záložku Constraints. Zde sloupci nastavíme identity a určíme, aby se hodnota vždy zvyšovala o 1 (vlastnost Increment) a aby první ID bylo 1 (vlastnost Start):

Tohoto chování se dá docílit i pomocí datového typu serial, ten je však specifický pro databáze PostgreSQL a již se moc nedoporučuje používat. Proto si zde raději uvádíme modernější identity, která je součástí SQL standardu.

Pokud máme přidánu identity, potvrdíme vytvoření nové tabulky tlačítkem Save.

SQL příkaz pro vygenerování tabulky si můžeme zobrazit obdobně jako u databáze:

SQL příkaz pro vygenerování tabulky vypadá následovně (odebral jsem některé nepovinné hodnoty a příkazy, které si pgAdmin vždy automaticky vygeneruje):

CREATE TABLE IF NOT EXISTS uzivatel
(
    uzivatel_id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    jmeno character varying(60),
    prijmeni character varying(60),
    datum_narozeni date,
    pocet_clanku integer,
    CONSTRAINT uzivatel_pkey PRIMARY KEY (uzivatel_id)
)

První řádek je jasný (příkaz pro vytvoření tabulky), zde je pro nás nová pouze podmínka IF NOT EXISTS, která zajistí vytvoření databáze jen v případě, že ještě neexistuje.
Na dalších řádcích se definují jednotlivé sloupce tabulky a jejich datové typy. Ke sloupci uzivatel_id se navíc definuje i identity, u které lze definovat další parametry. V případě, že je nezadáme, tak se automaticky zvolí zrovna ty co zde máme my.
Dále chceme, aby uzivatel_id nikdy nenabýval hodnoty NULL. U typu character varying je v závorce uveden i počet znaků. Nakonec je definováno omezení (constraint) jako primární klíč s názvem uzivatel_pkey na uzivatel_id.

Smazání tabulky

Odstranění tabulky je stejné jako odstranění databáze. V pgAdmin ji odstraníte kliknutím pravým tlačítkem na tabulku a zvolením Delete/Drop.

V SQL by byl příkaz následující.

DROP TABLE uzivatel;

V další lekci, PostgreSQL - Vkládání a mazání dat v tabulce, si ukážeme vkládání a mazání záznamů.