2. diel - PostgreSQL - Vytvorenie databázy a tabuľky

V minulom dieli seriálu tutoriálov o PostgreSQL sme si povedali niečo o relačných databázach a pripravili sme si prostredie. Dnes si vytvoríme vlastnú databázu a do nej nejakú tabuľku.

Vytvorme si databázu. V pgAdmin kliknite pravým tlačidlom myši na Databases -> New Database ....

Otvorí sa okno New Database, v ňom vyplňte názov novej databázy (Name) a potvrďte.

V databázach je zvykom pomenovávať položky bez diakritiky, malými písmenami as podtržítkovou notáciou. Snáď vám je jasné, prečo nie je diakritika dobrý nápad, za veľkými a malými písmenami je Linux, ktorý je rozlišuje a väčšina serverov práve na Linuxe beží.

Teraz máme všetko pripravené na to, aby sme sa mohli začať učiť jazyk SQL.

Jazyk SQL

SQL označuje Structured Query Language, teda štruktúrovaný dopytovací jazyk. SQL je tzv. Jazyk deklaratívny. Zatiaľ čo u imperatívnych jazykov počítači vlastne hovoríme krok po kroku čo má urobiť, u jazykov deklaratívnych len hovoríme čo má byť výsledkom a už nás nezaujíma, ako tento výsledok počítač dosiahne. Vďaka tomu sú databázové dotazy zjednodušené na príkaz typu "Vráť mi 10 užívateľov s najvyšším hodnotením". Databáza taký dotaz pochopí, rozloží si ho na nejaké svoje inštrukcie a tak ho spracuje. Nám potom naozaj vráti výsledok, bez toho aby sme tušili, ako k nemu došla. Ak vám príkaz prišiel ako nadsázka, tak to tak nie je a príkazy naozaj takto vyzerajú. Len sú anglicky.

SQL sa pôvodne volalo SEQUEL (Structured English Query Language) a vzniklo v laboratóriách spoločnosti IBM s cieľom vytvoriť jazyk, ktorým by sa dalo komunikovať s databázou jednoduchou angličtinou. SQL (relačné) databázy sa potom rozširovali a ujali. Dnes sa prakticky nič iné nepoužíva a aj keď má SQL v objektovom programovaní značné nevýhody, firmám sa nechce prechádzať na nič iné (aj keď existujú alternatívne riešenia). Ale to sme odbočili.

Naklikáním databázy nám pgAdmin vygeneroval a spustil príkaz v SQL, ktorý vytvoril novú databázu. Tento SQL príkaz vyzerá by asi takto:

CREATE DATABASE moje_databaze;

Presnú verziu kódu, ktorý bol použitý pre vytvorenie databázy môžete vidieť, keď kliknete v pgAdminu na databázu. V pravom dolnom okne s názvom "SQL pane" uvidíte celý kód. Hodnoty ako encoding, Tablespace, lc_collate atď. Sú použité defaultný a nie je potreba sa teraz nimi zaoberať.

V SQL sa väčšinou píšu príkazy veľkými písmenami, to preto, že je to lepšie odlíši od zvyšku dopytu alebo od kódu našej aplikácie (napr. V PHP či Jave). Názvy tabuliek, stĺpcov a ďalšie identifikátory sú naopak malými písmenami a podtržítkovou notáciou.

Skúste si databázu odstrániť (kliknutie pravým tlačidlom myši na databázu, ktorú chcete odstrániť -> Delete / Drop -> potvrdiť. Odstrániť databáze sa vám nemusí podariť, ak ju niekto používa (je do nej prihlásený).

SQL príkaz pre zmazanie databázy je:

DROP DATABASE moje_databaze;

Už vieme 2 SQL príkazy, vytvorenie databázy a jej odstránenie. Ani jeden ale v našej aplikácii asi používať nebudeme, pretože databázu si stačí vytvoriť len raz a môžeme to urobiť takto jednoducho v administračnom nástroji. To isté platí pre tvorbu tabuliek. Až samotná práca s dátami v SQL pre nás bude kľúčová, čoskoro sa k nej dostaneme.

Spustenie SQL dotazu

Teraz si databázu opäť vytvoríme (pozri postup vyššie). Označte myšou novovytvorenú databázu. V hornej lište sa vám sprístupní tlačidlo SQL.

Kliknite na neho a otvorí sa nové okno "Query" so záložkou "SQL Editor". Do horného ľavého okna je možné zadávať SQL príkazy a okne "Output pane" sa zobrazí výsledok.

Teraz si vytvoríme tabuľku. Spomenieme si na príklad tabuľky užívateľov, čo sme si ukázali v minulom dieli. Mala stĺpce meno, priezvisko, dátum narodenia a počet článkov. Už sme načali, že by každá tabuľka mala mať stĺpec, ktorého hodnota je pre každú položku unikátne. Stĺpcov bude teda dokopy 5, tabuľka sa bude volať "uzivatel". Či budete pri názvoslovie tabuliek používať jednotné či množné číslo záleží úplne na vás. Odporúčam ale byť v tomto konzistentné. Ak tabuľky budete pomenovávať v jednotnom čísle, používajte to tak pre všetky tabuľky. Ak sa rozhodnete pre množné číslo, platí to tiež. Hlavne nekombinujte oba spôsoby. Tabuľku je možné vyklikat aj v pgAdmin.

Otvoríme okno pre vytvorenie novej tabuľky (moje_databaze -> schemas -> public -> Tables -> pravým tlačidlom myši -> New Table). V záložke properties vyplníme názov novej tabuľky (Name).

Prepneme sa do záložky pre definovanie stĺpcov (Columns). Pomocou tlačidla pridať (Add) pridáme požadovaný počet a typ stĺpcov. Po kliknutí na tlačidlo pridať (Add) sa otvorí nové okno s názvom New Column. Vyplníme názov stĺpca (Name) a dátový typ (Data type). U niektorých dátových typov je potrebné uviesť aj veľkosť daného dátového typu (Length).

Dátový typ predstavuje typ údajov, ktorý bude v danom stĺpci uložený. Id záznamu (uzivatel_id) bude používať dátový typ celé číslo (integer).

Stĺpec pre meno a priezvisko bude dátového typu textový reťazec (character Varying). U tohto dátového typu je potrebné uviesť, akú maximálnu dĺžku reťazca (počet znakov) môže daný text obsahovať. V našom prípade zadefinujeme, že maximálna dĺžka reťazca bude 60 znakov. To by pre uloženie mena alebo priezviska malo stačiť.

Stĺpec pre uloženie dátumu narodenia bude dátového typu date.

Počet článkov bude opäť používať dátový typ integer (celé číslo).

Stĺpec uzivatel_id označíme ako primárny kľúč. To znamená, že databáza bude kontrolovať, či sa v tomto stĺpci nenachádza rovnakej hodnoty. Ak by sme do tohto stĺpca chceli vložiť hodnotu, ktorá sa tu už nachádza, dostali by sme chybu. Primárny kľúč je forma obmedzenia (CONSTRAINT), ktoré sa aplikuje na daný stĺpec. Primárny kľúč znamená, že v danom stĺpci sa nachádza jedinečné hodnoty. Primárny kľúč by mala mať každá tabuľka (aj keď teoreticky nemusí). Keď budeme chcieť užívateľa napr. Vymazať, vymažeme ho podľa tohto kľúča (teda podľa uzivatele_id). Keby sme ho mazali podľa mena, zmazali by sme niekoľko položiek, pretože treba Janů Novákov tam môže byť viac. Podľa uzivatele_id vymažeme vždy len toho jedného.

V pgAdmin primárny kľúč nastavíme tak, že sa prepneme do záložky Constraints -> zvolíme Primary Key -> Add.

Otvorí sa okno New Primary Key, v ktorom vyplníme názov. My si ho nazveme uzivatel_id_pk.

Teraz sa prepneme na záložku Columns, kde určíme, na ktorý stĺpec tabuľky sa má tento primárny kľúč použiť (vyberieme stĺpec a klikneme na Add).

Ak máme pridaný primárny kľúč, potvrdíme vytvorenie novej tabuľky.

SQL príkaz pre vygenerovanie tabuľky vyzerá nasledovne.

CREATE TABLE uzivatel
(
  uzivatel_id integer NOT NULL,
  jmeno character varying(60),
  prijmeni character varying(60),
  datum_narozeni date,
  pocet_clanku integer,
  PRIMARY KEY (uzivatel_id)
);

Prvý riadok je jasný (príkaz pre vytvorenie tabuľky), na ďalších riadkoch sa definujú jednotlivé stĺpce tabuľky a ich dátový typ. Pri type character Varying uvedieme aj počet znakov. Nakoniec pridáme obmedzenia (CONSTRAINT) ako primárny kľúč na uzivatele_id.

Odstránenie tabuľky je rovnaké ako odstránenie databázy. V pgAdmin ju odstránite kliknutím pravým tlačidlom na tabuľku a zvolením Delete / Drop.

V SQL by bol príkaz nasledujúci.

DROP TABLE uzivatel;

SQL príkaz pre vytvorenie tabuľky uzivatel ľahko vylepšíme. U stĺpca uzivatel_id používame dátový typ integer. To je úplne v poriadku, ale vzhľadom k tomu, že tento stĺpec bude slúžiť iba ako identifikátor záznamov, môžeme použiť dátový typ serial. Serial nie je skutočný dátový typ, ale uľahčenie, ktoré PostgreSQL ponúka. V prípade, že uvedieme serial, sa vytvorí stĺpec s dátovým typom integer, ktorý bude automaticky pri vložení záznamu do tohto stĺpca vkladať hodnotu, a táto hodnota sa bude automaticky navyšovať. Nový SQL príkaz pre vytvorenie tabuľky je nasledujúci.

CREATE TABLE uzivatel
(
  uzivatel_id serial,
  jmeno character varying(60),
  prijmeni character varying(60),
  datum_narozeni date,
  pocet_clanku integer,
  PRIMARY KEY (uzivatel_id)
);

Teraz si skúste tabuľku dropnout a potom znova vložiť pomocou SQL príkazu. Kliknite na tlačidlo SQL, ktoré vám otvorí SQL Editor. Do horného ľavého okna vložte váš SQL príkaz a kliknite na tlačidlo Execute query (zelená šípka play).

Nabudúce si tabuľku naplníme nejakými dátami