Wszystkie wpisy, których autorem jest sqlwizzard

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 SELECT

SQL INSERT INTO  SELECT



DEFINICJA

Chciałbym Wam dzisiaj opisać działanie składni INSERT INTO SELECT. Jak już widzimy po słowach kluczowych coś wstawiamy (INSERT) i określamy skąd i co wrzucamy (SELECT). Żeby jakieś dane wziąć i gdzieś dodać musimy określić dwie rzeczy źródło i zakres danych do pobrania oraz cel, czyli gdzie te pobrane dane chcemy „wrzucić”. To gdzie chcemy „wrzucić” określamy po słówkach INSERT INTO, a to co i skąd chcemy pobrać określamy po słówku SELECT, tutaj będziemy konstruowali po prostu zapytanie. Składnia całej konstrukcji poniżej. PAMIĘTAJ, że struktura danych kopiowanych i struktura danych do której te dane kopiujemy muszą być zgodne.


SQL INSERT INTO SELECT składnia

INSERT INTO tabela_cel(nazwy_kolumn_po_przecinkach)
SELECT nazwy_kolumn_po_przecinkach FROM tabela_źródło
— opcjonalnie możemy dodać „sekcję” WHERE

 


Przykład zastosowania INSERT INTO SELECT (baza: Northwind)

Do przykładu wykorzystamy treningową bazę Northwind. Dodamy do niej nową tabelę „Customers_tmp” z kolumnami (CustomersID, CompanyName, City). Zrzut z tworzenia tabeli poniżej.

Nasze zadanie będzie polegało na „skopiowaniu” rekordu o CustomersID = ‚ALFKI’ z tabeli Customers (ale tylko dane z kolumn: CustomersID, CompanyName, City) do tabeli „Customers_tmp” z takimi samymi kolumnami. Zapytanie poniżej.

INSERT INTO Customers_tmp
SELECT CustomerID, CompanyName, City FROM Customers WHERE CustomerID = ‚ALFKI’

czyli do tabeli „Customers_tmp” wrzucimy dane z rekordu o CustomersID = ‚ALFKI z tabeli „Customers”, ale tylko dane z kolumn: CustomerID, CompanyName, City.
Uruchamiamy zapytanie i o poprawności wykonania zapytania mówi nam komunikat poniżej.

Teraz żeby sprawdzić czy wszystko zadziałało musimy odpytać tabelę „Customers_tmp” o wszystkie rekordy.

SELECT
*
FROM
Customers_tmp

W wyniku powinniśmy otrzymać jeden rekord


Przykład zastosowania INSERT INTO SELECT (baza: Adventureworks)

W poprzednim przykładzie kopiowaliśmy tylko jeden rekord. Teraz spóbujmy skopiować więcej rekordow i do tego celu wykorzystam bazę AdventureWorks2008R2.

Tworzymy nową tabelę o nazwie „Person_SP” (skrót SP od Sales Person) do której będziemy kopiowali osoby z tabeli „Person.Person” ale tylko takie którę są sprzedawcami. Zakres kopiowanych danych to: BusinessEntityID, FirstName, LastName.

Tworzymy nową tabelę „Person_SP”

Teraz tworzymy strukturę z INSERT INTO SELECT gdzie w części SELECT zbudujemy zapytanie które pobierze nam dane (BusinessEntityID, FirstName, LastName) o osobach które sa sprzedawcami.

INSERT INTO Person_SP
SELECT BusinessEntityID, FirstName, LastName FROM Person.Person WHERE PersonType = ‚SP’

W tabeli „Person.Person” takich osób jest 17 więc dane tylu osób powinny być skopiowane do tabeli „Person_SP”

Uruchamiamy zapytanie (F5).
Powinniśmy otrzymać komunikat o poprawnym wykonaniu naszej konstrukcji.

Teraz żeby sprawdzić czy w tabeli „Person_SP” jest 17 rekordów z danymi budujemy zapytanie.

SELECT
*
FROM
Person_SP

Wynikiem zapytanie powinno wyć wygenerowanie 17-tu rekordow, co widzimy na zrzucie poniżej.


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’ )