Relacyjne Bazy Danych Oracle Prezentacja jest wspfinansowana przez

„Relacyjne Bazy Danych (Oracle)” Prezentacja jest współfinansowana przez Unię Europejską w ramach Europejskiego Funduszu Społecznego w projekcie pt. „Innowacyjna dydaktyka bez ograniczeń - zintegrowany rozwój Politechniki Łódzkiej zarządzanie Uczelnią, nowoczesna oferta edukacyjna i wzmacniania zdolności do zatrudniania osób niepełnosprawnych” Prezentacja dystrybuowana jest bezpłatnie Projekt współfinansowany przez Unię Europejską w ramach Europejskiego Funduszu Społecznego Politechnika Łódzka, ul. Żeromskiego 116, 90 -924 Łódź, tel. (042) 631 28 83 www. kapitalludzki. p. lodz. pl 1

SELECT Projection Selection Table 1 Join Table 1 Table 2 2

SELECT Kwerenda SELECT służy do pobierania informacji z bazy danych spełniających podane kryteria SELECT ename, sal FROM emp ename, sal – lista kolumn * - wszystkie kolumny emp – nazwa tabeli 3

Kwerendy Kwerenda SELECT 4

Ograniczanie wierszy PName Price Category Manufacturer Gizmo $19. 99 Gadgets Gizmo. Works Powergizmo $29. 99 Gadgets Gizmo. Works Single. Touch $149. 99 Photography Canon Multi. Touch $203. 99 Household Hitachi PName Price Category Manufacturer Gizmo $19. 99 Gadgets Gizmo. Works Powergizmo $29. 99 Gadgets Gizmo. Works SELECT * FROM Product WHERE category=‘Gadgets’ 5

Ograniczanie wierszy PName Price Category Manufacturer Gizmo $19. 99 Gadgets Gizmo. Works Powergizmo $29. 99 Gadgets Gizmo. Works Single. Touch $149. 99 Photography Canon Multi. Touch $203. 99 Household Hitachi SELECT PName, Price, Manufacturer FROM Product WHERE Price > 100 PName Price Manufacturer Single. Touch $149. 99 Canon Multi. Touch $203. 99 Hitachi 6

Ograniczanie wierszy Klauzula WHERE pozwala na ograniczanie wyników zapytania do wierszy spełniających kryteria SELECT * FROM emp WHERE empno>7700 7

Ograniczanie wierszy Klauzula WHERE Klauzulę umieszczamy po klauzuli FROM Warunek w klauzuli WHERE może składać się z wielu części SELECT * FROM emp WHERE empno>7700 AND empno<7900 8

Wyrażenia arytmetyczne SELECT last_name, salary + 300 FROM employees; 9

Wyrażenia arytmetyczne SELECT last_name, 12*salary*commission_pct FROM employees; 10

Ograniczanie wierszy Warunki można grupować używając nawiasów SELECT * FROM emp WHERE (empno>7700 AND empno<7900) OR empno=7934; 11

Ograniczanie wierszy Podstawowe operatory algebraiczne „=” równy „>” większy niż „<” mniejszy niż „>=” większy bądź równy „<=” mniejszy bądź równy „<>” różny 12

Ograniczanie wierszy Inne operatory BETWEEN – analogicznie do „>” AND „<” SELECT * FROM emp WHERE sal BETWEEN 1500 AND 2000 13

Ograniczanie wierszy Operator IN SELECT * FROM emp WHERE sal IN (1500, 1600, 3000, 950); 14

Ograniczanie wierszy Operator LIKE Pozwala sprawdzić wzorzec wartości: „%” oznacza dowolny ciąg znaków, „_” oznacza jeden znak SELECT * FROM emp WHERE ename LIKE '%R' 15

Ograniczanie wierszy Operator LIKE – przykłady ename like '%ER' ename LIKE '%E%' ename LIKE '%E_' 16

Ograniczanie wierszy Warunek wykorzystany po klauzuli WHERE jest ewaluowany przez bazę danych, zwrócone zostaną wiersze, dla których dany warunek jest prawdziwy Warunek nie musi zawierać nazw kolumn np: SELECT * FROM emp WHERE 1=1 Warunek zawsze prawdziwy 17

Tabela DUAL – metatabela w bazie danych Oracle służąca do wykonywania prostych operacji SELECT 2+2 FROM DUAL 18

Wartość specjalna NULL w danej kolumnie oznacza brak wartości Wartość NULL nie oznacza ani pustego ciągu znaków dla kolumn tekstowych ani wartości zero dla kolumn liczbowych NULL <> NULL 19

Ograniczanie kolumn Listę zwróconych kolumn można ograniczyć wpisując odpowiednie nazwy po słowie kluczowym SELECT ename, job, sal FROM emp WHERE empno>7700; 20

Ograniczanie wyników Operatory logiczne – pozwalają na łączenie wyników wielu warunków AND – oba warunki muszą być spełnione OR – jeden z warunków musi być spełniony NOT – operator jednoargumentowy – negowanie listy wyników 21

SELECT DISTINCT Klauzula DISTINCT pozwala na wybranie tylko wierszy zawierających unikatową wartość jednej lub więcej kolumn SELECT DISTINCT job FROM emp; 22

SELECT DISTINCT Klauzula DISTINCT zastosowana do wielu kolumn SELECT DISTINCT job, sal FROM emp; 23

Użycie operatora IS NULL SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL; 24

Aliasy SELECT last_name "Name" , salary*12 AS "Annual Salary” FROM employees; 25

Funkcje pozwalają na zmianę sposobu prezentacji wyniku, lub wykonanie operacji na jednej lub więcej kolumnach przed prezentacją wyniku SELECT ename, LENGTH(job) FROM emp; 26

Funkcje można stosować do kolumn, większość funkcji występuje tylko dla kolumn danego typu Wywołanie funkcji na typie danych, dla którego nie została ona określona powoduje błąd 27

Funkcje Wybrane funkcje zdefiniowane dla ciągów znaków CONCAT (wartość1, wartość2) SUBSTR(wartość, początek, koniec) LENGTH(wartość) LPAD(wartość, długość, znak)/RPAD(wartość, długość, znak) UPPER(wartość)/LOWER(wartość) REVERSE(wartość) INITCAP(wartość) 28

Funkcje SELECT last_name ||' is a '|| job_id AS "Employee Details” FROM employees; 29

Funkcje Wybrane funkcje dla wartości liczbowych ROUND(wartość, liczba_znaków) TRUNC(wartość, liczba_znaków) MOD(wartość, dzielnik) SIN(wartość)/COS(wartość)/SINH(wartość)/COSH(wartość) POWER(wartość, potega) 30

Funkcje round(125. 315) = 125 round(125. 315, 0) = 125 round(125. 315, 1) =125. 3 round(125. 315, 2) =125. 32 round(125. 315, 3) =125. 315 round(-125. 315, 2) =-125. 32 31

Funkcje Wykorzystanie funkcji przy klauzuli WHERE SELECT * FROM emp WHERE MOD(sal, 200)=0; 32

Sortowanie wyników Klauzula ORDER BY Klauzulę umieszczamy po klauzuli WHERE 33

Sortowanie wyników Można wykorzystać kilka kolumn do sortowania Można sortować rosnąco lub malejąco (ASC/DESC) SELECT * FROM emp WHERE sal>2000 ORDER BY sal ASC, ename DESC; 34

Agregacje Agregowanie wyników pozwala na analizę wybranych kolumn z tabeli oraz zaprezentowanie wyniku danej funkcji w postaci jednej kolumny Proste agregacje pozwalają np. na: Obliczenie średniej wartości danej kolumny Obliczenie wartości minimalnej/maksymalnej Policzenie liczby wierszy Funkcje grupujące ignorują wartości null w kolumnie. 35

Agregacje 36

COUNT(*) Agregacja COUNT(*) zwraca liczbę wierszy pasujących do danego zapytania Agregacja ta może skanować daną tabelę, bądź indeks, jej wykorzystanie na dużych tabelach może być powolne SELECT COUNT(*) FROM emp WHERE sal>2000; 37

MIN(), MAX(), AVG() Korzystając z agregacji MIN, MAX lub AVG można policzyć minimalną, maksymalną bądź średnią wartość danej kolumny pasującej do zapytania 38

Grupowanie danych 39

Grupowanie danych SELECT column, group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column]; 40

Grupowanie danych SELECT department_id, AVG(salary) FROM employees GROUP BY department_id ; 41

Grupowanie danych SELECT department_id dept_id, job_id, AVG(salary) FROM employees GROUP BY department_id, job_id ; 42

Grupowanie danych Działanie HAVING odrzuca grup niespełniające warunku 1. Wiersze są grupowane. 2. Funkcja grupująca jest zastosowana. 3. Grupy spełniające warunek z klauzuli HAVING są wyświetlane. SELECT column, group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column]; 43

Grupowanie danych SELECT department_id, MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary)>10000 ; 44

Funkcja NVL Konwertuje null do wartości podanej Dane mogą być liczbami, tekstem lub datami. NVL(commission_pct, 0) NVL(job_id, ‘Bez pracy') 45

Funkcja NVL SELECT ename, sal, NVL(comm, 0), (sal*12) + (sal*12*NVL(comm, 0)) AN_SAL FROM emp; 46

Użycie wyrażenia CASE Dają możliwość użycia konstrukcji typu IF-THEN-ELSE w poleceniu SQL CASE expr WHEN [WHEN ELSE END comparison_expr 1 THEN return_expr 1 comparison_expr 2 THEN return_expr 2 comparison_exprn THEN return_exprn else_expr] 47

Użycie wyrażenia CASE SELECT last_name, job_id, salary, CASE job_id WHEN 'IT_PROG' ELSE THEN 1. 10*salary WHEN 'ST_CLERK' THEN 1. 15*salary WHEN 'SA_REP' THEN 1. 20*salary END "REVISED_SALARY" FROM emp; 48

Łączenie tabel 49

Uzyskiwanie danych z wielu tabel Product PName Price Category Manufacturer Gizmo $19. 99 Gadgets Gizmo. Works Powergizmo $29. 99 Gadgets Gizmo. Works Single. Touch $149. 99 Photography Canon Multi. Touch $203. 99 Household Hitachi Company CName Stock. Price Country Gizmo. Works 25 USA Canon 65 Japan Hitachi 15 Japan 50

Uzyskiwanie danych z wielu tabel Product Company PName Price Category Manufacturer Cname Stock. Price Country Gizmo $19. 99 Gadgets Gizmo. Works 25 USA Powergizmo $29. 99 Gadgets Gizmo. Works Canon 65 Japan Single. Touch $149. 99 Photography Canon Hitachi 15 Japan Multi. Touch $203. 99 Household Hitachi SELECT PName, Price FROM Product, Company WHERE Manufacturer=CName AND Country=‘Japan’ AND Price <= 200 PName Price Single. Touch $149. 99 51

Iloczyny kartezjańskie 52

Łączenia zgodne z SQL • Cross – iloczyn kartezjański • Natural - naturalne • Klauzula Using • Pełne lub dwustronne zewnętrzne • Arbitralne warunki łączenia dla łączeń zewnętrznych 53

Łączenie tabel SELECT table 1. column, table 2. column FROM table 1, table 2 WHERE table 1. column 1 = table 2. column 2; 54

Łączenie tabel SELECT * FROM employees, departments WHERE employees. department_id = departments. department_id; 55

Używanie aliasów tabel SELECT * FROM employees, departments WHERE employees. department_id = departments. department_id; SELECT e. employee_id, e. last_name, e. department_id, d. location_id FROM employees e , departments d WHERE e. department_id = d. department_id; 56

Łączenia Naturalne • Klauzula NATURAL JOIN bazuje na wszystkich kolumnach z dwóch tabel, które mają tę samą nazwę. • Wybiera wiersze z dwóch tabel tak, aby wartości w tych samych kolumnach obu tabel były zgodne. • Jeśli kolumny mające identyczne nazwy mają rożne typy danych, wówczas zwracany jest błąd. 57

Łączenia Naturalne SELECT department_id, department_name, location_id, city FROM departments NATURAL JOIN locations ; 58

Tworzenie łączeń z klauzulą ON • Warunek łączenia dla łączenia naturalnego w podstawowym przypadku jest warunkiem równościowym dla wszystkich kolumn o tej samej nazwie. • Aby określić arbitralny warunek lub określić kolumny do łączenia używa się klauzuli ON. SELECT e. employee_id, e. last_name, e. department_id, d. location_id FROM employees e JOIN departments d ON (e. department_id = d. department_id); 59

Tworzenie łączeń z klauzulą ON SELECT employee_id, city, department_name FROM employees e JOIN departments d ON d. department_id = e. department_id JOIN locations l ON d. location_id = l. location_id; 60

Dodatkowe warunki SELECT e. employee_id, e. last_name, e. department_id, d. location_id FROM employees e JOIN departments d ON (e. department_id = d. department_id) AND e. manager_id = 149 ; 61

„Relacyjne Bazy Danych (Oracle)” Prezentacja jest współfinansowana przez Unię Europejską w ramach Europejskiego Funduszu Społecznego w projekcie pt. „Innowacyjna dydaktyka bez ograniczeń - zintegrowany rozwój Politechniki Łódzkiej zarządzanie Uczelnią, nowoczesna oferta edukacyjna i wzmacniania zdolności do zatrudniania osób niepełnosprawnych” Prezentacja dystrybuowana jest bezpłatnie Projekt współfinansowany przez Unię Europejską w ramach Europejskiego Funduszu Społecznego Politechnika Łódzka, ul. Żeromskiego 116, 90 -924 Łódź, tel. (042) 631 28 83 www. kapitalludzki. p. lodz. pl 62