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