Search
Před touto konzultací by jste měli odevzdat DB model v DB. Na této konzultaci můžete vaše vypracované modely uložené v DB ještě zkonzultovat a popřípadě provést finální úpravy.
Pro výběr dat slouží v SQL příkaz SELECT, jehož obecné schéma je:
SELECT sloupce
FROM tabulky
WHERE podmínky
GROUP BY spojit podle…
HAVING podmínky na skupiny
ORDER BY seřadit podle…
V této části se seznámíme s tučně označenými klauzulemi příkazu SELECT, tj. FROM, WHERE, ORDER BY. Příkazy budeme přímo zkoušet na databázi Foodmart. Uvedené příklady berte jako ukázky příkazů, vymyslete ke každému příkazu jiný podobný.
Vypracování je možné získat označením textu za slovem SELECT, napsáno bíle.
V předchozím cvičení je tabulka zaměstnanců (employee) a tabulku skladů (store) spojena pomocí podmínky v klazuli WHERE, což je nejjednodušší varianta spojení tabulek.
SELECT * from store, employee where store.store_id=employee.store_id
můžeme přepsat pomocí INNER JOIN
SELECT * from employee INNER JOIN store ON store.store_id=employee.store_id
Představme si, že existuje zaměstnanec, kterému zatím nebylo přiděleno pracoviště (nemá vyplněno store_id). Takový zaměstnanec nesplňuje podmínku pro spojení tabulek a proto vypadává z výsledné tabulky. To může být nežádoucí například v případě, že chceme vypsat všechny zaměstnance a jejich pracoviště (pokud zaměstnanec nemá pracoviště, chceme ho mít v seznamu s nevyplněným pracovištěm). Pro tento účel existují varianty JOIN - LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL JOIN.
Příklady:
Vypíše všechny zaměstnance i ty, pro které neexistuje vazba do tabulky store. SELECT * from employee LEFT OUTER JOIN store ON store.store_id=employee.store_id
Vypíše pouze zaměstnance, pro které existuje vazba do tabulky store, ale doplní i ty řádky z tabulky store, pro které neexistuje vazba do tabulky employee. SELECT * from employee RIGHT OUTER JOIN store ON store.store_id=employee.store_id
Vypíše všechny zaměstnance a všechny řádky z tabulky store bez ohledu na vazbu mezi tabulkami. SELECT * from employee FULL JOIN store ON store.store_id=employee.store_id
Pro výběr dat slouží v SQL příkaz SELECT:
V této části se zaměříme na klauzule GROUP BY a HAVING. Doposud jsme pracovali s jednotlivými zaměstnanci. Často potřebujeme pracovat ne pouze s jednotlivými zaměstnanci, ale se skupinami zaměstnanců. Například v našem příkladě nás mohou zajímat skupiny zaměstnanců podle obchodu, ve kterém pracují (tabulka store). Vzhledem k tomu, že pracujeme se skupinou (množinou), nás budou zajímat tzv. agregované hodnoty - počet COUNT, průměr AVG, minimum MIN, maximum MAX, součet SUM pro zvolené skupiny. Například nás nezajímá věk jednoho konkrétního zaměstnance, ale zajímáme se o charakteristiku celé skupiny zaměstnanců, tedy o průměrný věk, nebo o nejnižší věk apod.
Vypracování je možné opět získat označením textu za slovem SELECT, napsáno bíle.
V některých případech potřebujeme nejprve zjistit z databáze nějakou hodnotu, kterou následně použijeme v dotazu. Například chceme najít všechny zaměstnance obchodů s vyšším než průměrným platem. Nejprve musíme z databáze zjistit velikost průměrného platu: SELECT avg(salary) from employee. Výsledek následně použijeme pro hledání všech zaměstnanců, kteří mají plat vyšší než průměrný: SELECT * from employee where salary>(SELECT avg(salary) from employee)
INSERT INTO fakturazbozi (id_faktura,ks,id_zbozi,id_sleva,cena,sleva)
SELECT 1,10,id_zbozi,id_sleva,cena,sleva FROM zbozi LEFT JOIN sleva USING (id_sleva) WHERE id_zbozi = 13; INSERT INTO fakturazbozi (id_faktura,ks,id_zbozi,id_sleva,cena,sleva) SELECT 1,20,id_zbozi,id_sleva,cena,sleva FROM zbozi LEFT JOIN sleva USING (id_sleva) WHERE id_zbozi = 14; INSERT INTO fakturazbozi (id_faktura,ks,id_zbozi,id_sleva,cena,sleva) SELECT 2,10,id_zbozi,id_sleva,cena,sleva FROM zbozi LEFT JOIN sleva USING (id_sleva) WHERE id_zbozi = 13; INSERT INTO fakturazbozi (id_faktura,ks,id_zbozi,id_sleva,cena,sleva) SELECT 2,30,id_zbozi,id_sleva,cena,sleva FROM zbozi LEFT JOIN sleva USING (id_sleva) WHERE id_zbozi = 15;
INSERT INTO fakturazbozi (id_faktura,ks,id_zbozi,id_sleva,cena,sleva) SELECT 3,100,id_zbozi,id_sleva,cena,sleva FROM zbozi LEFT JOIN sleva USING (id_sleva)
Dokumentace k databázi PostgreSQL specifikuje jakým způsobem lze vytvořit select a jaké parametry a klíčová slova lze použít. Dokumentaci k příkazu select je zde zde. Kromě specifikování parametru zde naleznete také ukázkové příkazy a použití.
Vytvořte si tabulku representující zůstatky na běžném bankovním účtu
CREATE TABLE zustatek ( id_ucet int NOT NULL UNIQUE, zustatek_kc numeric(10,2) NOT NULL DEFAULT 10 CHECK (zustatek_kc>0), PRIMARY KEY (id_ucet));
UPDATE zustatek SET zustatek_kc = zustatek_kc + (nejaka castka) WHERE id_ucet = (ucet1); UPDATE zustatek SET zustatek_kc = zustatek_kc - (nejaka castka) WHERE id_ucet = (ucet2);
Pokud není využito transakce (logicky atomické operace, která je v databázi řešena pomocí dvou a více dotazů), může být chování systému krajně nebezpečné. Proto je v takových situacích nutné použít transakcí. Otestujte předchozí převod peněz pomocí uzavření do transakce (v SQL pomocí BEGIN a COMMIT/ROLLBACK) a zamyslete se nad dalšími výhody jejich použití.
Úkol 6: Co znamenají vlastnosti ACID, a jakými technickými prostředky je každé z nich v relačních databázích dosaženo ?
Spusťte si dva DB klienty C1 a C2, napr. PgAdmin, psql, a připojte je k vasi databazi.
Úkol 7: Jaký minimální stupeň izolovanosti musí mít databáze nastaven, pokud se nezměnil výsledek SELECT dotazů pro C1 v následujícím rozvrhu:
C1: BEGIN; C2: BEGIN; C1: SET TRANSACTION ISOLATION LEVEL ..... C1: SELECT zustatek_kc FROM zustatek WHERE id_ucet = (cislouctu); C2: UPDATE zustatek SET zustatek_kc = zustatek_kc + 100; C1: SELECT zustatek_kc FROM zustatek WHERE id_ucet = (cislouctu); C1: COMMIT; C2: COMMIT;
nebo
C1: BEGIN; C2: BEGIN; C1: SET TRANSACTION ISOLATION LEVEL ..... C1: SELECT count(value) FROM mytable WHERE value = 100; C2: INSERT INTO mytable(value) VALUES (100); C1: SELECT count(value) FROM mytable WHERE value = 100; C1: COMMIT; C2: COMMIT;
Úkol 8: Nastavte vhodný stupeň izolovanosti tak, aby oba dva SELECTy v C1 v následujícím rozvrhu vracely stejný výsledek. Stupeň izolovanosti se nastavuje na začátku transakce pomocí SQL příkazu SET TRANSACTION ISOLATION LEVEL <X>, kde <X> je SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED; viz SET TRANSACTION .
SET TRANSACTION ISOLATION LEVEL <X>
SERIALIZABLE
REPEATABLE READ
READ COMMITTED
READ UNCOMMITTED
C1: BEGIN; C2: BEGIN; C1: SET TRANSACTION ISOLATION LEVEL ..... C1: SELECT zustatek_kc FROM zustatek WHERE id_ucet = (cislouctu); C2: UPDATE zustatek SET zustatek_kc = zustatek_kc + 100; C1: SELECT zustatek_kc FROM zustatek WHERE id_ucet = (cislouctu); C2: COMMIT; C1: SELECT zustatek_kc FROM zustatek WHERE id_ucet = (cislouctu); C1: COMMIT;
C1: BEGIN; C2: BEGIN; C1: SELECT count(value) FROM mytable WHERE value = 100; C2: INSERT INTO mytable(value) VALUES (100); C2: COMMIT; C1: SELECT count(value) FROM mytable WHERE value = 100; C1: COMMIT;
Databázový deadlock se projevuje tak, že jedna transakce čeká než ji jiná transakce uvolní zámky.
Níže je příklad jakým nasimulovat DB deadlock. Přihlaste se pomocí PGADMINA a psql na jednu a tu samou DB. Nechte proběhnout následující skript:
CREATE TABLE osoby ( id serial NOT NULL, jmeno character varying(100), prijmeni character varying(100), CONSTRAINT pk_osoby PRIMARY KEY (id) ); INSERT INTO osoby(jmeno, prijmeni) VALUES ('jiri', 'novak'), ('petr', 'konecny'),('jan', 'fistr'), ('petr', 'maly');
Nyní v psql napište následující sekvenci:
BEGIN; delete from osoby where id=3; delete from osoby where id=4; delete from osoby where id=1;
V PGADMINU si otevřete sql příkazový řádek a napište následující:
BEGIN; select * from osoby; delete from osoby where id=2; delete from osoby where id=3; delete from osoby where id=4; END;
Nechte provést příkaz v pgadminu. Všimněte si, že se nic neděje. Vypadá to, jako by pgadmin vytuhl. Problém je v tom, že session v pgadminu čeká až se dokončí session v psql, která má zamknuté záznamy ke smazání. Došlo k takzvanému deadlocku. Když to psql napíšete END; a tím ukončíte transakci, tak se zámky uvolní a vše je zase v normálu.
Dokumentace k select pro PostgreSQL
Dokumentace k insert pro PostgreSQL
Dokumentace k delete pro PostgreSQL
Dokumentace k update pro PostgreSQL