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

Bazy danych – SQL

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

Bazy danych – SQL

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#)

Bazy danych – SQL

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

Bazy danych – SQL

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

Bazy danych – SQL

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

Bazy danych – SQL

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

Bazy danych – SQL

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.

Bazy danych – SQL

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;

Bazy danych – SQL

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

Bazy danych – SQL

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

Bazy danych – SQL

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

Bazy danych – SQL

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

Bazy danych – SQL

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

Bazy danych – SQL

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#)

Podaj dane producenta filmu Gwiezdne Wojny

SELECT nazwisko

FROM Film, FilmDyr

WHERE tytuł = ‘Gwiezdne Wojny’ AND producentC# = cert#;

George Lucas

Zapytania dotyczące więcej niż jednej
relacji

Bazy danych – SQL

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#)

Podaj pary producentów i gwiazd o tym samym adresie

SELECT Gwiazda.nazwisko, FilmDyr.nazwisko

FROM Gwiazda, FilmDyr

WHERE Gwiazda.adres=FilmDyr.adres

Atrybuty niejednoznaczne

Gwiazda.nazwisko FilmDyr.nazwisko
Jane Fonda

Ted Turner

Bazy danych – SQL

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

Bazy danych – SQL

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;

Bazy danych – SQL

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)

Bazy danych – SQL

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)

Bazy danych – SQL

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 minut trwają filmy wyprodukowane przez poszczególne
studia ?

SELECT nazwaStudia, SUM(długość)

FROM Film

GROUP BY nazwaStudia;

Grupowanie

– krotki relacji rozważane są w grupach, które

zależą od innych kolumn

studio

SUM(długoś
ć)

Disnay

12345

MGM

54321

Bazy danych – SQL

Modyfikacje bazy danych

•Wstawianie nowych krotek do relacji

•Usuwanie pewnych krotek z relacji

•Zmiany wartości pewnych składowych w

określonych krotkach

Bazy danych – SQL

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

Bazy danych – SQL

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

Bazy danych – SQL

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

Bazy danych – SQL

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

Bazy danych – SQL

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;

Bazy danych – SQL

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

Bazy danych – SQL

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

Bazy danych – SQL

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

Bazy danych – SQL

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

Bazy danych – SQL

Definiowanie schematu relacji

•

Usuwanie tabel

Usunięcie relacji R
Słowo kluczowe: DROP R

Bazy danych – SQL

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

Bazy danych – SQL

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;

Bazy danych – SQL

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

Bazy danych – SQL

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