Tworzenie tabel

 

Instrukcja do zajęć

bazy danych

 

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.

  

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	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.  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 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ść	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, 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;