Operowanie na danych INSERT dopisywanie danych do tabeli

Operowanie na danych: INSERT – dopisywanie danych do tabeli; UPDATE – modyfikowanie danych w tabelach; DELETE – usuwanie danych z tabel; SELECT – wyszukiwanie danych w bazie. 1

Instrukcja SELECT: • podstawowa instrukcja języka SQL, służącą głównie (choć nie tylko) do pobierania danych z tabeli lub tabel na podstawie zadanych warunków. • kolejność klauzul (odpowiednich słów kluczowych) jest istotna. • wynikiem jej wywołania (o ile nie wystąpi błąd) jest zawsze pewna tabela. 2

Wyniki z różnych postaci: 1. SELECT wyrażenie zwróci wartość podanego wyrażenia (zbudowanego z wykorzystaniem stałych i funkcji) - czyli tabelę składającą się z jednego wiersza i jednej kolumny. 2. SELECT wyrażenie 1, wyrażenie 2, . . . tabela o jednym wierszu i o kolumnach zawierających kolejno wartości podanych wyrażeń. 3. SELECT wyrażenie 1, wyrażenie 2, . . . FROM tabela dane będą pobierane z tabeli wymienionej po słowie kluczowym FROM. 3

Przykłady z jednym wyrażeniem: 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 | +-------+ 4

$Składnia polecenia SELECT: postać ogólna: SELECT [DISTINCT ALL] {* [wyrażenie_kolumnowe [AS nowa_nazwa]], […]} FROM$

Składnia polecenia SELECT: postać ogólna: SELECT [DISTINCT ALL] {* [wyrażenie_kolumnowe [AS nowa_nazwa]], […]} FROM Nazwa. Tabeli [alias], [. . . ] [WHERE warunek_selekcji_wierszy] [GROUP BY lista_kolumn] [HAVING warunek_selekcji_grup] [ORDER BY lista_kolumn]; - czerwone obowiązkowe [] w nawiasach kwadratowych opcjonalne {} w nawiasach klamrowych do wyboru jedno z kilku ; znak kończący zapis (zdania) 5

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

Wyszukiwanie wszystkich wierszy: 1. Wszystkie kolumny i wszystkie wiersze z tabeli personel: SELECT * FROM personel; * zastępuje nazwy wszystkich kolumn mysql> SELECT * FROM personel; +------------+------------+------------+---------+ | personel. Nr | imie | nazwisko | stanowisko | plec | data. Ur | pensja | biuro. Nr | +------------+------------+------------+---------+ | SA 8 | Katarzyna | Morawska | kierownik | K | 1971 -05 -06 | 1700 | B 007 | | SA 9 | Maria | Hojna | asystent | K | 1970 -02 -19 | 900 | B 007 | | SB 20 | Sabina | Bober | dyrektor | K | 1940 -06 -03 | 2400 | B 003 | | SB 21 | Daniel | Frankowski | kierownik | M | 1958 -03 -24 | 1800 | B 003 | | SB 22 | Małgorzata | Kowalska | asystent | K | 1972 -03 -15 | 1000 | B 003 | | SB 23 | Anna | Biały | asystent | K | 1960 -11 -10 | 1200 | B 003 | | SB 30 | Katarzyna | Michalska | dyrektor | K | 1960 -11 -17 | 2500 | B 006 | | SB 31 | Dawid | Piotrowski | asystent | M | 1975 -03 -22 | 1100 | B 006 | | SB 32 | Małgorzata | Plichta | asystent | K | 1971 -10 -03 | 1200 | B 006 | | SG 20 | Karolina | Mucha | dyrektor | K | 1953 -03 -03 | 2200 | B 004 | | SG 21 | Piotr | Cybulski | asystent | M | 1974 -12 -06 | 1300 | B 004 | | SL 20 | Paweł | Nowak | kierownik | M | 1962 -02 -02 | 1500 | B 002 | | SL 21 | Paweł | Kowalski | asystent | M | 1969 -05 -05 | 1000 | B 002 | | SL 22 | Monika | Munk | asystent | K | 1977 -07 -26 | 1100 | B 002 | | SL 30 | Jan | Wiśniewski | dyrektor | M | 1945 -10 -01 | 3000 | B 005 | | SL 31 | Julia | Lisicka | asystent | K | 1965 -07 -13 | 900 | B 005 | | SL 32 | Michał | Brzęczyk | asystent | M | 1959 -03 -15 | 1000 | B 005 | +------------+------------+------------+---------+ 17 rows in set (0. 00 sec) 7

2. Wybrane kolumny i wszystkie wiersze: SELECT personel. Nr, imie, nazwisko, pensja FROM personel; mysql> SELECT personel. Nr, imie, nazwisko, pensja -> FROM personel; +------------+------+----+ | personel. Nr | imie | nazwisko | pensja | +------------+------+----+ | SA 8 | Katarzyna | Morawska | 1700 | | SA 9 | Maria | Hojna | 900 | | SB 20 | Sabina | Bober | 2400 | | SB 21 | Daniel | Frankowski | 1800 | | SB 22 | Małgorzata | Kowalska | 1000 | | SB 23 | Anna | Biały | 1200 | | SB 30 | Katarzyna | Michalska | 2500 | | SB 31 | Dawid | Piotrowski | 1100 | | SB 32 | Małgorzata | Plichta | 1200 | | SG 20 | Karolina | Mucha | 2200 | | SG 21 | Piotr | Cybulski | 1300 | | SL 20 | Paweł | Nowak | 1500 | | SL 21 | Paweł | Kowalski | 1000 | | SL 22 | Monika | Munk | 1100 | | SL 30 | Jan | Wiśniewski | 3000 | | SL 31 | Julia | Lisicka | 900 | | SL 32 | Michał | Brzęczyk | 1000 | +------------+------+----+ 17 rows in set (0. 00 sec) 8

Sortowanie wyników uporządkowane malejąco według kolumny pensja: SELECT personel. Nr, imie, nazwisko, pensja FROM personel ORDER BY pensja DESC; mysql> SELECT personel. Nr, imie, nazwisko, pensja FROM personel ORDER BY pensja DESC; +-------------+-------+----+ | personel. Nr | imie | nazwisko | pensja | +-------------+-------+----+ | SC 1 | Jan | Bogacz | 16250 | | SL 30 | Jan | Wiśniewski | 14650 | | SB 20 | Sabina | Bober | 14050 | | SB 23 | Anna | Biały | 11566 | | SL 21 | Paweł | Kowalski | 11366 | | SL 32 | Michał | Brzęczyk | 11366 | | SL 31 | Julia | Lisicka | 11266 | | SB 21 | Daniel | Frankowski | 1500 | | SL 22 | Monika | Munk | 1466 | | SB 22 | Małgorzata | Kowalska | 1366 | | SL 20 | Paweł | Nowak | 1200 | +-------------+-------+----+ 11 rows in set (0. 00 sec) 9

Sortowanie według wielu kolumn typ, czynsz : SELECT nieruchomosc. Nr, typ, pokoje, czynsz FROM nieruchomosc ORDER BY typ, czynsz DESC; mysql> SELECT nieruchomosc. Nr, typ, pokoje, czynsz -> FROM nieruchomosc -> ORDER BY typ, czynsz DESC; +--------+------+--------+ | nieruchomosc. Nr | typ | pokoje | czynsz | +--------+------+--------+ | G 01 | dom | 7 | 830 | | A 14 | dom | 6 | 715 | | B 21 | dom | 5 | 660 | | B 16 | mieszkanie | 495 | | L 94 | mieszkanie | 440 | | B 17 | mieszkanie | 3 | 412 | | B 18 | mieszkanie | 385 | +--------+------+--------+ 7 rows in set (0. 01 sec) 10

Aliasy nazw kolumn i wyrażeń (nazwy zastępcze): Jako argumenty klauzul ORDER BY i GROUP BY nie mogą być użyte wyrażenia złożone - jedynie nazwy kolumn, . Jeżeli chcemy grupować lub sortować według wartości wyrażeń złożonych stosujemy aliasy tych wyrażeń. Stosuje się słowo kluczowe AS: SELECT wyrażenie 1, wyrażenie 2 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). 11

Wyliczanie pensji rocznej: SELECT personel. Nr, imie, nazwisko, pensja*12 AS Roczna FROM personel; mysql> SELECT personel. Nr, imie, nazwisko, pensja*12 AS Roczna FROM personel; +-------------+-------+----+ | personel. Nr | imie | nazwisko | Roczna | +-------------+-------+----+ | SB 20 | Sabina | Bober | 168600 | | SB 21 | Daniel | Frankowski | 18000 | | SB 22 | Małgorzata | Kowalska | 16392 | | SB 23 | Anna | Biały | 138792 | | SC 1 | Jan | Bogacz | 195000 | | SL 20 | Paweł | Nowak | 14400 | | SL 21 | Paweł | Kowalski | 136392 | | SL 22 | Monika | Munk | 17592 | | SL 30 | Jan | Wiśniewski | 175800 | | SL 31 | Julia | Lisicka | 135192 | | SL 32 | Michał | Brzęczyk | 136392 | +-------------+-------+----+ 11 rows in set (0. 00 sec) 12

Wyliczanie pensji rocznej z sortowaniem: SELECT personel. Nr, imie, nazwisko, pensja*12 AS Roczna FROM personel ORDER BY Roczna; mysql> SELECT personel. Nr, imie, nazwisko, pensja*12 AS Roczna FROM personel ORDER BY Roczna; +-------------+-------+----+ | personel. Nr | imie | nazwisko | Roczna | +-------------+-------+----+ | SL 20 | Paweł | Nowak | 14400 | | SB 22 | Małgorzata | Kowalska | 16392 | | SL 22 | Monika | Munk | 17592 | | SB 21 | Daniel | Frankowski | 18000 | | SL 31 | Julia | Lisicka | 135192 | | SL 21 | Paweł | Kowalski | 136392 | | SL 32 | Michał | Brzęczyk | 136392 | | SB 23 | Anna | Biały | 138792 | | SB 20 | Sabina | Bober | 168600 | | SL 30 | Jan | Wiśniewski | 175800 | | SC 1 | Jan | Bogacz | 195000 | +-------------+-------+----+ 11 rows in set (0. 00 sec) 13

Wyliczanie pensji rocznej (bez alias’u): SELECT personel. Nr, imie, nazwisko, pensja*12 FROM personel; mysql> SELECT personel. Nr, imie, nazwisko, pensja*12 FROM personel; +-------------+-------+------+ | personel. Nr | imie | nazwisko | pensja*12 | +-------------+-------+------+ | SB 20 | Sabina | Bober | 168600 | | SB 21 | Daniel | Frankowski | 18000 | | SB 22 | Małgorzata | Kowalska | 16392 | | SB 23 | Anna | Biały | 138792 | | SC 1 | Jan | Bogacz | 195000 | | SL 20 | Paweł | Nowak | 14400 | | SL 21 | Paweł | Kowalski | 136392 | | SL 22 | Monika | Munk | 17592 | | SL 30 | Jan | Wiśniewski | 175800 | | SL 31 | Julia | Lisicka | 135192 | | SL 32 | Michał | Brzęczyk | 136392 | +-------------+-------+------+ 11 rows in set (0. 00 sec) 14

Eliminacja powtórzeń DISTINCT – podaj numery nieruchomości odwiedzane przez klientów: SELECT DISTINCT nieruchomosc. Nr FROM wizyta; mysql> SELECT DISTINCT nieruchomosc. Nr -> FROM wizyta; +--------+ | nieruchomosc. Nr | +--------+ | A 14 | | B 36 | | B 4 | +--------+ 3 rows in set (0. 00 sec) 15

z powtórzeniami: SELECT nieruchomosc. Nr FROM wizyta; mysql> SELECT nieruchomosc. Nr FROM wizyta; +--------+ | nieruchomosc. Nr | +--------+ | A 14 | | B 36 | | B 4 | | A 14 | | B 4 | +--------+ 5 rows in set (0. 01 sec) 16

Komentarze w liniach komend: (jeżeli chcemy coś opisać w pliku. sql) 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; 17

Dodajemy warunki do zapytań: (wybieranie wierszy) Warunek WHERE Rodzaje warunków: - liczbowe z zakresu - znakowe - daty - oraz łączone. 18

Złożony warunek znakowy Podaj adresy biur w Łomży lub w Białymstoku: SELECT biuro. Nr, ulica, miasto, kod FROM Biuro WHERE miasto='Łomża' OR miasto="Białystok"; mysql> SELECT biuro. Nr, ulica, miasto, kod -> FROM biuro -> WHERE miasto='Łomża' OR miasto="Białystok"; +--------------+--------+ | biuro. Nr | ulica | miasto | kod | +--------------+--------+ | B 001 | Piękna 46 | Białystok | 15 -900 | | B 002 | Cicha 56 | Łomża | 18 -400 | | B 003 | Mała 63 | Białystok | 15 -900 | | B 005 | Dobra 22 | Łomża | 18 -400 | | B 006 | Słoneczna 55 | Białystok | 15 -900 | +--------------+--------+ 5 rows in set (0. 00 sec) możemy stosować cudzysłów " lub apostrof ' 23

Warunek znakowy - przynależność do zbioru: SELECT biuro. Nr, ulica, miasto, kod FROM biuro WHERE miasto IN ('Łomża', 'Białystok'); mysql> SELECT biuro. Nr, ulica, miasto, kod -> FROM biuro -> WHERE miasto IN ('Łomża', 'Białystok'); +--------------+--------+ | biuro. Nr | ulica | miasto | kod | +--------------+--------+ | B 001 | Piękna 46 | Białystok | 15 -900 | | B 002 | Cicha 56 | Łomża | 18 -400 | | B 003 | Mała 63 | Białystok | 15 -900 | | B 005 | Dobra 22 | Łomża | 18 -400 | | B 006 | Słoneczna 55 | Białystok | 15 -900 | +--------------+--------+ 5 rows in set (0. 40 sec) 24

Warunek czynszu z zakresu oraz miasta SELECT nieruchomosc. Nr, miasto, typ, czynsz FROM nieruchomosc WHERE miasto='Białystok' AND czynsz BETWEEN 350 AND 450 OR miasto='Augustów'; mysql> SELECT nieruchomosc. Nr, miasto, typ, czynsz -> FROM nieruchomosc -> WHERE miasto='Białystok' AND czynsz BETWEEN 350 AND 450 OR miasto='Augustów'; +--------+------------+----+ | nieruchomosc. Nr | miasto | typ | czynsz | +--------+------------+----+ | A 14 | Augustów | dom | 715 | | B 17 | Białystok | mieszkanie | 412 | | B 18 | Białystok | mieszkanie | 385 | +--------+------------+----+ 3 rows in set (0. 00 sec) 28

Warunki łączone Wyszukaj informacje o wszystkich mieszkaniach w Białymstoku, w których są przynajmniej trzy pokoje, a czynsz nie przekracza 350 złotych: SELECT nieruchomoscnr AS nr, miasto, ulica, typ, pokoje, czynsz FROM nieruchomosc WHERE miasto = 'Białystok' AND pokoje >= 3 AND czynsz < 350; +-----------+--------+--------+ | nr | miasto | ulica | typ | pokoje | czynsz | +-----------+--------+--------+ | B 16 | Białystok | Nowa 5 | mieszkanie | 4 | 195 | | B 17 | Białystok | Mała 2 | mieszkanie | 3 | 112 | | B 18 | Białystok | Leśna 6 | mieszkanie | 3 | 172 | +-----------+--------+--------+ 30

Wyodrębnianie składowych z czasu: HOUR(czas) MINUTE(czas) SECOND(czas) mysql> SELECT HOUR('10: 15: 43'); +---------+ | HOUR('10: 15: 43') | +---------+ | 10 | +---------+ mysql> SELECT MINUTE('10: 16: 27'); +----------+ | MINUTE('10: 16: 27') | +----------+ | 16 | +----------+ mysql> SELECT SECOND('10: 18: 38'); +----------+ | SECOND('10: 18: 38') | +----------+ | 38 | +----------+ 32

Warunki z daty - wyliczanie wieku i jego ograniczenie: SELECT personel. Nr, imie, nazwisko, stanowisko, data_ur, Year(Now())Year(data_ur) AS wiek FROM personel WHERE (Year(Now())-Year(data_ur)) > 40; mysql> SELECT personel. Nr, imie, nazwisko, stanowisko, data_ur, Year(Now())Year(data_ur) AS wiek FROM personel WHERE (Year(Now())-Year(data_ur))> 40; +------------+------------+------+------+ | personel. Nr | imie | nazwisko | stanowisko | data. Ur | wiek | +------------+------------+------+------+ | SB 20 | Sabina | Bober | dyrektor | 1940 -06 -03 | 70 | | SB 21 | Daniel | Frankowski | kierownik | 1958 -03 -24 | 52 | | SB 23 | Anna | Biały | asystent | 1960 -11 -10 | 50 | | SB 30 | Katarzyna | Michalska | dyrektor | 1960 -11 -17 | 50 | | SG 20 | Karolina | Mucha | dyrektor | 1953 -03 -03 | 57 | | SL 20 | Paweł | Nowak | kierownik | 1962 -02 -02 | 48 | | SL 21 | Paweł | Kowalski | asystent | 1969 -05 -05 | 41 | | SL 30 | Jan | Wiśniewski | dyrektor | 1945 -10 -01 | 65 | | SL 31 | Julia | Lisicka | asystent | 1965 -07 -13 | 45 | | SL 32 | Michał | Brzęczyk | asystent | 1959 -03 -15 | 51 | +------------+------------+------+------+ 10 rows in set (0. 04 sec) 33

Wyliczanie wieku i jego ograniczenie (bez alias’u): SELECT personel. Nr, imie, nazwisko, stanowisko, data_ur, Year(Now())-Year(data_ur) FROM personel WHERE (Year(Now())-Year(data_ur)) > 40; mysql> SELECT personel. Nr, imie, nazwisko, stanowisko, data _ur, Year(Now())-Year(data_ur) FROM personel WHERE (Year(Now())-Year(data _ur))>40; +------------+------------+------+--- ------------+ | personel. Nr | imie | nazwisko | stanowisko | data. Ur | Year(Now())-Year(data. Ur) | +------------+------------+------+--- ------------+ | SB 20 | Sabina | Bober | dyrektor | 1940 -06 -03 | 70 | | SB 21 | Daniel | Frankowski | kierownik | 1958 -03 -24 | 52 | | SB 23 | Anna | Biały | asystent | 1960 -11 -10 | 50 | | SB 30 | Katarzyna | Michalska | dyrektor | 1960 -11 -17 | 50 | | SG 20 | Karolina | Mucha | dyrektor | 1953 -03 -03 | 57 | | SL 20 | Paweł | Nowak | kierownik | 1962 -02 -02 | 48 | | SL 21 | Paweł | Kowalski | asystent | 1969 -05 -05 | 41 | | SL 30 | Jan | Wiśniewski | dyrektor | 1945 -10 -01 | 65 | | SL 31 | Julia | Lisicka | asystent | 1965 -07 -13 | 45 | | SL 32 | Michał | Brzęczyk | asystent | 1959 -03 -15 | 51 | +------------+------------+------+- -------------+ 10 rows in set (0. 04 sec) 34

Warunek z wartości pustych Wyszukaj informacje wizytach w nieruchomości B 4, po których nie zgłoszono uwag: SELECT klient. Nr, data_wizyty, uwagi FROM wizyta WHERE nieruchomosc. Nr='B 4' AND uwagi IS NULL; mysql> SELECT klient. Nr, data_wizyty, uwagi -> FROM wizyta -> WHERE nieruchomosc. Nr='B 4' AND uwagi IS NULL; +-------------+-------+ | klient. Nr | data_wizyty | uwagi | +-------------+-------+ | CR 52 | 2010 -05 -26 | NULL | +-------------+-------+ 1 row in set (0. 00 sec) 35

lub zgłoszono uwagi: WHERE nieruchomosc. Nr='B 4' AND uwagi IS NOT NULL; mysql> SELECT klient. Nr, data_wizyty, uwagi -> FROM wizyta -> WHERE nieruchomosc. Nr='B 4' AND uwagi IS NOT NULL; +-------------+-------+ | klient. Nr | data_wizyty | uwagi | +-------------+-------+ | CR 51 | 2010 -03 -18 | brak jadalni | | CR 53 | 2010 -04 -20 | za daleko | +-------------+-------+ 2 rows in set (0. 01 sec) 36

Zawartość tabeli wizyta: mysql> SELECT * FROM wizyta; +----------------+--------------+ | klientnr | nieruchomoscnr | data_wizyty | uwagi | +----------------+--------------+ | CR 51 | A 14 | 2010 -05 -24 | za mały | | CR 51 | B 4 | 2010 -03 -18 | brak jadalni | | CR 52 | A 14 | 2010 -05 -14 | brak jadalni | | CR 52 | B 36 | 2010 -04 -28 | NULL | | CR 52 | B 4 | 2010 -05 -26 | NULL | | CR 53 | B 4 | 2010 -04 -20 | za daleko | +----------------+--------------+ 6 rows in set (0. 00 sec) 37

Zadania do ćwiczeń: 1. Za wynajęcie których nieruchomości dokonano opłaty gotówką? SELECT FROM WHERE 2. Dla których nieruchomości kaucja była większa od czynszu? SELECT FROM WHERE Diagram oraz odpowiedzi na następnych stronach 38

Diagram do zadań: 39

Odpowiedzi: 1. Za wynajęcie których nieruchomości dokonano opłaty gotówką? SELECT nieruchomoscnr FROM wynajecie WHERE forma_platności = 'gotówka'; 2. Dla których nieruchomości kaucja była większa od czynszu? SELECT nieruchomoscnr FROM wynajecie WHERE kaucja > czynsz; 40

Widoki (views) - Perspektywy: Perspektywa – dynamicznie obliczany wynik jednej lub wielu operacji relacyjnych tworzących nową relację (tabelę) z relacji bazowych. Perspektywa jest tabelą wirtualną, która nie musi fizycznie istnieć w bazie danych, ale może być wyliczona w każdej chwili na żądanie użytkownika. Perspektywy są obiektami zapisywanymi w bazie z ich właściwą nazwą i poprzez tę nazwę wykorzystywane. 41

Podstawowa składnia polecenia: CREATE VIEW nazwa_perspektywy [(lista_kolumn)] AS SELECT zapytanie 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). zapytanie - jest po prostu zapytaniem które chcemy zrealizować i zapisać w bazie w postaci perspektywy. 42

Utworzenie perspektywy: Utwórz perspektywę wyświetlającą pensje pracowników, którzy są dyrektorami: mysql> CREATE VIEW dyrektorzy AS SELECT personelnr, pensja FROM personel WHERE stanowisko='dyrektor'; Utworzenie perspektywy mysql> SELECT * FROM dyrektorzy; +------+----+ | personelnr | pensja | +------+----+ | SB 20 | 2400 | | SB 30 | 2500 | | SG 20 | 2200 | | SL 30 | 3000 | +------+----+ Wyświetlenie danych przez perspektywę 43