Podstawowe operacje języka SQL

Wydział Elektrotechniki, Informatyki i Telekomunikacji
Instytut Informatyki i Elektroniki
Instrukcja do zajęć laboratoryjnych
Przetwarzanie i organizowanie danych : bazy danych
Nr ćwiczenia
Temat
Cel ćwiczenia
Wymagane teoretyczne przygotowanie
Sposób zaliczenia
Prowadzący
2
Podstawowe operacje języka SQL
Zapoznanie się operacjami tworzenia bazy
danych, tworzenia tabel, operacje aktualizacji
tabel: operacja dodawania, usuwania i
modyfikacji. Zapytania proste, złożone
(wtórne) skorelowane.
Podstawy programowania.
dr inż. Paweł Majdzik
1. Wstęp
1.1. Oprogramowanie
Większość operacji zostanie przedstawiona i zrealizowana w środowisku MySQL, która jest
jedną z najpopularniejszych baz danych. Zapytania skorelowane będą realizowane w
środowisku Dbase.
1.2. Cel ćwiczeń.
Celem ćwiczeń zawartych w instrukcji jest przedstawienie operacji związanych z
definiowaniem struktur danych: tabel, indeksów, itd. oraz operacji związanych z
manipulowaniem danymi: operacje aktualizacji danych oraz zapytania.
1.3. Przygotowanie środowiska MySQL.
a) uruchomić program putty.exe.\
b) W polu Host name podać adres IP serwera (dendrit.issi.uz.zgora.pl) i kliknąć przycisk
Open.
c) Przy zapytaniu o nazwę użytkownika (login as) podać podyplom i wcisnąć enter.
d) Przy zapytaniu o hasło (password) podać podyplom i wcisnąć enter (podczas
wprowadzania hasła nie pokazują się żadne znaki).
e) mysql – u podyplom -p
Każdy student będzie miał założone własne konto. Wówczas instrukcja e) będzie posiadała
postać:
mysql – u nazwisko_studenta -p
2.
Tworzenie tabel – operacja CREATE TABLE
Proste deklaracja tabeli
/* tworzenie tabeli osoba*/
CREATE TABLE osoba (
nazwisko
CHAR(30)
NOT NULL,
adres
VARCHAR(255) NOT NULL,
płeć
CHAR(1)
NOT NULL,
telefon
VARCHAR(9)
);
Komentarz:
 tabela osoba składa się z 4 kolumn.
 tekst umieszczony pomiędzy /*........*/ lub poprzedzony – stanowi komentarz i
jest w związku z tym pomijany przez interpreter języka SQL.
 pierwsza kolumna deklaracji określa nazwy atrybutów relacji (kolumny tabeli),
druga typ poszczególnych atrybutów relacji. Status NOT NULL oznacza, że dla
każdej krotki (wiersza) w relacji (tabeli) pierwsze 3 atrybuty muszą posiadać
pewną wartość (różną od NULL).
-- tworzenie tabeli studio
-- Dana jest relacja film_dyr (nazwisko, adres, cert)
CREATE TABLE studio (
nazwa CHAR(30)
PRIMARY KEY,
adres VARCHAR(255) ,
prez INT REFERENCES film_dyr(cert)
);
lub
CREATE TABLE studio (
nazwa CHAR(30) PRIMARY KEY,
adres VARCHAR(255),
prez
INT,
FOREIGN KEY prez REFERENCES Film_dyr (cert)
);
Komentarz:
 tabela studio składa się z 3 kolumn.
 w 3 wierszu pierwszym poleceniu tworzenia tabeli jest zdefiniowany klucz obcy
 4 wiersz drugiego polecenia pokazuje inną notacje definiowania klucza obcego.
Ćwiczenie:
W tym punkcie utworzymy dwie tabele: marka_sam oraz model. Tabela marka_sam jest
tabelą nadrzędną w stosunku do tabeli model. Stąd w tabeli model jest zdefiniowany klucz
obcy id_marki.
marka_sam
Id
INT
Klucz
podstawowy
marka
kraj
liczba modeli
VARCHAR (20) VARCHAR (22) INT
NOT NULL
NOT NULL
NOT NULL
model
Id
nazwa
silnik
moc moment cena_srednia id_marki
INT
VARCHAR(20) VARCHAR(1) INT
INT
FLOAT
INT
Klucz
NOT NULL
NOT NULL
NOT
NOT
NOT NULL Klucz obcy
podstawowy
NULL NULL
dla
marka_sam
(id)
Polecenia:
CREATE TABLE marka_sam (
id INT PRIMARY KEY,
marka VARCHAR (20) NOT NULL,
kraj VARCHAR (22) NOT NULL,
liczba_modeli INT NOT NULL
);
CREATE TABLE model ( id INT PRIMARY KEY,
nazwa VARCHAR (20) NOT NULL,
silnik VARCHAR (1) NOT NULL,
moc INT NOT NULL,
moment INT NOT NULL,
cena_srednia FLOAT,
id_marki INT REFERENCES marka_sam (id)
);
lub
CREATE TABLE model ( id INT PRIMARY KEY,
nazwa VARCHAR (20) NOT NULL,
silnik VARCHAR (1) NOT NULL,
moc INT NOT NULL,
moment INT NOT NULL,
cena_srednia FLOAT,
FOREIGN KEY (id_marki) REFERENCES marka_sam (id));
);
Po utworzeniu tabel wykonaj polecenia:
mysql > show tables;
mysql > describe marka_sam;
mysql > describe model;
2.1. Operacja usuwania tabeli – operacja DROP TABLE
Ćwiczenie:
W tym punkcie utworzymy usuniemy z bazy danych wszystkie tabele z wyjątkiem
utworzonych w poprzednim ćwiczeniu: marka_sam oraz model.
Uwaga: Jeśli nie istnieją tabele inne jak powyższe należy utworzyć dowolną tabelę.
wykonaj polecenie - mysql > show tables
wykonaj polecenie - mysql > DROP TABLE nazwa_tabeli
dla każdej wyświetlonej tabeli tabeli wyjątkiem utworzonych w poprzednim ćwiczeniu:
marka_sam oraz model.
wykonaj polecenie - mysql > show tables
3.
Operacja dodawania krotek - INSERT
Opis i przykłady:
Operacja dodawania jest jedną z trzech podstawowych operacji aktualizacji danych w
tabelach:
1. Wstawiania nowych krotek
2. Usuwania pewnych krotek
3. Zmiany wartości pewnych atrybutów w określonych krotkach
INSERT INTO R(A1, A2, ... An) VALUES (V1, V2,...V3)
Przykład: dodanie nowych krotek
INSERT INTO osoba (id, nazwisko, adres, plec, telefon ) VALUES (1, ‘Kot’, ‘XXX’, ‘M’,
‘068111222’);
INSERT INTO osoba VALUES (1, ‘Kowalska’, ‘YYY’, ‘K’, ‘068333444’);
Przykład: Dołączenie szeregu krotek – podzapytanie występuje zamiast klauzuli
VALUES
Studio (nazwa, adres, prezC)
Film (tytuł, rok, długość, nazwaStudia, producentC)
INSERT INTO Studio(nazwa)
SELECT DISTINCT nazwaStudia
FROM Film
WHERE nazwaStudia NOT IN
(SELECT nazwa FROM Studio)
uwaga: Atrybuty adres i prezC są uzupełnione wartościami NULL
Ćwiczenie:
Wprowadzenie danych do tabeli marka_sam
Wykonaj trzy poniższe instrukcje
INSERT INTO marka_sam (id , marka, kraj) VALUES (1, ‘AUDI’, ‘Niemcy’);
INSERT INTO marka_sam (id , marka, kraj) VALUES (2, ‘BMW’, ‘Niemcy’);
INSERT INTO marka_sam (id , marka, kraj) VALUES (3, ‘Fiat’, ‘Włochy’);
lub
INSERT INTO marka_sam (id , marka, kraj) VALUES
(1, ‘AUDI’, ‘Niemcy’),
(2, ‘BMW’, ‘Niemcy’),
(3, ‘Fiat’, ‘Włochy’),
uwaga: druga postać polecenia INSERT może być stosowana w MySQL.
Wprowadzenie danych do tabeli model
INSERT INTO model (nazwa, silnik, moc, moment, cena_srednia, id_marki) VALUES
(‘A6’, ‘B’, 200, 180, 200100, 1);
INSERT INTO model (nazwa, silnik, moc, moment, cena_srednia, id_marki) VALUES
(‘A6’, ‘D’, 180, 390, 200100, 1);
INSERT INTO model (nazwa, silnik, moc, moment, cena_srednia, id_marki) VALUES
(‘A4’, ‘D’, 160, 290, 100100, 1);
INSERT INTO model (nazwa, silnik, moc, moment, cena_srednia, id_marki) VALUES (‘5’,
‘B’, 250, 200, 210100,2);
Zadania:
Wstaw krotki do relacji (tabeli) osoba.
4. Zapytania - SELECT
Zapytania umożliwiają wyszukiwanie danych (informacji) z poszczególnych tabel. W tym
punkcie przeanalizujmy najprostsze zapytania. Podstawowa postać operacji zapytania jest
następująca:
SELECT atrybut_1, atrybut_2, …., atrybut_k
FROM relacja_1, ..., relacja_n
WHERE warunek
Ćwiczenie:
Wykonaj poniższe polecenia:
Operacja projekcji
SELECT id, nazwisko FROM osoba;
Operacja selekcji
SELECT id, nazwisko FROM osoba WHERE plec = ‘K’ ;
SELECT * FROM osoba WHERE plec = ‘K’ ;
SELECT * FROM osoba;
Poniższe przykłady pokazują zastosowanie instrukcji select. Pozwolą i ułatwią realizacje
ćwiczeń przedstawionych w dalszej części instrukcji.
Przykład:
SELECT tytuł AS nazwa, długość AS czasTrwania
FROM Film
WHERE nazwa STUDIA= ‘Disney’ and rok = 1990;
Nawa
czasTrwcmia
Pretty Woman
119
Woman
Przykład:
SELECT tytuł AS nazwa, długość * 0.016667 AS czasWGodzinach
Przykład:
W klauzuli SELECT można również umieszczać stałe. Stosując następujące zapytanie:
SELECT tytuł, długość * 0.016667 AS długość, 'godz.'AS wGodzinach
FROM Film
WHERE nazwaStudia = 'Disney" AND rok = 1990;
Tytuł
długość
wGodzinach
Pretty Woman l.98334
godz.
Do zapisu porównywania wartości w języku SQL służy sześć operatorów: =, < >, <, >, <=,
oraz >=. Ich znaczenie Jest powszechnie znane, jest ono takie same jak w Pascalu (dla
przeciwników Pascala przypominamy, że tam <> oznacza „nierówne'').
W wyrażeniu mogą występować stałe oraz atrybuty tych relacji, które są wymienione w
klauzuli FROM. Wartości numeryczne możemy łączyć w wyrażenia arytmetyczne,
korzystając ze zwyczajowych operatorów +, * itp. Na przykład wartością warunku (rok 1930) * (rok - 1930) < 100 jest prawda, jeśli wartość atrybutu rok oznacza pewien rok z lat
trzydziestych. Z kolei teksty można konkatenować, stosując w tym celu operator ||, na
przykład wyrażenie 'dwie' ||'belki', oznacza to samo co 'dwie-belki'.
Przykład:
SELECT tytuł
FROM Film
WHERE rok < 1 970 AND NOT czyKolor;
SELECT tytuł
FROM Film
WHERE (rok < 1970 OR długość < 90) AND nazwaStudia = 'MGM' ;
Wyrażenie:
s LIKE p
jest porównaniem, w którym jest tekstem, a p pewnym wzorcem, tzn. takim tekstem, w
którym mogą wystąpić szablony, czyli w tym przypadku znaki % oraz _. Inne znaki w napisie
s muszą być dokładnie równe znakom z wzorca p, natomiast szablonowi % z p może
odpowiadać w s dowolny ciąg znaków, także o długości 0; z kolei znakowa _ z wzorca p
odpowiada jeden dowolny znak w tekście s. Wartość tego porównania wynosi prawda
wówczas, gdy s pasuje do wzorca p. Analogicznie jest zdefiniowane wyrażenie s NOT LIKE
P którego wartością jest prawda wówczas, gdy tekst s nie pasuje do wzorca p.
Znak wyjątku w wyrażeniu LIKE
W języku SQL można dowolnego znaku użyć jako znaku wyjątku. Definiuje się go za pomocą słowna
kluczowego ESCAPE, po którym umieszcza się ten wybrany znak otoczony apostrofami. Wówczas,
jeśli we wzorcu znak % lub _ zostanie poprzedzony tym wybranym znakiem wyjątku, to będzie on
traktowany dosłownie jako znak °/o lub _, a niejako szablon. Na przykład sformułowanie
s LIKE 'x%%x% ESCAPE ‘x’
definiuje .Y jako znak wyjątku we wzorcu 'x%%x%'. Wobec tego ciąg x% powoduje, że znak % w tym
miejscu wzorca nie jest szablonem, ale po prostu znakiem %. Z tym wzorcem są zgodne wszystkie
teksty, które zaczynają się od znaku % i kończą na znaku %.
Przykład:
SELECT tytuł
FROM Film
WHERE tytuł LIKE ‘Gwiezdn_ _ _ _ _ _’
Przykład:
Wyszukajmy teraz te angielskojęzyczne tytuły filmów, w których występuje apostrof '.
Zapytanie przybiera wówczas następującą postać:
SELECT tytuł
FROM Film
WHERE tytuł LIKE ‘ %‘’s%’
Przyjęto konwencję, w której występujący w tekście ciąg dwóch bezpośrednio po sobie
następujących apostrofów oznacza znak apostrofu, a nie nawias stałej tekstowej. Dlatego do
zawartego we wzorcu ciągu znaków ‘s pasują te tytuły, w których występuje ciąg znaków 's.
porządkowanie wyniku
ORDER BY <lista atrybutów >
Z założenia porządek jest rosnący, ale można go odwrócić, dopisując na końcu słowo DESC
(„descending - tj. malejący). Można także użyć słowa ASC do określenia porządku rosnącego
(ascending), ale nie jest to konieczne.
PRZYKŁAD
Film (tytuł, rok, długość, czyKolor, nazwaStudia, producentC#)
SELECT *
FROM Film
WHERE nazwaStudia = "Disney" AND rok = 1990
ORDER BY długość, tytuł;
Ćwiczenia:
Wynikiem zapytania mają być nazwy marek pochodzących z Niemiec
SELECT marka FROM marka_sam WHERE kraj = ‘Niemcy’
Wynikiem zapytania mają być nazwy modeli , oraz ich moc i moment modeli dla których
moc cena średnia jest większa niż 150.000
SELECT nazwa, moc, moment FROM model WHERE cena > 1500.000
Wynikiem zapytania mają być wszystkie dane (kolumny) z relacji model opisujące modele
dla których cena średnia jest większa niż 150.000. Wynik zapytania powinien być
uporządkowany według mocy
SELECT * FROM model WHERE cena > 150000 ORDER BY moc
Wynikiem zapytania mają nazwy modeli cena średnia jest większa niż 150.000 oraz moment
wiekszy niż 200. Wynik zapytania powinien być uporządkowany według mocy
SELECT nazwa FROM model WHERE cena > 150000 and moment > 200
Wynikiem zapytania mają nazwy modeli, dla których cena średnia jest większa niż 150.000
oraz moment wiekszy niż 200. Wynik zapytania powinien być uporządkowany według mocy
SELECT nazwa FROM model WHERE cena > 150000 and moment > 200
Wynikiem zapytania mają nazwy marek i modeli, których marki pochodzą z Niemiec
SELECT
marka_sam.marka, model.nazwa FROM mark_sam,
marka_sam.kraj = ‘Niemcy’ and marka_sam.Id = model.Id_marki
model
WHERE
lub
SELECT a.marka, b.nazwa FROM mark_sam a, model b WHERE a.kraj = ‘Niemcy’ and a.Id
= b.Id_marki
Zapytania wtórne i skorelowane
Wynikiem zapytania mają być nazwy modeli, dla których średnia cena modelu jest większa
niż średnia cena wszystkich modeli z Niemiec.
SELECT nazwa FROM model WHERE srednia_cena > all
(SELECT srednia_cena FROM model WHERE kraj = ‘Niemcy’;
Wynikiem zapytania mają być nazwy modeli, jeśli dany model nie jest jedynym model marki
do której należy.
SELECT nazwa FROM model kopia WHERE id > any
(SELECT id FROM model WHERE id_marki = st.id_marki)
Zawansowane zapytania będą realizowane w następnej instrukcji (listy ćwiczeń)
Zadania:
Napisz zapytanie dla tabeli osoba, którego wynikiem będą osoby płci męskiej.
Napisz zapytanie dla tabeli model, które wypisze modele Diesla w cenie nie mniejszej niż
100000.
Napisz zapytanie, które wypisze modele Diesla w cenie nie mniejszej niż 100000 tylko marek
niemieckich.
4. Usuwanie i modyfikacja – DELETE, UPDATE
Usuwanie wybranych krotek
DELETE FROM R WHERE <warunek>
Przykład: dane są relacje Gwiazdy (tytuł, rok, nazwisko), Film (tytuł, rok, długość)
DELETE FROM Gwiazdy
WHERE tytuł = ‘IT’ AND
rok = 1990 AND
nazwisko = ‘IT’;
DELETE FROM Film
WHERE rok < 1990;
Modyfikacja wybranych krotek
UPDATE R SET <nowe przypisania> WHERE <warunek>
UPDATE FilmDyr
SET nazwisko = ‘Prez.’ || nazwisko
WHERE cert IN (SELECT prez FROM STUDIO)
Ćwiczenie:
Usuwanie krotki z relacji model.
Wykonaj polecenie :
DELETE FROM model WHERE moc = ‘160’;
Sprawdzenie realizacji powyższego polecenia:
SELECT * FROM model;
Ćwiczenie:
Aktualizacja wartości atrybutów z relacji marka.
Wykonaj polecenie:
UPDATE model SET moc = ‘185’ WHERE nazwa = ‘A4’
UPDATE model SET moc = ‘185’, moment = ‘245’ WHERE nazwa = ‘A4’
Sprawdzenie realizacji powyższych poleceń:
SELECT * FROM model;
UPDATE marka
SET liczba_modeli = liczba_modeli +1
WHERE id IN (SELECT id_marki FROM model)
SELECT * FROM model;
Zadanie:
Zmodyfikuj wybrane wartości dla danych w tabeli osoba.
4. Modyfikacja schematu relacji (tabeli) – ALTER TABLE
Operacja ALTER TABLE umożliwia modyfikacje schematu (struktury) tabeli.
Ćwiczenia:
ALTER TABLE osoba ADD COLUMN telefon_kom char(9);
Sprawdzenie realizacji powyższego polecenia:
Describe osoba
ALTER TABLE osoba ADD COLUMN data_urodzenia DATE;
Describe osoba
ALTER TABLE osoba CHANGE COLUMN telefon_kom numer_tel_kom char(9);
Describe osoba
ALTER TABLE osoba DROP COLUMN data_urodzenia;
Describe osoba
Sprawdzenie danych w tabeli osoba dla powyższych zmian:
SELECT * FROM osoba
Zadanie:
Zrealizuj powyższe instrukcje dla tabeli model lub marka_sam;