Archiwum kategorii: SQL polecenia funkcje

SQL NOT NULL

SQL NOT NULL


    


DEFINICJA

Zasada NOT NULL nakłada ograniczenie na wskazane kolumny, polegające na tym, że można dodać nowego rekordu, bądź zmodyfikować istniejącego, który we wskazanej kolumnie ma wartość NULL. Innymi słowy, jeżeli np. na kolumnie o nazwie „ID_pracownik” ustawimy zasadę NOT NULL to taka kolumna nie może przechowywać wartości NULL.


Przykład zastosowania NOT NULL

W przykładzie zdefiniujemy, przy nowo tworzonej tabeli „Pracownicy”,  kolumny „ID_pracownika” i „Nazwisko” jako te kolumny, które nie mogą przechowywać wartości NULL.

CREATE TABLE Pracownicy
(
                 ID_pracownik int NOT NULL
                ,Imie varchar(20) NOT NULL
                ,Nazwisko varchar(50)
                ,email varchar(50)
                ,telefon int(11)
)


    

SQL FOREIGN KEY (klucz obcy)

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 zdefinowana 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 pomoca kluczy obcego i głównego połączymy te tabele. Przeanalizuje poniższy obrazek.

sql foreign key
Ż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 kolumne "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.

CREATE TABLE 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".

CREATE TABLE 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".

ALTER TABLE Pracownicy
ADD CONSTRAINT
fk_PracownikMiasto FOREIGN KEY (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 PRIMARY KEY (klucz główny)

SQL PRIMERY KEY


    


DEFINICJA

Klucz główny (ang. primary key) to nic innego jak unikalny identyfikator każdego rekordu w tabeli. Słówko unikalny mówi o tym, że wartości w tej kolumnie nie mogą się powtarzać. Kolumna która jest kluczem głównym nie może także przechowywać wartości NULL, czyli wartości nieokreślonych/nieznanych. W jednej tabeli może być tylko jedna kolumna będąca kluczem głównym. Z mojej dotychczasowej praktyki z bazami danych wynika, że zdecydowana większość tabel powinna mieć klucz główny. Klucz ten nadajemy używając polecenia PRIMARY KEY, co pokażą poniższe przykłady.

Klucz główny możemy określić w kilku sytuacjach:
1. Tworzymy nową tabele i definiujemy klucz główny który obejmuje jedną kolumnę
2. Składnia polecenia PRIMARY KEY dla takiego przypadku.

Przykład utworzenia klucza głównego na kolumnie "ID_miasto" w tabeli "Miasta" przechowująca dane nt. miast.

CREATE TABLE Miasta
    (
         ID_miasto INT PRIMARY KEY
        ,nazwa NVARCHAR(30)
        ,kod_pocztowy NVARCHAR(6)
    );

Kluczem głównym nie muszą zawsze być wartości z jednej tabeli. Niekiedy dopiero wartości z dwóch kolumn są wartościami unikalnymi w skali tabeli i mogą być kluczem głównym. Wtedy klucz główny definiujemy przy pomocy poniższej składni. Przyjmijmy, że wartości z kolumn "ID_miasto" i "nazwa" mają tworzyć klucz główny.

CREATE TABLE Miasta
    (
         ID_miasto INT
        ,nazwa NVARCHAR(30)
        ,kod_pocztowy NVARCHAR(6)
        CONSTRAINT pk_Miasta PRIMARY KEY (ID_miasto,nazwa)
    );

zwróć uwagę, że kluczowi głównemu w tym przypadku nadano nazwę "pk_Miasta" która identyfikuje ten klucz. Jeżeli nie określiliśmy klucza głównego przy tworzeniu tabeli możemy zrobić to później wykorzystując polcenie ALTER TABLE, które służy do modyfikacji struktury tabeli. W kontekście klucza głównego modyfikacja ta może polegać na dodaniu lub usunięciu tego klucza. Wtedy nasz kod wyglądał będzie tak.

Przykład ustawienia kolumny ID_miasto jako klucz główny (dodanie klucza głównego) w tabeli Miasta.

ALTER TABLE Miasta
ADD PRIMARY KEY(ID_miasto)

A jeżeli potrzebujemy żeby kluczem głównym było kilka kolumn to składnia kodu będzie analogiczny jest przy tworzeniu nowej tabeli czyli:

ALTER TABLE Miasta
ADD CONSTRAINT pk_Miasta PRIMARY KEY(ID_miasto,nazwa)

Tak jak to wyglądało przy tworzeniu nowej tabeli utworzonemu kluczowi przypisywana jest nazwa, w tym przypadku jest to "pk_Miasta". Teraz warto przedstawić "mechanizm" usuwania kluczy głównych z tabeli. Do tego posługujemy się poleceniem DROP.

Przykład usunięcia klucza głównego z kolumny "ID_miasto" z tabeli "Miasta", w sytuacji kiedy kluczem głównym jest jedna kolumna "ID_miasto".

ALTER TABLE Miasta
DROP PRIMARY KEY(ID_miasto)

Przykład usunięcia klucza głównego w tabeli "Miasta", gdzie kluczem głównym są dwie kolumny "ID_miasto" i "nazwa".

ALTER TABLE Miasta
DROP CONSTRAINT pk_Miasta

zwróć uwagę, że przy usuwaniu klucza głównego (dwie kolumny) posługujemy się nazwą przypisaną wcześniej do tego klucza, czyli "pk_Miasta".


    

SQL CREATE TABLE

SQL CREATE TABLE


    


DEFINICJA

Polecenie CREATE TABLE służy do tworzenia table w bazie danych. Przy okazji tworzenia tabel określamy jakie kolumny mają się znaleźć w danej tabeli, jakiego typu dane mają tam być przechowywane (np. INTEGER, VARCHAR, DECIMAL itp.) i jakie są powiązania danej tabeli z innymi tabelami w bazie danych. Przy tworzeniu tabeli możemy wskazać także czy dana kolumna ma być kluczem głównym czy obcym (klucze główny i obcy opisany w innym wpisie) lub czy może przechowywać wartości NULL (więcej tutaj).

Składnia polecenia CREATE TABLE

CREATE TABLE nazwa_tabeli
    (
        nazwa_kolumny1 typ_danych(rozmiar),
        nazwa_kolumny2 typ_danych(rozmiar),
        ….
    );


Przykład zastosowania polecenia CREATE TABLE (baza Bank_v4)

Przykład tworzenia tabeli "KLIENCI" z treningowej bazy BANK_v4

CREATE TABLE Klienci
    (
         ID_klienta INT CONSTRAINT PK_klient PRIMARY KEY
        ,ID_oddzialu INT CONSTRAINT FK_ID_oddzial_Klienci REFERENCES Oddzialy(ID_oddzialu)
        ,imie NVARCHAR(20)
        ,nazwisko NVARCHAR(25)
        ,telefon INT
        ,mail NVARCHAR(50)
        ,dataUrodzenia DATE
        ,dataZalozeniaKonta DATE
        ,dataZamknieciaKonta DATE
    );

 


Zobacz także: create database


    

SQL CREATE DATABASE

SQL CREATE DATABASE


    


DEFINICJA

Wyrażenie CREATE DATABASE służy do utworzenia nowej bazy danych. To pierwszy etap do stworzenia w pełni funkcjonalnej bazy danych. Kolejnymi krokami są stworzenie tabel i kolumn w tabelach. Oczywiście przy tworzeniu struktury danych nie należy zapomnieć o zdefiniowaniu powiązań między poszczególnymi tabelami w bazie danych. Wróćmy jednak do polecenia CREATE DATABASE, poniżej przykład utworzenia nowej bazy danych o nazwie "nowa_baza".

CREATE DATABASE nowa_baza;

 


Zobacz także: create table


    

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.

sql intersect

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 UNION ALL

SQL UNION ALL


    


DEFINICJA

Za pomocą polecenia UNION możemy połączyć wyniki dwóch lub więcej zapytań. UNION ALL jest wariantem polecenia UNION. W pierwszym przypadku, czyli UNION, wynikiem będą oba połączone wyniki zapytań ale bez wartości powtarzających się. UNION ALL sprawi, że połączymy oba wyniki zapytań z wartościami powtarzającymi się, patrz obrazek poniżej.

SQL UNION

sql union

SQL UNION ALL

sql union all

SQL UNION składnia

SELECT
    nazwa_kolumny1 , nazwa_kolumny2 , …
FROM
    nazwa_tabeli1

UNION

SELECT
    nazwa_kolumny1 , nazwa_kolumny2 , …
FROM
    nazwa_tabeli2

Pamiętaj, UNION łączy dwa zapytania (zbiory) ale wyświetla tylko unikalne wartości.

Natomiast UNION ALL wyświetla wszystkie rekordy z łączonych zapytań.

SQL UNION ALL składnia

SELECT
    nazwa_kolumny1 , nazwa_kolumny2 , …
FROM
    nazwa_tabeli1

UNION ALL

SELECT
    nazwa_kolumny1 , nazwa_kolumny2 , …
FROM
    nazwa_tabeli2

 


Zobacz także: union , except , intersect


   

SQL IS NULL

SQL IS NULL


    


DEFINICJA

Polecenie IS NULL jest związane z wartością NULL, czyli z wartością nieznaną (więcej o wartości NULL tutaj). Za pomocą polecenia IS NULL możemy np. wyszukać  wartości NULL w bazie (przykład poniżej) .


Przykład zastosowania IS NULL (baza AdventureWorks) 

Wyświetl pracowników (z tabeli Person.Person) którzy NIE mają drugiego imienia.

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

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


lub filtrować wyniki zapytania do rekordów które w wynikach mają wartość NULL (przykład poniżej).

Przykład zastosowania IS NULL (baza AdventureWorks) 

Wyświetl listę produktów które NIE były ani razu, 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ą polecenia LEFT JOIN. Jeżeli produkt nie znalazł się w żadnym z zamówień w ostatniej kolumnie wyniku zapytania pojawi nam się wartość NULL

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:

kurs sql is not null

Ż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 nigdy NIE znalazły się w zamówieniach musimy dodać warunek: pokaż produkty które w polu ProductID (z tabeli Sales.SalesOrderDetails) ma wartość 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 NULL    
GROUP BY
    P.ProductID, P.Name, P.ProductNumber, D.ProductID   

 


    

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ą). O wartości NULL możesz więcej przeczytać tutaj. 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:

kurs sql is not null

Ż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 wartość NULL

SQL wartość NULL


    


DEFINICJA

NULL jest reprezentacją nieznanych danych/wartości. Jest bardzo często mylone, przez początkujących programistów, z wartością zero. Wartości te nie są równoważne. NULL mówi nam o tym, że nie znamy danej wartości. Żeby wartość taka znalazła się w bazie to przy tworzeniu tabel musimy pamiętać o tym, żeby "powiedzieć bazie", że takie wartości mogą się w danej kolumnie pojawić. Poniżej przykład tworzenia nowej tabeli z kolumnami w SQL SERVER za pomocą kreatora, zobaczymy tam checkbox którego zaznaczenie zezwana na przechowywanie wartości NULL w danej kolumnie.

kurs sql null sql server

Możemy "ręcznie" utworzyć tabelę i zdefiniowac które kolumny mogą a które nie mogą przechowywac wartości NULL. Poniżej przykład kodu który utworzy nam tabelę "pracownicy". Zdefiniujemy w nim, że pola ID, Imię i Nazwisko nie mogą przechowywać wartości NULL bo chyba nie ma takiej osoby która nie miałaby imienia i nazwiska, ale nie każdy ma drugie imię i tutaj zdefiniujemy, że kolumna ta może przechowywać wartości NULL.

CREATE TABLE PRACOWNICY
(
   ID integer NOT NULL,
   Imie varchar(30) NOT NULL,
   Drugie_imie varchar (30),
   Nazwisko varchar (30) NOT NULL
);

Z wartością NULL spotkamy się głównie w dwóch przypadkach. Pierwszy przypadek to taki, jak opisałem wyżej, że w samej tabeli znajdą się wartości NULL (obrazek poniżej, przykład tabeli "Employees" z treningowej bazy Northwind).

kurs sql null sql server

A drugi przypadek jest taki, że NULL może nam się pojawić przy zapytaniach do bazy np. przy różnego rodzaju łączeniach tabel kiedy wymuszamy połączenie konkretnego typu np. LEFT JOIN i system nie może we wszytkich przypadkach dopasować danych "po obu stronach". W sytuacji kiedy system nie znajdzie dopasowania wartości w to miejsce wstawi NULL.

Z wartością NULL związane są dwa polecenie IS NULL i IS NOT NULL. Jak sama nazwa wskazuje kiedy chcemy znaleść wartości NULL korzystamy z IS NULL a jeżeli chcemy "wyłączyć" wartości NULL (czyli wskazać wszystkie wartości oprócz NULL) to korzystamy z IS NOT NULL.