danych przy pomocy komend języka SQL
Większość z obecnych systemów bazodanowych udostępnia programy pozwalające w sposób graficzny zarządzać strukturą a także zawartością bazy danych. Programy są specyficznymi rodzajami klientów bazy, pozwalającymi zakładać nowe lub modyfikować istniejące tabele, a także usuwać, dodawać i modyfikować rekordy w bazie. Popularną aplikacją zarządzającą bazą Oracle jest program o nazwie TOAD. System MS Access z kolei łączy w sobie funkcje takiego klienta, dostarczając intuicyjnego środowiska do projektowania tabel, a także przeglądania i modyfikacji zawartych w nich danych. Docelowo jednak większość z tych programów i tak tłumaczy wykonane przez nas operacje w kliencie graficznym, na ciąg instrukcji języka SQL. Innymi słowy każdą operację możliwą do wykonania w programie zarządzającym bazą, można również wykonać poprzez wywoływanie odpowiednich poleceń SQL. Ich znajomość w połączeniu z wprawnym posługiwaniem się instrukcją SELECT pozwoli każdemu na pracę z dowolną bazą danych, niezależnie od systemu bazodanowego na jakim się ona znajduje.
5.1. Dodawanie nowych rekordów przy pomocy instrukcji INSERT
Aby dodawać nowe rekordy do istniejących już tabel posługujemy się instrukcją INSERT. Za jej pomocą możemy dodawać pojedyncze rekordy, lub też wstawiać do tabeli cały szereg rekordów zwróconych z podzapytania (utworzonego za pomocą instrukcji SELECT). W pojedynczej instrukcji INSERT możemy ładować dane tylko do jednej tabeli na raz.
5.1.1. Dodawanie pojedynczych rekordów
Składnię instrukcji INSERT dodającej do tabeli pojedynczy wiersz przedstawia Listing 1. Składa się ona z trzech zasadniczych elementów:
- nazwy tabeli do której chcemy dodać rekord
- opcjonalnej listy kolumn (pól), które chcemy wypełnić dodając nowy rekord - listy wartości, które wstawimy w określone kolumny (pola) dla nowo
dodawanego rekordu
INSERT INTO tabela [(kolumna1 [, kolumna2, ...])]
VALUES (wartosc1 [, wartosc2, ...]);
Listing 1: Składnia instrukcji INSERT dodającej pojedynczy rekord
Proste użycie tej instrukcji, powodujące dodanie pojedynczego rekordu do tabeli Oferty_Wycofane pokazuje Listing 2.
INSERT INTO Oferty_Wycofane (ID_Oferty,Powod,Data_Wycofania) VALUES (19, 'Rezygnacja ze sprzedaży', '2004-12-02');
Tabela Oferty_Wycofane przed dodaniem rekordu
Tabela Oferty_Wycofane po dodaniu rekordu
Listing 2: Użycie instrukcji INSERT w celu dodania pojedynczego rekordu
Po uruchomieniu dowolnej kwerendy zawierającej instrukcję INSERT MS Access pokaże komunikat wyświetlający ilość wierszy, które zostaną dodane w wyniku działania kwerendy, prosząc jednocześnie o potwierdzenie wykonania operacji (Rysunek 1).
Rysunek 1: Komunikat MS Access po uruchomieniu kwerendy z instrukcją INSERT
Kolejność i ilość wartości na liście po klauzuli VALUES powinna odpowiadać kolejności kolumn na liście podanej po nazwie tabeli. Użyte w Listing 2 wartości odpowiadały następującym kolumnom:
19 ID_Oferty
'Rezygnacja ze sprzedaży' Powod '2004-12-02' Data_Wycofania
Kolejność i ilość użytych kolumn nie musi dokładnie odpowiadać kolejności i ilości wszystkich kolumn występujących w tabeli. Istotnym jest jednak aby przy dodawaniu nowego rekordu wpisać wartości do wszystkich kolumn, które nie mogą być puste (czyli w projekcje tabeli ustawione są jako pola wymagane i nie mają określonej wartości domyślnej). Pozostałe pola, które mogą przyjmować wartość NULL (mogą być puste) lub / i mają określoną wartość domyślną, nie muszą być użyte w instrukcji INSERT. W tabeli Oferty_Wycofane pole przechowujące informacje na temat powodu wycofania auta z komisu jest opcjonalne. Listing 3 może je zatem pominąć w trakcie wstawiania nowego rekordu.
INSERT INTO Oferty_Wycofane (ID_Oferty, Data_Wycofania) VALUES (9, '2005-03-21');
Tabela Oferty_Wycofane przed dodaniem rekordu
Tabela Oferty_Wycofane po dodaniu rekordu
Listing 3: Użycie instrukcji INSERT do dodania rekordu zawierającego jedynie pola wymagane
W instrukcji INSERT możemy pominąć jawną listę kolumn, do których chcemy wpisać wartości. W takim wypadku na lista po klauzuli VALUES musi zawierać tę samą ilość oraz kolejność wartości, co lista wszystkich kolumn w tabeli. Dla pól opcjonalnych, jeżeli nie chcemy podawać ich wartości, musimy jawnie podać wartość NULL. Listing 4 pomija listę kolumn, a także nie wpisuje wartości do pola Powod.
INSERT INTO Oferty_Wycofane VALUES (21, '2005-04-15', NULL);
Tabela Oferty_Wycofane przed dodaniem rekordu
Tabela Oferty_Wycofane po dodaniu rekordu
Listing 4: Użycie instrukcji INSERT z pominięciem listy kolumn do wypełnienia
Pominięte w liście kolumn pola, będące typu autonumerowanego, bądź też posiadające wartości domyślne przy dodawaniu rekordu otrzymają odpowiednio właściwą wartość autonumeru, lub swoją wartość domyślną. Mimo, iż w niektórych systemach bazodanowych nie można jawnie podać wartości dla pola autonumerowanego, MS Access dopuszcza taką operację, pod warunkiem że wpisywana wartość będzie różna od istniejących już wpisów. W tabeli Sprzedawcy pole ID jest typu autonumerowanego, a pola Miejscowosc i Kraj
posiadają wartości domyślne odpowiednio: Wrocław i Polska. Pola ID, Nazwisko oraz Imie są polami wymaganymi. W pozostałych kolumnach wartości mogą być umieszczone opcjonalnie. Listing 5 dodaje nowy rekord do tabeli Sprzedawcy podając wartości jedynie dla pól Nazwisko i Imie. Pole ID otrzymuje numer automatyczny, a pola Miejscowosc i Kraj swoje wartości domyśle. Pozostałe kolumny w tabeli nie są wypełniane dla tego rekordu.
INSERT INTO Sprzedawcy (Nazwisko, Imie) VALUES ('Kowalski', 'Jan');
Tabela Sprzedawcy przed dodaniem rekordu
Tabela Sprzedawcy po dodaniu rekordu
Listing 5: Automatyczne uzupełnianie pól autonumerowanych lub posiadających wartości domyślne
5.1.2. Dodawanie serii rekordów otrzymanych z podzapytania
W instrukcji INSERT zastępując klauzulę VALUES odpowiednim podzapytaniem otrzymujemy możliwość dodania na raz więcej niż jednego rekordu. Pozostałe części instrukcji INSERT takie jak nazwa tabeli docelowej i lista kolumn przeznaczonych do wypełnienia pozostają bez zmian. Podobnie jak było w przypadku listy wartości po klauzuli VALUES, tak samo lista wyrażeń po instrukcji SELECT musi być zgodna z listą kolumn instrukcji INSERT.
Listing 6 za pomocą podzapytania wybiera z bieżącej oferty komisu auta wystawione na sprzedaż w 2004 roku i do tej pory niesprzedane, bądź jeszcze niewycofane. Następnie wstawia je do tabeli Oferty_Wycofane, jako datę wycofania podając bieżącą datę (wyrażenie DATE()).
Ponownie tak samo jak było przy dodawaniu pojedynczych rekordów, w przypadku nie podania listy wypełnianych kolumn w instrukcji INSERT, lista wyrażeń po instrukcji SELECT w podzapytaniu musi dokładnie odpowiadać ilości i kolejności kolumn w tabeli docelowej. Istnieje jednak jeden wyjątek od tej reguły.
Jeżeli w podzapytaniu nazwy zwracanych wyrażeń (kolumn) będą takie same jak nazwy kolumn w tabeli docelowej, wówczas nie musimy już zachowywać odpowiedniej ilości i kolejności tych wyrażeń. Przypadek ten ilustruje Listing 7.
Wykorzystując fakt, że struktury (a tym samym nazwy kolumn) dla tabel Sprzedawcy i Klienci są niemal identyczne konstruowane jest podzapytanie zwracające z tabeli Klienci tylko niektóre kolumny. Następnie wybierani są w nim klienci pochodzący z Wrocławia i wstawiani do tabeli Sprzedawcy jako nowi pracownicy komisu. Kolejność oraz ilość kolumn wybranych z tabeli Klienci nie jest zgodna z kolejnością i ilością kolumn w tabeli Sprzedawcy. Jednakże, mimo że w instrukcji INSERT nie została podana jawna lista kolumn, do których należy
wpisać wartości pochodzące z odpowiednich wyrażeń podzapytania, to dopasowanie jest wykonywane na podstawie zgodności nazw kolumn z obu tabel.
INSERT INTO Oferty_Wycofane (ID_Oferty,Data_Wycofania,Powod) SELECT O.ID, DATE(),'Wycofanie oferty 2004'
FROM ((Oferty AS O
LEFT JOIN Transakcje AS T ON O.ID = T.ID_Oferty)
LEFT JOIN Oferty_Wycofane AS OW ON O.ID = OW.ID_Oferty) WHERE (T.Data IS NULL) AND (OW.Data_Wycofania IS NULL)
AND (YEAR(O.Data_Wstawienia) = 2004);
Tabela Oferty_Wycofane przed dodaniem rekordów
Tabela Oferty_Wycofane po dodaniu rekordów
Listing 6: Użycie instrukcji INSERT z podzapytaniem
INSERT INTO Sprzedawcy
SELECT Miejscowosc, Ulica, Nr_Ulicy, Nazwisko, Kraj, Imie FROM Klienci
WHERE Miejscowosc = 'Wrocław';
Tabela Sprzedawcy przed dodaniem rekordów
Tabela Sprzedawcy po dodaniu rekordów
Listing 7: Użycie instrukcji INSERT z podzapytaniem i pominięciem listy kolumn do wypełnienia
5.1.3. Błędy zwracane przez instrukcję INSERT
W zależności od wpisów zgromadzonych już w tabeli, bądź też od rodzaju nowych danych, które chcemy dodać, w pewnych sytuacjach wykonanie instrukcji INSERT może zakończyć się niepowodzeniem. Nie musi to oczywiście dotyczyć wszystkich rekordów, które chcieliśmy dodać, a jedynie tych które zawierały pola z niepoprawnymi dla tej tabeli danymi, bądź też nie zawierały wszystkich wymaganych pól. W każdym z tych przypadków aparat bazy wyświetli odpowiedni komunikat z mniej lub bardziej szczegółową informacją na temat tego co się nie udało. Postać tego komunikatu w przypadku próby dodania rekordu o tej samej wartości klucza podstawowego do tabeli w środowisku MS Access prezentuje Listing 8.
INSERT INTO Oferty_Wycofane (ID_Oferty,Data_Wycofania,Powod) VALUES (21, '2005-06-25', 'Kasacja auta');
Tabela Oferty_Wycofane przed próbą dodania rekordu
Komunikat MS Access o niemożności dodania rekordu na skutek naruszenia klucza
Listing 8: Naruszenie klucza podstawowego przy próbie dodania pojedynczego rekordu
Komunikat ten wyświetla informację na temat ile rekordów i z jakiej przyczyny nie zostało dodanych lub ilu polom wskutek błędu konwersji typu została nadana wartość NULL.
Pozostałymi przyczynami błędów w trakcie dodawania rekordów mogą być np. naruszenia reguł poprawności pojawiające się wtedy kiedy dodajemy rekord nie posiadający wartości dla pola wymaganego lub też posiadający wartość niepoprawną dla danej kolumny (reguły poprawności definiujemy w projekcje tabeli). Z kolei błędy konwersji pojawiają się przy zamianie jednego typu danych na drugi. Mogą się również pojawić przy wprowadzaniu w pole daty / czasu wartości o niepoprawnym formacie.
5.2. Usuwanie rekordów przy pomocy instrukcji DELETE
Rekordy usuwamy przy pomocy instrukcji DELETE. Jej składnia została przedstawiona na Listing 9. Instrukcja ta kasuje tylko te rekordy z tabeli, które spełniają warunek podany wewnątrz klauzuli WHERE. Konstrukcja tych warunków mająca wybrać określone rekordy do usunięcia jest identyczna jak w instrukcji SELECT, gdzie służy ona do wybrania rekordów przeznaczonych do wyświetlenia.
Jeżeli pominiemy klauzulę WHERE usunięte zostaną wszystkie rekordy z danej tabeli. Zmian spowodowanych działaniem instrukcji DELETE nie można już cofnąć dlatego należy się nią posługiwać bardzo ostrożnie. W przeciwnym razie przez przypadek możemy w jednej chwili wykasować ważne dane, które mogły być gromadzone w naszej bazie całymi latami.
DELETE FROM tabela
[WHERE warunek1 [AND|OR warunek2 [AND|OR ...]]];
Listing 9: Składnia instrukcji DELETE
Przykład użycia instrukcji DELETE do usunięcia z tabeli Oferty_Wycofane rekordów dodanych uprzednio przy pomocy instrukcji INSERT w Listing 6 przedstawia Listing 10.
DELETE FROM Oferty_Wycofane
WHERE Powod = 'Wycofanie oferty 2004';
Tabela Oferty_Wycofane przed usunięciem rekordów
Tabela Oferty_Wycofane po usunięciu rekordów
Listing 10: Użycie instrukcji DELETE do usunięcia rekordów z tabeli Oferty_Wycofane
Po uruchomieniu dowolnej kwerendy zawierającej instrukcję DELETE MS Access pokaże komunikat wyświetlający ilość wierszy, które zostaną usunięte w wyniku działania kwerendy, prosząc jednocześnie o potwierdzenie wykonania operacji. Treść komunikatu w przypadku Listing 10 została pokazana na Rysunek 2
Rysunek 2: Komunikat MS Access po uruchomieniu kwerendy z instrukcją DELETE
Wewnątrz klauzuli WHERE instrukcji INSERT możliwe jest użycie podzapytania (podobnie jak wewnątrz klauzuli WHERE instrukcji SELECT). Listing 11 usuwa rekordy dodane do tabeli Sprzedawcy w Listing 7, stosując pod zapytanie
wybierające z tabeli Klienci sklejone razem nazwiska i imiona klientów. W ten sposób można określić, którzy ze sprzedawców „pochodzili” z tabeli Klienci (zakładając oczywiście, że wśród pozostałych (pierwotnych) sprzedawców nie było osób o takim samym imieniu i nazwisku jak któryś z klientów).
DELETE FROM Sprzedawcy
WHERE Nazwisko&Imie IN(SELECT Nazwisko&Imie FROM Klienci);
Tabela Sprzedawcy przed usunięciem rekordów
Tabela Sprzedawcy po usunięciu rekordów
Listing 11: Użycie instrukcji DELETE z podzapytaniem
5.2.1. Błędy zwracane przez instrukcję DELETE
Najczęstszym błędem pojawiającym się w trakcie pracy z instrukcją DELETE jest komunikat o niemożności usunięcia danego rekordu wskutek istnienia powiązanych z nim wpisów w innych tabelach. Z sytuacją taką mamy do czynienia gdy próbujemy usunąć rekord z tabeli połączonej relacją z inną tabelą, przy uaktywnionej opcji wymuszania więzów integralności dla tej relacji. Nie można wówczas usunąć rekordu z tabeli pierwszej, jeżeli w drugiej znajduje się rekord zawierający klucz obcy odwołujący się do usuwanego rekordu.
Rozważmy następujący przykład. Odnośniki do rekordów z tabeli Oferty znajdują się zarówno w tabeli Transakcje jaki i Oferty_Wycofane. Listing 12 próbuje usunąć trzy wpisy z tabeli Oferty. Jednakże jedynie jeden z nich (o ID
= 11, obrysowany linią przerywaną) nadaje się do usunięcia gdyż żaden inny rekord w bazie nie jest z nim powiązany. Pozostałe dwa rekordy nie mogą zostać usunięte, gdyż w tabeli Oferty_Wycofane znajdują się odwołujące się do nich rekordy.
DELETE FROM Oferty WHERE ID IN(9, 11, 21);
Tabela Oferty przed próbą usunięcia rekordu
Tabela Oferty_Wycofane przed próbą usunięcia rekordu z tabeli Oferty
Komunikat MS Access o niemożności usunięcia rekordu na skutek naruszenia klucza (więzów integralności)
Listing 12: Naruszenie więzów integralności przy próbie usunięcia rekordów z tabeli Oferty, do których odwołują się rekordy z tabeli Oferty_Wycofane
5.3. Modyfikowanie rekordów przy pomocy instrukcji UPDATE
Do zmian wartości wpisanych w poszczególnych polach istniejących już rekordów służy przedstawiona w Listing 13 instrukcja UPDATE. Podobnie jak instrukcje INSERT oraz DELETE może ona operować tylko na jednej tabeli na raz.
Na liście po klauzuli SET określa się kolumny oraz nowe wartości jakie mają zostać do nich przypisane. Użycie klauzuli WHERE jest opcjonalne i pozwala wybrać rekordy, których pola będą modyfikowane. W przypadku pominięcia tej klauzuli zmiany zostaną wykonane dla wszystkich wpisów z tabeli. Składnia warunków logicznych wybierających określone rekordy jest identyczna jak w przypadku instrukcji SELECT i DELETE. Dopuszcza ona także użycie podzapytań.
UPDATE tabela
SET kolumna1 = wartosc1 [, kolumna2 = wartosc2, ...]
[WHERE warunek1 [AND|OR warunek2 [AND|OR ...]]];
Listing 13: Składnia instrukcji UPDATE
Listing 14 przy pomocy instrukcji UPDATE uzupełnia puste pola Powod z tabeli Oferty_Wycofane, wpisując do nich wartość: ‘Nie podano’.
UPDATE Oferty_Wycofane SET Powod = 'Nie podano' WHERE Powod IS NULL;
Tabela Oferty_Wycofane przed modyfikacją rekordów
Tabela Oferty_Wycofane po modyfikacji rekordów
Listing 14: Użycie instrukcji UPDATE do modyfikacji rekordów tabeli Oferty_Wycofane
Podobnie jak było to w przypadku wywoływania instrukcji INSERT i DELETE w momencie uruchomienia kwerendy MS Access pokaże komunikat wyświetlający ilość wierszy, które zostaną objęte jej działaniem (czyli zostaną zaktualizowane), prosząc jednocześnie o potwierdzenie wykonania operacji. Treść komunikatu dla polecenia z Listing 14 przedstawia Rysunek 3.
Rysunek 3: Komunikat MS Access po uruchomieniu kwerendy z instrukcją UPDATE
Nieumiejętne posługiwanie się instrukcją UPDATE może mieć równie poważne konsekwencje jak przy złym użyciu instrukcji DELETE. Możemy bowiem omyłkowo nadać tę samą wartość wielu rekordom, co niekoniecznie musi być naszym zamiarem. Dlatego szczególną uwagę należy przywiązywać do konstrukcji warunku wewnątrz klauzuli WHERE, tak aby wybrać tylko te rekordy, które rzeczywiście chcemy zmodyfikować. W Listing 15 pominięto klauzulę WHERE, przez co zmiana nazwiska i imienia została wykonana dla każdego ze sprzedawców. W ten sposób utracona została duża porcja informacji, gdyż po wykonaniu operacji wszystkie imiona i nazwiska stały się takie same. Od tej pory nie odpowiadają już one prawdziwym danym dla konkretnego sprzedawcy.
UPDATE Sprzedawcy
SET Nazwisko = 'Nowak', Imie = 'Piotr';
Tabela Sprzedawcy przed modyfikacją rekordów
Tabela Sprzedawcy po modyfikacji rekordów
Listing 15: Utrata informacji o nazwiskach i imionach sprzedawców spowodowana niewłaściwym użyciem instrukcji UPDATE
5.3.1. Błędy zwracane przez instrukcję UPDATE
Błędy mogące się pojawić podczas wykonywania kwerend z instrukcją UPDATE są bardzo podobne do tych, które występowały w przypadku instrukcji INSERT (patrz podpunkt 5.1.3). Zatem mogą być to np. problemy związane z naruszeniem klucza lub reguł poprawności podczas zamiany istniejących w polach już danych na inne. Listing 16 próbuje ustawić pustą datę wycofania dla wybranego rekordu z tabeli Oferty_Wycofane. Jednakże ze względu, iż pole to jest polem wymaganym aparat bazy danych odmawia aktualizacji rekordu, wyświetlając odpowiedni komunikat o błędzie.
UPDATE Oferty_Wycofane SET Data_Wycofania = NULL WHERE ID_Oferty = 19;
Tabela Oferty_Wycofane przed próbą modyfikacji rekordu
Komunikat MS Access o niemożności zmodyfikowania rekordu na skutek naruszenia reguł poprawności
Listing 16: Naruszenie reguł poprawności przy próbie ustawienia pustej daty wycofania w tabeli Oferty_Wycofane
5.4. Tworzenie nowych obiektów przy pomocy instrukcji CREATE
Instrukcja CREATE służy do tworzenia nowych obiektów w bazie danych, a dla większości systemów bazodanowych nawet do tworzenia samej bazy. Jej składnia oraz możliwości mogą się nieco różnic w zależności od implementującego ją aparatu bazy danych. Dlatego też zostaną tutaj omówione tylko jej podstawowe cechy, a pełnej specyfikacji należy szukać w dokumentacji właściwego systemu bazodanowego. Podobna sytuacja ma miejsce w przypadku omawianych w podpunktach 5.5 i 5.6 instrukcji DROP i ALTER.
5.4.1. Instrukcja CREATE DATABASE
Nową bazę danych można założyć przy pomocy instrukcji CREATE DATABASE. Nie jest ona obsługiwana przez aparat bazy danych MS Access. W systemie MySQL przyjmuje ona postać widoczną w Listing 17. Opcjonalna klauzula IF NOT EXIST zapewnia, że w przypadku, gdy baza o podanej nazwie istnieje już w systemie to nie zostanie podjęta próba utworzenia jej po raz kolejny.
CREATE DATABASE [IF NOT EXIST] nazwa_bazy;
5.4.2. Instrukcja CREATE TABLE
Instrukcja CREATE TABLE dodaje nową tabelę do istniejącej bazy danych. Jej podstawowa składnia dla aparatu bazy MS Access została pokazana na Listing 18.
CREATE TABLE nazwa_tabeli
( kolumna1 typ_danych[(rozmiar)][NOT NULL][PRIMARY KEY]
[REFERENCES tabela(kolumna)]
[,kolumna2 typ_danych[(rozmiar)]...]
[,kolumna3 typ_danych[(rozmiar)]...]
...
[,kolumnaN typ_danych[(rozmiar)]...]
[,CONSTRAINT ...]
...
);
Listing 18: Składnia instrukcji CREATE TABLE
Budując kwerendę z wykorzystaniem instrukcji CREATE TABLE na początku podajemy nazwę tabeli, którą chcemy utworzyć, a następnie wewnątrz nawiasów okrągłych podajemy oddzielone od siebie przecinkami definicje poszczególnych kolumn. W skład każdej definicji kolumny wchodzi jej nazwa, a następnie typ danych z opcjonalnym rozmiarem dla danego pola. Typy danych dostępne w systemach MS Access i MySQL zostały zgromadzone w tabeli 1 (w rozdziale 1).
Po ustaleniu typu danych można określić zbiór opcji (właściwości) dla danej kolumny. Niestety w systemie MS Access z poziomu instrukcji CREATE TABLE nie jesteśmy w stanie ustawić wszystkich opcji dostępnych z graficznego edytora projektu tabeli. W większości pozostałych systemów bazodanowych (np.
MySQL) to nie graficzny edytor, a właśnie instrukcja CREATE TABLE jest podstawowym narzędziem do dodawania nowych tabel w bazie, a co za tym idzie oferuje ona znacznie szerszy zbiór dostępnych opcji. Jakkolwiek w MS Access wewnątrz instrukcji CREATE TABLE możemy określić, że dane pole nie może przyjmować wartości NULL (opcja NOT NULL), że jest kluczem podstawowym w danej tabeli (opcja PRIMARY KEY), lub też że jest elementem relacji jaką tworzy nowodefiniowana tabela z inną już istniejącą (opcja REFERENCES). Za pomocą opcji CONSTRAINT, umieszczanej na końcu, po zdefiniowaniu już wszystkich kolumn możemy na poziomie tabeli zdefiniować np.
wielokolumnowy klucz podstawowy lub obcy. W pozostałych systemach bazodanowych możliwe jest również określenie wartości domyślnej dla pól w danej kolumnie (opcja DEFAULT) lub też określenie reguł poprawności na poziomie pola lub nawet tabeli (opcja CHECK).
Listing 19 używa instrukcji CREATE TABLE do utworzenia nowej tabeli w komisie samochodowym, przechowującej informacje o reklamacjach zgłaszanych przez klientów, którzy zakupili auto. Tabela ta zawiera następujące pola: ID będące kluczem podstawowym w tabeli, jednoznacznie identyfikującym każdą z reklamacji, ID_Transakcji będące kluczem obcym wskazującym na transakcję, której dotyczy dana reklamacja, Data_Reklamacji, która przechowuje datę zgłoszenia problemu i Powod przechowujący krótki opis
reklamacji. Po wykonaniu kwerendy tabela pojawia się w zbiorze tabel należących bazy danych, a także w widoku relacji.
CREATE TABLE Reklamacje
(ID AUTOINCREMENT NOT NULL PRIMARY KEY,
ID_Transakcji INTEGER NOT NULL REFERENCES Transakcje(ID), Data_Reklamacji DATETIME NOT NULL,
Powod TEXT(255) );
Fragment widoku relacji po dodaniu tabeli Reklamacje
Listing 19: Użycie instrukcji CREATE TABLE do utworzenia nowej tabeli
5.4.3. Użycie instrukcji SELECT...INTO do tworzenia tabel z jednoczesnym ich wypełnianiem
W niektórych systemach bazodanowych dostępna jest bardzo pożyteczna instrukcja pozwalająca na zapamiętanie wyników zapytania bezpośrednio w nowo tworzonej tabeli. Instrukcja SELECT...INTO różni się od zwykłej instrukcji SELECT tylko tym, że po liście wyrażeń występuje dodatkowo klauzula INTO, po której podajemy nazwę dla nowotworzonej tabeli służącej przechowującej wyniki zapytania. Kolejność, typy i nazwy kolumn w utworzonej w ten sposób tabeli są zgodne z kolejnością, typami i nazwami wyrażeń na liście po instrukcji SELECT.
SELECT Nazwisko, Imie, Miejscowosc INTO Sprzedawcy_Kopia FROM Sprzedawcy;
Zawartość nowoutworzonej tabeli Sprzedawcy_Kopia
Listing 20: Użycie instrukcji SELECT...INTO do utworzenia nowej tabeli
5.4.4. Instrukcja CREATE INDEX
Instrukcja CREATE INDEX służy do zakładania indeksów na kolumnach w istniejących już tabelach. Jej składnię pokazuje Listing 21. Opcjonalna klauzula UNIQUE określa czy indeks ma być unikalny – czyli indeksować kolumnę (lub grupę kolumn), w której wartości się nie powtarzają. W przypadku kolumn o tekstowym typie danych, wchodzących w skład indeksu można określić maksymalną ilość indeksowanych znaków podając je jako opcjonalny rozmiar w nawiasach okrągłych za nazwą kolumny. Ma to szczególnie ważne znaczenie przy dużych długościach przechowywanego w kolumnie tekstu.
CREATE [UNIQUE] INDEX nazwa_indeksu ON tabela(kolumna1[(n)]
[,kolumna2[(n)],...]);
Listing 21: Składnia instrukcji CREATE INDEX
Listing 22 prezentuje użycie instrukcji CREATE INDEX do założenia nieunikalnego indeksu na kolumnie Kwota w tabeli Transakcje.
CREATE INDEX Transakcje_Kwota ON Transakcje(Kwota);
Listing 22: Użycie CREATE INDEX do utworzenia indeksu
5.5. Usuwanie obiektów przy pomocy instrukcji DROP
Instrukcja DROP służy do usuwania obiektów bazodanowych. Jej działanie jest silnie destrukcyjne, dlatego należy się nią posługiwać bardzo ostrożnie, szczególnie w przypadku usuwania pojedynczych tabel lub całej bazy danych.
5.5.1. Instrukcja DROP DATABASE
Przy pomocy instrukcji DROP DATABASE usuwamy istniejącą bazę danych wraz z całą jej zawartością. Instrukcja ta, podobnie jak omawiana w podpunkcie 5.4.1 instrukcja CREATE DATABASE nie jest wspierana przez MS Access. Jej składnię w MySQL przedstawia Listing 23. Opcjonalna klauzula IF EXIST zapobiega pojawieniu się komunikatu o błędzie w przypadku, gdy baza o podanej nazwie nie została znaleziona w systemie.
DROP DATABASE [IF EXIST] nazwa_bazy;
Listing 23: Składnia instrukcji DROP DATABASE
5.5.2. Instrukcja DROP TABLE
Instrukcja DROP TABLE usuwa utworzoną wcześniej tabelę wraz ze wszystkimi zgromadzonymi w niej danymi. Listing 24 prezentuje jej podstawową
składnię, a Listing 25 przykład użycia do usunięcia tabeli Reklamacje utworzonej w podpunkcie 5.4.2.
DROP TABLE nazwa_tabeli;
Listing 24: Składnia instrukcji DROP TABLE
DROP TABLE Reklamacje;
Listing 25: Użycie instrukcji DROP TABLE do usunięcia tabeli
5.5.3. Instrukcja DROP INDEX
Za pomocą instrukcji DROP INDEX możemy usunąć wybrany indeks. Składnia tej instrukcji została przedstawiona w Listing 26. Usunięcie indeksu założonego w podpunkcie 5.4.4 zostało wykonane w Listing 27.
DROP INDEX nazwa_indeksu ON tabela;
Listing 26: Składnia instrukcji DROP INDEX
DROP INDEX Transakcje_Kwota ON Transakcje;
Listing 27: Użycie instrukcji DROP INDEX do usunięcia indeksu
5.6. Modyfikowanie obiektów przy pomocy instrukcji ALTER
Instrukcja ALTER służy do dokonywania zmian w obiektach bazy danych. Jest ona potężnym narzędziem pozwalającym zmodyfikować prawie wszystko co było określone na etapie tworzenia danego obiektu. Podobnie jak w przypadku instrukcji CREATE omawianej w paragrafie 5.4 oferowane przez nią opcje silnie zależą od konkretnego aparatu bazy danych.
5.6.1. Instrukcja ALTER TABLE
Instrukcja ALTER TABLE używana jest do zmieniania struktury wybranej tabeli. Pozwala ona na dodawanie, usuwanie i modyfikację definicji poszczególnych kolumn, indeksów, a także kluczy podstawowych i obcych. W systemie MS Access ma ona jednak ograniczoną funkcjonalność (podobnie jak instrukcja CREATE TABLE). Jej podstawową składnię w tym środowisku, pozwalającą jedynie na dodawanie i usuwanie kolumn w tabeli przedstawia Listing 28. W przypadku użycia instrukcji ALTER TABLE w celu dodania nowej kolumny należy użyć opcji ADD COLUMN, a następnie podać nazwę i definicję kolumny wraz z określeniem przechowywanego w niej typu danych, a także właściwości. Definicja nowej kolumny jest identyczna w instrukcji CREATE
DROP COLUMN. Jedynym argumentem tej opcji jest nazwa kolumny, którą chcemy usunąć.
ALTER TABLE nazwa_tabeli
[ADD COLUMN nazwa_kolumny typ_danych[(rozmiar)]
[NOT NULL][PRIMARY KEY][REFERENCES tabela(kolumna)]]
[DROP COLUMN nazwa_kolumny];
Listing 28: Składnia instrukcji ALTER TABLE
Przykłady użycia instrukcji ALTER TABLE w celu dodania i usunięcia kolumny z tabeli Sprzedawcy zostały przedstawione odpowiednio w Listing 29 i Listing 30.
ALTER TABLE Sprzedawcy
ADD COLUMN Data_Zatrudnienia DATETIME;
Listing 29: Użycie instrukcji ALTER TABLE do dodania nowej kolumny
ALTER TABLE Sprzedawcy
DROP COLUMN Data_Zatrudnienia;
Listing 30: Użycie instrukcji ALTER TABLE do usunięcia kolumny
