Adatbázisok / SQL v 2.1 Viczián Gergely (eredeti: Török János 2004)

1. Adatbázisok/ SQLv 2.1Viczián Gergely(eredeti: Török János 2004)

2. Útiterv az első 45 percre • Egy kis adatbázis történelem • Relációs Adatbázis-kezelők • Mi az SQL? • SQL alapjai • parancsok • lekérdezés • lekérdezési terv • variációk (Lotus, Access) • Közkívánatra: XML • Adatbiztonság

3. Az adatbázisok története • „data base” 1963 (~data bank) • Charles Bachman – szekv. feldolg. felváltása • 1970 E. F. Codd 12 szabálya relációs model • 1978 CP/M →MsDOS (Vulcan → dBASE) • 1979 Oracle 2 (RDBMS) • 1981 Tate és Lashlee dBASE-II PC assembly • 1984 dBASE-III (89 / 92 / 99 modosítások) • 1986 SQL ANSI és ISO szabvány lesz • XML ~ adatbázis

4. Relációs adatbáziskezelők „RDBMS” • Edgar Frank „Tedd” Codd szabályai 0.Reláció kizárólag csak kezelésre • Az egységes megjelenésű információ szabálya (tábla) • Garantált lokalizálhatóság szabálya (tábla, oszlop, sor/kulcs) • A NULL értékek egységes kezelése (mező lehessen "üres") • Dinamikus szerkezetleírás (maga a séma is adatbázis) • A teljeskörű „adatnyelv” szabálya (lineáris, interaktív, adat: SQL) • A nézetek frissítésének szabálya (valósidejű manipulálás: nincs) • Magas szintű beszúrás, frissítés és törlés (sok adat egyidejűleg) • Fizikai szintű adatfüggetlenség • Logikai szintű adatfüggetlenség (nehéz megvalósítani) • Jóság (integritás) függetlenség (adatkorlát, jóság kezelése) • Elosztástól való függetlenség (változások, lekérdezések) • Megkerülhetetlenség szabálya (alacsony szintű megkerülés)

5. dBASE USE bertabla REPLACE ALL fizetes WITH fizetes * 1.1 FOR vezeto > 0 LIST ALL nev, beosztas, fizetes TO PRINT

6. Relációs adatbázis • Relációs adatmodell (Codd szabályok) alapján létrehozott adatbázis részei: • Felhasználók, jogosultsági rendszerek • Táblák • Nézetek • Indexek (bináris fa) • Megszorítások • Eljárások, függvények • Triggerek • Azonos szerkezetű rekordok: • táblázat • minden oszlop rögzített típusú • alap, vagy null érték • Automatikus programok • változások véglegesítése • üres mezők kitöltése • integritás biztosítása • indexek létrehozása dialektus, más programnyelv

7. SQL • Structured Query Language • Általános leíró nyelv adatbázis elérésére, módosítására • Relációs adatbázis kezelők elterjedt nyelve • ANSI szabvány… • Sokféle programtámogatás: • Visual Basic (MsSQL) • Java • PL/SQL • ACCESS • php (MySQL) A dBASE nem SQL!

8. Az SQL alapjai 1. • Lekérdezés • (CREATE VIEW) SELECT • Adatmanipuláció • UPDATE • DELETE • INSERT INTO • Adatleírás • CREATE TABLE • ALTER TABLE • DROP TABLE • CREATE INDEX • DROP INDEX Az olvashatóság miatt írjuk nagybetűvel a parancsokat COMMIT/ROLLBACK

9. AZ SQL alapjai 2. Amire kíváncsiak vagyunk nincs/van ismétlődés • Lekérdezés SELECT [any/distinct all/distinct/top/distinctrow] { oszlopok } [AS alias1, alias2,…] FROMtáblakifejezés [,…] [INkülső adatbázis] [INNER/LEFT/RIGHT JOIN …] [WHERE ….] [GROUP BY …] [HAVING…] [ORDER BY…] [WITH OWNERACCESS OPTION] Megjelenített táblázat fejléccíme van/nincs ismétlődés/első n/ nincs dupla sor Lehet az oszlopok után is írni Csak ACCESS, a vizuális összekapcsolás miatt Táblázat/ok Szűrési feltétel Csoportosítás (összeg, átlag) Csoportosítás utáni szűrési feltétel Az eredmény rendezését adja meg

10. Példa SELECT Fokonyv_2K4.Sorsz, Fokonyv_2K4.Datum, Fokonyv_2K4.[Brutto(Tart)], Fokonyv_2K4.[Brutto(Kov)], Fokonyv_2K4.[KP/Bank], [Brutto(Tart)]-[Brutto(Kov)] AS akt FROM Fokonyv_2K4 WHERE (((Fokonyv_2K4.Datum)<=[vegnap]));

11. Összetettebb példa  SELECT SQL_CALC_FOUND_ROWS base.actiroleID AS col0, actiID, actiName AS col1, actiState AS col3, wbseID, wbseName AS col4, flowID, flowName AS col6, actiPriority AS col8, atypAbr AS col9, actiID AS col10, IF(ISNULL(actiRealStart) OR actiRealStart='0000-00-00',actiPlannedStart,actiRealStart) AS col11, actiPlannedEnd AS col12, IF(ISNULL(actiPlannedEnd) OR actiPlannedEnd='0000-00-00','',IF((ISNULL(actiRealEnd) OR actiRealEnd='0000-00-00') AND actiPlannedEnd>=CURRENT_DATE,'',TO_DAYS(IF(ISNULL(actiRealEnd) OR actiRealEnd='0000-00-00',CURRENT_DATE,actiRealEnd))-TO_DAYS(actiPlannedEnd))) AS col13 FROM TQactirole as base left join TQacti AS acti on (actiID=base.actiroleActiID) left join TQwbse AS wbse on (wbseID=actiWbseID) left join TQflow AS flow on (wbseFlowID=flowID) left join TQatyp AS atyp on (atypID=actiATypID) WHERE actiWbseID>-1 and (flowFCatID<1 or ISNULL(flowFCatID)) AND (base.actiroleRoleID IN ('22')) AND (ISNULL(actiPlannedEnd) OR actiPlannedEnd<=CURDATE()+INTERVAL 7 DAY) AND (actiState='3' OR (3=3 AND actiState<>2) OR (3=4 AND actiState<>0)) GROUP BY actiID ORDER BY col12 asc LIMIT 0,30

12. SQL hátrányai... • Nem igazi relációs adatbázis modell • A szintaxis komplikált • Sokszor igen hosszú parancsok születnek, amelyek nehezen törhetők darabokra • A különböző implementációk kicsit eltérnek egymástól (pl. Oracle és Access) • Ékezetes problémák bizonyos megvalósításokban

13. Lekérdezési terv • A keresés végeredményét általában tovább analizáljuk • Lekérdezési terv • A kapott halmazt (eredmények különböző táblákból) egy kurzor segítségével tudjuk tovább olvasni

15. Fontosabb különbségek • Adattípusok • NUMERIC-NUMBER • DATE  Oracle-ben dátum és idő is, máshol csak dátum • időnként van nem standard típus is • Gyors keresésnél néha csak pontos találat lehetséges, míg máshol nem • Helyettesítések (%_ (Oracle) *?#[a-z](Access)) • Programozási kiterjesztések

16. Programozás • Visual Basic for ACCESS Sub test() Dim s As String s=”SELECT ….” DoCmd.RunSQL s End Sub • Flexibilis lekérdezések formokkal!

17. PHP @ $db = mysql_pconnect('localhost', 'root', ''); if (!$db) { echo 'Nem konnektál!'; exit; } mysql_select_db('gergo'); $q = "SELECT tartuser.userid as uid, tartuser.nev, sum( tartitem.osszeg ) as ossz , max( tartitem.datum ) as utolso" ." FROM tartuser, tartitem" ." WHERE tartitem.userid = tartuser.userid" ." GROUP BY tartitem.userid" ." ORDER BY ossz desc"; $res = mysql_query($q); $n = mysql_num_rows($res); $ossz=0; echo '<table border=1>'; echo ‘<tr><td>Név</td>'; echo '<td>Tart</td>'; echo '<td>Utsó</td>'; echo '</tr>'; for ($i=0;$i<$n; $i++) { $row = mysql_fetch_array($res); if($row['ossz']!=0) { echo '<tr>'; echo '<td><a href=user.php?id='.$row['uid'].'&wr=1>'.$row['nev']‘; echo ‘</a></td>'; echo '<td>'.$row['ossz'].'</td>'; $ossz+=$row['ossz']; echo '<td>'.substr($row['utolso'],5).'</td></tr>'; } } echo "<tr><td>Összesen:</td><td>$ossz</td><td></td></tr>";

18. XML = eXtensible Markup Language <?xml version="1.0"?> <department> <staff no="1"> <surname>Goulson</surname> <firstname>Phil</firstname> </staff> <staff no="2"> <surname>Pooley</surname> <firstname>Joanna</firstname> </staff> <staff no="3"> <surname>Jack</surname> <firstname>Angus</firstname> </staff> : : <staff no="8"> <surname>Scott</surname> <firstname>Steve</firstname> </staff> </department> • Elemek • Kezdő "tag" • Tartalom • Záró "tag" • Attribútumok • Név • Érték no; surname; firstname 1; Goulson; Phil 2; Pooley; Joanna 3; Jack; Angus 4; Parsons-Hann; Wendy 5; Jenkinson; Debra 6; Jenkinson; Jon 7; Wintringham; Ben 8; Scott; Steve

19. <?xml version="1.0" encoding="UTF-8"?> <recept nev="kenyer" keszites="5 perc" sutes="3 ora"> <cim>Egyszeru kenyer</cim> <osszetevo menny="3" egys="csesze">Liszt</osszetevo> <osszetevo menny="15" egys="gramm">Eleszto</osszetevo> <osszetevo menny="2" egys="csesze" allapot="meleg">Viz</osszetevo> <osszetevo menny="1" egys="kanal">So</osszetevo> <utasitasok> <lepes>Gyúrd össze az összetevőket egyenletesen</lepes> <lepes>Takard le ruhával és hagyd kelni egy órán keresztül</lepes> <lepes>Gyúrd át újra majd tegyed forró sütőbe két órára</lepes> </utasitasok> </recept> • DOM=Document Object Model (bejárható) • SAX=Simple API for XML (eseményvezérelt) Mire jó? RSS, INI, kis adatbázis, oldalleírás, adatcsere...

20. Adatbiztonság • Az adatbázis feladata • minden esemény naplózása • összeomlás után automatikus helyreállás • hibás adatok kiszűrése • Rendszergazda/főnök feladata • biztonsági másolat készítése • jogosultságok beállítása/ellenőrzése • biztonsági frissítések figyelése, különösen webes alkalmazásnál • auditálás