6. Pogłębienie wiedzy na temat języka SQL
6.1. Introduction
W tym rozdziale przedstawiamy
- dalsze składnie polecenia SELECT, które sprawiają, że jest to bardzo potężne polecenie zapytania, zwłaszcza do przeszukiwania wielu tabel jednocześnie.
- rozszerzone składnie poleceń już omówionych
Aby zilustrować różne polecenia, będziemy pracować z następującymi tabelami wykorzystywanymi do zarządzania zamówieniami w systemie dystrybucji książek PME:
6.1.1. tabela CLIENTS
Przechowuje informacje o klientach z PME:
 |
numer jednoznacznie identyfikujący klienta – klucz główny | |
nazwa klienta | |
I = osoba fizyczna, E = przedsiębiorstwo, A = administracja | |
imię w przypadku osoby fizycznej | |
Nazwisko osoby kontaktowej u klienta (w przypadku przedsiębiorstwa lub administracji) | |
Adres klienta – ulica | |
miasto | |
kod pocztowy | |
Telefon | |
Od kiedy jest Pan/Pani klientem? | |
O (Tak), jeśli klient ma zaległości płatnicze wobec firmy, a N (Nie) w przeciwnym razie. |
6.1.2. tabela ARTICLES
Przechowuje informacje o sprzedanych produktach, w tym przypadku książkach. Jej struktura jest następująca:
numer jednoznacznie identyfikujący książkę (ISBN = Międzynarodowy Standardowy Numer Książki) – klucz główny | |
Tytuł książki | |
Kod jednoznacznie identyfikujący wydawcę | |
Nazwisko autora | |
Streszczenie książki | |
Ilość sprzedana w ciągu roku | |
Ilość sprzedana w poprzednim roku | |
Data ostatniej sprzedaży | |
Ilość ostatniej dostawy | |
Data ostatniej dostawy | |
Cena sprzedaży | |
Koszt zakupu | |
Minimalna ilość zamówienia | |
Minimalny poziom zapasów | |
Ilość na magazynie |
Jego zawartość może wyglądać następująco:
6.1.3. tabela COMMANDES
Przechowuje informacje o zamówieniach złożonych przez klientów. Jej struktura jest następująca:
Numer jednoznacznie identyfikujący zamówienie – klucz główny | |
Numer klienta składającego zamówienie – klucz zagraniczny – numer katalogowy CLIENTS(ID) | |
Data wprowadzenia tego zamówienia | |
O (Tak), jeśli zamówienie zostało anulowane, a N (Nie) w przeciwnym razie. |
6.1.4. Tabela DETAILS
Zawiera szczegóły zamówienia, tj. numery katalogowe i ilości zamówionych książek. Jej struktura jest następująca:
Numer zamówienia – klucz obcy odwołujący się do kolumny NOCMD w tabeli COMMANDES | |
Nr zamówionej książki – klucz obcy odwołujący się do kolumny ISBN w tabeli LIVRES | |
Zamówiona ilość |
Jej zawartość może wyglądać następująco:
Z powyższego wynika, że zamówienie nr 3 (NOCMD) dotyczy trzech książek. Oznacza to, że klient zamówił trzy książki jednocześnie. Dane tego klienta można znaleźć w tabeli [COMMANDES], gdzie widać, że zamówienie nr 3 zostało złożone przez klienta nr 5. Z tabeli [CLIENTS] wynika, że klient nr 5 to firma NetLogos z Segré.
6.2. Zamówienie SELECT
W tym miejscu zamierzamy pogłębić naszą wiedzę na temat zamówienia SELECT, przedstawiając jego nowe składnie.
6.2.1. Składnia zapytania obejmującego wiele tabel
SELECT kolumna1, kolumna2, ... FROM tabela1, tabela2, ..., tabela p WHERE condition ORDER BY ... | |
Nowością jest tutaj fakt, że kolumny kolumna1, kolumna2, ... pochodzą z kilku tabel tabela1, tabela2, ... Jeśli dwie tabele mają kolumny o tej samej nazwie, niejednoznaczność usuwa się za pomocą notacji tablei.colonnej. Kod condition może odnosić się do kolumn z różnych tabel. |
Działanie
Tworzona jest tabela kartezjańska zawierająca tabele table1, table2, ..., tablep. Jeśli ni jest liczbą wierszy w tablei, to utworzona tabela ma zatem n1*n2*...*np wierszy zawierających wszystkie kolumny z różnych tabel. | |
Do tej tabeli stosuje się condition z WHERE. W ten sposób powstaje nowa tabela | |
Jest ona uporządkowana zgodnie z trybem wskazanym w ORDER. | |
Wyświetlane są kolumny żądane na podstawie SELECT. |
Przykłady
Wykorzystujemy tabele przedstawione powyżej. Chcemy poznać szczegóły zamówień złożonych po 25 września:
SQL>select details.nocmd,isbn,qte from commandes,details
where commandes.datecmd>'25-sep-91'
and details.nocmd=commandes.nocmd
Warto zauważyć, że za FROM umieszcza się nazwy wszystkich tabel, do których kolumn się odwołujemy. W powyższym przykładzie wszystkie wybrane kolumny należą do tabeli DETAILS. Jednak warunek odnosi się do tabeli COMMANDES. Stąd konieczność podania nazwy tej ostatniej tabeli po FROM. Operacja sprawdzająca równość kolumn z dwóch różnych tabel jest często nazywana dołączaniem równościowym.
Zapytanie SELECT można by również zapisać w następujący sposób:
SQL> select details.nocmd,isbn,qte from commandes
inner join details on details.nocmd=commandes.nocmd
where commandes.datecmd>'25-sep-91'
Kontynuujmy nasze przykłady. Chcemy uzyskać ten sam wynik co poprzednio, ale z tytułem zamówionej książki zamiast jej numeru ISBN:
SQL>select commandes.nocmd, articles.titre, details.qte
from commandes,articles,details
where commandes.datecmd>'25-sep-91'
and details.nocmd=commandes.nocmd
and details.isbn=articles.isbn
Ten sam wynik uzyskuje się za pomocą następującego, mniej czytelnego zapytania SQL:
SQL> select details.nocmd,articles.titre,details.qte from details
inner join commandes on details.nocmd=commandes.nocmd
inner join articles on details.isbn=articles.isbn
where commandes.datecmd>'25-sep-91'
Powyżej wykonano dwa połączenia wewnętrzne z tabelą [DETAILS]:
- jedno z tabelą [COMMANDES] w celu uzyskania dostępu do daty zamówienia książki
- jedna z tabelą [ARTICLES], aby uzyskać dostęp do tytułu zamówionej książki
Ponadto potrzebujemy nazwiska klienta składającego zamówienie:
SQL>select commandes.nocmd, articles.titre, qte ,clients.nom
from commandes,details,articles,clients
where commandes.datecmd>'25-sep-91'
and details.nocmd=commandes.nocmd
and details.isbn=articles.isbn
and commandes.idcli=clients.id
Potrzebujemy również dat zamówień oraz ich wyświetlenia w porządku malejącym według tych dat:
SQL>select commandes.nocmd, commandes.datecmd, articles.titre, qte ,clients.nom
from commandes,details,articles,clients
where commandes.datecmd>'25-sep-91'
and details.nocmd=commandes.nocmd
and details.isbn=articles.isbn
and commandes.idcli=clients.id
order by commandes.datecmd descending
Oto kilka zasad, których należy przestrzegać przy tworzeniu połączeń:
- Za SELECT należy umieścić kolumny, które mają zostać wyświetlone. Jeśli kolumna występuje w różnych tabelach, przed jej nazwą należy podać nazwę tabeli.
- Za FROM należy umieścić wszystkie tabele, które będą przeszukiwane przez SELECT, czyli tabele zawierające kolumny znajdujące się za SELECT i WHERE.
6.2.2. Automatyczne połączenie
Chcemy poznać książki, których cena sprzedaży jest wyższa niż cena książki „Using SQL”:
SQL>select a.titre from articles a, articles b
where b.titre='Using SQL'
and a.prixvente>b.prixvente
Obie tabele w połączeniu są tutaj identyczne: tabela articles. Aby je rozróżnić, nadaje się im aliasy: from articles a, articles b. Alias pierwszej tabeli to a, a drugiej – b. Tę składnię można stosować nawet wtedy, gdy tabele są różne. W przypadku użycia aliasu należy go stosować wszędzie w poleceniu SELECT zamiast nazwy tabeli, którą oznacza.
6.2.3. Łączenie zewnętrzne
Chcemy poznać klientów, którzy dokonali zakupu we wrześniu, wraz z datą zamówienia. Pozostali klienci są wyświetleni bez tej daty:
SQL>select clients.nom,commandes.datecmd from clients
left outer join commandes on clients.id=commandes.idcli
where datecmd between '01-sep-91' and '30-sep-91'
Zaskakujące jest, że nie otrzymujemy tutaj prawidłowego wyniku. W tabeli [CLIENTS] powinni znaleźć się wszyscy klienci, a tak nie jest. Gdy zastanowimy się nad działaniem połączenia zewnętrznego, zdamy sobie sprawę, że klienci, którzy nie dokonali zakupu, zostali powiązani z pustym wierszem w tabeli COMMANDES, a tym samym z pustą datą (wartość NULL w terminologii SQL). Data ta nie spełnia zatem warunku zdefiniowanego dla daty, a odpowiedni klient nie jest wyświetlany. Spróbujmy czegoś innego:
SQL>select clients.nom,commandes.datecmd from clients
left outer join commandes on clients.id=commandes.idcli
where (commandes.datecmd between '01-sep-91' and '30-sep-91')
or (commandes.datecmd is null)
Tym razem otrzymujemy prawidłową odpowiedź na nasze pytanie.
6.2.4. Zapytania zagnieżdżone
SELECT kolumna[s] FROM tabela[s] WHERE wyrażenie operator zapytanie ORDER BY ... | |
requête jest poleceniem SELECT, które zwraca zbiór 0, 1 lub więcej wartości. Mamy wówczas warunek WHERE typu wyrażenie operator (val1, val2, ..., vali) expression i vali muszą być tego samego typu. Jeśli zapytanie zwraca tylko jedną wartość, sprowadza się to do warunku typu wyrażenie operator wartość , którą dobrze znamy. Jeśli zapytanie zwraca listę wartości, można zastosować następujące operatory:
expression IN (val1, val2, ..., vali): prawdziwe, jeśli expression ma wartość będącą jednym z elementów listy vali.
odwrotność IN
musi być poprzedzone znakiem =, !=, >, >=, <, <= expression >= ANY (val1, val2, .., valn): prawdziwe, jeśli expression jest >= jednej z wartości vali z listy
musi być poprzedzone znakiem =, !=, >, >=, <, <= expression >= ALL (val1, val2, .., valn): prawdziwe, jeśli wyrażenie jest >= wszystkim wartościom vali z listy
warunek: prawdziwe, jeśli funkcja requête zwraca co najmniej jeden wiersz. |
Przykłady
Powróćmy do pytania, które zostało już rozwiązane za pomocą połączenia równościowego: wyświetlić tytuły, których cena sprzedaży jest wyższa niż cena książki „Using SQL”.
SQL>select titre from ARTICLES
where prixvente > (select prixvente from ARTICLES where titre='Using SQL')
To rozwiązanie wydaje się bardziej intuicyjne niż to z połączeniem równościowym. Najpierw przeprowadzamy filtrację z użyciem SELECT, a następnie drugą na uzyskanym wyniku. W ten sposób można przeprowadzać kilka filtracji po kolei.
Chcemy poznać tytuły, których cena sprzedaży jest wyższa od średniej ceny sprzedaży:
Którzy klienci zamówili tytuły wynikające z poprzedniego zapytania?
SQL>select distinct idcli from COMMANDES,DETAILS
where DETAILS.isbn in
(select isbn from ARTICLES where prixvente
> (select avg(prixvente) from ARTICLES))
and COMMANDES.nocmd=DETAILS.nocmd
Objaśnienia
- W tabeli DETAILS wybieramy kody ISBN znajdujące się wśród książek, których cena jest wyższa od średniej ceny książek.
- W wierszach wybranych w poprzednim kroku nie ma kodu klienta IDCLI. Znajduje się on w tabeli COMMANDES. Powiązanie między obiema tabelami odbywa się poprzez numer zamówienia NOCMD, stąd równoważne połączenie COMMANDES.nocmd=DETAILS.nocmd.
- Ten sam klient mógł kilkakrotnie kupić jedną z omawianych książek, w którym to przypadku jego kod IDCLI pojawi się wielokrotnie. Aby tego uniknąć, umieszczamy klucz DISTINCT za SELECT. DISTINCT zasadniczo eliminuje duplikaty w wierszach wynikowych SELECT.
- Aby uzyskać nazwę klienta, należałoby wykonać dodatkowe połączenie równoznaczące między tabelami COMMANDES i CLIENTS, jak pokazuje poniższe zapytanie.
SQL> select distinct CLIENTS.nom from COMMANDES,DETAILS,CLIENTS
where DETAILS.isbn in
(select isbn from ARTICLES where prixvente
> (select avg(prixvente) from ARTICLES))
and COMMANDES.nocmd=DETAILS.nocmd
and COMMANDES.IDCLI=CLIENTS.ID
Znajdź klientów, którzy nie złożyli zamówienia od 24 września:
SQL>select nom from CLIENTS
where clients.id not in
(select distinct commandes.idcli from commandes where datecmd>='24-sep-91')
Zauważyliśmy, że można filtrować wiersze w inny sposób niż za pomocą klauzuli WHERE: wykorzystując klauzulę HAVING w połączeniu z klauzulami GROUP i BY. Klauzula HAVING filtruje grupy wierszy.
Podobnie jak w przypadku klauzuli WHERE, składnia
HAVING expression opérateur requête
jest dopuszczalna, z już przedstawionym ograniczeniem, że expression musi być jednym z wyrażeń expri w klauzuli
GROUP BY expr1, expr2, ...
Przykłady
Jakie są ilości sprzedanych egzemplarzy książek o wartości powyżej 200F?
Najpierw wyświetlmy ilości sprzedanych egzemplarzy według tytułów:
SQL>select ARTICLES.titre,sum(qte) QTE from ARTICLES, DETAILS
where DETAILS.isbn=ARTICLES.isbn
group by titre
Teraz przefiltrujmy pozycje:
SQL> select ARTICLES.titre,sum(qte) QTE from ARTICLES, DETAILS
where DETAILS.isbn=ARTICLES.isbn
group by titre
having titre in (select titre from ARTICLES where prixvente>200)
Być może bardziej oczywistym rozwiązaniem byłoby napisanie:
SQL>select ARTICLES.titre,sum(qte) QTE from ARTICLES, DETAILS
where DETAILS.isbn=ARTICLES.isbn
and ARTICLES.prixvente>200
group by titre
6.2.5. Zapytania powiązane
W przypadku zapytań zagnieżdżonych mamy do czynienia z zapytaniem nadrzędnym (najbardziej zewnętrznym) i zapytaniem podrzędnym (najbardziej wewnętrznym). Zapytanie nadrzędne jest oceniane dopiero po całkowitym przetworzeniu zapytania podrzędnego.
Zapytania powiązane mają tę samą składnię, z następującą drobną różnicą: zapytanie podrzędne wykonuje połączenie z tabelą zapytania nadrzędnego. W tym przypadku zestaw zapytania nadrzędnego i podrzędnego jest oceniany wielokrotnie dla każdego wiersza tabeli nadrzędnej.
Przykład
Powróćmy do przykładu, w którym chcemy uzyskać nazwiska klientów, którzy nie złożyli zamówienia od 24 września:
SQL>
select nom from clients
where not exists
(select idcli from commandes
where datecmd>='24-sep-91'
and commandes.idcli=clients.id)
Zapytanie nadrzędne dotyczy tabeli clients. Zapytanie podrzędne wykonuje połączenie między tabelami clients i commandes. Mamy zatem do czynienia z zapytaniem skorelowanym. Dla każdego wiersza tabeli clients wykonywane jest zapytanie podrzędne: wyszukuje ono kod klienta id wśród zamówień złożonych po 24 września. Jeśli go nie znajdzie (not exists), wyświetlana jest nazwa klienta. Następnie przechodzi się do kolejnego wiersza tabeli clients.
6.2.6. Kryteria wyboru dla zapisu w tabeli SELECT
Wielokrotnie widzieliśmy, że ten sam wynik można uzyskać na różne sposoby przy zapisach w tabeli SELECT. Weźmy przykład: Wyświetlenie klientów, którzy złożyli zamówienie:
Łączenie
Zapytania zagnieżdżone
daje ten sam wynik.
Zapytania powiązane
SQL>
select nom from clients
where exists (select * from commandes where commandes.idcli=clients.id)
daje ten sam wynik.
Autorzy Christian MAREE i Guy LEDANT w swojej książce „SQL, Wprowadzenie, programowanie i zaawansowane zastosowania” proponują kilka kryteriów wyboru:
Wydajność
Użytkownik nie wie, w jaki sposób SGBD „radzi sobie” ze znalezieniem wyników, o które prosi. Dopiero na podstawie doświadczenia odkryje więc, że taki zapis jest wydajniejszy od innego. MAREE i LEDANT twierdzą na podstawie doświadczenia, że zapytania skorelowane wydają się zazwyczaj wolniejsze niż zapytania zagnieżdżone lub połączenia.
Sformułowanie
Sformułowanie za pomocą zapytań zagnieżdżonych jest często bardziej czytelne i intuicyjne niż połączenie. Nie zawsze jednak można z niego skorzystać. Należy zwrócić uwagę zwłaszcza na dwie kwestie:
- Tabele, z których pochodzą kolumny będące argumentami zapytania SELECT (SELECT col1, col2, ...), muszą być wymienione po słowie kluczowym FROM. Następnie wykonywany jest iloczyn kartezjański tych tabel, co nazywamy połączeniem.
- Gdy zapytanie zwraca wyniki z jednej tabeli, a filtrowanie wierszy tej tabeli wymaga odwołania się do innej tabeli, można zastosować zapytania zagnieżdżone.
6.3. Rozszerzenia składni
Ze względów praktycznych najczęściej przedstawialiśmy uproszczone składnie poszczególnych poleceń. W tej sekcji przedstawiamy ich rozszerzone składnie. Są one intuicyjne, ponieważ są analogiczne do składni szeroko omówionego polecenia SELECT.
INSERT
INSERT INTO table (col1, col2, ..) VALUES (val1, val2, ...) | |
INSERT INTO table (kol1, kol2, ...) (requête) | |
Przedstawiono te dwie składnie |
DELETE
DELETE FROM table WHERE condition | |
Ta składnia jest znana. Dodajmy, że warunek może zawierać zapytanie o składni WHERE wyrażenie operator (zapytanie) |
UPDATE
UPDATE table SET kolumna1=wyrażenie1, kolumna2=wyrażenie2, ... WHERE condition | |
Ta składnia została już przedstawiona. Dodajmy, że warunek może zawierać zapytanie o składni WHERE wyrażenie operator (zapytanie) |
UPDATE table SET (kol1, kol2, ...) = zapytanie1, (kolA, kolB, ...) = zapytanie2, ... WHERE condition | |
Wartości przypisane do poszczególnych kolumn mogą pochodzić z zapytania. |