ADATBÁZISOK users.nik.uni-obuda.hu/szabozs/

1. ADATBÁZISOK http://users.nik.uni-obuda.hu/szabozs/ szabo.zsolt@nik.uni-obuda.hu

2. ISMÉTLÉS… szabo.zsolt@nik.uni-obuda.hu

3. SELECTZáradékok sorrendje • INTO • FROM • WHERE • GROUP BY • HAVING • UNION/MINUS • INTERSECT • ORDER BY szabo.zsolt@nik.uni-obuda.hu

4. NÉZETEK szabo.zsolt@nik.uni-obuda.hu

5. NÉZETEK • CREATE VIEW {NÉV} AS {ALLEKÉRDEZÉS}; • CREATE [OR REPLACE] …; DROP VIEW; • Allekérdezések elkerüléséhez, illetve rövidítéséhez használható • „Modularizált”, részekre bontható lekérdezések • Áttekinthetőbb lekérdezések • Egy nézet egy konkrét feladatra (rész-lista létrehozására), ami később újra felhasználható • Normalizálás előtti állapot (join eredmény) mentése szabo.zsolt@nik.uni-obuda.hu

6. NÉZETEK • CREATE [OR REPLACE] VIEW …; • Ugyanúgy használható, mint egy egyszerű tábla, főleg olvasásra használt (SELECT utasítással forrástáblaként, vagy később UPDATE/DELETE allekérdezéseként) • DE lehetséges módosítani / törölni / beszúrni is akár (ha „updatable view” akkor közvetlenül, egyébként „instead of” triggerrel) szabo.zsolt@nik.uni-obuda.hu

7. NÉZETEK • create or replace view NumOfBeoszt AS select mgr as FonokID, count(*) as Num from emp group by mgr; • select ename, Num from emp, NumOfBeoszt where empno=FonokID; • drop view NumOfBeoszt; • Ha változik az emp  változik a nézet is • Create table vs Create view  részletesebben következő óra szabo.zsolt@nik.uni-obuda.hu

8. NÉZETEK • Általánosságban kijelentve: nem gyorsít, csak egyszerűsít • Mi sosem fogunk olyan lekérdezést használni, ahol ez számítana • Ha mégis kellene: „Materialized view” (Oracle/Sybase/DB2) , „Indexed view” (MSSQL) • PostgreSQL: 9.3+ (2013. szeptember) • MySQL: csak triggerrel megoldható szabo.zsolt@nik.uni-obuda.hu

9. NÉZETEK + ORDER BY • SQL> create or replace view MyView as select * from emp order by ename;ORA & MySQL  View created. • SQL> create or replace view MyView as (select * from emp order by ename);ORA  ORA-00907: missing right parenthesisMySQL  View created • SQL> create or replace view MyView as (select * from (select * from emp order byename));ORA  View created.MySQL  ERROR subquery in the FROM??? 8/30/2014 szabo.zsolt@nik.uni-obuda.hu 9

10. "FELSŐ-N ANALÍZIS" ROWNUM (LIMIT, TOP, ROWCOUNT, START AT) szabo.zsolt@nik.uni-obuda.hu

11. ROWNUM • Az Oracle minden eredmény-sorhoz sorszámot rendel, ez a ROWNUM mező • WHERE-ben használható: select ename, rownum from emp where rownum<=3 order by ename; • Az ORDER BY hátrébb van  Nem a rendezett sorból válogat  CSAK allekérdezéssel használható!! szabo.zsolt@nik.uni-obuda.hu

12. ROWNUM • SELECT rownum, ename FROM (select ename from emp order by ename) WHERE rownum<=3; • SELECT rownum as order_num, ename, physical_num FROM (select rownum as physical_num, ename from emp order by ename) WHERE rownum<=3; • WHERE rownum>1 ? WHERE rownum>=10 AND rownum<20 ? szabo.zsolt@nik.uni-obuda.hu

13. ROWNUM • SELECT rownum, ename FROM (select ename from emp order by ename) WHERE rownum>1; •  ÜRES kimenet, a feltétel sosem lesz igaz… • SELECT * FROM (select ename, rownum as sorsz from (select ename from emp order by ename) sub) WHERE sorsz>=3 and sorsz<6; szabo.zsolt@nik.uni-obuda.hu

14. ROWNUM + NÉZET • create or replace view sorrend as (select rownum as sorsz, al.* from (select * from emp order by ename) al); • select * from sorrend where sorsz=6; • select * from sorrend where sorsz>=3 and sorsz<6; szabo.zsolt@nik.uni-obuda.hu

15. ROWNUM? • CSAK az SQL2003 "szabványban" van hasonló dolog: window functions • A leginkább implementáció-függő rész… !!! CSAK AZ ORACLE DIALEKTUST !!!!!! KELL ISMERNI !!! Most csak néhány google-kompatibilis kulcsszó… szabo.zsolt@nik.uni-obuda.hu

16. ROWNUM ~ SZÁMOZÁSSAL • MSSQL:select rownum=identity(int,1,1), ename into #temp from emp order by ename; select ename from #temp where rownum between {start} and {end}; • SYBASE:select rownum = identity(3), ename into #tempA from emp order by ename; select ename from #tempA where rownum between {start} and {end}; szabo.zsolt@nik.uni-obuda.hu

17. ROWNUM ~ SZÁMOZÁSSAL, MYSQL • set @num = 0;SELECT emp.*, @num := (@num + 1) as row_number from emp; • set @num = 0;SELECT emp.ename, emp.sal, @num := (@num + 1) as row_number from emp order by emp.enameWHERE row_number between {start} and {end}; szabo.zsolt@nik.uni-obuda.hu

18. ROWNUM ~ SZŰRÉS • MSSQL / SYBASE: set rowcount {num} • SYBASE: select TOP {length} START AT {start} ename from emp order by ename; • MSSQL: select TOP {length} ename from emp order by ename OFFSET {start}; (2012) • Postgresql, MySQL: selectenamefromemporder by ename LIMIT {start},{length} [= LIMIT X OFFSET Y – Sybase és SQLite is] szabo.zsolt@nik.uni-obuda.hu

19. ROWNUMEGZOTIKUS MEGOLDÁSOK • INGRES:SELECT FIRST 10 * from T; • INFORMIX:SELECT SKIP 20 FIRST 10 * FROM T order by c, d; • INTERBASE, FIREBIRD:SELECT FIRST 10 SKIP 20 * FROM T; • FIREBIRD:SELECT * FROM T ROWS 20 TO 30; szabo.zsolt@nik.uni-obuda.hu

20. ROW_NUMBER() • SQL:2008 ( DB2, Sybase, PostgreSQL ) SELECT * FROM EMP FETCH FIRST 10 ROWS ONLY; • SQL:2003 ( SQL SERVER 2005, Oracle 8i (2000), PostgreSQL 8.4 (2009) ) SELECT ROW_NUMBER() OVER (ORDER BY ENAME ASC) AS RNUM, ENAME FROM EMP; !!! CSAK A ROWNUM ISMERETE KELL!!! szabo.zsolt@nik.uni-obuda.hu

21. MySQL = TOY DATABASE? • 3.2x (1997-2004): „működik és gyors” (1992: Oracle 7: „integritás, eljárások, triggerek”) • 4.1 (2004): Allekérdezések, prepared statements • 5.0 (2005): Nézetek, eljárások, triggerek (De nem nézetre! Nézetek csak allekérdezések nélkül! Egy trigger / akció / időzítés!) • 5.5 (2010): InnoDB default (integritás, tranzakciók!), majdnem minden allekérdezés mehet nézetbe • Sun (2008), Oracle (2010) … Ingyenes, Gyors + Több tárolási motor + commit grouping + haldoklik?  MariaDB szabo.zsolt@nik.uni-obuda.hu

22. PÉLDÁK szabo.zsolt@nik.uni-obuda.hu

23. PÉLDA #1 Listázza azon dolgozókat, akiknek nincs, vagy nulla a jutalékuk, foglalkozási csoportjukban egynél többen vannak, valamint Dallas-ban vagy Chicago-ban dolgoznak. szabo.zsolt@nik.uni-obuda.hu

24. RÉSZLEG-FELTÉTEL • CREATE OR REPLACE VIEW data1 ASSELECT empno FROM emp, deptWHERE emp.deptno=dept.deptno AND dept.loc IN ('DALLAS', 'CHICAGO'); • Eredmény: azon dolgozó-azonosítók listája, akik Dallasban, illetve Chicagoban dolgoznak szabo.zsolt@nik.uni-obuda.hu

25. MUNKAKÖR-FELTÉTEL • CREATE OR REPLACE VIEW data2 ASSELECT job FROM emp GROUP BY jobHAVING count(*)>1; • Eredmény: azon munkakörök listája, ahol egynél többen dolgoznak szabo.zsolt@nik.uni-obuda.hu

26. MEGOLDÁS SELECT ename FROM emp WHERE ((comm=0) OR (comm is null)) AND empno IN (SELECT * FROM data1) ANDjob IN (SELECT * FROM data2); SELECT ename FROM emp, data1, data2 WHERE ((comm=0) OR (commis null)) AND data1.empno=emp.empno ANDdata2.job=emp.job;  IN vs EXISTS  MySQL és Oracle alatt is működik mindkettő szabo.zsolt@nik.uni-obuda.hu

27. PÉLDA #2 Minden részlegben jelenítsük meg a két legalacsonyabb fizetésű dolgozót, emellett jelenítsük meg a dolgozó részlegének fizetési arányát (a részleg legnagyobb és legkisebb fizetése közti különbség 15%-át) szabo.zsolt@nik.uni-obuda.hu

28. SZÜKSÉGES ADATOK • Részlegenként a legnagyobb és legkisebb fizetés különbsége  Data1  JOIN • Részlegenként a két legalacsonyabb fizetésű dolgozó  Data2  WHERE szabo.zsolt@nik.uni-obuda.hu

29. Nézettábla1 • CREATE OR REPLACE VIEW data1 AS SELECT min(sal) as Minimum, max(sal) as Maximum, max(sal)-min(sal) as Delta, 0.15*(max(sal)-min(sal)) as DeltaPct, deptno FROM emp GROUP BY deptno; • select * from data1; szabo.zsolt@nik.uni-obuda.hu

30. Nézettábla2 • Sorszámozásra gondolnánk, de ez túl bonyolult (+ lassú, rossz…) • Amikor (!!!) lehet, kerülni ajánlott a használatát • Túl mély allekérdezés-lánc, bár ez nézetekkel elkerülhető • Alternatív megoldási módszer … Darabszámolással? szabo.zsolt@nik.uni-obuda.hu

31. Nézettábla2 CREATE OR REPLACE VIEW data2 AS SELECTempnoFROM emp kulso WHERE(SELECT count(*) FROM emp belsoWHERE(belso.sal<kulso.sal) and (belso.deptno=kulso.deptno))<=1; szabo.zsolt@nik.uni-obuda.hu

32. MEGOLDÁS • SELECT ename, sal, emp.deptno, deltapct FROM emp, data1WHERE emp.deptno=data1.deptno AND empno IN (select * from data2); • SELECT ename, sal, emp.deptno, deltapct FROM emp, data1, data2WHERE emp.deptno=data1.deptno AND emp.empno=data2.empno; szabo.zsolt@nik.uni-obuda.hu

33. Nézettábla2 alternatíva • Cél: CREATE OR REPLACE VIEW data2 AS SELECT empno FROM reszlegsorrend WHERE sorrend<=2; Vagyis: használjunk valahogy sorszámozást! szabo.zsolt@nik.uni-obuda.hu

34. Nézettábla2 alternatíva(rownum, Oracle) • Sorrendnézet:CREATE OR REPLACE VIEW deptsal AS(SELECT rownum as dsal, al.* FROM (select* from emp order by deptno,sal) al); • Minden részlegből kell az első kettő • Második ZH-ban: ROW_NUMBER() OVER (ORDER BY sal PARTITION BY deptno) • Most:1 + {sorszám} - {részleg_minimum_sorszáma} szabo.zsolt@nik.uni-obuda.hu

35. Nézettábla2 alternatíva(rownum, Oracle) • Részlegen belüli sorrend:CREATE OR REPLACE VIEWdeptsal2 AS ( SELECT a.*,(1+a.dsal-(select min(dsal) fromdeptsal bwhere b.deptno=a.deptno)) AS dsal2 FROMdeptsal a); szabo.zsolt@nik.uni-obuda.hu

36. MEGOLDÁS CREATE OR REPLACE VIEW data2 AS SELECT empno FROM deptsal2 WHEREdsal2<=2; SELECT ename, sal, emp.deptno, deltapct FROM emp, data1, data2WHERE emp.deptno=data1.deptno AND emp.empno=data2.empno; csak 6 sor a 7 helyett!!! 8/30/2014 szabo.zsolt@nik.uni-obuda.hu 36

37. Nézettábla2 alternatíva(rownum, MySQL) • set @num = 0;create or replace view deptsal asSELECT empno, deptno, sal, @num := (@num + 1) as row_number FROM emp ORDER BY deptno, sal; • View's SELECT contains a variable or parameter • Változó + UPDATE/VIEW  VÉR, VERÍTÉK, KÖNNYEK … Vagy stored procedure … Vagy favágás? szabo.zsolt@nik.uni-obuda.hu

38. Nézettábla2 alternatíva(rownum, MySQL) droptableifexistsdeptsal; set @num = 0;create tabledeptsalasSELECT empno, deptno, sal, @num := (@num + 1) as dsal FROM empORDER BY deptno, sal; select * fromdeptsal; szabo.zsolt@nik.uni-obuda.hu

39. Nézettábla2 alternatíva(rownum, MySQL) drop table if exists deptsal2; create table deptsal2 AS SELECT a.*, (1+a.dsal- (select min(dsal) from deptsal b where b.deptno=a.deptno)) AS dsal2 FROM deptsal a; select * from deptsal2; szabo.zsolt@nik.uni-obuda.hu

40. MEGOLDÁS CREATE OR REPLACE VIEW data2 AS SELECT empno FROM deptsal2 WHEREdsal2<=2; SELECT ename, sal, emp.deptno, deltapct FROM emp, data1, data2WHERE emp.deptno=data1.deptno AND emp.empno=data2.empno; A lekérdezés vége változatlan, és mindenhol működik!  Amikor csak lehet, a nézetek használata javasolt 8/30/2014 szabo.zsolt@nik.uni-obuda.hu 40

41. FELADAT • Jelenítsük meg a dolgozók nevét, részlegének nevét, és a főnökük nevét. A listában szerepeljen a dolgozók és a főnökök jövedelembeállási értéke is. • Jövedelembeállási érték: (jövedelem) – (a vele egy évben belépett dolgozók átlagjövedelme) szabo.zsolt@nik.uni-obuda.hu