Podstawowe polecenia JMD SQL: SELECT
advertisement
BAZY DANYCH
Język manipulacji danymi – SQL cz. I
•
Język SQL – podstawowe cechy
•
Zapytania SELECT
Bazy danych
‹#›
JĘZYK SQL – WPROWADZENIE
Idealny język baz danych powinien umożliwiać użytkownikowi:
tworzenie bazy danych i struktur relacji;
wykonywanie podstawowych zadań związanych z zarządzaniem danymi, takich jak: dodawanie,
modyfikacja i usuwanie danych z relacji;
wykonywanie zarówno prostych, jak i skomplikowanych zapytań.
SQL jest przykładem języka transformacji, czyli języka zaprojektowanego w ten sposób,
by umożliwić przekształcenie relacji wejściowych w relację wyjściową.
W międzynarodowym standardzie SQL wyróżnia się dwie główne części:
język definicji danych (JDD lub DDL od ang. Data Definition Language) służący do
definiowania struktury danych i do kontroli dostępu do danych;
język manipulowania danymi (JMD, lub DML od ang. Data Manipulation Language)
służący do wyszukiwania i modyfikowania danych.
Bazy danych
‹#›
JĘZYK SQL – PODSTAWOWE CECHY
Cechy języka SQL:
język nieproceduralny - użytkownik opisuje informację, której potrzebuje, a nie
wskazuje, w jaki sposób należy ją odnaleźć. Innymi słowy, SQL nie wymaga od użytkownika
podania sposobu dostępu do danych;
ma dość swobodny format, co oznacza, że poszczególne fragmenty poleceń nie muszą być
umieszczone w określonych miejscach;
polecenie składa się z typowych słów angielskich, takich jak CREATE TABLE, INSERT
czy SELECT;
SQL może być wykorzystywany przez różnych użytkowników, w tym administratorów
bazy danych (DBA), pracowników obsługujących bazę, programistów aplikacji oraz wielu
innych.
Przykład zapytania w języku SQL:
CREATE TABLE Personel (pracownikNr VARCHAR(5), Imię VARCHAR(15),
pensja DECIMAL(7,2));
INSERT INTO Personel YALUES ('SG16', 'Brown', 8300);
SELECT pracownikNr, Imię, pensja FROM Presonel WHERE pensja > 10000;
Bazy danych
‹#›
FORMUŁOWANIE POLECEŃ W JĘZYKU SQL
Zdanie (polecenie, zapytanie) języka SQL składa się ze słów zarezerwowanych (kluczowych) oraz ze
słów zdefiniowanych przez użytkownika.
Słowa zarezerwowane są niezmienną częścią języka i mają ustalone znaczenie. Należy je zapisywać
dokładnie tak, jak wymaga tego składnia języka, i nie wolno ich dzielić, przenosząc do następnego
wiersza.
Słowa zdefiniowane przez użytkownika są tworzone przez użytkownika (zgodnie z pewnymi regułami
syntaktycznymi) i stanowią nazwy różnych obiektów bazy danych, takich jak tabele, kolumny,
perspektywy, indeksy itd.
Słowa układają się w zdania (polecenia, zapytania), także zgodnie z pewnymi regułami
syntaktycznymi.
Chociaż standard tego nie określa, w wielu dialektach SQL wymagane jest zakończenie każdego zdania
specjalnym znakiem (zazwyczaj jest to średnik ';').
Większość elementów polecenia SQL można pisać dowolnie, wielkimi lub małymi literami. Jedynym
istotnym wyjątkiem są dane podawane „dosłownie”, czyli literały, które muszą być zapisane dokładnie
w takiej postaci, w jakiej występują w bazie danych.
Jeżeli, na przykład, mamy zapisane w bazie danych nazwisko osoby „KOWALSKI”, a poszukujemy
danych, używając słowa ‘Kowalski', to nie odnajdziemy poszukiwanego wiersza.
Chociaż SQL ma swobodny format, to jego polecenia są bardziej czytelne, gdy stosuje się odpowiednie
wcięcia i podział na wiersze.
Bazy danych
‹#›
LITERAŁY
Literały to stałe wykorzystywane w poleceniach SQL.
Istnieją różne postaci literałów, odpowiednie dla każdego typu danych występującego w SQL.
Ogólnie literały można podzielić na:
zapisywane w pojedynczym cudzysłowie;
zapisywane bez cudzysłowu.
Wszystkie nieliczbowe wartości danych muszą być ujęte w pojedynczy cudzysłów.
Liczbowe wartości danych nie należy ujmować w cudzysłów.
Przykład zastosowania literałów w celu dopisania danych do tabeli:
INSERT INTO Nieruchomość (nieruchomośćNr, ulica, miasto, kodPocztowy, typ, pokoje,
czynsz, właścicielNr, pracownikNr, biuroNr)
VALUES ('P113', ‘Piastów 24', Szczecin', ’71-111', Dom', 6, 1500.00, 'CO46', 'SA9', 'B007');
Bazy danych
‹#›
ZAPIS SKŁADNI SQL - NOTACJA BACKUSA-NAURA (BNF)
Notacja Backusa-Naura:
wielkie litery są wykorzystywane do zapisywania słów zarezerwowanych; w poleceniach należy
je zapisywać dokładnie tak, jak w definicji;
małe litery służą do zapisywania słów definiowanych przez użytkownika;
pionowa kreska jest używana, by zaznaczyć możliwość wyboru jednej z przedstawionych opcji,
na przykład: a | b | c;
w nawiasach klamrowych umieszczane są elementy wymagane, które koniecznie muszą
wystąpić, na przykład: {a};
w nawiasach kwadratowych umieszczane są elementy opcjonalne, które mogą (ale nie muszą)
wystąpić, na przykład: [a];
nawiasów okrągłych (...) używa się, by zaznaczyć możliwość powtórzenia elementu zero lub
dowolną liczbę razy.
Na przykład:
{a | b} (,c...)
oznacza literę a albo b, po której następuje dowolna liczba liter c (może ich być także zero),
oddzielonych przecinkami.
Bazy danych
‹#›
OPEROWANIE NA DANYCH
Podstawowe polecenia JMD SQL:
SELECT - wyszukiwanie danych w bazie;
INSERT - dopisywanie danych do tabel;
UPDATE - modyfikowanie danych w tabelach;
DELETE - usuwanie danych z tabel.
BAZA DLA REALIZACJI ZAPYTAŃ SQL:
Biuro
(biuroNr, ulica, miasto, kodPocztowy)
Personel
(pracownikNr, imię, nazwisko, stanowisko, płeć, dataUr, pensja, biuroNr)
Nieruchomość
(nieruchomośćNr, ulica, miasto, kodPocztowy, typ, pokoje, czynsz,
właścicielNr, pracownikNr, biuroNr)
Klient
(klientNr, imię, nazwisko, telNr, typPreferencji, maksCzynsz)
Właściciel Prywatny (właścicielNr, imię, nazwisko, adres, telNr)
Wizyta
(klientNr, nieruchomośćNr, dataWizyty, uwagi)
Bazy danych
‹#›
ZAPYTANIE SELECT
Zapytanie SELECT służy do wyszukiwania i przedstawiania użytkownikowi danych z jednej lub
wielu tabel bazy danych.
SELECT jest najczęściej używaną instrukcją SQL i ma następującą postać ogólną:
Kolejność przetwarzania klauzul polecenia SELECT jest następująca:
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
określa tabele (lub tabele), z których będziemy korzystać,
pozwala wybrać wiersze spełniające zadany warunek selekcji wierszy,
tworzy grupy wierszy o tej samej wartości wskazanej kolumny,
pozwala wybrać grupy ze względu na podany warunek selekcji grup,
wskazuje, które kolumny powinny pojawić się w wyniku,
określa uporządkowanie wyniku.
Porządku elementów zapytania SELECT nie można zmieniać. Jedynymi klauzulami, które musi
zawierać każde polecenie, są SELECT i FROM.
Operacja SELECT jest domknięta; wynik zapytania zastosowanego do tabeli jest także tabelą.
Bazy danych
‹#›
WYSZUKIWANIE WSZYSTKICH KOLUMN I WIERSZY
PODAJ WSZYSTKIE DANE WSZYSTKICH PRACOWNIKÓW:
Bazy danych
‹#›
WYSZUKIWANIE WYBRANYCH KOLUMN I WSZYSTKICH WIERSZY
PODAJ LISTĘ PŁAC WSZYSTKICH PRACOWNIKÓW; LISTA POWINNA ZAWIERAĆ
JEDYNIE NUMER PRACOWNIKA, JEGO IMIĘ I NAZWISKO ORAZ PENSJĘ:
Bazy danych
‹#›
WYKORZYSTANIE DISTINCT
PODAJ NUMERY WSZYSTKICH NIERUCHOMOŚCI, KTÓRE ZOSTAŁY ODWIEDZONE
PRZEZ KLIENTÓW:
Polecenie SELECT nie eliminuje powtórzeń po wykonaniu rzutowania na wybrane kolumny.
By to uczynić, trzeba użyć słowa DISTINCT.
Bazy danych
‹#›
POLA WYLICZANE
PODAJ LISTĘ MIESIĘCZNYCH PŁAC WSZYSTKICH PRACOWNIKÓW; LISTA POWINNA
ZAWIERAĆ NUMER PRACOWNIKA, JEGO IMIĘ I NAZWISKO ORAZ PŁACĘ:
By utworzyć pole wyliczane, należy na liście SELECT podać wyrażenie SQL, które może zawierać dodawanie,
odejmowanie, mnożenie i dzielenie oraz nawiasy pozwalające budować bardziej skomplikowane wyrażenia.
W kolumnie wyliczanej może wystąpić więcej niż jedna kolumna; jednak trzeba uważać, by kolumny występujące w
wyrażeniach arytmetycznych miały typ liczbowy.
Standard ISO pozwala nadawać kolumnom nazwy za pomocą klauzuli AS.
Bazy danych
‹#›
WYBIERANIE WIERSZY – KLAUZULA WHERE
Do wyszukania wierszy spełniających pewne ograniczenia służy klauzula WHERE, która składa się ze
słowa kluczowego WHERE, po którym następuje warunek selekcji opisujący wiersze, które
zamierzamy wybrać.
Pięć podstawowych warunków (nazywanych także predykatami zgodnie z terminologią ISO) to:
porównanie - polega na porównaniu wartości jednego wyrażenia z wartością drugiego
wyrażenia;
sprawdzenie zakresu - polega na sprawdzeniu, czy zadana wartość należy do wskazanego
przedziału wartości;
przynależność do zbioru - polega na sprawdzeniu, czy wartość jest równa jednemu spośród
elementów zbioru;
dopasowanie do wzorca - polega na sprawdzeniu, czy słowo pasuje do podanego wzorca;
wartość pusta - polega na sprawdzeniu, czy w kolumnie jest wartość pusta (null).
Bazy danych
‹#›
OPERATORY
Bardziej skomplikowane predykaty można zbudować, używając spójników logicznych AND (i), OR
(lub) oraz NOT (nie), a także nawiasów (gdy są potrzebne lub konieczne) w celu wskazania kolejności
działań.
Zasady obliczania wartości wyrażenia logicznego są następujące:
wyrażenie oblicza się od lewej do prawej;
w pierwszej kolejności obliczane są podwyrażenia umieszczone w nawiasach;
NOT jest obliczane przed AND i OR;
AND jest obliczane przed OR.
Bazy danych
‹#›
WARUNEK SELEKCJI - PORÓWNANIE
PODAJ WSZYSTKICH PRACOWNIKÓW, KTÓRYCH PENSJA JEST WYŻSZA
NIŻ 10 000 ZŁOTYCH:
Bazy danych
‹#›
ZŁOŻONY WARUNEK SELEKCJI - PORÓWNANIE
PODAJ ADRESY WSZYSTKICH BIUR ZNAJDUJĄCYCH SIĘ W LONDYNIE
LUB W GLASGOW:
Bazy danych
‹#›
WARUNEK SELEKCJI – WARTOŚCI Z ZAKRESU (BETWEEN, NOT BETWEEN)
PODAJ WSZYSTKICH PRACOWNIKÓW MAJĄCYCH PENSJĘ
POMIĘDZY 20 000 A 30 000 ZŁ
Bazy danych
‹#›
WARUNEK SELEKCJI – PRZYNALEŻNOŚĆ DO ZBIORU (IN, NOT IN )
PODAJ WSZYSTKICH KIEROWNIKÓW I DYREKTORÓW:
Warunek przynależności do zbioru (IN) polega na sprawdzeniu, czy wartość danych jest jedną
z wartości podanych na liście.
Istnieje zanegowana wersja warunku (NOT IN), którą można stosować w celu sprawdzenia,
czy wartości nie występują na wskazanej liście.
Bazy danych
‹#›
WARUNEK SELEKCJI – DOPASOWANIE DO WZORCA (LIKE, NOT LIKE)
ZNAJDŹ WSZYSTKICH WŁAŚCICIELI, W KTÓRYCH ADRESIE WYSTĘPUJE
SŁOWO ’GLASGOW’:
W SQL występują dwa szczególne symbole zastępcze, które można stosować we wzorcu:
%
- znak procent zastępuje ciąg znaków dowolnej długości (także zero);
_
- znak podkreślenia zastępuje dowolny (jeden) znak.
Bazy danych
‹#›
WARUNEK SELEKCJI – WARTOŚCI PUSTE (IS NULL, IS NOT NULL)
PODAJ SZCZEGÓŁOWE INFORMACJE O WSZYSTKICH WIZYTACH W
NIERUCHOMOŚCI PG4, PO KTÓRYCH NIE ZGŁOSZONO UWAG:
Sprawdzenie, czy w polu występuje wartość pusta, musimy wykonać za pomocą specjalnych słów
kluczowych IS NULL.
Bazy danych
‹#›
PORZĄDKOWANIE WYNIKU – ORDER BY
Zazwyczaj wiersze wyniku zapytania SQL nie są uporządkowane (chociaż niektóre SZBD mogą
domyślnie porządkować wynik w oparciu, na przykład, o klucz główny).
Do uporządkowania wyniku zapytania służy klauzula ORDER BY, zawierająca listę oddzielonych
przecinkami identyfikatorów kolumn, według których należy posortować wynik.
Identyfikatorem kolumny może być jej nazwa lub numer, który oznacza odpowiedni element z listy
SELECT (pierwszy element z listy ma numer l, drugi numer 2 itd).
Klauzula ORDER BY pozwala przedstawiać wyszukane rekordy w porządku rosnącym (ASC) lub
malejącym (DESC), określonym przez dowolną kolumnę lub układ kolumn, niezależnie od tego, czy
taka kolumna występuje w wyniku.
W niektórych dialektach SQL elementy z listy ORDER BY muszą występować na liście SELECT.
Klauzulę ORDER BY piszemy zawsze jako ostatnią w zapytaniu SELECT.
Bazy danych
‹#›
PORZĄDKOWANIE WEDŁUG JEDNEJ KOLUMNY
WYGENERUJ LISTĘ PENSJI WSZYSTKICH PRACOWNIKÓW UPORZĄDKOWANĄ
MALEJĄCO WEDŁUG PENSJI:
Bazy danych
‹#›
PORZĄDKOWANIE WEDŁUG JEDNEJ KOLUMNY
WYGENERUJ LISTĘ WYBRANYCH INFORMACJI DOTYCZĄCYCH NIERUCHOMOŚCI
UPORZĄDKOWANĄ WEDŁUG RODZAJÓW NIERUCHOMOŚCI I CZYNSZU:
Wynik jest uporządkowany najpierw według typu nieruchomości w rosnącym porządku
alfabetycznym (ASC jest wartością domyślną), a w ramach tego samego typu nieruchomości - według
malejących wartości czynszu.
Bazy danych
‹#›
FUNKCJE AGREGUJĄCE (1)
W standardzie ISO jest zdefiniowanych pięć funkcji agregujących:
COUNT
- zwraca liczbę wartości występujących w określonej kolumnie;
SUM
- zwraca sumę wartości występujących w określonej kolumnie;
AVG
- zwraca średnią wartości występujących w określonej kolumnie;
MIN
- zwraca najmniejszą wartość występującą w określonej kolumnie;
MAX
- zwraca największą wartość występującą w określonej kolumnie.
Wymienione funkcje są obliczane na podstawie wartości jednej kolumny tabeli i zwracają w wyniku
jedną wartość.
Funkcje COUNT, MIN i MAX można stosować zarówno do pól liczbowych, jak i nieliczbowych.
Funkcje SUM i AVG można stosować jedynie do pól liczbowych.
Wszystkie funkcje, oprócz COUNT(*), pomijają wartości puste i zwracają wynik obliczony jedynie
dla pozostałych wartości niepustych. Natomiast COUNT(*) jest specjalnym zastosowaniem COUNT,
które polega na zliczeniu wszystkich wierszy tabeli, niezależnie od tego, czy są to wartości puste lub
powtórzenia.
Bazy danych
‹#›
FUNKCJE AGREGUJĄCE (2)
W celu wyeliminowania powtórzenia przed przystąpieniem do obliczania funkcji, używamy słowa
kluczowego przed nazwą kolumny w argumencie funkcji.
W standardzie ISO zezwala się na użycie słowa kluczowego ALL, gdy nie chcemy eliminowania
powtórzeń (opcja ALL jest domyślna).
Opcja DISTINCT nie wpływa na wynik funkcji MIN i MAX. Może jednak wpływać na wynik SUM
lub AVG, więc przy ich obliczaniu należy rozważyć, czy powtórzenia mają być uwzględniane czy nie.
Ponadto opcja DISTINCT może być użyta tylko raz w zapytaniu.
Funkcje agregujące mogą być stosowane jedynie na liście SELECT i w klauzuli HAVING.
Użycie ich w innym miejscu jest błędem. Jeżeli lista SELECT zawiera funkcję agregującą i w
zapytaniu nie jest zastosowana klauzula GROUP BY służąca do grupowania danych, to wówczas
żaden z elementów listy SELECT nie może odwoływać się do kolumny, o ile kolumna ta nie jest
argumentem funkcji agregującej.
Na przykład, poniższe zapytanie jest błędne:
ponieważ zapytanie nie zawiera klauzuli GROUP BY i kolumna pracownikNr na liście SELECT
nie jest argumentem funkcji agregującej.
Bazy danych
‹#›
ZASTOSOWANIA COUNT(*)
W ILU NIERUCHOMOŚCIACH MIESIĘCZNY CZYNSZ JEST WYŻSZY NIŻ 350 ZŁ?
Bazy danych
‹#›
ZASTOSOWANIA COUNT(DISTINCT)
ILE NIERUCHOMOŚCI ODWIEDZONO W MAJU 2001 ROKU?
Ograniczenie zapytania do wizyt, które nastąpiły w maju 2001 roku, uzyskujemy, stosując klauzulę
WHERE. Całkowitą liczbę wizyt spełniających ten warunek obliczamy za pomocą funkcji agregującej
COUNT. Jednak, ponieważ ta sama nieruchomość mogła być odwiedzana wielokrotnie, musimy użyć
słowa kluczowego DISTINCT, by wyeliminować nieruchomości występujące wielokrotnie.
Bazy danych
‹#›
FUNKCJE COUNT, SUM
OBLICZ, ILU JEST DYREKTORÓW I JAKA JEST ICH SUMARYCZNA PENSJA:
Bazy danych
‹#›
FUNKCJE MIN, MAX, AVG
OBLICZ NAJMNIEJSZĄ, NAJWIĘKSZĄ I ŚREDNIĄ PENSJĘ PRACOWNIKA:
Bazy danych
‹#›
GRUPOWANIE WYNIKU – GROUP BY
Podsumowania częściowe mogą być realizowane w oparciu o klauzulę GROUP BY zapytania
SELECT. Zapytanie takie nazywamy zapytaniem grupującym, ponieważ w trakcie jego obliczania
dane z tabeli SELECT są dzielone na grupy i dla każdej z grup jest generowany jeden wiersz
podsumowania.
Kolumny wymienione w klauzuli GROUP BY nazywamy kolumnami grupowania.
W standardzie ISO zawarte są wymagania ściśle wiążące klauzule SELECT i GROUP BY.
Gdy w zapytaniu występuje GROUP BY, dla każdego elementu z listy SELECT musi istnieć
możliwość wyznaczenia jednoznacznie wartości w ramach grupy.
Ponadto klauzula SELECT może zawierać jedynie:
nazwy kolumn grupowania;
funkcje agregujące;
stałe;
wyrażenia zawierające kombinacje powyższych elementów.
Wszystkie nazwy kolumn na liście SELECT muszą występować w klauzuli GROUP BY, chyba że
nazwa kolumny jest używana tylko jako argument funkcji agregującej. Odwrotna własność nie jest
wymagana: mogą istnieć kolumny wymienione na liście GROUP BY i nie pojawiające się na liście
SELECT.
Gdy wraz z GROUP BY zastosujemy klauzulę WHERE, to jest ona wykonywana w pierwszej
kolejności. Dopiero potem, z pozostałych wierszy spełniających warunek selekcji, tworzone są grupy.
Bazy danych
‹#›
ZASTOSOWANIE GROUP BY
OBLICZ DLA KAŻDEGO BIURA LICZBĘ ZATRUDNIONYCH W NIM PRACOWNIKÓW
ORAZ ICH SUMARYCZNĄ PENSJĘ:
Zasady (teoretyczne) wykonywania polecenia SELECT z grupowaniem:
(1)
Pracownicy są dzieleni na grupy według numerów biur.
W ramach każdej grupy wszyscy pracownicy mają ten sam numer biura (trzy grupy)
(2)
Dla każdej grupy jest wyliczana liczba pracowników oraz suma wartości z kolumny
pensja w celu uzyskania sumarycznej pensji pracowników, a następnie jest generowany pojedynczy wiersz
podsumowania.
(3)
Wynik jest porządkowany według rosnących wartości numerów biur (biuroNr).
Bazy danych
‹#›
WYBÓR GRUPY - HAVING
Klauzulę HAVING stosuje się w połączeniu z klauzulą GROUP BY w celu wybrania grup, które
wystąpią ostatecznie w tabeli wynikowej.
Chociaż jej składnia przypomina WHERE, klauzule te służą do różnych celów.
Klauzula WHERE pozwala przetestować oddzielnie każdy wiersz i wybrać tylko te, które przechodzą
do tabeli wynikowej.
Klauzula HAVING służy natomiast do wyboru grup, które ostatecznie trafią do tabeli wynikowej.
W standardzie ISO jest zamieszczone wymaganie, by nazwy kolumn występujące w klauzuli HAVING
pojawiały się także na liście GROUP BY lub były argumentami funkcji agregujących.
W praktyce, warunek wyszukiwania w klauzuli HAVING zawsze zawiera przynajmniej jedną funkcję
agregującą, gdyż w przeciwnym razie warunek selekcji mógłby zostać przeniesiony do klauzuli
WHERE i zastosowany do każdego wiersza oddzielnie.
Klauzula HAVING nie jest niezbędnym elementem SQL - każde zapytanie wykorzystujące klauzulę
HAVING można zapisać bez tej klauzuli.
Bazy danych
‹#›
ZASTOSOWANIE HAVING
DLA KAŻDEGO BIURA ZATRUDNIAJĄCEGO WIĘCEJ NIŻ JEDNEGO PRACOWNIKA,
PODAJ LICZBĘ PRACOWNIKÓW BIURA ORAZ SUMĘ ICH ZAROBKÓW:
Bazy danych
‹#›
LITERATURA
1.
Thomas Connolly, Carolyn Begg – Systemy baz danych, tom 1, wyd.
RM, 2004 (Rozdział 5 – SQL: język manipulowania danymi)
Bazy danych
‹#›
KONIEC
KONIEC CZ. I
Bazy danych
‹#›
