Bazy danych – relacyjny model danych
J zyki zapyta
• j zyk zapyta umo liwia u ytkownikom uzyskanie
danej
informacji z bazy danych
• j zyki zapyta s najcz ciej j zykami wy szego poziomu w porównaniu z j zykami programowania
j zyki zapyta :
• proceduralne – u ytkownik specyfikuje sposób wykonania sekwencji operacji na bazie danych w celu uzyskania danej
informacji
• nieproceduralne – u ytkownik specyfikuje tylko dan informacj
z bazy danych bez podawania procedury uzyskania tej informacji
J zyk baz danych SQL (Structured Query Language)
standardy:
ANSI - (American National Standard Institute)
SQL-92 (SQL2)
SQL-3
funkcje:
DDL – j zyk definicji danych
definiowanie bazy danych oraz struktur tablicowych, indeksy, atrybuty tablic, typy danych, klucze
DML – j zyk zarz dzania danymi
tworzenie, odczyt, modyfikacja, usuwanie atrybutów, krotek, tablic oraz indeksów
DML – j zyk manipulacji danych
tworzenie oraz generowanie zapyta okresowych oraz zapyta ad-hoc
Schemat bazy danych
Film (tytuł, rok, długo , czyKolor, nazwaStudia, producentC#) GwiazdyW (tytułFilmu, rokFilmu, nazwiskoGwiazdy)
GwiazdaFilmowa (nazwisko, adres, płe , dataUrodzenia) FilmDyr (nazwisko, adres, cert#, cenaSieci)
Studio (nazwa, adres, prezC#)
Proste zapytania w j zyku SQL
umo liwiaj wybieranie krotek relacji spełniaj cych okre lony w zapytaniu warunek
FROM - słu y do okre lenia relacji, których dotyczy zapytanie WHERE - warunek, jaki musz spełnia krotki, aby zostały wybrane jako odpowied na zadane pytanie
SELECT - okre la nazwy atrybutów doł czonych do odpowiedzi dla krotek spełniaj cych warunek
Film (tytuł, rok, długo , czyKolor, nazwaStudia, producentC#) GwiazdyW (tytułFilmu, rokFilmu, nazwiskoGwiazdy)
GwiazdaFilmowa (nazwisko, adres, płe , dataUrodzenia) FilmDyr (nazwisko, adres, cert#, cenaSieci)
Studio (nazwa, adres, prezC#)
Jakie filmy wyprodukowano w studiu Disnaya w roku 1990 ?
SELECT *
FROM Film
WHERE nazwaStudia = ‘Disnay’ AND rok=1990; tytuł
rok
długo
czyKolor nazwaStudia
producentC#
Pretty Woman
1990
119
prawda
Disnay
999
Film (tytuł, rok, długo , czyKolor, nazwaStudia, producentC#) GwiazdyW (tytułFilmu, rokFilmu, nazwiskoGwiazdy)
GwiazdaFilmowa (nazwisko, adres, płe , dataUrodzenia) FilmDyr (nazwisko, adres, cert#, cenaSieci)
Studio (nazwa, adres, prezC#)
Jakie filmy wyprodukowano w studiu Disnaya w roku 1990 ? Podaj tytuł
i długo .
SELECT tytuł, długo
FROM Film
WHERE nazwaStudia = ‘Disnay’ AND rok=1990; tytuł
długo
Pretty Woman
119
Film (tytuł, rok, długo , czyKolor, nazwaStudia, producentC#) GwiazdyW (tytułFilmu, rokFilmu, nazwiskoGwiazdy)
GwiazdaFilmowa (nazwisko, adres, płe , dataUrodzenia) FilmDyr (nazwisko, adres, cert#, cenaSieci)
Studio (nazwa, adres, prezC#)
Jakie filmy wyprodukowano w studiu Disnaya w roku 1990 ? Podaj tytuł
i długo . Zmie nazwy kolumn na: nazwa i czasTrwania.
SELECT tytuł AS nazwa, długo
AS czasTrwania
FROM Film
WHERE nazwaStudia = ‘Disnay’ AND rok=1990; nazwa
czasTrwania
Pretty Woman
119
Film (tytuł, rok, długo , czyKolor, nazwaStudia, producentC#) GwiazdyW (tytułFilmu, rokFilmu, nazwiskoGwiazdy)
GwiazdaFilmowa (nazwisko, adres, płe , dataUrodzenia) FilmDyr (nazwisko, adres, cert#, cenaSieci)
Studio (nazwa, adres, prezC#)
Jakie filmy wyprodukowano w studiu Disnaya w roku 1990 ? Podaj tytuł
i długo . Przedstaw czas w godzinach. Dodaj kolumn z jednostk czasu
SELECT tytuł, długo
*0.016667 AS długo , ‘godz:’ AS wGodzinach FROM Film
WHERE nazwaStudia = ‘Disnay’ AND rok=1990; tytuł
długo
wGodzinach
Pretty Woman
1.98334
godz.
Film (tytuł, rok, długo , czyKolor, nazwaStudia, producentC#) GwiazdyW (tytułFilmu, rokFilmu, nazwiskoGwiazdy)
GwiazdaFilmowa (nazwisko, adres, płe , dataUrodzenia) FilmDyr (nazwisko, adres, cert#, cenaSieci)
Studio (nazwa, adres, prezC#)
Filmy czarno-białe wyprodukowane przed rokiem 1970.
SELECT tytuł
FROM Film
WHERE rok<1970 AND NOT czyKolor;
Film (tytuł, rok, długo , czyKolor, nazwaStudia, producentC#) GwiazdyW (tytułFilmu, rokFilmu, nazwiskoGwiazdy)
GwiazdaFilmowa (nazwisko, adres, płe , dataUrodzenia) FilmDyr (nazwisko, adres, cert#, cenaSieci)
Studio (nazwa, adres, prezC#)
Filmy wyprodukowane przez studio MGM przed rokiem 1970 lub trwaj ce krócej ni 90 minut.
SELECT tytuł
FROM Film
WHERE (rok < 1970 OR długo
< 90) AND nazwaStudia=‘MGM’;
Film (tytuł, rok, długo , czyKolor, nazwaStudia, producentC#) GwiazdyW (tytułFilmu, rokFilmu, nazwiskoGwiazdy)
GwiazdaFilmowa (nazwisko, adres, płe , dataUrodzenia) FilmDyr (nazwisko, adres, cert#, cenaSieci)
Studio (nazwa, adres, prezC#)
Filmy wyprodukowane przez studio Disnaya w roku 1990. Posortuj najpierw według czasu trwania, a nast pnie według tytułu.
SELECT *
FROM Film
WHERE nazwaStudia = ‘Disnay’ AND rok = 1990
ORDER BY długo , tytuł;
Porównywanie tekstu
dwa teksty s równe, je li wyst puj w nich kolejno te same znaki np. tekst =tekst
porównania typu: <, >, >=, <=
porównanie kolejno ci alfabetycznej nast puj cych po sobie znaków np: nic < niebo nic < nico
Porównywanie tekstu i wzorca
s LIKE p
s - tekst
p - wzorzec
we wzorcu mog wyst pi szablony:
% - zast puje dowolny ci g znaków
_ - zast puje dowolny (pojedynczy) znak
Film (tytuł, rok, długo , czyKolor, nazwaStudia, producentC#) GwiazdyW (tytułFilmu, rokFilmu, nazwiskoGwiazdy)
GwiazdaFilmowa (nazwisko, adres, płe , dataUrodzenia) FilmDyr (nazwisko, adres, cert#, cenaSieci)
Studio (nazwa, adres, prezC#)
Tytuł filmu składa si z dwóch wyrazów. Pierwszy rozpoczyna si od litery “G” , a druki składa si z 5 znaków
SELECT tytuł
FROM Film
WHERE tytuł LIKE ‘G% _ _ _ _ _’ Gwiezdne Wojny Gwiezdne Wrota
Gwiezdna Droga
Film (tytuł, rok, długo , czyKolor, nazwaStudia, producentC#) GwiazdyW (tytułFilmu, rokFilmu, nazwiskoGwiazdy)
GwiazdaFilmowa (nazwisko, adres, płe , dataUrodzenia) FilmDyr (nazwisko, adres, cert#, cenaSieci)
Studio (nazwa, adres, prezC#)
Zapytania dotycz ce wi cej ni jednej relacji
Podaj dane producenta filmu Gwiezdne Wojny
SELECT nazwisko
FROM Film, FilmDyr
WHERE tytuł = ‘Gwiezdne Wojny’ AND producentC# = cert#; George Lucas
Film (tytuł, rok, długo , czyKolor, nazwaStudia, producentC#) GwiazdyW (tytułFilmu, rokFilmu, nazwiskoGwiazdy)
Gwiazda (nazwisko, adres, płe , dataUrodzenia)
FilmDyr (nazwisko, adres, cert#, cenaSieci)
Studio (nazwa, adres, prezC#)
Atrybuty niejednoznaczne
Podaj pary producentów i gwiazd o tym samym adresie SELECT Gwiazda.nazwisko, FilmDyr.nazwisko
FROM Gwiazda, FilmDyr
WHERE Gwiazda.adres=FilmDyr.adres
Gwiazda.nazwisko
FilmDyr.nazwisko
Jane Fonda
Ted Turner
Agregowanie
•
Tworzenie jednej warto ci z listy warto ci
Operatory agregowania:
1. SUM – oblicza sum wszystkich warto ci w kolumnie 2. AVG - oblicza warto
redni wszystkich warto ci w kolumnie,
3. MIN – oblicza najmniejsz warto
w kolumnie
4. MAX – oblicza najwi ksz warto
w kolumnie
5. COUNT – okre la liczb warto ci wyst puj cych w kolumnie
Film (tytuł, rok, długo , czyKolor, nazwaStudia, producentC#) GwiazdyW (tytułFilmu, rokFilmu, nazwiskoGwiazdy)
Gwiazda (nazwisko, adres, płe , dataUrodzenia)
FilmDyr (nazwisko, adres, cert#, cenaSieci)
Studio (nazwa, adres, prezC#)
rednia cena sieci nale cych do wszystkich prezesów ?
SELECT AVG (cenaSieci)
FROM FilmDyr;
Film (tytuł, rok, długo , czyKolor, nazwaStudia, producentC#) GwiazdyW (tytułFilmu, rokFilmu, nazwiskoGwiazdy)
Gwiazda (nazwisko, adres, płe , dataUrodzenia)
FilmDyr (nazwisko, adres, cert#, cenaSieci)
Studio (nazwa, adres, prezC#)
Ile krotek wyst puje w relacji FilmDyr ?
SELECT COUNT (*)
FROM FilmDyr;
(nie unika zliczania powtarzaj cych si krotek o tym samym nazwisku)
Film (tytuł, rok, długo , czyKolor, nazwaStudia, producentC#) GwiazdyW (tytułFilmu, rokFilmu, nazwiskoGwiazdy)
Gwiazda (nazwisko, adres, płe , dataUrodzenia)
FilmDyr (nazwisko, adres, cert#, cenaSieci)
Studio (nazwa, adres, prezC#)
Ile krotek wyst puje w relacji FilmDyr ?
SELECT COUNT (DISTINCT nazwisko)
FROM FilmDyr;
(unika zliczania powtarzaj cych si krotek o tym samym nazwisku)
Film (tytuł, rok, długo , czyKolor, nazwaStudia, producentC#) GwiazdyW (tytułFilmu, rokFilmu, nazwiskoGwiazdy)
Gwiazda (nazwisko, adres, płe , dataUrodzenia)
FilmDyr (nazwisko, adres, cert#, cenaSieci)
Studio (nazwa, adres, prezC#)
Grupowanie – krotki relacji rozwa ane s w grupach, które zale od
innych kolumn
Ile minut trwaj filmy wyprodukowane przez poszczególne studia ?
SELECT nazwaStudia, SUM(długo )
FROM Film
GROUP BY nazwaStudia;
studio
SUM(długo )
Disnay
12345
MGM
54321
Modyfikacje bazy danych
• Wstawianie nowych krotek do relacji
• Usuwanie pewnych krotek z relacji
• Zmiany warto ci pewnych składowych w okre lonych krotkach
Modyfikacje bazy danych
•
Wstawianie nowych krotek do relacji
Podstawowe elementy instrukcji wstawiania:
1. Słowo kluczowe INSERT INTO,
2. Nazwa relacji R,
3. Lista atrybutów relacji R uj ta w nawiasy, 4. Słowo kluczowe VALUES,
5. Uj ta w nawiasy lista warto ci, po jednej warto ci dla ka dego atrybutu z listy (punkt 3)
INSERT INTO R(A1, A2, ... An) VALUES (v1, ...vn);
Film (tytuł, rok, długo , czyKolor, nazwaStudia, producentC#) GwiazdyW (tytułFilmu, rokFilmu, nazwiskoGwiazdy)
GwiazdaFilmowa (nazwisko, adres, płe , dataUrodzenia) FilmDyr (nazwisko, adres, cert#, cenaSieci)
Studio (nazwa, adres, prezC#)
Do listy gwiazd filmu „Sokół Malta ski” wprowadzamy Sydneya Greenstreeta
INSERT INTO GwiazdyW (tytułFilmu, rokFilmu, nazwiskoGwiazdy) VALUES (‘Sokół Malta ski’, 1942, ‘Sydney Greenstreet’); lub
INSERT INTO GwiazdyW
VALUES (‘Sokół Malta ski’, 1942, ‘Sydney Greenstreet’); Bo: lista atrybutów w cz ci INSERT INTO pokrywa si z list atrybutów relacji
Modyfikacje bazy danych
•
Usuwanie pewnych krotek z relacji
Podstawowe elementy instrukcji usuwania:
1. Słowo kluczowe DELETE FROM,
2. Nazwa relacji R,
3. Słowo kluczowe WHERE,
4. Warunek.
DELETE FROM R WHERE <warunek>;
Film (tytuł, rok, długo , czyKolor, nazwaStudia, producentC#) GwiazdyW (tytułFilmu, rokFilmu, nazwiskoGwiazdy)
GwiazdaFilmowa (nazwisko, adres, płe , dataUrodzenia) FilmDyr (nazwisko, adres, cert#, cenaSieci)
Studio (nazwa, adres, prezC#)
Usun
informacj , e Sydney Greenstreet wyst pował w filmie „Sokół
Malta ski”
DELETE FROM GwiazdyW
WHERE tytułFilmu = ‘Sokół Malta ski’ AND rokfilmu = 1942 AND
nazwiskoGwiazdy = ‘Sydney Greenstreet’;
Film (tytuł, rok, długo , czyKolor, nazwaStudia, producentC#) GwiazdyW (tytułFilmu, rokFilmu, nazwiskoGwiazdy)
GwiazdaFilmowa (nazwisko, adres, płe , dataUrodzenia) FilmDyr (nazwisko, adres, cert#, cenaSieci)
Studio (nazwa, adres, prezC#)
Usun
informacj na temat wszystkich prezesów sieci, których cena jest ni sza od 10 000 000 $
DELETE FROM FilmDyr
WHERE cenaSieci < 10000000;
Modyfikacje bazy danych
•
Zmiany warto ci pewnych składowych w okre lonych krotkach (aktualizacja)
Podstawowe elementy instrukcji aktualizacji:
1. Słowo kluczowe UPDATE,
2. Nazwa relacji R,
3. Słowo kluczowe SET,
4. Lista wyra e , z których ka de okre la warto
pewnego atrybutu
relacji R,
5. Słowo kluczowe WHERE,
6. Warunek.
UPDATE R SET <nowe przypisania> WHERE <warunek>;
Film (tytuł, rok, długo , czyKolor, nazwaStudia, producentC#) GwiazdyW (tytułFilmu, rokFilmu, nazwiskoGwiazdy)
GwiazdaFilmowa (nazwisko, adres, płe , dataUrodzenia) FilmDyr (nazwisko, adres, cert#, cenaSieci)
Studio (nazwa, adres, prezC#)
Poprzedzenie nazwisk producentów tytułem Prez. Je li producent jest jednocze nie prezesem studia
UPDATE FilmDyr
SET nazwisko = ‘Prez.’ || nazwisko
WHERE cert# IN (SELECT prezC# FROM Studio);
Definiowanie schematu relacji
•
Podstawowe typy danych
CHAR (n) – tekst o długo ci n znaków
VARCHAR(n) - tekst o długo ci nie wi kszej ni n znaków INT, INTEGER – liczby całkowite
FLOAT, REAL – liczby rzeczywiste (zmiennopozycyjne) DECIMAL(n,d) – liczby rzeczywiste stałopozycyjne (n cyfr, z czego d po kropce), np. 2121.45 – DECIMAL(6,2)
DATE – data
TIME - czas
Definiowanie schematu relacji
•
Deklaracja tabeli
1. Słowo kluczowe: CREATE TABLE
2. Nazwy relacji z listami nazw i typów atrybutów
CREATE TABLE GwiazdaFilmowa
(nazwisko CHAR(30), adres VARCHAR(255), płe CHAR(1), dataUrodzenia DATE);
Definiowanie schematu relacji
•
Usuwanie tabel
Usuni cie relacji R
Słowo kluczowe: DROP R
Definiowanie schematu relacji
•
Zmiany schematów relacji
Wskazanie modyfikowanej relacji R
ALTER TABLE R
Modyfikowanie kolumn
ADD – dodawanie nowej kolumny (wymaga okre lenia nazwy atrybutu i typu danych)
DROP – usuwanie kolumny (wymaga okre lenia nazwy kolumny) Do relacji GwiazdaFilmowa doda atrybut telefon ALTER TABLE GwiazdaFilmowa ADD telefon CHAR(16);
Definiowanie schematu relacji
•
Zmiany schematów relacji
Wskazanie modyfikowanej relacji R
ALTER TABLE R
Modyfikowanie kolumn
ADD – dodawanie nowej kolumny (wymaga okre lenia nazwy atrybutu i typu danych)
DROP – usuwanie kolumny (wymaga okre lenia nazwy kolumny) Z relacji GwiazdaFilmowa usun
atrybut dataUrodzenia
ALTER TABLE GwiazdaFilmowa DROP dataUrodzenia;
Definiowanie schematu relacji
•
Warto ci domniemane
Słowo kluczowe: DEFAULT - doł czone do deklaracji atrybutu i jego typu
Przykłady warto ci domniemanych: NULL, stała, bie cy czas lub data CREATE TABLE GwiazdaFilmowa (nazwisko CHAR(30), adres VARCHAR(255), płe CHAR(1) DEFAULT ‘?’, dataUrodzenia DATE
DEFAULT ‘0000-00-00’);
ALTER TABLE GwiazdaFilmowa ADD telefon CHAR(16) DEFAULT ‘nieznany’;
Definiowanie schematu relacji
•
Dziedzina – pozwala na okre lenie typu atrybutu; do jednej dziedziny mo e nale e kilka atrybutów (łatwiejsze definiowanie zwi zków mi dzy atrybutami)
Definicja dziedziny:
1. Słowo kluczowe CREATE DOMAIN,
2. Nazwa dziedziny,
3. Słowo kluczowe AS,
4. Typ danych.
CREATE DOMAIN <nazwa> AS <opis typu>; CREATE DOMAIN DziedzinaFilmu AS VARCHAR(50) DEFAULT ‘nieznany’;
Definiowanie schematu relacji
•
Indeks – okre lony na atrybucie relacji pozwala na efektywne wyszukiwanie krotek o ustalonej warto ci składowej atrybutu Okre lenie indeksu:
1. Słowo kluczowe CREATE INDEX,
2. Nazwa indeksu,
3. Słowo kluczowe ON,
4. Nazwa relacji
5. Nazwa atrybutu.
CREATE INDEX IndeksRoku ON Film(rok); CREATE INDEX IndeksKlucza ON Film(tytuł,rok); USUWANIE INDEKSU:
DROP INDEX IndeksRoku;