Inspiracja , wiedza , realizacja
Jsystems

W przebudowie

Login



Java

Oracle

Linux

Android

PostgreSQL

Microsoft SQL Server

Zarządzanie strukturami danych

Rafał Czarkowski
Data dodania: Jun 21, 2016
Data aktualizacji: Jun 21, 2016

Tworzenie tabel


Tworząc tabelę podajemy pola jakie ta tabela ma posiadać, rodzaj danych przechowywanych przez te pola oraz własności tabeli.

Podczas tworzenia możemy nakładać różne klucze, ograniczenia, a także ustawić dodawanie wartości domyślnej w przypadku nie wprowadzenia wartości żadnej dla danego pola podczas uzupełniania tabeli.


Najprostszy kod, który stworzy nam tabelę o nazwie SklepSpozywczy. Będzie ona zawierała 5 kolumn o nazwach: ID, Nazwa, Kolor, KrajPochodzeniaID, Cena.


create table SalesLT.SklepSpozywczy(

ID int not null primary key,

Nazwa nchar(20) NOT NULL,

Kolor nchar(20) NULL,

KrajPochodzeniaID int NOT NULL,

Cena decimal(4,2) NOT NULL);




W powyższym przykładzie widać polecenie tworzące tabelę o nazwie SklepSpozywczy w schemacie SalesLT.

Pierwsza kolumna o nazwie ID, na którą od razu przy tworzeniu tabeli został nałożony klucz główny, będzie zawierała wartości liczbowe typu int, w kolumnie tej nie będą mogły być przechowywane wartości null.

Druga o tytule Nazwa, będzie przechowywała dane znakowe typu char, do maksymalnie 20 znaków, w kolumnie tej też nie będą mogły być przechowywane wartości null.

Kolumna trzecia w odróżnieniu do drugiej, poza oczywiście nazwą, może już przechowywać wartości null.

Czwarta poza nazwą i tym, że nie posiada klucza głównego to niczym się nie różni od pierwszej.

Piąta natomiast będzie zawierała wartości liczbowe typu decimal, do czterech znaków, z czego dwa po przecinku, nie może również przechowywać wartości typu null.

create table SalesLT.KrajPochodzenia(

ID int not null identity(1,1) constraint pk_KP primary key,

NazwaKraju varchar(10) not null);




W schemacie stworzona została tabela KrajPochodzenia, z dwoma kolumnami. Pierwsza z nich o nazwie ID, typu int, z opcją autouzupełniania tej kolumny począwszy od numeru pierwszego i wzrostem o jeden oraz kluczem głównym. Druga kolumna o tytule NazwaKraju, typ danych varchar o długości do 10 znaków.


Zmiana nazwy tabeli


Aby zmienić nazwę tabeli korzystamy z polecenia sp_rename:

SP_RENAME ‘ nazwa_tabeli’, ‘nowa_nazwa_tabeli’;

sp_rename ‘SalesLT.SklepSpozywczy’ , ‘SalesLT.XXX’;




Usuwanie tabel


Aby usunąć tabelę korzystamy z polecenia drop:


DROP TABLE nazwa_tabeli;


drop table SalesLT.SklepSpozywczy;




Więzy integralności


Dzięki więzom integralności nie można tak zmodyfikować danych by straciły on spójność.

Są zbiorem zasad nałożonych na tabele w bazie danych.


Primary key

Wymusza unikatowość wierszy kolumnie, a oprócz tego zabrania stosowania znaczników null. Każdy unikatowy zbiór wartości w atrybutach ograniczeń w tabeli może wystąpić tylko raz – czyli tylko w jednym wierszu. Najczęściej zakładany jest na kolumnę która przechowuje dane jednoznacznie określające pojedynczy wiersz. W tabeli może być tylko jeden klucz główny.


Unique

Zapewnia unikalność wartości w kolumnie, jednak w przeciwieństwie do primary key takich kluczy może być więcej niż jeden, oraz umożliwia występowanie wartości null.


Foreign key

Jest to klucz obcy. Służy do definiowania relacji pomiędzy tabelami. Zapewnia on, że rekord w

tabeli podrzędnej zawsze będzie miał swojego odpowiednika w tabeli nadrzędnej. Klucz obcy musi się odwoływać do kolumny (kolumn) w tabeli nadrzędnej, na których założony jest unique lub klucz główny. Obie tabele mogą być tak naprawdę jedną i tą samą tabelą.


Not null

Zapobiega wstawianiu wartości null do kolumny.


Check

Zapewnia, że wartość wstawiana do kolumny spełnia wymagany warunek logiczny.


Default

Jest to wyrażenie używane jako wartość domyślna, jeśli podczas wstawiania wiersza nie jest wprost wyspecyfikowana wartość atrybutu.


Modyfikowanie tabel


Po utworzeniu tabeli, może się okazać, że musimy jednak jeszcze dodać do niej kolumnę, zmodyfikować ją lub usunąć. Służy do tego polecenie alter.


Dodanie kolumny do tabeli


ALTER TABLE nazwa_tabeli ADD nazwa_kolumny typ_danych;


alter table SalesLT.SklepSpozywczy add CenaPromocyjna decimal(4,2) null;




Dodanie do tabeli SklepSpozywczy nowej kolumny o nazwie CenaPromocyjna, o liczbowym typie danych decimal, kolumna ta będzie mogła przechowywać wartości null.


Modyfikacja kolumny w tabeli


ALTER TABLE nazwa_tabeli ALTER COLUMN nazwa_kolumny nowy_typ_danych;


alter table SalesLT.SklepSpozywczy alter column CenaPromocyjna money null;



Zmiana typu danych w kolumnie CenaPromocyjna z decimal na money.


Zmiana nazwy kolumny


SP_RENAME ‘nazwa_tabeli.nazwa_kolumny’, ‘nowa_nazwa_kolumny’, ‘COLUMN’


sp_rename ‘SalesLT.SklepSpozywczy.CenaPromocyjna’, ‘NowaCenaPromocyjna’, ‘column’;




Nazwa kolumny CenaPromocyjna została zmieniona na NowaCenaPromocyjna.


Usuwanie kolumny z tabeli


ALTER TABLE nazwa_tabeli DROP COLUMN nazwa_kolumny;


alter table SalesLT.SklepSpozywczy drop column NowaCenaPromocyjna;




Usunięta została kolumna NowaCenaPromocyjna


Widoki


Jeśli często wykonujemy jakiej zapytanie do bazy danych, np. z użyciem wielu tabel lub po prostu długie, możemy zdefiniować widok. Korzystanie z niego będzie o wiele wygodniejsze. Możemy pobierać z niego dane jak ze zwykłej tabeli. Nagle długie zapytanie z wieloma parametrami do którego często trzeba coś dodawać możemy zamienić w taki zapis:


SELECT * FROM nazwa_widoku;


Widoki są strukturami całkowicie dynamicznymi, tzn. zapytanie, które je definiuje w momencie definicji jest tylko sprawdzane pod względem poprawności składniowej i semantycznej, natomiast nie jest wykonywane. Zapytanie to jest wykonywane w momencie odwoływania się do widoku.


Tworzenie widoków


CREATE VIEW nazwa_widoku AS treść_zapytania


create view WIDOK as

select ProductID, Name, Color, ListPrice from SalesLT.Product

where Color in ('Black', 'Red', 'White');




select * from WIDOK;




Modyfikacja widoków


ALTER VIEW nazwa_widoku AS treść_zapytania




Usuwanie widoków


DROP VIEW nazwa_widoku;


drop view WIDOK;




Tabele tymczasowe


Pojęcie tabeli tymczasowej jest nierozerwalnie związane z pojęciem sesji. Wystarczy spojrzeć do narzędzia SQL Server Management Studio (SSMS), aby stwierdzić, że każde zapytanie otwarte w nowym oknie rozpoczyna nową sesję, czyli nowe połączenie do SQL Server. Aplikacje mogą korzystać z wielu sesji podczas pobierania czy manipulacji danymi w SQL Server, mogą również dzielić jedno aktywne połączenie z innymi programami. Możliwa jest również taka komunikacja, w której każde zapytanie otwiera połączenie i natychmiast je zamyka po otrzymaniu wyniku.

Tworzymy ją w ten sam sposób co zwykłe tabele lub poprzez polecenie into (możemy je wykorzystać również wobec zwykłych tabel).


select ID, NazwaKraju into #tab from SalesLT.KrajPochodzenia;




SQL Server pozwala na utworzenie dwóch rodzajów tabel tymczasowych:

- lokalnych, których nazwa jest poprzedzona prefiksem #

- globalnych, których nazwa jest poprzedzona prefiksem ##


Obydwa rodzaje tabel tymczasowych tworzone są fizycznie w bazie danych tempdb.


Lokalne tabele tymczasowe są widoczne w ramach jednego połączenia, czyli sesji. Można je uznać za prywatne i widoczne tylko dla nas.


Należy pamiętać, że nazwa tabeli tymczasowej – lokalnej czy globalnej – jest ograniczona do 116 znaków, zamiast 128 jak w przypadku tabel użytkownika. Jest to związane z procesem tworzenia tabel tymczasowych. Silnik baz danych musi mieć możliwość identyfikacji każdego obiektu, a tabele tymczasowe istniejące w wielu połączeniach mogą mieć takie same nazwy. Z tego względu SQL Server dokleja wewnętrzny numer (jako sufiks) do każdej nazwy tabeli tymczasowej. W efekcie wygląda to tak, że po utworzeniu tabel o takich samych nazwach w różnych połączeniach:


select name from sys.tables;




Lokalne tabele tymczasowe od razu po ich utworzeniu i zostaną usunięte między innymi po:

- wywołaniu polecenia drop table

- zakończeniu sesji, w której zostały utworzone.

Globalne tabele tymczasowe (utworzone z przedrostkiem ##) mają większą widoczność, ponieważ są dostępne dla wszystkich sesji. Należy jednak zawsze sprawdzać, czy globalna tabela tymczasowa istnieje zanim zostanie utworzona ponownie, ponieważ w takim wypadku SQL Server zgłosi komunikat o błędzie. Globalna tabela tymczasowa również jest usuwana w momencie, kiedy sesja, w której została utworzona tabela tymczasowa, została zakończona i tabela nie posiada referencji (odwołań) w innych aktywnych sesjach.