h) definiowanie obiektów w schemacie bazy danych
Definiowanie dziedzin
CREATE DOMAIN nazwa_dziedziny [AS] typ_danych
[ DEFAULT default_expr ][ ograniczenie ]
gdzie ograniczenie to:
[ CONSTRAINT nazwa_ograniczenia ] { NOT NULL ��NULL }
DROP DOMAIN nazwa_dziedziny [,...] [CASCADE �� RESTRICT ]
CASCADE  usuwa domenę wraz z kolumnami ją używającymi
RESTRICT  zabrania usuwać domenę, gdy są kolumny ją używające
Przykład:
create domain pesele as char(6) constraint c1 not null;
create domain nazwiska as char(10) constraint c2 not null;
create domain imiona as char(6) constraint c3 null;
112
� dr inż. Witold Rekuć
Definiowanie tabel  struktura tabel
CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE nazwa_tabeli
(
{ nazwa_kolumny typ_danej [ DEFAULT wyrażenie ] [ ograniczenie_kolumnowe [, ... ] ]
| ograniczenie_tablicowe } [, ... ]
)
[ INHERITS ( tabela_rodzic [, ... ] ) ]
[ WITH OIDS | WITHOUT OIDS ]
ograniczenie_tablicowe:
ograniczenie_kolumnowe:
[ CONSTRAINT nazwa_ograniczenia ]
[ CONSTRAINT nazwa_ograniczenia ]
{ UNIQUE ( nazwa_kolumny [, ... ] ) |
{ NOT NULL | NULL | UNIQUE | PRIMARY KEY |
PRIMARY KEY ( nazwa_kolumny [, ... ] ) |
CHECK (wyrażenie) |
CHECK ( wyrażenie ) |
REFERENCES tabela_docelowa
FOREIGN KEY ( nazwa_kolumny [, ... ] )
[ ( kolumna_docelowa ) ] [ MATCH FULL | MATCH
REFERENCES tabela_docelowa
PARTIAL ] [ ON DELETE akcja ] [ ON UPDATE
akcja ] }
[ ( kolumna_docelowa [, ... ] ) ]
[ DEFERRABLE | NOT DEFERRABLE ]
[ MATCH FULL | MATCH PARTIAL ] [ ON DELETE
[ INITIALLY DEFERRED | INITIALLY
akcja ] [ ON UPDATE akcja ] }
IMMEDIATE ]
[ DEFERRABLE | NOT DEFERRABLE ]
[ INITIALLY DEFERRED | INITIALLY IMMEDIATE
]
118
� dr inż. Witold Rekuć
Przykład
create domain pesele as char(6) constraint c1 not null;
create domain nazwiska as char(10) constraint c2 not null;
create domain imiona as char(6) constraint c3 null;
create table osoba
( pesel pesele,
nazwisko nazwiska,
imie imiona
);
create table student
( ukonczona_szkola char(10),
rok_ukonczenia int
) inherits (osoba);
insert into student values ('500329','Reagan','Billy','Podst',1990);
insert into student values ('522529','Klinton','Rony','WestPoint',1992);
insert into student values ('501329','Tacher','Maggy','EastPoint',1994);
insert into student values ('500399','Major','Domus','Srednia',1991);
119
� dr inż. Witold Rekuć
 pesel | nazwisko | imie
--------+------------+--------
500329 | Reagan | Billy
522529 | Klinton | Rony
501329 | Tacher | Maggy
500399 | Major | Domus
(4 rows)
pesel | nazwisko | imie | ukonczona_szkola | rok_ukonczenia
--------+------------+--------+------------------+----------------
500329 | Reagan | Billy | Podst | 1990
522529 | Klinton | Rony | WestPoint | 1992
501329 | Tacher | Maggy | EastPoint | 1994
500399 | Major | Domus | Srednia | 1991
(4 rows)
120
� dr inż. Witold Rekuć
Definiowanie tabel  więzy (ograniczenia) integralnościowe
klucz dodatkowy (canditate)
klucz podstawowy
ograniczenie_kolumnowe:
[ CONSTRAINT nazwa_ograniczenia ]
reguła poprawności
{ NOT NULL | NULL |
UNIQUE |
klucz obcy
PRIMARY KEY |
CHECK (wyrażenie) |
REFERENCES tabela_docelowa [ ( kolumna_docelowa ) ] [ MATCH FULL | MATCH PARTIAL ]
[ ON DELETE akcja ] [ ON UPDATE akcja ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
121
� dr inż. Witold Rekuć
Definiowanie tabel  więzy (ograniczenia) integralnościowe
klucz dodatkowy (canditate)
klucz podstawowy
reguła poprawności
ograniczenie_tablicowe:
[ CONSTRAINT nazwa_ograniczenia ]
klucz obcy
{ UNIQUE ( nazwa_kolumny [, ... ] ) |
PRIMARY KEY ( nazwa_kolumny [, ... ] ) |
CHECK ( wyrażenie ) |
FOREIGN KEY ( nazwa_kolumny [, ... ] ) REFERENCES tabela_docelowa
[ ( kolumna_docelowa [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL ] [ ON DELETE akcja ] [ ON UPDATE akcja ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
122
� dr inż. Witold Rekuć
Przykład
create table Dokumenty
( Numer char(15) not null,
NrSprzed integer not null check(NrSprzed>0),
NrNabywcy integer not null check(NrNabywcy>0),
Data date not null,
constraint CD1 primary key (Numer, NrSprzed),
constraint CD2 check (NrSprzed <> NrNabywcy));
create table Towary
( Symbol char (3) not null constraint Ctw1 primary key,
Nazwa char (30) unique not null,
StawkaVAT real not null check(StawkaVAT>0),
CenaNorm real not null,
JednMiary char(6) not null);
create table Pozycje
( Numer char(15) not null,
NrSprzed integer not null,
Np smallint not null,
SmbTow char(3) not null constraint CP1 references Towary(Symbol),
CenaNetto real not null,
Ilosc real not null,
constraint CP2 primary key(Numer,NrSprzed, Np),
constraint CP4 unique (Numer, NrSprzed, SmbTow),
constraint CP3 foreign key(Numer,NrSprzed) references Dokumenty(Numer, NrSprzed));
123
� dr inż. Witold Rekuć
3. WYSZUKIWANIE DANYCH
SELECT [DISTINCT]
{{ funkcja agregująca | wyrażenie } [AS nazwa kolumny] , ... } |
{ {nazwa tablicy | alias}. * } | *
FROM {{ nazwa tablicy [AS] [alias] [ (nazwa kolumny,... ) ] } |
{ podzapytanie [AS] [alias] [ (nazwa kolumny,... ) ] } |
połączona tablica } , ...
[WHERE wyrażenie logiczne]
GROUP BY {{ [nazwa tablicy | alias }.] nazwa kolumny},...}
[HAVING predykat]
[ { UNION | INTERSECT | EXCEPT } [ALL] CORRESPONDING [BY (nazwa kolumny ,& )]]
Instrukcja select |
{TABLE nazwa tablicy} |
konstruktor tablicy ]
[ORDER BY {{kolumna wyjściowa [ASC ] DESC]},...} ] {{dodatni integer [ASC ] DESC]},...}
124
� dr inż. Witold Rekuć
3. WYSZUKIWANIE DANYCH
SELECT [DISTINCT]
{{ funkcja agregująca |
funkcja agregująca ::= wyrażenie } [AS nazwa kolumny] , ... } |
{ COUNT(*) } | { { AVG | SUM |
{ {nazwa tablicy | alias}. * } | *
MAX | MIN | COUNT }
{DISTINCT | ALL
wyrażenie } } FROM {{ nazwa tablicy [AS] [alias]
[ (nazwa kolumny,... ) ] } |
{ podzapytanie [AS] [alias]
Wyrażenie (algebraiczne) są to
[ (nazwa kolumny,... ) ] } |
kombinacje:
połączona tablica
- literałów,
- odniesień do kolumn,
} , ...
alias - nazwa robocza
- funkcji agregujących,
tablicy, zmienna zakresu
- funkcji łańcuchowych i
[WHERE wyrażenie logiczne]
numerycznych,
- wyrażeń CAST i CASE,
- funkcji użytkownika,
- skalarnych podzapytań,
połączona tablica::=
- zmiennych języka bazowego,
tablica A CROSS JOIN tablica B |
- parametrów,
- nawiasów. tablica A [NATURAL] [ typ złaczenia] JOIN tablica B |
tablica A UNION JOIN tablica B |
tablica A [ typ złaczenia] JOIN tablica B ON wyrażenie logiczne |
podzapytania skalarne
tablica A [ typ złaczenia] JOIN tablica B USING (nazwa
podzapytania wierszowe
kolumny, ...)
podzapytania tablicowe
typ złączenia ::= INNER { LEFT | RIGHT | FULL} [OUTER] }
133
� dr inż. Witold Rekuć
SELECT [DISTINCT]
create table t (a int, b int, c int);
{{ funkcja agregująca |
insert into t values (1,2,3);
wyrażenie } [AS nazwa kolumny] , ... } |
{ {nazwa tablicy | alias}. * } | * insert into t values (4,5,6);
insert into t values (7,8,9);
FROM {{ nazwa tablicy [AS] [alias] [ (nazwa kolumny,... ) ] } |
{ podzapytanie [AS] [alias] [ (nazwa kolumny,... ) ] } |
select * from t;
połączona tablica
} , ...
select t.* from t;
select tt.* from t as tt;
a | b | c
---+---+---
1 | 2 | 3
4 | 5 | 6
7 | 8 | 9
(3 rows)
138
� dr inż. Witold Rekuć
SELECT [DISTINCT]
select a+b+c as abc
{{ funkcja agregująca |
from t;
wyrażenie } [AS nazwa kolumny] , ... } |
abc
{ {nazwa tablicy | alias}. * } | *
-----
FROM {{ nazwa tablicy [AS] [alias] [ (nazwa kolumny,... ) ] } | 6
15
{ podzapytanie [AS] [alias] [ (nazwa kolumny,... ) ] } |
połączona tablica
24
} , ... (3 rows)
select a+b+c as abc, a as aa, b as bb, c as cc
from t;
abc | aa | bb | cc
-----+----+----+----
6 | 1 | 2 | 3
15 | 4 | 5 | 6
24 | 7 | 8 | 9
(3 rows)
142
� dr inż. Witold Rekuć
SELECT [DISTINCT]
{{ funkcja agregująca |
select t1.a, t2.b
wyrażenie } [AS nazwa kolumny] , ... } | from t as t1, t as t2;
{ {nazwa tablicy | alias}. * } | *
a | b
---+---
FROM {{ nazwa tablicy [AS] [alias] [ (nazwa kolumny,... ) ] } |
1 | 2
{ podzapytanie [AS] [alias] [ (nazwa kolumny,... ) ] } |
1 | 5
połączona tablica
1 | 8
} , ...
4 | 2
4 | 5
4 | 8
7 | 2
7 | 5
7 | 8
(9 rows)
144
� dr inż. Witold Rekuć
SELECT [DISTINCT]
{{ funkcja agregująca |
wyrażenie } [AS nazwa kolumny] , ... } |
{ {nazwa tablicy | alias}. * } | *
FROM {{ nazwa tablicy [AS] [alias] [ (nazwa kolumny,... ) ] } |
{ podzapytanie [AS] [alias] [ (nazwa kolumny,... ) ] } |
połączona tablica
} , ...
create table s (a real, b real);
insert into s values (1.0, 2.0);
insert into s values (3.0, 4.0);
select sum(a) as suma, avg(b) as srednia
insert into s values (5.0, 6.0);
from t;
select t2.a/(select sum(t1.a) from s as t1) as udzial
from s as t2;
suma | srednia
udzial
--------+--------------------
----------
12 | 5.0000000000000000
0.111111
(1 row)
0.333333 podzapytanie skalarne
0.555556
(3 rows)
149
� dr inż. Witold Rekuć
SELECT [DISTINCT]
{{ funkcja agregująca |
wyrażenie } [AS nazwa kolumny] , ... } |
{ {nazwa tablicy | alias}. * } | *
FROM {{ nazwa tablicy [AS] [alias] [ (nazwa kolumny,... ) ] } |
{ podzapytanie [AS] [alias] [ (nazwa kolumny,... ) ] } |
połączona tablica
} , ...
select * select e+f+g as efg select e,f,g
from t; from (select a,b,c from t) as tt(e,f,g); from t as tt(e,f,g);
a | b | c efg e | f | g
---+---+--- ----- ---+---+---
1 | 2 | 3 6 1 | 2 | 3
4 | 5 | 6 15 4 | 5 | 6
7 | 8 | 9 24 7 | 8 | 9
(3 rows (3 rows) (3 rows)
152
� dr inż. Witold Rekuć
SELECT [DISTINCT]
{{ funkcja agregująca | wyrażenie } [AS nazwa kolumny] , ... } |
{ {nazwa tablicy | alias}. * } | *
FROM {{ nazwa tablicy [AS] [alias] [ (nazwa kolumny,... ) ] } |
{ podzapytanie [AS] [alias] [ (nazwa kolumny,... ) ] } |
połączona tablica } , ...
[WHERE wyrażenie logiczne]
wyrażenie logiczne ::=
[(] [NOT] { predykat porównania |
predykat BETWEEN |
predykat IN |
predykat LIKE |
predykat NULL |
Predykaty
predykat porównania ilościowego |
predykat EXISTS |
predykat UNIQUE |
predykat MATCH |
predykat OVERLAPS }
[ AND|OR ] wyrażenie logiczne [ ) ]
[ IS [ NOT ] TRUE | FALSE | UNKNOWN ]
153
� dr inż. Witold Rekuć
Predykat porównania
select * from t;
1. Równość i nierówność wartości
2. Równość wartości
a | b | c
skalarnych:
wierszowych:
---+---+---
1 | 2 | 3
select * from t where b > 2 and c = 6 or a < 3;
select * from t where (a,b) = (1,2);
4 | 5 | 6
a | b | c
a | b | c
7 | 8 | 9
---+---+---
---+---+---
(3 rows)
1 | 2 | 3
1 | 2 | 3
4 | 5 | 6
(1 row)
(2 rows)
3. Nierówność wartości wierszowych:
a)
b)
select * from t where (a,b) > (1,2);
select * from t where (a,b) < (1,10);
a | b | c
a | b | c
---+---+---
---+---+---
4 | 5 | 6
(0 rows)
7 | 8 | 9
(2 rows)
d)
c)
select * from t where (a,b) > (1,7);
select * from t where (a,b) < (4,2);
a | b | c
a | b | c
---+---+---
---+---+---
7 | 8 | 9
(0 rows)
(1 row)
160
� dr inż. Witold Rekuć
Predykat BETWEEN
select * from t;
1. Zawieranie się wartości skalarnych w przedziale:
a | b | c
---+---+---
select * from t where a between 2 and 6 or
1 | 2 | 3
b between 1 and 7;
4 | 5 | 6
a | b | c
7 | 8 | 9
---+---+---
(3 rows)
1 | 2 | 3
4 | 5 | 6
(2 rows)
2. Zawieranie się wartości wierszowych w przedziale (SQL92):
select * from t where (a,b) between (2,3) and (6,10) or
(a,b) between (1,9) and (7,20);
a | b | c
---+---+---
4 | 5 | 6
7 | 8 | 9
(1 row)
163
� dr inż. Witold Rekuć
Predykat IN
select * from t;
1. Zawieranie sie wartości skalarnych w zbiorze:
a | b | c
---+---+---
select * from t where a in (1,2,3,4,5);
1 | 2 | 3
a | b | c
4 | 5 | 6
---+---+---
7 | 8 | 9
1 | 2 | 3
(3 rows)
4 | 5 | 6
(2 rows)
select * from s;
a | b
2. Zawieranie sie wartości wierszowych w zbiorze:
---+---
1 | 2
select * from t where (a,b) in (select a,b from s where b<6);
3 | 4
a | b | c
5 | 6
---+---+---
(3 rows)
1 | 2 | 3
(1 row)
167
� dr inż. Witold Rekuć
Predykat LIKE
1. Użycie znaku '%' we wzorcu
select * from klient where nazwa like '%szk%' or nazwa like '%Szk%';
nrklienta | nazwa | adres
-----------+---------------+----------------
select * from klient;
4 | Przedszkole | Przedszkolna 6
nrklienta | nazwa | adres
5 | Szkola | Szkolna 6
-----------+-------------+----------------
(2 rows)
1 | Sklep | Sklepowa 6
2 | Hurtownia | Hurtowa 6
3 | Piekarnia | Piekarska 6
2. Użycie znaku '_' we wzorcu
4 | Przedszkole | Przedszkolna 6
5 | Szkola | Szkolna 6
select * from klient where nazwa like '__r%';
6 | Dolmed SA | Lekarska 6
nrklienta | nazwa | adres
7 | Firma %szk | Firmowa 6
-----------+---------------+-----------
8 | % | Procentowa 8
2 | Hurtownia | Hurtowa 6
(8 rows)
7 | Firma %szk | Firmowa 6
(2 rows)
3. Użycie escape
select * from klient where nazwa like '%'; znaczy tyle co select * from
klient;
select * from klient where nazwa like '/%' escape '/';
nrklienta | nazwa | adres
-----------+-------+--------------
8 | % | Procentowa 8
(1 row)
171
� dr inż. Witold Rekuć
1. Predykat is null
Predykat NULL
select * from Klient where telefon is null;
alter table klient add telefon character(5);
nrklienta | nazwa | adres | telefon
-----------+----------------+-------------------+---------
Table "klient"
4 | Przedszkole | Przedszkolna 6 |
Column | Type | Modifiers
6 | Dolmed SA | Lekarska 6 |
-------------+-----------------+-----------
7 | Firma %szk | Firmowa 6 |
nrklienta | character(4) | not null
8 | % | Procentowa 8 |
nazwa | text | not null
(4 rows)
adres | text | not null
telefon | character(5) |
2. Predykat is not null
Indexes: klient_pkey primary key btree (nrklienta)
select * from Klient where telefon is not null;
update Klient set telefon='22-98' where nrklienta='1';
nrklienta | nazwa | adres | telefon
...
-----------+--------------+---------------+---------
select * from klient;
2 | Hurtownia | Hurtowa 6 | 23-23
nrklienta | nazwa | adres | telefon
3 | Piekarnia | Piekarska 6 | 24-23
-----------+-------------+-------------------+---------
5 | Szkola | Szkolna 6 | 65-12
1 | Sklep | Sklepowa 6 | 22-98
1 | Sklep | Sklepowa 6 | 22-98
2 | Hurtownia | Hurtowa 6 | 23-23
(4 rows)
3 | Piekarnia | Piekarska 6 | 24-23
4 | Przedszkole | Przedszkolna 6 |
3. Zaprzeczenie predykatu is null
5 | Szkola | Szkolna 6 | 65-12
6 | Dolmed SA | Lekarska 6 |
select * from Klient where not telefon is null;
7 | Firma %szk | Firmowa 6 |
nrklienta | nazwa | adres | telefon
8 | % | Procentowa 8 |
-----------+--------------+---------------+---------
(8 rows)
2 | Hurtownia | Hurtowa 6 | 23-23
3 | Piekarnia | Piekarska 6 | 24-23
5 | Szkola | Szkolna 6 | 65-12
1 | Sklep | Sklepowa 6 | 22-98
(4 rows)
178
� dr inż. Witold Rekuć
Predykat porównania ilościowego
select * from t;
1. Predykat z ANY lub SOME (jakiś, pewien)
a | b | c
select * from t where a < ANY (select a from s);
---+---+---
select * from t where a < SOME (select a from s);
1 | 2 | 3
a | b | c
4 | 5 | 6
---+---+---
7 | 8 | 9
1 | 2 | 3
(3 rows)
4 | 5 | 6
(2 rows)
select * from s;
a | b
---+---
1 | 2
3 | 4
2. Predykat bez ANY lub bez SOME
5 | 6
select * from t where a < (select a from s);
(3 rows)
ERROR: More than one tuple returned by a subselect used as an expression.
3. Predykat z ALL (każdy)
select * from t where a > ALL (select a from s);
a | b | c
---+---+---
7 | 8 | 9
(1 row)
183
� dr inż. Witold Rekuć