Podstawowe pojęcia.

Relacyjna baza danych to zbiór danych o ściśle określonej strukturze. Dane przechowywane są w wielu odrębnych, ale powiązanych ze sobą tabelach.

Tabele w bazie AdventureWorks2012

Tabela to dwuwymiarowy obiekt zbudowany z pionowych kolumn i poziomych wierszy. Na ich przecięciu znajdują się komórki.

W kolumnach tabeli powinny znajdować się dane tego samego typu np. w kolumnie Nazwisko powinny być przechowywane nazwiska.

W wierszach (rekordach) znajduje się komplet informacji o konkretnych obiektach np. o kliencie. Każdy rekord tabeli składa się z takiej samej liczby pól (kolumn).

Kolumny w przykładowej tabeli Person.PersonPhone w bazie AdventureWorks2012

Przykładowa tabela

Informacje w tabeli Person.PersonPhone

Kolumny w tabeli

Przykłady kodu prezentowane w artykule możesz uruchomić w bazie AdventureWorks2012 lub Northwind.

Typy danych

Dane mogą reprezentować m.in. takie elementy jak imiona, nazwiska, opisy, liczby, obrazy, dokumenty.

Typy danych:

  • mogą obejmować dane tekstowe (ciągi znaków), liczby, daty i godziny, obrazy (*.jpg) i inne dane binarne (*.zip)
  • określają sposób przechowywania danych – decydują, czy w danej kolumnie mogą być zapisywane dane alfanumeryczne, numeryczne, daty i czasu.

Każda implementacja SQL może mieć swoje własne, niestandardowe typy danych oraz własne nazwy dla standardowych typów danych.

Ciągi znaków i daty muszą być ujęte w znaki apostrofu, a ciągi reprezentujące wartości numeryczne nie powinny być wpisywane w apostrofach.

SELECT *
FROM Person.PersonPhone
   WHERE PhoneNumberTypeID = 1 
   AND  PhoneNumber = '612-555-0100'
   AND ModifiedDate = '20071128';

Datę należy podawać w formacie „yyyyMMdd” np. „20071128”. Format ten jest właściwie interpretowany przez SQL Server niezależnie od ustawień regionalnych komputera.

Podstawowe typy danych

SQL podstawowe typy danych

Operatory

W języku SQL używamy operatorów:

  • arytmetycznych – dodawanie, odejmowanie, mnożenie, dzielenie, dzielenie modulo
  • porównania – równe, większe niż, mniejsze niż, większe lub równe, mniejsze lub równe, różne
  • SQL – IN, BETWEEN …AND, LIKE, IS NULL
  • logiczne – AND, OR, NOT

Operatorów używamy najczęściej w klauzulach WHERE i HAVING instrukcji SELECT.

Logika trójwartościowa

W serwerach bazodanowych występuje wartość specjalna NULL. Reprezentuje ona brak danych w polu tabeli.

Ze względu na występowanie wartości specjalnej NULL, w SQL obowiązuje logika trójwartościowa. Wynikiem wyrażenia logicznego może być wartość TRUE, FALSE lub UNKNOWN.

Przykład:
1 + 1 = 2 –> TRUE
1 + 1 = 3 –> FALSE
1 + NULL = UNKNOWN –> UNKNOWN

Więcej informacji na temat operatorów i logiki trójwartościowej znajdziesz w artykule pod linkiem MS SQL – operatory

Odczytywanie danych – instrukcja SELECT

Poniżej zapytanie SQL, za pomocą którego możesz:

  • odczytać z bazy dane z dwóch tabel
  • ograniczyć liczbę wybieranych rekordów
  • pogrupować wybrane rekordy
  • ograniczyć liczbę grup
  • posortować wybrane grupy
SELECT LastName, COUNT(*)
FROM [Person].[EmailAddress] a
   JOIN Person.Person b
   ON a.BusinessEntityID = b.BusinessEntityID
WHERE a.BusinessEntityID < 100
GROUP BY LastName
HAVING COUNT(*) > 2
ORDER BY LastName;

SELECT – wskazujemy kolumny, które chcemy otrzymać w wyniku
FROM – wskazujemy jedną lub więcej tabel, z których dane mają zostać odczytane
JOIN – wskazujemy nazwę tabeli, którą chcemy dołączyć
ON – wskazujemy kolumny, według których będziemy dokonywać złączenia
WHERE – filtr dla wybieranych wierszy
GROUP BY – wskazujemy kolumny, według których ma się odbywać grupowanie
HAVING – filtr dla wybieranych grup
ORDER BY – wskazujemy kolumny, według których ma się odbywać sortowanie

Uwaga! Kolejność klazul w każdym zapytaniu musi być dokładnie taka, jak w powyższym przykładzie. Nie każde zapytanie będzie wymagało użycia wszystkich klauzul. Niektóre z nich możesz pomijać np. jeśli nie łączysz tabel, nie potrzebujesz klauzuli JOIN … ON. Jeśli nie grupujesz danych, nie będzie Ci potrzebna klauzula GROUP BY itd.

Przykłady podstawowych zapytań

Poniżej przykłady, które możesz wykorzystać do rozpoczęcia samodzielnego pisania prostych zapytań i odczytywania danych z bazy.

Odczytanie wszystkich kolumn i wszystkich wierszy z jednej tabeli (Person.Person). Aby odczytać wszystkie kolumny wstawiamy symbol gwiazdki (*).

SELECT *
FROM Person.Person;

Odczytanie wybranych kolumn i wszystkich wierszy z jednej tabeli. Aby odczytać wybrane kolumny, wypisujemy ich nazwy. Z tabeli Person.Person odczytujemy imię (FirstName) i nazwisko (LastName) oraz wszystkie rekordy.

SELECT FirstName, LastName
FROM Person.Person;

Odczytanie wybranych wierszy. Aby odczytać tylko niektóre wiersze używamy klauzuli WHERE. Umożliwia ona filtrowanie zwracanych przez zapytanie wierszy. Umieszczone w niej warunki logiczne są sprawdzane dla każdego wiersza tabeli. Jeśli wynikiem testu logicznego jest PRAWDA, wiersz trafia do wyniku zapytania. Jeżeli wynikiem jest FAŁSZ lub WARTOŚĆ NIEZNANA, wiersz nie jest uwzględniony w wyniku. Z tabeli Person.Person wybrany zostanie tylko ten wiersz, dla którego w kolumnie BusinessEntityID jest wartość równa 1702.

SELECT *
FROM Person.Person
WHERE BusinessEntityID = 1702;

Ograniczenie zapytania do niektórych wierszy. Ograniczenie liczby wierszy zwróconych w zapytaniu umożliwia klauzula TOP. Jest ona opcjonalna. W klauzuli TOP liczba wierszy może być określona bezwzględnie lub procentowo.

Poniższe zapytanie zwróci 3 wiersze

SELECT TOP 3 *
FROM Person.Person;

Poniższe zapytanie zwróci 3% wierszy

SELECT TOP 3 PERCENT *
FROM Person.Person;

Wyeliminowanie z wyniku zapytania powtarzających się danych. Można to zrobić za pomocą słowa kluczowego DISTINCT.

SELECT DISTINCT FirstName, LastName
FROM Person.Person;

Słowo kluczowe DISTINCT przykład

Łączenie ciągów znaków – w instrukcji SELECT można umieszczać stałe i łączyć ciągi znaków. W poniższym zapytaniu łączymy imię (FirstName) i Nazwisko (LastName) i wstawiamy pomiędzy nie stałą (w tym przypadku jest to spacja).

SELECT DISTINCT FirstName, LastName,  FirstName + ' '+ LastName
FROM Person.Person;

Komentarze – komentarzy używamy do dokumentowania kodu lub wyłączania partii kodu z części wykonywanej przez silnik bazodanowy.

SQL Server obsługuje dwa typy znaków komentarza:

  • podwójne myślniki – wszystko od podwójnych myślników do końca linii jest częścią komentarza i jest pomijane.
    SELECT * 
    FROM Person.Person --tabela z klientami
    --WHERE FirstName = 'Ken'
    
  • para znaków z ukośnikiem w przód – cały kod pomiędzy otwartym i zamkniętym znakiem komentarza jest pomijany.
    SELECT * 
    FROM Person.Person 
    WHERE FirstName = 'Ken'
    /*AND LastName like 'M%'
    AND Gender = 'M' */;
    

Wygodnym sposobem komentowania kodu jest użycie przycisków znajdujących się na pasku narzędzi w edytorze SQL Server Management Studio

SQL komentowanie kodu

Aliasy kolumn – alias umożliwia chwilową zmianę nazwy kolumny w ramach danego zapytania. Słowo kluczowe AS jest opcjonalne.

SELECT SalesYTD AS wyniki
FROM Sales.SalesTerritory;

Aliasy tabel – aliasy tabel definiujemy w klauzuli FROM. Po zdefiniowaniu aliasu dla tabeli, w pozostałych klauzulach zapytania nie możemy już używać oryginalnej nazwy. Musimy używać aliasu. Słowo kluczowe AS jest opcjonalne.

SELECT ST.SalesYTD as wyniki
FROM Sales.SalesTerritory as ST;

Sortowanie wyników zapytań – wyniki zapytań mogą być sortowane według jednej lub wielu kolumn. Kolejność, w jakiej kolumny są wymienione w klauzuli ORDER BY wyznacza kolejność sortowania.

SELECT FirstName, LastName
FROM Person.Person
ORDER BY FirstName;
SELECT FirstName, LastName
FROM Person.Person
ORDER BY FirstName, LastName;

Złożone warunki logiczne – wszystkie operatory porównania możemy łączyć ze sobą za pomocą operatorów logicznych AND, OR.

SELECT *
FROM Person.Person
WHERE (LastName LIKE 'B%' OR LastName LIKE 'C%')
       AND PersonType = 'EM'
       AND EmailPromotion = 1;

Funkcje wbudowane

Zaokrąglenie w górę – funkcja CEILING()

SELECT Freight, CEILING(Freight) 
FROM Orders; 

Zaokrąglenie w dół – funkcja FLOOR()

SELECT Freight, FLOOR(Freight) 
FROM Orders;

Zaokrąglenie liczby do określonej, przez drugi argument wywołania, liczby miejsc po przecinku – funkcja ROUND()

SELECT Freight, ROUND(Freight, 1)
FROM Orders; 

Obliczenie długości przekazanego ciągu znaków – funkcja LEN()

SELECT ShipCity, LEN(ShipCity)
FROM Orders; 

Usunięcie wiodących spacji – funkcja LTRIM()

SELECT ShipCity, LTRIM(ShipCity)
FROM Orders;

Usunięcie spacji z prawej strony ciągu znaków – funkcja RTRIM()

SELECT ShipCity, RTRIM(ShipCity)
FROM Orders;

Zwrócenie wskazanej liczby znaków z lewej lub z prawej strony tekstu – funkcje LEFT() i RIGHT()

SELECT ProductNumber, LEFT(ProductNumber,2)
FROM Production.Product;
SELECT ProductNumber, RIGHT(ProductNumber,4)
FROM Production.Product;

Zwrócenie części ciągu znaków o określonej długości (zaczynając od znaku o podanej pozycji) – funkcja SUBSTRING()

SELECT ShipCity, SUBSTRING(ShipCity,1,3)
FROM Orders;

Zwrócenie tekstu zamienionego na duże lub małe litery – funkcje UPPER() i LOWER()

SELECT ShipCity, UPPER(ShipCity)
FROM Orders; 
SELECT ShipCity, LOWER(ShipCity)
FROM Orders; 

Zwrócenie daty i czasu zwiększonego o podaną liczbę wskazanych jednostek – funkcja DATEADD()

SELECT OrderDate, DATEADD(Day,2,OrderDate)
FROM Orders; 

Zwrócenie liczby jednostek dzielących przekazane jako argumenty wywołania daty – funkcja DATEDIFF()

SELECT RequiredDate, OrderDate, DATEDIFF(Day,OrderDate, RequiredDate)
FROM Orders; 

Zwrócenie numeru dnia, miesiąca lub roku z przekazanej jako argument daty – funkcje DAY(), MONTH(), YEAR()

SELECT OrderDate, DAY(OrderDate)
FROM Orders; 
SELECT OrderDate, MONTH(OrderDate)
FROM Orders; 
SELECT OrderDate, YEAR(OrderDate)
FROM Orders; 

Zwrócenie bieżącej daty i czasu – funkcja GETDATE()

SELECT GETDATE();

Konwersja typów danych – za pomocą funkcji CAST i CONVERT można określić jawną konwersję danych. Funkcja CONVERT różni się od funkcji CAST tym, że można w niej określić styl typu danych. Jest to przydatne przy konwersji na typy danych daty i czasu.

SELECT SalesYTD, CAST(SalesYTD as VARCHAR(100))
FROM Sales.SalesTerritory;
SELECT SalesYTD, CONVERT(varchar(10), ModifiedDate, 120) as [date]
FROM Sales.SalesTerritory;

Wyrażenie CASE

Ocenia listę warunków i zwraca jedno z możliwych wyrażeń wynikowych:

  • jeśli określony w klauzuli IF warunek jest prawdziwy, wykonywany jest blok instrukcji THEN, w przeciwnym razie wykonywane są instrukcje z bloku ELSE.
  • kolejne warunki logiczne (klauzule WHEN) są sprawdzane w kolejności, w jakiej zostały zapisane. Po znalezieniu pierwszego prawdziwego warunku, funkcja CASE zwraca odpowiadającą mu wartość i nie sprawdza dla tego wiersza kolejnych warunków.
SELECT [TerritoryID], SalesYTD / 1000000,
CASE
    WHEN SalesYTD < 5000000 THEN 'słaby wynik'
    WHEN SalesYTD >= 5000000 AND SalesYTD < 10000000 THEN 'dobry wynik'
ELSE 'kosmiczna sprzedaż!'
END
FROM Sales.SalesTerritory;

Grupowanie i funkcje agregujące

Grupowanie danych polega na łączeniu wielu wierszy w jeden. Najprostszym przypadkiem grupowania jest połączenie wszystkich wierszy tabeli w jedną grupę i np. ich policzenie.

SELECT COUNT(*)
FROM Person.Person;

Można też podzielić wiersze na grupy. Podstawą zakwalifikowania wiersza do danej grupy jest wartość jednej z kolumn lub wynik użytego do grupowania wyrażenia.

SELECT PersonType, COUNT(*)
FROM Person.Person
GROUP BY PersonType;

Funkcje agregujące

Funkcje agregujące (grupujące) operują na zbiorach a nie na pojedynczych wartościach. Pozwalają one podsumowywać wyniki działania innych poleceń SQL, zliczać liczbę wystąpień, sumować, obliczać wartości średnie.

Zliczenie wierszy lub wartości w kolumnach – funkcja COUNT()
Funkcja COUNT() wywołana ze znakiem gwiazdki zlicza wszystkie rekordy przechowywane w tabeli włącznie z duplikatami i rekordami zawierającymi wartość specjalną NULL.

Zliczamy wszystkie rekordy

SELECT COUNT (*)
FROM Person.Person;

Zliczamy wszystkie rekordy z wyjątkiem tych, które zawierają wartość specjalną NULL.

SELECT COUNT (LastName)
FROM Person.Person;

Zliczamy unikalne rekordy – tylko te, które mają unikalne nazwiska (dane w kolumnie LastName).

SELECT COUNT (DISTINCT LastName)
FROM Person.Person;

Zwrócenie sumy wartości w grupie – funkcja SUM(). Jej argumentami mogą być tylko wartości numeryczne.

SELECT SUM(StandardCost)
FROM Product;

Zwrócenie średniej wartości w grupie – funkcja AVG(). Jej argumentami mogą być tylko wartości numeryczne.

SELECT AVG(StandardCost)
FROM Product;

Zwrócenie najmniejszej wartości w grupie – funkcja MIN(). Jej argumentami mogą być dane typu liczbowego, daty i czasu oraz tekstowe.

SELECT MIN(StandardCost)
FROM Product;

Zwrócenie największej wartości w grupie – funkcja MAX(). Jej argumentami mogą być dane typu liczbowego, daty i czasu oraz tekstowe.

SELECT MAX(StandardCost)
FROM Product;

Klauzula GROUP BY umożliwia połączenie identycznych danych w grupy. Najczęściej wiersz jest klasyfikowany do danej grupy na podstawie wartości kolumny innej niż ta, dla której wywołana jest funkcja grupująca.

W poniższym zapytaniu funkcja grupująca wywołana jest dla kolumny Quantity natomiast wiersz jest klasyfikowany do grupy według kolumny ProductID.

SELECT ProductID, SUM(Quantity)
FROM [Order Details]
GROUP BY ProductID;

W klauzuli GROUP BY możemy używać jedynie kolumn, które zostały wymienione na liście klauzuli SELECT. Klauzula GROUP BY umożliwia tworzenie podgrup poprzez dodanie dodatkowych kolumn.

SELECT ProductID, OrderID, SUM(Quantity)
FROM [Order Details]
GROUP BY ProductID, OrderID;

Filtrowanie grup

Za pomocą klauzuli HAVING możesz ograniczyć liczbę wyświetlanych grup. Możesz umieścić w niej dowolną funkcję grupującą lub nazwy kolumn użytych do grupowania. Umieszczony w klauzuli HAVING warunek logiczny jest sprawdzany dla każdej grupy i tylko te dane, dla których będzie on prawdziwy zostaną wybrane do wyniku zapytania.

SELECT ProductID, SUM(Quantity)
FROM [Order Details]
GROUP BY ProductID
HAVING SUM(Quantity) > 1000;

Uwaga! Klauzula WHERE nakłada warunki na pojedyncze rekordy. Klauzula HAVING nakłada warunki na grupy rekordów tworzone przez klauzulę GROUP BY.

Złączenia tabel

Tabele możemy łączyć za pomocą operatora JOIN … ON, który umieszczamy w klauzuli FROM.

W ramach operatora:

  • wymieniamy nazwy wszystkich odczytywanych tabel
  • określamy warunki ich łączenia

Złączenie wewnętrzne

W wyniku złączenia wewnętrznego (INNER JOIN, JOIN) otrzymujemy tabelę wynikową, składającą się ze wszystkich kolumn tabel wejściowych. Zawierać ona będzie tylko te wiersze, dla których warunki złączenia będą spełnione (wynik będzie TRUE).

SELECT *
FROM Customers a
JOIN [Address] b
ON a.CustomerID = b.CustomerID;

INNER JOIN jest złączeniem symetrycznym – nie ma znaczenia, w jakiej kolejności łączymy tabele A z B czy odwrotnie. Nie ma też znaczenia kolejność, w jakiej wypiszemy warunki złączenia w słowie kluczowym ON.

Jeżeli chcesz wyświetlić tylko niektóre kolumny, należy je wymienić w klauzuli SELECT. Jeśli nazwa kolumny powtarza się w tabelach, które złączyłeś, aby ją wyświetlić należy jej nazwę poprzedzić nazwą tabeli lub aliasem nadanym dla tabeli.

SQL inner join przykład

Jeżeli tabele, które chcemy łączyć nie mają wspólnej kolumny musimy utworzyć złączenie na podstawie innej tabeli, która posiada wspólną kolumnę z jedną z łączonych tabel lub z obydwoma.

SQL inner join tabela pośrednia

Aby połączyć tabelę A z tabelą C:

  • Łączymy tabelę A z tabelą B według kolumny BusinessEntityID
  • Łączymy tabelę B z tabelą C według kolumny AddressID
SELECT *
FROM Person.Person A
JOIN Person.BusinessEntityAddress B
ON A.BusinessEntityID = B.BusinessEntityID
JOIN Person.Address C
ON B.AddressID = C.AddressID;

Złączenie zewnętrzne lewostronne (LEFT OUTER JOIN, LEFT JOIN)

Łączone są najpierw wewnętrznie dwa zbiory (INNER JOIN), na koniec dodawane są do wyniku wszystkie niedopasowane elementy tabeli po PRAWEJ stronie operatora JOIN.

W wyniku złączenia LEFT JOIN otrzymamy:

  • wszystkie rekordy z tabeli po LEWEJ stronie operatora JOIN
  • dopasowane rekordy z tabeli po PRAWEJ stronie operatora JOIN
  • niedopasowane rekordy z tabeli po PRAWEJ stronie operatora JOIN
SELECT *
FROM Customers a
LEFT JOIN [Address] b
ON a.CustomerID = b.CustomerID;

SQL left join przykład

Złączenie zewnętrzne prawostronne (RIGHT OUTER JOIN, RIGHT JOIN)

Łączone są najpierw wewnętrznie dwa zbiory (INNER JOIN), na koniec dodawane są do wyniku wszystkie niedopasowane elementy tabeli po PRAWEJ stronie operatora JOIN.

W wyniku złączenia RIGHT JOIN otrzymamy:

  • wszystkie rekordy z tabeli po PRAWEJ stronie operatora JOIN
  • dopasowane rekordy z tabeli po LEWEJ stronie operatora JOIN
  • niedopasowane rekordy z tabeli po LEWEJ stronie operatora JOIN
SELECT *
FROM Customers a 
RIGHT JOIN [Address] b
ON a.CustomerID = b.CustomerID;

SQL right join przykład

Złączenie zewnętrzne obustronne (FULL OUTER JOIN, FULL JOIN)

Zwraca wszystkie wiersze obu złączonych tabel, w tym te, które nie spełniają warunku złączenia

SELECT *
FROM Customers a
FULL JOIN [Address] b
ON a.CustomerID = b.CustomerID;

SQL full join przykład

Iloczyn kartezjański

Zapytanie wykonuje iloczyn kartezjański na podstawie łączonych tabel – łączy każdy wiersz tabeli A z każdym wierszem tabeli B.

SELECT *
FROM Customers a
CROSS JOIN [Address] b;

Połączenie tabeli z samą sobą

Złączenie tabeli z samą sobą występuje, gdy po lewej i prawej stronie złączenia występuje ta sama tabela. Konieczne jest w nim użycie aliasów.

Ma zastosowanie, jeżeli chcemy porównać wybrane rekordy tabeli z innymi rekordami tej samej tabeli.

SELECT a.BusinessEntityID, a.FirstName, a.LastName, b.FirstName, b.LastName
FROM Person.Person a, Person.Person b
WHERE a.BusinessEntityID > b.BusinessEntityID
AND a.FirstName = b.FirstName;

Złączenia wielokrotne

W jednym zapytaniu możemy odwołać się do wielu tabel. Jednak z każdą następną dołączoną tabelą spada wydajność zapytania. Aby zoptymalizować zapytanie można stworzyć tabele tymczasowe.

Złączenia wielokrotne przykład 1

Wynik złączenia

Złączenia wielokrotne przykład 2

Łączenie wyników zapytań

Oprócz łączenia tabel, język SQL pozwala także łączyć wyniki zapytań. Łączenie wyników zapytań to dodawanie lub usuwanie wierszy zwróconych przez złączone zapytania.

Łączone wyniki zapytań muszą składać się z tej samej liczby kolumn. Dane w odpowiadających sobie kolumnach muszą być tego samego typu albo umożliwiać niejawną konwersję typów.

Operator UNION

Za jego pomocą możemy dodać do siebie wyniki zapytań. Operator UNION powoduje usunięcie z wyniku powtórzonych wierszy.

SELECT * 
FROM Customers
UNION
SELECT * 
FROM Employees

Operator UNION ALL

Za jego pomocą możemy złączyć ze sobą wyniki zapytań bez eliminowania duplikatów.

SELECT * 
FROM Customers
UNION
SELECT * 
FROM Employees

Operator INTERSECT

Zwraca część wspólną wyników dwóch zapytań, a więc tylko te wiersze, które zostały zwrócone przez oba zapytania.

SELECT * 
FROM Customers
INTERSECT
SELECT * 
FROM Employees

Operator EXCEPT

Zwraca te wiersze, które znalazły się wyłączenie w wyniku pierwszego zapytania i nie było ich w wyniku drugiego zapytania.

SELECT * 
FROM Customers
EXCEPT
SELECT * 
FROM Employees

Po zapoznaniu się z opisanymi wyżej instrukcjami, powinieneś potrafić samodzielnie pisać podstawowe zapytania i odczytywać dane z bazy. Jeśli będziesz miał problem z jakąś instrukcją, napisz do nas wiadomość mailem lub zrób komentarz. Postaramy się pomóc.

Dodaj komentarz

Twój adres email nie zostanie opublikowany.