Transcript 6 Az SQL nyelv eleme..

Az SQL nyelv elemei
1 / 36
Az SQL nyelv elemei
Gáspár Bencéné
Dr. Vér Katalin
nyomán
Barna Róbert
KE GTK
Informatika Tanszék
Az SQL nyelv elemei
2 / 36
Mi is az az SQL
Az SQL, azaz Structured Query Language (strukturált
lekérdezőnyelv) relációs adatbázis-kezelők lekérdezési
nyelve.
Angol nyelvterületen 'eszkjuel' a kiejtése. A hagyományokhoz
való hűség jegyében sokan 'szíkvel'-nek ejtik, ugyanis
korábban Structured English Query Language (SEQUEL)
volt az elnevezés, és ezt rövidítették le.
Az SQL nyelv elemei
3 / 36
Az SQL története
Alapjait az IBM-nél fektették le, még az 1970-es években
(SEQUEL). Elvi alapot a relációs adatmodell szolgáltatott,
amit Edgar F. Codd 12 szabállyal írt le.
Az IBM, az Oracle és más gyártók is érdekeltek voltak egy
szabványos lekérdező nyelv kifejlesztésében, amivel a
relációs adatbázisok „programozhatók” lehettek. Az iparági
összefogással létrejött ANSI NCITS (National Committee
on Information Technology Standards) H2 csoport lerakta az
SQL alapjait.
Az SQL nyelv elemei
4 / 36
Az SQL története
• A szabványt az ANSI (Amerikai Nemzeti Szabványügyi
Intézet – American National Standards Institute) 1986-ban,
az ISO (International Organization for Standardization)
1987-ben jegyezte be. Az első változatot SQL86 néven
szokták emlegetni.
• Az SQL-t folyamatosan továbbfejlesztették, hat jelentős
kiadást különböztetünk meg: SQL86, SQL89, SQL92,
SQL99, (v. más néven: SQL3) SQL:2006, SQL:2008
Az SQL nyelv elemei
5 / 36
Az SQL tulajdonágai
•Az SQL egy szabványosított lekérdező nyelv, melyet több
relációs adatbáziskezelő ismer, különböző operációs rendszeri
környezetben.
•Az SQL nem algoritmikus nyelv, nem tartalmaz algoritmus
szerkezeteket (elágazás, ciklus stb.).
Az SQL nyelv elemei
6 / 36
Az SQL tulajdonágai
Az SQL halmaz orientált nyelv, mely a relációkon dolgozik.
A halmaz orientáltság azt jelenti, hogy nem kell definiálni a
művelet végrehajtásának lépéseit, hanem a feladat nem eljárás
szerű megfogalmazását kell megadni, melyek a reláció vagy
relációk kiválasztott sorain hajtódnak végre.
Az SQL nyelv elemei
7 / 36
Az SQL felhasználási lehetőségei
Az SQL nyelv két felhasználási lehetősége:
• önálló SQL, vagy 4. generációs eszközbe építve
• beágyazott SQL
Az SQL nyelv önálló felhasználása esetén csak a nyelv
utasításai állnak rendelkezésre. Ilyen eszközök a jelentés
készítő, az űrlap készítő vagy menü készítő lehet.
A beágyazott SQL esetén egy harmadik generációs algoritmikus
nyelvbe (C, PL/SQL, Pascal FORTRAN, PHP stb.) ágyazva
alkalmazzuk az SQL nyelv elemeit.
Az SQL nyelv elemei
8 / 36
Az SQL elemei
Az SQL a következő elemekre osztható:
• Adat definiciós nyelv (Data Definition Language – DDL),
• Adatmanipulációs nyelv (Data Manipulation Language, DML),
• Lekérdező nyelv (Data Query Language – DQL) ,
• Adatvezérlő nyelv (Data Control Language, DCL).
Az SQL utasítások kulcsszavakból (SQL names, keywords),
azonosítókból, műveleti jelekből, literálokból (számszerű,
dátumjellegű, szöveges konstansok) állnak. Minden utasítást
pontosvesszővel kell lezárni.
Az SQL nyelv elemei
9 / 36
Adattípusok a DBASE IV-ben
A dBase nyelv gyakorlatilag az adatbázisokkal egyidõs, egyike a legelsõ adatbáziskezelõ nyelveknek. Mint ilyen, nem is
tekinthetõ igazán programozási nyelvnek, pusztán az adatbázisokon gyakran végzett műveletek leírására és fájlban való
rögzítésére szolgáló eszköznek. Ez volt az első széleskörben elterjedt adatbázis kezelő program. Az alap ötlet Wayne
Ratliff-tól származik aki 1978-ban assembly-ben írt egy adatbázis programot (még CP\M alá) hogy segítse az irodai
futball-házibajnokságot.
dBase II was the first widely used database management system (DBMS) for microcomputers. (1981–1983)
dBASE III was the first release written in the C programming language to make it easier to support and port to other
platforms. (1983–1985)
dBASE IV The new version was going to be more powerful, faster, easier to create databases with, improved indexes,
networkable, support SQL internally as well as interact with SQL Server, and would include a compiler. (1988–1990)
A dBase IV öt alapmezőtípust támogat:
• Numerikus (N)
• Szöveges (C)
• Dátum (D)
• Logikai (L)
• Memo (M)
Az SQL nyelv elemei
10 / 36
Adattípusok a DBASE IV-ben
Max 254 hosszú tetszőleges szöveges adat karakterenként
Karakteres (C): egy byte-on ASCII kódban helyigény szöveg hossza + 2
byte.
Előjeles számok ábrázolására alkalmas. Az egyetlen,
Numerikus (N):
matamatikai műveletek végrehajtására alkalmas típus.
8 karakter hosszú (hónap/nap/év). Értelmezettek rajta
bizonyos alapműveletek pl. egészszám hozzáadása ill
Dátum (D):
kivonása egy dátumból, vagy dátumok összegének,
különbségének meghatározása)
Logikai (L):
Tetszőleges kétértékű adat tárolására. Mérete 1 byte.
Tetszõleges karakteres szöveges adat tárolására alkalmas. A
felső határát a használt szerkesztő határozza meg. Az
Memo (M):
adatbázisban mindig 10 karakternyi helyet foglal el. A
tartalom maga külön .dbt kiterjesztésű fájlban tárolódik.
Az SQL nyelv elemei
11 / 36
SQL adattípusok MS Acces 2003
Tárolási
Leírás
méret
BINARY Karakteren- Bármilyen adat tárolására alkalmas. Az adatokon
ként 1 bájt semmilyen átalakítás nem történik (például
szöveggé alakítás). Az adatok kimenetként később
is a bináris mezőbe való beíráskor megadott
formában jelennek meg.
BIT
1 bájt
Yes és No értékeket, valamint mindig két
lehetséges érték egyikét tartalmazó mezők.
MONEY 8 bájt
Rögzített tizedes jegyű számok a következő
értékhatárok között:
– 922 337 203 685 477,5808 és
+ 922 337 203 685 477,5807
Adattípus
Az SQL nyelv elemei
12 / 36
SQL adattípusok MS Acces 2003
Adattípus
REAL
FLOAT
Tárolási
Leírás
méret
4 bájt
Egyszeres pontosságú lebegőpontos szám a következő
határok közt:
– 3,402823E38 és
– 1,401298E-45 között negatív értékek esetén,
+ 1,401298E-45 és
+ 3,402823E38 között pozitív értékek esetén, és 0.
8 bájt
Dupla pontosságú lebegőpontos szám a következő
határok között:
– 1,79769313486232E308 és
– 4,94065645841247E-324 negatív értékek esetén,
+ 4,94065645841247E-324 és
+ 1,79769313486232E308 között pozitív értékek
esetén, és 0.
Az SQL nyelv elemei
13 / 36
SQL adattípusok MS Acces 2003
Tárolási
Leírás
méret
TINYINT
1 bájt
0 és 255 közötti egész szám.
SMALLINT 2 bájt
Kisebb egész szám – 32 768 és 32 767 között.
INTEGER
4 bájt
Nagyobb egész szám – 2 147 483 648 és
+ 2 147 483 647 között.
DECIMAL 17 bájt
Pontos numerikus adattípus, amelynek értékei
-1028-1 és + 1028-1 között lehetnek. Megadhatja
mind a pontosságot (a szám hány jegyet
tartalmaz: 1-28), mind pedig a tizedesjegypontosságot (hány tizedes jegyet tartalmaz: 0 megadott pontosság). Az alapértelmezés szerinti
pontosság: 18, a tizedesjegy-pontosság pedig
nulla.
Adattípus
Az SQL nyelv elemei
14 / 36
SQL adattípusok MS Acces 2003
Tárolási
Leírás
méret
DATETIME
8 bájt
A 100. és 9999. év közé eső dátum- vagy
(Lásd: DOUBLE)
időpontérték.
UNIQUE128 bit
Egyedi azonosítószám távoli
IDENTIFIER
eljáráshívásokhoz.
TEXT
2 bájt/
Nulla és 2,14 GB közötti terjedelmű.
karakter
IMAGE
Szükség
Nulla és 2,14 GB közötti terjedelmű. OLE
szerinti
objektumok esetén használatos.
CHARACTER
2 bájt/
Nulla és 255 karakter közötti terjedelmű.
karakter
Adattípus
Az SQL nyelv elemei
15 / 36
A példák olvasása
A parancsok ismertetésénél
• NAGY betűvel az SQL parancsokon belüli fix szöveg,
• kisbetűvel a felhasználó által megadható részek,
• [ ] szögletes zárójelbe a parancsok elhagyható részei,
• ... (három pont) az előző rész ismételhetőségére utal,
• | (függőleges vonal) jelet az egymást kizáró paraméterek
közé, ezek közül csak egy adható meg.
Az SQL nyelv elemei
16 / 36
Az adat definíciós nyelv
Segítségével hozhatjuk létre illetve szüntethetjük meg a
relációkat, az indexeket illetve a nézet táblázatokat.
A nézet táblázat az adatbázisban fizikailag nem létező
relációs műveletek (szelekció, projekció, összekapcsolás,
halmazműveletek) segítségével létrehozott táblázat, mely a
relációkhoz hasonlóan kezelhető
Az SQL nyelv elemei
17 / 36
Adatdefiniáló utasítások
A relációk létrehozása
CREATE TABLE reláció_név
(attribútum_név adattípus [(szélesség)] [NOT NULL].
(attribútum_név adattípus [(szélesség)] [NOT NULL].
... );
Névadási szabályok:
• a névben csak az angol ABC betűi, a számjegyek és az _, #, $ karakterek
szerepelhetnek ???
• a névnek betűvel kell kezdődnie
• a neveknek hatáskörükön belül egyedinek kell lennie (nem lehet egy
adatbázisban két azonos nevű reláció, egy relációban két azonos nevű
attribútum, stb.)
Az SQL nyelv elemei
18 / 36
Adatdefiniáló utasítások
Táblák módosítása
ALTER TABLE reláció_név
ADD attribútum_név adattípus [(szélesség)];
Teljes táblák törlése
DROP TABLE reláció_név;
Nézettáblák létrehozása, módosítása, törlése
• létező reláción vagy relációkon végrehajtott művelet eredményét tartalmazó
olyan új táblázat, amely mögött a valóságban nem áll megfelelő táblázat
CREATE VIEW nézettábla_név [alias_név, alias_név ...]
AS lekérdezés;
Az SQL nyelv elemei
19 / 36
Az adatmanipulációs nyelv
Az SQL adatmanipulációs része biztosítja a relációk feltöltését, az
attribútumok módosítását és a sorok törlését.
Az SQL nyelv elemei
20 / 36
Az adatmanipulációs utasítások
Új sorok beszúrása
INSERT INTO táblanév [(mező_név_1, mező_név_2, ...)]
VALUES (érték, érték, ...);
A mezők tartalmának módosítása
UPDATE reláció_név
SET attribútum_név = érték, attribútum_név = érték,
...
[WHERE feltétel];
Sorok törlése
DELETE FROM reláció_név
[WHERE feltétel];
Az SQL nyelv elemei
21 / 36
A lekérdező nyelv
• A lekérdező nyelv egyetlen utasításból (SELECT) áll,
mely számos alparancsot tartalmazhat, és a lekérdező
utasítások többszörös mélységben egymásba ágyazhatók.
Figyelem!
A szelekció művelete és a SELECT utasítás
csak nevében hasonló, egymásnak nem
felelnek meg.
Az SQL nyelv elemei
22 / 36
A lekérdező utasítás
SELECT [DISTINCT] oszloplista
FROM táblanév_lista
[WHERE feltétel]
[GROUP BY oszloplista]
[HAVING feltétel]
[ORDER BY oszloplista];
projekció
Descartes-szorzat
szelekció
csoportosítás
csoport-feltétel
rendezés
A projekció során egy tábla oszlopai közül csak bizonyosakat
őrzünk meg az eredmény táblában.
A szelekció művelete során egy relációból csak az adott feltételt
kielégítő sorokat őrizzük meg az eredmény relációban
Az SQL nyelv elemei
23 / 36
Descartes (direkt) szorzat
A és B direkt szorzatának eredményeképpen olyan számpárokat
kapunk, amelyek-nek első tagja A-nak eleme, második tagja B-nek
eleme.
A
B
1
2
3
7
8
A×B
{1,7}
{1,8}
{2,7}
{2,8}
{3,7}
{3,8}
Az SQL nyelv elemei
24 / 36
Descartes (direkt) szorzat
A reláció szó „eredetileg” kapcsolatot jelent, és a matematikában a
reláció alatt több alaphalmaz Descartes szorzatának egy
részhalmazát értik.
Minden tábla a hozzá tartozó rekordtípus elméletileg lehetséges
előfordulásainak egy részhalmazát tartalmazza, ahol minden
rekordelőfordulás a mezőkhöz tartozó domain halmazokból
tartalmaz egy-egy elemet az egyes mezőiben, így egy
rekordelőfordulás nem más, mint a domain halmazok Descartes
szorzatának egy eleme.
Így maga a tábla, vagy reláció nem más, mint a domain halmazok
Descartes szorzatának egy részhalmaza, tehát a tábla, a reláció
matematikai értelemben is megfelel a reláció definíciójának.
A domain alatt a mező által felvehető értékek halmazát értjük.
Az SQL nyelv elemei
25 / 36
Descartes (direkt) szorzat
Az SQL nyelv elemei
26 / 36
A lekérdező utasítás
A SELECT utasítás alapváltozata:
SELECT [DISTINCT] oszloplista
FROM táblanévlista
[WHERE feltétel];
• A felsorolt táblák Descartes-szorzatából szelektáljuk a
feltételnek eleget tévő sorokat, majd ezekből projekcióval
választjuk ki az eredménytábla oszlopait.
• A DISTINCT opciót akkor kell kiírni, ha az azonos sorokból
csak egyet kívánunk megtartani.
• Ha az oszloplista helyére * karaktert írunk, ez valamennyi
oszlop felsorolásával egyenértékű.
• A SELECT legegyszerűbb változatával adattábla listázását
érhetjük el. (SELECT * FROM Diak;)
Az SQL nyelv elemei
27 / 36
A lekérdező utasítás
SELECT [DISTINCT] oszloplista
FROM táblanévlista
[WHERE feltétel];
• A WHERE záradékban feltételt kell megadni, és csak a
feltételnek megfelelő sorokat válogatja ki.
• A kifejezések elemei lehetnek oszlopok nevei, operátorok,
mezőkre vonatkozó függvények.
• Csak azonos típusú kifejezéseket hasonlíthatunk össze (pl.
numerikust numerikussal stb.).
• Ha több feltételt is megadunk akkor meg kell adni a
feltételek közötti viszonyt is az AND, az OR és a NOT
szavakkal.
Az SQL nyelv elemei
28 / 36
A lekérdező utasítás
SELECT [DISTINCT] oszloplista
FROM táblanévlista
[WHERE feltétel];
• Az összehasonlításra használható operátorok:
<, >, <=, >=, =, valamint nem egyenlő: != vagy <>.
• Egy mező értéke, két határérték között van-e?
mezőnév BETWEEN érték1 And érték2
• Adott értékek között van-e:
mezőnév IN (érték1, érték2, …)
• Megfelel-e a mintának:
mezőnév LIKE minta
Mintában használható helyettesítő karakterek:
*, ?, #, [karakterlista], [!karakterlista]
Az SQL nyelv elemei
29 / 36
Adatvezérlő nyelv
Az adatvezérlő utasításokkal az adatvédelmi és a
tranzakció-kezelő műveletek hajthatóak végre.
Az SQL nyelv elemei
30 / 36
Adatvezérlő utasítások
Védelemmel, jogosultságokkal kapcsolatos utasítások
Adott felhasználónak az adatbázis valamelyik objektumára
valamilyen jog adása:
GRANT jog ON objektum TO {felhasználó|PUBLIC}
[WITH GRANT OPTION];
• Jogok: SELECT (olvasási), UPDATE (módosítási),
INSERT (beszúrási), DELETE (törlési.
• Objektum pl.: Tábla, lekérdezés, …
• Felhasználói név vagy PUBLIC = mindenki
• A felhasználó a kapott jogot továbbadhatja másoknak: WITH
GRANT OPTION
Az SQL nyelv elemei
31 / 36
Adatvezérlő utasítások
Védelemmel, jogosultságokkal kapcsolatos utasítások
• Jog visszavonása felhasználótól
REVOKE jog ON objektum FROM felhasználó;
Az SQL nyelv elemei
32 / 36
Adatvezérlő utasítások
Tranzakciókezelő utasítások
• Műveletek eredményének véglegesítése
COMMIT;
• Műveletek eredményének visszagörgetése
ROLLBACK;
Az SQL nyelv elemei
33 / 36
Házi feladat 2.
1. A megismert SQL utasítás mindegyikére (kivéve az
adatvezérlő utasítások) 1-1 konkrét példát írni az előzőleg
elkészített házi feladat adatbázisába!
2. Az Adat definiciós nyelv és az
Adatmanipulációs nyelv utasításainak hozzanak
létre egy SQL utasítások nevű táblát. Ebben
hozzanak létre egy Utasítás nevű, Feljegyzés
típusú mezőt. Ebbe írják bele a két utasítást!
3. A Lekérdező nyelv utasítását a Lekérdezések,
SQL nézetében lehet megadni.
4. A kész feladatot e-mail mellékleteként kérem elküldeni a
[email protected] címre .
Az SQL nyelv elemei
34 / 36
Leadási határidő
A házi feladat beérkezésének végső határideje:
2012. november 30. 00.00
Az SQL nyelv elemei
35 / 36
Köszönöm a figyelmet!
Az SQL nyelv elemei
36 / 36