MySQL-Integralność, zapytania

Transcript MySQL-Integralność, zapytania

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: 1. dane wymagane; 2. więzy dziedzinowe; 3. integralność encji; 4. integralność referencyjna (związków); 5. więzy ogólne.

Wszystkie one mogą być zdefiniowane w poleceniach CREATE i ALTER TABLE .

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.

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

) 3

W więzach kolumnowych w klauzuli kolumnę następująco: 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ę plec CHAR NOT NULL CHECK (plec IN ('M','K')) 4

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 można zdefiniować regułę poprawności dla dziedziny za pomocą , typ danych i, opcjonalnie, wartość domyślną. Również opcjonalnie klauzuli CHECK .

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 6

Parametr

warunek_selekcji

moż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. 7

Dziedzinę można usunąć z bazy danych poleceniem DROP DOMAIN : DROP DOMAIN NazwaDziedziny [RESTRICTI CASCADE] Zasięg usunięcia, dziedzinę.

RESTRICT lub CASCADE , określa tryb postępowania w przypadku, gdy usuwamy używaną aktualnie 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.

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) 9

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 tabeli.

PRIMARY KEY może wystąpić tylko raz w definicji 10

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 potrzebnych.

UNIQUE , ile jest 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 napisać:

Wizyta

moglibyśmy w definicji klientNr VARCHAR{5) NOT NULL, nieruchomośćNr VARCHAR(5)NOT NULL, UNIQUE (klientNr, nieruchomośćNr) 11

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 .

Na przykład, aby zdefiniować klucz obcy Nieruchomość , dodajemy klauzulę: biuroNr w tabeli 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.

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 ON DELETE 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ą.

• 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. 15

Powiązania kluczy obcych:

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

mysql> INSERT INTO personel VALUES ('SL33', 'Paweł', 'Biały','asystent','M','1982-03-16',1100,'B008'); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`biuro`.`personel`, CONSTRAINT `biuroNr` FOREIGN KEY (`biuroNr`) REFERENCES `biuro` (`biuroNr`) ON DELETE CASCADE ON UPDATE CASCADE) mysql> 17

Aktualizacja obiektów w tabeli nadrzędnej:

mysql> UPDATE biuro SET biuroNr='B008' WHERE miasto='Augustów' and ulica='Akacjowa 16'; Query OK, 1 rows affected (0.03 sec) Rows matched: 0 Changed: 0 Warnings: 0 mysql> SELECT * FROM biuro; +---------+--------------+-----------+--------+ | biuroNr | ulica | miasto | kod | +---------+--------------+-----------+--------+ | B001 | Piękna 46 | Białystok | 15-900 | | B002 | Cicha 56 | Łomża | 18-400 | | B003 | Mała 63 | Białystok | 15-900 | | B004 | Miodowa 32 | Grajewo | 19-200 | | B005 | Dobra 22 | Łomża | 18-400 | | B006 | Słoneczna 55 | Białystok | 15-900 | | B008 | Akacjowa 16 | Augustów | 16-300 | +---------+--------------+-----------+--------+ 7 rows in set (0.02 sec) 18

mysql> SELECT * FROM personel; 19

Usuwanie obiektów z tabeli nadrzędnej:

mysql> DELETE FROM biuro WHERE ulica='Akacjowa 16'; Query OK, 1 row affected (0.05 sec) mysql> SELECT * FROM biuro; +---------+--------------+-----------+--------+ | biuroNr | ulica | miasto | kod | +---------+--------------+-----------+--------+ | B001 | Piękna 46 | Białystok | 15-900 | | B002 | Cicha 56 | Łomża | 18-400 | | B003 | Mała 63 | Białystok | 15-900 | | B004 | Miodowa 32 | Grajewo | 19-200 | | B005 | Dobra 22 | Łomża | 18-400 | | B006 | Słoneczna 55 | Białystok | 15-900 | +---------+--------------+-----------+--------+ 6 rows in set (0.00 sec) 20

mysql> SELECT * FROM personel; Kolejność usuwania tabel powiązanych: DROP TABLE personel, biuro; 21

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_asercji

CHECK (

warunek_selekcji

) 22

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)); 23

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); 24

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); 25

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

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

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.

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 wyrażenia. 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 29

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 nazwami interesujących nas kolumn. 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 30

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 działanie instrukcji SELECT ogranicza do wierszy spełniających ten warunek. Powinien on być wyrażeniem logicznym, zbudowanym z wykorzystaniem nazw kolumn tabeli. 31

Proste przykłady:

mysql> SELECT 2+2; +-----+ | 2+2 | +-----+ | 4 | +-----+ mysql> SELECT SQRT(16); +----------+ | SQRT(16) | +----------+ | 4 | +----------+ mysql> SELECT IF(5>6,1,2); +-------------+ | IF(5>6,1,2) | +-------------+ | 2 | +-------------+ 32

Komentarze w liniach komend:

mysql> SELECT 1+1; # Komentarz do końca linii mysql> SELECT 1+1; -- Komentarz do końca linii mysql> SELECT 1 /* komentarz w linii */ + 1; mysql> SELECT 1+ /* komentarz składający się z wielu linii */ 1; 33

Składnia:

jest najczęściej używaną instrukcją SQL i ma następującą postać ogólną:

SELECT

[

ALL



DISTINCT

] {*  [wyrażenie_kolumnowe [

nowa_nazwa]],[...]}

FROM

NazwaTabeli [alias],[...] [

WHERE

warunek_selekcji_wierszy] [

GROUP BY

lista_kolumn ] [

HAVING

warunek_selekcji_grup ] [

ORDER BY

lista_kolumn]; 34

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.

Wyszukiwanie wszystkich wierszy:

Wszystkie kolumny i wszystkie wiersze:

SELECT FROM

* personel; mysql> SELECT * FROM personel; +------------+------------+------------+------------+------+------------+--------+---------+ | personelNr | imie | nazwisko | stanowisko | plec | dataUr | pensja | biuroNr | +------------+------------+------------+------------+------+------------+--------+---------+ | SA8 | Katarzyna | Morawska | kierownik | K | 1971-05-06 | 1700 | B007 | | SA9 | Maria | Hojna | asystent | K | 1970-02-19 | 900 | B007 | | SB20 | Sabina | Bober | dyrektor | K | 1940-06-03 | 2400 | B003 | | SB21 | Daniel | Frankowski | kierownik | M | 1958-03-24 | 1800 | B003 | | SB22 | Małgorzata | Kowalska | asystent | K | 1972-03-15 | 1000 | B003 | | SB23 | Anna | Biały | asystent | K | 1960-11-10 | 1200 | B003 | | SB30 | Katarzyna | Michalska | dyrektor | K | 1960-11-17 | 2500 | B006 | | SB31 | Dawid | Piotrowski | asystent | M | 1975-03-22 | 1100 | B006 | | SB32 | Małgorzata | Plichta | asystent | K | 1971-10-03 | 1200 | B006 | | SG20 | Karolina | Mucha | dyrektor | K | 1953-03-03 | 2200 | B004 | | SG21 | Piotr | Cybulski | asystent | M | 1974-12-06 | 1300 | B004 | | SL20 | Paweł | Nowak | kierownik | M | 1962-02-02 | 1500 | B002 | | SL21 | Paweł | Kowalski | asystent | M | 1969-05-05 | 1000 | B002 | | SL22 | Monika | Munk | asystent | K | 1977-07-26 | 1100 | B002 | | SL30 | Jan | Wiśniewski | dyrektor | M | 1945-10-01 | 3000 | B005 | | SL31 | Julia | Lisicka | asystent | K | 1965-07-13 | 900 | B005 | | SL32 | Michał | Brzęczyk | asystent | M | 1959-03-15 | 1000 | B005 | +------------+------------+------------+------------+------+------------+--------+---------+ 17 rows in set (0.00 sec) 36

Wybrane kolumny i wszystkie wiersze:

SELECT FROM

personelNr, imie, nazwisko, pensja personel; mysql> SELECT personelNr, imie, nazwisko, pensja -> FROM personel; +------------+------------+------------+--------+ | personelNr | imie | nazwisko | pensja | +------------+------------+------------+--------+ | SA8 | Katarzyna | Morawska | 1700 | | SA9 | Maria | Hojna | 900 | | SB20 | Sabina | Bober | 2400 | | SB21 | Daniel | Frankowski | 1800 | | SB22 | Małgorzata | Kowalska | 1000 | | SB23 | Anna | Biały | 1200 | | SB30 | Katarzyna | Michalska | 2500 | | SB31 | Dawid | Piotrowski | 1100 | | SB32 | Małgorzata | Plichta | 1200 | | SG20 | Karolina | Mucha | 2200 | | SG21 | Piotr | Cybulski | 1300 | | SL20 | Paweł | Nowak | 1500 | | SL21 | Paweł | Kowalski | 1000 | | SL22 | Monika | Munk | 1100 | | SL30 | Jan | Wiśniewski | 3000 | | SL31 | Julia | Lisicka | 900 | | SL32 | Michał | Brzęczyk | 1000 | +------------+------------+------------+--------+ 17 rows in set (0.00 sec) 37

Sortowanie wyników - wybrane kolumny i wszystkie wiersze uporządkowane malejąco:

SELECT

personelNr, imie, nazwisko, pensja

FROM

personel

ORDER BY

pensja

DESC

; mysql> SELECT personelNr, imie, nazwisko, pensja -> FROM personel -> ORDER BY pensja DESC; +------------+------------+------------+--------+ | personelNr | imie | nazwisko | pensja | +------------+------------+------------+--------+ | SL30 | Jan | Wiśniewski | 3000 | | SB30 | Katarzyna | Michalska | 2500 | | SB20 | Sabina | Bober | 2400 | | SG20 | Karolina | Mucha | 2200 | | SB21 | Daniel | Frankowski | 1800 | | SA8 | Katarzyna | Morawska | 1700 | | SL20 | Paweł | Nowak | 1500 | | SG21 | Piotr | Cybulski | 1300 | | SB32 | Małgorzata | Plichta | 1200 | | SB23 | Anna | Biały | 1200 | | SB31 | Dawid | Piotrowski | 1100 | | SL22 | Monika | Munk | 1100 | | SB22 | Małgorzata | Kowalska | 1000 | | SL21 | Paweł | Kowalski | 1000 | | SL32 | Michał | Brzęczyk | 1000 | | SA9 | Maria | Hojna | 900 | | SL31 | Julia | Lisicka | 900 | +------------+------------+------------+--------+ 17 rows in set (0.00 sec) 38

Porządkowanie według wielu kolumn:

SELECT

nieruchomoscNr, typ, pokoje, czynsz

FROM

nieruchomosc

ORDER BY

typ, czynsz

DESC

; mysql> SELECT nieruchomoscNr, typ, pokoje, czynsz -> FROM nieruchomosc -> ORDER BY typ, czynsz DESC; +----------------+------------+--------+--------+ | nieruchomoscNr | typ | pokoje | czynsz | +----------------+------------+--------+--------+ | G01 | dom | 7 | 830 | | A14 | dom | 6 | 715 | | B21 | dom | 5 | 660 | | B16 | mieszkanie | 4 | 495 | | L94 | mieszkanie | 4 | 440 | | B17 | mieszkanie | 3 | 412 | | B18 | mieszkanie | 3 | 385 | +----------------+------------+--------+--------+ 7 rows in set (0.01 sec) 39

Eliminacja powtórzeń

DISTINCT

– podaj numery nieruchomości odwiedzone przez klientów:

SELECT DISTINCT FROM

wizyta; nieruchomoscNr mysql> SELECT DISTINCT nieruchomoscNr -> FROM wizyta; +----------------+ | nieruchomoscNr | +----------------+ | A14 | | B36 | | B4 | +----------------+ 3 rows in set (0.00 sec) 40

z powtórzeniami:

SELECT FROM

nieruchomoscNr wizyta; mysql> SELECT nieruchomoscNr FROM wizyta; +----------------+ | nieruchomoscNr | +----------------+ | A14 | | B36 | | B4 | | A14 | | B4 | +----------------+ 5 rows in set (0.01 sec) 41

6a.

Wyliczanie pensji rocznej:

SELECT

personelNr, imie, nazwisko, pensja*12

pensja _ Roczna

FROM

mysql> SELECT personelNr, imie, nazwisko, pensja*12 AS pensjaRoczna FROM personel; personel; +------------+------------+------------+---------------+ | personelNr | imie | nazwisko | pensja_Roczna | +------------+------------+------------+---------------+ | SA8 | Katarzyna | Morawska | 20400 | | SA9 | Maria | Hojna | 10800 | | SB20 | Sabina | Bober | 28800 | | SB21 | Daniel | Frankowski | 21600 | | SB22 | Małgorzata | Kowalska | 12000 | | SB23 | Anna | Biały | 14400 | | SB30 | Katarzyna | Michalska | 30000 | | SB31 | Dawid | Piotrowski | | SB32 | Małgorzata | Plichta | | SG20 | Karolina | Mucha | 13200 | 14400 | 26400 | | SG21 | Piotr | Cybulski | | SL20 | Paweł | Nowak | | SL21 | Paweł | Kowalski | | SL22 | Monika | Munk | | SL30 | Jan | Wiśniewski | 15600 | 18000 | 12000 | 13200 | 36000 | | SL31 | Julia | Lisicka | 10800 | | SL32 | Michał | Brzęczyk | +------------+------------+------------+---------------+ 17 rows in set (0.00 sec) 12000 | 42

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

alias

FROM złączenie ORDER BY

alias

Alias nadany wyrażeniu staje się nazwą odpowiedniej kolumny tabeli wynikowej (zamiast dosłownej postaci tego wyrażenia). 43

6a.

wyliczanie pensji rocznej (bez alias’u):

SELECT

personelNr, imie, nazwisko, pensja*12

FROM

personel; mysql> SELECT personelNr, imie, nazwisko, pensja*12 AS pensjaRoczna FROM personel; +------------+------------+------------+--------------+ | personelNr | imie | nazwisko | pensja*12 | +------------+------------+------------+--------------+ | SA8 | Katarzyna | Morawska | 20400 | | SA9 | Maria | Hojna | 10800 | | SB20 | Sabina | Bober | 28800 | | SB21 | Daniel | Frankowski | 21600 | | SB22 | Małgorzata | Kowalska | 12000 | | SB23 | Anna | Biały | 14400 | | SB30 | Katarzyna | Michalska | 30000 | | SB31 | Dawid | Piotrowski | 13200 | | SB32 | Małgorzata | Plichta | 14400 | | SG20 | Karolina | Mucha | 26400 | | SG21 | Piotr | Cybulski | 15600 | | SL20 | Paweł | Nowak | 18000 | | SL21 | Paweł | Kowalski | 12000 | | SL22 | Monika | Munk | 13200 | | SL30 | Jan | Wiśniewski | 36000 | | SL31 | Julia | Lisicka | 10800 | | SL32 | Michał | Brzęczyk | 12000 | +------------+------------+------------+--------------+ 17 rows in set (0.07 sec) 44

6b.

wyliczanie wieku i jego ograniczenie:

SELECT

personelNr, imie, nazwisko, stanowisko, dataUr, Year(Now()) Year(dataUr)

wiek

FROM

personel

WHERE

(Year(Now())-Year(dataUr))>40; mysql> SELECT personelNr, imie, nazwisko, stanowisko, dataUr, Year(Now()) Year(dataUr) AS wiek FROM personel WHERE (Year(Now())-Year(dataUr))>40; +------------+-----------+------------+------------+------------+------+ | personelNr | imie | nazwisko | stanowisko | dataUr | wiek | +------------+-----------+------------+------------+------------+------+ | SB20 | Sabina | Bober | dyrektor | 1940-06-03 | 70 | | SB21 | Daniel | Frankowski | kierownik | 1958-03-24 | 52 | | SB23 | Anna | Biały | asystent | 1960-11-10 | 50 | | SB30 | Katarzyna | Michalska | dyrektor | 1960-11-17 | 50 | | SG20 | Karolina | Mucha | dyrektor | 1953-03-03 | 57 | | SL20 | Paweł | Nowak | kierownik | 1962-02-02 | 48 | | SL21 | Paweł | Kowalski | asystent | 1969-05-05 | 41 | | SL30 | Jan | Wiśniewski | dyrektor | 1945-10-01 | 65 | | SL31 | Julia | Lisicka | asystent | 1965-07-13 | 45 | | SL32 | Michał | Brzęczyk | asystent | 1959-03-15 | 51 | +------------+-----------+------------+------------+------------+------+ 10 rows in set (0.04 sec) 45

6b.

wyliczanie wieku i jego ograniczenie (bez alias’u):

SELECT

personelNr, imie, nazwisko, stanowisko, dataUr, Year(Now()) Year(dataUr)

FROM WHERE

personel (Year(Now())-Year(dataUr))>40; mysql> SELECT personelNr, imie, nazwisko, stanowisko, dataUr, Year(Now())-Year(dataUr) FROM personel WHERE (Year(Now())-Year(dataUr))>40; +------------+-----------+------------+------------+------------+--------------------------+ | personelNr | imie | nazwisko | stanowisko | dataUr | Year(Now())-Year(dataUr) | +------------+-----------+------------+------------+------------+--------------------------+ | SB20 | Sabina | Bober | dyrektor | 1940-06-03 | 70 | | SB21 | Daniel | Frankowski | kierownik | 1958-03-24 | 52 | | SB23 | Anna | Biały | asystent | 1960-11-10 | | SB30 | Katarzyna | Michalska | dyrektor | 1960-11-17 | | SG20 | Karolina | Mucha | dyrektor | 1953-03-03 | | SL20 | Paweł | Nowak | kierownik | 1962-02-02 | | SL21 | Paweł | Kowalski | asystent | 1969-05-05 | 50 | 50 | 57 | 48 | 41 | | SL30 | Jan | Wiśniewski | dyrektor | 1945-10-01 | | SL31 | Julia | Lisicka | asystent | 1965-07-13 | 65 | 45 | | SL32 | Michał | Brzęczyk | asystent | 1959-03-15 | +------------+-----------+------------+------------+------------+--------------------------+ 10 rows in set (0.04 sec) 51 | 46

Wybieranie wierszy:

warunek selekcji: porównanie pensja do stałej

SELECT

stanowisko, pensja

FROM

personelNr, imie, nazwisko, personel

WHERE

złożony warunek selekcji: porównanie – podaj adresy biur w Łomży lub w Białymstoku

SELECT FROM

biuroNr, ulica, miasto, kod Biuro

WHERE

miasto='Łomża'

miasto="Białystok"; mysql> SELECT biuroNr, ulica, miasto, kod -> FROM biuro -> WHERE miasto='Łomża' OR miasto="Białystok"; +---------+--------------+-----------+--------+ | biuroNr | ulica | miasto | kod | +---------+--------------+-----------+--------+ | B001 | Piękna 46 | Białystok | 15-900 | | B002 | Cicha 56 | Łomża | 18-400 | | B003 | Mała 63 | Białystok | 15-900 | | B005 | Dobra 22 | Łomża | 18-400 | | B006 | Słoneczna 55 | Białystok | 15-900 | +---------+--------------+-----------+--------+ 5 rows in set (0.00 sec) 48

warunek selekcji przynależność do zbioru:

SELECT

biuroNr, ulica, miasto, kod

FROM

biuro

WHERE

miasto

('Łomża','Białystok'); mysql> SELECT biuroNr, ulica, miasto, kod -> FROM biuro -> WHERE miasto IN ('Łomża', 'Białystok'); +---------+--------------+-----------+--------+ | biuroNr | ulica | miasto | kod | +---------+--------------+-----------+--------+ | B001 | Piękna 46 | Białystok | 15-900 | | B002 | Cicha 56 | Łomża | 18-400 | | B003 | Mała 63 | Białystok | 15-900 | | B005 | Dobra 22 | Łomża | 18-400 | | B006 | Słoneczna 55 | Białystok | 15-900 | +---------+--------------+-----------+--------+ 5 rows in set (0.40 sec) 49

czy też spoza zbioru:

WHERE

miasto

NOT IN

Inny przykład:

SELECT FROM

personelNr, imie, nazwisko, stanowisko personel

WHERE

stanowisko

lub:

SELECT FROM

personelNr, imie, nazwisko, stanowisko personel

WHERE

stanowisko

NOT IN

10a.

Selekcja pensji z zakresu:

SELECT

personelNr, imie, nazwisko, stanowisko, pensja

FROM

personel

WHERE

pensja

BETWEEN

2000

AND

10a.

Selekcja pensji z zakresu: lub

SELECT

personelNr, imie, nazwisko, stanowisko, pensja

FROM WHERE

personel pensja>=2000

AND

10b.

Selekcja czynszu z zakresu oraz miast

SELECT

nieruchomoscNr, miasto, typ, czynsz

FROM

nieruchomosc

WHERE

350 miasto='Białystok'

AND

450

OR AND

czynsz miasto='Augustów';

BETWEEN

mysql> SELECT nieruchomoscNr, miasto, typ, czynsz -> FROM nieruchomosc -> WHERE miasto='Białystok' AND czynsz BETWEEN 350 AND 450 OR miasto='Augustów'; +----------------+-----------+------------+--------+ | nieruchomoscNr | miasto | typ | czynsz | +----------------+-----------+------------+--------+ | A14 | Augustów | dom | 715 | | B17 | Białystok | mieszkanie | 412 | | B18 | Białystok | mieszkanie | 385 | +----------------+-----------+------------+--------+ 3 rows in set (0.00 sec) 55

11.

warunek selekcji – dopasowanie do wzorca

SELECT

wlascicielNr, imie, nazwisko, adres, telefon

FROM WHERE

wlasciciel adres

LIKE

12.

Warunek selekcji – wartości puste – podaj informacje wizytach w nieruchomości B4, po których nie zgłoszono uwag:

SELECT

klientNr, data_wizyty, uwagi

FROM

wizyta

WHERE

nieruchomoscNr='B4'

AND

uwagi

IS NULL

lub zgłoszono uwagi:

WHERE

nieruchomoscNr=‘B4’

IS NOT NULL

;

AND

uwagi mysql> SELECT klientNr, data_wizyty, uwagi -> FROM wizyta -> WHERE nieruchomoscNr='B4' AND uwagi IS NOT NULL; +----------+-------------+--------------+ | klientNr | data_wizyty | uwagi | +----------+-------------+--------------+ | CR51 | 2010-03-18 | brak jadalni | | CR53 | 2010-04-20 | za daleko | +----------+-------------+--------------+ 2 rows in set (0.01 sec) 58

tabela wizyta : mysql> SELECT * FROM wizyta; +----------+----------------+-------------+--------------+ | klientnr | nieruchomoscnr | data_wizyty | uwagi | +----------+----------------+-------------+--------------+ | CR51 | A14 | 2010-05-24 | za mały | | CR51 | B4 | 2010-03-18 | brak jadalni | | CR52 | A14 | 2010-05-14 | brak jadalni | | CR52 | B36 | 2010-04-28 | NULL | | CR52 | B4 | 2010-05-26 | NULL | | CR53 | B4 | 2010-04-20 | za daleko | +----------+----------------+-------------+--------------+ 6 rows in set (0.00 sec) 59

Zastosowanie funkcji agregujących:

standardzie ISO

zdefiniowanych jest pięć funkcji agregujących:

COUNT

– zwraca liczbę wartości występujących w określonej kolumnie;

SUM

– zwraca sumę wartości występujących w określonej kolumnie;

AVG

– zwraca średnią wartości występujących w określonej kolumnie;

MIN

– zwraca najmniejszą wartość występującą w określonej kolumnie;

MAX

– zwraca największą wartość występującą w określonej kolumnie.

Funkcje

COUNT , MIN

MAX

można stosować zarówno do pól liczbowych jak i nieliczbowych.

Natomiast

SUM

AVG

jedynie do pól liczbowych.

Funkcje agregujące mogą być stosowane jedynie na liście

SELECT

i w klauzuli

HAVING

W MySQL istnieją następujące funkcje agregujące: •

COUNT

(

wyrażenie

) wyrażenie – zlicza wiersze, dla których przyjmuje wartość różną od NULL ; •

AVG

(

wyrażenie

) – oblicza średnią wartość wyrażenia dla uwzględnionych wierszy; •

MIN

(

wyrażenie

MAX

uwzględnionych wierszy; (wyrażenie) – podają odpowiednio minimalną i maksymalną wartość wyrażenia dla •

SUM

(

wyrażenie

) – sumuje wyrażenie po uwzględnionych wierszach; •

STD

(

wyrażenie

) lub

STDDEV

(wyrażenie) standardowe odchylenie wyrażenia; – oblicza •

BIT_OR

(

wyrażenie

BIT_AND

(wyrażenie) alternatywa i odpowiednio koniunkcja bitów wyrażenia. – 62

Przykłady:

Liczba ocen każdego ucznia: mysql> SELECT uczen, COUNT(*)FROM oceny -> GROUP BY uczen; +-------+----------+ | uczen | COUNT(*) | +-------+----------+ | 1 | 7 | | 2 | 9 | +-------+----------+ 2 rows in set (0.05 sec) 63

Liczba ocen każdego ucznia z każdego przedmiotu: mysql> SELECT przedmiot, uczen, COUNT(*) AS liczba -> FROM oceny GROUP BY przedmiot, uczen; +------------+-------+--------+ | przedmiot | uczen | liczba | +------------+-------+--------+ | biologia | 1 | 1 | | biologia | 2 | 2 | | matematyka | 1 | 3 | | matematyka | 2 | 4 | | polski | 1 | 3 | | polski | 2 | 3 | +------------+-------+--------+ 6 rows in set (0.00 sec) 64

Liczba ocen każdego ucznia z wybranych przedmiotów: mysql> SELECT przedmiot, uczen, COUNT(*) AS liczba -> FROM oceny -> WHERE przedmiot='polski' OR przedmiot='matematyka' -> GROUP BY przedmiot, uczen; +------------+-------+--------+ | przedmiot | uczen | liczba | +------------+-------+--------+ | matematyka | 1 | 3 | | matematyka | 2 | 4 | | polski | 1 | 3 | | polski | 2 | 3 | +------------+-------+--------+ 65

Minimum z kolumny w grupie (jaką ma najgorszą ocenę z przedmiotu dany uczeń): mysql> SELECT uczen, przedmiot, MIN(ocena) AS najgorsza -> FROM oceny GROUP BY uczen, przedmiot; +-------+------------+-----------+ | uczen | przedmiot | najgorsza | +-------+------------+-----------+ | 1 | biologia | 5.0 | | 1 | matematyka | 3.0 | | 1 | polski | 3.5 | | 2 | biologia | 3.0 | | 2 | matematyka | 2.0 | | 2 | polski | 4.5 | +-------+------------+-----------+ 66

Diagram związków:

Diagram związków

13.

Policz z ograniczeniem – w ilu nieruchomościach miesięczny czynsz jest wyższy od 350:

SELECT

Count(*)

liczba

FROM

nieruchomosc

WHERE

czynsz>400; mysql> SELECT Count(*) AS liczba -> FROM nieruchomosc -> WHERE czynsz>400; +--------+ | liczba | +--------+ | 6 | +--------+ 1 row in set (0.00 sec) 69

Tabela nieruchomosc : mysql> SELECT nieruchomoscNr, ulica, miasto, typ, czynsz FROM nieruchomosc; +----------------+-------------+-----------+------------+--------+ | nieruchomoscNr | ulica | miasto | typ | czynsz | +----------------+-------------+-----------+------------+--------+ | A14 | Handlowa 16 | Augustów | dom | 715 | | B16 | Nowa 5 | Białystok | mieszkanie | 495 | | B17 | Mała 2 | Białystok | mieszkanie | 412 | | B18 | Leśna 6 | Białystok | mieszkanie | 385 | | B21 | Dobra 18 | Białystok | dom | 660 | | G01 | Długa 33 | Grajewo | dom | 830 | | L94 | Akacjowa 6 | Łomża | mieszkanie | 440 | +----------------+-------------+-----------+------------+--------+ 7 rows in set (0.00 sec) 70

14.

Policz z ograniczeniem daty - ile nieruchomości odwiedzono w maju 2010:

SELECT

Count(nieruchomoscNr)

liczba

FROM

wizyta

WHERE

Month(data_wizyty)=5

AND

Year(data_wizyty)=2010; mysql> SELECT Count(nieruchomoscNr) AS liczba FROM wizyta -> WHERE Month(data_wizyty)=5 AND Year(data_wizyty)=2010; +--------+ | liczba | +--------+ | 3 | +--------+ 1 row in set (0.70 sec) 71

15.

Policz i sumuj – oblicz ilu jest dyrektorów i jaka jest ich sumaryczna pensja:

SELECT COUNT

(personelNr)

AS SUM

(pensja)

suma liczba,

FROM WHERE

personel stanowisko='dyrektor'; mysql> SELECT COUNT(personelNr) AS liczba, SUM(pensja) AS suma -> FROM personel -> WHERE stanowisko='dyrektor'; +--------+-------+ | liczba | suma | +--------+-------+ | 4 | 10100 | +--------+-------+ 1 row in set (0.00 sec) 73

16.

Minimum, maksimum i średnia – policz parametry pensji wszystkich pracowników:

SELECT MIN AVG

(pensja) AS srednia

FROM

(pensja) personel;

min,

MAX

(pensja)

max, mysql> SELECT MIN(pensja) AS min, MAX(pensja) AS max, -> AVG(pensja) AS srednia -> FROM personel; +------+------+-----------+ | min | max | srednia | +------+------+-----------+ | 900 | 3000 | 1517.6471 | +------+------+-----------+ 1 row in set (0.04 sec) 74

Grupowanie wyniku – klauzula GROUP BY

Zapytanie grupujące

klauzulę

GROUP BY

– zapytanie

SELECT

zawierające – w trakcie jego obliczania dane z tabeli

SELECT

są dzielone na grupy i dla każdej z grup jest generowany jeden wiersz podsumowania.

Kolumny grupowania GROUP BY

– kolumny wymienione w klauzuli 75

17.

Policz z grupowaniem – ile jest nieruchomości w każdym mieście:

SELECT FROM

miasto,

COUNT

nieruchomosc (nieruchomoscNr)

AS GROUP BY

18.

Policz z grupowaniem i sumowaniem - dla każdego biura liczbę pracowników i ich sumaryczną pensję:

SELECT

biuroNr,

SUM

(pensja)

AS COUNT

suma (personelNr)

FROM

personel

GROUP BY

biuroNr

ORDER BY

biuroNr;

liczba, mysql> SELECT biuroNr, COUNT(personelNr) AS liczba, -> SUM(pensja) AS suma FROM personel -> GROUP BY biuroNr ORDER BY biuroNr; +---------+--------+------+ | biuroNr | liczba | suma | +---------+--------+------+ | B002 | 3 | 3600 | | B003 | 4 | 6400 | | B004 | 2 | 3500 | | B005 | 3 | 4900 | | B006 | 3 | 4800 | | B007 | 2 | 2600 | +---------+--------+------+ 6 rows in set (0.39 sec) 77

Wybór grup – klauzula HAVING 19.

Policz z grupowaniem i wyborem grupy - dla każdego biura zatrudniającego więcej niż jednego (dwóch) pracowników, podaj ich liczbę oraz sumę ich zarobków:

SELECT

biuroNr,

COUNT

(personelNr)

liczba,

SUM

(pensja)

suma

FROM

personel

GROUP BY

biuroNr

HAVING COUNT

(personelNr)>1

ORDER BY

biuroNr; 78

mysql> SELECT biuroNr, COUNT(personelNr) AS liczba, -> SUM(pensja) AS suma -> FROM personel -> GROUP BY biuroNr -> HAVING COUNT(personelNr)>

-> ORDER BY biuroNr; +---------+--------+------+ | biuroNr | liczba | suma | +---------+--------+------+ | B002 | 3 | 3600 | | B003 | 4 | 6400 | | B004 | 2 | 3500 | | B005 | 3 | 4900 | | B006 | 3 | 4800 | | B007 | 2 | 2600 | +---------+--------+------+ 6 rows in set (0.00 sec) 79

mysql> SELECT biuroNr, COUNT(personelNr) AS liczba, -> SUM(pensja) AS suma -> FROM personel -> GROUP BY biuroNr -> HAVING COUNT(personelNr)>

-> ORDER BY biuroNr; +---------+--------+------+ | biuroNr | liczba | suma | +---------+--------+------+ | B002 | 3 | 3600 | | B003 | 4 | 6400 | | B005 | 3 | 4900 | | B006 | 3 | 4800 | +---------+--------+------+ 4 rows in set (0.00 sec) 80

MySQL-Integralność, zapytania

Transcript MySQL-Integralność, zapytania

Kontrola integralności danych:

1. Dane wymagane:

2. Więzy dziedzinowe:

3. Integralność encji:

4. Integralność referencyjna:

Powiązania kluczy obcych:

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

Aktualizacja obiektów w tabeli nadrzędnej:

Usuwanie obiektów z tabeli nadrzędnej:

5. Więzy ogólne:

Przykład:

Instrukcja SELECT :

Proste przykłady:

Komentarze w liniach komend:

Składnia:

Wyszukiwanie wszystkich wierszy:

Aliasy nazw kolumn i wyrażeń:

Wybieranie wierszy:

Zastosowanie funkcji agregujących:

Diagram związków:

Diagram związków

Grupowanie wyniku – klauzula GROUP BY

Directory