J

ę

zyk SQL (Structured Query Language)

DDL (Data Definition Language)

Wykład

S. Kozielski

J

ę

zyk SQL (Structured Query Language)

1974 IBM

→

SEQUEL

↓

SQL

↓

standaryzacja:

SQL – 86

SQL – 89

SQL – 92 (SQL - 2)

SQL – 99 (SQL - 3)

SQL – 2003

DDL (Data Definition Language)

Tworzenie tabel

CREATE TABLE <tabela> (<kolumna> <typ>

[<ograniczenie kolumny>], ... [,<ograniczenie

dodatkowe>])

Typy danych

Typy znakowe

:

CHARACTER(n), CHAR(n):

ła

ń

cuch znaków o sta

ł

ej d

ł

ugo

ś

ci,

CHARACTER VARYING(n), VARCHAR(n)

:

ła

ń

cuch znaków o

zmiennej d

ł

ugo

ś

ci,

LONG (LONG VARCHAR)

:

ła

ń

cuch znaków

≤

2 GB

CLOB –

słu

ż

y do przechowywania danych znakowych o du

ż

ych rozmiarach

BLOB -

słu

ż

y do przechowywania danych binarnych o du

ż

ych rozmiarach

Typy numeryczne

dokładne:

NUMERIC(p,q)

p – precyzja (ca

ł

kowita liczba cyfr)

DECIMAL(p,q), DEC(p,q)

q – skala (liczba cyfr cz

ęś

ci u

ł

amk.)

INTEGER, INT

SMALLINT

przybliżone:

FLOAT(p)

REAL

DOUBLE PRECISION

Typ Data i czas

DATE

: rrrr-mm-dd

TIME

: hh-mm-ss:u

ł

amki sekund

TIMESTAMP

DATETIME

INTERVAL

Inne typy

BIT(n)

–

ł

a

ń

cuch n-bitów

BYTE

-

ł

a

ń

cuch 8-bitów

BOOLEAN

– true/false

MONEY

SERIAL

...

Ograniczenia kolumny

• NOT NULL

• DEFAULT <warto

ść

domy

ś

lna>

• UNIQUE

• [CONSTRAINT <nazwa ogranicz.>] CHECK (<warunek>)

• wi

ę

zy referencyjne

Ograniczenia dodatkowe

•[CONSTRAINT <nazwa ogranicz.>] CHECK (<warunek>)

•wi

ę

zy referencyjne

Przyk

ł

ad: definiowanie tabeli o strukturze:

uczniowie (nazwisko, wzrost, waga, klasa)

create table uczniowie ( nazwisko varchar(15)

not null, wzrost int constraint max_min

check (wzrost > 70 and wzrost < 280),

waga dec(5,2) check (waga < 180),

klasa smallint);

Modyfikowanie struktury tabeli

ALTER TABLE <tabela>

ADD (<kolumna><typ>[<ogranicz. kolumny>])

ADD (<ograniczenie dodatkowe>)

MODIFY (<kolumna><typ>[<ogran. kolumny>])

DROP <kolumna>

DROP <ograniczenie dodatkowe>

DROP CONSTRAINT <ograniczenie kolumny>

Indeksy

i_prac_nazw

Jabło

ń

ski

Grabski

Bukowy

nazwisko

Topolski

Malina

•

•

•

•

•

wsk

Pracownicy

Malina

Bukowy

Jabło

ń

ski

Topolski

nazwisko

Grabski

5

4

3

2

1

nrp

3

1

2

1

nrz

2

2

2

1

1

nrz

3

wsk

•

•

•

•

•

i_prac_nrz

Tworzenie indeksów

CREATE [UNIQUE] INDEX <indeks>

ON <tabela> (<kolumna>[ASC|DESC], …)

Przykład

create index i_prac_nazw on pracownicy(nazwisko);

Tworzenie perspektyw (widoków)

CREATE VIEW <perspektywa> …

Usuwanie obiektów

DROP TABLE <tabela>

DROP INDEX <indeks>

DROP VIEW <perspektywa>

Tworzenie perspektyw

Cele tworzenia perspektyw:
a) upraszczanie zapyta

ń





Tworzenie perspektyw

Cele tworzenia perspektyw:
b) ograniczanie dost

ę

pu do tabel

Tworzenie perspektyw

CREATE VIEW <perspektywa> [(<lista nazw kolumn>)]

AS <instrukcja SELECT>

[WITH CHECK OPTION]

Tworzenie przykładowej perspektywy

zpwt

create view zpwt (nrz, nazwa_z, nrpk_z, nrp, nazwisko,

nrt, nazwa_t, nrpk_t, kwota)

as select z.nrz, z.nazwa, z.nrpk, p.nrp, nazwisko,

t.nrt, t.nazwa, t.nrpk, kwota

from zespoły z, pracownicy p, wypłaty w, tematy t

where z.nrz = p.nrz and p.nrp = w.nrp and w.nrt = t.nrt

Przykłady wykorzystania perspektywy

zpwt

select distinct nazwa_z

from zpwt

where nazwa_t = ‘Projekt sterownika’

select nazwa_z, nazwa_t, sum(kwota)

from zpwt

group by nazwa_z, nazwa_t

Wykorzystanie perspektywy

zpwt -

problem

•

Rozwa

ż

my zapytanie

:

select nazwisko, nazwa_z

from zpwt

•

W tym zapytaniu potrzebne s

ą

2 tabele, ale procesor zapyta

ń

zgodnie z definicj

ą

prespektywy

zpwt

ł

ą

czy ze sob

ą

4 tabele i

rezultacie wyst

ą

pi efekt „obcinania krotek zwisaj

ą

cych”.

•

W rezultacie w odpowiedzi na powy

ż

sze pytanie nie pojawi

ą

si

ę

np. nazwiska pracowników, którzy nie mieli

ż

adnych wypłat

Wykorzystanie perspektyw do aktualizacji tabel

Niedopuszczalne w definicji perspektyw:

- fraza GROUP,

- wyra

ż

enia, funkcje agreg., DISTINCT na li

ś

cie SELECT,

- fraza UNION,

- zł

ą

czenia – z wyj

ą

tkami, np. SQL Server (o ile aktualizacja

dotyczy 1 tabeli).

Rola frazy CHECK OPTION

create view sekretariat_1

as select nrp, nazwisko, nrz

from pracownicy

where nrz = 1

with check option

select *

from sekretariat_1

insert into sekretariat_1 values (12,’Sosna’,1)

insert into sekretariat_1 values (13,’D

ę

bski’,2)

Wi

ę

zy referencyjne – ochrona integralno

ś

ci

bazy danych

Klucze g

ł

ówne (PRIMARY KEY)

Kolumna lub najmniejszy zestaw kolumn, których warto

ś

ci

jednoznacznie identyfikuj

ą

ka

ż

dy wiersz.

Wymagania dla klucza g

ł

ównego:

- mo

ż

e by

ć

tylko 1 klucz główny w tabeli,

- klucz główny musi mie

ć

warto

ś

ci unikalne i niepuste.

Wskazanie kluczy głównych

Zespoły (nrz, nazwa, nrpk)

Pracownicy (nrp, nazwisko, nrz)

Wypłaty (nrp, nrt, kwota)

Tematy (nrt, nazwa, nrpk)

Klucze obce (FOREIGN KEY)

Kolumna lub zestaw kolumn, które tworz

ą

logiczne powi

ą

zanie z

kluczem głównym jakiej

ś

tabeli (nadrz

ę

dnej)

Wymagania dla kluczy obcych:

- definicja klucza obcego musi odpowiada

ć

definicji klucza głównego

istniej

ą

cej ju

ż

tabeli nadrz

ę

dnej,

- niepustej warto

ś

ci klucza obcego musi odpowiada

ć

istniej

ą

ca

warto

ść

klucza g

ł

ównego,

- dopuszcza si

ę

warto

ś

ci puste kluczy obcych.

Wskazanie kluczy obcych

Zespoły (nrz, nazwa, nrpk)

Pracownicy (nrp, nazwisko, nrz)

Wypłaty (nrp, nrt, kwota)

Tematy (nrt, nazwa, nrpk)

Ograniczenia na usuwanie wierszy

-

restrykcyjne (RESTRICT, NO ACTION) –

usuni

ę

cie wiersza

nadrz

ę

dnego nie jest mo

ż

liwe, je

ś

li istniej

ą

wiersze podrz

ę

dne (logicznie

z nim powi

ą

zane),

- z wstawianiem warto

ś

ci pustych (SET NULL) – usuni

ę

cie wiersza

nadrz

ę

dnego powoduje automatyczne wstawienie warto

ś

ci pustych w

miejsce kluczy obcych wierszy podrz

ę

dnych,

- kaskadowe (CASCADE) – usuni

ę

cie wiersza nadrz

ę

dnego powoduje

automatyczne usuni

ę

cie wierszy podrz

ę

dnych (logicznie z nim

powi

ą

zanych).

Nało

ż

enie ogranicze

ń

na usuwanie danych

Zespoły (nrz, nazwa, nrpk)

Pracownicy (nrp, nazwisko, nrz)

Wypłaty (nrp, nrt, kwota)

Tematy (nrt, nazwa, nrpk)

set null

cascade

restrict

Definiowanie wi

ę

zów referencyjnych

Klucz główny

a) Ograniczenie kolumny: PRIMARY KEY

b) Ograniczenie dodatkowe: PRIMARY KEY (<kolumna>, …)

Klucz obcy

a) Ograniczenie kolumny:

REFERENCES <tabela nadrz

ę

dna> [(<kolumna>)]

b) Ograniczenie dodatkowe:

FOREIGN KEY (<kolumna>, …) REFERENCES <tabela

nadrz

ę

dna> [(<kolumna>, …)]

Ograniczenia na usuwanie i modyfikacj

ę

wierszy

ON DELETE { NO ACTION | RESTRICT |

CASCADE |

SET NULL | SET DEFAULT }

ON UPDATE { NO ACTION | RESTRICT |

CASCADE |

SET NULL | SET DEFAULT }

create table zespoły (nrz int

primary key

, nazwa varchar(30),

nrpk int);

create table pracownicy (nrp int

primary key

, nazwisko

varchar(20) not null,nrz int

references zespoły on delete

set null);

create table tematy (nrt int

primary key

, nazwa varchar(50),

nrpk int);

create table wypłaty (nrp int

references pracownicy

, nrt int

references tematy on delete cascade

,kwota dec(8,2),

primary key (nrp, nrt

));

Dodatkowe wi

ę

zy – wersja Oracle:

alter table zespoły add (foreign key(nrpk) references

pracownicy on delete set null);

alter table tematy add (foreign key(nrpk) references

pracownicy on delete set null);

Wersja MS SQLServer:

alter table zespoły add foreign key(nrpk) references

pracownicy on delete set null;

alter table tematy add foreign key(nrpk) references

pracownicy on delete set null;