Informacje o SQL

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

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:
   • 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 ustawien, 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łowiu jest kopiowany do wyniku.
   Przyrostki	Znaczenie
   TH	Liczebnik cyfrowo (np. DDTH dla 4TH).
   SP	Słownie (np. DDSP dla FOUR).
   SPTH lub THSP	Liczebik słownie (np. DDSPTH dla FOURTH).

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

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

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;

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

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

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:

   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

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;

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.