Autor, Název akce Databázové systémy a SQL Daniel Klimeš 1 Autor, Název akce About me Daniel Klimeš, Databázové systémy a SQL 2 Daniel Klimeš • •Vzdělání: Obecná biologie •PGS: onkologie •Specializace: klinické databáze •Databáze ORACLE • • •klimes@iba.muni.cz •Kotlářská 2, budova 11 • • Autor, Název akce Databáze v biomedicíně Daniel Klimeš, Databázové systémy a SQL 3 Každé pondělí od 16:00 – do 17:40 Teoretická přednáška –> navazující praktické cvičení Praktická část : databáze ORACLE 11g Domácí cvičení: PostgreSQL Zakončení: zápočet – domácí úkol zkouška – praktický test, pomůcky bez omezení, časový limit Autor, Název akce Význam databáze pro analytika 4 Daniel Klimeš, Databázové systémy a SQL Matematicko - statistický skriptový SW R, Matlab, Maple, SPSS, SAS, programovací jazyk Databáze SQL Grafický statistický SW Statistika for Windows, MS Excel SPSS, SAS Rostoucí objem dat (miliony záznamů) • Potřeba matematického aparátu • Automatizace zpracování Předzpracování dat čištění dat popisná analýza Autor, Název akce Databázové systémy Daniel Klimeš, Databázové systémy a SQL 5 Relační databáze (RDBMS) Relace – termín z relační algebry Základ: tabulka sloupec = atribut/parametr řádek = popsaný objekt Databáze = systém provázaných tabulek ID pacienta Jmeno Prijmeni 1 Jan Novák 2 Jana Nová 3 Karel Starý ID pacienta Datum vysetreni Vysledek vysetreni 1 12.1.2011 39,5 1 15.3.2011 36,8 2 2.2.2011 37,5 Autor, Název akce Databázové systémy - Produkty Daniel Klimeš, Databázové systémy a SQL 6 Dle dostupnosti Komerční ORACLE - databáze* MS SQL server* DB2 MS ACCESS FOX PRO Freeware MySQL PostgreSQL Firebird Dle počtu uživatelů Jednouživatelské MS ACCESS FOX PRO Víceuživatelské ORACLE MS SQL DB2 MySQL PostgreSQL Firebird * Okleštěné verze jsou k dispozici zdarma Autor, Název akce Tabulka Daniel Klimeš, , Databázové systémy a SQL 7 Definovaná struktura, do které se vkládají záznamy Definují se sloupce • jméno • datový typ • text • číslo • datum • BLOB • doplňující vlastnosti ID pacienta Datum vysetreni Vysledek vysetreni 1 12.1.2011 39,5 1 15.3.2011 36,8 2 2.2.2011 37,5 Autor, Název akce Klíče Daniel Klimeš, Databázové systémy a SQL 8 ID pacienta Jmeno Prijmeni 1 Jan Novák 2 Jana Nová 3 Karel Starý ID pacienta Datum vysetreni Vysledek vysetreni 1 12.1.2011 39,5 1 15.3.2011 36,8 2 2.2.2011 37,5 Vybrané sloupce se označují jako klíče (keys) Primární klíč (primary key - PK)– 1až n sloupců jednoznačně identifikující řádek Cizí klíč (foreign key - FK) – identifikuje nadřazený řádek v rodičovské tabulce PK PK FK Autor, Název akce Přístup do databáze Daniel Klimeš, Databázové systémy a SQL 9 Klient = SW umožňující ověření uživatele a spouštění řídících příkazů Řídící příkazy = Structured Query Language – SQL • DDL - vytváření, změna, rušení objektů (tabulka, index, pohled, …) • CREATE / ALTER / DROP • DML • SELECT – získávání dat z databáze • INSERT – vkládání dat do databáze • DELETE – mazání dat v databázi • UPDATE – změna/aktualizace dat • transakční příkazy • COMMIT – potvrzení transakce • ROLLBACK – odvolání transakce Autor, Název akce ORACLE – databázový server Daniel Klimeš, Databázové systémy a SQL 10 Klient sqlplus – textový SQLDeveloper - grafický Network alias musí být definován na klientském počítači: TNS (Transparent Network Substrate) přístupné databáze jsou definované v lokálním souboru tnsnames.ora ../network/Admin Identifikace ORACLE databáze IP adresa + SID nebo síťový alias (network alias) SID = identifikace instance databáze na serveru Autor, Název akce SQL developer - připojení Daniel Klimeš, Obhajoba disertační práce 11 Autor, Název akce Sqlplus - připojení Daniel Klimeš, Databázové systémy a SQL 12 Připojení k databázi příkazová řádka – cmd sqlplus login@network_alias sqlplus student@TESTORCL Varianta s přímým zadáním hesla: sqlplus login/heslo@network_alias student/DBM753 Autor, Název akce PostgreSQL Daniel Klimeš, Databázové systémy a SQL 13 Klient • pgAdmin • Přihlášení k serveru • Nová databáze - matbi • Schémata – public • • psql • ve Windows nutné nastavení jazykové sady a fontu • Autor, Název akce SQL Daniel Klimeš, Databázové systémy a SQL 14 SQL jazyk • case insensitive • různá rozšíření v jednotlivých DB produktech • klíčová slova – pro názornost VELKÝM písmem • názvy objektů (tabulek, sloupců) • pouze alfanumerické znaky • první znak písmeno • omezená délka (ORACLE 32 znaků) • operátory • funkce • SQL příkazy – ve skriptu ukončeny defaultně středníkem (;) • komentáře odděleny - - nebo v bloku /* komentar */ Autor, Název akce SQL - SELECT Daniel Klimeš, Databázové systémy a SQL 15 SELECT * FROM tabulka; -- všechny řádky i sloupce tabulky /* vybrané sloupce, všechny řádky */ SELECT sloupec1, sloupec2, sloupec1 + sloupec2 AS soucet FROM tabulka; /* všechny sloupce, vybrané řádky */ SELECT * FROM tabulka WHERE sloupec1 = 1 AND sloupec2 > 10 AND sloupec3 < sloupec 4 --Sumární výstupy SELECT COUNT(*) FROM tabulka -- počet řádků v tabulce -- Agregační funkce SELECT SUM(sloupec1), AVG(sloupec2), MIN(sloupec3), MAX(sloupec4) FROM tabulka SELECT COUNT(*), sloupec1 FROM tabulka – nelze Autor, Název akce Cvičení 1 Daniel Klimeš, Databázové systémy a SQL 16 Tabulka STUDIES •Kolik má sloupců? • •Kolik má řádků? • •Minimum, maximum a průměrná hodnota sloupce study_id? • •Minimum, maximum hodnota sloupce study_name pouze řádky is_active= 1 ? •Výpis sloupců study_name, description,všechny řádky, dle abecedy podle study_name Autor, Název akce GROUP BY Daniel Klimeš, Databázové systémy a SQL 17 Seskupení položek SELECT sloupec, count(*), MAX(sloupec2), MIN(sloupec2) FROM tabulka GROUP BY sloupec; SELECT sloupec, count(*), MAX(sloupec2), MIN(sloupec2) FROM tabulka WHERE sloupec2 > 1 and … GROUP BY sloupec; SELECT sloupec, count(*), MAX(sloupec2), MIN(sloupec2) FROM tabulka GROUP BY sloupec HAVING count(*) > 1 Autor, Název akce Cvičení 2 Daniel Klimeš, Databázové systémy a SQL 18 Výpis počtu studií pro jednotlivé verze trialdb –> 2 sloupce trialdbversion, počet řádků To samé pouze pro is_active = 1 Výpis principle_investigator, kteří mají na starosti více jak 5 aktivních studií principle_ivestigator, počet studií Autor, Název akce CREATE TABLE Daniel Klimeš, Databázové systémy a SQL 19 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 20 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 21 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 22 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 Domácí úkol Daniel Klimeš, Databázové systémy a SQL 23 •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í