Wykład 9

Język baz danych – SQL

Streszczenie

Dla relacyjnych baz danych został opracowany specjalny język o nazwie SQL (ang. Structured Query Language - Strukturalny Język Zapytań), umożliwiający dostęp i przetwarzanie danych w bazie danych – na poziomie obiektów modelu relacyjnego tj. tabel i perspektyw.

Wykład 9 omawia podstawowe rodzaje instrukcji SQL, których znajomość jest potrzebna przy tworzeniu aplikacji bazodanowej w dowolnym środowisku implementacyjnym, w szczególności w MS Access.
 

Wprowadzenie

Jako międzynarodowy standard dla relacyjnych baz danych został opracowany specjalny język o nazwie SQL (ang. Structured Query Language - Strukturalny Język Zapytań), umożliwiający dostęp i przetwarzanie danych w bazie danych – na poziomie obiektów modelu relacyjnego tj. tabel i perspektyw.

Ponadto został także opracowany sposób użycia instrukcji tego języka w programach konwencjonalnych języków programowania jak C, C++, Java, Visual Basic. O użyciu instrukcji języka SQL w językach Visual Basic i Java będzie mowa na następnych wykładach.

Znamienne jest też powszechne stosowanie narzędzi generowania aplikacji klienckich takich jak MS Access czy Oracle Forms bez potrzeby sięgania do tradycyjnego sposobu programowania.

Omówimy kolejno najważniejsze instrukcje języka SQL. Pokazywane przykłady będą dotyczyć bazy danych Faktury o następującym schemacie:

Schemat ten jest prawie identyczny do tego, który był przedstawiony na wykładzie 3. Jedyna różnica to dodana tabela Pracownicy, połączona  związkiem z tabelą Faktury. Dla tego związku została wyspecyfikowana reguła złączenia zewnętrznego mówiąca, że przy rozważaniu faktur i pracowników, uwzględniamy również faktury, dla których nie został określony żaden pracownik wypisujący fakturę.

Do wykonywania instrukcji SQL będziemy używać środowiska programu MS Access.  Na wykładzie 2 było pokazane jak tworzyć kwerendy w siatce kwerendy. Jednym z widoków dla kwerendy jest widok SQL (SQL View). W ten sposób najprościej otrzymać instrukcję SQL w środowisku MS Access. Jednak sposób ten jest ograniczony do MS Access. Dlatego na tym wykładzie będziemy używać metody bezpośredniego formułowania tekstu instrukcji SQL w edytorze tekstowym. Czytelnik pozna w ten sposób programowanie w języku SQL.

Byłoby dobrze aby w trakcie studiowania tego wykładu czytelnik sam testował działanie omawianych instrukcji. W tym celu należy utworzyć bazę danych Faktury i wprowadzić do utworzonych tabel przykładowe dane.
 

Instrukcja SELECT

Instrukcja SELECT wydobywa dane z bazy danych. Składa się z części nazywanych klauzulami. Trzeba mianowicie określić:

• z jakich tabel w bazie danych mają być sprowadzone dane - tak zwaną klauzulę FROM,
• jakie warunki mają spełniać dane - tak zwaną klauzulę WHERE i
• w jakiej postaci mają się pojawiać przed użytkownikiem (aplikacją użytkownika) - tak zwaną klauzulę SELECT.

SELECT nazwa_kolumny,... FROM nazwa_tabeli [WHERE warunek];

Uwagi do notacji składniowej:

1. Zapis: nazwa_kolumny,...  daje możliwość użycia jednej lub więcej nazw kolumn rodzielonych przecinkami.
2. Meta-nawiasy [...] oznaczają opcjonalne fragmenty. To znaczy, powyższa definicja składniowa, w sposób zwarty, definiuje nam dwie postacie instrukcji SELECT:

SELECT nazwa_kolumny,... FROM nazwa_tabeli;

SELECT nazwa_kolumny,... FROM nazwa_tabeli WHERE warunek;

Ropoczniemy od przykładów instrukcji SELECT nie zawierających klauzuli WHERE. Oto nasze pierwsze zadanie.

Wypisz imiona i nazwiska pracowników firmy.

Definiujemy kwerendę jako zapytanie SQL:

SELECT Imie, Nazwisko FROM Pracownicy;

Na zajęciach w salach laboratoryjnych do wykonywania instrukcji SQL będziemy używać programu MS Access. Zapytanie SQL tworzymy w następujący sposób:

1. W oknie bazy danych wybieramy zakładkę "Kwerendy" ("Queries") a następnie pozycję z listy "Utwórz kwerendę w widoku projekt"  ("Create Query in Design View").
2. Zamykamy okienko "Pokazywanie tabeli" ("Show Table") nie wybierając z listy żadnej tabeli.
3. Z menu "Plik" ("File") wybieramy opcję "Widok SQL" ("SQL View").
4. W wyświetlonym okienku wpisujemy tekst instrukcji SELECT po czym ją zapisujemy jako kwerendę i wykonujemy.

Oto zapis w okienku MS Access w widoku SQL:

Oraz wynik zapytania:

Kolejne zapytanie jest jeszcze prostsze.

Wypisz zawartość całej tabeli.

Jak można się domyśleć, oznaczeniem "wszystkiego" jest gwiazdka *.

SELECT * FROM Pracownicy;

Oto zapis w okienku MS Access:

Wynik:

Teraz zadanie do rozwiązania dla czytelnika:

Wypisz imiona, nazwiska i telefony klientów.

Wypisywany wynik można sformatować używając w tym celu wyrażeń. Załóżmy, że interesuje nas informacja tekstowa o pracownikach. Stosujemy operator konkatenacji napisów '&'. Operator AS daje nam możliwość określenia etykiety dla wynikowej kolumny.

SELECT "Osoba: " & Imie & " " & Nazwisko & " pracuje na stanowisku: " & Stanowisko AS [Informacja o pracownikach] FROM Pracownicy;

 

Użyliśmy nawiasów kwadratowych ponieważ w nazwie wynikowej kolumny występują spacje. W rezultacie otrzymujemy:

Teraz zadanie do rozwiązania dla czytelnika:

Wypisz pełną informację o klientach w postaci słownej.

Przejdziemy teraz do przykładów instrukcji SELECT zawierających klauzulę WHERE. Kolejne nasze zadanie to:

 

Wypisz wszystkich kierowników.

Kierowników wyznacza warunek Stanowisko="Kierownik". Wstawiamy go do klauzuli WHERE.

SELECT Imie, Nazwisko FROM Pracownicy WHERE Stanowisko = "Kierownik";

Wynik:

Teraz zadanie do rozwiązania dla czytelnika:

Wyznacz wszystkich klientów o imieniu "Jan".

Kolejne zadanie.
 

Wypisz numery telefonów pracowników bez powtórzeń.

A oto jego rozwiązanie - korzystające z operatora DISTINCT, który powoduje eliminację powtarzających się wierszy wynikowych.

SELECT DISTINCT Telefon FROM Pracownicy;

W wyniku otrzymujemy trzy wiersze; wśród nich jeden pusty - reprezentujący NULL:

ORDER BY

Kolejną klauzulą instrukcji SELECT jest ORDER BY. Pozwala ona wyspecyfikować kolejność w jakiej wynikowe wiersze mają być sprowadzane z bazy danych. Klauzula ORDER BY jest klauzulą instrukcji SELECT występującą na jej końcu. Ma postać:
            ORDER BY kolumna [specyfikator], ... 
gdzie specyfikator oznacza porządek sortowania wynikowych wierszy albo ASC (rosnący) - domyślny, albo DESC (malejący).

Wypisz pracowników w odwrotnej kolejności alfabetycznej według nazwisk.

Stosujemy specyfikator DESC.

SELECT Imie, Nazwisko FROM Pracownicy ORDER BY Nazwisko DESC;

Oto wynik:

Zwykłą kolejność alfabetyczną uzyskujemy podając specyfikator ASC w miejsce DESC. Gdy nie podamy ani ASC ani DESC domyślnie przyjmowany jest specyfikator ASC.

Teraz zadanie do rozwiązania dla czytelnika:

Wypisz informacje o klientach w kolejności alfabetycznej według nazwisk, przy takich samych nazwiskach biorąc pod uwagę imiona, a przy takich samych imionach i nazwiskach niech decyduje Id_klienta.

IS [NOT] NULL

Operatorem testującym obecność NULL jest IS NULL a drugim testującym brak NULL -  IS NOT NULL.
 

Wypisz pracowników, którzy nie mają określonego numeru telefonicznego.

Stosujemy IS NULL.

[NOT] BETWEEN

Operator BETWEEN sprawdza czy dana wartość należy do określonego przedziału wartości - końce przedziału są wliczane do przedziału.
Operator NOT BETWEEN sprawdza - czy dana wartość nie należy do określonego przedziału wartości.
 

Wypisz pracowników, których identyfikatory znajdują się w określonym przedziale wartości od 2 do 4.

Operator BETWEEN daje się zapisać w postaci koniunkcji dwóch nierówności: 2<=Id_pracownika AND Id_pracownika<=4. Która postać jest czytelniejsza?
 

[NOT] LIKE

Operator LIKE sprawdza, czy w danym napisie występuje określony wzorzec - np. czy na początku (podobnie w środku, na końcu) napisu występuje dana litera. Operator NOT LIKE ma działanie odwrotne. Przypominamy z wykładu 6, że znakami uniwersalnymi we wzorcu są: "*" - cokolwiek, "?" - jeden znak.
 

Wypisz pracowników,  których nazwiska zaczynają się na literę "K".

Teraz zadanie do rozwiązania dla czytelnika:

Wyznacz klientów, których nazwiska zaczynają się na literę "K" a kończą się na literę "i".

[NOT] IN

Operator IN sprawdza czy wartość podana jakio lewy argument występuje na liście wartości będącej prawym argumentem. Operator NOT IN ma działanie odwrotne. Oto przykład:
 

 

SELECT Imie, Nazwisko FROM Pracownicy WHERE Stanowisko IN ("Dyrektor", "Kierownik", "Prezes");

Warunek Stanowisko IN ("Dyrektor", "Kierownik", "Prezes") jest równoważny alternatywie:

Która postać jest czytelniejsza?
 

OR, AND, NOT

Proste warunki logiczne możemy łączyć spójnikami logicznymi: alternatywy "lub", koniunkcji "i" oraz negacji "nie prawda, że".

 

Wypisz pracowników, których nazwiska kończą się na literę "i" oraz którzy posiadają określony numer telefonu.

Mamy więc o czynienia z koniunkcją dwóch prostych warunków logicznych.

SELECT Imie, Nazwisko FROM Pracownicy WHERE Nazwisko LIKE "*i" AND Telefon IS NOT NULL;

Teraz zadanie do rozwiązania dla czytelnika:

 

Wyznacz klientów, którzy nie mają określonego numeru telefonu lub adresu.

Instrukcja INSERT

Instrukcja INSERT służy do wstawiania wierszy do tabeli. Ma dwie klauzule INSERT INTO oraz VALUES:

INSERT INTO tabela(kolumna,...) VALUES(wartość,...);

Kolumny, które nie występują na liście VALUES uzyskują przy wstawieniu wiersza wartość NULL chyba, że są typu Autonumer (AutoNumber) albo zostały dla nich określone wartości domyślne.

Oto nasze zadanie.
 

Dodaj nowego pracownika do tabeli Pracownicy.

INSERT INTO Pracownicy(Imie, Nazwisko, Stanowisko) VALUES("Adam", "Sapieha", "Dyrektor");

Na liście kolumn nie występują dwie nazwy kolumn tabeli Pracownicy: Id_pracownika uzyska automatycznie wartość będącą kolejnym numerem, a Telefon – uzyska NULL.
 

Instrukcja DELETE

Instrukcja DELETE służy do usuwania wierszy z tabeli. Instrukcja ma dwie klauzule: wymaganą DELETE FROM i opcjonalną WHERE.

DELETE FROM tabela [WHERE warunek];

Z danej tabeli zostają usunięte wszystkie wiersze spełniające podany warunek - gdy brak klauzuli WHERE - wszystkie wiersze tabeli.

DELETE FROM Pracownicy WHERE Stanowisko="Dyrektor";

Instrukcja UPDATE

Instrukcja UPDATE służy do aktualizacji wierszy w tabeli. Instrukcja ma trzy klauzule: dwie wymagane UPDATE i SET oraz jedną opcjonalną WHERE.

UPDATE tabela SET kolumna = wyrażenie, ... WHERE warunek;

W danej tabeli zostają zmodyfikowane wszystkie wiersze spełniające podany warunek. Modyfikacja polega na zastosowaniu instrukcji przypisania kolumna=wyrażenie do każdej kolumny, której nazwa znajduje się po lewej stronie równości w klauzuli SET.

UPDATE Pracownicy  SET Telefon = "678-9981" WHERE Telefon = "679-9981";

Teraz zadanie do rozwiązania dla czytelnika:

 

W tabeli Klienci zmień wartości NULL kolumny Telefon na napis "BRAK".

Operator UNION

Jest możliwość połączenia wyników kilku instrukcji SELECT o ile dają wyniki zgodnych typów danych. Służy do tego operator UNION.

instrukcja_SELECT UNION instrukcja_SELECT

Ta postać zapytania nie ma swojego odpowiednika w siatce kwerendy. Musi być użyte tekstowe okienko do wpisania instrukcji SQL, do którego można dojść na dwa sposoby: albo postępując tak samo jak uprzednio albo po dojściu do siatki kwerendy wybierając z menu "Kwerenda -> Wyłącznie SQL -> Kwerenda składająca" ("Query -> SQL Specific -> UNION").

Oto rezultat:

Wśród opcji zebranych w "Kwerenda –> Wyłącznie SQL" ("SQL Specific") są jeszcze dwie:

1. definicja danych (Data Definition) - instrukcje definiowania danych: tworzenie tabeli (CREATE TABLE), zmiana schematu tabeli (ALTER TABLE) oraz usuwanie tabeli (DROP TABLE) - omówimy je na wykładzie przedmiotu "Systemy baz danych", a także
2. kwerenda przekazująca (Pass-Through) - instrukcja SQL, która ma być wykonana w odległej bazie danych – składnia rozumiana przez odległą bazę danych (wymaga określenia DSN do zewnętrznego źródła danych).
   

Przejście do drugiej części wykładu