Chcemy zaprojektowad bazę danych:
Każdy z nas chodził lub chodzi do szkoły. A w szkole jak to w szkole, dostajemy stopnie, z każdego przedmiotu po kilka podczas semestru. Zabawmy się projektanta baz danych budującego bazę danych dla szkoły. Ażeby za bardzo nie zagmatwad sprawy zrobimy sobie bazę dla jednej klasy Nasza klasa składa się z kilku osób i tworzy tabelę Uczniowie.
Każdy uczeo identyfikowany jest w tabeli jednym numerem, który nie powtarza się: Jasio to "1" Ania to "2" itd..
Kolumna"ID" jest to tzw. klucz podstawowy.
Oprócz uczniów są w szkole także przedmioty. A jeżeli są przedmioty (mniej i bardziej lubiane w zależności od tego co dany osobnik lubi) to jest i następna tabela. (Nie denerwujcie się!. Za parę minut będzie wszystko wiadomo.). Aby łatwiej nam się uczyło, weźmiemy pod uwagę tylko kilka przedmiotów i stwórzmy tabelę Przedmioty. Pierwsza kolumna to wspomniany wcześniej klucz podstawowych. W tej kolumnie wartości muszą byd unikatowe. Druga kolumna tabeli to obraz prywatnej opinii autora tej strony. Proszę więc nie przywiązywad do tego wagi. A kolumna "nr 2" przyda się nam później - w zapytaniach. W szkole, jak to w szkole, uczniowie są oceniani za swoje postępy w nauce Stworzymy więc kolejną tabelę Ocena, w której będą wypisane rodzaje ocen.
Są uczniowie, są przedmioty, są oceny, więc musi byd i jakieś połączenie między nimi. W szkole są dzienniki ze stronami na odpowiednie przedmioty, a w bazie jest tabela wiążąca uczniów z przedmiotami.Załóżmy, że jest środek semestru. Na każdej lekcji różni uczniowie byli pytani na stopnie z różną częstotliwością.Jasio ma np. 2 stopnie z matematyki, 1 z fizyki itd.. Nasza tabela PrzedmiotUczeoOcena będzie wyglądad tak. Z tabeli tej można odczytad:
"Nr 1" - Jasio ma z matematyki 3(dostateczny) i 5(b. dobry)
"Nr 3" - Zosia nie ma głowy do fizy i ma tylko 2(mierny)
"Nr 5" - Janek ma z "anglika" 3(dostateczny), z chemii 4(dobry) a z "niemca" 6(celujący), bo ma dziadka w Reich'u
"Nr 4" - Tomek uznaje tylko języki słowiaoskie i z "niemca" ma 1(pałę)
Mam nadzieję, że jak dotąd wszystko było oczywiste. Relacyjna baza danych to pewien program do przechowywania danych.
Wyboraźmy sobie więc owe 4 tabele:
Oceny
Uczniowie
PrzedmiotUczeoOcena
Przedmioty
A teraz jak to zrobid w programie Access 2007: Będziemy korzystad z pustej bazy danych.
W menu Tworzenie możemy stworzyd tabelę.
By zmienid nazwę kolumny zamiast „pole1” czy „pole2” wystarczy zaznaczyd kolumnę (kliknąd na kolumnę lewym przyciskiem myszy) a następnie kliknąd prawym przyciskiem myszy i wybrad z menu podręcznego opcję „zmieo nazwę kolumny”.
Jeśli chcemy usunąd z tabeli Przedmioty klucz podstawowy i usunąd pole „identyfikator” musimy przejśd do opcji WIDOK PROJEKTU TABELI i gdy pojawi się okno takie jak poniżej kliknąd przycisk „klucz podstawowy”:
Wszystkie tabele będą takie jak w oknie poniżej:
Teraz utworzymy relacje między tabelami:
Pamiętajmy aby pierwsze pola w każdej tabeli były „kluczami podstawowymi”.
Przeciągając pole „Przedmiot” z tabeli „Przedmioty” do tabeli „PrzedmiotUczeoOcena” i pole „Przedmiot” pojawi się okno takie jak poniżej gdzie tylko zatwierdzamy opcję „Utwórz”.
Kiedy już mamy utworzone relacje możemy korzystad z możliwości bazy danych.
1. Formularze. W tym celu przejdź do zakładki „Tworzenie” i tam znajdź opcję „Formularz”. Dla bieżąco
zaznaczonej tabeli utworzy się najprostszy formularz który teraz można dowolnie przeglądad, formatowad jego wygląd (kolor, czcionki, układ pól).
Korzystaj z możliwości:
Sprawdź, która co pozwala zrobid z tabelą i formularzem dla takiej tabeli.
Formularz tworzony samemu nie jest trudny do wykonania (opcja „Projekt formularza”). Np.:
Każdy z utworzonych formularzy można zapisad jako:
Wystarczy w lewym menu zaznaczyd odpowiedni formularz i korzystając z menu podręcznego (prawy przycisk myszy) wybrad odpowiednią opcję.
2. Kwerendy – przeszukiwanie bazy danych (jednej tabeli bądź wielu tabel) ze względu na wybrane kryteria.
Możemy je tworzyd na dwa sposoby: korzystając z kreatora kwerend albo tworząc je samemu.
Menu jest proste:
Kreator kwerend pozwala tworzyd kwerendy zupełnie początkującym użytkownikom. Ja zachęcam od razu do korzystania z drugiej opcji „Projekt kwerendy”.
Musimy teraz wybrad tabelę z której chcemy wyświetlid informacje. Może to byd więcej niż jedna tabela. My na razie wybierzemy tylko tabelę Uczniowie. Klikamy przycisk „Dodaj” a następnie „Zamknij”.
Pojawi się następujący ekran:
Teraz jeśli chcemy wybrad tylko niektóre informacje do wyświetlenia, np. tylko Id i imie, albo tylko imię i nazwisko, to w kolejnych kolumnach wybieramy te pola jak poniżej:
Albo jeśli chcemy wszystko wyświetlid wystarczy wybrad opcję „Uczniowie.*” oznaczające wszystkie pola z tabeli Uczniowie. Zapamiętaj, że symbol „*” oznacza wszystkie pola z danej tabeli.
Aby zobaczyd efekt wykonania kwerendy znajdź w menu programu przycisk (opcję) „Uruchom”
Efekt jest następujący:
Spróbuj sprawdzid opcje dostępny w menu „Widok”
Spróbuj sprawdzid co kryje się pod opcją „Widok SQL”.
Jak widad ukrywa się pod nim napisana tekstowo procedura kwerendy:
Teraz musisz trochę pouczyd się SQL’a.
http://office.microsoft.com/pl-pl/access-help/jezyk-access-sql-podstawowe-pojecia-sownictwo-i-skadnia- HA010256402.aspx
oraz
http://www.zie.pg.gda.pl/md/bazy_danych/Sql.pdf
Wracamy do dwiczeo:
Stworzenie bazy danych o poprawnej strukturze to jeden problem. Poprawnie skonstruowane tabele i relacje między nimi ułatwiają wprowadzanie danych oraz utrzymanie porządku w bazie. Stworzone przez nas formularz ułatwiają nam wprowadzanie danych do poszczególnych tablic. Jednak, aby wybrad z bazy to czego szukamy, potrzebujemy umiejętności posługiwania się językiem SQL (tzn. Structured Query Language). Dopiero znając SQL jesteśmy w stanie wydobyd z bazy dane, które nas interesują w danym momencie. SQL jest bardzo elastycznym językiem pozwalającym operatorowi bazy danych wybrad dane zgodne z założonymi wcześniej kryteriami. W zależności, z jakiego programu korzystamy, istnieją pewne kosmetyczne różnice w zapisie instrukcji SQL (należy wtedy przejrzed dokumentację programu, jednak podstawowy zapis instrukcji SQL jest podobny.
klucz podstawowy - jest to pole tabeli identyfikujące jednoznacznie dany rekord. Innymi słowy, każde pole w kolumnie klucza podstawowego ma inną niepowtarzalną wartośd. W tabeli Uczniowie będzie to kolumna "ID"
SELECT - jest to podstawowa instrukcja języka SQL i zawsze występuje w parze z klauzulą FROM. Najprostsza postad instrukcji może wyglądad jak poniżej:
SELECT * FROM Uczniowie
Wynikiem powyższej instrukcji będzie taka sama tabela danych, z której korzystaliśmy jako ze źródła. Spytacie więc po co taka prosta instrukcja skoro nie daje nam nic. Jeżeli zamiast * wstawimy nazwę jednego lub kilku pól oddzielonych przecinkami, to otrzymamy jako wynik zawartośd wyszczególnionych tylko pól.
Jeżeli chcemy pobrad dane z kilku tabel to należy:
gdy w różnych tabelach istneją pola o tej samej nazwie, przed nazwą pola umieścid nazwę tabeli z której pochodzi pole i kropkę np.: Uczniowie.Imię
gdy nazwy pól nie powtarzają się w różnych tabelach to wpisywanie przed nie nazwy tabel, z których pochodzą, nie jest konieczne
*-symbol ten oznacza, że pobierane są wszystkie kolumny z tabeli
FROM-klauzula ta wskazuje, o które tabele nam chodzi tzn. z których tabel chcemy pobrad dane. W przypadku pobierania danych z kilku tabel, nazwy tabel oddzielamy przecinkami (,).
WHERE-klauzula ta pozwala ustalid warunek jak i muszą spełniad rekordy tabeli, aby były wybrane przez zapytanie.
Najprostszy warunek może wyglądad tak:
SELECT * FROM Uczniowie WHERE Imię="Jasio"
Z tabeli Uczniowie zostaną wybrane tylko rekordy, w których pole "Imię" będzie miało wartośd "Jasio" (W naszym wypadku będzie to pierwszy rekord tabeli). Klauzula WHERE może także służyd do prostego połączenia dwóch tabel, w których istnieją te same pole. Jeżeli chcemy np.: wybrad uczniów, którzy mają z niemieckiego jakieś stopnie, ale chcemy
ponadto znad ich nazwiska i imiona zbudujemy następujące zapytanie dla tabel "Uczniowie" oraz
"PrzedmiotUczeoOcena":
SELECT Uczniowie.Imię, Uczniowie.Nazwisko, PrzedmiotUczeoOcena.Przedmiot, PrzedmiotUczeoOcena.Ocena FROM Uczniowie, PrzedmiotUczeoOcena WHERE PrzedmiotUczeoOcena.Uczeo=Uczniowie.ID AND PrzedmiotUczeoOcena.Przedmiot='Niemiecki'
Zapytanie wybierze nam dane:
Imię Nazwisko Przedmiot Ocena Janek Zetowski Niemiecki 6 Tomek Iksioski Niemiecki 1
DISTINCT - klauzula ta pozwala na wybranie różnych rekordów z zestawu przez nas zaproponowanego. Najlepiej wyjaśni to przykład:Instrukcja:
SELECT DISTINCT * FROM PrzedmiotUczeoOcena
wybierze całą tabelę PrzedmiotUczeoOcena. natomiast instrukcja:
SELECT DISTINCT Przedmiot, Uczeo FROM PrzedmiotUczeoOcena zwróci dane:
Przedmiot Uczeo Matematyka 1 Fizyka 3 Angielski 5 Chemia 5 Niemiecki 5 Niemiecki 4
Wynika z tego, że klauzula DISTINCT "działa" tylko W WYBRANYM PRZEZ NAS ZAKRESIE (tzn. porównuje wybrany przez nas zakres!)
AND-jest to operator, dzięki któremu możemy tworzyd warunki złożone. Jest on odpowiednikiem matematycznej koniunkcji.
OR-jest to operator, dzięki któremu możemy tworzyd warunki złożone. Jest odpowiednikiem matematycznej alternatywy. Przeciwieostwo "AND"
IN-operator ten sprawdza czy dana wartośd jest zawarta w określonym zbiorze Elementy zbioru są zawarte w parze nawiasów i pooddzielane od siebie przecinkami.
BETWEEN-operator ten sprawdza czy wartośd zawiera się w przedziale przez nas podanym.
LIKE-operator ten jest bardzo przydatnym i elastycznym operatorem Operator ten służy do odnajdywania tylko pól tekstowych. Możemy też zastosowad w nim maski na jeden lub kilka znaków (tak jak przy wyszukiwaniu plików w komputerze. W zależności od odmiany SQL z jakiej korzystamy, znaki te mają różną postad Poniższy przykład zobrazuje nam użycie 5 ostatnich operatorów:
SELECT * FROM PrzedmitUczeoOcena WHERE Przedmiot LIKE "%e%" AND (Uczeo BETWEEN 1 AND 4) AND (Ocena IN (1,2,3) OR Ocena=6)
Powyższe zapytanie wybrałoby: wszystkie kolumny tabeli PrzedmiotUczeoOcena z rekordami, w których nazwa przedmiotu zawierad będzie w swojej nazwie "e", uczniowie mają numery od "1" do "4" i posiadają oceny "1", "2", "3"
lub "6"
SUM- jest to funkcja agregująca obliczająca sumę wartości pola dla zbioru rekordów.
AVG- funkcja agregująca obliczająca średnią wartośd pola dla zbioru rekordów.
MIN- funkcja agregująca znajdująca wartośd minimalną pola dla zbioru rekordów.
MAX- funkcja znajduje wartośd maksymalną zakresu rekordów.
COUNT- zlicza ilośd rekordów z zestawu dla określonego kryterium.
SELECT SUM(PrzedmiotUczeoOcena.Ocena), COUNT(PrzedmiotUczeoOcena.Przedmiot) FROM PrzedmiotUczeoOcena Powyższe zapytanie zwróci nam dwie kolumny z dwoma wartościami: całkowitą sumą wszystkich ocen z tabeli PrzedmiotUczeoOcena oraz liczbę tych stopni.
Poniżej znajdują się polecenia przydatne przy tworzeniu tabel, usuwaniu tabel, zmianie ich struktury, dodawaniu indeksów, usuwaniu indeksów, dodawaniu oraz usuwaniu danych.
CREATE TABLE - tworzy tabelę o danej strukturze. Poniższe polecenie utworzy tabelę ze strony 1 PRZEDMIOTY o dwóch polach: Przedmiot (pole typu znakowego o możliwości wpisania 10 znaków) i pole OpiniaOPrzedmiocie (pole o 30 znakach). Kluczem głównym (tzn. polem, wktórym wartości nie będą się powtarzały w tabeli) będzie pole Przedmiot.
CREATE TABLE Przedmioty ( Przedmiot CHAR(10), OpiniaOPrzedmiocie CHAR(30) PRIMARY KEY (Przedmiot));
DROP TABLE - usuwa całkowicie całą tabelę z bazy danych (dane i strukturę tabeli). Podobnym poleceniem jest polecenie DELETE, z tą jednak różnicą, że usuwa ono dane z tabeli pozostawiając samą tabelę w bazie danych tak, że możliwe jest jej ponowne wypełnienie w przyszłości. Przykład poniżej usuwa całkowicie tabelę PRZEDMIOTY
DROP TABLE Przedmioty;
DELETE - polecenie to pozwala na wymazanie danych z tabeli nie niszcząc samej struktury tabeli w przeciwieostwie do polecenia DROP. Polecenie:
DELETE FROM Przedmioty;
usunie wszystkie dane z tabeli Przedmioty. Ażeby usunąd tylko wybrane rekordy spełniające odpowiedni warunek należy skorzystad z klauzuli WHERE. Tak więc polecenie
DELETE FROM Przedmioty WHERE Przedmiot="Matematyka"
spowoduje wymazanie z tabeli rekordu, w którym pole Przedmiot="Matematyka" pozostawiając inne rekordy.
CREATE INDEX - polecenie to tworzy indeks w polu danej tabeli. Indeksy są bardzo przydatne przy wyszukiwaniu danych w tabelach. Nie należy jednak nadużywad ich gdyż mogą spowalniad działanie bazy danch, gdyż np. każde wprowadzenia nowego rekordu powoduje przebudowę struktury indeksów.
CREATE INDEX PrzedmInd ON Przedmioty Przedmiot;
tworzy indeks o nazwie PrzedmInd na polu Przedmiot tabeli PRZEDMIOTY
DROP INDEX - polecenie to jest odwrotne do polecenia CREATE INDEX. i usuwa nideks z pola w tabeli. Przykład polecenia wygląda jak poniżej:
DROP INDEX Przedmioty.PrzedmInd;
INSERT - jest to instrukcja umożliwiająca wstawienie nowego rekordu do tabeli. W przypadku wstawienia danych do tabeli PRZEDMIOTY polecenie wyglądałoby tak:
INSERT INTO Przedmioty (Przedmiot, OpiniaOPrzedmiocie) VALUES ("Matematyka", "królowa nauk");
UPDATE - instrukcja ta służy do modyfikacji rekordów istniejących już w tabeli. Przy użyciu tej instrukcji możliwa jest zmiana jednego, kilku lub wszystkich rekordów tabeli
UPDATE Przedmioty SET OpiniaOPrzedmiocie ="bardzo trudny język"
Wtedy wszystkie rekordy w tabeli PRZEDMIOTY będą miały w polu OpiniaOPrzedmiocie wartośd "bardzo trudny język"
UPDATE Przedmioty SET OpiniaOPrzedmiocie="trudny" WHERE Przedmiot="Matematyka"
W rekordzie "Matematyka" wartośd pola OpiniaOPrzedmiocie zmieni się z "królowa nauk" na "trudny"
ALTER TABLE -polecenie to zmienia strukturę tabeli, tzn. przy jej pomocy możliwe jest usunięcie lub dodanie pól. Przy dodawaniu pola używamy akcji ADD i należy podad rodzaj pola i jego ewentualny rozmiar. Przy usuwaniu pola należy dopisad akcję DROP. W tym wypadku oprócz nazwy pola nie są wymagane żadne inne informacje
ALTER TABLE Przyklady DROP OpiniaOPrzedmiocie ADD NazwiskoNauczyciela CHAR(25);
Powyższa instrukcja usuwa z tabeli PRZEDMIOTY pole OpiniaOPrzedmiocie oraz dodaje pole NazwiskoNauczyciela typu znakowego o maksymalnej ilości wpisywanych znaków 25.
GROUP BY -polecenie to służy do grupowania rekordów w tabeli.
Weźmy pod uwagę bardzo hipotetyczną sytuację: szkoła wypłaca uczniom pieniądze za otrzymane stopnie (za 6-
"celujący" 6zł, za 5-"bardzo dobry" 5zł, ... , za 1-"mierny" 1zł). Pani księgowa siada do komputera i zlicza, ile pieniędzy musi byd w kasie pancernej na wypłaty dla uczniów.:). To by dopiero była szkoła. Jest jeszcze tylko jeden warunek. Pan dyrektor chce wiedzied ile przypada pieniędzy na dany przedmiot (za dobry nauczyciel musi zostad usunięty ze szkoły, bo kosztuje za dużo pieniędzy :). Zakładamy, że w szkole jest jeden nauczyciel od danego przedmiotu.)Co więc robi pani księgowa?. Wchodzi do bazy danych i wpisuje polecenie:
SELECT Przedmiot, SUM(Ocena) FROM PrzedmiotUczenOcena GROUP BY Przedmiot.
Nasza kochana pani księgowa otrzymuje następujący wynik:
Matematyka 8 Fizyka 2 Angielski 3 Chemia 4 Niemiecki 7
Tak więc w pierwszej kolejności "poleci" nauczyciel z matematyki.
ORDER BY- bardzo łatwe polecenie wprwadzające porządek w naszych danych.
Biorąc pod uwagę przykład z klauzuli GROUP BY przy poleceniu:
SELECT Przedmiot, SUM(Ocena) FROM PrzedmiotUczenOcena GROUP BY Przedmiot ORDER BY Przedmiot Nasze dany wyglądałyby w następujący sposób:
Angielski 3 Chemia 4 Fizyka 2 Matematyka 8 Niemiecki 7
Czyż nie wygląda to ładniej? :).
HAVING- klauzula ta zawęża grupy rekordów wybranych przy pomocy klauzuli GROUP BY.
Jak to zwykle bywa, przykład pokaże to lepiej niż cała strona objaśnieo.Biorąc pod uwagę przykład z klauzulą GROUP BY:
Przypuśdmy, że dyrektor szkoły chce znad nazwy przedmiotów, z powodu których szkoła musi wypłacid więcej niż 5zł.
Sama klauzula GROUP BY powoduje tylko zgrupowanie danych wg grup jakiejś kolumny. Gdy chcemy wprowadzid jakiś dodatkowy warunek (u nas: przedmioty, za które trzeba wypłacid więcej niż 5zł) używamy dodatkowo klauzuli HAVING.
Nasza biedna, zapracowana księgowa musi wpisad do komputera polecenie:
SELECT Przedmiot, SUM(Ocena) FROM PrzedmiotUczenOcena GROUP BY Przedmiot HAVING SUM(Ocena)>5 Nasza księgowa otrzyma na monitorze wynik:
Matematyka 8 Niemiecki 7
CREATE DATABASE- Polecenie to tworzy bazę danych o określonej nazwie. Polecenie to tworzy tylko bazę danych i nie
"uruchamia" jej. Aby to zrobid należy użyd polecenia USE CREATE DATABASE pawlinka;
USE- Polecenie to otwiera bazę danych o określonej nazwie.
USE pawlinka;
W mySQL wykonanie tego polecenia powoduje wyświetlenie w terminalu komunikatu "Database changed".
SHOW DATABASES- Polecenie to pokazuje bazy danych dostępne na serwerze.
SHOW DATABASES;
Występuje w mySQL
SHOW TABLES- Polecenie to pokazuje wszystkie tabele w danej bazie danych.
SHOW TABLES;
Występuje w mySQL
DESCRIBE- Polecenie to pokazuje strukturę tabeli o podanej nazwie.
DESCRIBE Przedmioty;
Występuje w mySQL. Po wykonaniu polecenia ukazuje się struktura bazy danych w formie tabeli.
