Informacje o SQL

Spis treści

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

Funkcje agregujące

Funkcja	Opis
`MIN`	Minimum
`MAX`	Maksimum
`AVG`	Średnia arytmetyczna
`SUM`	Suma
`COUNT`	Liczba wierszy/wartości
`VARIANCE`	Wariancja
`STDDEV`	Odchylenie 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`

Składnia:
```
SELECT 
  FROM
  WHERE
  START WITH
  CONNECT BY
```
PRIOR może występować także w klauzuli SELECT (przykład poniżej).
WHERE działa na już zbudowanej hierarchii (a więc później niż to sugeruje zapis). Można w nim użyc pseudokolumny LEVEL.

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

Do konwersji data-napis służą funkcje:

TO_DATE(napis, format)
TO_CHAR(wyrażenie_datowe, format)

Drugi argument (format) jest opcjonalny. Jego wartość domyślna to 'DD-MON-YY'.
Data i czas pamietają: wiek, rok, miesiąc, dzień, godzinę, minutę, sekundę.
Datę i czas bieżący podaje funkcja SYSDATE.

format jest napisem, który może zawierać nastepujące elementy:

użycie małych liter w elemencie formatu powoduje wypisanie wyniku małymi literami, uzycie dużych dużymi;
pierwsza litera duża, reszta małe, wtedy wynik zacznie się z dużej litery a potem będą małe;
fm przełącza między występowaniem lub nie dopełniania wyników zerami/odstępami.

Formaty daty	Znaczenie
`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 czasu	Znaczenie
`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 formaty	Znaczenie
`/ . ,`	Znaki przestankowe są kopiowane do wyniku.
`" dnia "`	Napis w cudzysłowie jest kopiowany do wyniku.
Przyrostki	Znaczenie
`TH`	Liczebnik cyfrowo (np. `DDTH` dla `4TH`).
`SP`	Słownie (np. `DDSP` dla `FOUR`).
`SPTH` lub `THSP`	Liczebnik słownie (np. `DDSPTH` dla `FOURTH`).

Przykłady:

Format wynik

HH24:MI:SS 15:45:32

DD "of" MONTH 12 of OCTOBER

ddspth fourteenth

fmDD Month YYYY 17 November 1981
1 May 1981
9 June 1981
2 April 1981
28 September 1981
20 February 1981

Format	wynik
`HH24:MI:SS`	`15:45:32`
`DD "of" MONTH`	`12 of OCTOBER`
`ddspth`	`fourteenth`
`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

Przywilej	Tabela	Perspektywa	Sekwencja	Procedura
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

Do konwersji liczba-napis służą funkcje:

TO_CHAR(napis, format)
TO_NUMBER(wyrazenie_datowe, format)

Drugi argument (format) jest opcjonalny.

Format jest napisem, który może zawierać następujące elementy:

Element	Opis	Przykład	Wynik
`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`

nazwa kolumny,
alias kolumny,
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,
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

Podzapytanie może występować zarówno w SET jak i WHERE.
Podzapytania mogą być skorelowane z główną tabelą, na której działamy.
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.

Operacje mnogościowe UNION ALL, INTERSECT ALL, EXCEPT ALL działają tak jak powinny na multizbiorach.
Są operacje (FULL/LEFT/RIGHT OUTER)/INNER JOIN ON.
Rekurencyjne zapytanie z WITH ... są, ale nie jest to wyszukiwanie do znalezienia punktu stałego, ale raczej DFS.

Złączenia zależne są. Definiuje się je przez TABLE(...).

SELECT ... FROM A, TABLE (SELECT * FROM B WHERE b1 = A.a1) BA ...

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.
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.
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.

SQL