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/datatypes Daniel Klimeš, Databázové systémy a SQL 3 https://www.postgresql.org/docs/10.5/static/datatype.html Skupina / Group Name of datatype Číslo numeric(x,y) Text (omezený) varchar(x) Text neomezený text Datum /date date Datum + čas / date with time timestamp Časový interval/time 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); https://www.postgresql.org/docs/10/static/ddl.html Autor, Název akce DATA MANIPULATION LANGUAGE •DML Daniel Klimeš, Databázové systémy a SQL 6 Autor, Název akce INSERT DATA Daniel Klimeš, Databázové systémy a SQL 7 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, lastname FROM student WHERE sex= ‘muž’ ; Autor, Název akce UPDATE, DELETE Daniel Klimeš, Databázové systémy a SQL 8 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; https://www.postgresql.org/docs/10/static/dml.html Autor, Název akce TRANSAKCE Daniel Klimeš, Databázové systémy a SQL 9 TRANSAKCE/transaction = set DML příkazů/commands – všechny nebo žádný (all or none) Ukončení transakce/ end of transaction COMMIT; = potvrzení změn or ROLLBACK; = zrušení/cancel 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 !! Zahájení transakce / start transaction BEGIN TRANSACTION https://www.postgresql.org/docs/10/static/sql-begin.html Autor, Název akce NULL, prázdná hodnota Daniel Klimeš, Databázové systémy a SQL 10 https://www.postgresql.org/docs/10/static/functions-comparison.html Autor, Název akce Cvičení 1 / task 1 Daniel Klimeš, Databázové systémy a SQL 11 • Vytvořte tabulku (vaše příjmení jako název tabulky) create table (your lastname as table name) • textový sloupec firstname • textový sloupec lastname • datumový sloupec date_of_enrollment • Vložte záznam, naplňte první 2 sloupce tabulky svým jménem (firstname, lastname) insert your name as a record to the table (without date) • Doplňte datum zápisu na aktuální datum fill in date of enrollment to an existing row • • Přidejte libovolný další řádek insert another record • • Ověřte počet řádků v tabulce check the number of rows • • Smažte řádek se svým jménem remove the record with your name Autor, Název akce Cvičení 2 / task 2 Daniel Klimeš, Databázové systémy a SQL 12 1.Přidejte do tabulky student sloupec birthdate jako datum Add column birthdate to the table student 2. 2.Nastavte tento sloupec na datum narození u svého jména (UCO) Set a birthdate for your UCO 3. 3.Vytvořte primární klíč na sloupec uco create primary key on column uco 4. 4.Zkuste vložit pomocí INSERT duplicitně své UČO Try insert duplicate UCO 5. 5.Smažte nepřítomné (birthdate je prázdný - NULL) Remove rows where birthdate is empty Autor, Název akce FUNKCE A OPERÁTORY •Functions and operators Daniel Klimeš, Databázové systémy a SQL 13 Autor, Název akce How to test operator or function Daniel Klimeš, Databázové systémy a SQL 14 ØSELECT function() ØSELECT function(parameter) ØSELECT function(parameter1, parameter2) ØSELECT ABS(-5) Ø Ø ØSELECT 1/2 ØSELECT 1/2.0 Ø Ø Ø Ø Ø Autor, Název akce Operators and functions for number Daniel Klimeš, Databázové systémy a SQL 15 Function Description +,-,*,/ 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 See http://www.postgresql.org/docs/10.5/static/functions.html Autor, Název akce Operators and function for text Daniel Klimeš, Databázové systémy a SQL 16 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 Operators and functions for datetime Daniel Klimeš, Databázové systémy a SQL 17 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 Operators and functions for datetime Daniel Klimeš, Databázové systémy a SQL 18 DATE_PART(text, timestamp) Extrakce component 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 Other operatos and functions Daniel Klimeš, Databázové systémy a SQL 19 [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 / task 3 Daniel Klimeš, Databázové systémy a SQL 20 ØTextové funkce ØRozdělte jméno a příjmení v tabulce student do vlastních sloupců / split firstname and lastname in the table student ØDatumové funkce ØPřidejte do tabulky student sloupec birthdate s datovým typem date / Add column birthdate to the table student ØNastavte hodnotu sloupce u svého jména na své datum narození / set the column birthdate for your row ØSELECT DATE_PART('year', AGE(CURRENT_DATE,birth)) FROM student Ø Ø Ø Ø Autor, Název akce Cvičení 4 / task 4 Daniel Klimeš, Databázové systémy a SQL 21 Zjistěte , který den v týdnu odpovídá datum narození / which weekday is your birthday https://www.postgresql.org/docs/10.5/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 / task 5 Daniel Klimeš, Databázové systémy a SQL 22 Přidejte do tabulky student sloupce / add columns to the table student •Age numeric(2) •facebook varchar(1) •instagram jako varchar(1) •other varchar(500) Doplňte hodnoty sloupců u svého řádku / fill in columns for your row (uco) •výpočet věku k dnešnímu dni (dle data narozeni) / age from column birthdate •Používání sociálních sítí / using social network (Y/N) •Jiné aplikace jako text / other apps as text Autor, Název akce Cvičení 6 / task 6 Daniel Klimeš, Databázové systémy a SQL 23 Přidejte do tabulky student sloupec / add colums to the table student •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 studies / split value in the column studies to the new columns 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 / task 7 Daniel Klimeš, Databázové systémy a SQL 24 •Vytvořte si na lektorovi kopii tabulky student, dále na ní vyzkoušejte create copy of the table student and try • •Sloupec příjmení převeďte na velká písmena / convert lastname to uppercase •Smažte řádky s lichým/sudým ročníkem / delete row •Odháčkujte sloupec jmeno •Zkraťe jméno na 1. znak a tečku / extract first letter from firstname and add dot • • •Kolikátého bude za 7 let 7 měsíců a 7 dní / what date will be after 7 years 7 months and 7 days •Ověřte, zda ženy mají v příjmení „ová“ / compare the end of lastname with sex • • •Vymažte řádky ze své tabulky / delete all rows from your table •Zrušte svoji tabulku / remove your table • Autor, Název akce Domácí úkol / homework Daniel Klimeš, Databázové systémy a SQL 25 Prostudujte/read: Kapitola 2 a 3 skript + http://www.postgresql.org/docs/10.5/static/functions.html 9.1, 9.2, 9.3, 9.4, 9.8, 9.9 • use function REPLACE to remove/replace numbers from text ‘Harry P0tter goes 2 Brno’ •convert to a date ‘26092018’ (use TO_DATE) •Extract first 3 letters from text ‘452Kampus’ •