Temat: Podstawowe pojęcia dotyczące lokalnej sieci komputerowej

Temat 2: Strukturalny język
zapytań – charakterystyka
składni
Jak każdy język komputerowy, również strukturalny język zapytań SQL posiada reguły, które muszą
być spełnione, aby zbudować poprawną instrukcję. Instrukcja SQL zaczyna się poleceniem
(słowem kluczowym) określającym operację, którą zamierzamy wykonać. Po słowie kluczowym
mogą znaleźć się klauzule (dookreślenia dla słowa kluczowego).
Oprócz tego możemy wyróżnić pięć głównych kategorii składniowych zamieszczonych w tabeli
dalej.
Instrukcje języka SQL, bez względu na ich pogrupowanie czy implementacje, zostaną omówione na
przykładzie baz danych PostgreeSQL, MySQL, Access. MySQL jest SZBD bardzo często
spotykanym w usługach hostingowych.
Domeny
Podczas tworzenia tabeli definiujemy również typy atrybutów, np. tworzymy dla wybranej kolumny
identyfikator: wiek z zamiarem umieszczenia w niej wieku osób wpisanych na listę promocji towaru.
Wartości, jakie będziemy umieszczać w kolumnie, będą to liczby całkowite z przedziału 18-110
(zakładamy, że produkt adresujemy do osób pełnoletnich). W bazie danych zdefiniujemy taki typ
jako int(3), co oznacza maksymalnie trzycyfrowe liczby całkowite. To, jakie wartości mogą przyjąć
umieszczone w kolumnie dane, nazywane jest dziedziną lub domeną.
Zbiór dopuszczalnych wartości dla co najmniej jednego atrybutu nazywamy domeną.
Lp.
Identyfikatory
Nazwy obiektów
1
Literały
Stałe
2
Operatory
Spójniki
Przykłady
Arytmetyczne – dla tych
wyrażeń można używać
nawiasów ( )
Znakowe
Logiczne
Porównania
Typowe dla SQL operatory
relacji
iloczyn
*
iloraz
/
modulo
%
suma
+
różnica
-
konkatencja
+ lub ||
symbol wieloznaczny (zastępujący dowolną
liczbę znaków)
%
symbol wieloznaczny (zastępujący jeden
znak)
-
koniunkcja
AND
alternatywa
OR
negacja
NOT
równy
=
mniejszy
<
większy
>
mniejszy lub równy
<=
większy lub równy
=>
różny
!= lub <>
operator przynależności do zbioru przedziału
domkniętego
BETWEN n AND n
operator przynależności do zbioru
IN (...)
operator dopasowania do wzorca
LIKE
operator dopasowania do wyrażenia
regularnego
REGEXP, RLIKE
operator porównania sprawdzający
występowanie znacznika NULL
IS NULL
Lp.
Identyfikatory
Nazwy obiektów
Przykłady
3
Słowa kluczowe
Wyrazy interpretowane
przez serwer w
określony sposób
Słowa kluczowe to zarezerwowane – podobnie jak w językach programowania – ciągi
znaków. Możemy do nich zaliczyć:
4
Komentarze
Ignorowane
Instrukcje
CREATE, SELECT
Klauzule
WHERE, JOIN
Nazwy typów danych
INTEGER, CHAR
Nazwy funkcji systemowych
ISNULL, ABS
Nazwy zarezerwowane do użycia w przyszłości
zależnie od bazy danych
Podwójny znak myślnika (–) to komentarz, którego używamy, aby określony wiersz nie był
interpretowany.
Jeśli chcemy, aby został zignorowany więcej niż jeden wiersz, warto posłużyć się
komentarzem, który odnosi się do bloku instrukcji:
/* tu
znajduje się
blok instrukcji
*/
5
Operator
konkatencji
Operator ten pozwala łączyć atrybut z:
- innym atrybutem
- literałem
- wyrażeniem arytmetycznym
- wartością stałą
Na podstawie tej operacji tworzona jest jedna wynikowa kolumna.
Definicja wskazuje, iż w obrębie jednej relacji kilka atrybutów może mieć różne domeny (każdy atrybut na inną
domenę – tekst, liczby całkowite, daty), może też się zdarzyć, że wszystkie atrybuty w relacji będą odnosić się do
jednej domeny (jako zbioru dopuszczalnych wartości). Domena definiuje zbiór dopuszczalnych wartości atrybutu
niezależnie od czasu.
Podobnie jak w każdym języku programowania, podstawą elementów języka SQL są pojedyncze znaki. Zaliczamy
do nich:
-wielkie litery A-Z
-małe litery a-z
-cyfry 0-9
-zbiór znaków specjalnych: „ % ‘ ( ) * + , - . / : ; = < > ? _ |
Choć implementacje SQL mogą się od siebie różnić, wszystkie musza obsługiwać zbiór znaków o nazwie
SQL_TEXT.
Słowa kluczowe w języku SQL
Słowami kluczowymi są słowa zastrzeżone, ponieważ mają już przypisane co najmniej jedno znaczenie.
Podobnie jak w językach programowania, słowa if, begin, else są słowami kluczowymi, analogicznie jak w języku
SQL.
Należy pamiętać, że sformułowanie „zastrzeżone” oznacza, że nazwy takiej nie można użyć jako identyfikatora,
np. nie możemy kolumny nazwać SELECT. W związku z tym należy zapoznać się nie tylko ze słowami
kluczowymi standardu SQL, lecz także implementacjami tego języka funkcjonującymi w SZBD: MySQL,
PostgreSQL, Access, itp.. Dokumentację tych odmian możemy znaleźć w dokumentacji oferowanej przez
producentów.
Słowo kluczowe DISTINCT. Podczas pracy z bazą danych możemy obsługiwać tabele zawierające powtarzające
się wartości tego samego atrybutu w kolejnych krotkach. DISTINCT umożliwia eliminowanie duplikatów, wartości
kolejnych wierszy oraz kolumn – w zależności od użycia.
Klauzula ORDER BY. To klauzula służąca uporządkowaniu zwracanego wyniku. Pamiętać należy, iż stosujemy ją
jako ostatnią klauzulę polecenia SELECT. Dane segregowane są domyślnie rosnąco lub według ustawień bazy
danych.
Operator BETWEEN…AND. Używany jest do sprawdzenia, czy wartość znajduje się w podanym przedziale.
Operator IN. Służy do sprawdzenia, czy wartość znajduje się na zdefiniowanej liście.
Operator LIKE. Służy do wybierania wartości, które odpowiadają wcześniej ustalonemu wzorcowi. Wzorzec
ustalamy za pomocą symboli:
-% - odpowiada dowolnemu ciągowi znaków
- _ - odpowiada jednemu dowolnemu znakowi.
Operator ISNULL. Służy do wyszukiwania wartości NULL.
Literały
W językach programowania często zachodzi potrzeba posługiwania się literałami definiowanymi jako stałe
dosłowne.
Przykład:
Jeśli napisalibyśmy program, którego celem byłoby wyświetlenie napisu: „Witaj świecie” (Hello World),
posłużylibyśmy się następującym kodem SQL:
SELECT ‘Witaj świecie’;
W bazie PostgreSQL wynik działania takiej instrukcji przedstawiony jest na ilustracji.
Użyte pomiędzy znakami cudzysłowu Witaj świecie jest literałem – ciągiem znaków, danych, przytaczanym
dosłownie.
W języku SQL dostępne są następujące rodzaje literałów:
-napisy, ciągi znaków w pojedynczych cudzysłowach, np. ‘ulica’, ‘1410’;
-zapisy bitowe poprzedza się literą B i umieszcza w cudzysłowach, np. B’1101’;
-napisy szesnastkowe poprzedza się literą X, np. X’ FA’;
-dokładne wartości liczbowe zapisywane jako liczba dziesiętna – ze znakiem lub bez – z możliwością
zastosowania kropki dziesiętnej, np. -25.3 lub 0.07.
Typy danych
W języku SQL posługujemy się typami danych na etapie określania, jakiego typu będą kolumny (atrybuty) tabeli
(relacji). Język SQL obsługuje wiele typów danych, jednak – podobnie jak w przypadku słów kluczowych –
najlepsze efekty uzyskamy, zapoznając się z dokumentacją wersji języka SQL (dialektu) występującego w
wybranym przez nas SZBD. Pomiędzy określeniem typów danych dla zmiennych relacyjnych w programach
PostgreSQL, MySQL, Access mogą zachodzić różnice, których znajomość pozwoli uniknąć błędów, a także w
pełni wykorzystać wydajność tych aplikacji.
Typy danych w PostgreSQL
Gdy tworzymy lub modyfikujemy tabele, zachodzi potrzeba określenia typu danych, które będą przechowywane w
kolumnie. Warto zauważyć, że nawet po określeniu typu dla kolumny nadal możemy go zmodyfikować.
Typu numeryczne
Typ integer (liczby całkowite) zawiera:
-smallint typ zalecany dla kolumn, których pojedyncze wartości wierszy można przechowywać w 2 bajtach pamięci
-integer typ dla liczb całkowitych, które stanowią wartości większe niż smallint, ponieważ do zapisania mogą
wymagać czterech bajtów pamięci
-bigint typ wybierany dla liczb całkowitych, które potrzebują 8 bajtów pamięci
Typ NUMERIC
Może zajść konieczność umieszczania w kolumnach baz danych dużych wartości liczbowych, takich jak kwoty
pieniężne.
Do tego celu stosowany jest typ Numeric, który pozwala na wykonywanie dokładnych przeliczeń.
Aby przeprowadzić działania na dużych wartościach liczbowych, należy określić skalę i precyzję:
p – precyzja to ilość liczb występujących przed przecinkiem, która służyć będzie do wykonywania obliczeń (ta
wartość musi być dodatnia)
s – skala, dopuszczalna liczba miejsc po przecinku (ta wartość nie może być ujemna – zatem równa zeru lub
dodatnia)
Typ numeryczny w przypadku niezdefiniowanych wyników operacji może również zaakceptować i przechować
tzw. Wartość NaN (określane jako Not a Number)
NUMERIC(S,P)
Przykład NUMERIC(8,2) będzie odpowiedni do przechowywania liczbowych wartości do: 99999999,99 (8 wartości
przed przecinkiem i dwie po przecinku).
Typy zmiennoprzecinkowe (floating point), znane w matematyce jako liczby rzeczywiste używane są przy
definiowaniu kolumn, które zawierać będą wartości ułamkowe:
-real: 1E-37 do 1E+37 z precyzją przynajmniej 6 znaków po przecinku
-double: 1E-307 do 1E+308 z precyzją przynajmniej 15 znaków po przecinku.
-Te typy danych akceptują specjalne wartości nienumeryczne, włączając w to nieskończoność oraz wartość NaN.
Typ SERIAL
Podczas tworzenia tabeli zwykle zachodzi potrzeba nadania każdej krotce unikalnej wartości. Funkcję taką
spełnia kolumna, którą wyznacza twórca tabeli jako klucz główny.
Wartości w takiej kolumnie mogą być uzupełniane automatycznie (autoincrement) i nie mogą być puste(NOT
NULL).
Typy znakowe CHARACTER
W PostgreSQL występują trzy główne rodzaje typu znakowego:
-Character (Char)
-Character Varying (Varchar)
-Text
Gdy używamy typu Char lub Varchar, możemy określić maksymalną liczbę znaków, które przechowywane będą w
pojedynczej krotce definiowanej kolumny.
Przykład:
Jeżeli, przykładowo, kolumna musi zawierać imiona klientów, wówczas po zdefiniowaniu jej typu jako Varchar
umieszczamy numer znaków, który według naszych założeń odpowiada maksymalnej liczbie znaków w imieniu.
Jeśli założymy, że najdłuższe imię, jakie może wystąpić, składa się z 10 znaków, możemy przyjąć, że kolumnę
Imie zdefiniujemy jako Varchar(10) lub Char(10). Oznacza to, że kolumna może przechować wartości do 10
znaków, np. polecenie:
INSERT INTO TABLE klienci Values (‘Przemyslaw’);
zostanie wykonane prawidłowo. Jednak gdy chcemy wprowadzić imię Maksymilian, zawierające 11 znaków,
napotkamy przeszkody. Dlatego definiując typ kolumn, musimy wykazać się umiejętnością przewidywania nawet
rzadkich, dość ekstremalnych sytuacji, gdy np. klientem instytucji może zostać osoba pochodzenia arabskiego
bądź azjatyckiego i liczba znaków w jej imieniu może przekraczać 14 i zawierać spację, np. Abdul Muhaymin.
Gdy rozważamy użycie typu Char lub Varchar do zdefiniowania kolumny, może pojawić się pytanie o to, czym typ
Char różni się od Varchar. Odpowiedź dotyczy ilości pamięci, jaką system zarezerwuje w celu przechowywania
kolejnych krotek kolumny.
Jeśli kolumnę zdefiniujemy jako Varchar(10), wówczas system na przechowanie każdej krotki dynamicznie
przydzieli tyle miejsca, ile będzie wymagał wprowadzany ciąg znaków, przyjmując, że maksymalna liczba znaków
wyniesie 10. Np. gdy kolumnę IMIE w tabeli klienci zdefiniujemy jako Varchar(10) i umieścimy imię Maciek,
wówczas system zarezerwuje tylko sześć znaków. Jeśli tę samą kolumnę zdefiniujemy jako Char(10), system dla
każdej krotki zarezerwuje 10 znaków bez względu na to, czy w danej krotce liczba znaków jest o wiele mniejsza
od dziesięciu, np. w przypadku imienia Jan.
Gdy do kolumny tabeli wprowadzimy ciągi znaków, umieszczamy je w pojedynczych cudzysłowach ‘tekst
wprowadzany’.
Typ daty i znaczniki czasu
Typy, którymi dysponujemy dla kolumn mających przechowywać czas, to:
-Timestamp without time zone – znacznik czasu bez strefy czasowej
-Timestamp with time zone – znacznik czasu ze strefą czasową
-Date – data
-Time without timezone – czas bez strefy czasowej
-Time with timezone – czas ze strefą czasową
-Interval – interwał (przedział czasowy).
Dla czasu i znaczników czasowych zawierających sekundy można określić precyzję.
Precyzję określa numer ułamkowych cyfr w polu sekund.
Podczas wprowadzania wartości czasowych należy pamiętać o ujęciu ich w znaki pojedynczego cudzysłowu.
Operując czasem, możemy używać 12- i 24- godzinnej notacji, przyjmując, że czas w notacji 12 godzinnej
wprowadzamy jako ’12:10 AM’ lub ’01:12 PM’. W przypadku czasu ze strefą czasową w notacji 12 godzinnej
wprowadzona wartość możę mieć postać ’12:15 pm-5’ albo ’12:15 am EST’.
SZBD PostgreSQL pozwala również na użycie specjalnych wartości, takich jak: nieskończoność, minus
nieskończoność, jutro, wczoraj.
Konfigurację tego parametru można przeprowadzić w pliku postgresql.conf
Ustawienia formy dla wyświetlania daty wykonujemy poleceniem:
SET DATESTYLE TO POSTGRES, MDY;
Parametr POSTGRES odnosi się do stylu, jaki zostanie przyjęty w wyświetlanej dacie, i może zostać zastąpiony
np. parametrami ISO, SQL, GERMAN. Opcja MDY określa kolejność w wyświetlanej dacie – Miesiąc Dzień Rok i
może zostać zastąpiona DMY (dzień, miesiąc, rok).
W postgresql typ Bolean może przyjmować wartości:
-Prawda (True, T, Yes, Y, On, 1)
Wartości znajdujące się w nawiasie oprócz True, które jest słowem kluczowym, umieszczamy w pojedynczych
cudzysłowach
-Fałsz (False, F, No, N, Off, 0)
Podobnie jak w powyższym przykładzie, wartość z nawiasu, oprócz False, również umieszczamy w pojedynczych
cudzysłowach.
Wartość nieznana reprezentowana jest przez NULL
Wartości puste
Na szczególną uwagę podczas nauki teorii relacyjnych baz danych zasługuje właściwa interpretacja wartość
pustej NULL. Wielokrotną przyczyną błędów jest utożsamianie tej wartości pustej NULL. Wartość ta oznacza, że
oznaczone nią pole tabeli jest puste i tym samym nie znajdują się w nim żadne informacje. Dla przykładu,
gdybyśmy umieszczali w kolumnie bazy danych wyniki pomiarów temperatury, umieszczone w niej zero byłoby
informacją, że danego dnia temperatura wynosiła 0 stopni Celsjusza. Wartość NULL oznacza, że pomiar nie
został dokonany i że pole tabeli pozostaje puste. Prowadzi to do wniosku, że NULL nie jest równa zeru (tożsama z
zerem).
NULL zasługuje na szczególną uwagę, ponieważ obok TRUE i FALSE określana jest jako trzeci wartość logiczna.
Podczas stosowania operatorów logicznych OR, AND, NOT użycie argumentu NULL może powodować
następujące różnice w zależności od SZBD.
Dzielenie przez zero jest błędne w ujęciu matematycznym, jednak w rozumieniu niektórych twórców SZBD może
powodować wartość NULL. W PostgreSQL, gdy spróbujemy wykonać dzielenie przez zero, otrzymamy
komunikaat o błędzie:
MySQL wynik dzielenia przez zero sprowadza do braku wartości, umieszczając NULL.
Typ wyliczeniowy
Kolumna może zawierać również wartości typu wyliczeniowego ENUM. W takim wypadku możemy zdefiniować
wartości typu jako np. PONIEDZIAŁEK, WTOREK, ŚRODA, CZWARTEK, PIĄTEK, SOBOTA, NIEDZIELA. Aby
utworzyć własny typ wyliczeniowy, możemy posłużyć się następującą instrukcją SQL:
CREATE TYPE tydzien AS ENUM
(‘PONIEDZIAŁEK’,’WTOREK’,’ŚRODA’,’CZWARTEK’,’PIĄTEK’,’SOBOTA’,’NIEDZIELA’);
Warto zapamiętać, że wartości typu wyliczeniowego umieszczamy w pojedynczych cudzysłowach w trakcie
definiowania typu. Istotne jest również to, iż wartości te są czułe na wielkość liter. Wprowadzenie wartości ‘piątek’
będzie oznaczało, iż jest to wartość inna niż np. ‘Piątek’.
Podczas tworzenia tabeli możemy określić kolumnę nowo utworzonym typem, np.
CREATE TABLE przepracowane_dni (dni tydzien);
Typ wyliczeniowy określa wartość PONIEDZIAŁEK jako najmniejszą, a wartość NIEDZIELA jako największą,
umożliwiając porównywanie tych wartości z innymi.
Typ danych geometrycznych
Używa się go do reprezentowania obiektów dwuwymiarowych.
-PUNKT (point) reprezentuje punkt za pomocą dwóch współrzędnych: x,y
-LINIA (line) definiowana jest za pomocą dwóch punktów: np. A(Xa,Ya) B(Xb,Yb).
Tablice
W PostgreSQL jako wartości poszczególnych krotek kolumny mogą być przechowywane nie tylko pojedyncze
wartości, lecz także całe tablice. Ponieważ baza danych zawierająca tablice nie jest wartością niepodzielną
(atomową), zastosowanie jej jest sprzeczne z teorią relacyjnych baz danych i w takim wypadku tabela nie spełnia
zasad pierwszej postaci normalnej.
Tablice możemy tworzyć, korzystając nie tylko z samodzielnie zdefiniowanych typów, lecz także z typów
wbudowanych, np.
CREATE TABLE tabelka (kolumna1 INTEGER[5]);
Polecenie tworzy tabelę o nazwie tabelka, zawierającą kolumnę – kolumna1, z pięcioelementowymi tablicami
typu integer. Tablice mogą być również wielowymiarowe, np. kolumna2 INTEGER [ ] [ ].
Należy pamiętać, że takiej tabeli nie można nazwać relacją.
Identyfikatory
Obiekty bazy danych, takie jak baza, tabela, kolumna, muszą mieć niepowtarzalną nazwę, tzn. swój identyfikator,
który musi być zgodny ze standardem języka SQL.
Identyfikatory:
-Nie mogą mieć więcej niż 128 znaków
-Mogą zawierać litery, cyfry i symbole: @ $ # (pozostałe symbole, takie jak znak spacji, są niedozwolone)
-Identyfikatory nie mogą zaczynać się cyfrą, natomiast identyfikatory zaczynające się symbolem @ oznaczają
zmienną, a zaczynające się od # oznaczają obiekt tymczasowy.
Identyfikatory nie mogą być również słowami kluczowymi języka SQL.
Aby ułatwić sobie pracę, zwłaszcza projektując bazy danych, które w przyszłości miałyby być obsługiwane przez
innych ludzi, warto stosować ustalone konwencje nadawania identyfikatorów. Takie postępowanie sprawi, że
tworzone tabele i kod SQL będą bardziej czytelne dla nas i dla innych. Nazwy identyfikatorów powinny być
możliwie krótkie, jednoznacznie opisujące obiekt. Gdy identyfikator składa się z kilku wyrazów, powinny być
pisane bez spacji, każdy wyraz wielką literą, z wyjątkiem pierwszego, np. przykładIdentyfikatoraTabeli. Gdy
tworzymy funkcje, procedury lub wyzwalacze, powinniśmy używać przedrostków:
-udf – funkcja zdefiniowana przez użytkownika
-usp – procedura zdefiniowana przez użytkownika
-v – widok
-tr – wyzwalacz
Podczas tworzenia identyfikatorów w PostgreSQL należy zachować ostrożność w dobieraniu wielkości liter.
Identyfikatory, w których użyjemy na przemian wielkiej i małej litery, wymagają cudzysłowu. Gdy utworzymy tabelę
o nazwie: kLiEnCi, będziemy zmuszeni do identycznego wpisania takiej nazwy w cudzysłowie, np.
SELECT * FROM „kLiEnCi”;
Gdy w przeciwieństwie do powyższego przykładu dobierzemy konsekwentnie wielkość znaków dla identyfikatora
tabeli, stosując wyłącznie małe litery, np.
CREATE TABLE klienci;
będziemy mogli wskazywać na tabelę przy użyciu dużych liter, małych liter lub dowolnej kombinacji dużych i
małych liter na przemian, np.
SELECT * FROM kLiEnCi;
SELECT * FROM klienci;
SELECT * FROM KLIENCI;
Wszystkie trzy powyższe instrukcje zadziałają bez konieczności użycia cudzysłowu na identyfikatorze tabeli.
Wielkość identyfikatorów w MySQL
Podczas tworzenia tabel w MySQL rozróżnianie wielkich i małych liter jest obowiązkowe. Do tabel odwołujemy się
zaś, podając identyfikator identyczny pod względem wielkości liter z tym, który ma tabela.
Jeśli nazwiemy tabelę np. klienci, wówczas skuteczne wywołanie tabeli nastąpi jedynie przez umieszczenie takiej
samej nazwy w instrukcji, np.
SELECT * FROM klienci;
Analogicznie, jeśli nazwa tabeli to Klienci, wówczas identyczną nazwę będziemy musieli umieścić w instrukcji
SQL, np.
SELECT * FROM Klienci;
W przeciwieństwie do PostrgreSQL nie używamy cudzysłowu, jedynie zmuszeni jesteśmy do konsekwentnego
dobierania nazw i stosowania się do raz przyjętej nomenklatury.