ADATBÁZISOK HASZNÁLATA I. nik.uni-obuda.hu/szabozs/ - PowerPoint PPT Presentation

slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
ADATBÁZISOK HASZNÁLATA I. nik.uni-obuda.hu/szabozs/ PowerPoint Presentation
Download Presentation
ADATBÁZISOK HASZNÁLATA I. nik.uni-obuda.hu/szabozs/

play fullscreen
1 / 40
ADATBÁZISOK HASZNÁLATA I. nik.uni-obuda.hu/szabozs/
97 Views
Download Presentation
sook
Download Presentation

ADATBÁZISOK HASZNÁLATA I. nik.uni-obuda.hu/szabozs/

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

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

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

  3. ISMÉTLÉS • DDL: CREATE TABLE, DROP TABLE • DDL: RENAME, ALTER TABLE • DDL: NOT NULL, UNIQUE, PRIMARY KEY, REFERENCES • DDL: PRIMARY KEY, FOREIGN KEY, CHECK • DML: INSERT INTO, UPDATE, DELETE FROM • TRANZAKCIÓK: SAVEPOINT, ROLLBACK, COMMIT 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ó  áttekinthetőbb • Ugyanúgy használható, mint egy egyszerű tábla, főleg SELECT utasítással használt (DE lehetséges UPDATE, DELETE FROM, INSERT is) szabo.zsolt@nik.uni-obuda.hu

  6. NÉZETEK • create view NumOfBeoszt AS select mgr, count(*) as num from emp group by mgr; • select ename, num from emp, NumOfBeoszt where empno=mgr; • drop view NumOfBeoszt; • Ha változik az emp  változik a nézet is • Create table vs Create view szabo.zsolt@nik.uni-obuda.hu

  7. "KONZISZTENS MÓDON" • create or replace view dolgnezet as SELECT * FROM emp WHERE mod(empno, 2)=0; • Konzisztens=nem lehet sérült hivatkozás • Vagyis: nem lehet olyan főnök az MGR oszlopban, aki nem szerepel a táblában szabo.zsolt@nik.uni-obuda.hu

  8. "KONZISZTENS MÓDON" • CREATE TABLE dolgozo AS SELECTempno, ename, job,CASE WHEN mgr IN (SELECT empno FROM dolgnezet) THEN mgr ELSE NULL END AS mgr,hiredate, sal, comm, deptnoFROM dolgnezet; szabo.zsolt@nik.uni-obuda.hu

  9. "KONZISZTENS MÓDON" Egyszerűbben: • CREATE TABLE dolgozo ASSELECT * FROM dolgnezet; • UPDATE dolgozoSET mgr=nullWHERE mgr NOT IN (select empno from dolgozo); Legegyszerűbben (worst case): • CREATE TABLE dolgozo AS SELECT * FROM EMP; szabo.zsolt@nik.uni-obuda.hu

  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, ename, rown FROM (select rownum as rown, ename from emp order by ename) WHERE rownum<=3; Csak < és <= !!!! szabo.zsolt@nik.uni-obuda.hu

  13. ROWNUM • SELECT rownum, ename FROM (select ename from emp order by ename) WHERE rownum>1; • SELECT rownum as rown, ename FROM (select ename from emp order by ename) WHERE rownum<=6 and rownum >3; • SELECT * FROM (select rownum as rown, ename from (select ename from emp order by ename) where rownum<=6) WHERE rown>3; szabo.zsolt@nik.uni-obuda.hu

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

  15. ROWNUM? • CSAK az SQL2003 "szabványban" van hasonló dolog: row_number() • A leginkább implementáció-függő rész… !!! CSAK AZ ORACLE-T KELL TUDNI !!! !!! CSAK A ROWNUM ISMERETE KELL!!! 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} • MSSQL: select TOP {length} ename from emp order by ename; • SYBASE: select TOP{length} START AT {start} ename from emp order by ename; • Postgresql, MySQL: selectenamefromemporder by ename LIMIT {start},{length} [= LIMIT X OFFSET Y] szabo.zsolt@nik.uni-obuda.hu

  19. ROWNUM? • SQL SERVER 2005, Oracle, SZABVÁNY: SELECT ROW_NUMBER() OVER (ORDER BY ENAME ASC) AS RNUM, ENAME FROM EMP;  Allekérdezés … !!! CSAK A ROWNUM ISMERETE KELL!!! szabo.zsolt@nik.uni-obuda.hu

  20. Gyakorlás szabo.zsolt@nik.uni-obuda.hu

  21. 6.21 • +999 USD jutalék mindenkinek, akinek legalább kettő közvetlen beosztottja van. • … nézettábla vagy allekérdezés? • Cél: meghatározni, hogy kinek kell növelni a jutalékát szabo.zsolt@nik.uni-obuda.hu

  22. ALLEKÉRDEZÉS • UPDATE emp SET comm=nvl(comm, 0)+999 WHERE empno IN(SELECT mgr FROM emp GROUP BY mgr HAVING count(*)>=2); szabo.zsolt@nik.uni-obuda.hu

  23. NÉZETTÁBLA • CREATE OR REPLACE VIEW workers_num AS SELECT mgr FROM emp GROUP BY mgr HAVING count(*)>=2; • UPDATE emp SET comm=nvl(comm,0)+999 WHERE empno IN (select * from workers_num) szabo.zsolt@nik.uni-obuda.hu

  24. FELADAT Írjon script-programot, mely megnöveli mindazok fizetését(sal) egy, a felhasználó által megadott értékkel, akiknek nincs, vagy nulla a jutalékuk, foglalkozási csoportjukban egynél többen vannak, valamint Dallas-ban vagy Chicago-ban dolgoznak. […] drop table emp1; create table emp1 as select * from emp; szabo.zsolt@nik.uni-obuda.hu

  25. RÉSZLEG-FELTÉTEL • CREATE VIEW data1 ASSELECT empno FROM emp1, deptWHERE emp1.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

  26. MUNKAKÖR-FELTÉTEL • CREATE VIEW data2 ASSELECT job FROM emp1 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

  27. MEGOLDÁS UPDATE emp1 a SET sal=sal+&delta_sal WHERE ((COMM=0) OR (COMM IS NULL)) AND empno IN (SELECT * FROM data1)AND job IN (SELECT * FROM data2); szabo.zsolt@nik.uni-obuda.hu

  28. 6.22 Írjon script-programot, amely minden részlegben növeli a két legalacsonyabb fizetésű dolgozó fizetését a részleg legnagyobb és legkisebb fizetése közti különbség 15%-ával szabo.zsolt@nik.uni-obuda.hu

  29. 6.22 • Szükséges adatok • Részlegenként a legnagyobb és legkisebb fizetés különbsége  Nézettábla1 • Részlegenként a két legalacsonyabb fizetésű dolgozó (két legalacsonyabb fizetéssel rendelkező dolgozók???)  Nézettábla2 • Nézettábla1  SETNézettábla2  WHERE szabo.zsolt@nik.uni-obuda.hu

  30. Nézettábla1 • CREATE OR REPLACE VIEW nezet1 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 nezet1; szabo.zsolt@nik.uni-obuda.hu

  31. Nézettábla2 • RowNum –ra 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 … szabo.zsolt@nik.uni-obuda.hu

  32. Nézettábla2 SELECT kulso.empno, ( SELECT count(*) FROM emp belsoWHERE(belso.sal<kulso.sal) and (belso.deptno=kulso.deptno) ) AS KISEBB from emp kulso; szabo.zsolt@nik.uni-obuda.hu

  33. Nézettábla2 CREATE OR REPLACE VIEW nezet2 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

  34. UPDATE • UPDATE emp SETsal=sal+ (SELECT deltapct FROM nezet1 WHERE deptno=emp.deptno)WERE empno IN (select * from nezet2); szabo.zsolt@nik.uni-obuda.hu

  35. Nézettábla2 alternatíva (rownum) • Cél: CREATE OR REPLACE VIEW nezet2 AS SELECT empno FROM reszlegsorrend WHERE rown2<=2; • Sorrendnézet:CREATE OR REPLACE VIEW sorrend AS(SELECT rownum as rown, al.* FROM (select* from emp order by deptno,sal) al); szabo.zsolt@nik.uni-obuda.hu

  36. Nézettábla2 alternatíva (rownum) • Részlegen belüli sorrend:CREATE OR REPLACE VIEW reszlegsorrend AS ( SELECT a.*,(1+a.rown-(select min(rown) from sorrend bwhere b.deptno=a.deptno)) AS rown2 FROM sorrend a); szabo.zsolt@nik.uni-obuda.hu

  37. Nézettábla2 alternatíva (rownum) CREATE OR REPLACE VIEW nezet2 AS SELECT empno FROM reszlegsorrend WHERE rown2<=2; UPDATE emp SETsal=sal+ (SELECT deltapct FROM nezet1 WHERE deptno=emp.deptno)WERE empno IN (select * from nezet2); 9/26/2014 szabo.zsolt@nik.uni-obuda.hu 37

  38. 6.23 • Minden nem-president munkakörben megnöveli a két lekisebb fizetésű dolgozó fizetését a munkakör legkisebb és legnagyobb fizetése közti különbség 5%-ával •  gyakorlatilag ugyanaz, mint az előző szabo.zsolt@nik.uni-obuda.hu

  39. ZH • Március 24. • Teljes SQL: SELECT + DDL + DML • Tranzakció-kezelés, felső-N, nézetek(!) • „Nézettábláért pluszpont adható” szabo.zsolt@nik.uni-obuda.hu

  40. TANÁCSOK • "Ellenőrizze a műveletek végrehajtását" • Formázottan: BTITLE, TTITLE, COLUMN, BREAK ON, Linesize, Pagesize • String-egyezésnél UPPER/LOWER • Tábla-összekapcsolás: ALAPISMERET • 413_Labor\#01_SQL Bevezető Feladatgyűjtemény [25].pdf ! ! ! ! ! ! ! ! ! ! ! szabo.zsolt@nik.uni-obuda.hu