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 manager • textový sloupec study_name • datumový sloupec managed_since •Naplňte první 2 sloupce tabulky záznamy z tabulky studies (principal_investigator, study_name) •Smažte řádky s managerem Chroust 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 •Do řádků bez managera zapište svoje příjmení a sloupce managed_since dnešní datum • Do zbylých řádků vložte datum 1.1.2000 • 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 • Které datum následuje měsíc po 28.2. 2013, 31.1. 2012 a po 31.1.2013 Kolik dnů, týdnů, měsíců, roků řídí projekty jednotliví lidé? 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 Logické operátory Daniel Klimeš, Databázové systémy a SQL 14 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 15 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 MODIFIKÁTOR DISTINCT Daniel Klimeš, Databázové systémy a SQL 16 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 Domácí úkol Daniel Klimeš, Databázové systémy a SQL 17 Prostudujte: 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í