---------------------------------------------- dr. Hajas Csilla, ELTE IK, 2008. március 26. IP-cATP Adatbázisok tervezése és programozása gyakorlatokhoz (ZH-n is használható) segédlet ---------------------------------------------- A. SELECT (lekérdezés) 1. SQL utasítások áttekintése 2. Egytáblás lekérdezések SELECT_lista, WHERE_feltétel, ORDER BY 3. Többtáblás lekérdezések, összekapcsolások FROM lista 4. Halmazműveletek 5. Oracle függvények 6. Csoportosítás 7. Alkérdések WHERE-ben 8. Alkérdések FROM-ban 9. Hierarchikus lekérdezések B. DDL Adatdefiníciós nyelv C. DML Adatkezelő nyelv D. DCL Adatvezérlő nyelv E. Tranzakció vezérlés ---------------------------------------------- 1. SQL utasítások áttekintése - QL Lekérdező nyelv - DDL Adatdefiníciós nyelv - DML Adatkezelő nyelv - DCL Adatvezérlő nyelv - Tranzakció vezérlés 1.1. Lekérdező nyelv (Query language) SELECT 1.2. Adatdefiníciós nyelv DDL (Data definition language) CREATE ALTER DROP TRUNCATE RENAME COMMENT 1.3. Adatkezelő nyelv DML (Data manipulation language) INSERT UPDATE DELETE MERGE 1.4. Adatvezérlő nyelv DCL (Data control language) GRANT REVOKE 1.5. Tranzakció vezérlés Transaction control COMMIT ROLLBACK SAVEPOINT ---------------------------------------------- 2. Egytáblás lekérdezések. Egyszerű SELECT 2.1. Vetítés, projekció SELECT *|{[DISTINCT] column|expression [alias],...} FROM table; 2.2.Kiválasztás, szelekció SELECT *|{[DISTINCT] column|expression [alias],...} FROM table [WHERE condition(s)]; 2.2.1. egyszerű értékvizsgálat - aritmetikai összehasonlítás =, >, >=, <, <=, <> - LIKE '_ %' - IS NULL | IS NOT NULL 2.2.2. egyszerű értékek listája - BETWEEN...AND... - IN (set) | NOT IN (set) 2.2.3. logikai kifejezések kombinációja AND, OR, NOT - precedencia szabályok és zárójelezés 2.2.4. Allekérdezések >> lásd 6.téma 2.2.B. Kiválasztott sorok rendezése SELECT *|{[DISTINCT] column|expression [alias],...} FROM table [WHERE condition(s)] [ORDER BY column(s) [DESC]]; ---------------------------------------------- 3. Összekapcsolások (4.gyak./B) 3.1. JOIN - Természetes összekapcsolás - where utasításrészben megadva - IN (SFW) alkérdéssel adva - SQL-1999-es JOIN szintaxissal SELECT table1.column, table2.column FROM table1 [NATURAL JOIN table2] | [JOIN table2 USING (column_name)] | [JOIN table2 ON (table1.column_name = table2.column_name)] 3.2. Theta összekapcsolás (nem-ekvijoin) 3.3. Külső összekapcsolás 3.3.1. 9i előtti szintaxis (+) 3.3.2. 9i utáni aktuális szintaxis SELECT table1.column, table2.column [LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name)] 3.4. Descartes szorzat SELECT table1.column, table2.column FROM table1 [CROSS JOIN table2]; ---------------------------------------------- 4. Lekérdezések. Halmazműveletek SELECT *|{[DISTINCT] column|expression [alias],...} FROM table [, table, ...] [WHERE condition(s)] [ORDER BY column(s) [DESC]]; SFW = SELECT ... FROM ... WHERE ... 4.1. Unió (csak unió-kompatibilis táblákra) - duplikációk nélkül | illetve minden duplikációval SFW union | union all SFW 4.2. Különbség (csak unió-kompatibilis táblákra) SFW minus SFW 4.3. Metszet (származtatott művelet: (A metszet B) = A-(A-B) = stb.) SFW intersect SFW ---------------------------------------------- 5. Az Oracle leggyakrabban használt függvényei >> 5.1. Egysoros függvények >> 5.2. Csoport függvények >> 5.3. Listakezelő függvények 5.1. Egysoros függvények 5.1.1. Karakterkezelő függvények ASCII(str) az első karakter ascii kódja CHR(n) az n kódú karakter az adott kódkészletben CONCAT(str, str) LOWER(str) UPPER(str) INITCAP(str) szavak kezdőbetűi nagybetűssé alakítva LENGTH(str) karakterlánc hossza SUBSTR(str, pozíció [,hossz]) részkarakterlánc. pozíció < 0 esetén hátulról számol INSTR(str, str [,pozíció] [,előfordulás]) keresett részkarakterlánc kezdete LPAD(str, hossz [,str2]) balról adott hosszúságúra való kiegészítés (default str2 = ' ') RPAD(str, hossz [,str2]) jobbról adott hosszúságúra való kiegészítés LTRIM(str [,str2]) str2-beli karakterek eltávolítása str bal oldaláról RTRIM(str [,str2]) str2-beli karakterek eltávolítása str jobb oldaláról TRIM([LEADING | TRAILING | BOTH str] FROM str) NLS_LOWER NLS_UPPER NLS_INITCAP NLS_SORT adott nyelven történő rendezéshez REPLACE(str, mit [,mire]) str-beli karakterláncok lecserélése |kivágása TRANSLATE(str, 'input_karakterek', 'csere_karakterek') 5.1.1.B. Konkatenáció || 5.1.1.C. Reguláris kifejezések (koncepció UNIX grep parancs) REGEXP_LIKE (srcstr, pattern [,match_option]) REGEXP_INSTR (srcstr, pattern [, position [, occurrence [, return_option [, match_option]]]]) REGEXP_SUBSTR (srcstr, pattern [, position [, occurrence [, match_option]]]) REGEXP_REPLACE(srcstr, pattern [,replacestr [, position [, occurrence [, match_option]]]]) 5.1.2. Numerikus függvények ABS(n) ACOS(n) ASIN(n) ATAN(n), ATAN2(n, m) ATAN2(n, m) = ATAN(n/m) BITAND(positiv_int, positiv_int2) bitenkénti és művelet CEIL(n) felső egészrész COS(n) COSH(n) EXP(n) e az n-ediken FLOOR(n) egészrész LN(n) LOG(m, n) MOD(m, n) n=0 esetén m-et ad vissza POWER(m, n) m az n-ediken ROUND(n [, int]) kerekítés. int lehet <0 is, alapértelmezése = 0 SIGN(n) előjel SIN(n) SINH(n) SQRT(n) négyzetgyök TAN(n) TANH(n) TRUNC(n [, int]) csonkolás. int lehet <0 is, alapértelmezése = 0 5.1.2.B. Aritmetikai műveletek +, -, *, / 5.1.3. Dátumkezelő függvények SYSDATE rendszerdátum ADD_MONTHS(d, n) n hónap hozzáadása d-hez MONTHS_BETWEEN(d, d) az eltelt hónapok száma LAST_DAY(d) az adott hónap utolsó napja NEXT_DAY(d, str) a legközelebbi adott nevű nap d után ROUND(d, [, formátum]) kerekítés TRUNC(d, [, formátum]) csonkolás 5.1.3.B. Dátum és időkezelés a 9i verziótól TIMESTAMP adattípus lsd. köv.félévben 5.1.4. Alapvető konverziós függvények TO_CHAR(d [, fmt [, nlsparam]]) TO_CHAR(n [, fmt [, nlsparam]]) TO_DATE(str [, fmt [, nlsparam]]) TO_NUMBER(str [, fmt [, nlsparam]]) CHARTOROWID(str) ROWIDTOCHAR(rowid) 5.1.5. Egyéb függvények NVL(expr1, expr2) ha expr1 is NULL -> expr2 egyébként -> expr1 NVL2(expr1, expr2, expr3) ha expr1 is NULL -> expr3 egyébként -> expr2 NULLIF(expr1, expr2) CASE WHEN expr1 = expr 2 THEN NULL ELSE expr1 END DECODE(expr, search1, result1 [, search2, result2,...,] [, default]) CASE expr WHEN search1 THEN result1 [ WHEN search2 THEN result2 ... ELSE default ] END USER az aktuális felhasználó neve UID az aktuális felhasználó rendszerbeli azonosító kódja 5.2. Csoportfüggvények AVG COUNT MAX MEDIAN MIN STDDEV SUM VARIANCE 5.2.B. DISTINCT a csoportfüggvényekben 5.2.C. NULL érték a csoportfüggvényekben 5.2.D. Csoport- és egysoros függvények egymásba ágyazása 5.3. Listakezelő függvények COALESCE(expr1, expr2, ...) az első nem NULL értéket adja vissza GREATEST(expr_list) legnagyobb elem LEAST(expr_list) legkisebb elem ---------------------------------------------- 6. Csoportosítás SELECT column,] group_function(column), ... FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column]; ---------------------------------------------- 7. Allekérdezések WHERE-ben 7.1. IN (SFW) többsoros allekérdezések 7.2. THETA (SFW) egysoros allekérdezések 7.3. THETA [ANY|ALL](SFW) többsoros allekérdezések 7.4. EXISTS (SFW) többsoros allekérdezések Korrelált allekérdezések Relációs algebrai hányados (az osztás) kifejezése egymásba ágyazott NOT EXISTS allekérdezésekkel ---------------------------------------------- 8. Allekérdezések FROM-ban ---------------------------------------------- 9. Hierarchikus lekérdezések (4.gyak./C) Családfák. SFW start with ... connect by ... SELECT [LEVEL], column, expr... FROM table [WHERE condition(s)] [START WITH condition(s)] [CONNECT BY PRIOR condition(s)] ; ahol WHERE condition: expr comparison_operator expr Fa bejárása Kiindulás: START WITH column1 = value CONNECT BY PRIOR column1 = column2 Felülről-lefelé: Column1 = Parent Key Column2 = Child Key Alulról-felfelé: Column1 = Child Key Column2 = Parent Key ---------------------------------------------- ---------------------------------------------- B. DDL Adatdefiníciós nyelv B.1. Adatbázis objektumok áttekintése - table, view >> itt csak egyszerű szintaxissal, teljes szintaxisa >> köv. 5.félévben - index, IOT, partícionált táblák, sequence, synonym, >> köv.5.félévben lesz. B.2. Táblák létrehozása B.2.1. Új tábla definiálása CREATE TABLE [schema.]table (column datatype [DEFAULT expr][, ...]); B.2.2. Új tábla létrehozása táblából (allekérdezéssel) CREATE TABLE table [(column, column...)] AS subquery; B.3. Adattípusok - itt csak a leggyakrabban használt standard adattípusok - absztrakt adattípusok >> csak a köv.évben, 6.félévben Data Type Description VARCHAR2(size) Variable-length character data CHAR(size) Fixed-length character data NUMBER(p,s) Variable-length numeric data DATE Date and time values LONG Variable-length character data (up to 2 GB) CLOB Character data (up to 4 GB) RAW Raw binary data LONG RAW Raw binary data BLOB Binary data (up to 4 GB) BFILE Binary data stored in an external file (up to 4 GB) ROWID A base-64 number system representing the unique address of a row in its table Datatime Type Description TIMESTAMP Date with fractional seconds INTERVAL YEAR TO MONTH an interval of years and months INTERVAL DAY TO SECOND an interval of days, hours, minutes, seconds B.4. Megszorítások (CONSTRAINT) a create table utasításban PRIMARY KEY NOT NULL UNIQUE FOREIGN KEY ... REFERENCES ... CHECK B.5. Táblák szerkezetének módosítása, törlése ALTER TABLE ... DROP TABLE table_name; A tábla törlése TRUNCATE TABLE table_name; Az összes sor törlése RENAME COMMENT B.6. Nézetek létrehozása, átírása, törlése CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]...)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY [CONSTRAINT constraint]]; egyszerű nézet, összetett nézet ALTER VIEW view; DROP VIEW view; ----------------------------------------------- C. DML Adatkezelő nyelv (6.gyak./C) C.1. Adatsorok felvitele C.1.1. Értékek megadásával INSERT INTO table [(column [, column...])] VALUES (value [, value...]); - NULL értékre is kivételesen itt az = jelet használjuk - Dátum-értékek beszúrása TO_DATE függvény, formázási maszkkal C.1.2. Változók használata, helyettesítő érték INSERT INTO departments (department_id, department_name, location_id) VALUES (&department_id, '&department_name',&location); C.1.3. Beszúrás select-tel INSERT INTO table [(column [, column...])] subquery; C.2. Adatok módosítása C.2.1. Értékek megadásával UPDATE table SET column = value [, column = value, ...] [WHERE condition]; C.2.2. Allekérdezések használatával Update beágyazott selecttel, itt arra kell figyelni, hogy minden felülírandó rekordban csak egy rekordot adjon az allekérdezés. C.3. Adatsorok törlése DELETE [FROM] table [WHERE condition]; C.4. MERGE (update illetve insert utasításrész) C.5. DML utasítások használata, Karbantartás nézeten keresztül ---------------------------------------------- D. DCL Adatvezérlő nyelv Felhasználók. Szerepkörök. Jogosultságok. Jogok kiosztása és visszavonása GRANT REVOKE ---------------------------------------------- E. Tranzakció vezérlés Tranzakció = elemi tranzakciók (DML utasítások) sorozata show autocommit; set autocommit OFF; Véglegesítés: COMMIT Mentési pontok: SAVEPOINT mentésipont Visszagörgetés: ROLLBACK [TO mentésipont] ----------------------------------------------- ----------------------------------------------- Ennyi az SQL-ről most ebben az első 4.félévben, folyt.köv., de ebben a félévben PL/SQL-lel folyt. ----------------------------------------------- -----------------------------------------------