Archiwa kategorii: SQL komendy/funkcje

SQL IS NOT NULL

SQL IS NOT NULL



DEFINICJA

Polecenie IS NOT NULL jest związane z wartością NULL, czyli z wartością nieokreśloną (nieznaną). Jak sama nazwa sugeruje za pomocą IS NOT NULL możemy wyszukać wszystkie wartość które NIE są NULL-ami. Poniżej przykład filtrowania rekordów w tabeli za pomocą polecenia IS NOT NULL.


Przykład zastosowania IS NOT NULL (baza AdventureWorks)

Wyświetl pracowników (tabela Person.Person) którzy mają drugie imię.

SELECT
FirstName AS Imię
,MiddleName AS [Drugie imię]
,LastName AS Nazwisko
FROM
Person.Person
WHERE
MiddleName IS NOT NULL
AND (PersonType = ‚EM’ OR PersonType = ‚SP’)

Żeby uzyskać prawidłowy wynik w części WHERE musimy dodać warunek MiddleName IS NOT NULL, co można zinterpretować tak: pokaż mi wszystkie rekordy z pracownikami gdzie pole MiddleName NIE JEST PUSTE, czyli którzy pracownicy mają drugie imię.


Za pomocą IS NOT NULL możemy także filtrować wyniki zapytania do rekordów które w zdefiniowanym polu NIE mają wartości NULL, przykład poniżej.

Przykład zastosowania IS NOT NULL (baza AdventureWorks)

Wyświetl listę produktów które były, choć raz, użyte w zamówieniach (bo wiadomo, że nie każdy produkt z asortymentu musi być zamówiony).

Na początek żeby pokazać Wam jak wykorzystując wartości NULL możemy rozwiązać ten problem.

Połączmy dwie tabele Production.Product z tabelą Sales.SalesOrderDetails za pomocą polecenie LEFT JOIN. Jeżeli produkt nie znalazł się w żadnym z zamówień w ostatniej kolumnie wyniku zapytania pojawi nam się wartość NULL a jeśli wystąpił pojawi się ID tego produktu z zamówienia (ID z kolumn 1 i 4 powinny być takie same).

Zapytanie:

SELECT
P.ProductID
,P.Name
,P.ProductNumber
,D.ProductID
FROM
Production.Product P
LEFT JOIN Sales.SalesOrderDetail D ON P.ProductID = D.ProductID

Zrzut z częścią wyniku:

Żeby nie zaciemniać wyników wyświetliłem tylko cztery kolumny. W ostatniej wyświetliłem ID produktu z tabeli Sales.SalesOrderDetails. Jeżeli system nie znalazł dopasowania produtku do żadnego z zamówień to wyświetla NULL a jeżeli znalazł takie dopasowanie to wyświetlane jest ID produktu z zamówienia (oczywiście ID produktu z kolumny 1 i 4 powinny być takie same).

Jeżeli teraz chcemy wyświetlić tylko te produtkty, które znajdują się w zamówieniach musimy dodać warunek: pokaż produkty które w polu ProductID (z tabeli Sales.SalesOrderDetails) ma wartość inną niż NULL, czyli nasze zapytanie przybierze formę.

SELECT
P.ProductID
,P.Name
,P.ProductNumber
,D.ProductID
FROM
Production.Product P
LEFT JOIN Sales.SalesOrderDetail D ON P.ProductID = D.ProductID
WHERE
D.ProductID IS NOT NULL
GROUP BY
P.ProductID, P.Name, P.ProductNumber, D.ProductID


SQL INTERSECT

SQL INTERSECT



DEFINICJA

Dzięku poleceniu INTERSECT otrzymamy wartości wspólne z dwóch zapytań. Dla lepiej zobrazowania tej operacji zerknij na obrazek poniżej.

Pamiętaj, aby móc zastosować polecenie INTERSECT wyniki obu zapytań muszą mieć identyczną strukturę (ilość kolumn) i typy danych w poszczególnych kolumnach.


SQL INTERSECT składnia

SELECT
nazwa_kolumny1 , nazwa_kolumny2 , …
FROM
nazwa_tabeli1

INTERSECT

SELECT
nazwa_kolumny1 , nazwa_kolumny2 , …
FROM
nazwa_tabeli2


SQL INSERT INTO

SQL INSERT INTO



DEFINICJA

Polecenie INSERT INTO służy do wstawiania/tworzenia nowych rekordów w tabeli. Ogólnie rzecz biorą po INSERT INTO wskazujemy tabelę do której chcemy dołożyć rekord, musimy określić do których kolumn chcemy „wrzucić” wartości i określić te wartości, składnia poniżej.


SQL INSERT INTO składnia

INSERT INTO nazwa_tabeli(nazwa_kolunny1, nazwa_kolunny2, nazwa_kolunny3, itd)
VALUES (wartość1, wartość2, wartość3, itd)

PAMIĘTAJ, żeby w nawiasie określającym nazwy kolumn było tyle samo parametrów co w nawiasie określającym wartości pól w poszczególnych kolumnach. Istnieje jeszcze jedna forma tego polecenia a mianowicie

INSERT INTO nazwa_tabeli
VALUES (wartość1, wartość2, wartość3, itd)

W tym przypadku nie określamy kolumn. Ja osobiście jestem zwolennikiem pierwszego rozwiązania, czyli jawnego deklarowania wszystkiego co można, co pozwala uniknąć późniejszych problemów szczególnie jeżeli daną bazą administruje większa liczba osób.


Przykład zastosowania INSERT INTO (baza Adventureworks)

Dodaj do typów osób w bazie nowy typ „Temporary Worker”.

INSERT INTO Person.ContactType(Name,ModifiedDate)
VALUES(‚Temporary Worker’ , ‚2017-01-01 00:00:00.000’)


Przykład zastosowania INSERT INTO (baza Northwind)

Dodaj nowego dostawcę do tabeli „Suppliers”.

INSERT INTO Suppliers(CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone,Fax,HomePage)
VALUES (‚Zzimo’,’Rob Zikosen’,’Sales Manager’,’Zielona 4′,’Warszawa’,’NULL’,’00-900′,’Poland’,'(22)127624′,'(22)735483′,NULL)

INFO: do kolumny „SupplierID” nie trzeba „wpisywać” wartości, zostanie ona wygenerowana automatycznie.


SQL IN z CASE

SQL IN z CASE



DEFINICJA

Polecenie IN opisywałem tutaj, ale ostatnio „buszując” w Internecie natrafiłem na ciekawe pytanie dotyczące zapytania SQL i postanowiłem rozwinąć trochę poprzedni wpis o rozwiązanie z CASE i podzapytaniem.


Przykład zastosowania polecenia IN z CASE

SELECT
*
FROM
nazwa_tabeli
WHERE
nazwa_pola IN (
CASE
WHEN warunek1 THEN wartość1
WHEN warunek2 THEN wartość2
ELSE warunek3 THEN wartość3
END
)

Widzimy tutaj że w zależności od wyniku polecenie CASE zostanie wybrana wartość która będzie argumentem dla polenia IN.


Przykład zastosowania polecenia IN z podzapytaniem

SELECT
*
FROM
nazwa_tabeli
WHERE
nazwa_pola IN (
SELECT TOP 1
nazwa_pola
FROM
nazwa_tabeli
WHERE
warunek
)

W tym przypadku zbiorem wartości dla IN jest wynik podzapytania.
Można by pójść o krok dalej i uzależnić wartości dla zbioru IN od zmiennej.


Przykład zastosowania polecenia IN z CASE i z deklaracją zmiennej.

DECLARE @zmienna varchar(5) = ‚100’

SELECT
*
FROM
nazwa_tabeli
WHERE
nazwa_pola IN (
CASE
WHEN @zmienna < 100 THEN wartość1
WHEN @zmienna = 100 THEN wartość2
WHEN @zmienna > 100 THEN wartość3
END
)


Przykład zastosowania polecenia IN z podzapytaniem i z deklaracją zmiennej.

DECLARE @zmienna varchar(5) = ‚100’

SELECT
*
FROM
nazwa_tabeli
WHERE
nazwa_pola IN (
SELECT TOP 1
nazwa_pola
FROM
nazwa_tabeli
WHERE
nazwa_pola <= @zmienna
)


SQL GRANT

SQL GRANT



DEFINICJA

Tworząc i administrując bazami danych przychodzi taki moment kiedy potrzebujemy nadać komuś uprawnienia do naszej bazy i niekoniecznie chcemy aby miał on wszystkie uprawnienia do całej naszej bazy np. jakiś analityk potrzebuje robić okresowe zestawienia sprzedaży, jakaś aplikacja korzysta z naszej bazy danych itd. Zwróć uwagę, że jenek użytkownik potrzebuje większe uprawnienia np. odczyt ale również i zapis danych na jakichś tabelach, a drugiemu użytkownikowi wystarczy tylko odczyt na jednej tabeli. Rozwiązaniem tej sytuacji jest stworzenie użytkownika na bazie i nadanie mu odpowiednich uprawnień. Możemy to zrobić dzięki poleceniu GRANT. Jedna uwaga we różnych środowiskach bazodanowych uprawnienia nadajemy w różny sposób tzn. niektóre środowiska jak np. SQL Server wymaga już utworzonego użytkownika i dopiero dla utworzonego użytkownika możemy nadać uprawnienia.


SQL GRANT składnia

GRANT
uprawnienia_użytkownika ON nazwa_tabeli TO nazwa_użytkownika

uprawnienia użytkownika – to lista która może zawierać jedno lub więcej (oddzielonych przecinkami) uprawnień które chcemy nadać użytkownikowi
nazwa tabeli – tutaj wpisujemy nazwę tabeli na którą chcemy nadać uprawnienie
nazwa_użytkownika – tutaj wpisujemy nazwę użytkownika, któremu chcemy nadać uprawnienia


Nadawanie uprawnień w SQL SERVERZE

Krok 1. Uruchamiamy Management Studio

Krok 2. Przechodzimy do gałęzi Security -> Logins i sprawdzamy czy nasz użytkownik (któremu chcemy nadać uprawnienia) jest na liście. Jeżeli jest to z poziomu okna zapytań możemy poleceniem GRANT nadać użytkownikowi wymagane uprawnienia. Jeżeli użytkownika nie ma na liście klikamy prawym przyciskiem myszy (dalej PPM) i z menu wybieramy opcję „New Login

Krok 3. W oknie „Login – New” wprowadzamy nazwę użytkownika (UWAGA wymagana jest pełna nazwa użytkownika razem z domeną: domena\nazwa_użytkownika), możemy określić metodę autektykacji użytkownika a nawet domyślą bazę danych. Po wypełnieniu formatki potwierdzamy nasz wybór przyciskiem OK. W tym momencie nasz użytkownik powiniem znaleźć się na liście Security -> Logins

Krok 4. W oknie zapytań wybieramy odpowiedni kontekst (odpowiednią bazę danych) i poleceniem GRANT nadajemy odpowiednia uprawnienia naszemu użytkownikowi np.
GRANT select ON Wypozyczenia TO [DESKTOP\USER]

Krok 5. Teraz we właściwościach tabeli Wypozyczenia możemy sprawdzić i zobaczyć czy uprawnienia zostały nadane. W tym celu klikamy PPM na tabeli Wypozyczenia w bazie Biblioteka i wybieramy opcję Properties. W oknie „Table Properties – Wypozyczenia” w sekcji „Select a page” klikamy na „Permissions”. Na poniższym zrzucie widzimy nadane uprawnienie SELECT dla użytkowniak USER.


SQL FOREIGN KEY

SQL FOREIGN KEY



DEFINICJA

Klucz obcy służy do definiowania relacji między tabelami. Kolumnę (lub kolumny) którą zdefiniujemy jako klucz obcy w jednej tabeli wiążemy z kolumną (kolumnami) która jest kluczem głównym w drugiej tabeli. Oznacza to, że wartości przechowywane w kolumnie która jest zdefinIowana jako klucz obcy w pierwszej tabeli zawsze będą miały swój odpowiednik w kolumnie która jest zdefiniowana jako klucz główny w drugiej tabeli.

Żeby lepiej zrozumieć ten mechanizm posłużę się przykładem. Wyobraź sobie, że mamy dwie tabele. Jedna o nazwie „Pracownicy” i druga o nazwie „Miasta”. Tabla „Pracownicy” przechowuje informacje na temat pracowników. W jednej z kolumn chcemy przechowywać informacje na temat miasta w którym mieszka nasz pracownik. Ale zamiast wpisywać w każdym rekordzie pełną nazwę miasta które będą się powtarzać stworzymy nową tabelę o nazwie „Miasta” gdzie będziemy przechowywać pełne nazwy miast i w której nazwy miast nie będą dublowane. Za pomocą kluczy obcego i głównego połączymy te tabele. Przeanalizuje poniższy obrazek.

Żeby nie dublować i przechowywać pełnych nazw miast w tabeli „Pracownicy” w kolumnie „ID_miasto” będziemy przechowywali identyfikator miasta z tabeli „Miasta”. W naszym przykładzie kolumnę „ID_miasto” z tabeli „Pracownicy” musimy zdefiniować jako klucz obcy a kolumnę „ID_miasto” z tabeli „Miasta” musimy zdefiniować jako klucz główny tej tabeli.

Oba typu kluczy możemy utworzyć w momencie tworzenia tabeli lub dodać je już na gotowej tabeli.

Na początku zajmiemy się sytuacją w której tworzymy nowe tabele od początku. Tworzenie tabel musimy rozpocząć od tabeli „Miasta” bo to do niej będziemy tworzyli relację z tabeli „Pracownicy”. Nie możemy utworzyć tabeli w której tworzymy relację do tabeli która jeszcze nie istnieje bo otrzymamy komunikat o błędzie. Teraz utworzymy tabelę „Miasta” i definiujemy kolumnę „ID_miasto” jako klucz główny tej tabeli, bo to wartości w tej kolumnie będą jednoznacznie identyfikować rekordy w tej tabeli.

CREATETABLE Miasta
(
ID_miasto int NOT NULL
,nazwa varchar(40) NOT NULL
,PRIMARY KEY (ID_miasto)
)

teraz utworzymy tabele „Pracownicy” gdzie zdefiniujemy klucz obcy na kolumnie „ID_miasto” i ustawimy relację tej kolumny z kolumną „ID_miasto” z tabeli „Miasta” i klucz główny na kolumnie „ID_pracownik” w tabeli „Pracownicy”.

CREATETABLE Pracownicy
(
ID_pracownik int NOT NULL
,Nazwisko varchar(40) NOT NULL
,Imie varchar(20) NOT NULL
,ID_miasto int NOT NULL
,PRIMARY KEY (ID_pracownik)
, CONSTRAINT fk_PracownikMiasto FOREIGN KEY (ID_miasto)
REFERENCES Miasta(ID_miasto)
)

Zdefiniowanie klucza obcego możemy dokonać na istniejącej już tabeli. Powiedzmy, że mamy stworzone już tabele „Pracownicy” i „Miasta” i chcemy zdefiniować klucz obcy na kolumnie „ID_miasto” w tabeli „Pracownicy” i ustawić relację do klucza głównego (którym jest kolumna „ID_miasto”) w tabeli „Miasta”.

ALTERTABLE Pracownicy
ADDCONSTRAINT fk_PracownikMiasto FOREIGNKEY (ID_miasto) REFERENCES Miasta(ID_miasto)

w celu usunięcia tego klucza posłużymy się poniższym kodem

ALTER TABLE Pracownicy
DROP FOREIGN KEY fk_PracownikMiasto


SQL DATEDIFF

SQL DATEDIFF



DEFINICJA

Funkcja DATEDIFF() zwraca różnicę, w zdefiniowanych jednostkach, między dwiema datami. !!! UWAGA !!! nie we wszystkich środowiskach bazodanowych funkcja DATEDIFF() działa tak samo. Poniższe przykłady pochodzą z SQL SERVER-a.


SQL DATEDIFF() składnia funkcji

 

SELECT
DATEDIFF(parametr1 , parametr2 , parametr3)

parametry:
parametr1 – definiujemy w jakich jednostkach funkcja ma zwrócić różnicę np. jeśli w latach to YEAR, w miesiącach to MONTH itd
parametr2 – data początkowa (wcześniejsza)
parametr3 – data końcowa (późniejsza)


Przykład zastosowania DATEDIFF() (baza Adventureworks)

Wyświetl wszystkie zamówienia klienta o identyfikatorze 29825 i pokaż ile lat temu było składane każde z jego zamówień.

  SELECT
O.CustomerID AS [Identyfikator klienta]
,O.SalesOrderID AS [Identyfikator zamówienia]
,DATEDIFF( YEAR , O.OrderDate , GETDATE()) AS [Różnica w latach]
FROM
Sales.SalesOrderHeader O
WHERE
O.CustomerID = 29825


Przykład zastosowania DATEDIFF() (baza Northwind)

Oblicz staż pracy dla każdego pracownika.

  SELECT
E.LastName + ‚ ‚ + E.FirstName AS [Nazwisko i Imię]
,DATEDIFF( YEAR , E.HireDate , GETDATE()) AS [Staż Pracy]
FROM
Employees E


SQL EXCEPT

SQL EXCEPT



DEFINICJA

Dzięku poleceniu EXCEPT odejmiemy od siebie wyniki dwóch zapytań (zbiorów). Od wyników pierwszego zapytania odejmiemy wyniki drugiego, włącznie z częścią wspólną. Wynikiem naszej operacji będą tylko te rekordy z zapytania pierwszego które nie mają swoich duplikatów w wynikach drugiego zapytania. Aby lepiej zobrazować tą operację zerknij na obrazek poniżej.

Pamiętaj, aby móc „odjąć” od siebie wyniki obu zapytań musisz (w obu zapytaniach) mieć identyczną strukturę (ilość kolumn) i typy danych w poszczególnych kolumnach muszą do siebie pasować.


SQL EXCEPT składnia

SELECT
nazwa_kolumny1 , nazwa_kolumny2 , …
FROM
nazwa_tabeli1

EXCEPT

SELECT
nazwa_kolumny1 , nazwa_kolumny2 , …
FROM
nazwa_tabeli2


SQL CONVERT

SQL CONVERT



DEFINICJA

Funkcja CONVERT to druga, oprócz funkcji CAST, funkcja służąca do konwersji danych jednego typu na drugi. Przykład: konwersja liczb na tekst, tekstu na liczbę lub daty na tekst.


SQL CONVERT() składnia funkcji

SELECT
CONVERT( nowy_typ_danych , wartość_konwertowana , styl_danych_po_konwersji)

Parametry:
nowy_typ_danych – określamy na jaki typ danych chcemy konwertować wskazacą wartość
wartość_konwertowana – określamy jaką wartość chcemy konwertować
typ_danych_po_konwersji – (OPCJONALNIE) możemy określić typ danych po konwersji. Dotyczy np. daty i czasu ale także liczb np. określnie precyzji.


Przykład zastosowania funkcji CONVERT()

  SELECT
‚1 styczeń 2017 = ‚ + CONVERT( VARCHAR(10) , ‚2017-01-01’ )


SQL DROP INDEX

SQL DROP INDEX



DEFINICJA

Polecenie DROP INDEX służy do usuwania indeksów z wybranej tabeli. W różnych środowiskach bazodanowych składnia do usuwanięcia indeksu wygląda trochę inaczej. W tym wpisie pokażę składnię w wybranych środowiskach.


SQL DROP INDEX składnia dla MySQL

ALTER TABLE nazwa_tabeli DROP INDEX nazwa_indeksu


SQL DROP INDEX składnia dla SQL SERVER

DROP INDEX nazwa_tabeli.nazwa_indeksu


SQL DROP INDEX składnia dla ORACLE

DROP INDEX nazwa_indeksu