Techniki operowania danymi

w bazach relacyjnych

Warszawa, 2010

© Witold Marowski, IMRC PW

Politechnika Warszawska, Wydział Samochodów

i Maszyn Roboczych

Studia Inżynierskie, semestr VII

Przegląd zagadnień

• Rodzaje operacji na danych w bazie

relacyjnej

• Operowanie danymi w bazach relacyjnych:

język SQL, technika Query by Example

• Struktura i podstawowe cechy języka SQL
• Kwerendy wyszukujące dane w języku SQL

• Składnia ogólna, warunki wyszukiwania,

porządkowanie zbioru wyników

• Wyszukiwanie danych w pojedynczej tabeli
• Wyszukiwanie danych w kilku tabelach

• Kwerendy agregujące: operacje statystyczne na

danych

• Kwerendy zagnieżdżone

• Wprowadzanie nowych wierszy do tabeli
• Edycja danych w tabeli
• Usuwanie wierszy z tabeli

Podstawowe zasady operowania
danymi

Operacje na danych są dokonywane przy użyciu

odwołań do logicznej struktury relacyjnej bazy

danych

Struktura logiczna

(uporządkowanie

danych)

Operowanie

danymi

Rodzaje operacji na danych

• Wyszukiwanie danych, które spełniają zadane

kryteria wyboru, z jednej lub wielu tabel (



zwracanie zbiorów rekordów

)

• Wstawianie nowych wierszy do tabeli

• Modyfikowanie wartości pól w tych wierszach

tabeli, które spełniają zadane warunki (



operacje na zbiorach wierszy

)

• Usuwanie tych wierszy tabeli, które spełniają

zadane warunki (

 operacje na zbiorach wierszy

)

Język SQL

• Przedmiotowo zorientowany język IV generacji

przeznaczony do obsługi relacyjnych baz
danych

• Historia: początki ok. 1975 (IBM), implementacja

rynkowa 1979 (Oracle), ostatni standard
ANSI/ISO: 1992

• Język nieproceduralny (

co

zrobić, nie

jak

)

• DDL

(Data Definition Language) – język

definiowania danych – definiowanie obiektów bazy
danych oraz określanie powiązań między nimi (np.
definiowanie struktury tabel oraz relacji między
tabelami)

• DML

(Data Manipulation Language) – język

operowania danymi – przeprowadzanie operacji
na danych w istniejącej strukturze logicznej
relacyjnej bazy danych

• Nazwa:

S

tructured

Q

uery

L

anguage

• Podzbiory poleceń języka SQL

Język SQL, cd.

• Polecenia języka SQL

• dowolna wielkość liter

• logika trójwartościowa (

True, False, Unknown

)

• ciągi znakowe zgodne ze składnią języka

• Operatory języka operowania danymi

• dowolny podział na wiersze, brak znaku

kontynuacji

• NULL

– wartość pusta

• łączenia ciągów znakowych (konkatenacji):

&

,

||

• arytmetyczne:

+

,

-

,

*

,

/

• relacji (ogólne):

=

,

<>

,

>

,

>=

,

<

,

<=

• logiczne:

NOT

,

AND

,

OR

• relacji (specyficzne dla SQL):

[

NOT

]

IN (

lista

)

,

[

NOT

]

BETWEEN

WartośćMin

AND

WartośćMax

,

[

NOT

]

LIKE

wzorzec

,

IS

[

NOT

]

NULL

Wyszukiwanie danych przy użyciu
języka SQL

• Instrukcja SELECT – kwerenda wybierająca

• zwraca zbiór rekordów danych pochodzących z

jednej lub wielu tabel

• Składnia ogólna instrukcji SELECT

• rekordy zbioru wyników spełniają zadane

kryteria wyszukiwania

• jeżeli brak jest rekordów spełniających kryteria

wyszukiwania, zbiór wyników jest pusty

SELECT

specyfikacja danych podlegających wyszukiwaniu

FROM

źródło danych

WHERE

kryteria wyszukiwania

ORDER BY

sposób uporządkowania zbioru wyników

Człony

obowiązkowe

Człony

opcjonalne

Wyszukiwanie danych przy użyciu
języka SQL

• Struktura logiczna przykładowej bazy danych

KURSY

Wyszukiwanie danych przy użyciu
instrukcji SELECT języka SQL

• Wybór całej zawartości tabeli

SELECT * FROM

nazwa tabeli

ORDER BY

kolumna1 [DESC][, kolumna2 [DESC], ...]

Określenie źródła

danych i

zwracanych kolumn,

obowiązkowe

Porządkowanie

zbioru

wyników,

opcjonalne

SELECT * FROM Wykladowcy ORDER BY WYK_NAZ

• Wybór niektórych kolumn tabeli

SELECT

lista elementów

FROM

nazwa tabeli

ORDER BY

kolumna1 [DESC][, kolumna2 [DESC], ...]

obowiązkowe

opcjonaln

e

Nazwy kolumn lub wyrażenia zbudowane z ich

użyciem

SELECT WYK_TYTUL, WYK_IMIE, WYK_NAZ FROM Wykladowcy
ORDER BY WYK_NAZ, WYK_IMIE, WYK_TYTUL

Wyszukiwanie danych przy użyciu
instrukcji SELECT języka SQL

• Wybór wierszy z tabeli zgodnie z kryteriami

wyszukiwania

SELECT

lista elementów

FROM

nazwa tabeli

WHERE

kryteria wyszukiwania

ORDER BY

kolumna1

[DESC][,

kolumna2

[DESC], ...]

Nazwy kolumn

lub wyrażenia

zbudowane z

ich użyciem

SELECT KURS_NAZ, KURS_OD, KURS_DO, KURS_OPL FROM Kursy
WHERE KURS_NAZ <> "AutoCAD – kurs wstępny" AND KURS_OPL < 750
AND (KURS_OD >= #03/01/03# OR KURS_DO >= #04/01/03#)
ORDER BY KURS_NAZ, KURS_DO DESC

Wyrażenie logiczne, którego argumentami są

nazwy kolumn tabeli lub wyrażenia zbudowane

przy ich użyciu. Kwerenda zwraca tylko te wiersze

tabeli, dla których wyrażenie logiczne

umieszczone po słowie WHERE

ma wartość PRAWDA (True)

Określenie

źródła

danych i

zwracanych

kolumn,

obo-

wiązkowe

Wyszukiwanie danych przy użyciu
instrukcji SELECT języka SQL

• Zwracanie tylko jednego z identycznych

rekordów zbioru wyników przeszukiwania
źródła danych

SELECT

DISTINCT

lista elementów

FROM

nazwa tabeli

WHERE

kryteria wyszukiwania

ORDER BY

kolumna1 [DESC][, kolumna2 [DESC], ...]

Zbiór wyników takiej kwerendy jest

nieedytowalny, gdyż jeden jego rekord może być

przedstawicielem wielu różnych rekordów źródła

danych

Słowo kluczowe DISTINCT – tylko różne

rekordy w zbiorze wyników działania

kwerendy

SELECT DISTINCT WYK_TYTUL
FROM WYKLADOWCY
ORDER BY WYK_TYTUL

Przykład: lista tytułów wykładowców nie zawierająca powtórzeń

Wyszukiwanie danych przy użyciu
instrukcji SELECT języka SQL

• Przeszukiwanie wielu tabel (źródeł

danych)

• Należy określić warunki złączenia, czyli

sposób, w jaki powinny być kojarzone ze sobą
wiersze danych z poszczególnych obiektów
źródłowych. Zazwyczaj używanym warunkiem
jest żądanie identycznych wartości klucza
obcego oraz jego rodzica, tj.

nazwa kolumny klucza obcego = nazwa kolumny

rodzica

• Trzeba stosować jednoznaczne odwołania do

kolumn źródeł danych podlegających złączeniu,
w razie potrzeby należy używać składni

nazwa obiektu.nazwa kolumny

Brak określenia warunków złączenia: zostaje

zwrócony iloczyn kartezjański wierszy złączanych

obiektów

SELECT

lista elementów z obu złączonych tabel

FROM

nazwa1

INNER JOIN

nazwa2

ON

[nazwa1.]kolumna1

= [nazwa2.]kolumna2

WHERE

kryteria wyszukiwania

ORDER BY

kol_porz1 [DESC][, kol_porz2 [DESC], ...]

Instrukcja SELECT języka SQL

• Przeszukiwanie wielu tabel: złączenie

wewnętrzne

Kolumny

lub wyrażenia

(ew. składnia

obiekt.kolumna

)

Pełne

odwołanie do

kolumny tabeli

źródłowej

(jednoznacznoś

ć)

Określenie źródeł danych i sposobu ich

złączenia. Tutaj: złączenie wewnętrzne dwóch

tabel.

Wartości pól kolumn użytych do zdefiniowania złączenia nie

muszą wchodzić w skład zbioru wyników działania kwerendy.

SELECT PRZ_NAZ, ZAJ_GODZ
FROM PRZEDMIOTY INNER JOIN ZAJECIA
ON PRZEDMIOTY.PRZ_ID = ZAJECIA.PRZ_ID
WHERE KURS_ID = 3
ORDER BY PRZ_NAZ

Złączenie wewnętrzne

tabel PRZEDMIOTY i ZAJECIA

Instrukcja SELECT języka SQL

• Złączenie wewnętrzne więcej niż

dwóch tabel

SELECT KURS_NAZ, PRZ_NAZ,
ZAJ_GODZ FROM

KURSY INNER JOIN

(ZAJECIA INNER JOIN PRZEDMIOTY
ON ZAJECIA.PRZ_ID =
PRZEDMIOTY.PRZ_ID)

ON

KURSY.KURS_ID = ZAJECIA.KURS_ID

ORDER BY KURS_NAZ, PRZ_NAZ

• W członie FROM instrukcji SELECT buduje się

przy użyciu nawiasów kolejne złączenia par tabel
według wzoru:

tabela_A

INNER JOIN (

złączenie tabel B i C przy

użyciu klucza obcego i jego rodzica

) ON

para klucz

obcy – jego rodzic pozwa-lająca na złączenie tabeli A z
parą tabel złączonych w nawiasie

Złączenie tabeli PRZEDMIOTY z tabelą

ZAJECIA

Złączenie tabeli KURSY ze

złączeniem tabel PRZEDMIOTY i

ZAJECIA

Przykład: utworzenie listy kursów wraz z przedmiotami wchodzącymi do ich programów

SELECT PRZ_NAZ, KURS_ID
FROM PRZEDMIOTY LEFT JOIN ZAJECIA
ON PRZEDMIOTY.PRZ_ID = ZAJECIA.PRZ_ID
ORDER BY PRZ_NAZ

Przykład: lista wszystkich proponowanych przedmiotów + ew. numery kursów,

na których te przedmioty są prowadzone

SELECT

lista elementów z obu złączonych tabel

LEFT

FROM

nazwa1

JOIN

nazwa2

RIGHT

ON

[nazwa1.]kolumna1

= [nazwa2.]kolumna2

WHERE

kryteria wyszukiwania

ORDER BY

kol_porz1 [DESC][, kol_porz2 [DESC], ...]

Instrukcja SELECT języka SQL

• Przeszukiwanie wielu tabel: złączenie

zewnętrzne

Kolumny

lub wyrażenia

(ew. składnia

obiekt.kolumna

)

Pełne

odwołanie do

kolumny tabeli

źródłowej

(jednoznacznoś

ć)

Określenie źródeł danych i sposobu ich złączenia

(lewe lub prawe złączenie wewnętrzne dwóch

tabel)

Lewe złączenie zewnętrzne

tabel PRZEDMIOTY i ZAJECIA

Instrukcja SELECT języka SQL

• Kwerendy agregujące – zasada działania

A

A
A

A
A
A

B

B

W1

W1
W1

W1

W2

W2

W2

W3

Wybór:

tylko A

(WHERE)

A
A
A
A
A
A

W1

W1

W2

W2

W2

W3

Źródło danych

Wyniki

wyszukiwani

a

Grupowanie

według wartości

kolumny W

(GROUP BY)

A

W3

A
A

W1
W1

A
A
A

W2
W2

W2

O

b

lic

ze

n

ia

s

ta

ty

s

ty

k

(f

u

n

k

c

je

a

g

re

g

u

ją

c

e

)

Warunki dla grup

(HAVING)

W1 wyniki obliczeń statyst.

W2 wyniki obliczeń statyst.

W3 wyniki obliczeń statyst.

W1 wyniki obliczeń statyst.

W3 wyniki obliczeń statyst.

Ostateczny

zbiór

wyników

Instrukcja SELECT języka SQL

• Kwerendy agregujące – składnia ogólna

SELECT

lista kolumn grupujących i funkcji agregujących

FROM

źródło danych

WHERE

kryteria wyszukiwania rekordów w źródle danych

GROUP BY

lista kolumn grupujących wyniki wyszukiwania

HAVING

warunki dla grup, dla których zwracane są wyniki obliczeń

ORDER BY

kolumny grupujące lub wyniki obliczeń statystycznych

człony opcjonalne, każdy z nich może być

pominięty

SELECT KURS_NAZ, Count(PRZ_ID) AS LiczbaPrzedmiotow
FROM KURSY INNER JOIN ZAJECIA
ON KURSY.KURS_ID = ZAJECIA.KURS_ID
GROUP BY KURS_NAZ
HAVING Count(PRZ_ID) > 2
ORDER BY KURS_NAZ

Przykład: określenie liczby przedmiotów w programach poszczególnych kursów,

jednak z pominięciem kursów, w których programach są mniej niż trzy przedmioty

Instrukcja SELECT języka SQL

• Przegląd funkcji agregujących

COUNT(*) liczba wierszy źródła danych, które spełniają

kryteria wyszukiwania

COUNT(

arg

) liczba różnych od NULL wartości argumentu

(kolumny lub wyrażenia) w spełniających kryteria
wyszukiwania wierszach źródła danych

AVG(

argnum

)

wartość średnia z wartości argumentu numerycznego

(kolumna lub wyrażenie typu numerycznego)
w spełniających kryteria wyszukiwania wierszach
źródła danych

SUM(

argnum

)

suma wartości argumentu numerycznego (kolumny

lub wyrażenia typu numerycznego) w spełniających
kryteria wyszukiwania wierszach źródła danych

MAX(

arg

)

wartość maksymalna argumentu (kolumny lub
wyrażenia dowolnego typu) w spełniających kryteria
wyszukiwania wierszach źródła danych

MIN(

arg

)

wartość minimalna argumentu w zbiorze określonym
tak samo, jak dla funkcji MAX

Instrukcja SELECT języka SQL

• Kwerendy agregujące – warunki WHERE i

HAVING

• W członie HAVING należy zamieszczać tylko te
warunki, których
spełnienie można ocenić dopiero po wykonaniu
obliczeń przez
funkcje agregujące
• Kryteria wyboru rekordów ze źródła danych, które
można
sprawdzić bezpośrednio na podstawie wartości pól
danego
rekordu należy zamieścić w członie WHERE
(zmniejszenie
wielkości zbioru, na którym dokonywane są obliczenia
agregujące)

A

B

A

B

A

B

B

B

A
A
A

Wybór:

tylko A

(WHERE)

Źródło danych

Wyniki wyszukiwania –

zbiór wyjściowy dla

obliczeń agregujących

Instrukcja SELECT języka SQL

• Kwerendy agregujące – przykłady

stosowania warunków WHERE i HAVING

SELECT KURS_NAZ, AVG(KURS_DO – KURS_OD + 1) AS
PrzecietnaLiczbaDni
FROM KURSY
GROUP BY KURS_NAZ
HAVING KURS_NAZ NOT LIKE " *bazy danych* "
ORDER BY KURS_NAZ

Przykład: Określenie przeciętnego czasu trwania kursów (w dniach),

w zależności od nazwy kursu. Ze statystyki wyłączone są kursy

zawierające w nazwie ciąg znakowy "bazy danych"

Ten warunek można
sprawdzić w członie

WHERE

SELECT KURS_NAZ, AVG(KURS_DO – KURS_OD + 1) AS
PrzecietnaLiczbaDni
FROM KURSY
WHERE KURS_NAZ NOT LIKE " *bazy danych* "
GROUP BY KURS_NAZ
ORDER BY KURS_NAZ

Wstępne ograniczenie

liczby rekordów danych

Instrukcja SELECT języka SQL

• Kwerendy zagnieżdżone

SELECT

lista

FROM

źródło danych

WHERE

nazwa kolumny

IN

(SELECT

element

FROM

źródło

danych kwerendy zagnieżdżonej

WHERE

kryteria wyboru

dla kwerendy zagnieżdżonej

)

ORDER BY

lista kolumn porządkujących

kwerenda zagnieżdżona tworzy listę argumentów

operatora IN

• kwerenda zagnieżdżona: ujęta w nawiasy instrukcja

SELECT umieszczona w członie WHERE nadrzędnej
instrukcji SELECT

• kwerenda zagnieżdżona może zawierać następną

kwerendę zagnieżdżoną

• kwerendy zagnieżdżone wykonywane są w pierwszej

kolejności, zaczynając od kwerendy najgłębiej
zagnieżdżonej i określają argument(y) wyrażenia
logicznego w członie WHERE kwerendy nadrzędnej

• przykład składni kwerendy zawierającej kwerendę

zagnieżdżoną:

Instrukcja SELECT języka SQL

• Kwerendy zagnieżdżone - przykład

SELECT DISTINCT ZAJECIA.KURS_ID, KURS_NAZ, KURS_OD
FROM KURSY INNER JOIN ZAJECIA
ON KURSY.KURS_ID = ZAJECIA.KURS_ID
WHERE WYK_ID IN

(SELECT WYK_ID FROM WYKLADOWCY WHERE WYK_TYTUL LIKE "

*dr* ")

ORDER BY KURS_NAZ, KURS_OD

Wybór kursów, dla których przynajmniej jeden z wykładowców ma tytuł,

w skład którego wchodzi ciąg znakowy "dr"

• Zamiast kwerendy zagnieżdżonej lepiej jest

stosować złączenie tabel i warunek WHERE,
jeżeli jest to możliwe, np.:

SELECT DISTINCT ZAJECIA.KURS_ID, KURS_NAZ, KURS_OD
FROM WYKLADOWCY INNER JOIN (KURSY INNER JOIN ZAJECIA
ON KURSY.KURS_ID = ZAJECIA.KURS_ID)
ON WYKLADOWCY.WYK_ID = ZAJECIA.WYK_ID
WHERE WYK_TYTUL LIKE " *dr* "

ORDER BY KURS_NAZ, KURS_OD

Wprowadzanie do tabeli nowego
wiersza

• Instrukcja INSERT języka SQL

INSERT INTO

nazwa tabeli

(

lista nazw kolumn

)

VALUES (

lista wartości pól kolumn w nowym

wierszu tabeli

)

• dotyczy pojedynczego wiersza tabeli
• postać ogólna:

• wartości pól typu innego niż numeryczny należy

ujmować w odpowiednie separatory (np. tekst: "
lub ', data: #)

• w bazach programu Access daty podaje się w formacie

amerykańskim. tj.

mm/dd/rr

(miesiąc/dzień/rok)

• możliwe źródła błędów: naruszenie referencyjnej

integralności danych, naruszenie reguł
poprawności, próba powtórzenia wartości w obrębie
jednoznacznego indeksu

INSERT INTO KURSY (KURS_NAZ, KURS_OD, KURS_DO, KURS_OPL)
VALUES ("Programowanie obrabiarek CNC", #02/03/03#,
#02/07/03#, 650)

... ale wówczas trzeba nadać wartości wszystkim polom

wiersza (zgodnie z ich kolejnością określoną w projekcie

tabeli)

Listę nazw kolumn
można pominąć ...

Modyfikowanie danych w tabeli

• Instrukcja UPDATE języka SQL

UPDATE

nazwa tabeli

SET

nazwa kolumny 1

=

wartość pola 1
[, nazwa kolumny 2 = wartość pola 2, ...]

WHERE

warunki dla rekordów, których pola

podlegają edycji

• dotyczy wszystkich wierszy spełniających podane

w członie WHERE kryteria wyboru

• wartości pól typu innego niż numeryczny należy

ujmować w odpowiednie separatory (np. tekst: "
lub ', data: #)

• postać ogólna:

• możliwe źródła błędów: naruszenie referencyjnej

integralności danych, naruszenie reguł
poprawności, próba powtórzenia wartości w obrębie
jednoznacznego indeksu

UPDATE KURSY SET KURS_OD = KURS_OD + 1
WHERE KURS_OD BETWEEN #04/01/03# AND #04/24/03#

Jeżeli nie podano kryteriów wyboru modyfikowanych

rekordów, edycji zostaną poddane WSZYSTKIE

WIERSZE TABELI !

Wartość

stała

lub

wyrażenie

Usuwanie wierszy z tabeli

• Instrukcja DELETE języka SQL

DELETE * FROM

nazwa tabeli

WHERE

kryteria wyboru rekordów usuwanych z

tabeli

• dotyczy wszystkich wierszy spełniających podane

w członie WHERE kryteria wyboru

• postać ogólna:

• możliwe źródło błędów: naruszenie referencyjnej

integralności danych (próba usunięcia z tabeli
znajdującej się po stronie jeden relacji typu jeden do
wielu takiego rekordu, do którego odwołują się
klucze obce w tabeli po stronie wiele tej relacji)

DELETE * FROM KURSY WHERE KURS_DO <= #12/31/00#

DELETE * FROM WYKLADOWCY WHERE WYK_NAZ = "Kowalski"

Jeżeli nie podano kryteriów wyboru usuwanych

rekordów,

z tabeli zostaną usunięte WSZYSTKIE WIERSZE !

Wykorzystywanie poleceń języka SQL

• Interaktywne

• zażądanie

wykonania
polecenia

• ręczne wpisanie treści polecenia języka SQL w

interfejsie odpowiedniego programu obsługi bazy
danych

• obejrzenie

zwróconego zbioru
rekordów

Wykorzystywanie poleceń języka SQL

• Zagnieżdżenie w kodzie innego języka

programowania

• zastosowanie techniki umożliwiającej

interpretację

i wykonanie wpisanej instrukcji (metody

obiektów tworzonych przez środowisko budowy
aplikacji bazy danych, obiekty dostępu do danych,
prekompilatory)

• wpisanie do kodu programu instrukcji SQL w

postaci

ciągu znaków lub zaprogramowanie tworzenia

takiej instrukcji przez wykonywany segment kodu
odpowiednio do aktualnego kontekstu pracy
programu

• ewentualne wykorzystanie w kodzie aplikacji

zwróconego zbioru danych (zestawy rekordów,
kursory)

SQLText = "DELETE FROM DOK_OBIEKTY WHERE OBPR_ID = " & _
CStr(Me.df_OBPR_ID)
DoCmd.RunSQL SQLText, True

Wykorzystywanie poleceń języka SQL

• Związanie ze zdarzeniami

• umieszczenie instrukcji SQL w procedurze

wykonywanej w reakcji na wystąpienie
określonego zdarzenia

• określenie źródła danych dla formularza

• Związanie z właściwościami obiektu

wchodzącego w skład interfejsu
użytkownika aplikacji

• określenie źródła danych dla formantu

typu lista lub pole kombi (lista rozwijalna)

Interfejs budowy poleceń operowania
danymi

Technika QBE (Query by Example)

Przeciąganie

żądanych

pól myszą

Samochody

ID_Samochodu

Nr rejestracyjny

Opis

Rocznik

Naprawy

ID_Naprawy

Data przyjęcia

Data wydania

ID_Samochodu

1

N

• Praca w graficznym interfejsie użytkownika bazy

danych

• Idea: graficzna budowa wzorcowej postaci wiersza

zbioru wyników wyszukiwania wraz z kryteriami i
sposobem sortowania

• W tle tworzona jest instrukcja SELECT języka SQL

Nr rejestracyjnyRocznik

Data przyjęcia Data wydania

Like ‘WID*’

> 25.10.02

Rosnąco

Rosnąco

Struktura wiersza wyników

Kryteria wyszukiwania

Uporządkowanie wyników

Technika QBE – realizacja w programie
Access

• Interfejs

projektowani
a kwerendy
wybierającej
dane z tabel
bazy

• Utworzona w

tle instrukcja
SELECT

języka SQL

Technika QBE – realizacja w środowisku Visual
Studio

Utworzo
na
instrukc
ja
SELECT

Interfejs
projektowan
ia kwerendy
wybierającej
dane z tabel
bazy

Wynik
wykona
nia
kwerend
y

Techniki operowania danymi

w bazach relacyjnych

Warszawa, 2010

© Witold Marowski, IMRC PW

Politechnika Warszawska, Wydział Samochodów

i Maszyn Roboczych

Studia Inżynierskie, semestr VII