:~$ newsletter --subscribe
cat ~/artykuly/bazy-danych/tworzenie-usuwanie-i-modyfikacja-tabel-w-sql.md

Tworzenie, usuwanie i modyfikacja tabel w SQL

Dane w typowych bazach danych (np. MySQL, MsSQL) przechowywane są w tak zwanych relacjach czyli inaczej mówiąc tabelach. Tabele oczywiście składają się z wierszy (rekordów) i określonego typu kolumn. W tym artykule zapoznamy się z funkcjami SQL umożliwiającymi tworzenie tabel.

Tworzymy pierwszą tabelę w SQL

Na początek stwórzmy tabelę składającą się z trzech kolumn (jedna kolumna będzie typu int, druga i trzecia typu varchar – czyli będzie przystosowana do przechowywania tekstu):

IDImieNazwisko
1JanKowalski
2MartaNowak

Aby stworzyć powyższą tabelę w SQL musimy skorzystać z funkcji CREATE TABLE, przykład:

sql
CREATE TABLE nazwaTabeli (
ID INT,
Imie VARCHAR(200),
Nazwisko VARCHAR(200)
);

Powyższy kod stworzył nam tabelę o nazwie nazwaTabeli. Oczywiście nie zawiera ona żadnych danych.

Ogólna składnia:

sql
CREATE TABLE nazwaTabeli (
nazwaKolumny TypDanych,
drugaNazwaKolumny TypDanych
-- itd. (możemy mieć dowolną liczbę kolumn)
);

Typy danych

Jak pewnie zauważyliście SQL oferuje różne typy przechowywanych danych, w tabeli poniżej zamieszczam te najbardziej popularne:

TYPOpis
VARCHAR(200)Typ tekstowy, w nawiasach podajmy liczbę znaków jakie maksymalnie będzie można zapisać w danej kolumnie (do 8 000)
INTLiczba całkowita (od -231 do 231-1)
FLOAT(2)Liczby rzeczywiste z określoną w nawiasach dokładnością (1-24 – 7 cyfr, 25-53 – 15 cyfr)
DATETIMEData i czas (Format: YYYY-MM-DD HH:mm:SS, Y – Rok, M – Miesiąc, D – Dzień, H – Godzina, m – Minuta, S – sekunda)
CHARŁańcuch znaków o długości do 8000 znaków
TEXTTyp tekstowy (maksymalna liczba znaków: 231 – 2 147 483 647)
MONEYTyp danych służący do przechowywania wartości pieniężnych (dokładność do jednej dziesięciotysięcznej jednostki, przechowuje liczby z zakresu: od -263 do 263-1)

Usuwanie tabeli

Tabelę usuwamy stosując instrukcję DROP TABLE:

sql
DROP TABLE nazwaTabeli;

Modyfikacja utworzonej tabeli

Modyfikując utworzoną już tabelę, musimy zastosować instrukcję ALTER TABLE. Umożliwia ona między innymi dodanie nowej kolumny, skasowanie już utworzonej kolumny czy też dodanie lub usunięcie danego ograniczenia.

Dodanie nowej kolumny do już istniejącej tabeli:

sql
ALTER TABLE nazwaTabeli
ADD nazwaKolumny VARCHAR(100);

Usunięcie kolumny z istniejącej tabeli:

sql
ALTER TABLE nazwaTabeli
DROP COLUMN nazwaKolumny;

Ograniczenia

W tabelach tworzonych w bazach danych można narzucić pewne ograniczenia określające dokładnie strukturę danych jakie mogą zostać zapisane w danej kolumnie.

Zabraniamy wstawianie wartości NULL (czyli po prostu nie wstawienie niczego):

sql
CREATE TABLE nazwaTabeli (
ID INT NOT NULL,
Imie VARCHAR(200),
Nazwisko VARCHAR(200)
);

Jak widać na powyższym przykładzie robimy to wstawiając po typie danej kolumny klauzulę NOT NULL.

Można to oczywiście zrobić na utworzonej już tabeli:

sql
ALTER TABLE nazwaTabeli
ALTER COLUMN nazwaKolumny VARCHAR(200) NOT NULL;

Aby jawnie zezwolić na wstawianie NULL można po prostu pominąć słowo NOT:

sql
ALTER TABLE nazwaTabeli
ALTER COLUMN nazwaKolumny VARCHAR(200) NULL;

Klucz podstawowy

Klucz podstawowy w tabelach odgrywa bardzo ważną rolę, bowiem służy do jednoznacznego identyfikowania wierszy (rekordów) w danej tabeli. Logiczne jest zatem to, że dane w tej kolumnie (lub kolumnach) nie mogą się powtarzać (lub nie mogą występować w takim samym układzie wielokrotnie).

Klucz podstawowy to oczywiście zwykła kolumna o jakimś typie danych (najczęściej jest to INT) zawierająca stosowne ograniczenia (dane muszą być unikalne, czyli nie mogą się powtarzać).

Tworzenie tabeli z kluczem podstawowym:

sql
CREATE TABLE nazwaTabeli (
ID INT PRIMARY KEY,
Imie VARCHAR(200),
Nazwisko VARCHAR(200)
);

Jak widać po typie podajemy informację iż jest to klucz podstawowy – PRIMARY KEY.

Dodanie klucza podstawowego do istniejącej tabeli:

sql
ALTER TABLE nazwaTabeli
ADD CONSTRAINT Klucz PRIMARY KEY(ID);

Automatyczna inkrementacja klucza podstawowego

Dobrze by było, żeby przy wstawianiu nowych danych do tabeli nikt nie myślał, jaką wartość podać dla klucza gdyż ktoś po prostu może nie wiedzieć jakie ID posiada ostatnio dodany rekord. Przydałoby się, żeby wartość tej kolumny była automatycznie uzupełniana przez serwer bazodanowy.

MsSQL

W bazie danych firmy Microsoft (MsSQL) auto-inkrementacje uzyskuje się za pomocą komendy IDENTITY (UWAGA: Dodanie tej własności dla kolumny jest możliwe tylko przy jej tworzeniu, nie ma możliwości zaktualizowania utworzonej już kolumny):

sql
CREATE TABLE tabela (
ID INT IDENTITY (1,1) CONSTRAINT klucz PRIMARY KEY,
Imie VARCHAR(200),
Nazwisko VARCHAR(200)
);

MySQL

W bazie danych MySQL efekt ten uzyskujemy przez zastosowanie komendy AUTO_INCREMENT:

sql
CREATE TABLE tabela (
id INT NOT NULL AUTO_INCREMENT,
Imie Varchar(200),
Nazwisko VARCHAR(200),
PRIMARY KEY (id)
)

Niepowtarzalność danych

Jeżeli zdecydujemy się na używanie jako klucza podstawowego danych, które nie są automatycznie generowane przez serwer bazodanowy musimy oczywiście zagwarantować ich niepowtarzalność (unikalność) tak aby można było jednoznacznie zidentyfikować poszczególny rekord w tabeli.

W bazach MsSQL oraz MySQL wykonuje się to za pośrednictwem słowa kluczowego UNIQUE:

sql
CREATE TABLE nazwaTabeli (
ID INT PRIMARY KEY UNIQUE,
Imie VARCHAR(200),
Nazwisko VARCHAR(200)
);

Klucz obcy (FOREIGN KEY)

Klucz podstawowy jednoznacznie identyfikuje wiersz w tabeli. Klucz obcy (ang. foreign key) idzie o krok dalej, bo łączy ze sobą dwie tabele. To fundament relacyjnych baz danych. Załóżmy, że oprócz tabeli klienci mamy tabelę zamowienia. Chcemy, aby każde zamówienie wskazywało na istniejącego klienta:

sql
CREATE TABLE zamowienia (
    Id INT PRIMARY KEY,
    KlientId INT,
    Kwota DECIMAL(10,2),
    FOREIGN KEY (KlientId) REFERENCES klienci(Id)
);

Kolumna KlientId jest teraz kluczem obcym wskazującym na kolumnę Id w tabeli klienci. Baza danych pilnuje tzw. integralności referencyjnej. Nie pozwoli dodać zamówienia dla nieistniejącego klienta ani (domyślnie) usunąć klienta, który ma powiązane zamówienia. Dzięki temu dane nigdy nie „rozjadą się” między tabelami, bo nie powstanie zamówienie-widmo bez właściciela.

Indeksy i przyspieszanie wyszukiwania

Gdy tabela rośnie do setek tysięcy wierszy, wyszukiwanie po kolumnie potrafi znacznie zwolnić, bo baza musi wtedy przejrzeć każdy wiersz po kolei. Rozwiązaniem jest indeks, który działa jak skorowidz na końcu książki: zamiast czytać ją w całości, od razu skaczemy do właściwej strony.

sql
CREATE INDEX idx_nazwisko ON klienci(Nazwisko);

Od tej chwili zapytania filtrujące po kolumnie Nazwisko (np. WHERE Nazwisko = 'Kowalski') będą wykonywać się znacznie szybciej. Indeksy nie są jednak darmowe. Zajmują miejsce i nieco spowalniają operacje zapisu, bo przy każdym INSERT czy UPDATE trzeba zaktualizować także indeks. Dlatego zakłada się je świadomie: na kolumnach, po których naprawdę często wyszukujemy, a nie na wszystkich naraz.

Łukasz Dudziński
Łukasz Dudziński · założyciel i autor
Pasjonat informatyki, a zwłaszcza programowania. Zwariowany maniak na tle nowych technologii oraz komputerów. W wolnych chwilach bloger... więcej-o-mnie →
ls ~/artykuly/powiazane --limit 3
komentarze --count 3
rss dla komentarzy →
n8n8n8n2020-02-08 ·

Kurcze, nigdzie nie ma informacji jak to zrobić od początku, czyli co jest potrzebne, jakie oprogramowanie, potem jak stworzyć tabelę a na końcu jak z niej korzystać/modyfikować.
Mam MySQL Workbench ale jak tam wpisuję te komendy to nie mogę ich egzekwować, nie pojawia się żadna tabela, a w tutorialach są same komendy….

lukas1242020-02-09 ·

Pewnie nie masz zainstalowanej bazy MySQL. MySQL Workbench to narzędzie do zarządzania bazą.

n8n8n8n2020-02-09 ·

To znaczy zainstalowałem wszystko z takiej stronki i wiem, że na workbenchu się po prostu wpisuje komendy, po instalacji trochę tych elementów było, bo poinstalowałem wszystkie. Kiedyś nadziałem się na fajny kurs, gdzie babeczka tłumaczyła działanie SQL od samego stworzenia bazy, a użytkownik rwzem z nią tworzył to wszystko, tylko za cholerę nie pamiętam co to był za tutorial…

dodaj-komentarz

Komentarze są moderowane. Podane dane przetwarzamy wyłącznie w celu publikacji komentarza (polityka prywatności).