Informacje o SQL

Spis treści

  1. Funkcje agregujące
  2. Funkcje na napisach
  3. Komentowanie tabel i kolumn
  4. Kolejność i semantyka klauzul przy CONNECT BY
  5. Format zapisu daty
  6. Funkcje na datach
  7. Różne funkcje o zastosowaniu ogólnym
  8. Tworzenie użytkowników i nadawanie im uprawnień
  9. Format zapisu liczb
  10. Funkcje na liczbach
  11. Dopuszczalne wyrażenia po ORDER BY
  12. Zmiana nazwy i opróżnianie obiektu
  13. Polecenia do obsługi transakcji
  14. Instrukcje UPDATE i DELETE ze skorelowanymi podzapytaniami
  15. Zapisywanie wyniku zapytań i w ogóle odpowiedzi serwera do pliku (SQL*Plus)
  16. Cechy SQL w DB2
  17. Dodawanie wartości NULL w składowych zapytania z UNION
  18. Tworzenie perspektyw

Funkcje agregujące

FunkcjaOpis
MINMinimum
MAXMaksimum
AVGŚrednia arytmetyczna
SUMSuma
COUNTLiczba wierszy/wartości
VARIANCEWariancja
STDDEVOdchylenie standardowe

Funkcje na napisach

LOWER(e)
Przekazuje napis e z wszystkimi literami zmienionymi na małe.
UPPER(e)
Przekazuje napis e z wszystkimi literami zmienionymi na wielkie.
INITCAP(e)
Przekazuje napis e, przy czym litery początkowe wszystkich wyrazów zmienia na wielkie, a pozostałe litery na małe.
CONCAT(e1, e2)
Przekazuje sklejenie e1 z e2 (równoważne operatorowi ||).
SUBSTR(e,m[,n])
Przekazuje n znaków napisu e od pozycji m.
Jeśli m jest ujemne, odliczanie rozpoczyna sie od końca e.
Jeśli n pominięto, przekazywana są wszystkie znaki do końca e (lub początku gdy n jest ujemne).
LENGTH(e)
Przekazuje liczbę znaków w e.
INSTR(e,m)
Przekazuje liczbę wskazującą położenie znaku m w e.
LPAD(e,n,s)
Przekazuje napis powstający z uzupełnienia e do n znaków napisem s z lewej strony.
RPAD(e,n,s)
Przekazuje napis powstający z uzupełnienia e do n znaków napisem s z prawej strony.
e LIKE wzorzec
ma wartość TRUE wtedy i tylko wtedy, gdy e pasuje do wzorca. W wzorcu:
% oznacza dowolny napis;
_ oznacza dowolny znak.

Przykłady

LOWER('SQL Course')		= 'sql course'
UPPER('SQL Course')		= 'SQL COURSE'
INITCAP('SQL Course')		= 'Sql Course'
CONCAT('Good', 'String')	= 'GoodString'
SUBSTR('String',1,3)		= 'Str'
LENGTH('String')		= '6'
INSTR('String', 'r')		= '3'
LPAD(sal,10,'*')		= '******5000'

Komentowanie tabel i kolumn

Do kolumn i tabel można dodać komentarze:

COMMENT ON TABLE emp IS 'wszyscy pracownicy firmy';
COMMENT ON COLUMN emp.empno IS 'identyfikator pracownika';

Kolejność i semantyka klauzul przy CONNECT BY

  1. Składnia: 
    SELECT 
  FROM
  WHERE
  START WITH
  CONNECT BY
    PRIOR może występować także w klauzuli SELECT (przykład poniżej).
  2. WHERE działa na już zbudowanej hierarchii (a więc później niż to sugeruje zapis). Można w nim użyc pseudokolumny LEVEL.
  3. Aby odciać gałąź zaczynająca się od pewnego miejsca należy to odcięcie zapisać w CONNECT BY (to zapytanie pominie SMITHa i jego podwładnych): 
    SELECT level, ename, PRIOR ename
  FROM emp
  START WITH ename = 'KING'
  CONNECT BY mgr = PRIOR empno 
    AND ename <> 'SMITH';
    Natomiast nastepujące zapytanie pominie SMITHa, ale zostawi jego podwładnych: 
    SELECT level, ename, PRIOR ename
  FROM emp
  WHERE ename <> 'SMITH'
  START WITH ename = 'KING'
  CONNECT BY mgr = PRIOR empno;

Format zapisu daty

  1. Do konwersji data-napis służą funkcje: 
    TO_DATE(napis, format)
TO_CHAR(wyrażenie_datowe, format)
  2. Drugi argument (format) jest opcjonalny. Jego wartość domyślna to 'DD-MON-YY'.
  3. Data i czas pamietają: wiek, rok, miesiąc, dzień, godzinę, minutę, sekundę.
  4. Datę i czas bieżący podaje funkcja SYSDATE.
  5. format jest napisem, który może zawierać nastepujące elementy:
    Formaty datyZnaczenie
    SCC lub CC Wiek. Jeśli użyto S, to daty ujemne są poprzedzone przez BC.
    YYYY lub SYYYY Rok. Jeśli użyto S, to daty ujemne są poprzedzone przez BC.
    YYY lub YY lub Y Ostatnie 3, 2, 1 cyfra (y) roku.
    Y,YYY Rok z przecinkiem we wskazanym miejscu.
    IYYY, IYY, IY, I 4-, 3-, 2-, 1-cyfrowy rok zgodnie ze standardem ISO.
    SYEAR lub YEAR Rok słownie. Jeśli użyto S, to daty ujemne są poprzedzone przez BC.
    BC lub AD Oznacznik BC/AD
    B.C. lub A.D. Oznacznik BC/AD z kropkami.
    Q Kwartał roku.
    MM Miesiąc dwucyfrowo.
    MONTH Nazwa miesiąca uzupełniona odstępami do dziewięciu znaków.
    MON Trzyliterowy skrót nazwy miesiąca.
    RM Miesiąc cyframi rzymskimi.
    WW lub W Tydzień roku lub miesiąca.
    DDD, DD lub D Dzień roku, miesiąca lub tygodnia.
    DAY Nazwa dnia tygodnia uzupełniona odstępami do dziewięciu znaków.
    DY Trzyliterowy skrót nazwy dnia tygodnia.
    J Dzień wg kalendarz juliańskiego; liczba dni do 31-12-4713 p.n.e.
    RR Dwucyfrowy numer roku, ale z okienkowaniem wieków, tzn. jeśli
    • w latach (x)00-49 konwertujemy z roku 00-49 to wychodzi rok (x)??
    • w latach (x)00-49 konwertujemy z roku 50-99 to wychodzi rok (x-1)??
    • w latach (x)50-99 konwertujemy z roku 00-49 to wychodzi rok (x+1)??
    • w latach (x)50-99 konwertujemy z roku 50-99 to wychodzi rok (x)??
    To dotyczy tylko konwersji z napisu na date.
    • w 1995 TO_DATE('27-OCT-95', 'DD-MON-YY') oznacza 27 X 1995
    • w 1995 TO_DATE('27-OCT-95', 'DD-MON-RR') oznacza 27 X 1995
    • w 1995 TO_DATE('27-OCT-17', 'DD-MON-YY') oznacza 27 X 1917
    • w 1995 TO_DATE('27-OCT-17', 'DD-MON-RR') oznacza 27 X 2017
    • w 2001 TO_DATE('27-OCT-17', 'DD-MON-YY') oznacza 27 X 2017
    • w 2001 TO_DATE('27-OCT-17', 'DD-MON-RR') oznacza 27 X 2017
    • w 2001 TO_DATE('27-OCT-95', 'DD-MON-YY') oznacza 27 X 2095
    • w 2001 TO_DATE('27-OCT-95', 'DD-MON-RR') oznacza 27 X 1995
    Formaty czasuZnaczenie
    AM lub PM Oznacznik AM/PM.
    A.M. lub P.M. Oznacznik AM/PM.
    HH, HH12 lub HH24 Godzina dnia: wg lokalnych ustawień, 1-12, lub 0-23.
    MI Minuta (0-59).
    SS Sekunda (0-59).
    SSSSS Liczba sekund północy (0-86399).
    Inne formatyZnaczenie
    / . , Znaki przestankowe są kopiowane do wyniku.
    " dnia " Napis w cudzysłowie jest kopiowany do wyniku.
    PrzyrostkiZnaczenie
    TH Liczebnik cyfrowo (np. DDTH dla 4TH).
    SP Słownie (np. DDSP dla FOUR).
    SPTH lub THSP Liczebnik słownie (np. DDSPTH dla FOURTH).
  6. Przykłady:
    Formatwynik
    HH24:MI:SS15:45:32
    DD "of" MONTH12 of OCTOBER
    ddspthfourteenth
    fmDD Month YYYY 17 November 1981
    1 May 1981
    9 June 1981
    2 April 1981
    28 September 1981
    20 February 1981

Funkcje na datach

data + liczba
Dodaje do daty podaną liczbę dni. Wynikiem jest DATE.
data - liczba
Odejmuje od daty podaną liczbę dni. Wynikiem jest DATE.
data - data
Przekazuje liczbę dni od pierwszej daty do drugiej daty.
data + liczba/24
Dodaje do daty podaną liczbę godzin. Wynikiem jest DATE.
MONTHS_BETWEEN(d1, d2)
Przekazuje liczbę miesięcy między d1 i d2. Wynik może być dodatni albo ujemny. Wynik nie musi być liczbą całkowitą. Ułamek odpowiada kawałkowi miesiąca.
ADD_MONTHS(data, n)
Dodaje n kalendarzowych miesięcy do daty. Liczba n musi być całkowita, ale może być ujemna.
NEXT_DAY(data, 'znak')
Znajduje nastepny dzień po dacie, który jest dniem tygodnia wskazanym przez 'znak'. 'znak' może być liczbą lub pierwszą literą nazwy dnia tygodnia.
LAST_DAY(data)
Znajduje datę ostatniego dnia miesiąca zawierajšcego datę.
ROUND(data[,'fmt'])
Przekazuje zaokrąglenie daty do jednostki określonej przez model formatu 'fmt'. Jeśli nie podano 'fmt' zaokrąglenie nastepuje do najbliższej pełnej daty.
TRUNC(data[, 'fmt'])
Przekazuje obcięcie daty do jednostki określonej przez model formatu 'fmt'. Jeśli nie podano 'fmt' obcięcie nastepuje do najbliższej pełnej.

Przykłady

MONTHS_BETWEEN('01-SEP-95','11-JAN-94')	= 19.6774194
ADD_MONTHS ('11-JAN-94',6)		= '11-JUL-94'
NEXT_DAY ('01-SEP-95','FRIDAY')		= '08-SEP-95'
LAST_DAY('01-SEP-95')			= '30-SEP-95'
ROUND('25-JUL-95','MONTH')		= '01-AUG-95'
ROUND('25-JUL-95','YEAR')		= '01-JAN-96'
TRUNC('25-JUL-95','MONTH')		= '01-JUL-95'
TRUNC('25-JUL-95','YEAR')		= '01-JAN-95'

Różne funkcje o zastosowaniu ogólnym

NVL(x, y)
Jeśli x nie jest NULL, to przekazuje x.
Jeśli x jest NULL, to przekazuje y.
DECODE(e, s1, r1 [, s2, r2,...,] [, defautl])
Jeśli e = s1, to przekazuje r1.
Jeśli e = s2, to przekazuje r2.
Jeśli e = s3, to przekazuje r3.
...
Wpp. przekazuje default (jeśli nie podano default, to przekazuje NULL).

Przykład

	SELECT	job, sal,
		DECODE(job,	'ANALYST',	SAL*1.1,
				'CLERK',	SAL*1.15,
				'MANAGER',	SAL*1.20,
						SAL)		REVISED_SALARY
	  FROM   emp;

JOB             SAL REVISED_SALARY
--------- --------- --------------
PRESIDENT      5000           5000
MANAGER        2850           3420
MANAGER        2450           2940
...
14 rows selected.

Tworzenie użytkowników i nadawanie im uprawnień

Użytkownik

CREATE USER użytkownik IDENTIFIED BY hasło;

Zmiana hasla:

ALTER USER użytkownik IDENTIFIED BY nowe_hasło;

Przywileje nie związane z obiektami bazy danych

GRANT przywilej [, przywilej...]			
  TO użytkownik [, użytkownik...];

Takimi przywilejami są:

CREATE SESSION
Połączenie z bazą danych.
CREATE TABLE
Tworzenie tabel w swoim schemacie.
CREATE SEQUENCE
Tworzenie sekwencji w swoim schemacie.
CREATE VIEW
Tworzenie perspektyw w swoim schemacie.
CREATE PROCEDURE
Tworzenie procedury i funkcji składowanej oraz pakietu składowanego w swoim schemacie.
CREATE (ANY) PROCEDURE | TRIGGER | TYPE
ALTER (ANY) PROCEDURE | TRIGGER | TYPE
DROP (ANY) PROCEDURE | TRIGGER | TYPE
EXECUTE (ANY) PROCEDURE | TYPE
Żeby tworzyć, zmieniać lub usuwać podprogram w PL/SQL musisz mieć odpowiedni przywilej systemowy. Jeśli nie użyto słowa kluczowego ANY, przywilej dotyczy tylko twojego schematu. Musisz mieć przywilej EXECUTE, aby móc wywołać podprogram w PL/SQL, jeśli nie jesteś jego właścicielem. Przywileje obiektowe do obiektów, z których ten podprogram korzysta muszą być nadane wprost (a nie przez rolę). Podprogram PL/SQL wykonuje się z prawami właściciela (set uid). Słowo kluczowe PROCEDURE dotyczy procedur, funkcji oraz pakietów składowanych.

Role

CREATE   ROLE  rola;

Przykład:

CREATE ROLE szef;
GRANT create table to szef;	-- nadanie roli prawa
GRANT szef to józio, kazio;	-- nadanie użytkownikowi roli

Są pewne role predefiniowane:

PUBLIC
Wszyscy.
DBA
Administrator bazy danych) ma prawo do:
CREATE USER
Pozwala na tworzenie nowych użytkowników.
DROP USER
Pozwala na usuwanie użytkowników.
DROP ANY TABLE
Pozwala na usuwanie tabel z dowolnego schematu.
BACKUP ANY TABLE
Pozwala na wykonywanie kopii zapasowej tabel z dowolnego schematu za pomocą narzędzia do eksportu.

Uprawnienia tabelowe

PrzywilejTabelaPerspektywaSekwencjaProcedura
ALTER X   X  
DELETE X X    
EXECUTE       X
INDEX X      
INSERT X X    
REFERENCES X      
SELECT X X X  
UPDATE X X     
GRANT przywilej_obiektowy [(kolumny)]
     ON	obiekt
     TO	{użytkownik|rola|PUBLIC}
     [WITH GRANT OPTION];
REVOKE {przywilej [, przywilej...]|ALL}
     ON	obiekt
     FROM	{uzytkownik[, użytkownik...]|rola|PUBLIC}
     [CASCADE CONSTRAINTS];  -- jest konieczne w celu usunięcia wszystkich
			     -- więzów spójności referencyjnej utworzonych
			     -- na mocy przywileju REFERENCES

Format zapisu liczb

  1. Do konwersji liczba-napis służą funkcje: 
    TO_CHAR(napis, format)
TO_NUMBER(wyrazenie_datowe, format)
  2. Drugi argument (format) jest opcjonalny.
  3. Format jest napisem, który może zawierać następujące elementy:
    ElementOpisPrzykładWynik
    9 Cyfra (liczba dziewiątek określa szerokość pola) 999999 1234
    0 Wyświetl wiodące zera 099999 001234
    $ Ruchomy znak dolara $999999 $1234
    L Ruchomy znak lokalnej waluty L999999 zł1234
    . Kropka dzisiętna na wskazanej pozycji 9999.99 1234.00
    , Przecinek na wskazanej pozycji 999,999 1,234
    MI Znak minus z prawej strony (przy wartościah ujemnych) 9999MI 1234-
    PR Liczby ujemne w nawiasach 9999PR <1234>
    EEEE Notacja inżynierska (w formacie muszą być cztery E) 9.9EEEE 1.2E+03
    V Pomnóż przez 10 n razy (n to liczba dziewiątek po V) 9999V99 123400

Funkcje na liczbach

ROUND(e, [n])
Przekazuje zaokrąglenie e do n miejsc po przecinku.
Jeśli pominięto n, jest to zaokrąglenie do zera miejsc po przecinku.
Jeśli n jest ujemne, zaokrągla się cyfry na lewo od przecinka dziesiętnego.
TRUNC(e, [n])
Przekazuje obcięcie e do n miejsc po przecinku.
Jeśli pominięto n, jest to obcięcie do zera miejsc po przecinku.
Jeśli n jest ujemne, obcina się cyfry na lewo od przecinka dziesiętnego.
MOD(m, n)
Przekazuje resztę z dzielenia n przez m.

Przykłady

ROUND(45.926, 2)	=  45.93
TRUNC(45.926, 2)	=  45.92
MOD(1600, 300)		= 100
ROUND(45.923, 2)	=  45.92
ROUND(45.923, 0)	=  46
ROUND(45.923, -1)	=  50

Dopuszczalne wyrażenia po ORDER BY

  1. nazwa kolumny,
  2. alias kolumny,
  3. dowolne wyrażenie (nie musi być wyświetlane przez SELECT!)
    UWAGA: ze względu na 4 i na zdrowy rozsądek nie może to być stała naturalna,
  4. numer kolumny.

Zmiana nazwy i opróżnianie obiektu

RENAME nazwa_stara TO nazwa_nowa;
Może to być tabela, perspektywa albo dowolny inny obiekt składowany.
TRUNCATE nazwa_tabeli;
Powoduje usunięcie wszystkich wierszy z tabeli i zwolnienie zajmowanej przestrzeni.
DELETE FROM nazwa_tabeli;
Nie zwalnia zajmowanej przestrzeni.

Polecenia do obsługi transakcji

SAVEPOINT nazwa;
Tworzy punkt powrotu nazwa.
ROLLBACK;
Odtwarza dane z początku transakcji.
ROLLBACK to nazwa;
Odtwarza dane z chwili wykonania SAVEPOINT nazwa.
COMMIT;
Zatwierdza transakcję.

W Oracle każde polecenie DDL powoduje automatyczne COMMIT przed i po swoim wykonaniu, niezależnie od tego czy się powiedzie.

Instrukcje UPDATE i DELETE ze skorelowanymi podzapytaniami

  1. Podzapytanie może występować zarówno w SET jak i WHERE.
  2. Podzapytania mogą być skorelowane z główną tabelą, na której działamy.
  3. Alias dla tej tabeli nadajemy tak jak w SELECT (przykład).

Zapisywanie wyniku zapytań i w ogóle odpowiedzi serwera do pliku (SQL*Plus)

SPOOL nazwa_pliku
Od tej chwili wszystkie wyniki idą do pliku nazwa_pliku i na konsolę.
SPOOL OFF
Od tej chwili wszystkie wyniki idą tylko na konsolę.

Można to wykorzystać do automatycznego tworzenia skryptów. Np. wynik tego zapytania kasuje wszystkie tabele:

SELECT 'drop table ' || table_name || ' cascade constraints;'
  FROM user_tables;

Przykład

Skrypt kasujący wszystkie tabele: dropall.sql.

Cechy SQL w DB2

Zawiera wiele cech SQL2 a nawet SQL3.

  1. Operacje mnogościowe UNION ALL, INTERSECT ALL, EXCEPT ALL działają tak jak powinny na multizbiorach.
  2. Są operacje (FULL/LEFT/RIGHT OUTER)/INNER JOIN ON.
  3. Rekurencyjne zapytanie z WITH ... są, ale nie jest to wyszukiwanie do znalezienia punktu stałego, ale raczej DFS.
  4. Złączenia zależne są. Definiuje się je przez TABLE(...). 
    SELECT ... FROM A, TABLE (SELECT * FROM B WHERE b1 = A.a1) BA ...
  5. Są podtypy tabelowe, muszą być oparte na definiowanych przez użytkownika typach/podtypach:
    CREATE TABLE ... UNDER ...
    SELECT * FROM A;
    Pokazuje wszystkie wiersze z A i z jej podtabel, ale tylko te kolumny, które są w A.
    SELECT * FROM ONLY(A);
    Pokazuje wszystkie wiersze z A, ale nie wiersze, które są w podtabelach A.
    SELECT * FROM OUTER(A);
    Pokazuje wszystkie wiersze z A i z jej podtabel. Drukuje wszystkie kolumny podtabel. Jeśli dany wiersz nie zawiera odpowiedniej wartości, używa się NULL.
  6. We FROM mogą występować aplikacje funkcji wierszowych: 
    SELECT t.c1, z.c5
     FROM t, TABLE( tf3(t.c2) ) AS z
     WHERE t.c3 = z.c4;
    t jest przed tf3 we FROM, więc t.c2 jest znane.

    Na podstawie wartości wiersza z t, funkcja tf3 wylicza wiersz, który jest złączany z wierszem z t. Jest to pewna forma złączenia zależnego.

  7. Wielokryterialne grupowanie

    W przypadku grupowania na innym poziomie niz najwyższy "niegrupowane-tym-razem" kolumny sa wypelniane NULLami.

    Grupowanie może być po wielu GROUPING SETS.

    GROUPING SETS ((a,b), (b,c), (d))
    ROLLUP(a,b,c)
    Generuje cztery GROUPING SETS: (a,b,c), (a,b), (a) i ()
    CUBE(a,b)
    Generuje cztery GROUPING SETS:(a,b), (a), (b) i ().

    ROLLUP grupuje po coraz mniejszym zbiorze, za każdym razem odrzucając ostatnie kryterium.

    CUBE grupuje po każdym pozdbiorze swego argumentu.

Dodawanie wartości NULL w składowych zapytania z UNION

Jeśli napiszemy:

SELECT deptno, ename
  FROM emp
UNION
SELECT NULL, dname
  FROM dept;

będzie błąd (niezgodność typów danych). Aby to zadziałało trzeba użyć konwersji typów i napisać:

SELECT deptno, ename
  FROM emp
UNION
SELECT TO_NUMBER(NULL), dname
  FROM dept;

Tworzenie perspektyw

Składnia:

CREATE [ FORCE | NOFORCE ] VIEW nazwa AS
  SELECT ...
  [ WITH CHECK OPTION [ CONSTRAINT nazwa_wiezu ] ]
  [ WITH READ ONLY ];
NOFORCE
Powoduje, że nie sprawdza się istnienia tabel, z których korzysta pespektywa.
FORCE
Wręcz przeciwnie. Opcja domyślna.
WITH CHECK OPTION
Wymusza sprawdzenie czy istrukcje DML na perspektywie nie powodują sfalsyfikowania warunku WHERE pespektywy. Jeśli falsyfikują operacja DML jest odrzucana. To są więzy spójności jak każde inne i można mu nadać im nazwę poprzez CONSTRAINT.
WITH READ ONLY
Powoduje, że poprzez tę perspektywę nie można zmieniać danych.