1 / 40

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

ADATBÁZISOK HASZNÁLATA I. http://nik.uni-obuda.hu/szabozs/. ISMÉTLÉS…. 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

sook
Télécharger la présentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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

More Related