Sapientia - Erdélyi Magyar TudományEgyetem (EMTE) Csíkszereda
3. Előadás Az SQL adatbázisnyelv (Tartalom) •Az SQL története •Az SQL felépítése
• Adattípúsok •SQL utasítások
1
Sapientia - Erdélyi Magyar TudományEgyetem (EMTE) Csíkszereda Az SQL története • SEQUEL (Structured English QUEry Language): az IBM kísérleti relációs adatbázis-kezelő rendszerének (System R) relációs nyelve. • SQL (Structured Query Language): az IBM kereskedelmi adatbázis-kezelő rendszereinek (DB2, SQL/DS) relációs nyelve • SQL1: ANSI 1986 • SQL2: ISO 1992 • SQL3: (rekurzió, triggerek, objektum-relációs modell) 2
Sapientia - Erdélyi Magyar TudományEgyetem (EMTE) Csíkszereda
Az SQL felépítése(Szintaxis)
Kisbetű és nagybetű a nyelv alapszavaiban egyenértékű. •Utasítások sorfolytonosan írhatók, lezárás pontosvesszővel. •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ó). •Alias név: név AS másodnév (egyes implementációkban AS elhagyható). •Szövegkonstans: 'szöveg' 3
Sapientia - Erdélyi Magyar TudományEgyetem (EMTE) Csíkszereda Az SQL felépítése(Szintaxis)2
•Dátum: DATE '1968-05-12'. Egyes rendszerek az SQL szabványtól eltérő konvenciót alkalmaznak, például 13NOV-94 (Oracle), 02/15/1994 (dBase). •Idő: TIME '15:31:02.5' (óra, perc, másodperc). •Stringek konkatenációja: + (xBase), || (Oracle). •Relációjelek: =, <=, >=, !=, <> •Logikai műveletek: AND, OR, NOT. Egy logikai kifejezés értéke ISMERETLEN (UNKNOWN), ha benne NULL érték szerepel 4
Sapientia - Erdélyi Magyar TudományEgyetem (EMTE) Csíkszereda Az adattípusok (rendszerenként eltérők lehetnek): CHAR(n) VARCHAR(n) INTEGER REAL DECIMAL(n[,d]) DATE TIME
n hosszúságú karaktersorozat legfeljebb n hosszúságú karaktersorozat egész szám (röviden INT) valós (lebegőpontos) szám, más néven FLOAT n jegyű decimális szám, ebből d tizedesjegy dátum idő
5
Adattípusok a MySQL-ben Numerikus típusok BIT[(M)] TINYINT[(M)] [UNSIGNED] [ZEROFILL] BOOL, BOOLEAN SMALLINT[(M)] [UNSIGNED] [ZEROFILL] MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] INT[(M)] [UNSIGNED] [ZEROFILL] INTEGER[(M)] [UNSIGNED] [ZEROFILL] BIGINT[(M)] [UNSIGNED] [ZEROFILL] FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL] REAL[(M,D)] [UNSIGNED] [ZEROFILL] FLOAT(p) [UNSIGNED] [ZEROFILL] DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL] NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL], FIXED[(M[,D])] [UNSIGNED] [ZEROFILL] 6
Dátum és idő reprezentációja
DATE DATETIME
TIMESTAMP TIME
YEAR[(2|4)]
“Zero” Value '0000-00-00 00:00:00' DATE '0000-00-00' TIMESTAMP '0000-00-00 00:00:00' TIME '00:00:00' YEAR 0000 Data Type DATETIME
7
String tipusok CHARACTER SET CHARACTER SET binary ASCII UNICODE BINARY [NATIONAL] CHAR[(M)] [NATIONAL] VARCHAR(M) BINARY(M) VARBINARY(M) TINYBLOB TINYTEXT [CHARACTER SET charset_name] BLOB[(M)] TEXT[(M)] [CHARACTER SET charset_name] MEDIUMBLOB MEDIUMTEXT [CHARACTER SET charset_name] LONGBLOB LONGTEXT [CHARACTER SET charset_name] ENUM('value1','value2',...) [CHARACTER SET charset_name] SET('value1','value2',...) [CHARACTER SET charset_name]
8
Ekvivalens adattipusok más szállítók tipusaival Other Vendor Type
MySQL Type
BOOL
TINYINT
BOOLEAN
TINYINT
CHARACTER VARYING(M)
VARCHAR(M)
FIXED
DECIMAL
FLOAT4
FLOAT
FLOAT8
DOUBLE
INT1
TINYINT
INT2
SMALLINT
INT3
MEDIUMINT
INT4
INT
INT8
BIGINT
LONG VARBINARY
MEDIUMBLOB
LONG VARCHAR
MEDIUMTEXT
LONG
MEDIUMTEXT
MIDDLEINT
MEDIUMINT
NUMERIC
DECIMAL
9
Sapientia - Erdélyi Magyar TudományEgyetem (EMTE) Csíkszereda SQL utasítások
Az SQL utasításait két fő csoportba szokták sorolni: - DDL (= Data Definition Language): adatstuktúra definiáló utasítások. - DML (= Data Manipulation Language): adatokon műveletet végző utasítások. Jelen anyagban - az RDBMS fő feladatai alapján - az alábbi csoportokban tárgyaljuk az SQL utasításokat: - adatbázisséma definiálása (DDL) - adatok aktualizálása (DML) - lekérdezési lehetőségek (DML) 10
Adatbázissémák definiálása (DDL) Adatbázisséma létrehozására SCHEMA utasítás szolgál.
a
CREATE
DATABASE/
CREATE {DATABASE/SCHEMA} [IF NOT EXISTS] db_name [create_specification] ... create_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name Adatbázisséma átnevezése RENAME {DATABASE | SCHEMA} db_name TO new_db_name; 11
Adatbázisséma törlése (eldobása) DROP {DATABASE | SCHEMA} [IF EXISTS] db_name; Adatbázisséma módosítása ALTER {DATABASE | SCHEMA} [db_name] alter_specification ... ALTER {DATABASE | SCHEMA} db_name UPGRADE DATA DIRECTORY NAME (5.1 előtti feljavítása)
alter_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name Adatbázisséma megnyitása (aktuálissá tétele): USE db_name;
12
Sapientia - Erdélyi Magyar TudományEgyetem (EMTE) Csíkszereda Relációsémák definiálása (DDL) Relációséma létrehozására a CREATE TABLE utasítás szolgál, amely egyben egy üres táblát is létrehoz a sémához. Az attribútumok definiálása mellett a kulcsok és külső kulcsok megadására is lehetőséget nyújt: CREATE TABLE táblanév ( oszlopnév adattípus [feltétel], ... ..., oszlopnév adattípus [feltétel] [, táblaFeltételek] );
13
Sapientia - Erdélyi Magyar TudományEgyetem (EMTE) Csíkszereda Feltételek (egy adott oszlopra vonatkoznak): PRIMARY KEY: elsődleges kulcs UNIQUE: kulcs (egyértékűség) REFERENCES tábla(oszlop) [ON-feltételek]: külső kulcs
Táblafeltételek (az egész táblára vonatkoznak): PRIMARY KEY (oszloplista): elsődleges kulcs UNIQUE (oszloplista): kulcs FOREIGN KEY (oszloplista) REFERENCES tábla(oszloplista) [ON-feltételek]: külső kulcs 14
1. Példa. SZAMLA (szamlaszam, AFA, Datum, kliensKod) KLIENS(klienskod, név, Bank, Bankszamla, adoszam) CREATE TABLE Kliens ( klienskod integer PRIMARY KEY, nev CHAR(20), Bank CHAR(20), Bankszamla CHAR(30), adoszam CHAR(30) ); CREATE TABLE Szamla ( szamlaszam integer PRIMARY KEY, AFA integer, datum Date, Osszeg Decimal(10,2), klienskod integer REFERENCES Kliens(klienskod) );
15
Sapientia - Erdélyi Magyar TudományEgyetem (EMTE) Csíkszereda Relációséma törlése:
DROP TABLE táblanév; Hatására a séma és a hozzá tartozó adattábla törlődik. Relációséma módosítása: ALTER TABLE táblanév [ADD (újelem, ..., újelem)] [MODIFY (módosítás, ..., módosítás)] [DROP (oszlop, ..., oszlop)]; 16
Sapientia - Erdélyi Magyar TudományEgyetem (EMTE) Csíkszereda
-újelem: egy "oszlopnév adattípus [feltétel]", vagy egy "táblafeltétel", mint a CREATE TABLE utasításban. - módosítás: "oszlopnév adattípus [feltétel]". Oszlopok törlését nem minden rendszer engedi meg. Példák: ALTER Table Termek Add (gyartoEV integer); ALTER Table Termek DROP Column gyartoEv; 17
Adattábla aktualizálása (DML) Új sor felvétele táblába INSERT INTO táblanév [(oszloplista)] VALUES (értéklista); Ha oszloplista nem szerepel, akkor valamennyi oszlop értéket kap a CREATE TABLE-ben megadott sorrendben. Egyébként, az oszlopnév-listában nem szereplő mezők NULL értéket kapnak. Példák: INSERT INTO Szamla(AFA, szamlaszam, klienskod) VALUES (0.19, 12345, 6783); INSERT INTO Szamla VALUES (12345, 0.19,’2008-04-21’ , 6783); 18
Sor(ok) módosítása UPDATE táblanév SET oszlop = kifejezés, ..., oszlop = kifejezés [ WHERE feltétel ]; Az értékadás minden olyan soron végrehajtódik, amely eleget tesz a WHERE feltételnek. Ha WHERE feltétel nem szerepel, akkor az értékadás az összes sorra megtörténik.
Példák: UPDATE Kliens SET lakcím = "Szeged, Rózsa u. 5." WHERE klienskod = 6783; UPDATE Kliens SET Bank = “Reiffeisen" WHERE Bank = “Banca Agricola";
19
Sor(ok) törlése
DELETE FROM táblanév [ WHERE feltétel ]; Hatására azok a sorok törlődnek, amelyek eleget tesznek a WHERE feltételnek. Ha a WHERE feltételt elhagyjuk, akkor az összes sor törlődik (de a séma megmarad). Példa: DELETE FROM Kliens WHERE klienskod>200 AND klienskod<400; DELETE FROM Kliens; !!!!! – mindent töröl
20
Sapientia - Erdélyi Magyar TudományEgyetem (EMTE) Csíkszereda
Lekérdezések(SELECT) Egy vagy több adattáblából egy eredménytáblát állít elő A SELECT utasítás általános alakja SELECT [DISTINCT] oszloplista projekció FROM táblanévlista Descartes-szorzat [WHERE feltétel] szelekció [GROUP BY oszloplista] csoportosítás [HAVING feltétel] csoport-feltétel [ORDER BY oszloplista]; rendezés 21
Sapientia - Erdélyi Magyar TudományEgyetem (EMTE) Csíkszereda Egyszerű lekérdezések SQL-ben
Szelekció:f(R) SELECT * FROM R WHERE f; Példa: Film(cim, ev, hossz, szines, studioNev, producerAzon); Szinesz(nev, cim, nem, szuletesnap); SELECT * FROM Film WHERE hossz>100 AND szines; 22
Vetítés(Projekció):
Ai1 , Ai2 ,, Aik
( R)
SELECTAi , Ai , , Ai FROM R; 1
2
k
Példa: SELECT studioNev, cim FROM Film;
Alias (AS) használata - másodnév SELECT cim AS nev, hossz*0,016667 AS hosszOrakban FROM Film WHERE studioNev=‘Disney’; 23
Konstansok használata a SELECT záradékban SELECT cim , hossz*0,016667 AS hosszOrakban FROM Film WHERE studioNev=‘Disney’; Karakterláncok összehasonlítása: s LIKE p , ahol s karakterlánc és p egy minta Helyettesítő karakterek (standard SQL): %, _ Helyettesítő karakterek (Access): *, ? SELECT * FROM Film WHERE cim LIKE ‘csill*’
SELECT * FROM Film WHERE cim LIKE ‘*lága’ 24
SELECT * FROM Film WHERE cim LIKE ‘*or*’
SELECT * FROM Film WHERE cim LIKE ‘Wayne???world’
Speciális karakterek használata a mintában ‘ (egyszeres idézőjel) keressük a következő kombinációt: ‘s SELECT * FROM Film WHERE cim LIKE ‘*’’s*’ idézőjelet
megduplázzuk az egyszeres
s LIKE ‘x%%x%’ ESCAPE ‘x’ Az x% egy % karakternek fog megfelelni. A minta olyan karaktersornak felel meg, amelyik % al kezdődik és %-al végződik 25
Null értékre vonatkozó szabályok: 1. Amikor egy aritmetikai műveletben, mint x vagy +, legalább az egyik tag NULL, akkor az eredmény is NULL 2. Amikor egy NULL értéket hasonlítunk össze bármely más értékkel, beleértve a NULL-t is, egy összehasonlítási operátor segítségével, mint = vagy >, az eredmény ISMERETLEN. Logikai műveletek a háromértékű logikában Szabály: könnyen megjegyezhető, ha az IGAZ=1, HAMIS=0, ISMERETLEN=1/2. 1. Két logikai értékre alkalmazott AND eredménye a két érték minimuma. 2. Két logikai értékre alkalmazott OR eredménye a két érték maximuma. 3. A v logikai érték tagadásának értéke 1-v 26
x
y
x AND y
x OR y
NOT x
IGAZ
IGAZ
IGAZ
IGAZ
HAMIS
IGAZ
ISMERETLEN
ISMERETLEN
IGAZ
HAMIS
IGAZ
HAMIS
HAMIS
IGAZ
HAMIS
ISMERETLEN
IGAZ
ISMERETLEN
IGAZ
ISMERETLEN
ISMERETLEN
ISMERETLEN
ISMERETLEN
ISMERETLEN
ISMERETLEN
ISMERETLEN
HAMIS
HAMIS
ISMERETLEN
ISMERETLEN
HAMIS
IGAZ
HAMIS
IGAZ
IGAZ
HAMIS
ISMERETLEN
HAMIS
ISMERETLEN
IGAZ
HAMIS
HAMIS
HAMIS
HAMIS
IGAZ
27
Sapientia - Erdélyi Magyar TudományEgyetem (EMTE) Csíkszereda SELECT cím FROM Film WHERE cím LIKE ‘Halálos _______’ (7 db. alulhúzás) (Halálos fegyver v. Halálos játszma) Szűrőfeltételek: WHERE
BETWEEN AND WHERE IN (, [,...])
28
Példa: SELECT Név FROM Alkalmazottak WHERE Fizetés BETWEEN 300 AND 500; Legyen az Egyetem nevű adatbázis a következő relációsémákkal:
Szak (SzakKod, SzakNév, Nyelv); Csoport (CsopKod, Evfolyam, SzakKod); Diák(Törzsszám, Név, SzemSzám, Cím, SzületésiDatum, CsopKod, Átlag); SELECT Név FROM Diák WHERE CsopKod IN (’531’, 532’, ’631’);
29
Több relációra vonatkozó lekérdezések Descartes szorzat: R1 x R2 SELECT * FROM R1, R2 Théta-összekapcsolás: R1 ⋈ θ R2 SELECT * FROM R1, R2 WHERE θ; Természetes összekapcsolás: R1 ⋈ R2 = SELECT * FROM R1, R2 WHERE R .A R .A AND R .A R .A AND AND R .A 1
1
2
1
1
2
2
2
1
p
R2 .Ap
30
Példa: SELECT Diak.Név, Csoport.CsopKod, Csoport.Evfolyam, SzakKod FROM Diák, Csoport WHERE Diák.CsopKod = Csoport.CsopKod; SELECT Diak.Név, Csoport.CsopKod, Evfolyam, SzakKod FROM Diák INNER JOIN Csoport ON Diák.CsopKod = Csoport.CsopKod; SELECT Név FROM Diák, Csoport WHERE Diák.CsopKod = Csoport.CsopKod AND Evfolyam = 3; 31
Halmazműveletek (egyesítés, különbség és metszet) : (SELECT Név, UtcaSzám FROM Szállítók WHERE Helység = “Kolozsvár”) UNION (SELECT Név, UtcaSzám FROM Vevők WHERE Helység = “Kolozsvár”);
(SELECT SzemSzám FROM Alkalmazottak) EXCEPT (SELECT SzemSzám FROM Managerek); (SELECT Név FROM Tanárok) INTERSECT (SELECT Név FROM Diákok);
32
Sapientia - Erdélyi Magyar TudományEgyetem (EMTE) Csíkszereda
Ismétlődő sorok (DISTINCT ) A SELECT kulcsszó után a DISTINCT szó segítségével kérhetjük az azonos sorok megszüntetését.
Példa: Az Egyetem adatbázis esetén keressük azon csoportokat, amelyekben vannak diákok, akik átlaga kisebb, mint 7. SELECT DISTINCT CsopKod FROM Diákok WHERE Átlag < 7;
33
Sapientia - Erdélyi Magyar TudományEgyetem (EMTE) Csíkszereda
Összefoglaló kérdések 1. Az SQL szintaxisa 2. Adattípusok az SQL-ben 3. SQL utasítások típusai 4. Relációs sémák definiálása, törlése, módosítása 5. Oszlopra és sorra vonatkozó feltételek 6. Adattáblák aktualizálása 7. Karakterláncok összehasonlítása 8. Szorzat és összekapcsolás az SQL –ben 9. Egyesítés , metszet és különbség a SQL –ben 34
