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: Screeningové projekty v onkologii, Národní zdravotní registry •Databáze MS SQL, PostgreSQL, ORACLE •Zaměstnání: Datové centrum ÚZIS • • •klimes@iba.muni.cz • • Autor, Název akce Databáze v biomedicíně Daniel Klimeš, Databázové systémy a SQL 3 Lectures Každou středu/wednesday od 10:00 – do 11:40 Teoretická přednáška –> navazující praktické cvičení Praktická část/practical tasks : PostgreSQL, Domácí cvičení/homework: PostgreSQL Zakončení: zápočet/credit – domácí úkol/homework zkouška/exam – praktický test, pomůcky bez omezení, časový limit Autor, Název akce Rozsah předmětu Daniel Klimeš, Databázové systémy a SQL 4 ØZáklady SQL ØSELECT, UPDATE, DELETE, INSERT ØFunkce a operátory ØZáklady agregace dat ØGROUP BY, HAVING, AVG, SUM, … ØPráce s více tabulkami ØJOIN, UNION ØZanořené dotazy ØWindow funkce ØRank, lag, lead, OVER ØVyhledávání v textu ØLike, regulární výrazy Autor, Název akce Kdy zpracovávat data v databázi 5 Daniel Klimeš, Letní škola MATBI 2015 1.Data jsou primárně v databázi uložena 2.Zpracováváme objemná data v řádu sto tisíc záznamů a více 3.Zpracování dat plánujeme provádět opakovaně 4.S daty bude pracovat více uživatelů 5.Způsob zpracování potřebujete dokladovat 6.Nechcete se zbláznit z excelu Relační databáze x NoSQL databáze Autor, Název akce Význam databáze pro analytika 6 Daniel Klimeš, Databázové systémy a SQL Matematicko - statistický skriptový SW R, Matlab, Maple, programovací jazyk Databáze SQL Statistický SW Statistika for Windows, R, SPSS, SAS, MS Excel •Předzpracování dat •Čištění dat •Popisná analýza •Filtrování •Propojení dat •Agregace dat • Autor, Název akce Databázové systémy Daniel Klimeš, Databázové systémy a SQL 7 Relační databáze (Relational database management system - RDBMS) Relace/relation – termín z relační algebry Základ: tabulka/table - sloupec/column = atribut/parametr - řádek/row = popsaný objekt Databáze = systém provázaných tabulek PatientID FirstName LastName 1 Jan Novák 2 Jana Nová 3 Karel Starý PatientID ExaminationDate Result 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 8 Dle dostupnosti Komerční ORACLE* MS SQL* DB2 MS ACCESS FOX PRO Freeware MySQL PostgreSQL Firebird Dle počtu uživatelů Jednouživatelské/single user MS ACCESS FOX PRO Víceuživatelské/multiuser ORACLE MS SQL DB2 MySQL PostgreSQL Firebird * Okleštěné verze jsou k dispozici zdarma https://en.wikipedia.org/wiki/List_of_relational_database_management_systems Autor, Název akce Tabulka/Table Daniel Klimeš, , Databázové systémy a SQL 9 Definovaná struktura, do které se vkládají záznamy Definují se sloupce/columns • jméno/name • datový typ /datatype • text • číslo/number • datum/date • BLOB • doplňující vlastnosti/other properties PatientID ExaminationDate Result 1 12.1.2011 39,5 1 15.3.2011 36,8 2 2.2.2011 37,5 Definice tabulky předchází načtení dat A table must be created before data import Autor, Název akce Klíče/keys Daniel Klimeš, Databázové systémy a SQL 10 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)– 1 až n sloupců jednoznačně identifikující řádek (unique values) 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/Access to a database Daniel Klimeš, Databázové systémy a SQL 11 Klient/client = SW umožňující ověření uživatele a spouštění řídících příkazů Ovladač/Driver = komponenta klienta Řídící příkazy/commands = Structured Query Language – SQL • DDL = data definiton language • vytváření, změna, rušení objektů (tabulka, index, pohled, …) • CREATE / ALTER / DROP • DML = data manipulation language • 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 / commands for transactions • COMMIT – potvrzení transakce • ROLLBACK – odvolání transakce Autor, Název akce SQL Daniel Klimeš, Databázové systémy a SQL 12 SQL jazyk (Structured Query Language) • case insensitive • klíčová slova /keywords – pro názornost VELKÝM písmem/uppercase • názvy objektů (tabulek, sloupců) / name of objects • pouze alfanumerické znaky / only alfanumeric • první znak písmeno / first letter • omezená délka (ORACLE 32 znaků) / length limit • operátory / operators • funkce / function • různá rozšíření v jednotlivých DB produktech • SQL příkazy – ve skriptu ukončeny defaultně středníkem (;) • komentáře / comments odděleny - - nebo v bloku /* komentar */ Autor, Název akce Cvičení Daniel Klimeš, Databázové systémy a SQL 13 Instalace PostgreSQL https://www.enterprisedb.com/downloads/postgres-postgresql-downloads Autor, Název akce Instalace klienta Daniel Klimeš, Databázové systémy a SQL 14 Klient/client • pgAdmin – specifický pro postgres • Přihlášení k serveru/connection • Nová databáze/new database - matbi • Schéma/schema – public •Dbeaver •Univerzální • • https://dbeaver.io/download/ Community edition server je 147.251.145.6 db:ucebnarcx studentucebna / RCX2019ucebna Autor, Název akce SQL - SELECT Daniel Klimeš, Databázové systémy a SQL 15 SELECT * FROM tabulka; -- všechny řádky i sloupce tabulky / all rows , all columns /* vybrané sloupce, všechny řádky /selected columns */ SELECT sloupec1, sloupec2, sloupec1 + sloupec2 AS soucet FROM tabulka; /* všechny sloupce, vybrané řádky / selected rows, all columns */ SELECT * FROM tabulka WHERE sloupec1 = 1; SELECT * FROM tabulka WHERE sloupec2 = 'Jan' ; -- text do apostrofů SELECT * FROM tabulka WHERE sloupec1 = 1 AND sloupec2 > 10 AND sloupec3 < sloupec 4 --Setřídění výstupu (ORDER BY) SELECT * FROM tabulka ORDER BY sloupec1; -- vzestupné třídění /ascending SELECT * FROM tabulka ORDER BY sloupec2 DESC; -- sestupné třídění / descending SELECT * FROM tabulka ORDER BY sloupec1, sloupec2 DESC --kombinace Autor, Název akce SQL - SELECT Daniel Klimeš, Databázové systémy a SQL 16 --Sumární výstupy = Agregační funkce SELECT COUNT(*) FROM tabulka -- počet řádků v tabulce / number of rows /* suma, aritmetický průměr, minimum, maximum */ SELECT SUM(sloupec1), AVG(sloupec2), MIN(sloupec3), MAX(sloupec4) FROM tabulka SELECT COUNT(*), sloupec1 FROM tabulka – nelze / error Autor, Název akce Cvičení 2 / Task 2 Daniel Klimeš, Databázové systémy a SQL 17 Spuštění prvního skriptu (skript1.sql) / Run script Tabulka/Table STUDENT • Zobrazte celý obsah tabulky / select all data from table • Zobrazte jen jméno a příjmení / select only firstname and lastname columns • Setřiďte výstup podle studia, jména / order by study • Kolik má tabulka řádků? / how many rows are in the table? • Vyberte pouze svůj záznam (své UČO) / select only your UCO row • Minimum, maximum a průměrná hodnota sloupce UCO? Min, max , average of UCO • Minimum, maximum z jména / min, max of firstname • Kolik máme v seznamu mužů? / How many men are in the table? •