====== 2. konzultace ====== Před touto konzultací by jste měli odevzdat [[https://cw.fel.cvut.cz/wiki/courses/bd6b33dbs/semestralni_prace|návrh modulu DB]]. Na této konzultaci můžete vaše vypracované modely ještě zkonzultovat a popřípadě provést finální úpravy. ===== Modelování dat, dotazování v relačních databázích ===== - modelování dat (pro relační databáze) {{:courses:bd6b33dbs:ds-3.ppt| osnova3}} - základy relační algebry - normální formy - dotazování v relačních databázích {{:courses:bd6b33dbs:sql-1.ppt|osnova4}} - jazyk SQL - základní dotazy (CRUD - create, read, update delete) ====== Studentské databáze ====== {{:courses:bd6b33dbs:screen6.png|login}} == Změna hesla == ALTER USER jmeno WITH PASSWORD 'nove_heslo' ==== Zkušební databáze ==== === SQL dotaz pro vytvoření databázových tabulek: === Vytvoření sequence pro tabulku sleva. CREATE SEQUENCE seq_sleva; Vytvoření tabulky //sleva//. Tabulka bude mít sloupce **id_sleva**, **nazev** a **sleva**. Primárním klíčem bude sloupec **id_sleva**. Tabulka má také několik dalších vlastností. Sloupec **id_sleva** nesmí být nulový a hodnoty v tomto sloupci musí být unikátní v rámci této tabulky (víte proč tomu tak musí být?). Hodnoty v tomto sloupci se také budou generovat ze sekvence, kterou jste vytvořili v předchozím kroku. Sloupec **naze** bude nenulový a unikátní napříč tabulkou. Sloupec **sleva** bude nenulový a jeho hodnota musí být mezi 0 a 100. 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)); Nyní je potřeba nagrantovat práva uživateli na tabulku a sequenci. GRANT USAGE ON SEQUENCE seq_sleva TO db16_; GRANT ALL PRIVILEGES ON TABLE sleva TO db16_; Vytvoření sequence pro tabulku sleva. CREATE SEQUENCE seq_zbozi; Vytvoření tabulky //zbozi//. Sloupec **id_zbozi** je nenulový, unikátní a jeho hodnoty se generují na základě sekvence. Sloupec **nazev** je nenulový a unikátní, sloupec **popis** je textový a může být nulový, sloupec **id_sleva** je cizí klíč z tabulky //sleva//. V případě smazání se nastaví na null. Sloupec **cena** je hodnota, která může být nulová a musí být větší jak 0. 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)); Nyní je potřeba nagrantovat práva uživateli na tabulku a sequenci. GRANT USAGE ON SEQUENCE seq_zbozi TO db16_; GRANT ALL PRIVILEGES ON TABLE zbozi TO db16_; 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 db16_; GRANT ALL PRIVILEGES ON TABLE faktura TO db16_; 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 db16_; === SQL skripty pro vkládání dat === Vložení dat do tabulku //sleva//. * První řádek vloží záznam, v kterém bude nazev = AKCE: dnes 20% a sleva = 20. id_sleva bude vyplněno na základě sekvence. * Druhý řádek vloží záznam, v kterém bude nazev = TOTÁLNÍ VÝPRODEJ: 80% a ve sloupci sleva bude 80. id_sleva bude vyplněno na základě sekvence INSERT INTO sleva(nazev,sleva) VALUES ('AKCE: dnes 20%',20); INSERT INTO sleva(nazev,sleva) VALUES ('TOTALNÍ VÝPRODEJ: 80%',80); Vložení dat do tabulky //zbozi//. * První řádek vloží do tabulky **nazev** = ponožky modré s **popis** = Krásné modré ponožky, co musíte mítbez reference na **slevu** * Druhý řádek vloží do tabulky **naze** = ponožky rudé s **popis** = Kdo by nechěl tyto krásně rudé ponožky a s referenci na **slevu** Akce: dnes 20% * Třetí řádek skončí s chybou. 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'); ====== SQL - základní operace (CRUD) ====== Na minulých konzultací se prezentovali základy dotazování v jazyku SQL. Nyní se podíváme na další příkazy. Zkratka v názvu cvičení **CRUD** vyjadřuje (create,read,update,delete). Doposud jsme se zabývali "čtením - read" dotazováním do databáze. Dnes se zaměříme na další práci s daty (INSERT, UPDATE, DELETE) i na práci s tabulkami (CREATE TABLE, ALTER, DROP). ==== Vytvoření, změna struktury a mazání tabulek ==== === Vytvoření tabulky === CREATE TABLE jméno_tabulky ( jméno_sloupce_1 datový_typ [DEFAULT výraz] [integritní omezení], ... jméno_sloupce_N datový_typ [DEFAULT výraz] [integritní omezení], další_integritní_omezení ) CREATE TABLE kniha( \\ kniha_id int, \\ nazev varchar(100), \\ autor varchar(100), \\ pocet_stran int, \\ vydani int ); **Primární klíč:** CREATE TABLE kniha( \\ kniha_id int PRIMARY KEY, \\ nazev varchar(100), \\ autor varchar(100), \\ pocet_stran int, \\ vydani int ); **Přidání kontroly nevyplněné hodnoty (NOT NULL) a kontroly hodnoty (CHECK):** CREATE TABLE kniha( \\ kniha_id int PRIMARY KEY, \\ nazev varchar(100) NOT NULL, \\ autor varchar(100), \\ pocet_stran int CHECK (pocet_stran > 0), \\ vydani int ); **Kontrola více hodnot (možno kontrolovat i např. součty a rozdíly hodnot polí):** CREATE TABLE kniha( \\ kniha_id int PRIMARY KEY, \\ nazev varchar(100) NOT NULL, \\ autor varchar(100), \\ pocet_stran int, \\ vydani int, \\ CONSTRAINT con1 CHECK (pocet_stran > 0 AND vydani > 0) ); **Unikátní hodnota:** CREATE TABLE kniha( \\ kniha_id int PRIMARY KEY, \\ nazev varchar(100) NOT NULL, \\ autor varchar(100), \\ pocet_stran int, \\ vydani int, \\ UNIQUE(nazev) ); **Cizí klíče:** CREATE TABLE kniha( \\ kniha_id int PRIMARY KEY, \\ nazev varchar(100) NOT NULL ); CREATE TABLE vytisk( \\ vytisk_id integer PRIMARY KEY, \\ kniha_id integer, \\ police integer, \\ FOREIGN KEY (kniha_id) REFERENCES kniha (kniha_id) ); Další varianty a příklady lze najít v [[http://www.postgresql.org/docs/9.0/static/sql-createtable.html|dokumentaci PostgreSQL]]. === Změna struktury tabulky === ALTER TABLE jméno_tabulky ( ADD COLUMN jméno_sloupce typ [integritní omezení], ALTER COLUMN jméno_sloupce typ [integritní omezení], DROP COLUMN jméno_sloupce, RENAME COLUMN jméno_sloupce TO nové_jméno_sloupce, RENAME TO nové_jméno_tabulky ) ALTER TABLE kniha ADD COLUMN vydavatel varchar(50);\\ ALTER TABLE kniha DROP COLUMN vydavatel;\\ ALTER TABLE kniha RENAME COLUMN pocet_stran TO pages;\\ ALTER TABLE kniha RENAME TO books;\\ ALTER TABLE kniha ALTER COLUMN autor TYPE varchar(120); Další varianty a příklady lze najít v [[http://www.postgresql.org/docs/9.0/static/sql-altertable.html|dokumentaci PostgreSQL]]. === Mazání tabulky === DROP TABLE jméno_tabulky DROP TABLE kniha ==== Vkládání, úprava a mazání údajů v tabulkách ==== **Vkládání nových dat do tabulky** INSERT INTO jméno_tabulky (seznam sloupců) VALUES (hodnoty v pořadí uvedených sloupců) Seznam sloupců je nepovinný, pokud není seznam uveden, berou se všechny sloupce v pořadí, v jakém byly vytvořeny při vytváření tabulky (CREATE TABLE). INSERT INTO kniha VALUES (1,'Myslime objektove v jazyku Java', 'Rudolf Pecinovsky', 576); \\ INSERT INTO kniha (kniha_id,nazev) VALUES (1,'Myslime objektove v jazyku Java'); **Úprava dat v tabulce** UPDATE jméno_tabulky SET sloupec = nová_hodnota WHERE podmínka UPDATE kniha SET vydani=2 where nazev='Myslime objektove v jazyku Java' v rámci jednoho příkazu UPDATE lze upravovat i několik sloupců najednou\\ UPDATE kniha SET vydani=2,pocet_stran=580 where nazev='Myslime objektove v jazyku Java' **Mazání dat v tabulce** DELETE FROM jméno_tabulky WHERE podmínka Smazání všech dat v tabulce \\ DELETE FROM kniha Smazání všech druhých vydání knih \\ DELETE FROM kniha WHERE vydani=2 ====== Další důležité odkazy ====== Pokorný J., Valenta M.: Databázové systémy (kap. 4.1, 4.2) /* [[http://www.tutorialspoint.com/dbms/relational_algebra.htm|Základy relační algebry]] */ [[http://www.postgresql.org/docs/9.0/static/sql-createtable.html|Dokumentace k create table pro PostgreSQL]] [[http://www.postgresql.org/docs/9.0/static/sql-insert.html|Dokumentace k insert pro PostgreSQL]] [[http://www.postgresql.org/docs/9.0/static/sql-delete.html|Dokumentace k delete pro PostgreSQL]] [[http://www.postgresql.org/docs/9.0/static/sql-update.html|Dokumentace k update pro PostgreSQL]] [[http://www.postgresql.org/docs/9.0/static/sql-select.html|Dokumentace k select pro PostgreSQL]] [[http://www.pgadmin.org/docs/|Dokumentace k pqadmin]]