SQL (Structure Query Language) to język zapytań do baz danych. 

SQL możemy podzielić na kilka części:

• DDL - Data Definition Language - cześć języka SQL, odpowiadające za tworzenie, modyfikacje czy usuwanie obiektów bazy danych, operacje takie jak CREATE, ALTER, DROP.
• DML - Data Manipluation Langugage - zbiór instrukcji języka SQL, pozwalający na wykonanie operacji INSERT, UPDATE,  DELETE (dodawanie, aktualizacja, usuwanie wierszy). 
• DQL - Data Query Language - podstawa pobierania danych, czyli SELECT.
• DCL - Data Control Language - element pozwalające na dodawanie lub odbieranie uprawnień, GRANT / REVOKE.
• DTL / TCL - Data Transaction Language / Transaction Control Language - cześć odpowiedzialna za obsługę transakcji: COMMIT, ROLLBACK , SAVEPOINT.

DBMS - Database Management System - jest zbiorem reguł (system) opisanych przez programy komputerowe, które pozwalają na manipulację (manage) wartości atrybutów kilku różnych typów jednostek uporządkowanych w sensowny sposób (database).

CREATE:

CREATE TABLE sales ( id SERIAL );

ALTER:

ALTER TABLE sales ADD COLUMN sales_amount NUMERIC(10,2);

DROP:

DROP TABLE sales;

DCL (Data Control Language) jest elementem języka zapytań do baz danych, który ma na celu, nadawanie i odbieranie dostępu do obiektów bazy danych.

Do kluczowych elementów składni należą operacje: GRANT / REVOKE (DENY)

• GRANT - nadaję dostępy do obiektów bazodanowych
• REVOKE (DENY) - odbiera dostęp do obiektów bazodanowych

TCL (Transaction Control Language) - jest elementem języka zapytań do baz danych, który ma na celu zarządzanie transakcjami w bazie danych.

Do kluczowych elementów składni należą operacje: COMMIT, ROLLBACK, SAVEPOINT.

• COMMIT - potwierdza transakcje i wszystkie akcje w niej wykonane.
• ROLLBACK - wycofuje transakcje i wszystkie akcje w niej wykonane mające wpływ na struktury bazy danych i samych danych.
• SAVEPOINT - zapisuje stan, do którego można się odwołać wewnątrz transakcji.

Do typowych elementów struktury bazy danych zaliczamy:

• schemat bazy danych
• tabele
• kolumny (atrybuty / pola)
• wiersze (rekordy)
• klucze / ograniczenia (constraints)
• widoki
• indeksy
• procedury / funkcje

Można to łatwo przedstawić na grafice:

• PRIMARY KEY - klucz główny tabeli (relacji) - jeżeli, jest prosty tzn. jednoelementowy, jest ograniczeniem atrybutu. W przypadku złożonego klucza głównego będzie on ograniczeniem relacji.
• NOT NULL / NULL - wartość nie może / może być nieokreślona
• UNIQUE - wartość musi być unikalna w całej relacji
• SERIAL / AUTO_INCREMENT - atrybut jest typu numerycznego z automatycznym wzrostem wartości w polu w momencie wykonywania operacji INSERT
• DEFAULT wartosc - wartość domyślna dla atrybutu
• CHECK warunek - ograniczenie dziedziny atrybutu np. kolumna WIEK z ograniczeniem CHECK > 14, tj. wartość atrybutu WIEK musi być wyższy od 14 w momencie wykonywania operacji INSERT

To nic innego jak metoda wykorzystywana przez aplikację do grupowania, operacji odczytu i zapisu w jedną logiczną jednostkę.

Wynikiem transakcji jest jeden z dwóch stanów: Sukces (transakcja jest zatwierdzana) lub Porażka (transakcja jest anulowana lub wycofywana).

Zerknijmy na klasyczny przykład transakcji: transakcja bankowa wymiany środków pomiędzy dwoma kontami, Kontem A i Kontem B.
Właściciel Konta A chce przelać 100 PLN na Konto B Generalnie mamy w tym przykładzie dwa podejścia.

Podejście 1:

• Operacja 1: Stan Konta A = Stan Konta A - 100 PLN;
• Operacja 2: Stan Konta B = Stan Konta B + 100 PLN;

Podejście 2:

• Operacja 1: Stan Konta B = Stan Konta B + 100 PLN;
• Operacja 2: Stan Konta A = Stan Konta A - 100 PLN;

W rzeczywistości cała operacja jest troszkę bardziej skomplikowana (dochodzą sprawdzenia, reguły biznesowe i prawne banku itp.), jednak idea jest ta sama.

Jeżeli dojdzie do awarii w trakcie wykonywania operacji, mamy problem. Albo 100 PLN wyparuje z Konta A i nie pojawi się na Koncie B, lub pojawi się na Koncie B, ale stan Konta A nie zostanie zmieniony. Z pomocą przychodzi transakcja, która obejmując wykonanie operacji w jednym z Podejść, gwarantuje: wykonanie obu operacji z sukcesem (COMMIT), lub brak zmian w stanach obu Kont (cofnięcie zmian / ROLLBACK)

BEGIN [TRANSACTION]
    StanKontaA = StanKontaA - 100;
    StanKontaB = StanKontaB + 100;
END [TRANSACTION] (COMMIT)

ACID gwarantem bezpieczeństwa w relacyjnych bazach danych. 

Jest to zestaw mechanizmów, który ma zapewnić pewną odporność na błędy i bolączki relacyjnych baz danych.

ACID to akronim od 4 takich gwarantów:

• A (Atomicity) - atomowość
• C (Consistency) - spójność
• I (Isolation) - izolacja
• D (Durability) - trwałość

Kontrola Współbieżności (Concurrency Control) - zbiór funkcji dostarczanych przez bazę danych w celu zezwolenia wielu użytkownikom jednoczesnego dostępu do danych i operacji na nic.

Kontrola Współbieżności Problemy:

• Dirty Read - odczytanie "aktualizowanych" (UPDATE) danych przed ich zaakceptowaniem (COMMIT) przez inną transakcję.

• Phantom Read - pojawienie się nowych rekordów / lub zniknięcie istniejących podczas gdy takie samo zapytanie zostanie uruchomione po wykonaniu 1 zapytania równoważnego.

• Non-Repeatable Read - powoduje pojawienie się rekordów z poprzedniego zapytania w ich zmienionej formie w momencie ponownego wykonania zapytania.

• Lost Update - powoduje nadpisanie oczekującej transakcji inną transakcją zapoczątkowaną wcześniej przez inną transakcję.

LOCK ma miejsce gdy dwie transakcje czekają wzajemnie na zwolnienie zasobów i przynajmniej jedna z nich nie może kontynuować swojego działania.

Izolacja oznacza, że jednocześnie wykonywane transakcje są od siebie odizolowane - nie mogą mieć na siebie wpływu. Jest to szczególnie ważne w sytuacji dostępu do tego samego zasobu (tej samej tabeli), w przypadku dostępu do różnych obiektów konflikt nie będzie występował.

Inna definicja to taka, w której izolacja jest gwarantem, w którym równoległy sposób wykonania transakcji pozostawi bazę danych w takim stanie, jakby operacje zostały wykonane w sposób sekwencyjny.

Warto pamiętać, że jest izolacja to wymiana integralności bazy kosztem wydajności.

Przykład:

Poziomy izolacji transakcji (isolation levels) są standardami / podejściami radzenia sobie z problemami współbieżności transakcji.

• Read Uncommited - najmniej restrykcyjny, dopuszcza 3 typy odczytów (Dirty, Phantom i Non-Repeatable Reads). Raczej nie stosowany w komercyjnych bazach danych.
• Read Commited - pozwala zapytaniu na dostęp do danych, które zostały zaakceptowane (COMMIT). Brak Dirty Reads, zezwolenie na Phantom i Non-Repeatable Reads. Najczęściej stosowany poziom izolacji.
• Repeatable Read - gwarantuje spójność odczytów. Zezwala jedynie na Phantom Read.
• Serializable - najbardziej restrykcyjny poziom izolacji (nie dopuszcza Dirty, Non-Repeatable i Phantom Reads). Każda transakcja jest traktowana, jakby była jedyną transakcją wykonywaną w bazie danych w danym momencie.

Read Commited - dobry podział pomiędzy spójnością a współbieżnością. Dobra dla standardowych aplikacji OLTP (OnLine Transaction Processing) w których istnieje sporo współbieżnych, krótko trwałych transakcji z których rzadko które powodują konflikty. Lepsza wydajność.

Serializable - dobre do baz danych z wieloma spójnymi transakcjami. Mniejsza wydajność z uwagi na większe blokowanie. Używana głównie w bazach danych z transakcjami typu read-only, które pracują przez dłuższy czas (zapytania analityczne, patrz OLAP - OnLine Analytical Processing).

Normalizacja bazy danych to technika organizacji danych w bazie, jest ona systematycznym metodycznym podejście "rozbijania" tabel w celu eliminowania powtórzeń i niechcianych cech operacji dodawania, usuwania i aktualizacji wierszy.

Dwa kluczowe cele normalizacji:

1. Reprezentacja faktów o rzeczywistym świecie w zrozumiały sposób;
2. Ograniczenie nadmiarowego przechowywania faktów i zapobiegania występowaniu nieprawidłowych lub niespójnych danych;

INSERT - problemy przy braku normalizacji

Kolumna miejscowość (CITY) w tabeli CONTACTS, jest dobrym przykładem, żeby rozpatrzeć problemy braku normalizacji w kontekście operacji INSERT.

W przypadku dodawania 100 kontaktów z Poznania wszystkim rekordom przypisujemy taką samą informację tekstową. Z drugiej strony, gdy jakiś kontakt nie podał informacji o miejscowości (nie jest ona wymagana), będziemy musieli wstawić w to pole wartość nieokreśloną NULL.

UPDATE - problemy przy braku normalizacji

Kolumna firma (COMPANY) w tabeli CONTACTS, jest dobrym przykładem, żeby rozpatrzeć problemy braku normalizacji w kontekście operacji UPDATE.

Jeżeli wiele kontaktów, pracuje dla tej samej firmy - ACME - i firma ta zmieni nazwę, lub zniknie z rynku. Wszystkie rekordy będą musiały mieć zaktualizowaną wartość, pominięcie jakiegoś kontaktu i pozostawienie mu wartości ACME, będzie skutkować niespójnością w danych.

DELETE - problemy przy braku normalizacji

Załóżmy, że tabela CONTACTS, to jedyne miejsce, w którym przechowujemy informacje o miejscowości (CITY). Jeżeli z jakiegoś powodu usuniemy wszystkie kontakty z Poznania, nie będziemy w stanie odnieść się do tej wartości w przypadku innych zapytań.

1 Postać Normalna (1NF) - Tabela musi być relacją i nie może zawierać żadnych powtarzających się grup.

2 Postać Normalna (2NF) - tabela jest w 1 postaci normalnej, i posiada dokładnie jeden klucz kandydat (candidate key, który jest tym samym kluczem głównym - primary key) i nie jest on kluczem złożonym (składa się z 1 kolumny).

3 Postać Normalna (3NF) - w 3 postaci normalnej wszystkie atrybuty spoza klucza muszą zależeć od klucza danej tabeli.

3.5 / BCNF Postać Normalna Boyce'a - Codda (BCNF) - w postaci normalnej Boyce'a - Codda wszystkie atrybuty również te należące do klucza muszą zależeć od klucza danej tabeli.

4 Postać Normalna (4NF) - każda tabela, która w założeniu ma reprezentować wiele relacji wiele-do-wielu, narusza reguły czwartej postaci normalnej.

5 Postać Normalna (5NF) - każda tabela, która spełnia kryteria postaci normalnej Boyce'a - Codda i nie zawiera złożonego klucza głównego, znajduje się w piątej postaci normalnej.

5.5 Postać Normalna DKNF (Domain-Key Normal Form) (DKNF) - każde ograniczenie w tabeli musi być logiczną konsekwencją ograniczeń dziedziny tabeli i ograniczeń kluczy.

6 Postać Normalna (6NF) - tabela jest w 6 postaci normalnej jeżeli oprócz atrybutu, który jest jednocześnie kluczem głównym, posiada maksymalnie 1 dodatkowy atrybut.

Widok jest trwałą definicją tabeli pochodnej (wynikiem zapytania SELECT), która jest przechowywana w bazie danych.

W SQL tabela, którą utworzono za pomocą zapytania CREATE TABLE, nazywana jest tabelą bazową (base table). Tabelą pochodną (derived table) formalnie nazywamy wynik każdego zapytania SELECT. Tabele, których użyto do zdefiniowania widoku, są nazywane underlying tables.

Składnia:

CREATE OR REPLACE VIEW view_name AS SELECT ...

DROP VIEW view_name;

Właściwości widoku:

• nie przechowuje danych - definicja widoku jest zapisana w bazie danych. Sam widok jest jedynie interfejsem odczytu danych;
• zawsze pokazuje aktualne dane - baza danych odtwarza dane za każdym razem aktualne z tabel źródłowych użytych do zbudowania widoku;

Widoki są używane do:

• utworzenia dodatkowego poziomu zabezpieczenia tabeli poprzez ograniczenie dostępu do określonych kolumn lub wierszy tabeli bazowej;
• ukrycia złożoności danych - gdy definicja widoku jest złożonym zapytaniem SELECT, samo odpytanie widoku jest widoczne jako proste zapytanie;
• pokazywania danych z innej perspektywy - dla przykładu widok może zostać użyty do zmiany nazwy kolumny bez zmiany rzeczywistych danych zapisanych w tabeli;

Widok zmaterializowany w odróżnieniu od widoku jest definicją zapytania SELECT, która w momencie stworzenia (i z określoną częstotliwością) zapisuje wyniki zapytania do obiektu bazodanowego, tabeli.

Dane z defnicji widoku zmaterializowanego odświeżają się według ustalonego harmonogramu (ustawianego w defnicji widoku zmaterializowanego).

Co do zasady w przypadku skomplikowanych zapytań w momencie odpytania zwykłego widoku, całe skomplikowane zapytanie musi zostać wykonane. W przypadku widoku zmaterializowanego, dane są od razu dostępne z aktualnością równą ostatniemu odświeżeniu widoku według harmonogramu.

OLTP - OnLine Transaction Processing - to system zorientowany na krótkotrwałe, szybie transakcje. 

• źródło danych: dane operacyjne; systemy OLTP są oryginalnie źródłem danych;
• cel danych: obsługa podstawowych procesów biznesowych i ich kontrola;
• co jest w danych: stan bieżący procesów biznesowych;
• operacje Insert / Update: krótkie i szybkie operacje Insert / Update inicjowane przez użytkowników końcowych;
• zapytania: relatywnie proste i standardowe zapytania zwykle zwracające kilka rekordów;
• czas procesowania: zwykle bardzo szybki;
• potrzebne miejsce: dane mogą zajmować relatywnie mało miejsca jeżeli dane historyczne są archiwizowane;
• struktura bazy danych: wysoce znormalizowana z dużą ilością tabel;
• kopia i odzyskiwanie danych: obowiązkowe robienie kopii danych; dane operacyjne są krytyczne do prowadzenia biznesu, utrata danych zwykle pociąga za sobą duże straty finansowe i odpowiedzialność prawną;

Głównym celem jest przetwarzanie danych. 

Typowe przykłady to w zasadzie większość produkcyjnych systemów wspomagania pracę przedsiębiorstwa - systemy ERP / CRM (z wyjątkiem modułów analitycznych). 

Inne Przykłady: system bankowy obsługujący odczytywanie i modyfikację salda rachunków klientów; systemy finansowo-księgowe.

OLAP - OnLine Analytical Processing - to systemy zorientowane na dostarczenie modelu analitycznego, tj. takie przygotowanie danych i optymalizacja modelu, żeby procesy analityczne był priorytetowe. 

• źródło danych: dane skonsolidowane; dane źródłowe systemów OLAP pochodzą z różnych baz / źródeł danych;
• cel danych: pomoc w planowaniu, rozwiązywaniu problemów i procesie podejmowania strategicznych decyzji;
• co jest w danych: wielowymiarowe spojrzenie na różnego rodzaju aktywności biznesowe, stan obecny i historia;
• operacje Insert / Update: cykliczne, długo trwające odświeżanie danych zwykle przy pomocy plików wsadowych;
• zapytania: zapytania często bardzo złożone wymagające agregacji;
• czas procesowania: zależy od ilości procesowanych danych;
• potrzebne miejsce: potrzebna duża ilość miejsca ze względu na istnienie danych zagregowanych i historycznych;
• struktura bazy danych: zwykle zdenormalizowana z niewielką ilością tabel; używane schematy gwiazdy i / lub płatka śniegu;
• kopia i odzyskiwanie danych: dodatkowo do kopii zapasowych można rozważyć ponowne załadowanie danych z systemów źródłowych jako metodę odzyskiwania danych;

Głównym celem jest analiza danych.

Typowym przykładem są hurtownie danych, lub ogólnie systemy stworzone i zoptymalizowane pod kątem procesów analitycznych.

Funkcje agregujące w SQL, to grupa funkcji, które jak wskazuje nazwa, służą do otrzymania wyniku operacji dla grupy rekordów, według wybranego klucza (kluczy) grupy. 

Przykładem, może być wartość sprzedaży w podziale na kategorie produktów.

Najczęściej wykorzystywane funkcje agregujące:

• SUM() - sumowanie
• AVG() - średnia
• COUNT() - zliczanie wystąpień
• MIN() / MAX() - minimum / maksimum
• STDEV() - odchylenie standardowe

Funkcje analityczne są specyficznym rodzaje funkcji SQL-owe, które dają wynik dla danej wiersza w kontekście wierszy sąsiednich (poprzedzających / następujących) po wierszu. Ten rodzaj funkcji wykorzystujemy np. do "rankingu" danych czy wyliczania stanów (balance).

Najczęściej wykorzystywane funkcje analityczne:

• ROW_NUMBER() OVER()
• DENSE_RANK() / RANK () OVER()
• SUM() OVER()
• LEAD() / LAG() OVER()

W momencie gdy nie są one użyte w kontekście grupowania (GROUP BY), są one w zasadzie równoważne.

Jednak prawdziwe zastosowanie mają właśnie w momencie grupowania danych.

• Warunek WHERE odfiltrowuje rekordy z wyniku przed wykonaniem grupowania.
• Warunek HAVING odfiltrowuje rekordy z wyniku po wykonaniu grupowania

Obie komendy służą do usuwania wierszy z tabeli, ale są między nimi pewne subtelne różnice.

DELETE jest operacją DML-ową (Data Manipulation Language) działa na poziomie wiersza tabeli. Z jej pomocą możemy usunąć wszystkie wierszy lub jeden konkretny (dokładając WHERE).

DELETE FROM <TABLE NAME> WHERE …

TRUNCATE jest operacją DDL-ową (Data Definition Language) działa na poziomie całej tabeli. Jeżeli chcesz usunąć wszystkie wiersze z tabeli (a dodatkowo Twoja tabela nie ma kluczy obcych), to prawdopodobnie to będzie komenda, której szukasz.

TRUNCATE TABLE <TABLE NAME> …

Trochę o detalach obu komend.

Transakcyjność

• DELETE podobnie jak INSERT czy UPDATE może zostać zaakceptowany (COMMIT) lub cofnięty (ROLLBACK).
• TRUNCATE w części systemów baz danych ma w sobie tzw. „niejawny COMMIT”. To znaczy, że operacja COMMIT jest wykonywana przed i po wykonaniu operacji TRUNCATE bez konieczności dodatkowego wykonywania COMMIT-a.
  
  Uwaga: Powyższe jest prawdą w przypadku np. Oracle-a, w przypadku SQL Server-a lub PostgreSQL-a operacja TRUNCATE może być wycofana (ROLLBACK).

Odzyskiwanie Miejsca 

• DELETE nie odzyskuje miejsca po usunięciu wierszy. Potrzebna jest dodatkowa operacja VACUUM.
• TRUNCATE odzyskuje miejsce po usunięciu wierszy.
  

Lockowanie

• DELETE blokuje konkretne wiersze tabeli, które są usuwane.
• TRUNCATE blokuje całą tabelę.

Wyzwalacze / Triggers

• DELETE wykonuje operacje wyzwalaczy AFTER / BEFORE DELETE.
• TRUNCATE nie wykonuje wyzwalaczy.

Pomiędzy TRUNCATE-em a DELETE-em jest jeszcze kilka różnic, które są związane z ich obsługą pomiędzy bazami danych np. kwestie indeksów, kluczy obcych, kolumn typu IDENTITY.

Jednak są to już dość specyficzne kwestie, jak chcesz zgłębić temat, odwołałbym się do konkretnych dokumentacji systemów, na których pracujesz.

Gdy pomiędzy tabelami istnieje zdefiniowana relacja klucza obcego, oraz istnieją powiązane z tą relacją rekordy, wykonanie operacji DELETE na danych tabeli, do której jest stworzone powiązanie, nie będzie możliwe.

Baza danych poinformuje nas o tym, że w relacji pomiędzy tabelą A i B, istnieją rekordy, które chcemy usunąć, żeby wykonać tę operację,  najpierw musimy usunąć rekordy powiązane z tabeli B lub skorzystać z opcji CASCADE.

Opcja CASCADE usuwa wszystkie wiersze z tabeli A oraz wszystkie powiązane wierszy z tabeli B, które łączy relacja klucza obcego pomiędzy tabelami.

Gdy pomiędzy obiektami bazy danych istnieje relacja, tzn. obiekt B zależy od obiektu A, np. widok B od tabeli A, wykonanie operacji DROP na obiekcie A, nie będzie możliwe.

Baza danych poinformuje nas o tym, że w relacji pomiędzy obiektem A i B, istnieje powiązanie. Jeżeli chcemy usunąć obiekt A, należy wcześniej usunąć obiekt B lub skorzystać z opcji CASCADE.

Opcja CASCADE usuwa obiekt zdefiniowany do usunięcia oraz wszystkie obiekty, którego korzystają / są stworzone na podstawie obiektu A.

Funkcje CEIL oraz FLOOR pozwalają na zaokrąglenie danej liczby do części całkowitej. 

Pamiętasz pewnie z matematyki kwestie zaokrąglania. Mając liczbę 254 gdy chcemy zaokrąglić ją do pełnych dziesiątek, dostaniemy w wyniku liczbę 250. Gdy mamy np. 255 to dostaniemy 260. 

CEIL (lub CEILING) oraz FLOOR. W “wolnym” tłumaczeniu sufit i podłoga i już się domyślasz co do czego.

Funkcja CEIL (CEILING) zwraca najmniejszą liczbę całkowitą większą lub równą, względem podanej na wejściu wartości.

SELECT 8.28 as wartosc_wyjsciowa, 

               CEILING(8.28) as wartosc_pozytywna, 

               CEILING(-8.28) as wartosc_negatywna;

Wynik:

wartosc_wyjsciowa: 8.28

wartosc_pozytywna: 9

wartosc_negatywna: -8

Funkcja FLOOR zwraca największą liczbę całkowitą mniejszą lub równą, względem podanej na wejściu wartości.

SELECT 8.28 as wartosc_wyjsciowa, 

               FLOOR(8.28) as wartosc_pozytywna, 

               FLOOR(-8.28) as wartosc_negatywna;

Wynik:

wartosc_wyjsciowa: 8.28

wartosc_pozytywna: 8

wartosc_negatywna: -9

Funkcja ROUND podobnie jak CEIL i FLOOR pozwala na zaokrąglenie wartości. Jednak zaokrąglenie bardziej "po matematycznemu” (czyli jak 5 to w górę a jak mniej niż 5 to w dół). 

Dodatkowo przy tej funkcji możesz doprecyzować, do ilu miejsc po przecinku, wartość wyjściowa ma zostać zaokrąglona. 

SELECT 8.49 as wartosc_wyjsciowa, 

               FLOOR(8.28) as wartosc_floor, 

               CEIL(8.28) as wartosc_ceil,

               ROUND(8.49) as wartosc_zaokraglona

wartosc_wyjsciowa: 8.49

wartosc_floor: 8

wartosc_ceil: 9

wartosc_zaokraglona: 8 (niepodane zostało miejsce zaokrągleń po przecinku, domyślnie 0 miejsc po przecinku)

SELECT 8.51 as wartosc_wyjsciowa, 

               FLOOR(8.51) as wartosc_floor, 

               CEIL(8.51) as wartosc_ceil,

               ROUND(8.51) as wartosc_zaokraglona

wartosc_wyjsciowa: 8.49

wartosc_floor: 8

wartosc_ceil: 9

wartosc_zaokraglona: 9 (niepodane zostało miejsce zaokrągleń po przecinku, domyślnie 0 miejsc po przecinku)

UWAGA:

Jakiego typu jest wartość zwracana przez daną funkcję (CEIL / FLOOR / ROUND)?

To zależy od silnika bazodanowego, który wykorzystujesz.

W przypadku PostgreSQL zwracane typy dla funkcji to:

• CEIL - taki sam typ jak typ wejściowy
• FLOOR - taki sam typ jak typ wejściowy
• ROUND - typ numeryczny

Zerknij do dokumentacji silnika, z którego korzystasz na co dzień, żeby mieć pewność.

Operator ANY, służy do sprawdzenia, czy w przeszukiwanym podzbiorze danych (podzapytaniu) znajduje się przynajmniej jedna wartość spełniająca wykorzystany operator. Możesz go użyć w sekcji WHERE oraz / lub w sekcji HAVING.

Składnia:

operator - to jeden z dostępnych operatorów logicznych =, <>, !=, >, >=, <, or <=

Przykład:

Wyświetlenie diet, wśród których dla posiłków jednym ze składników jest owoc pomarańczy.

Uwaga:

Z operatorem ANY wiąże się również operator SOME, który uwaga jest takim samym operatorem jak ANY.

Co do zasady działania operatory są takie same. Pierwszy standard SQL wszedł w życie w 1986, a sam SQL swoje początki datuje na lata 1970+, stąd 2 operatory, być może jakaś wsteczna kompatybilność?

Operator ALL, służy do sprawdzenia, czy wszystkie wiersze przeszukiwanego podzbioru danych (podzapytania) spełniają wykorzystany operator. Możesz go użyć w sekcji WHERE oraz / lub w sekcji HAVING.

Składnia:

operator - to jeden z dostępnych operatorów logicznych =, <>, !=, >, >=, <, or <=

Przykład:

Wyświetlenie diet, wśród których dla posiłków jednym ze składników nie jest arbuz.

Uwaga:

Z operatorem ANY wiąże się również operator SOME, który uwaga jest takim samym operatorem jak ANY.

Co do zasady działania operatory są takie same. Pierwszy standard SQL wszedł w życie w 1986, a sam SQL swoje początki datuje na lata 1970+, stąd 2 operatory, być może jakaś wsteczna kompatybilność?

Operator EXISTS, służy do wyszukiwania wierszy, które spełniają warunek użyty w podzapytaniu. Enigmatyczny opis, a sprowadza się do tego, że w wyniku dostaniesz rekordy istniejące w podzapytaniu użytym do zbudowania EXIST-a. Operator EXISTS zwraca TRUE, gdy podzapytanie zwróci 1 lub więcej rekordów.

Składnia:

Dlaczego nie JOIN?
JOIN według swojej definicji, służy do łączenia tabel i wyświetlania w wyniku kolumn z różnych obiektów (tabel). EXISTS ma za zadanie "odfiltrować" finalny wynik na podstawie jakiegoś podzapytania.

Przykład:

Wyświetl wszystkie produkty (tabela PRODUCTS), które wzięły udział w transakcji sprzedażowej (tabel SALES)

Co EXISTS ma wspólnego z ANY / SOME.

Załóżmy, że chcemy wyświetlić tylko produkty, dla których istnieje transakcja sprzedażowa. Możemy to zrobić za pomocą EXIST-a, ale również za pomocą ANY.

Jaka jest wiec różnica?
Plan zapytania jest identyczny w obu przypadkach. Jeżeli warunek logiczny jest ten sam, ANY / SOME i EXISTS są zamienne.

Prawdopodobnie znasz z relacyjnej bazy danych, na której pracujesz na co dzień składnie:

• LIMIT (m.in. MySQL, PostgreSQL, SQLite, H2)
• TOP (m.in. MS SQL Server)
• ROWNUM (Oracle) - pseudo kolumna w bazie Oracle, tworzona w momencie generowania wyników określająca kolejność pobranych wierszy

Przykłady:

    SELECT * FROM tab LIMIT 10;

    SELECT * FROM tab WHERE ROWNUM <= 10;

    SELECT TOP 10 * FROM tab;

Dlaczego, pomiędzy bazami danych są różnice, w takim wydawałoby się prostym elemencie?

Poniekąd byli do tego zmuszeni. Pierwszy raz taka funkcjonalność została wprowadzona do standardu razem z wersją SQL:2008.

FETCH FIRST n ROWS ONLY bo o niej mowa, pozwala na pobranie jedynie n (gdzie n to liczba lub wynik operacji np. (2+2)) wierszy z całego zbioru wynikowego.

Przykład:

    SELECT * FROM tab FETCH FIRST 10 ROWS ONLY;

Instrukcja warunkowa (IF) to popularna konstrukcja w językach programowania, pozwalająca na sprawdzenie warunku i wykonanie odpowiedniej akcji w zależności od tego czy warunek jest spełniony, czy też nie.

Składnia (podstawowa - w językach programowania - jako pseudokod)

IF <WARUNEK LOGICZNY>

THEN  <AKCJA DO WYKONANIA JAK WARUNEK JEST SPEŁNIONY>

ELSE  <AKCJA DO WYKONANIA JAK WARUNEK JEST NIESPEŁNIONY>

END 

Warunkiem logicznym może być dowolna funkcja / operator, który w wyniku da wartość TRUE lub FALSE. 

Konstrukcja jak powyżej w standardowym SQL-u nie istnieje. Istnieje natomiast w Proceduralnym SQL, tj. w procedurach składowanych i funkcjach wykonywanych w bazie danych.

 IF w SQL-u istnieje, kryje się pod operatorem CASE

• warunek - to dowolna operacja, której wynikiem jest wartość logiczna TRUE / FALSE (operator logiczny >, <, =, <> etc.; wynik z podzapytania TRUE / FALSE itp.)
• akcja - czynność która ma zostać wykonana po spełnieniu warunku np. wyświetlenie jakiejś kolumny, dodanie czegoś do wartości etc.

W operatorze CASE warunki są sprawdzane po kolei, po napotkaniu pierwszego warunku, który jest spełniony, wykonywana jest akcja, pozostałe warunki są pomijane. Jeżeli żaden z warunków nie jest spełniony, wykonywana jest akcja z sekcji ELSE. 

Jeżeli ELSE nie został zadeklarowany, zwrócona zostanie wartość nieokreślona - NULL.

Przykład:

Operator CASE możesz wykorzystać w samej składni SELECT, w WHERE, w ORDER BY, GROUP BY etc. Jednak taki operator to zawsze dodatkowy narzut w momencie wykonania (na wykonanie instrukcji sprawdzenia + wykonanie akcji), co będzie miało wpływ na wydajność. W niektórych przypadkach warto rozważyć wyliczanie kolumn z operatorem CASE wcześniej (jako element w procesie poprzedzającym wykorzystanie), dodając odpowiednie indeksowanie i następnie wykorzystując wyliczoną kolumnę w SELECT-ie. 

Uwaga: W niektórych baza danych IF istnieje np. MySQL, lub jako IIF - Firebird / SQL Server. Jednak nie jest standardem SQL a nakładką na konkretnej bazie danych.

Operacja ORDER BY służy do sortowania danych wynikowych. 

Czy wyniki mają jakiekolwiek sortowanie bez ORDER BY.

Nie. W przypadku niepodania klauzuli ORDER BY wyniki zapytania zostaną zwrócone w nieokreślonej formie. Może się zdarzyć, że pojawią się w tej samej kolejności, jak zostały dodane do tabeli źródłowej, ale uważaj i nie bierz tego za pewnik.

Chcesz otrzymać wiersze w żądanej kolejności, używaj ORDER BY.

Składnia

SELECT …
FROM …
ORDER BY < NAZWA_KOLUMNY/FUNKCJI> < DESC | ASC>
< NULLS FIRST | NULLS LAST >

• NAZWA_KOLUMNY/FUNKCJI - nazwa / nazwy kolumn lub funkcji, po których ma zostać wykonane sortowanie
• DESC - sortuj w porządku malejącym
• ASC - sortuj w porządku rosnącym
• NULLS FIRST - pokazuj wartości nieokreślone na początku zbioru wynikowego
• NULLS LAST - pokazuj wartości nieokreślone na końcu zbioru wynikowego

Porządek sortowania DESC / ASC możesz określić dla każdej kolumny / funkcji użytej przy sortowaniu osobno. Domyślnym porządkiem sortowania jest sortowanie rosnące - ASC.

Elementy użyte w składni ORDER BY nie muszą być w części SELECT.

ORDER BY jest jednym z ostatnich logicznych elementów, które zostaną wykonane. Dlatego przy sortowaniu możemy sobie pozwolić na używanie aliasów - zamiast rzeczywistych nazw kolumn lub kolejności pól zamiast ich nazw (np. ORDER BY 1, 2, 3, gdzie 1, 2, 3 to kolejne kolumny po składni SELECT)

Złączenie to powiązanie tabel (lub tabeli), na podstawie pewnego warunku (tych samych kolumn, operatorów logicznych >, < etc.).

W najprostszym przypadku złączenia używamy do odwzorowania relacji pomiędzy tabelami, gdy chcemy dostać określony wynik.

Rodzaje złączeń:

• (INNER) JOIN
• LEFT (OUTER) JOIN
• RIGHT (OUTER) JOIN
• FULL (OUTER) JOIN

(INNER) / (OUTER) w nawiasach oznaczają fakt, że ten fragment jest opcjonalny dla SQL tzn. LEFT OUTER JOIN = LEFT JOIN.

UNION - jest operacją łączenia zbiorów danych. Możesz jej użyć, do połączenia rekordów z różnych tabel w 1 zbiór rekordów.

Po co? Zwykle, gdy potrzebujesz zbudować listę wartości z różnych zbiorów (tabel).

Przykład:

Limity

• ilość kolumn w obu zapytaniach musi być identyczna
• typy danych muszą być kompatybilne w odpowiadających sobie kolumnach - (w przykładzie powyżej car_manufacturer nie może być raz wartością numeryczną, a raz tekstową)
• w celu uzyskania posortowanego rezultatu użyj ORDER BY - możesz go użyć tylko w ostatnim SELECT-cie. ORDER BY zadziała na całym zbiorze wynikowym. Jeżeli chcesz otrzymać posortowane wyniki tylko z pierwszego zapytania, zastosuj podzapytanie dla 1 SELECT-a
  

UNION vs UNION ALL

• UNION - złączy wyniki usuwając przy tym powielające się wiersze (taki DISTINCT)
• UNION ALL - złączy wyniki nie usuwając powielających się wierszy

Różnica jest znacząca i będzie zauważalna szczególnie na większych zbiorach danych. Operacja usuwania duplikatów jest kosztowna, dlatego warto przed użyciem operacji UNION sprawdzić i zweryfikować czy istnieje możliwość pojawienia się tych samych wartości w złączanych zbiorach.

Uwaga:

W niektórych bazach danych UNION oprócz usuwania duplikatów sortuje również wynikowy zbiór danych (np. SQL Server).

EQUI JOIN - dowolny typ złączenia, w którym w kluczu złączenia używamy znaku =.

NON-EQUI JOIN - dowolny typ złączenia, w którym w kluczu złączenia nie używamy znaku = (ale np. <. >, >=, =<, !=).

SELF JOIN - typ złączenia, w którym łączymy między sobą ten sam zbiór danych / tabele.

Wewnętrzne czy czasami nazywane fizycznymi typami złączeń, lub ogólniej algorytmy złączenia. To złączenia, które są wykorzystywane przez silnik bazy danych do faktycznego łączenia danych (abstrahując od tego, co użytkownik użył w zapytaniu LEFT / INNER JOIN).

Trzy podstawowe typy:

• NESTED LOOP
• MERGE JOIN
• HASH JOIN

Mając dwa zbiory do złączenia N i M, w najprostszym (najgorszym) scenariuszu, każdy rekord ze zbioru N, musi zostać sprawdzony względem rekordów w zbiorze M.

Stąd nazwa "nested loop", przechodzimy przez elementy zbioru M, iterując elementy zbioru N.

W zależności od sytuacji złożoność tego algorytmu może być:

• O(N*M) gdy na kluczu złączenia zbiorów nie mamy indeksu
• O(M*logN) gdy na jednym z kluczy złączenia mamy założony indeks typu B-Tree
• O(M) gdy na jednym z kluczy złączenia mamy założony hash indeks.

Zwykle wykorzystywany gdy jeden ze zbiorów złączenia jest "zdecydowanie" mniejszy od drugiego.

Przykład na podstawie tabel PRODUCTS i SALES:

Chcąc złączyć dwa zbiory N i M. Algorytm MERGE JOIN zostanie wykorzystany w momencie gdy zbiory użyte w złączenie są posortowane według klucza złączenia i zostanie wykorzystany operator równości w kluczu złączenia.

Co to oznacza w praktyce? Gdy mamy posortowane dwa zbiory sprawdzanie równości jest bardzo uproszczone.

Patrząc na zbiór N i mając wartość 1, szukamy tej wartości w posortowanym zbiorze M. Bierzemy pierwszy element czy jest on równy 1, nie, czy jest on większy, tak - nie sprawdzamy kolejnych elementów. Dalej bierzemy kolejny rekord ze zbioru N. Taka operacja powtarza się, dopóki wszystkie elementy z jednego ze zbiorów nie zostaną sprawdzone (Dlaczego z jednego? Skoro zbiory są posortowane i przeszukaliśmy wszystko ze zbioru np. N to zbiór M, już nas niczym nowym nie zaskoczy, bo niesprawdzone wartości i tak nie spełnią warunków złączenia).

Złożoność tego algorytmu to O(N+M).

Świetny przy złączeniach "dużych" tabel, gdy oba zbiory są posortowane według klucza złączenia i warunku złączenia użyty jest operator równości "=".

Przykład na podstawie tabel PRODUCTS i SALES:

Chcąc złączyć dwa zbiory N i M, gdy wykorzystany zostanie algorytmy HASH JOIN.

Baza danych dla mniejszego zbioru, wyznaczy skróty (hash-e) dla kluczy złączenia i zbuduję tabelę skrótów (połączenie wyznaczonego skrótu z rzeczywistym rekordem tabeli N).

Następnie w pętli dla każdego rekordu z tabeli M, dla klucza złączenia zostanie wyznaczony skrót (hash) i porównany ze zbudowaną tabelą skrótów.

Tabela skrótów zwykle jest przechowywana w pamięci, jednak gdy zbiór danych jest bardzo dużo, ilość miejsca w pamięci może się okazać nie wystarczająco, przez co część tabeli skrótów, zostanie zrzucona na dysk i stamtąd będzie odczytywana (co znacznie spowolni odczyt / złączenie).

Złożoność obliczeniowa tego algorytmu przy założeniu, że tabela skrótów zmieści się w pamięci i dodatkowe zasoby nie będą wykorzystane to: O(N+M).

Przykład na podstawie tabel PRODUCTS i SALES:

Od magii wykorzystywanej przez Ciebie bazy danych.

A tak serio to od wielu, możemy do nich zaliczyć:

• wielkości zbiorów danych użytych w złączeniu
• operatora klucza złączenia (czy jest to równość "=", czy inny operator)
• od tego czy na kluczach złączenia są założonego indeksy
• czy statystyki tabel użytych w złączeniu są aktualne (przeliczone)

Nie ma niestety, jednej doskonałej odpowiedzi na to, który typ złączenia jest najlepszy. Można oczywiście wskazać te z lepszą złożonością obliczeń, jednak w rzeczywistym świecie, każdy przypadek należy rozpatrywać osobno.

Warto analizować plan wykonania zapytania (EXPLAIN / EXPLAIN ANALYZE) i momentami poddawać w wątpliwość przedstawiony przez optymalizator bazy danych sposób łączenia zbiorów.

Duplikatem w danych nazywamy przynajmniej 2 takie wiersze wynikowe, dla których wszystkie kolumny mają takie same wartości.

Przykład:
PRODUCT_NAME | PRODUCT_CATEGORY
       Product 1        |           Category 1
       Product 1        |           Category 1

NULL to swego rodzaju znacznik w języku SQL i w relacyjnych bazach danych, wskazujący na fakt, że dana wartość jest nieokreślona (nie ma wartości w danych źródłowych).

Uwaga: NULL nie jest 0 dla liczb czy pustym tekstem dla wartości tekstowych.

Do sposobów radzenia sobie z NULL-em możemy zaliczyć:

• porównania warunkowe IS NULL / IS NOT NULL
• COALESCE() - funkcja, która w przypadku wystąpienia wartości NULL, zamieni ją na podaną wartość np. COALESCE(product_name, 'unknown_product');
• CASE - składnia pozwalająca na zbudowanie warunkowego podejścia do wartości kolumny / funkcji CASE WHEN product_name IS NULL THEN 'unknown_product' ELSE product_name END

Zwróć uwagę, że pomimo faktu, iż COALESCE czy CASE są elementu standardu ISO dla SQL-a, nie muszą być wspierane przez wszystkie bazy danych.

Są też inne alternatywy np. NULLIF(), IFNULL(), ISNULL(), NVL() - których zadaniem jest spełnianie tej samej lub podobnej funkcji tj. uporanie się z wartością nieokreśloną.

Dodanie ograniczenia (CONSTRAINT) typu NOT NULL z dodatkową wartością domyślną DEFAULT ''.

Uwaga: nie zawsze musi to być pożądane rozwiązanie. Czasem wartości NULL są jak naj bardziej OK i takie ograniczenie pozwoli nam sobie z nimi radzić w tabeli, ale przy budowaniu SELECT-a ze złączeniami i tak możemy otrzymać wartości nieokreślone.

Inne podejście

Typowy błąd: DISTINCT poza COUNT-em nie uwzględni zduplikowanych wierszy wynikowych, pokazując tylko product_name nie widzimy całości wiersza. COUNT jest robiony na całym zbiorze wynikowym.

Wykorzystanie funkcji okna (WINDOW FUNCTION - OVER()) + iteratora wiersza row_number(). 

Lub z wykorzystaniem składni EXCEPT (MINUS w niektórych bazach danych).

Lub z wykorzystaniem składni CASE WHEN THEN

ORDER BY 2 jak najbardziej zadziała w momencie gdy w zbiorze wynikowym są co najmniej 2 kolumny wtedy zapytanie użyje 2 kolumny do wykonania sortowania.

Natomiast w przykładzie powyżej, baza danych zwróci błąd, gdyż w zbiorze wynikowym nie ma 2 elementu.

Składnia ORDER BY jest ostatnim elementem "logicznego" planu wykonania zapytania. Stąd gdy w składni SELECT korzystamy z aliasów dla kolumn (bo np. są to kolumny zbudowane na funkcjach) możemy z nich tychże aliasów skorzystać w ORDER BY.

Inna ciekawostka, kolejność kolumn wymieniona w części SELECT również może posłużyć w sekcji ORDER BY. Zamiast używać aliasu lub nazwy drugiej kolumny, można użyć miejsca jej wystąpienia w SELECT, czyli cyfry 2.

Tak i Nie 🙂 

Domyślnie ORDER BY służy do sortowania całego zbioru wynikowego, więc będzie on ostatnim element składni SELECT po UNION czy UNION ALL.

Można jednak osobno sortować zbiory użyte w UNION / UNION ALL korzystając z podzapytań (czyli oba zbiory traktować jako podzapytania) lub wstawiając sztuczną kolumnę i sortując początkowo po niej.

Przy podejściu ze sztuczną kolumną uważaj na wyniki, UNION, które przy okazji złączenia eliminuje wiersze o tych samych wartościach nie będzie miał zastosowania bo sztuczna kolumna zawsze będzie różna w obrębie zbiorów (czyli na koniec dodatkowo podzapytanie + distinct).