A MYSQL NYELV ALAPJAI

A MySQL NYELV Alapjai

1.1    Miért Mysql?

Az utóbbi években nagyon elterjedt az adatbázisok használata az interneten. Számos olyan fontos alkalmazás van, amely ilyen adatbázisokat használ. Például:

Elektronikus vásárlás: olyan alkalmazások, amelyek segítségével bizonyos cégek felkínálják és eladják a termékeiket. Ebben az esetben az adatbázisok tartalmazzák egyfelöl a tudnivalókat a termékekröl, másfelöl pedig a vásárlók rendeléseit és adatait. Az elektronikus vásárlás elönye hatalmas mert egyáltalán nincs üzlethelyiség fenntartásából adodó kiadás és mert nem helyhez kötött, gyakorlatilag az egész világon bárhol kerülhet vevö.

Elektronikus szavazás: olyan alkalmazások, amelyek segítségével megtudható a látogatók véleménye egy adott kérdésben (valamilyen témával kapcsolatban). Ezeknél az alkalmazásoknál igen fontos, hogy egyetlen látogató csak egyszer szavazhasson ezért adatbázisba kerül a látogatók IP címe.

Komunikáció: olyan alklmazások, amelyek segítségével egy bizonyos site látogatói megvitatnak egy adott témát - különbözö forumok. Ebben az esetben az adatbázisba információkat rögzítenek azokról akik hozzászólnak az adott témához (általában egyszer fel kell íratkozni) és magukat a hozzászólásokat is adatbázisban tárolják.

Egy adott site látogatottságának a mérése: ezek az alkalmazások pontos képet nyujtanak arról, hogy hányan léptek be az illetö sitera, vagy bizonyos oldalakat hányan néztek meg. A szavazáshoz hasonlóan szükség van az IP címek tárolására, ami szintén adatbázisba kerül.

Folytathatnánk a sort a keresö alkalmazásokkal, különbözö tvk, ujságok website-val.... Éppen azért mert ennyire elterjedt lássuk hogyan lehet ilyen adatbázisokat létrehozni és használni.

Mi az SQL ? A relációs adatbázisok létrehozásának és felhasználásának gyakorlatba ültetése szükségessé tette egy olyan standard nyelv létezését, amely lehetövé teszi ezen müveletek elvégzését. Így jelent meg az SQL-Structured Query Language. A nyelvet jóváhagyta az ANSI standardizáló bizotság. Ez nem egy adott céghez tartozó nyelv. Számos adatbáziskezelö rendszer használja: Microsoft Access, Oracle, Microsoft SQL Server és természetesen a MySql.

Mi a MySql ? A MySQL egy többfelhasználós, többszálú, SQL-alapú relációs adatbázis-kezelö szerver. A szoftver fejlesztöje a svéd MySQLAB cég, amely kettös licenceléssel teszi elérhetövé a MySQL-t; választható módon vagy a GPL, vagy egy kereskedelmi licenc érvényes a felhasználásra. 2008 januárjában a Sun felvásárolta a céget. Az MySQL az egyik legelterjedtebb adatbázis-kezelö, aminek egyik oka lehet, hogy a teljesen nyílt forráskódú LAMP (Linux-Apache-MySQL-PHP) összeállítás részeként költséghatékony és egyszerüen beállítható megoldást ad dinamikus webhelyek szolgáltatására. A MySQL adatbázisok adminisztrációjára a mellékelt parancssori eszközöket ( mysql és mysqladmin) használhatjuk.

A MySQL honlapjáról grafikus felületü adminisztráló eszközök is letölthetök: MySQL Administrator és MySQL Query Browser. Széles körben elterjedt és népszerü adminisztrációs eszköz a PHP nyelven írt, nyitott forráskódú phpMyAdmin. A phpMyBackupPro (amelyet szintén PHP-ban írtak) adatbázisok (akár idözített, ismétlödö) mentésére szolgál eszközül.

1.2    Adatbázis létrehozása

MySqlben egy adatbázist a

CREATE DATABASE adatbazis_neve;

paranccsal hozunk létre. Ha már létezik ilyen nevü adatbázis, akkor hibaüzenetet kapunk és az adatbázis nem jön létre.

USE adatbazis_neve;

paranccsal megnyítunk egy adatbázist. Ha nem létezik ilyen nevü adatbázis, akkor hibaüzenetet kapunk.

DROP DATABASE adatbazis_neve;

paranccsal kitörlünk egy adatbázist. Ha nem létezik ilyen nevü adatbázis, akkor hibaüzenetet kapunk.

SHOW DATABASES

paranccsal megkapjuk a létezö adatbázisok listáját.

1.3    Adattipusok. Müveletek. Függvények MySqlben

Adattípusok

A Mysql nyelvben megtalálhatóak a következö típusok:

a)     Szöveg típusok(Karakterlánc).

b)     Numerikus típusok.

c)     Dátum és idö típusok.

a)     Szöveg típusok.

b)     Numerikus típusok

c)     Dátum és idö típusok.

Müveletek

A legfontosabb müveletek a proirításuk szerinti növekvö sorrendben:

1.     ||, OR, XOR

2.     &&, AND

3.     BETWEEN, CASE WHEN THEN ELSE

4.     ==, >=, <=, <, >, !=, <>, IS, LIKE, IN

5.    

6.     &

7.     <<, >>

8.     -, +(bináris müv.)

9.     *, /, DIV, %, MOD

10. 

11.  -, +(unáris müv.)

12.  !, NOT

a)     Arítmetikai müveletek

b)     Összehasonlítási müveletek

Összehasonlíthatunk két számot illetve két karakterláncot. Karakterláncok hasonlítása esetén a MySQL nem tesz különbséget a nagy- és kisbetük között. Az összehasonlítás eredménye 1 ha igaz és 0 ha hamis

Megj: két DATE vagy TIME típusú adatot is össze tudunk hasonlítani, ebben a sorrendben is mint a karakterláncok esetében lexikográfikus soorrend számít.

c)     Logikai müveletek:

A mysql nyelvben két logikai érték van a False, aminek a megfelelö értéke a 0 éa a True, aminek a megfelelö ártéke az 1 vagy bármely 0-tól különbözö érték. A logikai müveletek a logikai adatokkal dolgoznak és 1-et térítenek vissza igaz esetén, 0-t hamis esetén.

d)     Logikai müveletek bitekre. Bit eltolási müveletek:

Az egész típusú adatokra alkalmazzuk, és a bitenkénti ábrázolással operálnak.

e)     Más müveletek:

IS NULL, IS NOT NULL: ellenörzi, hogy egy érték NULL(lásd 3.5.2 fejezet) vagy sem.

Pl.:1 IS NULL =>0; NULL IS NULL =>1; 1 IS NOT NULL =>1; NULL IS NOT NULL =>0

IN, NOT IN: ellenörzi, hogy egy érték hozzátartozik vagy sem egy halmazhoz. Az érték lehet numerikus vagy karakterlánc típusú.

Pl.: 1 IN(1,2,3,4) =>1; 5 IN(1,2,3,4) =>0; 'abc1' IN('abc1','abc2','abc3','abc4') =>1; '1abc' IN('abc1','abc2','abc3','abc4') =>0

LIKE, NOT LIKE: ellenörzi, hogy egy bizonyos karakterláncra teljesülnek bizonyos feltételek vagy sem. Ha egy bizonyos részkaraktersor(%) vagy egy bizonyos karakter(_) megtalálható a karakterlánc elején végén vagy középen.

Pl. marika' LIKE 'mar%' =>1 ; 'marika' LIKE 'mam%' =>0; 'marika' NOT LIKE 'mar%' =>0

BETWEEN min AND max: ellenörzi, hogy egy adott érték egy adott minimális érték és agy adott maximális érték között van vagy sem. Használható számokra de karakterláncokra és Date vagy Time tipusú adatokra is.

Pl.: 1 BETWEEN 0 AND 4 =>1; 'mama' BETWEEN 'min' AND 'lin' =>1; 2 BETWEEN 2 AND 2 =>1; ! BETWEEN 2 AND 4 =>0; '1999-10-31' BETWEEN '1989-12-10' AND '2000-01-01' =>1

CASE WHEN THEN ELSE : két formája van:

CASE  v

WHEN v₁ THEN e₁

[WHEN v₂ THEN e₂]

[WHEN v_n THEN e_n]

[ELSE e_n+1]

END

Kiszámolja a v értékét és ha megegyezik e_i-vel, akkor a visszatérített érték e_i lesz. Ha a v egyetlen v_i-vel sem egyezik meg akkor a visszatérített érték e_n+1 lesz.

CASE

WHEN felt₁ THEN e₁

[WHEN felt₂ THEN e₂]

[WHEN felt_n THEN e_n]

ELSE e_n+1

END

Ebben az esetben ha a felt_i teljesül akkor e_i -t téríti vissza. Ha egyik feltétel sem teljesül akkor az e_n+1 -t téríti vissza.

Pl.: Case 2

When 1 then 'egy'

When 2 then 'ketto'

When 3 then 'harom'

Else 'mas ertek'

End;

Vagy

Case

When 1<2 then 'igaz'

Else 'hamis'

End;

Függvények

Mivel a Mysql nyelv függvényei hasonlítanak a többi programozási nyelvekben használt függvényekhez rövid leírást adok rólluk:

MÁS FÜGGVÉNYEK

IF: IF(kif₁, kif₂ ,kif₃): a kif₁ egész tipusú, ha különbözik 0-tól, akkor az eredmény a kif₂ ha egyenlö 0-val akkor az eredmény kif_3..

Pl. IF(1>2, 'igaz','hamis') =>'igaz'

IFNULL:IFNULL(kif₁, kif₂): ha a kif₁ nem NULL akkor az eredmény a kif₁ különben az eredmény a kif_2.

1.4    Az alapelemek meghatározásai

A legfontosabb elemek mehatározásai: táblák, mezők, rekordok, kulcsok és indexek

Táblák Az adatbázisok legnagyobb alkotórészei. A táblákat közvetlenül az adatbázisok után hozzuk létre. Amikor egy adatbázisban új táblát hozunk létre három különböző állomány jön létre. Ezek tárolják a tábla meghatározását, a benne tárolt adatokat és az indexeket. A létrehozott táblát törölhetjük, vagy lekérdező parancsokkal módosíthatjuk. A tábla szerkezeti felépítésére oda kell figyelnünk mert ha egy 200 karakter befogadására képes mezőt 100 karakteresre módosítunk akkor a fennmaradó karakterek levágásra kerülnek.

Mezők Egy MySQL tábla maximálisan 3398 mezőt tartalmazhat, azonban érdemes megfontolnunk hogy elrontottunk-e valamit ha ezt a lehetőséget maximálisan kihasználtuk, ugyanis ekkora mennyiségre csak nagyon ritkán van szükség. Három lényeges dolgot kell új mező létrehozásánál megadnunk: a mező nevét a mezö típusát és a mező hosszát. A mezők pontos megadása kiemelkedően fontos. Gondoljunk csak bele, hogy egy megrendelőlapon nem adunk elegendő hosszúságot a címnek, így nem töltheti ki a megrendelő megfelelően. Ebben az esetben eleshetünk a bevételtől.

Rekordok A rekord egy bejegyzés a táblában. A rekord lehet teljesen kitöltött (complete), vagy kitöltetlen (incomplete). Az hogy egy rekord incomplete az nem jelenti feltétlenül azt hogy a rekord üres. Egy mezőhöz rendelhetünk alapértelmezett beállítást is, például az ország esetében beállíthatjuk hogy ha nem ad meg a felhasználó értéket akkor az legyen "RO". Fontos hogy lehetőség szerint teljesen kitöltött rekodokkal rendelkezzünk.

Kulcsok A kulcsok MySQL tábláink és rekordjaink hatékony elemei lehetnek. Segítségükkel összekapcsolhatjuk tábláinkat. Két típusát különböztetjük meg. Vannak egyedi (unique) és elsődleges (primary) kulcsok. Amikor egy mezőt egyedinek adunk meg, akkor egyféle adatot csak egyszer vihetünk fel egyetlen rekordba (sorba). Ha például a tejet "a01" azonosítóval rögzítjük, akkor nem adhatjuk meg ugyanezt az azonosítót a cukornak is. Az elsődleges kulcs összekötő elemként működik egy vagy több tábla között. Egy tábla csak egy elsődleges kulccsal és több egyedi kulccsal rendelkezhet.

Indexek Az adatbázisok indexei segítenek abban hogy gyorsabban megtaláljuk amit keresünk. Amikor az adatbázis által indexelt rekordokat válogatunk ki, sokkal gyorsabban lefut a lekérdezés, és így az eredményt is hamarabb megkapjuk, mintha ezt egy nem indexelt táblában tennénk. Hátránya viszont hogy ha egy indexelt táblába szúrunk be rekordot az kicsit lassúbb, tehát a legtöbb esetben inkább az olvasott mint írt táblákhoz érdemes ezt a lehetőséget használni.

1.5    Táblák

3.5.1         Alapmüveletek táblákkal: tábla létrehozása, adatsor beszúrása , kiírás, számított oszlopok

A tábák létrehozása a következö paranccsal történik

CREATE TABLE táblaneve (mezö_{1_}neve tipus [tulajdonságok], mezö_{2_}neve tipus [tulajdonságok],... mezö_{n_}neve tipus [tulajdonságok]);

A tulajdonságok a kulcsokra, az alapértelmezett értékekre, a NULL érték lehetségességére stb. vonatkoznak (lásd a következö fejezetekben).

Feladat: készítsünk egy három oszloppal rendelkezö táblát egy anyagraktár részére , ahol az elsö oszlop az anyag azonosítója legyen (legtöbb 5 karakterböl állhat), a második az anyag neve   (legtöbb 5 karakterböl állhat ), a harmadik pedig a mennyiség mindenik anyagból (darabban kifejezve, legtöbb 3 számjegyü természetes szám).

Megoldás

CREATE TABLE anyagok (kod char(5), nev char(20), mennyiség int(3))

Új rekord(bejegyzés) hozzáadása a táblához a következö paranccsal történik:

INSERT INTO táblaneve VALUES (adat₁, adat₂,... adat_n,);

Mivel a fenti parancs hib hoz vezethet, abban az esetben ha elrontjuk az adatok sorrendjét és így hibás adtok kerülhetnek a táblába, gyakrabban használjuk a következö formát még akkor is, ha egy kicsit többet kell írni.

INSERT INTO táblaneve (mezö_{1_}neve , mezö_{2_}neve ,... mezö_{n_}neve) VALUES (adat₁, adat₂,... adat_n,);

Feladat: a fenti anyagok táblába szúrjunk be két rekordot.

Megoldás

INSERT INTO anyagok VALUES('001','papir',78

INSERT INTO anyagok (kod, nev, mennyiseg) VALUES('007','rajzszeg',54

A tábla tartalmának a kiírása a következö paranccsal történik:

SELECT * FROM táblaneve;

Feladat: a fenti anyagok tábla esetén:

Megoldás:

SELECT * FROM anyagok;

Az erdmény pedig:

Lehetöség van arra is, hogy ne írjuk ki a tábla valamennyi oszlopát, ebben az esetben a fenti parancs következö formáját használjuk:

SELECT mezö_{1_}neve , mezö_{2_}neve ,... mezö_{n_}neve FROM táblaneve;

Abban az esetben ha azt szeretnénk, hogy a kiírt táblázat fejlécében valamelyik oszlop esetén más jelenjen meg, ne a mezö neve a fenti parancs következö formáját használjuk:

SELECT mezö_{1_}neve AS oszlop₁, mezö_{2_}neve AS oszlop₂,... mezö_{n_}neve AS oszlop_n, FROM táblaneve;

Egy adatbázisban levö összes táblát megnézhatjük

SHOW TABLES [FROM adatbazisneve];

Egy adatb zis mezöinek nevét, tipusát és tulajdonságait megnézhetjük

SHOW COLUMNS FROM táblaneve;

Feladat: a fenti anyagok táblába esetén:

Megoldás:

SHOW COLUMNS FROM anyagok;

Az eredmény:

Az táblák megtervezésénél és létrehozásánál mindig oda kell figyelni arra, hogy egy táblában olyan adatokat örizzünk meg amelyeket nem lehet kiszámítani. Ellenkezö esetben a táblák mérete nagy lesz. Lássuk hogyan tudunk kiszámítani bizonyos adatokat a táblák adatait felhasználva.

SELECT mezö_{1_}neve , mezö_{2_}neve ,... mezö_{n_}neve, kifejezes₁ AS oszlop₁, kifejezes₂ AS oszlop₂, ... kifejezes_k AS oszlop_k FROM táblaneve;

Feladat: Vegyük a következö termekek táblát amely egy raktárban levö termékeket tart nyilván. Minden termék esetén megörizzük a nevet (nev),a mennyisget az illetö termékböl(mennyiseg) és az egységárát(egyseg_ar). Szertnénk kilistázni azt, hogy milyen értéket képviselnek az egyes termékek. Tudjuk azt, hogy az értéket a mennyiség és az egysegár szorzata adja. Ebben az esetben a tábla adatainak kiírásánál beteszünk egy ujabb oszlopot amelybe az mennyiseg*egyseg_ar fog kerülni az oszlop neve pedig ertek lesz.

Megoldás:

SELECT nev, mennyiseg, egyseg_ar, mennyiseg*egyseg_ar AS ertek FROM termekek

Ha a termekek tábla így néz ki :

A kiírás után a következö eredményt kapjuk:

3.5.2       A NULL érték

Mysql-ben létezik egy különleges érték a NULL, aminek az értelme: érték nélküli. Amikor létrehozunk egy táblát a tábla mezöi automatikusan megkapják a NULL tulajdonságot, de megadhatjuk a NOT NULL tulajdonságot is.

Ha egy oszlopnál a NULL érték van beállítva és amikor megadunk egy bejegyzést és az illetö oszlophoz nem rendelünk értéket, akkor oda a NULL érték fog bekerülni bármilyen típusú is legyen az illetö oszlop.

Ha egy oszlopnál a NOT NULL érték van beállítva és amikor megadunk egy bejegyzést és az illetö oszlophoz nem rendelünk értéket, akkor oda a :

0 kerül, ha az oszlop numerikus típusú

üres karakterlánc, ha karakterlánc tipusú

a sorozat elsö azaz 0-ik sorszámú tagja, ha ENUM típusú

üres halmaz ha SET típusú

Feladat: létrehozzuk a teszt1 nevü táblát:

CREATE TABLE teszt1 (o1 int , o2 int not null)

Ha megn zzük ezután a teszt1 tábla szerkezetét, az így néz ki:

Beszúrunk három bejegyzést:

INSERT INTO teszt1 VALUES (10,15)

INSERT INTO teszt1 (o2) VALUES (13)

INSERT INTO teszt1 (o1)VALUES (17)

És az eredményt SELECT * FROM teszt1; paranccsal megnézhetjük:

3.5.3 Az Alapértelmezett ( DEFAULT) érték

A valóságban nem mindig ismerjük az adtokat. Ebben az esetben ha bizonyos oszlopok értékeit nem ísmerjük ezeket állíthatjuk NULL-ra, de állíthatjuk egy kedvezöen megválasztott kezdöértékre.

Ha van egy táblánk a tanulók vizsgajegyeivel és egy tanuló nem vesz részt a vizsgán az ö jegye 1-es legyen. (se nem NULL se nem 0). Ezt a DEFAULT kezdo_ertek formában adjuk meg amikor a táblát létrehozzuk.

Megj.          Kezdöértéket csak a meghatározott hosszusággal rendelkezö mezökhez rendelhetünk pl ha a mezö típusa CHAR(20) akkor kaphat kezdöértéket de ha TEXT a típusa akkor nem.

Feladat: Legyen egy teszt2 táblánk, amely bizonyos személyek nevét (nev) és szülövárosát (szul_varos) tartalmazza, azt kívánjuk, hogy azoknál a személyeknél ahol nem adjuk meg a szülövárost az ismeretlen' szó jelenjen meg.

Megoldás:

CREATE TABLE teszt2 (nev CHAR(25), szul_varos CHAR(30) DEFAULT 'ismeretlen'

Ha megn zzük ezután a teszt2 tábla szerkezetét, az így néz ki:

Utánna beteszünk a táblába két bejegyzést, ugy hogy az egyiknek nem adjuk meg a szuletési városát.

INSERT INTO teszt2 (nev) VALUES ('Balint Bela'

INSERT INTO teszt2 VALUES ('Daniel Richard', 'Marosvasarhely'

Az eredmény:

3.5.4         Az elsödleges (PRIMARY) kulcs és az egyedi (UNIQUE) kulcs

Az elsödleges kulcs -PRIMARY KEY, egy vagy több mezöböl állhat és a következö feltételeket kell teljesítse:

o      Az adatok abban az oszlopban amely az elsödleges kulcs föltétlenül különbözöek kell legyenek. Abban az esetben ha az elsödleges kulcs több mezöböl áll, az értékek a kulcsot alkotó mezök közül legalább egyik mezöben eltéröek kell legyenek Ha a táblába olyan bejegyzést akarunk beírni aminek az elsödleges kulcsa megegyezik valamelyik másik bejegyzés elsödleges kulcsával hibát kapunk.

o      Azok a mezök amelyek elsödleges kulcsot alkotnak föltétlenül meghatározott hosszuságuak kell legyenek. (Nem lehet TEXT tipusú.)

Az elsödleges kulcs szerepe abban áll, hogy nagyon meggyorsítja a keresést adott kulcs szerint. Tudnunk kell, hogy Mysql-ben a bejegyzések a bevitel sorrendjében tárolódnak el a táblában. Ha egy táblánál beállítunk egy egyedi kulcsot, akkor a tábla mellett létrejön egy segédtábla, (a felhasználó számára láthatatlan) amely a kulcs értékét tartalmazza és azt a poziciót, amelyet az illetö bejegyzés elfoglalna, ha a kulcsok értéke szerint növekvö sorrendbe rendeznénk az adtokat. Ily modon a keresés egy rendezetlen tábla helyett rendezett táblában történik, és egy sokkal gyorsabb algoritmust: pl. bináris keresést használja.

Ahhoz, hogy egy mezö elsödleges kulcs legyen, a tábla létrehozásakor az illetö mezö neve és típusa után beírjuk a PRIMARY KEY tulajdonságot. Ha több mezöböl álló elsödleges kulcsot akarunk létrehozni akkor a tábla létrehozásakkor a mezök leírása után felsoroljuk az elsödleges kulcsban résztvevö mezöket, ugy hogy : PRIMARY KEY(mezö_{1_}neve, mezö_{2_}neve, ...). Vegyük a következö feladatot a 3.5.1 fejezetböl

Feladat: készítsünk egy három oszloppal rendelkezö táblát egy anyagraktár részére , ahol az elsö oszlop az anyag azonosítója legyen (legtöbb 5 karakterböl állhat), a második az anyag neve   (legtöbb 5 karakterböl állhat ), a harmadik pedig a mennyiség mindenik anyagból (darabban kifejezve, legtöbb 3 számjegyü természetes szám). Állítsuk be a kod-ot elsödleges kulcsnak.

Megoldás:

CREATE TABLE anyagok (kod char(5) PRIMARY KEY, nev char(20), mennyiség int(3))

Az erdmény:

Két mezöböl álló elsödleges kulcsra példa a lenti tábla:

CREATE TABLE teszt3 (c1 INT, c2 CHAR(10), c3 TEXT, PRIMARY KEY(c1,c2))

Amire a következö táblát kapjuk:

Az egyedi kulcsot akkor használjuk ha azt kivánjuk elérni, hogy egy vagy több oszlopban csak különbözö értékek legyenek. Ugyanugy a tábla létrehozásánál amelyik mezök után odaírjuk az UNIQUE KEY tulajdonságot, azokból a mezökböl egyedi kulcs lesz. Ha egy bejegyzésnél egy ilyen mezönek olyan értéket probálunk adni ami már volt az illetö oszlopban, hibát kapunk. Azok a mezök amelyek egyedi kulcsot alkotnak föltétlenül meghatározott hosszuságuak kell legyenek.(Nem lehet TEXT tipusú).

Feladat: Hozzuk létre a teszt4 táblát két egyedi kulccsal

CREATE TABLE teszt4 (c1 INT UNIQUE KEY,c2 CHAR(10) UNIQUE KEY)

Amire a következö táblát kapjuk:

3.5.5       Az AUTOINCREMENT tulajdonság

Gyakran készítünk olyan táblát amiben a bejegyzéseket sorszámozzuk A Mysql lehetöséget nyújt olyan mezö létrehozására, amely autómatikusan sorszámozza a bejegyzéseket. Ahhoz, hogy ilyen mezöt tegyünk egy táblába szükséges a tábla létrehozásakkor ezt a mezöt INT típusúra állítani, azért hogy az értékek ne ismétlödhessenek szükséges elsödleges vagy egyedi kulcsot csinálni belöle ( lásd a 3.5.3 fejezetet), a mezö létrehozásakkor használjuk az AUTO_INCREMENT tulajdonságot amit a mezö neve tipusa és egyéb tulajdonságai után írunk.

Feladat: Hozzuk létre a teszt5 táblát aminek legyen egy sorszám mezöje.

CREATE TABLE teszt5 (sorszam INT UNIQUE KEY AUTO_INCREMENT, nev CHAR(20))

És utánna beszurunk két bejegyzést:

INSERT INTO teszt5 (nev) VALUES ('Piroska') ;

INSERT INTO teszt5 (nev) VALUES ('Marci') ;

És az eredmény:

Az is lehetséges, hogy egy ilyen sorszámozó mezönek mi adjunk értéket, ebben az estben a további sorszámozást az adott értéktöl fogja folytatni a mysql.

Szúrjuk be az alábbi két sort és vizsgáljuk meg a táblánkat.

INSERT INTO teszt5 VALUES (7,'Annacska') ;

INSERT INTO teszt5 (nev) VALUES ('Jancsika')

És az eredmény:

3.5.6         Tábla adatainak rendezése

Sokszor szükség van arra, hogy egy tábla adatait egyik vagy másik oszlopa szerint (esetenként több oszlop szerint is) növekvö vagy csökkenö sorrendbe rendezzük. A parancs amivel ezt elérhetjük:

SELECT * FROM táblaneve ORDER BY mezö_{1_}neve [DESC], mezö_{2_}neve [DESC],... mezö_{n_}neve [DESC];

Feladatok:

Vegyük a következö táblát:

1. Nézzük meg a tábla adatait a név oszlop szerint rendezve. Ehhez a következö parancsot adjuk:

SELECT * FROM rendezes ORDER BY nev;

Az eredmény:

Megj.: autómatikusan növekvö sorrendbe rendez, ha csökkenö sorrendet akarunk akkor használnunk kell a DESC tulajdonságot.

2. Lássuk, hogy rendezzük az adatokat a születési dátum  szerint csökkenö sorrendbe:

SELECT * FROM rendezes ORDER BY szul_datum DESC

Az eredmény:

3. Lehetöség van arra is, hogy egy tábla adatait több mezö szerint rendezzük. Ez a gyakorlatban úgy történik hogy az elsö mezö szerint rendezödnek az adatok és ott ahol az elsö mezö értéke megegyezik ott rendezödnek a második szerint és így tovább.

Tehát ha a fenti táblára a :

SELECT * FROM rendezes ORDER BY nev, szul_datum DESC;

Az eredmény:

(Észrevehetjük, hogy ahol a név megeggyezett a Miklós esetében a két sor felcserélödött a szül_datum szerinti csökkenö sorrend szerint).

3.5.7         Tábla adatainak szürése

Bizonyos esetekben nem érdekel a tábla valamennyi adata, csak azokra a sorokra vagyunk kiváncsiak, amelyekre teljesülnek bizonyos feltételek. Ezt a müveletet nevezzük szürésnek. Ezt úgy tudjuk elérni, hogy a SELECT parancs végére beírjunk egy WHERE feltétel, megjegyzést. Ebben az esetben csak azokat a bejegyzéseket írjuk ki amelyekre a feltétel teljesül. Abban az esetben ha a szürt adatokat rendezve szeretnénk kiírni a parancs végére a WHERE után fogjuk odaírni az ORDER BY megjegyzést.

Feladatok

Vegyük a következö táblát:

1. Írjuk ki azon tanulók nevét akiknek a metek jegye nagyobb vagy egyenlö mint 8.

SELECT nev FROM tanulok WHERE matek >=8;

Az eredmény:

2. És, ha ugyanazt a listát abc sorrendben szeretnénk látni a nevek szerint akkor:

SELECT nev FROM tanulok WHERE matek >=8 ORDER BY nev;

Akkor az eredmény:

3. Írjuk ki azoknak a tanulóknak a nevét és az általánosát a három tantárgyból akiknek mindhárom tantárgyból átmenö jegyük van.

SELECT nev, (matek+angol+info)/3 AS altalanos FROM tanulok WHERE matek >4 AND angol>4 AND info>4   ;

Az eredmény:

3.5.8         Tábla adatainak módosítása

A.     A tábla bejegyzéseinek a módosítása

Bejegyzés beszúrását láttuk már a 3.5.1. fejezetben

INSERT INTO táblaneve (mezö_{1_}neve , mezö_{2_}neve ,... mezö_{n_}neve) VALUES (adat₁, adat₂,... adat_n,);

Ha egy tábla bejegyzéseit egy másik táblából akarjuk átvenni, azt az alábbi paranccsal tudjuk megtenni:

INSERT INTO [DISTINCT] tábla₁ (mezö_{1_}neve , mezö_{2_}neve ,... mezö_{n_}neve) SELECT mezö₁ , mezö₂ ,... mezö_n FROM tábla₂ ;

Megj:

Ha a DISTINCT megjegyzés jelen van, csak azokat a sorokat veszi át, amelyek nem ismétlödnek.

Ha nem szeretnénk a tábla₂ összes sorát átvenni csak bizonyos sorokat használhatjuk a WHERE megjegyzést.

Feladat: legyen a két alábbi tábla a teszt2 és a teszt6, másoljuk be a teszt6 táblából a teszt2 táblába azokat a bejegyzéseket, amelyeknek szülövárosa 'Marosvasarhely'

A teszt2:

A teszt6:

Megoldás:

INSERT INTO teszt2 (nev,szul_varos) SELECT nev, szul_varos FROM teszt6 WHERE szul_varos='Marosvasarhely'

Az eredmény:

Ha egy táblában egy vagy több oszlop adatait akarjuk módosítani azt az alábbi paranccsal tudjuk megtenni:

UPDATE táblaneve SET oszlop₁=kifejezes₁, oszlop₂=kifejezes₂,... oszlop₃=kifejezes₃ [WHERE kifejezes]

Feladat: Vegyük az elöbbi teszt2 táblát. Módosítsuk a tábla adatait úgy, hogy minden 'B' betüvel kezdödö nevü személy szul_varosa 'Kolozsvar' legyen.

Megoldás:

UPDATE teszt2 SET szul_varos='Kolozsvar' WHERE substring(nev,1,1)='B'

Az eredmény:

Ha egy táblából egy vagy több sort szeretnénk törülni azt az alábbi paranccsal tudjuk megtenni:

DELETE FROM táblaneve [WHERE kifejezes

Figyelem ! Ha a WHERE megjegyzés hiányzik az összes bejegyzést kitörli a táblából.

Feladat: Vegyük az elöbbi teszt2 táblát. Törüljük a táblából mindazokat akiknek szülövárossa 'Marosvasarhely'.

Megoldás:

DELETE FROM teszt2 WHERE szul_varos= 'Marosvasarhely'

Az eredmény:

B.     A tábla nevének és felépítésének a módosítása.

Ha egy táblát át akarunk nevezni :

RENAME TABLE réginév TO újnév

Ha ki akarjuk törülni egy tábla egyik oszlopát, akkor is ha vannak benne adatok:

ALTER TABLE táblaneve DROP COLUMN oszlopneve

Ha egy ujabb oszlopot akarunk hozzáadni a táblához:

ALTER TABLE táblaneve ADD oszlopneve tipus

Az ujoszlop tipusa az lesz amit megadunk a parancs végén az oszlop neve után.

3.5.9 Statisztikai függvények

Az összes számítás amit eddig végeztünk a tábla egyetlen sorának mezöire vonatkoztak. Szükségünk van olyan függvényekre, amelyek segítségével egy oszlop adataiból számíthatunk ki bizonyos információkat. Ezek a függvények a statisztikai függvények.

Feladat: Vegyük a következö tanulok táblát:

1. A tanulók száma ?:

SELECT COUNT(*) AS tanulok_szama FROM tanulok

2. Az informatika jegyek számtani középarányosa?:

SELECT AVG(info) AS info_altalanos FROM tanulok

3. Hány tanulónak van 10-e angolból?

SELECT COUNT(angol) AS tizes_angol FROM tanulok WHERE angol=10

4. Hány tanulónak van 8-as fölötti általánossa?

SELECT COUNT(nev) AS nyolcas_folottiek FROM tanulok WHERE (matek+angol+info)/3>8;

3.5.10       Lekérdezések

Minden olyan eredmény amit egy táblából a SELECT parancsot használva kapunk meg lekérdezés. Ilyen értelemben a szürés is tulajdonképpen a lekérdezések sajátos esete. Általánosan egy lekérdezés eredménye lehet

A.           Egyetlen sor egy vagy több oszloppal.

B.           Több sor egy vagy több oszloppal(egy tábla).

Lehetöség van arra, hogy egy adott lekérdezés eredményét felhasználva egy újabb lekérdezést fogalmazzunk meg. Ebben az esetben az elsö lekérdezés alárendelt lekérdezés. Az ö eredményét felhasználjuk.

Az alárendelt lekérdezés egyetlen sort térít vissza:

Feladatok: Vegyük a következö tanulok táblát:

1. Keressük azokat a tanulókat, akiknek a legnagyobb jegyük van matekböl. Elöször meg kell találjuk melyik a legnagyobb matek jegy, ezt a MAX függvény segítségével tudjuk megkapni.  Ez az alárendelt lekérdezés-egyetlen értéket számol ki a legnagyobb matek jegyet. Utánna egy ujabb lekérdezéssel megkeressük azokat akiknek ez a jegye van matekböl.

SELECT nev, matek FROM tanulok WHERE matek=(SELECT MAX(matek) FROM tanulok)

Az eredmény:

Ilyen esetben elöször mindig az alárendelt lekérdezés hajtódik végre.

2. Keressük azokat a tanulókat, akiknek a legnagyobb a matek és info tantárgyakból számított általánosuk. Ugyanúgy járunk el mint az elöbb. Elöször megkeressük mekkora a legnagyobb matek-info általános, azután kiírjuk azokat akiknek ekkora az általánosuk a két tantárgyból.

SELECT nev,(matek+info)/2 as altalanos FROM tanulok WHERE (matek+info)/2=(SELECT MAX((matek+info)/2) FROM tanulok)

3. Keressük azokat a tanulókat, akiknek az általánosuk a három tantárgyból nagyobb mint az általánosok átlaga a táblában. Ezeket a tanulókat az általánosuk szerinti csökkenö sorrendbe szeretnénk kiírni.

SELECT nev,(matek+angol+info)/3 as altalanos FROM tanulok WHERE (matek+angol+info)/3>(SELECT AVG((matek+angol+info)/3) FROM tanulok) ORDER BY altalanos DESC

Az eredmény:

4. Keressük azokat a tanulókat, akiknek ugyanakkora a matek jegyük mint 'Peter Maria'-nak. Ebben az esetben az alárendelt lekérdezés a 'Peter Maria' matek jegyét téríti és fö lekérdezés keresi azokat akiknek a matek jegye megegyezik ezzel a jeggyel.

SELECT nev FROM tanulok WHERE matek=(SELECT matek FROM tanulok WHERE nev='Peter Maria')

Az eredmény:

Az alárendelt lekérdezés egy táblát térít vissza(több sort):

Ebben az esetben az alárendelt lekérdezés által visszatérített táblának nevet kell adni. Ez lehetséges ha használjuk az AS megjegyzést. A fö lekérdezés használja ennek a táblának az oszlopait, ez ugy lehetséges, hogy tábla_neve.oszlop_neve.

Feladat Vegyük a következö tanulók táblát:

5. Mennyi az átlaguk matekböl és infoból, azoknak a tanulóknak, akiknek vagy a matek vagy az info jegyük legalább 8. Az  alárendelt lekérdezés egy olyan táblát hoz létre, amely tartalmazza a matek és info jegyeit azoknak a tanulóknak, akiknek legalább az egyik a kettö közül nagyobb vagy egyenlö 8-al. Ezt a táblát seged-nek nevezzük. A fölekérdezés a matek és info átlagokat számolja ki ebböl a táblából.

SELECT AVG(seged.matek) AS matek_atlag ,AVG(seged.info) AS info_atlag FROM (SELECT matek,info FROM tanulok WHERE matek>=8 OR info>=8) AS seged;

Az eredmény:

3.5.11 Tábla adatainak csoportosítása

Egy tábla adatait csoportosíthatjuk egy adott oszlop értékei szerint. Ha vesszük a következö táblát:

és a dátum szerint csoportosítjuk akkor a következö csoportokat kapjuk:

A statisztikai függvények segítségével mindenik csoportról megkaphatunk különbözö információkat. Egy tábla adatainak a csoportosítása a GROUP BY oszlop_neve megjegyzéssel történik, amit a SELECT parancsba a tábla neve után kell tenni.

Feladatok:

1. Hány eladás történt naponta? (dátum szerint csoportositjuk)

SELECT datum,COUNT(*) AS eladasok_szama FROM tabla GROUP BY datum;

Az eredmény:

2. Mennyi a napi eladás összege?

SELECT datum,SUM(ertek) AS napi_osszeg FROM tabla GROUP BY datum;

Az eredmény:

3. Mikor volt a legnagyob összegre eladás és mekkora összegre?

Készitünk egy segéd táblát, amely az elöbbihez hasonlóan csoportosítja dátum szeint az eredeti táblát és napi összeget számol. Ezt a táblát seged-nek neveztük el.

CREATE TABLE seged AS SELECT datum,SUM(ertek) AS osszeg FROM tabla GROUP BY datum ;

A kapott táblára készítünk egy lekérdezést. Az alárendelt lekérdezés megadja a legnagyobb összeget, és utánna a fölekérdezés megmondja, hogy melyik a megfelelö dátum.

SELECT datum, osszeg FROM seged WHERE osszeg=(SELECT MAX(osszeg) from seged) ;

Az eredmény:

3.5.12             Táblák összekapcsolása -JOIN.

Az adatbázis kezelésben fontos szerepet játszik a táblák összekapcsolása. Az összekapcsolásban egy vagy több tábla vehet részt, olyan táblák, amelyek között kapcsolat van. Leggyakrabban a kapcsolatot a két (vagy több) tábla egy-egy oszlopában levö értékek képezik. A használt táblák kapnak egy aliast(egy új nevet) , ugy, hogy: ...FROM tabla_neve AS alias , ezeket az aliasokat használjuk utánna a SELECT-ben ha hívatkozni akarunk egy bizonyos tábla mezöire. A hivatkozás úgy történik, hogy : alias.mezö_neve.

Az alias használatának elönyei:

• Lerövidíthetöek a táblanevek, pl A, B, ...
• Abban az esetben ha egy táblát önmagával akarjuk összekötni, egyetlen tábla kaphat több különbözö aliast, és úgy fog viselkedni mintha különbözö táblákkal dolgoznánk.

1. A táblák összekapcsolásának az elsö formája, a SELECT paranccsal történik, ahol a FROM után felsoroljuk az összekapcsolásban résztvevö táblákat és magát a kapcsolatot a WHERE ágon írjuk le.

Feladat: Vegyük a termékek és az adatok táblákat ahol a termékek táblának két oszlopa van a kód és a mennyiség, míg az adatok táblának három oszlopa van a kód a név és a termelö. Természetesen a két tábla közötti kapcsolatot a kód oszlop adja. Vagyis ha megadjuk egy termék kódját a termékek táblából az illetö termék mennyisége mellett elérhetö a termék neve és a termelö az adatok táblából.

Vegyük tehát a két táblát:

termekek

adatok

Mindenik termékre, amelyröl vannak adatok az adtok táblában írjuk ki a nevét és a mennyiségét.

SELECT B.nev, A.mennyiseg FROM termekek AS A, adatok AS B WHERE A.kod=B.kod;

Az eredmény:

2. INNER JOIN típusú kapcsolat (belsö kereszt). Ugyanúgy mint a fenti SELECT azokat a bejegyzéseket érjük el mindkét táblából, amelyekre teljesül az ON ágon megadott feltétel. A SELECT parancshoz a FROM után ezt írjuk hozzá:
• .tabla₁ AS alias₁  INNER JOIN tabla₂ AS alias₂ ON oszekotesi feltetel

Feladat:. Vegyük az elöbbi példát:

SELECT B.nev, A.mennyiseg FROM termekek AS A INNER JOIN adatok AS B ON A.kod=B.kod;

Ebben az esetben az eredmény ugyanaz lesz mint fennebb.

3. LEFT OUTER típusú kapcsolat, (külsö bal oldali). Ebben az esetben az egyes tábla minden adata kiíródik de a kettes táblából csak azok az adatok amelyek teljesítik a feltételt, amit az ON után adunk meg, a többinél a NULL érték íródik ki. A SELECT parancshoz a FROM után ezt írjuk hozzá:
• .tabla₁ AS alias₁  LEFT OUTER JOIN tabla₂ AS alias2 ON oszekotesi feltetel

Feladat.: A fenti példa esetén:

SELECT B.nev, A.mennyiseg FROM termekek AS A LEFT OUTER JOIN adatok AS B ON A.kod=B.kod

Az eredmény:

4. RIGHT OUTER típusú kapcsolat, (külsö jobb oldali). Ebben az esetben a kettes tábla minden adata kiíródik de az egyes táblából csak azok az adatok amelyek teljesítik a feltételt, amit az ON után adunk meg, a többinél a NULL érték íródik ki. A SELECT parancshoz a FROM után ezt írjuk hozzá:
• .tabla₁ AS alias₁ RIGHT OUTER JOIN tabla₂ AS alias2 ON oszekotesi feltetel

Feladat.: A fenti példa esetén:

SELECT B.nev, A.mennyiseg FROM termekek AS A RIGHT OUTER JOIN adatok AS B ON A.kod=B.kod

Az eredmény:

Feladat: Ha ki akarjuk írni a termékek tábla azon adatait, amik nincsenek benne az adatok táblában:

SELECT kod FROM termek WHERE kod NOT IN (SELECT kod FROM adatok)

és forditva: az adatok táblából ami nincs benne a termékek táblában:

SELECT kod FROM adatok WHERE kod NOT IN (SELECT kod FROM termek) ;