Docela dlouho jsem požíval MySQL, ale když jsem začínal psát jeden větší projekt, tak jsem se rozhodl, že zkusím PostgreSQL.Postupně tu bude návod na instalaci, takovej menší tahák na správu a pak základy SQL.
Pro vlastní kompilaci je potřeba soubor se zdrojáky, ten je ke stažení na stránce www.posrgresql.org, z českého mirroru jsem si stáhnul soubor postgresql-7.4beta2.tar.bz2. Jěště jsem si stáhnul pgadmin3, chtěl jsem si ho taky zkompilovat, ale narážel jsem na miliony chyb, tak jsem si nakonec sosnul balíček pro slacka.
Kompilace
Soubor postgresql-7.4beta2.tar.bz2 rozbalíme, vlezeme do něj a provedeme magickou trojkombinaci…
tar -xjf postgresql-7.4beta2.tar.bz2
cd postgresql-7.4beta2
./configure --with-openssl --prefix=/usr
make
make install
Místo make install
pouštím checkinstall
, kterým si dělám balíčky.
Je potřeba udělat uživatele pod kterým se bude databáze spouštět:
groupadd pgsql
useradd -g pgsql -d /home/pgsql_data/ pgsql
mkdir /home/pgsql_data/
chown pgsql:pgsql /home/pgsql_data/
chmod 0700 /home/pgsql_data/
Spuštění
Dalím krokem je inicializace prostoru pro databáze(provedeme jako user pgsql):
initdb /home/pgsql_data/
vytvoření naší pokusné databáze:
createdb test
v souboru /home/pgsql_data/pg_hba.conf si změnte/přidejte nastavení pro ověřování. Soubor /home/pgsql_data/postgresql.conf obsahuje nastaveni databáze.
Pro spouštění databáze jsem si udělal rc.pgsql skript. Nyní můžeme spustit databázi příkazem /etc/rc.d/rc.pgsql start
Tahák na SQL
PostgreSQL ma program psql
, který nám umožní ovládání databáze pomocí příkazového řádku. Také můžete použít program pgadmin3
, je docela pěkný, grafický a klikací…
Uživatelé
Vytvoření uživatele:
CREATE USER username;
Smazání uživatele:
DROP USER username;
Vlastnosti uživatele:
- superuser
- Má práva na všechno, pouze on může vytvářet nové uživatele.
Pro vytvoření superuživatele použij:CREATE USER username CREATEUSER
- zakládání databáze
- Má právo na zakládání nových databází
CREATE USER username CREATEDB
- heslo
- Heslo do databáze je oddělené od systému, takže se musí natsavit zvlášť. Nastavení hesla:
CREATE USER username PASSWORD 'heslo'
Parametry zadávané při použití příkazu CREATE
se dají upravit příkazem ALTER
, takže například změna hesla se provede takto:
ALTER USER username PASSWORD 'noveheslo';
Skupiny
Přidání skupiny:
CREATE GROUP groupname;
Přidání uživatele do skupiny:
ALTER GROUP groupname ADD USER username1, ...
Odstranění uživatele ze skupiny:
ALTER GROUP groupname DROP USER username1, ...
Práva
Práva, která je možné nastavit:
SELECT, INSERT, UPDATE, DELETE, RULE, REFERENCES, TRIGGER, CREATE, TEMPORARY, EXECUTE, USAGE a ALL PRIVILEGES
Pro nastavení práv se používá příkaz GRANT
:
GRANT INSERT ON tabulka TO username;
Uživatel provádějící tento příkaz musí být majitelem tabulky.
Přiřazení práv skupině:
GRANT SELECT ON tabulka TO GROUP groupname;
Přiřazení práv speciálnímu uživatelskému jménu PUBLIC
přiřadíme práva všem uživatelům databáze.
To revoke a privilege, use the fittingly named REVOKE command:
REVOKE ALL ON accounts FROM PUBLIC;
Vytvoření databáze
CREATE DATABASE databasename;
případně
CREATE DATABASE databasename OWNER username;
nebo z prostředí shellu:
createdb -O username databasename
Výroba tabulek
CREATE TABLE (nazev_pole typ_pole [NULL|NOT NULL][UNIQUE|PRIMARY KEY][REFERENCES tabulka][DEFAULT hodnota][,...])
NULL
– určuje, zda může byt pole prázdnéUNIQUE
– hodnota se v tabulce může vyskytovat pouze jednouPRIMARY KEY
– primární klíč, automaticky jeUNIQUE
REFERENCES
– použijeme pokud je sloupec tabulky zároveň cizím klíčem (vytvoříme referenční integritu)DEFAULT
– pokud není při zadávání do tabulky pole známo, použije se tato hodnota
Vkládání dat
INSERT INTO tabulka [(pole[,...])] VALUES (hodnota[,...])
Při vkládání lze definovat pořadí polí. Pokud vynecháme definici polí vložíme data v pořadí v jakém jsme vytvořili tabulku.
Výběr dat
SELECT pole [AS nazev][,...] FROM relace zkratka [,...] [WHERE podminka] [ORDER BY poradi]
pole
– Seznam sloupců, které chceme při výběru dat zobrazit oddělený čáarkami.AS nazev
– pokud chceme aby byl sloupec uvozen jiným nadpisem než je název pole, uvede me ho jakonazev
- Když chceme vybrat všechny pole, můžeme použít znak
*
relace
– Název pole můžeme uvodit jménemrelace
(resp. jejízkratkou
), abychom jednoznačně identifikovali dané pole v případě stejných názvů polí ve dvou různých tabulkách
Definování podmínek – WHERE
logické operátory: =, <, >
logické spojky: AND, OR, NOT
porovnání řetězců: znak %
zastupuje libovolný počet libovolných znaků
Řazení – ORDER BY
Výsledek dotazu můžeme seřadit podle libovolného výrazu. Nejčastěji řadíme záznamy podle jednoho ze sloupců, ale teoreticky můžeme řadit podle libovolného výrazu využívající jednotlivých polí záznamu.
Sloučení tabulek – JOIN
Často potřebujeme spojit několik tabulek dohromady v jednom dotazu. V našem případě chceme spojit tabulky Ucitel a Predmet pomocí pole ucitel v tabulce Predmet. Máme na výběr dvě možnosti. Buď použijeme úplný kartézský součin těchto dvou relací a vybereme pouze záznamy, které mají sobě odpovídající hodnoty sloupců Ucitel.id a Predmet.ucitel. Nebo můžeme použít klauzuli JOIN v definici relací.
SELECT p.nazev AS "Název", u.prijmeni + ", " + u.jmeno AS "Učitel" FROM Predmet p, Ucitel u WHERE p.ucitel=u.id
Tento dotaz vypíše seznam názvů předmětů a k nim příslušných učitelů. Všimněte si, že jsme v definici zobrazených polí použili sloučení řetězců do jednoho. Jedná o tzv. vnější spojení (z angl. outer join).
SELECT p.nazev AS "Název", u.prijmeni + ", " + u.jmeno AS "Učitel" FROM Predmet p JOIN Ucitel u ON p.ucitel=u.id
Tento dotaz je ve výsledku stejný jako dotaz předcházející, ovšem použili jsme tentokrát tzv. vnitřní spojení (z angl. inner join). Použití tohoto typu spojení nám dává větši flexibilitu při definici spojení, což si ukážeme později.
Vypočtené sloupce
Při definici vypisovaných sloupců či při konstrukci výrazů se nemusíme omezit pouze na počítání s jedním řádkem, ale můžeme též počítat s celou množinou sloupce. Pro toto počítání se používají následující funkce:
SUM()
– součet hodnot sloupceMIN()
– minimální hodnota množinyMAX()
– maximální hodnota množinyAVG()
– průměrná hodnotaCOUNT()
– počet unikátních hodnot
SELECT min(plat) AS "Minimum", max(plat) AS "Maximum", avg(plat) AS "Průměr" FROM Ucitel
Vypíše minimální, maximální a průměrný plat všech učitelů.