Transcript MySQL-Złączenia, perspektywy

POWTÓRZENIE
• Kontrola integralności danych;
1. dane wymagane;
2. więzy dziedzinowe;
3. integralność encji;
4. integralność referencyjna (związków);
5. więzy ogólne.
• Zapytania SELECT;
• Funkcje agregujące;
• Przykłady.
1
Podzapytania
- zapytania SELECT umieszczone w innym
zapytaniu SELECT
2. Podzapytanie z równością – podaj wszystkich pracowników
zatrudnionych w biurze przy ulicy Dobrej 22:
SELECT personelNr, imie, nazwisko, stanowisko
FROM personel
WHERE biuroNr=(SELECT biuroNr
FROM biuro
WHERE ulica = 'Dobra 22');
2
mysql> SELECT personelNr, imie, nazwisko, stanowisko
-> FROM personel
-> WHERE biuroNr=(SELECT biuroNr
-> FROM biuro WHERE ulica = 'Dobra 22');
+------------+--------+------------+------------+
| personelNr | imie
| nazwisko
| stanowisko |
+------------+--------+------------+------------+
| SL30
| Jan
| Wiśniewski | dyrektor
|
| SL31
| Julia | Lisicka
| asystent
|
| SL32
| Michał | Brzęczyk
| asystent
|
+------------+--------+------------+------------+
3 rows in set (0.01 sec)
3
3. Podzapytanie z funkcją agregującą – podaj wszystkich
pracowników, których pensja jest wyższa od średniej; pokaż
różnice między poszczególnymi pensjami a średnią:
SELECT personelNr, imie, nazwisko, stanowisko,
pensja, pensja-(SELECT AVG(pensja)FROM
personel) AS roznica
FROM personel
WHERE pensja>(SELECT AVG(pensja) FROM
personel)
ORDER BY pensja;
4
mysql> SELECT personelNr, imie, nazwisko, stanowisko, pensja,
-> pensja-(SELECT AVG(pensja)FROM personel) AS roznica
-> FROM personel
-> WHERE pensja>(SELECT AVG(pensja) FROM personel)
-> ORDER BY pensja;
+------------+-----------+------------+------------+--------+-----------+
| personelNr | imie
| nazwisko
| stanowisko | pensja | roznica
|
+------------+-----------+------------+------------+--------+-----------+
| SA8
| Katarzyna | Morawska
| kierownik |
1700 | 182.3529 |
| SB21
| Daniel
| Frankowski | kierownik |
1800 | 282.3529 |
| SG20
| Karolina | Mucha
| dyrektor
|
2200 | 682.3529 |
| SB20
| Sabina
| Bober
| dyrektor
|
2400 | 882.3529 |
| SB30
| Katarzyna | Michalska | dyrektor
|
2500 | 982.3529 |
| SL30
| Jan
| Wiśniewski | dyrektor
|
3000 | 1482.3529 |
+------------+-----------+------------+------------+--------+-----------+
6 rows in set (0.06 sec)
mysql> SELECT AVG(pensja) FROM personel;
+-------------+
| AVG(pensja) |
+-------------+
|
1517.6471 |
+-------------+
1 row in set (0.00 sec)
5
4a. Podzapytania zagnieżdżone - powtórzenia właścicieli
nieruchomości:
SELECT wlascicielNr, nieruchomoscNr,
miasto, ulica
FROM nieruchomosc
WHERE wlascicielNr IN (SELECT wlascicielNr
FROM nieruchomosc GROUP BY wlascicielNr
HAVING Count(*)>1 )
ORDER BY wlascicielNr;
6
mysql> SELECT wlascicielNr, nieruchomoscNr, miasto, ulica
-> FROM nieruchomosc
-> WHERE wlascicielNr IN (SELECT wlascicielNr FROM
nieruchomosc
-> GROUP BY wlascicielNr HAVING Count(*)>1 )
-> ORDER BY wlascicielNr;
+--------------+----------------+-----------+------------+
| wlascicielNr | nieruchomoscNr | miasto
| ulica
|
+--------------+----------------+-----------+------------+
| CO87
| B21
| Białystok | Dobra 18
|
| CO87
| L94
| Łomża
| Akacjowa 6 |
| CO93
| B16
| Białystok | Nowa 5
|
| CO93
| B17
| Białystok | Mała 2
|
+--------------+----------------+-----------+------------+
4 rows in set (0.06 sec)
7
4b. Podzapytania zagnieżdżone podwójnie - podaj wszystkie
nieruchomości nadzorowane przez pracowników zatrudnionych w
biurze przy ulicy Małej 63:
SELECT nieruchomoscNr, miasto, ulica
FROM nieruchomosc
WHERE personelNr IN (SELECT personelNr FROM
personel WHERE biuroNr = (SELECT biuroNr FROM
biuro WHERE ulica = 'Mała 63'));
8
mysql> SELECT nieruchomoscNr, miasto, ulica
-> FROM nieruchomosc
-> WHERE personelNr IN (SELECT personelNr FROM personel
-> WHERE biuroNr = (SELECT biuroNr FROM biuro WHERE
-> ulica = 'Mała 63'));
+----------------+-----------+----------+
| nieruchomoscNr | miasto
| ulica
|
+----------------+-----------+----------+
| B16
| Białystok | Nowa 5
|
| B17
| Białystok | Mała 2
|
| B18
| Białystok | Leśna 6 |
| B21
| Białystok | Dobra 18 |
+----------------+-----------+----------+
4 rows in set (0.01 sec)
9
5. Zastosowanie ANY / SOME - znajdź wszystkich pracowników,
którzy mają pensję wyższą niż przynajmniej jeden pracownik
biura o numerze B003
SELECT personelNr, imie, nazwisko,
stanowisko, pensja
FROM personel
WHERE pensja > ANY (SELECT pensja FROM
personel WHERE biuroNr='B003');
10
mysql> SELECT personelNr, imie, nazwisko, stanowisko, pensja
-> FROM personel
-> WHERE pensja > SOME (SELECT pensja FROM personel WHERE
biuroNr='B003');
+------------+------------+------------+------------+--------+
| personelNr | imie
| nazwisko
| stanowisko | pensja |
+------------+------------+------------+------------+--------+
| SA8
| Katarzyna | Morawska
| kierownik |
1700 |
| SB20
| Sabina
| Bober
| dyrektor
|
2400 |
| SB21
| Daniel
| Frankowski | kierownik |
1800 |
| SB23
| Anna
| Biały
| asystent
|
1200 |
| SB30
| Katarzyna | Michalska | dyrektor
|
2500 |
| SB31
| Dawid
| Piotrowski | asystent
|
1100 |
| SB32
| Małgorzata | Plichta
| asystent
|
1200 |
| SG20
| Karolina
| Mucha
| dyrektor
|
2200 |
| SG21
| Piotr
| Cybulski
| asystent
|
1300 |
| SL20
| Paweł
| Nowak
| kierownik |
1500 |
| SL22
| Monika
| Munk
| asystent
|
1100 |
| SL30
| Jan
| Wiśniewski | dyrektor
|
3000 |
+------------+------------+------------+------------+--------+
12 rows in set (0.00 sec)
11
6. zastosowanie ALL - znajdź wszystkich pracowników, którzy
mają pensję wyższą niż pensja każdego z pracowników biura o
numerze B003
SELECT personelNr, imie, nazwisko,
stanowisko, pensja
FROM personel
WHERE pensja > ALL (SELECT pensja FROM
personel WHERE biuroNr='B003');
12
mysql> SELECT personelNr, imie, nazwisko, stanowisko, pensja
-> FROM personel
-> WHERE pensja > ALL (SELECT pensja FROM personel WHERE
biuroNr='B003');
+------------+-----------+------------+------------+--------+
| personelNr | imie
| nazwisko
| stanowisko | pensja |
+------------+-----------+------------+------------+--------+
| SB30
| Katarzyna | Michalska | dyrektor
|
2500 |
| SL30
| Jan
| Wiśniewski | dyrektor
|
3000 |
+------------+-----------+------------+------------+--------+
2 rows in set (0.04 sec)
13
Pracownicy biura B003 i ich zarobki:
mysql> SELECT personelNr, imie, nazwisko, pensja
-> FROM personel WHERE biuroNr = 'B003'
-> ORDER BY pensja DESC;
+------------+------------+------------+--------+
| personelNr | imie
| nazwisko
| pensja |
+------------+------------+------------+--------+
| SB20
| Sabina
| Bober
|
2400 |
| SB21
| Daniel
| Frankowski |
1800 |
| SB23
| Anna
| Biały
|
1200 |
| SB22
| Małgorzata | Kowalska
|
1000 |
+------------+------------+------------+--------+
4 rows in set (0.00 sec)
14
Wszyscy pracownicy biur i ich zarobki:
mysql> SELECT personelNr, imie, nazwisko, pensja, biuroNr
-> FROM personel ORDER BY pensja DESC;
+------------+------------+------------+--------+---------+
| personelNr | imie
| nazwisko
| pensja | biuroNr |
+------------+------------+------------+--------+---------+
| SL30
| Jan
| Wiśniewski |
3000 | B005
|
| SB30
| Katarzyna | Michalska |
2500 | B006
|
| SB20
| Sabina
| Bober
|
2400 | B003
|
| SG20
| Karolina
| Mucha
|
2200 | B004
|
| SB21
| Daniel
| Frankowski |
1800 | B003
|
| SA8
| Katarzyna | Morawska
|
1700 | B007
|
| SL20
| Paweł
| Nowak
|
1500 | B002
|
| SG21
| Piotr
| Cybulski
|
1300 | B004
|
| SB32
| Małgorzata | Plichta
|
1200 | B006
|
| SB23
| Anna
| Biały
|
1200 | B003
|
| SB31
| Dawid
| Piotrowski |
1100 | B006
|
| SL22
| Monika
| Munk
|
1100 | B002
|
| SB22
| Małgorzata | Kowalska
|
1000 | B003
|
| SL21
| Paweł
| Kowalski
|
1000 | B002
|
| SL32
| Michał
| Brzęczyk
|
1000 | B005
|
| SA9
| Maria
| Hojna
|
900 | B007
|
| SL31
| Julia
| Lisicka
|
900 | B005
|
+------------+------------+------------+--------+---------+
17 rows in set (0.00 sec)
15
Zapytania dotyczące wielu
tabel
16
Proste złączenie dwóch tabel
SELECT k.klientNr, imie, nazwisko,
nieruchomoscNr, uwagi
FROM klient k, wizyta w
WHERE k.klientNr = w.klientNr;
mysql> SELECT k.klientNr, imie, nazwisko, nieruchomoscNr, uwagi
-> FROM klient k, wizyta w
-> WHERE k.klientNr = w.klientNr;
+----------+--------+------------+----------------+--------------+
| klientNr | imie
| nazwisko
| nieruchomoscNr | uwagi
|
+----------+--------+------------+----------------+--------------+
| CR51
| Michał | Rafalski
| A14
| za mały
|
| CR51
| Michał | Rafalski
| B4
| brak jadalni |
| CR52
| Ludwik | Wierzba
| A14
| brak jadalni |
| CR52
| Ludwik | Wierzba
| B36
| NULL
|
| CR52
| Ludwik | Wierzba
| B4
| NULL
|
| CR53
| Janusz | Kalinowski | B4
| za daleko
|
+----------+--------+------------+----------------+--------------+
6 rows in set (0.03 sec)
Dla każdej z tabel wymienionych w klauzuli FROM można zdefiniować nazwę
zastępczą - alias.
17
Złączenia tabel:
Instrukcja SELECT o postaci:
SELECT wyrażenie1, wyrażenie2, ...
FROM tabela1, tabela2, ...
zwraca tabelę o kolumnach zawierających wartości podanych
wyrażeń, obliczone dla iloczynu kartezjańskiego (pełnego
złączenia) podanych tabel.
18
Iloczyn kartezjański
SELECT k.klientNr, imie, nazwisko,
nieruchomoscNr, uwagi
FROM klient k, wizyta w;
mysql> SELECT k.klientNr, imie, nazwisko, nieruchomoscNr, uwagi
-> FROM klient k, wizyta w;
+----------+-----------+-------------+----------------+--------------+
| klientNr | imie
| nazwisko
| nieruchomoscNr | uwagi
|
+----------+-----------+-------------+----------------+--------------+
| CO16
| Alicja
| Stefańska
| A14
| za mały
|
| CO16
| Alicja
| Stefańska
| B4
| brak jadalni |
| CO16
| Alicja
| Stefańska
| A14
| brak jadalni |
| CO16
| Alicja
| Stefańska
| B36
| NULL
|
| CO16
| Alicja
| Stefańska
| B4
| NULL
|
| CO16
| Alicja
| Stefańska
| B4
| za daleko
|
| CO17
| Katarzyna | Winiarska
| A14
| za mały
|
| CO17
| Katarzyna | Winiarska
| B4
| brak jadalni |
| CO17
| Katarzyna | Winiarska
| A14
| brak jadalni |
| CO17
| Katarzyna | Winiarska
| B36
| NULL
|
| CO17
| Katarzyna | Winiarska
| B4
| NULL
|
| CO17
| Katarzyna | Winiarska
| B4
| za daleko
|
| CO18
| Anna
| Nowak
| A14
| za mały
|
| CO18
| Anna
| Nowak
| B4
| brak jadalni |
| CO18
| Anna
| Nowak
| A14
| brak jadalni |
...
19
| CO18
| Anna
| Nowak
| B36
| NULL
|
| CO18
| Anna
| Nowak
| B4
| NULL
|
| CO18
| Anna
| Nowak
| B4
| za daleko
|
| CR51
| Michał
| Rafalski
| A14
| za mały
|
| CR51
| Michał
| Rafalski
| B4
| brak jadalni |
| CR51
| Michał
| Rafalski
| A14
| brak jadalni |
| CR51
| Michał
| Rafalski
| B36
| NULL
|
| CR51
| Michał
| Rafalski
| B4
| NULL
|
| CR51
| Michał
| Rafalski
| B4
| za daleko
|
| CR52
| Ludwik
| Wierzba
| A14
| za mały
|
| CR52
| Ludwik
| Wierzba
| B4
| brak jadalni |
| CR52
| Ludwik
| Wierzba
| A14
| brak jadalni |
| CR52
| Ludwik
| Wierzba
| B36
| NULL
|
| CR52
| Ludwik
| Wierzba
| B4
| NULL
|
| CR52
| Ludwik
| Wierzba
| B4
| za daleko
|
| CR53
| Janusz
| Kalinowski | A14
| za mały
|
| CR53
| Janusz
| Kalinowski | B4
| brak jadalni |
| CR53
| Janusz
| Kalinowski | A14
| brak jadalni |
| CR53
| Janusz
| Kalinowski | B36
| NULL
|
| CR53
| Janusz
| Kalinowski | B4
| NULL
|
| CR53
| Janusz
| Kalinowski | B4
| za daleko
|
| CR54
| Maria
| Tomaszewska | A14
| za mały
|
| CR54
| Maria
| Tomaszewska | B4
| brak jadalni |
| CR54
| Maria
| Tomaszewska | A14
| brak jadalni |
| CR54
| Maria
| Tomaszewska | B36
| NULL
|
| CR54
| Maria
| Tomaszewska | B4
| NULL
|
| CR54
| Maria
| Tomaszewska | B4
| za daleko
|
+----------+-----------+-------------+----------------+--------------+
42 rows in set (0.00 sec)
20
Najczęściej bardziej przydatne są zapytania wykorzystujące
równozłączenia tabel, a nie iloczyny kartezjańskie. Dokonuje się
tego za pomocą odpowiednio dobranego warunku w klauzuli
WHERE:
SELECT wyrażenie1, wyrażenie2, ...
FROM tabela1, tabela2, ...
WHERE warunek
Notacje:
tabela1, tabela2
tabela1 JOIN tabela2
są równoważne.
Warunek w klauzuli WHERE powinien być wyrażeniem
logicznym dotyczącym kolumn złączanych tabel, dyktuje on
które wiersze iloczynu kartezjańskiego zostaną uwzględnione w
złączeniu.
21
Inny sposób złączenia tabel to złączenie zewnętrzne.
MySQL implementuje dwie postacie zapisu lewostronnego
złączenia zewnętrznego:
tabela1 LEFT [OUTER] JOIN tabela2
ON warunek
gdzie warunek po słowie kluczowym ON może być dowolnym
wyrażeniem logicznym zbudowanym z wykorzystaniem nazw
kolumn złączanych tabel.
Oraz druga postać:
tabela1 LEFT [OUTER] JOIN tabela2
USING (kolumna1, kolumna2, ...)
22
Lista nazw kolumn po słowie kluczowym USING musi zawierać
kolumny występujące w obu złączanych tabelach pod tymi
samymi nazwami. Inaczej mówiąc, notacja powyższa jest
równoważna zapisowi
tabela1 LEFT [OUTER] JOIN tabela2 ON
tabela1.kolumna1=tabela2.kolumna1 AND
tabela1.kolumna2=tabela2.kolumna2 ...
Słowo kluczowe OUTER jest opcjonalne i nie ma wpływu na
efekt złączenia.
23
Definicja (lewostronnego) złączenia zewnętrznego oznacza, że
jeżeli w ,,prawej'' tabeli brak wiersza ,,pasującego'' do pewnego
wiersza tabeli ,,lewej'' (tzn. spełniającego warunek złączenia),
to stworzony zostanie wiersz zawierający wartość NULL we
wszystkich kolumnach pochodzących z tabeli ,,prawej''.
Dodatkowo istnieje jeszcze operacja naturalnego złączenia
lewostronnego:
tabela1 NATURAL LEFT [OUTER] JOIN tabela2
będąca po prostu skrótowym zapisem złączenia z klauzulą
USING zawierającą jako argument listę nazw wszystkich
kolumn powtarzających się w obu złączanych tabelach.
24
Złączenia
Operacja złączenia kojarzy dane z dwóch tabel, tworząc pary z
wierszy, w których kolumny złączenia mają taką samą wartość.
Dla złączenia wewnętrznego – jeżeli wiersz tabeli nie zostanie
połączony w parę (z żadnym wierszem z drugiej tabeli), to nie
trafia do tabeli wynikowej.
Złączenie zewnętrzne zachowuje wiersze niespełniające warunku
złączenia.
25
7. Złączenie wewnętrzne
- podaj biura i nieruchomości znajdujące się w tym samym mieście:
mysql> SELECT * FROM biuro2;
+---------+-----------+
| biuronr | miasto
|
+---------+-----------+
|
B002 | Łomża
|
|
B003 | Białystok |
|
B004 | Grajewo
|
+---------+-----------+
mysql> SELECT * FROM nieruchomosc2;
+---------------+-----------+
| nieruchomoscr | miasto
|
+---------------+-----------+
|
A14
| Augustów |
|
B4
| Białystok |
|
L94
| Łomża
|
+---------------+-----------+
SELECT Biuro2.*, Nieruchomosc2.*
FROM Biuro1 INNER JOIN Nieruchomosc2 ON
Biuro2.miasto = Nieruchomosc2.miasto;
26
mysql> SELECT biuro2.*, nieruchomosc2.*
-> FROM biuro2 INNER JOIN nieruchomosc2 ON
biuro2.miasto = nieruchomosc2.miasto;
+---------+------------+----------------+------------+
| biuroNr | miasto
| nieruchomoscNr | miasto
|
+---------+------------+----------------+------------+
| B003
| Białystok | B4
| Białystok |
| B002
| Łomża
| L94
| Łomża
|
+---------+------------+----------------+------------+
2 rows in set (0.00 sec)
Polecenie równoważne:
SELECT biuro2.*, nieruchomosc2.*
FROM biuro2 INNER JOIN nieruchomosc2
USING(miasto);
27
Przykład na innych tabelach:
SELECT biuro.biuronr, biuro.miasto, personel.personelnr,
personel.nazwisko, personel.biuronr
FROM biuro INNER JOIN personel ON biuro.biuronr =
personel.biuronr;
biuronr
B002
B002
B002
B003
B003
B003
B003
B004
B004
B005
B005
B005
B006
B006
B006
B007
B007
miasto
Łomża
Łomża
Łomża
Białystok
Białystok
Białystok
Białystok
Grajewo
Grajewo
Łomża
Łomża
Łomża
Białystok
Białystok
Białystok
Augustów
Augustów
personelnr
SL20
SL21
SL22
SB20
SB21
SB22
SB23
SG20
SG21
SL30
SL31
SL32
SB30
SB31
SB32
SA8
SA9
nazwisko
Nowak
Kowalski
Munk
Bober
Frankowski
Kowalska
Biały
Mucha
Cybulski
Wiśniewski
Lisicka
Brzęczyk
Michalska
Piotrowski
Plichta
Morawska
Hojna
biuronr
B002
B002
B002
B003
B003
B003
B003
B004
B004
B005
B005
B005
B006
B006
B006
B007
B007
28
8. Lewostronne złączenie zewnętrzne:
- podaj biura i nieruchomości znajdujące się w tym samym mieście, umieść
również te biura, w których nie zarejestrowano żadnej nieruchomości:
SELECT Biuro2.*, Nieruchomosc2.*
FROM Biuro2 LEFT JOIN Nieruchomosc2 ON
Biuro2.miasto = Nieruchomosc2.miasto;
mysql> SELECT biuro2.*, nieruchomosc2.*
-> FROM biuro2 LEFT JOIN nieruchomosc2 ON
biuro2.miasto = nieruchomosc2.miasto;
+---------+------------+----------------+------------+
| biuroNr | miasto
| nieruchomoscNr | miasto
|
+---------+------------+----------------+------------+
| B002
| Łomża
| L94
| Łomża
|
| B003
| Białystok | B4
| Białystok |
| B004
| Grajewo
| NULL
| NULL
|
+---------+------------+----------------+------------+
3 rows in set (0.02 sec)
29
Z dodanymi warunkami:
SELECT biuro2.*, nieruchomosc2.*
FROM biuro2 LEFT JOIN nieruchomosc2 USING(miasto)
WHERE nieruchomosc2.miasto IS NULL;
mysql> SELECT biuro2.*, nieruchomosc2.*
-> FROM biuro2 LEFT JOIN nieruchomosc2 USING(miasto)
-> WHERE nieruchomosc2.miasto IS NULL;
+---------+---------+----------------+--------+
| biuroNr | miasto | nieruchomoscNr | miasto |
+---------+---------+----------------+--------+
| B004
| Grajewo | NULL
| NULL
|
+---------+---------+----------------+--------+
1 row in set (0.00 sec)
30
Przykład na innych tabelach:
SELECT biuro.biuronr, biuro.miasto, personel.personelnr,
personel.nazwisko, personel.biuronr
FROM biuro LEFT JOIN personel ON biuro.biuronr =
personel.biuronr;
biuronr
B001
B002
B002
B002
B003
B003
B003
B003
B004
B004
B005
B005
B005
B006
B006
B006
B007
B007
miasto
Białystok
Łomża
Łomża
Łomża
Białystok
Białystok
Białystok
Białystok
Grajewo
Grajewo
Łomża
Łomża
Łomża
Białystok
Białystok
Białystok
Augustów
Augustów
personelnr
NULL
SL20
SL21
SL22
SB20
SB21
SB22
SB23
SG20
SG21
SL30
SL31
SL32
SB30
SB31
SB32
SA8
SA9
nazwisko
NULL
Nowak
Kowalski
Munk
Bober
Frankowski
Kowalska
Biały
Mucha
Cybulski
Wiśniewski
Lisicka
Brzęczyk
Michalska
Piotrowski
Plichta
Morawska
Hojna
biuronr
NULL
B002
B002
B002
B003
B003
B003
B003
B004
B004
B005
B005
B005
B006
B006
B006
B007
B007
31
9. Prawostronne złączenie zewnętrzne:
- podaj biura i nieruchomości znajdujące się w tym samym mieście,
umieść również nieruchomości nie zarejestrowane w żadnym
biurze:
SELECT biuro2.*, nieruchomosc2.*
FROM biuro2 RIGHT JOIN nieruchomosc2 ON
biuro2.miasto = nieruchomosc2.miasto;
mysql> SELECT biuro2.*, nieruchomosc2.*
-> FROM biuro2 RIGHT JOIN nieruchomosc2 ON
biuro2.miasto = nieruchomosc2.miasto;
+---------+------------+----------------+------------+
| biuroNr | miasto
| nieruchomoscNr | miasto
|
+---------+------------+----------------+------------+
| NULL
| NULL
| A14
| Augustów
|
| B003
| Białystok | B4
| Białystok |
| B002
| Łomża
| L94
| Łomża
|
+---------+------------+----------------+------------+
3 rows in set (0.00 sec)
32
Z dodanymi warunkami:
SELECT biuro2.*, nieruchomosc2.*
FROM biuro2 LEFT JOIN nieruchomosc2 USING(miasto)
WHERE biuro2.miasto IS NULL;
mysql> SELECT biuro2.*, nieruchomosc2.*
-> FROM biuro2 RIGHT JOIN nieruchomosc2
USING(miasto)
-> WHERE biuro2.miasto IS NULL;
+---------+--------+----------------+-----------+
| biuroNr | miasto | nieruchomoscNr | miasto
|
+---------+--------+----------------+-----------+
| NULL
| NULL
| A14
| Augustów |
+---------+--------+----------------+-----------+
1 row in set (0.01 sec)
33
Przykład na innych tabelach:
SELECT biuro.biuronr, biuro.miasto, personel.personelnr,
personel.nazwisko, personel.biuronr
FROM biuro RIGHT JOIN personel ON biuro.biuronr =
personel.biuronr;
biuronr
B007
B007
B003
B003
B003
B003
B006
B006
B006
B004
B004
B002
B002
B002
B005
B005
B005
miasto
Augustów
Augustów
Białystok
Białystok
Białystok
Białystok
Białystok
Białystok
Białystok
Grajewo
Grajewo
Łomża
Łomża
Łomża
Łomża
Łomża
Łomża
personelnr nazwisko
SA8
Morawska
SA9
Hojna
SB20
Bober
SB21
Frankowski
SB22
Kowalska
SB23
Biały
SB30
Michalska
SB31
Piotrowski
SB32
Plichta
SG20
Mucha
SG21
Cybulski
SL20
Nowak
SL21
Kowalski
SL22
Munk
SL30
Wiśniewski
SL31
Lisicka
SL32
Brzęczyk
biuronr
B007
B007
B003
B003
B003
B003
B006
B006
B006
B004
B004
B002
B002
B002
B005
B005
B005
Wynik w tym przypadku identyczny jak dla INNER JOIN ze względu na istniejące więzy
34
między tabelami
Pełne złączenie zewnętrzne
- podaj biura i nieruchomości znajdujące się w tym samym mieście,
umieść również te biura, w których nie zarejestrowano żadnej
nieruchomości oraz nieruchomości nie zarejestrowane w żadnym
biurze:
SELECT Biuro2.*, Nieruchomosc2.*
FROM Biuro1 FULL JOIN Nieruchomosc2 ON
Biuro2.miasto = Nieruchomosc2.miasto;
35
Utworzenie pełnego złączenia zewnętrznego
(z wykorzystaniem UNION):
(SELECT biuro2.biuroNr, biuro2.miasto,
nieruchomosc2.nieruchomoscNr,
nieruchomosc2.miasto AS miasto2
FROM (biuro2 LEFT JOIN nieruchomosc2 ON
(biuro2.miasto = nieruchomosc2.miasto)))
UNION
(SELECT biuro2.biuroNr, biuro2.miasto,
nieruchomosc2.nieruchomoscNr,
nieruchomosc2.miasto AS miasto2
FROM (biuro2 RIGHT JOIN nieruchomosc2 ON
(biuro2.miasto = nieruchomosc2.miasto)));
36
mysql> (SELECT biuro2.biuroNr, biuro2.miasto,
nieruchomosc2.nieruchomoscNr,
-> nieruchomosc2.miasto AS miasto2
-> FROM (biuro2 LEFT JOIN nieruchomosc2 ON (biuro2.miasto
= nieruchomosc2.miasto)))
-> UNION
-> (SELECT biuro2.biuroNr, biuro2.miasto,
nieruchomosc2.nieruchomoscNr,
-> nieruchomosc2.miasto AS miasto2
-> FROM (biuro2 RIGHT JOIN nieruchomosc2 ON (biuro2.miasto
= nieruchomosc2.miasto)));
+---------+-----------+----------------+-----------+
| biuronr | miasto
| nieruchomoscnr | miasto2
|
+---------+-----------+----------------+-----------+
| B002
| Łomża
| L94
| Łomża
|
| B003
| Białystok | B4
| Białystok |
| B004
| Grajewo
| NULL
| NULL
|
| NULL
| NULL
| A14
| Augustów |
+---------+-----------+----------------+-----------+
4 rows in set (0.00 sec)
37
mysql> (SELECT biuro2.biuroNr, biuro2.miasto,
nieruchomosc2.nieruchomoscNr,
-> nieruchomosc2.miasto
-> FROM (biuro2 LEFT JOIN nieruchomosc2 ON (biuro2.miasto
= nieruchomosc2.miasto)))
-> UNION
-> (SELECT biuro2.biuroNr, biuro2.miasto,
nieruchomosc2.nieruchomoscNr,
-> nieruchomosc2.miasto
-> FROM (biuro2 RIGHT JOIN nieruchomosc2 ON (biuro2.miasto
= nieruchomosc2.miasto)));
+---------+-----------+----------------+-----------+
| biuroNr | miasto
| nieruchomoscNr | miasto
|
+---------+-----------+----------------+-----------+
| B002
| Łomża
| L94
| Łomża
|
| B003
| Białystok | B4
| Białystok |
| B004
| Grajewo
| NULL
| NULL
|
| NULL
| NULL
| A14
| Augustów |
+---------+-----------+----------------+-----------+
4 rows in set (0.00 sec)
38
UNION
mysql> (SELECT nieruchomoscnr, miasto, personelnr FROM nieruchomosc
WHERE biuronr='B003' AND personelnr='SB23')
-> UNION
-> (SELECT nieruchomoscnr, miasto, personelnr FROM nieruchomosc
WHERE biuronr='B005' AND personelnr='SL31')
-> ORDER BY nieruchomoscnr;
+----------------+-----------+------------+
| nieruchomoscnr | miasto
| personelnr |
+----------------+-----------+------------+
| B17
| Białystok | SB23
|
| B18
| Białystok | SB23
|
| L94
| Łomża
| SL31
|
+----------------+-----------+------------+
3 rows in set (0.11 sec)
39
LIMIT
mysql> (SELECT nieruchomoscNr, miasto, personelNr FROM nieruchomosc
WHERE biuroNr='B003' AND personelNr='SB23')
-> UNION
-> (SELECT nieruchomoscNr, miasto, personelNr FROM nieruchomosc
WHERE biuroNr='B005' AND personelNr='SL31')
-> ORDER BY nieruchomoscnr
-> LIMIT 2;
+----------------+-----------+------------+
| nieruchomoscnr | miasto
| personelnr |
+----------------+-----------+------------+
| B17
| Białystok | SB23
|
| B18
| Białystok | SB23
|
+----------------+-----------+------------+
2 rows in set (0.09 sec)
40
LIMIT
mysql> SELECT personelNr, imie, nazwisko, pensja, biuroNr
-> FROM personel
-> LIMIT 8;
+------------+------------+------------+--------+---------+
| personelNr | imie
| nazwisko
| pensja | biuroNr |
+------------+------------+------------+--------+---------+
| SA8
| Katarzyna | Morawska
|
1700 | B007
|
| SA9
| Maria
| Hojna
|
900 | B007
|
| SB20
| Sabina
| Bober
|
2400 | B003
|
| SB21
| Daniel
| Frankowski |
1800 | B003
|
| SB22
| Małgorzata | Kowalska
|
1000 | B003
|
| SB23
| Anna
| Biały
|
1200 | B003
|
| SB30
| Katarzyna | Michalska |
2500 | B006
|
| SB31
| Dawid
| Piotrowski |
1100 | B006
|
+------------+------------+------------+--------+---------+
8 rows in set (0.00 sec)
41
Widoki (views) - Perspektywy:
Perspektywa – dynamicznie obliczany wynik jednej lub wielu
operacji relacyjnych tworzących nową relację (tabelę) z relacji
bazowych.
Perspektywa jest relacją wirtualną, która nie musi fizycznie istnieć
w bazie danych, ale może być wyliczona w każdej chwili na
żądanie użytkownika.
Składnia polecenia:
CREATE [OR REPLACE] VIEW nazwa_perspektywy
[(lista_kolumn)] AS SELECT zapytanie
[WITH [CASCADED | LOCAL] CHECK OPTION]
42
Jeżeli w poleceniu pominięta jest lista kolumn, to każda z kolumn
perspektywy będzie miała taką samą nazwę, jak odpowiednia
kolumna w wyniku zapytania SELECT.
Lista kolumn powinna być podana, jeśli istnieją jakiekolwiek
niejasności dotyczące nazw kolumn (np. dla kolumn wyliczanych
lub, gdy w wyniku złączenia powstają dwie kolumny o
identycznych nazwach).
43
Usuwanie perspektywy:
DROP VIEW [IF EXISTS]
nazwa_perspektywy [, nazwa_perspektywy] ...
[RESTRICT | CASCADE]
Modyfikowanie perspektywy:
ALTER VIEW nazwa_perspektywy [(lista_kolumn)]
AS SELECT zapytanie
[WITH [CASCADED | LOCAL] CHECK OPTION]
44
Rodzaje perspektyw:
Utworzenie perspektywy poziomej:
mysql> CREATE VIEW _11_biuro_B003 AS SELECT personelNr, imie,
nazwisko, stanowisko, pensja
-> FROM personel WHERE biuronr='B003';
-> SELECT * FROM _11_biuro_B003;
Query OK, 0 rows affected (0.02 sec)
+------------+-------------+------------+------------+--------+
| personelNr | imie
| nazwisko
| stanowisko | pensja |
+------------+-------------+------------+------------+--------+
| SB20
| Sabina
| Bober
| dyrektor
|
2400 |
| SB21
| Daniel
| Frankowski | kierownik |
1800 |
| SB22
| Małgorzata | Kowalska
| asystent
|
1000 |
| SB23
| Anna
| Biały
| asystent
|
1200 |
+------------+-------------+------------+------------+--------+
4 rows in set (0.01 sec)
45
Utworzenie perspektywy pionowej:
mysql> CREATE VIEW _10_dyrektorzy AS SELECT personelnr, pensja
-> FROM personel WHERE stanowisko='dyrektor';
-> SELECT * FROM _10_dyrektorzy;
Query OK, 0 rows affected (0.03 sec)
+------------+--------+
| personelnr | pensja |
+------------+--------+
| SB20
|
2400 |
| SB30
|
2500 |
| SG20
|
2200 |
| SL30
|
3000 |
+------------+--------+
4 rows in set (0.02 sec)
46
Perspektywy oparte na grupowaniu:
mysql> CREATE VIEW srednie AS SELECT uczen, przedmiot,
-> AVG(ocena) AS srednia FROM oceny
-> GROUP BY uczen, przedmiot;
-> SELECT* FROM srednie;
+-------+------------+---------+
| uczen | przedmiot | srednia |
+-------+------------+---------+
|
1 | biologia
| 5.00000 |
|
1 | matematyka | 3.83333 |
|
1 | polski
| 4.00000 |
|
2 | biologia
| 3.25000 |
|
2 | matematyka | 3.12500 |
|
2 | polski
| 4.66667 |
+-------+------------+---------+
47
Podaj nieruchomości, które były wynajmowane częściej
niż średnio:
mysql> CREATE VIEW tmp AS SELECT count(nieruchomoscNr) AS ile
FROM wynajecie w GROUP BY nieruchomoscNr;
Query OK, 0 rows affected (0.04 sec)
mysql> SELECT * FROM tmp;
+-----+
| ile |
+-----+
|
3 |
|
4 |
|
2 |
|
2 |
|
2 |
|
2 |
|
2 |
+-----+
7 rows in set (0.01 sec)
48
mysql> CREATE VIEW srednio AS SELECT n.nieruchomoscNr,
miasto, n.czynsz, count(*) FROM nieruchomosc n,
wynajecie w
-> WHERE w.nieruchomoscNr = n.nieruchomoscNr
-> GROUP BY nieruchomoscNr
-> HAVING count(*)>=(SELECT AVG(ile) FROM tmp);
Query OK, 0 rows affected (0.10 sec)
mysql> SELECT * FROM srednio;
+----------------+-----------+--------+----------+
| nieruchomoscNr | miasto
| czynsz | count(*) |
+----------------+-----------+--------+----------+
| A14
| Augustów |
715 |
3 |
| B16
| Białystok |
495 |
4 |
+----------------+-----------+--------+----------+
2 rows in set (0.01 sec)
49
SELECT AVG(ile) AS srednia FROM
(SELECT count(nieruchomoscNr) AS ile
FROM wynajecie w GROUP BY nieruchomoscNr) tmp;
mysql> SELECT AVG(ile) AS srednia FROM
-> (SELECT count(nieruchomoscNr) AS ile
-> FROM wynajecie w GROUP BY nieruchomoscNr) tmp;
+---------+
| srednia |
+---------+
| 2.2857 |
+---------+
1 row in set (0.00 sec)
50
Podaj nieruchomości, które były wynajmowane częściej
niż średnio:
SELECT n.nieruchomoscNr, miasto, n.czynsz, count(*)
FROM nieruchomosc n, wynajecie w
WHERE w.nieruchomoscNr = n.nieruchomoscNr
GROUP BY nieruchomoscNr
HAVING count(*)>=(SELECT AVG(ile) FROM
(SELECT count(nieruchomoscNr) AS ile FROM wynajecie
w GROUP BY nieruchomoscNr) tmp);
+----------------+-----------+--------+----------+
| nieruchomoscNr | miasto
| czynsz | count(*) |
+----------------+-----------+--------+----------+
| A14
| Augustów |
715 |
3 |
| B16
| Białystok |
495 |
4 |
+----------------+-----------+--------+----------+
51
bez WHERE
mysql> SELECT n.nieruchomoscNr, miasto, n.czynsz, count(*)
-> FROM nieruchomosc n, wynajecie w
-> GROUP BY nieruchomoscNr
-> HAVING count(*)>=(SELECT AVG(ile) FROM
-> (SELECT count(nieruchomoscNr) AS ile
-> FROM wynajecie w GROUP BY nieruchomoscNr) tmp);
+----------------+-----------+--------+----------+
| nieruchomoscNr | miasto
| czynsz | count(*) |
+----------------+-----------+--------+----------+
| A14
| Augustów |
715 |
17 |
| B16
| Białystok |
495 |
17 |
| B17
| Białystok |
412 |
17 |
| B18
| Białystok |
385 |
17 |
| B21
| Białystok |
660 |
17 |
| G01
| Grajewo
|
830 |
17 |
| L94
| Łomża
|
440 |
17 |
+----------------+-----------+--------+----------+
7 rows in set (0.01 sec)
Wszystkie 7 nieruchomości i wszystkie 17 wynajęć
52
Inne przykłady:
mysql> CREATE TABLE test (ilosc INT, cena INT);
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO test VALUES(3, 50);
Query OK, 1 row affected (0.00 sec)
mysql> CREATE VIEW w AS SELECT ilosc, cena, ilosc*cena AS wartosc
FROM test;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM w;
+-------+------+---------+
| ilosc | cena | wartosc |
+-------+------+---------+
|
3 |
50 |
150 |
+-------+------+---------+
1 row in set (0.00 sec)
53
mysql> CREATE VIEW roczna AS SELECT personelNr, stanowisko,
pensja, pensja*12 AS pensja_roczna FROM personel;
Query OK, 0 rows affected (0.02 sec)
mysql> SELECT * FROM roczna;
+------------+------------+--------+---------------+
| personelNr | stanowisko | pensja | pensja_roczna |
+------------+------------+--------+---------------+
| SA8
| kierownik |
1700 |
20400 |
| SA9
| asystent
|
900 |
10800 |
| SB20
| dyrektor
|
2400 |
28800 |
| SB21
| kierownik |
1800 |
21600 |
| SB22
| asystent
|
1000 |
12000 |
| SB23
| asystent
|
1200 |
14400 |
| SB30
| dyrektor
|
2500 |
30000 |
| SB31
| asystent
|
1100 |
13200 |
| SB32
| asystent
|
1200 |
14400 |
| SG20
| dyrektor
|
2200 |
26400 |
| SG21
| asystent
|
1300 |
15600 |
| SL20
| kierownik |
1500 |
18000 |
| SL21
| asystent
|
1000 |
12000 |
| SL22
| asystent
|
1100 |
13200 |
| SL30
| dyrektor
|
3000 |
36000 |
| SL31
| asystent
|
900 |
10800 |
| SL32
| asystent
|
1000 |
12000 |
+------------+------------+--------+---------------+
17 rows in set (0.01 sec)
54
mysql> CREATE TABLE t1 (a INT);
Query OK, 0 rows affected (0.19 sec)
mysql> CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 2
-> WITH CHECK OPTION;
Query OK, 0 rows affected (0.08 sec)
mysql> CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 0
-> WITH LOCAL CHECK OPTION;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE VIEW v3 AS SELECT * FROM v1 WHERE a > 0
-> WITH CASCADED CHECK OPTION;
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO v2 VALUES (2);
Query OK, 1 row affected (0.06 sec)
mysql> select * from t1;
+------+
| a
|
+------+
|
2 |
+------+
1 row in set (0.00 sec)
55
mysql> select * from t1;
+------+
| a
|
+------+
|
2 |
+------+
1 row in set (0.00 sec)
mysql> INSERT INTO v1 VALUES (2);
ERROR 1369 (HY000): CHECK OPTION failed 'biuro.v1'
mysql> INSERT INTO v3 VALUES (2);
ERROR 1369 (HY000): CHECK OPTION failed 'biuro.v3'
mysql> INSERT INTO v1 VALUES (1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+
| a
|
+------+
|
2 |
|
1 |
+------+
2 rows in set (0.01 sec)
56
mysql> CREATE VIEW _10_dyrektorzy2 AS SELECT
personelnr,nazwisko,imie,stanowisko, pensja,biuroNr FROM personel WHERE
stanowisko='dyrektor' AND pensja >= 2500 WITH CHECK OPTION; SELECT * FROM
_10_dyrektorzy2;
Query OK, 0 rows affected (0.01 sec)
+------------+------------+-----------+------------+--------+---------+
| personelnr | nazwisko
| imie
| stanowisko | pensja | biuroNr |
+------------+------------+-----------+------------+--------+---------+
| SB30
| Michalska | Katarzyna | dyrektor
|
2500 | B006
|
| SL30
| Wiśniewski | Jan
| dyrektor
|
3000 | B005
|
+------------+------------+-----------+------------+--------+---------+
2 rows in set (0.00 sec)
mysql> INSERT INTO _10_dyrektorzy2 (personelNr, nazwisko, imie, stanowisko,
pensja, biuroNr) VALUES ('SA1 ','Biedak', 'Jan', 'dyrektor', 2000, 'B007 ');
ERROR 1369 (HY000): CHECK OPTION failed 'gilg._10_dyrektorzy2'
mysql> INSERT INTO _10_dyrektorzy2 (personelNr,nazwisko,imie,stanowisko,pensja,
biuroNr) VALUES ('SC1 ','Bogacz', 'Jan', 'dyrektor', 5000, 'B001 ');
Query OK, 1 row affected, 2 warnings (0.09 sec)
mysql> SELECT * FROM _10_dyrektorzy2;
+------------+------------+-----------+------------+--------+---------+
| personelnr | nazwisko
| imie
| stanowisko | pensja | biuroNr |
+------------+------------+-----------+------------+--------+---------+
| SB30
| Michalska | Katarzyna | dyrektor
|
2500 | B006
|
| SC1
| Bogacz
| Jan
| dyrektor
|
5000 | B001
|
| SL30
| Wiśniewski | Jan
| dyrektor
|
3000 | B005
|
+------------+------------+-----------+------------+--------+---------+
3 rows in set (0.00 sec)
57