Programozás | SQL » Fábián Zoltán - BMF-NIK Adatbázisok programozása, MS SQL

Adatbázisok programozása I. (MS SQL) előadásjegyzet by UFO BMF-NIK, 5. félév (2006) Adatbázis-kezelő rendszerek elmélete Az adattárolás módjai - szöveges fájlok o egyszerű írás, közepes olvasás, nehéz módosítás o adattárolásra nem használjuk o platform független, könnyű szerkeszteni - Típusos fájl o Van rekordhossz, és típus o Könnyű indexelés o Külső programokkal nem módosítható, nem szabványos o Háttértár közvetlen elérése (blokkos átvitel, gyors) - Szabványos formátumok (dBase, Paradox, Excel) o Szabványos típusos fájl, külső programokkal is módosítható o A konkurens hozzáférést nem vagy gyengén kezelik - Adatbázis-kezelő rendszerek o Nem közvetlenül, hanem szoftveren keresztül férünk hozzá a táblákhoz o Tranzakció, konkurencia, hibatűrés kezelése Adatbázis-kezelők története - 1960: adatgyűjtés lyukkártyára - szabványosított fájlrendszer - 1970-80: hierarchikus, hálós, végül relációs

(E/K) - a hálós modell jobb egymással összefüggő adatok tárolásakor, pl. tudásbázis (kutya-harap-eszik) - tervezési modellek, indexelés, SQL, optimalizálás, konkurencia-kezelés - Online Transaction Processing (OLTP) - Kiterjesztett ADBKR o SQL 3, OOP - tudásalapú rendszerek, multimédiát támogató, térinformatikai rendszerek (specializált tárolás) - Webalapú rendszerek, szabványos, XML alapú tárolás - 1980: Adattárházak, adatbányászat - OLAP: többdimenziós táblák, tudásalapú rendszerek - 2000: integrált megoldások Fogalmak - Adatbázis: Nagy mennyiségű adatok strukturált együttese, amelyeket egy ABKR-en keresztül tudunk kezelni - ABKR: Az adatbázis kezelését lehetővé tévő szoftver. - Séma: egyedek, tulajdonságok összefüggése, absztrakciója - Előfordulás: konkrét egyed, tulajdonság, összefüggés Elvárások egy ABKR-rel szemben - Új adatbázis létrehozása - Adatok felvétele, kezelése, hatékony lekérdezése (a

winchesteren értelmesen van elhelyezve az adat, hogy gyorsabban lehessen visszaolvasni, egyszeri fejmozgás) - Biztonságos tárolás: hibatűrő, jogosultságok kezelése - Tranzakció-kezelés, konkurencia-kezelés (fontosabb, mint a sebesség) Az ABKR részei: - felhasználói interfész, protokoll - lekérdezés-feldolgozó: a parancsokat elemzi, hajtják végre - tárkezelő, fizikai tároló - tranzakció-kezelő -1- Hozzáférés az adatbázishoz - helye: o konzol o hálózaton keresztül - a hozzáférés módja: o szabványos protokoll (különböző, egyedi funkciók nem használhatók) o saját protokoll o közvetett módon (BDE, ODBC, JDBC) - a hozzáférés nyelve: o szabványos: SQL o saját nyelv Absztrakciós szintek - fizikai, belső szint: o az adatok fizikai elhelyezkedése o elérésük módja - logikai, koncepcionális szint: o az adatbázison belül a konkrét reláció elhelyezkedése o logikai egységbe fogja a teljes relációs adatbázist - külső szint

o hogyan látják az egyes felhasználók az adatbázist o jogosultságok, nézetek Adatfüggetlenség - logikai: az adatbázis logikai szerkezetében bekövetkezett változások ne befolyásolják az azt használó programokat - fizikai: az információkérés független a tárolás módjától Adattípusok (SQL 92 + MS SQL) - - - - - - Alfanumerikus típusok o Char(8): fix hosszúságú o VarChar(200): változó hosszúságú Unicode alfanumerikus típusok o Uchar(8) o UVarChar(200) Numerikus típusok o Numeric / decimal / dec (5,2) o Int, Smallint o Float, Real: egész szám tárolása esetén nem pontos Dátum típusok o Date, time o TimeStamp: nagyon pontos időbélyegző, ezt érdemes használni Bináris adatok o A fejlesztői környezet segítségével tölthetők fel (insert into-val nem) o Binary, longbinary o BLOB (Binary Large OBject) Egyéb (szerverfüggő) o Boolean / Bit: igaz, hamis értékekhez o Money: pl. a bankszámla-összeg túlcsordulása ellen o NULL:

Ismeretlen érték, nem pedig üres string  ANSI: minden NULL-lal történő összehasonlítás Ismeretlen-t ad (NULL = NULL is)  A NULL kezelését be lehet állítani -2- Domain-ek a.) CREATE TABLE Hallgato ( nev VARCHAR(50), jegy INT CHECK ( jegy >= 1 AND jegy <= 3 ) //vagy jegy IN (1, 2, 3) vagy EXIST SELECT jegy FROM Adhatojegyek WHERE ajegy = jegy //innen vehet fel értékeket ) Ezt általában nem valósítják meg a szerverek. b.) CREATE DOMAIN erdemjegy INT CHECK (VALUE >= 1 AND jegy <=3) DEFAULT 1 CREATE TABLE x ( y erdemjegy ) Megszorítások (constraints) - Definíció: a lehetséges adatok halmazát leíró szabály. Helye szerint különböző szintjei vannak: o Mezőszintű: pl. a mezők típusa o Rekordszintű: pl. ha egy ember neme nő, akkor nem lehet a keresztneve Károly o Táblaszintű: pl. ha az egész táblában csak egyetlen igazgató lehet o Sémaszintű: pl. a ’hallgatók’ táblában szereplők legalább fele legyen benne a

’fizető hallgatók’ táblában is Rekordszintű megszorítások CREATE TABLE hallgato ( nev VARCHAR(50) CHECK ( SELECT COUNT(nev) FROM hallgato WHERE anev = nev <= 3 ) szuletes INT CHECK ( akt ev – szul ev > 18 OR nev LIKE ’Szász%’ ) CHECK ( ) -3- Táblaszintű megszorítások Pl. szuperkulcs, kulcsjelölt, elsődleges kulcs CREATE TABLE hallgato ( neptunkod CHAR(8) PRIMARY KEY, nev VARCHAR(50), becenev VARCHAR(50) UNIQUE ) //két azonos nem szerepelhet (egyedi) vagy PRIMARY KEY(azonosito, neptunkod) Sémaszintű megszorítások - Idegen (távoli) kulcs CREATE TABLE potvizsgadij ( neptunkod CHAR(8), osszeg INT FOREIGN KEY(neptunkod) REFERENCE CASCADE ) hallgato(neptunkod) ON UPDATE 1. Módosítás esetén: ON UPDATE CASCADE: az első mező változásával a második is megváltozik ON UPDATE SET NULL: a második NULL lesz ON UPDATE NO ACTION: a másodikkal nem történik semmi ON UPDATE RESTRICT: nem engedélyezi a változtatást 2. Törlés esetén ON

DELETE Globális megszorítások CREATE ASSERTION CHECK (feltételek) ( SELECT COUNT(*) FROM hallgato ) //pl. ne legyen hallgató géphely nélkül Lekérdezések szintaktikája SELECT mezőlista - mezők - konstansok: SELECT 1 FROM (mindenhová 1-et ír) - függvények: számtani, karakteres, logikai, dátum - konverziók: CAS mező AS típus. Pl CAST(SUBSTRING(szoba,1,1) AS INT) [INTO új tábla] [FROM tábla forrás] [WHERE feltétel] [GROUP BY mezőlista] [HAVING csoportfeltétel] [ORDER BY mezőlista] -4- Alaplekérdezés SELECT [ALL | DISTINCT] [TOP n [PERCENT]] [WITH TIES] mezőlista [INTO tablanev] FROM tablanev ALL: ismétlődő sorokat is megjelenít DISTINCT: ismétlődő sorokat nem jelenít meg TOP n: egyszerre n sort ad vissza (nyilván a lekérdezés eredményének tetejétől) TOP n PERCENT: n százaléknyi sort ad vissza Példa: SELECT DISTINCT nev INTO bukottak FROM jegyek WHERE jegy = 1 Aliasok használata SELECT FROM empname e vagy nev AS Név A WHERE

feltétel használata - operátorok: !=, <>, !<, logikai operátorok: NOT, OR, AND speciális operátorok: BETWEEN, IN, LIKE ’N gy’, IS [NOT] NULL // ’ ’ a joker karakter A GROUP BY használata Összesítő függvények: AVG, MIN, MAX, COUNT, SUM SELECT AVG(jegy) FROM jegyek SELECT COUNT( DISTINCT ) //az egyedieket számolja SELECT hallgato, targy, AVG(jegy) FROM jegyek GROUP BY hallgato, targy //ami itt van, annak szerepelnie kell a SELECT-ben is HAVING AVG(jegy) = 1 Az ORDER BY használata ORDER BY ASC: rendezés növekvő sorrendben ORDER BY DESC: rendezés csökkenő sorrendben -5- Tábla összekapcsolások Táblák Hallgato (nev, szul, nem) Oktato (nev, szul) Kurzus (kod, targy, oktato, datum) Jelentkezes (hallgatonev, kurzus, jegy) Módszerek 1. Direkt szorzat SELECT nev, jegy FROM Hallgato h, Jelentkezes j WHERE h.nev = jhallgatonev vagy SELECT nev, jegy FROM Hallgato h JOIN Jelentkezes j ON (h.nev = jhallgatonev) 2. Lógó sorok SELECT nev, jegy FROM

Hallgato h LEFT OUTER JOIN Jelentkezes j (h.nev = jhallgatonev) //a bal tábla összes sora megjelenik Több tábla összekapcsolása SELECT oktato FROM Hallgato h JOIN Jelentkezes j ON (h.nev = jhallgatonev) JOIN Kurzus k ON (k.kurzus = jkurzus) WHERE jegy=1 AND hallgatonev LIKE ’%Béla’ Halmaz-függvények UNION: unió INTERSECTION: metszet EXCEPT / MINUS / NOT EXISTS: kivonás Példa: SELECT nev FROM Hallgato WHERE (GETDATE - szul) >= 30 * 365 UNION SELECT nev FROM Oktato WHERE () -6- Allekérdezések 1. WHERE-ben (EXISTS, NOT EXISTS, SOME, ANY, ALL) a.) SELECT nev FROM Hallgato h WHERE EXISTS ( SELECT hallgatonev FROM Jelentkezes j WHERE h.nev = jhallgatonev AND jegy = 1) b.) Akik minden tárgyból megbuktak SELECT nev FROM Hallgato h WHERE NOT EXISTS ( SELECT hallgatonev FROM Jelentkezes j WHERE h.nev = jhallgatonev AND jegy=1) c.) SELECT nev FROM Hallgato h WHERE 1 = ANY ( SELECT jegy FROM Jelentkezes j WHERE h.nev = jhallgatonev) 2. SELECT-ben SELECT nev, (

SELECT AVG(jegy) FROM Jelentkezes j WHERE h.hallgatonev = jnev) AS atlag, ( SELECT COUNT(*) FROM Jelentkezes j2 WHERE j2.hallgatonev = jnev AND jegy = 1) AS bukasszam FROM Hallgato h 3. FROM-ban (mint egy nézettábla) SELECT nev FROM ( SELECT nev, nem FROM Oktato UNION SELECT nev, nem FROM Hallgato) AS h WHERE nev LIKE ’%Károly’ AND nem=’N’ -7- Példafeladatok Táblák Hallgato nev szul nem Oktato nev szul Targy targykod targynev kredit Kurzus kurzuskod targynev oktatonev nap Jelentkezes hallgatokod kurzuskod jegy 1. Kik azok az oktatók, akik minden lányt átengednek? SELECT nev FROM Oktato WHERE NOT EXISTS ( SELECT * FROM Kurzus k JOIN Jelentkezes j ON (k.kurzuskod = jkurzuskod) JOIN Hallgato h ON (h.nev = jhallgatonev) WHERE nem = ’N’ AND jegy = ’1’ AND k.oktatonev = hnev ) 2. Az egyes oktatók hány embert buktattak meg? SELECT nev, COUNT(*) FROM Oktato o JOIN Kurzus k ON (o.nev = koktatonev) JOIN Jelentkezes j ON (k.kurzuskod = jkurzuskod) WHERE

jegy = 1 GROUP BY nev, nap 3. Listázzuk ki tárgyanként az átlagosnál jobb jegyet adó oktatókat! SELECT oktatonev, targy, AVG(jegy) FROM Kurzus k JOIN Jelentkezes ON () GROUP BY oktatonev, targy HAVING AVG(jegy) > ( SELECT AVG(jegy) FROM Kurzus JOIN Jelentkezes j ON () GROUP BY targynev HAVING k.targy = targy ) -8- A Transact SQL nyelv elemei Feladatok megoldása: - SQL segítségével (a szerveren fut) - Szerveroldali programok segítségével - Magasszinten, pl. Delphivel (a kliensen fut) Változók: - Deklarálás o DECLARE @nev típus o Lokális változó: @ o Globális változó: @@ - Értékadás o SET @jegy = 2 o SELECT @jegy = 2 - Kiírás a képernyőre o PRINT @jegy o SELECT @jegy //hagyományosan //lekérdezéssel //hagyományosan //lekérdezéssel Példa: DECLARE @kereses VARCHAR(50) SET @kereses = ’%Béla’ SELECT * FROM Nevek WHERE nev LIKE @kereses - - Blokkok használata o BEGIN és END között Feltételek használata o IF @nev LIKE ’%Sándor’

műveletek o ELSE Ciklusok //nincs THEN, nincs zárójel Példa: SET @x = 1 WHILE @x < 30 BEGIN INSERT INTO Jelentkezes (jegy) VALUES (@x) SET @x = @x + 1 END Tárolt eljárások létrehozása Általában: CREATE PROCEDURE <eljárásnév> [@param név típus] [WITH {RECOMPILE, ENCRYPTION}] AS {SQL utasítások | blokk} Példa: CREATE PROCEDURE Vkl (@vk VARCHAR(50)) AS SELECT nev FROM Hallgato WHERE nev LIKE ’/’ + @vk -9- Futtatás: EXEC Vkl(30) Függvények Általában: //ilyen típusú a visszatérési érték CREATE FUNCTION() RETURNS típus BEGIN RETURN ertek END Kurzorok Általában: DECLARE nev [INSENSITIVE] [SCROLL] CURSOR FOR lekerdezes [FOR READ ONLY] [FOR UPDATE OF mező1, mező2, ] INSENSITIVE: érzéketlen arra, ha megváltoznak az adatok, miközben működik SCROLL: engedélyezi az előre-hátra történő lépkedést Megnyitás: OPEN nev Adatok beolvasása: FETCH [NEXT | PRIOR | FIRST | LAST | RELATIVE n | ABSOLUTE n] FROM nev INTO változó1,

változó2, @@FETCH STATUS - Ha 0  sikeres - Ha -1  túlszaladás - Ha -2  hiba (törölték) Kurzor lezárása: CLOSE nev Kurzor felszabadítása: DEALLOCATE nev Teljes példa: DECLARE @alma VARCHAR(50) FETCH NEXT FROM hkurzor INTO @alma WHILE @@FETCH STATUS = 0 BEGIN PRINT @alma UPDATE Hallgato SET nev=’1’ + @alma WHERE nev = @alma //vagy WHERE CURRENT OF hkurzor - 10 - FETCH NEXT FROM hkurzor INTO @alma END CLOSE hkurzor DEALLOCATE hkurzor Példa: IF @alma LIKE ’%Béla’ DELETE FROM Hallgato WHERE CURRENT OF hkurzor Triggerek Trigger Meghatározott események bekövetkezése esetén Tetszőleges művelet végrehajtható Megszorítás Mindig él Visszautasítja a tranzakciót, ha megsérti a feltételt Szintaxis CREATE TRIGGER Név ON [táblanév | nézetnév] [WITH ENCRYPTION] [FOR ALTER | INSTEAD] [INSERT, UPDATE] AS [IF UPDATE(mezőnév) AND | OR UPDATE(mező)] -- sql parancsok sorozata -- a deleted és az inserted táblák tartalmazzák a törölt / új

adatokat Példa: ha a ’Zoli’ nevű hallgató ’5’-öst kap, akkor ROLLBACK Hallgato(nev, szul, anyja) Jegyek(nev, jegy) a.) CREATE TRIGGER Szivat ON Jegyek FOR INSERT, UPDATE AS IF EXITS ( SELECT * FROM inserted WHERE nev=’Zoli’ AND jegy=5) ROLLBACK --az egész tranzakciót visszavonja, bármi is volt még benne b.) CREATE TRIGGER Szivat2 ON Jegyek FOR INSERT AS UPDATE Jegyek SET jegy=1 WHERE nev=’Zoli’ AND jegy=1 - 11 - c.) CREATE TRIGGER Szivat3 ON Jegyek INSTEAD OF INSERT AS INSERT INTO Jegyek SELECT nev, jegy FROM inserted WHERE NOT (nev=’Zoli’ AND jegy=’5’) d.) CREATE TRIGGER helytakarekos ON Jegyek FOR INSERT, UPDATE AS DECLARE @nev CHAR(50), @ee INT DECLARE a CURSOR FOR SELECT nev FROM inserted WHERE jegy=1 OPEN a FETCH a INTO @nev WHILE @@FETCH STATUS=0 SET @ee = ( SELECT COUNT(*) FROM jegyek WHERE nev=@nev AND jegy=1) IF (@ee > 3) OR (@ee=2 AND @nev=’Zoli’) INSERT INTO Torlendo (nev, idopont, egyesek szama) VALUES (@nev, GETDATE(), @ee)

FETCH a INTO @nev END CLOSE DEALLOCATE a Az indexelés módjai Index: egy kulcsot és egy mutatót tartalmaz. Típusai a.) Sűrű index Az adatfájl valamennyi rekordjához tartalmaz egy kulcs-mutató párost Egy olvasással 2 blokkot érünk el. 10 Adat 10 Mutató 20 Adat 20 Mutató 30 Mutató 30 Adat 40 Mutató 40 adat Index fájl Adatfájl: Kulcs szerint rendezettszekvenciális fájl - 12 - b.) Ritka index Az adatfájl valamennyi rekordjához tartalmaz egy kulcs-mutató párost. A keresések száma több lesz, a beolvasások száma kevesebb 10 Adat 20 Adat 10 30 50 70 Mutató Mutató Mutató Mutató 30 40 Adat adat 50 60 Adat Adat 70 80 Adat adat Index fájl Adatfájl: Kulcs szerint rendezett  szekvenciális fájl Többszintű indexelés Az indexet is indexeljük Indexelés ismétlődő kulcs esetén A, Egyszerű sűrű index, ismétlődést is megengedve 10 Adat 10 Mutató 20 Adat 20 Mutató 30 Mutató 30 Adat 40 Mutató 40 adat B, Sűrű minden értékre 10

20 10 20 30 40 Mutató Mutató Mutató Mutató 20 20 Adat adat 30 40 Adat Adat 40 50 Adat adat C, Egyszerű ritka indexel 10 10 Mutató 20 20 Mutató 30 Mutató 30 Mutató Adat Adat 30 30 Adat Adat Adat adat - 13 - D, Adatblokkonként a legkisebb érték tárolása 10 Adat 20 Adat 10 20 30 40 Mutató Mutató Mutató Mutató 20 20 Adat adat 30 40 Adat Adat 40 50 Adat adat Törlés a.) sűrű index 10 | 10 | 20 | 20 | 30 | 40 | Töröljük a 30-at: - adat esetén: o sírkő alkalmazása praktikus o a feljebb csúsztatás sok ideig tart - index esetén: o csúsztatás (konszolidáció) 30 | 40 | b.) ritka index 10 | 20 | 30 | 40 | 50 | 60 | Beszúrás 10 | 30 | 50 | Töröljük a 30-at: - adat esetén: o konszolidáció. A 40-et feljebb csúsztatjuk, majd a régi 40-et töröljük - index esetén: o van módosítás vagy nincs Töröljük a 40-et is: - adat esetén: o konszolidáció, nincs mit feljebb csúsztatni - index esetén: o feljebb kell csúsztatni

az egészet a.) sűrű index Szúrjuk be a 15-öt: 1. Adatok csúsztatása A kivettnek keresünk egy üres/törölt helyet Akkor alkalmazható, ha sűrűn vannak a sírkövek 2. Túlcsordulási blokk felvétele Ha már létező elemet akarunk beszúrni, akkor a meglévő mögé láncoljuk, bízva abban, hogy majd csak lesz törlés. Akkor alkalmazható, ha a tárolt elemek eloszlása egyenletes - 14 - Másodlagos indexek a.) sűrű index - az azonos elemeket feleslegesen tároljuk 10 | 10 | 20 | 20 | 10 | 20 | 30 | 10 | 40 | b.) Bucket – kosár 20 | 10 | 20 | 10 | 20 | 30 | 40 | Előnyei: - Gyors keresés, pl. az egyik 20-as - Azonos elemek keresése, pl. az összes 20-as - Különböző halmazműveleteket lehet megoldani a kosarak segítségével (unió, metszet) - Pl. ha egy kosár tartalmazza a barnahajúakat, egy másik pedig a kutyatartókat, akkor egyszerűen meg lehet határozni a metszetüket Elosztott adatbázisok kezelése Az elosztott adatbázisok több

gépen helyezkednek el. Oka: - teljesítménynövelés egyszerűbb backup (másik szerver beállítása a döglött helyett) Formái: - azonnali szinkronizáció (online, valósidejű) [ritka] - laza integráció / késleltetett szinkronizáció (kellően valósidejű) o adott időközönként történik a szinkronizáció o hibatűrő: ha a távoli gép túlterhelt, a szinkronizációt el lehet halasztani o jól illeszkedik a lassú kapcsolatokhoz o a távoli gépen nem időszerű adatok is megjelenhetnek (még nem volt frissítés) - 15 - Megvalósítás: 1. Adattöbbszörözés Működése: - Az összes adatbázis ugyanazokat az adatokat tartalmazza - Az adatlekérés attól a szervertől történik, amelyik éppen szabad ADB . ADB1 ADBn webszerver 2. Adatok megosztása Központi TESCO ADB [100e] Budaörsi TESCO ADB [20e] . Pesti úti TESCO ADB [16e] Működése: - Az egyes helyeken különböző adatokra van szükség - A központi szerveren intelligens ügynök

működik - Ha az alárendelt adatbázisokban változás történik, akkor bizonyos időközönként megtörténik a szinkronizáció (éjszaka) - Akkor előnyös, ha nincs mindig szükség a legfrissebb adatokra a központban - Hibatűrő, elosztott rendszer Komponensei: 1. Információkiadás (publisher) - Tárgya: a. teljes adatbázis b. teljes tábla c. vertikális és horizontális partíciók (sorok, oszlopok, mezők) d. nézettábla - jellemzői: a. bizonyos időközönként vagy konkrét időpontban történik b. például minden éjszaka, vagy szerverterheléstől függően 2. Feliratkozó-kezelés (subscriber) a. Jogosultságok b. Háttérbeli folyamatok - naplózást figyeli process - adatterjesztést végző process 3. Terjesztés (distributor) a. helyi vagy távoli (topológia) b. konfliktuskezelő (egy időben egy helyen egy adat módosulhat) c. csak adatokat vagy a séma változásait is érinti? - 16 - Lehetséges topológiák: 1. Központi információkiadó -

kiegészíthető kétirányú kapcsolattal publisher distributor subscriber 2. Központi előfizető publisher distributor publisher publisher distributor 3. Központi információ-kiadó távoli adatterjesztéssel (adattárház) publisher distributor subscriber 4. Információ-kiadó előfizető (elosztott adattárház) publisher distributor dual mode distributor subscriber subscriber 5. Egyesítő többszörözés - a módosításokat felküldik az előfizetők a szervernek - a szerver pedig leküldi azokat a többi előfizetőnek subscriber publisher distributor subscriber subscriber - 17 - SQL-optimalizálás Lekérdezés-optimalizálók - szabályalapú: a lekérdezés formája alapján dönti el, hogyan futtatja le, az adatok figyelembe vétele nélkül költségalapú: az adatbázisra vonatkozó statisztikák szerint próbál optimalizálni. A plusz információk miatt hatékonyabb. Gyakran jobban

optimalizál, mint az ember Eszközei: 1. a táblák összekapcsolásának sorrendje 2. használjon-e indexeket, hasítófüggvényeket 3. mely táblákat töltse be a memóriába A B 1 millió sor 5 sor - - A.x = Bx szabályalapú: a megadott sorrendben végzi el az összehasonlítást. A elemein megy végig (lassú) költségalapú: felismeri, hogy B kisebb. B elemein megy végig (gyors) Optimalizálási tippek 1. A konstansokat előre számítsuk ki -- nem használ indexeket, mert bonyolult matematikai számításoknak hiszi az alábbiakat SELECT WHERE a > sin(90) * 5 / 2 2. A logikai kifejezéseket egyszerűsítsük a DeMorgan azonosságoknak megfelelően NOT (A=1 AND B=2) helyett A <> 1 OR B <> 2 3. Logikai kifejezések sorrendje az AND operátor használata során -- célszerű előre írni azt a kifejezést, amely nagyobb valószínűséggel hamis, így nem kell megvizsgálni a második kifejezést, ha az első hamis. (a leginkább megszorító feltétel

legyen az első) SELECT WHERE atlag = 5 AND nev = ’Benjamin’ 4. Logikai kifejezések sorrendje az OR operátor használata során -- célszerű előre írni azt a kifejezést, amelyik a legnagyobb valószínűséggel igaz, a többit utána pedig csökkenő sorrendben. Ugyanez igaz az IN kapcsolatra is: IN (5,4,3,2,1) A or B or C - 18 - 5. Logikai kifejezések kiértékelési sorrendje -- a leggyorsabban kifejthető legyen elöl, a leglassabb lekérdezés pedig leghátul SELECT nev FROM Hallgatok WHERE ( SELECT AVG(jegy) FROM Jegyek WHERE hnev = nev) = 5 AND Nev LIKE ’%Benjamin’ 5. A BETWEENIN operátor --Sebesség szerint növekvő sorrendben: WHERE jegy >= 4 AND jegy <= 5 IN (4,5) jegy BETWEEN 4 AND 5 Adatok beolvasásának módja a táblákból 1. Folytonos olvasás az adatok (rekordok) fizikai sorrendjében (blokkokat olvas be) akkor hatékony, ha a sok egymás mellett lévő adatot kell beolvasnunk 2. Indexelt beolvasás nem blokkokat, hanem egyetlen rekordot

olvas be akkor hatékony, ha össze-vissza lévő elemeket kell beolvasni fűzött index: ha az indexek szerint fizikailag rendezett a fájl 3. Hasító függvények használata Egyedi indexek, pl. növekményes index (auto increment) Tökéletes hasítófüggvény: egyenletes leképezés, kevés rés Egyéb hasítófüggvény: pl. csak körülbelül határozza meg a keresett elem helyét 4. Bitmap indexek használata TÁBLA Név Béla Józsi Sanyi Laci Béla Sanyi Jegy 1 2 5 3 2 5 BITMAP INDEX Sanyi-e vagy? 0 0 1 0 0 1 1-esed-e van? 1 0 0 0 0 0 5-ösöd-e van? 0 0 1 0 0 1 Indexek használata WHERE T1.a = T2b 1. 2. 3. 4. 5. egyik táblán sincs index csak A-n van index csak B-n van index mindkét táblán van index A és B azonos indexben szerepel - 19 - Példa: WHERE nev=’Sanyi’ AND jegy=5 - - AND kapcsolatot vizsgálunk a sorokra a következő bitmintával: 1 0 1 Ahol az eredmény igaz, ott egy keresett elem található Az indexhasználat kikényszerítése 1. NULL

értékek ne legyenek a lekérdezés eredményében (és a táblákban is) -- nem használ indexeket WHERE nev IS NOT NULL -- használ indexeket WHERE nev > ’AA’ 2. A nemegyenlőség vizsgálata --sorról sorra végigmegy, mert a nemJózsi sűrűbben fordul elő WHERE nev <> ’Józsi’ -- használ indexeket WHERE (nev < ’Józsi’) OR (nev > ’Józsi’) 3. Összesítő függvények a.) --nem használ indexet SELECT COUNT(*) --ezzel az egy mezővel dolgozik, de ha van index, akkor azzal SELECT COUNT(neptunkod) b.) --nem használ indexeket, mert túl bonyolultnak ítéli a lekérdezést SELECT MIN(adat), MAX(adat) --használ indexeket SELECT MIN(adat) SELECT MAX(adat) c.) --HAVING helyett WHERE SELECT nev, AVG(szam) FROM Sz GROUP BY nev HAVING nev LIKE ’%Béla’ SELECT nev, AVG(szam) FROM Sz WHERE nev=’%Béla’ (GROUP BY nev) -- kötelező, de nincs plusz hatása d.) Urban Legend -- Tévhit: A SELECT COUNT(*) megoldásnál gyorsabb a SELECT SUM(1) 4. LIKE

vs INDEX -- nem használ indexeket SELECT nev FROM Oktato WHERE szoba LIKE ’3%’ --használ indexeket, ha a szobaszámok háromjegyűek WHERE szoba >= ’300’ OR szoba <= ’399’ - 20 - 5. Kifejezésekben ne használjunk indexelt mezőt -- a hatarido indexelt mező, ne legyen azon az oldalon művelet WHERE hatarido + 5 < GETDATE helyett WHERE hatarido < GETDATE – 5 6. Indexválasztás --alaphelyzet SELECT * FROM T1, T2 WHERE T1.x = T2x --kikényszeríti az első index használatát SELECT * FROM T1, T2 WHERE T1.x > 0 AND T1x = T2x Felesleges indexek elhagyása – az index használat elkerülése Ne használjunk feleslegesen indexeket, mert: - az adatok módosítása lassabb lesz, mert az indexeket is módosítani kell - ha mindkét mezőn van index, kiveszi a barnákat, majd az 5-ösöket, s ezeknek veszi a metszetét. Lassabb, mintha csak az egyiken lenne index. WHERE hajszin = ’barna’ AND atlag = 5 - a barnára ne használjon indexet (a matematikai

művelet miatt): WHERE hajszin+’’ = ’barna’ Tábla-összekapcsolások, allekérdezések optimalizálása 1. Összekapcsolási feltétel megadása a. tranzitivitás WHERE A.x = Bx AND Ax = y b. tranzitivitás WHERE A.x = y AND Bx = y Lépésszámok alakulása, ha A: 100-ból 10db x, és ha B:100-ból 5db x: - összekapcsolás + szűrés: 100 x 100 = 10.000 sor - szűrés + összekapcsolás: 10 x 100 = 1.000 sor - csak szűrés: 10 x 5 = 50 sor 2. Plusz információk felhasználása (A-ban kevesebb adat van) a. WHERE A.x = By AND By = Cz b. WHERE A.x = By AND Ax = Cz c. ha nem tudjuk, hogy melyikben van kevesebb adat WHERE A.x = By AND Ax = Cz AND By = Cz - 21 - 3. Tábla-összekapcsolás vagy allekérdezés? a. SELECT nev, anyja FROM Hallgato WHERE nev IN (SELECT nev FROM Jegyek WHERE atlag=1) b. hátránya: rendezi a két táblát SELECT nev, anyja FROM Hallgato, Jegyek WHERE Hallgato.nev = Jegyeknev AND atlag=1 c. SELECT nev, anyja FROM Hallgato WHERE EXISTS ( SELECT nev

FROM Jegyek WHERE Hallgato.nev = Jegyeknev AND atlag=1) 4. Rendezések elkerülése - Összekapcsolás - SELECT DISTINCT: ne használjuk - UNION, INTERSECT, EXACT a. Mely tankörökben van Béla nevű hallgató? SELECT DISTINCT tankor FROM Tankor JOIN Hallgato ON (Tankor.szam = Hallgatoszam) WHERE nev LIKE ’%Béla’ b. Gyorsabb megoldás (egy rendezést megúszunk) SELECT tankor FROM Tankor WHERE EXISTS ( SELECT * FROM Hallgato WHERE Tankor.szam = Hallgatoszam ’%Béla’) - 22 - AND nev LIKE