Základní práce s (Postgre)SQL (s přihlédnutím k MySQL)

PostgreSQL byl pro cvičení z databází zvolen z toho důvodu, že se jedná o opensource databázi, která bez větších omezení pokrývá většinu klíčových vlastností SQL a může tak konkurovat komerčním databázím. Zkušenosti získané s PostgreSQL by tak měly být bez problémů přenositelné na ostatní databázové systémy.

Pro cvičení je možné použít i jiné SŘBD, např. MySQL (MariaDB), odchylky budou zmíněny v textu.

Vytvoření tabulky

Nová tabulka se v SQL vytváří pomocí příkazu CREATE TABLE:

CREATE TABLE tabulka
(sloupec1 typ-dat [vlastnosti-sloupce],
 sloupec2 typ-dat [vlastnosti-sloupce],
 ...,
 sloupecN typ-dat [vlastnosti-sloupce],
 [omezeni-pro-vice-sloupcu]);

Datové typy

smallint -- 2 bytové celé číslo se znaménkem (-32768 až +32767)
integer nebo int -- 4 bytové celé číslo se znaménkem (-2147483648 až +2147483647)
bigint -- 8 bytové celé číslo se znaménkem (-9223372036854775808 až 9223372036854775807)
NUMERIC(pocet-cifer,pocet-desetin-mist) -- slouží k práci s čísly s přesně definovanou přesností. pocet-cifer udává celkový počet cifer v daném čísle (před i za desetinnou čárkou), pocet-desetin-mist udává počet cifer za desetinnou čárkou. Např. NUMERIC(6,2) umožňuje přesně pracovat s čísly ve tvaru 1234.56. Přesnost operací je u tohoto datového typu vykoupena pomalejší prací s těmito hodnotami.
real, double precision -- typy odpovídající číslům s plovoucí řádovou čárkou podle normy IEEE 754, tj. typům float a double z jazyka C. U těchto datových typů není zaručena přesnost a nejsou proto vhodné pro ukládání dat, kde na přesnosti záleží, typicky u finančních operací. Ale díky přímé podpoře hardwaru jsou operace s hodnotami v těchto sloupcích relativně rychlé.
varchar(n) -- řetezec proměnlivé velikosti mající maximálně n znaků
char(n) -- řetezec pevné velikosti mající právě n znaků (chybějící znaky jsou doplněny mezerami)
text -- text libovolné velikosti (specifikum PostgreSQL; v MySQL platí omezení podle použitého enginu)
boolean -- dvouhodnotový datový typ
date, time, timestamp -- datové typy pro reprezentaci data, času a data+času

Mimo hodnot, které jsou specifikovány danými typy, můžou (pokud není uvedeno jinak) sloupec obsahovat i ,,hodnotu'' NULL, která představuje ,,neznámou'' nebo ,,nedefinovanou'' hodnotu.

Vlastnosti sloupců

Aby se předešlo problémům s chybně zadanými daty, je možné definovat dodatečné podmínky, které musí hodnoty v daném sloupci splňovat. Slouží k tomu následující modifikátory:

NOT NULL -- daný sloupec nesmí obsahovat hodnotu NULL (proč je NULL zlo, bude vysvětleno na přednášce)
UNIQUE -- udává, že hodnoty v daném sloupci musí být unikátní (tj. nesmí se opakovat)
PRIMARY KEY -- určuje, že daný sloupec je primární klíč, tj. umožňuje jednoznačně identifikovat daný řádek (odpovídá UNIQUE NOT NULL)
DEFAULT hodnota -- definuje implicitní hodnotu pro daný sloupec, pokud není uvedeno, bere se jako implicitní hodnota NULL
CHECK podminka -- určuje podmínku, kterou musí hodnota v daném sloupci splňovat

Lze definovat omezení i pro více sloupců současně. Zejméná je to dobré u těchto omezení:

UNIQUE (sloupec1, ..., sloupecN) -- udává, že hodnoty v daných sloupcích musí být unikátní (tj. nesmí se opakovat)
PRIMARY KEY (sloupec2, ..., sloupecN) -- určuje, že dané sloupce tvoří primární klíč, tj. umožňují jednoznačně identifikovat daný řádek
CHECK podminka -- umožňuje deklarovat podmínku, platící pro více sloupců

Příklad

CREATE TABLE zamestnanci
(jmeno varchar(30) PRIMARY KEY,
 vek int NOT NULL CHECK (vek > 0),
 plat numeric(8, 2) NOT NULL CHECK (plat > 8000),
 uvazek NUMERIC(3, 2) NOT NULL DEFAULT 1 CHECK ((uvazek > 0) AND (uvazek < 2)),
 funkce varchar(20));

CREATE TABLE domy
(ulice varchar(40),
 cislo_popisne int CHECK (cislo_popisne > 0),
 mesto varchar(20),
 pocet_obyvatel int,
 PRIMARY KEY (ulice, cislo_popisne, mesto));

Automaticky inkrementované hodnoty

PostgreSQL obsahuje navíc dva typy serial a bigserial, které svým rozsahem odpovídají int a bigint a fungují jako automaticky inkrementované počítadlo, tj. pokaždé, když je do tabulky vložen nový řádek, je danému atributu přiřazeno nové číslo. Toto má praktické uplatnění zejména při automatickém generování identifikátorů. Technicky oba dva typy představují syntaktický cukr. Pokud vytvoříte tabulku:

CREATE TABLE foo
(bar serial);

Vytvoří se tzv. sekvence pojmenovaná foo_bar_seq (sloužící jako počítadlo), typ serial se převede na int a hodnoty danému sloupci jsou přiřazovány pomocí DEFAULT. Takže výše zmíněný zápis je ekvivalentní:

CREATE SEQUENCE foo_bar_seq;
CREATE TABLE foo
(bar int NOT NULL DEFAULT nextval('foo_bar_seq'));

V MySQL je podobného efektu dosáhnout použitím klauzule AUTO_INCREMENT u celočíselného typu a daný sloupec musí být klíčem.

CREATE TABLE foo
(bar INT AUTO_INCREMENT);

Vkládání dat

Data do tabulek se vkládají příkazem INSERT INTO v následujícím tvaru:

INSERT INTO tabulka (sloupec1, sloupec2, ..., sloupecN) VALUES (hodnota1, hodnota2, ..., hodnotaN);

Seznam sloupců lze vynechat, v takovém případě se předpokládá, že budou zadány hodnoty pro všechny sloupce v pořadí, jak byly sloupce zadány příkazem CREATE TABLE.

V seznamu sloupců je možné některé sloupce vynechat, v takovém případě se pro daný sloupec použije hodnota ,,DEFAULT'', byla-li zadána, nebo hodnota NULL.

Je možné vložit několik řádek pomocí jednoho příkazu INSERT. V časti VALUES jsou jednotlivé řádky odděleny závorkami.

Příklad:

INSERT INTO zamestnanci (jmeno, vek, plat, uvazek, funkce) VALUES ('Tomáš Pech', 67, 50000, 1.2, 'Manager');
INSERT INTO zamestnanci (jmeno, vek, funkce, plat) VALUES ('Tomáš Marný', 20, 'Kreativec', 15000);
INSERT INTO zamestnanci VALUES ('Ronald McDonald', 30, 100000, 1.0, 'CEO');
INSERT INTO zamestnanci (jmeno, vek, plat, uvazek, funkce)
  VALUES ('Jan Novák', 40, 16789.20, 0.8, 'Manager'),
         ('Petr Klíč', 37, 23100, 0.5, 'Údržbář');

Získání obsahu tabulky

K získání obsahu tabulky slouží příkaz SELECT v nejjednodušší verzi mající tvar:

SELECT * FROM tabulka;
-- vrati vsechny sloupce a vsechny radky v dane tabulce

Příklad:

SELECT * FROM zamestnanci;

Příkazu SELECT bude věnováno následující cvíčení, kde budou probrány další jeho vlastnosti.

Změna hodnot

Hodnoty jednotlivých řádků se mění příkazem UPDATE ve tvaru.

Příklad:

UPDATE tabulka SET sloupec1 = vyraz1 [, sloupec2 = vyraz2, ... ];
-- aktualizuje vsechny radky tabulky tak, ze sloupec1 bude mit hodnotu vyrazu1, sloupec2 bude roven vyrazu2

UPDATE tabulka SET sloupec1 = vyraz1 [, sloupec2 = vyraz2, ... ] WHERE podminka;
-- chova se stejne jako predchozi tvar jen aktualizuje radky splnujici danou podminku

Příklad:

UPDATE zamestnanci SET plat = 200000 WHERE  funkce = 'CEO';
-- nastavi generalnimu rediteli plat 200 tis.

UPDATE zamestnanci SET plat = plat * 1.10;
-- zvysi vsem zamestnancum plat o 10% 

UPDATE zamestnanci SET plat = plat * 2, funkce = 'Senior Manager' WHERE (vek > 50) AND (funkce = 'Manager'); 
-- vsem zamestnancum, kteri maji nad 50 let a jsou ve funkci Manager, zdvojnasobi plat a zmeni funkci

Odstranění řádků

K odstranění řádků slouží příkaz DELETE mající následující tvar:

DELETE FROM tabulka;
-- odstrani z tabulky vsechny zaznamy

DELETE FROM tabulka WHERE podminka;
-- odstrani z tabulky zaznamy splnujici danou podmínku

Příklad:

DELETE FROM zamestnanci WHERE vek > 50;

Úkoly

Navrhněte tabulku pro jednoduchý adresář, který bude obsahovat následující informace:
- jméno
- příjmení
- telefon
- email
- web
- datum narození
- pohlavi
- pocet deti
Zvolte vhodné typy a vhodná integritní omezení včetně primárního klíče.
Naplňte předchozí tabulku alespoň pěti záznamy.
Vybrané osobě přidejte jedno dítě.
Odstraňte všechny muže z adresáře.