PostgreSQL i WITH

Nie wiem czy wiecie, ale PostgreSQL jest bardzo ciekawym silnikiem bazy danych. Zapewne większość z was korzysta głównie z bazy MySQL lub bazy Oracle jeżeli pracujemy w dużych korporacjach. Mi udało się trafić do firmy, która pracuje na PostgreSQL i nie żałuję swojego doświadczenia zdobytego właśnie z tym silnikiem.

Poniżej prosty przykład jak można użyć polecania WITH do budowania ciekawych wyników, zwiększania optymalności zapytań.

WITH to jest w skrócie okienko, które buduje nam zestaw danych przechowywany w pamięci

I tak np. możemy zbudować zapytanie

SELECT * FROM client WHERE ....

które użyjemy później

WITH klienci as (
SELECT * FROM client WHERE ...
)

SELECT * FROM zamowienia
INNER JOIN klienci on klienci.id = zamowienia.id_client

Zastanawia się pewnie ktoś po co to komu potrzebne skoro możemy użyć tutaj zwykłego JOINa

Racja, ale jeżeli w okienku mamy wiele tabel i wiele danych, albo okienek mamy kilka i jedno okienko korzysta z innego mamy już ciekawe rozwiązanie, np.

WITH zamowienia_cte as (
SELECT * FROM zamowienia WHERE date > '2015-01-01'
),
produkty as (
SELECT * FROM produkty WHERE id IN (SELECT id_product FROM zamowienia_cte
),
...
...
...
...

Jak widzicie w okienku produkty będziemy wyświetlać tylko te produkty, które występują w zamówieniach, Dodatkowo finalnie możemy skorzystać z wszystkich okienek przy wyświetlaniu danych, możemy budować JOINy itd.

Jeżeli zaczniecie używać komendy WITH na pewno zrozumiecie dokładniej sens jej działania, bowiem czasami warto ograniczyć z góry zakres danych używając np indeksu, niż robić JOINa do tabeli, bowiem czas wykonywania się znacznie wydłuża...

przekonacie się o tym używając EXPLAINa, ale o  nim napiszę innym razem.

UPDATE

UPDATE - polecenie używane do aktualizacji istniejących w tabeli rekordów.

Postać instrukcji UPDATE jest następująca:

UPDATE tabela
SET kolumna_1=wartosc, kolumna_2=wartosc_2,...
WHERE jakas_kolumna=jakas_wartosc


W określonej przez nas tabeli zostaną zaktualizowane tylko kolumny, które zostały określone przez nas. Pozostałe kolumny nie ulegną zmianie (przykład 1).


UWAGA!!!
Należy zwrócić uwagę na klauzulę WHERE. Bez użycia jej zostaną zmodyfikowane wszystkie rekordy w tabeli. (przykład 2)

Często zachodzi sytuacja, iż chcemy zaktualizować tylko jeden rekord.
Najlepszym rozwiązaniem w takiej sytuacji jest użycie WHERE określając klucz główny (jest on unikalną wartością - mamy pewność, iż dokładnie jeden rekord zostanie zaktualizowany).



Dla poniższych przykładów utworzono nową tabelę o nazwie TEST:


CREATE TABLE TEST (
id int,
nazwa VARCHAR2(20)
);


SELECT * FROM TEST;

ID NAZWA
---------------------- --------------------

0 rows selected



Przykład 1.

Na początek, abyśmy mogli zmodyfikować jakiekolwiek wiersze, należy dodać kilka rekordów.

INSERT INTO TEST VALUES('1', 'nazwa 1');
INSERT INTO TEST VALUES('2', 'nazwa 2');

SELECT * FROM TEST;

ID NAZWA
---------------------- --------------------
1 nazwa 1
2 nazwa 2

2 rows selected



UPDATE TEST SET NAZWA='nazwa zmieniona' WHERE ID='2';

1 rows updated


SELECT * FROM TEST;

ID NAZWA
---------------------- --------------------
1 nazwa 1
2 nazwa zmieniona

2 rows selected




Przykład 2.

UWAGA!
W tym przykładzie zostanie przedstawione użycie polecenia UPDATE bez klauzuli WHERE (zostaną zmodyfikowane wszystkie rekordy).

INSERT INTO TEST VALUES('1', 'nazwa 1');
INSERT INTO TEST VALUES('2', 'nazwa 2');

1 rows inserted
1 rows inserted

SELECT * FROM TEST;

ID NAZWA
---------------------- --------------------
1 nazwa 1
2 nazwa 2

2 rows selected


UPDATE TEST SET NAZWA='tekst';
2 rows updated


SELECT * FROM TEST;

ID NAZWA
---------------------- --------------------
1 tekst
2 tekst

2 rows selected

SELECT

SELECT - polecenie, które jest najczęściej używaną instrukcją SQL. SELECT służy głównie do pobierana danych z tabeli lub kilku tabel.


Postać instrukcji SELECT jest następująca:
SELECT kolumna FROM tabela;

Można też wybierać kilka tabel z tabeli.
SELECT kolumna1, kolumna2, kolumna3 FROM tabela;


Warto też dodać, iż SQL pozwala na używanie dużych jak i małych liter do budowania zapytań. Polecenie
SeLeCt kolumna fRoM tabela;
również będzie porawne.



Poleceniem SELECT można również pobrać wszystkie dane z tabeli.
SELECT * FROM tabela;



Instrukcja SELECT daje nam możliwość pobierania danych z kilku kolumn oraz kilku tabel jednocześnie.
Można to uczynić wykonując polecenie.

SELECT tabela.kolumna, tabela2.kolumna2 FROM tabela, tabela2;

Nazwę tabeli piszemy w pierwszej kolejności, następnie stawiamy kropkę oraz wpisujemy nazwę kolumny (bez spacji).

Należy jednak pamiętać, iż powtarzające się dane z jednej tabeli będą wiązane z danymi z drugiej tabeli.
Jeżeli na przykład nasza tabela DEPT posiada 4 rekordy oraz tabela EMP 13 rekordów, to zapytanie

SELECT DEPT.deptno, EMP.empno FROM DEPT, EMP;

zwróci nam 52 wiersze (4x13).

Zapytanie
SELECT DEPT.deptno, EMP.empno FROM DEPT, EMP WHERE DEPT.DEPTNO=EMP.DEPTNO;
pozwoli na rozwiązanie tego problemu, gdyż zadbaliśmy o to, aby DEPTNO z tabeli DEPT był taki sam jak DEPTNO z tabeli EMP (użycie WHERE).
Wynikiem tego zapytania było 13 rekordów.



Przykład 1.
Wyświetlenie całej zawartości tabeli EMP.

SELECT * FROM EMP;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------------------- --------------- --------------- ---------------------- ------------------------- ---------------------- ---------------------- ----------------------
7369 SMITH CLERK 7902 80/12/17 800 20
7499 ALLEN SALESMAN 7698 81/02/20 2400 300 30
7521 WARD SALESMAN 7698 81/02/20 1875 500 30
7566 JONES MANAGER 7839 81/04/02 2975 20
7654 MARTIN SALESMAN 7698 81/09/28 1875 1400 30
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7788 SCOTT ANALYST 7566 82/12/09 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7876 ADAMS CLERK 7788 83/01/12 1100 20
7900 JAMES CLERK 7698 81/12/03 950 30
7902 FORD ANALYST 7566 81/12/03 3000 20
7934 MILLER CLERK 7782 82/01/23 1300 10

13 rows selected


Przykład 2.
Wyświetlenie danych z tabeli EMP z kolumny EMPNO.

SELECT empno FROM EMP;

EMPNO
----------------------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7876
7900
7902
7934

13 rows selected



Przykład 3.
Wyświetlenie danych z tabeli DEPT z kolumny DEPTNO oraz DNAME.

SELECT deptno, dname FROM DEPT;

DEPTNO DNAME
---------------------- -------------------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS

4 rows selected


Przykład 4.
Wyświetlenie danych z tabeli DEPT z kolumny DEPTNO oraz DNAME (nazwa tabeli dodana do nazwy kolumny).
Polecenie to daje taki sam wynik jak wynik z przykładu 3.

SELECT DEPT.deptno, DEPT.dname FROM DEPT;

DEPTNO DNAME
---------------------- -------------------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS

4 rows selected

ORDER BY

ORDER BY - klauzula ta służy do sortowania danych. Dane można sortować rosnąco, malejąco lub używając obu warunków jednocześnie względem kilku kolumn.

Składnia polecenia jest następująca:

SELECT kolumna FROM tabela ORDER BY kolumna ASC|DESC
(przykład 1, przykład 2)

możemy definiować sortowanie względem kilku tabel, np:

SELECT kolumna1, kolumna2, FROM tabela ORDER BY kolumna1 ASC|DESC, kolumna2 ASC|DESC;
(przykład 3)



ASC i DESC oznacza sposób sortowania:
ASC - sortowanie rosnąco,
DESC - sortowanie malejąco.


UWAGA!
Możemy użyć klauzuli ORDER BY nie podawając sposobu sortowania. Domyślnym sposobem sortowania jest wtedy sortowanie rosnące (ASC).



Przykład 1.
Wybieramy numery departamentów oraz nazwy departamentów sortując względem numeru departamentów (rosnąco).

SELECT DEPTNO, DNAME FROM DEPT ORDER BY DEPTNO ASC;

lub

SELECT DEPTNo, DNAME FROM DEPT ORDER BY DEPTNO;


DEPTNO DNAME
---------------------- -------------------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS

4 rows selected


Przykład 2.
Wybieramy numery departamentów oraz nazwy departamentów sortując względem numeru departamentów (malejąco).

SELECT DEPTNo, DNAME FROM DEPT ORDER BY DEPTNO DESC;s

EPTNO DNAME
---------------------- -------------------------
40 OPERATIONS
30 SALES
20 RESEARCH
10 ACCOUNTING

4 rows selected



Przykład 3.

Wybieramy wynagrodzenie (wyższe lub równe od 3000 - WHERE SAL=>'3000'), numer pracownika oraz nazwę sortując dane względem wynagrodzenia - malejąco oraz numeru pracownika - rosnąco.

SELECT SAL, EMPNO, ENAME FROM EMP WHERE SAL>='3000' ORDER BY SAL DESC, EMPNO ASC;


SAL EMPNO ENAME
---------------------- ---------------------- ---------------
5000 7839 KING
3000 7788 SCOTT
3000 7902 FORD

3 rows selected

DISTINCT

DISTINCT - polecenie, które służy do wyświetlania unikalnych (niepowtarzających się) wartości w naszym zapytaniu.

Postać instrukcji zawierającej DISTINCT jest następująca:
SELECT DISTINCT kolumna FROM tabela;

lub

SELECT DISTINCT(kolumna) FROM tabela;


UWAGA!
Polecenie DISTINCT wyświetla również puste rekordy (patrzy przykład 2).


Przykład 1.
Rozważmy następującą sytuację. Chcemy wyświetlić wszystkie unikalne nazwy zawodów naszych pracowników. W tym celu tworzymy zapytanie:

SELECT DISTINCT EMP.JOB FROM EMP;

JOB
---------------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST

5 rows selected

Wynikiem jest 5 rekordów, gdyż tylko tyle różnych stanowisk posiadamy w naszej bazie danych. (przypominam, że liczba rekordów w tabeli EMP jest równa 13).



Przykład 2.
Polecenie
SELECT DISTINCT EMP.MGR FROM EMP;
zwróci 7 rekordów (proszę zwrócić uwagę na rekord drugi).

MGR
----------------------
7839

7782
7698
7902
7566
7788

7 rows selected

DELETE

DELETE - polecenie używane do usuwania isteniejących w tabeli rekordów.

Postać instrukcji DELETE jest następująca:

DELETE FROM tabela WHERE kolumna=wartosc;

UWAGA!!!
Należy zwrócić uwagę na klauzulę WHERE. Bez jej użycia zostaną usunięte wszystkie rekordy w tabeli. (przykład 2).

Często zachodzi sytuacja, iż chcemy usunąć tylko jeden rekord.
Najlepszym rozwiązaniem w takiej sytuacji jest użycie WHERE określając klucz główny (jest on unikalną wartością - mamy pewność, iż dokładnie jeden rekord

zostanie usunięty).



Dla poniższych przykładów utworzono nową tabelę o nazwie TEST:

CREATE TABLE TEST (
id int,
nazwa VARCHAR2(20)
);


SELECT * FROM TEST;

ID NAZWA
---------------------- --------------------

0 rows selected





Przykład 1.

Na początek, abyśmy mogli usunąć jakiekolwiek wiersze, należy dodać kilka rekordów.

INSERT INTO TEST VALUES('1', 'do usuniecia 1');
1 rows inserted
INSERT INTO TEST VALUES('2', 'do usuniecia 2');
1 rows inserted

SELECT * FROM TEST;

ID NAZWA
---------------------- --------------------
1 do usuniecia 1
2 do usuniecia 2

2 rows selected


DELETE FROM TEST WHERE ID='1';
1 rows deleted



SELECT * FROM TEST;

ID NAZWA
---------------------- --------------------
2 do usuniecia 2

1 rows selected




Przykład 2.

UWAGA!!!
Usuwamy wszytkie rekordy (nie ma określonej klauzuli WHERE).


DELETE FROM TEST


ID NAZWA
---------------------- --------------------

0 rows selected

INSERT

INSERT - polecenie to służy do dodawania rekordów do tabeli.

Postać instrukcji INSERT jest następująca:

INSERT INTO tabela
VALUES (wartosc1, wartosc2, wartosc3,...)

lub

INSERT INTO tabela (nazwa_kolumny_1, nazwa_kolumny_2, nazwa_kolumny_3,...)
VALUES (wartosc1, wartosc2, wartosc3,...)



UWAGA!
Jeżeli chcemy wprowadzić wartość pustą (null) musimy użyć operacji INSERT określając nazwy kolumy (kolumn).
W poleceniu

INSERT INTO tabela VALUES(wartosci....);
musimy podać wszystkie wartości. W przeciwnym wypadku otrzymamy błąd. (przykład 2, przykład 3)



Przykład 1.

Wyświetlamy zawartość tabeli DEPT, następnie dodajemy rekord pierwszym sposobem.
W dalszej kolejności ponownie wyświetlamy zawartość tabeli, aby sprawdzić efekt działania polecenia INSERT.
Czynność tą powtarzamy dodając rekord drugim sposobem (uwzględniając nazwy kolumn w  poleceniu INSERT).


SELECT * FROM DEPT;

DEPTNO                 DNAME                     LOC                      
---------------------- ------------------------- -------------------------
10                     ACCOUNTING                NEW YORK                 
20                     RESEARCH                  DALLAS                   
30                     SALES                     CHICAGO                  
40                     OPERATIONS                DENVER                   

4 rows selected



INSERT INTO DEPT VALUES('50', 'NOWY WPIS 1', 'LOKALIZACJA 1');
1 rows inserted



SELECT * FROM DEPT;
DEPTNO                 DNAME                     LOC                      
---------------------- ------------------------- -------------------------
10                     ACCOUNTING                NEW YORK                 
20                     RESEARCH                  DALLAS                   
30                     SALES                     CHICAGO                  
40                     OPERATIONS                DENVER                   
50                     NOWY WPIS 1               LOKALIZACJA 1            

5 rows selected




INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES('60', 'NOWY WPIS 2', 'LOKALIZACJA 2');
1 rows inserted




SELECT * FROM DEPT;

DEPTNO                 DNAME                     LOC                      
---------------------- ------------------------- -------------------------
10                     ACCOUNTING                NEW YORK                 
20                     RESEARCH                  DALLAS                   
30                     SALES                     CHICAGO                  
40                     OPERATIONS                DENVER                   
50                     NOWY WPIS 1               LOKALIZACJA 1            
60                     NOWY WPIS 2               LOKALIZACJA 2            

6 rows selected



Przykład 2.


INSERT INTO DEPT (DEPTNO)  VALUES('90');

DEPTNO                 DNAME                     LOC                      
---------------------- ------------------------- -------------------------
10                     ACCOUNTING                NEW YORK                 
20                     RESEARCH                  DALLAS                   
30                     SALES                     CHICAGO                  
40                     OPERATIONS                DENVER                           
90                     (null)                    (null)                               

7 rows selected



Przykład 3.

INSERT INTO DEPT VALUES('100');

Error starting at line 1 in command:
INSERT INTO DEPT  VALUES('100')
Error at Command Line:1 Column:12
Error report:
SQL Error: ORA-00947: not enough values
00947. 00000 -  "not enough values"
*Cause:   
*Action:

Otrzymujemy błąd - not enough values!!!


Błąd jest spowodowany tym, iż struktura tabeli DEPT zawiera 3 kolumny (DEPTNO, DNAME, LOC), a w naszym INSERT została określona tylko jedna wartość.