Politechnika Poznańska,
Instytut Elektrotechniki i Elektroniki Przemysłowej
JĘZYK DDL
(DATA DEFINITION LANGUAGE)
Politechnika Poznańska,
Instytut Elektrotechniki i Elektroniki Przemysłowej
JĘZYK DDL
(DATA DEFINITION LANGUAGE)
2
Instytut Elektrotechniki i Elektroniki Przemysłowej Politechniki Poznańskiej: J. Jajczyk
Modyfikacja nie jest możliwa gdy jest to kolumna:
typu text, image,
obliczeniowa używana w innej kolumnie
replikowana
używana w indeksie
używana w statystykach
używana w ograniczeniu klucza podstawowego
(PRIMARY KEY) oraz obcego (FOREIGN KEY)
używana w ograniczeniu CHECK, UNIQUE, DEFAULT
MODYFIKACJA TABEL (KOLUMN)
3
Instytut Elektrotechniki i Elektroniki Przemysłowej Politechniki Poznańskiej: J. Jajczyk
ALTER TABLE <nazwa_tabeli>
[ADD <definicja_kolumny>] |
[DROP COLUMN <nazwa_kolumny>] |
[ALTER COLUMN <definicja_kolumny>] |
[[WITH NOCHECK] ADD CONSTRAINT
<ograniczenie_tabeli>] |
[DROP CONSTRAINT <nazwa_ograniczenia>]
MODYFIKACJA TABEL (2)
4
Instytut Elektrotechniki i Elektroniki Przemysłowej Politechniki Poznańskiej: J. Jajczyk
kolumny bez ograniczeń
ALTER TABLE pracownicy
ADD PRAC_PESEL varchar(11)
ALTER TABLE pracownicy
ALTER COLUMN PRAC_PESEL int
ALTER TABLE pracownicy
DROP COLUMN PRAC_PESEL
MODYFIKACJA TABEL (3)
5
Instytut Elektrotechniki i Elektroniki Przemysłowej Politechniki Poznańskiej: J. Jajczyk
kolumny z ograniczeniami typu CHECK
ALTER TABLE pracownicy
- ADD PRAC_PESEL int
- ADD CONSTRAINT sprawdz_PESEL
CHECK(PRAC_PESEL > 0)
- DROP COLUMN PRAC_PESEL
- DROP CONSTRAINT sprawdz_PESEL
- DROP COLUMN PRAC_PESEL
- ADD PRAC_PESEL int CONSTRAINT
sprawdz_PESEL CHECK(PRAC_PESEL > 0)
MODYFIKACJA TABEL (4)
6
Instytut Elektrotechniki i Elektroniki Przemysłowej Politechniki Poznańskiej: J. Jajczyk
modyfikacja ograniczeń typu CHECK
ALTER TABLE pracownicy
ADD CONSTRAINT MIN_PLACA_POD
CHECK(PRAC_PLACA_POD > 1000)
ALTER TABLE pracownicy
DROP CONSTRAINT MIN_PLACA_POD
ALTER TABLE pracownicy
ADD CONSTRAINT MIN_PLACA_POD
CHECK(PRAC_PLACA_POD > 1000)
MODYFIKACJA TABEL (5)
7
Instytut Elektrotechniki i Elektroniki Przemysłowej Politechniki Poznańskiej: J. Jajczyk
modyfikacja naruszająca ograniczenie CHECK
ALTER TABLE pracownicy
- ALTER COLUMN PRAC_PLACA_POD int
- DROP CONSTRAINT MIN_PLACA_POD
- ALTER COLUMN PRAC_PLACA_POD int
- ADD CONSTRAINT MIN_PLACA_POD
CHECK(PRAC_PLACA_POD > 2000)
- WITH NOCHECK ADD CONSTRAINT
MIN_PLACA_POD
CHECK(PRAC_PLACA_POD > 2000)
MODYFIKACJA TABEL (6)
8
Instytut Elektrotechniki i Elektroniki Przemysłowej Politechniki Poznańskiej: J. Jajczyk
modyfikacje naruszające ograniczenia kluczy
ALTER TABLE pracownicy
- ALTER COLUMN PRAC_ID int
- DROP CONSTRAINT PK_PRAC
- DROP CONSTRAINT FK_ID_SZEFA
- DROP CONSTRAINT PK_PRAC
ALTER TABLE PRZYDZIALY
DROP CONSTRAINT FK_PRZYDZIALY_PRACOWNICY
ALTER TABLE WYPLATY
DROP CONSTRAINT FK_WYPLATY_PRACOWNICY
- DROP CONSTRAINT PK_PRAC
- ALTER COLUMN PRAC_ID int
MODYFIKACJA TABEL (7)
9
Instytut Elektrotechniki i Elektroniki Przemysłowej Politechniki Poznańskiej: J. Jajczyk
modyfikacje kolumn z wartościami domyślnymi
ALTER TABLE pracownicy
- ADD CONSTRAINT DF_PLACA_DOD DEFAULT 200
FOR PRAC_PLACA_DOD
- ALTER COLUMN PRAC_PLACA_DOD int
- DROP COLUMN PRAC_PLACA_DOD
- DROP CONSTRAINT DF_PLACA_DOD
- ADD PRAC_PLACA_DOD int CONSTRAINT
DF_PLACA_DOD DEFAULT 300
- ALTER COLUMN PRAC_PLACA_DOD numeric(6)
- DROP CONSTRAINT DF_PLACA_DOD
- ALTER COLUMN PRAC_PLACA_DOD numeric(6)
MODYFIKACJA TABEL (8)
10
Instytut Elektrotechniki i Elektroniki Przemysłowej Politechniki Poznańskiej: J. Jajczyk
Pozwala sprawdzić, czy (nie)istnieją wiersze spełniające
kryterium podzapytania.
IF [NOT] EXISTS (<podzapytanie>)
<instrukcja wykonywana warunkowo>
GO
IF NOT EXISTS(SELECT * FROM pracownicy
WHERE prac_id = 280)
INSERT INTO PRACOWNICY VALUES
(280,'Kamil','Baczyński','Czeladnik',22
0,'2000-10-01',2050.00,NULL,50,123456);
GO
INSTRUKCJA IF EXIST
11
Instytut Elektrotechniki i Elektroniki Przemysłowej Politechniki Poznańskiej: J. Jajczyk
Widoki są wirtualnymi tabelami, w których znajdują się wyniki
zapytania traktowane jako prawdziwa tabela.
tworzenie widoków
CREATE VIEW <nazwa_widoku> AS <instr_select>
CREATE VIEW numery_prac AS SELECT
prac_nazwisko, prac_id FROM pracownicy
zmiana w widokach
ALTER VIEW <nazwa_widoku>
AS <instrukcja_select>
usuwanie widoków
DROP VIEW <nazwa_widoku>
DROP VIEW numery_prac
WIDOKI
12
Instytut Elektrotechniki i Elektroniki Przemysłowej Politechniki Poznańskiej: J. Jajczyk
wykorzystanie instrukcji IF EXISTS
IF EXISTS (SELECT name FROM sysobjects
WHERE NAME='numery_prac' AND type='V')
DROP VIEW numery_prac
GO
CREATE VIEW numery_prac AS
SELECT prac_nazwisko, prac_imie, prac_id
FROM pracownicy
GO
WIDOKI (2)
13
Instytut Elektrotechniki i Elektroniki Przemysłowej Politechniki Poznańskiej: J. Jajczyk
modyfikacja widoków
ALTER VIEW numery_prac(prac_nazwisko) AS
SELECT prac_nazwisko FROM pracownicy
ALTER VIEW numery_prac
AS
SELECT prac_nazwisko, prac_id,
zakl_nazwa, zaklady.zakl_id FROM
pracownicy INNER JOIN zaklady
ON pracownicy.prac_zakl_id =
zaklady.zakl_id
GO
WIDOKI (3)
14
Instytut Elektrotechniki i Elektroniki Przemysłowej Politechniki Poznańskiej: J. Jajczyk
Powoduje zwiększenie szybkości wyszukiwania danych w
instrukcji typu SELECT. Pogarszają sprawność instrukcji UPDATE,
DELETE
oraz
INSERT.
Indeks
wielokolumnowy
jest
wykorzystywany, gdy w klauzuli WHERE wywołana zostanie jego
pierwsza kolumna.
Kiedy używać indeksów:
•
Pole jest często wykorzystywane w zapytaniach
•
Pole posiada dużą ilość różnych wartości
•
Pola stosowane są często w złączeniach tabel
Kiedy nie używać indeksów:
•
Pole przyjmują tylko kilka różnych wartości
•
Nie stosuje się indeksów w przypadku częstych modyfikacji
tabel (narzuty czasowe)
INDEKSOWANIE TEABEL
15
Instytut Elektrotechniki i Elektroniki Przemysłowej Politechniki Poznańskiej: J. Jajczyk
tworzenie indeksów
CREATE [UNIQUE] INDEX <nazwa_indeksu>
ON <tabela__widok>(kolumny_indeksowane)
CREATE INDEX IX_nazwiska_prac
ON pracownicy(prac_nazwisko)
usuwanie indeksów
DROP INDEX
<nazwa_tabeli_lub_widoku.nazwa_indeksu>
DROP INDEX pracownicy.IX_nazwiska_prac
INDEKSY
16
Instytut Elektrotechniki i Elektroniki Przemysłowej Politechniki Poznańskiej: J. Jajczyk
wykorzystanie instrukcji IF EXISTS
IF EXISTS(SELECT * FROM sysindexes
WHERE name = 'IX_nazwiska_prac' )
DROP INDEX
pracownicy.IX_nazwiska_prac
GO
CREATE INDEX IX_nazwiska_prac
ON pracownicy(prac_nazwisko DESC,
prac_imie)
GO
INDEKSY (2)
17
Instytut Elektrotechniki i Elektroniki Przemysłowej Politechniki Poznańskiej: J. Jajczyk
Zmienne użytkownika i zmienne systemowe
declare @mojazmienna int, @nazwa char(40)
declare @data datetime
set @mojazmienna=56
set @nazwa=‘Jakiś tekst’
Set @date=getdate();
select Str(Year(@data))+' '+Str(Month(@data))+'
'+Str(Day(@data))
@@error, @@servername, @@TRANCOUNT
SELECT *, @@servername as ‘Nazwa serwera
TRANSACT-SQL (1)
18
Instytut Elektrotechniki i Elektroniki Przemysłowej Politechniki Poznańskiej: J. Jajczyk
Instrukcja warunkowa IF, BEGIN, END
declare @x int;
set @x=10;
if @x>11 select * from Pracownicy
else
begin
insert into Pracownicy (PracNazwisko) values('Jankowski');
select * from Pracownicy;
end;
TRANSACT-SQL (2)
19
Instytut Elektrotechniki i Elektroniki Przemysłowej Politechniki Poznańskiej: J. Jajczyk
Instrukcja warunkowa IF, BEGIN, END
if EXISTS (select * from Pracownicy)
BEGIN
SELECT * FROM Pracownicy
SELECT Count(*) FROM Pracownicy
END
ELSE
SELECT 'Brak rekordów'
TRANSACT-SQL (3)
20
Instytut Elektrotechniki i Elektroniki Przemysłowej Politechniki Poznańskiej: J. Jajczyk
Procedury składowane
Procedury składowane to jeden z elementów języka SQL nie
należący do standardu.
Ich zadaniem jest możliwość tworzenia złożonych struktur
programowych, przechowywanych w strukturach serwera.
Uruchamianie odbywa się często w sposób zdalny z
komputera klienta.
Od chwili wykorzystywania procedur składowanych baza
przestaje być zwykłym elementem gromadzącym dane i
może być wyposażona w tzw. reguły biznesowe,
implementowane wcześniej tylko po stronie aplikacji
klienckich.
TRANSACT-SQL (4)
21
Instytut Elektrotechniki i Elektroniki Przemysłowej Politechniki Poznańskiej: J. Jajczyk
Tworzenie procedury
CREATE PROCEDURE <nazwa procedury>
[@parametr typ_danych (długość)]
AS
Wyrażenia SQL
Usuwanie procedur
DROP PROCEDURE <nazwa_procedury>
Uruchamianie procedur
EXEC <nazwa_procedury> [parametry]
EXECUTE <nazwa_procedury> [parametry]
TRANSACT-SQL (5)
22
Instytut Elektrotechniki i Elektroniki Przemysłowej Politechniki Poznańskiej: J. Jajczyk
CREATE PROCEDURE pro1
AS
BEGIN
IF (SELECT COUNT(*) FROM PRACOWNICY WHERE
PRAC_PLACA_POD>10000)>0
BEGIN
SELECT 'COŚ JEST'
END
ELSE
BEGIN
SELECT 'NIE MA NIC'
END
IF @@ERROR=0 SELECT 'Wszystko jest OKI'
END
EXEC pro1
TRANSACT-SQL (6)
- procedury skaładowane
23
Instytut Elektrotechniki i Elektroniki Przemysłowej Politechniki Poznańskiej: J. Jajczyk
CREATE PROCEDURE pro2 @param1 float, @param2 char(3)
AS
BEGIN
DECLARE @a INT
SET @a=4
SELECT * FROM pracownicy WHERE
(PRAC_PLACA_POD>@param1+@a)
and (PRAC_NAZWISKO like '%'+@param2)
IF @@ERROR=0 SELECT 'NAPIS'
END
EXEC pro2 3000,'ski'
TRANSACT-SQL (7)
- procedury skaładowane
24
Instytut Elektrotechniki i Elektroniki Przemysłowej Politechniki Poznańskiej: J. Jajczyk
CREATE PROCEDURE pro3 @@param int output
AS
BEGIN
SELECT @@param=COUNT(*) FROM PRACOWNICY
END
DECLARE @A INT
EXEC pro3 @A OUTPUT
SELECT @A
TRANSACT-SQL (8)
- procedury skaładowane
25
Instytut Elektrotechniki i Elektroniki Przemysłowej Politechniki Poznańskiej: J. Jajczyk
Funkcje definiowane przez użytkownika
Parametry wyjściowe zastąpiono wartościami
wynikowymi. Mogą one mieć postać skalarną
(dowolnego typu) jak i tabel.
CREATE FUNCTION Prac1 (@placa numeric(6,2))
RETURNS TABLE
AS
RETURN (
SELECT P.Nazwisko, P.Placa_pod
FROM Pracownicy AS P where P.Placa_pod>@placa );
select * from Prac1(100)
TRANSACT-SQL (9)
26
Instytut Elektrotechniki i Elektroniki Przemysłowej Politechniki Poznańskiej: J. Jajczyk
CREATE FUNCTION Prac2 (@param1 numeric(6,2))
RETURNS numeric(6,2)
AS
Begin
DECLARE @zwrot numeric(6,2)
SELECT @zwrot=Avg(P.Placa_pod) FROM Pracownicy AS P
SET @zwrot=@zwrot+@param1
RETURN(@zwrot+@param1)
end;
declare @a int
EXEC @a=Prac2 200
select @a
TRANSACT-SQL (10)
- funkcje użytkownika
27
Instytut Elektrotechniki i Elektroniki Przemysłowej Politechniki Poznańskiej: J. Jajczyk
Wyzwalacze
Wyzwalacze są specjalnymi procedurami
uruchamianymi automatycznie w następstwie pewnego
zdarzenia. Związane są one z tabelami.
Tworzenie
CREATE TRIGGER nazwa_wyzwalacza
ON nazwa_tabeli
FOR [INSERT, UPDATE, DELETE]
AS kod_SQL
Usuwanie
DROP TRIGGER nazwa_wyzwalacza
TRANSACT-SQL (11)
28
Instytut Elektrotechniki i Elektroniki Przemysłowej Politechniki Poznańskiej: J. Jajczyk
CREATE TRIGGER Trig1
ON PRACOWNICY
FOR INSERT
AS
BEGIN
…
END
TRANSACT-SQL (12)
- wyzwalacze
29
Instytut Elektrotechniki i Elektroniki Przemysłowej Politechniki Poznańskiej: J. Jajczyk
Rzeczywistość musi być zapisana w określonej formie w
bazie. Zmiany rzeczywistości muszą powodować zmiany w
stanie bazy.
Baza danych jest spójna wówczas, gdy jej stan
odpowiada stanowi świata rzeczywistego.
PRZETWARZANIE TRANSAKCYJNE (1)
Świat
rzeczywisty
Świat
rzeczywisty
Baza ...
Baza ...
Zmiany
świata
Zmiany
w bazie
30
Instytut Elektrotechniki i Elektroniki Przemysłowej Politechniki Poznańskiej: J. Jajczyk
Przez transakcje rozumie się grupę operacji na bazie,
która od strony logicznej stanowi całość i wykonywana jest
razem. W transakcji zostają wykonane jej wszystkie
składowe lub żaden z jej elementów.
Elementy składowe transakcji:
•
Odczyt i/lub zapis danych
•
Zakończenie transakcji z zatwierdzeniem
•
Wycofanie transakcji (zakończenie bez zatwierdzenia)
Przykład transakcji: przelew określonej kwoty z jednego
konta na drugie
PRZETWARZANIE TRANSAKCYJNE (2)
31
Instytut Elektrotechniki i Elektroniki Przemysłowej Politechniki Poznańskiej: J. Jajczyk
WŁAŚCIWOŚCI TRANSAKCJI (ACID)
Atomowość (niepodzielność) – ang.
A
tomicity: zbiór operacji, które
wchodzą w skład transakcji jest niepodzielny.
Spójność - ang.
C
onsistency preservation: baza przed i po wykonaniu
transakcji musi być spójna (odpowiadać rzeczywistości). Nie mogą
być naruszone
więzy integralności
(reguły pozwalające
utrzymywać bazę w poprawnym stanie).
Izolacja – ang.
I
solation: dwie wykonywane jednocześnie transakcje
nie powinny na siebie wzajemnie wpływać. Jeśli wpływają, to musi
nastąpić ich wycofanie.
Trwałość – ang.
D
urability: po zakończeniu transakcji (z
zatwierdzeniem) wynik jej działania nie może zostać utracony
nawet przy zaniku napięcia zasilającego. W przypadku awarii
zmiany musza być odwracalne.
PRZETWARZANIE TRANSAKCYJNE (3)
32
Instytut Elektrotechniki i Elektroniki Przemysłowej Politechniki Poznańskiej: J. Jajczyk
rozpoczęcie
- begin tran
begin transaction tr1
zatwierdzenie
- commit
commit transaction tr1
wycofanie
- rollback
rollback transaction tr1
begin transaction transakcja1;
INSERT INTO Etaty (EtatNazwa) VALUES ('Sprzątacz');
if @@ERROR>0 rollback transaction transakcja1
else commit transaction transakcja1
PRZETWARZANIE TRANSAKCYJNE (4)
33
Instytut Elektrotechniki i Elektroniki Przemysłowej Politechniki Poznańskiej: J. Jajczyk
Wykorzystanie szablonów w
SQL Query Analyzer
zakładka Templates
edycja Replace Template Parameters
SZABLONY