Warning
This page is located in archive.

3. konzultace

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.

Dotazování v relačních databázích, paralelní přístup k databázi

  1. dotazování v relačních databázích osnova5
    1. jazyk SQL
    2. pokročilé dotazy dotazy (agregace, spojení, množinové operace, vnořené dotazy)
  2. paralelní přístup k databázi osnova6
    1. transakce
    2. stupně isolace

SQL dotazy - SELECT

Jednoduché dotazy

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.

  1. Vypište celou tabulku zaměstnanců (skladu).
    • SELECT * FROM employee
  2. Vypište pouze sloupce first_name, last_name, birth_date z tabulky zaměstnanců.
    • SELECT first_name, last_name, birth_date FROM employee
  3. Vypište jména pozic zaměstnanců (sloupec position_title) tak, aby se žádnéjméno neopakovalo.
    • SELECT distinct position_title FROM employee
  4. Vypište přijmení zaměstnanců podle abecedy od a do z
    • SELECT distinct last_name FROM employee order by last_name asc
  5. Vypište přijmení zaměstnanců podle abecedy od z do a
    • SELECT distinct last_name FROM employee order by last_name desc
  6. Vypište jména a přijmení zaměstnanců a seřaďte je podle abecedy nejprve podle přijmení a pak podle jména
    • SELECT first_name, last_name FROM employee order by last_name, first_name
  7. Vypište pouze zaměstnance s platem větším než 40.000
    • SELECT * from employee where salary>40000
  8. Vyzkoušejte postupně další podmínky (=, >, <, >=, ⇐)
  9. Vypište pouze zaměstnance s platem mezi 10.000 a 20.000
    • SELECT * from employee where salary between 10000 and 20000
  10. Vyzkoušejte i složené podmínky s operátory AND, OR, NOT
  11. Vypište pouze zaměstnance jejichž přijmení začíná na S
    • SELECT * from employee where last_name like 'S%'
  12. Vypište pouze zaměstnance jejichž jméno končí na S a srovnejte je podle abecedy
    • SELECT * from employee where first_name like '%s' order by last_name
  13. Spojte tabulku zaměstnanců (employee) a tabulku skladů (store) a vypište všechny údaje.
    • SELECT * from store as s, employee as e where s.store_id=e.store_id
  14. Vypište všechny zaměstnance, kteří pracují v obchodu Store 1.
    • SELECT * from store as s, employee as e where s.store_id=e.store_id and store_name='Store 1'
  15. Spočítejte kolik takových zaměstnanců v databázi existuje.
    • SELECT count(*) from store as s, employee as e where s.store_id=e.store_id and store_name='Store 1'

Spojování tabulek pomocí JOIN

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

Dotazy s klauzulemi GROUP BY, HAVING

Pro výběr dat slouží v SQL příkaz SELECT:

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 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.

  1. Zjistěte počty zaměstnanců podle jejich profese (position_title).
    • SELECT position_title, count(*) FROM employee GROUP BY position_title ORDER BY position_title
  2. Zjistěte průměrné platy zaměstnanců podle profese, seřaďte podle průměrného platu vzestupně.
    • SELECT position_title, avg(salary) as avg FROM employee GROUP BY position_title ORDER BY avg
  3. Zjistěte průměrné platy zaměstnanců - mužů podle profese, seřaďte podle průměrného platu sestupně.
    • SELECT position_title, avg(salary) as avg FROM employee WHERE gender='M' GROUP BY position_title ORDER BY avg desc
  4. Zjistěte pro každý obchod (store) počet zaměstnanců, seřaďte podle jména obchodu.
    • SELECT store_name, count(*) FROM store as s, employee as e WHERE s.store_id=e.store_id GROUP BY store_name ORDER BY store_name
  5. Zjistěte jména obchodů s více jak 50 zaměstnanci.
    • SELECT store_name, count(*) as count FROM store as s, employee as e WHERE s.store_id=e.store_id GROUP BY store_name HAVING count(*)>50
  6. Zjistěte jména obchodů, kde pracuje více než 40 žen.
    • SELECT store_name, count(*) as count FROM store as s, employee as e WHERE s.store_id=e.store_id AND gender='F' GROUP BY store_name HAVING count(*)>40

Vnořené dotazy

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)

  1. Zjistěte počet zaměstnanců, kteří měli nižší než je průměrný věk při nástupu do zaměstnání. Pomůcka: věk při nástupu do zaměstnání je (date_part('year',hire_date)-date_part('year',birth_date)).
    • SELECT count(*) FROM employee WHERE (date_part('year',hire_date)-date_part('year',birth_date))<(SELECT avg(date_part('year',hire_date)-date_part('year',birth_date)) FROM employee)
  2. Případně pro INSERT:

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)
          
          

Využití selectu v PostgreSQL

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í.

Transakce v PostgreSQL

Ukázka zajištění automicity operace

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));
Zadejte si několik účtů s různými zůstatky a implementujte převod peněz z jednoho účtu na druhý.
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);
Pomocí převodu otestujte funkčnost integritního omezení CHECK (zustatek_kc>0)

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í.

V PGADMINU je vhodné vypnout autocommit. File→Options→Query Tool→ Query Editor→Enable Autocommit odškrtnout.

Ukázka ACID

Ú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 .

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;

nebo

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;

Deadlock

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.

Důležité odkazy

courses/bd6b33dbs/3.konzultace.txt · Last modified: 2016/04/01 14:57 by komenant