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 jmeno = ‘Jan’ ; 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 prednaska_05_10 jako číslo 2. 2.Nastavte tento sloupec na 1 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é (sloupec prednaska_05_10 je prázdný) Autor, Název akce Ukázka transakčního více uživatelského chování Daniel Klimeš, Databázové systémy a SQL 12 Uživatel 1: BEGIN TRANSACTION DELETE FROM tabulka; Uživatel 2: UPDATE tabulka SET sloupec = Uživatel 3: SELECT * FROM tabulka …. Uživatel 1: COMMIT; 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 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 Ø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 Domácí úkol Daniel Klimeš, Databázové systémy a SQL 20 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í? •