2. Tworzenie tabel – operacja CREATE TABLE

Wydział Elektrotechniki, Informatyki i Telekomunikacji Instytut Informatyki i Elektroniki

Instrukcja do zajęć laboratoryjnych

Przetwarzanie i organizowanie danych : bazy danych

Nr ćwiczenia 2

Temat Podstawowe operacje języka SQL

Cel ćwiczenia Zapoznanie się operacjami tworzenia bazy danych, tworzenia tabel, operacje aktualizacji tabel: operacja dodawania, usuwania i

modyfikacji. Zapytania proste, złożone (wtórne) skorelowane.

Wymagane teoretyczne przygotowanie Podstawy programowania.

Sposób zaliczenia

Prowadzący dr inż. Paweł Majdzik

1.

1.1.

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 marka kraj liczba modeli

INT VARCHAR (20) VARCHAR (22) INT

Klucz podstawowy

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 podstawowy

NOT NULL NOT NULL NOT

NULL

NOT NULL

NOT NULL Klucz obcy 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.

Ć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 Woman

119 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ść wGodzinac

Pretty Woman l.98334 hgodz.

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.

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ł;

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

Ć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, model WHERE marka_sam.kraj = ‘Niemcy’ and marka_sam.Id = model.Id_marki

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;