1 Podstawy SQL dla FirebirdSQLTą prezentacją chciałbym was wprowadzić w tematykę baz danych. Jeśli do tej pory baza to był po prostu magazyn na dane, do którego coś zapisywaliście lub zczytywaliście, to teraz chciałbym wam pokazać jak to wygląda od kuchni. Marek Dziedziczak, DB, DB1 Ericpol Telecom Sp. z o.o. Tel.:

2 Co to jest Relacyjna Baza Danych Projektowanie relacyjnych baz danych Agenda Co to jest Relacyjna Baza Danych Projektowanie relacyjnych baz danych Składniki relacyjnych baz danych Podstawy SQLa RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

3 Rozdział I: Co to jest Relacyjna Baza DanychRRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

4 Baza danych Zbiór danych zapisanych w ściśle określony sposób w strukturach odpowiadających założonemu modelowi danych. (pl.wikipedia.org) Książka telefoniczna Kod kreskowy Metka DNS Karta SIM System plików RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

5 System zarządzania bazą danychSystem zarządzania bazą danych, SZBD (ang. Database Management System, DBMS) nazywany też serwerem baz danych lub systemem baz danych. To oprogramowanie bądź system informatyczny służący do zarządzania komputerowymi bazami danych. (pl.wikipedia.org) Możliwość zapisywania, odczytywania i aktualizowania danych Sprawny dostęp do danych Zapewnienie spójności danych Możliwość dostępu wielu użytkownikom na raz Bezpieczeństwo danych Zarządzanie prawami dostępu do danych Spośród wszystkich cech bazy danych tak naprawdę najistoniejsza jest wydajność. To tylko dla tego firmy są skłonne płacić ogromne sumy na zakup i utrzymanie baz danych. RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

6 Relacyjna baza danych W bazach relacyjnych wiele tablic danych może współpracować ze sobą (są między sobą powiązane). (pl.wikipedia.org) Informacje dzielone są na powiązane ze sobą dane umieszczane w różnych tabelach i kolumnach. Wszystkie wartości danych oparte są na prostych typach danych. Wszystkie dane w bazie relacyjnej przedstawiane są w formie dwuwymiarowych tabel. Każda tabela zawiera zero lub więcej wierszy i jedną lub więcej kolumn. Na każdy wiersz składają się jednakowo ułożone kolumny wypełnione wartościami, które z kolei w każdym wierszu mogą być inne. Wszystkie operacje wykonywane są w oparciu o algebrę relacji. Wynikiem zapytań zawsze jest zbiór danych. Najlepiej podział informacji na powiązane dane pokazać na przykładzie. RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

7 Informacje o pracowniku: Imię, Nazwisko, PESEL, kontak e- mail Relacyjna baza danych Informacje o pracowniku: Imię, Nazwisko, PESEL, kontak e- mail Pracownik może mieć adres ale nie musi Każdy pracownik może mieć kilka adresów z różnymi atrybutami (służbowy, prywatny, uczelniany) Weźmy przykładową informację o pracowniku. RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

8 Relacyjna baza danych Imię Nazwisko PESEL Jan Smela 123456 Anna 234567Paweł Szczerkowski 345678 Wojciech Malkiewicz 456789 Dubiel 567890 Tak podzielone informacje spełniają założenia: każdy pracownik może mieć kilka adresów , tylko jeden lub nie mieć żadnego. PESEL adres kategoria 123456 służbowy 234567 456789 567890 prywatny RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

9 Relacyjna baza danych ∞ Imię Nazwisko PESEL Jan Smela 123456 Anna234567 Paweł Szczerkowski 345678 Wojciech Malkiewicz 456789 Dubiel 567890 Taką zależność między tabelami zapisujemy jako relację. Relacja przedstawia które dane zależą od których. Relacje przeważnie są typu 1 do wielu, czyli dla jednego rekordu z jednej tabeli odpowiada kilka rekordów z innej tabeli. Jeśli dopuszczamy możliwość nieprzypisania żadnego rekordu z tabeli podległej to mówimy, że relacja jest nieobowiązkowa – praktycznie wszystkie takie są Jeśli dopuszczamy możliwość niepowiązania do rodzica mamy wtedy relację 0 do wielu – bardzo rzadko spotykany przypadek Czasami można zdefiniować relację 1-1, rzadko kiedy ma ona sens – w takich sytuacjach należy rozważyć połączenie tych tabel w jedną. PESEL adres kategoria 123456 służbowy 234567 456789 567890 prywatny 1 ∞ RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

10 Relacyjna baza danych EMAILE PRACOWNICY Imię PESEL adres NazwiskoZwyczajowo też na schemacie bazy danych tabele przedstawia się w ten sposób. PRACOWNICY Imię Nazwisko PESEL E PESEL adres kategoria 1 ∞ RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

11 Schemat bazy danych To jest fragment struktury bazy danych. RRRR-MM-DDCOMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

12 Rozdział II: Projektowanie relacyjnych baz danychPewnie nie będziecie nigdy projektować żadnego rozbudowanego systemu bazodanowego, ale warto znać pewne pojęcia z tym związane. Aby dodać choćby nową kolumną należy kierować się pewnymi wytycznymi. RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

13 Projektowanie baz danych - normalizacjaNormalizacja bazy danych jest to proces mający na celu eliminację powtarzających się danych w relacyjnej bazie danych. Główna idea polega na trzymaniu danych w jednym miejscu, a w razie potrzeby linkowania do nich. (pl.wikipedia.org) Nadmiarowość – redundancja - doprowadza do błędów lub wielu niepotrzebnych problemów z utrzymaniem bazy danych Brytyjski informatyk Edgar Frank Codd w 1970 roku wydał pracę w której porządkował zagadnienia bazodanowe definiując najważniejsze pojęcia z tej dziedziny. Już 40 lat temu zauważono pewne problemy – anomalie - związane z przechowywaniem danych i zaproponowano sposoby ich unikania. Bezpieczeństwo danych Unikanie niespójności (problemy anomalii): nadmiarowość, anomalia modyfikacji i usunięć RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

14 Projektowanie baz danych - 1NF1NF -Atomowość Jak przygotować strukturę do przechowywania danych tele-adresowych? ul.Targowa 9A Łódź tel: fax: RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

15 Projektowanie baz danych - 1NFdane ul.Targowa 9A; Łódź; ; ; Wszystkie dane w jednym stringu porozdzielane znakiem „;” Adres; Telefon; Fax; Łatwe dopisywanie zwłaszcza w przypadku danych pochodzących z różnych źródeł, w których posiadały różne formy Oszczędność miejsca na dysku Trudne w wyszukiwaniu Format zapisu zależny wyłącznie od programu/osoby wpisującej – nie gwarantuje jednolitej formy – problem w wyszukiwaniu i wyświetlaniu Problem z formatowaniem przy wyświetlaniu – ciężko połamać linie wg określonych zasad RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

16 Projektowanie baz danych - 1NFAdres Telefon Fax Łódź,ul.Targowa 9A Wszystkie dane podzielone wg kategorii Adres - osobny nieokreślony string Telefon i fax – osobne nieokreślone stringi – osobny nieokreślony string Dość łatwe dopisywanie zwłaszcza w przypadku danych pochodzących z różnych źródeł, w których posiadały różne formy Możliwość wyszukiwania po , telefon i fax Trudności w wyszukiwaniu – po mieście i ulicy Forma zapisu w dużym stopniu zależna od programu/osoby wpisującej – nie gwarantuje jednolitej formy (kolejność zapisu adresu, nr tel z/bez prefiksów) – problem przy wyszukiwaniu Problem z formatowaniem przy wyświetlaniu – ciężko połamać linie wg określonych zasad w przypadku adresu RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

17 Projektowanie baz danych - 1NFKodPoczt Miasto Ulica Telefon Fax 90-042 Łódź ul.Targowa 9A Wszystkie dane podzielone wg kategorii Adres – podzielone na kolumny dla kodu pocztowego, miasta i ulicy Telefon i fax – osobne nieokreślone stringi – osobny nieokreślony string Duże możliwości wyszukiwania - po , telefon i fax, kod pocztowy, miasto Duże możliwości formatowania przy wyświetlaniu – kolejność i podział na linie wg dowolnych zasad Trudności w wyszukiwaniu – po ulicy Utrudniony zapis – należy pilnować co gdzie jest wpisywane Forma zapisu w pewnym stopniu zależna od programu/osoby wpisującej – nie gwarantuje jednolitej formy (nr tel z/bez prefiksów) – problem przy wyszukiwaniu RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

18 Projektowanie baz danych - 1NFKodPoczt Miasto Pref Ulica Nr Kier Telefon Fax 90-042 Łódź ul Targowa 9A +48 Wszystkie dane podzielone wg kategorii Adres – podzielone na kolumny dla kodu pocztowego, miasta i ulicy Ulica – podzielona na kolumny dla prefix (ul., al.), nazwa ulicy i numery Telefon i fax – osobne nieokreślone stringi i wydzielony nr kierunkowy – osobny nieokreślony string Duże możliwości wyszukiwania - po , telefon i fax, kod pocztowy, miasto, ulica Duże możliwości formatowania przy wyświetlaniu – kolejność i podział na linie wg dowolnych zasad Bardzo utrudniony zapis – należy pilnować co gdzie jest wpisywane Forma zapisu w pewnym stopniu zależna od programu/osoby wpisującej – nie gwarantuje jednolitej formy (nr tel z/bez prefiksów) – problem przy wyszukiwaniu RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

19 Projektowanie baz danych – 2NF i 3NF2NF - Dla zdefiniowanego klucza nie może istnieć podzbiór atrybutów podstawowych, który identyfikuje atrybuty wtórne. 3NF - Każdy atrybut wtórny jest tylko bezpośrednio zależny od klucza głównego. RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

20 Projektowanie baz danych – 2NF i 3NFnr data .. nabywca adres tow1 jm1 tow2 jm2 456/123 Ericpol Targowa 9A Karczek .. kg Kiełbasa .. RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

21 Projektowanie baz danych – 2NF i 3NFnr data .. nabywca adres tow1 jm1 tow2 jm2 456/123 Ericpol Targowa 9A Karczek .. kg Kiełbasa .. RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

22 Projektowanie baz danych – 2NF i 3NFnr data .. nabywca adres tow1 jm1 tow2 jm2 456/123 Ericpol Targowa 9A Karczek .. kg Kiełbasa .. RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

23 Projektowanie baz danych – 2NF i 3NFnr data .. nabywca adres tow1 jm1 tow2 jm2 456/123 Ericpol Targowa 9A Karczek .. kg Kiełbasa .. RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

24 Projektowanie baz danych – 2NF i 3NFnr data .. nabywca adres tow1 jm1 tow2 jm2 456/123 Ericpol Targowa 9A Karczek .. kg Kiełbasa .. RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

25 Projektowanie baz danych – 2NF i 3NFBaza danych . Tak zdefiniowana baza będzie składała się z jednej tabeli zawierającej mnóstwo kolumn. W tej chwili nawet trudno jest mi powiedzieć ile dokładnie tych kolumn jest. Przyjrzyjmy się więc dokładniej tej tabeli. Na początek popatrzmy na część przeznaczoną dla pozycji faktury. RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

26 Projektowanie baz danych – 2NF i 3NFnr data .. nabywca adres tow1 jm1 tow2 jm2 456/123 Ericpol Targowa 9A Karczek .. kg Kiełbasa .. Towar 1 Towar 2 Towar 3 Towar 4 Towar 5 Towar 6 Towar 7 Towar 8 Karczek../kg/15/.. Kiełbasa../kg/20/.. Musztarda/szt/3/.. Ketchup/szt/3/5/.. Kaczka/kg/2/23/.. Ogórek/kg/2/10/.. Indyk/kg/12/21/.. Myśliwska/kg/5/.. Kaszanka/kg/5/.. Towar 1 Towar 2 Towar 3 Towar 4 Towar 5 Towar 6 Towar 7 Towar 8 Karczek../kg/15/ Kiełbasa../kg/20/ Musztarda/szt/3/ Ketchup/szt/3/5/ tow1 jm1 .. tow2 jm2 tow3 jm3 tow4 jm4 Karczek .. kg Kiełbasa .. Musztarda .. szt Katchup .. Każda pozycja to kilka kolumn. Tych kolumn musi być przynajmniej tyle ile jest pozycji na fakturze. A co jeśli na fakturze będzie więcej pozycji? Musimy utworzyć jeszcze więcej kolumn, żeby się zmieściły wszelkie możliwe pozycje – kolumn musi być tyle ile maksymalnie wejdzie pozycji na fakturę (np. x8). Jeśli więc wystawimy kilka faktur to ten fragment tabeli będzie wyglądał mniej więcej tak: Zwróćcie uwagę, że większość faktur nie wykorzysta tych dodatkowych kolumn – będą one niepotrzebnie zajmować miejsce w bazie. Dużo poważniejszym problemem jest wyszukiwanie – żeby odnaleźć wszystkie faktury z określonym towary, należy przeszukać wszystkie zdefiniowane kolumny towarowe. Najlepszym rozwiązaniem tej sytuacji jest: RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

27 Projektowanie baz danych – 2NF i 3NFnr data .. nabywca adres tow1 jm1 tow2 jm2 456/123 Ericpol Targowa 9A Karczek .. kg Kiełbasa .. nr towar jm ilosc cena vat wartosc 456/123 Karczek.. kg 15 25 23 461,25 Kiełbasa.. 20 369,00 Musztarda.. szt 5 18,45 Ketchup.. nr data .. nabywca adres 456/123 Ericpol Targowa 9A Podział na dwie tabele: 1 – informacje o fakturze (nagłówkowe) 2 – informacje o pozycjach faktury Żeby dane trzymały się kupy do tabeli z pozycjami dodajemy kolumnę „nr faktury” dzięki czemu wiadomo będzie która pozycja należy do której faktury. Taką kolumnę nazywamy kluczem zewnętrznym, a całe rozwiązanie to relacja między tabelami. Teraz bardzo łatwo znaleźć wszystkie faktury zawierające określony towar – wystarczy przeszukać jedną kolumnę w jednej tabeli. RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

28 Projektowanie baz danych – 2NF i 3NF3NF - Każdy atrybut wtórny jest tylko bezpośrednio zależny od klucza głównego. nr towar jm ilosc cena WartoscNETTO vat kwotaVAT wartosc 456/123 Karczek Smażony kg 15 25 375,00 23 86,25 461,25 Kiełbasa Zwyczajna 20 300,00 69,00 369,00 Musztarda Stołowa szt 5 15,00 3,45 18,45 Ketchup Pudliszki Znowu przyjrzyjmy się naszym pozycją. Zmieścił mi się teraz już kompletny opis każdej pozycji – przepisałem po prostu wszystkie kolumny z faktury. Cały ten zestaw musiałby się powtarzać w poprzednim kroku. Zastanówmy się teraz czy taka struktura jest optymalna KLUCZ GŁÓWNY – są to kolumny unikalne, które jednoznacznie wskazują na każdy rekord. Tak jak adres pokazują konkretny rekord. W naszym przypadku takim kluczem będzie NR FAKTURY i TOWAR, bo na każdej fakturze każdy towar pojawia się tylko raz. Chociaż każdy towar może pojawić się na dowolnej liczbie faktur, oraz każda faktura może zawierać wiele towarów (pozycji). Wg tej reguły przyjrzyjmy się kolejno naszym kolumną: 1. Od czego zależy WARTOŚĆ? - od WARTOSCNETTO i KWOTAVAT. Wg tej reguły ta informacja jest zbędna. Zwłaszcza, że każdy komputer znając wartości WARTOSCNETTO i KWOTAVAT jest w stanie wyliczyć WARTOSC „w locie” w czasie wyświetlania wyników. Nie ma więc potrzeby trzymania tej danej w bazie. Ma to jeszcze taką zaletę, że z czasem mógłby się pojawić błąd – na skutek nieprawidłowej modyfikacji (np. ktoś zmodyfikuje WARTOSCVAT ale zapomni zmodyfikować WARTOSC) dane przestaną być prawdziwe – WARTOSCNETTO + KWOTAVAT <> WARTOSC. Taka sytuacja jest niedopuszczalna i nazywa się to anomalią modyfikacji. Tak więc kolumna WARTOSC jest zbędna. 2. A od czego zależy KWOTAVAT? 3. A WARTOŚĆNETTO? RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

29 Projektowanie baz danych – 2NF i 3NF2NF - Dla zdefiniowanego klucza nie może istnieć podzbiór atrybutów podstawowych, który identyfikuje atrybuty wtórne. nr towar jm ilosc cena vat 456/123 Karczek Smażony kg 15 25 23 Kiełbasa Zwyczajna 20 Musztarda Stołowa szt 5 Ketchup Pudliszki Patrzmy dalej: Jak już wspomniałem nasz klucz główny składa się z dwóch kolumn: Zobaczmy teraz od czego zależy JEDNOSTA MIARY: Czy zależy od numeru faktury? Czy kiedykolwiek Karczek Smażony zostanie sprzedany na sztuki lub na litry? To jest właściwość towaru – karczek zawsze będzie sprzedawany na kilogramy. Spójrzmy jeszcze na STAWKĘ VAT: Czy stawka ta zależy od faktury (pomijam sytuację zmiany stawki VAT, która jest bardzo rzadka i odbywa się tylko na początku roku)? Każdy towar będzie zawsze sprzedawany wg określonej stawki vat bo jest on przypisana do określonych towarów. Co w takim razie należy zrobić, żeby baza spełniła regułę 2 postaci formalne? Usunąć kolumn nie można, bo gdzieś trzeba zapisać te informacje. Ale można: RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

30 Projektowanie baz danych – 2NF i 3NF2NF - Dla zdefiniowanego klucza nie może istnieć podzbiór atrybutów podstawowych, który identyfikuje atrybuty wtórne. nr towar ilosc cena 456/123 Karczek Smażony 15 25 Kiełbasa Zwyczajna 20 Musztarda Stołowa 5 Ketchup Pudliszki towar jm vat Karczek Smażony kg 23 Kiełbasa Zwyczajna Musztarda Stołowa szt Ketchup Pudliszki Podzielić tabelę na dwie: 1 – informacje o pozycjach faktury 2 – suche fakty o towarze Dodatkowo, żeby baza działała szybciej, zajmowała mniej miejsca i przyjemniej się z niej korzystało stosuje się taki trick: RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

31 Projektowanie baz danych – 2NF i 3NFnr towar ilosc cena 456/123 1 15 25 2 20 3 5 4 id towar jm vat 1 Karczek Smażony kg 23 2 Kiełbasa Zwyczajna 3 Musztarda Stołowa szt 4 Ketchup Pudliszki Zamiast trzymać długi string w (nazwa towaru) w dwóch (a często i więcej) tabelach zastępuje się go liczbą. Po stronie tabeli z towarami dodajemy kolumnę liczbową, która wypełniana jest kolejnymi wartościami (ważne żeby się nie powtarzały) zaś po stronie pozycji wpisujemy już tylko odpowiedni numerek. Taką dodatkową kolumnę nazywa się przeważnie ID. Liczba zawsze zajmuje tyle samo, zwykle jest to mniej niż string – oszczędza się miejsce. Praca na liczbach (o stałej długości) jest łatwiejsza – przyśpiesza działanie bazy. Konsekwentne stosowanie kolumn ID we wszystkich tabelach upraszcza zapytania. RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

32 Projektowanie baz danych – 2NF i 3NFnr towar ilosc cena 456/123 Karczek Smażony 15 25 Kiełbasa Zwyczajna 20 Musztarda Stołowa 5 Ketchup Pudliszki 568/123 10 30 611/124 2 35 towar jm vat Karczek Smażony kg 23 Kiełbasa Zwyczajna Musztarda Stołowa szt Ketchup Pudliszki Zwróćcie jeszcze uwagę jak będzie się zachowywała tak zaprojektowana baza: po wpisaniu kilku faktur okaże się, że tabela z towarem prawie się nie zmienia - jedynie na początku a później już tylko z rzadka dochodzą nowe rekordy; z druga tabela (pozycje) będzie tą najbardziej obciążoną, która będzie się najszybciej rozrastać. RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

33 Projektowanie baz danych – 2NF i 3NFnr towar ilosc cena 456/123 1 15 25 2 20 3 5 4 568/123 10 30 611/124 35 id towar jm vat 1 Karczek Smażony kg 23 2 Kiełbasa Zwyczajna 3 Musztarda Stołowa szt 4 Ketchup Pudliszki Zwróćcie jeszcze uwagę jak będzie się zachowywała tak zaprojektowana baza: po wpisaniu kilku faktur okaże się, że tabela z towarem prawie się nie zmienia - jedynie na początku a później już tylko z rzadka dochodzą nowe rekordy; z druga tabela (pozycje) będzie tą najbardziej obciążoną, która będzie się najszybciej rozrastać. Dzięki zastosowaniu naszych modyfikacji jest ona mocno odchudzona – 4 kolumny w tym 3 liczbowe – co powoduje, że cała baza nie będzie się zbytnio rozrastać, a co za tym idzie będzie szybka (szybciej działa mała baza od dużej). RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

34 Projektowanie baz danych – 2NF i 3NFBaza danych 1 Zakład Mięsny „Rarytas” Łódź, Piotrkowska 10 2 Ericpol SP z o.o. Łódź, Targowa 9a 1 123456/123 Łódź gotówka 2 1 15 25 2 20 3 5 4 1 Karczek Smażony kg 23 2 Kiełbasa Zwyczajna 3 Musztarda Stołowa szt 4 Kechup Pudliszki Podobne tricki zastosowałem na reszcie kolumn naszej tabeli początkowej – wydzieliłem jeszcze firmy (klientów) do oddzielnej tabeli, oraz zredukowałem kolumny do niezbędnych ale za to powstawiałem kolumny ID – i wyszedł z tego taki schemat: RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

35 Projektowanie baz danych – 2NF i 3NFBaza danych 1 Zakład Mięsny „Rarytas” Łódź, Piotrkowska 10 2 Ericpol SP z o.o. Łódź, Targowa 9a 1 123456/123 Łódź gotówka 2 1 15 25 2 20 3 5 4 1 Karczek Smażony kg 23 2 Kiełbasa Zwyczajna 3 Musztarda Stołowa szt 4 Kechup Pudliszki Są tu 4 tabele: 1 – dane o klientach 2 – pozostałe dane nagłówkowe 3 – dane o towarach 4 – pozostałe dane o pozycjach faktur RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

36 Projektowanie baz danych – 2NF i 3NFBaza danych 1 Zakład Mięsny „Rarytas” Łódź, Piotrkowska 10 2 Ericpol SP z o.o. Łódź, Targowa 9a 1 123456/123 Łódź gotówka 2 1 15 25 2 20 3 5 4 1 Karczek Smażony kg 23 2 Kiełbasa Zwyczajna 3 Musztarda Stołowa szt 4 Kechup Pudliszki Spójrzmy na to jeszcze raz. Dla uproszczenia narysuję relacje: RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

37 Projektowanie baz danych – 2NF i 3NFBaza danych 1 Zakład Mięsny „Rarytas” Łódź, Piotrkowska 10 2 Ericpol SP z o.o. Łódź, Targowa 9a 1 123456/123 Łódź gotówka 2 1 15 25 2 20 3 5 4 1 Karczek Smażony kg 23 2 Kiełbasa Zwyczajna 3 Musztarda Stołowa szt 4 Kechup Pudliszki RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

38 Projektowanie baz danych – 2NF i 3NFBaza danych 1 Zakład Mięsny „Rarytas” Łódź, Piotrkowska 10 2 Ericpol SP z o.o. Łódź, Targowa 9a 1 123456/123 Łódź gotówka 2 Słowniki 1 Karczek Smażony kg 23 2 Kiełbasa Zwyczajna 3 Musztarda Stołowa szt 4 Kechup Pudliszki 1 15 25 2 20 3 5 4 Dane transakcyjne 1. Klienci są w opcjonalnej relacji 1-wielu z nagłówkami faktur gdyż każda faktura musi mieć klienta. Klient może (nie musi) mieć wystawioną fakturę. Każda faktura ma tylko 1 klienta. Każdy klient może mieć kilka faktur. 2. Nagłówki faktur są w obowiązkowej relacji 1-wielu z pozycjami gdyż każda pozycja musi być przypisana do jakiejś faktury. Faktura musi mieć przynajmniej jedną pozycję. Każda faktura może mieć kilka pozycji. Każda pozycja może być tylko na jednej fakturze. 3. Towary są w opcjonalnej relacji 1-wiele z pozycjami gdyż każda pozycja musi zawierać towar. Towar może (nie musi) znajdować się na fakturze. Każda pozycja ma tylko 1 towar. Każdy towar może być na kilku pozycjach (w kilku fakturach). Zwróćcie teraz uwagę na KLIENCI i TOWARY. Co one maję ze sobą wspólnego? - są dość duże (zawierają stringi) - rosną dość wolno (poza początkiem) Takie tabele (wolnorosnące) nazywamy tabelami słownikowymi: Tabela NAGŁÓWKOWA i POZYCJE będą z czasem największe – to do nich będzie dopisywane najwięcej danych. To są tabele transakcyjne. Najważniejsza – największy przyrost, najważniejsze informacje, prawdopodobnie najczęściej odpytywana – tabela to POZYCJE. Zwróćcie uwagę, że dzięki naszym zabiegom jest ona maksymalnie odchudzona – 5 kolumn liczbowych. Nie ma tabeli bardziej zoptymalizowanej niż tabela liczbowa bez NULLi. RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

39 Projektowanie baz danych - notacjeDobrze jest przyjąć jakąś konwencję w nazywaniu obiektów bazy danych i konsekwentnie ją stosować, dzięki temu unika się pomyłek i upraszcza pracę z bazą. Nazewnictwo powinno spełniać podstawowe warunki: Jak najkrótsze Opisywać istotę obiektu W przypadku dużych schematów warto dzielić je na mniejsze grupy wyróżniając w nazwie przedrostkiem lub końcówką Wybierając notację należy mieć na uwadze ograniczenia konkretnego systemu bazodanowego (ograniczenia w długości nazw, ograniczenia użycia znaków specjalnych itp. Zwykle przyjmuje się jedną zasadę dla nazw tabel, a drugą dotyczącą obiektów powiązanych z tabelami. RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

40 Projektowanie baz danych - notacjeObierając system notacji należy pamiętać o wszystkich typach elementów bazy danych, które powinny być wyróżnione w nazwach. Nazwa tabeli Nazwa kolumn(y) PK (np. _PK) Nazwa kolumn(y) FK (np. _FK) Nazwa kolumn(y) specyficznych (data, flaga itp.) Nazwa tabeli zależnej (np. _elements) Widoki (np. V_) Indexy (np. _IDXn) Triggery (np. _TRGn) Procedury Funkcje RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

41 Rozdział III: Składniki relacyjnych baz danychRRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

42 SQL - CREATE CREATE name_object (); CREATE TABLE ( col1 [primary key] col2 [[not] null] [default value ]); CREATE TABLE INDEX indexName ON tableName (col1,col2); Umożliwia utworzenie nowego obiektu (tabela,widok,index,procedura itp.) Na początek chciałem pokazać ogólne polecenia operując na obiektach – tworzenie, usuwanie, modyfikacje. Polecenia te są dość uniwersalne i w większym lub mniejszym stopniu działają na wszystkie objekty. RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

43 Powoduje usunięcie obiektu z bazySQL - DROP DROP object_name; DROP TABLE CASCADE CONSTRAINTS; DROP VIEW ; DROP PROCEDURE ; Powoduje usunięcie obiektu z bazy RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

44 Powoduje zmianę struktury obiektuSQL - ALTER ALTER TABLE tab1 ADD (col5 ); ALTER TABLE tab1 RENAME COLUMN col5 TO col5_new; ALTER TABLE tab1 ADD CONSTRAINT pk_id PRIMARY KEY (id); ALTER PROCEDURE my_procedure COMPILE; Powoduje zmianę struktury obiektu RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

45 Obiekty bazy danych - tabelaTabela – podstawowa jednostka przechowywania danych w bazie. Każda tabela składa się z kolumn i wierszy. CREATE TABLE tableName (); DROP TABLE tableName; ALTER TABLE tableName ADD col2 ; CREATE TABLE users ( id int, name varchar(100), lastName varchar(100) ); RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

46 Obiekty bazy danych - tabelaTypy danych w systemie FirebirdSQL: CHAR VARCHAR TIMESTAMP SMALLINT NUMERIC INTEGER DECIMAL FLOAT DOUBLE BLOB RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL CREATE TABLE users ( id int, name varchar(100), lastName varchar(100) );

47 Obiekty bazy danych - tabelaWidok – zapytanie zapisane. Są to pseudotabele, które zamiast danych przechowują zapytania do tabel. Służą głównie do uproszczenia lub przyśpieszenia innych zapytań. CREATE VIEW viewName () AS DROP VIEW viewName; CREATE VIEW admini (name, lastname) AS select name,lastname from users where admin=1; RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL CREATE TABLE users ( id int, name varchar(100), lastName varchar(100) );

48 Obiekty bazy danych - indexIndeks – struktura powiązana z tabelą, pozwalająca na przyspieszenie wyszukania. Na każdej tabeli może być jeden lub więcej założonych indeksów. Indeksy mogą być unikalne lub nieunikalne Indeksy mogą być jedno i wielokolumnowe. CREATE INDEX indexName ON tableName () [unique]; DROP INDEX indexName; CREATE INDEX usersIDX1 (name, lastname) unique; RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

49 Obiekty bazy danych - indexRRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

50 Elementy bazy danych - constraintCONSTRAINT – reguła pilnująca dane w tabeli. Jest to funkcja, która zawsze zwraca TRUE. Dzielimy na: NOT NULL UNIQUE KEY PRIMARY KEY FOREIGN KEY CHECK. Jeśli dane w tabeli nie spełniają reguły to constraint'a nie da się założyć. Jeśli spróbujemy wstawić dane łamiące reguły to dostaniemy błędem. PRIMARY KEY jest mocno zalecany FOREIGN KEY jest powszechnym sposobem pilnowania zależności między tabelami (relacje – relacyjna baza danych) CHECK w wielu przypadkach jest wydajniejszy od testowania z poziomu aplikacji (zwłaszcza przetwarzanie wsadowe) Bardzo wskazane do współpracy z przetwarzaniem wsadowym RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

51 Elementy bazy danych – constraintcheck (value > 10000) check (Town like 'Amst%') check (upper(value) in ( 'A', 'B', 'X' )) check (Minimum <= Maximum) create table MyData ( id int not null primary key, record_created timestamp default current_timestamp) create table eik ( a int not null primary key, b int not null unique); create table beuk ( b int references eik); RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

52 Rozdział IV: Podstawy SQLaRRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

53 DML (ang. Data Manipulation Language) SQL SQL (ang. Structured Query Language ) – strukturalny język zapytań używany do tworzenia, modyfikowania baz danych oraz do umieszczania i pobierania danych z baz danych. (pl.wikipedia.org) DML (ang. Data Manipulation Language) select, insert, update, delete DDL (ang. Data Definition Language) create, drop, alter DCL (ang. Data Control Language) grant, revoke, deny RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

54 INSERT INTO tab1 [(col1,col2,col3)] VALUES (val1,val2,val3);SQL - INSERT INSERT INTO tab1 [(col1,col2,col3)] VALUES (val1,val2,val3); INSERT INTO tab1 [(col1,col2,col3)] SELECT val1,val2,val3 FROM other_table; Typ wstawianej wartości musi się zgadzać z typem kolumny Brak wpisanej wartości do kolumny jest równoważne w wstawieniem wartości NULL Przelicza indeksy i sprawdza constrainty (np. klucze obce) RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

55 UPDATE tab1 SET col1 = val1 WHERE ; SQL - UPDATE UPDATE tab1 SET col1 = val1 WHERE ; UPDATE tab1 t1 SET col4 = 4 WHERE t1.col2 = 1; UPDATE tmp_visits v SET vis_prevTime = (select max(t.vis_time) from tmp_visits t where t.vis_time < v.vis_time); Zapytanie zmienia wartość we wskazanej kolumnie dla wszystkich wierszy bądź wskaznych w klauzuli WHERE Typ zmienianej wartości musi się zgadzać z typem kolumny (możliwe są konwersje niejawne) Przelicza indeksy i sprawdza constrainty (np. klucze obce) RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

56 Usuwa wszystkie rekordy z tabeli bądź wskaznych w klauzuli WHERE SQL - DELETE DELETE FROM tab2 WHERE col3 = ; Usuwa wszystkie rekordy z tabeli bądź wskaznych w klauzuli WHERE Przelicza indeksy i sprawdza constrainty (np. klucze obce) TRUNCATE usuwa szybko i wszystko RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

57 Wynikiem jest zbiór danych SQL - SELECT SELECT col1 , col2 FROM tab1 , tab2 JOIN tab3 ON WHERE GROUP BY col1 , col2 HAVING ORDER BY col1 , col2 Wynikiem jest zbiór danych W SELECT można używać funkcji agregujących: min(),max(),sum(),count(),list() (select list('[' || va.VAN_CODE || '] ' || va.VAN_DESCRIPTION,'
') from DRT_VISIT_ANALYSIS va where va.VAN_VIS_ID = v.vis_id;) Jeśli użyło się fkcji agregującej, wszystkie pozostałe kolumny muszą znaleźć się w GROUP BY (np. select count(*),PAR_NAME from DRT_APPOINTMENTS group by PAR_NAME;) RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

58 SQL - SELECT W FROM zamiast tabeli można użyć innego zapytania (podzapytanie), ujmując je w nawias (np. SELECT name FROM (SELECT name FROM address_book WHERE city = 'Ladek Zdroj');) Niestety Firebird słabo to optymalizuje. Sortowanie (ORDER BY) można ustawić rosnąco (ASC) lub malejąco DESC. Domyślnie sortowanie jest ASC. Klauzulę HAVING stosuje się w przypadku użycia funkcji agregujących z grupowaniem (GROUP BY), przy żądaniu wyników których agregacja ma spełniać określony warunek (np. select count(*),PAR_NAME from DRT_APPOINTMENTS group by PAR_NAME having count(*) >1;) Użycie słowa kluczowego DISTINCT powoduje ograniczenie zapytania do niepowtarzających się wierszy (np. SELECT DISTINCT name FROM addres_book WHERE city='Ladek Zdroj';) RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

59 SQL – SELECT (inner / left join)FROM tab1 , tab2 INNER JOIN tab3 on tab1.col3 = tab3.col1 LEFT JOIN tab4 on tab1.col4 = tab4.col1 and tab4.col4 = 1 WHERE tab1.col2 = tab2.col1 And tab4.col4 = 1 And tab4.id is null; Perfidny przykład RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

60 SQL – SELECT (inner / left join)FROM tab1 , tab2 INNER JOIN tab3 on tab1.col3 = tab3.col1 LEFT JOIN tab4 on tab1.col4 = tab4.col1 and tab4.col4 = 1 WHERE tab1.col2 = tab2.col1 And tab4.col4 = 1 And tab4.id is null; pokaże wszystkie rekordy z tab1, które łączą się z tab3 RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

61 SQL – SELECT (inner / left join)FROM tab1 , tab2 INNER JOIN tab3 on tab1.col3 = tab3.col1 LEFT JOIN tab4 on tab1.col4 = tab4.col1 and tab4.col4 = 1 WHERE tab1.col2 = tab2.col1 And tab4.col4 = 1 And tab4.id is null; pokaże wszystkie rekordy z tab1, które łączą się z tab2 RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

62 SQL – SELECT (inner / left join)FROM tab1 , tab2 INNER JOIN tab3 on tab1.col3 = tab3.col1 LEFT JOIN tab4 on tab1.col4 = tab4.col1 and tab4.col4 = 1 WHERE tab1.col2 = tab2.col1 And tab4.col4 = 1 And tab4.id is null; pokaże wszystkie rekordy z tab1 oraz te z tab4, które łączą się z powyższymi jeśli dla jakiegoś wiersza tab1 i tab2 nie ma wartości w tab4, to odpowiednie kolumny w wynikach będą NULLowe RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

63 SQL – SELECT (inner / left join)FROM tab1 , tab2 INNER JOIN tab3 on tab1.col3 = tab3.col1 LEFT JOIN tab4 on tab1.col4 = tab4.col1 and tab4.col4 = 1 WHERE tab1.col2 = tab2.col1 And tab4.col4 = 1 And tab4.id is null; które położenie jest właściwe? RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

64 SQL – SELECT (inner / left join)FROM tab1 , tab2 INNER JOIN tab3 on tab1.col3 = tab3.col1 LEFT JOIN tab4 on tab1.col4 = tab4.col1 and tab4.col4 = 1 WHERE tab1.col2 = tab2.col1 And tab4.col4 = 1 And tab4.id is null; Jaki będzie efekt powyższego zapytania? RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

65 SQL – SELECT (inner / left join)FROM tab1 , tab2 INNER JOIN tab3 on tab1.col3 = tab3.col1 LEFT JOIN tab4 on tab1.col4 = tab4.col1 and tab4.col4 = 1 WHERE tab1.col2 = tab2.col1 And tab4.col4 = 1 And tab4.id is null; Proste? RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

66 SELECT count(*) , APP_DR_ID FROM DRT_APPOINTMENTS GROUP BY APP_DR_ID SQL – SELECT (GROUP BY) SELECT count(*) , APP_DR_ID FROM DRT_APPOINTMENTS GROUP BY APP_DR_ID HAVING count(*) >10 ORDER BY APP_DR_ID; Przykład RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

67 SELECT count(*) , APP_DR_ID FROM DRT_APPOINTMENTS GROUP BY APP_DR_ID SQL – SELECT (GROUP BY) SELECT count(*) , APP_DR_ID FROM DRT_APPOINTMENTS GROUP BY APP_DR_ID HAVING count(*) >10 ORDER BY APP_DR_ID; Podlicza wszystkie rekordy w DRT_APPOINTMENTS RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

68 SELECT count(*) , APP_DR_ID FROM DRT_APPOINTMENTS GROUP BY APP_DR_ID SQL – SELECT (GROUP BY) SELECT count(*) , APP_DR_ID FROM DRT_APPOINTMENTS GROUP BY APP_DR_ID HAVING count(*) >10 ORDER BY APP_DR_ID; Podlicza ilość rekordów w DRT_APPOINTMENTS dla każdego APP_DR_ID RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

69 SELECT count(*) , APP_DR_ID FROM DRT_APPOINTMENTS GROUP BY APP_DR_ID SQL – SELECT (GROUP BY) SELECT count(*) , APP_DR_ID FROM DRT_APPOINTMENTS GROUP BY APP_DR_ID HAVING count(*) >10 ORDER BY APP_DR_ID; Oblicza dla każdego i wybiera tylko te, których jest więcej niż 10 RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

70 SELECT count(*) , APP_DR_ID FROM DRT_APPOINTMENTS GROUP BY APP_DR_ID SQL – SELECT (GROUP BY) SELECT count(*) , APP_DR_ID FROM DRT_APPOINTMENTS GROUP BY APP_DR_ID HAVING count(*) >10 ORDER BY APP_DR_ID; Oblicza dla każdego i wybiera tylko te, których jest więcej niż 10, a na koniec sortuje po APP_DR_ID rosnąco RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

71 SELECT count(*) ilosc , APP_DR_ID FROM DRT_APPOINTMENTS SQL – SELECT (GROUP BY) SELECT * FROM ( SELECT count(*) ilosc , APP_DR_ID FROM DRT_APPOINTMENTS GROUP BY APP_DR_ID HAVING count(*) >10 ) ORDER BY ilosc; Aby posortować po wynikach podsumowań, trzeba zbudować podzapytanie RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

72 SQL – SELECT (konwencja 1)SELECT s.MON$STATEMENT_ID, s.MON$ATTACHMENT_ID, s.MON$TRANSACTION_ID, s.MON$STATE, s.MON$TIMESTAMP, s.MON$SQL_TEXT, s.MON$STAT_ID, i.MON$STAT_GROUP, (i.MON$PAGE_READS * d.MON$PAGE_SIZE)/(1024*1024) as MBreads, (i.MON$PAGE_WRITES * d.MON$PAGE_SIZE)/(1024*1024) as MBwrites, (i.MON$PAGE_FETCHES * d.MON$PAGE_SIZE)/(1024*1024) as MBfetches, (i.MON$PAGE_MARKS * d.MON$PAGE_SIZE)/(1024*1024) as MBmarks, a.MON$STATE, a.MON$ATTACHMENT_NAME, a.MON$USER, a.MON$REMOTE_ADDRESS, a.MON$TIMESTAMP, a.MON$REMOTE_PROCESS, t.MON$TRANSACTION_ID, t.MON$STATE, t.MON$TIMESTAMP, t.MON$ISOLATION_MODE, t.MON$LOCK_TIMEOUT, t.MON$TOP_TRANSACTION, t.MON$OLDEST_TRANSACTION, t.MON$OLDEST_ACTIVE, c.MON$CALL_ID, c.MON$STATEMENT_ID, c.MON$CALLER_ID, c.MON$OBJECT_NAME, c.MON$OBJECT_TYPE, c.MON$TIMESTAMP, c.MON$SOURCE_LINE, c.MON$SOURCE_COLUMN, c.MON$STAT_ID FROM MON$IO_STATS i inner join MON$DATABASE d on 1=1 inner join MON$ATTACHMENTS a on a.mon$stat_id=i.mon$stat_id and a.MON$STATE=1 inner join MON$TRANSACTIONS t on t.MON$STAT_ID = i.mon$stat_id and t.MON$STATE=1 inner join MON$STATEMENTS s on s.MON$STAT_ID = i.mon$stat_id OR s.MON$TRANSACTION_ID = t.MON$TRANSACTION_ID inner join MON$CALL_STACK c on c.MON$STAT_ID = i.MON$STAT_ID WHERE i.MON$STAT_GROUP=3 and a.MON$REMOTE_ADDRESS=' ' order by i.MON$PAGE_FETCHES desc; RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

73 SQL – SELECT (konwencja 2)SELECT s.MON$STATEMENT_ID, s.MON$ATTACHMENT_ID, s.MON$TRANSACTION_ID, s.MON$STATE, s.MON$TIMESTAMP, s.MON$SQL_TEXT, s.MON$STAT_ID, i.MON$STAT_GROUP, (i.MON$PAGE_READS * d.MON$PAGE_SIZE)/(1024*1024) as MBreads, (i.MON$PAGE_WRITES * d.MON$PAGE_SIZE)/(1024*1024) as MBwrites, (i.MON$PAGE_FETCHES * d.MON$PAGE_SIZE)/(1024*1024) as MBfetches, (i.MON$PAGE_MARKS * d.MON$PAGE_SIZE)/(1024*1024) as MBmarks, a.MON$STATE, a.MON$ATTACHMENT_NAME, a.MON$USER, a.MON$REMOTE_ADDRESS, a.MON$TIMESTAMP, a.MON$REMOTE_PROCESS, t.MON$TRANSACTION_ID, t.MON$STATE, t.MON$TIMESTAMP, t.MON$ISOLATION_MODE, t.MON$LOCK_TIMEOUT, t.MON$TOP_TRANSACTION, t.MON$OLDEST_TRANSACTION, t.MON$OLDEST_ACTIVE, c.MON$CALL_ID, c.MON$STATEMENT_ID, c.MON$CALLER_ID, c.MON$OBJECT_NAME, c.MON$OBJECT_TYPE, c.MON$TIMESTAMP, c.MON$SOURCE_LINE, c.MON$SOURCE_COLUMN, c.MON$STAT_ID FROM MON$IO_STATS i , MON$DATABASE d , MON$ATTACHMENTS a , MON$TRANSACTIONS t , MON$STATEMENTS s , MON$CALL_STACK c WHERE i.MON$STAT_GROUP=3 AND a.mon$stat_id=i.mon$stat_id AND a.MON$STATE=1 AND a.MON$REMOTE_ADDRESS=' ' AND t.MON$STAT_ID = i.mon$stat_id AND t.MON$STATE=1 AND (s.MON$STAT_ID = i.mon$stat_id OR s.MON$TRANSACTION_ID = t.MON$TRANSACTION_ID) AND c.MON$STAT_ID = i.MON$STAT_ID order by i.MON$PAGE_FETCHES desc; RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

74 < = > IS [not] NULL SQL - warunki < = > IS [not] NULL [not] IN () [not] IN (select col from tab where ) [not] EXISTS (select 1 from tab where nadrz.id = id) BETWEEN AND Like '*' select count(*) , type from group by type HAVING count(*)<10; select * from where col1 is null OR col1=0; select * from where COALESCE(col1,0)=0 RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

75 SQL - triggery CREATE TRIGGER name [ACTIVE | INACTIVE]{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON {tablename | viewname} AS [] BEGIN [] END CREATE TRIGGER DRP_APPOINTMENTS_APP_ID_TRG active before insert or update on DRT_APPOINTMENTS AS DECLARE VARIABLE USER_SESSION_ID INTEGER; BEGIN SELECT CURRENT_CONNECTION FROM RDB$DATABASE INTO :USER_SESSION_ID; IF (NEW.APP_ID IS NULL) THEN NEW.APP_ID = GEN_ID(GEN_APPOINTMENTS, 1); IF (NEW.APP_SUN_ID IS NULL) THEN NEW.APP_SUN_ID = GEN_ID(GEN_SERVICE_UNITS, 1); UPDATE DRT_LAST_ID SET LID_TIME=CURRENT_TIMESTAMP, LAST_ID = NEW.APP_ID WHERE SESSION_ID = :USER_SESSION_ID; IF (ROW_COUNT = 0) THEN INSERT INTO DRT_LAST_ID (SESSION_ID, LAST_ID) VALUES (:USER_SESSION_ID, NEW.APP_ID); END RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

76 CREATE OR ALTER TRIGGER DROP TRIGGER RECREATE TRIGGERSQL - triggery db_event: CONNECT | DISCONNECT | TRANSACTION START | TRANSACTION COMMIT | TRANSACTION ROLLBACK CREATE OR ALTER TRIGGER DROP TRIGGER RECREATE TRIGGER Bardzo przydatne w realizacji stałych operacji na danych „zawsze kiedy … to … „ - rozwiązanie bezpieczne i wydajne Bardzo przydatne przy przetwarzaniu wsadowym RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

77 CREATE PROCEDURE procname SQL - procedury CREATE PROCEDURE procname [{= | DEFAULT} value] [, {= | DEFAULT} value] ...])] [RETURNS ( [, ...])] AS [] BEGIN [] END RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

78 DECLARE EXTERNAL FUNCTION localname SQL - funkcje DECLARE EXTERNAL FUNCTION localname [ [, ...]] RETURNS { | PARAMETER 1- based_pos} [FREE_IT] ENTRY_POINT 'function_name' MODULE_NAME 'library_name' RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

79 http://www.firebirdsql.org/refdocs/langrefupd21.h tmlŹródła pomocy tml RRRR-MM-DD COMPANY/UNIT_PROJECT/PRS-RR:NNN UPL

80 Dziękuję za uwagę. Marek Dziedziczak, DB, DB1Ericpol Telecom Sp. z o.o. Tel.: