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 C:\Users\klimes\Documents\vyuka\matbi\2014\oracle.jpg 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@ORCLTEST 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 • 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 •různá rozšíření v jednotlivých DB produktech • 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/Agregační funkce SELECT COUNT(*) FROM tabulka -- počet řádků v tabulce 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 Domácí úkol Daniel Klimeš, Databázové systémy a SQL 19 1.Přečíst kapitolu 1 ve skriptech http://portal.matematickabiologie.cz/index.php?pg=zaklady-informatiky-pro-biology--databazove-syste my-v-biomedicine 1. 2.Instalace Postgresql 3.