Adatbázis rendszerek. Molnár Bence. Szerkesztette: Koppányi Zoltán

Adatbázis rendszerek Molnár Bence

Szerkesztette: Koppányi Zoltán

A mai órán

A mai órán — SQL — Mi

(és ami mögötte van)

lesz a ZH-ban?

SQL

Történet —

1970-es évek eleje IBM SEQUEL (Structured English QUery Languge)

—

Structured/Standard Query Language

—

1986-tól ANSI, 1987-től ISO szabvány

—

SQL2 ('92), SQL3 ('99), ...

—

folyamatos fejlesztés (SQL2011)

—

szabvány, melyet többé-kevésbé a legtöbb relációs adatbázis-kezelő támogat

—

Relational Software, Inc. → Oracle Corp.

Alapok —

Magja ekvivalens a relációs algebrával, de annál bővebb —

összesítő műveletek (pl. SUM, AVG)

—

adatbázis-módosítások

—

Nem procedurális nyelv (deklaratív), tehát az alapvető kérdés az, hogy mit kell csinálni, és nem az, hogy hogyan.

—

Adatleíró (DDL) -, Adatmódosító (DML) -, Adatelérést vezérlő (DCL) -, Lekérdező (QL) nyelv

SQL alapok —

adatbázis létrehozása CREATE DATABASE adatbazis;

—

jogosultságok

—

felhasználó

—

elérés „helye”

—

kódolás (UTF8, LATIN2 [ISO-8859-2])

—

sablonok (pl. téradatok tárolása esetén)

—

használat

—

explicit definíció kapcsolódáskor

—

USE adatbazis;

—

Használt karakterek: [_a-z0-9]

Emlékeztető Relációs adatbázis séma — Relációs

séma: Hallgató(Neptun: Szöveg, Név: Szöveg, Születési_év: Szám)

— Relációs

adatbázis séma: Hallgató Neptun: Szöveg Név: Szöveg Születési_év: Szám ...

Tábla létrehozása CREATE TABLE hallgato ( neptun VARCHAR(6) [...], nev VARCHAR(50), szuletesi_ev INTEGER, atlag DOUBLE PRECISION, PRIMARY KEY (neptun) );

Hogyan (ne) használjuk a dokumentációt? — PostgreSQL — MySQL — Oracle — W3Schools

Tábla létrehozása — CREATE

TABLE táblanév

— …: —

DEFAULT alapértelmezett érték

—

NULL /NOT NULL

—

UNIQUE (!= PRIMARY KEY!)

—

AUTO INCREMENT/Sequence

Tábla módosítása —

ALTER TABLE táblanév ADD oszlopnév típus; —

—

ALTER TABLE táblanév DROP oszlopnév; —

—

DROP hallgato;

TRUNCATE táblanév; —

—

ALTER TABLE hallgato RENAME TO bme_hallgatok;

DROP táblanév; —

—

ALTER TABLE hallgato DROP nev;

ALTER TABLE táblanév RENAME TO új_táblanév; —

—

ALTER TABLE hallgato ADD szuletesi_hely VARCHAR(50);

TRUNCATE hallgato;

Változtatások esetén figyelni kell a többi táblával való kapcsolatok frissítésére is!

Adatfeltöltés —

Beszúrás —

INSERT INTO táblanév [(attribútum1, attribútum2, …)] VALUES (érték1, érték2, …);

—

INSERT INTO hallgato (neptun, nev, szuletesi_ev, atlag) VALUES ('ABCDEF', 'Kiss Péter', 1993, 4.5);

—

LOAD DATA

—

COPY

Adatmódosítás —

—

Módosítás —

UPDATE táblanév WHERE feltétel;

SET

attribútum

=

érték

—

UPDATE hallgato SET atlag = 4.6 WHERE nev = 'Nagy Sándor';

Törlés —

DELETE FROM táblanév WHERE attribútum = érték;

—

DELETE hallgato WHERE nev = 'Kovács József';

Vetítés SQL-ben — Vetítés

(projekció) [π, Π]: a reláció bizonyos attribútumait megtartjuk és a többit törölve új relációt hozunk létre.

— SELECT

nev, szuletesi_ev FROM hallgato; neptun

— SELECT

nev

szuletesi_ev

* FROM hallgato; neptun

nev

szuletesi_ev

Egyszerű lekérdezések —

SELECT attribútum(ok) FROM táblá(k) WHERE tulajdonság(ok); —

SELECT nev FROM hallgatok WHERE nev = 'Kiss Péter';

—

attribútumok, táblák elválasztása vesszővel

—

tulajdonságok összefűzése logikai operátorral

—

aliasok (AS)

—

DISTINCT

—

különleges karakterek: *, _, %

—

escape karakterek

—

operátorok: =, <>, >, <, =>, <=, LIKE, BETWEEN, NOT

—

Idézőjelek: ', "

—

kis- és nagybetűk

Nézettáblák —

CREATE TABLE: fizikailag létrejön (tárolásra kerül)

—

CREATE VIEW nézettábla AS lekérdezés; —

CREATE VIEW hallgato_a AS SELECT * FROM hallgato WHERE nev LIKE 'A%';

—

nem jön létre (csak a definíció tárolódik), nem foglal tárkapacitást

—

lekérdezhető

—

módosítható? (materializált view)

—

DROP VIEW nézettábla;

Sorba rendezés — SELECT

* FROM hallgato WHERE nev LIKE 'Kovács%' ORDER BY szuletesi_hely ASC/DESC;

neptun_kod

nev

szuletesi_hely

neptun_kod

nev

szuletesi_hely

Abádszalók

Zalaegerszeg

Abádszalók

Zalaegerszeg

Almásfüzitő

Záhony

Budapest

Záhony

…

…

Csoportosítás —

Valamilyen attribútum értéken egyező rekordok csoportosítása, „együttes kezelése”

—

SELECT AVG(jegy) FROM hallgato WHERE targy = 'Adatbázisok' GROUP BY szuletesi_ido;

—

Feltétel megadása a csoport tulajdonságaira

—

SELECT AVG(jegy) FROM hallgato WHERE targy = 'Adatbázisok' GROUP BY szuletesi_ido HAVING szuletesi_ido < 1994;

Függvények használata —

SELECT COUNT(*) FROM hallgato;

—

SELECT AVG(atlag) FROM hallgato;

—

SELECT MIN(atlag) FROM hallgato;

—

SELECT MAX(atlag) FROM hallgato;

— SELECT

SUM(atlag)/COUNT(*) FROM hallgato;

— SELECT

DISTINCT szuletesi_hely FROM hallgato;

Bonyolultabb lekérdezések —

SELECT nev FROM hallgato WHERE (nev LIKE '%Péter' OR nev LIKE '%Sándor') AND nev LIKE 'Nagy%';

—

SELECT nev 'Kov_cs%';

—

SELECT eloadas_hossza*60 AS perc FROM targyak;

—

SELECT eloadas_hossza 'óra' FROM targyak;

—

SELECT nev FROM hallgatok 'Adatbázisok' AND NOT bukott;

FROM

hallgato

WHERE

WHERE

nev

LIKE

targy

=

Metszet/Únió — Általános

formula:

(SELECT neptun_kod FROM hallgatok WHERE nev LIKE 'Nagy%') INTERSECT/UNION (SELECT neptun_kod FROM hallgatok WHERE szuletesi_hely = 'Budapest'); — szintaktika — attribútumok

száma

Subselect — film(cim,

ev, hossz, studioNev, rendezoID)

— rendezo(ID,

nev, cim, bevetel)

— SELECT

nev FROM rendezo WHERE ID = (SELECT rendezoID FROM Film WHERE cim = 'Gyűrűk Ura');

— SELECT

* FROM film WHERE rendezoID IN (SELECT ID FROM Rendezo WHERE nev LIKE 'P_ter%');

Több táblát érintő lekérdezések — film(cim,

ev, hossz, studioNev, rendezoID)

— rendezo(ID, — SELECT

— SELECT

nev, cim, bevetel)

cim, nev FROM rendezo, film; (?)

cim, nev FROM rendezo, WHERE film.rendezoID = rendezo.ID;

film

Kapcsolatok — természetes

összekapcsolás

—

az összekapcsolási feltétel az azonos nevű attribútumok egyenlőségéből képződik

—

nincs más feltétel

—

az egyenlő attribútumok közül csak egyet tartalmaz az eredmény

— théta-összekapcsolás

(csak = operátor)

Kapcsolatok oktatok oktato

tanszekKod

Koppányi Zoltán

FMT

Lovas Tamás

FMT

Tuchband Tamás

AGT

Gipsz Jakab

NULL tanszekek tanszekKod

tanszekNev

FMT

Fotogrammetria

AGT

Geodézia

OCT

Szerves Kémia

Kapcsolatok (természetes) — SELECT

* FROM oktatok NATURAL JOIN tanszekek; oktato

tanszekKod

tanszekNev

Koppányi Zoltán

FMT

Fotogrammetria

Lovas Tamás

FMT

Fotogrammetria

Tuchband Tamás

AGT

Geodézia

Kapcsolatok (INNER JOIN) — SELECT

* FROM oktatok INNER JOIN tanszekek ON oktatok.tanszekKod = tanszekek.tanszekKod; oktatok.oktato

oktatok.tansz ekKod

tanszekek.tan szekKod

tanszekek.tan szekNev

Koppányi Zoltán

FMT

FMT

Fotogrammetria

Lovas Tamás

FMT

FMT

Fotogrammetria

Tuchband Tamás

AGT

AGT

Geodézia

Kapcsolatok —

Lógó sor: olyan sor, amelyek egy másik reláció egyetlen sorával sem kapcsolódnak össze.

—

Külső összekapcsolások —

a külső kapcsolatok esetén a lógó sorokat is megjelenítjük

—

teljes (FULL OUTER JOIN)

—

jobbról, balról (LEFT OUTER JOIN, RIGHT OUTER JOIN)

Kapcsolatok (LEFT OUTER) — SELECT

* FROM oktatok LEFT OUTER JOIN tanszekek ON oktatok.tanszekKod = tanszekek.tanszekKod; oktatok.oktato

oktatok.tansz ekKod

tanszekek.tan szekKod

tanszekek.tan szekNev

Koppányi Zoltán

FMT

FMT

Fotogrammetria

Lovas Tamás

FMT

FMT

Fotogrammetria

Tuchband Tamás

AGT

AGT

Geodézia

Gipsz Jakab

NULL

NULL

NULL

Kapcsolatok (RIGHT OUTER) — SELECT

* FROM oktatok RIGHT OUTER JOIN tanszekek ON oktatok.tanszekKod = tanszekek.tanszekKod; oktatok.oktato

oktatok.tansz ekKod

tanszekek.tan szekKod

tanszekek.tan szekNev

Koppányi Zoltán

FMT

FMT

Fotogrammetria

Lovas Tamás

FMT

FMT

Fotogrammetria

Tuchband Tamás

AGT

AGT

Geodézia

NULL

NULL

OCT

Szerves Kémia

Hivatkozási épség megőrzése —

NULL értékre állítás módszere

—

Kaszkádolt frissítés CREATE TABLE táblanév ( …, ON DELETE SET NULL ON UPDATE CASCADE );

Megszorítások —

Attribútumokra vonatkozó (definíciókor) CREATE TABLE hallgato ( neptun_kod VARCHAR(6) NOT NULL, atlag FLOAT CHECK (altag > 0), nem CHAR(1) CHECK (nem IN ('F','N')), ... );

Megszorítások —

Értéktartományra vonatkozó

—

PostgreSQL példa!

—

CREATE DOMAIN azonositok INT CHECK (VALUE >= 10000);

—

CREATE TABLE hallgatok ( azon azonositok, … );

Megszorítások — Rekordra

— CREATE

vonatkozó

TABLE hallgatok

( nev CHAR(30) UNIQUE, nem CHAR(1) CHECK (nem = 'N' OR nem = 'F') );

Megszorítások —

—

Elnevezés —

... nev CHAR(30) CONSTRAINT nevKulcs PRIMARY KEY ...

—

CREATE DOMAIN fizetes INT CONSTRAINT nagySzam CHECK (VALUE >= 100000);

Módosítás —

ALTER TABLE szinesz DROP CONSTRAINT nevKulcs;

—

ALTER TABLE rendezo ADD CONSTRAINT nagySzam;

Triggerek —

Ravasz :)

—

Esemény-feltétel-művelet szabály (+ tárolt eljárás/függvény)

—

Események bekövetkeztekor

—

—

beszúrás

—

törlés

—

módosítás

—

tranzakciók befejeződése

Ha a feltétel… —

teljesül

—

nem teljesül

Triggerek CREATE OR REPLACE FUNCTION bonusz_fv() RETURNS trigger AS $$ BEGIN -- NEW => az újonnan beszúrt sor -- ha az új sorban a jegy = 5 -- 10 bónusz pont jár IF NEW.jegy = 5 THEN NEW.bonusz = 10; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER bonusz BEFORE INSERT ON jegy FOR EACH ROW EXECUTE PROCEDURE bonusz_fv();

Triggerek — AFTER/BEFORE/INSTEAD — UPDATE — több

OF

OF attribútum/INSERT/DELETE

művelet

Mi lesz a ZH-ban?

Kérdések —

2 csoport, 5 kérdés

—

3 elméleti kérdés

—

—

2 rövidebb

—

1 kifejtős

—

a mai óra anyaga is!

2 gyakorlati kérdés —

relációs algebra

—

lekérdezés (rel. alg.)

Kulcsszavak —

adat, információ, metaadat, adatmodell, adatbázis, adatbázis kezelő rendszer, tábla, reláció, a reláció egy előfordulása, kulcs, szuperkulcs, összetett kulcs

—

E/K diagram: egyed, attribútum, kapcsolat, a kapcsolat multiplicitása, többágú kapcsolat, redundancia, inkonzisztencia, módosítási, beszúrási és törlési anomáliák, funkcionális függőség, dekompozíció

—

SQL: tábla létrehozása, módosítása, adatfeltöltés, adatmódosítás, lekérdezések, kapcsolatok, triggerek, megszorítások

Forrás —

Wikipedia —

http://hu.wikipedia.org/wiki/Adatb%C3%A1zis

—

http://en.wikipedia.org/wiki/Graph_database

—

http://en.wikipedia.org/wiki/Database

—

http://guide.couchdb.org

—

J. D. Ullman – J. Widom: Adatbázisrendszerek

—

M. J. Hernandez: Adatbázis-tervezés

—

Gajdos Sándor: Adatbázisok

—

Dominich Sándor: Adatbáziskezelő rendszerek

—

http://www.bigonehost.com

Köszönöm a figyelmet! Adatbázis rendszerek