Autor, Název akce Databázové systémy a SQL Lekce 2 Daniel Klimeš 1 Autor, Název akce CREATE TABLE Daniel Klimeš, Databázové systémy a SQL 2 Vytvoření tabulky (ORACLE) Ø DDL příkazem Ø v grafickém prostředí CREATE TABLE jmeno ( text VARCHAR2(200), cislo NUMBER(9,1), datum DATE ); jmeno = do 30 znaků (písmena, čísla, podtržítko) bez mezer, začíná písmenem Řádkování příkazu – nepovinné, pouze pro lepší čitelnost PostgreSQL CREATE TABLE jmeno ( text Varchar(200), cislo Numeric(5,2), datum Timestamp ); Autor, Název akce INSERT Daniel Klimeš, Databázové systémy a SQL 3 INSERT INTO tabulka (sloupec1, sloupec2, sloupec3) VALUES (cislo, ‘text’, TO_DATE (‘datum’, ‘dd.mm.yyyy’)); INSERT INTO jmeno (CISLO, TEXT, DATUM) VALUES (2.3,’testovací řetězec’, TO_DATE (’05.03.2011’,’dd.mm.yyyy’)); INSERT INTO tabulka (sloupec1, sloupec2, sloupec3) SELECT sloupec1,sloupec2, sloupec3 FROM tabulka2; INSERT INTO jmeno (cislo, text) SELECT study_id, text FROM studies WHERE is_active = 2; INSERT INTO jmeno (cislo, text) SELECT MAX(study_id), principal_investigator FROM studies GROUP BY principal_investigator Autor, Název akce UPDATE, DELETE Daniel Klimeš, Databázové systémy a SQL 4 UPDATE tabulka SET sloupec = hodnota; UPDATE jmeno SET cislo = cislo+1; UPDATE tabulka SET sloupec = hodnota WHERE sloupec2 = hodnota; UPDATE jmeno SET datum = SYSDATE WHERE text = ‘Klimeš’; DELETE FROM tabulka; DELETE FROM jmeno; DELETE FROM tabulka WHERE …; DELETE FROM jmeno WHERE cislo > 5; Autor, Název akce TRANSAKCE Daniel Klimeš, Databázové systémy a SQL 5 TRANSAKCE = sada DML příkazů – všechny nebo žádný Transakci zahajuje první příkaz Ukončení transakce COMMIT; = potvrzení změn (DDL příkazy => automatický commit) ROLLBACK; = zrušení změn Nepotvrzené transakce nevidí ostatní, brání provedení změn jiných uživatelů (zamykání sloupců, řádků, tabulek) Co nejkratší transakce! V PGADMIN automatický commit !! Autor, Název akce Cvičení 1 Daniel Klimeš, Databázové systémy a SQL 6 •Vytvořte tabulku (vaše příjmení jako název tabuky) • textový sloupec jmeno • textový sloupec prijmeni • datumový sloupec datum_zapisu •Vložte záznam, naplňte první 2 sloupce tabulky svým jménem (jmeno, prijmeni) •Doplňte datum zápisu na aktuální datum • • Přidejte libovolný další řádek • • Ověřte počet řádků v tabulce • • Smažte řádek se svým jménem Autor, Název akce Operátory a funkce Daniel Klimeš, Databázové systémy a SQL 7 +,- Sčítání, odečítání *,/ Násobení, dělení =, <>, >=, <= Rovnost, nerovnost [NOT] IN (hodnota, hodnota, …) Rovnost [NEROVNOST] se skupinou hodnot || Spojení textových řetězců NOT, AND, OR Negace, logický součin, logický součet TRUNC(x,[n]) Odstranění desetinných míst (odstranění časové frakce z datumu) COALESCE Vrací první NOT NULL argument GREATEST / LEAST Vrací největší/nejmenší argument DECODE Nahrazování hodnot ORACLE CASE WHEN podm END Podmíněný výraz NVL, NVL2 Nahrazení hodnoty NULL ORACLE Autor, Název akce Operátory a funkce – práce s datumy Daniel Klimeš, Databázové systémy a SQL 8 SYSDATE Aktuální datum a čas ORACLE CURRENT_DATE Aktuální datum PG CURRENT_TIMESTAMP NOW() Aktuální datum a čas PG TO_CHAR() Konverze na text TO_DATE() Konverze na datum Datum +- počet dnů Přičítání, odečítání dnů Datum - datum Počet dnů mezi datumy (desetinná část udává časový rozdíl) MONTHS_BETWEEN(datum, datum) Rozdíl datumů ORACLE ADD_MONTHS(datum, počet) Přičtení měsíců ORACLE Autor, Název akce Operátory a funkce – práce s textem Daniel Klimeš, Databázové systémy a SQL 9 SUBSTR(text, od, počet) Vrací podřetězec textu dle pozice INSTR(text, subtext) Hledání podřetězce v textu, vrací pozici ORACLE STRPOS(text, subtext) PG REPLACE(text, puvodni, nove) Nahrazení podřetězce LOWER(text) Převod na malá písmena UPPER(text) Převod na velká písmena LTRIM(text), RTRIM(text) Odstranění mezer zleva zprava LENGTH(text) Délka řetězce TRANSLATE(text, znaky,znaky) Nahrazení po znacích Autor, Název akce Operátory a funkce – práce s čísly Daniel Klimeš, Databázové systémy a SQL 10 ABS(cislo) Absolutní hodnota SIN(cislo), COS(cislo), TAN(cislo) Číslo v radiánech POWER(cislo,exp) Mocnina SQRT(cislo) Druhá odmocnina MOD(cislo, cislo) Zbytek po dělení LN(cislo) Přirozený logaritmus LOG(cislo) Dekadický logaritmus EXP(x) ex ROUND(x,[n]) zaokrouhlení CEIL() zaokrouhlení nahoru Autor, Název akce NULL, prázdná hodnota Daniel Klimeš, Databázové systémy a SQL 11 Autor, Název akce Cvičení 2 Daniel Klimeš, Databázové systémy a SQL 12 • Vytvořte si pracovní kopii tabulky STUDIES • CREATE TABLE nazev AS SELECT * FROM studies • Do řádků bez managera zapište svoje příjmení • U neaktivních studií is_active = 0 nastavte DATE_TERMINATED na dnešní datum • Vypište názvy studií malými písmeny • Vypište první 3 znaky názvu studií • Vypište studie, které obsahují ve study_name písmeno x • • Kolik dnů, týdnů, měsíců, roků řídí projekty jednotliví lidé (DATE_START)? • Smažte řádek se study_id = 2 Autor, Název akce Operátory a funkce – agregační funkce Daniel Klimeš, Databázové systémy a SQL 13 COUNT() Počet AVG() Průměr MIN() Minimum MAX() Maximum STDDEV() Směrodatná odchylka SUM() Suma MEDIAN() Medián ORACLE Autor, Název akce MODIFIKÁTOR DISTINCT Daniel Klimeš, Databázové systémy a SQL 14 SELECT DISTINCT sloupec1 FROM tabulka; -- unikátní hodnoty sloupce SELECT DISTINCT sloupec1, sloupec2 FROM tabulka; -- unikátní kombinace sloupců SELECT COUNT(*), COUNT(sloupec), COUNT(DISTINCT sloupec) Počet všech řádků, všech NOT NULL řádků, unikátních hodnot FROM tabulka; Autor, Název akce Cvičení - agregace Daniel Klimeš, Databázové systémy a SQL 15 Zjistěte • Nejstarší studii, nejmladší studii • Průměrná délka u ukončených studií • Celkový počet studií, • Počet studií se zadaným datem zahájení (DATE_STARTED) • Počet manažerů • Vypište seznam manažerů Autor, Název akce Daniel Klimeš, Databázové systémy a SQL 16 • Vypište seznam studií, kde délka textu study_title < 40 znaků • Prohoďte u těchto pacientů study_name a study_title • Které studie mají název jen z velkých písmen? • Které studie mají více jak jednoho magera? Autor, Název akce Logické operátory Daniel Klimeš, Databázové systémy a SQL 17 AND, OR, NOT AND TRUE FALSE NULL TRUE TRUE FALSE NULL FALSE FALSE FALSE FALSE OR TRUE FALSE NULL TRUE TRUE TRUE TRUE FALSE TRUE FALSE NULL NOT TRUE = FALSE NOT FALSE = TRUE NOT NULL = NULL Autor, Název akce LOGICKÉ OPERÁTORY - cvičení Daniel Klimeš, Databázové systémy a SQL 18 X AND FALSE => FALSE X OR TRUE => TRUE FALSE AND FALSE OR TRUE => TRUE FALSE AND (FALSE OR TRUE) => FALSE 5 > 1 AND NULL IS NOT NULL OR 1 = 1 => TRUE Autor, Název akce Domácí úkol Daniel Klimeš, Databázové systémy a SQL 19 Prostudujte: Kapitola 2 a 3 skript + http://www.postgresql.org/docs/9.2/static/functions.html 9.1, 9.2, 9.3, 9.4, 9.8, 9.9 •Nainstalovat PostgreSQL •Vytvořit databázi matbi •Vytvořit tabulku student se sloupci • jmeno, prijmeni, datum_narozeni, rok_prijeti • Vložit řádek se svým jménem • Pomocí update prohoďte jméno a příjmení, převeďte vše na velká písmena, odstraňte diakritiku (ř -> r, č->c), vyberte iniciály (1. písmeno jméno + 1. příjmení) Kolik dnů uplynulo od vašeho narození •