SQL – język baz danych – przegląd

SQL - historia
• SQL - "Structured Query Language" - język czwartej
generacji (4GL) wymowa: "es-kju-ell" lub "sequel [si:kwl]"
• SQL – język baz danych, strukturalny język zapytań, język
kwerend.
SQL – język baz danych –
przegląd
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ąd
1
(c) T. Pankowski, SQL - przegląd
Standaryzacja - zalety
SQL - historia
1.
2.
3.
4.
5.
6.
7.
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
1974, SEQUEL "Structured English Query Language", D. Chamberlin
i in., IBM San Jose Research Laboratory, implementacja: SEQUELXRM (1974-75)
1976-77, SEQUEL/2, implementacja i rozwój w ramach projektu
System R, IBM San Jose RL
1980-83, główne implementacje: ORACLE, DB2 (SQL/DS),
1986, SQL/86 - oficjalny standard ANSI (American National
Stanmdards Institute) oraz ISO (International Organization for
Standardization)
1992, SQL2 (lub SQL/92) - "International Standard ISO/IEC
9075:1992, Database Language SQL"
dalszy rozwój: SQL3 (SQL:1999), włącza idee podejścia
obiektowego, dane wielowymiarowe, heterogeniczne, XML, ...
(c) T. Pankowski, SQL - przegląd
2
3
•
•
•
•
•
•
redukcja kosztów szkolenia
przenośność aplikacji
wydłużenie żywotności aplikacji
komunikacja międzysystemowa, interoperacyjność
wygoda dla użytkownika, co robi system, a nie w jaki
sposób
wspólny interfejs do bazy danych
(c) T. Pankowski, SQL - przegląd
4
Standaryzacja - wady
•
•
•
•
ograniczenie kreatywności
SQL jest dość daleki od idealnego języka relacyjnego
braki samego SQL – wiele problemów pozostawiono jako
zależnych od implementacji "implementation
dependent", co utrudnia przenośność
złudzenie istnienia standardu (?)
Oprogramowanie standardowe
zapewniające wymianę danych
między serwerem i klientem:
ADO
(ActiveX Data Objects)
Architektura aplikacji baz
danych
ODBC
(Open Database Connectivity)
Warstwa
oprogramowania
serwera
MS SQL
Server
(c) T. Pankowski, SQL - przegląd
Warstwa
oprogramowania
klienta
Oprogramowanie klienta
ORACLE
Access
5
SQL – przeznaczenie
Platforma danych MS SQL Server
SQL = SQL2 = SQL/92
Uwaga: Używać będziemy notacji stosowanej w dialekcie języka SQL
systemu MS SQL Server, a więc języka Transact-SQL.
1.
Główne funkcje zarządzania danymi:
• definiowanie
• manipulowanie,
• administrowanie
2.
Przeznaczony dla:
• interakcyjnych użytkowników końcowych,
• twórców programów użytkowych (aplikacji),
• administratorów baz danych,
• twórców oprogramowania specjalizowanego (narzędziowego,
systemów współpracujących z bazami danych, ...)
7
(c) T. Pankowski, SQL - przegląd
8
Cechy platformy danych
1.
Architektura SQL Server
W systemie MS SQL Server istnieją dwa typy baz danych:
Zaufanie (bezpieczeństwo, wiarygodność, dostępność,
skalowalność) ang. trusted, security, reliability, scalability):
bazy systemowe,
bazy użytkowe.
szyfrowanie danych, klucze bezpieczeństwa,
audyt (monitorowanie) aktywności,
mirroring baz danych
2.
Bazy systemowe pamiętają zmiany odnoszące się do całego
systemu SQL Server. System wykorzystuje te bazy danych
do zarządzania samym sobą.
Systemowymi bazami danych są:
Produktywność (wydajność budowy i eksploatacji aplikacji):
wspomaganie procesów tworzenia aplikacji, administrowania,
optymalizacji;
zintegrowane środowiska programowania baz danych, aplikacji,
rozproszenia;
klastry baz danych, wieloprocesorowość
3.
• master – baza pamiętająca informacje o wszystkich użytkowych
bazach danych w systemie,
• model – baza stanowiąca wzorzec dla nowo tworzonych baz
danych
• tempdb – robocza baza danych
• msdb – baza danych wykorzystywana przez serwer w procesie
zarządzania
Inteligencja (bussines intelligence):
integracja danych,
analiza i eksploracja danych;
raportowanie danych.
(c) T. Pankowski, SQL - przegląd
9
Tworzenie baz danych
10
Operacje na bazie danych
CREATE DATABASE nazwa_bazy_danych
Wybieranie bazy danych:
USE nazwa_bazy_danych
Np.:
CREATE DATABASE KSS2010
Usuwanie bazy danych:
DROP DATABASE nazwa_bazy_danych
Utworzone zostaną dwa pliki:
KSS2010.mdf – główny plik bazy danych
KSS2010_LOG.ldf – plik dziennika transakcji
(c) T. Pankowski, SQL - przegląd
(c) T. Pankowski, SQL - przegląd
11
(c) T. Pankowski, SQL - przegląd
12
Typy danych
1.
2.
3.
Tworzenie tabel bazy danych
W systemie SQL Server każda kolumna w tabeli, każda zmienna, każde
wyrażenie i każdy parametr mają określony typ.
Oprócz omawianych poniżej systemowych typów danych, użytkownicy
mogą definiować własne typy (patrz CREATE TYPE), które są w istocie
synonimami dla pewnych ograniczeń typów systemowych.
Wyróżniamy następujące systemowe typy danych (przykłady):
<def kolumny> ::= <nazwa kolumny> <typ danych> <warunek spójności>
<typ danych> ::= int | char(n) | money | decimal(n, u), …
znakowe stałej długości: char(n)
znakowe zmiennej długości: varchar(n)
liczby całkowite: int, smallint, …
liczby o ustalonej dokładności (stałoprzecinkowe): decimal(p, s)
liczby zmiennoprzecinkowe: float(n)
walutowe: money
data: date
XML: xml
wielkie obiekty binarne (BLOB): image
…
(c) T. Pankowski, SQL - przegląd
CREATE TABLE <nazwa tabeli>
(<def. kolumny>,
...
<def kolumny>,
<warunek spójności>)
<warunek spójności> ::= [CONSTRAINT <nazwa ograniczenia>]
PRIMARY KEY |
NOT NULL |
REFERENCES <nazwa tabeli>(<nazwy kolumn klucza głównego>) |
CHECK (<nazwa kolumny> <warunek>)
13
Definiowanie tabel - przykład
(c) T. Pankowski, SQL - przegląd
14
Definiowanie tabel - przykład
create table Dostawca (
NrDcy
char(3) primary key,
Nazwa
char(20),
Adres
char(20))
create table Magazyn (
NrMag
INT primary key,
Adres
char(20))
create table Towar (
NrTow
char(5) not null primary key,
Nazwa
char(20),
GrupaTow
char(3) not null,
CenaZak
money,
CenaSprz
money)
15
16
Definiowanie tabel – przykład (c.d.)
Definiowanie tabel – przykład (c.d.)
create table Dostawa (
IdDost
int
identity(1,1),
NrDcy
char(3),
NrMag
int,
NrTow
char(5),
Data
datetime,
Ilosc
decimal(5),
constraint Kg_Dostawa primary key (IdDost),
constraint Ko_Dostawa_Dostawca
foreign key (NrDcy) references Dostawca,
constraint Ko_Dostawa_Towar
foreign key (NrTow) references Towar ,
constraint Ko_Dostawa_Magazyn
foreign key (NrMag) references Magazyn)
create table TowMag (
NrMag
int,
NrTow
char(5),
Stan
decimal(5),
primary key (NrMag, NrTow),
foreign key (NrMag) references Magazyn,
foreign key (NrTow) references Towar)
17
SQL – postać poleceń
2.
Pracownik(IDPrac, Nazwisko, Placa, Stanowisko)
Dochod(IDPrac, Rok, Kwota)
W trybie interakcyjnym, bezpośrednio z terminala
bezpośrednio w trybie interakcyjnym z terminala,
w oprogramowaniu serwera,
w oprogramowaniu klienta.
select
from
where
Kompletności posiadanej informacji w momencie formułowania
polecenia:
•
•
•
18
Pełne określenie zapytania
Zapytania w języku SQL mogą przybierać różną postać. Postać ta może
zależeć od:
1.
Miejsca wydawania polecenia:
•
•
•
1. W powyższych definicjach:
• klucz główny składa się z kilku kolumn dlatego jego definicja
podana jest po definicji wszystkich kolumn,
np: PRIMARY KEY (NrMag,NrTow);
• warunkom spójności bazy danych można nadać nazwy,
umieszczamy je wtedy we frazie CONSTRAINT,
• definiujemy zależności referencyjne przez określenie klucza
obcego FOREIGN KEY oraz tabeli, od której klucza głównego ten
klucz obcy zależy, np: FOREIGN KEY(NrMag) REFERENCES
Magazyn oznacza, że każda wartość wprowadzana do kolumny
TowMag.NrMag musi występować jako wartość klucza głównego
w tabeli Magazyn.
2. Za pomocą CRERATE TABLE tworzone są tabele bazowe
(ekstensjonalne), mające fizyczną reprezentację w bazie danych oraz
określamy niektóre warunki spójności bazy dnaych.
3. Tablice wirtualne (intencjonalne), tzw. widoki lub perspektywy (ang.
view) tworzone są za pomocą CREATE VIEW
posiadamy pełną informację (w zapytaniu nie ma potrzeby używania
zmiennych),
używamy zmiennych do przekazywania i pobierania do/z zapytania
pewnych parametrów, ale postać zapytania jest ustalona,
używamy zmiennych do dynamicznego tworzenia postaci zapytania, a
więc zmienne mogą być zarówno parametry zapytania, jak również jego
struktura.
19
Nazwisko, Placa
Pracownik
Placa >= 2000
Wynikiem jest tabela o dwóch kolumnach: Nazwisko i Placa.
Do tabeli wynikowej należą wiersze, w których atrybut Placa
ma wartość większą lub równą 2000.
Z wykorzystaniem zmiennnych wierszowych (aliasów):
select P.Nazwisko, D.Rok, D.Kwota
from
Pracownik P, Dochod D
where P.IDPrac = D.IDPrac AND Rok > 1980
Zapytania sparametryzowane
Zapytania sparametryzowane (c.d.)
Pracownik(IDPrac, Nazwisko, Placa, Stanowisko)
Dochod(IDPrac, Rok, Kwota)
Pracownik(IDPrac, Nazwisko, Placa, Stanowisko)
Dochod(IDPrac, Rok, Kwota)
Wykorzystanie zmiennych wyjściowych w T-SQL (Transact-SQL):
Wykorzystanie zmiennych wejściowych i wyjściowych (T_SQL):
declare
select
from
where
declare @IDPrac varchar(30)
declare @placa money
set @IDPrac='00123'
select @zm_placa = Placa
from
Pracownik
where IDPrac = @IDPrac
@placa money
@placa = Placa
Pracownik
IDPrac = '00123'
W pierwszym wierszu deklarowana jest zmienna @placa (nazwa każdej
zmiennej w języku Transact-SQL rozpoczyna się od znaku @) typu
money.
W drugim wierszu pod zmienną @placa podstawiana jest wartość
kolumny Placa z ostatniego wiersza stanowiącego zbiór odpowiedzi na
zapytanie.
Wartość zmienna @IDPrac określana jest przed wykonaniem zapytania.
Zauważmy, że dzięki znakowi @ system rozróżnia czy chodzi o nazwę
zmiennej @IDPrac, czy o nazwę kolumny IDPrac.
W obydwu powyższych przypadkach struktura zapytania jest znana.
21
Zapytania dynamiczne (T-SQL)
Zapytania dynamiczne (T-SQL)
declare @zapytanie varchar(100)
declare @tabela varchar(100)
declare @kolumny varchar(100)
set @tabela = 'Pracownik'
set @kolumny='Nazwisko,Placa'
set @zapytanie='select '+@kolumny + ' from '+@tabela
•
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.
execute sp_sqlexec @zapytanie
•
•
•
22
trzy pierwsze linie zawierają deklaracje zmiennych
w trzech kolejnych liniach następuje określenie wartości zmiennych,
tworzymy:
”select Nazwisko,Placa from Pracownik”
w ostatniej linii następuje wykonanie zapytania, procedury pamiętanej
sp_sqlexec, ktrórej parametr @zapytanie przekazuje tekst polecenia
(zapytania, procedury)
23
24
Operowanie danymi
Operowanie danymi
Cztery podstawowe operacje:
select
insert
update
delete
Operacja wyszukiwania:
- wyszukiwanie,
- dołączanie,
- aktualizacja,
- usuwanie.
select distinct W.NrTow, W.NrMag
from
Dostawa W
where NrDcy = '10' AND Ilosc > 2
Przykłady operacji modyfikujących:
distinct - usuwanie duplikatów
insert into Dostawa
values('10','100',1,'1996.11.26',3)
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
update Towar
set
CenaSprz = 1.1 * CenaSprz
where GrupaTow = 'AGD'
delete from Towar
whereCenaSprz < CenaZak
(c) T. Pankowski, SQL - przegląd
25
(c) T. Pankowski, SQL - przegląd
Funkcje agregujące (c.d.)
Funkcje agregujące
count, sum, avg, max, min, count(*)
1.
2.
Liczba wszystkich dostawców:
select count(*) as Ld
from Dostawca
Ld
4
26
3. Sumaryczny wartość towaru 100 w cenach zakupu:
select
sum(T.CenaZak * TM.Stan) as W100
from
Towar T, TowMag TM
where
T.NrTow = TM.NrTow AND T.NrTow = '100'
Liczba dostawców realizujących dostawy
select count(distinct W.NrDcy) as Ldd
from Dostawa W
Ldd
bez distinct:
select count(W.NrDcy) AS Ldd
from Dostawa W
3
W100
Ldd
5
45 000
(c) T. Pankowski, SQL - przegląd
28
Funkcje agregujące (c.d.)
4.
Widoki (views)
Wartości towarów w poszczególnych grupach towarowych:
select
from
where
group by
having
select
from
where
• Widoki są tabelami wirtualnymi (intencjonalnymi), nie mającymi
bezpośredniej reprezentacji w bazie danych. Zapamiętana jest jedynie
ich definicja.
• Definicja widoku wykorzystywana jest w momencie odwoływania się
do niego jak do każdej innej tabeli.
T.GrupaTow, sum(T.CenaZak * TM.Stan) as Wartość
Towar T, TowMag TM
GrupaTow Wartość
T.NrTow = TM.NrTow
----------------- -------------KOM
24 000.00
T.GrupaTow
RTV
45 000.00
GrupaTow <> 'AGD'
*
Towar T, TowMag TM
T.NrTow = TM.NrTow
(c) T. Pankowski, SQL - przegląd
29
(c) T. Pankowski, SQL - przegląd
Widoki (views)
Spójność bazy danych
"Towary RTV w cenach sprzedaży "
create view RTV (NrTow, Nazwa, CenaSprz)
as select T.NrTow, T.Nazwa, T.CenaSprz
from Towar T
where T.GrupaTow = 'RTV‘
1.
2.
Definicja widoku wykorzystywana jest w momencie odwoływania się do
niego:
select
RTV.NrTow, RTV.Nazwa, RTV.CenaSprz
from
RTV
where
RTV.CenaSprz < 2000
3.
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
4.
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ąd
30
31
Spójność, niesprzeczność, integralność (ang. consistency, integrity
constraints).
Spójność oznacza poprawność, niesprzeczność danych w bazie
danych. Standard SQL zawiera środki do definiowania warunków
spójności (lub więzów integralności), np. w obrębie zdania CREATE
TABLE.
Każda próba naruszenia zdefiniowanych warunków spójności
(podczas modyfikacji bazy danych) zostaje udaremniona, operacja
ją podejmująca jest odrzucana, a baza danych pozostaje nie
zmieniona.
W systemie pamiętana jest więc baza danych (dane podstawowe)
oraz wiedza o bazie danych (dane opisujące struktury i warunki
spójności).
(c) T. Pankowski, SQL - przegląd
32
Rodzaje warunków spójności
1.
2.
3.
4.
5.
6.
Programowanie SQL Servera
Typ kolumny – typ może być standardowy lub pochodny zdefiniowany
przez użytkownika.
UNIQUE – dla określenia, że kolumna lub zestaw kolumn ma mieć
unikalną wartość w tabeli (jest kluczem potencjalnym - alternatywnym,
kandydującym – w tabeli), taki klucz może przyjmować wartości NULL.
PRIMARY KEY – specjalny przypadek UNIQUE dla zdefiniowania
klucza głównego – nie może przyjmować wartości NULL.
FOREIGN KEY - dla zdefiniowania zależności referencyjnych
(zależności odniesień). Wartość klucza obcego musi występować jako
wartość klucza w powoływanej tabeli.
CHECK - dla określenia, że kolumna lub zestaw kolumn (z jednej krotki)
mają mieć wartości spełniające określony warunek. Taka spójność
może być zdefiniowane jako reguła w wyrażeniu CREATE RULE.
Bardziej złożone warunki spójności można definiować za pomocą
procedur wyzwalanych w wyrażeniach CREATE TRIGGER. Procedury
te są automatycznie wyzwalane przy aktualizacji tabeli.
(c) T. Pankowski, SQL - przegląd
33
Systemowe procedury pamiętane
(c) T. Pankowski, SQL - przegląd
34
Paczki (batches)
¾ Nazwy wszystkich systemowych procedur pamiętanych rozpoczynają
się od „sp_” lub „xp_”.
¾ Systemowe procedury pamiętana są prekompilowanymi zbiorami
wyrażeń SQL-owych („sp_”) lub języka programowanie („xp_”).
¾ Procedury typu „xp_” (extended stored procedures) są to funkcje
zdefiniowane w bibliotekach dołączanych dynamicznie (bibliotekach
DLL) i służą do rozszerzenia funkcji SQL Servera.
Przykłady:
exec sp_tables – podaje wszystkie tabele w bieżącej bazie danych
exec sp_help Dostawa– wypisuje informacje o tabeli Dostawa
(c) T. Pankowski, SQL - przegląd
¾ Dla wykonywania obliczeń, których nie można wyrazić za pomocą
pojedynczego wyrażenia w języku Transact-SQL (T-SQL), stosuje
się:
• systemowe procedury pamiętane (system stored procedures),
• paczki (wsady) (batches) i skrypty (scripts),
• funkcje (functions),
• procedury pamiętane (składowane) (stored procedures),
• procedury wyzwalane (trigery) (triggers),
• kursory (cursors).
35
• Paczka stanowi grupę złożoną z jednej lub z wielu wyrażeń
Transact-SQLa. Standardowym końcem paczki jest polecenie GO
lub koniec pliku. Paczkę może też stanowić grupa „podświetlonych”
wyrażeń (przy korzystaniu z Query Analysera).
• SQL Server kompiluje paczkę w pojedynczą jednostkę wykonywalną
zwaną planem wykonania (execution plan).
• Wyrażenia tworzące plan wykonania wykonywane są jednorazowo
w jednym czasie. W przypadku wystąpienia błędu kompilacji, żadne
z wyrażeń w paczce nie zostanie wykonane.
• 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ąd
36
Paczki i skrypty
Funkcje
¾ Przykład skryptu złożonego z dwóch paczek:
¾ Rodzaje funkcji:
• funkcje skalarne (scalar user-defined functions) – zwracaną
wartością jest pojedyncza liczba lub tekst, tj. wartość dowolnego
typu z wyjątkiem typów: text, ntext i image;
• proste funkcje tablicowe (inline table-valued functions) – zwracają
wartość typu TABLE, przy czym zwracana tabela jest określona za
pomocą pojedynczego wyrażenia SELECT (funkcje te nazywane są
sparametryzowanymi widokami);
• złożone funkcje tablicowe (multi-statement table-valued functions) –
zwracają zmienną typu TABLE, przy czym zwracana tabela jest
dowolną tabelą utworzoną w ciele funkcji.
USE sklep
GO
-- wykonanie paczki, wybór bazy danych sklep
DECLARE @Powitanie VARCHAR(50)
SET @Powitanie = 'Witaj w sklepie!'
print @Powitanie
GO
-- wykonanie paczki,
-- zmienna @Powitanie po GO nie jest już dostępna.
(c) T. Pankowski, SQL - przegląd
37
(c) T. Pankowski, SQL - przegląd
Funkcje skalarne
38
Proste funkcje tablicowe
Funkcja zwraca największy numer pracownika z podanego działu:
Funkcja zwraca zbiór pracowników z działu o podanym identyfikatorze:
CREATE FUNCTION ostatni_prac_dzialu (@id int)
RETURNS int
BEGIN
declare @nr int
set @nr = (select max(IdPrac) from pracownik
where IdDzialu=@id)
RETURN @nr
END
create function prac_dzialu(@id int)
RETURNS TABLE
as
RETURN(select * from pracownik where IdDzialu=@id)
go
Wykorzystanie funkcji (wywołanie funkcji występuje w roli tabeli):
select * from dbo.prac_dzialu(2)
Wykorzystanie funkcji:
lub bez dbo
select * from pracownik
where IdPrac = dbo.ostatni_prac_dzialu(1)
(c) T. Pankowski, SQL - przegląd
select * from prac_dzialu(2)
39
(c) T. Pankowski, SQL - przegląd
40
Procedury pamiętane
Procedura oblicza dochód w podanym okresie:
CREATE PROC Dochod
@idprac int,
-- id pracownika
@czas int,
-- liczba miesięcy
@wynik int OUTPUT
-- parametr wyjsciowy
as
select @wynik = (pensja + premia)*@czas
from Pracownik where IdPrac = @idprac
go
Wywołanie:
declare @c int, @w int
select @c = 12
exec Dochod 1,@c,@w output
select 'Dochod wynosi: ', @w
41