Lekcja 34. DDL - View

DDL (Data Definition Language) jest podzbiorem języka SQL Firebird i służy do tworzenia, modyfikowania oraz usuwania obiektów bazy danych.

Widok (View) jest predefiniowanym zapytaniem jednej lub wielu tabel (zwanych tabelami bazowymi). Pobieranie informacji z perspektywy odbywa się w taki sposób jak pobieranie informacji z tabeli. Wystarczy jedynie umieścić nazwę widoku w klauzuli FROM.

Widoki mają kilka zalet:

• Umożliwiają umieszczenie złożonego zapytania w widoku i przyznania do niego dostępu użytkownikom. To pozwala ukryć złożoność przez użytkownikami.
• Powalają na uniemożliwienie użytkownikom bezpośredniego wysłania zapytań do tabel bazy danych, przyznając im dostęp jedynie do widoków.
• Umożliwiają przyznanie widokom dostępu jedynie do określonych wierszy tabel bazodanowych, co pozwala na ukrywanie wierszy przed użytkownikami.

Z tej lekcji dowiesz się jak tworzyć widoki i ich używać, modyfikować widoki, a także je usuwać.

Tworzenie widoku

Do tworzenia widoku służy instrukcja CREATE VIEW.  Perspektywy proste korzystają z jednej tabeli bazowej. W poniższym przykładzie tworzymy widok ENTRY_LEVEL_JOBS, który pobiera z tabeli wiesze (a właściwie wartości kolumn JOB_CODE i JOB_TITLE), dla których MAX_SALARY jest mniejsze niż 50000:

Odpytywanie widoku

Po utworzeniu widoku możemy go użyć do uzyskania dostępu do tabeli. Poniższe zapytanie zwraca wiesze z widoku ENTRY_LEVEL_JOBS:

Modyfikowanie widoku

Za pomocą instrukcji ALTER VIEW można modyfikować zapisany widok. W poniższym przykładzie użyto tej instrukcji do modyfikacji widoku ENTRY_LEVEL_JOBS:

 Poniższe zapytanie zwraca wiesze z widoku ENTRY_LEVEL_JOBS:

Usuwanie widoku

Do usuwania widoku służy instrukcja DROP VIEW. W poniższym przykładzie usuwany jest widok ENTRY_LEVEL_JOBS:

Kod źródłowy

Lekcja 20. Złączenia wewnętrzne.

Złączenia wewnętrzne zwracają wiersz tylko wtedy, gdy kolumny w złączeniu spełniają warunek złączenia. To oznacza, że jeżeli wiersz w jednej kolumn w warunku złączenia posiada wartość NULL, nie zostanie on zwrócony. Prezentowane dotychczas złączenia (Lekcja 9. Instrukcja SELECT wykorzystująca klika tabel.) były przykładami złączeń wewnętrznych.

Wcześniej do wykonania złączenia wewnętrznego stosowaliśmy poniższe zapytanie zgodne ze standardem SQL/86: 

W standardzie SQL/92 do wykonywania złączeń wewnętrznych służą klauzule INNER JOIN i ON. Poniższe zapytanie ma takie samo znaczenie jak zapytanie przedstawione powyżej, użyto w nim jednak klauzul INNER JOIN i ON:

W zapytaniu słowo INNER jest opcjonalne.

Upraszczanie złączeń za pomocą słowa kluczowego USING

Standard SQL/92 pozwala jeszcze bardziej uprościć warunek złączenia przez zastosowanie słowa kluczowego USING. Występują tutaj jednak pewne ograniczenia:

•              Zapytanie musi wykorzystywać równozłączenie (=)
•              Kolumny w równozłączeniu muszą mieć taką samą nazwę

Równozłączenia stanowią większość wykonywanych złączeń, a jeżeli będziemy zawsze nazywali klucze obce tak jak odpowiednie klucze główne, warunki te będą spełnione.

W poniższym zapytaniu wykorzystano słowo kluczowe USING zamiast ON:

 

Gdy kolumny występujące w warunku łączącym są tej samej nazwy możemy również użyć słowa kluczowego NATURAL JOIN. Jest to jednak odradzane, ponieważ może się trafić sytuacja gdy będzie więcej dopasowań kolumn o tej samej nazwie niż jedna. Poniższe zapytanie ma takie samo znaczenie jak zapytanie przedstawione powyżej, użyto w nim jednak klauzul NATURAL JOIN:

 

Wykonywanie złączeń wewnętrznych obejmujących więcej niż dwie tabele

Wcześniej opracowaliśmy następujące zapytanie, pobierające wiesze z tabel employee, department, job, country :

Poniższe zapytanie ma takie samo znaczenie, ale wykorzystuje składnię SQL/92. Użyto w nim klauzul INNER JOIN i ON:

Kod źródłowy

Lekcja 9. Instrukcja SELECT wykorzystująca klika tabel.

Instrukcje SELECT wykorzystujące dwie tabele

Schematy bazy danych zawierają zwykle więcej niż jedną tabelę. Na przykład w schemacie employee znajdują się tabele przechowujące informacje o pracownikach, klientach, zarobkach itd. Jak dotąd wszystkie zapytania przedstawione w tym kursie pobierały wiersze tylko z jednej tabeli. W rzeczywistości często chcemy pobrać dane z kilku tabel. Możemy na przykład chcieć uzyskać nazwę klienta, kraj oraz walutę w obowiązującą w tym kraju.

W tej lekcji nauczysz się tworzyć zapytania wykorzystujące dwie tabele. Dowiesz się także, jak wykorzystywać zapytania pracujące na jeszcze większej liczbie tabel.

Powróćmy do naszego przykładu. Załóżmy, że chcemy pobrać nazwę klienta o numerze 1007 oraz kraj i walutę obowiązującą w tym kraju. Nazwa oraz kraj klienta jest przechowywana w kolumnie customer oraz country tabeli customer, a waluta – w kolumnie currency tabeli country. Tabele są ze sobą powiązane za pośrednictwem kolumny klucza obcego country. Kolumna ta (klucz obcy) w tabeli customer wskazuje na kolumnę country (klucz główny) tabeli country.

Poniższe zapytanie pobiera z tabeli customer kolumny customer i country dla klienta o numerze 1007:

Następne zapytanie pobiera z tabeli country kolumnę currnecy dla country równego USA:

Dowidzieliśmy się, że klient numer 1007 mieszka w USA, a obowiązująca tam walutą jest dolar. Musieliśmy w tym celu wykonać dwa zapytania. Możemy jednak otrzymać takie same informacje stosując jedno zapytanie. W takiej sytuacji należy zastosować w zapytaniu złączenie tabel. Aby to zrobić, należy dołączyć obie tabele do klauzuli FROM zapytania, a także uwzględnić odpowiednie kolumny ze wszystkich tabel w klauzuli WHERE.

W naszym przykładzie klauzula FROM będzie miała postać:

Natomiast klauzula WHERE:

Złączenie jest pierwszym warunkiem klauzuli WHERE (customer.country = country.country). Najczęściej w złączeniu są stosowane kolumny będące kluczem głównym jednej tabeli i kluczem obcym drugiej tabeli. Drugi warunek w klauzuli WHERE (customer.cust_no = 1007) pobiera klienta o numerze 1007.

Jak możemy zauważyć, w klauzuli WHERE są umieszczone zarówno nazwy kolumn, jak i tabel. Jest to spowodowane tym, że kolumna ­country znajduje się i w tabeli customer, i country, musimy więc w jakiś sposób określić tabelę z kolumną, której chcemy użyć. Gdyby kolumny miały różne nazwy, moglibyśmy pominąć nazwy tabel, należy jednak zawsze je umieszczać, aby było jasne, skąd pochodzi dana kolumna.

Klauzula SELECT w naszym zapytaniu będzie miała postać:

Zapytanie zatem ma postać:

To jedno zapytanie zwraca nazwę klienta, kraj i walutę obowiązującą w tym kraju. Kolejne zapytanie pobiera wszystkich klientów i porządkuje ich według kolumny customer.customer:

Używanie aliasów tabel

Powyżej utworzyliśmy następujące zapytanie:

Możemy zauważyć, że nazwy tabel customer i country zostały użyte zarówno w klauzuli SELECT, jak i WHERE. Możliwe jest zdefiniowanie aliasów tabel w klauzuli FROM i korzystanie z nich, gdy odwołujemy się do tabeli w innych miejscach w zapytaniu.

Na przykład w poniższym zapytaniu użyto aliasu cu dla tabeli customer  i co dla tabeli country. Należy zauważyć, że aliasy są definiowane w klauzuli  FROM i umieszczane przed nazwami kolumn w innych fragmentach zapytania:

Aliasy tabel zwiększają czytelność zapytań, zwłaszcza gdy piszemy długie zapytania, wykorzystujące wiele tabel.

Iloczyny kartezjańskie

Jeżeli warunek złączenia nie zostanie zdefiniowany, złączone zostanę wszystkie wiersze z jednej tabeli ze wszystkimi wierszami drugiej. Taki zestaw wyników nazywamy iloczynem kartezjańskim.

Załóżmy, że w jednej tabeli znajduje się 30 wierszy, a w drugiej 20. Jeżeli wybierzemy kolumny z tych tabel bez warunku złączenia, otrzymamy w wyniku 600 wierszy (30 * 20), ponieważ każdy wiersz z pierwszej tabeli zostanie złączony z każdym wierszem z drugiej tabeli.

Poniższy przykład przestawia fragment iloczynu kartezjański tabel customer i country:

Zapytanie zwróciło 240 wierszy, ponieważ tabela ­customer zawiera 15 wierszy,  a tabela country zawiera 16 wierszy. Wynika to z prostych obliczeń 15 * 16 = 240.

Instrukcje SELECT wykorzystujące więcej niż dwie tabele

Złączenia mogą obejmować dowolną liczbę tabel. Liczba złączeń potrzebnych w klauzuli WHERE jest równa liczbie tabel wykorzystywanych w zapytaniu – 1.

Rozważmy bardziej skomplikowany przykład, wykorzystujące cztery tabele, który pobierze:

•          imię oraz nazwisko pracownika (z tabeli employee),
•          nazwę działu, w którym pracuje (z tabeli department),
•          stanowisko, na którym pracuje (z tabeli job) ,
•          walutę kraju, w którym pracuje (z tabeli country).

Korzystamy z czterech tabel i dlatego potrzebujemy trzech złączeń. Poniżej zostały wymienione konieczne złączenia:

•        Aby uzyskać informacje na temat działu, w którym pracuje dany pracownik, musimy złączyć tabele employee i department, wykorzystują kolumny dept_no (employee.dept_no = department.dept_no).
•         Aby dowiedzieć się na jakim stanowisku pracuje dany pracownik, musimy złączyć tabele employee i job, wykorzystując kolumny job_code (employee.job_code = job.job_code).
•          Aby uzyskać walutę kraju w którym pracuje dany pracownik, musimy złączyć tabele job i country, wykorzystując kolumny job­_country i country (job.job_country = country.country).

Te złączenia zostały zastosowane w poniższym zapytaniu, które pobiera wszystkie ww. dane dla pracownika o numerze 2 (employee.emp_no = 2) :

Prezentowane dotychczas zapytania pobierające dane z wielu tabel wykorzystywały w warunkach złączenia operator równości (=), były to więc równozłączenia. 

Kod źródłowy

Lekcja 7. Instrukcja SELECT.

Polecenie SELECT służy do pobierania informacji z tabel bazy danych. W najprostszej postaci tej instrukcji należy określić tabelę i kolumny, z których chcemy pobrać dane.

Wykonywanie instrukcji SELECT

Poniższa instrukcja SELECT pobiera kolumny cust_no, customer oraz phone_no z tabeli customer:

Tuż za słowem kluczowym SELECT należy wpisać nazwy kolumn, z których mają być pobrane dane. Za słowem kluczowym FROM należy wpisać nazwę tabeli. Instrukcję SQL trzeba zakończyć średnikiem (;).

Jeżeli chcemy pobrać wszystkie kolumny z tabeli, możemy użyć gwiazdki (*) zamiast listy kolumn. W poniższym przykładzie gwiazdka została użyta do pobrania wszystkich kolumn z tabeli department:

Za pomocą słowa kluczowego FIRST możemy ograniczyć ilość pobieranych rekordów.  W poniższym przykładzie ilość pobranych rekordów z tabeli department została ograniczona do 5:

Wykorzystanie klauzuli WHERE do wskazywania wierszy do pobrania

Klauzula WHERE ogranicza zakres pobieranych wierszy. Klauzulę WHERE umieszczamy za klauzulą FROM:

SELECT lista elementów

FROM lista tabel

WHERE lista warunków;

W poniższym zapytaniu użyto klauzuli WHERE do pobrania z tabeli customer wiersza, w którym kolumna cust_no ma wartość 1002.

Używanie aliasów kolumn

Gdy pobieramy kolumnę z tabeli, w wynikach jako nagłówek kolumny Firebird wpisuje nawę kolumny wielkimi literami. Na przykład gdy pobieramy kolumnę cust_no, nagłówkiem w wynikach jest CUST_NO. Możemy określić własny nagłówek , stosując alias. W poniższym zapytaniu kolumna cust_no otrzymuje alias NUMBER:

Jeżeli chcemy użyć spacji i zachować wielkość liter w aliasie, należy jego tekst umieścić w cudzysłowie:

Przed tekstem aliasu można również umieścić opcjonalne słowo kluczowe AS:

Łączenie wartości z kolumn za pomocą konkatenacji

Za pomocą konkatenacji można łączyć wartości z kolumn pobrane przez zapytanie, co umożliwia uzyskanie bardziej sensownych wyników. Poniższe zapytanie łączy kolumny first_name oraz last_name z tabeli employee za pomocą operatora konkatenacji (||). Nalży zauważyć, że po kolumnie first_name został dodany znak spacji, a dopiero później kolumna last_name:

Wartości kolumn first_name i last_name zostały połączone w wynikach pod aliasem Employee name.

Wartość null

Wartość specjalna – null – oznacza, że wartość dla danej kolumny jest nieznana. Nie jest ona jednak pustym wpisem.

Jeżeli pobieramy kolumnę, która zawiera wartość null, w jej wynikach zobaczymy <null> .

Można również sprawdzić występowanie wartości null, umieszczając w zapytaniu IS NULL. W poniższym przykładzie zostały zwrócone informacje o projekcie Translator upgrade, ponieważ w tym wierszu w kolumnie team_leader występuje null:

Zdarza się, że chcemy wyświetlić inną wartość w miejsce null. Aby to zrobić musimy posłużyć się funkcją COALESCE(). Funkcja COALESCE() przyjmuje dwa lub więcej parametrów i zwraca pierwszą wartość, która okażę się nie być null. W poniższym zapytaniu COALESCE() zwraca wyrażenie  ‘lack’, jeżeli kolumna team_leader zawiera wartość null:

Wyświetlanie unikatowych wierszy

Załóżmy, że chcemy uzyskać listę walut obowiązujących w krajach znajdujący się w tabeli country. Możemy to zrobić za pomocą poniższego zapytania, które pobiera kolumnę currency z tabeli country:

Jak widzimy w wynikach zwróconych przez zapytanie, niektóre waluty obowiązują w więcej niż jednym kraju i w związku z tym się powtarzają. Można usunąć powtarzające wiersze za pomocą słowa kluczowego DISTINCT. W poniższym zapytaniu użyto go, aby pominąć powtarzające się wiersze:

Na tej liście łatwiej zauważyć, że wszystkich walut jest 11; powtarzające się wiersze zostały pominięte.

Kod źródłowy