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 (
[], ... [,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 d" 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
" UNIQUE
" [CONSTRAINT ] CHECK ()
" więzy referencyjne
Ograniczenia dodatkowe
" [CONSTRAINT ] CHECK ()
" 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
ADD ([])
ADD ()
MODIFY ([])
DROP
DROP
DROP CONSTRAINT
Indeksy
i_prac_nazw Pracownicy i_prac_nrz
nazwisko wsk nrp nazwisko nrz nrz wsk
Bukowy " 1 Topolski 1 1 "
Grabski " 2 Jabłoński 2 1 "
Jabłoński " 3 Bukowy 1 2 "
Malina " 4 Malina 3 2 "
Topolski " 5 Grabski 2 3 "
Tworzenie indeksów
CREATE [UNIQUE] INDEX
ON ([ASC|DESC], & )
Przykład
create index i_prac_nazw on pracownicy(nazwisko);
Tworzenie perspektyw (widoków)
CREATE VIEW &
Usuwanie obiektów
DROP TABLE
DROP INDEX
DROP VIEW
Tworzenie perspektyw
Cele tworzenia perspektyw:
a) upraszczanie zapytań

Tworzenie perspektyw
Cele tworzenia perspektyw:
b) ograniczanie dostępu do tabel
Tworzenie perspektyw
CREATE VIEW [()]
AS
[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ą prespektywyzpwtłą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)
set null
Pracownicy (nrp, nazwisko, nrz)
restrict
Wypłaty (nrp, nrt, kwota)
cascade
Tematy (nrt, nazwa, nrpk)
Definiowanie więzów referencyjnych
Klucz główny
a) Ograniczenie kolumny: PRIMARY KEY
b) Ograniczenie dodatkowe: PRIMARY KEY (, & )
Klucz obcy
a) Ograniczenie kolumny:
REFERENCES [()]
b) Ograniczenie dodatkowe:
FOREIGN KEY (, & ) REFERENCES nadrzędna> [(, & )]
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;