Wybrane metody tworzenia kwerend z wykorzystaniem języka SQL

Wszystkie treści na stronie ir.migra.pl chronione są prawami autorskimi. Więcej informacji znajdziesz tutaj.

Zapisy podstawy programowej 2024 realizowane w temacie:

II. Programowanie i rozwiązywanie problemów z wykorzystaniem komputera i innych urządzeń cyfrowych.
Zakres rozszerzony. Uczeń spełnia wymagania określone dla zakresu podstawowego, a ponadto:

3. przygotowując opracowania rozwiązań złożonych problemów, posługuje się wybranymi aplikacjami w stopniu zaawansowanym:
   c) projektuje i tworzy relacyjną bazę złożoną z wielu tabel, formułuje kwerendy, stosuje język SQL do wyszukiwania informacji w bazie i do jej modyfikacji, uwzględnia kwestie integralności danych, bezpieczeństwa i ochrony danych w bazie;

Spis treści

1. Czym jest język SQL?
2. Zastosowanie instrukcji   SELECT
   1. Klauzula   ORDER BY
   2. Klauzula   INNER JOIN
   3. Klauzula   LEFT JOIN
   4. Klauzula   GROUP BY
3. Dopisywanie rekordów
4. Aktualizacja danych
5. Usuwanie rekordów

1. Czym jest język SQL?

Aby pobrać określone informacje z bazy danych, w programie Microsoft Access tworzyliśmy odpowiednie kwerendy. Wybieraliśmy tabele służące jako źródła danych, a następnie w oknie projektowania kwerendy określaliśmy warunki, które musiały spełniać pobierane rekordy.

Kwerendy korzystają z języka SQL. Wszystkie kwerendy są w istocie ciągami instrukcji języka SQL. Na przykład, aby pobrać dane, używamy polecenia SELECT wraz z odpowiednimi klauzulami, określającymi źródło danych i warunki, jakie muszą spełniać pobierane rekordy.

W założeniach język SQL miał być prosty w użyciu. Większość jego słów kluczowych odpowiada zwyczajnym słowom w języku angielskim, np. SELECT oznacza wybierz, INSERT – wstaw, UPDATE – zaktualizuj.

W języku SQL małe i wielkie litery mają takie samo znaczenie.

Tworząc kwerendy w programie Microsoft Access, nie musieliśmy znać składni języka SQL – określaliśmy jedynie odpowiednie warunki, resztą zajmował się program. Znajomość języka SQL jest jednak bardzo przydatna. Czasami można szybciej napisać odpowiednią kwerendę w tym języku, niż za pomocą narzędzi graficznych. Poza tym stosowanie poleceń języka SQL jest niezbędne, gdy chcemy tworzyć rozbudowane kwerendy czy pisać własne programy korzystające z baz danych.

2. Zastosowanie instrukcji SELECT

Instrukcja SELECT jest podstawową instrukcją języka SQL. Wywołanie tej instrukcji tworzy zbiór rekordów wynikowych według określonych przez nas kryteriów.

Po słowie SELECT podajemy listę pól (oddzielonych przecinkami) mających pojawić się w wynikowym zbiorze rekordów. Po słowie FROM podajemy listę tabel lub kwerend (oddzielonych przecinkami), które będą stanowić źródło danych. Każdą instrukcję języka SQL kończy średnik.

Ćwiczenie 1. Analizujemy kod SQL kwerendy

1. Otwórz plik TB4_c1_Hurtownia.mdb (tabele zawierają fikcyjne dane).
   TB4_c1_Hurtownia.mdbPobierz
2. Otwórz kwerendę KListaZamowien i przeanalizuj jej kod SQL. W dalszej części tematu wyjaśnimy znaczenie klauzul instrukcji SELECT występujących w tym kodzie.
   Wskazówka: Aby zobaczyć kod SQL kwerendy, należy z menu kontekstowego formularza wybrać polecenie Widok SQL.

Przykład 1. Stosowanie prostej instrukcji SELECT

Utworzymy instrukcję wybierającą wartości pól Imie i Nazwisko dla każdego rekordu z tabeli Klienci.

Otwieramy bazę Hurtownia. Aby samodzielnie napisać polecenie w języku SQL, rozpoczynamy od utworzenia nowej kwerendy w Widoku projektu. Na ekranie wyświetli się okienko Pokazywanie tabeli. Zamykamy je, nie wybierając żadnej z tabel. W ten sposób powstała nowa pusta kwerenda.

Przechodzimy teraz do Widoku SQL utworzonej kwerendy. Wpisujemy polecenie (rys. 2.):

SELECT Imie, Nazwisko FROM Klienci;

Wynik działania kwerendy możemy zobaczyć w Widoku arkusza danych (rys. 3.).

Ćwiczenie 2. Stosujemy prostą instrukcję SELECT

1. Przygotuj kwerendę zgodnie z opisem podanym w przykładzie 1.
2. Zmodyfikuj utworzoną kwerendę, aby wyświetlały się wartości wszystkich pól.
   Wskazówka: Listę pól w instrukcji SELECT zastąp znakiem gwiazdki.

W przykładzie 1. pokazaliśmy, jak utworzyć prostą kwerendę pobierającą dane z jednej tabeli. Zwykle jednak potrzebne są dane z kilku tabel. Utworzymy teraz kwerendę pobierającą informacje z dwóch tabel połączonych relacjami.

Przykład 2. Pobieranie danych z kilku tabel

Załóżmy, że chcemy wyświetlić listę zamówień wraz z nazwiskami klientów, którzy ich dokonali. Tabelę Klienci połączono relacją „jeden do wielu” z tabelą Zamowienia.

Użyjmy następującego kodu kwerendy (rys. 4.):

SELECT IdOwocu, LiczbaKg, DataZlozenia, DataRealizacji, Nazwisko, Imie FROM Klienci, Zamowienia WHERE Klienci.IdKlienta=Zamowienia.IdKlienta;

W instrukcji pojawiła się nowa klauzula WHERE. Określa ona warunki, które muszą spełniać wybierane rekordy – w tym przypadku wartość pola IdKlienta z tabeli Klienci musi być równa wartości pola IdKlienta z tabeli Zamowienia.

Dlaczego klauzula WHERE jest ważna? Gdyby jej nie było, każda kombinacja dwóch rekordów z tabel Zamowienia i Klienci spełniałaby warunki zapytania. Dla czternastu klientów i dwunastu zamówień otrzymalibyśmy więc 14 x 12 = 168 rekordów wynikowych. Tylko część z nich reprezentowałaby rzeczywiste zamówienia złożone przez klientów.

W przykładzie 2. pokazaliśmy, jak stosować klauzulę WHERE podczas pobierania rekordów z dwóch tabel połączonych relacjami. Warto zwrócić uwagę, że w poleceniu SELECT klauzulę WHERE umieszczamy zawsze po klauzuli FROM:

SELECT Pole1, Pole2 FROM Tabela1, Tabela2 WHERE Tabela1.Id=Tabela2.Id;

Klauzula WHERE przydaje się również, kiedy chcemy określić warunki, jakie ma spełniać wartość określonego pola.

Na przykład, aby z tabeli Tabela1 wybrać rekordy, dla których Pole1 ma wartość „kwiatek”, a wartość pola Numer jest większa od 10 (warto zauważyć, że ciąg znaków umieszczamy w apostrofach; w przypadku liczb apostrofów nie stosujemy), możemy napisać:

SELECT * FROM Tabela1 WHERE Pole1='kwiatek' AND Numer>10;

W klauzuli WHERE możemy stosować wszystkie standardowe operatory porównania:

= (równy), <> (różny), > (większy), >= (większy lub równy), < (mniejszy), <= (mniejszy lub równy).

Do łączenia warunków stosujemy operatory logiczne AND (i) oraz OR (lub).

Przykład 3. Stosowanie operatora logicznego AND

Pobieranie listy owoców w cenie od 2 do 4 zł za kilogram:

SELECT NazwaOwocu, CenaKg FROM Owoce WHERE CenaKg>=2 AND CenaKg<=4;

Ćwiczenie 3. Piszemy kwerendę pobierającą dane z kilku tabel

1. Przygotuj kwerendę zgodnie z opisem podanym w przykładzie 2.
2. Zmodyfikuj utworzoną kwerendę, aby wyświetlały się tylko zamówienia Janusza Nowaka.
3. Zapisz kwerendę.

Ćwiczenie 4. Piszemy kwerendę wyświetlającą listę zamówień na jabłka

1. Napisz kwerendę, która wyświetli listę zamówień na jabłka. W tabeli wynikowej powinny być widoczne kolumny z imieniem i nazwiskiem klienta, datą złożenia i datą realizacji zamówienia oraz liczbą kilogramów.
2. Zapisz kwerendę.

Zwykle w zapytaniach podajemy nazwy pól, bez określania tabel. Nawet jeśli pobieramy dane z kilku tabel, to baza potrafi rozpoznać, z której z nich pochodzi dane pole. Tylko w sytuacji, kiedy w dwóch tabelach umieszczonych w klauzuli FROM znajdą się pola o takich samych nazwach, odwołując się do nich, musimy podawać nazwę tabeli oddzieloną kropką od nazwy pola: Tabela.Pole.

3. Wybrane klauzule instrukcji SELECT

Omówiliśmy instrukcję SELECT z jej podstawowymi klauzulami – FROM i WHERE. Pokażemy teraz, jak ograniczać zakres pobieranych danych do unikalnych rekordów, sortować je i grupować. Opiszemy także sposoby łączenia danych z kilku tabel.

Przykład 4. Stosowanie słowa kluczowego DISTINCT

Zapytanie:

SELECT DISTINCT Miejscowosc FROM Dostawcy;

zwraca listę miejscowości, w których mamy dostawców. Nawet jeśli kilku dostawców pochodzi z jednej miejscowości, zostanie ona wymieniona na liście tylko raz (rys. 6.).

Ćwiczenie 5. Stosujemy słowo kluczowe DISTINCT

1. Utwórz kwerendę, korzystając z przykładu 4.
2. Zapisz kwerendę.

3.1. Klauzula ORDER BY

Przykład 5. Stosowanie klauzuli ORDER BY

Zapytanie:

SELECT Nazwisko, Imie FROM Klienci ORDER BY Nazwisko, Imie;

zwróci listę klientów posortowanych w porządku alfabetycznym (rys. 7.). Możemy także określić odwrotny porządek sortowania:

SELECT Nazwisko, Imie FROM Klienci ORDER BY Nazwisko DESC, Imie DESC;

Słowo DESC obok nazwy kolumny oznacza sortowanie w porządku malejącym (DESC to skrót angielskiego słowa descending – „schodzenie”, „schodzący”).

ASC (skrót angielskiego słowa ascending) oznacza sortowanie w porządku rosnącym.

Zauważmy, że odwrotny porządek sortowania określamy dla każdej kolumny z osobna.

Ćwiczenie 6. Stosujemy klauzulę ORDER BY

1. Zmodyfikuj kwerendę z przykładu 2., aby lista zamówień wyświetlała się według dat ich złożenia.
2. Zapisz kwerendę.

3.2. Klauzula INNER JOIN

Zapytanie:

SELECT Klienci.Nazwisko, Klienci.Imie, Zamowienia.DataZlozenia,
Zamowienia.DataRealizacji FROM Klienci INNER JOIN Zamowienia ON Klienci.IdKlienta = Zamowienia.IdKlienta; [1]

zwraca dokładnie takie same rezultaty jak:

SELECT Klienci.Nazwisko, Klienci.Imie, Zamowienia.DataZlozenia, Zamowienia.DataRealizacji FROM Klienci, Zamowienia WHERE Klienci.IdKlienta=Zamowienia.IdKlienta; [2]

Program Microsoft Access stosuje klauzulę INNER JOIN w przypadku, gdy w oknie projektu kwerendy tworzymy kwerendę pobierającą dane z więcej niż jednej tabeli.

Przykład 6. Stosowanie klauzuli INNER JOIN

Za pomocą Kreatora prostych kwerend (rys. 8.) utworzymy kwerendę wyświetlającą listę zamówień złożonych przez określonych klientów. Wybieramy do wyświetlania pola

Nazwisko i Imie z tabeli Klienci oraz DataZlozenia i DataRealizacji z tabeli Zamowienia.

Po utworzeniu kwerendy przechodzimy do Widoku SQL. Możemy zobaczyć, w jaki sposób program Microsoft Access zrealizował nasze zapytanie (rys. 9.).

Takie same rezultaty można uzyskać, pisząc kwerendę [2].

Ćwiczenie 7. Stosujemy klauzulę INNER JOIN

1. Zmodyfikuj kwerendę z przykładu 6., aby dodatkowo wyświetlała się liczba kilogramów i nazwa zamówionego owocu.
2. Przeanalizuj kod SQL kwerendy.

Ćwiczenie 8. Analizujemy kod SQL kwerendy

1. Otwórz bazę SOWy (tworzoną w tematach B1-B3).
2. Otwórz kwerendę KWypozyczenia. Przeanalizuj jej kod SQL (rys. 10.). Jakie występują w niej klauzule instrukcji SQL? Omów ich przeznaczenie.

3.3. Klauzula LEFT JOIN

Oznacza to, że w wynikach będą uwzględnione wszystkie rekordy z tabeli znajdującej się po lewej stronie napisu LEFT JOIN (Tabela1), nawet jeśli w tabeli po prawej stronie (Tabela2) nie ma odpowiadających im wpisów. W takim przypadku do pól z tabeli Tabela1 będą dołączone pola o wartościach Null, odpowiadające polom z tabeli Tabela2.

Przykład 7. Stosowanie klauzuli LEFT JOIN

Chcemy uzyskać listę dostawców wraz z ich adresami korespondencyjnymi. Jednak nie każdy dostawca posiada taki adres.

Zapytanie:

SELECT * FROM Dostawcy, AdresyKorespondencyjne WHERE Dostawcy.
IdDostawcy = AdresyKorespondencyjne.IdDostawcy;

zwróci tylko tych dostawców, którzy mają adres korespondencyjny.

Aby uzyskać listę wszystkich dostawców, należy zastosować złączenie lewostronne:

SELECT * FROM Dostawcy LEFT JOIN AdresyKorespondencyjne ON Dostawcy.
IdDostawcy = AdresyKorespondencyjne.IdDostawcy;

Wyniki działania kwerendy ze złączeniem lewostronnym widać na rysunku 11. (liczba wyświetlanych pól została tu ograniczona). Rekordy z tabeli Dostawcy bez przypisanych adresów korespondencyjnych mają wartości Null w polach odpowiadających adresowi korespondencyjnemu.

Ćwiczenie 9. Stosujemy klauzulę LEFT JOIN

Wykonaj kwerendę z przykładu 7., dokonującą lewostronnego złączenia danych z tabel Dostawcy i AdresyKorespondencyjne. Dopisz w wynikach kwerendy dane adresowe jednego z dostawców. Przeanalizuj efekty.

3.4. Klauzula GROUP BY

Wywołanie:

SELECT Pole1 FROM Tabela1 GROUP BY Pole1;

daje takie same rezultaty jak:

SELECT DISTINCT Pole1 FROM Tabela1;

Klauzula GROUP BY pozwala dodatkowo stosować funkcje agregujące na zgrupowanych rekordach.

Funkcje agregujące pozwalają obliczać wartości na podstawie grup rekordów.

Najczęściej stosuje się funkcje:

COUNT(*) – zwraca liczbę rekordów w każdej grupie,

SUM(Liczba1) – zwraca sumę wartości w kolumnie Liczba1 w każdej grupie,

AVG(Liczba2) – zwraca średnią wartość w kolumnie Liczba2 w każdej grupie,

MAX(Liczba3) – zwraca maksymalną wartość zapisaną w kolumnie Liczba3 w każdej grupie,

MIN(Liczba4) – zwraca minimalną wartość zapisaną w kolumnie Liczba4 w każdej grupie.

Zastosowanie funkcji COUNT i AVG pokażemy na przykładach.

Przykład 8. Stosowanie funkcji COUNT

Policzymy, ilu dostawców mamy w każdej miejscowości. Należy wybrać rekordy z tabeli Dostawcy, grupując je według pola Miejscowosc, i skorzystać z funkcji agregującej COUNT(*):

SELECT COUNT(*) AS LiczbaOsob, Miejscowosc FROM Dostawcy GROUP BY Miejscowosc;

Warto zwrócić uwagę, że po funkcji COUNT(*) umieszczamy słowo kluczowe AS i wybrany identyfikator. Oznacza to, że wyniki działania tej funkcji będą widoczne po wykonaniu zapytania w kolumnie o nazwie LiczbaOsob (rys. 12.).

Ćwiczenie 10. Modyfikujemy kwerendę

1. Zmodyfikuj kwerendę z przykładu 8., aby wyniki były posortowane malejąco według liczby dostawców w miejscowości.
2. Zapisz kwerendę.
   Wskazówka: Umieść w klauzuli ORDER BY LiczbaOsob.

Przykład 9. Stosowanie funkcji AVG

Napiszemy kwerendę, która policzy średnią wartość zamówień dokonywanych przez poszczególnych klientów i posortuje rekordy malejąco według tej wartości (rys. 13.).

Skorzystamy z funkcji agregującej AVG:

SELECT Klienci.Nazwisko, Klienci.Imie, AVG(Zamowienia.LiczbaKg*Owoce.CenaKg) AS SrednieZamowienie FROM Klienci LEFT JOIN (Zamowienia LEFT JOIN Owoce ON Zamowienia.IdOwocu=Owoce.IdOwocu) ON Klienci.IdKlienta=Zamowienia.IdKlienta GROUP BY Klienci.Nazwisko, Klienci.Imie, Zamowienia.IdKlienta ORDER BY SrednieZamowienie DESC;

Można wyświetlać tylko wartości funkcji zagregowanych i pola, które są wymienione w klauzuli GROUP BY. Dlatego warto zauważyć, że aby wyświetlić wartości pól Imie i Nazwisko klienta, musimy ich nazwy umieścić po GROUP BY.

Ćwiczenie 11. Modyfikujemy kwerendę

1. Zmodyfikuj kwerendę z przykładu 9., aby wyświetlała się również suma wartości zamówień dokonanych przez klienta (rekordy powinny być posortowane malejąco według tej sumy). Na liście nie wyświetlaj klientów, którzy nie dokonali zamówień.
2. Zapisz kwerendę.

4. Dopisywanie rekordów

Po nazwie tabeli wpisujemy w nawiasach okrągłych listę pól (oddzielonych przecinkami). Następnie umieszczamy słowo kluczowe VALUES i w nawiasach okrągłych podajemy listę wartości (w takiej kolejności, w jakiej podaliśmy listę pól). Wartości takie, jak napisy, numery telefonów, kody pocztowe itp., umieszczamy zawsze pomiędzy znakami apostrofu. Liczby i dane w formacie walutowym podajemy bez apostrofów, używając kropki jako separatora dziesiętnego.

Na liście pól nie musimy umieszczać nazw wszystkich kolumn występujących w tabeli. Pomijamy na przykład pola typu AUTO_INCREMENT (Autonumerowanie) – baza danych automatycznie przypisuje im wartość.

Przykład 10. Dodawanie rekordów

Dodamy do bazy dane nowego owocu. Wywołamy w tym celu polecenie:

INSERT INTO Owoce (NazwaOwocu, CenaKg) VALUES ('Banany', 4.70);

Ćwiczenie 12. Dodajemy rekordy

Napisz instrukcję INSERT, która doda dane nowego klienta do tabeli Klienci.

5. Aktualizacja danych

W instrukcji UPDATE stosujemy klauzulę SET, aby określić wartości, jakie chcemy zapisać w bazie (podajemy nazwę pola, a po znaku równości wartość, która zostanie mu przypisana).

Opcjonalna klauzula WHERE określa, które rekordy zostaną zaktualizowane.

Przykład 11. Stosowanie klauzuli SET

Napiszemy instrukcję, która podniesie ceny wszystkich owoców o 10%:

UPDATE Owoce SET CenaKg = CenaKg * 1.10;

Instrukcja ta poleca bazie danych pomnożyć wartość pola CenaKg dla każdego owocu przez liczbę 1.10 (pamiętaj, że do zapisu liczb używamy kropki dziesiętnej zamiast przecinka) i zapisać jako nową cenę.

Można również dodać klauzulę WHERE, aby na przykład zmienić cenę owocu o konkretnym Id:

UPDATE Owoce SET CenaKg = 2.00 WHERE IdOwocu = 4;

Można też zmienić cenę owocu o konkretnej nazwie:

UPDATE Owoce SET CenaKg = CenaKg + 0.30 WHERE NazwaOwocu = 'Wiśnie';

Ćwiczenie 13. Stosujemy klauzulę SET

Napisz instrukcję, która obniży o 15% ceny owoców kosztujących więcej niż 2 złote za kilogram.

6. Usuwanie rekordów

W przypadku instrukcji DELETE, podobnie jak dla instrukcji UPDATE, należy dokładnie sprawdzać klauzulę WHERE. Po wykonaniu zapytania cofnięcie wprowadzonych zmian jest niemożliwe.

Przykład 12. Usuwanie jednego rekordu

Załóżmy, że omyłkowo wprowadziliśmy do bazy dane nowego owocu. Nowemu rekordowi został nadany numer Id 12. Możemy usunąć ten rekord, wywołując kwerendę:

DELETE FROM Owoce WHERE IdOwocu = 12;

Zastosowanie instrukcji DELETE jest ograniczone przez więzy integralności. Nie uda się nam usunąć z bazy rekordu, do którego odwołania znajdują się w innych tabelach. Oznacza to na przykład, że nie będziemy mogli usunąć danych klienta, który wcześniej złożył zamówienia. W takim przypadku musimy najpierw usunąć wszystkie rekordy z tabeli Zamowienia powiązane z danym klientem – wraz z nimi znikną odwołania do danych tego klienta. Dopiero potem można usunąć dane klienta. Przypomnijmy, że z podobną sytuacją spotkaliśmy się w systemie SOWy, gdy chcieliśmy usunąć dane klienta wypożyczalni filmów, który miał wypożyczone filmy.

Przykład 13. Usuwanie wielu rekordów

Wykorzystamy mechanizm chroniący rekordy, do których istnieją odwołania, aby usunąć z bazy dane klientów, którzy nigdy nie składali zamówień. Wystarczy wywołać zapytanie:

DELETE FROM Klienci;

Celowo pominęliśmy klauzulę WHERE – tak sformułowane zapytanie poleca usunąć wszystkie rekordy z tabeli.

Jeśli jednak prawidłowo zdefiniowaliśmy relacje, baza pozwoli usunąć tylko te rekordy, które nie są powiązane więzami integralności (czyli klientów, którzy nie składali zamówień).

Ćwiczenie 14. Usuwamy rekordy

1. Dodaj nowe zamówienie, korzystając z formularza FZamowienia.
2. Następnie usuń je, korzystając z instrukcji DELETE (klient się rozmyślił).

Zadania

1. Otwórz bazę TB4_z1_Hurtownia.mdb (tabele zawierają fikcyjne dane). Napisz w języku SQL kwerendę, która:
   TB4_z1_Hurtownia.mdbPobierz
   1. wyświetli wszystkich klientów pochodzących z Wrocławia,
   2. wyświetli listę dostawców z Wrocławia, którzy mają adresy korespondencyjne,
   3. wyświetli nazwy owoców, które kosztują od 2 do 4 zł za kilogram,
   4. zmieni numer telefonu Mariana Nowaka na 888-01-01,
   5. doda nowy owoc – arbuzy w cenie 1,5 zł za kilogram,
   6. usunie z bazy dane klienta o Id 14, jeśli nie złożył żadnego zamówienia.
2. Zmodyfikuj kwerendę KListaZamowien w bazie Hurtownia, aby wyświetlała się lista niezrealizowanych zamówień.
   Wskazówka: Niezrealizowane zamówienia mają wartość Null w polu DataRealizacji. Wyszukasz je, umieszczając w klauzuli WHERE warunek DataRealizacji IS NULL.
3. Napisz kwerendę, która wyświetli liczbę zamówień dokonanych przez poszczególnych klientów w bazie Hurtownia (bez uwzględniania klientów, którzy nie dokonali żadnych zamówień).
4. Otwórz bazę SOWy, a w niej formularz FZwroty. Przeanalizuj kod SQL kwerendy, która sortuje dane klienta w tym formularzu (temat B3, przykład 5.). Jakie występują w niej klauzule instrukcji SQL? Omów ich przeznaczenie.