Tworzenie baz danych i tabel

Tworzenie baz danych i tabel
Wprowadzenie
SQL (ang. Structured Query Language) – strukturalny język zapytań używany do tworzenia,
modyfikowania baz danych oraz do umieszczania i pobierania danych z baz danych. SQL został
opracowany w latach 70. w firmie IBM. Stał się standardem w komunikacji z serwerami relacyjnych
baz danych. Wiele współczesnych systemów relacyjnych baz danych używa do komunikacji z
użytkownikiem SQL, dlatego potocznie mówi się, że korzystanie z relacyjnych baz danych to
korzystanie z SQL-a.
Pierwszą firmą, która włączyła SQL do swojego produktu komercyjnego, był Oracle. Dalsze
wprowadzanie SQL-a, w produktach innych firm, wiązało się nierozłącznie z wprowadzaniem
modyfikacji pierwotnego języka. Wkrótce utrzymanie dalszej jednolitości języka wymagało
wprowadzenia standardu.
Użycie SQL, zgodnie z jego nazwą, polega na zadawaniu zapytań do bazy danych. Zapytania można
zaliczyć do jednego z trzech głównych podzbiorów:
 SQL DML (ang. Data Manipulation Language – „język manipulacji danymi”),
 SQL DDL (ang. Data Definition Language – „język definicji danych”).
 SQL DCL (ang. Data Control Language – „język kontroli nad danymi”).
Instrukcje SQL w obrębie zapytań tradycyjnie zapisywane są wielkimi literami, jednak nie jest to
obowiązkowe. W standardzie, zapytania SQL kończone są średnikiem (;), ale niw we wszystkich
implementacjach jest to obowiązkowe (np. w TransactSQL firmy Microsoft). Dodatkowo, niektóre
programy do łączenia się z silnikiem bazy danych (np. psql w przypadku PostgreSQL), używają
swoich własnych instrukcji, spoza standardu SQL, które służą np. do połączenia się z bazą,
wyświetlenia dokumentacji itp.
Tworzenie bazy danych
IF DB_ID('NazwaBazy') IS NULL
CREATE DATABASE NazwaBazy
-- Jeśli baza nie istnieje to
-- utwórz bazę danych
Usuwanie bazy danych
IF DB_ID('NazwaBazy') IS NOT NULL
DROP DATABASE NazwaBazy
-- Jeśli baza istnieje to
-- usuń bazę danych
Tworzenie tabel
Dane w relacyjnej bazie danych przechowywane są w tabelach, które są dwuwymiarowymi
macierzami. Tabele zostały wybrane jako logiczna struktura do przechowywania danych ze względu
na łatwość uzyskiwania i wyświetlania danych, jak również manipulowania nimi. Tabele tworzy się za
pomocą polecenia: CREATE TABLE.
IF OBJECT_ID('Baza.Właściciel.NazwaTabeli','U') IS NULL -- Jeśli tabela nie
istnieje
CREATE TABLE Baza.Właściciel.NazwaTabeli ( -- to utwórz tabelę
nazwa_atrybutu typ [ograniczenie],
nazwa_atrybutu typ [ograniczenie],
... )
UWAGI:
1. Podawanie nazwy bazy nie jest obowiązkowe np.
CREATE TABLE Właściciel.NazwaTabeli
Tabela będzie wówczas zakładana w bieżącej (aktualnej) bazie danych.
BAD210
Strona 1
2. Podawanie właściciela również nie jest konieczne np. CREATE TABLE NazwaTabeli.
Automatycznie właścicielem tabeli będzie użytkownik, który ją zakłada.
3. Jeśli tabela ma być dostępna dla wielu użytkowników, jako właściciela należy podać dbo
(database owner), tzn. CREATE TABLE dbo.NazwaTabeli.
Pamiętajmy, że tworząc zapytania będziemy musieli odwoływać się do atrybutów tabel. Dlatego warto
nadawać im takie nazwy, które niosą informację o zawartych w nich danych.
Typy danych
Typy danych charakteryzują daną komórkę w wierszu. Określają, jakiego rodzaju informacje możemy
umieścić w danej kolumnie. Przykładowo innego typu danych użyjemy, gdy będziemy chcieli
przechowywać imię użytkownika, innego dla daty jego urodzin, a jeszcze innego, gdy będziemy chcieli
przechowywać jego zdjęcie. Poszczególne typy danych można zakwalifikować do rożnych grup,
według funkcjonalności jaką mogą spełniać:
•
•
Dane tekstowe:
Liczbowe:
•
•
•
•
Data i czas:
Binarne:
Waluta:
Specjalne:
CHAR, VARCHAR, NCHAR, NTEXT, NVARCHAR
INT, SMALLINT, BIGINT, TINYINT, FLOAT,
REAL, DECIMAL, NUMERIC
DATETIME, SMALLDATETIME
BINARY, VARBINARY
MONEY, SMALLMONEY
TEXT, IMAGE, XML, BIT
Dokładną definicję oraz wyjaśnienie zastosowania danego typu można znaleźć w pomocy
dołączonej do MS SQL Server.
Ograniczenia
Poszczególne atrybuty w tworzonej tabeli mogą posiadać ograniczenia co do dopuszczalnych
danych:
PRIMARY KEY – ograniczenie definiujące klucz podstawowy dla relacji
FOREIGN KEY – ograniczenie definiujące ¿e dany atrybut jest kluczem obcym w relacji
NOT NULL
CHECK
UNIQUE
(przykład użycia poniżej)
– zastosowanie tego ograniczenia wymusi aby w każdej krotce wartość atrybutu
była nie pusta
– sprawdzenie czy wartość atrybutu jest z dopuszczalnego zakresu
– wymagania unikalnej wartości atrybutu
W poniższej deklaracji dla atrybutu nazwa ustalamy, że ma być wartością tekstową dla której
rezerwujemy 30 znaków, pole to nie może przyjmować wartości NULL oraz jest kluczem głównym
relacji:
IF OBJECT_ID('Przedmioty','U') IS NULL
-- Jeśli tabela nie istnieje
CREATE TABLE Przedmioty
-- Utwórz tabelę
(nazwa VARCHAR(30) NOT NULL PRIMARY KEY)
Przykłady instrukcji tworzących (rożne) tabele:
IF OBJECT_ID('Nauczyciele','U') IS NULL -- Jeśli tabela nie istnieje
CREATE TABLE Nauczyciele(
-- Utwórz tabelę
id
INT NOT NULL PRIMARY KEY,
nazwisko VARCHAR(30) CHECK (nazwisko LIKE '[A-Z]%'),
dyzur
VARCHAR(30) CHECK (dyzur in ('pon', 'wt', 'sr', 'czw', 'pt')),
BAD210
Strona 2
zarobek
FLOAT CHECK (zarobek >= 1000)
)
IF OBJECT_ID('Studenci','U') IS NULL
-- Jeśli tabela nie istnieje
CREATE TABLE Studenci(
-- Utwórz tabelę
id
INT IDENTITY(1,1) PRIMARY KEY,
imie
VARCHAR(10),
id_wydz
INT REFERENCES Wydzialy(id_wydz),
rodzaj
VARCHAR(7) CHECK (rodzaj in ('dzienny', 'zaoczny')) DEFAULT
'dzienny',
rok_studiow INT
)
Modyfikacja tabeli
Istniejącą tabelę możemy zmodyfikować - dodając bądź usuwając atrybuty, a także dodając do nich
ograniczenia. Wykorzystujemy do tego polecenie alter.
IF OBJECT_ID('NazwaTabeli','U') IS NULL -- Jeśli tabela nie istnieje
ALTER TABLE NazwaTabeli <operacja> [, <operacja> …];
gdzie <operacja> =
ADD <definicja atrybutu>
ADD <ograniczenie>
ALTER [COLUMN] <definicja atrybutu>
DROP nazwa atrybutu
DROP CONSTRAINT nazwa ograniczenia
- dodanie nowego atrybutu(kolumny)
- dodanie ograniczenia
- modyfikacja atrybutu
- usunięcie atrybutu
- usunięcie ograniczenia
UWAGA: Przed dodaniem, usunięciem lub modyfikacją kolumny należy sprawdzić, czy dana kolumna
istnieje!
Sposób użycia (dodanie to tabeli nauczyciele atrybutu DataUrodz):
IF COL_LENGTH('Nauczyciele','DataUrodz') IS NULL -- Jeśli kolumna nie
istnieje
ALTER TABLE Nauczyciele ADD DataUrodz SMALLDATETIME -- dodaj kolumnę
Usuwanie tabeli
Aby usnąć tabele (wraz z danymi które się w niej znajdują) należy zastosować polecenie:
IF OBJECT_ID('NazwaTabeli','U') IS NOT NULL
DROP TABLE NazwaTabeli
-- Jeśli tabela istnieje
-- usuń tabelę
Przykład użycia (w efekcie zostanie usunięta tabela nauczyciele):
IF OBJECT_ID('Nauczyciele','U') IS NOT NULL
-- Jeśli tabela istnieje
DROP TABLE Nauczyciele
-- usuń tabelę
Należy pamiętać, że nie możemy usunąć tabeli do której istnieją powiązania bez uprzedniego
usunięcia powiązań (kluczy obcych w innych tabelach) lub całych tabel które zawierają odwołania do
usuwanej tabeli.
Tworzenie automatycznego identyfikatora
Bardzo często definiując tabele chcemy aby identyfikator krotek był automatycznie tworzony przez
silnik bazy danych. W ten sposób wstawiając dane do tabeli nie musimy sprawdzać, czy wartość
identyfikatora krotki, którą chcemy dodać jest unikalna. W przypadku MS SQL aby uzyskać
automatyczne tworzenie identyfikatora tabeli wystarczy, że w definicji tabeli przy polu, które chcemy
BAD210
Strona 3
aby było kluczem głównym dodamy ograniczenie IDENTITY(START,KROK) gdzie parametr START
określa od jakiej wartości chcemy rozpocząć numerowanie, KROK określa o jaką wartość zwiększane
będą wartości atrybutu w kolejnych krotkach.
IF OBJECT_ID('Studenci','U') IS NULL
-- Jeśli tabela nie istnieje
CREATE TABLE Studenci(
-- Utwórz tabelę
id INT IDENTITY(1,1) PRIMARY KEY, -- Automatycznie zwiększaj wartość
imie
VARCHAR(10),
id_wydz
INT REFERENCES Wydzialy(id_wydz),
rodzaj
VARCHAR(7) CHECK (rodzaj in ('dzienny', 'zaoczny')) DEFAULT
'dzienny',
rok_studiow INT
)
Wprowadzanie danych do tabeli
INSERT INTO NazwaTabeli [(atrybut, atrybut, ... )] VALUES (wartosc,
wartosc,...)
Przykłady:
INSERT INTO Autorzy VALUES (1, 'Marciniak', 'Polska')
INSERT INTO Studenci (pesel, imie, wydzial, rok)
VALUES ('11111111111', 'Jakub',1, 2)
UWAGA: Możemy nie podawać nazw kolumn, ale musimy wtedy podać wartości dla wszystkich
kolumn w odpowiednim porządku (pierwszy przykład). Jeżeli nie chcemy podawać wszystkich
wartości, bo są one np. domyślne, generowane automatycznie to musimy podać nazwy kolumn dla
których wprowadzone będą wartości (drugi przykład).
Klucze własne i obce oparte o kilka kolumn
Przykład tabeli z kluczem własnym opartym o dwie kolumny
IF OBJECT_ID('InvoiceLines','U') IS NULL -- Jeśli tabela nie istnieje
CREATE TABLE InvoiceLines (
-- Utwórz tabelę
ID
INT IDENTITY(1,1),
InvoiceNr
VARCHAR(10) NOT NULL, -- element klucza własnego
--(Klucz główny tabeli InvoiceHeaders)
LineNr
INT NOT NULL,
-- element klucza własnego
ItemCode
VARCHAR(20),
... ,
PRIMARY KEY (InvoiceNr, LineNr),
-- definicja klucza własnego
FOREIGN KEY InvoiceNr REFERENCES InvoiceHeaders(InvoiceNr)
)
Łączenie innej tabeli z tabelą InvoiceLines po kluczu składającym się z dwóch kolumn:
IF OBJECT_ID('InvoiceLineDetails','U') IS NULL -- Jeśli tabela nie istnieje
CREATE TABLE InvoiceLineDetails (
-- Utwórz tabelę
ID
INT IDENTITY(1,1),
InvoiceNr
VARCHAR(10) NOT NULL,
-- elem. klucza własnego (i obcego)
LineNr
INT NOT NULL,
-- elem. klucza własnego (i obcego)
PropertyID VARCHAR(10) NOT NULL,
-- elem. klucza własnego
Value
VARCHAR(10),
PRIMARY KEY (InvoiceNr, LineNr, PropertyID),
-- definicja klucza własnego
FOREIGN KEY (InvoiceNr, LineNr) REFERENCES InvoiceLines(InvoiceNr, LineNr)
-- definicja klucza obcego
)
BAD210
Strona 4
Kopiowanie zawartości tabeli
1. Kopiowanie do tabeli, która już istnieje
Tworzenie tabeli
IF OBJECT_ID('InvoiceHistory','U') IS NULL
CREATE TABLE InvoiceHistory (
ID
INT IDENTITY(1,1),
InvoiceNr
VARCHAR(10) NOT NULL,
ClientNr
VARCHAR(10) NOT NULL,
...
)
-- Jeśli tabela nie istnieje
Kopiowanie danych
INSERT INTO InvoiceHistory (InvoiceNr, ClientNr)
SELECT (InvoiceNr, ClientNr)
FROM Invoices
WHERE...
2. Kopiowanie danych z jednoczesnym tworzeniem tabeli
SELECT (InvoiceNr, ClientNr)
INTO InvoiceHistory2
FROM Invoices
WHERE...
BAD210
-- Utwórz tabelę InvoiceHistory2
-- i wstaw dane z tabeli Invoices
Strona 5
Zadania
W poniższych zadaniach kolorem czerwonym zaznaczono kolumnę, która ma być kluczem własnym
tabeli.
UWAGA: Przed nazwą tabel nie wstawiaj dbo.
1. Utwórz tabelę: SZKOLY (ID_szkola, Nazwa, Miasto)
IF OBJECT_ID(...,'U') IS NULL
CREATE TABLE (..., PRIMARY KEY ...)
2. Utwórz tabelę: NAUCZYCIELE (ID_nauczyciel, Nazwisko, Imie, Data_urodzenia, Stawka)
3. Utwórz tabelę: PRZEDMIOTY (ID_przedmiot, nazwa, ID_szkola, ID_nauczyciel),
ID_szkola i ID_nauczyciel są kluczami obcymi do utworzonych wcześniej tabel.
4. Utwórz tabelę: DZIALY (ID_dzial, Nazwa, Adres) identyfikator ma się nadawać automatycznie
5. Utwórz tabelę: ETATY(ID_etat, Placa_min, Placa_max), Placa _max mniejsza od Placa_min
Wskazówka: Dodaj warunek (CHECK) po utworzeniu tabeli.
6. Utwórz tabelę: KADRY (ID_pracownik, Nazwisko, ID_etat, Szef, Zatrudniony, ID_dzial),
ID_etat jest kluczem obcym do tabeli ETATY, ID_dzial jest kluczem obcym do tabeli DZIALY .
7. Dodaj kolumnę 'Wymagania' (pole tekstowe do 255 znaków) do tabeli ETATY
IF OBJECT_ID(...,'U') IS NOT NULL
-- Jeśli tabela istnieje
IF COL_LENGTH(...,'Wymagania') IS NULL -- Jeśli kolumna nie istnieje
ALTER TABLE ... ADD ...
-- dodaj kolumnę
8. Dodaj kolumnę 'Pesel' w tabeli KADRY VARCHAR(11)
9. Dodaj ograniczenie na kolumnę 'Pesel' w tabeli KADRY by akceptowane były tylko liczby
Wskazówka: (Pesel LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][09][0-9]'
IF OBJECT_ID(...,'U') IS NOT NULL
-- Jeśli tabela istnieje
IF COL_LENGTH(...,'Wymagania') IS NULL -- Jeśli kolumna nie istnieje
ALTER TABLE ... ADD CONSTRAINT CT_Pesel CHECK (...)
10. Usuń kolumnę ‘Wymagania’ w tabeli KADRY
IF OBJECT_ID(...,'U') IS NOT NULL
-- Jeśli tabela istnieje
IF COL_LENGTH(...,'Wymagania') IS NOT NULL -- Jeśli kolumna istnieje
ALTER TABLE ...
11. Wprowadź przykładowe dane do tabel DZIALY, ETATY, KADRY korzystając z tabel ODDZIALY,
STANOWISKA, PRACOWNICY i zapytań SELECT
INSERT INTO DZIALY (...)
SELECT ... FROM Oddzialy
INSERT INTO ETATY (...)
SELECT ... FROM STANOWISKA
INSERT INTO KADRY (...)
SELECT ... FROM PRACOWNICY
BAD210
Strona 6