POWTÓRZENIE

1. POWTÓRZENIE • definicja danych; • opcje definicji kolumn; • deklaracje dodatkowe do definicji tabeli; • modyfikacja struktury tabel; • usuwanie tabel; • wprowadzanie danych; • usuwanie wierszy; • modyfikowanie danych; • wprowadzanie poleceń z pliku; • wprowadzanie danych z pliku.

2. Kontrola integralności danych: Rozważmy teraz elementy standardu SQL, które zostały wprowadzone w celu kontroli integralności danych. Polega ona na przestrzeganiu więzów, które nakładamy na dane, aby uniknąć niespójnego stanu bazy danych. Mamy do dyspozycji pięć rodzajów więzów integralności: • dane wymagane; • więzy dziedzinowe; • integralność encji; • integralność referencyjna (związków); • więzy ogólne. Wszystkie one mogą być zdefiniowane w poleceniach CREATE i ALTER TABLE.

3. 1. Dane wymagane: W standardzie ISO (również w MySQL) możemy zdefiniować więzy danych wymaganych poprzez umieszczenie w poleceniach CREATE i ALTER TABLE przy kolumnie klauzuli NOT NULL. Klauzulą domyślną w standardzie ISO jest dopuszczanie wartości pustych.

4. 2. Więzy dziedzinowe: Z każdą kolumną związana jest dziedzina, czyli zbiór dopuszczalnych wartości. Na przykład, pleć pracownika może być zapisana jako 'M' lub 'K', czyli dziedziną kolumny płeć w tabeli jest łańcuch znaków długości jeden równy 'M' lub 'K'. Standard ISO opisuje dwa mechanizmy określania dziedziny w poleceniach CREATE i ALTER TABLE. Pierwszy z nich to klauzula CHECK, która pozwala ograniczyć dopuszczalne wartości dla konkretnej kolumny lub dla całej tabeli, zapisujemy ją następująco: CHECK (warunek_selekcji)

5. W więzach kolumnowych w klauzuli CHECK można odwoływać się jedynie do kolumny, dla której jest zdefiniowana klauzula. Tak więc, by zagwarantować, że w kolumnie płeć będzie mogła występować tylko wartość 'M' lub 'K', możemy zdefiniować tę kolumnę następująco: plec CHAR NOT NULL CHECK (plec IN ('M','K'))

6. Standard ISO zezwala również na niezależne od tabel definiowanie dziedzin za pomocą polecenia CREATE DOMAIN: CREATE DOMAIN NazwaDziedziny [AS] typ_danych [DEFAULT wartość__domyślna] [CHECK (warunek_selekcji)] W ten sposób dziedzinie przypisuje się nazwę NazwaDziedziny, typ danych i, opcjonalnie, wartość domyślną. Również opcjonalnie można zdefiniować regułę poprawności dla dziedziny za pomocą klauzuli CHECK.

7. Np. możemy zdefiniować dziedzinę dla atrybutu płeć następująco: CREATE DOMAIN RodzajPlci AS CHAR DEFAULT 'M' CHECK (VALUE IN ('M','K')); Polecenie to tworzy dziedzinę RodzajPlci, która składa się z pojedynczych znaków o wartości 'M' lub 'K'. Teraz, definiując kolumnę płeć, możemy zastosować nazwę dziedziny RodzajPłci zamiast typu danych CHAR: plec RodzajPlci NOT NULL

8. Parametr warunek_selekcjimoże wymagać odwołania się do innej tabeli. Na przykład, możemy stworzyć dziedzinę NumerBiura, by upewnić się, że wprowadzane wartości odpowiadają numerom biur występującym w tabeli Biuro: CREATE DOMAIN NumerBiura AS CHAR(4) CHECK (VALUE IN (SELECT biuroNr FROM Biuro)); Zazwyczaj lepiej jest definiować więzy dziedzinowe za pomocą polecenia CREATE DOMAIN niż tylko CHECK. Oba polecenia nie funkcjonują jednak w MySQL.

9. Dziedzinę można usunąć z bazy danych poleceniem DROP DOMAIN: DROP DOMAIN NazwaDziedziny [RESTRICTI CASCADE] Zasięg usunięcia, RESTRICT lub CASCADE, określa tryb postępowania w przypadku, gdy usuwamy używaną aktualnie dziedzinę. RESTRICT- i dziedzina jest wykorzystywana w istniejącej tabeli, perspektywie lub asercji, to usunięcie nie zostanie wykonane. CASCADE - to w każdej kolumnie tabeli, dla której jako typ danych określono usuwaną dziedzinę, typ ten zostanie zamieniony na typ, w oparciu, o który jest zdefiniowana usuwana dziedzina. Także wartość domyślna i więzy kolumny zostaną zastąpione wartością domyślną i więzami usuwanej dziedziny, o ile występowały w definicji dziedziny.

10. 3. Integralność encji: Klucz główny tabeli musi zawierać unikalną, niepustą wartość w każdym wierszu. Na przykład, każdy wiersz tabeli Nieruchomość ma unikalny numer nieruchomości nieruchomośćNr, który jednoznacznie określa nieruchomość zapisaną w tym wierszu. Standard ISO pozwala kontrolować integralność encji za pomocą klauzuli PRIMARY KEY w poleceniach CREATE i ALTER TABLE. Na przykład, aby zdefiniować klucz główny tabeli Nieruchomość dodajemy klauzule: PRIMARY KEY(nieruchomośćNr)

11. Aby zdefiniować złożony klucz główny, przy klauzuli PRIMARY KEY podajemy nazwy kilku kolumn oddzielone przecinkami. Na przykład, aby zdefiniować klucz główny tabeli Wizyta, składający się z kolumn klientNr i nieruchomośćNr, dodajemy klauzulę: PRIMARY KEY(klientNr, nieruchomośćNr) Klauzula PRIMARY KEY może wystąpić tylko raz w definicji tabeli.

12. Mimo to istnieje możliwość zapewnienia unikalności wartości kluczy alternatywnych. Służy do tego słowo kluczowe UNIQUE. Każda kolumna, która wystąpi w klauzuli UNIQUE, musi być także określona jako NOT NULL. W tabeli może wystąpić tyle klauzul UNIQUE, ile jest potrzebnych. SQL odrzuci każdą operację INSERT i UPDATE, która będzie powodowała utworzenie duplikatu wartości dowolnego klucza kandydującego (głównego lub alternatywnego). Na przykład, dla tabeli Wizyta moglibyśmy w definicji napisać: klientNr VARCHAR{5) NOT NULL, nieruchomośćNr VARCHAR(5) NOT NULL, UNIQUE (klientNr, nieruchomośćNr)

13. 4. Integralność referencyjna: Klucz obcy to kolumna lub zbiór kolumn, łączących każdy wiersz tabeli podrzędnej, w której występują, z wierszem tabeli nadrzędnej zawierającym wartość klucza kandydującego równą ich wartości. Integralność referencyjna oznacza, że jeśli w polach klucza obcego występują pewne wartości, to muszą odnosić się one do istniejącego, poprawnego wiersza w tabeli nadrzędnej. Standard ISO pozwala definiować klucze obce za pomocą klauzuli FOREIGN KEY w poleceniach CREATE i ALTER TABLE.

14. Na przykład, aby zdefiniować klucz obcy biuroNr w tabeli Nieruchomość, dodajemy klauzulę: FOREIGN KEY(biuroNr) REFERENCES Biuro W podklauzuli PREFERENCES podaje się nazwę tabeli nadrzędnej oraz listę kolumn klucza kandydującego (w przypadku braku listy przyjmuje się, że tym kluczem jest klucz główny tabeli nadrzędnej). SQL odrzuci każdą operacje INSERT lub UPDATE, jeżeli w jej następstwie powstałaby wartość klucza obcego, do której nie pasuje żadna wartość klucza kandydującego w tabeli nadrzędnej.

15. Przebieg operacji DELETE lub UPDATE, powodującej zmianę lub usunięcie takiej wartości klucza kandydującego, której odpowiadają pewne wiersze w tabeli podrzędnej, zależy od akcji referencyjnej zdefiniowanej za pomocą podklauzuli ON UPDATE lub ONDELETE w klauzuli FOREIGN KEY. W SQL są określone cztery tryby postępowania w przypadku, gdy użytkownik próbuje usunąć wiersz z tabeli nadrzędnej, a w tabeli podrzędnej istnieją związane z nim wiersze: • CASCADE – usunięcie wiersza z tabeli nadrzędnej automatycznie powoduje usunięcie związanych z nim wierszy z tabeli podrzędnej. • SET NULL – usunięcie wiersza z tabeli nadrzędnej i zastąpienie wartości klucza obcego w jego wierszach podrzędnych wartością pustą.

16. SET DEFAULT – usuniecie wiersza z tabeli nadrzędnej i zastąpienie wartości klucza obcego w jego wierszach podrzędnych wartością domyślną. • NO ACTION - zaniechanie operacji usunięcia z tabeli nadrzędnej. Jest to domyślny tryb postępowania, przyjmowany, gdy w definicji nie występuje reguła ON DELETE. SQL umożliwia takie same operacje w przypadku, gdy klucz kandydujący w tabeli nadrzędnej zostanie zmodyfikowany.

17. Powiązania kluczy obcych:

18. Próba wykonania polecenia z nieistniejącą wartością w tabeli nadrzędnej:

19. Aktualizacja obiektów w tabeli nadrzędnej:

21. Usuwanie obiektów z tabeli nadrzędnej:

22. Kolejność usuwania tabel powiązanych: DROP TABLE personel, biuro;

23. 5. Więzy ogólne: Możliwości zmian w tabelach mogą być ograniczone więzami ogólnymi (nazywanymi w SQL asercjami) rządzącymi przebiegiem rzeczywistych transakcji, z których takie zmiany wynikają. Standard ISO pozwala specyfikować więzy ogólne za pomocą klauzul CHECK i UNIQUE w poleceniach CREATE i ALTER TABLE oraz za pomocą polecenia CREATE ASSERTION:  CREATE ASSERTION nazwa_asercjiCHECK (warunek_selekcji)

24. Np. aby zdefiniować warunek zapobiegający jednoczesnemu nadzorowaniu przez pracownika więcej niż stu nieruchomości, możemy napisać: CREATE ASSERTION PracownikNadzorującyNieZaDużo CHECK (NO EXISTS (SELECT pracownikNr FROM Nieruchomość GROUP BY pracownikNr HAVING COUNT(*)>100));

25. Przykład: Utwórz tabelę Nieruchomość. CREATE DOMAIN NumeryWłaścieli AS VARCHAR(5) CHECK (VALUE IN (SELECT właścicielNr FROM WłaścicielPrywatny)); CREATE DOMAIN Numery Pracowników AS VARCHAR(5) CHECK (VALUE IN (SELECT pracownikNr FROM Personel)); CREATE DOMAIN NumeryBiur AS CHAR(4) CHECK (VALUE IN (SELECT biuroNr FROM Biuro)); CREATE DOMAIN NumeryNieruchomoŚci AS VARCHAR(5);

26. CREATE DOMAIN Ulice AS VARCHAR(25); CREATE DOMAIN Miasta AS VARCHAR(15): CREATE DOMAIN KodyPocztowe AS VARCHAR(8); CREATE DOMAIN TypyNieruchomości AS CHAR(l) CHECK(VALUE IN ('B','C','D','E','F','M','S')); CREATE DOMAIN PokojeWNieruchomoś AS SMALLINT CHECK(VALUE BETWEEN 1 AND 15); CREATE DOMAIN OpłatyZaWynajęcie AS DECIMAL(6,2) CHECK(VALUE BETWEEN 0 AND 9999.99);

27. CREATE TABLE Nieruchomość ( nieruchomośćNrNumeryNieruchomościNOT NULL, ulica Ulice NOT NULL, miasto Miasta NOT NULL, kod Pocztowy Kody Pocztowe, typ TypyNieruchomości NOT NULL DEFAULT'M', pokoje PokojeWNieruchomoś NOT NULL DEFAULT 4, czynsz OpłatyZaWynajęcie NOT NULL DEFAULT 600, właścicielNr NumeryWłaścicieli NOT NULL,

28. pracownikNr NumeryPracowników CONSTRAINTPracownikNadzorującyNieZaDużo CHECK (NOT EXISTS(SELECT pracownikNr FROM Nieruchomość GROUP BY pracownikNr HAYING COUNT{*) > 100)), BiuroNr NumeryBiur NOT NULL, PRIMARY KEY (nieruchomośćNr), FOREIGN KEY (pracownikNr) REFERENCES Personel ON DELETESET NULL ON UPDATE CASCADE, FOREIGN KEY (właścicielNr) REFERENCES Właściciel PrywatnyON DELETE NO ACTION ON UPDATE CASCADE, FOREIGN KEY (biuroNr) REFERENCES Biuro ON DELETE NOACTION ON UPDATE CASCADE);

29. Kolumnie typ, która wskazuje rodzaj nieruchomości przypisano wartość domyślną 'M' czyli mieszkanie. Dla kolumny zawierającej numer pracownika zdefiniowano klauzule CONSTRAINT gwarantującą, że dany pracownik nie nadzoruje zbyt wielu nieruchomości, czyli ich liczba nie przekracza 100.

30. Instrukcja SELECT: Instrukcja SELECT jest podstawową instrukcją języka SQL, służącą głównie (choć nie tylko) do pobierania danych z tabeli lub tabel na podstawie zadanych warunków. Wynikiem jej wywołania (o ile nie wystąpi błąd) jest zawsze pewna tabela. Składnia tej instrukcji jest dość złożona; należy przy tym pamiętać, że kolejność klauzul (tj. odpowiednich słów kluczowych) jest istotna. SELECT wyrażenie W tej postaci instrukcja SELECT zwróci po prostu wartość podanego wyrażenia (zbudowanego z wykorzystaniem stałych i funkcji). Dokładniej mówiąc, zwróci ona tabelę składającą się z jednego wiersza i jednej kolumny, zawierającą wartość tego wyrażenia.

31. SELECT wyrażenie1, wyrażenie2, ... Tym razem wynikiem będzie tabela o jednym wierszu i o kolumnach zawierających kolejno wartości podanych wyrażeń. SELECT wyrażenie1, wyrażenie2, ... FROM tabela W takiej postaci instrukcji SELECT sygnalizujemy, że dane chcemy pobierać z tabeli wymienionej po słowie kluczowym FROM. Możemy teraz, budując wyrażenia, używać nazw kolumn z tej tabeli. Kolejność kolumn w tabeli wynikowej będzie taka, jak kolejność podanych wyrażeń. W szczególności, wyrażenia mogą być po prostu nazwami interesujących nas kolumn.

32. Szczególny przypadek to SELECT * FROM tabela: co spowoduje wypisanie całej tabeli. Inna postać z warunkiem: SELECT wyrażenie1, wyrażenie2, ... FROM tabela WHERE warunek Warunek podany po słowie kluczowym WHERE ogranicza działanie instrukcji SELECT do wierszy spełniających ten warunek. Powinien on być wyrażeniem logicznym, zbudowanym z wykorzystaniem nazw kolumn tabeli.

33. Proste przykłady:

34. Komentarze w liniach komend:

35. Składnia: jest najczęściej używaną instrukcją SQL i ma następującą postać ogólną: SELECT[ALLDISTINCT] {*[wyrażenie_kolumnowe [AS nowa_nazwa]],[...]} FROM NazwaTabeli [alias],[...] [WHERE warunek_selekcji_wierszy] [GROUP BYlista_kolumn][HAVINGwarunek_selekcji_grup] [ORDER BY lista_kolumn];

36. SELECT – wskazuje, które kolumny powinny pojawić się w wyniku; DISTINCT – eliminuje powtórzenia po wykonaniu rzutowania na wybrane kolumny; FROM – określa tabelę (lub tabele), z których będziemy korzystać; WHERE – pozwala wybrać wiersze spełniające zadany warunek selekcji wierszy; GROUP BY – tworzy grupy wierszy o tej samej wartości wskazanej kolumny; HAVING – pozwala wybrać grupy ze względu na podany warunek selekcji grup; ORDER BY – określa uporządkowanie wyniku.

37. Wyszukiwanie wszystkich wierszy: 1.Wszystkie kolumny i wszystkie wiersze: SELECT * FROMpersonel;

38. 2.Wybrane kolumny i wszystkie wiersze: SELECTpersonelNr, imie, nazwisko, pensja FROMpersonel;

39. 3.Sortowanie wyników - wybrane kolumny i wszystkie wiersze uporządkowane malejąco: SELECTpersonelNr,imie,nazwisko,pensja FROMpersonel ORDER BYpensja DESC;

40. 4. Porządkowanie według wielu kolumn: SELECTnieruchomoscNr,typ, pokoje,czynsz FROMnieruchomosc ORDER BYtyp,czynsz DESC;

41. 5.Eliminacja powtórzeń DISTINCT– podaj numery nieruchomości odwiedzone przez klientów: SELECTDISTINCTnieruchomoscNr FROMwizyta;

42. 5.z powtórzeniami: SELECT nieruchomoscNr FROMwizyta;

43. SELECTpersonelNr, imie, nazwisko, pensja*12 ASpensja_Roczna FROMpersonel; 6a.Wyliczanie pensji rocznej:

44. Aliasy nazw kolumn i wyrażeń: Jako argumenty klauzul ORDER BY i GROUP BY nie mogą być użyte wyrażenia złożone. Można tu użyć jedynie nazw kolumn, lub - jeżeli chcemy grupować lub sortować według wartości wyrażeń złożonych - aliasy tych wyrażeń. Do stworzenia aliasu dla wyrażenia podanego w instrukcji SELECT stosuje się słowo kluczowe AS, w sposób następujący: SELECT wyrażenie1, wyrażenie2 AS aliasFROM złączenie ORDER BY alias Alias nadany wyrażeniu staje się nazwą odpowiedniej kolumny tabeli wynikowej (zamiast dosłownej postaci tego wyrażenia).

45. SELECTpersonelNr, imie, nazwisko, pensja*12FROMpersonel; 6a. wyliczanie pensji rocznej (bez alias’u):

46. 6b. wyliczanie wieku i jego ograniczenie: SELECTpersonelNr,imie, nazwisko, stanowisko,dataUr, Year(Now())-Year(dataUr) AS wiek FROMpersonel WHERE (Year(Now())-Year(dataUr))>40;

47. 6b. wyliczanie wieku i jego ograniczenie (bez alias’u): SELECTpersonelNr,imie, nazwisko, stanowisko,dataUr, Year(Now())-Year(dataUr) FROMpersonel WHERE (Year(Now())-Year(dataUr))>40;

48. 7. warunek selekcji: porównanie pensja do stałej SELECTpersonelNr,imie,nazwisko, stanowisko,pensja FROMpersonel WHERE pensja>1000; Wybieranie wierszy:

49. 8. złożony warunek selekcji: porównanie – podaj adresy biur w Łomży lub w Białymstoku SELECTbiuroNr,ulica,miasto,kod FROM Biuro WHERE miasto='Łomża'ORmiasto="Białystok";

50. 9. warunek selekcji przynależność do zbioru: SELECT biuroNr, ulica, miasto, kod FROMbiuro WHERE miasto IN ('Łomża','Białystok');