POSTGRESQL

Olyan adatbáziskezelő rendszer, amely SQL (Structured Query Language, Rendszerezett Lekérdezőnyelv) szabványú felületen elérhető adatbáziskezelést tesz lehetővé.

Rövid Történet

SQL

Rövid Történet

Jelentősége

Az utasítások csoportjai

A nyelv utasításait a következő csoportokra oszthatjuk:

Adatleíró nyelv - DDL - Data Definition Language (create, alter, drop)

Adatmódosító nyelv - DML - Data Manipulation Language (select, insert, delete, update)

Adatelérést vezérlő nyelv - DCL - Data Control Language (grant, revoke)

A nyelvben - a szöveg literálok kivételével - a kis- és nagybetűket nem különböztetjük meg. A parancsok több sorba is átnyúlnak, a sorokra tördelésnek szemantikai jelentősége nincs. Az SQL parancsokat mindig a pontosvessző zárja le.

Lekérdezések

A lekérdezések általános szintaxisa

SELECT <jellemzők>
[FROM <tábla(k)>]
[WHERE <logikai kifejezés(ek)>]
[GROUP BY <csoportosító(k)>]
[HAVING < logikai kifejezés(ek)>]
[ORDER BY <rendezés(ek)>];

A lekérdezés művelete (reláció képzés) eredményül egy újabb táblát állít elő - persze lehet, hogy az eredmény táblának csak 1 oszlopa és 1 sora lesz. Az eredmény tábla a lekérdezés után megjelenik vagy a tábla felhasználható pl. az adatmódosító utasításokban.

A <jellemzők> definiálják az eredmény tábla oszlopait,
<táblák> adják meg a lekérdezésben résztvevő táblák nevét,
a <logikai kifejezés> segítségével "válogathatunk" az eredmény sorai között.
A <csoportosítás> az eredmény tábla sorait rendezi egymás mellé, illetve
a <rendezés> a megjelenő sorok sorrendjét határozza meg.

Nézzük meg, hogy a lekérdezés műveletével hogyan lehet megvalósítani a relációs algebra alapműveleteit.

Vetítés (projection)

A vetítés művelete egy táblából adott oszlopokat válogat ki. A <jellemzők> között kell felsorolni a kívánt oszlopokat. Például:

Az alkalmazottak neve és fizetése:

SELECT alk_nev, fizetes FROM alkalmazott;

Minden oszlop kiválasztása:

SELECT * FORM alkalmazott;

Kizárás (restriction)

A kizárás műveletnél a tábla sorai közül válogatunk. A <logikai kifejezés> igaz értékeinek megfelelő sorok kerülnek be az eredmény táblába.

A 2000 dollárnál többet kereső dolgozók:

SELECT alk_nev, fizetes FROM alkalmazott WHERE fizetes > 2000;

Összekapcsolás (join)

A természetes összekapcsolás műveleténél 2 vagy több tábla soraiból hozunk össze egy-egy új rekordot akkor, ha a kér sor egy-egy mezőjének értéke megegyezik. A SELECT kifejezésben a <táblák> -ban kell megadni az érintett táblák neveit, a WHERE mögötti logikai kifejezés definiálja azokat az oszlopokat, amely értékei szerint történik meg az összekapcsolás.

Az egyes osztályok neve székhelye és a hozzájuk tartozó dolgozók:

SELECT reszleg.reszleg_nev, reszleg.reszleg_cim, alkalmazott.alk_nev
FORM reszleg,alkalmazott
WHERE reszleg.reszleg_kod= alkalmazott.reszleg_kod;

Látható, hogy mivel mindkét felhasznált táblában azonos az összekapcsolás megvalósító oszlop neve, a WHERE -t követő logikai kifejezésben az oszlop neve mellé meg kell adni a tábla nevét is. Hasonló helyzet előfordulhat a SELECT-et követő <jellemzők> között is.

Ha megvizsgáljuk a fenti példában kapott eredményt, láthatjuk, hogy 40-es osztály nem szerepel a listában. Ennek az az oka, hogy az osztálynak nincs egyetlen dolgozója sem, tehát az egyesítésnél nem találtunk az emp táblában egyetlen olyan sort sem, amelyet ehhez az osztályhoz kapcsolhattunk volna Ez lehet kivánatos eredmény, azonban az SQL-ben lehetőség van arra is, hogy ezeket a sorokat is egyesítsük, azaz az egyesítésben az emp táblához hozzáképzeljünk egy üres sort is. Ezt külső egyesítésnek hívjuk. A módosított példa a következőképpen néz ki:

SELECT reszleg.reszleg_nev, reszleg.reszleg_cim, alkalmazott.alk_nev
FORM reszleg,alkalmazott
WHERE reszleg.reszleg_kod= alkalmazott.reszleg_kod (+);

A (+) jel azt jelzi azt a táblát, amelyikhez az egyesítés előtt az üres mezőket tartozó sort hozzá kel venni.

Egyéb nem részletezett műveletek

Az egyes lekérdezések által előállított táblák halmazként is felfoghatók, az SQL nyelv ezen táblák kombinálására tartalmaz szokásos halmazműveleteket is.

A műveleteket két SELECT utasítás közé kell írni. Pl.:

select alk_kod from alkalmazott1
UNION
select alk_kod from alkalmazott2;

Az SQL nyelv alapvető parancsai

SELECT adatok olvasására az adatbázisból
INSERT
UPDATE
DELETE
adatok megváltoztatására az adatbázisban
CREATE
ALTER
DROP
TRUNCATE
az adatbázis szerkezetének megváltoztatására
GRANT
REVOKE
Felhasználói jogosultságok kezelésére

Az SQL nyelv használatának szabályai

Egy SQL parancs bevihető egy vagy több sorban - az egyes klauzulákat célszerű külön sorba írni - a parancsszavakat nem lehet elválasztani

Az SQL parancsoknak nincsen rövidített alakja

Az SQL parancsok bevitelnél nem érzékenyek a kis/nagybetűkre (A példákban mindenhol nagy betűvel fognak szerepelni, hogy megkülönböztessük a nem parancs szövegektől.)

A bevitt SQL parancsok az SQL pufferba kerülnek. A pufferban mindig csak egyetlen SQL utasítás lehet aktív!

Az aktív SQL utasítás többféleképpen futtatható

a./ autok=> SELECT * FROM autok;

b./ autok=> SELECT * FROM autok
   autok-> /

c./ autok=> /

d./ autok=> \g

Lekérdezések

A lekérdezés szintaxisa

SELECT [ALL/DISTINCT] <jellemzők>
[FROM <tábla(k)>]
[WHERE <logikai kifejezés(ek)>]
[GROUP BY <csoportosító(k)>]
[HAVING <logikai kifejezés(ek)>]
[ORDER BY <rendezés(ek)> [ASC | DESC]];

Egyszerű lekérdezés

autok=> SELECT * FROM alkalmazott;

autok=> SELECT reszleg_kod, alk_nev, beosztas
autok-> FROM alkalmazott;

Aritmetikai kifejezések (oszlopok, értékek, operátorok kombinációja)

autok=> SELECT alk_nev, fizetes*12
autok-> FROM alkalmazott;

Alternatív oszlopnevek (alias)

autok=> SELECT alk_nev, fizetes*12 AS eves_fizetes
autok-> FROM alkalmazott;

vagy:

autok=> SELECT alk_nev, fizetes*12 AS "eves fizetes"
autok-> FROM alkalmazott;

Összekapcsolt oszlopok (konkatenálás ||)

autok=> SELECT alk_kod||alk_nev AS dolgozo
autok-> FROM alkalmazott;

Literálok

autok=> SELECT alk_kod||'-'||alk_nev AS dolgozo,
autok-> 'részlege: ',reszleg_kod
autok-> FROM alkalmazott;

Feltételes kifejezések (CASE,COALESCE,NULLIF)

Feltételtől függő értékek (CASE)

autok=> SELECT fizetes,
autok-> CASE WHEN fizetes<1000 THEN 'Elég kevés'
autok-> WHEN fizetes>1000 AND fizetes<5000 THEN 'Nem rosz'
autok-> ELSE 'Ez az igazi' END AS Kategoria
autok-> FROM alkalmazott;

Null értékek kezelése (COALESCE)

autok=> SELECT alk_nev, fizetes*12+premium AS eves_jov
autok-> FROM alkalmazott;

autok=> SELECT alk_nev, fizetes*12+COALESCE(premium,0) AS eves_jov
autok-> FROM alkalmazott;

Null értékek kezelése 2 (NULLIF)

autok=> SELECT fizetes, nullif(fizetes,1000) AS csokkentes
autok-> FROM alkalmazott;

Duplikált sorok kiküszöbölése (DISTINCT)

autok=> SELECT DISTINCT reszleg_kod
autok-> FROM alkalmazott;

autok=> SELECT DISTINCT reszleg_kod, beosztas
autok-> FROM alkalmazott;

Eredmény sorok rendezése (ORDER BY)

autok=> SELECT alk_nev, beosztas, fizetes*12, reszleg_kod
autok-> FROM alkalmazott
autok-> ORDER BY alk_nev;

autok=> SELECT alk_nev, beosztas, belepes
autok-> FROM alkalmazott
autok-> ORDER BY belepes DESC;

Válogatás a sorok között (WHERE)

=,!=,<=,>=,<,>,BETWEEN, IN, IS NULL, LIKE (NOT, AND, OR)

autok=> SELECT *
autok-> FROM alkalmazott
autok-> WHERE alk_nev='SCOTT';

WHERE fizetes<13000
WHERE fizetes BETWEEN 10000 AND 13000
WHERE fizetes IN (10000,13000)
WHERE alk_nev LIKE '%S%'
WHERE premium IS NULL

Összetett feltételek

autok=> SELECT alk_kod, alk_nev, beosztas, fizetes
autok-> FROM alkalmazott
autok-> WHERE fizetes BETWEEN 10000 AND 13000
autok-> AND beosztas = 'ELADO';

illetve:

autok=> SELECT alk_kod, alk_nev, beosztas, fizetes
autok-> FROM alkalmazott
autok-> WHERE fizetes BETWEEN 10000 AND 13000
autok-> OR beosztas = 'ELADO';

Adatelem létrehozás, módosítás

CREATE TABLE autok
(
rendszam VARCHAR(7) NOT NULL,
tipus_nev VARCHAR(15),
auto_csop_nev VARCHAR(6),
vasarlas_datuma DATE,
ar FLOAT8,
futott_km INT8,
ut_szerviz INT8,
allapot VARCHAR(1),
reszleg_kod INT4
);

ALTER TABLE autok ADD (alk_kod int8);

SELECT * INTO TABLE autoextra FROM autok WHERE tipus_nev = 'EXTRA';

DROP TABLE autok;

CREATE INDEX i_autok_rendszam on autok(rendszam);

DROP INDEX i_autok_rendszam;

CREATE VIEW v_auto_opel AS SELECT * FROM autok WHERE tipus_nev='OPEL';

DROP VIEW v_auto_opel;

Adat létrehozás, változtatás

INSERT INTO autok VALUES ('E-012345','FORD ESCORT','EXTRA','19930102','1300000','151003','141410','F',30,1236);

UPDATE autok SET rendszam='ABR-047' WHERE rendszam='E-012345';

DELETE FROM autok WHERE rendszam='ABR-047';

Hasznos PSQL parancsok

\? parancs - Help

\q - Kilépés

\g - Puffer tartalmának végrehajtása

\r - Puffer tartalmának ürítése

\copy - az adatok másolására szolgál

pl:  adat kimásolása egy kimenet állományba
  \copy autok to kimenet
 adat beolvasása egy bemenet állományból
  \copy autok from bemenet

Függvények egymásba ágyazása

Hány 'S' betű van a ALKALMAZOTT tábla sorainak ALK_NEV oszlopában?

autok=> SELECT
autok-> alk_nev,
autok-> LENGTH(alk_nev) AS l1,
autok-> LENGTH(REPLACE(alk_nev, 'S')) AS l2,
autok-> LENGTH(alk_nev)-LENGTH(REPLACE(alk_nev, 'S')) AS s
autok-> FROM alkalmazott