SQL – część II. - SLIDEPPTX.COM

SQL – część II.

1 SQL – część II ...

Author: Cibor Rorat

0 downloads 1 Views

1 SQL – część II

2 Modyfikowanie danych

5 Kopiowanie wierszy Uwaga! Kolumny zwracane przez SELECT muszą odpowiadać tym, z których składa się tabela docelowa, co do ilości i typu danych Tworzenie trwałych kopii tabel czy zapytań ma sens tylko wtedy gdy skraca przetwarzanie danych lub ułatwia edycję w osadzonym SQL

6 Modyfikowanie danych UPDATE LiniaZamowienia SET Cena = Cena * 1.1WHERE IdZamowienia IN SELECT IdZamowienia FROM Zamowienie WHERE DataZamowienia = ' ' AND IdKlienta = SELECT IdKlienta FROM Klient WHERE NazwaKlienta = 'FH Klin SA'

7 Usuwanie wierszy DELETE FROM LiniaZamowienia WHERE IdZamowienia INSELECT IdZamowienia FROM Zamowienie WHERE DataZamowienia = ' ' AND IdKlienta = SELECT IdKlienta FROM Klient WHERE NazwaKlienta = 'FH Klin SA'

8 Przykładowa baza danych

9 Tworzenie i użycie tabelicreate database Dydaktyka Use Dydaktyka

10 Tworzenie tabel i związkówCreate table Klient IdKlienta Bigint Identity NOT NULL, NazwaKlienta Char100 NULL, Primary Key IdKlienta Create table NaglowekFaktury NrFaktury Char20 NOT NULL, DataFaktury Datetime NULL, IdKlienta Bigint NOT NULL, Primary Key NrFaktury, foreign keyIdKlienta references Klient IdKlienta on update no action on delete no action

11 Tworzenie tabel i związkówCreate table Cennik DataOd Datetime NOT NULL, IdTowaru Bigint NOT NULL, DataDo Datetime NOT NULL, Cena Money NULL, Primary Key DataOd,IdTowaru, foreign keyIdTowaru references Towary IdTowaru on update no action on delete no action

12 Wprowadzanie danych insert into Cennik DataOd, DataDo, IdTowaru, Cenavalues ' ', ' ',1,2 values ' ', '',2,3 values ' ', ' ',1,2.2

13 Prezentacja wszystkich krotekselect * from cennik

14 Wszystkie dane

15 Złączenie nieprawidłoweSELECT Klient.NazwaKlienta, NaglowekFaktury.NrFaktury, NaglowekFaktury.DataFaktury, Towary.NazwaTowaru, SpecyfikacjaFaktury.Ilosc, JednostkiMiary.JednostkaMiary, Cennik.Cena FROM SpecyfikacjaFaktury INNER JOIN JednostkiMiary ON SpecyfikacjaFaktury.JednostkaMiary = JednostkiMiary.JednostkaMiary INNER JOIN NaglowekFaktury ON SpecyfikacjaFaktury.NrFaktury = NaglowekFaktury.NrFaktury INNER JOIN Klient ON NaglowekFaktury.IdKlienta = Klient.IdKlienta INNER JOIN Towary ON SpecyfikacjaFaktury.IdTowaru = Towary.IdTowaru INNER JOIN Cennik ON Towary.IdTowaru = Cennik.IdTowaru ORDER BY NaglowekFaktury.NrFaktury

16 Efekt

17 Poprawne złączenie SELECT NazwaKlienta, NaglowekFaktury.NrFaktury, DataFaktury, NazwaTowaru, Ilosc, JednostkiMiary.JednostkaMiary, Cena FROM SpecyfikacjaFaktury JOIN JednostkiMiary ON SpecyfikacjaFaktury.JednostkaMiary = JednostkiMiary.JednostkaMiary JOIN NaglowekFaktury ON SpecyfikacjaFaktury.NrFaktury = NaglowekFaktury.NrFaktury JOIN Klient ON NaglowekFaktury.IdKlienta = Klient.IdKlienta JOIN Towary ON SpecyfikacjaFaktury.IdTowaru = Towary.IdTowaru JOIN Cennik ON Towary.IdTowaru = Cennik.IdTowaru AND NaglowekFaktury.DataFaktury > Cennik.DataOd AND NaglowekFaktury.DataFaktury < Cennik.DataDo OR Cennik.DataDo IS NULL ORDER BY NaglowekFaktury.NrFaktury

18 Efekt

19 Obliczamy wartość SELECT NazwaKlienta, NaglowekFaktury.NrFaktury, DataFaktury, NazwaTowaru, Ilosc, JednostkiMiary.JednostkaMiary, Cena, Ilosc * Cena AS Wartosc

20 Efekt

21 Integralność danych w SQLWprowadzenie kluczy podstawowych i obcych zapewnia automatyczną kontrolę poprawności struktury danych i operacji przetwarzania danych Klucz podstawowy zapewnia unikalność i możliwość identyfikacji każdego zapisu Klucze obce zapewniają integrację referencyjną głoszącą, że każda niepusta wartość klucza obcego musi odpowiadać jednej z istniejących wartości klucza podstawowego

23 Klucz podstawowy W tablicach rodzicach (parent) jest niezbędny jako łącznik z tablicami dziećmi (child) Najlepiej rolę tą spełnia klucz, który jest kolumną tożsamości (identity)

24 Klucz podstawowy w definicji i zmianie tabeliCREATE TABLE Towary ( IdTowaru Int NOT NULL AUTO_INCREMENT, NazwaTowaru Char(50), PRIMARY KEY (IdTowaru)) ENGINE = MyISAM ROW_FORMAT = Default; ALTER TABLE Towary DROP PRIMARY KEY; ALTER TABLE Towary ADD PRIMARY KEY (IdTowaru);

25 Klucze obce W związkach nieidentyfikujących pozwalają na tworzenie złączeń (nie jest do tego konieczne wymuszanie integralności) W związkach identyfikujących pozwalają na kontrolę unikalności krotek w relacjach dzieciach

26 CREATE TABLE Klient ( IdKlienta Int NOT NULL AUTO_INCREMENT, NazwaKlienta Char(50), Adres Char(50), PRIMARY KEY (IdKlienta)) ENGINE = MyISAM ROW_FORMAT = Default; CREATE TABLE Zamowienie ( IdZamowienia Int NOT NULL AUTO_INCREMENT, DataZamowienia Date, NrZamowienia Char(20), IdKlienta Int, PRIMARY KEY (IdZamowienia)) ENGINE = MyISAM

27 Złączenie naturalne

28 Złożony klucz obcy CREATE TABLE JednostkiMiary (IdJednostki Int NOT NULL AUTO_INCREMENT, JednostkaMiary Char(20), PRIMARY KEY (IdJednostki)) ENGINE = MyISAM ROW_FORMAT = Default; CREATE TABLE Specyfikacja ( IdZamowienia Int NOT NULL, IdTowaru Int NOT NULL, IdJednostki Int NOT NULL, Ilosc Float, PRIMARY KEY (IdZamowienia,IdTowaru,IdJednostki)) ENGINE = MyISAM

29 próba wprowadzenia identycznego klucza

30 Wymuszanie integralnościREFERENCES – podaje źródło klucza obcego, tj. tabelę i klucz podstawowy ON DELETE, ON UPDATE – określenie czynności, które należy podjąć jeśli wartość klucza podstawowego zostanie usunięta lub ulegnie zmianie: SET NULL zastąp wartość klucza obcego przez NULL, SET DEFAULT zastąp wartość klucza obcego przez wartość domyślną, CASCADE skasuj lub zmodyfikuj wszystkie wiersze zawierające zmienianą wartość klucza obcego NO ACTION (tylko modyfikacja) nie zmieniaj wartości klucza RESTRICT nie dopuść do zmiany

31 Zabronione usuwanie w MySQLALTER TABLE `zamowienie` TYPE = INNODB; ALTER TABLE `klient` TYPE = INNODB; ALTER TABLE Zamowienie ADD FOREIGN KEY (IdKlienta) REFERENCES Klient (IdKlienta) ON DELETE RESTRICT ON UPDATE RESTRICT; DELETE FROM `klient` WHERE `IdKlienta` =1 LIMIT 1 # Cannot delete a parent row: a foreign key constraint fails

33 Kaskadowa aktualizacja w MySQLALTER TABLE Zamowienie ADD FOREIGN KEY ( IdKlienta ) REFERENCES Klient( IdKlienta ) ON DELETE CASCADE ON UPDATE CASCADE DELETE FROM `klient` WHERE `IdKlienta` =1

35 Porządkowanie kluczy w MySQLZmiana wartości klucza w tabeli rodzicielskiej powoduje odpowiednie zmiany w tabelach dzieciach UPDATE `klient` SET `IdKlienta` = '8‘ WHERE `IdKlienta` =10;

37 Struktura baz danych Dodatkowe elementy

38 Perspektywy (ang. Views)Zapytanie posiadające własną nazwę i przechowywane w słowniku danych Perspektywy tworzymy po to by: zapisać często wykonywane złożone zapytania stworzyć logiczne modele dla różnych użytkowników zwiększyć bezpieczeństwo danych

39 Tworzenie perspektyw CREATE VIEW PodgladZamowieniaSELECT Klient.NazwaKlienta, Zamowienie.NrZamowienia, Zamowienie.datazamowienia, Towar.NazwaTowaru, LiniaZamowienia.Ilosc, LiniaZamowienia.Cena FROM Zamowienie INNER JOIN Klient ON Zamowienie.IdKlienta = Klient.IdKlienta INNER JOIN LiniaZamowienia ON Zamowienie.IdZamowienia = LiniaZamowienia.IdZamowienia INNER JOIN Towar ON LiniaZamowienia.IdTowaru = Towar.IdTowaru

41 Modyfikowalność perspektywZgodnie ze standardem SQL-92 można modyfikować wyłącznie takie perspektywy, które: nie zawierają złączeń, są pojedyncze (np. unie nie są dopuszczalne), nie mogą być oparte na zapytaniu grupującym lub zawierającym słowo DISTINCT, nie można modyfikować kolumn wyliczonych

42 Przyznawanie praw dostępuSerwer baz danych może obsługiwać wielu użytkowników identyfikowanych przez nazwę i hasło Nie każdy z użytkowników musi mieć równe prawa W momencie utworzenia nowego elementu bazy danych aktualny użytkownik staje się jego właścicielem Właściciel może nadawać i odbierać prawa innym użytkownikom

43 Przyznawanie praw dostępu składnia SQL99GRANT {ALL [PRIVILEGES] } | SELECT | INSERT [ nazwa_kolumny [,...n]) ] | DELETE | UPDATE [ nazwa_kolumny [,...n]) ] | REFERENCES [ nazwa_kolumny [,...n]) ] | USAGE } [,...n] ON { [TABLE] nazwa_tabeli | DOMAIN nazwa_domeny | COLLATION nazwa_zestawienia CHARACTER SET nazwa_zestawu_znaków | TRANSLATION nazwa_translacji } TO {nazwa_podmiotu | PUBLIC} [WITH GRANT OPTION]

44 Przykład MySQL Aktualny użytkownik, posiadający wszelkie uprawnienia przekazuje część uprawnień użytkownikowi andrzej na serwerze localhost Następnie odbiera mu wszelkie uprawnienia i przekazuje inne

45 haslo GRANT CREATE , DROP , INDEX , ALTER , CREATE TEMPORARY TABLES ON * . * TO IDENTIFIED BY '*****' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 ; GRANT ALL PRIVILEGES ON `sprzedaz` . * TO WITH GRANT OPTION ;

46 REVOKE ALL PRIVILEGES ONREVOKE ALL PRIVILEGES ON * . * FROM GRANT SELECT , INSERT , UPDATE , DELETE , RELOAD , SHUTDOWN , PROCESS , FILE , REFERENCES , SHOW DATABASES , SUPER , LOCK TABLES , EXECUTE , REPLICATION SLAVE , REPLICATION CLIENT ON * . * TO 'localhost' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 ;

47 Nowy użytkownik Logowanie nowego użytkownikaUżytkownik ma prawa do edycji danych, nie może jednak zmieniać struktur bazy danych

48 brak uprawnień do tworzenia tabelisą uprawnienia do aktualizacji tabeli

SQL – część II.

Recommend Documents