Search
V této hodině budeme pracovat s demonstrační databází Foodmart. Ta obsahuje data z datového skladu popisujícího prodeje jídla a doprovodné informace. Zde budeme pracovat s tabulkou zaměstnanci (table employee) a tabulkou sklady (table store). Vztah mezi těmito tabulkami je 1:N.
K databázi se připojíme pomocí programu Pgadmin3.
Vytvoříme připojení k serveru
Postupným rozklikáváním hierarchie nalezneme databázi Foodmart, kterou máme právo prohledávat. Následně se přepneme do okna SQL, kde můžeme zkoušet připravené dotazy.
Na následujícím obrázku je nakresleno schéma databáze pro lepší orientaci v tabulkách databáze.
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)
ALTER USER jmeno WITH PASSWORD 'nove_heslo'
SQL dotaz pro vytvoření databáze:
CREATE SEQUENCE seq_sleva; CREATE TABLE sleva ( id_sleva int NOT NULL UNIQUE DEFAULT nextval('seq_sleva'), nazev varchar(64) NOT NULL UNIQUE, sleva numeric(2) NOT NULL CHECK (sleva BETWEEN 0 AND 100), PRIMARY KEY (id_sleva)); GRANT USAGE ON SEQUENCE seq_sleva TO student_db12_00; GRANT ALL PRIVILEGES ON TABLE sleva TO student_db12_00;
CREATE SEQUENCE seq_zbozi; CREATE TABLE zbozi ( id_zbozi int NOT NULL UNIQUE DEFAULT nextval('seq_zbozi'), nazev varchar(128) NOT NULL UNIQUE, popis text, id_sleva int REFERENCES sleva ON UPDATE CASCADE ON DELETE SET NULL, cena numeric(2) CHECK (cena >0), PRIMARY KEY (id_zbozi)); GRANT USAGE ON SEQUENCE seq_zbozi TO student_db12_00; GRANT ALL PRIVILEGES ON TABLE zbozi TO student_db12_00;
CREATE SEQUENCE seq_faktura; CREATE TABLE faktura ( id_faktura int NOT NULL UNIQUE DEFAULT nextval('seq_faktura'), odberatel varchar(128) NOT NULL, prodavac varchar(64) NOT NULL DEFAULT (user), PRIMARY KEY (id_faktura)); GRANT USAGE ON SEQUENCE seq_faktura TO student_db12_00; GRANT ALL PRIVILEGES ON TABLE faktura TO student_db12_00;
CREATE TABLE fakturazbozi ( id_faktura int NOT NULL REFERENCES faktura ON UPDATE CASCADE ON DELETE CASCADE, id_zbozi int NOT NULL REFERENCES zbozi ON UPDATE CASCADE ON DELETE CASCADE, id_sleva int REFERENCES sleva ON UPDATE CASCADE ON DELETE SET NULL, cena numeric NOT NULL, sleva numeric(2), ks numeric NOT NULL CHECK(ks>0), PRIMARY KEY (id_faktura,id_zbozi)); GRANT ALL PRIVILEGES ON TABLE fakturazbozi TO student_db12_00;
Vkládání dat
insert into sleva(nazev,sleva) VALUES ('AKCE: dnes 20%',20); insert into sleva(nazev,sleva) VALUES ('TOTALNÍ VÝPRODEJ: 80%',80);
INSERT INTO zbozi(nazev,popis,id_sleva) VALUES ('ponožky modré','Krásné modré ponožky, co musíte mít',NULL); INSERT INTO zbozi(nazev,popis,id_sleva) VALUES ('ponožky rudé','Kdo by nechěl tyto krásně rudé ponožky',1); INSERT INTO zbozi(nazev,popis,id_sleva) VALUES ('ponožky děravé','druhá jakost, velká sleva',3);
INSERT INTO faktura (odberatel) VALUES ('Pepa Novák, Technická 8, Praha 6'); INSERT INTO faktura (odberatel) VALUES ('Pepina Nováková, Technická 18, Praha 6'); INSERT INTO faktura (odberatel) VALUES ('Pepina Nováková, Technická 18, Praha 6');
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)