SQL - historia
SQL - historia
" SQL - "Structured Query Language" - język czwartej
generacji (4GL) wymowa: "es-kju-ell" lub "sequel [si:kwl]"
SQL  język baz danych 
SQL  język baz danych 
" SQL  język baz danych, strukturalny język zapytań, język
przegląd
przegląd
kwerend.
Język do definiowania, wyszukiwania i
administrowania danymi w relacyjnej bazie danej.
Tadeusz Pankowski
www.put.poznan.pl/~tadeusz.pankowski
(c) T. Pankowski, SQL - przegląd1
(c) T. Pankowski, SQL - przegląd2
Standaryzacja - zalety
Standaryzacja - zalety
SQL - historia
SQL - historia
1. 1970, E.F.Codd, "A relational model of data for large shared data
banks", Communications of the ACM, Vol.13, No.6, 1970, podstawy
relacyjnego modelu danych
2. 1974, SEQUEL "Structured English Query Language", D. Chamberlin
" redukcja kosztów szkolenia
i in., IBM San Jose Research Laboratory, implementacja: SEQUEL-
XRM (1974-75)
" przenośność aplikacji
3. 1976-77, SEQUEL/2, implementacja i rozwój w ramach projektu
" wydłużenie żywotności aplikacji
System R, IBM San Jose RL
" komunikacja międzysystemowa, interoperacyjność
4. 1980-83, główne implementacje: ORACLE, DB2 (SQL/DS),
5. 1986, SQL/86 - oficjalny standard ANSI (American National " wygoda dla użytkownika, co robi system, a nie w jaki
Stanmdards Institute) oraz ISO (International Organization for
sposób
Standardization)
" wspólny interfejs do bazy danych
6. 1992, SQL2 (lub SQL/92) - "International Standard ISO/IEC
9075:1992, Database Language SQL"
7. dalszy rozwój: SQL3 (SQL:1999), włącza idee podejścia
obiektowego, dane wielowymiarowe, heterogeniczne, XML, ...
(c) T. Pankowski, SQL - przegląd3 (c) T. Pankowski, SQL - przegląd4
Warstwa
oprogramowania
Oprogramowanie klienta
Standaryzacja - wady
Standaryzacja - wady
klienta
Oprogramowanie standardowe
ADO
zapewniające wymianę danych
" ograniczenie kreatywności
między serwerem i klientem:
(ActiveX Data Objects)
" SQL jest dość daleki od idealnego języka relacyjnego
Architektura aplikacji baz
Architektura aplikacji baz
danych
danych
" braki samego SQL  wiele problemów pozostawiono jako
ODBC
zależnych od implementacji "implementation
(Open Database Connectivity)
dependent", co utrudnia przenośność
" złudzenie istnienia standardu (?)
Warstwa
oprogramowania
serwera
MS SQL
ORACLE Access
Server
(c) T. Pankowski, SQL - przegląd5
SQL  przeznaczenie SQL  postać poleceń
SQL  przeznaczenie SQL  postać poleceń
Zapytania w języku SQL mogą przybierać różną postać. Postać ta może
zależeć od:
SQL = SQL2 = SQL/92
1. Miejsca wydawania polecenia:
Uwaga: Używać będziemy notacji stosowanej w dialekcie języka SQL
" bezpośrednio w trybie interakcyjnym z terminala,
systemu MS SQL Server, a więc języka Transact-SQL.
" w oprogramowaniu serwera,
1. Główne funkcje zarządzania danymi:
" w oprogramowaniu kienta.
" definiowanie
" manipulowanie,
2. Kompletności posiadanej informacji w momencie formułowania
" administrowanie
polecenia:
2. Przeznaczony dla:
" posiadamy pełną informację (w zapytaniu nie ma potrzeby używania
zmiennych),
" interakcyjnych użytkowników końcowych,
" używamy zmiennych do przekazywania i pobierania do/z zapytania
" twórców programów użytkowych (aplikacji),
pewnych parametrów, ale postać zapytania jest ustalona,
" administratorów baz danych,
" używamy zmiennych do dynamicznego tworzenia postaci zapytania, a
" twórców oprogramowania specjalizowanego (narzędziowego,
więc zmienne mogą być zarówno parametry zapytania, jak również jego
systemów współpracujących z bazami danych, ...) struktura.
7 8
Pełne określenie zapytania
Pełne określenie zapytania Zapytania sparametryzowane
Zapytania sparametryzowane
Pracownik(IDPrac, Nazwisko, Placa, Stanowisko)
Pracownik(IDPrac, Nazwisko, Placa, Stanowisko)
Dochod(IDPrac, Rok, Kwota)
Dochod(IDPrac, Rok, Kwota)
Wykorzystanie zmiennych wyjściowych w T-SQL (Transact-SQL):
Wtrybie interakcyjnym, bezpośrednio z terminala
select Nazwisko, Placa
declare @zm_placa money
from Pracownik
select @zm_placa = Placa
where Placa >= 2000
from Pracownik
Wynikiem jest tabela o dwóch kolumnach: Nazwisko i Placa.
where IDPrac = '00123'
Do tabeli wynikowej należą wiersze, w których atrybut Placa
ma wartość większą lub równą 2000.
W pierwszym wierszu deklarowana jest zmienna @zm_placa (nazwa
każdej zmiennej w języku Transact-SQL rozpoczyna się od znaku @) typu
Z wykorzystaniem zmiennnych wierszowych (aliasów):
money.
select P.Nazwisko, D.Rok, D.Kwota
W drugim wierszu pod zmienną @zm_placa podstawiana jest wartość
from Pracownik P, Dochod D
kolumny Placa z ostatniego wiersza stanowiącego zbiór odpowiedzi na
where P.IDPrac = D.IDPrac AND Rok > 1980
zapytanie.
9 10
Zapytania sparametryzowane (c.d.) Zapytania dynamiczne (T-SQL)
Zapytania sparametryzowane (c.d.) Zapytania dynamiczne (T-SQL)
declare @zm_zapytanie varchar(100)
Pracownik(IDPrac, Nazwisko, Placa, Stanowisko)
declare @zm_tabela varchar(100)
Dochod(IDPrac, Rok, Kwota)
declare @zm_kolumny varchar(100)
Wykorzystanie zmiennych wejściowych i wyjściowych (T_SQL):
set @zm_tabela = 'Pracownik'
set @zm_kolumny='Nazwisko, Placa'
declare @IDPrac varchar(30)
set @zm_zapytanie='select ' + @zm_kolumny +  from '+@zm_tabela
declare @zm_placa money
set @IDPrac='00123'
execute sp_sqlexec @zm_zapytanie
select @zm_placa = Placa
from Pracownik
" trzy pierwsze linie zawierają deklaracje zmiennych
where IDPrac = @IDPrac
" w trzech kolejnych liniach następuje określenie wartości zmiennych,
tworzymy:
Wartość zmienna @IDPrac określana jest przed wykonaniem zapytania.
 select Nazwisko, Placa from Pracownik
Zauważmy, że dzięki znakowi @ system rozróżnia czy chodzi o nazwę
" w ostatniej linii następuje wykonanie zapytania, procedury pamiętanej
zmiennej @IDPrac, czy o nazwę kolumny IDPrac.
sp_sqlexec, ktrórej parametr @zm_zapytanie przekazuje tekst
polecenia (zapytania, procedury)
W obydwu powyższych przypadkach struktura zapytania jest znana.
11 12
Zapytania dynamiczne (T-SQL) Zapytania dynamiczne (VB - ADO)
Zapytania dynamiczne (T-SQL) Zapytania dynamiczne (VB - ADO)
" W przypadku zapytań dynamicznych postać zapytania nie
jest znana na etapie kompilacji, w szczególności więc
system nie jest w stanie ani sprawdzić poprawności
składniowej zapytania, ani tym bardziej opracować
optymalnej strategii jego wykonania.
" Czynności te realizowane są dopiero na etapie wykonania,
co istotnie zmniejsza efektywność przetwarzania.
13 14
Definiowanie tabel - przykład
Definiowanie tabel - przykład
Zapytania dynamiczne (VB - ADO)
Zapytania dynamiczne (VB - ADO)
 Podprogram tworzenia tabeli w bieżącej bazie danych
 nazwa tabeli podawana jest w polu tekstowym NazwaTabelii jest wykorzystana
 do dynamicznego utworzenia polecenia SQL-owego CREATE TABLE
-----------------------------------------------------------------------------------------------------------
Private Sub TworzTabele_Click()
Dim c As ADODB.Command
On Error Resume Next
Set c = New ADODB.Command
Set c.ActiveConnection = db
c.CommandText = "Create Table " & NazwaTabeli.Text & " (KolA Char (2),KolB Int)"
c.CommandType = adCmdText
db.Errors.Clear
c.Execute
If db.Errors.Count > 0 Then
WriteError
Else
StatusBar1.SimpleText = "Tabela utworzona."
End If
End Sub
15
Definiowanie tabel  przykład (c.d.)
Definiowanie tabel  przykład (c.d.)
Definiowanie tabel - przykład
Definiowanie tabel - przykład
create table Dostawa (
create table Dostawca (
IdDost int identity(1,1),
NrDcy char(3) primary key,
NrDcy char(3),
Nazwa char(20),
NrMag int,
Adres char(20))
NrTow char(5),
Data datetime,
Ilosc decimal(5),
constraint Kg_Dostawa primary key (IdDost),
create table Magazyn (
constraint Ko_Dostawa_Dostawca
NrMag INT primary key,
foreign key (NrDcy) references Dostawca,
Adres char(20))
constraint Ko_Dostawa_Towar
foreign key (NrTow) references Towar ,
constraint Ko_Dostawa_Magazyn
foreign key (NrMag) references Magazyn)
create table Towar (
NrTow char(5) not null primary key,
create table TowMag (
Nazwa char(20),
NrMag int,
GrupaTow char(3) not null,
NrTow char(5),
CenaZak money,
Stan decimal(5),
CenaSprz money)
primary key (NrMag, NrTow),
foreign key (NrMag) references Magazyn,
foreign key (NrTow) references Towar)
17 18
Operowanie danymi
Operowanie danymi
Definiowanie tabel  przykład (c.d.)
Definiowanie tabel  przykład (c.d.)
1. W powyższych definicjach:
Cztery podstawowe operacje:
" klucz główny składa się z kilku kolumn dlatego jego definicja
podana jest po definicji wszystkich kolumn, np: primary key select - wyszukiwanie,
insert - dołączanie,
(NrMag, NrTow);
update - aktualizacja,
" warunkom spójności bazy danych można nadać nazwy,
delete - usuwanie.
umieszczamy je wtedy we frazie constraint,
" definiujemy zależności referencyjne przez określenie klucza
Przykłady operacji modyfikujących:
obcego foreign key oraz tabeli, od której klucza głównego ten
klucz obcy zależy, np: foreign key (NrMag) references Magazyn
insert into Dostawa
oznacza, że każda wartość wprowadzana do kolumny
values('10','100',1,'1996.11.26',3)
TowMag.NrMag musi występować jako wartość klucza głównego
w tabeli Magazyn. update Towar
set CenaSprz = 1.1 * CenaSprz
2. Za pomocą create table tworzone są tablice bazowe
where GrupaTow = 'AGD'
(ekstensjonalne), mające fizyczną reprezentację w bazie danych oraz
określamy niektóre warunki spójności bazy dnaych.
delete from Towar
3. Tablice wirtualne (intencjonalne), tzw. widoki lub perspektywy (ang.
whereCenaSprz < CenaZak
view) tworzone są za pomocą CREATE VIEW
19 (c) T. Pankowski, SQL - przegląd20
SELECT *
SELECT *
Problemy z SELECT *:
Co zadecydowało o sukcesie systemów relacyjnych baz
1. Wybiera wszystkie kolumny, ale w jakiej
danych?
kolejności? Kolejność kolumn w modelu
relacyjnym nie jest określona. Musimy się
więc odwoływać do poziomu fizycznego
I think there are three elements of the System R prototype and its
określającego sposób pamiętania tabeli. deployment in test environments that had an effect on the eventual adoption
of relational data storage systems.
2. Jeśli za pomocą SELECT * został
zdefiniowany widok, to co się stanie jeśli do
bazowej tabeli dodam nową kolumnę? Ma
być ona włączana do widoku czy nie?
(c) T. Pankowski, SQL - przegląd21 (c) T. Pankowski, SQL - przegląd22
2.
But the real reasons that the relational system won have nothing to do with the
1. relational model; they have to do with the fact that the early prototypes,
both System R and Ingres, supported ad hoc queries and online data
The first one is the obvious one that everybody suggests: the invention of
definition.
a nonprocedural query specification was a tremendous simplification that
made it much easier to specify applications. No longer did you have to say You could try out a query right away, rather than typing it into your application,
which index to use and which join method to use to get the job done. compiling the application, running it, and having a failure, usually a syntax error.
This was a tremendous boon to application development. Even if there were no syntax errors, you still had the advantage that you could
look at the query answers and see if you had the right tuples.
Ad hoc online query execution was something that was unheard of in the
database community at that time, so this was a tremendous boon to
application developers and to people who were browsing the data.
(c) T. Pankowski, SQL - przegląd23 (c) T. Pankowski, SQL - przegląd24
Operowanie danymi
Operowanie danymi
Operacja wyszukiwania:
3.
Maintaining the coherence between the metadata that the system
select distinct W.NrTow, W.NrMag
uses at run time to figure out where things are and which way's up and
from Dostawa W
which way's down and the actual use of the system.
where NrDcy = '10' AND Ilosc > 2
So a difficult and rather successful effort in the System R project was to figure
out ways to synchronize the modification of metadata without
impacting or interrupting the ongoing work involving other data distinct - usuwanie duplikatów
objects.
select W.NrDcy, D.Nazwa, T.Nazwa, T.CenaSprz
from Dostawa W, Dostawca D, Towar T
where W.NrDcy = D.NrDcy AND W.NrTow = T.NrTow
(c) T. Pankowski, SQL - przegląd25 (c) T. Pankowski, SQL - przegląd26
Funkcje agregujące (c.d.)
Funkcje agregujące (c.d.)
Funkcje agregujące
Funkcje agregujące
count, sum, avg, max, min, count(*)
3. Sumaryczny wartość towaru 100 w cenach zakupu:
select sum(T.CenaZak * TM.Stan) as W100
1. Liczba wszystkich dostawców:
from Towar T, TowMag TM
select count(*) as Ld
Ld
where T.NrTow = TM.NrTow AND T.NrTow = '100'
from Dostawca
4
2. Liczba dostawców realizujących dostawy
select count(distinct W.NrDcy) as Ldd
from Dostawa W
Ldd
3
bez distinct:
select count(W.NrDcy) AS Ldd
W100
from Dostawa W
45 000
Ldd
5
(c) T. Pankowski, SQL - przegląd28
Operacje z wykorzystaniem kursora
Operacje z wykorzystaniem kursora
Funkcje agregujące (c.d.)
Funkcje agregujące (c.d.)
4. Wartości towarów w poszczególnych grupach towarowych:
Kursor (ang. cursor) dostarcza mechanizmu dostępu do pojedynczych
wierszy wg zasady "jeden po drugim".
select T.GrupaTow, sum(T.CenaZak * TM.Stan) as Wartość
declare @zmNazwa char(20)
from Towar T, TowMag TM
declare @zmCenaSprz Money
GrupaTow Wartość
where T.NrTow = TM.NrTow
declare kurAGD cursor
----------------- --------------
for select Nazwa, CenaSprz
group by T.GrupaTow KOM 24 000.00
RTV 45 000.00 from Towar
having GrupaTow <> 'AGD'
where GrupaTow = 'AGD'
open kurAGD
select *
fetch next from kurAGD into @zmNazwa, @zmCenaSprz
from Towar T, TowMag TM
while @@FETCH_STATUS = 0
where T.NrTow = TM.NrTow
begin
select @zmNazwa, @zmCenaSprz
fetch next from kurAGD into @zmNazwa, @zmCenaSprz
end
close kurAGD
deallocate kurAGD
(c) T. Pankowski, SQL - przegląd29 30
Operacje z wykorzystaniem kursora Operacje pozycyjne: UPDATE i DELETE
Operacje z wykorzystaniem kursora Operacje pozycyjne: UPDATE i DELETE
declare @zmNazwa char(20)
Aby przetwarzać dane za pomocą kursora należy:
declare @zmCenaSprz money
" zadeklarować kursor DECLARE ... CURSOR i zdefiniować jego zakres
declare kurAGD cursor
FOR SELECT,
for select Nazwa, CenaSprz
" otworzyć kursor, OPEN - wykonywana jest operacja SELECT, kursor
from Towar
wskazuje na miejsce bezpośrednio przed pierwszym wierszem w where GrupaTow = 'AGD'
utworzonym zbiorze wierszy (zakresie kursora), open kurAGD
fetch next from kurAGD into @zmNazwa, @zmCenaSprz
" wykonać operację pobrania danych na kolejnym wierszu FETCH NEXT
while @@FETCH_STATUS = 0
FROM, pobierane dane można zapamiętać pod podanymi zmiennymi,
begin
" pobieranie danych może być realizowane w pętli, różna od zera wartość
select @zmNazwa, @zmCenaSprz
zmiennej (funkcji) globalnej @@FETCH_STATUS może kończyć
update Towar set CenaSprz = CenaSprz + 1 where current of kurAGD
wykonywanie operacji pobierania danych,
fetch next from kurAGD into @zmNazwa, @zmCenaSprz
" zmienne mogą być wykorzystywane w dowolnych operacjach,
end
wyrażenie SELECT @zmNazwa, @zmCenaSprz oznacza wypisanie
close kurAGD
danych,
deallocate kurAGD
" w końcu kursor należy zamknąć CLOSE i dealokować DEALLOCATE.
(c) T. Pankowski, SQL - przegląd31 (c) T. Pankowski, SQL - przegląd32
Widoki (views)
Widoki (views)
Widoki (views)
Widoki (views)
"Towary RTV w cenach sprzedaży "
create view RTV (NrTow, Nazwa, CenaSprz)
" Widoki są tabelami wirtualnymi (intencjonalnymi), nie mającymi
as select T.NrTow, T.Nazwa, T.CenaSprz
from Towar T
bezpośredniej reprezentacji w bazie danych. Zapamiętana jest jedynie
where T.GrupaTow = 'RTV
ich definicja.
Definicja widoku wykorzystywana jest w momencie odwoływania się do
" Definicja widoku wykorzystywana jest w momencie odwoływania się
niego:
do niego jak do każdej innej tabeli.
select RTV.NrTow, RTV.Nazwa, RTV.CenaSprz
from RTV
where RTV.CenaSprz < 2000
Przekształcone jest do zapytania względem tabeli TOWAR:
select RTV.NrTow, RTV.Nazwa, RTV.CenaSprz
from (select T.NrTow, T.Nazwa, T.CenaSprz
from Towar T
where T.GrupaTow = 'RTV' ) RTV
where RTV.CenaSprz < 2000
lub równoważnie:
select T.NrTow, T.Nazwa, T.CenaSprz
from Towar T
where T.GrupaTow = 'RTV' and T.CenaSprz < 2000
(c) T. Pankowski, SQL - przegląd33 (c) T. Pankowski, SQL - przegląd34
Modyfikowalność widoków (c.d.)
Modyfikowalność widoków (c.d.)
Modyfikowalność widoków
Modyfikowalność widoków
W przypadku widoku RTV, jego definicja
1. Jeśli widok jest tak zdefiniowany, że można
create view RTV (NrTow, Nazwa, CenaSprz)
przetransformować operacje jego modyfikowania na
as select T.NrTow, T.Nazwa, T.CenaSprz
modyfikację tabel bazowych, to można go aktualizować.
from Towar T
where T.GrupaTow = 'RTV'
2. Sytuacja taka występuje na przykład wtedy, gdy w
zawiera klucz główny tabeli Towar. Można go więc modyfikować.
definicji kursora zawarto klucz główny tabel bazowych,
Wówczas polecenie:
nad którymi jest definiowany i nie zawiera funkcji
agregujących ani słowa DISTINCT.
update RTV
set CenaSprz = CenaSprz + 10
zostanie wykonane i spowoduje odpowiednią modyfikację tabeli Towar.
(c) T. Pankowski, SQL - przegląd35 (c) T. Pankowski, SQL - przegląd36
Modyfikowalność widoków (c.d.) Modyfikowalność widoków (c.d.)
Modyfikowalność widoków (c.d.) Modyfikowalność widoków (c.d.)
Bardzo często widoki są niemodyfikowalne. 2. Widok Miasta definiuje pary (DAdres, MAdres), gdzie DAdres jest
Przykłady niemodyfikowalnych widoków: adresem dostawcy, a MAdres jest adresem magazynu, do którego
dostawca dostarcza towary:
1. Widok zawierający łączną ilość poszczególnych towarów we
wszystkich dostawach: create view Miasta (DAdres, MAdres)
as select distinct D.Adres, M.Adres
create view TD(NrTow, IlDost) from Dostawca D, Magazyn M, Dostawa W
as select W.NrTow, SUM(W.Ilosc) where D.NrDcy = W.NrDcy and M.NrMag = W.NrMag
from Dostawa W
group by W.NrTow Widok ten jest niemodyfikowalny, gdyż zawiera słowo distinct.
Widok jest niemodyfikowalny, gdyż zawiera funkcję agregującą.
(c) T. Pankowski, SQL - przegląd37 (c) T. Pankowski, SQL - przegląd38
Modyfikowalność widoków (c.d.)
Modyfikowalność widoków (c.d.)
Modyfikowalność widoków (c.d.)
Modyfikowalność widoków (c.d.)
Rozważmy widok jak poprzednio, ale bez słowa distinct:
create view Miasta1 (DAdres, MAdres)
as select D.Adres, M.Adres
from Dostawca D, Magazyn M, Dostawa W
Miasta1
where D.NrDcy = W.NrDcy and M.NrMag = W.NrMag
Dostawca
update Miasta1
set DAdres = 'Kraków'
where DAdres = 'Poznań'
Widok Miasta1 jest modyfikowalny, gdyż zawarte w jego definicji warunki na kluczach głównych
pozwalają dokonać odpowiednich modyfikacji na wyjściowych tabelach
39
Bezpieczeństwo baz danych (c.d.)
Bezpieczeństwo baz danych (c.d.)
Bezpieczeństwo baz danych
Bezpieczeństwo baz danych
Przykład pokazuje, w jaki sposób tworzy się nowego użytkownika i nową rolę w systemie
1. Mechanizmy zapewnienia bezpieczeństwa:
SQL Server oraz w jaki sposób dodaje się użytkownika do istniejącej roli.
" uwierzytelnianie i autoryzacja:
1. Tworzenie nowego konta logowania do systemu MS SQL Server umożliwiającego
użytkownikowi przyłączenie się do serwera:
(nazwa logowania, hasło)
EXEC sp_addlogin 'Piotr', 'piotr',  sklep'
'Piotr' jest nazwą użytkownika, 'piotr' jest hasłem, a  sklep' bazą danych.
użytkownik bazy danych
2. Tworzenie nowego użytkownika (w tym przypadku nazwa użytkownika jest
uprawnienia identyczna z nazwą konta logowania):
EXEC sp_adduser 'Piotr', 'Piotr'
" operacje GRANT/REVOKE/DENY  przyznawanie,
3. Tworzenie nowej roli:
EXEC sp_addrole 'Klient'
odbieranie, odmawiane uprawnień,
4. Dodanie użytkownika do roli:
" wykorzystanie widoków (selektywne udostępnianie
EXEC sp_addrolemember 'Klient', 'Piotr'
5. Zdefiniowanie uprawnień:
danych),
grant SELECT on Pracownik to Klient
" warunki spójności bazy danych
deny SELECT on Pracownik to Piotr
6. Odczytanie informacji o kontach logowania, użytkownikach i rolach (dostępne po
" archiwowanie i odtwarzanie
ponownym uruchomieniu SQL Servera):
sp_helplogins
sp_helpuser
sp_helprole
(c) T. Pankowski, SQL - przegląd41 (c) T. Pankowski, SQL - przegląd42
Rodzaje warunków spójności
Rodzaje warunków spójności
Spójność bazy danych
Spójność bazy danych
1. Typ kolumny  typ może być standardowy lub pochodny zdefiniowany
1. Spójność, niesprzeczność, integralność (ang. consistency, integrity
przez użytkownika.
constraints).
2. UNIQUE  dla określenia, że kolumna lub zestaw kolumn ma mieć
unikalną wartość w tabeli (jest kluczem potencjalnym - alternatywnym,
2. Spójność oznacza poprawność, niesprzeczność danych w bazie
kandydującym  w tabeli), taki klucz może przyjmować wartości NULL.
danych. Standard SQL zawiera środki do definiowania warunków
3. PRIMARY KEY  specjalny przypadek UNIQUE dla zdefiniowania
spójności (lub więzów integralności), np. w obrębie zdania CREATE
klucza głównego  nie może przyjmować wartości NULL.
TABLE.
4. FOREIGN KEY - dla zdefiniowania zależności referencyjnych
3. Każda próba naruszenia zdefiniowanych warunków spójności
(zależności odniesień). Wartość klucza obcego musi występować jako
(podczas modyfikacji bazy danych) zostaje udaremniona, operacja
wartość klucza w powoływanej tabeli.
ją podejmująca jest odrzucana, a baza danych pozostaje nie
5. CHECK - dla określenia, że kolumna lub zestaw kolumn (z jednej krotki)
zmieniona.
mają mieć wartości spełniające określony warunek. Taka spójność
4. W systemie pamiętana jest więc baza danych (dane podstawowe)
może być zdefiniowane jako reguła w wyrażeniu CREATE RULE.
oraz wiedza o bazie danych (dane opisujące struktury i warunki
6. Bardziej złożone warunki spójności można definiować za pomocą
spójności).
procedur wyzwalanych w wyrażeniach CREATE TRIGGER. Procedury
te są automatycznie wyzwalane przy aktualizacji tabeli.
(c) T. Pankowski, SQL - przegląd43 (c) T. Pankowski, SQL - przegląd44
Programowanie SQL Servera Systemowe procedury pamiętane
Programowanie SQL Servera Systemowe procedury pamiętane
Dla wykonywania obliczeń, których nie można wyrazić za pomocą Nazwy wszystkich systemowych procedur pamiętanych rozpoczynają
pojedynczego wyrażenia w języku Transact-SQL (T-SQL), stosuje się od  sp_ lub  xp_ .
się:
Systemowe procedury pamiętana są prekompilowanymi zbiorami
" systemowe procedury pamiętane (system stored procedures), wyrażeń SQL-owych ( sp_ ) lub języka programowanie ( xp_ ).
" paczki (wsady) (batches) i skrypty (scripts), Procedury typu  xp_ (extended stored procedures) są to funkcje
zdefiniowane w bibliotekach dołączanych dynamicznie (bibliotekach
" funkcje (functions),
DLL) i służą do rozszerzenia funkcji SQL Servera. Aby zainstalować
" procedury pamiętane (składowane) (stored procedures),
taką procedurę należy najpierw skopiować plik DLL do katalogu
" procedury wyzwalane (trigery) (triggers),
zawierającego SQL Server  najczęścij jest to C:\Mssql\Binn, a
" kursory (cursors).
następnie wykonać systemową procedurę pamiętaną
sp_addextendedproc.
(c) T. Pankowski, SQL - przegląd45 (c) T. Pankowski, SQL - przegląd46
Systemowe procedury pamiętane - Systemowe procedury pamiętane -
Systemowe procedury pamiętane - Systemowe procedury pamiętane -
przykłady przykłady
przykłady przykłady
Dodanie rozszerzonej procedury pamiętanej:
USE master
Tworzenie typu PESEL opartego na standardowym typie danych varchar. Typ
EXEC sp_addextendedproc xp_hello, 'xp_hello.dll'
nie może przyjmować wartości NULL, varchar(11) jest ujęte w apostrofy, gdyż
zawiera nawiasy:
USE master
Wykonuje podaną komendę systemu operacyjnego i zwraca wyjście jako zbiór
EXEC sp_addtype PESEL, 'VARCHAR(11)', 'NOT NULL wierszy tekstowych:
xp_cmdshell 'dir *.exe'
Odczytanie tekstu procedury wyzwalanej employee_insupd zawartej w bazie
danych pubs:
USE pubs
Tworzenie wyrażenia INSERT:
EXEC sp_helptext 'employee_insupd'
DECLARE @ret_string varchar (255)
Wykonanie prostego wyrażenia SELECT zawierającego wbudowany parametr
EXEC xp_sprintf @ret_string OUTPUT,
@level:
'INSERT INTO %s VALUES (%s, %s)', 'table1', '1', '2'
execute sp_executesql
PRINT @ret_string
N'select * from pubs.dbo.employee where job_lvl = @level',
Wynik:
N'@level tinyint',
INSERT INTO table1 VALUES (1, 2)
@level = 35
(c) T. Pankowski, SQL - przegląd47 48
Paczki (batches) Paczki i skrypty
Paczki (batches) Paczki i skrypty
" Paczka stanowi grupę złożoną z jednej lub z wielu wyrażeń
Przykład skryptu złożonego z dwóch paczek:
Transact-SQLa. Standardowym końcem paczki jest polecenie GO
lub koniec pliku. Paczkę może też stanowić grupa  podświetlonych
USE sklep
wyrażeń (przy korzystaniu z Query Analysera).
GO -- wykonanie paczki, wybór bazy danych sklep
" SQL Server kompiluje paczkę w pojedynczą jednostkę wykonywalną
DECLARE @Powitanie VARCHAR(50)
zwaną planem wykonania (execution plan).
SET @Powitanie = 'Witaj w sklepie!'
" Wyrażenia tworzące plan wykonania wykonywane są jednorazowo
print @Powitanie
w jednym czasie. W przypadku wystąpienia błędu kompilacji, żadne
GO -- wykonanie paczki,
z wyrażeń w paczce nie zostanie wykonane.
-- zmienna @Powitanie po GO nie jest już dostępna.
" Zmienna zdefiniowana w paczce nie jest dostępna poza nią 
zmienne są lokalne w paczce.
" Nie wszystkie wyrażenia mogą występować w jednej paczce (np.
definiowanie i wywoływanie procedury).
(c) T. Pankowski, SQL - przegląd49 (c) T. Pankowski, SQL - przegląd50
Funkcje skalarne
Funkcje skalarne
Funkcje
Funkcje
Funkcja zwraca największy numer pracownika z podanego działu:
Rodzaje funkcji:
CREATE FUNCTION ostatni_prac_dzialu (@id int)
" funkcje skalarne (scalar user-defined functions)  zwracaną
RETURNS int
wartością jest pojedyncza liczba lub tekst, tj. wartość dowolnego
BEGIN
typu z wyjątkiem typów: text, ntext i image;
declare @nr int
" proste funkcje tablicowe (inline table-valued functions)  zwracają
set @nr = (select max(IdPrac) from pracownik
wartość typu TABLE, przy czym zwracana tabela jest określona za
where IdDzialu=@id)
pomocą pojedynczego wyrażenia SELECT (funkcje te nazywane są
RETURN @nr
sparametryzowanymi widokami);
END
" złożone funkcje tablicowe (multi-statement table-valued functions) 
zwracają zmienną typu TABLE, przy czym zwracana tabela jest
Wykorzystanie funkcji:
dowolną tabelą utworzoną w ciele funkcji.
select * from pracownik
where IdPrac = dbo.ostatni_prac_dzialu(1)
(c) T. Pankowski, SQL - przegląd51 (c) T. Pankowski, SQL - przegląd52
Proste funkcje tablicowe Złożone funkcje tablicowe
Proste funkcje tablicowe Złożone funkcje tablicowe
Funkcja zwraca listę dobrze zarabiających pracowników:
Funkcja zwraca zbiór pracowników z działu o podanym identyfikatorze:
create function krezus(@z int)
RETURNS @lista table (id int PRIMARY KEY,
create function prac_dzialu(@id int)
nazwisko varchar(22), dochod int)
RETURNS TABLE
as
as
begin
RETURN(select * from pracownik where IdDzialu=@id)
if @z > 5000
go
insert @lista select id_prac,nazwisko,zarobki
from pracownik1 where zarobki > @z
Wykorzystanie funkcji (wywołanie funkcji występuje w roli tabeli):
RETURN
end
select * from dbo.prac_dzialu(2)
Wykorzystanie funkcji:
lub bez dbo
select * from krezus(5500)
select * from prac_dzialu(2)
select * from krezus(1000)  - tabela pusta
(c) T. Pankowski, SQL - przegląd53 (c) T. Pankowski, SQL - przegląd54
Procedury pamiętane
Procedury pamiętane
Procedury wyzwalane (trigery)
Procedury wyzwalane (trigery)
Procedura oblicza dochód w podanym okresie:
CREATE PROC dochod
@idprac int,
@czas int,
@wynik int OUTPUT -- parametr wyjsciowy Procedury wyzwalane (triggery):
as
" rozszerzają możliwości definiowania warunków spójności,
select @wynik = (pensja + premia)*@czas
" wywoływane są automatycznie w wyniku wykonania operacji:
from pracownik where IdPrac = @idprac
INSERT, UPDATE lub DELETE,
go
Wywołanie: " może specyfikować operację, która wykona się zamiast operacji
declare @c int, @w int
głównej  wariant INSTEAD OF,
select @c = 12
" wykorzystują systemowe tabele INSERTED i/lub DELETED
exec dochod 1,@c,@w output
(INSERTED zawiera dołączane krotki lub nową wersję krotek
select
zmienianych; DELETED zawiera usuwane krotki lub starą wersję
cast(@c as varchar(4))+' miesiecy' as [dochod za okres],
krotek zmienianych),
cast(@w as varchar(11))+' zl' as wynosi
go
" mogą być przypisane do tabeli lub widoku,
Wynik:
" mogą być wywoływane rekurencyjnie.
dochod za okres wynosi
-------------------- --------
12 miesiecy 14400 zl
(c) T. Pankowski, SQL - przegląd56
Procedury wyzwalane  INSTEAD OF
Procedury wyzwalane  INSTEAD OF
Procedury wyzwalane - przykład
Procedury wyzwalane - przykład
Trigger zaznacza w kolumnie del elementy przeznaczone do usunięcia:
Zakaz dopisania pracownika, który zarabia więcej niż jego kierownik:
create trigger zaznacz
on Pracownik
create trigger maks
INSTEAD OF delete as
update Pracownik
on pracownik
set del='*'
for insert as
where nazwisko in (select nazwisko from deleted)
if (select zarobki from inserted) >
go
(select k.zarobki from pracownik k, inserted i
delete from Pracownik
where k.IdPrac = i.IdKier)
where nazwisko like 'K%
begin
rollback
Wynik:
end
nazwisko premia del
-------------------- ----------- ----
Kowalski 200 *
Lipski 200 NULL
Kowal 200 *
Pawlak 200 NULL
(c) T. Pankowski, SQL - przegląd57 58
Kursory Kursory
Kursory Kursory
Składnia SQL-92: Kursor (ang. cursor) dostarcza mechanizmu dostępu do pojedynczych
wierszy wg zasady "jeden po drugim".
DECLARE nazwa_kursora [INSENSITIVE] [SCROLL] CURSOR
declare @zmNazwa char(20)
FOR wyrażenie_select
declare @zmCenaSprz Money
[FOR {READ ONLY | UPDATE [OF nazwa_kolumny [,...n]]}]
declare kurAGD cursor
for select Nazwa, CenaSprz
Składnia Transact-SQL:
from Towar
where GrupaTow = 'AGD'
open kurAGD
DECLARE nazwa_kursora CURSOR
fetch next from kurAGD into @zmNazwa, @zmCenaSprz
[LOCAL | GLOBAL]
while @@FETCH_STATUS = 0
[FORWARD_ONLY | SCROLL]
begin
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]
select @zmNazwa, @zmCenaSprz
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
fetch next from kurAGD into @zmNazwa, @zmCenaSprz
[TYPE_WARNING]
end
FOR wyrażenie_select
close kurAGD
[FOR UPDATE [OF nazwa_kolumny [,...n]]]
deallocate kurAGD
59 60
Kursory (c.d.)
Kursory (c.d.)
Kursory a ADO Recordset
Kursory a ADO Recordset
Aby przetwarzać dane za pomocą kursora należy:
Obiekt Recordset representuje zbiór rekordów z tabeli bazowej lub
" zadeklarować kursor DECLARE ... CURSOR i zdefiniować jego zakres
wynik wykonania polecenia. W każdej chwili czasu obiekt Recordset
FOR SELECT,
wskazuje jeden rekord w zbiorze zwany rekordem bieżącym.
Tworzenie nowego obiektu Recordset:
" otworzyć kursor, OPEN - wykonywana jest operacja SELECT, kursor
wskazuje na miejsce bezpośrednio przed pierwszym wierszem w Set Klient = New ADODB.Recordset
utworzonym zbiorze wierszy (zakresie kursora),
Set Klient.ActiveConnection = db
" wykonać operację pobrania danych na kolejnym wierszu FETCH NEXT
FROM, pobierane dane można zapamiętać pod podanymi zmiennymi,
If Len(StanUSA.Text) = 0 Then
Klient.Source = "Select CustomerId, Name, State From
" pobieranie danych może być realizowane w pętli, różna od zera wartość
Customers"
zmiennej (funkcji) globalnej @@FETCH_STATUS może kończyć
wykonywanie operacji pobierania danych, Else
Klient.Source = "Select CustomerId,Name,State From
" zmienne mogą być wykorzystywane w dowolnych operacjach,
Customers Where State = '" _
wyrażenie SELECT @zmNazwa, @zmCenaSprz oznacza wypisanie
& StanUSA.Text & "'"
danych,
End If
" w końcu kursor należy zamknąć CLOSE i dealokować DEALLOCATE.
(c) T. Pankowski, SQL - przegląd61 (c) T. Pankowski, SQL - przegląd62
Kursory a ADO Recordset
Kursory a ADO Recordset
(c) T. Pankowski, SQL - przegląd63