Systemy baz danych

DML – Język Manipulacji Danymi

3G grupa 1

Utwórzmy bazę danych na podstawie ćwiczenia 1.10 ze strony webmaster. Wykorzystamy do tego program Query Browser aby nie tracić czasu na wpisywanie pojedynczych poleceń do konsoli.

Sprawdźmy jakie mamy bazy danych i zalogujmy się do naszej:

Sprawdźmy strukturę naszych tabel wprowadzonych za pomocą programu powyżej:

Wybieramy kolumny z tabel

Wiemy już, że instrukcja SELECT służy do pobierania danych z bazy i że z reguły zawiera ona co najmniej dwie klauzule: w klauzuli SELECT określamy interesujące nas kolumny, w klauzuli FROM wskazujemy tabelę, z której pobieramy dane.

Zapytanie zwracające imiona i nazwiska klientów:

Kolejność podawanych nazw kolumn nie jest obojętna. Serwer baz danych zwróci dane, szeregując poszczególne kolumny według kolejności ich występowania w klauzuli SELECT. Czyli wynik wykonania poniższej instrukcji będzie różny od poprzedniego.

Lista nazwisk i imion klientów:

Wyrażenia

W skład wyrażenia mogą wchodzić nazwy kolumn, stałe, wartości liczbowe i ogólnie znane operatory, takie jak: + (dodawania), – (odejmowania), * (mnożenia), / (dzielenia).

Prosty przykład zastosowania funkcji arytmetycznych — zwróćmy uwagę, że ta sama kolumna tabeli może być wielokrotnie zwrócona przez zapytanie:

Stosując funkcje arytmetyczne, możemy łatwo sprawdzić, z jaką marżą sprzedawane są poszczególne towary. Ponieważ oprócz informacji o cenie zakupu (umieszczonej w kolumnie cost_price) przechowujemy (w kolumnie sell_price) informacje o cenie sprzedaży, wystarczy odjąć od ceny sprzedaży cenę zakupu każdego towaru.

Lista cen i marż towarów:

Kolejność wyrażeń w klauzuli SELECT określa kolejność kolumn wyniku zapytania:

Aliasy

Język SQL umożliwia zastąpienie nazwami opisowymi domyślnych, utworzonych podczas tworzenia tabel, nagłówków kolumn. Jest to szczególnie przydatne w przypadku używania wyrażeń. Ponieważ wynik wyrażenia jest obliczany w chwili wykonania instrukcji SELECT, w tabeli nie znajduje się kolumna, w której byłyby przechowywane dane będące wynikiem operacji arytmetycznej. W związku z tym serwer baz danych „nie wie”, jaka miałaby być domyślna nazwa nowej kolumny. Jak widzieliśmy, MySQL za nagłówek nowej kolumny przyjmie wyrażenie arytmetyczne. Aby utworzyć alias dla kolumny, należy bezpośrednio po nazwie, którą chcemy zastąpić, użyć słowa kluczowego AS, a następnie podać nową nazwę kolumny.

Przykład użycia aliasów:

Literały

Oprócz nazw kolumn, ich aliasów i wyrażeń w klauzuli SELECT można umieszczać literały. Literałem jest dowolny ciąg znaków lub liczb. W wyniku umieszczenia w niej literału serwer baz danych do każdego zwróconego wiersza wpisze do odpowiedniej kolumny treść literału. Na przykład do utworzenia raportu informującego o tym, gdzie mieszka dana osoba, można wykorzystać instrukcję pokazaną poniżej.

SQL jest językiem operowania na zbiorach. Dzięki temu dodanie do każdego wiersza wyniku stałej wartości sprowadza się do wymienienia jej w klauzuli SELECT:

Łączenie ciągów znaków

Podstawowym założeniem przy projektowaniu i tworzeniu relacyjnych baz danych jest wyodrębnienie i zapisywanie w odrębnych kolumnach danych elementarnych. Na przykład imiona i nazwiska klientów zamiast w jednej kolumnie, zostały zapisane w odrębnych kolumnach. Zapisane w tabelach dane są podzielone na „atomowe” części, co oprócz wielu zalet mają też jedną wadę — niektóre raporty (tak jak poprzedni) wyglądają dość dziwnie.

W efekcie często chcemy połączyć informacje przechowywane w odrębnych kolumnach i w zestawieniu traktować je jako dane elementarne. Przeprowadzenie takiej operacji w większości serwerów baz danych umożliwia specjalny operator konkatenacji (podwójny znak || albo & lub +). W MySQL-u musimy użyć w tym celu systemowej funkcji CONCAT().

Przykład wywołania funkcji CONCAT:

Parametry wywołania funkcji umieszcza się w nawiasach. Jeżeli funkcja jest wywoływana z większą liczbą parametrów, oddziela się je przecinkami.

Praktyczne wykorzystanie łączenia ciągów znaków:

Niestandardowa funkcja serwera MySQL CONCTAT_WS() pozwala dodatkowo uprościć zapytania, w których łączymy ciągi znaków — jej pierwszym parametrem jest separator (znak lub ciąg znaków, które będą dodane pomiędzy łączonymi kolumnami), pozostałymi — nazwy łączonych kolumn.

Najważniejsze funkcje serwera MySQL:

Eliminacja duplikatów

Domyślnie serwery bazodanowe wyświetlają wszystkie wiersze wchodzące w skład wyniku zapytania, nawet jeżeli w kilku wierszach przechowywana jest taka sama wartość. Dlatego, odczytując identyfikatory klientów z tabeli orderinfo, zobaczymy identyfikator tego samego klienta tyle razy, ile razy składał on u nas zamówienia.

Lista identyfikatorów klientów zawierająca duplikaty:

Do wyeliminowania z powyższego zestawienia powtarzających się wartości służy słowo kluczowe DISTINCT. Słowo DISTINCT musi pojawić się bezpośrednio po słowie kluczowym SELECT i odnosi się do wszystkich kolumn występujących w klauzuli SELECT. Aby w naszym przypadku wyeliminować powtarzające się identyfikatory, wystarczy napisać:

Słowo kluczowe DISTINCT eliminuje powtarzające się wartości wierszy, a nie pojedynczych wyrażeń:

Wartość NULL

Jednym z podstawowych postulatów twórcy teorii relacyjnych baz danych jest postulat wartości Null. Na jego podstawie w każdym serwerze baz danych jest dostępna specjalna wartość reprezentująca wartość nieokreśloną, brakującą lub nieznaną. Jest to wartość różna od wszelkich konkretnych wartości, w szczególności — od ciągu pustego (" ") i zera (0). Charakterystyczne dla wartości Null jest to, że dowolne wyrażenie, w którym wystąpi ta wartość, również przybierze wartość Null oraz że nie możemy sprawdzać, jaka jest wartości Null (np. sprawdzenie, czy nazwa miasta jest równa Null, jest bezsensowne), a jedynie — czy jakaś wartość jest nieokreślona.

Na przykład podatek VAT od towaru o nieznanej nazwie jest — jak należało się spodziewać — nieznany, a więc będzie reprezentowany przez wartość Null.

Przykład operacji arytmetycznej na wartości Null:

Porządkowanie danych

Instrukcja SELECT zwraca wiersze w tej kolejności, w jakiej dane są przechowywane w tabeli. Z reguły jest to kolejność, w jakiej były dopisywane następne wiersze z danymi. Do zmiany kolejności, w jakiej zwracane będą wyniki zapytania, służy klauzula ORDER BY. ORDER BY („uporządkuj według”) jest opcjonalnym składnikiem instrukcji SELECT. Jeżeli jednak ta klauzula wystąpi, musi być ostatnią.

Obowiązkowym parametrem klauzuli ORDER BY jest wyrażenie lub nazwa kolumny — według ich wartości należy posortować dane wynikowe. Wykonanie poniższej instrukcji spowoduje wyświetlenie opisów towarów i cen ich zakupu uszeregowanych według cen zakupu.

Posortowana lista towarów:

Lista towarów posortowana od najdroższego do najtańszego:

Lista nazw towarów posortowana według cen ich zakupu:

Jeżeli kilku klientów mieszka w tym samym mieście, o ich kolejności na liście zadecyduje nazwa i numer ulicy:

Typowe operatory porównania

Operatory logiczne porównują, czy dany warunek jest spełniony, czyli czy w wyniku porównania argumentów otrzymamy wartość logiczną True (Prawda). Na przykład wszystkie poniższe warunki są prawdziwe: 1<4, 2=2, 5>=5, 'mama'='mama' itd.

Aby na przykład wybrać tylko te towary, których cena zakupu nie przekracza 5 zł, należy wykonać instrukcję:

Przykład wykorzystania przedstawionych do tej pory wiadomości o instrukcji SELECT:

Porównanie danych odczytanych z tabeli. Serwer baz danych sprawdzi ten warunek dla każdego wiersza tabeli:

Przykład użycia złożonego warunku logicznego — w wyniku zapytania znajdą się tylko te zamówienia, które zostały złożone przed końcem czerwca 2000 roku przez klienta o identyfikatorze 8:

Specjalne znaczenie wśród operatorów logicznych ma operator negacji. W przeciwieństwie do pozostałych jest on operatorem jednoargumentowym, to znaczy, że w celu obliczenia wyniku wystarczy podać jeden argument. Jak wynika z tabeli prawdziwości, wynik operacji NOT a jest prawdą wtedy i tylko wtedy, gdy argument a jest fałszywy. Operator negacji służy do zaprzeczania warunkom podanym w klauzuli WHERE.

Zapytanie zwracające informacje o mężczyznach spoza Lowtown:

Oczywiście, w klauzuli WHERE, tak jak w klauzulach SELECT czy ORDER BY, możemy używać wyrażeń. W efekcie możemy łatwo wybrać na przykład informacje o tych towarach, które sprzedajemy z ponad 50-procentową marżą.

Przykład wykorzystania wyrażenia w klauzuli WHERE:

Lista towarów, których cena sprzedaży jest większa od 10, ale mniejsza niż 15:

Operator IN służy do sprawdzenia, czy dana wartość należy do podanego zbioru.

Dane osób mieszkających w Nicetown lub Welltown:

LIKE

Za pomocą operatora LIKE możemy wyszukać dane tekstowe zgodne z podanym wzorcem. Przy tworzeniu wzorca można wykorzystać symbole o specjalnym znaczeniu:

1. %— zastępuje dowolny ciąg znaków.

2. _ — zastępuje dokładnie jeden dowolny znak.

Operator LIKE powinien być używany tylko z danymi tekstowymi.Aby na przykład odszukać wszystkie panie lub panny, których nazwisko zaczyna się od liter ST, napiszemy:

IS

Operator IS służy przede wszystkim do wyszukiwania tych rekordów, w których przechowywana jest wartość nieokreślona (wartość Null), oraz do sprawdzania, czy dana wartość nie jest nieokreślona.

Zapytanie zwracające informacje o towarach, które mają nazwę, ale nie mają określonej ceny sprzedaży:

Ograniczanie liczby wierszy

Język SQL pozwala również ograniczyć liczbę wierszy wyniku zapytania — wystarczy w klauzuli LIMIT wpisać tę liczbę. W efekcie bardzo łatwo możemy np. odczytać nazwy i ceny pięciu najdroższych produktów.

Wynik został ograniczony do pierwszych pięciu wierszy:

Klauzula LIMIT użyta do wyszukiwania najmniejszych lub największych danych:

Zapytanie zwracające nazwy siedmiu z dziesięciu najdroższych produktów. W wyniku nie znalazły się dane trzech najdroższych produktów:

Funkcja COUNT()

Pierwszą funkcją agregującą, jest funkcja COUNT(). Funkcja ta zlicza w przekazanym zbiorze wartości wystąpienia różne od NULL, chyba że jako argumentu użyto znaku * (gwiazdka) — takie wywołanie funkcji spowoduje zliczenie wszystkich wierszy, łącznie z duplikatami i wartościami NULL. Argumentem funkcji mogą być liczby, daty, znaki i ciągi znaków.

Zapytanie zwracające liczbę klientów:

Funkcja COUNT() wywołana dla dwóch różnych zbiorów — raz dla nazwisk, raz dla imion klientów. Jak widać, jedna osoba nie podała nam imienia.

Zapytanie zwracające liczbę miast, w których mieszkają nasi klienci — w pierwszej kolumnie to samo miasto liczone jest tyle razy, ilu mieszka w nim klientów, w drugiej kolumnie każde miasto policzone jest tylko raz:

Funkcja SUM()

Za pomocą funkcji SUM() dodawane są wszystkie wartości rekordów wybranych w zapytaniu i zwracany jest pojedynczy wynik. W przeciwieństwie do funkcji COUNT(), która działa dla wszystkich typów danych, argumentami funkcji SUM() mogą być wyłącznie liczby. Funkcja SUM(), tak jak inne funkcje grupujące, nie uwzględnia wartości Null.

Zapytanie zwracające liczbę wszystkich towarów w magazynie:

Oczywiście, zbiór argumentów wywołania funkcji grupujących możemy ograniczać za pomocą wcześniej omówionej klauzuli WHERE.

Liczba drewnianych puzzli — ponieważ nazwy towarów przechowywane są w innej tabeli niż stany magazynowe, konieczne było użycie klauzuli JOIN:

Funkcja AVG()

W wyniku działania funkcji AVG() zwracana jest wartość średnia dla podanych wyrażeń. Wiersze przechowujące wartość Null nie są uwzględniane. Argumentami funkcji AVG() muszą być dane liczbowe. Aby na przykład obliczyć średnie ceny sprzedaży i zakupu towarów, napiszemy:

Zapytanie zwracające różnicę pomiędzy średnią ceną zakupu a średnią ceną sprzedaży towarów:

Funkcje MIN() i MAX()

Funkcja MIN() służy do znajdowania wartości najmniejszej w zbiorze wartości, a funkcja MAX() — największej. Obie funkcje, podobnie jak funkcja COUNT(), mogą być użyte dla różnych typów danych.

Za pomocą funkcji MAX() można znaleźć największy łańcuch danych, najnowszą datę (lub najodleglejszą w przyszłości) oraz największą liczbę w zestawieniu. W wyniku działania funkcji MIN() można znaleźć odpowiednio wartości najmniejsze.

Data pierwszego zamówienia:

Przykład wywołania funkcji grupującej na danych będących wynikiem wcześniejszych obliczeń, lecz nieodczytanych bezpośrednio z tabeli:

Funkcja STDDEV_POP()

Funkcja STDDEV_POP() wyznacza odchylenie standardowe zbioru. Wiersze przechowujące wartość Null nie są uwzględniane. Argumentami funkcji muszą być dane liczbowe. Aby wyznaczyć odchylenie standardowe dla cen zakupu wszystkich towarów, napiszemy:

Funkcja VARIANCE()

Funkcja VARIANCE() wyznacza wariancje zbioru. Wiersze przechowujące wartość Null nie są uwzględniane. Argumentami funkcji muszą być dane liczbowe. Aby obliczyć wariancje ceny sprzedaży, należy wykonać instrukcję:

Funkcja GROUP_CONCAT()

Funkcja GROUP_CONCAT() w przeciwieństwie do pozostałych funkcji grupujących zwraca dane tekstowe, a nie liczbowe. Jej działanie jest bardzo proste — łączy w jeden ciąg znaków przekazane argumenty wywołania. Poniższy przykład pokazuje, jak przygotować listę imion klientów:

Grupowanie danych

Do tej pory wywoływaliśmy funkcje grupujące raz dla całych tabel lub ich fragmentów. Klauzula GROUP BY umożliwia grupowanie wyników względem zawartości wybranych kolumn. W wyniku jej działania uzyskujemy podział wierszy tablicy na dowolne grupy. W pewnym sensie jej działanie jest podobne do działania operatora DISTINCT, ponieważ po jej zastosowaniu zwracany jest pojedynczy wynik dla każdej grupy.

Klauzula GROUP BY użyta do wyeliminowania duplikatów — skoro dane są grupowane według identyfikatorów osób, czyli zamówienia złożone przez tę samą osobę dodawane są do jednej grupy, to w wyniku nie może pojawić się kilka razy ten sam identyfikator klienta.

Ogólna liczba sprzedanych towarów:

Liczba towarów sprzedanych w ramach poszczególnych zamówień:

Ta instrukcja SELECT jest jak najbardziej poprawna — przecież dane zostały pogrupowane według wartości orderinfo_id, a następnie dla każdej grupy była wywołana funkcja grupująca:

Uporządkowany wynik poprzedniego zapytania:

W zapytaniach grupujących dane możemy używać klauzuli WHERE tak samo jak klauzuli ORDER BY . W ten sposób ograniczymy liczbę wierszy, jeszcze zanim będą one dzielone na grupy i podgrupy. Przy stosowaniu klauzuli WHERE łącznie z GROUP BY najpierw realizowane jest ograniczenie wynikające z kryteriów w klauzuli WHERE. Następnie wybrane rekordy są grupowane i powstaje ostateczny wynik zapytania ograniczony do zamówień z pierwszej połowy 2000 roku:

Otrzymane w ten sposób grupy wierszy możemy dalej dzielić na podgrupy. Wymieniając w klauzuli GROUP BY wiele wyrażeń, podzielimy zbiór wierszy na grupy wyznaczone pierwszym wyrażeniem, grupy te podzielimy na podgrupy na podstawie wartości drugiego wyrażenia itd.

Liczba towarów kupionych przez poszczególnych klientów w ramach poszczególnych zamówień:

Operator ROLLUP

Jeżeli dane są grupowane według wartości kilku kolumn, to, jak pokazały poprzednie przykłady, kolejność ich występowania w klauzuli GROUP BY wyznacza podział na grupy i podgrupy. Jeżeli tych grup i podgrup jest niewiele, użytkownicy potrafią samodzielnie wyliczyć brakujące podsumowania, w tym przypadku sumy cen wszystkich towarów dla poszczególnych grup. Możemy jednak dodać do wyniku zapytania takie sumy pośrednie — służy do tego operator ROLLUP. Jego działanie prześledzimy najpierw na bardzo prostym przykładzie.

Pierwsze zapytanie zwraca liczbę sprzedanych egzemplarzy każdego towaru, drugie zawiera dodatkowy wiersz z podsumowaniem sprzedaży wszystkich towarów:

Operator ROLLUP jest szczególnie przydatny w zapytaniach grupujących dane według wartości kilku kolumn — w takim przypadku zwróci on dodatkowy wiersz z podsumowaniem dla każdej grupy wartości.

Zapytanie zwracające informacje o liczbie różnych towarów kupionych przez poszczególnych klientów. Dodatkowe wiersze z całkowitą sumą towarów kupionych przez klientów oraz całkowitym podsumowaniem sprzedaży zostały dodane przez operator ROLLUP:

Klauzula HAVING

Język SQL dostarcza jeszcze jedną metodę filtrowania wyników zapytań — jeżeli grupujemy wyniki (a więc używamy klauzuli GROUP BY), możemy sprawdzić, czy te grupy wierszy spełniają jakiś warunek. Wiesz już, że po zastosowaniu klauzuli WHERE wyniki zapytania najpierw są filtrowane, a potem grupowane. Klauzula HAVING, tak jak WHERE, umożliwia określenie testu logicznego, ale w jej przypadku będzie on zastosowany do grup, a nie pojedynczych wierszy. Testy logiczne zawarte w klauzuli HAVING wykonywane są na całych grupach, a nie na pojedynczych rekordach. Tak więc klauzula ta służy do wybierania interesujących nas grup, a klauzula WHERE — interesujących nas wierszy. Warunek umieszczony w klauzuli HAVING wyrażony jest za pomocą dowolnej funkcji grupowej.

Informacje o zamówieniach, których wartość przekroczyła 30. Iloczyn quantity*sell_price jest wyliczany dla każdego sprzedanego towaru, a następnie wyliczana jest suma wartości dla każdej grupy, czyli w tym przypadku dla każdego zamówienia. Na końcu klauzula HAVING usuwa z wyniku te grupy, których wartość nie przekroczyła 30:

Klauzule HAVING i WHERE mogą wystąpić w tym samym zapytaniu — w takim przypadku najpierw będzie zastosowany test z klauzuli WHERE, a następnie — z klauzuli HAVING.

Wyniki poprzedniego zamówienia ograniczone do zamówień złożonych w pierwszej połowie 2000 roku:

Zapisanie w klauzuli HAVING warunku, który jest sprawdzany na poziomie wierszy, nie jest błędem składniowym, ale taka instrukcja jest nie tylko nieelegancka i nieczytelna, ale również może być dłużej wykonywana. Klauzula HAVING użyta do wybierania wierszy: