Bazy danych 4. Przykłady SQL – podstawy

Bazy danych
4. Przykłady
SQL – podstawy
P. F. Góra
http://th-www.if.uj.edu.pl/zfs/gora/
2011/12
Przykład I
Przedsiebiorca
˛
tworzy baz˛e danych pracowników na potrzeby wypłacania im wynagrodzeń i przekazywania zaliczek do Urz˛edu Skarbowego. Zachodza˛ naste˛
pujace
˛ zależności funkcyjne:
PracId → Stanowisko
Stanowisko → Stawka
PracId → StażPracy
StażPracy Stawka → Wynagrodzenie
PracId → Gmina
Gmina → UrzadSkarbowy
˛
c 2010-11 P. F. Góra
Copyright (1a)
(1b)
(1c)
(1d)
(1e)
(1f)
4–2
1. Udowodnić, że PracId → Wynagrodzenie
PracId → Stanowisko ∧ Stanowisko → Stawka =⇒ PracId → Stawka (2a)
PracId → Stawka ∧ PracId → StażPracy =⇒ PracId → StażPracy Stawka
(2b)
PracId → StażPracy Stawka ∧ StażPracy Stawka → Wynagrodzenie
=⇒ PracId → Wynagrodzenie
(2c)
2. Spróbujmy zaprojektować taka˛ oto tabele:
˛
Pracownik (PracId, StażPracy, Stanowisko, Stawka, Gmina)
(3)
Tabela (??) nie jest w 3PN, gdyż zawiera relacje˛ przechodnia˛ (do atrybutu nie
bed
˛ acego
˛
cz˛eścia˛ klucza). Ponieważ stawka przyporzadkowana
˛
jest stanowisku, nie zaś konkretnemu pracownikowi, wpisywanie stawek jak w (??) prowadzi do redundancji i anomalii modyfikacji.
c 2010-11 P. F. Góra
Copyright 4–3
3. Nastepuj
˛ acy
˛ projekt jest w 3PN, a nawet w PNBC
Pracownicy (PracId, StażPracy, Stanowisko, Gmina)
(4a)
Stanowiska (Stanowisko, Stawka)
(4b)
Płace StażPracy, Stawka, Wynagrodzenie
(4c)
˛
Urz˛edy (Gmina, UrzadSkarbowy
)
(4d)
4. W opraciu o projekt (??), w jezyku
˛
algebry relacji sformułować zapytanie
o wynagrodzenie konkretnego pracownika
ρT1 πStanowisko,StażPracy,Wynagrodzenie (Stanowiska 1 Płace)
πPracId,Wynagrodzenie (T1 1 Pracownicy)
(5a)
(5b)
Sa˛ inne możliwe ścieżki wykonania tego zapytania.
c 2010-11 P. F. Góra
Copyright 4–4
Przykład II
Kancelaria adwokacka prowadzi baz˛e czynności adwokackich. Każda czynność
wykonana jest przez pracownika na jakimś stanowisku; ze stanowiskiem zwia˛
zana jest stawka. Każda czynność wykonana jest na rzecz pewnego klienta.
Cena, jaka˛ płaci klient, zależy od czasu trwania czynności adwokackiej i stawki,
jaka˛ pobiera pracownik kancelarii.
Zależności funkcyjne:
PracId → Stanowisko
Stanowisko → Stawka
Czynność → Pracownik KlientId Czas
Czas Stawka → Koszt
KlientId → NazwaKlienta
c 2010-11 P. F. Góra
Copyright (6a)
(6b)
(6c)
(6d)
(6e)
4–5
Tabele:
Czynności Czynność, PracId, KlientId, Czas
(7a)
Pracownicy (PracId, Stanowisko)
(7b)
Stanowiska (Stanowisko, Stawka)
(7c)
Opłaty (Czas, Stawka, Koszt)
(7d)
Kilenci (KlientId, NazwaKlienta)
(7e)
Mimo iż po zdefiniowaniu osobnych tabel Pracownicy i Stanowiska tabel jest
wiecej,
˛
łacznie
˛
przechowujemy mniej informacji.
c 2010-11 P. F. Góra
Copyright 4–6
Przykład III
Tworzymy baz˛e opisujac
˛ a˛ zaliczenia, jakie studenci uzyskiwali ze swoich projektów. Jeden student może podchodzić do tego samego projektu wiele razy (może
poprawiać ocene).
˛ Jednego dnia jeden student może starać sie˛ tylko o jedno zaliczenie.
NrZaliczenia → NrIndeksu NrProjektu Data Ocena
(8a)
NrIndeksu Data → NrZaliczenia
(8b)
Wydaje sie,
˛ że projekt
Zaliczenia (NrZaliczenia, NrIndeksu, Data, NrProjektu, Ocena)
(9)
nie jest poprawny, gdyż nie uwzglednia
˛
drugiej z zależności (??).
c 2010-11 P. F. Góra
Copyright 4–7
Projekt
Zaliczenia (NrZaliczenia, NrIndeksu, Data, NrProjektu, Ocena)
(10)
nie jest poprawny, gdyż zawiera zależności przechodnie:
NrIndeksu Data → NrZaliczenia → NrProjektu Ocena, a zatem nie jest w 3PN
(ustaliliśmy, że NrZaliczenia nie może być kluczem!).
c 2010-11 P. F. Góra
Copyright 4–8
Rozbijmy pierwsza˛ z zależności funkcyjnych (??)na dwie cz˛eści:
NrZaliczenia → NrProjektu Ocena
(11a)
NrZaliczenia → NrIndeksu Data
(11b)
NrIndeksu Data → NrZaliczenia
(11c)
Zależności (??), (??) sa˛ swoimi odwrotnościami, a zatem jedna˛ z nich można
wyeliminować.
Projekt
KtoKiedy (NrIndeksu, Data, NrZaliczenia)
(12a)
CoZaCo (NrZaliczenia, NrProjektu, Ocena)
(12b)
jest w PNBC. Ta analiza pokazuje, że projekt (??) tak naprawde˛ jest poprawny .
c 2010-11 P. F. Góra
Copyright 4–9
Przykład IV∗
Fabryka w magazynie przechowuje cz˛eści różnego typu (śrubki i nakretki).
˛
Każda cz˛eść ma swój unikalny identyfikator. W magazynie każdy pojemnik („regał”) zawiera identyczne cz˛eści. Mamy relacje funkcyjne
Śrubki:
Nakretki:
˛
NrRegału → IdCz˛eści Ilość
(13a)
IdCz˛eści → Długość Średnica TypŁba
(13b)
IdCz˛eści → Średnica
(13c)
Projekty odpowiednich tabel sa˛ (pozornie) oczywiste.
c 2010-11 P. F. Góra
Copyright 4–10
Niektóre zapytania bardzo wykonać jest bardzo łatwo, na przykład wskaż regały,
w których znajduja˛ sie˛ śrubki o długości 3/4":
πNrRegału πIdCz˛eści σDługość=3/4"Śrubki
1 Regały
(14)
Inne zapytania sa˛ bardziej problematyczne — na przykład jaki typ łba maja˛
śrubki przechowywane na regale 58
πTypŁba
σNrRegału=58Regały
1 Śrubki
(15)
To zapytanie powinno zadziałać dobrze: Jeśli cz˛eści przechowywane na regale
58 sa˛ śrubkami, wszystko jest OK. Jeśli cz˛eści przechowywane na regale 58 nie
sa˛ śrubkami (tylko nakretkami),
˛
złaczenie
˛
w (??) da zbiór pusty, a wiec
˛ także OK
— pod warunkiem, że nie przypiszemy tego samego IdCz˛eści śrubkom i nakret˛
kom, a takiego wiezu
˛ w naszym projekcie nie ma.
c 2010-11 P. F. Góra
Copyright 4–11
Problem można cz˛eściowo rozwiazać
˛
modyfikujac
˛ tabele˛ Regały:
NrRegału → IdCz˛eści RodzajCz˛eści Ilość
(16)
Wówczas możliwe jest sprawdzenie na których regałach znajduja˛ sie˛ śrubki
ρRegałyŚrubek σRodzajCz˛eści=’Śrubki’Regały
(17)
i dalej
πTypŁba
σNrRegału=58RegałyŚrubek
1 Śrubki
(18)
Jeśli na regale 58 nie ma śrubek, dostaniemy zbiór pusty.
c 2010-11 P. F. Góra
Copyright 4–12
W relacyjnym modelu danych nie ma “rekordów z wariantami”, zanych z niektórych jezyków
˛
programowania.
Prawdziwym rozwiazaniem
˛
byłoby przekonanie zleceniodawcy, że stosowanie
jednolitej numeracji do różnego rodzaju cz˛eści nie jest rozsadne.
˛
Jeżeli nie da sie˛ zleceniodawcy do tego przekonać — a zleceniodawca może
mieć uzasadnione powody aby protestować — najlepszym rozwiazaniem
˛
jest
zsumowanie zależności (??), (??), które maja˛ ten sam poprzednik:
NrRegału → IdCz˛eści Ilość
IdCz˛eści → Długość Średnica TypŁba
(19a)
(19b)
Atrybut Średnica wystepuje
˛
w nastepnikach
˛
dwa razy, wiec
˛ po zsumowaniu pojawia sie˛ tylko raz. Teraz należy rozumieć, że jeżeli atrybuty Długość, TypŁba
przyjmuja˛ wartość null, mamy do czynienia z nakretk
˛ a,
˛ w przeciwnym zaś razie ze śrubka.
˛
c 2010-11 P. F. Góra
Copyright 4–13
Zgodnie z zasadami sztuki, funkcja określajaca
˛ czy dana cz˛eść jest śrubka,
˛ czy
nakretk
˛ a,
˛ powinna być wbudowana w baz˛e danych (jako tak zwana procedura
składowana).
Podobnie w bazie powinien znaleźć sie˛ wiez
˛ domenowy (check) stwierdzajacy,
˛
że atrybuty Długość, TypŁba musza˛ być jednocześnie null lub not null.
Atrybut Średnica powinien być zawsze not null.
c 2010-11 P. F. Góra
Copyright 4–14
Przykład V (kolejowy)
Tworzymy baz˛e danych zawierajac
˛ a˛ (uproszczony) rozkład jazdy pociagów
˛
oraz
informacje o sprzedanych biletach.
Po pierwsze,
• Z każdym pociagiem
˛
stowarzyszona jest lista stacji, na których sie˛ on zatrzymuje.
• Z każda˛ stacja˛ stowarzyszony jest zbiór pociagów,
˛
na których sie˛ on zatrzymuje.
• Dla każdej stacji należy podać godziny przyjazdu i odjazdu każdego pocia˛
gu, który sie˛ na niej zatrzymuje.
c 2010-11 P. F. Góra
Copyright 4–15
Pierwszy i ostatni punkt odpowiadaja˛ nastepuj
˛ acym
˛
zależnościom funkcyjnym:
NrPociagu
˛ Stacja → NrKolejny
(20a)
NrPociagu
˛ Stacja → Przyjazd Odjazd
(20b)
co prowadzi do nastepuj
˛ acej
˛ struktury tabeli
Stacja, NrKolejny, Przyjazd, Odjazd
Przystanki NrPociagu,
˛
(21)
Powstaje jednak pewna watpliwość:
˛
A co sie˛ stanie jeśli jakiś pociag
˛ nie przejeżdża przez dana˛ stacje?
˛ Czy przypoadkiem zależności funkcyjne (??) nie wymagaja˛ wyspecyfikowania wszystkich możliwych par NrPociagu-Stacja?
˛
Oczywiście nie: Zależność funkcyjna ma postać implikacji: jeżeli podamy atrybuty
poprzednika, to ustalamy jednoznacznie wartości atrybutów nastepnika.
˛
A jeżeli
nie, to nie ,.
c 2010-11 P. F. Góra
Copyright 4–16
Rola zapytań
A co z określeniem zbioru pociagów,
˛
które zatrzymuja˛ sie˛ na danej stacji? Nie
trzeba w tym celu konstruować osobnej tabeli (relacji) — można to osiagn
˛ ać
˛
poprzez odpowiednie zapytanie:
πNrPociagu
σStacja=’Koluszki’ (Przystanki)
˛
(22)
Nie wszystkie informacje przechowuje sie˛ w postaci osobnych tabel —
użyteczność baz danych polega (miedzy
˛
innymi) na tym, że wiele rzeczy da sie˛
zrealizować poprzez zapytania.
c 2010-11 P. F. Góra
Copyright 4–17
Zadanie
Posługujac
˛ sie˛ schematem tabeli Przystanki (??), znaleźć wszystkie pociagi,
˛
które przejeżdżaja˛ przez stacje˛ X oraz przez stacje˛ Y.
πNrPociagu
σStacja=’X’ (Przystanki) 1 πNrPociagu
σStacja=’Y’ (Przystanki)
˛
˛
(23)
Tabela Przystanki wystepuje
˛
w powyższym zapytaniu dwa razy, po obu stronach
złaczenia.
˛
Jest to przykład tak zwanego samozłaczenia
˛
(self-join).
c 2010-11 P. F. Góra
Copyright 4–18
Zadanie∗
Posługujac
˛ sie˛ schematem tabeli Przystanki (??), znaleźć wszystkie pociagi,
˛
które najpierw przejeżdżaja˛ przez stacje˛ X, później przez stacje˛ Y.
h
i
(24a)
h
i
(24b)
ρStacjaX(NrPociagu,NrX)
πNrPociagu,NrKolejny
σStacja=’X’ (Przystanki)
˛
˛
ρStacjaY(NrPociagu,NrY)
πNrPociagu,NrKolejny
σStacja=’Y’ (Przystanki)
˛
˛
πNrPociagu
σNrX<NrY (StacjaX 1 StacjaY)
˛
(24c)
Zapytanie (??) oczywiście także zawiera samozłaczenie,
˛
tyle że w formie niejawnej.
Pytanie: Po jakim atrybucie realizowane jest złaczenie
˛
w (??)? Po atrybucie
NrPociagu,
˛
bo to jest powtarzajacy
˛ sie˛ atrybut w tabelach StacjaX, StacjaY.
c 2010-11 P. F. Góra
Copyright 4–19
Structured Query Language
Używane standardy:
• SQL92
• SQL99
• SQL:2003
Żaden dostawca nie jest w pełni zgodny ze standardem — prawie wszyscy wprowadzaja˛ rozszerzenia, prawie nikt nie spełnia wszystkich wymogów. W dużych
systemach komercyjnych odejście od wymogów nie jest wielkie
c 2010-11 P. F. Góra
Copyright 4–20
Programowanie deklaratywne
SQL — a ściślej, podzbiór SQL obejmujacy
˛ zapytania SELECT — jest jezy˛
kiem deklaratywnym: określa logik˛e programowania, bez określenia control flow.
Innymi słowy, zapytanie SQL mówi co chcemy osiagn
˛ ać,
˛ bez określenia jak
chcemy to zrobić.
Przeciwieństwem deklaratywnego paradygmatu programowania jest programowanie imperatywne.
c 2010-11 P. F. Góra
Copyright 4–21
Połaczenie
˛
sie˛ z serwerem
Przed rozpocz˛eciem pracy, nalezy wywołać proces kliencki, który połaczy sie˛ z
serwerem. W MySQL może to wygladać
˛
na przykład tak:
C:\>mysql -upawel -p
Enter password: *************
Po zakończeniu pracy trzeba sie˛ pożegnać:
mysql> QUIT;
Bye
Znacznie cz˛eściej połaczenie
˛
z serwerem nawiazuje
˛
sie˛ za pomoca˛ jakiejś aplikacji, nie zaś bezpośrednio z shella.
c 2010-11 P. F. Góra
Copyright 4–22
Utworzenie bazy danych
mysql> CREATE DATABASE MoiStudenci CHARACTER SET cp1250;
Query OK, 1 row affected (0.06 sec)
mysql> USE MoiStudenci;
Database changed
mysql>
Po wywołaniu klienta, trzeba “wejść” do wybranej bazy za pomoca˛ instrukcji USE
— nie tylko po jej utworzeniu, ale zawsze.
c 2010-11 P. F. Góra
Copyright 4–23
To, jakie bazy znajduja˛ sie˛ na serwerze, możemy zobaczyć za pomoca˛ instrukcji
SHOW DATABASES.
mysql> SHOW DATABASES;
+--------------------+
| Database
|
+--------------------+
| information_schema |
| kaskady
|
| moistudenci
|
| mysql
|
| test
|
+--------------------+
5 rows in set (0.00 sec)
c 2010-11 P. F. Góra
Copyright 4–24
Utworzenie tabeli
mysql> CREATE TABLE Studenci
-> (NrStudenta SMALLINT UNSIGNED NOT NULL
-> AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.14 sec)
Jeśli okaże sie˛ to konieczne, po utworzeniu można zmienić definicje˛ tabeli.
mysql> ALTER TABLE Studenci
-> ADD COLUMN (Nazwisko VARCHAR(20) NOT NULL);
Query OK, 1 row affected (0.17 sec)
Records: 1 Duplicates: 0 Warnings: 0
c 2010-11 P. F. Góra
Copyright 4–25
Składnia polecenia CREATE TABLE
CREATE TABLE NazwaTabeli (
NazwaKolumny1 TypKolumny1 NULL | NOT NULL . . . ,
NazwaKolumny2 TypKolumny2 NULL | NOT NULL . . . ,
...
)
PRIMARY KEY (NazwaKolumnyp,NazwaKolumnyq ,. . . )
;
Inne opcje poznamy później.
c 2010-11 P. F. Góra
Copyright 4–26
Typy danych (atrybutów)
TINYINT
SMALLINT
MEDIUMINT
INT
INTEGER
BIGINT
REAL
DOUBLE
FLOAT
DECIMAL
NUMERIC
c 2010-11 P. F. Góra
Copyright DATE
TIME
TIMESTAMP
DATETIME
CHAR
VARCHAR
BOOLEAN
TINYBLOB
BLOB
MEDIUMBLOB
LONGBLOB
TINYTEXT
TEXT
LONGTEXT
ENUM
SET
4–27
Instrukcja DESCRIBE podaje definicje˛ tabeli.
mysql> DESCRIBE Studenci;
+------------+----------------------+------+-----+---------+----------------+
| Field
| Type
| Null | Key | Default | Extra
|
+------------+----------------------+------+-----+---------+----------------+
| NrStudenta | smallint(5) unsigned | NO
| PRI | NULL
| auto_increment |
| Nazwisko
| varchar(20)
| NO
|
|
|
|
+------------+----------------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
Niepotrzebna˛ tabele˛ usuwamy za pomoca˛ instrukcji DROP TABLE.
mysql> DROP TABLE Studenci;
Query OK, 0 rows affected (0.02 sec)
Lepiej ja˛ utworzyć od poczatku
˛
dobrze niż dodawać kolumna po kolumnie. . . ,
c 2010-11 P. F. Góra
Copyright 4–28
mysql> CREATE TABLE Studenci
-> (NrStudenta SMALLINT UNSIGNED NOT NUll
-> AUTO_INCREMENT PRIMARY KEY,
-> Imie VARCHAR(20),
-> Nazwisko VARCHAR(20) NOT NULL,
-> Uwagi VARCHAR(30),
-> Grupa CHAR(2) NOT NULL));
Query OK, 0 rows affected (0.06 sec)
mysql> DESCRIBE Studenci;
+------------+----------------------+------+-----+---------+----------------+
| Field
| Type
| Null | Key | Default | Extra
|
+------------+----------------------+------+-----+---------+----------------+
| NrStudenta | smallint(5) unsigned | NO
| PRI | NULL
| auto_increment |
| Imie
| varchar(20)
| YES |
| NULL
|
|
| Nazwisko
| varchar(20)
| NO
|
|
|
|
| Uwagi
| varchar(30)
| YES |
| NULL
|
|
| Grupa
| char(2)
| NO
|
|
|
|
+------------+----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
c 2010-11 P. F. Góra
Copyright 4–29
Wstawianie wartości do tabeli
mysql> SET CHARACTER SET cp1250;
Query OK, 0 rows affected (0.00 sec)
Gdybyśmy tego nie zrobili, MySQL używałby takiego zestawu znaków, jaki obowiazywał
˛
domyślnie w momencie połaczenia
˛
z serwerem. Kwestie określania
zestawu znaków to osobliwość MySQLa /
mysql> INSERT INTO Studenci VALUES
-> (1,’Milena’,’Zahorska’,’wt’,’uzupełniajace’);
˛
Query OK, 1 row affected (0.02 sec)
c 2010-11 P. F. Góra
Copyright 4–30
Co teraz jest w tabeli?
mysql> SELECT * FROM Studenci;
+------------+--------+----------+---------------+-------+
| NrStudenta | Imie
| Nazwisko | Uwagi
| Grupa |
+------------+--------+----------+---------------+-------+
|
1 | Milena | Zahorska | uzupełniajace
˛
| wt
|
+------------+--------+----------+---------------+-------+
1 row in set (0.00 sec)
c 2010-11 P. F. Góra
Copyright 4–31
Składnia zapytania SELECT;
SELECT [DISTINCT] lista select
FROM tabela lub zapytanie
WHERE warunek logiczny
GROUP BY wyrażenie grupujace
˛
HAVING warunek wyszukiwania po grupowaniu
ORDER BY wyrażenie porzadkuj
˛
ace
˛ [ASC | DESC]
;
W zapytaniu SELECT wszystkie klauzule musza˛ wystepować
˛
w powyższej kolejności. Niektóre klauzule można opuścić, ale to, co jest, musi być w tej kolejności.
c 2010-11 P. F. Góra
Copyright 4–32
Dodajmy wiecej
˛
danych
mysql> INSERT INTO Studenci VALUES
-> (2,’Sylwester’,’Tomiec’,’uzupełniajace’,’wt’),
˛
-> (3,’Michał’,’Gajewczyk’,’’’’,’wt’);
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM Studenci;
+------------+-----------+-----------+---------------+-------+
| NrStudenta | Imie
| Nazwisko | Uwagi
| Grupa |
+------------+-----------+-----------+---------------+-------+
|
1 | Milena
| Zahorska | uzupełniajace
˛
| wt
|
|
2 | Sylwester | Tomiec
| uzupełniajace
˛
| wt
|
|
3 | Michał
| Gajewczyk | ’
| wt
|
+------------+-----------+-----------+---------------+-------+
3 rows in set (0.00 sec)
c 2010-11 P. F. Góra
Copyright 4–33
Wykorzystanie opcji AUTOINCREMENT
mysql> INSERT INTO Studenci (Imie,Nazwisko,Grupa) VALUES
-> (’Rafał’,’Świderski’,’wt’),
-> (’Maciej’,’Matowicki’,’wf’),
-> (’Janko’,’Muzykant’,’nd’);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM Studenci;
+------------+-----------+-----------+---------------+-------+
| NrStudenta | Imie
| Nazwisko | Uwagi
| Grupa |
+------------+-----------+-----------+---------------+-------+
|
1 | Milena
| Zahorska | uzupełniajace
˛
| wt
|
|
2 | Sylwester | Tomiec
| uzupełniajace
˛
| wt
|
|
3 | Michał
| Gajewczyk | ’
| wt
|
|
4 | Rafał
| Świderski | NULL
| wt
|
|
5 | Maciej
| Matowicki | NULL
| wf
|
|
6 | Janko
| Muzykant | NULL
| nd
|
+------------+-----------+-----------+---------------+-------+
6 rows in set (0.00 sec)
c 2010-11 P. F. Góra
Copyright 4–34
Zapytanie UPDATE
mysql> UPDATE Studenci
-> SET Uwagi=NULL
-> WHERE NrStudenta=3;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE Studenci
-> SET Grupa=’wt’ WHERE Grupa=’wf’;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
c 2010-11 P. F. Góra
Copyright 4–35
mysql> SELECT * FROM Studenci;
+------------+-----------+-----------+---------------+-------+
| NrStudenta | Imie
| Nazwisko | Uwagi
| Grupa |
+------------+-----------+-----------+---------------+-------+
|
1 | Milena
| Zahorska | uzupełniajace
˛
| wt
|
|
2 | Sylwester | Tomiec
| uzupełniajace
˛
| wt
|
|
3 | Michał
| Gajewczyk | NULL
| wt
|
|
4 | Rafał
| Świderski | NULL
| wt
|
|
5 | Maciej
| Matowicki | NULL
| wt
|
|
6 | Janko
| Muzykant | NULL
| nd
|
+------------+-----------+-----------+---------------+-------+
6 rows in set (0.00 sec)
c 2010-11 P. F. Góra
Copyright 4–36
Zapytanie DELETE
Usuwanie wierszy spełniajacych
˛
podane kryterium:
mysql> DELETE FROM Studenci
-> WHERE Imie=’Janko’;
Query OK, 1 row affected (0.03 sec)
Dodajmy coś jeszcze:
mysql> INSERT INTO Studenci (Imie,Nazwisko,Grupa) VALUES
-> (’Michał’,’Czubek’,’pt’),(’Marcin’,’Pyra’,’pt’),
-> (’Marcin’,’Baranowski’,’pt’);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
c 2010-11 P. F. Góra
Copyright 4–37
mysql> SELECT * FROM Studenci;
+------------+-----------+------------+---------------+-------+
| NrStudenta | Imie
| Nazwisko
| Uwagi
| Grupa |
+------------+-----------+------------+---------------+-------+
|
1 | Milena
| Zahorska
| uzupełniajace
˛
| wt
|
|
2 | Sylwester | Tomiec
| uzupełniajace
˛
| wt
|
|
3 | Michał
| Gajewczyk | NULL
| wt
|
|
4 | Rafał
| Świderski | NULL
| wt
|
|
5 | Maciej
| Matowicki | NULL
| wt
|
|
7 | Michał
| Czubek
| NULL
| pt
|
|
8 | Marcin
| Pyra
| NULL
| pt
|
|
9 | Marcin
| Baranowski | NULL
| pt
|
+------------+-----------+------------+---------------+-------+
8 rows in set (0.01 sec)
Brakuje numeru ’6’ — AUTOINCREMENT “zapamietał”,
˛
że on tam był.
AUTOINCREMENT zawsze startuje od high water mark.
c 2010-11 P. F. Góra
Copyright 4–38
Jawnie wyspecyfikowana lista select
mysql> SELECT Imie, Nazwisko FROM Studenci;
+-----------+------------+
| Imie
| Nazwisko
|
+-----------+------------+
| Milena
| Zahorska
|
| Sylwester | Tomiec
|
| Michał
| Gajewczyk |
| Rafał
| Świderski |
| Maciej
| Matowicki |
| Michał
| Czubek
|
| Marcin
| Pyra
|
| Marcin
| Baranowski |
+-----------+------------+
8 rows in set (0.01 sec)
Gwiazdka w zapytaniu SELECT (SELECT * FROM...) oznacza “wszystkie kolumny”.
c 2010-11 P. F. Góra
Copyright 4–39
Problem duplikatów
mysql> SELECT Imie
-> FROM Studenci;
+-----------+
| Imie
|
+-----------+
| Milena
|
| Sylwester |
| Michał
|
| Rafał
|
| Maciej
|
| Michał
|
| Marcin
|
| Marcin
|
+-----------+
8 rows in set (0.00 sec)
c 2010-11 P. F. Góra
Copyright mysql> SELECT DISTINCT Imie
-> FROM Studenci;
+-----------+
| Imie
|
+-----------+
| Milena
|
| Sylwester |
| Michał
|
| Rafał
|
| Maciej
|
| Marcin
|
+-----------+
6 rows in set (0.00 sec)
4–40
Operator LIKE
mysql> SELECT Imie, Nazwisko
-> FROM Studenci
-> WHERE Imie LIKE ’Mi%’;
+--------+-----------+
| Imie
| Nazwisko |
+--------+-----------+
| Milena | Zahorska |
| Michał | Gajewczyk |
| Michał | Czubek
|
+--------+-----------+
3 rows in set (0.00 sec)
c 2010-11 P. F. Góra
Copyright mysql> SELECT Imie, Nazwisko
-> FROM Studenci
-> WHERE Imie LIKE ’%ł’;
+--------+-----------+
| Imie
| Nazwisko |
+--------+-----------+
| Michał | Gajewczyk |
| Rafał | Świderski |
| Michał | Czubek
|
+--------+-----------+
3 rows in set (0.00 sec)
4–41
Wykorzystanie ORDER BY
mysql> SELECT Imie, Nazwisko
-> FROM Studenci
-> WHERE Grupa=’wt’
-> ORDER BY Nazwisko ASC;
+-----------+-----------+
| Imie
| Nazwisko |
+-----------+-----------+
| Michał
| Gajewczyk |
| Maciej
| Matowicki |
| Sylwester | Tomiec
|
| Milena
| Zahorska |
| Rafał
| Świderski |
+-----------+-----------+
5 rows in set (0.02 sec)
Problemy z poprawnym sortowaniem polskich znaków diakrytycznych /
c 2010-11 P. F. Góra
Copyright 4–42
MySQL i programowanie wsadowe
C:\wyklady\bazy> mysql < nazwa pliku
C:\wyklady\bazy> mysql -hhostname -uusername -p < nazwa pliku
Plik musi być dostepny
˛
dla klienta.
Pliki wsadowe można też wołać “z wnetrza”
˛
klienta:
mysql> source nazwa pliku;
Uwaga: Jeśli któreś polecenie w pliku wsadowym spowoduje bład,
˛ dalsze polecenia nie sa˛ wykonywane.
c 2010-11 P. F. Góra
Copyright 4–43
Tworzenie tabeli wraz z kopiowaniem danych
mysql> USE moistudenci;
Database changed
mysql> DESCRIBE Studenci;
+------------+----------------------+------+-----+---------+----------------+
| Field
| Type
| Null | Key | Default | Extra
|
+------------+----------------------+------+-----+---------+----------------+
| NrStudenta | smallint(5) unsigned | NO
| PRI |
| auto_increment |
| Imie
| varchar(20)
| NO
|
|
|
|
| Nazwisko
| varchar(20)
| NO
|
|
|
|
| Uwagi
| varchar(30)
| YES |
|
|
|
| Grupa
| char(2)
| YES |
|
|
|
+------------+----------------------+------+-----+---------+----------------+
5 rows in set (0.21 sec)
c 2010-11 P. F. Góra
Copyright 4–44
mysql> CREATE TABLE Studenci2
-> (NrStudenta INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> Imie VARCHAR(20) NOT NULL, Nazwisko VARCHAR(20) NOT NULL,
-> Uwagi VARCHAR(30), Grupa CHAR(2))
-> -- TU kończy si˛
e definicja tabeli!
-> SELECT * FROM STUDENCI;
Query OK, 9 rows affected (1.08 sec)
Records: 9 Duplicates: 0 Warnings: 0
mysql> DESCRIBE Studenci2;
+------------+------------------+------+-----+---------+----------------+
| Field
| Type
| Null | Key | Default | Extra
|
+------------+------------------+------+-----+---------+----------------+
| NrStudenta | int(10) unsigned | NO
| PRI |
| auto_increment |
| Imie
| varchar(20)
| NO
|
|
|
|
| Nazwisko
| varchar(20)
| NO
|
|
|
|
| Uwagi
| varchar(30)
| YES |
|
|
|
| Grupa
| char(2)
| YES |
|
|
|
+------------+------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
c 2010-11 P. F. Góra
Copyright 4–45
mysql> SELECT * FROM Studenci2;
+------------+----------+--------------+-------+-------+
| NrStudenta | Imie
| Nazwisko
| Uwagi | Grupa |
+------------+----------+--------------+-------+-------+
| 1
| Mariusz | Lewandowski | xxxx | xy
|
| 2
| Jakub
| Stefanowski |
| xy
|
| 3
| Marek
| Giebułtowski |
| xy
|
| 4
| Barbara | Jeziorek
|
| xy
|
| 6
| Wojciech | Wachal
˛
|
| xy
|
| 72
| Jakub
| Janoszek
|
| xy
|
| 73
| Maciej
| Gowin
|
| xy
|
| 74
| Tadeusz | Drozda
|
| xy
|
| 75
| Marek
| Kolejny
|
| xy
|
+------------+----------+--------------+-------+-------+
9 rows in set (0.03 sec)
c 2010-11 P. F. Góra
Copyright 4–46
Zmienne tymczasowe
SQL pozwala na definiowanie zmiennych tymczasowych — nazwa zmiennej
tymczasowej zawsze zaczyna sie˛ od @. Zmienna istnieje dopóty, dopóki nie zostanie zakończone połaczenie
˛
z serwerem. Procesy klienckie nie widza˛ zmiennych zdefiniowanych przez inne procesy.
mysql> SET @a=5;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT @a-2;
+------+
| @a-2 |
+------+
| 3
|
+------+
1 row in set (0.00 sec)
c 2010-11 P. F. Góra
Copyright 4–47
Zmiennej można przypisać wartość, która jest wynikiem zapytania:
mysql> SET @b=(SELECT NrStudenta FROM Studenci2 WHERE Imie=’Tadeusz’);
Query OK, 0 rows affected (0.89 sec)
mysql> SELECT @b, @a+@b;
+------+-------+
| @b
| @a+@b |
+------+-------+
| 74
| 79
|
+------+-------+
1 row in set (0.00 sec)
c 2010-11 P. F. Góra
Copyright 4–48
Aliasy
W zapytaniu SELECT możemy nadawać kolumnom inne nazwy niż te, które wystepuj
˛ a˛ w definicji tabeli. Nosza˛ one nazwe˛ aliasów.
mysql> SELECT NrStudenta AS ToJestNumer, Imie FROM Studenci2;
+-------------+----------+
| ToJestNumer | Imie
|
+-------------+----------+
| 1
| Mariusz |
| 2
| Jakub
|
| 3
| Marek
|
| 4
| Barbara |
| 6
| Wojciech |
| 72
| Jakub
|
| 73
| Maciej
|
| 74
| Tadeusz |
| 75
| Marek
|
| 65536
| X
|
+-------------+----------+
10 rows in set (0.00 sec)
c 2010-11 P. F. Góra
Copyright 4–49
Niekiedy wygodnie jest użyć aliasu w dalszej cz˛eści zapytania, ale nie można go
użyć w kaluzuli WHERE
mysql> SELECT NrStudenta AS ToJestNumer, Imie FROM Studenci2
-> WHERE ToJestNumer > 10;
ERROR 1054 (42S22): Unknown column ’ToJestNumer’ in ’where clause’
Można natomiast użyć aliasu w klauzuli HAVING:
mysql> SELECT NrStudenta AS ToJestNumer, Imie as ’Pierwsze imi˛
e’ FROM Studenci2
-> HAVING ToJestNumer > 10;
+-------------+---------------+
| ToJestNumer | Pierwsze imi˛
e |
+-------------+---------------+
| 72
| Jakub
|
| 73
| Maciej
|
| 74
| Tadeusz
|
| 75
| Marek
|
| 65536
| X
|
+-------------+---------------+
5 rows in set (0.00 sec)
c 2010-11 P. F. Góra
Copyright 4–50
Jest to szczególnie przydatne, gdy w warunku wystepuje
˛
wartość obliczana na
podstawie wiecej
˛
niż jednej kolumny:
mysql> SELECT CONCAT(Imie,’ ’,Nazwisko) AS Imie_Nazwisko FROM Studenci2
-> HAVING Imie_Nazwisko LIKE "%r%z%";
+---------------------+
| Imie_Nazwisko
|
+---------------------+
| Mariusz Lewandowski |
| Barbara Jeziorek
|
| Tadeusz Drozda
|
+---------------------+
3 rows in set (0.00 sec)
c 2010-11 P. F. Góra
Copyright 4–51