Temat 2: Strukturalny język zapytań – charakterystyka składni

1. Temat 2: Strukturalny język zapytań – charakterystyka składni

2. 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ą.

5. 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.

6. 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.

7. 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

8. 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).

9. 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

10. 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;

11. 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:

12. 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.

13. 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.

14. 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.

15. 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.