Autor, Název akce Databázové systémy a SQL Lekce 2 Daniel Klimeš 1 Autor, Název akce DATA DEFINITION LANGUAGE •DDL Daniel Klimeš, Databázové systémy a SQL 2 Autor, Název akce Datové typy Daniel Klimeš, Databázové systémy a SQL 3 https://www.postgresql.org/docs/9.5/static/datatype.html Skupina Název Číslo numeric(x,y) Text (omezený) varchar(x) Text neomezený text Datum date Datum + čas timestamp Časový interval interval Obsah sloupců tabulky určuje přiřazený tzv. datový typ Autor, Název akce CREATE TABLE/DROP TABLE Daniel Klimeš, Databázové systémy a SQL 4 ORACLE CREATE TABLE jmeno ( text VARCHAR2(200), cislo NUMBER(9,1), datum DATE ); PostgreSQL CREATE TABLE jmeno ( text Varchar(200), cislo Numeric(5,2), datum Timestamp ); DROP TABLE tabulka; Autor, Název akce ALTER TABLE Daniel Klimeš, Databázové systémy a SQL 5 •ALTER TABLE tabulka ADD sloupec typ; •ALTER TABLE tabulka DROP sloupec; •ALTER TABLE tabulka ADD PRIMARY KEY (sloupec); Autor, Název akce INSERT Daniel Klimeš, Databázové systémy a SQL 6 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 uco, prijmeni FROM student WHERE pohlavi = ‘muž’ ; Autor, Název akce UPDATE, DELETE Daniel Klimeš, Databázové systémy a SQL 7 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 8 TRANSAKCE = sada DML příkazů – všechny nebo žádný Ukončení transakce COMMIT; = potvrzení změn 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 PGSQL automatický commit !! Nutné nejprve napsat BEGIN TRANSACTION Autor, Název akce NULL, prázdná hodnota Daniel Klimeš, Databázové systémy a SQL 9 Autor, Název akce Cvičení 1 Daniel Klimeš, Databázové systémy a SQL 10 •Vytvořte tabulku (vaše příjmení jako název tabulky) • 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 Cvičení 2 Daniel Klimeš, Databázové systémy a SQL 11 1.Přidejte do tabulky student sloupec dat_nar jako datum 2. 2.Nastavte tento sloupec na datum narození u svého jména (UCO) 3. 3.Vytvořte primární klíč na sloupec uco 4. 4.Zkuste vložit pomocí INSERT duplicitně své UČO 5. 5.Smažte nepřítomné (dat_nar je prázdný - NULL) Autor, Název akce Ukázka transakčního víceuživatelského chování Daniel Klimeš, Databázové systémy a SQL 12 Všichni: BEGIN TRANSACTION INSERT INTO student (uco) VALUES(cislo) Všichni: SELECT uco FROM student Ucitel: DELETE FROM student; …. Vsichni: COMMIT; Úklid? Připojení na lektorský počítač 147.251.145.67 Uživatel postgres Autor, Název akce FUNKCE A OPERÁTORY • Daniel Klimeš, Databázové systémy a SQL 13 Autor, Název akce Operátory a funkce – práce s čísly Daniel Klimeš, Databázové systémy a SQL 14 Funkce Popis +,-,*,/ Aritmetické operace 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 Operátory a funkce – práce s textem Daniel Klimeš, Databázové systémy a SQL 15 Funkce Popis || Spojení textových řetězců 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 datumy Daniel Klimeš, Databázové systémy a SQL 16 Funkce Popis CURRENT_DATE Aktuální datum Operátor CURRENT_TIMESTAMP NOW() Aktuální datum a čas Operátor Funkce() 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 AGE(datum, datum) Rozdíl datumů PG interval '1 year 2 months 3 days 4 hours 5 minutes 6 seconds' Datový typ, možnost přičítat, odčítat PG ADD_MONTHS(datum, počet) Přičtení měsíců ORACLE Autor, Název akce Operátory a funkce – práce s datumy Daniel Klimeš, Databázové systémy a SQL 17 DATE_PART(text, timestamp) Extrakce komponent PG Century, day, dow, doy, hour, isoyear, minute, month, second, week, year TO_CHAR, TO_DATE Konverze Datum ó Text PG, ORACLE Dd,mm,yyyy, HH, HH24, mi, ss, Month, Day, D, DDD, W, WW, IYYY, IDDD, IW EXTRACT(co FROM interval) Extrakce z intervalu PG Autor, Název akce Operátory a funkce Daniel Klimeš, Databázové systémy a SQL 18 [NOT] IN (hodnota, hodnota, …) Rovnost [NEROVNOST] se skupinou hodnot COALESCE Vrací první NOT NULL argument CASE WHEN podm END Podmíněný výraz Autor, Název akce Cvičení 3 Daniel Klimeš, Databázové systémy a SQL 19 ØSELECT funkce() Ø ØČíselné funkce ØSELECT 1/2 X SELECT 1/2.0 Ø ØTextové funkce ØRozdělte jméno a příjmení v tabulce student do vlastních sloupců ØDatumové funkce ØPřidejte do tabulky student sloupec dat_nar s datovým typem date ØNastavte hodnotu sloupce u svého jména na své datum narození ØSELECT DATE_PART('year', AGE(CURRENT_DATE,dat_nar)) FROM student Ø Ø Ø Ø Autor, Název akce Cvičení 4 Daniel Klimeš, Databázové systémy a SQL 20 Zjistěte , který den v týdnu odpovídá datum narození https://www.postgresql.org/docs/9.6/static/functions-formatting.html SELECT TO_CHAR(TO_DATE('2018-01-01','yyyy-mm-dd'), 'DAY') SELECT TO_CHAR(TO_DATE('2018-02-30)','yyyy-mm-dd'), 'DAY') ? SELECT TO_CHAR(TO_DATE('2018-02-30)','yyyy-mm-dd'), 'dd.mm.yyyy') SELECT TO_CHAR(TO_DATE('2017-01-01)','yyyy-mm-dd'), 'iyyy') ISO rok, týden – „zlomový“ čtvrtek SELECT TO_CHAR(TO_DATE('2017-01-01)','yyyy-mm-dd'), 'iw') Autor, Název akce Cvičení 5 Daniel Klimeš, Databázové systémy a SQL 21 Přidejte do tabulky student sloupce (1x) •vek jako numeric(2) •facebook jako varchar(1) •twitter jako varchar(1) •jine jako varchar(500) Doplňte hodnoty sloupců u svého řádku (všichni) •výpočet věku k dnešnímu dni (dle data narozeni) •Používání sociálních sítí (A/N) •Jiné aplikace jako text Autor, Název akce Cvičení 6 Daniel Klimeš, Databázové systémy a SQL 22 Přidejte do tabulky student sloupce (1x) •Fakulta jako varchar(3) •Stupen jako varchar(1) •Obor jako varchar(10) •Semestr jako numeric(1) •Rocnik jako numeric(1) •Pocet_semestru jako numeric(2) •Rocnik_celkem jako numeric(2) Naplňte sloupce rozdělením sloupce studium Pomocí sloupců Stupeň, Rocnik, Semestr Sloupec pocet_semestru nastavte jako celkový dosud absolvovaný počet semestrů (6 semestrů za bakalářské studium) Sloupec rocnik_celkem nastavte jako celkový aktuální ročník (3 roky za bakalářské studium) Autor, Název akce Cvičení 7 Daniel Klimeš, Databázové systémy a SQL 23 •Vytvořte si na lektorovi kopii tabulky student, dále na ní vyzkoušejte • •Sloupec příjmení převeďte na velká písmena •Smažte řádky s lichým/sudým ročníkem •Odháčkujte sloupec jmeno •Zkraťe jméno na 1. znak a tečku • • •Kolikátého bude za 7 let 7 měsíců a 7 dní •Ověřte, zda ženy mají v příjmení „ová“ • • •Vymažte řádky ze své tabulky •Zrušte svoji tabulku • Autor, Název akce Domácí úkol Daniel Klimeš, Databázové systémy a SQL 24 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 • 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í? •