Adatbázis Rendszerek MSc 2. Gy: MySQL – Táblák, adatok
B I T MAN
1/41
B Iv: T2015.03.01 MAN
Témakörök
SQL alapok
DDL utasítások
DML utasítások
DQL utasítások DCL utasítások
2/41
B I T MAN
Az SQL jellemzése Az SQL a relációs adatbázis-kezelők szabványosított
adatmanipulációs és lekérdező nyelve A relációs algebrára épül Nem algoritmikus: Parancsnyelv jellegű, megfogalmazhatjuk, mit akarunk csinálni, de a megoldási algoritmust nem kell megadni. Nincsenek benne ciklusok, feltételes elágazások, stb. Mintaillesztéses, halmazorientált: A táblákat mint a sorok (rekordok) halmazát tekintjük. Az adott utasításban megfogalmazott feltételnek eleget tevő összes sor részt vesz a műveletben
3/41
B I T MAN
Az SQL jellemzése 2. Szabványos: Illeszkedik az SQL szabványhoz. A
szabványban van egy SQL utasításcsoport, amelyet minden SQL alapú szoftver implementációnak meg kell valósítani, de mindegyik implementáció plusz lehetőséget is nyújt a standard SQLhez képest, felülről kompatibilis a szabvánnyal Fontosabb használati módjai: – Önállóan, fejlesztő eszközökben: pl.: SQL*Plus, – Beágyazva procedurális programozási nyelvekbe. pl.: C/C++, Pacal Az SQL nem DBMS!
4/41
B I T MAN
Az SQL jellemzése 3. Az SQL által lefedett területek:
– DDL: Data Definition Language – adatstruktúra definiáló utasítások – adatbázisok, táblák létrehozása, módosítása és törlése – DML: Data Manipulation Language – adatokon műveletet végző utasítások – adatok rögzítése, módosítása, törlése – DQL: Data Query Language – adat lekérdező utasítás – DCL: Data Control Language – adatvezérlő, felügyelő utasítások – tranzakciók kezelése, jogosultságok menedzselése
5/41
B I T MAN
Az SQL használata Tipikus műveleti sorrend: Objektumok, táblák létrehozása, adatok felvitele, adatok lekérdezése, adatok módosítása, szerkezetek módosítása, működési környezet beállítása. Rendszám Típus
Szín
Kor
Rendszám Típus
Szín
Kor
Fehér
6
JED-123
Nissan Ezüst
5
AKT-392
Trabant
GBC-765
ABC-765 6/41
Opel
Fehér
6
Opel
Kék
32
B I T MAN
Szintaxis Kisbetű és nagybetű a nyelv alapszavaiban egyenértékű.
Utasítások sorfolytonosan írhatók, lezárás
7/41
pontosvesszővel. Szövegkonstans: 'szöveg' Változó nincs, csak tábla- és oszlopnevekre lehet hivatkozni. Kifejezésben hivatkozás egy tábla adott oszlopára: tábla.oszlop (ha a tábla egyértelmű, akkor elhagyható). Logikai műveletek: AND, OR, NOT Az utasítások szintaxisának leírásánál az elhagyható részleteket szögletes zárójelek között vannak.
B I T MAN
Az SQL utasítások csoportosítása DDL: - objektum létrehozás - objektum megszüntetés - objektum módosítás
CREATE DROP ALTER
DML: - rekord felvitel - rekord törlés - rekord módosítás
INSERT DELETE UPDATE
DQL: - lekérdezés
SELECT
DCL: - védelem - tranzakció kezelés
GRANT, REVOKE COMMIT, ROLLBACK
8/41
B I T MAN
Témakörök
SQL alapok
DDL utasítások
DML utasítások
DQL utasítások DCL utasítások
9/41
B I T MAN
DDL – Adatstruktúra definiáló utasítások DDL – Data Definition Language
Adatszerkezetek (elsősorban táblák) létrehozása,
módosítása, törlése Egy tábla szerkezete, sémája a táblához tartozó mezőkkel egyértelműen megadható A mezők megadása a mező nevének és a mező adattípusának, valamint az integritási feltételeknek a kijelölésével történik Több tábla is létezhet ugyanazzal a szerkezettel, de a nevük nem lehet egyforma
10/41
B I T MAN
Tábla (vagy más objektum) létrehozása CREATE objektumtípus objektumnév paraméterek; CREATE TABLE táblanév ( mezőnév adattípus [integritási feltétel], mezőnév adattípus [integritási feltétel], ••• mezőnév adattípus [integritási feltétel] [ ,további integritási feltételek] ); CREATE TABLE Autó ( Rendszám char(7) PRIMARY KEY, Típus char(25) NOT NULL, Szín char(15) DEFAULT 'Fehér', Kor number(2) CHECK (Kor > 0) ); 11/41
Rendszám Típus
Szín
Kor
Fehér
6
JED-123
Nissan Ezüst
5
AKT-392
Trabant
GBC-765
Opel
Kék
32
B I T MAN
Adattípusok Alap adattípusok:
– Char(n) – n hosszúságú karaktersorozat, – Number(n,m) – n jegyű decimális szám, ebből m tizedes jegy; Number(8,2) ↔ 123456,12 – Date – dátum. További típusok (környezetfüggő!): – INTEGER – egész szám (röviden INT) – REAL – valós (lebegőpontos) szám – Time – időpont
12/41
B I T MAN
Integritási feltételek megadása Primary key – elsődleges kulcs
References – idegen kulcs Foreign key (mezőlista) References táblanév(mezőlista)
– idegen kulcs megadása ha a kulcs több mezőből áll Not null – nem maradhat üresen a mező, kötelező kitölteni Unique – a mező egyedi, nem kerülhet bele két egyforma érték Default – alapérték megadása Check – értékellenőrzés
13/41
B I T MAN
Tábla módosítása ALTER TABLE táblanév [ADD (újelem, ..., újelem)] [MODIFY (módosítás, ..., módosítás)] [DROP (oszlop, ..., oszlop)]; újelem: egy "oszlopnév adattípus [feltétel]” csoport módosítás: "oszlopnév adattípus [feltétel]” ALTER TABLE Autó ADD (Tulaj REFERENCES Ember);
Fontos: a mezők mérete általában csak növelhető, a
DROP funkciót pedig általában nem engedik a DBMS-ek. 14/41
B I T MAN
Tábla törlése DROP TABLE táblanév; Azok a táblák amelyekre más tábla hivatkozik, nem
törölhetők! DROP TABLE Autó;
15/41
B I T MAN
Témakörök
SQL alapok
DDL utasítások
DML utasítások
DQL utasítások DCL utasítások
16/41
B I T MAN
DML – adatokon műveletet végző utasítások DML – Data Manipulation Language
Rekordok (sorok) beszúrása (felvitele) Mezők értékének módosítása Rekordok törlése
17/41
B I T MAN
Rekordok beszúrása INSERT INTO táblanév [(oszloplista)] VALUES (értéklista); Rendszám Típus
INSERT INTO Autó VALUES ('AKT-392', 'Trabant', 'Kék', 32);
GBC-765 JED-123
Szín
Kor
Fehér
6
Nissan Ezüst
5
Opel
Az oszloplista elhagyható, de ekkor az összes mező
értékét meg kell adni, a megfelelő sorrendben! INSERT INTO Autó (Rendszám, Típus, Kor) VALUES ('ASD-602', 'Mazda', 7); INSERT INTO Autó VALUES ('ALM-332', 'Renault', Null, 8); INSERT INTO Autó VALUES ('IJK-222', 'Lada', Default, 25); 18/41
B I T MAN
Mezők értékének módosítása UPDATE táblanév SET mező = kifejezés, ..., mező = kifejezés [ WHERE feltétel ]; A feltételnek eleget tevő mezők módosulnak, Where
feltétel nélkül minden mezőt módosít! UPDATE Autó SET Kor=10 WHERE Rendszám='JED-123'; UPDATE Autó SET Szín= 'Barna'; UPDATE Autó SET Kor=10, Szín='Kék' WHERE Rendszám='JED-123';
A mezőben lévő aktuális érték is felhasználható: UPDATE Autó SET Ár = Ár * 1.2 WHERE Ár<1000000; 19/41
B I T MAN
Rekordok törlése DELETE FROM táblanév [ WHERE feltétel ]; Where feltétel nélkül minden sor törlődik! DELETE FROM Autó WHERE Kor>15;
20/41
B I T MAN
Témakörök
SQL alapok
DDL utasítások
DML utasítások
DQL utasítások DCL utasítások
21/41
B I T MAN
DQL – Adat lekérdező utasítás Most csak ízelítő!
DQL – Data Query Language Az SQL nyelvben egyetlen parancs, a SELECT parancs
szolgál az adatok lekérdezésére Feladata: Egy vagy több adattáblából egy eredménytábla előállítása, amely a képernyőn listázásra kerül, vagy más módon használható fel. SELECT oszloplista FROM táblalista [WHERE feltétel];
22/41
B I T MAN
DQL – Példák SELECT * FROM autó;
Szín
Kor
Ár
Fehér
6
1200000
JED-123
Nissan Ezüst
5
1500000
AKT-392
Trabant
32
25000
Rendszám Típus GBC-765
Opel
Kék
Rendszám Típus
SELECT Rendszám, Típus FROM autó;
GBC-765
Opel
JED-123
Nissan
AKT-392
Trabant
SELECT * FROM autó WHERE Kor<10; Szín
Kor
Ár
Fehér
6
1200000
Nissan Ezüst
5
1500000
Rendszám Típus GBC-765 JED-123
23/41
Opel
B I T MAN
Órai feladat 1. Készítsünk egy autos adatbázist, és nyissuk meg:
create database autos; use autos; Készítsünk egy táblát, autók nyilvántartására (neve: auto): auto_id (elsődleges kulcs) (max. 5 számjegy) rendszam (7 karakter) marka (20 karakter) tipus (20 karakter) uzembe helyezve (dátum) klima (igen, nem) 24/41
B I T MAN
Órai feladat 1. Írassuk ki az elkészült auto tábla szerkezetét. Vigyünk fel néhány adatsort: insert into auto values (1, 'ABC-321', 'Opel', 'Astra', '2010-03-15', 1); insert into auto values (2, 'BCD-582', 'Opel', 'Corsa', '2007-11-29', null); insert into auto values (3, 'CGT-616', 'Opel', 'Corsa', '2007-11-29'); insert into auto (auto_id, rendszam, marka, tipus, uzhely) values (3, 'CGT-616', 'Opel', 'Corsa', '2007-11-29'); insert into auto values (null, 'CGT-616', 'Opel', 'Corsa', '2007-11-29', 1);
25/41
B I T MAN
Órai feladat 1. Vigyünk fel néhány adatsort, közben próbáljuk ki:
– – – – –
26/41
Teljes adatsor megadása, Klíma mező kimarad, Rendszám mező kimarad, Auto_id mező kimarad. Csak ID-t és rendszámot viszünk fel.
B I T MAN
Órai feladat 1. Javítsuk ki az adatokat:
– Az 1-es id-jű autóban nincs klíma. update auto set klima=0 where auto_id=1; – A BCD-582 rendszámú autót 2008 május 10.-én helyezték üzembe: update auto set uzhely='2008-5-10' where rendszam='BCD-582'; – A 2-es id-jű autó egy Renault Megane. update auto set marka='Renault', tipus='Megane' where auto_id=2;
27/41
B I T MAN
Órai feladat 1. Bővítsük ki a táblát egy kategória mezővel (10 karakter).
alter table auto add column kategoria char(10); Egészítsük ki a meglévő rekordokat.
update auto set kategoria = 'közép' where auto_id = 1; update auto set kategoria = 'alsó' where auto_id = 2; Töröljük a táblát.
drop table auto;
28/41
B I T MAN
Órai feladat 2. Készítsünk egy táblát, autók nyilvántartására:
auto_id (elsődleges kulcs) (max. 5 számjegy) rendszám (7 karakter, nem lehet két egyforma) márka (20 karakter, kötelező megadni) típus (20 karakter, kötelező megadni) klíma (1 karakter, érték: I,N) kategória (10 karakter, érték: alsó, közép, felső) napidíj (max. 8 számjegy, nagyobb, mint 0, kötelező megadni, alapérték 10000) kilométerdíj (max. 8 számjegy, nagyobb, mint 0, kötelező megadni, alapérték 1000) 29/41
B I T MAN
Órai feladat 2. create table auto( auto_id numeric(5) primary key, rendszam varchar(7) not null unique, marka varchar(20) not null, tipus varchar(20) not null, klima char(1) not null check (klima in ('I','N')), kategoria varchar(5), napidij numeric(8) default 10000 not null check( napidij>0), kmdij numeric(8) default 1000 not null check (kmdij>0) );
30/41
B I T MAN
Órai feladat 2. Vigyük be az adatokat:
insert into auto values (1,'MBC-123', 'Honda','Accord', 'I','közép',12000,1200); insert into auto values (2,'MBC-124', 'Volkswagen','Polo', 'N','kis',7000,800); insert into auto values (3,'MBC-125', 'Nissan','Maxima QX', 'I','felső',22000,2200); insert into auto values (4,'KAS-126', 'Toyota','Yaris', 'N','kis',7000,700); Érdekesség: insert into auto values (5,'LGW-493', 'Mercedes','ML', 'K','felső',default,default); 31/41
B I T MAN
Órai feladat 2. Megoldás a klíma problémára:
DELIMITER // create trigger klima_teszt after insert on auto for each row begin if (select count(*) from auto where rendszam=new.rendszam and klima in ('I','N') < 1) then delete from auto where rendszam=new.rendszam; end if; end; // DELIMITER ; insert into auto values (6,'LTD-451', 'Toyota','Auris', 'K','kis',7000,700); 32/41
B I T MAN
Órai feladat 2. Próbálgassuk az adatfelvitelt, próbáljuk ki:
– – – – –
33/41
Teljes adatsor megadása alapadatok használatával, Klíma mező kimarad, Rendszám mező kimarad, Rendszám egyforma az előzővel, Auto_id mező kimarad.
B I T MAN
Órai feladat 3. Készítsük el a kölcsönző személyek tábláját:
create table kolcsonzo ( k_az numeric(5,0) primary key, nev varchar(50) not null, lakcim varchar(30) not null, telefon varchar(20) );
34/41
B I T MAN
Órai feladat 3. Vigyünk fel adatokat:
insert into kolcsonzo values(1,'Kis Béla','Valahol u 3.','1234567'); insert into kolcsonzo values(2,'Közepes Béla','Akármerre u 3.','1234568'); insert into kolcsonzo values(3,'Nagy Béla','Híd alatt tér 1.','1234569'); insert into kolcsonzo values(4,'Nagyon Béla','Aluljáró tér 4.','1234560'); Vidd fel saját magad a kolcsonzo táblába! Írasd ki a tábla tartalmát! 35/41
B I T MAN
Órai feladat 4. Hozzuk létre a kölcsönzések tábláját is:
create table kolcsonzes ( ko_id numeric(10) primary key, auto_id numeric(5) not null, torzsszam numeric(5) not null, ki_dat timestamp not null default current_timestamp, vissza_dat date, ki_km numeric(6) not null, vissza_km numeric(6), foreign key (auto_id) references auto(auto_id), foreign key (torzsszam) references kolcsonzo(k_az) );
36/41
B I T MAN
Órai feladat 4. Vigyünk fel adatokat:
insert into kolcsonzes values(11,1,1,adddate(sysdate(),100),adddate(sysdate(),-95),150,2000); insert into kolcsonzes values(2,1,1, adddate(sysdate(),-30), adddate(sysdate(),-25),18000,20000); insert into kolcsonzes values(3,1,1, adddate(sysdate(),-6), adddate(sysdate(),-1),22000,24000); sysdate() – mai nap adddate(sysdate(), -1) – tegnap adddate(sysdate(), +1) – holnap 37/41
B I T MAN
Órai feladat 4. Vigyünk fel adatokat:
insert into kolcsonzes values(4,3,2,'2015-02-23','2015-0228',100,300); insert into kolcsonzes values(5,3,2, adddate(sysdate(),-35), adddate(sysdate(),-33),2000,2871); insert into kolcsonzes values(6,3,2,sysdate()1,NULL,3122,NULL); insert into kolcsonzes values(7,1,3,sysdate()-70,sysdate()65,3800,4225); insert into kolcsonzes values(8,2,3,sysdate()-13,sysdate()10,100,1400); insert into kolcsonzes values(9,2,4,sysdate()3,NULL,1500,NULL); 38/41
B I T MAN
Órai feladat 4. Kölcsönözd ki saját magadnak tegnapelőtti elvitellel a
Toyota Aurist, induló km: 1725! Hozd vissza most a Toyotát, km: 2019! Hány km-t mentél az autóval (megtett út)? select vissza_km-ki_km Megtett_út from kolcsonzes; Hány napig volt nálad az autó? select DATEDIFF(vissza_dat, ki_dat) Eltelt_napok from kolcsonzes;
39/41
B I T MAN
Felhasznált irodalom Kovács László: PL/SQL, elektronikus jegyzet
Barabás Péter: Adatbázis rendszerek 2.,
elektronikus jegyzet Jeffery D. Ullman, Jennifer Widom: Adatbázisrendszerek Kende Mária, Nagy István: ORACLE példatár Don Burleson: Oracle Tips (www.dba-oracle.com)
40/41
B I T MAN
VÉGE V ÉGE 41/41
B I T MAN
