SQL. Ćwiczenia praktyczne [2 ed.] [PDF]

Zitiervorschau

Wszelkie prawa zastrzeżone. Nieautoryzowane rozpowszechnianie całości lub fragmentu niniejszej publikacji w jakiejkolwiek postaci jest zabronione. Wykonywanie kopii metodą kserograficzną, fotograficzną, a także kopiowanie książki na nośniku filmowym, magnetycznym lub innym powoduje naruszenie praw autorskich niniejszej publikacji. Wszystkie znaki występujące w tekście są zastrzeżonymi znakami firmowymi bądź towarowymi ich właścicieli. Autor oraz Wydawnictwo HELION dołożyli wszelkich starań, by zawarte w tej książce informacje były kompletne i rzetelne. Nie biorą jednak żadnej odpowiedzialności ani za ich wykorzystanie, ani za związane z tym ewentualne naruszenie praw patentowych lub autorskich. Autor oraz Wydawnictwo HELION nie ponoszą również żadnej odpowiedzialności za ewentualne szkody wynikłe z wykorzystania informacji zawartych w książce. Redaktor prowadzący: Ewelina Burska Projekt okładki: Maciej Pasek Materiały graficzne na okładce zostały wykorzystane za zgodą iStockPhoto Inc. Wydawnictwo HELION ul. Kościuszki 1c, 44-100 GLIWICE tel. 32 231 22 19, 32 230 98 63 e-mail: [email protected] WWW: http://helion.pl (księgarnia internetowa, katalog książek) Drogi Czytelniku! Jeżeli chcesz ocenić tę książkę, zajrzyj pod adres http://helion.pl/user/opinie?cwsqw2_p Możesz tam wpisać swoje uwagi, spostrzeżenia, recenzję.

ISBN: 978-83-246-3478-1 Numer katalogowy: 6289

Copyright © Helion 2011 Printed in Poland.

• Poleć książkę na Facebook.com • Kup w wersji papierowej • Oceń książkę

• Księgarnia internetowa • Lubię to! » Nasza społeczność

Lidce, Jarkowi, Arturowi i Andrzejowi Rzepkom — z sympatią i uśmiechem :)

Spis treści Wstęp Rozdział 1. Podstawy relacyjnych baz danych Tabele Klucze Relacje Podstawowe zasady projektowania tabel Określenie celu Duplikowanie danych Informacje atomowe Puste pola Jednoznaczna identyfikacja rekordów

Rozdział 2. Praca z tabelami Typy danych Typy liczbowe Typy daty i czasu Typy łańcuchowe Typ null Tworzenie i wybór bazy danych Tworzenie tabel Atrybuty kolumn Indeksy Modyfikacja tabel Usuwanie tabel

9 13 13 14 15 20 20 21 22 24 26

27 27 28 29 29 30 31 32 34 39 42 49

6

SQL • Ćwiczenia praktyczne

Rozdział 3. Umieszczanie danych w bazie Instrukcja INSERT INTO Wprowadzanie wielu wierszy Druga postać instrukcji INSERT

Rozdział 4. Pobieranie danych z tabel Podstawy instrukcji SELECT Sortowanie wyników zapytań Kryteria pobierania danych Niepowtarzalność wierszy

51 51 57 59

61 61 65 67 75

Rozdział 5. Modyfikacja i usuwanie danych

77

Instrukcja UPDATE Modyfikacja danych w tabelach Usuwanie danych

77 78 81

Rozdział 6. Złączenia Łączenie wyników zapytań Instrukcja UNION Instrukcja INTERSECT Instrukcja EXCEPT Pobieranie danych z wielu tabel Złączenia Złączenie typu CROSS JOIN Złączenie typu INNER JOIN Złączenie typu LEFT OUTER JOIN Złączenie typu RIGHT OUTER JOIN Złączenie typu FULL OUTER JOIN Złączenia i klauzula WHERE Aliasy tabel i rozróżnianie nazw kolumn

85 85 86 87 88 89 94 94 95 96 97 99 100 100

Rozdział 7. Funkcje agregujące

105

Rozdział 8. Grupowanie danych

115

Rozdział 9. Podzapytania

123

Podzapytania w klauzuli FROM Podzapytania klauzuli WHERE Podzapytania proste Podzapytania skorelowane Podzapytania w instrukcjach aktualizujących dane

124 126 126 128 130

Spis treści

Rozdział 10. Transakcje Transakcje w systemach baz danych Objęcie instrukcji transakcją Wycofywanie transakcji Izolacja transakcji

Rozdział 11. Więzy integralności

Dodatek A

7

133 133 134 135 136

139

Integralność danych Definiowanie klucza obcego Dodawanie i usuwanie więzów

139 140 143

Instalacja i podstawowa konfiguracja baz

145

Baza MySQL Instalacja w systemie Linux Instalacja w systemie Windows Baza PostgreSQL Instalacja w systemie Linux Instalacja w systemie Windows Baza SQLite Praca z bazą Baza Microsoft SQL Server (MS SQL) Instalacja Tworzenie bazy testowej Praca z bazą Baza Oracle Instalacja w systemie Linux Instalacja w systemie Windows

145 145 150 154 154 158 161 162 162 163 165 166 167 167 171

8

SQL • Ćwiczenia praktyczne

Wstęp Dla kogo jest ta książka? Książka przeznaczona jest dla wszystkich osób, które chciałyby poznać podstawy stosowanego we współczesnych systemach baz danych języka SQL. Skorzystają z niej więc zarówno uczniowie, studenci i uczestnicy kursów komputerowych, jak i hobbyści pragnący poznać omawiany temat na własną rękę. Wszystkie zagadnienia prezentowane są w postaci prostych i gotowych do wykonania przykładów, dzięki czemu każdy czytelnik może od razu przećwiczyć prezentowaną wiedzę w praktyce. Niniejsza publikacja, mimo stosunkowo niewielkiej objętości, omawia bardzo wiele zagadnień, od samej koncepcji relacyjnych baz danych i tworzenia tabel po takie zagadnienia, jak transakcje, więzy integralności czy złożone podzapytania. Po jej przeczytaniu każdy czytelnik będzie w stanie sprawnie posługiwać się systemami baz danych opartymi na SQL.

Czym jest SQL? SQL to Strukturalny Język Zapytań (z ang. Stuctured Query Language) służący do wykonywania wszelkich operacji w systemach relacyjnych baz danych RDBMS (z ang. Relational Database Management System).

10

SQL • Ćwiczenia praktyczne

Jest to język uniwersalny, stosowany zarówno w systemach komercyjnych, jak: DB2, MS SQL czy Oracle, jak i rozwijanych na zasadach Open Source, jak PostgreSQL czy darmowe wersje MySQL. Początki SQL sięgają wczesnych lat 70. ubiegłego wieku, kiedy to w firmie IBM został rozpoczęty projekt bazodanowy o nazwie System/R, na którego potrzeby powstał język SEQUEL (z ang. Structured English Query Language). Druga wersja SEQUEL-a, SEQUEL/2, w roku 1997 została przemianowana na SQL. W roku 1986 Amerykański Narodowy Instytut Standaryzacji ANSI (American National Standards Institute) opublikował pierwszą standardową wersję SQL, a rok później uczyniła to Międzynarodowa Organizacja Standaryzacji ISO (International Standards Organization). Kolejne oficjalne standardy powstawały w latach: 1989 (tak zwany SQL1, SQL89), 1992 (tak zwany SQL2, SQL92, SQL-92), 1999 (tak zwany SQL3, SQL99, SQL:1999), 2003 (tak zwany SQL:2003), 2006 (SQL:2006) oraz 2008 (SQL:2008). SQL realizuje trzy różne typy zadań, w związku z tym występujące w nim instrukcje można podzielić na trzy grupy: T DDL — język definiowania danych (z ang. Data Definition Language), umożliwiający definicję struktury danych; T DML — język manipulacji danymi (z ang. Data Manipulation Language), umożliwiający pobieranie i modyfikowanie danych; T DCL — język kontroli danych (z ang. Data Control Language), umożliwiający kontrolę dostępu do danych.

Systemy baz danych Pomimo istnienia standardów SQL trudno spotkać system zarządzania bazą danych, który byłby ściśle zgodny z którymkolwiek z nich. Najczęściej producenci dodają własne rozszerzenia; bywa też, że niektóre elementy standardów nie są implementowane. Można jednak założyć, że wszelkie typowe operacje, nawet stosunkowo skomplikowane, będą wykonywane podobnie we wszystkich popularnych produktach. Założeniem niniejszej książki jest, że czytelnik ma już zainstalowany, poprawnie skonfigurowany i gotowy do działania system bazy danych. Może być to zarówno produkt komercyjny, jak i darmowy, ćwiczenia

Wstęp

11

zostały bowiem przygotowane w taki sposób, aby wykorzystywały standardowe cechy SQL dostępne w większości produktów bazodanowych (przykłady dotyczą baz: MySQL, PostgreSQL, Microsoft SQL Server, Oracle i SQLite1). Osoby, które dotychczas nie miały styczności z serwerami baz danych, powinny zajrzeć najpierw do dodatkowego rozdziału umieszczonego na końcu książki, w którym opisane zostały typowe czynności instalacyjne i konfiguracyjne wymienionych systemów baz danych.

1

W przypadku bazy SQLite część przykładów nie będzie działać, ze względu na to, że nie jest to pełna implementacja SQL.

12

SQL • Ćwiczenia praktyczne

1 Podstawy relacyjnych baz danych Tabele W systemach relacyjnych baz danych dane przechowywane są w strukturach nazywanych tabelami. Pomiędzy danymi w tych tabelach występują wiążące je relacje (stąd: systemy relacyjnych baz danych)1. Jak wygląda pojedyncza tabela z danymi? Każdy, kto widział kiedykolwiek dowolny arkusz kalkulacyjny, może ją sobie z łatwością wyobrazić. Składa się ona z wierszy i kolumn. Każdy wiersz opisuje jeden rekord, a kolumna — właściwości danego rekordu. Terminem rekord określamy pojedynczy element zbioru danych (wiersz tabeli). Załóżmy na przykład, że chcielibyśmy przechować w bazie dane dotyczące osób. Interesuje nas zapamiętanie imion, nazwisk oraz dat urodzenia. Należałoby zatem utworzyć tabelę zawierającą trzy kolumny. W pierwszej kolumnie byłyby zapisywane dane dotyczące imion, w drugiej — nazwisk, a w trzeciej — dat urodzenia osób. Przykładowy wygląd takiej tabeli został zobrazowany na rysunku 1.1. Tabela ta zawiera trzy wiersze (czyli trzy rekordy), zawierające dane dotyczące trzech różnych osób. 1

Ściśle rzecz ujmując, w teorii relacyjnych baz danych relacją jest sama tabela. W książce przyjęto jednak uproszczoną terminologię, dzięki czemu osoby początkujące będą mogły łatwiej przyswoić prezentowaną wiedzę.

14

SQL • Ćwiczenia praktyczne

Rysunek 1.1. Przykładowa tabela zawierająca dane osób

Klucze Czy jednak struktura przedstawiona na rysunku 1.1 faktycznie dobrze nadawałaby się do przechowywania danych dotyczących osób? Chwila zastanowienia pokaże, że niestety — nie. Otóż tabela ta nie pozwala na jednoznaczną identyfikację konkretnej osoby. Co się bowiem stanie, jeśli trzeba będzie zapisać dane dwóch Janów Kowalskich urodzonych pierwszego stycznia 1970 roku? Użycie dotychczasowej struktury (z rysunku 1.1) spowodowałoby powstanie dwóch wpisów o takich samych danych, których nie można byłoby rozróżnić. Taka sytuacja została przedstawiona na rysunku 1.2. Rysunek 1.2. Nieprawidłowa struktura tabeli uniemożliwia rozróżnienie niektórych rekordów

Tak oczywiście być nie powinno. Należy zatem do tabeli wprowadzić takie zmiany, aby każda osoba mogła być jednoznacznie zidentyfikowana. Najczęściej robi się to przez wprowadzenie dodatkowej kolumny zawierającej abstrakcyjne dane, inne dla każdego wiersza. Abstrakcyjne, czyli takie, które nie mają odzwierciedlenia w danych rzeczywistych zapisywanych w bazie. Ć W I C Z E N I E

1.1

Jednoznaczna identyfikacja rekordu

Popraw strukturę tabeli z rysunków 1.1 i 1.2 w taki sposób, aby każdy wiersz jednoznacznie identyfikował konkretną osobę. Aby wykonać ćwiczenie, do tabeli należy dodać kolumnę zawierającą identyfikator każdej osoby. Może on mieć dowolną postać, najczęściej jednak (co jest najwygodniejsze) stosuje się po prostu wartość całkowitą,

Rozdział 1. • Podstawy relacyjnych baz danych

15

tak jak zostało to zaprezentowane na rysunku 1.3. Jak widać, tym razem bez problemów możemy rozróżnić dwóch Janów Kowalskich, mimo że obaj urodzili się w tym samym dniu, każdy z nich ma bowiem przypisany inny identyfikator. Rysunek 1.3. Dodatkowa kolumna pozwala jednoznacznie zidentyfikować każdy rekord

Wprowadzona w ostatnim ćwiczeniu kolumna zawierająca wartość jednoznacznie identyfikującą każdy rekord nazywana jest kluczem podstawowym lub głównym (z ang. primary key). Ogólnie mówiąc, kluczem możemy nazwać dowolnie wybrany zestaw kolumn (czyli np. Imię, Nazwisko to również klucz), niemniej klucz podstawowy powinien zawsze jednoznacznie identyfikować każdy wiersz tabeli (wspomniane kolumny Imię, Nazwisko tego warunku nie spełniają, zatem nie mogą być kluczem głównym). O tym, która kolumna (lub kolumny) będzie kluczem, decyduje programista tworzący bazę.

Relacje Wiemy już, że dane w bazie przechowywane są w tabelach. Logika podpowiada, że takie tabele muszą być ze sobą w jakiś sposób powiązane, inaczej nie będzie z nich większego pożytku. Jeśli na przykład prowadzimy sklep internetowy, a w bazie znajdują się tabele zawierające dane o klientach i złożonych przez nich zamówieniach, musi istnieć takie powiązanie, które jednoznacznie przypisze zamówienie do klienta. Oznacza to, że pomiędzy danymi musi istnieć tzw. relacja (czyli pewien związek). W praktyce takie powiązanie zostanie zrealizowane przez wprowadzenie do jednej z tabel tak zwanego klucza obcego. Struktura takich tabel została zaprezentowana na rysunku 1.4, a widok po wypełnieniu danymi zobrazowano na rysunku 1.5. Przedstawione zostały dwie tabele: jedna opisuje klientów, a druga zamówienia. Obie mają klucze podstawowe: w pierwszej jest to kolumna KlientId, w drugiej — ZamówienieId. W tabeli opisującej zamówienia

16

SQL • Ćwiczenia praktyczne

Rysunek 1.4. Struktura tabel Klienci i Zamówienia Rysunek 1.5. Tabele Klienci i Zamówienia wypełnione przykładowymi danymi

znajduje się również klucz obcy KlientId, czyli klucz podstawowy z tabeli opisującej klientów. Dzięki takiej strukturze wiemy, że zamówienie o identyfikatorze 1 zostało złożone przez Jana Kowalskiego 25 stycznia 2012 roku, a np. zamówienie o numerze 3 zostało złożone 22 kwietnia 2012 roku przez Annę Jabłońską2. A zatem powtórzmy raz jeszcze: KlientId jest kluczem podstawowym w tabeli opisującej klientów, ale kluczem obcym w tabeli opisującej zamówienia. W ten sposób pomiędzy tymi dwiema tabelami powstała relacja. W teorii projektowania relacyjnych baz danych możemy wyróżnić trzy podstawowe typy relacji: T jeden do jednego, T jeden do wielu, T wiele do wielu. W przypadku relacji jeden do jednego jednemu rekordowi (wierszowi) z tabeli X odpowiada dokładnie jeden rekord z tabeli Y. Z taką sytuacją moglibyśmy mieć do czynienia na przykład wtedy, gdyby w jednej tabeli zapisać imiona i nazwiska osób, a w innej numery PESEL. Struktura takich tabel została przedstawiona na rysunku 1.6, a przykładowe dane na rysunku 1.7. Jak wiadomo, każdy polski obywatel ma przypisany unikalny numer PESEL, a zatem będzie tu występowała relacja

2

Oczywiście w praktyce należałoby przechowywać również takie informacje jak liczba sztuk zamówionego towaru oraz wartość zamówienia. To jednak tylko ilustracja przykładowego powiązania między tabelami.

Rozdział 1. • Podstawy relacyjnych baz danych

17

Rysunek 1.6. Struktura tabel powiązanych relacją jeden do jednego

Rysunek 1.7. Tabele Osoba i Pesel wypełnione przykładowymi danymi

jeden do jednego. Oczywiście od razu należałoby przyjąć założenie, że nie będziemy przechowywać danych obywateli innych państw, którzy PESEL-i nie mają. W przypadku relacji jeden do wielu jednemu rekordowi (wierszowi) z tabeli X może odpowiadać jeden lub więcej rekordów z tabeli Y, ale — uwaga! — jednemu wierszowi z tabeli Y odpowiada dokładnie jeden wiersz z tabeli X. Z taką sytuacją już mieliśmy do czynienia. Spójrzmy ponownie na rysunki 1.4 i 1.5. Znajdują się na nich dwie tabele, z których pierwsza opisuje klientów, a druga — złożone przez nich zamówienia. Jeden klient może złożyć wiele zamówień, ale jedno zamówienie może należeć tylko do jednego klienta. A zatem jest to relacja jeden do wielu (lub też odwrotnie: wiele do jednego). Trzeci przypadek jest bardziej złożony. Otóż relacja wiele do wielu ma miejsce, kiedy jednemu wierszowi z tabeli X może odpowiadać wiele wierszy z tabeli Y oraz jednemu wierszowi z tabeli Y może odpowiadać wiele wierszy z tabeli X. Kiedy możemy mieć do czynienia z taką sytuacją? Na przykład wtedy, kiedy chcemy zapisać w bazie dane dotyczące książek i autorów. Wiadomo bowiem, że każda książka może mieć jednego lub więcej autorów, ale także każdy autor mógł napisać jedną lub wiele książek. Jest to więc typowa relacja wiele do wielu. Pytanie, jak taką sytuację odzwierciedlić w praktyce w bazie danych? Ć W I C Z E N I E

1.2

Relacja wiele do wielu

Zaprojektuj tabele bazy danych, które mogłyby przechowywać dane o książkach i ich autorach.

18

SQL • Ćwiczenia praktyczne

W przypadku wystąpienia relacji wiele do wielu należy stosować dodatkową tabelę pomocniczą, łączącą tabele, między którymi relacja występuje. Będziemy więc mieć do czynienia z trzema tabelami: T Autorzy — przechowującą dane o autorach, T Książki — przechowującą dane o książkach, T KsiążkiAutorzy — wiążącą dane z tabel Autorzy i Książki. Struktura tych tabel będzie taka jak przedstawiona na rysunku 1.8. Rysunek 1.8. Rozbicie relacji wiele do wielu na trzy tabele

Tabela Autorzy ma trzy kolumny: AutorId (przechowująca unikalny identyfikator autora i będąca kluczem głównym), Imię (przechowująca imię autora), Nazwisko (przechowująca nazwisko autora), natomiast tabela Książki ma tylko dwa pola: KsiążkaId (które będzie przechowywać unikalny identyfikator książki) oraz Tytuł. Za połączenie autorów i książek odpowiada dodatkowa tabela o nazwie KsiążkiAutorzy. Ma ona dwa pola: w pierwszym polu, KsiążkaId, zapisywane są identyfikatory książek z tabeli Książki (jest to więc klucz obcy), a w drugim polu, AutorId, zapisywane są identyfikatory autorów z tabeli Autorzy (a zatem to również jest klucz obcy). Dzięki takiemu układowi możemy bez problemu odzwierciedlić sytuacje, kiedy jeden autor napisał wiele książek i kiedy jedna książka ma więcej niż jednego autora. Zwróćmy przy tym uwagę, że w tabeli KsiążkiAutorzy obie kolumny zawierają klucze obce, ale jednocześnie jednoznacznie identyfikują one każdy wiersz tej tabeli (przy założeniu, że wprowadzane dane są spójne i prawidłowe). A zatem obie te kolumny, mimo że każda z nich jest kluczem obcym, będą stanowić klucz podstawowy tabeli KsiążkiAutorzy (o ile dane w tabeli nie zawierają błędów, para KsiążkaId, AutorId jednoznacznie wyznacza każdy wiersz tabeli KsiążkiAutorzy).

Rozdział 1. • Podstawy relacyjnych baz danych

19

Ć W I C Z E N I E

1.3

Relacja wiele do wielu w praktyce

Wypełnij tabele z ćwiczenia 1.2 przykładowymi danymi w taki sposób, aby odzwierciedlały sytuację, kiedy dana książka ma jednego autora, dwóch autorów oraz kiedy jeden autor jest autorem dwóch książek. Przykładowe rozwiązanie ćwiczenia zostało przedstawione na rysunku 1.9. Widać wyraźnie, że w bazie zapisano dane dwójki autorów: Orsona Scotta Carda3 i Kathryn Kidd4 (tabela Autorzy), oraz trzech książek: Gra Endera, Lovelock i Paradise Vue (tabela Książki). Tabela KsiążkiAutorzy zawiera informacje pozwalające dopasować książki do autorów i autorów do książek. Z zawartych w niej danych wynika, że: T Książka Gra Endera o identyfikatorze 1 ma jednego autora, o identyfikatorze 1, a zatem jest nim Orson Scott Card. T Książka Lovelock o identyfikatorze 2 ma dwóch autorów, o identyfikatorach 1 i 2, a zatem są nimi Orson Scott Card oraz Kathryn Kidd. T Książka Paradise Vue o identyfikatorze 3 ma jednego autora, o identyfikatorze 2, a zatem jest nim Kathryn Kidd.

Rysunek 1.9. Tabele z ćwiczenia 1.3 wypełnione przykładowymi danymi

3

Warte rozważenia jest, jak postępować w sytuacji, kiedy autor posługuje się dwoma imionami. Czy zapisywać je oba, w jednej kolumnie, tak jak w przedstawionym przykładzie (zmieniając ewentualnie nazwę kolumny na Imiona), czy też zastosować inne rozwiązanie?

4

Dokładniej: Kathryn Helms Kidd. W jednym z polskich wydań na okładce błędnie podano nazwisko Kathryn Kerr.

20

SQL • Ćwiczenia praktyczne

Podstawowe zasady projektowania tabel Przed przystąpieniem do tworzenia tabel zawsze musimy się zastanowić, jakiemu celowi będą służyły zebrane w bazie dane, jakie informacje będą faktycznie potrzebne oraz jakie zachodzą między nimi powiązania i relacje. Nie będziemy, co prawda, przedstawiać tutaj metodyk modelowania i projektowania baz danych — tym zagadnieniom poświęcone są oddzielne publikacje — niemniej przydatnych będzie kilka ogólnych zasad i informacji.

Określenie celu Strukturę bazy zawsze musimy dobrać do konkretnego projektu. Te same dane można zapisywać w różny sposób, w zależności od tego, do czego mają służyć. Jeśli np. przygotowujemy bazę adresową dla celów administracyjno-meldunkowych, z pewnością utworzymy oddzielne tabele dla osób i dla adresów. Powstanie między nimi relacja jeden do wielu. Pod jednym adresem może być bowiem zameldowanych wiele osób, ale jedna osoba może być (przynajmniej teoretycznie) zameldowana tylko pod jednym adresem. Jeśli będziemy chcieli przedstawić stosunki własnościowe, powstaną zapewne tabele Mieszkania i Osoby, a między nimi relacja wiele do wielu, bowiem jedna osoba może być właścicielem jednego lub większej liczby mieszkań, a jedno mieszkanie może być własnością jednej lub wielu osób. Odmienna sytuacja będzie jednak miała miejsce, gdybyśmy chcieli tworzyć bazę pozwalającą na okresową wysyłkę np. materiałów reklamowych. W takim przypadku dane dotyczące osób i ich adresów zamieszkania można by zapisać w jednej tabeli, każdej osobie będzie bowiem przypisany zapewne tylko jeden adres wysyłkowy. Czy w takiej sytuacji tworzyć jedną tabelę, czy dwie — decyzja należy do projektanta bazy, niemniej oba rozwiązania będą miały rację bytu.

Rozdział 1. • Podstawy relacyjnych baz danych

21

Duplikowanie danych Należy bezwzględnie unikać sytuacji, w której w kolejnych wierszach tabeli znajdują się duplikaty tych samych danych. Jeśli coś takiego się zdarzy, oznacza to zazwyczaj, że baza została źle zaprojektowana. Załóżmy na przykład, że prowadzimy księgarnię i w bazie utworzyliśmy tabelę z danymi dotyczącymi książek i wydawnictw o strukturze przedstawionej na rysunku 1.10. Po wypełnieniu danymi jej fragment mógłby wyglądać tak jak na rysunku 1.11. Jak widać, w tabeli znalazły się trzy różne książki wydane przez to samo wydawnictwo, a zatem nazwa i adres wydawnictwa pojawiają się w tabeli trzy razy. Rysunek 1.10. Nieprawidłowa struktura tabeli Książki

Rysunek 1.11. Tabela Książki po wypełnieniu przykładowymi danymi

Choć na pierwszy rzut oka takie rozwiązanie może wydawać się bardzo czytelne, w praktyce będzie sprawiało wyłącznie problemy. Po pierwsze, marnujemy ogromną ilość miejsca w bazie, gdyż dopisanie każdej nowej książki wymaga powtórzenia nazwy i adresu wydawnictwa. Tym samym, jeśli mamy w bazie dane dotyczące 1000 książek wydawnictwa Helion, to mamy również 999 zupełnie niepotrzebnych powtórzonych wpisów dotyczących nazwy i adresu. Po drugie, jeśli przy wprowadzaniu nowych książek do bazy pomylimy się choćby o jeden znak w nazwie lub adresie wydawnictwa, a przy dużej liczbie wpisów o taką pomyłkę nietrudno, dane w bazie będą niespójne. Po trzecie, jeśli wydawnictwo zmieni nazwę lub adres, trzeba będzie wprowadzić zmiany w 1000 wierszy tabeli, a to kolejne miejsce, gdzie łatwo o pomyłkę. Można by tu wymienić jeszcze kilka problemów, ale widać już chyba wyraźnie, że duplikowanie danych w tabelach jest niezbyt dobrym pomyłem. Jeśli zatem podczas projektowania zdarzy nam się utworzyć taką tabelę, to trzeba ją rozbić na dwie — a w razie potrzeby na więcej — powiązane ze sobą tabele.

22

SQL • Ćwiczenia praktyczne

Ć W I C Z E N I E

1.4

Tabele bez danych nadmiarowych

Zmodyfikuj przedstawioną na rysunkach 1.10 i 1.11 strukturę tak, aby nie występował problem nadmiarowych danych. Rozwiązanie ćwiczenia zostało przedstawione na rysunkach 1.12 i 1.13. Tabela Książki z rysunku 1.10 została rozbita na dwie: Książki, zawierającą pola: KsiążkaId (jako klucz podstawowy), Tytuł, WydawnictwoId (jako klucz obcy), oraz Wydawnictwa, zawierającą pola: WydawnictwoId (jako klucz podstawowy), Nazwa wydawnictwa i Adres wydawnictwa. Po takiej modyfikacji dane z rysunku 1.11 przyjęłyby postać widoczną na rysunku 1.13. Rysunek 1.12. Prawidłowa struktura tabel Książki i Wydawnictwa

Rysunek 1.13. Tabele Książki i Wydawnictwa wypełnione danymi

Informacje atomowe O ile to tylko możliwe, w każdym polu bazy danych należałoby zapisywać pojedyncze, atomowe informacje. Co jest informacją atomową, zależy od konkretnego przypadku i naszego zdrowego rozsądku. Spójrzmy jednak na poprzedni przykład i struktury widoczne na rysunkach 1.10 i 1.12. Cały adres wydawnictwa był wtedy zapisywany w jednym wierszu. Czy jest to dobre rozwiązanie? Raczej nie, chociaż w pewnych sytuacjach dopuszczalne. Dlaczego nie najlepsze? Dlatego, że jeśli będziemy chcieli znaleźć np. wydawnictwa z danego miasta

Rozdział 1. • Podstawy relacyjnych baz danych

23

czy wręcz znajdujące się w danym mieście przy jednej ulicy, będziemy mieli kłopot5. Adres najlepiej więc rozbić na poszczególne pola typu: miasto, ulica, numer domu, kod, co dodatkowo ułatwi wprowadzanie ewentualnych modyfikacji. Z drugiej strony, jeśli baza wydawnictw jest niewielka, zapisanie adresu w jednej kolumnie może być w zupełności wystarczającym rozwiązaniem. Bardzo dużym błędem jest również umieszczenie w jednym polu wielu odwołań do innej tabeli. Rozważmy przykład, który pozwalałby na przechowywanie w bazie informacji o wypożyczeniach książek z biblioteki. Dane takie mogłyby znaleźć się w tabeli Wypożyczenia o strukturze przedstawionej na rysunku 1.14. Zawiera ona pole KlientId, które jest kluczem obcym pochodzącym z tabeli identyfikującej klientów, pole Data, w którym mają być zapisywane daty wypożyczeń, i pole KsiążkaId, które jest kluczem obcym pochodzącym z tabeli zawierającej dane książek. Rysunek 1.14. Struktura tabeli Wypożyczenia

Załóżmy teraz, że fakt wypożyczenia kilku książek przez jednego klienta odnotowaliśmy w bazie w sposób widoczny na rysunku 1.15. To oczywiście jest błąd. Co prawda w tabeli znajdą się wtedy wszelkie niezbędne informacje i przykładowo będziemy w stanie ustalić, że klient o identyfikatorze 1 wypożyczył 5 maja 2012 roku trzy książki, o identyfikatorach 1, 18 i 24, ale już bardzo trudno będzie nam uzyskać wszelkie informacje statystyczne. Jak bowiem sprawdzić, ile razy w danym okresie była wypożyczana dana książka albo jakiego typu książki najchętniej wypożycza klient o identyfikatorze 4? Rysunek 1.15. Przykładowe dane w tabeli Wypożyczenia

5

Choć oczywiście odpowiednio skonstruowane zapytanie (czasami wspomagane przetwarzaniem danych po stronie klienta) poradzi sobie z takim problemem.

24

SQL • Ćwiczenia praktyczne

Wydobycie takich informacji co prawda będzie możliwe, ale będzie też wymagało analizy zawartości kolumny KsiążkaId, co spowoduje sporo dodatkowej pracy programisty oraz zwiększenie obciążenia systemu bazy danych (a tym samym zmniejszenie wydajności). Tymczasem prawidłowe zapisanie danych w tabeli Wypożyczenia spowoduje, że uzyskiwanie wszelkich informacji dotyczących wypożyczeń będzie możliwe przy użyciu typowych zapytań (zostanie to opisane w kolejnych rozdziałach). „Prawidłowe” w tym wypadku oznacza, że dane dotyczące wypożyczenia każdej książki należy zapisywać w osobnych wierszach tabeli, a więc każde wypożyczenie ma być zapisane w pojedynczym wierszu. Ć W I C Z E N I E

1.5

Zapis informacji atomowych

Popraw dane widoczne na rysunku 1.15 w taki sposób, aby prawidłowo były zapisywane informacje dotyczące wypożyczeń. Poprawna postać tabeli Wypożyczenia została przedstawiona na rysunku 1.16. Zgodnie z opisanymi wyżej zasadami każda informacja dotycząca wypożyczenia pojedynczej książki znajduje się w osobnym wierszu, dzięki czemu uzyskiwanie różnych informacji z tej bazy będzie możliwie proste. Rysunek 1.16. Prawidłowa struktura tabeli Wypożyczenia

Puste pola Należy unikać pozostawiania w tabelach pustych pól, czyli takich, które nie zawierają danych. W wielu sytuacjach ich istnienie jest co prawda konieczne, gdyby jednak w jakiejś tabeli miało się ich pojawić bardzo dużo, należy raczej zastanowić się nad modyfikacją struktury bazy. Jeśli przygotowujemy np. bazę meldunkową, która ma zawierać informacje o tym, gdzie dana osoba jest zameldowana, możemy przygotować dwie tabele: jedną zawierającą dane osób i drugą zawierającą

Rozdział 1. • Podstawy relacyjnych baz danych

25

adresy. W tabeli opisującej osoby wprowadzamy dodatkowe pole, np. o nazwie AdresZameldowaniaId, które będzie kluczem obcym wskazującym na wybrany rekord tabeli Adresy, tak jak zostało to zaprezentowane na rysunku 1.17. Rysunek 1.17. Struktura tabel przechowujących informacje meldunkowe

Musimy jednak pamiętać, że pole AdresZameldowaniaId w tabeli Osoby w niektórych wypadkach będzie puste, nie każdy bowiem ma adres zameldowania (podobnie jak pole AdresZamieszkaniaId w tabeli Osoby czy też Nr lokalu w tabeli Adresy). Ponieważ jednak większość osób taki adres ma, jest to dobre rozwiązanie. Łatwo też będzie znaleźć osoby, które nie są nigdzie zameldowane. Z odmienną sytuacją będziemy mieli do czynienia, gdy pustych pól w danej kolumnie tabeli pojawi się bardzo dużo. Zdecydowanie należy wtedy rozważyć usunięcie takiej kolumny i przeniesienie jej do dodatkowej tabeli. Przykładowo, jeśli mamy tabelę zawierającą dane o zamówieniach, taką jak przedstawiona na rysunku 1.18, i wiemy, że uwagi będą się pojawiały bardzo rzadko, kolumnę Uwagi możemy usunąć z tabeli Zamówienia, a zamiast tego utworzyć specjalną tabelę dla samych uwag.

Rysunek 1.18. Tabela zawierająca kolumnę o wielu pustych polach Ć W I C Z E N I E

1.6

Przeniesienie kolumny do nowej tabeli

Zmodyfikuj przykład z rysunków 1.17 i 1.18 tak, aby uwagi były zapisywane w oddzielnej tabeli.

26

SQL • Ćwiczenia praktyczne

Poprawiona struktura tabel została przedstawiona na rysunku 1.19. Kolumna Uwagi została przeniesiona do tabeli Uwagi zawierającej dwie kolumny: jedną wskazującą na numer zamówienia i drugą zawierającą uwagi do tego zamówienia. Po takiej modyfikacji każde zamówienie będzie mogło mieć przypisane mu uwagi, a jednocześnie w tabeli Zamówienia nie będzie już w większości pustej kolumny. Rysunek 1.19. Wprowadzenie dodatkowej tabeli zamiast rzadko używanej kolumny

Jednoznaczna identyfikacja rekordów Należy pamiętać o tym, aby każdy wiersz był jednoznacznie identyfikowany, w przeciwnym razie będą powstawały rekordy, których nie będziemy w stanie odróżnić. A zatem zawsze musi być dobrze określony klucz podstawowy (główny). Ten problem został opisany w sekcji „Klucze”. W tym celu najczęściej wprowadzamy do tabeli dodatkowy identyfikator, taki jak wykorzystywane wcześniej: KlientId, OsobaId, TowarId, Id itp. Czasami zamiast identyfikatora dodatkowego można wykorzystać identyfikator istniejący w rzeczywistości, np. dla książek czasami stosuje się numer ISBN. W takim wypadku należy jednak bardzo uważać. Sztuczny identyfikator wprowadzony przez nas do bazy gwarantuje unikalność każdego rekordu. Identyfikator rzeczywisty — nie zawsze. Wspomniany numer ISBN faktycznie jest unikalny dla każdej publikacji książkowej, ale jeśli np. jedna książka została wydana w dwóch tomach, to mogą one mieć jeden numer ISBN6. Jeśli zatem w bazie chcemy zapisać oddzielne rekordy dla każdego z tomów, numer ISBN nie będzie jednoznacznym identyfikatorem, a tym samym nie będzie dobrym kluczem podstawowym. Zawsze więc trzeba dobrze przemyśleć budowę bazy, tak aby zagwarantować faktyczną jednoznaczną identyfikację rekordów. 6

Mogą też istnieć książki, które nie mają numeru ISBN.

2 Praca z tabelami W poprzednim rozdziale zostały omówione podstawowe zasady tworzenia relacyjnych baz danych. Wiadomo już, że podstawą są tabele. Czas więc dowiedzieć się, w jaki sposób można je tworzyć, modyfikować i usuwać z bazy. W tym i dalszych rozdziałach założeniem jest, że czytelnik dysponuje zainstalowanym, skonfigurowanym i gotowym do pracy serwerem baz danych, w którym założona jest baza testowa, a więc system jest gotowy do wydawania poleceń SQL. Osoby, które takiego systemu nie mają, powinny najpierw zapoznać się ze znajdującym się na końcu książki dodatkiem opisującym podstawowe czynności przygotowujące do działania popularne systemy bazodanowe. Ćwiczenia w tym i następnych rozdziałach występują w wersjach dla dialektów SQL stosowanych w następujących bazach: MySQL, PostgreSQL, SQLite, Microsoft SQL Server (opisywana dalej jako MS SQL) i Oracle.

Typy danych Przed przystąpieniem do tworzenia tabel trzeba poznać występujące w SQL podstawowe typy (czyli rodzaje) danych. Możemy je podzielić następująco1:

1

Istnieje także kilka innych rodzajów typów danych wprowadzanych w kolejnych standardach SQL, którymi jednak nie będziemy zajmować się w niniejszej publikacji.

28

SQL • Ćwiczenia praktyczne

T typy liczbowe, T typy daty i czasu, T typy łańcuchowe.

Koniecznie trzeba jednak wspomnieć, że standard SQL definiuje typy danych bardzo ogólnie, a ich konkretna postać zależy od implementacji.

Typy liczbowe Typy liczbowe można podzielić na całkowitoliczbowe (z ang. integer types) oraz zmiennoprzecinkowe (rzeczywiste, zmiennopozycyjne, z ang. real types, floating point types). Podstawowym typem całkowitoliczbowym, czyli służącym do reprezentowania liczb całkowitych, jest INTEGER. Zakres wartości, który może być zapisany za pomocą tego typu, jest zależny od implementacji. Z reguły jest to 32-bitowa liczba ze znakiem (co odpowiada wartościom od –231 do 231–1). Odmianami typu INTEGER są SMALLINT i BIGINT. Pierwsza z nich jest określana jako typ całkowity mniejszy od INTEGER (z reguły liczba 16-bitowa), druga — jako typ całkowity większy od INTEGER (z reguły liczba 64-bitowa). Typy zmiennoprzecinkowe służą do reprezentowania wartości rzeczywistych. Wyróżniamy tu typy: REAL, DOUBLE PRECISION oraz FLOAT. Zakres wartości zależny jest od systemu bazodanowego oraz platformy sprzętowo-systemowej. Standard zakłada jedynie, że typ DOUBLE PRECISION ma umożliwiać przedstawienie większego zakresu wartości niż REAL. W przypadku typu FLOAT możliwe jest natomiast podanie precyzji2, np. FLOAT(5). Oprócz wymienionych wyżej istnieją także typy NUMERIC oraz DECIMAL, które pozwalają na reprezentowanie liczb zawierających część dziesiętną (wartość po separatorze dziesiętnym), czyli np. 11,4, 152,581 itp. Oba typy pozwalają na określenie precyzji i (opcjonalnie) skali; przy czym jako precyzję rozumiemy całkowitą liczbę cyfr, a jako skalę — liczbę cyfr po separatorze dziesiętnym. Przykładowo typ zdefiniowany jako NUMERIC(5) będzie mógł reprezentować wartości o maksymalnej

2

Wiele implementacji SQL pozwala również na podawanie precyzji dla typów REAL i DOUBLE PRECISION.

Rozdział 2. • Praca z tabelami

29

liczbie cyfr równej 5, np. 111,11, 22,222, 2,2222, a typ NUMERIC(5,2) — wartości o całkowitej liczbie cyfr równej 5 i części dziesiętnej o maksymalnej liczbie cyfr równej 2, czyli 333,33, 5,55, 66,6, 77,77 itp. Praktycznie we wszystkich systemach bazodanowych jako separatora dziesiętnego należy używać znaku kropki, a nie przecinka, który obowiązuje w języku polskim!

Typy daty i czasu Typy pozwalające na opisanie daty oraz czasu to3: T DATE, T TIME, T TIMESTAMP. Typ DATE opisuje datę, TIME — czas, a TIMESTAMP — datę i czas. W niektórych implementacjach jako typ opisujący jednocześnie datę i czas stosuje się DATETIME. W MS SQL dostępne są typy: DATETIME, SMALLDATETIME i TIMESTAMP.

Typy łańcuchowe Typy służące do przechowywania łańcuchów znakowych, czyli tekstów, to CHARACTER i CHARACTER VARYING4. Częściej jednak wykorzystuje się wersje skrócone nazw: CHAR dla typu CHARACTER oraz VARCHAR dla typu CHARACTER VARYING. Oba wymagają podania długości łańcucha za nazwą typu, w nawiasie okrągłym, czyli: CHAR(długość)

i VARCHAR(długość),

3

Zgodnie ze standardem SQL:2003 typy daty i czasu to: DATE, TIME WITHOUT TIME ZONE, TIMESTAMP WITHOUT TIME ZONE, TIME WITH TIME ZONE i TIMESTAMP WITH TIME ZONE, jednak zazwyczaj stosuje się wymienione wersje skrócone.

4

W SQL:1999 został wprowadzony również dodatkowy typ CLOB (z ang. Character Large Object), służący do przechowywania bardzo długich łańcuchów znakowych.

30

SQL • Ćwiczenia praktyczne

gdzie długość oznacza liczbę znaków. Przykładowo, jeśli chcemy utworzyć kolumnę, która będzie mogła przechowywać do 20 znaków, należy zastosować konstrukcję: CHAR(20)

lub VARCHAR(20)

W przypadku typu CHAR cała kolumna w bazie danych będzie miała długość wskazaną parametrem długość. Jeśli zapisywane dane będą miały mniej znaków, pozostałe miejsca zostaną uzupełnione spacjami z prawej strony. Najlepiej więc stosować go w przypadku kolumn tabeli, w których liczba znaków jest z góry ustalona. Natomiast w przypadku typu VARCHAR każdy wiersz kolumny ma zmienną długość, wynikającą z liczby znaków zapisywanego łańcucha (jeśli zapisywany łańcuch jest krótszy niż wartość podawana w specyfikacji typu, wolne miejsca nie są wypełniane spacjami).

Typ null Typ null jest typem specjalnym. Wartość typu null (zapisywana jako ciąg znaków null5) jest wartością pustą, czyli oznacza po prostu brak wartości. Jak to rozumieć? Przykładowo, jeśli mamy kolumnę przechowującą wartości całkowite, może się zdarzyć, że dla części wierszy właściwa wartość będzie nieznana, wtedy takie wiersze będą zawierały właśnie wartość specjalną null. Inny przykład. Przypomnijmy sobie tabelę przedstawioną na rysunku 1.3 w pierwszym rozdziale. Zawierała ona identyfikatory, imiona, nazwiska oraz daty urodzenia osób. Mogłoby się jednak zdarzyć, że nie znamy czyjejś daty urodzenia, np. osoby o identyfikatorze 3. W takim wypadku w wierszu opisującym dane tej osoby zamiast daty pojawi się właśnie wartość null, tak jak zostało to przedstawione na rysunku 2.1.

5

Nie oznacza to jednak, że w tabeli bazy danych faktycznie jest zapisywany ciąg znaków null. To, w jaki sposób wartość ta jest reprezentowana wewnątrz bazy, jest uzależnione od danej implementacji.

Rozdział 2. • Praca z tabelami

31

Rysunek 2.1. Wartość null w tabeli opisującej dane osób

Tworzenie i wybór bazy danych Jeden serwer bazy danych może zarządzać wieloma bazami. W trakcie instalacji może być tworzona baza domyślna, częściej jednak bazę (bazy) należy utworzyć samodzielnie. Służy do tego instrukcja CREATE DATABASE w postaci: CREATE DATABASE [IF NOT EXISTS] nazwa_bazy;

Jeżeli baza o podanej nazwie będzie istniała i nie zostanie użyta opcjonalna6 klauzula IF NOT EXISTS, zostanie zgłoszony błąd. Aby utworzyć przykładową bazę o nazwie test, należy użyć polecenia7: CREATE DATABASE test;

Współczesne systemy bazodanowe oferują z reguły dużą dowolność w nazewnictwie baz, a także opisanych dalej tabel oraz kolumn, jednak każdy z nich wprowadza pewne ograniczenia, dlatego też w celu zapewnienia maksymalnej przenośności kodu zostanie przyjęte założenie, że nazwy te będą pisane małymi literami alfabetu łacińskiego oraz nie będą zawierały znaków spacji. Warto jednak odnotować, że jeśli nazwa (bazy, tabeli, kolumny) ma zawierać znaki niestandardowe (spoza alfabetu łacińskiego) lub znaki spacji (o ile dany system dopuszcza taką możliwość; można to sprawdzić w dokumentacji technicznej danego serwera), to powinna być ujęta w znaki lewego apostrofu: `, np.: `moja baza`

6

Opcjonalność danej konstrukcji języka zaznacza się przez umieszczenie jej w nawiasie kwadratowym.

7

Instrukcja CREATE DATABASE nie jest częścią standardu SQL, jednak jest zaimplementowana w większości dialektów SQL.

32

SQL • Ćwiczenia praktyczne

Część systemów baz danych udostępnia też zewnętrzne narzędzia pozwalające na tworzenie i wybór bazy. Sposoby postępowania w konkretnych przypadkach (jak założyć bazę, utworzyć użytkownika, dokonać połączenia) zostały opisane w dodatku znajdującym się na końcu książki.

Tworzenie tabel Do tworzenia tabel służy instrukcja CREATE TABLE o schematycznej postaci: CREATE TABLE nazwa_tabeli ( nazwa_kolumny_1 typ_kolumny_1 [atrybuty], nazwa_kolumny_2 typ_kolumny_2 [atrybuty], ... nazwa_kolumny_n typ_kolumny_n [atrybuty], );

Taka instrukcja utworzy tabelę w bieżącej bazie. Nazwa bazy może być jednak podana jawnie. Należy wtedy użyć konstrukcji nazwa_bazy.nazwa_ ´tabeli. Gdyby nazwy miały zawierać znaki niestandardowe, obie powinny być ujęte w znaki lewego apostrofu: CREATE TABLE `nazwa_bazy`.`nazwa_tabeli` ( tutaj definicja tabeli );

Jednak zgodnie z tym, co zostało napisane wcześniej, w celu ujednolicenia zapisu i uzyskania zgodności z możliwie dużą liczbą systemów baz danych w dalszej części książki we wszelkich nazwach będą stosowane tylko litery alfabetu łacińskiego i, ewentualnie, znak podkreślenia zastępujący spacje. Wykorzystując przedstawioną dotychczas wiedzę, utwórzmy zatem kilka prostych tabel. Ć W I C Z E N I E

2.1

Utworzenie prostej tabeli

Utwórz tabelę o nazwie klient zawierającą dwie kolumny: pierwszą o nazwie id, typu INTEGER, drugą o nazwie nazwa, typu VARCHAR(20).

Rozdział 2. • Praca z tabelami

33

Utworzenie opisanej w ćwiczeniu tabeli nastąpi po wydaniu polecenia w postaci: CREATE TABLE klient ( id INTEGER, nazwa VARCHAR(20) ); Ć W I C Z E N I E

2.2

Tabela z kolumnami typu INTEGER

Utwórz tabelę, która będzie zawierała dwie kolumny typu INTEGER: pierwszą o nazwie id i drugą o nazwie znacznik. CREATE TABLE test ( id INTEGER, znacznik INTEGER ); Ć W I C Z E N I E

2.3

Tabela z kolumną przechowującą wartości rzeczywiste

Utwórz tabelę zawierającą kolumnę przechowującą wartości rzeczywiste podwójnej precyzji. CREATE TABLE test ( id INTEGER, wartosc DOUBLE PRECISION );

Tabela będzie zawierała dwie kolumny: pierwszą o nazwie id, typu INTEGER, i drugą o nazwie wartosc, typu DOUBLE PRECISION. Ć W I C Z E N I E

2.4

Kolumna z wartościami rzeczywistymi o określonej precyzji

Utwórz tabelę zawierającą kolumnę przechowującą wartości z separatorem dziesiętnym, z szerokością wyświetlania określoną na 6 cyfr znaczących, z trzema miejscami po przecinku.

34

SQL • Ćwiczenia praktyczne CREATE TABLE test ( wartosc DECIMAL(6, 3) );

Ć W I C Z E N I E

2.5

Kolumna przechowująca dane o dacie i czasie

Utwórz tabelę zawierającą kolumnę przechowującą wartości określające jednocześnie datę i czas. CREATE TABLE test ( dataiczas TIMESTAMP ); Ć W I C Z E N I E

2.6

Tabela z kolumnami przechowującymi krótkie dane tekstowe

Utwórz tabelę, która będzie zawierała następujące kolumny: id typu INTEGER, imie typu VARCHAR i nazwisko typu VARCHAR. CREATE TABLE test ( id INTEGER, imie VARCHAR(20), nazwisko VARCHAR(30) );

W takiej tabeli w kolumnie imie będzie można przechowywać ciągi o długości do 20 znaków, a w kolumnie nazwisko — ciągi nie dłuższe niż 30 znaków.

Atrybuty kolumn Każda kolumna może mieć dodatkowe atrybuty. Najczęściej spotykane, które często przydają się przy pracy z bazą danych, to: PRIMARY KEY, NOT NULL, DEFAULT, UNIQUE.

Rozdział 2. • Praca z tabelami

35

Atrybut PRIMARY KEY oznacza, że dana kolumna będzie kluczem podstawowym (głównym). Kolumny z atrybutem PRIMARY KEY są automatycznie indeksowane8 (patrz sekcja „Indeksy”) oraz unikatowe (tzn. każdy wiersz takiej kolumny musi mieć inną wartość). Jeśli kolumna ma być kluczem podstawowym, za jej definicją należy umieścić słowa PRIMARY KEY według schematu: CREATE TABLE nazwa_tabeli ( nazwa_kolumny typ_kolumny PRIMARY KEY, definicje pozostałych kolumn ); Ć W I C Z E N I E

2.7

Utworzenie klucza podstawowego

Utwórz tabelę zawierająca dwie kolumny: id typu INTEGER oraz nazwa typu VARCHAR. Kolumna id ma być kluczem podstawowym. CREATE TABLE test ( id INTEGER PRIMARY KEY, nazwa VARCHAR(20) );

W przypadku gdyby klucz podstawowy miał się składać z więcej niż jednej kolumny, jego definicja będzie wyglądała inaczej. Schemat tego typu konstrukcji ma postać: CREATE TABLE nazwa_tabeli ( nazwa_kolumny_1 typ_kolumny_1 [atrybuty], nazwa_kolumny_2 typ_kolumny_2 [atrybuty], ... nazwa_kolumny_n typ_kolumny_n [atrybuty], PRIMARY KEY (kolumna_x, kolumna_y, ..., kolumna_z) );

A zatem nazwy kolumn powinny być umieszczone w nawiasie okrągłym za słowami PRIMARY KEY, występującymi po definicji wszystkich kolumn składowych tabeli. 8

To oznacza, że dla tej kolumny indeks zostanie utworzony automatycznie. W niektórych systemach baz danych zostanie też wyświetlony komunikat informujący o tym fakcie.

36

SQL • Ćwiczenia praktyczne

Ć W I C Z E N I E

2.8

Klucz podstawowy złożony z dwóch kolumn

Utwórz tabelę, w której kolumny id typu INTEGER oraz nazwa typu VARCHAR tworzą klucz podstawowy. CREATE TABLE test ( id INTEGER, nazwa VARCHAR(20), PRIMARY KEY(id, nazwa) );

Atrybut NOT NULL oznacza, że w danej kolumnie nie mogą znajdować się wartości puste, czyli że każdy wiersz w tej kolumnie musi zawierać jakąś wartość. Próba zapisania wartości pustej zakończy się niepowodzeniem. Ć W I C Z E N I E

2.9

Kolumna bez wartości pustych

Utwórz tabelę zawierającą kolumnę id typu INTEGER oraz nazwa typu VARCHAR. Kolumna nazwa powinna być zdefiniowana w taki sposób, aby nie mogła zawierać wartości pustych. CREATE TABLE test ( id INTEGER, nazwa VARCHAR(20) NOT NULL );

Każdej kolumnie można przypisać wartość domyślną, która będzie w niej umieszczana, kiedy w instrukcji wprowadzającej dane nie zostanie podana wartość dla tej kolumny. Przypisanie wartości domyślnej uzyskamy przez umieszczenie w definicji kolumny atrybutu DEFAULT w postaci: nazwa_kolumny typ_kolumny atrybuty DEFAULT wartość_domyślna

Należy pamiętać, że jeśli wartością domyślną ma być ciąg znaków, to należy go ująć w znaki zwykłego (prostego) apostrofu: 'ciąg znaków'.

Rozdział 2. • Praca z tabelami

37

Ć W I C Z E N I E

2.10

Wartość domyślna kolumny

Utwórz tabelę zawierającą kolumny id typu INTEGER oraz nazwa typu VARCHAR. Kolumna nazwa powinna mieć wartość domyślną zdefiniowaną jako ciąg znaków brak. CREATE TABLE test ( id INTEGER, nazwa VARCHAR(20) DEFAULT 'brak' );

Przy projektowaniu tabeli może się okazać, że wartości jednej lub kilku kolumn będą musiały być unikatowe, czyli niepowtarzalne. Będziemy więc chcieli, aby serwer nie pozwolił na wprowadzanie do takiej kolumny duplikatów danych. Można to osiągnąć przez zastosowanie atrybutu UNIQUE (co spowoduje automatyczne utworzenie indeksu dla tej kolumny — patrz sekcja „Indeksy”). Ć W I C Z E N I E

2.11

Kolumna bez duplikatów danych

Utwórz tabelę zawierającą kolumnę nazwa typu VARCHAR, tak aby wprowadzane do tej kolumny wartości nie mogły się powtarzać. CREATE TABLE test ( id INTEGER, nazwa VARCHAR(20) UNIQUE );

Czasami chcielibyśmy, aby wartości wybranej kolumny automatycznie zwiększały swoją wartość podczas wprowadzania każdego nowego wiersza danych. Najczęściej dotyczy to kolumn zawierających identyfikatory wierszy. Po co bowiem tworzyć je samemu, skoro baza może zrobić to za nas automatycznie? Przygotowanie kolumny, której wartości mogą być zwiększane automatycznie, różni się jednak w zależności od systemu bazodanowego; zostanie to pokazane w następnym ćwiczeniu.

38

SQL • Ćwiczenia praktyczne

Ć W I C Z E N I E

2.12

Kolumna automatycznie zwiększająca wartość

Utwórz tabelę zawierającą dwie kolumny: id typu INTEGER oraz nazwa typu VARCHAR. Pierwsza kolumna powinna być kluczem podstawowym (głównym), a jej wartości powinny być zwiększane automatycznie podczas dodawania danych. W przypadku bazy MySQL wystarczy do kolumny typu INTEGER dodać atrybut AUTO_INCREMENT. Instrukcja tworząca tabelę będzie wtedy miała postać: CREATE TABLE test ( id INTEGER PRIMARY KEY AUTO_INCREMENT, nazwa VARCHAR(20) );

W SQLite używane jest słowo AUTOINCREMENT9 (bez znaku pokreślenia): CREATE TABLE test ( id INTEGER PRIMARY KEY AUTOINCREMENT, nazwa VARCHAR(20) );

W przypadku PostgreSQL należy użyć typu danych o nazwie SERIAL10: CREATE TABLE test ( id SERIAL PRIMARY KEY NOT NULL, nazwa VARCHAR(20) );

9

W systemie SQLite każda kolumna typu INTEGER PRIMARY KEY jest traktowana jako automatycznie zwiększająca swoją wartość. Można to osiągnąć, pomijając daną kolumnę przy wprowadzaniu danych lub też wprowadzając do niej wartość null. W takim przypadku nie ma jednak gwarancji, że uzyskamy zawsze zwiększające się wartości — po wyczerpaniu puli dostępnych numerów mogą zostać ponownie użyte te z usuniętych wierszy. Użycie słowa AUTOINCREMENT gwarantuje, że numery zawsze będą się zwiększały, a po wyczerpaniu puli dodawanie nowych danych nie będzie możliwe.

10

W rzeczywistości powoduje to powstanie sekwencji, którą można by również utworzyć ręcznie. Użycie słowa SERIAL to zwykłe uproszczenie zapisu.

Rozdział 2. • Praca z tabelami

39

W MS SQL za typem danych należy umieścić identyfikator IDENTITY: CREATE TABLE test ( id INTEGER IDENTITY PRIMARY KEY NOT NULL, nazwa VARCHAR(20) );

W przypadku zaś bazy Oracle należy utworzyć zwykłą kolumnę typu INTEGER: CREATE TABLE test ( id INTEGER PRIMARY KEY NOT NULL, nazwa VARCHAR(20) )

oraz sekwencję generującą kolejne liczby całkowite, co zostanie pokazane w rozdziale 3. (ćwiczenie 3.7).

Indeksy Indeksowanie kolumny oznacza, że wewnątrz bazy powstanie specjalna struktura porządkująca dane w kolumnie, co przyspiesza wiele operacji, takich jak sortowanie czy przeszukiwanie. Indeks nie musi jednak dotyczyć tylko jednej kolumny, może się składać z kilku. Można go utworzyć za pomocą instrukcji CREATE INDEX o ogólnej postaci: CREATE [UNIQUE] INDEX nazwa_indeksu ON nazwa_tabeli (kolumny_składowe)

Jeśli zostanie użyte opcjonalne słowo UNIQUE, kolumny zawarte w indeksie będą musiały zawierać wartości unikatowe (znaczenie będzie takie samo jak użycie atrybutu UNIQUE w ćwiczeniu 2.11). W większości systemów utworzenie klucza głównego wiąże się również z automatycznym utworzeniem indeksu związanego z tym kluczem. Nazwa indeksu nadana mu podczas tworzenia pozwala na późniejsze odwoływanie się do niego, np. kiedy trzeba go z tabeli usunąć. Uwaga: w wielu systemach (m.in. SQLite, Oracle, PostgreSQL) nazwa indeksu powinna być unikatowa w obrębie całej bazy, tzn. nie mogą istnieć dwa indeksy o takiej samej nazwie, nawet jeśli dotyczą różnych tabel (nie dotyczy to MS SQL i MySQL).

40

SQL • Ćwiczenia praktyczne

Ć W I C Z E N I E

2.13

Indeks dla pojedynczej kolumny

Dodaj do tabeli powstałej w ćwiczeniu 2.6 indeks związany z kolumną id. CREATE INDEX id_ind ON test (id); Ć W I C Z E N I E

2.14

Kilka indeksów w jednej tabeli

Dodaj do tabeli powstałej w ćwiczeniu 2.6 indeksy związane z kolumnami imie i nazwisko. Aby wykonać ćwiczenie, należy użyć dwóch następujących instrukcji: CREATE INDEX imie_ind ON test (imie); CREATE INDEX nazwisko_ind ON test (nazwisko); Ć W I C Z E N I E

2.15

Indeks złożony z kilku kolumn

Utwórz dla tabeli test z ćwiczenia 2.6 indeks złożony z kolumn imie i nazwisko. CREATE INDEX indeks1 ON test (imie, nazwisko);

W przypadku bazy MySQL indeks można zdefiniować również podczas tworzenia tabeli. Instrukcja CREATE tworząca tabelę wraz z indeksem ma ogólną postać11: CREATE TABLE nazwa_tabeli ( definicje kolumn, INDEX nazwa_indeksu_1 (kolumna_x, kolumna_y, ..., kolumna_z), INDEX nazwa_indeksu_2 (kolumna_x, kolumna_y, ..., kolumna_z), ... INDEX nazwa_indeksu_N (kolumna_x, kolumna_y, ..., kolumna_z) )

11

W rzeczywistości instrukcja taka jest rozbijana na instrukcję tworzącą tabelę oraz serię instrukcji CREATE INDEX, tworzących indeksy.

Rozdział 2. • Praca z tabelami

41

Ć W I C Z E N I E

2.16

Indeks dla pojedynczej kolumny (MySQL)

Utwórz tabelę zawierającą dwie kolumny: pierwszą typu INTEGER, będącą kluczem podstawowym, oraz drugą typu VARCHAR, która ma być indeksowana. CREATE TABLE test ( id INTEGER, nazwa VARCHAR(20), INDEX ind_nazwa (nazwa) ); Ć W I C Z E N I E

2.17

Kilka indeksów w jednej tabeli (MySQL)

Zmodyfikuj instrukcję tworzącą tabelę z ćwiczenia 2.6 w taki sposób, aby wszystkie kolumny były indeksowane. CREATE TABLE test ( id INTEGER, imie VARCHAR(20), nazwisko VARCHAR(30), INDEX ind_id (id), INDEX ind_imie (imie), INDEX ind_nazwisko (nazwisko) ); Ć W I C Z E N I E

2.18

Indeks złożony z kilku kolumn (MySQL)

Utwórz tabelę zawierającą trzy kolumny: id typu INTEGER, będącą kluczem podstawowym, oraz imie i nazwisko, typu VARCHAR. Kolumny imie i nazwisko powinny tworzyć indeks. CREATE TABLE test ( id INTEGER PRIMARY KEY, imie VARCHAR(20) NOT NULL, nazwisko VARCHAR(30) NOT NULL, INDEX ind_imie_nazwisko (imie, nazwisko) );

42

SQL • Ćwiczenia praktyczne

Usunięcie indeksu zapewnia nam instrukcja DROP INDEX o postaci: DROP INDEX nazwa_indeksu;

gdzie nazwa_indeksu to nazwa nadana w instrukcji CREATE INDEX (lub nazwa systemowa, jeśli indeks został utworzony automatycznie). Baza PostgreSQL pozwala na jednoczesne usunięcie wielu indeksów: DROP INDEX nazwa_indeksu1[, nazwa_indeksu2[, ... [, nazwa_indeksuN]]];

W przypadku baz MySQL i MS SQL instrukcja ta ma postać: DROP INDEX nazwa_indeksu ON nazwa_tabeli Ć W I C Z E N I E

2.19

Usuwanie indeksów

Usuń wszystkie indeksy dodane do tabeli test w ćwiczeniach 2.13, 2.14 i 2.15. Indeksy można usunąć za pomocą serii instrukcji (dla baz Oracle, PostgreSQL, SQLite): DROP DROP DROP DROP

INDEX INDEX INDEX INDEX

id_ind; imie_ind; nazwisko_ind; indeks1;

lub też za pomocą pojedynczej instrukcji o postaci (tylko w przypadku bazy PostgreSQL): DROP INDEX id_ind, imie_ind, nazwisko_ind, indeks1;

W bazach MySQL i MS SQL należy dodatkowo uwzględnić nazwy tabel: DROP DROP DROP DROP

INDEX INDEX INDEX INDEX

id_ind ON test imie_ind ON test nazwisko_ind ON test indeks1 ON test

Modyfikacja tabel Jeśli konieczna jest zmiana struktury już istniejącej tabeli, należy skorzystać z polecenia ALTER TABLE o postaci:

Rozdział 2. • Praca z tabelami

43

ALTER TABLE nazwa_tabeli zmiana

Można w ten sposób dodawać i usuwać kolumny, modyfikować typy i nazwy kolumn, dodawać bądź usuwać indeksy itp. Zmiany wprowadza się w podobny sposób jak przy tworzeniu tabeli. Każdy z ciągów zmiana jest w istocie poleceniem do wykonania. Lista dostępnych poleceń zależna jest od systemu bazy danych (np. w przypadku bazy SQLite dostępne są jedynie polecenia zmiany nazwy tabeli oraz dodawania kolumny12). Najczęściej spotykane to: T ADD COLUMN definicja_kolumny — dodaje nową kolumnę; T ADD CONSTRAINT symbol PRIMARY KEY (kolumna1, kolumna2,...,kolumnaN)

— dodaje do tabeli klucz podstawowy, na który będą się składały kolumny wymienione w nawiasie okrągłym; T ADD CONSTRAINT symbol UNIQUE (kolumna1, kolumna2,...,kolumnaN)

T T T T T T T T T

12

— dodaje do tabeli nowy unikalny indeks, na który będą się składały kolumny wymienione w nawiasie okrągłym; ALTER COLUMN nazwa SET NOT NULL — ustawia atrybut NOT NULL dla kolumny nazwa; ALTER COLUMN nazwa SET DEFAULT wartość — ustawia wartość domyślną dla kolumny nazwa; ALTER COLUMN nazwa DROP NOT NULL — usuwa atrybut NOT NULL z kolumny nazwa; ALTER COLUMN nazwa DROP DEFAULT — usuwa wartość domyślną dla kolumny nazwa; DROP COLUMN nazwa — usuwa kolumnę nazwa; DROP CONSTRAINT symbol — usuwa ograniczenie (klucz podstawowy13, obcy, atrybut UNIQUE) o symbolu symbol; 14 DROP PRIMARY KEY — usuwa klucz podstawowy ; RENAME COLUMN stara_nazwa TO nowa_nazwa — zmienia nazwę kolumny na nowa_nazwa; RENAME TO nowa_nazwa — zmienia nazwę tabeli na nowa_nazwa;

Dotyczy to wersji 3.7 — najnowszej dostępnej w trakcie przygotowywania materiałów do książki. Być może w nowszych wersjach zostanie dodana obsługa również innych poleceń.

13

W przypadku bazy MySQL należy użyć polecenia DROP PRIMARY KEY.

14

W przypadku bazy PostgreSQL należy użyć polecenia DROP CONSTRAINT.

44

SQL • Ćwiczenia praktyczne

Przećwiczmy teraz różne rodzaje modyfikacji struktury tabel. W tym celu przygotujemy tabelę testową o nazwie osoby, która początkowo będzie zawierała następujące kolumny: T id — typu INTEGER, przechowująca identyfikator każdej osoby; T imie — typu VARCHAR, przechowująca imiona osób; T nazwisko — typu VARCHAR, przechowująca nazwiska osób. Do jej utworzenia należy zatem wykorzystać instrukcję SQL: CREATE TABLE osoby ( id INTEGER, imie VARCHAR(20), nazwisko VARCHAR(30) );

Szybko zauważymy, że nie zdefiniowaliśmy tutaj klucza głównego, którym powinna być kolumna id. Trzeba zatem naprawić to niedopatrzenie. Ć W I C Z E N I E

2.20

Zdefiniowanie klucza głównego w istniejącej tabeli

Dodaj do tabeli osoby klucz główny składający się z kolumny id. Aby dodać do tabeli osoby klucz główny, należy użyć polecenia15: ALTER TABLE osoby ADD CONSTRAINT pk PRIMARY KEY (id);

w którym pk jest nazwą klucza głównego.

Jeśli okaże się, że klucz podstawowy umieściliśmy nie w tej kolumnie, co trzeba, można go usunąć za pomocą polecenia DROP PRIMARY KEY. Ć W I C Z E N I E

2.21

Usuwanie klucza głównego

Usuń klucz główny dodany w ćwiczeniu 2.20.

15

W przypadku bazy Microsoft SQL Server najpierw należy dodać do kolumny id atrybut NOT NULL.

Rozdział 2. • Praca z tabelami

45

Aby usunąć z tabeli osoby dodany w ćwiczeniu 2.20 klucz główny, należy wydać polecenie: ALTER TABLE osoby DROP CONSTRAINT pk;

lub (w przypadku bazy MySQL): ALTER TABLE osoby DROP PRIMARY KEY; Ć W I C Z E N I E

2.22

Dodanie do tabeli złożonego klucza głównego

Dodaj do tabeli osoby klucz główny składający się z kolumn id, imie, nazwisko. Jeśli klucz podstawowy ma składać się z więcej niż jednej kolumny, ich nazwy należy wymienić w nawiasie okrągłym występującym po słowach PRIMARY KEY, oddzielając je od siebie znakami przecinka. A zatem, jeśli mają to być kolumny id, imie, nazwisko, instrukcja będzie miała postać16: ALTER TABLE osoby ADD CONSTRAINT pk PRIMARY KEY (id, imie, nazwisko); Ć W I C Z E N I E

2.23

Dodanie do tabeli nowej kolumny

Dodaj do tabeli osoby kolumnę pesel typu VARCHAR o długości 11 znaków. Nową kolumnę dodamy za pomocą instrukcji: ALTER TABLE osoby ADD pesel VARCHAR(11);

W przypadku baz MySQL, PostgreSQL i SQLite poprawne będzie również polecenie: ALTER TABLE osoby ADD COLUMN pesel VARCHAR(11); Ć W I C Z E N I E

2.24

Usuwanie kolumny

Usuń z tabeli osoba kolumnę pesel.

16

W przypadku bazy Microsoft SQL Server wszystkie kolumny składowe muszą być wcześniej zdefiniowane jako NOT NULL.

46

SQL • Ćwiczenia praktyczne

Kolumnę pesel możemy usunąć za pomocą instrukcji: ALTER TABLE osoby DROP COLUMN pesel; Ć W I C Z E N I E

2.25

Zmiana nazwy istniejącej kolumny

Zmień nazwę kolumny id (z tabeli osoba) na osobaid. Zmianę nazwy kolumny uzyskamy za pomocą instrukcji (dla baz Oracle i PostgreSQL): ALTER TABLE osoby RENAME COLUMN id TO osobaid;

lub (dla bazy MySQL): ALTER TABLE osoby CHANGE COLUMN id osobaid INTEGER;

W przypadku MS SQL należy użyć systemowej procedury składowanej (z ang. system stored procedure) sp_rename: EXEC sp_rename 'osoby.id', 'osobaid'

Istniejącym w tabeli kolumnom można nadawać (i usuwać z nich) wartości domyślne, co odbywa się za pomocą wymienionego wyżej polecenia ALTER TABLE ALTER COLUMN. Dodajmy więc wartość domyślną dla kolumny imie. Ć W I C Z E N I E

2.26

Dodanie wartości domyślnej

Ustaw wartość domyślną dla kolumny imie. Aby wykonać ćwiczenie, należy wykonać instrukcję (dla baz MySQL i PostgreSQL): ALTER TABLE osoby ALTER COLUMN imie SET DEFAULT 'nieznane';

lub (dla bazy Oracle): ALTER TABLE osoby MODIFY (imie VARCHAR(20) DEFAULT 'nieznane');

W przypadku MS SQL trzeba będzie nałożyć na kolumnę ograniczenie (ang. constraint) za pomocą składni ADD CONSTRAINT. Zakładając, że miałoby mieć nazwę osoby_imie_nn, całe polecenie wyglądałoby następująco: ALTER TABLE osoby ADD CONSTRAINT osoby_imie_nn DEFAULT 'nieznane' ´FOR imie;

Rozdział 2. • Praca z tabelami

47

Ć W I C Z E N I E

2.27

Usunięcie wartości domyślnej

Usuń wartość domyślną z kolumny imie. Usunięcie wartości domyślnej z kolumny imie zapewni nam polecenie (dla baz MySQL i PostgreSQL): ALTER TABLE osoby ALTER COLUMN imie DROP DEFAULT;

lub (dla bazy Oracle): ALTER TABLE osoby MODIFY (imie VARCHAR(20) DEFAULT null);

lub (dla bazy MS SQL; zakładając, że ograniczenie DEFAULT ma nazwę nadaną w ćwiczeniu 2.26): ALTER TABLE osoby DROP CONSTRAINT osoby_imie_nn;

Atrybut UNIQUE, uniemożliwiający wprowadzanie do kolumny duplikatów danych, również może być dodawany i usuwany już po utworzeniu samej tabeli. Ć W I C Z E N I E

2.28

Dodanie atrybutu UNIQUE

Zmodyfikuj tabelę osoby w taki sposób, aby dane w kolumnie pesel nie mogły się powtarzać. Dodanie atrybutu UNIQUE do kolumny pesel uzyskamy za pomocą instrukcji: ALTER TABLE osoby ADD CONSTRAINT pesel_unique UNIQUE (pesel);

Nazwą ograniczenia będzie pesel_unique. Należy jej użyć podczas usuwania ograniczenia. Ć W I C Z E N I E

2.29

Usunięcie atrybutu UNIQUE

Usuń atrybut UNIQUE z kolumny pesel. Należy wykonać polecenie (dla baz MS SQL Server, Oracle, PostgreSQL): ALTER TABLE osoby DROP CONSTRAINT pesel_unique;

48

SQL • Ćwiczenia praktyczne

lub (dla bazy MySQL): ALTER TABLE osoby DROP INDEX pesel_unique; Ć W I C Z E N I E

2.30

Dodanie atrybutu NOT NULL

Dodaj do kolumny nazwisko atrybut NOT NULL. Dodanie atrybutu NOT NULL uzyskamy dzięki poleceniu (PostgreSQL): ALTER TABLE osoby ALTER COLUMN nazwisko SET NOT NULL;

lub (MySQL): ALTER TABLE osoby MODIFY COLUMN nazwisko VARCHAR(30) NOT NULL;

lub (Oracle): ALTER TABLE osoby MODIFY (nazwisko VARCHAR(30) NOT NULL);

lub (MS SQL): ALTER TABLE osoby ALTER COLUMN nazwisko VARCHAR(30) NOT NULL; Ć W I C Z E N I E

2.31

Usunięcie atrybutu NOT NULL

Usuń z kolumny nazwisko atrybut NOT NULL. Usunięcie atrybutu NOT NULL uzyskamy dzięki poleceniu (PostgreSQL): ALTER TABLE osoby ALTER COLUMN nazwisko DROP NOT NULL;

lub (MySQL): ALTER TABLE osoby MODIFY COLUMN nazwisko VARCHAR(30);

lub (Oracle): ALTER TABLE osoby MODIFY (nazwisko VARCHAR(30));

lub (MS SQL): ALTER TABLE osoby ALTER COLUMN nazwisko VARCHAR(30);

W przypadku baz PostgreSQL i MS SQL, aby polecenie zadziałało prawidłowo, kolumna nazwisko nie może być składnikiem klucza głównego.

Rozdział 2. • Praca z tabelami

49

Na zakończenie postarajmy się zmienić nazwę całej tabeli osoby. Jak to zrobić, pokaże następne ćwiczenie. Ć W I C Z E N I E

2.32

Zmiana nazwy tabeli

Zmień nazwę tabeli osoby na osoba. Aby zmienić nazwę tabeli osoby na osoba, trzeba użyć polecenia: ALTER TABLE osoby RENAME TO osoba;

W przypadku systemu MS SQL należy użyć systemowej procedury składowanej sp_rename: EXEC sp_rename 'osoby', 'osoba'

Usuwanie tabel Tabele usuwa się za pomocą instrukcji DROP TABLE, której schemat ma postać: DROP TABLE tabela;

W przypadku baz MS SQL, MySQL i PostgreSQL można też użyć polecenia w postaci: DROP TABLE tabela1, tabela2,..., tabelaN;

To pozwala na usunięcie zarówno jednej tabeli, jak i całego zestawu tabel. Ć W I C Z E N I E

2.33

Usunięcie wskazanej tabeli

Usuń z bazy tabelę osoba. Usunięcie tabeli o nazwie osoba wykonamy, wydając polecenie: DROP TABLE osoba;

50

SQL • Ćwiczenia praktyczne

3 Umieszczanie danych w bazie Instrukcja INSERT INTO Tabele, których sposób tworzenia został przedstawiony w rozdziale 2., trzeba wypełnić danymi. Służy do tego instrukcja INSERT INTO. Jej podstawowa forma, obsługiwana przez praktycznie wszystkie dialekty SQL, ma ogólną postać: INSERT [INTO] nazwa_tabeli [(kolumna1, kolumna2, ..., kolumnaN)] VALUES (wartość1, wartość2, ..., wartośćN)

Powoduje ona wprowadzenie do tabeli nowego wiersza, w którym w polu kolumna1 została zapisana wartość wartość1, w polu kolumna2 — wartość wartość2 itd. Słowo INTO zazwyczaj może być pominięte, my jednak będziemy je stosować w celu zachowania maksymalnej kompatybilności. Przygotujmy teraz przykładową tabelę, która posłuży do wykonywania następnych ćwiczeń. Niech będzie to tabela osoby utworzona za pomocą instrukcji: CREATE TABLE osoby ( id INTEGER PRIMARY KEY NOT NULL, imie VARCHAR(20) NOT NULL, nazwisko VARCHAR(30) NOT NULL, pesel CHAR(11) );

52

SQL • Ćwiczenia praktyczne

Ć W I C Z E N I E

3.1

Wprowadzenie pojedynczego wiersza do tabeli

Wprowadź do tabeli osoby przykładowy wiersz danych. Zakładając, że nowy wiersz ma zawierać dane Jana Kowalskiego mającego PESEL 01234567890, któremu został nadany identyfikator 1, należy użyć instrukcji INSERT INTO w postaci: INSERT INTO osoby (id, imie, nazwisko, pesel) VALUES (1, 'Jan', ´'Kowalski', '01234567890');

Aby zwiększyć czytelność, można ją rozbić na kilka wierszy (rysunek 3.1), np.: INSERT INTO osoby (id, imie, nazwisko, pesel) VALUES (1, 'Jan', 'Kowalski', '01234567890');

Rysunek 3.1. Wykonanie zapytania wprowadzającego dane w bazie PostgreSQL

Efekt wykonania zapytania w bazie PostgreSQL jest widoczny na rysunku 3.1, a w bazie MySQL — na rysunku 3.2. Rysunek 3.2. Wykonanie zapytania wprowadzającego dane w bazie MySQL

Zwróćmy przy tym uwagę, że wszystkie wprowadzone ciągi znaków zostały ujęte w apostrofy (w MySQL i MS SQL można także użyć znaków cudzysłowu — lepiej jednak pozostać przy znakach apostrofu). Jest to niezbędne, aby zapytanie zostało wykonane prawidłowo. Nie ma natomiast potrzeby ujmowania w znaki apostrofu wartości liczbowych.

Rozdział 3. • Umieszczanie danych w bazie

53

W sytuacji, kiedy wprowadzany wiersz zawiera dane dla wszystkich kolumn, ich nazwy mogą zostać pominięte. Kolejność danych powinna być wtedy taka jak kolejność kolumn w definicji tabeli. Ć W I C Z E N I E

3.2

Wprowadzenie danych z pominięciem nazw kolumn

Wprowadź do tabeli osoby przykładowy wiersz bez używania nazw kolumn. INSERT INTO osoby VALUES (2, 'Adam', 'Nowak', '12345678901');

Nie ma jednak konieczności każdorazowego wprowadzania danych do wszystkich kolumn. Część z nich może zostać pominięta, o ile oczywiście nie mają one przypisanego atrybutu NOT NULL. Ć W I C Z E N I E

3.3

Pominięcie danych dla wybranych kolumn

Wprowadź do tabeli osoby wiersz zawierający jedynie dane o identyfikatorze, imieniu oraz nazwisku. Operację wprowadzenia do tabeli osoby wiersza bez danych dla kolumny pesel można przeprowadzić na dwa sposoby. Pierwszy z nich to instrukcja: INSERT INTO osoby VALUES (3, 'Janusz', 'Nowak', NULL);

W takim przypadku w kolumnie pesel jawnie wstawiamy wartość pustą NULL. Druga możliwość to instrukcja: INSERT INTO osoby (id, imie, nazwisko) VALUES (3, 'Janusz', 'Nowak');

W tym przypadku niezbędne jest użycie nazw kolumn.

Taka operacja jak w ćwiczeniu 3.3 nie byłaby jednak możliwa w przypadku kolumny imie bądź nazwisko, gdyż mają one atrybut NOT NULL, czyli nie mogą być puste. Sprawdźmy, czy tak jest faktycznie.

54

SQL • Ćwiczenia praktyczne

Ć W I C Z E N I E

3.4

Pominięcie danych dla kolumny z atrybutem NOT NULL

Wykonaj próbę wprowadzenia do tabeli osoby wiersza niezawierającego danych o imieniu. Pominięcie danych o imieniu zapewni przykładowa instrukcja o postaci: INSERT INTO osoby (id, nazwisko, pesel) VALUES (4, 'Nowak', ´'89012345678');

której wykonanie zakończy się zgłoszeniem komunikatu o błędzie (komunikaty będą się różnić w zależności od wykorzystywanego systemu bazy danych). Skoro bowiem kolumna imie ma nadany atrybut NOT NULL oraz nie została jej nadana wartość domyślna, to nie można wprowadzić do tabeli wiersza niezawierającego danych dla tej kolumny. Efekt wykonania instrukcji dla bazy MS SQL został przedstawiony na rysunku 3.3.

Rysunek 3.3. Przykładowy efekt próby wprowadzenia wartości null do kolumny oznaczonej jako NOT NULL

Warto również wiedzieć, że kolejność wprowadzania danych w instrukcji INSERT nie musi być taka sama jak struktura kolumn w tabeli. Ć W I C Z E N I E

3.5

Zmiana kolejności kolumn w zapytaniu wprowadzającym dane

Dodaj do tabeli osoby wiersz danych tak, aby kolejność kolumn była inna niż zdefiniowana w tabeli. Wykonanie ćwiczenia zapewni nam instrukcja: INSERT INTO osoby (pesel, id, nazwisko, imie) VALUES ('23456789012', 5, 'Arkuszewski', 'Janusz');

Rozdział 3. • Umieszczanie danych w bazie

55

Zobaczmy teraz, co się stanie, jeśli, np. przez przeoczenie, spróbujemy dwukrotnie wykonać instrukcję wprowadzającą dane z ćwiczenia 3.2. Czy taka operacja będzie możliwa? Ć W I C Z E N I E

3.6

Próba wprowadzenia duplikatu danych

Wykonaj ponownie instrukcję z ćwiczenia 3.2. Zaobserwuj zachowanie serwera. Po ponownym wykonaniu instrukcji INSERT INTO z ćwiczenia 3.2 serwer zgłosi nam błąd. Komunikat może mieć różną postać, np. w przypadku PostgreSQL będzie to tekst widoczny na rysunku 3.4, a w przypadku MySQL — na rysunku 3.5, jednak przyczyna zawsze będzie ta sama. Otóż wartości dla kolumn imie, nazwisko oraz pesel mogą się powtarzać, ale wartość dla kolumny id — już nie, jest ona bowiem kluczem podstawowym.

Rysunek 3.4. Próba wprowadzenia duplikatu klucza podstawowego w bazie PostgreSQL

Rysunek 3.5. Próba wprowadzenia duplikatu klucza podstawowego w bazie MySQL

Jak jednak uniknąć przypadkowych duplikatów klucza głównego? Najlepiej pozostawić jego wygenerowanie serwerowi baz danych. Usuńmy więc tabelę osoby, wykonując instrukcję: drop table osoby;

i utwórzmy ponownie, tym razem w taki sposób, aby kolumna id umożliwiała automatyczne generowanie kolejnego identyfikatora.

56

SQL • Ćwiczenia praktyczne

Z rozdziału 2. wiadomo, że definicja takiej kolumny będzie miała postać (należy ją wstawić do przedstawionej na początku tego rozdziału instrukcji CREATE TABLE): T id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT — dla MySQL; T id SERIAL PRIMARY KEY NOT NULL — dla PostgreSQL; T id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL — dla SQLite; T id INTEGER IDENTITY PRIMARY KEY NOT NULL — dla MS SQL; T id INTEGER PRIMARY KEY NOT NULL — dla Oracle. W bazie Oracle należy dodatkowo utworzyć sekwencję w postaci1: CREATE SEQUENCE osoby_id_seq START WITH 1 INCREMENT BY 1;

Oczywiście nazwa sekwencji (osoby_id_seq) jest przykładowa i może być zmieniona na inną. Ć W I C Z E N I E

3.7

Automatyczne generowanie wartości dla kolumny

Wprowadź do tabeli osoby wiersz danych w taki sposób, aby wartość dla kolumny id została wygenerowana automatycznie. Wartość kolumny id może zostać wygenerowana automatycznie, gdyż w trakcie tworzenia tabeli została ona odpowiednio zadeklarowana. Jeśli chcemy skorzystać z takiego udogodnienia w przypadku baz MySQL, PostgreSQL, MS SQL i SQLite, w instrukcji wprowadzającej wiersz należy pominąć kolumnę id, np.: INSERT INTO osoby (imie, nazwisko, pesel) VALUES ('Marceli', 'Przybysz', ´'56789012345');

lub też (jedynie w przypadku MySQL i PostgreSQL) zastosować w niej wartość DEFAULT: INSERT INTO osoby (id, imie, nazwisko, pesel) VALUES (DEFAULT, ´'Marceli', 'Przybysz', '56789012345');

Tylko w MySQL i SQLite zamiast DEFAULT można użyć NULL: INSERT INTO osoby (id, imie, nazwisko, pesel) VALUES (NULL, 'Marceli', ´'Przybysz', '56789012345');

1

Dokładne omówienie tego tematu wykracza, niestety, poza ramy niniejszej publikacji.

Rozdział 3. • Umieszczanie danych w bazie

57

W MySQL dopuszczalne jest też użycie wartości 0: INSERT INTO osoby (id, imie, nazwisko, pesel) VALUES (0, 'Marceli', ´'Przybysz', '56789012345');

Wprowadzony w ten sposób wiersz będzie miał identyfikator o jeden większy niż maksymalna wartość zapisana w kolumnie id. Czyli jeśli przed wykonaniem jednej z wymienionych instrukcji największą wartością w kolumnie id było np. 142, to wprowadzony wiersz będzie miał w tej kolumnie wartość 15. W przypadku bazy Oracle należy skorzystać z utworzonej wcześniej sekwencji. Nowy wiersz z automatycznie wygenerowanym kolejnym identyfikatorem uzyskamy zatem dzięki instrukcji: INSERT INTO osoby (id, imie, nazwisko, pesel) VALUES ´(osoby_id_seq.nextval, 'Marceli', 'Przybysz', '56789012345');

Wprowadzanie wielu wierszy Niektóre wersje SQL, dostępne np. w MySQL, MS SQL i PostgreSQL w wersji 9 lub nowszej, pozwalają na jednoczesne wstawianie wielu wierszy3. Służy do tego lekko zmodyfikowana instrukcja INSERT INTO o postaci: INSERT [INTO] nazwa_tabeli VALUES (wartość1A, wartość2A, (wartość1B, wartość2B, ... (wartość1Z, wartość2Z,

[(kolumna1, kolumna2, ..., kolumnaN)] ..., wartośćNA), ..., wartośćNB), ..., wartośćNZ);

Wykonajmy trzy ćwiczenia wykorzystujące tę wersję.

2

Chodzi o największą wartość kiedykolwiek wprowadzoną do kolumny, a nie o największą wartość aktualnie znajdującą się w kolumnie.

3

Jednoczesne wstawianie wielu wierszy jest możliwe również poprzez użycie podzapytania, o czym będzie mowa w rozdziale 9.

58

SQL • Ćwiczenia praktyczne

Ć W I C Z E N I E

3.8

Umieszczenie w tabeli wielu wierszy

Za pomocą pojedynczej instrukcji INSERT wprowadź do tabeli osoby dane trzech osób. Aby wykonać zadanie, możemy zastosować instrukcję SQL o postaci: INSERT VALUES (15, (16, (17,

INTO osoby (id, imie, nazwisko, pesel) 'Anna', 'Kowalska', '07902657182'), 'Janina', 'Nowak', '17912652182'), 'Dariusz', 'Malinowski', '02699657182');

Ć W I C Z E N I E

3.9

Wprowadzenie wielu wierszy z pominięciem wybranych danych

Za pomocą pojedynczej instrukcji INSERT wprowadź do tabeli osoby trzy wiersze, tak aby dane z kolumny pesel zostały pominięte. Również w przypadku umieszczania w tabeli wielu wierszy część kolumn może być pomijana. Możemy np. dodać dane trzech osób, rezygnując z wprowadzania ich peseli: INSERT VALUES (15, (16, (17,

INTO osoby (id, imie, nazwisko) 'Anna', 'Kowalska'), 'Janina', 'Nowak'), 'Dariusz', 'Malinowski');

Ć W I C Z E N I E

3.10

Wprowadzenie wielu wierszy z automatycznym generowaniem identyfikatorów

Za pomocą pojedynczej instrukcji INSERT wprowadź do tabeli osoby trzy wiersze, tak aby wartości kolumny id zostały wygenerowane automatycznie. Jeśli identyfikatory wprowadzanych klientów mają być generowane automatycznie, zastosujemy instrukcję (o ile kolumna id została zdefiniowana jako automatycznie zwiększająca wartość):

Rozdział 3. • Umieszczanie danych w bazie

59

INSERT INTO osoby (imie, nazwisko, pesel) VALUES ('Anna', 'Kowalska', '07902657182'), ('Janina', 'Nowak', '17912652182'), ('Dariusz', 'Malinowski', '02699657182');

lub (w przypadku MySQL i PostgreSQL): INSERT INTO VALUES (DEFAULT, (DEFAULT, (DEFAULT,

osoby (id, imie, nazwisko, pesel) 'Anna', 'Kowalska', '07902657182'), 'Janina', 'Nowak', '17912652182'), 'Dariusz', 'Malinowski', '02699657182');

Druga postać instrukcji INSERT Oprócz przedstawionej na wcześniejszych stronach wersji instrukcji INSERT spotyka się wersję o ogólnej postaci: INSERT [INTO] nazwa_tabeli SET kolumna1=wartość1, kolumna2=wartość2, ´..., kolumaN=wartośćN

Jej przeznaczenie jest takie samo: wprowadzenie do tabeli nowego wiersza zawierającego wskazane wartości. Obsługę tej wersji polecenia znajdziemy w systemie MySQL. Wykonajmy więc kilka ćwiczeń na tabeli osoba, z której najpierw należy usunąć wszystkie dotychczas wprowadzone dane. Można w tym celu usunąć tabelę za pomocą przedstawionej już wcześniej instrukcji DROP TABLE i ponownie ją utworzyć lub też skorzystać z nowej instrukcji: DELETE FROM osoby;

która zostanie dokładniej omówiona w rozdziale 5. Ć W I C Z E N I E

3.11

Wstawianie wiersza za pomocą drugiej postaci instrukcji INSERT

Wykorzystaj drugą postać instrukcji INSERT, aby wprowadzić do tabeli osoby wiersz z danymi takimi jak w ćwiczeniu 3.1. Instrukcja INSERT INTO w drugiej wersji, wykonująca opisane zadanie, będzie miała postać:

60

SQL • Ćwiczenia praktyczne INSERT INTO osoby SET id=1, imie='Jan', nazwisko='Kowalski', ´pesel='01234567890';

Aby zwiększyć czytelność zapisu, można ją rozbić na kilka wierszy: INSERT INTO osoby SET id=1, imie='Jan', nazwisko='Kowalski', pesel='01234567890'; Ć W I C Z E N I E

3.12

Wiersz z automatycznie generowanym identyfikatorem

Wykorzystaj drugą postać instrukcji INSERT, aby wprowadzić do tabeli osoby wiersz, w którym identyfikator zostanie wygenerowany automatycznie. Jeśli podczas wstawiania nowego wiersza wartość kolumny id ma być generowana automatycznie, można użyć instrukcji: INSERT INTO osoby SET imie='Jan', nazwisko='Kowalski', ´pesel='01234567890';

lub: INSERT INTO osoby SET id=DEFAULT, imie='Jan', nazwisko='Kowalski', ´pesel='01234567890'; Ć W I C Z E N I E

3.13

Zmiana kolejności wprowadzanych danych

Wykorzystaj drugą postać instrukcji INSERT do wstawienia nowego wiersza tak, aby kolejność wprowadzanych kolumn była inna niż zdefiniowana w tabeli. Wprowadzanie danych w innej kolejności niż ustawienie kolumn w tabeli odbywa się w taki sam sposób jak w przypadku pierwszej postaci instrukcji INSERT. Należy po prostu zmienić kolejność kolumn występujących po słowie SET: INSERT INTO osoby SET pesel='09934577890', id=5, nazwisko='Nowak', ´imie='Adam';

4 Pobieranie danych z tabel Podstawy instrukcji SELECT Dane zapisane w tabelach bazy danych można pobierać za pomocą instrukcji SELECT. Jej podstawowa postać ma schemat: SELECT kolumna1, kolumna2, ..., kolumnaN FROM tabela [WHERE warunek] [ORDER BY kolumna1, kolumna2, ..., kolumnaN [ASC | DEC]]

Taka konstrukcja oznacza: pobierz wartości wymienionych kolumn z tabeli tabela, spełniających warunek warunek, a wyniki posortuj względem kolumn wymienionych w klauzuli ORDER BY, rosnąco (ASC) lub malejąco (DESC). Elementy ujęte w nawiasy kwadratowe są opcjonalne. Aby przećwiczyć działanie tej wersji instrukcji SELECT, utworzymy przykładową tabelę pracownicy o następujących kolumnach: T id — typu INTEGER, będąca kluczem głównym i zawierająca identyfikator każdego wiersza; T imie — typu VARCHAR(20), z atrybutem NOT NULL, zawierająca imię pracownika; T nazwisko — typu VARCHAR(30), z atrybutem NOT NULL, zawierająca nazwisko pracownika; T placa — typu DECIMAL(7, 2), z atrybutem NOT NULL, zawierająca miesięczne wynagrodzenie pracownika;

62

SQL • Ćwiczenia praktyczne

T stanowisko — typu VARCHAR(20), z atrybutem NOT NULL, zawierająca

stanowisko pracownika1; T pesel — typu CHAR(11), zawierająca PESEL pracownika. Taka tabela zostanie utworzona za pomocą instrukcji: CREATE TABLE pracownicy ( id INTEGER PRIMARY KEY, imie VARCHAR(20) NOT NULL, nazwisko VARCHAR(30) NOT NULL, placa DECIMAL(7, 2) NOT NULL, stanowisko VARCHAR(20), pesel CHAR(11) );

Wypełnimy ją przykładowymi danymi: INSERT INTO pracownicy VALUES (1, 'Adam', 'Kowalski', 1624.50, ´'magazynier', '12345678901'); INSERT INTO pracownicy VALUES (2, 'Adam', 'Nowak', 3760.00, 'kierownik', ´'92345678901'); INSERT INTO pracownicy VALUES (3, 'Andrzej', 'Kowalski', 4200.00, ´'kierownik', '72345678901'); INSERT INTO pracownicy VALUES (4, 'Arkadiusz', 'Malinowski', 1600.00, ´'kierowca', '92345678909'); INSERT INTO pracownicy VALUES (5, 'Andrzej', 'Malinowski', 1450.00, ´'sprzedawca', NULL); INSERT INTO pracownicy VALUES (6, 'Krzysztof', 'Nowicki', 1300.00, ´'sprzedawca', NULL); INSERT INTO pracownicy VALUES (7, 'Kacper', 'Adamczyk', 1610.50, ´'serwisant', '92341678903'); INSERT INTO pracownicy VALUES (8, 'Kamil', 'Andrzejczak', 1200.00, ´'asystent', NULL); INSERT INTO pracownicy VALUES (9, 'Krzysztof', 'Arkuszewski', 1500, ´'magazynier', '02343678913'); INSERT INTO pracownicy VALUES (10, 'Kamil', 'Borowski', 1600.00, ´'sprzedawca', '32349678913'); Ć W I C Z E N I E

4.1

Wyświetlenie zawartości wybranej tabeli

Użyj instrukcji SELECT do wyświetlenia zawartości tabeli pracownicy. 1

Jak wiadomo z rozdziału 1., w realnie działającej bazie nazwy stanowisk powinny raczej znajdować się w oddzielnej tabeli, jednak umieszczenie ich w tabeli pracownicy ułatwi nam wykonywanie dalszych ćwiczeń.

R o z d z i a ł 4 . • P o b i e r a n i e d a n y c h z t ab e l

63

Instrukcja SELECT, która pobierze wszystkie wiersze z tabeli pracownicy, ma postać: SELECT * FROM pracownicy;

Symbol * oznacza tu, że interesuje nas zawartość wszystkich kolumn. Przykładowy efekt działania tego polecenia został zaprezentowany na rysunku 4.1. Widać na nim, że faktycznie wyświetlone zostały wszystkie dane wprowadzone uprzednio do tabeli pracownicy, jak również to, że kolejność wierszy jest taka, w jakiej były one wprowadzane do bazy.

Rysunek 4.1. Efekt działania instrukcji SELECT pobierającej wszystkie dane z tabeli pracownicy

Aby wyświetlić zawartość tylko niektórych kolumn z wybranej tabeli, nazwy tych kolumn należy umieścić za słowem SELECT, oddzielając poszczególne nazwy znakami przecinka. Przykładowo mogą nas interesować jedynie imiona, nazwiska i stanowiska pracowników. Ć W I C Z E N I E

4.2

Pobieranie danych z wybranych kolumn

Pobierz z tabeli pracownicy dane o imionach, nazwiskach i stanowiskach. Wykonanie ćwiczenia zapewni nam instrukcja: SELECT imie, nazwisko, stanowisko FROM pracownicy;

której efekt działania został zaprezentowany na rysunku 4.2.

Istnieje również możliwość zmiany nazw kolumn w wynikach zapytania. Wystarczy, jeśli występujące w zapytaniu SELECT nazwy zastąpimy sekwencjami o ogólnej postaci:

64

SQL • Ćwiczenia praktyczne

Rysunek 4.2. Wynik pobrania danych z trzech wybranych kolumn

nazwa_kolumny AS alias

gdzie nazwa_kolumny to nazwa oryginalnej kolumny, a alias to nazwa, jaka ma się pojawić w wynikach zapytania. Ć W I C Z E N I E

4.3

Zmiana nazw kolumn w wynikach zapytania

Pobierz z tabeli pracownicy dane o imionach, nazwiskach i płacach, tak aby kolumna placa miała nazwę wynagrodzenie. Wykonanie ćwiczenia zapewni nam instrukcja: SELECT imie, nazwisko, placa AS wynagrodzenie FROM pracownicy;

Efekt wykonania ćwiczenia został zaprezentowany na rysunku 4.3. Rysunek 4.3. Nazwy kolumn w wynikach zapytania zostały zmienione

R o z d z i a ł 4 . • P o b i e r a n i e d a n y c h z t ab e l

65

Sortowanie wyników zapytań Wyniki zapytania typu SELECT mogą być sortowane, co umożliwia klauzula ORDER BY. Sortowanie może odbywać się w porządku rosnącym lub malejącym względem jednej bądź kilku kolumn. Ć W I C Z E N I E

4.4

Sortowanie w porządku rosnącym

Wyświetl zawartość tablicy pracownicy posortowaną względem kolumny nazwisko w porządku rosnącym. Zatem aby wyświetlić wszystkie wiersze tabeli posortowane względem nazwiska w porządku alfabetycznym rosnącym, należy zastosować konstrukcję: SELECT * FROM pracownicy ORDER BY nazwisko ASC;

lub prościej: SELECT * FROM pracownicy ORDER BY nazwisko;

opcja ASC jest bowiem domyślną. Wynik działania takiego zapytania został zaprezentowany na rysunku 4.4.

Rysunek 4.4. Wynik sortowania tabeli pracownicy względem kolumny nazwisko w porządku rosnącym Ć W I C Z E N I E

4.5

Sortowanie w porządku malejącym

Wyświetl zawartość tablicy pracownicy posortowaną względem kolumny nazwisko w porządku malejącym.

66

SQL • Ćwiczenia praktyczne

Aby wyświetlić wszystkie wiersze tabeli posortowane względem kolumny nazwisko w porządku alfabetycznym malejącym, należy zastosować konstrukcję: SELECT * FROM pracownicy ORDER BY nazwisko DESC;

Wynik działania tego zapytania jest widoczny na rysunku 4.5.

Rysunek 4.5. Wynik sortowania tabeli pracownicy względem kolumny nazwisko w porządku malejącym

Sortowanie może się również odbywać względem większej liczby kolumn. Możemy sobie na przykład zażyczyć, aby tablica została posortowana najpierw względem nazwiska, a następnie względem płacy. Przy czym kierunek sortowania jest niezależny dla każdej kolumny, czyli można jednocześnie sortować względem nazwiska w porządku rosnącym i płacy w porządku malejącym. Ć W I C Z E N I E

4.6

Sortowanie względem kilku kolumn

Wyświetl zawartość tablicy pracownicy posortowaną względem kolumn nazwisko (w porządku rosnącym) i placa (w porządku malejącym). Zadanie takie zostanie zrealizowane przez instrukcję SELECT o postaci: SELECT * FROM pracownicy ORDER BY nazwisko ASC, placa DESC;

Efekt jego działania został przedstawiony na rysunku 4.6.

R o z d z i a ł 4 . • P o b i e r a n i e d a n y c h z t ab e l

67

Rysunek 4.6. Wynik sortowania tabeli pracownicy względem dwóch kolumn

Kryteria pobierania danych Możliwości pobierania danych z tabeli nie ograniczają się, rzecz jasna, do wszystkich zapisanych w niej wierszy. Najczęściej interesuje nas przecież tylko pewien podzbiór danych, spełniających zadane kryteria. Otrzymanie określonego zestawu wierszy zapewnia nam klauzula WHERE instrukcji SELECT. Za klauzulą WHERE należy umieścić warunek, jaki muszą spełniać dane, aby znalazły się w wynikach zapytania. Warunek w klauzuli WHERE może zawierać operatory relacyjne przedstawione w tabeli 4.1 oraz operatory logiczne przedstawione w tabeli 4.22. Oprócz przedstawionych w powyższych tabelach operatorów relacyjnych i logicznych stosunkowo często wykorzystywane są także dwa wyrażenia operujące na ciągach znaków. Są to LIKE i NOT LIKE3. Wywołanie ze słowem LIKE ma postać: wyrażenie LIKE wzorzec

Zwraca ono wartość TRUE, jeśli wyrażenie pasuje do wzorca, w przeciwnym razie zwraca FALSE. Jako wyrażenie zazwyczaj jest stosowana nazwa kolumny. Argument wzorzec może zawierać znaki specjalne. Pierwszy z nich to %, który zastępuje dowolną liczbę znaków, drugi to _ (podkreślenie), który zastępuje dokładnie jeden znak. Oznacza

2

Poszczególne dialekty SQL mogą również zawierać inne operatory.

3

W zależności od systemu bazy danych określa się je również mianem operatorów lub funkcji.

68

SQL • Ćwiczenia praktyczne

Tabela 4.1. Operatory relacyjne Operator

Opis

Przykład

=

Operator równości. Zwraca wartość TRUE, jeśli argument znajdujący się z lewej strony jest równy argumentowi znajdującemu się z prawej strony, w przeciwnym razie zwraca FALSE.

id=10, nazwisko='Kowalski'

Zwraca wartość TRUE, jeśli argument znajdujący się z lewej strony jest różny od argumentu znajdującego się z prawej strony, w przeciwnym razie zwraca FALSE.

id2, nazwisko'Kowalski'

!=

Takie samo znaczenie jak .

id!=2, nazwisko!='Kowalski'


10

=10

IS NULL

Zwraca wartość TRUE, jeśli argument znajdujący się z lewej strony jest równy NULL, w przeciwnym razie zwraca FALSE.

adres IS NULL, id IS NULL

IS NOT NULL

Zwraca wartość TRUE, jeśli argument znajdujący się z lewej strony jest różny od NULL, w przeciwnym razie zwraca FALSE.

adres IS NOT NULL, id IS NOT NULL

R o z d z i a ł 4 . • P o b i e r a n i e d a n y c h z t ab e l

69

Tabela 4.1. Operatory relacyjne — ciąg dalszy Operator

Opis

Przykład

BETWEEN N AND M

Zwraca wartość TRUE, jeśli argument znajdujący się z lewej strony ma wartość z przedziału od N do M, w przeciwnym razie zwraca FALSE.

id BETWEEN 10 AND 20

NOT BETWEEN N AND M

Zwraca wartość TRUE, jeśli argument znajdujący się z lewej strony nie ma wartości z przedziału od N do M, w przeciwnym razie zwraca FALSE.

id NOT BETWEEN 10 AND 20

IN

Zwraca wartość TRUE, jeśli argument znajdujący się z lewej strony jest równy jednej z wartości wymienionych w nawiasie okrągłym za operatorem, w przeciwnym razie zwraca FALSE.

id IN(1, 3, 5), nazwisko IN('Kowalski', 'Nowak')

NOT IN

Zwraca wartość TRUE, jeśli argument znajdujący się z lewej strony nie jest równy jednej z wartości wymienionych w nawiasie okrągłym za operatorem, w przeciwnym razie zwraca FALSE.

id NOT IN(1, 3, 5), nazwisko NOT IN('Kowalski', 'Nowak')

to, że do przykładowego wzorca Jan% będą pasowały ciągi Jan, Janusz, Janek, Janowski itp., a do wzorca Warszaw_ będą pasowały ciągi Warszawa, Warszawy, Warszawo itp. Wyrażenie ze słowem NOT LIKE ma postać: wyrażenie NOT LIKE wzorzec

i działa odwrotnie do LIKE, czyli zwraca wartość TRUE, jeśli wyrażenie nie jest zgodne ze wzorcem, lub wartość FALSE, kiedy jest zgodne. Wykonajmy zatem serię ćwiczeń, które w praktyce pokażą, jak wykorzystywać warunki i wyrażenia w klauzuli WHERE. Operować będziemy na znanej nam z poprzednich przykładów tabeli pracownicy. Ć W I C Z E N I E

4.7

Kryteria dla pojedynczej kolumny

Wyświetl dane pracowników o nazwisku Kowalski.

70

SQL • Ćwiczenia praktyczne

Tabela 4.2. Operatory logiczne Operator

Opis

Przykład

AND

Logiczny iloczyn. Zwraca wartość TRUE wtedy i tylko wtedy, gdy oba argumenty mają wartość TRUE. W każdym innym przypadku zwraca wartość FALSE.

imie='Jan' AND Nazwisko= ´'Kowalski'

OR

Logiczna suma. Zwraca wartość TRUE, kiedy przynajmniej jeden z argumentów ma wartość TRUE. W przeciwnym przypadku zwraca wartość FALSE.

imie='Jan' OR imie='Andrzej'

XOR

Logiczna różnica symetryczna (logiczna alternatywa wykluczająca). Zwraca wartość TRUE, kiedy oba argumenty mają różne wartości logiczne, oraz wartość FALSE, kiedy oba argumenty mają takie same wartości logiczne.

kolumna1 XOR kolumna2, pole XOR 64

NOT

Logiczna negacja. Zmienia wartość argumentu na przeciwną. Jeśli wartością argumentu było TRUE, wynikiem będzie FALSE, a jeśli wartością argumentu było FALSE, wynikiem będzie TRUE.

NOT Aktywny

Interesują nas wiersze tabeli, które w kolumnie nazwisko zawierają wartość Kowalski, powinniśmy zatem zastosować warunek nazwisko= ´'Kowalski', a więc pełne zapytanie będzie miało postać: SELECT * FROM pracownicy WHERE Nazwisko='Kowalski';

Wynik jego działania został przedstawiony na rysunku 4.7.

Rysunek 4.7. Wyszukiwanie ze względu na nazwisko

R o z d z i a ł 4 . • P o b i e r a n i e d a n y c h z t ab e l

71

Ć W I C Z E N I E

4.8

Użycie operatora mniejszości

Wykorzystaj operator mniejszości do pobrania listy osób o zarobkach poniżej 1600 zł. Zapytanie SQL będzie miało postać: SELECT * FROM pracownicy WHERE placa < 1600;

Efekt jego działania został przedstawiony na rysunku 4.8.

Rysunek 4.8. Dane osób o zarobkach poniżej 1600 zł

Często do uzyskania pożądanych danych niezbędne jest użycie kilku warunków połączonych operatorem logicznym. Taki przykład przedstawia następne ćwiczenie. Ć W I C Z E N I E

4.9

Wykorzystanie operatorów relacyjnych i iloczynu logicznego

Użyj operatora logicznego AND do uzyskania listy osób o identyfikatorach z przedziału 3 – 6. Aby uzyskać w wyniku zapytania wartości pól z podanego zakresu, należy użyć dwóch warunków: id >= 3 i id = 3 AND id = początek_zakresu AND kolumna 1400 AND stanowisko ´'kierownik' AND pesel IS NOT NULL;

Efekt działania tego zapytania został zaprezentowany na rysunku 4.13.

Rysunek 4.13. Wyświetlenie danych spełniających kilka warunków

Niepowtarzalność wierszy Instrukcja SELECT może być również uzupełniona o klauzulę DISTINCT, która gwarantuje niepowtarzalność wierszy wynikowych, innymi słowy, eliminuje duplikaty z wyników zapytania. Załóżmy, że chcemy się dowiedzieć, jakie różne nazwiska noszą osoby, których dane są zapisane w tabeli pracownicy. Jeśli zastosujemy typową instrukcję: SELECT nazwisko FROM pracownicy ORDER BY nazwisko;

w wynikach znajdą się podwójne dane dla nazwisk Kowalski i Malinowski (rysunek 4.14). Nie o to nam jednak chodziło. Do uzyskania prawidłowych wyników niezbędne będzie więc użycie słowa DISTINCT. Ć W I C Z E N I E

4.16

Użycie klauzuli DISTINCT

Napisz zapytanie, które pobierze z tabeli pracownicy listę nazwisk. W wynikach nie mogą się pojawić duplikaty danych.

76

SQL • Ćwiczenia praktyczne

Rysunek 4.14. W wynikach zapytania pojawiły się duplikaty danych

Wyeliminowanie duplikatów danych uzyskamy, umieszczając za słowem SELECT słowo DISTINCT. Instrukcja będzie więc miała postać: SELECT DISTINCT nazwisko FROM pracownicy ORDER BY nazwisko;

Wynik jej działania został zaprezentowany na rysunku 4.15. Rysunek 4.15. Podwójne dane zostały usunięte z wyników zapytania

5 Modyfikacja i usuwanie danych Instrukcja UPDATE Do modyfikacji danych zawartych w tabelach służy instrukcja UPDATE. Ma ona ogólną postać: UPDATE nazwa_tabeli SET kolumna1=wartość1, kolumna2=wartość2, ..., kolumnaN=wartośćN [WHERE warunek]

co oznacza: zmień w tabeli nazwa_tabeli, w kolumnach spełniających warunek warunek, wartość kolumny kolumna1 na wartość1, kolumny kolumna2 na wartość2 itd. Warunek występujący po klauzuli WHERE jest przy tym opcjonalny, a jego pominięcie oznacza, że zmiany będą dokonane we wszystkich wierszach. W dalszych ćwiczeniach będziemy korzystać z tabeli pracownicy powstałej w rozdziale 4. Ć W I C Z E N I E

5.1

Zmiana wszystkich wartości we wskazanej kolumnie

Zmień zawartość kolumny pesel w tabeli pracownicy, tak aby wszystkie wiersze zawierały ciąg 01234567890.

78

SQL • Ćwiczenia praktyczne Wykonanie ćwiczenia zmodyfikuje zawartość WSZYSTKICH wierszy w tabeli pracownicy. Nie pojawi się przy tym żadne ostrzeżenie czy też pytanie o potwierdzenie chęci wykonania instrukcji. Przywrócenie oryginalnej zawartości tabeli będzie wymagało ponownego wprowadzenia danych.

Zmianę wszystkich wartości w kolumnie pesel zapewni nam instrukcja: UPDATE pracownicy SET pesel='01234567890';

Jeśli teraz wykonamy instrukcję SELECT pobierającą wszystkie dane z tabeli pracownicy, zobaczymy, że faktycznie wszystkie wiersze kolumny pesel zostały zmienione, tak jak jest to widoczne na rysunku 5.1.

Rysunek 5.1. Wszystkie dane w kolumnie pesel zostały zmodyfikowane

Modyfikacja danych w tabelach Ćwiczenie 5.1 pokazało, w jaki sposób zmienić dane we wszystkich wierszach wybranej kolumny. To jednak rzadko spotykany przypadek; najczęściej modyfikacji podlegają tylko rekordy spełniające zadane kryteria. Przykładowo, mogłoby się okazać, że Kacper Adamczyk ma przypisany błędny numer PESEL i należy go zmienić z 92341678903 na 12341678993. Ć W I C Z E N I E

5.2

Modyfikacja kolumny w wybranym wierszu tabeli

Zmień numer PESEL przypisany Kacprowi Adamczykowi w tabeli pracownicy.

Rozdział 5. • Modyfikacja i usuwanie danych

79

Aby wykonać ćwiczenie, należy zastosować instrukcję: UPDATE pracownicy SET pesel='12341678993' WHERE id=7;

Warunek id=7 został zastosowany, gdyż pole id jest kluczem głównym jednoznacznie identyfikującym każdy rekord. Co prawda można by wykonać również instrukcję: UPDATE pracownicy SET pesel='12341678993' WHERE pesel='92341678903';

ale nie jest to sposób polecany. W pierwszym bowiem przypadku zawsze mamy pewność, który rekord zostanie zmodyfikowany, w drugim — niestety, nie. Nie możemy mieć bowiem gwarancji, że w bazie nie znajduje się już PESEL 92341678903, gdyż ta kolumna nie gwarantuje unikalności każdego wpisu i nie powinna być stosowana jako wyróżnik modyfikowanego wiersza (jeden PESEL mógł być np. błędnie przypisany kilku osobom).

Nic nie stoi również na przeszkodzie, aby jednocześnie zmodyfikować kilka pól w danym wierszu. Moglibyśmy na przykład zmienić od razu imię, stanowisko i płacę danej osoby. Ć W I C Z E N I E

5.3

Modyfikacja kilku kolumn w jednym wierszu

Zmodyfikuj dane wybranej osoby tak, aby jednocześnie zostały zmienione: nazwisko, stanowisko oraz płaca. Aby wykonać to zadanie, możemy wykonać instrukcję: UPDATE pracownicy SET nazwisko='Andrzejewski', placa=3440.00, ´stanowisko='kierownik' WHERE id=8;

Tym samym Kamil Andrzejczak, pracujący na stanowisku asystenta, z płacą 1200 zł, stanie się Kamilem Andrzejewskim, pracującym na stanowisku kierowniczym, z płacą 3440 zł. O tym, że taka zmiana faktycznie nastąpiła, możemy się przekonać, wykonując instrukcję SELECT w postaci: SELECT * FROM pracownicy WHERE id=8;

co zostało również zobrazowane na rysunku 5.2.

80

SQL • Ćwiczenia praktyczne

Rysunek 5.2. Zmiana kilku danych w wybranym wierszu

Możliwości instrukcji UPDATE nie ograniczają się tylko do modyfikacji danych w jednym wierszu. To, które rekordy zostaną zmodyfikowane, zależy tylko od warunku klauzuli WHERE. Możemy więc np. zmienić nazwę stanowiska „sprzedawca” na „doradca klienta” we wszystkich wierszach kolumny stanowisko. Ć W I C Z E N I E

5.4

Modyfikacja kilku rekordów

W kolumnie stanowisko zmień wpisy o treści sprzedawca na doradca klienta. Aby wykonać tak przedstawione zadanie, należy posłużyć się instrukcją: UPDATE pracownicy SET stanowisko='doradca klienta' WHERE ´stanowisko='sprzedawca';

Pobranie danych z tabeli pokaże, że zmiana faktycznie została dokonana (rysunek 5.3).

Naraz można także modyfikować wiele kolumn w wielu wierszach. Jeśli więc firma przechowująca dane w tabeli pracownicy przejdzie kolejną reorganizację i doradcy klientów ponownie staną się sprzedawcami, i jednocześnie ich płace zostaną zrównane do 1400 zł, wszelkich niezbędnych zmian dokonamy również dzięki jednemu tylko zapytaniu.

Rozdział 5. • Modyfikacja i usuwanie danych

81

Rysunek 5.3. Nazwa stanowiska „sprzedawca” została zmieniona na „doradca klienta” Ć W I C Z E N I E

5.5

Modyfikacja kilku kolumn w wielu wierszach

Użyj pojedynczego zapytania do zmiany nazwy stanowiska „doradca klienta” na „sprzedawca” oraz płacy osób na tym stanowisku na 1400 zł. Niezbędne zapytanie ma postać: UPDATE pracownicy SET stanowisko='sprzedawca', placa=1400.00 WHERE ´stanowisko='doradca klienta'; Ć W I C Z E N I E

5.6

Modyfikacja kolumn z wartością NULL

Zmodyfikuj zawartość kolumny pesel w taki sposób, aby wiersze mające w niej wartość NULL miały przypisany ciąg znaków „nieznany”. UPDATE pracownicy SET pesel='nieznany' WHERE pesel IS NULL;

Usuwanie danych Do usuwania danych służy instrukcja DELETE o ogólnej postaci: DELETE FROM tabela [WHERE warunek]

82

SQL • Ćwiczenia praktyczne

Oznacza ona: usuń z tabeli tabela wszystkie wiersze spełniające warunek warunek. Jeśli warunek zostanie pominięty, zostaną usunięte wszystkie dane (podobnie jak w przypadku instrukcji UPDATE, gdzie pominięcie warunku powodowało modyfikację wszystkich wierszy tabeli). Ć W I C Z E N I E

5.7

Usunięcie wszystkich danych z tabeli

Usuń wszystkie dane z tabeli pracownicy. Aby usunąć wszystkie dane z tabeli pracownicy, należy wykonać instrukcję: DELETE FROM pracownicy;

Po jej wykonaniu tabela pracownicy nie będzie zawierała żadnych danych. Taką konstrukcję należy więc stosować z rozwagą, gdyż serwer nie wygeneruje żadnego ostrzeżenia czy dodatkowego pytania. Wpisanie powyższej konstrukcji i zatwierdzenie jej klawiszem Enter spowoduje natychmiastowe skasowanie danych!

Selektywne usuwanie danych zapewnia użycie klauzuli WHERE z odpowiednim wyrażeniem warunkowym, które konstruuje się na takich samych zasadach jak w przypadku instrukcji SELECT czy UPDATE. Zostanie to pokazane w kilku kolejnych ćwiczeniach. Ć W I C Z E N I E

5.8

Usunięcie wybranego wiersza

Usuń z tabeli pracownicy dowolnie wybrany wiersz. Aby usunąć z tabeli pracownicy dane osoby o identyfikatorze 5, zastosujemy instrukcję: DELETE FROM pracownicy WHERE id=5; Ć W I C Z E N I E

5.9

Jednoczesne usunięcie kilku wierszy

Usuń z tabeli pracownicy wiersze o identyfikatorach (wartości kolumny id): 1, 3, 7.

Rozdział 5. • Modyfikacja i usuwanie danych

83

Aby usunąć dane osób o identyfikatorach 1, 3 i 7, najprościej wykonać instrukcję: DELETE FROM pracownicy WHERE id IN (1, 3, 7);

Można również zastosować serię warunków połączonych operatorem OR: DELETE FROM pracownicy WHERE id=1 OR id=3 OR id=7; Ć W I C Z E N I E

5.10

Instrukcja DELETE i operator BETWEEN

Usuń z tabeli pracownicy wiersze o identyfikatorach z przedziału 4 – 8. Usunięcie z tabeli pracownicy wierszy, które mają w kolumnie id wartości z przedziału 4 – 8, uzyskamy, wykonując instrukcję: DELETE FROM pracownicy WHERE id BETWEEN 4 AND 8; Ć W I C Z E N I E

5.11

Usuwanie rekordów ze względu na ciąg znaków

Usuń z tabeli pracownicy dane wszystkich osób o nazwisku Kowalski. Aby usunąć z bazy dane pracowników o nazwisku Kowalski, zastosujemy instrukcję: DELETE FROM pracownicy WHERE nazwisko='Kowalski';

84

SQL • Ćwiczenia praktyczne

6 Złączenia Łączenie wyników zapytań Język SQL zawiera mechanizmy pozwalające na łączenie wyników kilku zapytań. Czynność taka może zostać wykonana za pomocą instrukcji UNION, INTERSECT, EXCEPT. Wszystkie one mają podobną postać: zapytanie1 UNION|INTERSECT|EXCEPT zapytanie2 ´[...UNION|INTERSECT|EXCEPT zapytanieN]

mogą więc służyć do łączenia dwóch lub większej liczby zapytań. Kilka następnych ćwiczeń pokaże różnice między tymi konstrukcjami. Do ich wykonania przygotujemy dwie nowe tablice. Będą one zawierały listę pracowników dwóch uczelni: UczelniaA i UczelniaB. Struktura obu tabel będzie się składała z następujących kolumn: T id — zawierającej identyfikator każdego rekordu; T imie — zawierającej imię pracownika; T nazwisko — zawierającej nazwisko pracownika; T pesel — zawierającej PESEL pracownika. Instrukcja tworząca tabelę UczelniaA przyjmie więc postać: CREATE TABLE UczelniaA ( id INTEGER PRIMARY KEY, imie VARCHAR(20) NOT NULL, nazwisko VARCHAR(30) NOT NULL, pesel CHAR(11) );

86

SQL • Ćwiczenia praktyczne

Instrukcja tworząca tabelę UczelniaB będzie praktycznie taka sama, jedyną różnicą będzie zmieniona nazwa. Do obu tabel należy następnie wprowadzić dane: INSERT INTO UczelniaA VALUES (1, 'Jan', 'Kowalski', '01234567890'); INSERT INTO UczelniaA VALUES (2, 'Adam', 'Nowak', '12345678901'); INSERT INTO UczelniaA VALUES (3, 'Anna', 'Wilk', '23456789012'); INSERT INTO UczelniaB VALUES (1, 'Janina', 'Wolska', '34567890123'); INSERT INTO UczelniaB VALUES (2, 'Beata', 'Nowak', '45678901234'); INSERT INTO UczelniaB VALUES (3, 'Jan', 'Kowalski', '01234567890');

Instrukcja UNION Instrukcja UNION ma ogólną postać: zapytanie1 UNION [ALL] zapytanie2 [...UNION zapytanieN]

i łączy wyniki zapytań zapytanie1 i zapytanie2. Oba zapytania powinny zwracać taką samą liczbę kolumn o takich samych typach. Jeżeli słowo ALL zostanie pominięte, z wyniku zostaną usunięte duplikaty danych, czyli instrukcja zachowa się tak, jakby zostało użyte słowo DISTINCT1. Jeśli słowo ALL zostanie użyte, w wynikach zostaną uwzględnione wszystkie wiersze z obu zapytań. Ć W I C Z E N I E

6.1

Wykorzystanie instrukcji UNION

Wykonaj zapytanie, które wyświetli dane (imię, nazwisko i PESEL) wszystkich pracowników uczelni UczelniaA i UczelniaB. Wyświetlenie danych pracowników obu uczelni uzyskamy dzięki instrukcji: SELECT imie, nazwisko, pesel FROM UczelniaA UNION SELECT imie, nazwisko, pesel FROM UczelniaB;

której efekt został zaprezentowany na rysunku 6.1. Jak widać, dane Jana Kowalskiego, będącego pracownikiem obu uczelni, pojawiły się w wynikach zapytania tylko raz, mimo że były elementem każdego z zapytań składowych typu SELECT.

1

Niektóre dialekty SQL (MySQL, PostgreSQL) pozwalają na jawne użycie słowa DISTINCT w instrukcji UNION.

Rozdział 6. • Złączenia

87

Rysunek 6.1. Efekt działania instrukcji UNION

Aby w wynikach zapytania znalazły się wszystkie wiersze z zapytań składowych, powinniśmy użyć dodatkowo słowa ALL. Zostanie to pokazane w następnym ćwiczeniu. Ć W I C Z E N I E

6.2

Instrukcja UNION i duplikaty danych

Użyj instrukcji UNION wyświetlającej dane (imię, nazwisko i PESEL) wszystkich pracowników uczelni UczelniaA i UczelniaB w taki sposób, aby w wynikach znalazły się wszystkie wiersze zapytań składowych. Po dodaniu słowa ALL uzyskamy następującą instrukcję: SELECT imie, nazwisko, pesel FROM UczelniaA UNION ALL SELECT imie, nazwisko, pesel FROM UczelniaB;

Efekt jej działania jest widoczny na rysunku 6.2. Rysunek 6.2. Efekt działania instrukcji UNION uwzględniającej duplikaty danych

Instrukcja INTERSECT Instrukcja INTERSECT ma ogólną postać: zapytanie1 INTERSECT [ALL] zapytanie2 [...UNION zapytanieN]

88

SQL • Ćwiczenia praktyczne

i łączy wyniki zapytań zapytanie1 i zapytanie22. Oba zapytania powinny zwracać taką samą liczbę kolumn o takich samych typach. Jeżeli słowo ALL zostanie pominięte, z wyniku zostaną usunięte duplikaty danych, czyli instrukcja zachowa się tak, jakby zostało użyte słowo DISTINCT. Jeśli słowo ALL zostanie użyte, w wynikach zostaną uwzględnione również duplikaty danych. Jej konstrukcja jest zatem bardzo podobna do UNION, jednak znaczenie jest inne. Otóż jej wynikiem są takie wiersze, które występują jednocześnie w zapytaniach zapytanie1 i zapytanie2. Zatem korzystając z INTERSECT, w prosty sposób dowiemy się, którzy pracownicy jednocześnie pracują na uczelniach UczelniaA i UczelniaB. Ć W I C Z E N I E

6.3

Użycie instrukcji INTERSECT

Napisz zapytanie stwierdzające, którzy pracownicy pracują zarówno na uczelni UczelniaA, jak i na uczelni UczelniaB. Aby wykonać zadanie, należy użyć polecenia: SELECT imie, nazwisko, pesel FROM UczelniaA INTERSECT SELECT imie, nazwisko, pesel FROM UczelniaB;

W wyniku tego zapytania na ekranie pojawi się tylko jeden wiersz danych, tak jak widać to na rysunku 6.3. Rysunek 6.3. Wynik użycia zapytania INTERSECT

Instrukcja EXCEPT Instrukcja EXCEPT ma ogólną postać3: zapytanie1 EXCEPT [ALL] zapytanie2 [...UNION zapytanieN]

2

Instrukcja INTERSECT nie jest obsługiwana przez bazę MySQL.

3

Instrukcja EXCEPT nie jest obsługiwana przez bazy MySQL i Oracle.

Rozdział 6. • Złączenia

89

i dotyczą jej te same uwagi, które znalazły się w opisie instrukcji UNION i INTERSECT. W wyniku jej działania zwrócone zostaną takie wiersze, które znajdują się w wynikach pierwszego zapytania i jednocześnie nie znajdują się w wynikach drugiego zapytania. Możemy więc na przykład dowiedzieć się, którzy z pracowników uczelni UczelniaA nie pracują jednocześnie ma uczelni UczelniaB. W przypadku bazy Oracle zamiast EXCEPT należy użyć instrukcji MINUS. Ć W I C Z E N I E

6.4

Wykorzystanie instrukcji EXCEPT

Napisz zapytanie pozwalające stwierdzić, którzy z pracowników uczelni UczelniaA nie pracują jednocześnie na uczelni UczelniaB. Aby znaleźć szukane dane, wykonamy zapytanie o postaci: SELECT imie, nazwisko, pesel FROM UczelniaA EXCEPT SELECT imie, nazwisko, pesel FROM UczelniaB;

lub (w przypadku bazy Oracle): SELECT imie, nazwisko, pesel FROM UczelniaA MINUS SELECT imie, nazwisko, pesel FROM UczelniaB;

którego wynik został zaprezentowany na rysunku 6.4. Rysunek 6.4. Wynik działania instrukcji EXCEPT

Pobieranie danych z wielu tabel W rozdziale 4. została przedstawiona instrukcja SELECT służąca do pobierania danych; stosowaliśmy ją wtedy do pobierania danych z pojedynczych tabel. Wiemy jednak, że dane w relacyjnych bazach są

90

SQL • Ćwiczenia praktyczne

przechowywane w wielu powiązanych ze sobą tabelach. Instrukcja SELECT działa również i w takiej sytuacji. Jeśli chcemy pobrać dane z wielu tabel, jej ogólna postać to: SELECT kolumna1, kolumna2, ..., kolumnaN FROM tabela, tabela2, ..., tabelaN [WHERE warunki] [ORDER BY kolumna1, kolumna2, ..., kolumnaN [ASC | DEC]]

Następne ćwiczenia pokażą, jak stosować taką konstrukcję; potrzebny nam będzie nowy zestaw tabel z danymi. Tabele i zawarte w nich dane będą oparte na przykładzie z rozdziału 4. Jak pamiętamy, mieliśmy wtedy do czynienia z tabelą pracownicy, zawierającą dane o pracownikach, ich płacach i stanowiskach. Zostało też wtedy wspomniane, że w rzeczywistości nazwy stanowisk powinny być umieszczone w osobnej tabeli. Poprawmy więc tamten przykład w taki właśnie sposób. Przygotujemy dwie tabele: pracownicy i stanowiska. Pierwsza z nich będzie zawierała następujące kolumny: T id — typu INTEGER, będącą kluczem głównym i zawierającą identyfikator każdego wiersza; T imie — typu VARCHAR(20), z atrybutem NOT NULL, zawierającą imię pracownika; T nazwisko — typu VARCHAR(30), z atrybutem NOT NULL, zawierającą nazwisko pracownika; T placa — typu DECIMAL(7, 2), z atrybutem NOT NULL, zawierającą miesięczne wynagrodzenie pracownika; T stanowisko — typu INTEGER, będącą kluczem obcym pochodzącym z tabeli stanowiska; T pesel — typu CHAR(11), zawierającą PESEL pracownika. Druga będzie zawierała kolumny: T id — typu INTEGER, będącą kluczem głównym i zawierającą identyfikator każdego wiersza; T nazwa — typu VARCHAR(20), z atrybutem NOT NULL, zawierającą nazwę stanowiska. Odpowiednie instrukcje SQL będą więc miały postać: CREATE TABLE pracownicy ( id INTEGER PRIMARY KEY,

Rozdział 6. • Złączenia

91

imie VARCHAR(20) NOT NULL, nazwisko VARCHAR(30) NOT NULL, placa DECIMAL(7, 2) NOT NULL, stanowisko_id INTEGER, pesel CHAR(11) ); CREATE TABLE stanowiska ( id INTEGER PRIMARY KEY, nazwa VARCHAR(20) NOT NULL );

Tak przygotowane tabele wypełnimy w taki sposób, aby opisywały takie same dane jak w przykładzie z rozdziału 4. Należy zatem wykonać następującą serię instrukcji INSERT INTO: INSERT INTO pracownicy ´'12345678901'); INSERT INTO pracownicy ´'92345678901'); INSERT INTO pracownicy ´'72345678901'); INSERT INTO pracownicy ´'92345678909'); INSERT INTO pracownicy ´NULL); INSERT INTO pracownicy ´NULL); INSERT INTO pracownicy ´'92341678903'); INSERT INTO pracownicy ´NULL); INSERT INTO pracownicy ´'02343678913'); INSERT INTO pracownicy ´'32349678913');

VALUES (1, 'Adam', 'Kowalski', 1624.50, 1,

INSERT INSERT INSERT INSERT INSERT INSERT

VALUES(1, VALUES(2, VALUES(3, VALUES(4, VALUES(5, VALUES(6,

INTO INTO INTO INTO INTO INTO

stanowiska stanowiska stanowiska stanowiska stanowiska stanowiska

VALUES (2, 'Adam', 'Nowak', 3760.00, 2, VALUES (3, 'Andrzej', 'Kowalski', 4200.00, 2, VALUES (4, 'Arkadiusz', 'Malinowski', 1600.00, 4, VALUES (5, 'Andrzej', 'Malinowski', 1450.00, 3, VALUES (6, 'Krzysztof', 'Nowicki', 1300.00, 3, VALUES (7, 'Kacper', 'Adamczyk', 1610.50, 6, VALUES (8, 'Kamil', 'Andrzejczak', 1200.00, 5, VALUES (9, 'Krzysztof', 'Arkuszewski', 1500, 1, VALUES (10, 'Kamil', 'Borowski', 1600.00, 3, 'magazynier'); 'kierownik'); 'sprzedawca'); 'kierowca'); 'asystent'); 'serwisant');

Skoro dane są gotowe, przystąpmy do ćwiczeń pobierających dane z obu tabel.

92

SQL • Ćwiczenia praktyczne

Ć W I C Z E N I E

6.5

Pobranie danych z dwóch tabel

Wykonaj instrukcję SQL pobierającą wszystkie dane z tabel pracownicy i stanowiska. Zapytanie będzie miało postać: SELECT imie, nazwisko, nazwa, placa FROM pracownicy, stanowiska;

Fragment generowanego przez nie wyniku został przedstawiony na rysunku 6.5. Okazało się ono niezbyt praktyczne, gdyż nie uwzględniliśmy żadnego warunku. Serwer wykonał więc tak zwane złączenie tabel, w którym każdy wiersz z tabeli pracownicy został połączony z każdym wierszem tabeli stanowiska (tzw. iloczyn kartezjański obu tabel). Rysunek 6.5. Fragment iloczynu kartezjańskiego dwóch tabel

Zdecydowanie bardziej praktyczna byłaby w tej sytuacji instrukcja, która wiązałaby pracowników z zajmowanymi przez nich stanowiskami. W związku z tym do zapytania należy dodać warunek wiążący kolumnę id z tabeli stanowiska z kolumną stanowisko_id z tabeli pracownicy. Ć W I C Z E N I E

6.6

Powiązanie łączonych tabel

Wykonaj instrukcję SQL pobierającą dane z tabel pracownicy i stanowiska, taką, aby pracownikom były przypisane odpowiadające im stanowiska. Powiązanie następuje oczywiście przez pola: id z tabeli stanowiska oraz stanowisko_id z tabeli pracownicy. Warunek będzie miał więc postać: pracownicy.stanowisko_id = stanowiska.id

Rozdział 6. • Złączenia

93

a pełna instrukcja SELECT: SELECT imie, nazwisko, nazwa, placa FROM pracownicy, stanowiska WHERE pracownicy.stanowisko_id = stanowiska.id;

Wynik takiego zapytania został przedstawiony na rysunku 6.6. Rysunek 6.6. Wynik zapytania łączącego pracowników i zajmowane przez nich stanowiska

Rzecz jasna, wyniki takiego zapytania mogą być również posortowane względem dowolnie wybranej kolumny. Należy w tym celu dodać odpowiednią klauzulę ORDER BY. Ć W I C Z E N I E

6.7

Sortowanie wyników z połączonych tabel

Wykonaj instrukcję SQL pobierającą dane z tabel pracownicy i stanowiska, taką, aby pracownikom były przypisane odpowiadające im stanowiska. Wyniki posortuj względem kolumny placa w porządku malejącym. Aby posortować względem kolumny placa wyniki zapytania pobierającego wskazane dane, należy użyć instrukcji: SELECT imie, nazwisko, nazwa, placa FROM pracownicy, stanowiska WHERE pracownicy.stanowisko_id = stanowiska.id ORDER BY placa DESC;

94

SQL • Ćwiczenia praktyczne

Złączenia Jeśli pobieramy dane z więcej niż jednej tabeli, w rzeczywistości wykonujemy tak zwane złączenie. W przykładach z poprzedniej sekcji nie wykorzystywaliśmy do tego celu żadnych specjalnych struktur, jednak w SQL istnieją instrukcje pozwalające na formalne wykonanie złączenia tabel, co więcej — istnieje wiele typów złączeń. Najbardziej znane to: T CROSS JOIN, T INNER JOIN, T LEFT OUTER JOIN, T RIGHT OUTER JOIN, T FULL OUTER JOIN.

Złączenie typu CROSS JOIN Złączenie CROSS JOIN to złączenie krzyżowe, którego ogólna postać jest następująca: SELECT kolumna1, kolumna2, ..., kolumnaN FROM tabela1 CROSS JOIN tabela2

Wykonuje ono iloczyn kartezjański łączonych tabel, czyli łączy każdy wiersz tabeli tabela1 z każdym wierszem tabeli tabela2. Jest to zatem odpowiednik instrukcji: SELECT kolumna1, kolumna2, ..., kolumnaN FROM tabela, tabela2 Ć W I C Z E N I E

6.8

Złączenie krzyżowe

Wykonaj złączenie krzyżowe tabel pracownicy i stanowiska. Instrukcja wykonująca złączenie krzyżowe tabel pracownicy i stanowiska ma postać: SELECT * FROM pracownicy CROSS JOIN stanowiska;

Rozdział 6. • Złączenia

95

Efekt jej działania będzie podobny do przedstawionego na rysunku 6.5, z tym że uwzględnione będą wszystkie kolumny, a nie tylko cztery wybrane.

Złączenie typu INNER JOIN Konstrukcja typu INNER JOIN to złączenie warunkowe4 o postaci: SELECT kolumna1, kolumna2, ..., kolumnaN FROM tabela1 [INNER] JOIN tabela2 ON wyrażenie_warunkowe

Słowo INNER w większości implementacji jest opcjonalne. W przypadku tego złączenia w wyniku pojawią się tylko te wiersze z tabel tabela1 i tabela2, które spełniają warunki wymienione po klauzuli ON. Jest to więc odpowiednik instrukcji: SELECT kolumna1, kolumna2, ..., kolumnaN FROM tabela, tabela2, ..., tabelaN WHERE warunki Ć W I C Z E N I E

6.9

Wykorzystanie złączenia INNER JOIN

Wykonaj złączenie typu INNER JOIN pobierające dane z kolumn: imie, nazwisko, placa z tabeli pracownicy i nazwa z tabeli stanowiska, takie, aby każdemu pracownikowi było przypisane odpowiadające mu stanowisko. Wykonanie zadania zapewni instrukcja: SELECT imie, nazwisko, nazwa, placa FROM pracownicy INNER JOIN stanowiska ON pracownicy.stanowisko_id = stanowiska.id;

której efekt działania będzie taki sam jak przedstawiony na rysunku 6.6 przy ćwiczeniu 6.6.

4

W bazie MySQL złączenia INNER JOIN i CROSS JOIN, choć jest to niezgodne ze standardem, są równoważne.

96

SQL • Ćwiczenia praktyczne

Złączenie typu LEFT OUTER JOIN Złączenie typu LEFT OUTER JOIN pozwala na uwzględnienie w wyniku danych, które nie mają swoich odpowiedników w złączanych tabelach. Oznacza to, że jeżeli w pierwszej tabeli znajdują się wiersze, które nie mają (ze względu na warunek złączenia) swoich odpowiedników w drugiej tabeli, i tak zostaną uwzględnione w złączeniu, a w miejsce pustych kolumn zostaną wstawione wartości NULL. Konstrukcja typu LEFT OUTER JOIN ma postać: SELECT kolumna1, kolumna2, ..., kolumnaN FROM tabela1 LEFT [OUTER] JOIN tabela2 ON wyrażenie_warunkowe

Słowo OUTER w większości implementacji jest opcjonalne. Aby sprawdzić, jak działa w praktyce, dodamy do tabeli pracownicy nowy wiersz. Posłuży do tego instrukcja: INSERT INTO pracownicy VALUES (11, 'Robert', 'Nowicki', 1600.00, 7, ´'02309670913');

Jak można zauważyć, osobie tej został przypisany identyfikator stanowiska 7, który nie ma swojego odpowiednika w tabeli stanowiska. Jest to oczywiście błąd polegający na niespójności danych w bazie, ale taka sytuacja może się zdarzyć w rzeczywistości. W takiej sytuacji zapytanie typu INNER JOIN takie jak w ćwiczeniu 6.9 nie ujawni istnienia osoby o identyfikatorze 11, ponieważ w tabeli stanowiska nie ma stanowiska, któremu przypisano identyfikator 7. Tak więc w tabeli wynikowej nie pojawiają się żadne dodatkowe wiersze. Zupełnie inaczej będzie w przypadku złączenia LEFT OUTER JOIN. Ć W I C Z E N I E

6.10

Użycie złączenia LEFT OUTER JOIN

Wyświetl dane pracowników wraz z zajmowanymi przez nich stanowiskami, tak aby w wyniku pojawiły się również osoby, którym przypisano błędny numer stanowiska. Po zastosowaniu instrukcji: SELECT imie, nazwisko, nazwa, placa FROM pracownicy LEFT OUTER JOIN stanowiska ON pracownicy.stanowisko_id = stanowiska.id;

Rozdział 6. • Złączenia

97

w wyniku pojawią się dane wszystkich pracowników. W kolumnach dotyczących stanowiska, dla których nie ma odpowiedników w tabeli stanowiska, pojawi się wartość NULL (tak jak to widać na rysunku 6.7) lub pusty ciąg znaków (w zależności od tego, z którego systemu baz danych korzystamy i w jaki sposób wyświetla on kolumny zawierające wartość NULL). W ten sposób otrzymamy dane wszystkich pracowników, nawet jeśli dane są niespójne. Rysunek 6.7. Wynik zapytania typu LEFT OUTER JOIN

Złączenie typu RIGHT OUTER JOIN Złączenie typu RIGHT OUTER JOIN5 jest przeciwieństwem LEFT OUTER JOIN. W tabeli wynikowej uwzględnia ono wiersze z drugiej tabeli, które nie mają swoich odpowiedników w pierwszej. Ma ono postać: SELECT kolumna1, kolumna2, ..., kolumnaN FROM tabela1 RIGHT [OUTER] JOIN tabela2 ON wyrażenie_warunkowe

Słowo OUTER, tak jak w poprzednio opisywanych przypadkach, zazwyczaj jest opcjonalne. Aby sprawdzić działanie tego typu złączenia, dodamy do tabeli stanowiska stanowisko, do którego nie będzie przypisany żaden pracownik. Użyjemy w tym celu instrukcji: INSERT INTO stanowiska VALUES(8, 'informatyk');

Nie będzie to błędem — taka sytuacja może przecież mieć miejsce.

5

Złączenie RIGHT OUTER JOIN nie jest obsługiwane przez bazę SQLite (w trakcie przygotowywania materiałów do książki najnowszą dostępną wersją była 3.7).

98

SQL • Ćwiczenia praktyczne

Ć W I C Z E N I E

6.11

Zapytanie typu RIGHT OUTER JOIN

Użyj zapytania typu RIGHT OUTER JOIN do złączenia tabel pracownicy i stanowiska. Zapytanie będzie miało postać: SELECT imie, nazwisko, nazwa, placa FROM pracownicy RIGHT OUTER JOIN stanowiska ON pracownicy.stanowisko_id = stanowiska.id;

A jego wykonanie spowoduje uzyskanie wyniku widocznego na rysunku 6.8. Rysunek 6.8. Wynik złączenia typu RIGHT OUTER JOIN

Łatwo jednak zaważyć, że RIGHT OUTER JOIN i LEFT OUTER JOIN to funkcjonalne odpowiedniki. Wystarczy zmienić kolejność tabel, aby uzyskać zapytanie pierwszego bądź drugiego typu. Ć W I C Z E N I E

6.12

Zmiana typu zapytania

Użyj złączenia typu LEFT OUTER JOIN do uzyskania rezultatów takich jak w ćwiczeniu 6.11. Aby uzyskać wynik z ćwiczenia 6.11 przy użyciu złączenia LEFT OUTER JOIN, należy użyć instrukcji: SELECT imie, nazwisko, nazwa, placa FROM stanowiska LEFT OUTER JOIN pracownicy ON pracownicy.stanowisko_id = stanowiska.id;

Rozdział 6. • Złączenia

99

Złączenie typu FULL OUTER JOIN Złączenie FULL OUTER JOIN6 jest kombinacją LEFT OUTER JOIN i RIGHT OUTER JOIN. Ma ogólną postać: SELECT kolumna1, kolumna2, ..., kolumnaN FROM tabela1 FULL [OUTER] JOIN tabela2 ON wyrażenie_warunkowe

i uwzględnia w wynikach zapytania zarówno takie wiersze z pierwszej tabeli, które nie mają swoich odpowiedników w drugiej, jak i takie wiersze z drugiej tabeli, które nie mają swoich odpowiedników w pierwszej. Ć W I C Z E N I E

6.13

Zapytanie FULL OUTER JOIN

Użyj zapytania typu FULL OUTER JOIN do złączenia tabel pracownicy i stanowiska. Złączenie typu FULL OUTER JOIN będzie miało postać: SELECT imie, nazwisko, nazwa, placa FROM pracownicy FULL OUTER JOIN stanowiska ON pracownicy.stanowisko_id = stanowiska.id;

W wyniku takiego zapytania otrzymamy dane przedstawione na rysunku 6.9. Rysunek 6.9. Wynik zapytania FULL OUTER JOIN dla tabel pracownicy i stanowiska

6

Złączenie tego typu nie jest obsługiwane przez bazy MySQL i SQLite.

100

SQL • Ćwiczenia praktyczne

Złączenia i klauzula WHERE W złączeniach można również stosować klauzulę WHERE. W wyniku takiego zapytania najpierw zostanie wykonane złączenie spełniające warunki występujące po klauzuli ON, następnie z otrzymanych wierszy zostaną wyeliminowane te niespełniające warunków występujących po klauzuli WHERE, a serwer bazy danych zwróci ostateczny wynik. Przykładowo możemy zmodyfikować zapytanie z ćwiczenia 6.10 w taki sposób, aby znaleźć pracowników, dla których brakuje odpowiadającego wpisu w tabeli stanowiska. Ć W I C Z E N I E

6.14

Złączenie z dodatkowym warunkiem

Napisz zapytanie wykorzystujące złączenie LEFT OUTER JOIN do wyświetlenia danych pracowników, dla których w kolumnie stanowisko_id znajdują się wartości niemające odpowiedników w tabeli stanowiska. Wyszukanie wszystkich pracowników, którzy mają przypisany nieprawidłowy identyfikator stanowiska, uzyskamy dzięki zapytaniu: SELECT imie, nazwisko, nazwa, pesel, placa FROM pracownicy LEFT OUTER JOIN stanowiska ON pracownicy.stanowisko_id = stanowiska.id WHERE nazwa IS NULL;

którego efekt działania został zaprezentowany na rysunku 6.10. Rysunek 6.10. Działanie złączenia LEFT OUTER JOIN z dodatkowym warunkiem

Aliasy tabel i rozróżnianie nazw kolumn Gdy korzystamy ze złączeń, może się zdarzyć, że w dwóch tabelach będą kolumny o takich samych nazwach. Wtedy jeśli mają być użyte w złączeniu, konieczne jest rozróżnienie, z których tabel pochodzą. W tym celu stosuje się konstrukcje w postaci: nazwa_tabeli.nazwa_kolumny

Rozdział 6. • Złączenia

101

Załóżmy, że mamy dwie tabele utworzone za pomocą następujących instrukcji: CREATE TABLE produkty ( id INTEGER PRIMARY KEY NOT NULL, kategoria_id INTEGER NOT NULL, nazwa VARCHAR(50) ); CREATE TABLE kategorie ( id INTEGER PRIMARY KEY NOT NULL, nazwa VARCHAR(50) );

Można w nich zatem przechowywać informacje o produktach i kategoriach, do których należą. Powiązanie produktu z kategorią następuje przez pole kategoria_id w tabeli produkty. Trzeba jednak zauważyć, że w obu tabelach są kolumny o nazwach id i nazwa. Te kolumny raz określają identyfikator i nazwę produktu, a drugi raz — identyfikator i nazwę kategorii. To oznacza, że złączenie, w którym nazwy kolumn zostaną użyte bez uwzględnienia nazw tabel, nie będzie możliwe. Przy pobieraniu danych trzeba więc określać, z której tabeli pochodzą interesujące nas kolumny. Sprawdźmy to. Najpierw wprowadzimy do tabel dane trzech przykładowych towarów należących do dwóch kategorii. Można w tym celu użyć instrukcji: INSERT INSERT INSERT INSERT INSERT

INTO INTO INTO INTO INTO

kategorie VALUES(1, 'agd'); kategorie VALUES(2, 'rtv'); produkty VALUES(1, 1, 'pralka'); produkty VALUES(2, 2, 'telewizor'); produkty VALUES(3, 2, 'kamera');

Ć W I C Z E N I E

6.15

Złączenie i kolumny o takich samych nazwach

Pobierz dane z tabel produkty i kategorie, tak by zostały wyświetlone nazwy i identyfikatory produktów wraz z nazwami i identyfikatorami kategorii, do których należą. Nie używaj symbolu *, tylko określ nazwy poszczególnych kolumn. Zapytanie pobierające dane ze wszystkich kolumn złączanych tabel będzie wyglądało tak:

102

SQL • Ćwiczenia praktyczne SELECT produkty.id, produkty.nazwa, kategorie.id, kategorie.nazwa FROM produkty INNER JOIN kategorie ON produkty.kategoria_id = kategorie.id;

W ten sposób uzyskamy listę produktów wraz z ich kategoriami. W wyniku, który dla bazy MySQL został przedstawiony na rysunku 6.11, nie będzie wtedy jednak rozróżnienia nazw kolumn, tzn. dwukrotnie otrzymamy kolumnę o nazwie id i dwukrotnie kolumnę o nazwie nazwa. Można to poprawić, stosując aliasy nazw kolumn. Rysunek 6.11. Niejednoznaczne nazwy kolumn w wynikach zapytania Ć W I C Z E N I E

6.16

Złączenie i aliasy nazw kolumn

Zmodyfikuj zapytanie z ćwiczenia 6.15 tak, aby nazwy kolumn w wynikach były jednoznaczne. Aby wykonać ćwiczenie, wystarczy dodać aliasy kolumn, modyfikując zapytanie do postaci: SELECT produkty.id AS "Identyfikator produktu", produkty.nazwa AS ´"Nazwa produktu", kategorie.id AS "Identyfikator kategorii", kategorie.nazwa AS ´"Nazwa kategorii" FROM produkty INNER JOIN kategorie ON produkty.kategoria_id = kategorie.id;

Aliasy mogą być używane nie tylko w przypadku kolumn, ale także tabel. Jest to szczególnie wygodne w przypadku złączeń z dużą liczbą warunków. Wtedy zamiast stosować za każdym razem pełną nazwę tabeli, w zapytaniu używa się tylko aliasów, często tylko jedno- lub dwuliterowych. Aby zastosować alias, należy w klauzuli FROM użyć instrukcji: nazwa_tabeli [AS] alias

Rozdział 6. • Złączenia

103

Słowo AS w większości implementacji jest opcjonalne, natomiast w przypadku bazy Oracle — niedopuszczalne. Alias dla Oracle powinien więc wyglądać tylko tak: nazwa_tabeli alias

Sposób skorzystania z aliasów nazw tabel został przedstawiony w ćwiczeniu 6.17. Ć W I C Z E N I E

6.17

Użycie aliasów nazw tabel

Zmodyfikuj kod z ćwiczenia 6.16 tak, aby w zapytaniu zamiast nazw tabel zostały użyte ich aliasy. Zmodyfikowane zapytanie będzie miało postać: SELECT p.id AS "Identyfikator produktu", p.nazwa AS "Nazwa produktu", k.id AS "Identyfikator kategorii", k.nazwa AS "Nazwa kategorii" FROM produkty AS p INNER JOIN kategorie AS k ON p.kategoria_id = k.id;

lub: SELECT p.id AS "Identyfikator produktu", p.nazwa AS "Nazwa produktu", k.id AS "Identyfikator kategorii", k.nazwa AS "Nazwa kategorii" FROM produkty p INNER JOIN kategorie k ON p.kategoria_id = k.id;

Po użyciu konstrukcji produkty AS p i kategorie AS k (MySQL, PostgreSQL, SQLite, MS SQL) bądź produkty p i kategorie k (MySQL, PostgreSQL, SQLite, MS SQL, Oracle), nazwy p i k mogą być używane jako nazwy tabel.

104

SQL • Ćwiczenia praktyczne

7 Funkcje agregujące Poszukiwana przez nas informacja często musi być wyliczona na podstawie danych znajdujących się w wielu wierszach tabeli. Może nas interesować np. średnia wartość zamówienia, liczba klientów zamieszkałych na danym obszarze czy liczba towarów spełniających zadane kryteria. Uzyskiwanie takich informacji umożliwiają funkcje agregujące. Ich zestaw zależy od danej implementacji bazy danych. Typowe funkcje zostały zebrane w tabeli 7.1. Tabela 7.1. Typowe funkcje agregujące Funkcja

Opis

Dostępność

AVG(wyrażenie)

Oblicza wartość średnią.

MySQL, PostgreSQL, MS SQL, Oracle, SQLite

BIT_AND(wyrażenie)

Oblicza iloczyn bitowy.

MySQL, PostgreSQL

BIT_OR(wyrażenie)

Oblicza sumę bitową.

MySQL, PostgreSQL

BIT_XOR(wyrażenie)

Oblicza bitową różnicę symetryczną.

MySQL

BOOL_AND

Oblicza iloczyn logiczny.

PostgreSQL

BOOL_OR

Oblicza sumę logiczną.

PostgreSQL

COUNT(wyrażenie)

Oblicza liczbę wierszy zwróconych przez zapytanie.

MySQL, PostgreSQL, MS SQL, Oracle, SQLite

106

SQL • Ćwiczenia praktyczne

Tabela 7.1. Typowe funkcje agregujące — ciąg dalszy Funkcja

Opis

Dostępność

GROUP_CONCAT ´(wyrażenie)

Tworzy ciąg będący połączeniem zwróconych przez zapytanie wartości różnych od NULL.

MySQL, SQLite

MIN(wyrażenie)

Podaje wartość minimalną zwróconą przez zapytanie.

MySQL, PostgreSQL, MS SQL, Oracle, SQLite

MAX(wyrażenie)

Podaje wartość maksymalną zwróconą przez zapytanie.

MySQL, PostgreSQL, MS SQL, Oracle, SQLite

STD(wyrażenie)

Oblicza odchylenie standardowe z wartości zwróconych przez zapytanie. Nie należy do standardu SQL.

MySQL

STDDEV(wyrażenie)

Alias dla STD. Oblicza odchylenie standardowe z wartości zwróconych przez zapytanie.

MySQL, PostgreSQL, Oracle

STDDEV_POP ´(wyrażenie)

Oblicza odchylenie standardowe z wartości zwróconych przez zapytanie.

MySQL, Oracle

SUM(wyrażenie)

Oblicza sumę wartości zwróconych przez zapytanie.

MySQL, PostgreSQL, MS SQL, Oracle, SQLite

VAR_POP(wyrażenie)

Oblicza wariancję standardową z wartości zwróconych przez zapytanie.

MySQL, Oracle

VARIANCE(wyrażenie)

Oblicza wariancję standardową z wartości zwróconych przez zapytanie. Nie należy do standardu SQL.

MySQL, PostgreSQL, Oracle

Następne ćwiczenia pokażą, jak wykorzystywać funkcje agregujące w praktyce. Będziemy w nich wykorzystywać dane z tabeli pracownicy z rozdziału 6.

Rozdział 7. • Funkcje agregujące

107

Ć W I C Z E N I E

7.1

Liczba wierszy występujących w tabeli

Wykonaj instrukcję obliczającą liczbę wszystkich wierszy znajdujących się w tabeli pracownicy. Aby otrzymać informację o liczbie wszystkich wierszy w tabeli pracownicy, zastosujemy instrukcję: SELECT COUNT(*) FROM pracownicy;

Znak * jako argument funkcji COUNT oznacza po prostu wszystkie wiersze, a zatem wynikiem będzie wartość 10, tak jak to widać na rysunku 7.1. Widzimy też wyraźnie, że w wyniku tego zapytania zwrócona została tabela zawierająca kolumnę o nazwie COUNT(*) i jedno pole w tej kolumnie, zawierające wartość 10. Rysunek 7.1. Wynik działania instrukcji obliczającej liczbę pracowników

Jeżeli konieczne jest zliczenie niepustych wierszy z jednej kolumny, zamiast * należy użyć nazwy tej kolumny. Można zatem w prosty sposób policzyć np. liczbę pracowników, dla których znany jest numer PESEL. Wtedy w miejsce * wpiszemy nazwę kolumny pesel. Ć W I C Z E N I E

7.2

Liczba wierszy w wybranej kolumnie

Sprawdź, jaka jest liczba niepustych wierszy w kolumnie pesel tabeli pracownicy. W celu wykonania zadania należy użyć instrukcji: SELECT COUNT(pesel) FROM pracownicy;

W ćwiczeniu 7.1 zliczaniu podlegały wszystkie wiersze znajdujące się we wskazanej tabeli. Nic nie stoi jednak na przeszkodzie, aby dodać do zapytania warunek w klauzuli WHERE. Można więc np. sprawdzić, ilu pracowników pracuje na stanowisku sprzedawcy.

108

SQL • Ćwiczenia praktyczne

Ć W I C Z E N I E

7.3

Liczba wierszy spełniających określone kryterium

Wykonaj zapytanie obliczające, ilu pracowników jest zatrudnionych na stanowisku sprzedawcy. Ponieważ stanowisku sprzedawcy w tabeli stanowiska jest przypisany identyfikator 3, będziemy zliczać tylko takie wiersze, które w kolumnie stanowisko_id w tabeli pracownicy mają zapisaną wartość 3. Warunek będzie miał zatem postać: WHERE stanowisko_id = 3

a całe zapytanie: SELECT COUNT(*) FROM pracownicy WHERE stanowisko_id = 3;

Wynik jego działania został przedstawiony na rysunku 7.2.

Rysunek 7.2. Liczba pracowników na stanowisku sprzedawcy

Nazwa kolumny będącej wynikiem zapytania, w którym została użyta funkcja agregująca, ma taką samą postać jak wywołanie tej funkcji (w poprzednich ćwiczeniach było to COUNT(*) lub COUNT(pesel)). Można ją jednak zmienić, stosując znaną nam już konstrukcję ze słowem AS. Ć W I C Z E N I E

7.4

Zmiana nazwy kolumny zawierającej efekt działania funkcji

Zmień nazwę kolumny tabeli wynikowej z ćwiczenia 7.3 na liczba sprzedawców.

Jeśli kolumna wynikowa zapytania obliczającego liczbę osób na stanowisku sprzedawcy miałaby się nazywać liczba sprzedawców, należy zastosować zapytanie:

Rozdział 7. • Funkcje agregujące

109

SELECT COUNT(*) AS "liczba sprzedawców" FROM pracownicy WHERE ´stanowisko_id = 3;

którego efekt działania jest widoczny na rysunku 7.3. Rysunek 7.3. Zmieniona nazwa kolumny w wynikach zapytania

Funkcje agregujące pozwalają również uzyskać inne informacje statystyczne, takie jak wartość minimalna, maksymalna czy średnia dla danej kolumny. Ć W I C Z E N I E

7.5

Obliczenie średniej wartości danych w kolumnie

Wykonaj zapytanie obliczające średnie wynagrodzenie pracowników. Obliczenie średniej wartości wynagrodzenia zapisanego w kolumnie placa tabeli pracownicy zapewni nam zapytanie: SELECT AVG(placa) AS "Średnie wynagrodzenie" FROM pracownicy;

Efektem jego działania będzie tabela z kolumną o nazwie Średnie wyna ´grodzenie, zawierająca jedno pole z obliczoną średnią arytmetyczną wszystkich wynagrodzeń, tak jak to widać na rysunku 7.4.

Rysunek 7.4. Obliczenie średniej wartości wynagrodzeń Ć W I C Z E N I E

7.6

Pobranie z kolumny wartości maksymalnej

Wykonaj zapytanie stwierdzające, jakie jest maksymalne wynagrodzenie zapisane w kolumnie placa.

110

SQL • Ćwiczenia praktyczne

Obliczenie maksymalnego wynagrodzenia jest możliwe za pomocą zapytania: SELECT MAX(placa) AS "Maksymalne wynagrodzenie" FROM pracownicy; Ć W I C Z E N I E

7.7

Pobranie z kolumny wartości minimalnej

Wykonaj zapytanie obliczające minimalne wynagrodzenie pracowników. Wynagrodzenie minimalne znajdziemy dzięki instrukcji: SELECT MIN(placa) AS "Minimalne wynagrodzenie" FROM pracownicy; Ć W I C Z E N I E

7.8

Użycie wielu funkcji w jednym zapytaniu

Wykonaj zapytanie obliczające średnie, maksymalne i minimalne wynagrodzenie pracowników. Aby jednocześnie uzyskać wartość średnią, minimalną oraz maksymalną dla wynagrodzeń zapisanych w kolumnie placa w tabeli pracownicy, należy użyć zapytania w postaci: SELECT AVG(placa) AS "Średnie wynagrodzenie", MAX(placa) AS "Maksymalne wynagrodzenie", MIN(placa) AS "Minimalne wynagrodzenie" FROM pracownicy;

Efekt jego działania został przedstawiony na rysunku 7.5.

Rysunek 7.5. Efekt działania zapytania z ćwiczenia 7.7

Rozdział 7. • Funkcje agregujące

111

Ć W I C Z E N I E

7.9

Użycie wielu funkcji z ograniczeniem zakresu danych

Wykonaj zapytanie obliczające średnie, maksymalne i minimalne wynagrodzenie pracowników na stanowisku sprzedawcy (identyfikator stanowiska = 3). Aby pobrać średnią, minimalną i maksymalną płacę dla pracowników zajmujących stanowisko sprzedawcy (czyli stanowisko o identyfikatorze 3), do zapytania z ćwiczenia 7.8. należy dodać warunek sekcji WHERE: SELECT AVG(placa) AS "Średnie wynagrodzenie", MAX(placa) AS "Maksymalne wynagrodzenie", MIN(placa) AS "Minimalne wynagrodzenie" FROM pracownicy WHERE stanowisko_id=3;

Efekt działania takiej instrukcji jest widoczny na rysunku 7.6.

Rysunek 7.6. Statystyki wynagrodzeń dla stanowiska sprzedawcy Ć W I C Z E N I E

7.10

Użycie funkcji sumującej

Wykonaj zapytanie podające sumaryczną wartość wynagrodzeń pracowników. Żądaną informację uzyskamy dzięki zapytaniu: SELECT SUM(placa) AS "Suma płac" FROM pracownicy;

Dostępna w MySQL funkcja grupująca GROUP_CONCAT pozwala na połączenie wartości występujących w agregowanej kolumnie i zaprezentowanie ich w postaci łańcucha znakowego. Jej ogólne wywołanie ma postać:

112

SQL • Ćwiczenia praktyczne GROUP_CONCAT( [DISTINCT] wyrażenie [ORDER BY kolumna [ASC|DESC] kolumna] [SEPARATOR separator] )

Jeżeli zostanie użyta klauzula DISTINCT, z wyników zapytania zostaną usunięte duplikaty danych; użycie klauzuli ORDER BY umożliwia sortowanie wyników (ma ona taką samą postać jak ta przedstawiona podczas omawiania instrukcji SELECT w rozdziale 4.). Klauzula separator pozwala natomiast na określenie łańcucha znaków, który zostanie użyty jako separator danych. Domyślnie separatorem jest przecinek. Spróbujmy więc pobrać listę wszystkich nazwisk występujących w tabeli pracownicy. Ć W I C Z E N I E

7.11

Łączenie łańcuchów znakowych

Wykonaj zapytanie pobierające listę wszystkich nazwisk z tabeli pracownicy w postaci ciągu znaków. Pobranie wszystkich nazwisk z tabeli pracownicy zapewni nam zapytanie: SELECT GROUP_CONCAT(nazwisko) AS "Nazwiska" FROM pracownicy;

Efektem działania zapytania z ćwiczenia 7.11 będzie ciąg nazwisk oddzielonych od siebie znakami przecinka. Nazwiska nie będą jednak posortowane; ponadto w wynikach znajdą się wszystkie nazwiska (również te powtarzające się). Jeśli więc w tabeli wielokrotnie wystąpi nazwisko Kowalski, to wielokrotnie pojawi się ono w wynikach. Możemy tego uniknąć, stosując dodatkowe klauzule. Ć W I C Z E N I E

7.12

Dodatkowe klauzule funkcji GROUP_CONCAT

Wykonaj zapytanie pobierające listę wszystkich nazwisk z tabeli pracownicy w postaci ciągu znaków. Nazwiska nie mogą się powtarzać, mają być posortowane w porządku alfabetycznym rosnącym oraz oddzielone od siebie znakami średnika.

Rozdział 7. • Funkcje agregujące

113

Niepowtarzalność nazwisk zapewni klauzula DISTINCT, sortowanie — klauzula ORDER BY, a znak separatora — klauzula SEPARATOR. Całe zapytanie będzie więc miało postać: SELECT GROUP_CONCAT(DISTINCT nazwisko ORDER BY nazwisko SEPARATOR ";") AS "Nazwiska" FROM pracownicy;

Efekt jego działania został zaprezentowany na rysunku 7.7.

Rysunek 7.7. Posortowana lista niepowtarzających się nazwisk

Funkcja GROUP_CONCAT występuje także w SQLite, ma tam jednak bardziej uproszczoną postać. Regulować można tylko znak separatora, przekazując go w postaci opcjonalnego drugiego argumentu (pierwszym argumentem powinna być nazwa kolumny; domyślnie znakiem separatora jest przecinek). Użycie konstrukcji GROUP_CONCAT(nazwisko)

spowoduje zatem pobranie listy nazwisk oddzielonych od siebie znakiem przecinka (dokładnie tak samo jak w ćwiczeniu 7.11), a użycie konstrukcji: GROUP_CONCAT(nazwisko, ";")

pobranie listy nazwisk oddzielonych od siebie znakiem średnika. Ć W I C Z E N I E

7.13

Funkcja GROUP_CONCAT w SQLite

W bazie SQLite pobierz listę nazwisk z tabeli pracownicy tak, aby były oddzielone od siebie znakami średnika. Aby wykonać ćwiczenie, należy użyć zapytania w postaci: SELECT GROUP_CONCAT(nazwisko, ";") AS "Nazwiska" FROM pracownicy;

114

SQL • Ćwiczenia praktyczne

8 Grupowanie danych W rozdziale 7. zostały przedstawione funkcje agregujące, pozwalające na zebranie danych dotyczących wielu wierszy tabeli. Dane te, jak np. średnia płaca, były jednak zawsze pobierane ze względu na jedno określone kryterium. Mogły to być zarówno wszystkie wiersze tabeli, jak i wiersze spełniające pewien warunek (np. średnia płaca na stanowisku kierowcy). Czasem jednak niezbędne jest bardziej złożone grupowanie danych. Przykładowo, niezbędna może być informacja, jaka jest średnia płaca na poszczególnych stanowiskach. Można by oczywiście wykonać w tym celu serię zapytań, takich jak przedstawione w poprzednim rozdziale, nie byłoby to jednak wygodne. Dlatego też język SQL udostępnia klauzulę grupującą GROUP BY. Schemat zapytania ma wtedy postać: SELECT kolumna1[, kolumna2, ..., kolumnaN] FROM tabela1[, tabela2, ..., tabelaN] WHERE warunki GROUP BY kolumna1[, kolumna2, ..., kolumnaN]

Wyniki takiego zapytania zostaną zgrupowane względem kolumn wskazanych za słowami GROUP BY. W pierwszym ćwiczeniu użyjemy tabeli pracownicy z rozdziału 4., w której nazwa stanowiska była zapisywana razem z pozostałymi danymi. Dowiedzmy się, jaka jest średnia płaca na danym stanowisku.

116

SQL • Ćwiczenia praktyczne

Ć W I C Z E N I E

8.1

Proste użycie klauzuli GROUP BY

Wykonaj zapytanie operujące na tabeli pracownicy z rozdziału 4., pozwalające stwierdzić, jaka jest średnia płaca na poszczególnych stanowiskach. Aby dowiedzieć się, jaka jest średnia płaca na poszczególnych stanowiskach, należy użyć funkcji agregującej w stosunku do kolumny placa (AVG(placa)) oraz klauzuli grupującej w stosunku do kolumny stanowisko (GROUP BY stanowisko). Pełne zapytanie będzie więc miało postać: SELECT stanowisko, AVG(placa) AS "Średnia płaca" FROM pracownicy GROUP BY stanowisko;

Efekt jego działania został zaprezentowany na rysunku 8.1. Widać wyraźnie, że spełniło ono swoje zadanie. Rysunek 8.1. Średnia płaca dla każdego stanowiska

Użycie grupowania nie wyklucza korzystania ze złączeń tabel. W takim bowiem wypadku najpierw jest wykonywane złączenie, a dopiero na wynikach tego złączenia — grupowanie danych. Następne ćwiczenia wykonamy więc na tabelach pracownicy i stanowiska powstałych w rozdziale 6. Ć W I C Z E N I E

8.2

Użycie klauzuli GROUP BY razem ze złączeniem tabel

Wykonaj zapytanie podające, jaka jest średnia płaca na poszczególnych stanowiskach, pobierające dane z tabel pracownicy i stanowiska z rozdziału 6.

Rozdział 8. • Grupowanie danych

117

Jeśli dane mają być pobierane z tabel pracownicy i stanowiska, do zapytania musi zostać dodany warunek wiążący obie tabele, którym w tym przypadku jest: pracownicy.stanowisko_id = stanowiska.id

Dodatkowo, ponieważ nazwa stanowiska znajduje się w kolumnie nazwa tabeli stanowiska, można zmienić nazwę tej kolumny na Stanowisko. Wtedy zapytanie będzie miało postać: SELECT nazwa AS "Stanowisko", AVG(placa) AS "Średnia płaca" FROM pracownicy, stanowiska WHERE pracownicy.stanowisko_id = stanowiska.id GROUP BY nazwa;

A efekt jego działania będzie taki sam jak przedstawiony na rysunku 8.1.

W przypadku grupowania danych można również stosować sortowanie. Do zapytania należy wtedy dodać klauzulę ORDER BY. Wyniki uzyskane w ćwiczeniach 8.1 i 8.2 możemy więc posortować np. względem kolumny Średnia płaca w porządku malejącym. Ć W I C Z E N I E

8.3

Użycie klauzuli GROUP BY z sortowaniem danych

Zmodyfikuj zapytanie z ćwiczenia 8.2 w taki sposób, aby jego wyniki były posortowane względem średniej płacy w porządku malejącym. Sortowanie względem średniej płacy w porządku malejącym uzyskamy, dodając do zapytania klauzulę ORDER BY o postaci: ORDER BY "Średnia płaca" DESC

Pełne zapytanie będzie w związku z tym wyglądało tak1: SELECT nazwa AS "Stanowisko", AVG(placa) AS "Średnia płaca" FROM pracownicy, stanowiska WHERE pracownicy.stanowisko_id = stanowiska.id GROUP BY nazwa ORDER BY "Średnia płaca" DESC;

Wynik jego działania został przedstawiony na rysunku 8.2.

1

Aby uzyskać efekt sortowania w bazie MySQL, zamiast cudzysłowów należy użyć lewych apostrofów `. Dotyczy to również dalszych ćwiczeń.

118

SQL • Ćwiczenia praktyczne

Rysunek 8.2. Wyniki grupowania sortowane względem wybranej kolumny

W jednym zapytaniu grupującym można też użyć kilku funkcji agregujących. Spróbujmy zatem za jednym razem dowiedzieć się, jaka jest maksymalna, minimalna i średnia płaca dla każdej z grup pracowników. Ć W I C Z E N I E

8.4

Grupowanie z użyciem kilku funkcji agregujących

Napisz zapytanie stwierdzające, jaka jest minimalna, maksymalna i średnia płaca dla każdego stanowiska pracy. Wyniki posortuj względem kolumny stanowisko w porządku rosnącym. Aby wykonać to zadanie, należy posłużyć się zapytaniem SQL o postaci: SELECT nazwa AS "Stanowisko", MIN(placa) AS "Minimalna płaca", MAX(placa) AS "Maksymalna płaca", AVG(placa) AS "Średnia płaca" FROM pracownicy, stanowiska WHERE pracownicy.stanowisko_id = stanowiska.id GROUP BY nazwa ORDER BY "Stanowisko" ASC;

W efekcie otrzymamy dane przedstawione na rysunku 8.3.

Rysunek 8.3. Wyniki grupowania sortowane względem wybranej kolumny

Rozdział 8. • Grupowanie danych

119

Ć W I C Z E N I E

8.5

Grupowanie ze zliczaniem wierszy

Wykonaj zapytanie obliczające liczbę pracowników na każdym ze stanowisk. Aby dowiedzieć się, ilu pracowników pracuje na każdym ze stanowisk, powinniśmy użyć funkcji COUNT oraz grupowania według stanowiska: SELECT nazwa AS stanowisko, COUNT(*) AS "Liczba pracowników" FROM pracownicy, stanowiska WHERE pracownicy.stanowisko_id = stanowiska.id GROUP BY nazwa ORDER BY "Liczba pracowników";

Podczas grupowania mogą zostać również użyte dodatkowe warunki, pozwalające wyeliminować wybrane dane z wyników zapytania. Wykonajmy ćwiczenie obrazujące to zagadnienie. Jeśli obejrzymy zawartość tabeli pracownicy, zauważymy, że niektórym osobom nie został przypisany numer PESEL. Wykluczmy więc z wyników grupowania takie wiersze, które w kolumnie pesel mają zapisaną wartość NULL. Ć W I C Z E N I E

8.6

Eliminacja wybranych wierszy

Sprawdź, jaka jest średnia płaca na każdym stanowisku. Z wyników obliczeń wyklucz pracowników, którym nie przypisano numeru PESEL. Aby z wyników zapytania wykluczyć pracowników, którym nie przypisano numeru PESEL, należy zastosować dodatkowy warunek o postaci: pracownicy.pesel IS NOT NULL

umieszczony w klauzuli WHERE: SELECT nazwa AS stanowisko, AVG(placa) AS "Średnia płaca" FROM pracownicy, stanowiska WHERE pracownicy.stanowisko_id = stanowiska.id AND pracownicy.pesel ´IS NOT NULL GROUP BY nazwa ORDER BY "Średnia płaca";

120

SQL • Ćwiczenia praktyczne

Grupowanie może się również odbywać względem większej liczby kolumn. Aby to lepiej zobrazować, wprowadzimy kilka zmian do tabeli pracownicy: UPDATE pracownicy SET nazwisko='Nowak' WHERE id=3; UPDATE pracownicy SET nazwisko='Kowalski' WHERE id=9; UPDATE pracownicy SET nazwisko='Malinowski' WHERE id=6;

Postarajmy się teraz dowiedzieć, ile osób o danym nazwisku pracuje na danym stanowisku. Ć W I C Z E N I E

8.7

Grupowanie względem kilku kolumn

Wykonaj zapytanie obliczające liczbę osób o takich samych nazwiskach pracujących na każdym ze stanowisk. Jeśli chcemy się dowiedzieć, ile osób o danym nazwisku pracuje na każdym ze stanowisk, niezbędne będzie jednoczesne grupowanie względem dwóch kolumn: stanowisko oraz nazwisko, a także użycie funkcji COUNT. Zapytanie będzie miało postać: SELECT nazwa AS stanowisko, nazwisko, COUNT(*) AS "Liczba pracowników" FROM pracownicy, stanowiska WHERE pracownicy.stanowisko_id = stanowiska.id GROUP BY nazwa, nazwisko ORDER BY stanowisko, nazwisko;

Efekt jego działania jest widoczny na rysunku 8.4. Rysunek 8.4. Efekt grupowania względem dwóch kolumn

Zapytanie grupujące dane może również zawierać dodatkowe warunki grupowania. Umożliwia to klauzula HAVING. Takie zapytanie ma ogólną postać:

Rozdział 8. • Grupowanie danych

121

SELECT kolumna1[, kolumna2, ..., kolumnaN] FROM tabela1[, tabela2, ..., tabelaN] WHERE warunki_where GROUP BY kolumna1[, kolumna2, ..., kolumnaN] HAVING warunki_having

gdzie warunki_having określają, jakie warunki muszą spełniać dane będące wynikiem działania funkcji agregujących, aby zostały zawarte w wynikach. Przykładowo, gdybyśmy chcieli uzyskać dane o stanowiskach, na których średnia płaca jest większa niż 1400 zł, powinniśmy zastosować klauzulę HAVING o postaci: HAVING AVG(placa) > 1400 Ć W I C Z E N I E

8.8

Użycie klauzuli HAVING

Wyświetl listę stanowisk, dla których średnia płaca jest większa niż 1400 zł. Aby wykonać ćwiczenie, należy wykorzystać funkcję agregującą AVG oraz klauzulę grupującą względem stanowiska, podobnie jak miało to miejsce we wcześniej wykonywanych zadaniach, uzupełniając całość o odpowiednią klauzulę HAVING: SELECT nazwa AS stanowisko, AVG(placa) AS "Średnia płaca" FROM pracownicy, stanowiska WHERE pracownicy.stanowisko_id = stanowiska.id GROUP BY nazwa HAVING AVG(placa) > 1400 ORDER BY "Średnia płaca" DESC;

W efekcie uzyskamy dane jak na rysunku 8.5. Rysunek 8.5. Wynik zapytania korzystającego z klauzuli HAVING

122

SQL • Ćwiczenia praktyczne

9 Podzapytania Podzapytania (z ang. subqueries) to mechanizm, który pozwala wykorzystać wyniki jednego zapytania w innym zapytaniu. Nazywane są również zapytaniami zagnieżdżonymi (z ang. nested queries) lub zapytaniami wewnętrznymi (z ang. inner queries). Można je stosować w zapytaniach typu SELECT, INSERT, UPDATE i DELETE, gdzie najczęściej są wprowadzane do klauzul WHERE bądź FROM. Możemy wyróżnić dwa główne typy podzapytań: T proste (z ang. simple), T skorelowane (z ang. correlated). Do ćwiczeń pokazujących działanie tych konstrukcji potrzebne będą tabele opisujące towary, klientów i zamówienia o następujących danych: klienci +----+-----------+------------+ | id | imie | nazwisko | +----+-----------+------------+ | 1 | Jan | Kowalski | | 2 | Andrzej | Nowak | | 3 | Janusz | Malinowski | | 4 | Adam | Kowalski | | 5 | Krzysztof | Nowicki | +----+-----------+------------+

towary +----+-------------+-------+-------+ | id | nazwa | grupa | cena | +----+-------------+-------+-------+ | 1 | Śruby | 1 | 2.00 | | 2 | Nakrętki | 1 | 3.00 | | 3 | Kątowniki | 2 | 8.00 | | 4 | Płaskowniki | 2 | 9.00 | | 5 | Gwoździe | 1 | 1.00 | | 6 | Panele | 3 | 15.00 | | 7 | Wkręty | 1 | 4.00 | | 8 | Deski | 3 | 12.00 | | 9 | Płyty | 3 | 19.00 | +----+-------------+-------+-------+

124

SQL • Ćwiczenia praktyczne zamowienia +----+-----------+----------+------------+---------+ | id | klient_id | towar_id | data | wartosc | +----------------+----------+------------+---------+ | 1 | 1 | 2 | 2012-01-01 | 12.44 | | 2 | 1 | 4 | 2012-01-01 | 10.22 | | 3 | 1 | 2 | 2012-02-12 | 15.88 | | 4 | 2 | 1 | 2012-01-01 | 22.35 | | 5 | 2 | 1 | 2012-02-12 | 28.00 | | 6 | 2 | 4 | 2012-03-01 | 2.28 | | 7 | 3 | 1 | 2012-02-11 | 18.48 | | 8 | 3 | 4 | 2012-01-01 | 12.44 | | 9 | 4 | 1 | 2012-03-11 | 15.26 | | 10 | 5 | 4 | 2012-03-02 | 6.11 | +----------------+----------+------------+---------+

Podzapytania w klauzuli FROM Wynikiem zapytania typu SELECT jest tablica zawierająca określone kolumny i dane. Skoro jest to tablica, to możliwe jest wykonanie na niej kolejnego zapytania typu SELECT. W ten sposób otrzymamy zapytanie złożone o ogólnej postaci: SELECT kolumny_zapytania FROM (SELECT kolumny_podzapytania FROM tablice WHERE warunki_podzapytania ) [AS] nazwa WHERE warunki_zapytania

Należy zwrócić uwagę na argument nazwa, którym jest nazwa tablicy wynikowej zwróconej przez podzapytanie. Może być ona dowolna, jednak w bazach MySQL, PostgreSQL i MS SQL zawsze musi być obecna. Słowo AS jest opcjonalne i nie trzeba go używać, przy czym w bazie Oracle musi być pominięte. W przypadku Oracle i SQLite można również pominąć cały alias (co jest niedopuszczalne w pozostałych omawianych systemach).

Rozdział 9. • Podzapytania

125

Ć W I C Z E N I E

9.1

Podzapytanie w klauzuli FROM

Wykonaj złączenie tabel klienci i zamowienia wiążące klientów oraz ich zamówienia i użyj tego złączenia jako podzapytania zapytania pobierającego dane zamówień o wartości większej niż 15 zł. Wyniki posortuj względem wartości zamówień w porządku rosnącym. Instrukcja SQL będzie miała postać (MySQL, PostgreSQL, MS SQL, SQLite): SELECT imie, nazwisko, wartosc, data FROM (SELECT imie, nazwisko, wartosc, data FROM klienci, zamowienia WHERE klienci.id = zamowienia.klient_id) AS klienci_zamowienia WHERE wartosc > 15 ORDER BY wartosc;

lub (Oracle, SQlite): SELECT imie, nazwisko, wartosc, data FROM (SELECT imie, nazwisko, wartosc, data FROM klienci, zamowienia WHERE klienci.id = zamowienia.klient_id) WHERE wartosc > 15 ORDER BY wartosc;

W podzapytaniu zostało wykonane złączenie tablic zamowienia i klienci i z tak powstałej tablicy zostały wybrane wiersze, w których wartość zamówienia była większa niż 15. Wyniki zostały posortowane względem wartości zamówienia. Efektem działania całej instrukcji jest tablica o kolumnach imie, nazwisko, wartosc i data, taka jak przedstawiona na rysunku 9.1. W przypadku PostgreSQL i SQLite w podzapytaniu nie trzeba wymieniać nazw kolumn i zamiast nich można użyć znaku *. Rysunek 9.1. Efekt działania instrukcji z ćwiczenia 9.1

126

SQL • Ćwiczenia praktyczne

Podzapytania klauzuli WHERE Podzapytania proste Podzapytanie proste to takie, które jest wykonywane raz, a jego wynik jest następnie używany w zapytaniu głównym. Wykorzystajmy podzapytanie proste do stwierdzenia, na który towar zostało złożone zamówienie o najniższej wartości. Ć W I C Z E N I E

9.2

Użycie podzapytania prostego

Wykonaj instrukcję pozwalającą stwierdzić, jaki jest identyfikator towaru, na który złożone zostało zamówienie o najniższej wartości. Aby wykonać ćwiczenie, należy użyć instrukcji: SELECT towar_id FROM zamowienia WHERE wartosc = (SELECT MIN(wartosc) FROM zamowienia);

Efekt jej działania jest widoczny na rysunku 9.2. Działanie jest następujące: najpierw wykonywane jest zapytanie: SELECT MIN(wartosc) FROM zamowienia

a jego wynik (wartość 2.59) jest wstawiany do warunku klauzuli WHERE. Następnie wykonywane jest zapytanie zewnętrzne, które tym samym przyjmie postać: SELECT TowarId FROM Zamowienia WHERE Wartosc = 2.59

Rysunek 9.2. Efekt wykorzystania podzapytania prostego

Rozdział 9. • Podzapytania

127

Ostatecznie wynikiem jest tabela zawierająca identyfikator towaru o wartości 4.

Gdybyśmy chcieli dodatkowo otrzymać również nazwę towaru, na który zostało złożone zamówienie o najniższej wartości, musielibyśmy złączyć tabele zamowienia i towary. Podzapytanie musiałoby być w takim wypadku jednym z elementów warunku złożonego w klauzuli WHERE. Ć W I C Z E N I E

9.3

Podzapytanie proste i złączenie tabel

Wykonaj instrukcję pozwalającą stwierdzić, jaka jest nazwa i identyfikator towaru, na który złożone zostało zamówienie o najniższej wartości. Instrukcja realizująca tak postawione zadanie będzie miała postać: SELECT towar_id, nazwa FROM zamowienia, towary WHERE wartosc = (SELECT MIN(wartosc) FROM zamowienia) AND towary.id = zamowienia.towar_id;

Efekt jej działania został przedstawiony na rysunku 9.3. Rysunek 9.3. Podzapytanie jako element warunku złożonego w klauzuli WHERE

Efektem działania podzapytania nie musi być pojedyncza wartość, jak w powyższych przykładach — może to być również zbiór wartości. Możemy zatem otrzymać np. szczegóły zamówień danego towaru, które miały największą wartość. Podzapytanie pozwoli nam tu wybrać zamówienia o największych wartościach z podziałem na towary, a zapytanie zewnętrzne złączy tabele zamowienia, towary oraz klienci.

128

SQL • Ćwiczenia praktyczne

Ć W I C Z E N I E

9.4

Podzapytanie zwracające wiele wartości

Napisz zapytanie zwracające szczegóły zamówień o największej wartości dla każdego z towarów. Instrukcja SQL będzie miała postać: SELECT zamowienia.id, imie, nazwisko, nazwa, data, wartosc FROM zamowienia, towary, klienci WHERE wartosc IN (SELECT MAX(wartosc) FROM zamowienia GROUP BY towar_id) AND towary.id = zamowienia.towar_id AND klienci.id = zamowienia.klient_id ORDER BY wartosc;

Efektem jej działania będzie tabela widoczna na rysunku 9.4.

Rysunek 9.4. Podzapytanie wybierające wiele wartości

Podzapytania skorelowane W przedstawionych dotychczas przykładach podzapytania wykonywane były tylko raz, podczas wywołania zapytania głównego, czyli najpierw było wywoływane podzapytanie, jego wynik był wstawiany do zapytania głównego, a następnie było wykonywane zapytanie główne. Zupełnie inaczej wygląda to w przypadku podzapytań skorelowanych. Są one wykonywane dla każdej wartości analizowanej przez zapytanie główne. Ich cechą charakterystyczną jest odwołanie w zapytaniu skorelowanym do kolumny tabeli występującej w zapytaniu głównym. Jeśli w obu zapytaniach występuje ta sama tabela, niezbędne jest użycie aliasu. Zapytania skorelowane pozwolą nam odpowiedzieć na bardziej skomplikowane pytania, na przykład: które towary mają cenę większą niż średnia cena w grupie, do której należą?

Rozdział 9. • Podzapytania

129

Ć W I C Z E N I E

9.5

Użycie zapytania skorelowanego

Wykonaj zapytanie pozwalające stwierdzić, które towary mają cenę wyższą niż średnia cena w grupie, do której należą. Aby wykonać ćwiczenie, dla każdego towaru trzeba wyliczyć średnią cenę grupy i porównać ją z ceną z kolumny cena. Nie wystarczy zatem prosta instrukcja warunkowa; trzeba będzie wykorzystać podzapytanie skorelowane. Cała instrukcja będzie miała postać: SELECT id, nazwa, cena, grupa FROM towary WHERE cena > (SELECT AVG(cena) FROM towary AS towary2 WHERE towary.grupa=towary2.grupa ) ORDER BY cena;

Dla każdego towaru analizowanego w zapytaniu głównym, w zapytaniu skorelowanym zostanie wyliczona średnia cena grupy towarów, do której ten towar należy. Ponieważ oba zapytania operują na tej samej tabeli, w zapytaniu skorelowanym tabela towary została przemianowana na towary2 (uwaga: w przypadku bazy Oracle należy pominąć słowo AS), tak aby warunek w klauzuli WHERE miał sens (inaczej miałby on postać towary.grupa=towary.grupa i zapytanie nie mogłoby być poprawnie wykonane). Wynik działania powyższego zapytania złożonego został przedstawiony na rysunku 9.5. Rysunek 9.5. Wynik działania zapytania zawierającego podzapytanie skorelowane

130

SQL • Ćwiczenia praktyczne

Podzapytania w instrukcjach aktualizujących dane Zastosowanie podzapytań nie ogranicza się do instrukcji SELECT. Mogą być one również wykorzystywane w przypadku instrukcji aktualizujących dane, jak INSERT, UPDATE czy DELETE. W wykonaniu ćwiczeń ilustrujących to zagadnienie pomoże nam dodatkowa tabela, która będzie przechowywała imiona i nazwiska klientów oraz wartości złożonych przez nich zamówień. Powstanie ona dzięki instrukcji: CREATE TABLE zam_tymczas ( imie VARCHAR(20), nazwisko VARCHAR(30), wartosc DECIMAL(7, 2) );

Chcielibyśmy teraz wypełnić ją danymi pobieranymi z tabel przedstawionych na początku rozdziału. Przygotowywanie osobnych instrukcji INSERT dla każdego wiersza byłoby z pewnością bardzo czasochłonne, a jeśli w tabelach klienci i zamowienia byłoby dużo danych — praktycznie niewykonalne. Dlatego w takiej sytuacji najlepiej posłużyć się instrukcją typu INSERT wykorzystującą odpowiednio przygotowane podzapytanie. Schemat takiego zapytania to: INSERT INTO tabela (kolumny) SELECT kolumny_podzapytania FROM tabele_podzapytania WHERE warunki_podzapytania ;

W bazach innych niż Oracle takie podzapytanie może też zostać ujęte w nawias okrągły: INSERT INTO tabela (kolumny) ( SELECT kolumny_podzapytania FROM tabele_podzapytania WHERE warunki_podzapytania );

Rozdział 9. • Podzapytania

131

Ć W I C Z E N I E

9.6

Proste podzapytanie aktualizujące dane

Użyj podzapytania typu SELECT do wypełniania tabeli zam_tymczas danymi poszczególnych zamówień (imię i nazwisko klienta oraz wartość zamówienia). Aby wykonać ćwiczenie, należy użyć instrukcji: INSERT INTO zam_tymczas(imie, nazwisko, wartosc) SELECT imie, nazwisko, wartosc FROM klienci, zamowienia WHERE klienci.id = zamowienia.klient_id ;

Po jej wykonaniu w tablicy zam_tymczas znajdą się następujące dane: +-----------+------------+---------+ | imie | nazwisko | wartosc | +-----------+------------+---------+ | Andrzej | Nowak | 2.59 | | Krzysztof | Nowicki | 6.25 | | Janusz | Malinowski | 12.44 | | Jan | Kowalski | 12.44 | | Jan | Kowalski | 14.88 | | Adam | Kowalski | 15.26 | | Jan | Kowalski | 15.90 | | Janusz | Malinowski | 18.00 | | Andrzej | Nowak | 22.35 | | Andrzej | Nowak | 28.00 | +-----------+------------+---------+

Nic nie stoi na przeszkodzie, aby w podzapytaniu użyć grupowania oraz funkcji agregujących i w tablicy zam_tymczas umieścić imiona i nazwiska klientów wraz z sumaryczną wartością złożonych przez nich zamówień. Ć W I C Z E N I E

9.7

Grupowanie w podzapytaniach aktualizujących

Napisz instrukcję wypełniającą tabelę zam_tymczas danymi o sumarycznej wartości zamówień dla każdego klienta. Niezbędne będzie użycie funkcji agregującej SUM (dla kolumny wartosc) oraz klauzuli grupującej GROUP BY. Właściwa instrukcja będzie miała postać:

132

SQL • Ćwiczenia praktyczne INSERT INTO zam_tymczas(imie, nazwisko, wartosc) SELECT imie, nazwisko, SUM(wartosc) AS wartosc FROM klienci, zamowienia WHERE klienci.id = zamowienia.klient_id GROUP BY klient_id, imie, nazwisko ;

A po jej wykonaniu (o ile dane z poprzedniego ćwiczenia zostały usunięte) w tabeli zam_tymczas pojawią się następujące wpisy: +-----------+------------+---------+ | imie | nazwisko | wartosc | +-----------+------------+---------+ | Krzysztof | Nowicki | 6.25 | | Adam | Kowalski | 15.26 | | Janusz | Malinowski | 30.44 | | Jan | Kowalski | 43.22 | | Andrzej | Nowak | 52.94 | +-----------+------------+---------+

Załóżmy teraz, że chcemy z pierwszej wersji tabeli zam_tymczas usunąć wszystkie wpisy, które w kolumnie wartosc mają wartość mniejszą niż średnia arytmetyczna ze wszystkich zamówień z tabeli zamowienia. Ć W I C Z E N I E

9.8

Użycie podzapytania w instrukcji DELETE

Wykonaj instrukcję usuwającą z tabeli zam_tymczas powstałej w ćwiczeniu 9.6 takie zamówienia, których wartość jest mniejsza niż średnia wartość zamówień w tabeli zamowienia. Podzapytanie znajdzie się w klauzuli WHERE instrukcji DELETE. Całe zapytanie będzie miało postać: DELETE FROM zam_tymczas WHERE wartosc < ( SELECT AVG(wartosc) FROM zamowienia );

W tym przypadku użycie nawiasu okrągłego było niezbędne do prawidłowego wykonania instrukcji.

10 Transakcje Transakcje w systemach baz danych Transakcja to zbiór operacji na bazie danych, które stanowią logiczną całość i albo mogą być wykonane wszystkie, albo żadna z nich. Najpopularniejszym przykładem operacji, która powinna być wykonana jako transakcja, jest przelew. Dodajmy do naszej bazy danych nową tabelę o nazwie konta, która będzie przechowywała informację o stanie kont. Będzie się ona składała z trzech kolumn: T id — typu INTEGER, będącej kluczem głównym i zawierającej identyfikator każdego wiersza; T id_osoby — typu INTEGER, będącej kluczem obcym i zawierającej identyfikator właściciela każdego konta; T saldo — typu DECIMAL(9,2), zawierającej aktualny stan konta. Tabela ta zostanie utworzona za pomocą instrukcji: CREATE TABLE konta ( id INTEGER PRIMARY KEY, id_osoby INTEGER NOT NULL, saldo DECIMAL(7, 2) NOT NULL );

Wypełnimy ją przykładowymi danymi: INSERT INTO konta VALUES(1, 1, 120.00); INSERT INTO konta VALUES(2, 2, 30.00);

134

SQL • Ćwiczenia praktyczne

Aby wykonać przelew o wartości 40 zł z pierwszego konta na drugie, trzeba wykonać dwie operacje. Będą to instrukcje UPDATE o postaci: UPDATE konta SET saldo = saldo – 40 WHERE id = 1; UPDATE konta SET saldo = saldo + 40 WHERE id = 2;

Problem w tym, że pomiędzy jedną a drugą instrukcją UPDATE mogą się zdarzyć najrozmaitsze awarie, których skutkiem będzie to, że instrukcja zmniejszająca saldo pierwszego konta zostanie wykonana, natomiast instrukcja zwiększająca saldo drugiego konta — już nie. Tym samym z systemu zniknie 40 zł, a do tego nie można dopuścić.

Objęcie instrukcji transakcją Przebieg transakcji można podzielić na trzy etapy: T rozpoczęcie transakcji, T wykonanie instrukcji składających się na transakcję, T zatwierdzenie transakcji. Do rozpoczynania transakcji służy instrukcja SQL (MySQL, PostgreSQL): START TRANSACTION

lub (MS SQL, SQLite): BEGIN TRANSACTION

Do jej zatwierdzania służy instrukcja: COMMIT

W bazie Oracle transakcje rozpoczynane są automatycznie po rozpoczęciu pracy z systemem. Te wiadomości pozwolą nam bezpiecznie wykonać przelew 40 zł pomiędzy kontami 1 i 2. Ć W I C Z E N I E

10.1

Wykonanie transakcji

Wykonaj transakcję polegającą na zmniejszeniu salda konta o identyfikatorze 1 o 40 zł oraz zwiększeniu o tę samą kwotę salda konta o identyfikatorze 2.

Rozdział 10. • Transakcje

135

Aby wykonać ćwiczenie, należy wykonać serię instrukcji (MySQL, PostgreSQL): START TRANSACTION; UPDATE konta SET saldo = saldo – 40 WHERE id = 1; UPDATE konta SET saldo = saldo + 40 WHERE id = 2; COMMIT;

lub (MS SQL, SQLite): BEGIN TRANSACTION; UPDATE konta SET saldo = saldo – 40 WHERE id = 1; UPDATE konta SET saldo = saldo + 40 WHERE id = 2; COMMIT;

lub (Oracle): UPDATE konta SET saldo = saldo – 40 WHERE id = 1; UPDATE konta SET saldo = saldo + 40 WHERE id = 2; COMMIT;

O tym, że salda faktycznie zostały zmienione, przekonamy się, wyświetlając zawartość tabeli konta za pomocą instrukcji: SELECT * FROM konta;

Wycofywanie transakcji Transakcja nie zawsze musi być zatwierdzana. Jeśli się rozmyślimy, możemy ją odwołać (wycofać). Zamiast COMMIT należy wtedy użyć polecenia ROLLBACK. Schemat postępowania będzie wtedy następujący: START TRANSACTION; instrukcje wchodzące w skład transakcji ROLLBACK;

System zachowa się wtedy tak, jakby instrukcje wchodzące w skład transakcji nie zostały wykonane. Ć W I C Z E N I E

10.2

Odwołanie transakcji

Obejmij transakcją instrukcje UPDATE zmieniające salda w tabeli konta. Zamiast polecenia COMMIT użyj ROLLBACK.

136

SQL • Ćwiczenia praktyczne

Tym razem należy użyć instrukcji (MySQL, PostgreSQL): START TRANSACTION; UPDATE konta SET saldo = saldo – 40 WHERE id = 1; UPDATE konta SET saldo = saldo + 40 WHERE id = 2; ROLLBACK;

lub (MS SQL, SQLite): BEGIN TRANSACTION; UPDATE konta SET saldo = saldo – 40 WHERE id = 1; UPDATE konta SET saldo = saldo + 40 WHERE id = 2; ROLLBACK;

lub (Oracle): UPDATE konta SET saldo = saldo – 40 WHERE id = 1; UPDATE konta SET saldo = saldo + 40 WHERE id = 2; ROLLBACK;

Jeśli po ich wykonaniu wyświetlimy zawartość tabeli konta, przekonamy się, że faktycznie salda pozostały niezmienione.

Izolacja transakcji Instrukcje wykonywane podczas transakcji nie mają wpływu na stan danych w bazie. Dopiero użycie polecenia COMMIT powoduje widoczną modyfikację danych. Możemy się o tym przekonać, wykonując następne ćwiczenie. Ć W I C Z E N I E

10.3

Stan danych w trakcie trwania transakcji

Nawiąż dwa połączenia z bazą danych; w jednym z nich wykonaj transakcję zmieniającą stan kont w tabeli konta. W trakcie wykonywania transakcji sprawdzaj stan tabeli w obu połączeniach. Należy uruchomić dwukrotnie klienta bazy danych i nawiązać połączenia. W pierwszym połączeniu trzeba wykonać instrukcje (MySQL, PostgreSQL): START TRANSACTION; UPDATE konta SET saldo = saldo – 40 WHERE id = 1; UPDATE konta SET saldo = saldo + 40 WHERE id = 2;

Rozdział 10. • Transakcje

137

lub (MS SQL, SQLite): BEGIN TRANSACTION; UPDATE konta SET saldo = saldo – 40 WHERE id = 1; UPDATE konta SET saldo = saldo + 40 WHERE id = 2;

lub (Oracle): UPDATE konta SET saldo = saldo – 40 WHERE id = 1; UPDATE konta SET saldo = saldo + 40 WHERE id = 2;

a następnie w obu połączeniach sprawdzić stan tabeli konta. Będzie on taki jak na rysunku 10.1. W dalszej kolejności w pierwszym połączeniu należy zatwierdzić transakcję, wywołując polecenie COMMIT, i ponownie sprawdzić stan tabeli (rysunek 10.2). Przekonamy się, że faktycznie w drugim połączeniu stan tabeli zmienił się dopiero po użyciu instrukcji COMMIT.

Rysunek 10.1. Stan tabeli konta w trakcie transakcji

138

SQL • Ćwiczenia praktyczne

Rysunek 10.2. Stan tabeli konta po zatwierdzeniu transakcji

11 Więzy integralności Integralność danych W wielu dotychczasowych przykładach wykorzystywaliśmy tabele powiązane ze sobą kluczami obcymi. Z taką sytuacją mieliśmy na przykład do czynienia w rozdziale 6. w przypadku tabel pracownicy i stanowiska, gdzie w tabeli pracownicy znajdowała się kolumna stanowisko_id odwołująca się do klucza głównego tabeli stanowiska. Tabele te zostały utworzone za pomocą instrukcji: CREATE TABLE pracownicy ( id INTEGER PRIMARY KEY, imie VARCHAR(20) NOT NULL, nazwisko VARCHAR(30) NOT NULL, placa DECIMAL(7, 2) NOT NULL, stanowisko_id INTEGER, pesel CHAR(11) ); CREATE TABLE stanowiska ( id INTEGER PRIMARY KEY, nazwa VARCHAR(20) NOT NULL );

140

SQL • Ćwiczenia praktyczne

Jeśli wprowadzimy teraz dane za pomocą zapytań: INSERT INTO pracownicy ´'12345678901'); INSERT INTO pracownicy ´'92345678909'); INSERT INTO stanowiska INSERT INTO stanowiska

VALUES (1, 'Adam', 'Kowalski', 1624.50, 1, VALUES (4, 'Arkadiusz', 'Malinowski', 1600.00, 4, VALUES(1, 'magazynier'); VALUES(2, 'kierownik');

tabele będą miały postać: pracownicy +----+-----------+------------+---------+---------------+-------------+ | id | imie | nazwisko | placa | stanowisko_id | pesel | +----+-----------+------------+---------+---------------+-------------+ | 1 | Adam | Kowalski | 1624.50 | 1 | 12345678901 | | 4 | Arkadiusz | Malinowski | 1600.00 | 4 | 92345678909 | +----+-----------+------------+---------+---------------+-------------+ stanowiska +----+------------+ | id | nazwa | +----+------------+ | 1 | magazynier | | 2 | kierownik | +----+------------+

Dane będą więc niespójne. Pracownik o identyfikatorze 4 ma przypisane stanowisko o identyfikatorze 4, gdy tymczasem w tabeli stanowiska nie ma rekordu o takim identyfikatorze. To oczywiście błąd, który w dodatku bardzo łatwo popełnić. Aby go uniknąć, trzeba by za każdym razem podczas wprowadzania danych do tabeli pracownicy sprawdzać, czy w tabeli stanowiska istnieje odpowiedni wpis. Taka czynność może być jednak wykonana automatycznie przez serwer bazy danych, o ile odpowiednio zdefiniujemy więzy integralności. Można więc przerzucić na serwer konieczność badania spójności danych, choć będzie się to odbywało kosztem wydajności systemu.

Definiowanie klucza obcego Badanie spójności danych odbywa się przez jawne zdefiniowanie klucza obcego. Możemy bowiem poinformować bazę, że kolumna stanowisko_id w tabeli pracownicy to klucz obcy pochodzący z kolumny id z tabeli stanowiska. Ogólna konstrukcja wprowadzająca ograniczenie ze względu na klucze obce ma postać:

Rozdział 11. • Więzy integralności

141

[CONSTRAINT nazwa] FOREIGN KEY (kolumna1, kolumna2, ..., kolumnaN) ´REFRENCES nazwa_tabeli (kolumna1, kolumna2, ..., kolumnaN)

gdzie: T nazwa jest nazwą ograniczenia, za pomocą której będzie ono

identyfikowane. Jeżeli parametr zostanie pominięty, zostanie nadana nazwa systemowa. T Kolumny wymienione po słowach FOREIGN KEY określają kolumny w tabeli, dla której definiujemy ograniczenie, zawierające klucz obcy. T nazwa_tabeli określa, z której tabeli pochodzi klucz obcy. T Kolumny wymienione po REFRENCES nazwa_tabeli określają, z których kolumn pochodzi klucz obcy. Taką konstrukcję należy umieścić za definicją kolumn w instrukcji CREATE TABLE. Ć W I C Z E N I E

11.1

Utworzenie klucza obcego

Napisz instrukcję tworzącą tabelę pracownicy, w której kolumna stanowisko_id będzie powiązana więzami integralności z kolumną id tabeli stanowiska. Jeśli kolumna stanowisko_id ma być oznaczona jako klucz obcy pochodzący z kolumny id tabeli stanowiska, do utworzenia tabeli pracownicy należy użyć następującej instrukcji (przed jej wykonaniem trzeba usunąć z bazy istniejącą tabelę pracownicy): CREATE TABLE pracownicy ( id INTEGER PRIMARY KEY, imie VARCHAR(20) NOT NULL, nazwisko VARCHAR(30) NOT NULL, placa DECIMAL(7, 2) NOT NULL, stanowisko_id INTEGER, pesel CHAR(11), CONSTRAINT stanowiska_fk FOREIGN KEY (stanowisko_id) REFERENCES ´stanowiska(id) );

142

SQL • Ćwiczenia praktyczne

Istnieje także możliwość użycia skróconej wersji definicji klucza obcego; odwołanie należy wtedy umieścić za definicją kolumny według schematu: nazwa_kolumny typ_kolumny atrybuty REFERENCES ´nazwa_tabeli(nazwa_kolumny) Ć W I C Z E N I E

11.2

Skrócona definicja klucza obcego

Napisz instrukcję tworzącą tabelę pracownicy, w której kolumna stanowisko_id będzie powiązana więzami integralności z kolumną id tabeli stanowiska. Użyj skróconej definicji klucza obcego. Aby użyć skróconej definicji klucza obcego, należy skorzystać z instrukcji: CREATE TABLE pracownicy ( id INTEGER PRIMARY KEY, imie VARCHAR(20) NOT NULL, nazwisko VARCHAR(30) NOT NULL, placa DECIMAL(7, 2) NOT NULL, stanowisko_id INTEGER REFERENCES stanowiska(id), pesel CHAR(11) ); Ć W I C Z E N I E

11.3

Wykorzystanie klucza obcego

Wykonaj przedstawione na początku rozdziału instrukcje INSERT dotyczące tabeli pracownicy utworzonej w ćwiczeniu 11.1 (lub w ćwiczeniu 11.2). Zaobserwuj reakcję serwera baz danych. Jeśli spróbujemy wykonać instrukcję INSERT: INSERT INTO pracownicy VALUES (1, 'Adam', 'Kowalski', 1624.50, 1, ´'12345678901'); INSERT INTO pracownicy VALUES (4, 'Arkadiusz', 'Malinowski', 1600.00, 4, ´'92345678909');

dla tabeli pracownicy powstałej w ćwiczeniu 11.1 lub 11.2, a w bazie będzie istniała utworzona i wypełniona przedstawionymi na początku rozdziału danymi tabela stanowiska, zostanie zgłoszony błąd. W przypadku bazy MySQL będzie on miał postać widoczną na rysunku 11.1.

Rozdział 11. • Więzy integralności

143

O ile bowiem pierwsza instrukcja INSERT jest prawidłowa (istnieje w tabeli stanowiska wiersz o identyfikatorze 1), to druga jest błędna (nie istnieje w tabeli stanowiska wiersz o identyfikatorze 4).

Rysunek 11.1. Próba naruszenia więzów integralności

Dodawanie i usuwanie więzów Ograniczenia związane z kluczami obcymi mogą być dodawane do istniejących tabel. Służy do tego przedstawiona już w rozdziale 2. instrukcja ALTER TABLE. Jej ogólna postać będzie w tym przypadku następująca1: ALTER TABLE nazwa_tabeli ADD [CONSTRAINT nazwa] FOREIGN KEY (kolumna1, kolumna2, ..., kolumnaN) REFRENCES nazwa_tabeli (kolumna1, kolumna2, ..., kolumnaN)

Została ona podzielona na cztery wiersze dla większej czytelności, ale może być również w całości zapisana jednym ciągiem. Ć W I C Z E N I E

11.4

Dodanie klucza obcego do istniejącej tabeli

Utwórz tabele pracownicy i stanowiska za pomocą instrukcji przedstawionych na początku rozdziału (nie wypełniaj ich danymi), a następnie utwórz klucz obcy w taki sposób, aby kolumna stanowisko_id z tabeli pracownicy była powiązana z kolumną id tabeli stanowiska.

1

Baza SQLite (co najmniej do wersji 3.7) nie obsługuje takich poleceń.

144

SQL • Ćwiczenia praktyczne

Aby do istniejącej tabeli pracownicy dodać klucz obcy wiążący kolumnę stanowisko_id z kolumną id w tabeli stanowiska, należy użyć instrukcji: ALTER TABLE pracownicy ADD CONSTRAINT stanowiska_fk FOREIGN KEY (stanowisko_id) REFERENCES stanowiska(id);

Ograniczenie związane z kluczem obcym może być w każdej chwili usunięte przy wykorzystaniu instrukcji ALTER TABLE o ogólnej postaci (dla baz PostgreSQL, MS SQL, Oracle): ALTER TABLE nazwa_tabeli DROP CONSTRAINT nazwa_ograniczenia;

lub (dla bazy MySQL): ALTER TABLE nazwa_tabeli DROP FOREIGN KEY nazwa_ograniczenia; Ć W I C Z E N I E

11.5

Usuwanie klucza obcego

Usuń ograniczenie związane z kluczem obcym z tabeli pracownicy. Aby usunąć dodane w jednym z poprzednich ćwiczeń ograniczenie, należy wykorzystać jedną z instrukcji (w zależności od systemu baz danych, na którym pracujemy): ALTER TABLE pracownicy DROP CONSTRAINT stanowiska_fk; ALTER TABLE pracownicy DROP FOREIGN KEY stanowiska_fk;

A Instalacja i podstawowa konfiguracja baz Baza MySQL Instalacja w systemie Linux Użytkownicy systemu Linux są w bardzo dobrej sytuacji, gdyż większość popularnych dystrybucji zawiera pakiety MySQL, które zresztą często są standardowo instalowane (zależy to od opcji wybranych w trakcie procesu instalacji). A jeśli nie zostały zainstalowane, można je zawsze doinstalować, korzystając z dołączonego do systemu instalatora. Proces ten zostanie omówiony na przykładzie popularnej dystrybucji Fedora Core 14 i MySQL w wersji 5, jednak dla innych wersji systemu będzie wyglądał bardzo podobnie.

Instalacja za pomocą graficznego menedżera pakietów Jeżeli dysponujemy gotowym systemem Fedora, w którym baza MySQL nie została zainstalowana, można ją doinstalować. Czynność tę wykonuje się w taki sam sposób jak w przypadku innych aplikacji: za pomocą Menedżera pakietów. Aby go wywołać, należy z menu System wybrać pozycję Administracja, a następnie Dodaj/Usuń oprogramowanie.

146

SQL • Ćwiczenia praktyczne

W Menedżerze pakietów na znajdującej się z lewej strony liście grup aplikacji należy wybrać pozycję Serwery, a następnie w oknie z prawej strony odszukać i zaznaczyć opcję The MySQL server and related files lub podobną (rysunek A.1). Po wykonaniu tych czynności trzeba kliknąć Zastosuj, co spowoduje pojawienie się (po chwili niezbędnej na rozwiązanie zależności między pakietami) następnego okna (rysunek A.2), zawierającego listę pakietów związanych z opcją MySQL. Należy w nim kliknąć przycisk Dalej.

Rysunek A.1. Okno Menedżera pakietów z zaznaczoną opcją serwera MySQL Rysunek A.2. Lista pakietów do zainstalowania

Dodatek A • Instalacja i podstawowa konfiguracja baz

147

Do instalacji potrzebne są uprawnienia administratora systemu (użytkownik root), dlatego też pojawi się dodatkowe okno umożliwiające wprowadzenie hasła przypisanego temu użytkownikowi. Po podaniu prawidłowego hasła rozpocznie się pobieranie danych i instalacja pakietów. Po jej zakończeniu można zamknąć okno Menedżera pakietów i przystąpić do uruchomienia i konfiguracji serwera bazy danych.

Instalacja w wierszu poleceń Sposób instalacji w wierszu poleceń zależy od tego, z jakiego menedżera pakietów będziemy korzystać i jaki jest dostępny w naszym systemie. Przykładowo, w przypadku menedżera yum wydajemy polecenie: sudo yum install mysql-server

a w przypadku apt: sudo apt-get install mysql-server

Uruchamianie serwera MySQL Kiedy pakiet zostanie zainstalowany, należy uruchomić serwer MySQL i ewentualnie ustalić, w jaki sposób będzie uruchamiany. Usługa ta bowiem domyślnie może być zatrzymana, co zależy od tego, z jakiej dystrybucji korzystamy oraz jakie opcje instalacyjne zostały wybrane. Do zarządzania usługami w systemie Fedora służy okno konfiguracji usług, które można wywołać przez wybranie z menu System pozycji Administracja, a następnie Usługi (jeśli nie ma takiej pozycji menu, należy najpierw zainstalować pakiet o nazwie system-config-services z grupy System). W oknie konfiguracji usług na liście z lewej strony należy znaleźć pozycję mysqld, odpowiadającą usłudze serwera MySQL, zaznaczyć ją, a następnie kliknąć przycisk Włącz (rysunek A.3). Do włączenia usługi niezbędne są uprawnienia administratora systemu, dlatego pojawi się okno z prośbą o wprowadzenie hasła administratora. Po podaniu prawidłowego hasła usługa zostanie włączona i będzie też automatycznie uruchamiana podczas startu systemu.

148

SQL • Ćwiczenia praktyczne

Rysunek A.3. Fragment okna konfiguracji usług w systemie Fedora

Konfiguracja Po zainstalowaniu oraz uruchomieniu usługi serwera MySQL należy wykonać jeszcze kilka czynności konfiguracyjnych. Pierwszą z nich jest ustawienie hasła dla administratora bazy (czyli użytkownika root), ponieważ domyślnie hasło nie jest do tego konta przypisane (w niektórych dystrybucjach nadanie hasła administratora jest możliwe w trakcie instalacji pakietu). W tym celu należy uruchomić konsolę (z menu Programy wybieramy kolejno pozycje Narzędzia systemowe i Terminal), a w niej program mysqladmin, stosując wywołanie: mysqladmin –u root password hasło

Przykładowo, jeśli hasłem użytkownika root ma być abh126WL, należy wydać polecenie: mysqladmin –u root password abh126WL

Po jego wykonaniu wszelkie czynności administracyjne wymagające logowania do serwera będą wymagały podawania hasła. Kolejne czynności to założenie na serwerze bazy danych oraz konta do codziennej pracy. Uruchamiamy w tym celu klienta mysql, logując się jako administrator: mysql –u root –pabh126WL

a następnie wydajemy polecenie: CREATE DATABASE test;

które utworzy bazę o nazwie test (można ją oczywiście zmienić), oraz: GRANT ALL ON test.* TO user1 IDENTIFIED BY 'tyhOL981d';

Dodatek A • Instalacja i podstawowa konfiguracja baz

149

Dzięki temu zostanie utworzone konto użytkownika o nazwie user1 i haśle tyhOL981d, który będzie miał prawo dostępu/dostęp do bazy test i wykonywania na niej wszystkich operacji (oczywiście nazwę i hasło należy dostosować do własnych potrzeb). Aby zakończyć pracę z klientem mysql, należy wpisać polecenie quit. Opisane czynności zostały zilustrowane na rysunku A.4. Po ich wykonaniu można przystąpić do pracy z bazą.

Rysunek A.4. Przygotowanie serwera MySQL do działania

Praca z bazą Do wykonywania ćwiczeń najlepiej wykorzystywać utworzone w poprzednim podpunkcie konto użytkownika user1 oraz bazę test. Za pomocą klienta mysql należy połączyć się z serwerem. Zrobimy to, wydając w konsoli polecenie: mysql –u user1 -ptyhOL981d test

Oznacza ono: uruchom klienta mysql, który wykona połączenie z serwerem MySQL, zaloguje użytkownika o nazwie user1 posługującego się hasłem tyhOL981d oraz wybierze bazę o nazwie test. Po wykonaniu tej czynności można już wykonywać instrukcje SQL, pamiętając, aby każdą z nich zakończyć znakiem średnika. Można również zalogować się do serwera bez wybierania bazy danych, pisząc: mysql –u user1 -ptyhOL981d

150

SQL • Ćwiczenia praktyczne

Wtedy po nawiązaniu połączenia trzeba wybrać bazę, używając polecenia: use test;

Kiedy chcemy rozłączyć się z serwerem, wydajemy komendę quit.

Instalacja w systemie Windows MySQL dostępny jest praktycznie dla wszystkich wersji systemu Windows, począwszy od wiekowych już systemów 9x, Me, NT (tylko MySQL 3.X i 4.X) poprzez 2000, XP, Vista, 7, a skończywszy na Windows Server 2003, 2008 i innych wersjach serwerowych (również 64-bitowych). Pakiet instalacyjny można znaleźć w dziale pobierania na stronie http://www.mysql.com. Na kilku następnych stronach zostanie opisana procedura instalacji MySQL w wersji 5.5. Jest praktycznie taka sama we wszystkich odmianach systemu Windows.

Procedura instalacyjna Instalacja MySQL pod Windows jest bardzo prosta. Należy uruchomić archiwum dystrybucyjne (plik będzie miał nazwę mysql-5.5.8-win32.msi lub podobną, w zależności od użytej wersji). Po chwili ujrzymy typowe okno instalatora Windows, który przeprowadzi nas przez dalsze czynności. Do wyboru są następujące typy instalacji: T Instalacja standardowa (Typical) — umożliwia zainstalowanie serwera, działającego w wierszu poleceń klienta oraz kilku innych narzędzi. T Instalacja pełna (Complete) — umożliwia zainstalowanie wszystkich składników pakietu. T Instalacja użytkownika (Custom) — pozwala ustalić, które składniki pakietu mają być zainstalowane. W przypadku wybrania instalacji standardowej lub pełnej po kliknięciu przycisku Next pojawi się okno dialogowe podsumowujące wybór, zawierające przycisk Install. Kliknięcie go powoduje rozpoczęcie właściwego procesu instalacji. Należy zwrócić uwagę, że obie wymienione opcje nie umożliwiają wybrania katalogu docelowego — automatycznie wybierany jest katalog Program Files/MySQL. Aby zmienić katalog docelowy, należy skorzystać z instalacji użytkownika (Custom).

Dodatek A • Instalacja i podstawowa konfiguracja baz

151

Po zakończeniu procesu instalacji pojawi się okno informujące o jej zakończeniu. Jeśli w tym oknie zaznaczymy opcję Launch the MySQL Instance Configuration Wizard (rysunek A.5), po kliknięciu przycisku Finish zostanie uruchomiony kreator konfiguracji — warto z tej możliwości skorzystać. Opis działania kreatora konfiguracji znajduje się w następnej sekcji.

Rysunek A.5. Opcja pozwalająca na automatyczne uruchomienie kreatora konfiguracji

Konfiguracja Podstawową konfigurację MySQL najłatwiej przeprowadzić za pomocą kreatora konfiguracji. Jeżeli podczas instalacji zaznaczyliśmy opcję Launch the MySQL Instance Configuration Wizard, kreator uruchomi się automatycznie, w przeciwnym razie po zakończeniu instalacji należy uruchomić program MySQLInstanceConfig.exe. Znajduje się on w katalogu, w którym został zainstalowany serwer: w podkatalogu bin. Konfigurator dostępny jest także z poziomu menu Start/Wszystkie Programy/ MySQL/MySQL Server 5.5/MySQL Instance Configuration Wizard. Po uruchomieniu kreatora konfiguracji można wybrać jeden z dwóch trybów pracy: T Detailed Configuration — przeznaczony dla zaawansowanych użytkowników, którzy chcą mieć możliwość szczegółowej ingerencji w ustawienia serwera. T Standard Configuration — przeznaczony dla osób, którym wystarczą standardowe ustawienia. Przy pierwszej instalacji w zupełności wystarczą standardowe ustawienia serwera, wybieramy zatem opcję Standard Configuration (opis konfiguracji w trybie Detailed Configuration można znaleźć w dokumentacji MySQL). Po wybraniu opcji Standard Configuration i kliknięciu przycisku Next zobaczymy na ekranie kolejne okno dialogowe (rysunek A.6), w którym

152

SQL • Ćwiczenia praktyczne

Rysunek A.6. Rejestrowanie MySQL jako usługi systemowej

zaznaczamy Launch the MySQL Server automatically, co spowoduje, że usługa MySQL będzie uruchamiana automatycznie razem z systemem (opcja Install As Windows Service powinna być zaznaczona domyślnie). Z listy rozwijalnej Service Name można również wybrać nazwę usługi MySQL. Omawiane okno dialogowe zawiera też opcję Include Bin Directory in Windows PATH, której zaznaczenie powoduje dodanie do zmiennej środowiskowej PATH ścieżki dostępu do plików binarnych MySQL, dzięki czemu będą one mogły być wygodnie wywoływane w wierszu poleceń. Następne okno dialogowe pozwala ustawić hasło dla administratora bazy danych (użytkownik root). Hasło należy wprowadzić w polu tekstowym Enter the root password oraz potwierdzić w polu Retype the password (rysunek A.7). Po wprowadzeniu i potwierdzeniu hasła klikamy Next, a w dwóch kolejnych oknach — na Execute i Finish. Tym samym działanie kreatora zostanie zakończone.

Rysunek A.7. Konfiguracja konta administratora

Następnie należy uruchomić konsolę systemową (Start/Uruchom/ cmd.exe w systemach 2000/XP/Vista/7 itp. lub Start/Uruchom/ command.exe w systemach 9x/Me), a w niej klienta mysql, logując się jako administrator: mysql –u root –pabh126WL

Dodatek A • Instalacja i podstawowa konfiguracja baz

153

(zakładamy, że hasło administratora brzmi abh126WL). Po uruchomieniu klienta należy wydać polecenie: CREATE DATABASE test;

które utworzy bazę o nazwie test (można ją oczywiście zmienić), oraz: GRANT ALL ON test.* TO user1 IDENTIFIED BY 'tyhOL981d';

dzięki czemu zostanie utworzone konto użytkownika o nazwie user1 i haśle tyhOL981d, który będzie miał prawa dostępu do bazy test i wykonywania na niej wszystkich operacji. Aby zakończyć pracę z klientem mysql, należy wpisać polecenie quit. Opisane czynności oraz reakcje serwera zostały zilustrowane na rysunku A.8.

Rysunek A.8. Pierwsze połączenie z serwerem MySQL

Praca z bazą Do wykonywania ćwiczeń najlepiej wykorzystywać utworzone w poprzednim podpunkcie konto użytkownika user1 oraz bazę test. Za pomocą klienta mysql należy połączyć się z serwerem; zrobimy to, wydając w konsoli polecenie: mysql –u user1 -ptyhOL981d test

Oznacza ono: uruchom klienta mysql, który wykona połączenie z serwerem MySQL, zaloguje użytkownika o nazwie user1 posługującego się hasłem tyhOL981d oraz wybierze bazę o nazwie test. Po wykonaniu tej czynności można już wykonywać instrukcje SQL, pamiętając, aby każdą z nich zakończyć znakiem średnika.

154

SQL • Ćwiczenia praktyczne

Można również zalogować się do serwera bez wybierania bazy danych, pisząc: mysql –u user1 -ptyhOL981d

Wtedy po nawiązaniu połączenia trzeba wybrać bazę, używając polecenia: use test;

Kiedy chcemy rozłączyć się z serwerem, wydajemy komendę quit.

Baza PostgreSQL Instalacja w systemie Linux Podobnie jak w przypadku opisywanej wcześniej bazy MySQL, większość popularnych dystrybucji zawiera pakiety PostgreSQL. Proces instalacji zostanie pokazany na przykładzie popularnej dystrybucji Fedora Core 14.

Instalacja za pomocą graficznego menedżera pakietów Jeżeli dysponujemy gotowym systemem Fedora, w którym baza PostgreSQL nie została zainstalowana, można ją doinstalować. Czynność tę wykonuje się w taki sam sposób jak w przypadku innych aplikacji: za pomocą Menedżera pakietów. Aby go wywołać, należy z menu System wybrać pozycję Administracja, a następnie Dodaj/Usuń oprogramowanie. W Menedżerze pakietów na znajdującej się z lewej strony liście grup aplikacji należy wybrać pozycję Serwery, a następnie w oknie z prawej strony odszukać i zaznaczyć opcję opisaną jako postgresql-server-X.Y.Z lub podobną (rysunek A.9). Po wykonaniu tych czynności trzeba kliknąć Zastosuj, co spowoduje pojawienie się (po chwili niezbędnej na rozwiązanie zależności między pakietami) następnego okna, zawierającego listę pakietów związanych z opcją postgresql-server. Należy w nim kliknąć przycisk Dalej. Do instalacji potrzebne są uprawnienia administratora systemu (użytkownik root), dlatego też pojawi się dodatkowe okno umożliwiające wprowadzenie hasła przypisanego temu użytkownikowi. Po podaniu

Dodatek A • Instalacja i podstawowa konfiguracja baz

155

Rysunek A.9. Wybór pakietu serwera PostgreSQL

prawidłowego hasła rozpocznie się pobieranie danych i instalacja pakietów. Po jej zakończeniu można zamknąć okno Menedżera pakietów i przystąpić do uruchomienia i konfiguracji serwera bazy danych.

Instalacja w wierszu poleceń Sposób instalacji w wierszu poleceń zależy od tego, z jakiego menedżera pakietów będziemy korzystać i jaki jest dostępny w naszym systemie. Przykładowo w przypadku menedżera yum wydajemy polecenie: sudo yum install postgresql

a w przypadku apt: sudo apt-get install postgresql

Uruchamianie serwera PostgreSQL Kiedy pakiet zostanie zainstalowany, należy uruchomić serwer PostgreSQL i ewentualnie ustalić, w jaki sposób będzie uruchamiany. Usługa ta bowiem domyślnie może być zatrzymana, co zależy od tego, z jakiej dystrybucji korzystamy oraz jakie opcje instalacyjne zostały wybrane. Do zarządzania usługami w systemie Fedora służy okno konfiguracji usług, które zostało opisane w części dotyczącej serwera MySQL. W oknie konfiguracji usług na liście z lewej strony należy znaleźć pozycję postgresql-server, odpowiadającą usłudze serwera PostgreSQL,

156

SQL • Ćwiczenia praktyczne

zaznaczyć ją, a następnie kliknąć przycisk Włącz (rysunek A.10). Do włączenia usługi niezbędne są uprawnienia administratora systemu, dlatego pojawi się okno z prośbą o wprowadzenie hasła administratora. Po podaniu prawidłowego hasła usługa zostanie włączona i będzie też automatycznie uruchamiana podczas startu systemu.

Rysunek A.10. Fragment okna konfiguracji usług w systemie Fedora

Konfiguracja Po zainstalowaniu oraz uruchomieniu usługi serwera należy założyć konto użytkownika oraz bazę, w której będą wykonywane ćwiczenia. W tym celu należy uruchomić konsolę (z menu Programy wybieramy kolejno pozycje Narzędzia systemowe i Terminal). Następnie trzeba przełączyć użytkownika na postgres: sudo su – postgres

lub: su – su - postgres

oraz założyć w bazie konto o nazwie zgodnej z nazwą konta, na którym standardowo pracujemy w Linuksie. Przykładowo, jeśli naszym codziennym kontem jest jkowalski, zakładamy w bazie konto o takiej właśnie nazwie, wydając polecenie: createuser jkowalski

Zostaną nam wtedy zadane trzy pytania: T Czy konto ma mieć uprawnienia administracyjne (ang. Shall the new role be a supersuer?)? — odpowiadamy n (nie).

Dodatek A • Instalacja i podstawowa konfiguracja baz

157

T Czy konto ma mieć uprawnienia do tworzenia baz (ang. Shall the new role be allowed to create database?)? — odpowiadamy y (tak). T Czy konto ma mieć uprawnienia do tworzenia innych kont

(ang. Shall the new role be allowed to create more new roles?)? — odpowiadamy y (tak). Kiedy udzielimy wszystkich odpowiedzi, konto zostanie utworzone, co zostanie potwierdzone komunikatem: CREATE ROLE

Wydajemy następnie polecenie exit, aby opuścić konto postgres (oraz, ewentualnie, ponownie exit, aby opuścić konto root). Opisane etapy zostały przedstawione na rysunku A.11.

Rysunek A.11. Tworzenie nowego konta

Następną czynnością do wykonania jest założenie bazy danych — nazwiemy ją test. W konsoli, kiedy jesteśmy zalogowani jako jkowalski, wydajemy polecenie: createdb test

W odpowiedzi otrzymamy1 komunikat (rysunek A.12): CREATE DATABASE

który oznacza, że baza została założona i jest gotowa do działania. Rysunek A.12. Tworzenie nowej bazy danych

1

W niektórych wersjach systemu ten komunikat nie pojawia się.

158

SQL • Ćwiczenia praktyczne

Praca z bazą Aby wykonywać polecenia SQL, trzeba uruchomić klienta PostgreSQL oraz wskazać bazę danych, na której chcemy pracować. W tym celu w konsoli wydajemy polecenie: psql test

Klient uruchomi się i nawiąże połączenie z serwerem baz danych (rysunek A.13). Możemy wtedy wykonywać polecenia SQL, pamiętając, aby każde z nich kończyć znakiem średnika. Aby zakończyć działanie klienta, należy użyć polecenia: \q

Rysunek A.13. Uruchomienie klienta psql

Instalacja w systemie Windows PostgreSQL może być zainstalowany na większości wersji systemu Windows, jednak rekomendowane są wersje oparte na architekturze NT (2000, XP, Vista, 7, Windows Server 2003, 2008 itp.). Oprogramowanie można znaleźć pod adresem http://www.postgresql.org/. Instalacja zostanie pokazana na przykładzie systemu Windows XP i PostgreSQL 9, jednak w innych wersjach jest bardzo podobna lub wręcz identyczna.

Instalacja Zawartość archiwum dystrybucyjnego należy uruchomić (plik instalacyjny będzie miał nazwę postgresql-9.0.2-1-windows.exe lub podobną). Procedura instalacyjna przebiega podobnie jak w przypadku innych produktów dla Windows. W jej trakcie można wybrać katalog docelowy oraz katalog, w którym będą przechowywane dane. Pojawi się także

Dodatek A • Instalacja i podstawowa konfiguracja baz

159

okno podobne do widocznego na rysunku A.14, w którym należy podać hasło konta administracyjnego bazy danych (użytkownik postgres) i nowego konta w systemie Windows o nazwie postgres (uwaga: jeśli takie konto istnieje już w Windowsie, np. baza była już wcześniej instalowana, należy podać hasło do istniejącego konta).

Rysunek A.14. Konfiguracja konta administracyjnego

W kolejnych oknach można ustalić port, na którym serwer będzie nasłuchiwał połączeń (standardowo 5432), i standard kodowania i porównywania znaków (opcja Locale, najlepiej wybrać Polish/Poland). Po ustaleniu wszystkich opcji konfiguracyjnych nastąpi właściwa instalacja oprogramowania.

Konfiguracja Po przeprowadzeniu czynności instalacyjnych opisanych w poprzednim punkcie serwer zostanie automatycznie uruchomiony i będzie uruchamiał się razem z systemem. Pozostaje więc założenie konta do pracy oraz bazy testowej. Najlepiej konto to nazwać tak, jak nazywa się konto, na którym pracujemy w systemie operacyjnym. W przykładzie została przyjęta nazwa jkowalski. Aby założyć konto na serwerze PostgreSQL, należy uruchomić konsolę i przejść do katalogu, w którym został zainstalowany pakiet (typowo: c:\Progam Files\PostgreSQL\9.0\), a następnie do podkatalogu bin. W konsoli wydajemy następnie polecenie: createuser –P –U postgres jkowalski

160

SQL • Ćwiczenia praktyczne

wprowadzamy wymyślone hasło do nowo tworzonego konta, odpowiadamy na trzy pytania (ich opis znajduje się w sekcji Konfiguracja podrozdziału Instalacja w systemie Linux) oraz, w odpowiedzi na monit, wprowadzamy hasło użytkownika postgres (zostało ustalone podczas instalacji pakietu; patrz rysunek A.14). Po utworzeniu konta należy utworzyć bazę danych. Nazwiemy ją test. W konsoli wydajemy więc polecenie (musimy być zalogowani w systemie Windows jako jkowalski, w przeciwnym razie do opisanego polecenia należy dodać opcję –U jkowalski): createdb test

i w odpowiedzi na monit wprowadzamy hasło użytkownika jkowalski (hasło wprowadzone podczas tworzenia konta tego użytkownika). Opisane czynności zostały pokazane na rysunku A.15.

Rysunek A.15. Utworzenie konta użytkownika oraz testowej bazy danych

Praca z bazą Aby wykonywać polecenia SQL, musimy uruchomić klienta PostgreSQL oraz wskazać bazę danych, na której chcemy pracować. W tym celu należy uruchomić konsolę i przejść do katalogu, w którym został zainstalowany pakiet, a następnie do podkatalogu bin (typowo: c:\Progam Files\PostgreSQL\9.0\bin), a następnie wydać polecenie: psql test

lub (jeśli w Windowsie jest zalogowany inny użytkownik niż jkowalski): psql –U jkowalski test

oraz wprowadzić hasło.

Dodatek A • Instalacja i podstawowa konfiguracja baz

161

Klient uruchomi się i nawiąże połączenie z serwerem baz danych (rysunek A.16). Możemy wtedy wykonywać polecenia SQL, pamiętając, aby każde z nich kończyć znakiem średnika. Aby zakończyć działanie klienta, należy użyć polecenia: \q

Rysunek A.16. Uruchomienie klienta psql pod Windows Uwaga: w celu prawidłowej obsługi polskich znaków przed uruchomieniem klienta psql może być konieczna zmiana strony kodowej z CP852 (wartość domyślna) na CP1250. W takim przypadku psql wyświetli stosowny komunikat. Należy wtedy opuścić psql, w konsoli systemowej dodatkowo wydać polecenie: chcp 1250

i ponownie uruchomić psql.

Baza SQLite SQLite nie jest typowym serwerem bazy danych, tak jak wszystkie pozostałe opisywane produkty, ale niewielką biblioteką implementującą standard SQL. Najczęściej jest wbudowywana w inne produkty jako ich integralna część. Ta biblioteka może być jednak bez problemów skompilowana w taki sposób, aby stanowiła aplikację uruchamianą w wierszu poleceń. Takie prekompilowane wersje są dostępne dla Linuksa i Windows (a także MacOS, który w tej książce nie jest uwzględniany). W większości popularnych dystrybucji Linuksa SQLite jest dostępne bezpośrednio.

162

SQL • Ćwiczenia praktyczne

W żadnym z systemów nie jest wymagana instalacja. Wystarczy pobrać odpowiedni plik z sekcji pobierania dostępnej na stronie http://www. sqlite.org/ i uruchomić go w wierszu poleceń. W przypadku Linuksa będzie to program sqlite3, a w przypadku Windows — sqlite3.exe.

Praca z bazą Aby rozpocząć pracę z bazą, należy w konsoli wydać polecenie o postaci: sqlite3 nazwa_bazy

np.: sqlite3 test

Zostanie wtedy utworzony plik o nazwie test, w którym będą zapisywane dane, lub, jeśli taki plik istnieje, zostanie wczytany. Po wykonaniu tej czynności można zacząć wykonywać zapytania SQL, pamiętając, aby każde zakończyć znakiem średnika. W celu zakończenia pracy z SQLite należy użyć komendy .q (rysunek A.17). Rysunek A.17. Uruchamianie bazy SQLite

Baza Microsoft SQL Server (MS SQL) Baza Microsoft SQL Server (określana w książce jako MS SQL) jest produktem komercyjnym, występuje jednak w wersji Express, której można używać nieodpłatnie. Sposób instalacji i konfiguracji zostanie pokazany dla wersji 2008r2, najnowszej dostępnej w trakcie tworzenia książki (ta wersja nieoficjalnie była też określana jako MS SQL 2010). Podane oprogramowanie można pobrać z witryny http://www.microsoft. com/ (adresem bezpośrednim dla tego produktu jest http://www.microsoft. com/sqlserver/2008/en/us/default.aspx, nie ma jednak gwarancji, że w przyszłości nie zostanie zmieniony).

Dodatek A • Instalacja i podstawowa konfiguracja baz

163

Instalacja Aby serwer mógł być zainstalowany, należy się upewnić, że w systemie znajdują się następujące składniki: T .NET Framework w wersji 3.5sp1 lub nowszej, T Windows Installer w wersji 4.5 lub nowszej, a w przypadku pełnej instalacji również: T Windows Power Shell 1.0. Konkretne wersje wymienionego oprogramowania mogą się jednak różnić w zależności od tego, na jakim systemie Windows jest instalowany serwer. W przypadku braku niezbędnych aplikacji zostanie wyświetlony odpowiedni komunikat wraz z odnośnikiem pozwalającym na bezpośrednie pobranie danego narzędzia. Plik z pakietem serwera ma nazwę SQLEXPRWT_x86_ENU.exe lub podobną. Po jego uruchomieniu (o ile są spełnione podane wyżej warunki) rozpocznie się proces instalacji. Ekran instalatora wygląda inaczej niż w przypadku typowych aplikacji dla Windows (rysunki A.18 i A19). Jednak sam proces nie jest skomplikowany i zwykle przebiega bezproblemowo. Wszelkie niezbędne do wykonania zadania, które nie mogą być wykonane automatycznie (jak np. restart komputera czy konieczność instalacji dodatkowych składników), są raportowane.

Rysunek A.18. Ekran instalatora Miscrosoft SQL Server 2008r2

164

SQL • Ćwiczenia praktyczne

W trakcie procesu instalacji, tak jak jest to widoczne na rysunku A.19, będzie możliwy wybór katalogu docelowego oraz nazwy usługi systemowej serwera SQL. Nazwą domyślną jest SQLEXPRESS. Należy również wybrać, z jakiego konta systemowego będzie uruchamiana usługa SQL (rysunek A.20).

Rysunek A.19. Wybór katalogu docelowego i nazwy usługi

Rysunek A.20. Wybór konta dla usługi SQL

Jedno z kolejnych okien pozwala ustalić tryb uwierzytelniania przy dostępie do serwera. Z listy oznaczonej jako Specify SQL Server administrators należy wskazać użytkowników systemu Windows, którzy będą mieli uprawnienia administracyjne serwera SQL (rysunek A.21). Jeśli zostanie zaznaczona wybrana opcja Mixed Mode, do uwierzytelniania będzie używane również dodatkowe hasło dla konta administracyjnego o nazwie sa. Hasło należy wtedy wprowadzić do pola Enter password i potwierdzić w polu Confirm password. W przypadku instalacji dla celów testowych nie jest to jednak konieczne.

Dodatek A • Instalacja i podstawowa konfiguracja baz

165

Rysunek A.21. Wybór trybu uwierzytelniania użytkowników

Gdy instalacja zakończy się, usługa serwera zostanie uruchomiona i będzie też uruchamiana automatycznie przy każdym starcie systemu.

Tworzenie bazy testowej Po wykonaniu czynności instalacyjnych warto założyć bazę testową, na której będą wykonywane przykłady i ćwiczenia. Aby to zrobić, należy nawiązać połączenie z serwerem SQL. Służy do tego działający w wierszu poleceń klient sqlcmd. Ta aplikacja znajduje się w lokalizacji, w której został zainstalowany serwer w podkatalogu Tools\Binn (typowo: C:\Program Files\Microsoft SQL Server\100\Tools\Binn\). Należy przejść do tego katalogu (lub przy wykonywaniu polecenia podać pełną ścieżkę dostępu) i wydać komendę o schematycznej postaci: sqlcmd –Snazwa_komputera\nazwa_usługi

np.: sqlcmd –Sdomowy\SQLEXPRESS

jeżeli nazwą komputera jest domowy, a nazwą usługi SQLEXPRESS (nazwa usługi jest ustalana w trakcie procesu instalacji, patrz też rysunek A.19). Jeśli jednak w systemie jest zalogowany inny użytkownik niż użytkownik serwera bazy danych (por. kroki instalacji, rysunek A.20) lub w trakcie instalacji został ustalony mieszany tryb uwierzytelniania (Mixed Mode, rysunek A.21), należy dodatkowo uwzględnić nazwę i hasło użytkownika. Wtedy schemat polecenia jest następujący: sqlcmd -U nazwa_użytkownika -P hasło –Snazwa_komputera\nazwa_usługi

166

SQL • Ćwiczenia praktyczne

np.: sqlcmd -U sa –P tyhOL981d –Sdomowy\SQLEXPRESS

o ile ma być zalogowany główny administrator (sa), a hasło brzmi tyhOL981d. Po zalogowaniu należy utworzyć bazę, np. o nazwie test. W tym celu trzeba wykonać polecenie: CREATE DATABASE test

Polecenie zostanie wysłane do serwera po wykonaniu komendy go. Aby zakończyć pracę i rozłączyć się z serwerem, należy wpisać quit. Opisane czynności oraz reakcje serwera zostały zilustrowane na rysunku A.22.

Rysunek A.22. Logowanie do serwera MS SQL i tworzenie bazy testowej

Praca z bazą Po utworzeniu bazy testowej można przystąpić do wykonywania ćwiczeń. Ta baza musi być jednak wskazana serwerowi. Można to zrobić przy nawiązywaniu połączenia za pomocą sqlcmd poprzez dodanie opcji –d. Polecenie powinno mieć wtedy postać: sqlcmd –Snazwa_komputera\nazwa_usługi -d nazwa_bazy

lub: sqlcmd -U nazwa_użytkownika -P hasło –Snazwa_komputera\nazwa_usługi –d ´nazwa_bazy

np.: sqlcmd –Sdomowy\SQLEXPRESS –d test

Jeżeli połączenie zostało nawiązane bez wyboru nazwy bazy (np. w sposób opisany w poprzedniej sekcji; bez opcji -d), bazę można wybrać już po nawiązaniu połączenia poprzez zastosowanie instrukcji: use nazwa_bazy

Dodatek A • Instalacja i podstawowa konfiguracja baz

167

np.: use test

Należy zwrócić uwagę, że w odróżnieniu od wszystkich pozostałych systemów baz danych opisywanych w książce w przypadku MS SQL użycie średnika kończącego instrukcję i wciśnięcie klawisza Enter nie powoduje wykonania polecenia. Polecenie (lub grupa poleceń) jest wykonywane (przesyłane do serwera) dopiero po wykonaniu komendy go (rysunki A.22, A.23).

Rysunek A.23. Wybór bazy po nawiązaniu połączenia z serwerem

Baza Oracle Baza Oracle, podobnie jak opisana wcześniej MS SQL, jest produktem komercyjnym, istnieje jednak wersja o nazwie Express, z której można korzystać nieodpłatnie. To oprogramowanie można pobrać ze strony http://www.oracle.com. Dostępne są wersje dla Linuksa oraz Windows. Sposób instalacji zostanie przedstawiony dla wersji 10g, najnowszej dostępnej w trakcie przygotowywania materiałów do książki.

Instalacja w systemie Linux Instalowanie w środowisku graficznym Pakiety instalacyjne dostępne są w formacie deb dla systemów opartych na Debianie oraz rpm dla systemów wywodzących się od Red Hat. W praktyce umożliwia to zainstalowanie systemu właściwie w każdej popularnej dystrybucji Linuksa. Poniższy opis dotyczy dystrybucji Fedora Core 14, jednak w innych będzie bardzo podobny. Po pobraniu pakietu (przyjmiemy, że jest to wersja rpm, plik będzie miał nazwę

168

SQL • Ćwiczenia praktyczne

oracle-xe-univ-10.2.0.1-1.0.i386.rpm lub podobną) wystarczy kliknąć jego ikonę prawym przyciskiem myszy i z menu podręcznego wybrać pozycję Otwórz za pomocą Instalatora pakietów (rysunek A.24).

Rysunek A.24. Instalacja pakietu rpm pobranego z internetu

Po chwili niezbędnej na weryfikację powinno pojawić się okno z pytaniem, czy chcemy zainstalować pakiet (rysunek A.25), w którym należy kliknąć na przycisk Zainstaluj. Ponieważ do instalacji oprogramowania niezbędne są uprawnienia administratora, może się również pojawić okno, w którym trzeba będzie podać odpowiednie hasło. Po zakończeniu procesu instalacji można przystąpić do podstawowej konfiguracji serwera. Zostało to opisane w kolejnym punkcie. Rysunek A.25. Potwierdzanie chęci instalacji pakietu

Instalowanie w wierszu poleceń Sposób instalacji w wierszu poleceń zależy od tego, z jakiego menedżera pakietów będziemy korzystać i jaki jest dostępny w naszym systemie. Przykładowo, w przypadku menedżera yum wydajemy polecenie: sudo yum install oracle-xe-univ-10.2.0.1-1.0.i386.rpm

Dodatek A • Instalacja i podstawowa konfiguracja baz

169

a w przypadku rpm: sudo rpm –i oracle-xe-univ-10.2.0.1-1.0.i386.rpm

Podstawowa konfiguracja serwera Przed rozpoczęciem pracy z bazą należy wykonać podstawową konfigurację serwera. W tym celu w wierszu poleceń (w konsoli) wydajemy polecenie: sudo /etc/init.d/oracle-xe configure

Zostanie nam zadanych kilka pytań, na które możemy odpowiadać, wciskając klawisz Enter, co spowoduje użycie opcji domyślnych. W ten sposób ustalane są takie parametry jak port dla aplikacji zarządzającej (domyślnie: 8080), port nasłuchiwania serwera (domyślnie: 1521) oraz to, czy serwer ma być uruchamiany automatycznie wraz ze startem systemu (domyślnie: tak). Należy też podać i potwierdzić hasło administracyjne dla użytkowników SYS oraz SYSTEM. Opisane czynności zostały zobrazowane na rysunku A.26.

Rysunek A.26. Konfiguracja serwera Oracle

Po wstępnej konfiguracji dalsze czynności administracyjne można przeprowadzać (przy ustawieniach standardowych), wczytując do przeglądarki stronę http://localhost:8080/apex lub http://127.0.0.1:8080/apex.

170

SQL • Ćwiczenia praktyczne

Praca z bazą Po skonfigurowaniu serwera w sposób opisany w poprzednim punkcie baza będzie uruchamiana wraz ze startem systemu. Można ją jednak również uruchomić ręcznie, wpisując polecenie: sudo /etc/init.d/oracle-xe start

lub też wybierając z menu Programy pozycję Oracle Database 10g Express Edition oraz Start Database. Zatrzymanie serwera osiągniemy dzięki komendzie: sudo /etc/init.d/oracle-xe stop

lub wybierając z menu Programy pozycję Oracle Database 10g Express Edition oraz Stop Database. Aby uruchomić klienta sqlplus, dzięki któremu można nawiązać połączenie z bazą i wykonywać zapytania SQL, z menu Programy/Oracle Database 10g Express Edition wybieramy Run SQL Command line. To samo można osiągnąć, uruchamiając w konsoli skrypt slqplus.sh znajdujący się w lokalizacji, w której został zainstalowany serwer (typowo /usr/lib/Oracle/xe/app/oracle/product/10.2.0/serwer/, dla wersji 10.2.0) w podkatalogu config/scripts. Po uruchomieniu klienta wydajemy polecenie (rysunek A.27): connect SYS/hasło as SYSDBA

Rysunek A.27. Praca z klientem sqlplus

W celu zakończenia połączenia wydajemy natomiast komendę: quit

Dodatek A • Instalacja i podstawowa konfiguracja baz

171

Instalacja w systemie Windows Procedura instalacyjna Proces instalacji oprogramowania Oracle w wersji dla Windows jest typowy i nie odbiega od procedury stosowanej w przypadku innych aplikacji. W jego trakcie zostanie wyświetlone okno widoczne na rysunku A.28, w którym należy podać hasło dla kont administracyjnych SYS i SYSTEM. Po zakończeniu instalacji baza jest gotowa do pracy. Dodatkowe czynności administracyjne można przeprowadzić, wpisując w dowolnej przeglądarce WWW adres http://localhost:8080/apex lub http://127.0.0.1:8080/apex. Adres ten zostanie otworzony automatycznie, jeśli na ostatnim ekranie instalatora pozostawimy domyślnie zaznaczoną opcję Launch the Database Homepage.

Rysunek A.28. Ustalanie hasła dla kont administracyjnych

Praca z bazą Po zainstalowaniu usługa serwera jest automatycznie uruchamiana i będzie też uruchamiana przy każdym starcie systemu. Zatrzymanie usługi można osiągnąć, wybierając z menu grupy aplikacji Oracle (Start/Wszystkie Programy/Oracle Database 10g Express Edition) pozycję Stop Database, a ponowne uruchomienie wybierając pozycję Start Database. Aby uruchomić pracującego w wierszu poleceń klienta

172

SQL • Ćwiczenia praktyczne

sqlplus, należy z menu Start/Wszystkie Programy/Oracle Database 10g Express Edition wybrać Run SQL Command line, a następnie wykonać polecenie (rysunek A.29): connect SYS/hasło as SYSDBA

gdzie hasło jest hasłem podanym w trakcie instalacji pakietu. Nastąpi wtedy połączenie z bazą danych i będzie można wykonywać polecenia SQL. Aby zakończyć pracę klienta sqlplus, należy wydać polecenie: quit

Rysunek A.29. Nawiązywanie połączenia z bazą