Adatbázisok szerver oldali programozása Előadás, 2006.02.17.

Ez az oldal a korábbi SCH wikiről lett áthozva.

Ha úgy érzed, hogy bármilyen formázási vagy tartalmi probléma van vele, akkor, kérlek, javíts rajta egy rövid szerkesztéssel!

Ha nem tudod, hogyan indulj el, olvasd el a migrálási útmutatót.

<style> ol li { line-height: 18px; } </style>

<< Előadás, 2006.02.17. >>

Kliens-szerver architektúra

• Kliens: üzleti logika és megjelenítés
• Szerver: adatbázis

• Előny: kevés kommunikáció
• Hátrány: ha az üzleti logika megváltozik, minden klienst cserélni kell

n-rétegű architektúra

• Presentation Layer
• Business Logic Layer
• Data Access Layer
• Database -> mi csak ezzel foglalkozunk

Előnyök

• rugalmasabban módosítható
• DAL miatt a DB cserélhető
• skálázható

Hátrányok

• bonyolultabb
• több kommunikáció
• interfészeket előre definiálni kell

Szerver oldali programozás

• Tárolt eljárások és függvények
• Triggerek = eseményre automatikusan lefutó tárolt eljárások. Esemény alatt mást értünk Oracle és MS SQL alatt.

Tárolt eljárások

Előnyök

• Teljesítmény
  • Szerveren lefordítva tárolódnak
  • Több szál ugyanazt a memóriát használja
  • Kisebb hálózati forgalom
  • BLL-ben kevesebb funkció, jobban skálázható (rossz is lehet, mert a szervert terheljük)
• Kevésbé redundáns kódolás
• Biztonságos jogosultságkezelés a szerveren: felhasználó jogosultsága csak a tárolt eljárások futtatására korlátozható
• karbantarthatóság: ha csak a szervert módosítjuk, a klienst nem kell újrafordítani

Hátrányok

• Szervert terheljük
• Nem szabványos

SQL szintaktika

Alap típusok

• =int=
• =varchar(20)=: változó hosszú string
• =nvarchar(20)=: változó hosszú unicode string
• =char(20)=: fix hosszú string
• =nchar(20)=: fix hosszú unicode string
• =date= vagy =datetime=
• =float=
• =bit= (csak MS SQL-ben)

Tábla létrehozása

CREATE TABLE táblanév (
	{ oszlopdef | tábla szintű megkötés }*
)
oszlopdef: oszlopnév típus [oszlopszintű megkötés]
oszlopszintű megkötés:
	NULL | NOT NULL	// default: NULL
	[CONSTRAINT megkötésnév]
		PRIMARY KEY
		UNIQUE
		REFERENCES tábla(oszlop)
		CHECK(feltétel)	// a feltételben csak a saját oszlopnév szerepelhet
táblaszintű megkötés:
	CONSTRAINT név
		PRIMARY KEY (oszlopnév1, oszlopnév2, ...)
		UNIQUE (oszlopnév1, oszlopnév2, ...)
		FOREIGN KEY (oszlopnév1, ... oszlopnévn) REFERENCES táblanév(oszlopnév1, ... oszlopnévn)
		CHECK (több oszlopot tartalmazó logikai kifejezés

Tábla módosítása

ALTER TABLE
	ADD { oszlopdef | tábla szintű megkötés }
	DROP, stb.

Lekérdezés

SELECT [ALL|DISTINCT] oszloplista	// default: all
FROM táblalista
[WHERE keresési feltétel]
[GROUP BY csoportosítási kifejezés
	[HAVING csoport feltétel]]
[ORDER BY oszlop [ASC|DESC]]	// default: ASC

Táblák összekapcsolása

1. Descartes-szorzat

=SELECT * FROM t1,t2=

1. =WHERE= mögött id-k alapján

=SELECT * FROM t1,t2 WHERE t1.id=t2.t1_id=

1. =JOIN= kulcsszó

=SELECT * FROM t1 JOIN t2 ON t1.id=t2.t1_id=
Állítólag gyorsabb, de a jobb olvashatóság miatt a 2. változatot szokták használni.

1. =NATURAL JOIN=

=SELECT * FROM t1 NATURAL JOIN t2=
A két tábla közötti referenciánál illeszt.

1. Beágyazott =SELECT= a tábla helyén

=SELECT * FROM t1, (SELECT * FROM t2) a WHERE a.id=t1.a_id=

1. Beágyazott =SELECT= a =WHERE= mögött

=SELECT * from t1 WHERE t1_id IN (SELECT id FROM t2)=

GROUP BY

Oszlopfüggvények: =MIN=, =MAX=, =SUM=, =COUNT=, =AVG=.

• Legjobban megfizetett alkalmazott:

=SELECT MAX(fizetés) FROM alkalmazott=

• Minden tanszékről a legjobban megfizetett alkalmazott:

=SELECT tanszék, MAX(fizetés) FROM alkalmazott GROUP BY tanszék=

• Azok a tanszékek, ahol a legjobb fizetés 200000 fölött van + a fizetés értéke:

=SELECT tanszék, MAX(fizetés) maxfizu FROM alkalmazott
GROUP BY tanszék HAVING MAX(fizetés)>200000=
(A =HAVING= után nem használható az oszlopok új neve (itt: maxfizu))

• Ha a =SELECT= oszlopfüggvényt tartalmaz, a =GROUP BY= után szerepelnie kell minden olyan oszlopnak, ami a

  SELECT

  -ben oszlopfüggvény kívül szerepelt.

Halmazműveletek

• =UNION=
• =UNION ALL=
• =INTERSECT= (Az SQL Server 2000 nem támogatja, csak a 2005)
• =MINUS= (Az SQL Server 2000 nem támogatja, csak a 2005)

-- Peti - 2006.02.21.

Beszúrás

• =INSERT INTO tábla (oszlop1, oszlop2, ...) VALUES (érték1, érték2, ...)=
• =INSERT INTO tábla (oszlop1, oszlop2, ...) SELECT (oszlop1, oszlop2, ...) ...=

Azonosító generálás

• SQL Server
  • Létrehozás: =CREATE TABLE a (id INT IDENTITY PRIMARY KEY, szoveg VARCHAR(20))=
  • Beszúrás: =INSERT INTO a VALUES('abc')=
  • Beszúrás nem automatikusan generált id-vel:

=SET IDENTITY_INSERT a ON=
=INSERT INTO a(id,szoveg) VALUES(3, 'cde')=
=SET IDENTITY_INSERT a OFF=

• Oracle
  • Szekvencia létrehozása (különálló objektum, nem kapcsolódik táblához):

=CREATE SEQUENCE a_seq START WITH 1 INCREMENT BY 1=

• • Beszúrás: =INSERT INTO a VALUES(a_seq.NEXTVAL, 'abc')=
  • Beszúrás a szekvencia aktuális értékével:

=INSERT INTO a VALUES(a_seq.CURVAL, 'abc')=

Módosítás

• =UPDATE táblanév SET oszlop1=érték1, oszlop2=érték2, ... [WHERE feltétel]=
• Több táblás módosítás:
  • SQL Server: =FROM= kulcsszóval
  • Oracle: al-

    SELECT

    segítségével

Törlés

• =DELETE FROM táblanév [WHERE feltétel]=

Ha nincs feltétel megadva, az egész táblát törli.

• =TRUNCATE táblanév= == =DELETE FROM táblanév=. Az előbbi gyorsabb.

Nézet létrehozása

• =CREATE VIEW nézetnév(oszlop1, oszlop2, ...) AS lekérdezés=
• Egy táblás nézetbe be lehet szúrni sorokat, több tábla esetén csak triggerekkel lehet trükközni.

-- Peti - 2006.02.21.