Database

Zaawansowane systemy
baz danych
Wykonali:
Krzysztof Fitał
Michał Sobol
O czym będziemy dziś mówili
Tworzenie bazy dany
Typy danych
Zarządzanie bazą
Generowanie skyptów
Tworzenie bazy
Aby utworzyć bazę danych w SQL
Serverze można skorzystać z:
Database Creation Wizard,
SQL Server Enterprise Managera,
wyrażenia CREATE DATABASE.
Database Creation Wizard
Uruchom SQL Server
Enterprise Managera
- wybierz z menu
Start opcję Programy,
następnie Microsoft
SQL Server 2000 i
Enterprise Manager.
Połącz się z kopią
SQL Servera.
Kliknij na Tools a
następnie na Wizards
Database Creation Wizard
W otwartym oknie
rozwijamy Database,
następnie wybieramy
Create Database
Wizard
Database Creation Wizard
Na 2 ekranie możemy wpisać:
-Nazwę bazy dancyh
-Nazwę pliku bazy danych
-Nazwę pliku dziennika
Database Creation Wizard
Na 3 ekranie
podajemy
początkowe
rozmiary plików
tworzonej bazy
danych
Database Creation Wizard
Na 4 ekranie
definiujemy
wartość przyrostu
pliku bazdy
danych oraz
możemy
zdefinować
maksymalny
rozmiar pliku.
Database Creation Wizard
Na 5 ekranie
podajemy
początkowy rozmiar
pliku dziennika
(logfile)
Database Creation Wizard
Na 6 ekranie
definiujemy wartość
przyrostu pliku
dziennika bazdy
danych oraz możemy
zdefinować
maksymalny rozmiar
tego pliku.
Database Creation Wizard
Na ostatnim ekranie
widzimy wszystkie
informacje o nowo
tworzonej bazie
danych także
pozostaje nam
kliknięcie na zakończ.
Powinnien wyskoczyć
komunikat, że baza
została pomyślnie
stworzona.
SQL Server Enterprise Managera
Uruchom SQL Server Enterprise Managera — wybierz
z menu Start opcję Programy, następnie Microsoft SQL
Server 2000 i Enterprise Manager.
Połącz się z kopią SQL Servera.
Rozwiń folder Databases, jak pokazano na rysunku
SQL Server Enterprise Managera
Kliknij prawym klawiszem
ikonę folderu Databases lub
pustą przestrzeń w prawym
panelu, następnie wybierz
New Database z menu
kontekstowego.
Pojawi się okno dialogowe
Database Properites z
aktywną zakładką General.
Wpisz nazwę bazy danych.
Następnie możesz przejść do
zakładki Data Files, aby
zobaczyć nowy plik bazy
danych o rozmiarze wstępnie
ustawionym na 1 MB w
domyślnym folderze ...\Data.
SQL Server Enterprise Managera
Jeśli chcesz zmienić
właściwości plików bazy
danych, kliknij odpowiednie
pole i wprowadź modyfikacje
(w przykładzie pozostawiono
wartości domyślne). Możesz
dodać kolejne pliki bazy przejdź do wolnego pola w
kolumnie File Name i ustaw
właściwości dodatkowego
pliku.
Kliknij zakładkę Transaction
Log. Warto zauważyć, że
domyślną nazwą dziennika
jest NazwaBazy_Log, a jego
rozmiar to 1 MB.
SQL Server Enterprise Managera
Po kliknięciu na OK., okno powinno wyglądać podobnie, jak
pokazane na rysunku. Jeżeli stworzone przez Ciebie bazy nie są
widoczne, kliknij prawym przyciskiem myszy folder Databases i
wybierz z menu, które się pojawi, pozycję Refresh.
Wyrażenia CREATE DATABASE
Składnia polecenia CREATE DATABASE wygląda następująco:
CREATE DATABASE nazwa bazydanych
[ON { [PRIMARY]
(NAME = nazwa logiczna,
FILENAME = ‘nazwa fizyczna‘
[,SIZE = rozmiar]
[,MAXSIZE = max rozmiar [UNLIMITED]
[,FILEGROWTH =wart. przyrostu ] ) }
[....n] ]
[LOG ON
{(NAME = nazwa logiczna,
FILENAME = nazwa fizyczna‘
[,SIZE = rozmiar]
[,MAXSIZE = max rozmiar [UNLIMITED]
[,FILEGROWTH =wart. przyrostu ] ) }
[....n] ]
[,COLLATE strona kodowa]
[FOR LOAD | FOR ATTACH]
Wyrażenia CREATE DATABASE
Wyrażenia CREATE DATABASE
nazwa bazydanych - odnosi się do bazy danych jako całości.
ON PRIMARY - określa, do których grup plików przynależy baza
danych. Domyślną grupą plików jest Primary.
NAME - określa nazwę logiczną, która będzie używana w SQL
Serverze do odnoszenia się do fizycznego pliku bazy danych na
dysku.
FILENAME - jest to ścieżka i nazwa pliku, określająca lokalizację
danych na dysku twardym. Wymagany jest lokalny dysk twardy.
SIZE - określa, jak duży powinien być plik bazy danych. Wartość ta
może być wyrażona w megabajtach lub kilobajtach. Domyślnym
rozmiarem jest wielkość pliku Model. Aby określić jednostkę
(megabajt lub kilobajt), należy dołączyć sufiks MB lub KB do
parametru rozmiaru. Przykładowo, 10 MB utworzy plik o wielkości
10 megabajtów. Megabajty mogą być używane tylko do określenia
pełnych liczb. Aby utworzyć 2,5 megabajtową bazę danych, należy
użyć kilobajtów, czyli 2 560 KB.
Wyrażenia CREATE DATABASE
MAXSIZE - określa maksymalny rozmiar, do którego baza danych
może się rozrastać. Jeżeli nie zostanie on określony, włączony jest
automatyczny wzrost. Baza danych może rozrastać się, aż zajmie
cały dysk twardy. Parametr ten jest również wyrażony w
megabajtach lub kilobajtach.
FILEGROWTH - określa wartość przyrostu, używanego do
automatycznego powiększania pliku bazy danych. Parametr ten
może być wyrażony w megabajtach, kilobajtach lub jako procent
rozmiaru pliku w czasie powiększania go. Jeśli nie jest on określony,
domyślnie przyjmuje się 1 MB. Parametr FILEGROWTH nie może
przekroczyć wartości parametru MAXSIZE.
LOG ON - opisuje, gdzie położone są pliki dziennika transakcji i jaki jest
ich rozmiar.
Wyrażenia CREATE DATABASE
COLLATE - nowa opcja w SQL Serverze 2000, określa językową stronę
kodową w pojedynczej bazie danych. Może to być strona kodowa
dostępna w SQL Serverze lub w systemie Windows. Jeżeli ten
parametr nie zostanie określony, domyślnie przyjmowana jest
wartość z kopii SQL Servera 2000. Strona kodowa może być
również określona na poziomie tabeli i pojedynczej kolumny.
FOR LOAD - ustawia dla bazy danych własność DBO Use Only. Opcja
ta jest wprowadzona jedynie dla zachowania zgodności wstecz z
SQL Serverem 6.5 i nie powinna być używana w SQL Serverze
2000.
FOR ATTACH - dołącza ponownie zbiór plików, które tworzą bazę
danych. Pliki bazy danych muszą być wcześniej utworzone i
oddzielone od SQL Servera 2000.
Typy danych
Kategoria
Typ danych
Tekstowy
char(n), varchar(n) Przechowuje
łańcuchy znaków
Binarny
binary(n),
varbinary(n)
Całkowity
Komentarz
Przechowuje
informacje binarne
w parach po 2
bajty
int, smallint, bigint, Przechowuje
tinyint
wartości całkowite
Typy danych
Kategoria
Typ danych
Komentarz
Przybliżony
numeryczny
float, real
Przechowuje
przybliżone wartości
liczbowe
Dokładny
numeryczny
decimal, numeric
Przechowuje
dokładne informacje
liczbowe
Specjalny
bit, text, image,
sql_variant, table
znakowe większe
niż 8 000 bajtów lub
dane obrazu.
sql__variant
Typy danych
Kategoria
Typ danych
Komentarz
Data i czas
datetime,
smaildatetime
Przechowuje wartości
daty i czasu
Waluta
money, smallmoney
Przechowuje wartości
walutowe
Automatycznie
zwiększany
rowversion (formalnie
timestamp)
Przechowuje wartości
typów danych
zwiększanych
automatycznie lub
ustawionych przez
SQL Server
Typy danych
Kategoria
Typ Danych
Mapuje typ danych ANS do
typów danych SQL Servera
Synonimy
Dane Unicode
Komentarz
nchar, ntest, nvarchar
Przechowuje dane w formacie
Unicode (podwójny bajt na
znak)
Zdefiniowany przez
użytkownika
Można tworzyć własne typy
danych do przechowywania
informacji
Wyliczeniowy
Przechowuje wyrażenia,
służące do wyliczenia wartości
kolumny. Nie przechowuje
danych, jedynie wyliczenia,
które służą do utworzenia tych
danych.
Typy danych - NULL
Nullability (dopuszczanie w kolumnie
wartości NULL) odnosi się do tego, czy
wpis w danej kolumnie jest wymagany.
Jeżeli kolumna ma zgłaszać, że wpisana
wartość jest nieznana, należy ustawić
wartość NULL. Jeżeli założeniem jest, że
każdy wiersz musi mieć wpis w tej
kolumnie, należy określić kolumnę jako
NOT NULL.
Typy danych - tekstowe
Łańcuchy zawierają dane znakowe,
utworzone z liter, liczb i symboli. Można
przechowywać dane znakowe w formacie
o ustalonej lub zmiennej długości pola za
pomocą słów kluczowych char(n) lub
varchar(n). Można przechowywać
maksymalnie 8 000 znaków w tych typach
danych.
Typy danych - binarne
Typ binarny przechowuje dane binarne. Są one
przechowywane jako seria zer i jedynek,
reprezentowanych na wejściu i wyjściu jako pary
heksadecymalne. Te heksadecymalne pary są
tworzone ze znaków od 0 do 9 i od A do F.
Jak w przypadku danych typu łańcuchowego, tu
też można określić maksymalnie 8 000 bajtów
dla danych typu binary(n) i varbinary(n).
Typy danych – liczby całkowite
Istnieją cztery rodzaje danych
typu całkowitego (integer),
które mogą przechowywać
dokładne, skalarne wartości:
bigint, int, smallint i tinyint.
Różnica pomiędzy typami
danych całkowitych występuje
w ilości przestrzeni pamięci,
której wymagają, oraz w
zakresie wartości, jakie mogą
przechowywać.
Typ
Długość Zakres
tinyint
1
0-255
smallint
2
± 32 767
int
4
±2 147 483 657
bigint
8
± 2^63
Typy danych liczbowych
przybliżonych
Przybliżonymi typami danych liczbowych
są float i real. Liczby, które są
przechowywane za pomocą tych typów
danych, składają się z dwóch części:
mantysy i wykładnika. Algorytm, używany
do określenia takiej pary, nie jest
szczególnie precyzyjny. Inaczej mówiąc,
można nie otrzymać z powrotem tego, co
zostało wprowadzone.
Dokładne typy danych liczbowych
Dokładne typy danych liczbowych to
decimal i numeric. Dokładność jest
utrzymywana do najmniej znaczącej cyfry.
Jeżeli zostaje zadeklarowany dokładny typ
danych numerycznych, należy określić
dwa składniki: precyzję i skalę. Jeżeli nie
zostaną one określone, SQL Server używa
domyślnych wartości 18 i 0.
Typy danych - specjalne
Specjalne typy danych:





Bit
Text
Image
RowGUID
Sql_variant
Typy danych - bit
Typ danych bit jest logicznym typem danych,
używanym do przechowywania informacji
Boolean. Jest on używany jako flaga, aby
oznaczyć zdarzenia typu włączone/wyłączone,
prawda/fałsz lub tak/nie. Wartości
przechowywane przez ten typ to 0 lub 1
Kolumny typu bit mogą mieć wartość NULL
(nieznaną), ale nie mogą być indeksowane.
Typy danych bit wymagają pojedynczego bajta
pamięci na przechowywanie.
Typy danych – text i image
Można użyć typów danych text i image, jeżeli
wymagania przechowywania przekroczą 8 000 znaków co jest limitem kolumny. Często występują odwołania do
nich jako do danych typu BLOB. Typy danych text i
image mogą przechowywać na deklarację do 2 GB
danych binarnych lub tekstowych.
Jeżeli zostają zadeklarowane dane typu text lub image,
do wiersza dodawany jest 16-bajtowy wskaźnik.
Wskazuje on na osobne 8 kB strony danych - tu są
przechowywane dodatkowe informacje na temat danych.
Jeżeli przekroczą one 8 kB na stronę danych, tworzone
są wskaźniki do dodatkowych stron danych BLOB.
Typy danych – sql-variant
Typ danych sql_variant jest podobny do
typu danych Visual Basica variant. Typ
sql_variant pozwala na przechowywanie w
nim prawie każdego innego
podstawowego typu danych SQL Servera.
Wyjątki stanowią: ntext, timestamp i
sql_variant.
Typy danych – data i czas
Dane daty i czasu mogą być
przechowywane w typach datetime i
smalldatetime. Warto zauważyć, że data i
czas są zawsze przechowywane razem, w
pojedynczej wartości.
Dane daty i czasu mogą występować w
kilku różnych formatach.
Typy danych – data i czas
Daty w typie datetime są przechowywane z dokładnością
do milisekund. W tym przypadku używane jest 8 bajtów
— 4 dla liczby dni od 1 stycznia 1900 oraz 4 liczby
sekund po północy (daty wcześniejsze niż ten przedział
są przechowywane jako liczby ujemne, tworząc zakres
dat od 1/1/1753 do 12/31/9999). Dokładność dat jest w
zakresie 3,33 milisekund.
Typ danych smalldatetime używa 4 bajtów. Daty
przechowywane tym sposobem są dokładne co do
minuty. Jedna wartość smallint (2 bajty) wewnętrznie
jest używana do określenia liczby dni po 1 stycznia
1900; druga wartość smallint jest używana do określenia
liczby sekund po północy.
Typy danych - waluta
Dwoma typami danych waluty są money i
smallmoney. Obydwa mają skalę równą cztery,
co oznacza, że mogą przechowywać cztery cyfry
po przecinku dziesiętnym. Te typy danych mogą
przechowywać informacje inne niż wartości
dolarowe, dla użytku międzynarodowego. Nie są
dostępne żadne funkcje konwersji walutowej w
serwerze SQL. Kiedy zostaną wprowadzone
dane walutowe, należy poprzedzić je znakiem
dolara.
Typy danych - użytkownika
Można tworzyć typy danych, zdefiniowane przez
użytkownika dla specyficznej bazy danych lub można
umieszczać je w bazie danych model. Należy pamiętać,
że ta baza danych jest szablonem do tworzenia nowych
baz. Utworzone przez użytkownika typy danych będą
dostępne we wszystkich kolejnych bazach danych.
Aby utworzyć typ danych, zdefiniowany przez
użytkownika, należy skorzystać z typu danych, który
został dostarczony przez system. Przykładowo, jeśli
tworzy się typ danych zwany EmployeelD i definiuje się
go, należy określić, czy jest to typ znakowy, czy
całkowity.
Typy danych - użytkownika
Należy najpierw utworzyć własny typ
danych, a dopiero po tym dodać go do
tabeli. Aby utworzyć własny typ danych,
należy skorzystać z SQL Server Enterprise
Managera lub z systemowej procedury
składowej sp_addtype.
Typy danych - użytkownika
Tworzenie za pomocą sp_addtype
EXEC sp_addtype NAZWA ‘typ’, ‘czy null’
np: EXEC sp_addtype PracID ‘char(5)’, ‘NULL’

Po zadeklarowaniu typów, zdefiniowanych
przez użytkownika, można ich używać w
bazie danych tak często, jak tylko
zachodzi taka potrzeba.
Zarządzanie bazą
Będziemy tutaj mówić o następująych
zagdanieniach:
Rozbudowywanie bazy danych
Wykonywanie backup’ów
Plik dziennika
Zarządzanie bazą
Tworzenie tabeli
Zarządzanie bazą
Gdy już utworzymy nową tabele, możemy
dodawać kolejne wiersze do niej
Zarządzanie bazą
Polecenia operujące na
danych w tabeli:
INSERT
UPDATE
DELETE
DELETE FROM Table
WHERE CategoryName LIKE ‘ever%’
DROP
DROP TABLE Table
Zarządzanie bazą
Buckup Bazy
Wybieramy baze którą chcemy buckup’ować. Klikamy
prawym przyciekiem myszy, wybieramy z menu All Tasks
a następnie Backup Database
Zarządzanie bazą
Zarządzanie bazą
Zarządzanie bazą
Można również zmniejszyć rozmiar całej
bazy danych poleceniem DBCC
SHRINKDATABASE'
Jego składnia jest następująca:
DBCC SHRINKDATABASE {
(database_name
[. target_percent]
[.{NOTRUNCATE | TRUNCATEONLY}]) }
Zarządzanie bazą
Znaczenie składni:
Database_name - jest nazwą bazy danych, która będzie
zmniejszana.
Target_percent - jest procentowym określeniem wolnej
przestrzeni w bazie danych po zmniejszeniu.
NOTRUNCATE - wymusza pozostawienie wolnej
przestrzeni w plikach bazy danych. Parametr
target_percent jest ignorowany. Domyślnie zwolniona
przestrzeń jest z powrotem dostępna dla systemu
operacyjnego.
TRUNKCATEONLY - wymusza, aby nieużywana
przestrzeń w plikach danych była oddawana do systemu
operacyjnego. Po wybraniu TRUNCATEONLY parametr
target_percent jest ignorowany i cała wolna przestrzeń
jest zwracana do systemu.
Zarządzanie bazą
Polecenie SHRINKDATABASE zmniejsza wszystkie pliki
danych w bazie, pozostawiając wyznaczony procent wolnej
przestrzeni. Używane na końcu strony plików danych są
przesuwane poniżej ustalonego procentowego progu. Na
przykład w bazie danych o wielkości 10 MB, przechowującej
jedynie 5 MB danych, można ustawić parametr
target_percent na 20. Oznacza to, że 20% bazy danych ma
być pozostawione jako wolna przestrzeń. Wszystkie wiersze
utworzone w bazie danych są przemieszczane, tym samym
wszelkie wolne miejsce na początku bazy danych jest
usuwane. Proces ten jest podobny do defragmentacji
dysków. Jest tylko 5 MB danych i ma pozostać 20% wolnej
przestrzeni; 20% z 5 MB to 1 MB, czyli rozmiar bazy danych
zostanie zmieniony na 6 MB, a pozostałe 4 MB zostają
oddane z powrotem do systemu operacyjnego.
Zarządzanie bazą
Nie można zmniejszyć bazy danych
bardziej niż do obszaru, który jest
potrzebny do przechowywania danych, nie
można także jej zminimalizować do
rozmiaru mniejszego niż baza model. Nie
można również zmniejszyć bazy danych
do rozmiarów mniejszych niż pierwotne,
które były określone przy jej tworzeniu (za
pomocą polecenia CREATE DATABASE).
Zarządzanie bazą
Usuwanie bazy danych
Czasem konieczne staje się usunięcie
bazy danych z systemu. Może ona na
przykład nie być już dłużej potrzebna. W
każdym przypadku usuwanie bazy danych
z SQL Serwera jest względnie proste.
Można ją usunąć zarówno za pomocą
SQL Server Enterprise Managera, jak też
języka Transact-SQL.
Zarządzanie bazą
Zanim usunie się bazę danych, należy uświadomić sobie
kilka istotnych zależności:
Usunięcie bazy danych powoduje wyeliminowanie
informacji na temat tabel systemowych oraz usunięcie z
systemu plików danych i dziennika.
Usunięta baza danych może być przywrócona jedynie za
pomocą odtwarzania z kopii bezpieczeństwa.
Podczas usuwania z bazy nie może korzystać żaden
użytkownik.
Aby usunąć bazę danych, należy mieć uprawnienia roli
db_owner (lub serwer_sysadmin).
Polecenie DROP DATABASE należy wydawać po
zalogowaniu do bazy master.
Zarządzanie bazą
Składnia polecenia DROP DATABASE wygląda
następująco:
DROP DATABASE database_nam,
database_nam2
Polecenie DROP DATABASE umożliwia usunięcie
jednorazowo wielu baz danych.
Generowanie skryptów
Po co są skrypty
Dzięki skryptom możemy w łatwy sposób utworzyć
obiekty bazy danych i zabezpieczenia.
SQL Server może również odtworzyć strukturę
obiektów bazy danych i zabezpieczeń, przy
pomocy wcześniej wygenerowanego skryptu,
który może być później uruchamiany
Generowanie skryptów
W celu uzyskania dostępu do funkcji, tworzących skrypty
z SQL Server Enterprise Managera, należy kliknąć
prawym klawiszem bazę danych, następnie Wszystkie
zadania, Generale SQL Scripts, aby otworzyć okno
dialogowe
Generowanie skryptów
Z okna Generate SQL Scripts
można wybrać, jaki rodzaj
skryptu ma być generowany.
Zakładka General zawiera
obiekty, które mają zostać
wykorzystane przy tworzeniu
skryptu.
Jednym z rodzajów skryptów
są skrypty bezpieczeństwa.
Aby je wygenerować należy
zaznaczyć wszystkie obiekty
Generowanie skryptów
Po kliknięciu zakładki
Formatting warto
zauważyć, że domyślną
opcją jest generowanie
skryptu, który usuwa
obiekty i je tworzy. Należy
usunąć obydwie z tych
możliwości oraz wszelkie
inne opcje, ustawione z
zakładki Formatting,
Generowanie skryptów
Następnie trzeba kliknąć
zakładkę Options, aby
wybrać odpowiednie opcje
wykonywania skryptów
bezpieczeństwa .
Aby w danym momencie
wykonać skrypt, należy
wcisnąć przycisk OK.
Następnie system zapyta o
nazwę pliku skryptu i jego
położenie.
Generowanie skryptów
Można również wcisnąć
przycisk Preview na
zakładce General, który
powoduje, że skrypty są
generowane w oknie podglądu. Jeżeli został wybrany
przycisk Preview, można
kopiować cały skrypt do
Schowka Windows poprzez
wciśnięcie przycisku Copy
Generowanie skryptów
Skrypty replikacji
Kolejny przykładem skryptów są skrypty
replikacji zawierające polecenia TransactSQL, niezbędne do tworzenia i konfiguracji
danego scenariusza replikacji
Generowanie skryptów
Skrypty takie mają następujące zalety:
Można używać skryptu do identycznej konfiguracji wielu
serwerów. Takie działanie oszczędzi kłopotów z ciągłym
używaniem kreatorów.
Można używać skryptów do śledzenia różnych wersji
środowiska replikacyjnego. Jeżeli w środowisku są
wykonywane zmiany, można tworzyć dodatkowe skrypty.
Można łatwo i szybko dostosować środowisko
replikacyjne do swoich potrzeb.
Można używać skryptów jako część procesu
odzyskiwania oraz do reinstalowania dowolnych
aspektów środowiska replikacji.
Generowanie skryptów
Należy pamiętać że gdy został utworzony
skrypt dla wielu serwerów, należy dokonać
kilku niewielkich modyfikacji. Głównie
należy zmienić nazwę komputera, do
którego odwołuje się skrypt. Pomimo że
wprowadzanie zmian może być
czasochłonne, jest na pewno szybsze niż
kilkakrotne przechodzenie przez proces
kreatora.