Bazy Danych
wykład
Wprowadzenie do SQL (Access a)
Typy danych w Access
Typ danych Opis
Text tekst lub kombinacja liter i cyfr, maksymalna wielkość pola jest ograniczona do 255
znaków, należy pamiętać że wielkość a jest zawsze taka jak została zdefiniowana.
Memo tekst lub kombinacja liter i cyfr, maksymalna wielkość pola jest ograniczona do 64 tysięcy
znaków, należy pamiętać że wielkość a jest zawsze taka jak została zdefiniowana.
Number dane numeryczne używane do obliczeń. Pole to można zdefiniować jako Byte, Integer,
Long Integer, Single, Double, Replication ID.
Date/Time Data i czas, od 100 do 9999 roku.
Currency do zapisu kwot pieniężnych lub numerycznych, używające formatu stałoprzecinkowego
do zwiększenia dokładności obliczeń. Jedno do czterech miejsc dziesiętnych i 15 miejsc
całkowitych.
AutoNumber automatycznie generowany numer, mogący służyć jako identyfikator, nie może być
zmieniany, jest nadawany automatycznie podczas tworzenia nowego rekordu.
Yes/No pole wartości logicznych operujących na wartościach prawda (true) lub fałsz (false).
OLE Object dowolne dane binarny nie przekraczające rozmiaru 1GB: grafika, dz
więk, dokument
edytora tekstów, arkusz kalkulacyjny.
Lookup Wizard Tworzy pole wyboru z listy. Lista może być utworzona na podstawie istniejącej tabeli,
bądz
tworzona jest nowa tabela, jako definicja pola. Typ danych tego pola jest zgodny z
jednym z wyżej wymienionych typów.
Tworzenie i modyfikacja tabel
Tworzenie tabeli
CREATE TABLE tabela
(pole_1 typ [(rozmiar)] [NOT NULL] [indeks_1]
[, pole_2 typ [(rozmiar)] [NOT NULL] [indeks_2] [, ...]]
[, CONSTRAINT indeks_wielopolowy [, ...]])
Tworzenie indeksów
CREATE [ UNIQUE ] INDEX indeks
ON tabela (pole [ASC|DESC][, pole [ASC|DESC], ...])
[WITH { PRIMARY | DISALLOW NULL | IGNORE NULL }]
Aby utworzyć indeks dla pojedynczego pola, należy za nazwą tabeli umieścić w nawiasach nazwę pola.
Aby utworzyć indeks dla wielu pól, należy podać nazwę każdego pola, które ma być włączone do indeksu. Aby
utworzyć indeksy malejące, należy użyć słowa zastrzeżonego DESC; w przeciwnym przypadku indeksy będą
uporządkowane rosnąco.
Aby zapobiec występowaniu powtarzających się wartości w polu lub polach indeksowanych, należy
użyć słowa zastrzeżonego UNIQUE.
Posługując się opcjonalną klauzulą WITH, można określić reguły poprawności danych. Na przykład:
" Stosując opcję DISALLOW NULL, można zapobiec występowaniu wartości Null w polu lub polach
indeksowanych nowych rekordów.
" Stosując opcję IGNORE NULL, można zapobiec dołączaniu do indeksu rekordów zawierających w polu
lub polach indeksowanych wartości Null.
" Dodając słowo zastrzeżone PRIMARY, można określić indeksowane pole lub pola jako klucz podstawowy.
Pociąga to za sobą unikatowość, tak więc można wówczas pominąć słowo zastrzeżone UNIQUE.
Modyfikacja istniejącej tabeli
ALTER TABLE tabela {ADD {COLUMN typ_pola[(rozmiar)] [NOT NULL]
[CONSTRAINT indeks] |
CONSTRAINT indeks_wielopolowy} |
DROP {COLUMN pole I CONSTRAINT nazwa_indeksu} }
Za pomocą instrukcji ALTER TABLE można zmienić istniejącą tabelę na kilka sposobów:
opracował: mgr inż. Tomasz Karczewski str. 1
Bazy Danych
wykład
" Klauzula ADD COLUMN powoduje dodanie nowego pola do tabeli. Należy określić nazwę pola, typ
danych oraz opcjonalnie rozmiar (dla pół tekstowych i binarnych).
" Klauzula DROP COLUMN powoduje usunięcie pola. W tym celu należy określić jedynie nazwę pola.
" Klauzula ADD CONSTRAINT pozwala na zdefiniowanie indeksu.
" Klauzula DROP CONSTRAINT powoduje usunięcie indeksu wielopolowego. W tym celu należy po słowie
zastrzeżonym CONSTRAINT określić jedynie nazwę indeksu.
Definiowanie indeksów
Indeks dla pojedynczego pola:
CONSTRAINT nazwa {PRIMARY KEY | UNIQUE | NOT NULL |
REFERENCES tabela_obca [(pole_obce_1, pole_obce_2)]}
Indeks dla wielu pól:
CONSTRAINT nazwa
{PRIMARY KEY (podstawowe_1[, podstawowe_2 [, ...]]) |
UNIQUE (unikatowe_1[, unikatowe_2 [, ...]]) |
NOT NULL (niepuste_1[, niepuste_2 [, ...]]) |
FOREIGN KEY (odesłanie_1[, odesłanie_2 [, ...]]) REFERENCES tabela_obca
[(pole_obce_1[, pole_obce_2 [, ...]])]}
Składnia ograniczenia dla pojedynczego pola w klauzuli definiującej pole w instrukcji ALTER TABLE lub
CREATE TABLE występuje zaraz po specyfikacji typu danych pola.
Składnia ograniczenia dla wielu pól jest używana wtedy, gdy słowo zastrzeżone CONSTRAINT występuje
poza klauzulą definicji pola w instrukcji ALTER TABLE lub CREATE TABLE.
Za pomocą klauzuli CONSTRAINT można określić dla pola następujące typy ograniczenia:
" Za pomocą słowa zastrzeżonego UNIQUE można określić pole jako klucz unikatowy. Oznacza to, że żadne
dwa rekordy w tabeli nie mogą mieć w tym polu takiej samej wartości. Jako unikatowe można określić
dowolne pole lub listę pól. Jeśli w jednym ograniczeniu wiele pól jest określonych jako klucz unikatowy,
unikatowe muszą być wartości tych pól traktowane jako całość, natomiast w pojedynczych polach wartości
mogą się powtarzać.
" Za pomocą zastrzeżonego słowa PRIMARY KEY można określić pojedyncze pole lub zbiór pól w tabeli
jako klucz podstawowy. Wszystkie wartości klucza podstawowego muszą być unikatowe i mieć wartości
różne od Null, a tabela może zawierać tylko jeden klucz podstawowy.
" Za pomocą słów zastrzeżonych FOREIGN KEY można określić pola jako klucz obcy. Jeśli klucz
podstawowy tabeli obcej składa się z więcej niż jednego pola, należy użyć definicji ograniczenia dla wielu
pól, wymieniając wszystkie wskazujące pola, nazwę tabeli obcej oraz nazwy wskazywanych pól w tabeli
obcej w tej samej kolejności, co pola wskazujące. Jeśli wskazywane pole lub pola są kluczami
podstawowymi tabeli obcej, to nie jest konieczne określenie wskazywanych pól - domyślnie, aparat bazy
danych działa tak, jakby pola klucza podstawowego tabeli obcej były polami wskazywanymi.
Usuwanie tabeli
DROP {TABLE tabela | INDEX indeks ON tabela}
Manipulowanie danymi
Wybieranie danych
SELECT [predykat] { * | tabela.* | [tabela.]pole1 [AS alias1] [,
[tabela.]pole2 [AS alias2] [, ...]]}
FROM wyrażenie_tabelowe [, ...] [IN zewnętrzna_baza_danych]
[WHERE... ]
[GROUP BY... ]
[HAVING... ]
[ORDER BY... ]
[WITH OWNERACCESS OPTION]
opracował: mgr inż. Tomasz Karczewski str. 2
Bazy Danych
wykład
Opis predykatów:
Element Opis
ALL Predykat przyjmowany domyślnie, jeśli jawnie nie podano żadnego innego, aparat bazy
danych wybiera wszystkie rekordy spełniające kryteria podane w instrukcji SQL.
DISTINCT Pomijane są te rekordy, które zawierają powtarzające się wartości we wszystkich
wybieranych polach. Aby pojawić się w wyniku kwerendy, wartości każdego z pól
wymienionych w instrukcji SELECT muszą być niepowtarzalne.
DISTINCTROW Pomija powtarzające się dane na podstawie wartości całych wierszy, a nie jedynie
wybranych pól.
TOP n Zwraca tylko te wiersze, które mieszczą się (od góry lub od dołu) w podanym zakresie
[PERCENT] rekordów uporządkowanych klauzulą ORDER BY.
Dodawanie danych
Instrukcja umożliwiająca dodanie wielu wierszy:
INSERT INTO cel [IN zewnętrzna_baza_danych] [(pole_1[,pole_2[, ...]])]
SELECT [z
ródło.] pole_1[,pole_2[, ...]
FROM wyrażenie_tabelowe
Instrukcja dołączająca pojedynczy wiersz:
INSERT INTO cel [(pole_1[,pole_2[, ...]])]
VALUES (wartość_1[,wartość_2[, ...])
Aktualizacja danych
Składnia:
UPDATE tabela
SET nowa_wartość
WHERE kryteria
Przykład:
UPDATE Zamówienia
SET WartośćZamówienia = WartośćZamówienia * 1.1,
Fracht = Fracht * 1.03
WHERE KrajOdbiorcy = 'UK';
Usuwanie danych
DELETE [tabela.*]
FROM tabela
WHERE kryteria
Rozwiązanie przykładowego problemu
Definicja zadania
Należy zaprojektować strukturę danych w której będą przechowywane informacje na temat studentów
oraz pracowników uczelni.
O studentach należy przechowywać następujące informacje:
" Dane osobowe,
" Kierunek na którym studiuje student (student może jednocześnie uczęszczać na dwa kierunki).
O pracownikach należy przechowywać następujące informacje:
" Dane osobowe,
" Instytut w którym zatrudniony jest pracownik (pracownik może jednocześnie być zatrudniony przez dwa
instytuty).
Inne informacje:
Instytuty mają nadrzędną jednostkę, którą są wydziały. Instytut może istnieć tylko w ramach jednego wydziału.
Kierunki są prowadzone przez wydziały. Każdy kierunek może być prowadzony tylko przez jeden wydział,
natomiast pracownicy z rożnych wydziałów mogą uczyć na jednym kierunku.
opracował: mgr inż. Tomasz Karczewski str. 3
Bazy Danych
wykład
Koncepcja struktury bazy danych
Osoba
S_Ulica
S_Miejscowosc
Osoba_Miejscowosc
ID_Osoby NO
ID_Ulicy NO
ID_Miejscowosci NO
Imie TXT25
Nazwa_Ulicy TXT40
Nazwa_Miejscowosci TXT30
Nazwisko TXT30
1..1 0..n
1..1
Osoba_ulica
0..n
Nazwisko_Rodowe TXT30
Nr_Domu TXT5
Nr_Mieszkania TXT5
0..1
Kod_Pocztowy TXT5
Osoba_Student
0..1
1..1 Student
S_Tytuly
ID_Studenta NO
ID_Tytulu NO
Osoba_wykladowca
Nr_Grupy TXT6
Tytul TXT20
Podgrupa_Lab TXT2
1..1
0..n
Tytul_Wykladowca
1..n
1..1 Wykladowca
Wybrane_Kierunki
ID_Wykladowcy NO
1..n
S_Kierunek
1..n
ID_Kierunku NO
Nazwa_Kieunku TXT40
Instytuty_Zatrudnienia
1..n
1..n
Kierunki_Na_Wydziale
S_Instytut
1..n
ID_Instytutu NO
Nazwa_Instytutu TXT40
1..1
Instytuty_Na_Wydziale0..n S_Wydzial
Symbol_Instytutu TXT5
ID_Wydzialu NO
Nazwa_Wydzialu TXT40
Symbol_Wydzialu TXT
Struktura danych stworzona na podstawie opracowanej koncepcji
Osoba
S_Ty tuly
ID_Osoby COUNTER
ID_Ty tulu COUNTER ID_Studenta LONG Ty tul TEXT(20) ID_Ulicy LONG ID_Miejscowosci LONG ID_Wy kladowcy LONG ID_Miejscowosci = ID_Miejscowosci
Imie TEXT(25)
S_Miejscowosc
ID_Wy ladowcy = ID_Wy kladowcy
ID_Ty tulu = ID_Tykulu
t
Nazwisko TEXT(30)
ID_Miejscowosci COUNTER Nazwisko_Rodowe TEXT(30)
Nazwa_Miejscowosci TEXT(30) ID_Osoby = ID_OsoNr_Domu TEXT(5)
by
Nr_Mieszkania TEXT(5)
Wy kladowca K d P t TEXT(5)
ID_Studenta = ID_Studenta
ID_Wy kladowcy COUNTER
ID_Osoby = ID_Osoby
ID_Ty tulu LONG ID_Ulicy = ID_Ulicy
ID_Osoby LONG Student
ID_Studenta COUNTER
S_Ulica
ID_Wy kladowcy = ID_Wy kladowcy
ID_Osoby LONG
ID_Ulicy COUNTER Nr_Grupy TEXT(6)
Nazwa_Ulicy TEXT(40) Podgrupa_Lab TEXT(2)
Insty tuty _Zatrudnienia
ID_Studenta = ID_Studenta
ID_Insty tutu LONG Kierunki_Na_Wy dziale
ID_Wy kladowcy LONG ID_Wy dzialu LONG Wy brane_Kierunki
ID_Kierunku LONG ID_Insty tutu = ID_Insty tutu
ID_Kierunku LONG ID_Studenta LONG S_Insty tut
ID_Insty tutu COUNTER ID_Kierunku = ID_Kierunku
ID_Wy dzialu LONG ID_Kierunku = ID_Kierunku
ID_Wy dzialu = ID_Wy dzialu
Nazwa_Insty tutu TEXT(40) Sy mbol_Insty tutu TEXT(5)
ID_Wy dzialu = ID_Wy dzialu
S_Kierunek
ID_Kierunku COUNTER S_Wy dzial
Nazwa_Kieunku TEXT(40) ID_Wy dzialu COUNTER
Nazwa_Wy dzialu TEXT(40)
Sy mbol_Wy dzialu LONGTEXT
opracował: mgr inż. Tomasz Karczewski str. 4
Bazy Danych
wykład
Fizyczna implementacja bazy
alter table S_Instytut Imie TEXT(25) not null,
drop constraint FK_S_INSTYT_INSTYTUTY_S_WYDZIA; Nazwisko TEXT(30) not null,
alter table Instytuty_Zatrudnienia Nazwisko_Rodowe TEXT(30) not null,
drop constraint FK_INSTYTUT_INSTYTUTY_S_INSTYT; Nr_Domu TEXT(5) not null,
alter table Instytuty_Zatrudnienia Nr_Mieszkania TEXT(5),
drop constraint FK_INSTYTUT_INSTYTUTY_WYKLADOW; Kod_Pocztowy TEXT(5) not null,
constraint PK_OSOBA primary key (ID_Osoby)
alter table Kierunki_Na_Wydziale );
drop constraint FK_KIERUNKI_KIERUNKI__S_WYDZIA; create table S_Instytut
(
alter table Kierunki_Na_Wydziale ID_Instytutu COUNTER not null,
drop constraint FK_KIERUNKI_KIERUNKI__S_KIERUN; ID_Wydzialu LONG not null,
alter table Osoba Nazwa_Instytutu TEXT(40) not null,
drop constraint FK_OSOBA_OSOBA_MIE_S_MIEJSC; Symbol_Instytutu TEXT(5) not null,
alter table Osoba constraint PK_S_INSTYTUT primary key (ID_Instytutu)
drop constraint FK_OSOBA_OSOBA_STU_STUDENT; );
alter table Student create table S_Kierunek
drop constraint FK_STUDENT_OSOBA_STU_OSOBA; (
alter table Osoba ID_Kierunku COUNTER not null,
drop constraint FK_OSOBA_OSOBA_ULI_S_ULICA; Nazwa_Kieunku TEXT(40) not null,
alter table Wykladowca constraint PK_S_KIERUNEK primary key (ID_Kierunku)
drop constraint FK_WYKLADOW_OSOBA_WYK_OSOBA; );
alter table Osoba create table S_Miejscowosc
drop constraint FK_OSOBA_OSOBA_WYK_WYKLADOW; (
alter table Wykladowca ID_Miejscowosci COUNTER not null,
drop constraint FK_WYKLADOW_TYTUL_WYK_S_TYTULY; Nazwa_Miejscowosci TEXT(30) not null,
alter table Wybrane_Kierunki constraint PK_S_MIEJSCOWOSC primary key
drop constraint FK_WYBRANE__WYBRANE_K_S_KIERUN; (ID_Miejscowosci)
alter table Wybrane_Kierunki );
drop constraint FK_WYBRANE__WYBRANE_K_STUDENT; create table S_Tytuly
alter table S_Instytut (
drop constraint AK_AK_INSTYTUT_S_INSTYT; ID_Tytulu COUNTER not null,
alter table S_Kierunek Tytul TEXT(20) not null,
drop constraint AK_AK_KIERUNEK_S_KIERUN; constraint PK_S_TYTULY primary key (ID_Tytulu)
alter table S_Miejscowosc );
drop constraint AK_AK_MIEJSCOWOSC_S_MIEJSC; create table S_Ulica
alter table S_Tytuly (
drop constraint AK_AK_TYTUL_S_TYTULY; ID_Ulicy COUNTER not null,
alter table S_Ulica Nazwa_Ulicy TEXT(40) not null,
drop constraint AK_AK_ULICA_S_ULICA; constraint PK_S_ULICA primary key (ID_Ulicy)
alter table S_Wydzial );
drop constraint AK_AK_WYDZIAL_S_WYDZIA; create table S_Wydzial
alter table Wykladowca (
drop constraint AK_AK_WYKLADOWCY_WYKLADOW; ID_Wydzialu COUNTER not null,
drop table Instytuty_Zatrudnienia; Nazwa_Wydzialu TEXT(40) not null,
drop table Kierunki_Na_Wydziale; Symbol_Wydzialu LONGTEXT not null,
drop table Osoba; constraint PK_S_WYDZIAL primary key (ID_Wydzialu)
drop table S_Instytut; );
drop table S_Kierunek; create table Student
drop table S_Miejscowosc; (
drop table S_Tytuly; ID_Studenta COUNTER not null,
drop table S_Ulica; ID_Osoby LONG not null,
drop table S_Wydzial; Nr_Grupy TEXT(6) not null,
drop table Student; Podgrupa_Lab TEXT(2) not null,
drop table Wybrane_Kierunki; constraint PK_STUDENT primary key (ID_Studenta)
drop table Wykladowca; );
create table Instytuty_Zatrudnienia create table Wybrane_Kierunki
( (
ID_Instytutu LONG not null, ID_Kierunku LONG not null,
ID_Wykladowcy LONG not null, ID_Studenta LONG not null,
constraint PK_INSTYTUTY_ZATRUDNIENIA primary key constraint PK_WYBRANE_KIERUNKI primary key
(ID_Instytutu, ID_Wykladowcy) (ID_Kierunku, ID_Studenta)
); );
create table Kierunki_Na_Wydziale create table Wykladowca
( (
ID_Wydzialu LONG not null, ID_Wykladowcy COUNTER not null,
ID_Kierunku LONG not null, ID_Tytulu LONG not null,
constraint PK_KIERUNKI_NA_WYDZIALE primary key ID_Osoby LONG not null,
(ID_Wydzialu, ID_Kierunku) constraint PK_WYKLADOWCA primary key (ID_Wykladowcy)
); );
create table Osoba alter table S_Instytut
( add constraint AK_AK_INSTYTUT_S_INSTYT unique
ID_Osoby COUNTER not null, (Nazwa_Instytutu);
ID_Studenta LONG not null, alter table S_Kierunek
ID_Ulicy LONG not null, add constraint AK_AK_KIERUNEK_S_KIERUN unique
ID_Miejscowosci LONG not null, (Nazwa_Kieunku);
ID_Wykladowcy LONG not null, alter table S_Miejscowosc
opracował: mgr inż. Tomasz Karczewski str. 5
Bazy Danych
wykład
add constraint AK_AK_MIEJSCOWOSC_S_MIEJSC unique add constraint FK_OSOBA_OSOBA_MIE_S_MIEJSC foreign
(Nazwa_Miejscowosci); key (ID_Miejscowosci)
alter table S_Tytuly references S_Miejscowosc (ID_Miejscowosci);
add constraint AK_AK_TYTUL_S_TYTULY unique alter table Osoba
(Tytul); add constraint FK_OSOBA_OSOBA_STU_STUDENT foreign
alter table S_Ulica key (ID_Studenta)
add constraint AK_AK_ULICA_S_ULICA unique references Student (ID_Studenta);
(Nazwa_Ulicy); alter table Student
alter table S_Wydzial add constraint FK_STUDENT_OSOBA_STU_OSOBA foreign
add constraint AK_AK_WYDZIAL_S_WYDZIA unique key (ID_Osoby)
(Nazwa_Wydzialu); references Osoba (ID_Osoby);
alter table Wykladowca alter table Osoba
add constraint AK_AK_WYKLADOWCY_WYKLADOW unique add constraint FK_OSOBA_OSOBA_ULI_S_ULICA foreign
(ID_Tytulu, ID_Osoby); key (ID_Ulicy)
alter table S_Instytut references S_Ulica (ID_Ulicy);
add constraint FK_S_INSTYT_INSTYTUTY_S_WYDZIA alter table Wykladowca
foreign key (ID_Wydzialu) add constraint FK_WYKLADOW_OSOBA_WYK_OSOBA foreign
references S_Wydzial (ID_Wydzialu); key (ID_Osoby)
alter table Instytuty_Zatrudnienia references Osoba (ID_Osoby);
add constraint FK_INSTYTUT_INSTYTUTY_S_INSTYT alter table Osoba
foreign key (ID_Instytutu) add constraint FK_OSOBA_OSOBA_WYK_WYKLADOW foreign
references S_Instytut (ID_Instytutu); key (ID_Wykladowcy)
alter table Instytuty_Zatrudnienia references Wykladowca (ID_Wykladowcy);
add constraint FK_INSTYTUT_INSTYTUTY_WYKLADOW alter table Wykladowca
foreign key (ID_Wykladowcy) add constraint FK_WYKLADOW_TYTUL_WYK_S_TYTULY
references Wykladowca (ID_Wykladowcy); foreign key (ID_Tytulu)
alter table Kierunki_Na_Wydziale references S_Tytuly (ID_Tytulu);
add constraint FK_KIERUNKI_KIERUNKI__S_WYDZIA alter table Wybrane_Kierunki
foreign key (ID_Wydzialu) add constraint FK_WYBRANE__WYBRANE_K_S_KIERUN
references S_Wydzial (ID_Wydzialu); foreign key (ID_Kierunku)
alter table Kierunki_Na_Wydziale references S_Kierunek (ID_Kierunku);
add constraint FK_KIERUNKI_KIERUNKI__S_KIERUN alter table Wybrane_Kierunki
foreign key (ID_Kierunku) add constraint FK_WYBRANE__WYBRANE_K_STUDENT
references S_Kierunek (ID_Kierunku); foreign key (ID_Studenta)
alter table Osoba references Student (ID_Studenta);
opracował: mgr inż. Tomasz Karczewski str. 6