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 — Házi

tapasztalatok

— SQL

(és ami mögötte van)

Házi tapasztalatok

Házi tapasztalatok —

Házik besorolása megtörtént —

Oktatói visszajelzések a feladatok alatt

—

Kategóriák – nem jegy, inkább a jövőbeni munka Kevés egyed — Lekérdezések átgondolása — Kész feladat Javítani lehet bármikor —

—

—

Sorsolás legkésőbb kedd este

Házi tapasztalatok —

Nyilvántartás és nem tervezési feladat

—

Geometriai elemzésekre nincs lehetőség accessben

—

Hibalista

—

Nyers adatok fogalma vs. teljesen feldolgozott adatok

—

Ékezetes fájlok

—

Rar tömörítés helyett Zip használata ajánlott

—

4 egyed, lekérdezések, adatmennyiség —

Esélyegyenlőség miatt fontos

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]

Áttérés relációs algebráról

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/AutoNumber

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 kapcsolatok frissítésére is!

kell

a

többi

táblával

való

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

Szelekció SQL-ben — Lekérdezés — SELECT

Péter';

eredmény sorainak szűrése

* FROM hallgato WHERE nev='Kis

— Több

feltétel is lehet, logikai kapcsolat közöttük

— Módosítási

jelentőségű

parancsoknál

különös

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: AND, OR

—

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

—

ismétlődések megszüntetése (DISTINCT)

—

aliasok (AS) -

—

különleges karakterek: *, _, % (Access-ben: *, ?, #)

—

escape karakterek

—

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

…

…

Sorok számának korlátozása —

SELECT * FROM tabla LIMIT sorok_szama kezdo_sor;

—

SELECT nev, jegy FROM hallgatok ORDER BY jegy LIMIT 1;

—

Ha egy sort keresünk, érdemes ezt beleírni, mert gyorsítja a lekérdezést, hiszen nem keres tovább.

—

Access: SELECT TOP 1 FROM hallgatok ORDER BY jegy;

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 AVG(jegy)>=3;

Függvények használata —

SELECT COUNT(*) FROM hallgato;

—

SELECT AVG(jegy) FROM hallgato;

—

SELECT MIN(jegy) FROM hallgato;

—

SELECT MAX(jegy) FROM hallgato;

— SELECT

SUM(jegy)/COUNT(*) FROM hallgato jegy IS NOT NULL;

— SELECT

DISTINCT szuletesi_hely FROM hallgato;

Szöveg függvények —

SELECT vnev || " " || knev FROM hallgatok;

—

SELECT * FROM hallgatok WHERE vnev || " " || knev LIKE "Nagy Béla";

—

MySQL (MariaDB): CONCAT()

—

PgSQL: ||

—

Access: &

—

SELECT * FROM tantargyak WHERE SUBSTRING(kod FROM 6 TO 7)="FT";

Szabadszavas kersés — mezo

LIKE 'szoveg'

— SELECT

'Kiss%';

— Joker

* FROM hallgatok WHERE nev LIKE

karakterek:

—

%: tetszőleges karakter

karakter;

—

_: egy tetszőleges karakter

0,1,sok

Bonyolultabb lekérdezések —

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

—

SELECT nev FROM hallgato WHERE nev LIKE 'Kov_cs%';

—

SELECT eloadas_hossza*60 AS perc FROM targyak;

—

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

Metszet/Unió — Á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;

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