[e-mail] Bazy danych

Bazy danych

Dr inż. Paweł Kasprowski

[e-mail]

Podstawy języka SQL

[e-mail] Bazy danych

Plan wykładu

Plan wykładu

• Relacyjne bazy danych
• Język SQL
• Zapytania SQL (polecenie select)
• Bezpieczeństwo danych
• Integralność danych
• Współbieżność dostępu do danych
• Projektowanie schematu bazy danych (ERD)

[e-mail] Bazy danych

Literatura

Literatura

• Jeffrey D. Ullman, J. Widom

Podstawowy wykład z systemów baz danych
WNT 2001

• Ramez Elmasri, Shamkant B. Navathe

Wprowadzenie do systemów baz danych
Helion 2004

• Marcin Szeliga

ABC języka SQL
Helion 2002

[e-mail] Bazy danych

Sposób przechowywania danych

Sposób przechowywania danych

• Plik – zawiera rekordy (dane pracowników)
• Rekord – składa się z pól (pracownik Kowalski)
• Pole – zawiera pojedynczą wartość (nazwisko, imie,

data urodzenia)

• Pola mogą być różnych typów:

– tekstowe
– numeryczne
– daty i czasu

[e-mail] Bazy danych

Nomenklatura

Nomenklatura

• Plik –> tablica
• Rekord –> wiersz
• Pole –> kolumna

[e-mail] Bazy danych

Przykład

Przykład

• Pracownicy

pensja

dataur

adres

imie

nazwisko

[e-mail] Bazy danych

Dodanie nazwy zespołu

Dodanie nazwy zespołu

• Pracownicy

• Problemy:

– redundancja danych
– problem ze zmianą nazwy zespołu
– zespół bez pracowników nie istnieje
– co jeśli chcemy przechować więcej informacji o zespołach?

pensja

nazwa

dataur

adres

imie

nazwisko

[e-mail] Bazy danych

Przykład

Przykład

• Pracownicy

pensja

nazwa

dataur

adres

imie

nazwisko

[e-mail] Bazy danych

Rozwiązanie

Rozwiązanie

–

–

druga tabela

druga tabela

• Pracownicy

• Zespoły

kierownik

telefon

budynek

nazwa

pensja

dataur

adres

imie

nazwisko

[e-mail] Bazy danych

Problem

Problem

–

–

jak połączyć dwie

jak połączyć dwie

tablice?

tablice?

• Konieczna jest reguła mówiąca który wiersz "pasuje"

do którego

• Każdy wiersz musi więc mieć identyfikator – klucz
• Klucz to zbiór atrybutów unikalnych (różnych) dla

każdego wiersza

• Możemy przechowywać pary kluczy z dwóch tablic w

osobnej strukturze (model sieciowy)

[e-mail] Bazy danych

Model relacyjny

Model relacyjny

• 1971 E.F. Codd – laboratoria IBM
• Tylko dane – brak innych struktur
• Nie ma specjalnych połączeń
• Połączenia wynikają z podobnych wartości atrybutów

w różnych tabelach

• W przypadku związku 1-N wystarczy przechować

klucz jednej tabeli w drugiej

[e-mail] Bazy danych

Realizacja

Realizacja

• Dodajemy kolumnę nazwa zespołu do tablicy

pracowników (czy można odwrotnie?)

• Co jeśli nazwa zespołu nie jest unikalna?
• Odpowiedź:

należy w tablicy pracownicy umieścić klucz a więc
zbiór kolumn zapewniający jednoznaczną
identyfikację zespołu (np. nazwa, budynek, telefon)

• Jest to niewygodne – znowu redundancja – dlatego

często tworzy się specjalną kolumnę kluczową (np.
numer zespołu: idz)

[e-mail] Bazy danych

Przykład

Przykład

• Pracownicy

idz

pensja

dataur

adres

imie

nazwisko

• Zespoły

idz

kierownik

telefon

budynek

nazwa

[e-mail] Bazy danych

Przykład

Przykład

• Pracownicy

idz

pensja

dataur

adres

imie

nazwisko

• Zespoły

idz

kierownik

telefon

budynek

nazwa

[e-mail] Bazy danych

Zalety rozwiązania

Zalety rozwiązania

• Nie ma redundancji
• Łatwo znaleźć zespół w którym pracuje pracownik
• Łatwo znaleźć wszystkich pracowników zespołu
• Zmiana atrybutów zespołu (nie licząc idz!) jest

bezproblemowa

• Baza składa się tylko z tablic – żadnych dodatkowych

struktur

[e-mail] Bazy danych

Język SQL

Język SQL

1972 - Codd, model relacyjny
1974 - baza SYSTEM/R i język SEQUEL
1979 - Relational Software Inc. tworzy system Oracle
1986 - Ingres RDBMS – język QUEL
1989 - standard ANSI SQL (SQL-89)
1992 - ANSI SQL-92 – podstawowy standard

– stał się także standardem ISO

1999 - ANSI SQL-99 (6 części)

[e-mail] Bazy danych

Język SQL

Język SQL

• Język deklaratywny – składa się poleceń
• Elementy języka:

– DDL Data Definition Language

• CREATE, ALTER, DROP

– DML Data Manipulation Language

• INSERT, UPDATE, DELETE

– DQL Data Query Language

• SELECT

– DCL Data Control Language

• GRANT, REVOKE, DENY

[e-mail] Bazy danych

Tworzenie tabeli

Tworzenie tabeli

CREATE TABLE <nazwa>

(<kolumna> <typ> [<ograniczenia>] [,...])

Typy kolumn:
- tekstowe
- numeryczne
- datowe

Pomimo istnienia standardu istnieją różnice

w dostępnych typach dla różnych serwerów

[e-mail] Bazy danych

Najważniejsze typy danych

Najważniejsze typy danych

• Typy tekstowe

– CHAR(n)
– VARCHAR(n)

• Typy numeryczne

– INT, SMALLINT, BIGINT
– DECIMAL(m, n)
– FLOAT, DOUBLE

• Typy datowe

– DATE, DATETIME, TIME, INTERVAL

• Inne typy

– BIT, BYTE, BOOLEAN
– SERIAL, AUTOINCREMENT
– BLOB, TEXT

[e-mail] Bazy danych

Rodzaje ograniczeń

Rodzaje ograniczeń

• [ NOT ] NULL
• DEFAULT <wartość>
• UNIQUE
• CHECK (warunek)
• PRIMARY KEY
• FOREIGN KEY

[e-mail] Bazy danych

Tworzenie tabeli

Tworzenie tabeli

CREATE TABLE prac (

idp int primary key,
nazw varchar(20) not null,
dataur date,
pensja decimal(7,2) default 2000

);

[e-mail] Bazy danych

Modyfikacja tabeli

Modyfikacja tabeli

ALTER TABLE <nazwa> <opis_zmiany>

Przykłady:

ALTER TABLE prac ADD COLUMN idz INT
ALTER TABLE prac MODIFY COLUMN nazw varchar(50)

Składnia może się różnić dla różnych serwerów!

[e-mail] Bazy danych

Usunięcie tabeli

Usunięcie tabeli

DROP TABLE <nazwa_tabeli>

Usuwa tabelę ze wszystkimi danymi

[e-mail] Bazy danych

Praca z danymi

Praca z danymi

• INSERT – wstawienie wiersza/wierszy

INSERT INTO <tabela>[(<kolumny>)]

VALUES (<wartości>)

• UPDATE – modyfikacja wiersza/wierszy

UPDATE <tabela>

SET <kolumna>=<wartość>[, <kolumna>=<wartość>...]
WHERE <warunek>

• DELETE – usunięcie wiersza/wierszy

DELETE FROM <tabela>

WHERE <warunek>

[e-mail] Bazy danych

Wstawianie wierszy

Wstawianie wierszy

CREATE TABLE prac (idp int unique, nazw varchar(20) not null,

dataur date, pensja decimal(7,2) default 2000 );

INSERT INTO prac VALUES(1,'Kowalski','1-1-1980',3000);
INSERT INTO prac(idp,nazw) VALUES(

1

,'Nowak');

-- Błąd: idp nie jest unikalny!

INSERT INTO prac(idp,nazw) VALUES(2,'Nowak');

-- OK: pola dataur=null, pensja=2000

INSERT INTO prac(idp,pensja) VALUES(3,5000);

-- Błąd: nazw nie może być null!

INSERT INTO prac(idp,nazw) <polecenie SELECT>

[e-mail] Bazy danych

Modyfikacja wierszy

Modyfikacja wierszy

UPDATE prac SET nazw='Nowak' WHERE nazw='Kowalski'

-- Uwaga! Zmieni nazwiska WSZYSTKICH Kowalskich!

UPDATE prac SET nazw='Nowak' WHERE idp=8
UPDATE prac SET nazw='Nowak, dataur='1-1-1975' WHERE

pensja > 2000

Warunek WHERE nie jest obowiązkowy – można zmodyfikować
wszystkie wiersze. Można także używać wyrażeń:

UPDATE prac SET pensja=pensja+100

-- globalna podwyżka! – bardzo użyteczne

☺

[e-mail] Bazy danych

Usuwanie wierszy

Usuwanie wierszy

Usuń najlepiej zarabiających:

DELETE FROM prac WHERE pensja>3000

Usuń najstarszych:

DELETE FROM prac WHERE dataur<'1950'

Usuń starych dobrze zarabiających:

DELETE FROM prac WHERE dataur<'1950' AND pensja>3000

Warunek WHERE nie jest obowiązkowy

DELETE FROM prac

-- usuwa wszystkie rekordy z tablicy prac

[e-mail] Bazy danych

Wyszukiwanie danych

Wyszukiwanie danych

• Najczęściej wykonywana operacja (wyrocznia!)
• Polecenie: SELECT
• Możliwości sporej rozbudowy tego polecenia
• Podstawowa składnia:

SELECT <kolumny>
FROM <tablice>
WHERE <warunki>

[e-mail] Bazy danych

Przykłady SELECT

Przykłady SELECT

• Podaj nazwiska i pensje pracowników zakładu nr 5

SELECT nazw,pensja FROM prac WHERE idz=5

-- wynik: tablica w dwóch kolumnach

• Podaj nazwisko pracownika nr 8

SELECT nazw FROM prac WHERE idp=8

-- wynik: jeden wiersz, jedna kolumna

• Zamiast listy kolumn można podać *, WHERE jest

nieobowiązkowe:

SELECT * FROM prac

-- wynik: zawartość całej tablicy prac

[e-mail] Bazy danych

Warunki

Warunki

• Porównania: =,<>,<,>,>=,<=
• [NOT] BETWEEN a AND b
• [NOT] IN (<lista wartości>)
• [NOT] LIKE 'tekst'

% - dowolny ciąg znaków
_ - dowolny znak

• Warunki logiczne: AND, OR

– AND ma wyższy priorytet!

• Wyrażenia algebraiczne (pensja*2>1000)

[e-mail] Bazy danych

Przykłady warunków

Przykłady warunków

select * from prac where idp between 2 and 5
select * from prac where nazw in ('Wolski','Niemczyk')
select * from prac where nazw like 'Kro%'
select * from prac where nazw like 'Kowalsk_'
select * from prac where pensja/1000>2
select * from prac where nazw='Wolski' OR

nazwisko='Niemczyk'

select * from prac where nazw like 'A%' and

pensja=1000 or pensja=3000

[e-mail] Bazy danych

Złączenia w select

Złączenia w select

• Podaj nazwiska pracowników zespołu

'Oprogramowanie'

Pracownicy
idp
nazw
dataur
idz

Zespoły
idz
nazwa
kierownik

Filtr na nazwę

Złączenie

wypisanie
nazwisk

[e-mail] Bazy danych

Złączenia w select

Złączenia w select

• Podaj nazwiska pracowników zespołu

'Oprogramowanie'

select nazwiska
from prac

join

zesp

on prac.idz = zesp.idz

where nazwa= 'Oprogramowanie'

[e-mail] Bazy danych

Złączenia w select

Złączenia w select

• Podaj nazwiska pracowników zespołu

'Oprogramowanie'

select nazwiska
from prac

p

join zesp

z

on

p

.idz =

z

.idz

where nazwa= 'Oprogramowanie'

Jeśli nie ma warunku połączenia – iloczyn kartezjański

(każdy wiersz z każdym)

warunek połączenia

[e-mail] Bazy danych

Dziękuję za uwagę

Dziękuję za uwagę

Do zobaczenia...

materiały dostępne pod adresem:

www.kasprowski.pl